Нужно уникальное количество текста из нескольких столбцов по категориям

Question

Мои данные выглядят примерно так (предположим, начинается с A1):

 Category,Label1,Label2 
 Dogs,blank,doe
 Dogs,zoo,blank 
 Cats,zoo,yup 
 Cats,doe,hgg 
 Cats,blank,doe 

Как должен выглядеть результат:

 Dogs, 2
 Cats, 4

Мои реальные данные имеют 100 строк и 5 столбцов меток.

Я ищу решение, которое использует sumproduct или формулу массива и может обрабатывать пробелы и массивы разных размеров. Я хотел бы избежать VBA, если это возможно.

Я закончил с этой формулой, но все еще не правильный ответ:

=SUM((($A$2:$A$6="Dogs")*($B$2:$C$6<>""))/(COUNTIF($B$2:$C$6,$B$2:$C$6&"")))

Я попытался объединить (A2:A6 и B2:C6) в параметре диапазона счетчиков, но счетчик не принял это. Я пробовал sumproduct, но мне не понравилось, что я использовал массивы разных размеров (1 столбец против 2 столбца) и многое другое. Я потратил большую часть двух дней, исследуя и пытаясь решить эту проблему.

Ждем вашей помощи и опыта.

Answer 1

Я бы использовал Power Query Add-In для этого. Он имеет команду Group By, которая включает в себя операцию Count Distinct Rows. Это задокументировано здесь (хотя они еще не догнали эту операцию):

https://support.office.com/en-us/article/Group-rows-in-a-table-Power-Query-e1b9e916-6fcc-40bf-a6e8-ef928240adf1?ui=en-US&rs=en-US&ad= НАС

Чтобы подготовить ваши данные в Power Query (перед использованием команды Group By), я бы выбрал столбец Category и использовал команду Transform / Unpivot Columns / Unpivot Other Columns. Это преобразует столбцы Label1 и Label2 в столбцы Attribute и Value. Я бы удалил столбец Attribute - который оставляет только столбец Value, содержащий doe, zoo и т.д.

Последним шагом является Group By, которая будет правильно подсчитывать различные / уникальные значения, например, Cats = 4