Edukiak
Praktikan, askotan, zu eta biok multzo batean (taula) baliorik hurbilena aurkitu behar dugu zenbaki jakin bati dagokionez. Hau izan daiteke, adibidez:
- Deskontuaren kalkulua bolumenaren arabera.
- Planaren ezarpenaren araberako hobari kopuruaren kalkulua.
- Bidalketa-tasen kalkulua distantziaren arabera.
- Salgaietarako ontzi egokiak hautatzea, etab.
Gainera, egoeraren arabera, gora zein behera biribiltzea beharrezkoa izan daiteke.
Hainbat modu daude –agerikoak eta ez hain agerikoak– halako arazo bat konpontzeko. Ikus ditzagun sekuentzialki.
Hasteko, imajina dezagun hornitzaile bat handizkakoetan deskontuak egiten dituena, eta deskontuaren portzentajea erositako ondasunen kopuruaren araberakoa da. Esate baterako, 5 pieza baino gehiago erostean, % 2ko deskontua ematen da, eta 20 piezatik erostean, dagoeneko % 6, etab.
Nola kalkulatu azkar eta ederki deskontu-ehunekoa erositako ondasunen kopurua sartzen denean?
1. metodoa: IF habiatuak
Serieko metodo bat "zer dago pentsatzeko: salto egin behar duzu!". Habiaratutako funtzioak erabiltzea IF (BADA) sekuentzialki egiaztatzeko gelaxka-balioa tarte bakoitzean jaisten den eta dagokion tarterako deskontua bistaratzeko. Baina kasu honetan formula oso astuna izan daiteke:
Uste dut begi-bistakoa dela horrelako "munstro panpina" bat araztea edo denbora pixka bat igaro ondoren baldintza berri pare bat gehitzea dibertigarria dela.
Gainera, Microsoft Excel-ek IF funtziorako habia-muga bat du - 7 aldiz bertsio zaharretan eta 64 aldiz bertsio berriagoetan. Zer gehiago behar baduzu?
2. metodoa. VLOOKUP tarte-ikuspegiarekin
Metodo hau askoz trinkoagoa da. Deskontu-ehunekoa kalkulatzeko, erabili legendazko funtzioa VPR (VLOOKUP) gutxi gorabeherako bilaketa moduan:
non
- B4 – Deskontua bilatzen ari garen lehenengo transakzioko ondasun-kantitatearen balioa
- $G$4:$H$8 – Deskontuen taularako esteka – “goibururik” gabe eta helbideak $ zeinuarekin finkatuta.
- 2 — Deskontu-balioa lortu nahi dugun deskontu-taulako zutabearen zenbaki ordinala
- TRUE – hemen dago “txakurra” lurperatuta. Azken funtzioaren argumentu gisa VPR zehaztu GEZURRA (GEZURRA) edo 0, orduan funtzioak bilatuko du partida zorrotza kantitatearen zutabean (eta gure kasuan #N/A errorea emango du, deskontuen taulan 49 baliorik ez baitago). Baina horren ordez GEZURRA idatzi TRUE (EGIA) edo 1, orduan funtzioak ez du zehatza bilatuko, baina hurbilen txikiena balioa eta behar dugun deskontuaren ehunekoa emango digu.
Metodo honen alde txarra deskontu-taula lehen zutabaren arabera goranzko ordenan ordenatu beharra da. Horrelako ordenamendurik ez badago (edo alderantzizko ordenan egiten bada), orduan gure formulak ez du funtzionatuko:
Horren arabera, hurbileneko balio txikiena aurkitzeko soilik erabil daiteke hurbilketa hau. Hurbilen handiena aurkitu behar baduzu, beste ikuspegi bat erabili behar duzu.
3. metodoa. Hurbilen handiena aurkitzea INDEX eta MATCH funtzioak erabiliz
Ikus dezagun orain gure arazoa beste alde batetik. Demagun potentzia ezberdinetako ponpa industrialen hainbat modelo saltzen ditugula. Ezkerreko salmenta-taulak bezeroak behar duen potentzia erakusten du. Potentzia maximo edo berdineko ponpa bat hautatu behar dugu, baina ez proiektuak eskatzen duena baino txikiagoa.
VLOOKUP funtzioak ez du lagunduko hemen, beraz, bere analogikoa erabili beharko duzu - INDEX funtzio mordoa (AURKIBIDEA) eta ERABILI GEHIAGO (PARTIDA):
Hemen, -1 azken argumentua duen MATCH funtzioak balio handiena aurkitzeko moduan funtzionatzen du, eta INDEX funtzioak ondoko zutabetik behar dugun ereduaren izena ateratzen du.
4. metodoa. Funtzio berria IKUSI (XLOOKUP)
Eguneratze guztiak instalatuta dituen Office 365 bertsioa baduzu, VLOOKUP egin beharrean (VLOOKUP) bere analogikoa erabil dezakezu: IKUSI funtzioa (XLOOKUP), dagoeneko zehatz-mehatz aztertu dudana:
Here:
- B4 – Deskontua bilatzen ari garen produktuaren kantitatearen hasierako balioa
- $G$4:$G$8 – pospoloak bilatzen ari garen tartea
- $H$4:$H$8 – deskontua itzuli nahi duzun emaitza sorta
- laugarren argumentua (-1) bat-etortze zehatz baten ordez nahi dugun zenbaki txikienaren bilaketa biltzen du.
Metodo honen abantailak deskontu-taula ordenatu beharrik ez dagoela eta, behar izanez gero, bilatu ahal izatea da, ez bakarrik hurbilen txikiena, baita hurbilen dagoen baliorik handiena ere. Kasu honetan azken argumentua 1 izango da.
Baina, zoritxarrez, denek ez dute oraindik eginbide hau - Office 365-en jabe pozik soilik.
5. metodoa. Power Query
Excel-erako Power Query gehigarri indartsua eta guztiz doakoa ezagutzen ez baduzu, hemen zaude. Dagoeneko ezagutzen baduzu, saia gaitezen gure arazoa konpontzeko erabiltzen.
Egin dezagun lehenik prestaketa lan batzuk:
- Bihur ditzagun gure iturburu-taulak dinamiko (adimentsua) teklatuko lasterbide bat erabiliz Ctrl+T edo taldea Hasiera - Taula gisa formatu (Hasiera — Taula gisa formatu).
- Argitasunerako, eman diezaiegun izenak. salmentak и deskontuak fitxa Constructor (Diseinua).
- Kargatu taula bakoitza Power Query-n botoia erabiliz Taula/Barrutitik fitxa Data (Datuak — Taula/barrutitik). Excel-en azken bertsioetan, botoi honi izena aldatu zaio Hostoekin (Fitxatik).
- Taulek zutabe-izen desberdinak badituzte kantitateekin, gure adibidean bezala ("Ondasun kopurua" eta "Kantitatea..."), orduan Power Query-n izena aldatu behar zaie eta izen bera jarri behar zaie.
- Horren ondoren, Excel-era itzuli dezakezu Power Query editorearen leihoan komandoa hautatuta Hasiera — Itxi eta kargatu — Itxi eta kargatu… (Hasiera — Itxi&Kargatu — Itxi&Kargatu…) eta gero aukera Konexio bat sortu besterik ez dago (Sortu konexioa soilik).
- Orduan hasten da interesgarriena. Power Query-n esperientzia baduzu, uste dut beste pentsamendu-lerroa bi taula hauek batu-kontsulta batekin (batzea) bateratzeko norabidean egon beharko lukeela VLOOKUP, aurreko metodoan gertatu zen bezala. Izan ere, gehitzeko moduan batu beharko dugu, lehen begiratuan batere nabaria ez dena. Hautatu Excel fitxan Datuak – Lortu datuak – Konbinatu eskaerak – Gehitu (Datuak — Lortu datuak — Konbinatu kontsultak — Erantsi) eta gero gure mahaiak salmentak и deskontuak agertzen den leihoan:
- Klik egin ondoren OK gure mahaiak osotasun bakar batean itsatsiko dira, bata bestearen azpian. Kontuan izan taula hauetan ondasun kopurua duten zutabeak bata bestearen azpian zeudela, zeren. izen bera dute:
- Salmenta-taularen jatorrizko errenkaden sekuentzia garrantzitsua bada zuretzat, ondoren eraldaketa guztien ondoren berreskuratu ahal izateko, gehitu zenbakidun zutabe bat gure taulan komandoa erabiliz. Zutabe bat gehitzea - Indizearen zutabea (Gehitu zutabea — Indizearen zutabea). Lerroen sekuentzia ez bazaizu axola, urrats hau salta dezakezu.
- Orain, taularen goiburuko goitibeherako zerrenda erabiliz, ordena ezazu zutabeen arabera Kopurua Goranzkoa:
- Eta trikimailu nagusia: egin klik eskuineko botoiarekin zutabearen goiburuan Deskontua talde bat aukeratu Bete – Behera (Bete — Behera). Gelaxkak hutsik null automatikoki aurreko deskontu-balioekin beteta:
- Jatorrizko errenkaden sekuentzia berreskuratzea geratzen da zutabeen arabera ordenatuta Index (gero segurtasunez ezabatu dezakezu) eta alferrikako lerroak kendu iragazki batekin null zutabeka Transakzio kodea:
- VLOOKUP funtzioa erabiliz datuak bilatzeko eta bilatzeko
- BLOOKUP (BLOOKUP) erabiltzeak maiuskulak eta minuskulak bereizten ditu
- XNUMXD VLOOKUP (VLOOKUP)