Vyhledání dodavatele s nejnižší cenou

Autor: Miroslav Lorenc

Na praktickém příkladu si vysvětlíme, jak vyhledávat v tabulce přepravních nákladů za kilometr různých přepravních společností v různých krajích

Naším úkolem je vyhodnotit, kterého přepravce v jednotlivých krajích objednat, pokud jediným kritériem výběru je nabídková cena.

Pro zjednodušení si pojmenujeme nabídkové ceny přepravců – označíme oblast buněk B2:J7 a do pole názvů napíšeme “Nabídky” + Enter.

Nejlepší cena

Do oblasti K2:K7 napíšeme vzorec, který nalezne nejnižší cenu za kilometr pro danou oblast.

• =MIN(INDEX(Nabídky;ŘÁDEK()-1;))

Do funkce MIN, která nám najde minimální ceny přepravy, vnoříme funkci INDEX.

• Jako argument pole nám poslouží oblast nabízených cen, kterou jsme pojmenovali Nabídky.
• Argument řádek bude vyplněn funkcí ŘÁDEK, která nám zjistí aktuální číslo řádku, na kterém se pole nachází. Nesmíme ale zapomenout od této hodnoty odečíst 1, neboť jeden řádek nám zabírá záhlaví tabulky.
• Argument sloupce necháme nevyplněný.

Funkce INDEX nám tedy vybrala příslušný řádek za hledaný kraj a funkce MIN v něm najde nejlepší nabídku. Výše uvedená syntaxe by šla přirozeně (a mnohem jednodušeji) nahradit vzorcem MIN(B2:J2), ale v tomto článku se věnujeme možnostem použití kombinace funkcí POZVYHLEDTA a INDEX a v další části článku na toto navážeme a rozvineme.

Nejlepší přepravce

Dále chceme do sloupce Nejlepší přepravce vypsat jméno společnosti, která podala nejlepší nabídku. Do buňky L2 napíšeme následující vzorec:

• =INDEX($B$1:$J$1;1;POZVYHLEDAT(K2;B2:J2;0))

Vzorec se skládá z těchto částí:

• První argument funkce INDEX (pole) představuje oblast s názvy společností, kterou zafixujeme pomocí klávesy F4, protože bude pro všechny výpočty stejná.
• Argument řádek bude 1, protože oblast s názvy společností má jen jeden řádek.
• Argument sloupec bude tvořit funkce POZVYHLEDAT (MATCH), která nám nalezne číslo sloupce, ve kterém se nachází nejnižší cena.

Pokud máte ve vašem Excelu dostupnou funkci XLOOKUP, pak jde použít také vzorec

• =XLOOKUP(K2;B2:J2;$B$1:$J$1)

Podmíněné formátování

Pro zpřehlednění tabulky ještě použijeme podmíněné formátování – aby cena nejlevnějšího přepravce v kraji byla vždy zvýrazněna.

Označíme oblast Nabídky, dále zvolíme příkaz Domů – Podmíněné formátování – Nové pravidlo – Určit buňky k formátování pomocí vzorce a napíšeme takový vzorec, který vrátí PRAVDA nebo NEPRAVDA podle toho, zda je číslo v daném řádku nejnižší.

Všimněte si, jak je zapsána adresa oblasti, kde hledáme minimum – tím, že ukotvíme sloupce B a J a naopak neukotvíme řádek 2 dosáhneme toho, že podmínka platí (podmínka se vyhodnocuje pro každý řádek zvlášť, ale vždy právě mezi sloupci Ba J (neposune se). Přečtete si také náš článek o smíšeném adresování v Excelu.

A takto by mohl vypadat výsledek:

.

Zdrojový soubor vč. výsledku ke stažení