Посчитать количество вхождений в столбце excel
Как посчитать количество вхождений за год / квартал / месяц / неделю в Excel?
Обычно мы можем применить функцию СЧЁТЕСЛИ для подсчета элементов с критериями в Excel. Но знаете ли вы, как считать даты только по годам / месяцам / дням недели? В этой статье будут представлены несколько методов подсчета количества вхождений в год / квартал / месяц / день недели в Excel.
- Подсчитайте количество вхождений в год / месяц с помощью формул
- Подсчитайте количество вхождений в год / месяц / квартал / час с помощью сводной таблицы
- Подсчитайте количество вхождений в год / месяц / будний день / день с помощью Kutools for Excel
Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!
Подсчитайте количество вхождений в год / месяц с помощью формул
Вы можете применять формулы для подсчета количества вхождений в год или месяц в Excel. Пожалуйста, сделайте следующее:
Выберите пустую ячейку, в которую вы поместите результат подсчета, и введите формулу =SUMPRODUCT((MONTH($A$2:$A$24)=F2)*(YEAR($A$2:$A$24)=$E$2)) в него, а затем перетащите дескриптор автозаполнения этой ячейки до нужного вам диапазона. Смотрите скриншот:
Ноты:
(1) В приведенной выше формуле A2: A24 столбец даты, который вы будете считать за год / месяц, E2 это указанный год, F2 это указанный месяц, и вы можете изменить их по своему усмотрению.
(2) Для подсчета за месяц без учета года вы можете применить эту формулу = СУММПРОИЗВ (1 * (МЕСЯЦ ($ A $ 2: $ A $ 24) = F2)).
Легко выбирайте и подсчитывайте любые ячейки даты, если даты находятся между двумя датами в Excel
Если у вас установлен Kutools for Excel, вы можете применить его Выберите специальные ячейки утилита для быстрого выбора и подсчета любых ячеек дат, если даты находятся в определенном диапазоне дат. Полнофункциональная бесплатная 30-дневная пробная версия!
Подсчитайте количество вхождений в год / месяц / квартал / час с помощью сводной таблицы
Фактически, мы также можем создать сводную таблицу для подсчета количества вхождений в год, месяц, квартал, часы, секунды и т. Д. Пожалуйста, сделайте следующее:
1. Выберите таблицу, с которой вы будете работать, и нажмите Вставить > PivotTable. Смотрите скриншот:
2. В открывшемся диалоговом окне «Создание сводной таблицы» укажите целевой диапазон, в который будет помещена сводная таблица, и щелкните значок OK кнопку.
В нашем случае мы проверяем параметр Существующий рабочий лист, выбираем ячейку E1 на активном листе и щелкаем OK кнопка. Смотрите скриншот:
2. Теперь появится панель «Поля сводной таблицы». Пожалуйста, перетащите Время столбец в Ряды раздел и перетащите Сумма столбец в Ценности разделов.
Затем создается сводная таблица, как показано ниже:
3. Щелкните правой кнопкой мыши заголовок столбца значений (в нашем случае «Сумма суммы») и выберите Обобщить ценности по > Количество из контекстного меню.
4. Щелкните правой кнопкой мыши любую ячейку в столбце «Ярлыки строк» и выберите группы из контекстного меню.
5. Теперь в открывшемся диалоговом окне «Группировка» выберите один или несколько критериев группировки из By и нажмите OK кнопка. Смотрите скриншот выше.
В нашем случае мы выбираем оба Месяцев и Лет в By коробка. И вы можете выбрать подходящие критерии в зависимости от ваших потребностей.
И теперь он подсчитал количество вхождений в год и месяц в сводной таблице. См. Снимок экрана ниже:
Внимание: Когда мы группируем метки строк по месяцам и годам, Лет метка будет добавлена над Время в Ряды на панели Поля сводной таблицы. Для подсчета количества вхождений в месяц вы можете переместить Лет этикетка под Время метка. См. Снимок экрана ниже:
Подсчитайте количество вхождений в год / месяц / будний день / день с помощью Kutools for Excel
Если у вас установлен Kutools for Excel, вы можете применить его утилиты для подсчета количества вхождений в год / месяц / день недели / день, или год и месяц, или месяц и день и т. Д.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
1. Выберите столбец даты и щелкните Кутулс > Формат > Применить форматирование даты. Смотрите скриншот:
2. В открывшемся диалоговом окне «Применить форматирование даты» выберите указанное форматирование даты слева. Форматирование даты и нажмите Ok кнопка. Смотрите скриншот выше:
В соответствии с критериями подсчета выберите указанное форматирование даты, например месяц, год, день недели, день, месяц и день и т. Д. В нашем случае мы выбираем один из Месяц форматирование, например Mar.
3. Продолжайте выбирать столбец даты и щелкните Кутулс > К фактическому для преобразования дат в названия месяцев.
4. Выберите диапазон, который вы будете считать, и нажмите Кутулс > Content > Расширенные ряды комбинирования. Смотрите скриншот:
5. В открывшемся диалоговом окне Advanced Combine Rows сделайте следующее (см. Снимок экрана выше):
(1) Выберите Время и нажмите первичная Ключ;
(2) Выберите Сумма столбец и щелкните Рассчитать > Количество;
(3) Выберите другие столбцы и укажите правила комбинирования или правила расчета. В нашем случае мы выбираем Фрукты столбец и щелкните Сочетать > Запятая.
(4) Щелкните значок Ok кнопку.
А теперь подсчитывает количество вхождений в месяц. См. Снимок экрана ниже:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Демо: подсчитайте количество повторов в год / месяц / будний день / день
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше
Предположим, вам нужно узнать количество уникальных значений в диапазоне, который содержит повторяющиеся значения. Например, если столбец содержит:
Значения 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.
Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.
Аннотация
Эта статья содержит и описывает формулы, которые вычисляют следующее:
- Количество случаев появления текстовой строки в диапазоне ячеек.
- Количество случаев появления символа в одной ячейке.
- Количество случаев появления символа в диапазоне ячеек.
- Количество слов (или текстовых строк), разделенных символом в ячейке.
Дополнительная информация
Формула для подсчета количества вхождений текстовой строки в диапазоне
=SUM(LEN(range)-LEN(SUBSTITUTE(range,"text","")))/LEN("text")
Где диапазон — диапазон ячеек, а "text" заменяется конкретной текстовой строкой, которую вы хотите посчитать.
Вышеупомянутая формула должна быть введена в виде формулы массива. Чтобы ввести формулу в качестве массива в Excel для Windows, нажмите CTRL+SHIFT+ENTER. Чтобы ввести формулу в качестве массива в Excel для Macintosh, нажмите COMMAND+RETURN.
Формула должна быть разделена на длину строки текста, поскольку сумма длины символа диапазона уменьшается кратно каждому вхождению текстовой строки. Эта формула может заменить все более поздние формулы в этой статье, кроме формулы для подсчета количества слов в ячейке.
Пример 1. Подсчет количества вхождений текстовой строки в диапазоне
Запустите Excel, а затем откройте новую книгу
Введите следующие сведения на Листе 1:
Значение ячейки A8 составляет 4, потому что текст "яблоко" появляется четыре раза в диапазоне.
Формула для подсчета количества происшествий одного символа в одной ячейке
=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. Подсчет количества слов, разделенных пространством в ячейке
Чтобы подсчитать количество слов в ячейке, где слова разделены пробелом, выполните следующие действия:
Запустите Excel, а затем откройте новую книгу
Введите следующие сведения на Листе 1:
Формула в ячейке A2 возвращает значение 4, чтобы отразить, что строка содержит четыре слова, разделенных пробелами. Если слова разделены несколькими пробелами или если слова начинаются или заканчиваются пробелами, это не имеет значения. Функция TRIM удаляет дополнительные пробелы и пробелы в начале и в конце в тексте ячейки.
В Excel можно также использовать макрос для подсчета количества случаев появления определенного символа в ячейке или диапазоне ячеек.
Ссылки
Дополнительные сведения о подсчете количества символов см. в следующей статье базы знаний Майкрософт:
89794 Как использовать Visual Basic для приложений для подсчета случаев появления символа в выделении в Excel
Как посчитать количество вхождений слова в столбец в Excel?
Если у вас есть данные столбца, которые включают несколько повторяющихся значений, как показано на скриншоте ниже, и теперь вы хотите подсчитать количество вхождений определенного слова в этом столбце. С помощью этого руководства я расскажу о некоторых приемах, которые помогут быстро решить эту проблему в Excel.
Подсчитайте количество появлений слова в столбце с формулами
В Excel я могу рассказать вам несколько простых формул для быстрого подсчета вхождений слова в столбец.
Выберите ячейку рядом со списком, в котором вы хотите подсчитать количество вхождений слова, а затем введите эту формулу = СЧЁТЕСЛИ (A2: A12; «Джуди») в него, затем нажмите Enter, и вы можете узнать количество появлений этого слова.
Примечание:
1. Вы также можете использовать эту формулу = СУММ (ЕСЛИ (A2: A12 = «Джуди»; 1,0)) чтобы получить результат подсчета, с этой формулой нужно нажать Shift + Ctrl + Enter одновременно, чтобы вернуть результат.
2. В двух приведенных выше формулах A2: A12 - это диапазон списка, из которого вы хотите подсчитать конкретное слово, и "Джуди" это слово, которое вы хотите подсчитать, когда оно встречается.
Подсчитайте количество вхождений слова в столбце с помощью Kutools for Excel
Если у вас есть Kutools for Excel, вы можете использовать его Выбрать определенные ячейки утилита, позволяющая не только быстро подсчитать количество появлений определенного слова, но и выбрать все эти дубликаты.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите список, из которого вы хотите посчитать слово, и нажмите Кутулс > Выберите > Выбрать определенные ячейки. Смотрите скриншот:
2. Затем в появившемся диалоговом окне отметьте Ячейка вариант под Тип выбора раздел, затем перейдите к выбору Равно из списка под Конкретный тип раздел и введите слово, которое хотите найти, в текстовое поле рядом. Смотрите скриншот:
3. Нажмите Ok, и другое диалоговое окно появляется, чтобы напомнить вам количество выбранных ячеек (то есть количество вхождений указанного слова), и в то же время все это слово в списке выбрано. Смотрите скриншот:
4. Нажмите OK , чтобы закрыть диалоговые окна, и слова также выбираются, и вы также можете просмотреть подсчет этих слов в строке состояния.
Работы С Нами Kutools for ExcelАвтора Выбрать определенные ячейки, вы можете выбирать ячейки на основе нескольких критериев.
Подсчитайте количество вхождений слова в одной ячейке с помощью Kutools for Excel
Если вы хотите подсчитать количество вхождений слова в одной ячейке, как показано на скриншоте ниже, вы можете использовать Kutools for ExcelАвтора Считайте, сколько раз появляется слово утилита для быстрого подсчета количества вхождений слова из одной ячейки.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите ячейку, в которой будет отображаться результат подсчета, и нажмите Кутулс > Формула Помощник > Статистический > Подсчитайте количество слова. Смотрите скриншот:
2. Затем в Помощник по формулам диалог, сделайте как показано ниже:
1) Выберите ячейку, от которой нужно посчитать слово до Текст текстовое окно;
2) Введите слово, которое вы хотите подсчитать, в Word текстовое окно. Смотрите скриншот:
3. Нажмите Ok. Теперь результат подсчета отображается в выбранной ячейке.
In Kutools for ExcelАвтора Формула group, вы можете легко и быстро выполнять множество вычислений в Excel.
Если у вас есть список данных на листе с уникальными значениями и повторяющимися значениями, и вы хотите не только подсчитывать частоту повторяющихся значений, но также хотите знать порядок появления повторяющихся значений. В Excel функция СЧЁТЕСЛИ может помочь вам подсчитать повторяющиеся значения.
- Подсчитайте частоту дублирования в Excel
- Подсчитайте порядок появления дубликатов в Excel
- Подсчитайте и выберите все повторяющиеся значения в столбце с помощью Kutools for Excel
- Подсчитайте вхождения каждого дубликата в столбце с помощью Kutools for Excel
Подсчитайте частоту дублирования в Excel
В Excel вы можете использовать функцию СЧЁТЕСЛИ для подсчета дубликатов.
Выделите пустую ячейку рядом с первыми данными вашего списка и введите эту формулу = СЧЁТЕСЛИ ($ A $ 2: $ A $ 9; A2) (диапазон $ A $ 2: $ A $ 9 указывает список данных, а A2 стоит ячейка, с которой вы хотите подсчитать частоту, вы можете изменить их по своему усмотрению), затем нажмите Enter, и перетащите маркер заполнения, чтобы заполнить нужный столбец. Смотрите скриншот:
Функции: Если вы хотите подсчитать количество дубликатов во всем столбце, используйте эту формулу = СЧЁТЕСЛИ (A: A; A2) ( Колонка A указывает столбец данных, а A2 стоит ячейка, с которой вы хотите посчитать частоту, можете менять их как вам нужно).
Быстрый выбор и подсчет всех повторяющихся / уникальных значений из столбца в Excel
В общем, мы можем легко удалить дубликаты из списка, Данные > Удалить дубликаты в Excel. Но как выбрать повторяющиеся / уникальные значения или подсчитать их из столбца? А что, если выбрать или подсчитать дубликаты / уникальные, кроме первого дубликата? Попробуйте Kutools для Excel Выберите повторяющиеся и уникальные ячейки утилита!
Бесплатная пробная версия 30 дней сейчас! Купить!
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Подсчитайте порядок появления дубликатов в Excel
Но если вы хотите подсчитать порядок появления дубликатов, вы можете использовать следующую формулу.
Выделите пустую ячейку рядом с первыми данными вашего списка и введите эту формулу = СЧЁТЕСЛИ ($ A $ 2: $ A2; A2) (диапазон $ A $ 2: $ A2 указывает список данных, а A2 стоит ячейка, в которой вы хотите посчитать порядок, вы можете изменить их по своему усмотрению), затем нажмите Enter, и перетащите маркер заполнения, чтобы заполнить нужный столбец. Смотрите скриншот:
Подсчитайте и выберите все дубликаты в столбце с помощью Kutools for Excel
Иногда вам может потребоваться подсчитать и выбрать все дубликаты в указанном столбце. Вы можете легко сделать это с помощью Kutools for Excel's Выберите дубликаты и уникальные ячейки утилита.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1. Выберите столбец или список, в котором вы будете подсчитывать все дубликаты, и нажмите кнопку Кутулс > Выберите > Выберите дубликаты и уникальные ячейки.
2. В открывшемся диалоговом окне Select Duplicate & Unique Cells установите флажок Дубликаты (кроме 1-го) Вариант или Все дубликаты (включая 1-й) вариант, как вам нужно, и щелкните Ok кнопку.
Затем вы увидите диалоговое окно, в котором показано, сколько дубликатов выбрано, и в то же время дубликаты выбраны в указанном столбце.
Примечание: Если вы хотите подсчитать все дубликаты, включая первый, вам нужно проверить Все дубликаты (включая 1-й) в диалоговом окне «Выбор повторяющихся и уникальных ячеек».
3, Нажмите OK кнопку.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Подсчитайте вхождения каждого дубликата в столбце с помощью Kutools for Excel
Kutools для Excel Расширенные ряды комбинирования Утилита может помочь пользователям Excel подсчитать количество вхождений каждого элемента в столбце (в нашем случае - столбец с фруктами), а затем легко удалить повторяющиеся строки на основе этого столбца (столбец с фруктами), как показано ниже:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1. Выберите таблицу, содержащую столбец, в котором вы будете считать каждый дубликат, и нажмите Кутулс > Content > Расширенные ряды комбинирования.
2. В Advanced Combine Rows выберите столбец, в котором вы будете считать каждый дубликат, и нажмите Основной ключ, затем выберите столбец, в который вы поместите результаты подсчета, и нажмите Рассчитать > Количество, а затем нажмите OK кнопка. Смотрите скриншот:
И теперь он подсчитал появление каждого дубликата в указанном столбце. Смотрите скриншот:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Демо: подсчет повторяющихся значений в столбце в Excel от Kutools for Excel
Читайте также: