Страницы: 1
RSS
Расчет бонуса менеджерам
 
Добрый день уважаемые знатоки Excel.
Помогите с решением или советом. Суть вопроса в файле, кратко опишу задачу: Две таблицы: в одной данные по продажам в другой ограничения по суммам и процент бонуса. Необходимо рассчитать бонус в связке менеджер/клиент/продукт учитывая ограничения по суммам второй таблицы. Остальное расписал в файле, всю голову сломал GPT замучил)
Заранее благодарю!
 
Прикольная мотивация, в апреле - голый оклад?
 
нет конечно, это просо пример.
 
сумма накопительная если не принимать во внимание, что таблица с процентами и порогами крайне неудобна, то
=LOOKUP(
SUMIFS($E$2:E3;A$2:A3;[@менеджер];B$2:B3;[@клиент];C$2:C3;[@продукт]);
INDEX(CHOOSE({1\2\3};0;порог[порог1];порог[порог2]);MATCH(C3;порог[продукт];););INDEX(CHOOSE({1\2\3};0;порог[порог1]*порог[%порога1];(порог[порог2]-порог[порог1])*порог[%порога2]+порог[порог1]*порог[%порога1]);MATCH(C3;порог[продукт];);))+
(SUMIFS($E$2:E3;A$2:A3;[@менеджер];B$2:B3;[@клиент];C$2:C3;[@продукт])-LOOKUP(SUMIFS($E$2:E3;A$2:A3;[@менеджер];B$2:B3;[@клиент];C$2:C3;[@продукт]);INDEX(CHOOSE({1\2\3};0;порог[порог1];порог[порог2]);MATCH(C3;порог[продукт];);)))*LOOKUP(
SUMIFS($E$2:E3;A$2:A3;[@менеджер];B$2:B3;[@клиент];C$2:C3;[@продукт]);
INDEX(CHOOSE({1\2\3};0;порог[порог1];порог[порог2]);MATCH(C3;порог[продукт];););INDEX(CHOOSE({1\2\3};порог[%порога1];порог[%порога2];0);MATCH(C3;порог[продукт];);))-SUMIFS(F$2:F2;A$2:A2;[@менеджер];B$2:B2;[@клиент];C$2:C2;[@продукт])
Изменено: БМВ - 15.05.2024 11:11:06 (Формула исправлена файл ниже)
По вопросам из тем форума, личку не читаю.
 
Огромное человеческое спасибо БМВ!!! Работает, считает, понимаю насколько скуден мой опыт в EXCEL...
 
Формула
Код
=IFERROR(E3*IF(E3<=INDEX(J$3:J$9;MATCH($C3;$I$3:$I$9;0));INDEX(K$3:K$9;MATCH($C3;$I$3:$I$9;0));IF(AND(E3>INDEX(J$3:J$9;MATCH($C3;$I$3:$I$9;0));E3<=INDEX(L$3:L$9;MATCH($C3;$I$3:$I$9;0)));INDEX(M$3:M$9;MATCH($C3;$I$3:$I$9;0));""));"")
 
Цитата
Lelya7 написал:
понимаю насколько скуден мой опыт в EXCEL...
все познается в сравнении. Возможно нужно перевернуть  и просто на фоне ваших знаний , мои выглядят более внушительно. Всегда найдется более сильны и более слабый.
Если Excel один из последних, то можно кажется поджать формулу, да и быстрее станет.
=LET(_S;SUMIFS($E$2:E3;A$2:A3;[@менеджер];B$2:B3;[@клиент]);_M;MATCH(C3;порог[продукт];);_C;{1\2\3};_P;INDEX(CHOOSE(_C;0;порог[порог1];порог[порог2]);_M;);_P1;порог[порог1]*порог[%порога1];
LOOKUP(_S;_P;INDEX(CHOOSE(_C;0;_P1;(порог[порог2]-порог[порог1])*порог[%порога2]+_P1);_M;))+
(_S-LOOKUP(_S;_P))*LOOKUP(_S;_P;INDEX(CHOOSE(_C;порог[%порога1];порог[%порога2];0);_M;))-SUMIFS(F$2:F2;A$2:A2;[@менеджер];B$2:B2;[@клиент];C$2:C2;[@продукт]))




jakim, попытка хорошая но некорректная. Расчет в примере дан и описан.
По вопросам из тем форума, личку не читаю.
 
Здравствуйте
Цитата
из файла:  ...на примере СВЕРЛО:
янв  20000*3%=600
фев 30000*3%(так ка порог 50, а в январе уже заплатили за 20) + 30000*1%(это уже свыше 50т)= 900+300=1200
мар 12000*1%= 1200, так как за 50т 3% и 30т 1% мы уже выплатили
Расчет должен быть в связке менеджер/клиент/продукт
Несколько своеобразно, но...
"Первый пришёл - первый ушёл" -  ФИФО однако  )

БМВ,  Интересный подход, но почему, например,  Иванов/ЗВЕЗДА/ЗУБИЛО/янв/250 000  => 13300 а не 12 500 ? Т.е. почему в "зубилах" учитываем "свёрла"? Или я что-то не понял?
 
Цитата
Павел \Ʌ/ написал:
Т.е. почему в "зубилах" учитываем "свёрла"?
скорее всего я прошляпил еще одно условие, пока тестировал  :-(
SUMIFS($E$2:E3;A$2:A3;[@менеджер];B$2:B3;[@клиент]) - надо заменить на SUMIFS($E$2:E3;A$2:A3;[@менеджер];B$2:B3;[@клиент];C$2:C3;[@продукт])
=LOOKUP(
SUMIFS($E$2:E3;A$2:A3;[@менеджер];B$2:B3;[@клиент];C$2:C3;[@продукт]);
INDEX(CHOOSE({1\2\3};0;порог[порог1];порог[порог2]);MATCH(C3;порог[продукт];););INDEX(CHOOSE({1\2\3};0;порог[порог1]*порог[%порога1];(порог[порог2]-порог[порог1])*порог[%порога2]+порог[порог1]*порог[%порога1]);MATCH(C3;порог[продукт];);))+
(SUMIFS($E$2:E3;A$2:A3;[@менеджер];B$2:B3;[@клиент];C$2:C3;[@продукт])-LOOKUP(SUMIFS($E$2:E3;A$2:A3;[@менеджер];B$2:B3;[@клиент];C$2:C3;[@продукт]);INDEX(CHOOSE({1\2\3};0;порог[порог1];порог[порог2]);MATCH(C3;порог[продукт];);)))*LOOKUP(
SUMIFS($E$2:E3;A$2:A3;[@менеджер];B$2:B3;[@клиент];C$2:C3;[@продукт]);
INDEX(CHOOSE({1\2\3};0;порог[порог1];порог[порог2]);MATCH(C3;порог[продукт];););INDEX(CHOOSE({1\2\3};порог[%порога1];порог[%порога2];0);MATCH(C3;порог[продукт];);))-SUMIFS(F$2:F2;A$2:A2;[@менеджер];B$2:B2;[@клиент];C$2:C2;[@продукт])
По вопросам из тем форума, личку не читаю.
 
Использовал некоторые "нотки" этой темы  ;)  :
{ }
=СУММ(ЕСЛИОШИБКА(EXP(LN(E3-ABS(СУММЕСЛИМН(E$2:E2;A$2:A2;A3;B$2:B2;B3;C$2:C2;C3)-ВПР(C3;порог;2;)*{0;1})-ABS(СУММЕСЛИМН(E$3:E3;A$3:A3;A3;B$3:B3;B3;C$3:C3;C3)-ВПР(C3;порог;{2;4}; ))+ВПР(C3;порог;{2;4}; )-ВПР(C3;порог;2;)*{0;1})); )*ВПР(C3;порог;{3;5}; ))/2
 
У меня вышло так.
Код
=IFERROR(E3*IF(SUMIFS(E$3:E$17;C$3:C$17;C3;D$3:D$17;D3;C$3:C$17;C3)<=INDEX(K$3:K$9;MATCH(C3;J$3:J$9;0));INDEX(L$3:L$9;MATCH(C3;J$3:J$9;0));IF(AND(SUMIFS(E$3:E$17;C$3:C$17;C3;D$3:D$17;D3;C$3:C$17;C3)>INDEX(K$3:K$9;MATCH(C3;J$3:J$9;0));SUMIFS(E$3:E$17;C$3:C$17;C3;D$3:D$17;D3;C$3:C$17;C3)<=INDEX(M$3:M$9;MATCH(C3;J$3:J$9;0)));INDEX(L$3:L$9;MATCH(C3;J$3:J$9;0));""));0)
 
Вариант.  (365/Web, динамический массив, без изысков, в таблице не работает, только рядом.)
Скрытый текст
 
Павел \Ʌ/, вот это хорошо получилось.

Не удержался и сравнил скорость. Моё длинное творение опередило , что несколько удивляет
=LOOKUP(
SUMIFS($E$2:E17,A$2:A17,[@менеджер],B$2:B17,[@клиент],C$2:C17,[@продукт]),
INDEX(CHOOSE({1,2,3},0,порог[порог1],порог[порог2]),MATCH(C17,порог[продукт],),),INDEX(CHOOSE({1,2,3},0,порог[порог1]*порог[%порога1],(порог[порог2]-порог[порог1])*порог[%порога2]+порог[порог1]*порог[%порога1]),MATCH(C17,порог[продукт],),))+
(SUMIFS($E$2:E17,A$2:A17,[@менеджер],B$2:B17,[@клиент],C$2:C17,[@продукт])-LOOKUP(SUMIFS($E$2:E17,A$2:A17,[@менеджер],B$2:B17,[@клиент],C$2:C17,[@продукт]),INDEX(CHOOSE({1,2,3},0,порог[порог1],порог[порог2]),MATCH(C17,порог[продукт],),)))*LOOKUP(
SUMIFS($E$2:E17,A$2:A17,[@менеджер],B$2:B17,[@клиент],C$2:C17,[@продукт]),
INDEX(CHOOSE({1,2,3},0,порог[порог1],порог[порог2]),MATCH(C17,порог[продукт],),),INDEX(CHOOSE({1,2,3},порог[%порога1],порог[%порога2],0),MATCH(C17,порог[продукт],),))-SUMIFS(F$2:F16,A$2:A16,[@менеджер],B$2:B16,[@клиент],C$2:C16,[@продукт])             1687,5
=SUM(IFERROR(EXP(LN(E17-ABS(SUMIFS(E$2:E16,A$2:A16,A17,B$2:B16,B17,C$2:C16,C17)-VLOOKUP(C17,порог,2,)*{0,1})-ABS(SUMIFS(E$3:E17,A$3:A17,A17,B$3:B17,B17,C$3:C17,C17)-VLOOKUP(C17,порог,{2,4},))+VLOOKUP(C17,порог,{2,4},)-VLOOKUP(C17,порог,2,)*{0,1})),)*VLOOKUP(C17,порог,{3,5},))/2  1734,375

Изменено: БМВ - 15.05.2024 20:37:39
По вопросам из тем форума, личку не читаю.
Страницы: 1
Наверх