엑셀 · 함수

업무효율 엑셀 함수 TOP3: SUMIFS·INDEX MATCH

엑셀로 매달 말일마다 담당자별, 카테고리별로 매출을 따로 집계해야 하는 정산 업무를 맡고 있다면 필터를 걸었다 풀었다 하며 숫자를 하나씩 더해본 경험이 있을 것이다. 조회하려는 값이 있는 열이 결과를 가져올 열보다 오른쪽에 있어서 VLOOKUP으로는 도저히 값을 끌어올 수 없었던 적도 있을 거고, 이름과 부서, 직급이 각각 다른 셀에 흩어져 있어 & 기호로 하나하나 이어 붙이다 순서를 놓친 적도 있을 것이다.

이 세 가지는 사실 함수 하나씩만 제대로 알아두면 손이 훨씬 덜 가는 작업들이다.

SUMIFS·COUNTIFS 함수 – 조건이 여러 개일 때

매출 데이터에서 조건 하나로 합계를 내는 거야 SUMIF로도 충분하다. 문제는 조건이 두 개, 세 개로 늘어날 때다. “상품 카테고리가 A이면서 담당자가 B인 경우”처럼 조건을 동시에 걸어야 하는 순간 SUMIF는 한계에 부딪힌다. 이럴 때 쓰는 게 SUMIFS다.

sum_range 뒤에 조건 범위와 조건값을 쌍으로 계속 붙여나가는 구조이고, 이 쌍은 최대 127개까지 추가할 수 있다. Microsoft 공식 문서에 실린 예시를 보면 상품명이 “A”로 시작하면서 판매자가 “Tom”인 항목의 수량 합계를 다음처럼 구한다.

위 표라면 상품명이 “A”로 시작하고 판매자가 Tom인 행(Apples, Artichokes)의 수량만 골라 더하는 식이다. “=A*”처럼 와일드카드(*)를 조건에 넣을 수 있고, “<>Bananas” 같은 부정 조건도 그대로 사용 가능하다. 조건 범위 안에 텍스트, 숫자, 셀 참조를 섞어 써도 문제없다.

개수만 필요할 때는 COUNTIFS를 쓴다. 인수 구조는 SUMIFS와 사실상 같다. criteria_range1과 criteria1은 필수고, 그 뒤로 criteria_range2, criteria2 식으로 조건 쌍을 이어 붙이면 된다. 이것도 127쌍까지 가능하며, 숫자든 수식이든 셀 참조든 텍스트든 조건으로 넣을 수 있고 ?, * 같은 와일드카드도 지원한다.

실무에서는 이 둘을 나란히 써야 하는 경우가 많다. 담당자별 매출 합계(SUMIFS)와 담당자별 처리 건수(COUNTIFS)를 같은 표 옆 칸에 나란히 뽑아두면, 합계만 봐서는 안 보이던 “건수는 적은데 금액이 큰 담당자” 같은 패턴이 한눈에 들어온다.

다만 조건이 네다섯 개를 넘어가면 수식 자체가 길어져서, 나중에 조건을 하나 빼거나 바꿀 때 어디를 고쳐야 하는지 헷갈리는 경우가 실무에서 자주 보고된다. 이럴 땐 조건값을 수식 안에 직접 적지 말고 옆 칸에 따로 표로 만들어 셀 참조로 걸어두면, 값이 바뀔 때마다 수식을 다시 뜯어고치지 않아도 된다.

INDEX MATCH 함수 – 왼쪽 열 값을 끌어와야 할 때

VLOOKUP을 쓰다 보면 꼭 한 번은 막히는 지점이 있다. 찾으려는 값이 있는 열이 결과를 가져올 열보다 오른쪽에 있는 경우다. VLOOKUP은 구조상 왼쪽에서 오른쪽으로만 조회가 가능해서, 표 순서를 통째로 바꾸지 않는 이상 이 상황에서는 손을 쓸 수가 없다. 이럴 때 쓰는 조합이 INDEX와 MATCH다.

MATCH가 찾는 값의 위치(몇 번째 행인지)를 먼저 찾아내고, INDEX가 그 위치를 그대로 받아서 값을 반환하는 구조다. Microsoft 공식 문서에서는 송장 번호·도시·날짜가 뒤섞인 표에서 특정 도시(Atlanta)의 가장 빠른 송장 정보를 다음 수식으로 뽑아낸다.

도시 이름이 있는 B열이 가운데에 끼어 있어도, 결과로 가져올 송장 번호(A열)나 날짜(C열)가 그 왼쪽이든 오른쪽이든 상관없이 작동한다. 문서에서도 이 부분을 VLOOKUP 대비 핵심 장점으로 명시한다 — 찾는 값이 맨 왼쪽 열에 있지 않아도 INDEX-MATCH는 문제없이 돌아간다는 것.

회사에서 공유받는 파일이 아직 구버전 엑셀 형식이거나, 여러 사람이 돌아가며 열을 추가하고 지우는 시트를 다룰 때 INDEX-MATCH의 진가가 드러난다(추정: 국내 기업의 구버전 엑셀 사용 비율을 뒷받침하는 통계는 확인하지 못했으나, 구버전 호환이 필요한 상황 자체는 실무에서 흔히 언급되는 제약이다).

VLOOKUP은 조회 범위 중간에 열이 하나라도 삽입되거나 삭제되면 참조 번호가 틀어져 #REF! 오류가 나기 쉬운데, INDEX-MATCH는 참조 범위를 열 번호가 아니라 실제 범위로 잡기 때문에 이런 변화에 상대적으로 덜 흔들린다.

다만 INDEX-MATCH도 완전히 안전하지는 않다. 실무에서 자주 보고되는 실수는 MATCH의 lookup_array 범위를 처음 잡을 때 실제 참조 대상 열보다 좁게 잡아버리는 경우다. 찾는 값이 범위 밖에 있으면 MATCH는 곧바로 #N/A를 돌려주는데, 수식 자체는 문법상 멀쩡해 보여서 원인을 범위 밖에서 한참 찾게 된다. 여기서 다들 한 번씩 막힌다.

한 가지 짚어둘 점은 Microsoft 문서 스스로도 최신 대안으로 XLOOKUP·XMATCH를 먼저 권하고 있다는 것이다. 최신 버전 엑셀을 쓰는 환경이라면 XLOOKUP 한 줄로 같은 결과를 더 짧게 낼 수 있다는 반론도 가능하다. 다만 회사 표준 엑셀 버전이 낮거나 호환성을 우선해야 하는 문서라면, INDEX-MATCH 쪽이 여전히 안전한 선택이다.

TEXTJOIN 함수 – 여러 셀 텍스트를 하나로 합칠 때

이름, 부서, 직급이 각각 다른 열에 나뉘어 있는데 이걸 “홍길동 / 마케팅팀 / 과장”처럼 한 셀에 합쳐야 하는 순간이 있다. & 기호로 하나씩 이어 붙이면 되긴 하는데, 열이 늘어날수록 수식이 길어지고 구분기호를 넣는 위치도 매번 다시 신경 써야 한다. TEXTJOIN은 이 작업을 한 번에 처리한다.

구분기호(delimiter)를 한 번만 지정해두면 뒤에 나열한 텍스트나 범위를 전부 그 구분기호로 이어 붙여준다. 공식 문서 예시를 보면 구조가 명확하다.

실무에서는 이름·부서·직급을 합쳐 메일 참조 리스트를 만들거나, 여러 셀에 흩어진 도로명·상세주소·우편번호를 한 줄로 정리할 때 자주 쓴다. 예를 들어 B2에 이름, C2에 부서, D2에 직급이 있다면 =TEXTJOIN(", ", TRUE, B2, C2, D2) 한 줄로 “홍길동, 마케팅팀, 과장”이 만들어진다.

두 번째 인수인 ignore_empty를 TRUE로 두면 빈 셀은 건너뛰고 나머지만 이어 붙인다. FALSE로 두면 빈 셀도 자리를 차지해서 구분기호만 연달아 찍힌 결과가 나온다. 데이터가 중간중간 비어 있는 직원 명단 같은 경우라면 TRUE 쪽이 결과가 훨씬 깔끔하다.

반대로 한 셀에 뭉쳐 있는 텍스트를 쪼개야 한다면 LEFT·RIGHT·MID 같은 함수로 필요한 부분만 먼저 잘라낸 다음 TEXTJOIN으로 다시 합치는 순서가 된다. 이 세 함수는 이번 글의 주제는 아니니, TEXTJOIN과 짝을 이루는 전 단계 정도로만 기억해두면 될 것 같다.