Методы сравнения таблиц в Microsoft Excel

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

Способы сравнения

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

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

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

    Способ 1: простая формула

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

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

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

      =A2=D2

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

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

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

    4. Как видим, теперь в дополнительном столбце отобразились все результаты сравнения данных в двух колонках табличных массивов. В нашем случае не совпали данные только в одной строке. При их сравнении формула выдала результат . По всем остальным строчкам, как видим, формула сравнения выдала показатель .
    5. Кроме того, существует возможность с помощью специальной формулы подсчитать количество несовпадений. Для этого выделяем тот элемент листа, куда оно будет выводиться. Затем щелкаем по значку .
    6. В окне в группе операторов выделяем наименование . Щелкаем по кнопке .
    7. Активируется окно аргументов функции , главной задачей которой является вычисление суммы произведений выделенного диапазона. Но данную функцию можно использовать и для наших целей. Синтаксис у неё довольно простой:

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

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

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

      —(A2:A7<>D2:D7)

      Щелкаем по кнопке .

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

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

    =B2=Лист2!B2

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

    Способ 2: выделение групп ячеек

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

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

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

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

    Способ 3: условное форматирование

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

    1. Прежде всего, выбираем, какую табличную область будем считать основной, а в какой искать отличия. Последнее давайте будем делать во второй таблице. Поэтому выделяем список работников, находящийся в ней. Переместившись на вкладку , щелкаем по кнопке , которая имеет месторасположение на ленте в блоке . Из выпадающего списка переходим по пункту .
    2. Активируется окошко диспетчера правил. Жмем в нем на кнопку .
    3. В запустившемся окне производим выбор позиции . В поле записываем формулу, содержащую адреса первых ячеек диапазонов сравниваемых столбцов, разделенные знаком «не равно» (). Только перед данным выражением на этот раз будет стоять знак . Кроме того, ко всем к координатам столбцов в данной формуле нужно применить абсолютную адресацию. Для этого выделяем формулу курсором и трижды жмем на клавишу . Как видим, около всех адресов столбцов появился знак доллара, что и означает превращение ссылок в абсолютные. Для нашего конкретного случая формула примет следующий вид:

      =$A2<>$D2

      Данное выражение мы и записываем в вышеуказанное поле. После этого щёлкаем по кнопке .

    4. Активируется окно . Идем во вкладку . Тут в перечне цветов останавливаем выбор на цвете, которым хотим окрашивать те элементы, где данные не будут совпадать. Жмем на кнопку .
    5. Вернувшись в окно создания правила форматирования, жмем на кнопку .
    6. После автоматического перемещения в окно щелкаем по кнопке и в нем.
    7. Теперь во второй таблице элементы, которые имеют данные, несовпадающие с соответствующими значениями первой табличной области, будут выделены выбранным цветом.

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

    1. Производим выделение областей, которые нужно сравнить.
    2. Выполняем переход во вкладку под названием . Делаем щелчок по кнопке . В активировавшемся списке выбираем позицию . В следующем меню делаем выбор позиции .
    3. Запускается окно настройки выделения повторяющихся значений. Если вы все сделали правильно, то в данном окне остается только нажать на кнопку . Хотя при желании в соответствующем поле данного окошка можно выбрать другой цвет выделения.
    4. После того, как мы произведем указанное действие, все повторяющиеся элементы будут выделены выбранным цветом. Те элементы, которые не совпадают, останутся окрашенными в свой изначальный цвет (по умолчанию белый). Таким образом, можно сразу визуально увидеть, в чем отличие между массивами.

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

    Таким образом, будут выделены именно те показатели, которые не совпадают.

    Урок: Условное форматирование в Экселе

    Способ 4: комплексная формула

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

    Оператор относится к статистической группе функций. Его задачей является подсчет количества ячеек, значения в которых удовлетворяют заданному условию. Синтаксис данного оператора имеет такой вид:

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

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

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

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

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

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

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

    4. В элемент листа выводится результат. Он равен числу . Это означает, что в перечне имен второй таблицы фамилия , которая является первой в списке первого табличного массива, встречается один раз.
    5. Теперь нам нужно создать подобное выражение и для всех других элементов первой таблицы. Для этого выполним копирование, воспользовавшись маркером заполнения, как это мы уже делали прежде. Ставим курсор в нижнюю правую часть элемента листа, который содержит функцию , и после преобразования его в маркер заполнения зажимаем левую кнопку мыши и тянем курсор вниз.
    6. Как видим, программа произвела вычисление совпадений, сравнив каждую ячейку первой таблицы с данными, которые расположены во втором табличном диапазоне. В четырех случаях результат вышел , а в двух случаях – . То есть, программа не смогла отыскать во второй таблице два значения, которые имеются в первом табличном массиве.

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

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

    1. Прежде всего, немного переработаем нашу формулу , а именно сделаем её одним из аргументов оператора . Для этого выделяем первую ячейку, в которой расположен оператор . В строке формул перед ней дописываем выражение без кавычек и открываем скобку. Далее, чтобы нам легче было работать, выделяем в строке формул значение и жмем по иконке .
    2. Открывается окно аргументов функции . Как видим, первое поле окна уже заполнено значением оператора . Но нам нужно дописать кое-что ещё в это поле. Устанавливаем туда курсор и к уже существующему выражению дописываем без кавычек.

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

      СТРОКА(D2)

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

    3. Как видим, первый результат отображается, как . Это означает, что значение не удовлетворяет условиям оператора . То есть, первая фамилия присутствует в обоих списках.
    4. С помощью маркера заполнения, уже привычным способом копируем выражение оператора на весь столбец. Как видим, по двум позициям, которые присутствуют во второй таблице, но отсутствуют в первой, формула выдает номера строк.
    5. Отступаем от табличной области вправо и заполняем колонку номерами по порядку, начиная от . Количество номеров должно совпадать с количеством строк во второй сравниваемой таблице. Чтобы ускорить процедуру нумерации, можно также воспользоваться маркером заполнения.
    6. После этого выделяем первую ячейку справа от колонки с номерами и щелкаем по значку .
    7. Открывается . Переходим в категорию и производим выбор наименования . Щелкаем по кнопке .
    8. Функция , окно аргументов которой было раскрыто, предназначена для вывода указанного по счету наименьшего значения.

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

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

    9. Оператор выводит результат – число . Именно оно наименьшее из нумерации несовпадающих строк табличных массивов. С помощью маркера заполнения копируем формулу до самого низа.
    10. Теперь, зная номера строк несовпадающих элементов, мы можем вставить в ячейку и их значения с помощью функции . Выделяем первый элемент листа, содержащий формулу . После этого переходим в строку формул и перед наименованием дописываем название без кавычек, тут же открываем скобку и ставим точку с запятой (). Затем выделяем в строке формул наименование и кликаем по пиктограмме .
    11. После этого открывается небольшое окошко, в котором нужно определить, ссылочный вид должна иметь функция или предназначенный для работы с массивами. Нам нужен второй вариант. Он установлен по умолчанию, так что в данном окошке просто щелкаем по кнопке .
    12. Запускается окно аргументов функции . Данный оператор предназначен для вывода значения, которое расположено в определенном массиве в указанной строке.

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

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

      Жмем на кнопку .

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

    Способ 5: сравнение массивов в разных книгах

    При сравнении диапазонов в разных книгах можно использовать перечисленные выше способы, исключая те варианты, где требуется размещение обоих табличных областей на одном листе. Главное условие для проведения процедуры сравнения в этом случае – это открытие окон обоих файлов одновременно. Для версий Excel 2013 и позже, а также для версий до Excel 2007 с выполнением этого условия нет никаких проблем. Но в Excel 2007 и Excel 2010 для того, чтобы открыть оба окна одновременно, требуется провести дополнительные манипуляции. Как это сделать рассказывается в отдельном уроке.

    Урок: Как открыть Эксель в разных окнах

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

    Мы рады, что смогли помочь Вам в решении проблемы.
    Помимо этой статьи, на сайте еще 13048 полезных инструкций.
    Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам. Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

    Источник: starhit
    Читайте также  Открываем файлы презентации PPT

    Домашние хитрости