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

1. В новой книге создайте таблицу по образцу:

Район	Кол-во комнат	Этаж	Общая площадь, м^2	Жилая площадь, м^2	Кухня, м^2	Стоимость квартиры, руб.	Стоимость квартиры, $	Стоимость 1 кв.м. квартиры руб.	Стоимость 1 кв.м. квартиры, $

2. Требования при заполнении таблицы:

· названия районов - ЮМР, МХГ, СХИ, ЦМР (при заполнении можно воспользоваться командой Выбор из списка);

· в каждом районе несколько 1, 2, 3-х комнатных квартир;

· не менее 25 записей.

3. Для перевода в доллары курс укажите в отдельной ячейке.

4. Отформатируйте таблицу.

5. Произведите сортировку данных по районам, затем по количеству комнат и в последнюю очередь по площади.

6. Скопируйте эту таблицу в текстовый редактор WORD.

7. Сохраните документ как База.

8. Произведите отбор данных в соответствии с Вашим вариантом:

0) отберите 1 комнатные квартиры ЮМР, общая площадь которых не менее 45 м ² и стоимостью до 18 тыс. $;

1) отберите 2 комнатные квартиры ЮМР, кухня которых не менее 10 м ² и стоимостью до 900 тыс.руб;

2) отберите 1 комнатные квартиры района СХИ, жилая площадь не менее 25 м ² и кухня 6-8 м ² и стоимостью не более 19 тыс.$;

3) отберите 2 комнатные квартиры района СХИ, общей площадью 45-50 м ² и стоимостью до 1 млн.руб;

4) отберите 3 комнатные квартиры ЦМР, общей площадью 60-70 м ², жилой не менее 40 м ² и кухней 10 м ²;

5) отберите 1 комнатные квартиры ЦМР на 3-8 этаже, общая площадь которых не менее 40 м ² стоимостью до 20 тыс. $;

6) отберите 2 комнатные квартиры ЦМР, кухня которых не менее 8 м ², стоимостью до 950 тыс.руб, находящиеся выше 3 этажа;

7) отберите 3 комнатные квартиры района СХИ, жилая площадь 45-60 м ² и кухней не менее 6 м ² и выше 2 этажа;

8) отберите 3 комнатные квартиры ЮМР, кухня которых не менее 10 м ² и стоимостью до 1,5 млн.руб, расположенные до 5 этажа;

9) отберите 2 комнатные квартиры района МХГ, общей площадью 45-60 м ², кухня которых не менее 8 м ² и стоимостью до 28 тыс. $, расположенными до 7 этажа;

10. Скопируйте отобранные данные на новый лист, дайте имя листу;

9. На новых листах подведите итоги:

· по средней цене квартиры в рублях 1,2,3 комнатных квартир сгруппированных по районам;

· по средней цене квартиры в долларах 1,2,3 комнатных квартир сгруппированных по районам;

· по средней цене в рублях 1 м² 1,2,3 комнатных квартир сгруппированных по районам;

· по средней цене в долларах 1 м² 1,2,3 комнатных квартир сгруппированных по районам;

· по количеству квартир в каждом районе.

9.11. Работа с финансовыми функциями. Прогнозирование с помощью анализа "Что-если".

Финансовые функции при экономических расчётах

Функция ПЛТ. Расчёт величины ежемесячной выплаты кредита

Функция ПЛТ определяет сумму периодического платежа для аннуитета[2] на основе постоянства сумм платежей и постоянства процентной ставки.

Задание 1

Определить ежемесячный платёж, если банк предоставляет кредит в 140000р. с рассрочкой в 5 лет под 8,5% годовых с ежемесячной выплатой. Последний платёж должен составить 10000р.

Введём данные в таблицу Excel согласно рис. 6)

1 Выделить ячейку В6 и щелкнуть по кнопке Вставка функции (знак f_x слева от строки формул). Появится окно Мастера функций, выбрать категорию Финансо­вые.

2 Щелкнуть мышью по функции ПЛТ, перетащить окно ПЛТ на свободное место экрана, чтобы освободить таблицу и заполнить его поля:

Рисунок 6 Расчёт аннуитета

· Поле Ставка – это процент в месяц, вводим 0,085,

· Кпер – количество периодов выплат, т.е. 5лет*12мес, вводим 5*12

· Нз – общая сумма всех платежей с текущего момента, вводим 140000,

· Бс – будущая стоимость, вводится 130000 со знаком "-", т.к. платим мы, а не банк,

§ Тип – выплата в конце месяца, поэтому вводим 0 или ничего.

3 Нажать ОК.

Результат: около 2738 р. ежемесячно нужно выплачивать, чтобы погасить 130000 р. за 5 лет (в конце срока последним платежом ещё 10000р.)

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

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

 

Задание 2

Компания сделала заём на 80 000 руб. сроком на 3 года. Определить:

· - ежемесячные выплаты при процентных ставках 7%, 8% и 9% годовых,

· - ежемесячные выплаты при процентной ставке 5%, сроке заема 5 лет и сумме заема 100 000р.

 

1 Введем таблицу подстановок в виде (рис. 7):

 

 

Рисунок 7 Таблица подстановок

 

2 Введём в ячейку D2 формулу платежа ПЛТ (В3/12;В4*12;В5) вручную или через окно ПЛТ из Мастера функций (см. пример 1), в D2 появится рассчитанное значение функции -2470,17р.

3 Изменим значение ячейки В3 на 8%, получим в D2 cумму платежа –2506,91р.

4 Изменим значение ячейки В3 на 9%, получим в D2 cумму платежа –2543,98р.

5 Изменим одновременно значения ячеек: В3на 5%, В4на 5 и В5 на 100000, получим в D2 cумму платежа –1887,12р.

 

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

 

2 способ. Сценарий – это набор значений подстановки, используемый для прогнозирования поведения модели. На одном листе Excel можно создать и сохранить несколько различных сценариев и переключаться на любой из них для просмотра результатов и выбора наилучшего.

Задание 2

 

Оформим в виде сценариев варианты подстановки данных из пунктов 2 и 3 примера 7.

Для создания сценария необходимо выполнить следующие действия:

1 Из меню Сервис выберете команду Сценарии.

2 В открывшемся окне Диспетчер сценариев нажмите кнопку Добавить.

3 Введите имя сценария., например "Ставка 7%"".

4 В поле Изменяемые ячейки задайте те ячейки (через двоеточие), которые Вы собираетесь изменить, в данном случае – ячейку В3.

5 Нажмите кнопку ОК.

6 В открывшемся диалоговом окне Значения сценария для каждой изменяемой ячейки введите новое значение или формулу, в данном случае вводим в В3число 0,07. Нажмите кнопку ОК. Исходную модель " что-если " желательно сохранить в виде сценария, присвоив ему, например, имя «Стартовые значения». В противном случае при задании новых изменяемых ячеек исходные данные будут потеряны.

Для просмотра сценария необходимо воспользоваться кнопкой Вывести в окне Диспетчер сценариев. Щелкнув кнопку Итоги в диалоговом окне Диспетчер сценариев, можно получить итоговый отчет на отдельном рабочем листе с названием "Структура сценариев", показывающий влияние разных сценариев на одну или несколько результирующих ячеек. Знаки "+"("-") слева и сверху позволяют разворачивать (сворачивать) отдельные разделы отчёта. Серым выделены изменяемые поля.

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

Задание 3

 

Условие примера 6. Компания может ежемесячно выплачивать не более 2500р. Определить, каким должен для этого быть последний платёж.

1.Выделим ячейку.В6:

2.В меню Сервис выбрать команду Подбор параметра.

В окне Подбор параметра:

· в поле Установить в ячейке – введено В6,

· в поле Значение - ввести -2500

· в поле Изменяя значение ячейки – ввести В3 (ячейка последнего платежа),

· нажать ОК.

Результат: последний платёж = -27716 р.

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

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

 

Задание 4

1 Выполнить задание примера 6, изменив сумму кредита на 140000· n, где n - номер студента в журнале преподавателя. Выполнить то же для новой суммы кредита, изменив годовой процент с 8,5% на 5%, а срок кредита с 5 на 10 лет.