В одном из предыдущих проектов мы помогли студенту накопить на новый телефон с помощью Excel. Мы воспользовались очень простыми возможностями Экселя, а теперь прокачаем его:
- добавим подсветку,
- банковские счета
- и учёт процентов.
Всё для того, чтобы накопить ещё быстрее.
Исходные данные
Условия были такие:
Московский студент батрачит в фастфуде после занятий и получает 20 000 рублей в месяц, а каждые три месяца ещё получает премию в 3000 рублей. На комнату у него уходит 5000 рублей в месяц, а на еду и развлечения он тратит ровно половину от всего заработанного за месяц. Всё, что осталось, он откладывает на Айфон, который стоит 50 000 рублей. Получится ли у него купить его через год или раньше, если он дополнительно будет тратить по 2000 рублей на качалку каждые 2 месяца?
Всё это мы уже рассчитали, поэтому сегодня будем улучшать так:
- добавим поле ввода стоимости телефона;
- будем подсвечивать зелёным цветом ту ячейку, в которой сработали наши условия (когда денег хватает на телефон);
- откроем счёт в банке и будем отправлять деньги туда, чтобы капающие проценты помогали нам накопить быстрее.
Для этого используем наш старый файл — будем работать в нём.
Добавим стоимость телефона
Так как начальная цена телефона нам нужна только один раз, мы её добавим не в отдельный столбец, а запишем после декабря:
Как накопить на айфон с помощью Экселя
Как научить Эксель самому находить деньги на телефон
Как заставить Эксель думать за тебя
5 полезных функций Excel для начинающих программистов
Купить или сделать самому?
Находим самые популярные слова в опросах
6 полезных функций для аналитики данных в Экселе
Крутые штуки в Excel, о которых вы не зналиПодсветка ячеек
Чтобы сразу понимать, в каком месяце будет нужная сумма, добавим подсветку ячеек. С ней таблица выглядит гораздо нагляднее — проще находить нужные данные, сразу видно, куда нужно смотреть. Для этого мы используем функцию «Условное форматирование» — она смотрит, подходят ли наши ячейки под какие-то условия, и если подходят, то меняет их внешний вид.
Чтобы это заработало, нам нужно:
1. Выделить нужные ячейки (используем столбец «Накоплено»).
2. Нажать «Условное форматирование» и выбрать «Правила выделения ячеек» → «Другие правила»:
3. В правиле выбрать условие «Больше или равно», а затем выбрать нашу ячейку со стоимостью телефона:
4. Нажать «Формат», перейти в раздел «Заливка» и выбрать светло-зелёный цвет:
5. Нажимаем «ОК» и видим, что теперь ячейка с нужной нам суммой подсвечивается автоматически:
Банковский счёт
Правила будут такие:
- Всё, что накопилось за месяц, отправляем на счёт в банк.
- Исходим из того, что мы не платим за обслуживание счёта.
- Процентная ставка — 11% годовых, выплата процентов ежемесячно (это очень хорошая ставка, сейчас таких уже почти нет, но нам она нужна для наглядности).
- Открываем счёт с капитализацией — это значит, что все проценты прибавляются к основному вкладу и тоже увеличивают следующие проценты.
Чтобы было нагляднее, сделаем эту колонку рядом с «Остатком» — так будет удобнее сравнивать и оценивать эффективность вклада. Для этого сперва заводим отдельную ячейку со значением процентной ставки — сделаем это сразу под стоимостью телефона:
Теперь считаем результат по вкладу для первого месяца — он нам понадобится для автоматического расчёта всех остальных месяцев. Пишем в ячейке «Счёта» напротив января: «=RC[-2]+RC[-2]*R[15]C[-7]/12». Это значит, что мы взяли первую накопленную сумму и прибавили к ней набежавшие проценты за месяц (12 — потому что в году 12 месяцев):
Для февраля будет почти то же самое, но с одним изменением — теперь мы учитываем и предыдущую сумму остатка на счету. Пишем напротив февраля: «=R[-1]C+RC[-2]+(R[-1]C+RC[-2])*R17C2/12»
Вот что тут происходит:
- Мы складываем сумму остатка за январь с тем, что накопили в феврале.
- Затем берём эту же сумму и считаем проценты, которые набежали за месяц.
- Складываем эти две суммы.
Обратите внимание — адрес ячейки с процентной ставкой у нас записан без квадратных скобок. Это называется абсолютным адресом — он не зависит от позиции исходной ячейки и не поменяется, когда мы протянем ячейку вниз или вверх. Правило такое: то что в квадратных скобках — это относительные адреса, и они зависят от положения ячейки с формулой, а то что без скобок — жёстко стоит на месте.
Специально для банкиров и экономистов: мы знаем, что в реальности проценты начисляются не так просто, а проценты с внесённой суммы мы увидим только в расчётах следующего месяца. Но усложнять нужно постепенно.
Протянем эту ячейку до декабря:
Чтобы было нагляднее, добавим условное форматирование по цене — точно так же, как мы сделали это для колонки «Накоплено»:
Получается, что если студент будет класть остаток с каждого месяца на такой счёт, то сможет накопить на новый телефон на месяц раньше.
