Лабораторная работа № 4_5

Расчет зарплаты в среде электронных таблиц EXCEL

Лист Тарифные ставки

Лист содержит перечень разрядов и соответствующих им тарифных коэффициентов и ставок. Ставка 1 разряда может периодически изменяться.

Создайте следующую таблицу на листе, которому дайте имя Тарифные ставки.

Ставка отдельного разряда рассчитывается по формуле Коэффициент* МРОТ.

Для удобства дальнейшей работы следует создать именованные блоки ячеек Разряд, Коэффициент, Ставка. (См. ниже).

Создание именованных блоков ячеек.

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

Блок ячеек одного листа может включать не обязательно смежные ячейки. Для выделения нескольких областей ячеек листа одного блока используется клавиша <Ctrl>.

1 способ создания именованного блока ячеек одного листа:

• Закрасить диапазон ячеек, например, содержащий номера рабочих разрядов на листе Тарифные ставки;

• Выполнить команду меню Формулы/Присвоить_Имя;

• Ввести имя создаваемого блока ячеек в строке Имя (автоматически вводится имя, соответствующее содержимому ячейки, расположенной над выделенным блоком - Разряд), в строке Формула выводится ссылка на диапазон выделенных ячеек. Нажать кнопку ОК.

2 способ создания именованного блока ячеек одного листа:

• Закрасить диапазон ячеек, содержащий, например, номера рабочих разрядов, а также название столбца;

• Выполнить команду меню Формулы/Диспетчер_Имен/Создать;

• Указать, что имя блока находится в строке выше;

• Нажать кнопку ОК.

Для удаления неправильно определенного блока следует:

• Выполнить команду Формулы / Диспетчер_Имен/Удалить;

• Выбрать из списка имя удаляемого блока;

• Нажать кнопку Удалить.

Лист Тарифные ставки следует защитить от изменений.

При обеспечения защиты данных следует:

1. Обеспечить возможность внесения новой величины МРОТ в условиях защищенного рабочего листа. Для этого установить курсор на ячейку, содержащую МРОТ, выполнить команду Формат/Ячейки вкладка Защита и снять защиту с ячейки, которая установлена по умолчанию.

2. Скрыть от просмотра на защищенном листе формулы. Для этого выделить блок Ставка (например, с помощью команды Правка/Перейти) и вызвав команду Формат/Ячейки/Защита, установить флажки Защищаемая ячейка и Скрыть формулы.

3. Защитить лист от изменений путем выполнения команды Рецензирование/Защита/Защитить лист (можно ввести пароль).

Лист Должностные оклады

Данный лист содержит сведения о должностных окладах работающих:

При заполнении таблицы предусмотреть:

1. Выбор должностного разряда из ограниченного списка значений. Для этого следует выделить весь диапазон ячеек в колонке Должн. разряд, выполнить команду Данные/Проверка и указать на вкладке Параметры тип данных - Список, Источник - именованный блок Разряд (список именованных блоков вызывается нажатием клавиши F3).

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

=ПРОСМОТР(Х№; Разряд; Ставка)

где Х№ - адрес ячейки, содержащей Должн тарифный разряд работающего.

Данная формула копируется в остальные ячейки столбца.

Сведения о функции ПРОСМОТР

При вызове функции в первом диалоговом окне мастер функций предлагает два варианта аргументов. В рассматриваемом примере необходимо выбрать первый аргумент из предложенных. В этом случае функция ПРОСМОТР будет иметь вид:

ПРОСМОТР(искомое_значение;просматриваемый_вектор; вектор_результатов)

Искомое_значение - это значение, которое ищется в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение. В нашем примере – это значение должностного разряда.

Просматриваемый_вектор - это интервал, содержащий только одну строку или один столбец. Значения в аргументе просматриваемый_вектор могут быть текстами, числами или логическими значениями. В нашем примере – это блок Разряд из таблицы Тарифная ставка. СПИСОК ИМЕНОВАННЫХ БЛОКОВ ВЫВОДИТСЯ КЛАВИШЕЙ F3!!!

Важно! Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания; в противном случае функция ПРОСМОТР может вернуть неверный результат. Тексты в нижнем и верхнем регистре считаются эквивалентными.

Вектор_результатов - это интервал, содержащий только одну строку или один столбец. Он должен быть того же размера, что и просматриваемый_вектор. . В нашем примере – это блок Ставка из таблицы Тарифная ставка

· Если ПРОСМОТР не может найти искомое_значение, то подходящим считается наибольшее значение в аргументе просматриваемый_вектор, которое меньше, чем искомое_значение.

· Если искомое_значение меньше, чем наименьшее значение в аргументе просматриваемый_вектор, то функция ПРОСМОТР возвращает значение ошибки #Н/Д.

3. Создать именованные блоки ячеек: Должность и Должн.оклад.

4. Обеспечить защиту рабочего листа, включая скрытие формул.

Лист Картотека

Данный лист содержит сведения о кадровом составе работающих:

При заполнении таблицы предусмотреть:

1. Ввод табельных номеров в текстовом формате - команда Формат/Ячейки, вкладка Число - Текстовый.

2. Выбор должности работающего из ограниченного списка значений, при помощи команды Данные/Проверка ( источник - именованный блок Должность).

3. Величина оклада работающего выбирается из справочника Должностные оклады с помощью формулы:

=ПРОСМОТР(Х№; Должность; Должн.оклад)

где Х№ - адрес ячейки, содержащей должность работающего;

Данная формула копируется в остальные ячейки столбца.

Важно!!! Проверить, чтобы таблица . Должностные оклады была отсортирована по полю Должность.

4. Создать именованные блоки ячеек: Таб.№; ФИО; Оклад_работающего, Льготы; Персон_надбавка.

5. Обеспечить защиту рабочего листа, включая скрытие формул.

Лист Ведомость начисления заработной платы.

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

При заполнении таблицы предусмотреть:

1. Выбор табельного № работающего из ограниченного списка значений, при помощи команды Данные/Проверка ( источник - именованный блок :. Таб.№);

2. Фамилия работающего выбирается из справочника Картотека с помощью формулы:

=ПРОСМОТР(Х№; Таб.№; ФИО),

где Х№ - адрес ячейки, содержащей табельный № работающего.

Данная формула копируется в остальные ячейки столбца.

3. Величина оклада работающего выбирается из справочника Картотека с помощью формулы:

=ПРОСМОТР(Х№; Таб.№; Оклад_работающего),

где Х№ - адрес ячейки, содержащей Табельный .№ работающего.

Данная формула копируется в остальные ячейки столбца.

Важно!!! Проверить, чтобы таблица . Картотека была отсортирована по полю Таб. №.

4. Значения остальных граф таблицы вычисляются по формулам:

Надбавка =Персональная надбавка в %*Оклад;

1% в пенсионный. фонд=(Оклад+Надбавка)*1%;

Подоходный. налог=(Оклад+Надбавка-1% в пенс. фонд – Льготы*МРОТ)*12%.

На руки=Оклад+Надбавка-1% в пенс. фонд- Подоходн. налог

При вводе формул учесть, что Персональная надбавка в % и Льготы выбираются из таблицы Картотека с помощью функции ПРОСМОТР, а величина МРОТ находится на листе Тарифные ставки.

6. Обеспечить защиту рабочего листа, включая скрытие формул.

Организация контроля ввода данных

Microsoft Excel позволяет организовать различные режимы контроля достоверности вводимой информации в указанные ячейки листа с помощью команды Данные/Проверка.

Виды контроля:

1. Контроль формата вводимых данных (целое число, дата, время, текст определенной длины);

2. Контроль диапазона значений определенного формата (между, вне, равно, неравно, больше, меньше, больше или равно, меньше или равно);

3. Контроль на соответствие фиксированному списку значений (список вводится непосредственно в виде перечисления через точку с запятой; может указываться как ссылка на именованный блок или диапазон ячеек¹). В этом случае для ячейки создается управляющий элемент - Поле со списком для выбора.

4. Ввод формулы для логического контроля вводимых данных - ввод данных в ячейку может быть обусловлен значениями других ячеек, находящихся на том же листе рабочей книгах. Для использования данных других листов или рабочих книг создаются именованные блоки, ссылки на которые размещаются в ячейках текущего рабочего листа. В поле Формула вводится формула для расчета логического значения. Вводимые данные считаются некорректными, если результатом вычислений является значение ЛОЖЬ.

·

Можно подготовить сообщения для ввода данных в ячейки, для которых задана проверка, а также в случае обнаружения ошибок ввода. Условия проверки отдельной ячейки можно распространять на другие ячейки. Для этого:

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

• Выполнить команду Правка/Копировать,

• Указать область ячеек, принимающая условия проверки вводимых данных,

• Выполнить команду Правка/Специальная вставка/Вставить условия на значения.