'OS(사무능력)'에 해당되는 글 425건

  1. 2017.12.16 한글(HWP) TIP 58 - (표/셀 가로, 세로 높이,너비를 단축키[H,C]로 같게하기, 자영업자 실업/구직급여기준보수, 금액)
  2. 2017.12.16 한글(HWP) TIP 57 - (한글 컬러인쇄, 그레이스케일/흑백인쇄 설정하기, 연금저축[신탁,펀드,보험) 세액공제 한도표 )
  3. 2017.12.16 엑셀(EXCEL) 함수 TIP 45 - (두개의 표에서 vlookup으로 중복값찾기, 은행별 정기예금 및 적금금리 표예)
  4. 2017.12.16 엑셀(EXCEL) 함수 TIP 44 - (SUMIF/SUMIFS함수)
  5. 2017.12.16 엑셀(excel) 함수 TIP 43- (데이터베이스 함수를 이용한 표준편차 등 구하기)
  6. 2017.12.16 엑셀(excel)함수 tip 42 - (OFFSET함수를 사용하여 특정기간 값 구하기)
  7. 2017.12.16 엑셀 함수 tip 41 - (offset 함수를 이용하여 지정셀 값 반환하기)
  8. 2017.12.16 엑셀(excel) 함수 TIP 40 - (IF함수와 AND,OR함수 중첩)
  9. 2017.12.16 엑셀(excel)함수 tip 39 - (percentrank, percentile함수로 상대순위 구하기)
  10. 2017.12.16 엑셀함수 TIP 38 - (CEILING, FLOOR함수로 연장근무수당 구하기[배수올림,내림])
  11. 2017.12.16 엑셀(excel) 함수 tip 37 - (code 및 char함수로 일련번호 매기기)
  12. 2017.12.16 엑셀(excel)함수 tip 36 - 영어 대소문자 정리함수(upper,lower, proper함수)
  13. 2017.12.16 엑셀함수 TIP 35 - (QUOTIENT,MID, MOD함수를 이용한 남여 성별표시하기)
  14. 2017.12.16 엑셀(excel) 함수 TIP 34 - CHOOSE함수와 WEEKDAY함수를 이용한 요일구하기
  15. 2017.12.16 엑셀(excel)함수 TIP 34 - (hour, minute, round함수를 이용하여 급여계산하기)
  16. 2017.12.16 엑셀(excel) 함수 TIP 33 - subtotal 함수로 11개 값 구하기
  17. 2017.12.16 엑셀(excel)함수 TIP 32- (column, mid함수를 이용하여 세금계산서 만들기)
  18. 2017.12.16 엑셀(excel) 함수 TIP 32-(그룹화하여 부분합 구하기)
  19. 2017.12.16 엑셀(EXCEL) 함수 TIP 31 - (CUUNTA/COUNTBLANK함수, COUNTIF/COUNTIFS함수)
  20. 2017.12.16 엑셀(excel) 함수 TIP 30 - (참조하는,되는 셀,오류검사 및 추적,순환참조)
  21. 2017.12.16 엑셀(EXCEL) 함수 TIP 29 - (사용자정의 함수/visual basic 둘)
  22. 2017.12.16 엑셀(EXCEL) 함수 TIP 29 - (사용자정의 함수/visual basic 하나)
  23. 2017.12.16 엑셀(EXCEL) 함수 TIP 28 - (LARGE,SMALL,LMATCH,INDEX함수)
  24. 2017.12.16 엑셀(EXCEL) 함수 TIP 27 - (INDEX 및 MATCH함수로 원하는 값 빠르게 찾기)
  25. 2017.12.16 엑셀(excel) 함수 TIP 26 - (반올림함수 MROUND, ROUNDUP,ROUNDDOWN)
  26. 2017.12.16 엑셀(excel) Tip 25 - (틀고정 및 틀 나누기, 숨기기)
  27. 2017.12.16 엑셀(excel)함수 tip 25 - (indirect 및 vlookup함수로 원하는 값 추출하기)
  28. 2017.12.16 엑셀(excel) Tip 24 - (셀의 내용 변경, 행의 데이터 삭제하기)
  29. 2017.12.16 엑셀(excel) Tip 23 - (피벗테이블 보고서로 원하는 값 추출하기)
  30. 2017.12.16 엑셀(excel) Tip 22 - (엑셀 자동고침옵션 및 하이퍼링크 설정방법)

한글(HWP) TIP 58 - (표/셀 가로, 세로 높이,너비를 단축키[H,C]로 같게하기, 자영업자 실업/구직급여기준보수, 금액)


많은 분들이 퇴직을 하고 자영업에 뛰어들고 있습니다. 우리주위에서 가장 많이 볼 수 있는 자영업 형태가 편의점창업, 카페창업, 닭튀김집, 김밥집 등입니다. 물론 영세소상공인들로 시장상인들도 자영업자에 해당이 됩니다. 국세청의 통계자료에 의하면 우리나라 자영업자 7명 중에 4명은 3년안에 폐업하는 것으로 조사되었습니다. 국내 전체 자영업자가 하루동안 3천여명이 창업하고 기존에 창업자 중 2천명은 폐업을 합니다. 폐업률이 무려 66.6%에 이릅니다.


자영업을 해야 할까? 주식, 펀드등에 투자할까?


저같은 경우에는 주식을 좀 배워서 주식투자를 하고 있습니다. 퇴직 후에 주식이나 펀드등으로 연 수%의 수익을 낼 수 있다면 노후대책이 된다고 생각을 합니다. 그렇다고 무리한 투자보다는 적정하게 분산(주식, 펀드, 예금, 적금, P2P 등)투자 할 예정입니다.


자영업자 고용보험(실업급여 대상)


우리나라 총 자영업자수는 약 5백만명에 이르고 있으며 이 중 고용보험가입자는 약 20,000여명으로 0.4%에 달하고 있습니다. 자영업자 고용보험에 모르는 경우도 있지만 당장 보험료를 납부하는 것이 아까워서일수도 있습니다. 하지만 폐업률 66.6%정도에 달한다면 위험에 대한 안전장치로 고용보험을 가입하는 것이 필요합니다. 


자영업자 급여일수, 등급별 기준보수, 월 보험료 및 실업급여액


예를 들어 기준보수 1등급을 선택시 월 보험료는 34,650원입니다. 만약 12개월 납부를 했다면 총 보험료는 34,650원*12개월 = 415,800원입니다. 만약 폐업을 한다면 월 실업급여를 수급할 수 있는데 실업급여액은 월 77만원입니다. 급여일수가 90일이기 때문에 받을 수 있는 구직(실업급여)액은 231만원입니다. 즉, 보험료는 77만원 납부했지만 구직급여액은 231만원으로 훨씬 더 많이 받을 수가 있습니다.




자영업자 고용보험료 가입대상 시가, 수급조건, 보험료율, 기준보수, 실업급여액 등


아래와 같이 한글표(자영업자고용보험 세부내용)가 있습니다. 아래의 표에서 너비나 높이를 단축키를 통해서 간단하게 설정학 수 있습니다. 



셀너비를 같게 : 셀 선택(표 안을 클릭 후 F5 세번클릭) > 마우스오른쪽 > 셀너비를 같게


위와 같이 해도 되지만 간단하게 단축키 H를 클릭하면 두개의 행의 폭이 동일하게 됩니다. 



아래와 같이 표의 너비가 동일하게 되었습니다. 



위와같은 상태에서 중간의 셀선을 마우스로 클릭해서 좌측으로 끌여당기면 됩니다.



셀너비를 같게 : 위와 같이 동일한 방법으로 셀의 전체범위를 선택 후에 단축키(C) 또는 (W)를 클릭하면 됩니다.  



단축키로 셀의 너비, 높이를 동일하게 맞추기 결과



Posted by ,,.,

한글(HWP) TIP 57 - (한글 컬러인쇄, 그레이스케일/흑백인쇄 설정하기, 연금저축[신탁,펀드,보험) 세액공제 한도표 )


공공기관, 공무원 등 퇴직나이가 보장된 경우에는 안정적이기는 하지만 크게 돈을 벌 수가 없는 것 같습니다. 그와같은 경우는 대기업, 중소기업 등 사기업을 다니는 경우도 매 한가지 인 것 같습니다. 물론 본인이 어떻게 투자(재테크)를 하느냐에 따라서 일정부분 꾸준한 수익을 내는 길도 있습니다. 대부분 주식투자, 펀드, 부동산투자 등으로 재테크를 합니다. 또한가지 방법은 절세방안입니다. 그 중에 연말정산을 통한 세액공제, 소득공제 등을 통해서 입니다. 


<한글(hwp) 표 : 은행, 생명보험사, 손해보험사, 자산운용사의 연금저축>


아래는 연금저축의 세액공제혜택입니다. 종합소득금액에 따라 최대 세액공제 혜택과 공제비율입니다. 중도해지시의 과세와 연금수령한도, 연금개시후 과세기준 및 분리과세 한도에 대한 표입니다. 만약 개인형 IRP를 포함할 경우에는 300만원을 더해서 최대 700만원까지 공제혜택이 가능합니다. 연금수령조건으로는 연금을 최소 5년이상 납입을 해야 하며, 55세기준으로 10년동안 연금수령시만 연금소득으로 과세되고 과세율은 3.3%~5.5%입니다. 이와같은 조건을 미만족시는 기타소득세로 16.5%로 상당히 높은 세금이 부과가 됩니다. 




(은행, 생명보험사, 손해보험사, 자산운용사의 연금저축구분)


아래와 같은 표(은행, 생명보험사, 손해보험사, 자산운용사의 연금저축)가 있습니다. 연금저축의 종류로은 연금저축보험, 연금저축신탁, 연금저축펀드등으로 구분이 되며, 주요 판매사는 은행, 증권, 보험사입니다. 납입방식, 적용금리, 연금수령방식(확정기간형, 종신형 등), 예금자보허 등은 하단의 표를 참조하시기 바랍니다. 


한글표에서 컬러인쇄하기


아래와 같은 표에서 색상이 들어가 있습니다. 제목줄인 열머리글에는 연두색상이 행머리글은 노란색그라데이션이 되어 있습니다. 이와 같은 표를 칼러나 흑백인새를 한글(HWP)에서 설정하는 방법입니다.



인쇄단축키 : CTRL + P


인쇄단축키는 CTRL + P입니다. 기본적인 단축키는 한글메뉴를 이용하기보다는 단축키를 사용해야 빠르게 작업할 수 있습니다. 


인쇄대화상자에서 기본항목을 확인하면 현재 문서가 인쇄가 되는 프린터가 설정(체크)이 되어 있습니다. 여기에서 [설정]을 클릭합니다.



프린터기 문서속성 : 색상 > 컬러인쇄(체크) > 확인


이렇게 하면 컬러인쇄로 설정이 됩니다. 이를 다시 그레이스케일인 흑백으로 인쇄를 하기 위해서는 하단부분을 체크하면 흑백으로 인쇄가 됩니다. 



미리보기를 통한 인쇄확인하기 


미리보기 메뉴를 통해서 확인을 하면 아래와 같이 A4용지에 인쇄가 됩니다. 



Posted by ,,.,

엑셀(EXCEL) 함수 TIP 45 - (두개의 표에서 vlookup으로 중복값찾기, 은행별 정기예금 및 적금금리 표예)


vlookup중복값찾기


아래는 은행별 정기적금과 정기예금금리 표입니다. 아래와 같은 엑셀(EXCEL)표가 있는 경우 좌측의 표를 참고하여 우측을 채우고자 합니다. 즉, 좌측의 K열을 참고하여 우측의 O열에 채워넣고자 합니다. 아래는 몇개가 되지 않아서 하나하나 좌측의 표를 참고하여 우측에 기록하면 됩니다. 하지만 열의 행이 많은 경우에는 불가능하거나 시간이 많이 걸립니다. 


열이 많을 경우 마우스로 드래그하여 하단방향으로 이동하면서 찾야야 할 것입니다. 이러한 경우 vlookup함수를 이용하면 간단하게 채울수가 있습니다. 저희 회사에서도 통계작업을 할 때 엑셀을 많이 사용하며, 그 중에 각종 함수사용을 많이 합니다. 


은행별 정기적금과 적기예금 금리(세후이자율) 비교 표



vlookup함수대화상자


해당값을 구하고자 하는 셀을 클릭(H3)한 후 함수마법사 클릭(fx), 함수마법사 대화상자에서 함수검색(vlookup) 한 후 vlookup를 선택합니다.  


함수인수대화상자


위와 같이 순서대로 하면 함수인수대화상자가 나타납니다. vlookup과 관련하여 4가지 인수가 나타납니다. 영어로 되어 있지만 전혀 겁먹을 필요가 없습니다. 해당 단어의 의미만 알면 쉽게 이해가 됩니다. 


첫번째 : lookup_value(두 표에서 공통된 값(단어)를 찾는 열의 첫값)


즉, 좌측표와 우측표에서 은행별 같은 공통된 단어가 있습니다. 예를 들어 우측의 광주은행의 값에 좌측의 광주은행값의 적금금리를 끌고와야 합니다. 



두번째 : Table_array(참조하고자 하는 값의 범위)


즉, 우측의 표에 적금금리을 찾아오기 위해서는 참조하고자 하는 범위가 좌측표(은행별정기적금금리)입니다. 좌측표를 마우스로 드래그하면 Table_array에 C2:D15라는 값이 나타납니다. 이를 절대값으로 변환을 해주는데 해당 부분을 클릭하고 절대값변환키인 F4를 한번클릭하면 됩니다. 아래와 같이 $C$2:$D$15로 변하게 됩니다.


이렇게 하는 이유는 나중에 아랫부분에서 마우스로 채우기핸들을 이용하기 때문에 절대값으로 입력이 되어야 하기 때문입니다. 




세번째 : Col_index_num(찾아오고자 하는 값들이 몇번째 열에 있는가)


좌측의 참조범위의 표는 2열로 이루어져 있습니다. col이라는 단어는 컬럼이라는 단어의 약자로 기둥이라는 의미입니다. 찾고자 하는 값이 2번째 열에 있기 때문에 2를 기록하면 됩니다. 만약 여기에 1을 기록하게 되면 금리를 찾아오는 것이 아니라 은행을 찾아오게 됩니다. 



네번째 : Range_lookup(똑 같은 값을 기록하라)


0이라고 적거나 false라고 기록하면 됩니다. 의미는 똑같은 값을 기록하라는 뜻입니다. 



결과표 : 첫열(H3)클릭 후 하단방향으로 드래그하여 채움


