Cynnwys
Beth yw araeau deinamig
Ym mis Medi 2018, rhyddhaodd Microsoft ddiweddariad sy'n ychwanegu offeryn cwbl newydd i Microsoft Excel: Arrays Dynamig a 7 swyddogaeth newydd ar gyfer gweithio gyda nhw. Mae'r pethau hyn, heb or-ddweud, yn newid yn radical yr holl dechneg arferol o weithio gyda fformiwlâu a swyddogaethau a phryder, yn llythrennol, pob defnyddiwr.
Ystyriwch enghraifft syml i egluro'r hanfod.
Tybiwch fod gennym dabl syml gyda data ar ddinasoedd-misoedd. Beth fydd yn digwydd os byddwn yn dewis unrhyw gell wag ar ochr dde'r ddalen ac yn nodi fformiwla sy'n cysylltu nid ag un gell, ond yn syth i ystod?
Ym mhob fersiwn blaenorol o Excel, ar ôl clicio ar Rhowch byddem yn cael cynnwys un gell gyntaf yn unig B2. Sut arall?
Wel, neu byddai'n bosibl lapio'r ystod hon mewn rhyw fath o swyddogaeth agregu fel = SUM(B2:C4) a chael cyfanswm mawr ar ei gyfer.
Pe bai angen gweithrediadau mwy cymhleth arnom na swm cyntefig, megis echdynnu gwerthoedd unigryw neu Top 3, yna byddai'n rhaid i ni nodi ein fformiwla fel fformiwla arae gan ddefnyddio llwybr byr bysellfwrdd Ctrl+Symud+Rhowch.
Nawr mae popeth yn wahanol.
Nawr ar ôl mynd i mewn i fformiwla o'r fath, gallwn ni glicio ar Rhowch – a chael o ganlyniad ar unwaith yr holl werthoedd uXNUMXbuXNUMXbto y cyfeiriasom atynt:
Nid hud yw hyn, ond yr araeau deinamig newydd sydd gan Microsoft Excel nawr. Croeso i'r byd newydd 🙂
Nodweddion gweithio gydag araeau deinamig
Yn dechnegol, mae ein cyfres ddeinamig gyfan yn cael ei storio yn y gell gyntaf G4, gan lenwi'r nifer ofynnol o gelloedd i'r dde ac i lawr gyda'i ddata. Os dewiswch unrhyw gell arall yn yr arae, yna bydd y ddolen yn y bar fformiwla yn anactif, gan ddangos ein bod yn un o'r celloedd “plentyn”:
Ni fydd ymgais i ddileu un neu fwy o gelloedd “plentyn” yn arwain at unrhyw beth - bydd Excel yn eu hailgyfrifo a'u llenwi ar unwaith.
Ar yr un pryd, gallwn gyfeirio'n ddiogel at y celloedd “plentyn” hyn mewn fformiwlâu eraill:
Os byddwch yn copïo cell gyntaf arae (er enghraifft, o G4 i F8), yna bydd yr arae gyfan (ei chyfeiriadau) yn symud i'r un cyfeiriad ag mewn fformiwlâu rheolaidd:
Os oes angen i ni symud yr arae, yna bydd yn ddigon i'w symud (gyda'r llygoden neu gyfuniad o Ctrl+X, Ctrl+V), eto, dim ond y brif gell gyntaf G4 - ar ei ôl, bydd yn cael ei drosglwyddo i le newydd a bydd ein casgliad cyfan yn cael ei ehangu eto.
Os oes angen i chi gyfeirio yn rhywle arall ar y ddalen at yr arae ddeinamig a grëwyd, yna gallwch ddefnyddio'r nod arbennig # (“punt”) ar ôl cyfeiriad ei gell arweiniol:
Er enghraifft, nawr gallwch chi wneud cwymplen yn hawdd mewn cell sy'n cyfeirio at yr arae ddeinamig a grëwyd:
Gwallau arae deinamig
Ond beth sy'n digwydd os nad oes digon o le i ehangu'r arae, neu os oes celloedd eisoes wedi'u meddiannu gan ddata arall yn ei lwybr? Cwrdd â math sylfaenol newydd o wallau yn Excel - #TROSGLWYDDO! (#SPILL!):
Fel bob amser, os byddwn yn clicio ar yr eicon gyda diemwnt melyn ac ebychnod, byddwn yn cael esboniad manylach o ffynhonnell y broblem a gallwn ddod o hyd i gelloedd sy'n ymyrryd yn gyflym:
Bydd gwallau tebyg yn digwydd os yw'r arae yn mynd oddi ar y ddalen neu'n taro cell wedi'i huno. Os byddwch chi'n cael gwared ar y rhwystr, yna bydd popeth yn cael ei gywiro ar unwaith ar y hedfan.
Araeau deinamig a thablau smart
Os yw'r arae ddeinamig yn pwyntio at dabl “clyfar” a grëwyd gan lwybr byr bysellfwrdd Ctrl+T neu drwy Cartref - Fformat fel bwrdd (Cartref - Fformat fel Tabl), yna bydd hefyd yn etifeddu ei brif ansawdd - auto-sizing.
Wrth ychwanegu data newydd i'r gwaelod neu i'r dde, bydd y tabl smart a'r ystod ddeinamig hefyd yn ymestyn yn awtomatig:
Fodd bynnag, mae un cyfyngiad: ni allwn ddefnyddio cyfeirnod amrediad deinamig mewn fforymau y tu mewn i dabl clyfar:
Araeau deinamig a nodweddion Excel eraill
Iawn, rydych chi'n dweud. Mae hyn i gyd yn ddiddorol ac yn ddoniol. Nid oes angen, fel o'r blaen, ymestyn y fformiwla â llaw gan gyfeirio at gell gyntaf yr ystod wreiddiol i lawr ac i'r dde a hynny i gyd. A dyna i gyd?
Ddim yn hollol.
Nid offeryn arall yn Excel yn unig yw araeau deinamig. Nawr maent wedi'u gwreiddio yng nghalon (neu ymennydd) Microsoft Excel - ei beiriant cyfrifo. Mae hyn yn golygu bod fformiwlâu a swyddogaethau Excel eraill sy'n gyfarwydd i ni nawr hefyd yn cefnogi gweithio gydag araeau deinamig. Gadewch i ni edrych ar ychydig o enghreifftiau i roi syniad i chi o ddyfnder y newidiadau sydd wedi digwydd.
Trosi
Er mwyn trawsosod amrediad (cyfnewid rhesi a cholofnau) mae Microsoft Excel bob amser wedi bod â swyddogaeth adeiledig TRANSP (TRASPOSE). Fodd bynnag, er mwyn ei ddefnyddio, rhaid i chi yn gyntaf ddewis yr ystod ar gyfer y canlyniadau yn gywir (er enghraifft, os oedd y mewnbwn yn ystod o 5 × 3, yna mae'n rhaid eich bod wedi dewis 3 × 5), yna nodwch y swyddogaeth a gwasgwch y cyfuniad Ctrl+Symud+Rhowch, oherwydd dim ond yn y modd fformiwla arae y gallai weithio.
Nawr gallwch chi ddewis un gell, nodwch yr un fformiwla ynddi a chliciwch ar y normal Rhowch - bydd amrywiaeth ddeinamig yn gwneud popeth ar ei ben ei hun:
Tabl lluosi
Dyma'r enghraifft roeddwn i'n arfer ei rhoi pan ofynnwyd i mi ddelweddu manteision fformiwlâu arae yn Excel. Nawr, i gyfrifo'r tabl Pythagorean cyfan, mae'n ddigon i sefyll yn y gell gyntaf B2, nodwch yno fformiwla sy'n lluosi dwy arae (set fertigol a llorweddol o rifau 1..10) a chlicio ar Rhowch:
Gludo a throsi cas
Nid yn unig y gellir lluosi araeau, ond hefyd eu gludo ynghyd â'r gweithredwr safonol a (ampersand). Tybiwch fod angen i ni dynnu'r enw cyntaf ac olaf o ddwy golofn a chywiro'r cas neidio yn y data gwreiddiol. Rydyn ni'n gwneud hyn gydag un fformiwla fer sy'n ffurfio'r arae gyfan, ac yna rydyn ni'n cymhwyso'r swyddogaeth iddo PROPNACH (PRIOD)i dacluso'r gofrestr:
Casgliad Uchaf 3
Tybiwch fod gennym griw o rifau yr ydym am gael y tri chanlyniad uchaf ohonynt, gan eu gosod mewn trefn ddisgynnol. Nawr gwneir hyn gan un fformiwla ac, unwaith eto, heb ddim Ctrl+Symud+Rhowch fel o'r blaen:
Os ydych chi am i'r canlyniadau gael eu gosod nid mewn colofn, ond mewn rhes, yna mae'n ddigon disodli'r colons (gwahanydd llinell) yn y fformiwla hon â hanner colon (gwahanydd elfen o fewn un llinell). Yn y fersiwn Saesneg o Excel, mae'r gwahanyddion hyn yn hanner colon a choma, yn y drefn honno.
VLOOKUP yn echdynnu colofnau lluosog ar unwaith
Swyddogaethau VPR (VLOOKUP) nawr gallwch chi dynnu gwerthoedd nid o un, ond o sawl colofn ar unwaith - dim ond nodi eu rhifau (mewn unrhyw drefn a ddymunir) fel arae yn nhrydedd ddadl y swyddogaeth:
Swyddogaeth OFFSET yn dychwelyd arae ddeinamig
Un o'r swyddogaethau mwyaf diddorol a defnyddiol (ar ôl VLOOKUP) ar gyfer dadansoddi data yw'r swyddogaeth GWAREDU (Gwrthosod), yr ymrwymais iddo ar un adeg bennod gyfan yn fy llyfr ac erthygl yma. Yr anhawster wrth ddeall a meistroli'r swyddogaeth hon erioed yw ei fod wedi dychwelyd amrywiaeth (ystod) o ddata o ganlyniad, ond ni allem ei weld, oherwydd nid oedd Excel yn gwybod sut i weithio gydag araeau allan o'r blwch.
Nawr mae'r broblem hon yn y gorffennol. Dewch i weld sut nawr, gan ddefnyddio un fformiwla ac arae ddeinamig a ddychwelwyd gan OFFSET, gallwch dynnu pob rhes ar gyfer cynnyrch penodol o unrhyw dabl wedi'i ddidoli:
Gadewch i ni edrych ar ei dadleuon:
- A1 – cell gychwyn (pwynt cyfeirio)
- ПОИСКПОЗ(F2;A2:A30; 0) – cyfrifo'r symudiad o'r gell gychwynnol i lawr – i'r bresych cyntaf a ddarganfuwyd.
- 0 – symud y “ffenestr” i'r dde o'i gymharu â'r gell gychwyn
- СЧЁТЕСЛИ(A2:A30;F2) – cyfrifo uchder y “ffenestr” a ddychwelwyd – nifer y llinellau lle mae bresych.
- 4 — maint y “ffenestr” yn llorweddol, hy allbwn 4 colofn
Swyddogaethau Newydd ar gyfer Araeau Dynamig
Yn ogystal â chefnogi'r mecanwaith arae deinamig mewn hen swyddogaethau, mae nifer o swyddogaethau cwbl newydd wedi'u hychwanegu at Microsoft Excel, wedi'u hogi'n benodol ar gyfer gweithio gydag araeau deinamig. Yn benodol, y rhain yw:
- GRADD (SORT) – yn didoli'r ystod mewnbwn ac yn cynhyrchu arae ddeinamig ar yr allbwn
- SORTPO (SORTBY) – yn gallu didoli un ystod yn ôl gwerthoedd o un arall
- Hidlo (HILYDD) - yn adfer rhesi o'r ystod ffynhonnell sy'n bodloni'r amodau penodedig
- UNIK (UNIQUE) - yn tynnu gwerthoedd unigryw o ystod neu'n dileu copïau dyblyg
- LLAIN (RANDARAY) – yn cynhyrchu amrywiaeth o haprifau o faint penodol
- AR ÔL GENI (DILYNIANT) — yn ffurfio arae o ddilyniant o rifau gyda cham penodol
Mwy amdanyn nhw - ychydig yn ddiweddarach. Maent yn werth erthygl ar wahân (ac nid un) ar gyfer astudiaeth feddylgar 🙂
Casgliadau
Os ydych chi wedi darllen popeth a ysgrifennwyd uchod, yna rwy'n meddwl eich bod eisoes yn sylweddoli maint y newidiadau sydd wedi digwydd. Bellach gellir gwneud cymaint o bethau yn Excel yn haws, yn haws ac yn fwy rhesymegol. Rhaid i mi gyfaddef fy mod wedi fy synnu braidd faint o erthyglau fydd yn rhaid eu cywiro nawr yma, ar y wefan hon ac yn fy llyfrau, ond rwy'n barod i wneud hyn gyda chalon ysgafn.
Wrth grynhoi'r canlyniadau, ychwanegol araeau deinamig, gallwch ysgrifennu'r canlynol:
- Gallwch chi anghofio am y cyfuniad Ctrl+Symud+Rhowch. Nid yw Excel bellach yn gweld unrhyw wahaniaeth rhwng “fformiwlâu rheolaidd” a “fformiwlâu arae” ac mae'n eu trin yr un ffordd.
- Am y swyddogaeth SUMPRODUCT (SUMPRODUCT), a ddefnyddiwyd yn flaenorol i fynd i mewn i fformiwlâu arae heb Ctrl+Symud+Rhowch gallwch hefyd anghofio – nawr mae'n ddigon hawdd SUM и Rhowch.
- Mae tablau clyfar a swyddogaethau cyfarwydd (SUM, IF, VLOOKUP, SUMIFS, ac ati) bellach hefyd yn cefnogi araeau deinamig yn llawn neu'n rhannol.
- Mae cydweddoldeb yn ôl: os byddwch chi'n agor llyfr gwaith gydag araeau deinamig mewn hen fersiwn o Excel, byddant yn troi'n fformiwlâu arae (mewn braces cyrliog) ac yn parhau i weithio yn yr “hen arddull”.
Wedi dod o hyd i ryw nifer minws:
- Ni allwch ddileu rhesi, colofnau neu gelloedd unigol o arae ddeinamig, hy mae'n byw fel un endid.
- Ni allwch ddidoli arae ddeinamig yn y ffordd arferol drwyddo Data - Didoli (Data - Trefnu). Bellach mae swyddogaeth arbennig i hyn. GRADD (SORT).
- Ni ellir troi ystod ddeinamig yn dabl smart (ond gallwch chi wneud ystod ddeinamig yn seiliedig ar dabl smart).
Wrth gwrs, nid dyma'r diwedd, ac rwy'n siŵr y bydd Microsoft yn parhau i wella'r mecanwaith hwn yn y dyfodol.
Ble alla i lawrlwytho?
Ac yn olaf, y prif gwestiwn 🙂
Cyhoeddodd Microsoft gyntaf a dangosodd ragolwg o araeau deinamig yn Excel yn ôl ym mis Medi 2018 mewn cynhadledd Ignite. Yn ystod yr ychydig fisoedd nesaf, roedd nodweddion newydd yn cael eu profi a'u rhedeg i mewn yn drylwyr, yn gyntaf cathod gweithwyr Microsoft ei hun, ac yna ar brofwyr gwirfoddol o gylch Office Insiders. Eleni, dechreuodd y diweddariad sy'n ychwanegu araeau deinamig gael ei gyflwyno'n raddol i danysgrifwyr Office 365 rheolaidd. Er enghraifft, dim ond ym mis Awst y derbyniais ef gyda'm tanysgrifiad Office 365 Pro Plus (Targed Misol).
Os nad oes gan eich Excel araeau deinamig eto, ond rydych chi wir eisiau gweithio gyda nhw, yna mae'r opsiynau canlynol:
- Os oes gennych danysgrifiad Office 365, yn syml iawn, gallwch aros nes bydd y diweddariad hwn yn eich cyrraedd. Mae pa mor gyflym y bydd hyn yn digwydd yn dibynnu ar ba mor aml y caiff diweddariadau eu cyflwyno i'ch Swyddfa (unwaith y flwyddyn, unwaith bob chwe mis, unwaith y mis). Os oes gennych gyfrifiadur personol corfforaethol, gallwch ofyn i'ch gweinyddwr sefydlu diweddariadau i'w llwytho i lawr yn amlach.
- Gallwch ymuno â rhengoedd y gwirfoddolwyr prawf Office Insiders hynny - yna chi fydd y cyntaf i dderbyn yr holl nodweddion a swyddogaethau newydd (ond mae siawns o fwy o fygi yn Excel, wrth gwrs).
- Os nad oes gennych danysgrifiad, ond fersiwn arunig mewn bocs o Excel, yna bydd yn rhaid i chi aros nes cyhoeddi'r fersiwn nesaf o Office ac Excel yn 2022, o leiaf. Dim ond diweddariadau diogelwch ac atgyweiriadau nam y mae defnyddwyr fersiynau o'r fath yn eu derbyn, ac mae'r holl “nwyddau” newydd bellach yn mynd i danysgrifwyr Office 365 yn unig. Trist ond gwir 🙂
Beth bynnag, pan fydd araeau deinamig yn ymddangos yn eich Excel - ar ôl yr erthygl hon, byddwch yn barod amdani 🙂
- Beth yw fformiwlâu arae a sut i'w defnyddio yn Excel
- Crynhoad ffenestr (ystod) gan ddefnyddio'r swyddogaeth OFFSET
- 3 Ffordd o Drawsosod Tabl yn Excel