Как я уже писал ранее, профессиональная работа в Excel рано или поздно приведет вас к необходимости написания собственных функций. Довольно часто пользователи «раскрашивают» ячейки в разные цвета.


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

Как я уже
писал ранее
͕ п
рофессиональная работа в
Excel

рано или поздно приведет вас к
необходимости

написания собственных функций͘

Довольно часто
пользователи
«раскрашивают» ячейк
и

в разные цвета͘ Если потом возникает необходимость
просуммировать значения в выделенных ячейках͕ то͕ к сожалению͕
в

Excel

нет такой
стандартной функции͘

Небольшой код
VBA

решит ваши проблемы͘ Для облегчения восприятия п
оследующего
материала откройте
Excel
-
файл

(
он запакован в
Zip
͕ так как на сайте размещение файлов͕
содержащих макросы запрещено)͘

1.

Убедитесь͕ что среди в
кладок на ленте
Excel
͕ присутствует «Разработчик»͗


2.

Если такой
закладки вы не видите͕ щелкните на кнопке
Office

в левом верхнем углу и
затем на кнопке «Параметры
Excel
»͗


В открывшемся окне «Параметры
Excel
» перейдите на вкладку «Основные» и поставьте
галочку в строке «Показывать меню Разработчик на ленте»͘ Нажмите
Ok


3.

Создайте на листе
Excel

диапазон со значениями͖ несколько ячеек раскрасьте͗


4.

Перейдите на вкладку Разработчик и щелкните на
Visual

Basic
:


5.

У вас откроется окно
VBA
͕ содержащее окно
VBAProject
:


6.

Если окна
VBAProject

нет на экране


щелкните

на

меню

View


Project Explorer
:


7.

Если вы хотите использовать

создаваемый код
VBA

в любом
Excel
-
файле͕ вам следует его
записать в
VBAProject
͕ относящийся к
Personal
.
xlsb
͘ Если вы хотите применять код только
в одном файле͕ с которым вы сейчас работаете͕ сохраните код в
VBAProject
`
е именно
этого файла (в нашем случае Сумма по цвету͘
xlsm
):


8.

Допустим͕ вы решили͕ что создаваемая функция
будет использоваться в
дальнейшем в
раз
личных файлах͘ Щелкните правой кнопкой мыши на
VBAProject
(
Personal
.
xlsb
) и
выберите
Ins
e
rt



Module


9.

Появится окно нового модуля͕ в которое следует перенести код͗

Function
СумЦвет

(
диапазон

As Range,
критерий

As Range) As
Doub
le

Application.Volatile True

Dim i As Range


For Each i In
диапазон


If i.Interior.Color

=
критерий
.Interior.Color Then


СумЦвет

=
Сум
Ц
вет

+ i.Value


End If


Next

End Function


Номер вашего модуля (у меня он 5) будет зависеть от числа ранее созданных
модулей͘

Несколько слов о коде͗

Function

СумЦвет

(
диапазон

As

Range
,
критерий

As

Range
)
As

Doub
le

/
Задает пользовательскую
функция под названием СумЦвет

с двумя параметрами͗ диапазоном суммирования и
критерием


ячейкой͕ по цвету которой определяется͕ значения в каких ячейках
суммировать͘

Application
.
Volatile

True

/
Заставит нашу функцию пересчитываться при любом изменении
значения в любой из ячеек на
листе

Dim

i

As

Range

/
Определяет

переменную

i

как

диапазон

ячеек

Далее следует цикл͗


For

Each

i

In

диапазон

/ Для всех ячеек из выбранного диапазона


If

i
.
Interior
.
Color

=
критерий
.
Interior
.
Color

Then

/ Если цвет
ячейки
совпадает с критерием


СумЦвет

=
Сум
Ц
вет

+
i
.
Value

/ то добавляем
значение
͕ хранящееся в

ячейк
е

в сумму


End If


Next

10.

Вы создали пользовательскую функцию СумЦвет͕ которую можно найти в категории
«Определенные пользователем»



11.

Окно мастера функции выглядит также͕ как и для стандартной функции
Excel


Хочу обратить ваше внимание на две особенности функции СумЦвет͗



При изменении значения в одной из ячеек происходит автоматический пересчет
значения функции СумЦвет͘
Если вы поменяли
только
цвет ячейки
͕ автоматически
й

пересч
е
т

не произойдет
͘ Нажмите
F
9
.



Функция не работает͕ если ячейки раскрашены с помощью условного
форматирования


См͘
Excel͘ Подсчет и суммирование ячеек͕ о
твечающих критерию
условного форматирования


На основании комментариев
͕ появившихся после первой публикации заметки͕

добавил в
Excel
-
файл код функци
й͗



КолЦвет


определяет число

ячеек выделенного цвета



СумНеЦвет


определяет сумму значений в ячейках выдел
енных любым цветом (не
белого цвета)



КолНеЦвет


определяет число выделенных ячеек (не белых)



Приложенные файлы

  • pdf 44396445
    Размер файла: 827 kB Загрузок: 0

Добавить комментарий