수식 만 사용하여 Excel에서 고유 한 값 가져 오기

수식 만 사용하여 Excel에서 고유 한 값 가져 오기

---

Excel에서 고유 값 목록을 수식으로 "계산"하는 방법을 알고 있습니까?

예를 들면 : 수직 범위는 값이 포함 "red", "blue", "red", "green", "blue", 나는 결과로 갖고 싶어 , , , + 결국이 다른 빈 세포를."black"
"red"blue""green""black"

INDEX와 결합 된 SMALL 또는 LARGE를 사용하여 계산 된 정렬 된 목록을 얻는 방법을 이미 찾았지만 VBA를 사용하지 않고이 계산 된 정렬도 갖고 싶습니다.

---

이것은 구식이며 몇 가지 해결책이 있지만 내가 만난 다른 어떤 것보다 더 짧고 간단한 공식을 생각해 냈으며 지나가는 사람에게 유용 할 수 있습니다.

색상 목록 Colors(A2 : A7)의 이름을 지정했으며 C2 셀에 넣은 배열 수식 은 다음과 같습니다 ( fixed ).

=IFERROR(INDEX(Colors,MATCH(SUM(COUNTIF(C$1:C1,Colors)),COUNTIF(Colors,"<"&Colors),0)),"")

C2에Ctrl+Shift+Enter 수식을 입력하고 C2를 C3 : C7에 복사 하는 데 사용 합니다 .

샘플 데이터에 대한 설명 { "red"; "푸른"; "빨간"; "초록"; "푸른"; "검정"}:

1. COUNTIF(Colors,"<"&Colors)데이터 {4; 1; 4; 3; 1; 0}의 각 항목보다 작은 값의 개수가있는 배열 (# 1)을 반환합니다 (검정색 = 0 항목 더 작음, 파란색 = 1 항목, 빨간색 = 4 항목 ). 이것은 각 항목에 대한 정렬 값 으로 변환 될 수 있습니다 .
2. COUNTIF(C$1:C...,Colors)이미 정렬 된 결과에있는 각 데이터 항목에 대해 1이있는 배열 (# 2)을 반환합니다. C2에서는 {0; 0; 0; 0; 0; 0}을 반환하고 C3에서는 {0; 0; 0; 0; 0; 1}을 반환합니다. "검정색"은 정렬에서 첫 번째이고 데이터에서 마지막이기 때문입니다. C4 {0; 1; 0; 0; 1; 1}에서는 "검은 색"을 나타내며 "파란색"의 모든 항목이 이미 존재합니다.
3. SUM복귀 k 번째 정렬 값 이미 (배열 2의 합계) 존재하는 모든 작은 값의 발생을 카운트함으로써.
4. MATCH k 번째 정렬 값의 첫 번째 인덱스 (배열 # 1의 인덱스)를 찾습니다.
5. 는 IFERROR숨기기 만입니다 #N/A(가) 고유의 목록이 완료 분류 할 때, 바닥 세포에서 오류가 발생했습니다.

얼마나 많은 고유 항목이 있는지 알아 보려면 다음 정규식을 사용할 수 있습니다 .

=SUM(IF(FREQUENCY(COUNTIF(Colors,"<"&Colors),COUNTIF(Colors,"<"&Colors)),1))

---

좋아, 두 가지 아이디어가 있습니다. 그들 중 하나가 당신이 가야 할 곳으로 데려다 줄 것입니다. 첫 번째 솔루션은 예쁘지 않기 때문에 공식으로 이것을 수행하라는 요청을 무시합니다. 나는 쉬운 방법이 정말로 당신을 위해 작동하지 않을 것이라고 생각했습니다. ^).

고급 필터 명령 사용

1. 목록을 선택하십시오 (또는 목록의 아무 곳에 나 선택 항목을 놓고 Excel이 목록에 머리글이 있는지 여부를 알 수 없다는 대화 상자가 나타나면 확인을 클릭하십시오)
2. 데이터 / 고급 필터 선택
3. "목록, 전체 필터링"또는 "다른 위치로 복사"를 선택합니다.
4. "고유 레코드 만"을 클릭합니다.
5. 확인을 클릭하십시오
6. 완료되었습니다. 고유 목록이 제자리 또는 새 위치에 생성됩니다. 이 작업을 기록하여 한 줄의 VBA 스크립트를 만들어 다른 상황에서 작업 할 수 있도록 일반화 할 수 있습니다 (예 : 위에 나열된 수동 단계 없음).

수식 사용 (내가 Locksfree 솔루션을 구축하여 구멍이없는 목록으로 끝납니다)

이 솔루션은 다음주의 사항과 함께 작동합니다.

목록을 정렬해야합니다 (오름차순 또는 내림차순은 중요하지 않음). 실제로 모든 유사한 항목이 연속적이어야하지만 정렬이 해당 상태에 도달하는 가장 쉬운 방법이기 때문에 이는 매우 정확합니다.

세 개의 새 열이 필요합니다 (계산 용 새 열 두 개와 새 목록 용 새 열 한 개). 두 번째와 세 번째 열은 결합 할 수 있지만 독자에게 연습으로 남겨 두겠습니다.

다음은 솔루션 요약입니다.

1. 목록의 각 항목에 대해 그 위에있는 중복 항목 수를 계산합니다.
2. 고유 목록의 각 장소에 대해 다음 고유 항목의 색인을 계산하십시오.
3. 마지막으로 색인을 사용하여 고유 한 항목 만있는 새 목록을 만듭니다.

다음은 단계별 예입니다.

1. 새 스프레드 시트 열기
2. a1 : a6에 원래 질문에 제공된 예를 입력하십시오 ( "red", "blue", "red", "green", "blue", "black").
3. 목록 정렬 : 선택 항목을 목록에 넣고 정렬 명령을 선택합니다.
4. B 열에서 중복을 계산합니다.
   1. B1에 "= IF (COUNTIF ($ A $ 1 : A1, A1) = 1,0, COUNTIF (A1 : $ A $ 6, A1))"를 입력합니다. 셀 참조의 "$"는 다음 단계 (나머지 열 채우기)를 훨씬 쉽게 수행하므로 매우 중요합니다. "$"는 절대 참조를 나타내므로 셀 내용을 복사 / 붙여 넣을 때 참조가 업데이트되지 않습니다 (업데이트되는 상대 참조와 반대).
   2. Smart Copy를 사용하여 B 열의 나머지 부분을 채 웁니다. B1을 선택합니다. 선택 항목의 오른쪽 하단 모서리에있는 검은 색 사각형 위로 마우스를 이동합니다. 목록 하단 (B6)을 클릭하고 아래로 끕니다. 릴리스하면 관련 참조가 업데이트 된 상태로 공식이 B2 : B6에 복사됩니다.
   3. B1 : B6의 값은 이제 "0,0,1,0,0,1"이어야합니다. "1"항목은 중복을 나타냅니다.
5. C 열에서 고유 항목의 색인을 만듭니다.
   1. C1에 "= Row ()"를 입력합니다. C1 = 1을 원하지만 Row ()를 사용하면 목록이 행 1에서 시작하지 않아도이 솔루션이 작동합니다.
   2. C2에 "= IF (C1 + 1 <= ROW ($ B $ 6), C1 + 1 + INDEX ($ B $ 1 : $ B $ 6, C1 + 1), C1 + 1)"을 입력합니다. "if"는 색인이 목록 끝에 도달 할 때 #REF가 생성되는 것을 중지하는 데 사용됩니다.
   3. 스마트 카피를 사용하여 C3 : C6을 채 웁니다.
   4. C1 : C6의 값은 "1,2,4,5,7,8"이어야합니다.
6. D 열에서 새 고유 목록을 만듭니다.
   1. D1에 "= IF (C1 <= ROW ($ A $ 6), INDEX ($ A $ 1 : $ A $ 6, C1)," ")"를 입력합니다. 그리고 인덱스가 목록의 끝을 넘어갈 때 #REF 케이스를 중지하기 위해 "if"가 사용됩니다.
   2. 스마트 카피를 사용하여 D2 : D6을 채 웁니다.
   3. D1 : D6의 값은 이제 "검정", "파란색", "녹색", "빨강", "", ""이되어야합니다.

도움이 되었기를 바랍니다....

---

해결책

VBA에서 함수를 만들었으므로 이제 쉽게 수행 할 수 있습니다. 이 자습서
에서 볼 수있는 것처럼 VBA 코드 모듈 (매크로)을 만듭니다 .

1. Alt+를 누릅니다.F11
2. 에 클릭 Module에서 Insert.
3. 코드를 붙여 넣습니다.
4. 엑셀 파일 형식으로 저장 이상의 매크로 친화적 아니라고 말한다면 Excel Macro-Enabled에서 Save As.

소스 코드

Function listUnique(rng As Range) As Variant
    Dim row As Range
    Dim elements() As String
    Dim elementSize As Integer
    Dim newElement As Boolean
    Dim i As Integer
    Dim distance As Integer
    Dim result As String

    elementSize = 0
    newElement = True

    For Each row In rng.Rows
        If row.Value <> "" Then
            newElement = True
            For i = 1 To elementSize Step 1
                If elements(i - 1) = row.Value Then
                    newElement = False
                End If
            Next i
            If newElement Then
                elementSize = elementSize + 1
                ReDim Preserve elements(elementSize - 1)
                elements(elementSize - 1) = row.Value
            End If
        End If
    Next

    distance = Range(Application.Caller.Address).row - rng.row

    If distance < elementSize Then
        result = elements(distance)
        listUnique = result
    Else
        listUnique = ""
    End If
End Function

용법

=listUnique(range)셀에 들어가기만하면 됩니다. 유일한 매개 변수는 range일반 Excel 범위입니다. 예 : A$1:A$28또는 H$8:H$30.

정황

• 는 range열 수 있어야합니다.
• 함수를 호출하는 첫 번째 셀은 range시작 위치와 동일한 행에 있어야 합니다.

예

일반 케이스

1. 데이터를 입력하고 기능을 호출합니다.
   
2. 성장 시키십시오.
   
3. Voilà.
   

빈 셀 케이스

빈 셀이있는 열에서 작동합니다. 또한 이전 예제의 "2. Grow it"부분에서 수행 한 것처럼 출력이 없어야하는 위치에 셀을 넘겨 주면 (함수 호출) 함수는 아무 것도 출력하지 않습니다 (오류가 아님).

---

로터리 방법은 Excel 스프레드 시트를 Google 스프레드 시트로로드하고 Google의 UNIQUE (범위) 기능을 사용하여 원하는대로 정확히 수행 한 다음 Google 스프레드 시트를 Excel 형식으로 다시 저장하는 것입니다.

이것이 Excel 사용자에게는 실행 가능한 솔루션이 아니라는 점을 인정하지만이 방법은 기능을 원하고 Google 스프레드 시트를 사용할 수있는 모든 사람에게 유용합니다.

---

매우 오래된 질문이지만 사람들은 여전히 ​​고유 한 항목을 추출하는 공식을 사용하는 데 어려움을 겪고있는 것 같습니다. 여기에 자신의 값을 반환하는 솔루션이 있습니다.

A2 : A7 열에 "red", "blue", "red", "green", "blue", "black"이 있다고 가정 해 보겠습니다.

그런 다음 이것을 B2에 배열 수식으로 넣고 복사하십시오. =IFERROR(INDEX(A$2:A$7;SMALL(IF(FREQUENCY(MATCH(A$2:A$7;A$2:A$7;0);ROW(INDIRECT("1:"&COUNTA(A$2:A$7))));ROW(INDIRECT("1:"&COUNTA(A$2:A$7)));"");ROW(A1)));"")

그러면 다음과 같이 보일 것입니다.

---

정렬 된 고유 값을 얻기 위해 공식을 사용하여 수행 할 수 있습니다. 다음은 사용할 수있는 옵션입니다.

=INDEX($A$2:$A$18,MATCH(SUM(COUNTIF($A$2:$A$18,C$1:C1)),COUNTIF($A$2:$A$18,"<" &$A$2:$A$18),0))

범위 데이터 : A2:A18

셀의 공식 C2

이것은 ARRAY FORMULA입니다.

---

이 수식을 B2셀 에서 시도하십시오.

=IFERROR(INDEX($A$2:$A$7,MATCH(0,COUNTIF(B$1:$B1,$A$2:$A$7),0),1),"")

클릭 F2후 Ctrl+ Shift+Enter

---

COUNTIF를 사용하여 범위에서 값의 발생 횟수를 가져올 수 있습니다. 따라서 값이 A3에 있으면 범위는 A1 : A6이고 다음 열에서 IF (EXACT (COUNTIF (A3 : $ A $ 6, A3), 1), A3, "")를 사용합니다. A4의 경우 IF (EXACT (COUNTIF (A4 : $ A $ 6, A3), 1), A4, "")

이렇게하면 모든 고유 값이 중복되지 않는 열이 제공됩니다.

---

열 A에 단일 고유 인스턴스를 찾으려는 값이 포함되어 있고 다음 수식을 사용한 제목 행이 있다고 가정합니다. 예측할 수없는 행 수로 확장하려면 A772 (내 데이터가 끝난 위치)를 = ADDRESS (COUNTA (A : A), 1)로 바꿀 수 있습니다.

= IF (COUNTIF (A5 : $ A $ 772, A5) = 1, A5, "")

이렇게하면 열에있는 각 값의 마지막 인스턴스에 고유 한 값이 표시되며 정렬을 가정하지 않습니다. 기본적으로 계산할 데이터의 "슬라이딩 창"이 감소하는 것은 절대 값의 부족을 이용합니다. 축소 된 창의 countif가 1이면 해당 행은 열에있는 해당 값의 마지막 인스턴스입니다.

---

Drew Sherman의 솔루션은 매우 좋지만 목록은 연속적이어야합니다 (그는 수동으로 정렬 할 것을 제안하며 저에게는 허용되지 않습니다). Guitarthrower의 솔루션은 항목 수가 많고 원래 목록의 순서를 따르지 않는 경우 다소 느립니다. 상관없이 정렬 된 목록을 출력합니다.

항목의 원래 순서 (다른 열의 날짜별로 정렬 됨)를 원했고 추가로 항목이 중복 된 경우뿐만 아니라 다양한 다른 이유로 최종 목록에서 항목을 제외하고 싶었습니다.

내 솔루션은 Drew Sherman의 솔루션을 개선 한 것입니다. 마찬가지로이 솔루션은 중간 계산에 2 개의 열을 사용합니다.

A 열 :

중복 및 필터링 할 공백이있는 목록입니다. 드류 셔먼의 솔루션을 첫 줄에서 시작하지 않은 상황으로 옮기는 데 문제가 있었기 때문에 예를 들어 A11 : A1100 간격에 배치하겠습니다.

B 열 :

이 수식은이 줄의 값이 유효한 경우 0을 출력합니다 (중복되지 않은 값 포함). 첫 번째 IF 또는 또 다른 외부 IF에서 원하는 다른 제외 조건을 추가 할 수 있습니다.

=IF(ISBLANK(A11);1;IF(COUNTIF($A$11:A11;A11)=1;0;COUNTIF($A11:A$1100;A11)))

스마트 카피를 사용하여 열을 채 웁니다.

C 열 :

첫 번째 줄에서 첫 번째 유효한 줄을 찾습니다.

=MATCH(0;B11:B1100;0)

해당 위치에서 다음 공식을 사용하여 다음 유효한 값을 검색합니다.

=C11+MATCH(0;OFFSET($B$11:$B$1100;C11;0);0)

두 번째 줄에 넣고 스마트 카피를 사용하여 나머지 열을 채 웁니다. 이 수식은 포인트에 대한 고유 한 itens가 더 이상 없을 때 # N / D 오류를 출력합니다. 다음 칼럼에서이를 활용하겠습니다.

열 D :

이제 C 열이 가리키는 값을 가져와야합니다.

=IFERROR(INDEX($A$11:$A$1100; C11); "")

스마트 카피를 사용하여 열을 채 웁니다. 이것은 출력 고유 목록입니다.

---

이 방법으로도 할 수 있습니다.

다음과 같은 명명 된 범위를 만듭니다.

nList = the list of original values
nRow = ROW(nList)-ROW(OFFSET(nList,0,0,1,1))+1
nUnique = IF(COUNTIF(OFFSET(nList,nRow,0),nList)=0,COUNTIF(nList, "<"&nList),"")

이 3 개의 명명 된 범위를 사용하면 아래 수식을 사용하여 순서가 지정된 고유 값 목록을 생성 할 수 있습니다. 오름차순으로 정렬됩니다.

IFERROR(INDEX(nList,MATCH(SMALL(nUnique,ROW()-?),nUnique,0)),"")

'?'대신 고유 한 순서가 지정된 목록의 첫 번째 요소 바로 위에있는 셀의 행 번호를 대체해야합니다. 캐릭터.

예. 고유 한 순서가 지정된 목록이 B5 셀에서 시작되면 수식은 다음과 같습니다.

IFERROR(INDEX(nList,MATCH(SMALL(nUnique,ROW()-4),nUnique,0)),"")

---

이 솔루션이 아직 나오지 않은 것이 놀랍습니다. 가장 쉬운 방법 중 하나라고 생각합니다

데이터에 제목을 지정하고 동적 명명 된 범위에 넣습니다 (예 : 데이터가 col에있는 경우 A).

=OFFSET($A$2,0,0,COUNTA($A:$A),1)

그런 다음 피벗 테이블을 만들어 소스를 명명 된 범위로 만듭니다.

단순히 제목을 행 섹션에 넣으면 고유 한 값을 갖게되고 내장 기능을 사용하여 원하는 방식으로 정렬 할 수 있습니다.

---

아래 엑셀 파일에 내가 사용하는 것을 붙여 넣었습니다. 그러면 범위에서 고유 한 값을 선택하여 L11:L300V, V11 열부터 채 웁니다. 이 경우 v11에이 공식이 있고 모든 고유 값을 얻기 위해 아래로 드래그합니다.

=INDEX(L$11:L$300,MATCH(0,COUNTIF(V$10:V10,L$11:L$300),0))

또는

=INDEX(L$11:L$300,MATCH(,COUNTIF(V$10:V10,L$11:L$300),))

이것은 배열 공식입니다

---

피벗 테이블을 사용하는 것은 수식 만 사용하는 것으로 간주되지 않을 수 있지만 지금까지 대부분의 다른 제안보다 실용적으로 보입니다.

---

나는 최근에 같은 문제에 부딪 쳤고 마침내 그것을 알아 냈습니다.

목록을 사용하여 수식을 사용하여 Excel에서 붙여 넣습니다.

예를 들어 C6내 예제의 셀 에 수식을 작성한 다음 복사하여 열에 붙여 넣는 것과 같이 목록 중간에 수식을 작성하는 것이 좋습니다. 수식 을 다시 입력 할 필요없이 자동으로 조정됩니다.

The only cell that has a uniquely different formula is in the first row.

Using your list ("red", "blue", "red", "green", "blue", "black"); here is the result: (I don't have a high enough level to post an image so hope this txt version makes sense)

• [Column A: Original List]
• [Column B: Unique List Result]

• [Column C: Unique List Formula]

  1. red, red, =A3
  2. blue, blue, =IF(ISERROR(MATCH(A4,A$3:A3,0)),A4,"")
  3. red, , =IF(ISERROR(MATCH(A5,A$3:A4,0)),A5,"")
  4. green, green, =IF(ISERROR(MATCH(A6,A$3:A5,0)),A6,"")
  5. blue, , =IF(ISERROR(MATCH(A7,A$3:A6,0)),A7,"")
  6. black, black, =IF(ISERROR(MATCH(A8,A$3:A7,0)),A8,"")

---

This only works if the values are in order i.e all the "red" are together and all the "blue" are together etc. assume that your data is in column A starting in A2 - (Don't start from row 1) In the B2 type in 1 In b3 type =if(A2 = A3, B2,B2+1) Drag down the formula until the end of your data All " Red" will be 1 , all "blue" will be 2 all "green" will be 3 etc.

In C2 type in 1, 2 ,3 etc going down the column In D2 = OFFSET($A$1,MATCH(c2,$B$2:$B$x,0),0) - where x is the last cell Drag down, only the unique values will appear. -- put in some error checking

---

For a solution that works for values in multiple rows and columns, I found the following formula very useful, from http://www.get-digital-help.com/2009/03/16/unique-values-from-multiple-columns-using-array-formulas/ Oscar at get-digital.help.com even goes through it step-by-step and with a visualized example.

1) Give the range of values the label tbl_text

2) Apply the following array formula with CTRL + SHIFT + ENTER, to cell B13 in this case. Change $B$12:B12 to refer to the cell above the cell you enter this formula into.

    =INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), MATCH(0, COUNTIF($B$12:B12, INDEX(tbl_text, MIN(IF(COUNTIF($B$12:B12, tbl_text)=0, ROW(tbl_text)-MIN(ROW(tbl_text))+1)), , 1)), 0), 1)

3) Copy/drag down until you get N/A's.

---

If one puts all the data in the same columns and uses the following formula Example Formula: =IF(C105=C104,"Duplicate","Not a Duplicate")

Steps

1. Sort the data
2. Add column for the formula
3. Checks if the cell equals the cell above it
4. Then filter Not a Duplicate
5. Optional: Copy the data calculated by the formula column and paste as values only (that way if you start deleting data, you don't start to get errors
6. NOTE/WARNING: This only works if you sort the data first

Example Formula: =IF(C105=C104,"Duplicate","Not a Duplicate")

---

Optimized VBScript Solution

I used totymedli's code but found it bogging down when using large ranges (as pointed out by others), so I optimized his code a bit. If anyone is interested in getting unique values using VBScript but finds totymedli's code slow when updating, try this:

    Function listUnique(rng As Range) As Variant
        Dim val As String
        Dim elements() As String
        Dim elementSize As Integer
        Dim newElement As Boolean
        Dim i As Integer
        Dim distance As Integer
        Dim allocationChunk As Integer
        Dim uniqueSize As Integer
        Dim r As Long
        Dim lLastRow  As Long

        lLastRow = rng.End(xlDown).row

        elementSize = 1
        unqueSize = 0

        distance = Range(Application.Caller.Address).row - rng.row

        If distance <> 0 Then
            If Cells(Range(Application.Caller.Address).row - 1, Range(Application.Caller.Address).Column).Value = "" Then
                listUnique = ""
                Exit Function
            End If
        End If

        For r = 1 To lLastRow
            val = rng.Cells(r)
            If val <> "" Then
                newElement = True
                For i = 1 To elementSize - 1 Step 1
                    If elements(i - 1) = val Then
                        newElement = False
                        Exit For
                    End If
                Next i
                If newElement Then
                    uniqueSize = uniqueSize + 1
                    If uniqueSize >= elementSize Then
                        elementSize = elementSize * 2
                        ReDim Preserve elements(elementSize - 1)
                    End If
                    elements(uniqueSize - 1) = val
                End If
            End If
        Next


        If distance < uniqueSize Then
            listUnique = elements(distance)
        Else
            listUnique = ""
        End If
    End Function

---

Select the column with duplicate values then go to Data Tab, Then Data Tools select remove duplicate select 1) "Continue with the current selection" 2) Click on Remove duplicate.... button 3) Click "Select All" button 4) Click OK

now you get the unique value list.

참고 URL : https://stackoverflow.com/questions/1429899/getting-unique-values-in-excel-by-using-formulas-only