엑셀 치트시트를 다시 쓴다 — 함수보다 참조와 버전이 문제다

엑셀 치트시트는 함수 목록이 길다. 그런데 실제로 시간을 잡아먹는 것은 함수를 몰라서가 아니라 참조가 밀려서, 그리고 그 함수가 이 버전에 없어서다. 그 두 가지를 중심으로 다시 썼다.

이 글은 시리즈에서 유일하게 실행으로 확인하지 못한 글이다. 앞의 글들은 코드를 직접 돌려 결과를 붙였지만 엑셀은 그럴 수가 없다. 여기 적은 것은 문서와 내 경험에서 온 것이고, 버전에 따라 다른 부분은 그렇다고 표시해 두었다.

참조 — `$` 하나가 전부다

수식을 아래로 끌었을 때 값이 이상해지는 것의 거의 전부가 이것이다.

표기	끌었을 때
`A1`	행도 열도 따라 움직인다 (상대)
`$A$1`	아무것도 안 움직인다 (절대)
`A$1`	열은 움직이고 행은 고정
`$A1`	행은 움직이고 열은 고정

수식 편집 중 F4 를 누르면 이 네 가지가 차례로 돈다. 손으로 $ 를 치지 않는다.

곱셈표처럼 가로·세로로 동시에 끄는 표에서는 혼합 참조가 답이다. 세로 방향 기준은 $A2, 가로 방향 기준은 B$1 로 두면 수식 하나를 사방으로 끌 수 있다.

조회 — VLOOKUP 의 네 번째 인자

=VLOOKUP(찾을값, 범위, 몇번째열, FALSE)

네 번째 인자를 비우면 근사 일치가 된다. 정렬이 안 된 표에서 근사 일치를 쓰면 에러 없이 엉뚱한 값이 나온다. 조회에서 나는 사고의 대부분이 여기서 나온다. 정확히 찾을 것이면 언제나 FALSE(또는 0)를 적는다.

VLOOKUP 은 찾을 값이 범위의 첫 열에 있어야 하고, 오른쪽만 볼 수 있다. 왼쪽을 봐야 하면 INDEX + MATCH 를 쓴다.

=INDEX(가져올범위, MATCH(찾을값, 찾을범위, 0))

MATCH 의 세 번째 인자 0 이 정확 일치다. 여기서도 기본값이 근사 일치라 같은 함정이 있다.

최신 버전이면 XLOOKUP 한 줄로 끝난다. 왼쪽도 보고, 못 찾았을 때 값도 인자로 받고, 기본이 정확 일치다.

=XLOOKUP(찾을값, 찾을범위, 가져올범위, "없음")

다만 Excel 2019 이하에는 없다. 회사 PC 와 집 PC 의 버전이 다르면 여기서 갈린다.

조건과 오류

=IF(조건, 참일때, 거짓일때)
=IFS(조건1, 값1, 조건2, 값2, ...)          앞에서부터 처음 참인 것을 돌려준다
=SWITCH(값, 후보1, 값1, 후보2, 값2, ...)    값이 후보와 같은지로 고른다
=IFERROR(수식, 에러일때)                    #N/A 를 0 이나 "" 로 바꿀 때

IFS 는 먼저 쓴 조건이 이긴다. 점수 구간을 나눌 때 조건 순서를 뒤집어 쓰면 전부 첫 조건에 걸린다.

에러 값은 원인이 이름에 적혀 있다.

값	뜻
`#N/A`	조회에서 못 찾았다
`#VALUE!`	자료형이 안 맞는다 (숫자 자리에 글자)
`#REF!`	참조하던 셀이 사라졌다 (행·열 삭제)
`#DIV/0!`	0 으로 나눴다
`#NAME?`	함수 이름이 틀렸거나 이 버전에 없는 함수다
`#SPILL!`	결과가 퍼질 자리에 다른 값이 있다 (동적 배열)

#NAME? 가 뜨는데 철자가 맞다면 버전을 의심한다. XLOOKUP, FILTER, TEXTJOIN 이 그렇게 걸린다.

조건부 집계 — 이것만 손에 붙으면 된다

=SUMIFS(더할범위, 조건범위1, 조건1, 조건범위2, 조건2, ...)
=COUNTIFS(조건범위1, 조건1, ...)
=AVERAGEIFS(평균낼범위, 조건범위1, 조건1, ...)

SUMIF 와 SUMIFS 는 인자 순서가 다르다. SUMIFS 는 더할 범위가 맨 앞, SUMIF 는 맨 뒤다. 헷갈릴 바에는 조건이 하나여도 SUMIFS 로 통일하는 편이 낫다.

조건을 곱해서 세는 방법도 알아 두면 쓸모가 많다.

=SUMPRODUCT((지역="서울")*(금액>100))      조건을 만족하는 행의 개수
=SUMPRODUCT((지역="서울")*금액)            조건에 맞는 금액의 합

TRUE·FALSE 를 곱하면 1·0 이 된다는 성질을 쓰는 것이다. 조건이 복잡할 때 COUNTIFS 보다 유연하다.

동적 배열 — 최신 버전에서만

Microsoft 365 · Excel 2021 부터는 결과가 여러 칸이면 알아서 아래로 퍼진다(스필).

=FILTER(A2:D11, A2:A11="서울", "없음")     조건에 맞는 행 전체
=UNIQUE(A2:A11)                            중복 제거
=SORT(A2:D11, 2, -1)                        2번째 열 기준 내림차순

이 세 개가 있으면 “조건에 맞는 것만 뽑아 정렬해서 중복 없이” 를 수식 한 줄로 한다. 옛 버전에서는 같은 일을 필터·복사·중복 제거로 손이 하게 된다.

텍스트 정리

=TRIM(A2)                    앞뒤 공백 제거. 조회가 안 맞을 때 첫 번째로 의심할 것
=SUBSTITUTE(A2, "N", "X")    특정 글자를 바꾼다 (무엇을 바꿀지로 지정)
=REPLACE(A2, 2, 1, "X")      위치로 바꾼다 (몇 번째부터 몇 글자)
=LEFT/RIGHT/MID(A2, ...)     잘라내기
=TEXTJOIN(",", TRUE, A2:A11) 구분자로 잇기 (2019 이상)
=VALUE(A2) / =TEXT(A2, "0.0%")

SUBSTITUTE 와 REPLACE 의 차이가 헷갈리는데, 무엇을 바꿀지 아는 것이 SUBSTITUTE, 어디를 바꿀지 아는 것이 REPLACE 다.

조회가 안 되는데 눈으로는 같아 보이면 십중팔구 공백이나 자료형이다. TRIM 을 씌우고, 숫자처럼 보이는 문자열은 VALUE 로 바꾼다.

단축키는 이 정도면 충분하다

하는 일	키 (Windows)
참조를 절대/혼합으로 돌리기	F4 (수식 편집 중)
마지막 동작 반복	F4 (셀에서)
데이터 끝까지 이동 / 선택	CTRL+방향키 / CTRL+SHIFT+방향키
표 전체 선택	CTRL+A
필터 켜고 끄기	CTRL+SHIFT+L
표로 만들기	CTRL+T
셀 안에서 줄 바꾸기	ALT+Enter
선택 범위에 같은 값 한꺼번에 입력	CTRL+Enter
자동 합계	ALT+=
오늘 날짜	CTRL+;
값만 붙여넣기	CTRL+ALT+V → V

맥에서는 대체로 CTRL 을 CMD 로 바꾸면 된다.

언제 엑셀을 놓을 것인가

같은 정리를 매달 반복한다 → 수식 대신 파워 쿼리, 아니면 pandas
행이 수십만을 넘는다 → 엑셀이 버티더라도 사람이 못 버틴다
검증이 필요하다 → 수식은 화면에 보이지 않는다. 코드는 남고, 다시 돌릴 수 있다

엑셀은 한 번 보고 판단할 때 가장 빠르다. 그 이상 반복되면 옮기는 편이 결국 빠르다.

버전에 따라 없는 것들

함수	언제부터
`IFS`, `SWITCH`, `TEXTJOIN`, `MAXIFS`	Excel 2019 · Microsoft 365
`XLOOKUP`, `FILTER`, `UNIQUE`, `SORT`, `SEQUENCE`	Excel 2021 · Microsoft 365
`VLOOKUP`, `INDEX`/`MATCH`, `SUMIFS`	예전부터 다 있다

남에게 파일을 보낼 때는 아래쪽 줄만 쓰는 것이 안전하다. 최신 함수로 만든 파일을 옛 버전에서 열면 #NAME? 로 깨진다. 이 시리즈에서 파이썬 라이브러리를 두고 계속 했던 이야기가 엑셀에서도 똑같이 반복된다.

출처

DataCamp 의 Excel Basics, Data Manipulation in Excel, Excel Keyboard Shortcuts 치트시트 세 장을 보고 다시 쓴 것이다. 원본은 DataCamp 치트시트 페이지에서 받을 수 있다. 원본은 Microsoft 365 기준이라, 옛 버전에서 무엇이 없는지는 내가 따로 확인해 덧붙였다.

참조 — $ 하나가 전부다