MacroFacili: Dati corrispondenti ad un criterio da un elenco

Negli esempi di ricerca con un valore o con 2 (o più) valori abbiamo visto come sia possibile ricercare un valore singolo in una tabella con certi criteri, il primo valore che corrisponde ai criteri per essere precisi nel caso che questi siano più di uno.

In questo esempio potete vedere come sia possibile estarre tutti i valori che corrispondono ad un criterio, non solo il primo.

Problema

Il problema che vogliamo risolvere è: data una tabella di dati organizzati in righe ed il valore di una colonna, vogliamo avere i dati di tutte le righe in cui c'è corrispondenza, non solo della prima.

Nel nostro esempio (che trovate in allegato):

la tabella dei dati è nelle colonne da B a D;
inserendo un codice della prima colonna in F3
il risultato sono le colonne C e D di tutte le righe in cui compare lo stesso valore nella prima colonna.

Soluzione con SCARTO+AGGREGA

In questo caso la formula che useremo ( da copiare in basso per il numero di righe che vogliamo avere e a destra) sarà:

{=SE.ERRORE(SCARTO(C$1;AGGREGA(15;6;RIF.RIGA($B$3:$B$17)/($B$3:$B$17=$F$4)-1;RIF.RIGA($A1));0);"")}

Che è una formula di matrice: dovrà essere confermata con CTRL+MAIUSC+INVIO

Qui a lato la rappresentazione ad albero della formula data dal nostro file di analisi, proviamo a spiegarlo anche se sarà di certo più facile vedere i commenti alle celle nel file allegato.

In questo caso si usa un piccolo "trucco": la funzione AGGREGA restituisce il1°,2°,3*... valore da un elenco, in questo caso partendo dal più piccolo (15), e saltando gli errori (6).

Il parametro di elenco è il numero di riga ricavato con la funzione RIF.RIGA dell'intervallo diviso per la condizione ($B$3:$B$17=$F$4): nel caso in cui questa non è verificata restituisce 0 determinado un errore nella divisione; il valore sarà quindi "saltato" dalla funzione AGGREGA.

Il numero di valore da restituire è dato dalla funzione RIF.RIGA($A1) che partendo dalla riga 1, ricopiando in basso la funzione, avrà valori 1,2,3...

Per ultimo, una volta trovato il numero di riga, la funzione SCARTO con riferimento sulla prima cella della colonna ci consentirà di avere il valore desiderato.

Soluzione con FILTRO (solo per la versione 365)

Un risultato molto simile si può ottenere utilizzando la funzione è FILTRO (disponibile nella versione 2021 o 365), In questo caso scriveremo la funzione nella prima cella e il risultato sarà esteso alle celle a sinistra e in basso attraverso la funzione di espansione di Excel 365 o 2021.

In questo caso la funzione da inserire, avendo il valore in F13, nella prima cella in alto a sinistra sarà:

=FILTRO($B$3:$D$17;B3:B17=F13;"")

e otterremo il risultato dell'immagine di destra.

In questo caso la funzione ci restituirà tutte le colonne della nostra tabella (a volte può essere scomodo) e le celle del risultato saranno visibili con la logica della funzione di espansione e quindi non in un'area "certa" anche solo per la formattazione.

L'esempio completo é scaricabile da questo link: Es390.xlsx

Gli esempi contenuti nel sito sono per uso personale, non é consentito l'uso professionale, commerciale o la riproduzione senza autorizzazione.

Per dubbi o domande potete scriverci a domande@macrofacili.it, sulla nostra pagina FB MacroFacili.it o sul gruppo FB Excel, macro e formule.