Информационные и компьютерные технологии (Бикеева Н.Г.)

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

Составим простую модель финансирования данного подразделения: существует фиксированный набор Должностей, например, ассистент, доцент и т.д. Для каждой должности есть несколько категорий (разрядов). В зависимости от категории начисляется основная зарплата: для наименьшей категории (первой) зарплата равна минимальной зарплате, на каждую единицу последующей категории зарплата увеличивается на определенный процент за категорию.

Для реализации данной модели создать на Листе1, список поименованных полей, в которые занести необходимые значения. На Листе2 создать требуемую ведомость.

Вот порядок действий для разработки заданной модели.

 Этап 1. Подготовка исходных данных

1. Создать новую книгу Excel и сохранить ее в файле с именем Вашей фамилии, например, ПЕТРОВА_ВОЛЧЕНКО: кнопка Office→Сохранить как.

2. Используя контекстное меню на вкладке Листа1 дать название Исходные данные.

3. Ввести в него данные, показанные на рис. 20.

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

4. Создать имена для ячеек первого листа. Для этого выделить ячейку или диапазон, например С1. Затем выполнить команду Формулы →Определение имени →Присвоить имя. Или вызвать эту же команду из контекстного меню. В появившемся диалоговом окне Создание имени (рис. 21) в поле Имя ввести имя ячейке – Мин_зарпл (минимальная зарплата). В поле Область указывается область видимости для создаваемого имени – в данном случае Книга. Другой способ создания имени:  выделить ячейку Е1. Перенести курсор мыши в поле ИМЯ экрана Excel (слева от строки формул), и внести в него Кол_сотр (количество сотрудников). Выделить диапазон А3:А9 и дать ему имя Должность. Ячейке D1 установить числовой формат Процентный и присвоить имя проц_кат (процент за категорию).

Если требуется исправить введенное имя, сделать это можно в поле ИМЯ, или через диалоговое окно Формулы → Диспетчер имен. В нем же можно удалить имя, если такая необходимость возникает.

Рис. 21 Диалоговое окно Создание имени.

5. В ячейки Е3:Е9 выбрать категорию каждой должности, используя функцию =СЛУЧМЕЖДУ(C3;D3) (стр. 22), которая назначит случайным образом значение в диапазоне от кат1 до кат2 для каждой должности диапазона А3:А9. Присвоить диапазону Е3:Е9 имя Категории.

Этап 2. Разработка основной ведомости

1. Перейти на лист2 и даем ему имя Ведомость

2. Выделить первые пять ячеек.

а) На вкладке Главная → Ячейки →Формат → Ширина столбца установить ширину столбца 12 пт.

б) Ввести в первую строку заголовки столбцов будущей ведомости.

в) Вызвав диалоговое окно Формат ячеек (на вкладке Главная  или в контекстном меню) и отформатировать заголовки так, как показано на рис. 22.

Рис. 22 Форматирование заголовков столбцов будущей таблицы Ведомость

3. Заполнить первый столбец таблицы согласно рис. 23 и отформатировать диапазон А1:Е11 как таблицу. Для придания ей пропорционального вида еще раз дать команду на установку ширины столбцов 12пт.

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

а) поместите курсор в ячейку В2. На вкладке Данные в группе Работа с данными выберите команду Проверка данных. откроется вкладка Проверка вводимых значений (рис. 23).

Рис. 23 Первый столбец таблицы Ведомость и подготовка раскрывающегося списка

 б) В поле Тип данных, нажав стрелку прокрутки открыть список допустимых значений и выбрать Список. В поле Источник записать ссылку на диапазон: =Должность.

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

5. Переходим к заполнению столбца Табельный номер. Пусть в нашей модельной задаче табельный номер имеет текстовое значение и формируется слиянием (знак &) первых трех символов фамилии и номера, выбранным случайным образом из количества сотрудников (Кол_сотр). Откройте палитру функции =ЛЕВСИМВ и познакомьтесь с ее значением и синтаксисом. Используя прием Автозаполнение формул, ввести в ячейку C2 формулу:

=ЛЕВСИМВ(Таблица1[[#Эта строка];[ФИО]];3)&СЛУЧМЕЖДУ(1;кол_сотр)

Рис. 24 а) Выбор значений из раскрывающегося списка; б) Заполненный второй столбец таблицы с использованием списка

Обратите внимание:

а) После ввода формулы (без Вашего вмешательства) произошло заполнение всех строк таблицы. Почему?

б) Очевидно, что использование имени ячейки (Кол_сотр) эквивалентно использованию абсолютного адреса. Как реализована в таблице относительная ссылка?

в) Поскольку в формировании табельного номера участвует функция выбора случайного числа, то при пересчете таблицы значения ячеек столбца будут меняться – проверьте это, нажав клавишу < F9 >. То же самое будет происходить и с диапазоном Категории. В реальной практике таких ситуаций, конечно, не должно быть, так как табельные номера и категории сотрудникам присваивают не случайным образом, а по определенным правилам. Но для учебных целей взятая модель пригодна.

6. Для удобного назначения категории каждому сотруднику, соответствующей его должности, задать ячейке Сообщение для ввода. Для этого снова вызвать диалоговое окно Данные ÞРабота с даннымиÞПроверка данных и перейти на вкладку Сообщение для ввода, где в поле Сообщение ввести список должностей в соответствие с диапазоном Должность, отступив от края поля так, как показано на рис. 25а.

Ввести в ячейку раскрывающийся список из диапазона Категории (см. пункт 4).

Скопировать свойство ячейки на весь столбец Категории. Теперь при вводе категории нужно выбирать ее соответственно должности сотрудника из раскрывающегося списка (рис. 25б). 

Рис. 25 а) Диалоговое окно для создания всплывающего сообщения в ячейке. б) Выбор категории сотрудника из списка соответственно его должности

 7. Последний столбец таблицы должен содержать формулу, по которой вычисляется Основная зарплата сотрудника, исходя из его Категории, минимальной зарплаты (Мин_зарпл) и процента начисления на категорию (катег_проц). В рамках заданной модели, формула будет иметь следующий вид: 

Основная зарплата = Мин_зарпл*(1+(Категория ‑ 1)*катег_проц)

Введите формулу в ячейку Е2, выбирая мышью необходимые ячейки с листов. Внимание!: после выбора именованных ячеек с листа Исходные данные не уходить сразу с этого листа, а перенести курсор мыши в строку формул и в ней продолжать набор арифметических операторов и скобок, затем, при необходимости, можно перейти на лист Ведомость. Формула = Мин_зарпл * ( 1 + Таблица1[[#Эта строка] ; [Категория]]  ‑  1) * катег_проц автоматически будет скопирована на весь столбец.

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

8. Основной макет таблицы Ведомость готов. Поместите курсор на стрелку раскрывающегося списка заголовка ФИО и отсортируйте таблицу по алфавиту. В режиме просмотра (Кнопка Office→Печать→Предварительный просмотр) или при печати документ будет выглядеть так, как на рис. 26

Рис. 26 Заполненная таблица Ведомость

Этап 3. Расширение ведомости дополнительными полями и итоговыми расчетами.

1. Введем столбец для начисления подоходного налога. Для этого на листе Исходные данные ячейке F1 присвоить Процентный формат и ввести в нее число 13. Обратите внимание: знак % появляется автоматически. Дать ячейке имя Подоходный. 

Перейти на лист Ведомость и ввести заголовок Под.налог в ячейку F1.

В ячейку F2 ввести формулу, для вычисления налога:

=Таблица1[[#Эта строка];[Основная зарплата]]*Подоходный

2. Ввести столбец для вычисления суммы К выдаче, которая равна разности Основной зарплаты и Под.налога. Самостоятельно введите требуемую формулу в ячейку G1.

3. Начисление премии сделайте по следующему правилу: если категория сотрудника не больше 10, премия равна двум минимальным зарплатам ‑ Мин_зарпл, если больше десяти, то одной Мин_зарпл. Для этого:

а) Ввести между столбцами таблицы F и G  новый столбец и дать ему заголовок Премия.

б) Вызвать Мастер функций (значок f_x ) и среди логических функций выбрать функцию ЕСЛИ.

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

=ЕСЛИ(Таблица1[[#Эта строка];[Категория]]<=10;Мин_зарпл*2;Мин_зарпл)

 

Рис. 27 Пример функции ЕСЛИ для вычисления премии
по заданному условию

4. Учесть премию в столбце К выдаче.

5. Присвоить Финансовый формат данным столбцов Под.налог, Премия и К выдаче.

6. Усложните условие начисления премии: для сотрудников категории менее 9 – две Мин_зарпл, для категории, не больше 13 – одна Мин_зарпл, выше 13 – половина Мин_зарпл.

При записи формулы следуйте алгоритму: 

если Категория < 9, то премия равна Мин_зарпл ‑ поле Значение_если_истина; в поле Значение_если_ложь: если Категория < 13, то 2* Мин_зарпл, иначе Мин_зарпл/2.

7. Ввести Строку итогов: Конструктор→ Параметры стилей таблиц →Строка итогов. В столбце К выдаче сразу появляется итоговая сумма.

а) Используя прием автокопирование, получить общую сумму премий.

б) Используя прокрутку Строки итогов в столбце Основная зарплата, вычислить максимальную зарплату.

в) Обратите внимание, что в строке формул функция имеет =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(104;[Основная зарплата]): вместо имени функции указан ее код ‑ 104. Скопировать формулу из ячейки Е11 в Е12. Установить курсор в ячейку Е12 и клавишей <F2> вызвать формулу на редактирование. Подведите курсор к коду 104 и удалите цифры 04, раскроется список кодов и функций (рис. 28). Выберите курсором функцию МИН и клавишей <Tab> введите ее в формулу.

г) В столбце F12 подсчитать сумму премий, выдаваемых для сотрудников с зарплатой ниже 6000 руб. Использовать функцию СУМЕСЛИ, изучив назначение полей ввода на ее палитре.

 

Рис. 28 Раскрывающийся список итоговых функций и их кодов.

д) В столбце Должность, выбрать Другие функции, отрыть окно мастера функций и найдя функцию СЧЁТЕСЛИ, подсчитать количество профессоров. В столбце Категории, подсчитать количество сотрудников с категорий, меньше 8.

е) В столбце Табельный номер подсчитать количество сотрудников подразделения – функция Количество.

8. Создать заголовок к созданной Вами Ведомости.

а) Вставить перед таблицей 3 пустых строки, пользуясь командой Вставить из контекстного меню или вкладки Главная →Ячейки. В ячейку А1 ввести текст Ведомость выдачи заработной платы.

б) Выделить диапазон А1:Н1, вызвать диалоговое окно Формат ячеек и установить Выравнивание →по горизонтали → по центру выделения, затем установив шрифт Аrial полужирный 12. Название растянется над всей таблицей.

в) Перейти в ячейку D2. Ввести функцию TDATA, устанавливающую текущую дату и время создания таблицы. Чтобы увидеть полученное значение, применить Выравнивание → по горизонтали → по центру выделения.

9. Задание выполнено. Таблица должна выглядеть так, как показано на рис. 29.

Рис. 29 Окончательный вариант таблицы Ведомость