Как сравнить два файла 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. Ваши предложения ждем в комментариях.

  • 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. определить результат - разность или "разницы нет"

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

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