V tomto videu se podíváme na to, jak zvýraznit přibližné vyhledávání shod pomocí podmíněného formátování.
Zde máme jednoduchou vyhledávací tabulku, která ukazuje náklady na materiál pro různé výšky a šířky. Vzorec v K8 používá funkce INDEX a MATCH k načtení správné ceny na základě hodnot šířky a výšky zadaných v K6 a K7.
Všimněte si, že vyhledávání je založeno na přibližné shodě. Vzhledem k tomu, že hodnoty jsou ve vzestupném pořadí, MATCH kontroluje hodnoty, dokud není dosaženo větší hodnoty, a poté ustoupí a vrátí předchozí pozici.
Vytvořme pravidlo podmíněného formátování, které zvýrazní uzavřený řádek a sloupec.
Jako vždy u složitějšího podmíněného formátování doporučuji nejprve pracovat s fiktivními vzorci a poté přenést pracovní vzorec přímo do pravidla podmíněného formátování. Tímto způsobem můžete při ladění vzorce použít všechny nástroje aplikace Excel, což vám ušetří spoustu času.
Nejprve nastavím vzorec pro šířku. Musíme vrátit TRUE pro každou buňku v řádku 7, kde je shodná šířka 200.
To znamená, že začneme náš vzorec s $ B5 = a musíme sloupec uzamknout.
= $ B5 =
Nyní nemůžeme hledat 275 ve sloupci šířky, protože tam není. Místo toho potřebujeme přibližnou shodu, která najde 200, stejně jako náš vzorec vyhledávání.
Nejjednodušší způsob je použít funkci VYHLEDAT. LOOKUP automaticky provede přibližnou shodu a místo vrácení pozice jako MATCH vrátí LOOKUP skutečnou hodnotu shody. Můžeme tedy napsat:
$ B5 = VYHLEDÁVÁNÍ ($ K $ 6, $ B $ 6: $ B $ 12)
S naší vstupní šířkou pro vyhledávací hodnotu a všemi šířkami v tabulce pro vektor výsledků.
Mám-li použít F9, můžete vidět hodnotu LOOKUP vrací.
Teď, když zadám vzorec v tabulce, dostaneme TRUE pro každou buňku v řádku 200 šířky.
Nyní musíme rozšířit vzorec tak, aby odpovídal sloupci výšky. K tomu přidám funkci OR a poté druhý vzorec odpovídající výšce.
Vzorec začneme stejným způsobem, ale tentokrát musíme řádek uzamknout:
= 5 $
Pak použijeme funkci LOOKUP znovu s výškou pro vyhledávací hodnotu a a všechny výšky v tabulce jako vektor výsledku.
= NEBO ($ B5 = VYHLEDÁVÁNÍ ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = VYHLEDÁVÁNÍ ($ K $ 7, $ C $ 5: $ H $ 5))
Když zkopíruji vzorec přes tabulku, dostaneme TRUE pro každou buňku ve shodném sloupci a každou buňku ve shodném řádku - přesně to, co potřebujeme pro podmíněné formátování.
Můžu přesně zkopírovat vzorec v levé horní buňce přesně a vytvořit nové pravidlo.
Nyní, když změním šířku nebo výšku, zvýraznění funguje podle očekávání.
Nakonec, pokud chcete pouze zvýraznit samotnou vyhledávací hodnotu, je to jednoduchá změna. Stačí upravit vzorec a nahradit funkci OR funkcí AND.
= AND ($ B5 = VYHLEDÁVÁNÍ ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = VYHLEDÁVÁNÍ ($ K $ 7, $ C $ 5: $ H $ 5))
Kurs
Podmíněné formátováníSouvisející zkratky
Zadejte stejná data do více buněk Ctrl + Enter ⌃ + Return Zobrazit dialogové okno Vložit jinak Ctrl + Alt + V ⌘ + ⌃ + V Přepnout absolutní a relativní odkazy F4 ⌘ + T
