Arrays dinamic ann an Excel

Dè a th’ ann an arrays fiùghantach

San t-Sultain 2018, leig Microsoft a-mach ùrachadh a chuireas inneal gu tur ùr ri Microsoft Excel: Dynamic Arrays agus 7 gnìomhan ùra airson a bhith ag obair còmhla riutha. Bidh na rudan sin, gun cus cus, ag atharrachadh gu mòr an dòigh àbhaisteach air obrachadh le foirmlean agus gnìomhan agus dragh, gu litearra, a h-uile neach-cleachdaidh.

Beachdaich air eisimpleir shìmplidh gus brìgh a mhìneachadh.

Seach gu bheil clàr sìmplidh againn le dàta air mìosan a' bhaile. Dè thachras ma thaghas sinn cealla falamh air taobh deas na duilleige agus ma chuireas sinn a-steach foirmle a tha a’ ceangal chan ann ri aon chill, ach sa bhad ri raon?

Anns a h-uile dreach roimhe de Excel, às deidh dhut briogadh air Cuir a-steach chan fhaigheadh ​​sinn susbaint ach aon chiad chealla B2. Ciamar eile?

Uill, neo bhiodh e comasach an raon seo a phasgadh ann an seòrsa de dh’ obair cruinneachaidh mar = SUM (B2: C4) agus suim iomlan fhaighinn air a shon.

Nam biodh feum againn air gnìomhachd nas iom-fhillte na suim prìomhadail, leithid a bhith a’ toirt a-mach luachan sònraichte no Top 3, dh’ fheumadh sinn ar foirmle a chuir a-steach mar fhoirmle rèite a’ cleachdadh ath-ghoirid meur-chlàr Ctrl+Shift+Cuir a-steach.

A-nis tha a h-uile dad eadar-dhealaichte.

A-nis às deidh dhuinn a leithid de fhoirmle a chuir a-steach, is urrainn dhuinn dìreach cliogadh air Cuir a-steach - agus mar thoradh air sin faigh sa bhad na luachan gu lèir uXNUMXbuXNUMXbto air an tug sinn iomradh:

Chan e draoidheachd a tha seo, ach na h-sreathan fiùghantach ùra a tha aig Microsoft Excel a-nis. Fàilte don t-saoghal ùr 🙂

Feartan a bhith ag obair le arrays fiùghantach

Gu teicnigeach, tha an raon fiùghantach againn gu lèir air a stòradh anns a ’chiad chealla G4, a’ lìonadh an àireamh riatanach de cheallan air an taobh cheart agus sìos leis an dàta aige. Ma thaghas tu cealla sam bith eile san raon, bidh an ceangal sa bhàr fhoirmeil neo-ghnìomhach, a’ sealltainn gu bheil sinn ann an aon de na ceallan “leanabh”:

Cha toir oidhirp air aon no barrachd cheallan “leanabh” a sguabadh às gu dad - bidh Excel ag ath-àireamhachadh agus gan lìonadh sa bhad.

Aig an aon àm, is urrainn dhuinn gu sàbhailte iomradh a thoirt air na ceallan “leanabh” sin ann am foirmlean eile:

Ma nì thu lethbhreac den chiad chealla de raon (mar eisimpleir, bho G4 gu F8), gluaisidh an t-sreath gu lèir (na h-iomraidhean) san aon taobh ri foirmlean àbhaisteach:

Ma dh'fheumas sinn an raon a ghluasad, bidh e gu leòr airson gluasad (leis an luchag no measgachadh de Ctrl+X, Ctrl+V), a-rithist, dìreach a’ chiad phrìomh chealla G4 - às deidh sin, thèid a ghluasad gu àite ùr agus thèid an raon iomlan againn a leudachadh a-rithist.

Ma dh'fheumas tu iomradh a thoirt air àite eile air an duilleag chun an t-sreath fiùghantach a chaidh a chruthachadh, faodaidh tu an caractar sònraichte # ("punnd") a chleachdadh às deidh seòladh a phrìomh chill:

Mar eisimpleir, a-nis is urrainn dhut liosta tuiteam-sìos a dhèanamh gu furasta ann an cealla a tha a’ toirt iomradh air an raon fiùghantach a chaidh a chruthachadh:

Mearachdan rèiteachadh dinamic

Ach dè a thachras mura h-eil àite gu leòr ann airson an raon a leudachadh, no ma tha ceallan mar-thà air an cleachdadh le dàta eile na shlighe? Coinnich ri seòrsa de mhearachdan bunaiteach ùr ann an Excel - #IOMPACHADH! (#DÒIRT!):

Mar as àbhaist, ma bhriogas sinn air an ìomhaigh le daoimean buidhe agus comharra clisgeadh, gheibh sinn mìneachadh nas mionaidiche air cò às a thàinig an duilgheadas agus gheibh sinn gu sgiobalta ceallan eadar-theachd:

Bidh mearachdan coltach ris a’ tachairt ma thèid an t-sreath far an duilleag no ma bhuaileas e cealla aonaichte. Ma bheir thu air falbh an cnap-starra, thèid a h-uile càil a cheartachadh sa bhad air an itealan.

Arrays dinamic agus bùird snasail

Ma tha an t-sreath fiùghantach a’ comharrachadh clàr “smart” air a chruthachadh le ath-ghoirid meur-chlàr Ctrl+T no le Dachaigh - Cruth mar chlàr (Dachaigh - Cruth mar Clàr), an uairsin sealbhaichidh e cuideachd a phrìomh chàileachd - fèin-sizing.

Nuair a chuireas tu dàta ùr ris a’ bhonn no air an taobh dheas, bidh an clàr snasail agus an raon fiùghantach a’ sìneadh gu fèin-ghluasadach cuideachd:

Ach, tha aon chuingealachadh ann: chan urrainn dhuinn iomradh raon fiùghantach a chleachdadh ann am fòraman taobh a-staigh clàr snasail:

Arrays dinamic agus feartan Excel eile

Ceart gu leòr, tha thu ag ràdh. Tha seo uile inntinneach agus èibhinn. Chan eil feum, mar a bha e roimhe, an fhoirmle a shìneadh le làimh le iomradh air a’ chiad chill den raon thùsail sìos agus air an taobh cheart agus a h-uile càil. Agus tha sin uile?

Chan eil buileach.

Chan e dìreach inneal eile ann an Excel a th’ ann an arrays dinamic. A-nis tha iad freumhaichte ann am fìor chridhe (no eanchainn) Microsoft Excel - an einnsean àireamhachaidh aige. Tha seo a’ ciallachadh gu bheil foirmlean agus gnìomhan Excel eile air a bheil sinn eòlach a-nis cuideachd a’ toirt taic do bhith ag obair le arrays fiùghantach. Bheir sinn sùil air beagan eisimpleirean gus beachd a thoirt dhut air doimhneachd nan atharrachaidhean a tha air tachairt.

Cuir thairis

Gus raon a thar-chuir (sreathan suaip agus colbhan) bha gnìomh togte a-riamh aig Microsoft Excel TRANSP (TRANSPOSE). Ach, airson a chleachdadh, feumaidh tu an toiseach taghadh ceart a dhèanamh air an raon airson nan toraidhean (mar eisimpleir, ma bha an cuir a-steach raon de 5 × 3, feumaidh tu a bhith air 3 × 5 a thaghadh), an uairsin cuir a-steach an gnìomh agus brùth air an measgachadh Ctrl+Shift+Cuir a-steach, oir cha b' urrainn dha obrachadh ach ann am modh foirmle rèite.

A-nis faodaidh tu dìreach aon chealla a thaghadh, cuir a-steach an aon fhoirmle a-steach ann agus cliog air an àbhaist Cuir a-steach - nì raon fiùghantach a h-uile càil leis fhèin:

