Se sei qui, significa che stai tentando di utilizzare CERCA.VERT per recuperare più valori da un set di dati. Ma cerchiamo di capire, CERCA.VERT NON PU RESTITUIRE VALORI MULTIPLI. Ma questo non significa che non possiamo farlo. Possiamo fare una ricerca che recuperi più valori invece del primo solo.

286

Formula generica

{=INDEX(array,SMALL(IF(lookup_value=lookup_value_range,ROW(lookup_value_range)-ROW(first cell of lookup_value_range)+1),ROW(1:1)))}

Array: l’intervallo da cui vuoi recuperare i dati.

lookup_value: il tuo lookup_value che desideri filtrare.

lookup_value_range: l’intervallo in cui si desidera filtrare lookup_value.

La prima cella nell’intervallo lookup_value: se l’intervallo lookup_value è $ A $ 5: $ A $ 100, allora sarà $ A $ 5.

Importante: tutto dovrebbe essere link: / nome-intervallo-excel-riferimento-assoluto-in-excel [riferimento assoluto]. lookup_value può essere relativo in base al requisito.

Inseriscilo come formula di matrice. Dopo aver scritto la formula, premi il tasto CTRL + MAIUSC + INVIO per renderla una formula di matrice.

Esempio di ricerca di più risultati Ho questi dati degli studenti nell’intervallo A2: E14. Nella cella G1, ho un elenco a discesa dei valori della regione, ad es. Centrale, Est, Nord, Sud e Ovest.

Ora voglio, qualunque sia la regione che ho in G1, un elenco di tutti gli studenti di quella regione dovrebbe essere visualizzato nella colonna H.

287

Per cercare più valori in Excel, identifichiamo le nostre variabili.

Array: $ C $ 2: $ C $ 14 lookup_value: $ G $ 1 lookup_value_range: $ A $ 2: $ A $ 14 La prima cella dell’intervallo lookup_value: $ A $ 2 In base ai dati precedenti, la nostra formula per recuperare più valori in Excel sarà:

{=INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(1:1)))}

Copia questa formula in H2 e premi CTRL + MAIUSC + INVIO. Ora trascina verso il basso questa formula fino a ottenere un errore #NUM. #NUM indica che non ci sono altri valori corrispondenti rimanenti a quel lookup_value ed è la fine dell’elenco.

288

Se #NUM ti infastidisce, puoi avere link: / logical-formas-excel-iferror-function [IFERROR function] davanti alla formula e visualizzare alcune informazioni significative invece dell’errore.

{=IFERROR(INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(1:1))),"--List Ends--")}

Questo mostrerà –List Ends– quando vengono mostrati tutti gli elementi.

Adesso capiamo COME FUNZIONA.

Anche se la formula può sembrare complessa, l’idea è semplice. Dobbiamo ottenere il numero di indice di ogni occorrenza di valore, quindi recuperare i valori utilizzando`link: / lookup-formas-excel-index-function [INDEX function] `di Excel.

Quindi la sfida principale è ottenere un array di numeri di indice di lookup_value. Per ottenere i numeri di indice, abbiamo usato link: / tips-if-condition-in-excel [IF] ʻand`link: / lookup-and-reference-excel-row-function [ROW]

funzioni. La formula è davvero complessa complessivamente, analizziamola.

Vogliamo ottenere valori dalla colonna studente, quindi il nostro array per`link: / lookup-formas-excel-index-function [INDEX function] `è $ C $ 2: $ C $ 14. Ora dobbiamo fornire i numeri di riga da $ A $ 2: $ A $ 14 (valore di ricerca) in cui esiste il valore di G1 (per ora, supponiamo che G1 abbia al centro).

IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14): ora, questa parte restituisce il numero di riga se il valore di G1 (centrale) di una cella è compreso nell’intervallo $ A $ 2: $ A $ 14 altrimenti restituisce FALSE. In questo esempio, restituirà

\ {2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE}.

  • Ora, poiché l’array sopra contiene il numero di riga dalla prima riga (1: 1) e abbiamo bisogno di una riga che inizia dal nostro array (A2: A14). Per farlo, usiamo -ROW ($ A $ 2) +1 nella formula IF. Questo restituirà un numero di righe prima di iniziare il nostro array.

Per questo esempio, è -1. Se partisse da A3, restituirebbe -2 e così via. numero verrà sottratto da ogni numero nell’array restituito da IF. Quindi, infine, IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) questo si tradurrà in \ {1; FALSE; FALSE; FALSE; FALSE; 6; 7; FALSE; FALSE; 10; FALSE; FALSE; FALSE}.

  • Successivamente, questo array è circondato da`link: / statistico-formule-excel-small-function [SMALL] funzione `

    1. Questa funzione restituisce l’ennesimo piccolo valore est nell’array dato. Ora, abbiamo link: / statistics-formas-excel-small-function [SMALL] (\ {2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE} , ROW (1: 1)).

ROW (1: 1) restituirà 1. Quindi, la funzione sopra restituirà il primo valore più piccolo nell’array, che è 2.

Quando copierai questa formula nelle celle sottostanti, RIGA (1: 1) diventerà RIGA (2: 2) e restituirà il 2 ° valore più piccolo nella matrice, che è 7 e così via. Ciò consente alla funzione di restituire prima il primo valore trovato. Ma se si desidera prima ottenere l’ultimo valore trovato, utilizzare la funzione LARGE anziché la funzione SMALL.

Ora utilizzando i valori restituiti sopra le funzioni, la funzione INDICE restituisce facilmente ogni valore corrispondente da un intervallo.

Quindi, sì ragazzi, potete cercare più valori corrispondenti per un valore di ricerca. Nella gif sopra, ho usato la funzione SE.ERRORE per rilevare errori e la formattazione condizionale per renderlo un po ‘visivo. Nell’esempio sopra, ho usato molte altre funzioni e tecniche che ho spiegato in altri articoli.

Scarica il file: