Чем больше я использую Google Таблицы, тем больше понимаю, насколько они эффективны. И в то же время есть некоторые вещи, которые я хотел бы встроить в таблицы Google. Одна из таких функций, которую я хотел бы иметь в Google Таблицах, — это подсчет количества ячеек на основе цвета фона.

Например, предположим, что у меня есть набор данных, показанный ниже, и я хочу быстро узнать общее количество ячеек с зеленым и желтым цветом фона.

Но поскольку эта функция еще не является частью таблиц Google, вы можете сделать это, используя собственный скрипт приложений Google для создания собственной функции в таблицах Google.

В этом уроке я покажу вам, как легко создать функцию, которая позволит вам подсчитывать ячейки на основе цвета фона.

Итак, приступим!

Подсчет ячеек на основе цвета фона

Предположим, у вас есть набор данных, показанный ниже, и вы хотите быстро узнать общее количество ячеек с зеленым и желтым цветом фона.

Первым шагом будет создание скрипта Google Apps для создания настраиваемой функции, которая может делать это в Google Таблицах.

Я попытался найти такой сценарий в Интернете и, наконец, нашел его в этом блоге.

Ниже приведен скрипт приложений Google для настраиваемой функции, которая позволит вам подсчитывать ячейки в зависимости от цвета ячеек фона.

function countColoredCells(countRange,colorRef) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formula = activeRange.getFormula();

var rangeA1Notation = formula.match(/\((.*)\,/).pop();
var range = activeSheet.getRange(rangeA1Notation);
var bg = range.getBackgrounds();
var values = range.getValues();

var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
var colorCell = activeSheet.getRange(colorCellA1Notation);
var color = colorCell.getBackground();

var count = 0;

for(var i=0;i<bg.length;i++)
for(var j=0;j<bg[0].length;j++)
if( bg[i][j] == color )
count=count+1;
return count;
};

Вот шаги, которые необходимо предпринять, чтобы поместить этот скрипт в документ Google Таблиц:

  • В вашем документе Google Таблиц нажмите Инструменты
  • В появившихся параметрах щелкните Редактор сценариев. Откроется редактор скриптов приложений.
  • По умолчанию вы будете в окне кода Code.gs. Удалите все, что уже там, и скопируйте и вставьте приведенный выше код.
  • Щелкните значок Сохранить на панели инструментов.
  • Закройте окно скрипта приложений.

Выполнив указанные выше шаги, я добавил код сценария в Google Таблицы, чтобы теперь я мог использовать свои недавно созданные настраиваемые формулы на листе (как обычные формулы).

Примечание. Если у вас уже есть другой код в окне Code.gs и вы не хотите его испортить, лучше всего вставить новый файл сценария и вставить туда код. Для этого щелкните значок «Плюс» рядом с «Файлы» и выберите «Сценарий». Это вставит новый файл сценария (дайте ему имя) и скопируйте и вставьте приведенный выше код в этот новый файл сценария.

Теперь, когда у нас есть код, давайте посмотрим, как использовать настраиваемую формулу для подсчета ячеек с определенным цветом фона.

В нашем примере мне нужно посчитать все имена, выделенные зеленым цветом.

Для этого введите следующую формулу в ячейку, в которой вы хотите подсчитать количество цветных ячеек:

= countcoloredcells (A2: A11, C1)

В приведенной выше формуле я использовал ячейку C1 как ячейку, из которой формула должна подбирать цвет фона. Вы также можете использовать любую ячейку в диапазоне данных, чтобы получить цвет, а затем рассчитывать на его основе.

Так же, как формула для подсчета ячеек зеленого цвета, приведенная ниже формула подсчитывает все ячейки желтого цвета:

= countcoloredcells (A2: A11, D1)

Как работает эта формула?

Позвольте мне быстро объяснить, что здесь происходит.

Эта настраиваемая формула, которую мы создали, принимает два аргумента:

  • диапазон ячеек с цветными ячейками, которые нам нужно подсчитать
  • ячейка с цветом фона, который необходимо подсчитать

В нашем примере я взял второй аргумент как ячейку C1, так как она также имеет тот же цвет, который я хочу посчитать. При желании вы можете выбрать любую ячейку из диапазона и использовать ее вместо создания новой.

Например, вы можете выбрать ячейку A3 вместо C1 в качестве второго аргумента, поскольку она также имеет тот же цвет.

Когда вы используете эту формулу, она просматривает все ячейки в диапазоне и проверяет цвет фона всех ячеек. Если цвет фона ячейки совпадает с цветом фона из второго аргумента, то это засчитывается, иначе это не так.

После того, как формула прошла через все ячейки в диапазоне, она просто возвращает общее количество найденных ячеек с одинаковым цветом фона.

Примечание. Вы можете использовать ту же формулу для подсчета ячеек с разным цветом фона. Например, если в одном наборе данных у меня есть зеленый и красный, то я могу использовать эту формулу в двух разных ячейках: одна относится к зеленой ячейке и подсчитывает общее количество зеленых ячеек, а другая — к количеству красных ячеек. общее количество эритроцитов.

Как сделать так, чтобы формула автоматически обновлялась при изменении

Хотя формула работает идеально и подсчитывает количество ячеек с определенным цветом фона, есть один незначительный раздражающий фактор.

Он не обновляется автоматически при внесении изменений. Например, если я удалю цвет из одной из ячеек или вручную добавлю цвет к одной из ячеек, формула не будет автоматически обновляться, чтобы дать мне правильный результат.

Даже если я перейду к ячейке с формулой, перейду в режим редактирования и нажму клавишу «Ввод», она все равно не будет пересчитана.

Причина в том, что он не рассматривает это как изменение, влекущее за собой пересчет. Я даже попытался обновить книгу, но она тоже не сработала (возможно, из-за какой-то проблемы с кешем)

К сожалению, нет автоматического способа сделать это, но есть действительно простой ручной способ сделать это.

Итак, вот исправление — перейдите к любой из ячеек в диапазоне, войдите в режим редактирования (нажмите F2 или дважды щелкните ячейку), добавьте пробел в конце содержимого ячейки и нажмите Enter. Как только вы это сделаете, вы увидите, что формула пересчитывается и дает правильный результат.

Теперь вы можете вернуться в ячейку, в которой вы внесли это изменение, добавив пробел и убрав его.

Хотя это неудобно, на данный момент это единственный способ, которым я мог выяснить, чтобы убедиться, что мы получаем правильное количество цветных ячеек, используя эту настраиваемую формулу.

Если вы работаете с большим набором данных, эта формула может занять несколько секунд или даже несколько минут, чтобы вычислить общее количество ячеек с определенным цветом фона. В это время вы можете увидеть текст «Загрузка» в ячейке (как показано ниже).

Вот как вы можете создавать и использовать свою собственную функцию для подсчета продаж на основе цвета фона в Google Таблицах.

Надеюсь, вы нашли этот урок полезным.

Рекомендуем! Пройти очень эффективный Онлайн-курс «Гугл-таблицы» и «Excel» для начинающих.