Nejnižší cena v Excelu - porovnání dodavatelů

Podrobnosti

Vytvořeno 12. 4. 2013 12:40

Napsal Daniel Mikláš

Vyhlašujete pravidelně více výběrových řízení na dodavatele zboží či služeb? Zjednodušte si dohledání nejnižší ceny a jejího dodavatele přímo v Excelu. Po použití níže uvedeného příkladu bude probíhat vše rychle a zcela automaticky.

Popis situace

Máme hypotetickou situaci: vyhlásili jsme výběrové řízení na dodání různého ovoce od různých dodavatelů. Sebrané vstupy jsme vložili do excelové tabulky a rádi bychom zjistili nejnižší cenu a dodavatele této nejnižší ceny.

Postup

Ve sloupci Minimální cena je použita základní funkce MIN, která z dané oblasti dohledá hodnotu s nejnižší cenou.

Obsah buňky I4: =MIN(NejnižšíCena[@[HONZA]:[KLÁRA]])

Ve sloupci Kdo nabízí za pomoci funkcí INDEX, POZVYHLEDAT (anglicky MATCH) a MIN zobrazíme dodavatele nejnižší ceny, který je uveden v záhlaví tabulky. Funkce POZVYHLEDAT dohledá pozici nejnižší ceny (přes funkci MIN) a tuto pozici předá funkci INDEX, která vrátí pozici příslušného dodavatele.

Jinými slovy: funkce POZVYHLEDAT zjistí pozici nejnižší ceny, funkce INDEX zjistí, kdo je dodavatelem nejnižší ceny. Vše je pro potřeby této tabulky smíšeně adresováno.

Obsah buňky K4: =INDEX($C$3:$H$3;;POZVYHLEDAT(MIN(C4:H4);C4:H4;0))

U tohoto řešení může nastat určitá nepřesnost. Pokud budou dva nebo více dodavatelů s nejnižší cenou, náš postup vždy dohledá a zpracuje pouze první nejnižší cenu a prvního dodavatele nejnižší ceny směrem z levé části tabulky. Pro ošetření této ne příliš pravděpodobné situace si vypomůžeme samostatným sloupcem JeDuplicita? a podmíněným formátováním.

Ve sloupci JeDuplicita je použita funkce COUNTIF, která zjišťuje počet výskytů nejnižší ceny v řádku. Tato funkce je vložena do podmínkové funkce KDYŽ, která v případě vyššího výsledku než 1 upozorní textem ANO na to, že v řádku jsou dva nebo více dodavatelů se stejnou nejnižší cenou.

Obsah buňky J4:

=KDYŽ(COUNTIF(NejnižšíCena[@[HONZA]:[KLÁRA]];MIN(NejnižšíCena[@[HONZA]:[KLÁRA]]))>1;"Ano";"")

Buňky ve sloupci MinimálníCena jsou navíc podmíněně naformátovány, takže když se vypočítá ANO, buňka nesoucí tuto informaci bude barevně zvýrazněna.

Na buňku C4 je dále použito podmíněné formátování typu Určit buňky k formátování pomocí vzorce, které se za pomoci smíšené adresace rozprostře do oblasti C4:I12, čímž v této oblasti zvýrazní duplicitní nejnižší ceny.

Vzorec v C4 je následující:

=A(C4=MIN($C4:$H4);COUNTIF($C4:$H4;MIN($C4:$H4))>1)

Tento praktický příklad je jeden z mnoha, které představujeme účastníkům našich MS Excel školení. V případě zájmu jste na školení MS Excel srdečně vítáni, pokud máte otázku, ptejte se níže v komentářích.