Kaundan
Unsa ang mga dinamikong arrays
Niadtong Septembre 2018, gipagawas sa Microsoft ang usa ka update nga nagdugang usa ka hingpit nga bag-ong himan sa Microsoft Excel: Dynamic Arrays ug 7 nga bag-ong mga gimbuhaton alang sa pagtrabaho kauban nila. Kini nga mga butang, nga walay pagpasobra, radikal nga nagbag-o sa tanan nga naandan nga pamaagi sa pagtrabaho sa mga pormula ug mga gimbuhaton ug kabalaka, sa literal, matag tiggamit.
Binagbinaga ang isa ka simple nga halimbawa para ipaathag ang esensya.
Ibutang ta nga kita adunay usa ka yano nga lamesa nga adunay datos sa mga bulan sa lungsod. Unsa ang mahitabo kung mopili kita og bisan unsang walay sulod nga cell sa tuo sa sheet ug mosulod niini og usa ka pormula nga dili mag-link sa usa ka cell, apan diha-diha dayon ngadto sa usa ka range?
Sa tanan nga miaging mga bersyon sa Excel, pagkahuman sa pag-klik sa Pagsulod makuha nato ang sulod sa usa lang ka unang cell B2. Unsaon pa man?
Aw, o posible nga i-wrap kini nga range sa usa ka matang sa aggregating function sama sa =SUM(B2:C4) ug makakuha usa ka grand total alang niini.
Kung kinahanglan namon ang labi ka komplikado nga mga operasyon kaysa usa ka primitive nga kantidad, sama sa pagkuha sa talagsaon nga mga kantidad o Top 3, nan kinahanglan namon nga mosulod sa among pormula ingon usa ka pormula sa array gamit ang usa ka shortcut sa keyboard. Ctrl+pagbalhin+Pagsulod.
Karon lahi na ang tanan.
Karon pagkahuman sa pagsulod sa ingon nga pormula, mahimo ra naton i-klik Pagsulod - ug makuha dayon ang tanan nga mga kantidad uXNUMXbuXNUMXbto nga among gipasabut:
Dili kini salamangka, apan ang bag-ong mga dinamikong arrays nga naa karon sa Microsoft Excel. Welcome sa bag-ong kalibutan 🙂
Mga bahin sa pagtrabaho sa mga dinamikong arrays
Sa teknikal nga paagi, ang among tibuok nga dinamikong array gitipigan sa unang cell G4, nga nagpuno sa gikinahanglan nga gidaghanon sa mga selula sa tuo ug ubos sa datos niini. Kung magpili ka ug lain nga cell sa array, nan ang link sa formula bar mahimong dili aktibo, nga nagpakita nga naa kami sa usa sa mga "bata" nga mga cell:
Ang pagsulay sa pagtangtang sa usa o daghang mga "bata" nga mga selyula dili mosangpot sa bisan unsang butang - Ang Excel dayon mag-recalculate ug pun-on kini.
Sa samang higayon, luwas kitang makatumong niining mga “bata” nga mga selula sa ubang mga pormula:
Kon imong kopyahon ang unang cell sa array (pananglitan, gikan sa G4 ngadto sa F8), nan ang tibuok array (mga reference niini) mobalhin sa samang direksyon sama sa regular nga mga pormula:
Kung kinahanglan naton nga ibalhin ang laray, nan kini igo na nga molihok (uban ang mouse o kombinasyon sa Ctrl+X, Ctrl+V), pag-usab, ang una lamang nga nag-unang cell G4 - pagkahuman niini, kini ibalhin sa usa ka bag-ong lugar ug ang among tibuuk nga array mapalapad pag-usab.
Kung kinahanglan nimo nga i-refer ang laing lugar sa sheet sa gibuhat nga dinamikong array, nan mahimo nimong gamiton ang espesyal nga karakter # ("pound") pagkahuman sa adres sa nag-unang cell niini:
Pananglitan, karon dali ka makahimo og usa ka dropdown list sa usa ka cell nga nagtumong sa gibuhat nga dinamikong array:
Dynamic array nga mga sayop
Apan unsa ang mahitabo kung walay igo nga luna aron mapalapad ang laray, o kung adunay mga selula nga giokupar na sa ubang mga datos sa agianan niini? Himamata ang usa ka sukaranan nga bag-ong tipo sa mga sayup sa Excel - #TRANSFER! (#SPILL!):
Sama sa kanunay, kung mag-klik kami sa icon nga adunay dilaw nga diamante ug usa ka marka sa pagtuaw, makakuha kami usa ka mas detalyado nga pagpasabut sa gigikanan sa problema ug dali namon makit-an ang mga nakabalda nga mga selyula:
Ang susamang mga kasaypanan mahitabo kung ang array mawala sa sheet o moigo sa usa ka gisagol nga cell. Kung imong tangtangon ang babag, nan ang tanan matul-id dayon sa langaw.
Dynamic arrays ug smart tables
Kung ang dinamikong array nagpunting sa usa ka "smart" nga lamesa nga gihimo sa usa ka shortcut sa keyboard Ctrl+T o pinaagi sa Panimalay - Pag-format ingon usa ka lamesa (Balay — Format isip Talaan), unya makapanunod usab kini sa panguna nga kalidad niini - auto-sizing.
Kung magdugang bag-ong datos sa ilawom o sa tuo, ang intelihenteng lamesa ug dinamikong hanay usab awtomatiko nga maabut:
Bisan pa, adunay usa ka limitasyon: dili kami makagamit sa usa ka dynamic range reference sa forumula sulod sa usa ka smart table:
Dynamic arrays ug uban pang mga feature sa Excel
Okay, ingon nimo. Kining tanan makapaikag ug kataw-anan. Dili kinahanglan, sama sa kaniadto, nga mano-mano nga i-stretch ang pormula nga adunay usa ka pakisayran sa una nga cell sa orihinal nga range paubos ug sa tuo ug tanan kana. Ug kana lang?
Dili kaayo.
Ang mga dinamikong arrays dili lang laing himan sa Excel. Karon nabutang na sila sa kasingkasing (o utok) sa Microsoft Excel - ang makina sa pagkalkula niini. Nagpasabot kini nga ang ubang mga pormula sa Excel ug mga gimbuhaton nga pamilyar kanamo karon nagsuporta usab sa pagtrabaho sa mga dinamikong array. Atong tan-awon ang pipila ka mga pananglitan aron mahatagan ka usa ka ideya sa giladmon sa mga pagbag-o nga nahitabo.
Pagbalhin
Aron ma-transpose ang usa ka range (swap row ug columns) ang Microsoft Excel kanunay adunay built-in nga function TRANSP (TRANSPOSE). Bisan pa, aron magamit kini, kinahanglan nimo una nga husto nga pilion ang sakup alang sa mga resulta (pananglitan, kung ang input usa ka sakup nga 5 × 3, nan kinahanglan nimo nga gipili ang 3 × 5), dayon isulod ang function ug ipadayon ang kombinasyon Ctrl+pagbalhin+Pagsulod, tungod kay mahimo ra kini molihok sa array formula mode.
Karon mahimo ka lang makapili usa ka cell, isulud ang parehas nga pormula niini ug i-klik ang normal Pagsulod - Ang dinamikong laray mobuhat sa tanan sa iyang kaugalingon:
Talaan sa pagpadaghan
Kini ang pananglitan nga akong gigamit sa paghatag sa dihang gihangyo ako sa paghanduraw sa mga benepisyo sa array formula sa Excel. Karon, aron makalkulo ang tibuuk nga lamesa sa Pythagorean, igo na nga mobarug sa una nga cell B2, pagsulod didto usa ka pormula nga nagpadaghan sa duha nga mga arrays (vertical ug horizontal set sa mga numero 1..10) ug pag-klik lang sa Pagsulod:
Pag-gluing ug pagkakabig sa kaso
Ang mga arrays dili lamang madaghanon, kondili ipapilit usab sa standard operator & (ampersand). Ibutang ta nga kinahanglan natong kuhaon ang una ug apelyido gikan sa duha ka kolum ug itul-id ang jumping case sa orihinal nga datos. Gibuhat namo kini sa usa ka mubo nga pormula nga nagporma sa tibuok nga array, ug dayon among gipadapat ang function niini PROPNACH (PROPER)sa paglimpyo sa rehistro:
Konklusyon Top 3
Ibutang ta nga kita adunay usa ka pundok sa mga numero diin gusto natong makuha ang nag-unang tulo ka mga resulta, nga naghan-ay niini sa paubos nga han-ay. Karon kini gihimo pinaagi sa usa ka pormula ug, pag-usab, walay bisan unsa Ctrl+pagbalhin+Pagsulod sama kaniadto:
Kung gusto nimo nga ang mga resulta ibutang dili sa usa ka kolum, apan sa usa ka laray, nan igo na nga ilisan ang mga colon (line separator) niini nga pormula sa usa ka semicolon (element separator sulod sa usa ka linya). Sa English nga bersyon sa Excel, kini nga mga separator mga semicolon ug mga koma, matag usa.
Gikuha sa VLOOKUP ang daghang mga kolum sa usa ka higayon
Katungdanan VPR (VLOOKUP) karon mahimo nimong makuha ang mga kantidad dili gikan sa usa, apan gikan sa daghang mga kolum sa usa ka higayon - ipiho ang ilang mga numero (sa bisan unsang gusto nga pagkasunud) ingon usa ka laray sa ikatulo nga argumento sa function:
OFFSET function nga nagbalik sa usa ka dinamikong laray
Usa sa labing makapaikag ug mapuslanon (pagkahuman sa VLOOKUP) nga mga gimbuhaton alang sa pagtuki sa datos mao ang function PAGBALIGYA (OFFSET), nga akong gigugol sa usa ka higayon ang tibuok kapitulo sa akong libro ug usa ka artikulo dinhi. Ang kalisud sa pagsabut ug pag-master niini nga function mao ang kanunay nga nagbalik kini usa ka array (range) nga datos ingon usa ka sangputanan, apan dili namon kini makita, tungod kay ang Excel wala pa nahibal-an kung giunsa ang pagtrabaho sa mga arrays gikan sa kahon.
Karon kini nga problema anaa sa nangagi. Tan-awa kung giunsa karon, gamit ang usa ka pormula ug usa ka dinamikong laray nga gibalik sa OFFSET, mahimo nimong makuha ang tanan nga mga laray alang sa usa ka produkto gikan sa bisan unsang nasunud nga lamesa:
Atong tan-awon ang iyang mga argumento:
- A1 - pagsugod nga cell (reference point)
- ПОИСКПОЗ(F2;A2:A30;0) – kalkulasyon sa pagbalhin gikan sa sinugdanan cell ngadto sa unang nakita nga repolyo.
- 0 – pagbalhin sa "window" ngadto sa tuo nga paryente sa nagsugod cell
- СЧЁТЕСЛИ(A2:A30;F2) – pagkalkula sa gitas-on sa mibalik nga “bintana” – ang gidaghanon sa mga linya diin adunay repolyo.
- 4 — gidak-on sa "window" horizontally, ie output 4 kolum
Bag-ong mga Function para sa Dynamic Arrays
Dugang sa pagsuporta sa dinamikong mekanismo sa array sa daan nga mga gimbuhaton, daghang mga bag-ong function ang gidugang sa Microsoft Excel, gipahait ilabi na sa pagtrabaho uban sa dinamikong arrays. Sa partikular, kini mao ang:
- GRADO (SORT) – paghan-ay sa input range ug paghimo sa usa ka dinamikong array sa output
- SORTPO (SORTI) - mahimong mag-sort sa usa ka range pinaagi sa mga kantidad gikan sa lain
- filter (FILTER) – kuhaa ang mga laray gikan sa tinubdan range nga nagtagbo sa gipiho nga mga kondisyon
- UNIK (UNIQUE) - Gikuha ang talagsaon nga mga kantidad gikan sa usa ka range o gitangtang ang mga duplicate
- SLMASSIVE (RANDARRAY) - Naghimo usa ka han-ay sa mga random nga numero sa gihatag nga gidak-on
- PAGKATAPOS SA PAGKATAWO (SEQUENCE) — nagporma ug laray gikan sa han-ay sa mga numero nga adunay gihatag nga lakang
Dugang pa mahitungod kanila - sa ulahi. Sila adunay bili sa usa ka bulag nga artikulo (ug dili usa) alang sa mahunahunaon nga pagtuon 🙂
konklusyon
Kung nabasa na nimo ang tanan nga nahisulat sa ibabaw, sa akong hunahuna nahibal-an na nimo ang sukod sa mga pagbag-o nga nahitabo. Daghang mga butang sa Excel ang mahimo na karon nga mas sayon, mas sayon ug mas lohikal. Angkunon nako nga medyo nakurat ako kung pila ka mga artikulo ang kinahanglan nga tul-iron dinhi, sa kini nga site ug sa akong mga libro, apan andam ako nga buhaton kini nga adunay gaan nga kasingkasing.
Pagsumada sa mga resulta, Dugang pa dinamikong arrays, mahimo nimong isulat ang mosunod:
- Mahimo nimong kalimtan ang bahin sa kombinasyon Ctrl+pagbalhin+Pagsulod. Wala nay nakita ang Excel nga kalainan tali sa "regular nga mga pormula" ug "mga pormula sa array" ug gitratar kini sa parehas nga paagi.
- Mahitungod sa function SUMPRODUCT (SUMPRODUCT), nga gigamit kaniadto sa pagsulod sa mga pormula sa array nga wala Ctrl+pagbalhin+Pagsulod mahimo ka usab makalimot - karon kini igo na nga sayon SUM и Pagsulod.
- Ang mga smart table ug pamilyar nga mga function (SUM, IF, VLOOKUP, SUMIFS, ug uban pa) karon hingpit o partially nagsuporta sa dinamikong arrays.
- Adunay paatras nga pagpahiangay: kung magbukas ka usa ka workbook nga adunay dinamikong mga arrays sa usa ka daan nga bersyon sa Excel, kini mahimong mga pormula sa array (sa mga curly braces) ug magpadayon sa pagtrabaho sa "daan nga istilo".
Nakakita ug numero mga minus:
- Dili nimo mapapas ang tagsa-tagsa nga mga laray, mga kolum o mga selula gikan sa usa ka dinamikong array, ie kini nagpuyo isip usa ka entidad.
- Dili nimo maihap ang usa ka dinamikong laray sa naandan nga paagi Data - Pagsunud (Data - Pagsunud). Adunay karon usa ka espesyal nga function alang niini. GRADO (SORT).
- Ang usa ka dinamikong hanay dili mahimo nga usa ka intelihenteng lamesa (apan mahimo ka makahimo usa ka dinamikong hanay nga gibase sa usa ka intelihenteng lamesa).
Siyempre, dili kini ang katapusan, ug sigurado ako nga ang Microsoft magpadayon sa pagpalambo niini nga mekanismo sa umaabot.
Asa ko maka-download?
Ug sa katapusan, ang panguna nga pangutana 🙂
Una nga gipahibalo ug gipakita sa Microsoft ang usa ka preview sa mga dinamikong arrays sa Excel kaniadtong Setyembre 2018 sa usa ka komperensya Ignite. Sa sunod nga pipila ka bulan, adunay usa ka bug-os nga pagsulay ug pagdagan sa mga bag-ong bahin, una sa iring mga empleyado sa Microsoft mismo, ug dayon sa mga boluntaryo nga tester gikan sa sirkulo sa Office Insiders. Karong tuiga, ang pag-update nga nagdugang dinamikong mga arrays nagsugod nga hinayhinay nga gilukot sa regular nga mga subscriber sa Office 365. Pananglitan, nadawat ra nako kini kaniadtong Agosto sa akong suskrisyon sa Office 365 Pro Plus (Bulanan nga Target).
Kung ang imong Excel wala pa adunay mga dinamikong arrays, apan gusto nimo nga magtrabaho uban kanila, nan adunay mga mosunod nga mga kapilian:
- Kung ikaw adunay usa ka suskrisyon sa Office 365, mahimo ka lang maghulat hangtod maabot nimo kini nga update. Kung unsa kadali kini mahitabo nagdepende kung unsa ka sagad ang mga pag-update ipadala sa imong Opisina (kausa sa usa ka tuig, kausa matag unom ka bulan, kausa sa usa ka bulan). Kung ikaw adunay usa ka corporate PC, mahimo nimong hangyoon ang imong tagdumala sa pag-set up sa mga update aron ma-download kanunay.
- Mahimo ka nga moapil sa mga ranggo sa mga boluntaryo sa pagsulay sa Office Insiders - unya ikaw ang una nga makadawat sa tanan nga mga bag-ong bahin ug gimbuhaton (apan adunay higayon nga madugangan ang buggy sa Excel, siyempre).
- Kung wala ka usa ka suskrisyon, apan usa ka boxed standalone nga bersyon sa Excel, nan kinahanglan ka maghulat hangtod sa pagpagawas sa sunod nga bersyon sa Office ug Excel sa 2022, labing menos. Ang mga tiggamit sa ingon nga mga bersyon makadawat lamang og mga update sa seguridad ug mga pag-ayo sa bug, ug ang tanan nga mga bag-ong "goodies" moadto na lang sa mga subscriber sa Office 365. Subo pero tinuod 🙂
Sa bisan unsang kaso, kung ang mga dinamikong arrays makita sa imong Excel - pagkahuman sa kini nga artikulo, andam ka na alang niini 🙂
- Unsa ang mga pormula sa array ug kung giunsa kini gamiton sa Excel
- Window (range) summation gamit ang OFFSET function
- 3 Mga Paagi sa Pagbalhin sa usa ka Talaan sa Excel