Как посчитать количество дублей в excel
Предположим, вам нужно узнать количество уникальных значений в диапазоне, который содержит повторяющиеся значения. Например, если столбец содержит:
Значения 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.
Особенности использования функции СЧЁТЕСЛИ в Excel
- Ячейка строго с определенным значением, можно поставить значения («яблоко»), (B4),(36). Регистр не учитывается, но даже лишний пробел уже включит в подсчет ячейку.
- Больше или меньше определенного числа. Тут уже идет в ход знак равенства, точнее неравенств, а именно («>5»);(«10″);(» <=103″).
В данной статье мы рассмотрим с вами как найти уникальные значения в Excel. Предположим у вас есть выгрузка наименований телефонов, причем в данной выгрузке мобильные телефоны повторяются по несколько раз.
Полезные сведения → Как объединить ячейки → Как вставить значения → Аргументы функции → Работа с форматами → Функция ЕСЛИ → Как удалить пробелы → Функция впр vlookup→ Работа с таблицами
Различные значения с учетом регистра.
Подобно подсчету уникальных значений, самый простой способ подсчета различных значений с учетом регистра — это добавить вспомогательный столбец формулы массива, который идентифицирует нужные элементы, включая повторяющиеся первые вхождения.
Подход в основном такой же, как тот, который мы использовали для подсчета уникальных значений с учетом регистра, с одним небольшим изменением:
Как вы помните, все формулы массива в Excel требуют нажатия Ctrl + Shift + Enter.
Заметив это выражение, вы можете подсчитать «различные» значения, используя обычную функцию СЧЁТЕСЛИ, например:
Если вы не можете добавить вспомогательный столбец на свой рабочий лист, вы можете использовать следующую более сложную формулу массива для подсчета различных значений с учетом регистра без создания дополнительного столбца:
Как видите, обе формулы дают одинаковые результаты.
Добавление вспомогательного столбца в набор данных
Примечание. Если вы используете Excel 2013 и более поздние версии, пропустите этот метод и перейдите к следующему (вам доступна встроенная функция).
Это простой способ подсчета разных значений в сводной таблице, поскольку вам нужно только добавить вспомогательный столбец к исходным данным. После добавления вспомогательного столбца вы легко ответите на вопросы задачи.
Хотя это простой обходной путь, у него есть некоторые недостатки (которые будут рассмотрены далее).
Позвольте мне сначала показать вам, как добавить вспомогательный столбец и посчитать разные значения.
Предположим, у меня есть набор данных, как показано ниже:
Добавьте следующую формулу в столбец F и примените ее ко всем ячейкам, в которых есть данные в соседних столбцах.
= ЕСЛИ (СЧЁТЕСЛИМН ($C$2:C2; C2; $B$2:B2; B2) > 1;0;1)
Приведенная выше формула использует функцию СЧЁТЕСЛИМН для подсчета количества раз, когда имя появляется в данном регионе
Также обратите внимание на диапазоны критериев: $C$2:C2 и $B$2:B2. Это означает, что они продолжают расширяться, когда вы идете вниз по столбцу
Например, в ячейке F2 диапазон критериев составляет $C$2:C2 и $B$2:B2, а в ячейке F3 эти диапазоны расширяются до $C$3:C3 и $B$3:B3.
Это гарантирует, что функция СЧЁТЕСЛИМН считает первый экземпляр имени как 1, второй экземпляр имени как 2 и так далее.
Поскольку мы хотим получить только разные имена, используется функция ЕСЛИ, которая возвращает 1, когда имя появляется для региона в первый раз, и возвращает 0, когда оно появляется снова. Это гарантирует, что учитываются только разные имена, а не повторы.
Ниже показано, как будет выглядеть таблица, когда вы добавите вспомогательный столбец.
Теперь, когда мы изменили исходные данные, мы можем использовать их для создания сводной таблицы. Подключив вспомогательный столбец, получим количество различных сотрудников в каждом регионе.
Ниже приведены шаги, как сделать это:
- Выберите любую ячейку в таблице.
- Нажмите вкладку «Вставка».
Нажмите на кнопку Сводная таблица.
В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно (и включает вспомогательный столбец), и выбран «На новый лист» в качестве места размещения.
Нажмите ОК.
Вышеуказанные шаги вставят новый лист со сводной таблицей.
Перетащите поле «Регион» в область «Строки» и поле «Помощник» в область «Значения».
Вы получите вот такую сводную таблицу:
Теперь вы можете изменить заголовок столбца с «Сумма по полю Помощник» на «Количество сотрудников».
Суммирование по нескольким условиям. Функция СУММЕСЛИМН.
Ситуация усложняется, если при суммировании нам необходим учесть не один, а несколько критериев. В таком случае можно применить разные способы. Сегодня мы рассмотрим применение функции СУММЕСЛИМН. Она позволяет выполнить выборочное суммирование в зависимости от нескольких заданных условий. Всего таких условий можно указать до 127 вариантов. Как минимум надо задать 2 условия. Вы можете использовать и только одно условие, что не запрещено, однако в этом случае проще применить СУММЕСЛИ.
Синтаксис данной функции выглядит так
=СУММЕСЛИМН( диапазон суммирования; диапазон проверки на соответствие первому критерию ( то есть первому условию); первый критерий ( условие, которому должна соответствовать ячейка в диапазоне проверки первого критерия); диапазон проверки на соответствие второму критерию ( второму условию); второй критерий ( второе условие)… и так до 127 диапазонов проверки критериев и самих критериев).
Приведем пример работы СУММЕСЛИМН. Предположим следующее. Наименования товаров заданы в диапазоне B3:B50, количество упаковок каждого товара находится в графе С3:С50, а в диапазоне D3:D50 указаны соответствующие заданным позициям товара расценки. Наша задача – найти общее количество упаковок рубашек с ценой ниже 3000. Получаем следующую задачу:
«Найти общую сумму в диапазоне С3:С50, но при этом в диапазоне B3:B50 должно содержаться слово «рубашка», а в диапазоне D3:D50 значение должно быть меньше 3000». Итоговая формула будет выглядеть следующим образом.
=СУММЕСЛИМН(C3:C50;B3:B50;”рубашка”;D3:D50;”<3000″)
Рисунок 12
Естественно, что вместо указания явным образом названия нужного товара можно было указать ячейку, содержащую данное название. Это бы никак не повлияло на расчет.
При работе с функцией СУММЕСЛИМН надо учитывать все правила и тонкости, которые применялись для функции СУММЕСЛИ. Кроме этого, существуют и особенности непосредственно функции СУММЕСЛИМН:
- В отличии от функции СУММЕСЛИ равенство диапазонов суммирования и поиска соответствия условию является не рекомендательным, а обязательным. Если хотя бы один из указанных диапазонов не совпадет по размеру с остальными, получите не просто неверные данные, а значение ошибки #ЗНАЧ!
- В отличии от функции СУММЕСЛИ, где третий параметр не обязателен и иногда допускается его пропуск, для функции СУММЕСЛИМН указание каждого параметра обязательно. Нельзя задать диапазон проверки условия и при этом не указать само условие или наоборот. Тем более вы не можете не указать диапазон суммирования, так как таким диапазоном автоматически будет считаться самый первый диапазон, указанный при введении данной функции. Заданные для проверки условия проверяются по содержимому разных диапазонов. Нельзя проверить на соответствие разным критериям ячеек одного и того же диапазона. Вы можете найти общую сумму затрат по выбранному филиалу, группе затрат и периоду, так как наименование филиала, группа затрат и период будут указаны в разных графах. Если же вы попытаетесь с помощью функции СУММЕСЛИМН найти общую сумм затрат по нескольким филиалам из общего списка, то у вас ничего не получится, так как наименования филиалов расположены в одной колонке таблицы.
Нулевые строки.
Также имейте в виду, что СЧЕТЕСЛИ и СЧИТАТЬПУСТОТЫ считают ячейки с пустыми строками, которые только на первый взгляд выглядят пустыми.
Что такое эти пустые строки? Они также часто возникают при импорте данных из других программ (например, 1С). Внешне в них ничего нет, но на самом деле это не так. Если попробовать найти такие “пустышки” (F5 -Выделить – Пустые ячейки) – они не определяются. Но фильтр данных при этом их видит как пустые и фильтрует как пустые.
Дело в том, что существует такое понятие, как «строка нулевой длины» (или «нулевая строка»). Нулевая строка возникает, когда программе нужно вставить какое-то значение, а вставить нечего.
Проблемы начинаются тогда, когда вы пытаетесь с ней произвести какие-то математические вычисления (вычитание, деление, умножение и т.д.). Получите сообщение об ошибке #ЗНАЧ!. При этом функции СУММ и СЧЕТ их игнорируют, как будто там находится текст. А внешне там его нет.
И самое интересное – если указать на нее мышкой и нажать Delete (или вкладка Главная – Редактирование – Очистить содержимое) – то она становится действительно пустой, и с ней начинают работать формулы и другие функции Excel без всяких ошибок.
Если вы не хотите рассматривать их как пустые, используйте для подсчета реально пустых клеток следующее выражение:
Откуда могут появиться нулевые строки в ячейках? Здесь может быть несколько вариантов:
- Он есть там изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе (вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет – они просто заполняются строкой нулевой длины.
- Была создана формула, результатом которой стал текст нулевой длины. Самый простой случай:
=ЕСЛИ(Е1=1;10;””)
В итоге, если в Е1 записано что угодно, отличное от 1, программа вернет строку нулевой длины. И если впоследствии формулу заменять значением (Специальная вставка – Значения), то получим нашу псевдо-пустую позицию.
Если вы проверяете какие-то условия при помощи функции ЕСЛИ и в дальнейшем планируете производить с результатами математические действия, то лучше вместо “” ставьте 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: Файл -Параметры -Дополнительно – Показывать нули в позициях, которые содержат нулевые значения.
Задача 1 (с одним критерием)
Подсчитаем количество продаж на сумму >3000. Т.е. подсчет будем вести по столбцу Продажи .
Алгоритм следующий:
- Создадим табличку критериев (желательно над исходной таблицей, чтобы она не мешала добавлению новых данных в таблицу), например в диапазоне A2:A3 (см. рисунок выше). Табличка должна состоять из заголовка (совпадает с названием заголовка столбца исходной таблицы, к которому применяется критерий) и собственно критерия (условия отбора);
- Условия отбора могут быть записаны в текстовом формате. В нашем случае, просто >3000
- Теперь можно записать саму формулу =БСЧЁТ(B8:B13;B8;A2:A3) В B8:B13 находится База_данных (исходная таблица). Столбец А (Продавец) можно в данном случае не включать в базу_данных , т.к. по нему не производится подсчет и он не участвует в отборе. B8 – это ссылка на заголовок столбца по которому будет производиться подсчет ( Продажи ), в нем должны быть числовые значения. A2:A3 – ссылка на табличку критериев.
Альтернативное решение – простая формула = СЧЁТЕСЛИ(B9:B13;A3) .
Вывод : для простых однокритериальных задач огород с функцией =БСЧЁТ() городить не стоит.
СЧЕТЕСЛИ с несколькими условиями.
На самом деле функция Эксель СЧЕТЕСЛИ не предназначена для расчета количества ячеек по нескольким условиям. В большинстве случаев я рекомендую использовать его множественный аналог – функцию СЧЕТЕСЛИМН. Она как раз и предназначена для вычисления количества ячеек, которые соответствуют двум или более условиям (логика И). Однако, некоторые задачи могут быть решены путем объединения двух или более функций СЧЕТЕСЛИ в одно выражение.
Количество чисел в диапазоне
Одним из наиболее распространенных применений функции СЧЕТЕСЛИ с двумя критериями является определение количества чисел в определенном интервале, т.е. меньше X, но больше Y.
Например, вы можете использовать для вычисления ячеек в диапазоне B2: B9, где значение больше 5 и меньше или равно 15:
Количество ячеек с несколькими условиями ИЛИ.
Когда вы хотите найти количество нескольких различных элементов в диапазоне, добавьте 2 или более функций СЧЕТЕСЛИ в выражение. Предположим, у вас есть список покупок, и вы хотите узнать, сколько в нем безалкогольных напитков.
Сделаем это:
Обратите внимание, что мы включили подстановочный знак (*) во второй критерий. Он используется для вычисления количества всех видов сока в списке
Как вы понимаете, сюда можно добавить и больше условий.
Считаем числа и значения
Три полезных вещи, помогающих в работе с программой.
Сколько чисел находится в массиве, можно рассчитать с помощью формулы СЧЁТ(значение1;значение2;…)
Она учитывает только те элементы, которые включают в себя цифры.То есть если в некоторых из них будет прописан текст, они будут пропущены, в то время как даты и время берутся во внимание. В данной ситуации не обязательно задавать параметры по порядку: можно написать, к примеру, =СЧЁТ(А1:С3;В4:С7;…).
Другая статистическая функция — СЧЕТЗ — подсчитает вам непустые клетки в диапазоне, то есть те, которые содержат буквы, числа, даты, время и даже логические значения ЛОЖЬ и ИСТИНА
Обратное действие выполняет формула, показывающая численность незаполненных секций — СЧИТАТЬПУСТОТЫ(массив). Она применяется только к непрерывным выделенным областям.
Ставим экселю условия
Когда нужно подсчитать элементы с определённым значением, то есть соответствующие какому-то формату, применяется функция СЧЁТЕСЛИ(массив;критерий). Чтобы вам было понятнее, следует разобраться в терминах.
Массивом называется диапазон элементов, среди которых ведется учет. Это может быть только прямоугольная непрерывная совокупность смежных клеток. Критерием считается как раз таки то условие, согласно которому выполняется отбор. Если оно содержит текст или цифры со знаками сравнения, мы его берем в кавычки. Когда условие приравнивается просто к числу, кавычки не нужны.
Разбираемся в критериях
Примеры критериев:
- «>0» — считаются ячейки с числами от нуля и выше;
- «Товар» — подсчитываются секции, содержащие это слово;
- 15 — вы получаете сумму элементов с данной цифрой.
Для большей ясности приведу развернутый пример.
Чтобы посчитать ячейки в зоне от А1 до С2, величина которых больше прописанной в А5, в строке формул необходимо написать =СЧЕТЕСЛИ(А1:С2;«>»&А5).
Задачи на логику
Хотите задать экселю логические параметры? Воспользуйтесь групповыми символами * и ?. Первый будет обозначать любое количество произвольных символов, а второй — только один.
К примеру, вам нужно знать, сколько имеет электронная таблица клеток с буквой Т без учета регистра. Задаем комбинацию =СЧЕТЕСЛИ(А1:D6;«Т*»). Другой пример: хотите знать численность ячеек, содержащих только 3 символа (любых) в том же диапазоне. Тогда пишем =СЧЕТЕСЛИ(А1:D6;«???»).
Средние значения и множественные формулы
В качестве условия может быть задана даже формула. Желаете узнать, сколько у вас секций, содержимое которых превышают среднее в определенном диапазоне? Тогда вам следует записать в строке формул следующую комбинацию =СЧЕТЕСЛИ(А1:Е4;«>»&СРЗНАЧ(А1:Е4)).
Если вам нужно сосчитать количество заполненных ячеек по двум и более параметрам, воспользуйтесь функцией СЧЕТЕСЛИМН. К примеру, вы ищите секций с данными больше 10, но меньше 70. Вы пишете =СЧЕТЕСЛИМН(А1:Е4;«>10»;А1:Е4;«<70»).
Помимо этого, у вас есть возможность задать условия И/ИЛИ. Только во втором случае придется использовать сразу несколько правил. Смотрим: вам необходимо найти клетки, в которых слова начинаются с буквы В или Р — пишете =СЧЕТЕСЛИ(А1:Е4;«В*»)+ СЧЕТЕСЛИ(А1:Е4;«Р*»).
Может быть, на первый взгляд вышеизложенные инструкции кажутся вам не совсем понятными. Но применив их несколько раз на практике, вы убедитесь, что они намного упрощают и совершенствуют работу с программой Excel.
Подсчет различных значений.
Используйте следующее общее выражение:
Помните, что это формула массива, поэтому вам следует нажать Ctrl + Shift + Enter вместо обычного Enter.
В качестве альтернативы вы можете использовать функцию СУММПРОИЗВ и написать формулу обычным способом:
= СУММПРОИЗВ (1 / СЧЁТЕСЛИ (интервал; интервал))
Например, чтобы подсчитать различные значения в диапазоне A2: A10, вы можете использовать выражение:
{= СУММ (1 / СЧЁТЕСЛИ (A2: A10; A2: A10))}
или
= СУММПРОИЗВ (1 / СЧЁТЕСЛИ (A2: A10; A2: A10))
Этот метод подходит не только для подсчета в столбце, но и для диапазона данных. Например, у нас есть два столбца для имен. Итак, давайте сделаем это:
Этот способ подходит для текста, чисел, дат.
Единственное ограничение — диапазон должен быть непрерывным и не содержать пустых ячеек или ошибок.
Если у вас есть пустые ячейки в диапазоне данных, вы можете изменить:
Тогда пустая ячейка будет включена в расчет и будет засчитана.
Как это работает?
Как вы уже знаете, мы используем функцию СЧЁТЕСЛИ, чтобы узнать, сколько раз каждый отдельный элемент встречается в указанном диапазоне. В приведенном выше примере результатом функции СЧЁТЕСЛИ является числовой массив: {3: 2: 2: 1: 3: 2: 1: 2: 3}.
Затем выполняется серия операций деления, в которых одна делится на каждую цифру этой матрицы. Это преобразует все неуникальные значения в дробные числа, соответствующие количеству повторений. Например, если число или текст появляется в списке 2 раза, в массиве создаются 2 элемента, равных 0,5 (1/2 = 0,5). А если он встречается 3 раза, то в массиве создаются 3 элемента из 0,333333.
В нашем примере результатом вычисления выражения 1 / COUNTIF (A2: A10; A2: A10) является массив {0,3333333333333333: 0,5: 0,5: 1: 0,333333333333333: 0,5: 1: 0,5: 0,333333333333333}.
Все еще не совсем ясно? Это потому, что мы еще не применили функцию СУММ / СУММПРОИЗВ. Когда одна из этих функций добавляет числа в массив, сумма всех дробных чисел для любого отдельного элемента всегда дает 1, независимо от того, сколько раз оно встречается. И поскольку все уникальные элементы отображаются в массиве как единицы (1/1 = 1), конечный результат — это сумма всех значений, которые встречаются.
Как и в случае с подсчетом уникальных значений в Excel, вы можете использовать универсальные параметры формулы для обработки чисел, текста или чувствительности к регистру.
Помните, что все следующие выражения являются формулами массива и требуют нажатия Ctrl + Shift + Enter.