Довольно часто перед пользователями Excel стоит задача сравнения двух таблиц или списков для выявления в них отличий или недостающих элементов. Каждый юзер справляется с этой задачей по своему, но чаще всего на решение указанного вопроса тратится довольно большое количество времени, так как далеко не все подходы к данной проблеме являются рациональными. В то же время, существует несколько проверенных алгоритмов действий, которые позволят сравнить списки или табличные массивы в довольно сжатые сроки с минимальной затратой усилий. Давайте подробно рассмотрим данные варианты.
Способы сравнения
Существует довольно много способов сравнения табличных областей в Excel, но все их можно разделить на три большие группы:
Именно исходя из этой классификации, прежде всего, подбираются методы сравнения, а также определяются конкретные действия и алгоритмы для выполнения задачи. Например, при проведении сравнения в разных книгах требуется одновременно открыть два файла Excel.
Кроме того, следует сказать, что сравнивать табличные области имеет смысл только тогда, когда они имеют похожую структуру.
Способ 1: простая формула
Самый простой способ сравнения данных в двух таблицах – это использование простой формулы равенства. Если данные совпадают, то она выдает показатель ИСТИНА, а если нет, то – ЛОЖЬ. Сравнивать можно, как числовые данные, так и текстовые. Недостаток данного способа состоит в том, что ним можно пользоваться только в том случае, если данные в таблице упорядочены или отсортированы одинаково, синхронизированы и имеют равное количество строчек. Давайте посмотрим, как использовать данный способ на практике на примере двух таблиц, размещенных на одном листе.
Итак, имеем две простые таблицы со списками работников предприятия и их окладами. Нужно сравнить списки сотрудников и выявить несоответствия между столбцами, в которых размещены фамилии.
- Для этого нам понадобится дополнительный столбец на листе. Вписываем туда знак . Затем кликаем по первому наименованию, которое нужно сравнить в первом списке. Опять ставим символ с клавиатуры. Далее кликаем по первой ячейке колонки, которую мы сравниваем, во второй таблице. Получилось выражение следующего типа:
=A2=D2
Хотя, конечно, в каждом конкретном случае координаты будут отличаться, но суть останется одинаковой.
- Щелкаем по клавише , чтобы получить результаты сравнения. Как видим, при сравнении первых ячеек обоих списков программа указала показатель , что означает совпадение данных.
- Теперь нам нужно провести аналогичную операцию и с остальными ячейками обеих таблиц в тех колонках, которые мы сравниваем. Но можно просто провести копирование формулы, что позволит существенно сэкономить время. Особенно данный фактор важен при сравнивании списков с большим количеством строк.
Процедуру копирования легче всего выполнить при помощи маркера заполнения. Наводим курсор на правый нижний угол ячейки, где мы получили показатель . При этом он должен преобразоваться в черный крестик. Это и есть маркер заполнения. Жмем левую кнопку мыши и тянем курсор вниз на количество строчек в сравниваемых табличных массивах.
- Как видим, теперь в дополнительном столбце отобразились все результаты сравнения данных в двух колонках табличных массивов. В нашем случае не совпали данные только в одной строке. При их сравнении формула выдала результат . По всем остальным строчкам, как видим, формула сравнения выдала показатель .
- Кроме того, существует возможность с помощью специальной формулы подсчитать количество несовпадений. Для этого выделяем тот элемент листа, куда оно будет выводиться. Затем щелкаем по значку .
- В окне в группе операторов выделяем наименование . Щелкаем по кнопке .
- Активируется окно аргументов функции , главной задачей которой является вычисление суммы произведений выделенного диапазона. Но данную функцию можно использовать и для наших целей. Синтаксис у неё довольно простой:
=СУММПРОИЗВ(массив1;массив2;…)
Всего в качестве аргументов можно использовать адреса до 255 массивов. Но в нашем случае мы будем использовать всего два массива, к тому же, как один аргумент.
Ставим курсор в поле и выделяем на листе сравниваемый диапазон данных в первой области. После этого в поле ставим знак () и выделяем сравниваемый диапазон второй области. Далее обворачиваем полученное выражение скобками, перед которыми ставим два знака . В нашем случае получилось такое выражение:
—(A2:A7<>D2:D7)
Щелкаем по кнопке .
- Оператор производит расчет и выводит результат. Как видим, в нашем случае результат равен числу , то есть, это означает, что в сравниваемых списках было найдено одно несовпадение. Если бы списки были полностью идентичными, то результат бы был равен числу .
Таким же образом можно производить сравнение данных в таблицах, которые расположены на разных листах. Но в этом случае желательно, чтобы строки в них были пронумерованы. В остальном процедура сравнения практически точно такая, как была описана выше, кроме того факта, что при внесении формулы придется переключаться между листами. В нашем случае выражение будет иметь следующий вид:
=B2=Лист2!B2
То есть, как видим, перед координатами данных, которые расположены на других листах, отличных от того, где выводится результат сравнения, указывается номер листа и восклицательный знак.
Способ 2: выделение групп ячеек
Сравнение можно произвести при помощи инструмента выделения групп ячеек. С его помощью также можно сравнивать только синхронизированные и упорядоченные списки. Кроме того, в этом случае списки должны располагаться рядом друг с другом на одном листе.
- Выделяем сравниваемые массивы. Переходим во вкладку . Далее щелкаем по значку , который располагается на ленте в блоке инструментов . Открывается список, в котором следует выбрать позицию .
Кроме того, в нужное нам окно выделения группы ячеек можно попасть и другим способом. Данный вариант особенно будет полезен тем пользователям, у которых установлена версия программы ранее Excel 2007, так как метод через кнопку эти приложения не поддерживают. Выделяем массивы, которые желаем сравнить, и жмем на клавишу .
- Активируется небольшое окошко перехода. Щелкаем по кнопке в его нижнем левом углу.
- После этого, какой бы из двух вышеперечисленных вариантов вы не избрали, запускается окно выделения групп ячеек. Устанавливаем переключатель в позицию . Жмем по кнопке .
- Как видим, после этого несовпадающие значения строк будут подсвечены отличающимся оттенком. Кроме того, как можно судить из содержимого строки формул, программа сделает активной одну из ячеек, находящуюся в указанных не совпавших строках.
Способ 3: условное форматирование
Произвести сравнение можно, применив метод условного форматирования. Как и в предыдущем способе, сравниваемые области должны находиться на одном рабочем листе Excel и быть синхронизированными между собой.
- Прежде всего, выбираем, какую табличную область будем считать основной, а в какой искать отличия. Последнее давайте будем делать во второй таблице. Поэтому выделяем список работников, находящийся в ней. Переместившись на вкладку , щелкаем по кнопке , которая имеет месторасположение на ленте в блоке . Из выпадающего списка переходим по пункту .
- Активируется окошко диспетчера правил. Жмем в нем на кнопку .
- В запустившемся окне производим выбор позиции . В поле записываем формулу, содержащую адреса первых ячеек диапазонов сравниваемых столбцов, разделенные знаком «не равно» (). Только перед данным выражением на этот раз будет стоять знак . Кроме того, ко всем к координатам столбцов в данной формуле нужно применить абсолютную адресацию. Для этого выделяем формулу курсором и трижды жмем на клавишу . Как видим, около всех адресов столбцов появился знак доллара, что и означает превращение ссылок в абсолютные. Для нашего конкретного случая формула примет следующий вид:
=$A2<>$D2
Данное выражение мы и записываем в вышеуказанное поле. После этого щёлкаем по кнопке .
- Активируется окно . Идем во вкладку . Тут в перечне цветов останавливаем выбор на цвете, которым хотим окрашивать те элементы, где данные не будут совпадать. Жмем на кнопку .
- Вернувшись в окно создания правила форматирования, жмем на кнопку .
- После автоматического перемещения в окно щелкаем по кнопке и в нем.
- Теперь во второй таблице элементы, которые имеют данные, несовпадающие с соответствующими значениями первой табличной области, будут выделены выбранным цветом.
Существует ещё один способ применения условного форматирования для выполнения поставленной задачи. Как и предыдущие варианты, он требует расположения обоих сравниваемых областей на одном листе, но в отличие от ранее описанных способов, условие синхронизации или сортировки данных не будет являться обязательным, что выгодно отличает данный вариант от ранее описанных.
- Производим выделение областей, которые нужно сравнить.
- Выполняем переход во вкладку под названием . Делаем щелчок по кнопке . В активировавшемся списке выбираем позицию . В следующем меню делаем выбор позиции .
- Запускается окно настройки выделения повторяющихся значений. Если вы все сделали правильно, то в данном окне остается только нажать на кнопку . Хотя при желании в соответствующем поле данного окошка можно выбрать другой цвет выделения.
- После того, как мы произведем указанное действие, все повторяющиеся элементы будут выделены выбранным цветом. Те элементы, которые не совпадают, останутся окрашенными в свой изначальный цвет (по умолчанию белый). Таким образом, можно сразу визуально увидеть, в чем отличие между массивами.
При желании можно, наоборот, окрасить несовпадающие элементы, а те показатели, которые совпадают, оставить с заливкой прежним цветом. При этом алгоритм действий практически тот же, но в окне настройки выделения повторяющихся значений в первом поле вместо параметра следует выбрать параметр . После этого нажать на кнопку .
Таким образом, будут выделены именно те показатели, которые не совпадают.
Урок: Условное форматирование в Экселе
Способ 4: комплексная формула
Также сравнить данные можно при помощи сложной формулы, основой которой является функция . С помощью данного инструмента можно произвести подсчет того, сколько каждый элемент из выбранного столбца второй таблицы повторяется в первой.
Оператор относится к статистической группе функций. Его задачей является подсчет количества ячеек, значения в которых удовлетворяют заданному условию. Синтаксис данного оператора имеет такой вид:
=СЧЁТЕСЛИ(диапазон;критерий)
Аргумент представляет собой адрес массива, в котором производится подсчет совпадающих значений.
Аргумент задает условие совпадения. В нашем случае он будет представлять собой координаты конкретных ячеек первой табличной области.
- Выделяем первый элемент дополнительного столбца, в котором будет производиться подсчет количества совпадений. Далее щелкаем по пиктограмме .
- Происходит запуск . Переходим в категорию . Находим в перечне наименование . После его выделения щелкаем по кнопке .
- Происходит запуск окна аргументов оператора . Как видим, наименования полей в этом окне соответствуют названиям аргументов.
Устанавливаем курсор в поле . После этого, зажав левую кнопку мыши, выделяем все значения столбца с фамилиями второй таблицы. Как видим, координаты тут же попадают в указанное поле. Но для наших целей следует сделать данный адрес абсолютным. Для этого выделяем данные координаты в поле и жмем на клавишу .
Как видим, ссылка приняла абсолютную форму, что характеризуется наличием знаков доллара.
Затем переходим к полю , установив туда курсор. Щелкаем по первому элементу с фамилиями в первом табличном диапазоне. В данном случае оставляем ссылку относительной. После того, как она отобразилась в поле, можно щелкать по кнопке .
- В элемент листа выводится результат. Он равен числу . Это означает, что в перечне имен второй таблицы фамилия , которая является первой в списке первого табличного массива, встречается один раз.
- Теперь нам нужно создать подобное выражение и для всех других элементов первой таблицы. Для этого выполним копирование, воспользовавшись маркером заполнения, как это мы уже делали прежде. Ставим курсор в нижнюю правую часть элемента листа, который содержит функцию , и после преобразования его в маркер заполнения зажимаем левую кнопку мыши и тянем курсор вниз.
- Как видим, программа произвела вычисление совпадений, сравнив каждую ячейку первой таблицы с данными, которые расположены во втором табличном диапазоне. В четырех случаях результат вышел , а в двух случаях – . То есть, программа не смогла отыскать во второй таблице два значения, которые имеются в первом табличном массиве.
Конечно, данное выражение для того, чтобы сравнить табличные показатели, можно применять и в существующем виде, но есть возможность его усовершенствовать.
Сделаем так, чтобы те значения, которые имеются во второй таблице, но отсутствуют в первой, выводились отдельным списком.
- Прежде всего, немного переработаем нашу формулу , а именно сделаем её одним из аргументов оператора . Для этого выделяем первую ячейку, в которой расположен оператор . В строке формул перед ней дописываем выражение без кавычек и открываем скобку. Далее, чтобы нам легче было работать, выделяем в строке формул значение и жмем по иконке .
- Открывается окно аргументов функции . Как видим, первое поле окна уже заполнено значением оператора . Но нам нужно дописать кое-что ещё в это поле. Устанавливаем туда курсор и к уже существующему выражению дописываем без кавычек.
После этого переходим к полю . Тут мы воспользуемся ещё одной вложенной функцией – . Вписываем слово без кавычек, далее открываем скобки и указываем координаты первой ячейки с фамилией во второй таблице, после чего закрываем скобки. Конкретно в нашем случае в поле получилось следующее выражение:
СТРОКА(D2)
Теперь оператор будет сообщать функции номер строки, в которой расположена конкретная фамилия, и в случае, когда условие, заданное в первом поле, будет выполняться, функция будет выводить этот номер в ячейку. Жмем на кнопку .
- Как видим, первый результат отображается, как . Это означает, что значение не удовлетворяет условиям оператора . То есть, первая фамилия присутствует в обоих списках.
- С помощью маркера заполнения, уже привычным способом копируем выражение оператора на весь столбец. Как видим, по двум позициям, которые присутствуют во второй таблице, но отсутствуют в первой, формула выдает номера строк.
- Отступаем от табличной области вправо и заполняем колонку номерами по порядку, начиная от . Количество номеров должно совпадать с количеством строк во второй сравниваемой таблице. Чтобы ускорить процедуру нумерации, можно также воспользоваться маркером заполнения.
- После этого выделяем первую ячейку справа от колонки с номерами и щелкаем по значку .
- Открывается . Переходим в категорию и производим выбор наименования . Щелкаем по кнопке .
- Функция , окно аргументов которой было раскрыто, предназначена для вывода указанного по счету наименьшего значения.
В поле следует указать координаты диапазона дополнительного столбца , который мы ранее преобразовали с помощью функции . Делаем все ссылки абсолютными.
В поле указывается, какое по счету наименьшее значение нужно вывести. Тут указываем координаты первой ячейки столбца с нумерацией, который мы недавно добавили. Адрес оставляем относительным. Щелкаем по кнопке .
- Оператор выводит результат – число . Именно оно наименьшее из нумерации несовпадающих строк табличных массивов. С помощью маркера заполнения копируем формулу до самого низа.
- Теперь, зная номера строк несовпадающих элементов, мы можем вставить в ячейку и их значения с помощью функции . Выделяем первый элемент листа, содержащий формулу . После этого переходим в строку формул и перед наименованием дописываем название без кавычек, тут же открываем скобку и ставим точку с запятой (). Затем выделяем в строке формул наименование и кликаем по пиктограмме .
- После этого открывается небольшое окошко, в котором нужно определить, ссылочный вид должна иметь функция или предназначенный для работы с массивами. Нам нужен второй вариант. Он установлен по умолчанию, так что в данном окошке просто щелкаем по кнопке .
- Запускается окно аргументов функции . Данный оператор предназначен для вывода значения, которое расположено в определенном массиве в указанной строке.
Как видим, поле уже заполнено значениями функции . От уже существующего там значения следует отнять разность между нумерацией листа Excel и внутренней нумерацией табличной области. Как видим, над табличными значениями у нас только шапка. Это значит, что разница составляет одну строку. Поэтому дописываем в поле значение без кавычек.
В поле указываем адрес диапазона значений второй таблицы. При этом все координаты делаем абсолютными, то есть, ставим перед ними знак доллара уже ранее описанным нами способом.
Жмем на кнопку .
- После вывода результат на экран протягиваем функцию с помощью маркера заполнения до конца столбца вниз. Как видим, обе фамилии, которые присутствуют во второй таблице, но отсутствуют в первой, выведены в отдельный диапазон.
Способ 5: сравнение массивов в разных книгах
При сравнении диапазонов в разных книгах можно использовать перечисленные выше способы, исключая те варианты, где требуется размещение обоих табличных областей на одном листе. Главное условие для проведения процедуры сравнения в этом случае – это открытие окон обоих файлов одновременно. Для версий Excel 2013 и позже, а также для версий до Excel 2007 с выполнением этого условия нет никаких проблем. Но в Excel 2007 и Excel 2010 для того, чтобы открыть оба окна одновременно, требуется провести дополнительные манипуляции. Как это сделать рассказывается в отдельном уроке.
Урок: Как открыть Эксель в разных окнах
Как видим, существует целый ряд возможностей сравнить таблицы между собой. Какой именно вариант использовать зависит от того, где именно расположены табличные данные относительно друг друга (на одном листе, в разных книгах, на разных листах), а также от того, как именно пользователь желает, чтобы это сравнение выводилось на экран.
Мы рады, что смогли помочь Вам в решении проблемы.
Помимо этой статьи, на сайте еще 13048 полезных инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам. Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.