Работа с табличным процессором MS Excel.
Освоение приемов работы с электронными таблицами
Практическое занятие 6.
Создание бухгалтерских документов: ведомость заработанной платы.
Цель работы:
- Закрепление навыков вычислений в Excel: использование нескольких рабочих листов.
- Освоение вычислений с использованием условий
1. Работа с данными, расположенными на нескольких рабочих листах.
В качестве операндов в формулах могут использоваться адреса ячеек, находящихся не только на текущем рабочем листе, но и на других рабочих листах и даже в других рабочих книгах. В этом случае рабочие листы или рабочие книги называются связанными.
Формула, отображаемая в строке формул, будет включать не только адреса (или имена ячеек), но и имена рабочих листов и рабочих книг. В общем виде она будет выглядеть так:
=Диск\Каталог\[Рабочая книга.хls] Рабочий лист!Адрес или имя ячейки
где:
Диск — имя логического диска;
Каталог — имя одного или нескольких каталогов;
Рабочая книга — имя файла электронной таблицы Excel;
Рабочий лист — имя рабочего листа в рабочей книге;
Адрес (имя) ячейки — адрес или имя ячейки или диапазона ячеек на рабочем
листе.
Например, если в файле (рабочей книге) Квартал1.х1s на листе с именем Данные
в ячейке А1 находится число 10, а в другой открытой рабочей книге
(Квартал2.хls) на текущем листе в ячейке В1 находится число 5 и в ячейке С1
эти данные надо просуммировать, то формула для этого будет выглядеть так:
= В1+[Квартал1.х1s]Данные!$А$1
Если книга закрыта, то в формуле нужно указать полный путь к файлу рабочей
книги, например
= B1+'C:\DATA\[ Квартал l.xls] Данные'!$А$1
Некоторые стандартные функции
=ОКРУГЛ (число; число_разрядов) - округляет число до указанного количества десятичных разрядов.
Число — округляемое число.
Число_разрядов — количество десятичных разрядов, до которого нужно округлить число.
- Если число_разрядов больше 0, то число округляется до указанного количества десятичных разрядов справа от десятичной запятой.
- Если число_разрядов равно 0, то число округляется до ближайшего целого.
- Если число_разрядов меньше 0, то число округляется слева от десятичной запятой.
Примеры
|
=СЕГОДНЯ() — возвращает текущую дату
= ЕСЛИ (лог_выражение; значение_если_истина;значение_если_ложь) — Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.
Допустимо до 7 уровней вложений функции ЕСЛИ.
= НЕ(лог_выражение) - возвращает логическое значение, обратное значению аргумента
= ЕПУСТО(значение) - возвращает значение ИСТИНА, если значение аргумента ссылается на пустую ячейку.
Задание на работу
3.1. Составить рабочую книгу для формирования ведомости выдачи заработной платы.
3.2. Рабочая книга должны состоять из трех рабочих листов:
- лист «штатное расписание» имеет вид:
| Сотрудник | Принят на работу | Стаж | Часовая ставка | Особые отметки |
где «Принят на работу » - дата в формате ДД.ММ.ГГГГ;
для вычисления Стажа можно применить формулу =ОКРУГЛ((СЕГОДНЯ()- Адрес_ячейки_ столбца_ Принят_на работу)/365,0)
часовая ставка – принимает значения 120..150 р/час;
особые отметки – может содержать любой признак, указывающий на необходимость удержания с заработанной платы.
- лист «справочные данные» содержит информацию о премиях в виде:
| Стаж | Премиальный коэффициент |
| до 10 | 0.05 |
| до 20 | 0.1 |
| свыше 20 | 0.15 |
а также величины удержаний и налогов:
| Исполнительный лист | ПФ | НФЛ |
| 0.25 | 0.01 | 0.13 |
- лист ведомости выдачи зарплаты:
| Сотрудник | Отработано часов | Зарплата | Премия | Удержания | ПФ | НФЛ | На руки |
| Иванов | |||||||
| Петров | |||||||
| Сидоров | |||||||
| Итого |
Значения в столбце «отработано часов» от 50 до 100. В ведомости должно быть не менее 6 сотрудников.
Все данные – условны!
Указания по выполнению работы
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 р/ч».