Добрый день, уважаемый читатель!

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

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

Используя функцию СЧЁТЕСЛИ

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

=СЧЁТЕСЛИ($B$2:$B$11;B15) В первом аргументе «диапазон» $B$2:$B$11, указываем тот , в котором и будет производиться подсчёт повторяющихся данных. Важно! Указывать случайный диапазон данных недопустимо. Его особенностью является то, что он может быть только диапазоном ячеек или ссылкой на определённую ячейку.

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

Дополнительная информация! Кроме прямого указания поиска данных, функция СЧЁТЕСЛИ умеет работать с символами подстановки. Таких знаков используют двух видов «?» и «*», применять их возможно только при работе с символами. Знак «*» позволяет заменить абсолютно любое количество значений, а Знак «?» производит замену только одного символа.

Для работы с числовыми значениями необходимо применять знаки операторов сравнения: «>», «<», «<>» и «=». К примеру, для подсчёта числовых значений больше «нуля» прописывайте «>0», а для подсчёта непустых ячеек нужен указать «<>».

Используем функцию СЧЁТЕСЛИМН

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

В моём примере я добавлю категорию продаж по городам и используя формулы соберу необходимые мне повторы:

=СЧЁТЕСЛИМН($B$2:$B$11;B14;$C$2:$C$11;C14)
Замечу, что орфография функции абсолютно аналогична предыдущей функции СЧЁТЕСЛИ, разница состоит только в их количестве. В нашем примере их два, но функция умеет работать и со 127 диапазонами.

Работаем с функцией ДЛСТР

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

=(ДЛСТР($B$2) -ДЛСТР(ПОДСТАВИТЬ($B$2;B5;"")))/ДЛСТР(B5)
Итак, используя , мы считаем, сколько же символов содержится в ячейке «$B$2» и «B5», результат будет «71». А потом с помощью производим замену текущего значения на «пусто», получаем результат «47». Следующим действием отнимаем от общего количества символов наш остаток «71-47=24» и делим на количество символов в одном значении «24/6=4», как результат получаем сколько раз в строке, встречается необходимый результат… Ответ: 4. (Это результат рассматривая только первую строку поиска).

С помощью функций VBA

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

Для начала вам нужно запустить VBA и вставить новый модуль с помощью команд «Insert» — «Module» .
В созданное окно модуля вы вставляете код функции:

Function GetRepeat(sTxt As String, sCntWord As String) GetRepeat = (Len(sTxt) - Len(Replace(sTxt, sCntWord, ""))) / Len(sCntWord) End Function

Function GetRepeat (sTxt As String , sCntWord As String ) GetRepeat = (Len (sTxt ) - Len (Replace (sTxt , sCntWord , "" ) ) ) / Len (sCntWord ) End Function

После всего этого вызываете «Диспетчер функций» в панели управления или с помощью Ctrl+F3 и в категории «Определённые пользователем» у вас появляется новая необходимая функция.
Используем функцию на ячейке стандартным образом с помощью формулы:

=GetRepeat ($B$2;B8) , где:

А на этом у меня всё! Был очень рад помочь и поделится информацией возможностью подсчитывать повторения в Excel. Если есть чем дополнить статью, напишите это в комментариях. Жду ваши лайки, это лучший стимул видеть пользу моих статей!

Каждый наш день - это счет в банке, а деньги на нем - это наше время. Здесь нет бедных и богатых, у каждого есть по 24 часа.
Кристофер Райс

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

Значения 5, 6, 7 и 6 выводятся в виде трех уникальных значений: 5, 6 и 7.

Значения "Кирилл", "Сергей", "Сергей", "Сергей", результат - это два уникальных значения - "Кирилл" и "Сергей".

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

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

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

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

Для выполнения этой задачи используйте сочетание функций Если , сумм , Частота , ПОИСКПОЗ и ДЛСТР .

Назначьте значение 1 каждому из истинных условий с помощью функции ЕСЛИ .

Добавьте итог с помощью функции сумм .

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

Возвращает расположение текстового значения в диапазоне с помощью функции ПОИСКПОЗ . Возвращаемое значение затем используется в качестве аргумента функции FREQUENCY , чтобы можно было оценивать соответствующие текстовые значения.

Поиск пустых ячеек с помощью функции Len . Длина пустых ячеек равна 0.

Примечания:

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

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

Описание функций

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

Функция ПОИСКПОЗ осуществляет поиск определенного элемента в диапазоне ячеек и возвращает относительное расположение этого элемента в диапазоне. Например, если в диапазоне a1: A3 содержатся значения 5, 25 и 38, функция формула = Match (25; a1: A3; 0) возвращает число 2, так как значение 25 является вторым элементом диапазона.

Функция ДЛСТР возвращает число символов в текстовой строке.

Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом другой функции. Например, функция сумм (a1: A5) складывает все числа, содержащиеся в ячейкАх от a1 до A5.

Функция Если возвращает одно значение, если указанное условие имеет значение true, и другое, если условие имеет значение false.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте

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

Способ 1: указатель в строке состояния

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

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

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

Способ 2: использование функции

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

ЧСТРОК(массив)

Её можно вбить в любую пустую ячейку на листе, а в качестве аргумента «Массив» подставить координаты диапазона, в котором нужно произвести подсчет.

Для вывода результата на экран достаточно будет нажать кнопку Enter .

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

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

Способ 3: применение фильтра и условного форматирования

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

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

SEO-анализ текста включает в себя:

Счетчик символов, подсчет количества знаков и слов в тексте онлайн

С помощью данного онлайн-сервиса можно определить число слов в тексте, а также количество символов с пробелами и без них.

Определение ключей и семантического ядра текста

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

Определение процента водности текста

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

• до 15% - естественное содержание «воды» в тексте;
• от 15% до 30% - превышенное содержание «воды» в тексте;
• от 30% - высокое содержание «воды» в тексте.

Определение процента заспамленности текста

Процент заспамленности текста отражает количество поисковых ключевых слов в тексте. Чем больше в тексте ключевых слов, тем выше его заспамленность:

• до 30% - отсутствие или естественное содержание ключевых слов в тексте;
• от 30% до 60% - SEO-оптимизированный текст. В большинстве случаев поисковые системы считают данный текст релевантным ключевым словам, которые указаны в тексте.
• от 60% - сильно оптимизированный или заспамленный ключевыми словами текст.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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