СДЕЛАЙТЕ СВОИ УРОКИ ЕЩЁ ЭФФЕКТИВНЕЕ, А ЖИЗНЬ СВОБОДНЕЕ

Благодаря готовым учебным материалам для работы в классе и дистанционно

Выбрать материалы

Скидки до 50 % на комплекты
только до 19.05.2025

Готовые ключевые этапы урока всегда будут у вас под рукой

Организационный момент

Проверка знаний

Объяснение материала

Закрепление изученного

Итоги урока

Обо мне Блог Файлы Тесты Галерея Активность Награды

Планирование работ средствами MS Excel

Категория: Информатика

Нажмите, чтобы узнать подробности

Планирование работ средствами MS Excel

Просмотр содержимого документа
«Планирование работ средствами MS Excel»

Информационные технологии в экономике и управлении

Лабораторная работа №4. Планирование работ средствами MS Excel.

Проектной организации, где работает 6 конструкторов и 4 технолога, поручили выполнить 6 проектов (Проект А, Проект Б и т.д.). Работа над каждым проектом включает два этапа: 1) этап конструкторской подготовки производства (КПП) и 2) этап технологической подготовки производства (ТПП). Необходимо распределить проектировщиков по проектам, назначить даты начала этапов, рассчитать даты завершения этапов. Для простоты планирование осуществляется только на один месяц – май 2005 года.

Накладываемые ограничения.

  • Этап ТПП может начаться только после завершения предыдущего этапа КПП.

  • Над одним проектом может работать не более 4 конструкторов и не более 3 технологов.

  • Все проекты должны завершиться не позднее заданных сроков.

  • Один проектировщик может участвовать в нескольких проектах, но одновременно может работать только над одним проектом.


Требуется:

  1. Ввести данные на рабочие листы Исходные данные, Распределение, Диаграмма Ганта и Зарплата согласно заданию.

  2. Осуществить распределение проектировщиков по проектам.

  3. Составить ведомость на выплату заработной платы.


Задание 1. Формирование исходных данных.


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

  1. Создайте новую книгу MS Excel и сохраните в своей папке под именем Планирование работ_ФИО.

  2. Переименуйте лист Лист1 в лист Исходные данные. Введите данные на лист Исходные данные согласно рис. 1. и нижеприведенным указаниям.

  3. В ячейке C1 задайте проверку вводимых значений. Тип данных – список; Источник данных – Январь; Февраль; Март; Апрель; Май; Июнь; Июль; Август; Сентябрь; Октябрь; Ноябрь; Декабрь (список значений формируется через точку с запятой без пробелов). На вкладке Сообщение для ввода задайте Заголовок Месяц и Сообщение Выберите месяц, для которого создается план работ.

  4. Для ячейки D1 задайте проверку ввода, указав в качестве Источника текст 2004;2005;2006;2007;2008;2009;2010

  5. Введите данные в диапазон ячеек A4:H13. Рассчитайте итоговые значения трудоемкости.

  6. Для удобства дальнейшей работы рекомендуется создавать имена для ячеек и диапазонов ячеек. Создайте имя для диапазона ячеек Н5:Н13. Для этого выделите эти ячейки и щелкните левой кнопкой мыши по полю Имя (слева от строки формул), введите имя Праздники и нажмите клавишу Enter. (ВНИМАНИЕ! Имена вводятся БЕЗ пробелов! Ввод имени завершается нажатием клавиши ENTER!).

  7. Введите данные в диапазон ячеек A14:C17.

  8. Создайте имена: СпецКонструктор для ячейки В16, СпецТехнолог для ячейки В17, ЧислоКонструкторов для ячейки D16, ЧислоТехнологов для ячейки D17, ВсегоПроектировщиков для ячейки D18.

  9. Введите данные в диапазон ячеек A20:B31.

  10. Для диапазона ячеек С22:С31 задайте проверку вводимых значений. Тип данных - Список, Источник = $В$16:$В$17). Введите данные в этот диапазон.

  11. Для этого же диапазона ячеек С22:С31 создайте имя Специальность.

  12. Введите данный в столбец Разряд

  13. Для автоматизации подсчета числа конструкторов в ячейку D16 введите формулу:

Функция СЧЕТЕСЛИ находится в категории Статистические. Для ввода имен удобно использовать клавишу F3.

  1. Аналогичным образом подсчитайте число технологов в ячейке D17.

  2. В ячейке D18 подсчитайте количество всех проектировщиков.

Рис. 1. Лист Исходные данные.

Задание 2. Распределение сотрудников по проектам.


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

  1. Добавьте в книгу Планирование работ_ФИО новый лист и дайте ему имя Распределение. Введите данные согласно рис. 2 и приведенным ниже указаниям.

  2. Чтобы не копировать данные с рабочего листа Исходные данные в диапазон ячеек А3:С12 лист Распределение, введите в ячейку А3 формулу

='Исходные данные'!A22

  1. Скопируйте эту формулу в ячейки диапазона А3:С12.

  2. Выполните сортировку диапазона ячеек A2:C12 по столбцу Специальность по возрастанию.


Рис. 2 Лист Распределение.

  1. Заполните ячейки D2:I2, используя процедуру транспонирования. Для этого на листе Исходные данные выделите ячейки В6:В11 и скопируйте их в буфер обмена. Затем щелкните правой кнопкой мыши по ячейке D2 на листе Распределение и в контекстном меню выберите команду Специальная вставка… В окне Специальная вставка установите флажок Транспонировать и нажмите ОК.

  2. Для проверки ввода в диапазон D3:I12 задайте проверку данных с параметрами Тип данных – Список, Источник - 0;1.

  3. Заполните диапазон ячеек D3:I12 согласно рис. 2 (с учетом накладываемых ограничений - над одним проектом может работать не более 4 конструкторов и не более 3 технологов).

  4. Для ячейки J2 создайте Примечание. Для этого перейдите на вкладку Рецензирование – область Примечания - , либо вызовите команду Вставить примечание из контекстного меню. Введите текст: Количество проектов, в которых участвует работник.

  5. В ячейках J3:J12 подсчитайте сумму по соответствующей строке.

  6. Для подсчета конструкторов, участвующих в проектах, в ячейку D13 введите формулу:

Функция СУММЕСЛИ находится в категории Математические.

  1. Скопируйте формулу на диапазон E13:I13.

  2. Аналогично подсчитайте количество технологов в ячейке D14 и скопируйте ее на диапазон ячеек E14:I14.

  3. Формулы для расчета объема работ в днях в диапазоне ячеек К3:К12 будут вводиться позднее.


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


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

  1. Добавьте в книгу Планирование работ_ФИО новый лист и задайте ему имя Диаграмма Ганта. Введите данные согласно рис. 3 и приведенным ниже указаниям.

  2. Введите название таблицы Диаграмма Ганта в ячейках A1:I1.

  3. Оформите шапку таблицы в ячейках A2:I2.

  4. В столбце A объедините указанные ячейки и заполните их.

  5. В столбце B объедините указанные ячейки без заполнения.

Рис. 3. Лист Диаграмма Ганта

  1. Чтобы автоматизировать заполнение ячеек В3:В14, ни один из ранее рассмотренных способов не подходит. Воспользуемся функцией СМЕЩ из категории Ссылки и массивы. Введите в ячейку В3 формулу:


Примечание:

Функция СМЕЩ возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов. Возвращаемая ссылка может быть отдельной ячейкой или диапазоном ячеек.

Синтаксис функции = СМЕЩ (ССЫЛКА; СМЕЩ_ПО_СТРОКАМ; СМЕЩ_ПО_СТОЛБЦАМ;ВЫСОТА;ШИРИНА)

  • ССЫЛКА – ссылка, от которой вычисляется смещение. Аргумент «ссылка» должен быть ссылкой на ячейку или на диапазон смежных ячеек.

  • СМЕЩ_ПО_СТРОКАМ – количество строк, которые нужно отсчитать вверх или вниз от ссылки. СМЕЩ_ПО_СТРОКАМ может быть положительным (ниже начальной ссылки) или отрицательным (выше начальной ссылки).

  • СМЕЩ_ПО_СТОЛБЦАМ – количество столбцов, которые нужно отсчитать влево или вправо от ссылки. СМЕЩ_ПО_СТОЛБЦАМ может быть положительным (вправо от начальной ссылки) или отрицательным (влево от начальной ссылки).

  • ВЫСОТА – высота (число строк) возвращаемой ссылки. Высота должна быть положительным числом.

  • ШИРИНА – ширина (число столбцов) возвращаемой ссылки. Ширина должна быть положительным числом.

  1. Скопируйте формулу в диапазоне ячеек В3:В14.

  2. В столбце C объедините указанные ячейки и заполните их, используя функцию СМЕЩ. Установите для ячеек формат Дата.

  3. Введите данные в столбец D.

  4. В ячейку Е3 введите формулу:



  1. В ячейку E4 введите формулу:



  1. Выделите эти две формулы и скопируйте их на столбец E.

  2. В ячейку F3 введите формулу:



  1. В ячейку F4 введите формулу:

  1. Выделите эти две формулы и скопируйте их на столбец F.

  2. Для расчета длительности каждого этапа проекта (КПП и ТПП) воспользуемся функцией ОКРУГЛВВЕРХ из категории Математические. В ячейку G3 введите формулу:



Примечание:

Функция ОКРУГЛВВЕРХ округляет число до ближайшего большего по модулю.

Синтаксис функции = ОКРУГЛВВЕРХ (ЧИСЛО;ЧИСЛО_РАЗРЯДОВ)


    • ЧИСЛО – любое вещественное число, которое нужно округлить с избытком.

    • ЧИСЛО_РАЗРЯДОВ – количество цифр, до которого округляется число.


  1. Скопируйте формулу в столбец G.

  2. В диапазон Н3:Н14 введите даты начала работ.

  3. Чтобы найти день завершения этапа в ячейку I3 введите формулу:

  1. Скопируйте формулу в столбец I.

Примечание:


Функция РАБДЕНЬ возвращает дату, отстоящую на заданное количество рабочих дней вперед или назад от начальной даты. Рабочими днями не считаются выходные дни и дни, определенные как праздничные.


Синтаксис функции=РАБДЕНЬ(НАЧ_ДАТА;КОЛИЧЕСТВО_ДНЕЙ;ПРАЗДНИКИ)

  • НАЧ_ДАТА – это начальная дата.

  • КОЛИЧЕСТВО_ДНЕЙ – это количество рабочих дней до или после НАЧ_ДАТА. Положительное значение аргумента КОЛИЧЕСТВО_ДНЕЙ означает будущую дату; отрицательное значение – прошедшую дату.

  • ПРАЗДНИКИ – это необязательный параметр и представляет собой список из одной или нескольких дат, которые требуется исключить из рабочего календаря (например, государственные праздники).

  1. В ячейку J2 введите формулу:


Примечание:

Функция ДАТАЗНАЧ возвращает числовой формат даты, представленной в виде текста. Синтаксис функции =ДАТАЗНАЧ(ДАТА_КАК_ТЕКСТ).

  • ДАТА_КАК_ТЕКСТ – это текст, представляющий дату (например, 30.01.1998).

Оператор & позволяет объединить две текстовые строки в одну строку.


  1. В ячейку К2 введите формулу =J2+1 и скопируйте ее на строку K.

  2. Отформатируем ячейку J2, чтобы кроме даты, был виден день недели. Подходящего встроенного формата не существует. Чтобы создать его, вызовите из контекстного меню Формат ячеек. На вкладке Число выберите Числовые форматы (все форматы), в поле Тип задайте ДД.ММ.ГГ ДДД. Шаблон ДДД отображает день недели в виде Пн, Вт, …, Вс.

  3. Отформатируйте диапазон K2:AN2, используя Формат по образцу .

  4. Для выделения выходных дней желтым цветом в диапазоне ячеек J2:AN2 создайте правило условного форматирования. Для этого выделите ячейку J2 и перейдите на вкладку Главная – Стили – Условное форматирование . Из раскрывающегося списка выберите команду Создать правило.

  5. В диалоговом окне Создание правил форматирования выберите тип правила Использовать формулу для определения форматируемых ячеек. В поле ввода запишите формулу =ЕСЛИ(ДЕНЬНЕД(J2;2)5;1;0) и задайте желтый фон ячейки для выходных дней (см. рис. 4).

Примечание

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



  1. Для диапазона K2:AN2 примените Формат по образцу.

Рис. 4. Окно Создание правил форматирования

  1. Создайте правило условного форматирования для выделения праздничных дней красным цветом. Для этого в поле ввода в диалоговом окне Создание правил форматирования запишите формулу и задайте красный фон ячейки для праздничных дней:

  1. Для диапазона K2:AN2 примените Формат по образцу.

  2. Для того чтобы на диаграмме Ганта были представлено число проектировщиков, участвующих в проекте на данном этапе в ячейку J3 введите формулу:

  1. Скопируйте формулу в диапазон ячеек J3:AN14.

  2. Чтобы выделить цветом дни, когда ведется работа над проектом, а также выделить требуемый день завершения проекта, также создайте правила условного форматирования для этапов КПП и ТПП.



  1. Для этапа КПП (конструкторской подготовки производства) выделите ячейку J3 и создайте три правила:

    Правила условного форматирования для этапов КПП.

    №

    Формула

    Формат

    1

    =ЕСЛИ(И(J$2=$H3;J$2

    задает формат для дней работы над проектом и для последнего допустимого срока

    Граница – красная, фон - желтый

    2

    =ЕСЛИ(И(J$2=$H3;J$2

    формат для дней работы над проектом

    Фон - серый

    3

    =ЕСЛИ(J$2=$C3;1;0)

    задает формат для последнего допустимого срока

    Граница – красная, фон - желтый

  2. Примените формат по образцу для диапазонов ячеек, которые соответствуют этапу КПП: J3:AN3, J5:AN5, J7:AN7, J9:AN9, J11:AN11, J13:AN13.

  3. Создайте аналогичным образом в ячейке J4 правила условного форматирования для этапа ТПП (технологической подготовки производства). Для правила 2 (формат для дней работы над проектом) установите зеленый цвет заливки.

  4. Примените формат по образцу для диапазонов ячеек, которые соответствуют этапу ТПП: J4:AN4, J6:AN6, J8:AN8, J10:AN10, J12:AN12, J14:AN14.

  5. Рассчитайте число конструкторов, занятых во всех проектов на 01.05.05, в ячейке J15 с помощью функции СУММЕСЛИ.

  1. Скопируйте формулу в диапазон ячеек K15:AN15.

  2. Для ячейки J15 задайте условное форматирование согласно рис.5. В диалоговом окне Создание правил форматирования выберите тип правила: Форматировать только ячейки, которые содержат. Правило 1 задает красный цвет заливки, Правило 2 – желтый и Правило 3 – зеленый.

Рис. 5. Окно Условное форматирование для диапазона J15:AN15

  1. Примените формат по образцу для диапазона ячеек J15:AN15.



  1. Самостоятельно задайте формулу в ячейке J16 для подсчета числа технологов, занятых во всех проектов на 01.05.05, а также создайте аналогичные правила условного форматирования для данной ячейки.



  1. Скопируйте формулу в диапазон ячеек K16:AN16 и примените к нему формат по образцу.



  1. В ячейке J17 рассчитайте, сколько всего занято в работе проектировщиков на 01.05.05. Задайте условия форматирования. Скопируйте в диапазон ячеек K17:AN17.


Задание 4. Построение плана-графика работы сотрудников.


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

  1. Для построения план-графика работы каждого сотрудника введите данные в диапазон D19:AN26 согласно следующим указаниям.

  2. Создайте имя Сотрудники для диапазона 'Исходные данные'!B22:B31.

  3. Для ячейки F20 задайте проверку вводимых значений. Тип данных Список, Источник =Сотрудники.

  4. В ячейку F21 введите формулу:

Функция ВПР() (Вертикальный Просмотр) позволит по заданной ФИО проектировщика (ячейка F20) установить его специальность, просмотрев таблицу 'Исходные данные'!B22:C31.

  1. В ячейку G20 введите формулу: ='Исходные данные'!A6 и скопируйте ее на диапазон ячеек G20:Н25.

  2. В ячейку I20 введите формулу:

Она позволяет извлечь информацию об участии проектировщика в конкретном проекте (0 – не участвует, 1 – участвует).

  1. Скопируйте формулу в диапазон ячеек I21:I25.

  2. В ячейку J20 введите формулу:

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

  1. Для ячейки J20 задайте условное форматирование согласно рис. 6.

Рис. 6. Окно Условное форматирование для диапазона J20:AN25

  1. Скопируйте формулу в диапазон ячеек J20:AN25.

  2. В ячейке J26 с помощью функции СЧЁТ рассчитайте число проектов, в которых участвует сотрудник в этот день. Задайте условное форматирование, сигнализирующее красным цветом ячеек, что число проектов больше 1.

  3. Скопируйте формулу в диапазон ячеек J26:AN26.

  4. Вернемся к формуле в ячейке I3. Если дата начала работ равна 01.05.05, то на диаграмме Ганта возникает ошибка – при длительности работы в пять дней, на диаграмме работа занимает четыре рабочих дня. Ошибка связана с особенностями работы функции РАБДЕНЬ(). Введите в ячейку I3 «подправленную» формулу =РАБДЕНЬ(H3-1; G3; Праздники) и размножьте ее по столбцу.

  5. Чтобы защитить лист Исходные данные перейдите на вкладку Рецензирование – область Изменения – . В окне Защитить лист введите пароль (можно не вводить), чтобы избежать несанкционированного изменения формул. Можно защитить ячейки, листы или всю книгу в целом.

  6. Чтобы защитить лист Распределение за исключением ячеек D3:I12, в которые будут вводиться данные, выделите диапазон ячеек D3:I12 и вызовите диалоговое окно Формат ячеек. На вкладке Защита сбросьте флажок Защищаемая ячейка. Нажмите кнопку ОК. Затем защитите лист Распределение.

  7. Защитите лист Диаграмма Ганта за исключением ячеек H3:H14.


Задание 4. Расчет заработной платы.

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

На основе полученного плана работ рассчитаем заработную плату каждого работника согласно формуле:


  1. Перейдите на лист Распределение и создайте вспомогательную таблицу для расчета объема работ в днях (рис.7). Не забудьте снять защиту листа .

Рис. 7. Таблица длительностей этапов проектов

  1. В ячейку M3 введите формулу ='Исходные данные'!A6 и скопируйте ее на диапазон ячеек M3:N8.

  2. В ячейку О3 введите формулу =СМЕЩ('Диаграмма Ганта'!$G$3;2*(M3-1);0).

  3. В ячейку Р3 введите формулу =СМЕЩ('Диаграмма Ганта'!$G$3;2*M3-1;0)

  4. Размножьте формулы по таблице.

  5. Создайте имя для диапазона О3:О8 - Этап_КПП, а для диапазона Р3:Р8 - Этап_ТПП.

  6. В ячейку К3 введите формулу:

  1. Размножьте формулу по столбцу.

  2. Для расчета зарплаты введите данные на лист Исходные данные согласно рис. 8 и нижеприведенным указаниям.


Рис. 8. Тарифная сетка

  1. Для ячейки Е33 создайте имя ДневнаяТарифнаяСтавка.

  2. В ячейку D36 введите формулу =C36*ДневнаяТарифнаяСтавка и размножьте ее по столбцу.

  3. Создайте лист Зарплата. Введите данные согласно рис. 9.

Рис. 9. Ведомость на выдачу зарплаты за май 2005 года

  1. В ячейку А1 введите формулу ="Ведомость на выдачу зарплаты за "&'Исходные данные'!C1&" "&'Исходные данные'!D1

  2. В ячейку А3 введите формулу ='Исходные данные'!A22 и скопируйте ее на диапазон ячеек A3:D12.

  3. В ячейку Е3 введите формулу =ВПР(B3;Распределение!$B$3:$K$12;10;0)

  4. В ячейку F3 введите формулу =E3*ВПР(D3;'Исходные данные'!$B$36:$D$53;3;1).

  5. Размножьте формулы по столбцам.


ВЫВОДЫ.


Полученное решение не удовлетворяет условиям задачи. Например, Петров С.И. одновременно участвует в проектах Г, Д и Е; в отдельные дни (6 мая и с 12 по 16 мая) будет не хватать конструкторов. Поэтому необходимо скорректировать разработанный план работ.

Пользуясь созданными таблицами, перераспределите сотрудников по проектам, назначьте новые даты начала работ по этапам. Изменяйте данные только в диапазонах ячеек Распределение!D3:I12 и 'Диаграмма Ганта'!H3:H14. Для проверки того, что план-график работы сотрудника удовлетворяет заданным ограничением, используйте ячейку J20. Для упрощения распределения сотрудников разбейте их на группы по 2-4 человека и переводите эту группу с одного проекта на другой.


10

Видеоуроки по информатике 6 класс (к...

Электронная тетрадь по информатике 5...

Информатика 5 класс ФГОС

Информатика 6 класс ФГОС

Информатика 7 класс ФГОС

Информатика 5 класс (Россия)

Информатика 3 класс

Информатика 10 класс (Россия)
Скачать

© 2021, 1371 19

Рекомендуем курсы ПК и ППК для учителей

Внедрение современных педагогических технологий в условиях реализации...
800 руб.  4000 руб.
Учитель, преподаватель физики и информатики
3560 руб.  17800 руб.
Учитель, преподаватель информатики в начальной школе
2760 руб.  13800 руб.
Система работы с высокомотивированными и одаренными учащимися по...
800 руб.  4000 руб.
Специфика преподавания дисциплины «Информационные технологии» в...
800 руб.  4000 руб.
Основы HTML
800 руб.  4000 руб.

Похожие файлы

0 Нет комментариев

Вебинар для учителей

Свидетельство об участии БЕСПЛАТНО!

Полезное для учителя

Новости проекта
03.06.24

Лайфхак учителя №1! Что делать с плохой накопляемостью отметок

12.05.24

Лайфхак учителя №2! Как перестать бегать от ученика к ученику во время практической работы

04.05.24

Лайфхак учителя №3! Что делать, если вы устали из урока в урок повторять одно и то же

Курсы для учителей!
Реализация образовательных программ осуществляется с применением исключительно электронного обучения и ДОТ
Применение облачных технологий в образовании
800 руб.
4000 руб.
Современные педагогические технологии в образовательном процессе
800 руб.
4000 руб.
Учитель, преподаватель информатики
2760 руб.
13800 руб.
Смотреть все курсы


Поделитесь с друзьями
ВКонтактеОдноклассникиTwitterМой МирLiveJournalGoogle PlusЯндекс