Практическое занятие 8.
Создание бухгалтерских документов: ведомость заработанной платы.

Цель работы:

1.                 Закрепление навыков вычислений в  Excel: использование нескольких рабочих листов.

2.                 Освоение вычислений с использованием условий

1.     Работа с данными, расположенными на нескольких рабочих листах.

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

Формула, отображаемая в строке формул, будет включать не только адреса (или имена ячеек), но и имена рабочих листов и рабочих книг. В общем виде она будет выглядеть так:

 =Диск:\Каталог\[Рабочая книга.хls] Рабочий лист!Адрес или имя ячейки

где:

Диск — имя логического диска;

Каталог   — имя одного или нескольких каталогов;

Рабочая книга    — имя файла электронной таблицы Excel;

Рабочий лист     — имя рабочего листа в рабочей книге;

Адрес (имя) ячейки — адрес или имя ячейки или диапазона ячеек на рабочем

листе.

Например, если в файле (рабочей книге) Квартал1.х1s  на листе с именем Данные в ячейке А1   находится  число  10, а  в другой  открытой рабочей  книге Квартал2.хls на текущем листе в ячейке В1 находится число 5 и в ячейке С1эти данные надо просуммировать, то формула для этого будет выглядеть так:

= В1+[Квартал1.х1s]Данные!$А$1

Если книга закрыта, то в формуле нужно указать полный путь к файлу рабочей книги, например, если рабочая книга хранится в папке DATA, то формула приобретет вид:

= B1+'C:\DATA\[ Квартал l.xls] Данные'!$А$1
Замечание.

Рекомендуется следующий порядок ввода формул, параметры которых расположены на разных рабочих листах:

·        Если используется Мастер функций – свернуть окно Мастера

·        Перейти на рабочий лист с требуемыми данными

·        Выделить диапазон данных

·        Ввести знак операции или развернуть окно Мастера

·        Перейти на лист с формулой

Эти шаги повторяются до завершения ввода формулы.

2. Некоторые стандартные функции

=ОКРУГЛ(число;число_разрядов) -   округляет число до указанного количества десятичных разрядов.

Число  —  округляемое число.

Число_разрядов — количество десятичных разрядов, до которого нужно округлить число.

·        Если число_разрядов больше 0, то число округляется до указанного количества десятичных разрядов справа от десятичной запятой.

·        Если число_разрядов равно 0, то число округляется до ближайшего целого.

·        Если число_разрядов меньше 0, то число округляется слева от десятичной запятой.

Примеры

=СЕГОДНЯ()  —  возвращает текущую дату

=ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)   —  Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Допустимо до 7 уровней вложений функции ЕСЛИ.

Замечания. 

1) Чтобы ввести вместо одного из значений вложенную функцию с помощью Мастера функций, нужно выбрать ее наименование из списка функций в поле адреса (слева от строки редактирования).
2) Чтобы в Мастере функций перейти от одной введенной функции к другой, нужно в строке редактирования щелкнуть по имени этой другой функции или по одному из ее параметров.

3) Чтобы перейти к редактированию с помощью Мастера функций ранее  введенной функции, достаточно выделить ячейку с редактируемой формулой и нажать кнопку «Вставить функцию»  f_x

=НЕ(лог_выражение) -  возвращает логическое значение, обратное значению аргументаЮ, например, функция =НЕ(5=10) вернет значение ИСТИНА.

=ЕПУСТО(значение) -  возвращает значение ИСТИНА, если значение аргумента ссылается на пустую ячейку.

3. Задание на работу

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

3.2. Рабочая книга должны состоять из трех рабочих листов:

·        лист 1 «штатное расписание» имеет вид:

где «Принят на работу» - дата в формате ДД.ММ.ГГГГ;

      для вычисления Стажа можно применить формулу =ОКРУГЛ((СЕГОДНЯ()-Адрес_ячейки_из_столбца_Принят_на работу)/365,0)

       часовая ставка – принимает значения 120..150 р/час;

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

·        лист 2 «справочные данные» содержит информацию о премиях в виде:

а также величины удержаний и налогов:

·        лист 3 «ведомость выдачи зарплаты» имеет вид:

Значения в столбце «отработано часов» от 50 до 100.
В ведомости должно быть не менее 6 сотрудников.

Все данные – условны!

4. Указания по выполнению работы

4.1. В таблице «Ведомость выдачи зарплаты» вводятся только данные столбца «Отработано часов». Фамилии сотрудников в ведомости должны браться из листа  «штатное расписание». Все остальные данные вычисляются.

4.2. Заработанная плата вычисляется по формуле:

=отработано_часов*часовая_ставка

4.3. При вычислении премии следует использовать конструкцию вложенной функции ЕСЛИ.

4.4. Премия  рассчитывается по формуле:

=зарплата*премиальный_коэффициент

4.5. Все удержания и налоги вычисляются от суммы зарплаты и премии, например:

Отчисления_в_ПФ=( Зарплата + Премия)*ПФ, где значение ПФ берется из листа справочных данных

4.6. Значение суммы «На руки» определяется по формуле:

=Зарплата + Премия –Удержания -ПФ- НДФЛ

4.7. Дополнительное задание.

4.7.1. Средствами условного форматирования выделить в ведомости фамилии  сотрудников, зарплата которых меньше средней по организации.

4.7.2. Отформатируйте заголовок таблицы таким образом, чтобы месяц и год в заголовке ведомости зависели от текущей даты, например, «октябрь 2007».

4.7.3. Примените для столбцов «Стаж» и «Часовая ставка» созданный Вами пользовательский формат, чтобы данные имели вид числа с размерностью: «25 лет», «120 р/ч».

5.     Материал к  следующему занятию:
Импорт данных в электронные таблицы; стандартные (встроенные функции) Excel: ЕСЛИ, ВПР, вложенные функции
[Справочная система Excel]