Простой способ отслеживать расходы при помощи новых функций в Excel 2007

  • Article

Простой способ отслеживать расходы при помощи новых функций в Excel 2007

Сегодняшний автор, Моника Поинеску, разработчик программ для тестирования ПО в команде Excel, представляет обзор нескольких новых функций в Excel 2007 и рассказывает, как отслеживать расходы без лишних усилий.

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

Предположим, я ввела суммы расходов в таблицу:

clip_image002

Решение, приведенное ниже, использует новую особенность Excel 2007 - структурированные ссылки; об упрощении табличных формул см. «Tables Part 3: Using Formulas with Tables».

Возможности формулы SUMIFS (СУММЕСЛИМН) шире, чем у SUMIF (СУММЕСЛИ): в нужном диапазоне формула будет складывать только те значения, которые удовлетворяют всем заданным критериям (к соответствующим диапазонам можно применять от 1 до 127 критериев).

Синтаксис следующий: SUMIFS (диапазон суммирования, диапазон, удовлетворяющий критерию 1, критерий 1, [диапазон, удовлетворяющий критерию 2, критерий 2], … ), где первый параметр указывает программе на диапазон, в котором необходимо выполнить суммирование, а следующие две пары аргументов определяют, в каком диапазоне применять соответствующее условие.

В качестве наглядного примера представьте каждую пару (диапазон, удовлетворяющий критерию 1, критерий 1) в качестве пачки карточек с несколькими (от 0 и более) отверстиями там, где критерий ИСТИНЕН. Сложите все карточки друг на друга так, чтобы диапазон суммирования находился в самом низу, и SUMIFS просуммирует только те значения, которые остались видны.

Заполняем отчетную таблицу, вводя в ячейки F1:I3 категории и месяцы:

clip_image004

И в ячейке G2 вводим:

=SUMIFS( Table1[Amount], Table1[Category], G$1, Table1[Date], $F2 ),

что даст нам сумму расходов на дом за январь (235.35).

Таблица будет выглядеть следующим образом:

clip_image006

Подобным образом, в ячейке H2 вводим:

=SUMIFS( Table1[Amount], Table1[Category], H$1, Table1[Date], $F2 ),

что даст нам сумму расходов на питание за январь (235.35).

И в I2 вводим:

=SUMIFS( Table1[Amount], Table1[Category], I$1, Table1[Date], $F2 ),

чтобы получить сумму расходов, затраченных на путешествия.

Эти формулы можно скопировать и вставить при подсчете расходов за очередной месяц. Формула автоматически учтет новые записи в списке расходов. Кроме того, для подсчета средней суммы расходов по категориям за каждый месяц можно использовать формулу:

=AVERAGEIFS( Table1[Amount], Table1[Category], "home", Table1[Date], "January" ) – результат составляет 117.675.

Опубликовано: Джозефом Чириловым (Joseph Chirilov)

Перевод:
Михаил Даньшин
https://danshin.ms