Clàr iomadachaidh

Seo an eisimpleir a b’ àbhaist dhomh a thoirt seachad nuair a chaidh iarraidh orm na buannachdan bho fhoirmlean rèite fhaicinn ann an Excel. A-nis, gus an clàr Pythagorean gu lèir obrachadh a-mach, tha e gu leòr seasamh anns a’ chiad chealla B2, cuir a-steach an sin foirmle a bhios ag iomadachadh dà arrays (seata àireamhan dìreach agus còmhnard 1..10) agus dìreach cliog air Cuir a-steach:

Gluing agus tionndadh cùise

Chan urrainn arrays a bhith air an iomadachadh a-mhàin, ach cuideachd air an glaodhadh còmhla ris a’ ghnìomhaiche àbhaisteach & (ampersand). Osbarr feumaidh sinn a’ chiad ainm agus an t-ainm mu dheireadh a thoirt a-mach à dà cholbh agus a’ chùis leum a cheartachadh anns an dàta thùsail. Bidh sinn a 'dèanamh seo le aon fhoirmle ghoirid a bhios a' cruthachadh an raon gu lèir, agus an uairsin cuiridh sinn an gnìomh ris PROPNACH (PROPER)gus an clàr a sgioblachadh:

Co-dhùnadh Top 3

Seach gu bheil dòrlach de dh'àireamhan againn às a bheil sinn airson na trì toraidhean as àirde fhaighinn, gan cur ann an òrdugh teàrnaidh. A-nis tha seo air a dhèanamh le aon fhoirmle agus, a-rithist, às aonais gin Ctrl+Shift+Cuir a-steach mar roimhe:

Ma tha thu airson gun tèid na toraidhean a chuir ann an colbh, ach ann an sreath, tha e gu leòr leth-dhuilleag (sgaradair eileamaid taobh a-staigh aon loidhne) a chuir an àite nan coloin (sgaradh loidhne) san fhoirmle seo. Anns an dreach Beurla de Excel, tha na sgaraidhean sin nan leth-choloin agus cromagan, fa leth.

VLOOKUP a’ toirt a-mach grunn cholbhan aig an aon àm

gnìomhan VPR (VLOOKUP) a-nis faodaidh tu luachan a tharraing chan ann bho aon, ach bho ghrunn cholbhan aig an aon àm - dìreach sònraich na h-àireamhan aca (ann an òrdugh sam bith a tha thu ag iarraidh) mar raon anns an treas argamaid den ghnìomh:

Gnìomh OFFSET a’ tilleadh raon fiùghantach

Is e aon de na gnìomhan as inntinniche agus as fheumaile (às deidh VLOOKUP) airson mion-sgrùdadh dàta an gnìomh URNAIGH (AIRGEAD), ris an do chuir mi seachad aig aon àm caibideil slàn anns an leabhar agam agus artaigil an seo. Is e an duilgheadas ann a bhith a’ tuigsinn agus a ’maighstireachd a’ ghnìomh seo a-riamh gun do thill e sreath (raon) de dhàta mar thoradh air an sin, ach cha b ’urrainn dhuinn fhaicinn, oir cha robh fios aig Excel fhathast mar a dh’ obraicheadh ​​​​e le arrays a-mach às a ’bhogsa.

A-nis tha an duilgheadas seo san àm a dh'fhalbh. Faic mar a-nis, a’ cleachdadh aon fhoirmle agus sreath fiùghantach air a thilleadh le OFFSET, is urrainn dhut a h-uile sreath airson toradh sònraichte a thoirt a-mach à clàr sam bith:

Bheir sinn sùil air na h-argamaidean aice:

• A1 - cealla tòiseachaidh (puing iomraidh)
• ПОИСКПОЗ(F2;A2: A30;0) - obrachadh a-mach an gluasad bhon chill tòiseachaidh sìos - chun a’ chiad chàl a chaidh a lorg.
• 0 - gluasad na “uinneag” air an taobh cheart an coimeas ris a ’chill tòiseachaidh
  
