컴퓨터활용능력 1급 실기 계산문제 - 엑셀 함수 MAX, MATCH, INDEX 함수

컴퓨터활용능력 1급 실기 계산문제 - 엑셀 함수 MAX, MATCH, INDEX 함수

컴퓨터활용능력 1급 실기 공부를 하면서 이렇게 포스팅을 하게 되었습니다. 계산문제는 컴퓨터활용능력1급 실기 스프레드시트 과목 중에서 무려 30점이라는 비중이나 차지하고 있는 파트이기 때문이죠. 실기 같은 경우에는 공부하는 내용들이 많이 올라와 있지도 않을 뿐더러 홍보글이 워낙 많아서 도움되는 글을 찾기도 힘들었구요. 그래서 제가 하나하나 공부해가면서 이렇게 글을 올리게 되었네요. 컴퓨터활용능력 공부하시는 분들도 이 글을 보시고 많은 도움이 되셨으면 좋겠습니다. 책을 봐도 잘 이해가 되지 않는 편이라 제가 이해하기 편한 방향으로 설명드릴 예정이니 참고 부탁드립니다.

오늘 제가 공부한 함수는 MAX, MATCH, INDEX 함수입니다. 컴퓨터활용능력 1급 실기 계산문제에서 함수가 한개씩만 활용가능하게 나오면 참 좋겠지만 안타깝게도 함수가 2~3개가 복합적으로 사용되는 경우가 많아서 제 머릿속에도 정리하고 다른분들께도 알릴 겸 이렇게 포스팅을 하게 되었네요.

문제1) 위의 표에서 수량이 가장 많은 품목의 이름을 나타내어라. 
(INDEX, MAX, MATCH 함수 사용)

제가 예제로 만든 문제입니다. 얼마 전에 상시로 본 실기 문제에서도 비슷한 문제가 출제되었기 때문에 빼놓지 않고 공부를 할 수 밖에 없었는데요. 이 문제를 풀기위해서 바로 한줄로 빨리 완성해서 끝내시는 분들도 있으시겠지만 저는 머리가 빨리 회전하지 않기 때문에 한단계씩 끊어서 문제를 풀었습니다. 하지만 어떤 함수인지 아예 모르는 상태에서는 푸는데 시간도 오래걸리고 이해하기 어려우니 '=INDEX(' 나 '=MAX(', '=MATCH(' 까지 입력하고 'SHIFT + F3'을 누르셔서 함수마법사를 호출해서 미리 어떤 함수인지 어떤 인수가 들어가는지 확인해 보시는 것이 좋습니다.

각 함수별 인수 설명(INDEX, MAX, MATCH)

MAX(최대값을 구하고자 하는 범위)
MATCH(찾을값, 찾고자 하는 범위, 매치타입)
매치타입이란?
찾을값과 정확히 일치하는지 작은값인지 큰값인지 비교(0 : 정확히 일치, 1 : 보다 작음, -1 : 보다 큼)
INDEX(찾고자 하는 범위, 행번호, 열번호)

 

문제를 풀기 위해서 가장 중요한 것은 함수의 배열 순서인데요. 위의 문제의 경우에는 수량이 가장 많은 품목을 찾는 것이기 때문에 최대값을 찾는 MAX 함수는 수량의 범위(D3:D5) 만큼을 지정해줍니다. 표에 있는 수량 중에서 제일 많은 값인 100이 결과값으로 나오게 되죠. MATCH 함수는 범위에서 찾는 값이 몇 번째의 위치에 있는지 찾는 함수로 가장 많은 수량이 몇 번째 행에 위치해 있는지 확인할 수 있습니다. 범위 중에서 100은 세번째 행에 위치해있기 때문에 결과값은 3이 나오구요. 3번째 행에 위치한 품목명을 구하기 위해서 INDEX 함수를 사용하여 INDEX(품목의 범위, 작성한 MATCH 함수, 열번호)를 작성하셔서 수량이 가장 많은 결과값 '손난로'를 구하시면 됩니다. 열번호는 범위에서 첫번째 열이기 때문에 1로 입력하시면 되세요.

문제2) 위의 표에서 분기별 매출액이 가장 높은 회사의 이름을 나타내어라.
(INDEX, MAX, MATCH 함수 사용)

한문제만 풀어보고는 기억에 남지 않기 때문에 약간 수정을 해서 예제 문제를 만들어 보았습니다. 정말 근소하게 차이가 나는 문제이지만 유심히 보시고 약간 바뀌어서 나올 수 있다는 생각도 들어서요. 이번에도 문제1과 다르지 않게 함수의 순서는 동일합니다. 하지만 문제1번은 답을 1개만 구하는 문제였고 이번 문제는 분기별로 매출액을 구해야하기 때문에 '$'를 잘 활용해서 답을 구해야겠지요?

위의 이미지는 1분기 매출액 기준의 식이구요. 답을 문제2의 표 우측에 있다고 하면 자동채우기 기능을 통해서 2,3분기 매출액도 구할 수 있습니다. 먼저 A,B,C사의 1분기 매출액 중 최대값을 구하기 위해 MAX 함수를 사용해서 B8:D7까지의 범위로 최대값인 10000이 구해줍니다. 하지만 제가 위에 입력한 값을 보시면 $(달러)가 앞에 들어가 있는데요. 달러 표시는 고정값을 의미합니다. 만약 위처럼 $B8:$D8까지로 범위를 설정하고 자동채우기를 통해 아래쪽으로 쭉 끌어서 식을 채운다고 하면 열의 값은 1씩 증가하면서 자동으로 변경되게 됩니다. 물론 문제의 상황에서 반드시 써야하는가? 라는 질문을 받았다고 하면 그렇지는 않죠. 우측이나 아래쪽으로 이동을 했을 때 값이 변경되면 안되는 값이 있을 경우 적용하는 것이라 저는 습관상 변경되지 않을 열이나 행이 있을 경우에는 저렇게 처리를 해놓게 되었네요. 달러($)를 직접 입력하실 수도 있지만 'F4'키를 누르시면 열이나 행, 열과 행 둘다 설정이 가능하니 참고하시기 바랍니다. 다음으로는 MATCH 함수를 통해서 B8부터 D8까지의 범위에서 최대값이 몇번째의 위치해 있는지 확인해줍니다. 매치타입은 0을 입력해서 정확히 일치하는 값인지 설정해주셔야하구요. 다르게 설정이 되어있으면 전혀 상이한 값이 될 수 있으니 반드시 설정해주셔야 합니다. 마지막으로 INDEX 함수를 사용하여 구하고자 하는 회사명의 범위를 첫번째 인수에 넣고, 행 인수값에는 첫번째 행이기 때문에 1을 인수에 넣고, 1분기 매출액의 최대값의 순번이 들어있는 MATCH 함수를 INDEX 함수의 행 인수값에 넣어줍니다. 아까 말씀드렸던 MAX 함수에서는 달러($)를 사용하여 고정값을 굳이 하지 않으셔도 된다고 말씀드렸는데요. INDEX 함수에서의 회사명의 범위인 'B8:D8'은 반드시 고정값으로 해주셔야하니 참고하시기 바랍니다. 우측으로 자동채우기 할일은 크게 없을테니 열만 고정하셔도 무방할 듯하네요.
이만 포스팅을 마치겠습니다.
안녕.

-관련글 2017/05/08 - [햄릿슈의 생활 정보] - 2017년 2회 텔레마케팅관리사 필기 시험 솔직 후기 2017/04/21 - [햄릿슈의 생활 정보] - 2017년 대한상공회의소 컴퓨터활용능력 1급 실기시험 공부 방법, 시험 팁 솔직 후기 2017/01/12 - [햄릿슈의 생활 정보] - 안드로이드폰에서 아이폰으로 이동하기에 유용한 어플 2016/11/21 - [햄릿슈의 생활 정보] - 대한상공회의소 컴퓨터활용능력 1급 필기 상시검정 합격수기