Vyhľadanie najbližšieho čísla – Zdravé jedlo v mojom okolí

Obsah

V praxi sa veľmi často vyskytujú prípady, keď vy a ja potrebujeme nájsť najbližšiu hodnotu v množine (tabuľke) vo vzťahu k danému číslu. Môže to byť napríklad:

Výpočet zľavy v závislosti od objemu.
Výpočet výšky odmien v závislosti od realizácie plánu.
Výpočet ceny dopravy v závislosti od vzdialenosti.
Výber vhodných kontajnerov na tovar a pod.

Okrem toho môže byť potrebné zaokrúhľovanie nahor aj nadol v závislosti od situácie.

Existuje niekoľko spôsobov – zrejmých a nie až tak zrejmých – ako vyriešiť takýto problém. Pozrime sa na ne postupne.

Na začiatok si predstavme dodávateľa, ktorý dáva zľavy vo veľkoobchode a percento zľavy závisí od množstva nakúpeného tovaru. Napríklad pri nákupe nad 5 kusov zľava 2% a pri nákupe od 20 kusov už 6% atď.

Ako rýchlo a krásne vypočítať percento zľavy pri zadávaní množstva nakupovaného tovaru?

Metóda 1: Vnorené IF

Metóda zo série „na čo myslieť – treba skákať!“. Používanie vnorených funkcií IF (AK) na sekvenčnú kontrolu, či hodnota bunky spadá do každého z intervalov, a zobrazenie zľavy pre príslušný rozsah. Vzorec sa však v tomto prípade môže ukázať ako veľmi ťažkopádny:

Nájdenie najbližšieho čísla

Myslím, že je zrejmé, že ladenie takejto „bábiky monštra“ alebo pokus o pridanie niekoľkých nových podmienok po určitom čase je zábavné.

Okrem toho má Microsoft Excel limit vnorenia pre funkciu IF – 7-krát v starších verziách a 64-krát v novších verziách. Čo ak potrebujete viac?

Metóda 2. VLOOKUP s intervalovým zobrazením

Táto metóda je oveľa kompaktnejšia. Na výpočet percenta zľavy použite legendárnu funkciu VPR (VLOOKUP) v režime približného vyhľadávania:

Nájdenie najbližšieho čísla

kde