• Seo(A2: A30;F2) - obrachadh a-mach àirde an “uinneig” a chaidh a thilleadh - an àireamh de loidhnichean far a bheil càl.
• 4 — meud na “uinneige” gu còmhnard, ie toradh 4 colbhan

Gnìomhan ùra airson Arrays Dynamic

A bharrachd air a bhith a’ toirt taic don uidheamachd eagrachaidh fiùghantach ann an seann ghnìomhan, chaidh grunn ghnìomhan gu tur ùr a chur ri Microsoft Excel, air an gèilleadh gu sònraichte airson a bhith ag obair le arrays fiùghantach. Gu sònraichte, is iad seo:

• CHOITCHEANN (SORT) - a’ rèiteach an raon cuir a-steach agus a’ toirt a-mach sreath fiùghantach air an toradh
  
• SORTPO (SORTBY) - is urrainn dha aon raon a sheòrsachadh a rèir luachan bho fhear eile
  
• FILTAR (SGILEAN) - a’ faighinn air ais sreathan bhon raon stòr a choinnicheas ris na cumhachan ainmichte
• UNIK (UNIQUE) - thoir a-mach luachan sònraichte bho raon no thoir air falbh dùblaidhean
  
• SLUAGH (RANDARRAY) - a’ gineadh sreath de àireamhan air thuaiream de mheud sònraichte
• ATHAIR (SEquENCE) - a’ cruthachadh sreath bho shreath àireamhan le ceum sònraichte
  

Tuilleadh mun deidhinn - beagan an dèidh sin. Is fhiach iad artaigil air leth (agus chan e aon) airson sgrùdadh smaoineachail 🙂

Co-dhùnaidhean

Ma tha thu air a h-uile dad a chaidh a sgrìobhadh gu h-àrd a leughadh, tha mi a’ smaoineachadh gu bheil thu mu thràth a’ tuigsinn meud nan atharrachaidhean a tha air tachairt. Faodar uimhir de rudan ann an Excel a dhèanamh a-nis nas fhasa, nas fhasa agus nas loidsigeach. Feumaidh mi aideachadh gu bheil mi air mo chlisgeadh le cia mheud artaigil a dh’ fheumar a cheartachadh an seo, air an làrach seo agus anns na leabhraichean agam, ach tha mi deiseil airson seo a dhèanamh le cridhe aotrom.

A 'toirt geàrr-chunntas air na toraidhean, cuiridhean arrays fiùghantach, faodaidh tu na leanas a sgrìobhadh:

• Faodaidh tu dìochuimhneachadh mun mheasgachadh Ctrl+Shift+Cuir a-steach. Chan eil Excel a-nis a’ faicinn eadar-dhealachadh sam bith eadar “foirmlean cunbhalach” agus “foirmlean rèite” agus bidh e gan làimhseachadh san aon dòigh.
• Mu dheidhinn an gnìomh RO-RÀDH (SUMPRODUCT), a chaidh a chleachdadh roimhe seo gus foirmlean array a chuir a-steach às aonais Ctrl+Shift+Cuir a-steach faodaidh tu cuideachd dìochuimhneachadh - a-nis tha e furasta gu leòr SUM и Cuir a-steach.
• Tha bùird snasail agus gnìomhan eòlach (SUM, IF, VLOOKUP, SUMIFS, msaa) a-nis cuideachd a’ toirt taic gu h-iomlan no ann am pàirt de arrays fiùghantach.
• Tha co-fhreagarrachd air ais ann: ma dh'fhosglas tu leabhar-obrach le arrays fiùghantach ann an seann dreach de Excel, tionndaidhidh iad gu foirmlean rèite (ann an sèbhirean lùbach) agus leanaidh iad orra ag obair san “seann stoidhle”.

