Note: The other languages of the website are Google-translated. Back to English

Excel의 열 범위에서 고유 값 목록을 동적으로 추출하는 방법은 무엇입니까?

값이 정기적으로 변경되는 열 범위의 경우 변경 방법에 관계없이 항상 범위에서 모든 고유 값을 가져와야합니다. 고유 값의 동적 목록을 만드는 방법은 무엇입니까? 이 기사는 그것을 다루는 방법을 보여줄 것입니다.

수식을 사용하여 열 범위에서 고유 값 목록을 동적으로 추출
VBA 코드를 사용하여 열 범위에서 고유 값 목록을 동적으로 추출


수식을 사용하여 열 범위에서 고유 값 목록을 동적으로 추출

아래 스크린 샷과 같이 B2 : B9 범위에서 고유 한 값 목록을 동적으로 추출해야합니다. 다음 배열 수식을 시도하십시오.

1. D2와 같은 빈 셀을 선택하고 아래 수식을 입력하고 Ctrl 키 + 변화 + 엔터 버튼 동시에 키. (B2 : B9는 고유 값을 추출하려는 열 데이터이고 D1은 수식이있는 위 셀입니다.)

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

2. D2 셀을 계속 선택한 다음 채우기 핸들을 아래로 끌어 지정된 범위에서 모든 고유 값을 가져옵니다.

이제 열 범위 B2 : B9의 모든 고유 값이 추출됩니다. 이 범위의 값이 변경되면 고유 값 목록이 즉시 동적으로 변경됩니다.

Excel에서 범위의 모든 고유 값을 쉽게 선택하고 강조 표시합니다.

또한 중복 및 고유 셀 선택 ~의 유용성 Excel 용 Kutools 모든 고유 값 (첫 번째 중복 포함) 또는 한 번만 표시되는 고유 값을 쉽게 선택하고 강조 표시 할 수 있으며 아래 스크린 샷과 같이 필요한 중복 값도 선택할 수 있습니다.
지금 Excel용 Kutools를 다운로드하세요! (30-하루 무료 트레일)


VBA 코드를 사용하여 열 범위에서 고유 값 목록을 동적으로 추출

다음 VBA 코드를 사용하여 열 범위에서 동적으로 고유 값 목록을 추출 할 수도 있습니다.

1. 프레스 다른 + F11 동시에 열려면 키 응용 프로그램 용 Microsoft Visual Basic 창.

2. 에서 응용 프로그램 용 Microsoft Visual Basic 창을 클릭합니다 끼워 넣다 > 모듈. 그런 다음 아래 VBA 코드를 복사하여 모듈 창.

VBA 코드 : 범위에서 고유 값 목록 추출

Sub CreateUniqueList()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
On Error Resume Next
Set xRng = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
On Error Resume Next
xRng.Copy Range("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
For I = 1 To xLastRow2
  If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then
     ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
  End If
Next
End Sub

주의 사항: 코드에서 D2는 고유 값 목록을 찾을 셀입니다. 필요에 따라 변경할 수 있습니다.

3. 워크 시트로 돌아가서 끼워 넣다 > 모양 > 구형. 스크린 샷보기 :

4. 워크 시트에 직사각형을 그린 다음 표시 할 단어를 입력합니다. 그런 다음 마우스 오른쪽 버튼으로 클릭하고 매크로 지정 오른쪽 클릭 메뉴에서. 에서 매크로 지정 대화 상자에서 고유 목록 만들기 에서 매크로 이름 상자를 클릭 한 다음 OK 단추. 스크린 샷보기 :

5. 이제 사각형 버튼을 클릭합니다. Excel 용 Kutools 대화 상자가 나타나면 추출해야하는 고유 값이 포함 된 범위를 선택한 다음 OK 버튼을 클릭합니다.

이제부터는 위의 5 단계를 반복하여 고유 값 목록을 자동으로 업데이트 할 수 있습니다.


관련 기사:


최고의 사무 생산성 도구

Excel용 Kutools는 대부분의 문제를 해결하고 생산성을 80% 증가시킵니다.

  • 재사용: 빠르게 삽입 복잡한 공식, 차트 그리고 이전에 사용한 모든 것; 셀 암호화 암호로; 메일 링리스트 생성 이메일 보내기 ...
  • 슈퍼 포뮬러 바 (여러 줄의 텍스트와 수식을 쉽게 편집 할 수 있습니다.) 레이아웃 읽기 (많은 수의 셀을 쉽게 읽고 편집합니다.) 필터링 된 범위에 붙여 넣기...
  • 셀 / 행 / 열 병합 데이터 손실없이; 셀 내용 분할; 중복 행 / 열 결합... 중복 셀 방지; 범위 비교...
  • 중복 또는 고유 선택 행; 빈 행 선택 (모든 셀이 비어 있음); 슈퍼 찾기 및 퍼지 찾기 많은 통합 문서에서; 무작위 선택 ...
  • 정확한 사본 수식 참조를 변경하지 않고 여러 셀; 참조 자동 생성 여러 시트에; 글 머리 기호 삽입, 확인란 등 ...
  • 텍스트 추출, 텍스트 추가, 위치 별 제거, 공간 제거; 페이징 부분합을 만들고 인쇄합니다. 셀 내용과 주석 간 변환...
  • 슈퍼 필터 (다른 시트에 필터 구성표 저장 및 적용) 고급 정렬 월 / 주 / 일, 빈도 등 특수 필터 굵은 기울임 꼴로 ...
  • 통합 문서와 워크 시트 결합; 키 열을 기반으로 테이블 병합; 데이터를 여러 시트로 분할; xls, xlsx 및 PDF 일괄 변환...
  • 300개 이상의 강력한 기능. Office / Excel 2007-2021 및 365를 지원합니다. 모든 언어를 지원합니다. 기업 또는 조직에서 쉽게 배포할 수 있습니다. 전체 기능은 30일 무료 평가판입니다. 60일 환불 보장.
kte 탭 201905

Office Tab은 Office에 탭 인터페이스를 제공하여 작업을 훨씬 쉽게 만듭니다.

  • Word, Excel, PowerPoint에서 탭 편집 및 읽기 사용, Publisher, Access, Visio 및 Project.
  • 새 창이 아닌 동일한 창의 새 탭에서 여러 문서를 열고 만듭니다.
  • 생산성을 50% 높이고 매일 수백 번의 마우스 클릭을 줄입니다!
officetab 하단
코멘트 (35)
아직 평가가 없습니다. 가장 먼저 평가하세요!
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
튜토리얼 주셔서 감사합니다. 수식 방법을 사용하여 범주 한정자를 추가하려는 경우 수식을 어떻게 변경합니까? C열에서 그 품목이 과일인지 채소인지 구별한다고 말하십시오. 고유한 과일만 정렬하고 야채는 제외하도록 코드를 어떻게 변경하시겠습니까? (LIST RANGE,"CATEGORY")의 두 번째 countif 기준을 사용하여 COUNTIF를 COUNTIFS로 바꾸려고 시도했지만 공백을 반환합니다. 어레이를 확장하고 VLOOKUP을 통합해야 합니까?
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
나는 엑셀에 능숙하지만 위의 공식이 작동하는 방법과 이유에 대해 머리를 감싸려고 노력하고 있습니다. 때때로 배열을 사용하는 것이 약간 혼란스러워서 바보 용어로 된 설명은 매우 도움이 될 것입니다.
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
이 공식은 구식이며 작동하지 않습니다. 나는 말 그대로 이 공식이 작동하도록 할 수 있는지 확인하기 위해 이 정확한 Excel 시트를 설정했습니다.
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
이봐,
어떤 Office 버전을 사용하십니까?
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
{=INDEX($Q$3:$Q$263,MATCH(0,COUNTIF(V$2:V2,$Q$3:$Q$263),0))} - 다른 사이트에서 작동하는 것으로 나타났습니다...

Ctrl+Shift+Enter를 사용하여 배열 함수(중괄호)를 가져옵니다. #NA가 표시될 때까지 수식을 복사하여 붙여넣습니다. 내 데이터 세트는 Column-Q에 있었고, 동일한 열을 따라 계속 확장되는 Column-V의 고유한 목록에 존재하는지 확인하기 위해 비교되었습니다.
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
좋은 날.
열 Q의 모든 고유 값을 abobv 공식으로 나열한 다음 공식 =IF(D2=V1,"Match","No match")를 사용하여 동일한 행의 V 열과 비교하여 Q 열의 고유 값을 비교하십시오. .
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
안녕하세요. 도움을 주셔서 감사합니다.

정확히 이 기능이 필요하지만 "고유 값" 목록이 행 대신 열 전체로 확장되어야 하므로 행 아래로 확장 목록이 작동하지 않습니다.

열을 가로질러 드래그할 때 "고유 값" 목록이 확장되도록 이 수식을 수정하려면 어떻게 해야 합니까?

오프셋()?
바꾸어 놓다()?
행 대신 열에 대한 참조와 연결된 절대 참조 문자열이 있는 간접()?


다시 한 번 고마워요!
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
친애하는 라이언,
이 수식은 =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$2:D2, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter 수 문제를 해결하는 데 도움이 됩니다.
아래 스크린샷 참조:
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
또한 어떤 이유로든 원래 공식은 다음을 제공했습니다.
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

"순환 참조" 경고를 반환하고 계산하지 않습니다.
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
친애하는 라이언,
어떤 Office 버전을 사용하십니까? 수식은 내 Office 2016 및 2013에서 잘 작동합니다.
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
이전에 이런 일이 발생한 적이 있습니다. 수정한 것은 D1 셀에 수식을 입력하는 것이었습니다(사용하고 있던 워크시트와 동일). $D:$1이 어느 셀에 해당하든 아래 셀(D2)에 입력해야 합니다. 그것이 오류가 발생한 이유가 아니라면 사과드립니다.
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
macOS용 Excel 2016에서 VBA 옵션을 사용하는 방법에 대한 팁이 있습니까? 나는 단계를 따랐다. 그러나 매크로를 실행하면 아무 일도 일어나지 않습니다. 감사해요!
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
대어 존스,
아래 VBA 코드를 시도하고 그것이 당신을 위해 작동하는지 알려주십시오. 고맙습니다!

하위 CreateUniqueList()
범위로 희미한 xRng
Dim xLastRow As Long
xLastRow2를 길게 흐리게
Dim I as 정수
' 오류 시 다음 재개
xRng 설정 = Application.InputBox("범위를 선택하세요:", "Kutools for Excel", Selection.Address, , , , , 8)
xRng가 아무것도 아닌 경우 Sub를 종료하십시오.
오류에 대한 다음 재개
xRng.복사 범위("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates 열:=1, 헤더:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
I = 1에서 xLastRow2까지
ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = ""인 경우
ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
END IF
다음
최종 하위
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
안녕하세요 크리스탈님,
고유 값 목록의 VB 버전을 사용하려고 하는데 문제가 발생합니다.
고유 값 열을 만들려는 범위는 다른 탭을 참조하는 모든 수식입니다.
공식 대신 전달할 값을 어떻게 얻습니까?
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
친애하는 마이크,
공식 참조를 절대값으로 변환한 다음 VB 스크립트를 적용하십시오.
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
내 수식이 열 이름을 참조하고 절대로 변환할 수 없다는 점을 제외하면 동일한 문제가 있습니다.
수식이 아닌 값을 붙여넣도록 vba를 어떻게 변경합니까?
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
날짜가 9/12일 때만 동적 목록에 추가하려는 경우와 같이 여러 기준을 추가하려면 어떻게 해야 합니까?

MATCH 수식에서 "&"를 시도하고 있지만 작동하지 않습니다.

예를 들면 다음과 같습니다.
=IFERROR(INDEX($B$2:$B$9, MATCH(0 & B4,COUNTIF($D$1:D1, $B$2:$B$9) & $A$2:$A$9, 0)),"" )
이렇게 하면 오류가 발생하거나 중복이 생성됩니다.

또는 "+"가 작동할 수는 있지만 작동하지 않는다는 것을 읽었습니다. 또는 SMALL을 사용합니다.

아이디어?
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
친애하는 잭,
도움을 드릴 수 없어 죄송합니다. 포럼에 질문을 게시할 수 있습니다. https://www.extendoffice.com/forum.html 전문가로부터 더 많은 Excel 지원을 받으려면
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
두 번째 변수를 어떻게 추가하시겠습니까? 예를 들어, 한 열의 모든 고유한 요소가 다른 열에서도 유사한 값을 공유하기를 원합니다. 귀하의 예에서 제품, 육류 등과 같은 값을 갖는 "부서"라는 제목의 세 번째 열을 상상해 보십시오. 그것들이 모두 생산품이라는 것을 알고 있지만 제 요점을 이해하시기 바랍니다. CountIF 수식을 COUNTIFS로 수정하시겠습니까, 아니면 다른 방식으로 수정하시겠습니까?
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
안녕 매트
이 공식을 시도하십시오. =IF(ISNA(VLOOKUP(A2,$C$2:$C$13,1,FALSE)),"예","").
두 개의 비교 목록이 A열과 C열이라고 가정하고 고유한 값이 A열에만 있고 C열에는 없으면 B열에 Yes로 표시됩니다. B 열에 아무것도 반환하지 않으면 해당 값이 A 열과 C 열 모두에 유지된다는 의미입니다.
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
답장을 보내주셔서 감사합니다. 하지만 YES가 표시되면 고유한 값을 꺼내는 것이 좋습니다. 다른 열에서 고유한 값을 가져오는 수식을 조언해 주시겠습니까?
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
Mac의 최신 Excel 버전에서 천 행 Excel 시트에 대해 이 작업을 수행하면 절대 반환되지 않습니다. 첫 번째 행은 작동하지만 아래로 복제하면 Excel이 현재 XNUMX시간 이상 값을 반환하지 않는 계산 모드로 들어갑니다.

2 또는 50개의 고유 값을 반환하는 큰 목록(최대 60k 행)에 대해 이 작업을 수행하는 방법에 대한 생각이 있습니까?

"Numbers" 앱에서 이것을 조롱했고 계산하는 데 몇 분 밖에 걸리지 않아 완벽하게 작동합니다. Excel에서 시간이 너무 오래 걸려서 완료될지 궁금합니다. 어떤 일이 일어날지 보기 위해 밤새 "실행"하도록 할 계획입니다.
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
계산 옵션을 확인하십시오. 자동으로 설정해야 합니다. 파일 > 옵션 > 수식 > 계산 옵션 > 통합 문서 계산(자동 선택)
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
다른 크기의 데이터 세트를 입력하고 아무 것도 조정할 필요가 없도록 실제 데이터를 지나 수식을 아래로 끌어내려 합니다. 그러나 실제 데이터가 끝난 후 마지막 행은 항상 "0"을 반환합니다. 인접한 열의 다른 항목에 대해 고유한 값을 사용하고 있으며 0으로 인해 마지막 값이 반복됩니다(0을 삭제하면 값이 더 이상 반복되지 않음). 이 문제를 해결하는 방법을 알고 있습니까? 또한 Office 365 Business를 사용하고 있습니다.
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
안녕하세요, 도와주셔서 감사합니다.
이제 어떻게 내 값도 알파벳순으로 정렬할 수 있습니까? (내 마스터 테이블에서 필터를 사용하고 싶지 않습니다)
COUNTIF 대신 COUNTIFS를 사용해야 합니까?
도와주세요
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
안녕하세요 알렉시스,
죄송합니다. 추출된 값을 수식과 함께 알파벳순으로 동시에 정렬할 수 없습니다. 당신의 의견에 감사드립니다.
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"") 수식을 사용하고 있습니다. 하나의 열에 대한 데이터이지만 내 데이터는 다양한 열과 행에 걸쳐 있습니다. 전체 영역을 포함하도록 공식을 편집할 수 있습니까? 내 데이터는 AC4에서 AR60까지 ...
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
나는 VBA 코드와 공식을 시도합니다. 코드 VBA는 잘 작동하지만 매크로가 있는 파일을 유지할 수 없습니다. 그러나 문제는 공식을 작동시킬 수 없다는 것입니다. 아무도 생각이 없었나요? 고맙습니다
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
안녕 샬럿,
당신의 의견에 감사드립니다. 통합 문서를 Excel 매크로 사용 통합 문서로 저장하여 나중에 사용할 수 있도록 매크로가 있는 파일을 보관할 수 있습니다.
수식 문제의 경우 데이터의 스크린샷을 제공해 주시겠습니까? 당신의 의견에 감사드립니다.
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
감사
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
다른 수식이 사용된 범위에 대해 vba 코드가 작동하도록 하는 방법? 열 BI에는 D 및 E 열을 참조하는 수식이 있습니다.
코드를 L 열에 적용하면(예를 들어), (분명히 코드의 셀을 적절히 수정하면) 매크로는 M과 N 열에 적용된 수식을 반환합니다. 그러면 작동하지만 원하는 대로는 되지 않습니다! 열 B의 값을 유지하는 방법? 감사
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
Ik heb gemerkt dat de 'formule'-methode erg traag werkt bij grote dataset. Een goed alternatief is het gebruik van een draaitabel. Kies dan alleen de rijlabels, je krijgt dan een lijstje met unieke waarden. 추가 정보는 "(leeg)"의 기본 정보입니다. Je kunt deze er vervolgens uitfilteren. Helaas kun je maar op 1 기준 필터. Ook daar zijn wel weer oplossingen voor, maar dat is wat complexer.
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
두 개의 개별 열 범위(B2:B9)와 (D2:D9)를 사용하는 것이 가능하다는 점을 제외하고 정확히 동일한 작업을 수행할 수 있기를 원합니다.
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
안녕하세요 안토니,
결과를 원본 데이터와 동일한 열에 배치할 수 있습니다. 이 경우 열 B와 같습니다.
그러나 다음과 같이 수식에서 결과 셀의 맨 위 셀을 참조해야 합니다.
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($B$11:B11, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter
이 댓글은 사이트의 중재자에 의해 최소화되었습니다
con este procedimiento de filtro se hace de forma muy Rapida

1.EN ESTE EJEMPLO los datos a remover los duplicados estan en la col A de la fila 59 a la 239
2. se 정의 un criterio de filtrado en este caso en la fila d56 el mismo titulo de la lista a remover duplicados y la d57 la dejo en blanco
3. una vez ejecutado se muestran los datos en la fila destino, que en mi caso fue la d59

범위("A59:A239").AdvancedFilter 작업:=xlFilterCopy, CriteriaRange:=범위 _
("D56:D57"), CopyToRange:=Range("D59"), 고유:=True
아직 코멘트가 없습니다
여러분의 의견을 남겨
게스트로 게시
×
이 게시물 평가 :
0   등장 인물
추천 위치

Follow Us

저작권 © 2009 - WWW.extendoffice.com. | 판권 소유. 에 의해 구동 ExtendOffice. | | 사이트 맵
Microsoft 및 Office 로고는 미국 및 / 또는 기타 국가에서 Microsoft Corporation의 상표 또는 등록 상표입니다.
Sectigo SSL로 보호