Gjetja e numrit më të afërt

Përmbajtje

Në praktikë, shumë shpesh ka raste kur ju dhe unë duhet të gjejmë vlerën më të afërt në një grup (tabelë) në lidhje me një numër të caktuar. Mund të jetë, për shembull:

Llogaritja e zbritjes në varësi të vëllimit.
Llogaritja e shumës së bonuseve në varësi të zbatimit të planit.
Llogaritja e tarifave të transportit në varësi të distancës.
Përzgjedhja e kontejnerëve të përshtatshëm për mallra etj.

Për më tepër, mund të kërkohet rrumbullakimi lart dhe poshtë, në varësi të situatës.

Ka disa mënyra - të dukshme dhe jo aq të dukshme - për të zgjidhur një problem të tillë. Le t'i shikojmë ato në mënyrë sekuenciale.

Për të filluar, le të imagjinojmë një furnizues që jep zbritje në shitje me shumicë, dhe përqindja e zbritjes varet nga sasia e mallrave të blera. Për shembull, kur blini më shumë se 5 copë, jepet një zbritje prej 2%, dhe kur blini nga 20 copë - tashmë 6%, etj.

Si të llogarisni shpejt dhe bukur përqindjen e zbritjes kur futni sasinë e mallit të blerë?

Metoda 1: IF të mbivendosur

Një metodë nga seria "çfarë ka për të menduar - ju duhet të hidheni!". Përdorimi i funksioneve të mbivendosur IF (NESE) për të kontrolluar në mënyrë sekuenciale nëse vlera e qelizës bie në secilin prej intervaleve dhe për të shfaqur një zbritje për diapazonin përkatës. Por formula në këtë rast mund të rezultojë shumë e rëndë:

Gjetja e numrit më të afërt

Mendoj se është e qartë se korrigjimi i një "kukulle përbindësh" të tillë ose përpjekja për t'i shtuar disa kushte të reja pas njëfarë kohe është argëtuese.

Përveç kësaj, Microsoft Excel ka një kufi të foleve për funksionin IF - 7 herë në versionet e vjetra dhe 64 herë në versionet më të reja. Po sikur të keni nevojë për më shumë?

Metoda 2. VLOOKUP me pamje intervali

Kjo metodë është shumë më kompakte. Për të llogaritur përqindjen e zbritjes, përdorni funksionin legjendar VPR (VLOOKUP) në modalitetin e përafërt të kërkimit:

Gjetja e numrit më të afërt

B4 – vlera e sasisë së mallit në transaksionin e parë për të cilin kërkojmë zbritje
$4: $H $8 – një lidhje me tabelën e zbritjeve – pa “header” dhe me adresat e fiksuara me shenjën $.
2 — numri rendor i kolonës në tabelën e zbritjes nga e cila duam të marrim vlerën e zbritjes
TRUE – këtu është varrosur “qeni”. Nëse si argumenti i fundit i funksionit VPR specifikoj GJENJE (I RREMË) ose 0, atëherë funksioni do të kërkojë ndeshje strikte në kolonën e sasisë (dhe në rastin tonë do të japë një gabim #N/A, pasi nuk ka vlerën 49 në tabelën e zbritjes). Por nëse në vend të kësaj GJENJE shkruaj TRUE (E VERTETE) ose 1, atëherë funksioni do të kërkojë jo të saktë, por më e afërta më e vogla vlerë dhe do të na japë përqindjen e zbritjes që na nevojitet.

Ana negative e kësaj metode është nevoja për të renditur tabelën e zbritjeve në rend rritës sipas kolonës së parë. Nëse nuk ka një renditje të tillë (ose bëhet në mënyrë të kundërt), atëherë formula jonë nuk do të funksionojë:

Gjetja e numrit më të afërt

Prandaj, kjo qasje mund të përdoret vetëm për të gjetur vlerën më të vogël më të afërt. Nëse keni nevojë të gjeni më të madhin më të afërt, atëherë duhet të përdorni një qasje tjetër.

Metoda 3. Gjetja më e madhe më e afërt duke përdorur funksionet INDEX dhe MATCH

Tani le ta shohim problemin tonë nga ana tjetër. Supozoni se ne shesim disa modele të pompave industriale të kapaciteteve të ndryshme. Tabela e shitjeve në të majtë tregon fuqinë e kërkuar nga klienti. Ne duhet të zgjedhim një pompë me fuqinë maksimale më të afërt ose të barabartë, por jo më pak se ajo që kërkohet nga projekti.

Funksioni VLOOKUP nuk do të ndihmojë këtu, kështu që do t'ju duhet të përdorni analogun e tij - një mori funksionesh INDEX (INDEX) dhe MË TË EKSPOZUARA (ndeshje):

Gjetja e numrit më të afërt

Këtu, funksioni MATCH me argumentin e fundit -1 funksionon në mënyrën e gjetjes së vlerës më të madhe më të afërt, dhe funksioni INDEX më pas nxjerr emrin e modelit që na nevojitet nga kolona ngjitur.

Metoda 4. Funksioni i ri VIEW (XLOOKUP)

Nëse keni një version të Office 365 me të gjitha përditësimet e instaluara, atëherë në vend të VLOOKUP (VLOOKUP) mund të përdorni analogun e tij - funksionin VIEW (XLOOKUP), të cilën tashmë e kam analizuar në detaje:

Gjetja e numrit më të afërt

këtu:

B4 – vlera fillestare e sasisë së produktit për të cilin kërkojmë zbritje
$4 G$: $8 G$ – diapazoni ku ne kërkojmë ndeshje
$4 H $: $H $8 – gamën e rezultateve nga të cilat dëshironi të ktheni zbritjen
argumenti i katërt (-1) përfshin kërkimin për numrin më të vogël më të afërt që duam në vend të një përputhje të saktë.

Përparësitë e kësaj metode janë se nuk ka nevojë të renditni tabelën e zbritjeve dhe aftësinë për të kërkuar, nëse është e nevojshme, jo vetëm vlerën më të vogël më të afërt, por edhe vlerën më të madhe më të afërt. Argumenti i fundit në këtë rast do të jetë 1.

Por, për fat të keq, jo të gjithë e kanë ende këtë veçori - vetëm pronarë të lumtur të Office 365.

Metoda 5. Query Power

Nëse nuk jeni ende të njohur me shtesën e fuqishme dhe plotësisht falas të Power Query për Excel, atëherë jeni këtu. Nëse jeni tashmë të njohur, atëherë le të përpiqemi ta përdorim atë për të zgjidhur problemin tonë.

Le të bëjmë fillimisht disa punë përgatitore:

Le t'i konvertojmë tabelat tona burimore në dinamike (të zgjuara) duke përdorur një shkurtore të tastierës Ctrl+T ose ekipi Faqja kryesore – Formatoni si tabelë (Shtëpia - Formatoni si tabelë).
Për qartësi, le t'u japim emra. Shitjet и Zbritje tab konstruktor (Dizajn).
Ngarkoni secilën nga tabelat me radhë në Power Query duke përdorur butonin Nga tabela/gama tab Data (Të dhënat - Nga tabela/vargu). Në versionet e fundit të Excel, ky buton është riemërtuar në Me gjethe (Nga fleta).
Nëse tabelat kanë emra të ndryshëm kolonash me sasi, si në shembullin tonë (“Sasia e mallrave” dhe “Sasia nga…”), atëherë ato duhet të riemërohen në Power Query dhe të emërtohen njësoj.
Pas kësaj, mund të ktheheni në Excel duke zgjedhur komandën në dritaren e redaktuesit të Power Query Faqja kryesore — Mbyll dhe ngarko — Mbylle dhe ngarko në… (Shtëpia - Mbyll&Ngarko - Mbylle&Ngarko për…) dhe më pas opsioni Thjesht krijoni një lidhje (Vetëm krijoni lidhje).
Pastaj fillon më interesante. Nëse keni përvojë në Power Query, atëherë supozoj se linja e mëtejshme e mendimit duhet të jetë në drejtim të bashkimit të këtyre dy tabelave me një pyetje bashkimi (bashkimi) a la VLOOKUP, siç ishte rasti në metodën e mëparshme. Në fakt, do të na duhet të bashkohemi në modalitetin e shtimit, i cili nuk është aspak i dukshëm në shikim të parë. Zgjidhni në skedën Excel Të dhëna – Merrni të dhëna – Kombinoni kërkesat – Shto (Të dhënat - Merr të dhëna - Kombino pyetjet - Shtoj) dhe pastaj tavolinat tona Shitjet и Zbritje në dritaren që shfaqet:
Pas klikimit OK tavolinat tona do të ngjiten në një tërësi të vetme - njëra nën tjetrën. Ju lutemi vini re se kolonat me sasinë e mallrave në këto tabela binin njëra nën tjetrën, sepse. kanë të njëjtin emër:
Nëse sekuenca origjinale e rreshtave në tabelën e shitjeve është e rëndësishme për ju, atëherë në mënyrë që pas të gjitha transformimeve të mëvonshme të mund ta rivendosni atë, shtoni një kolonë të numëruar në tabelën tonë duke përdorur komandën Shtimi i një kolone - kolona e indeksit (Shto kolonë - kolonë indeksi). Nëse sekuenca e rreshtave nuk ka rëndësi për ju, atëherë mund ta kaloni këtë hap.
Tani, duke përdorur listën rënëse në kokën e tabelës, renditeni atë sipas kolonës sasi Në ngjitje:
Dhe truku kryesor: kliko me të djathtën në kokën e kolonës Zbritje zgjidhni një ekip Mbush – Poshtë (Plotëso - Poshtë). Qelizat e zbrazëta me null plotësohet automatikisht me vlerat e mëparshme të zbritjes:
Mbetet për të rivendosur sekuencën origjinale të rreshtave duke renditur sipas kolonës indeks (mund ta fshini me siguri më vonë) dhe hiqni linjat e panevojshme me një filtër null sipas kolonës Kodi i transaksionit: