Содержание:
Человеку свойственно ошибаться, и все мы это знаем. При работе с большими объемами данных, человеческий глаз не очень способен идентифицировать небольшие различия или сходства между элементами данных.
К счастью, программное обеспечение для обработки данных, такое как Google Таблицы, может улавливать детали, которые может упустить даже самый зоркий человеческий глаз. Таким образом, такие задачи, как сравнение столбцов, поиск различий и выявление сходств, выполняются легко, с молниеносной скоростью и без ошибок.
В этом руководстве мы покажем вам, как вы можете использовать Google Таблицы для сравнения двух столбцов и поиска совпадений и различий .
Для каждой из вышеперечисленных тем мы будем использовать следующий образец данных, состоящий из двух столбцов — ингредиенты для торта и ингредиенты для файлов cookie:
Также обратите внимание, что в этом случае мы сравниваем столбцы на одном листе. Но вы также можете использовать те же методы для сравнения двух столбцов в разных документах Google Sheets. Для этого вам нужно будет использовать функцию IMPORTRANGE, чтобы иметь возможность ссылаться на столбцы на другом листе.
Как сравнить два столбца для точного совпадения строк
Самый простой способ сравнить два столбца — найти точное совпадение строк. Все это требует простого сравнения строк за строкой.
Таблицы Google просто должны видеть, какие строки имеют одинаковые значения, а какие — разные, и отображать результат в третьем пустом столбце. В нашем примере мы просто собираемся создать новый столбец для результатов и отобразить ИСТИНА, если пара элементов в одной строке совпадают, и ЛОЖЬ, если нет.
Формула, которую вы бы использовали для такого сравнения:
= A2 = B2
Приведенная выше формула сравнивает данные в строке 2 обоих столбцов, чтобы проверить, совпадают ли они. Если они это сделают, то вы получите ИСТИННЫЙ результат. В противном случае вы получите ЛОЖНЫЙ результат.
Вот шаги, которые необходимо выполнить:
- Во второй строке столбца C (в ячейке C2) вставьте формулу: = A2 = B2
- Нажмите клавишу возврата.
- Дважды щелкните маркер заполнения, чтобы скопировать ту же формулу в остальные ячейки в столбце C.
Теперь вы должны увидеть результаты каждого сравнения в столбце C.
Сравните две колонки и покажите содержательный текст
Вышеупомянутый метод отлично работает, если вы единственный, кто будет работать с данными.
Но третий человек, просматривающий ваш рабочий лист, может не понять, что именно вы подразумеваете под словами «ИСТИНА» и «ЛОЖЬ».
Поэтому имеет смысл дать более описательный результат в столбце C. Например, вы можете использовать слово «Соответствие» для элементов строки, которые совпадают, и «Несоответствие» для элементов строки, которые не совпадают.
Если вы хотите получить более наглядный результат, вы можете использовать простую формулу ЕСЛИ, чтобы вернуть текст «Соответствие», если значения совпадают, и «Несоответствие», если значения различны.
Для этого, предполагая, что вы работали в ячейке C2 нашего примера, вы должны использовать следующую формулу:
= ЕСЛИ (A2 = B2, «Соответствует», «Не совпадает»)
В приведенной выше формуле используется функция ЕСЛИ для сравнения значений в A2 и B2. Если условие «A2 = B2» истинно, возвращается текст «Соответствие». В противном случае возвращается текст ЛОЖЬ.
Вот шаги, которые необходимо выполнить:
- Во второй строке столбца C (в ячейке C2) вставьте формулу: = ЕСЛИ (A2 = B2, «Соответствует», «Не соответствует»).
- Нажмите клавишу возврата.
- Дважды щелкните маркер заполнения, чтобы скопировать ту же формулу в остальные ячейки в столбце C.
Теперь вы должны увидеть результаты каждого сравнения как «Соответствует» или «Не совпадает» в столбце C.
Сравните два столбца и выделите совпадающие строки
Если вместо отображения результатов в отдельном столбце вы предпочитаете, чтобы строки с совпадающими данными были выделены, вы можете выполнить условное форматирование.
Условное форматирование — отличный метод, позволяющий форматировать ячейки на основе условия.
Вот как можно использовать условное форматирование, чтобы выделить совпадающие строки в Google Таблицах:
- Щелкните меню Формат в строке меню.
- Выберите параметр «Условное форматирование».
- В правой части окна откроется боковая панель «Правила условного формата».
- В поле ввода под «Применить к диапазону» введите диапазон ячеек, который вы хотите сравнить. В нашем примере мы можем ввести A2: B12.
- Затем в разделе «Правила форматирования» в разделе «Форматировать ячейки, если» щелкните стрелку раскрывающегося списка.
- В появившемся раскрывающемся меню выберите «Пользовательская формула».
- Вы увидите поле ввода под раскрывающимся списком. Введите здесь свою формулу: «= $ A2 = $ B2».
- В разделе «Стиль форматирования» нажмите кнопку «Цвет заливки».
- Выберите цвет, который хотите использовать, чтобы выделить совпадающие ячейки / строки. Мы выбрали «светло-васильковый 1».
- Наконец, нажмите кнопку «Готово», чтобы условное форматирование сделало свою работу.
Теперь вы должны увидеть все совпадающие ячейки / строки, выделенные выбранным цветом.
Сравните два столбца, чтобы найти совпадающие данные
До сих пор методы, которые мы обсуждали, в основном определяли совпадение ячеек в одной строке.
Но что, если вы хотите сравнить два столбца и выяснить, какие значения столбца A повторяются в столбце B, независимо от того, в какой строке он находится?
Это имело бы больше смысла для наших выборочных данных, не так ли? Возможно, вы захотите узнать, какие ингредиенты торта также используются для выпечки печенья.
Вот как можно использовать условное форматирование для поиска повторяющихся данных:
- Щелкните меню Формат в строке меню.
- Выберите параметр «Условное форматирование».
- В правой части окна откроется боковая панель «Правила условного формата».
- В поле ввода под «Применить к диапазону» введите диапазон ячеек, к которому вы хотите применить форматирование. В нашем примере мы хотим видеть только основные моменты столбца B. Итак, мы набираем: B2: B12.
- Затем в разделе «Правила форматирования» в разделе «Форматировать ячейки, если» щелкните стрелку раскрывающегося списка.
- В появившемся раскрывающемся меню выберите «Пользовательская формула».
- Вы увидите поле ввода под раскрывающимся списком. Введите здесь свою формулу: = СЧЁТЕСЛИ ($ A $ 2: $ A $ 9, B2)> 0
- В разделе «Стиль форматирования» нажмите кнопку «Цвет заливки» ().
- Выберите цвет, который хотите использовать, чтобы выделить совпадающие ячейки / строки. Мы выбрали «светло-васильковый 1».
- Наконец, нажмите кнопку «Готово», чтобы условное форматирование сделало свою работу.
Теперь вы должны увидеть все ячейки (ингредиенты) столбца B (печенье), которые также присутствуют в столбце A (торт), выделенные выбранным цветом.
Объяснение формулы
В этой задаче мы хотели узнать, какие ингредиенты столбца B также встречаются в столбце A.
Другими словами, мы хотели выделить только те элементы столбца B, которые имеют 1 или более вхождений в ячейках с A2 по A9.
В формуле:
= СЧЁТЕСЛИ ($ A $ 2: $ A $ 9; B2)> 0
Функция СЧЁТЕСЛИ подсчитывает, сколько раз значение в B2 встречается в диапазоне ячеек A2: A9.
Если это число больше 0, это означает, что дубликат значения B2 существует в столбце A. Он может существовать один, два или более раз. В любом случае, пока счетчик больше 0, функция возвращает ИСТИНА.
Когда функция возвращает значение ИСТИНА, наше условное форматирование начинает работать и выделяет ячейку B2.
Это повторяется для всех ячеек, начиная с B2 до B12.
Сравните два столбца, чтобы найти отсутствующие данные
Что же происходит, когда вы хотите сделать обратное? Что делать, если вы хотите найти ингредиенты печенья, которых нет в торте? В этом случае вам нужно будет лишь немного изменить формулу, использованную на шаге 7 предыдущего метода.
Пользовательская формула, используемая на боковой панели правила условного формата, теперь должна иметь следующий вид:
= СЧЁТЕСЛИ ($ A $ 2: $ A $ 9; B2) = 0
Это даст вам результат, как показано ниже:
Но методы, обсуждаемые в этом руководстве, могут быть действительно эффективными при использовании с большими наборами данных, особенно если вы используете их для аналитической обработки.
Мы рекомендуем вам применить и поэкспериментировать с этими методами и надеемся, что вы нашли объяснения полезными.