엑셀(Excel)에서 조건에 따라 고유 목록 만들기 – 9가지 실전 방법

마이크로소프트 엑셀(Microsoft Excel)에서는 다양한 조건을 적용해 고유한 값을 가진 목록을 만드는 여러 가지 방법이 있습니다. 고유 목록은 주로 표에서 중복 데이터를 제거하기 위해 사용됩니다. 이 글에서는 여러 조건에 맞춰 고유 목록을 생성하는 다양한 방법을 단계별로 자세히 알아보겠습니다.

조건에 따라 엑셀에서 고유 목록을 만드는 9가지 방법

1. 여러 열의 기준을 적용해 고유 행 목록 만들기

아래 그림에서 왼쪽 표에는 참가자 이름과 나이, 종목 등 무작위 데이터가 들어 있습니다. 오른쪽 표는 출력용 표로, 여기에는 고유값만 추출하여 정리할 예정입니다. 예를 들어, 중복 없는 참가자 이름과 각각의 나이만 확인하고 싶다고 가정해 보겠습니다.

여기서는 범위나 배열에서 고유값을 반환하는 UNIQUE 함수를 사용합니다. 이 함수의 일반적인 구문은 다음과 같습니다.

=UNIQUE(array, [by_col], [exactly_once])

UNIQUE 함수는 Excel 365에서만 제공되지만, 마지막 3개 섹션에서는 기존의 전통적인 함수들을 조합한 대체 방법도 함께 소개하니 참고하세요.

따라서 F5 셀에 입력할 수식은 다음과 같습니다.

Enter 키를 누르면 중복 없는 이름과 해당 참가자의 나이가 배열 형태로 반환됩니다.

2. 알파벳 순으로 정렬된 고유 값 목록 얻기

SORT 함수를 활용하면 고유값을 알파벳 순서대로 정렬할 수도 있습니다. 오름차순(A~Z) 또는 내림차순(Z~A) 기준을 지정하여 참가자 이름을 첫 글자를 기준으로 재정렬할 수 있습니다.

출력 셀인 E5 셀에 입력할 SORT와 UNIQUE 함수가 결합된 수식은 다음과 같습니다.

Enter 키를 누르면 A부터 Z까지 알파벳 순으로 정렬된 배열이 반환됩니다.

3. 하나의 셀로 연결된 고유 값 목록 만들기

두 개의 열에서 고유 데이터를 추출한 뒤, 이를 하나의 열에 합쳐서 표시하고 싶은 경우도 있습니다. 아래 예시에서는 특정 구분 기호인 콤마(,)를 사용해 고유한 이름과 나이를 한 열에 함께 표시합니다. 서로 다른 두 열의 값을 연결하기 위해 앰퍼샌드(&)를 활용합니다.

출력 셀인 F5 셀에 입력할 수식은 다음과 같습니다.

이 수식은 고유한 행들의 배열을 하나의 열로 반환합니다.

4. 조건이 있는 고유 값 목록 만들기 (UNIQUE-FILTER 수식)

i. 여러 개의 AND 조건으로 고유 값 찾기

이번 섹션에서는 몇 가지 조건을 추가하고, 해당 조건에 맞는 고유 데이터를 추출하는 방법을 알아봅니다. 예를 들어, 수영에만 참가했으면서 25세 미만인 참가자의 고유 이름을 알고 싶다고 가정해 보겠습니다. 이 경우 UNIQUE 함수와 FILTER 함수를 결합해 주어진 조건으로 데이터를 필터링해야 합니다.

출력 셀인 F5 셀에 필요한 수식은 다음과 같습니다.

Enter 키를 누르면 선택한 조건에 부합하는 고유 이름과 나이가 배열 형태로 반환됩니다.

ii. 여러 개의 OR 조건으로 고유 값 검색하기

이번에는 수영과 자전거 타기라는 두 가지 야외 스포츠에 모두 참가한 참가자의 고유 이름을 확인한다고 가정해 보겠습니다. 조건을 하나의 열에서 지정하는 경우에는 FILTER 함수 안에서 두 조건을 숫자 덧셈으로 연결하면 됩니다.

F5 셀에 입력할 수식은 다음과 같습니다.

Enter 키를 누르면 아래 스크린샷처럼 고유 데이터가 바로 추출됩니다.

iii. 빈 셀을 제외한 고유 값 목록 가져오기

데이터 세트에 빈 셀이나 빈 행이 포함되어 있을 수 있습니다. 이런 빈 행을 건너뛰고 표에서 고유 데이터만 추출하려면 수식에 비교 연산자인 같지 않음(<>)을 사용해야 합니다.

출력 셀인 F5 셀에 필요한 수식은 다음과 같습니다.

Enter 키를 누르면 빈 셀이 제외된 고유 행만 출력 테이블에 반환됩니다.

5. 지정된 열에서 고유 값 목록 찾기

특정 몇 개의 열에서만 고유 데이터를 추출하고 싶은 경우가 있을 수 있습니다. 하지만 마우스 커서로 떨어져 있는 여러 열을 동시에 선택해 UNIQUE 함수의 인수로 직접 입력하는 것은 불가능합니다. 이럴 때 CHOOSE 함수와 UNIQUE 함수를 결합하면 됩니다. CHOOSE 함수는 인덱스 번호를 기준으로 값 목록에서 원하는 열이나 셀 범위를 자유롭게 선택할 수 있게 해줍니다.

여기서는 데이터 테이블에서 고유한 참가자 이름을 추출하고, 이름(B열)과 종목명(D열) 두 열의 결과만 표시해 보겠습니다.

F5 셀에 입력할 수식은 다음과 같습니다.

Enter 키를 누르면 아래 그림처럼 서로 떨어져 있는 두 열에서 고유한 이름과 해당 종목명이 반환됩니다.

CHOOSE 함수 내부의 인덱스 번호는 1과 3입니다. 즉, 값 목록에서 1번째와 3번째 셀 범위가 선택됩니다. 이후 UNIQUE 함수는 지정된 열만 대상으로 삼아 해당 열의 고유 데이터를 배열로 반환합니다.

6. 고유 목록 생성 시 IFERROR 함수 활용하기

UNIQUE 함수 뒤에 IFERROR 함수를 사용하면 반환값에 오류가 발생했을 때 사용자 지정 메시지를 표시할 수 있습니다. 예를 들어, 21세 미만 참가자의 고유 이름을 알고 싶다고 가정해 보겠습니다. 21세 미만 참가자가 한 명도 없다면 수식은 #N/A 오류를 반환하게 됩니다. 하지만 오류를 그대로 보여주는 대신, IFERROR 함수를 사용해 "찾을 수 없음"이라는 사용자 지정 메시지를 표시하도록 설정할 수 있습니다.

따라서 F5 셀에 필요한 수식은 다음과 같습니다.

Enter 키를 누르면 아래 스크린샷과 같이 지정한 메시지가 반환됩니다.

7. 조건 기반으로 고유 목록 추출하기 (INDEX-MATCH 수식)

이제 UNIQUE 함수 대신 INDEX 함수와 MATCH 함수의 조합을 활용해 보겠습니다. INDEX 함수는 특정 행과 열이 교차하는 위치의 값 또는 셀 참조를 반환하고, MATCH 함수는 지정된 순서에서 특정 값과 일치하는 항목의 위치를 반환합니다. 여기서는 수영에 참가한 참가자의 고유 이름을 확인한다고 가정합니다.

📌 1단계:

➤ 출력 셀인 F5 셀을 선택하고 다음 수식을 입력합니다.

➤ Enter 키를 누릅니다.

첫 번째 고유 이름이 결과값으로 반환됩니다.

📌 2단계:

➤ 채우기 핸들(Fill Handle)을 사용해 셀을 오른쪽으로 드래그합니다.

그러면 참가자의 나이까지 함께 표시됩니다.

📌 3단계:

➤ G5 셀부터 열 아래로 #N/A 값이 나타날 때까지 채우기를 진행합니다.

이렇게 하면 INDEX-MATCH 수식을 통해 주어진 조건에 맞는 고유 데이터를 추출할 수 있습니다.

🔎 수식 작동 원리:

• COUNTIF($F$4:$F4, $B$5:$B$13): COUNTIF 함수는 B5:B13 범위에 있는 모든 셀을 저장하고 계산합니다. 이 함수는 다음과 같은 결과를 반환합니다.

{0;0;0;0;0;0;0;0;0}

• IF($F$12=$D$5:$D$13, COUNTIF($F$4:$F4, $B$5:$B$13), ""): IF 함수가 셀에서 주어진 조건을 검색하며 다음과 같은 결과를 반환합니다.

{0;"";0;"";"";0;"";"";""}

• MATCH 함수는 이전 단계에서 찾은 셀의 행 번호를 반환합니다.
• 마지막으로 INDEX 함수가 해당 행 번호를 기준으로 데이터를 추출합니다.

8. 여러 조건을 기반으로 고유 목록 준비하기

이 섹션에서는 여러 조건이 적용될 때 INDEX-MATCH 수식이 어떻게 작동하는지 살펴봅니다. 예를 들어, 수영에 참가했고 25세 미만인 참가자의 고유 이름을 알고 싶다고 가정해 보겠습니다.

출력 셀인 F5 셀에 필요한 수식은 다음과 같습니다.

Enter 키를 누른 후 출력 열의 새 셀들을 자동 채우기하면 아래 그림과 같이 고유 이름들이 표시됩니다.

이 수식에서는 두 개의 IF 함수로 조건을 지정하고, COUNTIF 함수가 해당 조건들을 반영해 출력 셀 배열을 계산합니다. 앞서 설명한 방식과 마찬가지로 INDEX-MATCH 수식이 그 배열을 기반으로 결과를 반환하며, IFERROR 함수는 오류 발생 시 사용자 지정 메시지를 반환하는 역할을 합니다.

9. 조건에 따라 행과 열 방향으로 여러 고유 목록 만들기

마지막 방법에서는 엑셀 테이블을 활용해 여러 개의 고유 데이터 목록을 추출해 보겠습니다. 아래 그림에서 하단 표는 종목 유형별로 참가자의 고유 이름을 보여줍니다.

표 또는 셀 범위 (B5:C13)의 이름을 'Sports'로 지정했습니다. 열 머리글은 SportsName과 Name입니다. 이때 표의 머리글에는 공백이 포함되면 안 된다는 점을 꼭 기억하세요.

📌 1단계:

➤ 첫 번째 출력 셀인 B16 셀에 다음 수식을 입력합니다.

Enter 키를 누르면 첫 번째 결과값이 즉시 반환됩니다.

📌 2단계:

➤ 채우기 핸들(Fill Handle)을 사용해 빈 셀이 나타날 때까지 열 아래로 채웁니다.

📌 3단계:

➤ 첫 번째 열의 출력 결과(B16:B20) 셀 범위를 복사합니다.

➤ C16 셀에 수식(Formulas, F) 옵션을 선택해 붙여넣습니다.

그러면 자전거 타기에만 참가한 참가자의 고유 이름이 담긴 두 번째 열이 완성됩니다.

📌 4단계:

➤ 같은 방식으로 복사한 값을 D16, E16 셀에도 수식으로 붙여넣습니다.

그러면 종목 유형별 나머지 고유 이름들도 바로 얻을 수 있습니다. 단, 여기서 주의할 점은 B열의 첫 번째 결과값에서 오른쪽 방향으로 채우기 핸들을 사용해 자동 채우기를 하면 안 된다는 것입니다. 자동 채우기를 사용하면 데이터가 왜곡되어 원래의 출력 결과를 얻을 수 없습니다.

🔎 수식 작동 원리:

• IF(Sports[SportsName]=B$15, ROW(Sports)-4): 수식의 이 부분은 B15 셀의 머리글로 지정된 조건을 검색하며 다음과 같은 결과를 반환합니다.

{1;FALSE;FALSE;4;FALSE;FALSE;7;8;FALSE}

• SMALL(IF(Sports[SportsName]=B$15, ROW(Sports)-4), ROW(1:1)): SMALL 함수는 이전 출력값에서 가장 작은 숫자를 추출하며, C16 셀의 경우 '1'이 됩니다.
• INDEX 함수는 SMALL 함수가 지정한 행 번호를 기준으로 이름을 가져옵니다.
• IFERROR 함수는 오류가 발생했을 때 빈 셀을 표시하는 데 사용됩니다.
• 이 결합 수식에서 "ROW(Sports)-4" 부분의 숫자 '4'는 원본 데이터 테이블에서 머리글이 위치한 행 번호입니다.

마무리

지금까지 소개한 다양한 방법들을 활용하면 엑셀 스프레드시트에서 데이터 테이블로부터 고유 값 목록을 훨씬 더 효율적으로 만들 수 있습니다. 궁금한 점이나 피드백이 있다면 댓글로 알려주세요. 이 사이트에서 엑셀 함수 관련 다른 글들도 확인해 보실 수 있습니다.

함께 읽으면 좋은 글

• 엑셀에서 글머리 기호 목록 만들기 (9가지 방법)
• 엑셀에서 한 셀 안에 목록 만들기 (3가지 빠른 방법)
• 엑셀에서 가격표 목록 만들기 (단계별 가이드)