Excel

Dynamické maticové vzorce v programe Excel

Dynamic Array Formulas Excel

Dynamické polia sú najväčšou zmenou vzorcov programu Excel za posledné roky. Možno najväčšia zmena vôbec. Dôvodom je, že dynamické polia vám umožňujú jednoducho pracovať vo vzorci s viacerými hodnotami súčasne. Pre mnohých používateľov to bude prvýkrát, čo porozumejú a použijú vzorce poľa.



Toto je veľký upgrade a vítaná zmena. Dynamické polia vyriešia niektoré skutočne ťažké problémy v programe Excel a zásadne zmenia spôsob, akým sú pracovné listy navrhnuté a skonštruované.

Dostupnosť

Dynamické polia a nové funkcie uvedené nižšie sú k dispozícii iba Excel 365 . Excel 2016 a Excel 2019 neponúkajú podporu vzorcov dynamických polí. Na rozlíšenie verzií nižšie použijem „dynamický Excel“ (Excel 365) a „tradičný Excel“ (2019 alebo starší).





Nový: Video tréning Dynamic Array Formula

Nové funkcie

Ako súčasť aktualizácie dynamického poľa teraz Excel obsahuje 8 nových funkcií, ktoré priamo využívajú dynamické polia na riešenie problémov, ktoré je tradične ťažké vyriešiť pomocou bežných vzorcov. Kliknutím na odkazy nižšie zobrazíte podrobnosti a príklady pre každú funkciu:

Funkcia Účel
FILTER Filtrujte údaje a vráťte zodpovedajúce záznamy
RANDARRAY Vygenerujte pole náhodných čísel
SEKVENCIA Vygenerujte pole postupných čísel
Triediť Zoradiť rozsah podľa stĺpcov
TRIEDIŤ PODĽA Zoradiť rozsah podľa iného rozsahu alebo poľa
JEDINEČNÉ Extrahujte jedinečné hodnoty zo zoznamu alebo rozsahu
XLOOKUP Moderná náhrada za VLOOKUP
XMATCH Moderná náhrada funkcie MATCH

Video: Nové funkcie dynamického poľa v programe Excel (asi 3 minúty).



zaokrúhlenie na celé čísla v programe Excel

Poznámka: XLOOKUP a XMATCH nepatrili do pôvodnej skupiny nových funkcií dynamického poľa, ale v novom motore dynamického poľa fungujú skvele. XLOOKUP nahrádza VLOOKUP a ponúka moderný, flexibilný prístup, ktorý využíva výhody polí. XMATCH je inováciou funkcie MATCH, ktorá poskytuje nové možnosti INDEX a ZHODA vzorce.

Príklad

Predtým, ako sa dostaneme k podrobnostiam, pozrime sa na jednoduchý príklad. Nižšie používame nové JEDINEČNÁ funkcia na extrakciu jedinečných hodnôt z rozsahu B5: B15, pomocou a slobodný vzorec zadaný v E5:

 
= UNIQUE (B5:B15) // return unique values in B5:B15

JEDINEČNÝ príklad funkcie

Výsledkom je zoznam piatich jedinečných názvov miest, ktoré sú uvedené v E5: E9.

Rovnako ako všetky vzorce, aj UNIQUE sa automaticky aktualizuje pri zmene údajov. Vancouver nižšie nahradil Portland v riadku 11. Výsledok z UNIQUE teraz zahŕňa Vancouver:

JEDINEČNÝ príklad funkcie po zmene

Rozliatie - jeden vzorec, veľa hodnôt

V dynamickom programe Excel budú vzorce, ktoré vracajú viac hodnôt, ' hra “tieto hodnoty priamo do pracovného hárka. To bude hneď logickejšie pre užívateľov formulí. Je to tiež plne dynamické správanie - keď sa zdrojové údaje zmenia, rozliate výsledky sa okamžite aktualizujú.

Obdĺžnik, ktorý uzatvára hodnoty, sa nazýva „ herný rozsah '. Všimnete si, že rozsah rozliatia má špeciálne zvýraznenie. V UNIKÁTNOM príklade uvedenom vyššie je rozsah rozliatia E5: E10.

Keď sa údaje zmenia, rozsah úniku sa podľa potreby rozšíri alebo stiahne. Môžete vidieť pridané nové hodnoty alebo existujúce hodnoty zmiznúť. Týmto spôsobom je rozsah rozliatia novým druhom dynamického rozsahu.

Poznámka: Keď rozliatie zablokujú iné údaje, zobrazí sa chyba #SPILL. Akonáhle vytvoríte priestor pre rozsah rozliatia, vzorec sa automaticky rozleje.

Video: Rozliatie a rozsah rozliatia

Referencia rozsahu rozliatia

Ak chcete odkazovať na rozsah úniku, použite symbol hash (#) za prvou bunkou v rozsahu. Ak chcete napríklad odkazovať na výsledky z vyššie uvedenej funkcie UNIQUE, použite:

 
=E5# // reference UNIQUE results

Je to rovnaké ako pri odkazovaní na celý rozsah rozliatia a túto syntax uvidíte pri písaní vzorca, ktorý odkazuje na celý rozsah rozliatia.

Odkaz na rozsah rozliatia môžete vložiť priamo do iných vzorcov. Ak napríklad chcete spočítať počet miest vrátených spoločnosťou UNIQUE, môžete použiť:

 
= COUNTA (E5#) // count unique cities

Príklad odkazu na rozsah úniku dynamického poľa

Keď sa zmení rozsah rozliatia, vzorec bude odrážať najnovšie údaje.

Masívne zjednodušenie

Pridanie nových vzorcov dynamického poľa znamená, že niektoré vzorce je možné drasticky zjednodušiť. Tu je niekoľko príkladov:

  • Extrahujte a zadajte jedinečné hodnoty ( predtým | po )
  • Spočítajte jedinečné hodnoty ( predtým | po )
  • Filtrovať a extrahovať záznamy ( predtým | po )
  • Extrahovať čiastočné zhody ( predtým | po )

Sila jedného

Jednou z najsilnejších výhod prístupu „jeden vzorec, mnoho hodnôt“ je menšia závislosť absolútny alebo zmiešané referencie. Pretože vzorec dynamického poľa rozlieva výsledky do pracovného hárka, odkazy zostanú nezmenené, ale vzorec generuje správne výsledky.

Napríklad nižšie používame funkciu FILTER na extrahovanie záznamov v skupine 'A'. Do bunky F5 sa zadáva jeden vzorec:

 
= FILTER (B5:D11,B5:B11='a') // references are relative

Príklad iba dynamického poľa jeden vzorec

Všimnite si, že oba rozsahy sú odomknuté relatívne odkazy, ale vzorec funguje perfektne.

To je pre mnohých používateľov obrovská výhoda, pretože proces písania vzorcov je oveľa jednoduchší. Ďalší dobrý príklad nájdete v tabuľke násobenia nižšie.

Reťazové funkcie

Veci začnú byť skutočne zaujímavé, keď spojíte viac ako jednu funkciu dynamického poľa. Chcete zoradiť výsledky vrátené systémom UNIQUE? Ľahko. Stačí zabaliť Funkcia SORT okolo funkcie UNIQUE takto:

Príklad JEDINEČNÉHO a TRIEDIŤ spoločne

Rovnako ako predtým, keď sa zdrojové údaje zmenia, automaticky sa objavia nové jedinečné výsledky, pekne zoradené.

Natívne správanie

Je dôležité pochopiť, že správanie dynamického poľa je a pôvodný a hlboko integrovaný . Kedy akýkoľvek vzorec vráti viac výsledkov, tieto výsledky sa spoja do viacerých buniek v hárku. To zahŕňa staršie funkcie, ktoré pôvodne neboli navrhnuté na prácu s dynamickými poľami.

Napríklad v tradičnom programe Excel, ak dáme príponu LEN function do rozsah textových hodnôt, uvidíme a slobodný výsledok. V Dynamic Excel, ak dáme funkcii LEN rozsah hodnôt, uvidíme viacnásobné výsledky. Nasledujúca obrazovka zobrazuje staré správanie vľavo a nové správanie vpravo:

Funkcia LEN s poľami - stará a nová

Je to obrovská zmena, ktorá môže ovplyvniť všetky druhy vzorcov. Napríklad, Funkcia VLOOKUP je určený na načítanie jednej hodnoty z tabuľky pomocou stĺpcového indexu. Ak však v programe Dynamic Excel poskytneme VLOOKUP viac ako jeden stĺpcový index pomocou súboru konštanta poľa Páči sa ti to:

 
= VLOOKUP ('jose',F7:H10,{1,2,3},0)

VLOOKUP vráti viac stĺpcov:

Viacnásobné výsledky s funkciou VLOOKUP a dynamickými poľami

Inými slovami, aj keď VLOOKUP nebol nikdy navrhnutý tak, aby vracal viac hodnôt, teraz to môže robiť vďaka novému formulárovému nástroju v Dynamic Excel.

Všetky vzorce

Nakoniec si všimnite, že dynamické polia fungujú s všetky vzorce nie len funkcie . V nižšie uvedenom príklade bunka C5 obsahuje jeden vzorec:

ako sa zoradiť vzostupne v programe Excel
 
=B5:B14*C4:L4

Výsledok sa rozšíri do rozsahu 10 x 10, ktorý obsahuje 100 buniek:

Tabuľka násobenia dynamického poľa

Poznámka: V tradičnom Exceli môžete vidieť viac výsledkov vrátených podľa vzorca poľa, ak áno na kontrolu vzorca použite kláves F9 . Pokiaľ však nezadávate vzorec ako a viacbunkový maticový vzorec , v pracovnom hárku sa zobrazí iba jedna hodnota.

Polia sa dostávajú do hlavného prúdu

S rozšírením dynamických polí sa slovo „ pole 'sa bude zobrazovať oveľa častejšie. V skutočnosti môžete vidieť „pole“ a „rozsah“ používané takmer zameniteľne. Polia v Exceli uvidíte uzavreté v zložených zátvorkách takto:

 
{1,2,3} // horizontal array {123} // vertical array

Pole je programovací výraz, ktorý odkazuje na zoznam položiek, ktoré sa zobrazujú v konkrétnom poradí. Dôvodom, prečo sa polia vo vzorcoch programu Excel zobrazujú tak často, je to, že polia môžu dokonale vyjadrujú hodnoty v rozsahu buniek .

Video: Čo je pole?

Operácie poľa sú dôležité

Pretože vzorce Dynamického Excelu môžu ľahko pracovať s viacerými hodnotami, operácie na poli budú čoraz dôležitejšie. Termín „operácia poľa“ sa týka výrazu, ktorý na poli vykonáva logický test alebo matematickú operáciu. Nasledujúci výraz napríklad testuje, či sú hodnoty v B5: B9 rovné „ca“

 
=B5:B9='ca' // state = 'ca'

Príklad testu prevádzky poľa a

pretože v B5: B9 je 5 buniek, výsledkom je 5 hodnôt TRUE/FALSE v poli:

 
{FALSETRUEFALSETRUETRUE}

Nasledujúca operácia poľa kontroluje sumy vyššie ako 100:

 
=C5:C9>100 // amounts > 100

Príklad testu prevádzky poľa b

Posledná operácia poľa kombinuje test A a test B v jednom výraze:

 
=(B5:B9='ca')*(C5:C9>100) // state = 'ca' and amount > 100

Príklad testu činnosti poľa a a b

Poznámka: Excel počas matematickej operácie automaticky vynúti hodnoty PRAVDA a NEPRAVDA na 1 a 0.

Aby sme to vrátili späť k vzorcom dynamických polí v programe Excel, nasledujúci príklad ukazuje, ako môžeme vo funkcii FILTER použiť presne rovnakú operáciu poľa ako zahrnúť argument:

Operácia poľa s funkciou FILTER

FILTER vráti dva záznamy, kde state = 'ca' a čiastka> 100.

Ukážku nájdete na: Ako filtrovať podľa dvoch kritérií (video).

Nové a staré vzorce polí

V dynamickom Exceli nie je potrebné zadávať maticové vzorce pomocou klávesov Ctrl + Shift + Enter. Keď sa vytvorí vzorec, Excel skontroluje, či vzorec môže vrátiť viacero hodnôt. Ak je to tak, automaticky sa uloží ako vzorec dynamického poľa, ale neuvidíte kučeravé zátvorky. Nasledujúci príklad ukazuje typický vzorec poľa zadaný v programe Dynamic Excel:

Základný vzorec poľa v tradičnom programe Excel

Ak otvoríte rovnaký vzorec v tradičnom programe Excel, zobrazia sa vám zložené zátvorky:

Základný vzorec poľa v dynamickom programe Excel

Keď sa obrátime iným smerom, keď sa v programe Dynamic Excel otvorí „tradičný“ vzorec poľa, na paneli vzorcov uvidíte zložené zátvorky. Nasledujúca obrazovka napríklad zobrazuje jednoduchý vzorec poľa v tradičnom programe Excel:

Jednoduchý vzorec poľa s viditeľnými zloženými zátvorkami

Ak však vzorec znova zadáte bezo zmien, zložené zátvorky sa odstránia a vzorec vráti rovnaký výsledok:

Jednoduchý vzorec poľa so zvlnenými zátvorkami nie je viditeľný

Pointa je, že vzorce polí zadané pomocou klávesov Ctrl + Shift + Enter (CSE) stále fungujú na zachovanie kompatibility, ale v Dynamickom Exceli by ste nemali zadávať vzorce poľa pomocou CSE.

Znak @

So zavedením dynamických polí sa znak @ zobrazí vo vzorcoch častejšie. Znak @ umožňuje správanie známe ako ' implicitná križovatka '. Implicitná križovatka je logický proces, v ktorom je mnoho hodnôt redukovaných na jednu hodnotu.

V tradičnom programe Excel je implicitná križovatka tiché správanie, ktoré sa používa (v prípade potreby) na zníženie viacerých hodnôt na jeden výsledok v jednej bunke. V programe Dynamic Excel to zvyčajne nie je potrebné, pretože do pracovného hárka sa môže dostať viac výsledkov. Keď je to potrebné, implicitnú križovatku vyvoláme ručne so znakom @.

Pri otváraní tabuliek vytvorených staršou verziou Excelu sa môže zobraziť znak @ automaticky pridaný do existujúcich vzorcov, ktoré majú príponu potenciál vrátiť mnoho hodnôt. V tradičnom programe Excel sa vzorec, ktorý vracia viac hodnôt, na pracovný hárok nevysype. Znak @ vynucuje rovnaké správanie v programe Dynamic Excel, aby sa vzorec správal rovnako a vrátil rovnaký výsledok ako v pôvodnej verzii programu Excel.

Inými slovami, @ sa pridá, aby sa zabránilo staršiemu vzorcu v rozliatí viacerých výsledkov na pracovný hárok. V závislosti od vzorca možno budete môcť odstrániť znak @ a správanie vzorca sa nezmení.

Zhrnutie

  • Dynamické polia uľahčia písanie určitých vzorcov.
  • Teraz môžete pomocou vzorcov filtrovať zodpovedajúce údaje, triediť a extrahovať jedinečné hodnoty.
  • Vzorce dynamického poľa je možné reťaziť (vnoriť), napríklad na filtrovanie a triedenie.
  • Vzorce, ktoré vracajú viac ako jednu hodnotu, sa automaticky rozlejú.
  • Na zadanie vzorca poľa nie je potrebné používať Ctrl+Shift+Enter.
  • Vzorce dynamického poľa sú k dispozícii iba v Exceli 365.
Autor Dave Bruns


^