Сравнение ячейки с диапазоном excel

Сравнение ячейки с диапазоном excel

трюки • приёмы • решения

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

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

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

Первый список — А2:В31 , этот диапазон называется OldList. Второй список — D2:E31 , диапазон называется NewList. Диапазоны были названы с помощью команды Формулы ► Определенные имена ► Присвоить имя. Давать названия диапазонам необязательно, но это облегчает работу с ними.

Начнем с добавления условного форматирования к старому списку.

  1. Выделите ячейки диапазона OldList.
  2. Выберите Главная ► Условное форматирование ► Создать правило для открытия диалогового окна Создание правила форматирования.
  3. В окне Создание правила форматирования выберите пункт под названием Использовать формулу для определения форматируемых ячеек.
  4. Введите эту формулу в поле окна (рис. 164.2): =СЧЁТЕСЛИ(NewList;A2)=0 .
  5. Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно. Лучше всего выбрать различные цвета заливки.
  6. Нажмите кнопку ОК.

Рис. 164.2. Применение условного форматирования

Ячейки в диапазоне NewList используют подобную формулу условного форматирования.

  1. Выделите ячейки диапазона NewList.
  2. Выберите Главная ► Условное форматирование ► Создать правило для открытия диалогового окна Создание правила форматирования.
  3. В окне Создание правила форматирования выберите пункт Использовать формулу для определения форматируемых ячеек.
  4. Введите эту формулу в поле окна: =СЧЕТЕСЛИ(OldList;D2)=0 .
  5. Нажмите кнопку Формат и задайте форматирование, которое будет применяться, когда условие истинно (другой цвет заливки).
  6. Нажмите кнопку ОК.

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

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

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

Ссылка на ячейку в функции СЧЁТЕСЛИ всегда должна быть в верхней левой ячейке выбранного диапазона.

Добрый день!

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

Читайте также:  Как увеличить видеопамять в биосе

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

Рассмотрим несколько вариантов и возможностей для сравнения таблиц в Excel:

Простой способ, как сравнить две таблицы в Excel

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

Быстрое выделение значений, которые отличаются

Это также не очень обременительный способ. Если вам просто нужно найти и удостовериться в наличии, ну или отсутствии отличий между таблицами, вам нужно на вкладке «Главная», выбрать кнопку меню «Найти и выделить», предварительно выделив диапазон где надо сравнить две таблицы в Excel. В открывшимся меню выберите пункт «Выделить группу ячеек…» и в появившемся диалоговом окне выберите «отличия по строкам».

Сравнить две таблицы в Excel с помощью условного форматирования

Очень хороший способ, при котором вы сможете видеть выделенным цветом значение, которые при сличении двух таблиц отличаются. Применить условное форматирование вы можете на вкладке «Главная», нажав кнопку «Условное форматирование» и в предоставленном списке выбираем «Управление правилами». В диалоговом окне «Диспетчер правил условного форматирования», жмем кнопочку «Создать правило» и в новом диалоговом окне «Создание правила форматирования», выбираем правило «Использовать формулу для определения форматируемых ячеек». В поле «Изменить описание правила» вводим формулу =$C2<>$E2 для определения ячейки, которое нужно форматировать, и нажимаем кнопку «Формат». Определяем стиль того, как будет форматироваться наше значение, которое соответствует критерию. Теперь в списке правил появилось наше ново сотворённое правило, вы его выбираете, нажимаете «Ок».

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

Как сравнить две таблицы в Excel с помощью функции СЧЁТЕСЛИ и правил

Все вышеперечисленные способы хороши для упорядоченных таблиц, а вот когда данные, не упорядоченные необходимы иные способы один из которых мы сейчас и рассмотрим. Представим, к примеру, у нас есть 2 таблицы, значения в которых немного отличаются и нам необходимо сравнить эти таблицы для определения значения, которое отличается. Выделяем значение в диапазоне первой таблицы и на вкладке «Главная», пункт меню «Условное форматирование» и в списке жмем пункт «Создать правило…», выбираем правило «Использовать формулу для определения форматируемых ячеек», вписываем формулу =СЧЁТЕСЛИ($C$1:$C$7;C1)=0 и выбираем формат условного форматирования.

Читайте также:  Команда создать файл linux

Формула проверяет значение из определенной ячейки 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 и ввести код:

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

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

Читайте также:  Ремонт йогуртницы своими руками

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

Рис. 6.1. Диапазоны для сравнения

Способ 1. Использование значений Истина и Ложь

В первом способе нужно ввести простую формулу в другой диапазон того же размера и формы. Удобно то, что можно добавить формулу за один шаг, не копируя и не вставляя данные. Чтобы сравнить диапазоны, показанные на рис. 6.1, выделите диапазон E1:G7, начиная с ячейки Е1. Это гарантирует, что ячейка Е1 будет активной ячейкой выделения. Выделив диапазон, щелкните строку формул и введите следующую формулу: =А1=А9

Если два набора данных находятся на разных рабочих листах, для хранения значений Истина/Ложь (True/False) можно использовать третий рабочий лист, введя формулу способом для массивов ячеек. Например, предполагая, что вторая таблица данных находится на листе Sheet2 и начинается с ячейки А9, а исходная таблица данных хранится на листе Sheetl и начинается с.ячейки А1, на третьем рабочем листе введите следующую формулу массива: =Sheet1!A1=Sheet2!A9. При работе с большими объемами данных бывает полезно уменьшить масштаб листа.

Способ 2. Условное форматирование

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

Снова предполагая, что мы сравниваем два предыдущих диапазона, выделите диапазон А1:С7, начиная с ячейки А1. Это гарантирует, что ячейка А1 будет активной ячейкой выделения. Выделив диапазон, выберите команду Формат → Условное форматирование (Format → Conditional Formatting). Выберите пункт Формула (Formula Is) и введите следующую формулу: =NOT(A1=A9), в русской версии Excel =НЕ(А1=А9). Щелкните кнопку Формат (Format) (рис. 6.2) и выберите форматирование, которым будут выделены отличающиеся данные.

Рис. 6.2. Диалоговое окно условного форматирования

Щелкните на кнопке ОК, и все различия будут отформатированы согласно выбранному вами формату. Если вы внесете какие-либо изменения в данные, то при одинаковом содержимом ячеек в обеих таблицах будет восстановлено обычное форматирование.

Ссылка на основную публикацию
Adblock detector