vsebina
Kaj so dinamični nizi
Septembra 2018 je Microsoft izdal posodobitev, ki Microsoft Excelu doda popolnoma novo orodje: dinamične nize in 7 novih funkcij za delo z njimi. Te stvari brez pretiravanja radikalno spremenijo vso običajno tehniko dela s formulami in funkcijami in zadevajo dobesedno vsakega uporabnika.
Razmislite o preprostem primeru, da pojasnite bistvo.
Recimo, da imamo preprosto tabelo s podatki o mestih-meseci. Kaj se bo zgodilo, če izberemo katero koli prazno celico na desni strani lista in vanjo vnesemo formulo, ki se ne poveže z eno celico, ampak takoj z obsegom?
V vseh prejšnjih različicah Excela po kliku na Vnesite dobili bi vsebino samo ene prve celice B2. Kako drugače?
No, ali pa bi bilo mogoče ta obseg zaviti v nekakšno združevalno funkcijo, kot je =SUM(B2:C4) in za to dobiti skupno vsoto.
Če bi potrebovali bolj zapletene operacije od primitivne vsote, kot je pridobivanje edinstvenih vrednosti ali Top 3, bi morali vnesti našo formulo kot matrično formulo z uporabo bližnjice na tipkovnici Ctrl+Shift+Vnesite.
Zdaj je vse drugače.
Zdaj, ko vnesemo takšno formulo, lahko preprosto kliknemo na Vnesite – in kot rezultat takoj dobite vse vrednosti uXNUMXbuXNUMXb, na katere smo se sklicevali:
To ni čarovnija, ampak nove dinamične matrike, ki jih ima zdaj Microsoft Excel. Dobrodošli v novem svetu 🙂
Značilnosti dela z dinamičnimi nizi
Tehnično gledano je naše celotno dinamično polje shranjeno v prvi celici G4, ki s svojimi podatki zapolnjuje zahtevano število celic desno in navzdol. Če izberete katero koli drugo celico v matriki, bo povezava v vrstici s formulami neaktivna, kar kaže, da smo v eni od »podrejenih« celic:
Poskus brisanja ene ali več "otroških" celic ne bo privedel do ničesar - Excel jih bo takoj preračunal in zapolnil.
Hkrati se lahko varno sklicujemo na te "otroške" celice v drugih formulah:
Če kopirate prvo celico matrike (na primer iz G4 v F8), se bo celotna matrika (njene reference) premaknila v isto smer kot v običajnih formulah:
Če moramo premakniti matriko, bo dovolj, da se premaknemo (z miško ali kombinacijo Ctrl+X, Ctrl+V), spet samo prva glavna celica G4 – za njo se bo prenesla na novo mesto in naš celoten niz bo ponovno razširjen.
Če se morate nekje drugje na listu sklicevati na ustvarjeno dinamično matriko, potem lahko uporabite poseben znak # (»funt«) za naslovom njene vodilne celice:
Na primer, zdaj lahko preprosto ustvarite spustni seznam v celici, ki se nanaša na ustvarjeno dinamično matriko:
Napake dinamičnega niza
Toda kaj se zgodi, če ni dovolj prostora za razširitev matrike ali če so celice na njeni poti že zasedene z drugimi podatki? Spoznajte bistveno novo vrsto napak v Excelu – #PRENOS! (#IZLIJ!):
Kot vedno, če kliknemo na ikono z rumenim diamantom in klicajem, dobimo podrobnejšo razlago izvora težave in hitro najdemo moteče celice:
Podobne napake se bodo pojavile, če matrika zaide z lista ali zadene združeno celico. Če odstranite oviro, se bo vse takoj popravilo.
Dinamična polja in pametne tabele
Če dinamični niz kaže na »pametno« tabelo, ustvarjeno z bližnjico na tipkovnici Ctrl+T ali Domov – Oblikuj kot tabelo (Domov — Oblikuj kot tabelo), potem bo podedoval tudi svojo glavno kakovost – samodejno določanje velikosti.
Pri dodajanju novih podatkov na dno ali desno se pametna tabela in dinamični razpon tudi samodejno raztegneta:
Vendar pa obstaja ena omejitev: ne moremo uporabiti reference dinamičnega obsega v forumih znotraj pametne tabele:
Dinamična polja in druge Excelove funkcije
V redu, praviš. Vse to je zanimivo in smešno. Ni potrebe, kot prej, ročno raztegniti formule s sklicevanjem na prvo celico prvotnega obsega navzdol in v desno in vse to. In to je vse?
Ne čisto.
Dinamični nizi niso le še eno orodje v Excelu. Zdaj so vgrajeni v samo srce (ali možgane) Microsoft Excela – njegov računski mehanizem. To pomeni, da druge Excelove formule in funkcije, ki jih zdaj poznamo, prav tako podpirajo delo z dinamičnimi nizi. Oglejmo si nekaj primerov, da boste dobili predstavo o globini sprememb, ki so se zgodile.
Prenesi
Za prenos obsega (zamenjava vrstic in stolpcev) je imel Microsoft Excel vedno vgrajeno funkcijo TRANSP (PRENOS). Če pa jo želite uporabiti, morate najprej pravilno izbrati obseg za rezultate (na primer, če je bil vnos obseg 5×3, potem morate izbrati 3×5), nato vstopiti v funkcijo in pritisniti kombinacija Ctrl+Shift+Vnesite, ker bi lahko deloval le v načinu matrične formule.
Zdaj lahko samo izberete eno celico, vanjo vnesete isto formulo in kliknete na normalno Vnesite – dinamično polje bo vse naredilo samo:
Tabela množenja
To je primer, ki sem ga dal, ko so me prosili, naj vizualiziram prednosti matričnih formul v Excelu. Zdaj je za izračun celotne Pitagorejske tabele dovolj, da stojite v prvi celici B2, vnesete formulo, ki pomnoži dva niza (navpični in vodoravni niz števil 1..10) in preprosto kliknete na Vnesite:
Lepljenje in predelava ohišij
Nizov ni mogoče samo pomnožiti, ampak tudi zlepiti skupaj s standardnim operatorjem & (&). Recimo, da moramo iz dveh stolpcev izvleči ime in priimek ter popraviti skakanje velikih in malih črk v izvirnih podatkih. To naredimo z eno kratko formulo, ki tvori celotno matriko, nato pa nanjo uporabimo funkcijo PROPNAČ (PRAVILNO)za urejanje registra:
Zaključek Top 3
Recimo, da imamo kup številk, iz katerih želimo izpeljati prve tri rezultate in jih razvrstiti v padajočem vrstnem redu. Zdaj se to izvaja z eno formulo in spet brez katere koli Ctrl+Shift+Vnesite kot prej:
Če želite, da rezultati niso postavljeni v stolpec, ampak v vrstico, potem je dovolj, da dvopičja (ločilo vrstic) v tej formuli zamenjate s podpičjem (ločilo elementov v eni vrstici). V angleški različici Excela so ta ločila podpičja oziroma vejice.
VLOOKUP ekstrahira več stolpcev hkrati
funkcije VPR (VLOOKUP) zdaj lahko potegnete vrednosti ne iz enega, ampak iz več stolpcev hkrati - samo določite njihove številke (v poljubnem vrstnem redu) kot matriko v tretjem argumentu funkcije:
Funkcija OFFSET, ki vrne dinamično polje
Ena najbolj zanimivih in uporabnih (po VLOOKUP) funkcij za analizo podatkov je funkcija ODSTRANJEVANJE (ZAMIK), ki sem ji nekoč posvetil celo poglavje v svoji knjigi in članek tukaj. Težava pri razumevanju in obvladovanju te funkcije je bila vedno v tem, da je kot rezultat vrnila matriko (obseg) podatkov, ki pa je nismo mogli videti, ker Excel še vedno ni znal delati z matrikami takoj.
Zdaj je ta problem preteklost. Oglejte si, kako lahko zdaj z uporabo ene same formule in dinamične matrike, ki jo vrne OFFSET, izvlečete vse vrstice za dani izdelek iz katere koli razvrščene tabele:
Oglejmo si njene argumente:
- A1 – začetna celica (referenčna točka)
- ПОИСКПОЗ(F2;A2:A30;0) – izračun premika od začetne celice navzdol – do prvega najdenega zelja.
- 0 – premik "okna" v desno glede na začetno celico
- SČETESLI(A2:A30;F2) – izračun višine vrnjenega “okna” – število vrstic, kjer je zelje.
- 4 — velikost "okna" vodoravno, tj. izpiši 4 stolpce
Nove funkcije za dinamična polja
Poleg podpore mehanizmu dinamičnih matrik v starih funkcijah je bilo v Microsoft Excel dodanih več popolnoma novih funkcij, posebej izostrenih za delo z dinamičnimi matrikami. To so zlasti:
- GRADE (RAZVRSTI) – razvrsti vhodni obseg in ustvari dinamično polje na izhodu
- SORTPO (RAZVRSTI PO) – lahko razvrsti en obseg po vrednostih iz drugega
- FILTER (FILTER) – pridobi vrstice iz izvornega obsega, ki izpolnjujejo podane pogoje
- UNIK (UNIKATNO) – izvleče edinstvene vrednosti iz obsega ali odstrani dvojnike
- SLMASSIVE (RANDARRAY) – ustvari matriko naključnih števil dane velikosti
- POPORODSTVO (SEKVENCA) — tvori matriko iz zaporedja števil z danim korakom
Več o njih - malo kasneje. Vredni so ločenega članka (in ne enega) za premišljeno študijo 🙂
Sklepi
Če ste prebrali vse zgoraj napisano, potem mislim, da se že zavedate razsežnosti sprememb, ki so se zgodile. Toliko stvari v Excelu je zdaj mogoče narediti lažje, lažje in bolj logično. Moram priznati, da sem nekoliko šokiran, koliko člankov bo zdaj treba popraviti tukaj, na tej strani in v mojih knjigah, vendar sem to pripravljen narediti z lahkim srcem.
Če povzamemo rezultate, pluse dinamičnih nizov, lahko zapišete naslednje:
- Na kombinacijo lahko pozabite Ctrl+Shift+Vnesite. Excel zdaj ne vidi razlike med »navadnimi formulami« in »matričnimi formulami« in jih obravnava na enak način.
- O funkciji SUMPRODUCT (SUM PRODUKT), ki se je prej uporabljal za vnos matričnih formul brez Ctrl+Shift+Vnesite lahko tudi pozabiš – zdaj je dovolj enostavno SUM и Vnesite.
- Pametne tabele in znane funkcije (SUM, IF, VLOOKUP, SUMIFS itd.) zdaj v celoti ali delno podpirajo tudi dinamična polja.
- Obstaja povratna združljivost: če odprete delovni zvezek z dinamičnimi nizi v stari različici Excela, se bodo spremenili v matrične formule (v zavitih oklepajih) in še naprej delovali v "starem slogu".
Našel neko številko minuse:
- Iz dinamične matrike ne morete izbrisati posameznih vrstic, stolpcev ali celic, kar pomeni, da živi kot ena entiteta.
- Dinamičnega niza ne morete razvrstiti na običajen način Podatki – Razvrščanje (Podatki — Razvrsti). Za to zdaj obstaja posebna funkcija. GRADE (RAZVRSTI).
- Dinamičnega obsega ni mogoče spremeniti v pametno tabelo (lahko pa naredite dinamični obseg na osnovi pametne tabele).
Seveda to še ni konec in prepričan sem, da bo Microsoft v prihodnosti še izboljševal ta mehanizem.
Kje lahko prenesem?
In za konec še glavno vprašanje 🙂
Microsoft je prvič napovedal in pokazal predogled dinamičnih nizov v Excelu septembra 2018 na konferenci Ignite. V naslednjih nekaj mesecih je potekalo temeljito testiranje in uvajanje novih funkcij mačke zaposlenih v samem Microsoftu, nato pa na prostovoljnih preizkuševalcih iz kroga Office Insiders. Letos so posodobitev, ki dodaja dinamična polja, začeli postopoma uvajati rednim naročnikom Office 365. Na primer, prejel sem ga šele avgusta z mojo naročnino na Office 365 Pro Plus (mesečno ciljano).
Če vaš Excel še nima dinamičnih nizov, vendar res želite delati z njimi, potem obstajajo naslednje možnosti:
- Če imate naročnino na Office 365, lahko preprosto počakate, da dobite to posodobitev. Kako hitro se to zgodi, je odvisno od tega, kako pogosto so posodobitve dostavljene v vaš Office (enkrat na leto, enkrat na šest mesecev, enkrat na mesec). Če imate službeni računalnik, lahko prosite skrbnika, da nastavi pogostejši prenos posodobitev.
- Lahko se pridružite vrstam tistih prostovoljcev za preizkušanje programa Office Insiders – takrat boste prvi prejeli vse nove funkcije in funkcije (seveda obstaja možnost povečanja hrošča v Excelu).
- Če nimate naročnine, ampak samostojno različico Excela v škatli, boste morali počakati vsaj do izdaje naslednje različice Officea in Excela leta 2022. Uporabniki takšnih različic prejmejo samo varnostne posodobitve in popravke napak, vse nove "dobrote" pa gredo zdaj samo naročnikom Office 365. Žalostno, a resnično 🙂
V vsakem primeru, ko se v vašem Excelu pojavijo dinamični nizi – po tem članku boste na to pripravljeni 🙂
- Kaj so matrične formule in kako jih uporabljati v Excelu
- Seštevek okna (razpona) z uporabo funkcije OFFSET
- 3 načini za prenos tabele v Excel