Найдите и прочитайте описание функции СМЕЩ() (категория Ссылки и массивы).
Самостоятельно введите формулы в ячейки С3:С14, предварительно объединив их по 2.
В ячейку Е3 введите формулу: =СМЕЩ('Исходные данные'!$ D $6; A 3-1;0)
В ячейку Е4 введите формулу: =СМЕЩ('Исходные данные'!$ D $6; A 3-1;1)
Растяните эти формулы по столбцу Е.
В ячейку F3 введите формулу: =СМЕЩ(Распределение,.$D$13;0;A3-1)
В ячейку F4 введите формулу: =СМЕЩ(Распределение,.$D$13;1;A3-1)
Растяните эти формулы по столбцу F.
В ячейку G3 введите формулу: =ОКРУГЛВВЕРХ(Е3/F3;0)
Растяните эту формулу по столбцу G.
В диапазон Н3:Н14 введите даты начала работ.
Чтобы рассчитать день завершения этапа, используем функцию РАБДЕНЬ(). Она возвращает дату, отстоящую на заданное количество рабочих дней вперед или назад от даты Нач_дата. Рабочими днями не считаются выходные дни и дни, определенные как праздничные. Функция РАБДЕНЬ() используется, чтобы исключить выходные дни или праздники при вычислении даты завершения этапа.
Синтаксис функции РАБДЕНЬ(Нач_дата; Количество_дней; Праздники)
Нач_дата - это начальная дата.
Количество_дней - это количество рабочих дней до или после Нач_дата. Положительное значение аргумента Количество_дней означает будущую дату; отрицательное значение - прошедшую дату.
Праздники - это необязательный параметр и представляет собой список из одной или нескольких дат, которые требуется исключить из рабочего календаря (например, государственные праздники).
Чтобы найти день завершения этапа в ячейку I3 введите формулу:
=РАБДЕНЬ(Н3-1; G 3;Праздники). Растяните формулу по столбцу I.
В ячейку J2 введите формулу:
=ДАТАЗНАЧ("1 "&'Исходные данные '! C 1& 'Исходные данные ’! D 1)
Функция ДАТАЗНАЧ() возвращает числовой формат даты, представленной в виде текста.
Синтаксис функции ДАТАЗНАЧ(Дата_как_текст)
Дата_как_текст - это текст, представляющий дату (например, 30.01.1998).
Оператор & позволяет объединить две текстовые строки в одну строку.
В ячейку К2 введите формулу: =J2+1 и размножьте ее по строке.
Отформатируем ячейку J2, чтобы кроме даты, был виден день недели. Подходящего встроенного формата не существует. Чтобы создать его, выполните команду Формат ячеек... На вкладке Число выберите Числовые форматы (все форматы), в поле Тип задайте ДД.ММ.ГГ ДДД.
Шаблон ДДД отображает день недели в виде Пн, Вт, . .. , Вс.
Чтобы отформатировать диапазон J2:AN2, скопируйте формат из ячейки J2 в остальные ячейки диапазона
Чтобы выделить цветом выходные и праздничные дни, воспользуемся условным форматированием. Выделите ячейку J2 и выполните команду Главная → Стили → Условное форматирование → Создать правило .. . → Использовать формулу для определения форматируемых ячеек. В строке Форматировать значения, для которых следующая формула является истинной введите формулу:
=ЕСЛИ(ДЕНЬНЕД(J2;2)>5;1;0).
Это условие задает формат для выходных дней (с помощью кнопки Формат... задайте желтый цвет заливки ячеек). Щелкните по кнопке ОК.
Повторите для ячейки J2 условное форматирование еще раз, но задайте другую формулу:
=ЕСЛИ(ЕНД(ПОИСКПОЗ( J 2;Праздники;0));0;1).
Это условие задает формат для праздничных дней (задайте зеленый цвет заливки ячеек). Щелкните по кнопке ОК.
Чтобы проверить правильность заданных условий, выполните команду Главная → Стили → Условное форматирование → Управление правилами... Если все сделано верно, то Вы увидите окно, как на рисунке 1.4.
При вводе формул в окне Создание правила форматирования удобнее не вводить формулы, а вставлять их из буфера обмена, предварительно набрав и отладив в какой-либо ячейке. Для копирования формулы выделите ячейку, затем В СТРОКЕ ФОРМУЛ выделите формулу и скопируйте ее в буфер обмена (кнопка Копировать). В окне Создание правила форматирования в нужном месте выполните команду Вставить (кнопка Вставить)
Рисунок 1.4 - Окно Диспетчер правил условного форматирования
для диапазона J2:AN2
Скопируйте созданный формат из ячейки J2 в остальные ячейки строки.
Чтобы на диаграмме Ганта были представлено число проектировщиков, участвующих в проекте на данном этапе, в ячейку J3 введите формулу:
=ЕСЛИ(И(J$2>=$Н3;J$2<=$I3;$F3;"")
Найдите и прочитайте описание функции И() (категория Логические).
Размножьте формулу на диапазон J3:AN14.
Чтобы выделить цветом дни, когда ведется работа над проектом, а также выделить требуемый день завершения проекта, воспользуемся условным форматированием. Для ячейки J3 задайте следующие правила условного форматирования:
=ЕСЛИ(И(J$2>=$H$3;J$2<=$I$3;J$2=$C$3);1;0)
Это Условие 1 задает формат для дней работы над проектом и для последнего допустимого срока (задайте красную границу ячейки и песочный цвет заливки).
=ЕСЛИ(И(J$2>=$H$3;J$2<=$I$3);1;0)
Условие 2 задает формат для дней работы над проектом (задайте серый цвет заливки для этапов КПП).
=ЕСЛИ(J$2=$C$3;1;0)
Условие 3 задает формат для последнего допустимого срока (повторите формат для Условия 1 ).
Если все сделано правильно, то при проверке правил в ячейке J3 Вы увидите окно, представленное на рисунке 1.5.
Рисунок 1.5-Окно Диспетчер правил условного форматирования
для диапазона J3:AN14
Скопируйте созданный формат из ячейки J3 в диапазон J3:AN3, а также в диапазоны J5:AN5, J7:AN7, J9:АN9, J11:AN11, J13:AN13. Для диапазона J5:AN5 формулы будут иметь вид:
=ЕСЛИ(И (J$2>=$H$5;J$2<=$I$5;J$2=$C$5);1;0);
=ЕСЛИ(И(J$2>=$H$5;J$2<=$I$5);1;0);
=ЕСЛИ(J$2=$C$5;1;0).
Задайте условное форматирование для ячейки J4:
=ЕСЛИ(И(J$2>=$Н$4;J$2<=$I$4;J$2=$C$3);1;0);
=ЕСЛИ(И(J$2>=$H$4;J$2<=$I$4);1;0);
=ЕСЛИ(J$2=$C$3;1;0).
Все 3 условия будут иметь вид, как для ячейки J3, только во 2 условии цвет будет вместо серого зеленый для этапов ТПП. Скопируйте созданный формат в диапазон J4:AN4, а также в диапазоны J6:AN6, J8:AN8, J10:AN10, J12:AN12, J14:AN14.
Чтобы подсчитать число всех конструкторов, работающих в определенный день, воспользуемся Мастером суммирования.
Для активизации Мастера суммирования щелкните по кнопке Office, выберите Параметры Excel, перейдите на вкладку Надстройки, выберите Мастер суммирования и щелкните по кнопке Перейти внизу окна. В открывшемся окне Надстройки установите флажок напротив строки Мастер суммирования и щелкните по кнопке ОК. Ждите установки Мастера суммирования.
Выполните команду Формулы → Решения → Частичная сумма. На шаге 1 укажите, где находится таблица для суммирования 'Диаграмма Ганma'!$D$2:$AN$14. Нажмите кнопку Далее. На шаге 2 задайте Суммировать 01.05.12 Вт , Столбец Этап, Оператор =, Значение КПП и затем нажмите кнопку Добавить условие. Нажмите кнопку Далее. На шаге 3 нажмите кнопку Далее. На шаге 4 выберите ячейку J15 и нажмите кнопку Готово. В результате в ячейке J15 находится формула массива: {=СУММ(ЕСЛИ($D$3:$D$14="КПП ";J$3:J$14;0))}
Если формула отличается от указанной, чтобы отредактировать формулу массива, после редактирования нажмите одновременно клавиши Ctrl, Shift и Enter.
Для ячейки J15 задайте условное форматирование согласно рисунку 1.6.
Рисунок 1.6 - Окно Диспетчер правил условного форматирования
для диапазона J15:AN15
Самостоятельно задайте формулы и форматирование для остальных ячеек диапазона J15:AN16.
В ячейке J17 найдите сумму ячеек J15 и J16. Задайте условия форматирования.
Для построения план-графика работы каждого сотрудника введите данные в диапазон D19:AN26 согласно следующим указаниям.
Создайте имя Сотрудники для диапазона 'Исходные данные'!B22:B31.
Для ячейки F20 задайте проверку вводимых значений (Тип данных Список, Источник =Сотрудники.
В ячейку F21 введите формулу:
=BПP(F20;’Исходные данные’!B22:C31;2;0)
Функция ВПР() позволит по заданной ФИО проектировщика (ячейка F20) установить его специальность, просмотрев таблицу 'Исходные данные'!B22:C31.
Заполните ячейки G19:H25 в соответствии с рисунком 1.3.
В ячейку I20 введите формулу
=ВПР($F$20;Распределение!$B$3:$I$12; G20+2;0)
Она позволяет извлечь информацию об участии проектировщика в конкретном проекте (0 - не участвует, 1 - участвует).
В ячейку J20 введите формулу
=ЕСЛИ($I20= 1;СМЕЩ(J$3;ЕСЛИ($F$21=СпецКонструктор;2*($G20-1);
2*$G20-1);0);"")
Она позволяет скопировать нужную информацию из вышележащей таблицы о числе конструкторов или технологов, участвующих в проекте в этот день. Для ячейки J20 задайте условное форматирование согласно рисунку 1.7.
Рисунок 1.7 - Окно Создание правила форматирования
для диапазона J20:AN25
В ячейку J26 введите формулу:
=СЧЁТ(J20:J25), подсчитывающую число проектов, в которых участвует сотрудник в этот день. Задайте условное форматирование, сигнализирующее красным цветом ячеек, что число проектов больше 1.
Размножьте введенные формулы по соответствующим диапазонам.
На основе полученного плана работ рассчитаем заработную плату каждого работника согласно формуле:
Зарплата работника = Объем работ в днях * Дневная тарифная ставка
Чтобы рассчитать объем работ в днях, перейдите на лист Распределение и создайте вспомогательную таблицу согласно рисунку 1.8 и приведенным ниже указаниям.
Рисунок 1.8 - Таблица длительностей этапов проектов
В ячейку О3 введите формулу:
=СМЕЩ(Диаграмма Ганта'!$ G $3;2*( M 3-1);0)
В ячейку Р3 введите формулу:
=СМЕЩ(Диаграмма Ганта'!$ G $3;2* M 3- 1;0)
Размножьте формулы по таблице.
Создадим имя для диапазона О3:О8. Выделите ячейки О3:О8. Выполните команду Формулы → Определенные имена → Присвоить имя, и в окне Создание имени нажмите кнопку ОК.
В результате автоматически будет создано имя Этап_КПП.
Самостоятельно создайте имя Этап_ТПП для диапазона Р3:Р8.
В ячейку К3 введите формулу:
=МУМНОЖ(D3:I3;ЕСЛИ(C3=СпецКонсmрукmор;Эmап_КПП;Эmап_ТПП))
Размножьте формулу по столбцу.
Для расчета зарплаты введите данные на лист Исходные данные согласно Рисуноку 1.9 и приведенным ниже указаниям.
Рисунок 1.9 - Тарифная сетка
Для ячейки Е33 создайте имя ДневнаяТарифнаяСтавка.
В ячейку D36 введите формулу:
=С36*ДневнаяТарифнаяСтавка и размножьте ее по столбцу.
1. Создайте лист Зарплата. Введите данные согласно рисунку 1.10 и приведенным ниже указаниям.
В ячейку А1 введите формулу
="Ведомость на выдачу зарплаты за "&Исходные данные'!С1&" "&Исходные данные'D1&" г. "
Ячейки А3:С12 заполните путем ссылок на соответствующие ячейки листа Исходные данные.
Ячейки D3:D12 заполните с помощью копирования соответствующих значений с листа Исходные данные.
В ячейку Е3 введите формулу:
=ВПР(В3;Распределение!$В$3:$К$12;10;0)
В ячейку F3 введите формулу:
=Е3*ВПР(D3;'Исходные данные'!$B$36:$D$53;3;1) Размножьте формулы по столбцам.
Рисунок 1.10- Ведомость на выдачу зарплаты за май 2012 года
Полученное решение не удовлетворяет условиям задачи. Например, Петров С.И. одновременно участвует в проектах Г, Д и Е; в отдельные дни (3 мая, 4 мая и с 12 по 16 мая) будет не хватать конструкторов. Поэтому необходимо скорректировать разработанный план работ.
2. Пользуясь созданными таблицами, перераспределите сотрудников по проектам, назначьте новые даты начала работ по этапам.
Изменяйте данные только в диапазонах ячеек Расnределение!D3:l12 и 'Диаграмма Ганта'!Н3:Н14. Для проверки того, что план-график работы сотрудника удовлетворяет заданным ограничением, используйте ячейку J 20.
Для упрощения распределения сотрудников разбейте их на группы по 2-4 человека и переводите эту группу с одного проекта на другой.
Для имитации диаграммы Ганта в MS Excel можно воспользоваться линейчатой диаграммой с накоплением, особым образом настроенной.
Построение такой диаграммы можно условно разбить на несколько этапов:
1. Подготовка таблицы исходных данных.
Для построения диаграммы потребуется таблица с информацией о сроках каждого этапа следующего вида:
2. Построение диаграммы дат начала
Сначала построим линейчатую диаграмму с накоплением для дат начала этапов:
3. Добавление длительностей
Теперь добавим к полученной диаграмме длительности каждого этапа:
4. Настройка внешнего вида
На диаграмме Ганта отображаются начало, длительность и конец каждого этапа. Поэтому осталось убрать синие столбцы. Для этого их надо обесцветить: выделить ряды синих данных и убрать заливку. В итоге получаем:
