Как в экселе сравнить два файла на совпадение

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

Что можно делать с средство диагностики электронных таблиц Excel для Windows

​Смотрите также​​ значений​ хромой поиск... :(​ делать отбор не​ раза укажите на​ - искать нужно​ прошу снисходительно отнестись.​ всего макрос написать​ способ занимает около​ Conditional formatting -​ сравнить значения в​ вводите соответствующее имя​ более удобный для​ которую хотите сравнить​ ячеек на листе.​ в нескольких. Это​ кнопку​Примечание:​.Item(arrA(i, 1)) =​Igor67​ числовые значения, а​

​ данные в одном​ по scripting.dictionary​ Опишу задачу: Есть​ с циклом. Что-то​ часа (да даже​ Highlight cell rules​ соседних ячейках каждой​ для диапазона и​ чтения. Выберите​ с более ранней.​Если вы используете функции​ поможет создать более​Средство сравнения электронных таблиц​ Мы стараемся как можно​

​ .Item(arrA(i, 1)) +​: Потренировался, попробуйте, может​ строковые. И мне​ листе, затем два​​Можно и написать​​ два файла Excel​​ типа такого​​ само открытие окна​ - Duplicate Values)​ строки. Как самый​

​ нажмите Enter.​​Home > Export Results​​Примечание:​ надстройки Inquire (Запрос)​ четкую картину зависимостей​на экране​

Сравнение двух книг

​ оперативнее обеспечивать вас​​ 1​​ быть подойдет.​ не совсем подходит​ раза на данные​ - только вот​ 2010, в каждом​i = 1​ фильтра в этой​

​:​ простой вариант -​Теперь воспользуемся условным форматированием,​(Главная > Экспорт результатов).​  Можно сравнивать два​ для выполнения анализа​ ваших данных от​Приложения​ актуальными справочными материалами​p = p​ПыСы первый раз​ то, что в​

​ во втором.​​ что делать с​​ порядка 300-500 тысяч​Do While Worksheets("Лист1").Cells(i,​ колонке занимает около​Если выбрать опцию​

• ​ используем формулу для​ чтобы выполнить сравнение​Чтобы скопировать результаты и​ файла с одинаковыми​ и сравнения защищенных​​ ячеек в других​​.​​ на вашем языке.​​ + 1 'если​​ объявил словарь, тяжело.​​ результате выводится только​

• ​Hugo​ обнаруженными?​​ строк, нужно сравнить​​ 1) <> ""​​ 20 минут, думаю​​Повторяющиеся​

• ​ сравнения значений, выдающую​ двух списков в​ ​ вставить их в​​ именами, если они​​ паролем книг, потребуется​​ источниках.​​В Windows 7 нажмите​​ Эта страница переведена​​ значение повторилось, то​​ Hugo, не посмотришь​​ значения из колонок​: Проверил - можно​​Только не говорите,​

​ эти два файла​If Worksheets("Лист1").Cells(i, 1)​ потому что в​, то Excel выделит​ на выходе логические​ Excel. Нам нужно​

Анализ книги

​ другую программу, выберите​​ хранятся в разных​​ добавить пароль книги​Эта схема отображает​ кнопку​ автоматически, поэтому ее​ можно будет уменьшить​ файл, что то​ отбора, мне нужны​ выделять вместо ID​ что нужно их​ по значениям одного​ = Worksheets("Лист2").Cells(i, 1)​ 500000 строк каждое​ цветом совпадения в​

​ значения​ получить следующий результат:​Home > Copy Results to​

​ папках.​

Отображение связей книги

​ в список паролей,​ связи между листами​Пуск​ текст может содержать​ конечный массив уникальных​​ много у меня​​ строки полностью.​ ячейку в пустом​ красить :)​ из столбцов (скажем​ Then ' Если​ значение повторяется максимум​ наших списках, если​ИСТИНА (TRUE)​​ Clipboard​Нажмите кнопку​ чтобы с помощью​ четырех различных книг​, выберите пункт​ неточности и грамматические​Else: .Add key:=arrA(i,​ переходных массивов получилось.​Тем не менее​ столбце.​

​Hugo​ по столбцу B,​ названия совпали​ дважды), на файле​ опцию​или​Позиции, которые есть в​(Главная > Копировать результаты​ОК​ надстройки Inquire (Запрос)​

​ с зависимостями между​Все программы​ ошибки. Для нас​ 1), Item:=1 'заносим​

Отображение связей листа

​ Не понял как​ весьма благодарен за​​Т.е. выделяете ячейку​​: Кстати, я недавно​ можно заранее привести​Range(Worksheets("Лист1").Cells(i, 1), Worksheets("Лист1").Cells(i,​ более 50Мб у​Уникальные​ЛОЖЬ (FALSE)​ Таблице_1, но нет​ в буфер обмена).​, чтобы выполнить сравнение.​ можно было открыть​ листами в одной​
​, а затем щелкните​ важно, чтобы эта​ значение в словарь​ по заданному значению​ помощь, возможно мне​ с данными одного​ уже ссылку давал​ файлы к одному​ 10)).Select​ меня комп повесился​- различия.​:​ в Таблцие_2 будут​Чтобы отобразить форматирование ячеек​Примечание:​

​ сохраненную копию книги.​ и той же​Microsoft Office 2013​ статья была вам​

Отображение связей ячейки

​ и указываем на​ Item вытащить Key.​ Ваша обработка поможет​ файла, затем пустую​ на файлик:​ виду). Теоретически значения​' здесь 10​​Данные можно разместить​​Цветовое выделение, однако, не​Число несовпадений можно посчитать​ отображаться зеленым цветом.​ из книги, выберите​  Появление сообщения "Не​ Используйте команду​ книге, а также​

​,​ полезна. Просим вас​ первое повторение​Юрий М​ достичь требуемого результата,​ ячейку рядом с​ibay.narod.ru/other/DoublesRemoveTwoColumns_v6.rar​ в файле 1​ - это номер​ на листах в​ всегда удобно, особенно​ формулой:​ В тоже время​Home > Show Workbook Colors​

​ удается открыть книгу"​Workbook Passwords​ связями между листами​Средства Office 2013​

​ уделить пару секунд​

Очистка лишнего форматирования ячеек

​End If​: А я ещё​ попробую разобраться в​ данными, затем аналогично​Можете сразу открыть​ и файле 2,​ последнего столбца в​ одной книге, это​ для больших таблиц.​​=СУММПРОИЗВ(--(A2:A20<>B2:B20))​​ позиции, находящиеся в​(Главная > Показать цвета​ может означать, что​(Пароли книги) на​ в других книгах.​и​ и сообщить, помогла​Next i​

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

​ книги).​ книга защищена паролем.​ вкладке​ Когда вы наводите​

Управление паролями

