Как сравнить два листа в google таблицах
Перейти к содержимому

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

  • автор:

Сравните значения на двух листах Google в одном документе.

У меня есть таблица Google, содержащая два листа. Каждый лист содержит столбец URL-адресов, за которым следуют 6 столбцов данных. Я хотел бы выделить на втором листе, если значение отличается, в идеале стилизовать конкретную ячейку с красным или зеленым фоном в зависимости от значения.

В этом случае я бы хотел, чтобы значения 55, 68 и 90 были выделены, поскольку они отличаются от листа 1.

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

2 ответа

Условное форматирование

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

Где-нибудь на втором листе (например, в ячейке AB2). Затем используйте условное форматирование для диапазона B2: H2 с правилом «значение не равно . = AB2».

(Формула условного форматирования вводится так, как должна читаться в верхнем левом углу форматируемого диапазона).

Apps Script

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

Вышеупомянутый подход с условным форматированием является предпочтительным.

Обзор

Используйте INDIRECT в настраиваемой формуле правила условного форматирования, как показано ниже:

Объяснение

Встроенная функция условного форматирования Google Таблиц не позволяет использовать ссылки на разные листы напрямую, но позволяет использовать КОСВЕННО.

Вышеупомянутая формула включает в себя два «хака». Первое было упомянуто в обзоре, использование КОСВЕННО. Второй «прием» — использовать нотацию R1C1 вместе с ROW () и COLUMN (), чтобы сделать правило условного форматирования очень гибким по сравнению с использованием жестко закодированной ссылки и вести себя как «нормальная» ссылка.

Как искать и сравнивать данные с разных листов в Гугл Таблицах?

Привет. Помогите, пожалуйста, решить задачу в Гугл Таблицах?

Мне нужно сделать следующий поиск:

(первая строчка — заголовки)

Из листа ОПЛАТЫ взять значение D2 (почта) и H2 (стоимость).

Выполнить поиск значения D2 по столбцу D листа ВСТРЕЧИ.

Если есть совпадение, то проверить, совпадает ли значение ячейки H2 листа ОПЛАТЫ со значением ячейки K2 листа ВСТРЕЧИ (тут нюанс — в столбце D есть одинаковые значения, напротив которых в столбце H есть разные значения. То есть надо найти все значения из столбца D и их проверять отдельно?)

Если есть совпадение, то записать значение ячейки F2 листа ВСТРЕЧИ в ячейку C2 листа ОПЛАТЫ.

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

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

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

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

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

Образец 2:

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

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

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

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

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

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

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

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

Функция ЕСЛИ (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 столбца, ваша формула будет выглядеть так:

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

Если вы предпочитаете выделять совпадающие строки на одном из двух листов вместо отображения результатов на отдельном листе, вы можете использовать функцию условного форматирования в 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 столбца, ваша формула будет такой:

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

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

Первые три метода в основном находили строки на обоих листах, которые точно совпадали и находились в одинаковом порядке. Однако на практике строки на обоих листах не всегда располагаются одинаково. Итак, давайте посмотрим на ситуацию, когда мы хотим сравнить два листа и выяснить, какие строки 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 выглядит немного сложной, но вы сможете понять, если мы разберем ее для вас. Давайте проанализируем формулу слой за слоем, начиная с внутреннего слоя и двигаясь наружу:

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

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

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

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

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

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

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

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

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

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

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

Выводы

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

Как сравнить столбцы в Google Sheets

Google Sheets — это мощное и простое в освоении облачное приложение для работы с электронными таблицами. Таблицы конкурируют с Microsoft Excel на рынке электронных таблиц, у них нет такой же широты и глубины функций.

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

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

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

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

Столбцы сравнения Google Таблиц

Как сравнить две колонки в Google Sheets2

Сравните два столбца в Google Таблицах

Один из простых способов сравнения столбцов в Таблицах — использовать формулу. Допустим, у нас есть два столбца данных, столбец A и столбец B. Если мы хотим сравнить столбцы и отметить любые различия, мы можем использовать формулу.

Формула ЕСЛИ — мощный инструмент в Таблицах (а также в Excel). В операторе IF есть три аргумента.

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

  1. Откройте свой лист на странице, которую вы хотите сравнить.
  2. С данными в столбцах A и B выделите ячейку C1.
  3. Вставьте ‘ = if ( A1 = B1 , «» , «Mismatch» ) »в ячейку C1. Логика такова: если A1 и B1 одинаковы (т. Е. A1 = B1), формула возвращает пустую строку, а если они не совпадают (A1 не равно B1), формула возвращает «Несоответствие».
  4. Кликните левой кнопкой мыши в правом нижнем углу ячейки C1 и перетащите вниз. Это копирует формулу из C1 во все ячейки столбца C.

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

Сравнение данных из нескольких столбцов

Сравнение данных между двумя столбцами — это хорошо и полезно … но что, если у вас есть несколько столбцов данных и вам нужно провести сравнение? Что ж, Sheets может справиться и с этим, используя функцию ARRAYFORMULA.

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

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

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

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

Если мы хотим проверить, совпадают ли цены за единицу, указанные Джейн и Бобом, мы можем использовать для этого ARRAYFORMULA. Мы хотим сообщать о любых различиях и распечатывать их, начиная с ячейки I3, поэтому в I3 мы вводим эту формулу:

= ARRAYFORMULA (СЧЁТЕСЛИ (ЕСЛИ (C12: C336 <D12: D336, «Да», «Нет»), «Да»))

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

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

Использование Power Tools для сравнения столбцов

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

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

Раньше они были бесплатными на неопределенный срок, но теперь Power Tools требует подписки по окончании бесплатного пробного периода. Я бы сказал, что Power Tools стоит своих денег для активных пользователей электронных таблиц в 29,95 долларов в год или 89,95 долларов за пожизненную подписку.

Таблицы Дополнения Электроинструменты

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

  1. После того, как Power Tools будет добавлен в таблицу Google, перейдите к Ons Add- ниспадающее меню
  2. Выберите Электроинструменты
  3. Затем выберите Пуск
  4. Кликните пункт меню «Данные», затем выберите «Сравнить два листа».
  5. Введите диапазоны столбцов, которые вы хотите сравнить. Обратите внимание, что вы можете сравнивать несколько столбцов одновременно и даже сравнивать на разных листах!
  6. Выберите, хотите ли вы найти уникальные значения или повторяющиеся значения.
  7. Выберите, как вы хотите, чтобы Power Tools отображал результаты сравнения. Вы можете выбрать цвет в повторяющихся или уникальных ячейках, для перемещения или копирования данных в новые столбцы и другие варианты.

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

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

    .
  1. Вставьте один набор текста или значений в левую панель, а другой столбец или текст — в правую.
  2. Выберите «Найди отличия!»
  3. Сайт сравнит две панели и выделит любые различия.

Diffchecker полезен, если вы пытаетесь различать столбцы и нуждаетесь только в результатах.

А как насчет того, чтобы с помощью Excel сравнивать столбцы с помощью этого инструмента? Ну конечно можно!

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

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

Метод 1 для сравнения столбцов на наличие дубликатов в Excel:

  1. Выделите два столбца, которые хотите проверить.
  2. Выберите «Условное форматирование» на ленте «Главная».
  3. Выберите «Выделить правила для ячеек и повторяющиеся значения».
  4. Выберите формат для отображения и нажмите ОК.

Метод 2 для сравнения столбцов на предмет различий в Excel:

  1. Выделите ячейку 1 в столбце C.
  2. Вставьте ‘= ЕСЛИ (СЧЁТЕСЛИ ($ A: $ A, $ B2) = 0, «Нет совпадений в A», «») »в строку формул.
  3. Вы должны увидеть «Нет совпадений в A» в столбце C, если эти два столбца различаются.

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

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

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

Ваш адрес email не будет опубликован.