Truvà u numeru più vicinu

cuntenutu

In pratica, assai spessu ci sò casi quandu voi è aghju bisognu di truvà u valore più vicinu in un settore (tavula) in relazione à un numeru datu. Puderia esse, per esempiu:

Calculu di scontu secondu u voluminu.
Càlculu di a quantità di bonus secondu l'implementazione di u pianu.
Calculu di i tassi di spedizione secondu a distanza.
Selezzione di cuntenituri adattati per merchenzie, etc.

Inoltre, l'arrotondamentu pò esse dumandatu à l'altu è à u bassu, secondu a situazione.

Ci hè parechje manere - evidenti è micca cusì evidenti - per risolve un tali prublema. Fighjemu à elli in sequenza.

Per principià, imaginemu un fornitore chì dà sconti à l'ingrossu, è u percentualità di u scontu dipende da a quantità di merchenzie compru. Per esempiu, quandu cumprà più di 5 pezzi, un scontu di 2% hè datu, è quandu si compra da 20 pezzi - digià 6%, etc.

Cumu calculà rapidamente è bè u percentualità di scontu quandu inserite a quantità di merchenzie acquistate?

Metudu 1: IF nidificate

Un metudu da a serie "ciò chì ci hè di pensà - avete bisognu di saltà!". Utilizà e funzioni nidificate IF (SI) per verificà sequenziale se u valore di a cellula cade in ognuna di l'intervalli è vede un scontu per a gamma currispondente. Ma a formula in questu casu pò esse assai ingombrante:

Truvà u numeru più vicinu

Pensu chì hè evidenti chì debugging un tali "bambola mostru" o pruvà à aghjunghje un paru di novi cundizioni dopu à qualchì tempu hè divertente.

Inoltre, Microsoft Excel hà un limitu di nidificazione per a funzione IF - 7 volte in versioni più antichi è 64 volte in versioni più recenti. E se avete bisognu di più?

Metudu 2. VLOOKUP cù vista intervallu

Stu metudu hè assai più compactu. Per calculà u percentuale di scontu, utilizate a funzione legendaria VPR (CERCA V) in modu di ricerca apprussimata:

Truvà u numeru più vicinu

induva

B4 - u valore di a quantità di merchenzie in a prima transazzione per quale avemu cercatu un scontu
$G$4:$H$8 - un ligame à a tavola di scontu - senza "header" è cù l'indirizzi fissi cù u segnu $.
2 - u numeru ordinale di a colonna in a tabella di scontu da quale vulemu ottene u valore di scontu
CALDO - quì hè intarratu u "cane". Se cum'è l'ultimu argumentu di funzione VPR specificà MENZONI (FALSE) o 0, allura a funzione cercà match strettu in a colonna di quantità (è in u nostru casu darà un errore #N / A, postu chì ùn ci hè micca valore 49 in a tabella di scontu). Ma si invece MENZONI scrivemu CALDO (VERU) o 1, allura a funzione ùn cercà micca l'esatta, ma u più vicinu più chjucu valore è ci darà u percentuale di scontu chì avemu bisognu.

L'inconveniente di stu metudu hè a necessità di sorte a tavola di scontu in ordine crescente da a prima colonna. Se ùn ci hè micca una tale classificazione (o hè fatta in ordine inversu), allora a nostra formula ùn funziona micca:

Truvà u numeru più vicinu

Per quessa, stu approcciu pò esse usatu solu per truvà u valore più vicinu. Sè avete bisognu di truvà u più vicinu più grande, allora avete aduprà un accostu diversu.

Metudu 3. Truvà u più vicinu più grande utilizendu e funzioni INDEX è MATCH

Avà fighjemu u nostru prublema da l'altra parte. Supponemu chì vendemu parechji mudelli di pompe industriali di diverse capacità. A tavula di vendita à a manca mostra a putenza necessaria da u cliente. Avemu bisognu di selezziunà una pompa di a putenza massima o uguale più vicinu, ma micca menu di ciò chì hè necessariu da u prugettu.

A funzione VLOOKUP ùn aiuterà micca quì, cusì avete da aduprà u so analogicu - una mansa di funzioni INDEX. (INDICE) è PIÙ SPOSI (MATCH):

Truvà u numeru più vicinu

Quì, a funzione MATCH cù l'ultimu argumentu -1 travaglia in u modu di truvà u più grande valore più vicinu, è a funzione INDEX estrae u nome di mudellu chì avemu bisognu da a colonna adiacente.

Metudu 4. Nova funzione VIEW (XLOOKUP)

Se tenete una versione di Office 365 cù tutte l'aghjurnamenti installati, invece di VLOOKUP (CERCA V) pudete aduprà u so analogicu - a funzione VIEW (XLOOKUP), chì aghju digià analizatu in dettu:

Truvà u numeru più vicinu

Eccu:

B4 - u valore iniziale di a quantità di u pruduttu per quale avemu cercatu un scontu
$G$4:$G$8 - a gamma induve circhemu i partiti
$ H $ 4: $ H $ 8 - a gamma di risultati da quale vulete rinvià u scontu
quartu argumentu (-1) include a ricerca di u più chjucu numeru più vicinu chì vulemu invece di una partita esatta.

I vantaghji di stu metudu sò chì ùn ci hè micca bisognu di sorte a tavola di scontu è a capacità di circà, se ne necessariu, micca solu u più vicinu più chjucu, ma ancu u più vicinu valore più grande. L'ultimu argumentu in questu casu serà 1.

Ma, sfurtunatamenti, micca tutti anu ancu sta funzione - solu i pruprietarii felici di Office 365.

Metudu 5. Power Query

Se ùn site micca ancu familiarizatu cù u putente è completamente gratuitu Power Query add-in per Excel, allora site quì. Sè site digià familiarizatu, allora pruvemu à aduprà per risolve u nostru prublema.

Facemu un pocu di travagliu preparatori prima:

Cunvertemu e nostre tavule fonte in dinamica (intelligente) utilizendu una scurciatoia di tastiera Ctrl+T o squadra Home - Formate cum'è una tavola (Casa - Formate cum'è Table).
Per chjarità, damu i nomi. Ventes и Shepard Insignia regala (Cuncepimentu).
Caricà ogni tavule in turnu in Power Query usendu u buttone Da Table/Range Insignia Dati (Dati - Da a tavola / gamma). In e versioni recenti di Excel, stu buttone hè statu rinominatu Cù foglie (Da u fogliu).
Se i tavulini anu diversi nomi di colonna cù quantità, cum'è in u nostru esempiu ("Quantità di merchenzie" è "Quantità da ..."), allora devenu esse rinominati in Power Query è chjamati u listessu.
Dopu quì, pudete vultà in Excel scegliendu u cumandimu in a finestra di l'editore Power Query Casa — Chiudi è Carica — Chiudi è Carica in… (Home — Close&Load — Close&Load to…) e poi opzione Basta à creà una cunnessione (Crea solu cunnessione).
Allora principia u più interessante. Sè vo avete spirienza in Power Query, allura sugnu chì l 'ulteriore linea di pensamentu deve esse in a direzzione di fusione sti dui tavule cù una quistione di unisce (fusione) à la VLOOKUP, cum'è era u casu in u metudu precedente. In fatti, avemu bisognu di unisce in u modu di aghjunghje, chì ùn hè micca in tuttu ovvi à u primu sguardu. Sceglite in a tabulazione Excel Dati - Get Data - Cumbine Requests - Add (Dati - Get Data - Cumbine e dumande - Append) e poi i nostri tavulini Ventes и Shepard in a finestra chì appare:
Dopu avè cliccatu OK i nostri tavulini seranu colati in un unicu tutale - sottu à l'altru. Per piacè nutate chì e culonni cù a quantità di merchenzie in questi tavulini cascò sottu à l'altri, perchè. anu u listessu nome:
Se a sequenza originale di fila in a tavula di vendita hè impurtante per voi, perchè dopu tutte e trasfurmazioni successive pudete restaurà, aghjunghje una colonna numerata à a nostra tavula cù u cumandimu. Adding a Column - Index Column (Aggiungi una colonna - Colonna d'indice). Se a sequenza di linii ùn importa micca per voi, pudete saltà stu passu.
Avà, aduprendu a lista drop-down in l'intestazione di a tavula, sorte per colonna quantità Ascendante:
È u truccu principale: cliccate right-click nantu à l'intestazione di a colonna Discount sceglie una squadra Fill - Down (Fill - Down). Cellule viote cun null null cumpletu automaticamente cù i valori di sconti precedenti:
Resta à restaurà a sequenza originale di fila per sorte per colonna Index (pudete sguassà in modu sicuru dopu) è sguassate di e linee inutili cù un filtru null null per colonna Codice di transazzione: