contingut
A la pràctica, molt sovint hi ha casos en què tu i jo necessitem trobar el valor més proper en un conjunt (taula) en relació a un nombre determinat. Podria ser, per exemple:
- Càlcul de descompte en funció del volum.
- Càlcul de l'import de les bonificacions en funció de l'aplicació del pla.
- Càlcul de les tarifes d'enviament en funció de la distància.
- Selecció de contenidors adequats per a mercaderies, etc.
A més, pot ser necessari arrodonir tant amunt com a baix, segons la situació.
Hi ha diverses maneres, òbvies i no tan òbvies, de resoldre aquest problema. Mirem-los seqüencialment.
Per començar, imaginem un proveïdor que fa descomptes a l'engròs, i el percentatge del descompte depèn de la quantitat de mercaderies comprades. Per exemple, quan es compren més de 5 peces, es fa un descompte del 2%, i quan es compra a partir de 20 peces, ja el 6%, etc.
Com calcular de manera ràpida i bella el percentatge de descompte en introduir la quantitat dels productes comprats?
Mètode 1: FI nidats
Un mètode de la sèrie "Què hi ha per pensar: has de saltar!". Ús de funcions imbricades IF (SI) per comprovar seqüencialment si el valor de la cel·la cau en cadascun dels intervals i mostrar un descompte per a l'interval corresponent. Però la fórmula en aquest cas pot resultar molt complicada:
Crec que és obvi que depurar un "ninot monstre" o intentar afegir-hi un parell de condicions noves després d'un temps és divertit.
A més, Microsoft Excel té un límit de nidificació per a la funció IF: 7 vegades en versions anteriors i 64 vegades en versions més noves. Què passa si necessites més?
Mètode 2. VLOOKUP amb vista d'interval
Aquest mètode és molt més compacte. Per calcular el percentatge de descompte, utilitzeu la funció llegendària VPR (VISUALITZACIÓ) en mode de cerca aproximada:
where
- B4 – el valor de la quantitat de mercaderies de la primera transacció per a la qual busquem un descompte
- $G$4:$H$8 – un enllaç a la taula de descomptes – sense “capçalera” i amb les adreces fixades amb el signe $.
- 2 — el número ordinal de la columna de la taula de descomptes de la qual volem obtenir el valor de descompte
- VERITABLE – aquí és on està enterrat el “gos”. Si com a darrer argument de funció VPR especificar ESTIRAT (FALS) o 0, llavors la funció buscarà concordança estricta a la columna de quantitat (i en el nostre cas donarà un error #N/A, ja que no hi ha el valor 49 a la taula de descomptes). Però si en canvi ESTIRAT escriure VERITABLE (VERTADER) o 1, aleshores la funció no buscarà l'exacte, però més propera més petita valor i ens donarà el percentatge de descompte que necessitem.
L'inconvenient d'aquest mètode és la necessitat d'ordenar la taula de descomptes en ordre ascendent per la primera columna. Si no hi ha aquesta classificació (o es fa en ordre invers), la nostra fórmula no funcionarà:
En conseqüència, aquest enfocament només es pot utilitzar per trobar el valor més petit més proper. Si necessiteu trobar el més gran més proper, heu d'utilitzar un enfocament diferent.
Mètode 3. Trobar el més proper amb les funcions INDEX i MATCH
Ara mirem el nostre problema des de l'altra banda. Suposem que venem diversos models de bombes industrials de diferents capacitats. La taula de vendes de l'esquerra mostra la potència requerida pel client. Hem de seleccionar una bomba de la potència màxima o igual més propera, però no inferior a la que requereix el projecte.
La funció VLOOKUP no us ajudarà aquí, de manera que haureu d'utilitzar el seu analògic: un munt de funcions INDEX (ÍNDEX) i MÉS EXPOSADA (PARTIDA):
Aquí, la funció MATCH amb l'últim argument -1 funciona en la manera de trobar el valor més proper i la funció INDEX extreu el nom del model que necessitem de la columna adjacent.
Mètode 4. Nova funció VIEW (XLOOKUP)
Si teniu una versió d'Office 365 amb totes les actualitzacions instal·lades, en comptes de BUSCAR V (VISUALITZACIÓ) podeu utilitzar el seu analògic: la funció VIEW (CERCA XL), que ja he analitzat amb detall:
Aquí:
- B4 – el valor inicial de la quantitat del producte per al qual busquem un descompte
- $G$4:$G$8 – la gamma on busquem llumins
- $H$4:$H$8 – l'interval de resultats des del qual voleu retornar el descompte
- quart argument (-1) inclou la cerca del nombre més petit més proper que volem en lloc d'una coincidència exacta.
Els avantatges d'aquest mètode són que no cal ordenar la taula de descomptes i la possibilitat de cercar, si cal, no només el valor més petit més proper, sinó també el valor més proper més. L'últim argument en aquest cas serà 1.
Però, malauradament, encara no tothom té aquesta funció, només els propietaris feliços d'Office 365.
Mètode 5. Power Query
Si encara no esteu familiaritzat amb el potent i completament gratuït del complement Power Query per a Excel, sou aquí. Si ja esteu familiaritzat, intentem utilitzar-lo per resoldre el nostre problema.
Anem a fer primer un treball preparatori:
- Convertim les nostres taules font a dinàmiques (intel·ligents) mitjançant una drecera de teclat Ctrl+T o equip Inici – Format com a taula (Inici — Format com a taula).
- Per a més claredat, donem-los noms. Sales и Descomptes llengüeta constructor (Disseny).
- Carregueu cadascuna de les taules al seu torn a Power Query mitjançant el botó De Taula/Range llengüeta dades (Dades: de la taula/interval). En les darreres versions d'Excel, aquest botó s'ha canviat de nom a Amb fulles (Del full).
- Si les taules tenen noms de columnes diferents amb quantitats, com en el nostre exemple ("Quantitat de mercaderies" i "Quantitat de..."), s'han de canviar el nom a Power Query i anomenar-los igual.
- Després d'això, podeu tornar a Excel seleccionant l'ordre a la finestra de l'editor de Power Query Inici — Tancar i carregar — Tancar i carregar a... (Inici — Tancar i carregar — Tancar i carregar a...) i després opció Només cal crear una connexió (Només crea connexió).
- Aleshores comença el més interessant. Si teniu experiència en Power Query, suposo que la línia de pensament posterior hauria d'anar en la direcció de fusionar aquestes dues taules amb una consulta d'unió (fusionar) a la VLOOKUP, com va ser el cas del mètode anterior. De fet, haurem de fusionar-nos en mode d'addició, cosa que no és gens evident a primera vista. Seleccioneu a la pestanya Excel Dades – Obtenir dades – Combinar sol·licituds – Afegeix (Dades — Obtenir dades — Combinar consultes — Afegeix) i després les nostres taules Sales и Descomptes a la finestra que apareix:
- Després de fer clic OK les nostres taules estaran enganxades en un únic tot, les unes sota les altres. Si us plau, tingueu en compte que les columnes amb la quantitat de mercaderies d'aquestes taules van caure una sota l'altra, perquè. tenen el mateix nom:
- Si la seqüència original de files de la taula de vendes és important per a vostè, per tal que després de totes les transformacions posteriors la pugueu restaurar, afegiu una columna numerada a la nostra taula mitjançant l'ordre Afegir una columna - Columna d'índex (Afegeix columna — Columna d'índex). Si la seqüència de línies no us importa, podeu ometre aquest pas.
- Ara, utilitzant la llista desplegable de la capçalera de la taula, ordena-la per columna Quantitat Ascendent:
- I el truc principal: feu clic amb el botó dret a la capçalera de la columna Descompte triar un equip Omplir - Avall (Omplir — Avall). Cel·les buides amb nul s'emplena automàticament amb els valors de descompte anteriors:
- Queda per restaurar la seqüència original de files ordenant per columna Index (podeu eliminar-lo amb seguretat més tard) i desfer-vos de línies innecessàries amb un filtre nul per columna Codi de transacció:
- Utilitzant la funció BUSCARV per cercar i cercar dades
- L'ús de VLOOKUP (VLOOKUP) distingeix entre majúscules i minúscules
- XNUMXD VLOOKUP (VLOOKUP)