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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: https://www.planetaexcel.ru/techniques/14/187/

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

Числовые значения

В диапазоне

= СУММ(—(ЧАСТОТА(данные; данные)>0))

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

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

ID номера работников появляются в диапазоне B5: B14. Для того, чтобы получить количество уникальных номеров, вы можете использовать следующую формулу:

= СУММ(— (ЧАСТОТА(B5:B14; B5:B14) > 0))

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

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

{3; 0; 0; 2; 0; 3; 0; 0; 2; 0; 0}

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

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

Теперь каждая ИСТИНА в списке представляет собой уникальный номер в списке, и нам просто нужно сложить Истинные значения с СУММ.

Однако СУММ не будет добавлять логические значения в массиве, так что мы должны сначала превратить значения в 1 или ноль. Это делается с помощью двойного минуса (двойной унарный). В результате массив только 1 или 0.

{1; 0; 0; 1; 0; 1; 0; 0; 1; 0; 0}

И, наконец, СУММ добавляет эти значения и рассчитывает общее число, которое в данном случае равно 4.

Примечание: вы можете также использовать СУММПРОИЗВ для сложения элементов в массиве.

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

Другой способ подсчета уникальных числовых значений состоит в использовании СЧЁТЕСЛИ вместо ЧАСТОТА. Это гораздо проще, но нужно учитывать, что с помощью СЧЁТЕСЛИ на больших наборах данных для подсчета уникальных значений может вызвать проблемы с производительностью. Формула на основе частотных полос, хотя и более сложная, вычисляет гораздо быстрее.

С критериями

{=СУММ(—(ЧАСТОТА(ЕСЛИ(criteria;значения);значения)>0))}

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

Например, предположим, у вас есть список номеров сотрудников, которые записывали часы в двух разных зданиях: здание А и здание Б. Вы хотите знать, сколько уникальных сотрудников регистрирует время в каждом здании. Так как одно и то же число сотрудников более одного раза в списке, то вам нужна формула, которая будет рассчитывать уникальные идентификаторы сотрудника в здании.

В показанном примере, формула в G4 является:

{= СУММ(— (ЧАСТОТА(ЕСЛИ(B5:B14 = «A»; C5:C14); C5:C14)> 0))}

Примечание: это формула массива и должна быть введена с помощью сочетания клавиш Ctrl+Shift+Enter

Функция ЧАСТОТА рассчитывает массив значений, которые соответствуют массиву_интервалов. В этом случае мы подставлем «отфильтрованный» набор идентификаторов для массива данных, а также полный набор идентификаторов для массива_интервалов. Фильтрация осуществляется с помощью функции ЕСЛИ:

ЕСЛИ (B5:B14 = «А»; С5:С14)

Которая в примере рассчитывает это:

{81400; 81405; 81405; 82364; 82364; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}

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

{1; 2; 0; 2; 0; 0; 0; 0; 0; 0; 0}

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

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

Каждая ИСТИНА в списке представляет собой уникальный номер в списке, и нам просто нужно сложить Истинные значения с СУММ.

{1; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0}

И, наконец, СУММ добавляет эти значения и рассчитывает общее число, которое в данном случае составляет 3.

Текстовые значения

В диапазоне

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

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

Кроме того, можно использовать СЧЁТЕСЛИ, как описано ниже.

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

Имена работников находятся в диапазоне B5:B14. Для того, чтобы получить количество уникальных имен, вы можете использовать следующую формулу:

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

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

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

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

{1; 1; 1; 4; 4; 6; 6; 6; 9; 9}

Аргумент, хранимый в массиве строится из этой части формулы:

СТРОКА(B5:B14) -СТРОКА(B5) +1

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

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10}

Функция ЧАСТОТА возвращает массив значений, которые соответствуют «хранимым». В этом случае мы подставляем массив, рассчитываемый ПОИСКПОЗ для массива данных, а также рассчитываемый массив построчного массива выше, как массив хранимых.

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

{3; 0; 0; 2; 0; 3; 0; 0; 2; 0; 0}

Далее, каждое из этих значений преобразуется в значение ИСТИНА или ЛОЖЬ, которые > 0, а затем в 1 или ноль с двойным отрицанием (двойной минус). Это делается потому что СУММПРОИЗВ нужны числовые значения, она не может работать непосредственно с текстом или логическими значениями.

Внутри функции СУММПРОИЗВ конечный массив выглядит следующим образом:

{1; 0; 0; 1; 0; 1; 0; 0; 1; 0; 0}

И, наконец, СУММПРОИЗВ просто добавляет эти значения и рассчитывает общее число, которое в данном случае равно 4.

Обработка пустых ячеек в диапазоне

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

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

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

Рассмотрим формулу изнутри, ЕСЛИ требуется, потому что ПОИСКПОЗ рассчитает # N / A, если значение совпадений содержит пустые значения. Тестируя для пустых значений с данными <> «», в том числе и ПОИСКПОЗ как значение, если оно истинно, то результирующий массив будет содержать номера в сочетании с ЛОЖЬ:

{1; 1; ЛОЖЬ; 4; 4; 6; 6; ЛОЖЬ; 9; 9}

которая подается ЧАСТОТА, что и массив данных. ЧАСТОТА будет рассчитывать массив вроде этого:

{2; 0; 0; 2; 0; 2; 0; 0; 2; 0; 0}

Элементы в этом массиве преобразуются либо в 1, или ЛОЖЬ с окончательным (внешним) ЕСЛИ заявлением. Результат выглядит следующим образом:

{1; ЛОЖЬ; ЛОЖЬ; 1; ЛОЖЬ; 1; ЛОЖЬ; ЛОЖЬ; 1; ЛОЖЬ; ЛОЖЬ}

СУММ затем складывает 1 и рассчитывает 4.

Эта формула из замечательной книги Майка Гивина по формулам массива.

Использование СЧЁТЕСЛИ вместо ЧАСТОТЫ для подсчета уникальных значений

Другой способ подсчета уникальных числовых значений состоит в использовании СЧЁТЕСЛИ вместо ЧАСТОТЫ. Эта формула гораздо проще, но нужно учитывать, что использование СЧЁТЕСЛИ на больших наборах данных для подсчета уникальных значений, может вызвать проблемы с производительностью. Формула на основе ЧАСТОТА, хоть и более сложная, но вычисляет гораздо быстрее.

С критериями

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

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

В показанном примере формула в G5 является:

= {СУММ(—(ЧАСТОТА(ЕСЛИ(C5:C11 = G4;СТРОКА(B5:B11; B5:B11;0));СТРОКА(B5:B11) — СТРОКА(B5)+1) > 0))}

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

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

С помощью ЕСЛИ ПОИСКПОЗ рассчитывает только для строк, которые соответствуют критериям.

В конце концов, массив позиций, генерируемых ПОИСКПОЗ подаются к частотному в аргументе массива данных.

Аргумент, хранимый в массиве, строится из этой части формулы:

СТРОКА(B5:B11) -СТРОКА(B5) +1

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

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10}

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

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

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

Наконец, СУММПРОИЗВ просто складывает эти значения и рассчитывает общее число.

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

Обработка пустых ячеек в диапазоне

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

{= СУММ(— (ЧАСТОТА(ЕСЛИ(B5:B11 <> «»; ЕСЛИ(C5:C11 = G4; ПОИСКПОЗ(B5:B11; B5:B11;0))); СТРОКА(B5:B11) -СТРОКА(B5) +1)> 0))}

С двумя критериями

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

= {СУММ(— (ЧАСТОТА(ЕСЛИ(c1; ЕСЛИ(c2; ПОИСКПОЗ (значения;значения;0))); СТРОКА (значения) — СТРОКА (значения.первая позиция) +1)> 0))}

Там, где c1 = критерий1, c2 = критерий2 и значения = диапазон значений.

Взято из замечательной книги Майка Гивина по формулам массива, Control-Shift-Enter.

В диапазоне с СЧЁТЕСЛИ

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(данные;данные))

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

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

{3; 3; 3; 2; 2; 3; 3; 3; 2; 2}.

После того, как СЧЁТЕСЛИ заканчивает, результаты используются в качестве делителя, 1 в качестве числителя. Значения, которые появляются в данных, после появляются в массиве как 1, но значения, которые появляются несколько раз будут отображаться как дробные значения, которые соответствуют множеству. (Т.е. значение, которое появляется в 5 раз, данные будут генерировать 5 элементов в массиве со значением 1/5 = 0,2).

Наконец, функция СУММПРОИЗВ суммирует все значения в массиве и рассчитывает результат.

Обработка пустых ячеек

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

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(данные; данные&»»))

Данные &»» выражение предотвращает нули массива, созданного СЧЁТЕСЛИ, когда имеются пустые ячейки данных. Она делает это путем обеспечения того, что критерии для пустой ячейки «», а не ноль. Это важно, так как ноль в делителе выбросит #ДЕЛ/0. Так что эта версия формулы не будет выдавать ошибку, когда есть пустые ячейки, но она будет включать в себя пустые ячейки в счете. Если вы хотите исключить пустые ячейки в счете, используйте:

=СУММПРОИЗВ((данные <> «»)/СЧЁТЕСЛИ(данные;данные и «»))

Низкая производительность?

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

Источник: https://excelpedia.ru/formuli-v-excel/kolichestvo-unikalnyh-sovpadenii

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

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

Значения 5, 6, 7 и 6, результатом являются три уникальных значения : 5, 6 и 7.
Значения "Брэнли", "Дойл", "Дойл", "Дойл" — это два уникальных значения: "Андрей" и "Дойл".

Существует несколько способов подсчета уникальных значений среди дубликатов.

В диалоговом окне Расширенный фильтр можно извлечь уникальные значения из столбца данных и ввести их в новое место. Затем с помощью функции ЧСТРОК можно подсчитать количество элементов в новом диапазоне.

Выберем диапазон ячеек или убедитесь, что активная ячейка находится в таблице.
Убедитесь, что диапазон ячеек имеет заголовок столбца.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Появится диалоговое окно Расширенный фильтр.
Нажмите кнопку Копировать в другое место.
В поле Копировать в введите ссылку на ячейку.
Вы также можете нажать кнопку Свернуть , чтобы временно скрыть диалоговое окно, выбрать ячейку на этом сайте и нажать кнопку Развернуть .
Выберите поле Уникальные записи и нажмите кнопку ОК.
Уникальные значения из выбранного диапазона копируется в новое место, начиная с ячейки, указанной в поле Копировать в.
В пустой ячейке под последней ячейкой диапазона введите функцию СТРОКИ. Используйте диапазон уникальных значений, скопированные в качестве аргумента, исключая заголовок столбца. Например, если диапазон уникальных значений — B2:B45, введите =СТРОКИ(B2:B45).

Для этой задачи используйте сочетание функций ЕСЛИ,СУММ,ЧАСТОТА,НАЙТИИ LEN:

Назначьте значение 1 каждому из истинных условий с помощью функции ЕСЛИ.
Сложить итог с помощью функции СУММ.
Подсчет количества уникальных значений с помощью функции ЧАСТОТА. Функция ЧАСТОТА игнорирует текст и нулевые значения. Для первого вхождения определенного значения эта функция возвращает число, равное количеству его вхождений. Для каждого вхождения с одинаковым значением после первого функция возвращает ноль.
Возвращает положение текстового значения в диапазоне с помощью функции MATCH. Возвращаемая величина затем используется в качестве аргумента функции ЧАСТОТА для оценки соответствующих текстовых значений.
Находите пустые ячейки с помощью функции LEN. Пустые ячейки имеют длину 0.

Примечания:

Формулы, приведенные в этом примере, должны быть введены как формулы массива. Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Чтобы оценить функцию пошаговую проверку, выйдите из ячейки, содержащей формулу, а затем на вкладке Формулы в группе Зависимости формул нажмите кнопку Вы оцениваете формулу.

Функция ЧАСТОТА вычисляет частоту ветвей значений в диапазоне значений и возвращает вертикальный массив чисел. Например, с помощью частоты можно подсчитать количество результатов тестирования, которые попадают в диапазоны оценок. Так как эта функция возвращает массив, она должна быть введена как формула массива.
Функция ПОИСК ПОИСК ПО ищет указанный элемент в диапазоне ячеек, а затем возвращает его относительную позицию в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, формула =MATCH(25;A1:A3;0) возвращает число 2, поскольку 25 является вторым элементом в диапазоне.
Функция LEN возвращает количество символов в текстовой строке.
Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом другой функции. Например, СУММ(A1:A5) суммирует все числа, содержащиеся в ячейках A1–A5.
Функция ЕСЛИ возвращает одно значение, если условие, которое вы указываете, возвращает значение ИСТИНА, и другое, если условие возвращает значение ЛОЖЬ.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

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

Источник: https://support.microsoft.com/ru-ru/office/%D0%BF%D0%BE%D0%B4%D1%81%D1%87%D0%B5%D1%82-%D0%BA%D0%BE%D0%BB%D0%B8%D1%87%D0%B5%D1%81%D1%82%D0%B2%D0%B0-%D1%83%D0%BD%D0%B8%D0%BA%D0%B0%D0%BB%D1%8C%D0%BD%D1%8B%D1%85-%D0%B7%D0%BD%D0%B0%D1%87%D0%B5%D0%BD%D0%B8%D0%B9-%D1%81%D1%80%D0%B5%D0%B4%D0%B8-%D0%BF%D0%BE%D0%B2%D1%82%D0%BE%D1%80%D1%8F%D1%8E%D1%89%D0%B8%D1%85%D1%81%D1%8F-8d9a69b3-b867-490e-82e0-a929fbc1e273

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

history 23 апреля 2013 г.

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

Сначала поясним, что значит подсчет уникальных значений. Пусть имеется массив текстовых значений {"а","b", "а","а","а" }. При подсчете уникальных игнорируются все повторы, т.е. значения выделенные жирным . Соответственно, подсчитываются остальные значения, т.е. "а" и "b". Ответ очевиден: количество уникальных значений равно 2.

Задача

Произведем подсчет числа уникальных текстовых значений в диапазоне A7:A15 (см. файл примера ). Диапазон может содержать пустые ячейки.

Решение

Запишем формулу =СУММПРОИЗВ(( A7:A15<>"" )/СЧЁТЕСЛИ(A7:A15;A7:A15))

Если в диапазоне кроме текстовых значений содержатся также и числа, то формула подсчитает и их. Чтобы игнорировать числовые значения нужно записать формулу =СУММПРОИЗВ(ЕТЕКСТ( A7:A15 )/СЧЁТЕСЛИ(A7:A15;A7:A15))

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

=СУММПРОИЗВ((Исходный_список<>"")/СЧЁТЕСЛИ(Исходный_список;Исходный_список))

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

Примечание : Уникальные значения в файле примера выделены с помощью Условного форматирования (см. статью Выделение уникальных значений в MS EXCEL ).

Часто вместо формулы =СУММПРОИЗВ(( A7:A15<>"" )/СЧЁТЕСЛИ(A7:A15;A7:A15)) используют более простую формулу =СУММПРОИЗВ(1 /СЧЁТЕСЛИ(A7:A15;A7:A15)) . Разница между формулами состоит в том, что вторая формула учитыват значения Пустой текст ("") , а первая их игнорирует.

Приведем пример, когда это бывает важно.

Пусть дана таблица продаж товаров (см. рисунок ниже, столбцы А и В). С помощью формулы =ЕСЛИ(МЕСЯЦ(B26)=1;A26;"") определяются товары, которые были проданы в январе. Если товар продан не в январе, то формула возвращает значение Пустой текст. Пользователь решает подсчитать количество уникальных товаров в январе (их всего 3: Товар1, Товар2 и Товар3).

Формула =СУММПРОИЗВ(( A7:A15<>"" )/СЧЁТЕСЛИ(A7:A15;A7:A15)) вернет правильный результат 3, а формула =СУММПРОИЗВ(1 /СЧЁТЕСЛИ(A7:A15;A7:A15)) вернет 4, т.к. в "пустых" ячейках С31:С34 на самом деле содержатся 4 значения "", которые воспринимаются ей как некое текстовое значение, хотя и нулевой длины.

СОВЕТ : Как подсчитать уникальные числовые значения показано в одноименной статье Подсчет уникальных числовых значений .

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

Источник: https://excel2.ru/articles/podschet-unikalnyh-tekstovyh-znacheniy-v-ms-excel

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

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

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

Как посчитать количество слов в ячейке Excel

Для подсчета количества слов в ячейке нам потребуются функции и . Формула для учета количества слов будет выглядеть так:

=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;” “;””))+1

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

Как эта формула работает?

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

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

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

Соответственно, наша формула работает следующим образом:

Функция в первой части формулы подсчитывает количество символов в ячейке (с учетом пробелов)
Во второй и третьей части формулы мы комбинируем функции и для подсчета количества символов в ячейке без пробелов
Прибавляем к полученному значению число “один”

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

Во избежание этого, я предлагаю использовать в дополнение две функции: и ЕПУСТО . Формула будет выглядеть так:

=ЕСЛИ(ЕПУСТО(A1);0;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;” “;””))+1)

Эти две функции проверяют, есть ли текст в ячейке или она пустая. Если в ячейке нет текста, формула вернет значение “ноль”.

Как посчитать количество слов в нескольких ячейках Excel

Теперь, перейдем на более сложный уровень.

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

=СУММПРОИЗВ(ДЛСТР(A1:A10)-ДЛСТР(ПОДСТАВИТЬ(A1:A10;” “;””))+1)

В указанной выше формуле А1:А10 это диапазон ячеек в рамках которого мы хотим посчитать количество слов.

Как эта формула работает?

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

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

Я надеюсь, что в будущем Excel получит штатную возможность для подсчета слов.

Уверен, эти приемы помогут вам стать лучше в Excel.

Всем добрый день, сегодня я открываю рубрику "Функции" и начну с функции СЧЁТЕСЛИ. Честно говоря, не очень-то и хотел, ведь про функции можно почитать просто в справке Excel. Но потом вспомнил свои начинания в Excel и понял, что надо. Почему? На это есть несколько причин:

Функций много и пользователь часто просто не знает, что ищет, т.к. не знает названия функции.
Функции - первый шаг к облегчению жизни в Экселе.

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

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

СЧЁТЕСЛИ("Диапазон";"Критерий")

Если с первым аргументом более-менее понятно, можно подставить диапазон типа A1:A5 или просто название диапазона, то со вторым уже не очень, потому что возможности задания критерия достаточно обширны и часто незнакомы тем, кто не сталкивается с логическими выражениями.

Самые простые форматы "Критерия":

Ячейка строго с определенным значением, можно поставить значения ("яблоко"), (B4),(36). Регистр не учитывается, но даже лишний пробел уже включит в подсчет ячейку.
Больше или меньше определенного числа. Тут уже идет в ход знак равенства, точнее неравенств, а именно (">5");("<>10");("<=103").

Но ведь нам иногда нужны более специфичные условия:

Есть ли текст. Хотя кто-то может сказать, что функция и так считает только непустые ячейки, но если поставить условие ("*"), то будет искаться только текст, цифры и пробелы в расчет приниматься не будут.
Больше (меньше) среднего значения диапазона: (">"&СРЗНАЧ(A1:A100))
Содержит определенное количество символов, например 5 символов:("?????")
Определенный текст, который содержится в ячейке: ("*солнце*")
Текст, который начинается с определенного слова: ("Но*")
Ошибки: ("#ДЕЛ/0!")
Логические значения ("ИСТИНА")

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

«Глаза боятся, а руки делают»

Навигация по записям

Функция СЧЁТЕСЛИ: подсчет количества ячеек по определенному критерию в Excel : 58 комментариев

поМарка
Подскажите, а как найти повторяющиеся значения, но что бы к регистру не было чувствительности?
admin Автор записи
по идее, СЧЁТЕСЛИ как раз и ищет совпадения, невзирая на регистр.
поМарка
В том и дело, что не находит — он не различает при поиске заглавные и строчные буквы, а объединяет их в общее количество совпадений…
Подскажите, может надо какой-нибудь символ поставить при поиске? (апострофы и кавычки не помогают)
Игорь
Как задать условие в excel что бы он считал определенный диапазон ячеек в строках с определенным значением в первом столбце?
admin Автор записи
Игорь, вообще-то СЧЁТЕСЛИ это и делает. Наверное, вам лучше конкретизировать задачу.
Анна
понравилась статься, но увы не получается что-то, мне нужно суммировать из разных чисел повторяющиеся цифры, например 123 234 345 456 мне нужно посчитать сколько «1″, «2″, «3″ и т.д. в этих числах то есть чтобы формула распознала одинаковые цифры и считала их, если это возможно напишите как быть? Буду очень ждать, С уважением, Анна Ириковна
admin Автор записи
Гм. хорошо бы увидеть пример
Но без неё могу дать наметку — создайте рядом столбец, где через текстовую формулу вы отберете числа по группам. Например, правсимв(А1;1). А потом уже по этому столбцу работайте СЧЁТЕСЛИ.
Alex
Как распространить формулу по столбцу, чтобы диапазон в формуле оставался тот же, а критерий менялся?
admin Автор записи
СЧЁТЕСЛИ($A$1:$A$100;B1)
Анна
Пример такой, 23 12 1972 то есть это дата рождения, мне нужно чтобы суммировалось количество двоек, то есть не 2+2+два, а что их всего три двойки, то есть в ячейке конечной должно стоять 3, единиц 2, 3 7 9 по единице, такое возможно? просто я голову сломала, я самоучка, но такие формулы мне сложноваты, если Вам не очень трудно дайте пожалуйста образец формулы полностью, хотя бы на одно число, с уважением, Анна Ириковна
admin Автор записи
Представим, что ваша дата в ячейке А1.
Тогда считаем сколько двоек: = длстр(A1)-длстр(ПОДСТАВИТЬ(A1;»2″;»"))
Если же на несколько чисел, то лучше скиньте пример, как вы это хотите в конце, а то вариантов много.
Анна
спасибо, я сейчас попробую формулу … попробую сама, если совсем не выйдет тогда с большим поклоном буду просить советов еще)) С уважением, Анна Ириковна
Анна
не выходит… можно я пришлю Вам то что мне нужно на электронный адрес? просто это таблица.. мне сложно ее описать… Анна И.
Анна
Макс
Хорошая статья) Но все равно не смог разобраться со своим заданием. У меня есть 3 столбца, один это Студенты, второй Преподаватели, третий Оценки. Подскажите, как посчитать количество студентов, обучающихся у Ивановой, получивших положительные оценки? Получается вроде как 2 диапазона и 2 критерия, и я не могу понять)
admin Автор записи
Используйте СЧЁТЕСЛИМН
Maykot
Добрый день.
Помогите разобраться с диапазоном.
У меня есть ячейка А2 в которой есть текстовое значение — например «солнце».
В ячейке A3 значение «море». И т.д.
Как правильно вписать в Формулу =СУММЕСЛИ(C:C;»*солнце*»;D:D) вместо конкретного диапазона («*солнце*») содержимое ячейки A2, т.е. не =СУММЕСЛИ(C:C;»*солнце*»;D:D), а вместо «*солнце*» была ссылка на ячейку?
admin Автор записи
СУММЕСЛИ(C:C;»*»&$A$1&»*»;D:D)
Дмитрий
Александр
Здравствуйте! Подскажите пожалуйста, как в Excel представить формулу:
Скорректированная стоимость =
= Стоимость * (К1 + К2 + … + КN – (N — 1);
где:
К1, К2, КN — коэффициенты, отличные от 1
N – количество коэффициентов, отличных от 1.
Александр
Здравствуйте! Подскажите пожалуйста, как в формуле:
=СУММЕСЛИ(C3:C14;»
Михаил
Добрый вечер!
Подскажите, пожалуйста, как посчитать количество ячеек в которых указана какая-либо дата? То есть, в столбце есть ячейки с датами (разными) и есть ячейки с текстом (разным), мне нужно посчитать количество ячеек с датами.
Спасибо!
admin Автор записи
СЧЁТЕСЛИ(F26:F29;»01.01.2016″)
Пойдет?
Юлия
Добрый вечер! Подскажите, пожалуйста, формулу, считающую цифры только которые больше 8 (переработка в табеле учета рабочего времени). Вот неправильный вариант: =SUMIF(C42:V42;»>8″)+SUMIF(C42:V42)
admin Автор записи
SUMIF(C42:V42,»>8″) =СУММЕСЛИ(C1:C2;»>8″)
Не забывайте, что английская версия использует запятые между аргументами.
Д.Н.
Добрый день! спасибо за формулу массива для диапазона с несколькими критериями!
у меня, наверное, глупый вопрос, но как заменить текст {1;2;3} на ссылки на ячейки с текстовыми значениями.
то есть если я ввожу { «X»; «Y»;»Z»} считает всё верно
но при вводе {A1;A2;A3} — ошибка,
дело в фигурных скобках?)
Виталий
Спасибо. Очень помогла статья.
Денис
Добрый день. Столкнулся с такой проблемой в функции СЧЁТЕСЛИМН. При вводе 2х диапазонов все считает отлично, но при добавлении 3-го — выдает ошибку. Может ли скрываться подвох в количестве ячеек?
У меня =СЧЁТЕСЛИМН(‘очная форма обучения’!R11C13:R250C13;»да»; ‘очная форма обучения’!R11C7:R250C7;»бюджет»; ‘очная форма обучения’!R16C9:R30C9;»да»)
Без 3-го диапазона и условия все нормально.
Заранее благодарен.
admin Автор записи
Денис, выровняйте диапазоны, они все должны быть одинаковые и можете ставить до 127 наборов условий.
A.K.
Здравствуйте, помогите, пожалуйста, разобраться.
Есть два столбца: один — дата, второй — время (формат 00:00:00).
Необходимо выбрать даты соответствующие определенному периоду времени.
При этом, таких промежутков должно быть 4, т.е. каждые 6 часов.
Возможно ли это это задать одной формулой и если — да, то какой?
admin Автор записи
Добрый день.
Конечно, можно. Правда, я не понял, вам надо по датам или по часам? Две разные формулы. И как вы хотите это разбить? Чтобы промежутки помечались номерами? Типа первые 6 часов суток — это 1, вторые -2 и т.д.?
андрей
Спасибо за ранее, а вопрос такой:
дано:
много строчек, один столбец
зеленым цветом обозначены готовые пакеты документов, белым -недоработанные
содержание ячеек: разные фамилии сотрудников.
задача: как с помощью формулы сделать таблицу: сколько у каждого сотрудника готовых пакетов.
admin Автор записи
К сожалению цвет формулой не определяется. Точнее определяется, но там надо формулу пользовательскую писать
Я бы сделал так — отфильтровал по цвету зеленых и в соседнем столбце поставил «готовые», потом так же поставил «недоработанные» — белые ячейки.
Потом через СЧЁТЕСЛИ нашел все, что надо.
Кросс
Здравствуйте! Вопрос такой. Имеются 4 столбца, в которых соответственно указаны ученики (столбец А), № школы (столбец В), баллы по химии (столбец С), баллы по физике (столбец D). Надо найти кол-во учеников определённой школы (например, 5), которые набрали по физике баллов больше, чем по химии. Всего учеников 1000. Можно ли использовать какую-то одну формулу для ответа на вопрос? Пробую использовать СЧЕТЕСЛИМН, но не получается.
admin Автор записи
Нет, прежде чем использовать Счётесли, придется добавить еще один столбец, где через ЕСЛИ определить тех, у кого по физике больше баллов, чем по физике и потом уже использовать СЧЁТЕСЛИМН.
Кросс
Окей, спасибо
Александр
Добрый день! Задача в общем такая. В таблице указано построчно фио, вид спорта, разряд. Как на другой вкладке сделать таблицу автоматически считающую сколько разрядников в каждом виде спорта и каких конкретно разрядов?
Алена
Здравствуйте! Нужна помощь.
Имеется столбец с датами рождения в формате 19740815, а необходимо преобразовать в формат 15.08.1974
Спасибо за ранее.
admin Автор записи
Добрый день.
Ну самое простое — Текст по столбцам -фиксированная ширина (4-2-2) — потом добавить столбец с функцией ДАТА.
admin Автор записи
Попробуйте СЧЁТЕСЛИМН.
Надежда
Здравствуйте.
Подскажите, пожалуйста, как найти сумму в ячейках по условию:
есть таблица, в первом столбце которой указан код товара, во второй сумма по нему. Строк с одинаковым кодом может быть несколько. Надо найти общую (итоговую) сумму по каждому товару.
Заранее спасибо.
admin Автор записи
Попробуйте СУММЕСЛИ.
Дим Подскажите что исправить надо?
СЧЁТЕСЛИМН(‘Raw Data’!B2:B150;»Bucharest»;’Raw Data’!E1:E150;»06.11.2014″)
Вера
Добрый день. Подскажите, какой критерий в формуле СУММЕСЛИ нужно поставить если нужно посчитать количество ячеек содержащих цифры из диапазона, где есть и числа и буквы.
Спасибо.
admin Автор записи
Здравствуйте. Вот так просто не получится. Либо формулой массива либо сделать еще один диапазон, где через текстовые функции будут искаться цифры. И потом уже через СЧЁТЕСЛИ найдете свой результат.
Роман
Добрый вечер.
Есть столбец, в который постоянно добавляются данные, нужно в соседний столбец вставлять цифру, сколько раз до этой строчки встречались подобные данные
Анастасия
Добрый день всем!
Есть фактический график выхода сотрудников, все рабочие часы написаны в формате «09*21″ — дневная полная смена и «21*09″ — ночная полная смена.
Также есть дни с неполными сменами, которые считаются к зарплате по почасовой ставке, например «18*23″ и тд.
Формат всех ячеек — текстовый.
Необходимо, чтобы формула высчитывала по каждой строке (каждому сотруднику соответственно) количество полных смен за месяц, в идеале если она будет учитывать критерии «09*21″+»21*09″, но можно и по одному критерию, я тогда просто столбцы эти скрою и их уже объединю суммой.
Через =счетесли пробовала, в окошке формулы значение считает верно, а в самой ячейке отображает тупо написанную формулу, формат какой только не ставила — не помогает.
Пыталась заменить 09*21 на 09:00 — 21:00 в ячейках и формуле соответственно, но тоже ни в какую.
Проставляла в формуле и «09*21*», и «*09*21*» — без толку.
Если можно такую штуку делать при условии, что записано будет «09:00 — 21:00″ — вообще отлично, один месяц мне проще будет перелопатить, но дальше уже всё будет ровно)
и сразу с ходу вопрос — есть ли формула, по которой можно будет считать общее количество часов в диапазоне со всеми любыми значениями («18:00 — 23:00″, «12:45 — 13:45″ и тд), кроме вышеуказанных «09:00 — 21:00″ и «21:00 — 09:00″ либо считать все ячейки, где количество часов 12 и отдельно все, где количество часов меньше 12.
Заранее спасибо огромное, ломаю голову уже неделю!(((
admin Автор записи
Попробуйте СЧЁТЕСЛИ($A$1:A10;A10) — вставляется в ячейку B10.
Наталья
Вопрос уже задавался, но ответили вы через почту, не смогли бы повторить ответ уже здесь?
«Пример такой, 23 12 1972 то есть это дата рождения, мне нужно чтобы суммировалось количество двоек, то есть не 2+2+два, а что их всего три двойки, то есть в ячейке конечной должно стоять 3, единиц 2, 3 7 9 по единице, такое возможно? просто я голову сломала, я самоучка, но такие формулы мне сложноваты, если Вам не очень трудно дайте пожалуйста образец формулы полностью, хотя бы на одно число, с уважением, Анна Ириковна»
Вера
Добрый день! У меня в Экселе диапазон 30 ячеек,но необходимо,что бы считало сумму только первых 20 ячеек. Помогите пожалуйста!
admin Автор записи
Здравствуйте. А просто указать диапазон первых 20ти ячеек не вариант? Мне без файла непонятно, в чём сложность.

Пример 1: данные из панели состояния

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

Бывают более сложные ситуации. Например, если столбцы заполнены не полностью, т.е. в столбце есть пустые позиции. В случае выбора исключительно столбца, пустые строки подсчитаны не будут. Рассмотрим пример ниже. Пустым ячейкам из первого столбца соответствуют значения ячеек второго. Выберем полностью первый столбец и при нажатой клавише «Ctrl» нажимаем по соответствующим ячейкам со значениями из второго столбца. В нижней части окна отобразиться все количество строк выбранного диапазона, где ячейки не имеют значений.

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

Пример 2: вычисление с помощью формул

Рассмотренный выше пример не дает возможности сохранить результаты подсчета и отображать их в ячейке на листе. Более того, часто необходимо учитывать и пустые позиции. Для этого удобно пользоваться формулой ЧСТРОК.

Формула имеет вид: =ЧСТРОК(массив значений).

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

Затем нажать Enter.

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

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

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

2) Откроется окно Конструктора формул. В значении «Категория» установите «Ссылки и массивы», также можно выбрать «Полный алфавитный перечень». Находим функцию «ЧСТРОК» и нажимаем «ОК»

3) В окне выбора аргументов формулы установите курсор на после массива и выделите область с нужным диапазоном значений. После появления ячеек диапазона в поле аргументов функции нажмите «ОК»

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

Пример 3: использование форматирования и фильтрации

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

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

2) Перейдите на главную панель и выберите «Стили» далее нажимаем на пиктограмму «Условное форматирование». В условном форматировании выберите «Правила выделения ячеек» и для нашего случая выбираем правило «Большее...»

3) Появляется диалог задания условий. Слева указывается значение больше которого, ячейки окрасятся заданным цветом. Справа части задается цвет. После выбора нажимаем «ОК»

4) Как видно ниже, позиции, которые соответствуют условиям окрашены заданным цветом. Находясь на главной панели выделите весь диапазон и нажимаем пункт меню «Сортировка и фильтр» и выбираем «Фильтр»

Графики и диаграммы (5)
Работа с VB проектом (12)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (64)
Разное (41)
Баги и глюки Excel (4)

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

И необходимо подсчитать количество повторений каждого наименования:

Как ни странно, но сделать это весьма просто: в Excel имеется функция - СЧЁТЕСЛИ , при помощи которой все это сделать можно буквально за секунды. Если количество повторений каждого наименования необходимо вывести в столбец В таблицы, а сами наименования расположены в столбце А:
=СЧЁТЕСЛИ($A$2:$A$30 ; A2)
Диапазон ($A$2:$A$30) - указываются ячейки диапазона, в которых записаны значения, количество которых необходимо подсчитать. Главная особенность: данный аргумент может быть исключительно ссылкой на ячейку или диапазон ячеек. Недопустимо указывать произвольный массив значений.
Критерий (A2) - указывается ссылка на ячейку или непосредственно значение для подсчета. Т.е. можно указать и так: =СЧЁТЕСЛИ($A$2:$A$30 ;"Яблоко") . Помимо этого можно применять символы подстановки: ? и *. Т.е. указав в качестве Критерия "*банан*" можно подсчитать количество ячеек, в которых встречается слово "банан" (банановый, банан, бананы, банановый сок, сто бананов, три банана и орех и т.п.). А указав "банан*" - значения, начинающиеся на "банан" (бананы, банановый сок, банановая роща и т.п.). "?" - заменяет лишь один символ, т.е. указав "бан?н" можно подсчитать строки и со значением "банан" и со значением "банон" и т.д. Если в качестве критерия указать =СЧЁТЕСЛИ($A$2:$A$30 ;"*") , то будут подсчитаны все текстовые значения. Числовые значения при этом игнорируются. Данные подстановочные символы (* и?) не получится применить к числовым значениям - исключительно к тексту. Т.е. если если указать в качестве критерия "12*", то числа 1234, 123, 120 и т.п. не будут подсчитаны. Для подсчета числовых значений следует применять операторы сравнения: =СЧЁТЕСЛИ($A$2:$A$30 ;">12")

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

Если необходимо подсчитать количество повторений на основании нескольких условий(значений), то начиная с 2007 Excel это легко можно сделать при помощи функции СЧЁТЕСЛИМН(COUNTIFS). Синтаксис функции почти такой же, как у СЧЁТЕСЛИ(COUNTIF), только условий и диапазонов больше:
=СЧЁТЕСЛИМН($A$2:$A$30 ; A2 ; $B$2:$B$30 ; B2)
предполагается, что условия записаны в столбце В
По сути идет просто перечисление:
=СЧЁТЕСЛИМН(Диапазон_условий1;Условие1; Диапазон_условий2;Условие2; Диапазон_условий3;Условие3; и т.д.)

Но. Бывают случаи, когда список расположен вовсе не в таблице, а в одной ячейке($D$1):
Дыня Киви Груша Яблоко Дыня Груша Груша Арбуз Яблоко Банан Яблоко Яблоко Банан Яблоко Яблоко Дыня Дыня Киви Банан Дыня Арбуз Дыня Киви Яблоко Дыня Груша Яблоко Киви Арбуз
Здесь СЧЁТЕСЛИ точно не поможет. Но в Excel полно других функций и все можно сделать так же достаточно просто:
=(ДЛСТР($D$1)-ДЛСТР(ПОДСТАВИТЬ($D$1 ; D3 ;"")))/ДЛСТР(D3)
ДЛСТР - подсчитывает количество символов в указанной ячейке/строке($D$1 , D3)
ПОДСТАВИТЬ (текст; старый_текст; новый_текст) - заменяет в указанном тексте заданный символ на любое другое заданное значение. По умолчанию заменяет все повторы указанного символа. Именно это и положено в основу алгоритма. На примере значения Банан(D3) пошаговый разбор формулы:

при помощи функции ДЛСТР получаем количество символов в строке с исходным текстом($D$1) =(170-ДЛСТР(ПОДСТАВИТЬ($D$1 ; D3 ;"")))/ДЛСТР(D3) ;
при помощи функции ПОДСТАВИТЬ заменяем в строке с исходным текстом($D$1) все значения Банан(D3) на пусто и при помощи ДЛСТР получаем количество символов строки после этой замены =(170-155)/ДЛСТР(D3) ;
вычитаем из общего количества символов количество символов в строке после замены и делим результат на количество символов в критерии =(170-155)/5 .

Получаем число 3. Что нам и требовалось.

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

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

Выполняем подсчет ячеек

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

Способ 1: счетчик на нижней панели

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

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

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

Наведите курсор на строку состояния.
Нажмите правую кнопку мыши.
В появившемся меню поставьте галочку напротив пункта «Количество».

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

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

Вы также можете посчитать количество заполненных ячеек в «Экселе» формулой СЧЕТЗ. В некоторых случаях она бывает очень полезной. Например, она отлично подойдет, когда количество заполненных ячеек нужно отобразить непосредственно в какой-то ячейке. Таким образом вы сможете наблюдать за изменениями в показаниях сразу же.

Выделите ячейку, в которой будет выводиться результат подсчета.
Нажмите по кнопке «Вставить функцию», расположенной слева от строки формул.
В появившемся окне Мастера функций найдите в списке строку «СЧЕТЗ», выделите ее и нажмите кнопку «ОК».
На втором этапе в параметрах «Значение» укажите область или области, в которых будет проводиться подсчет.
Нажмите «ОК».

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

При желании эти же действия вы можете выполнить путем ввода функции СЧЕТЗ непосредственно в строку функций. Синтаксис у нее следующий:

СЧЕТЗ(значение1;значение2;...).

То есть вам изначально необходимо ввести само название функции (=СЧЕТЗ), а потом в скобках через точку с запятой прописать диапазон ячеек, в которых необходимо проводить подсчет.

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

Продолжая разжевывать тему, как в «Экселе» посчитать количество ячеек, коснемся функции СЧЕТ. Как можно понять, она очень похожа на предыдущую, однако дает возможность подсчитать ячейки, в которых введены лишь числовые значения. Вот как ей пользоваться:

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

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

Способ 4: функция СЧЕТЕСЛИ

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

Так же, как и в предыдущих способах, установите курсор на ячейку, в которой будет выполняться функция.
Вызовите окно Мастера создания функций.
Из списка выделите строку «СЧЕТЕСЛИ» и нажмите кнопку «ОК».
Появится довольно знакомое окно, в котором необходимо ввести переменные функции. Как можно заметить, помимо диапазона, нужно указать критерий. Например, вы можете ввести «>400». Это будет означать, что считаться будут те ячейки, значения в которых больше 400.
Далее вы можете указать еще диапазоны и критерии к ним.
По окончании ввода параметров нажмите кнопку «ОК».

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

Способ 5: функция СЧЕТЕСЛИМН

Последний, пятый способ, как в «Экселе» посчитать количество заполненных ячеек, по своей сути ничем не отличается от предыдущего. Он используется в тех случаях, когда рассчитать нужно много переменных. То есть вы сможете указать 126 условий. Приводить подробную инструкцию, как использовать функцию СЧЕТЕСЛИМН нет смысла, так как она полностью повторяет предыдущую.

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

Источник: https://passportbdd.ru/theory-reviews/podschet-kolichestva-unikalnyh-znachenii-sredi-povtoryayushchihsya-kak/

#14 MS Excel Как просуммировать повторяющиеся строки в таблице, time: 3:21

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

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

Как посчитать количество слов в ячейке Excel

=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;” “;””))+1

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

Как эта формула работает?

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

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

Соответственно, наша формула работает следующим образом:

Функция в первой части формулы подсчитывает количество символов в ячейке (с учетом пробелов)
Во второй и третьей части формулы мы комбинируем функции и для подсчета количества символов в ячейке без пробелов
Прибавляем к полученному значению число “один”

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

=ЕСЛИ(ЕПУСТО(A1);0;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;” “;””))+1)

Как посчитать количество слов в нескольких ячейках Excel

Теперь, перейдем на более сложный уровень.

=СУММПРОИЗВ(ДЛСТР(A1:A10)-ДЛСТР(ПОДСТАВИТЬ(A1:A10;” “;””))+1)

В указанной выше формуле А1:А10 это диапазон ячеек в рамках которого мы хотим посчитать количество слов.

Как эта формула работает?

Я надеюсь, что в будущем Excel получит штатную возможность для подсчета слов.

Уверен, эти приемы помогут вам стать лучше в Excel.

Функций много и пользователь часто просто не знает, что ищет, т.к. не знает названия функции.
Функции - первый шаг к облегчению жизни в Экселе.

СЧЁТЕСЛИ("Диапазон";"Критерий")

Самые простые форматы "Критерия":

Ячейка строго с определенным значением, можно поставить значения ("яблоко"), (B4),(36). Регистр не учитывается, но даже лишний пробел уже включит в подсчет ячейку.
Больше или меньше определенного числа. Тут уже идет в ход знак равенства, точнее неравенств, а именно (">5");("<>10");("<=103").

Но ведь нам иногда нужны более специфичные условия:

Есть ли текст. Хотя кто-то может сказать, что функция и так считает только непустые ячейки, но если поставить условие ("*"), то будет искаться только текст, цифры и пробелы в расчет приниматься не будут.
Больше (меньше) среднего значения диапазона: (">"&СРЗНАЧ(A1:A100))
Содержит определенное количество символов, например 5 символов:("?????")
Определенный текст, который содержится в ячейке: ("*солнце*")
Текст, который начинается с определенного слова: ("Но*")
Ошибки: ("#ДЕЛ/0!")
Логические значения ("ИСТИНА")

«Глаза боятся, а руки делают»

Навигация по записям

Функция СЧЁТЕСЛИ: подсчет количества ячеек по определенному критерию в Excel : 58 комментариев

поМарка
Подскажите, а как найти повторяющиеся значения, но что бы к регистру не было чувствительности?
admin Автор записи
по идее, СЧЁТЕСЛИ как раз и ищет совпадения, невзирая на регистр.
поМарка
В том и дело, что не находит — он не различает при поиске заглавные и строчные буквы, а объединяет их в общее количество совпадений…
Подскажите, может надо какой-нибудь символ поставить при поиске? (апострофы и кавычки не помогают)
Игорь
Как задать условие в excel что бы он считал определенный диапазон ячеек в строках с определенным значением в первом столбце?
admin Автор записи
Игорь, вообще-то СЧЁТЕСЛИ это и делает. Наверное, вам лучше конкретизировать задачу.
Анна
понравилась статься, но увы не получается что-то, мне нужно суммировать из разных чисел повторяющиеся цифры, например 123 234 345 456 мне нужно посчитать сколько «1″, «2″, «3″ и т.д. в этих числах то есть чтобы формула распознала одинаковые цифры и считала их, если это возможно напишите как быть? Буду очень ждать, С уважением, Анна Ириковна
admin Автор записи
Гм. хорошо бы увидеть пример
Но без неё могу дать наметку — создайте рядом столбец, где через текстовую формулу вы отберете числа по группам. Например, правсимв(А1;1). А потом уже по этому столбцу работайте СЧЁТЕСЛИ.
Alex
Как распространить формулу по столбцу, чтобы диапазон в формуле оставался тот же, а критерий менялся?
admin Автор записи
СЧЁТЕСЛИ($A$1:$A$100;B1)
Анна
Пример такой, 23 12 1972 то есть это дата рождения, мне нужно чтобы суммировалось количество двоек, то есть не 2+2+два, а что их всего три двойки, то есть в ячейке конечной должно стоять 3, единиц 2, 3 7 9 по единице, такое возможно? просто я голову сломала, я самоучка, но такие формулы мне сложноваты, если Вам не очень трудно дайте пожалуйста образец формулы полностью, хотя бы на одно число, с уважением, Анна Ириковна
admin Автор записи
Представим, что ваша дата в ячейке А1.
Тогда считаем сколько двоек: = длстр(A1)-длстр(ПОДСТАВИТЬ(A1;»2″;»"))
Если же на несколько чисел, то лучше скиньте пример, как вы это хотите в конце, а то вариантов много.
Анна
спасибо, я сейчас попробую формулу … попробую сама, если совсем не выйдет тогда с большим поклоном буду просить советов еще)) С уважением, Анна Ириковна
Анна
не выходит… можно я пришлю Вам то что мне нужно на электронный адрес? просто это таблица.. мне сложно ее описать… Анна И.
Анна
Макс
Хорошая статья) Но все равно не смог разобраться со своим заданием. У меня есть 3 столбца, один это Студенты, второй Преподаватели, третий Оценки. Подскажите, как посчитать количество студентов, обучающихся у Ивановой, получивших положительные оценки? Получается вроде как 2 диапазона и 2 критерия, и я не могу понять)
admin Автор записи
Используйте СЧЁТЕСЛИМН
Maykot
Добрый день.
Помогите разобраться с диапазоном.
У меня есть ячейка А2 в которой есть текстовое значение — например «солнце».
В ячейке A3 значение «море». И т.д.
Как правильно вписать в Формулу =СУММЕСЛИ(C:C;»*солнце*»;D:D) вместо конкретного диапазона («*солнце*») содержимое ячейки A2, т.е. не =СУММЕСЛИ(C:C;»*солнце*»;D:D), а вместо «*солнце*» была ссылка на ячейку?
admin Автор записи
СУММЕСЛИ(C:C;»*»&$A$1&»*»;D:D)
Дмитрий
Александр
Здравствуйте! Подскажите пожалуйста, как в Excel представить формулу:
Скорректированная стоимость =
= Стоимость * (К1 + К2 + … + КN – (N — 1);
где:
К1, К2, КN — коэффициенты, отличные от 1
N – количество коэффициентов, отличных от 1.
Александр
Здравствуйте! Подскажите пожалуйста, как в формуле:
=СУММЕСЛИ(C3:C14;»
Михаил
Добрый вечер!
Подскажите, пожалуйста, как посчитать количество ячеек в которых указана какая-либо дата? То есть, в столбце есть ячейки с датами (разными) и есть ячейки с текстом (разным), мне нужно посчитать количество ячеек с датами.
Спасибо!
admin Автор записи
СЧЁТЕСЛИ(F26:F29;»01.01.2016″)
Пойдет?
Юлия
Добрый вечер! Подскажите, пожалуйста, формулу, считающую цифры только которые больше 8 (переработка в табеле учета рабочего времени). Вот неправильный вариант: =SUMIF(C42:V42;»>8″)+SUMIF(C42:V42)
admin Автор записи
SUMIF(C42:V42,»>8″) =СУММЕСЛИ(C1:C2;»>8″)
Не забывайте, что английская версия использует запятые между аргументами.
Д.Н.
Добрый день! спасибо за формулу массива для диапазона с несколькими критериями!
у меня, наверное, глупый вопрос, но как заменить текст {1;2;3} на ссылки на ячейки с текстовыми значениями.
то есть если я ввожу { «X»; «Y»;»Z»} считает всё верно
но при вводе {A1;A2;A3} — ошибка,
дело в фигурных скобках?)
Виталий
Спасибо. Очень помогла статья.
Денис
Добрый день. Столкнулся с такой проблемой в функции СЧЁТЕСЛИМН. При вводе 2х диапазонов все считает отлично, но при добавлении 3-го — выдает ошибку. Может ли скрываться подвох в количестве ячеек?
У меня =СЧЁТЕСЛИМН(‘очная форма обучения’!R11C13:R250C13;»да»; ‘очная форма обучения’!R11C7:R250C7;»бюджет»; ‘очная форма обучения’!R16C9:R30C9;»да»)
Без 3-го диапазона и условия все нормально.
Заранее благодарен.
admin Автор записи
Денис, выровняйте диапазоны, они все должны быть одинаковые и можете ставить до 127 наборов условий.
A.K.
Здравствуйте, помогите, пожалуйста, разобраться.
Есть два столбца: один — дата, второй — время (формат 00:00:00).
Необходимо выбрать даты соответствующие определенному периоду времени.
При этом, таких промежутков должно быть 4, т.е. каждые 6 часов.
Возможно ли это это задать одной формулой и если — да, то какой?
admin Автор записи
Добрый день.
Конечно, можно. Правда, я не понял, вам надо по датам или по часам? Две разные формулы. И как вы хотите это разбить? Чтобы промежутки помечались номерами? Типа первые 6 часов суток — это 1, вторые -2 и т.д.?
андрей
Спасибо за ранее, а вопрос такой:
дано:
много строчек, один столбец
зеленым цветом обозначены готовые пакеты документов, белым -недоработанные
содержание ячеек: разные фамилии сотрудников.
задача: как с помощью формулы сделать таблицу: сколько у каждого сотрудника готовых пакетов.
admin Автор записи
К сожалению цвет формулой не определяется. Точнее определяется, но там надо формулу пользовательскую писать
Я бы сделал так — отфильтровал по цвету зеленых и в соседнем столбце поставил «готовые», потом так же поставил «недоработанные» — белые ячейки.
Потом через СЧЁТЕСЛИ нашел все, что надо.
Кросс
Здравствуйте! Вопрос такой. Имеются 4 столбца, в которых соответственно указаны ученики (столбец А), № школы (столбец В), баллы по химии (столбец С), баллы по физике (столбец D). Надо найти кол-во учеников определённой школы (например, 5), которые набрали по физике баллов больше, чем по химии. Всего учеников 1000. Можно ли использовать какую-то одну формулу для ответа на вопрос? Пробую использовать СЧЕТЕСЛИМН, но не получается.
admin Автор записи
Нет, прежде чем использовать Счётесли, придется добавить еще один столбец, где через ЕСЛИ определить тех, у кого по физике больше баллов, чем по физике и потом уже использовать СЧЁТЕСЛИМН.
Кросс
Окей, спасибо
Александр
Добрый день! Задача в общем такая. В таблице указано построчно фио, вид спорта, разряд. Как на другой вкладке сделать таблицу автоматически считающую сколько разрядников в каждом виде спорта и каких конкретно разрядов?
Алена
Здравствуйте! Нужна помощь.
Имеется столбец с датами рождения в формате 19740815, а необходимо преобразовать в формат 15.08.1974
Спасибо за ранее.
admin Автор записи
Добрый день.
Ну самое простое — Текст по столбцам -фиксированная ширина (4-2-2) — потом добавить столбец с функцией ДАТА.
admin Автор записи
Попробуйте СЧЁТЕСЛИМН.
Надежда
Здравствуйте.
Подскажите, пожалуйста, как найти сумму в ячейках по условию:
есть таблица, в первом столбце которой указан код товара, во второй сумма по нему. Строк с одинаковым кодом может быть несколько. Надо найти общую (итоговую) сумму по каждому товару.
Заранее спасибо.
admin Автор записи
Попробуйте СУММЕСЛИ.
Дим Подскажите что исправить надо?
СЧЁТЕСЛИМН(‘Raw Data’!B2:B150;»Bucharest»;’Raw Data’!E1:E150;»06.11.2014″)
Вера
Добрый день. Подскажите, какой критерий в формуле СУММЕСЛИ нужно поставить если нужно посчитать количество ячеек содержащих цифры из диапазона, где есть и числа и буквы.
Спасибо.
admin Автор записи
Здравствуйте. Вот так просто не получится. Либо формулой массива либо сделать еще один диапазон, где через текстовые функции будут искаться цифры. И потом уже через СЧЁТЕСЛИ найдете свой результат.
Роман
Добрый вечер.
Есть столбец, в который постоянно добавляются данные, нужно в соседний столбец вставлять цифру, сколько раз до этой строчки встречались подобные данные
Анастасия
Добрый день всем!
Есть фактический график выхода сотрудников, все рабочие часы написаны в формате «09*21″ — дневная полная смена и «21*09″ — ночная полная смена.
Также есть дни с неполными сменами, которые считаются к зарплате по почасовой ставке, например «18*23″ и тд.
Формат всех ячеек — текстовый.
Необходимо, чтобы формула высчитывала по каждой строке (каждому сотруднику соответственно) количество полных смен за месяц, в идеале если она будет учитывать критерии «09*21″+»21*09″, но можно и по одному критерию, я тогда просто столбцы эти скрою и их уже объединю суммой.
Через =счетесли пробовала, в окошке формулы значение считает верно, а в самой ячейке отображает тупо написанную формулу, формат какой только не ставила — не помогает.
Пыталась заменить 09*21 на 09:00 — 21:00 в ячейках и формуле соответственно, но тоже ни в какую.
Проставляла в формуле и «09*21*», и «*09*21*» — без толку.
Если можно такую штуку делать при условии, что записано будет «09:00 — 21:00″ — вообще отлично, один месяц мне проще будет перелопатить, но дальше уже всё будет ровно)
и сразу с ходу вопрос — есть ли формула, по которой можно будет считать общее количество часов в диапазоне со всеми любыми значениями («18:00 — 23:00″, «12:45 — 13:45″ и тд), кроме вышеуказанных «09:00 — 21:00″ и «21:00 — 09:00″ либо считать все ячейки, где количество часов 12 и отдельно все, где количество часов меньше 12.
Заранее спасибо огромное, ломаю голову уже неделю!(((
admin Автор записи
Попробуйте СЧЁТЕСЛИ($A$1:A10;A10) — вставляется в ячейку B10.
Наталья
Вопрос уже задавался, но ответили вы через почту, не смогли бы повторить ответ уже здесь?
«Пример такой, 23 12 1972 то есть это дата рождения, мне нужно чтобы суммировалось количество двоек, то есть не 2+2+два, а что их всего три двойки, то есть в ячейке конечной должно стоять 3, единиц 2, 3 7 9 по единице, такое возможно? просто я голову сломала, я самоучка, но такие формулы мне сложноваты, если Вам не очень трудно дайте пожалуйста образец формулы полностью, хотя бы на одно число, с уважением, Анна Ириковна»
Вера
Добрый день! У меня в Экселе диапазон 30 ячеек,но необходимо,что бы считало сумму только первых 20 ячеек. Помогите пожалуйста!
admin Автор записи
Здравствуйте. А просто указать диапазон первых 20ти ячеек не вариант? Мне без файла непонятно, в чём сложность.

Пример 2: вычисление с помощью формул

Формула имеет вид: =ЧСТРОК(массив значений).

Затем нажать Enter.

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

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

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

при помощи функции ДЛСТР получаем количество символов в строке с исходным текстом($D$1) =(170-ДЛСТР(ПОДСТАВИТЬ($D$1 ; D3 ;"")))/ДЛСТР(D3) ;
при помощи функции ПОДСТАВИТЬ заменяем в строке с исходным текстом($D$1) все значения Банан(D3) на пусто и при помощи ДЛСТР получаем количество символов строки после этой замены =(170-155)/ДЛСТР(D3) ;
вычитаем из общего количества символов количество символов в строке после замены и делим результат на количество символов в критерии =(170-155)/5 .

Получаем число 3. Что нам и требовалось.

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

Выполняем подсчет ячеек

Способ 1: счетчик на нижней панели

Наведите курсор на строку состояния.
Нажмите правую кнопку мыши.
В появившемся меню поставьте галочку напротив пункта «Количество».

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

Выделите ячейку, в которой будет выводиться результат подсчета.
Нажмите по кнопке «Вставить функцию», расположенной слева от строки формул.
В появившемся окне Мастера функций найдите в списке строку «СЧЕТЗ», выделите ее и нажмите кнопку «ОК».
На втором этапе в параметрах «Значение» укажите область или области, в которых будет проводиться подсчет.
Нажмите «ОК».

СЧЕТЗ(значение1;значение2;...).

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

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

Способ 4: функция СЧЕТЕСЛИ

Так же, как и в предыдущих способах, установите курсор на ячейку, в которой будет выполняться функция.
Вызовите окно Мастера создания функций.
Из списка выделите строку «СЧЕТЕСЛИ» и нажмите кнопку «ОК».
Появится довольно знакомое окно, в котором необходимо ввести переменные функции. Как можно заметить, помимо диапазона, нужно указать критерий. Например, вы можете ввести «>400». Это будет означать, что считаться будут те ячейки, значения в которых больше 400.
Далее вы можете указать еще диапазоны и критерии к ним.
По окончании ввода параметров нажмите кнопку «ОК».

Способ 5: функция СЧЕТЕСЛИМН

Источник: https://passportbdd.ru/theory-reviews/podschet-kolichestva-unikalnyh-znachenii-sredi-povtoryayushchihsya-kak/

Посчитать повторяющиеся значения в excel - Все про Эксель

Может быть интересно:

Сумма прописью в Microsoft Excel | Блокирование и разблокирование внешнего содержимого в документах приложений Office