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

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

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

Но в табличном редакторе «Microsoft Excel» существуют методы, которые являются менее ресурсоёмкими и не требуют от вас огромного количества ненужных манипуляций. Итак, сегодня мы рассмотрим пятёрку таких алгоритмов.

Читайте также: Как сравнить два документа Word на различия


Сравнение таблиц в Excel

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

• сравнение списков, которые находятся на одном листе;

• сравнение таблиц, которые располагаются на разных листах;

• сравнение таблиц, которые располагаются в разных файлах.

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

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

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


Метод 1 – Облегчённый

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

Всё достаточно просто – если данные в двух ячейках совпадают, то напротив неё будет показатель «ИСТИНА», если же нет, то «ЛОЖЬ».

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

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

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

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

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

1) Первым делом нам потребуется активировать ещё один столбец на листе, в который будет выводиться результат сравнения. Вписываем в данный столбец знак равенства.

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

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

=A2=D2

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

2) Теперь нажимаем на клавишу «Enter» на клавиатуре. Как вы можете заметить, после этого в ячейке, в которую выводится результат, отобразилось значение «ИСТИНА».

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

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

Но так как каждый раз прописывать одну и ту же формулу слишком долго мы поступим более рационально.

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

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

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


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

У нас выявилось несовпадение показателей исключительно в одной строке. В этой же строке располагается аргумент «ЛОЖЬ».

Со всеми остальными ситуация точно такая же, как и с первой ячейкой – везде стоит показатель «ИСТИНА».

5) Помимо этого вы сможете подсчитать количество показателей «ЛОЖЬ» при помощи одной функции, которая так же интегрирована в данный табличный редактор.

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

6) В разделе с выбором функции нам необходимо выставить оператор «Математические» и в списке отыскать пункт СУММПРОИЗВ. После этого кликаем по нему и нажимаем на кнопку «ОК».

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

Синтаксическая часть данной функции достаточно проста в написании:

=СУММПРОИЗВ(массив1;массив2;…)

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

Теперь кликаем по графе «Массив1» и на листе выделяем первую таблицу с данными. Далее устанавливаем один из двух знаков неравенства (<>), после чего выделяем другую таблицу с данными.

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

--(A2:A7<>D2:D7)

Кликните по кнопке «ОК».

8) После этого формула рассчитает количество несовпадений и выведет результат в отмеченную ранее ячейку.

Как вы можете заметить, в нашем случае несовпадение всего одно, поэтому результатом вычислений формулы стало число «1».

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

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

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

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

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

=B2=Лист2!B2


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


Метод 2 – Выделяем группы ячеек

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

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

1) Для начала необходимо выделить обе таблицы, которые требуется сравнить.

После этого переключаемся во вкладку «Главная» и выбираем там инструмент «Найти и выделить», которые располагается в разделе «Редактирование».

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

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

Особенно данный способ будет актуален для тех пользователей, у кого табличный редактор версии 2007-го года и старше, поскольку инструмент «Найти и выделить» в таком же виде там отсутствует.

Для этого просто выделяем обе таблицы на одном листе и на клавиатуре жмём клавишу F5.

2) После вас переадресует в раздел с конфигурацией. Здесь вам необходимо кликнуть по клавише «Выделить…».

3) Теперь, каким бы из двух описанных ранее вариантов вы не воспользовались запустится меню с редактированием выделения групп ячеек.

Здесь вам необходимо активировать параметр «Выделить по строкам».

Для применения изменений кликаем по клавише «OK».

4) Итак, теперь подсветятся те ячейки, значения в которых не совпадают.

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



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

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

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

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

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

После этого переключаемся во вкладку «Главная» и в блоке инструментов «Стили» кликаем по клавише «Условное форматирование».

В появившемся меню кликаем по клавише «Управление правилами».

2) Нас переадресует в меню правил. Здесь нам необходимо кликнуть по клавише «Создать правило».

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

Между этими адресами необходимо вставить разделитель, в нашем случае это будет знак неравенства (<>).

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

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

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

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

=$A2<>$D2

Собственно, именно такое выражение должно у вас получится в графе «Форматировать ячейки». Теперь нам необходимо кликнуть по клавише «Формат…».

