Corrispondenze approssimative per tabelle a fasce

Una tabella a fasce suddivide i valori continui in intervalli. Tra gli esempi rientrano gli scaglioni fiscali, le spese di spedizione in base al peso, gli sconti quantità e i voti in lettere in base al punteggio.

Non esiste una riga per ogni valore possibile: viene memorizzata solo la soglia iniziale di ogni fascia. Un punteggio di 87 non ha una voce esatta, ma rientra nella fascia che inizia da 80.

È qui che il confronto approssimativo dà il meglio di sé: trova la fascia corretta invece di richiedere una corrispondenza esatta.

Finora abbiamo usato MATCH(value, range, 0) per una corrispondenza esatta. Il terzo argomento 0 significa "trovare esattamente questo valore oppure restituire #N/A".

Per le fasce usiamo invece il tipo di corrispondenza 1. Trova il valore più grande minore o uguale a quello cercato. È esattamente il comportamento necessario per cercare una fascia.

Una regola fondamentale: con il tipo di corrispondenza 1, l'elenco delle soglie deve essere ordinato in modo crescente.

Immaginate una tabella dei voti. La colonna E contiene le soglie inferiori, ordinate in modo crescente: 0, 60, 70, 80, 90. La colonna F contiene le lettere: F, D, C, B, A.

Un punteggio da 0 a 59 corrisponde a F, da 60 a 69 a D e così via. Memorizziamo solo l'inizio di ogni fascia, non ogni singolo punteggio.

Il nostro obiettivo è restituire il voto in lettere dato un punteggio nella cella G1.

Usate MATCH in modalità approssimativa per trovare la fascia in cui rientra un punteggio. MATCH(G1, E2:E6, 1), con un punteggio di 87, cerca la soglia più grande che non supera 87.

Le soglie sono 0, 60, 70, 80, 90. La più grande che non supera 87 è 80, che si trova nella posizione 4. MATCH restituisce quindi 4.

Questa posizione indica la fascia corretta, anche se 87 non è presente nell'elenco.

Ora passate la posizione a INDEX sulla colonna delle etichette F2:F6.

INDEX(F2:F6, MATCH(G1, E2:E6, 1)) usa la posizione 4 e restituisce la quarta etichetta, "B".

Un punteggio di 87 viene quindi associato correttamente al voto B. Se modificate G1 impostandolo su 95, MATCH restituisce 5 e ottenete "A"; se lo impostate su 55, MATCH restituisce 1 e ottenete "F".

MATCH approssimativo (tipo 1) richiede un ordine crescente nell'intervallo di ricerca. Presuppone che i dati aumentino e si arresta non appena supera il valore cercato.

Se le soglie non sono ordinate, MATCH potrebbe arrestarsi troppo presto e restituire una posizione errata, senza alcun errore che vi avvisi. Ordinate sempre la colonna delle soglie dalla più piccola alla più grande prima di usare una ricerca per fasce.

Anche XLOOKUP può eseguire corrispondenze approssimative. Il suo quinto argomento, la modalità di corrispondenza, accetta -1 per "corrispondenza esatta o elemento immediatamente più piccolo", una modalità perfetta per le tabelle a fasce.

Trova la soglia più grande minore o uguale a G1 e restituisce l'etichetta corrispondente, senza dover usare INDEX. Per le ricerche per fasce è spesso più facile da leggere rispetto a INDEX-MATCH.

Consideriamo ora uno sconto quantità. Le soglie nella colonna E (quantità ordinata) sono: 0, 10, 50, 100. Gli sconti nella colonna F sono: 0%, 5%, 10%, 15%.

• Ordine di 7: la soglia più grande minore o uguale a 7 è 0, la posizione è 1 e viene restituito 0%.
• Ordine di 60: la soglia più grande minore o uguale a 60 è 50, la posizione è 3 e viene restituito 10%.
• Ordine di 200: la soglia più grande minore o uguale a 200 è 100, la posizione è 4 e viene restituito 15%.

Una sola formula gestisce qualsiasi quantità.

Che cosa succede se un valore è inferiore a tutte le soglie? Con MATCH approssimativo non esiste alcun valore minore o uguale a esso, quindi MATCH restituisce #N/A.

Per evitarlo, assicuratevi che la prima soglia copra il limite inferiore (spesso 0), oppure racchiudete la formula in IFERROR per visualizzare un messaggio chiaro quando l'input è fuori intervallo.

Prestate attenzione a questi problemi tipici delle tabelle a fasce:

• Soglie non ordinate: la causa principale di risultati errati ma privi di avvisi.
• Uso del tipo di corrispondenza 0: forza una corrispondenza esatta e restituisce #N/A per qualsiasi valore intermedio.
• Memorizzazione dei limiti superiori invece degli inizi: MATCH di tipo 1 si aspetta il limite inferiore di ogni fascia, non quello superiore.
• Soglie testuali: i numeri memorizzati come testo compromettono il confronto; manteneteli in formato numerico.

Potete combinare il confronto approssimativo con la tecnica a due vie. Immaginate un costo di spedizione basato sia sulla fascia di peso (righe) sia sulla fascia di zona (colonne).

Usate un MATCH approssimativo (tipo 1) per trovare la riga del peso e un altro per trovare la colonna della zona, quindi passate entrambi a INDEX. Poiché entrambi gli assi contengono soglie ordinate, ogni MATCH individua la fascia corretta.

In questo modo INDEX-MATCH-MATCH si combina con la logica delle fasce per creare tabelle tariffarie dettagliate.

Verificate la vostra comprensione delle ricerche approssimative nelle tabelle a fasce.

Per le ricerche in tabelle a fasce:

• Memorizzate la soglia inferiore di ogni fascia, in ordine crescente.
• Usate MATCH(value, thresholds, 1) per trovare la posizione della fascia (il valore più grande minore o uguale all'input).
• Racchiudetelo in INDEX(labels, ...) per restituire la fascia, oppure usate XLOOKUP(..., -1) per ottenere lo stesso risultato.

Coprite il limite inferiore con una soglia pari a 0 oppure usate IFERROR per gli input fuori intervallo e non lasciate mai le soglie non ordinate.