Добрый день, друзья и гости моего канала. Сегодня мы разберём с вами задачу по выделению повторяющихся значений в Google.Таблицах. Всего будет рассмотрено 3 варианта:
- 1-й вариант выделения повторяющихся значений через условное форматирование;
- 2-й вариант с использованием Apps Script;
- 3-й вариант с использованием дополнительных полей + условное форматирование.
Для тех кому лень читать, или в процессе статьи будут вопросы - вы можете посмотреть данное обучающее видео:
Выделение повторяющихся значений через условное форматирование
Алгоритм выделения повторных значений достаточно прост
1. Выделяем диапазон, для которого необходимо применить условное форматирование:
2. Применяем к выбранному диапазону условное форматирование:
Формат -> Условное форматирование
3. В появившемся справа блоке "Правила условного форматирования" создаём новое правило:
Правила условного работают в порядке приоритезации, при выполнении нескольких правил итоговым будет то, что выше
4. Настраиваем правило условного форматирования
Еще раз проверьте диапазон к которому будут применяться правила, если вам надо применить правило только к одной колонке до конца, вы можете указать диапазон A2:A (при условии, что в ячейке A1 расположена шапка таблицы).
5. Формулы Гугл.Таблиц для выделения повторяющихся значений:
=COUNTIF(A:A;A1>1)
в русском варианте
=СЧЁТЕСЛИ(A:A;A1>1)
в примере выше формула будет проверять значения по колонкам, повторы в рамках колонки A, B и т.д. в выбранном диапазоне.
Для проверки всех ячеек разом из диапазона, необходимо формулу чуть изменить:
=COUNTIF(A:H;A1>1)
в русском варианте:
=СЧЁТЕСЛИ(A:H;A1>1)
В примере выше данные будут проверяться разом в диапазоне данных начиная с колонки A до колонки H.
В Интернете также есть более старая формула, которая также очень хорошо работает, чаще всего я использую именно её:
=AND(NOT(ISBLANK(A1)); COUNTIF($A$1:$A; "=" & A1) > 1)
в русском варианте:
=И(НЕ(ЕПУСТО(A1)); СЧЁТЕСЛИ($A$1:$A; "=" & A1) > 1)
Здесь опять же, по аналогии формулу можно адаптировать под свои потребности, например если требуется проверять и подсвечивать повторяющиеся значения только по колонке F:
=AND(NOT(ISBLANK(F1)); COUNTIF($F$1:$F; "=" & F1) > 1)
в русском варианте:
=И(НЕ(ЕПУСТО(F1)); СЧЁТЕСЛИ($F$1:$F; "=" & F1) > 1)
либо в диапазоне от B до G:
=AND(NOT(ISBLANK(B1)); COUNTIF($B$1:$G; "=" & B1) > 1)
в русском варианте:
=И(НЕ(ЕПУСТО(B1)); СЧЁТЕСЛИ($B$1:$G; "=" & B1) > 1)
Условное форматирование имеет ограничения работы в рамках одного листа, в случае если вы будете ссылаться на диапазон данных из другого листа (или другой Гугл.Таблицы) у вас высветится уведомление об ошибки:
Далее мы рассмотрим два варианта решения как обойти это ограничение, каждый вариант по своему хорош.
Повторяющееся значения в Google.Таблицах на разных листах
В этом варианте мы будем использовать Apps Script. Для этого необходимо зайти в Расширения -> Apps Script.
В поле для ввода кода Apps Script вместо стартовой заготовки вставляем следующий код:
function onEdit(e) {
// Получаем активную таблицу
var activeSpreadsheet = e.source;
// Получаем лист, в котором произошли изменения
var editedSheet = e.range.getSheet();
// Проверяем, что изменения произошли на "1 Вариант II" или "2 Вариант II"
if (editedSheet.getName() == "1 Вариант II" || editedSheet.getName() == "2 Вариант II") {
// Получаем данные из колонки A на "2 Вариант II"
var sheet2 = activeSpreadsheet.getSheetByName("2 Вариант II");
var columnADataSheet3 = sheet2.getRange("A:A").getValues().flat();
// Получаем данные из колонки A на "1 Вариант II"
var sheet1 = activeSpreadsheet.getSheetByName("1 Вариант II");
var range = sheet1.getRange("A:A");
var columnADataSheet1 = range.getValues();
// Итерируем по значениям колонки A на "1 Вариант II"
for (var i = 0; i < columnADataSheet1.length; i++) {
// Получаем значение ячейки
var enteredValue = columnADataSheet1[i][0];
// Получаем объект ячейки
var cell = range.getCell(i + 1, 1);
// Проверяем, что значение не пустое
if (enteredValue !== "") {
// Проверяем, есть ли такое значение в колонке A "2 Вариант II"
if (columnADataSheet3.indexOf(enteredValue) !== -1) {
// Если значение найдено, устанавливаем цвет фона ячейки
cell.setBackground("#b7e1cd"); //можете поменять цвет бекграунда на свой
//cell.setFontColor("#ffa500"); //измененице цвета шрифта
} else {
// Если значение не найдено, сбрасываем цвет фона ячейки
cell.setBackground(null);
//cell.setFontColor(null); //обнуление цвета шрифта
}
}else{
// Если значение стало пустым, сбрасываем цвет фона ячейки
cell.setBackground(null);
//cell.setFontColor(null); //обнуление цвета шрифта
}
}
}
}
Комментарии по коду:
1. Данный код работает с листом под названием "1 Вариант II" и "2 Вариант II", где на листе "2 Вариант II" указаны значения, которые будут проверяться на совпадения с данными, которые будут указаны на листе "1 Вариант II". В ваших таблицах названия листов могут быть другие, соответственно в формуле выше вам необходимо будет поставить ваши названия листов (выделено жёлтым в коде выше). Вы также можете изменить цвет бэкграунда или шрифта (цвет шрифта закомментирован через "//", чтобы он работал уберите комментарии).
2. Данный код работает с колонками A:A и на листе с проверочными данными и на листе, где необходимо делать подсветку повторяющихся значений.
В вашем случае колонка (или колноки) могут быть другие. Для изменения колонки с проверочными данными вам необходим изменить диапазон "A:A" в части кода, подчеркнутого фиолетовым на скриншоте ниже
Для изменения колонки с данными, дубли которых необходимо будет подсвечивать, вам необходимо изменить диапазон "A:A" в части кода, подчеркнутого зелёным на скриншоте ниже.
3. Данный код не претендует на звание лучшего, однако, он рабочий. При первом запуске потребуется разрешение на применение скрипта Apps Script. Скрипт срабатывает каждый раз при изменении данных в таблице (на лету). После внесения изменения в код не забывайте его сохранить:
При желании код можно доработать, чтобы повторяющиеся значения работали не только на разных листах, но и в разных Google.Таблицах.
В моей статье "Зависимые выпадающие списки в Google таблицах" подробно рассматривается как предоставить разрешение к Apps Script.
Вопросы по скрипту можно задать здесь, в Дзене (в Дзене могу отвечать не очень оперативно) или в ВК: https://vk.com/aleks_ku или в Телеграм https://t.me/Aleks32Ku
Повторяющееся значения в Google.Таблицах на разных листах еще один вариант
Для тех, кто не любит связываться со скриптами есть еще один вариант заставить подсвечиваться повторяющиеся значения между разными листами с использованием дополнительного поля.
В соседнюю колонку вставляется формула, и протягивается вниз:
=IF(A2="";"";COUNTIF('2 Вариант III'!$A$2:$A;A2))
в русском варианте:
=ЕСЛИ(A2="";"";СЧЁТЕСЛИ('2 Вариант III'!$A$2:$A;A2))
Формула выше построчно проверяет ваши данные из колонки A и подсчитывает их количество на листе "2 Вариант III" в диапазоне от $A$2 до конца колонки A. В вашем случае лист будет называться по другому, в формуле необходимо будет вписать название вашего листа и ваш диапазон. В случае обнаружения совпадений возвращает количество данных совпадений. Нас интересует любое не нулевое значение.
Выбираем колонку, к которой необходимо будет применить условное форматирование и создаем новое правило (см. вариант 1, там рассказано как добавить правило). В поле "Ваша формула" вставляем следующий вариант:
=AND(NOT(ISBLANK(A1));B1>=1)
в русском варианте:
=И(НЕ(ЕПУСТО(A1));B1>=1)
В видео указан старый вариант формулы, который не учитывал момент, что число повторов в проверочных данных может быть более 1. Формула выше - более устойчива к вносимых данным.
=B1=1 - старый вариант формулы из видео, не совсем верный.
Если вам необходим производить подсветку например в колонке H, а дополнительная таблица у вас на колонке O, то формула будет выглядеть следующим образом:
=AND(NOT(ISBLANK(H1));O1>=1)
в русском варианте:
=И(НЕ(ЕПУСТО(H1));O1>=1)
Из особенностей: формулу надо вручную протягивать по всем данным, если формулу не протянуть - условное форматирование к данным не применится. Как я уже говорил в видео можно повесить обработчик Apps Script, который будет на лету добавлять формулу в проверочную колонку, в случае наличия данных в проверяемой колонке\диапазоне.
Все разобранные выше примеры доступны по ссылке https://docs.google.com/spreadsheets/d/1XC146hgbPyAINbQKo_aRcyBch9dxm4VQ7Eix7AbCD04/edit#gid=781545522
Буду рад, если материал оказался для вас полезным. Пишите ваши комментарии. Подписывайтесь на канал, чтобы не потеряться.