Risultati da 1 a 13 di 13

Discussione: Formula CERCA con condizioni di testo e valori



  1. #1
    L'avatar di Pagero
    Clicca e Apri
    Data Registrazione
    Aug 2015
    Località
    Milano
    Età
    36
    Messaggi
    18
    Versione Office
    Excel 2013
    Likes ricevuti
    6
    Likes dati
    1

    Formula CERCA con condizioni di testo e valori

    Buongiorno,

    Il mio problema consiste nel cercare un valore in base ad un criterio di testo e di valore.

    File: https://onedrive.live.com/redir?resi...nt=file%2cxlsx

    Nel file allegato (utilizzo OneDrive in quanto si tratta di un file pesante) è presente una tabella di circa 3.000 righe e 15 colonne con le seguenti caratteristiche:

    1) nella colonna A è presente la tabella ANF (Assegno per il nucleo familiare) il cui elenco completo è nella colonna S;
    2) nelle colonne B e C è presente il reddito del nucleo familiare;
    3) nelle colonne da D a O sono presenti gli importi ANF in base al numero dei componenti del nucleo familiare.

    Il mio obbiettivo è quello di trovare l'importo ANF (nella cella C4) in base a tre criteri:
    - tabella ANF(colonna A);
    - Reddito (colonna B e C);
    - numero componenti (colonne da D a O).

    Ad Esempio:
    Tabella: Tab.12 (cella C1)
    Reddito: 19.000,00 (cella C2)
    Numero componenti: 3 (cella C3)
    Il risultato mi deve dare 213,92 (ossia la cella F788)

    N.B.: è presente un ulteriore problema. Come potete vedere ogni tabella parte da 0 (costante) fino ad arrivare un determinato reddito (variabile).

    Ad Esempio:
    Tabella "Tab.11" reddito massimo 99.302,72 (cella C746)
    Tabella "Tab.12" reddito massimo 110.119,01 (cella C1579)

    Pertanto in presenza della tabella "Tab.11" e con reddito di 100.000,00 l'importo ANF mi deve restituire 0 (zero) ma in presenza di tabella "Tab.12" (e con 6 componenti del nucleo familiare - colonna I) mi deve restituire il valore 18,32 (Cellla I1492).

    Spero di essere stato chiaro.

  2. #2
    L'avatar di ninai
    Clicca e Apri
    Data Registrazione
    Jul 2015
    Località
    Barcellona P.G.
    Età
    55
    Messaggi
    1689
    Versione Office
    2010 PC
    Likes ricevuti
    899
    Likes dati
    185
    ciao
    vi sono vari modi, per primo mi è venuto questo:
    =MATR.SOMMA.PRODOTTO(($A$8:$A$3101=C1)*($B$8:$B$3101<=C2)*($C$8:$C$3101>=C2)*(D7:O7=C3)*$D$8:$O$3101)
    ma devi eliminare i segni "-" dalla tabella
    se questo è un problema , troviamo altre formule

    edit
    ad esempio questa:
    =INDICE(D8:O3101;CONFRONTA(C2;INDICE((A8:A3101=C1)*B8:B3101;);1);CONFRONTA(C3;D7:O7;0))
    Ultima modifica fatta da:ninai; 19/08/15 alle 12:55

  3. #3

    L'avatar di ges
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Como
    Età
    53
    Messaggi
    7174
    Versione Office
    2011MAC 2016WIN
    Likes ricevuti
    2066
    Likes dati
    1308
    Una possibile soluzione:

    =INDICE(D8:O3101;CONFRONTA(C2;INDICE((A8:A3101=C1)*B8:B3101;);1);CONFRONTA(C3;D7:O7;0))

    Edit:
    Oppss: non mi ero accorto che la stessa formula l'aveva postata anche ninai
    Ultima modifica fatta da:ges; 19/08/15 alle 13:01
    Quando si scartano tutte le ipotesi possibili, quella che resta, anche se può sembrare improbabile, non può che essere quella giusta!

  4. #4
    L'avatar di ninai
    Clicca e Apri
    Data Registrazione
    Jul 2015
    Località
    Barcellona P.G.
    Età
    55
    Messaggi
    1689
    Versione Office
    2010 PC
    Likes ricevuti
    899
    Likes dati
    185
    figurati Ges è stato questione di qualche minuto , ne approfitto per un'altra opzione:
    =CERCA.VERT(C2;INDICE(B8:O3101*(A8:A3101=C1););CONFRONTA(C3;D7:O7;0)+2)

  5. #5

    L'avatar di ges
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Como
    Età
    53
    Messaggi
    7174
    Versione Office
    2011MAC 2016WIN
    Likes ricevuti
    2066
    Likes dati
    1308
    Ottima formula la tua prima ninai con MATR.SOMMA.PRODOTTO, mi piace molto.
    Anche il lavoro di Pagero mi sembra interessante e ho voluto dargli una mia interpretazione di forma, che allego da scaricare QUI.

    Quando si scartano tutte le ipotesi possibili, quella che resta, anche se può sembrare improbabile, non può che essere quella giusta!

  6. #6
    L'avatar di Pagero
    Clicca e Apri
    Data Registrazione
    Aug 2015
    Località
    Milano
    Età
    36
    Messaggi
    18
    Versione Office
    Excel 2013
    Likes ricevuti
    6
    Likes dati
    1
    Innanzitutto vi ringrazio per le risposte!

    Con la formula -> =MATR.SOMMA.PRODOTTO(($A$8:$A$3101=C1)*($B$8:$B$3101<=C2)*($C$8:$C$3101>=C2)*(D7:O7=C3)*$D$8:$O$3101)

    Funziona alla grande!


    > ma devi eliminare i segni "-" dalla tabella

    Non mi ero accorto che erano presenti dei segni "-", avendo la formattazione come "contabilità" li avevo confusi come degli zeri. Ho provveduto pertanto a sostituire il segno "-" in 0 (zero).



    Grazie!

  7. #7
    L'avatar di Gerardo Zuccalà
    Clicca e Apri
    Data Registrazione
    May 2015
    Località
    Milano, Italy
    Età
    49
    Messaggi
    4920
    Versione Office
    2013
    Likes ricevuti
    1117
    Likes dati
    1127
    Ciao ragazzi!
    giusto per condividere qualcosa

    =INDICE($D$8:$O$3101;CONFRONTA(C1&C2;A8:A3101&B8:B3101);CONFRONTA(C3;Elenco_Num_Componenti;0))

    da attivare con CTRL+SHIFT+ENTER
    alla fine una formula matrice non'è il male di tutti i mali

    PS da testare
    :299:

  8. #8

    L'avatar di ges
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Como
    Età
    53
    Messaggi
    7174
    Versione Office
    2011MAC 2016WIN
    Likes ricevuti
    2066
    Likes dati
    1308
    Accidenti Gerardo, quante cose che s'imparano qui!!! Questa non la sapevo, ho fatto qualche prova e funziona benissimo!!!

    Da correggere solo un piccolo errore, la corrispondenza del primo CONFRONTA approssimativa, vedo che nella formula ti è sfuggita:

    =INDICE($D$8:$O$3101;CONFRONTA(C1&C2;A8:A3101&B8:B3101;1);CONFRONTA(C3;D7:O7;0))
    Ultima modifica fatta da:ges; 19/08/15 alle 18:17
    Quando si scartano tutte le ipotesi possibili, quella che resta, anche se può sembrare improbabile, non può che essere quella giusta!

  9. #9
    L'avatar di Gerardo Zuccalà
    Clicca e Apri
    Data Registrazione
    May 2015
    Località
    Milano, Italy
    Età
    49
    Messaggi
    4920
    Versione Office
    2013
    Likes ricevuti
    1117
    Likes dati
    1127
    Ciao ges
    Di default il CONFRONTA ci restituisce corrispondenza approssimativa
    solo quando si mette zero (FALSO) e corrispondenza esatta
    infatti l'ultimo argomento del CONFRONTA è un argomento facoltativo che è tra parentesi quadra che vuol dire appunto facoltativo
    Se viene omesso ti da corrispondenza approssimativa di default come se fosse 1 (VERO)
    ciao Ges fai una verifica e fammi sapere!

  10. #10

    L'avatar di ges
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Como
    Età
    53
    Messaggi
    7174
    Versione Office
    2011MAC 2016WIN
    Likes ricevuti
    2066
    Likes dati
    1308
    Hai perfettamente ragione Gerardo, in effetti questa è una cosa elementare; solo che il MIO excel del Mac (famoso ormai per le magre figure! :52:) chissà per quale motivo se lo lascio senza argomento non mi funziona, se invece metto 1 invece va bene!
    Mi dovrò decidere di tornare a usare windows. :54:
    Grazie
    Quando si scartano tutte le ipotesi possibili, quella che resta, anche se può sembrare improbabile, non può che essere quella giusta!

  11. #11
    L'avatar di ninai
    Clicca e Apri
    Data Registrazione
    Jul 2015
    Località
    Barcellona P.G.
    Età
    55
    Messaggi
    1689
    Versione Office
    2010 PC
    Likes ricevuti
    899
    Likes dati
    185
    comunque è un tipo di quesito con moltissime soluzioni possibili, eccone qualcun'altra:

    =CERCA.ORIZZ(C3;D7:O3101;CONFRONTA(C2;INDICE((A8:A3101=C1)*B8:B3101;);1)+1)

    =SCARTO(D8;CONFRONTA(C2;INDICE((A8:A3101=C1)*B8:B3101;);1)-1;CONFRONTA(C3;D7:O7;0)-1)

    =INDIRETTO(INDIRIZZO(CONFRONTA(C2;INDICE((A8:A3101=C1)*B8:B3101;);1)+7;CONFRONTA(C3;D7:O7;0)+3))

  12. #12
    L'avatar di MauroS
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Abruzzo
    Età
    40
    Messaggi
    56
    Versione Office
    2007 WIN
    Likes ricevuti
    1
    Citazione Originariamente Scritto da ninai Visualizza Messaggio
    comunque è un tipo di quesito con moltissime soluzioni possibili, eccone qualcun'altra:

    =CERCA.ORIZZ(C3;D7:O3101;CONFRONTA(C2;INDICE((A8:A3101=C1)*B8:B3101;);1)+1)

    =SCARTO(D8;CONFRONTA(C2;INDICE((A8:A3101=C1)*B8:B3101;);1)-1;CONFRONTA(C3;D7:O7;0)-1)

    =INDIRETTO(INDIRIZZO(CONFRONTA(C2;INDICE((A8:A3101=C1)*B8:B3101;);1)+7;CONFRONTA(C3;D7:O7;0)+3))
    Ciao Ninai,
    come sempre hai diverse soluzioni per lo stesso problema. Ti volevo dire che ho provato le prime formule e funzionano tutte perfettamente mentre queste 3 formule danno 0 come risultato e con 8 persone come componenti nucleo familiare danno un risultato sballato. La cosa mi interessa molto per capire fino in fondo il meccanismo. Grazie.

  13. #13
    L'avatar di MauroS
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Abruzzo
    Età
    40
    Messaggi
    56
    Versione Office
    2007 WIN
    Likes ricevuti
    1
    Ciao Ninai,
    ho fatto diverse prove per capire e probabilmente ci deve essere qualcosa che non corrisponde nella colonna dove è indicato il numero di tabella in quanto cambiando la tabella (ad esempio dalla tab.11 alla tab.12 le 3 formule funzionano correttamente.
    Quella che sballa di più è la seconda con SCARTO(.....
    Probabilmente è solo un problema di scrittura del numero tabella nella convalida dati della cella C1

Discussioni Simili

  1. [Risolto] Cerca vert con piu condizioni
    Di Miglia nel forum Domande su Excel in generale
    Risposte: 7
    Ultimo Messaggio: 09/03/17, 16:49
  2. Risposte: 6
    Ultimo Messaggio: 28/01/17, 00:18
  3. [Risolto] Cerca valori che si ripetono in tabella. Formula per arrivare dove il cerca.vert non può
    Di Gatt88 nel forum Domande su Excel in generale
    Risposte: 7
    Ultimo Messaggio: 02/12/16, 11:45
  4. formula somma più se, con condizioni di testo o data
    Di -Matteo- nel forum Domande su Excel in generale
    Risposte: 31
    Ultimo Messaggio: 28/11/16, 21:33
  5. cerca.vert più condizioni
    Di Frandani nel forum Domande su Excel in generale
    Risposte: 4
    Ultimo Messaggio: 22/12/15, 22:23

Permessi di Scrittura

  • Tu non puoi inviare nuove discussioni
  • Tu non puoi inviare risposte
  • Tu non puoi inviare allegati
  • Tu non puoi modificare i tuoi messaggi
  •