Corrispondenza approssimativa bidirezionale con criteri multipli
In questa guida scopriremo come cercare una corrispondenza approssimativa in base a più criteri distribuiti tra colonne e righe di un foglio Excel, utilizzando in modo efficace le funzioni INDICE, CONFRONTA e SE.
Come eseguire una corrispondenza approssimativa bidirezionale con criteri multipli in Excel?
Per determinare la taglia di abbigliamento di una donna alta 165,5 cm e dal peso di 55 kg in base alla tabella sopra, puoi combinare due funzioni CONFRONTA all’interno della formula: una, abbinata alla funzione SE, individua il numero di riga (in base a sesso e altezza), mentre l’altra determina il numero di colonna (in base al peso). In seguito, la funzione INDICE restituirà la taglia corrispondente utilizzando queste coordinate. (Ricorda che nella funzione INDICE il numero di riga va sempre indicato prima di quello di colonna.)
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 essere inserita premendo Ctrl+Maiusc+Invio.
- return_range: L'intervallo da cui si desidera che la formula combinata restituisca la taglia. In questo caso, si riferisce all'intervallo delle taglie.
- lookup_value: Il valore utilizzato dalla formula per individuare la posizione della taglia corrispondente. In questo caso si riferisce alle informazioni su sesso, altezza e peso fornite.
- lookup_array: L'intervallo di celle contenente i valori da confrontare con il lookup_value. In questo caso, si riferisce agli intervalli relativi a sesso, altezza e peso.
- match_type: 1 o -1.
1 oppure omesso (valore predefinito): la funzione CONFRONTA trova il valore più grande minore o uguale a lookup_value. I valori presenti nell'lookup_array devono essere disposti in ordine crescente.
-1: la funzione CONFRONTA trova il valore più piccolo maggiore o uguale a lookup_value. I valori presenti nell'lookup_array devono essere disposti in ordine decrescente.
Per determinare la taglia di abbigliamentodella donnaalta 165,5cm e dal peso di 55kg, copiare o digitare la formula seguente nella cella M9 e premere Ctrl+Maiusc+Invioper ottenere il risultato:
=INDICE()D5:J16;CONFRONTA()M6;SE()B5:B16=M5;C5:C16);1);CONFRONTA(M7;D4:J4;1))
Spiegazione della formula
=INDEX()D5:J16,MATCH(M6,)IF(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))
- SE(B5:B16=M5;C5:C16):La funzione SE confronta ogni valore nell'intervallo B5:B16con il contenuto della cella M5, ovvero Femmina. Se la condizione è vera, la funzione restituisce le altezze corrispondenti presenti in C5:C16; in caso contrario, restituisce FALSO. Il risultato della formula è un array come questo:{157;160;162,5;165;167,5;170;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO}.
- CONFRONTA(M6;)SE(B5:B16=M5;C5:C16),1)=CONFRONTA(M6;){157;160;162,5;165;167,5;170;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO},1):Con il match_type 1, la funzione CONFRONTA restituisce la posizione del valore più grande minore o uguale a 165,5(il valore nella cella)M6) nell'array. Pertanto, CONFRONTA restituirà 4, la posizione del valore 165.
- CONFRONTA(M7;D4:J4;1):La funzione CONFRONTA restituisce 3, poiché il valore più grande minore o uguale a quello in M7, ovvero 55, si trova alla 3ª posizione dell'intervallo D4:J4.
- INDICE()D5:J16,CONFRONTA(M6;)SE(B5:B16=M5;C5:C16),1),CONFRONTA(M7;D4:J4;1)) = INDICE(D5:J16:La funzione INDICE recupera il valore all'intersezione tra la 4ª riga e la 3ª colonna nell'intervallo delle taglie D5:J16, ovvero il valore presente nella cella F8: M.
Funzioni correlate
La funzione INDICE di Excel restituisce il Valore visualizzato in base a una posizione specificata all'interno di un intervallo o di una matrice.
La funzione CONFRONTA di Excel cerca un valore specifico in un intervallo di celle e restituisce la posizione relativa di tale valore.
La funzione SE è una delle più semplici e utili in Excel. Esegue un test logico che, a seconda del risultato del confronto, restituisce un valore se VERO, un altro se FALSO.
Formule correlate
Ricerca bidirezionale con INDICE e CONFRONTA
Per cercare un elemento sia tra le righe che tra le colonne in Excel, ovvero per trovare un valore all'intersezione di una riga e di una colonna specifiche, possiamo utilizzare le funzioni INDICE e CONFRONTA.
Per individuare il valore più vicino a un valore di ricerca all’interno di un set di dati numerici in Excel, puoi combinare le funzioni INDICE, CONFRONTA, ASS e MIN.
Ricerca del valore più vicino con criteri multipli
In alcuni casi potrebbe essere necessario trovare il valore più vicino o approssimativo in base a più criteri. Grazie alla combinazione delle funzioni INDICE, CONFRONTA e SE, è possibile ottenere rapidamente il risultato desiderato in Excel.
Ricerca con criteri multipli tramite INDICE e CONFRONTA
Quando si lavora con un database complesso in un foglio di calcolo Excel, caratterizzato da numerose colonne e intestazioni di riga, individuare un elemento che soddisfi più criteri contemporaneamente può rivelarsi complicato. In questi casi, la soluzione ideale è utilizzare una formula di matrice basata sulle funzioni INDICE e CONFRONTA.
Corrispondenza approssimativa con INDICE e CONFRONTA
A volte è necessario trovare corrispondenze approssimative in Excel per valutare le prestazioni dei dipendenti, assegnare voti agli studenti, calcolare le spese postali in base al peso e molto altro. In questa guida scoprirai come sfruttare al meglio le funzioni INDICE e CONFRONTA per ottenere i risultati desiderati.
I Migliori Strumenti per la Produttività in Office
Kutools per Excel – Aiuta te a distinguerti dalla folla
Kutools per Excel Vanta oltre 300 funzionalità,assicurandoti che ciò di cui hai bisogno sia sempre a portata di clic...
Office Tab – Abilita la lettura e la modifica a schede in Microsoft Office (incluso Excel)
- Passa tra decine di documenti aperti in un secondo!
- Riduci centinaia di clic del mouse ogni giorno e dì addio al “mouse hand”.
- Aumenta la tua produttività del 50 % visualizzando e modificando più documenti contemporaneamente.
- Porta l’efficienza delle schede in Office (incluso Excel), proprio come in Chrome, Edge e Firefox.