Функции и формулы

Как сравнить два листа в Google Таблицах

Вы когда-нибудь пробовали сравнивать содержимое двух разных Google Таблиц, чтобы найти расхождения? Если это небольшие листы с несколькими строками и столбцами данных, это может быть не так сложно. Однако с большими объемами данных — совсем другое дело!

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

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

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

Образец 1:

Образец 2:

Предположим, что Образец 1 находится на листе с именем Лист 1, а Образец 2 находится на листе с именем Лист 2.

Как получить доступ к ячейкам с другого листа в Google Таблицах

Если вы хотите получить доступ к ячейкам с другого листа, вы можете использовать ссылки на ячейки с префиксом имени листа и восклицательным знаком (‘!’).

Поэтому, если вы хотите получить доступ к ячейке A2 листа 1 (SHEET 1), вы можете использовать ссылку на ячейку «Sheet1! A2».

Точно так же для абсолютной ссылки на ячейку вы можете использовать ссылку «Sheet1! $ A $ 2».

Как сравнить два листа в Google Таблицах, чтобы найти несоответствия в ячейках

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

Вы можете использовать следующую формулу:

=IF(Sheet1!A1<>Sheet2!A1,Sheet1!A1&" | "&Sheet2!A1,"")

Функция ЕСЛИ (IF) требует трех параметров:

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

Здесь мы указали условие как: Sheet1! A1 <> Sheet2! A1, что означает «ячейка A1 из ЛИСТ1 не равна ячейке A1 из ЛИСТ2».

Если условие ИСТИНА, мы указали, что функция должна возвращать значение в ЛИСТ1, ячейке A1, за которым следует символ вертикальной черты (‘|’), за которым следует значение в ЛИСТ2, ячейке A1 или «Sheet1! A1 &» | «& Sheet2! A1».

Если условие — ЛОЖЬ, функция должна просто отображать пустую ячейку («»).

