Довольно часто перед пользователями Excel стоит задача сравнения двух таблиц или списков для выявления в них отличий или недостающих элементов. Каждый юзер справляется с этой задачей по своему, но чаще всего на решение указанного вопроса тратится довольно большое количество времени, так как далеко не все подходы к данной проблеме являются рациональными. В то же время, существует несколько проверенных алгоритмов действий, которые позволят сравнить списки или табличные массивы в довольно сжатые сроки с минимальной затратой усилий. Давайте подробно рассмотрим данные варианты.
Существует довольно много способов сравнения табличных областей в Excel, но все их можно разделить на три большие группы:
Именно исходя из этой классификации, прежде всего, подбираются методы сравнения, а также определяются конкретные действия и алгоритмы для выполнения задачи. Например, при проведении сравнения в разных книгах требуется одновременно открыть два файла Excel.
Кроме того, следует сказать, что сравнивать табличные области имеет смысл только тогда, когда они имеют похожую структуру.
Способ 1: простая формула
Самый простой способ сравнения данных в двух таблицах – это использование простой формулы равенства. Если данные совпадают, то она выдает показатель ИСТИНА, а если нет, то – ЛОЖЬ. Сравнивать можно, как числовые данные, так и текстовые. Недостаток данного способа состоит в том, что ним можно пользоваться только в том случае, если данные в таблице упорядочены или отсортированы одинаково, синхронизированы и имеют равное количество строчек. Давайте посмотрим, как использовать данный способ на практике на примере двух таблиц, размещенных на одном листе.
Итак, имеем две простые таблицы со списками работников предприятия и их окладами. Нужно сравнить списки сотрудников и выявить несоответствия между столбцами, в которых размещены фамилии.
- Для этого нам понадобится дополнительный столбец на листе. Вписываем туда знак «=»
. Затем кликаем по первому наименованию, которое нужно сравнить в первом списке. Опять ставим символ «=»
с клавиатуры. Далее кликаем по первой ячейке колонки, которую мы сравниваем, во второй таблице. Получилось выражение следующего типа:
Хотя, конечно, в каждом конкретном случае координаты будут отличаться, но суть останется одинаковой.
- Щелкаем по клавише Enter , чтобы получить результаты сравнения. Как видим, при сравнении первых ячеек обоих списков программа указала показатель «ИСТИНА» , что означает совпадение данных.
- Теперь нам нужно провести аналогичную операцию и с остальными ячейками обеих таблиц в тех колонках, которые мы сравниваем. Но можно просто провести копирование формулы, что позволит существенно сэкономить время. Особенно данный фактор важен при сравнивании списков с большим количеством строк.
Процедуру копирования легче всего выполнить при помощи маркера заполнения. Наводим курсор на правый нижний угол ячейки, где мы получили показатель «ИСТИНА» . При этом он должен преобразоваться в черный крестик. Это и есть маркер заполнения. Жмем левую кнопку мыши и тянем курсор вниз на количество строчек в сравниваемых табличных массивах.
- Как видим, теперь в дополнительном столбце отобразились все результаты сравнения данных в двух колонках табличных массивов. В нашем случае не совпали данные только в одной строке. При их сравнении формула выдала результат «ЛОЖЬ» . По всем остальным строчкам, как видим, формула сравнения выдала показатель «ИСТИНА» .
- Кроме того, существует возможность с помощью специальной формулы подсчитать количество несовпадений. Для этого выделяем тот элемент листа, куда оно будет выводиться. Затем щелкаем по значку «Вставить функцию» .
- В окне Мастера функций в группе операторов «Математические» выделяем наименование СУММПРОИЗВ . Щелкаем по кнопке «OK» .
- Активируется окно аргументов функции СУММПРОИЗВ
, главной задачей которой является вычисление суммы произведений выделенного диапазона. Но данную функцию можно использовать и для наших целей. Синтаксис у неё довольно простой:
СУММПРОИЗВ(массив1;массив2;…)
Всего в качестве аргументов можно использовать адреса до 255 массивов. Но в нашем случае мы будем использовать всего два массива, к тому же, как один аргумент.
Ставим курсор в поле «Массив1» и выделяем на листе сравниваемый диапазон данных в первой области. После этого в поле ставим знак «не равно» (<> ) и выделяем сравниваемый диапазон второй области. Далее обворачиваем полученное выражение скобками, перед которыми ставим два знака «-» . В нашем случае получилось такое выражение:
--(A2:A7<>D2:D7)
Щелкаем по кнопке «OK» .
- Оператор производит расчет и выводит результат. Как видим, в нашем случае результат равен числу «1» , то есть, это означает, что в сравниваемых списках было найдено одно несовпадение. Если бы списки были полностью идентичными, то результат бы был равен числу «0» .
Таким же образом можно производить сравнение данных в таблицах, которые расположены на разных листах. Но в этом случае желательно, чтобы строки в них были пронумерованы. В остальном процедура сравнения практически точно такая, как была описана выше, кроме того факта, что при внесении формулы придется переключаться между листами. В нашем случае выражение будет иметь следующий вид:
B2=Лист2!B2
То есть, как видим, перед координатами данных, которые расположены на других листах, отличных от того, где выводится результат сравнения, указывается номер листа и восклицательный знак.
Способ 2: выделение групп ячеек
Сравнение можно произвести при помощи инструмента выделения групп ячеек. С его помощью также можно сравнивать только синхронизированные и упорядоченные списки. Кроме того, в этом случае списки должны располагаться рядом друг с другом на одном листе.
Способ 3: условное форматирование
Произвести сравнение можно, применив метод условного форматирования. Как и в предыдущем способе, сравниваемые области должны находиться на одном рабочем листе Excel и быть синхронизированными между собой.
Существует ещё один способ применения условного форматирования для выполнения поставленной задачи. Как и предыдущие варианты, он требует расположения обоих сравниваемых областей на одном листе, но в отличие от ранее описанных способов, условие синхронизации или сортировки данных не будет являться обязательным, что выгодно отличает данный вариант от ранее описанных.
При желании можно, наоборот, окрасить несовпадающие элементы, а те показатели, которые совпадают, оставить с заливкой прежним цветом. При этом алгоритм действий практически тот же, но в окне настройки выделения повторяющихся значений в первом поле вместо параметра «Повторяющиеся» следует выбрать параметр «Уникальные» . После этого нажать на кнопку «OK» .
Таким образом, будут выделены именно те показатели, которые не совпадают.
Способ 4: комплексная формула
Также сравнить данные можно при помощи сложной формулы, основой которой является функция СЧЁТЕСЛИ . С помощью данного инструмента можно произвести подсчет того, сколько каждый элемент из выбранного столбца второй таблицы повторяется в первой.
Оператор СЧЁТЕСЛИ относится к статистической группе функций. Его задачей является подсчет количества ячеек, значения в которых удовлетворяют заданному условию. Синтаксис данного оператора имеет такой вид:
СЧЁТЕСЛИ(диапазон;критерий)
Аргумент «Диапазон» представляет собой адрес массива, в котором производится подсчет совпадающих значений.
Аргумент «Критерий» задает условие совпадения. В нашем случае он будет представлять собой координаты конкретных ячеек первой табличной области.
Конечно, данное выражение для того, чтобы сравнить табличные показатели, можно применять и в существующем виде, но есть возможность его усовершенствовать.
Сделаем так, чтобы те значения, которые имеются во второй таблице, но отсутствуют в первой, выводились отдельным списком.
- Прежде всего, немного переработаем нашу формулу СЧЁТЕСЛИ , а именно сделаем её одним из аргументов оператора ЕСЛИ . Для этого выделяем первую ячейку, в которой расположен оператор СЧЁТЕСЛИ . В строке формул перед ней дописываем выражение «ЕСЛИ» без кавычек и открываем скобку. Далее, чтобы нам легче было работать, выделяем в строке формул значение «ЕСЛИ» и жмем по иконке «Вставить функцию» .
- Открывается окно аргументов функции ЕСЛИ
. Как видим, первое поле окна уже заполнено значением оператора СЧЁТЕСЛИ
. Но нам нужно дописать кое-что ещё в это поле. Устанавливаем туда курсор и к уже существующему выражению дописываем «=0»
без кавычек.
После этого переходим к полю «Значение если истина» . Тут мы воспользуемся ещё одной вложенной функцией – СТРОКА . Вписываем слово «СТРОКА» без кавычек, далее открываем скобки и указываем координаты первой ячейки с фамилией во второй таблице, после чего закрываем скобки. Конкретно в нашем случае в поле «Значение если истина» получилось следующее выражение:
СТРОКА(D2)
Теперь оператор СТРОКА будет сообщать функции ЕСЛИ номер строки, в которой расположена конкретная фамилия, и в случае, когда условие, заданное в первом поле, будет выполняться, функция ЕСЛИ будет выводить этот номер в ячейку. Жмем на кнопку «OK» .
- Как видим, первый результат отображается, как «ЛОЖЬ» . Это означает, что значение не удовлетворяет условиям оператора ЕСЛИ . То есть, первая фамилия присутствует в обоих списках.
- С помощью маркера заполнения, уже привычным способом копируем выражение оператора ЕСЛИ на весь столбец. Как видим, по двум позициям, которые присутствуют во второй таблице, но отсутствуют в первой, формула выдает номера строк.
- Отступаем от табличной области вправо и заполняем колонку номерами по порядку, начиная от 1 . Количество номеров должно совпадать с количеством строк во второй сравниваемой таблице. Чтобы ускорить процедуру нумерации, можно также воспользоваться маркером заполнения.
- После этого выделяем первую ячейку справа от колонки с номерами и щелкаем по значку «Вставить функцию» .
- Открывается Мастер функций . Переходим в категорию «Статистические» и производим выбор наименования «НАИМЕНЬШИЙ» . Щелкаем по кнопке «OK» .
- Функция НАИМЕНЬШИЙ
, окно аргументов которой было раскрыто, предназначена для вывода указанного по счету наименьшего значения.
В поле «Массив» следует указать координаты диапазона дополнительного столбца «Количество совпадений» , который мы ранее преобразовали с помощью функции ЕСЛИ . Делаем все ссылки абсолютными.
В поле «K» указывается, какое по счету наименьшее значение нужно вывести. Тут указываем координаты первой ячейки столбца с нумерацией, который мы недавно добавили. Адрес оставляем относительным. Щелкаем по кнопке «OK» .
- Оператор выводит результат – число 3 . Именно оно наименьшее из нумерации несовпадающих строк табличных массивов. С помощью маркера заполнения копируем формулу до самого низа.
- Теперь, зная номера строк несовпадающих элементов, мы можем вставить в ячейку и их значения с помощью функции ИНДЕКС . Выделяем первый элемент листа, содержащий формулу НАИМЕНЬШИЙ . После этого переходим в строку формул и перед наименованием «НАИМЕНЬШИЙ» дописываем название «ИНДЕКС» без кавычек, тут же открываем скобку и ставим точку с запятой (; ). Затем выделяем в строке формул наименование «ИНДЕКС» и кликаем по пиктограмме «Вставить функцию» .
- После этого открывается небольшое окошко, в котором нужно определить, ссылочный вид должна иметь функция ИНДЕКС или предназначенный для работы с массивами. Нам нужен второй вариант. Он установлен по умолчанию, так что в данном окошке просто щелкаем по кнопке «OK» .
- Запускается окно аргументов функции ИНДЕКС
. Данный оператор предназначен для вывода значения, которое расположено в определенном массиве в указанной строке.
Как видим, поле «Номер строки» уже заполнено значениями функции НАИМЕНЬШИЙ . От уже существующего там значения следует отнять разность между нумерацией листа Excel и внутренней нумерацией табличной области. Как видим, над табличными значениями у нас только шапка. Это значит, что разница составляет одну строку. Поэтому дописываем в поле «Номер строки» значение «-1» без кавычек.
В поле «Массив» указываем адрес диапазона значений второй таблицы. При этом все координаты делаем абсолютными, то есть, ставим перед ними знак доллара уже ранее описанным нами способом.
Жмем на кнопку «OK» .
- После вывода результат на экран протягиваем функцию с помощью маркера заполнения до конца столбца вниз. Как видим, обе фамилии, которые присутствуют во второй таблице, но отсутствуют в первой, выведены в отдельный диапазон.
Способ 5: сравнение массивов в разных книгах
При сравнении диапазонов в разных книгах можно использовать перечисленные выше способы, исключая те варианты, где требуется размещение обоих табличных областей на одном листе. Главное условие для проведения процедуры сравнения в этом случае – это открытие окон обоих файлов одновременно. Для версий Excel 2013 и позже, а также для версий до Excel 2007 с выполнением этого условия нет никаких проблем. Но в Excel 2007 и Excel 2010 для того, чтобы открыть оба окна одновременно, требуется провести дополнительные манипуляции. Как это сделать рассказывается в отдельном уроке.
Как видим, существует целый ряд возможностей сравнить таблицы между собой. Какой именно вариант использовать зависит от того, где именно расположены табличные данные относительно друг друга (на одном листе, в разных книгах, на разных листах), а также от того, как именно пользователь желает, чтобы это сравнение выводилось на экран.
Иногда нужно просмотреть в таблице Access только те записи, которым соответствуют записи из другой таблицы, содержащие поля с совпадающими данными. Например, вам может быть нужно просмотреть записи сотрудников, обработавших хотя бы один заказ, чтобы определить, кто из них заслуживает поощрения. Или же вам может потребоваться просмотреть контактные данные клиентов, проживающих в одном городе с сотрудником, чтобы организовать личную встречу.
Если вам нужно сравнить две таблицы Access и найти совпадающие данные, возможны два варианта действий.
Создайте запрос, объединяющий поля из каждой таблицы, которые содержат подходящие данные, используя для этого существующую связь или объединение, созданное для запроса. Этот способ оптимален по скорости возврата результатов запроса, но не позволяет объединять поля с данными разного типа.
Создать запрос для сравнения полей, в котором одно поле используется в качестве условия для другого. Этот способ обычно требует больше времени, поскольку при объединении строки исключаются из результатов запроса до чтения базовых таблиц, тогда как условия применяются к результатам запроса после чтения этих таблиц. Зато поле можно использовать в качестве условия для сравнения полей с данными разного типа, чего нельзя сделать при использовании объединений.
В этой статье рассматривается сравнение двух таблиц для выявления совпадающих данных и приводится образец данных, которые можно использовать в примерах процедур.
В этой статье
Сравнение двух таблиц с помощью объединений
Для сравнения двух таблиц с помощью объединений нужно создать запрос на выборку, включающий обе таблицы. Если между таблицами еще нет связи по полям, содержащим нужные данные, вам нужно создать объединения по ним. Объединений можно создать сколько угодно, но каждая пара объединенных полей должна содержать данные одного и того же или совместимого типа.
Допустим, вы работаете в университете и хотите узнать, как недавние изменения в учебном плане по математике повлияли на оценки учащихся. В частности, вас интересуют оценки тех студентов, у которых профилирующим предметом является математика. У вас уже есть таблица, содержащая данные о профилирующих предметах, и таблица, содержащая данные о студентах, которые их изучают. Данные об оценках хранятся в таблице "Учащиеся", а данные о профилирующих предметах - в таблице "Специализации". Чтобы увидеть, как после недавних изменений в учебном плане изменились оценки у тех, кто специализируется на математике, вам нужно просмотреть записи из таблицы "Учащиеся", соответствующие записям в таблице "Специализации".
Подготовка примера данных
В данном примере вы создаете запрос, который определяет, как недавние изменения в учебном плане по математике повлияли на оценки студентов с соответствующим профилирующим предметом. Используйте две приведенные ниже таблицы: "Специализации" и "Учащиеся". Добавьте их в базу данных.
Access предоставляет несколько способов добавления этих таблиц образец базы данных. Можно ввести данные вручную, скопируйте каждую таблицу в электронную таблицу программы (например, Microsoft Office Excel 2007) и затем импортируйте листы в Access или можно вставлять данные в текстовом редакторе, например Блокнот и затем импортировать данные из результирующего текстовые файлы.
В пошаговых инструкциях этого раздела объясняется, как вводить данные вручную на пустой лист, а также как копировать примеры таблиц в Excel и затем импортировать их в Access.
Специализации
Код учащегося | Специализация |
|
|---|---|---|
Учащиеся
Код учащегося | Семестр | Учебный план | Номер предмета | Оценка |
|
|---|---|---|---|---|---|
Если вы собираетесь вводить пример данных в электронной таблице, можете .
Ввод примеров данных вручную
Если вас не интересует создание листа на основе примера данных, пропустите следующий раздел ("Создание листов с примерами данных").
Создание листов с примерами данных
Создание таблиц базы данных на основе листов
Сравнение образцов таблиц и поиск соответствующих записей с использованием объединений
Теперь все готово для сравнения таблиц "Учащиеся" и "Специализации". Так как связи между двумя таблицами не определены, вам необходимо создать объединения соответствующих полей в запросе. Таблицы содержат по несколько полей, и вам потребуется создать объединение для каждой пары общих полей: "Код учащегося", "Год", а также "Учебный план" (в таблице "Учащиеся") и "Специализация" (в таблице "Специализации"). В данном случае нас интересует только математика, поэтому можно ограничить результаты запроса с помощью условия поля.
На вкладке Создание нажмите кнопку Конструктор запросов .
В диалоговом окне Добавление таблицы дважды щелкните таблицу, которая содержит нужные записи (Учащиеся ), а затем дважды щелкните таблицу, с которой ее сравниваете (Специализации ).
Закройте диалоговое окно Добавление таблицы .
Перетащите поле Код учащегося из таблицы Учащиеся в поле Код учащегося таблицы Специализации . В бланке запроса между двумя таблицами появится линия, которая показывает, что создано объединение. Дважды щелкните линию, чтобы открыть диалоговое окно Параметры объединения .
Обратите внимание на три варианта в диалоговом окне Параметры объединения . По умолчанию выбран вариант 1. В некоторых случаях требуется добавить в параметры объединения дополнительные строки из одной таблицы. Так как вам нужно найти только совпадающие данные, оставьте для объединения значение 1. Закройте диалоговое окно Параметры объединения , нажав кнопку Отмена .
Вам нужно создать еще два объединения. Для этого перетащите поле Год из таблицы Учащиеся в поле Год таблицы Специализации , а затем - поле Учебный план из таблицы Учащиеся в поле Специализация таблицы Специализации .
В таблице Учащиеся дважды щелкните звездочку (* ), чтобы добавить все поля таблицы в бланк запроса.
Примечание: Учащиеся.* .
В таблице Специализации дважды щелкните поле Специализация , чтобы добавить его в бланк.
Показать столбца Специализация .
В строке Условие отбора столбца Специализация введите МАТЕМ .
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить .
Сравнение двух таблиц с использованием поля в качестве условия
Иногда необходимо сравнить таблицы на основе полей с совпадающими значениями, но разными типами данных. Например, поле в одной таблице может иметь числовой тип данных, а сравнить его нужно с полем из другой таблицы, которое имеет текстовый тип данных. Поля, содержащие аналогичные данные разного типа, могут появиться при сохранении чисел в качестве текста (например, при импорте данных из другой программы). Так как создать объединения полей с данными разного типа невозможно, вам потребуется использовать другой способ. Для сравнения двух полей с данными разного типа можно использовать одно поле в качестве условия для другого.
Допустим, вы работаете в университете и хотите узнать, как недавние изменения в учебном плане по математике повлияли на оценки учащихся. В частности, вас интересуют оценки тех студентов, у которых профилирующим предметом является математика. У вас уже есть таблицы "Специализации" и "Учащиеся". Данные об оценках хранятся в таблице "Учащиеся", а данные о профилирующих предметах - в таблице "Специализации". Чтобы увидеть, как изменились оценки у тех, кто специализируется на математике, вам нужно просмотреть записи из таблицы "Учащиеся", соответствующие записям в таблице "Специализации". Однако у одного из полей, которые вы хотите использовать для сравнения таблиц, тип данных не такой, как у поля, с которым оно сопоставляется.
Для сравнения двух таблиц с использованием поля в качестве условия нужно создать запрос на выборку, включающий обе таблицы. Включите в запрос поля, которые хотите отобразить, а также поле, соответствующее тому полю, которое будет использоваться в качестве условия. Затем создайте условие для сравнения таблиц. Вы можете создать столько условий, сколько потребуется для сравнения полей.
Чтобы проиллюстрировать этот способ, мы используем , но в поле "Код учащегося" таблицы "Специализации" изменим числовой тип данных на текстовый. Так как нельзя создать объединение двух полей с разными типами данных, нам придется сравнить два поля "Код учащегося", используя одно поле в качестве условия для другого.
Изменение типа данных в поле "Код учащегося" таблицы "Специализации"
Откройте базу данных, в которой вы сохранили примеры таблиц.
В области навигации щелкните таблицу "Специализации" правой кнопкой мыши и выберите пункт Конструктор .
Таблица "Специализации" откроется в режиме конструктора.
В столбце Тип данных измените для поля Код учащегося тип данных Число на Текст .
Закройте таблицу "Специализации". Нажмите кнопку Да , когда вам будет предложено сохранить изменения.
Сравнение примеров таблиц и поиск соответствующих записей с использованием условия поля
Ниже показано, как сравнить два поля "Код учащегося", используя поле из таблицы "Учащиеся" в качестве условия для поля из таблицы "Специализации". С помощью ключевого слова Like вы можете сравнить два поля, даже если они содержат данные разного типа.
На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов .
В диалоговом окне Добавление таблицы дважды щелкните таблицу Учащиеся , а затем таблицу Специализации .
Закройте диалоговое окно Добавление таблицы .
Перетащите поле Год из таблицы Учащиеся в поле Год таблицы Специализации , а затем - поле Учебный план из таблицы Учащиеся в поле Специализация таблицы Специализации . Эти поля содержат данные одного типа, поэтому для их сравнения можно использовать объединения. Для сравнения полей с данными одного типа рекомендуется использовать объединения.
Дважды щелкните звездочку (* ) в таблице Учащиеся , чтобы добавить все поля таблицы в бланк запроса.
Примечание: При использовании звездочки для добавления всех полей в бланке отображается только один столбец. Имя этого столбца включает имя таблицы, за которым следуют точка (.) и звездочка (*). В этом примере столбец получает имя Учащиеся.* .
В таблице Специализации дважды щелкните поле Код учащегося , чтобы добавить его в бланк.
В бланке запроса снимите флажок в строке Показать столбца Код учащегося . В строке Условие отбора столбца Код учащегося введите Like [Учащиеся].[Код учащегося] .
В таблице Специализации дважды щелкните поле Специализация , чтобы добавить его в бланк.
В бланке запроса снимите флажок в строке Показать столбца Специализация . В строке Условие отбора введите МАТЕМ .
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить .
Запрос выполняется, и отображаются оценки по математике только тех учащихся, у которых этот предмет профилирующий.
Say you want to compare versions of a workbook, analyze a workbook for problems or inconsistencies, or see links between workbooks or worksheets. If Microsoft Office 365 or Office Professional Plus 2013 is installed on your computer, the Spreadsheet Inquire add-in is available in Excel.
You can use the commands in the Inquire tab to do all these tasks, and more. The Inquire tab on the Excel ribbon has buttons for the commands described below.
If you don"t see the Inquire tab in the Excel ribbon, see Turn on the Spreadsheet Inquire add-in .
Compare two workbooks
The Compare Files command lets you see the differences, cell by cell, between two workbooks. You need to have two workbooks open in Excel to run this command.
Results are color coded by the kind of content, such as entered values, formulas, named ranges, and formats. There"s even a window that can show VBA code changes line by line. Differences between cells are shown in an easy to read grid layout, like this:
The Compare Files command uses Microsoft Spreadsheet Compare to compare the two files. In Windows 8, you can start Spreadsheet Compare outside of Excel by clicking Spreadsheet Compare on the Apps screen. In Windows 7, click the Windows Start button and then > All Programs > Microsoft Office 2013 > Office 2013 Tools > Spreadsheet Compare 2013 .
To learn more about Spreadsheet Compare and comparing files, read Compare two versions of a workbook .
Analyze a workbook
The Workbook Analysis command creates an interactive report showing detailed information about the workbook and its structure, formulas, cells, ranges, and warnings. The picture here shows a very simple workbook containing two formulas and data connections to an Access database and a text file.
Show workbook links
Workbooks connected to other workbooks through cell references can get confusing. Use the to create an interactive, graphical map of workbook dependencies created by connections (links) between files. The types of links in the diagram can include other workbooks, Access databases, text files, HTML pages, SQL Server databases, and other data sources. In the relationship diagram, you can select elements and find more information about them, and drag connection lines to change the shape of the diagram.
This diagram shows the current workbook on the left and the connections between it and other workbooks and data sources. It also shows additional levels of workbook connections, giving you a picture of the data origins for the workbook.
Show worksheet links
Got lots of worksheets that depend on each other? Use the to create an interactive, graphical map of connections (links) between worksheets both in the same workbook and in other workbooks. This helps give you a clearer picture of how your data might depend on cells in other places.
This diagram shows the relationships between worksheets in four different workbooks, with dependencies between worksheets in the same workbook as well as links between worksheets in different workbooks. When you position your pointer over a node in the diagram, such as the worksheet named "West" in the diagram, a balloon containing information appears.
Show cell relationships
To get a detailed, interactive diagram of all links from a selected cell to cells in other worksheets or even other workbooks, use the Cell Relationship tool. These relationships with other cells can exist in formulas, or references to named ranges. The diagram can cross worksheets and workbooks.
This diagram shows two levels of cell relationships for cell A10 on Sheet5 in Book1.xlsx. This cell is dependent on cell C6 on Sheet 1 in another workbook, Book2.xlsx. This cell is a precedent for several cells on other worksheets in the same file.
To learn more about viewing cell relationships, read See links between cells .
Clean excess cell formatting
Ever open a workbook and find it loads slowly, or has become huge? It might have formatting applied to rows or columns you aren"t aware of. Use the Clean Excess Cell Formatting command to remove excess formatting and greatly reduce file size. This helps you avoid "spreadsheet bloat," which improves Excel"s speed.
Manage passwords
If you"re using the Inquire features to analyze or compare workbooks that are password protected, you"ll need to add the workbook password to your password list so that Inquire can open the saved copy of your workbook. Use the Workbook Passwords command on the Inquire tab to add passwords, which will be saved on your computer. These passwords are encrypted and only accessible by you.
Добрый день!
Эта статья посвящена решению такого вопроса, как сравнить две таблицы в Excel, ну или, по крайней мере, два столбца. Да, работать с таблицами удобно и хорошо, но вот когда нужно их сравнение, визуально это сделать достаточно затруднительно. Быть может таблицу до десятка или двух, вы и сможете визуально отсортировать, но вот когда они будут превышать тысячи, тут уже вам будет необходимо дополнительные инструменты анализа.
Увы, нет магической палочки, с помощью которой в один клик всё сделается и информация будет проверена, необходимо и подготовить данные, и прописать формулы, и иные процедуры позволяющие сравнить вашитаблицы.
Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:
Простой способ,
Это самые простой и элементарный способ сравнения двух таблиц. Сравнивать таким способом возможно, как числовые значение, так и текстовые. Для примера сравним два диапазона числовых значений, всего на всего прописав в соседней ячейке формулу их равенства =C2=E2 , как результат при равенстве ячеек мы получим ответ «ИСТИНА », а если совпадений нет, будет «ЛОЖЬ» . Теперь простым авто копированием копируем на нашу формулу позволяющую сравнить два столбика в Excel и видим разницу.
Быстрое выделение значений, которые отличаются
Это также не очень обременительный способ. Если вам просто нужно найти и удостовериться в наличии, ну или отсутствии отличий между таблицами, вам нужно на вкладке «Главная», выбрать кнопку меню «Найти и выделить», предварительно выделив диапазон где надо сравнить две таблицы в Excel. В открывшимся меню выберите пункт «Выделить группу ячеек…»
и в появившемся диалоговом окне выберите «отличия по строкам»
.
Сравнить две таблицы в Excel
с помощью условного форматирования
Очень хороший способ, при котором вы сможете видеть выделенным цветом значение, которые при сличении двух таблиц отличаются. Применить вы можете на вкладке «Главная»
, нажав кнопку «Условное форматирование»
и в предоставленном списке выбираем «Управление правилами»
.
В диалоговом окне «Диспетчер правил условного форматирования»
, жмем кнопочку «Создать правило»
и в новом диалоговом окне «Создание правила форматирования»
, выбираем правило . В поле «Изменить описание правила»
вводим формулу =$C2<>$E2 для определения ячейки, которое нужно форматировать, и нажимаем кнопку «Формат»
.
Определяем стиль того, как будет форматироваться наше значение, которое соответствует критерию. 
Теперь в списке правил появилось наше ново сотворённое правило, вы его выбираете, нажимаете «Ок»
.
И все правило применилось к нашему диапазону, где мы пытаемся проверить на похожесть две таблицы, и стало видны отличия, к которым применилось условное форматирование.
Как сравнить две таблицы в Excel
с помощью функции СЧЁТЕСЛИ и правил
Все вышеперечисленные способы хороши для упорядоченных таблиц, а вот когда данные, не упорядоченные необходимы иные способы один из которых мы сейчас и рассмотрим. Представим, к примеру, у нас есть 2 таблицы, значения в которых немного отличаются и нам необходимо сравнить эти таблицы для определения значения, которое отличается. Выделяем значение в диапазоне первой таблицы и на вкладке «Главная»
, пункт меню «Условное форматирование»
и в списке жмем пункт «Создать правило…»
, выбираем правило «Использовать формулу для определения форматируемых ячеек»
, вписываем формулу = ($C$1:$C$7;C1)=0
и выбираем формат условного форматирования.
Формула проверяет значение из определенной ячейки C1 и сравнивает ее с указанным диапазоном $C$1:$C$7 из второго столбика. Копируем правило на весь диапазон, в котором мы сравниваем таблицы и получаем выделенные цветом ячейки значения, которых не повторяется.
Как сравнить две таблицы в Excel
с помощью функции ВПР
В этом варианте мы будем использовать , которая позволит нам сравнить две таблицы на предмет совпадений. Для сравнения двух столбиков, введите формулу =ВПР(C2;$D$2:$D$7;1;0) и скопируйте ее на весь сравниваемый диапазон. Эта формула последовательно начинает проверять есть ли повторы значения из столбика А в столбике В, ну и соответственно возвращает значение элемента, если оно было там найдено если же значение не найдено получаем .
Как сравнить две таблицы в Excel
функции ЕСЛИ
Этот вариант предусматривает использования логической и отличие этого способа в том что для сравнения двух столбцов будет использован не весь целиком, а только та ее часть, которая нужна для сравнения.
Для примера, сравним два столбика А и В на рабочем листе, в соседней колонке С введем формулу: =ЕСЛИ( (ПОИСКПОЗ(C2;$E$2:$E$7;0));"";C2) и копируем ее на весь . Эта формула позволяет просматривать последовательно есть ли определенные элементы из указанного столбика А в столбике В и возвращает значение, в случае если оно было найдено в столбике В.
Сравнить две таблицы
с помощью макроса VBA
Есть много способов проверить две таблицы на схожесть, но некоторые варианты возможно только с помощью макросов VBA. Макросы для того что бы сравнить две таблицы, унифицирует этот процесс и существенно сокращает затраченное время на подготовку данных. Исходя из решаемой вами задачи и знаний макросов VBA, вы можете создавать любые варианты макросов. Ниже я привел методику, указанную на официальной страничке Microsoft. Вам нужно создать модуль для кода VBA и ввести код:
Sub Find_Matches() Dim CompareRange As Variant, x As Variant, y As Variant " Установка переменной CompareRange равной сравниваемому диапазону Set CompareRange = Range("B1:B11") " Если сравниваемый диапазон находится на другом листе или книге, " используйте следующий синтаксис " Set CompareRange = Workbooks("Книга2"). _ " Worksheets("Лист2").Range("B1:B11") " " Сравнение каждого элемента в выделенном диапазоне с каждым элементом " переменной CompareRange For Each x In Selection For Each y In CompareRange If x = y Then x.Offset(0, 2) = x Next y Next x End Sub
Sub Find_Matches () Dim CompareRange As Variant , x As Variant , y As Variant " Установка переменной CompareRange равной сравниваемому диапазону Set CompareRange = Range("B1:B11") " Еслисравниваемыйдиапазоннаходитсянадругомлистеиликниге, " используйте следующий синтаксис " Set CompareRange = Workbooks ("Книга2" ) . _ Горячее сочетание клавиш Alt+F8 . В новом диалоговом окне выбираете ваш макрос Find_similar и выполняете его. Сравнение
с помощью надстройки Inquire
Этот вариант сравнить стал доступен с релизом 2013 версии Excel, редактору добавили надстройку Inquire , которая позволит проанализировать и сравнить два файла Excel. Этот способ хорош, когда у вас есть необходимость сравнить два файла, в случае, когда ваш коллега работал над книгой и ввел некоторые изменения. Вот для определения этих изменений вам необходим инструмент WorkbookCompare в надстройкеInquire. Ну вот мы и рассмотрели 8 способов как сравнить две таблицы в Excel, эти варианты помогут вам решить свои аналитические задачи и упростят вашу работу. Был рад вам помочь!
|
Умение сравнивать два массива данных в Excel часто пригождается для людей, обрабатывающих большие объемы данных и работающих с огромными таблицами. Например, сравнение может быть использовано в , корректности занесения данных или внесение данных в таблицу в срок. В статье ниже описаны несколько приемов сравнения двух столбцов с данными в Excel.
Использование условного оператора ЕСЛИ
Метод использования условного оператора ЕСЛИ отличается тем, что для сравнения двух столбцов используется только необходимая для сравнения часть, а не весь массив целиком. Ниже описаны шаги по реализации данного метода:
Разместите оба столбца для сравнения в колонках A и B рабочего листа.
В ячейке С2 введите следующую формулу =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A2;$B$2:$B$11;0));"";A2) и протяните ее до ячейки С11. Данная формула последовательно просматривает наличие каждого элемента из столбца A в столбце B и возвращает значение элемента, если оно было найдено в столбце B.
Использование формулы подстановки ВПР
Принцип работы формулы аналогичен предыдущей методике, отличие заключается в , вместо ПОИСКПОЗ. Отличительной особенностью данного метода также является возможность сравнения двух горизонтальных массивов, используя формулу ГПР.
Чтобы сравнить два столбца с данными, находящимися в столбцах A и B(аналогично предыдущему способу), введите следующую формулу =ВПР(A2;$B$2:$B$11;1;0) в ячейку С2 и протяните ее до ячейки С11.
Данная формула просматривает каждый элемент из основного массива в сравниваемом массиве и возвращает его значение, если оно было найдено в столбце B. В противном случае Excelвернет ошибку #Н/Д.
Использование макроса VBA
Использование макросов для сравнения двух столбцов позволяет унифицировать процесс и сократить время на подготовку данных. Решение о том, какой результат сравнения необходимо отобразить, полностью зависит от вашей фантазии и навыков владения макросами. Ниже представлена методика, опубликованная на официальном сайте Микрософт.
1 | Sub
Find_Matches() |
В данном коде переменной CompareRange присваивается диапазон со сравниваемым массивом. Затем запускается цикл, который просматривает каждый элемент в выделенном диапазоне и сравнивает его с каждым элементом сравниваемого диапазона. Если были найдены элементы с одинаковыми значениями, макрос заносит значение элемента в столбец С.
Чтобы использовать макрос, вернитесь на рабочий лист, выделите основной диапазон (в нашем случае, это ячейки A1:A11), нажмите сочетание клавиш Alt+F8. В появившемся диалоговом окне выберите макрос Find_ Matches и щелкните кнопку выполнить.
После выполнения макроса, результат должен быть следующим:
Использование надстройки Inquire
Итог
Итак, мы рассмотрели несколько способов сравнения данных в Excel, которые помогут вам решить некоторые аналитические задачи и упростят работу в поиске повтояющихся (или уникальных) значений.