Уникальное количество значений в столбце в месяц

#excel

Вопрос:

Excel-Таблица:

      |       A        |         B       |       C          |    D   |   E     |     F   |     G   |
-----|----------------|-----------------|------------------|--------|---------|---------|---------|-----
1    |   monthamp;year   |      date       |     customer     |        | 2020-01 | 2020-03 | 2020-04 |
-----|----------------|-----------------|------------------|--------|---------|---------|---------|-----
2    |     2020-01    |    2020-01-10   |    Customer A    |        |    3    |    2    |    4    |
3    |     2020-01    |    2020-01-14   |    Customer A    |        |         |         |         |
4    |     2020-01    |    2020-01-17   |    Customer B    |        |         |         |         | 
5    |     2020-01    |    2020-01-19   |    Customer B    |        |         |         |         | 
6    |     2020-01    |    2020-01-23   |    Customer C    |        |         |         |         | 
7    |     2020-01    |    2020-01-23   |    Customer B    |        |         |         |         | 
-----|----------------|-----------------|---------------- -|--------|---------|---------|---------|-----
8    |     2020-03    |    2020-03-18   |    Customer E    |        |         |         |         | 
9    |     2020-03    |    2020-03-19   |    Customer A    |        |         |         |         | 
-----|----------------|-----------------|------------------|--------|---------|---------|---------|-----
10   |     2020-04    |    2020-04-04   |    Customer B    |        |         |         |         | 
11   |     2020-04    |    2020-04-07   |    Customer C    |        |         |         |         | 
12   |     2020-04    |    2020-04-07   |    Customer A    |        |         |         |         | 
13   |     2020-04    |    2020-04-07   |    Customer E    |        |         |         |         | 
14   |     2020-04    |    2020-04-08   |    Customer A    |        |         |         |         | 
15   |     2020-04    |    2020-04-12   |    Customer A    |        |         |         |         | 
16   |     2020-04    |    2020-04-15   |    Customer B    |        |         |         |         | 
17   |                |

В моем файле Excel я хочу рассчитать уникальное количество кутомеров в месяц, как вы можете видеть в Cell E2:G2 .

Я уже вставил Column A в качестве вспомогательного столбца, который извлекает только месяц и год из даты Column B .
Таким образом, форматирование даты такое же, как и в строке времени Cell E1:G2 .

Я предполагаю, что формула для получения уникального количества в месяц как-то связана =COUNTIFS($A:$A,E$1) , но я понятия не имею, как изменить эту формулу, чтобы получить ожидаемые значения.

У тебя есть какие-нибудь идеи?

1. Вы можете создать сводную таблицу, в которой вы берете «месяц и год» в качестве строк и используете «клиент» в качестве значений. При инициализации сводной таблицы выберите добавление данных в модель данных. Затем вы можете установить значения для отображения уникального количества.

2. Какая у вас версия Excel?

3. Офис 365. Однако, если у вас также есть решение для Excel 2016, было бы здорово.

Ответ №1:

Вот один из подходов, который будет работать для Office 365, и если у вас есть доступ к UNIQUE :

=COUNTA(UNIQUE(IF($A$2:$A$16=G$1,$C$2:$C$16,""),,FALSE))-1

Для более старых версий следующее будет работать с CTRL SHIFT ENTER (ввод массива)

=SUM(--(FREQUENCY(IFERROR(MATCH($A$2:$A$16amp;$C$2:$C$16,E$1amp;$C$2:$C$16,0),"a"),MATCH($A$2:$A$16amp;$C$2:$C$16,E$1amp;$C$2:$C$16,0))>0))

Ответ №2:

Вы можете сделать это без какой-либо помощи.

 =SUM(--(UNIQUE(FILTER($C$2:$C$16,TEXT($B$2:$B$16,"yyyy-mm")=E$1))<>""))

Для более старой версии excel используйте приведенную ниже формулу со вспомогательным столбцом.

 =SUMPRODUCT(--($A$2:$A$16=D$1)*(1/COUNTIFS($A$2:$A$16,$A$2:$A$16,$C$2:$C$16,$C$2:$C$16)))

1. Я не знаю почему, но на моей стороне работает только тогда, когда я использую вспомогательный столбец и эту формулу: =СУММА(—(УНИКАЛЬНЫЙ(ФИЛЬТР($C$2:$C$16;$A$2:$A$16=E$1))<>»»)). Возможно, это вызвано тем, что я использую немецкую версию Excel. У вас также есть решение для более старых версий Excel, которые не имеют УНИКАЛЬНОЙ функции?

2. Для более старой версии excel вам необходимо использовать вспомогательный столбец, как показано в вашем вопросе. Затем используйте эту формулу =SUMPRODUCT(--($A$2:$A$16=D$1)*(1/COUNTIFS($A$2:$A$16,$A$2:$A$16,$C$2:$C$16,$C$2:$C$16)))