H3열에 #N/A라고 기록이 됩니다. 이 부분을 클릭해서 하단방향으로 드래그를 하면 아래와 같이 숫자로 채워집니다. 해당 값이 있는 경우에는 그 값을 끌고와서 기록이 되고 없는 경우에는  #N/A라고 기록이 됩니다. 찾고자 하는 값이 없다는 뜻입니다. 케이뱅크은행, 한국카카오은행, 농협은행의 경우는 좌측표에 없기 때문에  #N/A라고 기록이 됩니다. 


좌측은 %로 나타났지만 우측의 적금금리는 소숫점으로 나타났습니다. 이를 셀서식에서 바꿔주면 됩니다. 


셀서식 변환하기 : 해당열선택 > 백분율, 자릿수늘림(두번클릭)



vlookup으로 찾은 결과값



Posted by ,,.,

엑셀(EXCEL) 함수 TIP 44 - (SUMIF/SUMIFS함수)

 

엑셀 2007(EXCEL)에서는 조건이 여러가지 일 경우에 중첩함수를 사용하지 않고 값을 구할 수 있도록 몇가지 함수가 추가되었습니다. SUMIFS, AVERAGEIFS, COUNTIFS가 있습니다.

 

이러한 함수들은 복잡한 함수계산을 간단하게 할 수 있도록 합니다. 만약 이러한 함수가 아니라면 SUM과 IF함수를 중첨하던가AVERAGE함수에 IF함수를 중첩해야 하는 등 복잡한 계산과정을 거쳐야 합니다. 엑셀이 업그레이드 되면서 함수의 사용도 더더욱 간단해지고 있습니다.  

 

 

 

● SUMIF 함수의 사용예


 

하단에서 [마포점의 판매금액의 총합계]는 [마포점]이란 1가지조건이 들어가 있으므로[SUMIF]함수를 사용하면 됩니다. 하지만 두번째 [마포점이면서 제품이 TV인 판매금액의 총합계]는 [마포점],[TV]라는 두가지 조건이 들어가 있습니

다. 이때는 [SUMIFS]함수를 이용하면 됩니다.

 

 

[수식탭 - 함수라이브러리그룹 - 수학/삼각 - SUMIF]

 

▶ 함수인수창에서 작업

 

[RANGE(A3:A14) - CRITERIA(마포점) -SUMRANGE(F3:F14) - 확인]

 

 

 

과값

 

사용함 함수 [ =SUMIF(A3:A14,"마포점",F3:F14) ]

 

● SUMIFS 함수의 사용예


 

[수식탭 - 함수라이브러리그룹 - 수학/삼각 - SUMIF]

 

▶ 함수인수창에서 작업

[SUM_RANGE(F3:F14) - CRITERIA_RANGE 1(A3:A14) -CRITERIA 1(마포점) - CRITERIA_RANGE 2 (C3:C14) -CRITERIA 2(TV- 확인]

 

[결과값

사용함수[ =SUMIFS(F3:F14,A3:A14,"마포점",C3:C14,"TV") ]

 

 

 


Posted by ,,.,

셀(excel) 함수 TIP 43- (데이터베이스 함수를 이용한 표준편차 등 구하기)

 

엑셀의 함수의 종류가 많습니다. 그 중 데이터베이스함수에 대해서 알아보겠습니다. 데이터베이스는 우리가 평소에 자주 사용하는 부분을 구할 수 있습니다. 가장 쉬운것으로는 셀의 갯수를 비롯해서 부터... 수학에서 어려운 표준편차 등도 데이터베이스함수를 이용하면 쉽게 계산할 수 있습니다.

 

엑셀함수는 자주 사용해 봐야 잊어버리지 않습니다. 종류가 많기 때문에 하나하나 외워서 사용하기가 쉽지는 않습니다. 이용하는 함수의 형식도 직접 함수를 입력할 수도 있지만 함수창을 이용할 수도 있습니다. 

 

1. 데이터베이스 함수

 

앞에 나와있는 D는(DATABASE)의 약자입니다. 데이터베이스의 함수는 아래와 같이 구분이 되며 각종 작업(최대값, 최소값, 원하는 값찾기, 평균, 셀의 갯수, 표준편차)를 간단한 수식을 이용하여 쉽게 계산할 수 있습니다.

 

함수종류

내용

 DAVERAGE

 선택한 데이터베이스 항목의 평균을 구함

 DCOUNT

 데이터베이스에서 숫자 있는 셀갯수구함

 DCOUNTA

 셀이 공란으로 되어있지 않은 갯수를 구함

 DMAX

 최대값을 찾기

 DMIN

 최소값 찾기

 DGET

 원하는 조건에 맞는 레코드하나를 찾기

 DSUM

 조건에맞는 레코드필드 열에있는 값합하기

 DVAR

 표본집단의 분산 예측하기

 DVARP

데이터베이스항목 전체 모집단의 분산계산

 DSTDEV

 데이터 표본의 표준편차 계산하기

 

2. 데이터베이스함수를 이용하여 원하는 값 추출

 

하단과 같이 사원별, 구분별, 업무시작일, 업무일수, 월급이 있다면 이를 바탕으로 전 사원의 총업무일수, 최고금액, 최소금액, 평균금액, 사원수를 계산해보도록 하겠습니다.

 

 

 데이터베이스함수를 이용할 때는 아래와 같이 3개의 구성요소로 이루어집니다

 데이터베이스 함수(Database, Field, Criteria)

 Database : 필드 제목과 데이터로 구성되어 있는 범위

Field : 계산을 수행하고자 하는 필드(열)의 번호(데이터베이스 범위에서 몇번째 열)

Criteria : 필드 제목과 조건으로 구성되이 있는 범위(대부분 데이터베이스 범위와는 따로 입력되어 있음 

 

3. 실제 적용 예

 

▷ (총업무일수 셀)=DSUM(A1:G9,F1,A12:A13)

(최고금액 셀)=DAVERAGE(A1:G9,G1,A12:A13)

(최고금액 셀)=DMAX(A1:G9,G1,A12:A13)

(최소금액 셀)=DMIN(A1:G9,G1,A12:A13)

(사원수)=DCOUNT(A1:G9,F1,A12:A13) 

 


 

Posted by ,,.,

엑셀(excel)함수 tip 42 - (OFFSET함수를 사용하여 특정기간 값 구하기)

 

엑셀(EXCEL)에서 OFFSET함수는 셀 범위에 대한 참조를 반환하는 함수로서 특정한 범위안의 셀 값을 반환하여 다른함수(예:SUM 함수)와 중첩을 통하여 해당 범위의 값의 합계 등을 계산 할수 있는 유용하면서도 많이 사용하는 함수입니다.

 

하단과 같이 일자별, 품목별, 판매값과 반품된 값이 있을 경우에 특정 범위의 일자(예 5일~10일)만의 자료만 필요한 경우가 있습니다. 이때 사용하는 함수가 바로 OFFSET함수와 SUM함수의 중첩함수입니다.

 

 

 

▶ 원본값과 구하고자 하는 값

 

구하고자 하는 값(판매값과 반품값)은 G3와 I3셀에 해당날짜만 입력할 경우 하단의 값들이 자동으로 구해집니다. 이때 사용하는 함수가 바로 OFFSET함수 입니다.

 

 

데이터 유효성검사(설정)하기

 

하단의 자료에서 상단의 [판매기간]의 5일부터 9일까지에서 해당 셀에 5와 9를 입력하면 하단부위의 [기간]에서 5-9라는 숫자가 입력이 되도록 지정을 합니다 이렇게 지정하는 것은 엑셀 탭의 데이터유효성검사에서 할 수 있습니다. [I3셀 선택 - 데이터탭 - 데이터도구 그룹 - 데이터유효성검사]를 클릭

 

▶ 데이터유효성 입력창

 

[제한대상 / 정수, 제한방법 / 해당범위, 최소값 / G3, 최대값 /10] 과 같이 데이터가 유효하다는 설정을 해 줍니다. 이렇게 데이터유효성 검사를 해 두면 제한대상, 최소값, 최대값의 범위를 벗어나게 되면 결과값이 유효하지 않기 때문에 값이 나타나지 않습니다. 구하고자 하는 값과 범위가 위의 엑셀 예보다 많거나 적다면 거기에 맞게  데이터유효성 설정을 하시면 됩니다.

 

 

▶ OFFSET 함수인수 창

 

먼저 판매값은 구해보도록 하겠습니다. 판매값에 입력된 함수식은 =SUM(OFFSET(D2,G3,0,I3-G3+1,1)) 입니다. 이를 함수인수창에서 입력을 할 경우에 [I7셀 클릭 / =  / SUM함수 클릭 ]

 

 

 

[SUM함수인수창에서 Number 1 클릭 / OFFSET함수 클릭]

 

 

[OFFSET함수인수창에서 각각의 인수 입력 / Reference(D2) / ROWS(G3) / COLS(0) / HEIGHT(I3-G1+1) / WIDTH(1) / 확인]

 

 

다음은 반품값을 구해보도록 하겠습니다. 위의 판매값을 구하는 절차와 동일하게 진행하시면 됩니다.

 

[OFFSET함수인수창에서 각각의 인수 입력 / Reference(E2) / ROWS(G3) / COLS(0) / HEIGHT(I3-G1+1) / WIDTH(1) / 확인]

 

▶ 판매값

 

여기에 사용된 함수식을 해석하여 보면 =SUM(OFFSET(D2,G3,0,I3-G3+1,1))

OFFSET함수에서 Reference(D2)는 참조의 범위를 D2로 즉 판매값을 참조하고 / ROWS(G3)는 참조행은 G3(5일)이며 / COLS(0) 참조행은 0(없고)  / HEIGHT(I3-G1+1)는 I3-G1은 4에다 1을 더한 값, 즉 5개의 값을 구하라  / WIDTH(1)은 참조폭은 1열만 참조하라는 뜻입니다. 앞에 SUM함수가 있기 때문에 그 결과를 더하면 됩니다.

 

 

▶ 반품값

 

반품값에 사용된 함수식을 해석하여 보면 =SUM(OFFSET(E2,G3,0,I3-G3+1,1))여기에 사용된 함수식도 판매값과 마찬가지로 해석을 하시면 됩니다.

 


Posted by ,,.,

엑셀 함수 tip 41 - (offset 함수를 이용하여 지정셀 값 반환하기)

 

엑셀(excel)에서 offset함수는 기준이 되는 셀로부터 사용자가 정한 행과 열로부터 해당되는 값은 가져오는 함수입니다.  이 함수는 셀의 범위를 반환하는 함수로서 셀 참조를 요구하는 다른 함수의 인수로 사용되기도 합니다. 많이 사용되는 함수이므로 사용법에 대해서알아보겠습니다.

 

 

함수 구문 : OFFSET(reference,rows,cols,[height,width])


 

  • reference : 기준이 되는 셀 또는 범위
  • rows : 기준점으로 부터 몇번째 행인지, 양수는 하단으로, 음수는 상단방향으로 지정
  • cols : 기준점으로 버터 몇번째 열인지, 양수는 오른쪽, 음스는 왼쪽방향으로 지정
  • [height] : 행의 개수를 나타내며 양수만 지정
  • [width] : 열의 개수를 나타내며 양수만 지정

 

▶ 함수사용 예(수식 직접입력하기)


 

하단은 학생별 과목별 점수입니다. 아래의 표에서 offset함수를 사용하여 결과값을 추출해 보겠습니다. 여기에서 사용된 함수식은 =SUM(OFFSET(B2,3,2,3,3)) 입니다. 즉 B2를 기준으로 3열 하단으로, 2열 우측으로 한 기준셀에서 3행,3열의 갯수의 합을 구하시오 라는 뜻입니다. 따라서 박정형, 수빈정, 명김창 세 학생의 수학,과학,체육값을 더한 결과값이 구해집니다.

 

 

 

▶ 함수사용 예(함수인수창 사용)


 

OFFSET함수인수창에서 작업을 하면 함수식을 직접입력하는 것보다는 더 수월하게 작업할 수 있습니다. 각각의 인수창에다 각 인수의 값을 넣어주면 됩니다. 여기에서는 SUM함수와 OFFSET함수를 중첩하였습니다.

 

중첩함수를 사용할 경우에는 하단처럼 직접 함수삽입창에 입력할 수도 있지만 함수마법사기능을 이용해도 편리합니다. 제 글을 지금까지 읽어오신 분이라면 함수마법사는 쉽게 사용하시리라 생각됩니다.

 

 

에서와 같이 함수삽입을 통해서 [이름정의 창]에 사용하고자 하는 함수가 나타나도록 먼저 검색을 한 후 함수삽입 시 [이름정의 창]에 나타난 함수들을 클릭하면 됩니다. 

 

 

 

☞ 이름정의 창/빨간박스 

 

아래에서와 같이 [값을 구하고자 하는 셀 클릭하여 = 입력 - 좌측 SUM함수 클릭 - SUM함수인수창 - Number1의 인수삽입창클릭 - OFFSET클릭]

 

[OFFSET함수인수창 - 각각의 인수 입력 - 확인 - 결과값 생성]

 

 

 

▶ 결과값


 

 


Posted by ,,.,

IF 함수에 AND와 OR함수를 중첩사용하여 원하는 값은 산출할 수 있다. AND함수는 모든 조건이 TRUE일 경우에 TRUE가 되고  조건중에서 하나라도 FALSE가 있으면 FALSE가 된다. OR함수는 조건중 하나라도 TRUE가 있으면 TRUE가 된다.

 

함수형식

 

=AND(logical1,[logical2],...) : 모든조건이 TRUE일 경우에 TRUE

=OR(logical1,[logical2],...) : 조건중 하나라도 TRUE가 있으면 TRUE, 존건 중 FALSE가 모두일 경우에 FALSE

 

=AND(A1>10,B1>10, C1>10 : 셀의 값이 모두 10보다 클경우 TRUE

=IF(OR(A1>90,A2>90),"합격","불합격") : 셀 중에서 하나라도 90점 이상이면 합격, 두개다 미만일 경우에 불합격

 

AND함수 사용예


 

=AND(B2>90,C2>90,D2>90)

위의 식은 AND라는 조건을 이용했는데 모든 점수가 90점 이상일때 TRUE값을 갖는다. 즉, 어느 과목이라도 90점 이하가 있으면 FALSE 이다.

 

 

  OR함수 사용예


 

=OR(B2>90,C2>90,D2>90)

위의 식은 OR라느 조건을 이용했는데 어느과목에라도 90점 이상이 하나라도 있으면 TRUE이고 모든 점수가 90점 이상이 하나도없을 때만 FALSE이다.

 

 

 

IF (OR) 함수 사용예


 

=IF(OR(F2="A",G2>85),G2*30%,G2*20%)

위의 식은 IF와 OR을 중첩함수로 이용했다. F셀의 값이 A이거나, G셀의 값이 85 점 이상일 경우에(즉, 둘중에 하나의 조건이라도 만족한다면) G 셀의 값에 30%(0.3)를 곱하고, 나머지는 20%(0.2)를 곱하라는 의미이다. 즉 점수를 잘 맞은 사람에게 많은 적립점수를 주기위한 조건식이다.

 

 

●IF(AND) 함수 사용예


 

=IF(AND(F2="A",G2>90),"10만원","5만원")

위의 식은 IF와 AND를 중첩함수로 이용했다. F셀의 값이 A이고 G셀의 값이 90점 일 경우에(즉, 둘의 조건을 동시에 만족할 경우에만) 10만원권 이고 둘중의 하나라도 함수의 조건을 만족하지 않을 경우에는 5만원권을 기록하라는 의미이다.

 


Posted by ,,.,

엑셀(excel)함수 tip 39 - (percentrank, percentile함수로 상대순위 구하기)

 

percentrank, percentile함수는 상대순위를 구하는 함수입니다. 우리가 늘상 익숙해 있는 절대값과는 차이가 있습니다.

 

하단에서 개인별 과목별 점수가 있습니다. 그리고 평균의 값이 있습니다. round함수를 이용해서 평균값의 첫번째에서 반올림을 했습니다. round함수는 5이상일 경우에는 올리고 5미만(1~4.9999)일 경우에는 내리는(없애는)함수입니다.

 

 

if함수를 이용해서 70점을 기준으로 70점 이상일 경우에는 합격 70점 미만일 경우에는 불합격을 구해보면 하단과 같이 함수식은 =IF(G3>=70,"합격","불합격")으로 구해보면 하단과 같은 결과값이 나타납니다. 이때 70점이란 기준은 절대값은 나타냅니다.

 

 

● 절대값으로 합격불합격 정하기


 

▶ IF함수인수창에서 작업

 

 

 

▶ IF함수를 직접 입력

 

 

 

percentrank를 이용해서 상대순위 구하기


 

하단에서 5개의 값이 있을 경우 PERCENTILE함수를 이용해서 상대값을 구했습니다. 여기에 사용된 함수식은 하단과 같습니다. 즉 =PERCENTILE($B$3:$B$7,1) 에서 1은 최대값이기 때문에 98점입니다. 당연히 0의 값은 최소값이기 때문에 67입니다.

 

 

 

여기에서 이용된 K의 값은 0~1까지의 범위로써 1의 값은 최대값이며 0의값은 최소값입니다.

 

 

percentrank함수는 절대의 순위가 아닌 상대의 순위값만을 나타냅니다. 즉 최고값이 100%, 최저값이 0%그 안에 해당되는 값들은 나누기를 통해서 순위만을 정해서 %로 나타냅니다. 즉 서로간의 상대순위만 중요하지 그 값(평균값)의 크기는 중요한 인자가 아닙니다. 이를 함수식으로 구해보면 [=percentrank($G$3:$G$7,$G3]함수로 구해보면 5개의 값들이 최대값 100%,75,50,25,0 다섯개 값으로 나타납니다.

 

 

 

합격여부를 결정할 때는 상대순위의 어느 값을 기준으로 하면 됩니다. 하단에서는50%보다 큰 점수를 기준으로 합격을 정했습니다. 50%이하는 불합격입니다. 이를 IF함수식을 이용하면 [=IF(I3.50%."합격","불합격")]입니다. 이처럼 상대적인 값을 비교해서 합격불합격을 정했습니다.

 

 

 

 

●percentile로상대순위 구하기


 

percentrank함수,percentile함수 두 함수 다 상대순위(값)을 구하는데 percentrank함수는 순위가 구해지면 균등으로 그 안의 값을 정합니다. 즉, 값의 크기는 고려하지 않습니다. 반면 percentile 함수는 상대크기를 정한 후에 그 값의 크기를 고려해서 나타냅니다. 즉 하단위의 함수 [percentile($F$3:$F$7,0.6)]의 의미는  각각의 평균값의 0.6에 해당하는 값(절대값)입니다. 이 값이 82.13이 나왔습니다.

 

 

 

percentile함수로 구한 82.13의 값과 평균값을 비교하여 합격불합격을 결정합니다. 여기에서 이용된 함수식은 =IF(F3>K3,"합격","불합격")]을 이용해서 82.13보다 크면 합격 적으면 불합격은 나타냅니다.

 

 


Posted by ,,.,

엑셀함수 TIP 38 - (CEILING, FLOOR함수로 연장근무수당 구하기[배수올림,내림])

 

엑셀(excel) 함수에서 ceiling함수는 지정한 배수로 올릴 때 사용하며, FLOOR함수는 숫자를 지정한 배수로 내릴 때 사용할 수 있는 유용한 함수입니다. 예를 들어 직장에서 사원들에게 포인트를 지급하고자 할 때 포인트 값이 2550원일 경우 100원단위로 올려서 2600원을 받으려면 CEILING함수를 사용하고 100원단위로 내려서 2500원을 받으려면 FLOOR함수를 사용합니다.

 

직장인 월급을 엑셀에서 계산할 때도 엑셀에서 예를 들어 월급 2,546,587인 경우 천단위를 올릴 경우 CEILING함수를 사용하면 2,547,000원으로 올리면 되고 천단위로 내릴 경우 FLOOR함수를 사용하여 2,545,000원으로 하면 됩니다. 월급의 경우에는 FLOOR함수를 이용하면 깍이게 되기 때문에 CEILING함수를 이용하는 것이 유리하겠죠^^

 

● 사용형식 및 예

 

=CEILING(number, signficance) : 수를 가장 가까운 배수로 올림한 값을 구하라

=CEILING(2124.23,100) , 2134를 100의 배수로 올림한 값을 구하라(=2200)

=FLOOR(number, signficance) : 수를 가장 가까운 배수로 내림한 값을 구하라

=FLOOR(2124.23,100) , 2134를 100의 배수로 내림한 값을 구하라(=2100)

 

  * 두개의 인수가 숫자로 지정되어 있어야 하며 두 인수의 부호가 동일해야 합니다.

 

 

● ceiling함수를 이용한 올림값 구하기

 

하단에서 분기별 채소 등의 평균가격이 있을 경우 평균값에서 소수점 이하의 자리뿐 아니라 1000원단위 이하의 자리에서 올림하고자 할 경우에 =ceiling(eE3,1000)을 이용하여 구하면 됩니다. 하단부위로 채우기 핸들하여 나머지 값 구하기

 

 

 

floor 함수를 이용한 올림값 구하기

 

위의 ceiling함수와 같이 1000원이하는 절사하지만 내림하여 구합니다. 이용한 함수는 =floor(e3,1000)으로 구하고 나머지는 채우기핸들로 구하면 됩니다.

 

 

 

ceiling함수, floor함수, time함수를 이용하여 근무시간 구하기

 

[출근시간 구하기]

 

직장생활하는 분들은 잘 아시겠지만 직장 출퇴근시 출근 및 퇴근카드를 출입구에서 체크하여 자신이 회사에서 근무한 시간이 관리가 됩니다. 또한 이렇게 체크된 시간은 연장근무, 일당 등의 지급시에도 유용하게 이용이 됩니다. 일반적으로 회사출근시간은 30분단위로 계산이 됩니다. 예를 들어 7:01~7:29분 사이의 출근을 7:30분으로 체크를 합니다. 이때 이용하는 함수가 ceiling과 time함수입니다. 사용된 식은 =CEILING(C5,(TIME(0,30,0)))입니다. 즉 TIME(0,30,0)의 의미는 30분 단위로 반올림하라는 뜻입니다.

 

 

 

 

[시간변환하기]

 

위에서  TTIME함수를 사용한 결과값이 이상하게 나타나 있습니다. 시간으로가 아니고 소수점으로 0.35416667 등으로 나타나 있는데 이는 시간을 0:00~12:00를 함수에서는 0.00000000~0.99999999사이의 값으로 변환 하기 때문입니다. 이 값을 우리가 평상시 사용하는 시간형식으로 변경해 주면 됩니다. [셀서식 - 시간 - 13:00- 확인]을 하시면 0.3125 라는 시간이 7:30으로 변경이 됩니다.

 

 

 

 

[퇴근시간 구하기]

 

퇴근시간은 FLOOR함수를 이용하여 구합니다. 여기에서 사용된 함수식은 =FLOOR(D5,(TIME(0,30,0)))입니다. 30분 단위로 내림하라는 의미입니다. 회사에서는 근무시간을 구하기 위해 18:20분에 퇴근했다 하더라고 올림해 줄리는 없죠^^ 그래서 바로 18:00분으로 내림을 합니다. 올려주면 시간이 늘어나서 연장근무 수당이 많이 나가거든요. 나머지 값은 채우기 핸들로 이용하면 됩니다. 이렇게 출근시간과 퇴근시간을 구해놓으면 일당 = 시급(5,000원)* 근무시간(퇴근시간-출근시간)하면 구해집니다.

 

 

 


Posted by ,,.,

엑셀(excel) 함수 tip 37 - (code 및 char함수로 일련번호 매기기)


엑셀(excel)작업시 일련번호를 매기는 경우가 대부분입니다. 모든 데이터는 전체의 수를 알수 있도록 첫번째 행머리글에 일련번호를 삽입을 합니다. 일련번호를 삽입하다 보면 중간에 빈셀이 있는 경우 또는 셀병합이 되어있는 경우에는 자동채우기가 되지 않습니다. 이때에도 자동일련번호를 매길 수 있는 함수가 바로 code 및 char 함수입니다.

 

 

▶ 문자와 코드번호


 

하단과 같이 CODE함수와 CHAR함수를 사용하기 위해서는 문자와 코드의 조합을 알아야 합니다. 숫자, 영어대문자, 영어소문자에 대한 코드번호가 각각 있습니다. CODE함수로 문자를 코드번호로, CHAR함수를 이용하새 코드번호를 문자로 나타낼수 있습니다.

 

 

 ▶ROW 함수로 일련번호 매기기


 

보통 일련번호를 매길 경우에 하단처럼 셀의 최 상부에 1을 기록한 후 셀을 선택하여 하단으로 드래그 하면 1값이 복사되기 때문에 하단도 1로 채워집니다. 따라서 첫번째셀과 두번째셀에 1,2를 각각 기록 후 두셀을 선택하여 하단으로 드래그 하면 보시는 것처럼 일련번호가 순서대로 매겨집니다.

 

 

하단은 ROW함수를 이용하여 일련번호를 매긴 결과입니다. B3셀에 입력된  ROW(A1)의 결과값은 1입니다. A1셀은 열에서 첫번째이기 때문에 1입니다. RAW(A2)의 값은 2가됩니다. 하단부위는 3,4,5,6,....순으로 나갑니다.

 

 

 ▶ CODE 및 CHAR함수로 영문 일련번호 매기기


 

[입력하고자 하는 셀 선택(C3:C10) - 함수삽입(=CHAR(CODE("A1")+ROW(A1)-1) ) - Ctrl + enter]

 

하단의 수식을 보면 복잡한것 같지만 실은 단순합니다. 영문으로 일련번호를 매기기 위하여 사용된 함수는 CHAR, CODE, ROW함수입니다. 사용된 함수값은 =CHAR(CODE("A1")+ROW(A1)-1) 입니다. 이를 해석해 보면 CODE("A")는 65, ROW(A1)은 1, 따라서위의 식은 = CHAR(65+1-1)가 되어 A값이 표시가 됩니다. 하단셀은 CODE("B")가 되어 66+1-1로 B가 됩니다. 만약 일련번호를 소문자로 매길려면 CODE("a")로 A를 소문자인 a로 하면 됩니다.

 

 


▶ 병합된 셀 일련번호 매기기


 

[입력하고자 하는 셀 선택(E3:E9) - 함수입력( =COUNTA($D$3:D3) - Ctrl + enter]

 

COUNTA함수(☞관련글 보러가기)는 비어있지 않은 셀의 갯수를 구하는 함수입니다. 따라서 여기에 사용된 함수식은  ( =COUNTA($D$3:D3) ) 입니다. D3셀을 $D$3 절대주소로 지정해 준 이유는 하단으로 드래그시 그 값이 변하면 되지 않기 때문입니다. 셀의 값을 절대주소로 변경할 경우에는 F4기능키를 이용하면 됩니다. 즉 D3를 셀에 입력 후 F4기능키를 클릭하면 됩니다. 예를 들어 E5셀의 일련번호 값이 2인 이유는 ( =COUNTA($D$3:D4) ) D3에서 D4 사이에서 비어있지 않은 셀은 2개이기 때문에 2가 됩니다. 즉 병합된 셀은 비어있지 않은 셀로 인식을 합니다. 

 

 

▶ 같은 데이터(과목)로 일련번호 매기기


 

[입력하고자 하는 셀 선택(E3:E9) - 함수입력 ( =COUNTIF($F$3:F3,F3) )  - Ctrl + enter]

 

COUNTIF함수는 조건에 맞는 수를 구하는 함수입니다(관련글 보러가기) 따라서 여기에 입력된 함수( =COUNTIF($F$3:F3,F3) )에 대한 의미를 분석해 보면 $F$에서F3까지 F3와 같은 것은 1개 이기에 1입니다. =COUNTIF($F$3:F7,F7)가 2가 나온 이유는 $F$에서F7까지 F3와 같은 것(국어)는 2이기에 2입니다 

 


Posted by ,,.,

엑셀(excel)에서 영문자를 사용하다 보면 때에 따라서 대소문자를 변환 할 필요가 있습니다. 우리이름을 영문으로 사용할 때도 첫 글자는 대문자 로 사용하고 나머지는 소문자로 사용합니다. 또한 기업의 영문 이니셜도 첫글자는 대문자 나머지는 소문자로 사용을 많이 합니다. 문론 하나하나 입력시에는 컴퓨터 자판의 Caps lock기능으로 대소문자를 변환하여 사용하면 되지만 수십, 수백개의 영문이 입력이 있을 때 이를 하나하나 변환하기란 여간 쉬운일이 아닙니다.

 

이때 upper,lower, proper함수를 사용하면 간단하게 변환이 가능합니다. 엑셀(excel)은 사용하면 할수록 좋은 프로그램입니다. 

 

♥ 형식 =UPPER(text), LOWER(text), PROPER(text)

  -  UPPER는 모두 대문자로, LOWER는 모두 소문자로, PROPER는 첫글자만 대문자로 나머지는 소문자로

예 =upper(i love you)는 I LOVE YOU, =lower(i love you)는 i love you =proper(i love you)는 I love you

 

● UPPER함수로 대문자 변환하기


 

[E3:E8 범위 선택 - 함수 입력(=UPPER(D3) - Ctrl + Enter]를 하면 하단과 같이 전체의 코드값이 대문자로 변환이 됩니다.여기에서 함수를 입력하는 방법을 Ctrl + Enter를 이용하였습니다.

 

 

☞ 함수입력 TIP - (Ctrl + Enter)로 빠르게 값 계산하기

 

함수로 값을 구할 경우에 구하고자 하는 함수의 첫번째 값을 구하고 난 후에 하단으로 드래그 하여 구하는 경우도

있지만 (Ctrl + Enter)를 이용하면 한번에 구해집니다. 이때는[구하고자 하는 전 범위를 선택 - 함수입력 - (Ctrl + Enter)]를 하면 됩니다.

  

● LOWER 함수로 소문자 변환하기


 

이작업은 함수마법사기능을 이용해보도록 하게습니다. 채우기핸들이 아닌 구하고자 하는 전체범위를 선택(F3:F8)하여구하도록 하겠습니다. 이때 이용하는 기능은 Ctrl 기능키입니다.[F3:F8 범위 선택 - 수식탭 - 함수삽입(FX) - 함수마법사 - LOWER검색 - 확인]

 

 

 

[LOWER 함수인수 창 - Text(E3) - Ctrl + Enter]를 하면 구하고자 하는 전 범위에 걸쳐서 값이 소문자로 변환이 되었습니다. LOWER함수인수창에서 확인을 하지 않고 Ctrl + Enter를 합니다.

 

  

▶ 결과값

 

 하단과 같이 모든 영자들이 소문자로 변환이 되었습니다. 

.



 

● PROPER 함수로 대문자 변환하기


 

이번에는 함수를 직접입력하여 구해보겠습니다. [G3:8G 범위선택 - 함수입력 / PROPER(F3) - Ctrl + Enter]를 하면 전체범위에 값이 한번에 구해집니다. 함수마법사 기능이용한 것보다 더 간편하게 구할 수 있습니다. 다만 인수가 하나라서 이렇게 구할 수 있지만 인수가 많아지면수식을 입력시 ()등으로 인하여 헤깔릴 경우도 있고 또한 복잡하기 때문에 함수인수창을 이용하면 더 쉽게 구할 수있습니다. 따라서 인수의 갰수에 따라 함수마법사를 이용할 것인지 함수를 직접입력할 것인지 결정해서 사용하면 됩니다.

 

 

▶ 결과값

 

하단과 같이 영문장의 첫번째 글자는 대문자 나머지는 소문자로 변환이 되어 있습니다. 영어단어가 띠어쓰기가

되어 있을 경우에 각각의 문장으로 인식하여 첫번째 글자를 대문자로 변환하고 나머지는 소문자로 변환합니다.

   


Posted by ,,.,

엑셀함수 TIP 35 - (QUOTIENT,MID, MOD함수를 이용한 남여 성별표시하기)

 

엑셀(EXCEL)함수에서 나누기에 대한 값을 구할 수 있습니다. 어떤 수(X)를 Y의 값으로 나누었을 때 몫을 구할 때는 QUOTIENT 함수를 나머지를 구할 때는 MOD함수를 이용합니다. 하단과 같이 학생별 총점수가 있을 때 이 총점을 일정금액당 쿠폰으로 지급하고 나머지값을 구할 때 바로 QUOTIENT,MOD함수를 이용합니다. 

 

T -TIMES라는 홈페이지에서 읽은 글입니다. "나쁜 상사가 될 징후 다섯가지" 읽으면서 같이 근무하는 상사와 왜 이리도 똑같을까 하는 생각을 하게 되었습니다. 이러한 경우 이직을 반드시 고려하라고 하는데..... 좋은 상사되시기 바랍니다.

하나. 쉽게 냉정을 잃는다(미친 듯이 화를 내는 상사)

둘, 남을 자주 비방한다(동료, 부하, 직원잘못을 자주 지적하는 상사)

셋, 쉬고있는 직원에게 연락을 하는 상사

넷, 비전을 공유하지 않는다(본인만 가지고 있는 상사)

다섯, 일에 대해서만 커뮤니케이션하는 상사(개인사에 전혀 관심이 없는 상사)


▶ 원본값

 

QUOTIENT 함수를 이용한 몫 구하기


 

하단과 같이 QUOTIENT함수에 대한 함수구조는 QUOTIENT(numerator, denominater) 구조로 이루어 집니다. numerator는 나누고자 하는 원본 값이고, denominater는 이 값으로 나눕니다. 하단과 같이 QUOTIENT(E5,30)에서 E5의 값을 30으로 나눈값의 몫(쿠폰갯수)의 값이 나와있습니다. 하단으로 채우기 핸들을 이용하여 나머지 값을 구하면 됩니다.

 

 

 MOD 함수를 이용한 나머지 구하기


 

하단과 같이 MOD함수에 대한 함수구조는MOD(NUMBER, DIVISOR) 구조로 이루어 집니다. NUMBER는 나누고자 하는 원본 값이고, DIVISOR는 이 값으로 나눕니다. 하단과 같이 MOD(E5,30)에서 E5의 값을 30으로 나눈값의 나머지 값이 나와있습니다. 하단으로 채우기 핸들을 이용하여 나머지 값을 구하면 됩니다. 

 

 

 MOD 함수를 이용한 현재시간 구하기


 

날짜,시간 함수에서 현재시간만을 구하는 합수는 없습니다. NOW함수는 현재 날짜와 시간을 구할 수 있고 TODAY함수는 현재 날자를 구할 수 있습니다. 이때 MOD함수를 이용해서 현재 시간만 구할 수 있습니다. 즉 NOW()를 TODAY()로 나누면 됩니다. 즉 현재날짜 및 시간(NOW)을 현재날짜(TODAY)로 나누면 당연히 현재 시간만 남을 것입니다.

 

 

 

 IF, MID, MOD 함수를 이용하여 성별 구하기


 

하단은 다소 복잡해 보이지만 IF, MID, MOD 함수를 이용하여 성별 구했습니다. 직접 수식을 입력할 경우에 많은 ()부호 때문에 헤깔 릴수가 있기 때문에 이때는 함수인수창을 이용하면 쉽게 구할 구 있습니다. 이 때 사용한 함수는 =IF(MOD(MID(B3,8,1),2)=1,"남","여")입니다. 이를 해석하면 하단의 엑셀 시트에서 MID(B3,8,1)은 B3의 8번째 숫자에서 첫번째 숫자가, 란 의미입니다. 따라서 주민번호의 뒷자리 첫번째 1 또는 2를 지칭합니다. 

 

[ IF함수 인수창]

 

IF함수인수창의 Logical_test를 클릭한 후 MOD함수인수를 삽입합니다.

 

 

 하단의 MOD함수 인수창의 NUMBER인수에 MID인수를 입력합니다.

 

 

MID 함수인수창의 인수들에 해당되는 값들을 입력합니다.

 

 

▶ 결과값

 

하단과 같이 주민등록번호 뒷자리가 2로 시작될 경우에는 "여"로 1로 시작될 경우에는 "남"으로 표기가 되었습니다. 이렇게 IF함수와 MID, MOD함수를 중첩사용하여 원하는 값을 구할 수 있습니다.이 방법 외에도 IF함수와 MOD함수를 이용해서 성별을 구하는 방법이 있습니다.(바로가기☞주민등록번호에서 IF함수를 이용하여 성별 구하기)

 


Posted by ,,.,

● CHOOSE함수와 WEEKDAY함수를 이용한 요일구하기


 

상단부위의 자료에서 시험응시일은 제공되어 있습니다. 이 시험응시일은 [연-월-일] 형태로 되어 있습니다.

이 시험응시일을 참조로 하고 CHOOSE함수와 WEEKDAY함수를 이용하여 요일을 구해보도록 하겠습니다.

 

[구하고자 하는 셀 선택(G3) - 수식 탭 - 함수라이브러리 그룹 - 찾기/참조영역 - CHOSE함수 - WEEKDAY함수 중첩하기]

 

▶ WEEKDAY함수 인수창에서 작업

[SERIAL_NUMBER(F3) - RETURN_TYPE(1) - 함수입력창에서 CHOOSE선택하여 CHOOSE창으로 변경]

 

 

 CHOOSE 함수인수창에서 하단처럼 요일을 ["일요일"부터 해서 "토요일"까지 차례로 입력 - 확인]

 

 

● 결과값


시험요일의 G3셀에 [목요일]값이 나타나며 이 값을 클릭하여 하단부분으로 채우기핸들을 이용하면 됩니다.

함수입력값 =CHOOSE(WEEKDAY(H3,1),"일요일","월요일","화요일","수요일","목요일","금요일","토요일")

 

 


Posted by ,,.,

엑셀(EXCEL)함수에서 급여계산에 유용한 함수가 있습니다. 바로 HOUR, MINUTE, ROUND함수입니다.

 

 

● HOUR함수와 MINUTE함수로 실제근무시간의 급여계산하기


 

▶ HOUR 함수

 

하단은 개인별 출근시간이 있습니다. HOUR함수를 적용하여 분을 삭제하고 시간만 나타낼 수 있습니다. HOUR함수는 시간함수이기 때문입닏. 예로 6:30분을 HOUR함수를 적용시 시간만 나타내기 때문에 6으로 결과값이 계산이 됩니다.

 

 

▶ MINUTE 함수

 

MINUTE함수는 말뜻 그대로 분을 나타냅니다. 예를 들어 6:30분이 있을 때 이를 MINUTE 함수를 적용을 하면 시간을 삭제가 되고 분만 구해집니다. 따라서 6은 삭제되고 30분만 결과값으로 나타납니다.

 

 

▶ 급여계산하기

 

[급여 = 근무시간*시급]으로 하면 됩니다. 시간에 대한 급여는 [시간*시급]을 하면 되지만 분에 대한 급여는 시간으로 환산을 해야 하기 때문에 [분/60*시급]을 해야 합니다. 그래서 두개를 더해 주면 급여가 계산됩니다. 이처럼 급여계산에 HOUR함수와 MINUTE함수가 사용이 됩니다.

 

 

● ROUND함수란?


 

ROUND(number,num_digits) : 수를 지정한 자릿수로 반올림 한다

ROUND(3.141592,2) 3.141592를 반올림 해서 소수이하 둘째자리까지 구하라(3.14)

 

위의 설명과 같이 ROUND함수는 수를 지정한 자릿수로 반올힘을 하는 함수입니다. 예로 =ROUND(B2,3)의 값은 B2셀의 값을 소수점 3째짜리에서 반올림하라는 뜻입니다. 만약 숫자가 123,4567이 있을 경우 소수셋째자리에서 반올림 하기 때문에 123.457이 됩니다. 반올림하기 때문에 5의 값까지는 반올림이 되지만 1~4까지는 그 값이 사라집니다.

 


 

● TIME함수 알아보기


 

TIME함수는 시간을 숫자로 변환을 합니다. 1~24시간을 숫자로 변환을 하는데 아래와 같이 1시간을 24로 나눈값입니다. 따라서 1시간은 TIME함수로 나타내면 0.0416667이 나오며 12시간은 12*0.0416667=0.5가 나옵니다. 24시간은 24*0.041667로 1의 값이 나옵니다. 즉 TIME함수는 24시간이 1의 값입니다. 하단에서와 같이 D3셀의 값 0.041667을 HOUR함수를 적용을 하면 =HORR(D3)로 1의 값이 나옵니다.

 

 

TIME함수식으로 표현을 하면 =TIME(12,0,0)의 값은 12이기 때문에 0.5가 나옵니다. 만약 =TIME(24,0,0)의 값은 1의 값이 나옵니다.

 

 

=TIME(6,30,30)초의 값은 0.271180556의 값이 나옵니다. 각각의 시간, 분, 초를 값으로 환산한 값입니다.

 

 

● ROUND함수와 HOUR함수를 적용한 급여계산하기


하단의 함수식 [=ROUND(E2*24,0)24]의 식은 근무시간을 반올림하기 위한 함수식입니다. 여기에서의 급여계산은 30분 이상은 반올림을 하고30분미만은 무시를 해서 급여를 계산하는 방법입니다. 8:40분 근무시간이라면 9:00으로. 8:20분이라면 8:00으로 적용을 합니다. 하단과 같이 ROUND함수식을 적용을 했습니다.

 

 

 

[총 급여 = 시급 * 근무시간]이 됩니다. 여기에서 근무시간에 HOUR함수를 사용하여 HOUR(I2)로 나타낸것은 시간을 숫자로 변환하기 위해서입니다. 하단과 같이 HOUR함수를 이용해 7:00을 7로 8:00을 8로 변환을 했습니다.

 

 

[급여 = 시급 * 근무시간]으로 ROUND함수와 HOUR함수를 이용해서 급여가 구해졌습니다.

 

 


Posted by ,,.,

 

목록이나 데이터베이스의 부분합(subtotal)을 구합니다. 합계, 평균, 개수, 최대, 최소, 표준편차 등 함수 하나로 구할 수 있는 값이 11가지나 됩니다. subtotal함수는 1부터 11까지 또는 101부터 111까지의 함수 번호를 이용해서 해당되는 함수로 계산을 수행합니다. 이 함수의 특징은 숨겨져 있는 셀을 계산에 포함시키거나 제외할 수 있도록 지정할 수 있습니다.

 

 

▶ SUBTOTAL 함수 형식

 

  • 형식 : SUBTOTAL(function_num, ref1. ref2,....)

                 - 데이터목록(ref)에서 지정함 함수(function)를 구하시오 

  •    사용예 : SUBTOTAL(4,A1:B2, C1:G2)

               -  [A1:B2], [C1:G2]의 범위에서 4(최대값/MAX)을 구하시오

 

 

 

 

▶ SUBTOTAL 함수입력창 구조 

 

 

 

▶  SUBTOTAL함수의 FUNCTION-NUM에 따른 함수값

 

 

 

▶  SUBTOTAL함수의 사용예

 

하단과 같이 [박희망]학생의 [학년학기별] [과목별] 점수가 있습니다. 이 점수에 대한 각각의 SUBTOTAL값을 구하는 방법입니다. 하단의 자료에서는 숨겨진 셀이 없는 경우의 계산 방법(숨겨진 값이 있는 경우에는 숨겨진 값도 포함)입니다. 해당 값들은 하나의 숫자로만 표시되어야 합니다.

 

 

 

 

▶CHOOSE함수를 사용하기

 

CHOOSE함수를 사용한 이유는 [함수번호]란에 간단하게 번호를 입력하면 해당 함수가 나타나고 그에 따른 결과값을 알수가 있습니다. [C3]에 들어간 CHOOSE함수에는 SUBTOTAL함수에 사용되는 함수들이 순서대로 들어가있습니다.

 

 

 

SUBTOTAL(B3,C6:F11)이란 구문은 [C6:F11]의 범위에서 B3의 함수(SUM)를 구하시오라는 뜻입니다. 하단과 같이 16개의 시트에 있는 전체의 값들이 더해졌습니다.

 


 

▶ 숨겨진 셀에 대한 값

 

하단에서 우측에 보이는 것처럼 7,9행의 사이에 8행(우측의 겨울학기)이 숨겨져 있습니다. 함수번호가 1~11번을 이용하면 숨겨진 셀도 포함을 시킵니다. 그결과 실제로 보이는 셀이 24개라 하더라도 숨겨진 4개의 셀값이 있기 때문에 COUNT는 결과값이 28로 나타납니다. 만약 번호를 101~109번호로 바꾸어서 함수를 사용한다면 숨겨진 셀을 제외하기 때문에 COUNT에 대한 결과값은 24가 될 것입니다.

 

 

 

함수번호를 1,2,4,7,9번호로 바꾸면 해당하는 함수AVERAGE, COUNT, MAX, STDEB, SUM함수에 대한 결과값이 표시가 됩니다. 이처럼 CHOOSE함수와 SUBTOTAL함수를 이용하여서 11가지 함수에 대한 값을 한번에 계산할 수 있습니다.

 

 

 

 


Posted by ,,.,

엑셀(excel)함수 TIP 32- (column, mid함수를 이용하여 세금계산서 만들기)

 

엑셀(excel)에서 셀 하나의 값에 숫자하나로 표시할 수 있습니다. 사업장(회사)에서 가장 많이 사용하는 세금계산서, 거래명세서, 견적서, 계약서에서 숫자를 작성할 때 오른쪽 끝을 기준으로 숫자를 표시합니다. 이때 숫자 하나에 칸 하나씩을 이용합니다. 이렇게 끝을 맞추는 이유는 금액이 얼마인지 쉽게 구별할 수 있기 때문입니다.

 

이번글에서는 TEXT함수, COLUMN 함수와 MID, ROW, trim함수를 이용하서 한칸에 한자씩 숫자를 추출하는 방법에 대해서 기술하도록 하겠습니다.

 

 

 

 

 TEXT함수로 글자수 맞추기

 

[값을 구하고자 하는 셀 범위지정(D2:D11) - 함수입력(=TEXT(C2,"???????") - Ctrl *Enter]

 

하단과 같이 text함수는 셀의 숫자를 "?" 서식코드를 사용하여 글자로 만드는데 숫자가 부족할 경우에는 공백으로 처리합니다. "?"를 7로 지정한 이유는 최대 금액이 7자리 숫자(1,256,000)이기 때문입니다.

 

 

 

 LEN함수, TRIM함수로 공백수지정하기

 

[값을 구하고자 하는 셀 범위지정(D2:D11) - 함수입력( =LEN(D2)-LEN(TRIM(D2) )- Ctrl *Enter]

 

LEN(텍스트)함수는 텍스트의 길이를 구하는 함수이며, TRIM(텍스트) 함수는 양쪽의 불필요한 공백을 없애는 함수입니다. 따라서 =LEN(D2)-LEN(TRIM(D2))는 전체 텍스트 숫자(LEN)에서 LEN(TRIM)공백을 제외한 텍스트 숫자를 빼기 때문에 예로 7-2를 하면 5가 됩니다.

 

 

 

MID함수와 COLUMN함수를 이용하여 결과값 구하기

 

[F2 - 함수입력( =MID($D$2,COLUMN(A2),1) ) - 채우기핸들(우측으로드래그)]

 

여기에서 COLUMN함수가 사용이 되었습니다. COLUMN함수는 열의 번호를 구하는 함수로서 COLUMN(A1)은 A1셀의 열 번호가 1이르로 1을 뜻하며 COLUMN(A4)는 열번호가 4이르모 4를 의미합니다. MID함수는 지정한 숫자부터 반환을 하는 함수로서 예를 들어 MID($D$2,COLUMN(A2),1)  의 의미는 D2셀에서 첫번째(1)항부터(COLUMN(A2) ) 첫번째(1)수를 반환하라는 의미입니다. 예를 들어 = MID(D2),3,2는 D2셀의 3번째 부터 2째자리까지 반환하라는 의미입니다. 따라서 백만자리에서는 D2셀에서 공백이기 때문에 0으로 표시가 됩니다.

 

 



나머지 값들은 채우기 핸들을 이용해서 우측으로 드래그 하면 되는데 십만자리의 함수예를 보시면 하단처럼 =MID($D$2,COLUMN(B2),1)되어 있습니다. 채우기 핸들을 하면서 A2 셀이 B2셀로 변경이 되었습니다. COLUMN(B2)는 셀의 열번호가 2이므로 숫자 2를 의미하며 D2셀의 숫자에서 첫번째 (=$D$2,COLUMN(B2),1) 를 반환하므로 123,000원에서 1이 됩니다.

 

 

▶ 채우기 핸들을 이용하여 나머지 값 구하기

 

 하단과 같이 나머지 값들은 채우기핸들을 이용하면 되는데 (F2:L2)범위를 지정하여 하단부위로 드래그 하면 나머지 행의 값들이 채워집니다. F4셀의 함수를 살펴보면 =MID($D4,COLUMN(A4),1)과 같습니다. 하단부위로 드래그 하면 D셀의 값이 절대참조이기 때문에 숫자만 $D1,$D2,$D3,$D4로 변경됩니다.

 

 

 

 결과값

 

하단과 같이 깔끔한 세금계산서 또는 견적서가 엑셀표로 작성되었습니다. 필요없는 부분은 숨기기로 처리하면 됩니다.

 

 


Posted by ,,.,
엑셀(excel) 함수 TIP 32-(그룹화하여 부분합 구하기)

 

기업별로 엑셀(EXCEL)을 사용하지 않는 경우는 거의 없을 것입니다. 기업에서 생산하는 각종 제품의 생산량, 판매량, 제고율 등을 비롯해서 고객관리에 대한 데이터베이스, 직원에 대한 데이터베이스등 엑셀을 통해서 관리하는 부분이 많습니다. 따라서 엑셀은 회사(직장)생활에서 필요한 부분이므로 반드시 마스트해야 할 것입니다. 요즘은 한컴오피스가 나와서 한셀, 한글 2014, 한쇼 등을 공공기관을 중심으로 많이 사용합니다.

 

ms office보다는 기능면에서 부족하지만 가격을 고려한다면 상당히 장점이 있다고 할 수가 있습니다. 직장에서 어떤 문서작성툴을 이용하든간 자유롭게 구사할 수 있는 능력이 있어야 하겠습니다. 오늘은 그륩화하여 부분합하는 방법에 대해서 알아보겠는데 같은 데이터끼리 묶어서 부분합을 구하는 방법입니다.

 

1.원본데이터 


 

하단과 같이 제품에 따른 고객과 분기별 판매량 데이터가 나와있습니다. 기업에서 관리하는 자료는 이보다 더더욱 방대할 것입니다. 이렇게 하나하나의 자료를 통합 분석하는 작업이 피벗테이블(PIVOT TABLE)을 통해서 하면 간단하고 또한 부분합을 통해서도 가능합니다. 아래의 자료를 예를 들면 제품별, 분기별 판매량을 구할 수도 있고 고객별, 분기별 판매량을 구할 수도있습니다. 이러한 원본을 가지고 부분합을 통해서 간략하게 결과를 나타나도록 해 보겠습니다.

 

 

 

2. 부분합


부분합을 하기 위해서는 부분합을 실행하기전에 셀의 선택위치가 해당범위 내에 포인터가 위치하고 있습니다. 어느부분이라도 상관은 없지만 반드시 해당범위내에 위치하고 있어야 합니다.

 

(데이터 - 윤곽선 - 부분합)

 

 

3. 부분합할 항목 선택 및 지정


 

하단과 같이 부분합 할 세항목(그룹화할 항목, 사용할 함수, 부분합 계산 항목)이 나타납니다. 여기에서 그룹화할 항목(제품)을 선택하고 사용할 함수(합계), 부분합계산항목(제품, 고객, 1~4분기)를 선택하고 확인을 합니다.사용할 함수에는 (합계, 평균, 최대값, 최소값, 갯수, 곱, 표준편차)여러가지 중에 계산하고자 하는 부분을 선택하면 됩니다.

 

 



4. 부분합 결과


 

부분합을 실행한 결과입니다. 좌측 상단에 (1,2,3)이 나타나며 각각의 제품에 대한 부분합이 합계로 나타나 있습니다.(보라색)

 

 

 

5. 1단계


 

1단계를 클릭하면 전체 생산제품의 총 합계가 나타납니다. 제품의 구분이 되지 않고 전체 총 합계입니다.

 

 

 

6. 2단계


 

해당 제품의 제품별 총합계만 나타나며 각각제품에 대한 고객항은 없어지며 전체 총 합계가 나타납니다.

 

 

 

7. 3단계


 

3단계는 전체의 부분합을 나타내며 아래와 같습니다. 부분합에서도 셀 숨기기 기능이 가능합니다. 좌측에 보이는 윤곽선을 숨길 수도 있습니다. 숨기는 과정은 [데이터탭 - 윤곽선 - 그룹해제 - 윤곽지우기]를 하면 좌측하단의 윤곽선이 사라집니다.

 



Posted by ,,.,

엑셀(EXCEL) 함수 TIP 31 - (CUUNTA/COUNTBLANK함수, COUNTIF/COUNTIFS함수)

 

 

엑셀(excel 2007)에서 CUUNTA/COUNTBLANK함수, COUNTIF/COUNTIFS함수는 데이터의 집계를 하는 함수입니다. 이 데이터 집계함수의 각각의 역활을 아래와 같습니다.

 

CUUNTA 함수 : 해당범위의 셀 범위내에서 공백을 제외하고 값이 있는 모든 셀의 합계

COUNTBLANK함수 : 해당범위의 셀 범위 내에서 공백인 셀의 합계

COUNTIF 함수 : 한가지 조건에 맞는 셀들의 값을 찾아서 그 셀들의 합계를 구함

COUNTIFS함수 : 여러가지 조건에 맞는 셀들의 값을 찾아서 그 셀들의 합계를 구함. COUNTIFS함수는 엑셀 2007에서 추가된 함수로 최대 127개까지의 조건이 가능함

 

CUUNTA/COUNTBLANK, COUNTIF함수의 사용예


 

하단과 같은 데이터시트가 있을 때(해외연수프로그램 지원 결과) 총 지원자, 합격한 인원수, 봉사점수 미제출자를 구하고자 한다면 CUUNTA/COUNTBLANK, COUNTIF함수를 사용합니다.

 

 

 

 

▶ 이름상자를 이용한 이름정의

 

CUUNTA/COUNTBLANK, COUNTIF함수를 사용할 경우 해당 셀의 범위를 수식에 하나하나 기록하는 것보다는 이렇게 이름상자를 이용하여 정의하면 이해도 쉽고 또한 빠르게 작업할 수 있는 장점이 있습니다. 이 이름상자 정의를 이용해서 문제를 풀어보도록 하게습니다. 이름상자 정의는 [해당 셀 범위 선택 - 이름상자 클릭 - 해당이름 기록 - 엑터]를 하면 됩니다. 이렇게 해서 [학년],[합격여부]에 대한 이름상자를 먼저 지정을 했습니다.

 

 

 

수식사용하여 구하기

 

총 지원자 [=COUNTA(B3:B15)]

합격인원수 [=COUNTIF(합격여부,"합격")]

봉사점수 미제출자 [=COUNTBLANK(F3:F16)]

 

위와 같이 함수를 직접 입력을 해도 되고 각각의 함수를 찾아서 함수 인수창에서 작업을 해도 됩니다.

 

COUNTIF함수 인수창에서 작업예(수식 탭 - 함수추가 - 통계)

 

[RANGE(해당범위) : 합격여부(이름상자로 지정한 이름) - CRITERIA(조건): 합격]

 

 

 

결과값

 

좌측의 각각으 결과값(C17~19)에는 우측과 같은 함수가 삽입되어 있습니다.

 


COUNTIF/COUNTIFS함수의 사용예


 

하단과 같은 원본자료가 있을 때 [학년별 합격한 인원수]를 구하고자 한다면 3가지 조건이 사용됩니다. [학년],[합격],[인원수] 이럴때 사용할 수 있는 함수가 엑셀(EXCEL) 2007에서 추가된 COUNTIFS함수 입니다. 이 함수가 없었다면 COUNT와 IF함수등을 중첩사용하는 등 계산 방법이 약간을 복잡해 집니다. 엑셀의 진화와 함께 함수도 계속 진화하고 있습니다.

 

 

 

 

▶ 수식사용하여 결과값 구하기

 

[구하고자 하는 셀 선택(C18) - 수식탭 - 함수라이브러리 그룹 -  함수추가 -  통계 - COUNTIFS - 함수인수창에서 작업 - 확인]

 

[함수인수창에서 작업

 

Criteria_ range 1(조건범위) : 학년(이름상자로 정한 이름 직접 기록)

Criteria 1(조건) : A18

Criteria_ range 2(조건범위) : 합격여부(이름상자로 정한 이름 직접 기록)

Criteria 2(조건) : $C$17(나중에 채우기 핸들을 사용해야 하므로 F4를 이용하여 절대주소로 변경)

 

 

 

▶ 결과값

사용된 함수 [ =COUNTIFS(학년,A18,합격여부,$C$17) ]

 

 

하단과 같이 이름상자와 COUNTIF함수를 이용하여 계산한 결과값을 구할 수 있습니다. 2,3,4학년은 채우기 핸들로 마우스를 하단으로 드래그하면 결과값이 나옵니다.

 

 


Posted by ,,.,

 

 

● 수식 표시하기


 

하단과 같은 자료가 있을 때 [금액] = [수량]*[단가]입니다. 수식입력이 잘 되었는지 안되었는지를 확인하기 위해서는 해당셀을 클릭하고 수식입력줄에서 수식을 하나하나 확인해야 할 것입니다. 이는 시간이 많이 소요되는 작업이고 또한 여간 번거로운일이 아닐 수 없습니다. 엑셀(EXCEL 2007)에서 이러한 수식이 맞게 작성되었는지를 수식표시를 통해서 간단하게 확인 할 수 있습니다.

 

▷ 엑셀(EXCEL)원본데이터

 

 

 

[수식탭 - 수식분석그룹 - 수식표시]

 하단과 같이 수식이 적용되어 있는 부분은 값이 아닌 수식으로 나타납니다. 이 수식에서 수식오류에 대한 부분을 확인할 수 있습니다. 만약 수식에 오류가 있을 경우에는 빨강색으로 표시됩니다. 이렇게 나타난 수식을 값으로 나타내고자 한다면 다시 [수식표시]를 클릭하면 해당수식이 사라지고 원래의 값이 나타납니다.

 

 

 

● 참조되는셀, 참조하는 셀 표시


 

엑셀의 시트에서 수식을 통하여 값이 계산될 때 [참조되는 셀]과 [참조하는 셀]을 화살표를 통하여 표시할 수 있습니다.

 

[참조되는 셀 : 분석하고자 하는 셀 선택(E3) - 수식탭 - 수식분석 그룹 - 참조되는 셀 추적]

 

하단과 같이 해당 셀(E3)의 값이 나오기 위해 참조되는 셀은 [C3]와 [D3]가 됩니다. 화살표를 통하여 연결이 되어 있고 참조되는 두 셀에는 파란 원점이 선과 연결되어 있습니다.

 

[참조하는 셀 : 분석하고자 하는 셀 선택(E3) - 수식탭 - 수식분석 그룹 - 참조하는 셀 추적]

 

하단과 같이 해당 셀(E3)의 값을 참조하여 계산되는 값은 [E18]셀이 됩니다.  화살표를 통하여 두 셀이 연결되어 있습니다. 이런 연결선은 문서 저장시자동으로 사라지거나 또는 [수식분석그룹 - 연결설 제거]를 클릭하면 사라집니다.

 

 

 

오류검사하기


 

엑셀에서 작업을 하다보면 하단과 같이 녹색삼각형(빨간동그라미 부분)이 표시되는 경우가 있습니다. 이는 그 부분의 셀에 오류가 있다는 의미입니다. 이번에는 이러한 오류를 찾고 그 오류에 대한 해결을 해 나가는 방법에 대해서 알아보도록 하겠습니다.

 

 

 

▷ 오류표시의 의미

 

하단과 같이 오류셀을 클릭하고 오류경고아이콘을 클릭하면 6개로 구분이 되어서 나타납니다

 

첫번째 : 오류에 대한 내용으로 해당 오류의 설명(0으로 나누기 오류)

두번째 : 오류에 대한 도움말(MS OFFICE 검색도움말 창이 나타나며 오류에 대한 설명이 자세히 나옵니다)

세번째 : 계산단계 표시(해당 셀에 적용된 수식이 나타납니다)

네번째 : 오류무시(오류무시를 클릭하면 오류표식(녹색삼각형)이 사라지며 오류로 인식하지 않습니다)

다섯번째 : 수식입력줄에서 편집(수식입력 줄에서 해당 오류를 편집, 수정할 수 있습니다)

여섯번째 : 오류검사옵션

 

 

 

위와 같이 셀에 나타난 오류표식을 클릭하여 하나하나의 오류에 대한 수정작업을 할수 있지만 데이터가 많을 경우에는 많은 시간이 걸릴 것입니다. 이러한 경우에 전체 오류를 표시한 후 단계적으로 오류를 제거하는 방법이 있습니다.

[수식탭 - 수식분석그룹 - 오류검사 - 오류검사]

 

[오류검사 창 : 이 오류에 대한 도움말, 계산단계표시, 오류무시, 수식입력줄에서 편집]  

 

 

 

만약 아래와 같이 [#NAME]오류가 있다면 이를 [오류검사]창에서 수정해 보도록 하겠습니다. [F6셀 오류]는 [잘못된 수식이름 오류]로 [수식에 인식할 수 없는 텍스트가 있습니다]라고 나타납니다. 이를 [수식입력줄에서 편집/클릭]하면

 

 

 

[수식입력 줄]에 그 해당 수식이 나타나며 이곳에서 수정하면 됩니다. 이 오류는 AVERAGE가 AVERAGA로 입력되어서 나타난 오류였습니다.

 

 

 

▷ 오류추적을 통해 오류의 연결선 나타내기

 

[수식-수식분석그룹 - 오류추적]을 하면 하단과 같이 오류의 해당되는 셀들이 선으로 연결이 됩니다. 이 연결선을 없애기 위해서 [연결설 제거]를 클릭하면 됩니다 

 

 

조사식창


 

조사식창을 이용하여 셀에대한 정보를 확인할 수 있습니다

 

[수식탭 -수식분석 - 조사식창 - 조사식추가 - 조사식추가상자 - 추가할 조사식입력 - 드래그해서 입력 - 조사식확인]

 

하단과 같이 통합문서 이름, 시트이름, 해당 셀, 값, 수식이 나타납니다. 이를 통해서 각각의 해당셀(수식이 입력되어 있는 셀)에 대한 정보를 확인할 수 있습니다.

 

 

 

순환참조


오류검사의 한 방법으로 순환참조를 통하여도 검사하고 해당 오류를 수정할 수 있습니다.

 

[수식탭- 수식분석-오류검사-순환참조 - 오류셀 클릭] 수식입력 줄에서 해당 오류내용 수정]

 

 

 

 

하단과 같이 [F8]값의 참조되는 범위가 잘못되어서 나타난 결과 입니다. 즉 [=AVERAGE(B8:E8)로 입력이 되어야 하는데 [=AVERAGE(B8:F8)]로 계산되는 자신의 셀이 포함되어서 나타난 오류입니다.

 

 

 

위의 수식입력줄에서 오류의 내용(수식오류)을 수정을 하면 하단과 같이 정확한 계산 값이 나옵니다.

 



Posted by ,,.,

엑셀(EXCEL) 함수 TIP 29 - (사용자정의 함수/visual basic 둘)

 

심한 독감에 걸렸습니다. 하루는 전혀 일어나지도 못하고 누워만 있었습니다. 몸이 천근만근이고 머리는 지끈지끈아팠습니다. 평상시 건강하고 안녕한 것, 그 자체만으로도 행복이라는 것을 다시한번 깨닫게 되었습니다. 직장에서 자기계발을 위해 철저한 시간관리와 꾸준함으로 생활해야 하겠지만 건강을 잃게되면 아무런 소용이 없습니다. 성공한 사람들 중 어느 한 사람도 건강에 심혈을 기울이지 않은 사람이 없다고 합니다. 오늘 하루 출근해서 편안마음을 동료와 차 한잔 하는 그 행복감을 느끼는 것이 미래의 성공한 나를 위해 지나치게 몰두하는 것보다 더 소중하다는 것을............

 

● 사용자정의함수 사용예 - 둘


 

하단과 같이 [포인트]에 대한 계산결과값을 구하고자 합니다. 이 식은[판매단가 = 판매단가 *0.15(단, 판매단가가 10,000,000원 이상일 경우 적용]입니다. 이것을 함수와 수식을 사용하여 적용할려면 수식이 복잡해 지고 작업방법도 어렵습니다. 이를 micro visual basic 창에서 사용자정의 함수로 정의 한 간단하게 값을 구하는 방법을 계산해 보도록 하겠습니다.

 

▶ 원본데이터

 

 

▶ [개발도구 - 코드그룹 - visual basic - 삽입 - 모듈]

 

 

 

Function 포인트(판매단가) : 포인트는 함수 (판매단가)는 인수
If 판매단가 >= 10000000 Then : 수식으로 판매단가가 10000000원보다 크거나 같을 경우
포인트 = 판매단가 * 0.15 : 포인트값은 판매단가 *0.15이다
End If
End Function

 

 

▶[구하고자 하는 셀값 선택(F3) - 수식 - 함수삽입 - 함수마법사(창) - 사용자정의 - 포인트 - 확인]

 

하단과 같이 함수마법사 창에서는 micro visual basic 창에서 사용자정의 함수로 정의한 [판매단가]가 있습니다. 이 판매단가를 선택해서 작업을 하면 됩니다.

  


▶[함수인수 창에서 작업 - [포인트]하는 [함수]에 [인수]인 [판매단가(E3)를 입력하고 - 확인]

  

 

▶ 결과값

 

하단과 같이 E3에 판매단가가 나타나며 이때 사용한 함수는 [=판매단가(D3)]입니다. E3셀을 선택하여 하단으로 드래그하여 채우기를 하면 전체 판매단가에 대한 값이 구해집니다. 포인트가 1000만원 이상인 값에 대해서만 추출이 되었으며 1000만원 이상인 금액에 대해서는 판매단가*0.15 가 적용되었음을 확인 할수 있습니다.

  

 


Posted by ,,.,

엑셀(EXCEL) 함수 TIP 29 - (사용자정의 함수/visual basic 하나)

 

엑셀에서(excel 2007) 자주사용하는 수식 또는 복잡한 수식 등은 사용자가직접 만들어서 사용하는 것입니다. 같은 수식을 계속해서 입력사용하는 것은 번거롭고도 까다로운 작업입니다. 이 작업을 사용자정의 함수를 이용해서 필요할때 사용하는 것입니다. 이 사용자정의 함수는  micro visual basic(마이크로소프트비주얼베이직) 창에서 작업을 합니다.

 

● 사용자정의 함수 사용 예


 

하단과 같이 엑셀에서 데이터시트가 있습니다. 판매단가를 구하기 위하여 수식을 사용해야 하며 적용하는 수식은 [판매단가 = 출그금액 + (출고금액*0.01)]입니다. 이렇게 수식을 적용하여 판매단가를 구할 수도 있지만 이를 micro visual basic 창에서 사용자정의 함수로 정의 한 간단하게 값을 구하는 방법을 계산해 보도록 하겠습니다.

 

▶ 원본데이터

 

 

 

▶ [개발도구 - 코드그룹 - visual basic - 삽입 - 모듈]

 

☞  개발도구 탭 삽입방법 [오피스단추 - 엑셀 옵션 - 기본설정 - 리본메뉴에 개발도구 탭 설정 - 확인]

 

 

 

 

Function으로 시작해서 마지막은 End Function으로 끝이 납니다. 그 사이에 함수를 기록하면 됩니다.

판매단가(출고단가)에서 [판매단가][함수]이며 [(출고단가)][인]입니다.
판매단가 = 출고단가 + 출고단가 * 0.1[수식]입니다.
End Function은 끝을 나타냅니다.

 

상기와 같이 MICROSOFT VISUAL BASIC 창에서 코드를 기록하고 다시 엑셀 데이터시트 화면으로 이동합니다. [엑셀표 클릭]

 

▶ [E3클릭 - 수식탭 - 함수삽입 - 함수마법사창 - 범주선택(사용자정의) - 함수선택(판매단가) - 확인]

 

 

 

▶ 함수마법사 창에서 작업

 

하단과 같이 함수마법사 창에서는 micro visual basic 창에서 사용자정의 함수로 정의한 [판매단가]가 있습니다. 이 판매단가를 선택해서 작업을 하면 됩니다.

 

 

 

 ▶ [함수인수창 - 출고단가(D3) - 확인]

 

 

 

▶ 결과값

 

하단과 같이 E3에 판매단가가 나타나며 이때 사용한 함수는 [=판매단가(D3)]입니다. E3셀을 선택하여 하단으로 드래그하여 채우기를 하면 전체 판매단가에 대한 값이 구해집니다.

 

 

[ VISUAL BASIC 및 매크로관련글]

 

 


Posted by ,,.,

엑셀(EXCEL) 함수 TIP 28 - (LARGE,SMALL,LMATCH,INDEX함수)

 

엑셀(excel 2007)에서 하단과 같은 데이터시트가 있을 때 오른쪽 처럼 상위 1,2,3,4 또는 하위 1,2,3,4위를 large함수 및 small함수를 이용하여 구할 수 있습니다. 또한 1,2,3,4위에 해당하는 학점을 match함수를 이용하여 추출할 수 있고 그에 해당되는 성명를 index함수를 이용하여 불러올 수 있습니다.

 

이처럼 함수의 사용용도를 잘 알고 있다면 원하고자 하는 값을 여러가지 함수를 사용하여 구할 수 있습니다. 따라서 함수는 지속적으로 사용하여서 그 사용용도와 방법을 완벽하게 익혀야 할 것입니다.

 

 

 

 

▶원본 데이터 값

 

 

 

▶ large 및 small함수의 사용 용도

 

=LARGE(array,D) : 데이터 내에서(array) D번째로 큰값을 구하라

=SMALL((array,K) : 데이터 내에서(array) K번째로 작은값을 구하라

 

 

● large 및 small 함수를 사용한 석차 구하기


 

▶ large함수

 

[F3:F6 블럭설정 - 수식탭  - 함수추가 - 통계 -largre- large함수인수창 - Array($D$17) - K(F3)- 확인]

 

Array는 셀참조 범위입니다. 평균학점이 있는 [D3:D15]입니다. 이 값은 나중에 채우기 핸들을 이용해야 하기때문에 기능키 F4를 눌어서 절대주소($D$15)로 변경을 해 주어야 합니다.

 

  

 

 

 ▶ small 함수

 

[F3:F6 블럭설정 - 수식탭  - 함수추가 - 통계 -small - small 함수인수창 - Array($D$17) - K(F3)- 확인]

 

Array는 셀참조 범위입니다. 평균학점이 있는 [D3:D15]입니다. 이 값은 나중에 채우기 핸들을 이용해야 하기때문에 기능키 F4를 눌어서 절대주소($D$15)로 변경을 해 주어야 합니다.

 

 

 

▶ large함수 및  small 함수를 이용한 결과값

 

하단과 같이 large함수를 이용한 평균학점 상위 1~4위까지의 결과와 small 함수를 이용한 하위 1위~4위까지의 결과값이 나왔습니다.

 



MATCH함수 사용


 

=MATCH(Look_value, look_array, match_type) (값, 범위, 방법)

  Lookup_value(구하고자 하는 값)

  look_array(참조 범위)

  match_type(방법)

 

 

 

 

 

MATCH함수는 데이터의 위치번호를 구하는 함수이다.  '구하고자 하는 값을 매치(연결)시켜라'라는 의미입니다.  즉 평균학점과 매치를 시켜서, 원본데이터의 평균학점이 몇번째 있느냐의 뜻입니다.

 

[G3:G6 블럭설정 - 수식탭  -찾기/참조영역 -match - match함수인수창 -  Lookup_value(G3)  look_array($D$3:$D$17) -  match_type(0- 확인]

 

 

MATCH를 이용한 결과값

 

사용한 함수[=MATCH(G3,$D$3:$D$17,0)]이며, 하단으로 채우기 핸들을 사용하면 됩니다. 수식의 의미는 '찾고자 하는 평균학점[4.9]이 원본데이터의 몇번째 있느냐'의 의미입니다. 이 MATCH함수는 INDEX함수를 구하기 위하여 필요한 결과값입니다.

 

 

 

INDEX함수 사용


= INDEX(array,row_num, columm_num) : 원본데이터의 범위(array)내에서 행번호와 열번호를 지정해서 특정셀값을 구라하는 의미이다.

 

 

하단에서 [I3]의 값을 구할 때 원본데이터(개인별 평균학점의 성명범위)에서 구하고자 하는 범위의 행, 열 번호에 있는 값을 구하라는 뜻이다.

 

 

 

[ 구하고자 하는 셀 범위 선택(I3:I6) - INDEX함수 선택 - 인수선택 창 - 상단클릭 - 확인]

 

 

 

▶[INDEX함수인추창 - array($D$3:$D$17),row_num(H3) - (ctrl + enter)]

 

하단과 같은 결과값이 나타난다. 즉 (홍수아)를 예를 들면 개인별 평균학점의 성명범위(좌측 데이터베이스)내에서 H3에 있는 값인 [6번]쩨 열번호에 있는 성명으로 반환하라는 뜻입니다.

 

 

 

 

▶ 사용된 함수

 

값을 구할 때 [구하고자 하는 범위 한셀(I3)을 선택하여서 값을 구하고 하단으로 드래그하여 채워넣을 수도 있지만 구하고자 하는 전범위(I3:I6)를 선택한 후 마지막 값을 구할 때 (Ctrl + enter)를 하면 전범위의 값이 동시에 구해진다. 위의 index함수 값은 (구하고자 하는 전범위 선택)과  (Ctrl + enter)를 해서 한번에 구한 값이다.

 

 

Posted by ,,.,

엑셀(EXCEL) 함수 TIP 27 - (INDEX 및 MATCH함수로 원하는 값 빠르게 찾기)

 

직장생활과 불금, 오늘을 불타는 금요일입니다. 모든 직장인들이 가장 좋아하는 날일 것입니다. 저의 경우에도 그렇습니다. 직장생활 중간단계에 접어드니 이젠 휴식도 필요하고 개인 취미생활도 하고 싶습니다. 직장이 내 모든 것을 책임 져 줄 줄알았던 신입사원때와는 달리 내 인생의 후반부는 스스로 준비하고 개척해 나가야 할 필요성을 느끼게 됩니다. 준비가 되어 있다면 더 좋은 조건이나 더 좋은 환경으로 이직할 수 있습니다. 개인준비를 철저히 해서 더 자유롭고 더 좋은 조건(시간이 많고 기업문화가 뛰어난 곳)의 직장으로 이직하고 싶은 생각이 많이 드는 요즘입니다. 나이가 들어도, 직급이 올라도 벗어날 수 없는 직장내 상사, 동료와의 인간관계의 틀......어렵습니다.

 

엑셀(excel 2007)에서 하단과 같이 데이터가 있을 때 개인별로 시험을 가장 빨리 응시한 일자와 그 일자에 응시한 사람을 찾고자 합니다. 이때 이용하는 함수가 INDEX함수와 MATCH함수의 중첩함수 입니다.  아래와 같이 자료가 적다면 하나하나 확인하면 금방 찾을 수 있지만 만약 해당 자료가 수페이지에 해당된다면 찾기란 여간 어려운일이 아닐 것입니다. 이때 INDEX와 MATCH 함수를 이용하면 금방 쉽게 찾을 수있습니다.

 

 

 

 

 

● MIN함수를 이용한 가장 적은 값 찾기


 

[찾고자하는 셀 선택(F18) - 수식 탭 - 합수라이브러리 그룹 -  통계 -  MIN]

▶ MIN 함수입력창에서 작업 [NUMBER(F3;F15) - 확인]

 

 

 하단과 같이 시험응시일이 가장 빠른(MIN) 시험응시일값이 추출됩니다.

 

● INDEX와 MATCH 함수의 중첩 사용


 

하단의 자료를 열어 놓으시고 따라서 해 보십시요

 


 

[찾고자하는 셀 선택(D18) - 수식 탭 - 합수라이브러리 그룹 -  찾기/참조영역  -INDEX - 인수선택(첫번째 창 선택 - 확인]

 

 

 

▶ INDEX함수 창에서 입력

 

ARRAY : 참조할 값이 있는 전체데이터의 범위 입력

ROW_NuM : ARRAY 내에서 위치한 행 번호 입력/MATCH 함수 중첩

 

 

 


▶ MATCH함수 창에서 입력

 

LOOKUP_VALUE : 찾으려고 하는 값(F18)

LOOKUP_ARRAY : 참조가 들어있는 연속셀 범위(F12:F19)

MATCH_TYPE : 0

 

 

위와 같이 입력하고 함수입력창에서 INDEX부분을 클릭하여

 

 

 ▶ INDEX함수 창에서 입력

 

COLUMN_NUM : ARRAY내에서 참조하고자 하는 행번호(2:이름이 들어있는 행 번호) 입력

 

 

 

● 결과값 추출


 

사용된 함수=INDEX(B2:G15,MATCH(F18,F2:F15,0))

 

 

Posted by ,,.,

엑셀(excel) 함수 TIP 26 - (반올림함수 MROUND, ROUNDUP,ROUNDDOWN)

 

엑셀(excel) 함수에서숫자를 반올림 하기 위해서 사용하는 함수가 ROUND함수이다. 반올림은 5를 기준으로 하며 5이상이면 반올림이 되고 5 미만이면 버려진다. MROUND함수는 원하는 숫자의 배수로 반올림을 한다. 예로 10을 기준으로 143을 반올림 하면 140이 되고 146을 기준으로 반올림을 하면 150이 된다.

 

● ROUND함수


 

ROUND(number,num_digits) : 수를 지정한 자릿수로 반올림 한다

ROUND(3.141592,2) 3.141592를 반올림 해서 소수이하 둘째자리까지 구하라(3.14)

 

 

=ROUND(E2:E6,0)

 

개인별 점수에 따른 평균점수가 있을 때 =ROUND(E2:E6,0)를 하면 (E2:E6,0)의 범위값에서 정한자릿수(0)에서 반올림하라.

[F2:F7범위선택 / =ROUND(E2:E6,0) 입력 / Ctrl + Enter]을 하면 하단과 같이 값이 계산되어 나온다. 소수점 이하의 값에서 반올림한 값들이 계산되었다.

 

 

=ROUND(G3:G9,-2)

 

분기별 과일 등 평균판매가격이 있을 때 소숫점 위 2째자리에서 반올림하여 구하라. 소수점 2째자리 이하에서 반올림한 값들로 백원단위까지 계산이 된다

 

 

 

MROUND함수


 

MROUND(number,multiple) : 수를 (number),정한배수(multiple)로 반올림 하라

MROUND(132,40) : 132를 40의 배수로 반올림하라.40의 배수는 40,80,120,160...에서 132에 가장 가까운 배수는 120

 

 

=MROUND(F2,10)

 

개인별 평균점수를 10의 배수로 반올림하라.

[G2:G6범위지정 / =MROUND(F2,10) 입력 / Ctrl + Enter]를 하면 개인별 점수가 10의 배수에서 가장 가까운 값으로 계산되어 나온다. 예를 들어 96.33333의 값에서 10의 배수는 10,20,.....90,100,110...)이르로 96에 가장 가까운 값은 100이다.

 



ROUNDUP함수


 

ROUNDUP(number,num_digits) : 수를 (number),정한자릿수(num_digits)로 올림 하라

ROUNDUP(42,687,-3) : 426,87을 -3자리에서 올림하라(43,000)

 

=ROUNDUP(F2,-3)

 

과일등 상품의 편균 판매가격이 있을 때 =ROUNDUP(F2,-3)를 하면 소수점 위 3째자리에서 올림을 하라

[G2:G6범위지정 / =ROUNDUP(F2,-3) 입력 / Ctrl + Enter]를 하면 값이 계산된다. 예를 들어 41,440이 있을 때 소수점 위 3자라에서 올림을 하면 42,000이 된다

 

 

 

ROUNDDOWN함수


 

ROUNDDOWN :(number,num_digits) : 수를 (number),정한자릿수(num_digits)로 내림 하라

ROUNDDOWN(132,40) : (42,687,-3) : 426.87을 -3자리에서 내림하라(42,000)

 

 =ROUNDDOWN(F2,-3)

 

과일등 상품의 편균 판매가격이 있을 때 =ROUNDDOWN(F2,-3)를 하면 소수점 위 3째자리에서 올림을 하라

[G2:G6범위지정 / =ROUNDDOWN(F2,-3) 입력 / Ctrl + Enter]를 하면 값이 계산된다. 예를 들어 41,440이 있을 때 소수점 위 3자라에서 내림을 하면 41,000이 된다. 이것은 큰 돈을 취급할 경우 또는 개인별 상품금액을 계산할 때 1000원 미만은 삭제하라는 것과 같은 의미로 많이 사용되는 함수이다.

 

 

Posted by ,,.,

엑셀(excel) Tip 25 - (틀고정 및 틀 나누기, 숨기기)

 

엑셀(excel)에서 시트(sheet)안에 행 또는 열머리글이 페이지를 초과할때 스크롤 하여 페이지를 하단 또는 우측으로 넘기다 보면 제목표시줄이 사라져서 그 해당 셀의 내용이 무엇인지를 모를 경우가 있습니다. 그때 다시 처음 제목표시줄로 되돌아가서 수고해야 하는 번거러움이 있습니다. 하단에서 보시는 것처럼 2행머리글에 제목들이 주황색으로 되어 있는데 하단부위의 값들을 확인하기 위해서 아래방향으로 스크롤 할 경우....

 

이렇게 보일 경우 숫자로만 채워져 있어서 무엇을 의미하는지 전혀 알 수가 없습니다. 일반적으로 첫머리행에는 해당 값에 대한 내용(제목)이 표시가 됩니다. 물론 열머리행에도 마찬가지입니다. 이때 틀고정을 통해서 첫머리행,열값들은 현재 본인이 보는 화면에서 사라지지 않고 언제나 표시될 수가 있도록 할수가 있습니다.

 

 

▶ 원본자료(한 시트(sheet)많은 행열머리글의 경우)

 

* 하단의 2열 첫머리행에는 [구분, 월지급액, 월간운영비용, 연간정액 감가상각]등이 표시가 되어 있습니다.

 

 

▶ 아랫방향으로 스크롤시

 

2행에 있는 제목표시줄이 사라져서 각각 셀의 내용이 무엇인지를 전혀 알 수 가 없습니다. 이때 간단하게 제목표시줄을 고정해 주면(틀고정) 제목표시줄은 그대로 있고 하단부위의 값들이 위로 올라옵니다. 

 

 

틀고정 후의 모습입니다. 상방향으로 스크롤 했서 3~26행까지의 내용은 사라졌지만 2행에 있는 제목표시줄(주황색)은 그대로 남아 있어서 각각 셀 내용들이 무엇을 나타내는지를 알수 있습니다.

 

 

1. 틀고정 방법 : (보기 - 창 - 틀고정)


 

 

 

▶ 틀고정 3가지 방법

 

  • 틀고정 : 현재 선택 영역을 기준으로 워크시트의 나머지 부분을 스크롤 하는 동안 행과 열 표시되도록 함
  • 첫 행 고정 : 워크시트의 나머지 부분을 스크롤 할 때 첫 행이 표시되도록 합니다
  • 첫 열 고정 : 워크시트의 나머지 부분을 스크롤 할 때 첫 열이 표시되도록 합니다.

 

 

 

2. 틀고정 하기


 

(본인이 원하는 해당셀 선택(M33) - 틀고정 클릭)을 하면 하단과 같이 마우스휠을 아래방향으로 하면 열의 내용들이 윗방향으로 움직이지만 틀 고정된 부분(M33의 윗부분([제목표시줄])은 고정되어 있고 열의 내용들만 윗부분으로 움직여서 하단부위의 내용들을 확인할 수 있습니다. 또한 행 부분으로도 고정이 되어 있어서 (셀을 선택 후 키보드의 화살표(→)를 누르면 해당 행의 항목들이 좌측방향(←)으로 움직이며 사라지지만 첫 행(구분)을 고정되어 있는 상태입니다.

 

 

3. 첫 행고정/첫 열고정하기


 

(첫행고정/첫열고정)을 클릭하면 셀을 선택하지 않아도 첫번째 행과 첫 열이 고정됩니다. 첫 열 고정시에는 화살표를 우측방향으로 하면 첫열은 고정된체 셀의 내용은 좌측이로 이동하고, 첫 행 고정시에는 화살표(마우스)를 아래방향으로 하면 셀의 내용은 윗방향으로 이동합니다.

 

 

4. 틀고정 취소


 

틀이 고정되어 있는 경우에 다시 취소하고자 하면 [보기 - 틀고정 - 틀고정취소]하면 됩니다.

 

 

5. 틀 나누기


워크시트 보기를 포함하여 크기를 조정할 수 있는 여러창으로 분할합니다. 이 기능을 사용하면 워크시트에서 멀리 떨어져 있는 여러부분을 한번에 볼수 있습니다. 나누기 하단부분의 숨기기를 클릭하면 셀의 내용이 사라집니다(현재의 창을 숨기고자 할 때 사용하는 기능). 다시 나타내고자할때 숨기기 취소를 클릭하면 됩니다.

 


Posted by ,,.,

엑셀(excel)함수 tip 25 - (indirect 및 vlookup함수로 원하는 값 추출하기)

 

엑셀(excel)에서 indirect함수는 텍스트 형태의 셀을 참조하여 실제 셀 참조로 바꾸는데 사용합니다. 즉 셀에 입력되어 있는 이름을 셀참조로 하여 사용할 때 사용합니다. 예를 들어 indirect(A6)에서 A6셀에 값이 있으면 그 값을 참조하고 셀범위(D2:D6)가 있으면 셀범위 참조, "판매현황"이라는 이름정의가 있으면 이름정의를 참조합니다.

 

 

 

▶ indirect함수 사용 예

 

하단에서 =INDIRECT(G5)는 G5에 있는 셀 B5를 참조하라는 의미입니다. 따라서 B5에 있는 값이 에어컨이므로 결과값이 에어컨으로 표시가 됩니다.

 

 

▶ 원본엑셀 함수

 

하단에서 2011년도와 2012년도의 데이터시트가 있습니다. 이 두 데이터를 참조로 하단부위의 [제품판매현황]에서 [해당연도]와 [품목]을 입력시에 수량. 단가, 금액이 자동으로 생성되도록 하는 법을 기술하겠습니다. 이때 이용하는 함수가 INDIRECT함수와 VLOOKUP함수입니다.

 

 

 

● 이름정의하기


 

하단과 같이 2011년도와 2012년도의 셀 값[품목,수량,단가,금액]에 대한 부분을 이름정의를 해 보도록 하겠습니다. (관련글 바로가기 ( 이름정의를 통한 빠른 수식계산) 이름정의를 하는 것은 하나하나의 셀을 선택할 필요 없이 정의된 이름을 입력하여 수식을 계산할 수 있기 때문입니다. 하단에서 (B4:E13)을 (_2011년도)로 (G4:J13)을 (_2012년도)로 정의하였습니다. 연도 앞에 _을 한 이유는 이름정의 규칙때문에 숫자로 시작할 수 없기 때문입니다.

 

☞ 이름정의 규칙

  • 이름은 한글, 영어, 밑줄 중 하나로 시작한다
  • 영어의 대/소문자를 구분하지 않는다
  • 이름에는 공백을 사용할 수 없다
  • '셀 참조주소'와 동일한 이름을 사용할수 없다
  • 이름은 최대 255자까지 사용할 수 있다

 



● 데이터 유효성 설정하기


데이터의 유효성[관련글바로가기 : 데이터유효성검사/유효성설정은? ] 을 설정해 놓은 셀을 클릭시에 역삼각형이 생성이 되면서 해당목록을 선택할 수 있는 값들이 생성이 됩니다. 하단에서처럼 [해당 셀 선택 -엑셀편집메뉴의 데이터탭 - 데이터유효성 검사]를 클릭하면 데이터유효성 창이 생성이 됩니다. [제한대상]은 [목록]으로, [원본]은 [2011년도,2012년도]로 하고 [확인]을 합니다.

 

 

 

하단은 품목에 해단되는 셀에 대한 값을 데이터유효성으로 설정하는 과정입니다.[해당셀 선택 - 데이터유효성 창 - 제한대상(목록) - 원본($B$4:$B$13) - 확인]을 하면 됩니다.

 

 

 

데이터유효성을 설정한 결과를 보시면 하단의 품목 옆셀을 클릭시 우측의 역삼각형이 생성이 되면서 목록들이 생성이 됩니다. 이렇듯 유효성설정으로 하나하나의 값을 입력하지 않아도 역삼각형을 선택해서 해당값을 클릭하면 됩니다.

 

 

 

● INDIRECT, VLOOKUP함수 중첩하여 값 구하기


 

[C19셀 선택 - =VLOOKUP(C18,INDIRECT("_"&C16),2,0) - ENTER]를 하면 해당값(95)이 생성이 됩니다. 이 함수에서 보시는 것처럼 두개의 함수가 중첩되어 사용되었습니다. 의 의미를 해석해 보면 INDIRECT("_"&C16)의 의미는 INDIRECT(_2012년)의 의미로서 이름정으로 되어 있는 [_2012년도]의 전체범위를 참조하라는 의미입니다.

 =VLOOKUP(C18,INDIRECT("_"&C16),2,0)은 찾으려는 값은 C18이고, 이름정의된 [_2012년도]를 참조하여, 두번째열(수량)과 정확히 일치하는 값을 찾으시오 라는 뜻입니다. 따라서 C18값을 품목(팩스)로 선택하면 2012년도 2번째 열의 (95)가 결과값으로 나옵니다.

 

 

 

▶ 연도와 품목선택시 자동으로 값 생성

 

위와 같이 [데이터유효성 설정]과 [INDIRECT함수와 VLOOKUP]함수두가지를 이용하면 하단과 같이 간단한 클릭만으로 해당값들이 추출이 되어서 나타납니다. [연도]와[품목]의 원하는 값들을 선택을 하면 하단의 값들은 자동으로 생성(추출)이 됩니다.

 

 

Posted by ,,.,

엑셀(excel) Tip 24 - (셀의 내용 변경, 행의 데이터 삭제하기)

엑셀에서는 특정 셀의 내용을 일괄적을 바꿀수가 있습니다. 또한일정 행의 데이터를 바꾸기 기능을 사용하여 삭제할 수도 있습니다. 수많은 분량의 자료를 엑셀(excel)로 작업을 했는데 그 중에 어떤 단어를 잘못 입력하였을 경우 하나하나 찾아서 바꿔야 한다면 많은 시간이 소요될 것입니다. 이때 바꾸기 기능을 이용하면 손쉽게 단 한번에 변경이 가능합니다.

 

하단과 같이 학교별, 학과별, 이름별 점수가 있다고 가정할 때 국어국문학과를 국어교육과로 입력해야 하는데 잘못입력을 했습니다. 이때 국어국문학과를 국어교육과로 바꿔야 합니다. 이렇게 한곳에 모아있다면 쉽게 변경이 가능하겠지만 여러 셀에 흩어져 있다면 하나하나 바꾸는데 시간이 걸릴 것이지만 엑셀에서는 간단하게 변경이 가능합니다. 

 

 

1. 특정 셀의 내용 바꾸기 : 찾기바꾸기 단축키(Ctrl + H)


 

(홈텝 - 찾기 및 선택 - 찾기/바꾸기)

 

찾기 바꾸기에서 찾을 내용을 (국어국문학과) 바꿀 내용을 (국어교육과)로 한 후 모두바꾸기를 하면 하단과 같이 국어교육과로 전부 바꾸어져 있습니다.

 

 



2. 삭제하기 - ( 빈 공란으로 하기)


  

먼저 찾을 내용을 (국어교육과) 바꿀 내용을 (빈 공란)으로 하여 모두바꾸기를 선택을 하면 하단과 같이 국어교육과의 셀 항목이 전부 공란으로 나타납니다.

 

 

 

3. 삭제하기 : (이동옵션 단축키 / F5)


 

(편집 - 찾기 및 선택 - 이동 - 하단의 이동옵션 선택)을 하고 (빈셀)을 클릭하고 확인을 합니다.

 

 

 

(홈텝 - 셀 - 삭제) 또는 삭제메뉴 단축키 (Ctrl, +,-)를 누르면 아래와 같이 삭제 메뉴나 나타나며 행 전체를 클릭하고 확인을 하면 국어교육과 항목 전체가 삭제됩니다.

 


엑셀(excel) Tip 22 - (엑셀 자동고침옵션 및 하이퍼링크 설정방법)

 

Posted by ,,.,

 

 

 

다음은 엑셀 2007의 피벗 테이블로 수행할 수 있는 대표적인 일들입니다.

 

  •  기초데이터를 구성하고 있는 필드(항목)의 위치를 조합하여 다양한 집계표 구성할 수 있음
  •  대량의 데이터에 대해서도 사용자가 원하는 방법으로 편리하게 분석할 수 있음
  •  피벗테이블을 구성하는 각 필드에 여러 조건을 지정하여 그룹별 집계를 할 수 있음
  •  피벗테이블의 특정항목을 기준으로 필터링하거나 정렬할 수 있음
  •  사용자가 보기옵션을 조정하여 데이터의 수준을 확장 또는 축소할 수 있음

 

엑셀에서 피벗테이블을 사용하여 복잡한 데이터를 쉽게 정렬 및 요약하고 세부정보를 드릴다운 할 수있습니다. 필터를 통하여서도 원하는 결과를 얻을 수도 있지만 필터로서 하지 못하는 부분을 쉽게 피벗테이블에서 데이터정리를 할 수 있습니다. 하단과 같이 제품별 고객에 따른 분기별 판매량이 나와있는데 셀의 항목(세로방향)이 너무나 많아서 한눈에 들어오지 않습니다. 이때 제품별로 분기당 판매량을 볼려고 할 때 피벗테이블을 이용하여 멋지게 편집할 수 있습니다.

 

 

 

 

1. 피벗테이블삽입

 

[엑셀 시트필드의 하나의 값 선택 -  삽입 - 피벗테이블]

 

 

 

2. 추출하고자 하는 범위 선택

 

하단과 같이 필드의 하나의 셀을 선택(A1)해서 우측과 하단으로 영역을 확장하면(피벗하고자 하는 범위 : E11)까지 선택을 하면 됩니다. 하단부위의 빠란 박스(까만 점선으로 표시됨) 피벗테이블 만들기에 표 또는 범위선택 란에 선택된 범위(빨간 네모박스)나타납니다.

 

 

 

상단에서 확인을 누르면 하단과 같이 시트 테이블의 원본데이터 좌측에 시트(Sheet4) 가 생성됩니다. 이 새로운 시트에 피벗한 결과값이 나타납니다.

 

 

 

좌측의 (피벗테이블 보고서에서 작업하려면 이영역을 클릭하십시오) 부분을 클릭하면 엑셀 시트(sheet)의 우측에 피벗테이블 필드목목이 생성이 됩니다. 여기에서 보고서에 추가할 필드를 선택하여 하단부위의 보고서 필터 열레이블, 행레이블, 합계값으로 적정하게 배치하면 원하는 결과값을 얻을 수 있습니다.

 

         

 

 

    

3. 피벗테이블 보고서 작성

 

1. (우측 상부)보고서에서 추가할 필드중 제품을 클릭하여 (하단부위의 행 레이블)에 위치를 시킵니다. 이부분은 아래의 좌측에 보이는 것처럼 (좌측에)판매된 상품목록이 행방향으로 추출이 됩니다.

2. (우측 상부)보고서에서 추가할 필드 중 1분기, 2분기, 3분기값을 선택하여 하단부의의 ∑ 값 부분에 위치를 시키면 (좌측에)분기별 판매값들이 더해져서 추출됩니다.

우측의 보고서에 추가할 필드선택을 체크하면 제 자리를 찾아 가며 원하는 결과의 값에 따라 다시 삭제하거나 자리를 이동시킬 수가 있습니다.

 

 

 

하단은 피벗테이블 보고서 결과 값에 표 스타일을 적용하여 예쁘게 꾸민 모습입니다.

 

 

 

만약 납품회사항목(빨간 박스)이 추가되었을때 (회사별, 제품별) 판매실적을 피벗테이블하는 방법을 알아보겠습니다

 

 

 

(납품회사)를 클릭하여 (보고서 필터)부분에 위치케 합니다. 이 보고서 필터 부분은 대 항목으로서 납품회사별로 추출할 수 있는 항목입니다. 그리고 나머지 부분은 앞에서 한 것처럼 동일하게 작업을 하면 됩니다. 앞부분과 다른 점은 보고서 필터 부분을 사용한 부분입니다.

 

 

 

피벗테이블 보고서를 작성하고 난 후 필터부분(빨간 동그라미)을 클릭하면 식품별 선택할 수 있도록 나타납니다. 여기에서 본인이 원하는 식품을 선택해서 확인을 하면 그 식품에 대한 값만 추출됩니다.

 

 

 

보고서 필터(식품회사별 판매실적)를 이용한 한 결과 값에 표 스타일을 적용했습니다.

 

 

 

보고서 필터 적용 예(학년별 총 점수)

 

하단과 같이 학년별 성명별 점수가 있을 때 학년별, 성명별 점수를 추출하기 위해서 피벗테이블을 적용해 보겠습니다.

 

 

 

 하단과 같이 조건을 주면(학년 - 보고서 필터, 성명 - 행레이블, 과목 - ∑ 값)

 

 

 

아래와 같이 데이터가 추출이 되며 필터(빨간동그라미)부분을 클릭하여 원하는 학년의 결과값을 나타나게 할 수 있습니다.

 

 

 

 피벗테이블관련글

 

Posted by ,,.,

엑셀(excel) Tip 22 - (엑셀 자동고침옵션 및 하이퍼링크 설정방법)

 

● 엑셀(excel)옵션기능



엑셀(excel)에서 기본적인 항목들을 설정할 수 있습니다. 엑셀화면 좌측 최 상단의 [오피스 단추 클릭 - 하단의 엑셀 옵션 클릭) 엑셀 2007에서 옵션기능이 어디에 있는지 몰라서 헤매이는 경우가 때로는 있습니다. 

 


 

●  다양한 엑셀(excel)옵션기능



엑셀 옵션의 다양한 기능(기본설정, 수식, 언어교정, 저장, 고급, 사용자지정, 추가기능, 보안센터, 리소스, 맞춤법검사, 자동복구저장간격, 작업표시줄, 표시할 최근문서 수, 빠른 실행 도구모음) 이곳에서 엑셀에 대한 다양한 기능을 설정하고 유익하게 작업할 수 있습니다.

 

● 다양한 엑셀(excel)옵션기능



이 자동고침기능을 설정하면 특정 문자를 자동으로 변경할 수 있습니다. 예를 들어 []을 한글로 입력하변 [excel]로 자동을 고쳐지는 설정을 할 수 있습니다. 이 자동고침설정은 [엑셀 옵션 - 언어교정 - 자동고침옵션]의 순서에 따라 하시면 됩니다.

 

 

아래와 같이 문장의 첫글자를 대문자로(the →The)에 체크하면 영어를 입력할 때 첫 글자가 대문자로 바뀝니다. 특정문자를 변경하는 방법은 빨간박스의 입력란에 변경하고자 할 글자(문자)를 입력하고 결과 항에 변경 후의 글자(문자)를 입력하면 됩니다. 예로 tel 을 영어로 입력하면 자동으로 전화기 모양으로 바뀝니다(☎)
 

 

 

 

☞ 엑셀 → excel로 자동고침하기


 

하단의 [다음목록에 있는 내용을 자동으로 바꾸기]를 [체크]한 후에 [입력]에는 [엑셀]을, [결과]에는 [excel]을 기록한 후 [확인]을 하면 하단부위에 자동고침옵션이 추가됨을 확인 할 수 있습니다. 하단에서 가장많이 사용되는 자동고침옵션이 기본적으로 설정되었습니다.

 

  • 처음 두문자가 대문자인 경우 고침(THe → The)
  • 문장의 첫 글자를 대문자로(the → The)
  • 요일을 대문자로(monday →Monday)
  • 한/영자동고침

 


 

하단과 같이 한글로 엑셀을 입력하면 자동으로 엑셀로 변경이 됩니다.

 

 

●  하이퍼링크 자동설정하기


 

예를 들어 엑셀에 인터넷 사이트 주소를 입력할 경우에 그 주소가 자동으로 줄이 그어지면서 색이 파란색으로 바뀝니다. 입력후 그 주소를 클릭하면 해당 인터넷사이트로 연결이 됩니다. 이는 하이퍼링크 자동설정이 되어 있기 때문입니다. 이 설정을 해놓지 않으면 인터넷 주소를 입력하더라도 하이퍼링크기능을 적용할 수 없습니다.

 

[자동고침 창 - 입력할때 자동서식 - (인터넷과 네트워크 경로를 하이퍼링크로 설정 작업할 때 적용) 체크-  확인]

 

제 블로그 주소를 입력하면 파란색으로 바뀌면서 밑줄이 쳐진 것을 볼수 있습니다. 이 부분을 클릭하면 하이퍼링크가 적용되어 바로 제 블로그로 넘어가게 됩니다. 따라서 하이퍼링크기능을 적용시에 자동고침옵션에서 이렇게 설정을 해 놓아야 합니다.


Posted by ,,.,