
엑셀 파일을 여러 개 다루다 보면 함수 이름을 아는데도 수식 앞에서 손이 멈출 때가 있습니다. ITQ 엑셀 문제도 비슷합니다. 어떤 값을 구하라는 문장인지 먼저 읽으면 길어 보이는 함수 목록이 몇 가지 유형으로 좁혀집니다.
함수 이름보다 문제의 요구를 먼저 읽기
한국생산성본부의 ITQ 한글엑셀 출제기준에는 표작성 영역의 함수 수식 작성이 포함돼 있습니다. 범위에는 SUM, AVERAGE 같은 기본 함수부터 IF, COUNTIF, VLOOKUP, 데이터베이스 함수까지 들어갑니다. 다만 실제 문제에 필요한 함수는 지시문과 표의 모양이 알려줍니다. ‘총액’이면 합계, ‘조건에 맞는 건수’면 조건부 개수, ‘코드에 해당하는 이름’이면 참조 함수를 떠올리는 식입니다.
수식을 입력하기 전에는 결과가 들어갈 셀, 계산에 쓸 데이터 범위, 조건이 적힌 셀을 차례로 확인합니다. 예를 들어 상품명은 B열, 수량은 C열, 단가는 D열에 있는데 총액을 구해야 한다면 C5*D5처럼 한 행의 값을 곱합니다. 이어서 아래 행까지 복사할 수식인지도 봅니다. 이 순서만 익혀도 표를 읽는 시간이 꽤 줄어듭니다.
합계와 개수는 가장 단순한 수식부터
판매금액이 E5부터 E14까지 있을 때 전체 합계는 =SUM(E5:E14)입니다. 평균은 =AVERAGE(E5:E14), 최댓값은 =MAX(E5:E14)로 구합니다. 각 셀을 더하는 =E5+E6+E7 방식보다 범위가 눈에 잘 들어오고, 빠뜨린 행도 찾기 쉽습니다.
개수 문제에서는 COUNT와 COUNTA를 구분해야 합니다. =COUNT(C5:C14)는 숫자가 들어 있는 셀을 세고, =COUNTA(C5:C14)는 숫자와 문자 등 비어 있지 않은 셀을 셉니다. 직원 이름의 수를 세는데 COUNT를 쓰면 결과가 0일 수 있습니다. ‘입력된 사람 수’처럼 문자가 들어 있는 열이라면 COUNTA가 맞는지 확인합니다.
반올림 지시도 놓치기 쉽습니다. =ROUND(E5,0)은 소수 첫째 자리에서 반올림해 정수로 만들고, =ROUND(E5,1)은 소수 둘째 자리에서 반올림해 소수 첫째 자리까지 남깁니다. 화면에 보이는 소수 자릿수만 바꾸는 셀 서식과 값 자체를 바꾸는 ROUND는 역할이 다릅니다. 지시문에 ‘함수를 이용하여 반올림’이라고 적혔다면 수식 안에서 처리해야 합니다.
조건이 붙으면 IF와 조건부 집계로 나누기
‘80점 이상이면 합격, 아니면 불합격’처럼 한 셀의 값에 따라 표시를 바꾸는 문제는 =IF(C5>=80,"합격","불합격")으로 씁니다. IF의 순서는 조건, 조건이 참일 때의 값, 거짓일 때의 값입니다. 경계값인 80점이 포함돼야 하므로 > 대신 >=를 써야 합니다. 실제로 79점, 80점, 81점을 대입해 보면 조건을 거꾸로 썼는지 금방 알 수 있습니다.
반면 ‘서울 지점이 몇 개인가’처럼 여러 행에서 조건에 맞는 셀을 세는 문제는 =COUNTIF(B5:B14,"서울")입니다. ‘서울 지점의 매출 합계’라면 =SUMIF(B5:B14,"서울",E5:E14)를 씁니다. 앞의 범위는 조건을 검사할 곳이고, 마지막 범위는 합산할 값이 있는 곳입니다. 조건을 찾는 열과 더할 열을 바꾸면 숫자가 나와도 답은 틀릴 수 있습니다.
조건이 셀에 적혀 있다면 문자 대신 셀 주소를 넣을 수 있습니다. G5에 ‘서울’이 있다면 =COUNTIF(B5:B14,G5)입니다. 수식을 아래로 복사하면서 지점명은 G6, G7로 바뀌어야 하고 검사 범위는 그대로여야 한다면 =COUNTIF($B$5:$B$14,G5)처럼 범위에 달러 표시를 붙입니다.
찾기 함수는 표의 방향과 정확한 일치를 확인하기
상품 코드로 상품명을 가져오는 표가 있다고 해보죠. 코드가 H5:H12, 상품명이 I5:I12에 있고 찾을 코드가 B5라면 =VLOOKUP(B5,$H$5:$I$12,2,FALSE)입니다. 2는 지정한 표 범위에서 두 번째 열인 상품명을 뜻합니다. FALSE는 코드와 정확히 일치하는 값을 찾겠다는 뜻입니다.
VLOOKUP은 찾을 값이 지정한 표 범위의 첫 열에 있어야 합니다. 코드보다 왼쪽에 상품명이 놓인 표라면 범위를 다시 잡거나 다른 함수를 골라야 합니다. 또 H5:I12를 고정하지 않고 수식을 아래로 복사하면 참조 범위가 함께 내려가 일부 코드가 검색되지 않을 수 있습니다. 수식 하나를 완성한 뒤 복사해서 채우고, 첫 행과 마지막 행의 결과를 모두 확인하는 편이 안전합니다.
문자 일부를 꺼내는 문제도 표를 읽는 방식은 같습니다. 코드 ‘AB-2407’의 앞 두 글자가 분류라면 =LEFT(B5,2), 네 번째 글자부터 네 글자가 번호라면 =MID(B5,4,4)입니다. 공백과 하이픈도 글자 위치를 차지하므로, 실제 셀 내용을 눈으로 세어 본 뒤 시작 위치를 정합니다.
오류가 나면 수식의 세 지점만 다시 보기
결과가 예상과 다를 때는 먼저 범위의 시작과 끝을 확인합니다. 다음은 조건의 경계입니다. ‘이상’과 ‘초과’를 바꿔 썼거나 따옴표가 빠지면 다른 결과가 나옵니다. 수식을 복사한 뒤 참조 주소가 움직였는지 봅니다. 특히 조건표나 찾기표는 고정해야 하는 경우가 많습니다.
연습할 때는 함수 이름만 외우기보다 기출문제 한 장에서 ‘무엇을 구하나, 어디를 읽나, 복사해도 되나’를 셀마다 표시해 두는 게 좋았습니다. 익숙한 함수도 표가 바뀌면 다른 실수를 부르거든요. 한 문제를 풀고 경계값과 마지막 행까지 확인하는 습관이 실제 작업에서도 시간을 아껴줍니다.