Përmbajtje
Çfarë janë vargjet dinamike
Në shtator 2018, Microsoft lëshoi një përditësim që shton një mjet krejtësisht të ri në Microsoft Excel: Dynamic Arrays dhe 7 funksione të reja për të punuar me to. Këto gjëra, pa ekzagjerim, ndryshojnë rrënjësisht të gjithë teknikën e zakonshme të punës me formula dhe funksione dhe shqetësojnë, fjalë për fjalë, çdo përdorues.
Konsideroni një shembull të thjeshtë për të shpjeguar thelbin.
Supozoni se kemi një tabelë të thjeshtë me të dhëna për muajt e qytetit. Çfarë do të ndodhë nëse zgjedhim ndonjë qelizë boshe në të djathtë të fletës dhe futim në të një formulë që nuk lidhet me një qelizë, por menjëherë me një varg?
Në të gjitha versionet e mëparshme të Excel, pasi të klikoni mbi Shkruani do të merrnim përmbajtjen e vetëm një qelize të parë B2. Kush tjeter?
Epo, ose do të ishte e mundur të mbështillesh këtë varg në një lloj funksioni grumbullues si =SUM(B2:C4) dhe të merrte një total total për të.
Nëse do të kishim nevojë për operacione më komplekse sesa një shumë primitive, siç është nxjerrja e vlerave unike ose Top 3, atëherë do të duhej të futnim formulën tonë si një formulë grupi duke përdorur një shkurtore të tastierës Ctrl+Ndryshim+Shkruani.
Tani gjithçka është ndryshe.
Tani pasi kemi futur një formulë të tillë, thjesht mund të klikojmë Shkruani – dhe si rezultat merrni menjëherë të gjitha vlerat uXNUMXbuXNUMXb të cilave iu referuam:
Kjo nuk është magji, por vargje të reja dinamike që tani ka Microsoft Excel. Mirë se vini në botën e re 🙂
Karakteristikat e punës me vargje dinamike
Teknikisht, i gjithë grupi ynë dinamik ruhet në qelizën e parë G4, duke plotësuar numrin e kërkuar të qelizave djathtas dhe poshtë me të dhënat e tij. Nëse zgjidhni ndonjë qelizë tjetër në grup, atëherë lidhja në shiritin e formulave do të jetë joaktive, duke treguar se jemi në një nga qelizat "fëmijë":
Një përpjekje për të fshirë një ose më shumë qeliza "fëmijë" nuk do të çojë në asgjë - Excel do t'i rillogarisë menjëherë dhe do t'i plotësojë ato.
Në të njëjtën kohë, ne mund t'u referohemi me siguri këtyre qelizave "fëmijë" në formula të tjera:
Nëse kopjoni qelizën e parë të një grupi (për shembull, nga G4 në F8), atëherë i gjithë grupi (referencat e tij) do të lëvizë në të njëjtin drejtim si në formulat e rregullta:
Nëse duhet të lëvizim grupin, atëherë do të mjaftojë të lëvizim (me miun ose me një kombinim të Ctrl+X, Ctrl+V), përsëri, vetëm qeliza e parë kryesore G4 - pas saj, ajo do të transferohet në një vend të ri dhe i gjithë grupi ynë do të zgjerohet përsëri.
Nëse duhet t'i referoheni diku tjetër në fletë grupit dinamik të krijuar, atëherë mund të përdorni karakterin special # ("pound") pas adresës së qelizës së saj kryesore:
Për shembull, tani mund të krijoni lehtësisht një listë rënëse në një qelizë që i referohet grupit dinamik të krijuar:
Gabimet e grupit dinamik
Por çfarë ndodh nëse nuk ka hapësirë të mjaftueshme për të zgjeruar grupin, ose nëse ka qeliza të zëna tashmë nga të dhëna të tjera në rrugën e tij? Takoni një lloj krejtësisht të ri të gabimeve në Excel - #TRANSFERIM! (#PERDHJE!):
Si gjithmonë, nëse klikojmë në ikonën me një diamant të verdhë dhe një pikëçuditëse, do të marrim një shpjegim më të detajuar të burimit të problemit dhe mund të gjejmë shpejt qelizat ndërhyrëse:
Gabime të ngjashme do të ndodhin nëse grupi del jashtë fletës ose godet një qelizë të bashkuar. Nëse hiqni pengesën, atëherë gjithçka do të korrigjohet menjëherë në fluturim.
Vargje dinamike dhe tabela inteligjente
Nëse grupi dinamik tregon një tabelë "të zgjuar" të krijuar nga një shkurtore e tastierës Ctrl+T ose nga Faqja kryesore – Formatoni si tabelë (Shtëpia - Formatoni si tabelë), atëherë do të trashëgojë edhe cilësinë e tij kryesore – madhësinë automatike.
Kur shtoni të dhëna të reja në fund ose në të djathtë, tabela inteligjente dhe diapazoni dinamik gjithashtu do të shtrihen automatikisht:
Sidoqoftë, ekziston një kufizim: ne nuk mund të përdorim një referencë të diapazonit dinamik në forumet brenda një tabele inteligjente:
Vargje dinamike dhe veçori të tjera të Excel
Mirë, thua ti. E gjithë kjo është interesante dhe qesharake. Nuk ka nevojë, si më parë, ta shtrini manualisht formulën duke iu referuar qelizës së parë të gamës origjinale poshtë dhe djathtas dhe gjithçka tjetër. Dhe kjo eshte e gjitha?
Jo mjaft.
Vargjet dinamike nuk janë vetëm një mjet tjetër në Excel. Tani ato janë të ngulitura në zemrën (ose trurin) e Microsoft Excel - motori i tij llogaritës. Kjo do të thotë se formulat dhe funksionet e tjera të Excel-it të njohura për ne tani mbështesin gjithashtu punën me vargje dinamike. Le të hedhim një vështrim në disa shembuj për t'ju dhënë një ide mbi thellësinë e ndryshimeve që kanë ndodhur.
zhvendos
Për të transpozuar një varg (ndërrim rreshtash dhe kolonash) Microsoft Excel ka pasur gjithmonë një funksion të integruar TRANSP (TRANSPOZOJ). Megjithatë, për ta përdorur atë, fillimisht duhet të zgjidhni saktë diapazonin për rezultatet (për shembull, nëse hyrja ishte një interval prej 5×3, atëherë duhet të keni zgjedhur 3×5), më pas futni funksionin dhe shtypni kombinim Ctrl+Ndryshim+Shkruani, sepse mund të funksiononte vetëm në modalitetin e formulës së grupit.
Tani mund të zgjidhni vetëm një qelizë, të futni të njëjtën formulë në të dhe të klikoni mbi normalen Shkruani – grupi dinamik do të bëjë gjithçka vetë:
Tabela e shumëzimit
Ky është shembulli që kam dhënë kur më kërkuan të përfytyroja përfitimet e formulave të grupeve në Excel. Tani, për të llogaritur të gjithë tabelën e Pitagorës, mjafton të qëndroni në qelizën e parë B2, futni atje një formulë që shumëzon dy vargje (bashkësi vertikale dhe horizontale të numrave 1..10) dhe thjesht klikoni në Shkruani:
Ngjitja dhe konvertimi i kasës
Vargjet jo vetëm që mund të shumëzohen, por edhe të ngjiten së bashku me operatorin standard & (ampersand). Supozoni se duhet të nxjerrim emrin dhe mbiemrin nga dy kolona dhe të korrigjojmë rastin e kërcimit në të dhënat origjinale. Ne e bëjmë këtë me një formulë të shkurtër që formon të gjithë grupin, dhe më pas aplikojmë funksionin në të PROPNACH (E DUHUR)për të rregulluar regjistrin:
Përfundim Top 3
Supozoni se kemi një grup numrash nga të cilët duam të nxjerrim tre rezultatet kryesore, duke i renditur në rend zbritës. Tani kjo bëhet me një formulë dhe, përsëri, pa asnjë Ctrl+Ndryshim+Shkruani si me pare:
Nëse dëshironi që rezultatet të mos vendosen në një kolonë, por në një rresht, atëherë mjafton që të zëvendësoni dy pikat (ndarëse vijash) në këtë formulë me një pikëpresje (ndarëse elementesh brenda një rreshti). Në versionin anglisht të Excel, këta ndarës janë përkatësisht pikëpresje dhe presje.
VLOOKUP nxjerr disa kolona njëherësh
Funksione VPR (VLOOKUP) tani mund të tërhiqni vlera jo nga një, por nga disa kolona menjëherë - thjesht specifikoni numrat e tyre (në çdo renditje të dëshiruar) si një grup në argumentin e tretë të funksionit:
Funksioni OFFSET kthen një grup dinamik
Një nga funksionet më interesante dhe më të dobishme (pas VLOOKUP) për analizën e të dhënave është funksioni SHPENZIMI (OFFSET), të cilit i kam kushtuar një herë një kapitull të tërë në librin tim dhe një artikull këtu. Vështirësia në të kuptuarit dhe zotërimin e këtij funksioni ka qenë gjithmonë se si rezultat kthente një grup (gamë) të dhënash, por ne nuk mund ta shihnim atë, sepse Excel ende nuk dinte të punonte me vargje jashtë kutisë.
Tani ky problem është në të kaluarën. Shihni se si tani, duke përdorur një formulë të vetme dhe një grup dinamik të kthyer nga OFFSET, mund të nxirrni të gjitha rreshtat për një produkt të caktuar nga çdo tabelë e renditur:
Le të hedhim një vështrim në argumentet e saj:
- A1 – qeliza fillestare (pika e referencës)
- ПОИСКПОЗ(F2;A2:A30;0) - llogaritja e zhvendosjes nga qeliza fillestare poshtë - në lakrën e parë të gjetur.
- 0 - zhvendosja e "dritares" djathtas në lidhje me qelizën fillestare
- СЧЁТЕСЛИ(A2:A30;F2) – llogaritja e lartësisë së “dritares” së kthyer – numri i rreshtave ku ka lakër.
- 4 — madhësia e “dritares” horizontalisht, dmth. nxjerr 4 kolona
Funksione të reja për vargje dinamike
Përveç mbështetjes së mekanizmit të grupeve dinamike në funksionet e vjetra, disa funksione krejtësisht të reja janë shtuar në Microsoft Excel, të mprehura posaçërisht për të punuar me vargje dinamike. Në veçanti, këto janë:
- KLASA (Rendit) – rendit gamën e hyrjes dhe prodhon një grup dinamik në dalje
- SORTPO (NDAJ SIPAS) - mund të rendit një varg sipas vlerave nga një tjetër
- Filtër (FILTER) – merr rreshta nga diapazoni i burimit që plotësojnë kushtet e specifikuara
- UNIK (UNIK) – nxjerr vlera unike nga një gamë ose heq dublikatat
- SLMASIVE (RANDARRAY) – gjeneron një grup numrash të rastësishëm të një madhësie të caktuar
- PAS LINDJES (SEKUENCA) — formon një grup nga një varg numrash me një hap të caktuar
Më shumë rreth tyre - pak më vonë. Ata ia vlen një artikull i veçantë (dhe jo një) për studim të zhytur në mendime 🙂
Konkluzione
Nëse keni lexuar gjithçka që është shkruar më lart, atëherë mendoj se tashmë e kuptoni shkallën e ndryshimeve që kanë ndodhur. Shumë gjëra në Excel tani mund të bëhen më lehtë, më lehtë dhe më logjike. Duhet të pranoj se jam pak i tronditur se sa artikuj do të duhet të korrigjohen tani këtu, në këtë faqe dhe në librat e mi, por unë jam gati ta bëj këtë me një zemër të lehtë.
Duke përmbledhur rezultatet, rekuizitë vargjeve dinamike, mund të shkruani sa vijon:
- Ju mund të harroni për kombinimin Ctrl+Ndryshim+Shkruani. Excel tani nuk sheh asnjë ndryshim midis "formulave të rregullta" dhe "formulave të grupit" dhe i trajton ato në të njëjtën mënyrë.
- Rreth funksionit SUMPRODUCT (SUMPRODUCT), e cila është përdorur më parë për të futur formulat e grupeve pa Ctrl+Ndryshim+Shkruani ju gjithashtu mund të harroni - tani është mjaft e lehtë SUM и Shkruani.
- Tabelat inteligjente dhe funksionet e njohura (SUM, IF, VLOOKUP, SUMIFS, etj.) tani gjithashtu mbështesin plotësisht ose pjesërisht vargje dinamike.
- Ekziston një përputhshmëri e prapambetur: nëse hapni një libër pune me vargje dinamike në një version të vjetër të Excel, ato do të kthehen në formula të grupeve (në kllapa kaçurrelë) dhe do të vazhdojnë të punojnë në "stilin e vjetër".
Gjeti një numër minuset:
- Ju nuk mund të fshini rreshta, kolona ose qeliza individuale nga një grup dinamik, dmth. ai jeton si një entitet i vetëm.
- Ju nuk mund të renditni një grup dinamik në mënyrën e zakonshme Të dhënat – Renditja (Të dhënat - Rendit). Tani ekziston një funksion i veçantë për këtë. KLASA (Rendit).
- Një diapazon dinamik nuk mund të shndërrohet në një tabelë inteligjente (por mund të bëni një diapazon dinamik bazuar në një tabelë inteligjente).
Sigurisht, ky nuk është fundi dhe jam i sigurt se Microsoft do të vazhdojë të përmirësojë këtë mekanizëm në të ardhmen.
Ku mund të shkarkoj?
Dhe së fundi, pyetja kryesore 🙂
Microsoft së pari njoftoi dhe tregoi një pamje paraprake të grupeve dinamike në Excel në shtator 2018 në një konferencë Ndez. Në muajt e ardhshëm, pati një testim të plotë dhe futje të veçorive të reja, fillimisht cats punonjës të vetë Microsoft-it, dhe më pas në testues vullnetarë nga rrethi i Office Insiders. Këtë vit, përditësimi që shton grupe dinamike filloi të shpërndahej gradualisht tek abonentët e rregullt të Office 365. Për shembull, e mora vetëm në gusht me abonimin tim në Office 365 Pro Plus (Të synuar mujor).
Nëse Excel-i juaj nuk ka ende vargje dinamike, por vërtet dëshironi të punoni me ta, atëherë ekzistojnë opsionet e mëposhtme:
- Nëse keni një abonim në Office 365, thjesht mund të prisni derisa ky përditësim të arrijë tek ju. Sa shpejt ndodh kjo varet nga sa shpesh përditësimet dërgohen në zyrën tuaj (një herë në vit, një herë në gjashtë muaj, një herë në muaj). Nëse keni një kompjuter të korporatës, mund t'i kërkoni administratorit të konfigurojë përditësimet që të shkarkohen më shpesh.
- Ju mund të bashkoheni me radhët e atyre vullnetarëve të testimit të Office Insiders – atëherë do të jeni të parët që do të merrni të gjitha veçoritë dhe funksionet e reja (por ka një shans për rritje të gabimeve në Excel, sigurisht).
- Nëse nuk keni një abonim, por një version të pavarur të Excel-it, atëherë do të duhet të prisni deri në lëshimin e versionit të ardhshëm të Office dhe Excel në 2022, të paktën. Përdoruesit e versioneve të tilla marrin vetëm përditësime të sigurisë dhe rregullime të gabimeve, dhe të gjitha "të mirat" e reja tani shkojnë vetëm për abonentët e Office 365. E trishtueshme por e vërtetë 🙂
Në çdo rast, kur vargjet dinamike shfaqen në Excel - pas këtij artikulli, do të jeni gati për të 🙂
- Cilat janë formulat e grupeve dhe si t'i përdorim ato në Excel
- Përmbledhja e dritares (rangut) duke përdorur funksionin OFFSET
- 3 mënyra për të transpozuar një tabelë në Excel