Посчитать сколько раз встречается слово excel
Перейти к содержимому

Посчитать сколько раз встречается слово excel

  • автор:

Подсчет количества вхождений значения

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2007 Excel для Mac 2011 Еще. Меньше

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

  • Если диапазон, например A2:D20, содержит числовые значения 5, 6, 7 и 6, то число 6 встречается два раза.
  • Если столбец содержит «Бьюкенен», «Додсворт», «Додсворт» и «Додсворт», «Dodsworth» возникает три раза.

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

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

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

Примеры с функцией СЧЁТЕСЛИ

Дополнительные сведения см. в статье Функция СЧЁТЕСЛИ.

Подсчет количества вхождений на основе нескольких критериев с помощью функции СЧЁТЕСЛИМН

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

Синтаксис функции СЧЁТЕСЛИМН имеет следующий вид:

СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)

Пример с функцией СЧЁТЕСЛИМН

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

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

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

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

    Примеры вложенных функций СЧЁТ и ЕСЛИ

    Дополнительные сведения об этих функциях см. в статьях Функция СЧЁТ и Функция ЕСЛИ.

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

    В следующих примерах функции ЕСЛИ и СУММ используются вместе. Функция ЕСЛИ сначала проверяет значения в определенных ячейках, а затем, если возвращается значение ИСТИНА, функция СУММ складывает значения, удовлетворяющие условию.

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

    • Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива.
    • Если вы открыли эту книгу в Excel для Windows или Excel 2016 для Mac и более поздних версий и хотите изменить формулу или создать аналогичную формулу, нажмите клавишу F2, а затем нажмите клавиши CTRL+SHIFT+ВВОД, чтобы формула возвращала ожидаемые результаты. В более ранних версиях Excel для Mac используйте клавиши

    Пример 1. Функции СУММ и ЕСЛИ, вложенные в формулу

    Функция выше означает, что если диапазон C2:C7 содержит значения Шашков и Туманов, то функция СУММ должна отобразить сумму записей, в которых выполняется условие. Формула найдет в данном диапазоне три записи для «Шашков» и одну для «Туманов» и отобразит 4.

    Пример 2. Функции СУММ и ЕСЛИ, вложенные в формулу

    Функция выше означает, что если ячейка D2:D7 содержит значения меньше 9 000 ₽ или больше 19 000 ₽, то функция СУММ должна отобразить сумму всех записей, в которых выполняется условие. Формула найдет две записи D3 и D5 со значениями меньше 9 000 ₽, а затем D4 и D6 со значениями больше 19 000 ₽ и отобразит 4.

    Пример 3. Функции СУММ и ЕСЛИ, вложенные в формулу

    Приведенная выше функция говорит, что если D2:D7 имеет счета для Бьюкенен менее чем за 9000 долл. США, сумма должна отобразить сумму записей, в которой выполняется условие. Формула найдет ячейку C6, которая соответствует условию, и отобразит 1.

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

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

    Пример данных для сводной таблицы

      Введите данные в электронную таблицу Excel.

    Поля сводной таблицы

  • Выделите диапазон A2:C8
  • Выберите Вставка >Сводная таблица.
  • В диалоговом окне «Создание сводной таблицы» установите переключатель Выбрать таблицу или диапазон, а затем — На новый лист и нажмите кнопку ОК. Пустая сводная таблица будет создана на новом листе.
  • В области «Поля сводной таблицы» выполните одно из указанных ниже действий.
    1. Перетащите элемент Спорт в область Строки.
    2. Перетащите элемент Квартал в область Столбцы.
    3. Перетащите элемент Продажи в область Значения.
    4. Повторите третье действие. Имя поля Сумма_продаж_2 отобразится и в области «Сводная таблица», и в области «Значения». На этом этапе область «Поля сводной таблицы» будет выглядеть так:

    Диалоговое окно

  • В области Значения щелкните стрелку раскрывающегося списка рядом с полем Сумма_продаж_2 и выберите пункт Параметры поля значений.
  • В диалоговом окне Параметры поля значений выполните указанные ниже действия.
    1. На вкладке Операция выберите пункт Количество.
    2. В поле Пользовательское имя измените имя на Количество.

    Сводная таблица отобразит количество записей для разделов «Гольф» и «Теннис» за кварталы 3 и 4, а также показатели продаж.

    Сводная таблица

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

    Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

    Описание формул для подсчета случаев появления текста, символов и слов в Excel

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

    • Количество случаев появления текстовой строки в диапазоне ячеек.
    • Количество случаев появления символа в одной ячейке.
    • Количество случаев появления символа в диапазоне ячеек.
    • Количество слов (или текстовых строк), разделенных символом в ячейке.

    Дополнительная информация

    Формула для подсчета количества вхождений текстовой строки в диапазоне

    =SUM(LEN(range)-LEN(SUBSTITUTE(range,»text»,»»)))/LEN(«text»)

    Где диапазон — диапазон ячеек, а «text» заменяется конкретной текстовой строкой, которую вы хотите посчитать.

    Вышеупомянутая формула должна быть введена в виде формулы массива. Чтобы ввести формулу в качестве массива в Excel для Windows, нажмите CTRL+SHIFT+ENTER. Чтобы ввести формулу в качестве массива в Excel для Macintosh, нажмите COMMAND+RETURN.

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

    Пример 1. Подсчет количества вхождений текстовой строки в диапазоне
    1. Запустите Excel, а затем откройте новую книгу
    2. Введите следующие сведения на Листе 1:
    A1: Fruit A2: apple,apple A3: orange A4: apple,orange A5: grape A6: orange,grape A7: grape, apple A8: =SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,"apple","")))/LEN("apple") 

    Формула для подсчета количества происшествий одного символа в одной ячейке

    =LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,»a»,»»))

    Где cell_ref ссылка на ячейку, и «а» заменяется символом, который вы хотите посчитать.

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

    Пример 2. Подсчет количества появлений символа в одной ячейке.

    Используйте те же данные из предыдущего примера; предполагая, что вы хотите подсчитать количество появлений символа «p» в A7. Введите следующую формулу в ячейку A9:

    Значение ячейки A9 составляет 3, потому что символ «p» появляется три раза в A7.

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

    =SUM(LEN(range)-LEN(SUBSTITUTE(range,»a»,»»)))

    Где range — это диапазон нужной ячейки, а «а» заменяется символом, который вы хотите сосчитать.

    Вышеупомянутая формула должна быть введена в виде формулы массива. Чтобы ввести формулу в качестве массивной формулы в Excel, нажмите на CTRL+SHIFT+ENTER.

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

    Используйте те же данные из предыдущего примера; предполагая, что вы хотите подсчитать количество символа «p» в A2:A7. В ячейку А10 введите следующую формулу:

    Вышеупомянутая формула должна быть введена в виде формулы массива. Чтобы ввести формулу в качестве массивной формулы в Excel, нажмите на CTRL+SHIFT+ENTER.

    Значение ячейки A10 составляет 11, потому что символ «p» появляется 11 раз в A2:A7.

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

    =IF(LEN(TRIM(cell_ref))=0,0,LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,char,»»))+1)

    Где cell_ref – это ссылка на ячейку, а char – это символ, разделяющий слова.

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

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

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

    1. Запустите Excel, а затем откройте новую книгу
    2. Введите следующие сведения на Листе 1:

    A1: The car drove fast A2: =IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1) 

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

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

    Ссылки

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

    89794 Как использовать Visual Basic для приложений для подсчета случаев появления символа в выделении в Excel

    Обратная связь

    Были ли сведения на этой странице полезными?

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

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

    Вы можете использовать функции =UNIQUE() и =COUNTIF() для подсчета количества вхождений различных значений в столбце Excel.

    В следующем пошаговом примере показано, как это сделать.

    Шаг 1: введите данные

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

    Шаг 2. Найдите уникальные значения в столбце

    Далее воспользуемся функцией =UNIQUE() , чтобы создать список всех уникальных названий команд в столбце A:

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

    Шаг 3: подсчитайте появление каждого уникального значения

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

    =COUNTIF( $A$2:$A$15 , D2 ) 

    На следующем снимке экрана показано, как использовать эту формулу на практике:

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

    Из вывода мы видим:

    • Название команды «Шершни» встречается 2 раза в столбце А.
    • Название команды «Мавс» встречается 3 раза в столбце А.
    • Название команды «Шпоры» встречается 3 раза в столбце А.
    • Название команды «Рокетс» встречается 1 раз в столбце А.

    Дополнительные ресурсы

    В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

    Посчитать сколько раз встречается слово excel

    Есть столбец значений.
    Значения в нем — это слова.
    Есть одинаковые слова
    Как посчитать сколько каких слов в этом столбце?
    Стандартными фильтрами что-то не получается. Может формулой?))
    спасибо)
    ПРикрепляю пример.

    Прикрепленные файлы

    • ПРимер Подсчет.xlsx (8.81 КБ)

    Пользователь
    Сообщений: 7809 Регистрация: 15.02.2016
    11.01.2017 21:09:44
    Добрый вечер.
    =СЧЁТЕСЛИ($A$1:$A$15;A21)

    Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл

    Пользователь
    Сообщений: 60 Регистрация: 09.12.2014
    11.01.2017 21:29:17

    Спасибо, но что делать если слова в ячейке А21 нет?

    Нужно просто подсчитать количество одинаковых слов в столбце-диапазоне А1-А15.
    В примере я привел как нужно чтобы было по итогу, а эта формула похоже берет значение из А21 и считает сколько раз встречается в А1-А15

    Пользователь
    Сообщений: 7809 Регистрация: 15.02.2016
    11.01.2017 21:37:29

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

    Цитата
    Limyh написал:
    Нужно просто подсчитать количество одинаковых слов

    Прикрепленные файлы

    • ПРимер Подсчет-1.xlsx (9.75 КБ)

    Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл

    Пользователь
    Сообщений: 60 Регистрация: 09.12.2014
    11.01.2017 22:42:44

    не, не то.
    Попробую объяснить. Будем считать, что есть только диапазон значений А1:А15

    Вот нужно в этом столбце посчитать, сколько раз каждое слово встречается.
    В данном диапазоне в ячейке А1 — слово Table.
    Всего в этом диапазоне данное слово встречается 4 раза.
    Нужно, чтобы формула это посчитала и рядом в ячейке к примеру B2 поставила значение 4.

    Ячейка А2 — Food
    В диапазоне встречается 1 раз. Тоже, чтобы формула подставила значение — 1.

    Я так думаю, что можно формулой делать так:
    Забиваем в ячейке B1 формулу, которая берет значение А1 и сверяет, сколько раз это слово встречается в диапазоне А1:А15 и число совпадений и есть результат формулы.
    В ячейке B2 соответственно формула берет значение А2 и также до низу считает.

    Проблема мне видится одна.
    Если взять мой изначальный пример, первые 6 раз — формула посчитает нормально, НО формула в ячейке B7 когда будет брать для себя строчку А7 — тут будет тоже слово, что и в А1, получается слово Table посчитается не 1 раз((

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *