[태그:] 직장인엑셀

엑셀 수식은 모두 등호(=)로 시작하고, 함수에 넣는 값은 괄호 안에 쉼표로 나눠 적는다. 실무에서 반복되는 엑셀 수식은 합계·조건·조회·정리 네 가지고 함수 이름으로는 20개 안쪽이다. XLOOKUP은 Microsoft 365와 Excel 2021부터 쓸 수 있고 Excel 2016과 2019에는 아예 없다.

함수를 몰라서 막히는 경우는 생각보다 적다. 하고 싶은 계산에 어떤 함수를 써야 하는지 몰라서 막힌다. 지점별로 묶어 합계를 내고 싶다는 생각이 SUMIFS까지 안 넘어가는 것이다. 그래서 이름순으로 정리하면 다음 날 절반은 기억나지 않고, 하고 싶은 일 순으로 정리해야 기억에 남는다.

1. 합계·조건·조회·정리 네 묶음

매일 쓰는 엑셀 수식 묶음

합계와 개수: SUM, AVERAGE, MAX, MIN, COUNT, COUNTA

조건: IF, IFS, SUMIF, SUMIFS, COUNTIF, COUNTIFS

조회: XLOOKUP, VLOOKUP, INDEX와 MATCH

정리: IFERROR, TRIM, ROUND, TEXT, TODAY

실제 수식은 이렇다.

=SUM(D2:D20)

=IF(E2="완료","지급","확인")

=SUMIFS(D2:D20,B2:B20,"영업",E2:E20,"완료")

=XLOOKUP(G2,A2:A20,C2:C20,"없음")

=IFERROR(XLOOKUP(G2,A2:A20,C2:C20),"확인필요")

글자를 조건으로 넣을 때는 큰따옴표로 감싼다. 30만원을 넘는 것만 더할 때도 부등호를 따옴표 안에 넣어 ">300000"으로 적는다. 이 따옴표를 빼먹으면 결과가 0으로 나오거나 #NAME? 이 뜬다.

2. SUMIF는 더할 범위가 뒤, SUMIFS는 앞

이름은 S 한 글자 차이인데 더할 범위가 들어가는 자리는 반대다.

=SUMIF(조건범위, 조건, 더할범위)

=SUMIFS(더할범위, 조건범위1, 조건1, 조건범위2, 조건2)

SUMIF를 써 둔 수식 뒤에 조건을 하나 더 적으면 값이 틀어진다. 오류 표시도 안 뜬다. 수식 형식은 맞기 때문에 엑셀도 오류라고 알려 줄 수 없다.

조건이 하나뿐일 때도 SUMIFS로 쓰는 편이 낫다. SUMIFS 형식으로 통일하면 조건이 늘어날 때 뒤에 두 칸씩 붙이기만 하면 된다. COUNTIF와 COUNTIFS는 더할 범위가 없어서 이 문제가 안 생긴다.

3. Excel 2016·2019에서는 XLOOKUP 대신 VLOOKUP

VLOOKUP은 찾는 값이 표의 맨 왼쪽 열에 있어야 하고, 가져올 값이 몇 번째 열인지 숫자로 세어 넣는다. 표 중간에 열이 하나 끼어들면 세어 둔 숫자가 밀려서 다른 열의 값이 따라온다.

XLOOKUP은 찾을 범위와 가져올 범위를 따로 지정하니 표 중간에 열이 추가돼도 가져오는 값은 바뀌지 않는다. 못 찾았을 때 대신 넣을 문구도 네 번째 자리에 같이 적는다. 왼쪽이든 오른쪽이든 상관없이 가져온다.

문제는 이 함수가 Excel 2016과 2019에는 없다는 것이다. XLOOKUP으로 짠 파일을 2019 이하에서 열면 함수 이름을 못 알아봐서 #NAME? 이 뜬다. 혼자 쓰는 파일이면 XLOOKUP이 낫고, 여러 사람에게 보내는 파일이면 VLOOKUP이나 INDEX와 MATCH 조합으로 두는 편이 안전하다. 파일을 받는 사람의 버전을 물어보기 어려운 경우가 대부분이라 결국 그 버전에 맞춰 함수를 고른다.

4. 오류 코드 다섯 개와 바로 고칠 원인

오류 코드가 떴다는 건 엑셀이 계산을 안 한 게 아니다. 계산 중에 오류가 난 것이다. 계산 옵션이나 서식을 뒤지기 전에 코드부터 읽는다.

수식 셀에 뜨는 오류와 원인

#DIV/0!: 나누는 셀이 0이거나 비어 있다

#VALUE!: 숫자가 들어갈 자리에 글자가 섞였다

#REF!: 참조하던 행·열·시트가 지워졌다

#NAME?: 함수 이름이나 따옴표, 시트 이름 표기가 틀렸다

#N/A: 조회 범위에 찾는 값이 없다

#N/A는 보고서에 그대로 낼 수 없으니 IFERROR로 감싸 빈칸이나 문구로 바꾸게 된다. 다만 IFERROR는 원인을 고치는 함수가 아니라 화면에서 가리는 함수다. 진짜로 값이 빠진 행까지 같이 가려지니, 원인을 한 번 확인한 표에만 씌운다.

5. 내 파일 수식을 모아 다음 달에도 다시 쓰기

이미 사용 중인 파일에 어떤 수식이 들어 있는지부터 볼 수 있다. 홈 탭에서 찾기 및 선택 → 이동 옵션 → 수식을 고르면 수식이 든 셀이 한꺼번에 선택된다. 셀 하나만 선택하고 실행하면 시트 전체의 수식 셀이 선택되고, 범위를 잡아 두고 실행하면 그 범위 안의 수식 셀만 선택된다.

시트 전체의 수식을 보고 싶으면 Ctrl과 백틱(`)을 같이 누른다. 결과 대신 수식이 보이고, 다시 누르면 원래대로 돌아온다. 계산을 끄는 기능이 아니라 보기만 바꾸는 것이라 파일이 상하지 않는다.

여기서 나온 수식을 빈 시트에 함수 이름, 실제로 쓴 수식, 어디에 썼는지 세 칸으로 옮겨 적는다. 검색해서 저장해 둔 정리표를 다시 안 보는 이유는 분량이 아니라 남의 표라서다. 30줄짜리라도 내가 지난달에 쓴 수식만 모인 표는 다음 달에도 그대로 다시 쓴다.