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

ЗАДАНИЯ ПО РАБОТЕ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ

Лабораторная работа № 1.

Тема: Создание простых таблиц. Вычисление значений по формулам.

Задание. Создайте таблицу следующего вида

Таблица 1

Расчет повременной заработной платы за февраль 1998 года.

Фамилия	Ставка за час, руб.	Количество отработанных часов	Начислено руб.	Налог (14%) руб.	Итого к выдаче руб.
Иванов
Петров
Сидоров
Первухин
Кошкин
Мышкин

Всего:

Пояснения к работе:

1. Формула для вычисления в графе “НАЧИСЛЕНО” есть произведение почасовой ставки на количество часов. Налог составляет 14% от начисленной суммы, к выдаче причитается разность между “НАЧИСЛЕНО” и суммой налога.

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

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

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

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

6. Переименуйте рабочий лист, присвоив ему имя ФЕВРАЛЬ.

7. Перейдите на второй лист и восстановите таблицу на этом листе. Переименуйте в заголовке таблицы “февраль” на “март”, аналогично присвойте листу имя “МАРТ”. Очистите область данных “Количество отработанных часов” и введите новые данные.

8. Удалите оставшиеся листы и сохраните таблицу.

Лабораторная работа № 2.

Тема: Редактирование в таблицах. Использование функций.

Задание.

1. Загрузите таблицу, созданную в лабораторной работе №1

2. Добавьте в конец таблицы строку: Козлов 11 190. Выполните копирование формул для этой строки.

3. Добавьте столбец “ПРЕМИЯ” после столбца “НАЛОГ”. Премию вычислите по формуле 30% от “НАЧИСЛЕНО” для тех, у кого отработано часов больше среднего. Для этого используйте логическую функцию “Если”. Для подсчета среднего используйте специальную функцию.

4. Сделайте перерасчет “К ВЫДАЧЕ”, учитывая наличие премии.

5. Добавьте первый столбец “НОМЕР ПО ПОРЯДКУ”. Для заполнения его значениями используйте Автозаполнение.

6. Для знакомства с функциями Excel оформите таблицу ниже исходной таблицы следующего вида

Таблица 2

	Количество отработанных часов	Итого к выдаче
Средние показатели
Наибольшие показатели
Наименьшие показатели

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

7. Сохраните таблицу с новым именем.

Лабораторная работа № 3.

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

Задание. Создайте таблицу (см. табл. 3), в которой представлены ежемесячные платежи за предоставленные кредиты, отмечаемые в кредитной карте.

Пояснения к заданию.

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

2. Вычислите ежемесячный средний взнос по все видам платежей в колонке “ВСЕГО”, и среднемесячный итоговый взнос по видам платежей в строке “ИТОГО”, вставив ее после строки “ДЕКАБРЬ”. Вычисление производите с точностью до двух знаков после запятой.

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

4. Сохраните таблицу.

5. Перейдите на Лист 2 и постройте линейную диаграмму с узловыми точками для рядов VISA, STB и MASTER CARD. Установите сетку по обеим осям и присвойте названия для диаграммы “Платежи по кредитным карточкам” и подзаголовок “За 1999 календарный год”.

6. Постройте объемную гистограмму для рядов VISA, STB и MASTER CARD.

7. Постройте объемную круговую диаграмму для ряда ИТОГО. Присвойте названия категорий для каждого сегмента. Отделите сегмент для STB CARD.

8. Постройте кольцевую диаграмму для MIN и MAX.

9. Выполните копию первой диаграммы на Листе 3 и подберите линии тренда: для 1-го ряда – степенную, для 2-го ряда – логарифмическую, для 3-го – экспоненциальную линию. Вынесите уравнения в область диаграмм и задайте для них размер 12 пунктов и курсивное начертание.

10. Сохраните свою работу.

Таблица 3

Платежи за кредит

	VISA	STB CARD	MASTER CARD	ВСЕГО
Январь
Февраль
Март
Апрель
Май

Лабораторная работа №6.

Тема: Финансовые функции. Логические функции. Абсолютные ссылки.

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

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

1. Откройте Лист 1 и присвойте ему имя КРЕДИТ. Укажите в ячейке А1 заголовок Финансирование, а в ячейке А3 подзаголовок Выбор кредита.

2. Сравните предложения разных банков, данные по которым будут представлены по строкам. Для этого укажите в ячейках В7, В9, В11 соответственно БАНК1, БАНК2, БАНК3. В ячейки C5, D5, E5, F5, G5 введите заголовки столбцов таблицы с исходными данными (см. табл. 6), а в ячейки I5, J5, K5, L5 введите заголовки таблицы, в которой будут производиться расчеты (см. табл. 7).

3. Введите исходные данные согласно табл. 6. Задайте в столбцах D F процентный формат, а в столбцах С и Е - денежный.

Таблица 6.

	Объем кредита	Выдача (%)	Плата за оформление	Ставка (%)	Срок (лет)
Банк 1	200000р.	0,95	300р.	0.12
Банк 2	200000р.	0,96	250р.	0,135
Банк 3	200000р.	0,965		0,142

4. Произведем расчеты во второй части таблицы. В ячейке J7определим величину платы за риск, которую банки берут с клиентов (дизажио) в случае невозврата кредита. Она равна произведению Объема кредита на (1 - Выдача). В ячейке I7 подсчитайте сумму, которую банк Вам выплатит. Для этого из Объема кредита нужно вычесть Дизажио и Плату за оформление. В ячейке L7 подсчитайте плату, которую Вы должны заплатить банку за кредит, для этого используйте финансовую функцию ППЛАТ(вводите только обязательные аргументы функции). В ячейке L7 определите отношение годовой выплаты банку к сумме полученного от банка кредита.

5. Скопируйте формулы из I7, J7, K7 , L7 для остальных банков. Задайте в столбцах I, J, K денежный формат, а в столбце L – процентный. Оформите таблицу. Сравните полученные результаты с результатами, приведенными в табл. 7.

6. По отношению в столбце L определите банк, в котором Вам выгоднее взять кредит и постройте для него план погашения кредита.

Таблица 7

Получено	Дизажио	Выплата (год)	Выплата/Получено
189 700,00р.	10 00,00р.	55 481,95р.	29,2%
191 750,00р.	8 000,00р.	57 558,22р.	30,0%
192 650,00р.	7 000,00р.	58 536,99р.	30,4%

4. По отношению в столбце L определите банк, в котором Вам выгоднее взять кредит и постройте для него план погашения кредита.

5. Перейдите на второй рабочий лист и присвойте ему имя Погашение кредита. В ячейке А2 введите заголовок таблицы План погашения кредита. В ячейках B4, C4, D4, E4 введите заголовки столбцов: Год, Погашение долга, Выплата по процентам, Остаток.

6. Используя операцию Автозаполнение введите в столбец В годы от 1 до 5. Определите в ячейке D5 размер выплачиваемого в первый год долга по процентам как произведение Объема кредита на Ставку процента из листа КРЕДИТ. Напоминаю, что при ссылке на лист КРЕДИТ, надо использовать абсолютные адреса. В ячейке С5 запишите часть годового платежа, которая уйдет в первый год на погашение основного долга по формуле Выплата ( год) из листа КРЕДИТ минус Выплата по процентам. Остаток долга к концу первого года подсчитаем в ячейке Е5 по формуле: Объем кредита минус Погашение долга за первый год.

7. Выплачиваемые по долгу проценты за второй год определяются, исходя из остатка к концу первого года. Запишите результат в ячейку D6. Остаток долга к концу второго года определяется как разница между остатком к концу первого года и суммой, выплаченной на погашение долга за второй год. Запишите формулу в ячейку Е6. Скопируйте формулы из ячеек C5, D6, E6. При правильных вычислениях остаток долга к концу пятого года должен быть равен нулю.

8. Оформите таблицу. Укажите во всей таблице денежный формат.

Лабораторная работа № 7.

Тема: Работа с данными.

Задание: Выполните операции над данными. Предварительно создайте справочник, приведенный в табл.8.