Расчет авторских гонораров

При выпуске различных произведений расчет авторских отчислений может ока­заться достаточно сложным, если автору выплачивается процент от проданных эк­земпляров произведения. Рассмотрим про­стой пример Определим выплаты гонораров авторам нескольких книг, которое произвело издательство.

Вся нужная информация размещается на двух листах рабочей книги Excel. Внача­ле заполним лист с информацией о продаже книг и о выплатах авторам (Рис 4.1). На этом листе никаких формул нет, просто ведутся два списка. Списки можно рас­ширить, например, добавив дату операции и количество проданных книг Одна­ко для.расчетов эта информация не нужна, и мы не будем ее добавлять, чтобы не усложнять таблицу.

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

Кроме заголовка, на втором листе располагается информация обо всех авторах, работающих с издательством. В нашем примере описаны два автора (Рис.4.2), но их количество может быть любым. В ячейки с A3 по A7 введите названия полей. В ячейку ВЗ введите фамилию и инициалы одного из авторов, а в ячейку В4 введите сальдо на начало периода. Если автор должен издательст­ву, сальдо вводится отрицательным, а если издательство должно автору, сальдо будет положительным. Отметим, что для каждой книги далее будет введено соб­ственное начальное сальдо, а пока нужно ввести долги, не связанные с перечис­ленными книгами, например за книгу, полностью распроданную в предыдущем периоде.

В ячейку В5 введите формулу =CУMM(G10:G12). Внашем примере у автора три книги. Если в вашем случае количество иное, нужно просуммировать другое количество ячеек, например G10:G15. Далее в ячейку В6 введите формулу

=СУММЕСЛИ(Операции!$D$5:$D$100;ВЗ;Операции!$Е$5:$Е$100)

Функция условного суммирования СУММЕСЛИ проверяет на листе Опера­ции ячейки $ D$5:$D$100. В этих ячейках расположена фамилия в списке выплат. Если значение в какой-то строке равно значению из ячейки ВЗ, то есть фамилии автора, значение из соответствующей строки столбца Е используется при сумми­ровании. То, что используется именно столбец Е описано выражением Операции!$Е$5:$Е$100. Врезультате суммируются все выплаты данному автору, вне­сенные в список на листе Операции. В этой и других формулах считается, что про­веряются строки до сотой включительно. Если у вас списки большего размера, вставьте в формулу большее число.

Операции!$В$5:$В$100)) *С10

Естественно, формула вводится в одной строке. Функция СУММЕСЛИ суммирует значения из ячеек Операции!$В$5:$В$100, то есть суммы продаж партий книг. При этом для суммирования отбираются только строки, в которых зна­чение в столбце А листа Операции совпадает со значением в ячейке А10, то есть с названием нужной книги. Это как раз и описано с помощью первого и второго аргумента функции. Результатом суммирования является общая сумма всех про­данных книг заданного наименования. Выражением * С10 данная сумма умножа­ется на процент, причитающийся автору. В итоге получается начисленное автор­ское вознаграждение без учета аванса, фиксированной выплаты и сальдо на на­чало периода.

Для вычисления окончательной суммы введите в ячейку G10 формулу

=ЕСЛИ(F10<D10;D10;F10)+E10+B10. Если выплаты по процентам меньше аванса, начисляется аванс. В противном случае начисляются выплаты с учетом проданных книг. К полученной сумме добавляются сальдо на начало периода и фикси­рованная выплата. В итоге получается реально начисленная сумма по одной из книг. Выделите ячейки F10 и G10, протяните фор­мулы из этих ячеек на расположенные ниже строки, чтобы вычислить начисле­ния по всем книгам данного автора.

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

Чтобы рассчитать выплаты другому автору, скопируйте строки с третьей по двенадцатую в нижнюю часть листа. После этого нужно изменить исходные дан­ные, описав другого автора. Если изменится количество книг, нужно будет также изменить формулу в ячейке Всего начислено. Суммировать нужно начисленные суммы по всем книгам. Если книга написана в соавторстве, учет должен вестись для каждого автора отдельно. При этом указывается процент выплат по книге для каждого из авторов.

Описанные приемы можно использовать при всех расчетах, в которых нуж­но выплачивать определенный процент от полученного дохода. Ведя списки продаж и выплат, вы легко сможете получить н нужную сумму, воспользовавшись функцией условного суммирования СУММЕСЛИ.