Nilalaman
Ano ang mga dynamic na array
Noong Setyembre 2018, naglabas ang Microsoft ng update na nagdaragdag ng ganap na bagong tool sa Microsoft Excel: Dynamic Arrays at 7 bagong function para sa pagtatrabaho sa kanila. Ang mga bagay na ito, nang walang pagmamalabis, ay radikal na nagbabago sa lahat ng karaniwang pamamaraan ng pagtatrabaho sa mga formula at function at pag-aalala, literal, bawat gumagamit.
Isaalang-alang ang isang simpleng halimbawa upang ipaliwanag ang kakanyahan.
Ipagpalagay na mayroon kaming isang simpleng talahanayan na may data sa mga buwan ng lungsod. Ano ang mangyayari kung pipili tayo ng anumang walang laman na cell sa kanan ng sheet at ipasok dito ang isang formula na nagli-link hindi sa isang cell, ngunit kaagad sa isang saklaw?
Sa lahat ng nakaraang bersyon ng Excel, pagkatapos mag-click sa Magpasok makukuha natin ang mga nilalaman ng isang unang cell B2 lamang. Paano pa?
Well, o posibleng i-wrap ang range na ito sa ilang uri ng pinagsama-samang function tulad ng =SUM(B2:C4) at makakuha ng malaking kabuuan para dito.
Kung kailangan namin ng mas kumplikadong mga operasyon kaysa sa isang primitive na kabuuan, tulad ng pagkuha ng mga natatanging halaga o Nangungunang 3, pagkatapos ay kailangan naming ilagay ang aming formula bilang isang array formula gamit ang isang keyboard shortcut Ctrl+Ilipat+Magpasok.
Ngayon iba na ang lahat.
Ngayon pagkatapos na ipasok ang gayong formula, maaari na lamang nating i-click Magpasok – at makuha kaagad bilang resulta ang lahat ng mga halaga uXNUMXbuXNUMXbto na aming tinukoy:
Hindi ito magic, ngunit ang mga bagong dynamic na array na mayroon na ngayon sa Microsoft Excel. Maligayang pagdating sa bagong mundo 🙂
Mga tampok ng pagtatrabaho sa mga dynamic na array
Sa teknikal, ang aming buong dynamic na array ay nakaimbak sa unang cell G4, na pinupunan ang kinakailangang bilang ng mga cell sa kanan at pababa ng data nito. Kung pipili ka ng anumang iba pang cell sa array, ang link sa formula bar ay magiging hindi aktibo, na nagpapakita na kami ay nasa isa sa mga "bata" na cell:
Ang pagtatangkang tanggalin ang isa o higit pang "bata" na mga cell ay hindi hahantong sa anumang bagay - Kaagad na muling kalkulahin ng Excel at pupunuin ang mga ito.
Kasabay nito, maaari naming ligtas na sumangguni sa mga cell na ito ng "bata" sa iba pang mga formula:
Kung kopyahin mo ang unang cell ng isang array (halimbawa, mula sa G4 hanggang F8), ang buong array (mga sanggunian nito) ay lilipat sa parehong direksyon tulad ng sa mga regular na formula:
Kung kailangan nating ilipat ang array, kung gayon ito ay sapat na upang ilipat (gamit ang mouse o isang kumbinasyon ng Ctrl+X, Ctrl+V), muli, ang unang pangunahing cell G4 lamang - pagkatapos nito, ililipat ito sa isang bagong lugar at ang aming buong hanay ay mapapalawak muli.
Kung kailangan mong sumangguni sa ibang lugar sa sheet sa nilikhang dynamic na array, maaari mong gamitin ang espesyal na character # ("pound") pagkatapos ng address ng nangungunang cell nito:
Halimbawa, ngayon ay madali kang makakagawa ng listahan ng dropdown sa isang cell na tumutukoy sa nilikhang dynamic na array:
Mga error sa dynamic na array
Ngunit ano ang mangyayari kung walang sapat na espasyo upang palawakin ang array, o kung may mga cell na inookupahan na ng ibang data sa landas nito? Matugunan ang isang panimula na bagong uri ng mga error sa Excel - #ILIPAT! (#SPILL!):
Gaya ng dati, kung mag-click kami sa icon na may dilaw na brilyante at tandang padamdam, makakakuha kami ng mas detalyadong paliwanag sa pinagmulan ng problema at mabilis kaming makakahanap ng mga nakakasagabal na cell:
Ang mga katulad na error ay magaganap kung ang array ay umalis sa sheet o tumama sa isang pinagsamang cell. Kung aalisin mo ang balakid, ang lahat ay agad na maitama sa mabilisang.
Mga dynamic na array at smart table
Kung ang dynamic na array ay tumuturo sa isang "smart" na talahanayan na ginawa ng isang keyboard shortcut Ctrl+T o sa pamamagitan ng Tahanan – I-format bilang isang talahanayan (Home — Format bilang Talahanayan), pagkatapos ay mamanahin din nito ang pangunahing kalidad nito – ang auto-sizing.
Kapag nagdadagdag ng bagong data sa ibaba o sa kanan, ang matalinong talahanayan at dynamic na hanay ay awtomatikong mag-uunat:
Gayunpaman, mayroong isang limitasyon: hindi kami maaaring gumamit ng dynamic na saklaw na sanggunian sa mga forum sa loob ng isang matalinong talahanayan:
Mga dynamic na array at iba pang feature ng Excel
Okay, sabi mo. Ang lahat ng ito ay kawili-wili at nakakatawa. Hindi na kailangan, tulad ng dati, na manu-manong i-stretch ang formula na may reference sa unang cell ng orihinal na hanay pababa at sa kanan at lahat ng iyon. At yun lang?
Hindi masyadong.
Ang mga dynamic na array ay hindi lamang isa pang tool sa Excel. Ngayon sila ay naka-embed sa pinakapuso (o utak) ng Microsoft Excel - ang makina ng pagkalkula nito. Nangangahulugan ito na ang ibang mga formula at function ng Excel na pamilyar sa amin ngayon ay sumusuporta din sa pagtatrabaho sa mga dynamic na array. Tingnan natin ang ilang halimbawa upang mabigyan ka ng ideya ng lalim ng mga pagbabagong naganap.
I-transpose
Upang i-transpose ang isang range (magpalit ng mga row at column) Ang Microsoft Excel ay palaging may built-in na function TRANSP (TRANSPOSE). Gayunpaman, upang magamit ito, kailangan mo munang piliin nang tama ang hanay para sa mga resulta (halimbawa, kung ang input ay isang saklaw na 5×3, dapat ay pinili mo ang 3×5), pagkatapos ay ipasok ang function at pindutin ang kumbinasyon Ctrl+Ilipat+Magpasok, dahil maaari lang itong gumana sa array formula mode.
Ngayon ay maaari ka lamang pumili ng isang cell, ipasok ang parehong formula dito at mag-click sa normal Magpasok – gagawin ng dynamic na array ang lahat nang mag-isa:
Talaan ng multiplikasyon
Ito ang halimbawang ibinigay ko noong hiniling sa akin na ilarawan ang mga benepisyo ng mga array formula sa Excel. Ngayon, upang kalkulahin ang buong talahanayan ng Pythagorean, sapat na upang tumayo sa unang cell B2, ipasok doon ang isang formula na nagpaparami ng dalawang arrays (vertical at horizontal set ng mga numero 1..10) at mag-click lamang sa Magpasok:
Gluing at case conversion
Ang mga array ay hindi lamang maaaring paramihin, ngunit din nakadikit kasama ang karaniwang operator & (ampersand). Ipagpalagay na kailangan nating kunin ang una at apelyido mula sa dalawang column at itama ang jumping case sa orihinal na data. Ginagawa namin ito gamit ang isang maikling formula na bumubuo sa buong array, at pagkatapos ay inilalapat namin ang function dito PROPNACH (PROPER)para ayusin ang rehistro:
Konklusyon Nangungunang 3
Ipagpalagay na mayroon kaming isang bungkos ng mga numero kung saan gusto naming makuha ang nangungunang tatlong resulta, na inaayos ang mga ito sa pababang pagkakasunud-sunod. Ngayon ito ay ginagawa ng isang pormula at, muli, walang anuman Ctrl+Ilipat+Magpasok tulad ng dati:
Kung nais mong mailagay ang mga resulta hindi sa isang hanay, ngunit sa isang hilera, kung gayon ito ay sapat na upang palitan ang mga colon (line separator) sa formula na ito ng isang semicolon (element separator sa loob ng isang linya). Sa English na bersyon ng Excel, ang mga separator na ito ay mga semicolon at mga kuwit, ayon sa pagkakabanggit.
Kinukuha ng VLOOKUP ang maraming column nang sabay-sabay
Pag-andar VPR (VLOOKUP) Ngayon ay maaari kang kumuha ng mga halaga hindi mula sa isa, ngunit mula sa ilang mga haligi nang sabay-sabay - tukuyin lamang ang kanilang mga numero (sa anumang nais na pagkakasunud-sunod) bilang isang array sa ikatlong argumento ng function:
OFFSET function na nagbabalik ng isang dynamic na array
Ang isa sa mga pinaka-kawili-wili at kapaki-pakinabang (pagkatapos ng VLOOKUP) na function para sa pagsusuri ng data ay ang function TANGGALIN (OFFSET), kung saan inilaan ko minsan ang isang buong kabanata sa aking aklat at isang artikulo dito. Ang kahirapan sa pag-unawa at pag-master ng function na ito ay palaging nagbabalik ito ng array (range) ng data bilang resulta, ngunit hindi namin ito makita, dahil hindi pa rin alam ng Excel kung paano gumawa ng mga arrays sa labas ng kahon.
Ngayon ang problemang ito ay nasa nakaraan. Tingnan kung paano ngayon, gamit ang isang formula at isang dynamic na array na ibinalik ng OFFSET, maaari mong i-extract ang lahat ng row para sa isang partikular na produkto mula sa anumang pinagsunod-sunod na talahanayan:
Tingnan natin ang kanyang mga argumento:
- A1 – panimulang cell (reference point)
- ПОИСКПОЗ(F2;A2:A30;0) – pagkalkula ng shift mula sa panimulang cell pababa – hanggang sa unang natagpuang repolyo.
- 0 – paglipat ng "window" sa kanan na may kaugnayan sa panimulang cell
- СЧЁТЕСЛИ(A2:A30;F2) – pagkalkula ng taas ng ibinalik na "window" - ang bilang ng mga linya kung saan mayroong repolyo.
- 4 — laki ng “window” nang pahalang, ibig sabihin, 4 na column ang output
Mga Bagong Function para sa Mga Dynamic na Array
Bilang karagdagan sa pagsuporta sa dynamic na mekanismo ng array sa mga lumang function, ilang ganap na bagong function ang naidagdag sa Microsoft Excel, partikular na pinatalas para sa pagtatrabaho sa mga dynamic na array. Sa partikular, ang mga ito ay:
- GRADO (SORT) – inaayos ang saklaw ng input at gumagawa ng isang dynamic na array sa output
- SORTPO (SORTBY) – maaaring pag-uri-uriin ang isang saklaw ayon sa mga halaga mula sa isa pa
- FILTER (FILTER) – kinukuha ang mga row mula sa source range na nakakatugon sa mga tinukoy na kundisyon
- UNIK (NATATANGI) – kinukuha ang mga natatanging halaga mula sa isang saklaw o inaalis ang mga duplicate
- SLMASSIVE (RANDARRAY) - bumubuo ng isang hanay ng mga random na numero ng isang ibinigay na laki
- PAGKATAPOS NG KApanganakan (SEQUENCE) — bubuo ng array mula sa pagkakasunod-sunod ng mga numero na may ibinigay na hakbang
Higit pa tungkol sa kanila – ilang sandali pa. Ang mga ito ay nagkakahalaga ng isang hiwalay na artikulo (at hindi isa) para sa maalalahanin na pag-aaral 🙂
Konklusyon
Kung nabasa mo na ang lahat ng nakasulat sa itaas, sa palagay ko ay napagtanto mo na ang laki ng mga pagbabagong naganap. Napakaraming bagay sa Excel ang magagawa nang mas madali, mas madali at mas lohikal. Dapat kong aminin na medyo nabigla ako sa kung gaano karaming mga artikulo ang kailangang itama dito, sa site na ito at sa aking mga libro, ngunit handa akong gawin ito nang may magaan na puso.
Pagbubuod ng mga resulta, pluses mga dynamic na array, maaari mong isulat ang sumusunod:
- Maaari mong kalimutan ang tungkol sa kumbinasyon Ctrl+Ilipat+Magpasok. Wala na ngayong nakikitang pagkakaiba ang Excel sa pagitan ng "mga regular na formula" at "mga array formula" at tinatrato ang mga ito sa parehong paraan.
- Tungkol sa function SUMPRODUCT (SUMPRODUCT), na dating ginamit upang magpasok ng mga formula ng array nang wala Ctrl+Ilipat+Magpasok maaari mo ring kalimutan – ngayon ito ay sapat na madali SUM и Magpasok.
- Ang mga smart table at pamilyar na function (SUM, IF, VLOOKUP, SUMIFS, atbp.) ay ganap na o bahagyang sumusuporta sa mga dynamic na array.
- Mayroong pabalik na compatibility: kung magbubukas ka ng workbook na may mga dynamic na array sa isang lumang bersyon ng Excel, magiging array formula ang mga ito (sa curly braces) at patuloy na gagana sa "lumang istilo".
Nakahanap ng ilang numero mga minus:
- Hindi mo maaaring tanggalin ang mga indibidwal na row, column o cell mula sa isang dynamic na array, ibig sabihin, nabubuhay ito bilang isang entity.
- Hindi mo maaaring pag-uri-uriin ang isang dynamic na array sa karaniwang paraan Data – Pag-uuri (Data — Pagbukud-bukurin). Mayroon na ngayong isang espesyal na function para dito. GRADO (SORT).
- Ang isang dynamic na hanay ay hindi maaaring gawing isang matalinong talahanayan (ngunit maaari kang gumawa ng isang dynamic na hanay batay sa isang matalinong talahanayan).
Siyempre, hindi ito ang katapusan, at sigurado ako na patuloy na pagbubutihin ng Microsoft ang mekanismong ito sa hinaharap.
Saan ako makakapag-download?
At sa wakas, ang pangunahing tanong 🙂
Unang inihayag at ipinakita ng Microsoft ang isang preview ng mga dynamic na array sa Excel noong Setyembre 2018 sa isang kumperensya Papag-apuyin. Sa susunod na ilang buwan, nagkaroon ng masusing pagsubok at pagpapatakbo ng mga bagong feature, una cats mga empleyado ng Microsoft mismo, at pagkatapos ay sa mga volunteer tester mula sa bilog ng Office Insiders. Ngayong taon, ang update na nagdaragdag ng mga dynamic na array ay nagsimulang unti-unting ilunsad sa mga regular na subscriber ng Office 365. Halimbawa, natanggap ko lang ito noong Agosto kasama ang aking subscription sa Office 365 Pro Plus (Buwanang Naka-target).
Kung ang iyong Excel ay wala pang mga dynamic na array, ngunit gusto mo talagang magtrabaho kasama ang mga ito, mayroong mga sumusunod na opsyon:
- Kung mayroon kang subscription sa Office 365, maaari kang maghintay hanggang makarating sa iyo ang update na ito. Kung gaano ito kabilis mangyari ay depende sa kung gaano kadalas inihahatid ang mga update sa iyong Opisina (isang beses sa isang taon, isang beses bawat anim na buwan, isang beses sa isang buwan). Kung mayroon kang corporate PC, maaari mong hilingin sa iyong administrator na mag-set up ng mga update na mada-download nang mas madalas.
- Maaari kang sumali sa hanay ng mga boluntaryong pagsubok ng Office Insiders na iyon – kung gayon ikaw ang unang makakatanggap ng lahat ng mga bagong feature at function (ngunit may posibilidad na tumaas ang buggy sa Excel, siyempre).
- Kung wala kang subscription, ngunit isang naka-box na standalone na bersyon ng Excel, kakailanganin mong maghintay hanggang sa paglabas ng susunod na bersyon ng Office at Excel sa 2022, hindi bababa sa. Ang mga gumagamit ng naturang mga bersyon ay tumatanggap lamang ng mga update sa seguridad at pag-aayos ng bug, at ang lahat ng mga bagong "goodies" ay napupunta lamang sa mga subscriber ng Office 365. Malungkot pero totoo 🙂
Sa anumang kaso, kapag lumitaw ang mga dynamic na array sa iyong Excel – pagkatapos ng artikulong ito, magiging handa ka para dito 🙂
- Ano ang mga array formula at kung paano gamitin ang mga ito sa Excel
- Window (range) summation gamit ang OFFSET function
- 3 Mga Paraan sa Pag-transpose ng Table sa Excel