Разработка занятия "Анализ данных с помощью таблиц подстановки в Excel"

Дисциплина «Информационные технологии в профессиональной деятельности»

Раздел дисциплины «Обработка финансово-экономической информации с помощью табличного процессора MS Excel»

Практическая работа № __

1.Тема практической работы: Анализ данных с помощью таблиц подстановки в Excel

2. Количество часов: 2.

3.Место проведения: кабинет ИКТ, лаборатория ИКТ

4. Характер работы: репродуктивный

5. Форма организации учебной деятельности студентов: фронтальная, индивидуальная

6.Внутрипредметные и связи: использования маркера автозаполнения, адресация ячеек, форматирование ячеек.

7. Дидактические цели практической работы

7.1.Закрепить теоретические знания по теме «Таблицы подстановки»

7.2.Сформировать практические навыки и умения решения задач при помощи табличного процессора.

7.3.Сформировать исследовательские умения при работе с таблицами подстановок в Excel, при решении финансово-экономических задач в Excel

8.Оборудование (аппаратура, материалы и др.):ПК, ПО MS Excel, раздаточный материал

9.Дидактическая структура практической работы

1.Целевая установка.

2.Проверка теоретической готовности студентов к выполнению практической работы. Тестирование

3.Инструктаж о содержании, этапах практической работы, способах (методах) действий правилах и технике безопасности.

1 Инструктаж о содержании и этапах практической работы

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

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

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

Правила создания таблицы подстановки с одной переменной.

Таблица подстановки формируется с соблюдением следующих требований:

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

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

Если значения расположены в столбце:

• формула вводится в ячейку, расположенную на одну строку выше и на одну ячейку правее первого значения;

• любые другие формулы вводятся правее первой формулы.

Если значения расположены в строке:

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

• любые другие формулы вводятся ниже в том же столбце.

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

Пример. Рассчитайте, какую сумму надо положить на депозит, чтобы через четыре года она выросла до $25 000 при ежеквартальном начислении процентов и при разной годовой процентной ставке (3%, 4%, 5%, 6%).

Подготовим рабочий лист с исходными данными задачи.

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

Синтаксис:

ПС(ставка;кпер;плт;бс;тип) где

• ставка – это процентная ставка за период;

• кпер – это общее число периодов выплат;

• плт (выплата) – величина постоянных периодических платежей;

• бс (бз) – будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если аргумент бс (бз) опущен, то он полагается равным 0. В этом случае должно быть указано значение аргумента плт.

• тип – это число 0 или 1, обозначающее, когда должна производиться выплата (в конце или начале периода). Если этот аргумент опущен, то он полагается равным 0.

Выделите интервал ячеек C5:D9 и выполните команду Данные – Таблица подстановки. В диалоговом окне Таблица подстановки переключитесь в поле Подставлять значения по строкам в: и укажите ссылку на ячейку с исходной процентной ставкой

Полученная таблица представлена в форме массива – обратите внимание на содержимое строки формул , формула заключена в фигурные скобки. Это означает, что невозможно изменить содержимое одной из ячеек таблицы (интервал D6:D9). Однако можно удалить таблицу целиком.

Изменим условие задачи. Рассчитайте, какую сумму надо положить на депозит, чтобы при ежеквартальном начислении процентов она выросла до $25 000 при разных годовых процентных ставках (3%, 4%, 5%, 6%) и разных сроках накопления (5, 6, 7 и 8 лет).

Подготовьте рабочий лист с исходными данными задачи. Выделите интервал ячеек C5:G9 и выполните команду Данные – Таблица подстановки

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

Решение задачи представлено в таблице D6:G9. Формула массива имеет вид:

{=ТАБЛИЦА(B2;B3)}

2.Инструктаж по ТБ

4.Выполнение практической работы.

5.Оценка выполнения работы.

Преподаватель Жижко А.А.