V tomto videu sa pozrieme na to, ako zvýrazniť približné vyhľadanie zhody pomocou podmieneného formátovania.
Tu máme jednoduchú vyhľadávaciu tabuľku, ktorá zobrazuje náklady na materiál pre rôzne výšky a šírky. Vzorec v K8 využíva funkcie INDEX a MATCH na získanie správnej ceny na základe hodnôt šírky a výšky zadaných v K6 a K7.
Upozorňujeme, že vyhľadávanie je založené na približnej zhode. Pretože hodnoty sú vo vzostupnom poradí, MATCH kontroluje hodnoty, kým sa nedosiahne väčšia hodnota, a potom ustúpi a vráti predchádzajúcu pozíciu.
Vytvorme pravidlo podmieneného formátovania, aby sme zvýraznili zhodný riadok a stĺpec.
Ako vždy pri zložitejšom podmienenom formátovaní odporúčam najskôr pracovať s fiktívnymi vzorcami a potom preniesť pracovný vzorec priamo do pravidla podmieneného formátovania. Týmto spôsobom môžete pri ladení vzorca používať všetky nástroje programu Excel, čo vám ušetrí veľa času.
Najprv nastavím vzorec pre šírku. Musíme vrátiť TRUE pre každú bunku v riadku 7, kde je zhoda šírky 200.
To znamená, že náš vzorec začíname $ B5 = a musíme stĺpec uzamknúť.
= $ B5 =
Teraz nemôžeme hľadať 275 v stĺpci šírky, pretože tam nie je. Namiesto toho potrebujeme približnú zhodu, ktorá nájde 200, rovnako ako náš vzorec vyhľadávania.
Najjednoduchšie je použiť funkciu LOOKUP. LOOKUP automaticky vykoná približnú zhodu a namiesto vrátenia pozície ako MATCH vráti LOOKUP skutočnú hodnotu zhody. Môžeme teda napísať:
$ B5 = HĽADANIE ($ K $ 6, $ B $ 6: $ B $ 12)
S našou vstupnou šírkou pre vyhľadávaciu hodnotu a so všetkými šírkami v tabuľke pre vektor výsledku.
Ak používam klávesu F9, uvidíte hodnotu, ktorá sa vracia.
Teraz, keď zadám vzorec do tabuľky, dostaneme TRUE pre každú bunku v riadku s šírkou 200.
Teraz musíme rozšíriť vzorec tak, aby zodpovedal stĺpcu výšky. Aby som to urobil, pridám funkciu OR a potom druhý vzorec, ktorý zodpovedá výške.
Vzorec začneme rovnakým spôsobom, ale tentoraz musíme riadok uzamknúť:
= 5 USD
Potom znova použijeme funkciu LOOKUP s výškou ako hodnotou výsledku a so všetkými výškami v tabuľke ako vektorom výsledku.
= ALEBO ($ B5 = HĽADAŤ ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = HĽADAŤ ($ K $ 7, $ C $ 5: $ H $ 5))
Keď skopírujem vzorec cez tabuľku, dostaneme TRUE pre každú bunku v zhodnom stĺpci a každú bunku v zhodnom riadku - presne to, čo potrebujeme na podmienené formátovanie.
Môžem len presne skopírovať vzorec v ľavej hornej bunke a vytvoriť nové pravidlo.
Teraz, keď zmením šírku alebo výšku, zvýraznenie funguje podľa očakávania.
Nakoniec, ak chcete iba zvýrazniť samotnú vyhľadávaciu hodnotu, je to jednoduchá zmena. Stačí upraviť vzorec a nahradiť funkciu OR funkciou AND.
= AND ($ B5 = HĽADAŤ ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = HĽADAŤ ($ K $ 7, $ C $ 5: $ H $ 5))
Samozrejme
Podmienené formátovanieSúvisiace skratky
Zadajte rovnaké údaje do viacerých buniek Ctrl + Enter ⌃ + Return Zobrazte dialógové okno Prilepiť špeciálne Ctrl + Alt + V ⌘ + ⌃ + V Prepnite absolútne a relatívne odkazy F4 ⌘ + T
