☰

Выделите различия между двумя таблицами Excel

tag-icon • tag-icon microsoft-excel worksheet-function conditional-formatting
Выделите различия между двумя таблицами Excel

Данные из электронной таблицы A содержат несколько столбцов, но мне нужно сравнить только:

Column A - order #
Column C - count
Column D - date

В таблице B также много столбцов, но мне нужно сравнить:

Column A - order #
Column B - count
Column C - date

Мне нужно выделить столбец A в строке обеих таблиц, когда данные по строке идентичны только для этих 3 столбцов. В этом примере строки 6 и 14 имеют разные даты, а 7 и 15 имеют разные количества, поэтому они не будут выделены.

нажмите для примера

решение1

Этот метод опирается на некоторые вспомогательные столбцы в вашей первой электронной таблице или на отдельном листе, если вы предпочитаете.

MATCH() вернет номер строки заданного значения в массиве.

INDEX() ищет это значение в другом указанном массиве

Итак, в строке 1 вспомогательного столбца (скажем, столбца X) вы хотите... =MATCH(A1,SheetName!A:A,0) Это вернет строку на другом листе, содержащую номер заказа в ячейке A1.

Затем в следующих 2 столбцах вы хотите =INDEX(SheetName!C:C,$X1) =INDEX(SheetName!D:D,$X1). Это вернет количество и дату с другого листа в этой строке.

Затем, в последнем столбце, который вы хотите =And(Y1=C1,Z1=D1). Это вернет ИСТИНА, если все одинаково.

Затем, если хотите, используйте условное форматирование для выделения важных моментов.

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

Надеюсь, это помогло.

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

решение2

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

=AND(MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A)>0; INDEX(Sheet1!$A:$D;ROW();3)=INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);2); INDEX(Sheet1!$A:$D;ROW();4)=INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);3))

Эта формула предполагает следующее:

  • обе таблицы находятся на двух отдельных листах, называемых Лист1 и Лист2.
  • Лист1 содержит вашу первую таблицу, где столбцы A, C и D сравниваются со столбцами A, B и C второй таблицы Лист2.
  • на листе один в качестве области условного форматирования вы выбираете $A:$A или $A2:$AXX (где XX — достаточно большое число, чтобы вместить все значения) — это имеет значение только для цветов, если приведенное выше уравнение верно

Когда это не будет работать так, как «ожидается»:

  • Если во второй таблице есть несколько одинаковых номеров заказов, будет найдено и сравнено только первое совпадение.
  • При применении к таблице Sheet2 не будет восстановлена ​​правильная окраска, необходимо изменить некоторые ссылочные значения.

Используемые функции:

  • AND()- возвращает true только если все параметры верны
  • MATCH()- находит значение в выбранном диапазоне (столбце в нашем случае)
  • INDEX()- возвращает ссылку на ячейку из матрицы/диапазона, явно указывая номера строки и столбца
  • ROW()- без параметров возвращает текущий номер строки

Теперь давайте рассмотрим некоторые компоненты:

  • INDEX(Sheet1!$A:$A;ROW();1)это возвращает текущую ссылку на сравниваемую ячейку, когда условное форматирование запущено
  • MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A)здесь мы ищем ячейку на втором листе, которая совпадает с нашей текущей ячейкой. Если есть совпадение, возвращаемое значение больше 0, в противном случае выдается предупреждение/ошибка.
  • INDEX(Sheet1!$A:$D;ROW();3)- здесь мы ищем значение третьего столбца первой таблицы (Лист1) (псевдоним столбца C).
  • INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);2)- это извлечение 2-го столбца из таблицы Sheet2. У нас уже была строка, где размещен идентификатор заказа, это уравнение находится в середине этой функции, как определяющее строку.
  • 3-й AND()параметр такой же, как и в предыдущих двух пунктах, меняются только номера столбцов.

Примечание:

  • для нахождения правильной строки я использую только столбцы: Sheet1!$A:$AиSheet2!$A:$A
  • когда мне нужно получить еще один столбец, мне нужно расширить столбец до матрицы/таблицы, последний столбец которой является по крайней мере последним столбцом, на который я буду ссылаться, в случае листа 1 - 4-м, псевдонимом столбца DSheet1!$A:$D

Зная вышеизложенное, вам необходимо уметь изменять уравнение так, чтобы оно работало и в таблице Sheet2.

решение3

Это легко и просто сделать с помощью условного форматирования, как показано ниже.

Настраивать

Для приведенного ниже примера формулы данные находятся в ячейках Лист1!A1:C3 и Лист2!A1:C3.

Формула условного форматирования

Выделите диапазон ячеек наЛист2, добавьте условное форматирование с помощью формулы и введите следующую формулу: =И(Лист1!$A1=$A1,Лист1!$B1=$B1,Лист1!$C1=$C1) ...и выберите цвет, которым вы хотите выделить свои результаты.

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

Что касается ссылок, используемых в формуле условного форматирования, ссылки на ячейки должны быть самыми верхними ячейками соответствующих столбцов данных. Так, если оба столбца номеров заказов начинаются с A1, оставьте его как A1 согласно вышеизложенному. Но если на первой вкладке первый номер заказа, который нужно рассмотреть, находится в строке 4, а на второй вкладке он находится в строке 7, измените формулу на AND(Sheet1!$A4=A7...) и т. д.

Дайте знать, если что-то требует разъяснений!

Связанный контент