Пример 4. Выборочное суммирование

Посмотрите как при помощи одной формулы массива красиво и легко выбираются данные по определенному товару и заказчику:

В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

Вычисление нужной даты

В некоторых случаях события могут быть назначены не на конкретную дату, а привязаны к определенному дню недели заданного месяца и года - например:

· первый понедельник января 2007 года - самый тяжелый понедельник года

· второе воскресенье апреля 2011 года - день ПВО

· первое воскресенье октября 2012 года - день Учителя

· и т.д.

Чтобы определить точную дату, на которую приходится такой день недели, нам потребуется небольшая, но хитрая формула:

=ДАТА(B1;B2;1)+B3-ДЕНЬНЕД(ДАТА(B1;B2;1);2)+(B4-(B3>=ДЕНЬНЕД(ДАТА(B1;B2;1);2)))*7

в англоязычной версии это будет

=DATE(B1;B2;1)+B3-WEEKDAY(DATE(B1;B2;1);2)+(B4-(B3>=WEEKDAY(DATE(B1;B2;1);2)))*7

При использовании этой формулы предполагается, что

· B1 - год (число)

· B2 - номер месяца (число)

· B3 - номер дня недели (Пн=1, Вт=2 и т.д.)

· B4 - порядковый номер дня недели, который вам нужен

· Выборочное суммирование по двум критериям

· Постановка задачи

· Имеем таблицу по продажам, например, следующего вида:

·

· Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".

· Способ 1. Функция СУММЕСЛИ (SUMIF)

· Если бы в нашей задаче было только одно условие (все заказы Григорьева или все заказы в "Копейку"), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ из категории Математические. О том, как ее использовать можно почитать здесь. Но в нашем случае имеются два условия, а не одно, поэтому этот способ не подходит...

· Способ 2. Столбец-индикатор

· Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1 иначе 0. Формула, которую надо ввести в этот столбец очень простая:

· =(A2="Копейка")*(B2="Григорьев")

· Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать полученные суммы:

·

· Способ 3. Волшебная формула массива

· Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну а в нашем случае задача решается одной формулой:

· =СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)

·

· После ввода этой формулы необходимо нажать не Enter, как обычно, а Ctrl + Shift + Enter - тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.

· Способ 4. Функция баз данных БДСУММ

· В категории Базы данных можно найти функцию БДСУММ (DSUM), которая также может помочь нам решить нашу задачу:

·

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

· =БДСУММ(A1:D26;D1;F1:G2)

·

· Способ 5. Мастер Частичной Суммы

· Так называется надстройка Excel, которая помогает создавать сложные формулы для многокритериального суммирования. Подключить эту бесплатную надстройку можно через меню Сервис - Надстройки - Мастер суммирования (Tools - Add-Ins - Conditional Sum Wizard). После этого в меню Сервис должна появится команда Частичная сумма, запускающая Мастер суммирования:

·

· На первом шаге Мастера суммирования необходимо указать диапазон с данными, т.е. выделить всю нашу таблицу. На втором шаге нужно выбрать столбец для суммирования и сформировать условия для отбора значений, добавляя каждое условие в список кнопкой Добавить условие:

·

· И, наконец, на 3-м и 4-м шагах указываем ячейку, куда необходимо вывести результат. И получаем в итоге следующее:

·

· Легко заметить, что нечто похожее на эту формулу массива мы использовали в Способе 3. Только здесь можно к клавиатуре вообще не прикасаться - да здравствует лень - двигатель прогресса!

· Способ 6. Если у вас Excel 2007...

·... то все вышеописанные танцы с бубном становятся не нужны, потому как в последней версии Excel 2007 появились функции СУММЕСЛИМН (SUMIFS), СЧЁТЕСЛИМН(COUNTIFS) и даже СРЗНАЧЕСЛИМН(AVERAGEIFS), которые умеют считать по нескольким (до 128!) условиям. Подробнее про них можно почитать в обзоре новых возможностей Excel 2007.

Разные типы диаграмм "в одном флаконе"

Имеем таблицу с большим количеством информации. Например, такую:

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

Поехали...

1. Сначала выделяем данные, которые должны попасть в диаграмму (зеленые ячейки).

2. Далее идем, как обычно, в меню Вставка - Диаграмма (Insert - Chart)

3. На первом шаге Мастера диаграмм оставляем самый простой тип диаграммы - плоская гистограмма (выбран по-умолчанию) и жмем Готово.

4. Дальше самое интересное: если щелкнуть левой кнопкой мыши по любому из столбцов, например, прибыли, то все столбцы, имеющие отношение к прибыли, выделяются (появляются черные квадратные маркеры). Если теперь щелкнуть туда же правой кнопкой мыши и выбрать в контекстном меню Тип диаграммы, то мы будем менять тип уже не всей диаграммы вцелом, а только выделенных элементов:

То есть можно легко превратить голубые столбцы в график или круговую диаграмму.

Но есть две хитрости:

· если выбрать тип Гистограмма, то вся диаграмма (а не только выделенные элементы) превратится в плоские столбцы, поэтому столбцы для выделенных элементов надо делать первыми и только потом задавать другие типы диаграмм;

· некоторые типы диаграмм (например, плоские и объемные) не сочетаются - Excel честно предупредит Вас об этом.

Так что в итоге можно получить, например, вот такой шедевр:

Условное форматирование

Дата добавления: 2014-01-05