Сравнение данных в Excel на разных листах. Как сравнить два столбца в Excel на совпадения Как сравнить два документа excel на совпадения

Нужно сравнить два файла Microsoft Excel? Вот два простых способа сделать это.

Существует множество причин, по которым вам может понадобиться взять один документ Excel и сравнить его с другим. Это может быть трудоемкой задачей

это требует большой концентрации, но есть способы облегчить себе жизнь.

Нужно ли вам внимательно посмотреть вручную или вы хотите, чтобы Excel выполнял некоторые тяжелые работы

от вашего имени, вот два простых способа сравнить несколько листов.

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

Excel позволяет пользователям сразу выводить на экран две версии документа, чтобы быстро установить различия между ними:

Сначала откройте рабочие книги, которые нужно сравнить.
Перейдите к Вид> Окно> Вид рядом .

Сравнение файлов Excel на глаз

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

или совершенно разные файлы.

Если из одной и той же книги получено более одного листа, ее необходимо отделить заранее. Для этого перейдите к Вид> Окно> Новое окно .

Это не разделит отдельные листы навсегда, просто откроет новый экземпляр вашего документа.

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

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

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

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

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

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

Сравнение файлов Excel с использованием условного форматирования

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

Использование условного форматирования

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

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

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

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

Ctrl + Shift + End .

Перейдите к Главная> Стили> Условное форматирование> Новое правило .

Выбрать Используйте формулу, чтобы определить, какие ячейки форматировать и введите следующее:

A1sheet_name!A1

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

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

Пусть Excel сделает тяжелую работу

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

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

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

Say you want to compare versions of a workbook, analyze a workbook for problems or inconsistencies, or see links between workbooks or worksheets. If Microsoft Office 365 or Office Professional Plus 2013 is installed on your computer, the Spreadsheet Inquire add-in is available in Excel.

You can use the commands in the Inquire tab to do all these tasks, and more. The Inquire tab on the Excel ribbon has buttons for the commands described below.

If you don"t see the Inquire tab in the Excel ribbon, see Turn on the Spreadsheet Inquire add-in .

Compare two workbooks

The Compare Files command lets you see the differences, cell by cell, between two workbooks. You need to have two workbooks open in Excel to run this command.

Results are color coded by the kind of content, such as entered values, formulas, named ranges, and formats. There"s even a window that can show VBA code changes line by line. Differences between cells are shown in an easy to read grid layout, like this:

The Compare Files command uses Microsoft Spreadsheet Compare to compare the two files. In Windows 8, you can start Spreadsheet Compare outside of Excel by clicking Spreadsheet Compare on the Apps screen. In Windows 7, click the Windows Start button and then > All Programs > Microsoft Office 2013 > Office 2013 Tools > Spreadsheet Compare 2013 .

To learn more about Spreadsheet Compare and comparing files, read Compare two versions of a workbook .

Analyze a workbook

The Workbook Analysis command creates an interactive report showing detailed information about the workbook and its structure, formulas, cells, ranges, and warnings. The picture here shows a very simple workbook containing two formulas and data connections to an Access database and a text file.

Show workbook links

Workbooks connected to other workbooks through cell references can get confusing. Use the to create an interactive, graphical map of workbook dependencies created by connections (links) between files. The types of links in the diagram can include other workbooks, Access databases, text files, HTML pages, SQL Server databases, and other data sources. In the relationship diagram, you can select elements and find more information about them, and drag connection lines to change the shape of the diagram.

This diagram shows the current workbook on the left and the connections between it and other workbooks and data sources. It also shows additional levels of workbook connections, giving you a picture of the data origins for the workbook.

Show worksheet links

Got lots of worksheets that depend on each other? Use the to create an interactive, graphical map of connections (links) between worksheets both in the same workbook and in other workbooks. This helps give you a clearer picture of how your data might depend on cells in other places.

This diagram shows the relationships between worksheets in four different workbooks, with dependencies between worksheets in the same workbook as well as links between worksheets in different workbooks. When you position your pointer over a node in the diagram, such as the worksheet named "West" in the diagram, a balloon containing information appears.

Show cell relationships

To get a detailed, interactive diagram of all links from a selected cell to cells in other worksheets or even other workbooks, use the Cell Relationship tool. These relationships with other cells can exist in formulas, or references to named ranges. The diagram can cross worksheets and workbooks.

This diagram shows two levels of cell relationships for cell A10 on Sheet5 in Book1.xlsx. This cell is dependent on cell C6 on Sheet 1 in another workbook, Book2.xlsx. This cell is a precedent for several cells on other worksheets in the same file.

To learn more about viewing cell relationships, read See links between cells .

Clean excess cell formatting

Ever open a workbook and find it loads slowly, or has become huge? It might have formatting applied to rows or columns you aren"t aware of. Use the Clean Excess Cell Formatting command to remove excess formatting and greatly reduce file size. This helps you avoid "spreadsheet bloat," which improves Excel"s speed.

Manage passwords

If you"re using the Inquire features to analyze or compare workbooks that are password protected, you"ll need to add the workbook password to your password list so that Inquire can open the saved copy of your workbook. Use the Workbook Passwords command on the Inquire tab to add passwords, which will be saved on your computer. These passwords are encrypted and only accessible by you.

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

Сравнение двух столбцов на совпадения в Excel

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

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

Выберите инструмент «ФОРМУЛЫ»-«Определенные имена»-«Присвоить имя».
В появившемся окне в поле «Имя:» введите значение – Таблица_1.
Левой клавишей мышки сделайте щелчок по полю ввода «Диапазон:» и выделите диапазон: A2:A15. И нажмите ОК.

Для второго списка выполните те же действия только имя присвойте – Таблица_2. А диапазон укажите C2:C15 – соответственно.

Полезный совет! Имена диапазонов можно присваивать быстрее с помощью поля имен. Оно находится левее от строки формул. Просто выделяйте диапазоны ячеек, а в поле имен вводите соответствующее имя для диапазона и нажмите Enter.

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

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

Принцип сравнения данных двух столбцов в Excel

При определении условий для форматирования ячеек столбцов мы использовали функцию СЧЕТЕСЛИ. В данном примере эта функция проверяет сколько раз встречается значение второго аргумента (например, A2) в списке первого аргумента (например, Таблица_2). Если количество раз = 0 в таком случае формула возвращает значение ИСТИНА. В таком случае ячейке присваивается пользовательский формат, указанный в параметрах условного форматирования.

Ссылка во втором аргументе относительная, значит по очереди будут проверятся все ячейки выделенного диапазона (например, A2:A15). Например, для сравнения двух прайсов в Excel даже на разных листах . Вторая формула действует аналогично. Этот же принцип можно применять для разных подобных задач.

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

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

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

Сравним два столбца цифровых значений, в которых отличие имеется только в нескольких ячейках. Записав простую формулу в соседнем столбце, условие равенства двух ячеек «=B3=C3» , мы получим результат «ИСТИНА» , если содержимое ячеек одинаковое, и «ЛОЖ» , если содержимое ячеек отличается. Растянув формулу по всей высоте столбца сравниваемых значений очень легко будет найти отличающуюся ячейку.

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

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

В диспетчере правил выбираем пункт «Создать правило» , а в создании правил выбираем . Теперь мы можем задать формулу «=$B3<>$C3» для определения форматируемой ячейки, и задать для нее формат, нажав на кнопку «Формат» .

Теперь у нас имеется правило отбора ячеек, задано форматирование, и определен диапазон сравниваемый ячеек. После нажатия на кнопку «OK» , заданное нами правило будет применено.

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

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

Порядок действий следующий, выделяем первый набор данных, именуемый у нас «Столбец 1» , и в меню «Условное форматирование» выбираем пункт «Создать правило…» . В появившемся окошке выбираем , вписываем необходимую формулу «=СЧЁТЕСЛИ($C$3:$D$12;A3)=0» и выбираем способ форматирования.

В нашей формуле используется функция «СЧЁТЕСЛИ» , которая подсчитывает количество повторений значения из определенной ячейки «A3» в заданном диапазоне «$C$3:$D$12» , которым выступает наш второй столбец. В качестве сравниваемой ячейки необходимо указывать первую ячейку из диапазона значений, к которым будет применяться форматирование.

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

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

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

Необходимая нам формула будет выглядеть следующим образом: «=ВПР(B18;$B$3:$C$10;2;ЛОЖЬ)» . Первое значение «B18» соответствует первой ячейке искомого наименования товара. Второе значение «$B$3:$C$10» означает постоянный адрес диапазона таблицы старого прайса, значения из которой нам понадобятся. Третье значение «2» означает номер столбца из выделенного диапазона, в ячейке которого мы и будем брать старую цену товара. И последнее значение «ЛОЖЬ» задает поиск только по точному совпадению значений. После протаскивания формулы по всему столбцу новой таблицы мы получим в этом столбце старые значения цен по каждой позиции, имеющейся в новой таблице. Напротив наименования последнего товара формула выводит сообщение ошибки «#Н/Д» , что свидетельствует об отсутствии данного наименования в старом прайсе.

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

Друзья, рад вас приветствовать на страницах моего блога. Сегодня хочу рассказать вам о 4 способах сравнения файлов по содержимому. Необходимость найти различия возникает довольно часто. Приходится делать сравнение файлов по типу или размеру. Сравнение файлов в Word и Excel тоже весьма распространённая процедура в офисах и конторах, где постоянно приходится работать с текстовыми документами и таблицами.

Но для веб-мастера или владельца сайта часто бывает необходимо сравнить два файла по содержимому. И речь идёт не о текстовых файлах, хотя и они тоже анализируются так же, а о форматах.html, .css, .php и так далее. Тех файлах, где нужно найти изменения в коде. И порой изменение одного лишь знака может обеспечить уйму трудностей с достижением результата.

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

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

Способ 1. Сравнение файлов в программе Total Commander.

Этой программой я пользуюсь давно. Для работы с файлами, на мой взгляд, это одна из лучших программ. Программа Total Commander платная, но можно в течение 30 дней пользоваться и пробной версией, которая по истечении времени также будет работать, но с предложением о покупке. Скачать пробную версию можно .

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

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

Итак, запускаете Total Commander – в одной из панелей выбираете первый файл для сравнения – во второй панели открываете папку со вторым файлом.

Откроется новое окно, также разделённое на две панели. В самом верху, в каждой панели будет срока для выбора файла. В одной из них первый файл будет уже выбран (но может быть и пустым). Для выбора файла нужно нажать на небольшую кнопочку со стрелочками (>>) .

Выбрав файлы для сравнения, нажимаете на кнопку «Сравнить» . В каждой панели появится содержимое выбранного файла. Отличия будут отмечены фоновым цветом.

Для внесения изменений в файл достаточно нажать на кнопку «Редактировать» . И можно приступать к внесению изменений. Также здесь будут доступны функции копирования и отката, поиска и изменение кодировки.

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

Способ 2. Сравнение файлов в Notepad++.

Если вам приходилось, хоть когда-либо, редактировать файлы html, css и php. То наверняка вы слышали о бесплатном редакторе кода Notepad++. Скачать программу можно .

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

Но для этого потребуется установить небольшой плагин. Делается всё это непосредственно внутри самого редактора.

Итак, запускаете редактор – переходите в пункт меню «Плагины » — «Plugin Manager» — «Show Plugin Manager» .

В новом окне выбираете плагин « Compare» и жмёте кнопку « Install» .

После установки плагина можно приступать к сравнению содержимого файлов. Для этого нужно открыть два файла – перейти в пункт меню «Плагины» — « Compare» — « Compare (Alt+ D)» .

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

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

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

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

Завершить сравнение файлов можно комбинацией клавиш Ctrl+ Alt+ D .

Способ 3. Сравнение содержимого двух файлов в программе WinMerge.

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

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

Запустив программу, нужно выбрать файлы для сравнения. Открываете пункт меню «Файл» — «Открыть» .

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

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

Способ 4. Сравнение файлов с помощью командной строки Windows.

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

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

Для этого открываете «Пуск» — «Все программы» — «Стандартные» — «Командная строка» . И вводите вот эту команду:

Fc /N путь к первому файлу путь ко второму файлу

выглядит это так:

Fc /N C:\Мои документы\file1.txt C:\Мои документы\file2.txt

Готовый результат будет выглядеть так:

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

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

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

На этом сегодня всё, всем желаю успехов и хорошего настроения и до встречи в новых статьях и видеоуроках!

С уважением, Максим Зайцев.