스프레드시트 3강. 함수 그리고 배열 수식

VLOOKUP이 자꾸 틀리는 이유는 함수가 어려워서가 아니라, 마지막 인수를 비워 두기 때문입니다.

풀고 시작

조용히 틀리는 것이 가장 무섭습니다

VLOOKUP의 인수는 넷입니다. 무엇을 찾을지, 어디서 찾을지, 몇 번째 열을 가져올지, 그리고 어떻게 찾을지입니다.

마지막 인수를 비우면 엑셀은 TRUE로 봅니다. 근사값 찾기죠. 이 모드는 찾을 범위의 첫 열이 오름차순으로 정렬돼 있다고 믿고 이진 탐색으로 훑습니다. 정렬돼 있지 않으면 오류를 내는 게 아니라 그럴듯한 엉뚱한 값을 내놓습니다. 오류는 눈에 띄지만 틀린 값은 눈에 안 띄죠. 그래서 사원 코드나 상품명으로 찾을 때는 반드시 마지막에 FALSE나 0을 적으셔야 합니다.

찾을 값은 언제나 범위의 첫 열에 있어야 하고, 가져올 열 번호는 그 범위 안에서 셉니다. 시트 전체 기준이 아닙니다. 찾는 값이 왼쪽에 없다면 VLOOKUP으로는 안 되고 INDEX와 MATCH를 짝지어야 합니다. MATCH가 몇 번째인지 알려 주고 INDEX가 그 자리의 값을 꺼내는 조합이죠. 이 조합은 왼쪽으로도 찾을 수 있고 열이 추가돼도 깨지지 않습니다.

세는 함수만 다섯 가지입니다

시험이 특히 좋아하는 자리입니다. 이름이 비슷해서 헷갈리는데, 세는 대상이 다릅니다.

COUNT가 숫자만 센다는 점이 핵심입니다. 이름이 든 열에 COUNT를 걸면 0이 나오죠.

조건을 붙이는 함수들은 짝이 맞습니다. SUMIF와 SUMIFS, AVERAGEIF와 AVERAGEIFS가 그렇습니다. 그런데 인수 순서가 다릅니다. SUMIF는 조건 범위를 먼저 쓰고 합칠 범위를 나중에 쓰지만, SUMIFS는 합칠 범위를 맨 앞에 씁니다. 복수형은 조건이 여러 개라 개수가 정해지지 않으니 합칠 범위를 앞에 고정해 둔 겁니다.

조건에는 부등호를 문자열로 적습니다. 큰따옴표로 감싸 ">=100"처럼 쓰고, 별표와 물음표를 와일드카드로 쓸 수 있습니다. 별표는 임의의 여러 글자, 물음표는 임의의 한 글자입니다.

반올림 삼형제와 오류를 감싸는 함수

ROUND는 반올림, ROUNDUP은 무조건 올림, ROUNDDOWN은 무조건 내림입니다. 두 번째 인수인 자릿수가 헷갈리는 지점이죠. 양수면 소수점 아래, 0이면 정수, 음수면 정수 부분의 자리를 뜻합니다. 자릿수를 -2로 주면 백의 자리에서 정리됩니다.

INT는 소수점 이하를 버리는 게 아니라 작은 쪽 정수로 내립니다. 음수에서 차이가 드러납니다. INT로 -3.5를 처리하면 -4가 되지만, TRUNC는 소수를 잘라 내기만 하므로 -3이 됩니다.

IFERROR는 수식이 오류를 낼 때 대신 보여 줄 값을 정합니다. VLOOKUP이 못 찾아 #N/A를 낼 때 빈칸이나 안내 문구로 바꾸는 데 씁니다. 다만 오류를 가리는 것이지 고치는 것이 아니라는 점은 기억해 두셔야 합니다. 잘못된 수식까지 조용히 덮어 버릴 수 있으니까요.

배열 수식은 한 번에 여러 칸을 계산합니다

단가 열과 수량 열이 있고 총액만 필요할 때, 보통은 곱셈 열을 따로 만들어 더합니다. 배열 수식을 쓰면 중간 열 없이 한 번에 끝납니다.

전통적인 방식에서는 수식을 넣고 Ctrl과 Shift와 Enter를 함께 누릅니다. 그러면 수식이 중괄호로 감싸져 보이는데, 이 중괄호는 엑셀이 붙인 표시이지 사람이 직접 입력하는 것이 아닙니다. 이걸 손으로 치면 문자로 인식됩니다.

배열 수식의 핵심은 범위와 범위를 짝지어 한 쌍씩 계산한 뒤 모은다는 것입니다. 그래서 짝지을 범위들의 크기가 같아야 하고, 다르면 오류가 납니다. 같은 계산을 함수 하나로 하는 SUMPRODUCT도 있는데, 이쪽은 Ctrl+Shift+Enter 없이 그냥 쓸 수 있습니다.

인출 문제

이전: 스프레드시트 2강 · 다음: 스프레드시트 4강