MS Excel에서 공통 집합 연산(결합, 교차, 마이너스)을 수행하려면 어떻게 해야 합니까?

MS Excel에서 공통 집합 연산(결합, 교차, 마이너스)을 수행하려면 어떻게 해야 합니까?

예를 들어, 다음과 같은 xls가 있습니다.

• 열 A에 속성이 A인 항목 목록이 있습니다.
• 열 B에 속성 B가 있는 항목 목록이 있습니다.

다음이 필요합니다.

• A 연합 B인 C 열(A와 B의 고유 항목)
• A 교차로 B인 D 열(A 및 B의 공통 항목)
• A에서 B를 뺀 E열(A에 있는 항목이지만 B에는 없는 항목)
• B에서 A를 뺀 F열(B는 있지만 A는 아닌 항목)

SQL 또는 Python을 사용하면 요소 목록에 대한 설정 작업이 쉬워 보입니다.하지만 xls로 어떻게 합니까?

참고: 복사 붙여넣기 및 클릭을 최소화하는 자동화 기능이어야 합니다.예를 들어, 저는 A를 B 아래에 복사하여 붙여넣은 다음 A 연합 B를 얻기 위해 "중복 제거"하고 싶지 않습니다.

& B 내 교점차(A 및 B 내):=IFNA(VLOOKUP(B2,$A$2:$B$42,1,FALSE),"")

B에서 유언니에(A 또 B서는):=IFS(A2,A2,B2,B2):IFS는 Office 2019 이상 버전에서만 제공됩니다.

- A - B(A만해당에)):=IF(NOT(IFNA(MATCH(A2,$B$2:$B$42,0),FALSE)),IF(A2,A2,""),"")

- B - A(B만해당에)):=IF(NOT(IFNA(MATCH(B2,$A$2:$A$42,0),FALSE)),IF(B2,B2,""),"")를 바꿉니다 (글자를바다니꿉)글

엑셀만으로는 그 일을 할 수 없을 것 같습니다.그러나 추가 기능을 사용할 수 있습니다.무료 및 오픈 소스인 Excel용 Power Analytics를 테스트해 볼 수도 있습니다.일부 기능은 요청한 작업을 정확하게 수행합니다.

Excel 365에서의 사용

Excel 365에서 Excel용 전력 분석 기능을 사용하면 동적 어레이를 사용할 수 있습니다.이 기능은 Excel 365에만 포함되어 있으며 t Excel 2019, 2016 등에서는 사용할 수 없습니다.

다음 예제에서는 =PA_Sets_And(A2:A11;B2:B6) 단일 셀 D2로 이동한 다음 마법처럼 공식이 필요한 길이의 세 줄로 확장됩니다.

Excel 2019, 2016, ...에서의 사용

여기서는 셀(B2)이 전체 세트(A2:A11)의 범위에 포함되는지 여부를 확인하기 위해 PA_Sets_And 방법을 사용합니다.엑셀365만큼 멋지지는 않지만 브이룩업보다 조금 더 멋집니다 :-)

Microsoft Excel은 기본 제공 집합 작업을 처리하지 않습니다.그러나 MATCH 함수와 오류 처리를 사용하여 VBA를 통해 에뮬레이트할 수 있습니다.

다음은 저에게 효과적인 코드입니다(첫 번째 줄로 이동하셨을 것입니다).

