Check-moscow.ru

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

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

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

doc выровнять дубликаты 1

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

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

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

Введите эту формулу: =IF(ISNA(MATCH(A2,$C$2:$C$12,0)),»,INDEX($C$2:$C$12,MATCH(A2,$C$2:$C$12,0)) в пустую ячейку рядом с первым столбцом, например, B2, а затем перетащите дескриптор заполнения на ячейки, к которым вы хотите применить эту формулу, и все значения в столбце A и столбце C будут выровнены с соответствующими значениями в столбце A, см. экран :

doc выровнять дубликаты 2

Примечание: В приведенной выше формуле A2 — это значение ячейки первого столбца, который вы хотите сравнить, а C2:C12 — это данные во втором столбце, с которыми вы хотите сравнить. Ссылки на ячейки можно изменять по своему усмотрению.

Функция Select Same Cells and Different Cells в Kutools for Excel дает возможность быстро выбрать и выделить ячейки из двух столбцов, которые одинаковые или разные.

doc выровнять дубликаты 7

Дополнения для Excel от Kutools: 300 дополнений Excel, которые вы можете бесплатно протестировать в течение 30 дней. Скачайте и бесплатно опробуйте прямо сейчас!

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

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

Читайте так же:
Как вставить разрывы страниц при изменении значения в Excel?

doc выровнять дубликаты 3

1. Удерживая нажатой ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Чтобы вставить модуль, нажмите Вставка > Модули и вставьте следующий код.

Код V BA: Выравнивание повторяющихся или совпадающих значений в двух колонках

Здесь E1 означает первую строку вашего первого сравниваемого столбца, а E означает первую букву вашего столбца данных. При этом два столбца должны быть соседними.

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

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

Если у вас есть Kutools for Excel, с его функцией Select Same and Different Cells вы можете быстро сравнить данные в двух столбцах и выбрать дубликаты ячеек в одном столбце. Кроме того, для дубликатов можно изменить цвет фона или цвет шрифта.

Вот что нужно сделать после установки Kutools for Excel

Удерживая клавишу Ctrl, выделите два столбца данных отдельно, затем нажмите Cthulz > Select > Select equal cells and different cells, см. снимок экрана :

3. Скрытие с помощью контекстного меню

doc выровнять дубликаты 5

3. Затем нажмите кнопку Ok, и повторяющиеся значения в столбце A и столбце B будут выбраны и выделены в столбце A, как показано на следующем снимке экрана:

Сравнение двух таблиц

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

Поиск отличий в двух таблицах в Excel

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

Читайте так же:
Как добавить или переместить метки данных в диаграмме Excel?

В Excel почти всегда есть более одного решения любой проблемы (обычно 4-5). Для решения нашей задачи можно использовать различные подходы:

  • Функция VLOOKUP — поиск наименований товаров из нового прайс-листа в старом и отображение старой цены рядом с новой, затем фиксация различий
  • Объединение двух списков в один, а затем создание электронной таблицы для отображения различий
  • Использование надстройки Power Query для Excel

Давайте пройдемся по ним по очереди.

Способ 1. Сравнение таблиц функцией ВПР (VLOOKUP)

Если вы еще не знакомы с этой замечательной функцией, сначала ознакомьтесь с ней здесь и прочитайте или посмотрите видеоинструкцию — вы сэкономите себе несколько лет.

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

Поиск отличий с ВПР

Эти продукты имеют ошибку #N/D, т.е. они не появляются в исходном списке. были добавлены. Кроме того, изменения цен хорошо видны.

Преимущества этого метода: простой и незамысловатый, как говорится, «классика жанра». Работает во всех версиях Excel.

Есть и недостатки. Вам придется выполнять тот же поиск в обратном направлении, если вы хотите найти товары, добавленные в новый прайс-лист. подтянуть новые цены к старому прайс-листу с помощью VPR. Если завтра размер таблиц изменится, формулы придется корректировать. И все это будет очень медленно на действительно больших таблицах (>100k строк).

Способ 2. Сравнение таблиц с помощью сводной

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

Объединяем таблицы

Теперь на основе созданной таблицы создайте поворотную таблицу с помощью Вставка — Поворотная таблица. Поместим поле «Товар» в область строк, поле «Цена» — в область столбцов, а поле «Цена» — в область значений:

Читайте так же:
Как выполнить vlookup и вернуть несколько значений из раскрывающегося списка?

Сводная

Вы видите, что сводная таблица автоматически формирует общий список всех продуктов из старого и нового прайс-листов (без повторений!) и сортирует продукты в алфавитном порядке. Добавление продуктов (у них нет старой цены), удаление продуктов (у них нет новой цены) и изменение цены хорошо видны.

Большие итоги не имеют смысла в такой таблице и могут быть отключены на вкладке Макет — Большие итоги — Отключить для строк и столбцов (Макет — Большие итоги) .

Если цены (но не количество товаров!) изменятся, вы можете просто щелкнуть правой кнопкой мыши на созданной сводке и обновить ее.

Плюсы: для больших таблиц этот подход на порядок быстрее, чем VPR.

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

Способ 3. Сравнение таблиц с помощью Power Query

Дополнение Microsoft Excel Power Query позволяет импортировать данные практически из любого источника, а затем преобразовывать эту информацию по своему усмотрению. В Excel 2016 эта надстройка уже встроена по умолчанию на вкладке «Данные», но для Excel 2010-2013 ее нужно скачать отдельно с сайта Microsoft и установить — получите новую вкладку Power Query.

Прежде чем загрузить наши прайс-листы в Power Query, нам нужно преобразовать их в смарт-таблицы. Для этого выделите диапазон данных и нажмите Ctrl + T на клавиатуре или выберите вкладку Главная — Формат как таблица на ленте. Названия создаваемых таблиц можно настроить на вкладке Designer (я оставлю стандартные Table1 и Table2, которые используются по умолчанию).

Чтобы загрузить старый прайс-лист в Power Query, нажмите кнопку Из таблицы/диапазона на вкладке Данные или Power Query (в зависимости от вашей версии Excel). Нажмите кнопку Закрыть и загрузить, чтобы вернуться в Power Query, оставив открытым Excel. К (Закрыть и загрузить — закрыть и загрузить к.) :

Читайте так же:
Как вставить имя файла или путь в ячейку / верхний или нижний колонтитул в Excel?

Закрыть и загрузить

. и затем в появившемся окне выберите Только подключение .

Затем проделайте то же самое с новым прейскурантом.

Теперь создадим третий запрос, который объединит и сравнит данные из двух предыдущих. Для этого выберите в Excel вкладку Данные — Получить данные — Объединить запросы — Объединить или нажмите кнопку Объединить на вкладке Power Query.

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

Слияние запросов

Если вы нажмете кнопку OK, вы увидите таблицу из трех столбцов, с двойной стрелкой в заголовке для раскрытия содержимого вложенных таблиц:

Разворачиваем столбцы

В результате будет получена комбинация данных из этих двух таблиц:

Объединение таблиц

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

Переименованные столбцы

А теперь самое интересное. Чтобы добавить условный столбец, нажмите кнопку Условный столбец на вкладке Добавить столбец. После этого в появившемся окне введите несколько тестовых условий с соответствующими выходными значениями:

Условный столбец

Остается только нажать OK и выгрузить полученный отчет в Excel с помощью той же кнопки Закрыть и загрузить на вкладке Главная:

Результат сравнения

Более того, если в будущем в прайс-листах произойдут какие-либо изменения (добавятся или удалятся строки, изменятся цены и т.д.), то нам нужно будет только обновить запросы, нажав Ctrl + Alt + F5 на вкладке Данные. По этой причине нам достаточно обновить запросы, нажав Ctrl + Alt + F5 или выбрав Refresh All на вкладке Data.

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

Требования Требуется установленное дополнение Power Query (в Excel 2010-2013) или Excel 2016. Имена столбцов в исходных данных не должны быть изменены, иначе при попытке обновить запрос вы получите ошибку типа «Column so-and-so cannot be found!».

голоса
Рейтинг статьи
Читайте так же:
Как заполнить фигуру прозрачным цветом фона в Excel?
Ссылка на основную публикацию
Adblock
detector