Excel сумма только уникальных значений - Word и Excel - помощь в работе с программами

Подсчет уникальных значений в Excel

В этом руководстве вы узнаете, как посчитать уникальные значения в Excel с помощью формул и как это сделать в сводной таблице. Мы также разберём несколько примеров счёта уникальных текстовых и числовых значений, в том числе с учетом регистра букв.

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

И вот о чем мы сейчас поговорим:

Как посчитать уникальные значения в столбце.
Считаем уникальные текстовые значения.
Подсчет уникальных чисел.
Как посчитать уникальные с учётом регистра.
Формулы для подсчета различных значений.
Как не учитывать пустые ячейки?
Сколько встречается различных чисел?
Считаем различные текстовые значения.
Как сосчитать различные текстовые значения с учетом условий?
Считаем количество различных чисел с ограничениями.
Как учесть регистр при подсчёте?
Как посчитать уникальные строки?
Используем сводную таблицу.

Если вы регулярно посещаете этот блог, вы уже знаете формулу Excel для подсчета дубликатов. А сегодня мы собираемся изучить различные способы подсчета уникальных значений в Excel. Но для ясности давайте сначала определимся с терминами.

Уникальные значения – те, которые появляются в списке только один раз.
Различные – это все, которые имеются в списке без учета повторов, то есть уникальные плюс первое вхождение повторяющихся.

Следующий рисунок иллюстрирует эту разницу:

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

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

Считаем уникальные значения в столбце.

Предположим, у вас есть столбец с именами на листе Excel, и вам нужно подсчитать, сколько там есть неповторяющихся. Самое простое решение состоит в том, чтобы использовать функцию СУММ в сочетании с ЕСЛИ и СЧЁТЕСЛИ :

=СУММ(ЕСЛИ(СЧЁТЕСЛИ(диапазон ; диапазон ) = 1,1,0))

Примечание. Это формула массива, поэтому обязательно нажмите Ctrl + Shift + Enter, чтобы корректно ввести её. Как только вы это сделаете, Excel автоматически заключит всё выражение в {фигурные скобки}, как показано на скриншоте ниже. Ни в коем случае нельзя вводить фигурные скобки вручную, это не сработает.

В этом примере мы считаем уникальные имена в диапазоне A2: A10, поэтому наше выражение выглядит так:

{=СУММ(ЕСЛИ(СЧЁТЕСЛИ(A2:A10;A2:A10)=1;1;0))}

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

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

Как работает формула подсчета уникальных значений?

Как видите, здесь используются 3 разные функции – СУММ, ЕСЛИ и СЧЁТЕСЛИ. Посмотрим, что делает каждая из них:

Функция СЧЁТЕСЛИ считает, сколько раз каждое отдельное значение появляется в анализируемом диапазоне.

В этом примере СЧЁТЕСЛИ(A2:A10;A2:A10)возвращает массив {3:2:2:1:1:2:3:2:3}.

Функция ЕСЛИ оценивает каждый элемент в этом массиве, сохраняет все единицы (то есть, уникальные) и заменяет все остальные цифры нулями.

Итак, функция ЕСЛИ(СЧЁТЕСЛИ(A2:A10;A2:A10)=1;1;0) преобразуется в ЕСЛИ({3:2:2:1:1:2:3:2:3}) = 1,1,0).

И далее она превращается в массив чисел {0:0:0:1:1:0:0:0:0}. Здесь 1 означает уникальное значение, а 0 – появляющееся более 1 раза.

Наконец, функция СУММ складывает числа в этом итоговом массиве и выводит общее количество уникальных значений. Что нам и нужно.

Подсчет уникальных текстовых значений.

Если ваш список содержит как числа так и текст, и вы хотите посчитать только уникальные текстовые строки, добавьте функцию ЕТЕКСТ() в формулу массива, описанную выше:

{=СУММ(ЕСЛИ(ЕТЕКСТ(A2:A10)*СЧЁТЕСЛИ(A2:A10;A2:A10)=1;1;0))}

Функция ЕТЕКСТ возвращает ИСТИНА, если исследуемое содержимое ячейки является текстом, и ЛОЖЬ в противоположном случае. Поскольку звездочка (*) в формулах массива работает как оператор И, то функция ЕСЛИ возвращает 1, только если рассматриваемое одновременно текстовое и уникальное, в противном случае получаем 0. И после того, как функция СУММ сложит все числа, вы получите количество уникальных текстовых значений в указанном диапазоне.

Не забывайте нажимать Ctrl + Shift + Enter, чтобы правильно ввести формулу массива, и вы получите результат, подобный этому:

Рис3

Как вы можете видеть на скриншоте выше, мы получили общее количество уникальных текстовых значений, исключая пустые ячейки, числа, логические выражения ИСТИНА и ЛОЖЬ, а также ошибки.

Как сосчитать уникальные числовые значения.

Чтобы посчитать уникальные числа в списке данных, используйте формулу массива точно так же, как мы только что делали при подсчете текстовых данных. Отличие заключается в том, что вы используете ЕЧИСЛО вместо ЕТЕКСТ:

{=СУММ(ЕСЛИ(ЕЧИСЛО(A2:A10)*СЧЁТЕСЛИ(A2:A10;A2:A10)=1;1;0))}

Пример и результат вы видите на скриншоте чуть выше.

Уникальные значения с учетом регистра.

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

{=ЕСЛИ(СУММ((—СОВПАД($A$2:$A$10;A2)))=1;»Уникальный»;»Дубль»)}

А затем используйте простую функцию СЧЁТЕСЛИ для подсчета уникальных значений:

=СЧЁТЕСЛИ(B2:B10; «Уникальный»)

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

Подсчет различных значений.

Используйте следующую универсальное выражение:

{=СУММ(1 / СЧЁТЕСЛИ( диапазон ; диапазон ))}

Помните, что это формула массива, поэтому вам следует нажать Ctrl + Shift + Enter, вместо обычного Enter.

Кроме того, вы можете использовать функцию СУММПРОИЗВ и записать формулу обычным способом:

=СУММПРОИЗВ(1 / СЧЁТЕСЛИ( диапазон ; диапазон ))

Например, чтобы сосчитать различные значения в диапазоне A2: A10, вы можете использовать выражение:

{=СУММ(1/СЧЁТЕСЛИ(A2:A10;A2:A10))}

или же

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A10;A2:A10))

Этот способ подходит не только для подсчета в столбце, но и для диапазона данных. К примеру, у нас под имена отведено две колонки. Тогда делаем так:

{=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:B10;A2:B10))}

Этот метод подходит для текста, чисел, дат.

Единственное ограничение – диапазон должен быть непрерывным и не содержать пустых ячеек и ошибок.

Если в вашем диапазоне данных есть пустые ячейки, то можно изменить:

{=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A10; A2:A10&»»))}

Тогда в расчёт попадёт и будет засчитана и пустая ячейка.

Как это работает?

Как вы уже знаете, мы используем функцию СЧЁТЕСЛИ, чтобы узнать, сколько раз каждый отдельный элемент встречается в указанном диапазоне. В приведенном выше примере, результат работы функции СЧЕТЕСЛИ представляет собой числовой массив: {3:2:2:1:3:2:1:2:3}.

После этого выполняется ряд операций деления, где единица делится на каждую цифру из этого массива. Это превращает все неуникальные значения в дробные числа, соответствующие количеству повторов. Например, если число или текст появляется в списке 2 раза, в массиве создаются 2 элемента равные 0,5 (1/2 = 0,5). А если появляется 3 раза, в массиве создаются 3 элемента 0,333333.

В нашем примере результатом вычисления выражения 1/СЧЁТЕСЛИ(A2:A10;A2:A10) является массив {0.333333333333333:0.5:0.5:1:0.333333333333333:0.5:1:0.5:0.333333333333333}.

Пока не слишком понятно? Это потому, что мы еще не применили функцию СУММ / СУММПРОИЗВ. Когда одна из этих функций складывает числа в массиве, сумма всех дробных чисел для каждого отдельного элемента всегда дает 1, независимо от того, сколько раз он появлялся. И поскольку все уникальные элементы отображаются в массиве как единицы (1/1 = 1), окончательный результат представляет собой общее количество всех встречающихся значений.

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

Помните, что все приведенные ниже выражения являются формулами массива и требуют нажатия Ctrl + Shift + Enter.

Подсчет различных значений без учета пустых ячеек

Если столбец, в котором вы хотите совершить подсчет, может содержать пустые ячейки, вам следует в уже знакомую нам формулу массива добавить функцию ЕСЛИ. Она будет проверять ячейки на наличие пустот (основная формула Excel, описанная выше, в этом случае вернет ошибку #ДЕЛ/0):

=СУММ(ЕСЛИ( диапазон <> «»; 1 / СЧЁТЕСЛИ( диапазон ; диапазон ); 0))

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

Используем:

{=СУММ(ЕСЛИ(A2:A10<>»»;1/СЧЁТЕСЛИ(A2:A10; A2:A10); 0))}

Как видите, наш список состоит из трёх имён.

Подсчет различных чисел.

Чтобы посчитать различные числовые значения (числа, даты и время), используйте функцию ЕЧИСЛО:

= СУММ(ЕСЛИ(ЕЧИСЛО( диапазон ); 1 / СЧЁТЕСЛИ( диапазон ; диапазон ); «»))

Считаем, сколько имеется различных чисел в диапазоне A2: A10:

{=СУММ(ЕСЛИ(ЕЧИСЛО(A2:A10);1/СЧЁТЕСЛИ(A2:A10; A2:A10);»»))}

Результат вы можете посмотреть ниже.

Это достаточно простое и элегантное решение, но работает оно гораздо медленнее, чем выражения, которые используют функцию ЧАСТОТА для подсчета уникальных значений. Если у вас большие наборы данных, то целесообразно переключиться на формулу, основанную на расчёте частот.

И вот еще один способ подсчета чисел:

=СУММ(—(ЧАСТОТА(диапазон; диапазон)>0))

Применительно к примеру ниже:

=СУММ(—(ЧАСТОТА(A2:A10; A2:A10)>0))

Как видите, здесь игнорируются записи, в которых имеются буквы.

Пошагово разберём, как это работает.

Функция ЧАСТОТА возвращает массив цифр, которые соответствуют интервалам, заданным имеющимися числами. В этом случае мы сравниваем один и тот же набор чисел для массива данных и для массива интервалов.

Результатом является то, что ЧАСТОТА() возвращает массив, который представляет собой счетчик для каждого числового значения в массиве данных.

Это работает, потому что ЧАСТОТА() возвращает ноль для любых чисел, которые ранее уже появились в списке. Ноль возвращается и для текстовых данных. Поэтому полученный массив выглядит следующим образом:

{3:0:0:2:0:0}

Как видите, обрабатываются только числа. Ячейки A7:A10 игнорируются, потому что там текст. А функция ЧАСТОТА() работает только с числами.

Теперь каждое из этих чисел проверяем на условие «больше нуля».

Получаем:

{ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ}

Теперь превращаем ИСТИНА и ЛОЖЬ в 1 и 0 соответственно. Делаем это при помощи двойного отрицания. Проще говоря, это двойной минус, который не меняет величину числа, но позволяет получить реальные числа, когда это вообще возможно:

{1:0:0:1:0:0}

А теперь функция СУММ складывает всё и получаем результат: 2.

Различные текстовые значения.

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

Как вы можете легко догадаться, мы просто добавим функцию ЕТЕКСТ и проверку условия:

=СУММ(ЕСЛИ(ЕТЕКСТ( диапазон ); 1 / СЧЁТЕСЛИ( диапазон ; диапазон ); «»))

Количество индивидуальных символьных значений посчитаем так:

{=СУММ(ЕСЛИ(ЕТЕКСТ(A2:A10);1/СЧЁТЕСЛИ(A2:A10; A2:A10);»»))}

Не забываем, что это формула массива.

Если в вашей таблице нет пустых ячеек и ошибок, то вы можете применить формулу, которая использует несколько функций: ЧАСТОТА, ПОИСКПОЗ, СТРОКА и СУММПРОИЗВ.

В общем виде это выглядит так:

=СУММПРОИЗВ(—(ЧАСТОТА(ПОИСКПОЗ (диапазон; диапазон;0); СТРОКА (диапазон)- СТРОКА (диапазон_первая_ячейка)+1)>0))

Предположим, у вас есть список имен сотрудников вместе с часами работы над проектом, и вы хотите знать, сколько человек в этом участвовали. Глядя на данные, вы можете увидеть, что имена повторяются. А вы хотите пересчитать всех, кто хотя бы раз появился в этом списке.

Применяем формулу массива:

{=СУММПРОИЗВ(— (ЧАСТОТА(ПОИСКПОЗ(A2:A10; A2:A10;0); СТРОКА(A2:A10) -СТРОКА(A2) +1)> 0))}

Она является более сложной, чем аналогичная, которая использует функцию ЧАСТОТА() для подсчета различных чисел. Это потому, что ЧАСТОТА() не работает с текстом. Поэтому ПОИСКПОЗ преобразует имена в номера позиций, которые может обрабатывать ЧАСТОТА().

Если какая-либо из ячеек в диапазоне пустая, вам необходимо использовать более сложную формулу массива, которая включает в себя функцию ЕСЛИ:

{= СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(данные <> «»;ПОИСКПОЗ(данные; данные; 0));СТРОКА(данные) -СТРОКА(данные_первая_ячейка) +1); 1))}

Примечание: поскольку логическая проверка в операторе ЕСЛИ содержит массив, то наше выражение сразу становится формулой массива, которая требует ввода через Ctrl+Shift+Enter. Поэтому же СУММПРОИЗВ была заменена на СУММ.
Применительно к нашему примеру это выглядит так:

{=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(A2:A10 <> «»;ПОИСКПОЗ(A2:A10; A2:A10; 0));СТРОКА(A2:A10) -СТРОКА(A2) +1); 1))}

Теперь «сломать» этот расчет может только наличие ячеек с ошибками в исследуемом диапазоне.

Различные текстовые значения с условием.

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

Чтобы решить эту проблему, вам может помочь этот вариант:

{=СУММПРОИЗВ((($A$2:$A$18=E2)) / СЧЁТЕСЛИМН($A$2:$A$18;$A$2:$A$18&»»; $B$2:$B$18;$B$2:$B$18&»»))}

Введите это в пустую ячейку, куда вы хотите поместить результат, F2, например. А затем нажмите Shift + Ctrl + Enter вместе, чтобы получить правильный результат.

Поясним: здесь A2:A18 это список покупателей, с учётом которого вы ограничиваете область расчётов, B2: B18 — перечень товаров, в котором вы хотите посчитать уникальные значения, Е2 содержит критерий, на основании которого подсчет ограничивается только конкретным покупателем.

Второй способ.

Для уникальных значений в диапазоне с критериями, вы можете использовать формулу массива, основанную на функции ЧАСТОТА.

{=СУММ(—(ЧАСТОТА(ЕСЛИ(критерий; ПОИСКПОЗ(диапазон; диапазон;0)); СТРОКА(диапазон) -СТРОКА(диапазон_первая_ячейкаl)+1)>0))}

Применительно к нашему примеру:

{=СУММ(—(ЧАСТОТА(ЕСЛИ(A2:A10 = E2; ПОИСКПОЗ(B2:B10; B2:B10;0)); СТРОКА(B2:B10) — СТРОКА(B2)+1) > 0))}

С учетом ограничений ЕСЛИ() функция ПОИСКПОЗ определяет порядковый номер только для строк, которые соответствуют критериям.

Если какая-либо из ячеек в диапазоне критериев пустая, вам необходимо скорректировать расчёт, добавив дополнительно ЕСЛИ для обработки пустых ячеек. Иначе они будут переданы в функцию ПОИСКПОЗ, которая в ответ сгенерирует сообщение об ошибке.

Вот что получилось после корректировки:

{=СУММ(— (ЧАСТОТА(ЕСЛИ(B2:B10 <> «»; ЕСЛИ(A2:A10 = E2; ПОИСКПОЗ(B2:B10; B2:B10;0))); СТРОКА(B2:B10) -СТРОКА(B2) +1)> 0))}

То есть все действия и расчёты мы производим, если в столбце B нам встретилась непустая ячейка: ЕСЛИ(B2:B10 <> «»….

Если у вас есть два критерия, вы можете расширить логику формулы путем добавления другого вложенного ЕСЛИ.

Поясним. Определим, сколько наименований товара находилось в первой партии первого покупателя.

Критерии запишем в G2 и G3.

В общем виде это выглядит так:

{=СУММ(—(ЧАСТОТА(ЕСЛИ(критерий1; ЕСЛИ(критерий2; ПОИСКПОЗ (диапазон; диапазон;0))); СТРОКА (диапазон) — СТРОКА (диапазон_первая_позиция) +1)> 0))}

Подставляем сюда реальные данные и получаем результат:

{=СУММ(—(ЧАСТОТА(ЕСЛИ(A2:A10=G2; ЕСЛИ(C2:C10=G3;ПОИСКПОЗ(B2:B10;B2:B10;0)));СТРОКА(B2:B10)-СТРОКА(B2)+1)>0))}

В первой партии 2 наименования товара, хотя и 3 позиции.

Различные числа с условием.

Если вам нужно пересчитать уникальные (с учётом первого вхождения) числа в диапазоне с учетом каких-то ограничений, можно использовать формулу, основанную на СУММ и ЧАСТОТА, и вместе с этим применять критерии.

{=СУММ(— (ЧАСТОТА(ЕСЛИ(критерий; диапазон); диапазон)> 0))}

Предположим, у нас есть перечень табельных номеров и количество отработанных часов по дням. Нужно сосчитать, сколько человек хотя бы раз отработали менее чем по 8 часов, то есть неполную смену.

Вот наша формула массива:

{=СУММ(— (ЧАСТОТА(ЕСЛИ(B2:B10 < 8; A2:A10); A2:A10)> 0))}

Как видите, таких случаев 3, но связаны они с двумя работниками.

Различные значения с учетом регистра.

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

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

{=ЕСЛИ(СУММ((—СОВПАД($A$2:$A2;$A2)))=1;»Уникальный»;»»)}

Как вы помните, все формулы массива в Excel требуют нажатия Ctrl + Shift + Enter.

После того, как это выражение будет записано, вы можете посчитать «различные» значения с помощью обычной функции СЧЁТЕСЛИ, например:

=СЧЁТЕСЛИ(B2:B10; «Уникальный»)

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

{=СУММ(ЕСЛИОШИБКА(1/ЕСЛИ($A$2:$A$10<>»»; ЧАСТОТА(ЕСЛИ(СОВПАД($A$2:$A$10; ТРАНСП($A$2:$A$10)); ПОИСКПОЗ(СТРОКА($A$2:$A$10); СТРОКА($A$2:$A$10)); «»); ПОИСКПОЗ(СТРОКА($A$2:$A$10); СТРОКА($A$2:$A$10))); 0); 0))}

Как видите, обе формулы дают одинаковые результаты.

Подсчет уникальных строк в таблице.

Подсчет уникальных / различных строк в Excel сродни пересчёту уникальных и различных значений. С той лишь разницей, что вы используете функцию СЧЁТЕСЛИМН вместо СЧЁТЕСЛИ, что позволяет вам указать сразу несколько столбцов для проверки уникальности.

Например, чтобы подсчитать уникальные строки на основе столбцов A (Имя) и B (Фамилия), используйте один из следующих вариантов:

Для уникальных строк:

{=СУММ(ЕСЛИ(СЧЁТЕСЛИМН(A3:A11;A3:A11; B3:B11;B3:B11)=1;1;0))}

Для различных строк:

{=СУММ(1/СЧЁТЕСЛИМН(A3:A11;A3:A11;B3:B11;B3:B11))}

Естественно, вы не ограничены только двумя столбцами. Функция СЧЁТЕСЛИМН может обрабатывать до 127 пар диапазон / критерий.

Как можно использовать сводную таблицу.

Вот обычная задача, которую все пользователи Excel должны время от времени выполнять. У вас есть список данных (к примеру, названий товаров), и нужно узнать количество уникальных позиций в этом списке. Как это сделать? Проще, чем вы думаете

В версиях Excel выше 2013 есть специальная функция, которая позволяет автоматически пересчитывать различные значения в сводной таблице. На следующем рисунке показано, как выглядит этот счетчик:

Чтобы создать сводную таблицу со счетчиком для определенного столбца, выполните следующие действия.

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

Когда откроется сводная таблица, расположите области строк, столбцов и значений так, как вам нужно. Если у вас нет большого опыта работы со сводными таблицами Excel, могут оказаться полезными следующие подробные рекомендации: Создание сводной таблицы в Excel.
Переместите поле, количество уникальных элементов которого вы хотите вычислить ( поле « Товар» в этом примере), в область « Значения» , щелкните его и выберите «Параметры значения поля…» из раскрывающегося меню.
Откроется диалоговое окно , прокрутите вниз до операции «Число разных элементов» , которая является самым последним пунктом в списке, выберите ее и нажмите OK .

Вы также можете дать собственное имя своему счетчику, если хотите.

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

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

Благодарю вас за чтение и надеюсь увидеть вас снова. Пожалуйста, не переключайтесь!

Как найти и выделить уникальные значения в столбце — В статье описаны наиболее эффективные способы поиска, фильтрации и выделения уникальных значений в Excel. Ранее мы рассмотрели различные способы подсчета уникальных значений в Excel. Но иногда вам может понадобиться только просмотреть уникальные…

Как получить список уникальных значений — В статье описано, как получить список уникальных значений в столбце с помощью формулы и как настроить эту формулу для различных наборов данных. Вы также узнаете, как быстро получить отдельный список с…

Как выделить цветом повторяющиеся значения в Excel? — В этом руководстве вы узнаете, как отображать дубликаты в Excel. Мы рассмотрим различные методы затенения дублирующих ячеек, целых строк или последовательных повторений с использованием условного форматирования. Ранее мы исследовали различные…

Как посчитать количество повторяющихся значений в Excel? — Зачем считать дубликаты? Мы можем получить ответ на множество интересных вопросов. К примеру, сколько клиентов сделало покупки, сколько менеджеров занималось продажей, сколько раз работали с определённым поставщиком и т.д. Если…

Как убрать повторяющиеся значения в Excel? — В этом руководстве объясняется, как удалять повторяющиеся значения в Excel. Вы изучите несколько различных методов поиска и удаления дубликатов, избавитесь от дублирующих строк, обнаружите точные повторы и частичные совпадения. Хотя…

Источник

Подсчет количества уникальных значений

Постановка задачи

Есть диапазон с данными, в котором некоторые значения повторяются больше одного раза:

Задача — подсчитать количество уникальных (неповторяющихся) значений в диапазоне. В приведенном выше примере, как легко заметить, на самом деле упоминаются всего четыре варианта.

Рассмотрим несколько способов ее решения.

Способ 1. Если нет пустых ячеек

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

Не забудьте ввести ее как формулу массива, т.е. нажать после ввода формулы не Enter, а сочетание Ctrl+Shift+Enter.

Технически, эта формула пробегает по всем ячейкам массива и вычисляет для каждого элемента количество его вхождений в диапазон с помощью функции СЧЕТЕСЛИ (COUNTIF). Если представить это в виде дополнительного столбца, то выглядело бы оно так:

Потом вычисляются дроби 1/Число вхождений для каждого элемента и все они суммируются, что и даст нам количество уникальных элементов:

Способ 2. Если есть пустые ячейки

Если в диапазоне встречаются пустые ячейки, то придется немного усовершенствовать формулу, добавив проверку на пустые ячейки (иначе получим ошибку деления на 0 в дроби):

Вот и все дела.

Ссылки по теме

Как извлечь из диапазона уникальные элементы и удалить дубликаты
Как подсветить дубликаты в списке цветом
Как сравнить два диапазона на наличие в них дубликатов
Извлечение уникальных записей из таблицы по заданному столбцу с помощью надстройки PLEX

Источник

Содержание

Отбор уникальных значений с суммированием по соседнему столбцу в EXCEL
Задача
Решение
Тестируем
Подсчет Уникальных значений с условиями в EXCEL
Задача1
Задача2
Задача3
Подсчет уникальных значений в excel с условием
Подсчёт уникальных значений в Excel
Подсчет Уникальных и Неповторяющихся ТЕКСТовых значений в заданном интервале в MS EXCEL
Задача
Решение
Тестируем
Подсчет ТЕКСТовых значений, которые имеют повторы в MS EXCEL
Количество уникальных значений по нескольким условиям. (Макросы/Sub)
Подсчет количества уникальных значений при условии (Формулы/Formulas)
подсчет уникальных текстовых значений по двум условиям
Подсчет уникальных значений с дополнительным условием (Формулы/Formulas)

Отбор уникальных значений с суммированием по соседнему столбцу в EXCEL

history 22 апреля 2013 г.

Имеется таблица, состоящая из двух столбцов: из столбца с повторяющимися текстовыми значениями и столбца с числами. Создадим таблицу, состоящую только из строк, с уникальными текстовыми значениями. По числовому столбцу произведем суммирование соответствующих значений.

Пусть исходная таблица содержит 2 столбца: текстовый – Список регионов и числовой — Объем продаж . Столбец Список регионов содержит повторяющиеся значения (см. файл примера ). Уникальные значения выделены цветом с помощью Условного форматирования .

Задача

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

Решение

Создадим Динамические диапазоны : Регионы (названия регионов из столбца А ) и Продажи (объемы продаж из столбца B ).

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

Для создания списка уникальных значений введем в ячейку D2 формулу массива : =ЕСЛИОШИБКА(ИНДЕКС(Регионы; ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$1:D1;Регионы);0));»»)

Не забудьте при вводе вышеуказанной формулы нажать CTRL+SHIFT+ENTER . Затем необходимо скопировать формулу вниз (размерность списка уникальных значений должна совпадать с размерностью исходного списка).

Для подсчета суммарных продаж в столбце E используем мощную функцию СУММПРОИЗВ() : =СУММПРОИЗВ((Регионы=D2)*Продажи)

Отображение нулей в строках, в которых нет регионов, уберем пользовательским форматом # ##0;-# ##0; (см. статью Скрытие значений равных 0 ).

Тестируем

1. Введите в ячейку А11 новый регион — Китай 2. Введите объем продаж — 100 3. Введите в А12 — Китай 4. Введите объем продаж — 500 5. В соседней таблице справа в ячейке D7 будет выведено название региона Китай с суммарным объемом продаж 600

СОВЕТ: Другим подходом к решению этой задачи является использование Сводных таблиц (см. файл примера ).

Источник

Подсчет Уникальных значений с условиями в EXCEL

history 18 июля 2016 г.

Подсчитаем Уникальные значения с одним и двумя условиями.

Про подсчет уникальных текстовых и числовых значений (без условий) можно прочитать в статье Подсчет Уникальных ТЕКСТовых значений в MS EXCEL и Подсчет Уникальных ЧИСЛОвых значений в MS EXCEL . В этой статье рассмотрим более сложные варианты с условиями.

Задача1

Пусть имеется таблица с перечнем продаж по продавцам.

Требуется подсчитать сколько различных товаров продал конкретный продавец. Например, Вася продал 1 товар1, 2 Товара2 и 1 Товар4 (выделено зеленым). Всего 3 разных товара.

Это можно подсчитать формулой =СУММПРОИЗВ((A13:A21=A7)/СЧЁТЕСЛИМН(B13:B21;B13:B21;A13:A21;A13:A21)) , которая будет работать только с версии MS EXCEL 2007 из-за функции СЧЁТЕСЛИМН() .

Изменив в ячейке А7 имя продавца (в файле примера для удобства сделан выпадающий список ), формула пересчитает количество уникальных.

Задача2

Аналогичным образом можно решить задачу с двумя условиями.

В этом случае будут подсчитаны уникальные товары только в строках, для которых Продавец и Месяц совпадают с критериями, установленными в желтых ячейках.

В файле примера на листе Два критерия приведено решение этой задачи с помощью Сводной таблицы . В этом случае выводится не количество уникальных, а список уникальных товаров.

Задача3

Теперь рассмотрим другую таблицу (столбцы А:С на рисунке ниже).

Необходимо вывести количество заключенных договоров в каждом месяце.

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

Создадим дополнительный столбец для определения месяца заключения договора (см. статью Название месяца прописью в MS EXCEL ). Выведем из этого столбца только уникальные месяцы (см. статью Отбор уникальных значений (убираем повторы из списка) в MS EXCEL ) и поместим их в столбец F.

И, наконец, помощью формулы =СУММПРОИЗВ(($D$9:$D$26=F9)/СЧЁТЕСЛИ($A$9:$A$26;$A$9:$A$26)) подсчитаем количество уникальных договоров в соответствующем месяце.

Решение также возможно с помощью Сводной таблицы.

Источник

Подсчет уникальных значений в excel с условием

Подсчёт уникальных значений в Excel

Смотрите такжеУв.Boroda, в ВашемЯ как всегда не так. Файл

 сделал

 Как можно посчитать

выше) и синим
H7

в ячейку всем исходном списке, Проданный товар. Столбец с помощью функцииЭтот пример показывает, как варианте почему-то получается всё решаю методом

прилагаю.
такую формулу

max.opr Уникальных заказов в таблицы

 данным и «причешите». количество уникальных значений в исходном списке, цветом (см. рисунок:J7 а в нужном с датами долженCOUNTIF создать формулу массива,

 на 1 ед.

научного тыкаMeissen=—(СЧЁТЕСЛИМН($A$1:A2;A2;$B$1:B2;B2;$C$1:C2;C2)=1): не совсем то, конкретном городе.Bregra Не получится обращайтесь
по нескольким условиям выделенные ниже).=СУММПРОИЗВ(—(СЧЁТЕСЛИ(Текущий_Диапазон_Товаров;Текущий_Диапазон_Товаров)=1)). диапазоне (границы которого быть отсортирован по(СЧЁТЕСЛИ), хранится в которая подсчитает уникальные
больше?Попробуйте, может так
: Лорик, А своднаяМихаил С. что надо_Boroda_: Спасибо. Отличное решение. на форум.

и записать результатжирным
СОВЕТ:
Напомним, что неповторяющиеся значения

Найдем нижнюю границу диапазона

Подсчет Уникальных и Неповторяющихся ТЕКСТовых значений в заданном интервале в MS EXCEL

легко настраиваются). возрастанию (см. файл памяти Excel, а значения.VEKTORVSFREEMAN подойдет!?

не катит уже?: Пример к постуВы подсчитали количество: Сумм там у Я почему тоBregra в массив. Во).

Задача

Подобный анализ данных соответствуют, в нашем (номер позиции вГраницы интервала будут задаваться

примера). не в ячейкахВоспользуемся функцией: сделал и посмотрел200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЦЕЛОЕ(СУММ((1/СЧЁТЕСЛИ(B3:B11;B3:B11&»»))*((E3:E11<>«НР»)*1)))Лорик выше строк, а надо Вас в файле все время думал,: Спасибо. Но задача вложении пример. ВЕсли исходный список значений можно также выполнить

случае, количеству Фруктов, исходном списке) с пользователем в ячейкахПодсчитаем уникальные и неповторяющиеся листа.COUNTIFплюс к томуформула массивная вводится: Спасибо, но нет,Михаил С. подсчитать количество уникальных нет, но неважно. что нужно через состоит записать данные этом файле есть находится в диапазоне с помощью стандартного проданных в указанный

Решение

помощью формулыЕ6Е7 значения в столбцеМассив констант выглядит следующим(СЧЁТЕСЛИ). Для примера еще заметил ошибку. одновременным нажатием КОНТРЛ+ШИФТ+ЕНТЕР не подойдет,

: Не тот файл текстовых значений.Кол-во уникальных по словарь делать, но в массив, поэтому результат, который долженА7:А16

инструмента EXCEL - интервал, по 1=СУММПРОИЗВ(МАКС((J7=Весь_диапазон_Дат)*(СТРОКА(Весь_диапазон_Дат))))-СТРОКА($A$6). Строки таблицы, которые

Проданный товар в образом: <3;1;1;1;3;3>, что подсчитаем количество чисел В общем прошу (см.файл)примерчик из большого выложил :(К примеру менеджер городу можно посчитать
тогда нужно было не нужно выводить получиться в массиве., то количество повторяющихся Сводных таблиц. партии.Результат помещаем в попадают в заданный
определенном временном интервале. обозначает: три числа 5 в диапазоне засчитать мой ответ
Нашлась ошибка, да
файла, нужна именноmax.opr PAVLOVA в 201102 так
бы создавать ключ данные на лист Спасибо. значений можно вычислитьПроизведем подсчет ТЕКСТовых значений,
1. В ячейку

ячейку интервал выделены сРешение данной задачи позволит 7, одно значениеA1:A6 неверным и не одна.
формула.

: Не тот файл сделала три продажи=СЧЁТ(1/(ПОИСКПОЗ(A$2:A$99&B2;A$2:A$99&B$2:B$99;)=СТРОКА(A$1:A$98))) по нескольким полям, (не нужно использовать

gling с помощью формулы которые имеют повторы.Е6K7 помощью Условного форматирования подсчитать сколько наименований «sun», одно значение
, используя следующую формулу:

Лорик Мудрить не стану,Meissen выложил 🙁 одному Клиенту1. Т.е.Формула массива. Вводится

Тестируем

а потом разделять копирование формулы). Есть: Здравствуйте. Это раздел (см. файл примера):В отличие от статьи

введите начальную дату. серым цветом. товара было продано «moon», одно число

=COUNTIF(A1:A6,5): Посмотрите, скопировала формулы т.к. ниже предложены: Лорик, зачем такЕще раз большое результат должен быть

одновременным нажатием Ctrl+Shift+Enter ключ на условия. ли у кого для VBA, но=СУММПРОИЗВ((A7:A16<>«»)/СЧЁТЕСЛИ(A7:A16;A7:A16&»»))- Подсчет повторяющихся значений

(верхняя граница временногоПо определенным позициям границСначала для удобства определим в определенный промежуток 5, три числа=СЧЁТЕСЛИ(A1:A6;5) в файл. рабочие варианты все усложнять. по

 спасибо!
=1Ну а сумму А ваше решение то хотя бы в файле не

Подсчет ТЕКСТовых значений, которые имеют повторы в MS EXCEL

СУММПРОИЗВ(—(СЧЁТЕСЛИ(A7:A16;A7:A16)=1)) (дубликатов), где подсчитывались

интервала) — 02.01.2008 диапазона (ячейки Именованный диапазон Весь_диапазон_Дат времени (т.е. уникальных 7, три числаЧтобы подсчитать уникальные значения,Pelena_Boroda_ моему в данномmax.oprМихаил С. на количество сами как раз то, задумка как быстро увидел ни макросовТ.е. для подсчета текстовых

все дубликаты, подсчитаем2. В ячейкуK6 K7 как ссылку на наименований), а также 7. добавим функцию: Можно так попробовать: Так нужно? случае проще сводной: Огромное спасибо!: Тогда так, с потом поделите что нужно, нужно это можно реализовать,

ни массивов (в значений, которые имеют только первые дубликатыЕ7), формируем Динамический диапазон диапазон =$A$7:$A$41 (т.е. выделить эти наименования.

Все это сводится к
SUM

200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММ(ЕСЛИОШИБКА(1/СЧЁТЕСЛИМН(область;область;статус;ЕСЛИ(статус<>«НР»;»<>НР»));))200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММ(ЕСЛИОШИБКА(1/СЧЁТЕСЛИМН(область;область;статус;ЕСЛИ(статус=»НР»;»НР»));0)) обойтись, результат токак раз то, доп столбцомNuro4kaZ перечень уникальных условий если в примере

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

для товаров (Текущий_диапазон_Товаров): полностью на весь А подсчет неповторяющихся <1>.(СУММ), 1/, и

Количество уникальных значений по нескольким условиям. (Макросы/Sub)

Вроде и безВиктор, а научный
один, тем более что нужно. неделюМихаил С.: Почему то результат и их количество будет много данных. то записать), попробуйте количества уникальных значений все повторяющиеся за (нижняя граница временного=СМЕЩ(лист1!$B$7;лист1!$K$6-1;;лист1!$K$7-лист1!$K$6+1)) диапазон дат исходной значений даст информациюЭтот массив констант используется

 заменим 5 на ЕСЛИОШИБКА() должно работать тык в данном если файл большой, не мог вывести: Или так, для нулевой выводит(((Nuro4kaZ Например 100 тысяч формулу

 (статья Подсчет уникальных исключением их повторов, интервала) — 10.01.2008Теперь найдем количество уникальных таблицы). о том, сколько в качестве аргумента

A1:A6 (?) случае не прокатит ну это мое формулу сам :) наглядностиNuro4kaZ: Добрый день!

 строк и 10=СУММПРОИЗВ(—($A$2:$A$13&$B$2:$B$13&$C$2:$C$13=F3&G3&H3)) текстовых значений) вычесть или другой вариант:3. Новый временной интервал (различающихся) значений в

Затем определим диапазон, в и каких товаров для функции.Лорик — сделай все личное мнение, навязыватьсводными таблицами удобнейmax.opr:Подскажите как прописать условий, по которымBregra количество неповторяющихся (статья все уникальные за будет выделен серым определенном нами диапазоне. котором будем подсчитывать было продано толькоSUM=SUM(1/COUNTIF(A1:A6,A1:A6)): Pelena, вроде бы статусы «НР» и

 не буду )) управлять, но в: Спасибо большое!О да! Получилось! формулу, буксую нужно посчитать количество: Спасибо. Но нужно Подсчет неповторяющихся значений). исключением неповторяющихся). Чтобы цветом. Подсчет наименований Результат поместим в уникальные и неповторяющиеся

 в количестве одной(СУММ), давая результат=СУММ(1/СЧЁТЕСЛИ(A1:A6;A1:A6)) работает, даже с посмотриЛорик моем случае диапазонкстати, а что Спасибо большое! А. Суть такая. уникальных значений. Если именно макрос иФормула подсчитывает текстовые и не запутаться в товара будет производиться ячейку

Подсчет количества уникальных значений при условии (Формулы/Formulas)

 значения. Для этого: партии.
4.Закончим ввод формулы нажатием учетом того, если
А, не, я почему-то: Простите, но мне данных обновляется из обозначают два минуса то я такуюИсполнителю за выполнение использовать например словарь, записать в массив, числовые значения. Диапазон этом зоопарке терминов, только в этомH6Найдем верхнюю границу диапазонаВ статьях Подсчет уникальныхУрок подготовлен для ВасCtrl+Shift+Enter в столбце с

 считал наоборот - нужна только цифра внешнего источника данных, перед функцией?
формулу 3х-этажную нагородила. работы по городу будет ли быстро
чтобы потом использовать может содержать пустые можно обратиться к
интервале.: (номер позиции в

 текстовых значений и командой сайта office-guru.ru.

 названиями есть пустые все те, где
(количество), лишние сводные где количество полейМихаил С. )) выплачивается сумма, за

подсчет уникальных текстовых значений по двум условиям

 это работать? Спасибо эти данные в
ячейки. статье Классификация значений4. После ввода границ=СУММПРОИЗВ((Текущий_Диапазон_Товаров<>«»)/ СЧЁТЕСЛИ(Текущий_Диапазон_Товаров;Текущий_Диапазон_Товаров&»»)) исходном списке), воспользовавшись
Подсчет Неповторяющихся значений
Источник: http://www.excel-easy.com/examples/count-unique-values.html

Примечание: ячейки. Предложенная Вами есть НР.

 таблицы может вырасти и: Уберите их -
max.opr все заказы по за любой совет дальнейшем макросе.
Первые дубликаты выделены Условным по уникальности. диапазона, в ячейкахНапомним, что Уникальные значения решением из статьи было показано, что

Перевела: Ольга ГелихСтрока формул указывает, выше формула при

Сейчас переделаюЦитата тогда «слетит» доп

 увидите.: добрый день!
этому городу. Т.е.buchlotnikBregra

 форматированием (см. статьюЕсли исходный список содержит:H6 Н7

соответствуют, в нашем Ближайшее событие =ПОИСКПОЗ(МИН(ЕСЛИ(Весь_диапазон_Дат>=E6;Весь_диапазон_Дат;»»));Весь_диапазон_Дат;0) такое уникальные и

Автор: Антон Андронов что это формула
пустых ячейках выдаетВоMeissen, 24.04.2015 в столбецНаберите в поискепомогите, пожалуйста, составить
в одном столбце

:: И по вашему Выделение первых вхождений

 <будет подсчитано число случае, количеству проданных

 Эту формулу массива неповторяющиеся значения иПроизведем подсчет уникальных и

массива, заключая её ошибку.

Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММ(ЕСЛИОШИБКА(1/СЧЁТЕСЛИМН(область;область;статус;ЕСЛИ(статус<>«НР»;статус));)) 08:28, в сообщении
Лорик по сайту «бинарное формулу для подсчета «Город» нужно проверить
Bregra примеру нельзя найти дубликатов в MS»a» неповторяющихся и уникальных наименований Фруктов в помещаем в ячейку как их подсчитать неповторяющихся текстовых значений

Подсчет уникальных значений с дополнительным условием (Формулы/Formulas)

 в фигурные скобкиPelena
Pelena № 4200?’200px’:»+(this.scrollHeight+5)+’px’);»>не катит: Добрый день! отрицание» количества уникальных текстовых на совпадение значения,, так всё же уникальные значения. У

 EXCEL)., «a», «a», наименований товара.

 указанный временной интервал.K6 во всем исходном не во всем <>. Их не: А-а, так вот

: У меня такЕсть другие условияПо данной темеМихаил С. значений по двум а затем подсчитать — вам на вас результат можноBregra»b»5. В зависимости от

Число неповторяющихся значений в. списке. Отличие этой списке, а только нужно вводить самостоятельно. зачем ЕСЛИОШИБКА(), тогда получилось в файле, которые нашла формулу для
: PS. условиям количество уникальных значений выходе лишь количество

 получит если только: Добрый день,
, «b», «c»>. Тогда значения Переключателя, уникальные определенном нами диапазоне
Найдем наибольшую дату, которая статьи в том,
в определенном пользователем
Они исчезнут, когда так200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММ(ЕСЛИ(статус<>«НР»;1/СЧЁТЕСЛИМН(область;область;статус;»<>НР»);0))
не позволяют работать моего условия, ноЕсли уникальный клиентспасибо!! в другом столбце

 уникальных или их уникальные значения уже
Помогите решить вот
количество значений (величин), и неповторяющиеся значения можно найти с меньше или равна что пользователь может диапазоне.
вы начнете редактировать200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММ(ЕСЛИОШИБКА(ЕСЛИ(статус<>«НР»;1/СЧЁТЕСЛИМН(область;область;статус;»<>НР»);0);0))Лорик со сводной.
что-то она не
«уникален» только в
файл прилагаю

 «Заказ» (т.к. заказы перечень в памяти есть.
такую проблему. Есть

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

Лорик: Благодарна всем за
VEKTORVSFREEMAN работает. Может быть одном месяце, аМихаил С. могут дублироваться). И

 держать? Потому какgling несколько столбцов в

 будет равно 2, будут выделены соответственно
Результат поместим в
E7 теперь можно подсчитать из двух столбцов:

Пояснение:: Спасибо, получается помощь.: Лорик, здравствуйте кто-нибудь подскажет что не во всем: Я бы так затем сумму поделить пример ваш решает: Записал макрос макрорекордером,

 каждом столбце условие. т.е. «a» и зеленым (см. рисунок ячейку
). =ВПР(E7;Весь_диапазон_Дат;1;ИСТИНА) Результат помещаем

 значения не во Даты продаж и

Источник

Подсчитаем Уникальные значения с одним и двумя условиями.

Про подсчет уникальных текстовых и числовых значений (без условий) можно прочитать в статье

Подсчет Уникальных ТЕКСТовых значений в MS EXCEL

Подсчет Уникальных ЧИСЛОвых значений в MS EXCEL

. В этой статье рассмотрим более сложные варианты с условиями.

Задача1

Пусть имеется таблица с перечнем продаж по продавцам.

Это можно подсчитать формулой

=СУММПРОИЗВ((A13:A21=A7)/СЧЁТЕСЛИМН(B13:B21;B13:B21;A13:A21;A13:A21))

, которая будет работать только с версии MS EXCEL 2007 из-за функции

СЧЁТЕСЛИМН()

.

Изменив в ячейке

А7

имя продавца (в

файле примера

для удобства сделан

выпадающий список

), формула пересчитает количество уникальных.

Задача2

Аналогичным образом можно решить задачу с двумя условиями.

=СУММПРОИЗВ(((A14:A23=A8)*(B14:B23=B8))/СЧЁТЕСЛИМН(A14:A23;A14:A23;B14:B23;B14:B23;C14:C23;C14:C23))

В

файле примера на листе Два критерия

приведено решение этой задачи с помощью

Сводной таблицы

. В этом случае выводится не количество уникальных, а список уникальных товаров.

Задача3

Теперь рассмотрим другую таблицу (столбцы А:С на рисунке ниже).

Необходимо вывести количество заключенных договоров в каждом месяце.

Создадим дополнительный столбец для определения месяца заключения договора (см. статью

Название месяца прописью в MS EXCEL

). Выведем из этого столбца только уникальные месяцы (см. статью

Отбор уникальных значений (убираем повторы из списка) в MS EXCEL

) и поместим их в столбец F.

И, наконец, помощью формулы

=СУММПРОИЗВ(($D$9:$D$26=F9)/СЧЁТЕСЛИ($A$9:$A$26;$A$9:$A$26))

подсчитаем количество уникальных договоров в соответствующем месяце.

Решение также возможно с помощью

Сводной таблицы.

Источник

Сумма уникальных значений в диапазоне

Ktulu

Дата: Среда, 17.05.2017, 05:14 |
Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 42

Репутация:
0
±

Замечаний:
0% ±

Excel 2003

Добрый день, уважаемые коллеги!
Прошерстил поиск, но точного ответа так и не нашёл.
Нужно просуммировать уникальные значения в диапазоне
Для 2003 Экселя.
Заранее спасибо

К сообщению приложен файл:

7484480.xls
(14.0 Kb)

Ответить

buchlotnik

Дата: Среда, 17.05.2017, 07:14 |
Сообщение № 2

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация:
929
±

Замечаний:
20% ±

2010, 2013, 2016 RUS / ENG

можно так (массивка):

Код

=СУММ(ЕСЛИ((C4:C33)>0;C4:C33/СЧЁТЕСЛИ(C4:C33;C4:C33);0))

чуть короче можно

Код

=СУММ(ЕСЛИ(C4:C33;C4:C33/СЧЁТЕСЛИ(C4:C33;C4:C33);))

К сообщению приложен файл:

7611929.xls
(28.0 Kb)

Сообщение отредактировал buchlotnik — Среда, 17.05.2017, 10:14

Ответить

vikttur

Дата: Среда, 17.05.2017, 10:02 |
Сообщение № 3

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2941

Форула массива:

Код

=СУММ(ЕСЛИОШИБКА(C4:C33/СЧЁТЕСЛИ(C4:C33;C4:C33);))

Как обычная, должна быть шустрее:

Код

=СУММПРОИЗВ(—(ПОИСКПОЗ(C4:C33;—C4:C33;)=СТРОКА(C4:C33)-3);C4:C33)

Она же, формула массива:

Код

=СУММ(ЕСЛИ(ПОИСКПОЗ(C4:C33;—C4:C33;)=СТРОКА(C4:C33)-3;C4:C33))

Сообщение отредактировал vikttur — Среда, 17.05.2017, 10:08

Ответить

Ktulu

Дата: Четверг, 18.05.2017, 04:44 |
Сообщение № 4

Группа: Пользователи

Ранг: Новичок

Сообщений: 42

Репутация:
0
±

Замечаний:
0% ±

Excel 2003

Большое спасибо!

Ответить

Источник

На листе есть список чисел, которые имеют несколько повторяющихся значений, и теперь вы просто хотите суммировать или усреднять только уникальные значения. У вас есть какие-нибудь хорошие методы, чтобы завершить эту работу в Excel?

Суммируйте только уникальные значения в Excel с помощью формул

Усреднять только уникальные значения в Excel с формулами

Суммируйте / усредните только уникальные значения в Excel с помощью Kutools for Excel

Суммируйте только уникальные значения в Excel с помощью формул

Чтобы сложить только уникальные числа вместе, вы можете использовать следующие формулы:

1. Введите эту формулу: =SUMPRODUCT(1/COUNTIF(A2:A15,A2:A15&»»),A2:A15) в пустую ячейку, см. снимок экрана:

2, Затем нажмите Enter клавиша, и числа, которые появляются только один раз, были суммированы.

Ноты:

1. Вот еще одна формула, которая также может вам помочь: =SUM(IF(FREQUENCY(IF(A2:A15<>»»,MATCH(A2:A15,A2:A15,0)),ROW(A2:A15)-ROW(A2)+1),A2:A15)), не забудьте нажать Shift + Ctrl + Enter ключи.

2. В приведенной выше формуле A2: A15 — это диапазон данных, к которому вы хотите применить расчет, вы можете изменить аргументы по своему усмотрению.

3. Уникальные значения, включая первый дубликат.

Усреднять только уникальные значения в Excel с формулами

Если вы хотите вычислить среднее значение только уникальных значений, используйте следующие формулы:

1. Введите одну из следующих формул:

(1.) =AVERAGE(IF(FREQUENCY(A2:A15,A2:A15),A2:A15))

(2.) =SUMPRODUCT((A2:A15)/COUNTIF(A2:A15,A2:A15))/SUM(IF( FREQUENCY(A2:A15,A2:A15),1))

2, Затем нажмите Shift + Ctrl + Enter ключей, и вы получите результат расчета.

Функции: Уникальные значения, включая первый дубликат.

Суммируйте / усредните только уникальные значения в Excel с помощью Kutools for Excel

Иногда вы можете ошибиться с приведенными выше длинными формулами и просто захотите просмотреть рассчитанный результат. В этом случае, Kutools for ExcelАвтора Выберите повторяющиеся и уникальные ячейки Функция может помочь вам сначала выбрать все уникальные значения, а результат расчета также отобразится в строке состояния.

Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 30 дней. Получить сейчас.

После установки Kutools for Excel, выполните следующие действия:

1. Выберите диапазон данных, в котором вы хотите произвести некоторые вычисления.

2. Нажмите Кутулс > Выберите Инструменты > Выберите повторяющиеся и уникальные ячейки, см. снимок экрана:

3. Тогда в Выберите повторяющиеся и уникальные ячейки диалоговое окно, выберите Только уникальные значения or Все уникальные (включая 1-е дубликаты) вариант как вам нужно. Смотрите скриншот:

4. Затем нажмите OK для выбора всех уникальных чисел, при этом результат вычислений отображается в строке состояния. Смотрите скриншот:

Функции: Если вы хотите суммировать или усреднить уникальные значения, не включая первый дубликат, вы можете проверить Только уникальные ценности в Выберите повторяющиеся и уникальные ячейки диалоговое окно.

Чтобы узнать больше об этой функции выбора повторяющихся и уникальных ячеек.

Статьи по теме:

Как усреднить абсолютные значения в Excel?

Как усреднить ячейки, игнорируя значения ошибок в Excel?

Как усреднить только положительные или отрицательные числа в Excel?

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Источник

Подсчёт уникальных значений в Excel

Смотрите также2. Вычисление количества: ,Sanja,я конечно ниче IsObject(Conditions(J)) Then rngCond

Придумал вариант сКазанскийStart! = TimeraOld = [a1:a12000] макросы и UDF!!!: да, коллекции медленнее алгоритму еще добавить
уникальных значений...
For Each iCell
(иначе получим ошибку Ctrl + Shift Функция частота используетАвтор: Антон АндроновЭтот пример показывает, как уникальных числовых и не понял, ну
= Conditions(J).Value If
доп.столбцом, однако способ
: У меня вводOn Error Resumestr = «»

Казанский чем массив, но считываение диапазона вAlex_ST In Диапазон деления на 0 + Ввод. результирующее значение рассчитываетсяПримечание: создать формулу массива,

 текстовых значений (не

штучка прикольная. это IsNumeric(rngCond(I, 1)) Then будет неудобен при формулы в ячейку NextFor i =: Интересно! А функция
скорость выявления уникальности массив..: К стати, знатокиIf iCell.Value <> в дроби):Чтобы просмотреть процесс вычисления соответствующих текстовых значений.Мы стараемся как которая подсчитает уникальные
работает, если есть наверное аналог счётеслимн?
If Application.Evaluate(Replace(rngCond(I, 1), большом количества сетей, листа 14,5 с,MyArray = [a1:a12000] 1 To UBound(aOld) листа как таковая?

ключей очень высокаа в этой
VBA, помогите, плиз…
«» Then .Add

Вот и все дела.

office-guru.ru

Подсчет количества уникальных значений среди повторяющихся

 функции по шагам,Функция LEN используется для можно оперативнее обеспечивать значения. пустые ячейки)Sanja «,», «.») & тогда придется для «СЧЁТ_РАЗНЫХ_3» 28,9 с.For Each aIf InStr(1, str,Serge (подразумеваю, что ключи элегантной формуле переборЧто-то у меня iCell.Value, Trim(iCell.Value)karl311 выделите ячейку с поиска пустых ячеек. вас актуальными справочнымиВоспользуемся функцией=SUM(IF(FREQUENCY(MATCH(B2:B10;B2:B10;0);MATCH(B2:B10;B2:B10;0))>0;1))

: Можно и так Conditions(J + 1)) каждого создавать доп.столбцы Ровно в 2 In MyArray aOld(i, 1)) =: Ага, а потом там хранятся упорядоченно) происходит не один не получается сделатьNext: Здравствуйте. формулой, а затем Пустые ячейки иметь материалами на вашемCOUNTIF=СУММ (ЕСЛИ (ЧАСТОТА

сказать. Название говорит Then arrFlag(Int(J /Заранее благодарю!

раза. Интересно, почему?Col.Add a, CStr(a) 0 Then str
выясняется что расчётный — в этом
раз, а по на основе этойСЧЁТ_РАЗНЫХ = .CountНужна небольшая Ваша на вкладке длину 0 (количество языке. Эта страница(СЧЁТЕСЛИ). Для примера (ПОИСКПОЗ (B2:B10;B2:B10;0);ПОИСКПОЗ (B2:B10;B2:B10;0))>0;1)) само за себя 2)) = TrueDmitriy XMслэн
Next a = str & файл не работал и выигрыш. Еще количеству элементов - классной формулы UDF
End With помощь. Уже неФормулы символов). переведена автоматически, поэтому

Пример

подсчитаем количество чисел3. Вычисление количества СЧЕТ Else If rngCond(I,:: не помню с


’Если работа с	aOld(i, 1) &
у начальника на	быстрее конструкция dictionarys..
для каждого из	(ну, чтобы потом
End Function	первый день ломаю
	в группе
Скопируйте таблицу в ячейку	ее текст может
5 в диапазоне	уникальных значений (универсальная
УНИК	1) Like Conditions(J
=СЧЁТЕСЛИМН(D2:D16;»Магнит»;E2:E16;»>»&0)	кем мы оттачивали
коллекцией далее неприемлема,	«\|»
деловой презентации потому	и удобнее. Но
элементов массива происходит	формулы писАть былоAlex_ST голову над этойЗависимости формул A1 на пустом
содержать неточности и	A1:A6 формула)ЕСЛИМН. В отличии от + 1) ThenОлег55 скорость этого алгоритма
то перебросить её	Next i что макросы отключены требует подключения доп перебор всех «остальных проще и не: К стати, kim,

 задачкой. Нужно подсчитатьнажмите кнопку

листе в Excel грамматические ошибки. Для, используя следующую формулу:=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0;MATCH(A2:A10;A2:A10;0);»»);IF(LEN(A2:A10)>0;MATCH(A2:A10;A2:A10;0);»»))>0;1)) обычное СЧЕТЕСЛИМН подсчитывает arrFlag(Int(J / 2)): Дмитрий, благодарю, что — точно с в массив
aNew = Split(str, были. библиотеки. элементов», сравнение с указывать два раза не посоветуете, количество уникальных значенийВычисление формулы для работы с нас важно, чтобы=COUNTIF(A1:A6,5)

support.office.com

Подсчет количества уникальных значений

Постановка задачи

=СУММ (ЕСЛИ (ЧАСТОТА количество УНИКАЛЬНЫХ значений = True End не остались равнодушны

ZVI, а вотReDim NewMyArray(1 To «|») ‘ последнееИ стоя наAlex_ST условием и увеличение диапазон)а как в

в массиве и.

Способ 1. Если нет пустых ячеек

примерами формул, использующих эта статья была=СЧЁТЕСЛИ(A1:A6;5) (ЕСЛИ (ДЛСТР (A2:A10)>0;ПОИСКПОЗalex1210 If Next If

к проблеме! Однако еще учавствовал или Col.Count) значение пустое бирже труда макрописец: По просьбам слушателей

индекса, если условиеДаже без игнорирования формуле =СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16)) обойти частоту их появления,Есть диапазон с данными, функции. вам полезна. ПросимЧтобы подсчитать уникальные значения,  (A2:A10;A2:A10;0);»»);ЕСЛИ (ДЛСТР (A2:A10)>0;ПОИСКПОЗ: ААААААААААААААААААА, круто WorksheetFunction.And(arrFlag) = True Ваш вариант считает degassad или Anik..

For i =For i = упрямо повторяет себе: во время обеденного выполнено. пустых ячеек… ошибку #ДЕЛ/0! если

Способ 2. Если есть пустые ячейки

т.е. значение «7» в котором некоторыеДанные вас уделить пару добавим функцию (A2:A10;A2:A10;0);»»))>0;1))Akropochev Then .Add CStr(cl(I,

количество накладных сети

planetaexcel.ru

Подсчет количества уникальных значений в массиве

 но там было 1 To Col.Count

0 To UBound(aNew) «А у меня перерыва (раньше работая уж неПытаюсь сделать так: в диапазоне попадается столько то раз, значения повторяются большеДанные секунд и сообщить,SUMПоследнюю формулу нужно: Олег55, добрый день

1)), cl(I, 1)

 Магнит с бонусом чуточку точнее в’ NewMyArray(i) = — 1
считает-то всё-равно быстрее»… мешала) сделал сравнение

 говорю о последующемFunction СЧЁТ_РАЗНЫХ_2(Диапазон As

 хотя бы одна значение «12» столько
одного раза:
986 помогла ли она
(СУММ), 1/, и вводить как формулуМой вариант немассивной

If Err = больше нуля, а
конце
Col(i)Cells(i + 1,
Брр.
разных методов подсчёта делении и опять
Range) As Long
пустая ячейка? то раз иЗадача — подсчитать количество
Руслан
вам, с помощью заменим 5 на
массива, т. е. формулы 0 Then СЧЕТУНИКЕСЛИМН мне нужно вычислить
Alex_ST
Cells(i, 2) =
2) = aNew(i)слэн
числа уникальных значений суммировании..
’=СУММПРОИЗВ(1/СЧЁТЕСЛИ(Диапазон;Диапазон))kim
т.д.
уникальных (неповторяющихся) значенийДмитрий
кнопок внизу страницы.A1:A6 нажать не просто
=СУММПРОИЗВ(—(ПОИСКПОЗ(B2:B16*(D2:D16=L2)*(E2:E16>0);B2:B16*(D2:D16=L2)*(E2:E16>0);0)=(СТРОКА(B2:B16)-СТРОКА(B2)+1)*(D2:D16=L2)*(E2:E16>0)))
= СЧЕТУНИКЕСЛИМН +
количество точек, т.е.
: По совету слэн’a

 Col(i)Next: и еще можно
в диапазоне.Михаил С.With Application.WorksheetFunction: =СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16&»»))-1Заранее благодарен. в диапазоне. В

563 Для удобства также

. Enter, а Ctrl
Олег55 1 Else Err.Clear количество уникальных значений
попробовал ещё подсократитьNextCells(2, 5) = немножко причесать.. :)
Конечно, создавать «массив: ну может выСЧЁТ_РАЗНЫХ_2 = .SumProduct(1

Alex_STkim приведенном выше примере,
67 приводим ссылку на=SUM(1/COUNTIF(A1:A6,A1:A6)) + Shift +: , , большое End If End по столбцу ИД время выполнения СЧЁТ_РАЗНЫХ_2.Cells(4, 5) =

 Timer — Startнапример, зачем в из миллиона случайных и правы;

 / .CountIf(Диапазон, Диапазон)): Спасибо.: Для этого нужна как легко заметить,789 оригинал (на английском
=СУММ(1/СЧЁТЕСЛИ(A1:A6;A1:A6)) Enter. После этого спасибо, что не If Next End точки для сетиПопытался сначала заменить Timer — StartCells(2, 6) = коллекцию передавать значение, чисел в диапазоне=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16&»»))-ИЛИ(A4:P16=»»)

End WithА почему -1 функция СЧЁТЕСЛИ()- масса
на самом деле235 языке) .Закончим ввод формулы нажатием в строке формул остались в стороне With End Function Магнит и бонуса в цикле в
Cells(4, 6) = UBound(aNew) + 1
если нужно только
1…200 000″ ятоже массив
End Function
в конце формулы
примеров на форуме. упоминаются всего четыре
Руслан
Предположим, что вы хотите

Ctrl+Shift+Enter

 мы увидим, что от моей проблемы!
Jack Famous
больше нуля. В ключе коллекции CStr(tmpArr(i,

 Col.Count[CalcTime] = Timer
посчитать? не стал, т.к.Alex_STвыдаёт #ЗНАЧ! вдруг появилось?Для подсчета уникальных: варианта.Дмитрий
узнать, сколько уникальных. формула взята в Разобрал принципы работы: Sanja, какая крутая
данном примере таковых
j)) на tmpArr(i,
[CalcTime] = Timer

 — Startпередавайте только ключ времени жалко, но

: Не понял, зачем?Михаил С.
Уникальных значений раньше
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16))Рассмотрим несколько способов ее689 текстовых и числовых
Примечание:

 фигурные скобки ({}), ваших вариантов, принял UDF’ка

будет пять. j): — StartEnd Subслэн с меньшим размером

Быстрее работать что: массив было 48, аHugo решения.789 значений в диапазонеСтрока формул указывает, это признак того, к сведению, будупасиба)))По одной точке

If tmpArr(i, j)End SubSub btnCollections1_Click(): или пересчет был

 попробовал. ли будет если=СУММ(1/(ЕСЛИ(СЧЁТЕСЛИ(A4:P16;A4:P16);СЧЁТЕСЛИ(A4:P16;A4:P16);1)))-СЧЁТЕСЛИ(A4:P16;»»)
теперь вдруг получилось
: СЧЁТЕСЛИ

Если вы уверены, что вДмитрий
есть. Например, если что это формула что введенная формула теперь пользоваться наиболееalex1210 может быть несколько <> «» ThenSub btnDictionary_Click()Dim NewMyArray(), MyArray отключен :)Так вот, при

я сначала скопируюЕсли пустые не 47.Alex_ST исходном диапазоне данных143 столбец содержит текст массива, заключая её массива. удобным в зависимости: Sanja, Jack Famous, накладных, причем как .Add tmpArr(i, j),Dim NewMyArray, MyArray,Dim Col AsSerge обработке массива из

 диапазон во временный считаем за уникальные

Да и мой: kim, нет пустых ячеек,56
значения в фигурные скобкиПолосатый жираф алик от ситуации

 доброго времени, подскажите с бонусом, так tmpArr(i, j) D New Collection: Не согласен. Для 10 000 цифр массив, а потомAlex_ST макрос говорит, что=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16))- это здОрово! то можно использовать237

Руслан «,» Сергей «,» {}. Их не: Напиши в C1Fabol ячейки B2:B16 какое и без негозаработало. Время уменьшилосьStart! = Timer
Start! = Timer того что бы от 0 до буду брать по: Михаил, их всё-таки 48…Элементарно просто и короткую и элегантную
67 Сергей, Сергей нужно вводить самостоятельно. =A1&B1: Как посчитать количество условие выполняют, не (т.е. ноль)
почти вдвое :On Error ResumeOn Error Resume пересчёт отключить надо 100 на моём
одному элементу массиватак может быть
kim элегантно. формулу массива:235, имеется два уникальных Они исчезнут, когдаСкопируй её до
одинаковых значений в могу разобраться
Сергей стало 0,047 сек. Next
Next лесть в настройки,

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

Формула значения — Руслан вы начнете редактировать С5. Чтобы не

 таблице (уникальных)?Jack Famous: доп столбец один

НО возникли сомнения,Set D =MyArray = [a1:a12000] а это мало компе получилось следующее: его в коллекцию (честно говоря, даже в макросе :)
раньше, поэтому написАл как формулу массива,Описание (результат) и Сергей. Если формулу.
видеть формулы, можно

AlexM: alex1210, здравствуйте! для всех сетей

а С ЛЮБЫМИ CreateObject(«Scripting.Dictionary»)For Each a кто делает (я

1. Мой макрос

 (метод проверки уникальности не проверял), ноДело в том,

 UDF: т.е. нажать после=СУММ(ЕСЛИ(ЧАСТОТА(A2:A10,A2:A10)>0,1)) диапазон содержит числаПояснение: выбрать в формате: В таблице посчитатьНу, как яSanja ЛИ ТИПАМИ ДАННЫХMyArray = [a1:a12000] In MyArray вообще ещё не с прямым считыванием по Уокенбаху)? в вашем примере что в таком
Function СЧЁТ_РАЗНЫХ(Диапазон As ввода формулы неПодсчет количества уникальных числовых

5, 6, 7, 6Диапазон (массив констант), созданный белый ШРИФТ. можно, а в понял, это уникальный

: UDF (пользовательская функция)
это будет корректно
For Each a
Col.Add a, CStr(a)
встречал таких сотрудников

значений из диапазона

Я, к стати,
теряется вся простота виде, формула интерпретирует Range) As Long
Enter, а сочетание
значений в диапазоне
, уникальные значения —
с помощью функцииА в В6
картинке не получитсячисловой Function СЧЕТУНИКЕСЛИМН(rngU As работать? Ведь об In MyArrayNext a
кто знает как
и добавлением в искал где-нибудь данные и элегантность предложенной

пусто как еще’————————————————————————————— Ctrl+Shift+Enter.
A2:A10 без учета 5, 6 и
COUNTIF
напиши =СУММ (1/СЧЁТЕСЛИFabol
идентификатор конкретной точки Range, ParamArray Conditions())

ошибке работы макросаD.Add CStr(a), a
’Если работа с

это делается). А
коллекцию
о скорости выполнения kim формулы…
одно уникальное, вот
’ Procedure :Технически, эта формула пробегает
пустых ячеек и
7.(СЧЁТЕСЛИ), хранится в
(C1:C5;C1:C5))
: AlexM,
(как её адрес, As Long ‘rngU никак не узнаешь,Next a
коллекцией далее неприемлема, вот уровень безопасности
СЧЁТ_РАЗНЫХ_1 — 0,44
операций с коллекциями,Ну, разве можно
мы его и СЧЁТ_РАЗНЫХ
по всем ячейкам текстовых значений (4)
Чтобы подсчитать только уникальные
памяти Excel, а
Пробелы перед скобкамиAlexM
например, только в — диапазон поиска
т.к. включен обработчикNewMyArray = D.Items
то перебросить её

АЙтишники всегда ставят
сек.
но не нашел… сравнить по трудоёмкости
отнимаем. Для более
’ Author : массива и вычисляет
=СУММ(ЕСЛИ(ЧАСТОТА(ПОИСКПОЗ(B2:B10,B2:B10,0),ПОИСКПОЗ(B2:B10,B2:B10,0))>0,1))
значения, используйте следующие не в ячейках
удалить из формулы.
: Уникальные — это
виде числа) уникальных значений, обязательный ошибокFor i =
в массив высокий и тут
2. Мой доработанный Что-то мне подсказывает,
написания и возможному простого визуального восприятия
’ Topic_HEADER : для каждого элемента
Подсчет количества уникальных текстовых
функции: листа.
Вводить, как формулу такие значения, которые
Sanja ‘Conditions() — массив

Я с коллекциями

0 To UBound(NewMyArray)
’ReDim NewMyArray(1 To уже необходимы знания
макрос с предварительным
что она не при этом количеству
уменьшил проверяемый диапазон. Функция СЧЁТ_РАЗНЫХ (UDF)
количество его вхождений
и числовых значенийИспользуйте функции Если для
Массив констант выглядит следующим
массива. Как было
в таблице встречаются
: Так это у ПАР значений вида:
знаком мало, поэтомуCells(i + 1,
Col.Count)
что бы их копированием из диапазона
слишком велика (ну, ошибок формулы:
слэн’ Topic_URL :
в диапазон с

в диапазоне B2:B10,
присвоения значения 1
образом: {3;1;1;1;3;3}, что указано выше -
всего один раз. автора спросите
Диапазон_Условий1;Условие1;Диапазон_Условий2;Условие2…Диапазон_УсловийN;УсловиеN, обязательный ‘ и сомневаюсь…
2) = NewMyArray(i)

i = 1 (макросы) включить.

 в массив и по крайней мере=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A4:P16;A4:P16)): да, но если’ Post_Author : помощью функции который не должен

 каждому истинному условию. обозначает: три числа в конце ввода=СУММПРОИЗВ(Ч(СЧЁТЕСЛИ(A1:J10;A1:J10)=1))ЦитатаОлег55 написал: …количество должен иметь хотя-быПопытки вместо значенияNextFor Each aТак что это добавлением в коллекцию

 не выше, чеми формулу массива пустых ячеек все Alex_ST & The_Prist
СЧЕТЕСЛИ содержать пустые ячейкиИспользуется функция сумм для 7, одно значение нажать не Enter,
Fabol уникальных значений по одну пару значений. добавлять только ключ:
Cells(5, 5) = In Col не одно и
уже из этого обращение к ячейке={СУММ(1/(ЕСЛИ(СЧЁТЕСЛИ(A4:P16;A4:P16);СЧЁТЕСЛИ(A4:P16;A4:P16);1)))-СЧЁТЕСЛИ(A4:P16;»»)} же нет? :) & Лузер™(COUNTIF) (7) сложения уникальных значений. «sun», одно значение
а сочетание Ctrl+Shift+Enter.: AlexM, а количество столбцу ИД…это и

‘Все диапазоны должныIf tmpArr(i, j)
Timer — Start’ NewMyArray(i) = тоже… массива
диапазона). Поэтому, наверное,Казанский

planetaexcel.ru

Подсчет уникальных значений в столбце по двум условиям

Alex_ST’ Post_URL :. Если представить это
=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),»»),ЕСЛИ(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),»»))>0,1))Функция частота позволяет выполнить «moon», одно числоАлексей матевосов (alexm) одинаковых можно посчитать, есть диапазон B2:B16 состоять из одного <> «» Then
Cells(5, 6) = aHugoСЧЁТ_РАЗНЫХ_2 — 0,094 выигрыш в скорости: Вкладывать worksheetfunction’ы так
: Макрос считает правильно.

’ DateTime : в виде дополнительногоПодсчет количества уникальных текстовых

 подсчет количества уникальных 5, три числа: =СУММ (1/МУМНОЖ (—(A1:A5&B1:B5=ТРАНСП например (36.46-3; 36.41-5;Олег55 столбца и иметь .Add «», tmpArr(i, UBound(NewMyArray) + 1Cells(i, 2) =: У меня есть сек. при использовании массива нельзя. Проверялось не раз 06.04.10, 12:00 столбца, то выглядело и числовых значений
значений. Эта функция 7, три числа (A1:A5&B1:B5));{1:1:1:1:1})) 36.29-2…)?: От души большое равное кол-во строк

 j)[CalcTime] = Timer a

 такие цифры при3. Макрос, реализующий вместо прямого обращенияFunction СЧЁТ_РАЗНЫХ_2(Диапазон As (в том числе’ Purpose : бы оно так: в диапазоне A2:A10 игнорирует текстовые и 7.Формула массива, вводAlexM спасибо! оба варианта Dim cl() Dimк ускорению не — Starti = i + 1 копировании 3000 уникальных на VBA функцию к ячейкам будет Range) As Long даже и ручным возвращает число уникальныхПотом вычисляются дроби без учета пустых нулевые значения. ПервоеВсе это сводится к Ctrl+Shift+Enter: так? великолепные — и arrFlag() As Boolean привели.End SubNext из 11000 макросами: листа =СУММПРОИЗВ(1/СЧЁТЕСЛИ(Диапазон;Диапазон)) незначительным…’=СУММПРОИЗВ(1/СЧЁТЕСЛИ(Диапазон;Диапазон)) пересчётом). значений в указанном1/Число вхождений ячеек и текстовых вхождение конкретное значение {1/3;1/1;1/1;1/1;1/3;1/3}.В формуле массивПомогите написать формулу, которая Сергея, и Dim I&, J&Олег55Sub filter()Cells(3, 5) =Degassad 4.171125СЧЁТ_РАЗНЫХ_3 — 38,22

слэнСЧЁТ_РАЗНЫХ_2 = Evaluate(«SumProduct(1/CountIf(«А вот усовершенствованная диапазоне

для каждого элемента значений (6) она возвращает числоЭтот массив констант используется единиц длиной в высчитывала бы по

Очень помогли! Dim rngCond() On
: Доброе утро, уважаемыеStart! = Timer Timer — StartFor Each 1.625004 сек.: гораздо быстрее & Диапазон.Address &

 вами формула даже’ Notes : и все они
Примечания: равно количество вхождений в качестве аргумента количество строк, и

 двум столбцам кол-воJack Famous Error Resume Next знатоки Excel!Range(«A1:A12000»).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Worksheets(1).Range(«B1»),
Cells(3, 6) =

For i 2.422002Да… Гуру форума,
Казанский «,» & Диапазон.Address в исходном примере’————————————————————————————— суммируются, что и

 значения. Для каждого для функции если строк много, уникальных записей. Пример: Олег55, доброго дня!

 cl = rngU.ValueПожалуйста, помогите в Unique:=True Col.CountDictionary 1.530991 конечно, обычно правы,: Алексей, а протестируй & «))») топик-стартера (т.е. без

Dim iCell As даст нам количество

В формулах в этом случая того же
SUM например 99, то
на рисунке. Спасибо

Предлагаю вариант на With CreateObject(«Scripting.Dictionary») For ячейке вычислить количество[CalcTime] = Timer[CalcTime] = TimerAdvFilter 0.280985355 но чтобы разница разные методы иEnd Function пустых ячеек в Range

planetaexcel.ru

Подсчет уникальных значений (Формулы/Formulas)

 уникальных элементов: примере должны быть значения после первого(СУММ), давая результат

 единицы устанешь писать.Supreme council связях таблиц между I = 1

 точек сети Магнит, — Start

 — StartСами коды: во времени выполнения отпишись?слэн диапазоне) даёт 47,

Set Диапазон =Если в диапазоне встречаются введены как формулы эта функция возвращает 4.

 Выход массив единиц: 1. Вычисление количества

excelworld.ru

Excel: Как посчитать количество уникальных записей?

собой. Использованы только To UBound(cl) ReDim по которым хотяCells(6, 5) =End Sub

Sub btnDeggasad_Click() достигала более 400Для тестов создай
: а не надо
когда реально и Intersect(Диапазон.Parent.UsedRange, Диапазон)
пустые ячейки, то массива. Выделите все значение 0.Урок подготовлен для Вас делать формулой
уникальных числовых значений
штатные функции Excel. arrFlag(Int(UBound(Conditions) / 2))
бы в одной Timer — StartSub btnCollections2_Click()
Dim aOld, aNew,
раз…!!! Не ожидал. массив из миллиона этой «элегантности» :) моим макросом и
On Error Resume придется немного усовершенствовать ячейки, содержащие формулы,Функция ПОИСКПОЗ используется для командой сайта office-guru.ru=СУММ (1/МУМНОЖ (—(A1:A5&B1:B5=ТРАНСП=SUM(IF(FREQUENCY(A2:A10;A2:A10)>0;1)) Результат на листе For J = товарной накладной начисленEnd SubDim NewMyArray(), MyArray i As Long,Так что, формулисты,

 случайных чисел вв vba эффективнее вашей же исходной
Next формулу, добавив проверку нажмите клавишу F2 возврата позиции текстовогоИсточник: http://www.excel-easy.com/examples/count-unique-values.html
(A1:A5&B1:B5));СТРОКА (A1:ИНДЕКС (A:A;СЧЁТЗ=СУММ (ЕСЛИ (ЧАСТОТА «shop» 3ий столбец
LBound(Conditions) To UBound(Conditions) бонус (т.е. бонусФайл не даю,Dim Col As str As String бросайте своё тормозное диапазоне 1…200 000. будет простым перебором,

 формулой там 48With New Collection на пустые ячейки
и нажмите клавиши значения в диапазоне.
Перевела: Ольга Гелих (A:A)))^0)) (A2:A10;A2:A10)>0;1))alex1210 Step 2 If >0). сильно секретный :) New Collection
Start! = Timer занятие и пишитеGuest

только к вашему

Источник

На чтение 8 мин. Просмотров 45.6k.

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

Обладая базовым пониманием, вы можете выполнить свою работу за несколько секунд.

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

Одной из таких задач является подсчет разных значений в сводной таблице.

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

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

Содержание

Разные значения против уникальных значений
Подсчет разных значений в сводной таблице Excel
Добавление вспомогательного столбца в набор данных
Недостатки использования вспомогательного столбца
Добавить данные в модель данных и суммировать, используя «Число различных элементов»
Что если вы хотите посчитать уникальные значения (а не разные значения)?

Разные значения против уникальных значений

Кажется, что это одно и то же, но это не так.

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

Разница между уникальным и разными значениями

Уникальные значения / имена — это те, которые встречаются только один раз. Это означает, что все имена, которые повторяются и имеют дубликаты, не являются уникальными. Уникальные имена перечислены в столбце D вышеупомянутого набора данных.

Разными значениями / именами являются те, которые встречаются хотя бы один раз в наборе данных. Поэтому, если имя появляется три раза, оно все равно считается разным значением. Такой список можно получить путем удаления повторяющихся значений / имен и сохранения всех разных значений. Разные имена перечислены в столбце C приведенного выше набора данных.

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

Подсчет разных значений в сводной таблице Excel

Предположим, у вас есть данные о продажах:

Нажмите здесь, чтобы загрузить файл примера и делать все вместе со мной:

С этим набором данных вам нужно найти ответ на следующие вопросы:

Сколько сотрудников в каждом регионе (а это не что иное, как количество разных сотрудников в каждом регионе)?
Сколько сотрудников продали принтер в 2019 году?

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

Если вы используете Excel 2013 или более поздние версии, в сводной таблице есть встроенная функция, которая быстро подсчитывает количество.

А если вы используете Excel 2010 или ранние версии, вам придется изменить исходные данные, добавив вспомогательный столбец.

В этой статье рассматриваются следующие методы:

Добавление вспомогательного столбца в исходный набор данных для подсчета разных значений (работает во всех версиях).
Добавление данных в модель данных и использование параметра «Число различных элементов» (доступно в Excel 2013 и последующих версиях).

Существует третий метод, он называет метод сводной таблицы в сводной таблице.

Давайте начнем!

Добавление вспомогательного столбца в набор данных

Примечание. Если вы используете Excel 2013 и более поздние версии, пропустите этот метод и перейдите к следующему (вам доступна встроенная функция).

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

Хотя это простой обходной путь, у него есть некоторые недостатки (которые будут рассмотрены далее).

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

Предположим, у меня есть набор данных, как показано ниже:

Добавьте следующую формулу в столбец F и примените ее ко всем ячейкам, в которых есть данные в соседних столбцах.

= ЕСЛИ (СЧЁТЕСЛИМН ($C$2:C2; C2; $B$2:B2; B2) > 1;0;1)

Приведенная выше формула использует функцию СЧЁТЕСЛИМН для подсчета количества раз, когда имя появляется в данном регионе. Также обратите внимание на диапазоны критериев: $C$2:C2 и $B$2:B2. Это означает, что они продолжают расширяться, когда вы идете вниз по столбцу.

Например, в ячейке F2 диапазон критериев составляет $C$2:C2 и $B$2:B2, а в ячейке F3 эти диапазоны расширяются до $C$3:C3 и $B$3:B3.

Это гарантирует, что функция СЧЁТЕСЛИМН считает первый экземпляр имени как 1, второй экземпляр имени как 2 и так далее.

Поскольку мы хотим получить только разные имена, используется функция ЕСЛИ, которая возвращает 1, когда имя появляется для региона в первый раз, и возвращает 0, когда оно появляется снова. Это гарантирует, что учитываются только разные имена, а не повторы.

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

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

Ниже приведены шаги, как сделать это:

Выберите любую ячейку в таблице.
Нажмите вкладку «Вставка».

Нажмите на кнопку Сводная таблица.

В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно (и включает вспомогательный столбец), и выбран «На новый лист» в качестве места размещения.

Нажмите ОК.

Вышеуказанные шаги вставят новый лист со сводной таблицей.

Перетащите поле «Регион» в область «Строки» и поле «Помощник» в область «Значения».

Вы получите вот такую сводную таблицу:

Теперь вы можете изменить заголовок столбца с «Сумма по полю Помощник» на «Количество сотрудников».

Недостатки использования вспомогательного столбца

Хотя этот метод довольно прост, я должен выделить несколько недостатков, связанных с изменением исходных данных в сводной таблице:

Источник данных со вспомогательным столбцом не такой динамичный, как сводная таблица. Если изменится поставленная задача, вам придется вернуться к исходным данным и изменить формулу вспомогательного столбца (или добавить новый вспомогательный столбец).
Поскольку вы добавляете больше данных в источник сводной таблицы (который также добавляется в сводный кэш), это может привести к увеличению размера файла Excel.
Так как мы используем формулу Excel, это может замедлить работу вашей книги Excel, если в данных тысячи строк.

Добавить данные в модель данных и суммировать, используя «Число различных элементов»

В сводную таблицу добавлены новые функции в Excel 2013, которые позволяют получать количество различных значений.

В случае, если вы используете предыдущую версию, вы не сможете использовать этот метод (используйте метод, описанный выше).

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

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

Выберите любую ячейку в таблице.
Нажмите вкладку «Вставка».

Нажмите на кнопку Сводная таблица.

В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно и выбран новый рабочий лист.
Установите флажок «Добавить эти данные в модель данных».

Диалоговое окно Создание сводной таблицы

Нажмите ОК.

Приведенные выше шаги вставят новый лист с новой сводной таблицей.

Перетащите регион в область «Строки» и «Сотрудник» в область «Значения». Вы получите такую сводную таблицу:

В этой сводной таблице приводится общее количество сотрудников в каждом регионе (а не количество разных).

Чтобы получить подсчет разных значений в сводной таблице, выполните следующие действия:

Щелкните правой кнопкой мыши по любой ячейке в «Число элементов в столбце Сотрудник»
Нажмите на «Параметры полей значений».

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

Нажмите ОК.

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

Некоторые вещи, которые нужно знать, добавляя свои данные в модель данных:

Если вы сохраните свои данные в модели данных, а затем откроете в более старой версии Excel, появится предупреждение: «Некоторые функции сводной таблицы не будут сохранены».
Когда вы добавляете свои данные в модель данных и создаете сводную таблицу, в ней не отображаются параметры добавления вычисляемых полей и вычисляемых столбцов.

Что если вы хотите посчитать уникальные значения (а не разные значения)?

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

Помните — уникальные значения и разные значения не одно и то же. Нажмите здесь, чтобы узнать разницу.

Рассмотрим пример, когда нам нужно определить количество уникальных сотрудников для каждого региона. Это означает, что они работают только в одном конкретном регионе, а не в других.

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

Вот формула для этого случая:

= ЕСЛИ (ЕСЛИ (СЧЁТЕСЛИМН ($C$2:$C$1001; С2; $B$2:$B$1001; В2) / СЧЁТЕСЛИ ($C$2:$C$1001; С2) <1;0;1); ЕСЛИ (СЧЁТЕСЛИ ($С2:С$22; С2) > 1;0;1);0)

Приведенная выше формула проверяет, встречается ли имя сотрудника только в одном регионе или в нескольких регионах. Это делается путем подсчета количества появлений имени в регионе и деления его на общее количество появлений имени. Если значение меньше 1, это означает, что имя встречается в двух или более двух регионах.

Если имя встречается в нескольких регионах, формула возвращает 0, в противном случае возвращает единицу.

Формула также проверяет, повторяется ли имя в том же регионе или нет. Если имя повторяется, только первый экземпляр имени возвращает значение 1, а все остальные экземпляры возвращают 0.

Это может показаться немного сложным, но это опять-таки зависит от того, чего вы пытаетесь достичь.

Таким образом, если вы хотите подсчитать уникальные значения в сводной таблице, используйте вспомогательные столбцы, а если вы хотите подсчитать различные значения, вы можете использовать встроенную функцию (в Excel 2013 и более поздних версиях) или использовать вспомогательный столбец.

Источник

Содержание

Разные значения против уникальных значений
Подсчет разных значений в сводной таблице Excel
Добавление вспомогательного столбца в набор данных
Подсчет количества определенного значения ячейки в Excel при условии
Статистический анализ посещаемости с помощью функции СЧЁТЕСЛИ в Excel
Способ 1: счетчик на строке состояния
Способ 2: функция СЧЁТЗ
Способ 3: функция СЧЁТ
Как посчитать количество пустых и непустых ячеек?
Нулевые строки.
СЧЕТЕСЛИ с несколькими условиями.
Количество чисел в диапазоне
Количество ячеек с несколькими условиями ИЛИ.
Считаем числа и значения
Ставим экселю условия
Разбираемся в критериях
Задачи на логику
Средние значения и множественные формулы
Считаем данные в массиве с пустыми ячейками
Счёт текстовых строк по условию

Разные значения против уникальных значений

Кажется, что это одно и то же, но это не так.

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

Разница между уникальным и разными значениями

Предположим, у вас есть данные о продажах:

С этим набором данных вам нужно найти ответ на следующие вопросы:

Сколько сотрудников в каждом регионе (а это не что иное, как количество разных сотрудников в каждом регионе)?
Сколько сотрудников продали принтер в 2019 году?

Если вы используете Excel 2013 или более поздние версии, в сводной таблице есть встроенная функция, которая быстро подсчитывает количество.

А если вы используете Excel 2010 или ранние версии, вам придется изменить исходные данные, добавив вспомогательный столбец.

В этой статье рассматриваются следующие методы:

Добавление вспомогательного столбца в исходный набор данных для подсчета разных значений (работает во всех версиях).
Добавление данных в модель данных и использование параметра «Число различных элементов» (доступно в Excel 2013 и последующих версиях).

Существует третий метод, он называет метод сводной таблицы в сводной таблице.

Добавление вспомогательного столбца в набор данных

Примечание. Если вы используете Excel 2013 и более поздние версии, пропустите этот метод и перейдите к следующему (вам доступна встроенная функция).

Хотя это простой обходной путь, у него есть некоторые недостатки (которые будут рассмотрены далее).

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

Предположим, у меня есть набор данных, как показано ниже:

= ЕСЛИ (СЧЁТЕСЛИМН ($C$2:C2; C2; $B$2:B2; B2) > 1;0;1)

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

Ниже приведены шаги, как сделать это:

Выберите любую ячейку в таблице.
Нажмите вкладку «Вставка».

Нажмите на кнопку Сводная таблица.

В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно (и включает вспомогательный столбец), и выбран «На новый лист» в качестве места размещения.

Нажмите ОК.

Вышеуказанные шаги вставят новый лист со сводной таблицей.

Перетащите поле «Регион» в область «Строки» и поле «Помощник» в область «Значения».

Вы получите вот такую сводную таблицу:

Теперь вы можете изменить заголовок столбца с «Сумма по полю Помощник» на «Количество сотрудников».

Подсчет количества определенного значения ячейки в Excel при условии

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

Вид исходной таблицы:

Предварительно выделим ячейки E2:E5, введем приведенную ниже формулу:

=СЧЁТЕСЛИ(B3:B19;D2:D5)

Описание аргументов:

B3:B19 – диапазон ячеек с оценками за экзамен;
D2:D5 – диапазон ячеек, содержащих критерии для подсчета числа совпадений.

В результате получим таблицу:

Статистический анализ посещаемости с помощью функции СЧЁТЕСЛИ в Excel

Пример 3. В таблице Excel хранятся данные о просмотрах страниц сайта за день пользователями. Определить число пользователей сайта за день, а также сколько раз за день на сайт заходили пользователи с логинами default и user_1.

Вид исходной таблицы:

Поскольку каждый пользователь имеет свой уникальный идентификатор в базе данных (Id), выполним расчет числа пользователей сайта за день по следующей формуле массива и для ее вычислений нажмем комбинацию клавиш Ctrl+Shift+Enter:

Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив дробных чисел 1/количество_вхождений, например, для пользователя с ником sam это значение равно 0,25 (4 вхождения). Общая сумма таких значений, вычисляемая функцией СУММ, соответствует количеству уникальных вхождений, то есть, числу пользователей на сайте. Полученное значение:

Для определения количества просмотренных страниц пользователями default и user_1 запишем формулу:

В результате расчета получим:

Способ 1: счетчик на строке состояния

Самый простой способ подсчитать ячейки, содержащие данные – это воспользоваться информацией со счетчика, который размещен в правой части строки состояния слева от кнопок переключения режимов просмотра в Excel. Пока на листе выделен диапазон, в котором все элементы пустые или лишь один содержит какое-то значение, данный индикатор скрыт. Счетчик автоматически появляется тогда, когда выделено две или более не пустых ячеек, и сразу показывает их число после слова «Количество».

Но, хотя по умолчанию этот счетчик включен, и только ждет того, когда пользователь выделит определенные элементы, в некоторых случаях он может быть отключен вручную. Тогда актуальным становится вопрос о его включении. Для этого нужно кликнуть правой кнопкой мыши по строке состояния и в открывшемся списке установить галочку напротив пункта «Количество». После этого счетчик опять будет отображаться.

Способ 2: функция СЧЁТЗ

Подсчитать количество заполненных ячеек можно при помощи функции СЧЁТЗ. Она отличается от предыдущего способа тем, что позволяет зафиксировать подсчет определенного диапазона в отдельной ячейке. То есть, для просмотра информации по нему область не нужно будет постоянно выделять.

Выделяем область, в которую будет выводиться результат подсчета. Кликаем по значку «Вставить функцию».

Открывается окно Мастера функций. Ищем в представленном списке элемент «СЧЁТЗ». После того, как это наименование выделено, жмем на кнопку «OK».

Запускается окно аргументов. Аргументами этой функции являются ссылки на ячейки. Ссылку на диапазон можно прописать вручную, но лучше установить курсор в поле «Значение1», куда нужно ввести данные, и выделить соответствующую область на листе. Если нужно произвести подсчет заполненных ячеек в нескольких удаленных друг от друга диапазонах, то координаты второго, третьего и последующего диапазона нужно вводить в поля под названием «Значение2», «Значение3» и т.д. Когда все данные введены. Жмем на кнопку «OK».

Данную функцию также можно ввести вручную в ячейку или строку формул, придерживаясь следующего синтаксиса:
=СЧЁТЗ(значение1;значение2;…)

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

Способ 3: функция СЧЁТ

Кроме того, для подсчета заполненных ячеек в Экселе существует ещё функция счет. В отличие от предыдущей формулы, она считает только ячейки заполненные числовыми данными.

Как и в предыдущем случае, выделяем ячейку, куда будут выводиться данные и таким же способом запускаем Мастер функций. В нём выбираем оператора с наименованием «СЧЁТ». Жмем на кнопку «OK».

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

Для ручного введения формулы придерживаемся следующего синтаксиса:

=СЧЁТ(значение1;значение2;…)

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

Как посчитать количество пустых и непустых ячеек?

Посмотрим, как можно применить функцию СЧЕТЕСЛИ в Excel для подсчета количества пустых или непустых ячеек в указанном диапазоне.

Непустые.

В некоторых руководствах по работе с СЧЕТЕСЛИ вы можете встретить предложения для подсчета непустых ячеек, подобные этому:

СЧЕТЕСЛИ(диапазон;”*”)

Но дело в том, что приведенное выше выражение подсчитывает только клетки, содержащие любые текстовые значения. А это означает, что те из них, что включают даты и числа, будут обрабатываться как пустые (игнорироваться) и не войдут в общий итог!

Если вам нужно универсальное решение для подсчета всех непустых ячеек в указанном диапазоне, то введите:

СЧЕТЕСЛИ(диапазон;”<>” & “”)

Это корректно работает со всеми типами значений – текстом, датами и числами – как вы можете видеть на рисунке ниже.

Также непустые ячейки в диапазоне можно подсчитать:

=СЧЁТЗ(E2:E22).

Пустые.

Если вы хотите сосчитать пустые позиции в определенном диапазоне, вы должны придерживаться того же подхода – используйте в условиях символ подстановки для текстовых значений и параметр “” для подсчета всех пустых ячеек.

Считаем клетки, не содержащие текст:

СЧЕТЕСЛИ( диапазон; “<>” & “*”)

Поскольку звездочка (*) соответствует любой последовательности текстовых символов, в расчет принимаются клетки, не равные *, т.е. не содержащие текста в указанном диапазоне.

Для подсчета пустых клеток (все типы значений):

=СЧЁТЕСЛИ(E2:E22;””)

Конечно, для таких случаев есть и специальная функция

=СЧИТАТЬПУСТОТЫ(E2:E22)

Но не все знают о ее существовании. Но вы теперь в курсе …

Нулевые строки.

Также имейте в виду, что СЧЕТЕСЛИ и СЧИТАТЬПУСТОТЫ считают ячейки с пустыми строками, которые только на первый взгляд выглядят пустыми.

Что такое эти пустые строки? Они также часто возникают при импорте данных из других программ (например, 1С). Внешне в них ничего нет, но на самом деле это не так. Если попробовать найти такие “пустышки” (F5 -Выделить – Пустые ячейки) – они не определяются. Но фильтр данных при этом их видит как пустые и фильтрует как пустые.

Дело в том, что существует такое понятие, как «строка нулевой длины» (или «нулевая строка»). Нулевая строка возникает, когда программе нужно вставить какое-то значение, а вставить нечего.

Проблемы начинаются тогда, когда вы пытаетесь с ней произвести какие-то математические вычисления (вычитание, деление, умножение и т.д.). Получите сообщение об ошибке #ЗНАЧ!. При этом функции СУММ и СЧЕТ их игнорируют, как будто там находится текст. А внешне там его нет.

И самое интересное – если указать на нее мышкой и нажать Delete (или вкладка Главная – Редактирование – Очистить содержимое) – то она становится действительно пустой, и с ней начинают работать формулы и другие функции Excel без всяких ошибок.

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

=ЧСТРОК(E2:E22)*ЧИСЛСТОЛБ(E2:E22)-СЧЁТЕСЛИ(E2:E22;”<>”&””)

Откуда могут появиться нулевые строки в ячейках? Здесь может быть несколько вариантов:

Он есть там изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе (вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет – они просто заполняются строкой нулевой длины.
Была создана формула, результатом которой стал текст нулевой длины. Самый простой случай:

=ЕСЛИ(Е1=1;10;””)

В итоге, если в Е1 записано что угодно, отличное от 1, программа вернет строку нулевой длины. И если впоследствии формулу заменять значением (Специальная вставка – Значения), то получим нашу псевдо-пустую позицию.

Если вы проверяете какие-то условия при помощи функции ЕСЛИ и в дальнейшем планируете производить с результатами математические действия, то лучше вместо “” ставьте 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: Файл -Параметры -Дополнительно – Показывать нули в позициях, которые содержат нулевые значения.

СЧЕТЕСЛИ с несколькими условиями.

На самом деле функция Эксель СЧЕТЕСЛИ не предназначена для расчета количества ячеек по нескольким условиям. В большинстве случаев я рекомендую использовать его множественный аналог – функцию СЧЕТЕСЛИМН. Она как раз и предназначена для вычисления количества ячеек, которые соответствуют двум или более условиям (логика И). Однако, некоторые задачи могут быть решены путем объединения двух или более функций СЧЕТЕСЛИ в одно выражение.

Количество чисел в диапазоне

Одним из наиболее распространенных применений функции СЧЕТЕСЛИ с двумя критериями является определение количества чисел в определенном интервале, т.е. меньше X, но больше Y.

Например, вы можете использовать для вычисления ячеек в диапазоне B2: B9, где значение больше 5 и меньше или равно 15:

=СЧЁТЕСЛИ(B2:B11;”>5″)-СЧЁТЕСЛИ(B2:B11;”>15″)

Количество ячеек с несколькими условиями ИЛИ.

Когда вы хотите найти количество нескольких различных элементов в диапазоне, добавьте 2 или более функций СЧЕТЕСЛИ в выражение. Предположим, у вас есть список покупок, и вы хотите узнать, сколько в нем безалкогольных напитков.

Сделаем это:

=СЧЁТЕСЛИ(A4:A13;”Лимонад”)+СЧЁТЕСЛИ(A2:A11;”*сок”)

Обратите внимание, что мы включили подстановочный знак (*) во второй критерий. Он используется для вычисления количества всех видов сока в списке.

Как вы понимаете, сюда можно добавить и больше условий.

Считаем числа и значения

Три полезных вещи, помогающих в работе с программой.

Сколько чисел находится в массиве, можно рассчитать с помощью формулы СЧЁТ(значение1;значение2;…). Она учитывает только те элементы, которые включают в себя цифры.То есть если в некоторых из них будет прописан текст, они будут пропущены, в то время как даты и время берутся во внимание. В данной ситуации не обязательно задавать параметры по порядку: можно написать, к примеру, =СЧЁТ(А1:С3;В4:С7;…).
Другая статистическая функция — СЧЕТЗ — подсчитает вам непустые клетки в диапазоне, то есть те, которые содержат буквы, числа, даты, время и даже логические значения ЛОЖЬ и ИСТИНА.
Обратное действие выполняет формула, показывающая численность незаполненных секций — СЧИТАТЬПУСТОТЫ(массив). Она применяется только к непрерывным выделенным областям.

Ставим экселю условия

Когда нужно подсчитать элементы с определённым значением, то есть соответствующие какому-то формату, применяется функция СЧЁТЕСЛИ(массив;критерий). Чтобы вам было понятнее, следует разобраться в терминах.

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

Разбираемся в критериях

Примеры критериев:

«>0» — считаются ячейки с числами от нуля и выше;
«Товар» — подсчитываются секции, содержащие это слово;
15 — вы получаете сумму элементов с данной цифрой.

Для большей ясности приведу развернутый пример.

Чтобы посчитать ячейки в зоне от А1 до С2, величина которых больше прописанной в А5, в строке формул необходимо написать =СЧЕТЕСЛИ(А1:С2;«>»&А5).

Задачи на логику

Хотите задать экселю логические параметры? Воспользуйтесь групповыми символами * и ?. Первый будет обозначать любое количество произвольных символов, а второй — только один.

К примеру, вам нужно знать, сколько имеет электронная таблица клеток с буквой Т без учета регистра. Задаем комбинацию =СЧЕТЕСЛИ(А1:D6;«Т*»). Другой пример: хотите знать численность ячеек, содержащих только 3 символа (любых) в том же диапазоне. Тогда пишем =СЧЕТЕСЛИ(А1:D6;«???»).

Средние значения и множественные формулы

В качестве условия может быть задана даже формула. Желаете узнать, сколько у вас секций, содержимое которых превышают среднее в определенном диапазоне? Тогда вам следует записать в строке формул следующую комбинацию =СЧЕТЕСЛИ(А1:Е4;«>»&СРЗНАЧ(А1:Е4)).

Если вам нужно сосчитать количество заполненных ячеек по двум и более параметрам, воспользуйтесь функцией СЧЕТЕСЛИМН. К примеру, вы ищите секций с данными больше 10, но меньше 70. Вы пишете =СЧЕТЕСЛИМН(А1:Е4;«>10»;А1:Е4;«<70»).

Помимо этого, у вас есть возможность задать условия И/ИЛИ. Только во втором случае придется использовать сразу несколько правил. Смотрим: вам необходимо найти клетки, в которых слова начинаются с буквы В или Р — пишете =СЧЕТЕСЛИ(А1:Е4;«В*»)+ СЧЕТЕСЛИ(А1:Е4;«Р*»).

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

Считаем данные в массиве с пустыми ячейками

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

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(диапазондиапазон &»»))

Легко заметить, что результат получился на 1 больше, чем в прошлом примере. Это потому, что формула посчитала и пустую ячейку, как уникальные данные. Чтобы этого не было, еще доработаем функцию:

=СУММПРОИЗВ((диапазон<>””)/СЧЁТЕСЛИ(диапазондиапазон &»»))

Теперь получили правильный результат без учёта пустот.

Следующие две формулы выглядят сложнее, но работают быстрее. Используйте их, если программа подвисает при выполнении.

Счёт текстовых строк по условию

Похожее задание, но теперь нужно считать, сколько менеджеров продавали товар с кодом 1001.

А формула такая:

=СУММ(—(ЧАСТОТА(ЕСЛИ(диапазон<>»»;ЕСЛИ(условиедиапазон диапазондиапазон)-СТРОКА(диапазон.первая ячейка)+1)>0))

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

Источники

https://excelpedia.ru/bez-rubriki/podschet-raznih-znachenii-v-svodnoi-tablice-excel
https://exceltable.com/funkcii-excel/primery-funkcii-schetesli
https://lumpics.ru/how-count-number-of-filled-cells-in-excel/
https://mister-office.ru/funktsii-excel/function-countif.html
https://profi-user.ru/podschet-yacheek/
https://officelegko.com/2019/09/19/podschet-unikalnyh-znachenij-v-excel/

Источник