Как подсчитать или просуммировать ячейки в таблице Google по цвету фона?
В повседневной работе с электронными таблицами вы можете столкнуться с задачей подсчёта или суммирования значений ячеек на основе их цвета фона — как, например, на скриншоте ниже. Допустим, вам нужно быстро получить итог только по ячейкам, выделенным определённым цветом, чтобы проанализировать данные по категориям или статусам. В этом руководстве мы покажем, как решить такую задачу как в Google Таблицах, где нет встроенной поддержки расчётов по цвету, так и в Microsoft Excel, где доступны разные подходы — от стандартных функций до продвинутых инструментов.
Понимание того, как обрабатывать данные, основанные на цвете, повышает эффективность вашей работы — особенно когда цвета используются для обозначения статусов, приоритетов или категорий. Мы рассмотрим различные решения, сравним сценарии их применения, дадим практические советы по использованию и напомним о типичных ошибках, чтобы ваши задачи выполнялись без сбоев.
- Подсчёт значений ячеек по цвету ячейки с помощью скрипта в таблице Google
- Суммирование значений ячеек по цвету ячейки с помощью скрипта в таблице Google
- Подсчёт или суммирование значений ячеек по цвету ячейки с помощью Kutools для Excel в Microsoft Excel
Подсчёт значений ячеек по цвету ячейки с помощью скрипта в таблице Google
Таблицы Google не поддерживают прямой подсчёт ячеек по цвету фона, но эту задачу легко решить с помощью пользовательского скрипта на Apps Script. Такой скрипт работает как собственная функция, которую можно использовать в таблице точно так же, как обычную формулу. Ниже — пошаговое руководство по его настройке и применению:
1. Нажмите Инструменты > Редактор скриптов, чтобы открыть среду разработки скриптов. См. снимок экрана:
2. В окне проекта выберите Файл > Создать > Файл скрипта, чтобы открыть новый модуль кода, как показано:
3. Когда появится запрос, введите имя для нового файла скрипта и подтвердите. Дайте скрипту осмысленное название — так в будущем будет проще понять его назначение.
4. Нажмите ОК, затем скопируйте и вставьте приведённый ниже код, заменив им любой пример кода в модуле. Убедитесь, что код вставлен точно так, как указано.
function countColoredCells(countRange,colorRef) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var countCells = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
countCells = countCells + 1;
return countCells;
};
5. Сохраните файл скрипта, вернитесь в свою таблицу и используйте новую функцию так же, как любую другую формулу в Таблицах Google. Введите:=countcoloredcells(A1:E11,A1) в пустую ячейку, чтобы подсчитать ячейки в диапазоне A1:E11, цвет которых совпадает с цветом ячейки A1. Нажмите Enter, чтобы получить результат. Если появится запрос на разрешение, авторизуйте запуск скрипта в вашей таблице.
Примечание: A1:E11 — это ваш диапазон данных; A1 — эталонная ячейка с цветом, по которому выполняется подсчёт. Убедитесь, что эталонные ячейки имеют точный цвет и избегайте использования объединённых ячеек для обеспечения максимальной надёжности.
6. Чтобы подсчитать другие цвета, просто повторите формулу, указав другую эталонную ячейку с нужным цветом. Если диапазон изменится — не забудьте скорректировать его в формуле!
Если вы получили ошибку или неожиданный результат, дважды проверьте, сохранён ли скрипт и корректно ли указана эталонная ячейка с цветом. Функции на основе Apps Script пересчитываются только при изменении самой функции или её аргументов — если вы позже измените цвет ячеек, повторно введите формулу или нажмите Enter, чтобы обновить результат.
Суммирование значений ячеек по цвету ячейки с помощью скрипта в таблице Google
Суммирование значений ячеек по заданному цвету ячейки в Таблицах Google требует аналогичного подхода с использованием Apps Script. Это особенно полезно для финансовых таблиц, журналов статусов или любых других случаев, когда цвета обозначают категории, под которыми находятся числовые данные.
1. В Таблицах Google откройте Редактор скриптов через меню Инструменты > Редактор скриптов. В окне проекта выберите Файл > Создать > Файл скрипта, чтобы добавить новый модуль кода. Присвойте ему уникальное имя в появившемся окне — например, «SumColoredCells», — чтобы упростить отслеживание его назначения. Подтвердите создание модуля.
2. Нажмите ОК, и в новом окне модуля кода замените любой стандартный код на предоставленный скрипт для суммирования ячеек по цвету. Внимательно убедитесь, что весь код скопирован полностью — пропущенные символы могут вызвать синтаксические ошибки.
function sumColoredCells(sumRange,colorRef) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var sumValues = activeSht.getRange(countRangeAddress).getValues();
var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var totalValue = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
if ((typeof sumValues[i][k]) == 'number')
totalValue = totalValue + (sumValues[i][k]);
return totalValue;
};
3. После сохранения скрипта вернитесь в таблицу, введите формулу =sumcoloredcells(A1:E11,A1) в пустую ячейку и нажмите Enter. Эта формула суммирует значения в диапазоне A1:E11, где цвет фона совпадает с цветом ячейки A1. При использовании этой функции убедитесь, что все целевые ячейки содержат числовые значения — нечисловые значения будут проигнорированы.
Примечание: A1:E11 — это ваш диапазон данных, а A1 задаёт эталонный цвет. Формула суммирует только видимые числовые значения — убедитесь, что объединённые ячейки или ошибки в диапазоне не влияют на итоговую сумму.
4. Вы можете повторить описанный выше процесс для суммирования значений по другим цветовым категориям, изменив эталонную ячейку цвета в формуле. Если ваши данные обновляются или вы меняете цвет фона, не забудьте обновить формулу, чтобы получить актуальные результаты.
Если сумма возвращает ноль или значение ошибки, проверьте, содержит ли диапазон числа и точно ли совпадает цвет. Кроме того, пересчёт не выполняется автоматически при изменении только цвета ячейки — отредактируйте ячейку с формулой, чтобы принудительно обновить результат.
Подсчёт или суммирование значений ячеек по цвету ячейки с помощью Kutools для Excel в Microsoft Excel
При работе в Microsoft Excel подсчёт или суммирование ячеек по цвету — частая задача, особенно в отчётах по управлению проектами, учёту запасов или контролю качества. Kutools для Excel предлагает специальную утилиту Подсчёт по цвету, которая позволяет мгновенно получать количественные и суммарные значения на основе цвета фона или цвета шрифта — это особенно полезно для больших диапазонов и ситуаций, когда нужны быстрые и воспроизводимые результаты.
После установки Kutools для Excelвыполните следующие действия:
1. Выделите диапазон, в котором нужно подсчитать или просуммировать ячейки по цвету, затем нажмите KUTOOLS PLUS > Подсчет по цвету. См. снимок экрана ниже для справки:
2. Появится диалоговое окно Подсчет по цвету. В разделе Режим цвета установите параметр Стандартное форматирование и выберите Фон для Тип статистики. Внимательно проверьте предварительный просмотр и параметры:
3. Нажмите Создать отчет, чтобы создать новый лист с детализацией количества и сумм для каждого цвета в вашем диапазоне. В отчёт входят как количество, так и сумма цветных ячеек — это упрощает дальнейший анализ и удобно для использования в качестве справочного материала.
Примечание: Эта функция также может выполнять расчёты на основе условного форматирования или цвета шрифта. Используйте правила условного форматирования для динамического анализа; в противном случае инструмент лучше всего работает со статической заливкой ячеек. При изменении цвета исходных ячеек потребуется повторный запуск утилиты «Подсчёт по цвету» для получения обновлённых результатов. В случае возникновения проблем убедитесь, что Kutools активен и обновлён до последней версии.
Нажмите «Скачать» и начните бесплатную пробную версию Kutools для Excel прямо сейчас!
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек