- 최초 작성일: 2001-03-02
- 최종 수정일: 2026-09-29
- 조회수: 26 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: 전력요금 계산하기
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
질문 하나
안녕하십니까? 평소에 무척 많은 도움을 받고 있습니다. 어떻게 사례를 해야할 지… 까막눈이던 제가 많은 나이에 시작하고 보니 어려움도 많았습니다만, 선생의 올린 글로 장족의 발전을 하게 되었습니다. 하다보니까 자꾸만 궁금한게 생겨나서 열심으로 미쳐 갑니다. 그런데 아직은 걸음마 단계라서 혼자 공부하기에는 문제에 봉착하는 일이 많아서 한수만 부탁 올리겠습니다. 부디 바쁘신 가운데에도 답을 해주시면 은혜 잊지 않겠습니다. 몇일을 혼자 하려니 도저히 길을 찾지 못하고 있습니다. 이 불쌍한 중생을 위해 한 수 꼭 부탁 드립니다. 다름이 아니오라, 아래와 같은 예제에서 답(????)에 대한 수식을 구하고자 합니다. -다음-
| 번호 | 이름 | 사용량 | 기본요금 | 전력량요금 |
| 1 | 김동길씨 댁 | 87kWh | (????) | (????) |
| 2 | 김대중씨 댁 | 123kWh | (????) | (????) |
| 3 | 김영삼씨 댁 | 255kWh | (????) | (????) |
| 4 | 전두환씨 댁 | 316kWh | (????) | (????) |
| 5 | 노태우씨 댁 | 405kWh | (????) | (????) |
| 6 | 민중달씨 댁 | 580kWh | (????) | (????) |
| - 조건 1 - | - 조건 2 - |
| 기본요금(호당) | 전력량요금(원/kWh) | |||
| 100kWh 이하 사용 | 390 | 처음 50kWh까지 | 34.5 | |
| 101 ~ 200kWh | 850 | 51 ~ 100kWh | 81.7 | |
| 201 ~ 300kWh | 1500 | 101 ~ 200kWh | 122.9 | |
| 301 ~ 400kWh | 3590 | 201 ~ 300kWh | 177.7 | |
| 401 ~ 500kWh | 6750 | 301 ~ 400kWh | 308 | |
| 500kWh 초과 사용 | 11980 | 401 ~ 500kWh | 405.7 | |
| 500kWh 초과 사용 | 639.4 |
기본요금과 전력량요금의 조건이 다릅니다. 그러면 답을 기다리겠습니다. 부디 환절기에 건강하십시오.
세상에서 가장 불행한 사람은 어떤 사람일까요? 그것은 미치지 않은 사람입니다(죄송합니다 이런 과격한 표현을 써서… ^^;). 이 세상을 살면서 어느 한 가지에 미쳐보지 않은 채 생을 마감하는 사람이야말로 불행한 사람이지요.
어떤 일에도 미쳐보지 못한 채, 樂이라고는 그저 돈쓰는 것 밖에 모르는 사람도 마찬가지 입니다. 이 세상은 참으로 공평하다는 생각을 새삼스레 하게 됩니다. 여러분들도 EXCEL과 VBA가 아니더라도 꼭 한 가지 이상의 일에 미쳐보시기 바랍니다(그렇다고 해서 고스톱, 도박, 음주가무 같은 것은 말고… ^^).
그건 그렇고… 위의 질문하신 분의 과제는 어떻게 하면 해결할 수 있을까요? Vlookup 함수로는 힘들 것 같고… Index 함수와 Match 함수를 활용하면 가능합니다.
- 최신 글을 불러오는 중...
전력요금 계산하기
핵심 요약
- 사용량 구간마다 기본요금과 전력량요금이 달라지는 표에서는 INDEX와 MATCH를 조합해 해당 구간의 요금을 찾아올 수 있습니다.
- 사용량 기준표를 적절한 순서로 배치하고 MATCH의 유형을 지정하면 정확히 일치하지 않는 사용량도 가장 가까운 요금 구간에 연결할 수 있습니다.
- 최저·최고 구간은 IF로 별도 처리하고 INDEX/MATCH 결과와 합산해 각 가구의 전체 전력요금을 계산합니다.
참고: 이 강좌의 요금 수식과 계산 결과에는 오류가 있어 다음 강좌(X0191)에서 수정되었습니다. 실제 적용할 때는 X0191의 정정 내용을 함께 확인하시기 바랍니다.
(표 1) 전력사용요금계
| 이름 | 사용량 | 기본요금 | 전력량요금 | 전력요금계 |
| 김동길씨 댁 | 87 | =SUM(D69:E69) | ||
| 김대중씨 댁 | 123 | =SUM(D70:E70) | ||
| 김영삼씨 댁 | 255 | =SUM(D71:E71) | ||
| 전두환씨 댁 | 316 | =SUM(D72:E72) | ||
| 노태우씨 댁 | 405 | =SUM(D73:E73) | ||
| 민중달씨 댁 | 580 | =SUM(D74:E74) |
| (표 2) 기본요금 | (표 3) 전력량 요금 | |||
| 사용량 | 요금 | 사용량 | 요금 | |
| 501 | 11980 | 501 | 639.4 | |
| 500 | 6750 | 500 | 405.7 | |
| 400 | 3590 | 400 | 308 | |
| 300 | 1500 | 300 | 177.7 | |
| 200 | 850 | 200 | 122.9 | |
| 100 | 390 | 100 | 81.7 | |
| 50 | 34.5 |
(표 4) 전력사용요금계(완성)
| 이름 | 사용량 | 기본요금 | 전력량요금 | 전력요금계 |
| 김동길씨 댁 | 87 | =IF(C90>$B$79,$C$79,INDEX($C$79:$C$84,MATCH(C90,$B$79:$B$84,-1))) | =IF(C90>$E$79,$F$79,INDEX($F$79:$F$85,MATCH(C90,$E$79:$E$85,-1))) | =SUM(D90:E90) |
| 김대중씨 댁 | 100 | =IF(C91>$B$79,$C$79,INDEX($C$79:$C$84,MATCH(C91,$B$79:$B$84,-1))) | =IF(C91>$E$79,$F$79,INDEX($F$79:$F$85,MATCH(C91,$E$79:$E$85,-1))) | =SUM(D91:E91) |
| 김영삼씨 댁 | 255 | =IF(C92>$B$79,$C$79,INDEX($C$79:$C$84,MATCH(C92,$B$79:$B$84,-1))) | =IF(C92>$E$79,$F$79,INDEX($F$79:$F$85,MATCH(C92,$E$79:$E$85,-1))) | =SUM(D92:E92) |
| 전두환씨 댁 | 316 | =IF(C93>$B$79,$C$79,INDEX($C$79:$C$84,MATCH(C93,$B$79:$B$84,-1))) | =IF(C93>$E$79,$F$79,INDEX($F$79:$F$85,MATCH(C93,$E$79:$E$85,-1))) | =SUM(D93:E93) |
| 노태우씨 댁 | 405 | =IF(C94>$B$79,$C$79,INDEX($C$79:$C$84,MATCH(C94,$B$79:$B$84,-1))) | =IF(C94>$E$79,$F$79,INDEX($F$79:$F$85,MATCH(C94,$E$79:$E$85,-1))) | =SUM(D94:E94) |
| 민중달씨 댁 | 580 | =IF(C95>$B$79,$C$79,INDEX($C$79:$C$84,MATCH(C95,$B$79:$B$84,-1))) | =IF(C95>$E$79,$F$79,INDEX($F$79:$F$85,MATCH(C95,$E$79:$E$85,-1))) | =SUM(D95:E95) |
(표 4)에서 기본요금과 전력량요금 부분에는 아래와 같은 수식이 사용되었습니다.
기본요금
=IF(C90>$B$79,$C$79,INDEX($C$79:$C$84,MATCH(C90,$B$79:$B$84,-1)))전력량요금
=IF(C90>$E$79,$F$79,INDEX($F$79:$F$85,MATCH(C90,$E$79:$E$85,-1)))Index 함수와 Match 함수에 대해서는 언젠가 설명드린 기억이 납니다. Index 함수는 테이블 내의 지정한 위치에 해당되는 값을 돌려주는 함수이고 Match 함수는 지정한 값과 일치하는 배열요소를 찾아 그 위치값을 돌려주는 함수입니다. 먼 소린지 잘 이해가 안 가시지요? ^^ 설명하는 Exceller도 그렇습니다!
| 지점명 | 매출액 |
| 강동 | 100 |
| 강서 | 200 |
| 강남 | 300 |
| 강북 | 400 |
이런 표가 있다고 가정을 합니다. 여기서 강남지점이 지점명 중에서 몇번째 위치하느냐를 구하려면,
| =MATCH("강남",B112:B115,0) | =MATCH("강남",B110:B113,0) |
이렇게 하면 됩니다. 이번에는 반대로 전체 지점들 중에서 3번째에 위치한 지점이 무엇이냐를 구하려면,
| =INDEX(B112:B115,3) | =INDEX(B110:B113,3) |
이번에는 위의 표에서 세번째 위치한 지점의 매출액을 구하려면,
| =INDEX(C112:C115,MATCH(INDEX(B112:B115,3),B112:B115,0)) | =INDEX(C110:C113,MATCH(INDEX(B110:B113,3),B110:B113,0)) |
잘 되지요? 어려울 것이 하나도 없습니다. 그런데 가만히 보니까 마지막의 것은 바로 Vlookup 함수와 비슷한 역할을 하는군요. 그렇습니다! Index 함수와 Match 함수를 조합해서 쓰면 Vlookup 함수와 동일한 결과를 얻을 수가 있는데 복잡하게 조합을 해서 쓰는 만큼 그에 따른 장점이 있습니다. Vlookup 함수보다 더 다양한 기능을 수행할 수 있는 것이지요.
Match 함수에 대해 좀더 자세히 설명을 드리자면,
Match(찾을 값, 찾을 영역, match_type) 이렇게 세 부분으로 나뉘어 집니다. 그리고 여기서 match_type은 다시,
① -1: 찾을 값보다 크거나 같은 값들 중에서 최소값을 구합니다. ② 0: (찾을 값과 같은 값이 여럿 있다면) 같은 값 중에서 첫째 값을 구합니다. ③ 1: 찾을 값보다 작거나 같은 값들 중에서 최대값을 구합니다(default).
이렇게 세 가지의 값 중 하나를 가집니다. 위의 예에서는 이 중에서 세번째의 것이 사용되었지요? 이제 기본요금과 전력량요금을 구하기 위해 왜 위에서와 같이 해 주었는지 이해하시겠지요?
Index 함수와 Match 함수는 상당히 중요하면서도 재미있는 함수니까 반드시 도움말을 참고로 잘 정리해 두시기 바랍니다.
다음 시간에…
정리 — 전력요금 계산하기
| 구분 | 내용 |
|---|---|
| 문제 | 사용량 구간마다 기본요금(표 2)과 전력량요금(표 3)이 다름 |
| 사용 함수 | INDEX와 MATCH (VLOOKUP으로는 어려움) |
| 기본요금 | =IF(C90>$B$79,$C$79,INDEX($C$79:$C$84,MATCH(C90,$B$79:$B$84,-1))) |
| 전력량요금 | =IF(C90>$E$79,$F$79,INDEX($F$79:$F$85,MATCH(C90,$E$79:$E$85,-1))) |
| MATCH 유형 | -1은 찾을 값보다 크거나 같은 값 중 최소값, 0은 같은 값 중 첫째 값, 1은 작거나 같은 값 중 최대값(기본값) |
| 참고 | 이 강좌의 수식 오류는 X0191 강좌에서 정정 |
자주 묻는 질문 (FAQ)
Q1. 구간별로 요금이 다를 때는 어떤 함수를 사용하나요?
VLOOKUP 함수로는 힘들고 INDEX 함수와 MATCH 함수를 조합하면 해결할 수 있습니다. MATCH로 사용량이 속한 구간의 위치를 찾고 INDEX로 그 위치의 요금을 가져옵니다.
Q2. MATCH 함수의 유형 인수는 어떤 의미인가요?
-1은 찾을 값보다 크거나 같은 값들 중 최소값을, 0은 같은 값 중 첫째 값을, 1은 찾을 값보다 작거나 같은 값들 중 최대값을 구합니다. 1이 기본값입니다.
Q3. INDEX와 MATCH는 VLOOKUP과 어떻게 다른가요?
조합하면 VLOOKUP과 동일한 결과를 얻을 수 있고, 복잡하게 조합하는 만큼 VLOOKUP보다 다양한 기능을 수행할 수 있습니다.
마치며
INDEX와 MATCH는 중요하면서도 재미있는 함수이니 도움말을 참고해 잘 정리해 두세요. VBA에 대한 기초 지식을 공부하실 분은 아이엑셀러 닷컴 사이트 상단 메뉴에서 [Excel 강의] - [Excel 입문]을 먼저 보시면 이해하기 쉽습니다.
