Создание динамической модели календаря с помощью именованных констант в Microsoft Excel

Министерство образования и науки Российской Федерации Санкт-Петербургский государственный архитектурно-строительный университет Факультет экономики и управления Кафедра управления Курсовой проект по дисциплине «Информационные технологии в управлении»

Выполнила: студентка группы 3 -М-2

Велиханова М.Н.

Руководитель: Недобенко В.К.

Санкт-Петербург

2013 г Содержание Введение Создание динамической модели табеля учета рабочего времени Создание динамической модели календаря с помощью именованных констант Заключение Введение Актуальность темы: главным направлением перестройки менеджмента и его радикального усовершенствования, приспособления к современным условиям стало массовое использование новейшей компьютерной и телекоммуникационной техники, формирование на ее основе высокоэффективных информационно-управленческих технологий. Средства и методы прикладной информатики используются в менеджменте и маркетинге. Новые технологии, основанные на компьютерной технике, требуют радикальных изменений организационных структур менеджмента, его регламента, кадрового потенциала, системы документации, фиксирования и передачи информации. Особое значение имеет внедрение информационного менеджмента, значительно расширяющее возможности использования компаниями информационных ресурсов. Развитие информационного менеджмента связано с организацией системы обработки данных и знаний, последовательного их развития до уровня интегрированных автоматизированных систем управления, охватывающих по вертикали и горизонтали все уровни и звенья производства и сбыта.

Повышение эффективности управленческой деятельности становится одним из направлений совершенствования деятельности предприятия в целом. Наиболее очевидным способом повышения эффективности протекания трудового процесса является его автоматизация.

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

Создание динамической модели табеля учета рабочего времени Алгоритм.

В2: создать счетчик с 1901 по 2012.

С2: создать счетчик от 1 до 12.

Разработчик / Вставить / Счетчик (элемент управления формы).

Р6:Y6 =ДАТА (B2;C2;1). Формат ячейки / все форматы / ММММ.ГГГГ.

Функция ДАТА возвращает целое число, представляющее определенную дату. Например, формула

=ДАТА (2008;7;8)

возвращает 39 637, последовательное число, которое представляет дату 08.07.2008.

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

Функция ДАТА полезна в тех случаях, когда год, месяц и день представлены формулами и ссылками на ячейки. Например, на листе могут находиться даты в формате, который Microsoft Excel не распознает (например, в формате ГГГГММДД). Для преобразования дат в числа, которые Microsoft Excel распознает, можно использовать функцию ДАТА в сочетании с другими функциями.

Создать таблицу

D7 =ДАТА ($B$ 2;$C$ 2;СТОЛБЕЦ ()-3). Формат ДД. Протягиваем до 31.

С4: Дни недели.

D4 =ДЕНЬНЕД (D7;2), 2 — тип возврата для номеров и дней недели для России, что означает, что первый день недели — понедельник, протаскиваем по горизонтали.

ДЕНЬНЕД возвращает день недели, соответствующий дате. По умолчанию день недели определяется как целое число в интервале от 1 (воскресенье) до 7 (суббота).

ДЕНЬНЕД (дата_в_числовом_формате,[тип])

ПРИМЕР:

Ставим условный формат для выходных дней: =D4>5. Заливаем красным цветом.

Для D8: AH17 мы применяем условный формат =ДЕНЬНЕД (D$ 4)>5

BJ1:BJ15 вводим цифры от 1 до 15. BJ16: BJ20 вводим буквы: «б», «к», «о», «п», которые означают больничный, командировку, отпуск и прогул соответственно.

Выделяем область D8: AH17 / Данные / Проверка / Список, Источник: $BJ$ 1:$BJ$ 20.

Теперь можно заполнить поля в табеле через выпадающие списки.

В А7 вводим формулу: =АДРЕС (СТРОКА ();4)&" :" &АДРЕС (СТРОКА ();$D$ 3)

Протягиваем до А17.

Функцию АДРЕС можно использовать для получения адреса ячейки на листе по номерам строки и столбца. Например, функция АДРЕС (2;3) возвращает значение $C$ 2. Еще один пример: функция АДРЕС (77;300) возвращает значение $KN$ 77. Чтобы передать функции АДРЕС номера строки и столбца, в качестве ее аргументов (Аргумент. Значение, предоставляющее информацию для действия, события, метода, свойства, функции или процедуры.) можно использовать другие функции (например, функции СТРОКА и СТОЛБЕЦ).

ПРИМЕР:

Под табелем в ячейках А24: D34 создаем новую таблицу. Вводим номер месяцев, дней и названия праздников. В графу дата вводим в формулу =ДАТА ($B$ 2;B24;C24). В2 — год, В24 — ссылка на месяц, С24 -день.

Выделяем диапазон А24: А34 и присваиваем ему имя «Праздники».

Выделяем диапазон D5: AH5 / Условное форматирование / Создать правило / =D$ 5=1 и заливаем зеленым цветом.

То же проделываем для диапазона D8: AH17.

В итоге выходные дни обозначены розовым цветом, а праздники зеленым.

Когда выходные дни совпадают с праздниками необходимо сделать следующее: выделяем диапазон D8: AH17 и создаем условие =И (D$ 4>5;D$ 5=1). Цвет выбираем синий.

В диапазоне AL6: AX7 создаем таблицу:

Вводим формулы в ячейки *8 и протягиваем до *17.

Отработано дней: =СЧЁТ (ДВССЫЛ (A8)) вводим в А18, протягиваем до AI17

Пропущено по болезни: =СЧЁТЕСЛИ (ДВССЫЛ (A8);" б")

Пропущено — командировки: =СЧЁТЕСЛИ (ДВССЫЛ (A8);" к")

Пропущено — отпуск: =СЧЁТЕСЛИ (ДВССЫЛ (A8);" о")

Пропущено по прогулам: =СЧЁТЕСЛИ (ДВССЫЛ (A8);" п")

Выходные дни: =СЧИТАТЬПУСТОТЫ (ДВССЫЛ (A8)) > Ctrl + Shift + Enter

Всего часов: =СУММ (ДВССЫЛ (A8))

Сумма всех чисел >8: =СУММЕСЛИ (ДВССЫЛ (A8);" >8″)

Количество чисел >8: =СЧЁТЕСЛИ (ДВССЫЛ (A8);" >8″)*8

Сверхурочные часы: =AP8-AQ8

Рабочие часы в выходные дни: =СУММ (ЕСЛИ (ДВССЫЛ ($A$ 4)>5;ДВССЫЛ (A8))) > Ctrl + Shift + Enter

Рабочие часы в праздники: =СУММ (ЕСЛИ (ДВССЫЛ ($A$ 7)=праздники; ДВССЫЛ (A8))) > Ctrl + Shift + Enter

ЗП без доплат: =AO8*B8

Доплата за сверхурочные: =(AR8*B8)/2

Доплата за праздники: =AT8*B8*2

ИТОГО: =СУММ (AU8:AW8)

Чтобы подготовить работу к печати следуем инструкции:

выделяем область печати / файл / параметры / панель быстрого доступа / задать.

Задаем в нижнем колонтитуле ФИО и дату.

Создание динамической модели календаря с помощью именованных констант Алгоритм В ячейке С2 пишем «Пн» и растягиваем до AL2.

Далее диапазон С2: AL14 пронумеруем по горизонтали от 1 до 35.

Создаем счетчик: Разработчик / Вставить / Счетчик Щелкаем ПКМ на счетчике, выбираем «формат объекта» и в диалоговом окне устанавливаем минимальное значение 1900, максимальное — 3000. Связываем с ячейкой В1.

Чтобы вычислить даты понедельников для первых недель каждого месяца выбранного года, нужно в ячейку А3 ввести формулу: =ДАТА ($B$ 1;СТРОКА (3:14);1)-ДЕНЬНЕД (ДАТА ($B$ 1;СТРОКА (3:14);1);3)

Протягиваем до А14 и присваиваем этому диапазону имя «понедельники». календарь константа учет счетчик Преобразовываем таблицу в диапазоне С2: AL14 в константу: копируем таблицу ниже, удерживая ПКМ в диапазоне С17: AL14 / выделяем таблицу, и после «=» выделить диапазон первой таблицы / Ctrl+Shift+Enter / на строке формул 2 таблицы нажать F9 / Скопировать запись линейной таблицы / Присвоить имя / В Строке «Имя» ввести фамилию + слово Год, в строку «Диапазон» вставить скопированную линейную запись таблицы.

Затем нужно суммировать в диапазоне С17: AL14 созданную константу с именованным массивом «Понедельники». Для этого вводим формулу «=велихановагод + понедельники» и нажимаем Ctrl+Shift+Enter.

Меняем формат ячеек: ПКМ / формат ячеек / все форматы/ ДД Дописываем дни недели, месяцы, и нумерацию месяцев:

Далее делаем даты «не своего месяца» тусклыми.

Условное форматирование / создать правило / использовать формулу для определения форматируемых ячеек / вводим формулу =МЕСЯЦ (C17)<>$A17, и выбираем серый шрифт.

То же проделываем, чтобы выделить выходные дни. Вводим формулу =ДЕНЬНЕД (C17;2)>5.

Чтобы подготовить к печати: выделяем область печати / файл / параметры / панель быстрого доступа / задать. Создать нижний колонтитул, указав в центре путь к файлу, дату и время, фамилию.

Заключение

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