Содержание:
Чем больше я использую Google Таблицы, тем больше понимаю, насколько они эффективны. И в то же время есть некоторые вещи, которые я хотел бы встроить в таблицы Google. Одна из таких функций, которую я хотел бы иметь в 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)
Так же, как формула для подсчета ячеек зеленого цвета, приведенная ниже формула подсчитывает все ячейки желтого цвета:
= countcoloredcells (A2: A11, D1)
Как работает эта формула?
Позвольте мне быстро объяснить, что здесь происходит.
Эта настраиваемая формула, которую мы создали, принимает два аргумента:
- диапазон ячеек с цветными ячейками, которые нам нужно подсчитать
- ячейка с цветом фона, который необходимо подсчитать
В нашем примере я взял второй аргумент как ячейку C1, так как она также имеет тот же цвет, который я хочу посчитать. При желании вы можете выбрать любую ячейку из диапазона и использовать ее вместо создания новой.
Например, вы можете выбрать ячейку A3 вместо C1 в качестве второго аргумента, поскольку она также имеет тот же цвет.
Когда вы используете эту формулу, она просматривает все ячейки в диапазоне и проверяет цвет фона всех ячеек. Если цвет фона ячейки совпадает с цветом фона из второго аргумента, то это засчитывается, иначе это не так.
После того, как формула прошла через все ячейки в диапазоне, она просто возвращает общее количество найденных ячеек с одинаковым цветом фона.
Примечание. Вы можете использовать ту же формулу для подсчета ячеек с разным цветом фона. Например, если в одном наборе данных у меня есть зеленый и красный, то я могу использовать эту формулу в двух разных ячейках: одна относится к зеленой ячейке и подсчитывает общее количество зеленых ячеек, а другая — к количеству красных ячеек. общее количество эритроцитов.
Как сделать так, чтобы формула автоматически обновлялась при изменении
Хотя формула работает идеально и подсчитывает количество ячеек с определенным цветом фона, есть один незначительный раздражающий фактор.
Он не обновляется автоматически при внесении изменений. Например, если я удалю цвет из одной из ячеек или вручную добавлю цвет к одной из ячеек, формула не будет автоматически обновляться, чтобы дать мне правильный результат.
Даже если я перейду к ячейке с формулой, перейду в режим редактирования и нажму клавишу «Ввод», она все равно не будет пересчитана.
Причина в том, что он не рассматривает это как изменение, влекущее за собой пересчет. Я даже попытался обновить книгу, но она тоже не сработала (возможно, из-за какой-то проблемы с кешем)
К сожалению, нет автоматического способа сделать это, но есть действительно простой ручной способ сделать это.
Итак, вот исправление — перейдите к любой из ячеек в диапазоне, войдите в режим редактирования (нажмите F2 или дважды щелкните ячейку), добавьте пробел в конце содержимого ячейки и нажмите Enter. Как только вы это сделаете, вы увидите, что формула пересчитывается и дает правильный результат.
Теперь вы можете вернуться в ячейку, в которой вы внесли это изменение, добавив пробел и убрав его.
Хотя это неудобно, на данный момент это единственный способ, которым я мог выяснить, чтобы убедиться, что мы получаем правильное количество цветных ячеек, используя эту настраиваемую формулу.
Если вы работаете с большим набором данных, эта формула может занять несколько секунд или даже несколько минут, чтобы вычислить общее количество ячеек с определенным цветом фона. В это время вы можете увидеть текст «Загрузка» в ячейке (как показано ниже).
Надеюсь, вы нашли этот урок полезным.