Штатное расписание предприятия

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

Цель работы: Решить задачу «Создание штатного расписания предприятия». Построить круговые диаграммы.

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

1. Создайте новый файл под именем «Книга 4».

2. На Листе 1 решите задачу «Создание штатного расписания предприятия». Условия задачи:

-минимальная зарплата В = 1200 р.;

- минимальная ежемесячная надбавка С = 800 р.;

- коэффициент А задан для каждой должности в табл. 2.27;

- число сотрудников каждой должности указано в табл. 2.27;

- формула для вычисления окладов: Оклад = А×В+С (В и С - следует принять за абсолютные значения);

- формула для вычисления суммарного оклада: Суммарная выплата на должности = Количество сотрудников × Оклад.

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

3. На Лист 1 введите таблицу по образцу (таб. 2.27). Выберите границу и заливку произвольно.

 

Таблица 2.27

Таблица 2.27. Итоговая

4. Постройте объемную разрезанную круговую диаграмму по табл. 2.27 (

5. Самостоятельно пересчитайте таблицу с новыми значениями В = 800. Результат в табл. 2.28.

Таблица 2.28

6. Постройте объемную разрезанную круговую диаграмму по табл. 2.28.

 

7. Сохраните файл.

 

Логические функции

Функции И, ИЛИ, СЧЕТ ЕСЛИ, СУММ(А4:А24), Максимум, Минимум, Среднее, СРЗНАЧ используются при решении логических операций.

 

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

Цель работы: Применить логическую функцию «ЕСЛИ» при вычислении оплаты труда сотрудникам.

· Задание 1. Вычислите трудовой стаж и в зависимости от него рассчитайте надбавки и итоговые выплаты по каждому сотруднику, постройте диаграмму.

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

1. Откройте файл под именем «Книга 4». На Лист 2 введите данные (табл. 2.29).

2. Вычислите:

- стажкаждого сотрудника на текущий год по формуле: Стаж на текущий год = $C$1-СЗ;

- надбавку по следующему алгоритму: сотруднику дается надбавка в размере 1500 р., если его стаж не менее 20 лет. Формула: Надбавка= ЕСЛ И(ЕЗ>=20;1500;0);

- итого =3арплата+Надбавка;

- общий итог при помощи суммирования.

 

Таблица 2.29

Текущий год
Табельный номер	Ф.И.О.	Год приема	Зарплата	Стаж на текущий год	Надбавка	Итого
	Андреев А.Б.
	Рудников С.А.
	Савельев М.А.
	Соколов Л.В.
	Томарев Л.В.
	Токарев А.А.
	Тимофеев Б.Р.
	Ульянов Д.Г.
	Устинов Д.Г.
	Усиков В.В.
	Филатов Л.А.
	Федосеев Б.В.
	Филонов К.Е.
	Яковлев И.И.
				Итого

Таблица 2.29. Итоговая

Текущий год
Табельный номер	Ф.И.О.	Год приема	Зарплата	Стаж на текущий год	Надбавка	Итого
	Андреев А.Б.
	Рудников С.А.
	Савельев М.А.
	Соколов Л.В.
	Томарев Л.В.
	Токарев А.А.
	Тимофеев Б.Р.
	Ульянов Д.Г.
	Устинов Д.Г.
	Усиков В.В.
	Филатов Л.А.
	Федосеев Б.В.
	Филонов К.Е.
	Яковлев И.И.
				Итого

3. Постройте диаграмму, отражающую изменение оклада сотрудников в зависимости от стажа (рис. 2.26).

Рис. 2.26

· Задание 2. Вычислите трудовой стаж и в зависимости от его значения рассчитайте надбавки и итоговые выплаты по каждому сотруднику.

1. Скопируйте табл. 2.29 на Лист 2, измените заголовок на «Вычисление надбавок в зависимости от стажа работы и оклада сотрудников»

2. Удалите итоговые результаты столбцов «Надбавка» и «Итого».

3. Вычислите надбавки по алгоритму:

- надбавка - 0, если стаж меньше 10 лет;

- надбавка - 10% оклада, если стаж больше или равен 10 годам и меньше 15 лет;

- надбавка - 20% оклада, если стаж больше или равен 15 годам.

Формула: Надбавка=ЕСЛИ(ЕЗ<10;0;ЕСЛИ(ЕЗ>=15;DЗ*20%;DЗ*10%)).

Формула: Итого=Зарплата+Надбавка.

 

Таблица 2.30

Текущий год
Табельный номер	Ф.И.О.	Год приема	Зарплата	Стаж на текущий год	Надбавка	Итого
	Андреев А.Б.
	Рудников С.А.
	Савельев М.А.
	Соколов Л.В.
	Томарев Л.В.
	Токарев А.А.
	Тимофеев Б.Р.
	Ульянов Д.Г.
	Устинов Д.Г.
	Усиков В.В.
	Филатов Л.А.
	Федосеев Б.В.
	Филонов К.Е.
	Яковлев И.И.
				Итого

4. Постройте диаграмму, отражающую изменения окладов сотрудников (рис. 2.27). Удерживая нажатой клавишу Ctrl (Shift), выделите столбцы В («Ф.И.О.») и G («Итого») с данными расчета результатов и выберите вкладку Вставка • Диаграммы • График.

Рис. 2.27

 

5. Сохраните файл.

 

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

Цель работы: Применить логическую функцию «ЕСЛИ» при решении вопроса закупки и продажи овощей.

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

1. Откройте файл «Книга 4».

2. На Лист 3 введите следующие данные (табл. 2.31).

 

Таблица 2.31

Товар	Область	Количество товара	Купить	Продать
Курага	Краснодар
Инжир	Краснодар
Чернослив	Краснодар
Изюм	Краснодар
Курага	Дагестан
Инжир	Дагестан
Чернослив	Дагестан
Изюм	Дагестан
Курага	Осетия
Инжир	Осетия
Чернослив	Осетия
Изюм	Осетия
Курага	Ингушетия
Инжир	Ингушетия
Чернослив	Ингушетия
Изюм	Ингушетия
Итого

3. Используя логические функции, встолбец «Купить» введитеформулы, которыепозволят получить в ячейках столбца знак «+», если продукт поставлен из Краснодара и при этом его наличие на базе меньше 5000 кг, в противном случае должен быть полученпробел. Формула: Купить=ЕСЛИ(И(В2=»Краснодар»;С2<5000);»+»;» «).

4. Используя логические функции, в столбец «Продать» введите формулы, которыепозволят получить в ячейках столбца знак «+», если продукт поставлен из Дагестанаи при этом его наличие на базе больше 3000 кг, в противном случае должен быть получен пробел. Формула: Продать=ЕСЛИ(И(В2=»Дагестан»;С2>3000);»+»;» «).

Таблица 2.31. Итоговая

Товар	Область	Количество товара	Купить	Продать
Курага	Краснодар		+
Инжир	Краснодар		+
Чернослив	Краснодар
Изюм	Краснодар		+
Курага	Дагестан			+
Инжир	Дагестан
Чернослив	Дагестан			+
Изюм	Дагестан
Курага	Осетия
Инжир	Осетия
Чернослив	Осетия
Изюм	Осетия
Курага	Ингушетия
Инжир	Ингушетия
Чернослив	Ингушетия
Изюм	Ингушетия
Итого

5.Постройте диаграмму наличия на базе Кураги (рис. 2.28). Отфильтруйте таблицу по столбцу «Количество товара», для этоговыделите таблицу и выберите вкладку Главная • Редактирование • Сортировка и фильтр • Сортировать от А до Я Выделите строки с закупкой Кураги (табл. 2.32.) и постройтеразрезанную круговую диаграмму.

Таблица 2.32

Товар	Область	Количество товара	Купить	Продать
Курага	Краснодар		+
Курага	Дагестан			+
Курага	Осетия
Курага	Ингушетия

Рис. 2.28

Рис. 2.29

6. Выделите строки с закупкой Изюма (табл. 2.33) и постройте разрезанную круговую диаграмму наличия на базе Изюма (рис. 2.29).

Таблица 2.33

Товар	Область	Количество товара	Купить	Продать
Изюм	Краснодар
Изюм	Дагестан
Изюм	Осетия
Изюм	Ингушетия

7. Сохраните файл.

Обработка массивов данных

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

Цель работы: По данным метеорологических наблюдений вычислить максимальную и минимальную температуру за год, среднемесячную температуру за год, число морозных месяцев (<-20 °С), число жарких месяцев (>25 °С), используя логические функции.

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

1. Откройте файл «Книга 4».

2. На Лист 4 введите следующие данные по образцу (табл. 2.34).

 

Таблица 2.34

Среднемесячная температура воздуха в регионе за год, С°
Месяц
Январь	-21,0	-19,0	-20,5	-20,5	-21,0	-17,5
Февраль	-15,0	-16,5	-14,0	-12,5	-14,5	-12,5
Март	-6,0	-7,0	-6,5	-8,0	-7,5	-6,5
Апрель	-2,0	-1,0	-1,5	-0,5	-1,0	-0,5
Май	4,0	4,5	3,5	4,0	4,5	3,5
Июнь	15,0	14,5	16,0	15,5	14,5	15,0
Июль	21,0		24,0	24,5	23,5	23,0
Август	16,5	15,5	16,0	14,5	14,0	13,5
Сентябрь	12,4	14,3	12,0	14,5	12,8	14,0
Октябрь	7,6	6,9	7,2	8,1	6,9	6,5
Ноябрь	-3,0	-2,5	-3,8	-2,5	-3,5	-3,2
Декабрь	-12,5	-11,8	-10,3	-9,4	-10,5	-11,2

 

3. Ниже создайте табл. 2.35 и произведите необходимые расчеты по формулам:

Максимальная температура =MAKC(B3:G14)

Минимальная температура =MИH(B3:G14)

Среднемесячная температура за год =CPЗHAЧ(B3:G14)

Число морозных месяцев (<-20 °С) =СЧЕТЕСЛИ(ВЗ:С14;»<=-20»

Число жарких месяцев (> 25 °С) =СЧЕТЕСЛИ(ВЗ:С14;»>25»)

4. Постройте диаграмму (рис. 2.30) по данным табл. (2.35. Итоговая), используя Вставка • График • График с накоплением.

 

Таблица 2.35

Статистические данные за 2010-2015 гг.
Максимальная температура
Минимальная температура
Среднемесячная температура за год
Число морозных месяцев (<-20 °С)
Число жарких месяцев (> 25 °С)

Таблица 2.35. Итоговая

Статистические данные за 2010-2015 гг.
Максимальная температура	24,5
Минимальная температура	-21
Среднемесячная температура за год	1,7
Число морозных месяцев (<-20 °С)
Число жарких месяцев (> 25 °С)

Рис. 2.30

5. Сохраните файл.

 

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

Цель работы: Создать таблицу по образцу и произвести вычисления, используя стандартные и логические функции.

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

1. Откройте файл «Книга 4».

2. На Лист 5 введите следующие данные (табл. 2.36).

 

Таблица 2.36

Выручка торговой компании по магазинам в течение 2014г., р.
Месяц	Магазин
«Эльдорадо»	«Турист»	«Водник
Январь
Февраль
Март
Апрель
Май
Июнь
Июль
Август
Сентябрь
Октябрь
Ноябрь
Декабрь

3. Создайте табл. 2.37 и произведите необходимые вычисления по формулам:

Максимальная выручка в течение года =MAKC(B4:D15)

Минимальная выручка в течение года =MИH(B4:D15)

Среднегодовая выручка =CPЗHAЧ(B4:D15)

Суммарная выручка за год =CУMM(B4:D15)

Число убыточных месяцев (выручка<10000р.) =СЧЕТЕСЛИ(В4:D15;»<10000»)

Создайте табл. 2.37 для каждого магазина отдельно и произведите расчеты. На основании полученных данных постройте объемную разрезанную круговую диаграмму для каждого магазина (рис. 2.31).

 

Таблица 2.37

Максимальная выручка в течение года
Минимальная выручка в течение года
Среднегодовая выручка
Суммарная выручка за год
Число убыточных месяцев (выручка<10000р.)

Таблица 2.37. Итоговая

Максимальная выручка в течение года
Минимальная выручка в течение года
Среднегодовая выручка
Суммарная выручка за год
Число убыточных месяцев (выручка<10000р.)

Рис. 2.31

4. Сохраните файл.