Excel извлекает уникальные объекты из списка

У меня есть большой список компаний, которые сотрудничают с моей компанией. Моя компания разделена на различные исследовательские группы. Компания может сотрудничать с одной исследовательской группой несколько раз в списке или с несколькими исследовательскими группами.

Я хотел бы узнать, какие компании только сотрудничают с одной исследовательской группой. Ниже приведен пример данных, вы можете видеть, что компания A работает только с группой 1, но несколько раз, но компания B сотрудничает со многими группами. Как я могу считать это?

Примеры данных:

Group Company 1 A 1 B 1 C 1 A 1 C 2 D 2 D 2 E 2 E 2 B 2 D 3 D 3 F 3 B 3 F 4 G 4 B 4 B

Это будет бинарный результат, 1 = компания уникальна для группы, 0 = компания не уникальна для группы.

Расширение этого (хотя и не включено в мой вопрос) было бы, сколько групп компаний в среднем связаны с

Есть способ обхода, которого вы хотите достичь. Следующее решение использует пару вспомогательных столбцов и в конце даст результат, является ли компания уникальной для группы или нет, и количество групп, с которыми сотрудничают компании.

Предполагая, что ваши данные Group и Company находятся в Column A и Column B выполните следующие шаги:

Шаг 1: Получите уникальное сочетание группы и компании

В Cell D2 введите следующую формулу и перетащите / скопируйте ее по мере необходимости.

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

Шаг 2: Получить количество каждой комбинации данных

В Cell E2 введите следующую формулу и перетащите / скопируйте ее до строки, где отображаются значения Column D

 =COUNTIFS($A$2:$A$19,LEFT(D2,(FIND(",",D2,1)-1)),$B$2:$B$19,MID(D2,FIND(",",D2)+1,256)) 

Эта формула даст счетность каждой комбинации из Column D в ваших данных. Например, группа 1 и компания A встречаются два раза в данных, группа 2 и компания D происходят 3 раза в ваших данных и т. Д.

Шаг 3: Получите список уникальных компаний из Column B

В Cell F2 введите следующую формулу и перетащите / скопируйте ее по мере необходимости.

 =IFERROR(INDEX($B$2:$B$14,MATCH(0,INDEX(COUNTIF($F$1:F1,$B$2:$B$14),0,0),0)),"") 

Шаг 4: Получите количество групп, каждая из которых сотрудничает с

В Cell G2 введите следующую формулу и перетащите / скопируйте ее до строки, где отображаются значения Column F

 =COUNT(IF(MID($D$2:$D$12,FIND(",",$D$2:$D$12)+1,256)=F2,$E$2:$E$12)) 

Это формула массива, поэтому скопируйте ее, нажав Ctrl + Shift + Enter

Шаг 5: Проверьте, уникальна ли компания для группы или нет.

В Cell H2 введите следующую формулу и перетащите / скопируйте ее до строки, где отображаются значения Column G

 =IF(COUNT(IF(MID($D$2:$D$12,FIND(",",$D$2:$D$12)+1,256)=F2,$E$2:$E$12))=1,1,0) 

Опять же, это формула массива, поэтому скопируйте ее, нажав Ctrl + Shift + Enter

или вместо этого используйте эту формулу =IF(G2=1,1,0)

введите описание изображения здесь


EDIT: согласно требованию, указанному в комментарии

В Cell J2 введите:

 =IFERROR(INDEX($B$2:$B$19 & "," & $A$2:$A$19,MATCH(0,INDEX(COUNTIF($D$1:J1,$A$2:$A$19 & "," & $B$2:$B$19),0,0),0)),"") 

В Cell K2 введите:

 =COUNTIFS($A$2:$A$19,MID(J2,FIND(",",J2)+1,256),$B$2:$B$19,LEFT(J2,(FIND(",",J2,1)-1))) 

В Cell L2 введите:

 =IFERROR(INDEX($A$2:$A$19,MATCH(0,INDEX(COUNTIF($L$1:L1,$A$2:$A$19),0,0),0)),"") 

В Cell M2 введите:

 =COUNT(IF(VALUE(MID($J$2:$J$12,FIND(",",$J$2:$J$12)+1,256))=L2,$E$2:$E$12)) 

Это формула массива.

В Cell N2 введите:

 =IF(N2=1,1,0) 

или

 =IF(COUNT(IF(VALUE(MID($J$2:$J$12,FIND(",",$J$2:$J$12)+1,256))=L2,$E$2:$E$12))=1,1,0) 

Эта формула также является формулой массива.

См. Изображение для справки:

введите описание изображения здесь

  • Сложный индекс соответствует 4 критериям.
  • Как получить процентный ответ от сводных данных?
  • Проблема в шаблоне регулярных выражений
  • Рисование конкретных данных из больших таблиц?
  • Функция возвращает NA, тогда как конструктор функций возвращает правильный ответ
  • Почему мой макрос определен только для одного файла excel?
  • Как найти следующий рабочий день
  • Как получить значения из раскрывающегося списка в MsExcel?
  • как я могу проверить значение TRUE в excel
  • как использовать IF для выбора кодов excel?
  • Excel Если оператор, вычисляющий несколько ячеек для подстрок
  • Interesting Posts

    VLOOKUP для уплотнения данных в одну ячейку

    Как выполнить цикл на каждом рабочем листе в книге для расчета?

    Как импортировать данные из файла excel во множество шагов свойств в тестовой папке в бесплатной версии soapui

    Сумма чисел + 0

    Настройка названия диаграммы работает только при отладке в Excel 2016

    ActiveWorkbook.ConnectionsDisabled дает ошибку компиляции

    VBA: Группа диаграмм все данные из одного набора данных в одном столбце

    Как программно импортировать даты для многих пользователей на сервере обмена

    Excel VBA: общедоступные массивы в объектах класса

    Прокрутите столбец и проверьте, содержит ли ячейка определенные символы

    Applescript Excel 2016 SAVE AS CSV

    Ошибка, Невозможно установить свойство formulaArray класса range

    PHPexcel html, чтобы отличать изображения

    Показывать только лучшие записи в Pivottable excel

    Java POI предоставленные данные отображаются в формате Office 2007+ XML

    Давайте будем гением компьютера.