In questo articolo impareremo come recuperare un prezzo da un elenco che corrisponde ai criteri di categoria e articolo in Excel.
Scenario:
È facile trovare un valore con un criterio in una tabella, per questo potremmo semplicemente usare CERCA.VERT. Ma quando non hai un criterio a colonna singola nei tuoi dati e devi trovare più criteri di colonne per abbinare un valore, CERCA.VERT non aiuta.
Formula generica sia per la categoria che per l'articolo
| =INDEX(lookup_range,MATCH(1,INDEX((item1=item_range) * (category2=category_range),0,1),0)) |
lookup_range: è l'intervallo da cui si desidera recuperare il valore.
Criteria1, Criteria2, Criterio N: questi sono i criteri che vuoi far corrispondere in intervallo1, intervallo2 e Intervallo N. Puoi avere fino a 270 criteri - coppie di intervalli.
Range1, range2, rangeN : Questi sono gli intervalli in cui corrisponderai ai tuoi rispettivi criteri
Esempio :
Tutti questi potrebbero essere fonte di confusione da capire. Capiamo come utilizzare la funzione usando un esempio. Qui abbiamo una tabella di dati. Voglio estrarre il nome del cliente utilizzando Data di prenotazione, Costruttore e Area. Quindi qui ho tre criteri e un intervallo di ricerca.
Scrivi questa formula nella cella I4 premi invio.
| =INDICE(E2:E16,CONFRONTA(1,INDICE((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0)) |
Sappiamo già come funzionano le funzioni INDICE e CONFRONTA in EXCEL, quindi non lo spiegheremo qui. Parleremo del trucco che abbiamo usato qui.
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16): La parte principale è questa. Ogni parte di questa istruzione restituisce un array di true false.
Quando i valori booleani vengono moltiplicati, restituiscono un array di 0 e 1. La moltiplicazione funziona come un operatore AND. Quindi quando tutti i valori sono veri solo allora restituisce 1 altrimenti 0
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16) Tutto questo tornerà
Che si tradurrà in
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): INDEX Function restituirà lo stesso array ({0;0;0;0;0; 0;0;1;0;0;0;0;0;0;0}) alla funzione CONFRONTA come matrice di ricerca.
CONFRONTA(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): La funzione CONFRONTA cercherà 1 nell'array {0;0;0; 0;0;0;0;1;0;0;0;0;0;0;0}. E restituirà il numero di indice del primo 1 trovato nell'array. Che qui è 8.
INDEX(E2:E16,MATCH(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0)): Infine, INDEX restituirà il valore dall'intervallo dato (E2:E16) all'indice trovato (8).
Se puoi premere CTRL + MAIUSC + INVIO di conseguenza, puoi eliminare la funzione INDICE interna. Basta scrivere questa formula e premere CTRL + MAIUSC + INVIO.
Formula
| =INDICE(E2:E16,CONFRONTA(1,(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0)) |
Formula di matrice generica per la ricerca di criteri multipli
| =INDEX(lookup_range,MATCH(1,(criteri1 =range1)*(criteri2=range2)*(criteriN=rangeN),0)) |
Ecco tutte le note di osservazione utilizzando la formula in Excel
Appunti :
- Usa Vlookup se hai un criterio da soddisfare, è pratica comune.
- Puoi aggiungere più righe e colonne nell'array di ricerca.
- Gli argomenti del testo devono essere indicati tra virgolette ("").
- La tabella VLOOKUP deve avere il lookup_array nell'ultima colonna o nella prima colonna.
- L'indice col non può essere 1 in quanto è l'array di ricerca
- L'argomento 0 viene utilizzato per il valore di corrispondenza esatta. Utilizzare 1 per il valore di corrispondenza approssimativo.
- La funzione restituisce l'errore #N/D, se il valore di ricerca non viene trovato nell'array di ricerca. Quindi cattura l'errore, se necessario.
Spero che questo articolo su Come recuperare un prezzo da un elenco che corrisponde ai criteri di categoria e articolo in Excel sia esplicativo. Trova altri articoli sul calcolo dei valori e le relative formule di Excel qui. Se ti sono piaciuti i nostri blog, condividili 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.
Come recuperare l'ultimo prezzo in Excel : è comune aggiornare i prezzi in qualsiasi attività commerciale e l'utilizzo dei prezzi più recenti per qualsiasi acquisto o vendita è un must. Per recuperare l'ultimo prezzo da un listino in Excel utilizziamo la funzione CERCA. La funzione CERCA recupera il prezzo più recente.
Funzione CERCA.VERT per calcolare il voto in Excel : Per calcolare i voti IF e IFS non sono le uniche funzioni che puoi utilizzare. Il VLOOKUP è più efficiente e dinamico per tali calcoli condizionali. Per calcolare i voti usando VLOOKUP possiamo usare questa formula.
17 cose su Excel VLOOKUP : CERCA.VERT è più comunemente usato per recuperare i valori corrispondenti, ma CERCA.VERT può fare molto di più. Ecco 17 cose su CERCA.VERT che dovresti sapere per usare in modo efficace.
CERCA il primo testo da un elenco in Excel : la funzione CERCA.VERT funziona correttamente con i caratteri jolly. Possiamo usarlo per estrarre il primo valore di testo da un determinato elenco in Excel. Ecco la formula generica.
CERCA data con ultimo valore nell'elenco : Per recuperare la data che contiene l'ultimo valore utilizziamo la funzione CERCA. Questa funzione verifica la cella che contiene l'ultimo valore in un vettore e quindi utilizza tale riferimento per restituire la data.
Articoli popolari:
50 scorciatoie di Excel per aumentare la produttività : velocizza le tue attività in Excel. Queste scorciatoie ti aiuteranno ad aumentare l'efficienza del tuo lavoro in Excel.
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 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 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.