Практическая работа
Цель работы: Решить задачу «Создание штатного расписания предприятия». Построить круговые диаграммы.
Порядок выполнения работы
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. Сохраните файл.
