Практическое занятие № 4 Ведение в табличной базе данных сотрудников нескольких списков, проверка данных (6 часов)

1. Создание отдельного списка с указанием окладов сотрудников и работа с ним

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

- информация о должностных окладах является конфиденциальной;

- размеры окладов (или тарифов) могут быть изменены только должностным лицом, имеющим специальные полномочия;

- список, в котором указан размер оклада, должен быть достаточно мобильным (то есть легко изменяться с учетом различных требований предприятия), что не характерно для табличной базы данных сотрудников;

- список служащих с указанием их окладов используется при начислении самых разнообразных выплат, в том числе и связанных со временной нетрудоспособностью, а также различного рода надбавок;

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

Табличная база данных сотрудников, скорее всего, будет создаваться и редактироваться в отделе кадров, а список с окладами может быть составлен и в бухгалтерии, и в планово-экономическом отделе, что зависит от размера предприятия.

Поместить в левый верхний угол таблицу со списком сотрудников, в котором данные расположены следующим образом:

- столбец А - наименование отдела (подразделения);

- столбец В - должность работника;

- столбец С - фамилия и инициалы;

- столбец D - табельный номер;

- столбец Е - должностной оклад.

Рис.1. Список сотрудников с должностными окладами.

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

При помощи списка с описанными выше полями достаточно просто выполнить такие операции, как:

- расчет численности сотрудников в каждом отделе предприятия;

- определение общей численности работников, занимающих определенные должности;

- расчет новых размеров окладов.

Определение количества сотрудников в каждом отделе

Необходимо определить, сколько сотрудников числится в отделах предприятия.

Поскольку отделов немного (три), для наглядности разместить таблицу с расчетом в диапазоне ячеек G1:H4 того же рабочего листа, на котором располагается список отделов.

В ячейки G1 и H1 ввести названия столбцов (Отдел и Численность), а в ячейки G2:G4 - названия отделов (Контроля, Реализации, Снабжения).

В ячейки Н2:Н4 необходимо поместить формулы, с помощью которых будут производиться вычисления. Чтобы определить общее количество сотрудников в отделе, необходимо подсчитать, сколько ячеек с названием данного отдела имеется в диапазоне А2:А11 (то есть сколько раз упоминается в списке название отдела, столько в нем и сотрудников).

Наиболее подходящей для этой цели является функция СЧЕТЕСЛИ, которая подсчитывает количество в указанном диапазоне непустых ячеек, удовлетворяющих заданному критерию. Она относится к категории Статистические и имеет следующий синтаксис:

СЧЕТЕСЛИ(диапазон;условие)

Здесь диапазон - это интервал, в котором подсчитывается количество ячеек. В данном случае таковым является А2:А11. Аргумент условие должен представлять собой число, выражение или текст и определять, какие именно ячейки надо подсчитывать. В нашем примере условие соответствует названию отдела - Контроля. Название отдела можно внести непосредственно во второй аргумент функции СЧЕТЕСЛИ, но значительно проще вместо текста поместить туда ссылку на ячейку G2, в которой таковой находится. Для заполнения ячеек Н2:Н4 формулами, выполнить следующие действия:

1. Выделите диапазон ячеек Н2:Н4.

2. Вызовите посредством мастера функций панель функции СЧЕТЕСЛИ.

3. Поместите курсор в поле Диапазон и мышью выделите на рабочем листе ячейки А2:А11.

4. Нажмите функциональную клавишу [F4] для создания абсолютной ссылки на диапазон ячеек.

5. Перейдите в поле Условие и щелкните мышью на ячейке G2.

6. Нажмите комбинацию клавиш [Ctrl+Enter].

В результате этих действий в ячейке Н2 будет создана формула

=СЧЕТЕСЛИ($A$2:$A$11;G2)

Подобные формулы появятся также в ячейках НЗ и Н4, но во втором их аргументе будут указаны соответственно адреса ячеек G3 и G4.

Рис.2. Добавление таблицы отделов.

Определение количества сотрудников, занимающих определенные должности

Аналогичным образом можно для каждой должности определить, сколько сотрудников предприятия ее занимают. Разместить эти расчеты на том же листе, но в диапазоне ячеек G7:H13.

В ячейки G7 и G8 ввести названия столбцов (Должность и Численность), а в ячейки G8:G13 - названия должностей (Аудитор, Инженер, Менеджер, Начальник, Секретарь и Экспедитор).

Формулы в ячейках Н8:Н13 также будут построены на базе функции СЧЕТЕСЛИ. В частности, формула в ячейке Н8 должна быть такой:

=СЧЕТЕСЛИ($В$2:$В$11;G8)

Задача 1.

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

Для проверки изменить наименование отдела и должности в левой таблице и проверить изменение числовых данных.

Контроль рассчитываемых значений

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

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

Для данной задачи ввести метод контроля, основанный на следующем предположении. Если в списке работников нет ошибки, то значения в столбце "Оклады" должны быть больше нуля. Поэтому ввести в ячейку Н16 такую формулу:

=СЧЕТЕСЛИ($Е$2:$Е$11;">0")

Она произведет в диапазоне Е2:Е11 подсчет значений, которые больше 0. В качестве критерия в функции СЧЕТЕСЛИ могут использоваться числовые значения, однако их необходимо заключать а кавычки. Если расчеты проведены правильно, то значения в ячейках Н5 и Н14 должны быть равны значению в ячейке H16.

Рис.3. Добавление полей контроля.

Изменение должностных окладов

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

В ячейку G4 ввести значение индекса увеличения оклада (1,077).

Поместить в диапазон ячеек F2:F11 формулы перерасчета с функцией округления. Для этого нужно выделить диапазон F2:F11, ввести формулу

=ОКРУГЛ(Е2*$G$4;0)

и нажать комбинацию клавиш [Ctrl+Enter].

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

Рис.4. Рабочий лист со столбцом перерасчета окладов.

Если оклады в столбце F, полученные в результате вычислений, устраивают, можно произвести замену прежних их значений новыми. Для этого необходимо скопировать содержимое диапазона F2:F11 и, поместив табличный курсор в ячейку Е2, вызвать диалоговое окно Специальная вставка. В области "Вставить" этого окна нужно активизировать переключатель "Значение", а затем нажать кнопку ОК.

Изменение должностных окладов с использованием коэффициентов от одного оклада

Еще один метод расчета новых окладов заключается в том, что размер оклада каждого сотрудника с помощью определенного коэффициента "привязывается" к зарплате ведущего специалиста (например, директора или начальника отдела). Заработная плата сотрудников этих отделов вычисляется путем умножения оклада их начальника на заданный коэффициент. Для применения данной методики в рабочий лист в ячейку G3 ввести сумму прежнего оклада начальника отдела реализации (1400), а в ячейку G4 - коэффициент, на который увеличиваются все оклады. Будущий оклад начальника отдела реализации (то есть оклад после повышения) будет вычисляться в ячейке G5 по формуле

=ОКРУГЛ(G3*(1+G4);0)

В столбец Н занести все коэффициенты, используемые при перерасчете окладов, а в диапазон Е2:Е11 - формулы, по которым рассчитывается размер будущего оклада.

Если в ячейку G4 ввести значение коэффициента, равное нулю, то в диапазоне Е2:Е11 отобразятся прежние оклады.

Задача 2.

Произвести занесение расчета новых окладов таким образом, чтобы в каждом отделе оклады сотрудников рассчитывались от оклада своего начальника. А оклады начальников отделов от оклада начальника отдела реализации.

Указания по решению

1) В столбце окладов делать ссылки на соответствующие ячейки окладов своих начальников (не забывать о необходимости ввода абсолютных ссылок).

2) Для проверки на 10% увеличить оклад всем сотрудникам отдела снабжения.