Как сравнить два файла в excel на различия впр

Обновлено: 06.07.2024

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

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

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

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

Сказать, что я был удивлён – это значит ничего не сказать. Я лицезрел настоящее чудо.

Это была потрясающая демонстрации силы автоматизации.

Функция ВПР в Экселе одинаково нужна и маркетологом, и логистам, и закупщикам – всем тем, кто работает с таблицами данных, это просто Must Have.

Функция ВПР в Экселе – быстрый перенос данных

Самое простое применение функция ВПР это быстрый перенос данных из одной таблицы в другую.

Например, у вас есть большой прайс на 500 позиций и запрос от покупателя, скажем на 50 позиций (в реальности и прайс и запрос могут быть гораздо больше, но принцип от этого не меняется).

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

Итак, у нас в прайсе 500 позиций. Позиции обозначаются следующим образом, буквами обозначается вид позиции, а цифрами модификация.

Например, «Стул_1» и «Стул_21» это два совершенно разных стула.

Цены в прайсе указаны для примера и вряд ли имеют отношение к реальным ценам.

В ООО «ЫкэА» пришел запрос от «Петровича».

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

Однако это нас не страшит, во-первых, у нас есть ВПР, во-вторых мы и не такое видали.

Вот собственно и сам запрос:

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

Нам не хочется терять такого клиента и мы практически мгновенно открываем прайс:

Получается у нас должно быть открыто два файла (две книги в Эксель). Запрос от Петровича и Прайс.

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

Для этого перейдем в таблицу запроса и в первой ячейке столбца «Цены» (D4) введем «=впр» и два раза кликнем на значок функции:

Сразу же после этого, в строке формулы нужно поставить курсор внутри надписи ВПР и нажать Fx, перед вами появится окно с аргументами функции ВПР:

В аргументах функции вы говорите Экселю что и где нужно искать:

Искомое значение — это значение (в данном случае наименование), цену которого вы хотите найти в прайсе. Соответственно кликайте на первую ячейку столбца «Наименование».

Далее, сразу переходите в «Прайс»:

Теперь в аргументах функции заполните следующие поля:

Таблица — выделяете столбцы, которые содержат искомые наименования и цены, таким образом, чтобы наименования были крайним левым столбцом.

Так работает функция ВПР — ищет искомые значения в крайнем левом столбце (для ВПР это столбец №1). Когда ВПР находит искомое значение он начинает смотреть правее, в тот столбец, который вы указали в «Номере столбца».

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

Интервальный просмотр — ставьте 0. Ноль обозначает точное соответствие.

После заполнения аргументов функции нажимайте «Ок» и если всё сделано верно, то в столбце «Цена» (файл «Запрос от Петровича»), появится цена.

Вам нужно протянуть цены на оставшиеся ячейки:

Коллеги, вот и всё, вы овладели функцией ВПР.

Очень важное замечание!

Обратите внимание на то, что сейчас мы работали в двух разных файлах (книгах).

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

Делает это он при помощи значка $, который проставляет перед столбцами и строками таблицы.

Это позволяет не съезжать формуле когда вы протягиваете её вниз. Это очень актуально когда вы работаете в рамках одного листа или одной книги (в этом случае Эксель автоматически Не закрепляет ячейки).

Давайте посмотрим что получиться если протянуть формулу «без закрепления»:

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

Очень важное замечание №2

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

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

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

Для этого нужно выделить столбец с формулами, нажать Ctrl+C и в левом верхнем углу выбрать «Вставить» — «Вставить значения».

Для тех кто не любит изучать картинки, я записал небольшое видео в котором показываю всё то, что мы проговорили выше (кроме вставки значений):

Видео — «Быстрый перенос данных с помощью функции ВПР в Экселе»

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

Это очень актуально для тех кто работает в закупках и отправляет заказы поставщику.

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

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

Функция ВПР в Экселе – сравнение двух таблиц

Итак, у вас есть «Заказ поставщику» (1) и ответ поставщика в виде «Счёта на оплату» (2).

Для удобства восприятия я разместил их на одном листе:

Ваша задача сверить количество позиций и их цены.

Для начала проверим все ли позиции и по правильной ли цене указал в счёте поставщик.

Для этого нужно из Счёта перетянуть данные в Заказ при помощи функции ВПР.

Перед «перетяжкой», в таблицу «Заказ поставщику» нужно добавить два «сравнительных» столбца:

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

Обратите внимание, я закрепил диапазоны ячеек.

Теперь когда данные перенесены, нужно их сравнить, для это необходимо добавить еще два столбца (Разница 1 и Разница 2):

В столбце «Разница 1» нужно вычесть от исходного количества (D4) количество в счёте (E4).

В столбце «Разница 2» нужно вычесть от исходной цены (G4) цену в счёте (H4).

Таким образом мы сможем увидеть разницу и в количестве и в цене.

Если значение «0», то значит всё хорошо и данные одинаковые.

Если значение плюсовое (например «+3»), то это значит что в счёте не хватает 3 штук.

Если значение отрицательное, это значит, что нам пытаются «впихнуть» лишнее.

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

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

Друзья, вот так мы проверили насколько соответствует Заказ, полученному Счёту и казалось бы что это всё что необходимо для счастливой жизни. Однако это не совсем так.

Нужно еще проверить соответствие Счёта, отправленному заказу, на предмет лишних позиций.

Вдруг хитрый поставщик, среди сотни позиций решил нам скрытно что-то «допродать».

Для этого в «Счёт на оплату» нужно добавить столбец «Кол/во в заказе» и «отвепээрить» туда значения из столбца «Количество» Заказа поставщику.

Теперь всё тоже самое продемонстрирую в небольшом видео.

Видео — «Сравнение двух таблиц с помощью функции ВПР в Экселе»

Эпилог

Коллеги, поздравляю, с этого момента ваша работа с данными значительно упростится и ускорится, ведь теперь вы «почтигуру» по применению функции ВПР в Экселе.

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

Зачем сравнивать таблицы Excel?

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

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

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

Что можно сравнить в двух книгах Excel?

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

Ошибки формул SysGen

Ошибка имен SysGen

Подключение для передачи данных

Защита листа / книги

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

Как сравнить две книги Excel?

Первый шаг в сравнении двух книг Excel для активации вкладки «Запрос» в Microsoft Excel. Если у вас этого нет прямо сейчас, просто выполните следующие действия:

Активация вкладки запроса в Microsoft Excel

Перейдите в меню «Файл> Параметры». Это откроет вам небольшое окно «Параметры Excel».
Теперь посетите раздел «Надстройки». Вы увидите раздел активных и неактивных надстроек приложений.
В разделе «Неактивные надстройки приложений» щелкните надстройку «Запросить» и активируйте ее.
Выберите надстройку «Запросить» и посмотрите в нижней части «Управление». В раскрывающемся списке выберите «Надстройки COM», нажмите кнопку «Перейти…», а затем нажмите «ОК».
Теперь на ленте появится вкладка «Запрос».

Включение вкладки "Запрос" в Excel

Шаги по сравнению двух книг Excel

Допустим, у нас есть две книги для сравнения их данных. Здесь мы используем «предыдущую» рабочую книгу и «текущую» рабочую книгу. В «Раннюю» рабочую книгу мы внесли некоторые изменения и сохранили ее под новым названием «Текущая» рабочая книга. Более ранняя рабочая книга содержит старые данные, а «Текущая» содержит некоторые измененные данные.

Шаг 1. У нас есть более ранняя рабочая тетрадь, подобная этой:

Предыдущая рабочая тетрадь

Шаг 2: Вот наша текущая рабочая книга, в которой, как вы можете видеть, есть некоторые измененные данные.

Текущая рабочая тетрадь

Шаг 3: Теперь перейдите к любой из рабочих тетрадей. Щелкните вкладку «Запрос», а затем в разделе «Сравнение» щелкните «Сравнить файлы».

Выберите "Сравнить" на вкладке "Запрос".

Шаг 4: Вы увидите окно сравнения, в котором вас попросят выбрать файл сравнения и с каким файлом вы хотите его сравнить. В нашем случае для более раннего файла используется опция «Сравнить », а для текущего файла – опция «Кому». Вы можете легко обменивать файлы, нажав на кнопку «Поменять файлы».

Выберите файлы для сравнения

Результаты сравнения двух файлов Excel

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

Статьи по Теме:

Что нельзя сравнивать?

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

Эта функция работает только с двумя книгами, не более того. Как вы видели выше, в окнах сравнения вы можете добавить только 2 книги. Итак, это могло быть ограничением этой функции. Если какая-либо книга защищена, эта функция не позволит сравнивать этот файл. Сначала вам нужно снять защиту, затем вы можете использовать его для сравнения.

Заключение

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

Зачем сравнивать таблицы Excel?

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

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

Что можно сравнить в двух книгах Excel?

Ошибки формул SysGen

Ошибка имен SysGen

Подключение для передачи данных

Защита листа / книги

Как сравнить две книги Excel?

Активация вкладки запроса в Microsoft Excel

Перейдите в меню «Файл> Параметры». Это откроет вам небольшое окно «Параметры Excel».
Теперь посетите раздел «Надстройки». Вы увидите раздел активных и неактивных надстроек приложений.
В разделе «Неактивные надстройки приложений» щелкните надстройку «Запросить» и активируйте ее.
Выберите надстройку «Запросить» и посмотрите в нижней части «Управление». В раскрывающемся списке выберите «Надстройки COM», нажмите кнопку «Перейти…», а затем нажмите «ОК».
Теперь на ленте появится вкладка «Запрос».

Включение вкладки "Запрос" в Excel

Шаги по сравнению двух книг Excel

Шаг 1. У нас есть более ранняя рабочая тетрадь, подобная этой:

Предыдущая рабочая тетрадь

Шаг 2: Вот наша текущая рабочая книга, в которой, как вы можете видеть, есть некоторые измененные данные.

Текущая рабочая тетрадь

Шаг 3: Теперь перейдите к любой из рабочих тетрадей. Щелкните вкладку «Запрос», а затем в разделе «Сравнение» щелкните «Сравнить файлы».

Выберите "Сравнить" на вкладке "Запрос".

Шаг 4: Вы увидите окно сравнения, в котором вас попросят выбрать файл сравнения и с каким файлом вы хотите его сравнить. В нашем случае для более раннего файла используется опция «Сравнить », а для текущего файла – опция «Кому». Вы можете легко обменивать файлы, нажав на кнопку «Поменять файлы».

Выберите файлы для сравнения

Результаты сравнения двух файлов Excel

Статьи по Теме:

Что нельзя сравнивать?

Заключение

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

Важно: Spreadsheet Compare is only available with Office профессиональный плюс 2013, Office профессиональный плюс 2016, Office профессиональный плюс 2019, or Приложения Microsoft 365 для предприятий.

Откройте средство сравнения электронных таблиц.

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

На вкладке Home (Главная) выберите элемент Compare Files (Сравнить файлы).

В диалоговом окне "Сравнение файлов" в строке " " выберите более ранжную версию книги. Помимо выбора файлов, сохраненных на компьютере или в сети, можно также ввести веб-адрес, ведущий к книге, сохраненной на сайте.

В диалоговом окне "Сравнение файлов" в строке "С" до нужной версии.

Примечание: Можно сравнивать два файла с одинаковыми именами, если они хранятся в разных папках.

Результаты сравнения отображаются в виде таблицы, состоящей из двух частей. Книга в левой части соответствует файлу, указанному в поле "Compare" (Сравнить), а книга в правой части — файлу, указанному в поле "To" (С чем). Подробные сведения отображаются в области под двумя частями таблицы. Изменения выделяются разными цветами в соответствии с их типом.

Интерпретация результатов

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

Если содержимое не умещается в ячейках, выберите команду Resize Cells to Fit (Размер ячеек по размеру данных).

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

Другие способы работы с результатами сравнения

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

Вы можете экспортировать результаты в файл Excel, более удобный для чтения. Выберите Home > Export Results (Главная > Экспорт результатов).

Чтобы скопировать результаты и вставить их в другую программу, выберите Home > Copy Results to Clipboard (Главная > Копировать результаты в буфер обмена).

Чтобы отобразить форматирование ячеек из книги, выберите Home > Show Workbook Colors (Главная > Показать цвета книги).

Другие причины для сравнения книг

Средство сравнения электронных таблиц можно использовать не только для сравнения содержимого листов, но и для поиска различий в коде Visual Basic для приложений (VBA). Результаты отображаются в окне таким образом, чтобы различия можно было просматривать параллельно.

Читайте также: