Как подсчитать или суммировать ячейки на основе цвета ячеек в листе Google?
Подсчет или суммирование значений ячеек на основе определенного цвета фона ячейки, чтобы получить результат, как показано на следующем снимке экрана. В этой статье я расскажу о том, как решить эту задачу в Google Sheet и Microsoft Excel.
Подсчет значений ячеек на основе цвета ячеек с помощью скрипта в листе Google
Суммировать значения ячеек на основе цвета ячеек с помощью скрипта в листе Google
Подсчет значений ячеек на основе цвета ячеек с помощью скрипта в листе Google
Следующий скрипт может помочь вам подсчитать значения ячеек на основе определенного цвета ячеек, сделайте следующее:
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. Затем сохраните этот код сценария и вернитесь на лист, введите эту формулу: = countcoloredcells (A1: E11, A1) в пустую ячейку, а затем нажмите Enter ключ для получения рассчитанного результата. Смотрите скриншот:
Внимание: В этой формуле: A1: E11 диапазон данных, который вы хотите использовать, A1 это ячейка, заполненная определенным цветом, который вы хотите посчитать.
6. Повторите приведенную выше формулу, чтобы подсчитать другие цветные ячейки.
Суммировать значения ячеек на основе цвета ячеек с помощью скрипта в листе Google
Чтобы суммировать значения ячеек с определенным цветом, примените приведенный ниже код сценария.
1. Нажмите Инструменты > Редактор скриптов для перехода в окно проекта и щелкните Файл > Новые > Файл сценария чтобы вставить еще один новый модуль кода, затем в поле подсказки введите имя для этого сценария, см. снимок экрана:
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 это ячейка с определенным цветом фона, который вы хотите суммировать.
4. Затем вы можете повторить приведенную выше формулу, чтобы суммировать другие цветные ячейки.
Подсчитайте или суммируйте значения ячеек по цвету ячеек с помощью Kutools for Excel в Microsoft Excel
В Microsoft Excel для подсчета или суммирования значений ячеек на основе определенного цвета ячейки, Kutools for ExcelАвтора Считать по цвету Утилита может помочь вам выполнить эту задачу как можно быстрее.
| Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 30 дней. |
После установки Kutools for Excel, пожалуйста, сделайте так:
1. Выберите ячейки для диапазона, который вы хотите подсчитать или суммировать в зависимости от цвета ячеек, а затем щелкните Кутулс Плюс > Считать по цвету, см. снимок экрана:
2. В Считать по цвету диалоговое окно, выберите Стандартное форматирование из Цветовой метод раскрывающийся список, а затем выберите проверка данных из Тип подсчета выпадайте, см. снимок экрана:
3. Затем нажмите Создать отчет Кнопка, и сразу создается новый рабочий лист с рассчитанными результатами, см. снимок экрана:
Внимание: С помощью этой мощной функции вы также можете рассчитать значения ячеек на основе условного форматирования или цвета шрифта.
Нажмите Загрузить и бесплатную пробную версию Kutools for Excel прямо сейчас!
Лучшие инструменты для офисной работы
Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
