In questo articolo impareremo come cercare più istanze di un valore in Excel.
Valori di ricerca utilizzando l'opzione a discesa?
Qui capiamo come possiamo cercare risultati diversi usando la formula dell'array della funzione INDICE. Basta selezionare il valore dall'elenco e il risultato corrispondente sarà lì.
Formula generica
| {=INDEX(array,SMALL(IF(lookup_value=lookup_value_range,ROW(lookup_value_range)-ROW(prima cella di lookup_value_range)+1),ROW(1:1)))} |
Vettore: L'intervallo da cui si desidera recuperare i dati.
valore di ricerca: Il tuo lookup_value che vuoi filtrare.
intervallo_valore_ricerca: L'intervallo in cui si desidera filtrare lookup_value.
La prima cella nell'intervallo lookup_value: se il tuo intervallo lookup_value è $A$5:$A$100 allora è $A$5.
Importante: Tutto dovrebbe essere riferimento assoluto. valore di ricerca può essere relativo secondo il requisito.
Inseriscilo come formula di matrice. Dopo aver scritto la formula, usa il tasto CTRL+MAIUSC+INVIO per renderla una formula di matrice.
Esempio di ricerca per più risultati
Ho questi dati degli studenti nel raggio d'azione A2:E14. Nella cella G1, ho un menu a discesa dei valori della regione, ad es. Centro, 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.
Per cercare più valori in Excel, identifichiamo le nostre variabili.
Vettore: $C$2:$C$14
valore di ricerca : $ G $ 1
lookup_value_range: $A$2:$A$14
La prima cella dell'intervallo lookup_value: $A$2
Secondo i dati di cui sopra, la nostra formula per recuperare più valori in Excel sarà:
| {=IFERROR(INDICE($C$2:$C$14,PICCOLO(SE($G$1=$A$2:$A$14,RIGA($A$2:$A$14)-RIGA($A$2)+1) ,ROW(1:1))),"Nessun più valore")} |
Questa è una formula di matrice. Non usare solo Invio dopo aver digitato la formula. Usa CTRL + MAIUSC + INVIO insieme.
Ora 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 la funzione INDICE di Excel.
Quindi la sfida principale è ottenere una matrice di numeri indice di valore di ricerca. Per ottenere i numeri di indice, abbiamo usato le funzioni IF e ROW. La formula è davvero del tutto complessa, scomponiamola.
Vogliamo ottenere i valori dalla colonna studente, quindi il nostro array per la funzione INDICE è $C$2:$C$14.
Ora dobbiamo dare i numeri di riga da $A$2:$A$14 (valore di ricerca) in cui esiste il valore di G1 (per ora, diciamo G1 ha un centro).
SE($G$1=$A$2:$A$14, RIGA($A$2:$A$14): Ora, questa parte restituisce il numero di riga se il valore di una cella è G1 (centrale) nel raggio d'azione $A$2:$A$14 altrimenti ritorna FALSO. In questo esempio, tornerà
{2;FALSO;FALSO;FALSO;FALSO;7;8;FALSO;FALSO;11;FALSO;FALSO;FALSO}.
Ora, poiché l'array sopra contiene i numeri di riga dalla prima riga (1:1) e abbiamo bisogno di righe a partire dal nostro array (A2:A14). Per farlo, usiamo -ROW($A$2)+1 nella formula IF. Questo restituirà un numero di righe prima di avviare il nostro array.
Per questo esempio, è -1. Se iniziasse da A3, restituirebbe -2 e così via. Questo numero verrà sottratto da ogni numero nell'array restituito da IF. Quindi finalmente SE($G$1=$A$2:$A$14,RIGA($A$2:$A$14)-RIGA($A$2)+1) questo si tradurrà in {1;FALSO;FALSO;FALSO;FALSO;6;7;FALSO;FALSO;10;FALSO;FALSO;FALSO}.
Successivamente, questo array è circondato dalla funzione SMALL. Questa funzione restituisce l'ennesimo valore più piccolo nell'array specificato. Ora abbiamo SMALL({2;FALSO;FALSO;FALSO;FALSO;7;8;FALSO;FALSO;11;FALSO;FALSO;FALSO},RIGA(1:1)). ROW(1:1) restituirà 1. Quindi, la funzione sopra restituirà il primo valore più piccolo nell'array, che è 2.
Quando copi questa formula nelle celle sottostanti ROW(1:1) diventerà ROW(2:2) e restituirà il secondo valore più piccolo nell'array, che è 7 e così via. Ciò consente alla funzione di restituire prima il primo valore trovato. Ma se vuoi ottenere prima l'ultimo valore trovato, usa la funzione LARGE invece della funzione SMALL.
Ora, utilizzando i valori restituiti sopra le funzioni, la funzione INDICE restituisce facilmente ogni valore corrispondente da un intervallo.
Puoi anche eseguire la ricerca di corrispondenze esatte utilizzando la funzione INDICE e CONFRONTA in Excel. Ulteriori informazioni su Come eseguire la ricerca con distinzione tra maiuscole e minuscole utilizzando la funzione INDICE E CONFRONTA in Excel. Puoi anche cercare le corrispondenze parziali utilizzando i caratteri jolly in Excel.
Spero che questo articolo su Come cercare più istanze di un valore in Excel sia esplicativo. Trova altri articoli sul calcolo dei valori e le relative formule di Excel qui. Se ti è piaciuto il nostro blog, condividilo con i tuoi amici su Facebook. E puoi anche seguirci su Twitter e Facebook. Ci piacerebbe avere tue notizie, facci sapere come possiamo migliorare, completare o innovare il nostro lavoro e renderlo migliore per te. Scrivici al sito di posta elettronica.
Usa CERCA.VERT da due o più tabelle di ricerca | Per cercare da più tabelle possiamo adottare un approccio IFERROR. Per cercare da più tabelle, prende l'errore come un'opzione per la tabella successiva. Un altro metodo può essere un approccio If.
Come eseguire la ricerca con distinzione tra maiuscole e minuscole in Excel | la funzione VLOOKUP di excel non fa distinzione tra maiuscole e minuscole e restituirà il primo valore corrispondente dall'elenco. INDEX-MATCH non fa eccezione, ma può essere modificato per fare distinzione tra maiuscole e minuscole. Vediamo come…
Cerca il testo che appare frequentemente con criteri in Excel | La ricerca appare più frequentemente nel testo in un intervallo che usiamo la funzione INDICE-MATCH con MODALITÀ. Ecco il metodo.
Articoli popolari:
Come usare la funzione SE in Excel : l'istruzione IF in Excel controlla la condizione e restituisce un valore specifico se la condizione è VERA o restituisce un altro valore specifico se FALSO.
Come utilizzare la funzione CERCA.VERT in Excel : questa è una delle funzioni più utilizzate e popolari di excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
Come utilizzare la funzione SOMMA.SE in Excel : Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.
Come utilizzare la funzione CONTA.SE in Excel : Conta i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.