Sub set_operations()
    Dim i, j, rangeA, rangeB, rowC, rowD, rowE, rowF As Long
    Dim test1, test2 As Boolean

    rangeA = ActiveSheet.Range("A" & CStr(ActiveSheet.Rows.Count)).End(xlUp).Row()
    rangeB = ActiveSheet.Range("B" & CStr(ActiveSheet.Rows.Count)).End(xlUp).Row()
    rowC = 2
    rowD = 2
    rowE = 2
    rowF = 2
    test1 = False
    test2 = False
    test2 = False

    'A union B
    On Error GoTo errHandler1
    For i = 2 To rangeA
        If Application.Match(ActiveSheet.Cells(i, 1), ActiveSheet.Range("C:C"), 0) > 0 Then
            If test1 = True Then
                ActiveSheet.Cells(rowC, 3) = ActiveSheet.Cells(i, 1)
                rowC = rowC + 1
            End If
        End If
        test1 = False
    Next i
    For j = 2 To rangeB
        If Application.Match(ActiveSheet.Cells(j, 2), ActiveSheet.Range("C:C"), 0) > 0 Then
            If test1 = True Then
                ActiveSheet.Cells(rowC, 3) = ActiveSheet.Cells(j, 2)
                rowC = rowC + 1
            End If
        End If
        test1 = False
    Next j

    'A intersection B
    For i = 2 To rangeA
        On Error GoTo errHandler2
        If Application.Match(ActiveSheet.Cells(i, 1), ActiveSheet.Range("B:B"), 0) > 0 Then
            On Error GoTo errHandler1
            If Application.Match(ActiveSheet.Cells(i, 1), ActiveSheet.Range("D:D"), 0) > 0 Then
                If test1 = True And test2 = False Then
                    ActiveSheet.Cells(rowD, 4) = ActiveSheet.Cells(i, 1)
                    rowD = rowD + 1
                End If
            End If
        End If
        test1 = False
        test2 = False
    Next i

    'A minus B
    For i = 2 To rangeA
        On Error GoTo errHandler2
        If Application.Match(ActiveSheet.Cells(i, 1), ActiveSheet.Range("B:B"), 0) > 0 Then
            On Error GoTo errHandler1
            If Application.Match(ActiveSheet.Cells(i, 1), ActiveSheet.Range("E:E"), 0) > 0 Then
                If test1 = True And test2 = True Then
                    ActiveSheet.Cells(rowE, 5) = ActiveSheet.Cells(i, 1)
                    rowE = rowE + 1
                End If
            End If
        End If
        test1 = False
        test2 = False
    Next i

    'B minus A
    For i = 2 To rangeB
        On Error GoTo errHandler2
        If Application.Match(ActiveSheet.Cells(i, 2), ActiveSheet.Range("A:A"), 0) > 0 Then
            On Error GoTo errHandler1
            If Application.Match(ActiveSheet.Cells(i, 2), ActiveSheet.Range("F:F"), 0) > 0 Then
                If test1 = True And test2 = True Then
                    ActiveSheet.Cells(rowF, 6) = ActiveSheet.Cells(i, 2)
                    rowF = rowF + 1
                End If
            End If
        End If
        test1 = False
        test2 = False
    Next i


errHandler1:
    test1 = True
    Resume Next

errHandler2:
    test2 = True
    Resume Next
End Sub

간단하게 아래 공식을 사용하여 결과를 얻을 수 있습니다.

=IF(하부(A4)=하부(B4),","A4)

저는 몇 가지 측면에서 놀랐습니다.
2020년 현재 .. 아직 엑셀에는 설정된 기능이 없습니다.
가장 높은 투표율을 기록한 답변(2018년부터)은 매우 비현실적입니다. 실시간으로 볼 때 데이터 세트는 다른 데이터 세트에 비해 값이 누락된 빈 행이 삽입되어 있지 않습니다. 이는 이 솔루션의 전제 조건입니다.

가장 실용적인 해결책(아직은 어색하지만, 마이크로소프트, 들리나요?)?)는 피벗 테이블을 사용한 해결 방법입니다.

• 열 이름이 "set_name"이고 열의 모든 값이 "A"로 설정된 A 설정에 열을 추가합니다. -> 피벗 입력 A
• 열 이름이 "set_name"이고 열에 있는 모든 값이 "B"로 설정된 B 설정에 열 추가 -> 피벗 입력 B
• 피벗 입력 B(열 이름 없음 ;-))를 피벗 입력 A 아래에 복사하여 결합 범위를 형성합니다. -> 결합 범위
• 공동 범위에서 피벗 테이블 작성
  피벗 테이블의 열을 형성하는 데 사용되는 "이름 설정"
  피벗 함수가 "count"로 설정됨

결과적으로 다음과 같은 "원 핫 인코딩" 피벗 테이블이 생성됩니다.

• 첫 번째 열: A와 B의 결합 세트(일명 ALL 발생 값)
• 두 번째 열: 집합 A에서 발생하는 요소에 대해서만 1-값
  (주의: A에는 고유 요소만 포함되어 있다고 가정합니다.그렇지않으면,
  값이 1보다 클 수 있음)
• 세 번째 열: 집합 B에서 발생하는 요소에 대해서만 1-값
  (세트 A와 동일한 주의 사항이 적용됨
• 총 열: "2" 값을 나타내는 값이 두 집합에 모두 존재합니다.

결과 피벗 테이블은 열 2(일명 "세트 A"), 3(일명 "세트 B") 및 4(일명 "세트 A AND B")의 값을 통해 서로 다른 세트와 교차점에서 쉽게 필터링할 수 있습니다.

언급URL : https://stackoverflow.com/questions/31186547/how-can-we-perform-common-set-operations-union-intersection-minus-in-ms-exce