엑셀 같은 값 합치는 방법 (중복 데이터 정리)

엑셀로 데이터를 정리하다 보면 같은 이름이나 같은 항목이 여러 행에 반복되어 있는 경우가 많습니다.

예를 들어 아래처럼 상품명과 판매량이 입력되어 있다고 가정해 보겠습니다.

상품명	판매량
사과	10
배	5
사과	20
포도	8
배	15

이 데이터를 상품별로 정리해서 사과 30, 배 20, 포도 8처럼 같은 값끼리 합치고 싶다면 어떻게 해야 할까요?

엑셀에서는 목적에 따라 SUMIF 함수, 피벗 테이블, TEXTJOIN 함수 등을 활용할 수 있습니다.

이번 글에서는 엑셀에서 같은 값을 찾아 합치는 대표적인 방법을 알아보겠습니다.

1. SUMIF 함수로 같은 값의 숫자 합치기

가장 먼저 알아둘 방법은 SUMIF 함수입니다.

같은 이름이나 같은 항목에 해당하는 숫자를 모두 더하고 싶을 때 사용하기 좋은 함수입니다.

기본 형식은 다음과 같습니다.

=SUMIF(조건범위, 조건, 합계범위)

예를 들어 A열에 상품명, B열에 판매량이 있다고 가정해 보겠습니다.

A열	B열
사과	10
배	5
사과	20
포도	8
배	15

D2 셀에 사과라고 입력하고 E2 셀에 다음 수식을 입력합니다.

=SUMIF(A:A,D2,B:B)

각 부분의 의미는 다음과 같습니다.

A:A : 같은 값을 찾을 범위
D2 : 찾을 값
B:B : 합계를 계산할 숫자가 있는 범위

결과는 30이 표시됩니다.

A열에서 ‘사과’를 모두 찾은 다음 해당 행의 B열 숫자인 10과 20을 더해주는 것입니다.

배와 포도 역시 같은 방식으로 계산할 수 있습니다.

2. SUMIFS로 조건이 여러 개인 값 합치기

조건이 하나가 아니라 여러 개라면 SUMIFS 함수를 사용하는 것이 편리합니다.

예를 들어 다음과 같은 데이터가 있다고 해보겠습니다.

상품	지역	판매량
사과	서울	10
사과	부산	20
사과	서울	15
배	서울	10

여기에서 상품이 사과이면서 지역이 서울인 판매량만 합계를 구하고 싶다면 SUMIFS를 사용할 수 있습니다.

=SUMIFS(C:C,A:A,"사과",B:B,"서울")

결과는 25가 됩니다.

즉,

10 + 15 = 25

가 계산되는 것입니다.

SUMIF가 하나의 조건을 기준으로 합계를 계산한다면 SUMIFS는 여러 조건을 동시에 만족하는 데이터의 합계를 계산할 때 사용한다고 기억하면 쉽습니다.

3. 피벗 테이블로 같은 값 자동으로 합치기

데이터가 많다면 개인적으로 가장 편리한 방법 중 하나가 피벗 테이블입니다.

함수를 하나씩 입력하지 않아도 같은 항목별로 숫자를 자동으로 묶어 합계를 계산할 수 있습니다.

먼저 합치려는 데이터 영역을 선택합니다.

상단 메뉴에서

삽입 → 피벗 테이블

을 선택합니다.

피벗 테이블을 만든 다음 필드 설정에서 상품명을 행 영역으로 이동하고 판매량을 값 영역으로 이동합니다.

그러면 다음과 같이 자동으로 정리할 수 있습니다.

상품명	판매량 합계
사과	30
배	20
포도	8
총합계	58

원본 데이터가 수백 개 또는 수천 개라 하더라도 같은 상품명을 일일이 찾아낼 필요가 없습니다.

특히 거래내역이나 매출자료처럼 중복되는 항목이 많은 데이터를 항목별로 집계할 때 피벗 테이블이 상당히 유용합니다.

4. 같은 값은 하나만 남기고 싶다면?

숫자를 합산하는 것이 아니라 단순히 중복된 값을 제거하고 하나씩만 남기고 싶은 경우도 있습니다.

예를 들어

사과
배
사과
포도
배

라는 데이터가 있을 때

사과
배
포도

처럼 만들고 싶은 경우입니다.

이때는 엑셀의 중복된 항목 제거 기능을 사용할 수 있습니다.

중복 데이터가 들어 있는 범위를 선택한 다음

데이터 → 중복된 항목 제거

를 선택합니다.

기준으로 사용할 열을 확인한 후 확인을 누르면 중복된 값이 삭제되고 고유한 값만 남습니다.

다만 이 방법은 실제로 원본 데이터에서 중복 행을 삭제하는 기능이므로 원본을 그대로 보관해야 한다면 주의해야 합니다.

5. UNIQUE 함수로 중복 값만 따로 정리하기

Microsoft 365 등 UNIQUE 함수를 지원하는 엑셀 버전이라면 원본 데이터를 삭제하지 않고 중복되지 않은 값만 따로 가져올 수도 있습니다.

예를 들어 A2:A10 범위의 고유한 값만 가져오려면 다음과 같이 입력합니다.

=UNIQUE(A2:A10)

A열에

사과
배
사과
포도
배

가 있다면 UNIQUE 함수 결과에는

사과
배
포도

처럼 중복을 제외한 값이 표시됩니다.

SUMIF와 UNIQUE를 함께 사용하면 중복 항목 목록을 자동으로 만들고 항목별 합계를 계산하는 표도 만들 수 있습니다.

6. 같은 값의 텍스트를 한 셀에 합치려면?

숫자의 합계가 아니라 같은 항목에 해당하는 여러 텍스트를 한 셀에 합쳐야 하는 경우도 있습니다.

이때는 TEXTJOIN과 FILTER 함수를 함께 활용할 수 있습니다.

예를 들어 A열에 이름, B열에 담당업무가 있고 A열에서 ‘홍길동’에 해당하는 B열의 내용을 하나로 합치고 싶다면 다음과 같은 방식으로 사용할 수 있습니다.

=TEXTJOIN(", ",TRUE,FILTER(B2:B10,A2:A10="홍길동"))

조건에 맞는 텍스트를 FILTER 함수가 먼저 찾아주고 TEXTJOIN 함수가 결과를 하나의 셀로 연결해 줍니다.

따라서 숫자가 아닌 문자 데이터를 조건에 따라 합칠 때 활용하기 좋은 방법입니다.

엑셀 같은 값 합치기, 어떤 방법이 좋을까?

사용 목적에 따라 적절한 방법이 달라집니다.

원하는 작업	추천 방법
같은 항목의 숫자 합계	SUMIF
여러 조건에 맞는 숫자 합계	SUMIFS
많은 데이터를 항목별로 집계	피벗 테이블
중복 값을 삭제	중복된 항목 제거
중복 없이 고유값 추출	UNIQUE
같은 항목의 텍스트 합치기	TEXTJOIN + FILTER

단순히 같은 상품의 판매량처럼 같은 값을 기준으로 숫자를 더하는 작업이라면 SUMIF가 가장 간단합니다.

반면 데이터의 양이 많고 여러 항목을 한꺼번에 분석해야 한다면 피벗 테이블을 활용하는 것이 훨씬 편리합니다.

마무리

엑셀에서 ‘같은 값 합치기’라고 해도 실제로 원하는 작업은 여러 가지가 있습니다.

같은 값의 숫자를 더하고 싶다면 SUMIF, 여러 조건이 필요하다면 SUMIFS, 많은 데이터를 자동으로 분류하고 집계하고 싶다면 피벗 테이블을 활용하면 됩니다.

또한 중복된 값 자체를 정리하려면 중복 제거 또는 UNIQUE 함수를 활용할 수 있고, 같은 조건에 해당하는 문자들을 하나의 셀에 모으고 싶다면 TEXTJOIN과 FILTER 함수를 사용할 수 있습니다.

실무에서 자주 사용하는 기능들이기 때문에 각각의 차이만 알아두어도 엑셀 데이터를 정리하는 시간을 크게 줄일 수 있습니다.