Turinys
Kas yra dinaminiai masyvai
2018 m. rugsėjį „Microsoft“ išleido naujinimą, kuris į „Microsoft Excel“ prideda visiškai naują įrankį: dinaminius masyvus ir 7 naujas funkcijas, skirtas dirbti su jais. Šie dalykai, be perdėto, radikaliai pakeičia visą įprastą darbo su formulėmis ir funkcijomis techniką ir rūpi, žodžiu, kiekvienam vartotojui.
Apsvarstykite paprastą pavyzdį, kad paaiškintumėte esmę.
Tarkime, kad turime paprastą lentelę su miesto mėnesių duomenimis. Kas atsitiks, jei pasirinksime bet kurį tuščią langelį lapo dešinėje ir įvesime į jį formulę, kuri susieja ne su vienu langeliu, o iš karto su diapazonu?
Visose ankstesnėse „Excel“ versijose spustelėjus įeiti gautume tik vienos pirmosios ląstelės B2 turinį. Kaip kitaip?
Na, arba būtų galima apvilkti šį diapazoną į kokią nors agregavimo funkciją, pvz., =SUM(B2:C4) ir gauti už tai bendrą sumą.
Jei mums prireiktų sudėtingesnių operacijų nei primityvi suma, pvz., išskirti unikalias reikšmes arba Top 3, savo formulę turėtume įvesti kaip masyvo formulę naudodami sparčiuosius klavišus. "Ctrl"+perėjimas+įeiti.
Dabar viskas kitaip.
Dabar, įvedę tokią formulę, galime tiesiog spustelėti įeiti – ir iš karto gaukite visas reikšmes uXNUMXbuXNUMXb, kurias mes nurodėme:
Tai ne magija, o nauji dinaminiai masyvai, kuriuos dabar turi „Microsoft Excel“. Sveiki atvykę į naują pasaulį 🙂
Darbo su dinaminiais masyvais ypatybės
Techniškai visas mūsų dinaminis masyvas yra saugomas pirmajame langelyje G4, užpildydamas reikiamą langelių skaičių į dešinę ir į apačią savo duomenimis. Jei masyve pasirinksite bet kurį kitą langelį, nuoroda formulės juostoje bus neaktyvi, parodydama, kad esame viename iš „vaikų“ langelių:
Bandymas ištrinti vieną ar daugiau „vaikų“ langelių nieko neduos – „Excel“ iš karto juos perskaičiuos ir užpildys.
Tuo pačiu metu šias „vaikų“ ląsteles galime drąsiai vadinti kitose formulėse:
Jei nukopijuosite pirmąjį masyvo langelį (pavyzdžiui, iš G4 į F8), tada visas masyvas (jo nuorodos) judės ta pačia kryptimi, kaip ir įprastose formulėse:
Jei mums reikia perkelti masyvą, užteks jį perkelti (pele arba jų deriniu "Ctrl"+X, "Ctrl"+V), vėlgi tik pirmoji pagrindinė ląstelė G4 – po jos ji bus perkelta į naują vietą ir vėl bus išplėstas visas mūsų masyvas.
Jei kur nors kitur lape reikia nurodyti sukurtą dinaminį masyvą, galite naudoti specialųjį simbolį # („svaras“) po jo pirmaujančio langelio adreso:
Pavyzdžiui, dabar galite lengvai sudaryti išskleidžiamąjį sąrašą langelyje, kuriame nurodomas sukurtas dinaminis masyvas:
Dinaminės masyvo klaidos
Bet kas atsitiks, jei nėra pakankamai vietos išplėsti masyvą arba jei jo kelyje yra langelių, jau užimtų kitų duomenų? Susipažinkite su iš esmės naujo tipo klaidomis programoje „Excel“ – #PERVEDIMAS! (#IŠSILIKIMAS!):
Kaip visada, spustelėję piktogramą su geltonu deimantu ir šauktuku, gausime išsamesnį problemos šaltinio paaiškinimą ir greitai rasime trukdančias ląsteles:
Panašios klaidos atsiras, jei masyvas nukryps nuo lapo arba pateks į sujungtą langelį. Jei pašalinsite kliūtį, viskas bus nedelsiant ištaisyta.
Dinaminiai masyvai ir išmaniosios lentelės
Jei dinaminis masyvas nurodo „protingą“ lentelę, sukurtą sparčiuoju klavišu "Ctrl"+T arba Pagrindinis – formatuoti kaip lentelę (Pagrindinis – formatuoti kaip lentelę), tada jis paveldės ir pagrindinę savybę – automatinį dydžio nustatymą.
Pridedant naujų duomenų apačioje arba dešinėje, išmanioji lentelė ir dinaminis diapazonas taip pat automatiškai išsiplės:
Tačiau yra vienas apribojimas: negalime naudoti dinaminio diapazono nuorodos išmaniosios lentelės forumuose:
Dinaminiai masyvai ir kitos „Excel“ funkcijos
Gerai, sakai. Visa tai įdomu ir juokinga. Nereikia, kaip anksčiau, rankiniu būdu ištempti formulę su nuoroda į pirmąją pradinio diapazono langelį žemyn, į dešinę ir visa kita. Ir viskas?
Ne visai.
Dinaminiai masyvai nėra tik dar vienas „Excel“ įrankis. Dabar jie yra įterpti į pačią Microsoft Excel širdį (arba smegenis) – jos skaičiavimo variklį. Tai reiškia, kad kitos mums žinomos Excel formulės ir funkcijos taip pat palaiko darbą su dinaminiais masyvais. Pažvelkime į kelis pavyzdžius, kad suprastumėte įvykusių pokyčių gilumą.
Perkelti
Norėdami perkelti diapazoną (sukeisti eilutes ir stulpelius), „Microsoft Excel“ visada turėjo integruotą funkciją PRANEŠIMAS (TRANSPONUOTI). Tačiau norėdami ją naudoti, pirmiausia turite teisingai pasirinkti rezultatų diapazoną (pavyzdžiui, jei įvestis buvo diapazonas 5 × 3, tuomet turite pasirinkti 3 × 5), tada įveskite funkciją ir paspauskite derinys "Ctrl"+perėjimas+įeiti, nes jis gali veikti tik masyvo formulės režimu.
Dabar galite tiesiog pasirinkti vieną langelį, įvesti į ją tą pačią formulę ir spustelėti įprastą įeiti – dinaminis masyvas viską padarys pats:
Daugybos lentelė
Tai yra pavyzdys, kurį pateikiau, kai manęs paprašė vizualizuoti „Excel“ masyvo formulių pranašumus. Dabar, norint apskaičiuoti visą Pitagoro lentelę, pakanka atsistoti pirmame langelyje B2, įvesti ten formulę, kuri padaugina du masyvus (vertikali ir horizontali skaičių rinkinys 1...10) ir tiesiog spustelėkite įeiti:
Klijavimas ir korpuso konvertavimas
Masyvus galima ne tik padauginti, bet ir suklijuoti standartiniu operatoriumi & (ampersandu). Tarkime, kad turime iš dviejų stulpelių išskirti vardą ir pavardę ir pataisyti pradinių duomenų šokinėjančią raidę. Tai darome su viena trumpa formule, kuri sudaro visą masyvą, ir tada jai taikome funkciją PROPNACHAS (TINKAMA)sutvarkyti registrą:
Išvada Top 3
Tarkime, kad turime daugybę skaičių, iš kurių norime gauti tris geriausius rezultatus, išdėstydami juos mažėjančia tvarka. Dabar tai daroma pagal vieną formulę ir vėl be jokios "Ctrl"+perėjimas+įeiti kaip ir prieš tai:
Jei norite, kad rezultatai būtų pateikiami ne stulpelyje, o eilėje, tada šioje formulėje dvitaškius (eilutės skyriklį) pakanka pakeisti kabliataškiu (elementų skyriklis vienoje eilutėje). Angliškoje „Excel“ versijoje šie skyrikliai yra atitinkamai kabliataškiai ir kableliai.
VLOOKUP ištraukia kelis stulpelius vienu metu
Funkcijos VPR (ŽIŪRĖTI) dabar galite ištraukti reikšmes ne iš vieno, o iš kelių stulpelių vienu metu – tiesiog nurodykite jų skaičius (bet kokia norima tvarka) kaip masyvą trečiajame funkcijos argumente:
Funkcija OFFSET, grąžinanti dinaminį masyvą
Viena įdomiausių ir naudingiausių (po VLOOKUP) funkcijų duomenų analizei yra funkcija NAIKINIMAS (OFFSET), kuriam vienu metu skyriau visą skyrių savo knygoje ir straipsnį čia. Sunku suprasti ir įsisavinti šią funkciją visada buvo tai, kad ji grąžino duomenų masyvą (diapazoną), bet mes negalėjome jo matyti, nes „Excel“ vis dar nežinojo, kaip dirbti su masyvais.
Dabar ši problema jau praeityje. Pažiūrėkite, kaip dabar, naudodami vieną formulę ir OFFSET grąžintą dinaminį masyvą, galite iš bet kurios surūšiuotos lentelės išgauti visas nurodyto produkto eilutes:
Pažvelkime į jos argumentus:
- A1 – pradžios langelis (atskaitos taškas)
- ПОИСКПОЗ(F2;A2:A30;0) – poslinkio nuo pradinės ląstelės žemyn apskaičiavimas – iki pirmojo rasto kopūsto.
- 0 – „lango“ poslinkis į dešinę pradinės ląstelės atžvilgiu
- СЧЁТЕСЛИ(A2:A30;F2) – grąžinamo „lango“ aukščio skaičiavimas – eilučių skaičius, kur yra kopūstas.
- 4 — „lango“ dydis horizontaliai, ty išvesti 4 stulpelius
Naujos dinaminių masyvų funkcijos
Be dinaminio masyvo mechanizmo palaikymo senose funkcijose, į „Microsoft Excel“ buvo įtrauktos kelios visiškai naujos funkcijos, specialiai pritaikytos darbui su dinaminiais masyvais. Visų pirma tai yra:
- GRADE (RŪšiuoti) – surūšiuoja įvesties diapazoną ir išvestyje sukuria dinaminį masyvą
- SORTPO (RŪŠIUOTI PAGAL) – gali rūšiuoti vieną diapazoną pagal reikšmes nuo kitų
- FILTRAS (FILTRAS) – iš šaltinio diapazono nuskaito eilutes, kurios atitinka nurodytas sąlygas
- UNIKALUS (UNIKALUS) – ištraukia unikalias vertes iš diapazono arba pašalina pasikartojančius duomenis
- SLMASIVUS (RANDARRAY) – generuoja tam tikro dydžio atsitiktinių skaičių masyvą
- PO GIMIMO (SEQUENCE) — sudaro masyvą iš skaičių sekos tam tikru žingsniu
Plačiau apie juos – kiek vėliau. Jie verti atskiro straipsnio (ir ne vieno) dėl apgalvoto tyrimo 🙂
Išvados
Jei perskaitėte viską, kas parašyta aukščiau, tai, manau, jau suvokiate įvykusių pokyčių mastą. Tiek daug dalykų programoje „Excel“ dabar galima atlikti lengviau, lengviau ir logiškiau. Turiu pripažinti, kad esu šiek tiek šokiruotas, kiek straipsnių dabar reikės taisyti čia, šioje svetainėje ir mano knygose, bet esu pasiruošęs tai padaryti lengva širdimi.
Susumavus rezultatus, pliusai dinaminius masyvus, galite parašyti taip:
- Galite pamiršti apie derinį "Ctrl"+perėjimas+įeiti. „Excel“ dabar nemato skirtumo tarp „įprastų formulių“ ir „masyvo formulių“ ir traktuoja jas taip pat.
- Apie funkciją SUMPRODUCT (SUMPRODUCT), kuris anksčiau buvo naudojamas masyvo formulėms įvesti be "Ctrl"+perėjimas+įeiti galite ir pamiršti – dabar tai pakankamai lengva SUMA и įeiti.
- Išmaniosios lentelės ir žinomos funkcijos (SUM, IF, VLOOKUP, SUMIFS ir kt.) dabar taip pat visiškai arba iš dalies palaiko dinaminius masyvus.
- Yra atgalinis suderinamumas: jei atidarysite darbaknygę su dinaminiais masyvais senoje „Excel“ versijoje, jie pavirs masyvo formulėmis (riestiniuose skliaustuose) ir toliau veiks „senuoju stiliumi“.
Radau kažkokį skaičių minusai:
- Negalite ištrinti atskirų eilučių, stulpelių ar langelių iš dinaminio masyvo, ty jis veikia kaip vienas objektas.
- Negalite rūšiuoti dinaminio masyvo įprastu būdu Duomenys – Rūšiavimas (Duomenys – Rūšiuoti). Dabar tam yra speciali funkcija. GRADE (RŪšiuoti).
- Dinaminio diapazono negalima paversti išmaniąja lentele (tačiau galite sukurti dinaminį diapazoną pagal išmaniąją lentelę).
Žinoma, tai dar ne pabaiga, ir esu tikras, kad „Microsoft“ ir toliau tobulins šį mechanizmą ateityje.
Kur galiu parsisiųsti?
Ir galiausiai pagrindinis klausimas 🙂
„Microsoft“ pirmą kartą paskelbė ir parodė „Excel“ dinaminių masyvų peržiūrą 2018 m. rugsėjo mėn. konferencijoje Užsidegti. Per kelis ateinančius mėnesius buvo atliktas išsamus naujų funkcijų išbandymas ir įdiegimas Kačių pačios „Microsoft“ darbuotojų, o vėliau – savanorių testuotojų iš „Office Insiders“ rato. Šiais metais naujinimas, kuris prideda dinaminius masyvus, buvo palaipsniui pradėtas teikti įprastiems „Office 365“ prenumeratoriams. Pavyzdžiui, gavau jį tik rugpjūčio mėn. su „Office 365 Pro Plus“ (Mėnesio tikslinė) prenumerata.
Jei jūsų „Excel“ dar neturi dinaminių masyvų, bet tikrai norite su jais dirbti, yra šios parinktys:
- Jei turite „Office 365“ prenumeratą, galite tiesiog palaukti, kol jus pasieks šis naujinimas. Kaip greitai tai įvyks, priklauso nuo to, kaip dažnai naujinimai pristatomi į jūsų biurą (kartą per metus, kartą per šešis mėnesius, kartą per mėnesį). Jei turite įmonės kompiuterį, galite paprašyti administratoriaus nustatyti, kad naujinimai būtų atsisiunčiami dažniau.
- Galite prisijungti prie tų „Office Insiders“ bandomųjų savanorių gretų – tada būsite pirmieji, kurie gaus visas naujas funkcijas (tačiau, žinoma, „Excel“ klaidų tikimybė yra didesnė).
- Jei turite ne prenumeratą, o atskirą „Excel“ versiją, turėsite palaukti, kol bent jau bus išleista kita „Office“ ir „Excel“ versija 2022 m. Tokių versijų vartotojai gauna tik saugos naujinimus ir klaidų pataisymus, o visos naujos „gėrybės“ dabar atitenka tik „Office 365“ prenumeratoriams. Liūdna, bet tiesa 🙂
Bet kokiu atveju, kai jūsų „Excel“ pasirodys dinaminiai masyvai – po šio straipsnio būsite tam pasiruošę 🙂
- Kas yra masyvo formulės ir kaip jas naudoti „Excel“.
- Lango (diapazono) sumavimas naudojant OFFSET funkciją
- 3 būdai, kaip perkelti lentelę į „Excel“.