프로그래밍 언어를 다뤄 본 사람이라면 엑셀 함수도 어렵지 않게 다룰 수 있다. 개발자는 여러 언어를 다루면서 생긴 "눈치"가 있기 때문이다. 필자도 강의 없이 눈치로만 엑셀을 사용하고 있다. 이 글은 프로그래밍 경험이 있는 독자가 짧은 시간에 함수를 익힐 수 있도록 핵심만 작성했다.
=로 셀 시작
= (등호)는 셀에 참조나 함수를 사용한다는 선언이다. = 없이 입력하면 숫자나 텍스트 등으로 인식한다. 따라서 연산이나 함수가 포함된 셀은 =로 시작해야 한다. 참고로, 함수마다 =로 시작하는 것이 아니라 수식 처음에만 =를 입력한다.
참조
함수로 데이터 처리 전, 데이터를 어떻게 저장하고 불러오는지 알아야 한다.
셀 참조
=셀셀은 데이터가 저장되는 기본 단위로 행-열 형식의 표로 이루어져 있다. 사진에 보이는 칸 하나하나가 셀이다.
행(가로)은 1부터 시작하며, 열(세로)은 A부터 시작한다. 따라서 첫 번째 행 첫 번째 열은 A1이 된다.
| A1 | B1 | C1 | D1 | E1 |
| A2 | B2 | C2 | D2 | E2 |
| A3 | B3 | C3 | D3 | E3 |
셀을 참조할 때 셀 번호를 적는다. 예를 들어 =A2는 A2에 저장된 값을 참조한다.
| 입력 | 결과 | |
| abc | =A1 | abc |
| 가나다 | =A2 | 가나다 |
시트 참조
=시트!셀시트는 독립된 페이지로 A1부터 시작하는 새로운 셀을 가진다. 특정 시트의 셀을 참고할 때 !를 사용한다. 예를 들어, =data!A1은 "data" 시트 A1을 참조한다.
범위 지정
=셀:셀함수를 사용하다보면 특정 셀부터 특정 셀까지 범위를 지정하기도 한다. A1:C2는 A1부터 C2를 뜻한다.
| A1 | B1 | C1 | D1 |
| A2 | B2 | C2 | D2 |
| A3 | B3 | C3 | D3 |
기본 연산
수치 연산자
| 연산자 | 의미 |
| + | 더하기 |
| - | 빼기 |
| * | 곱하기 |
| / | 나누기 |
=셀 (연산자) 셀
| 입력 | 결과 | |
| 5 | =A1 + A2 | 7 |
| 2 | =A1 - A2 | 3 |
| =A1 * A2 | 10 | |
| =A1 / A2 | 2.5 | |
COUNT / COUNTA
=COUNT(셀:셀)
=COUNTA(셀:셀)- COUNT: 범위 내 숫자 데이터를 카운트한다.
- COUNTA: 범위 내 모든 데이터를 카운트한다.
| 입력 | 결과 | ||
| 3 | a | =COUNT(A1:B4) | 3 |
| 4 | =COUNTA(A1:B4) | 6 | |
| b | 5 | ||
| c | |||
조건문
조건문을 통해 조건에 따라 값을 보여준다.
비교 연산자
| 연산자 | 의미 |
| = | 같음 (==) |
| <> | 다름 (!=) |
| < | 보다 작음 |
| > | 보다 큼 |
| <= | 보다 작거나 같음 |
| >= | 보다 크거나 같음 |
IF
=IF(조건문, IF_TRUE, IF_FALSE)IF-ELSE 문을 작성한다. 마치 C나 JavaScript의 ? 조건문과 비슷하다.
| 입력 | 결과 | ||
| 3 | 5 | =IF(A1>B1, A1, B1) | 5 |
| =IF(A1<>B1, "diff", "equal") | diff | ||
IFERROR
=IFERROR(함수식, 대체값)오류가 발생했을 때 대체 값을 지정한다. try-expect나 try-catch 구문과 비슷하다. 엑셀 파일을 혼자 사용한다면 오류가 그대로 노출되는 편이 안전하지만, 사용자에게 배포한다면 IFERROR로 오류 처리를 고려해야 한다.
| 입력 | 결과 |
| =n!A1 | #REF! |
| =a+3 | #NAME? |
| =IFERROR(n!A1, "null") | null |
| =IFERROR(a+3 , 0) | 0 |
VLOOKUP
=VLOOKUP(key, table, column, 0)표를 참고해 값을 가져오는 함수다. Hash Map, Dict(python) 등에서 데이터를 가져오는 것과 유사하다.
- key: 참고할 데이터
- table: 참고할 표
- column: 표에서 탐색할 열 번호 (1부터 시작)
예시:
| 입력 | 결과 | ||
| 이름 | 나이 | 직업 | |
| 김철수 | 23 | 요리사 | |
| 이수지 | 21 | 개발자 | |
| 김철수 | =VLOOKUP(A5, A2:C3, 3, 0) | 요리사 | |
위 예시는 [A2:C3]의 표를 참고한다. 그 중 [A5]="김철수"와 일치하는 행을 찾고 3번째 열(직업)을 가져온다. 이때 key는 표 가장 왼쪽 열을 기준으로 찾는다.
익숙한 Python으로 풀어보면 아래와 같다.
# 데이터
key = "김철수"
table = {
"김철수": ["김철수", "23", "요리사"],
"이수지": ["이수지", "21", "개발자"],
}
column = 2 # 엑셀은 1부터 인덱스가 시작하므로 3
# VLOOKUP(key, table, column, 0)
table[key][column]VLOOKUP 마지막 인수인 0은 key와 완벽히 일치하는 값을 찾는다는 옵션이다. 자세한 내용은 [VLOOKUP 유사일치]에서 확인할 수 있다. 특별한 경우가 아니라면 0을 사용하면 된다.
문자열 포맷팅
=값 & 값 & ...&로 문자열을 합친다. 일반적으로 아는 + 연산자와 비슷하다. 다른 점은 문자열과 다른 데이터 타입을 연결할 수 있다.
| 입력 | 결과 | |
| 3 | 2 | |
| ="최대: " & IF(A1>B1, A1, B1) & "(개)" | 최대: 3(개) | |
팁
자동완성과 $
함수를 입력하고 셀 우측 하단을 드래그하면 함수가 자동완성된다.
예시:
| 3 | 2 | =A1+B1 |
| 5 | 9 | =A2+B2 |
| 1 | 6 | =A3+B3 |
이때 $가 붙은 값은 고정된다.
| 입력 | =A1+B1 | =A1+$B$1 | =A$1+B1 |
| 자동완성 | =A2+B2 | =A2+$B$1 | =A$1+B2 |
| =A3+B3 | =A3+$B$1 | =A$1+B3 |
셀에 데이터 저장
엑셀은 모두 함수로 이루어진다. 따라서 데이터를 여러 번 가공하면 식이 복잡해진다.
=함수1(함수2(함수3(...)...)...)값을 변수에 저장하듯 셀에 저장하면 가독성이 올라가고 수정이 용이해진다.
예시: (전)
| =VLOOKUP(IF(A1>SUM(B1:C10),SUM(B1:C10),0),D1:G10,2) |
예시: (후)
| 셀 번호 | 입력 |
| A11 | =SUM(B1:C10) |
| A12 | =IF(A1>A11, A11, 0) |
| A13 | =VLOOKUP(A12, D1:G10, 2) |
물론 값을 대놓고 저장하면 시트가 더러워진다. 따라서 별도의 시트를 만들어 사용하는 편이다.
셀 잠금 및 시트 보호
사용자에게 배포하는 파일이라면 매우 높은 확률로 함수가 깨진다. 셀을 무분별하게 복붙하거나 함수를 무시하고 값을 직접 입력하기도 한다. 경험담이다. 그러니 수정하지 못하도록 셀 잠금을 사용하면 좋다.
한셀의 경우, 보호할 셀을 선택한 후 셀 잠금을 해준다. 반대로 값을 입력해야하는 셀은 잠금을 풀어준다. 그리고 시트 보호를 설정하면 함수를 안전하게 유지할 수 있다.
VBS(VBA) 매크로
VBS(VBA)를 사용하면 더 정교한 자동화가 가능하다. 필자는 불가피하게 VBS로 코드를 만들고 있지만 추천하지는 않는다. 복잡한 로직이라면 Python + openpyxl로 작업하는 편이 낫다고 생각한다.