Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как найти и выделить повторяющиеся имена, которые совпадают как с именем, так и с фамилией в Excel?

Поиск и удаление дубликатов (повторяющихся значений) в Excel

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

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

Таблица данных

Удаление дубликатов

В Excel (начиная с версии 2007) есть стандартная функция удаления дубликатов.
Для поиска дубликатов выделяем диапазон ячеек, в котором будем искать повторяющиеся значение, и в панели вкладок выбираем Данные ->Работа с данными ->Удалить дубликаты:

Инструмент "Удалить дубликаты"

Укажите столбцы, для которых вы ищете дубликаты, и, если в таблице есть заголовки, также установите флажок Мои данные содержат заголовки:

Настройки параметров

Теперь у вас есть отформатированная таблица без дубликатов значений.

Расширенный фильтр

Для того, чтобы найти дубликаты в Excel также можно воспользоваться расширенным фильтром (работает начиная с версии 2003).
Вновь выделяем диапазон ячеек и в панели вкладок выбираем Данные ->Сортировка и фильтр ->Дополнительно:

Инструмент "Расширенный фильтр"

Выберите настройки фильтра, установите флажок Копировать результат в другое место и Только уникальные записи:

Настройка параметров фильтра

Также вместо копирования результата в другое место можно выбрать опцию Фильтровать список на месте.
В этом случае строки с дубликатами скроются и можно посмотреть какие конкретно данные являются дубликатами.
Итоговый результат (слева — исходная таблица, справа — без дубликатов):

Отформатированная таблица без дубликатов

Условное форматирование

Выделить дубликаты в Excel также можно с помощью условного форматирования (начиная с версии 2007).
Снова выделяем таблицу, в которой ищем дубликаты и переходим в панели вкладок на Главная ->Условное форматирование ->Правила выделения ячеек ->Повторяющиеся значения:

Инструмент "Условное форматирование"

Выберите условия форматирования (например, показать дубликаты с красной заливкой), в результате чего получится такая таблица:

Выделение дубликатов в таблице

Поскольку Excel проверяет дубликаты не по строке таблицы, а по столбцу таблицы, можно увидеть, что некоторые ячейки в уникальных строках были выбраны.

Читайте так же:
Как изменить размер существующего именованного диапазона в Excel?

Найти несколько значений в Excel

Давайте рассмотрим несколько примеров, демонстрирующих использование функции VLOOKUP для решения самых амбициозных задач Excel во второй части нашего учебника по функции VLOOKUP. Эти примеры предполагают, что у вас уже есть базовое понимание того, как работают эти функции. Возможно, вы захотите начать с первой части этого учебника, в которой объясняется синтаксис и базовое применение ВЛООКУП. Что ж, давайте начнем.

Поиск в Excel по нескольким критериям

Функция БПФ в Excel является мощным инструментом для поиска заданного значения в базе данных. Однако синтаксис имеет существенное ограничение — вы можете искать только одно значение. Как можно выполнить поиск по нескольким значениям, если это необходимо? Далее вы найдете решение.

Пример 1: Поиск по 2-м разным критериям

Предположим, у нас есть список заказов и мы хотим найти Количество товара (Qty.), основываясь на двух критериях – Имя клиента (Customer) и Название продукта (Product). Дело усложняется тем, что каждый из покупателей заказывал несколько видов товаров, как это видно из таблицы ниже:

Руководство по функции ВПР в Excel

В этой ситуации обычная функция FFT не работает. Вместо этого она возвращает первое найденное значение, совпадающее с искомым. В качестве примера, если вы хотите вычислить количество сладостей, заказанных клиентом Джереми Хиллом, вы можете написать такую формулу:

Простым решением является создание дополнительного столбца, в котором можно объединить столько критериев, сколько вы пожелаете. В нашем примере это столбцы Customer Name и Product Name. Помните, что комбинированный столбец всегда должен быть самым левым в диапазоне поиска, так как именно на этот столбец смотрит функция VPR при поиске значения.

Поэтому мы добавим в таблицу дополнительный столбец и скопируем во все ячейки следующую формулу: =B2&C2. Если вы хотите сделать строку более читабельной, вы можете разделить объединенные значения пробелом: =B2&»»&C2. Затем вы можете использовать следующую формулу:

Читайте так же:
Как использовать ярлык для добавления новой строки / разрыва строки в ячейку в Excel?

=VLOOKUP(«Jeremy Hill Sweets»,$A$7:$D$18,4,FALSE) =ВПР(«Jeremy Hill Sweets»;$A$7:$D$18;4;ЛОЖЬ)

Руководство по функции ВПР в Excel

Пример 2: ВПР по двум критериям с просматриваемой таблицей на другом листе

Добавление данных непосредственно из рабочего листа или рабочей книги в другой рабочей книге Excel (таблица поиска) в основную таблицу является простым, если вы можете включить значение в формулу, которую вставляете в основную таблицу.

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

Таким образом, формула ВПР может быть следующей:

Чтобы сделать формулу более читабельной, можно указать имя отображаемой области, и формула будет выглядеть намного проще:

Руководство по функции ВПР в Excel

Для того чтобы формула работала, значения в крайнем левом столбце таблицы поиска должны быть объединены именно так, как показано в критериях поиска. Как видно выше, мы объединили значения и вставили между ними пробел. То же самое следует сделать в первом аргументе функции (B2&»»&C2).

Добавление вспомогательного столбца — не самое элегантное и не всегда лучшее решение. То же самое можно сделать и без вспомогательного столбца, но тогда вам понадобится гораздо более сложная формула с функциями INDEX и MATCH вместе взятыми.

Извлекаем 2-е, 3-е и т.д. значения, используя ВПР

Вы уже знаете, что ВПР может возвратить только одно совпадающее значение, точнее – первое найденное. Но как быть, если в просматриваемом массиве это значение повторяется несколько раз, и Вы хотите извлечь 2-е или 3-е из них? А что если все значения? Задачка кажется замысловатой, но решение существует!

Рассмотрим случай, когда один столбец таблицы содержит имена клиентов, а другой — купленные ими продукты. Найдите 2-й, 3-й и 4-й товары, которые купил клиент.

Читайте так же:
Как найти и подсчитать повторяющиеся ячейки / значения в одной строке в Excel?

Самый простой способ — добавить вспомогательный столбец перед столбцом Customer Name и заполнить его именами клиентов с указанием номера повторения каждого имени, например, John Doe1, John Doe2 и т.д. Трюк с нумерацией будет достигнут с помощью функции COUNTIF, поскольку имена клиентов находятся в столбце B:

Затем вы можете использовать обычную функцию UPP для поиска нужного заказа. Например:

    Находим 2-й товар, заказанный покупателем Dan Brown:

=VLOOKUP(«Dan Brown2»,$A$2:$C$16,3,FALSE) =ВПР(«Dan Brown2»;$A$2:$C$16;3;ЛОЖЬ)

=VLOOKUP(«Dan Brown3»,$A$2:$C$16,3,FALSE) =ВПР(«Dan Brown3»;$A$2:$C$16;3;ЛОЖЬ)

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

Руководство по функции ВПР в Excel

Если вы ищете только второе повторение, это можно сделать без вспомогательного столбца, составив более сложную формулу:

=IFERROR(VLOOKUP($F$2,INDIRECT(«$B$»&(MATCH($F$2,Table4[Customer Name],0)+2)&»:$C16″),2,FALSE),»») =ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ(«$B$»&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&»:$C16″);2;ИСТИНА);»»)

  • $F$2 — ячейка с именем клиента (не меняется, обратите внимание, что ссылка абсолютная);
  • $B$ — столбец с именем клиента;
  • Table4 — ваша таблица (также может быть обычным диапазоном);
  • $C16 — конечная ячейка вашей таблицы или диапазона.

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

Если вам нужен список всех совпадений, функция FFT в этом случае не поможет, так как она возвращает только одно значение за раз, без остановки. Но в Excel есть функция ИНДЕКС, которая упрощает эту задачу. В следующем примере вы можете увидеть, как будет выглядеть такая формула.

Извлекаем все повторения искомого значения

В отличие от VRP, VRP не может извлечь все повторяющиеся значения из просматриваемого диапазона. Для этого требуется несколько более сложная формула, состоящая из нескольких функций Excel, таких как INDEX, SMALL и ROW.

Данная формула находит все повторения значения из ячейки F2 в диапазоне B2:B16 и возвращает результаты в столбце C.

Руководство по функции ВПР в Excel

Чтобы получить представление о том, как это работает, давайте разберемся в деталях:

Читайте так же:
Как использовать точное и приблизительное совпадение vlookup в Excel?

Часть 1:

В результате работы функции IF мы получаем следующий горизонтальный массив:

Как найти, посчитать и убрать повторяющиеся значения в Эксель

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

В этой статье мы рассмотрим, как найти, выделить, удалить и подсчитать количество повторяющихся значений в Excel.

Как найти и выделить

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

Как найти, посчитать и убрать повторяющиеся значения в Эксель

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

Excel выделил ту же информацию розовым цветом. На диаграмме данные сравниваются не непосредственно в строках, а в столбцах. Именно из-за этого выделена ячейка «Саша В.». У таких студентов может быть много разных фамилий.

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

Как удалить

Для удаления дубликатов в Excel можно использовать следующие методы. Выделите заполненные ячейки, перейдите на вкладку «Данные», а затем нажмите кнопку «Удалить дубликаты».

В следующем окне установите флажок «Мои данные содержат заголовки», если вы выбрали таблицу с заголовками. Затем отметьте столбцы, в которых вы хотите найти повторения, и нажмите «OK».

Появится диалоговое окно, содержащее информацию о количестве найденных и удаленных одинаковых данных.

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

Читайте так же:
Как использовать командную кнопку для сохранения активного рабочего листа в виде файла PDF в Excel?

В следующем окне ячейки уже указаны в поле «Исходный диапазон». Отметьте маркером пункт «копировать результат в другое место» и в поле «Поместить результат в диапазон» укажите адрес одной ячейки, которая будет верхней левой в новой таблице. Установите флажок «Только уникальные записи» и нажмите «ОК».

Будет создана новая таблица, в которой не будет строк с дублированной информацией.

Если у вас есть большая исходная таблица, вы можете создать аналогичную таблицу с уникальными записями на другом листе Excel. Подробнее об этом вы можете прочитать в статье: Фильтры в Excel.

Как посчитать

Давайте создадим таблицу Excel для поиска и подсчета количества повторяющихся значений. К исходному столбцу добавляется столбец «Код», который мы заполняем «1»: в первые две ячейки ставим 1, 1, выделяем их и перетаскиваем вниз. По мере нахождения дублирующихся строк значение «Код» каждый раз увеличивается на единицу.

Выделите все вместе с заголовками, перейдите на вкладку «Вставка» и нажмите кнопку «Сводная таблица».

Подробнее о сводных таблицах в Excel читайте в этой статье.

В следующем окне диапазон уже задан, выберите «На новый лист» и нажмите «ОК».

Перетащите первые три заголовка в область «Названия строк», а поле «Код» — в область «Значения».

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

Для удобства выделите все значения в колонке «Сумма по полю кода» и отсортируйте их в порядке убывания.

Теперь вы можете находить, выбирать, удалять и даже подсчитывать число дубликатов в Excel для всех строк таблицы или только для выбранных столбцов.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector