Как узнать свой пароль от Wi-Fi сети
Как увеличить быстродействие компьютера
Лучшие антивирусы 2015 года
Как найти человека в интернете
Как удалить вирусы с ПК
Как установить Windows 10

Функция сравнения двух таблиц в Excel на совпадения формулы

Jokend
|
|
просмотров: 1735
|
Опубликовано: 31-12-2019, 13:33
Функция сравнения двух таблиц в Excel на совпадения формулы

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

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

Группы сравнения:

— списки на одном листе;
— таблицы на нескольких листах;
— табличные диапазоны в разных файлах.

Вариант 1: формула

Использовать будем формулу равенства. Если данные не совпали — ЛОЖЬ, совпали — ИСТИННА.

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

Функция сравнения двух таблиц в Excel на совпадения формулы


Записываем знак "=" в дополнительной ячейке на рабочей области. Нажимаем на первую ячейку для сравнения, ставим снова «=» и нажимаем по второй напротив.

Функция сравнения двух таблиц в Excel на совпадения формулы


Нажимаем Enter и в ячейке появится результат.

Функция сравнения двух таблиц в Excel на совпадения формулы


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

Функция сравнения двух таблиц в Excel на совпадения формулы


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

Функция сравнения двух таблиц в Excel на совпадения формулы



Для выявления количества "ЛЖИ" выделяем любую другую ячейку и нажимаем на "Вставить функцию".

Функция сравнения двух таблиц в Excel на совпадения формулы


Появится окно, в котором отмечаем категорию "Математические" и функцию "СУММПРОИЗВ".

Функция сравнения двух таблиц в Excel на совпадения формулы


Появится окно аргументов.

Синтаксис функции:

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

Выделяем первую область сравнения и пишем знак "<>", что означает неравенство. Далее выделяем вторую область. Ставим скобки по обе стороны формулы и перед ней ставим два символа "-".

Функция сравнения двух таблиц в Excel на совпадения формулы


После записи формулы результат появится автоматически.

Функция сравнения двух таблиц в Excel на совпадения формулы


Если делать сравнение на разных листах, то формула будет такой:

=B2=Лист2!B2

Функция сравнения двух таблиц в Excel на совпадения формулы


Вариант 2: группы ячеек

Инструмент выделения групп ячеек работает только при условии, что таблицы на одном листе.

Порядок действий:

1. выделение массивов;
2. вкладка "Главная";
3. "Найти и выделить";
4. в перечне выбрать "Выделение группы ячеек...".

Функция сравнения двух таблиц в Excel на совпадения формулы


В появившемся окне нажимаем на "Выделить...".

Функция сравнения двух таблиц в Excel на совпадения формулы


Отмечаем пункт "Отличия по строкам".

Функция сравнения двух таблиц в Excel на совпадения формулы


Результат будет выведен автоматически и "ЛОЖЬ" выделится другим оттенком.

Функция сравнения двух таблиц в Excel на совпадения формулы


Вариант 3: форматирование

1 Способ форматирования

Последовательность:

1. выбрать основную область;
2. вкладка "Главная";
3. "Стили";
4. кликните на "Условное форматирование";
5. "Управление правилами".

Функция сравнения двух таблиц в Excel на совпадения формулы


В окне диспетчера кликните на "Создать правило".

Функция сравнения двух таблиц в Excel на совпадения формулы


Появится окно. Кликните на "Использовать формулу" и ниже под "Форматировать ячейки" введите формулу адресов ячеек. Перед ней поставьте знак "=".

Функция сравнения двух таблиц в Excel на совпадения формулы


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

Функция сравнения двух таблиц в Excel на совпадения формулы


Кликните на "ОК".

Функция сравнения двух таблиц в Excel на совпадения формулы


В "Диспетчере правил" также кликните на "ОК".

Функция сравнения двух таблиц в Excel на совпадения формулы


Выбранным ранее цветом, не соответствующие друг с другом ячейки будут выделены.

Функция сравнения двух таблиц в Excel на совпадения формулы


2 Способ форматирования

Выделить все области.

Функция сравнения двух таблиц в Excel на совпадения формулы


Последовательность:

1. вкладка "Главная";
2. "Стили";
3. "Условное форматирование";
4. Подпункт "Правила выделения ячеек";
5. Пункт "Повторяющиеся значения".

Функция сравнения двух таблиц в Excel на совпадения формулы


Сравните скриншот ниже и то, что высветилось у вас. Если все правильно, то кликните "ОК".

Функция сравнения двух таблиц в Excel на совпадения формулы


Одинаковые ячейки будут выделены другим цветом.

Функция сравнения двух таблиц в Excel на совпадения формулы


Можно изменить ракурс окрашивания на разные ячейки.

Функция сравнения двух таблиц в Excel на совпадения формулы


Теперь выделены разные ячейки.

Функция сравнения двух таблиц в Excel на совпадения формулы


Вариант 4: комплексная формула

Функция "СЧЁТЕСЛИ" является сложной. Она выполняет подсчет количества ячеек и выглядит так:

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

Кликните на любую другую ячейку и на "Вставить функцию".

Функция сравнения двух таблиц в Excel на совпадения формулы


В "Мастере функций" выберите категорию "Статистические" и функцию "СЧЁТЕСЛИ".

Функция сравнения двух таблиц в Excel на совпадения формулы


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

Функция сравнения двух таблиц в Excel на совпадения формулы


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

Функция сравнения двух таблиц в Excel на совпадения формулы


Копируем формулу маркером заполнения.

Функция сравнения двух таблиц в Excel на совпадения формулы


Значение "0" показывает, что формула не нашла совпадений каких-либо фамилий.

Функция сравнения двух таблиц в Excel на совпадения формулы


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

Выделяем "СЧЁТЕСЛИ" в ячейке и пишем перед формулой "ЕСЛИ".

Функция сравнения двух таблиц в Excel на совпадения формулы


В поле для выражения дописываем к формуле "=0". Ниже в поле для истинны вписываем "СТРОКА" и координаты первой ячейки второй таблицы.

Функция сравнения двух таблиц в Excel на совпадения формулы


Значение "ЛОЖЬ" отобразится напротив тех ячеек, которые имеют совпадения.

Функция сравнения двух таблиц в Excel на совпадения формулы


Маркером заполнения копируем формулу.

Функция сравнения двух таблиц в Excel на совпадения формулы


Справа проставляете количество строк второй таблицы по порядку.

Функция сравнения двух таблиц в Excel на совпадения формулы


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

Функция сравнения двух таблиц в Excel на совпадения формулы


В "Мастере функций" категория "Статистические" и функцию "НАИМЕНЬШИЙ".

Функция сравнения двух таблиц в Excel на совпадения формулы


В поле "Массив" ввести координаты столбца подсчитанных значений. В поле "К" координаты ячейки последнего столбца.

Функция сравнения двух таблиц в Excel на совпадения формулы


Число "3" означает наименьшую по нумерации ячейку, которая не имеет совпадений. Далее копируйте формулу маркером заполнения.

Функция сравнения двух таблиц в Excel на совпадения формулы


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

Функция сравнения двух таблиц в Excel на совпадения формулы


Сверяем данные на скриншоте и кликните "ОК".

Функция сравнения двух таблиц в Excel на совпадения формулы


В поле "Массив" вводим адрес области второй таблицы. Делаем все ссылки абсолютными.

Функция сравнения двух таблиц в Excel на совпадения формулы


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

Функция сравнения двух таблиц в Excel на совпадения формулы


Вариант 5: массивы в разных книгах

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

Функция сравнения двух таблиц в Excel на совпадения формулы

Как открыть файлы Excel в разных окнах

Сравнение таблиц в Excel на совпадения

Заключение

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



Рейтинг:
(голосов: 1)



Не пропустите похожие инструкции:

Комментариев пока еще нет. Вы можете стать первым!

Добавить комментарий!

Ваше Имя:
Ваш E-Mail:
Полужирный Наклонный текст Подчеркнутый текст Зачеркнутый текст | Выравнивание по левому краю По центру Выравнивание по правому краю | Вставка смайликов Выбор цвета | Скрытый текст Вставка цитаты Преобразовать выбранный текст из транслитерации в кириллицу Вставка спойлера
Введите код:



Популярное



Последние статьи

Какую ОС используете?
новую Windows 10
быструю Windows 8
стабильную Windows 7
требовательную Windows Vista
старинную Windows XP