È comune tenere traccia dei progressi tramite benchmark. Le commissioni sulle vendite sono un buon esempio di questo tipo di impostazione. Nello specifico, la percentuale di commissione aumenta con il totale dell’acquisto. A
d esempio, se il totale è compreso tra $ 1 e $ 299, la commissione è del 3%; se il totale è compreso tra $ 300 e $ 499, la commissione è del 4% e così via. In questo articolo, ti mostrerò come utilizzare sia XLOOKUP() che VLOOKUP() in Microsoft Excel per restituire la commissione appropriata in base al prezzo di acquisto totale. Sembra più difficile di quanto non sia in realtà. In questo caso nessuna funzione si dimostra superiore all’altra.
Sto usando Microsoft 365 su un sistema Windows 10 a 64 bit. Puoi usare le versioni precedenti con VLOOKUP(), ma XLOOKUP() è disponibile solo in Microsoft 365, Excel 2021 ed Excel Online. Ci sono due file dimostrativi ed entrambi contengono entrambi i fogli della soluzione. Tuttavia, le funzioni XLOOKUP() nel file formattato .xls verranno visualizzate come errori perché non sono supportate.
Informazioni su XLOOKUP() in Excel
XLOOKUP() è una delle numerose nuove funzioni di array dinamico. Se hai mai inserito un’espressione usando Ctrl + Maiusc + Invio, allora hai già familiarità con il modo in cui Excel funzionava con gli array dinamici. Grazie alla nuova funzionalità di array dinamico, questi tipi di espressioni sono molto più facili da creare e mantenere perché puoi inserire l’espressione come faresti normalmente: un semplice Invio. I risultati si riversano nelle celle sottostanti, riempiendo il numero necessario per completare i calcoli dell’espressione. Questo è chiamato l’ intervallo di fuoriuscita . Se vedi un errore di versamento, l’intervallo necessario per soddisfare la funzione non è disponibile. Ciò significa che è possibile utilizzare una funzione per restituire più colonne (o righe) di valori risultanti.
Per ulteriori informazioni sulla sintassi e sui vantaggi di questa nuova funzione, leggi Come utilizzare la nuova funzione di matrice dinamica XLOOKUP() in Excel .
Il problema
A volte XLOOKUP() ha ovvi vantaggi rispetto a VLOOKUP(), ma non sempre. Supponiamo di voler restituire un saldo corrente delle commissioni dovute e che la percentuale della commissione dipenda dall’importo totale dell’acquisto. Probabilmente ti sei imbattuto in situazioni in cui la percentuale della commissione è un importo fisso, ma in questo caso la percentuale dipende dal valore della vendita. Quando si esaminano i requisiti, si potrebbe pensare che entrambi i valori nella tabella di ricerca delle commissioni siano obbligatori: se l’acquisto è superiore a questo, ma inferiore a quello, utilizzare x percentuale . Tale presupposto potrebbe rendere la soluzione più difficile da raggiungere del necessario, ma non perché lo sia davvero. Con entrambe le funzioni di ricerca, devi solo cercare un valore: il valore basso o alto, ma non entrambi.
Il semplice set di dati mostrato nella Figura A ha colonne vuote per la percentuale della commissione, l’importo della commissione e un totale parziale della commissione guadagnata. La tabella di ricerca a destra memorizza i benchmark e le percentuali per i gruppi di importi di acquisto. Iniziamo con una soluzione utilizzando XLOOKUP(). La tabella di ricerca a destra esprime i gruppi di commissioni. Come puoi vedere, la percentuale aumenta all’aumentare dei prezzi di acquisto (creando gruppi di limiti bassi e alti).
Figura A
La tabella di ricerca a destra determina la percentuale di commissione.
Come utilizzare XLOOKUP() per calcolare le commissioni in Excel
Questa volta esaminiamo i requisiti della commissione utilizzando dati reali. Il primo valore di vendita è di $ 1.208. La tabella di ricerca a destra mostra che $ 1,208 rientra nel livello di commissione del 20% (inferiore a $ 1,499 ma superiore a $ 1.000).
Esaminiamo la tabella di ricerca prima di continuare. Noterai che i valori basso e alto fluiscono tutti consecutivamente dal livello precedente al livello successivo e lo fanno in modo coerente. Questa configurazione è importante per ottenere risultati corretti: nessun valore viene saltato. Potresti scambiare le cose e funzionerebbe ancora. Ad esempio, se il primo livello massimo fosse $ 400, il valore minimo nel livello successivo sarebbe $ 401.
Menzioniamo brevemente la sintassi di XLOOKUP:
=XLOOKUP(valore_ricerca, matrice_ricerca, matrice_ritorno, [se_non_trovato], [modalità_corrispondenza], [modalità_ricerca])
L’unico argomento opzionale che useremo è [match_mode], ma prima, mappamo gli argomenti richiesti:
- lookup_value sono i valori di acquisto nella colonna C.
- lookup_array è la ricerca oi dati di origine, che è J2:L7, la tabella di ricerca a destra.
- array_ritorno sono i valori percentuali che si desidera restituire, che sono nella colonna J.
Poiché i valori di acquisto nella colonna C non sono ordinati, utilizzeremo [match_code] per specificare il tipo di corrispondenza. Il valore predefinito è 0 per trovare una corrispondenza esatta . Invece, useremo -1, che è Trova una corrispondenza esatta; restituisce l’elemento successivo più piccolo se non viene trovata alcuna corrispondenza . Ci si potrebbe anche chiedere come una funzione di ricerca possa restituire i valori percentuali quando si trovano a sinistra delle colonne Bassa e Alta. Questo è uno dei nuovi aggiornamenti alla funzione XLOOKUP(); puoi fare riferimento alle colonne a sinistra della colonna di ricerca. Ora, mettiamoci al lavoro.
Per prima cosa, inserisci la funzione
=XCERCA(C3,$K$2:$K$7,$J$2:$J$7,,-1)
nella cella F3 e copialo nelle celle rimanenti in quella colonna. Si noti che i due riferimenti all’array sono assoluti; è importante. Se converti la tabella di ricerca in un oggetto Table, i tuoi riferimenti avranno un aspetto simile al seguente:
=XLOOKUP(C3,Tabella2[[#Tutti],[Basso]],Tabella2[[#Tutti],[Per]],,-1)
I riferimenti #ALL possono essere rimossi:
=XLOOKUP(C3,Tabella2[Basso],Tabella2[Per]],,-1)
Come puoi vedere nella Figura B , questa funzione restituisce la percentuale di commissione appropriata dalla tabella di ricerca a destra.
Figura B
La funzione XLOOKUP() restituisce la percentuale di commissione.
La funzione XLOOKUP() restituisce le percentuali di commissione dalla Colonna J a seconda dei valori di acquisto nella Colonna C. Una volta ottenuti questi valori, il resto del foglio è un gioco da ragazzi:
- Inserisci l’espressione = C3 * F3 in G3 e copia nelle celle rimanenti nella colonna G.
- Immettere l’espressione =G3 in H3. In questo modo verrà restituito il primo valore della commissione nel set di dati.
- Immettere l’espressione =H3+G4 nella cella H4. Così facendo sommerai la prima commissione con la seconda. Copia questa semplice espressione nelle celle rimanenti nella colonna H per creare un totale parziale per le commissioni.
La figura C mostra il foglio completato. Le colonne della commissione e del totale parziale dipendono dal valore della commissione percentuale restituito da XLOOKUP(). Potresti restituire un valore di valori alti altrettanto facilmente, ma hai solo bisogno di una colonna di ricerca. Includere entrambi è ottimo per la documentazione o la condivisione con gli utenti, ma entrambi non sono necessari per ottenere risultati.
Figura C
Il foglio completato contiene i valori dei risultati a sinistra dei valori di ricerca.
Al variare delle percentuali di commissione, puoi aggiornare rapidamente i valori di riferimento (basso, alto e percentuali). Non è necessario modificare le funzioni e le espressioni. Funziona ancora tutto.
Se non hai eseguito l’aggiornamento a Microsoft 365, devi usare VLOOKUP(). La buona notizia è che non è più difficile. Se hai Microsoft 365, ti consiglio di usare XLOOKUP(), ma non è probabile che Microsoft deprecherà presto le vecchie funzioni di ricerca. Ora, vediamo come funziona VLOOKUP() con questa configurazione.
Come usare CERCA.VERT() in Excel
Probabilmente hai familiarità con VLOOKUP(). Non può gestire un valore di risultato che si trova a sinistra del valore di ricerca, ma questa è la disposizione che abbiamo nella tabella di ricerca. (L’ho fatto apposta per enfatizzare una grande differenza tra le due funzioni.) Usando VLOOKUP(), devi spostare i valori percentuali a destra dei valori basso e alto, come mostrato nella Figura D .
Figura D
Sposta le percentuali a destra dei valori di ricerca.
La sintassi della funzione CERCA.VERT()
CERCA.VERT(valore_ricerca, array_ricerca, indice_colonna, [ricerca_intervallo])
è diverso da XLOOKUP(), ma il risultato sarà lo stesso. Inserisci la seguente funzione in F3 e copiala nelle celle rimanenti:
=CERCA.VERT(C3,$J$2:$L$7,3,VERO)
Puoi vedere nella Figura E , che la funzione restituisce gli stessi valori percentuali. Le formule per le colonne G e H sono le stesse di prima:
- SOL3: =DO3*FA3
- H3: =G3
- H4: =H3+G4
Figura E
La vecchia funzione di ricerca restituisce gli stessi risultati.
Il foglio è mostrato nella Figura F . L’argomento TRUE trova la corrispondenza più vicina, il che significa che non è necessario ordinare il set di dati. Inoltre, non abbiamo bisogno di entrambi i valori basso e alto per ottenere risultati.
Figura F
Il foglio completato con CERCA.VERT() è uguale a quello con CERCA.X(), ad eccezione del posizionamento dei valori percentuali nella tabella di ricerca.
In questo caso, non c’è un reale vantaggio nell’usare XLOOKUP() oltre a non dover riorganizzare la tabella di ricerca, che potrebbe essere importante. Andando avanti, ti consiglio di iniziare a utilizzare XLOOKUP(), ma non preoccuparti di cambiare i fogli esistenti.