In questo articolo impareremo come CERCARE il valore tra due numeri in Excel.
Scenario:
È facile cercare un valore con un criterio in una tabella. Possiamo semplicemente usare VLOOKUP. Ma cosa potremmo fare se avessimo quei criteri di più colonne da abbinare nei tuoi dati e avessimo bisogno di cercare in più colonne per abbinare un valore. Impariamo come risolvere questo problema utilizzando diverse versioni di ricerca di excel
Valore di CERCA tra due numeri
Utilizzo della formula CERCA.VERT
Nella corrispondenza approssimativa di CERCA.VERT, se non viene trovato un valore, viene abbinato l'ultimo valore inferiore al valore di ricerca e viene restituito il valore dall'indice di colonna specificato.
Formula generica per CERCA valore tra due numeri:
| = CERCA.VERT (valore, tabella, lookup_col , 1 ) |
E un'altra cosa su Vlookup è che cerca il valore nella colonna e se non trova il valore nell'array di colonne, allora corrisponde e restituisce il valore che è inferiore a quel valore nell'array di tabelle.
Nota: VLOOKUP per impostazione predefinita esegue una corrispondenza approssimativa, se omettiamo la variabile di ricerca dell'intervallo. Una corrispondenza approssimativa è utile quando si desidera eseguire una corrispondenza approssimativa e quando l'array di tabelle è ordinato in ordine crescente.
Utilizzo della formula INDICE e CONFRONTA
La formula INDICE e CONFRONTA fa lo stesso lavoro di sopra ma con una sintassi diversa. Ma se dovessi scegliere solo quella con le quali ti trovi a tuo agio
Formula generica per CERCA valore tra due numeri
| =INDICE(intervallo_di ricerca,CONFRONTA(1,INDICE((criteri1=intervallo1)*(criteri2=intervallo2),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 l'ID studente e i loro rispettivi voti in un test. Ora dovevamo ottenere i voti per ogni studente cercando nella tabella del sistema dei voti.
Per fare ciò, utilizzeremo un attributo della funzione CERCA.VERT, ovvero se la funzione CERCA.VERT non trova la corrispondenza esatta, cerca la corrispondenza approssimativa solo nella tabella e solo se l'ultimo argomento della funzione è VERO o 1.
Il punteggio/tabella dei punteggi deve essere in ordine crescente
Usa la formula:
| = CERCA.VERT ( B2 , tabella , 2 , 1 ) |
Come funziona?
La funzione Vlookup cerca il valore 40 nella colonna dei segni e restituisce il valore corrispondente, se corrisponde. Se non corrisponde, la funzione cerca il valore minore del valore di ricerca (cioè 40) e restituisce il risultato.
Qui la matrice_tabella è denominata intervallo come tavolo in formula e B2 è dato come riferimento di cella.
Come puoi vedere abbiamo ottenuto il voto per il primo studente. Ora per ottenere tutti gli altri voti, useremo la scorciatoia Ctrl + D o usa l'opzione di trascinamento della cella in Excel.
Ecco tutti i Gradi della classe che utilizzano la funzione CERCA.VERT.
Valore di RICERCA tra due numeri nella tabella dei dipendenti
Abbiamo una tabella che contiene i dettagli di tutti i dipendenti di un'organizzazione su un foglio separato. La prima colonna contiene l'ID di questi dipendenti. Ho chiamato questa tabella come emp_data.
Ora il mio foglio di ricerca deve recuperare le informazioni del dipendente il cui ID è scritto nella cella B3. Ho chiamato B3 come ID.
Per facilità di comprensione, tutte le intestazioni di colonna sono esattamente nello stesso ordine della tabella emp_data.
Ora scrivi la formula seguente nella cella C3 per recuperare la zona dell'ID dipendente scritto in B3.
| =CERCA.VERT(ID,Emp_Data,2,0) |
Ciò restituirà la zona dell'ID dipendente 1-1830456593 perché la colonna numero 2 nel database contiene la zona dei dipendenti.
Copia questa formula nel resto delle celle e modifica il numero di colonna nella formula per ottenere tutte le informazioni dei dipendenti.
Puoi vedere tutte le informazioni relative all'ID menzionato nella cella B3. Qualunque sia l'ID che scrivi nella cella B3, tutte le informazioni verranno recuperate senza apportare alcuna modifica alla formula.
Come funziona?
Non c'è niente di complicato. Stiamo semplicemente usando la funzione VLOOKUP per cercare l'ID e quindi recuperare la colonna menzionata. Esercitati a CERCA.VERT utilizzando tali dati per comprendere meglio CERCA.VERT.
Recupera i dati dei dipendenti utilizzando le intestazioni
Nell'esempio sopra, tutte le colonne erano organizzate nello stesso ordine, ma ci saranno momenti in cui avrai un database con centinaia di colonne. In questi casi, questo metodo di recupero delle informazioni sui dipendenti non sarà buono. Sarà meglio se la formula può guardare l'intestazione della colonna e recuperare i dati da quella colonna dalla tabella dei dipendenti.
Quindi, per recuperare il valore dalla tabella utilizzando l'intestazione della colonna, utilizzeremo un metodo di ricerca a 2 vie o diciamo la colonna dinamica CERCA.VERT.
Usa questa formula nella cella C3 e copia nelle celle rimanenti. Non è necessario modificare nulla nella formula, tutto verrà recuperato da thd emp_data.
| =CERCA.VERT(ID,Emp_Data,MATCH(C2,Emp_Data_Headers,0),0) |
Questa formula recupera semplicemente tutte le informazioni dalle colonne abbinate. Puoi confondere le intestazioni nel rapporto, questo non farà alcuna differenza. Qualunque sia l'intestazione scritta nella cella sopra, i rispettivi dati contengono.
Come funziona?
Questo è semplicemente un VLOOKUP dinamico. Puoi leggere a riguardo qui. Se lo spiego qui, diventerà un articolo troppo lungo.
Recupera l'ID dipendente con corrispondenza parziale
Può succedere che non ricordi l'intero ID di un dipendente, ma desideri comunque recuperare le informazioni di qualche ID. In questi casi la corrispondenza parziale CERCA.VERT è la soluzione migliore.
Ad esempio, se so che un ID contiene 2345 ma non conosco l'intero ID. Se inserisco questo numero nella cella C3, l'output sarà simile.
Non otteniamo niente. Dal momento che nulla corrisponde a 2345 nella tabella. Modifica la formula sopra in questo modo.
| =CERCA.VERT("*"&ID&"*",Emp_Dati,INCONTRO(C2,Emp_Data_Headers,0),0) |
Copia questo nell'intera riga. E ora hai le informazioni del primo dipendente che contiene questo numero.
Tieni presente che otterremo il primo ID che contiene il numero corrispondente nella colonna Emp Id. Se qualsiasi altro ID contiene lo stesso numero, questa formula non recupererà le informazioni di quel dipendente.
Se vuoi ottenere tutti gli ID dei dipendenti che contengono lo stesso numero, usa la formula che cerca tutti i valori corrispondenti.
Utilizzo della formula INDICE e CONFRONTA
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)) |
Come funziona:
Sappiamo già come funzionano le funzioni INDICE e CONFRONTA in EXCEL, quindi non lo spiegherò 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à
| {FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;VERO;VERO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO}*
{FALSO;FALSO;FALSO;VERO;VERO;VERO;VERO;VERO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;VERO}* {FALSO;FALSO;FALSO;VERO;FALSO;FALSO;FALSO;VERO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO} |
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).
Ecco tutte le note di osservazione utilizzando la formula in Excel
Appunti :
- La colonna della tabella dei voti/punteggi deve essere in ordine crescente, altrimenti la funzione può dare il risultato sbagliato.
- La funzione VLOOKUP cerca il valore nella prima riga di Table_array ed estrae i valori corrispondenti solo a destra di lookup_range.
- L'ultimo argomento della funzione CERCA.VERT deve essere impostato su VERO o 1 per ottenere la corrispondenza approssimativa.
- La funzione CERCA.VERT restituisce un errore se l'indirizzo della cartella di lavoro non è valido o è errato.
- La funzione CERCA.VERT restituisce un errore se il valore non corrisponde.
Spero che questo articolo su Come cercare il valore tra due numeri 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.
Modo di utilizzare la funzione Vlookup nella convalida dei dati : Limita gli utenti a consentire i valori dalla tabella di ricerca utilizzando la casella della formula di convalida dei dati in Excel. La casella della formula nella convalida dei dati consente di scegliere il tipo di restrizione richiesta.
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 valori 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.