Как научить Эксель самому находить деньги на телефон

Как научить Эксель самому находить деньги на телефон

Мы ставим задачу, а Эксель её решает

Сейчас расширим ваши горизонты в Экселе. 

Когда мы копили на айфон с помощью Экселя и банковского вклада, то строили таблицы — это нам было нужно, чтобы увидеть сумму, которая получится в конце. У того решения был один минус — мы сами должны были задавать стартовые суммы и проверять, сработает или нет, будет у нас нужная сумма в конце года или не накопим.

Сегодня будет другое решение, в котором мы указываем финансовую цель, а Эксель сам нам скажет, на какую сумму нужно пополнять счёт, чтобы всё сошлось. 

👉 Для этого мы воспользуемся возможностью «Подбор параметра». Это работает ровно так же, как звучит: Эксель за нас подгонит цифры, чтобы получить нужный результат. Никакой математической магии, просто перебор. Компьютерам так можно. 

Исходная ситуация

  • у нас есть стартовая сумма, которую мы можем сразу положить на счёт;
  • каждый месяц мы добавляем фиксированную сумму, чтобы накопить на телефон (как пример);
  • банк даёт нам какую-то процентную ставку на год, но проценты по счёту начисляет каждый месяц.

Нам нужно будет подобрать такую сумму ежемесячных взносов, чтобы в конце получилась стоимость телефона.

Исходная таблица

Сначала подготовим пустую таблицу, в которую будем заносить все данные:

Как научить Эксель самому находить деньги на телефон

Теперь заполним таблицу так:

  • стартовый взнос: 10 000 рублей;
  • процентная ставка: 6 процентов годовых;
  • срок вклада: 12 месяцев.

Ежемесячный взнос мы пока не знаем, поэтому оставим пустым:

Как научить Эксель самому находить деньги на телефон

Считаем будущую стоимость

В Экселе есть встроенная функция БС (в английской версии это FV) — она рассчитывает размер дохода по вкладу с фиксированной процентной ставкой. Работает она так:

БС(Ставка; Количество платежей; Размер каждого платежа; Стартовый взнос)

Ставка — сколько процентов начисляется за каждый период. Так как у нас проценты начисляются ежемесячно, то ставка должна показывать проценты за месяц. Для этого берём размер годовой ставки и делим на 12 месяцев.

Количество платежей — сколько раз мы будем пополнять счёт. В нашем случае — тоже 12 месяцев.

Размер каждого платежа — какую сумму будем добавлять каждый месяц. Особенность Экселя в том, что его нужно поставить со знаком «минус».

Стартовый взнос — он может быть, а может и не быть. Если его нет, можно не заполнять.

Используем эти знания и подготовим формулу, которая посчитает накопления за всё время:

=БС(B2/B3; B3; -B5; -B1)

Как научить Эксель самому находить деньги на телефон

Нажимаем энтер, чтобы увидеть результат:

Как научить Эксель самому находить деньги на телефон

Получается, что если мы ничего не будем больше вносить на счёт, то он увеличится на 616 рублей.

Подбираем размер платежа

Теперь у нас всё готово, чтобы Эксель нам рассчитал размер ежемесячного платежа. Для этого в меню Экселя выбираем Данные → Анализ «Что, если» → Подбор параметров:

Как научить Эксель самому находить деньги на телефон

Появится окно, где нам нужно ввести параметры подбора:

Как научить Эксель самому находить деньги на телефон

Допустим, новый телефон стоит 55 000 рублей, а значит, именно такую сумму нам нужно накопить за год. Формула с подсчётом наших накоплений хранится в ячейке B7, поэтому её указываем в первом параметре. Эту ячейку Эксель будет постоянно проверять, получилось что-то или нет.

Значение — тут мы указываем, какое значение нам нужно чтобы появилось в ячейке B7. Раз телефон стоит 55 000, то такое же значение указываем вторым параметром.

Третий параметр — с какой ячейкой Эксель будет экспериментировать, чтобы в B7 появилось нужное значение. В нашем случае — с первоначальным взносом, с ячейкой B5.

Как научить Эксель самому находить деньги на телефон

Нажимаем ОК и смотрим на результат:

Как научить Эксель самому находить деньги на телефон

Эксель решил нашу задачу и выдал ответ: нужно вносить на счёт 3798 рублей, чтобы за год накопилось ровно 55 тысяч. 

Скачать файл с примером

Теперь мы можем поручать Экселю считать и подбирать количество рабочих часов, размер зарплаты, KPI работников и что угодно ещё. 

Текст:

Михаил Полянин

Редактор:

Максим Ильяхов

Художник:

Даня Берковский

Корректор:

Ирина Михеева

Вёрстка:

Кирилл Климентьев

Соцсети:

Олег Вешкурцев

Получите ИТ-профессию
В «Яндекс Практикуме» можно стать разработчиком, тестировщиком, аналитиком и менеджером цифровых продуктов. Первая часть обучения всегда бесплатная, чтобы попробовать и найти то, что вам по душе. Дальше — программы трудоустройства.
Начать карьеру в ИТ
Получите ИТ-профессию Получите ИТ-профессию Получите ИТ-профессию Получите ИТ-профессию
Еще по теме
Самая сложная задача в мире

В заголовке всё сказано.

hard
Итого — 9 поездок, в два раза меньше, чем первым способом! Граждане, берегите лифт!
Логическая задача про лифт

Сколько нужно выдержать поездок, чтобы попасть на свой этаж?

easy
Задачи на рассуждения

Правильные решения в запутанных условиях.

easy
Задача о деньгах

Пропало 300 рублей, но никто этого не заметил.

medium
9 простых задач на математику

Всё настолько просто, что половину можно решить в уме.

easy
Задачка: узнать среднюю зарплату в строгой компании
Задачка: узнать среднюю зарплату в строгой компании

Непростая задачка против звериного оскала капитализма.

easy
Головоломка про альпиниста и короткую верёвку

Разбежавшись, слезет со скалы.

easy
Задача про таблетки и злого гения

Как логика и ограничения помогают найти банку с отравленными таблетками.

medium
Как выиграть в соревнованиях, когда играешь хуже всех

Ещё одна обалденная задача на стратегию и игровую теорию.

hard
Задача про новую должность и выбор зарплаты

Когда вы решили все логические задачи на собеседовании, вам предложат последнюю — самую важную.

hard
medium