#excel
Вопрос:
Если у меня есть список значений в диапазоне C3:C15, как эта формула вычисляет уникальные значения?
=SUMPRODUCT((C3:C15lt;gt;"")/COUNTIF(C3:C15,C3:C15amp;"")) | Покупатель |
|---|
| Клиент1 |
| Клиент5 |
| Клиент2 |
| Клиент4 |
| Клиент4 |
| Клиент6 |
| Клиент1 |
| Клиент2 |
| Клиент1 |
| Клиент3 |
| Клиент3 |
| Клиент3 |
| Клиент4 |
Ответ №1:
Иногда проще всего разбить формулу на части и начать с этого.
=C3:C15lt;gt;"" Вернет значение TRUE для любой ячейки, которая не является пустой. Затем SUMPRODUCT будет рассматривать это логическое значение TRUE как 1. Если он вернет значение FALSE, оно будет рассматриваться как 0.
=COUNTIF(C3:C15,C3:C15amp;"") Вернет количество раз, когда это значение будет найдено в списке. Добавляя amp;"" в конце, вы гарантируете, что всегда будете возвращать по крайней мере 1, устраняя любые ошибки деления на ноль.
В сочетании вы получаете нечто похожее на это:
| Источник | =C3:C15lt;gt;»» | =КОЛИЧЕСТВО(C3:C15,C3:C15amp;»») | COL B / COL C |
|---|---|---|---|
| Клиент1 | 1 | 3 | 0.333333 |
| Клиент5 | 1 | 1 | 1 |
| Клиент2 | 1 | 2 | 0.5 |
| Клиент4 | 1 | 3 | 0.333333 |
| Клиент4 | 1 | 3 | 0.333333 |
| Клиент6 | 1 | 1 | 1 |
| Клиент1 | 1 | 3 | 0.333333 |
| Клиент2 | 1 | 2 | 0.5 |
| Клиент1 | 1 | 3 | 0.333333 |
| Клиент3 | 1 | 3 | 0.333333 |
| Клиент3 | 1 | 3 | 0.333333 |
| Клиент3 | 1 | 3 | 0.333333 |
| Клиент4 | 1 | 3 | 0.333333 |
Когда вы СУММИРУЕТЕ столбец деления с помощью функции SUMPPRODUCT, вы получаете в общей сложности шесть для шести уникальных значений.
Если вы используете Excel 365 , вы получите тот же результат, используя:
=COUNTA(UNIQUE(FILTER(C3:C14,C3:C14lt;gt;""))) Ответ №2:
a) C3:C15lt;gt;"" Значение равно 1 для всех ячеек, в которых что-то есть, и 0 для пустых ячеек.
b) COUNTIF(C3:C15, C3:C15amp;"") возвращает массив, показывающий, сколько раз каждое число появляется в диапазоне. Поэтому, если первое значение в диапазоне также появляется еще раз в другом месте диапазона, первое значение возвращаемого массива равно 2 (оно появляется дважды). Согласно комментарию @ amp;"" JodyHighroller, пустые ячейки учитываются как значение, чтобы избежать ошибок деления на ноль на следующем шаге. c) Деление (a) на (b) возвращает для непустых значений 1 для значений, которые появляются один раз, 1/2 для значений, которые появляются дважды, 1/3 для значений, которые появляются 3 раза и так далее. Каждому уникальному элементу списка будут присвоены значения, суммирующие до 1. Поскольку шаг (a) равен 0 для пустых ячеек, все пустые ячейки на этом этапе оцениваются как 0 (0/1 = 0, 0/2 = 0 и т. Д.).
d) SUMPRODUCT(...) из вышеперечисленного складывает их все вместе, каждое уникальное значение равно 1, так что общее количество всех этих значений равно количеству уникальных значений. ( SUM на самом деле сделал бы то же самое)
Более понятная формула, которая делает то же самое, была бы =COUNT(UNIQUE(C3:C15) - IF(COUNTBLANK(C3:C15)gt;0, 1, 0) (этот второй раздел необходим, потому UNIQUE что функция возвращает 0 для представления пустых ячеек). Однако это не сработало бы в некоторых старых версиях Excel, уникальная функция-совершенно новая вещь.
Комментарии:
1. Добавив
amp;""в конец, вы вернете значение 1 для пустой ячейки. 0/1 = 0, поэтому ошибка IF не требуется.2. @JodyHighroller вы правы — мне было интересно, что это там делает, так как это довольно продвинутая формула, в которой есть явно избыточная часть. Я отредактирую ответ, спасибо.