Сравнение двух таблиц с целью выявления только совпадающих данных. Как сравнить две колонки в excel на совпадения


Сравнение двух таблиц с целью выявления только совпадающих данных

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

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

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

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

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

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

В этой статье

Сравнение двух таблиц с помощью объединений

Сравнение двух таблиц с использованием поля в качестве условия

Сравнение двух таблиц с помощью объединений

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

Допустим, вы работаете в университете и хотите узнать, как недавние изменения в учебном плане по математике повлияли на оценки учащихся. В частности, вас интересуют оценки тех студентов, у которых профилирующим предметом является математика. У вас уже есть таблица, содержащая данные о профилирующих предметах, и таблица, содержащая данные о студентах, которые их изучают. Данные об оценках хранятся в таблице "Учащиеся", а данные о профилирующих предметах — в таблице "Специализации". Чтобы увидеть, как после недавних изменений в учебном плане изменились оценки у тех, кто специализируется на математике, вам нужно просмотреть записи из таблицы "Учащиеся", соответствующие записям в таблице "Специализации".

Подготовка примера данных

В данном примере вы создаете запрос, который определяет, как недавние изменения в учебном плане по математике повлияли на оценки студентов с соответствующим профилирующим предметом. Используйте две приведенные ниже таблицы: "Специализации" и "Учащиеся". Добавьте их в базу данных.

Сделать это в Microsoft Office Access 2007 можно несколькими способами. Вы можете ввести данные вручную, скопировать каждую таблицу в редактор электронных таблиц (такой как Microsoft Office Excel 2007) и импортировать листы в Office Access 2007 или же вставить данные в текстовый редактор, например Блокнот, и импортировать их из созданных текстовых файлов.

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

Специализации

Код учащегося

Год

Специализация

123456789

2005

МАТЕМ

223334444

2005

АНГЛ

987654321

2005

МАТЕМ

135791357

2005

ИСТ

147025836

2005

БИОЛ

707070707

2005

МАТЕМ

123456789

2006

МАТЕМ

223334444

2006

АНГЛ

987654321

2006

ПСИХОЛ

135791357

2006

ИСТ ИССК

147025836

2006

БИОЛ

707070707

2006

МАТЕМ

Учащиеся

Код учащегося

Год

Семестр

Учебный план

Номер предмета

Оценка

123456789

2005

3

МАТЕМ

221

A

123456789

2005

3

АНГЛ

101

B

123456789

2006

1

МАТЕМ

242

C

123456789

2006

1

МАТЕМ

224

C

223334444

2005

3

АНГЛ

112

A

223334444

2005

3

МАТЕМ

120

C

223334444

2006

1

ПОЛИТ

110

A

223334444

2006

1

АНГЛ

201

B

987654321

2005

3

МАТЕМ

120

A

987654321

2005

3

ПСИХОЛ

101

A

987654321

2006

1

МАТЕМ

221

B

987654321

2006

1

МАТЕМ

242

C

135791357

2005

3

ИСТ

102

A

135791357

2005

3

ИСТ ИССК

112

A

135791357

2006

1

МАТЕМ

120

B

135791357

2006

1

МАТЕМ

141

C

147025836

2005

3

БИОЛ

113

B

147025836

2005

3

ХИМ

113

B

147025836

2006

1

МАТЕМ

120

D

147025836

2006

1

СТАТ

114

B

707070707

2005

3

МАТЕМ

221

B

707070707

2005

3

СТАТ

114

A

707070707

2006

1

МАТЕМ

242

D

707070707

2006

1

МАТЕМ

224

C

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

Ввод примеров данных вручную
  1. Откройте новую или существующую базу данных.

  2. на вкладке Создание в группе Таблицы нажмите кнопку Таблица.

    Изображение ленты Access

    Access добавит в базу данных новую пустую таблицу.

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

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

    По умолчанию в Access пустые поля обозначаются надписью Добавить поле в строке заголовков:

    Новое поле в режиме таблицы

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

  5. Введите данные в пример таблицы.

    По мере ввода данных Access определяет их тип для каждого поля. Для каждого поля таблицы задается тип данных, например "Число", "Текст" или "Дата/время". Это обеспечивает точный ввод данных и помогает предотвратить ошибки, например использование цифр номера телефона в вычислениях. Для этих примеров таблиц можно определить тип данных автоматически, но не забудьте проверить результаты.

  6. Завершив ввод данных, нажмите кнопку Сохранить или клавиши CTRL+S.

    Откроется диалоговое окно Сохранение документа.

  7. В поле Имя таблицы введите имя примера таблицы и нажмите кнопку ОК.

    Используйте имена образцов таблиц (например, "Специализации"), поскольку они также используются в разделах с описанием процедур в этой статье.

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

Если вас не интересует создание листа на основе примера данных, пропустите следующий раздел ("Создание листов с примерами данных").

Создание листов с примерами данных
  1. Запустите редактор электронных таблиц и создайте пустой файл. Если вы используете Excel, при его запуске по умолчанию создается пустая книга.

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

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

  4. Повторите шаги 2 и 3, чтобы скопировать второй пример таблицы на пустой лист и переименовать этот лист.

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

  5. Сохраните книгу в нужной папке на компьютере или в сети и переходите к следующей процедуре.

Создание таблиц базы данных на основе листов
  1. В новой или существующей базе данных:

    На вкладке Внешние данные в группе Импорт щелкните Excel.

    Изображение ленты Access

    -или-

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

    Откроется диалоговое окно Внешние данные — лист <имя программы>.

  2. Нажмите кнопку Обзор, найдите и откройте файл электронной таблицы, который вы создали на предыдущих этапах, и нажмите кнопку ОК.

    Откроется окно мастера импорта электронных таблиц.

    По умолчанию мастер выбирает первый лист в книге (в этом примере — лист "Специализации"), и данные из этого листа появляются в нижней части страницы мастера.

  3. Нажмите кнопку Далее.

  4. На следующей странице мастера установите флажок Первая строка содержит названия столбцов, а затем нажмите кнопку Далее.

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

  6. На следующей странице выберите вариант Не создавать ключ и нажмите кнопку Далее.

  7. По умолчанию Access использует имя листа для новой таблицы. Оставьте имя в поле Импорт в таблицу, а затем нажмите кнопку Готово.

  8. На странице Сохранение шагов импорта нажмите кнопку Закрыть, чтобы завершить работу мастера.

  9. Повторите шаги с 1 по 7 для каждого листа, чтобы создать для него таблицу.

Сравнение образцов таблиц и поиск соответствующих записей с использованием объединений

Теперь все готово для сравнения таблиц "Учащиеся" и "Специализации". Так как связи между двумя таблицами не определены, вам необходимо создать объединения соответствующих полей в запросе. Таблицы содержат по несколько полей, и вам потребуется создать объединение для каждой пары общих полей: "Код учащегося", "Год", а также "Учебный план" (в таблице "Учащиеся") и "Специализация" (в таблице "Специализации"). В данном случае нас интересует только математика, поэтому можно ограничить результаты запроса с помощью условия поля.

  1. Откройте базу данных, в которой вы сохранили примеры таблиц.

  2. На вкладке Создание нажмите кнопку Конструктор запросов.

  3. В диалоговом окне Добавление таблицы дважды щелкните таблицу, которая содержит нужные записи (Учащиеся), а затем дважды щелкните таблицу, с которой ее сравниваете (Специализации).

  4. Закройте диалоговое окно Добавление таблицы.

  5. Перетащите поле Код учащегося из таблицы Учащиеся в поле Код учащегося таблицы Специализации. В бланке запроса между двумя таблицами появится линия, которая показывает, что создано объединение. Дважды щелкните линию, чтобы открыть диалоговое окно Параметры объединения.

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

  7. Вам нужно создать еще два объединения. Для этого перетащите поле Год из таблицы Учащиеся в поле Год таблицы Специализации, а затем — поле Учебный план из таблицы Учащиеся в поле Специализация таблицы Специализации.

  8. В таблице Учащиеся дважды щелкните звездочку (*), чтобы добавить все поля таблицы в бланк запроса.

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

  9. В таблице Специализации дважды щелкните поле Специализация, чтобы добавить его в бланк.

  10. В бланке запроса снимите флажок в строке Показать столбца Специализация.

  11. В строке Условие отбора столбца Специализация введите МАТЕМ.

  12. На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.

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

К началу страницы

Сравнение двух таблиц с использованием поля в качестве условия

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

Допустим, вы работаете в университете и хотите узнать, как недавние изменения в учебном плане по математике повлияли на оценки учащихся. В частности, вас интересуют оценки тех студентов, у которых профилирующим предметом является математика. У вас уже есть таблицы "Специализации" и "Учащиеся". Данные об оценках хранятся в таблице "Учащиеся", а данные о профилирующих предметах — в таблице "Специализации". Чтобы увидеть, как изменились оценки у тех, кто специализируется на математике, вам нужно просмотреть записи из таблицы "Учащиеся", соответствующие записям в таблице "Специализации". Однако у одного из полей, которые вы хотите использовать для сравнения таблиц, тип данных не такой, как у поля, с которым оно сопоставляется.

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

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

Изменение типа данных в поле "Код учащегося" таблицы "Специализации"

  1. Откройте базу данных, в которой вы сохранили примеры таблиц.

  2. В области навигации щелкните таблицу "Специализации" правой кнопкой мыши и выберите пункт Конструктор.

    Таблица "Специализации" откроется в режиме конструктора.

  3. В столбце Тип данных измените для поля Код учащегося тип данных Число на Текст.

  4. Закройте таблицу "Специализации". Нажмите кнопку Да, когда вам будет предложено сохранить изменения.

