[Excel] Domanda su formula
Salve, ho un problema con una formula matematica che dovrei applicare in un foglio elettronico.
• "A" stipula un contratto con "B" per delle forniture (concordando un listino per differenti servizi);
• "B" garantisce ad "A" un valore di fatturato "Fg", entro una tolleranza "y", in più e in meno;
• in caso il fatturato reale "Fr" sia minore di "Fg-y", "B" dovrà pagare ad "A" la differenza tra "Fg-y-Fr";
• in caso "Fr" superi "Fg+y", per l'eccedenza beneficerà dello sconto "z" che quantifichiamo in "H".
Fin qui tutto bene. La difficoltà l'ho incontrata quando ho inserito la seguente condizione:
• "B" è un insieme ("b1", "b2" e "b3", di cui abbiamo fissato i singoli "Fg");
• ciascuno ("b1", "b2" e "b3") ha gli stessi costi/benefici di "B" in relazione al proprio"Fg";
• la tolleranza "y" e lo sconto "z" sono percentualmente gli stessi per "B", "b1", "b2" e "b3".
Esempio
BASI PER IL CALCOLO
y 1,5%
z 20,0%
Caso
B
Fg 100,00
Fr 90,00
H 8,50
b1
Fgb1 51,00
Frb1 40,00
Hb1 6,95
b2
Fgb2 25,00
Frb2 30,00
Hb2 (0,93)
b3
Fgb3 24,00
Frb3 20,00
Hb3 2,47
E' possibile realizzare una formula dove, inserendo in tre campi distinti "Frb1, Frb2 e Frb3", si ottengano i valori di consuntivo "H" per "b1", "b2" e "b3"? in qualunque condizione possibile?
Anticipatamente grazie
• "A" stipula un contratto con "B" per delle forniture (concordando un listino per differenti servizi);
• "B" garantisce ad "A" un valore di fatturato "Fg", entro una tolleranza "y", in più e in meno;
• in caso il fatturato reale "Fr" sia minore di "Fg-y", "B" dovrà pagare ad "A" la differenza tra "Fg-y-Fr";
• in caso "Fr" superi "Fg+y", per l'eccedenza beneficerà dello sconto "z" che quantifichiamo in "H".
Fin qui tutto bene. La difficoltà l'ho incontrata quando ho inserito la seguente condizione:
• "B" è un insieme ("b1", "b2" e "b3", di cui abbiamo fissato i singoli "Fg");
• ciascuno ("b1", "b2" e "b3") ha gli stessi costi/benefici di "B" in relazione al proprio"Fg";
• la tolleranza "y" e lo sconto "z" sono percentualmente gli stessi per "B", "b1", "b2" e "b3".
Esempio
BASI PER IL CALCOLO
y 1,5%
z 20,0%
Caso
B
Fg 100,00
Fr 90,00
H 8,50
b1
Fgb1 51,00
Frb1 40,00
Hb1 6,95
b2
Fgb2 25,00
Frb2 30,00
Hb2 (0,93)
b3
Fgb3 24,00
Frb3 20,00
Hb3 2,47
E' possibile realizzare una formula dove, inserendo in tre campi distinti "Frb1, Frb2 e Frb3", si ottengano i valori di consuntivo "H" per "b1", "b2" e "b3"? in qualunque condizione possibile?
Anticipatamente grazie
Risposte
ciao, Ilmaestro,
vorrei provare ad aiutarti. Ma, aiutami ad aiutarti
Non capisco bene come trovi i valori di consuntivo H 6,95, (0,93) e 2,47 relativi a b1, b2 e b3.
Io ho così interpretato il quesito: i tre elementi b1, b2 e b3 dell'insieme B, rispettano le condizioni indicate per lo stesso ins B. è così? Condizioni dunque che possono essere diverse a considerare il singolo elemento b1, ....
Provo a copincollare l'esempio dal foglio di lavoro, con la speranza che venga leggibile.
A____B_____C____D______E_____________________________________________________________F____G
_____Fg____Fr____H_____formula in col D__________________________________________________y____1,5%
B___100____90___8,50___=SE($C$2<$B$2*(1-$G$1);B2*(1-$G$1)-C2;(C2-(B2*(1+$G$1)))*$G$2)___________z____20%
b_1__51____40___10,24__=SE($C$2<$B$2*(1-$G$1);B3*(1-$G$1)-C3;(C3-(B3*(1+$G$1)))*$G$2)
b_2__25____30___-5,38__=SE($C$2<$B$2*(1-$G$1);B4*(1-$G$1)-C4;(C4-(B4*(1+$G$1)))*$G$2)
b_3__24____20___3,64___=SE($C$2<$B$2*(1-$G$1);B5*(1-$G$1)-C5;(C5-(B5*(1+$G$1)))*$G$2)
come vedi, in colonna D la somma algebrica dei valori di H relativi a b1, b2, b3, è quella del val H relativo a B
Fammi sapere!
ciao
g
PS: i riferimenti di cella che vedi in corsivo-blue sono riferimenti assoluti. Il simbolo dollaro viene qui interpretato come codice ASCIIMath...
vorrei provare ad aiutarti. Ma, aiutami ad aiutarti
Non capisco bene come trovi i valori di consuntivo H 6,95, (0,93) e 2,47 relativi a b1, b2 e b3.
Io ho così interpretato il quesito: i tre elementi b1, b2 e b3 dell'insieme B, rispettano le condizioni indicate per lo stesso ins B. è così? Condizioni dunque che possono essere diverse a considerare il singolo elemento b1, ....
Provo a copincollare l'esempio dal foglio di lavoro, con la speranza che venga leggibile.
A____B_____C____D______E_____________________________________________________________F____G
_____Fg____Fr____H_____formula in col D__________________________________________________y____1,5%
B___100____90___8,50___=SE($C$2<$B$2*(1-$G$1);B2*(1-$G$1)-C2;(C2-(B2*(1+$G$1)))*$G$2)___________z____20%
b_1__51____40___10,24__=SE($C$2<$B$2*(1-$G$1);B3*(1-$G$1)-C3;(C3-(B3*(1+$G$1)))*$G$2)
b_2__25____30___-5,38__=SE($C$2<$B$2*(1-$G$1);B4*(1-$G$1)-C4;(C4-(B4*(1+$G$1)))*$G$2)
b_3__24____20___3,64___=SE($C$2<$B$2*(1-$G$1);B5*(1-$G$1)-C5;(C5-(B5*(1+$G$1)))*$G$2)
come vedi, in colonna D la somma algebrica dei valori di H relativi a b1, b2, b3, è quella del val H relativo a B
Fammi sapere!
ciao
g
PS: i riferimenti di cella che vedi in corsivo-blue sono riferimenti assoluti. Il simbolo dollaro viene qui interpretato come codice ASCIIMath...
Ciao Gio000,
innanzitutto grazie per l'attenzione e per la risposta.
Purtroppo i dati precisi li ho nella macchina in ufficio, pertanto verificherò solo mercoledì o giovedì.
Mi sono comunque accorto di due imprecisioni nell'esempio:
1) nell'insieme "B" il 90,00 è relativo a "Fr" e non "Frb1";
2) gli "h" dei singoli "b" sono arrotondati (in realtà avrebbero qualche decimale in più).
A presto.
IlMaestro
innanzitutto grazie per l'attenzione e per la risposta.
Purtroppo i dati precisi li ho nella macchina in ufficio, pertanto verificherò solo mercoledì o giovedì.
Mi sono comunque accorto di due imprecisioni nell'esempio:
1) nell'insieme "B" il 90,00 è relativo a "Fr" e non "Frb1";
2) gli "h" dei singoli "b" sono arrotondati (in realtà avrebbero qualche decimale in più).
A presto.
IlMaestro
"Ilmaestro":
...
Mi sono comunque accorto di due imprecisioni nell'esempio:
1) nell'insieme "B" il 90,00 è relativo a "Fr" e non "Frb1";
2) gli "h" dei singoli "b" sono arrotondati (in realtà avrebbero qualche decimale in più).
ciao,
ho tenuto conto... per la prima imprecisione.
Per la seconda, non è solo una questione di arrotondamenti (sempre secondo quanto ho capito io).
a presto!
g
Ciao gio000,
"b2" ha diritto allo sconto "z" solo per la quota che è eccedente al proprio "Fg" più la propria "y"; che corrisponde cioè a: ((25+1,5%)-30)*20%=-0,925;
"b1" e "b3" a questo punto (individualmente avrebbero quanto da Te riportato) si dovranno ripartire l'"H" di "B" più l'"H" di "b2", in proporzione al delta tra il proprio fatturato garantito ed il proprio fatturato reale; cioè: a "b1" mancano 10,235 e a "b3" mancano 3,64; quindi:
• per "b1" si avrà: (8,50+0,925)/(10,235+3,64)*10,235=6,95.....
• per "b3" si avrà: (8,50+0,925)/(10,235+3,64)*3,64=2,47.....
Con questo spero di essere riuscito a spiegarmi meglio.
A presto
"b2" ha diritto allo sconto "z" solo per la quota che è eccedente al proprio "Fg" più la propria "y"; che corrisponde cioè a: ((25+1,5%)-30)*20%=-0,925;
"b1" e "b3" a questo punto (individualmente avrebbero quanto da Te riportato) si dovranno ripartire l'"H" di "B" più l'"H" di "b2", in proporzione al delta tra il proprio fatturato garantito ed il proprio fatturato reale; cioè: a "b1" mancano 10,235 e a "b3" mancano 3,64; quindi:
• per "b1" si avrà: (8,50+0,925)/(10,235+3,64)*10,235=6,95.....
• per "b3" si avrà: (8,50+0,925)/(10,235+3,64)*3,64=2,47.....
Con questo spero di essere riuscito a spiegarmi meglio.
A presto
"Ilmaestro":
Ciao gio000,
"b2" ha diritto allo sconto "z" solo per la quota che è eccedente al proprio "Fg" più la propria "y"; che corrisponde cioè a: ((25+1,5%)-30)*20%=-0,925;
ok, capito dunque che ogni elemento b rispetta proprie condizioni (non quelle di B). Inoltre nella formula io cambiavo l'ordine dei termini della sottrazione nell'argomento [Se_falso]
Bisogna dunque togliere i primi due rif assoluti (C2 e B2) e cambiare quell'ordine.
"b1" e "b3" a questo punto (individualmente avrebbero quanto da Te riportato) si dovranno ripartire l'"H" di "B" più l'"H" di "b2", in proporzione al delta tra il proprio fatturato garantito ed il proprio fatturato reale; cioè: a "b1" mancano 10,235 e a "b3" mancano 3,64; quindi:
• per "b1" si avrà: (8,50+0,925)/(10,235+3,64)*10,235=6,95.....
• per "b3" si avrà: (8,50+0,925)/(10,235+3,64)*3,64=2,47.....
Con questo spero di essere riuscito a spiegarmi meglio.
Sì, mi pare tu abbia chiarito.
Hai un bel numero di possibili "disposizioni"!
Considerando che H di B può essere sia negativo che positivo e così anche gli H di b1, b2 e b3, hai in tutto $2^4$ possibili disposizioni (con ripetizioni): è così?
Mi chiederei cosa deve succedere, in più casi: se tutti gli H sono positivi, oppure se tutti sono negativi...
Perché mi pare di poter dire che la soluzione con formula richiede un "formulone" quasi illeggibile, chilometrico!
Se invece potessimo escludere delle condizioni e precisare quelle possibili, si potrebbe tentare una formula accettabile.
Penso tuttavia sia necessaria una soluzione mediante macro, con VBA di Excel [in cui però non sono in grado di aiutarti
]E a tal proposito, considerando anche che il tuo quesito mi sembra un po' OT in questo forum (vedo che non è inserito in alcuna specifica sezione), ti suggerirei di postare in un News Group dedicato.
Non so se qui è corretto mettere dei link, ti basta però cercare ad es:
microsoft.public.it.office.excel
ti assicuro che trovi dei veri maghi di Excel!
Non devi far altro che precisare bene le condizioni (per es le ultime che hai descritto qui), riceverai un aiuto certo!
un saluto
g
"gio000":
considerando anche che il tuo quesito mi sembra un po' OT in questo forum (vedo che non è inserito in alcuna specifica sezione)
La sezione adatta per questo topic sarebbe quella di Informatica.
"dissonance":
[quote="gio000"]considerando anche che il tuo quesito mi sembra un po' OT in questo forum (vedo che non è inserito in alcuna specifica sezione)
La sezione adatta per questo topic sarebbe quella di Informatica.[/quote]
vero!
ora vado a leggere un po'...
Vi ringrazio per le informazioni.
Nel frattempo, spezzando l'operazione in più parti, sono riuscito a risolverlo; poi, da li, ho provato a riunire il tutto. Ma, come dici giustamente tu, il formulone è illeggibile e impossibile, in quanto le celle di excel hanno un limite di caratteri di scrittura. Pertanto, se non esistono funzioni che le raggruppino, è di impossibile soluzione.
Comunque non mi sono arreso e, ricostruita la situazione in ambiente "FileMaker" (con Mac), la formula funziona.
Ora proverò a spostarmi nella sezione informatica e vediamo se si trova una soluzione anche in excel.
Un grazie ancora a tutti.
A presto
IlMaestro
Nel frattempo, spezzando l'operazione in più parti, sono riuscito a risolverlo; poi, da li, ho provato a riunire il tutto. Ma, come dici giustamente tu, il formulone è illeggibile e impossibile, in quanto le celle di excel hanno un limite di caratteri di scrittura. Pertanto, se non esistono funzioni che le raggruppino, è di impossibile soluzione.
Comunque non mi sono arreso e, ricostruita la situazione in ambiente "FileMaker" (con Mac), la formula funziona.
Ora proverò a spostarmi nella sezione informatica e vediamo se si trova una soluzione anche in excel.
Un grazie ancora a tutti.
A presto
IlMaestro
Scusatemi, vorrei spostare la questione (come mi avete suggerito) nella sezione di Informatica: come devo fare?
Ho provato ad entrare in "modifica", ma o non sono capace o non è il metodo giusto.
Grazie ancora
IlMaestro
Ho provato ad entrare in "modifica", ma o non sono capace o non è il metodo giusto.
Grazie ancora
IlMaestro
Excel non supporta più di 6 funzioni SE annidate.
C'è una cosa che non mi è chiara comunque.
8,5 è una penale, mentre 0,925 è uno sconto.
Perchè vengono sommati?
Inoltre, è B la somma degli elementi b1, b2, b3?
Se così fosse, non aspettarti che la somma degli Hb1, Hb2, Hb3 sia uguale a HB, perchè questo non è necessariamente vero (sarebbe un caso limite).
"b1" e "b3" a questo punto (individualmente avrebbero quanto da Te riportato) si dovranno ripartire l'"H" di "B" più l'"H" di "b2", in proporzione al delta tra il proprio fatturato garantito ed il proprio fatturato reale; cioè: a "b1" mancano 10,235 e a "b3" mancano 3,64; quindi:Non capisco cosa si debbano ripartire.
• per "b1" si avrà: (8,50+0,925)/(10,235+3,64)*10,235=6,95.....
• per "b3" si avrà: (8,50+0,925)/(10,235+3,64)*3,64=2,47.....
8,5 è una penale, mentre 0,925 è uno sconto.
Perchè vengono sommati?
Inoltre, è B la somma degli elementi b1, b2, b3?
Se così fosse, non aspettarti che la somma degli Hb1, Hb2, Hb3 sia uguale a HB, perchè questo non è necessariamente vero (sarebbe un caso limite).
8,5 è una penale, ma è anche il risultato della somma delle varie penali e/o sconti.
Cerco di fare un esempio diverso, escludendo le tolleranze e considerando solo gli sconti:
Aldo, Giovanni e Giacomo si accordano per fare acquisti e garantiscono al loro negoziante 300 euro al mese; questi 300 sono suddivisi in parti uguali (100 ognuno).
Se nessuno raggiunge il budget previsto il conto è semplice: ognuno pagherà per quanto gli manca.
Se tutti superano il budget: ognuno godrà dello sconto che gli spetta.
Ma facendo un esempio limite: Aldo acquista per 300, Giovanni per 0 e Giacomo per 0; se non si applicasse questo genere di formula (o concetto di gruppo di insieme) si otterrebbe che: Giovanni (per i 200 eccedenti) godrebbe di uno sconto di 40 euro; Giovanni e Giacomo pagherebbero una penale di 100 euro a testa; risultato che il negoziante incasserebbe 460 euro per vendite effettive di 300 (con un indebito incasso di 160 euro).
Nel mio caso, invece, gestendo le differenze (positive o negative che siano) all'interno dell'insieme (o gruppo di acquisto): Aldo avrebbe il suo sconto di 40 euro, Giovanni e Giacomo (beneficiando dei maggiori acquisti di Aldo) pagherebbero una penale di soli 20 euro a testa; e a questo punto il commerciante sarebbe perfettamente in pari (in quanto il contratto di acquistare 300 è stato rispettato e lui avrebbe incassato il giusto).
Per quanto riguarda excel ho ovviato al limite di condizioni spaccando la formula in più celle; ho inoltre provato a ricreare la situazione in un'applicazione diversa, e con File Maker (anche se incomprensibile) sono riuscito a scrivere una formula unica.
Il mio quesito era rivolto alla verifica che fossero esistite delle formule logiche che potessero sostituire quella serie infinita di "SE".
Grazie a Membri e Moderatori della pazienza
IlMaestro
Cerco di fare un esempio diverso, escludendo le tolleranze e considerando solo gli sconti:
Aldo, Giovanni e Giacomo si accordano per fare acquisti e garantiscono al loro negoziante 300 euro al mese; questi 300 sono suddivisi in parti uguali (100 ognuno).
Se nessuno raggiunge il budget previsto il conto è semplice: ognuno pagherà per quanto gli manca.
Se tutti superano il budget: ognuno godrà dello sconto che gli spetta.
Ma facendo un esempio limite: Aldo acquista per 300, Giovanni per 0 e Giacomo per 0; se non si applicasse questo genere di formula (o concetto di gruppo di insieme) si otterrebbe che: Giovanni (per i 200 eccedenti) godrebbe di uno sconto di 40 euro; Giovanni e Giacomo pagherebbero una penale di 100 euro a testa; risultato che il negoziante incasserebbe 460 euro per vendite effettive di 300 (con un indebito incasso di 160 euro).
Nel mio caso, invece, gestendo le differenze (positive o negative che siano) all'interno dell'insieme (o gruppo di acquisto): Aldo avrebbe il suo sconto di 40 euro, Giovanni e Giacomo (beneficiando dei maggiori acquisti di Aldo) pagherebbero una penale di soli 20 euro a testa; e a questo punto il commerciante sarebbe perfettamente in pari (in quanto il contratto di acquistare 300 è stato rispettato e lui avrebbe incassato il giusto).
Per quanto riguarda excel ho ovviato al limite di condizioni spaccando la formula in più celle; ho inoltre provato a ricreare la situazione in un'applicazione diversa, e con File Maker (anche se incomprensibile) sono riuscito a scrivere una formula unica.
Il mio quesito era rivolto alla verifica che fossero esistite delle formule logiche che potessero sostituire quella serie infinita di "SE".
Grazie a Membri e Moderatori della pazienza
IlMaestro
Quindi, a quanto ho capito, il "contratto" è basato sul totale e non sul singolo prodotto.
Un sistema per rimpiazzare molti SE annidati è utilizzare la funzione VLOOKUP (in italiano credo che sia "cercaorizzontale" o qualcosa di simile).
Un sistema per rimpiazzare molti SE annidati è utilizzare la funzione VLOOKUP (in italiano credo che sia "cercaorizzontale" o qualcosa di simile).
Si, ricordo questa funzione.
Proverò a vedere se si può utilizzare nel mio caso.
Grazie
IlMaestro
Proverò a vedere se si può utilizzare nel mio caso.
Grazie
IlMaestro
È sempre utilizzabile, basta costruire una tabella con le condizioni ed i risultati.
Accedi a tutti gli appunti
Tutor AI: studia meglio e in meno tempo