Довольно часто перед пользователями Excel стоит задача сравнения двух таблиц или списков для выявления в них различий или отсутствующих элементов. Каждый пользователь справляется с этой задачей по-своему, но чаще всего на решение этой задачи уходит довольно большое количество времени, так как не все подходы к этой задаче рациональны. При этом существует несколько проверенных алгоритмов действий, позволяющих сравнивать списки или таблицы за достаточно короткое время с минимальными усилиями. Рассмотрим эти варианты подробно.
Способы сравнения
Способов сравнения табличных пространств в Excel довольно много, но все их можно разделить на три большие группы:
- сравнение списков, размещенных на одном листе;
- сравнение таблиц, размещенных на разных листах;
- сравнение табличных пространств в разных файлах.
Именно на основе этой классификации в первую очередь выбираются методы сравнения, определяются конкретные действия и алгоритмы выполнения поставленной задачи. Например, при выполнении сравнения в разных книгах вам нужно одновременно открыть два файла Excel.
Кроме того, следует сказать, что сравнивать позиции таблиц имеет смысл только тогда, когда они имеют схожую структуру.
Способ 1: простая формула
Самый простой способ сравнить данные в двух таблицах — использовать простую формулу равенства. Если данные совпадают, возвращается ИСТИНА, а если нет, то ЛОЖЬ. Вы можете сравнивать как числовые данные, так и текстовые данные. Недостатком этого метода является то, что его можно использовать только в том случае, если данные в таблице расположены или отсортированы одинаково, синхронизированы и имеют одинаковое количество строк. Давайте посмотрим, как использовать этот метод на практике на примере двух таблиц, размещенных на одном листе.
Итак, у нас есть две простые таблицы со списками сотрудников компании и их зарплатами. Вам необходимо сравнить списки сотрудников и выявить несоответствия между столбцами, где размещены фамилии.
- Теперь нам нужно проделать аналогичную операцию с остальными ячейками обеих таблиц в столбцах, которые мы сравниваем. Но вы можете просто скопировать формулу, что сэкономит много времени. Этот фактор особенно важен при сравнении списков с большим количеством строк.
Процедуру копирования проще всего выполнить с помощью маркера заполнения. Наводим курсор на правый нижний угол ячейки, где у нас появился показатель «ИСТИНА». При этом он должен превратиться в черный крест. Это маркер заполнения. Нажмите левую кнопку мыши и перетащите курсор вниз на количество строк в матрицах сравниваемой таблицы.
- Как видите, в дополнительной колонке теперь отображаются все результаты сравнения данных в двух колонках табличных массивов. В нашем случае данные не совпали только в одной строке. При их сравнении формула вернула результат «ЛОЖЬ». Для всех остальных строк, как видим, формула сравнения дала показатель «ИСТИНА».
- Кроме того, есть возможность рассчитать количество расхождений по специальной формуле. Для этого выберите элемент на листе, где он должен появиться. Затем нажмите на иконку «Вставить функцию».
- В окне Мастера функций в группе Математические операторы выберите имя СУММПРОИЗВ. Нажимаем на кнопку «ОК».
- Активируется окно с аргументами функции СУММПРОИЗВ, основной задачей которой является вычисление суммы произведений в выбранном диапазоне. Но эту функцию можно использовать для наших целей. Синтаксис довольно прост:
=СУММПРОИЗВ(матрица1;матрица2;…)
Всего в качестве аргументов можно использовать адреса до 255 массивов. Но в нашем случае мы также будем использовать только два массива в качестве одного аргумента.
Ставим курсор в поле «Массив1» и выбираем область сравниваемых данных в первой области листа. После этого ставим в поле знак «не равно» () и выбираем сравниваемый диапазон для другого диапазона. Затем оборачиваем полученное выражение в круглые скобки, предварительно поставив два знака «-». В нашем случае мы получили следующее выражение:
--(A2:A7D2:D7)
Нажимаем на кнопку «ОК».
- Оператор выполняет расчет и отображает результат. Как видите, в нашем случае результат равен числу «1», то есть это означает, что в сравниваемых списках найдено одно несовпадение. Если бы списки были полностью идентичными, результат был бы равен числу «0».
Таким же образом можно сравнивать данные в таблицах, расположенных на разных листах. Но при этом желательно, чтобы строки в них были пронумерованы. В остальном процедура сравнения почти точно такая же, как описана выше, за исключением того, что при вводе формулы необходимо переключаться между листами. В нашем случае выражение будет выглядеть так:
=B2=Лист2!B2
То есть, как мы видим, перед координатами данных, которые расположены на других листах, отличных от того, где отображается результат сравнения, указан номер листа и восклицательный знак.
Способ 2: выделение групп ячеек
Сравнение можно выполнить с помощью инструмента выбора группы ячеек. Он также может сравнивать только синхронизированные и упорядоченные списки. Кроме того, в этом случае списки должны располагаться рядом друг с другом на одном листе.
- После этого, какой бы из двух вариантов вы ни выбрали, откроется окно для выбора групп ячеек. Установите переключатель в положение «Выбрать по рядам». Нажмите на кнопку «ОК».
- Как видите, несовпадающие значения строк после этого будут подсвечены другим оттенком. Кроме того, как можно судить по содержимому строки формулы, программа активирует одну из ячеек, расположенных в указанных строках, которые не совпадают.
Способ 3: условное форматирование
Вы можете сравнить, используя условное форматирование. Как и в предыдущем способе, сравниваемые области должны находиться в одной таблице Excel и быть синхронизированы друг с другом.
- Активируется окно Менеджера правил. Нажмите на кнопку «Создать правило».
- В открывшемся окне выберите пункт «Использовать формулу». В поле «Формат ячеек» пишем формулу, содержащую адреса первых ячеек в диапазонах сравниваемых столбцов, разделенных знаком «не равно» (). На этот раз только этому выражению будет предшествовать символ "=". Кроме того, ко всем координатам столбцов в этой формуле должна применяться абсолютная адресация. Для этого выделите формулу курсором и трижды нажмите клавишу F4. Как видите, возле всех адресов столбцов появился знак доллара, что означает, что ссылки стали абсолютными. Для нашего частного случая формула будет иметь следующий вид:
=$A2$D2
Пишем это выражение в поле выше. Затем нажмите на кнопку «Формат…».
- Окно Формат ячеек активируется. Перейдите на вкладку «Заливка». Здесь в списке цветов мы останавливаем выделение на том цвете, которым хотим раскрасить элементы, где данные не совпадают. Нажмите на кнопку «ОК».
- Вернитесь в окно создания правила форматирования, нажмите кнопку «ОК».
- После автоматического перехода в окно «Управление правилами» нажмите кнопку «ОК» и в нем.
- Теперь во второй таблице элементы, имеющие данные, не совпадающие с соответствующими значениями в первом табличном пространстве, будут выделены выбранным цветом.
Есть еще один способ использовать условное форматирование для выполнения задачи. Как и предыдущие варианты, он требует размещения обеих сравниваемых областей на одном листе, но в отличие от ранее описанных способов синхронизация данных или условие сортировки не будут обязательными, что отличает этот вариант от ранее описанных.
- Выполняем переход на вкладку под названием «Главная». Нажмите кнопку «Условное форматирование». В активированном списке выберите пункт «Правила выбора ячейки». В следующем меню выберите пункт «Дублировать значения».
- Откроется окно настроек выделения повторяющихся значений. Если вы все сделали правильно, в этом окне остается только нажать на кнопку «ОК». Хотя при желании можно выбрать другой цвет подсветки в соответствующем поле в этом окне.
- После того, как мы выполним указанное действие, все повторяющиеся элементы будут выделены выбранным цветом. Несоответствующие элементы остаются окрашенными в исходный цвет (по умолчанию белый). Таким образом, можно сразу наглядно увидеть, в чем разница между массивами.
При желании можно, наоборот, закрасить несовпадающие элементы, а совпадающие индикаторы оставить с заливкой одного цвета. В этом случае алгоритм действий почти такой же, но в окне настроек для выделения повторяющихся значений в первом поле вместо параметра «Повторяющийся» выберите параметр «Уникальный». Затем нажмите на кнопку «ОК».
Таким образом, будут выделены не совпадающие показатели.
Урок: Условное форматирование в Excel
Способ 4: комплексная формула
Вы также можете сравнивать данные, используя сложную формулу, которая является основой функции СЧЁТЕСЛИ. С помощью этого инструмента можно подсчитать, сколько раз каждый элемент из выбранного столбца во второй таблице повторяется в первой.
Оператор COUNTIF относится к группе статистических функций. Его задача — подсчитать количество ячеек, значения которых удовлетворяют заданному условию. Синтаксис этого оператора следующий:
=СЧЁТЕСЛИ(площадь; критерии)
Аргумент диапазона — это адрес массива, в котором подсчитываются совпадающие значения.
Аргумент Criteria указывает условие совпадения. В нашем случае это будут координаты конкретных ячеек в первом диапазоне таблицы.
- Мастер функций запущен. Перейдите в категорию «Статистика». Находим в списке название «СЧЁТЕСЛИ». Выбрав его, нажмите на кнопку «ОК».
- Откроется окно Аргументы оператора COUNTIF. Как видите, названия полей в этом окне соответствуют названиям аргументов.
Поместите курсор в поле «Диапазон». После этого, удерживая левую кнопку мыши, выделить все значения в столбце с названиями второй таблицы. Как видите, координаты сразу попадают в указанное поле. Но для наших целей мы должны сделать этот адрес абсолютным. Для этого выберите эти координаты в поле и нажмите клавишу F4.
Как видите, ссылка приняла абсолютный вид, для которого характерно наличие знаков доллара.
Затем перейдите в поле «Критерии», поставив туда курсор. Щелкаем по первому элементу с фамилией в первой области таблицы. В этом случае оставляем ссылку относительную. После того, как оно отобразится в поле, можно нажать кнопку «ОК».
- Результат отображается в элементе листа. Он равен числу «1». Это означает, что в списке фамилий второй таблицы фамилия «Гринев В.П.», которая стоит первой в списке первой таблицы, встречается один раз.
- Теперь нам нужно создать аналогичное выражение для всех остальных элементов первой таблицы. Для этого скопируем с помощью маркера заливки, как делали раньше. Ставим курсор в правую нижнюю часть элемента листа, содержащего функцию СЧЁТЕСЛИ, и после преобразования его в курсор заливки, зажимаем левую кнопку мыши и тянем курсор вниз.
- Как видите, программа вычислила попадания, сравнивая каждую ячейку в первой таблице с данными во втором диапазоне таблицы. В четырех случаях результат получился "1", а в двух случаях - "0". То есть программа не смогла найти два значения во второй таблице в первой таблице.
Конечно, это выражение для сравнения табличных показателей можно использовать и в нынешнем виде, но есть возможность его улучшить.
Сделаем так, чтобы значения, которые есть во второй таблице, но нет в первой, появлялись в отдельном списке.
- Откроется окно аргументов функции ЕСЛИ. Как видите, первое поле в окне уже заполнено значением оператора СЧЁТЕСЛИ. Но нам нужно добавить что-то еще в это поле. Ставим туда курсор и добавляем "=0" к уже существующему выражению без кавычек.
Затем перейдите в поле «Значение, если правда». Здесь мы будем использовать другую вложенную функцию — STRING. Вводим слово «СТРОКА» без кавычек, затем открываем скобки и указываем координаты первой ячейки с фамилией во второй таблице, после чего скобки закрываем. Конкретно в нашем случае в поле «Значение, если правда» было получено следующее выражение:
СТРОКА(D2)
Теперь оператор СТРОКА будет сообщать функции ЕСЛИ номер строки, в которой находится конкретная фамилия, и в случае, когда условие, указанное в первом поле, верно, функция ЕСЛИ выведет этот номер в ячейку. Нажмите на кнопку «ОК».
- Как видите, первый результат отображается как «ЛОЖЬ». Это означает, что значение не удовлетворяет условиям оператора IF. То есть первая фамилия встречается в обоих списках.
- С помощью маркера заполнения копируем выражение оператора ЕСЛИ на весь столбец обычным способом. Как видите, для двух позиций, которые присутствуют во второй таблице, но отсутствуют в первой, формула возвращает номера строк.
- Отступаем от области таблицы вправо и заполняем столбец числами по порядку, начиная с 1. Количество чисел должно совпадать с количеством строк в другой сравниваемой таблице. Вы также можете использовать маркер заполнения, чтобы ускорить процесс нумерации.
- После этого выделите первую ячейку справа от столбца чисел и нажмите значок «Вставить функцию».
- Откроется мастер функций. Перейдите на вкладку «Статистика» и выберите название «LITEN». Нажимаем на кнопку «ОК».
- Функция НАИМЕНЬШИЙ, окно аргументов которой открыто, предназначена для отображения наименьшего значения, указанного в счете.
В поле «Массив» следует указать координаты диапазона дополнительного столбца «Количество попаданий», которые мы предварительно преобразовали с помощью функции ЕСЛИ. Сделайте все ссылки абсолютными.
В поле «К» указано, какое наименьшее значение на счете будет отображаться. Здесь мы указываем координаты первой ячейки в пронумерованном столбце, который мы только что добавили. Оставьте адрес ближайших родственников. Нажимаем на кнопку «ОК».
- Оператор выводит результат - число 3. Оно является наименьшим из нумерации несовпадающих строк табличных массивов. Используйте маркер заполнения, чтобы скопировать формулу до конца.
- Теперь, когда мы знаем номера строк несовпадающих элементов, мы можем вставить их значения в ячейку с помощью функции ИНДЕКС. Выберите первый элемент на листе, который содержит формулу МАЛЕНЬКИЙ. После этого заходим в строку формул и перед названием "LITEN" добавляем имя "INDEX" без кавычек, сразу раскрываем скобки и ставим точку с запятой (;). Затем выберите в строке формул название «ИНДЕКС» и щелкните значок «Вставить функцию».
- После этого откроется небольшое окно, в котором вам предстоит решить, должен ли ссылочный тип иметь функцию ИНДЕКС или предназначен для работы с массивами. Нам нужен второй вариант. Он установлен по умолчанию, поэтому в этом окне просто нажмите кнопку «ОК».
- Откроется окно Аргументы функции ИНДЕКС. Этот оператор предназначен для вывода значения, которое помещается в определенный массив на заданную строку.
Как видите, поле «Номер строки» уже заполнено значениями функции LITEN. Из уже имеющегося там значения следует вычесть разницу между нумерацией листа Excel и внутренней нумерацией области таблицы. Как видите, у нас есть только заголовок над значениями таблицы. Это означает, что разница составляет одну строку. Поэтому добавляем значение «-1» без кавычек в поле «Номер строки.
В поле «Массив» введите адрес диапазона значений второй таблицы. При этом все координаты делаем абсолютными, то есть ставим перед ними знак доллара так, как мы это описали ранее.
Нажмите на кнопку «ОК».
- После вывода результата на экран растягиваем функцию с помощью дескриптора заполнения до конца столбца вниз. Как видите, обе фамилии, присутствующие во второй таблице, но отсутствующие в первой, вынесены в отдельную область.
Способ 5: сравнение массивов в разных книгах
При сравнении диапазонов между рабочими книгами вы можете использовать перечисленные выше методы, за исключением тех, которые требуют, чтобы оба табличных пространства находились на одном рабочем листе. Главным условием процедуры сравнения в этом случае является одновременное открытие окон обоих файлов. Для версий Excel 2013 и более поздних версий, а также версий до Excel 2007 с этим условием проблем не возникает. Но в Excel 2007 и Excel 2010 открытие обоих окон одновременно требует дополнительных манипуляций. Как это сделать, описано в отдельном уроке.
Урок: Как открыть Excel в разных окнах
Как видите, существует ряд возможностей для сравнения таблиц друг с другом. Какой вариант использовать, зависит от того, как именно расположены табличные данные относительно друг друга (на одном листе, в разных книгах, на разных листах), а также от того, как именно пользователь хочет, чтобы это сравнение выглядело.
