Как подсчитать или суммировать ячейки на основе цвета ячейки в Google таблицах?
В повседневной работе с электронными таблицами вы можете столкнуться с ситуациями, когда нужно подсчитывать или суммировать значения ячеек в зависимости от конкретного цвета фона ячейки, как показано на скриншоте ниже. Например, вы можете захотеть учитывать или суммировать только те ячейки, которые выделены определенным цветом, чтобы быстро проанализировать данные по категориям или статусу. Это руководство расскажет, как выполнить эту задачу не только в Google Таблицах, где нет встроенной поддержки таких расчетов на основе цвета, но и в Microsoft Excel, который предлагает несколько различных подходов — от встроенных функций до продвинутых инструментов.
Понимание того, как обрабатывать анализ данных на основе цвета, может сделать вашу работу более эффективной, особенно если цвета используются для отметки статусов, приоритетов или категорий. Мы также обсудим различные решения, сравним их сценарии использования и предоставим практические советы по операциям, а также напоминания об ошибках, чтобы ваши задачи выполнялись гладко.
- Подсчет значений ячеек на основе цвета ячейки с помощью скрипта в Google таблицах
- Суммирование значений ячеек на основе цвета ячейки с помощью скрипта в Google таблицах
- Подсчет или суммирование значений ячеек по цвету ячейки с помощью Kutools для Excel в Microsoft Excel
Подсчет значений ячеек на основе цвета ячейки с помощью скрипта в Google таблицах
Google Таблицы не предлагают прямой возможности подсчета ячеек на основе цвета фона. Однако вы можете достичь этого с помощью пользовательского Apps Script. Этот скрипт действует как определяемая пользователем функция, позволяя вам использовать её как формулу. Вот как настроить и использовать этот скрипт:
1. Нажмите Инструменты > Редактор скриптов, чтобы получить доступ к среде написания скриптов. См. скриншот:
2. В окне проекта выберите Файл > Создать > Файл скрипта, чтобы открыть новый модуль кода, как показано:
3. При запросе введите имя для вашего нового скрипта и подтвердите. Дайте скрипту осмысленное название, чтобы позже было легче определить его назначение.
4. Нажмите OK, затем скопируйте и вставьте следующий код, чтобы заменить любой образец кода в модуле. Убедитесь, что вы вставили его точно так, как он предоставлен.
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. Нажмите OK, и в новом окне модуля кода замените любой стандартный код, вставив предоставленный скрипт для суммирования цветных ячеек. Убедитесь, что весь код скопирован внимательно, так как пропущенные символы могут привести к синтаксическим ошибкам.
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 предлагает специальную утилиту Count by Color (Подсчет по цвету), которая позволяет вам получать количество и суммы по цвету фона или шрифта напрямую — это особенно полезно для больших диапазонов данных и когда нужны быстрые, повторяемые результаты.
После установки 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек