Формула Excel - считайте уникальные проекты, но исключайте «Admin»

Question

Я ищу справку, связанную с Excel, для формулы, которую я сам не смог понять.

У меня есть примерный список данных в диапазоне A1:B7 - Столбец A - это список имен (например, Джон, Джейн и т.д.), А столбец B - список кодов проекта (например, ABC, ABD, Admin и т.д.), Как этот:

   A      B
  John   ABC
  Jane   ABD
  John   Admin
  Jane   ABC
  Mike   ABD
  John   ABC
  Jane   Admin

Я хочу , чтобы подсчитать количество уникальных проектов , что Джон и Джейн уже каждый работали над но исключить проект администратора.

Например, Джон дважды работал над проектами ABC и Admin, поэтому уникальное количество проектов для Джона было бы равно 1.

Джейн работала над проектами ABC, ABD и Admin, поэтому количество уникальных проектов для Джейн будет равно 2.

Я перепробовал все различные комбинации сумм, таблиц и сводных таблиц, но не сумел подсчитать уникальные проекты за исключением администратора.

Answer 1

Вот как это сделать, если таблица отсортирована по имени. Я полагаю, вы могли бы сделать это.

=SUM(1/COUNTIF(B1:B4;B1:B4))-IF(COUNTIF(B1:B4;"=Admin")>0;1;0)

Возможно, вам придется заменить ; с , по формуле, если ваша версия Excel отличается от моей. Обязательно подтвердите эту формулу, нажав CTRL+SHIFT+ENTER , а не просто ENTER . Это подсчитывает количество уникальных записей для B1:B4 , исключая Admin. Предполагая, что B1:B4 - только проект Джона, это будет номер Джона.

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

Я использовал следующую информацию: вот ссылка, объясняющая, как подсчитать количество уникальных значений в столбце. Он использует то, что называется функциями массива. Вы можете прочитать о них здесь.

UPD: Также смотрите ответ на этот вопрос. Здесь обсуждается, как исключить ячейку из диапазона, что должно быть полезно. Вы бы расширили B1:B4 на весь столбец, а затем исключили бы те ячейки, в которых нет Джона в соседней ячейке. Я не мог попробовать это в моем Excel 2003.