Recupera il primo valore corrispondente nella cella rispetto a un elenco
Supponiamo di avere un elenco di parole chiave, per recuperare la prima parola chiave apparsa in una cella specifica, mentre la cella contiene diversi altri valori, dovrai utilizzare un INDICE e PARTITA formula con l'aiuto di AGGREGATO e RICERCA funzioni.
Come recuperare il primo valore corrispondente nella cella rispetto a un elenco?
Prendere il prima parola chiave con corrispondenza in una cella rispetto all'elenco Parole chiave come la tabella mostrata sopra, significa che dobbiamo eseguire una corrispondenza contenente piuttosto che una corrispondenza esatta. Per farlo è possibile utilizzare la funzione RICERCA per alimentare le posizioni delle parole chiave apparse nella cella in numeri numerici nella funzione AGGREGATA. Quindi AGGREGATO otterrà il numero più piccolo impostando il suo numero_funzione a 15 e l'argomento ref2 a 1. Quindi utilizzare MATCH per individuare il primo valore più piccolo e inserire il numero della posizione in INDEX per recuperare il valore in quella posizione.
Sintassi generica
=INDEX(keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))
√ Nota: questa è una formula di matrice che richiede di inserire con Ctrl + spostamento + Entra.
- keyword_rng: L'intervallo di celle con parole chiave.
- cella_ricerca: La cella da cercare se contiene le parole chiave.
Per recuperare il la prima parola chiave con corrispondenza è apparsa nella cella B5 contro la colonna Parole chiave, per favore copia o inserisci la formula sotto nella cella C5 e premi Ctrl + spostamento + Entra per ottenere il risultato:
= INDICE ($ E $ 5: $ E $ 7,CONFRONTA(AGGREGATO(15,6,RICERCA($ E $ 5: $ E $ 7,B5),1),CERCA($ E $ 5: $ E $ 7,B5),0))
√ Nota: i simboli del dollaro ($) sopra indicano i riferimenti assoluti, il che significa che keyword_rng nella formula non cambierà quando sposti o copi la formula in altre celle. Tuttavia, non ci sono segni di dollaro aggiunti al ricerca_cella dal momento che vuoi che sia dinamico. Dopo aver inserito la formula, trascina verso il basso il quadratino di riempimento per applicare la formula alle celle sottostanti.
Spiegazione della formula
=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))
- RICERCA($E$5:$E$7,B5): La funzione SEARCH restituisce la posizione di ogni parola chiave dall'intervallo $ E $ 5: $ E $ 7 quando si trova in numeri numerici, e il #VALORE! errore se non trovato. Il risultato sarebbe in un array come questo: {15;11;#VALORE!}.
- AGGREGATO(15,6,RICERCA($E$5:$E$7,B5), 1) = AGGREGATO(15,6,{15;11;#VALORE!}, 1): La funzione AGGREGATO con a numero_funzione di 15 e opzione di 6 restituirà il valore più piccolo nell'array in base a ref2 argomento 1, ignorando i valori di errore. Quindi, lo snippet sarebbe tornato 11.
- INCONTRO(AGGREGATO(15,6,RICERCA($E$5:$E$7,B5), 1),RICERCA($E$5:$E$7,B5), 0) = INCONTRO(11,{15;11;#VALORE!}, 0): Il termoprotettore match_type 0 forza la funzione CONFRONTA a eseguire una corrispondenza esatta e a restituire la posizione di 11 nell'array {15;11;#VALORE!}. Quindi, la funzione tornerà 2.
- INDICE($E$5:$E$7,INCONTRO(AGGREGATO(15,6,RICERCA($E$5:$E$7,B5), 1),RICERCA($E$5:$E$7,B5), 0)) = INDICE($E$5:$E$7,2): La funzione INDICE quindi restituisce il 2nd valore nell'intervallo $ E $ 5: $ E $ 7, Che ha bbb.
osservazione
- Se non ci sono parole chiave in una cella, a #NUM! errore verrà restituito.
- La formula non fa distinzione tra maiuscole e minuscole. Per eseguire una corrispondenza con distinzione tra maiuscole e minuscole, puoi semplicemente sostituire la funzione CERCA con TROVA.
Funzioni correlate
La funzione INDICE di Excel restituisce il valore visualizzato in base a una determinata posizione da un intervallo o da una matrice.
La funzione CONFRONTA di Excel cerca un valore specifico in un intervallo di celle e restituisce la posizione relativa del valore.
In Excel, la funzione RICERCA può aiutarti a trovare la posizione di un carattere o sottostringa specifico dalla stringa di testo data come mostrato nella seguente schermata. In questo tutorial, introdurrò come utilizzare questa funzione di RICERCA in Excel.
La funzione AGGREGATO di Excel restituisce un aggregato di calcoli come SUM, COUNT, SMALLL e così via con l'opzione di ignorare gli errori e le righe nascoste.
Formule correlate
Recupera il primo valore di elenco da una cella
Per recuperare la prima parola chiave elencata in un intervallo da una cella specifica, mentre la cella contiene uno dei vari valori, dovrai utilizzare una formula di matrice piuttosto complessa con le funzioni INDICE, CONFRONTA, VAL.NUMERO e RICERCA.
Corrispondenza esatta con INDEX e MATCH
Se hai bisogno di trovare le informazioni elencate in Excel su uno specifico prodotto, film o persona, ecc., dovresti fare un buon uso della combinazione delle funzioni INDICE e CONFRONTA.
Controlla se una cella contiene un testo specifico
Qui il tutorial fornisce alcune formule per verificare se una cella contiene un testo specifico e restituisce VERO e FALSO come mostrato nell'immagine sottostante, e spiega gli argomenti e come funzionano le formule.
Controlla se la cella contiene molte cose
Supponendo che in Excel, ci sia un elenco di valori nella colonna E, si desidera verificare se le celle nella colonna B contengono tutti i valori nella colonna E e restituire VERO o FALSO come mostrato nell'immagine sottostante. Qui in questo tutorial, fornisce una formula per risolvere questo compito.
Controlla se la cella contiene una delle tante cose
Questo tutorial fornisce una formula per verificare se una cella contiene uno dei diversi valori in Excel e spiega gli argomenti nella formula e come funziona la formula.
I migliori strumenti per la produttività in ufficio
Kutools per Excel: ti aiuta a distinguerti dalla folla
Vorresti completare il tuo lavoro quotidiano in modo rapido e perfetto? Kutools per Excel offre 300 potenti funzionalità avanzate (Combina cartelle di lavoro, somma per colore, contenuto di celle divise, converti data e così via ...) e risparmia l'80% di tempo per te.
- Progettato per 1500 scenari di lavoro, ti aiuta a risolvere l'80% dei problemi di Excel.
- Riduci migliaia di clic della tastiera e del mouse ogni giorno, allevia i tuoi occhi e le tue mani stanche.
- Diventa un esperto di Excel in 3 minuti. Non è più necessario ricordare formule dolorose e codici VBA.
- Prova gratuita illimitata di 30 giorni. Garanzia di rimborso di 60 giorni. Aggiornamento gratuito e supporto per 2 anni.
Scheda Office: abilita la lettura e la modifica a schede in Microsoft Office (incluso Excel)
- Un secondo per passare da una dozzina di documenti aperti all'altra!
- Riduci ogni giorno centinaia di clic del mouse, dì addio alla mano del mouse.
- Aumenta la produttività del 50% durante la visualizzazione e la modifica di più documenti.
- Porta schede efficienti in Office (incluso Excel), proprio come Chrome, Firefox e il nuovo Internet Explorer.