Lorg àireamh air choireigin eas-bhuannachdan:

• Chan urrainn dhut sreathan, colbhan no ceallan fa leth a sguabadh às à sreath fiùghantach, ie tha e beò mar aon bhuidheann.
• Chan urrainn dhut sreath fiùghantach a sheòrsachadh san dòigh àbhaisteach troimhe Dàta - Deasachadh (Dàta - Deasaich). Tha gnìomh sònraichte ann airson seo a-nis. CHOITCHEANN (SORT).
• Chan urrainnear raon fiùghantach a thionndadh gu clàr snasail (ach faodaidh tu raon fiùghantach a dhèanamh stèidhichte air clàr snasail).
  

Gu dearbh, chan e seo an deireadh, agus tha mi cinnteach gun lean Microsoft air adhart ag adhartachadh an uidheamachd seo san àm ri teachd.

Càite an urrainn dhomh luchdachadh sìos?

Agus mu dheireadh, am prìomh cheist 🙂

Dh’ ainmich Microsoft an toiseach agus sheall e ro-shealladh de arrays fiùghantach ann an Excel air ais san t-Sultain 2018 aig co-labhairt Ignite. Anns na mìosan a tha romhainn, chaidh mion-dheuchainn agus ruith a-steach de fheartan ùra, an toiseach air adhart cait luchd-obrach Microsoft fhèin, agus an uairsin air luchd-dearbhaidh saor-thoileach bhon chearcall de Office Insiders. Am-bliadhna, thòisich an ùrachadh a chuireas arrays fiùghantach air a sgaoileadh a-mach mean air mhean gu luchd-aontachaidh àbhaisteach Office 365. Mar eisimpleir, cha d’ fhuair mi e ach san Lùnastal leis an fho-sgrìobhadh Office 365 Pro Plus (Targaidte Mìosail) agam.

Mura h-eil arrays fiùghantach aig an Excel agad fhathast, ach gu bheil thu dha-rìribh ag iarraidh obrachadh còmhla riutha, tha na roghainnean a leanas ann:

• Ma tha ballrachd Office 365 agad, faodaidh tu feitheamh gus an ruig an t-ùrachadh seo thu. Bidh cho luath sa thachras seo an urra ri dè cho tric sa thèid ùrachaidhean a lìbhrigeadh don Oifis agad (uair sa bhliadhna, aon uair gach sia mìosan, uair sa mhìos). Ma tha PC corporra agad, faodaidh tu iarraidh air an rianaire agad ùrachaidhean a chuir air dòigh airson an luchdachadh sìos nas trice.
  
• Faodaidh tu a dhol còmhla ris na saor-thoilich deuchainn Office Insiders sin - an uairsin bidh thu mar a’ chiad neach a gheibh na feartan agus na gnìomhan ùra gu lèir (ach tha cothrom ann gum bi barrachd buggy ann an Excel, gu dearbh).
• Mura h-eil ballrachd agad, ach dreach ann am bogsa leis fhèin de Excel, feumaidh tu feitheamh gus an tèid an ath dhreach de Office agus Excel a leigeil ma sgaoil ann an 2022, co-dhiù. Chan eil luchd-cleachdaidh nan dreachan sin a’ faighinn ach ùrachaidhean tèarainteachd agus rèiteachadh bug, agus tha a h-uile “goodies” ùr a-nis a’ dol dìreach gu luchd-aontachaidh Office 365. Duilich ach fìor 🙂
  

Co-dhiù, nuair a nochdas arrays fiùghantach san Excel agad - às deidh an artaigil seo, bidh thu deiseil air a shon 🙂

• Dè a th’ ann am foirmlean rèite agus mar a chleachdas tu iad ann an Excel
• Suim uinneig (raon) a’ cleachdadh a’ ghnìomh OFFSET
• 3 Dòighean air Clàr a thionndadh ann an Excel