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