Как быстро подсчитать только первые вхождения значений в Excel?
При работе с большими наборами данных в Excel часто встречаются списки, содержащие как дублирующиеся, так и уникальные значения. Иногда необходимо подсчитать только первое вхождение каждого значения, игнорируя все последующие повторы. Например, в списке, где некоторые значения встречаются несколько раз, может потребоваться подсчёт, учитывающий исключительно первое появление каждого из них — как показано на скриншоте ниже. Такой подход позволяет быстро определить количество различных записей по их «первым вхождениям», независимо от того, сколько раз каждое значение повторяется далее. Как эффективно реализовать эту задачу в Excel?
- Подсчёт первого вхождения элементов с помощью формулы
- Подсчёт первого вхождения элементов с помощью Kutools для Excel
- Подсчёт первого вхождения элементов с помощью макроса на VBA
Подсчёт первого вхождения элементов с помощью формулы
Один из простых способов подсчёта первого вхождения каждого значения — использовать формулу Excel. Этот метод выявляет первые записи каждого значения в наборе данных и позволяет суммировать их для получения итогового результата.
Сценарий и преимущества: Это решение идеально подходит, если вы работаете со столбцами данных и хотите создать динамическую формулу, которая автоматически обновляется при изменении исходных данных. Оно не требует надстроек или специальных разрешений, поэтому подойдёт большинству пользователей. Единственное условие — нужно добавить на лист дополнительный столбец.
Чтобы начать, выполните следующие шаги:
1. Выберите пустую ячейку рядом с первым значением вашего набора данных (например, если данные находятся в диапазоне A1:A10, выберите ячейку B1) и введите следующую формулу:
=(COUNTIF($A$1:$A1,$A1)=1)+0Нажмите Enter, а затем перетащите маркер заполнения вниз по всему столбцу данных, чтобы применить формулу ко всем строкам. В результатах отобразится «1» для строк с первым вхождением соответствующего значения и «0» — во всех остальных случаях. Пример показан на скриншотах ниже:
Совет: В этой формуле $A$1 обозначает первую ячейку вашего диапазона данных (при необходимости измените её), а $A1 — текущую строку. Если ваши данные начинаются не с A1, скорректируйте ссылки соответствующим образом. Комбинация абсолютных и относительных ссылок обеспечивает корректную работу формулы при её копировании вниз.
2. Чтобы получить общее количество первых вхождений, выберите другую пустую ячейку (например, под новым столбцом с формулой) и введите:
=SUM(B1:B10)Нажмите Enter, чтобы получить итоговый результат. Диапазон B1:B10 должен соответствовать ячейкам, в которые вы ввели предыдущую формулу. При необходимости скорректируйте ссылки на ячейки в зависимости от объёма ваших данных или расположения формулы.
Дополнительные примечания: Метод с использованием формулы обеспечивает автоматическое обновление подсчёта при изменении, добавлении или удалении значений. Учитывайте, что при изменении структуры диапазона данных (например, при вставке новых строк) может потребоваться расширить диапазоны формул. Для автоматического распространения формул рекомендуется преобразовать данные в таблицу Excel.
Подсчёт первого вхождения элементов с помощью Kutools для Excel
Если у вас установлен Kutools для Excel, вы можете воспользоваться его утилитой Выбрать дубликаты/уникальные ячейки, чтобы значительно упростить работу — особенно с большими или сложными наборами данных. Этот инструмент не только подсчитывает первые вхождения значений, но и позволяет удобно их выделять.
После бесплатной установкиKutools для Excel выполните следующие действия:
1. Выделите все ячейки в диапазоне, где нужно подсчитать первые вхождения (например, A1:A10), затем нажмите Kutools > Выделить > Выбрать дубликаты/уникальные ячейки на ленте. См. скриншот ниже:
2. В диалоговом окне Выбрать дубликаты/уникальные ячейки выберите параметр Уникальные значения (включая первый дубликат) в разделе Правило. При желании вы также можете задать отличительный цвет фона для выделенных ячеек или изменить их цвет шрифта, чтобы они лучше выделялись.
3. После нажатия кнопки OKпоявится диалоговое окно с количеством первых вхождений в указанном вами диапазоне. Итоговый результат включает как уникальные значения, так и первые вхождения дубликатов. См. скриншот для справки:
4. Нажмите OK, чтобы закрыть диалоговые окна. Первые вхождения каждого элемента будут выделены и, при необходимости, подсвечены — это упростит их идентификацию на листе.
Применимые сценарии и предостережения: Метод Kutools идеально подходит для пользователей, которые регулярно работают с большими таблицами или хотят мгновенно визуально выделять результаты. Он исключает ошибки формул и сводит к минимуму ручной ввод. Однако для его использования необходимо установить надстройку Kutools. Перед запуском утилиты внимательно проверьте выделение ячеек, чтобы обеспечить точность результатов. Если потребуется отменить выделение, воспользуйтесь стандартной функцией отмены Excel (Ctrl + Z).
Подсчёт первого вхождения элементов с помощью макроса на VBA
Когда требуется полная автоматизация процесса, воспользуйтесь макросом VBA для перебора списка и подсчёта первых вхождений значений — без ручного ввода формул и сторонних надстроек. Это особенно эффективно при выполнении повторяющихся задач или обработке больших объёмов данных. Обратите внимание: чтобы макросы VBA работали, необходимо включить вкладку «Разработчик» и сохранить файл в макросо-совместимом формате (*.xlsm).
Применимость и примечания: Этот макрос идеально подходит для опытных пользователей и тех, кто работает с очень большими или часто обновляемыми наборами данных. Поскольку он вносит прямые изменения, обязательно создавайте резервную копию данных перед запуском. Макросы могут не работать в веб-версиях Excel или если они отключены настройками безопасности системы.
1. В Excel нажмите Инструменты разработчика > Visual Basic. Когда откроется окно Microsoft Visual Basic for Applications, перейдите в меню Вставка > Модуль и вставьте следующий код в окно модуля:
Sub CountFirstInstances()
Dim rng As Range
Dim dict As Object
Dim cell As Range
Dim firstInstanceCount As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the range to count first instances:", xTitleId, rng.Address, Type:=8)
Set dict = CreateObject("Scripting.Dictionary")
firstInstanceCount = 0
For Each cell In rng
If Not dict.exists(cell.Value) Then
dict.Add cell.Value, 1
firstInstanceCount = firstInstanceCount + 1
End If
Next cell
MsgBox "The number of first instances in the selected range is: " & firstInstanceCount, vbInformation, "First Instance Count"
End Sub2. После вставки кода нажмите кнопку 
(Выполнить) или клавишу F5, чтобы запустить макрос. Когда появится запрос, выберите диапазон для анализа (например, A1:A10) и нажмите OK. В ответ откроется диалоговое окно с количеством первых вхождений — уникальных значений и первых появлений дубликатов — в выделенном диапазоне.
Советы и меры предосторожности: Если выделение выполнено некорректно, просто перезапустите макрос. Используемый объект Dictionary учитывает и пустые ячейки, поэтому будьте внимательны: если ваш диапазон данных содержит пустые ячейки, это может привести к появлению лишнего счётчика для них. Чтобы повысить точность, избегайте выделения пустых строк или предварительно отфильтруйте пустые значения. Методы VBA могут вызывать предупреждения безопасности или требовать разрешения на запуск макросов — при необходимости скорректируйте настройки Центра управления безопасностью.
Рекомендации по устранению неполадок: Если макрос не запускается, убедитесь, что макросы включены: Файл > Параметры > Центр управления безопасностью > Параметры Центра управления безопасностью > Параметры макросов. Всегда сохраняйте свою работу перед запуском кода. Данный код VBA предназначен для списков в одном столбце — для диапазонов с несколькими столбцами его необходимо соответствующим образом адаптировать.
Рекомендации по выбору метода: В заключение, выбор между формулой, утилитой Kutools и макросом VBA зависит от вашего уровня навыков, объёма данных и предпочтений в отношении ручных или автоматизированных решений. Формульный метод отлично подходит для небольших наборов данных и пользователей, уверенно владеющих базовыми возможностями Excel; Kutools обеспечивает быстрое и наглядное решение для тех, кто уже использует эту надстройку; а макрос VBA — оптимальный выбор для автоматизации подсчёта дубликатов или работы с очень большими массивами данных. Каждый из этих подходов эффективно выявляет и подсчитывает первые вхождения значений в соответствии с вашим рабочим процессом.
См. также:
- Как подсчитать количество непустых ячеек в Excel?
- Как подсчитать частоту вхождения текста, числа или символа в столбце Excel?
- Как подсчитать количество символов в ячейке Excel, не учитывая пробелы?
- Как подсчитать количество разрывов страниц на текущем листе в Excel?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек