Как найти значения из одной таблицы в другой эксель
Перейти к содержимому

Как найти значения из одной таблицы в другой эксель

  • автор:

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

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

Описание

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

Создание образца листа

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

Введите значение, которое вы хотите найти, в ячейку E2. Вы можете ввести формулу в любую пустую ячейку на том же листе.

Определения терминов

В этой статье для описания встроенных функций Excel используются указанные ниже условия.

Определение

Вся таблица подстановки

Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».

Просматриваемый_массив
-или-
Лукуп_вектор

Диапазон ячеек, которые содержат возможные значения подстановки.

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

3 (третий столбец в инфо_таблица)

Ресулт_аррай
-или-
Ресулт_вектор

Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор.

Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение.

Это ссылка, на основе которой вы хотите основать смещение. Топ_целл должен ссылаться на ячейку или диапазон смежных ячеек. В противном случае функция СМЕЩ возвращает #VALUE! значение ошибки #ИМЯ?.

Число столбцов, находящегося слева или справа от которых должна указываться верхняя левая ячейка результата. Например, значение «5» в качестве аргумента Оффсет_кол указывает на то, что верхняя левая ячейка ссылки состоит из пяти столбцов справа от ссылки. Оффсет_кол может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки).

Функции

LOOKUP ()

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

Ниже приведен пример синтаксиса формулы подСТАНОВКи.

= Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)

Следующая формула находит возраст Марии на листе «образец».

= ПРОСМОТР (E2; A2: A5; C2: C5)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе подстановки (столбец A). Формула затем соответствует значению в той же строке в векторе результатов (столбец C). Так как «Мария» находится в строке 4, функция Просмотр возвращает значение из строки 4 в столбце C (22).

Примечание. Для функции Просмотр необходимо, чтобы таблица была отсортирована.

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

ВПР ()

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

Ниже приведен пример синтаксиса формулы ВПР :

= ВПР (искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)

Следующая формула находит возраст Марии на листе «образец».

= ВПР (E2; A2: C5; 3; ЛОЖЬ)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Формула затем совпадет со значением в той же строке в Колумн_индекс. В этом примере используется «3» в качестве Колумн_индекс (столбец C). Так как «Мария» находится в строке 4, функция ВПР возвращает значение из строки 4 В столбце C (22).

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

INDEX () и MATCH ()

Вы можете использовать функции индекс и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при использовании поиска или функции ВПР.

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

= Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)

Следующая формула находит возраст Марии на листе «образец».

= ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем он будет соответствовать значению в той же строке в столбце C. Так как «Мария» находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).

Обратите внимание Если ни одна из ячеек в аргументе «число» не соответствует искомому значению («Мария»), эта формула будет возвращать #N/А.
Чтобы получить дополнительные сведения о функции индекс , щелкните следующий номер статьи базы знаний Майкрософт:

СМЕЩ () и MATCH ()

Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.

Ниже приведен пример синтаксиса, объединяющего смещение и сопоставление для достижения того же результата, что и функция Просмотр и ВПР.

= СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)

Эта формула находит возраст Марии на листе «образец».

= СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Формула затем соответствует значению в той же строке, но двум столбцам справа (столбец C). Так как «Мария» находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).

Чтобы получить дополнительные сведения о функции СМЕЩ , щелкните следующий номер статьи базы знаний Майкрософт:

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

Совет: Попробуйте использовать новые функции XLOOKUP и XMATCH , улучшенные версии функций, описанных в этой статье. Эти новые функции работают в любом направлении и по умолчанию возвращают точные совпадения, что делает их проще и удобнее в использовании, чем их предшественники.

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

Функции ВПР и HLOOKUP , а также INDEX и MATCH являются одними из наиболее полезных функций в Excel.

Примечание: Функция мастера подстановки больше не доступна в Excel.

Ниже приведен пример использования функции ВПР.

=ВПР(B2;C2:E7,3,ИСТИНА)

В этом примере B2 является первым аргументом — элементом данных, который должен работать функции. Для ВПР этот первый аргумент является значением, которое требуется найти. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, например smith или 21000. Второй аргумент — это диапазон ячеек C2-:E7, в котором выполняется поиск нужного значения. Третий аргумент — это столбец в диапазоне ячеек, содержащий нужное значение.

Четвертый аргумент необязателен. Введите TRUE или FALSE. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести значение FALSE, функция будет соответствовать значению, предоставленному первым аргументом. Другими словами, если оставить четвертый аргумент пустым или ввести TRUE, вы получаете большую гибкость.

В этом примере показано, как работает функция. При вводе значения в ячейку B2 (первый аргумент) ВПР выполняет поиск ячеек в диапазоне C2:E7 (2-й аргумент) и возвращает ближайшее приблизительное совпадение из третьего столбца в диапазоне, столбца E (3-й аргумент).

Типичный пример использования функции ВПР

Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.

Если вы знакомы с VLOOKUP, функция HLOOKUP также проста в использовании. Вы вводите те же аргументы, но выполняется поиск по строкам, а не по столбцам.

Использование INDEX и MATCH вместо ВПР

При использовании ВПР существуют определенные ограничения: функция ВПР может искать значение только слева направо. Это означает, что столбец, содержащий искать значение, всегда должен располагаться слева от столбца, содержащего возвращаемое значение. Теперь, если электронная таблица не создана таким образом, не используйте VLOOKUP. Вместо этого используйте сочетание функций INDEX и MATCH.

В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения «Воронеж» в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.

Использование функций ИНДЕКС и ПОИСКПОЗ для поиска значения

Дополнительные примеры использования INDEX и MATCH вместо ВПР см. в статье , https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Билл Джелен (Bill Jelen), Microsoft MVP.

Попробуйте попрактиковаться

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

Пример ВПР на работе

Скопируйте следующие данные в пустую электронную таблицу.

Совет: Перед вставкой данных в Excel задайте ширину столбцов от A до C равным 250 пикселям и нажмите кнопку Обтекать текст (вкладка Главная , группа Выравнивание ).

Как сделать ВПР в Excel: пошаговая инструкция со скриншотами

Как перенести данные из одной таблицы в другую, если строки идут не по порядку? Разбираемся на примере каталога авто — переносим цены.

Иллюстрация: Meery Mary для Skillbox Media

Ксеня Шестак

Ксеня Шестак

Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.

ВПР (Vlookup, или вертикальный просмотр) — поисковая функция в Excel. Она находит значения в одной таблице и переносит их в другую. Функция ВПР нужна, чтобы работать с большими объёмами данных — не нужно самостоятельно сопоставлять и переносить сотни наименований, функция делает это автоматически.

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

Зачем нужна функция ВПР и когда её используют

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

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

Просто скопировать и вставить эту колонку не получится. Искать каждое авто вручную и переносить цены — долго.

ВПР автоматически сопоставит названия автомобилей в двух таблицах. Функция скопирует цены из каталога в список забронированных машин. Так напротив каждого клиента будет стоять не только марка автомобиля, но и цена.

Ниже пошагово и со скриншотами разберёмся, как сделать ВПР для этих двух таблиц с данными.

Важно!

ВПР может не работать, если таблицы расположены в разных файлах. Тогда лучше собрать данные в одном файле, на разных листах.

Шаг 1

Готовимся к работе с функцией ВПР в Excel

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

Подробнее о том, как определить все эти значения, поговорим ниже. А пока разберёмся на примере с продажей авто, где найти функцию ВПР в Excel и с чего начать работу.

Сначала нужно построить функцию. Для этого выделяем ячейку, куда функция перенесёт найденное значение.

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

Дальше открываем окно для построения функции ВПР. Есть два способа сделать это. Первый — перейти во вкладку «Формулы» и нажать на «Вставить функцию».

Второй способ — нажать на «fx» в строке ссылок на любой вкладке таблицы.

Справа появляется окно «Построитель формул». В нём через поисковик находим функцию ВПР и нажимаем «Вставить функцию».

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

Шаг 2

Заполняем аргументы функции

Последовательно разберём каждый аргумент: искомое значение, таблица, номер столбца, интервальный просмотр.

Искомое значение — название ячейки с одинаковыми данными для обеих таблиц, по которым функция будет искать данные для переноса. В нашем примере это модель авто. Функция найдёт модель в таблице с каталогом авто, возьмёт оттуда стоимость и перенесёт в таблицу с клиентами.

Порядок действий, чтобы указать значение, выглядит так:

  • Ставим курсор в окно «Искомое значение» в построителе формул.
  • Выбираем первое значение столбца «Марка, модель» в таблице с клиентами. Это ячейка A2.

Выбранное значение переносится в построитель формул и одновременно появляется в формуле строки ссылок: fx=ВПР(A2).

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

В нашем случае нужно перенести цены автомобилей. Поэтому в диапазон обязательно нужно включить столбцы «Марка, модель» (искомое значение) и «Цена, руб.» (переносимое значение).

Важно!

Для правильной работы ВПР искомое значение всегда должно находиться в первом столбце диапазона. У нас искомое значение находится в ячейке A2, поэтому диапазон должен начинаться с A.

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

  • Ставим курсор в окно «Таблица» в построителе формул.
  • Переходим в таблицу «Каталог авто».
  • Выбираем диапазон, в который попадают столбцы «Марка, модель» и «Цена, руб.». Это A2:E19.
  • Закрепляем выбранный диапазон. На Windows для этого выбираем значение диапазона в строке ссылок и нажимаем клавишу F4, на macOS — выбираем значение диапазона в строке ссылок и нажимаем клавиши Cmd + T. Закрепить диапазон нужно, чтобы можно было протянуть функцию вниз и она сработала корректно во всех остальных строках.

Выбранный диапазон переносится в построитель формул и одновременно появляется в формуле строки ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19).

Номер столбца — порядковый номер столбца в первой таблице, в котором находится переносимое значение. Считается по принципу: номер 1 — самый левый столбец, 2 — столбец правее и так далее.

В нашем случае значение для переноса — цена — находится в пятом столбце слева.

Чтобы задать номер, установите курсор в окно «Номер столбца» в построителе формул и введите значение. В нашем примере это 5. Это значение появится в формуле в строке ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5).

Интервальный просмотр — условное значение, которое настроит, насколько точно сработает функция:

  • Если нужно точное совпадение при поиске ВПР, вводим 0.
  • Если нужно приближённое соответствие при поиске ВПР, вводим 1.

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

Ставим курсор в окно «Интервальный просмотр» в построителе формул и вводим значение: 0. Одновременно это значение появляется в формуле строки ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5;0). Это окончательный вид функции.

Шаг 3

Получаем результат ВПР

Чтобы получить результат функции, нажимаем кнопку «Готово» в построителе формул. В выбранной ячейке появляется нужное значение. В нашем случае — цена первой модели авто.

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

Получилась таблица с ценами — можно звонить клиентам и сообщать о стоимости авто. Данные перенесены без ошибок, а потратили мы на это несколько минут.

Что делать, если нужен поиск сразу по двум критериям

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

Например, у нас несколько одинаковых моделей с разным цветом.

И по традиции есть таблица с клиентами, которые эти модели забронировали.

Если идти по классическому пути ВПР, получится такая функция: fx=ВПР(A29;’каталог авто’!$A$29:$E$35;5;0). В таком виде ВПР найдёт первую совпавшую модель и подтянет её стоимость. Параметр цвета не будет учтён.

Соответственно, цены у всех Nissan Juke будут 1 850 000 рублей, у всех Subaru Forester — 3 190 000 рублей, у всех Toyota C-HR — 2 365 000 рублей.

Поэтому в этом варианте нужно искать стоимость авто сразу по двум критериям — модель и цвет. Для этого нужно изменить формулу вручную. В строке ссылок ставим курсор сразу после искомого значения.

Дописываем в формулу фразу ЕСЛИ(‘каталог авто’!$B$29:$B$35=B29, где:

  • ‘каталог авто’!$B$29:$B$35 — закреплённый диапазон цвета автомобилей в таблице, откуда нужно перенести данные. Это весь столбец с ценами.
  • B29 — искомое значение цвета автомобиля в таблице, куда мы переносим данные. Это первая ячейка в столбце с цветом — дополнительным параметром для поиска.

Итоговая функция такая: fx=ВПР(A29;ЕСЛИ(‘каталог авто’!$B$29:$B$35=B29;’каталог авто’!$A$29:$E$35);5;0). Теперь значения цен переносятся верно.

Как использовать ВПР в «Google Таблицах»? В них тоже есть функция Vlookup, но нет окна построителя формул. Поэтому придётся прописывать её вручную. Перечислите через точку с запятой все аргументы и не забудьте зафиксировать диапазон. Для фиксации поставьте перед каждым символом значок доллара. В готовой формуле это будет выглядеть так: =ВПР(A2;’Лист1′!$A$2:$C$5;3;0).

Другие материалы Skillbox Media для менеджеров

  • Статья: что такое матрица БКГ и как она помогает определить, какие проекты стоит развивать
  • Опрос руководителей о методах тайм-менеджмента: как сделать так, чтобы сотрудники всё успевали
  • Подборка из десяти неочевидных ошибок руководителя команды на удалёнке
  • Разбор инструмента: ищем первопричины проблем с помощью рыбьих костей Исикавы
  • Статья про управление персоналом: что это такое и зачем оно нужно

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

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

Имеем две таблицы (например, старая и новая версия прайс-листа), которые надо сравнить и оперативно найти отличия:
С ходу видно, что в новом прайсе что-то добавилось (финики, чеснок. ), что-то пропало (ежевика, малина. ), у каких-то товаров изменилась цена (инжир, дыня. ). Нужно быстро найти и вывести все эти изменения. Для любой задачи в Excel почти всегда есть больше одного решения (обычно 4-5). Для нашей проблемы можно использовать много разных подходов:

  • функцию ВПР (VLOOKUP) — искать названия товаров из нового прайс-листа в старом и выводить старую цену рядом с новой, а потом ловить отличия
  • объединить два списка в один и построить по нему потом сводную таблицу, где наглядно будут видны отличия
  • использовать надстройку Power Query для Excel

Давайте разберем их все последовательно.

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

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

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

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

Те товары, напротив которых получилась ошибка #Н/Д — отсутствуют в старом списке, т.е. были добавлены. Изменения цены также хорошо видны.

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

Минусы тоже есть. Для поиска добавленных в новый прайс товаров придется делать такую же процедуру в обратную сторону, т.е. подтягивать с помощью ВПР новые цены к старому прайсу. Если размеры таблиц завтра поменяются, то придется корректировать формулы. Ну, и на действительно больших таблицах (>100 тыс. строк) все это счастье будет прилично тормозить.

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

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

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

Теперь на основе созданной таблицы создадим сводную через Вставка — Сводная таблица (Insert — Pivot Table) . Закинем поле Товар в область строк, поле Прайс в область столбцов и поле Цена в область значений:

Сводная

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

Общие итоги в такой таблице смысла не имеют, и их можно отключить на вкладке Конструктор — Общие итоги — Отключить для строк и столбцов (Design — Grand Totals) .

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

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

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

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

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

Перед загрузкой наших прайс-листов в Power Query их необходимо преобразовать сначала в умные таблицы. Для этого выделим диапазон с данными и нажмем на клавиатуре сочетание Ctrl + T или выберем на ленте вкладку Главная — Форматировать как таблицу (Home — Format as Table) . Имена созданных таблиц можно подкорректировать на вкладке Конструктор (я оставлю стандартные Таблица1 и Таблица2, которые получаются по-умолчанию).

Загрузите старый прайс в Power Query с помощью кнопки Из таблицы/диапазона (From Table/Range) с вкладки Данные (Data) или с вкладки Power Query (в зависимости от версии Excel). После загрузки вернемся обратно в Excel из Power Query командой Закрыть и загрузить — Закрыть и загрузить в. (Close & Load — Close & Load To. ) :

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

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

Повторите то же самое с новым прайс-листом.

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

В окне объединения выберем в выпадающих списках наши таблицы, выделим в них столбцы с названиями товаров и в нижней части зададим способ объединения — Полное внешнее (Full Outer) :

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

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

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

В итоге получим слияние данных из обеих таблиц:

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

Названия столбцов в шапке лучше, конечно, переименовать двойным щелчком на более понятные:

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

А теперь самое интересное. Идем на вкладку Добавить столбец (Add Column) и жмем на кнопку Условный столбец (Conditional Column) . А затем в открывшемся окне вводим несколько условий проверки с соответствующими им значениями на выходе:

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

Останется нажать на ОК и выгрузить получившийся отчет в Excel с помощью все той же кнопки Закрыть и загрузить (Close & Load) на вкладке Главная (Home) :

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

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

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

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

Ссылки по теме

  • Как собрать данные из всех файлов Excel в заданной папке с помощью Power Query
  • Как найти совпадения между двумя списками в Excel
  • Слияние двух списков без дубликатов

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *