Esamina questa figura:
Supponi di voler produrre un report da questo come se avessi filtrato la regione. Cioè, se filtri su Nord, vedresti:
Ma cosa succederebbe se volessi una versione basata su formule della stessa cosa?
Ecco il risultato che stai cercando nelle colonne I: K:
Chiaramente, è lo stesso rapporto, ma qui non ci sono elementi filtrati. Se volessi un nuovo rapporto su Est, sarebbe bello cambiare semplicemente il valore in G1 in Est:
Ecco come si fa. Prima di tutto, non viene eseguito utilizzando CERCA.VERT. Quindi ho mentito sul titolo di questa tecnica!
La colonna F non è stata mostrata prima e può essere nascosta (o spostata altrove in modo che non interferisca con il report).
Ciò che viene mostrato nella colonna F sono i numeri di riga in cui si trova G1 nella colonna A; ovvero, quali righe contengono il valore "Nord"? Questa tecnica consiste nell'utilizzare la cella sopra, quindi deve iniziare in almeno fila 2. corrisponda al valore “nord” contro colonna A, ma invece di tutta la colonna, utilizzare una funzione OFFSET: OFFSET($A$1,F1,0,1000,1).
Poiché F1 è 0, questo OFFSET(A1,0,0,1000,1)è A1: A1000. (Il 1000 è arbitrario, ma abbastanza grande da svolgere il lavoro: puoi impostarlo con qualsiasi altro numero).
Il valore 2 in F2 è dove si trova il primo "Nord". Vuoi anche aggiungere di nuovo il valore di F1 alla fine, ma questo è zero, finora.
La "magia" prende vita nella cella F3. Sai già che il primo Nord si trova nella riga 2. Quindi, vuoi iniziare a cercare due righe sotto A1. Puoi farlo specificando 2 come secondo argomento della funzione OFFSET.
La formula nella F3 punterà automaticamente al 2 che è stato calcolato in cella F2: Quando si copia il basso formula, si vedrà =OFFSET($A$1,F2,0,1000,1)che è OFFSET($A$1,2,0,1000,1)che è A3: A1000. Quindi stai confrontando il Nord con questo nuovo intervallo e trova il Nord nella terza cella di questo nuovo intervallo, quindi la CONFRONTA dà 3.
Sommando di nuovo il valore dalla cella sopra, F2, vedrai il 3 più il 2, o 5, che è la riga che contiene il secondo Nord.
Questa formula viene compilata abbastanza da ottenere tutti i valori.
Questo ti darà i numeri di riga in cui si trovano tutti i record del Nord.
Come traduci quei numeri di riga nei risultati nelle colonne da I a K? È tutto fatto con un'unica formula. Inserire questa formula in I2: =IFERROR(INDEX(A:A,$F2),””). Copia a destra e poi copia in basso.
Perché usare IFERROR? Dov'è l'errore? Nota la cella F6: contiene # N / A (motivo per cui dovresti nascondere la colonna F) perché non ci sono più nord dopo la riga 15. Quindi se la colonna F è un errore, restituisci uno spazio vuoto. Altrimenti prendi il valore dalla colonna A (e se riempito a destra, B e C).
$ F2 è un riferimento assoluto alla colonna F, quindi il diritto di riempimento si riferisce ancora alla colonna F.
Questo articolo ospite è tratto da Bob Umlas, MVP di Excel. È una delle sue tecniche preferite dal suo libro, Excel Outside the Box.
Excel fuori dagli schemi »
