Как посчитать количество уникальных значений в столбце Excel
Содержание
При работе в таблице Excel может возникнуть необходимость определить количество уникальных значений. Решить данную задачу можно несколькими способами, в зависимости от специфики документа и содержащихся в нем данных. В данном материале рассмотрим основные способы подсчета неповторяющихся ячеек при помощи формул и других инструментов Excel.
Что такое уникальное значение
Прежде чем перейти к рассмотрению способов подсчета на конкретных примерах, сперва обратимся к теоретической части. В Microsoft Excel существует несколько групп данных, в том числе различные и уникальные. Это две разных категории, которые отличаются друг от друга. Однако некоторые пользователи, особенно новички, допускают ошибку, считая, что эти понятия идентичны.
Уникальным называется значение, которое встречается в диапазоне только 1 раз. Различным, в свою очередь, называется вхождение, которое присутствует в списке без учета повторов. Количество вхождений может быть любым. Таким образом, если значение встречается в диапазоне больше одного раза, его нельзя рассматривать как уникальное.
Способы поиска уникальных значений
Если потребуется выполнить подсчет уникальных значений, можно воспользоваться несколькими стандартными инструментами Microsoft Excel. Способ подбирают, исходя из специфики документа и объема содержащихся в нем данных. Начнем с самых простых методов и затем рассмотрим более сложные варианты поиска.
Условное форматирование
Если диапазон, в котором нужно посчитать уникальные значения, небольшой, использовать формулы и математические вычисления нецелесообразно. Намного проще воспользоваться инструментом форматирования, который позволяет выделить все уникальные и повторяющиеся клетки разным цветом. Сделать это очень просто:
- Выделяем диапазон, в котором необходимо найти уникальные ячейки. В нашем случае это колонка А. Далее во вкладке «Главная» находим «Условное форматирование».
Находим условное форматирование - В выпадающем окне нажимаем «Правила выделения ячеек», а в следующем жмем «Повторяющиеся значения». Нажимаем в диалоговом окне, предварительно выбрав цвет текста и заливки. Менять цветовую схему необязательно, можно использовать ту, что установлена по умолчанию.
Включаем выделение повторяющихся ячеек - В результате форматирования все клетки с повторяющимися значениями будут выделены. В свою очередь уникальные ячейки останутся без выделения и можно будет посчитать их число без применения формул.
Результат форматирования
С помощью данного способа можно посчитать неповторяющиеся клетки, при условии что их немного. Если число уникальных значений большое, лучше воспользоваться альтернативными способами поиска.
Подсчет количества вхождений
Чтобы определить число уникальных ячеек, можно воспользоваться функцией для подсчета дубликатов. Для этих целей используется формула СЧЁТЕСЛИ, которая позволяет определить число одинаковых значений в диапазоне.
В приведенном примере осуществляется подсчет повторяющихся и уникальных наименований. Поэтому для удобства создаем дополнительный столбец через контекстное меню.
После этого можем выполнить подсчет с использованием формулы:
- В ячейку В2 прописываем формулу «СЧЁТЕСЛИ». В качестве диапазона указываем весь столбец от первого до последнего значения через знак «$». Он нужен для закрепления данных из колонки. В качестве критерия указываем адрес первой клетки в диапазоне — А2.
Прописываем формулу - После того как формула прописана, нажимаем . В результате выполнения функции будет отображено число — это количество вхождений значения в заданном диапазоне. Остается применить формулу ко всем ячейкам, используя маркер автозаполнения.
Применяем ко всему столбцу - Все значения, напротив которых находится цифра 1, являются уникальными, так как они встречаются только однократно. Если таблица небольшая, число ячеек с единицей можно сосчитать самостоятельно. Если же документ содержит множество строк, проще воспользоваться вспомогательной функцией. Здесь нам также поможет формула «СЧЁТЕСЛИ». В качестве аргументов указываем диапазон, в котором находятся количества повторов. В нашем случае это столбец В. В качестве критерия указываем цифру 1.
Пишем функцию для подсчета уникальных позиций - В результате получаем число, которое отражает, сколько ячеек в диапазоне содержат неповторяющееся значение.
Результат выполнения
Как сделать отметку напротив уникальных значений
Одно из преимуществ формулы «СЧЁТЕСЛИ» - гибкая система ввода аргументов. С помощью данного инструмента можно не только посчитать, но и отметить уникальные значения в столбце. Это довольно удобный способ, если нужно узнать не только количество дубликатов, но и выделить их в диапазоне, сделав визуально заметными. Рассмотрим, как выглядит и используется модифицированная формула:
- Прописываем формулу СЧЁТЕСЛИ с диапазоном и критерием поиска, как в предыдущем примере. Эту функцию оборачиваем в скобки и дополняем формулой «ЕСЛИ». В качестве условия указываем «>1». Как результат прописываем пустую строку и метку «Уникальное значение».
Прописываем условную конструкцию - Конструкция выполняется простым образом. Если количество повторений для значения больше 1 — ячейка не помечается. Если же она содержит уникальное значение, в ней появится соответствующая надпись. Чтобы убедиться в том, что формула работает, применяем ее ко всему диапазону при помощи маркера автозаполнения.
Результат выполнения
Если уникальных значений много, здесь также поможет формула «СЧЁТЕСЛИ», примененная к столбцу с метками. Такой вариант будет удобен, если неповторяющихся ячеек в таблице слишком много для простого визуального подсчета.
Поиск с помощью формулы массива
Для таблиц с большим количеством данных лучше всего подходит формула массива. Она объединяет несколько функций и позволяет посчитать уникальные значения сразу, без выполнения вспомогательных вычислений. Рассмотрим способ использования формулы массива на конкретном примере.
В первую очередь необходимо правильно собрать набор функций. В нашем случае диапазон, в котором нужно выполнить подсчет количества уникальных значений — это столбец А2-А64.
Прописываем формулу =СУММ(ЕСЛИ(СЧЁТЕСЛИ(A2:A64;A2:A64)=1;1;0)). Функция СЧЕТ в комплексе со СЧЁТЕСЛИ отвечает за поиск уникальных ячеек, а функция СУММ — за определение их количества.
Результат выполнения функции — число 5. Это количество ячеек, содержимое которых не повторяется в заданном диапазоне. Данный вариант счёта в Эксель удобный, однако необходимо грамотно прописывать аргументы функции, а также следить за правильностью синтаксиса. В противном случае при счете могут возникнуть ошибки.
Важно отметить, что при использовании формул массива вместо кнопки «Enter» необходимо нажимать комбинацию ++. Иначе прописанная формула останется в ячейке в виде строки и не будет работать.
Подсчет значений без учета дубликатов
Выяснив ранее, как подсчитать количество уникальных ячеек, рассмотрим альтернативные варианты. Перед пользователем может возникнуть задача, связанная с подсчетом общего числа разных значений без учета повторов. В целом, найти все ячейки с разными значениями нетрудно, если знать о возможностях стандартных инструментов Excel.
Подсчет с помощью формулы
Есть несколько способов, позволяющих определить количество разных ячеек в диапазоне. Повторения при этом игнорируются и подсчитываются только первые вхождения каждого значения. Один из вариантов — формула СУММПРОИЗВ, которую можно применить следующим образом:
- Прописываем функцию. В качестве аргументов указываем «1/» и функцию «СЧЁТЕСЛИ» с диапазоном, в котором нужно выполнить поиск. В нашем случае это столбец А2-А64.
Пишем функцию суммпроизв - Нажимаем клавишу и в результате выполнения конструкции получится число — сумма разных значений из диапазона без учета дубликатов.
Результат выполнения
Данный способ позволит быстро узнать, сколько разных значений в Excel содержит диапазон, не учитывая при этом повторения.
Удаление дубликатов
Если пользователю необходимо посчитать количество уникальных значений и работать только с ними, дубликаты можно попросту удалить. В программе есть несколько способов, позволяющих решить данную задачу довольно просто. Самый простой — очистка через специальный инструмент во вкладке «Данные». С его помощью можно быстро преобразовать таблицу, удалив из нее все повторяющиеся ячейки. В листе остаются только разные значения, тогда как все повторы будут исключены независимо от их количества.
- Выделяем необходимый диапазон, либо весь столбец в таблице Эксель.
- Переходим во вкладку «Данные».
- В Панели инструментов находим «Удалить дубликаты».
Удаляем дубликаты через вкладку данные
На экране отобразится диалоговое окно, в котором нужно выбрать один из вариантов дальнейших действий. Программа предлагает автоматически расширить диапазон выделения и удалить в нем все дубликаты. Если по условию задачи нужно убрать повторения только в одном столбце, ставим отметку напротив строки «Сортировать в пределах указанного выделения». Нажимаем и в результате получаем таблицу, в которой нет повторяющихся значений.
Удаление дубликатов из столбца через фильтр
С помощью фильтра можно сделать выборку всех значений, удалив при этом повторы. Этот способ очень удобен, если нужно за короткий промежуток времени очистить лист от дубликатов, оставив только неповторяющиеся ячейки. Причем фильтр для этой цели можно использовать двумя способами.
Первый вариант предусматривает удаление дубликатов непосредственно в самом столбце:
- Выделяем нужную колонку. Затем переходим во вкладку «Данные». В разделе «Фильтр» нажимаем «Дополнительно».
Нажимаем дополнительно во вкладке данные - Ставим отметку напротив строчки «Только уникальные записи». Метку возле надписи «Фильтровать список на месте» оставляем без изменений.
Фильтруем записи
В результате применения такого фильтра в таблице будут только различные ячейки без повторов. Определив их число, можно узнать количество всех уникальных значений в столбце Excel.
Второй вариант подойдет в том случае, если менять исходную таблицу нельзя, однако нужно получить выборку из уникальных значений. Эту задачу также можно решить при помощи фильтра:
- Выделяем столбец, затем открываем вкладку «Данные», а потом нажимаем «Дополнительно» возле инструмента «Фильтр».
Нажимаем дополнительно - Ставим отметку напротив строки «Скопировать в другое место». Напротив строки «Поместить результат в диапазон» указываем адрес ячейки, в которую будут добавлены выбранные значения. Затем ставим метку напротив строки «Только уникальные записи».
Задаем параметры
В результате получаем выборку ячеек без повторений и дубликатов. С ней можно работать, не затрагивая столбец в основной части документа.
Заключение
С вопросом, как посчитать уникальные ячейки в таблице, сталкивается большинство пользователей Excel. Для этого можно воспользоваться инструментами форматирования и фильтрации, либо задействовать формулы для расчета. Каждый способ имеет свои плюсы и минусы, а потому подбирать метод следует с учетом специфики документа, с которым работает пользователь.
Загрузка...
