Saturs
Praksē ļoti bieži ir gadījumi, kad jums un man ir jāatrod tuvākā vērtība kopā (tabulā) attiecībā pret doto skaitli. Tas varētu būt, piemēram:
- Atlaides aprēķins atkarībā no apjoma.
- Prēmiju apmēra aprēķins atkarībā no plāna izpildes.
- Sūtīšanas tarifu aprēķins atkarībā no attāluma.
- Piemērotu konteineru izvēle precēm utt.
Turklāt atkarībā no situācijas var būt nepieciešama noapaļošana gan uz augšu, gan uz leju.
Ir vairāki veidi – acīmredzami un ne tik acīmredzami, kā atrisināt šādu problēmu. Apskatīsim tos secīgi.
Sākumā iedomāsimies piegādātāju, kurš dod atlaides vairumtirdzniecībā, un atlaides procentuālais daudzums ir atkarīgs no iegādāto preču daudzuma. Piemēram, pērkot vairāk par 5 gab., tiek dota 2% atlaide, bet pērkot no 20 gab. – jau 6% utt.
Kā ātri un skaisti aprēķināt atlaides procentu, ievadot iegādāto preču daudzumu?
1. metode: ligzdotie IF
Metode no sērijas “ko tur domāt – jālec!”. Ligzdoto funkciju izmantošana IF (JA) lai secīgi pārbaudītu, vai šūnas vērtība ietilpst katrā no intervāliem, un parādītu atlaidi attiecīgajam diapazonam. Bet formula šajā gadījumā var izrādīties ļoti apgrūtinoša:
Manuprāt, ir acīmredzams, ka šādas “briesmoņa lelles” atkļūdošana vai mēģinājums pēc kāda laika tai pievienot pāris jaunus nosacījumus ir jautri.
Turklāt Microsoft Excel ir ligzdošanas ierobežojums funkcijai IF – 7 reizes vecākās versijās un 64 reizes jaunākās versijās. Ko darīt, ja jums vajag vairāk?
2. metode. VLOOKUP ar intervāla skatu
Šī metode ir daudz kompaktāka. Lai aprēķinātu atlaides procentus, izmantojiet leģendāro funkciju VPR (MEKLĒT) aptuvenā meklēšanas režīmā:
kur
- B4 – preču daudzuma vērtība pirmajā darījumā, kuram meklējam atlaidi
- 4 $ G$: 8 $ – saite uz atlaižu tabulu – bez “galvenes” un ar adresēm, kas fiksētas ar $ zīmi.
- 2 — atlaižu tabulas kolonnas kārtas numurs, no kuras vēlamies iegūt atlaides vērtību
- TRUE – lūk, kur apglabāts “suns”. Ja kā pēdējais funkcijas arguments VPR precizēt MELO (FALSE) vai 0, tad funkcija meklēs stingra spēle daudzuma kolonnā (un mūsu gadījumā tas sniegs kļūdu #N/A, jo atlaižu tabulā nav vērtības 49). Bet ja tā vietā MELO rakstīt TRUE (PATIESA) vai 1, tad funkcija meklēs nevis precīzu, bet gan tuvākais mazākais vērtību un sniegs mums nepieciešamo atlaides procentuālo daļu.
Šīs metodes negatīvā puse ir nepieciešamība sakārtot atlaižu tabulu augošā secībā pēc pirmās kolonnas. Ja šādas šķirošanas nav (vai tas tiek darīts apgrieztā secībā), mūsu formula nedarbosies:
Attiecīgi šo pieeju var izmantot tikai tuvākās mazākās vērtības noteikšanai. Ja jums ir jāatrod tuvākais lielākais, jums ir jāizmanto cita pieeja.
3. metode. Tuvākā lielākā atrašana, izmantojot funkcijas INDEX un MATCH
Tagad paskatīsimies uz mūsu problēmu no citas puses. Pieņemsim, ka mēs pārdodam vairākus dažādu jaudu rūpniecisko sūkņu modeļus. Kreisajā pusē esošajā pārdošanas tabulā ir norādīta klientam nepieciešamā jauda. Mums ir jāizvēlas sūknis ar tuvāko maksimālo vai vienādu jaudu, bet ne mazāku par to, kas nepieciešams projektā.
Funkcija VLOOKUP šeit nepalīdzēs, tāpēc jums būs jāizmanto tās analogs - virkne INDEX funkciju (INDEX) un VAIRĀK IZSLĒGTS (MATCH):
Šeit funkcija MATCH ar pēdējo argumentu -1 darbojas tuvākās lielākās vērtības atrašanas režīmā, un funkcija INDEX pēc tam no blakus kolonnas izvelk mums vajadzīgo modeļa nosaukumu.
4. metode. Jauna funkcija VIEW (XLOOKUP)
Ja jums ir Office 365 versija ar visiem instalētiem atjauninājumiem, tad VLOOKUP vietā (MEKLĒT) varat izmantot tā analogu – funkciju VIEW (XLOOKUP), ko es jau detalizēti analizēju:
Šeit:
- B4 – preces daudzuma sākotnējā vērtība, kurai meklējam atlaidi
- 4 G$: 8 $ – diapazons, kurā mēs meklējam sērkociņus
- 4 $: 8 $ – rezultātu diapazons, no kura vēlaties atgriezt atlaidi
- ceturtais arguments (-1) ietver tuvākā mazākā vēlamā skaitļa meklēšanu, nevis precīzo atbilstību.
Šīs metodes priekšrocības ir tādas, ka nav jākārto atlaižu tabula un iespēja nepieciešamības gadījumā meklēt ne tikai tuvāko mazāko, bet arī tuvāko lielāko vērtību. Pēdējais arguments šajā gadījumā būs 1.
Taču diemžēl vēl ne visiem šī iespēja ir pieejama – tikai laimīgie Office 365 īpašnieki.
5. metode. Power Query
Ja vēl neesat iepazinies ar jaudīgo un pilnīgi bezmaksas Power Query pievienojumprogrammu programmai Excel, jūs esat šeit. Ja esat jau pazīstams, tad mēģināsim to izmantot, lai atrisinātu mūsu problēmu.
Vispirms veiksim sagatavošanās darbus:
- Pārveidosim mūsu avota tabulas uz dinamiskām (viedajām), izmantojot īsinājumtaustiņus Ctrl+T vai komanda Sākums — formatējiet kā tabulu (Sākums — formatēt kā tabulu).
- Skaidrības labad dosim tiem nosaukumus. Atlaides и Atlaides tab Konstruktors (Dizains).
- Ielādējiet katru no tabulām pēc kārtas programmā Power Query, izmantojot pogu No tabulas/diapazona tab Datums (Dati — no tabulas/diapazona). Jaunākajās Excel versijās šī poga ir pārdēvēta par Ar lapām (No lapas).
- Ja tabulām ir dažādi kolonnu nosaukumi ar daudzumiem, kā tas ir mūsu piemērā (“Preču daudzums” un “Daudzums no…”), tās ir jāpārdēvē programmā Power Query un jānosauc vienādi.
- Pēc tam varat atgriezties programmā Excel, Power Query redaktora logā atlasot komandu Sākums — Aizvērt un ielādēt — Aizvērt un ielādēt… (Sākums — Aizvērt&Ielādēt — Aizvērt&Ielādēt...) un tad opcija Vienkārši izveidojiet savienojumu (Izveidojiet tikai savienojumu).
- Tad sākas interesantākais. Ja ir pieredze darbā ar Power Query, tad pieņemu, ka tālākai domu līnijai vajadzētu virzīties uz šo divu tabulu sapludināšanu ar join query (merge) a la VLOOKUP, kā tas bija iepriekšējā metodē. Faktiski mums būs jāapvienojas pievienošanas režīmā, kas no pirmā acu uzmetiena nemaz nav acīmredzams. Izvēlieties Excel cilnē Dati – Saņemt datus – Apvienot pieprasījumus – Pievienot (Dati — iegūt datus — apvienot vaicājumus — pievienot) un tad mūsu galdi Atlaides и Atlaides parādītajā logā:
- Pēc noklikšķināšanas uz OK mūsu galdi tiks salīmēti vienotā veselumā – viens zem otra. Lūdzu, ņemiet vērā, ka ailes ar preču daudzumu šajās tabulās atradās viena zem otras, jo. tiem ir viens un tas pats nosaukums:
- Ja jums ir svarīga pārdošanas tabulas sākotnējā rindu secība, tad, lai pēc visām turpmākajām transformācijām jūs varētu to atjaunot, pievienojiet numurētu kolonnu mūsu tabulai, izmantojot komandu Kolonnas pievienošana — rādītāja kolonna (Pievienot kolonnu — rādītāja kolonna). Ja līniju secība jums nav svarīga, varat izlaist šo darbību.
- Tagad, izmantojot tabulas galvenē esošo nolaižamo sarakstu, kārtojiet to pēc kolonnas Daudzums Augošā secībā:
- Un galvenais triks: ar peles labo pogu noklikšķiniet uz kolonnas galvenes Atlaide izvēlēties komandu Aizpildiet – uz leju (Aizpildīt — uz leju). Iztukšojiet šūnas ar nulle automātiski aizpildītas ar iepriekšējām atlaižu vērtībām:
- Atliek atjaunot sākotnējo rindu secību, šķirojot pēc kolonnas Indekss (vēlāk varat droši izdzēst) un atbrīvoties no nevajadzīgām līnijām ar filtru nulle pēc kolonnas Darījuma kods:
- Funkcijas VLOOKUP izmantošana datu meklēšanai un uzmeklēšanai
- Izmantojot VLOOKUP (VLOOKUP), ir reģistrjutīgs
- XNUMXD VLOOKUP (VLOOKUP)