Суммарные данные по цвету ячеек в таблицах Google
Я видел этот вопрос, и хотя я сталкиваюсь с той же проблемой, ответ на этот вопрос не полезен для моего случая.
У меня есть большие таблицы, которые часто меняются. Таким образом, в соответствии с решением, принятым для этого вопроса, трудно дублировать все заполненные столбцы, и каждый раз, когда я хочу внести изменения в цвета фона ячеек, сложно редактировать еще одну ячейку.
Разве нет способа суммировать данные на основе условия, касающегося цвета фона ячейки в таблицах Google?
Обновить:
Мое представление для галереи скриптов Документов Google было принято, и вы можете установить его, чтобы получить желаемую функциональность.
- Откройте свою таблицу
- В меню выберите Сервис -> Галерея скриптов .
- Поиск суммы по цвету
- Нажмите Установить
- Нажмите кнопку Авторизоваться , если вы доверяете сценарию, а затем Закрыть
- вернуться к таблице
Теперь у вас есть три дополнительные функции, которые вы можете использовать в формуле таблицы:
- sumWhereBackgroundColorIs(color, rangeSpecification)
- sumWhereBackgroundColorIsNot(color, rangeSpecification)
- getBackgroundColor(rangeSpecification)
Пожалуйста, от того, что rangeSpecification параметры требуют кавычек ( » ) вокруг них.
суммирует значения всех ячеек в диапазоне A1: C4, которые имеют белый фон.
Обратите внимание, что если вы не знаете цвет определенной ячейки, вы можете использовать предоставленную getBackgroundColor функцию, чтобы узнать, какой это цвет. Эта функция необходима, потому что некоторые цвета выражаются в виде кодов RGB (например, #00ff00 вместо green ).
Функции SUMIF и SUMIFS в Google (Excel) таблице
В Google таблицах, как и в Excel применяются функции SUMIF (СУММЕСЛИ) и SUMIFS (СУММЕСЛИМН) в целом ряде ситуаций и при решении многочисленных повседневных аналитических задач.
Функция SUMIF (СУММЕСЛИ) в Google таблицах
- Принцип действия: находит и отображает сумму содержимого ячеек, соответствующих определенному условию.
- Синтаксис:
Если назначение функции VLOOKUP (ВПР) в том, чтобы просто отобразить данные из одного места Google таблиц (Excel) в другое, то SUMIF (СУММЕСЛИ) используют, чтобы просуммировать числовые данные по заданному сценарию.
Рассмотрим классический пример применения функции SUMIF на простом примере: у нас есть товары, рассортированные по группам и есть данные по продажам этих товаров.
Стоит задача посчитать сколько товаров было продано по каждой товарной группе и вывод этой информации в отдельный итоговый свод.
Хорошо, если данных мало и можно быстро отсортировать товары по группам и в уме посчитать общие продажи, но когда массив с данными превышает несколько десятков строк — просуммировать в уме уже проблематично и неудобно.
В ячейке G2 (напротив товарной группы с овощами) ставим знак «равно» («=») и пишем формулу следующего вида:
Чтобы «закомментировать» диапазон, после того, как он выбран жмем кнопку F4, это нужно для того, чтобы при протягивании формулы по другим ячейкам, формулы ссылались на строго определенные нами диапазоны.
Если пройти по таблице глазами и в уме сложить продажи по товарным группам, то выйдет абсолютно такой же результат. Таким образом, при помощи функции SUMIF (СУММЕСЛИ) на обработке больших массивов данных с продажами можно экономить уйму времени.
Функция SUMIFS (СУММЕСЛИМН) в Google таблицах
Практически всегда стоит задача отфильтровать данные по заданным условиям (параметр дата, значение показателя, наименование клиента, признак действия) и вывести результат суммы этих отфильтрованных данных в определенную ячейку гугл таблиц — этим и занимается функция SUMIFS (СУММЕСЛИМН).
Рассмотрим пример, где стоит задача: вывести сумму проданных товаров по месяцам, которые будут отфильтрованы по товарным группам. Для этой процедуры мы сначала создаем вспомогательный столбец (потом его можно будет удалить / очистить) и выводим месяцы из указанных дат формулой MONTH (МЕСЯЦ). Синтаксис прост:
Мы уже с вами знаем функцию IF(ЕСЛИ), давайте воспользуемся ею для красоты и заменим числа месяцев, выводимых формулой MONTH (МЕСЯЦ), на названия этих месяцев:
Когда мы из общей даты «изъяли» месяц из столбца с датами и отобразили его во вспомогательной колонке, мы можем ссылаться на эти месяцы при формировании отчета продаж по месяцам товаров, отфильтрованных по группам.
В формуле нужно будет изменять только соответствующий месяц в столбце, а потом протянуть до конца таблицы.
Таким образом можно построить аналитический отчет по месяцам или по любым другим параметрам через функцию SUMIFS (СУММЕСЛИМН). Поделитесь в комментариях, где вы применяли данную функцию и какие задачи решали с ее помощью?
Подсчет ячеек на основе цвета ячеек в Google Таблицах
Чем больше я использую Google Таблицы, тем больше понимаю, насколько они эффективны. И в то же время есть некоторые вещи, которые я хотел бы встроить в таблицы Google. Одна из таких функций, которую я хотел бы иметь в Google Таблицах, — это подсчет количества ячеек на основе цвета фона.
Например, предположим, что у меня есть набор данных, показанный ниже, и я хочу быстро узнать общее количество ячеек с зеленым и желтым цветом фона.
Но поскольку эта функция еще не является частью таблиц Google, вы можете сделать это, используя собственный скрипт приложений Google для создания собственной функции в таблицах Google.
В этом уроке я покажу вам, как легко создать функцию, которая позволит вам подсчитывать ячейки на основе цвета фона.
Подсчет ячеек на основе цвета фона
Предположим, у вас есть набор данных, показанный ниже, и вы хотите быстро узнать общее количество ячеек с зеленым и желтым цветом фона.
Первым шагом будет создание скрипта Google Apps для создания настраиваемой функции, которая может делать это в Google Таблицах.
Я попытался найти такой сценарий в Интернете и, наконец, нашел его в этом блоге.
Ниже приведен скрипт приложений Google для настраиваемой функции, которая позволит вам подсчитывать ячейки в зависимости от цвета ячеек фона.
Вот шаги, которые необходимо предпринять, чтобы поместить этот скрипт в документ Google Таблиц:
- В вашем документе Google Таблиц нажмите Инструменты
- В появившихся параметрах щелкните Редактор сценариев. Откроется редактор скриптов приложений.
- По умолчанию вы будете в окне кода Code.gs. Удалите все, что уже там, и скопируйте и вставьте приведенный выше код.
- Щелкните значок Сохранить на панели инструментов.
- Закройте окно скрипта приложений.
Выполнив указанные выше шаги, я добавил код сценария в Google Таблицы, чтобы теперь я мог использовать свои недавно созданные настраиваемые формулы на листе (как обычные формулы).
Примечание. Если у вас уже есть другой код в окне Code.gs и вы не хотите его испортить, лучше всего вставить новый файл сценария и вставить туда код. Для этого щелкните значок «Плюс» рядом с «Файлы» и выберите «Сценарий». Это вставит новый файл сценария (дайте ему имя) и скопируйте и вставьте приведенный выше код в этот новый файл сценария.
Теперь, когда у нас есть код, давайте посмотрим, как использовать настраиваемую формулу для подсчета ячеек с определенным цветом фона.
В нашем примере мне нужно посчитать все имена, выделенные зеленым цветом.
Для этого введите следующую формулу в ячейку, в которой вы хотите подсчитать количество цветных ячеек:
В приведенной выше формуле я использовал ячейку C1 как ячейку, из которой формула должна подбирать цвет фона. Вы также можете использовать любую ячейку в диапазоне данных, чтобы получить цвет, а затем рассчитывать на его основе.
Так же, как формула для подсчета ячеек зеленого цвета, приведенная ниже формула подсчитывает все ячейки желтого цвета:
Как работает эта формула?
Позвольте мне быстро объяснить, что здесь происходит.
Эта настраиваемая формула, которую мы создали, принимает два аргумента:
- диапазон ячеек с цветными ячейками, которые нам нужно подсчитать
- ячейка с цветом фона, который необходимо подсчитать
В нашем примере я взял второй аргумент как ячейку C1, так как она также имеет тот же цвет, который я хочу посчитать. При желании вы можете выбрать любую ячейку из диапазона и использовать ее вместо создания новой.
Например, вы можете выбрать ячейку A3 вместо C1 в качестве второго аргумента, поскольку она также имеет тот же цвет.
Когда вы используете эту формулу, она просматривает все ячейки в диапазоне и проверяет цвет фона всех ячеек. Если цвет фона ячейки совпадает с цветом фона из второго аргумента, то это засчитывается, иначе это не так.
После того, как формула прошла через все ячейки в диапазоне, она просто возвращает общее количество найденных ячеек с одинаковым цветом фона.
Примечание. Вы можете использовать ту же формулу для подсчета ячеек с разным цветом фона. Например, если в одном наборе данных у меня есть зеленый и красный, то я могу использовать эту формулу в двух разных ячейках: одна относится к зеленой ячейке и подсчитывает общее количество зеленых ячеек, а другая — к количеству красных ячеек. общее количество эритроцитов.
Как сделать так, чтобы формула автоматически обновлялась при изменении
Хотя формула работает идеально и подсчитывает количество ячеек с определенным цветом фона, есть один незначительный раздражающий фактор.
Он не обновляется автоматически при внесении изменений. Например, если я удалю цвет из одной из ячеек или вручную добавлю цвет к одной из ячеек, формула не будет автоматически обновляться, чтобы дать мне правильный результат.
Даже если я перейду к ячейке с формулой, перейду в режим редактирования и нажму клавишу «Ввод», она все равно не будет пересчитана.
Причина в том, что он не рассматривает это как изменение, влекущее за собой пересчет. Я даже попытался обновить книгу, но она тоже не сработала (возможно, из-за какой-то проблемы с кешем)
К сожалению, нет автоматического способа сделать это, но есть действительно простой ручной способ сделать это.
Итак, вот исправление — перейдите к любой из ячеек в диапазоне, войдите в режим редактирования (нажмите F2 или дважды щелкните ячейку), добавьте пробел в конце содержимого ячейки и нажмите Enter. Как только вы это сделаете, вы увидите, что формула пересчитывается и дает правильный результат.
Теперь вы можете вернуться в ячейку, в которой вы внесли это изменение, добавив пробел и убрав его.
Хотя это неудобно, на данный момент это единственный способ, которым я мог выяснить, чтобы убедиться, что мы получаем правильное количество цветных ячеек, используя эту настраиваемую формулу.
Если вы работаете с большим набором данных, эта формула может занять несколько секунд или даже несколько минут, чтобы вычислить общее количество ячеек с определенным цветом фона. В это время вы можете увидеть текст «Загрузка» в ячейке (как показано ниже).
Вот как вы можете создавать и использовать свою собственную функцию для подсчета продаж на основе цвета фона в Google Таблицах.
Суммирование данных на основе цвета ячеек в электронных таблицах Google
Я видел этот вопрос, и хотя я сталкиваюсь с той же проблемой, ответ на него не помогает в моем случае.
У меня большие электронные таблицы, которые часто меняются. Поэтому, согласно решению, принятому в этом вопросе, трудно дублировать все заполненные столбцы и громоздко редактировать еще одну ячейку каждый раз, когда я хочу изменить цвет фона ячеек.
Существует ли способ суммировать данные на основе условия относительно цвета фона ячеек в Google Spreadsheets?