Как в Microsoft Excel суммировать ячейки по цвету

MS-Excel logoВ Microsoft Excel, при создании таблиц, очень удобно и наглядно помечать ячейки цветом. Но что делать, если нам понадобиться суммировать значения ячеек одного цвета? По умолчанию, данной функции в Excel до сих пор нет. Решить эту проблему можно написав макрос в Visual Basic.

Для этого в Excel версии 2007-2013 переходим на вкладку Разработчик и нажимаем Visual Basic.


Вкладка разработчик

Если такой вкладки нет, то включаем ее следующим образом: Файл/Параметры/Настройка ленты и ставим галочку напротив слова Разработчик.

В открывшемся окне редактора нажимаем Insert/Module и копируем туда следующий текст:

1
2
3
4
5
6
7
8
9
10
11
Public Function SumByColor(DataRange As Range, ColorSample As Range) As Double
     Dim Sum As Double
     Application.Volatile True
 
     For Each cell In DataRange 
         If cell.Interior.Color = ColorSample.Interior.Color Then
             Sum = Sum + cell.Value 
         End If
     Next cell 
     SumByColor = Sum 
 End Function

Сохраняем изменения. Далее в Excel на вкладке Формулы выбираем пункт Вставить функцию. Необходимо выбрать категорию Определенные пользователем — тогда отобразится наша функция — SumByColor.

У этой функции 2 аргументы:

DataRange — диапазон ячеек с числами;

ColorSample — ячейка, цвет которой является образцом для суммирования.

Аргументы функции

Вот и все. Данная функция корректно работает на последних версиях Excel. Надеюсь, она пригодится Вам при составлении отчетов.


Оцените статью 1 Балл2 Балла3 Балла4 Балла5 Баллов (14 проголосовало, средняя оценка: 4,71 из 5)
Загрузка...


Теги:

Трекбэк с Вашего сайта.

Комментарии (14)

  • Avatar

    Анастасия

    |

    Спасибо! работает) и благодарю за содействие при споре. я выиграла золотое колечко )

    Reply

  • Avatar

    Виктор

    |

    Спасибо, но…макрос работает если вручную были отмечены ячейки цветами. В случае использования условного форматирования данный макрос не работает по цветам, а считает всю сумму в выбранном диапазоне, не зависимо от цвета ячейки присвоенного условиями форматирования.

    Reply

  • Avatar

    DONNIEL

    |

    Не получается. После вставки выплывает окно: у данной функции нет аргументов. Как быть?

    Reply

  • Avatar

    Анна

    |

    Спасибо большое!!! Очень Очень помогли)) все ясно и подробно)

    Reply

  • Avatar

    Евгений

    |

    А как сделать, чтобы он не суммировал то, что находится в ячейках, а добавлял единичку, если ячейка закрашена данным цветом? Заранее спасибо.

    Reply

  • Avatar

    Иван

    |

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

    Reply

  • Avatar

    Олеся

    |

    Благодарю! Очень помогли!

    Reply

  • Avatar

    Светлнала

    |

    Спасибо огромное!!!

    Reply

  • Avatar

    иван

    |

    вопрос, а как посчитать количество ячеек с одним цветом шрифта?
    Спасибо

    Reply

  • Avatar

    Макс Ф.

    |

    Спасибо Человек! Очень выручил при подготовке отчета.

    Reply

  • Avatar

    Дмитрий

    |

    Ребят, спасибо. Все работает. Вопрос, а можно сделать чтобы считалось если используешь фильтр? В смысле…выделил 100 ячеек..разукрасил по цветам. Но нужно посчитать допустим первые 25 ячеек.

    Reply

  • Avatar

    Наталия

    |

    Спасибо за функцию 🙂
    А как нужно ее изменить, чтобы функция проверяла один диапазон чисел (ячейки с цветом), а считала другой диапазон чисел

    Reply

  • Avatar

    Алексей

    |

    Спасибо большое! Очень нужно было именно такое решение проблемы, и я рад, что наткнулся на данную инструкцию 🙂

    Reply

  • Avatar

    Анжела

    |

    Спасибо за статью, очень полезна. Но она у меня работает не полностью. То есть когда пробовала на новом чистом файле, то все функции работали. Но когда начала делать нужной мне таблице (со всеми заполоненными данными., при написании формулы нет строчки «Определенные пользователем»

    Reply

Оставить комментарий

Читайте ранее:
компоненты лого
Включение и отключение компонентов в Windows 7

По умолчанию, многие компоненты программного ядра Windows отключены. Сделано это для того, чтобы не перегружать систему (как увеличить быстродействие компьютера)-...

Закрыть