Удержания из заработной платы

Для расчета налогов на заработную плату вводится дополнительная справочная информация на листе Налоги:

Стандартные вычеты на детей		Ставка подходного налога
							13%

Рисунок 6 - Нормативы налогов на заработную плату

1. Создать именованные блоки:

Вычеты - ячейки таблицы Стандартные вычеты на детей (заголовки столбцов не входят в блок).

Подоходный_налог, - ячейки таблицы Ставка подоходного налога (заголовок столбца не входя в блок).

2. Для расчета налогов и суммы к выдаче дополнить сводную таблицу столбцами рис. 6 (расширение вправо):

Прочие начисления	Итого начислено	Количество детей	Облагаемый налогом доход	Подоходный налог	Прочие удержания	Сумма к выдаче

Рисунок 7 - Дополнение к сводной таблице

3. Ячейки столбца Прочие начисления заполняются произвольным образом.

4. Ячейки столбца Итого начислено содержат формулу вида:

= Зарплата по наряду + Прочие начисления

Формулу скопировать в остальные ячейки столбца Итого начислено

5. Ячейки столбца Количество детей вычисляются по формуле вида:

= ПРОСМОТР(A5;ФИО;Дети),

где А5 - адрес ячейки, содержащей первую фамилию работающего в сводной таблице.

Формулу скопировать в остальные ячейки столбца Количество детей

6. Облагаемый налогом доход рассчитывается с учетом нормативов налогообложения совокупного дохода.

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

ГПР(G5; Вычеты;2;ИСТИНА)

Поиск в первой строке блока Вычеты подходящего для Итого начислено (G5) ограничения. Если значение найдено, сумма вычета выбирается из строки 2.

Облагаемый налогом доход для первой записи сводной таблицы рассчитывается по формуле:

=ЕСЛИ(F5-ГПР(G5;Вычеты;2;ЛОЖЬ)*G5>0;F5-ГПР(G5;Вычеты;2;ЛОЖЬ);0)

где F5 - Итого начислено;

G5 - количество детей.

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

7. Подоходный налог: рассчитывается согласно формуле:

=Н5*Подоходный_налог

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

8. Прочие удержания заполняются вручную.

10. Сумма к выдаче: рассчитывается по формуле:

Итого начислено - Подоходный налог – Прочие удержания

Формулу скопировать во все ячейки столбца Сумма к выдаче.

После добавления очередного листа наряда следует внести изменения в сводную таблицу, для этого:

1. Установить курсор в область сводной таблицы.

2. Запустить Мастер сводных таблиц и диаграмм.

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

4. Нажать кнопку Готово. В результате будут добавлен новый наряд.

5. Установить курсор в сводную таблицу и обновить данные - команда Параметры, Данные, Обновить все.

Практическая работа 2. Учет товаров на складе

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

На складе хранятся товары, которые поставляются поставщиками и продаются покупателям. Движение товаров оформляется документами Приходная накладная, Расходная накладная. Ведется учет текущих остатков товаров в натуральном и стоимостном выражении, сальдо (недоплата, переплата) расчетов в стоимостном выражении с покупателями и поставщиками товаров.

Таблица 5– Структура книги

Лист Товары	Справочник товаров
Лист Организации	Справочник поставщиков и покупателей товаров
Лист Движение	База данных движения товаров
Лист Итоги	Оборотная ведомость движения товаров
Лист Расчеты	Состояние расчетов с организациями
Лист Критерии	Критерии расширенного фильтра
Лист Фильтрация	Лист условий фильтрации базы данных
Лист Своды	Агрегированная информация о движении товаров
Лист Документ	Шаблон документа Накладная

НОРМАТИВНО-СПРАВОЧНАЯ ИНФОРМАЦИЯ

Нормативно-справочная информация представлена на листах Товары и Организации.

Лист Товары

Лист Товары содержит номенклатуру товаров, учитываемых на складе, в полном объеме.

Таблица 6 - Товары

Код товара	Наименование	Ед.изм.	Цена
	Стол	шт.
	Стул	шт.
	Диван	шт.
	Кресло	шт.
	Мебельный гарнитур	шт.
	Кухня	шт.
	Шкаф-купе	шт.
	Вешалка	шт.
	Книжная полка	шт.
	Дверь	шт.
	Палас	м^2

Информационная технология заполнения листа Товары

1. Заполнить шапку таблицы.

2. Создать именованные блоки столбцов таблицы - Код_товара; Наименование; Ед_изм.; Цена (блоки не включают имена столбцов), ориентировочное число строк таблицы– 10 - 15.

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

4. Выбор единиц измерения товаров осуществляется из фиксированного списка значений.

Выделить блок ячеек Ед_изм, выполнить команду меню Данные, Работа с данными, Проверка данных, Проверка данных, тип данных – Список, Источник - м²; шт.; комп.; дюж.

5. После заполнения таблицы создать именованный блок Товары, включающий имена столбцов и все строки.

6. Отсортировать таблицу по возрастанию кодов товаров.

7. Защитить информацию рабочего листа.

Лист Организации

На листе Организации содержится список организаций с указанием банковских реквизитов (таблица 7)

Таблица 7 - Организации

Название	Банковские реквизиты
АО Сигнал	р/с 12398476656455345630 в АКБ Балтийский банк
Магазин № 5	р/с 44335546565465640000 в КБ Санкт-Петербург
ПО Севмебель	р/с 12398476656344300000в АКБ Петровский
Универмаг ЦФТ	р/с 12365465876878760000 в КБ Энергобанк

Информационная технология заполнения листа Организации

1. Заполнить шапку таблицы.

2. Создать именованные блоки столбцов таблицы: Название; Банковские_реквизиты (блоки не включают имена столбцов). Ориентировочное число организаций 10.

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

4. Отсортировать таблицу по возрастанию названий организаций.

5. Защитить информацию рабочего листа.

БАЗА ДАННЫХ ДВИЖЕНИЯ ТОВАРОВ

Учет поступления и отпуска товаров со склада осуществляется на листе Движение (таблица 8)

Таблица 8 – Движение товаров

Дата	Код товара	Наименование	Ед. изм.	Цена	Организация	Документ	Поступление	Реализация	Итого поступление	Итого реализация

Информационная технология заполнения листа Движение

1. Заполнить шапку таблицы.

2. В первой пустой записи списка ввести формулы для ячеек столбцов:

Наименование = ПРОСМОТР($B2; Код_товара; Наименование)

Ед.изм = ПРОСМОТР($B2; Код_товара; Ед.изм.)

Цена = ПРОСМОТР($B2; Код_товара; Цена)

Итого поступление = Поступление*Цена

Итого реализация = Реализация*Цена

где B2 - адрес ячейки первой строки списка Движение товаров, содержащей код товара.

После ввода формул в ячейках таблицы появляется #НД.

3. Выделить диапазон ячеек, включающий имена столбцов и первую (пустую) строку таблицы, и создать именованный блок База_данных;

4. Использовать условное форматирование для ячеек столбцов Поступление и Реализация: количество по операции менее 20 единиц - фон белого цвета, от 21 до 40 единиц - фон зеленого цвета; свыше 40 единиц - фон голубого цвета.

5. Создать именованные блоки: Товар, Организация, Поступление, Реализация, Итого_поступление, Итого_реализация, в которые входят все ячейки столбцов, включая их имена.

Для простоты создания блоков следует выделить столбец, щелкнув на его заголовке, выполнить команду меню Формулы, Присвоить имя.

6. Выбрать любой из доступных код товара в первой строке, количество по поступлению или реализации выбранного товара не вводить. (Этот пункт выполняется для проверки правильности заполнения листа Движение).

Лист Движение заполняется автоматически при работе с документами Приходная накладная и Расходная накладная.