Числовые значения
В диапазоне
= СУММ(—(ЧАСТОТА(данные; данные)>0))
Если вам нужно подсчитать уникальные числовые значения в диапазоне, вы можете использовать формулу, которая использует частотную функцию вместе с функцией СУММ.
Предположим, у вас есть список номеров сотрудников вместе с часами работы над проектом, и вы хотите знать, сколько сотрудники работали над этим проектом. Глядя на данные, вы можете увидеть одно и то же число сотрудников более одного раза, так что вы хотите подсчитать уникальные номера служащих, которые появляются в списке.
ID номера работников появляются в диапазоне B5: B14. Для того, чтобы получить количество уникальных номеров, вы можете использовать следующую формулу:
= СУММ(— (ЧАСТОТА(B5:B14; B5:B14) > 0))
Функция ЧАСТОТА возвращает массив значений, которые соответствуют интервалам. В этом случае мы сравниваем один и тот же набор чисел для массива данных и для массива интервалов.
Результатом является то, что ЧАСТОТА возвращает массив значений, которые представляют собой счетчик для каждого числового значения в массиве данных. Это работает, потому что ЧАСТОТА имеет специальную функцию, которая автоматически возвращает ноль для любых чисел, которые появляются более чем один раз в массиве данных, поэтому обратный массив выглядит следующим образом:
{3; 0; 0; 2; 0; 3; 0; 0; 2; 0; 0}
Далее, каждое из этих значений проверяется, чтобы было больше нуля. Результат выглядит следующим образом:
{ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ}
Теперь каждая ИСТИНА в списке представляет собой уникальный номер в списке, и нам просто нужно сложить Истинные значения с СУММ.
Однако СУММ не будет добавлять логические значения в массиве, так что мы должны сначала превратить значения в 1 или ноль. Это делается с помощью двойного минуса (двойной унарный). В результате массив только 1 или 0.
{1; 0; 0; 1; 0; 1; 0; 0; 1; 0; 0}
И, наконец, СУММ добавляет эти значения и рассчитывает общее число, которое в данном случае равно 4.
Примечание: вы можете также использовать СУММПРОИЗВ для сложения элементов в массиве.
Использование СЧЁТЕСЛИ вместо ЧАСТОТА для подсчета уникальных значений
Другой способ подсчета уникальных числовых значений состоит в использовании СЧЁТЕСЛИ вместо ЧАСТОТА. Это гораздо проще, но нужно учитывать, что с помощью СЧЁТЕСЛИ на больших наборах данных для подсчета уникальных значений может вызвать проблемы с производительностью. Формула на основе частотных полос, хотя и более сложная, вычисляет гораздо быстрее.
С критериями
{=СУММ(—(ЧАСТОТА(ЕСЛИ(criteria;значения);значения)>0))}
Если вам нужно подсчитать уникальные числовые значения в диапазоне с критериями, можно использовать формулу, основанную на СУММ и частотной функции, вместе с функцией применять критерии.
Например, предположим, у вас есть список номеров сотрудников, которые записывали часы в двух разных зданиях: здание А и здание Б. Вы хотите знать, сколько уникальных сотрудников регистрирует время в каждом здании. Так как одно и то же число сотрудников более одного раза в списке, то вам нужна формула, которая будет рассчитывать уникальные идентификаторы сотрудника в здании.
В показанном примере, формула в G4 является:
{= СУММ(— (ЧАСТОТА(ЕСЛИ(B5:B14 = «A»; C5:C14); C5:C14)> 0))}
Примечание: это формула массива и должна быть введена с помощью сочетания клавиш Ctrl+Shift+Enter
Функция ЧАСТОТА рассчитывает массив значений, которые соответствуют массиву_интервалов. В этом случае мы подставлем «отфильтрованный» набор идентификаторов для массива данных, а также полный набор идентификаторов для массива_интервалов. Фильтрация осуществляется с помощью функции ЕСЛИ:
ЕСЛИ (B5:B14 = «А»; С5:С14)
Которая в примере рассчитывает это:
{81400; 81405; 81405; 82364; 82364; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}
Обратите внимание на то, что все идентификаторы не в здании А были преобразованы в ЛОЖЬ. Далее, ЧАСТОТА рассчитывает массив значений, которые представляют собой счетчик для каждого числового значения в массиве данных. Это работает, потому что ЧАСТОТА имеет специальную функцию, которая автоматически возвращает ноль для любых чисел, которые появляются более чем один раз в массиве данных, поэтому обратный массив выглядит следующим образом:
{1; 2; 0; 2; 0; 0; 0; 0; 0; 0; 0}
Далее, каждое из этих значений проверяется, чтобы было больше нуля. Результат выглядит следующим образом:
{ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}
Каждая ИСТИНА в списке представляет собой уникальный номер в списке, и нам просто нужно сложить Истинные значения с СУММ.
Однако СУММ не будет добавлять логические значения в массиве, так что мы должны сначала превратить значения в 1 или ноль. Это делается с помощью двойного минуса (двойной унарный). В результате массиве только 1 или 0.
{1; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0}
И, наконец, СУММ добавляет эти значения и рассчитывает общее число, которое в данном случае составляет 3.
Текстовые значения
В диапазоне
=СУММПРОИЗВ(—(ЧАСТОТА(ПОИСКПОЗ (данные;данные;0); СТРОКА (данные)- СТРОКА (данные.первая ячейка)+1)>0))
Если вам нужно подсчитать уникальные текстовые значения в диапазоне, вы можете использовать формулу, которая использует несколько функций: ЧАСТОТА, ПОИСКПОЗ, СТРОКА и СУММПРОИЗВ.
Кроме того, можно использовать СЧЁТЕСЛИ, как описано ниже.
Предположим, у вас есть список имен сотрудников вместе с часами работы над проектом, и вы хотите знать, сколько сотрудники работали над этим проектом. Глядя на данные, вы можете увидеть, что имена повторяются, а вы хотите подсчитать уникальные имена.
Имена работников находятся в диапазоне B5:B14. Для того, чтобы получить количество уникальных имен, вы можете использовать следующую формулу:
{=СУММПРОИЗВ(— (ЧАСТОТА(ПОИСКПОЗ(B5:B14; B5:B14;0); СТРОКА(B5:B14) -СТРОКА(B5) +1)> 0))}
Эта формула является более сложной, чем аналогичная формула, которая использует ЧАСТОТА для подсчета уникальных числовых значений, поскольку ЧАСТОТА не работает с нечисловыми значениями. В результате, большая часть формулы просто преобразует нечисловые данные в числовые данные, которые может обрабатывать ЧАСТОТА.
Функция ПОИСКПОЗ используется, чтобы получить позицию каждого элемента, который появляется в данных. Поскольку ПОИСКПОЗ только рассчитывает позицию значения «Первое совпадение», которое появляется более чем один раз, в данных тот же диапазон.
Поскольку ПОИСКПОЗ принимает и массив значений для значения совпадений аргумента, она рассчитывает массив позиций. Они подаются к частотному в аргументе массива данных.
{1; 1; 1; 4; 4; 6; 6; 6; 9; 9}
Аргумент, хранимый в массиве строится из этой части формулы:
СТРОКА(B5:B14) -СТРОКА(B5) +1
который использует номер строки для каждого элемента данных и номер строки первого элемента данных для построения прямого последовательного массива:
{1; 2; 3; 4; 5; 6; 7; 8; 9; 10}
Функция ЧАСТОТА возвращает массив значений, которые соответствуют «хранимым». В этом случае мы подставляем массив, рассчитываемый ПОИСКПОЗ для массива данных, а также рассчитываемый массив построчного массива выше, как массив хранимых.
Результатом является то, что ЧАСТОТА рассчитывает массив значений, которые указывают на количество, которое появляется каждое значение в массиве данных. ЧАСТОТА имеет специальную функцию, которая автоматически рассчитывает ноль для любых чисел, которые появляются более чем один раз в массиве данных, поэтому результирующий массив выглядит следующим образом:
{3; 0; 0; 2; 0; 3; 0; 0; 2; 0; 0}
Далее, каждое из этих значений преобразуется в значение ИСТИНА или ЛОЖЬ, которые > 0, а затем в 1 или ноль с двойным отрицанием (двойной минус). Это делается потому что СУММПРОИЗВ нужны числовые значения, она не может работать непосредственно с текстом или логическими значениями.
Внутри функции СУММПРОИЗВ конечный массив выглядит следующим образом:
{1; 0; 0; 1; 0; 1; 0; 0; 1; 0; 0}
И, наконец, СУММПРОИЗВ просто добавляет эти значения и рассчитывает общее число, которое в данном случае равно 4.
Обработка пустых ячеек в диапазоне
Если какая-либо из ячеек в диапазоне пустая, и вы хотите использовать ЧАСТОТА вместо СЧЁТЕСЛИ, вам необходимо использовать более сложную формулу массива, которая включает в себя ЕСЛИ:
{= СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(данные <> «»;ПОИСКПОЗ(данные; данные; 0));СТРОКА(данные) -СТРОКА(данные.первая ячейка) +1); 1))}
Примечание: поскольку логическая проверка в операторе ЕСЛИ содержит массив, то формула автоматически становится формулой массива, которая требует Ctrl+Shift+Enter. Поэтому СУММПРОИЗВ был заменен СУММ.
Рассмотрим формулу изнутри, ЕСЛИ требуется, потому что ПОИСКПОЗ рассчитает # N / A, если значение совпадений содержит пустые значения. Тестируя для пустых значений с данными <> «», в том числе и ПОИСКПОЗ как значение, если оно истинно, то результирующий массив будет содержать номера в сочетании с ЛОЖЬ:
{1; 1; ЛОЖЬ; 4; 4; 6; 6; ЛОЖЬ; 9; 9}
которая подается ЧАСТОТА, что и массив данных. ЧАСТОТА будет рассчитывать массив вроде этого:
{2; 0; 0; 2; 0; 2; 0; 0; 2; 0; 0}
Элементы в этом массиве преобразуются либо в 1, или ЛОЖЬ с окончательным (внешним) ЕСЛИ заявлением. Результат выглядит следующим образом:
{1; ЛОЖЬ; ЛОЖЬ; 1; ЛОЖЬ; 1; ЛОЖЬ; ЛОЖЬ; 1; ЛОЖЬ; ЛОЖЬ}
СУММ затем складывает 1 и рассчитывает 4.
Эта формула из замечательной книги Майка Гивина по формулам массива.
Использование СЧЁТЕСЛИ вместо ЧАСТОТЫ для подсчета уникальных значений
Другой способ подсчета уникальных числовых значений состоит в использовании СЧЁТЕСЛИ вместо ЧАСТОТЫ. Эта формула гораздо проще, но нужно учитывать, что использование СЧЁТЕСЛИ на больших наборах данных для подсчета уникальных значений, может вызвать проблемы с производительностью. Формула на основе ЧАСТОТА, хоть и более сложная, но вычисляет гораздо быстрее.
С критериями
{=СУММ(—(ЧАСТОТА(ЕСЛИ(критерий;ПОИСКПОЗ(значение; значение;0));СТРОКА(значение)-СТРОКА(значение.первая ячейкаl)+1)>0))}
Для уникальных значений в диапазоне с критериями, вы можете использовать формулу массива, основанную на функции ЧАСТОТА.
Предположим, у вас есть список имен сотрудников вместе с часами работы над проектом, и вы хотите знать, сколько сотрудники работали над этим проектом. Глядя на данные, вы можете увидеть, что имена повторяются, а вы хотите подсчитать уникальные имена.
В показанном примере формула в G5 является:
= {СУММ(—(ЧАСТОТА(ЕСЛИ(C5:C11 = G4;СТРОКА(B5:B11; B5:B11;0));СТРОКА(B5:B11) — СТРОКА(B5)+1) > 0))}
Эта формула использует ЧАСТОТА для подсчета уникальных числовых значений, которые получены с помощью функции ПОИСКПОЗ, которая сравнивает все значения против самих себя, чтобы определить позицию.
Функция ПОИСКПОЗ используется, чтобы получить позицию каждого элемента, который появляется в данных. Поскольку ПОИСКПОЗ рассчитывает только позиции значения «Первое совпадение», которые появляются более чем один раз, в данных рассчитывается то же число.
С помощью ЕСЛИ ПОИСКПОЗ рассчитывает только для строк, которые соответствуют критериям.
В конце концов, массив позиций, генерируемых ПОИСКПОЗ подаются к частотному в аргументе массива данных.
Аргумент, хранимый в массиве, строится из этой части формулы:
СТРОКА(B5:B11) -СТРОКА(B5) +1
который использует номер строки для каждого элемента данных и номер строки первого элемента данных для построения прямого, последовательного массива:
{1; 2; 3; 4; 5; 6; 7; 8; 9; 10}
Функция ЧАСТОТА рассчитывает массив значений, которые соответствуют «хранимым». В этом случае мы подставляем один и тот же набор чисел, как для массива данных и хранимых для массива.
Результатом является то, что ЧАСТОТА рассчитывает массив значений, которые указывают на количество, которое появляется каждое значение в массиве данных. Это работает, потому что ЧАСТОТА запрограммирована так, чтобы рассчитывать ноль для любых чисел, которые появляются более чем один раз в массиве данных.
Далее, каждое из этих значений преобразуется в значение ИСТИНА или ЛОЖЬ, которые > 0, а затем в 1 или ноль с двойным отрицанием (двойной минус). Это сделано, чтобы превратить все ненулевые значения в 1.
Наконец, СУММПРОИЗВ просто складывает эти значения и рассчитывает общее число.
Примечание: это формула массива и должна быть введена с помощью Ctrl + Shift + Enter.
Обработка пустых ячеек в диапазоне
Если какая-либо из ячеек в диапазоне пустая, вам необходимо скорректировать формулу, добавив дополнительно ЕСЛИ для предотвращения пустых клеток, передающуюся в функцию ПОИСКПОЗ, которая сгенерирует сообщение об ошибке. Формула:
{= СУММ(— (ЧАСТОТА(ЕСЛИ(B5:B11 <> «»; ЕСЛИ(C5:C11 = G4; ПОИСКПОЗ(B5:B11; B5:B11;0))); СТРОКА(B5:B11) -СТРОКА(B5) +1)> 0))}
С двумя критериями
Если у вас есть два критерия, вы можете расширить логику формулы путем добавления другого вложенного ЕСЛИ:
= {СУММ(— (ЧАСТОТА(ЕСЛИ(c1; ЕСЛИ(c2; ПОИСКПОЗ (значения;значения;0))); СТРОКА (значения) — СТРОКА (значения.первая позиция) +1)> 0))}
Там, где c1 = критерий1, c2 = критерий2 и значения = диапазон значений.
Взято из замечательной книги Майка Гивина по формулам массива, Control-Shift-Enter.
В диапазоне с СЧЁТЕСЛИ
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(данные;данные))
Если вам нужно подсчитать количество уникальных значений в диапазоне ячеек, вы можете использовать формулу, которая использует СЧЁТЕСЛИ и СУММПРОИЗВ.
СЧЁТЕСЛИ находится внутри диапазона данных и подсчитывает количество раз, которое появляется каждое отдельное значение данных. Результатом является массив чисел, который выглядит следующим образом:
{3; 3; 3; 2; 2; 3; 3; 3; 2; 2}.
После того, как СЧЁТЕСЛИ заканчивает, результаты используются в качестве делителя, 1 в качестве числителя. Значения, которые появляются в данных, после появляются в массиве как 1, но значения, которые появляются несколько раз будут отображаться как дробные значения, которые соответствуют множеству. (Т.е. значение, которое появляется в 5 раз, данные будут генерировать 5 элементов в массиве со значением 1/5 = 0,2).
Наконец, функция СУММПРОИЗВ суммирует все значения в массиве и рассчитывает результат.
Обработка пустых ячеек
Если данные могут содержать пустые ячейки, необходимо скорректировать формулу следующим образом:
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(данные; данные&»»))
Данные &»» выражение предотвращает нули массива, созданного СЧЁТЕСЛИ, когда имеются пустые ячейки данных. Она делает это путем обеспечения того, что критерии для пустой ячейки «», а не ноль. Это важно, так как ноль в делителе выбросит #ДЕЛ/0. Так что эта версия формулы не будет выдавать ошибку, когда есть пустые ячейки, но она будет включать в себя пустые ячейки в счете. Если вы хотите исключить пустые ячейки в счете, используйте:
=СУММПРОИЗВ((данные <> «»)/СЧЁТЕСЛИ(данные;данные и «»))
Низкая производительность?
Это крутая и элегантная формула, но она вычисляет гораздо медленнее, чем формулы, которые используют ЧАСТОТА для подсчета уникальных значений. Для больших наборов данных, вы можете переключиться на формулу, основанную на частотную функцию.