Trovare il numero più vicino

Contenuti

In pratica, molto spesso ci sono casi in cui io e te abbiamo bisogno di trovare il valore più vicino in un insieme (tabella) in relazione a un dato numero. Potrebbe essere, ad esempio:

Calcolo dello sconto in base al volume.
Calcolo dell'importo dei bonus in funzione dell'attuazione del piano.
Calcolo delle tariffe di spedizione in base alla distanza.
Selezione di contenitori adatti per merci, ecc.

Inoltre, l'arrotondamento può essere richiesto sia per eccesso che per difetto, a seconda della situazione.

Esistono diversi modi, ovvi e non così ovvi, per risolvere un problema del genere. Diamo un'occhiata a loro in sequenza.

Per cominciare, immaginiamo un fornitore che fa sconti all'ingrosso e la percentuale di sconto dipende dalla quantità di merce acquistata. Ad esempio, quando si acquistano più di 5 pezzi, viene concesso uno sconto del 2% e quando si acquista da 20 pezzi – già il 6%, ecc.

Come calcolare in modo rapido e bello la percentuale di sconto quando si inserisce la quantità della merce acquistata?

Metodo 1: IF nidificati

Un metodo della serie "cosa c'è da pensare - devi saltare!". Utilizzo di funzioni nidificate IF (SE) per verificare in sequenza se il valore della cella rientra in ciascuno degli intervalli e visualizzare uno sconto per l'intervallo corrispondente. Ma la formula in questo caso può risultare molto macchinosa:

Trovare il numero più vicino

Penso che sia ovvio che eseguire il debug di una tale "bambola mostruosa" o cercare di aggiungere un paio di nuove condizioni ad essa dopo un po' di tempo sia divertente.

Inoltre, Microsoft Excel ha un limite di annidamento per la funzione SE: 7 volte nelle versioni precedenti e 64 volte nelle versioni più recenti. E se avessi bisogno di più?

Metodo 2. VLOOKUP con visualizzazione a intervalli

Questo metodo è molto più compatto. Per calcolare la percentuale di sconto, usa la funzione leggendaria VPR (CERCA.VERT) in modalità di ricerca approssimativa:

Trovare il numero più vicino

where

B4 – il valore della quantità di merce nella prima transazione per la quale stiamo cercando uno sconto
$ SOL $ 4: $ H $ 8 – un link alla tabella degli sconti – senza “intestazione” e con gli indirizzi fissati con il segno $.
2 — il numero ordinale della colonna nella tabella degli sconti da cui si vuole ricavare il valore dello sconto
TRUE – qui è sepolto il “cane”. If come ultimo argomento della funzione VPR specificare GIACENTE (FALSO) o 0, quindi la funzione cercherà corrispondenza rigorosa nella colonna della quantità (e nel nostro caso darà un errore #N/D, poiché nella tabella degli sconti non c'è il valore 49). Ma se invece GIACENTE scrivere TRUE (VERO) o 1, quindi la funzione non cercherà l'esatto, ma il più vicino più piccolo valore e ci darà la percentuale di sconto di cui abbiamo bisogno.

Lo svantaggio di questo metodo è la necessità di ordinare la tabella degli sconti in ordine crescente in base alla prima colonna. Se non esiste tale ordinamento (o viene eseguito in ordine inverso), la nostra formula non funzionerà:

Trovare il numero più vicino

Di conseguenza, questo approccio può essere utilizzato solo per trovare il valore più piccolo più vicino. Se devi trovare il più grande più vicino, devi utilizzare un approccio diverso.

Metodo 3. Trovare il più grande più vicino utilizzando le funzioni INDICE e CONFRONTA

Ora diamo un'occhiata al nostro problema dall'altra parte. Supponiamo di vendere diversi modelli di pompe industriali di varie capacità. La tabella di vendita a sinistra mostra la potenza richiesta dal cliente. Dobbiamo selezionare una pompa della potenza massima o uguale più vicina, ma non inferiore a quella richiesta dal progetto.

La funzione VLOOKUP non sarà di aiuto qui, quindi dovrai usare il suo analogo: un sacco di funzioni INDEX (INDICE) e PIÙ ESPOSTO (INCONTRO):

Trovare il numero più vicino

Qui, la funzione MATCH con l'ultimo argomento -1 funziona nella modalità di trovare il valore più grande più vicino e la funzione INDEX estrae quindi il nome del modello di cui abbiamo bisogno dalla colonna adiacente.

Metodo 4. Nuova funzione VISUALIZZA (XLOOKUP)

Se hai una versione di Office 365 con tutti gli aggiornamenti installati, invece di VLOOKUP (CERCA.VERT) puoi usare il suo analogo: la funzione VIEW (CERCA X), che ho già analizzato in dettaglio:

Trovare il numero più vicino

Qui:

B4 – il valore iniziale della quantità del prodotto per il quale stiamo cercando uno sconto
$ G $ 4: $ G $ 8 – la gamma dove stiamo cercando le partite
$ H $ 4: $ H $ 8 – la gamma di risultati da cui si desidera restituire lo sconto
quarto argomento (-1) include la ricerca del numero più piccolo più vicino che desideriamo invece di una corrispondenza esatta.

I vantaggi di questo metodo sono che non è necessario ordinare la tabella degli sconti e la possibilità di cercare, se necessario, non solo il valore più piccolo più vicino, ma anche il valore più vicino. L'ultimo argomento in questo caso sarà 1.

Ma, sfortunatamente, non tutti hanno ancora questa funzionalità: solo i felici possessori di Office 365.

Metodo 5. Query di alimentazione

Se non hai ancora familiarità con il potente e completamente gratuito componente aggiuntivo Power Query per Excel, allora sei qui. Se hai già familiarità, allora proviamo a usarlo per risolvere il nostro problema.

Facciamo prima un po' di lavoro preparatorio:

Convertiamo le nostre tabelle di origine in dinamiche (intelligenti) usando una scorciatoia da tastiera Ctrl+T o squadra Home – Formatta come una tabella (Home — Formatta come tabella).
Per chiarezza, diamo loro dei nomi. Vendite и sconti linguetta Costruttore (Design).
Carica ciascuna delle tabelle a turno in Power Query utilizzando il pulsante Dalla tabella/intervallo linguetta Dati (Dati — Da tabella/intervallo). Nelle versioni recenti di Excel, questo pulsante è stato rinominato in Con foglie (Dal foglio).
Se le tabelle hanno nomi di colonne diversi con quantità, come nel nostro esempio ("Quantità di merci" e "Quantità da ..."), allora devono essere rinominate in Power Query e denominate allo stesso modo.
Successivamente, puoi tornare a Excel selezionando il comando nella finestra dell'editor di Power Query Home — Chiudi e carica — Chiudi e carica in... (Home — Chiudi&Carica — Chiudi&Carica in...) e poi opzione Basta creare una connessione (Crea solo connessione).
Quindi inizia il più interessante. Se hai esperienza in Power Query, presumo che l'ulteriore linea di pensiero dovrebbe essere nella direzione di unire queste due tabelle con una query di join (merge) a la VLOOKUP, come nel caso del metodo precedente. In effetti, dovremo unire in modalità aggiunta, che a prima vista non è affatto scontata. Seleziona nella scheda Excel Dati – Ottieni dati – Combina richieste – Aggiungi (Dati — Ottieni dati — Combina query — Aggiungi) e poi le nostre tavole Vendite и sconti nella finestra che compare:
Dopo aver fatto clic su OK i nostri tavoli saranno incollati in un unico insieme, uno sotto l'altro. Si noti che le colonne con la quantità di merci in queste tabelle cadevano l'una sotto l'altra, perché. hanno lo stesso nome:
Se la sequenza originale delle righe nella tabella di vendita è importante per te, in modo che dopo tutte le successive trasformazioni tu possa ripristinarla, aggiungi una colonna numerata alla nostra tabella usando il comando Aggiunta di una colonna – Colonna indice (Aggiungi colonna — Colonna indice). Se la sequenza di righe non ti interessa, puoi saltare questo passaggio.
Ora, utilizzando l'elenco a discesa nell'intestazione della tabella, ordinalo per colonna Quantità Ascendente:
E il trucco principale: fare clic con il pulsante destro del mouse sull'intestazione della colonna Codice Sconto scegli una squadra Riempi – Giù (Riempi — Giù). Celle vuote con nullo compilato automaticamente con i valori di sconto precedenti:
Resta da ripristinare la sequenza originale di righe ordinando per colonna Indice (puoi eliminarlo in sicurezza in un secondo momento) ed eliminare le righe non necessarie con un filtro nullo per colonna Codice transazione: