종합연습예재(2)

지난 번에 이어서 같은 연습예제를 가지고 COUNTIF와 VLOOKUP 함수를 설명드리겠습니다.   (COUNTIF 함수) 지난 번에 설명드린 IF 함수의 의미를 되새겨보겠습니다.   'IF' 는 영어로 "만약 ~~하다면 ~~하다" 는 의미입니다. 이 수식의 의미는 이렇습니다. "만약 D4 셀이 ""이라면 J4 결과값은 "" 이고, 아니면 J4 결과값은 O4 셀을 참조한다는 것입니다.   이제 눈치를 채시겠지요? IF 앞에 나오는 명령을 수행하는 것입니다. COUNT는 숫자를 헤아리는 것 ... IF에서 제시한 조건에 해당하면 숫자를 세라는 명령입니다. 셀 J37에는 이렇게 입력되어 있습니다.(아래 그림 참조)   '=COUNTIF(I4:I33,"<101")' 'I4'부터 'I33'의 범위 안내 101보다 작은 숫자가 들어있는 셀의 갯수를 세라는 의미입니다. 테스트를 해보실까요 ?  "<101"을 "<301)"로 바꾸어보셔요. 결과값이 '8'로 바뀔 것입니다. 8개가 있다는 의미죠.   이런 원리는 자주 사용하는 다른 함수에도 활용됩니다. SUMIF 함수는 조건에 해당하는 셀의 숫자를 합계하라는 함수명령입니다. ... 사용법은 똑 같습니다.   연습해볼까요? 셀 J47에 이렇게 입력해 보셔요. '=SUMIF(I4:I33,"<301")  ... 결과값이 2001이 나오면 제대로 된 것입니다. 계산기로 301보다 작은 사용량을 찾아서 합계해보십시오. 2001이 나올 것입니다.     <img src="https://t1.daumcdn.net/cfile/cafe/156CE54C4FCF63DB08" class="txc-image" style="FLOAT: none; CLEAR: none" actualwidth="720" border="0" hspace="1" vspace="1" width="720" id="A_156CE54C4FCF63DB08466B"/>   필요한 곳을 찾아서 활용을 해보시면 매우 유익한 함수입니다.   (VLOOKUP 함수) 쓸모있기는 하지만 흔하게 사용되지는 않습니다. 약간 고급에 속하는 함수라고 보시면 됩니다. 처음 액셀을 익히는 분에게는 지나칠 수도 있으나 기왕 나왔으니 설명을 드릴 수 밖에 없습니다.   전기요금은 단계별 계산방법이 좀 까다롭습니다. 단계별로 기본요금, 사용요금, 부가세, 전력기금, 거리에다 TV수신료까지 ... 액셀로 계산수식을 만들 수는 있지만 복잡해서 오히려 부담이 됩니다. 그래서 한전에서 만든 조견표를 사용한 것입니다. 기왕에 만든거 연습차원에서 VLOOKUP 함수를 제대로 익히도록 조견표 금액을 그대로 읽어오도록 해봤습니다.   우선 VLOOKUP 함수를 사용하는데 있어선 대상이 되는 시트를 별도로 만드는 것이 좋습니다. 그래야 행이나 열을 마음대로 삽입하거나 삭제할 수 있기 때문입니다. 예제에서는 [조견표]라는 시트가 있습니다. 한전에서 다운받은 조견표 원본 그대로입니다.   L4 셀에는 이렇게 입력되어 있습니다. '=IF(I4=0,0,IF(C4="","",VLOOKUP($I4,조견표!$A$3:$E$1002,2)))'   우선 IF 함수로 사용량( I4 셀)이 0이면 전기요금도 0으로 했습니다.    다음 IF 함수로 입주일(C4 셀)이 비어있으면 전기요금을 계산하지 않도록 했습니다. 승강기전기료는 별도로 계산하기 때문이죠. VLOOKUP 함수의 사용구조는 이렇게 되어있습니다.  VLOOKUP(찾을 값, 찾을 범위, 인덱스)  ....  VLOOKUP($I4,조견표!$A$3:$E$1002,2) 찾을 값   : I4에 있는 값 찾을 범위: 조견표시트의 A3부터 E1002까지 인덱스    : 2  (참고: 찾을 범위인 '조견표A3:E1002'에서 두번째 열이라는 뜻입니다.)     <img src="https://t1.daumcdn.net/cfile/cafe/12736F4D4FCF642A14" class="txc-image" style="FLOAT: none; CLEAR: none" actualwidth="1024" border="0" hspace="1" vspace="1" width="1024" id="A_12736F4D4FCF642A142F88"/>   ※ $ 에 대해서 의문을 가지신 분은 없으신가요?     $은 참조표시할 때 바뀌지말라고 대못박아 놓는 것입니다.     같은 수식을 여러 셀에서 사용할 때는 복사하게 되는데 복사해도 바뀌지않도록 고정시키는 방법입니다.     다음 번에 셀 복사, 이동 이런거 설명할 때 다시한번 설명드릴 것입니다.   L4, M4, N4 셀에 입력된 내용을 비교하여 보시기 바랍니다. L4 : VLOOKUP($I4,조견표!$A$3:$E$1002,2) M4 : VLOOKUP($I4,조견표!$A$3:$E$1002,3) N4 : VLOOKUP($I4,조견표!$A$3:$E$1002,4)   그리고 조견표 시트에서 해당되는 금액과 비교하여 보시기 바랍니다. 101동 101호 사용량 365㎾ ... 조견표 368행 사용량 365, 전기요금 55,445  부가세 5,545  전력기금 2,050 청구액 63,040     <img src="https://t1.daumcdn.net/cfile/cafe/125D964C4FCF646827" class="txc-image" style="FLOAT: none; CLEAR: none" actualwidth="763" border="0" hspace="1" vspace="1" width="763" id="A_125D964C4FCF6468273D56"/>   좀 어렵죠 ? 아는 걸 설명하기도 이렇게 어려운데 모르는 걸 몇줄 설명으로 쉽게 이해한다면 천재겠지요. 제 설명을 프린트해서 연습예제 파일에 입력된 걸 하나씩 확인해보시고, 스스로 만든 시트에 입력하여 테스트해보시면서 하나씩 바꾸어 보면 됩니다.   연습과제 : 예제는 주택용 전기요금(저압) 조견표를 사용했습니다.                 주택용 전기요금(고압) 조견표를 사용해보셔요.                 또 부과금액을 전기요금, 부가세, 전력기금 으로 나누지 말고 그냥 전기요금 하나로만 표시해보셔요.   이 연습과제만 해결하신다면 VLOOKUP 함수를 폭넓게 활용하는 고급액셀에 발을 들여놓는 것입니다. 끝까지 읽어주셔서 감사합니다.   <a href='javascript:fileFilterViewer("http://cfile272.uf.daum.net/attach/164BF34F4FCF647F174084", "/cfile272/16/4BF34F4FCF647F174084", "연습예제(전기요금).xlsx", "grpid%3D1MmrI%26fldid%3DloM9%26dataid%3D49%26fileid%3D4%26regdt%3D20120606230914&url=http%3A%2F%2Fcfile272.uf.daum.net%2Fattach%2F164BF34F4FCF647F174084")'><img src="https://t1.daumcdn.net/daumtop_deco/icon/icon.hanmail.net/editor/p_xls_s.gif?rv=1.0.1" border="0" alt="첨부파일" class="vam"/> 연습예제(전기요금).xlsx</a>