Когда вы вставляете приведенную выше формулу во все ячейки третьего листа (назовем ее «Sheet3»), она точно отображает, какие ячейки отличаются, а также то, как они отличаются, как показано ниже:

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

  • Создайте третий лист (щелкнув значок «+» в нижней части окна. Вы должны увидеть новую вкладку с именем «Sheet3».
  • В первой ячейке этого листа (ячейка A1) введите формулу: = IF (Sheet1! A1 <> Sheet2! A1, Sheet1! A1 & »|« & Sheet2! A1, »»)
  • Скопируйте формулу, нажав CTRL + C на клавиатуре.
  • Выделите все ячейки Sheet3, нажав CTRL + A на клавиатуре или нажав кнопку «Выбрать все» в верхнем левом углу листа.
  • Нажмите CTRL + V на клавиатуре, чтобы вставить формулу во все выбранные ячейки.

Теперь вы должны обнаружить, что все ячейки, одинаковые в Sheet1 и Sheet2, пусты. Однако ячейки, содержимое которых различается, должны отображать значения этой ячейки с обоих листов, разделенные знаком ‘|’ персонаж.

Как сравнить два листа на предмет точного совпадения строк

Очень распространенное применение Google Таблиц — сравнение листов по строкам, чтобы определить, какие строки (или записи) совпадают, а какие нет.

В нашем примере давайте попробуем сравнить Sheet1 и Sheet2 построчно. Мы отобразим результаты на третьем листе с именем Sheet3. Если строка совпадает на обоих листах, мы должны увидеть слово «Соответствие», отображаемое в соответствующей строке Sheet3. Если нет, то мы должны увидеть слово «Not Matching»  («Не совпадает») в соответствующей строке.

Для этого нам нужно будет использовать формулу, которая включает функцию ЕСЛИ (IF) с вложенным И. Функция И принимает в качестве параметров два или более условий. Она возвращает значение ИСТИНА, если все условия ИСТИНА, и ЛОЖЬ в противном случае.

Если в обеих ваших таблицах Google есть по 2 столбца для сравнения, вам необходимо выполнить следующие действия:

  • Создайте третий лист (щелкнув значок «+» в нижней части окна.
  • В ячейке A2 этого листа введите формулу: = IF (AND (Sheet1! A2 = Sheet2! A2, Sheet1! B2 = Sheet2! B2), «Matching», «Not Matching»)
  • Скопируйте формулу в оставшуюся часть столбца, перетащив маркер заполнения ячейки.

Теперь вы должны увидеть слово «Соответствие» там, где соответствующие строки двух листов совпадают, и слово «Не совпадает» в противном случае.

Обратите внимание, что даже если имена в последней строке наших образцов совпадают, поскольку соответствующие элементы в той же строке не совпадают, функция вернула результат «Not Matching».

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

=IF(AND(Sheet1!A2=Sheet2!A2,Sheet1!B2=Sheet2!B2, Sheet1!C2=Sheet2!C2),"Matching","Not Matching")

И так далее.

Как сравнить два листа и выделить совпадающие строки

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

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

Однако условное форматирование в Google Таблицах не позволяет ссылаться на ячейки с другого листа.

Один из способов обойти это — использовать функцию INDIRECT для косвенного доступа к другому листу. Это станет яснее, когда мы продемонстрируем метод по шагам.

Вот как можно использовать условное форматирование для сравнения двух листов и выделения совпадающих строк на одном из них:

  • Щелкните меню Формат в строке меню.
  • Выберите «Условное форматирование».
  • В правой части окна откроется боковая панель «Правила условного формата».
  • В поле ввода под «Применить к диапазону» введите диапазон ячеек, к которому вы хотите применить форматирование. В нашем примере, если вы хотите применить форматирование к Sheet2, введите Sheet2! A2: A10
  • В разделе «Правила форматирования» в разделе «Форматировать ячейки, если» щелкните стрелку раскрывающегося списка.
  • В раскрывающемся меню выберите «Пользовательская формула».
  • Вы увидите поле ввода под раскрывающимся списком. Введите здесь свою собственную формулу: “=AND(A2=INDIRECT(“Sheet1!A2:A”),B2=INDIRECT(“Sheet1!B2:B”))”.
  • В разделе «Стиль форматирования» нажмите кнопку «Цвет заливки».
  • Выберите цвет, который вы хотите использовать, для выделения совпадающих строк. Мы выбрали «светло-желтый 2».
  • Нажмите кнопку «Готово», чтобы условное форматирование сделало свою работу.

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

Используемая здесь формула сравнивает два столбца каждой строки в Sheet1 и Sheet2. Он использует функцию INDIRECT для извлечения ссылки на ячейки в столбцах A и B листа Sheet1. Затем формула проверяет, совпадают ли ячейки, соответствующие обоим столбцам в каждой строке. Если да, то строка выделяется.

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

“=AND(A2=INDIRECT("Sheet1!A2:A"),B2=INDIRECT("Sheet1!B2:B"), C2=INDIRECT("Sheet1!C2:C"))”

И так далее.

Примечание. Чтобы выделить только несовпадающие строки, вы можете заменить в формуле символы «=» на «<>».

Как сравнить два листа, чтобы найти повторяющиеся данные

Первые три метода в основном находили строки на обоих листах, которые точно совпадали и находились в одинаковом порядке. Однако на практике строки на обоих листах не всегда располагаются одинаково. Итак, давайте посмотрим на ситуацию, когда мы хотим сравнить два листа и выяснить, какие строки Sheet1 повторяются на Sheet2, независимо от порядка.

Другими словами, давайте использовать условное форматирование, чтобы найти повторяющиеся данные в Sheet2. Вот шаги:

  • Выберите пустой столбец сразу после крайнего правого столбца Sheet2. Допустим, в нашем примере мы использовали столбец C.
  • Во второй строке этого столбца вставьте формулу:=IFERROR(CELL(“address”,INDEX(Sheet1!$A$1:$A$9,MATCH(A2,Sheet1!$A$1:$A$9,0),1)),FALSE).. Эта формула вернет адрес ячейки на листе Sheet1, столбец A, который соответствует содержимому ячейки A2 на текущем листе. Если соответствующая ячейка не существует на Листе 1, формула вернет ЛОЖЬ.
  • Скопируйте эту формулу в остальные строки столбца, перетащив маркер заполнения. Вот что вы должны увидеть:
  • Теперь мы можем приступить к применению условного форматирования. Повторите шаги для условного форматирования, описанные в предыдущем разделе (шаги с 1 по 10), но замените формулу в шаге 7 на: “=IF(NOT(C2=FALSE),IF(OFFSET(INDIRECT(C2),0,1)=B2,TRUE,FALSE),FALSE)”

 

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

Примечание . Если вы хотите скрыть столбец, содержащий совпадающие адреса, вы можете просто щелкнуть столбец правой кнопкой мыши и выбрать «Скрыть столбцы» в появившемся контекстном меню.

Объяснение формулы

Формула в шаге 4 выглядит немного сложной, но вы сможете понять, если мы разберем ее для вас. Давайте проанализируем формулу слой за слоем, начиная с внутреннего слоя и двигаясь наружу:

OFFSET(INDIRECT(C2),0,1)

Эта функция возвращает значение в столбце B листа Sheet1, соответствующее адресу, полученному в ячейке C2. Помните, что C2 содержит адрес ячейки с именем в Sheet1, которое соответствует имени в ячейке A2. Таким образом, эта формула просто вернет значение элемента, соответствующее совпадающему имени.

IF(OFFSET(INDIRECT(C2),0,1)=B2,TRUE,FALSE)

Функция ЕСЛИ теперь проверяет, соответствует ли это значение Sheet1, Column B соответствующему значению в Sheet2, Column B. Если это так, то формула возвращает ИСТИНА, в противном случае она возвращает ЛОЖЬ.

IF(NOT(C2=FALSE),IF(OFFSET(INDIRECT(C2),0,1)=B2,TRUE,FALSE),FALSE)

Эта формула теперь проверяет, содержит ли C2 адрес ячейки или значение FALSE. Если он содержит адрес ячейки, это означает, что соответствующее имя было найдено в Sheet1. В этом случае формула возвращает значение ИСТИНА или ЛОЖЬ в зависимости от результата внутренней вложенной функции ЕСЛИ. Если C2 содержит значение FALSE, формула также возвращает FALSE.

Другими словами, вся формула сначала проверяет, есть ли повторяющееся значение Name в Sheet1. В противном случае возвращается ЛОЖЬ. Если дублирующееся имя действительно существует, он проверяет, совпадает ли соответствующее значение элемента. Если это так, возвращается ИСТИНА, в противном случае — ЛОЖЬ.

Примечание. Если на ваших листах более двух столбцов, вы можете расширить формулу, включив функцию И во вложенную функцию ЕСЛИ. Например, если у вас есть 3 столбца на каждом листе, вы можете изменить формулу на:

IF(NOT(D2=FALSE),IF(OFFSET(AND(INDIRECT(D2),0,1)=B2, INDIRECT(D2),0,2)=C2),TRUE,FALSE),FALSE)

Здесь мы предполагаем, что адреса совпадающих ячеек находятся в столбце D Листа 2, а не в столбце C.

Как сравнить два листа, чтобы найти недостающие данные

Наконец, что, если вы хотите выделить недостающие строки вместо повторяющихся?

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

Другими словами, ваша формула должна выглядеть так:

“=NOT(IF(NOT(C2=FALSE),IF(OFFSET(INDIRECT(C2),0,1)=B2,TRUE,FALSE),FALSE))”

Вот результат, который вы получите:

Выводы

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

Вас могут заинтересовать следующие статьи: