#Excel – Come trasporre corrispondenze multiple in colonne diverse

Per trasporre più corrispondenze in colonne separate, è possibile utilizzare una formula matriciale basata sulle funzioni INDICE e PICCOLO.

Trasporre corrispondenze multiple in colonne diverse

Nell’esempio mostrato, la formula in F3 è:

{=SE.ERRORE(INDICE(nomi;PICCOLO(SE(gruppi=$E3;RIF.RIGA(nomi)-MIN(RIF.RIGA(nomi))+1);COLONNE($E$3:E3)));"")}

dove nomi è l’intervallo denominato per C3:C9 e gruppi è l’intervallo denominato per B3:B9.

Si tratta di una formula matriciale e deve essere inserita con Ctrl + Maiusc + Invio.

Dopo aver immesso la formula nella prima cella, trascinatela verso il basso e poi a destra fino ad occupare tutte le celle da riempire con i nomi.

Come funziona questa formula

Il punto focale di questa formula è questo: stiamo usando la funzione PICCOLO per ottenere il numero di riga che corrisponde alla “n-esima corrispondenza”. Una volta che abbiamo il numero di riga, lo passiamo semplicemente alla funzione INDICE, che restituisce il valore presente in quella riga.

Il trucco è che PICCOLO sta lavorando con una matrice costruita dinamicamente da SE in questa porzione di formula:

SE(gruppi=$E3;RIF.RIGA(nomi)-MIN(RIF.RIGA(nomi))+1)

Questo codice testa un valore per vedere se esiste nell’intervallo “nomi”. Se è così, restituisce un numero di riga “normalizzato” da una serie di numeri di riga create da questo codice:

RIF.RIGA(nomi)-MIN(RIF.RIGA(nomi))

Il risultato finale è una matrice che contiene dei numeri dove c’è una corrispondenza, e FALSO dove non c’è. Una matrice simile a questa:

{FALSO.FALSO.FALSO.FALSO.5.FALSO.7}

Questa è la matrice che viene fornita a PICCOLO. Il valore di k (che è il secondo argomento della funzione PICCOLO; l’n-esimo valore) deriva da questo:

COLONNE($E$3:E3)

Si noti che questo è un intervallo “aperto”, che cambierà quando copierete la formula nelle celle della tabella dei risultati. Questo è ciò che fa in modo che k (n-esimo) venga incrementato.

LEGGI:  #Excel - Come usare INDICE / CONFRONTA: un CERCA.VERT potenziato

Un problema con questa formula è che quando COLONNE dovesse restituire un valore inesistente per k, si genererebbe un errore #NUM. Per questo usiamo SE.ERRORE, in modo da “catturare” l’errore e restituire “” (blank) se del caso.

 

Copyright © 2014-2017  Office Academy. Tutti i diritti riservati.
Vai alla barra degli strumenti