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

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

Лабораторная работа № 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.