Найти условную частоту уникальных текстовых записей в Excel?

Question

Итак, я осмотрел Интернет и столкнулся с кирпичной стеной со всем, что я пробовал.

У меня есть лист Excel с двумя столбцами данных; «Страна» и «Форма»; ниже небольшая выборка данных:

Country:   Shape:
us        cylinder
us        light
gb        circle
ca        circle
us        light
us        sphere
gb        circle
us        disk
us        disk
us        disk
us        circle
gb        fireball
us        disk
gb        unknown
us        oval
us        circle
ca        disk
us        disk

Я пытаюсь подсчитать количество экземпляров уникальной фигуры для данной страны ... например, если (нас), то сумма экземпляров (круг).

Я пытался использовать формулу SUM(FREQUENCY(IF())) вдохновленную подсчетом уникальных текстовых значений с критериями (в Exceljet), но я просто не могу заставить его работать. Какие-либо предложения?

Желаемый результат (после запуска для нескольких фигур) будет выглядеть примерно так:

Country:    Circle:    Cylinder:    light:   etc...
us          2          1            2
gb          2          0            0
ca          1          0            0

ИЛИ ЖЕ

еще проще,

us    circle    2

и затем я могу вручную ввести каждое значение, которое я получу сам.

Answer 1

Есть лучшие способы сделать это, чем FREQUENCY .

Я предполагаю, что вы предпочли бы создать сетку, которую вы показали в качестве первого желаемого результата.  Я также предполагаю, что вы хотите поместить его на тот же лист, что и исходные данные (в частности, я предполагаю, что вы хотите поместить его справа от входных данных, начиная с ячейки F1).  Если вы хотите поместить его на другой лист, основные шаги те же, но вам придется использовать Sheet1!A2 вместо простого A2 и т.д.

Чтобы создать метки строк:

• Нажмите на пустую ячейку, которая не смежна ни с одной ячейкой, в которой есть данные.

• На вкладке «Данные» на панели «Сортировка и фильтрация» нажмите «Дополнительно».  Заполните полученное диалоговое окно следующим образом:

  • Выберите «Копировать в другое место».
  • Задайте для параметра «Диапазон списка» значение «Столбец A или укажите диапазон, в котором находятся страны (A1:A19).  У вас должен быть заголовок (в ячейке A1), и вы должны включить его в диапазон.
  • Установите диапазон «Копировать в» в столбец F
  • Выберите «Только уникальные записи».
    
    

  Нажмите «ОК».

Создание заголовков столбцов аналогично:

• Нажмите на пустую ячейку; нажмите «Расширенный фильтр».  Заполните полученное диалоговое окно следующим образом:

  • Выберите «Копировать в другое место».
  • Установите «Список диапазонов» для столбца B (или укажите диапазон, в котором фигуры имеют форму: B1:B19).
  • Установите диапазон «Копировать в» в столбец G
  • Выберите «Только уникальные записи».
    
    

  Нажмите «ОК».

• Выберите список дедуплицированных фигур в ячейках G2:G9 (не включая G1).
• Скопируйте ячейки, набрав Ctrl+C или нажав «Домой» → «Буфер обмена» → «Копировать».
• Нажмите в ячейке G1 .
• Нажмите «Домой» → «Буфер обмена» → «Вставить» → «Транспонировать»:
              
• Снова выделите ячейки G2:G9 и очистите их (например, нажмите Del).

Если вы хотите, чтобы метки и заголовки были отсортированы, сделайте это.

Теперь нажмите на ячейку G2 и введите

=COUNTIFS($A$2:$A$19, $F2, $B$2:$B$19, G$1)

Перетащите / заполните вправо и вниз соответственно.  Для ваших образцов данных это клетки G2:N4 . Вот что я получил: