In questo articolo impareremo come cercare corrispondenze esatte utilizzando la funzione SUMPRODUCT in Excel.
Scenario:
In parole semplici, mentre si lavora con tabelle di dati, a volte è necessario cercare valori con lettere maiuscole e minuscole in Excel. Le funzioni di ricerca come le funzioni CERCA.VERT o CONFRONTA non trovano la corrispondenza esatta poiché non sono funzioni con distinzione tra maiuscole e minuscole. Supponiamo di lavorare su dati in cui 2 nomi sono differenziati in base alla distinzione tra maiuscole e minuscole, ad esempio Jerry e JERRY sono due nomi diversi. Puoi eseguire attività come operazioni su più intervalli utilizzando la formula spiegata di seguito.
Come risolvere il problema?
Per questo problema, ci verrà richiesto di utilizzare la funzione SUMPRODUCT e la funzione EXACT. Ora faremo una formula fuori dalla funzione. Qui ci viene data una tabella e dobbiamo trovare i valori corrispondenti alla corrispondenza esatta nella tabella in Excel. La funzione SUMPRODUCT restituisce la SOMMA dei corrispondenti valori VERO (come 1) e ignora i valori corrispondenti ai valori FALSO (come 0) nell'array restituito
Formula generica:
= SUMPRODUCT ( -- ( ESATTO ( valore_ricerca , matrice_ricerca ) ) , ( matrice_ritorno ) ) |
lookup_value: valore da cercare.
lookup_array: array di ricerca per il valore di ricerca.
array_ritorno: array, valore restituito corrispondente all'array di ricerca.
-- : Negazione (--) char cambia i valori, TRUEs o 1s in FALSEs o 0s e FALSEs o 0s in TRUEs o 1s.
Esempio:
Tutti questi potrebbero essere fonte di confusione da capire. Quindi, testiamo questa formula eseguendola nell'esempio mostrato di seguito. Qui abbiamo dati con quantità e prezzo dei prodotti ricevuti nelle date. Se un prodotto viene acquistato due volte ha 2 nomi. Qui dobbiamo trovare il conteggio degli elementi per tutti gli elementi essenziali. Quindi per questo abbiamo assegnato 2 elenchi come elenco ricevuto e elementi essenziali richiesti in una colonna per la formula. Ora useremo la formula seguente per ottenere la quantità del "latte" dalla tabella.
Usa la formula:
= SOMMAPRODOTTO ( -- ( ESATTO ( F5 , B3:B11 ) ) , ( C3:C11 ) ) |
F6: cerca il valore nella cella F6
B3: B11: l'array di ricerca è Items
C3: C11: l'array restituito è Quantità
-- : Negazione (--) char cambia i valori, TRUEs o 1s in FALSEs o 0s e FALSEs o 0s in TRUEs o 1s.
Qui l'intervallo è dato come riferimento di cella. Premi Invio per ottenere la quantità.
Come puoi vedere, 58 è la quantità corrispondente al valore "latte" nella cella B5, non il "Latte" nella cella B9. Dovrai cercare il valore "Latte" se hai bisogno della quantità "54" nella cella C9.
Puoi cercare il prezzo corrispondente al valore "latte" semplicemente utilizzando la formula mostrata di seguito.
Usa la formula:
= SOMMAPRODOTTO ( -- ( ESATTO ( F5 , B3:B11 ) ) , ( D3:D11 ) ) |
F6: cerca il valore nella cella F6
B3: B11: l'array di ricerca è Items
D3:D11: l'array restituito è Prezzo
Qui abbiamo il 58 è il prezzo corrispondente al valore "latte" nella cella B5 non il "Latte" nella cella B9. Dovrai cercare il valore "Latte" se hai bisogno del Prezzo "15.58" nella cella D9.
Valori univoci nell'array di ricerca
Allo stesso modo, puoi trovare i valori univoci usando la formula. Qui il valore di ricerca "UOVO" usato come esempio.
Nell'immagine mostrata sopra, abbiamo ottenuto i valori regolando la stessa formula. Ma il problema si verifica se ha un valore univoco e non stai cercando il valore di ricerca corretto. Come qui usando il valore "Pane" nella formula per cercare nella tabella.
La formula restituisce 0 come quantità e Prezzo, ma ciò non significa che la quantità e il Prezzo del pane siano 0. È solo che la formula restituisce 0 come valore quando il valore che stai cercando non viene trovato. Quindi usa questa formula solo per cercare la corrispondenza esatta.
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.
Di seguito sono riportate alcune note osservative.
Appunti:
- La formula funziona solo con la ricerca della corrispondenza esatta.
- La funzione SUMPRODUCT considera i valori non numerici come 0.
- La funzione SUMPRODUCT considera il valore logico TRUE come 1 e False come 0.
- L'array di argomenti deve essere della stessa dimensione, altrimenti la funzione restituisce un errore.
- La funzione SUMPRODUCT restituisce il valore corrispondente ai valori TRUE nell'array restituito dopo aver preso i singoli prodotti nell'array corrispondente.
- Operatori come uguale a ( = ), minore di uguale a ( <= ), più grande di ( > ) o non uguale a ( ) può essere eseguito all'interno di una formula applicata, solo con numeri.
Spero che questo articolo su Come cercare la corrispondenza esatta utilizzando la funzione SUMPRODUCT in Excel sia esplicativo. Trova altri articoli sulle formule di conteggio qui. Se ti sono piaciuti i nostri blog, condividilo con i tuoi fristarts 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
Come utilizzare la funzione SUMPRODUCT in Excel: restituisce la SOMMA dopo la moltiplicazione dei valori in più matrici in Excel.
Usa INDEX e MATCH per cercare il valore : La formula INDEX-MATCH viene utilizzata per cercare in modo dinamico e preciso un valore in una determinata tabella. Questa è un'alternativa alla funzione CERCA.VERT e supera le carenze della funzione CERCA.VERT.
Somma per gruppi OFFSET in righe e colonne : La funzione OFFSET può essere utilizzata per sommare dinamicamente gruppi di celle. Questi gruppi possono trovarsi ovunque nel foglio.
Come recuperare ogni ennesimo valore in un intervallo in Excel: Utilizzando la funzione OFFSET di Excel possiamo recuperare valori da righe o colonne alternate. Questa formula richiede l'aiuto della funzione RIGA per alternare le righe e la funzione COLONNA per alternare le colonne.
Come utilizzare la funzione OFFSET in Excel : La funzione OFFSET è una potente funzione di Excel sottovalutata da molti utenti. Ma gli esperti conoscono la potenza della funzione OFFSET e come possiamo usare questa funzione per eseguire alcune attività magiche nella formula di Excel. Ecco le basi della funzione OFFSET.
Come usare i caratteri jolly in Excel : Conta le celle che corrispondono alle frasi usando i caratteri jolly in Excel
Articoli popolari
50 scorciatoie Excel per aumentare la produttività : Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
Come usare tla funzione CERCA.VERT in Excel Nota: questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare il valore da diversi intervalli e fogli in Excel.
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 in Excel. La funzione CONTA.SE è essenziale per preparare la tua dashboard.
Come utilizzare la funzione SOMMA.SE in Excel : Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori con condizioni specifiche.