Funzione condizionale SE con molte condizioni.   (30/03/03)

Abbiamo visto in questa sezione, paragrafo "Funzione =SE", come sia possibile annidare tutta una serie di condizioni nell'istruzione. Quando però le condizioni da valutare sono molteplici, e soprattutto richiedono dei calcoli per verificare una condizione se è molto diversificata, conviene sempre usare delle celle esterne alla cella dove vogliamo il risultato e dove risiede la formula =SE, per svolgere i calcoli usando ancora altre funzioni =SE. Questo soprattutto per evitare di scrivere un'unica chilometrica istruzione, problematica da gestire. L'idea per questa pagina è scaturita da una domanda postami, e di cui riporto il problema da risolvere:

"un rappresentante prende delle provvigioni in %
Come dati ho il valore in € della fattura (A1) e lo sconto che il rappresentante ha fatto (B1)
se fattura <5000€ prende l'8%
5000-25000€ prende il 4%
>25000€ prende il 2,5%
fin qui tutto OK: [=SE(A1<5000;A1*8%;SE(E(A1>=5000;A1<25000);A1*4%;SE(A1>=25000;A1*2,5%))) ]
Se applica fino al 5% di sconto sulla fattura il rappresentante prende la provvigione piena (l'8% o il 4% o il 2,5% a seconda dell'importo della fattura)
ma se applica sconti >5% la sua provvigione dovrà essere decurtata di 1/3 dello sconto oltre il 5%
esempio:
1000€ di fatt.
17% sconto fatto dal rappresentante
Il rappresentante dovrebbe prendere l'8% di provvigione ma avendo fatto uno sconto in più del 12% rispetto al 5%(per ottenere la provvigione massima) , egli si dovrà prendere carico di 12/3=4% che dovranno essere decurtate dalle sue provvigioni.. quindi 8%-4%= 4% totale che spetta al rappresentante.
Ultimissima cosa: minimo provvigionale=1,5% ".

Appare evidente che la formula già impostata (che peraltro per funzionare correttamente deve essere così modificata =SE(A1<5000;A1*8%;SE(E(A1>=5000;A1<25001);A1*4%;SE(A1>=25000;A1*2,5%))), ( in rosso il valore modificato) dovrebbe essere talmente ramificata da farci perdere l'orientamento. Per le variabili  richieste, costruiremo una tabella di riferimenti, alla quale attingere per ottenere le provvigioni da assegnare alle varie condizioni degli importi fattura, lavorando quindi non più con le provvigioni scritte direttamente nella formula, ma con i riferimenti alle celle che porteranno le variazioni delle provvigioni. In questo modo il risultato sarà quello giusto in funzione delle variabili.

Per meglio capire vediamo la situazione con le celle usate, in D1 la formula su esposta:

A questo punto nel creare la tabella dei riferimenti, avevamo bisogno di usare tre celle, una per ogni aliquota provvigionale, e in più:

• tre celle per ottenere l'eventuale differenza tra lo sconto max concesso (5% in B1) senza intaccare le provvigioni, e l'eventuale maggiore sconto. Differenza che servirebbe per ottenere la decurtazione dell'aliquota provvigionale (1/3) stabilita in funzione dell'importo fattura. Per fare questo useremo un'altra funzione =SE, che verifichi in valore in B1: se sarà 5% non ci saranno differenze e indicheremo 0 come %, in caso contrario si riporta la differenza sempre in percentuale, e questa la formula:

• =SE($B$1>5%;$B$1-5%;0)  - stessa formula per tutte e tre le celle

• tre celle con i valori provvigionali pieni ( 8% - 4% - 2,50% ).

• Altre tre celle (una per ogni aliquota provv.) nelle quali fare il conteggio per la decurtazione dell'aliq. provv. se lo sconto concesso fosse maggiore del 5%. Queste celle leggeranno ognuna, il valore dello sconto nelle tre celle viste al punto primo: nel caso che il valore sia zero, lasceranno la cella con la provvigione piena, altrimenti verrà conteggiato il valore della differenza sconto, dividendolo per 3 e sottraendolo alla provvigione piena; anche qui useremo un'altra funzione =SE :

• =SE($B$1=5%;8%;SE($B$1>5%;F3-(F4/3);F3))

per meglio capire queste formule e i riferimenti usati, vediamo la zona con la tabella dei riferimenti:

La formula su esposta è nella cella F2, nelle altre due celle G2 e H2 (Provvigione condizionata) le rispettive formule in funzione dei riferimenti celle e delle provvigioni di pertinenza:

• in G2 : =SE($B$1=5%;4%;SE($B$1>5%;G3-(G4/3);G3))

• in H2 : =SE($B$1=5%;2,5%;SE($B$1>5%;H3-(H4/3);H3))

Poichè esiste un'ultima condizione : che comunque le provvigioni non potranno essere inferiori all 1,5%, avremo bisogno di altre tre celle (riga 1) dove svolgere l'ultima condizione: se la riga delle provvigioni condizionate porterà valori inferiori a 1,5% dovrà essere applicato questo valore, altrimenti la provvigione maggiore. Anche in queste celle (quelle gialle) ogni cella userà i riferimenti alla cella provvigioni di pertinenza:

• in F1 : =SE(F2<1,5%;1,5%;F2)

• in G1 : =SE(G2<1,5%;1,5%;G2)

• in H1 : =SE(H2<1,5%;1,5%;H2)

ora ci troviamo con tre celle che riportano ognuno i valori provvigionali previsti da tutte le condizioni, e saranno i riferimenti a queste celle che noi indicheremo nella formula principale posta in D1, che diventerà quindi

• =SE(A1<5000;A1*F1; SE(E(A1>=5000;A1<25001);A1*G1; SE(A1>=25000;A1*H1)))

E' evidente che questo esercizio è indicizzato all'uso per il quale è stato previsto: lo scopo è quindi suggerire come comportarsi in situazioni simili: usando tabelle o celle "esterne" per svolgere una parte dei calcoli o condizioni diverse, e poi usare i riferimenti a queste celle nella formula principale.

prelevato sul sito http://ennius.interfree.it