Как подсчитать или суммировать ячейки на основе цвета ячеек в Google Таблицах?

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

Подсчет значений ячеек на основе цвета с помощью скрипта в Google Таблицах

Суммирование значений ячеек на основе цвета с помощью скрипта в Google Таблицах

Подсчет или суммирование значений ячеек по цвету с помощью Kutools для Excel в Microsoft Excel

Подсчет значений ячеек на основе цвета с помощью скрипта в 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 для Excel в Microsoft Excel

В Microsoft Excel для подсчета или суммирования значений ячеек на основе определенного цвета ячейки утилита Подсчет по цвету из Kutools для Excel поможет вам быстро выполнить эту задачу.

После установки Kutools для Excel выполните следующие действия:

1. Выберите диапазон ячеек, которые вы хотите подсчитать или суммировать на основе цвета ячейки, затем нажмите Kutools Plus > Подсчет по цвету, см. скриншот:

2. В диалоговом окне Подсчет по цвету выберите Стандартное форматирование из выпадающего списка Метод цвета, затем выберите Фон из выпадающего списка Тип подсчета, см. скриншот:

3. Затем нажмите кнопку Создать отчет, и новый лист с рассчитанными результатами будет создан сразу, см. скриншот:

Примечание: С помощью этой мощной функции вы также можете рассчитывать значения ячеек на основе условного форматирования или цвета шрифта.

Нажмите Скачать и попробовать бесплатно Kutools для Excel прямо сейчас!