Как сделать формулу в excel если сумма

Рубрика: 7. Полезняшки Excel

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

Небольшой код VBA решит ваши проблемы. Для облегчения восприятия последующего материала откройте Excel-файл (он запакован в Zip, так как на сайте размещение файлов, содержащих макросы запрещено).

1. Убедитесь, что среди вкладок на ленте Excel, присутствует «Разработчик»:

Рис. 1. Вкладка Разработчик на ленте Excel

Скачать заметку Сумма по цвету в формате Word

Скачать заметку Сумма по цвету в формате pdf

Скачать файл примера в формате Excel с поддержкой макросов Сумма по цвету. На основании комментариев добавил в Excel-файл код функции КолЦвет  — определяет число ячеек выделенного цвета, СумНеЦвет — определяет сумму значений в ячейках выделенных любым цветом (не белого цвета)

2. Если такой закладки вы не видите, щелкните на кнопке Officeв левом верхнем углу и затем на кнопке «Параметры Excel»:

Рис. 2. Вызов окна Параметры Excel

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

Рис. 3. Опция Показать вкладку Разработчик на ленте

4. Создайте на листе Excel диапазон со значениями; несколько ячеек раскрасьте:

Рис. 4. Фрагмент листа Excel с данными и раскрашенными ячейками

5. Перейдите на вкладку Разработчик и щелкните на VisualBasic:

Рис. 5. Запуск Visual Basic

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

Рис. 6. Запуск VBAProject

7. Если окна VBAProjectнет на экране

Рис. 7. Если окна VBAProject нет на экране

щелкните на меню View — Project Explorer:

Рис. 8. Щелкните на меню View - Project Explorer

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

Рис. 9. Выбор файла для хранения кода VBA

9. Допустим, вы решили, что создаваемая функция будет использоваться в дальнейшем в различных файлах. Щелкните правой кнопкой мыши на VBAProject(Personal.xlsb) и выберите Insert — Module

Рис. 10. Вставить модуль для хранения кода VBA
Появится окно нового модуля, в которое следует перенести код:

Рис. 11. Программный код

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

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

Function СумЦвет (диапазон AsRange, критерий AsRange) AsDouble/ Задает пользовательскую функция под названием СумЦвет с двумя параметрами: диапазоном суммирования и критерием – ячейкой, по цвету которой определяется, значения в каких ячейках суммировать.

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

Dim i AsRange/ Определяет переменную i, как диапазон ячеек

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

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

If i.Interior.Color = критерий.Interior.Color Then/ Если цвет ячейки совпадает с критерием

СумЦвет = СумЦвет + i.Value/ то добавляем значение, сумма хранящееся в ячейке в сумму

End If

Next

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

Рис. 12. Запуск функции, определенной пользователем
11. Окно мастера функции выглядит также, как и для стандартной функции Excel

Рис. 13. Окно мастера функции СумЦвет

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

  • При изменении значения в одной из ячеек происходит автоматический пересчет значения функции СумЦвет. Если вы поменяли только цвет ячейки, автоматический пересчет не произойдет. Нажмите F9.
  • К сожалению, функция не работает, если ячейки раскрашены с помощью условного форматирования. Это ограничение можно преодолеть, если применить правила, использованные в условном форматировании, в функциях СУММЕСЛИ и СУММЕСЛИМН. Подробнее см. Подсчет и суммирование ячеек, отвечающих критерию условного форматирования

На основании комментариев, появившихся после первой публикации заметки, добавил в Excel-файл код функций:

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

Источник: http://baguzin.ru/wp/excel-summirovanie-po-yachejkam-vydelennym/

Поделись с друзьями



Рекомендуем посмотреть ещё:


Закрыть ... [X]

Сумма прописью в Excel Эксель Практик Решили конкурс красоты



Как сделать формулу в excel если сумма Формулы массива в Excel - Планета Excel
Как сделать формулу в excel если сумма Как сделать справочник в Excel
Как сделать формулу в excel если сумма Сумма по цвету ячеек в Excel
Как сделать формулу в excel если сумма Как сделать таблицу в Excel
Как сделать формулу в excel если сумма Cached
Как сделать формулу в excel если сумма Royal Thai - СПА салон тайского массажа в Санкт-Петербурге
Как сделать формулу в excel если сумма Бык по китайскому гороскопу - Астрология
Как сделать формулу в excel если сумма Вумбилдинг в домашних условиях для начинающих. Упражнения с
Вязание спицами для детей (от 0 до года, до 3 лет и старше) Детские стрижки для мальчиков 2017: фото и модные тренды Как выбрать солнцезащитный крем для лица? Женские Вопросы Карьера в СФУ Сибирский федеральный университет Места лишения свободы - m Пластические операции участников реалити-шоу «Дом-2

ШОКИРУЮЩИЕ НОВОСТИ