ЗДРАВСТВУЙТЕ, ДРУЗЬЯ!

НА СВЯЗИ ИЛЬЯ ГРИШИН – АВТОР И ВЕДУЩИЙ ПРОЕКТА «БЮДЖЕТИРОВАНИЕ И УПРАВЛЕНЧЕСКИЙ УЧЁТ В EXCEL».
Фишки Excel - Функция СуммЕсли


Мы продолжаем наш разговор о приёмах, которые могут оказаться полезны при ведении учёта в Excel. Сегодня я расскажу Вам про функцию, которая называется «СуммЕсли». Эта функция полезна, если Вам нужно подставить данные из одной таблицы в другую. Второе назначение этой функции – быстраявыборка данных из таблицы по определённому критерию.
Для начала давайте рассмотрим общие принципы работы функции СуммЕсли.
В ячейку Excell функция записывается в виде: =СуммЕсли(Диапазон поиска;Критерий Отбора; Диапазон Суммирования).

Если описать работу функции в двух словах, то Excel выбирает из Диапазона Поиска ячейки, значения в которых соответствуют Критерию Отбора и суммирует числа из ячеек Диапазона Суммирования, которые находятся напротив найденных ячеек из Диапазона Поиска.

Гораздо проще объяснить это на примере. Пускай у нас в таблице у нас есть список фруктов и напротив каждой позиции стоит их количество. Используем функцию СуммЕсли. Зелёным цветом на рисунке обозначен Диапазон Поиска, Оранжевым – Критерий и Голубым – ДиапазонСуммирования.

Теперь попробуем в поле «Критерий», которое обозначено оранжевым цветом напечатать «Яблоки»
Мы видим, что программа выбрала количество, которое стоит напротив каждого слово «Яблоки» и сложила их.

Теперь подставим в критерий слово «Груши». Вот что получится:
Скачать файл с этим примером Вы можете здесь. Однако баловаться Excel'ем можно до бесконечности, но как применить эту функцию на практике?

Давайте возьмём тот же самый файл, который мы сделали в прошлый раз (смотрите прошлую заметку).

В этом файле у нас есть таблица товаров, в которую занесены название товара и его цена.

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

В прошлый раз мы добились того, что программа сама выбирает данные из списка, а теперь мы научим её подставлять цену. Как Вы уже догадались – сделаем мы это с помощью функции «СуммЕсли».

Для начала на листе товары создадим список «Цены» (создание списков мы с Вами рассматривали в прошлой заметке).
Теперь на листе продаж введём формулу СуммЕсли:
Теперь при выборе наименования товара в таблице «Учёт – его цена будет проставляться автоматически с листа «Товары».

Это уже экономит нам время и снижает вероятность ошибки, но и это ещё не всё. Функция СуммЕсли очень удобна для построения отчётов. Но перед тем как продолжить – давайте заполним таблицу «Учёт продаж»
После этого создадим на этом листе списки «ПродажиМенеджер»
И далее по колонкам «ПродажиПокупатель» «ПродажиТовар» «ПродажиКоличество» «ПродажиЦена» «ПродажиСумма». О создании списков я подробно рассказывал в одной из предыдущих статей.

В принципе можно обойтись и без списков, напрямую ссылаясь на диапазон ячеек. Но в этом случае при тиражировании или перетаскивании формулы (например мы хотим «транслировать» формулу на насколько ячеек вниз) диапазон ячеек будет смещаться, а список – нет. Конечно диапазон можно зафиксировать с помощью значка "$", но всё-таки список в данном случае удобнее.

А теперь создадим в нашей книге новый лист, который назовём «Простые отчёты» (в одном из следующих выпусков я покажу Вам более сложные и интересные отчёты). На листе сделаем четыре отчёта:

1. Динамическая выборка

2. Отчёт по менеджерам

3. Отчёт по товарам

4. Отчёт по покупателям

Для начала в динамическом отчёте сделаем ссылку на менеджера

Сейчас менеджера можно выбирать из списка. Теперь в следующую ячейку введём функцию СуммЕсли – в качестве Диапазона поиска выберем список «ПродажиМенеджеры», в качестве критерия укажем ячейку, в которой будем выбирать менеджера, а в качестве Диапазона суммирования выберем список «ПродажиСумма». Вот что из этого получится:
Теперь при выборе менеджера мы получаем в этой ячейке сумму его продаж:
Потом ту же самую операцию проделаем для Товаров и для Покупателей.

Теперь сделаем Отчёт по менеджерам

Для этого в формуле СуммЕсли в качестве диапазона поиска выберите «ПродажиМенеджер», в качестве критерия ячейку с менеджером, а в качестве диапазона суммирования «ПродажиСумма».

Очень удобная штучка. Но главное достоинство функции СуммЕсли не в этом. В данном случае можно сослаться на ячейки напрямую:
Тогда мы получим формулу следующего вида:
Но эта формула ненадёжна. Давайте удалим на листе «Учёт продаж» строку № 9.
После этого посмотрите – что произошло в отчёте.
Стандартная формула «развалилась», а функция СуммЕсли продолжает работать. Теперь осталось только «протянуть» формулу вниз – и отчёт готов!
Точно так же составляются отчёты по покупателям и по товарам. А чтобы Вам было легче разобраться – я выкладываю файл для скачивания.
И напоследок.

В своих статьях я показываю лишь мизерную часть возможностей Excel. Однако, применив только этот инструмент в своей работе, вы уже сэкономите достаточно времени и сил, чтобы увидеть результат. А если вы хотите сделать большой рывок и вывести свою работу на новый, осознанный уровень, хотите научится собирать любые управленческие отчёты любой сложности буквально в 2 клика, то предлагаю вашему вниманию мой флагманский видеокурс "Бюджетирование и управленческий учёт в Excel".

Посмотреть подробное описание тренинга можно на сайте

Задать интересующие вопросы можно по телефону 8 800 201-49-35.