vzorce Office 365/Excel 2016+

  • 15 min

V tejto lekcii predstavíme tri nové vzorce, ktoré nájdete vo Office 365/Excel 2016+ s názvom XLOOKUP(), FILTER() a LET(). Ďalším spôsobom, ako sa dozvedieť o nových funkciách a funkciách, ktoré boli pridané do aplikácie, je pripojiť sa k programu Microsoft Office Insiders Program (na konci tohto modulu nájdete prepojenie v sekcii odkazov).

XLOOKUP()

XLOOKUP() je nová výkonnejšia verzia platformy VLOOKUP(). Je to jednoduchšie, rýchlejšie a flexibilnejšie.

Prečo je xlookup() optimálnejší ako funkcia VLOOKUP(), sú nasledujúce:

  • Vyhľadávacie stĺpce a riadky kombinujú VLOOKUP() a HLOOKUP() na komplexné vyhľadávanie.

  • Stĺpce vyhľadávania na ľavej strane nahraďte vzory INDEX() MATCH(), čo vám umožní použiť kombináciu, ktorá najlepšie funguje vo vašom vyhľadávaní.

  • Vzorec je robustnejší v tom, že sa po pridaní alebo odstránení stĺpcov "nepreruší".

XLOOKUP() obsahuje syntax s tromi požadovanými parametrami. Funkcia predvolene vykoná presnú zhodu.

Snímka obrazovky riadka vzorcov v Exceli so syntaxou funkcie XLOOKUP().

XLOOKUP() má nasledovné funkcie:

  • Vráti hodnotu z daného stĺpca na základe hodnoty v inom stĺpci

  • Vráti inú hodnotu, ak sa nenájde žiadny výsledok

  • Vyhľadávanie zhora alebo zdola

XLOOKUP() má šesť parametrov, pričom posledné tri sú voliteľné parametre:

  • _ Vyhľadávaná hodnota – parameter použitý na definovanie hodnoty, ktorú chcete vyhľadať.

  • "vyhľadávacie_pole" – parameter Poľa slúžiaci na určenie stĺpca, v ktorom sa má vyhľadať hodnota.

  • "return_array" – parameter Poľa, ktorý sa používa na definovanie stĺpca na vrátenie hodnoty.

  • Ak sa nenájde_žiadna zhoda, vráťte túto voliteľnú hodnotu._

  • Režim zhody_ – voliteľný parameter na určenie presnej zhody, prvé nad/pod alebo zástupné vyhľadávanie.

  • Režim vyhľadávania_ – pomocou tohto voliteľného parametra zadajte vyhľadávanie zhora alebo zdola.

Snímka obrazovky s príkladmi XLookup().

V predchádzajúcom príklade s množinou údajov si všimnite vzorec XLOOKUP() vpravo v čiernej skrinke, ktorá zobrazuje vrátené výsledky. Tri príklady odpovedajú na nasledujúce otázky:

  • Nájsť produkt podľa ID – vzorec preukazujúci nájdenie Produktu pre ID produktu = 109, kde sa výsledky produktu nachádzajú v stĺpci napravo od stĺpca ID produktu.

  • Find City by ZIP – príklad vzorca znázorňuje nájdenie mesta pre ZIP = 21658, ktorých výsledky sú umiestnené v stĺpci naľavo od stĺpca ZIP.

  • Vyhľadanie posledného produktu podľa mesta – tento vzorec znázorňuje použitie voliteľných parametrov : Vrátia sa žiadne výsledky, ak sa nenašli žiadne výsledky, presná zhoda a -1 označuje, že sa má vyhľadávať od dolnej časti do hornej časti tabuľky údajov.

FILTER()

Filter() je nová funkcia poľa. Pridaním vzorca do jednej bunky sa vráti podmnožina tabuľky a ostatné hodnoty sa vylievajú do ostatných buniek vo výsledku. Funkcia FILTER() vráti riadky údajov a umožňuje viacero podmienok pomocou a/alebo logiky .

Filter() má nasledujúce funkcie:

  • Vráti viacero výsledkov zhody pre jednu alebo viacero vyhľadávacích hodnôt

  • Filtruje údaje bez potreby [obnovenie]{.underline}

  • možno vnoriť do iných funkcií Excelu,

Nasledujúce podrobnosti vysvetľujú tri parametre, ktoré sú súčasťou funkcie FILTER():

  • 'pole' – parameter používaný na určenie rozsahu stĺpcov a riadkov, ktoré sa majú filtrovať.

  • include – parameter použitý na poskytnutie kritérií pravidiel filtrovania.

  • "ak_je prázdna" – voliteľná hodnota parametra, ktorá sa má vrátiť, ak žiadne riadky nespĺňajú podmienky.

Snímka obrazovky jedného príkladu s funkciou Filter().

V predchádzajúcom príklade množiny údajov sa vzorec FILTER() zobrazuje v čiernej skrinke s vrátenými výsledkami. Všimnite si, že namiesto rozsahu používa tabuľku. Tabuľku odporúčame používať vždy, keď je to možné. Predchádzajúci príklad filtruje tabuľku SalesTable, kde Region = West, a vráti všetky zhodné riadky vo výsledku.

Snímka obrazovky s viacerými príkladmi s funkciou Filter().

Tento príklad používa rovnakú množinu údajov, ale použije tri filtre v tabuľke. Vzorec vyfiltruje tabuľku podľa nasledujúcich kritérií (na zahrnutie riadka musia byť splnené všetky kritériá):

  • Produkt = Palma UM-01

  • Region = West

  • Výnos = väčšie ako 1 215,00 USD

Vzorec používa funkciu vynásobenia, pretože logické porovnanie bude mať za následok nulu (0) pre hodnotu False alebo jedno (1) pre hodnotu True. Ak sú všetky podmienky TRUE, potom 1 * 1 * 1 = 1. Ak je však akákoľvek podmienka nula (0) alebo nepravda, potom je celá logika false.

Hviezdička (*) sa používa pre podmienky AND a znamienko plus (+) sa používa pre podmienky OR .

LET()

Funkcia LET() ponúka značnú flexibilitu pri zložitých výpočtoch a poskytuje jednoduchší spôsob na trávenie rôznych častí vzorca. Spája schopnosť ukladať výpočty a hodnoty, ktoré používajú premenné, so syntaxou natívneho vzorca v Exceli.

Diagram syntaxe funkcie LET().

Premenné sa používajú na priradenie názvu k hodnote alebo výpočtu. Tieto premenné sa používajú na vyvolanie syntaxe bez toho, aby bolo potrebné vzorec opakovane prepisovať. Vo funkcii môžete definovať až 126 rôznych premenných, ale minimálne musíte mať tri súčasti (premennú, hodnotu premennej, výpočet). Vo funkcii LET() môžete využiť aj ďalšie funkcie poľa, ako napríklad FILTER(). Nasledujúci príklad vychádza z príkladu funkcie FILTER() zo skoršej, ale teraz s priradenými premennými.

Snímka obrazovky príkladu LET().

Na predchádzajúcej snímke obrazovky sú čísla jeden až štyri premenné a definície. Posledným príkazom je výpočet, ktorý používa premenné.

  • Rozsah stĺpcov ProductRange = Product

  • Product = Product to filter on (Produkt = Produkt, v ktorý sa má filtrovať)

  • Rozsah stĺpcov RegionRange = Region

  • Region = Region (Oblasť), kde sa má filtrovať

  • Filter = filtrovanie v tabuľke pre produkty a oblasti