Cerca e recupera l'intera riga
Per cercare e recuperare un'intera riga di dati abbinando un valore specifico, puoi utilizzare le funzioni INDICE e CONFRONTA per creare una formula matriciale.
Cerca e recupera un'intera riga in base a un valore specifico
Somma un'intera riga in base a un valore specifico
Ulteriore analisi di un'intera riga in base a un valore specifico
Cerca e recupera un'intera riga in base a un valore specifico
Per ottenere l'elenco delle vendite effettuate da Jimmy secondo la tabella sopra, puoi prima utilizzare la funzione CONFRONTA per restituire la posizione delle vendite effettuate da Jimmy, che verrà poi passata alla funzione INDICE per recuperare i valori in quella posizione.
Sintassi generica
=INDICE(intervallo_restituito;CONFRONTA(valore_ricerca;intervallo_confronto;0);0)
√ Nota: Questa è una formula matriciale che richiede di essere inserita con Ctrl + Maiusc + Invio.
- intervallo_restituito: L'intervallo che contiene l'intera riga che desideri restituire. Qui si riferisce all'intervallo delle vendite.
- valore_ricerca: Il valore che la formula combinata utilizza per trovare le informazioni sulle vendite corrispondenti. Qui si riferisce al venditore dato.
- intervallo_confronto: L'intervallo di celle dove confrontare il valore_ricerca. Qui si riferisce all'intervallo dei nomi.
- tipo_corrispondenza 0: Forza CONFRONTA a trovare il primo valore che corrisponde esattamente al valore_ricerca.
Per ottenere l'elenco delle vendite effettuate da Jimmy, copia o inserisci la formula sottostante nella cella I6, premi Ctrl + Maiusc + Invio, quindi fai doppio clic sulla cella e premi F9 per ottenere il risultato:
=INDICE(C5:F11;CONFRONTA("Jimmy";B5:B11;0);0)
Oppure, usa un riferimento di cella per rendere la formula dinamica:
=INDICE(C5:F11;CONFRONTA(I5;B5:B11;0);0)
Spiegazione della formula
=INDICE(C5:F11;CONFRONTA(I5;B5:B11;0);0)
- CONFRONTA(I5;B5:B11;0): Il tipo_corrispondenza 0 forza la funzione CONFRONTA a restituire la posizione di Jimmy, il valore in I5, nell'intervallo B5:B11, che è 7 poiché Jimmy è nella 7ª posizione.
- INDICE(C5:F11C5:F11;CONFRONTA(I5;B5:B11;0);0) = INDICE(C5:F11C5:F11;7;0): La funzione INDICE restituisce tutti i valori nella settima riga dell'intervallo C5:F11 in una matrice come questa: {6678;3654;3278;8398}. Nota che per rendere visibile la matrice in Excel, dovresti fare doppio clic sulla cella in cui hai inserito la formula, e quindi premere F9.
Somma un'intera riga in base a un valore specifico
Ora che abbiamo tutte le informazioni sulle vendite effettuate da Jimmy, per ottenere il volume annuale delle vendite effettuate da Jimmy, possiamo semplicemente aggiungere la funzione SOMMA alla formula per sommare tutti i valori delle vendite dall'elenco.
Sintassi generica
=SOMMA(INDICE(intervallo_restituito;CONFRONTA(valore_ricerca;intervallo_confronto;0);0))
In questo esempio, per ottenere il volume annuale delle vendite effettuate da Jimmy, copia o inserisci la formula sottostante nella cella I7, e premi Invio per ottenere il risultato:
=SOMMA(INDICE(C5:F11;CONFRONTA(I5;B5:B11;0);0))
Spiegazione della formula
=SOMMA(INDICE(C5:F11;CONFRONTA(I5;B5:B11;0);0))
- CONFRONTA(I5;B5:B11;0): Il tipo_corrispondenza 0 forza la funzione CONFRONTA a restituire la posizione di Jimmy, il valore in I5, nell'intervallo B5:B11, che è 7 poiché Jimmy è nella 7ª posizione.
- INDICE(C5:F11;CONFRONTA(I5;B5:B11;0);0) = INDICE(C5:F11;7;0): La funzione INDICE restituisce tutti i valori nella settima riga dell'intervallo C5:F11 in una matrice come questa: {6678;3654;3278;8398}. Nota che per rendere visibile la matrice in Excel, dovresti fare doppio clic sulla cella in cui hai inserito la formula, e quindi premere F9.
- SOMMA(INDICE(C5:F11;CONFRONTA(I5;B5:B11;0));0) = SOMMA({6678;3654;3278;8398}): La funzione SOMMA somma tutti i valori nella matrice, e quindi ottiene il volume annuale delle vendite effettuate da Jimmy, $22.008.
Ulteriore analisi di un'intera riga in base a un valore specifico
Per ulteriori elaborazioni sulle vendite effettuate da Jimmy, puoi semplicemente aggiungere altre funzioni come SOMMA, MEDIA, MAX, MIN, GRANDE, ecc. alla formula.
Ad esempio, per ottenere la media delle vendite trimestrali di Jimmy, puoi usare la formula:
=MEDIA(INDICE(C5:F11;CONFRONTA(I5;B5:B11;0);0))
Per scoprire le vendite più basse effettuate da Jimmy, usa una delle seguenti formule:
=MIN(INDICE(C5:F11;CONFRONTA(I5;B5:B11;0);0))
OPPURE
=PICCOLO(INDICE(C5:F11;CONFRONTA(I5;B5:B11;0);0);1)
Funzioni correlate
La funzione INDICE di Excel restituisce il valore visualizzato in base a una data posizione da un intervallo o una matrice.
La funzione CONFRONTA di Excel cerca un valore specifico in un intervallo di celle e restituisce la posizione relativa del valore.
Formule correlate
Cerca e recupera l'intera colonna
Per cercare e recuperare un'intera colonna abbinando un valore specifico, una formula INDICE e CONFRONTA ti sarà utile.
Corrispondenza esatta con INDICE e CONFRONTA
Se hai bisogno di scoprire le informazioni elencate in Excel su un prodotto, film o persona specifica, ecc., dovresti fare buon uso della combinazione delle funzioni INDICE e CONFRONTA.
Corrispondenza approssimativa con INDICE e CONFRONTA
Ci sono momenti in cui dobbiamo trovare corrispondenze approssimative in Excel per valutare le prestazioni dei dipendenti, classificare i punteggi degli studenti, calcolare il costo di spedizione in base al peso, ecc. In questo tutorial, parleremo di come utilizzare le funzioni INDICE e CONFRONTA per recuperare i risultati di cui abbiamo bisogno.
Ricerca sensibile alle maiuscole/minuscole
Potresti sapere che puoi combinare le funzioni INDICE e CONFRONTA, o utilizzare la funzione CERCA.VERT per cercare valori in Excel. Tuttavia, le ricerche non sono sensibili alle maiuscole/minuscole. Quindi, per eseguire una corrispondenza sensibile alle maiuscole/minuscole, dovresti sfruttare le funzioni IDENTICO e SCEGLI.
I migliori strumenti per la produttività di Office
Kutools per Excel - Ti aiuta a distinguerti dalla massa
Kutools per Excel vanta oltre 300 funzionalità, garantendo che ciò di cui hai bisogno sia a portata di clic...
Office Tab - Abilita la lettura e la modifica a schede in Microsoft Office (include Excel)
- Un secondo per passare tra decine di documenti aperti!
- Riduci centinaia di clic del mouse ogni giorno, dì addio al dolore al polso.
- Aumenta la tua produttività del 50% durante la visualizzazione e la modifica di più documenti.
- Porta le schede efficienti in Office (include Excel), proprio come Chrome, Edge e Firefox.