405_Parnacheva_Metod_lab

4. Порядок создания формул в ЭТ.

Лабораторная работа №4 Создание таблицы расчета заработной платы на фирме

Цель работы: Приобрести навыки создания, редактирования таблицы с помощью приложения Excel.

Порядок выполнения:

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

Фамилия	Оклад	Аванс	Пенсионный	Подоходный	К
			фонд	налог	выдаче
Иванов	8000
Антонов	9500
Борисов	9800
Петров	7200
Лазарев	6000
Николаев	8000
ИТОГО:

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

3.В ячейку А10 введите текст «Минимальный размер оплаты», а в ячейку Е10 введите действующий норматив минимального размера оплаты труда.

4.Произведите расчет незаполненных граф следующим образом: аванс составляет 40% от оклада; отчисления в Пенсионный фонд равны 1% от оклада;

подоходный налог составляет 12% от разницы между окладом, отчислением в Пенсионный фонд и минимальным размером оплаты труда;

последняя графа равна разнице между окладом и всеми удержаниями.

5.Выполните расчет итоговой строки, воспользовавшись для этого значком автосуммы.

6.Задайте для ячеек с числовой информацией формат Денежный. В случае необходимости увеличьте ширину столбцов.

11

7.Сохраните документ на рабочем диске, присвоив ему имя. Закройте документ.

8.Снова вызовите сохраненный документ на экран.

9.Вставьте новый столбец перед столбцом с фамилиями и в шапке введите «Номер п/п». Используя автозаполнение автоматически пронумеровав фамилии.

10.Обвести таблицу двойной красной линией.

11.Откорректируйте ячейку с фамилией «Иванов» - заменив ее на «Иванова».

12.Измените фамилию «Борисов» на «Васильев».

13.Измените числовое значение оклада Ивановой.

14.В начало листа вставьте две пустые строки. В первую строку введите заголовок таблицы «Расчетная ведомость» и расположите по центру всей таблицы.

15.Подготовьте документ к печати: введите в верхний колонтитул свою фамилию, а в нижний – текущую дату.

16.Проверьте полученный результат в режиме предварительного просмотра.

17.Сохраните документ на рабочий диск.

18.Выйдите из Excel.

Содержание отчета:

Отчет по лабораторной работе должен содержать:

1.Таблицу, созданную студентом.

2.Формулы, используемые для расчетов в таблице.

3.Описание способов форматирования табличных данных.

Контрольные вопросы:

1. Автозаполнение электронных таблиц Excel.

2.Форматирование электронных таблиц Excel.

3.Печать документов в Excel.

4.Порядок создания формул в ЭТ.

12

Лабораторная работа №5 Создание и подсчет показателей в таблице, отражающей доходы и

расходы фирмы

Цель работы: Получить навыки выполнения экономических расчетов и построения диаграмм в Excel.

Порядок выполнения:

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

Номер	Показатели		Год			Итого
п/п		1 кв.	2 кв.	3 кв.	4 кв.	за
						год
	Продано единиц	3592	4390	3192	4789
	Торговые доходы	143662	175587	127700	191549
	Торговые расходы	89789	109742	79812	119712
	Валовая прибыль
	Расходы на зарплату	8000	8000	9000	9000
	Расходы на рекламу	100000	100000	100000	100000
	Накладные расходы
	фирмы	21549	26338	19155	28732
	Общие затраты
	Производственная
	прибыль
	Удельная валовая
	прибыль

шапку таблицы введите в две строки, задав для ячеек, содержащих название граф, расположение по центру столбца; переопределите ширину столбцов (Номер п/п) и (Итого за год) и (Показатели);

название граф (Номер п/п) , (Показатели) и (Итого за год) введите в две ячейки, выполнив объединение ячеек; название графы (Год) введите в 4 ячейки, выполнив объединение ячеек;

расчертите таблицу так, как указано на рисунке.

2.Автоматически пронумеруйте все показатели в столбце (номер п/п), используя автозаполнение. Проведите выравнивание значений по этому столбику по центру.

3.Выполните необходимые расчеты:

данные по строке «Валовая прибыль» рассчитать как разность между торговыми доходами и торговыми расходами; данные по строке «Общие затраты» получить как сумму трех предыдущих строк;

13

данные по строке «Производственная прибыль» получить как разность между общими затратами и валовой прибылью; данные по строке «Удельная валовая прибыль» получить как результат

деления производственной прибыли на торговые доходы; данные в колонке «Итого за год» получить суммированием квартальных данных.

4.Задайте для строки «Удельная валовая прибыль» процентный формат, а для всех остальных строк – формат с разделителями.

5.На строке 1 (при необходимости вставить строку) введите заголовок “Доходы и расходы фирмы”, задав для него более крупный шрифт и расположение по центру всей таблицы.

6.Скройте столбик с итоговыми показателями.

7.По данным строки «Торговые расходы фирмы» за первые три квартала постройте разрезанную диаграмму (при выделении данных для построения диаграммы использовать и названия кварталов).

8.Добавьте на диаграмму данные по 4 кварталу.

9.По данным строки «Валовая прибыль» постройте гистограмму. Оформите заголовки и легенды диаграмм, расположив диаграммы на отдельных листах.

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

11.На отдельном листе постройте нормированную гистограмму со столбцами в виде цилиндров по данным всей таблицы (исключая графу «Итого за год»).

Содержание отчета:

Отчет по лабораторной работе должен содержать:

1.Таблицу, созданную студентом.

2.Формулы, используемые для расчетов в таблице.

3.Описание способов форматирования табличных данных.

Контрольные вопросы:

1.Автозаполнение электронных таблиц Excel.

2.Форматирование электронных таблиц Excel.

3.Построение диаграмм.

4.Стандартные функции в Excel.

14

Лабораторная работа №6 Работа со списками

Цель работы: Получить навыки в выполнении	операций по
сортировке информации, по поиску информации	в табли це и по

созданию итоговых строк в БД, созданной в Excel.

Порядок выполнения:

1.Создайте таблицу, содержащую сведения о сотрудниках предприятия. Названия колонок: «Фамилия», «Должность», «Отдел», «Дата поступления на работу», «Стаж работы», «Зарплата», «Надбавка», «Премия», «Всего начислено», «Пенсионный фонд», «Налогооблагаемая база», «Налог», «Выплатить». Колонки «Фамилия», «Должность», «Отдел», «Дата поступления на работу», «Зарплата» заполнить произвольными данными, при этом в колонке зарплата использовать суммы от 5000 до 10000 рублей. В колонке «Должность» использовать 5-6 названий (например, инженер, программист, секретарь, техник и т.д.). В колонке «Отдел» использовать 3-4 названия (например, бухгалтерия, отдел кадров, транспортный отдел, отдел маркетинга). Значения в остальных отделах рассчитать по формулам:

-стаж работы = (Текущая дата – дата поступления на работу)/365. Результат округлить до целого.

-Надбавка = - 0, если стаж работы менее 5 лет;

-5% от зарплаты, если стаж работы от 5 до 10 лет;

-10% от зарплаты, если стаж работы больше 10 лет.

При расчете надбавки необходимо воспользоваться логической функцией ЕСЛИ.

-Премия = 20%* (Зарплата + Надбавка).

-Всего начислено = Зарплата +Надбавка +Премия.

-Пенсионный фонд = 1% от «Всего начислено».

-Налогооблагаемая база = Всего начислено – Пенсионный фонд.

-Налог = 13% от налогооблагаемой базы.

-Выплатить = Всего начислено – Пенсионный фонд – Налог.

В таблице должно быть не менее 10 строк.

2.Присвойте рабочему листу имя «Сведения о сотрудниках». Это же название можно использовать в качестве заголовка таблицы.

3.Используя форму данных добавьте в список еще 5 записей.

4.Используя форму данных, выполнить поиск записей по следующим критериям:

-заданная должность;

-заданный отдел;

-стаж работы больше заданного;

15

-заданная должность и зарплата меньше заданной;

-заданный отдел и стаж работы больше заданного.

5.Выполните поочередно сортировку данных по:

-отделам;

-фамилиям;

-отделам и фамилиям;

-отделам и зарплатам;

-отделам, должностям и зарплатам и фамилиям.

6.Используя «Автофильтр», выбрать сведения о сотрудниках:

-с зарплатой от 7000 до 9000;

-фамилии, которых начинается на заданную букву;

-стаж, которых находится в заданном диапазоне;

-с заданной должностью и зарплатой в заданном диапазоне.

7.Используя Расширенный фильтр, выбрать сведения о сотрудниках:

-с зарплатой от 8000 до 10000;

-со стажем работы меньше 5 лет и с зарплатой меньше 7000;

-со стажем работы от 7 до 10 лет и с зарплатой до 6000;

-зарплата, которых выше 9000, а стаж работы от 3 до 5 лет;

-зарплата, которых выше 7000, а стаж меньше или равен 5 годам;

-обо всех инженерах отдела маркетинга, у которых стаж работы больше 5 лет.

8.Скопируйте таблицу на другой лист и присвойте ему имя «Итоги».

9.Используя инструмент подведения итогов, разбейте список на группы по отделам и подведите промежуточные итоги по полям «Фамилия» (операция Количество значений), «Зарплата», «Надбавка», «Премия», «Пенсионный фонд», «Налог», «Выплатить» (операция СУММА).

10.Откройте таблицу на листе «Сведения о сотрудниках».

11.Постройте гистограмму по итоговым данным по графе «Выплатить».

Содержание отчета:

Отчет по лабораторной работе должен содержать:

1.Таблицу, созданную студентом.

2.Формулы, используемые для расчетов в таблице.

3.Описание способов форматирования табличных данных.

16

Контрольные вопросы:

1. Автофильтры электронных таблиц Excel.

2.Построение расширенных фильтров.

3.Сортировка в Excel.

4.Порядок создания формул в ЭТ.

Лабораторная работа № 7 Разработка таблицы, отражающей работу страховых агентов

за квартал

Цель работы: Получить навыки использования встроенных функций в Excel.

Порядок выполнения:

1.Создать таблицу следующего вида, начиная с третьей строки.

№

Фамилия

Объем страховых сделок

Комисион-

п/п

За III

июль

август

Сен-

ное возна-

квартал

тябрь

граждение

Иванов

400

120

390

Федоров

250

20

270

Антонов

370

410

Орлов

290

360

Смирнов

350

210

280

Владимиров

410

380

Егоров

360

290

340

Громов

450

420

Антонов

420

75

470

Борисов

360

420

Всего:

Средний объем

сделок

Максимальный

объем

Минимальный

объем

Количество

неотчитавшихся

Шапку

таблицы

введите в

две строки, задав

для ячеек,

содержащих

название граф, расположение по центру столбца;

текст «Объем страховых сделок» расположите по центру четырех столбцов;

название граф «№ п/п», «Комиссионное вознаграждение» расположите в две ячейки по вертикали, объединив эти ячейки;

17

для граф «№ п/п», «за III квартал», «Комиссионное вознаграждение» - задайте перенос по словам;

название граф «июль», «август», «сентябрь» введите используя автозаполнение.

2.Введите фамилии, после чего переопределите ширину столбца В, сделав его равным максимальной длине фамилии.

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

4.Введите числовые данные в столбцы D, E и F.

5.Автоматически пронумеруйте все фамилии в столбце А.

6.Сделайте сортировку всей таблицы по фамилиям (без столбца А).

7.Вычислите для каждого страхового агента объем сделок за III квартал как сумму сделок за июль, август сентябрь.

8.Рассчитайте сводные показатели (расположенные в нижней части таблицы) по соответствующим функциям.

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

10.По каждой фамилии рассчитайте данные в графе «Комиссионное вознаграждение» как произведение «Объема страховых сделок за III квартал» на значение процента, вычисленного функцией ПРОСМОТР по тарификационной таблице. Для функции ПРОСМОТР выберите синтаксическую форму ПРОСМОТР (искомое значение; просматриваемый вектор; вектор результатов).

11.Введите текст примечаний в ячейки с фамилиями агентов, имеющих максимальный и минимальный объем сделок за III квартал.

12.Справа от графы «Комиссионное вознаграждение» рассчитайте ранг каждого страхового агента по показателям III квартала.

13.Создайте имя для диапазона ячеек с данными за III квартал и примените его к ячейкам диапазона с данными о ранге.

14.Рассчитайте процентную норму ранга за III квартал.

18

15. Разграфите таблицу, напишите заголовок по центру таблицы более крупным шрифтом.

Тарификационная таблица

200	8%
300	10%
400	12%
500	15%
600	18%
800	20%
1000	25%

Содержание отчета:

Отчет по лабораторной работе должен содержать:

1.Таблицу, созданную студентом.

2.Формулы, используемые для расчетов в таблице.

3.Описание способов форматирования табличных данных.

Контрольные вопросы:

1.Автозаполнение электронных таблиц Excel.

2.Сортировка в Excel.

3.Расчет итоговых показателей.

4.Функции в электронных таблицах Excel.

Лабораторная работа №8

Выполнение расчетов в таблицах Excel с использованием таблиц данных с одной изменяющейся переменной

Цель работы: Получить навыки работы с таблицей подстановки с одной изменяющейся переменной.

19

Порядок выполнения:

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

A		B	C	D	E	F
	Отчет заработной плате
		Отдел	З.плата
		ОК	10000			*
		Бухгалтерия	7800		ОК
		Плановый	6500		Бухгалтерия
		ОК	7000		Плановый
		Бухгалтерия	12000
		Бухгалтерия	9000
			Отдел

1.В ячейки таблицы с координатами B2:C8 вводятся необходимые данные по суммам зарплат по каждому отделу.

2.Названия отделов, по которым будет подсчитываться сумма, необходимо скопировать в ячейки E4:E6.

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

4.В верхней строке будущей таблицы подстановки необходимо ввести формулу, по которой будет выполняться расчет. В данном случае в ячейку F3 вводится формула БДСУММ, которая имеет следующий формат БДСУММ (база_данных; поле; критерий).

База данных – диапазон ячеек B2:C8.

Поле – 2 (№ столбца в имеющейся таблице). Критерий – C10:C11.

5.Далее, выделив диапазон ячеек содержащий таблицу подстановки (E3:F6),

выполнить команду данные/ таблица подстановки.

6.В появившемся диалоговом окне указать адрес ячейки, через которую будет выполняться обмен данными. В данном случае (ячейка ввода/ строка), указать координаты ячейки С11.

7.После выполнения приведенного выше примера, решить следующее задание:

20