faretesto > microsoft.* > microsoft.office.excel

emanumela.v (31.05.2020, 12:45)
Ciao a tutti!
Ecco la sfida: trovare una soluzione semplice e alternativa alla formula data dal Prof:
Agli agenti di una azienda si vuole riconoscere le provvigioni
riconoscendo un premio se raggiungono il budget ed un premio maggiore
se lo superano di oltre il 110$.
In pratica gli viene riconosciuta una provvigione base se non raggiungono
il budget una provvigione premio 1 se superano il 100% e una
provvigione premio 2 se superano il 110%.

Dobbiamo calcolare la provvigione tenendo conto dei tre scaglioni
provigionali. Per fare questo useremo due funzioni: SE e E nidificate.

Formula del Prof:
SE(E(vendite/budget*100>100;vendite/budget*100<110);vendite*premio1;
SE(vendite/budget*100>110;vendite*premio2; vendite* provv.base

Ho provato con PIU.SE, ma non riesce...Potete aiutarmi? :|
Grazie mille a tutti!: )
Bruno Campanini (31.05.2020, 19:24)
emanumela.v presented the following explanation :
[..]
> SE(vendite/budget*100>110;vendite*premio2; vendite* provv.base
> Ho provato con PIU.SE, ma non riesce...Potete aiutarmi? :|
> Grazie mille a tutti!: )


=(I1<=100)*J1+(I1>100)*(I1<=110)*K1+(I1>110)*L1

I1 = volume vendite
J1 = provv. base
K1 = provv. se vendite >100
L1 = provv. se vendite >110

Bruno
buonocoreelio (01.06.2020, 02:06)
Intanto la tua formula è scritta male; c'è un errore logico. Per vendite = 110 e budget = 100 si ha paradossalmente che il nostro venditore becca solo la provvigione base, in quanto puoi verificare che vendite/budget * 100 è uguale a 110 che non passa nessuno dei 2 test logici VERO delle 2 funzioni SE nidificate.
Esemplificando:
vendite 110
budget 100
premio1 0,10
premio2 0,15
provv.base 0,07

la formula del tuo prof restituisce 7,7

La formula andrebbe riscritta
=SE(E(vendite/budget*100>100;vendite/budget*100<=110);vendite*premio1;
SE(vendite/budget*100>110;vendite*premio2;vendite*provv.base) )
che poi è equivalente a
=SE(vendite/budget*100>110;vendite*premio2;SE(vendite/budget*100>100;vendite*premio1;vendite*provv.base) )
che presenta una funzione in meno (E) con lo stesso livello di nidificazione dei SE
Con il ricorso a PIÙ.SE diventa

=PIÙ.SE(vendite/budget*100>110;vendite*premio2;vendite/budget*100>100;vendite*premio1;VERO;vendite*provv. base)

tutte con i nostri dati di esempio restituiscono 11.

La funzione PIÙ.SE ha il vantaggio di una maggiore leggibilità emanutenzione in quanto l'aggiunta di ulteriori condizioni di premio non comporta necessità di nidificazioni, mentre con i SE devi aumentare di un livello di nidificazione all'aggiunta di ogni nuova condizione. Ovviamente si deve avere Excel 2019 oppure Excel con abbonamento a office 365 per avere la funzione PIÙ.SE.

Per Bruno:
dimentichi la variabile budget, ma questo è un dettaglio. La tua formula funziona ma non mi sentirei di consigliarla. Contrariamente a PIÙ.SE costringe Excel a valutare tutte le condizioni false oltre all'unica vera.. PIÙ.SE interrompe il computo alla prima condizione vera con miglioreperformance in calcoli complessi. Prova a mettere una divisione per zero nella tua formula: restituirà sempre errore; se invece la inserisci in PIÙ.SE nei test logici a valle del primo vero oppure nei parametri 'setest vero' di tutte le condizioni logiche false ( anche antecedenti il primo test logico vero) la formula darà risultato corretto e diverso da errore.

Elio
buonocoreelio (01.06.2020, 10:10)
Il giorno lunedì 1 giugno 2020 02:06:56 UTC+2, buonoc...@gmail.com ha scritto:
[..]
> Per Bruno:
> dimentichi la variabile budget, ma questo è un dettaglio. La tua formula funziona ma non mi sentirei di consigliarla. Contrariamente a PIÙ..SE costringe Excel a valutare tutte le condizioni false oltre all'unica vera. PIÙ.SE interrompe il computo alla prima condizione vera con migliore performance in calcoli complessi. Prova a mettere una divisione per zeronella tua formula: restituirà sempre errore; se invece la inserisci in PIÙ.SE nei test logici a valle del primo vero oppure nei parametri 'se test vero' di tutte le condizioni logiche false ( anche antecedenti il primo test logico vero) la formula darà risultato corretto e diverso daerrore.
> Elio


Ancora per Bruno:
aggiungo che volendo risolvere alla tua maniera per ogni ulteriore livello di premio è necessario valutare 2 espressioni per il confronto (una per ogni confine di classe) + 1 per la restituzione del valore ( se tale invece che un riferimento a valore costante); in PIÙ.SE basta aggiungere 1 coppia di parametri ( ne accetta fino a 127) che non è detto neppure che verranno valutati al ricalcolo della formula. In sostanza i 2 approcci anche se comportano risultati uguali ( discorso a parte la gestione degli errori ) non possono essere considerati equivalenti sotto il profilo della computazione della macchina, prospettiva estranea all' algebra booleana.
Elio
Ala A. (01.06.2020, 17:28)
Ciao a tutti!
Prima di tutto grazie per il vostro tempo e i vostri consigli.
Ho provato ad inserire la formula che riscrivo qui sotto, ma il risultato è zero.
=PIÙ.SE(vendite/budget*100>110;vendite*premio2;vendite/budget*100>100;vendite*premio1;VERO;vendite*provv. base)

Ma come è possibile...
Grazie ancora
Ala A. (01.06.2020, 17:53)
....ho un dubbio:
=PIÙ.SE(vendite/budget*100>110;vendite*premio2;vendite/budget*100>100;vendite*premio1;VERO;vendite*provv. base)

Questa formula contempla la possibilità: =100?

Era scritto:
La formula andrebbe riscritta
=SE(E(vendite/budget*100>100;vendite/budget*100<=110);vendite*premio1;
SE(vendite/budget*100>110;vendite*premio2;vendite*provv.base) )
che poi è equivalente a
=SE(vendite/budget*100>110;vendite*premio2;SE(vendite/budget*100>100;vendite*premio1;vendite*provv.base) )

Non lo vedo neppure qui...sono proprio a zero, vero? Grazie mille se me lo spiegherete.
buonocoreelio (01.06.2020, 19:48)
>Ho provato ad inserire la formula che riscrivo qui sotto, ma il risultato è >zero.

Ti hai postato una formula con nomi definiti che io ho ricreato sul mio file . È ovvio che se non ricrei i nomi e i valori come nel mio esempio potresti avere risultati bizzarri Se per es la cella denominata vendite è vuota tutti i prodotti che hanno come fattore vendite daranno zero. Stesso dicasi per le varie provvigioni. Se nella casella Nome (la casella a sinistra della barra della formula) scrivi vendite e premi invio quale cella attiva Excel?
Elio

>Questa formula contempla la possibilità: =100?

SI (o meglio NI)
Se tutte le coppie di parametri non contemplano il criterio Excel darebbe errore di valore non disponibile (#ND); il trucco sta nel forzarlo con un VERO all'ultima coppia di parametri a restituire l'ultimo parametro. Quindi pur non essendo esplicitata in via diretta la possibilità vendite/budget*100 = 100 Excel, la fa rientrare in VERO dell'ultima coppia di parametri.

Elio
Bruno Campanini (01.06.2020, 20:39)
Ala A. pretended :
[..]
> =SE(vendite/budget*100>110;vendite*premio2;SE(vendite/budget*100>100;vendite*premio1;vendite*provv.base) )
> Non lo vedo neppure qui...sono proprio a zero, vero? Grazie mille se me lo
> spiegherete.


Fammi capire:

- budget 100
- vendite <=100 ===> provv 13 (per unità o per blocco)
- 100<vendite<=110 ===> provv 15 ( "" "" )
- vendite > 110 ===> provv 25 ( "" "" )

Il 15 viene applicato su tutte le vendite o (in aggiunta al 13)
solo su quelle che eccedono le prime 100?
analogamente per il 25.

Ovvero fa' un esempio numerico.

Bruno
Ala A. (02.06.2020, 12:37)
Grazie a tutti, dico davvero!

Bruno, cerco di essere più chiara, dovevo farlo da subito, perdonami.
Allora, la prima riga:
- budget --->250000
- vendite --->292.760?
- provvigione base <100% --->4,75%
- premio1 >100% --->5,50%
- premio2 >110% --->7,50%
Penso ci sia tutto...

Elio, ho cambiato con i dati e viene correttissima! Chissà cosa sbagliavo...forse ero troppo stanca. Sai, non mi rendo conto del tempo che passa quando "gioco" con Excel, mi piace tanto!

Vi invidio moltissimo, voi siete espertissimi.
Comunque sono felice, la sfida è vinta. La formula del Prof è stata semplificata!
Complimenti a tutti voi!!!
ed io ho imparato tantissimo!
Grazie
Ammammata (03.06.2020, 10:50)
Il giorno Tue 02 Jun 2020 12:37:12p, *Ala A.* ha inviato su
microsoft.public.it.office.excel il messaggio news:17205bda-2433-4055-aedb-
2fec59b8a74e. Vediamo cosa ha scritto:

> - provvigione base <100% --->4,75%
> - premio1 >100% --->5,50%


<temperasupposte mode ON>
scritta così se si raggiunge il 100% esatto e preciso non ci sarebbe
commissione
<temperasupposte mode OFF>

quindi il 100% giusto-giusto viene pagato 4.75 oppure 5.50?
Ala A. (03.06.2020, 11:16)
Annamata grazie, infatti Elio aveva scritto di aggiungere >=100.
L'ho fatto, grazie anche a te : )
buonocoreelio (03.06.2020, 15:22)
Il giorno mercoledì 3 giugno 2020 11:16:11 UTC+2, Ala A. ha scritto:
> Annamata grazie, infatti Elio aveva scritto di aggiungere >=100.
> L'ho fatto, grazie anche a te : )


Tralasciando i problemi nati dalla mancata accuratezza degli operatori >=e <=
la domanda di ALA.A era sull'uso della funzione PIÙ.SE per semplificare le formule con nidificazioni di SE. Poco rileva che la classi di provvigioni si applichino anche a volumi di vendite delle classi precedenti piuttosto sul solo volume che eccedano le classi precedenti. L'intervento di Brunocon una formula senza nidificazioni di stampo algebrico booleano è stato lo spunto per considerazioni di performance delle formule che spesso omettiamo quando forniamo soluzioni.

Elio
Bruno Campanini (03.06.2020, 19:23)
buonocoreelio explained on 03-06-20 :
> Il giorno mercoledì 3 giugno 2020 11:16:11 UTC+2, Ala A. ha scritto:
> Tralasciando i problemi nati dalla mancata accuratezza degli operatori >= e
> <= la domanda di ALA.A era sull'uso della funzione PIÙ.SE per semplificare le
> formule con nidificazioni di SE. Poco rileva che la classi di provvigioni si
> applichino anche a volumi di vendite delle classi precedenti piuttosto sul
> solo volume che eccedano le classi precedenti. L'intervento di Bruno con una
> formula senza nidificazioni di stampo algebrico booleano è stato lo spunto
> per considerazioni di performance delle formule che spesso omettiamo quando
> forniamo soluzioni.
> Elio


È un gran casino!
Se ho ben capito, ma non ne son proprio sicuro:
- budget = 250000
- vendite = 260000
- provvigioni per vendite <250000 ... 0.0475
quindi se vendi 260000 devi applicare 0.0475 sulla parte
<250000... ma quanto vale <250000? 249999, 249999.9, 249999.99???
poi 0.055 sulla differenza 260000 e <250000...

Analogamente se aggiundi altra percentuale.

Non ho controllato la formula del prof perché le formule altrui,
specie se in ITA, non le capisco mai.

Per me è tempo perso.

Bruno
Bruno Campanini (04.06.2020, 03:34)
Bruno Campanini was thinking very hard :

> Per me è tempo perso.


Comunque, non riuscendo a prender sonno, se dall'esempio
(malamente esposto) di dovesse desumere:

- budget (B) = 250000
- vendite (V) = 292000
- provv fino a B = 0.0475
- provv su eccedenza fino a B*1.1 (275000) = 0.055
- provv su eccedenza senza limite 0.075
avremmo:

=V*0.0475+(V>B)*(V-B)*(0.055-0.0475)+(V>1.1*B)*(V-B*1.1)*(0.075-0.055)
pari a 14525 di provvigioni totali.
Bruno Campanini (04.06.2020, 03:42)
Bruno Campanini explained on 04-06-20 :
> Bruno Campanini was thinking very hard :
>> Per me è tempo perso.

> Comunque, non riuscendo a prender sonno, se dall'esempio
> (malamente esposto) di dovesse desumere: [...]


Errata
- provv su eccedenza senza limite 0.075
Corrige
- provv su eccedenza fino a V (292000) 0.075

Bruno

Discussioni simili