Сравнение примеров таблиц и поиск соответствующих записей с использованием условия поля

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

  1. На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.

  2. В диалоговом окне Добавление таблицы дважды щелкните таблицу Учащиеся, а затем таблицу Специализации.

  3. Закройте диалоговое окно Добавление таблицы.

  4. Перетащите поле Год из таблицы Учащиеся в поле Год таблицы Специализации, а затем — поле Учебный план из таблицы Учащиеся в поле Специализация таблицы Специализации. Эти поля содержат данные одного типа, поэтому для их сравнения можно использовать объединения. Для сравнения полей с данными одного типа рекомендуется использовать объединения.

  5. Дважды щелкните звездочку (*) в таблице Учащиеся, чтобы добавить все поля таблицы в бланк запроса.

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

  6. В таблице Специализации дважды щелкните поле Код учащегося, чтобы добавить его в бланк.

  7. В бланке запроса снимите флажок в строке Показать столбца Код учащегося. В строке Условие отбора столбца Код учащегося введите Like [Учащиеся].[Код учащегося].

  8. В таблице Специализации дважды щелкните поле Специализация, чтобы добавить его в бланк.

  9. В бланке запроса снимите флажок в строке Показать столбца Специализация. В строке Условие отбора введите МАТЕМ.

  10. На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.

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

К началу страницы

support.office.com

Возможности средства диагностики электронных таблиц в Excel 2016 для Windows

Предположим, нужно сравнить версии книги, проверить ее на наличие проблем и несоответствий либо просмотреть связи между книгами или листами. Если на вашем компьютере установлен выпуск Office профессиональный плюс 2013 или Office профессиональный плюс 2016, в программе Excel доступна надстройка средства диагностики электронных таблиц.

Чтобы выполнить все эти и другие задачи, вы можете использовать команды на вкладке Inquire (Запрос). Вкладка Inquire (Запрос) ленты Excel содержит кнопки для описанных ниже команд.

Вкладка "Запрос" в Excel

Если вкладка Inquire (Запрос) не отображается на ленте Excel, см. раздел Включение надстройки Inquire (Запрос).

Сравнение двух книг

Команда Compare Files (Сравнить файлы) позволяет просмотреть различия между двумя книгами по ячейкам. Чтобы выполнить эту команду, нужно открыть две книги в Excel.

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

Результаты сравнения

Команда Сравнить файлы сравнивает два файла с помощью средства сравнения электронных таблиц (Майкрософт).

  • В Windows 10 вы можете запустить его, не открывая Excel. Для этого нажмите кнопку Пуск, введите Средство сравнения электронных таблиц и щелкните Средство сравнения электронных таблиц.

  • В Windows 8 нажмите кнопку Средство сравнения электронных таблиц на экране Приложения.

  • В Windows 7 нажмите кнопку Пуск, выберите пункт Все программы, а затем щелкните Microsoft Office 2013, Средства Office 2013 и Средство сравнения электронных таблиц 2013.

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

Анализ книги

Команда Workbook Analysis (Анализ книги) создает интерактивный отчет, отображающий подробные сведения о книге и ее структуре, формулах, ячейках, диапазонах и предупреждениях. На рисунке ниже показана очень простая книга, которая содержит две формулы и подключения данных к базе данных Access и текстовому файлу.

Отчет об анализе книги

Подробнее об этом можно узнать в разделе Анализ книги.

К началу страницы

Отображение связей книги

В книгах, связанных с другими книгами с помощью ссылок на другие ячейки, можно запутаться. Используйте схему связей книги для создания интерактивной графической карты зависимостей, образованных соединениями (ссылками) между файлами. Типы ссылок в схеме могут включать другие книги, базы данных Access, текстовые файлы, HTML-страницы, базы данных SQL Server и другие источники данных. В схеме связей вы можете выбирать элементы и находить о них дополнительные сведения, а также перетаскивать линии соединения для изменения формы схемы.

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

Схема связей книги

Подробнее об этом можно узнать в статье Просмотр связей между книгами.

Отображение связей листа

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

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

Схема связей листа

Подробнее об этом можно узнать в статье Просмотр связей между листами.

Отображение связей ячейки

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

В данной схеме отображаются два уровня связей ячейки для ячейки A10 на листе 5 в книге "Книга1.xlsx". Эта ячейка зависит от ячейки C6 на листе 1 в другой книге — "Книга2.xlsx" и влияет на несколько ячеек на других листах в том же файле.

Схема связей ячейки

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

К началу страницы

Очистка лишнего форматирования ячеек

Если книга при открытии медленно загружается или ее размер становится чрезмерным, вероятной причиной этого может быть форматирование строк или столбцов, о котором вы даже не подозреваете. Используйте команду Clean Excess Cell Formatting (Удалить лишнее форматирование ячеек) для удаления лишнего форматирования и значительного уменьшения размера файла. Это помогает избежать "раздувания электронной таблицы", что увеличивает скорость работы Excel.

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

Подробнее об этом можно узнать в статье Очистка лишнего форматирования ячеек на листе.

Управление паролями

Если вы используете функции надстройки Inquire (Запрос) для выполнения анализа и сравнения защищенных паролем книг, потребуется добавить пароль книги в список паролей, чтобы с помощью надстройки Inquire (Запрос) можно было открыть сохраненную копию книги. Используйте команду Workbook Passwords (Пароли книги) на вкладке Inquire (Запрос), чтобы добавить пароли, которые будут сохранены на компьютере. Эти пароли шифруются и доступны только вам.

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

К началу страницы

support.office.com

Расширенное сравнение двух колонок (списков) - Макросы и программы VBA - Excel - Каталог статей

Введение в задачу

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

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

Вот перед вами упрощённый учебный пример такой задачи.

Путём нехитрых развлечений с функцией ВПР, вы можете получить такой результат:

То есть мы нашли пересечение наших двух диапазонов там, где ВПР вернул значение. Отфильтровав по #Н/Д в каждой таблице, мы получаем список значений того, что есть слева, но отсутствует справа, и того, что есть справа, но отсутствует слева. Это максимум того, что можно выжать из стандартного подхода с ВПР.

Недостатки стандартного подхода:

  • Мы потратили много сил. Если таких таблиц у нас много, то такой метод не годится. Это и трудоёмко и слишком легко запутаться.
  • Как мы знаем, ВПР ищет первое совпадение и значит о том, что, например, в левой таблице два значения 040310475653, а в правой только одно, мы можем и не узнать, особенно, если таблицы большие.
  • В случае с #Н/Д мы также не будем понимать структуру аномалий наших данных. Например, то, что значение 40310307297 справа встречается дважды.
  • У нас не будет информации, сколько вообще уникальных значений встречается в каждой таблице.

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

Основные возможности предлагаемого инструмента:

  • Исчерпывающее сравнение двух списков с выявлением всех возможных аномалий в данных
  • Сравнение осуществляется как на месте, где расположены указанные пользователем диапазоны (вставляется колонка справа), так и на отдельном новом листе рабочей книги
  • Цветовое акцентирование результатов сравнения
  • Предоставление детальной статистики по результатам сравнения (вставляется в комментарий к ячейке заголовка столбца, где происходит сравнение)
  • Автоопределение столбца с данными при указании диапазонов (достаточно указать одну ячейку)
  • Две модели сравнения: простая и обычная.
  • Учёт регистра текста, если в этом есть необходимость
  • Учёт наличия / отсутствия заголовка у диапазонов
  • 2 типа сортировки
  • Возможность заменить стандартные статусы сравнения на пользовательские

Используемая концепция сравнения списков

Концепция очень проста и легка для понимания при минимуме усилий.

  1. 2 сравниваемых диапазона будем называть ЛЕВЫЙ и ПРАВЫЙ. Это очевидно и естественно, если таблицы располагаются на одном листе. В случае разных листов, левым диапазоном можно называть ту таблицу, чей рабочий лист располагается левее листа второй таблицы.
  2. Каждое значение в сравниваемых списках получит СТАТУС, означающий то, как данное значение соотносится с аналогичным значением во второй таблице и (!) с такими же значениями в своей таблице, если данное значение повторяется.
  3. Есть 2 набора статусов: упрощённый и обычный.
  4. Упрощенные статусы: BOTH, LEFT, RIGHT.
    • BOTH - значение есть в обоих столбцах. Например, если значение "5" встречается в левой таблице 2 раза, а в правой 3 раза, то все эти строки получат статус BOTH
    • LEFT - значение встречается только в левой таблице
    • RIGHT - значение встречается только в правой таблице
  5. Обычные статусы (вы можете выбрать, какой набор статусов будете использовать в форме управления данным инструментом):
  • Группа BOTH:
    • L1R1 - в левои и правом диапазонах есть по одному значению
    • LnRn - в левои и правом диапазонах есть по нескольку (более 1) значений с каждой стороны.
    • L1Rn - слева - одно значение, справа - несколько
    • LnR1 - слева - несколько значений, справа - одно
  • Группа LEFT:
    • L1R0 - в левом диапазоне одно значение, в правом такого нет
    • LnR0 - в левом диапазоне несколько одинаковых значений, в правом таких нет
  • Группа RIGHT:
    • L0R1 - в левом диапазоне нет таких значений, в правом - одно
    • L0Rn - в левом диапазоне нет таких значений, в правом - несколько (более одного)

Результаты работы инструмента

Вот как выглядят результаты сравнения примера из начала статьи

Мы видим, что получается следующая картина:

  1. В левом списке есть 12 значений, которые отсутствуют в правом (статус L1R0)
  2. В левом списке задвоено значение 040310475653 (статус LnR1)
  3. Только 3 значения совпали в списках по принципу "один к одному" (статус L1R1)
  4. В правом списке есть 7 значений, которые отсутствуют в левом списке (статус L0R1)
  5. В правом списке есть 4 строки (две по 2 значения), которых нет слева и которые задвоены справа (статус L0Rn)
  6. И слева, и справа есть 2 значения, которые встречаются единожды слева, но которые задвоены справа (статус L1Rn)

и режим объединенных списков:

Как видите, моя утилита всё разложила по полочкам!

Пользовательский интерфейс

Описание элементов управления:
  1. Списки для сравнения - Левый список и Правый список
    • Сравниваемые списки должны быть в составе одной книги Excel
    • Списки могут располагаться на разных листах книги
    • Если они располагаются на одном листе, то колонка левого списка должен быть действительно левее правой колонки
    • Рекомендуется заполнять данные поля ввода при помощи кнопок Выбор
    • Через кнопку Выбор достаточно указать одну ячейку нужной колонки, программа автоматически расширит ваш выбор на всю область в составе этого столбца, к которой относится указанная ячейка. В этой связи пустые ячейки в сравниваемых колонках рекомендуется заменять на какие-то текстовые константы, типа "Пусто".
  2. Разное - Выделить цветом
    • Если указана данная опция, то колонки со сравниваемыми значениями и колонка со статусами получают стандартное цветовое акцентирование, образцы которого вы можете видеть ниже
    • Простые статусы:
    • Обычные статусы:
  3. Разное - Объединить списки
    • По умолчанию эта опция не выбрана и списки сравниваются в том месте, где они расположены. Справа от колонки с данными вставляется столбец со статусами сравнения.
    • Если эта опция выбрана, то создаётся новый лист, куда помещается таблица с объединенными уникальными значениями из сравниваемых списков. Данная таблица имеет 4 столбца: Значения, Кол-во слева, Кол-во справа, Статус.
    • Данный режим удобен для детального анализа всех аномалий в данных. В частности только так видны конкретные количества строк у статусов с буквой "n". Например, LnRn или L0Rn.
    • Только в режиме объединения списка таблица может сортироваться, так как в проивном случае это могло бы повредить ваши данные - ведь указанный столбец может быть в составе большой таблицы, но определение координат этой таблицы (для сортировки) уже слишком выходит за рамки данного инструмента.
  4. Разное - Учёт регистра
    • Полезно при анализе текстовых списков, где может быть важен регистр текстовых значений.
  5. Разное - Статистика в комм.
    • При этом создаётся комментарий к ячейке, содержащей заголовок столбца со статусом сравнения данных.
    • Таких комментариев 2, если списки сравниваются на своих оригинальных местах, и один, если они объединены.
    • Комментарий выглядит примерно так
  6. Разное - Простые статусы
    • Осуществляется выбор между простыми и обычными статусами. Данные статусы были описаны выше.
  7. Разное - Есть заголовки
    • Указываем имеют ли ваши списки заголовки столбцов. По умолчания включено.
  8. Где сравниваем? - Оригинальное место или Отдельный лист
    • Настройка блокируется, если выбрана опция Объединить списки.
    • В противном случае вы в праве выбрать место самостоятельно.
  9. Сортировка - По значению или По статусу
    • Настройка блокируется (по умолчанию), если не выбрана опция Объединить списки, так как сотрировка осуществляется только на отдельном листе.
  10. Дополнительно - Альтернативные статусы
    • По умолчанию отключено. Включаем, если вы хотите по каким-то своим причинам использовать свои статусы.
  11. Дополнительно - Обычные статусы или Упрощённые статусы
    • Статусы сохраняются на скрытом листе той книги, где вы воспользовались этим инструментом, и в следующий раз подгружаются оттуда на форму. То есть вам не придётся их вводить постоянно.
  12. Сравнить списки
    • Кнопка, которую необходимо нажать для запуска процедуры сравнения после того, как вы выбрали все необходимые вам опции.

Скачать утилиту

Можно здесь. Текущая версия 0.05 от 09.12.2014.

Как установить данную надстройку

Инструкция для MS Excel 2013 (или 2010)

Инструкция для MS Excel 2007

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

perfect-excel.ru

Как сравнивать две строки в программе «Excel»?

excel-2013-12-535x535

Когда вы используете табличный процессор Excel (от Microsoft Office), кроме задач по сравнению числовых значений, часто необходимо бывает сравнить текстовые (или «строковые») данные в ячейках таблицы. Сделать это можно так: в Excel есть встроенные  функции сравнения, и если результат операции необходимо получить в виде цифрового или же логического значения нужно воспользоваться этим сравнением. Так же можно использовать и опции условного форматирования, это делается в том случае, когда результатом должно стать визуальное выделение всех совпадающих (или же несовпадающих) ячеек данной таблицы.

