La formula attuale calcola correttamente fino a 100 kg, usando il Cerca.Vert con "Intervallo" 1; avrei comunque usato <100.1 e non <101.
Per >100 il calcolo riparte da 14 + il costo a kg desunto da tabella.
Insomma, per fare un esempio, un collo da 99 kg paga 21.50 (tabella Cliente) e uno da 101 kg paga 15.00
Evidentemente andrebbe modificata la "base" di partenza a cui aggiungere il sovrapprezzo per peso.
Per le maggiorazioni, oggi le tabelle Calabria-Sicilia e Sardegna sono il 130% di quella "nazionale"; quindi volendo semplificare potremmo limitarci a moltiplicare per 1.3 il valore calcolato (1.26 la tariffa a kg).
Immagino pero' che queste tabelle possano variare con dinamiche piu' complesse, per cui credo sia meglio ignorare questa semplificazione e lavorare su tre tabelle diverse.
Per semplificarsi la vita in futuro, e' meglio prevedere che le tabelle possano crescere fino a 20 righe, e che comunque possano essere articolate diversamente per regione (es 10 fasce sul Nazionale e 5 fasce su Sardegna).
Come possiamo fare:
Per identificare l'ultima riga, in colonna 1 non usiamo la dizione "oltre xxx" ma 99999 (cioe' un peso oltre le peggiori aspettative)
Visto che hai Office 365, semplifichiamoci la vita usando la funzione LET.
Definiamo pertanto alcune "variabili":
Peso; il "peso convenzionale" da usare nel calcolo; in un caso sara' pari a
MAX($O3:$P3), nell'altro a
MAX($I3:$J3)SDC; e' lo "scarto orizzontale" tra le tabelle Dealer e quella Cliente finale; nel caso Dealer sara' impostato a 0, nel caso Cliente finale sara' impostato a 15
Tabella; e' la tabella da cui estrarre i costi, e sara' calcolata tenendo presente la Regione e l'ambiente (Dealer /Cliente finale). Trattasi della definizione piu' complessa, e io l'ho calcolata con questa formula:
- Codice: Seleziona tutto
SCARTO($AE$4;0;SDC+SE.ERRORE(AGGREGA(14;2;(RICERCA($G3;$AE$2:$AQ$2)>0)*(RIF.COLONNA($AE$2:$AQ$2)-RIF.COLONNA($AE$4));1);0);20;3)
$AE$4 rappresenta la "base" delle tabelle
$AE$2:$AQ$2 rappresenta l'intestazione in cui cercare la Regione
$G3 e' la Regione in questione
SDC e' lo scarto orizzontale aggiuntivo da utilizzare, a seconda che si tratti di Dealer o Cliente finale
Con queste impostazioni la formula finale SEMPLIFICATA e':
- Codice: Seleziona tutto
=LET(Peso;DefinizionePeso;SDC;DefinizioneScartoAggiuntivo;Tabella;DefinizioneTabella;CERCA.VERT(Peso;Tabella;3;1)+CERCA.VERT(99999;Tabella;3;1)*(Peso>100)*(Peso-100))
L'ho scritta semplificata per mostrare la relativa semplicita' della formula e per renderla leggibile.
Nella forma reale, per la cella Q3, sara':
- Codice: Seleziona tutto
=LET(Peso;MAX($O3:$P3);SDC;15;Tabella;SCARTO($AE$4;0;SDC+SE.ERRORE(AGGREGA(14;2;(RICERCA($G3;$AE$2:$AQ$2)>0)*(RIF.COLONNA($AE$2:$AQ$2)-RIF.COLONNA($AE$4));1);0);20;3);CERCA.VERT(Peso;Tabella;3;1)+CERCA.VERT(99999;Tabella;3;1)*(Peso>100)*(Peso-100))
La stessa formula puo' essere usata in S3, con le
correzioni alle definizioni di Peso e di SDC; quindi
- Codice: Seleziona tutto
=LET(Peso;MAX($I3:$J3);SDC;0;Tabella;SCARTO($AE$4;0;SDC+SE.ERRORE(AGGREGA(14;2;(RICERCA($G3;$AE$2:$AQ$2)>0)*(RIF.COLONNA($AE$2:$AQ$2)-RIF.COLONNA($AE$4));1);0);20;3);CERCA.VERT(Peso;Tabella;3;1)+CERCA.VERT(99999;Tabella;3;1)*(Peso>100)*(Peso-100))
Se in futuro cambia la posizione delle tabelle dovrai modificare quel $AE$4 (la base) e quei $AE$2:$AQ$2 (l'area delle intestazioni)
Se cambia la larghezza dovrai modificare quel $AE$4 (la base, probabilmente), quei $AE$2:$AQ$2 (l'area delle intestazioni) e anche i valori di SDC: rimarra' 0 per Dealer ma variera' l'offset delle tabelle Cliente)
Spero che sia tutto chiaro, a partire dall'uso della LET (se non conosci questa funzione guarda qui:
viewtopic.php?f=26&t=111058#p658673 e nell'help di Excel)
Ciao
Registrazione
