Скачать Калькулятор родительской платы в Excel (первый вариант)

Создать копию файла "Калькулятор родительской платы" в Google-таблице (первый вариант)

Механизм в Excel и Google отличается, разберем формулы Excel.

Калькулятор рассчитывает общую сумму возможных затрат на ребенка: оплату за присмотр и уход (питание), добровольные целевые взносы (вода и охрана) и оплату платных образовательных услуг (кружки).

Основная часть расчетов приходится на присмотр и уход. Размер оплаты за присмотр и уход в ДОУ определяется нормативным актом - Постановлением Правительства СПб (см. здесь) и рассчитывается по формуле: x - (y * z)= d, где х -  размер ежемесячной родительской платы; y - средний размер родительской платы ; z - коэффициент компенсации (0,2 при 20%); d - размер оплаты за присмотр и уход. Последние года х и у сделали одинаковым, но формулу пока не меняю, вдруг обратно переиграют. Процент компенсации зависит от категории семей (см. по ссылке выше), которые перечислены в калькуляторе. Формула с расшифровкой прописана на листе таблицы (см. справа на рис.), этот диапазон столбцов обычно скрыт.

Разберем по пунктам:

Количество несовершеннолетних детей в семье - в принципе, лишний пункт, который теперь не имеет значения для компенсации оплаты.

Ребенок в семье по счету: от этого зависит процент компенсации (первый - 20%, второй - 50%, третий и далее - 70%). Выбор значения осуществляется с помощью элемента управления формы "Счетчик":

Настраиваем счетчик и привязываем его к ячейке, где будет отображаться значение ($D$5):

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

Иные основания для компенсации прописаны отдельными пунктами:
 

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

В настройках установлена связь с ячейкой в столбце L, где выполняется условие "ИСТИНА", если флажок установлен, и "ЛОЖЬ", если снят. В столбце J проставлены проценты компенсации по каждому из оснований.

В ячейке Е23 высчитывается коэффициент компенсации z по формуле (но формат ячейки Е23 установлен процентный, то есть в процентном формате значение ячейки (коэффициент компенсации умножается на 100 и выводится на экран с символом процента):

=ЕСЛИ(ИЛИ($L$8;$L$10;$L$20);1;ЕСЛИ(ИЛИ($D$5>2;$L$12);70;ЕСЛИ(ИЛИ($D$5=2;$L$18);
50;ЕСЛИ($L$15;40;ЕСЛИ($D$5=1;20))))/100)

Разбор формулы по частям:

ИЛИ($L$8;$L$10;$L$20);1
при условии, что в ячейках L8, L10 или L20 значение ИСТИНА, то есть поставлен флажок при выборе основания для компенсации (штатный сотрудник, или ребенок посещает компенсирующую группу, или ребенок-инвалид...), коэффициент компенсации равен 1 (100%).

ЕСЛИ(ИЛИ($D$5>2;$L$12);70;
если значение ячейки D5 больше 2 (третий или последующий ребенок в семье) или значение в ячейке L12 истинное (нажат флажок: доход семьи ниже прожиточного минимума), то процент компенсации равен 70.

ЕСЛИ(ИЛИ($D$5=2;$L$18);50;
если значение ячейки D5 равно 2 (второй ребенок в семье) или значение в ячейке L18 истинное (нажат флажок: один из родителей - инвалид), то процент компенсации равен 50.

ЕСЛИ($L$15;40;
если значение в ячейке L15 истинное (нажат флажок: неполная семья с недостаточным доходом), то процент компенсации равен 40.

ЕСЛИ($D$5=1;20
если значение ячейки D5 равно 1 (первый ребенок в семье), то процент компенсации равен 20.

…/100…
процент компенсации делим на 100, получаем коэффициент компенсации Z: 0,2; 0,4; 0,5; или 0,7. При 100% компенсации мы получили в первом действии коэффициент равный 1.

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

Далее считаем размер оплаты за присмотр и уход с учетом компенсации по формуле:

=ЕСЛИ(И(НЕ($L$8);НЕ($L$10);НЕ($L$20)); $M$4-($M$5*
ЕСЛИ(ИЛИ($D$5>2;$L$12);70;ЕСЛИ(ИЛИ($D$5=2;$L$18);50;
ЕСЛИ($L$15;40;ЕСЛИ($D$5=1;20))))/100);0)

Разбор формулы по частям:

ЕСЛИ(И(НЕ($L$8);НЕ($L$10);НЕ($L$20)); - если выполняется условие, что значения ячеек L8, L10 и L20 НЕ ИСТИНА, то есть нет оснований для 100% компенсации, то считаем по формуле расчета размера оплаты x - (y * z)= d:

$M$4-($M$5*, где М4=х, М5=у; умножаем на коэффициент компенсации в зависимостиот того, в какой ячейки нажат флажок или какой значение стоит в ячейке D5 (ребенок по счету), аналогично предыдущей формуле:

ЕСЛИ(ИЛИ($D$5>2;$L$12);70;ЕСЛИ(ИЛИ($D$5=2;$L$18);50;
ЕСЛИ($L$15;40;ЕСЛИ($D$5=1;20))))/100);

иначе, если не выполняется первое условие, то есть значения ячеек L8, L10 и L20 - ИСТИНА, то размер оплаты равен "0".

Считаем дополнительные траты:
учитываем желание родителя оплачивать добровольные целевые взносы:

в ячейке используем элемент управления формы "Флажок" и считаем по простой формуле:

=ЕСЛИ($L$27;350;0) - если в ячейке L27 нажат флажок (значение равно ИСТИНА, то результат равен 350 (350 рублей - рекомендуемый размер целевых взносов), если нет (ЛОЖЬ), то результат равен 0 рублей.

Оплата платных образовательных услуг:

для каждой группы (детского коллектива) установлен набор ПОУ, родитель может выбрать любое количество услуг из предлагаемого набора. Выбор реализован через использование элемента управления формы "Флажок".

Расчет производится по формуле:

=СУММ(ЕСЛИ($L$33;E34;0);ЕСЛИ($L$35;E36;0);ЕСЛИ($L$37;E38;0);ЕСЛИ($L$39;E40;0);
ЕСЛИ($L$41;E42;0);ЕСЛИ($L$47;E48;0);ЕСЛИ($L$49;E50;0);ЕСЛИ($L$51;E52;0))

СУММ - суммируем следующие результаты в скобках:
ЕСЛИ($L$33;Е34;0) - если значение ячейки L33 - ИСТИНА (нажат флажок, то есть родителем выбрана данная услуга), то берется значение из ячейки Е34: 1200 рублей - стоимость данной услуги, если ЛОЖЬ (услуга не выбрана, флажок не нажат), то 0 рублей.
Аналогично по остальным услугам (строки с 33 по 52). Значок $ ставится в формуле для закрепления адреса ячейки по копировании или изменении набора данных на листе.

Итоговую сумму всех затрат по оплате посещения ДОУ считаем простым сложением:
=$E$25+$E$28+$E$30 - суммируем оплату за присмотр и уход, целевые взносы и платные услуги. Аналогичная формула: =СУММ($E$25;$E$28;$E$30).

Почему-то неработающая формула:

В ячейке M7 коэффициент компенсации Z мог бы высчитываться по формуле: =ЕСЛИ(ИЛИ(НЕ(ЕПУСТО($D$8));НЕ(ЕПУСТО($D$10));НЕ(ЕПУСТО($D$20)));1;ЕСЛИ(ИЛИ($
D$5>2;НЕ(ЕПУСТО($D$12)));70;ЕСЛИ(ИЛИ($D$5=2;НЕ(ЕПУСТО($D$18)));50;ЕСЛИ(НЕ(ЕПУ
СТО($D$15));40;ЕСЛИ($D$5=1;20))))/100).

Но почему-то считает только по ячейке D5 (ребенок в семье по счету), остальные основания (флажки в ячейках с D8 по D20) в расчет не принимает (а в таблице Google аналогичная формула работает). Если можете подсказать, что не так с этой формулой - welcome в комментарии и спасибо заранее.

Разбор формулы по частям:

…ИЛИ(НЕ(ЕПУСТО($D$8));НЕ(ЕПУСТО($D$10));НЕ(ЕПУСТО($D$20)));1…- если выполняется одно из условий, что: ИЛИ ячейка D8 не пуста, то есть в семье есть штатный сотрудник ДОУ; ИЛИ ячейка D10 не пуста (ребенок посещает компенсирующую групп); ИЛИ ячейка D20 не пуста (в семье есть военнослужащий или инвалиды), то коэффициент Z равен 1.

…ИЛИ($D$5>2;НЕ(ЕПУСТО($D$12)));70…- если выполняется одно из условий, что: ИЛИ значение в ячейке D5 больше 2, (то есть ребенок третий или последующий в семье); ИЛИ ячейка D12 не пуста (доход ниже прожиточного минимума), то процент компенсации равен 70.

…ЕСЛИ(ИЛИ($D$5=2;НЕ(ЕПУСТО($D$18)));50…- если выполняется одно из условий, что: ИЛИ значение в ячейке D5 равно 2, то есть ребенок второй в семье; ИЛИ ячейка D18 не пуста (один из родителей - инвалид), то процент компенсации равен 50.

…ЕСЛИ(НЕ(ЕПУСТО($D$15));40…- если выполняется условие, что ячейка D15 не пуста (неполные семьи с недостаточным доходом), то процент компенсации равен 40.

…ЕСЛИ($D$5=1;20…- если выполняется условие, что значение в ячейке D5 равно 1, (то есть первый ребенок в семье), то процент компенсации равен 20.

…/100…- процент компенсации делим на 100, получаем коэффициент компенсации Z: 0,2; 0,4; 0,5; или 0,7. При 100% компенсации мы получили в первом действии коэффициент равный 1. В ячейке результат отображается в процентном формате(*100).

На сайте транслируется Калькулятор в формате Google-таблицы:

В ячейке Е19 коэффициент компенсации Z высчитывается по формуле:
=IF(OR(NOT(ISBLANK($D$7));NOT(ISBLANK($D$9));NOT(ISBLANK($D$17)));1;IF(OR($D$5>2;
NOT(ISBLANK($D$11)));70;IF(OR($D$5=2;NOT(ISBLANK($D$15)));50;IF(NOT(ISBLANK($D$13
));40;IF($D$5=1;20))))/100)

Разбор формулы по частям:

…IF(OR(NOT(ISBLANK($D$7));NOT(ISBLANK($D$9));NOT(ISBLANK($D$17)));1…

если выполняется одно из условий, что: ИЛИ ячейка D7 не пуста, то есть в семье есть штатный сотрудник ДОУ; ИЛИ ячейка D9 не пуста (ребенок посещает компенсирующую групп); ИЛИ ячейка D17 не пуста (в семье есть военнослужащий или инвалиды), то коэффициент Z равен 1.

…IF(OR($D$5>2;NOT(ISBLANK($D$11)));70;…

если выполняется одно из условий, что: ИЛИ значение в ячейке D5 больше 2, (то есть ребенок третий или последующий в семье); ИЛИ ячейка D11 не пуста (доход ниже прожиточного минимума), то процент компенсации равен 70.

…IF(OR($D$5=2;NOT(ISBLANK($D$15)));50;…

если выполняется одно из условий, что: ИЛИ значение в ячейке D5 равно 2, то есть ребенок второй в семье; ИЛИ ячейка D15 не пуста (один из родителей - инвалид), то процент компенсации равен 50.

…IF(NOT(ISBLANK($D$13));40;…

если выполняется условие, что ячейка D13 не пуста (неполные семьи с недостаточным доходом), то процент компенсации равен 40.

…IF($D$5=1;20…

если выполняется условие, что значение в ячейке D5 равно 1, (то есть первый ребенок в семье), то процент компенсации равен 20.

…/100…

процент компенсации делим на 100, получаем коэффициент компенсации Z: 0,2; 0,4; 0,5; или 0,7. При 100% компенсации мы получили в первом действии коэффициент равный 1.
В ячейке результат отображается в процентном формате(*100).

Разница с файлом Excel в том, что в google-калькуляторе не реализованы элементы управления формы "Флажок" и "Счетчик": количество детей заносится пользователем вручную, в ячейках с иными основаниями с D7 по D17 предлагается проставить любой символ, чтобы ячейка стала НЕПУСТОЙ.

Размер оплаты за присмотр и уход рассчитывается аналогично, как в Excel (см. по ссылке выше), с использованием вышеуказанной формулы для расчета размера компенсации:

=IF(AND(ISBLANK($D$7);(ISBLANK($D$9));(ISBLANK($D$17)));$M$4-($M$5*
IF(OR($D$5>2;NOT(ISBLANK($D$11)));70;IF(OR($D$5=2;NOT(ISBLANK($D$15)));50;
IF(NOT(ISBLANK($D$13));40;IF($D$5=1;20))))/100);0)

Считаем дополнительные траты:
учитываем желание родителя оплачивать добровольные целевые взносы:

считаем по простой формуле:
=IF(ISBLANK($D$23);0;350) - если ячейка D23 пуста, то результат равен 0 , если в ячейке стоит какой-нибудь символ, ячейка не пуста, то результат равен 350 (350 рублей - рекомендуемый размер целевых взносов).

Оплата платных образовательных услуг:

Не используя элементы управления формы, расчет производится по формуле:

=SUM(IF(NOT(ISBLANK($G$30));E30;0)+IF(NOT(ISBLANK($G$32));E32;0)+IF(NOT(ISBLANK($
G$34));E34;0)+IF(NOT(ISBLANK($G$36));E36;0)+IF(NOT(ISBLANK($G$38));E38;0)+IF(NOT(IS
BLANK($G$40));E40;0)+IF(NOT(ISBLANK($G$46));E46;0)+IF(NOT(ISBLANK($G$51));E51;0)+IF
(NOT(ISBLANK($G$53));E53;0)) - суммируем значения в столбце Е, если ячейки в столбце G не пусты.

Итоговую сумму всех затрат по оплате посещения ДОУ считаем простым сложением:
=$E$21+$E$24+$E$26 - суммируем оплату за присмотр и уход, целевые взносы и платные услуги. Аналогичная формула: =СУММ($E$21;$E$24;$E$26).

Небольшой недостаток калькулятора - следует добавить пункты "Гражданин РФ" и "Регистрация в СПб", так как компенсация оплаты за присмотр и уход предоставляется только гражданам РФ, зарегистрированным в СПб. - добавлено, читай ниже.

Ucoz предложил в 2017 году свой сервис калькулятора, но я еще не тестировала его.

Комментарии приветствуются:)

Добавлены пункты "Гражданин РФ" и "Регистрация в СПб":

Скачать дополненный Калькулятор родительской платы в Excel 

Теперь формула расчета компенсации выглядит так:

=ЕСЛИ(ИЛИ(НЕ($L$3);НЕ($L$5));0;ЕСЛИ(ИЛИ($L$13;$L$15;$L$25);1;ЕСЛИ(ИЛИ($D$10>2;
$L$17);70;ЕСЛИ(ИЛИ($D$10=2;$L$23);50;ЕСЛИ($L$20;40;ЕСЛИ($D$10=1;20;0))))/100))

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

Соответственно формула расчета оплаты за присмотр и уход тоже изменится:

=ЕСЛИ(ИЛИ(НЕ($L$3);НЕ($L$5));$M$9;ЕСЛИ(И(НЕ($L$13);НЕ($L$15);НЕ($L$25));$M$9-
($M$10*ЕСЛИ(ИЛИ($D$10>2;$L$17);70;ЕСЛИ(ИЛИ($D$10=2;$L$23);50;ЕСЛИ($L$20;40;ЕСЛИ
($D$10=1;20))))/100);0))

- на первом месте добавлено условие расчета компенсации: если не нажаты флажки в строках с гражданством и регистрацией, то размер оплаты равен значению в ячейке M9 (полная стоимость услуг): другие основания принимаются во внимание только при выполнении данных условий:

Создать копию дополненного Калькулятора родительской платы в Google-таблице

В google-таблице при добавлении условий наличия гражданства и регистрации формула расчета компенсации выглядит так:

=IF(OR(ISBLANK($D$3);ISBLANK($D$5));0;IF(OR(NOT(ISBLANK($D$11));NOT(ISBLANK($D$1
3));NOT(ISBLANK($D$21)));1;IF(OR($D$9>2;NOT(ISBLANK($D$15)));70;IF(OR($D$9=2;NOT
(ISBLANK($D$19)));50;IF(NOT(ISBLANK($D$17));40;IF($D$9=1;20))))/100))

- на первом месте добавлено условие расчета компенсации при заполненных ячейках в строках с гражданством и регистрацией (если ПУСТО, то "0"): другие основания принимаются во внимание только при выполнении данных условий:

Соответственно формула расчета оплаты за присмотр и уход тоже изменится:

=IF(OR(ISBLANK($D$3);ISBLANK($D$5));$M$8;IF(AND(ISBLANK($D$11);(ISBLANK($D$13));
(ISBLANK($D$21)));$M$8-($M$9*IF(OR(NOT(ISBLANK($D$11));
NOT(ISBLANK($D$13));NOT(ISBLANK($D$21)));0;IF(OR($D$9>2;NOT(ISBLANK($D$15)));70;
IF(OR($D$9=2;NOT(ISBLANK($D$19)));50;IF(NOT(ISBLANK($D$17));
40;IF($D$9=1;20))))/100));0))

- на первом месте добавлено условие расчета компенсации: если пустые ячейки в строках с гражданством и регистрацией, то размер оплаты равен значению в ячейке M8 (полная стоимость услуг): другие основания принимаются во внимание только при выполнении данных условий:

В Калькулятор на GoogleSheets добавлены кнопки и меню выбора значений:

Открыть копию Калькулятор с кнопками и меню выбора значений

Пример расчета калькулятора:

Кнопки и стрелочки для количества детей рисуем в рисунках Google, вставляем на лист, назначаем скрипты: для увеличения значения - increaseLine, для уменьшения - decreaseLine; в ячейке D9 (в данном примере) будет отображаться результат:

Создаем отдельные скрипты для кнопок и стрелок.

То, что в Excel реализовали с помощью элемента управления формы "Флажок", здесь реализуем с помощью меню выбора значения:

В формулах вместо пустоты ячеек учитываем , какое значение в ячейке: "да" или "нет".

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

=IF(OR($D$3="нет";$D$5="нет");0;IF(OR($D$11="да";$D$13="да";$D$21="да");1;IF(OR($D$9
>2;$D$15="да");70;IF(OR($D$9=2;$D$19="да");50;IF($D$17="да";40;IF($D$9=1;20))))/100))

Формула расчета размера оплаты за присмотр:

=IF(OR($D$3="нет";$D$5="нет");$M$8;IF(AND($D$11="нет";$D$13="нет";$D$21="нет");$M$8-
($M$9*IF(OR($D$11="да";$D$13="да";$D$21="да");1;IF(OR($D$9>2;$D$15="да");70;IF(OR(
$D$9=2;$D$19="да");50;IF($D$17="да";40;IF($D$9=1;20))))/100));0))

Формула расчета целевых взносов:

=IF($D$27="нет";0;350)

Формула расчета оплаты ПОУ:

=SUM(IF($G$34="да";E34;0)+IF($G$36="да";E36;0)+IF($G$38="да";E38;0)+IF($G$40="да";
E40;0)+IF($G$42="да";E42;0)+IF($G$44="да";E44;0)+IF($G$50="да";E50;0)+IF($G$55="да";
E55;0)+IF($G$57="да";E57;0))