Расчет определенного процента от полученного дохода

На примере расчета авторского вознаграждения.

Вся нужная информация размещается на двух листах рабочей книги. Вначале заполняется лист с информацией о продаже книг и выплатах авторам. На этом листе нет формул, просто ведутся два списка. Заполните заголовки столбцов и введите данные Текущий лист переименуйте, назвав его ОПЕРАЦИИ

Далее перейдите на другой лист, чтобы ввести исходные данные и формулы для расчетов.

Кроме заголовка на втором листе располагается информация обо всех авторах, работающих с издательством.

В ячейки с А3 по А7 введите названия полей. В ячейку В3 введите фамилию и инициалы одного из авторов, а в ячейку В4 введите сальдо на начало периода. Если автор должен издательству, сальдо вводится отрицательным, а если издательство должно автору, сальдо будет положительным. Для каждой книги будет введено собственное начальное сальдо, а пока вводятся долги, не связанные с перечисленными книгами, например за книгу, распроданную в предыдущем периоде.

В ячейку В5 введите формулу =СУММ(G10:G12). В данном случае у автора три книги. Далее в ячейку В6 введите формулу =СУММЕСЛИ(Операции!$D$5:$D$100;B3;Операции!$E$5:$E$100)

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

В ячейку В7 введите формулу =B4+B5-B6. К начальному сальдо добавляются все начисления и вычитаются ранее произведенные выплаты. Получаем сумму, которую нужно выплатить автору. Теперь рассчитаем конкретные суммы начислений.

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

=(СУММЕСЛИ(Операции!$A$5:$A$100;A10;Операции!$B$5:$B$100))*C10

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

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

=ЕСЛИ(F10<D10;D10;F10)+E10+B10.

Если выплаты по процентам меньше аванса, начисляется аванс. В противном случае начисляются выплаты с учетом проданных книг. К полученной сумме добавляется сальдо на начало периода и фиксированная выплата. В итоге получается реально начисленная сумма по одной из книг. С помощью автозаполнения скопируйте формулы из ячеек F10 и G10 на нижние строки, чтобы рассчитать начисления по всем книгам данного автора.

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

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

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

Задание:

В вашем издательстве появился новый автор – Сидоров С.С., который написал Книгу 4, получил аванс 2000 руб. Его книга была продана на сумму 36000 руб., процент от продажи составляет 4%, фиксированная выплата 1000 руб. Добавить автора в расчетную книгу и рассчитать авторское вознаграждение.