Как мы можем выполнять обычные операции над множествами (объединение, пересечение, минус) в MS Excel?

Question

Как мы можем выполнять обычные операции над множествами (объединение, пересечение, минус) в MS Excel?

Например, у меня есть xls где:

в столбце А есть список объектов со свойством А
столбец B содержит список объектов со свойством B

Мне нужно следующее:

столбец C, представляющий собой A union B (уникальные предметы как A, так и B)
столбец D, который является пересечением A B (общие элементы A & B)
столбец E, который является A минус B (элементы в A, но не в B)
столбец F, который является B минус A (элементы в B, но не в A)

Операции над множествами элементов списка кажутся простыми с SQL или Python. Но как это сделать в xls?

Примечание: это должна быть автоматизация с минимальным количеством копий-вставок и кликов. Например, я не хочу копировать-вставлять A ниже B, а затем "исключать дубликаты", чтобы получить A B.

15

excel statistics analytics

Источник

user3662213 02 июл '15 в 13:53

5 ответов

Другие вопросы по тегам excel statistics analytics

user161366 29 дек '18 в 22:39 2018-12-29 22:39 · Answer 1 · 2018-12-29 22:39

Пересечение (в A & B): =IFNA(VLOOKUP(B2,$A$2:$B$42,1,FALSE),"")

Союз (в A или B): =IFS(A2,A2,B2,B2) Обратите внимание, что IFS только в последних (по состоянию на 2018) версиях.

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,""),"") (Поменяйте местами буквы)

user2114462 23 янв '21 в 14:18 2021-01-23 14:18 · Answer 2 · 2021-01-23 14:18

Один Excel, кажется, не в состоянии выполнить эту работу. Тем не менее, есть надстройки. Возможно, вы захотите протестировать бесплатную Power Analytics для Excel с открытым исходным кодом . Он поставляется с некоторыми функциями, точно выполняющими то, что вы просили:

Использование в Excel 365

В Excel 365 Power Analytics для Excel можно использовать динамические массивы. Эта функция включена исключительно в Excel 365 и недоступна в Excel 2019, 2016 и т. д.

В следующем примере мы просто пишем =PA_Sets_And(A2:A11;B2:B6) в одну ячейку D2, а затем, как по волшебству, формула расширяется до необходимой длины в три строки.

Использование в Excel 2019, 2016,...

Здесь мы используем метод PA_Sets_And, чтобы узнать, содержится ли ячейка (B2) в диапазоне всего набора (A2:A11). Не так круто, как для Excel 365, но немного лучше, чем ВПР :-)

user5074213 02 июл '15 в 19:39 2015-07-02 19:39 · Answer 3 · 2015-07-02 19:39

Ну, Microsoft Excel не обрабатывает встроенные операции над множествами. Но вы можете эмулировать VBA, используя функцию MATCH и обработку ошибок.

Вот код, который работал для меня (я предполагаю, что у вас заголовок в первой строке):

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

user9998108 23 окт '19 в 15:28 2019-10-23 15:28 · Answer 4 · 2019-10-23 15:28

Вы можете просто использовать формулу ниже, чтобы получить результат

= ЕСЛИ (НИЖНИЙ (A4)= НИЖНИЙ (B4); ""; A4)

0

Источник

user9998108 23 окт '19 в 15:28

user3973302 28 сен '20 в 09:48 2020-09-28 09:48 · Answer 5 · 2020-09-28 09:48

Я удивлен на нескольких уровнях:
(1) 2020 год... и все еще нет установленных функций в Excel
(2) Ответ, получивший наибольшее количество голосов (с 2018 года), очень непрактичен: в реальном времени наборы данных не поступают аккуратно со вставленными пустыми строками, в которых отсутствует значение, по сравнению с другим набором данных; что является предварительным условием для этого решения.

Наиболее практичным решением (хотя до сих пор неловко, ты слышишь нас, Microsoft???) является работа вокруг с помощью сводной таблицы:

Добавьте столбец в набор A, с именем столбца "set_name" и всеми значениями в столбце, установленными на "A" -> поворотный ввод A
Добавьте столбец в набор B, с именем столбца "set_name" и всеми значениями в столбце, установленными на "B" -> поворотный ввод B
скопировать сводный вход B (без имен столбцов;-)) под сводный вход A, чтобы сформировать объединенный диапазон -> объединенный диапазон
создать сводную таблицу из объединенного диапазона с
--- "имена наборов", используемые для формирования столбцов сводной таблицы
--- функция сводной таблицы установлена на "count()"

Результатом является своего рода сводная таблица с горячим кодированием:

1-й столбец: объединенный набор A и B (также известный как ВСЕ возникающие значения)
2-й столбец: 1-значения только для элементов, входящих в набор A
(предостережение: предполагается, что A содержит только УНИКАЛЬНЫЕ элементы. В противном случае возможны
значения> 1)
3-й столбец: 1-значения только для элементов, входящих в набор B
(применяется то же предостережение, что и для набора A)
Столбец "Итого": значения, показывающие "2", существуют в обоих наборах

Результирующую сводную таблицу можно легко отфильтровать по различным наборам и пересечениям с помощью значений в столбцах 2 (он же "набор A"),3 (он же "набор B") и 4 (он же "Набор A И набор B").