Corrispondenza approssimativa bidirezionale con più criteri
In questo tutorial parleremo di come cercare una corrispondenza approssimativa basata su più criteri elencati sia in colonne che in righe in un foglio di calcolo Excel, con l'aiuto dif INDICE, PARTITAe IF funzioni.
Come eseguire una corrispondenza approssimativa a due vie con più criteri in Excel?
Per scoprire il file taglia per donna che è 165.5alto cm e pesa 55kg secondo la tabella sopra, puoi aggiungere due funzioni CONFRONTA alla formula: una con una funzione SE ottiene il numero di riga (sesso e altezza), l'altra ottiene il numero di colonna (peso). Quindi INDEX troverà la dimensione corrispondente in base alle coordinate. (Si noti che il numero di riga precede il numero di colonna in una formula INDICE.)
Sintassi generica
=INDEX(return_range,MATCH(lookup_value1,IF(lookup_array2=lookup_value2,lookup_array1),match_type),MATCH(lookup_value3,lookup_array3,match_type))
√ Nota: questa è una formula di matrice che richiede di inserire con Ctrl + spostamento + Entra.
- intervallo_ritorno: L'intervallo da cui si desidera che la formula di combinazione restituisca la dimensione. Qui si riferisce alla gamma di dimensioni.
- valore di ricerca: Il valore utilizzato dalla formula per individuare la posizione della dimensione corrispondente. Qui si fa riferimento alle informazioni sul sesso, l'altezza e il peso indicati.
- ricerca_array: L'intervallo di celle con i valori da confrontare con il valore di ricerca. Qui si riferisce alle gamme di sesso, altezza e peso.
- tipo_match: 1 o -1.
1 o omesso (predefinito), CONFRONTA troverà il valore più grande minore o uguale a valore di ricerca. I valori in ricerca_array devono essere inseriti in ordine crescente.
-1, MATCH troverà il valore più piccolo maggiore o uguale a valore di ricerca. I valori in ricerca_array devono essere posti in ordine decrescente.
Per scoprire il file taglia per il donna che è 165.5alto cm e pesa 55kg, per favore copia o inserisci la formula sotto nella cella M9 e premi Ctrl + spostamento + Entra per ottenere il risultato:
= INDICE (RE5:J16,INCONTRO(M6,SE(B5: B16=M5,C5: C16),1),CONFRONTA(M7,RE4:J4, 1))
Spiegazione della formula
=INDEX(D5:J16,MATCH(M6,IF(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))
- IF(B5:B16=M5,C5:C16): La funzione IF controlla ogni valore in B5: B16 se corrispondono al valore nella cella M5, Donna. In tal caso, la funzione SE manterrà le altezze corrispondenti elencate in C5: C16; In caso contrario, IF tornerà FALSI. La formula restituirà un array come questo: {157;160;162.5;165;167.5;170;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
- CORRISPONDENZA(M6,IF(B5:B16=M5,C5:C16), 1) = CORRISPONDENZA(M6,{157;160;162.5;165;167.5;170;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}, 1): Il termoprotettore match_type 1 forza la funzione CONFRONTA a restituire la posizione del valore più grande minore o uguale a 165.5 (il valore nella cella M6) nella matrice. Quindi, MATCH tornerà 4, la posizione del valore 165.
- CONFRONTA(M7,D4:J4,1): La funzione CONFRONTA restituisce 3, poiché il valore più grande che è minore o uguale al valore in M7, 55, è al 3terza posizione della gamma RE4:J4.
- INDICE(RE5:J16,CORRISPONDENZA(M6,IF(B5:B16=M5,C5:C16), 1),CONFRONTA(M7,D4:J4,1)) = INDICE(RE5:J16,4,3): La funzione INDICE recupera il valore all'intersezione di 4la fila e 3terza colonna nell'intervallo di dimensioni RE5:J16, che è il valore nella cella F8, M.
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.
La funzione SE è una delle funzioni più semplici e utili nella cartella di lavoro di Excel. Esegue un semplice test logico che dipende dal risultato del confronto e restituisce un valore se il risultato è VERO o un altro valore se il risultato è FALSO.
Formule correlate
Ricerca bidirezionale con INDEX e MATCH
Per cercare qualcosa su righe e colonne in Excel, o diciamo di cercare un valore all'intersezione della riga e della colonna specifiche, possiamo utilizzare l'aiuto delle funzioni INDICE e CONFRONTA.
Cerca la corrispondenza più vicina
Per cercare la corrispondenza più vicina di un valore di ricerca in un set di dati numerico in Excel, è possibile utilizzare insieme le funzioni INDICE, CONFRONTA, ABS e MIN.
Cerca il valore di corrispondenza più vicino con più criteri
In alcuni casi, potrebbe essere necessario cercare il valore di corrispondenza più vicino o approssimativo in base a più di un criterio. Con la combinazione delle funzioni INDICE, CONFRONTA e SE, puoi farlo rapidamente in Excel.
Ricerca a criteri multipli con INDEX e MATCH
Quando si ha a che fare con un grande database in un foglio di calcolo Excel con diverse colonne e didascalie di riga, è sempre difficile trovare qualcosa che soddisfi più criteri. In questo caso, puoi utilizzare una formula di matrice con le funzioni INDICE e CONFRONTA.
Corrispondenza approssimativa con INDEX e MATCH
Ci sono momenti in cui abbiamo bisogno di trovare corrispondenze approssimative in Excel per valutare le prestazioni dei dipendenti, valutare i punteggi degli studenti, calcolare l'affrancatura in base al peso, ecc. In questo tutorial, parleremo di come utilizzare le funzioni INDICE e CONFRONTA per recuperare il risultati di cui abbiamo bisogno.
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.
