Note: The other languages of the website are Google-translated. Back to English

Як підрахувати або підсумувати клітинки на основі кольору комірки в аркуші 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. Потім збережіть цей код сценарію і поверніться назад до аркуша, введіть таку формулу: = кол-во кольорових осередків (A1: E11, A1) у порожню клітинку, а потім натисніть вводити клавіша, щоб отримати розрахований результат. Дивіться знімок екрана:

примітки: У цій формулі: 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. А потім збережіть цей код, поверніться на аркуш і введіть таку формулу: = кольорові осередки (A1: E11, A1) у порожню клітинку та натисніть вводити ключ, щоб отримати розрахований результат, див. знімок екрана:

примітки: У цій формулі: A1: E11 - діапазон даних, який ви хочете використовувати, A1 це клітинка з певним кольором тла, який потрібно підсумувати.

4. А потім ви можете повторити наведену вище формулу для підсумовування інших конкретних кольорових клітин.


Підрахуйте або підсумуйте значення комірок за кольором комірок за допомогою Kutools для Excel у Microsoft Excel

У Microsoft Excel, щоб підрахувати або підсумувати значення комірок на основі певного кольору комірки, Kutools для ExcelАвтора Підрахувати за кольором утиліта допоможе вам виконати це завдання якомога швидше.

Kutools для Excel : з більш ніж 300 зручними надбудовами Excel, які можна спробувати без обмежень протягом 30 днів. 

після установки Kutools для Excel, будь ласка, зробіть так:

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

2, в Підрахувати за кольором діалогове вікно, виберіть Стандартне форматування від Колірний метод , а потім виберіть фон від Тип підрахунку випадаюче, див. знімок екрана:

3. Потім натисніть Створити звіт , і відразу створюється новий аркуш із обчисленими результатами, див. знімок екрана:

примітки: Завдяки цій потужній функції ви також можете обчислювати значення комірок на основі умовного форматування або кольору шрифту.

Натисніть Завантажити та безкоштовну пробну версію Kutools для Excel зараз!


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (66)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Привіт! Чудовий сценарій, але я зіткнувся з проблемою під час спроби редагувати значення в кольорових клітинках. Сценарій не оновлює значення, якщо я зміню колір комірки. Я можу примусово оновити його, лише якщо видалю вміст комірки, зміню колір, а потім знову введу значення.
Цей коментар був мінімізований модератором на сайті
Гей, дякую за цей сценарій. У мене виникла проблема зі сценарієм для таблиць Google: TypeError: Неможливо викликати метод "pop" з нулем. (рядок 5, файл «SumByColor»). Чи можете ви допомогти?, дякую
Цей коментар був мінімізований модератором на сайті
Дякуємо за допомогу з "Порахувати клітинки на основі кольору для таблиць Google". Здається, обмеження полягає в тому, що під час зміни кольорів клітинок аркуш не оновлюється, а підсумки є неправильними. Мені потрібно видалити код із загальної комірки та знову скопіювати з сусідньої комірки. Не ідеально, але працює. Знову дякую.
Цей коментар був мінімізований модератором на сайті
Привіт, Тодд,
Так, як ви сказали, ви повинні повторно ввести формулу, коли колір клітинки зміниться.
Можливо, інших хороших способів вирішення цієї проблеми немає.
Якщо хтось інший має хороші методи, прокоментуйте тут.
Дякую!
Цей коментар був мінімізований модератором на сайті
Ваша функція sumColoredCells() неправильна, оскільки вона не використовує sumRange
Цей коментар був мінімізований модератором на сайті
Привіт, Дейв,
Наведений вище сценарій добре працює в моїй таблиці Google.
Застосовуючи формулу, спочатку слід зберегти вікно сценарію.
Будь ласка, спробуйте, дякую!
Цей коментар був мінімізований модератором на сайті
Гей, дякую за цей сценарій. У мене виникла проблема зі сценарієм для таблиць Google: TypeError: Неможливо викликати метод "pop" з нулем. (рядок 5, файл «SumByColor»). Чи можете ви допомогти?, дякую
Цей коментар був мінімізований модератором на сайті
Привіт, Лора,
Сценарій добре працює в моїх таблицях Google.
Якщо у вашій таблиці Google є проблема, я можу поділитися з вами своєю таблицею Google.
Будь ласка, вкажіть свою електронну адресу.
Дякую!
Цей коментар був мінімізований модератором на сайті
Привіт, у мене така ж проблема, ви вирішили її?
Цей коментар був мінімізований модератором на сайті
Можливо, проблема полягає у вашій регіональній конфігурації, цей сценарій вважає, що ви розділяєте параметри за допомогою ',', але, ймовірно, це не ваш випадок. У моєму випадку я використовую ';', тому я змінив сценарій, змінивши символ у рядках

var countRangeAddress = activeformula.match(/\((.*)\;/).pop().trim();

var colorRefAddress = activeformula.match(/\;(.*)\)/).pop().trim();
Цей коментар був мінімізований модератором на сайті
cambio la coma (,) por punto y coma (;) pero aun así no me cuenta el color. me sale como resultado (0 cero).
Цей коментар був мінімізований модератором на сайті
Чи є спосіб включити від’ємні числа в суміш і отримати правильну суму? Мій продовжує додавати всі числа в діапазоні, навіть якщо число має від’ємний знак.
Цей коментар був мінімізований модератором на сайті
Я отримую повідомлення "Дія не дозволена, рядок 0" Що я роблю не так?
Цей коментар був мінімізований модератором на сайті
Я постійно отримую помилку "Дія не дозволена (рядок 0)". Що це означає?
Цей коментар був мінімізований модератором на сайті
чи він автоматично оновлюється при додаванні нових даних?
Цей коментар був мінімізований модератором на сайті
Не схоже
Цей коментар був мінімізований модератором на сайті
Ви коли-небудь з'ясовували, як зробити це для автоматичного оновлення?
Цей коментар був мінімізований модератором на сайті
Хтось знайшов спосіб автоматичного оновлення?
Цей коментар був мінімізований модератором на сайті
Мені теж потрібно з’ясувати, як налаштувати автоматичне оновлення!... Хтось знайшов відповідь?
Цей коментар був мінімізований модератором на сайті
Хтось знайшов спосіб автоматичного оновлення?
Цей коментар був мінімізований модератором на сайті
Ні, я намагаюся знайти відповідь
Цей коментар був мінімізований модератором на сайті
Замініть ці рядки:
var countRangeAddress = activeformula.match(/\((.*)\;/).pop().trim();


var colorRefAddress = activeformula.match(/\;(.*)\)/).pop().trim();

Повідомте, ніж , було змінено на ;
Цей коментар був мінімізований модератором на сайті
Привіт там,


Чи потрібна мені допомога з формулою, подібною до СУММИ загальних ключових слів, які відповідають і відображають кількість кольорів.


Що я, чого досягти, це визначити кількість ноутбуків, яке є ключовим словом, але розпізнає Доступний на основі кольору осередків?
Цей коментар був мінімізований модератором на сайті
я намагаюся зробити щось подібне
=countColoredCells (H62:H,B2)countif("MBA13R15")
Цей коментар був мінімізований модератором на сайті
Здравствуйте,
Формула мені не підходить.
Якщо я зроблю це в електронній таблиці, виникнуть лише помилки.
Цей коментар був мінімізований модератором на сайті
Дякую, це чудово спрацювало!
Цей коментар був мінімізований модератором на сайті
Сценарій SumColoredCells чудово працює. Чи знаєте ви, чи є спосіб відобразити (в одній клітинці) суму 2 кольорів? Наприклад, якщо я хочу, щоб сума жовтих і зелених клітинок відображалася в одній клітинці, чи це можливо? Я думаю, що формула буде такою:
=SUM(сума кольорових клітинок(A1:E11,A1)),(сума кольорових клітинок(A1:E11,A5)) ....Але це призводить до ПОМИЛКИ. Є ідеї?
Цей коментар був мінімізований модератором на сайті
Привіт, Джеффе,
Можливо, немає прямої формули для вирішення вашої проблеми, але ви можете спочатку підсумувати кольорові клітинки окремо, а потім підсумувати два обчислені результати, як вам потрібно.
Цей коментар був мінімізований модератором на сайті
Дякую за відповідь. Я зробив це, але хотів би об’єднати формулу в одну клітинку, оскільки мені доводиться оновлювати діапазони щотижня. У всякому разі, дякую за перевірку!
Цей коментар був мінімізований модератором на сайті
Здравствуйте,


Однак ця функція працює, коли я намагаюся підсумувати 2 її екземпляри в тій самій клітинці, що й у (у моєму випадку):


=сума кольорових клітинок(A103:AW103, C171) + сума кольорових клітинок(A138:AW138, C171)


Це дає мені #Error - діапазон не знайдено (рядок 6).


Це не працює, навіть якщо я використовую SUM() або ADD(). Є ідеї?


Спасибо заранее.
Цей коментар був мінімізований модератором на сайті
Дякую :*
Цей коментар був мінімізований модератором на сайті
Привіт там,
на таблицях Google, схоже, не працює, я перейшов до налагодження, і для обох процесів у рядку 5 здається помилка типу, повідомлення таке: неможливо згадати "метод pop2 з нулем. Чи має це сенс? Дякую багато!
Цей коментар був мінімізований модератором на сайті
Інтернет повний екземплярів цього рішення, і воно не працює.
Цей коментар був мінімізований модератором на сайті
Раніше цей скрипт працював чудово, але більше не працює. Чи є якась зміна, яка зруйнувала його?
There are no comments posted here yet
Load More
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця