Отбор повторяющихся значений (дубликатов) со значениями из соседнего столбца в EXCEL
history 22 апреля 2013 г.
- Группы статей
- Повторяющиеся
- Отбор на основании повторяемости
- Вывод отобранных значений в отдельный диапазон
- Условное форматирование
Имея список с повторяющимися текстовыми значениями, создадим список, состоящий только из дубликатов . А из соседнего столбца, выведем соответствующие числовые значения.
Пусть исходная таблица содержит 2 столбца: текстовый – Список регионов (в столбце А ) и числовой — Объем продаж (в столбце B ). Столбец Список регионов содержит повторяющиеся значения.
Названия регионов в исходной таблице, которые НЕ имеют повторов и соответственно НЕ должны быть выведены, выделены цветом с помощью Условного форматирования .
Задача
Создадим таблицу, в которой в столбце с перечнем регионов будут содержаться только те значения из исходной таблицы, которые имеют повторы (т.е. те, которые НЕ выделены Условным форматированием в исходной таблице), а рядом выведем соответствующие объемы продаж.
Решение
Используем идеи, изложенные в статье Отбор повторяющихся значений . Но, теперь не будем производить группировку дубликатов, а выведем их все вместе с соответствующими значениями из соседнего столбца (см. файл примера ). Для создания списка дубликатов введем в ячейку D2 формулу массива :
=ЕСЛИОШИБКА(ИНДЕКС(Регионы;НАИМЕНЬШИЙ( ЕСЛИ(СЧЁТЕСЛИ(Регионы;Регионы)>1;СТРОКА(Регионы)-МИН(СТРОКА(Регионы))+1;""); СТРОКА(A1)));"")
Динамический диапазон Регионы представляет собой исходный список с названиями регионов, причем его границы изменяются в зависимости от количества числа введенных значений в столбец А (пропуски не допускаются). Для этого использована формула =СМЕЩ(пример!$A$2;;;СЧЁТЗ(пример!$A$2:$A$26)) . Аналогичный диапазон Продажи создан для списка Объем продаж . Теперь при вводе новых значений в столбцы А и В , таблица с дубликатами будет обновляться автоматически.
Соответствующие дубликатам Объемы продаж выведены в столбце E с помощью аналогичной формулы массива .
Однако, столбец дубликатов не сортирован, что затрудняет анализ списка. Для сортировки таблицы по Объему продаж создадим 2 служебных столбца:
- Столбец G содержит формулу =ЕСЛИ(D2<>"";СЧЁТЕСЛИ(Дубликаты;" , которая подсчитывает число значений меньше и равных текущему;
- Столбец Н содержит формулу =ЕСЛИ(D2<>"";ЕСЛИ(СЧЁТЕСЛИ($G$2:G2;G2)=1;G2;G2+СЧЁТЕСЛИ($G$2:G2;G2)-1);"") для правильной нумерации дубликатов.
Теперь записав в столбец J формулу массива : =ЕСЛИОШИБКА(ИНДЕКС(Дубликаты;ПОИСКПОЗ(СЧЁТЗ(Дубликаты)-(СТРОКА()-СТРОКА($H$2));ДубликатыСорт;0));"")
Получим отсортированный список дубликатов. Аналогичную формулу запишем в столбце K для вывода соответствующих значений из смежного столбца. В результате получим новую таблицу.
Тестируем
1. Введите в ячейку А11 новый регион — Китай 2. Введите объем продаж — 100 3. Введите в А12 — Китай 4. Введите объем продаж — 500 5. В соседней таблице справа в ячейках D8 и D9 будут автоматически выведены 2 названия региона Китай с соответствующими объемами продаж (т.к. название региона Китай повторяется).
6. В таблице с отсортированным списком повторяющихся регионов будут также выведены 2 названия региона Китай с соответствующими объемами продаж, но уже с учетом сортировки по алфавиту (в обратном порядке).
СОВЕТ: Другим подходом к решению этой задачи является использование Сводных таблиц .
Как объединить ячейки с одинаковым значением в Excel
В приходных накладных или прайсах очень часто повторяются некоторые значения ячеек в разных позициях. Если отсортировать эти значения не всегда удобно их визуально анализировать. Например, названия поставщиков могут быть очень похожими и какие данные к ним относятся легко спутать.
Как выделить одинаковые ячейки группами
Допустим мы имеем список поставщиков:
Перед тем как найти повторяющиеся ячейки в Excel, отсортируем поставщиков по их идентификатору. Переходим в ячейку A2 и выбираем на закладке «ДАННЫЕ» в разделе «Сортировка и фильтр» инструмент «Сортировка от А до Я».
Чтобы автоматически отметить повторяющиеся ячейки и разделить их линиями на группы воспользуемся условным форматированием:
- Выделите диапазон A2:A11 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматированных ячеек:».
- В поле для ввода формулы вводим следующе значение: =$A2<>$A3
- Щелкните на кнопку «Формат», на вкладке «Граница» следует задать оформление для нижних границ строк. И ОК.
В результате получаем эффект как отображено на рисунке.
Нам удалось объединить и выделить повторяющиеся ячейки в Excel. Теперь в приходной накладной все отсортированные поставщики визуально разграничены линией друг от друга.
Так как мы сортировали поставщиков по их идентификатору в формуле мы зафиксировали смешанной ссылкой столбец листа $A. Если значения в соседних ячейках столбца $A равные между собой тогда формула возвращает значения ЛОЖЬ и форматирование границе не применяется. Но если верхнее значение неравно (оператор <>) нижнему значению тогда формула возвращает значение ИСТИНА и применяется форматирования нижней границы целой строки (так как в смешанной ссылке номер строки не есть абсолютным, а является относительным адресом).
Полезный совет! Если нужно разграничить данные не по первому столбцу таблицы, по любому другому, тогда соответственно отсортируйте и просто укажите адрес столбца. Например, разграничим по повторяющимся датам, а не по поставщикам. Для этого сначала сортируем данные по датам, а потом используем условное форматирование немного изменив формулу: =$C2<>$C3
Теперь список сгруппирован по одинаковым датам.