Как в экселе сделать сумму всех строк

Ранее я описал, как с помощью пользовательской функции найти сумму значений в ячейках, выделенных цветом. К сожалению, эта функция не работает, если ячейки раскрашены с помощью условного форматирования. Я обещал «доработать» функцию. Но за два года, прошедшие с публикации той заметки, я не смог ни самостоятельно, ни с помощью информации из Интернета написать удобоваримый код… (Дополнение от 29 марта 2017 г. Спустя еще пять лет, код мне всё же удалось написать; см. заключительную часть заметки). И вот недавно я наткнулся на идею, содержащуюся в книге Д.Холи, Р. Холи «Excel 2007. Трюки», которая позволяет обойтись вовсе без кода.

Пусть есть список чисел от 1 до 100, размещенных в диапазоне А1:А100 (рис. 1; см. также лист «СУММЕСЛИ» Excel-файла) [1]. На диапазон наложено условное форматирование, помечающее ячейки, содержащие числа больше 10 и меньше или равно 20.

Рис. 1. Диапазон чисел; условным форматированием выделены ячейки, содержащие значения от 10 до 20

Скачать заметку в формате Word, примеры в формате Excel

Теперь необходимо сложить значения в ячейках, отвечающих только что установленному критерию. Неважно, какое именно форматирование применяется к этим ячейкам, однако необходимо знать критерий, согласно которому ячейки выделяются.

Чтобы сложить диапазон ячеек, отвечающих одному критерию, можно использовать функцию СУММЕСЛИ (рис. 2).

Рис. 2. Суммирование ячеек, отвечающих одному условию

Если у вас несколько условий, можно использовать функцию СУММЕСЛИМН (рис. 3).

Рис. 3. Суммирование ячеек, отвечающих нескольким условиям

Для подсчета числа ячеек, отвечающих одному критерию, можно использовать функцию СЧЁТЕСЛИ.

Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию СЧЁТЕСЛИМН.

В Excel предусмотрена еще одна функция, которая позволяет указать несколько условий. Эта функция входит в набор функций баз данных Excel и называется БДСУММ. Чтобы проверить ее, используйте тот же набор чисел в диапазоне А2:А100 (рис. 4; см. также лист «БДСУММ» Excel-файла).

Рис. 4. Использование функций баз данных

Выделите ячейки C1:D2 и присвойте этому диапазону имя Критерий, введя его в поле имени слева от строки формул. Теперь выделите ячейку С1 и введите =$А, то есть ссылку на первую ячейку на листе, содержащую имя базы данных. Введите =$А в ячейку D1 и вы получите две копии заголовка столбца А. Эти копии будут использоваться как заголовки для условий БДСУММ (C1:D2), который вы назвали Критерий. В ячейке С2 введите >10. В ячейке D2 введите <=20. В ячейке, где должен быть результат, введите следующую формулу:

=БДСУММ($А:$А1,1,Критерий)

Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию БСЧЁТ.

Дополнение от 29 марта 2017 г.

Читая книгу Джона Уокенбаха Excel 2010. Профессиональное программирование на VBA я узнал, что, начиная с версии Excel 2010 в VBA появилось новое свойство DisplayFormat (см., например, Range.DisplayFormat Property). Т.е., VBA может считывать формат, отображаемый на экране. При этом не важно, как он был получен, прямыми настройками пользователя, или с помощью условного форматирования. К сожалению, разработчики MS сделали так, что свойство DisplayFormat работает только в процедурах, вызываемых из VBA, а пользовательские функции на основе этого свойства выдают ошибку #ЗНАЧ! Тем не менее, получить сумму значений в диапазоне по ячейкам определенного цвета, можно с помощью процедуры (макроса, но не функции). Откройте Excel-файл с примером (содержит код VBA). Пройдите по меню Вид -> Макросы -> Макросы; в окне Макрос, выделите строку СумЦветУсл, и нажмите Выполнить. Запуститься макрос, выберите диапазон суммирования и критерий. Ответ появится в окне.

Код процедуры

Хотя пользовательская функция и дает ошибку, но в процессе ее вызова можно «подсмотреть» ответ. В ячейке начните набирать формулу =su…

Воспользуйтесь подсказкой, кликнув на нее, а затем нажмите знак функции в строке формул:

Введите аргументы, и увидите ответ. К сожалению, нажав, ОК, получите в ячейке значение ошибки.

[1] Массив я создал с помощью функции =СЛУЧМЕЖДУ(1;100)


Источник: http://baguzin.ru/wp/?p=4042

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

MS Excel 2010 почему не считается сумма столбцов Как сделать мед орехи лимон

Как в экселе сделать сумму всех строк Вставка в отфильтрованные строки
Как в экселе сделать сумму всех строк Выпадающий список в ячейке листа
Как в экселе сделать сумму всех строк Путь воина » Полезняшки Excel
Как в экселе сделать сумму всех строк Работа со строками в Excel - m
Как в экселе сделать сумму всех строк Cached
Как в экселе сделать сумму всех строк 100 лучших идей дизайна: люстры для зала на фото
Как в экселе сделать сумму всех строк S что это за файл и как его удалить?
Windows cервер для синхронизации времени в локальной сети Вечерние прически на короткие волосы с челкой и Делайте с нами своими руками Как восстановить страницу в одноклассниках - восстановление Как сделать петли из ниток? - Милла Сидельникова Мастерим красивые поделки из шишек своими руками. Описание Почему конфеты называются Радий? - Мастерок. жж.рф Проверить, снять порчу. - Ezoterik. org Сад и огород своими руками, цветы на даче, полезные советы