엑셀 함수 처음 쓸 때 꼭 알아야 할 것들: IF, SUMIFS, XLOOKUP을 순서대로 익히는 법

💡 이 팁으로 해결할 수 있는 불편함: 엑셀 함수가 너무 많아 어디서부터 익혀야 할지 막막한 일. 실무에서 쓰는 함수는 손에 꼽히고, 순서대로 익히면 어렵지 않습니다.

엑셀 함수는 수백 개지만 업무에서 자주 쓰는 것은 열 개 안팎입니다. 그 열 개도 서로 이어져 있어서 순서대로 익히면 쉽게 늘어납니다. 아래 예시는 데이터가 2행부터 100행까지 있다고 가정한 것이니, 내 표의 범위에 맞게 바꿔서 쓰세요.

1단계: 수식 입력의 기본기부터 익힌다

함수를 쓰려면 수식 입력 규칙 세 가지만 알면 됩니다.

1. 모든 수식은 등호로 시작합니다. =SUM(B2:B10)처럼 씁니다
2. 범위는 시작 셀과 끝 셀 사이에 콜론을 넣습니다. B2부터 B10까지는 B2:B10입니다
3. 수식을 아래로 복사할 때는 셀 오른쪽 아래 모서리의 작은 네모를 더블클릭하면 아래 칸까지 자동으로 채워집니다

함수 이름은 소문자로 써도 자동으로 대문자로 바뀝니다. 입력하다 보면 함수 목록이 뜨니 화살표와 탭으로 골라도 됩니다.

2단계: 합계와 평균은 가장 먼저 익힌다

가장 기본이 되는 함수입니다.

1. 합계: =SUM(B2:B100)
2. 평균: =AVERAGE(B2:B100)
3. 개수: =COUNT(B2:B100)는 숫자가 들어 있는 칸의 개수를 셉니다

숫자만 있는 범위에 쓰는 함수라는 점을 기억하세요. 글자가 들어 있는 칸은 합계에서 무시됩니다.

3단계: 조건에 따라 다르게 처리하는 IF

IF는 조건이 맞으면 A, 아니면 B를 돌려주는 함수입니다.

=IF(B2>=60,"합격","불합격")

B2가 60 이상이면 합격, 아니면 불합격이 표시됩니다. 글자는 큰따옴표로 감싸야 합니다. 조건이 여러 개면 IF 안에 IF를 넣을 수 있지만, 세 겹 이상 겹치면 읽기 어려워집니다. 그럴 때는 조건을 표로 정리해서 뒤에서 나오는 조회 함수로 처리하는 편이 낫습니다.

4단계: 조건에 맞는 것만 더하고 세는 SUMIFS와 COUNTIFS

실무에서 가장 많이 쓰는 함수들입니다.

1. 조건 하나로 개수 세기: =COUNTIF(A2:A100,"사과")는 A열에서 사과가 몇 개인지 셉니다
2. 조건 하나로 더하기: =SUMIF(A2:A100,"사과",C2:C100)는 A열이 사과인 행의 C열 값을 더합니다
3. 조건 여러 개로 더하기: =SUMIFS(C2:C100,A2:A100,"사과",B2:B100,"서울")는 A열이 사과이면서 B열이 서울인 행의 C열을 더합니다
4. 조건 여러 개로 세기: =COUNTIFS(A2:A100,"사과",B2:B100,"서울")

여기서 주의할 점은 인수의 순서입니다. SUMIF는 조건 범위가 먼저이고 합계 범위가 나중이지만, SUMIFS는 반대로 합계 범위가 맨 앞에 옵니다. 헷갈리기 쉬우니 SUMIFS를 먼저 익히고 조건이 하나일 때도 SUMIFS를 쓰는 습관을 들이면 편합니다.

5단계: 다른 표에서 값을 찾아오는 조회 함수

두 개의 표를 이어 붙여야 할 때 쓰는 함수입니다. 예를 들어 상품 코드로 상품명을 찾아올 때입니다.

1. XLOOKUP: =XLOOKUP(E2,A2:A100,C2:C100,"없음")는 E2 값을 A열에서 찾아 같은 행의 C열 값을 돌려주고, 못 찾으면 없음을 표시합니다
2. VLOOKUP: =VLOOKUP(E2,A2:C100,3,FALSE)는 E2 값을 범위의 첫 열에서 찾아 세 번째 열의 값을 돌려줍니다. 마지막의 FALSE는 정확히 일치하는 값만 찾으라는 뜻입니다

XLOOKUP이 더 쓰기 편하지만 사용할 수 있는 버전이 제한됩니다. 마이크로소프트 안내에 따르면 XLOOKUP은 Microsoft 365와 Excel 2021에서 쓸 수 있고 Excel 2019와 2016에서는 지원되지 않습니다. 회사 컴퓨터의 엑셀 버전이 오래되었다면 VLOOKUP이 필요하고, 다른 사람과 파일을 주고받는다면 상대방 버전도 생각해야 합니다.

6단계: 수식을 복사할 때 필요한 고정 기호

수식을 아래로 복사했더니 결과가 어긋나는 가장 흔한 이유는 참조가 함께 움직여서입니다. 기본 참조는 복사하면 한 칸씩 이동합니다. 고정하고 싶으면 열과 행 앞에 달러 기호를 붙입니다.

1. $A$2는 열과 행을 모두 고정합니다
2. A$2는 행만 고정합니다
3. $A2는 열만 고정합니다

수식을 입력하다가 셀 주소를 선택한 상태에서 F4 키를 누르면 이 고정 방식이 순서대로 바뀝니다. 조회 함수의 표 범위를 고정할 때 특히 자주 씁니다.

7단계: 오류 표시가 뜨면 원인을 읽는다

수식이 잘못되면 셀에 오류가 나옵니다. 자주 보는 것만 알아두면 됩니다.

1. #N/A: 조회 함수가 값을 찾지 못했습니다. 오타나 공백이 원인인 경우가 많습니다
2. #DIV/0!: 0으로 나눴습니다
3. #REF!: 참조하던 셀이 삭제되었습니다
4. #VALUE!: 계산할 수 없는 형태의 값이 들어 있습니다
5. #NAME?: 함수 이름이나 이름 정의가 잘못되었습니다

오류를 감추고 싶다면 =IFERROR(수식,"")처럼 감싸서 빈 칸으로 표시할 수 있습니다. 다만 오류를 무조건 감추면 원인을 놓치니, 문제를 해결한 다음에 쓰세요.

조회가 안 되는 이유가 눈에 안 보이는 공백이라면 =TRIM(A2)로 앞뒤 공백을 지워서 맞춰볼 수 있습니다. 숫자가 글자로 저장돼 계산이 안 되는 경우도 흔합니다.

엑셀 파일이 갑자기 안 열려서 작업물이 날아가는 일을 막고 싶다면 중요한 파일 백업하는 법도 함께 참고하세요.