sadržaj
Šta su dinamički nizovi?
U septembru 2018. Microsoft je objavio ažuriranje koje dodaje potpuno novi alat u Microsoft Excel: dinamičke nizove i 7 novih funkcija za rad s njima. Ove stvari, bez pretjerivanja, radikalno mijenjaju svu uobičajenu tehniku rada sa formulama i funkcijama i zabrinjavaju, doslovno, svakog korisnika.
Razmotrite jednostavan primjer da objasnite suštinu.
Pretpostavimo da imamo jednostavnu tabelu sa podacima o gradovima-mesecima. Šta će se dogoditi ako odaberemo bilo koju praznu ćeliju na desnoj strani lista i unesemo u nju formulu koja ne povezuje 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 umotati ovaj raspon u neku vrstu agregirajuće funkcije kao što je =SUM(B2:C4) i dobiti ukupni zbroj za to.
Ako su nam bile potrebne složenije operacije od primitivnog zbroja, kao što je izdvajanje jedinstvenih vrijednosti ili Top 3, onda bismo morali uneti našu formulu kao formulu niza koristeći prečicu na tipkovnici 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 dobijete sve vrijednosti uXNUMXbuXNUMXb na koje smo spomenuli:
Ovo nije magija, već novi dinamički nizovi koje sada ima Microsoft Excel. Dobrodošli u novi svijet 🙂
Karakteristike rada sa dinamičkim nizovima
Tehnički, cijeli naš dinamički niz je pohranjen u prvoj ćeliji G4, ispunjavajući potrebni broj ćelija desno i dolje svojim podacima. Ako odaberete bilo koju drugu ćeliju u nizu, tada će veza u traci formule biti neaktivna, pokazujući da se nalazimo u jednoj od "podređenih" ćelija:
Pokušaj brisanja jedne ili više "podređenih" ćelija neće dovesti do ničega - Excel će ih odmah ponovo izračunati i popuniti.
U isto vrijeme, možemo sa sigurnošću upućivati na ove "djeteće" ćelije 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 regularnim formulama:
Ako trebamo pomjeriti niz, tada će biti dovoljno da ga pomjerimo (mišem ili kombinacijom Ctrl+X, Ctrl+V), opet samo prva glavna ćelija G4 – nakon nje će biti prebačena na novo mjesto i cijeli naš niz će se ponovo proširiti.
Ako trebate upućivati negdje drugdje na listu na kreirani dinamički niz, tada možete koristiti specijalni znak # („funta“) nakon adrese njegove vodeće ćelije:
Na primjer, sada možete jednostavno napraviti padajuću listu u ćeliji koja se odnosi na kreirani dinamički niz:
Greške dinamičkog niza
Ali šta se dešava ako nema dovoljno prostora za proširenje niza ili ako na njegovom putu postoje ćelije koje su već zauzete drugim podacima? Upoznajte fundamentalno novu vrstu grešaka u Excelu – #TRANSFER! (#PROLIJ!):
Kao i uvijek, ako kliknemo na ikonu sa žutim dijamantom i uskličnikom, dobit ćemo detaljnije objašnjenje izvora problema i brzo ćemo pronaći ćelije koje ometaju:
Slične greške će se pojaviti 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 ukazuje na „pametnu“ tabelu kreiranu prečicom na tastaturi Ctrl+T ili by Početna – Formatirajte kao tabelu (Početna — Format kao tabela), tada će naslijediti i svoj glavni kvalitet – automatsko određivanje veličine.
Prilikom dodavanja novih podataka na dno ili na 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 Excel funkcije
U redu, kažeš. Sve je ovo zanimljivo i smiješno. Nema potrebe, kao i ranije, da ručno rastežete formulu s referencom na prvu ćeliju originalnog raspona dolje i desno 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 Excel-a – njegovog mehanizma za izračunavanje. To znači da i druge Excel formule i funkcije koje su nam poznate sada također podržavaju rad s dinamičkim nizovima. Pogledajmo nekoliko primjera kako bismo vam dali predstavu o dubini promjena koje su se dogodile.
Transpose
Za transponovanje raspona (zamjena redova i kolona) Microsoft Excel je oduvijek imao ugrađenu funkciju TRANSP (TRANSPONIRAJ). Međutim, da biste ga koristili, prvo morate ispravno odabrati raspon za rezultate (na primjer, ako je unos bio raspon od 5×3, onda morate odabrati 3×5), zatim ući u funkciju i pritisnuti kombinacija Ctrl+smjena+ući, jer može raditi samo u načinu formule niza.
Sada možete samo odabrati jednu ćeliju, unijeti istu formulu u nju i kliknuti na normalnu ući – dinamički niz će sve učiniti sam:
Tablica množenja
Ovo je primjer koji sam dao kada su me zamolili da vizualiziram prednosti formula polja u Excelu. Sada, za izračunavanje cijele Pitagorine tablice, dovoljno je stati u prvu ćeliju B2, uneti tu formulu koja množi dva niza (vertikalni i horizontalni skup brojeva 1..10) i jednostavno kliknuti na ući:
Lepljenje i konverzija kućišta
Nizovi se ne mogu samo množiti, već i zalijepiti zajedno sa standardnim operatorom & (ampersandom). Pretpostavimo da trebamo izdvojiti ime i prezime iz dvije kolone i ispraviti velika i mala slova u originalnim podacima. To radimo jednom kratkom formulom koja formira cijeli niz, a zatim na njega primjenjujemo funkciju PROPNACH (ISPRAVNO)da sredim registar:
Zaključak Top 3
Pretpostavimo da imamo gomilu brojeva iz kojih želimo da izvedemo prva tri rezultata, raspoređujući ih u opadajućem redosledu. 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 kolonu, već u red, dovoljno je da dvotočke (razdjelnik reda) u ovoj formuli zamijenite tačkom i zarezom (razdjelnik elemenata unutar jednog reda). U engleskoj verziji Excela, ovi separatori su tačke i zarezi.
VLOOKUP izdvaja više kolona odjednom
funkcije VPR (Vlookup) sada možete povući vrijednosti ne iz jedne, već iz nekoliko stupaca odjednom - samo navedite njihove brojeve (u bilo kojem željenom redoslijedu) kao niz u trećem argumentu funkcije:
OFFSET funkcija vraća dinamički niz
Jedna od najzanimljivijih i najkorisnijih (nakon VLOOKUP) funkcija za analizu podataka je funkcija ODSTRANJEVANJE (OFFSET), kojoj sam svojevremeno posvetio cijelo poglavlje u svojoj knjizi i jedan članak ovdje. Teškoća u razumijevanju i savladavanju ove funkcije uvijek je bila to što je kao rezultat vraćala niz (opseg) podataka, ali mi to nismo mogli vidjeti, jer Excel još uvijek nije znao kako da radi sa nizovima izvan kutije.
Sada je ovaj problem u prošlosti. Pogledajte kako sada, koristeći jednu formulu i dinamički niz koje vraća OFFSET, možete izdvojiti sve redove za dati proizvod iz bilo koje sortirane tablice:
Pogledajmo njene argumente:
- A1 – početna ćelija (referentna tačka)
- POISKPOZ(F2;A2:A30;0) – izračunavanje pomaka od početne ćelije naniže – do prvog pronađenog kupusa.
- 0 – pomicanje “prozora” udesno u odnosu na početnu ćeliju
- SČËTESLI(A2:A30;F2) – obračun visine vraćenog „prozora“ – broj redova na kojima se nalazi kupus.
- 4 — veličina „prozora“ horizontalno, tj. izlaz 4 kolone
Nove funkcije za dinamičke nizove
Uz podršku mehanizma dinamičkog niza u starim funkcijama, nekoliko potpuno novih funkcija je dodano u Microsoft Excel, izoštrenih posebno za rad sa dinamičkim nizovima. Konkretno, to su:
- GRADE (SORTIRAJ) – sortira opseg ulaza i proizvodi dinamički niz na izlazu
- SORTPO (SORTIRAJ) – može sortirati jedan raspon po vrijednostima iz drugog
- FILTER (FILTER) – preuzima redove iz izvornog raspona koji ispunjavaju navedene uvjete
- JEDINSTVENO (JEDINSTVENO) – izdvaja jedinstvene vrijednosti iz raspona ili uklanja duplikate
- SLMASSIVE (RANDARRAY) – generiše niz slučajnih brojeva date veličine
- AFTERBIRTH (SEKVENCIJA) — formira niz od niza brojeva sa datim korakom
Više o njima – malo kasnije. Vrijede posebnog članka (a ne jednog) za pažljivo proučavanje 🙂
zaključci
Ako ste pročitali sve gore napisano, onda mislim da već shvatate razmjere promjena koje su se desile. Toliko stvari u Excelu sada se mogu raditi lakše, lakše i logičnije. Moram priznati da sam malo šokiran koliko će sada članaka morati da se ispravljaju ovde, na ovom sajtu i u mojim knjigama, ali spreman sam na to s laganim srcem.
Sumirajući rezultate, prednosti 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 niza" i tretira ih na isti način.
- O funkciji SUMPRODUCT (SUMPROIZVOD), koji se ranije koristio za unos formula niza bez Ctrl+smjena+ući možete i zaboraviti – sada je dovoljno lako SUMA и ući.
- Pametne tabele i poznate funkcije (SUM, IF, VLOOKUP, SUMIFS, itd.) sada takođe u potpunosti ili delimično podržavaju dinamičke nizove.
- Postoji kompatibilnost unatrag: ako otvorite radnu knjigu s dinamičkim nizovima u staroj verziji Excela, oni će se pretvoriti u formule niza (u vitičastim zagradama) i nastaviti raditi u „starom stilu“.
Našao neki broj minusi:
- Ne možete izbrisati pojedinačne redove, stupce ili ćelije iz dinamičkog niza, tj. živi kao jedan entitet.
- Ne možete sortirati dinamički niz na uobičajen način Podaci – Sortiranje (Podaci — sortiranje). Sada postoji posebna funkcija za to. GRADE (SORTIRAJ).
- Dinamički raspon se ne može pretvoriti u pametni sto (ali možete napraviti dinamički raspon na temelju pametnog stola).
Naravno, ovo nije kraj i siguran sam da će Microsoft nastaviti da poboljšava ovaj mehanizam u budućnosti.
Gdje mogu preuzeti?
I na kraju, glavno pitanje 🙂
Microsoft je prvi put najavio i pokazao pregled dinamičkih nizova u Excelu još u septembru 2018. na konferenciji zapaliti. U narednih nekoliko mjeseci došlo je do temeljnog testiranja i uvođenja novih funkcija, prvo mačke zaposlenike samog Microsofta, a zatim i na testere volontera iz kruga Office Insajdera. Ove godine, ažuriranje koje dodaje dinamičke nizove počelo je postepeno da se uvodi redovnim pretplatnicima na Office 365. Na primjer, dobio sam ga tek u avgustu sa svojom pretplatom na Office 365 Pro Plus (mjesečno ciljano).
Ako vaš Excel još nema dinamičke nizove, ali zaista želite raditi s njima, postoje sljedeće opcije:
- Ako imate pretplatu na Office 365, možete jednostavno pričekati dok ovo ažuriranje ne stigne do vas. Koliko će se to brzo dogoditi zavisi od toga koliko često se ažuriranja isporučuju u vaš ured (jednom godišnje, jednom u šest mjeseci, jednom mjesečno). Ako imate poslovni računar, možete zamoliti svog administratora da podesi ažuriranja koja će se preuzimati češće.
- Možete se pridružiti redovima volontera za testiranje Office Insiders – tada ćete biti prvi koji će dobiti sve nove funkcije i funkcije (ali postoji šansa za povećanje grešaka u Excelu, naravno).
- Ako nemate pretplatu, već samostalnu verziju Excela u kutiji, morat ćete pričekati do izlaska sljedeće verzije Officea i Excela barem 2022. godine. Korisnici ovakvih verzija dobijaju samo bezbednosna ažuriranja i ispravke grešaka, a sve nove „dobroće“ sada idu samo pretplatnicima na Office 365. Tužno ali istinito 🙂
U svakom slučaju, kada se dinamički nizovi pojave u vašem Excelu – nakon ovog članka, bit ćete spremni za to 🙂
- Šta su formule niza i kako ih koristiti u Excelu
- Prozor (opseg) sumiranje pomoću funkcije OFFSET
- 3 načina transponovanja tabele u Excel