1. Применение средств автоматизации ввода данных.
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.
2. Применение итоговых функций
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.