Лабораторный практикум по информатике
.pdf
2.Получить на экране следующий фрагмент
3.Получить на экране следующий фрагмент
4.Получить на экране следующий фрагмент
5.Получить на экране следующий фрагмент
6.Получить на экране следующий фрагмент
7.Получить на экране следующий фрагмент
8.Получить на экране следующий фрагмент
171
6.2. Задания для самостоятельного выполнения
Разделы: Специальная вставка, Формат по образцу, Расширенный фильтр, Сводная таблица рекомендуются для самостоятельного ознакомления.
6.2.1. Решение финансово-экономической задачи. Таблица подстановок. Рассчитать годовую ставку помещения по операциям с ценными бумагами по заданной купонной ставке и разности курсов покупки и погашения за указанный период действия ценной бумаги можно при помощи функции ДОХОД. Мастер функций предлагает синтаксис функции ДОХОД:
ДОХОД (дата_соглашения; дата_вступления_в_силу; ставка; цена; погашение; частота; базис), где
дата_соглашения – календарная дата покупки (дата инвестиций); дата_вступления_в_силу – календарная дата погашения или
выкупа ценной бумаги у инвестора (дата окончания действия ценной бумаги);
ставка – годовая ставка выплат по купонам в процентах; цена – рыночная цена (в абсолютном выражении) или курс (в
относительном выражении) ценной бумаги при покупке инвестором; погашение – цена или курс продажи ценной бумаги инвестором; частота – количество выплат по купонам в течение года (1, 2 или
4);
базис – принятый способ исчисления временного периода (года, месяца); Возможные значения для базиса: 0..4:
Задача. Облигации приобретены (дата_соглашения) 6 сентября 2008г. по курсу (цена) 89 и имеют купонный доход (ставка) в размере 9%, который выплачивается с периодичностью (частота) один раз в полугодие. Предполагаемая дата погашения облигации (дата_вступления_в_силу) – 12 сентября 2012 г. по курсу (погашение) 100. Определить годовую ставку помещения.
Для решения поставленной задачи нам потребуется оценить различные варианты возможных решений в зависимости от различных наборов исходных данных с помощью инструмента Excel, называемого Таблица подстановки. Возможные значения аргумента анализируемой функции (или двух ее аргументов) представляются в виде списка или таблицы.
172
Для одного аргумента список исходных значений задается в виде строки или столбца таблицы. Excel подставляет эти значения в функцию, задаваемую пользователем, после чего выстраивает результаты расчетов в строку или столбец. При использовании таблицы с двумя переменными значения одной из них располагаются в столбце, другой – в строке, а результаты вычислений – на пересечении строки и столбца.
В нашем случае с помощью Таблицы данных можно проанализировать влияние на годовую ставку помещения таких аргументов функции ДОХОД, как цена и купон облигации.
Решаем задачу следующим образом (рис.25). Вводим исходные данные в ячейки Е4:Е9. В ячейку D12 вводим формулу для вычисления функции ДОХОД по заданным исходным данным. В ячейки D13: D21 вводим диапазон возможных значений купонной ставки. В ячейки E12:I12 вводим диапазон возможных значений курса покупки ценной бумаги. Выделяем диапазон ячеек, в котором будет расположена таблица возможных решений D12:I21.
Выполняем команду Данные→Анализ «что если»→Таблица данных и в открывшееся диалоговое окно вводим абсолютный адрес ячейки, в которую для расчета функции ДОХОД подставляются значения аргумента, расположенного в столбцах таблицы ($E$6), и абсолютный адрес ячейки ($E$7), в которую в функцию ДОХОД подставляются значения аргумента, расположенного в строках таблицы.
Таблица с полученными результатами возможных значений процентов годовой ставки показана на рис. 2
173
Рис. 2. Значения процентов годовой ставки
174
6.2.2.Варианты заданий для самостоятельного выполнения.
1.Составить таблицу для вычисления n членов арифметической
прогрессии и их суммы. Формула n-го члена арифметической прогрессии: an a1 d (n 1) . Формула суммы n первых членов
арифметической прогрессии: Sn (a1 
где a1 - первый член прогрессии, а d - разность арифметической прогрессии. Значения первого члена и разности задайте самостоятельно.
2. Составьте таблицу для возведения целых чисел от 1 до 9 в степень n.
Показатель степени n
Число |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
1 |
1 |
1 |
1 |
1 |
1 |
1 |
1 |
1 |
1 |
2 |
2 |
4 |
8 |
16 |
32 |
64 |
… |
… |
… |
3 |
3 |
9 |
27 |
… |
… |
… |
… |
… |
… |
4 |
4 |
16 |
… |
… |
… |
… |
… |
… |
… |
5 |
5 |
25 |
… |
… |
… |
… |
… |
… |
… |
6 |
6 |
36 |
… |
… |
… |
… |
… |
… |
… |
7 |
7 |
49 |
… |
… |
… |
… |
… |
… |
… |
8 |
8 |
64 |
… |
… |
… |
… |
… |
… |
… |
9 |
9 |
81 |
… |
… |
… |
… |
… |
... |
… |
Формулы должны быть составлены таким образом, чтобы таблица заполнялась с помощью автокопирования формулы!
3. Составить таблицу для зачисления абитуриентов
|
ФИО |
Экзамены |
|
|
|
Всего |
|
Отметка о |
№ |
абитуриента |
Математика |
Русский |
Физика |
|
баллов |
|
зачислении |
пп |
|
|
Язык |
|
|
|
|
|
1 |
Иванов И.Н. |
60 |
50 |
50 |
160 |
|
Не зачислен |
|
2 |
Сидоров С.С. |
70 |
70 |
80 |
|
220 |
|
Зачислен |
3 |
Петров П.П. |
75 |
85 |
70 |
|
230 |
|
Зачислен |
Зачисляемых студентов и набранные ими баллы дополнительно выделять цветом ячеек, пользуясь командой Формат / Условное форматирование.
175
4. Составить таблицу для решения квадратных уравнений вида ax 2 bx c 0
Предусмотреть возможность решения задания при a 0 . 5. Составить таблицу для расчета платы за проезд.
6. Составить таблицу квадратов двузначных чисел, |
пользуясь |
|||||||||
абсолютными ссылками на ячейки. |
|
|
|
|
|
|||||
|
|
|
|
|
|
|
|
|
|
|
|
0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
1 |
100 |
121 |
144 |
169 |
196 |
225 |
256 |
289 |
324 |
361 |
2 |
400 |
441 |
484 |
529 |
576 |
625 |
676 |
729 |
784 |
841 |
… |
… |
… |
… |
… |
… |
… |
… |
… |
… |
… |
9 |
8100 |
8281 |
8464 |
8649 |
8836 |
9025 |
9216 |
9409 |
9604 |
9801 |
Формулы должны быть составлены таким образом, чтобы таблица заполнялась с помощью автозкопирования!
7. Составить таблицу для расчета выпуска продукции.
Формулы должны быть составлены таким образом, чтобы таблица заполнялась с помощью автоопирования!
176
8.Составить таблицу для анализа зависимости «затраты-эффект» для рекламной деятельности.
№пп |
Наименование |
Затраты |
Результат |
Эффектив- |
Суммар- |
Суммарный |
|
видов |
на |
(эффект) |
ность |
ные |
результат |
|
деятельности |
проект |
проекта |
проекта |
затраты |
|
1 |
Журналы |
300 |
110 |
0,367 |
300 |
110 |
2 |
Газеты |
500 |
40 |
0,080 |
800 |
150 |
… |
… |
… |
… |
… |
… |
… |
7 |
Выставки |
6000 |
70 |
0,012 |
15900 |
405 |
Затраты на проект оцениваются в денежных единицах, эффект – отклики на рекламу. Эффективность – отношение эффекта (результата) к затратам. После определения эффективности виды деятельности необходимо ранжировать по ее убыванию, после чего рассчитать оставшиеся показатели (колонки 6,7). Суммарные затраты и суммарный эффект вычисляются нарастающим итогом.
9. Составить таблицу для расчета платежей за электроэнергию.
Стоимость 1 кВт.ч, первое начальное и все конечные значения придумать самим, остальные значения рассчитать. Формулы должны быть составлены таким образом, чтобы таблица заполнялась с помощью автозаполнения!
177
9.Составить таблицу для определения маржинальной прибыли и маржинальной рентабельности.
Денежные |
|
средства на |
|
|
|
|
|
|
|
||
начало периода |
|
|
|
|
|
|
|
|
|||
|
Наименование |
|
|
|
Периоды времени |
|
|
||||
|
Показателя |
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
||
1 |
Поступления |
|
|
|
|
|
|
|
|
||
2 |
Платежи всего |
|
|
|
|
|
|
|
|
||
3 |
В |
том |
числе |
|
|
|
|
|
|
|
|
|
переменные издержки |
|
|
|
|
|
|
|
|||
4 |
Постоянные издержки |
|
|
|
|
|
|
|
|||
5 |
Сальдо |
|
|
|
|
|
|
|
|
|
|
6 |
Наличие |
денежных |
|
|
|
|
|
|
|
||
|
средств |
|
|
|
|
|
|
|
|
|
|
7 |
Маржинальная |
|
|
|
|
|
|
|
|
||
|
прибыль |
|
|
|
|
|
|
|
|
|
|
8 |
Маржинальная |
|
|
|
|
|
|
|
|
||
|
рентабельность |
|
|
|
|
|
|
|
|
||
Маржинальная прибыль – разность между выручкой за реализацию продукции и переменными затратами (т.е. затратами, растущими пропорционально объему продаж).
Маржинальная рентабельность – отношение маржинальной прибыли к переменным затратам. Характеризует предельно возможную эффективность использования финансов.
11. Составить таблицу для вычисления n - членов геометрической прогрессии и их суммы. Формула i-го члена геометрической
прогрессии |
a |
|
a q i 1 , i 1,..., n , |
где q- знаменатель геометрической |
|
i |
1 |
||
|
|
|
прогрессии.
Сумма всех членов геометрической прогрессии ( q 1) равна
S |
|
(1 q ...q n 1 ) a |
|
q n |
1 |
. |
|
n |
1 |
q |
1 |
||||
|
|
|
|||||
|
|
|
|
|
Значения первого члена и знаменателя задайте самостоятельно.
178
12. Составить таблицу, позволяющую прогнозировать стоимость товара в зависимости от изменяющейся цены (в $) и изменяющегося курса $.
Количество |
|
|
Прогноз стоимости товара |
|
||
товара |
|
|
|
|
|
|
Цена товара, $ |
|
|
Цена товара, $ |
|
||
Курс $ |
|
Январь |
Февраль |
Март |
Апрель |
… |
Цена при |
|
26,3 |
26,7 |
26,8 |
… |
… |
Курс $, январь |
28,567 |
|
|
|
|
|
февраль |
28,515 |
|
|
|
|
|
март |
… |
|
|
|
|
|
… |
… |
|
|
|
|
|
13. Составить таблицу для определения сроков и объемов требуемых заемных средств.
Денежные средства |
100 |
|
|
|
|
|
|
||
на начало периода |
|
|
|
|
|
|
|
|
|
|
Наименование |
|
|
|
Периоды времени |
|
|
||
|
Показателя |
|
1 |
2 |
3 |
4 |
5 |
6 |
7 |
1 |
Поступления |
|
324 |
3 |
9 |
6 |
45 |
12 |
200 |
2 |
Платежи |
|
223 |
16 |
34 |
200 |
34 |
100 |
4 |
3 |
Сальдо |
|
101 |
-13 |
-25 |
-194 |
11 |
-88 |
196 |
4 |
Наличие |
|
201 |
188 |
163 |
-31 |
-20 |
-108 |
88 |
|
денежных средств |
|
|
|
|
|
|
|
|
5 |
Потребность |
в |
нет |
нет |
нет |
31 |
20 |
108 |
нет |
|
займе средств |
|
|
|
|
|
|
|
|
Предусмотреть дополнительное визуальное информирование пользователя о результатах расчетов.
179
14. Составить таблицу для расчета сырья, необходимого для производства 100 кг хлеба.
6.2.3. Задание на построение диаграмм для самостоятельного выполнения. Создайте по таблице из задания 2.3
произвольные диаграммы, отражающие следующие зависимости:
соотношение различных соизмеримых по значениям рядов для каждой строки; процентное соотношение итоговых значений столбцов;
сравнение второго и четвертого столбца.
Данные для построения выбирать по смыслу, элементы оформления задавать произвольно. Использовать различные типы диаграмм, каждую располагать на отдельном листе.
7.Методика выполнения заданий
7.1.Расчет табличных значений
Понятие электронной таблицы
Основные правила:
1.Ввод формул начинайте со знака «=». Внутри формулы не допускаются пробелы, а в адресах используются только латинские буквы.
2.Завершайте ввод в ячейку и ее редактирование нажатием клавиши Enter для сохранения выполненных изменений.
3.Для форматирования данных необходимо активизировать ячейку с данными или выделить блок ячеек. Основные команды форматирования вынесены на вкладку ленты Главная.
4.Заголовок таблицы введите в ячейку, расположенную выше строки, с которой начинается таблица. Для центрирования заголовка по
180