Excel: подсчет дубликатов только один раз, на основании третьего критерия

Question

Поэтому я получил лист Excel, в котором перечислены (среди прочего) организации и год, в котором они были активны. Некоторые организации действовали более одного раза в год. Это выглядит так:

ГОД - ОРГАНИЗАЦИЯ

2015 --- Orga A
2015 --- Orga A
2015 --- Orga B
2014 --- Orga C
2014 --- Orga C
2013 --- Orga D
2013 --- Orga D

Я пытаюсь найти формулу, которая даст мне число уникальных организаций, работающих в год (в примере: 2 в 2015 году, 1 в 2014 году, 1 в 2013 году).

Что я пробовал: я создал новый столбец в C и ввел эту формулу в каждой строке:

=COUNTIF($B$2:B*X*,B*X*)=1

X = 2 (B2), в первом ряду, и затем увеличивается на 1 на строку (например, B2, B3, B4). Будет выведено, является ли организация активной в первый раз (как ДА / НЕТ).

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

Написание вышеупомянутой функции для каждого года не вариант, так как лист должен быть своего рода «самодостаточным», так как это постоянный список, который потребуется для учета будущих лет.

Я чувствую, что это можно решить с помощью формулы массива и функции IF, но я не уверен, как ... Любой вклад будет высоко ценится! (:

Answer 1

Мне показалось очень странным, что никто не ответил на это ... Ну, я попробую тогда.

Хорошо, если вы не любите сводные таблицы, я могу предложить вам пару решений, которые не используют их.

ПЕРВЫЙ ПУТЬ

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

В приведенном мной примере я поместил ваш список в столбец A (диапазон A1:A7), список возможных названий организаций в диапазоне C2:C5 и годы с 2013 по 2015 год в ячейках от D1 до F1 .

Теперь, когда у вас есть это, вы можете получить то, что вам нужно знать, выполнив следующие действия:

1. в D2 положить =IF(IFERROR(MATCH(CONCATENATE(D$1," --- ",$C2),$A$1:$A$7,0),0)>0,1,0) затем копировать формулу до F5 . Это создаст сетку из 0 и 1, где 1 означает, что организация была активна в отчетном году, а 0 означает, что это не так.

2. Тогда все, что вам нужно сделать, это просто получить суммы в строке 6 от D до F (просто поместите =SUM(D2:D5) в D6 а затем скопируйте формулу до F6).

В противном случае, если вам не нужна или не нужна сетка, вы можете использовать формулу CSE/ массив (можно найти некоторые ссылки, чтобы узнать о них больше в другом из моих ответов).

В этом примере в D7 я написал =SUM(IF(IFERROR(MATCH(CONCATENATE(D$1," --- ",$C2:$C5),$A$1:$A$7,0),0)>0,1,0)) затем нажмите Ctrl+Shift+Enter и скопируйте формулу до F7 . (Просто отметьте, что шаги 1. и 2. не нужны вообще, чтобы получить желаемые результаты в этом случае.)

ВТОРОЙ ПУТЬ

Наконец, если вы не можете легко получить список всех организаций, при условии, что вы можете составить список только тех лет, которые вы заинтересованы в проверке, возможное решение может быть следующим:

1. Выберите весь диапазон данных (в моем примере это A1:A7), перейдите в "Набор данных" и выберите "Удалить дубликаты".

В этом примере должны остаться только 4 строки, и они будут уникальными комбинациями year --- organization name (пример того, как работает удаление дубликатов), поэтому теперь мы можем просто "посчитать", сколько раз год появляется в данных оставшихся строк спектр. Количество строк, которые останутся после удаления дубликатов, не очень важно, так как мы будем считать весь столбец A диапазоном данных.

2. Таким образом, после удаления дубликатов остается использовать формулу (например, в D8 и затем копировать в F8) =COUNTIF($A:$A,CONCATENATE(D$1," --- *")) ( просто обратите внимание, что звездочка в формуле является подстановочным знаком Excel, некоторые ссылки можно найти в том же другом ответе, о котором я упоминал ранее).

и это все.