Электронные таблицы для составления бюджета - мощный инструмент для управления финансами, но с учетом всех данных и расчетов они могут быстро стать непосильными. К счастью, Google Sheets предлагает ряд формул, которые могут упростить процесс составления бюджета. Эти формулы помогут вам отслеживать расходы, управлять доходами и достигать поставленных финансовых целей.
1 AVERAGEIF
Функция AVERAGEIF помогает рассчитать среднее значение группы чисел в диапазоне ячеек, отвечающих определенным критериям. Синтаксис этой функции следующий:
[range] - диапазон ячеек, которые необходимо оценить, [criterion] - условие, которое должно быть выполнено, и [average_range] - диапазон ячеек для усреднения.
Предположим, вы решили создать электронную таблицу бюджетирования в которой отслеживаются различные расходы с указанием соответствующих дат, как в примере ниже.
Чтобы найти среднюю сумму, которую вы тратите на продукты, где «Продукты» указаны в столбце A, а сумма - в столбце B, используйте формулу:
Исходя из данных примера, средняя сумма, потраченная на продукты, составляет 150 долларов.
2 SUMIF
SUMIF позволяет суммировать значения в диапазоне, удовлетворяющем определенным критериям. Синтаксис функции SUMIF следующий:
Где range - диапазон ячеек для оценки, criterion - условие, которому нужно соответствовать, а [sum_range] - диапазон ячеек для суммирования.
Если вы хотите суммировать расходы на продукты, используйте формулу:
В данном случае общая сумма, потраченная на продукты, составляет 450 долларов, исходя из данных примера.
3 COUNTIF
С помощью COUNTIF можно подсчитать количество ячеек в диапазоне, удовлетворяющих определенным критериям. Это облегчает отслеживание частоты определенных расходов.
Синтаксис функции COUNTIF следующий:
где range - диапазон ячеек для подсчета, а criterion - условие, которое должно быть выполнено.
Чтобы подсчитать, сколько раз вы ходили в магазин за продуктами, используйте формулу:
Исходя из данных примера, формула возвращает 3, что означает, что я делал покупки продуктов три раза. Если необходимо ввести несколько критериев, можно использовать функцию COUNTIFS.
4 IFS
IFS - это более сложная функция, позволяющая проверять несколько условий. Она полезна для категоризации расходов на основе различных критериев.
Синтаксис функции IFS следующий:
Условие1 - первое условие для оценки, а значение_если_истина1 - результат, если это условие истинно. Вы можете добавить больше условий и соответствующих результатов.
Если вы хотите распределить расходы по категориям на основе сумм, используйте формулу:
По этой формуле каждый расход будет отнесен к категории «Низкий», «Средний» или «Высокий» в зависимости от его суммы. Например, покупка продуктов на 150 долларов будет отнесена к категории высоких, как показано на скриншоте ниже.
5 ТЕКСТ
Функция Функция ТЕКСТ форматирует числа в виде текста, что удобно для отображения чисел в удобочитаемом виде (например, для отображения валюты или процентов).
Синтаксис функции TEXT следующий:
где value - число, которое нужно отформатировать, а format_text - желаемый формат (например, валюта или процент).
Чтобы отобразить число в виде валюты, используйте формулу:
Поскольку ячейка B2 содержит 150, формула TEXT отображает его как $150.00, чтобы пояснить, что значение ячейки представляет собой деньги.
6 ИНДИРЕКТ
Функция INDIRECT позволяет динамически ссылаться на ячейки, преобразуя текстовую строку в ссылку на ячейку. Это позволяет обновлять формулы на основе изменяющихся данных, например, разных листов или диапазонов в таблице.
Синтаксис функции INDIRECT следующий:
где ref_text - ссылка, представленная в виде текста, а [a1] - необязательный аргумент, указывающий, какой стиль должна использовать ссылка - A1 (TRUE) или R1C1 (FALSE). По умолчанию для [a1] устанавливается стиль A1 (TRUE).
Предположим, у вас есть ссылка на ячейку в текстовом формате (например, «B2»), и вы хотите преобразовать ее в фактическую ссылку на ячейку. Для этого используйте формулу:
Эта формула возвращает значение в ячейке B2, которое равно $100.
Теперь предположим, что в рабочей книге «Бюджет» есть несколько листов, каждый из которых представляет собой отдельный месяц (например, «Январь», «Февраль», «Март»). Например, ниже приведен снимок экрана, на котором показаны примерные данные для ежемесячных расходов в марте:
Общая сумма указана в ячейке B7 и составляет 1 775 долларов.
Допустим, вы хотите создать сводный лист, который отображает месяцы в столбце A и динамически подтягивает общие расходы за любой месяц и записывает их в столбец B. Ниже приведен пример того, как будет выглядеть такой лист.
Теперь предположим, что вам нужно динамически извлечь общую сумму расходов из ячейки B7 в листе «Март» и отобразить ее в сводном листе. Вот как можно использовать для этого функцию INDIRECT:
В этом примере A4 относится к ячейке сводного листа, содержащей имя листа, из которого вы хотите извлечь данные (например, «Март»), а формула динамически ссылается на ячейку B7 из листа «Март», которая содержит общую сумму расходов за месяц. Амперсанд ( & ) используется для конкатенации или соединения текстовых строк вместе.
В данном случае она сочетает одинарную кавычку (') для имен листов с пробелами, имя листа из A4 и ' !B7 ', где восклицательный знак ( ! ) отделяет имя листа от ссылки на ячейку.
На снимке экрана видно, что при использовании формулы ИНДИРЕКТ общие ежемесячные расходы за март составили 1 775 долларов. Это соответствует общей сумме ежемесячных расходов на листе «Март». Интересно, что при изменении значения в A4 на «Февраль» автоматически обновляется ссылка на лист «Февраль».
7 ФИЛЬТР
Функция ФИЛЬТР позволяет отфильтровать диапазон данных на основе определенных условий, что упрощает выделение определенных расходов или категорий.
Синтаксис функции ФИЛЬТР следующий:
Где range - это данные, которые вы хотите отфильтровать, а condition1 и condition2 - условия, которым должны удовлетворять данные.
Вот как использовать функцию FILTER чтобы отфильтровать только расходы на бакалею, используя данные нашего примера:
Введите формулу в новый столбец. В моем примере я ввел ее в ячейку E2 в столбце E . Формула вернет только те строки, в которых столбец A содержит «Бакалея», что позволит вам сосредоточиться на расходах на продукты, не отвлекаясь на посторонние дела.
8 XLOOKUP
XLOOKUP - это универсальная функция, которая ищет совпадения в заданном диапазоне и возвращает соответствующее значение. Она идеально подходит для поиска расходов или категорий.
Синтаксис функции XLOOKUP следующий:
где lookup_value - значение для поиска, lookup_array - диапазон для поиска, return_array - диапазон для возврата результата. Дополнительные параметры позволяют определить, что делать, если совпадение не найдено, и как обрабатывать критерии совпадения.
Предположим, у вас есть бюджетная таблица, в которой различные расходы отражаются по категориям в столбце A, суммам расходов в столбце B и названиям статей в столбце C. Теперь вы хотите определить, сколько вы потратили на конкретный продукт, например овощи.
В этом вам поможет функция XLOOKUP:
В этом примере формула вернула 150 долларов - сумму, потраченную именно на овощи.
Все формулы, которые мы рассмотрели, могут изменить ваш процесс составления бюджета и облегчить эффективное управление финансами. Опробуйте их сегодня и узнайте, как они могут упростить ваши задачи по составлению бюджета.
Вы также можете читать меня в:
- Telegram: https://t.me/gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru
