기준에 따라 Excel에서 고유 목록 만들기(9가지 방법)

Microsoft Excel에서는 여러 기준에 따라 고유한 목록을 만드는 데 여러 가지 방법을 사용할 수 있습니다. 고유 목록은 일반적으로 테이블에서 중복을 제거하기 위해 준비됩니다. 이 기사에서는 다양한 방법을 사용하여 여러 조건에 따라 고유한 목록을 생성하는 방법을 배웁니다.

이 기사를 준비하는 데 사용한 Excel 통합 문서를 다운로드할 수 있습니다.

기준에 따라 Excel에서 고유한 목록을 만드는 9가지 방법

1. 여러 열 기준으로 고유한 행 목록 만들기

다음 사진에서 왼쪽 테이블에는 여러 스포츠 이름, 참가자 및 연령이 무작위로 표시되어 있습니다. 오른쪽에 있는 또 다른 테이블은 고유한 값만을 기반으로 데이터를 추출하는 출력 테이블입니다. 예를 들어 고유한 참가자의 이름과 연령만 알고 싶습니다.

UNIQUE 범위 또는 배열에서 고유한 값을 반환하는 함수입니다. 이 함수의 일반 공식은 다음과 같습니다.

=UNIQUE(배열, [by_col], [exactly_once])

고유한 기능은 Excel 365에서만 사용할 수 있습니다. , 결론으로 ​​넘어가기 전에 마지막 3개 섹션에서 다른 기존 기능의 조합으로 대체 방법도 보여줍니다.

따라서 F5 셀에서 필요한 기능은 다음이 될 것입니다:

Enter 키를 누른 후 , 함수는 참가자의 해당 연령과 함께 고유한 이름을 가진 배열을 반환합니다.

자세히 보기:Excel에서 기준에 따라 목록을 생성하는 방법(4가지 방법)

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

SORT를 사용하여 알파벳순으로 정렬된 고유 값을 얻을 수도 있습니다. 기능. 오름차순 (A to Z) 으로 정렬 순서를 나타냄 또는 내림차순 (Z에서 A) 기준에 따라 참가자 이름을 이니셜을 기준으로 재정렬할 수 있습니다.

출력 셀 E5에서 , SORT 가 있는 필수 공식 및 고유 기능은 다음과 같습니다.

Enter 키를 누른 후 , 이 함수는 A부터 Z까지 알파벳 순서로 정렬된 배열을 반환합니다. .

자세히 알아보기: 엑셀에서 알파벳순으로 나열하는 방법(3가지 방법)

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

두 열에서 고유한 데이터를 추출한 다음 값을 함께 결합하여 단일 열에서 연결된 값을 반환한다고 가정해 보겠습니다. 다음 그림에서 고유한 이름과 해당 연령은 특정 구분 기호(쉼표(,))를 사용하여 열에 함께 표시됩니다. 그리고 앰퍼샌드(&)를 사용합니다. 두 개의 다른 열과 함께 값을 연결하려면 여기를 클릭하세요.

출력 셀 F5의 필수 수식 다음이 될 것입니다:

수식은 단일 열에 고유한 행의 배열을 반환합니다.

자세히 알아보기: Excel에서 쉼표로 구분된 목록을 만드는 방법(5가지 방법)

4. 기준이 있는 고유 값 목록 만들기(UNIQUE-FILTER 공식)

나. Excel의 여러 AND 기준을 기반으로 고유한 값 식별

이 섹션에서는 몇 가지 기준을 추가하고 해당 조건을 기반으로 고유한 데이터를 추출합니다. 예를 들어 수영만 참가한 25세 미만 참가자의 고유 이름을 알고 싶습니다. 따라서 UNIQUE 및 필터링 주어진 기준으로 데이터를 필터링하는 기능입니다.

출력 셀 F5에 필요한 수식 다음이 될 것입니다:

Enter 키를 누른 후 , 수식은 선택한 기준에 따라 고유한 이름과 해당 연령이 포함된 배열을 반환합니다.

ii. 여러 OR 기준에 따라 고유한 값 검색

이제 야외 스포츠인 수영과 사이클링에 모두 참가한 고유 참가자의 이름을 알고 싶다고 가정해 보겠습니다. 하나의 열에서만 기준을 할당하므로 FILTER 에서 두 조건을 결합하기 위해 단순히 숫자 덧셈을 사용해야 합니다. 기능.

F5 셀의 필수 수식 지금:

Enter 키를 누릅니다. 다음 스크린샷과 같이 추출된 고유 데이터를 바로 얻을 수 있습니다.

iii. 빈 셀을 무시하고 고유 값 목록 가져오기

데이터 세트에는 여러 개의 빈 셀 또는 행이 포함될 수 있습니다. 따라서 빈 행을 건너뛰고 테이블에서 고유한 데이터를 추출하려면 비교 연산자를 사용해야 합니다. 다음과 같지 않음(<>) 공식에서.

출력 F5 셀 의 필수 수식 다음이 될 것입니다:

Enter 키를 누른 후 , 수식은 출력 테이블에 공백이 아닌 고유한 행만 있는 다음 배열을 반환합니다.

유사한 수치

• Excel에서 할 일 목록을 만드는 방법(3가지 쉬운 방법)
• Excel에서 메일링 리스트 만들기(2가지 방법)

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

여러 특정 열에서만 고유한 데이터를 추출한다고 가정합니다. 마우스 커서로 이러한 고유한 열을 선택하고 UNIQUE 에 대한 인수로 입력할 수 없습니다. 기능. 따라서 CHOOSE를 결합해야 합니다. 여기에서 UNIQUE 기능 선택 기능 함수를 사용하면 값 목록에서 인덱스 번호를 기반으로 지정된 열 또는 셀 범위를 원하는 수만큼 선택할 수 있습니다.

데이터 테이블에서 고유한 참가자의 이름을 추출하고 이름(B열)의 결과를 표시합니다. 및 스포츠 이름(D열) 열만.

F5 셀의 필수 수식 다음이 될 것입니다:

Enter 키를 누른 후 , 수식은 아래 그림과 같이 두 개의 개별 열에서 고유한 이름과 해당 스포츠의 이름을 반환합니다.

선택 내부 함수에서 색인 번호는 1과 3입니다. 이는 셀의 첫 번째 및 세 번째 범위가 값 목록에서 선택되어야 함을 의미합니다. 그런 다음 UNIQUE 함수는 지정된 열만 고려하고 배열의 해당 열에서 고유한 데이터를 반환합니다.

자세히 보기:Excel에서 번호 매기기 목록을 만드는 방법(8가지 방법)

6. 고유 목록 생성 시 IFERROR 함수 사용

IFERROR를 사용할 수도 있습니다. 고유 뒤에 있는 기능 반환 값에 오류가 있는 경우 사용자 정의된 메시지를 표시하는 함수입니다. 21세 미만의 고유한 참가자의 이름을 알고 싶다고 가정해 보겠습니다. 21세 미만의 참가자는 인스턴스가 없으므로 수식은 #N/A를 반환해야 합니다. 오류. 그러나 이 오류를 표시하는 대신 IFERROR 사용자 정의된 메시지를 표시하는 기능 - "찾을 수 없음".

따라서 F5 셀에 필요한 수식은 다음이 될 것입니다:

Enter 키를 누른 후 , 수식은 다음 스크린샷과 같이 지정된 메시지를 반환합니다.

7. 기준(INDEX-MATCH 공식)에 따라 고유 목록 추출

이제 UNIQUE 기능 및 INDEX 조합 적용 및 매치 기능. 색인 함수는 특정 행과 열의 교차점에 있는 셀의 값 또는 참조를 반환합니다. 그리고 MATCH 함수는 지정된 순서에서 지정된 값과 일치하는 항목의 위치를 ​​반환합니다. 수영만 참가한 참가자들의 고유한 이름을 알고 싶다고 가정합니다.

📌 1단계:

➤ 출력 셀 F5를 선택합니다. 및 유형:

➤ Enter 키를 누릅니다. .

첫 번째 고유한 이름을 반환 값으로 받게 됩니다.

📌 2단계:

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

참가자의 나이도 확인할 수 있습니다.

📌 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), ""): 만약 함수는 셀에서 주어진 기준을 검색하고 다음과 같이 반환합니다.

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

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

8. 여러 기준에 따라 Excel에서 고유한 목록 준비

이 섹션에서는 INDEX-MATCH 수식은 여러 조건이 적용될 때 작동합니다. 예를 들어 수영에 참가한 25세 미만 참가자의 고유한 이름을 알고 싶습니다.

출력 셀 F5의 필수 수식 다음이 될 것입니다:

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

이 공식에서 두 개의 IF 기준을 할당했습니다. 함수 및 COUNTIF 함수는 출력 셀의 배열을 추출하는 동안 이러한 기준을 고려합니다. 이전 방법에서 설명한 것처럼 INDEX-MATCH 수식은 해당 배열을 기반으로 출력을 반환합니다. 그리고 IFERROR 여기서 함수는 오류가 발견되면 사용자 정의된 메시지를 반환하는 데 사용되었습니다.

9. 기준이 있는 행과 열을 따라 여러 고유 목록 만들기

마지막 방법에서는 Excel 테이블을 사용하여 고유한 데이터 목록을 여러 개 추출합니다. 다음 그림에서 하단의 표는 스포츠 유형에 따라 참가자의 고유한 이름을 보여줍니다.

테이블 또는 셀 범위의 이름을 (B5:C13) 지정했습니다. 스포츠와 함께. 열에는 이름이 SportsName 및 Name인 헤더가 있습니다. 테이블의 헤더는 공간을 차지할 수 없다는 점에 유의해야 합니다.

📌 1단계:

➤ 첫 번째 출력 셀 B16 의 필수 수식 다음이 될 것입니다:

Enter 키를 누른 후 , 첫 번째 출력 값이 즉시 반환됩니다.

📌 2단계:

➤ 채우기 핸들 사용 빈 셀이 나타날 때까지 열을 채우십시오.

📌 3단계:

➤ 이제 출력에서 ​​셀 범위를 복사합니다. (B16:B20) 첫 번째 열에서.

➤ C16 셀에서 , 수식(F)으로 값을 붙여넣습니다. 옵션.

사이클링에만 참가한 참가자의 고유한 이름이 있는 두 번째 열이 표시됩니다.

📌 4단계:

➤ 마찬가지로 복사한 값을 셀 D16 및 E16에 수식으로 다시 붙여넣습니다. .

그리고 스포츠 유형에 따라 참가자의 다른 고유 이름을 즉시 얻을 수 있습니다. 그러나 여기서 채우기 핸들을 단순히 사용할 수 없다는 점을 기억해야 합니다. B열의 첫 번째 반환 값에서 오른쪽으로 셀 자동 채우기 . 자동 완성 사용 옵션을 사용하면 데이터가 조작되고 원본 출력을 추출할 수 없습니다.

🔎 공식은 어떻게 작동합니까?

• IF(스포츠[SportsName]=B$15, 행 (스포츠)-4): 함수의 이 부분은 셀 B15의 헤더에 지정된 주어진 조건을 검색합니다. 함수는 다음과 같이 반환됩니다.

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

• SMALL(IF(Sports[SportsName]=B$15, ROW(Sports)-4), ROW(1:1)): 작은 함수는 이전 출력과 C16 셀에서 가장 작은 숫자를 추출합니다. , '1'입니다. .
• 색인 함수는 SMALL 에 지정된 행 번호를 기반으로 이름을 가져옵니다. 기능.
• IFERROR 함수는 오류 출력이 발견되면 빈 셀을 표시하는 데 사용되었습니다.
• 이 결합된 공식에서 숫자 '4' "ROW(Sports)-4" 부분에서 기본 데이터 테이블에 있는 헤더의 행 번호입니다.

결말

위에 설명된 모든 방법이 이제 데이터 테이블에서 고유한 값 목록을 생성하면서 Excel 스프레드시트에 더 효과적으로 적용하는 데 도움이 되기를 바랍니다. 질문이나 피드백이 있으면 댓글 섹션에 알려주십시오. 또는 이 웹사이트에서 Excel 기능과 관련된 다른 기사를 확인할 수 있습니다.

탐색을 좋아할 수도 있습니다.

• Excel에서 글머리 기호 목록을 만드는 방법(9가지 방법)
• Excel에서 셀 안에 목록 만들기(3가지 빠른 방법)
• Excel에서 가격표를 만드는 방법(단계별 지침)