들어가기 전에
함수는 Excel의 핵심 요소라고 해도 과언이 아닙니다. Excel 함수는 사용자가 입력한 특정 인수에 따라 단일 값이나 여러 결과 배열을 생성합니다. 각각의 함수도 강력하지만 여러 가지 함수를 결합하거나 중첩하면 더욱 파워풀해집니다.
이 글에서는 웹용 Excel이나 Microsoft 365용 Excel에서 사용할 수 있는 기존 Excel 함수와 최신 Excel 함수를 모두 다룹니다. 이전 버전의 Excel을 사용하는 경우 일부 함수에 액세스하지 못할 수 있습니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: 4 Excel Function Combinations You Need to Know
- URL: https://www.howtogeek.com/microsoft-excel-function-combinations-need-to-know/
1. INDEX + XMATCH: 다양한 조회 수행
Excel에서 오랫동안 사랑받으며 많이 사용되어 온 함수 조합 중 하나는 INDEX와 MATCH 였습니다. 하지만 XMATCH 함수의 등장으로 상황은 달라졌습니다. INDEX와 MATCH의 최신 버전인 XMATCH를 결합하면 더 많은 옵션을 얻을 수 있습니다.
INDEX 함수는 데이터세트에서 값을 추출하고, XMATCH 함수는 해당 행 및/또는 열로 이동하는 탐색자 역할을 합니다. 이 두 가지 함수의 조합은 모든 방향으로 검색할 수 있고, 세로 및 가로 데이터세트를 지원하며, 전체 열이나 행을 반환할 수도 있습니다.
INDEX 구문은 다음과 같습니다.
=INDEX(a, b, c)
- a: 범위
- b: 행 번호
- c: 열 번호
다음 예에서 Nathaniel의 총점을 알고 싶다면 다음과 같은 수식을 입력합니다. Nathaniel은 T_Scores 표의 5번째 행에 있고, 그의 총점은 6번째 열에 있기 때문입니다.
=INDEX(T_Scores, 5, 6)
하지만 인수 b 에 행 번호(5)를 직접 입력하면 조회의 의미가 완전히 사라집니다. Nathaniel의 행을 식별하는 과정에서 그의 점수만 볼 수도 있었기 때문입니다. 대신, 위 수식에서 행 번호 "5"를 중첩된 XMATCH 수식으로 대체하면 INDEX 함수에 행 번호가 자동으로 입력됩니다.
XMATCH의 구문은 다음과 같습니다.
=XMATCH(a, b, c, d)
- a: 찾을 항목
- b: 검색할 범위
- c: 일치 유형
- 0: 정확한 일치(기본값)
- -1: 정확한 일치 또는 다음으로 가장 작은 항목
- 1: 정확한 일치 또는 다음으로 가장 큰 항목
- 2: 와일드카드 일치
- d: 검색 모드
- 1: 처음부터 끝까지(기본값)
- -1: 마지막에서 처음으로
- 2: 이진 검색(b는 오름차순)
- -2: 이진 검색(b는 내림차순)
전체 수식은 다음과 같습니다.
=INDEX(T_Scores, XMATCH(H2, T_Scores[Player], 0, 1), 6)
여기서 중첩된 XMATCH 수식은 T_Scores 테이블의 Player 열에서 H2의 값이 있는 행을 식별하여 수동으로 삽입한 행 번호를 대체합니다.
정확한 일치(0)와 위에서 아래로의 검색(1)이 기본 설정이기 때문에 XMATCH 수식에서 마지막 두 인수를 생략할 수도 있었습니다.
=INDEX(T_Scores, XMATCH(H2, T_Scores[Player]), 6)
H2 셀에서 다른 선수를 선택하면 INDEX-XMATCH 조합의 XMATCH 부분이 표에서 해당 선수의 이름을 찾아 INDEX 함수에 해당 이름이 있는 행을 알려줍니다. 그런 다음 INDEX 함수는 6열로 이동하여 해당 점수를 반환합니다.
만약 아래 스크린샷처럼 다른 열에서 값을 반환하고 싶다면 어떻게 해야 할까요?
현재 여섯 번째 열에 대한 참조가 수식에 하드코딩되어 있으므로 다른 열의 값을 찾으려면 수식을 수정해야 합니다. 이 경우에도 XMATCH를 사용하면 해결할 수 있습니다.
=INDEX(T_Scores, XMATCH(H2, T_Scores[Player]), XMATCH(I2, T_Scores[#Headers]))
이렇게 해서 첫 번째 중첩된 XMATCH 수식이 셀 H2의 값을 찾을 수 있는 행 번호를 식별하고, 두 번째 중첩된 XMATCH 수식이 셀 I2의 값을 찾을 수 있는 열 번호를 식별하는 양방향 조회를 만들었습니다.
H2 셀에서 다른 플레이어를 선택하면 이 수식이 제대로 잘 작동하는 것을 볼 수 있습니다.
2. IF + AND/OR: 여러 조건에 따라 값 반환
Excel의 IF 함수는 단독으로 조건을 적용하여 조건이 충족되면 하나의 값을, 충족되지 않으면 다른 값을 반환합니다. 하지만 경우에 따라 두 개 이상의 조건을 입력해야 할 수 있으며, 이럴 때 AND 함수와 OR 함수가 도움이 될 수 있습니다.
IF의 구문은 다음과 같습니다.
=IF(a, b, c)
- a: 논리적 테스트(평가하려는 셀 또는 셀과 설정하려는 조건)
- b: 조건이 충족되면 반환할 값
- c: 조건이 충족되지 않을 경우 반환할 값
이 예에서는 다음 수식을 입력합니다.
=IF([@ExpYears]>5, "Y", "N")
C2 셀에 직원 A의 경력이 5년 이상인지 테스트하여, 5년 초과면 "Y", 5년 이하면 "N"을 반환합니다. 수식이 Excel 표 형식이므로 Enter 키를 누르면 각 행에 동일한 수식이 적용됩니다.
이 원칙을 한 단계 더 발전시켜, 두 번째 예에서는 5년 이상의 경력 과 골드 인증을 보유한 모든 직원은 상태 열에 "Senior"로 표시되어야 하고, 그 외 모든 직원은 "Junior"로 표시하고자 합니다.
이 경우 IF 수식의 논리적 테스트 인수는 중첩된 AND 수식이 됩니다.
=AND(a, b)
- a: 첫 번째 논리적 테스트
- b: 두 번째 논리 테스트(총 255개까지 나올 수 있음)
두 가지 함수가 결합된 수식은 다음과 같습니다.
=IF(AND([@ExpYears]>5, [@Cert]="Gold"), "Senior", "Junior")
셀 D2에 수식을 입력하고 Enter 키를 눌러 Status 열까지 확장하면 다음과 같은 결과가 나타납니다.
AND 함수는 참조된 셀을 평가하여 모든 조건이 충족되는 경우에만 양수 결과를 반환합니다. 하지만 조건 중 하나라도 충족되는 경우에도 양수 결과를 반환할 수 있습니다.
직원이 최소 10년 이상의 경력이 있거나 골드 인증을 취득한 경우 관리자가 될 가능성이 있다고 가정해 볼까요? 이 조건을 충족하는 수식을 작성하려면 IF 내에 OR 함수를 중첩합니다.
=AND(a, b)
- a: 첫 번째 논리적 테스트
- b: 두 번째 논리 테스트(총 255개까지 나올 수 있음)
D2 셀에는 다음 수식을 입력합니다.
=IF(OR([@ExpYears]>=10, [@Cert]="Gold"), TRUE, FALSE)
IF 수식의 마지막 두 인수에 부울 값(TRUE 및 FALSE)을 사용했기 때문에 D열의 셀을 체크박스로 바꿀 수 있습니다 . 여기서 TRUE는 체크된 체크박스이고 FALSE는 체크되지 않은 체크박스입니다. 이렇게 하려면 ManPot 열의 모든 셀을 선택한 후 리본의 [삽입] 탭에서 [확인란]을 클릭하면 됩니다.
3. FILTER + UNIQUE: 중복 없는 정제된 배열 반환
Excel의 UNIQUE 함수는 범위 내에서 고유한 값 목록을 반환하는 반면, FILTER 함수를 사용 하면 설정한 조건에 따라 범위를 필터링할 수 있습니다.
FILTER 함수부터 시작해 보겠습니다. 구문은 다음과 같습니다.
=FILTER(a, b, c)
- a: 필터링할 범위
- b: 필터를 정의하는 범위와 조건
- c: 필터가 값을 반환하지 않을 경우 반환할 값
예를 들어, 다음 수식을 입력합니다.
=FILTER(T_Shops[Manager], T_Shops[Type]=F1, "No result")
F2 셀에 입력하면 T_Shops 테이블의 Type 열에서 "DIY"(F1 셀의 값)를 검색하여 같은 테이블의 Manager 열에서 해당하는 모든 이름을 반환합니다. 필터에서 값을 반환하지 않으면 F2 셀에 "No result"가 표시됩니다.
[참고] FILTER와 UNIQUE는 동적 배열 함수 이므로 결과가 인접 셀에 분산되므로 수식을 입력하기 전에 충분한 공간을 확보해야 합니다. 또한, 동적 배열 함수는 Excel 표에서는 사용할 수 없으므로 서식 이 지정되지 않은 일반 셀에 수식을 입력해야 합니다.
하지만 Lucy와 Vic은 두 개 이상의 DIY 매장을 관리하기 때문에 결과에 중복으로 나타나는 것을 볼 수 있습니다.
이럴 때 UNIQUE 함수가 힘을 발휘합니다. 먼저 구문을 살펴보겠습니다.
=UNIQUE(a, b, c)
- a: 고유 값이 반환되는 범위
- b: 고유한 열을 반환하려는 경우 TRUE이고 고유한 행을 반환하려는 경우 생략
- c: 정확히 한 번 발생하는 모든 고유 값을 반환하려는 경우 TRUE이고, 여러 번 발생하는 모든 고유 값을 반환하려는 경우 생략
다음과 같은 수식을 입력합니다.
=UNIQUE(T_Shops[Manager])
H2 셀에 T_Shops의 관리자 열에 있는 모든 사람의 이름을 반환합니다. 이제는 두 번 이상 나타나는 이름이 없습니다.
앞서 입력한 FILTER 수식이 중복된 결과를 반환했기 때문에, UNIQUE 함수와 결합하여 값 중복을 방지할 수 있습니다. 다음과 같이 FILTER 수식을 UNIQUE 함수 안에 간단히 넣을 수 있습니다.
=UNIQUE(FILTER(T_Shops[Manager], T_Shops[Type]=F1))
한 단계 더 나아가, UNIQUE와 FILTER 함수 조합에 SORT 함수를 추가하면 결과를 정렬하여 나타낼 수도 있습니다.
4. EOMONTH + SEQUENCE: 일련의 월말 날짜 생성
재무 보고서를 간소화하거나, 내년 예산을 세우거나, 다가올 프로젝트를 계획하려는 경우 시작일을 기준으로 한 달의 전이나 후 마지막 날짜를 반환하는 EOMONTH와 값(또는 이 경우 날짜)의 시퀀스를 생성할 수 있는 SEQUENCE를 결합하면 월말 날짜 목록을 생성하는 동적이고 시간을 절약하며 오류를 줄일 수 있습니다.
[팁] 시작하기 전에 날짜를 입력할 모든 셀에 날짜 숫자 서식이 설정 되어 있는지 확인하세요. 이렇게 하려면 셀을 선택하고 Ctrl+1을 눌러 셀 서식 대화 상자를 실행한 후, 범주 필드에서 "날짜" 또는 "사용자 지정"을 선택하세요. 그렇지 않으면 결과에 날짜 대신 날짜 관련 일련 번호가 표시됩니다.
EOMONTH 함수는 간단하며 두 개의 인수만 필요합니다.
=EOMONTH(a, b)
- a: 시작 날짜
- b: 시작 날짜 이전 또는 이후의 개월 수. 양수는 미래 날짜, 음수는 과거 날짜 반환
예를 들어 다음과 같이 입력합니다.
=EOMONTH(C1, B2)
C1 셀의 날짜(8월 15일)를 가져와서 B2 셀의 값(11월에서 3개월 후)에 따라 개월 수를 앞이나 뒤로 이동한 다음, 결과 월의 종료 날짜(11월 30일)를 반환합니다.
주어진 시작일 이후의 월말 날짜 시퀀스를 만들고 싶다고 가정해 보겠습니다. 이때 네 개의 인수를 갖는 SEQUENCE 함수가 유용합니다.
=SEQUENCE(a, b, c, d)
- a: 반환할 행의 수
- b: 반환할 열의 수
- c: 시퀀스의 첫 번째 숫자
- d: 시퀀스의 각 값 사이의 증가값
예를 들어, 다음 수식을 입력합니다.
=SEQUENCE(10, 1, 1, 2)
A1 셀에 1부터 시작하는 홀수의 10행 1열 시퀀스를 반환합니다.
SEQUENCE 함수는 동적 배열을 생성하므로, #SPILL! 오류를 방지하려면 스프레드시트에 결과가 인접 셀에 걸쳐 나타날 수 있도록 충분한 공간을 확보해야 합니다. 또한, 동적 배열은 Excel 표와 호환되지 않으므로 입력하는 수식은 일반 셀에 입력해야 합니다.
이러한 함수를 결합하기 위해 SEQUENCE 함수는 EOMONTH 함수에서 인수 b(전후의 개월 수) 역할을 합니다.
여기서 A1 셀에 시작 날짜를 입력한 후, 2025년의 남은 월말 날짜를 구하기 위해 A2 셀에 입력해야 하는 수식은 다음과 같습니다.
=EOMONTH(A1, SEQUENCE(11))
여기서 A1은 시작 날짜의 셀 참조이고, 11은 반환하려는 후속 월말 날짜의 개수입니다.
2025년의 두 달마다 월말 날짜를 반환하고 싶다고 가정해 보겠습니다. 이 경우 A2 셀의 수식은 다음과 같습니다.
=EOMONTH(A1, SEQUENCE(6, , , 2))
SEQUENCE 수식의 첫 번째 인수는 Excel에서 6개 행의 결과를 반환하도록 지시하고, 네 번째 인수는 각 값 사이에 2개월 간격을 나타냅니다.
마지막 예로, 월말 날짜 시퀀스가 A열을 따라가는 대신 1행을 따라가도록 하고 싶습니다. 따라서 A1 셀에 시작 날짜를 입력한 후 B2 셀에는 다음 수식을 입력합니다.
=EOMONTH(A1, SEQUENCE(, 6 , , 2))
SEQUENCE 수식의 두 번째 인수는 Excel에서 6개 열의 결과를 반환하도록 하고, 마지막 인수는 2개월마다 결과를 반환합니다.
이 함수 쌍은 동적이므로 시작 날짜를 변경하면 시퀀스도 그에 따라 조정됩니다. 하지만 EOMONTH와 SEQUENCE를 사용하여 월말 시퀀스를 만든 후 날짜를 고정하려면 날짜가 포함된 모든 셀(시작 날짜 포함)을 선택하고 Ctrl+C를 눌러 복사한 후 Ctrl + Shift + V를 눌러 값만 붙여넣습니다.
마치며
Excel에서 함께 잘 작동하는 다양한 함수에 대해 소개했습니다. 여러 가지 함수를 조합하여 다양한 상황에 활용하면 Excel의 파워는 더욱 강력해집니다.
'Excel' 카테고리의 다른 글
| 야근 없는 삶? 엑셀 x AI 자동화로 업무 속도 확 끌어올리는 비법! (0) | 2025.12.14 |
|---|---|
| 시간 낭비를 멈추게 해 주는 3가지 Excel 함수 (0) | 2025.12.11 |
| INDEX 및 XMATCH를 사용하여 Excel에서 양방향 조회 하는 방법 (0) | 2025.12.09 |
| 12개 기능 키 단축키로 스프레드시트 작업 빠르게 처리하기 (0) | 2025.12.08 |
| [강의 예고] 오타 내고 싶어도 못 냅니다. 엑셀 입력 실수 '원천 봉쇄' 수식 (0) | 2025.12.07 |
