Formula di Excel: estrai tutte le corrispondenze con la colonna helper -

Sommario

Formula generica

=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")

Sommario

Un modo per estrarre più corrispondenze in Excel consiste nell'usare INDICE e CONFRONTA con una colonna helper che contrassegna i dati corrispondenti. Ciò evita la complessità di una formula di matrice più avanzata. Nell'esempio mostrato, la formula in H6 è:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

dove ct (G3), data (B3: E52) e helper (E3: E52) sono intervalli denominati.

Spiegazione

La sfida con le formule di ricerca che recuperano più di una corrispondenza è la gestione dei duplicati (ovvero più corrispondenze). Le formule di ricerca come CERCA.VERT e INDICE + CONFRONTA possono facilmente trovare la prima corrispondenza, ma è molto più difficile cercare "tutte le corrispondenze" quando i criteri trovano più di una corrispondenza.

Questa formula si occupa di questa sfida utilizzando una colonna helper che restituisce un valore numerico che può essere utilizzato per estrarre facilmente più corrispondenze. La formula nella colonna helper ha questo aspetto:

=SUM(E2,AND(C3=$I$3,D3=$J$3))

La colonna helper verifica ogni riga dei dati per vedere se il dipartimento nella colonna C corrisponde al valore in I3 e l'edificio nella colonna D corrisponde al valore in J3. Entrambi i test logici devono restituire TRUE affinché AND restituisca TRUE.

Per ogni riga, il risultato della funzione AND viene aggiunto al "valore sopra" nella colonna helper per generare un conteggio. L'effetto pratico di questa formula è un contatore incrementale che cambia solo quando viene trovata una (nuova) corrispondenza. Quindi il valore rimane lo stesso fino a quando non viene trovata la corrispondenza successiva. Questo funziona perché i risultati VERO / FALSO restituiti da AND vengono forzati a valori 1/0 come parte dell'operazione di somma. I risultati FALSE non aggiungono nulla e i risultati TRUE aggiungono 1.

Tornando all'area di estrazione, la formula di ricerca per Nome nella colonna H ha questo aspetto:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

Lavorando dall'interno verso l'esterno, la parte INDICE + CORRISPONDENZA della formula cerca il nome per la prima corrispondenza trovata, utilizzando il numero di riga nella colonna G come valore di corrispondenza:

INDEX(data,MATCH($G6,helper,0),1)

INDICE riceve tutte e 3 le colonne di dati come array (denominato intervallo "dati") e CONFRONTA è configurato per abbinare il numero di riga all'interno della colonna helper (l'intervallo denominato "helper") in modalità corrispondenza esatta (terzo argomento impostato su zero) .

È qui che diventa evidente l'intelligenza della formula. La colonna helper ovviamente contiene duplicati, ma non importa, perché MATCH corrisponderà solo al primo valore. Per impostazione predefinita, ogni "primo valore" corrisponde alla riga corretta nella tabella dei dati.

Le formule nelle colonne I e J sono le stesse di H, ad eccezione del numero di colonna, che viene aumentato di uno in ogni caso.

L'istruzione IF che racchiude la formula INDICE / CONFRONTA esegue una semplice funzione: controlla ogni numero di riga nell'area di estrazione per vedere se il numero di riga è inferiore o uguale al valore in G3 (intervallo denominato "ct"), che è il conteggio totale di tutti i record corrispondenti. In tal caso, viene eseguita la logica INDICE / CONFRONTA. In caso contrario, IF restituisce una stringa vuota ("").

La formula in G3 (intervallo denominato "ct") è semplice:

=MAX(helper)

Poiché il valore massimo nella colonna helper è uguale al conteggio totale delle corrispondenze, la funzione MAX è tutto ciò di cui abbiamo bisogno.

Nota: l'area di estrazione deve essere configurata manualmente per gestire tutti i dati necessari (ad esempio 5 righe, 10 righe, 20 righe, ecc.). In questo esempio, è limitato a 5 righe solo per mantenere compatto il foglio di lavoro.

Ho imparato questa tecnica nel libro di Mike Girvin Control + Shift + Enter.