B4 – hodnota množstva tovaru v prvej transakcii, na ktorú hľadáme zľavu
$ G $ 4: $ H $ 8 – odkaz na tabuľku zliav – bez „hlavičky“ a s adresami zafixovanými znakom $.
2 — poradové číslo stĺpca v tabuľke zliav, z ktorého chceme získať hodnotu zľavy
TRUE – tu je zakopaný „pes“. Ak ako posledný argument funkcie VPR špecifikovať KLAMEŤ (NEPRAVDA) alebo 0, potom funkcia vyhľadá prísny zápas v stĺpci množstvo (a v našom prípade zobrazí chybu #N/A, pretože v tabuľke zliav nie je žiadna hodnota 49). Ale ak namiesto toho KLAMEŤ písať TRUE (PRAVDA) alebo 1, potom funkcia nebude hľadať presné, ale najbližší najmenší hodnotu a poskytne nám percento zľavy, ktoré potrebujeme.

Nevýhodou tejto metódy je nutnosť triediť tabuľku zliav vzostupne podľa prvého stĺpca. Ak takéto triedenie neexistuje (alebo sa vykonáva v opačnom poradí), náš vzorec nebude fungovať:

Nájdenie najbližšieho čísla

V súlade s tým možno tento prístup použiť iba na nájdenie najbližšej najmenšej hodnoty. Ak potrebujete nájsť najbližší najväčší, musíte použiť iný prístup.

Metóda 3. Nájdenie najbližšieho najväčšieho pomocou funkcií INDEX a MATCH

Teraz sa pozrime na náš problém z druhej strany. Predpokladajme, že predávame niekoľko modelov priemyselných čerpadiel rôznych výkonov. Predajná tabuľka vľavo zobrazuje výkon požadovaný zákazníkom. Musíme vybrať čerpadlo s najbližším maximálnym alebo rovnakým výkonom, ale nie menej, ako vyžaduje projekt.

Funkcia VLOOKUP tu nepomôže, takže budete musieť použiť jej analóg – množstvo funkcií INDEX (INDEX) a VIAC EXPOZÍCIE (ZÁPAS):

Nájdenie najbližšieho čísla

Tu funkcia MATCH s posledným argumentom -1 funguje v režime hľadania najbližšej najväčšej hodnoty a funkcia INDEX potom vytiahne zo susedného stĺpca názov modelu, ktorý potrebujeme.

Metóda 4. Nová funkcia VIEW (XLOOKUP)

Ak máte verziu Office 365 so všetkými nainštalovanými aktualizáciami, tak namiesto VLOOKUP (VLOOKUP) môžete použiť jeho analóg – funkciu VIEW (XLOOKUP), ktoré som už podrobne analyzoval:

Nájdenie najbližšieho čísla

Tu:

B4 – počiatočná hodnota množstva produktu, na ktorý hľadáme zľavu
$ G $ 4: $ G $ 8 – rozsah, kde hľadáme zhody
$ H $ 4: $ H $ 8 – rozsah výsledkov, z ktorých chcete vrátiť zľavu
štvrtý argument (-1) zahŕňa hľadanie najbližšieho najmenšieho čísla, ktoré chceme namiesto presnej zhody.

Výhodou tejto metódy je, že nie je potrebné triediť tabuľku zliav a možnosť vyhľadať v prípade potreby nielen najbližšiu najmenšiu, ale aj najbližšiu najväčšiu hodnotu. Posledný argument v tomto prípade bude 1.

Túto funkciu však, žiaľ, zatiaľ nemajú všetci – iba šťastní majitelia Office 365.

Metóda 5. Power Query

Ak ešte nepoznáte výkonný a úplne bezplatný doplnok Power Query pre Excel, potom ste tu. Ak ste už oboznámení, skúsme to použiť na vyriešenie nášho problému.

Najprv urobme nejaké prípravné práce:

Skonvertujme naše zdrojové tabuľky na dynamické (inteligentné) pomocou klávesovej skratky ctrl+T alebo tím Domov – Formátovať ako tabuľku (Domov – Formátovať ako tabuľku).
Pre prehľadnosť im dajme mená. Predaj и zľavy pútko staviteľ (Dizajn).
Načítajte postupne každú z tabuliek do Power Query pomocou tlačidla Z tabuľky/rozsahu pútko dátum (Údaje – z tabuľky/rozsahu). V posledných verziách Excelu bolo toto tlačidlo premenované na S listami (Z listu).
Ak majú tabuľky rôzne názvy stĺpcov s množstvami, ako v našom príklade („Množstvo tovaru“ a „Množstvo od…“), musia byť v Power Query premenované a pomenované rovnako.
Potom sa môžete vrátiť späť do Excelu výberom príkazu v okne editora Power Query Domov — Zavrieť a načítať — Zavrieť a načítať v… (Domov — Zavrieť&Načítať — Zavrieť&Načítať do...) a potom možnosť Stačí vytvoriť spojenie (Iba vytvoriť spojenie).
Potom začína to najzaujímavejšie. Ak máte skúsenosti s Power Query, tak predpokladám, že ďalší myšlienkový smer by mal smerovať k spojeniu týchto dvoch tabuliek so spojovacím dotazom (merge) a la VLOOKUP, ako to bolo v prípade predchádzajúcej metódy. V skutočnosti sa budeme musieť zlúčiť v režime pridávania, čo nie je na prvý pohľad vôbec zrejmé. Vyberte na karte Excel Údaje – Získať údaje – Kombinovať požiadavky – Pridať (Údaje – Získať údaje – Kombinovať dopyty – Pripojiť) a potom naše stoly Predaj и zľavy v zobrazenom okne:
Po kliknutí na OK naše stoly budú zlepené do jedného celku – pod sebou. Upozorňujeme, že stĺpce s množstvom tovaru v týchto tabuľkách zapadali pod seba, pretože. majú rovnaké meno:
Ak je pre vás dôležitá pôvodná postupnosť riadkov v tabuľke predajov, tak aby ste ju po všetkých následných transformáciách mohli obnoviť, pridajte do našej tabuľky očíslovaný stĺpec pomocou príkazu Pridanie stĺpca – Indexový stĺpec (Pridať stĺpec — Indexový stĺpec). Ak vám na poradí riadkov nezáleží, môžete tento krok preskočiť.
Teraz pomocou rozbaľovacieho zoznamu v záhlaví tabuľky zoraďte tabuľku podľa stĺpca Množstvo Vzostupne:
A hlavný trik: kliknite pravým tlačidlom myši na hlavičku stĺpca Zľava vybrať tím Vyplniť – nadol (Vyplniť – nadol). Prázdne bunky s null automaticky vyplnené predchádzajúcimi hodnotami zliav:
Zostáva obnoviť pôvodnú postupnosť riadkov triedením podľa stĺpca index (neskôr ho môžete pokojne vymazať) a zbavte sa zbytočných čiar pomocou filtra null podľa stĺpca Kód transakcie: