Funzione SE (forse troppo lunga?)

[incodemy]

Ciao a tutti, un infinito grazie a chi deciderà di dedicare del tempo per aiutarmi.
Ho scritto questo codice, ma mi fornisce errore e non capisco perché visto che è praticamente identico ad uno che invece funziona.
Copio e incollo:

Codice: Seleziona tutto

=SE(B2<700;40%;SE(E(B2>=700;A2="B01");10%;SE(E(B2>=700;A2="B02");10%;SE(E(B2>=700;A2="B03");10%;SE(E(B2>=700;A2="B04");10%;SE(E(B2>=700;A2="B05");10%;SE(E(B2>=700;A2="B07");10%;SE(E(B2>=700;A2="B08");10%;SE(E(B2>=700;A2="B09");10%;SE(E(B2>=700;A2="B10");10%;SE(E(B2>=700;A2="B11");10%;SE(E(B2>=700;A2="B12");10%;SE(E(B2>=700;A2="B13");10%;SE(E(B2>=700;A2="B14");10%;SE(E(B2>=700;A2="B15");10%;SE(E(B2>=700;A2="B16");10%;SE(E(B2>=700;A2="B17");10%;SE(E(B2>=700;A2="B18");10%;SE(E(B2>=700;A2="B19");10%;SE(E(B2>=700;A2="C01");30%;SE(E(B2>=700;A2="C02");30%;SE(E(B2>=700;A2="C03");30%;SE(E(B2>=700;A2="C04");30%;SE(E(B2>=700;A2="C05");10%;SE(E(B2>=700;A2="C06");10%;SE(E(B2>=700;A2="C07");10%;SE(E(B2>=700;A2="C08");10%;SE(E(B2>=700;A2="C09");10%;SE(E(B2>=700;A2="C10");30%;SE(E(B2>=700;A2="C11");10%;SE(E(B2>=700;A2="C12");10%;SE(E(B2>=700;A2="C13");10%;SE(E(B2>=700;A2="C14");10%;SE(E(B2>=700;A2="C15");30%;SE(E(B2>=700;A2="C16");30%;SE(E(B2>=700;A2="C17");10%;SE(E(B2>=700;A2="C18");10%;SE(E(B2>=700;A2="C19");10%;SE(E(B2>=700;A2="C20");10%;SE(E(B2>=700;A2="C21");10%;SE(E(B2>=700;A2="C22");10%;SE(E(B2>=700;A2="C23");30%;SE(E(B2>=700;A2="C24");30%;SE(E(B2>=700;A2="C25");10%;SE(E(B2>=700;A2="C26");10%;SE(E(B2>=700;A2="C27");10%;SE(E(B2>=700;A2="C28");10%;SE(E(B2>=700;A2="C29");10%;SE(E(B2>=700;A2="C30");10%;SE(E(B2>=700;A2="C31");10%;SE(E(B2>=700;A2="C34");10%;SE(E(B2>=700;A2="C35");10%;SE(E(B2>=700;A2="C36");10%;SE(E(B2>=700;A2="C37");10%;SE(E(B2>=700;A2="C38");10%;SE(E(B2>=700;A2="C39");30%;SE(E(B2>=700;A2="C40");30%;SE(E(B2>=700;A2="C41");30%;SE(E(B2>=700;A2="C42");30%;SE(E(B2>=700;A2="C51");10%;SE(E(B2>=700;A2="C54");10%;SE(E(B2>=700;A2="C55");10%;SE(E(B2>=700;A2="C56");10%;SE(E(B2>=700;A2="C63");10%;SE(E(B2>=700;A2="C66");10%;SE(E(B2>=700;A2="C67");10%;SE(E(B2>=700;A2="C70");10%;SE(E(B2>=700;A2="C71");10%;SE(E(B2>=700;A2="C74");10%;SE(E(B2>=700;A2="C75");10%;SE(E(B2>=700;A2="C76");10%;SE(E(B2>=700;A2="C77");10%;SE(E(B2>=700;A2="C78");10%;SE(E(B2>=700;A2="C79");10%;SE(E(B2>=700;A2="C80");10%;SE(E(B2>=700;A2="C81");10%;SE(E(B2>=700;A2="C82");10%;SE(E(B2>=700;A2="C83");10%;SE(E(B2>=700;A2="C84");10%;SE(E(B2>=700;A2="C85");10%;SE(E(B2>=700;A2="C86");10%;SE(E(B2>=700;A2="C87");10%;SE(E(B2>=700;A2="C95");10%;SE(E(B2>=700;A2="C96");10%;SE(E(B2>=700;A2="D20");10%;SE(E(B2>=700;A2="D21");10%;SE(E(B2>=700;A2="D22");10%;SE(E(B2>=700;A2="D23");10%;SE(E(B2>=700;A2="D24");10%;SE(E(B2>=700;A2="D25");10%;SE(E(B2>=700;A2="D48");20%;SE(E(B2>=700;A2="D50");20%;SE(E(B2>=700;A2="D51");20%;SE(E(B2>=700;A2="D54");20%;SE(E(B2>=700;A2="D56");20%;SE(E(B2>=700;A2="D58");20%;SE(E(B2>=700;A2="D59");20%;SE(E(B2>=700;A2="D62");20%;SE(E(B2>=700;A2="D72");20%;"Errore"))

In pratica, se la casella B2 è minore di 700, mi deve scrivere 40%
se invece la casella B2 è maggiore di 700 E nella casella A2 c'è il codice B01 deve scrivere 10%
e così via poi per tutti gli importi maggiori di 700 con codice B02, B03, B04 ecc...

Perché mi dà errore? Forse è troppo lungo il codice? E se è così, c'è un modo per ridurlo? Ho provato ad abbreviarlo così:

Codice: Seleziona tutto

=SE(B2<700;40%;SE(E(B2>=700;A2="B01";"B02";"B03";"B04");10%;"Errore"))

ma non funziona lo stesso

[Rafkus_pl]

Ho incollato il tuo codice nel mio LO e ho ricevuto un errore 514 (codici di errore). Quindi sì, il codice è troppo lungo.

Penso che invece di una funzione SE nidificata puoi usare una funzione SWITCH:

Codice: Seleziona tutto

=SE(B2<700; 40%; SWITCH(A2;  "B01";10%;  "B02";10%;  "B03";10%;  "B04";10%;  "B05";10%;  "B07";10%;  "B08";10%;  "B09";10%;  "B10";10%;  "B11";10%;  "B12";10%;  "B13";10%;  "B14";10%;  "B15";10%;  "B16";10%;  "B17";10%;  "B18";10%;  "B19";10%;  "C01";30%;  "C02";30%;  "C03";30%;  "C04";30%;  "C05";10%;  "C06";10%;  "C07";10%;  "C08";10%;  "C09";10%;  "C10";30%;  "C11";10%;  "C12";10%;  "C13";10%;  "C14";10%;  "C15";30%;  "C16";30%;  "C17";10%;  "C18";10%;  "C19";10%;  "C20";10%;  "C21";10%;  "C22";10%;  "C23";30%;  "C24";30%;  "C25";10%;  "C26";10%;  "C27";10%;  "C28";10%;  "C29";10%;  "C30";10%;  "C31";10%;  "C34";10%;  "C35";10%;  "C36";10%;  "C37";10%;  "C38";10%;  "C39";30%;  "C40";30%;  "C41";30%;  "C42";30%;  "C51";10%;  "C54";10%;  "C55";10%;  "C56";10%;  "C63";10%;  "C66";10%;  "C67";10%;  "C70";10%;  "C71";10%;  "C74";10%;  "C75";10%;  "C76";10%;  "C77";10%;  "C78";10%;  "C79";10%;  "C80";10%;  "C81";10%;  "C82";10%;  "C83";10%;  "C84";10%;  "C85";10%;  "C86";10%;  "C87";10%;  "C95";10%;  "C96";10%;  "D20";10%;  "D21";10%;  "D22";10%;  "D23";10%;  "D24";10%;  "D25";10%;  "D48";20%;  "D50";20%;  "D51";20%;  "D54";20%;  "D56";20%;  "D58";20%;  "D59";20%;  "D62";20%;  "D72";20%;  "Errore"))

forse questa funzione ha un nome diverso nella tua lingua; è disponibile solo in LO.

@Gioh66 ha ragione quando scrive:

ma non è che migliori molto.
Piuttosto se hai tante percentuali ti conviene fare una tabellina impostando gli scaglioni minimi e usando il CERCA.VERT

[incodemy]

Grazie mille per la formula che hai addirittura fatto completa (mi sembra). Fino a domenica prossima non riesco a controllarla comunque ti so dire e grazie infinite. Ma cosa intendi per LO?

[Rafkus_pl]

abbreviazione LO = LibreOffice, OO = Apache OpenOffice.
Questa funzione non è in OO.

PS. Non ho terminato la funzione, ma l'ho ripulita dalle cose ridondanti. La funzione SE() controllerà il contenuto della cella A2 se la cella B2 è maggiore o uguale a 700. L'uso di E() avrebbe senso se dovessi specificarlo anche per altri valori, ad esempio: 800, 1000 ...

[gioh66]

Come dice Rafkus_pl la formula è troppo lunga.
Modificando la tua seconda formula si potrebbe accorciare usando una costante di matrice, così

Codice: Seleziona tutto

=SE(B2<700;40%;SE(E(B2>=700;O(A2={"B01";"B02";"B03";"B04"}));10%;"Errore"))

ma temo che con tutte le variabili che hai potrebbero comunque essere troppe.
Ti consiglio di usare una colonna d'appoggio con i codici e usare

Codice: Seleziona tutto

=SE(B2<700;40%;SE(E(B2>=700;VAL.NUMERO(CONFRONTA(A2;H1:H168;0)));10%;"Errore"))

Nell'esempio che ti allego la formula è nella cella gialla, la precedente nella cella arancione

[incodemy]

Non so davvero come ringraziarti. Anche se come ho scritto non credo di riuscire a testarla prima di domenica prossima, farò il possibile. Devo però studiarla perché non conosco affatto quel formato. Ma ti chiedo.. nella formula che hai fatto, hai rispettato i miei codici e le percentuali? Appena posso scarico il file. Grazie davvero di cuore! E poi dicono che non esiste gente generosa. Sono anch'io così ma la prendo sempre in quel posto. Se riesco a fare il calcolatore lo regalerò ai 3.000 colleghi in azienda, solo xke senza di voi non ce l'avrei mai fatta

[gioh66]

Allora. Ho provato a ricostruire le percentuali in base ai codici e modificato la formula così

Codice: Seleziona tutto

=SE(B2<700;40%;SE(E(B2>=700;VAL.NUMERO(CONFRONTA(A2;H1:H168;0)));CERCA.VERT(A2;H1:I168;2;0);"Errore"))

Adesso vedi tu se manca qualcosa o se è di troppo e fai prove. Ti riallego il nuovo file.

[incodemy]

sono tornato. ringrazio infintamente tutti. Mi sono appena rimesso all'opera e la funzione switch di rafkus ha risolto... fino a quando non ho dovuto aggiungere altri codici e ho ricevuto di nuovo errore 512 (Formula troppo lunga Compilatore: il numero totale dei token interni (ovvero operatori, variabili, parentesi) nella formula supera 8192.) Quindi sono tornato di punto a capo. Ho provato a fare:
=SE(B2<700; 40%; SWITCH(A2; "B01">"B10";10%; ecc accorpando quindi i valori di B compresi tra 01 e 10 ma non riconosce il codice.
Non so che fare. La funzione di gioh66 è talmente complessa che non riesco a capirla

[incodemy]

inoltre la formula di gioh66 mi sembra che, usando cerca.vert sia costretto a fare una colonna con tutti i codici, ma è proprio quello che vorrei evitare per un puro semplice estetico del file

[gioh66]

Ciao. Bentornato. Le colonne d'appoggio non devono per forza essere sullo stesso foglio e in ogni caso hai un foglio con oltre mille colonne, le puoi mettere anche in un punto fuori schermo.
Per quanto riguarda la formula ho messo che se la condizione è B2>=700 e se la funzione CONFRONTA restituisce un numero (il confronta trova in quale posizione della colonna si trova A2) allora tramite il cerca verticale ti dice qual'è il valore corrispondente ad A2 nella colonna a fianco.

[incodemy]

ok grazie mille, stavo testando la formula che mi avevi scritto all'inizio e l'ho terminata adesso.
L'ho fatta così ma mi da errore 509: Operatore mancante Operatore mancante; ad esempio, "=2(3+4) * ", dove manca l'operatore tra "2" e "(".

Codice: Seleziona tutto

=SE(B2<700;40%;SE(E(B2>=700;O(A2={"B01";"B02";"B03";"B04";"B05";"B07";"B08";"B09";"B10";"B11";"B12";"B13";"B14";"B15";"B16";"B17";"B18";"B19";"C05";"C06";"C07";"C08";"C09";"C11";"C12";"C13";"C14";"C17";"C18";"C19";"C20";"C21";"C22";"C25";"C26";"C27";"C28";"C29";"C30";"C31";"C34";"C35";"C36";"C37";"C38";"C51";"C54";"C55";"C56";"C63";"C66";"C67";"C70";"C71";"C74";"C75";"C76";"C77";"C78";"C79";"C80";"C81";"C82";"C83";"C84";"C85";"C86";"C87";"C95";"C96";"D20";"D21";"D22";"D23";"D24";"D25";});10%;(A2={"C01";"C02";"C03";"C04";"C10";"C15";"C16";"C23";"C24";"C39";"C40";"C41";"C42";});30%;(A2={"C43";"C44";"C45";"C46";"C47";"C48";"C49";"C50";"C52";"C57";"C58";"C59";"C60";"C61";"C62";"C64";"C65";"C68";"C69";"C88";"C89";"C90";"C91";"C92";"C93";"C94";"D01";"D02";"D12";"D48";"D50";"D51";"D54";"D56";"D58";"D59";"D62";"D72";});20%;)"mandante inesistente"))

non capisco cosa mi sono dimenticato in tutto sto casino. A me sembra tutto corretto. Riesci a vederlo?

[incodemy]

pensavo di aver beccato 2 punti e virgola inutili ma ancora errore 509

[incodemy]

ci sto perdendo gli occhi

[incodemy]

Ciao. Bentornato. Le colonne d'appoggio non devono per forza essere sullo stesso foglio e in ogni caso hai un foglio con oltre mille colonne, le puoi mettere anche in un punto fuori schermo.
Per quanto riguarda la formula ho messo che se la condizione è B2>=700 e se la funzione CONFRONTA restituisce un numero (il confronta trova in quale posizione della colonna si trova A2) allora tramite il cerca verticale ti dice qual'è il valore corrispondente ad A2 nella colonna a fianco.

ok questo rileggendolo 20 volte l'ho capito e si, capisco perfettamente la formula e dovrei testarla ma mi piacerebbe evitare come la peste di dover mettere quella colonna seppur molto fuori dal foglio e nascosta, ma se sarà l'unica soluzione amen farò così, però la formula che ho scritto per ultimo e che mi avevi scritto tu, mi aiuterebbe anche parecchio nelle altre alternative perché dovrei solo cambiare le 3 percentuali..se funzionasse però. Probabilmente mi sto perdendo in un bicchiere d'acqua ed è un problema di punti e virgola, parentesi tonde o graffe

[Rafkus_pl]

Ed è per questo che suggeriamo di utilizzare la funzione di ricerca verticale.
Ho guardato la tua ultima funzione ed è strano. Semplificato, assomiglia a questo:

Codice: Seleziona tutto

=SE(B2<700; 40%;
	SE(E(B2>=700; ... 20%;)"mandante inesistente"))

Tutto è atterrato nel comando E(), inoltre penso che tu abbia perso alcuni altri comandi SE(), E(), O()...

In allegato la formula corretta

Database con calc(bis4).ods

(11.47 KiB) Scaricato 51 volte

Formula suggerita:

Codice: Seleziona tutto

=PIÙ.SE(B2<700; 40%;
	O(A2={"B01"; ... ; "D25"}); 10%;
	O(A2={"C01"; ... ; "C45"}); 30%;
	O(A2={"C43"; ... ; "D72"}); 20%;
	1; "mandante inesistente")

Per leggibilità non ho elencato tutti i valori attesi (li ho sostituiti con tre puntini)

[incodemy]

Ora ho staccato perché ero cotto ma domani mattina controllo il file che mi hai mandato. Grazie infinite davvero!! Spero sia la volta buona

[incodemy]

Eccomi, controllo subito e poi ti scrivo

[incodemy]

Così sembra funzionare:

Codice: Seleziona tutto

=PIÙ.SE(B2<700;40%;O(A2={"B01";"B02";"B03";"B04";"B05";"B07";"B08";"B09";"B10";"B11";"B12";"B13";"B14";"B15";"B16";"B17";"B18";"B19";"C05";"C06";"C07";"C08";"C09";"C11";"C12";"C13";"C14";"C17";"C18";"C19";"C20";"C21";"C22";"C25";"C26";"C27";"C28";"C29";"C30";"C31";"C34";"C35";"C36";"C37";"C38";"C51";"C54";"C55";"C56";"C63";"C66";"C67";"C70";"C71";"C74";"C75";"C76";"C77";"C78";"C79";"C80";"C81";"C82";"C83";"C84";"C85";"C86";"C87";"C95";"C96";"D20";"D21";"D22";"D23";"D24";"D25";});10%;O(A2={"C01";"C02";"C03";"C04";"C10";"C15";"C16";"C23";"C24";"C39";"C40";"C41";"C42";});30%;O(A2={"C43";"C44";"C45";"C46";"C47";"C48";"C49";"C50";"C52";"C57";"C58";"C59";"C60";"C61";"C62";"C64";"C65";"C68";"C69";"C88";"C89";"C90";"C91";"C92";"C93";"C94";"D01";"D02";"D12";"D48";"D50";"D51";"D54";"D56";"D58";"D59";"D62";"D72"});20%;1;"mandante inesistente")

ma non ho capito la formula. Non capisco perché devo usare O se le 2 condizioni devono essere entrambe vere, cioè:
B2 deve essere >=700 E ALLO STESSO TEMPO, il codice deve essere ad esempio B01 per fornire come risultato 10%
E poi non capisco 1 prima di ;"mandante inesistente"

[Rafkus_pl]

La funzione PIÙ.SEè costituita da una coppia di elementi, ovvero una condizione e un risultato.

• Se la condizione 1 è vera otterrai il primo risultato, se la condizione 1 è falsa verrà verificata la condizione successiva.
• Se la condizione 2 è vera otterrai il risultato 2, se la condizione 2 è falsa verrà verificata la condizione successiva e così via fino all'ultima condizione.

Nel tuo caso, la funzione PLUSIF():

• - per la prima condizione, controlla se il numero in B2 < 700, nel qual caso otteniamo il 40%, in caso contrario, significa che il numero è >= 700 (o NOTA: è stato inserito del testo lì) e la funzione va per verificare la condizione successiva
• - la seconda condizione è verificare se il testo nella cella A2 è coerente con i valori della lista, cioè: {"B01"; ... ; "D25"}. La funzione OR restituisce vero se almeno una condizione è vera.
• se O() restituisce false, viene verificata un'altra condizione
• ultima condizione: 1 qui rappresenta un vero valore booleano. Assicura che il risultato sarà ottenuto se tutte le condizioni precedenti sono false.

[incodemy]

Ho scoperto che questa formula è ancora sbagliata o meglio incompleta:

Codice: Seleziona tutto

=PIÙ.SE(B2<700;40%;O(A2={"B01";"B02";"B03";"B04";"B05";"B07";"B08";"B09";"B10";"B11";"B12";"B13";"B14";"B15";"B16";"B17";"B18";"B19";"C05";"C06";"C07";"C08";"C09";"C11";"C12";"C13";"C14";"C17";"C18";"C19";"C20";"C21";"C22";"C25";"C26";"C27";"C28";"C29";"C30";"C31";"C34";"C35";"C36";"C37";"C38";"C51";"C54";"C55";"C56";"C63";"C66";"C67";"C70";"C71";"C74";"C75";"C76";"C77";"C78";"C79";"C80";"C81";"C82";"C83";"C84";"C85";"C86";"C87";"C95";"C96";"D20";"D21";"D22";"D23";"D24";"D25";});10%;O(A2={"C01";"C02";"C03";"C04";"C10";"C15";"C16";"C23";"C24";"C39";"C40";"C41";"C42";});30%;O(A2={"C43";"C44";"C45";"C46";"C47";"C48";"C49";"C50";"C52";"C57";"C58";"C59";"C60";"C61";"C62";"C64";"C65";"C68";"C69";"C88";"C89";"C90";"C91";"C92";"C93";"C94";"D01";"D02";"D12";"D48";"D50";"D51";"D54";"D56";"D58";"D59";"D62";"D72"});20%;1;"mandante inesistente")

quando scrivo nella cella A2 un codice non presente nella formula, ad esempio AAA, dovrebbe restituirmi l'errore "mandante inesistente" e invece rimangono le percentuali del codice digitato precedentemente.

Riepilogo quello che sto cercando di fare nella stessa funzione:
SE B2<700 il risultato deve essere SEMPRE 40%
SE B2>=700 e allo stesso tempo A2="B01" allora il risultato deve essere 10% altrimenti errore "mandante inesistente".
Forse così è più chiaro di quello che devo fare. Sono 2 condizioni che devono essere vere entrambe e non una che esclude l'altra quindi la "O" è sbagliata. Ecco perché non ricevo l'errore "mandante inesistente"

[Rafkus_pl]

Ricevo tali risultati (la formula PIU.SE è inclusa nella colonna D):

piuse.png (36.19 KiB) Visto 1481 volte

[incodemy]

infatti ho scoperto dopo aver scritto il commento che l'errore che deve essere scritto "mandante inesistente" non compare quando l'importo è inferiore a 700. Ho aperto un nuovo argomento spiegando una volta per tutte cosa ho bisogno di fare. Lo trovi qui, se ti va di aiutarmi: viewtopic.php?t=12030