Как сравнить два файла MS Excel

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

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

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

В качестве рабочего примера возьмем файл с показателями вымышленных участников: бег на 100 метров, бег на 3000 метров и подтягивания. Первый файл – это замер в начале сезона, а второй – конец сезона.

Первый способ решения поставленной задачи. Решение только силами формул MS Excel.

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

Для сравнения показателей бега на 100 метров формула выглядит следующим образом:

=ЕСЛИ(ВПР($B2;Sheet2!$B$2:$F$13;3;ИСТИНА)<>D2;D2-ВПР($B2;Sheet2!$B$2:$F$13;3;ИСТИНА);"Разницы нет")

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

Формула для бега на 3000 метров выглядит следующим образом:

=ЕСЛИ(ВПР($B2;Sheet2!$B$2:$F$13;4;ИСТИНА)<>E2;"Разница есть";"Разницы нет")

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

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

Видео сравнения двух файлов MS Excel с помощью функций ВПР и ЕСЛИ.

Второй способ решения задачи. Решение с помощью MS Access.

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

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

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

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

Видео сравнения файлов MS в Excel, с помощью MS Access.

В результате проделанных манипуляций выведены все записи, с разными данными в поле: "Бег на 100 метров". Файл MS Access представлен ниже (к сожалению, внедрить, как файл Excel, SkyDrive не позволяет)

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

  • Андрей Козырный

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

    • msofficeprowork

      Сейчас способов реализации предостаточно, все зависит, что хотите получить на выходе: обновленный список только существующих товаров, подсветку цветом, записи в ячейках об отсутсвии товара и т.п..
      Если обходится ВПР (вернет запись об отсутствии товара), то просто дважды сделать проверку одну на цену, вторую на товар (если не будет функция вернет ошибку НД).
      Если нужно сделать подсветку товара, то посмотрите вот https://msoffice-prowork.com/nakhodim-povtoryayushhiesya-znacheniya-v-excel/
      А если нужно, чтобы список автоматически выбрасывал отсутствующие значения, рекомендую PowerQuery. Вот этот материал может помочь https://msoffice-prowork.com/excelworldchamp-3-1-neprostoe-zadanie-s-power-query/

  • zdevl

    Здравствуйте.
    У меня такая проблема, пытаюсь выбрать диапазон во втором файле, но ничего не выбирается. Все значения добавляются в функцию только из первого файла, а второй она просто не видит. Может что-то в настройках самого Экселя над включить?

    • msofficeprowork

      А какая версия Office у Вас. Ссылки на листы других книг стандартная возможность Excel, все должно нормально работать начиная с 2007-й версии. Можете скинуть файлики я гляну.

      • zdevl

        Вот гифку сделал. Может просто у меня руки кривые и я не так делаю
        https://drive.google.com/file/d/0BwUqAHUP-nVrQTRLQlB0emhzX0E/view?usp=sharing

        • msofficeprowork

          Вы когда таблицу пытаетесь выбрать, сначала кликните по кнопке выбора в мастере слева от поля.
          https://uploads.disquscdn.com/images/c7359e072be1ae112630b08eab34f59caf8fa5fbe8b5279ae1b9a37721b2f70b.png

          • zdevl

            пробовал, тоже самое

          • zdevl

            раньше то делал это, все нормально было. Версия Эксель не менялась, менялась только версия винды

          • msofficeprowork

            Понял, тогда даже не подскажу, я по настройкам пробежался, вроде нигде не встречал запрет на внешние ссылки. Есть возможность попробовать на другом компьютере? Либо скиньте мне, может в файле проблема (фильтр мегает или еще чего).
            koldovskyy@msoffice-prowork.com

  • shustryk lviv

    У меня следующая проблема
    Есть ПЕРВЫЙ файл с прайс листом и необходимыми колонками(Product name/Product code/Description/Category/List price/Quantity/Detailed image), который заливается на сайт. Каждую неделю приходит новый прайс лист(названий столбцов отличаются от тех которые есть в ПЕРВОМ файле) с новыми ценами на товар который есть в старом прайс листе плюс добавляются новые товары которых нет в ПЕРВОМ файле. Нужно сделать так чтобы обновлялись цены и добавлялся новый товар в ПЕРВЫЙ файл с нового. С екселем не очень дружу может кто то подскажет как осуществить эту задумку

    • msofficeprowork

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

      • shustryk lviv

        Со второго файла мне нужны 7 колонок, которые я могу просто скопировать в другой файл. И того у меня будут 2 файла практически одинаковые разница будет в колонке List price и могут появится новые позиции которых нет в ПЕРВОМ файле

        • shustryk lviv

          Задам вопрос по другому. Есть ПЕРВЫЙ файл 7 колонок и 1000 позиций товара.
          Есть второй файл 7 таких же колонок но 1100 позиций. Нужно сравнить два файла. Обновить цены в тех позициях которые есть в ПЕРВОМ файле и добавить те позиции в 7 колонок которых нет в ПЕРВОМ файле

          • shustryk lviv

            Пример файла

          • msofficeprowork

            Я так понимаю, что основная причина в том, что нельзя просто использовать второй список, заключается в том, что в первом находятся пощиции, которых нет во втором?
            Ну сравнить цены по позициях и их изменить не проблема, в статье как раз такой способ и описывается.
            Добавить новые, то без применения макросов я вижу только автоматизированный способ, не полностью автоматический.
            Вот материал, как найти записи, которых нет в первом диапазоне: http://msoffice-prowork.com/uslovnoe-formatirovanie-dlya-poiska-unikalnykh-zapisejj-v-excel/

          • shustryk lviv

            Все верно, ПЕРВЫЙ вроде базы даных а второй обновление, Спасибо попробую разобратся

  • Дмитрий Ткачук

    ура-а-а-а!!! разобрался, спасибо за Ваше внимание и предложенную помощь. Удачи Вам и процветания, Вы делаете полезное дело

    • msofficeprowork

      Рад, что все получилось.

  • Дмитрий Ткачук

    Не один раз просмотрел Ваше видео (с помощью MS Excel) все очень доступно рассказано, за что огромное Вам СПАСИБО, но у себя не смог добиться ожидаемого результата, может не получилось, потому что разное количество столбцов. Буду рад если поможете.

    • msofficeprowork

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

      • Хочу помочь

        Попробуйте вбить формулу не в одну ячейку, а разбить на несколько ячеек по действиям.
        1. Извлечь значение из второй таблицы (вертикальной проверкой найти по фамилии из первой таблицы результат по стометровке во второй)
        2. сравнить извлеченное значение с текущим
        3. определить результат - разность или "разницы нет"

        Так выбудете видеть, на каком этапе у Вас ошибка.

        лично у меня всё заработало, как только в формуле ВПР поставила точность ЛОЖЬ.