Практическое занятие -1
Основы обработки данных средствами электронной таблицы Excel.
Применение надстройки «Поиск решения».
Применение средств автоматизации ввода данных.
1. Запустите программу Excel.
2. Создайте новую рабочую книгу. Сохраните её под именем book.xls
3. Выберите щелчком на ярлычке неиспользуемый рабочий лист или создайте новый (Вставка - Лист). Дважды щелкните на ярлычке нового листа и переименуйте его как Дополнительные расходы.
4. Сделайте текущей ячейку А 1 и введите в нее текст: Месяцы.
5. Сделайте текущей ячейку В 1 и введите в нее текст: Расходы.
6. Сделайте текущей- ячейку А2. Введите в нее текст Январь 2021. Нажмите клавишу ENTER. Убедитесь, что текст был автоматически распознан как дата (изменился формат, а данные выровнялись по правому краю ячейки). Нажмите клавишу ВВЕРХ. При желании, дайте команду Формат. Ячейки и укажите иной формат записи даты.
7. У становите указатель мыши на маркер заполнения в правом нижнем углу рамки текущей ячейки. Нажмите правую кнопку мыши и протяните рамку так, чтобы она охватила все ячейки от А2 до А25.
8. Отпустите кнопку мыши. В открывшемся меню выберите пункт Заполнить по месяцам. Убедитесь, что в столбце А появились обозначения для всех месяцев по декабрь 2022 года включительно.
9. В данном примере будем считать, что в первый месяц расходы составляли 10 рублей, а в каждом последующем возрастали на 10%. Сделайте текущей ячейку В2. Введите в нее число 10. Нажмите клавишу ENTER.
10. Щелкните на ячейке В2 правой кнопкой мыши и выберите в контекстном меню пункт Формат ячеек. На вкладке Число выберите вариант Денежный и щелкните на кнопке ОК. Убедитесь, что число теперь записано как денежная сумма.
11. Щелкните правой кнопкой мыши на маркере заполнения и протяните рамку так, чтобы она охватила ячейки с В2 по В25. Отпустите кнопку мыши. В открывшемся меню выберите пункт Прогрессия.
12. На панели Тип установите переключатель Геометрическая, в поле Шаг задайте значение 1,1. Щелкните на кнопке ОК.
13. В ячейку С1 введите текст Нарастающий итог.
14. Сделайте текущей ячейку С2. Введите в нее текст =В2. Это формула, которая означает, что значение ячейки С2 равно значению ячейки В2. Эта простая формула гарантирует, что если значение в ячейке В2 будет изменено, то ячейка С2 все равно будет содержать верные данные.
15. Сделайте текущей ячейку С3. Введите формулу: В3+С2.
16. Снова сделайте ячейку С3 текущей. Наведите указатель мыши на маркер заполнения, нажмите левую кнопку и протяните рамку так, чтобы она охватывала ячейки с С3 по С25.
17. Щелкните на одной из ячеек столбца С и посмотрите, какая формула в ней записана. Убедитесь, что все формулы были скорректированы по принципу относительной адресации.
18. Сохраните рабочую книгу book.xls.
Применение итоговых функций
1. Запустите программу Exсel (Пуск - Программы - Мiсrоsоft Excel).
2. Откройте рабочую книгу book.xls.
3. Выберите рабочий лист Дополнительные расходы по месяцам, созданный в предыдущем упражнении.
4. Сделайте текущей первую свободную ячейку столбце В (В26).
5. Щелкните на кнопке Автосумма на стандартной панели инструментов.
6. Убедитесь, что программа автоматически подставила в формулу функцию СУММ и правильно выбрала диапазон ячеек для суммирования. Нажмите клавишу ENTER.
7. Проверьте правильность вычислений, сравнив значения в ячейках В26 и С25.
8. Сделайте текущей следующую свободную ячейку в столбце В.
9. Щелкните на кнопке Вставка функции на стандартной панели инструментов.
10. В списке Категори я выберите пункт Статистические.
11. В списке Функция выберите функцию СРЗНАЧ и щелкните на кнопке ОК.
12. Используя порядок действий, описанный в пп. 8-12, вычислите минимальное число в заданном наборе (функция МИН), максимальное число (МАКС), количество элементов в наборе (СЧЕТ).
13. Сохраните рабочую книгу book.xls.
3. Решение задачи оптимизации расходов предприятия
Решите задачу по подбору графика работы для работников с пятидневной рабочей неделей и двумя выходными подряд, обеспечивающий требуемый уровень обслуживания при наименьших затратах на оплату труда, методами оптимизации пакета Excel используя надстройку “Поиск решения”.
Цель - минимизация расходов на оплату труда.
Изменяемые данные - число работников в группе.
Ограничения - число работников не может быть отрицательным;
- число работников должно быть целым числом;
- число ежедневно занятых работников не должно быть меньше ежедневной потребности.
Выберете следующую модель:
1. В первой строке объединяем ячейки от А до L и пишем заголовок: “График занятости персонала”.
2. В первом столбце набираем: А6- График, А7-А, А8-Б, А9-В, А10-Г, А11-Д, А12-Е, А13-Ж; А14:А18-пропускаем; А19, В19, С19-объединяем и пишем - Дневная оплата работника; А20, В20, С20-объединяем и пишем – Общая недельная зарплата.
3. Объединяем ячейки столбцов В и С с шестой по 18-ю строки и записываем: В6 – выходные дни, В7 – вск-пн, В8 – пн-вт, В9 – вт-ср, В10 – ср-чт, В11 – чт-пт, В12 – пт-сб, В13 – сб-вск; В14 – пропускаем; В15 объединяем с А15 – Всего; В16 – пропускаем; В17 объединяем с А17 – Всего требуется; В18 – пропускаем.
4. Заполняем столбец D: D6 – Работники, D7 – 4, D8 – 4, D9 – 4, D10 – 6, D11 – 6, D12 – 4, D13 – 4, D14 – пропускаем, D15 – набираем формулу =СУММ(D7:D13), D16:D18 – пропускаем, D19 – 40, D20 набираем формулу =D15*D19.
5. Столбец Е пропускаем.
6. Заполняем столбец F (цифра 1 означает, что день рабочий, 0- выходной): F6 – вск, F7 – 0, F8 – 1, F9 – 1, F10 – 1, F11 – 1, F12 – 1, F13 – 0; F14 – пропускаем, F15 – набираем формулу =$D$7*F7+$D$8*F8+$D$9*F9+$D$10*F10+$D$11*F11+$D$12*F12+$D$13*F13 и распространяем её на все дни недели до столбца L включительно, F16 – пропускаем, F17 – 22, F18:F20 – пропускаем.
7. Заполняем столбец G: G6 – пн, G7 – 0, G8 – 0, G9 – 1, G10 – 1, G11 – 1, G12 – 1, G13 – 1, G14 и G16 – пропускаем, G17 – 19, G18:G20 – пропускаем.
8. Аналогично заполняем столбцы: H, I, J, K, L. До 14-й строки с учётом выходных дней.
9. В 17-й строке пишем: H17-15, I17-14, J1716, K17-18, L17-23.
Рекомендации к решению:
Запустите надстройку Поиск решения (Сервис - Поиск решения). Если пункт Поиск решения в меню отсутствует, значит, необходимо подключить эту надстройку, дав команду Сервис - Надстройки и установив соответствующий фла жок.
В поле Установить целевую ячейку выберите ячейку $D$20, для переключателя Равной выберите вариант минимальному значению. Изменяемые ячейки: $D$7:$D$13. Щелкните на поле Ограничения и затем - на кнопке Добавить – добавляем ограничения:
$D$7:$D$13>=0,
$D$7:$D$13=целое,
$F$15:$L$15>=$F$17:$L$17.
Во вкладке Параметры устанавливаем флажок- Линейная модель. Нажимаем ОК и анализируем ответ.