Risultati da 1 a 20 di 20

Discussione: estrarre dati su più tabelle 2° parte



  1. #1
    L'avatar di nick0573
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Pistoia
    Età
    47
    Messaggi
    1072
    Versione Office
    Excel 2016
    Likes ricevuti
    19
    Likes dati
    15

    estrarre dati su più tabelle 2° parte

    Ciao di nuovo ho modificato la tabella che ce nel foglio scadenza del nostro amico XOR LX
    spostando i riferimenti di cella e non riesco a dare quelli giusti per completare correttamente
    Questa tabella
    Grazie

    Vorrei di nuovo in B il cognome in C data di nascita e in D scad. visita medica
    Ultima modifica fatta da:nick0573; 07/08/15 alle 11:27
    windows 10
    Excel 2016

  2. #2

    L'avatar di ges
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Como
    Età
    53
    Messaggi
    7161
    Versione Office
    2011MAC 2016WIN
    Likes ricevuti
    2065
    Likes dati
    1301
    Ciao nick,
    la formula è complessa ma per i riferimenti delle scadenze basta cambiare solo queste 4 lettere evidenziate in rosso:

    =SE(RIGHE($1:1)>$I$1;"";INDICE(INDIRETTO("'"&INDICE(Fogli;MIN(SE(NUM(SCARTO(INDIRETTO("'"&Fogli&"'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6=PICCOLO(SE(T(SCARTO(INDIRETTO("'"&Fogli&"'!A2");Arry1-1;))<>"";NUM(SCARTO(INDIRETTO("'"&Fogli&"'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6);RIGHE($1:1));Arry2)))&"'!A2:A1000");MIN(SE(NUM(SCARTO(INDIRETTO("'"&Fogli&"'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6=PICCOLO(SE(T(SCARTO(INDIRETTO("'"&Fogli&"'!A2");Arry1-1;))<>"";NUM(SCARTO(INDIRETTO("'"&Fogli&"'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6);RIGHE($1:1));Arry1))))

    Il riferimento alla colonna e alla sua lunghezza è invece questo evidenziato in verde:

    =SE(RIGHE($1:1)>$I$1;"";INDICE(INDIRETTO("'"&INDICE(Fogli;MIN(SE(NUM(SCARTO(INDIRETTO("'"&Fogli&"'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6=PICCOLO(SE(T(SCARTO(INDIRETTO("'"&Fogli&"'!A2");Arry1-1;))<>"";NUM(SCARTO(INDIRETTO("'"&Fogli&"'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6);RIGHE($1:1));Arry2)))&"'!A2:A1000");MIN(SE(NUM(SCARTO(INDIRETTO("'"&Fogli&"'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6=PICCOLO(SE(T(SCARTO(INDIRETTO("'"&Fogli&"'!A2");Arry1-1;))<>"";NUM(SCARTO(INDIRETTO("'"&Fogli&"'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6);RIGHE($1:1));Arry1))))

    La formula sopra va nella cella A2 del foglio scadenze, mentre nelle celle B2 e C2 bisogna modificare A2:A1000 in B2:B1000 e D2:D1000
    Allo stesso modo nella cella I2 devi modificare in D2:D1000

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

  3. #3
    L'avatar di Jos
    Clicca e Apri
    Data Registrazione
    Aug 2015
    Località
    Torino, Piemonte
    Messaggi
    84
    Versione Office
    2013 PC
    Likes ricevuti
    90
    Likes dati
    5
    Ciao, Nick.

    La formula in D2 dovrebbe essere:

    =SE(RIGHE($1:1)>$J$1;"";INDICE(INDIRETTO("'"&INDICE(Fogli;MIN(SE(NUM(SCARTO(INDIRETTO("'"&Fogli&"'!K4");Arry1-1;))+Arry2/10^3+Arry1/10^6=PICCOLO(SE(T(SCARTO(INDIRETTO("'"&Fogli&"'!B4");Arry1-1;))<>"";NUM(SCARTO(INDIRETTO("'"&Fogli&"'!K4");Arry1-1;))+Arry2/10^3+Arry1/10^6);RIGHE($1:1));Arry2)))&"'!B4:B1000");MIN(SE(NUM(SCARTO(INDIRETTO("'"&Fogli&"'!K4");Arry1-1;))+Arry2/10^3+Arry1/10^6=PICCOLO(SE(T(SCARTO(INDIRETTO("'"&Fogli&"'!B4");Arry1-1;))<>"";NUM(SCARTO(INDIRETTO("'"&Fogli&"'!K4");Arry1-1;))+Arry2/10^3+Arry1/10^6);RIGHE($1:1));Arry1))))

    Il riferimento nella parte:

    NUM(SCARTO(INDIRETTO("'"&Fogli&"'!K4")...

    dev'essere sempre il primo riferimento nella gamma scad.vis.med. Non cambiare questo per altre colonne.

    Il riferimento nella parte:

    T(SCARTO(INDIRETTO("'"&Fogli&"'!B4")...

    dovrebbe essere il primo riferimento nella gamma che può essere utilizzata per verificare se la riga è vuota o no. Non cambiare questo per altre colonne.

    Il riferimento:

    "'!B4:B1000"

    dovrebbe essere la gamma da cui vuoi restituire i valori. Sarà necessario cambiare questo per altre colonne.

    Spero che aiuta.

    Darò una spiegazione completa della formula presto.

    Saluti
    Ultima modifica fatta da:Jos; 07/08/15 alle 12:38
    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    L'avatar di nick0573
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Pistoia
    Età
    47
    Messaggi
    1072
    Versione Office
    Excel 2016
    Likes ricevuti
    19
    Likes dati
    15
    Ciao Ragazzi ho caricato su dropbox il file originale, cosi come deve rimanere, ci sto fondendo,,,,:292:
    Se qualcuno potesse adattare il formulone nel foglio scadenze con tutti quattro i campi.

    Attenzione solo a una cosa nei fogli riferiti agli anni ho nascosto le righe fino a 50 per poter sfruttare all occorrenza per aggiungere altri nomi.......

    per il resto non ci capisco più nulla

    Grazie grazie Grazie grazie Grazie grazie Grazie grazie

    https://www.dropbox.com/s/cz18mmslqs...tori.xlsx?dl=0
    windows 10
    Excel 2016

  5. #5

    L'avatar di ges
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Como
    Età
    53
    Messaggi
    7161
    Versione Office
    2011MAC 2016WIN
    Likes ricevuti
    2065
    Likes dati
    1301
    @nick0573:
    Ma il file che ho caricato sopra non va bene? Ti ho messo la scadenza nella colonna D.
    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 nick0573
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Pistoia
    Età
    47
    Messaggi
    1072
    Versione Office
    Excel 2016
    Likes ricevuti
    19
    Likes dati
    15
    no no va bene l hai visto il file su dropbox??
    quello e originale se potessi fare quello sarebbe manna dal cielo
    windows 10
    Excel 2016

  7. #7

    L'avatar di ges
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Como
    Età
    53
    Messaggi
    7161
    Versione Office
    2011MAC 2016WIN
    Likes ricevuti
    2065
    Likes dati
    1301
    Citazione Originariamente Scritto da nick0573 Visualizza Messaggio
    no no va bene l hai visto il file su dropbox??
    quello e originale se potessi fare quello sarebbe manna dal cielo
    Adesso non ho tempo per cimentarmi, ma da un'occhiata veloce ho visto già un errore e cioè che hai nominato l'ultimo foglio riassuntivo "Scadenza" al posto di "Scadenze" come è indicato nella formula, così non la trova, quindi o cambi la formula o cambi il nome (meglio quest'ultima cosa!)
    Quando si scartano tutte le ipotesi possibili, quella che resta, anche se può sembrare improbabile, non può che essere quella giusta!

  8. #8
    L'avatar di nick0573
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Pistoia
    Età
    47
    Messaggi
    1072
    Versione Office
    Excel 2016
    Likes ricevuti
    19
    Likes dati
    15
    Ok.........e grazie per il tempo che ci dedichi
    windows 10
    Excel 2016

  9. #9

    L'avatar di ges
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Como
    Età
    53
    Messaggi
    7161
    Versione Office
    2011MAC 2016WIN
    Likes ricevuti
    2065
    Likes dati
    1301
    Ciao Nick,
    ho provato a cambiare i riferimenti di cella ma c'è qualcosa che non funziona se si utilizza la riga 4, bisognerebbe capire cosa fa la formula per sapere dove intervenire ma questo ce lo può dire solo Xor Lx.
    Intanto mi sono cimentato a modificare il tuo file, togliendo le prime due righe in modo che sia funzionante, vedi che te ne pare.
    Per scaricarlo, lo allego QUI
    Quando si scartano tutte le ipotesi possibili, quella che resta, anche se può sembrare improbabile, non può che essere quella giusta!

  10. #10
    L'avatar di nick0573
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Pistoia
    Età
    47
    Messaggi
    1072
    Versione Office
    Excel 2016
    Likes ricevuti
    19
    Likes dati
    15
    Hai fatto un gran lavoro Grazie.
    Ho provato nelle tabelle ad esmpio 2000 a cambiare una scadenza visita medica e mi sono accorto che nel foglio scadenze il nome non compare più
    cioe non viene visualizzato non sò perche............
    Mi sono accorto che se agiiorno una data di scadenza non deve essere superiore a quella gia esistente dell ultima già presente
    per questo non viene visualizzata


    Grazie in anticipo
    Ultima modifica fatta da:nick0573; 09/08/15 alle 22:35
    windows 10
    Excel 2016

  11. #11
    L'avatar di Beppe
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Brescia
    Età
    37
    Messaggi
    78
    Versione Office
    2010 Win
    Likes ricevuti
    1
    Non vedo l'ora:274:
    Ultima modifica fatta da:Canapone; 06/02/17 alle 16:24
    Windows 7
    Office 2010

  12. #12
    L'avatar di Jos
    Clicca e Apri
    Data Registrazione
    Aug 2015
    Località
    Torino, Piemonte
    Messaggi
    84
    Versione Office
    2013 PC
    Likes ricevuti
    90
    Likes dati
    5
    Ciao a tutti.

    Proverò di spiegare questa formula, utilizzando il file allegato. Per di più, agli effetti di questa spiegazione, sarà utile di fissare l'ultima riga a 10 (e non a, per esempio, 1000).

    (Mi scusate per il mio italiano.)

    Credo che la maggior parte delle persone capisce la formula in I1, che conta semplicemente il numero di celle non-vuote nella gamma A2:A10 tra tutti i fogli.

    Quindi mi concentrerò sulla formula in A1:

    =SE(RIGHE($1:1)>$I$1;"";INDICE(INDIRETTO("'"&INDICE(Fogli;MIN(SE(NUM(SCARTO(INDIRETTO("'"&Fogli&"'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6=PICCOLO(SE(T(SCARTO(INDIRETTO("'"&Fogli&"'!A2");Arry1-1;))<>"";NUM(SCARTO(INDIRETTO("'"&Fogli&"'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6);RIGHE($1:1));Arry2)))&"'!A2:A10");MIN(SE(NUM(SCARTO(INDIRETTO("'"&Fogli&"'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6=PICCOLO(SE(T(SCARTO(INDIRETTO("'"&Fogli&"'!A2");Arry1-1;))<>"";NUM(SCARTO(INDIRETTO("'"&Fogli&"'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6);RIGHE($1:1));Arry1))))

    Prima di tutto, sarà opportune considerare i due nomi definiti, Arry1 e Arry2. Allora cominciamo con Arry1, che è definita come:

    =RIF.RIGA(INDICE($A:$A;1):INDICE($A:$A;RIGHE(A$2:A$10)))

    Il punto di questa costruzione è di generare un (vettore colonna) matrice di numeri interi da 1 fino al numero di righe nel riferimento, cioè 9 (dalla riga 2 alla riga 10 compreso). Io preferisco questa costruzione alla solita:

    =RIF.RIGA(INDIRETTO("1:"&RIGHE(A$2:A$10)))

    poiché, anche se questo uso di INDICE è tecnicamente "parzialmente" volatile, non è affatto la stessa come il "completamente" volatile INDIRETTO.

    Qui, però, visto che siamo in ogni caso tenuti a usare INDIRETTO per il riferimento 3-D, si potrebbe sostenere che ulteriori tentativi di ridurre la volatilità sono un pochino ridondante. Eppure, è comunque una buona prassi.

    Si può facilmente vedere che quanto sopra risolve a:

    {1;2;3;4;5;6;7;8;9}

    Arry2 è una costruzione simile, vale a dire:

    =RIF.COLONNA(INDICE($1:$1;1):INDICE($1:$1;CONTA.VALORI(Fogli)))

    tuttavia in cui abbiamo utilizzato RIF.COLONNA al posto di RIF.RIGA, e in cui il valore superiore sarà, non il numero di righe, ma il numero di fogli.

    Certo, potremmo anche usare una costruzione con RIF.RIGA e poi trasporla:

    MATR.TRASPOSTA(RIF.RIGA(INDICE($A:$A;1):INDICE($A:$A;CONTA.VALORI(Fogli))))

    che ha il vantaggio che può essere usato per generare matrici più grandi (il numero di elementi nelle matrici derivate usando RIF.COLONNA è limitata a 16384; utilizzando RIF.RIGA possiamo aumentare questo limite a 1048576). Tuttavia, questa richiede una funzione in più (MATR.TRASPOSTA), e questa funzione addizionale è anche quella che, in genere, richiede CMI. Per questi motivi, e assumendo che 16384 è un sufficiente limite superiore per nostro matrice, io tendo a preferire una costruzione con RIF.COLONNA in questi casi.

    Quanto sopra risolve quindi a:

    {1,2,3}

    Va notato che è importante che questa matrice sia ortogonale a quella ottenuta da Arry1. Poiché Arry1 è una vettore colonna, costruiamo Arry2 come vettore riga. Questa è la ragione per l'uso di RIF.COLONNA qui, al contrario di RIF.RIGA che abbiamo usato per Arry1.

    La costruzione:

    NUM(SCARTO(INDIRETTO("'"&Fogli&"'!D2");Arry1-1;))

    genera un matrice di valori (numerici) da tutti i fogli per la gamma D2: D10. La sua risoluzione è:

    NUM(SCARTO(INDIRETTO({"'Foglio1'!D2","'Foglio2'!D2","'Foglio3'!D2"});Arry1-1;))

    che è:

    NUM(SCARTO(INDIRETTO({"'Foglio1'!D2","'Foglio2'!D2","'Foglio3'!D2"});{0;1;2;3;4;5;6;7;8};))

    che è:

    NUM({Foglio1'!D2,Foglio 2'!D2,Foglio 3'!D2;Foglio1'!D3,Foglio 2'!D3,Foglio 3'!D3;Foglio1'!D4,Foglio 2'!D4,Foglio 3'!D4;Foglio1'!D5,Foglio 2'!D5,Foglio 3'!D5;Foglio1'!D6,Foglio 2'!D6,Foglio 3'!D6;Foglio1'!D7,Foglio 2'!D7,Foglio 3'!D7;Foglio1'!D8,Foglio 2'!D8,Foglio 3'!D8;Foglio1'!D9,Foglio 2'!D9,Foglio 3'!D9;Foglio1'!D10,Foglio 2'!D10,Foglio 3'!D10})

    e poi NUM costringe questi riferimenti di intervallo nei loro valori reali, vale a dire:

    {42511,42084,42572;0,0,0;0,42056,42165;0,42107,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0}

    in cui i spazi vuoti nelle gamme sono stati resi come zeri.

    Quindi abbiamo una matrice di date da colonna D da tutti i fogli.

    Se potessimo assicuriamo che ogni data in questa matrice fosse unica, allora le cose sarebbero più facile. Poiché non possiamo essere sicuri di questo, sarà utile (anzi, necessario) per essere in grado di distinguere tra date identiche in qualche modo quando si tratta di identificare i dettagli associati a tali date.

    E questa differenziazione deve tener conto della possibilità di duplicazione o all'interno dello stesso foglio o in un foglio diverso. Con l'aggiunta di due piccoli incrementi ad ogni data, uno basato sulla riga e l'altra sul foglio, ci assicuriamo che le date nella nostra gamma sono uniche. Di più, affinché questi incrementi siano sufficientemente piccoli, garantiamo che non interferiamo negli risultati quando si tratta di collocare queste date in ordine crescente.

    Quindi:

    NUM(SCARTO(INDIRETTO("&'"&Fogli&"&'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6

    che è:

    {42511,42084,42572;0,0,0;0,42056,42165;0,42107,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0}+Arry2/10^3+Arry1/10^6

    diventa, dopo aver inserito Arry1 e Arry2:

    {42511,42084,42572;0,0,0;0,42056,42165;0,42107,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0}+{1,2,3}/10^3+{1;2;3;4;5;6;7;8;9}/10^6

    che è poi:

    {42511,42084,42572;0,0,0;0,42056,42165;0,42107,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,0}+{0.001,0.002,0.003}+{0.000001;0.000002;0.000003;0.000004;0.000005;0.000006;0.000007;0.000008;0.000009}

    i.e.:

    {42511.001001,42084.002001,42572.003001;0.001002,0.002002,0.003002;0.001003,42056.002003,42165.003003;0.001004,42107.002004,0.003004;0.001005,0.002005,0.003005;0.001006,0.002006,0.003006;0.001007,0.002007,0.003007;0.001008,0.002008,0.003008;0.001009,0.002009,0.003009}

    Non è importante che tutti i vuoti, cioè zeri, nelle nostre gamme originali sono ormai non-zeri, perché questi saranno in ogni caso esclusi tramite una clausola SE che utilizza:

    T(SCARTO(INDIRETTO("&'"&Fogli&"&'!A2");Arry1-1;))

    che, da una logica simile a quella di cui sopra, si risolve in una matrice di tutti i valori nella colonna A di tutti i 3 fogli, cioè:

    {"Pippo","Tippo","Lavoro";"","","";"","Casa","Forse";"","Culpa","";"","","";"","","";"","","";"","","";"","",""}

    Si noti che, visto che i valori di questa colonna sono testo, non numerico, dobbiamo usare T, non NUM, a costringere un matrice da SCARTO:

    Possiamo ora valutare:

    MIN(SE(NUM(SCARTO(INDIRETTO("&'"&Fogli&"&'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6=PICCOLO(SE(T(SCARTO(INDIRETTO("&'"&Fogli&"&'!A2");Arry1-1;))<>"";NUM(SCARTO(INDIRETTO("&'"&Fogli&"&'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6);RIGHE($1:1));Arry2))

    che, inserendo i nostri risultati dall'alto, diventa:

    MIN(SE({42511.001001,42084.002001,42572.003001;0.001002,0.002002,0.003002;0.001003,42056.002003,42165.003003;0.001004,42107.002004,0.003004;0.001005,0.002005,0.003005;0.001006,0.002006,0.003006;0.001007,0.002007,0.003007;0.001008,0.002008,0.003008;0.001009,0.002009,0.003009}=PICCOLO(SE({"Pippo";"Tippo";"Lavoro";"";"";"";"";"Casa";"Forse";"";"Culpa";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";""}<>"";{42511.001001,42084.002001,42572.003001;0.001002,0.002002,0.003002;0.001003,42056.002003,42165.003003;0.001004,42107.002004,0.003004;0.001005,0.002005,0.003005;0.001006,0.002006,0.003006;0.001007,0.002007,0.003007;0.001008,0.002008,0.003008;0.001009,0.002009,0.003009});RIGHE($1:1));Arry2))

    che è:

    MIN(SE({42511.001001,42084.002001,42572.003001;0.001002,0.002002,0.003002;0.001003,42056.002003,42165.003003;0.001004,42107.002004,0.003004;0.001005,0.002005,0.003005;0.001006,0.002006,0.003006;0.001007,0.002007,0.003007;0.001008,0.002008,0.003008;0.001009,0.002009,0.003009}=42056.002003;Arry2))

    Poiché siamo interessati a conoscere da quale dei nostri fogli tale valore deriva, impostiamo il argomento se_vero a Arry2, in modo che il sopra diventa:

    MIN(SE({FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,VERO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO};{1,2,3}))

    che è:

    MIN({FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,2,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO}))

    vale a dire 2.

    (Si noti che MIN viene utilizzato qui per tornare semplicemente l’unico valore numerico dalla matrice, cioè 2. Qualsiasi altra funzione adatta, ad esempio SOMMA, sarebbe anche sufficiente (ci sarà sempre solamente uno valore non-FALSO).)

    Questo vuole dire che la prima data da restituire è nel secondo dei nostri fogli. Possiamo quindi costruire:

    INDIRETTO("&'"&INDICE(Fogli;MIN(SE(NUM(SCARTO(INDIRETTO("&'"&Fogli&"&'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6=PICCOLO(SE(T(SCARTO(INDIRETTO("&'"&Fogli&"&'!A2");Arry1-1;))<>"";NUM(SCARTO(INDIRETTO("&'"&Fogli&"&'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6);RIGHE($1:1));Arry2)))&"&'!A2:A10")

    che ora è semplicemente:

    INDIRETTO("&'"&INDICE(Fogli;2)&"&'!A2:A10")

    i.e.:

    INDIRETTO("&'Foglio2&'!A2:A10")

    i.e.:

    Foglio2!A2:A10

    Avendo stabilito in che foglio risiede nostra valore, possiamo ora costruire una dichiarazione simile al fine di determinare in quale riga si trova, per il quale usiamo:

    MIN(SE(NUM(SCARTO(INDIRETTO("&'"&Fogli&"&'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6=PICCOLO(SE(T(SCARTO(INDIRETTO("&'"&Fogli&"&'!A2");Arry1-1;))<>"";NUM(SCARTO(INDIRETTO("&'"&Fogli&"&'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6);RIGHE($1:1));Arry1))

    che è, ancora una volta:

    MIN(SE({FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,VERO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO};Arry1))

    vale a dire esattamente la stessa costruzione come quella utilizzata per determinare il foglio appropriato, anche se questa volta, invece di utilizzare Arry2 per il argomento se_vero, invece usiamo Arry1, visto che, se vi ricordate, questa matrice è quella che contiene i nostri numeri di riga.

    Quindi quanto sopra è:

    MIN(SE({FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,VERO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO};{1;2;3;4;5;6;7;8;9}))

    i.e.:

    MIN({FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,3,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO})

    cioè 3.

    Ora possiamo mettere tutto insieme, in modo tale che:

    =SE(RIGHE($1:1)>$I$1;"";INDICE(INDIRETTO("'"&INDICE(Fogli;MIN(SE(NUM(SCARTO(INDIRETTO("'"&Fogli&"'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6=PICCOLO(SE(T(SCARTO(INDIRETTO("'"&Fogli&"'!A2");Arry1-1;))<>"";NUM(SCARTO(INDIRETTO("'"&Fogli&"'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6);RIGHE($1:1));Arry2)))&"'!A2:A10");MIN(SE(NUM(SCARTO(INDIRETTO("'"&Fogli&"'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6=PICCOLO(SE(T(SCARTO(INDIRETTO("'"&Fogli&"'!A2");Arry1-1;))<>"";NUM(SCARTO(INDIRETTO("'"&Fogli&"'!D2");Arry1-1;))+Arry2/10^3+Arry1/10^6);RIGHE($1:1));Arry1))))

    che è ora:

    =SE(FALSO;"";INDICE(Foglio2!A2:A10;3))

    i.e.:

    "Casa".

    Spero che sia d'aiuto.

    Saluti
    Ultima modifica fatta da:Jos; 11/08/15 alle 16:21
    Advanced Excel Techniques: http://excelxor.com/

  13. #13

    L'avatar di scossa
    Clicca e Apri
    Data Registrazione
    Jul 2015
    Località
    Verona Provincia
    Età
    57
    Messaggi
    1022
    Versione Office
    .
    Likes ricevuti
    366
    Likes dati
    0
    Premesso che non ho seguito tutto il thread, per cui potrei dire una cavolata, mi togli una curiosità?Visto che tanto se vuoi aumentare le righe di riferimento devi modificare manualmente il nome Arry1, allora perché anziché usare la complessa
    Codice: 
    =RIF.RIGA(INDICE(Scadenze!$A:$A;1):INDICE(Scadenze!$A:$A;RIGHE(Scadenze!A$2:A$10)))
    non usare la banale:
    Codice: 
    =RIF.RIGA(Scadenze!$1:$9)
    idem per Arry2 (colonne): anziché
    Codice: 
    =RIF.COLONNA(INDICE($1:$1;1):INDICE($1:$1;CONTA.VALORI(Fogli)))
    non è più veloce usare
    Codice: 
    =RIF.COLONNA(Scadenze!$A:$C)
    Ho provato a sostituirle e mi sembra funzioni tutto regolarmente.

  14. #14
    L'avatar di nick0573
    Clicca e Apri
    Data Registrazione
    Jun 2015
    Località
    Pistoia
    Età
    47
    Messaggi
    1072
    Versione Office
    Excel 2016
    Likes ricevuti
    19
    Likes dati
    15
    ottima formula

    ma se uno volesse un riferimento di riga invece che fino a 10 fino a 50 basta scrivere
    =RIF.RIGA(INDICE($A:$A;1):INDICE($A:$A;RIGHE(A$2:A$50))) ???????
    windows 10
    Excel 2016

  15. #15
    L'avatar di Jos
    Clicca e Apri
    Data Registrazione
    Aug 2015
    Località
    Torino, Piemonte
    Messaggi
    84
    Versione Office
    2013 PC
    Likes ricevuti
    90
    Likes dati
    5
    @scossa

    È possibile, anche se non è molto dinamico, nel senso che questa definizione è, in una certa misura, non correlato alla gamma in questione.

    Questo diventa evidente se, come spesso accade, decidiamo di definire la nostra gamma nel Gestione Nomi, forse perché è una gamma dinamica che cambia. Per esempio, se definiamo Gamma come:

    =Scadenze!B$2:B$10

    poi Arry1 diventerebbe:

    =RIF.RIGA(INDICE(Scadenze!$A:$A;1):INDICE(Scadenze!$A:$A;RIGHE(Gamma)))

    e così si aggiorna automaticamente se decidiamo di modificare la definizione di Gamma, mentre una costruzione quali:

    =RIF.RIGA(Scadenze!$1:$9)

    avrebbe bisogno di modificazione manuale per soddisfare questa nuova gamma.
    .
    Inoltre, con le costruzione usando RIF.RIGA/RIF.COLONNA dobbiamo diffidare degli effetti di inserzioni di righe/colonne nella gamma. Ad esempio, utilizzando la costruzione:

    =RIF.COLONNA(Scadenze!$A:$C)

    cosa succede se una nuova colonna viene inserito tra le colonne A e B?

    La definizione sopra diventerà:

    =RIF.COLONNA(Scadenze!$A:$D)

    cioè:

    {1,2,3,4}

    mentre:

    =RIF.COLONNA(INDICE(Scadenze!$1:$1;1):INDICE(Scadenze!$1:$1;CONTA.VALORI(Fogli)))

    continuerà a dare:

    {1,2,3}

    Saluti.
    Advanced Excel Techniques: http://excelxor.com/

  16. #16
    L'avatar di Gerardo Zuccalà
    Clicca e Apri
    Data Registrazione
    May 2015
    Località
    Milano, Italy
    Età
    49
    Messaggi
    4916
    Versione Office
    2013
    Likes ricevuti
    1117
    Likes dati
    1126
    Ciao Jos
    Non ho Excel in questo momento
    E mi scuserai se non posso fare degli esempi pratici, comunque credo che tu avendo la versione di Excel in inglese, quando traduci on line alcune formule, alcune cose non sono esattamente corrette e in particolare mi riferisco ai risultati che producono alcuni pezzi di formula.
    Esempio se con il tasto F9 evidenzi una matrice/vettore nelle versione in inglese {1,2,3,4,5} mentre in italiano è {1.2.3.4.5} not commas but periods
    Oppure se hai una matrice/vettore sulle colonne in inglese hai {1;2;3;4;5} in italiano {1\2\3\4\5} , not semi comma but back Slash
    So che questo potrebbe essere un grosso problema per spiegare le tue formule/tutorial, perché ho notato che usi molto rappresentare le Matrici /vettore che sono spiegate benissimo, e non devi preoccuparti per l italiano che eccellente
    Jos so che è impossibile correggere tutte le matrici/vettore sarebbe un lavoro gigantesco per cui ti consiglio di indicare all'inizio del tutorial.
    Ciao Jos grazie per il tuo grande contributo
    Ultima modifica fatta da:Gerardo Zuccalà; 11/08/15 alle 19:18

  17. #17

    L'avatar di scossa
    Clicca e Apri
    Data Registrazione
    Jul 2015
    Località
    Verona Provincia
    Età
    57
    Messaggi
    1022
    Versione Office
    .
    Likes ricevuti
    366
    Likes dati
    0
    Citazione Originariamente Scritto da XOR LX Visualizza Messaggio
    Per esempio, se definiamo Gamma come:

    =Scadenze!B$2:B$10

    poi Arry1 diventerebbe:

    =RIF.RIGA(INDICE(Scadenze!$A:$A;1):INDICE(Scadenze!$A:$A;RIGHE(Gamma)))

    e così si aggiorna automaticamente se decidiamo di modificare la definizione di Gamma, mentre una costruzione quali:

    =RIF.RIGA(Scadenze!$1:$9)

    avrebbe bisogno di modificazione manuale per soddisfare questa nuova gamma.
    Appunto, se devi cambiare manualmente la definizione di gamma, allora tanto vale cambiare manualmente =RIF.RIGA(Scadenze!$1:$9) - ad esempio -in =RIF.RIGA(Scadenze!$1:$12).

    Ovviamente se rendi i nomi completamente "dinamici" il mio discorso cade.
    Bye!
    scossa
    scossa's web site
    ___
    Se tu hai una mela, e io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno. Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)

  18. #18
    L'avatar di Jos
    Clicca e Apri
    Data Registrazione
    Aug 2015
    Località
    Torino, Piemonte
    Messaggi
    84
    Versione Office
    2013 PC
    Likes ricevuti
    90
    Likes dati
    5
    Citazione Originariamente Scritto da Gerardo Zuccalà Visualizza Messaggio
    Oppure se hai una matrice/vettore sulle colonne in inglese hai {1;2;3;4;5} in italiano {1\2\3\4\5}
    Ciao, Gerry.

    Puoi confermare questo? Ho pensato che fosse la virgole che viene reso come "\", non il punto e virgola.

    Per esempio, qual è la valutazione di:

    =MATR.SOMMA.PRODOTTO(RIF.COLONNA(A1:J1))

    ?

    È:

    =MATR.SOMMA.PRODOTTO({1\2\3\4\5\6\7\8\9\10})

    ?

    Saluti
    Advanced Excel Techniques: http://excelxor.com/

  19. #19
    L'avatar di Gerardo Zuccalà
    Clicca e Apri
    Data Registrazione
    May 2015
    Località
    Milano, Italy
    Età
    49
    Messaggi
    4916
    Versione Office
    2013
    Likes ricevuti
    1117
    Likes dati
    1126
    Ciao, Si esatto
    Ho fatto un po di confusione perché sono con il telefonino
    Le virgola = backslash
    Il punto e virgola = punto
    Ciao jos

  20. #20
    L'avatar di Jos
    Clicca e Apri
    Data Registrazione
    Aug 2015
    Località
    Torino, Piemonte
    Messaggi
    84
    Versione Office
    2013 PC
    Likes ricevuti
    90
    Likes dati
    5
    Citazione Originariamente Scritto da Gerardo Zuccalà Visualizza Messaggio
    Jos so che è impossibile correggere tutte le matrici/vettore sarebbe un lavoro gigantesco per cui ti consiglio di indicare all'inizio del tutorial
    Il problema è che ora non posso correggerlo!

    Ciao
    Advanced Excel Techniques: http://excelxor.com/

Discussioni Simili

  1. [Risolto] estrarre da una riga con punteggiatura la parte iniziale
    Di stefanofusco nel forum Domande su Excel in generale
    Risposte: 4
    Ultimo Messaggio: 02/03/17, 16:15
  2. Estrarre parte di testo da una cella
    Di lizz1183 nel forum Domande su Excel in generale
    Risposte: 4
    Ultimo Messaggio: 09/09/16, 14:57
  3. estrarre parte di testo da cella unita
    Di alessioje nel forum Domande su Excel in generale
    Risposte: 12
    Ultimo Messaggio: 30/08/16, 19:02
  4. Estrarre da un stringa una parte precisa
    Di morpheus1976 nel forum Domande su Excel in generale
    Risposte: 5
    Ultimo Messaggio: 01/06/16, 20:48
  5. estrarre dati da piu tabelle
    Di nick0573 nel forum Domande su Excel in generale
    Risposte: 2
    Ultimo Messaggio: 31/07/15, 07:15

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
  •