Как подсчитывать повторения в Excel?

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

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

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

  1. С помощью функции СЧЁТЕСЛИ;
  2. С помощью функции СЧЁТЕСЛИМН;
  3. С помощью функции ДЛСТР;
  4. С помощью функций VBA.

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

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

Kak poschitat povtory 3 1 Как подсчитывать повторения в Excel?

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

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

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

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

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

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

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

Kak poschitat povtory 4 Как подсчитывать повторения в Excel?

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

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

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

Kak poschitat povtory 5 Как подсчитывать повторения в Excel?

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

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

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

Kak poschitat povtory 6 Как подсчитывать повторения в Excel?

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

Функция ДЛСТР

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

Синтаксис функции:

  • текст – это прописанный вручную текст или ссылка на ячейку которая содержит текстовое значение.

Tekstovie function part1 3 Текстовые функции в Excel. Часть №1

Пример применения: Дополнительно ознакомится с функцией можно в статье «ТОП 10 функций Excel для SEO специалиста».

2. Использование расширенного фильтра для удаления дубликатов

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

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

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

Повторение в Excel, функция ПОВТОР

В Майкрософт Эксель есть функция, польза которой для вас пока неочевидна. Она позволяет повторить выбранную строку заданное количество раз. Это функция ПОВТОР. Её синтаксис такой: =ПОВТОР(Строка_для_повторения;Количество_повторов). Например, чтобы получить строку «АгуАгуАгу», можно записать формулу:

То есть, вы даёте команду Excel повторить «Агу» три раза.

Что же такого полезного в этой формуле? Я использую её тогда, когда нужно заполнить пустое место в ячейке определенными символами. Например, строка в ячейке А1 должна содержать ровно 15 символов, но вы вводите в нее лишь 11. Чтобы заполнить оставшиеся 4 звёздочками, запишите формулу:

Функция ПОВТОР в Excel

Применение функции ПОВТОР

Эта формула содержит оператор объединения строк «&». Программа возьмёт строку из ячейки А1 и объединит его с результатом вычисления функции ПОВТОР. В свою очередь, ПОВТОР использует функцию ДЛСТР для определения длины строки в ячейке А1 и вычислит, что нужно добавить еще 4 звёздочки.

Таким образом, формула сама будет определять, сколько звёзд нужно добавить к текстовой строке, чтобы всего было 15 символов. Если вы введете строку длиной более 15 символов, функция ПОВТОР должна будет повторить звёздочку отрицательное количество раз. В результате, она вернёт ошибку #ЗНАЧ!, тогда формулу нужно будет модифицировать.

Уверен, вы можете столкнуться с задачей, требующей многократного повторения, и теперь вы вооружены, чтобы решить её в два счёта!

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

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

Текстовая функция ПОВТОР() в Microsoft Excel

Функция ПОВТОР() в Microsoft Excel повторяет текст заданное число раз.

Синтаксис команды

  • Аргумент1 — текст, который нужно повторить.
  • Аргумент2 — положительное число, определяющее, сколько раз требуется повторить текст.

Текстовая функция ПОВТОР() в Excel.

Другие статьи из категории «Microsoft Excel»

  • Текстовая функция ПОДСТАВИТЬ() в Microsoft Excel
  • Текстовая функция НАЙТИ() в Microsoft Excel
  • Текстовая функция ПОИСК() в Microsoft Excel
  • Текстовая функция СЦЕПИТЬ() в Microsoft Excel
  • Текстовая функция ЗАМЕНИТЬ() в Microsoft Excel
  • Текстовая функция ПРАВСИМВ() в Microsoft Excel
  • Текстовая функция ЛЕВСИМВ() в Microsoft Excel
  • Текстовая функция СИМВОЛ() в Microsoft Excel
  • Текстовая функция СЖПРОБЕЛЫ() в Microsoft Excel
  • Текстовая функция ПСТР() в Microsoft Excel
  • Текстовая функция СОВПАД() в Microsoft Excel
  • Текстовая функция ДЛСТР() в Microsoft Excel
  • Текстовая функция ПРОПНАЧ() в Microsoft Excel
  • Текстовая функция ПРОПИСН() в Microsoft Excel
  • Текстовая функция СТРОЧН() в Microsoft Excel

Добавить комментарий Отменить ответ

Реклама от Google
Реклама от Google

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

Повторение формулы в Excel

​127​итог должен быть​

​ =ПОВТОР(“«текст2”;ЛОЖЬ) вернет пустое​

​ возвращена ссылка на​ к возвращаемому ей​ и пароля пользователей.​I​ дней до следующего​

​ 100:​​ber_times​REPT​ звездочек и трех​
​В этой статье описаны​для вставки строк​
​Тип данных: Другой​ ЛОЖЬ. Если лог_выражение​
​ выражение, принимающее значения​127​123​ значение «».​

​ ячейку с исходным​​ результату благодаря символу​ Если сайт «запомнил»​– MOD(B3,5) или​ отпуска, можно использовать​
​В ячейке B3 введите​(число_повторений) будет равно​(ПОВТОР) повторяет текстовую​ дефисов (*-).​ синтаксис формулы и​ на листе:​

​Формула:​​ равно ЛОЖЬ, а​ ИСТИНА или ЛОЖЬ.​Нина куликова​123​Если в качестве второго​ текстом.​ & (амперсанд) будут​
​ пользователя в форме​ ОСТАТ(B3;5).​
​ функцию​ 100.​ 0, результатом окажется​ строку заданное количество​*-*-*-​ использование функции​Вставьте строку, например, через​=СЧЁТЕСЛИ ($F$1:$F$1000;F1)=1​ значение_если_ложь пусто (то​ Например, A10=100 —​: просто выделяете ячейку​123​ аргумента было передано​Адаптируем формулу для остальных​ добавлены 4 последних​
​ входа, то поля​В сочетании с​REPT​В ячейке C3 такую​ пустая строка.​ раз.​=ПОВТ(“-“;10)​ПОВТОР​ контекстное меню (Это​В окне “Сообщение​ есть после значение_если_истина​ это логическое выражение;​ и за нижний​123​ числовое значение, содержащее​ ячеек и в​ символа из текста,​ Логин и Пароль​VLOOKUP​(ПОВТОР) для ведения​
​ формулу:​Если Вам нужно просто​Создавать забавные эффекты, такие​Возвращает последовательность из 10​в Microsoft Excel.​ меню вызывается правой​ об ошибке” прописываем​ стоит точка с​ если значение в​ правый уголок тянете​123​ дробную часть, она​ итоге получим:​ содержащегося в ячейке​ заполняются автоматически. В​(ВПР) Вы можете​ подсчета заработанных очков​=REPT(“n”,B3/5)​ заполнить ячейку символом,​ как в предыдущем​ дефисов (-).​Повторяет текст заданное число​ клавишей мыши при​ предупреждающую информацию. напр​ запятой с последующей​ ячейке A10 равно​

Читайте также:  Примеры формул с использованием функций ИЛИ И ЕСЛИ в Excel

Функция ПОВТОР

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

ПОВТОР(текст;число_повторений)

Текст — повторяемый текст.

Число_повторений — положительное число, определяющее, сколько раз нужно повторить текст.

Пример

Если «число_повторений» равно 0 (нулю), то функция ПОВТОР возвращает “” (пустой текст).

Если «число_повторений» не целое, то оно усекается.

Результат функции ПОВТОР не должен превышать 32 767 знаков, в противном случае ПОВТОР возвращает сообщение об ошибке #ЗНАЧ!.

Еще про Excel.

Сочетания клавиш в окне справки

F6 – Переключение между разделом справки и областью (Область окна. Часть окна документа, окруженная границей и отделенная от других частей вертикальными и горизонтальными полосами. ) вкладок «Содержание», «Мастер ответов» и «Указатель».
TAB – Выделение следующего скрытого текста или гиперссылки, либо стрелки Показать все или Скрыть все в начале раздела.
SHIFT+TAB – Выделение предыдущего скрытого текста или гиперссылки, либо кнопки Просмотреть в обозревателе в начале веб-статьи Microsoft Office.
ENTER – Выполнение действия, связанного с выделенной кнопкой Показать все, Скрыть все, скрытым текстом или гиперссылкой.
ALT+М – Открытие меню Параметры для доступа к любой команде панели инструментов (Панель инструментов. Панель с кнопками и параметрами, которые используются для выполнения команд. Для отображения панелей инструментов используется диалоговое окно Настройка (выберите в меню Вид команду Панели инструментов, а затем — команду Настройка). Для просмотра дополнительных кнопок нажмите кнопку Другие кнопки в конце панели инструментов.) справки.
ALT+М, затем К – Скрытие или отображение вкладок «Содержание», «Мастер ответов» и «Указатель».
ALT+М, затем З – Отображение предыдущего просмотренного раздела.
ALT+М, затем В – Отображение следующего раздела в последовательности ранее просмотренных разделов.
ALT+М, затем Д – Возврат к заданной домашней странице.
ALT+М, затем О – Остановка открытия раздела в окне справки (полезно для остановки загрузки веб-страницы).
ALT+М, затем А – Открытие диалогового окна Microsoft Internet Explorer Свойства обозревателя, в котором можно изменять параметры отображения.
ALT+М, затем Б – Обновление раздела (полезно при просмотре веб-страниц).
ALT+М, затем Е – Печать всех разделов книги или только выделенного раздела.
ALT+F4 – Закрытие окна справки.

Примечание. При использовании служебной программы интерпретации изображения (Служебные программы интерпретации изображения. Вспомогательные средства для людей с нарушениями зрения или способности к обучению (такими как дислексия). Эти вспомогательные средства делают отображаемые на экране сведения доступными, преобразуя их в речь или шрифт Брайля.) или другого вспомогательного средства (Вспомогательные средства. Служебные программы, облегчающие использование компьютера людьми с нарушениями подвижности, зрения и слуха. Примерами вспомогательных средств являются программы интерпретации изображения, программы распознавания речи и экранные клавиатуры. ) вопросы для поиска справки лучше всего вводить не в фигурной выноске помощника по Microsoft Office и не в поле Задать вопрос, а на вкладке Мастер ответов окна справки.

Использование областей Содержание, Указатель и Мастер ответов

Нажмите клавишу F6 для перехода из раздела справки в области (Область окна. Часть окна документа, окруженная границей и отделенная от других частей вертикальными и горизонтальными полосами. ) вкладок «Содержание», «Мастер ответов» и «Указатель».
CTRL+TAB – Переход на следующую вкладку.
ALT+О – Переход на вкладку Содержание.
ALT+A – Переход на вкладку Мастер ответов.
ALT+У – Переход на вкладку Указатель.
ENTER – Открытие выделенной главы или раздела справки.
СТРЕЛКА ВНИЗ – Выделение следующей главы или раздела справки.
СТРЕЛКА ВВЕРХ – Выделение предыдущей главы или раздела справки.
SHIFT+F10 – Открытие контекстного меню.

Примечание. При использовании служебной программы интерпретации изображения (Служебные программы интерпретации изображения. Вспомогательные средства для людей с нарушениями зрения или способности к обучению (такими как дислексия). Эти вспомогательные средства делают отображаемые на экране сведения доступными, преобразуя их в речь или шрифт Брайля.) или другого вспомогательного средства (Вспомогательные средства. Служебные программы, облегчающие использование компьютера людьми с нарушениями подвижности, зрения и слуха. Примерами вспомогательных средств являются программы интерпретации изображения, программы распознавания речи и экранные клавиатуры. ) вопросы для поиска справки лучше всего вводить не в фигурной выноске помощника по Office и не в поле Задать вопрос, а на вкладке Мастер ответов окна справки.

Использование области «Раздел справки»

Нажмите клавишу F6 для перехода из области (Область окна. Часть окна документа, окруженная границей и отделенная от других частей вертикальными и горизонтальными полосами. ) вкладок «Содержание», «Мастер ответов» и «Указатель» в открытый раздел справки.
ALT+СТРЕЛКА ВПРАВО – Переход к следующему разделу справки.
ALT+СТРЕЛКА ВЛЕВО – Переход к предыдущему разделу справки.
TAB – Выделение следующего скрытого текста или гиперссылки, либо стрелки Показать все или Скрыть все в начале раздела.
SHIFT+TAB – Выделение предыдущего скрытого текста или гиперссылки, либо кнопки Просмотреть в обозревателе в начале веб-статьи Microsoft Office.
ENTER – Выполнение действия, связанного с выделенной кнопкой Показать все, Скрыть все, скрытым текстом или гиперссылкой.
СТРЕЛКА ВВЕРХ или СТРЕЛКА ВНИЗ – Прокрутка экрана справки в направлении конца или начала раздела.
PAGE UP или PAGE DOWN – Ускоренная прокрутка к началу или к концу раздела справки.
HOME или END – Переход в начало или в конец раздела справки.
CTRL+P – Печать текущего раздела справки.
CTRL+A – Выделение всего раздела справки.
CTRL+C – Копирование выделенных элементов в буфер обмена.
SHIFT+F10 – Открытие контекстного меню (Контекстное меню. Меню, содержащее список команд, относящихся к конкретному объекту. Для вызова контекстного меню щелкните объект правой кнопкой мыши или нажмите клавиши SHIFT+F10.).

Функция ПОВТОР в Excel

Функция ПОВТОР Excel повторяет символы заданное количество раз. Например, = REPT (‘x’, 5) возвращает ‘xxxxx’.

  • текст – Текст для повторения.
  • number_times – Количество повторов текста.

Функция ПОВТОР повторяет символы указанное количество раз. Например, чтобы повторить «x» пять раз, вы можете использовать следующую формулу:

Входы в REPT могут быть переменными. В показанном примере REPT настроен на повторение строки в столбце B с использованием счетчика в столбце C. Формула в D5:

Функцию REPT можно разумно использовать в более сложных формулах. См. Ссылки ниже для конкретных примеров.

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

Функция =СЧЁТСИМВЛ(ТЕКСТ, СИМВОЛ) имеет два обязательных аргумента:

  • ТЕКСТ — Строка, в которой необходимо посчитать количество символов.
  • ШАБЛОН — Символ, который необходимо посчитать.

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

Теперь к практике. Далее привожу множество примеров использования данной функции.

Посчитать количество пробелов в тексте.

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

Посчитать количество символов не обращая внимание на регистр.

Посчитать количество слов в тексте.

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

Посчитать количество всех символов в строке за исключением пробелов.

Метод 4: оператор СЧЕТЕСЛИ

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

Синтаксис СЧЕТЕСЛИ типичен для всех операторов, работающих с условиями:

=СЧЕТЕСЛИ(диапазон;критерий)

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

Синтаксис функции ПОВТОР и её особенности

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

ПОВТОР(текст; число_повторений)

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

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

Следует учитывать следующие особенности функции:

  • Если число_повторений равно 0, функция ПОВТОР возвращает пустую строку (“”).
    На первый взгляд это бесполезная операция (действительно, зачем «повторять» что-то 0 раз?). Однако на практике такие случаи запросто могут возникать, особенно если вспомнить о том, что в качестве аргумента формулы может быть использована другая формула.
  • Если «число_повторений» не является целым числом, то оно усекается, то есть остаётся только целая часть.
    Это и понятно — не имеет смысла повторять что-то «полтора раза», так что Excel тут нам помогает избежать необрабатываемых ошибок вида «#ЗНАЧ!». Тем не менее, при использовании составных формул, когда число повторений рассчитывается по другой функции, стоит помнить о том, что может быть ошибка в результатах из-за приведения аргумента к целому числу.
  • Результат функции ПОВТОР не должен превышать 32 767 знаков, в противном случае функция ПОВТОР возвращает значение ошибки #ЗНАЧ!.
    Тут без комментариев. Столько раз выполнять повтор никому не нужно, так что это вполне разумное ограничение. Просто следите за тем, чтобы не повторять слишком длинный текст.
Читайте также:  Примеры функции ВПР в Excel для выборки значений по условию

Функция ПОВТОР в Excel добавляется в ячейку вручную или через Мастер функций.

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

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

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

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

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

Но если по каким-то причинам сводная не Ваш вариант – в Excel имеется функция СЧЁТЕСЛИ (COUNTIF) , при помощи которой все это можно сделать тоже буквально за секунды. Если количество повторений каждого наименования необходимо вывести в столбец В таблицы, а сами наименования расположены в столбце А:
=СЧЁТЕСЛИ( $A$2:$A$30 ; A2 )
=COUNTIF( $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″)

Подсчитать числа, которые больше нуля: =СЧЁТЕСЛИ( $A$2:$A$30 ;”>0″)
Подсчитать количество непустых ячеек: =СЧЁТЕСЛИ( $A$2:$A$30 ;”<>“)

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

Так же не могу не написать про небольшую особенность функции СЧЁТЕСЛИ (а так же СЧЁТЕСЛИМН , СУММЕСЛИ , СУММЕСЛИМН и им подобных) – данные функции всегда стремятся преобразовать все значения аргументов к типам(в отличии от той же ВПР , которая к типам относится очень бережно и ничего не преобразует). Что это значит. Если у нас в ячейке записано число 23 – оно будет воспринято как число. Если тоже число будет записано как текст – “23” , то функция преобразует его сначала в число, а потом уже будет работать с ним. Т.е. и 23 и “23” у нас будут считаться одинаковым значением. С одной стороны это хорошо, но иногда такое поведение может сыграть злую шутку. Например, у Вас в ячейках расположены некие номер счетов, длина которых более 15-ти символов и могут иметь ведущие нули:
000 34889913131323455
00 34889913131323455
000 34889913131323477
как видно, первые два числа почти одинаковые, но у первого числа три ведущих нуля спереди, а второго – два. И это разные счета. А третий счет вообще отличается на последние цифры. Но СЧЁТЕСЛИ после преобразования все три этих значения будет считать как число 348899131313234 00 и если записать функцию так: =СЧЁТЕСЛИ( $A$1:$A$3 ; A1 ) , то она вернет значение 3. Особо обращаю внимание на тот факт, что все числа после 15-го знака будут преобразованы в нули. Эти особенности всегда необходимо учитывать при использовании данных функций, чтобы не попасть в неловкую ситуацию, когда подсчет будет некорректным.

Еще один вариант подсчета значений. Бывают случаи, когда список расположен вовсе не в таблице, а в одной ячейке( $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. Что нам и требовалось.

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

Но тут есть и более каверзная ситуация – когда у нас диапазон ячеек, в каждой из которых наше слово может встречаться более одного раза. И подсчитать надо ВСЕ повторения. Диапазон для подсчета повторений у нас будет в ячейках A1:A10 . Слово для подсчета повторений запишем в ячейку B1 (там будет все тоже слово ” банан “):

Базируясь на формуле выше можно написать такую:
=СУММПРОИЗВ((ДЛСТР( A1:A10 )-ДЛСТР(ПОДСТАВИТЬ( A1:A10 ; B1 ;””)))/ДЛСТР( B1 ))

И простая функция пользователя, которая так же подсчитывает повторения внутри ячейки:

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

Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA( Alt + F11 ) -создать стандартный модуль(InsertModule) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций( Ctrl + F3 ), отыскав её в категории Определенные пользователем (User Defined Functions) .
Синтаксис функции:
=GetRepeat( $D$1 ; D3 )
sTxt – текст, в котором подсчитываем кол-во вхождения.
sCntWord – текст для подсчета. Может быть символом или словом.

Пример Подсчета повторений.xls (70,5 KiB, 11 929 скачиваний)

Поиск дубликатов при помощи встроенных фильтров Excel

Организовав данные в виде списка, Вы можете применять к ним различные фильтры. В зависимости от набора данных, который у Вас есть, Вы можете отфильтровать список по одному или нескольким столбцам. Поскольку я использую Office 2010, то мне достаточно выделить верхнюю строку, в которой находятся заголовки, затем перейти на вкладку Data (Данные) и нажать команду Filter (Фильтр). Возле каждого из заголовков появятся направленные вниз треугольные стрелки (иконки выпадающих меню), как на рисунке ниже.

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

Читайте также:  Создание базы данных в Excel и функции работы с ней

После настройки фильтра Вы можете удалить дубликаты строк, подвести промежуточные итоги или дополнительно отфильтровать данные по другому столбцу. Вы можете редактировать данные в таблице так, как Вам нужно. На примере ниже у меня выбраны элементы XP и XP Pro.

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

Расширенный фильтр для поиска дубликатов в Excel

На вкладке Data (Данные) справа от команды Filter (Фильтр) есть кнопка для настроек фильтра – Advanced (Дополнительно). Этим инструментом пользоваться чуть сложнее, и его нужно немного настроить, прежде чем использовать. Ваши данные должны быть организованы так, как было описано ранее, т.е. как база данных.

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

Теперь, когда критерий настроен, выделяем любую ячейку наших данных и нажимаем команду Advanced (Дополнительно). Excel выберет весь список с данными и откроет вот такое диалоговое окно:

Как видите, Excel выделил всю таблицу и ждёт, когда мы укажем диапазон с критерием. Выберите в диалоговом окне поле Criteria Range (Диапазон условий), затем выделите мышью ячейки L1 и L2 (либо те, в которых находится Ваш критерий) и нажмите ОК. Таблица отобразит только те строки, где в столбце Home / Visitor стоит значение H, а остальные скроет. Таким образом, мы нашли дубликаты данных (по одному столбцу), показав только домашние встречи:

Это достаточно простой путь для нахождения дубликатов, который может помочь сохранить время и получить необходимую информацию достаточно быстро. Нужно помнить, что критерий должен быть размещён в ячейке отдельно от списка данных, чтобы Вы могли найти его и использовать. Вы можете изменить фильтр, изменив критерий (у меня он находится в ячейке L2). Кроме этого, Вы можете отключить фильтр, нажав кнопку Clear (Очистить) на вкладке Data (Данные) в группе Sort & Filter (Сортировка и фильтр).

Предположим у вас есть столбец с фамилиями. Примерно такой, см. столбец А:

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

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

Решение одной строкой: Фильтр уникальных значений + СЧЕТЕСЛИ.

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

Копируем куда-нибудь все из столбца А, например в столбец F (затем мы удалим оттуда лишние строки). Выделяем данные, выбираем в меню: Данные – Удалить дубликаты.

Теперь считаем количество совпадений с помощью функции СЧЕТЕСЛИ (COUNTIF для английской версии)

=СЧЁТЕСЛИ (где нужно искать; что нужно найти)

В нашем случае диапазон A$1:A$25, а фамилию лучше не писать в кавычках, а указать ячейку нового списка, т.е. F1. Сразу поставьте знак $, чтобы диапазон данных не «съезжал» при растягивании формулы на все значения:

=СЧЁТЕСЛИ(A$1:A$25;F1)

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

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

Удалять дубликаты в оригинального столбца нельзя, тогда обновятся и результаты подсчета. Поэтому выделяем снова колонку А, выбираем в меню: Данные – Сортировка и фильтр – Дополнительно. В меню ставим галочку «Только уникальные записи».

Конкретно в нашем примере Excel ругнется всплывающим окошком, так как у нас нет заголовка таблицы, жмем там ОК. Если заголовок у колонка А есть, то проблем нет вообще. Результат:

Удалите лишнюю строку (в нашем случае там два Андреева).

Повторяющиеся строки в столбцах MS Excel и как с ними бороться

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

1. Удаление повторяющихся значений в Excel

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

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

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

ИТОГ: Данная функция предназначена для удаления записей, которые полностью дублируют строки в таблице.

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

2. Расширенный (дополнительный) фильтр для удаления дубликатов

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

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

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

3. Условное форматирование в Excel

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

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

ИТОГ: Для больших массивов информации данный метод не дает четкой картины. Все фамилии авторов книг, у которых есть повторения, выделены одинаковым цветом.

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

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

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

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

Ссылка на основную публикацию
Adblock
detector