Простой способ отслеживать расходы при помощи новых функций в Excel 2007
Сегодняшний автор, Моника Поинеску, разработчик программ для тестирования ПО в команде Excel, представляет обзор нескольких новых функций в Excel 2007 и рассказывает, как отслеживать расходы без лишних усилий.
Текущая экономическая ситуация могла многих подтолкнуть к мысли о более тщательном контроле расходов. Новые функции в Office Excel 2007 упрощают эту задачу. Как? Следующим образом: имеется список расходов по категориям и датам (еда, путешествия, одежда и т.д.), необходимо узнать, каковы затраты по каждой из категорий в месяц. Функции SUMIFS (СУММЕСЛИМН), AVERAGEIFS (СРЗНАЧЕСЛИМН) и COUNTIFS (СЧЁТЕСЛИМН) выполняют выборочные вычисления: они учитывают только те значения, которые удовлетворяют нескольким критериям. Значения могут принадлежать области, охватывающей несколько строк и колонок.
Предположим, я ввела суммы расходов в таблицу:
Решение, приведенное ниже, использует новую особенность Excel 2007 - структурированные ссылки; об упрощении табличных формул см. «Tables Part 3: Using Formulas with Tables».
Возможности формулы SUMIFS (СУММЕСЛИМН) шире, чем у SUMIF (СУММЕСЛИ): в нужном диапазоне формула будет складывать только те значения, которые удовлетворяют всем заданным критериям (к соответствующим диапазонам можно применять от 1 до 127 критериев).
Синтаксис следующий: SUMIFS (диапазон суммирования, диапазон, удовлетворяющий критерию 1, критерий 1, [диапазон, удовлетворяющий критерию 2, критерий 2], … ), где первый параметр указывает программе на диапазон, в котором необходимо выполнить суммирование, а следующие две пары аргументов определяют, в каком диапазоне применять соответствующее условие.
В качестве наглядного примера представьте каждую пару (диапазон, удовлетворяющий критерию 1, критерий 1) в качестве пачки карточек с несколькими (от 0 и более) отверстиями там, где критерий ИСТИНЕН. Сложите все карточки друг на друга так, чтобы диапазон суммирования находился в самом низу, и SUMIFS просуммирует только те значения, которые остались видны.
Заполняем отчетную таблицу, вводя в ячейки F1:I3 категории и месяцы:
И в ячейке G2 вводим:
=SUMIFS( Table1[Amount], Table1[Category], G$1, Table1[Date], $F2 ),
что даст нам сумму расходов на дом за январь (235.35).
Таблица будет выглядеть следующим образом:
Подобным образом, в ячейке 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)
Перевод: Екатерина Бондаренко, Михаил Даньшин
Комментариев нет:
Отправить комментарий