Подсчет количества вхождений значения
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 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше
Предположим, что нужно определить, сколько раз встречается конкретный текст или число в диапазоне ячеек. Например:
- Если диапазон, например A2:D20, содержит числовые значения 5, 6, 7 и 6, то число 6 встречается два раза.
- Если столбец содержит поговорки «Климов», «Наворов», «Наворов» и «Наворов», то этот столбец встречается три раза.
Подсчитать количество вхождений значения можно несколькими способами.
Подсчет количества вхождений отдельного значения с помощью функции СЧЁТЕСЛИ
Используйте функцию СЧЁТЕСЛИ, чтобы узнать, сколько раз встречается определенное значение в диапазоне ячеек.
Дополнительные сведения см. в статье Функция СЧЁТЕСЛИ.
Подсчет количества вхождений на основе нескольких критериев с помощью функции СЧЁТЕСЛИМН
Функция СЧЁТЕСЛИМН аналогична функции СЧЁТЕСЛИ с одним важным исключением: СЧЁТЕСЛИМН позволяет применить критерии к ячейкам в нескольких диапазонах и подсчитывает число соответствий каждому критерию. С функцией СЧЁТЕСЛИМН можно использовать до 127 пар диапазонов и критериев.
Синтаксис функции СЧЁТЕСЛИМН имеет следующий вид:
СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)
Дополнительные сведения об использовании этой функции для подсчета вхождений в нескольких диапазонах и с несколькими условиями см. в статье Функция СЧЁТЕСЛИМН.
Подсчет количества вхождений на основе условий с помощью функций СЧЁТ и ЕСЛИ
Предположим, вам нужно определить, сколько продавцов продало определенный товар в определенном регионе или сколько продаж было сделано конкретным продавцом. Функции ЕСЛИ и СЧЁТ можно использовать вместе. то есть сначала для проверки условия используется функция ЕСЛИ, а затем, только если функция ЕСЛИ имеет истинное положение, для подсчета ячеек используется функция СЧЁТ.
- Формулы в этом примере должны быть введены как формулы массива.
- Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива.
- Если вы открыли эту книгу в Excel для Windows или Excel 2016 для Mac и хотите изменить формулу или создать похожую, нажмите F2,а затем нажмите CTRL+SHIFT+ВВОД, чтобы формула возвращала нужные результаты. В более ранних версиях Excel для Mac используйте клавиши +SHIFT+ВВОД.
Дополнительные сведения об этих функциях см. в статьях Функция СЧЁТ и Функция ЕСЛИ.
Подсчет количества вхождений нескольких текстовых и числовых значений с помощью функций СУММ и ЕСЛИ
В следующих примерах функции ЕСЛИ и СУММ используются вместе. Функция ЕСЛИ сначала проверяет значения в определенных ячейках, а затем, если возвращается значение ИСТИНА, функция СУММ складывает значения, удовлетворяющие условию.
Примечания: Формулы, приведенные в этом примере, должны быть введены как формулы массива.
- Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива.
- Если вы открыли эту книгу в Excel для Windows или Excel 2016 для Mac и хотите изменить формулу или создать похожую, нажмите F2,а затем нажмите CTRL+SHIFT+ВВОД, чтобы формула возвращала нужные результаты. В более ранних версиях Excel для Mac используйте клавиши +SHIFT+ВВОД.
Функция выше означает, что если диапазон C2:C7 содержит значения Шашков и Туманов, то функция СУММ должна отобразить сумму записей, в которых выполняется условие. Формула найдет в данном диапазоне три записи для «Шашков» и одну для «Туманов» и отобразит 4.
Функция выше означает, что если ячейка D2:D7 содержит значения меньше 9 000 ₽ или больше 19 000 ₽, то функция СУММ должна отобразить сумму всех записей, в которых выполняется условие. Формула найдет две записи D3 и D5 со значениями меньше 9 000 ₽, а затем D4 и D6 со значениями больше 19 000 ₽ и отобразит 4.
Функция выше сообщает, что если в функции D2:D7 есть счета для записи «Иванов» на сумму менее 9 000 рублей, то функция СУММ должна отобразить сумму записей, в которых условие должно быть выполнены. Формула найдет ячейку C6, которая соответствует условию, и отобразит 1.
Подсчет времени висячего значения с помощью с помощью pivotTable
Вы можете использовать совокупные значения для отображения итогов и подсчета количества уникальных значений. Сводная таблица — это интерактивный способ быстрого суммирования больших объемов данных. Вы можете использовать ее для развертывания и свертывания уровней представления данных, чтобы получить точные сведения о результатах и детализировать итоговые данные по интересующим вопросам. Кроме того, можно перемещать строки в столбцы или столбцы в строки («сводить» их) для просмотра количества вхождений значения в сводной таблице. Рассмотрим пример электронной таблицы «Продажи», в которой можно подсчитать количество значений продаж для разделов «Гольф» и «Теннис» за конкретные кварталы.
- Введите данные в электронную таблицу Excel.
- Выделите диапазон A2:C8
- Выберите Вставка >Сводная таблица.
- В диалоговом окне «Создание сводной таблицы» установите переключатель Выбрать таблицу или диапазон, а затем — На новый лист и нажмите кнопку ОК. Пустая сводная таблица будет создана на новом листе.
- В области «Поля сводной таблицы» выполните одно из указанных ниже действий.
- Перетащите элемент Спорт в область Строки.
- Перетащите элемент Квартал в область Столбцы.
- Перетащите элемент Продажи в область Значения.
- Повторите третье действие. Имя поля Сумма_продаж_2 отобразится и в области «Сводная таблица», и в области «Значения». На этом этапе область «Поля сводной таблицы» будет выглядеть так:
- В области Значения щелкните стрелку раскрывающегося списка рядом с полем Сумма_продаж_2 и выберите пункт Параметры поля значений.
- В диалоговом окне Параметры поля значений выполните указанные ниже действия.
- На вкладке Операция выберите пункт Количество.
- В поле Пользовательское имя измените имя на Количество.
- Нажмите кнопку ОК.
Сводная таблица отобразит количество записей для разделов «Гольф» и «Теннис» за кварталы 3 и 4, а также показатели продаж.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Как подсчитать количество вхождений в Excel
Вы можете использовать функции =UNIQUE() и =COUNTIF() для подсчета количества вхождений различных значений в столбце Excel.
В следующем пошаговом примере показано, как это сделать.
Шаг 1: введите данные
Во-первых, давайте введем названия для списка баскетбольных команд в столбце A:
Шаг 2. Найдите уникальные значения в столбце
Далее воспользуемся функцией =UNIQUE() , чтобы создать список всех уникальных названий команд в столбце A:
Эта функция по умолчанию создает массив уникальных значений.
Шаг 3: подсчитайте появление каждого уникального значения
Далее воспользуемся следующей формулой для подсчета количества вхождений каждого уникального имени команды:
=COUNTIF( $A$2:$A$15 , D2 )На следующем снимке экрана показано, как использовать эту формулу на практике:
Обратите внимание, что мы просто копируем и вставляем формулу из ячейки E2 в каждую из оставшихся ячеек в столбце E.
Из вывода мы видим:
- Название команды «Шершни» встречается 2 раза в столбце А.
- Название команды «Мавс» встречается 3 раза в столбце А.
- Название команды «Шпоры» встречается 3 раза в столбце А.
- Название команды «Рокетс» встречается 1 раз в столбце А.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Как в Эксель сравнить два столбца на совпадения и найти расхождения
Как в Эксель сравнить два столбца? Напишите в каждой строке интересующих вертикальных секций формулу «ЕСЛИ». После создания формулы для 1-й строки ее можно протянуть / копировать на остальные строчки. Для проверки содержания одинаковых строк используйте формулу =ЕСЛИ(A2=B2; “Совпадают”; “”), для отличий — =ЕСЛИ(A2<>B2; “Не совпадают”; “”). Ниже подробно рассмотрим, как сравнить сведения для двух и более секциях, а также поговорим о выборе результата.
Как сравнить столбцы в Эксель
Одна из особенностей приложения — возможность в Эксель сравнить столбцы (два и более) на факт отличий и различий, а после вывести результаты в виде подсвечивания цветом. Ниже рассмотрим, как правильно сделать эту работу для разного количества столбцов.
Два
При рассмотрении вопроса, как сравнить два столбца в Excel на совпадения / отличия, нужно сравнить информацию в каждой отдельной строчке на отличия и одинаковые параметры. Сделать такой шаг можно с помощью «ЕСЛИ». Формула вставляется в каждую строчку в соседнем столбике около таблицы Эксель, где размещены основные параметры. После создания записи для 1-й строки ее можно протянуть и копировать на другие строчки.
Если вас интересует, как сравнить столбцы в Excel на совпадения, используйте запись с соответствующей командой — =ЕСЛИ(A2=B2; “Совпадают”; “”). Бывают ситуации, когда необходимо сравнить два столбика и найти отличия. В таком случае используйте иную запись — =ЕСЛИ(A2<>B2; “Не совпадают”; “”). По желанию можно выполнить проверку на совпадения / отличия между двумя секциями с помощью одной формулы. Для этого используется один из следующих вариантов:
- =ЕСЛИ(A2=B2; “Совпадают”; “Не совпадают”);
- =ЕСЛИ(A2<>B2; “Не совпадают”; “Совпадают”).
При этом в таблице выводится информация о наличии совпадений или отличий.
Если стоит задача в Экселе сравнить столбцы с учетом регистра, применяется другая запись. Используйте — =ЕСЛИ(СОВПАД(A2,B2); “Совпадает”; “Уникальное”)
Альтернативный вариант
Существует еще один способ, как в Эксель сравнить два столбца на совпадения. Задача в том, чтобы определить повторяющиеся параметры в обоих столбцах. Здесь можно использовать упомянутую ранее функцию ЕСЛИ или СЧЕТЕСЛИ. Формула имеет следующий вид =ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; “Нет совпадений в столбце B”; “Есть совпадения в столбце В”). После ввода формулы производится проверка в строчке «В» на факт совпадений с данными в строке «А». При наличии фиксированного количества строк в Эксель можно указать определенный диапазон, к примеру, $B2:$B20.
Больше двух
По-иному обстоит ситуация, если нужно сравнить в столбцы в Excel, когда их больше двух. Программа позволяет сравнивать данные в нескольких столбиках по ряду критериев: находить строчки с одинаковыми значениями во всех или в двух столбцах. Если их больше двух, используйте функции ЕСЛИ и И. При этом сама формула в Эксель приобретает следующий вид — =ЕСЛИ(И(A2=B2;A2=C2); “Совпадают”; ” “). Как только программе удалось сравнить данные, в последней строке выводится информация о совпадении.
Если столбцов в Эксель более двух, рекомендуется использовать опцию СЧЕТЕСЛИ и ЕСЛИ. При этом сама команда приобретает следующий вид — =ЕСЛИ(СЧЁТЕСЛИ($A2:$C2;$A2)=3;”Совпадают”;” “).
Поиск совпадений в двух и более столбцах
Бывают ситуации, когда в Эксель необходимо сравнить несколько столбцов, но найти совпадения хотя бы в двух из них. В таком случае применяются опции ИЛИ и ЕСЛИ. Для решения задачи делается следующая запись в специальной графе =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “).
В случае, когда в таблице много больше двух столбцов, формула может быть слишком большой, ведь в ней нужно указывать параметры совпадения для каждой вертикальной секции таблицы. Чтобы оптимизировать процесс, нужно использовать другую функцию СЧЕТЕСЛИ. При этом полная запись будет иметь следующий вид: =ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; “Уникальная строка”; “Не уникальная строка”).
В этой формуле условно выделяется две части. В первой СЧЕТЕСЛИ позволяет рассчитать число столбцов в строке с параметром А2 в ячейке, а вторая вычисляет это количество в таблице с параметром из В2. При равенстве результата «0» можно говорить, что в каждой ячейке столбца у этой сроки находятся уникальные параметры. При этом формула для Эксель выдает результат «Уникальная строка», а при их отсутствии «Не уникальная …».
Как вывести результат в Эксель
Выше мы уже рассмотрел, как в Excel сравнить данные для двух или более столбцов. В указанных выше случаях информация выводится в последней строчке таблицы и показывает, совпадают данные или различаются в зависимости от задачи и используемой команды. Но существует еще один путь — выделение совпадений одним цветом в двух и более столбцах. В таком случае информация выглядит более наглядно.
Для этого в Эксель сделайте следующее:
- Выделите вертикальные секции с данными, которые нужно сравнить.
- Войдите во вкладку «Главная» на панели инструментов и жмите «Условное форматирование».
- Кликните на пункт «Правила выделения ячеек» и «Повторяющиеся значения».
- В появившемся диалоговом окне выберите слева пункт «Повторяющиеся», а в правом списке укажите, каким цветом будут выделяться данные. Жмите на кнопку «ОК».
- После этого в выделенной колонке подсвечиваются цветом совпадения.
При желании можно найти и выделить совпадающие в Эксель строки. Для этого сделайте следующее:
- С правой стороны от таблицы сделайте дополнительный столбик, где напротив каждой строчки с информацией установите формулу. Последняя должна объединять все параметры строки в одну ячейку. В дополнительной колонке будут видны объединенные сведения.
- Выделите область с информацией в дополнительной колонке.
- В разделе «Главная» жмите на «Условное форматирование», а после «Правила выделения ячеек».
- Кликните на «Повторяющиеся значения».
- Во всплывающем окне выберите слева в перечне «Повторяющиеся», а справа — укажите цвет, который будет использоваться для выделения параметров.
Теперь вы знаете, как в Эксель сравнить два столбика и более, а после вывести результаты цветом или в последней колонке. В комментариях поделитесь, удалось ли вам сделать работу, и какие еще методы можно использовать.
Как посчитать количество совпадений
как мне посчитать общее количество ячеек, если у меня совпадают значения в двух столбцах, но при этом нужно чтоб он одинаковые совпадения учитывал только 1 раз. то есть 10 раз совпало, а он посчитал как один и потом в конце получил сумму всех таких совпадений.
Лучшие ответы ( 1 )
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:
Посчитать количество совпадений в столбце
Как посчитать кол-во совпадений в столбце. Аналог Excel СчетЕсли. мокрокодер код написал.Поиск совпадений и действия в зависимости от наличия совпадений
Попытаюсь вкратце описать суть. Приходит список позиций с ценами. Необходимо проверить артикул на.Как посчитать колличество вхождений в интервал, используя Stream api
Всем доброго времени суток. Подскажите пожалуйста! У меня есть около 1.000.000.000 значений, все.Как мне посчитать количество совпадений в поле
Привет! есть вопрос, как мне правильно составить вопрос к базе данных mysq чтобы запрос.Часто онлайн
854 / 570 / 260
Регистрация: 09.01.2017
Сообщений: 1,923Калабала, что с чем должно совпадать? ничего не понятно.
Приложите файл, и укажите как было и что должно получиться.Добавлено через 2 минуты
По какому критерию искать совпадения, в вашем примере кроме выделенных есть еще совпадения их не надо искать?Регистрация: 04.06.2020
Сообщений: 15если значение в столбце «04-4015» и значение в соседнем столбце равно единице, то посчитать эти совпадения, но при этом нужно посчитать так, что если таких совпадений было выявлено 10 или 20 без разницы сколько раз, чтоб оно считалось единожды. и по итогу нужно получить сумму сколько у меня таких совпадений.
например 04-4015 и 1 совпало 10 раз
04-4016 и 1 совпало 5 раз
04-4018 и 1 совпало 3 разаитог по сумме совпадений равен 3
документ не могу приложить это рабочий файл с кучей херни удалять долго.
Часто онлайн
854 / 570 / 260
Регистрация: 09.01.2017
Сообщений: 1,923Сообщение было отмечено Калабала как решение
Решение
Калабала,
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
Sub wert() Dim myRange As Range, myCell As Range, myCollection As New Collection, _ myElement As Variant, i, rr, itog As Long lLastRow = Sheets("Лист7").Cells.SpecialCells(xlLastCell).Row Set myRange = Sheets("Лист7").Range("A1:A" & lLastRow) On Error Resume Next For Each myCell In myRange rr = Cells(myCell.Row, 2) If rr = 1 Then myCollection.Add CStr(myCell.Value), CStr(myCell.Value) End If Next myCell On Error GoTo 0 For Each myElement In myCollection i = i + 1 Sheets("Лист7").Cells(i, 4) = myElement Next myElement itog = Cells(Rows.Count, 4).End(xlUp).Row Range("D" & itog + 1) = itog Range("E" & itog + 1) = "Всего совпадений" End Sub