Рис. 7
- Для ячеек столбца Код подразделения предусмотреть ввод данных из списка
значений - блока Код_подразделения, для этого использовать возможности предоставляемые
командой Данные – Работа с данными
- Проверка данных
- Для ячеек столбца Должность предусмотреть ввод данных из списка
значений - блока Должности, для этого использовать возможности предоставляемые
командой Данные – Работа с данными - Проверка данных
- Для ячеек столбца Образование предусмотреть ввод данных из списка значений - блока Образование, для этого использовать возможности предоставляемые
командой Данные – Работа с данными - Проверка данных
- Для ячеек столбца Тип психики предусмотреть ввод данных из списка
значений - блока Тип_психики, для этого использовать возможности предоставляемые командой Данные – Работа с данными
- Проверка данных
- Добавить в таблицу Сотрудники справа столбец с именем Оклад.
- Добавить в таблицу Сотрудники справа столбец с именем Тариф за час.
- На листе Штатное расписание выделить
в таблице Штатное расписание три столбца
со значениями: Должности, Количество должностей,
Оклад скопировать и вставить справа от таблицы, отделив хотя бы одним столбцом от исходной таблицы, таким образом создать вспомогательную таблицу штатного расписания, которую отсортировать по возрастанию
по значению поля должность.
- Вставить формулу расчёта для значения Оклад. Использовать функцию ВПР(Искомое значение;Таблица;Номер столбца). Аргументы
функции ВПР обозначают следующее:
- Искомое значение – это адрес
ячейки в таблице Персонал со значением Должности – десятый столбец;
- Таблица – это диапазон ячеек,
занимаемый вспомогательной таблицей
штатного расписания, которая содержит копию трёх столбцов
таблицы Штатное расписание (должность,
количество должностей, оклад), и отсортирована
по полю Должности от А до Я. Обязательно
задайте адрес диапазона в абсолютной
адресации с помощью клавиши F4;
- Номер столбца – это номер
столбца (3) с окладом во вспомогательной
таблице штатного расписания, которое вернёт функция ВПР в формулу
расчёта тарифа за час.
- Вставить формулу расчёта для значения Тариф за час. Использовать функцию ВПР(Искомое значение;Таблица;Номер столбца)*12/(262*8). Аргументы
функции ВПР обозначают следующее:
- Искомое значение – это адрес
ячейки в таблице Персонал со значением Должности – десятый столбец;
- Таблица – это диапазон ячеек,
занимаемый вспомогательной таблицей
штатного расписания, обязательно
задайте адрес диапазона в
абсолютной адресации с помощью
клавиши F4;
- Номер столбца – это номер
столбца (3) с окладом во вспомогательной таблице
штатного расписания, которое вернёт функция
ВПР в формулу расчёта тарифа за час.
Оклад умножаем на 12 месяцев и делим на
количество рабочих дней в году (262), умноженное
на 8 рабочих часов.
- Создать именованный блок со значениями ячеек таблицы с именем Персонал.
Лист Табель учёта работы
Лист содержит список или базу данных EXCEL- прямоугольную таблицу,
для которой выполняются следующие требования:
- Первая строка таблицы содержит имена
полей базы данных;
- Данные базы данных образуют логически связанную совокупность
полей в записях;
- Обеспечена регулярность структуры данных
базы данных (все ячейки одного столбца
имеют одинаковый формат данных и характер заполнения);
- Таблица отделяется от прочей информации
на текущем листе по крайней мере 1 строкой и/или 1 столбцом (рекомендуется
не размещать другой информации на листе
базы данных).
- Переименовать Лист 1 в лист Табель учёта работы.
- Создать структуру базы данных:
Дата работы |
Код подразделения |
Код работника |
Количество часов работы |
Тариф за час работы |
Код работы |
Количество выполненной работы |
Тариф за единицу работы |
Оценка качества работы |
Стоимость работника |
Стоимость работы |
| |
|
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
Рис. 8
- Для ячеек столбца Дата работы предусмотреть ввод дат диапазона значений
01.01.12-31.12.12
- Для ячеек столбца Количество часов работы предусмотреть ввод чисел диапазона
значений 0 -12
- Для ячеек столбца Количество выполненной работы предусмотреть ввод целых чисел
- Для ячеек столбца Код подразделения предусмотреть ввод данных из списка значений - блока Код_подразделения, для этого использовать возможности предоставляемые
командой Данные – Работа с данными
- Проверка данных
- Для ячеек столбца Код работника предусмотреть ввод данных из списка
значений - блока Код_ работника, для этого использовать возможности предоставляемые
командой Данные – Работа с данными
- Проверка данных
- Для ячеек столбца Код работы предусмотреть ввод данных из списка
значений - блока Код_работы, для этого использовать возможности предоставляемые
командой Данные – Работа с данными
- Проверка данных
- Тариф за час работы выбирается из таблицы Персонал с помощью функции ВПР(адрес ячейки со значением Код работника; именованный блок Персонал; номер столбца (14), в котором находится возвращаемое функцией значение из таблицы Персонал (Тариф за час)).
- Тариф за единицу работы выбирается из
таблицы Справочник работы, который предварительно отсортировать по возрастанию значения поля Код работы, с помощью функции ВПР(адрес ячейки со значением Код работы; абсолютный адрес диапазона значений таблицы Справочник работы; номер столбца (3), в котором находится возвращаемое функцией значение из таблицы Справочник работы). Справочник работ должен быть предварительно отсортирован по полю Код работы от А до Я.
- Оценка качества работы может содержать значения в диапазоне
от 0 до 1. Если значение 1, то работа закончена и прошла проверку на
качество. Если значение 0, то работа не
прошла проверку на качество (брак). Другие
значения оценивают долю выполнения от
полного объёма.
- Ввести в первую строку последних столбцов таблицы формулы расчета показателей:
Стоимость работника = Тариф за час работы * Количество
часов работы
Стоимость работы = Тариф за единицу
работы * Количество выполненной работы
* Оценка качества работы
- Добавьте столбец справа с именем Рентабельность и вычислите
значение по формуле = Стоимость работы/ Стоимость работника
- Установите формат ячеек для денежных
значений – денежный, для рентабельности – процентный, без десятичных знаков.
- Введенные условия проверки вводимых
данных можно распространять на другие ячейки
таблицы. Для этого следует:
- Выделить ячейку, содержащую условия
проверки;
- Выполнить команду меню Главная, Копировать;
- Выделить ячейки, которым нужно приписать
данные условия проверки;
- Выполнить команду меню Главная, Вставка, Специальная вставка,
Вставить условия на значение.
- Для удаления условий проверки следует
повторно выделить блок ячеек, выполнить
команду Данные, Проверка, указать Тип данных - Любое значение.
Формирование итоговой информации
Промежуточные итоги
Создать копию листа Штатное расписание – щёлкнуть правой клавишей мыши по названию
лист в подстрочном меню выбрать команду Переместить/Копировать,
указать положение куда переместить -
после листа Штатное расписание, установить
переключатель – Создать копию. Переименовать
лист-копию в Итоги по штатному расписанию
Промежуточные и общие итоги по данным Штатного расписания
формируются с помощью команды меню ДАННЫЕ, Структура, Промежуточные
итоги. Обязательное условие - таблица
должна быть отсортированной согласно
иерархии полей группирования для подведения
итогов. Итоговая таблица создается на
месте исходной таблицы.
Для каждого итога определяется:
- вид итоговой операции: сумма, количеств
значений, среднее, максимум, минимум и
т.п.,
- поле группирования, при изменении значении которого итог вычисляется,
- список полей, по которым подводится
итог указанного вида,
- порядок размещения итогов и др.
Можно многократно выполнять данную
команду меню, обеспечив формирование
вложенных итогов по различным полям группирования, а также нескольких итоговых операций
по одному и тому же полю группирования.
Промежуточные итоги соответствуют текущему
состоянию базы данных. От полученных
промежуточных итогов можно отказаться, выполнив
команду ДАННЫЕ, Структура, Промежуточные
итоги, Убрать все.
На листе Итоги по штатному расписанию:
Отсортировать таблицу по возрастанию поля Код подразделения -
команда ДАННЫЕ, Сортировка;
- Установить курсор в таблицу и выполнить
команду ДАННЫЕ, Структура, Промежуточные
итоги, указав:
- Поле группирования - Код подразделения,
- Операция - Сумма,
- Добавить итоги по полям:
Количество должностей
- Суммарный объем зарплаты
- Итоги размещать под данными, предыдущие
итоги не удалять.
После нажатия кнопки ОК создается структурная таблица, содержащая
уровни подведения итогов.
На листе Итоги по штатному расписанию:
Отсортировать таблицу по возрастанию
поля Код подразделения - команда ДАННЫЕ, Сортировка;
- Установить курсор в таблицу и выполнить
команду ДАННЫЕ, Структура, Промежуточные
итоги, указав:
- Поле группирования - Код подразделения,
- Операция - Сумма,
- Добавить итоги по полям:
Количество должностей
- Суммарный объем зарплаты
- Итоги размещать под данными, предыдущие
итоги не удалять.
После нажатия кнопки ОК создается структурная таблица, содержащая
уровни подведения итогов.
- Просмотреть отдельные части структурной
таблицы.
Создать копию листа Табель рабочего времени – щёлкнуть правой клавишей
мыши по названию лист в подстрочном меню
выбрать команду Переместить/Копировать,
указать положение куда переместить -
после листа Табель рабочего времени,
установить переключатель – Создать копию.
Переименовать лист-копию в Итоги по табелю
- Установить курсор в таблицу и выполнить
команду ДАННЫЕ, Структура, Промежуточные
итоги, указав:
- Поле группирования – Дата работы,
- Операция - Сумма,
- Добавить итоги по полям:
Стоимость работника
- Стоимость работы
- Итоги размещать под данными, предыдущие
итоги не удалять.
После нажатия кнопки ОК создается структурная таблица, содержащая
уровни подведения итогов.
- Просмотреть отдельные части структурной таблицы.
Создать копию листа Персонал – щёлкнуть правой клавишей мыши по названию
лист в подстрочном меню выбрать команду Переместить/Копировать,
указать положение куда переместить -
после листа Штатное расписание, установить
переключатель – Создать копию. Переименовать
лист-копию в Итоги по персоналу. Отсортировать
таблицу по возрастанию поля Код подразделения -
команда ДАННЫЕ, Сортировка;
- Установить курсор в таблицу и выполнить
команду ДАННЫЕ, Структура, Промежуточные
итоги, указав:
- Поле группирования – Код подразделения,
- Операция - Количество,
- Добавить итоги по полям:
Тариф
- Итоги размещать под данными, предыдущие
итоги не удалять.
После нажатия кнопки ОК создается структурная
таблица, содержащая уровни подведения
итогов.
- Установить курсор в таблицу и выполнить
команду ДАННЫЕ, Структура, Промежуточные
итоги, указав:
- Поле группирования – Должности,
- Операция - Количество,
- Добавить итоги по полям:
Тариф
- Итоги размещать под данными, предыдущие
итоги не удалять.
После нажатия кнопки ОК создается структурная таблица, содержащая
уровни подведения итогов
Создание сводных таблиц
Сводная таблица EXCEL - наиболее удобная технология формирования
итоговой информации различного вида
без предварительной сортировки, а также
вычисления новых итогов по формулам.
Сводная таблица позволяет осуществить
дополнительное группирование данных,
например:
- Создание интервальных рядов для полей
группирования числового типа с заданным шагом объединения;
- Создание временных интервалов для полей
группирования типа Дата/Время с заданным
шагом объединения.
Макет сводной таблицы включает области:
- Список полей исходной таблицы, из которых
формируется сводная таблица;
- Фильтр отчёта, для помещения полей, по
которым осуществляется фильтрация (отбор)
записей для представления в сводной таблице;
- Названия строк - для помещения полей группирования;
- Названия столбцов - для помещения полей группирования;
- Значения - для помещения полей, по которым подводятся итоги различного
вида (сумма, среднее значение, количеств
значений, максимум, минимум и др.).