Как использовать условное форматирование на основе данных с другого листа в Google Таблицах?
Использовать условное форматирование — это полезная функция в Google Sheets, которая позволяет автоматически выделять ячейки в соответствии с заданными критериями, упрощая анализ и визуализацию данных. Иногда вместо выделения ячеек по значениям в том же листе требуется основывать правила форматирования на справочном списке или критериях, хранящихся на другом листе. Например, вы можете захотеть выделить ячейки на одном листе, которые также присутствуют в списке, поддерживаемом на другом листе, как показано на рисунке ниже. Такая задача часто возникает при работе с перекрёстными данными, например, при сравнении текущих продаж со списком основных товаров или при проверке дубликатов относительно другого Исходный диапазон. Однако настройка такого Использовать условное форматирование в Google Sheets — особенно при обращении к данным на других листах — может вызвать затруднения, если вы делаете это впервые. Приведённое ниже руководство пошагово покажет вам простой способ выполнить эту задачу.
Инструкция по выделению ячеек на основе списка с другого листа в Google Sheets
Руководство по выделению ячеек на основе списка с другого листа в Google Sheets
Этот метод позволяет создать правило условного форматирования, чтобы выделять ячейки на текущем листе, если они присутствуют в указанном списке с другого листа. Такое межлистовое условное форматирование особенно полезно для динамического мониторинга данных и поддержания согласованности между связанными наборами данных.
Чтобы завершить этот процесс, выполните следующие подробные шаги:
1. Откройте нужный лист, затем в верхней части экрана нажмите меню Формат и выберите пункт Использовать условное форматирование. Справа откроется панель правил Условный формат.
2. На панели правил условного формата выполните следующие действия:
(1.) Нажмите кнопку рядом с полем «Применить к диапазону» и выберите диапазон ячеек, которые нужно выделить. Например, чтобы применить формат ко всем значениям в столбце C, начиная со строки 2, выделите диапазон 
C2:C . Выбор подходящего диапазона гарантирует, что форматированию будут подвергнуты только нужные ячейки.
(2.) В раскрывающемся меню выберите пункт Пользовательская формула, затем в появившемся поле введите следующую формулу: =MATCH(C2,INDIRECT(«data list!A2:A»),0). Эта формула проверяет, совпадает ли каждая ячейка в столбце C с любым значением из диапазона A2:A на листе «data list».
(3.) В разделе Стиль форматирования выберите желаемое оформление, например заливку ячейки определённым цветом или изменение стиля шрифта. Вы сразу увидите предварительный просмотр стиля прямо в таблице — ещё до его применения.
Примечание: В приведённой выше формуле C2 указывает на первую ячейку вашего диапазона выделения (при необходимости скорректируйте, если ваши данные начинаются с другой строки или столбца), а data list!A2:A ссылается на имя листа («data list») и соответствующий диапазон (A2:A), где хранится ваш список с другого листа. Убедитесь, что ссылка на ячейку в формуле соответствует верхней левой ячейке вашего диапазона выделения, иначе форматирование может примениться некорректно. Если диапазон вашего списка отличается, не забудьте обновить его в формуле (например, «data list!B2:B»).
3. После настройки правила ячейки, соответствующие условиям, в выбранном диапазоне будут немедленно выделены на основе списка с другого листа. Проверьте предварительный просмотр и нажмите кнопку Готово в нижней части панели правил Условный формат, чтобы применить и сохранить форматирование.
Советы и устранение неполадок:
- Внимательно проверьте формулу на опечатки, особенно в именах листов и ссылках на диапазоны — неверные ссылки часто становятся причиной сбоя применения правил.
- Если ваш список данных содержит пустые ячейки, функция
ПОИСКПОЗвернёт ошибку#Н/Ддля значений без совпадений — это ожидаемое поведение, которое не влияет на выделение совпадающих элементов. - Когда вы Копировать форматирование на новый лист или изменяете диапазоны, обязательно обновите соответствующие ссылки на ячейки в своей пользовательской формуле.
- Форматирование обновляется автоматически, если вы впоследствии добавите или удалите элементы из справочного списка.
- Указанные в вашей формуле лист и диапазон существуют и написаны корректно.
- Первая ячейка в вашей формуле совпадает с первой ячейкой выбранного вами диапазона.
- Все необходимые разрешения для доступа между листами уже предоставлены — этот метод работает только в пределах одного многостраничного файла Google Таблиц, а не между разными файлами.
В качестве альтернативы, если структура ваших данных или требования более сложны — например, нужно сравнивать несколько столбцов, допускать частичные совпадения или выполнять расширенный поиск — используйте вспомогательные столбцы с формулами СЧЁТЕСЛИ или ВПР, либо примените Google Apps Script (пользовательский код на JavaScript) для реализации гибких решений. Также можно использовать условное форматирование.
Таким образом, настройка Использовать условное форматирование на основе другого листа чрезвычайно эффективна для проверки списков, отслеживания дубликатов и различных межлистовых проверок данных — всё это в рамках Google Sheets. Всегда проверяйте введённые формулы, диапазоны ссылок и правила форматирования, чтобы обеспечить корректную и точную работу.
Раскройте магию Excel с помощью KUTOOLS AI
- Интеллектуальное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это доступно через простые команды.
- Пользовательские формулы: создавайте индивидуальные формулы для оптимизации рабочих процессов.
- Программирование на VBA: Пишите и внедряйте код VBA легко и без усилий.
- Анализ формул: Легко разбирайтесь даже в самых сложных формулах.
- Перевод текста: Ломайте языковые барьеры прямо в ваших таблицах.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек