Упражнение №3

Экономическое содержание задачи: при выпуске различных произведений расчет авторских отчислений может оказаться достаточно сложным, если автору выплачивается процент от проданных экземпляров произведения.

Вся нужная информация размещена на двух листах рабочей книги Excel. Вначале заполним лист с информацией о продаже книг и о выплатах авторам (рис. 1.20).

Рисунок 1.20 – Описание продаж и выплат

На этом листе никаких формул нет, просто ведутся два списка. Списки можно расширить, например, добавить дату проведения операции и количество проданных книг.

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

Рисунок 1.21 – Расчет авторского вознаграждения

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

(Внимание! Фамилия и инициалы должны быть написаны точно так же, как на первом листе рабочей книги).

В ячейку В4 введите сальдо на начало периода.

Таблица 1.10 – Формулы для расчета авторских вознаграждений

Ячейки	Формулы	Область копирования
B5	=СУММ(G10:G12)	-
B6	=СУММЕСЛИ(Операции!$D$5:$D$100; B3;Операции!$E$5:$E$100)	-
B7	=B4+B5-B6	-
F10	=(СУММЕСЛИ(Операции!$A$5:$A$100;A10; Операции!$B$5:$B$100))*C10	F11-F12
G10	=ЕСЛИ(F10<D10;D10;F10)+E10+B10	G11-G12

Ячейки В10:Е12 заполняются произвольными данными.

Лабораторная работа №4. Автоматизация создания платежных поручений

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

Вначале необходимо создать бланк платежного поручения (см. рисунок 1.22)

Рисунок 1.22 – Бланк платежного поручения

Для автоматизации создания платежных поручений вам понадобится рабочая книга Excel с тремя рабочими листами. На одном листе будет находиться бланк платежного поручения, а на другом – список платежей, а на третьем – реквизиты плательщика. Также нам понадобится файл с модулем перевода числа в сумму прописью.

После того, как оформление бланка закончено, перейдите на второй рабочий лист и создайте следующую таблицу (рисунок 1.23).

Рисунок 1.23 – Реквизиты плательщика

После этого вернитесь на рабочий лист с готовым бланком и поставьте в нужные места ссылки на ячейки листа с реквизитами (см. таблицу 1.11).

Таблица 1.11 – Формулы для заполнения платежного поручения

Ячейки	Формулы	Область копирования
В6	=Лист2!B3	-
Е6	=Лист2!B4	-
А7	=Лист2!B5	-
Н7	=Лист2!B6	-
А9	=Лист2!B7	-
Н9	=Лист2!B9	-
Н10	=Лист2!B8	-

После этого, перейдите на чистый лист, на котором будет располагаться информация обо всех платежах. В первой строке в ячейках с А1 по N1 введите заголовки полей платежного поручения. При этом информацию о плательщике и сумму прописью вводить не надо. Начиная со второй строки, будут располагаться строки с описанием разных платежных поручений. Заполните две-три строки, введя информацию о разных платежах.

Для поля «сумма» измените формат на «финансовый», а для поля «дата» установите формат «дата».

В результате вы должны получить примерно такую таблицу (рисунок 1.24).

Рисунок 1.24 – База платежных поручений

Далее перейдите на страницу с бланком платежного поручения и завершите заполнение полей. Единственным полем, заполняемым вручную, будет номер платежного поручения. У нас он расположен в ячейке Е3.

Далее необходимо заполнить оставшиеся поля формулами автоматизации заполнения (таблица 1.12).

Таблица 1.12 – Ссылки для заполнения платежного поручения

Ячейки	Формулы
F3	=ВПР(E3;Лист3!A2:M20;2;ЛОЖЬ)
C5	=СуммаПрописью(H6)
H6	=ВПР(E3;Лист3!A2:M20;3;ЛОЖЬ)
A12	=ВПР(E3;Лист3!A2:M20;9;ЛОЖЬ)
H12	=ВПР(E3;Лист3!A2:M20;11;ЛОЖЬ)
H13	=ВПР(E3;Лист3!A2:M20;10;ЛОЖЬ)
B15	=ВПР(E3;Лист3!A2:M20;6;ЛОЖЬ)
E15	=ВПР(E3;Лист3!A2:M20;7;ЛОЖЬ)
H15	=ВПР(E3;Лист3!A2:M20;8;ЛОЖЬ)
A16	=ВПР(E3;Лист3!A2:M20;5;ЛОЖЬ)
H16	=ВПР(E3;Лист3!A2:M20;13;ЛОЖЬ)
L17	=ВПР(E3;Лист3!A2:N20;14;ЛОЖЬ)
A20	=ВПР(E3;Лист3!A2:M20;4;ЛОЖЬ)

Так как все формулы однотипные, то рассмотрим одну из них:

=ВПР(E3;Лист3!A2:M20;3;ЛОЖЬ)

Функция ВПР находит в диапазоне ячеек Лист3!A2:M20, то есть в сотне строк базы платежей, строку с номером, совпадающим со значением ячейки Е3 (номером платежного поручения). Результатом вычисления будет значение из третьего столбца найденной строки, то есть сумма платежа для платежки с указанным номером.

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

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

Рисунок 1.25 – База платежных поручений с автофильтром