Содержание:
Иногда мне хотелось бы, чтобы в Google Таблицах было больше функций, таких как например в MS Excel. Одна из таких функций, которые недоступны в Google Таблицах, — это возможность фильтрации по цвету.
Например, предположим, что у меня есть набор данных, показанный ниже, и я хочу отфильтровать все строки, в которых запись была окрашена в определенный цвет.
К сожалению, в Google Таблицах нет прямого способа фильтрации по цвету . Но есть несколько косвенных способов…
В этом руководстве я покажу вам несколько методов, которые вы можете использовать для фильтрации строк на основе цвета в качестве критериев в Google Таблицах.
Использование скрипта Google Apps для фильтрации по цвету
Предположим, у вас есть набор данных, как показано ниже, и вы хотите отфильтровать все строки, в которых ячейки были раскрашены.
А поскольку цвет сам по себе не является значением, мы не можем фильтровать по нему по умолчанию.
Используя скрипт Google Apps, вы можете преобразовать цвет в шестнадцатеричный код (который является кодом, назначенным каждому цвету). Получив HEX-код, вы можете фильтровать данные на его основе.
Ниже приведен сценарий, который создаст функцию в вашем документе Google Таблиц, которую вы можете использовать для получения HEX-кода цвета, примененного к ячейкам.
function GetCellColorCode(input)
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var cell = ss.getRange(input);
var result = cell.getBackground();
return result
}
Вышеупомянутая функция принимает диапазон в качестве входных данных и возвращает шестнадцатеричный код цвета фона входной ячейки / диапазона.
После добавления этого кода в редактор скриптов Google Apps вы сможете использовать функцию GetCellColorCode для получения шестнадцатеричного кода цвета ячейки в отдельной ячейке.
О том, как добавить код в редактор скриптов Google, рассказывается далее в этом разделе. Позвольте мне сначала показать вам, как работает эта формула.
Введите следующую формулу в соседний столбец набора данных
=GetCellColorCode("A"&ROW())
Поскольку я не хотел, чтобы это выполнялось вручную, я использовал «A» и ROW () — который возвращает адрес ячейки на основе строки, в которой используется формула. В строке 2 он вернет A2, в строке 3 он вернет A3 и так далее.
После того, как вы введете эту формулу для всех записей, вы получите результат, как показано ниже:
Ячейки без цвета возвращают шестнадцатеричный код #ffffff, а ячейки с цветом возвращают шестнадцатеричный код этого цвета.
Теперь, когда у вас есть шестнадцатеричные коды в отдельном столбце, вы можете легко фильтровать по цвету, фактически фильтруя по значению шестнадцатеричного кода.
Ниже приведены шаги для фильтрации всех строк с желтым цветом в приведенном выше наборе данных:
- Выберите любую ячейку в наборе данных
- Перейдите на вкладку «Данные».
- В появившихся параметрах нажмите «Создать фильтр». Это добавит фильтры ко всем ячейкам заголовка в наборе данных (значок стрелки, направленной вниз, появится справа от каждой ячейки заголовка).
- Щелкните значок фильтра в столбце «Шестнадцатеричный код».
- В параметрах фильтра, которые отображаются в поле, отмените выбор всех шестнадцатеричных кодов цвета и выберите тот, на основе которого вы хотите отфильтровать набор данных.
- Щелкните ОК.
Это отфильтрует строки на основе выбранного цветового шестнадцатеричного кода (как показано ниже).
- Эта функция доступна только для документа Google Sheet, в который вы добавили скрипт. Он не будет доступен ни в одном существующем или новом документе Google Таблиц. Если вы хотите использовать его в других документах, вам также необходимо добавить его туда.
- Эта формула не является динамической. Это означает, что если вы измените цвет любой ячейки, формула не будет обновляться автоматически. Иногда он даже не обновляется, когда вы обновляете лист. Лучше всего использовать формулу в новом столбце и удалить существующий.
Как добавить скрипт в редактор скриптов Google
Ниже приведены шаги по добавлению скрипта для фильтрации по цвету в редакторе скриптов Гугл Таблиц:
- Скопируйте приведенный выше код скрипта
- Перейдите на вкладку «Инструмент».
- Нажмите на редактор сценариев.
- В открывшемся редакторе скриптов вставьте код в окно Code.gs (вы можете удалить там любой ранее существовавший код, если он вам не нужен).
- Нажмите кнопку Сохранить на панели инструментов.
- Закройте редактор сценариев.
Вышеупомянутые шаги добавляют код для создания функции для фильтрации по цвету в этом конкретном документе Google Таблиц.
Теперь вы можете использовать функцию, которую вы создали на любом из рабочих листов в документе.
Примечание. Когда вы создаете настраиваемую функцию, Google Sheets не отображает имя функции на листах, когда вы вводите несколько первых букв имени функции. Вам нужно знать точное имя функции и входные данные, необходимые для ее работы.
Использование надстройки для фильтрации по цвету в Google Таблицах
В Google Таблицах замечательно то, что в них есть множество замечательных надстроек, доступных для использования. Все, что вам нужно сделать, это найти один и начать его использовать. И это не займет больше нескольких кликов.
Поэтому, если вы хотите фильтровать по цвету в Google Таблицах (что не может быть сделано изначально в Google Таблицах), вы можете легко сделать это с помощью простого дополнения.
Дополнение, которое вы можете использовать, называется Сортировать по цвету .
Он не позволяет напрямую фильтровать строки, а вместо этого сортирует их. Когда у вас есть все отсортированные цветные строки, вы можете легко работать с ними и, если вам нужно, даже отфильтровать / скрыть их.
Ниже приведены шаги по добавлению этого дополнения в документ Google Таблиц:
- Откройте документ Google Таблиц, в котором вы хотите изменить регистр текста.
- Перейдите на вкладку «Дополнения».
- Нажмите «Получить надстройки».
- В открывшемся диалоговом окне «Надстройки» найдите «Фильтр по цвету» в поле в правом верхнем углу.
- В отображаемом списке надстроек нажмите синие кнопки для надстройки Sheetgo.
- В появившемся диалоговом окне он может попросить вас подтвердить свою учетную запись, войдя в свой Gmail. Введите учетные данные и нажмите синюю кнопку «Разрешить».
После того, как вы выполните все вышеперечисленные шаги, он добавит надстройку в ваш документ Google Таблиц.
Теперь вы можете получить доступ к параметрам сортировки данных с помощью этого надстройки.
Примечание. Хотя это дополнение великолепно, я лично считаю, что методы сценария проще, поскольку они менее сложны. Однако надстройка позволяет фильтровать по цвету, а затем экспортировать результаты в другой документ Google Таблиц. Так что, если вы этого хотите, попробуйте это дополнение,