​Средство сравнения электронных таблиц​ ли она вам,​For i =​ :-)​Если кто то​ во втором файле.​ и обработать этим.​ быть одинаковы, на​With Selection.Interior​ таблицы к одинаковому​ самих списков э

Сравнение 2 файлов в Excel на предмет различий

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

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

Таких действий множество. Давайте рассмотрим некоторые из них.

С какой целью проводится сравнение файлов Excel

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

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

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

Все способы сравнения 2 таблиц в Excel

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

Формула равенства и проверка на ложь-истину

Начнем, конечно, с наиболее простого метода. Таким методом сравнение документов возможно, причем в довольно широких пределах. Можно сопоставлять между собой не только текстовые значения, но и числовые. И давайте приведем небольшой пример. Допустим, у нас есть два диапазона с ячейками числового формата. Для этого достаточно просто написать формулу равенства =C2=E2. Если окажется, что они равны, в ячейке будет написано «ИСТИНА». Если же они будут отличаться, то «ЛОЖЬ». После этого нужно данную формулу перенести на весь диапазон с помощью маркера автозаполнения.

Теперь разница видна невооруженным глазом.

1

Выделение различающихся значений

Также можно сделать так, чтобы значения, которые отличаются между собой, были выделены особенным цветом. Это тоже довольно простая задача. Если вам достаточно найти отличия между двумя диапазонами значений или целыми таблицами, необходимо перейти на вкладку «Главная», и там выбрать пункт «Найти и выделить». Перед тем, как вы ее нажмете, не забудьте выделить набор ячеек, в которых сохраняется информация для сравнения. 

В появившемся меню необходимо кликнуть по меню «Выделить группу ячеек…». Далее откроется окошко, в котором нам необходимо в качестве критерия выбрать отличия по строкам.

23

Сравнение 2 таблиц при помощи условного форматирования

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

4

Далее из перечня критериев нам нужно выбрать тот, где написано, что нужно использовать формулу для определения ячеек, которые будут отформатированы особенным образом. В описании правила нужно задать формулу. В нашем случае это =$C2<>$E2, после чего подтверждаем свои действия нажатием кнопки «Формат». После этого задаем внешний вид ячейки и смотрим, нравится ли он, через специальное мини-окошко с образцом. 

Если все устраивает, нажимаем кнопку «ОК» и подтверждаем действия.

5

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

Функция СЧЁТЕСЛИ + правила для сравнения таблиц

Все методы, которые мы описали ранее, удобны для тех формат, формат которых един. Если же предварительно таблицы не были упорядоченными, то лучше всего метод, в котором необходимо сравнивать две таблицы с помощью функции СЧЕТЕСЛИ и правил. 

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

В этом примере формула такая, как показана на этом скриншоте.

6

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

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

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

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

7

Функция ЕСЛИ

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

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

8

С помощью формулы, которая использует функции ЕСЛИ, ЕСЛИОШИБКА и ПОИСКПОЗ можно перебрать все нужные элементы колонки А, а потом в колонке B. Если оно было обнаружено в колонке B и A, то оно возвращается в соответствующую ячейку.

Макрос VBA

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

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

Как провести сравнение файлов в Эксель

Если пользователь поставил перед собой задачу (ну или ему поставили ее) сравнить два файла, то это можно сделать сразу двумя методами. Первый из них – использование специализированной функции. Чтобы реализовать этот метод, следуйте инструкции:

1. Откройте те файлы, сравнение которых требуется.
2. Откройте вкладку «Вид» – «Окно» – «Вид рядом».

После этого два файла будут открыты одном документе Excel.

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

Условное форматирование для сравнения 2 файлов Эксель

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

Сперва нам необходимо перенести сравниваемые листы в один документ. 

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

9

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

После этого переходим в окно условного форматирования и создаем новое правило. В качестве критерия используем подходящую в конкретном случае формулу, потом задаем формат.

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

Сравнение данных в Эксель на разных листах

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

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

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

Как сравнить 2 листа в таблице Эксель

Сравнение информации, располагающейся на двух листах, осуществляется с помощью функции ПОИСКПОЗ. В качестве первого ее параметра располагается пара значений, которую нужно искать на том листе, который отвечает за следующий месяц. Простыми словами, март. Мы можем обозначить просматриваемый диапазон, как совокупность ячеек, которые входят в состав именных диапазонов, объединенных попарно.

Так можно осуществить сравнение строк по двум критериям – фамилия и заработная плата. Ну или любым другим, определенным пользователем. Для всех совпадений, которые удалось найти, записывается в ячейке, в которую вводится формула, число. Для Excel такое значение всегда будет истинным. Поэтому для того, чтобы форматирование применялось по отношению к тем ячейкам, которые были различны, необходимо это значение заменить на ЛОЖЬ, воспользовавшись функцией =НЕ().

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

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

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

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

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

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

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

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

Как интерпретировать результаты сравнения

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

Оцените качество статьи. Нам важно ваше мнение:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Далее идите к Посмотреть вкладка и найти Посмотреть бок о бок в Окно раздел.

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

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

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

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

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

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

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

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

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

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

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

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

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

Ctrl + Shift + End.

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

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

=A1sheet_name!A1

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

Далее нажмите Формат и выберите, как вы хотите выделить любые расхождения. Я пошел на стандартную красную заливку. Далее нажмите Хорошо.

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

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

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

,

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

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

Сравнение двух версий книги с помощью средства сравнения электронных таблиц

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

Важно:  Сравнение электронных таблиц поддерживается только в Office профессиональный плюс 2013 или Приложения Microsoft 365 для предприятий.

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

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

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

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

   
   

5. В диалоговом окне Compare Files (Сравнение файлов) в строке To (С чем) с помощью кнопки обзора выберите версию книги, которую хотите сравнить с более ранней.
   

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

6. Нажмите кнопку ОК, чтобы выполнить сравнение.

Результаты сравнения отображаются в виде таблицы, состоящей из двух частей. Книга в левой части соответствует файлу, указанному в поле "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). Результаты отображаются в окне таким образом, чтобы различия можно было просматривать параллельно.

Сравнение двух файлов Excel - выделение цветом совпадений и отличий

В статье описывается, как при использовании надстройки для подстановки данных выделять цветом совпавшие (найденные) и несовпавшие (отличающиеся) строки

 

Для чего это нужно:

Например, вы сравниваете 2 таблицы по значениям определенных столбцов, и хотите видеть, в какие строки были подставлены данные, а в какие нет.
Для файла-источника данных, - аналогично: данные каких строк были использованы при подстановке, а какие строки не были задействованы.

 

Параметры выделения цветом задаются в настройках программы:

 

После нажатия кнопки «Настроить цвета», отображаются настраиваемые опции:

 

На вкладке «Использованные строки» можно задать цвет заливки и/или цвет шрифта для совпавших строк,
а также указать, на какие столбцы будет распространяться раскраска:

 

На вкладке «Незадействованные строки», выставляются аналогичные опции для строк, которым не нашлось соответствия:

 

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

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

 

Настройки подсветки строк задаются отдельно для файла-источника данных, и для файла, куда подставляются данные.

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

Допустим, у вас есть две книги Excel или две версии одной и той же книги, которые вы хотите сравнить. А может быть, вам нужно найти потенциальные проблемы, такие как введенные вручную (а не вычисляемые) итоги или формулы с ошибками. Если у вас есть Microsoft Office профессиональный плюс 2013, вы можете с помощью средства сравнения электронных таблиц от корпорации Майкрософт создать отчет о найденных различиях.

Важно:  Сравнение электронных таблиц поддерживается только в Office профессиональный плюс 2013 или Приложения Microsoft 365 для предприятий.

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

В Windows 7    В меню Пуск Windows в разделе Office инструменты 2013выберите пункт сравнить электронную таблицу.

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

Помимо средства сравнения электронных таблиц имеется и соответствующая программа для Access — средство сравнения баз данных (Майкрософт). Для нее также требуется Office профессиональный плюс 2013. (Чтобы найти ее в Windows 8, введите средство сравнения баз данных.)

Сравнение двух книг Excel

1. Выберите Главная > Сравнить файлы.

   Появляется диалоговое окно Compare Files (Сравнение файлов).

2. Щелкните значок синей папки рядом с полем Compare (Сравнить), чтобы перейти к более ранней версии книги. Помимо выбора файлов, сохраненных на компьютере или в сети, можно также ввести веб-адрес, ведущий к книге, сохраненной на сайте.

1. Щелкните значок зеленой папки рядом с полем To (С чем), чтобы перейти к книге, которую нужно сравнить с более ранней версией, а затем нажмите кнопку ОК.

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

2. В левой области выберите элементы, которые нужно включить в результаты сравнения книг: установите или снимите флажки у таких элементов, как Formulas (Формулы), Macros (Макросы) или Cell Format (Формат ячеек). Или просто выберите вариант Select All (Выделить все).

1. Нажмите кнопку ОК, чтобы выполнить сравнение.

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

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

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

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

  Примечание: Даже если лист скрыт, он все равно сравнивается и отображается в результатах.

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

В приведенном здесь примере результаты 4-го квартала (Q4) в более ранней версии не являются окончательными. Последняя версия книги содержит окончательные цифры для 4-го квартала в столбце E.

В результатах сравнения ячейки E2:E5 в обеих версиях выделены зеленым фоном, что означает изменение введенного значения. В результате изменения этих значений изменились и вычисляемые результаты в столбце YTD — ячейки F2:F4 и E6:F6 выделены сине-зеленым фоном, что означает изменение вычисленного значения.

Вычисляемый результат в ячейке F5 тоже изменился, но гораздо важнее то, что в более ранней версии его формула содержала ошибку (суммировались только ячейки B5:D5, а значение Q4 пропускалось). После обновления книги формула в ячейке F5 была исправлена и теперь имеет вид =СУММ(B5:E5).

Надстройка средства диагностики в Excel

Помимо средства сравнения электронных таблиц в Excel 2013 есть надстройка средства диагностики электронных таблиц, при включении которой появляется вкладка "Inquire" (Запрос). На этой вкладке можно проанализировать книгу, посмотреть на связи между ячейками, листами и другими книгами, а также убрать с листа лишнее форматирование. Если в Excel открыты две книги, которые нужно сравнить, можно запустить средство сравнения электронных таблиц с помощью команды "Сравнить файлы".

Если вкладка "Inquire" (Запрос) не отображается в Excel 2013, см. статью Включение средства диагностики электронных таблиц. Подробнее об инструментах этой надстройки см. в статье Возможности средства диагностики электронных таблиц.

Дальнейшие действия

Если у вас есть "критически важные" книги Excel или базы данных Access в вашей организации, установите инструменты для работы с электронными таблицами и базами данных Microsoft. Сервер управления аудитом и контролем (Майкрософт) предоставляет широкие возможности управления изменениями для Excel и файлов, а также может использовать сервер оценки Microsoft Discovery и риска, который предоставляет возможности инвентаризации и анализа, позволяющие уменьшить риск, связанный с использованием инструментов, разработанных конечными пользователями в Excel и Access.

Также ознакомьтесь с обзором сравнения электронных таблиц.

Примечание:  Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Сравните два столбца на совпадения и различия

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

Как сравнить 2 столбца в Excel построчно

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

Пример 1. Сравните два столбца на совпадения или различия в одной строке

Чтобы сравнить два столбца в Excel построчно, напишите обычную формулу ЕСЛИ, которая сравнивает первые две ячейки . Введите формулу в другой столбец той же строки, а затем скопируйте ее в другие ячейки, перетащив маркер заполнения (маленький квадрат в правом нижнем углу выбранной ячейки). Когда вы это сделаете, курсор изменится на знак плюса:

Формула для матчей

Чтобы найти ячейки в одной строке с одинаковым содержимым, A2 и B2 в этом примере, формула имеет следующий вид:

= ЕСЛИ (A2 = B2, «Соответствие», «»)

Формула различий

Чтобы найти ячейки в одной строке с разным содержимым, просто замените "=" знаком неравенства:

= ЕСЛИ (A2 <> B2, «Нет совпадений», «»)

Совпадения и различия

И, конечно же, ничто не мешает найти и совпадения, и различия по одной формуле:

= ЕСЛИ (A2 = B2, «Совпадение», «Нет совпадения»)

или

= ЕСЛИ (A2 <> B2, «Нет совпадений», «Совпадение»)

Результат может выглядеть примерно так:

Как видите, формула одинаково хорошо обрабатывает чисел , даты , умножить на и текстовые строки .

Пример 2. Сравните два списка на предмет совпадений с учетом регистра в одной строке

Как вы, наверное, заметили, формулы из предыдущего примера игнорируют регистр при сравнении текстовых значений, как в строке 10 на скриншоте выше. Если вы хотите найти совпадений с учетом регистра между 2 столбцами в каждой строке, используйте ТОЧНУЮ функцию:

= ЕСЛИ (ТОЧНЫЙ (A2, B2), «Совпадение», «»)

Чтобы найти различий с учетом регистра в одной строке, введите соответствующий текст («Уникальный» в этом примере) в аргумент 3 ^rd функции ЕСЛИ, например.г .:

= ЕСЛИ (ТОЧНЫЙ (A2, B2), «Совпадение», «Уникальный»)

Сравнить несколько столбцов для совпадений в одной строке

В таблицах Excel можно сравнить несколько столбцов на основе следующих критериев:

• Найти строки с одинаковыми значениями во всех столбцах (Пример 1)
• Найдите строки с одинаковыми значениями в любых 2 столбцах (Пример 2)

Пример 1. Найти совпадения во всех ячейках в одной строке

Если в вашей таблице три или более столбца, и вы хотите найти строки, которые имеют одинаковые значения во всех ячейках, формула IF с оператором AND подойдет для вас:

= ЕСЛИ (И (A2 = B2, A2 = C2), «Полное совпадение», «»)

Если в вашей таблице много столбцов, более элегантным решением будет использование функции СЧЁТЕСЛИ:

= ЕСЛИ (СЧЁТЕСЛИ ($ A2: $ E2, $ A2) = 5, «Полное совпадение», «»)

Где 5 - количество сравниваемых столбцов.

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

Если вы ищете способ сравнить столбцы для любых двух или более ячеек с одинаковыми значениями в одной строке, используйте формулу ЕСЛИ с оператором ИЛИ:

= ЕСЛИ (ИЛИ (A2 = B2, B2 = C2, A2 = C2), «Соответствие», «»)

Если есть много столбцов для сравнения, ваш оператор OR может стать слишком большим. В этом случае лучшим решением было бы добавить несколько функций СЧЁТЕСЛИ.Первый COUNTIF подсчитывает, сколько столбцов имеют то же значение, что и в столбце 1 ^st , второй COUNTIF подсчитывает, сколько из оставшихся столбцов равно столбцу 2 ^и и так далее. Если счетчик равен 0, формула возвращает «Уникальный», в противном случае - «Соответствие». Например:

= ЕСЛИ (СЧЁТЕСЛИ (B2: D2, A2) + СЧЁТЕСЛИ (C2: D2, B2) + (C2 = D2) = 0, «Уникальный», «Соответствие»)

Как сравнить два столбца в Excel на совпадения и различия

Предположим, у вас есть 2 списка данных в Excel, и вы хотите найти все значения (числа, даты или текстовые строки), которые находятся в столбце A, но не в столбце B.

Для этого вы можете встроить функцию COUNTIF ($ B: $ B, $ A2) = 0 в логический тест IF и проверить, возвращает ли она ноль (совпадение не найдено) или любое другое число (найдено как минимум 1 совпадение) .

Например, следующая формула IF / COUNTIF выполняет поиск по всему столбцу B значения в ячейке A2. Если совпадений не найдено, формула возвращает «Нет совпадений в B», в противном случае - пустую строку:

= ЕСЛИ (СЧЁТЕСЛИ ($ B: $ B, $ A2) = 0, «Нет совпадений в B», «»)

Наконечник. Если ваша таблица имеет фиксированное количество строк, вы можете указать определенный диапазон (например, $ B2: $ B10), а не весь столбец ($ B: $ B), чтобы формула работала быстрее с большими наборами данных.

Тот же результат может быть достигнут при использовании формулы ЕСЛИ со встроенными функциями ЕСТЬ ОШИБКА и ПОИСКПОЗ:

= IF (ISERROR (MATCH ($ A2, $ B $ 2: $ B $ 10,0)), «Нет совпадений в B», «»)

Или, используя следующую формулу массива (не забудьте нажать Ctrl + Shift + Enter, чтобы ввести ее правильно):

= ЕСЛИ (СУММ (- ($ B $ 2: $ B $ 10 = $ A2)) = 0, "Нет совпадений в B", "")

Если вы хотите, чтобы одна формула определяла как совпадения (дубликаты), так и различия (уникальные значения), поместите текст для совпадений в пустые двойные кавычки («») в любой из приведенных выше формул.Например:

= ЕСЛИ (СЧЁТЕСЛИ ($ B: $ B, $ A2) = 0, «Нет совпадений в B», «Совпадения в B»)

Как сравнить два списка в Excel и извлечь совпадающие данные

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

Например, следующая формула сравнивает названия продуктов в столбцах D и A, и если соответствие найдено, соответствующая цифра продаж извлекается из столбца B.Если совпадений не найдено, возвращается ошибка # Н / Д.

= ИНДЕКС ($ B $ 2: $ B $ 6, ПОИСКПОЗ ($ D2, $ A $ 2: $ A $ 6,0))

Подробное описание синтаксиса формулы и другие примеры формул можно найти в следующем руководстве: ИНДЕКС и ПОИСКПОЗ в Excel - лучшая альтернатива ВПР.

Если вам не очень нравится эта формула, вы можете попробовать мастер объединения таблиц - быстрое и интуитивно понятное решение, которое может сравнивать и сопоставлять 2 таблицы по любому столбцу (столбцам).

Сравните два списка и выделите совпадения и различия

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

Пример 1. Выделите совпадения и различия в каждой строке

Чтобы сравнить два столбца и Excel и выделить ячейки в столбце A, которые содержат одинаковых записей в столбце B в той же строке, выполните следующие действия:

• Выберите ячейки, которые хотите выделить (вы можете выбрать ячейки в одном столбце или в нескольких столбцах, если хотите выделить целые строки).
• Щелкните Условное форматирование> Новое правило…> Используйте формулу, чтобы определить, какие ячейки нужно форматировать для .
• Создайте правило с простой формулой, например = $ B2 = $ A2 (при условии, что строка 2 является первой строкой с данными, не включая заголовок столбца). Пожалуйста, дважды проверьте, что вы используете относительную ссылку на строку (без знака $), как в приведенной выше формуле.

Чтобы выделить различия между столбцами A и B, создайте правило с формулой = $ B2 <> $ A2

Если вы плохо знакомы с условным форматированием Excel, см. Пошаговые инструкции в разделе Как создать правило условного форматирования на основе формул.

Пример 2. Выделить уникальные записи в каждом списке

Каждый раз, когда вы сравниваете два списка в Excel, вы можете выделить 3 типа элементов:

• Предметы, которые есть только в списке 1 ^st (уникальные)
• Предметы, которые есть только в списке 2 ^и (уникальные)
• Элементы, которые есть в обоих списках (дубликаты) - продемонстрированы в следующем примере.

В этом примере показано, как выделить элементы только в одном списке.

Предположим, что ваш список 1 находится в столбце A (A2: A6), а список 2 - в столбце C (C2: C5). Вы создаете правила условного форматирования с помощью следующих формул:

Выделите уникальные значения в списке 1 (столбец A): = COUNTIF ($ C $ 2: $ C $ 5, $ A2) = 0

Выделите уникальные значения в списке 2 (столбец C): = COUNTIF ($ A $ 2: $ A $ 6, $ C2) = 0

И получим следующий результат:

Пример 3. Выделить совпадения (дубликаты) между 2 столбцами

Если вы внимательно следовали предыдущему примеру, у вас не возникнет трудностей с настройкой формул СЧЁТЕСЛИ, чтобы они находили совпадения, а не различия.Все, что вам нужно сделать, это установить счетчик больше нуля:

Выделить совпадения в списке 1 (столбец A): = COUNTIF ($ C $ 2: $ C $ 5, $ A2)> 0

Выделить совпадения в списке 2 (столбец C): = COUNTIF ($ A $ 2: $ A $ 6, $ C2)> 0

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

При сравнении значений в нескольких столбцах построчно, самый быстрый способ выделить совпадения - создать правило условного форматирования, а самый быстрый способ скрыть различия - воспользоваться функцией Go To Special , как показано в следующих примерах.

Пример 1. Сравните несколько столбцов и выделите совпадения строк

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

= И ($ A2 = $ B2, $ A2 = $ C2)

или

= СЧЁТЕСЛИ ($ A2: $ C2, $ A2) = 3

Где A2, B2 и C2 - самые верхние ячейки, а 3 - количество столбцов для сравнения.

Конечно, ни AND, ни COUNTIF формула не ограничиваются сравнением только 3 столбцов, вы можете использовать аналогичные формулы для выделения строк с одинаковыми значениями в 4, 5, 6 или более столбцах.

Пример 2. Сравните несколько столбцов и выделите различия строк

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

1. Выберите диапазон ячеек, который вы хотите сравнить. В этом примере я выбрал ячейки от A2 до C8.

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

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

   Наконечник. Чтобы выбрать несмежных столбцов , выберите первый столбец, нажмите и удерживайте Ctrl, а затем выберите другие столбцы.Активная ячейка будет в последнем столбце (или в последнем блоке соседних столбцов). Чтобы изменить столбец сравнения, используйте клавишу Tab или Enter, как описано выше.

2. На вкладке Home перейдите в группу Editing и нажмите Find & Select > Go to Special… Затем выберите Row Differences и нажмите кнопку OK .
   
3. Ячейки, значения которых отличаются от ячеек сравнения в каждой строке, выделяются.Если вы хотите заштриховать выделенные ячейки каким-либо цветом, просто щелкните значок Цвет заливки на ленте и выберите нужный цвет.
   

Как сравнить две ячейки в Excel

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

Например, для сравнения ячеек A1 и C1 можно использовать следующие формулы:

Для совпадений: = ЕСЛИ (A1 = C1, "Соответствие", "")

Для различий: = ЕСЛИ (A1 <> C1, "Разница", "")

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

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

Способ сравнения двух столбцов / списков в Excel без формул

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

Ablebits Duplicate Remover для Excel может искать идентичные и уникальные записи в одной таблице, а также сравнивать две таблицы, находящиеся на одном листе или в двух разных листах / книгах.

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

Сравните 2 списка по нескольким столбцам за 6 быстрых шагов

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

Шаг 1 .Предполагая, что у вас установлен Duplicate Remover для Excel, выберите любую ячейку в таблице 1 ^st и нажмите кнопку Сравнить две таблицы на ленте, чтобы запустить мастер. Эта кнопка находится на вкладке Ablebits Data в группе Dedupe .

Шаг 2 . Мастер выбирает всю таблицу и предлагает на всякий случай создать резервную копию исходной таблицы. Итак, просто убедитесь, что ваша таблица 1 ^st выбрана правильно, и нажмите Next .

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

Шаг 4 . Выберите, искать ли совпадения или различия:

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

В этом примере мы выбираем Дублирующиеся значения и нажимаем Далее .

Шаг 5 . Это ключевой шаг, на котором вы, , выбираете пары столбцов, которые хотите сравнить, в 2 таблицах. В этом примере мы сравниваем 3 столбца - Дата , Товар и Продажи .

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

Шаг 6 . На последнем этапе вы выбираете, как поступить с найденными предметами, и нажимаете Готово .

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

• Добавить столбец статуса - добавляет новый столбец с «Дубликатом» или «Уникальным», как это делают формулы Excel IF.
• Выделить - закрашивает повторяющиеся или уникальные строки, как правило условного форматирования Excel.

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

В этом примере я решил выделить дубликаты следующим цветом:

И мгновенно получили следующий результат:

Если мы выбрали Добавить столбец статуса на предыдущем шаге, результат будет выглядеть следующим образом:

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

Получите промокод для Ultimate Suite - эксклюзивное предложение для читателей нашего блога!

Доступные загрузки

Сравнить списки Excel - рабочая тетрадь (файл .xlsx)
Ultimate Suite - полнофункциональная пробная версия (файл .zip)

Вас также может заинтересовать

.

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

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

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

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

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

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

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

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

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

Если из одной книги взято более одного листа, вам необходимо заранее разделить их.Для этого перейдите к View> Window> New Window .

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

Затем перейдите на вкладку View и найдите View Side by Side в разделе Window .

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

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

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

Одна важная опция, о которой следует помнить, - это переключатель Synchronous Scrolling .

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

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

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

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

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

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

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

Перейдите на главную страницу > Стили> Условное форматирование> Новое правило .

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

  = A1 <> имя_листа! A1  

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

Затем нажмите Формат и выберите, как вы хотите выделить любые расхождения.Я выбрал стандартную красную заливку. Затем нажмите ОК .

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

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

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

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

Если вам нужно не просто сравнить документы, но найти конкретные значения, вам следует ознакомиться с функцией Excel vlookup.Чтобы сделать это другим способом, попробуйте сравнить файлы с помощью Notepad ++ или воспользуйтесь этими инструментами сравнения файлов Mac.

Apple представляет M1: «Самое быстрое в мире ядро ​​процессора»

Об авторе Брэд Джонс (Опубликовано 110 статей)

Английский писатель, в настоящее время живущий в США.Найдите меня в Твиттере через @radjonze.

Ещё от Brad Jones

Подпишитесь на нашу рассылку новостей

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

Еще один шаг…!

Подтвердите свой адрес электронной почты в только что отправленном вам электронном письме.

.

Сравнить два листа Excel, обновить и сопоставить данные

Инструмент «Сравнить листы» является частью пакета Ablebits Ultimate Suite для Excel, который может помочь вам быстро найти и выделить разные строки или ячейки на двух листах Excel и объединить их вручную ячейку за ячейкой. Найдите ответы на наиболее часто задаваемые вопросы об этой утилите на странице «Сравнение двух листов: часто задаваемые вопросы».

Обратите внимание, что надстройка совместима только с Excel 2016 и 2013. Excel 2010 и 2007 не поддерживаются.

Перед тем, как начать

Пожалуйста, внимательно прочтите эту часть, прежде чем начинать работу с надстройкой.

Microsoft Excel 2016 - 2013

Надстройка совместима только с MS Excel 2016 и 2013. MS Excel 2010 и 2007 не поддерживаются.

Только XLSX и XLSM

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

Сохраните все изменения

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

Резервные копии

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

Форматирование ячеек

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

Защищенные книги

Защищенные книги не поддерживаются, так как мы просто не сможем отметить в них различия 🙂

Объединенные ячейки

Листы с объединенными ячейками не могут быть обработаны.

Временные функции

Не пытайтесь сравнивать листы, содержащие изменчивые функции, потому что каждый раз, когда Excel открывает или пересчитывает книгу, эти функции возвращают новые значения. Неустойчивые функции: NOW, TODAY, RAND, RANDBETWEEN, OFFSET, INDIRECT.Кроме того, INFO и CELL могут быть непостоянными в зависимости от своих аргументов.

Что игнорируется

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

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

Как использовать мастер сравнения листов

Начать сравнение листов

Чтобы запустить инструмент, нажмите кнопку Сравнить листы на вкладке Ablebits Data в группе Объединить :

В первом окне вы увидите ограничения и требования, внимательно прочтите их:

Нажмите кнопку Согласен .

Шаг 1. Выберите рабочие листы и диапазоны

В списке открытых книг выберите листы, которые вы собираетесь сравнить.Если нужной книги нет в этом списке, откройте ее с помощью кнопки Открыть книгу .

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

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

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

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

Нажмите кнопку Далее .

Шаг 2: Укажите режим сравнения

На этом шаге вы выбираете режим сравнения и опцию сопоставления.

Выбрать режим сравнения

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

  Посмотрите, как это работает в таблицах сравнения по всем столбцам видео.

• По ключевым столбцам
  Выберите этот тип, если у вас есть таблицы с организацией по столбцам, содержащие данные по строкам. Например, два прайс-листа с одинаковыми столбцами «SKU» или «Product ID».Обычно эти столбцы называют «ключевыми столбцами», поэтому этот режим сравнения называется По ключевым столбцам . Такие листы всегда имеют одинаковое количество столбцов, но могут включать разное количество строк.

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

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

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

Укажите вариант соответствия

Проверяя построчно, «Сравнить листы» ищет различия, ища пары похожих строк на листах. Но бывают случаи, когда одна строка на листе 1 соответствует нескольким строкам на листе 2 с полным или частичным совпадением ячеек. Для таких ситуаций "Сравнить листы" предлагает три варианта сопоставления:

• Первое совпадение
  Если вы выберете Первое совпадение для строки AAA на Листе 1, Compare Sheets сопоставит его с первой строкой, содержащей хотя бы одно значение A.Остальные строки со значениями A будут помечены как уникальные:
  
• Наилучшее совпадение
  Если вы выберете Наилучшее соответствие для строки AAA на листе 1, функция «Сравнить листы» сопоставит его со строкой, содержащей максимальное количество значений A. Остальные строки со значениями A будут помечены как уникальные:
  
• Только полное совпадение
  Если вы выберете Только полное совпадение для строки AAA на листе 1, функция «Сравнить листы» пометит строку AAA как совпадение только в случае обнаружения. Остальные строки со значениями A будут помечены как уникальные:
  

Выберите подходящий режим сравнения и нажмите Далее .

Шаг 3: Выберите ключевые столбцы (если есть)

Если вы выбрали режим сравнения По ключевым столбцам , Compare Sheets попросит вас выбрать ключевые столбцы. Просто выберите их для листа 1, и надстройка автоматически найдет соответствующие столбцы на листе 2:

Щелкните Далее .

Шаг 4. Выберите параметры сравнения

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

• Показать различия в формулах
  Различные формулы могут возвращать одинаковые значения, поэтому различия в формулах игнорируются.Но если они важны для вас, отметьте эту опцию.
• Показать различия в форматировании
  Выберите этот вариант для поиска различий в форматировании ячеек.
• Игнорировать скрытые строки / столбцы
  По умолчанию мы игнорируем скрытые строки и столбцы. Если вам нужно найти различия в скрытых строках или столбцах, снимите этот флажок. Но, конечно, вы не заметите этих различий, если не сделаете их видимыми сначала 🙂
• Игнорировать пустые строки / столбцы
  Иногда пустые строки и столбцы могут быть важной частью ваших данных.Если это ваш случай, снимите этот флажок, и они будут включены в сравнение.
• Добавить столбец состояния
  Если вы выберете этот параметр, «Сравнить листы» добавит новый столбец на ваши листы и отметит обнаруженные различия. Это не только сделает результат сравнения более понятным, но и позволит удобно управлять различиями.
• Отметьте строки с различиями только в столбце состояния.
  Вы можете не делать ничего с ячейками, а вместо этого отметить все различия в специальном столбце.Отметьте эту опцию, и различия будут отмечены только в столбце статуса. Ваши таблицы останутся нетронутыми.
• Отметьте различия с помощью
  Выберите наиболее подходящий вариант маркировки. По умолчанию мы предлагаем использовать цвет фона. Но если вам нужно увидеть исходный цвет ваших ячеек, вы можете отметить различия цветом шрифта или нижней границей.

Выберите все необходимые параметры и нажмите Сравнить .

Понимание процесса сравнения

Когда вы нажимаете кнопку Сравнить , сравнение листов следует определенному алгоритму.Вот пошаговое описание:

1. Сравниваемые книги сохраняются, включая все изменения, внесенные до сравнения, и закрываются (или одна книга, если она содержит оба указанных листа).
2. Резервные копии сохранены. Путь и время хранения можно найти и изменить в настройках инструментов.
3. Compare Sheets загружает книги в память (не в Excel, это отдельное приложение / процесс) и сканирует их на предмет различий.
4. Ваши листы обрабатываются построчно.
5. Инструмент берет первые строки из Листа 1 и Листа 2 и сравнивает их ячейку за ячейкой.
6. Если все ячейки разные, строки помечаются как уникальные.
7. Если хотя бы одна пара ячеек в найденной паре строк совпадает, то строки считаются частично совпадающими на обоих листах, а неидентичные ячейки считаются разными.
8. Инструмент сравнивает следующую пару строк и повторяет шаги 5–7.
9. По завершении обработки ваши книги открываются в режиме просмотра различий.

Примечание. Обратите внимание! Это описание максимально упрощено с единственной целью - улучшить понимание. Compare Sheets использует модифицированный, но тем не менее вполне стандартный алгоритм сравнения двух больших наборов данных.

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

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

Как работать в режиме просмотра различий

Что такое режим просмотра различий

Надстройка обрабатывает ваши рабочие листы и открывает их в режиме просмотра различий:

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

• Синие строки - строки, которые существуют только на листе 1.
• Красные строки - строки, существующие только на листе 2.
• Зеленые ячейки - ячейки, которые отличаются на листе 1 и листе 2 частично совпадающими строками.

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

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

Как использовать панель инструментов

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

1. Используйте кнопки Перейти к предыдущему различию и Перейти к следующему различию для перехода между найденными различиями в активном рабочем листе. Для синих строк и красных строк кнопки выбирают всю предыдущую или следующую строку. Для зеленых ячеек кнопки перемещаются по ячейкам.
2. Кнопка Вставить выбранные уникальные строки на другой лист. Кнопка вставляет все красные или синие строки , включенные в ваш выбор, в неактивный рабочий лист.После вставки «Сравнить листы» переходит к следующему отличию, если включена опция Автопрокрутка . Наконечник. Если вы попытаетесь перенести формулы с относительными ссылками на ячейки с Sheet1 на Sheet2, они будут соответственно изменены. Результат может измениться или даже вернуть ошибку формулы в зависимости от относительного положения строк и столбцов в другой книге.
   Используйте опцию Вставить значения , если ваша задача - получить результаты формулы.
3. Копировать выделенные ячейки, отмеченные как отличные от другого рабочего листа. Кнопка копирует все зеленые ячейки , включенные в ваш выбор, на неактивный рабочий лист.После копирования «Сравнить листы» переходит к следующему отличию, если включена опция Автопрокрутка . Наконечник. Если вы попытаетесь перенести формулы с относительными ссылками на ячейки с Sheet1 на Sheet2, они будут соответственно изменены. Результат может измениться или даже вернуть ошибку формулы в зависимости от относительного положения строк и столбцов в другой книге.

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

4. Удалить выбранные различия. Кнопка удаляет все красные, или синие строки , включенные в ваш выбор (в зависимости от текущего активного листа).Кроме того, эта кнопка очищает все зеленые ячейки на обоих листах, если они включены в ваш выбор.
5. Игнорировать выбранные различия и удалить метки различий из выбранных ячеек. Кнопка восстанавливает исходное форматирование для синих или красных строк , включенных в ваш выбор (в зависимости от текущего активного листа). Для зеленых ячеек кнопка восстанавливает исходное форматирование на обоих листах. После игнорирования инструмент переходит к следующему отличию, если включена опция Автоматическая прокрутка .
6. Отменить и Вернуть отменить и восстановить действия кнопок, описанных выше.

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

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

В нижней части панели инструментов вы можете щелкнуть значок Настройки и открыть всплывающее меню с несколькими параметрами:

• Выберите Опции , чтобы указать путь и время хранения резервных копий ваших файлов.

• Если опция Автопрокрутка включена, вы автоматически переходите к следующему отличию после каждого вашего действия. Мы рекомендуем не включать этот параметр, пока вы не освоитесь с инструментом «Сравнить листы».

• Параметры вставки здесь аналогичны встроенным параметрам вставки Excel. Вы можете использовать их для вставки и копирования синих и красных строк и зеленых ячеек .

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

Как выйти из режима просмотра различий

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

Выйти, когда будут рассмотрены все различия

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

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

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

Выйти из режима без просмотра всех отличий

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

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

Если что-то пойдет не так

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

Если вы попытаетесь открыть их после сбоя, вы обнаружите, что файлы защищены, и вы не можете вносить в них какие-либо изменения. Чтобы решить эту проблему, вы можете либо использовать резервные копии, которые были созданы при запуске инструмента «Сравнить листы», либо снять защиту с исходных файлов.

Для этого перейдите на вкладку Review , найдите группу Protect и щелкните Снять защиту листа :

Excel попросит вас ввести пароль. Вот он: битовсравнения

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

Как закрыть листы

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

Если ваши листы содержат цвета из-за различий в инструментах, которые вы не обрабатывали с помощью панели инструментов, Compare Two Sheets предложит вам четыре варианта:

• Сохраните и закройте книгу, сохранив отметки разницы.
  Выберите этот вариант, если вы хотите сохранить всю окраску, выполненную инструментом. Листы будут закрыты, а отметки различия сохранены.
• Закройте книгу без сохранения. Отметки различий сохранятся, все ваши изменения будут потеряны.
  Этот параметр имеет смысл, если вы обработали метки разницы с помощью панели инструментов (вставили строки с одного листа на другой, скопировали ячейки, удалили метки разницы или проигнорировали их), но не хотите сохранять результат своей работы.Таким образом, цвет будет сохранен, но все изменения, сделанные с помощью панели инструментов, будут потеряны.
• Закройте книгу и удалите отметки различия.
  Выберите этот параметр, если вы обработали некоторые различия и хотите сохранить эти изменения, но в то же время хотите удалить другие отметки отличий, которые не обрабатывались.
• Закройте книгу и восстановите ее содержимое из резервной копии.
  Если вы внесли некоторые изменения в свои листы с помощью набора инструментов, но не хотите их сохранять, выберите этот параметр, чтобы получить исходную версию листов.

Часто задаваемые вопросы

Найдите ответы на наиболее часто задаваемые вопросы о Compare Two Sheets:

.

Сравнить строки в двух ячейках для нечувствительности к регистру или точного совпадения

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

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

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

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

Как сравнить две ячейки в Excel

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

Формула без учета регистра для сравнения 2 ячеек

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

= A1 = B1

Где A1 и B1 - сравниваемые ячейки. Результатом формулы являются логические значения ИСТИНА и ЛОЖЬ.

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

= ЕСЛИ (A1 = B1, «Равно», «Не равно»)

Как видно на снимке экрана ниже, обе формулы одинаково хорошо сравнивают текстовые строки, даты и числа:

Формула с учетом регистра для сравнения строк в Excel

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

ТОЧНЫЙ (текст1, текст2)

Где text1 и text2 - две ячейки, которые вы сравниваете.

Предполагая, что ваши строки находятся в ячейках A2 и B2, формула выглядит следующим образом:

= ТОЧНЫЙ (A2, B2)

В результате вы получите ИСТИНА для текстовых строк, точно совпадающих с регистром каждого символа, в противном случае - ЛОЖЬ.

Если вы хотите, чтобы функция EXACT выдавала другие результаты, вставьте ее в формулу ЕСЛИ и введите свой собственный текст для value_if_true и value_if_false аргумента:

= ЕСЛИ (ТОЧНО (A2; B2), «Точно равно», «Не равно»)

На следующем снимке экрана показаны результаты сравнения строк с учетом регистра в Excel:

Как сравнить несколько ячеек в Excel

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

Формула без учета регистра для сравнения более чем 2 ячеек

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

= И (A2 = B2, A2 = C2)

или

= ЕСЛИ (И (A2 = B2, A2 = C2), «Равно», «Не равно»)

Формула И возвращает ИСТИНА, если все ячейки содержат одно и то же значение, ЛОЖЬ, если любое значение отличается. Формула ЕСЛИ выводит введенные вами метки: « равно » и « не равно » в этом примере.

Как показано на скриншоте ниже, формула отлично работает с любыми типами данных - текстом, датами и числовыми значениями:

Формула с учетом регистра для сравнения текста в нескольких ячейках

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

= И (ТОЧНЫЙ (A2, B2), ТОЧНЫЙ (A2, C2))

или

= ЕСЛИ (И (ТОЧНЫЙ (A2, B2), ТОЧНЫЙ (A2, C2)), «Точно равно», «Не равно»)

Как и в предыдущем примере, первая формула выдает значения ИСТИНА и ЛОЖЬ, тогда как вторая отображает ваши собственные тексты для совпадений и различий:

Сравните диапазон ячеек с образцом ячейки

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

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

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

ROWS ( диапазон ) * COLUMNS ( range ) = COUNTIF ( диапазон , образец ячейки )

В логическом тесте функции ЕСЛИ вы сравниваете два числа:

• Общее количество ячеек в указанном диапазоне (количество строк, умноженное на количество столбцов), и
• Количество ячеек, содержащих то же значение, что и в ячейке образца (возвращенное функцией СЧЁТЕСЛИ).

Предполагая, что образец текста находится в C2, а строки для сравнения находятся в диапазоне A2: B6, формула выглядит следующим образом:

= СТРОКИ (A2: B6) * КОЛОННЫ (A2: B6) = СЧЁТЕСЛИ (A2: B6, C2)

Чтобы сделать результаты более удобными для пользователя, т.е. вывести что-то вроде «Все совпадения» и «Не все совпадения» вместо ИСТИНА и ЛОЖЬ, используйте функцию ЕСЛИ, как мы делали в предыдущих примерах:

= ЕСЛИ (СТРОКИ (A2: B6) * COLUMNS (A2: B6) = COUNTIF (A2: B6, C2), «Все совпадения», «Не все совпадения»)

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

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

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

IF (ROWS ( диапазон ) * COLUMNS ( range ) = SUM (- EXACT ( sample_cell , range )), « text_if_match », « text_if_not match »)

Если исходный диапазон находится в A2: B6, а образец текста - в C2, формула принимает следующий вид:

= ЕСЛИ (СТРОКИ (A2: B6) * COLUMNS (A2: B6) = SUM (- EXACT (C2, A2: B6)), «Совпадение полностью», «Совпадение не полностью»)

В отличие от обычных формул Excel, формулы массива заполняются нажатием Ctrl + Shift + Enter.Если введено правильно, Excel заключает формулу массива в {фигурные скобки}, как показано на снимке экрана:

Как сравнить две ячейки по длине строки

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

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

= LEN (A2) = LEN (B2)

или

= ЕСЛИ (LEN (A2) = LEN (B2), «Равно», «Не равно»)

Как вы уже знаете, первая формула возвращает логические значения ИСТИНА или ЛОЖЬ, тогда как вторая формула выводит ваши собственные результаты:

Как показано на скриншоте выше, формулы работают как для текстовых строк, так и для чисел.

Наконечник. Если две, казалось бы, равные строки возвращают разную длину, скорее всего, проблема в ведущих или конечных пробелов в одной или обеих ячейках. В этом случае удалите лишние пробелы с помощью функции ОБРЕЗАТЬ. Подробное объяснение и примеры формул можно найти здесь: Как обрезать пробелы в Excel.

Сравнить две ячейки по вхождению определенного символа

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

Чтобы прояснить ситуацию, рассмотрим следующий пример. Допустим, у вас есть два списка отгруженных заказов (столбец B) и полученных (столбец C). Каждая строка содержит заказы на конкретный товар, уникальный идентификатор которого включен во все идентификаторы заказа и указан в той же строке в столбце A (см. Снимок экрана ниже).Вы хотите убедиться, что каждая строка содержит равное количество отправленных и полученных товаров с этим конкретным идентификатором.

Чтобы решить эту проблему, напишите формулу со следующей логикой.

• Во-первых, замените уникальный идентификатор ничем с помощью функции ЗАМЕНА:

  ЗАМЕНА (A1, character_to_count, "")

• Затем вычислите, сколько раз уникальный идентификатор появляется в каждой ячейке. Для этого получите длину строки без уникального идентификатора и вычтите ее из общей длины строки.Эта часть должна быть написана для ячейки 1 и ячейки 2 отдельно, например:

  LEN (ячейка 1) - LEN (ПОДСТАВИТЬ (ячейка 1, character_to_count, ""))
  и
  LEN (ячейка 2) - LEN (SUBSTITUTE (ячейка 2, character_to_count, ""))

• Наконец, вы сравните эти 2 числа, поместив знак равенства (=) между указанными выше частями.

LEN ( ячейка 1 ) - LEN (ПОДСТАВИТЬ ( ячейка 1 , символов к_счету , "")) =
LEN ( ячейка 2 ) - LEN (ПОДСТАВИТЬ ( ячейка 2 , символов к ""))

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

= LEN (B2) -LEN (ПОДСТАВИТЬ (B2, $ A2, "")) = LEN (C2) -LEN (ПОДСТАВИТЬ (C2, $ A2, ""))

Формула возвращает ИСТИНА, если ячейки B2 и C2 содержат одинаковое количество вхождений символа в ячейке A2, в противном случае - ЛОЖЬ. Чтобы результаты были более понятными для пользователей, вы можете встроить формулу в функцию ЕСЛИ:

= ЕСЛИ (LEN (B2) -LEN (ПОДСТАВИТЬ (B2, $ A2, "")) = LEN (C2) -LEN (ПОДСТАВИТЬ (C2, $ A2, "")), "Равно", "Не равно ")

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

• Подсчитываемый символ (уникальный идентификатор) может появляться в любом месте текстовой строки.
• Строки содержат переменное количество символов и разные разделители, такие как точка с запятой, запятая или пробел.

Вот как вы сравниваете строки в Excel. Чтобы ближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить Excel Compare Strings Worksheet. Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе.

Вас также может заинтересовать

.

Как сравнить два столбца в Excel и при совпадении скопировать соседнюю ячейку

Переполнение стека

1. Около
2. Товары
3. Для команд

1. Переполнение стека Общественные вопросы и ответы

.

---

Смотрите также

• 
  Как пдф файл перевести в тиф
• 
  Как запаролить архивный файл rar
• 
  Как открыть dat файл skype
• 
  Как убрать галочку использовать выбранную программу для всех файлов
• 
  Как открыть cdr файл на mac
• 
  Как полностью отключить файл подкачки
• 
  Как закрыть папку с файлами
• 
  Как из ibooks отправить файл
• 
  Как перекинуть файлы на виртуальную машину virtualbox
• 
  Как перенести файлы с iphone на компьютер через itunes
• 
  Как узнать откуда скачан файл на компьютере