Как найти однофамильцев в excel

При совместной работе с
таблицами Excel или большом числе записей
накапливаются дубли строк. Статья
посвящена тому, как выделить
повторяющиеся значения в Excel,
удалить лишние записи или сгруппировать,
получив максимум информации.

Поиск
одинаковых значений в Excel

Выберем
одну из ячеек в таблице. Рассмотрим, как
в Экселе найти повторяющиеся значения,
равные содержимому ячейки, и выделить
их цветом.

На
рисунке – списки писателей. Алгоритм
действий следующий:

• Выбрать
  ячейку I3
  с записью «С. А. Есенин».
• Поставить
  задачу – выделить цветом ячейки с
  такими же записями.
• Выделить
  область поисков.
• Нажать
  вкладку «Главная».
• Далее
  группа «Стили».
• Затем
  «Условное форматирование»;
• Нажать
  команду «Равно».

• Появится
  диалоговое окно:

• В
  левом поле указать ячейку с I2,
  в которой записано «С. А. Есенин».
• В
  правом поле можно выбрать цвет шрифта.
• Нажать
  «ОК».

В
таблицах отмечены цветом ячейки, значение
которых равно заданному.

Несложно
понять, как
в Экселе найти одинаковые значения в
столбце.
Просто выделить перед поиском нужную
область – конкретный столбец.

Ищем в таблицах Excel
все повторяющиеся значения

Отметим
все неуникальные записи в выделенной
области. Для этого нужно:

• Зайти
  в группу «Стили».
• Далее
  «Условное форматирование».
• Теперь
  в выпадающем меню выбрать «Правила
  выделения ячеек».
• Затем
  «Повторяющиеся значения».

• Появится
  диалоговое окно:

• Нажать
  «ОК».

Программа
ищет повторения во всех столбцах.

Если
в таблице много неуникальных записей,
то информативность такого поиска
сомнительна.

Удаление одинаковых значений
из таблицы Excel

Способ
удаления неуникальных записей:

1. Зайти
   во вкладку «Данные».
2. Выделить
   столбец, в котором следует искать
   дублирующиеся строки.
3. Опция
   «Удалить дубликаты».

В
результате получаем список, в котором
каждое имя фигурирует только один раз.

Список
с уникальными значениями:

Расширенный фильтр: оставляем
только уникальные записи

Расширенный
фильтр – это инструмент для получения
упорядоченного списка с уникальными
записями.

• Выбрать
  вкладку «Данные».
• Перейти
  в раздел «Сортировка и фильтр».
• Нажать
  команду «Дополнительно»:

• В
  появившемся диалоговом окне ставим
  флажок «Только уникальные записи».
• Нажать
  «OK»
  – уникальный список готов.

Поиск дублирующихся значений
с помощью сводных таблиц

Составим
список уникальных строк, не теряя данные
из других столбцов и не меняя исходную
таблицу. Для этого используем инструмент
Сводная таблица:

Вкладка
«Вставка».

Пункт
«Сводная таблица».

В
диалоговом окне выбрать размещение
сводной таблицы на новом листе.

В
открывшемся окне отмечаем столбец, в
котором содержатся интересующие нас
значений.

Получаем
упорядоченный список уникальных строк.

Функция СЧЁТЕСЛИ в Excel используется для подсчета количества ячеек в рассматриваемом диапазоне, содержащиеся данные в которых соответствуют критерию, переданному в качестве второго аргумента данной функции, и возвращает соответствующее числовое значение.

Функция СЧЁТЕСЛИ может быть использована для анализа числовых значений, текстовых строк, дат и данных другого типа. С ее помощью можно определить количество неповторяющихся значений в диапазоне ячеек, а также число ячеек с данными, которые совпадают с указанным критерием лишь частично. Например, таблица Excel содержит столбец с ФИО клиентов. Для определения количества клиентов-однофамильцев с фамилией Иванов можно ввести функцию =СЧЁТЕСЛИ(A1:A300;”*Иванов*”). Символ «*» указывает на любое количество любых символов до и после подстроки «Иванов».

Примеры использования функции СЧЁТЕСЛИ в Excel

Пример 1. В таблице Excel содержатся данные о продажах товаров в магазине бытовой техники за день. Определить, какую часть от проданной продукции составляет техника фирмы Samsung.

Вид исходной таблицы данных:

Для расчета используем формулу:

=СЧЁТЕСЛИ(C3:C17;”Samsung”)/A17

Описание аргументов:

• C3:C17 – диапазон ячеек, содержащих названия фирм проданной техники;
• “Samsung” – критерий поиска (точное совпадение);
• A17 – ячейка, хранящая номер последней продажи, соответствующий общему числу продаж.

Результат расчета:

Доля проданной продукции техники фирмы Samsung в процентах составляет – 40%.



Подсчет количества определенного значения ячейки в Excel при условии

Пример 2. По итогам сдачи экзаменов необходимо составить таблицу, в которой содержатся данные о количестве студентов, сдавших предмет на 5, 4, 3 балла соответственно, а также тех, кто не сдал предмет.

Вид исходной таблицы:

Предварительно выделим ячейки E2:E5, введем приведенную ниже формулу:

=СЧЁТЕСЛИ(B3:B19;D2:D5)

Описание аргументов:

• B3:B19 – диапазон ячеек с оценками за экзамен;
• D2:D5 – диапазон ячеек, содержащих критерии для подсчета числа совпадений.

В результате получим таблицу:

Статистический анализ посещаемости с помощью функции СЧЁТЕСЛИ в Excel

Пример 3. В таблице Excel хранятся данные о просмотрах страниц сайта за день пользователями. Определить число пользователей сайта за день, а также сколько раз за день на сайт заходили пользователи с логинами default и user_1.

Вид исходной таблицы:

Поскольку каждый пользователь имеет свой уникальный идентификатор в базе данных (Id), выполним расчет числа пользователей сайта за день по следующей формуле массива и для ее вычислений нажмем комбинацию клавиш Ctrl+Shift+Enter:

Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив дробных чисел 1/количество_вхождений, например, для пользователя с ником sam это значение равно 0,25 (4 вхождения). Общая сумма таких значений, вычисляемая функцией СУММ, соответствует количеству уникальных вхождений, то есть, числу пользователей на сайте. Полученное значение:

Для определения количества просмотренных страниц пользователями default и user_1 запишем формулу:

В результате расчета получим:

Особенности использования функции СЧЁТЕСЛИ в Excel

Функция имеет следующую синтаксическую запись:

=СЧЕТЕСЛИ(диапазон; критерий)

Описание аргументов:

• диапазон – обязательный аргумент, принимающий ссылку на одну либо несколько ячеек, в которых требуется определить число совпадений с указанным критерием.
• критерий – условие, согласно которому выполняется расчет количества совпадений в рассматриваемом диапазоне. Условием могут являться логическое выражение, числовое значение, текстовая строка, значение типа Дата, ссылка на ячейку.

Примечания:

1. При подсчете числа вхождений в диапазон в соответствии с двумя различными условиями, диапазон ячеек можно рассматривать как множество, содержащее два и более непересекающихся подмножеств. Например, в таблице «Мебель» необходимо найти количество столов и стульев. Для вычислений используем выражение =СЧЁТЕСЛИ(B3:B200;”*стол*”)+СЧЁТЕСЛИ(B3:B200;”*стул*”).
2. Если в качестве критерия указана текстовая строка, следует учитывать, что регистр символов не имеет значения. Например, функция СЧЁТЕСЛИ(A1:A2;”Петров”) вернет значение 2, если в ячейках A1 и A2 записаны строки «петров» и «Петров» соответственно.
3. Если в качестве аргумента критерий передана ссылка на пустую ячейку или пустая строка «», результат вычисления для любого диапазона ячеек будет числовое значение 0 (нуль).
4. Функция может быть использована в качестве формулы массива, если требуется выполнить расчет числа ячеек с данными, удовлетворяющим сразу нескольким критериям. Данная особенность будет рассмотрена в одном из примеров.
5. Рассматриваемая функция может быть использована для определения количества совпадений как по одному, так и сразу по нескольким критериям поиска. В последнем случае используют две и более функции СЧЁТЕСЛИ, возвращаемые результаты которых складывают или вычитают. Например, в ячейках A1:A10 хранится последовательность значений от 1 до 10. Для расчета количества ячеек с числами больше 3 и менее 8 необходимо выполнить следующие действия:

Скачать примеры функции СЧЁТЕСЛИ для подсчета ячеек в Excel

• записать первую функцию СЧЁТЕСЛИ с критерием «>3»;
• записать вторую функцию с критерием «>=8»;
• определить разницу между возвращаемыми значениями =СЧЁТЕСЛИ(A1:10;”>3″)-СЧЁТЕСЛИ(A1:A10;”>=8″). То есть, вычесть из множества (3;+∞) подмножество [8;+∞).

Настоящая проблема для больших MS Excel таблиц — повторяющиеся данные или дублирующиеся строки. В обычных условиях, поиск «дублей» это сизифов труд. Просмотреть вручную даже несколько десятков строк — уже сложная задача, а если счет идет на тысячи?

К счастью, у MS Excel есть довольно эффективный способ борьбы с дубликатами строк — проверка данных. Однако, чтобы она заработала, сперва нам придется как следует подготовиться.

Подготовка таблицы MS Excel к поиску дублей

Чтобы искать дублирующиеся записи, нам нужна какая-то точка опоры. По какому принципу будет осуществляться отбор дублей? Поэтому первым делом, нужно определить «эталонный столбец» — именно по нему и будет осуществляться отбор дубликатов. Данные размещенные в нем должны быть уникальны и исключать возможность ошибки ввода.

Отличный пример эталона — email адрес: он уникален для каждого владельца, не склоняется, пишется всегда на английском языке, не содержит пробелов.

Плохой вариант — фамилия: во-первых в списке могут быть однофамильцы, во-вторых может быть написана с дефисами и без и т.п.

Как только эталонный столбец определен, нужно его привести к максимально чистому виду — то есть внимательно просмотреть и убедится, что в нем дубликатов нет в принципе. Дело в том, что инструмент «проверка данных» замечательно справляется с попытками неправильного ввода, но не обратит внимания на те данные, которые уже присутствуют в документе, даже если они не верны.

Настраиваем проверку данных в MS Excel

Продолжаем наводить порядок. Выделяем все данные нашего эталонного столбца (конечно с запасом — мы ведь будем пополнять его!), чтобы ускорить этот процесс — выделите первую ячейку столбца, зажмите клавишу SHIFT и промотайте лист вниз до конца, а затем снова щелкните мышью, но уже в последнюю ячейку. Столбец выделен.

Перейдите на вкладку «Данные», и в группе «Работа с данными», щелкните на инструмент «Проверка данных».

В открывшемся окне, на вкладке «Параметры», в выпадающем списке «Тип данных» выберите «Другой», а в появившемся ниже поле «Формула», введите:

=СЧЁТЕСЛИ($A:$A;A2)=1

или

=COUNTIF($A:$A,A2)=1

Естественно, в зависимости от буквы-обозначения вашего эталонного столбца, меняются и буквенные обозначения в параметрах функции ($A:$A;A2). По русски это звучало бы:

применить функцию СЧЁТЕСЛИ, которая сравнивает каждую вводимую в столбец А строчку с каждой уже там существующей ($A:$A), начиная со второй ячейки (А2), при этом одинаковое значение для каждой комбинации может появится только 1 раз (=1).

Перейдите на вкладку «Сообщение об ошибке» и введите текст, который будет выводится в случае несоблюдения описанного выше условия (поля «Заголовок» и «Сообщение). Поле «Вид» оставьте по-умолчанию, в положении «Останов».

Вот и готово. Давайте проверим?

Отлично — при вводе слова «Ананас» ничего не происходит, но стоит мне ввести дубликат — слово «Груши», как MS Excel немедленно выдает ошибку и рекомендации по её исправлению, которые я ввел раньше.

А как же исключения?

Бывают и исключения из правил. Предположим, что искомые груши мне все-таки надо внести в список, что называется «кровь из носу». Чтобы создать такую лазейку, вновь откройте окно «Проверка данных», перейдите на вкладку «Сообщение об ошибке», но вместо категоричного «Останов», выберите более либеральное «Предупреждение». Примените изменения и вновь попробуйте ввести заведомо неправильное значение.

Как видите, форма вывода ошибки изменилась — она как и ранее предупреждает об ошибке, однако нажатие кнопки «Да», позволит вам обойти написанное нами правило, и все-таки добавить наши груши в список повторно.