Сопоставление 2-ух таблиц

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

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

Для хоть какой задачки в Excel практически постоянно есть больше 1-го решения (обычно 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) .

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

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

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

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

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

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

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

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

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

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

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

Сравните два столбца в Excel с помощью Vlookup (поиск совпадений)

ВПР для сопоставления 2-ух столбцов в Excel и поиска совпадений

Когда дело доходит до сопоставления 1-го элемента с иным в Excel, функции поиска — повелители, а ВПР — это рядовая формула для всех юзеров Excel. Не почти все из нас употребляют ВПР в полной мере, да, я говорю в полной мере, поэтому что есть намного больше, чем обычная ВПР, и мы можем созодать много остальных вещей с помощью ВПР. Итак, в данной для нас статье мы покажем для вас методы сопоставления 2-ух столбцов данных в Excel с помощью функции ВПР в Excel.

Сравните два столбца в Excel с помощью Vlookup (поиск совпадений)

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

• Когда данные 2-ух столбцов выровнены, как показано ниже, мы будем употреблять ВПР, чтоб узреть, включает ли столбец 1 столбец 2 либо нет.

• Нам необходимо сравнить, содержит ли «Перечень А» все значения «Перечня Б» либо нет, это можно создать с помощью функции ВПР. Поначалу откройте функцию ВПР.

• Из поиска значения будет значение C2 клеток , поэтому что мы сравниваем «А» содержит все значения «Перечень B» либо нет, так что избрать ссылку C2 клеток.

• В массиве таблицы будут значения ячеек «Перечень A», потому изберите спектр ячеек от A2 до A9 и сделайте его абсолютной ссылкой на ячейку.

• Дальше идет «Col Index Num», другими словами из избранного массива таблицы, из какого столбца нам нужен итог. Так как мы избрали лишь один столбец, наш «Col Index Num» будет равен 1.

• Поиск спектра — мы отыскиваем четкое совпадение, потому изберите FALSE в качестве аргумента либо введите 0 в качестве значения аргумента.

• Отлично, мы окончили с формулой, закройте скобку и нажмите кнопку ввода, чтоб получить итог.

Итак, если у нас есть «# N / A», это значит, что эти значения не есть в столбце «List A».

Но поглядите, что значение строчки 7 в «Перечне B» — это «Mind Tree», но в «List A» то же самое заглавие компании написано на сто процентов как «Mind Tree Software Co.» (ячейка A6). В таковых вариантах ВПР ничего не в состоянии сделать.

Частичный поиск с внедрением подстановочных символов

Как мы лицезрели выше, функция VLOOKUP просит, чтоб значение поиска было схожим как в «Перечне А», так и «Перечне Б». Даже если есть излишний пробел либо знак, он не может соответствовать результату. Но та же самая формула ВПР может соответствовать данным 2-ух столбцов, если мы предоставим подстановочные знаки для значения поиска.

Таковым образом, этот подстановочный символ представляет собой звездочку (*), при всем этом предоставляя значение поиска до и опосля значения поиска, которое нам нужно для объединения этого подстановочного знака.

Как вы сможете созидать выше, я соединил значение поиска со особым эмблемой подстановки звездочка (*) до и опосля значения поиска с помощью знака амперсанда (&).

Сейчас окончите формулу, выполнив уже показанные шаги.

Сейчас поглядите на результаты: в прошлом примере у нас были ошибки в строчках 2 и 7, но сейчас мы получили итог.

Как употреблять 5 продвинутых приёмов для сводных таблиц Excel

Неувязка, с которой мы все сталкиваемся — это не отсутствие данных; напротив — это большие объемы данных! Конкретно потому я выступаю за внедрение восхитительной функции Excel — Cводные таблицы, чтоб обобщить и проанализировать ваши данные.

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

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

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

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

Как использовать продвинутые приёмы сводных таблицы в Excel (куцее видео)

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

5 продвинутых приёмов сводных таблиц в Excel

Продолжайте читать для ознакомления о том, как употреблять каждую из этих 5 функций в приведенном ниже уроке, включая: Срезы, Временную шкалу, Табличный вид, Вычисляемые поля и Рекомендуемые Сводные таблицы. Давайте приступим.

1. Срезы

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

В этом примере я вставил срез для типа Item. Опосля того, как я нажимаю на Backpack, сводная таблица указывает лишь этот параметр в таблице.

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

Чтоб добавить срез, кликните в сводной таблице и найдите вкладку Анализ на ленте Excel.

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

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

2. Временная шкала

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

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

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

Чтоб добавить временную шкалу, удостоверьтесь, что вы избрали сводную таблицу (щелкните снутри неё), а потом нажмите клавишу Анализ > Вставить временную шкалу на ленте Excel. Во всплывающем окне отметьте столбец даты (либо несколько столбцов) и нажмите OK, чтоб сделать временную шкалу.

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

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

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

3. Табличный вид

Обычный вид сводных таблиц в Excel смотрится как водопад; при перетаскивании большего количества полей в строчки, Excel делает больше «слоев» в данных.

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

Изберите Конструктор > Макет отчета > Показать в табличной форме, чтоб работать с вашей сводной таблицей в виде обыкновенной таблицы.

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

Используйте табличный вид, чтоб создать сводную таблицу наиболее похожей с виду обычных строк и столбцов.

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

4. Вычисляемые поля

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

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

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

Используйте Анализ > Поля, элементы и наборы > Вычисляемое поле, чтоб вставить вычисленное поле в вашу сводную таблицу.

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

Чтоб вычислить общую стоимость заказа, я помножил имеющееся поле цены за единицу (Unit Price) на поле количества (Quantity).

Опосля прибавления наименования этого поля, я добавлю символ умножения *, а потом два раза кликаю по полному количеству (quantity). Давайте продолжим и нажмите ОК.

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

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

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

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

5. Рекомендуемые сводные таблицы

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

Перейдите в Вставка > Рекомендуемые сводные таблицы, чтоб испытать эту функцию.

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

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

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

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

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

Повторяйте и продолжайте обучение (педагогический процесс, в результате которого учащиеся под руководством учителя овладевают знаниями, умениями и навыками) (с ещё бо́льшими уроками по Excel)

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

Эти уроки для вас продвинуть ваши способности с Excel и сводным таблицам на последующий уровень. Проверь их:

• ExcelZoo имеет большенный обзор приёмов сводной таблицы в их статье, 10 уроков для освоения сводных таблиц (на британском).
• Мы в Envato Tuts+ разглядели сводные таблицы с помощью урока для новичков Как сделать свою первую сводную таблицу в Microsoft Excel.
• Для наиболее обычного введения в Microsoft Excel ознакомьтесь с нашей учебной серией Как создать и употреблять формулы в Excel (Учебный лагерь для начинающих).

Что вы всё ещё желаете выяснить о сводных таблицах? Сообщите мне ваши идеи либо вопросцы в комментах ниже этого урока.