4) Вас переадресует в раздел «Формат ячеек». Переключаемся в раздел «Заливка».

Здесь вам необходимо выбрать тот цвет, которым будут обозначаться ячейки, показатели которых в таблице не совпадают. После выбора цвета кликните по клавише «ОК».

5) Теперь нас обратно переадресует в меню с настройкой правил. Здесь жмём по клавише «ОК».

6) Далее нас переадресует в окно с правилами, где нам так же необходимо кликнуть по клавише «ОК».

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

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

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

Итак, давайте приступим.

1) Для начала выделяем обе таблицы, которые будем сравнивать.

2) Теперь переключаемся во вкладку «Главная». Там кликаем по клавише «Условное форматирование».

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

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

В случае, если ранее вы сделали всё так, как написано в инструкции, то от вас требуется только кликнуть по кнопке «ОК».

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

4) Теперь все элементы, которые повторяются будут окрашены в указанный вами ранее цвет.

Собственно, все остальные ячейки так и останутся с исходным для них цветом (если вы ничего не меняли, то это будет белый).

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

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

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

Результат будет приблизительно таким.


Читайте также: Условное форматирование в Excel


Метод 4 – Использование комплексной формулы

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

Функция СЧЁТЕСЛИ является представителем статистической группы операторов. Собственно, её основной функцией является подсчёт количества ячеек, которые удовлетворяют условию, которое вы заранее задали.

Синтаксис у данной функции так же выглядит достаточно просто:

=СЧЁТЕСЛИ(диапазон;критерий)

В качестве «Диапазона» подразумевается то количество ячеек, в котором и будет происходить расчёт совпадающих параметров.

А вот «Критерием» будет выступать условие подсчёта. В нашей ситуации критерием будет являться первая таблица, ячейки из которой мы и будем считать за основные.

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

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

2) Далее в окне мастера функций выставляем категорию «Статические» и отыскиваем здесь нужную нам функцию СЧЁТЕСЛИ.

После этого кликаем по ней левой клавишей мыши и жмём на кнопку «ОК».

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

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

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

А для того, чтобы функция работала корректно, нам потребуется сделать данную ссылку абсолютной.

Как мы уже говорили ранее, сделать это достаточно просто – нажмите на клавиатуре клавишу F4 и после этого адрес приобретёт нужный нам вид.

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

Итак, теперь нам необходимо заполнить графу «Критерий». Устанавливаем в ней курсор.

Здесь всё достаточно просто – кликните по первой ячейке первой таблицы.

Здесь делать ссылку абсолютной не требуется, поэтому оставляем её относительной. Итак, для сохранения внесённых коррективов кликаем по клавише «ОК».

4) В отмеченной ранее ячейке отобразится результат применения данной функции.

Как вы видите, здесь результат равен единице. Это обозначает, что фамилия «Гринев В. П.», в данной таблице встречается только единожды.

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

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

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

В четырёх случаях результат сравнения равен единице, а в остальных двух – нулю.

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

Конечно, данный вариант применения функции «СЧЁТЕСЛИ» достаточно неплох, но есть способ, который позволяет усовершенствовать данный и без того хороший метод. Конечно, он более трудоёмкий, но это того стоит.

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

1) Для начала нам необходимо немного доработать саму формулу СЧЁТЕСЛИ. Чтобы сделать это, мы применим её вместе с другой функцией, которая называется «ЕСЛИ».

Для начала нам необходимо кликнуть по ячейке, в которой располагается функция СЧЁТЕСЛИ.

После этого в начало синтаксиса данной функции дописываем оператор «ЕСЛИ» убрав кавычки и открыв скобку.

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

2) Откроется окно с настройкой аргументации функции «ЕСЛИ». Как вы можете заметить, первый аргумент уже там прописан – это функция «СЧЁТЕСЛИ».

Но, помимо этой функции нам необходимо добавить ещё одну деталь. Для этого кликаем по функции с этим аргументом и прописываем туда «=0» убрав кавычки.

Теперь переключаемся к следующему полю «Значение если истина». Здесь нам потребуется воспользоваться ещё одной функцией, которая называется «СТРОКА».

Вписываем туда слово «СТРОКА» убрав кавычки, после этого открываем скобку и указываем адрес первой ячейки второй таблицы из столбца с фамилиями сотрудников, после чего закрываем скобку. В нашей ситуации синтаксис функции получился вот таким:

СТРОКА(D2)

Итак, теперь функция СТРОКА будет синергировать с функцией «ЕСЛИ».

Это означает, что в случае, если условие из первой графы с конфигурацией данной функции будет выполняться, то в ячейку с результатом будет выводиться номе строки. Для сохранения изменений кликаем по клавише «ОК».

3) После активации функции в первой же строке выдалось значение «ЛОЖЬ».

Это обозначает, что фамилия, которая располагается на первом месте в списке во втором столбце, присутствует как в первой, так и во второй таблице.

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

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

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

5) Теперь отступаем на одну ячейку вправо от таблицы, в которую выводился результат применения функции «ЕСЛИ» и начинаем пронумеровывать ячейки.

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

6) Далее отступаем на одну ячейку вправо от пронумерованных ячеек и выделяем первую ячейку из столбца. Теперь вызываем «Мастера функций».

7) Запустится окно мастера функций. Здесь нам необходимо выставить категорию «Статические». Теперь здесь нам необходимо отыскать и кликнуть по функции «НАИМЕНЬШИЙ», после чего нажимаем на клавишу «OK».

8) Оператор «НАИМЕНЬШИЙ», окно конфигурации которого мы сейчас раскрыли, предназначается для выводов наименьшего значения из таблицы.

В графе «МАССИВ» необходимо прописать адрес целого столбца, в котором мы ранее применяли функцию «ЕСЛИ».

Адрес данной ячейки необходимо сделать абсолютным. Для этого нажмите на клавиатуре F4.

В графе «К» необходимо указать, какое по порядку наименьшее значение будет выведено.

Здесь необходимо указать адрес первой ячейки столбца с пронумерованными ячейками, которые мы сделали ранее. Для применения кликаем по клавише «ОК».

9) Формула выдаст результат – цифру 3. Собственно, именно это число и является наименьшим из отмеченного нами ранее столбца.

Эту функцию необходимо размножить при помощи метода, о котором мы уже говорили выше.

10) Итак, теперь, когда мы знаем все номера строк, значения в которых не совпадают мы можем вставить их в ячейки при помощи формулы «ИНДЕКС».

Для начала кликаем по первой ячейке, в которой располагается функция «НАИМЕНЬШИЙ», после чего перед этой функцией дописываем слово «ИНДЕКС» убрав кавычки, и выставляем значок точки с запятой.

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

11) Вас переадресует в окно, в котором необходимо указать тип данной функции.

Мы будем использовать тип, который предназначен для работы с табличными массивами. Данный тип выбран по умолчанию, поэтому от нас требуется только кликнуть по клавише «ОК».

12) Далее откроется меню с настройкой аргументации функции «ИНДЕКС». Этот оператор предназначается для вывода информации, которая расположена в отмеченной строке.

Как вы можете заметить, в графе «Номер строки» уже указаны значения от функции «НАИМЕНЬШИЙ». От уже расположенного там значения нам следует отнять разность номера листа и номера таблицы.

Как вы можете заметить, здесь у нас в качестве разницы выступает исключительно шапка таблицы с названием столбцов, поэтому разность будет равна единице. Поэтому в конце функции «Номер строки» нам необходимо дописать «-1» убрав кавычки.

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

Также необходимо установить возле этого адреса значки доллара, при помощи ранее описанного метода. Итак, кликаем по клавише «ОК».

13) Как только функция будет применена нам необходимо размножить её до конца таблицы.

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



Метод 5 – Сравниваем таблицы из разных файлов

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

Но самым главным условием для сравнения таблиц из разных файлов является открытие обоих документов одновременно.

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


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

Как открыть несколько таблиц в разных окнах в «Microsoft Excel»

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

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


Заключение

Итак, как вы видите, сравнить две таблицы в табличном редакторе «Microsoft Excel» достаточно просто. Надеемся, что данная статья смогла помочь вам. Всем мир!

Рейтинг:(голосов:1)