Ricerca più complicata

Hai un problema con Excel?

Nuova discussione

Ricerca più complicata
di NicoPana data: 16/10/2017 13:51:19
Buongiorno al forum.

Vi posto un file di esempio in allegato.

Nel foglio "dati", ci sono tutti i dati che mi interessa poi in maniera automatica raccogliere e riepilogare nel foglio "sommario", solo che siccome si tratta di incrociare più condizioni, non ci riesco tantissimo.

Nel foglio "dati" trovate la colonna DATA, TURNO, CODICE PAN e IDPAN

Io vorrei che nel foglio sommario, in maniera automatica, venisse esplicitato il valore IDPAN per singolo CODICE PAN, DATA e TURNO, su una tabella che come formattazione potrebbe essere anche lasciata cosi come la vedete, oppure la potete anche cambiare.

Datemi un aiuto, grazie
di Luca73 data: 17/10/2017 13:49:47
Con Formule
inserisci in D4
=SE.ERRORE(1/(1/GRANDE(--(dati!$A$2:$A$104=sommario!$A4)*(--(dati!$B$2:$B$104=sommario!$B4))*(--(dati!$C$2:$C$104=sommario!D$2))*dati!$D$2:$D$104;RESTO(RIF.RIGA()+1;4)));"")

in D5
=SE.ERRORE(1/(1/GRANDE(--(dati!$A$2:$A$104=sommario!$A4)*(--(dati!$B$2:$B$104=sommario!$B4))*(--(dati!$C$2:$C$104=sommario!D$2))*dati!$D$2:$D$104;RESTO(RIF.RIGA()+1;4)));"")

in D6
=SE.ERRORE(1/(1/GRANDE(--(dati!$A$2:$A$104=sommario!$A4)*(--(dati!$B$2:$B$104=sommario!$B4))*(--(dati!$C$2:$C$104=sommario!D$2))*dati!$D$2:$D$104;RESTO(RIF.RIGA()+1;4)));"")

Confermi Ciascuna formula come matriciale (CTRL+SHIFT +Invio) e vedrai che compaiono delle parentesi quadre.
Poi Copi nelle altre colonne
Poi Copi nelle altre tabelle

Attanzione devi sistemare il turno nel foglio sommario che sia uguale a qullo del foglio dati (manca il °)

Ciao
Luca
di NicoPana (utente non iscritto) data: 17/10/2017 15:35:00
Complimenti, non ho capito tutte le funzioni nidificate che ci sono all'interno ma funziona.

Vorrei solo chiederti un'informazione: dicendo "Confermi Ciascuna formula come matriciale (CTRL+SHIFT +Invio) e vedrai che compaiono delle parentesi quadre." Excel praticamente che cosa fa? è una mia curiosità visto che non mi è mai capitato.

Giusto per precisare, perché non so se serve, ma dopo la precedente operazione le parentesi non sono quadre ma graffe
di NicoPana (utente non iscritto) data: 17/10/2017 15:59:07
E mi spiegheresti quella parte ...RESTO(RIF.RIGA()+1;4) dove va a puntare? perché tutta la parte precedente più o meno capisco dove va a puntare, quindi vado a modificare il foglio da cui prelevare i dati, teoricamente capisco cosa andare a modificare.

Però questa parte non mi è chiara: cosa fa?
di Luca73 data: 17/10/2017 16:17:56
Ciao
Ha ragione le parentesi sono graffe non quadre...mio errore.
Un po di spiegazione
dati!$A$2:$A$104=sommario!$A4 (e similari)
confronta i valori del folgio dati nel range da A2 a A104 con il valore nel foglio sommario cella A4 (confronto di date)
usandolo in maniera matriciale (poi ti spiego) crea un vettore di VERO e FALSO
--(dati!$A$2:$A$104=sommario!$A4)
aggiungendo un - davanti converte il vettore di VERO e FALSO in numeri VERO=1 FALSO=0, il secondo - serve per non cambiare il segno
Quindo le tre parentesi creano dei vettori di 1 e di 0 a seconda se le tr condizioni (data, turno e CODICE PAN) sono uguali a quelli cercati. pertanto creo un vettore che varra 1 solo se le tre condizioni sono verificate.
Ora moltiplico il vettore per il numero che sta nel foglio dati!$D$2:$D$104 e così ottengo un vettore di zero (dove le condizioni danno un falso e di valori dove le condizioni sono tutte e tre vere.
Poiche di valori (dal tuo schema posso averne da 1 a 3) allora con la funzione grande seleziono il più grande oppure il secondo piu grande o il terzo più grande.
Per fare questo ho usato RESTO(RIF.RIGA()+1;4 per come è costruita la tua tabella In pratica prende il numero di riga ne aggiunge 1 e prende il resto dividendo per 4 e così ottengo 1, 2 o 3.
La funzione SE.ERRORE l'ho usata per non far scrivere niente se il valore fosse 0
in pratica ho fatto 1/(1/numero) ora se numero è 0 allora 1/0 vale errore e 1/errore vale errore invece se numero è diverso da 0 1/(1/numero) vale numero.
SE.ERRORE scrive il valore della funzione se non dà errore altrimenti scrive il secondo parametro che io ho messo a "" ovvero nulla

LA funzione matriciale serve per spiegare ad excel come usare i dati

Quando io scrivo una formula che contiene un intervallo Excel solitamente considera la corrispondente riga
Se tu Scrivi
in E1=SOMMA($A$11:$A$20*$B$11:$B$20;$C$11:$C$20*$D$11:$D$20)
e copi in basso fino a E10 Excel per ogni riga calcola il prodotto del corrispettivo valore in A e in B
per esempio in E5 troverai A5*B5+C5*D5
Se inveci confermi matriciale allora excel considera gli intervalli come di vettori e calcola tutte le moltiplicazioni e poi le somma ovvera lavora sul vettore e non sula cella del Vettore.

Spero di essermi spiegato.
Ciao
Luca
di Luca73 data: 17/10/2017 16:19:10
RIF.RIGA() senza oggetto restituisce il numero della riga della cella in cui è scritta.
Usa l'help che ti aiuta un sacco.
CIAO
LUCA
di NicoPana (utente non iscritto) data: 19/10/2017 07:26:56
Quando scrivi: "...Poiche di valori (dal tuo schema posso averne da 1 a 3) allora con la funzione grande seleziono il più grande oppure il secondo piu grande o il terzo più grande...." intendi che sono 3 le condizioni da verificare giusto? Cioè sulla coincidenza con data, turno e CODICE PAN?
di NicoPana (utente non iscritto) data: 19/10/2017 07:48:15
E un'altra cosa: come mai se aggiungo in testa delle righe vuote, per inserire magari una testata, i risultati delle formule cambiano? Ho controllato, e comunque con il simbolo dei $ dovrebbero agganciarsi a celle "mobili", cioè dopo l'inserimento di righe vuote, il riferimento alle celle giuste si mantiene anche nella nuova formula
di NicoPana data: 19/10/2017 11:09:39
Ti allego il file originale da cui ho estrapolato quello su cui tu hai lavorato: i dati vengono inseriti nel foglio InsDati (per adesso ci sono dati per Settembre ed Ottobre), e poi riepilogati in fogli mensili di contabilità, dove per semplicità ho inserito solo Settembre.

Nella versione del file che ti ho mandato, tu mi hai permesso di inserire nelle tabelle il valore nella colonna D, che invece in questa versione corrisponde alla M. Ho quindi trasformato tutti i riferimenti e le tue tabelle funzionano, i dati partono dal 26/9 e puoi verificare.

Il mio problema adesso è questo: vorrei aggiungere la raccolta di un ultimo dato; sempre quanto contenuto della colonna M come tipologia, che ricopio invece nella colonna X qualora venga svolta l'attività di rifacimento della pavimentazione su un codice PAN. Questo nelle tue formule dovrebbe semplicemente tramutarsi nel sostituire i riferimenti alla colonna M con quelli della colonna X. Ma vedo che non funziona, non mi restituisce nessun valore (per semplicità il pavimento viene realizzato sulla CCO3-1 in data 29/9 di 2° turno).

Mi daresti quest'ultima mano?
di Luca73 data: 20/10/2017 09:26:33
Ciao
é la colonna Y non la colonna X
Per il resto la stessa formula va benissimo.
Ciao
Luca
di NicoPana (utente non iscritto) data: 20/10/2017 10:55:46
Se vedi ti avevo segnalato la X perché di fianco al valore del 29/9 di 2° turno, nella colonna X, avevo ricopiato il valore della colonna M che è quello che poi andava a finire nel riepilogo.

Se so la Y, in questo caso non mi ricopierebbe "32"? non mi va bene, mi deve ricopiare "1", in corrispondenza del CCO3
di Luca73 data: 20/10/2017 12:11:47
Scusa non avevo capito.
Ho inoltre capito il problema.
la colonna M è piena di numeri e celle fisicamente vuote
la colonna X è piena di numeri e di celle forzate a vuote ("") ora quando vai a moltiplicare un numero per una cella vuota fisicamente vuota il risultato è 0 mentre se moltiplichi per una cella forzata a vuoto ("") allora il risultato è errore.
Siccome l'ultima parte della formula moltiplica per la colonna X allora non mi trovo un vettore di 0 e numeri ma un vettore di numeri ed errori.
Basta modificare la formula inserendo un se.errore per la moltiplicazione che forza a 0 in caso di errore.
TI do' la formula per J13
=SE.ERRORE(1/(1/GRANDE(SE.ERRORE(--(InsDati!$K$5:$K$500='Contabilità SETTEMBRE'!$A13)*(--(InsDati!$V$5:$V$500='Contabilità SETTEMBRE'!$B13))*(--(InsDati!$L$5:$L$500='Contabilità SETTEMBRE'!J$11))*InsDati!$X$5:$X$500;0);RESTO(RIF.RIGA()+8;4)));"")

Modificala tu per il resto.
Ciao
Luca
di NicoPana data: 20/10/2017 13:34:43
Grazie, funziona tutto

Vuoi Approfondire?