Excel

Formule dinamičnih nizov v Excelu

Dynamic Array Formulas Excel

Dinamični nizi so največja sprememba Excelovih formul v zadnjih letih. Mogoče največja sprememba doslej. To je zato, ker vam dinamični nizi omogočajo enostavno delo z več vrednostmi hkrati v formuli. Za mnoge uporabnike bo prvič, da bodo razumeli in uporabili matrične formule.

To je velika nadgradnja in dobrodošla sprememba. Dinamični nizi bodo v Excelu rešili nekatere res težke težave in temeljito spremenili način oblikovanja in izdelave delovnih listov.



Razpoložljivost

Na voljo so samo dinamični nizi in nove funkcije spodaj Excel 365 . Excel 2016 in Excel 2019 ne ponujata podpore za dinamične matrične formule. Za udobje bom za razlikovanje spodnjih različic uporabil 'Dynamic Excel' (Excel 365) in 'Traditional Excel' (2019 ali starejši).



Novo: Dynamic Array Formula video trening

Nove funkcije

Kot del posodobitve dinamičnega polja Excel zdaj vključuje 8 novih funkcij, ki neposredno uporabljajo dinamične nize za reševanje težav, ki jih je tradicionalno težko rešiti s konvencionalnimi formulami. Za podrobnosti in primere posamezne funkcije kliknite spodnje povezave:

Funkcija Namen
FILTRIRAJ Filtrirajte podatke in vrnite ujemajoče se zapise
RANDARRAY Ustvari niz naključnih števil
ZAPOREDJE Ustvari niz zaporednih števil
SORT Razvrsti obseg po stolpcu
RAZVRSTI PO Razvrsti obseg po drugem obsegu ali matriki
ENOTNA Izvlecite edinstvene vrednosti s seznama ali obsega
XLOOKUP Sodobna zamenjava za VLOOKUP
XMATCH Sodobna zamenjava funkcije MATCH

Video: Nove funkcije dinamičnega polja v Excelu (približno 3 minute).



excel formula za prihodnjo vrednost naložbe

Opomba: XLOOKUP in XMATCH niso bili v prvotni skupini novih funkcij dinamičnega polja, vendar se odlično obnesejo v novem mehanizmu dinamičnega polja. XLOOKUP nadomešča VLOOKUP in ponuja sodoben, prilagodljiv pristop, ki izkorišča polja. XMATCH je nadgradnja funkcije MATCH, ki ponuja nove zmogljivosti za KAZALO in UTEK formule.

Primer

Preden se spustimo v podrobnosti, si oglejmo preprost primer. Spodaj uporabljamo novo UNIQUE funkcija za pridobivanje edinstvenih vrednosti iz obsega B5: B15, z a samski formula vnesena v E5:

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

Primer funkcije UNIQUE



Rezultat je seznam petih edinstvenih imen mest, ki se pojavijo v E5: E9.

Kot vse formule se bo tudi UNIQUE samodejno posodobil, ko se podatki spremenijo. Spodaj je Vancouver v 11. vrstici zamenjal Portland. Rezultat UNIQUE zdaj vključuje Vancouver:

Primer funkcije UNIQUE po spremembi

Razlivanje - ena formula, veliko vrednosti

V Dynamic Excelu bodo formule, ki vrnejo več vrednosti, ' igra 'te vrednosti neposredno na delovni list. To bo takoj bolj logično za uporabnike formul. To je tudi popolnoma dinamično vedenje - ko se izvorni podatki spremenijo, se razliti rezultati takoj posodobijo.

Pravokotnik, ki zajema vrednosti, se imenuje ' obseg igre '. Opazili boste, da ima razlitje posebno poudarjanje. V zgornjem primeru UNIQUE je območje razlitja E5: E10.

Ko se podatki spremenijo, se bo razlitje po potrebi razširilo ali skrčilo. Morda boste videli nove vrednosti ali obstoječe vrednosti izginile. Na ta način je območje razlitja nova vrsta dinamičnega območja.

Opomba: ko razlitje blokirajo drugi podatki, se prikaže napaka #SPILL. Ko sprostite prostor za razlitje, se formula samodejno razlije.

Video: Razlitje in območje razlitja

Referenca območja razlitja

Če se želite sklicevati na območje razlitja, za prvo celico v območju uporabite znak zgoščevanja (#). Na primer za sklicevanje na rezultate zgoraj navedene funkcije UNIQUE uporabite:

 
=E5# // reference UNIQUE results

To je enako kot sklicevanje na celotno območje razlitja in to sintakso boste videli, ko napišete formulo, ki se nanaša na celotno območje razlitja.

Referenco območja razlitja lahko neposredno vnesete v druge formule. Na primer za štetje mest, ki jih je vrnil UNIQUE, lahko uporabite:

 
= COUNTA (E5#) // count unique cities

Primer sklica na območje razlitja dinamičnega polja

Ko se območje razlitja spremeni, bo formula odražala najnovejše podatke.

Velika poenostavitev

Dodajanje novih formul dinamičnega polja pomeni, da je mogoče nekatere formule močno poenostaviti. Tu je nekaj primerov:

  • Izvleček in seznam edinstvenih vrednosti ( prej | po )
  • Štejte enolične vrednosti ( prej | po )
  • Filtriraj in izvleči zapise ( prej | po )
  • Izvleči delna ujemanja ( prej | po )

Moč enega

Ena najmočnejših prednosti pristopa „ena formula, številne vrednote“ je manjše zanašanje nanje absolutno ali mešano reference. Ko dinamična matrična formula razlije rezultate na delovni list, sklici ostanejo nespremenjeni, vendar formula generira pravilne rezultate.

Na primer, spodaj uporabljamo funkcijo FILTER za pridobivanje zapisov v skupini 'A'. V celico F5 se vnese ena sama formula:

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

Primer samo dinamične matrike ena formula

Upoštevajte, da sta oba obsega odklenjena relativna sklica, vendar formula deluje popolnoma.

To je za mnoge uporabnike velika korist, saj postopek pisanja formul tako poenostavi. Za še en dober primer glejte spodnjo tabelo množenja.

Verižne funkcije

Stvari postanejo res zanimive, če povežete več kot eno funkcijo dinamičnega polja. Morda želite razvrstiti rezultate, ki jih je vrnil UNIQUE? Enostavno Samo zavijte Funkcija SORT okoli funkcije UNIQUE, kot je ta:

Primer UNIQUE in SORT skupaj

Kot prej, ko se izvorni podatki spremenijo, se samodejno prikažejo novi edinstveni rezultati, lepo razvrščeni.

Domače vedenje

Pomembno je razumeti, da je vedenje dinamičnega polja a domač in globoko integriran . Kdaj kaj formula vrne več rezultatov, ti se bodo razlili v več celic na delovnem listu. Sem spadajo starejše funkcije, ki prvotno niso bile zasnovane za delo z dinamičnimi nizi.

Na primer, v tradicionalnem Excelu, če damo Funkcija LEN do obseg besedilnih vrednosti, bomo videli a samski rezultat. Če bomo v Dynamic Excelu dali funkciji LEN vrsto vrednosti, bomo videli večkraten rezultatov. Ta zaslon spodaj prikazuje staro vedenje na levi in ​​novo vedenje na desni:

Funkcija LEN s polji - starimi in novimi

To je velika sprememba, ki lahko vpliva na vse vrste formul. Na primer VLOOKUP funkcija je zasnovan za pridobivanje ene vrednosti iz tabele z uporabo indeksa stolpca. Če pa v Dynamic Excelu damo VLOOKUP več kot en indeks stolpcev z uporabo konstanta matrike Všečkaj to:

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

VLOOKUP bo vrnil več stolpcev:

Več rezultatov z VLOOKUP-om in dinamičnimi nizi

Z drugimi besedami, čeprav VLOOKUP nikoli ni bil zasnovan za vrnitev več vrednosti, zdaj to lahko stori zahvaljujoč novemu mehanizmu formul v Dynamic Excelu.

Vse formule

Na koncu upoštevajte, da dinamična polja delujejo z vse formule ne samo funkcije . V spodnjem primeru celica C5 vsebuje eno samo formulo:

ustvari tekoči seštevek v Excelu
 
=B5:B14*C4:L4

Rezultat se razlije v obseg 10 na 10, ki vključuje 100 celic:

Tabela množenja dinamičnega polja

Opomba: V tradicionalnem Excelu lahko vidite več rezultatov, ki jih vrne matrična formula uporabite F9 za pregled formule . Ampak, razen če vnašate formulo kot a formula večceličnega polja , bo na delovnem listu prikazana samo ena vrednost.

Nizi gredo v mainstream

Z uvajanjem dinamičnih nizov se beseda ' matriko 'se bo pojavljalo veliko pogosteje. Pravzaprav boste morda videli 'matriko' in 'obseg', ki se uporabljata skoraj izmenično. V Excelu boste videli polja, zaprta v zavite oklepaje, kot je ta:

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

Matrika je programski izraz, ki se nanaša na seznam elementov, ki so prikazani v določenem vrstnem redu. Razlogi, zaradi katerih se matrike tako pogosto pojavljajo v Excelovih formulah, so, da polja lahko popolnoma izrazijo vrednosti v območju celic .

Video: Kaj je matrika?

Operacije z matriko postanejo pomembne

Ker lahko dinamične Excelove formule zlahka delujejo z več vrednostmi, bodo operacije matrike postale pomembnejše. Izraz 'operacija matrike' se nanaša na izraz, ki izvaja matrično logično preizkusno ali matematično operacijo. Na primer spodnji izraz preizkusi, ali so vrednosti v B5: B9 enake 'ca'

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

Primer preizkusa polja z nizom a

ker je v B5: B9 5 celic, je rezultat 5 vrednosti TRUE / FALSE v matriki:

 
{FALSETRUEFALSETRUETRUE}

Spodnja operacija matrike preverja količine, večje od 100:

 
=C5:C9>100 // amounts > 100

Primer preizkusa delovanja niza b

Končna operacija matrike združuje test A in test B v enem izrazu:

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

Primer preizkusa array polja a in b

Opomba: Excel med matematično operacijo samodejno prisili vrednosti TRUE in FALSE na 1 in 0.

Če želite to vrniti na formule dinamičnih nizov v Excelu, spodnji primer prikazuje, kako lahko v funkciji FILTER uporabimo popolnoma enako operacijo matrike kot vključujejo prepir:

Operacija matrike s funkcijo FILTER

FILTER vrne dva zapisa, kjer je stanje = 'ca' in znesek> 100.

Za predstavitev glejte: Kako filtrirati z dvema kriterijema (video).

Nove in stare formule matrike

V Dynamic Excelu ni treba vnašati formul matrike s tipko control + shift + enter. Ko je formula ustvarjena, Excel preveri, ali lahko formula vrne več vrednosti. V tem primeru se samodejno shrani kot dinamična formula matrike, vendar kodrastih oklepajev ne boste videli. Spodnji primer prikazuje tipično formulo matrike, vneseno v Dynamic Excel:

Osnovna formula matrike v tradicionalnem Excelu

Če v tradicionalnem Excelu odprete isto formulo, boste videli zavite oklepaje:

Osnovna formula matrike v dinamičnem Excelu

V drugi smeri, ko se v Dynamic Excelu odpre „tradicionalna“ formula matrike, boste v vrstici s formulami videli zavite oklepaje. Na spodnjem zaslonu je na primer prikazana preprosta formula matrike v tradicionalnem Excelu:

Enostavna matrična formula z zavitimi oklepaji

Če pa formulo znova vnesete brez sprememb, se zaviti oklepaji odstranijo in formula vrne enak rezultat:

Preprosta formula matrike z zavitimi oklepaji ni vidna

Bottom line je, da formule matrike, vnesene s control + shift + enter (CSE), še vedno delujejo, da ohranijo združljivost, vendar vam formule matrike ne bi bilo treba vnašati s CSE v Dynamic Excel.

Znak @

Z uvedbo dinamičnih nizov boste videli, da se znak @ pogosteje pojavlja v formulah. Znak @ omogoča vedenje, znano kot ' implicitno presečišče '. Implicitno presečišče je logičen postopek, pri katerem se veliko vrednosti zmanjša na eno vrednost.

V tradicionalnem Excelu je implicitno presečišče tiho vedenje, ki se uporablja (kadar je potrebno) za zmanjšanje več vrednosti na en rezultat v eni celici. V Dynamic Excelu običajno ni potreben, saj se lahko več rezultatov razlije na delovni list. Po potrebi se implicitno presečišče prikliče ročno z znakom @.

Pri odpiranju preglednic, ki so ustvarile starejšo različico Excela, boste morda videli znak @, ki se samodejno doda obstoječim formulam, ki imajo potencial vrniti veliko vrednosti. V tradicionalnem Excelu se formula, ki vrne več vrednosti, ne razlije na delovni list. Znak @ vsiljuje enako vedenje v Dynamic Excelu, tako da se formula obnaša enako in vrne enak rezultat kot v prvotni različici Excela.

Z drugimi besedami, znak @ je dodan, da prepreči, da bi starejša formula prelila več rezultatov na delovni list. Odvisno od formule boste morda lahko odstranili znak @ in vedenje formule se ne bo spremenilo.

Povzetek

  • Dinamični nizi bodo olajšali pisanje nekaterih formul.
  • Zdaj lahko s pomočjo formul enostavno filtrirate ujemajoče se podatke, razvrščate in izvlečete edinstvene vrednosti.
  • Formule dinamičnega polja lahko v verige (ugnezdijo), da lahko na primer filtrirajo in razvrščajo.
  • Formule, ki vrnejo več kot eno vrednost, se samodejno razlijejo.
  • Za vnos formule matrike ni treba uporabljati Ctrl + Shift + Enter.
  • Formule dinamičnega polja so na voljo samo v Excelu 365.
Avtor Dave Bruns


^