Функция Excel работает как группа SQL по + count(отлично *)?

Question

Предположим, у меня есть лист Excel с данными ниже

 CODE (COL A) | VALUE (COL B)
==============================
  A01         | 10
  A01         | 20
  A01         | 30
  A01         | 10
  B01         | 30
  B01         | 30

Есть ли функция Excel, работающая как ...

SELECT CODE, count (Distinct *) FROM TABLE GROUP BY CODE


 CODE    | Distinct Count of Value
===================================
  A01    | 3
  B01    | 1

или, что еще лучше, можно вставить формулу Excel в столбец C, чтобы получить что-то вроде этого:

 
 CODE (COL A) | VALUE (COL B) | DISTINCT VALUE COUNT WITH MATCHING CODE (COL C)
===============================================================================
  A01         | 10            | 3
  A01         | 20            | 3
  A01         | 30            | 3
  A01         | 10            | 3
  B01         | 30            | 1
  B01         | 30            | 1

Я знаю, что могу использовать сводную таблицу, чтобы получить этот результат легко. Однако из-за требований к отчетам я должен добавить столбец "различное количество" на лист Excel, поэтому сводная таблица не вариант.

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

Answer 1

Введите эту формулу в ячейку C2, предполагая, что у вас есть данные в строках со 2 по 7,

=SUMPRODUCT(($A$2:$A$7=A2)  /  COUNTIFS($B$2:$B$7, $B$2:$B$7, $A$2:$A$7, $A$2:$A$7))

и перетащите его вниз.

Как это устроено:

Когда SUMPRODUCT задается список скалярных аргументов, он работает как SUM , но в качестве аргумента он принимает массив без специальной записи в массиве.

Массив заполняется нулями для записей, которые не соответствуют значению CODE в столбце A Для тех, которые совпадают, массив заполняется 1/(the number of records that have the same A and B values as this record) . Так, например, есть две записи, которые имеют A = A01 и B = 10 , поэтому для этих двух записей в массив вводится 1/2 (½). Думайте об этом как о некотором весе для повторяющихся значений. Всякий раз, когда эти значения суммируются, сумма для каждого уникального значения B равна 1 (в этом примере две записи суммируют ½ +½ = 1). Это дает количество различных записей.

Полный пример с использованием данных вашего примера:

Для любой записи с A = A01 формула вернет сумму {½,1,1,½,0,0} = 3 .
Для любой записи с A = B01 формула вернет сумму {0,0,0,0,½,½} = 1 .

Answer 2

Вот подход, который немного легче понять, чем у Excellll, но он требует дополнительного столбца. Предполагая, что ваши данные находятся в строках со 2 по 7 (столбцы A и B), введите это в C2:

=COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)=1

и это в D2:

=COUNTIFS($C$2:$C$7, TRUE, $A$2:$A$7, $A2)

и перетащите вниз.

Как это устроено:

COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2) подсчитывает, сколько строк выше, включая текущую, имеют те же значения A и B что и текущая строка.  Это будет 1 в первом вхождении пары значений (строки 2, 3, 4 и 6) и выше в строках, которые повторяют пару значений, которая произошла выше (т. Е. Это будет 2 в строках 5 и 7).  Проверка того, равен ли это 1, дает значение TRUE при первом появлении каждой отдельной пары значений и FALSE другом месте.  Тогда формула в столбце D подсчитывает, сколько TRUE есть для текущего значения A

Вы можете немного упростить формулы:

C:    =COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2)

D:    =COUNTIFS($C$2:$C$7, 1, $A$2:$A$7, $A2)

и, конечно, вы можете скрыть столбец C.

Answer 3

Я бы использовал Power Pivot Excel Add-In. Он ест различные графства на завтрак ...

Сначала я бы добавил таблицу Excel в Power Pivot с помощью кнопки «Создать связанную таблицу» на ленте Power Pivot.

Затем я использовал бы кнопку «Сводная таблица» на ленте Power Pivot, чтобы создать сводную таблицу, перетащив столбец «Код» (столбец A) в зону «Метки строк» и столбец «Значение» (столбец B) в зону «Значения» (в списке полей Power Pivot. ).

По умолчанию поле «Значения» будет агрегировано как сумма значений (столбец B). Я бы изменил это, щелкнув запись «Сумма стоимости» (Col B) в зоне «Значения» и выбрав «Суммировать по», а затем «Определить количество».

Вот скриншот с результатом