Sisu
Mis on dünaamilised massiivid
2018. aasta septembris andis Microsoft välja värskenduse, mis lisab Microsoft Excelisse täiesti uue tööriista: dünaamilised massiivid ja 7 uut funktsiooni nendega töötamiseks. Need asjad muudavad liialdamata radikaalselt kogu tavalist valemite ja funktsioonidega töötamise tehnikat ning puudutavad sõna otseses mõttes iga kasutajat.
Vaatleme olemuse selgitamiseks lihtsat näidet.
Oletame, et meil on lihtne tabel linnakuude andmetega. Mis juhtub, kui valime lehelt paremalt suvalise tühja lahtri ja sisestame sellesse valemi, mis ei seo mitte ühe lahtriga, vaid kohe vahemikuga?
Kõigis varasemates Exceli versioonides pärast klõpsamist sisene saaksime ainult ühe esimese lahtri B2 sisu. Kuidas muidu?
Noh, või oleks võimalik mähkida see vahemik mingisse koondamisfunktsiooni nagu =SUM(B2:C4) ja saada selle eest üldsumma.
Kui vajaksime keerukamaid toiminguid kui primitiivne summa, näiteks unikaalsete väärtuste või Top 3 ekstraheerimine, siis peaksime sisestama oma valemi massiivivalemina, kasutades kiirklahvi Ctrl+nihe+sisene.
Nüüd on kõik teisiti.
Nüüd pärast sellise valemi sisestamist saame lihtsalt klõpsata sisene – ja selle tulemusel saate kohe kõik väärtused uXNUMXbuXNUMXb, millele viitasime:
See pole maagia, vaid uued dünaamilised massiivid, mis Microsoft Excelil nüüd olemas on. Tere tulemast uude maailma 🙂
Dünaamiliste massiividega töötamise omadused
Tehniliselt on kogu meie dünaamiline massiiv salvestatud esimesse lahtrisse G4, täites oma andmetega vajaliku arvu lahtreid paremale ja alla. Kui valite massiivist mõne muu lahtri, on valemiriba link mitteaktiivne, näidates, et asume ühes "lapse" lahtris:
Katse kustutada üks või mitu „lapslahtrit” ei too kaasa midagi – Excel arvutab need kohe ümber ja täidab.
Samal ajal võime ohutult viidata nendele "lapsrakkudele" teistes valemites:
Kui kopeerite massiivi esimese lahtri (näiteks G4-st F8-sse), liigub kogu massiiv (selle viited) samas suunas nagu tavavalemites:
Kui peame massiivi liigutama, piisab liigutamisest (hiire või nende kombinatsiooniga Ctrl+X, Ctrl+V), jällegi ainult esimene põhilahter G4 – pärast seda viiakse see uude kohta ja kogu meie massiiv laieneb uuesti.
Kui teil on vaja loodud dünaamilisele massiivile viidata lehel kuhugi mujale, saate selle juhtlahtri aadressi järel kasutada erimärki # ("nael"):
Näiteks saate nüüd hõlpsasti luua lahtrisse rippmenüü, mis viitab loodud dünaamilisele massiivile:
Dünaamilise massiivi vead
Aga mis juhtub, kui massiivi laiendamiseks pole piisavalt ruumi või kui selle teel on lahtreid, mis on juba hõivatud muude andmetega? Tutvuge Excelis täiesti uut tüüpi vigadega – #ÜLEKANNE! (#SPILL!):
Nagu ikka, kui klõpsame kollase rombi ja hüüumärgiga ikooni, saame täpsema selgituse probleemi allika kohta ja leiame kiiresti segavad lahtrid:
Sarnased vead ilmnevad ka siis, kui massiiv läheb lehelt välja või tabab ühendatud lahtrit. Kui eemaldate takistuse, parandatakse kõik kohe käigu pealt.
Dünaamilised massiivid ja nutikad tabelid
Kui dünaamiline massiiv osutab klaviatuuri otsetee abil loodud nutikale tabelile Ctrl+T või Avaleht – vorminda tabelina (Avaleht — vorminda tabelina), siis pärib see ka oma peamise kvaliteedi – automaatse suuruse.
Uute andmete lisamisel alla või paremale venivad automaatselt ka nutikas tabel ja dünaamiline ulatus:
Siiski on üks piirang: me ei saa kasutada nutika tabeli foorumites dünaamilise ulatuse viidet:
Dünaamilised massiivid ja muud Exceli funktsioonid
Olgu, sa ütled. Kõik see on huvitav ja naljakas. Pole vaja, nagu varem, käsitsi venitada valemit viitega algse vahemiku esimesele lahtrile alla ja paremale ja kõik muu. Ja see on kõik?
Mitte päris.
Dünaamilised massiivid ei ole lihtsalt üks Exceli tööriist. Nüüd on need põimitud Microsoft Exceli – selle arvutusmootori – südamesse (või ajusse). See tähendab, et dünaamiliste massiividega töötamist toetavad ka teised meile tuttavad Exceli valemid ja funktsioonid. Vaatleme mõnda näidet, et anda teile aimu toimunud muutuste sügavusest.
Üleminek
Vahemiku transponeerimiseks (ridade ja veergude vahetamiseks) on Microsoft Excelil alati olnud sisseehitatud funktsioon ÜLEKANDMINE (TRANSPOSEERIDA). Selle kasutamiseks tuleb aga kõigepealt õigesti valida tulemuste vahemik (näiteks kui sisendiks oli vahemik 5×3, siis peab olema valitud 3×5), seejärel sisesta funktsioon ja vajuta kombinatsioon Ctrl+nihe+sisene, sest see sai töötada ainult massiivivalemi režiimis.
Nüüd saate lihtsalt valida ühe lahtri, sisestada sellesse sama valemi ja klõpsata tavalisel sisene - dünaamiline massiiv teeb kõik ise:
Korrutustabel
See on näide, mida ma kasutasin, kui mul paluti visualiseerida Exceli massiivivalemite eeliseid. Nüüd piisab kogu Pythagorase tabeli arvutamiseks seismisest esimeses lahtris B2, sisestage sinna valem, mis korrutab kaks massiivi (vertikaalne ja horisontaalne arvude komplekt 1...10) ja klõpsake lihtsalt nuppu sisene:
Liimimine ja korpuse teisendamine
Massiive ei saa mitte ainult korrutada, vaid ka standardoperaatoriga & (ampersand) kokku liimida. Oletame, et peame kahest veerust eraldama ees- ja perekonnanime ning parandama algandmetes hüppavat tähte. Teeme seda ühe lühikese valemiga, mis moodustab kogu massiivi, ja seejärel rakendame sellele funktsiooni PROPNAAT (KORRALIK)registri korrastamiseks:
Kokkuvõte Top 3
Oletame, et meil on hunnik numbreid, millest tahame tuletada kolm parimat tulemust, korraldades need kahanevas järjekorras. Nüüd tehakse seda ühe valemiga ja jällegi ilma ühegita Ctrl+nihe+sisene nagu enne:
Kui soovite, et tulemused paigutataks mitte veergu, vaid ritta, siis piisab selles valemis koolonite (reaeraldaja) asendamisest semikooloniga (elemendi eraldaja ühe rea sees). Exceli ingliskeelses versioonis on need eraldajad vastavalt semikoolonid ja komad.
VLOOKUP ekstraheerib korraga mitu veergu
Funktsioonid VPR (OTSING) nüüd saate väärtusi tõmmata mitte ühest, vaid mitmest veerust korraga – lihtsalt määrake nende numbrid (mis tahes soovitud järjekorras) massiivina funktsiooni kolmandas argumendis:
Funktsioon OFFSET, mis tagastab dünaamilise massiivi
Üks huvitavamaid ja kasulikumaid (pärast VLOOKUP-i) andmeanalüüsi funktsioone on funktsioon KÕRVALDAMINE (NIHE), millele pühendasin omal ajal terve peatüki oma raamatus ja artikli siin. Selle funktsiooni mõistmise ja valdamise raskused on alati seisnenud selles, et see andis tulemuseks andmemassiivi (vahemiku), kuid me ei näinud seda, sest Excel ei teadnud ikka veel, kuidas massiividega töötada.
Nüüd on see probleem minevik. Vaadake, kuidas saate nüüd, kasutades ühte valemit ja OFFSET-i tagastatud dünaamilist massiivi, mis tahes sorteeritud tabelist antud toote kõik read ekstraheerida:
Vaatame tema argumente:
- A1 - alguslahter (võrdluspunkt)
- ПОИСКПОЗ(F2;A2:A30;0) – nihke arvutamine alglahtrist alla – esimesele leitud kapsale.
- 0 – “akna” nihutamine alglahtri suhtes paremale
- СЧЁТЕСЛИ(A2:A30;F2) – tagastatud “akna” kõrguse arvutamine – ridade arv, kus on kapsas.
- 4 — “akna” suurus horisontaalselt, st väljund 4 veergu
Dünaamiliste massiivide uued funktsioonid
Lisaks dünaamilise massiivi mehhanismi toetamisele vanades funktsioonides on Microsoft Excelisse lisatud mitmeid täiesti uusi funktsioone, mis on teravdatud spetsiaalselt dünaamiliste massiividega töötamiseks. Eelkõige on need järgmised:
- GRADE (SORT) – sorteerib sisendvahemiku ja loob väljundis dünaamilise massiivi
- SORTPO (SORTEERIMA) – saab sortida ühte vahemikku väärtuste järgi teisest
- FILTER (FILTER) – otsib lähtevahemikust read, mis vastavad määratud tingimustele
- UNIK (UNIKALNE) – eraldab vahemikust ainulaadsed väärtused või eemaldab duplikaadid
- SLMASSIIVNE (RANDARRAY) – genereerib teatud suurusega juhuslike arvude massiivi
- JÄRELSÜNNITUS (SEQUENCE) — moodustab antud sammuga arvujadast massiivi
Nendest lähemalt – veidi hiljem. Need on läbimõeldud uurimiseks väärt eraldi artiklit (ja mitte ühte) 🙂
Järeldused
Kui olete kõik ülalkirjeldatud läbi lugenud, siis arvan, et te juba mõistate toimunud muutuste ulatust. Nii palju asju saab Excelis nüüd teha lihtsamalt, lihtsamalt ja loogilisemalt. Pean tunnistama, et olen pisut šokeeritud, kui palju artikleid tuleb nüüd siin, sellel saidil ja minu raamatutes parandada, kuid olen valmis seda kerge südamega tegema.
Tulemusi kokku võttes, plussid dünaamilised massiivid, saate kirjutada järgmise:
- Võite kombinatsiooni unustada Ctrl+nihe+sisene. Excel ei näe nüüd „tavaliste valemite” ja „massiivivalemite” vahel erinevust ning käsitleb neid samamoodi.
- Funktsiooni kohta SUMPRODUCT (TOODE SUMMA), mida varem kasutati massiivivalemite sisestamiseks ilma Ctrl+nihe+sisene võite ka unustada – nüüd on see piisavalt lihtne SUM и sisene.
- Nutikad tabelid ja tuttavad funktsioonid (SUM, IF, VLOOKUP, SUMIFS jne) toetavad nüüd ka täielikult või osaliselt dünaamilisi massiive.
- Tagasiühilduvus on olemas: kui avate Exceli vanas versioonis dünaamiliste massiividega töövihiku, muutuvad need massiivivalemiteks (lokkides sulgudes) ja jätkavad tööd "vanas stiilis".
Leidsin mingi numbri miinused:
- Dünaamilisest massiivist ei saa kustutada üksikuid ridu, veerge ega lahtreid, st see elab ühe üksusena.
- Dünaamilist massiivi ei saa tavapärasel viisil sortida Andmed – sorteerimine (Andmed — sortimine). Nüüd on selleks spetsiaalne funktsioon. GRADE (SORT).
- Dünaamilist vahemikku ei saa muuta nutikaks tabeliks (aga saate teha dünaamilise vahemiku nutika tabeli põhjal).
Muidugi pole see veel lõpp ja olen kindel, et Microsoft jätkab selle mehhanismi täiustamist ka tulevikus.
Kust ma saan alla laadida?
Ja lõpuks põhiküsimus 🙂
Microsoft teatas ja näitas Exceli dünaamiliste massiivide eelvaadet esmakordselt 2018. aasta septembris ühel konverentsil Süttima. Järgmise paari kuu jooksul toimus uute funktsioonide põhjalik testimine ja käivitamine, esmalt kassid Microsofti enda töötajad ja seejärel Office Insidersi ringi vabatahtlikud testijad. Sel aastal hakati dünaamilisi massiive lisavat värskendust järk-järgult Office 365 tavatellijatele levitama. Näiteks sain selle alles augustis oma Office 365 Pro Plus (monthly Targeted) tellimusega.
Kui teie Excelil pole veel dünaamilisi massiive, kuid soovite tõesti nendega töötada, on järgmised valikud.
- Kui teil on Office 365 tellimus, võite lihtsalt oodata, kuni see värskendus teieni jõuab. Kui kiiresti see juhtub, sõltub sellest, kui sageli teie Office'i värskendusi toimetatakse (kord aastas, kord kuue kuu jooksul, kord kuus). Kui teil on ettevõtte arvuti, võite paluda administraatoril seadistada värskendusi sagedamini allalaadimiseks.
- Võite liituda nende Office Insidersi testimisvabatahtlike ridadega – siis saate esimesena kõik uued funktsioonid ja funktsioonid (kuid Excelis on muidugi suurem lollakas võimalus).
- Kui teil pole tellimust, vaid Exceli eraldiseisev kastiversioon, peate ootama vähemalt Office'i ja Exceli järgmise versiooni ilmumiseni 2022. aastal. Selliste versioonide kasutajad saavad ainult turbevärskendusi ja veaparandusi ning kõik uued "maiustused" lähevad nüüd ainult Office 365 tellijatele. Kurb, aga tõsi 🙂
Igal juhul, kui teie Excelis ilmuvad dünaamilised massiivid – pärast seda artiklit olete selleks valmis 🙂
- Mis on massiivivalemid ja kuidas neid Excelis kasutada
- Akna (vahemiku) liitmine funktsiooni OFFSET abil
- 3 võimalust tabeli ülekandmiseks Excelis