Вычисление будущего значения с помощью функции БС

Функция БС определяет будущую стоимость инвестиций на основе периодических постоянных платежей.

К примеру, Вы можете подсчитать значение Вашего капитала через 20 лет, если Вы будете вносить на свой счет по 10000 рублей в год при 10% годовых (рис. 18).

6.4. Ошибки в функциях

Диалоговое окно Мастер функций делает ввод функций относительно несложным. Если Вы при вводе сделаете ошибку, то увидите в ячейке код, называемый ошибочным значением. Ошибочное значение начинается со знака (#) и оканчивается обычно восклицательным знаком.

В табл. 7 показаны наиболее распространенные ошибочные значения Excel.

Рис. 17. Пример применения функции ПЛТ

Рис. 18. Пример применения функции БС

Таблица 7

Коды ошибочных значений Мастера функций.

Ошибочное значение	Описание
#ДЕЛ/О!	Вы ввели в формулу деление на ноль. Убедитесь, что в формуле нет ссылок на пустые ячейки.
#Н/Д	Возможно, Вы пропустили аргумент функции. В ячейке отсутствует значение.
#ИМЯ?	Имя используемого диапазона не найдено в рабочей книге.
#ПУСТО!	Вы попытались использовать пересечение двух областей, не имеющих общих ячеек. Возможно, в одном из аргументов имеются лишние пробелы.
#ЧИСЛО!	В функции использованы неприемлемый аргумент или не сходящиеся итерации, поэтому невозможно получить результат.
#ССЫЛКА!	В формуле содержится недопустимая ссылка на ячейку.
#ЗНАЧ!	В качестве аргумента использована ячейка, содержащая текст.
#####	Результат вычисления не помещается в ячейку. Увеличьте ширину столбца.

6.5. Ссылки абсолютные, относительные и смешанные

Практически все формулы включают ссылки на ячейки или диапазоны ячеек. При изменении значения в ячейке формулы, в которых есть ссылка на эту ячейку, автоматически пересчитываются в соответствии с новым значением ячейки. В формулах используются три типа ссылок на ячейки и диапазоны:
относительные, абсолютные и смешанные. От метода адресации ссылок зависит, что будет с ними происходить при копировании формулы из одной ячейки в другую.

Относительная адресация. По умолчанию, ссылки на ячейки в формулах рассматриваются как относительные. Это означает, что адреса в ссылках при копировании формулы из одной ячейки в другую автоматически изменяются. Они приводятся в соответствие с относительным расположением исходной ячейки и создаваемой копии.

Абсолютная адресация. При абсолютнойадресации адреса ссылок при копировании формулы не изменяются, так что ячейка, на которую указывает ссылка, рассматривается как постоянная.

Для изменения способа адресации при редактировании формулы надо выделить ссылку на ячейку и нажать клавишу F4. Перед элементами номера ячейки, использующими абсолютную адресацию, ставится символ $. Например, при последовательных нажатиях клавиши F4 номер ячейки А1 будет записываться как

А1, $А$1, А$1 и $А1.

В двух последних случаях один из компонентов номера ячейки рассматривается как абсолютный, а другой – как относительный. Это так называемая смешанная адресация.

Пример использования относительных ссылок приведен на рис.19.

В этом примере в ячейку D2 вводится формула =B2*C2, далее она протягивается до ячейки D4. Так как в исходной формуле применена относительная адресация, адреса ячеек в последующих формулах автоматически меняются (приведены в столбике Е), что позволяет получить правильный результат, не набирая несколько раз одну и ту же формулу.

Рис. 19. Пример применения относительных ссылок

На рис.20 приведен пример использования абсолютных ссылок, в котором в формуле для подсчета комиссии (столбец Комиссия) необходимо использовать процент комиссии (ячейка В7). Так как процент в данной задаче постоянен, то ссылка на ячейку В7 должна быть абсолютной, что и реализовано в формуле в ячейке D2. Заметим, что ссылки на ячейки столбиков Количество и Цена определены как относительные (см. пример на рис. 19).

Рис. 20. Пример применения абсолютных ссылок

В примере создания таблицы умножения (рис. 21) используется смешанная адресация:

Рис. 21. Пример создания таблицы умножения

- в ячейке В1 набираем = А1+1;

- протягиваем через первую строчку до столбца К;

- в ячейке А2 набираем = А1+1;

- протягиваем через первый столбец до строчки 11;

- в ячейке В2 набираем = $А2+В$1;

- протягиваем диапазон ячеек до ячейки К11.

6.6. Использование имен диапазонов в функциях

Диапазонам ячеек можно присваивать имена и использовать их вместо ссылок на ячейки.

Например, можно присвоить имя Ряд диапазону ячеек с В2 по В5 и тогда суммирование значений этих ячеек с помощью функции СУММ может быть оформлено двумя способами:

= СУММ(В2:В5) = СУММ(Ряд)

Имена диапазонов должны начинаться с буквы и не могут содержать пробелов, специальных символов и символов пунктуации (кроме знака подчеркивания и точки).

Для присвоения имени диапазону в Excel предусмотрены два способа: команда Присвоить имя группы команд Определение имени вкладки ленты Формулы или щелчок на поле имен с последующим вводом названия (рис. 22).

Рис. 22. Пример создания имени диапазона ячеек

В первом случае открывается диалоговое окно Создание имени, в котором уточняется диапазон ячеек, вводится его имя и задается зона применимости имени (пункт Область) (см. рис. 22).

Excel позволяет создавать имена с помощью текста, расположенного в соседних ячейках, для этого необходимо выполнить следующее:

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

2.

Выполните команду Создать из выделенного группы команд Определение имени вкладки ленты Формулы. Открывается диа­логовое окно Создание имен из выделенного диапазона, в котором необходимо указать, из какой ячейки (ячеек) выбрать название диапазона (рис. 23).

Рис. 23. Создание имен из выделенного диапазона

Имена диапазонов могут применяться в качестве аргументов функций там, где допустимы ссылки на диапазоны. Например, имя диапазона Ряд может стать аргументом функции СУММ или СРЗНАЧ.

Имя диапазона можно выбрать с помощью команды Использовать в формуле группы Определение имени либо из ниспадающего списка в поле имени.

Команда Диспетчер имен той же группы предоставляет полную информацию о всех именах рабочей книги, позволяет редактировать имена, создавать новые и удалять существующие имена, фильтровать списки имен (рис. 24).

Рис. 24. Диалоговое окно «Диспетчер имен»

6.7. Установка связей между листами

При работе с книгой из нескольких листов довольно часто возникает необходимость использовать данные одного листа при создании формулы на другом. Установка таких связей между листами в Excel называется ссылкой.

Пример ссылки:

= Фрукты!B1 + Овощи!C4 + Ягода!B33

В приведенном примере вычисляется сумма значений ячейки В1, находящейся на листе Фрукты, ячейки С4 листа Овощи и ячейки В33 листа Ягода.

Возможно создание более сложных внешних ссылок, включающих имя другой рабочей книги Excel, в этом случае оно должно быть заключено в квадратные скобки. Если рабочая книга, на которую делается ссылка, закрыта и не находится в текущей папке, то следует добавить к ссылке полный путь к этой книге, как показано на примере:

= С:\BUDGET\[plan.xlsx]Лист1!А1

7. Анализ данных С ПОМОЩЬЮ СЦЕНАРИЕВ «ЧТО-ЕСЛИ»

Электронные таблицы в Excel представляют собой динамические модели, которые немедленно реагируют на изменение данных, т.е. формулы будут автоматически пересчитывать результат при изменении содержимого ячеек, которые в них использовались. Наряду с таким самым простым видом анализа «что-если» Excel предоставляет еще две возможности:

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

Диспетчер сценариев – создает поименованный сценарий и генерирует структурированные отчеты для различных моделей.

7.1. Создание таблиц данных

Таблица данных (в предыдущих версиях Excel называлась таблицей подстановок) – это динамический диапазон результирующих ячеек, в котором по заданным формулам, зависящих от входных ячеек, выполняются определенные вычисления. Таблица данных одновременно может оперировать только с одной или двумя входными ячейками, соответственно создаются таблицы данных с одним входом или с двумя входами.

На рис. 25 приведен пример создания таблицы данных с одним входом.

Рис. 25. Исходные данные и результирующая таблица данных с одним входом

Решается задача об ипотечной ссуде. В диапазоне ячеек С4 – С7 задаются исходные данные, в ячейках С10 – С13 помещены формулы для расчета результирующих величин. Изменяя значения любых исходных данных, мы можем
отслеживать изменение всех результирующих ячеек – это простейший вид анализа «что-если».

Представленная на рис. 25 таблица данных позволяет отследить изменение четырех результирующих величин (Размер ссуды, Месячная плата, Общая сумма выплат и Общая сумма комиссионных) при изменении величины процентной ставки от 6 до 8% с шагом 0,25%. Для этого необходимо выполнить следующее.

1. Создать заготовку таблицы (ячейки Е3 – I12). Строка 3 должна состоять из ссылок на соответствующие формулы (например, ячейка F3 содержит формулу = С10), в столбец Е заносятся все возможные значения входной величины (в данном случае – процентной ставки).

2. Выделить ячейки таблицы Е3 – I12 и выполнить команду Таблица данных пункта Анализ «что-если» группы команд Работа с данными вкладки ленты Данные.

3. В открывшемся диалоговом окне Таблица данных (рис. 26) выбрать пункт Подставлять значения по строкам и указать адрес ячейки, в которую будут подставляться значения входной величины из таблицы (С7).

4.

Щелкнуть на кнопке ОК.

Рис. 26. Диалоговое окно «Таблица данных»

При создании таблицы данных с двумя входами диапазоны входных величин помещаются как в первую строчку таблицы, так и в первый столбец, а в ячейку на их пересечении (вычисляемая ячейка) помещается ссылка на ячейку, в которой прописана формула для искомой величины.

Например, на рис. 27 приведена таблица данных с двумя входами, в которой вычисляется размер месячной платы при различных комбинациях процентной ставки (ячейки Е4 – I12) и срока ссуды (ячейки F3 – J3). В ячейку Е3 (вычисляемая ячейка) заносится формула = С11 (адрес ячейки, где прописана формула для вычисления месячной платы).

Рис. 27. Исходные данные и результирующая таблица данных с двумя входами

7.2. Диспетчер сценариев

С помощью средства Диспетчер сценариев можно автоматизировать процесс выполнения анализа «что-если» для различных моделей – создавать несколько наборов исходных данных для любого количества рассчитываемых функций, а затем по имени модели выводить на рабочий лист результаты расчетов для заданного набора исходных данных.

Для примера рассмотрим компанию, производящую три изделия, на изготовление которых требуется разное количество материала и времени (рис. 28). Необходимо спрогнозировать общую прибыль от продажи всех изделий. Так как точно не известны стоимость материалов и тариф почасовой оплаты, определяем три возможных набора этих исходных данных и задаем им соответствующие имена (рис. 29). Для этого необходимо выполнить следующие действия.

1. Выполняем команду Диспетчер сценариев пункта Анализ «что если» группы Работа с данными вкладки ленты Данные.

Рис. 28. Производственная модель для демонстрации работы средства «Диспетчер сценариев»

Рис. 29. Исходные данные различных сценариев производственной модели

2. В открывшемся диалоговом окне Диспетчер сценариев (рис. 30) нажимаем клавишу Добавить и в диалоговом окне Добавление сценария вводим его название и адреса изменяемых ячеек. Нажав кнопку ОК,
открываем диалоговое окно Значения ячеек сценария, в котором вводим значения изменяемых ячеек для
рассматриваемого сценария. Возвращаемся в диалоговое окно Диспетчер сценариев (кнопка ОК).

3. Повторяем действия п. 2 для всех рассматриваемых сценариев.

Теперь в окне Диспетчер сценариев будут отражены все занесенные в него сценарии и для того, чтобы отразить на рабочем листе расчеты для определенного сценария достаточно выбрать его в списке и нажать на клавишу
Вывести (см. рис. 30).

После создания сценариев можно создать итоговый отчет, воспользовавшись клавишей Отчет в диалоговом окне Диспетчер сценариев. В открывшемся окне Отчет по сценарию следует выбрать тип отчета, а в графе Ячейки
результата ввести адреса тех ячеек, которые содержат интересующие нас формулы (в нашем примере это ячейки B13:D13 и В15). На рис. 31 показан один из вариантов такого отчета.

Рис. 31. Отчет по результатам работы средства «Диспетчер сценариев»

8. Анализ данных: ПОДБОР ПАРАМЕТРОВ И ПОИСК РЕШЕНИЯ

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

8.1. Использование команды Подбор параметра

Самой простой командой для решения оптимизационных задач в Excel является команда Подбор параметра. Эта команда определяет неизвестную величину, приводящую к требуемому результату, – например, количество проданных компакт-дисков по 27 рублей, необходимое для достижения объема продаж в 500 000 рублей.

При использовании команды Подбор параметра меняется всего одна переменная, т.е. изменение дополнительных переменных не допускается.

Для работы с командой Подбор параметра необходимо, чтобы на листе находились:

- формула для расчета;

- ячейка для искомого значения;

- значения прочих величин, встречающихся в формуле.

Во время подбора параметра в переменную ячейку непрерывно заносятся новые значения, пока не будет найдено решение поставленной задачи. Такой процесс называется итерацией, и продолжается он до тех пор, пока Excel не выполнит заданное по умолчанию количество попыток (итераций) или не найдет решение, лежащее в пределах заданной точности (чтобы настроить оба параметра, необходимо выполнить команду Параметры вкладки ленты Главная и задать нужные значения выбрав пункт Формулы).

Оптимизация с помощью команды Подбор параметра выполняется так:

1. Создайте лист с формулой, пустой (переменной) ячейкой и любыми данными, которые могут понадобиться при вычислениях.

2. Выделите ячейку, в которой содержится формула (в диалоговом окне Подбор параметра этой ячейке соответствует поле Установить в ячейке).

3. Выполните команду Подбор параметра пункта Анализ «что если» группы Работа с данными вкладки ленты Данные. Открывается диалоговое окно Подбор параметра. В поле Установить в ячейке автоматически заносится адрес выделенной ячейки.

4. Введите в поле Значение ту итоговую величину, которая должна быть получена по условию задачи.

5. Переместите курсор в поле Изменяя значение ячейки и выделите ту ячейку, в которой должен содержаться ответ (переменная ячейка). Ее содержимое будет подобрано и подставлено в формулу командой Подбор параметра. Выбранная ячейка выделяется на листе рамкой.

6. Щелкните на кнопке ОК, чтобы запустить поиск решения. После завершения итерационного цикла в диалоговом окне Результат подбора параметра появляется сообщение о результате поиска решения.

7. Закройте диалоговое окно Результат подбора параметра.

При длительных вычислениях (например, при включении в формулу нескольких финансовых функций) можно остановить итерационный цикл кнопкой Пауза в диалоговом окне Результат подбора параметра или просматривать итерации в пошаговом режиме кнопкой Шаг.

Примеры применения команды Подбор параметра приведены на рис. 32.

8.2. Использование команды Поиск решения

В тех случаях, когда оптимизационная задача содержит несколько переменных величин, на которые могут быть наложены различные ограничения, для ее решения необходимо воспользоваться надстройкой Поиск решения.

Если команда Поиск решения отсутствует в группе задач Анализ вкладки ленты Данные, следует выполнить команду Надстройки из меню Параметры вкладки Файл, нажать клавишу Перейти и установить флажок Пакет анализа в диалоговом окне Надстройки.

Рис. 32. Примеры применения команды «Подбор параметра»

Рассмотрим решение простой оптимизационной задачи на примере небольшого кафетерия, для которого необходимо определить число продаваемых чашек кофе для получения максимального дохода.

Предположим, что в кафетерии продается кофе трех разновидностей:

- обычный кофе (19 руб. 99 коп.);

- особый кофе со сливками (25 руб. 50 коп.);

- особый кофе с шоколадом (41 руб.).

У нас нет полной информации относительно размеров потенциальной прибыли и того, на какой напиток следует делать основную ставку (хотя особые виды кофе приносят больше прибыли, но их компоненты стоят дороже, к тому же приготовление порции занимает больше времени).

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

8.2.1. Постановка задачи при работе с командой Поиск решения

1. Создание специализированного листа.

- Создание целевой ячейки, в которой определяется суть задачи (например, формула определения общего дохода, который необходимо максимизировать).

- Создание одной или нескольких переменных ячеек, значения которых могут изменяться для достижения поставленной цели.

- Включение других значений и формул, использующих значения
целевой и переменных ячеек.

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

2. Выяснение или задание ограничений.

- Ограничением называется граничное условие или руководящий принцип, которые должны выполняться при решении конкретной задачи. Например, в нашем примере: складские помещения и условия продажи позволяют производить за неделю не более 500 чашек кофе (как обычного, так и особого).
Кроме того, существуют ограничения на поставку сливок и шоколада, которые не позволяют производить в неделю более 125 чашек кофе с шоколадом и 350 чашек особого кофе обоих видов (рис. 33).

-
На листе должны содержаться ячейки, в которых вычисляются ограничиваемые величины (в нашем примере – ячейки с F9 по F11). Численные значения самих ограничений приведены в ячейках с F14 по F16 для наглядности.

Рис. 33. Подготовка специализированного листа для команды «Поиск решения»

Мы воспользовались командой Влияющие ячейки из группы Зависимости формул вкладки ленты Формулы, чтобы показать связи между ячейками листа.

8.2.2. Непосредственный поиск решения

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

1. Выделите на листе целевую ячейку (F4).

2. Выполните команду Поиск решения группы Анализ вкладки Данные. Открывается диалоговое окно Параметры поиска решения (рис. 34).

3. В группе Оптимизировать целевую функцию установить переключатель Максимум, поскольку в рассматриваемой задаче нас интересует именно максимальное значение целевой ячейки.

4.

В поле Изменяя ячейки переменных поместите адреса переменных ячеек (при этом ссылки в поле разделяются точкой с запятой – С5;С10;С15).

Рис. 34. Диалоговое окно «Параметры поиска решения»

5. В группе В соответствии с ограничениями щелкните на кнопке Добавить, если требуется ввести ограничения (в нашем случае присутствуют три условия). Открывается диалоговое окно Добавление ограничения (рис. 35), в котором задается первое ограничение (F11<=500).

Рис. 35. Диалоговое окно «Добавление ограничения»

6. Щелкая на кнопке Добавить, введите все ограничения. Щелкните
на кнопке 0К, чтобы занести все ограничения в диалоговое окно Параметры поиска решения.

Чтобы изменить ограничения, приведенные в диалоговом окне Параметры поиска решения, выделите ограничение в списке и щелкните на кнопке Изменить. Чтобы настроить итерационный цикл и задать параметры вычислений, щелкните на кнопке Параметры и внесите требуемые изменения.

Задача готова к выполнению. Щелчок на кнопке Найти решение открывает диалоговое окно Результаты поиска решения (рис. 36) или выводит на экран сообщение об ошибке, если при поиске решения возникли проблемы.

Рис. 36. Диалоговое окно «Результаты поиска решения»

Установите переключатель Сохранить найденное решение и щелкните на кнопке ОК. Найденная максимальная величина помещается в целевую ячейку, а переменные ячейки заполняются оптимальными значениями переменных, которые удовлетворяют наложенным ограничениям (рис. 37).

Рис. 37. Результаты работы команды «Поиск решения»

8.2.3. Изменение условий оптимизационных задач

Самым большим достоинством решений, находимых с помощью команды Поиск решения, является простота, с которой они могут пересчитываться с учетом новых целей. Например, если Вы желаете получать от продажи кофе ровно 10000 р. в неделю, то сможете вычислить оптимальную комбинацию продаж различного кофе для выполнения этого условия. Можно вводить дополнительные ограничения: задать по каждой категории определенное минимальное значение или потребовать сведения к минимуму количества продаваемых продуктов.

При различных наборах начальных условий можно получить различные решения, поэтому можно перед выполнением командызадать в переменных ячейках те значения, которые бы Вас устроили, и Excel при поиске решения начнет именно с них.

8.3. Решение системы линейных алгебраических уравнений с помощью команды «Поиск решения»

Процедура поиска решения используется в разнообразных практических задачах: минимизация расходов на перевозку товаров (транспортная задача), оптимизация объемов выпускаемой продукции в зависимости от имеющихся ресурсов, максимизация доходов от портфеля ценных бумаг и пр. Ограничимся рассмотрением решения системы линейных алгебраических уравнений (СЛАУ).

Предположим нам необходимо решить СЛАУ из трех уравнений с тремя неизвестными. Подготавливаем специализированный лист (рис. 38), на котором в ячейки I5 – I7 заносим произвольные значения переменных x, y и z (переменные ячейки), в ячейках E5 – E7 набираем формулы для уравнений, входящих в систему (Excel автоматически рассчитывает их значения для текущих значений переменных), а в ячейки F5 – F7 заносим искомые значения функций.

Рис. 38. Подготовка специализированного листа для решения СЛАУ

Выполняем команду Поиск решения и в открывшемся диалоговом окне Параметры поиска решения заносим адреса переменных ячеек и прописываем ограничения, которые в рассматриваемой задаче соответствуют равенствам текущих и искомых значений функции, т.е. E5=F5, E6=F6 и E7=F7 (рис. 39). В данном случае нам нет необходимости определять целевую ячейку, поэтому формально можно занести в поле Оптимизировать целевую ячейку адрес любой ячейки, содержащей формулу (например, Е5). На рис. 39 показано полученное решение (x = 0,75; y = – 2,0; z = 0,5), которое удовлетворяет всем трем уравнениям

Второй способ решения задачи предусматривает определение целевой ячейки. Это может быть текущее значение любого уравнения системы, которое следует оптимизировать до искомого значения. При этом равенства текущих и искомых значений двух других функций будут представлять накладываемые ограничения (как и в первом способе решения СЛАУ).

Рис. 39. Результаты работы команды «Поиск решения» при решении СЛАУ

Библиографический список

1. Информатика. Базовый курс. 2-е изд. / Под ред. С. В. Симоновича. СПб: Питер, 2005. 640 с.

2. Информатика для юристов и экономистов / С. В. Симонович,
Г. А. Юксеев и др. СПб: Питер, 2001. 688 с.

3. Экономическая информатика / Под ред. П. В. Конюховского и Д.Т. Колесова. СПб: Питер, 2001. 560 с.

4. Леонтьев В. П. Новейшая энциклопедия персонального компьютера / В. П. Леонтьев. М.: ОЛМА-пресс, 2004. 734 с.

5. Хэлворсон М. Эффективная работа с Microsoft Office / М. Хэлворсон, М. Янг; Пер. с англ. Т. Федоровой. СПб: Питер, 2003. 1232 с.

Дата добавления: 2017-01-14