Sadržaj
Što su dinamički nizovi
U rujnu 2018. Microsoft je objavio ažuriranje koje Microsoft Excelu dodaje potpuno novi alat: dinamičke nizove i 7 novih funkcija za rad s njima. Ove stvari, bez pretjerivanja, radikalno mijenjaju svu uobičajenu tehniku rada s formulama i funkcijama i tiču se doslovno svakog korisnika.
Razmotrite jednostavan primjer kako biste objasnili suštinu.
Pretpostavimo da imamo jednostavnu tablicu s podacima o gradovima-mjesecima. Što će se dogoditi ako odaberemo bilo koju praznu ćeliju s desne strane lista i unesemo u nju formulu koja se ne povezuje na jednu ćeliju, već odmah na raspon?
U svim prethodnim verzijama Excela, nakon klika na ući dobili bismo sadržaj samo jedne prve ćelije B2. Kako drugačije?
Pa, ili bi bilo moguće zamotati ovaj raspon u neku vrstu agregacijske funkcije kao što je =SUM(B2:C4) i dobiti ukupni zbroj za to.
Ako su nam potrebne složenije operacije od primitivnog zbroja, kao što je izdvajanje jedinstvenih vrijednosti ili Top 3, tada bismo morali unijeti našu formulu kao formulu polja pomoću tipkovničkog prečaca Ctrl+smjena+ući.
Sada je sve drugačije.
Sada nakon unosa takve formule, možemo jednostavno kliknuti na ući – i kao rezultat odmah dobiti sve vrijednosti uXNUMXbuXNUMXb na koje smo se pozivali:
Ovo nije magija, već nova dinamička polja koja sada ima Microsoft Excel. Dobro došli u novi svijet 🙂
Značajke rada s dinamičkim nizovima
Tehnički, cijeli naš dinamički niz pohranjen je u prvoj ćeliji G4, ispunjavajući potrebni broj ćelija s desne i dolje svojim podacima. Ako odaberete bilo koju drugu ćeliju u nizu, tada će poveznica u traci formule biti neaktivna, pokazujući da se nalazimo u jednoj od ćelija "djeteta":
Pokušaj brisanja jedne ili više "podređenih" ćelija neće dovesti do ničega - Excel će ih odmah ponovno izračunati i ispuniti.
U isto vrijeme, možemo se sa sigurnošću pozvati na ove "dijete" stanice u drugim formulama:
Ako kopirate prvu ćeliju niza (na primjer, iz G4 u F8), tada će se cijeli niz (njegove reference) kretati u istom smjeru kao u uobičajenim formulama:
Ako trebamo pomicati niz, tada će biti dovoljno pomicati (mišem ili kombinacijom Ctrl+X, Ctrl+V), opet samo prva glavna ćelija G4 – nakon nje će se ona prebaciti na novo mjesto i cijeli naš niz će se ponovno proširiti.
Ako se trebate pozvati negdje drugdje na listu na stvoreni dinamički niz, tada možete upotrijebiti poseban znak # ("funta") nakon adrese njegove vodeće ćelije:
Na primjer, sada možete jednostavno napraviti padajući popis u ćeliji koja se odnosi na stvoreni dinamički niz:
Pogreške dinamičkog niza
Ali što se događa ako nema dovoljno prostora za proširenje niza ili ako postoje ćelije koje su već zauzete drugim podacima na njegovom putu? Upoznajte potpuno novu vrstu pogrešaka u Excelu – #PRIJENOS! (#PROSUTI!):
Kao i uvijek, kliknemo li na ikonu sa žutim rombom i uskličnikom, dobit ćemo detaljnije objašnjenje izvora problema i brzo pronaći interferirajuće stanice:
Slične pogreške dogodit će se ako niz ode s lista ili pogodi spojenu ćeliju. Ako uklonite prepreku, sve će se odmah ispraviti u hodu.
Dinamički nizovi i pametne tablice
Ako dinamički niz pokazuje na “pametnu” tablicu stvorenu tipkovničkim prečacem Ctrl+T ili Početna – Format kao tablica (Početna — Formatiraj kao tablicu), tada će također naslijediti svoju glavnu kvalitetu – automatsko određivanje veličine.
Prilikom dodavanja novih podataka na dno ili desno, pametna tablica i dinamički raspon također će se automatski rastegnuti:
Međutim, postoji jedno ograničenje: ne možemo koristiti referencu dinamičkog raspona u forumulama unutar pametne tablice:
Dinamički nizovi i druge značajke programa Excel
Dobro, kažeš. Sve je ovo zanimljivo i smiješno. Nema potrebe, kao prije, ručno rastezati formulu s referencom na prvu ćeliju izvornog raspona dolje i udesno i sve to. I to je sve?
Ne baš.
Dinamički nizovi nisu samo još jedan alat u Excelu. Sada su ugrađeni u samo srce (ili mozak) Microsoft Excela – njegovu mašinu za izračun. To znači da druge Excel formule i funkcije koje su nam sada poznate također podržavaju rad s dinamičkim nizovima. Pogledajmo nekoliko primjera kako bismo vam dali ideju o dubini promjena koje su se dogodile.
prenositi
Za transponiranje raspona (zamjena redaka i stupaca) Microsoft Excel je uvijek imao ugrađenu funkciju TRANSP (TRANSPONIRAJ). Međutim, da biste je koristili, prvo morate ispravno odabrati raspon za rezultate (na primjer, ako je unos bio raspon 5×3, tada morate odabrati 3×5), zatim ući u funkciju i pritisnuti tipku kombinacija Ctrl+smjena+ući, jer je mogao raditi samo u načinu formule polja.
Sada možete samo odabrati jednu ćeliju, unijeti istu formulu u nju i kliknuti na normalu ući – dinamički niz će sve učiniti sam:
Tablica množenja
Ovo je primjer koji sam dao kad su me zamolili da vizualiziram prednosti formula polja u Excelu. Sada, da biste izračunali cijelu Pitagorinu tablicu, dovoljno je stati u prvu ćeliju B2, tamo unijeti formulu koja množi dva niza (okomiti i vodoravni skup brojeva 1..10) i jednostavno kliknuti na ući:
Lijepljenje i preinaka kućišta
Nizovi se ne mogu samo množiti, već i lijepiti zajedno standardnim operatorom & (&). Pretpostavimo da trebamo izdvojiti ime i prezime iz dva stupca i ispraviti velika i mala slova u izvornim podacima. To činimo jednom kratkom formulom koja tvori cijeli niz, a zatim na njega primjenjujemo funkciju PROPNAČ (ISPRAVAN)za sređivanje registra:
Zaključak Top 3
Pretpostavimo da imamo hrpu brojeva iz kojih želimo izvesti prva tri rezultata, poredajući ih silaznim redoslijedom. Sada se to radi po jednoj formuli i opet bez ikakve Ctrl+smjena+ući kao prije:
Ako želite da se rezultati ne postavljaju u stupac, već u red, tada je dovoljno dvotočke (razdjelnik redaka) u ovoj formuli zamijeniti točkom i zarezom (razdjelnik elemenata unutar jednog retka). U engleskoj verziji Excela ti razdjelnici su točka-zarez i zarezi.
VLOOKUP izdvaja više stupaca odjednom
Funkcije VPR (VLOOKUP) sada možete povući vrijednosti ne iz jednog, već iz nekoliko stupaca odjednom - samo navedite njihove brojeve (u bilo kojem željenom redoslijedu) kao niz u trećem argumentu funkcije:
OFFSET funkcija koja vraća dinamički niz
Jedna od najzanimljivijih i najkorisnijih (nakon VLOOKUP-a) funkcija za analizu podataka je funkcija RASPOLAGANJE (POMAK), kojemu sam svojedobno posvetio cijelo poglavlje u svojoj knjizi i članak ovdje. Poteškoća u razumijevanju i svladavanju ove funkcije uvijek je bila u tome što je vraćala niz (raspon) podataka kao rezultat, ali ga nismo mogli vidjeti, jer Excel još uvijek nije znao raditi s nizovima izvan okvira.
Sada je ovaj problem prošlost. Pogledajte kako sada, pomoću jedne formule i dinamičkog polja koje vraća OFFSET, možete izdvojiti sve retke za dati proizvod iz bilo koje sortirane tablice:
Pogledajmo njene argumente:
- A1 – početna ćelija (referentna točka)
- POISKPOZ(F2;A2:A30;0) – izračun pomaka od početne ćelije prema dolje – do prvog pronađenog kupusa.
- 0 – pomak “prozora” udesno u odnosu na početnu ćeliju
- SČËTESLI(A2:A30;F2) – izračun visine vraćenog “prozora” – broj linija u kojima se nalazi kupus.
- 4 — veličina “prozora” vodoravno, tj. izlaz 4 stupca
Nove funkcije za dinamičke nizove
Osim podrške mehanizmu dinamičkog niza u starim funkcijama, u Microsoft Excel je dodano nekoliko potpuno novih funkcija, posebno izoštrenih za rad s dinamičkim nizovima. Konkretno, to su:
- GRADE (VRSTA) – sortira ulazni raspon i proizvodi dinamički niz na izlazu
- SORTPO (SORTIRAJ) – može sortirati jedan raspon prema vrijednostima iz drugog
- FILTER (FILTAR) – dohvaća retke iz izvornog raspona koji zadovoljavaju navedene uvjete
- JEDINSTVENO (JEDINSTVENA) – izvlači jedinstvene vrijednosti iz raspona ili uklanja duplikate
- SLMASIVE (RANDARRAY) – generira niz slučajnih brojeva zadane veličine
- POSTELJICA (SLIJED) — formira niz od niza brojeva sa zadanim korakom
Više o njima - malo kasnije. Vrijedni su zasebnog članka (a ne jednog) za pažljivo proučavanje 🙂
Zaključci
Ako ste pročitali sve gore napisano, onda mislim da ste već shvatili razmjere promjena koje su se dogodile. Toliko se stvari u Excelu sada može raditi lakše, lakše i logičnije. Moram priznati da sam pomalo šokiran koliko će članaka sada morati biti ispravljeno ovdje, na ovoj stranici iu mojim knjigama, ali spreman sam to učiniti laka srca.
Sumirajući rezultate, prozodija dinamičke nizove, možete napisati sljedeće:
- Možete zaboraviti na kombinaciju Ctrl+smjena+ući. Excel sada ne vidi razliku između "običnih formula" i "formula polja" i tretira ih na isti način.
- O funkciji SUMPRODUCT (SUMPROIZVOD), koji se prije koristio za unos formula polja bez Ctrl+smjena+ući možete i zaboraviti – sada je dovoljno jednostavno IZNOS и ući.
- Pametne tablice i poznate funkcije (SUM, IF, VLOOKUP, SUMIFS, itd.) sada također u potpunosti ili djelomično podržavaju dinamičke nizove.
- Postoji povratna kompatibilnost: ako otvorite radnu knjigu s dinamičkim nizovima u staroj verziji Excela, oni će se pretvoriti u formule polja (u vitičastim zagradama) i nastaviti raditi u "starom stilu".
Našao neki broj minusi:
- Ne možete izbrisati pojedinačne retke, stupce ili ćelije iz dinamičkog niza, tj. on živi kao jedna cjelina.
- Ne možete sortirati dinamički niz na uobičajeni način Podaci – sortiranje (Podaci — Sortiraj). Za to sada postoji posebna funkcija. GRADE (VRSTA).
- Dinamički raspon se ne može pretvoriti u pametnu tablicu (ali možete napraviti dinamički raspon na temelju pametne tablice).
Naravno, ovo nije kraj i siguran sam da će Microsoft nastaviti poboljšavati ovaj mehanizam u budućnosti.
Gdje mogu preuzeti?
I za kraj, glavno pitanje 🙂
Microsoft je prvi put najavio i prikazao pregled dinamičkih polja u Excelu još u rujnu 2018. na konferenciji Zapaliti. U sljedećih nekoliko mjeseci obavljeno je temeljito testiranje i uvođenje novih značajki mačke zaposlenicima samog Microsofta, a potom i na volonterima testerima iz kruga Office Insidera. Ove godine, ažuriranje koje dodaje dinamičke nizove počelo se postupno uvoditi redovitim pretplatnicima na Office 365. Na primjer, dobio sam ga tek u kolovozu sa svojom pretplatom na Office 365 Pro Plus (Mjesečno ciljano).
Ako vaš Excel još nema dinamičke nizove, ali stvarno želite raditi s njima, tada postoje sljedeće opcije:
- Ako imate pretplatu na Office 365, možete jednostavno pričekati dok ovo ažuriranje ne stigne do vas. Koliko brzo se to događa ovisi o tome koliko se često ažuriranja isporučuju u vaš Office (jednom godišnje, jednom svakih šest mjeseci, jednom mjesečno). Ako imate poslovno računalo, možete zatražiti od svog administratora da postavi ažuriranja za češće preuzimanje.
- Možete se pridružiti redovima onih dobrovoljaca za testiranje Office Insidera – tada ćete biti prvi koji će primiti sve nove značajke i funkcije (naravno, postoji mogućnost povećanja buggy-a u Excelu).
- Ako nemate pretplatu, već samostalnu verziju Excela u kutiji, tada ćete morati pričekati barem do izdavanja sljedeće verzije Officea i Excela 2022. godine. Korisnici takvih verzija dobivaju samo sigurnosna ažuriranja i ispravke grešaka, a sve nove "dobrote" sada idu samo pretplatnicima na Office 365. Tužno ali istinito 🙂
U svakom slučaju, kada se u vašem Excelu pojave dinamički nizovi – nakon ovog članka bit ćete spremni za to 🙂
- Što su formule polja i kako ih koristiti u Excelu
- Zbrajanje prozora (raspona) pomoću funkcije OFFSET
- 3 načina transponiranja tablice u Excelu