Воспользуемся встроенной функцией для сравнения ячеек СЧЁТЕСЛИ, когда нужно сравнить текстовые показатели в ячейках колонки таблицы непосредственно с образцом текста и потом пересчитать все возможные совпадающие значения. Начинаем с заполнения конкретной колонки текстовыми значениями, потом делаем это в другой колонке и щелкаем ячейку, где вы хотите увидеть результат вашего подсчета, и там вводим соответствующую формулу. К примеру, если проверяемые показатели находятся в колонке типа «A», а результат нужно поместить в 1-ю ячейку колонки типа «C», то всё ее содержимое должно выглядеть так: =СЧЁТЕСЛИ($A:$A;"Виноград").  «Виноград» здесь, это строковое значение, непосредственно с которым и сравниваются показатели всех ячеек данной колонки «A». Можно так же не указывать это в формуле, но поместить в свободную отдельную ячейку (в «B1», например) и вставить в формулу нужную ссылку: =СЧЁТЕСЛИ($A:$A;B1).

Дальше мы будем использовать опции условного форматирования, особенно, если нужно визуально выделить в конкретной таблице результаты сравнения строковых переменных. Так, если необходимо выделить в данной колонке ячейки «A», текст совпадающий с образцом находящимся в ячейке «B1», старайтесь начинать с выделения данной колонки, а для этого  щелкните по её заголовку. Потом щелкните по кнопке «Условное форматирование», находящееся в группе команд типа «Стили», закладке «Главная» в программе Excel. Переходим в раздел под названием «Правила выделения ячеек» с выбором строки «Равно». Там указываем ячейку-образец (щелкаем по клетке «B1») и выбираем в выпадающем списке вариант для оформления совпадающих строк. Нажимаем кнопку «OK».

Тут нужно использовать комбинацию встроенных функций типа «ЕСЛИ» и «СЦЕПИТЬ» и  при необходимости сравнивать  с данным образцом не одну, а несколько текстовых ячеек. Функция «СЦЕПИТЬ» будет соединять указанные ей значения непосредственно в одну строковую переменную. К примеру, команда типа «СЦЕПИТЬ» (шаг «A1» или «B1») в строке из ячейки типа «A1» добавит нужный текст « и », ну а после него он поместит строку из другой ячейки, а именно из  «B1». Сделанную таким способом строку можно затем сравнивать с образцом при помощи функции «ЕСЛИ». Если сравнивать необходимо не одну строку, то удобнее дать имя (своё собственно) ячейке-образцу. Что бы это сделать щелкните ее, а потом влево от строки формулы вместо обозначения ячейки («C1» например) наберите новое ее имя («образец», к примеру). Затем нужно кликнуть ту ячейку, в которой и должен быть результат вашего сравнения, и вводим формулу такого типа:

ЕСЛИ(СЦЕПИТЬ(A1;" и ";B1)=образец;1;0)

Тут, единица -  значение, которое станет содержать ячейка с формулой, когда сравнение даст вам положительный результат, а цифра ноль – то же самое, но для отрицательного результата. Размножать эту формулу ко всем строкам таблицы, которые необходимо сравнить с данным образцом очень легко — наведите курсор к правому нижнему углу ячейки и, в тот момент, когда  курсор изменится (он станет черным крестиком), нужно нажать на левую кнопку мышки и растянуть эту ячейку далеко вниз до последней из сравниваемых строк.

ewerty.ru

Сравнение двух таблиц

Имеем две таблицы (например, старая и новая версия прайс-листа), которые надо сравнить и оперативно найти отличия:

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

Для любой задачи в Excel почти всегда есть больше одного решения (обычно 4-5). Для нашей проблемы можно использовать много разных подходов:

  • функцию ВПР (VLOOKUP) - искать названия товаров из нового прайс-листа в старом и выводить старую цену рядом с новой, а потом ловить отличия
  • объединить два списка в один и построить по нему потом сводную таблицу, где наглядно будут видны отличия
  • использовать надстройку Power Query для Excel

Давайте разберем их все последовательно.

Способ 1. Сравнение таблиц функцией ВПР (VLOOKUP)

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

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

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

Плюсы этого способа: просто и понятно, "классика жанра", что называется. Работает в любой версии Excel.

Минусы тоже есть. Для поиска добавленных в новый прайс товаров придется делать такую же процедуру в обратную сторону, т.е. подтягивать с помощью ВПР новые цены к старому прайсу. Если размеры таблиц завтра поменяются, то придется корректировать формулы. Ну, и на действительно больших таблицах (>100 тыс. строк) все это счастье будет прилично тормозить.

Способ 2. Сравнение таблиц с помощью сводной

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

Теперь на основе созданной таблицы создадим сводную через Вставка - Сводная таблица (Insert - Pivot Table). Закинем поле Товар в область строк, поле Прайс в область столбцов и поле Цена в область значений:

Как видите, сводная таблица автоматически сформирует общий список всех товаров из старого и нового прайс-листов (без повторений!) и отсортирует продукты по алфавиту. Хорошо видно добавленные товары (у них нет старой цены), удаленные товары (у них нет новой цены) и изменения цен, если были.

Общие итоги в такой таблице смысла не имеют, и их можно отключить на вкладке Конструктор - Общие итоги - Отключить для строк и столбцов (Design - Grand Totals).

Если изменятся цены (но не количество товаров!), то достаточно просто обновить созданную сводную, щелкнув по ней правой кнопкой мыши - Обновить (Referesh).

Плюсы: такой подход на порядок быстрее работает с большими таблицами, чем ВПР. 

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

Способ 3. Сравнение таблиц с помощью Power Query

Power Query - это бесплатная надстройка для Microsoft Excel, позволяющая загружать в Excel данные практически из любых источников и трансформировать потом эти данные любым желаемым образом. В Excel 2016 эта надстройка уже встроена по умолчанию на вкладке Данные (Data), а для Excel 2010-2013 ее нужно отдельно скачать с сайта Microsoft и установить - получите новую вкладку Power Query.

Перед загрузкой наших прайс-листов в Power Query их необходимо преобразовать сначала в умные таблицы. Для этого выделим диапазон с данными и нажмем на клавиатуре сочетание Ctrl+T или выберем на ленте вкладку Главная - Форматировать как таблицу (Home - Format as Table). Имена созданных таблиц можно подкорректировать на вкладке Конструктор (я оставлю стандартные Таблица1 и Таблица2, которые получаются по-умолчанию).

Загрузите старый прайс в Power Query с помощью кнопки Из таблицы/диапазона (From Table/Range) с вкладки Данные (Data) или с вкладки Power Query (в зависимости от версии Excel). После загрузки вернемся обратно в Excel из Power Query командой Закрыть и загрузить - Закрыть и загрузить в... (Close & Load - Close & Load To...):

... и в появившемся затем окне выбрем Только создать подключение (Connection Only).

Повторите то же самое с новым прайс-листом. 

Теперь создадим третий запрос, который будет объединять и сравнивать данных из предыдущих двух. Для этого выберем в Excel на вкладке Данные - Получить данные - Объединить запросы - Объединить (Data - Get Data - Merge Queries - Merge) или нажмем кнопку Объединить (Merge) на вкладке Power Query.

В окне объединения выберем в выпадающих списках наши таблицы, выделим в них столбцы с названиями товаров и в нижней части зададим способ объединения - Полное внешнее (Full Outer):

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

В итоге получим слияние данных из обеих таблиц:

Названия столбцов в шапке лучше, конечно, переименовать двойным щелчком на более понятные:

А теперь самое интересное. Идем на вкладку Добавить столбец (Add Column) и жмем на кнопку Условный столбец (Conditional Column). А затем в открывшемся окне вводим несколько условий проверки с соответствующими им значениями на выходе:

Останется нажать на ОК и выгрузить получившийся отчет в Excel с помощью все той же кнопки Закрыть и загрузить (Close & Load) на вкладке Главная (Home):

Красота.

Причем, если в будущем в прайс-листах произойдут любые изменения (добавятся или удалятся строки, изменятся цены и т.д.), то достаточно будет лишь обновить наши запросы сочетанием клавиш Ctrl+Alt+F5 или кнопкой Обновить все (Refresh All) на вкладке Данные (Data).

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

Минусы: Требует установленной надстройки Power Query (в Excel 2010-2013) или Excel 2016. Имена столбцов в исходных данных не должны меняться, иначе получим ошибку "Столбец такой-то не найден!" при попытке обновить запрос.

Ссылки по теме

www.planetaexcel.ru

Поиск значений в списке данных

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

В этой статье

Поиск значений в списке по вертикали по точному совпадению

Поиск значений в списке по вертикали по приблизительному совпадению

Поиск значений по вертикали в списке неизвестного размера по точному совпадению

Поиск значений в списке по горизонтали по точному совпадению

Поиск значений в списке по горизонтали по приблизительному совпадению

Создание формулы подстановки с помощью мастера подстановок (только Excel 2007)

Поиск значений в списке по вертикали по точному совпадению

Для решения этой задачи можно использовать функцию ВПР или сочетание функций ИНДЕКС и ПОИСКПОЗ.

Примеры функции ВПР

Пример 1 функции ВПР

Пример 2 функции ВПР

Дополнительные сведения см. в разделе, посвященном функции ВПР.

Примеры функций ИНДЕКС и ПОИСКПОЗ

Функции ИНДЕКС и ПОИСКПОЗ можно использовать вместо функции ВПР

Что означает:

=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение "Капуста" в массиве B2:B10))

Формула ищет в C2:C10 первое значение, соответствующее значению Капуста (B7), и возвращает значение в ячейке C7 (100).

Дополнительные сведения см. в разделах, посвященных функциям ИНДЕКС и ПОИСКПОЗ.

К началу страницы

Поиск значений в списке по вертикали по приблизительному совпадению

Для выполнения этой задачи используется функция ВПР.

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

Пример формулы ВПР для поиска неточного совпадения

В приведенном выше примере функция ВПР ищет имя первого учащегося с 6 пропусками в диапазоне A2:B7. Учащихся с 6 пропусками в таблице нет, поэтому функция ВПР ищет первую запись со следующим максимальным значением, не превышающим 6. Она находит значение 5 и возвращает связанное с ним имя Алексей.

Дополнительные сведения см. в разделе, посвященном функции ВПР.

К началу страницы

Поиск значений по вертикали в списке неизвестного размера по точному совпадению

Для выполнения этой задачи используются функции СМЕЩ и ПОИСКПОЗ.

Примечание: Данный метод целесообразно использовать при поиске данных в ежедневно обновляемом внешнем диапазоне данных. Известна цена в столбце B, но неизвестно, сколько строк данных возвратит сервер, а первый столбец не отсортирован в алфавитном порядке.

Пример функций СМЕЩ и ПОИСКПОЗ

C1 — это левая верхняя ячейка диапазона (также называемая начальной ячейкой).

Формула ПОИСКПОЗ("Апельсины";C2:C7;0) ищет значение "Апельсины" в диапазоне C2:C7. Начальную ячейку не следует включать в этот диапазон.

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

К началу страницы

Поиск значений в списке по горизонтали по точному совпадению

Для выполнения этой задачи используется функция ГПР. См. пример ниже.

Пример формулы ГПР для поиска точного совпадения

Функция ГПР выполняет поиск по столбцу Продажи и возвращает значение из строки 5 в указанном диапазоне.

Дополнительные сведения см. в разделе, посвященном функции ГПР.

К началу страницы

Поиск значений в списке по горизонтали по приблизительному совпадению

Для выполнения этой задачи используется функция ГПР.

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

Пример формулы ГПР для поиска неточного совпадения

В приведенном выше примере функция ГПР ищет значение 11 000 в строке 3 в указанном диапазоне. Значение 11 000 отсутствует, поэтому она ищет следующее максимальное значение, не превышающее 11 000, и возвращает 10 543.

Дополнительные сведения см. в разделе, посвященном функции ГПР.

К началу страницы

Создание формулы подстановки с помощью мастера подстановок (только Excel 2007)

В Excel 2007 мастер подстановок создает формулу подстановки, основанную на данных листа, содержащих названия строк и столбцов. С помощью мастера подстановок можно найти остальные значения в строке, если известно значение в одном столбце, и наоборот. В формулах, которые создает мастер подстановок, используются функции ИНДЕКС и ПОИСКПОЗ.

  1. Щелкните ячейку в диапазоне.

  2. На вкладке Формулы в группе Решения выберите команду Подстановка.

  3. Если команда Подстановка недоступна, необходимо загрузить надстройка мастера подстановок.

    Загрузка надстройки мастера подстановок

  4. Нажмите кнопку Microsoft Office Изображение кнопки Office , а затем — кнопку Параметры Excel и выберите категорию Надстройки.

  5. В поле Управление выберите значениеНадстройки Excel и нажмите кнопку Перейти.

  6. В области Доступные надстройки установите флажок рядом с пунктом Мастер подстановок и нажмите кнопку ОК.

  7. Следуйте инструкциям мастера.

К началу страницы

support.office.com

Поиск отличий в двух списках

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

Вариант 1. Синхронные списки

Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант - используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE):

Число несовпадений можно посчитать формулой:

=СУММПРОИЗВ(--(A2:A20<>B2:B20))

или в английском варианте =SUMPRODUCT(--(A2:A20<>B2:B20))

Если в результате получаем ноль - списки идентичны. В противном случае - в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter.

Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special) - Отличия по строкам (Row differences). В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) - Выделение группы ячеек (Go to Special) на вкладке Главная (Home)

Excel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:

  • залить цветом или как-то еще визуально отформатировать
  • очистить клавишей Delete
  • заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
  • удалить все строки с выделенными ячейками, используя команду Главная - Удалить - Удалить строки с листа (Home - Delete - Delete Rows)
  • и т.д.

Вариант 2. Перемешанные списки

Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.

Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная - Условное форматирование - Правила выделения ячеек - Повторяющиеся значения (Home - Conditional formatting - Highlight cell rules - Duplicate Values):

Если выбрать опцию Повторяющиеся, то Excel выделит цветом совпадения в наших списках, если опцию Уникальные - различия.

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

В качестве альтернативы можно использовать функцию СЧЁТЕСЛИ (COUNTIF) из категории Статистические, которая подсчитывает сколько раз каждый элемент из второго списка встречался в первом:

Полученный в результате ноль и говорит об отличиях.

И, наконец, "высший пилотаж" - можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:

Выглядит страшновато, но свою работу выполняет отлично ;)

Ссылки по теме

 

www.planetaexcel.ru