Dinamiese skikkings in Excel

Wat is dinamiese skikkings

In September 2018 het Microsoft 'n opdatering vrygestel wat 'n heeltemal nuwe hulpmiddel by Microsoft Excel voeg: Dynamic Arrays en 7 nuwe funksies om daarmee te werk. Hierdie dinge, sonder oordrywing, verander radikaal al die gewone tegniek om met formules en funksies te werk en raak letterlik elke gebruiker.

Oorweeg 'n eenvoudige voorbeeld om die essensie te verduidelik.

Gestel ons het 'n eenvoudige tabel met data oor stad-maande. Wat sal gebeur as ons enige leë sel aan die regterkant van die blad kies en 'n formule daarin invoer wat nie na een sel skakel nie, maar onmiddellik na 'n reeks?

In alle vorige weergawes van Excel, nadat u op Tik ons sou die inhoud van slegs een eerste sel B2 kry. Hoe anders?

Wel, of dit sou moontlik wees om hierdie reeks in 'n soort samevoegingsfunksie soos =SUM(B2:C4) te verpak en 'n groottotaal daarvoor te kry.

As ons meer komplekse bewerkings benodig as 'n primitiewe som, soos die onttrekking van unieke waardes of Top 3, dan sal ons ons formule as 'n skikkingsformule moet invoer met 'n sleutelbordkortpad Ctrl+Skuif+Tik.

Nou is alles anders.

Nou nadat ons so 'n formule ingevoer het, kan ons eenvoudig op klik Tik – en kry as gevolg daarvan onmiddellik al die waardes uXNUMXbuXNUMXb waarna ons verwys het:

Dit is nie magie nie, maar die nuwe dinamiese skikkings wat Microsoft Excel nou het. Welkom by die nuwe wêreld 🙂

Kenmerke van werk met dinamiese skikkings

Tegnies word ons hele dinamiese skikking in die eerste sel G4 gestoor, en vul die vereiste aantal selle regs en af ​​met sy data. As jy enige ander sel in die skikking kies, sal die skakel in die formulebalk onaktief wees, wat wys dat ons in een van die "kind" selle is:

'n Poging om een ​​of meer "kind"-selle uit te vee, sal tot niks lei nie - Excel sal dit onmiddellik herbereken en vul.

Terselfdertyd kan ons veilig na hierdie "kind"-selle in ander formules verwys:

As jy die eerste sel van 'n skikking kopieer (byvoorbeeld van G4 na F8), dan sal die hele skikking (sy verwysings) in dieselfde rigting beweeg as in gewone formules:

As ons die skikking moet skuif, sal dit genoeg wees om te beweeg (met die muis of 'n kombinasie van Ctrl+X, Ctrl+V), weer net die eerste hoofsel G4 – daarna sal dit na 'n nuwe plek oorgeplaas word en ons hele skikking sal weer uitgebrei word.

As jy iewers anders op die blad na die geskepde dinamiese skikking moet verwys, kan jy die spesiale karakter # ("pond") na die adres van sy voorste sel gebruik:

Byvoorbeeld, nou kan jy maklik 'n aftreklys in 'n sel maak wat na die geskepde dinamiese skikking verwys:

Dinamiese skikking foute

Maar wat gebeur as daar nie genoeg spasie is om die skikking uit te brei nie, of as daar selle is wat reeds deur ander data in sy pad beset is? Ontmoet 'n fundamenteel nuwe tipe foute in Excel - #OORDRAG! (#MORS!):

Soos altyd, as ons op die ikoon met 'n geel diamant en 'n uitroepteken klik, sal ons 'n meer gedetailleerde verduideliking van die bron van die probleem kry en ons kan vinnig interfererende selle vind:

Soortgelyke foute sal voorkom as die skikking van die blad af gaan of 'n saamgevoegde sel tref. As jy die hindernis verwyder, sal alles dadelik reggestel word.

Dinamiese skikkings en slim tabelle

As die dinamiese skikking na 'n "slim" tabel wys wat deur 'n sleutelbordkortpad geskep is Ctrl+T of deur Tuis – Formateer as 'n tabel (Tuis — Formateer as tabel), dan sal dit ook sy hoofkwaliteit erf – outomatiese grootte.

Wanneer nuwe data onder of regs bygevoeg word, sal die slim tabel en dinamiese reeks ook outomaties strek:

Daar is egter een beperking: ons kan nie 'n dinamiese reeksverwysing in forumulas binne 'n slim tabel gebruik nie:

Dinamiese skikkings en ander Excel-kenmerke

Goed, sê jy. Dit alles is interessant en snaaks. Dit is nie nodig om, soos voorheen, die formule handmatig uit te rek met 'n verwysing na die eerste sel van die oorspronklike reeks af en na regs en dit alles. En dis al?

Nie heeltemal.

Dinamiese skikkings is nie net nog 'n hulpmiddel in Excel nie. Nou is hulle ingebed in die hart (of brein) van Microsoft Excel – sy berekeningsenjin. Dit beteken dat ander Excel-formules en -funksies wat aan ons bekend is, nou ook werk met dinamiese skikkings ondersteun. Kom ons kyk na 'n paar voorbeelde om jou 'n idee te gee van die diepte van die veranderinge wat plaasgevind het.

Transponeer

Om 'n reeks te transponeer (rye en kolomme omruil) het Microsoft Excel nog altyd 'n ingeboude funksie gehad OORDRAG (TRANSPONEER). Om dit egter te gebruik, moet jy eers die reeks vir die resultate korrek kies (byvoorbeeld, as die invoer 'n reeks van 5×3 was, dan moes jy 3×5 gekies het), voer dan die funksie in en druk die kombinasie Ctrl+Skuif+Tik, omdat dit net in skikkingsformule-modus kon werk.

Nou kan jy net een sel kies, dieselfde formule daarin invoer en op die normale klik Tik - dinamiese skikking sal alles op sigself doen:

Vermenigvuldigingstabel

Dit is die voorbeeld wat ek gebruik het toe ek gevra is om die voordele van skikkingsformules in Excel te visualiseer. Nou, om die hele Pythagorese tabel te bereken, is dit genoeg om in die eerste sel B2 te staan, 'n formule daar in te voer wat twee skikkings vermenigvuldig (vertikale en horisontale stel getalle 1..10) en klik eenvoudig op Tik:

Gom en kas-omskakeling

Skikkings kan nie net vermenigvuldig word nie, maar ook saamgeplak word met die standaard operateur & (ampersand). Gestel ons moet die voornaam en van uit twee kolomme onttrek en die springval in die oorspronklike data regstel. Ons doen dit met een kort formule wat die hele skikking vorm, en dan pas ons die funksie daarop toe PROPNAG (BEHEERLIK)om die register op te ruim:

Slot Top 3

Gestel ons het 'n klomp getalle waaruit ons die top drie resultate wil aflei, rangskik hulle in dalende volgorde. Nou word dit gedoen deur een formule en, weer, sonder enige Ctrl+Skuif+Tik soos voorheen:

As jy wil hê dat die resultate nie in 'n kolom geplaas moet word nie, maar in 'n ry, dan is dit genoeg om die dubbelpunte (lynskeier) in hierdie formule te vervang met 'n kommapunt (elementskeier binne een lyn). In die Engelse weergawe van Excel is hierdie skeiers onderskeidelik semikolons en kommas.

VLOOKUP onttrek verskeie kolomme gelyktydig

Funksies VPR (VERSOEKING) nou kan jy waardes nie uit een nie, maar uit verskeie kolomme gelyktydig trek - spesifiseer net hul nommers (in enige gewenste volgorde) as 'n skikking in die derde argument van die funksie:

OFFSET-funksie gee 'n dinamiese skikking terug

Een van die interessantste en nuttigste (na VLOOKUP) funksies vir data-analise is die funksie BESKIKKING (OFFSET), waaraan ek op 'n tyd 'n hele hoofstuk in my boek en 'n artikel hier gewy het. Die moeilikheid om hierdie funksie te verstaan ​​en te bemeester was nog altyd dat dit 'n reeks (reeks) data as gevolg daarvan teruggestuur het, maar ons kon dit nie sien nie, want Excel het steeds nie geweet hoe om met skikkings uit die boks te werk nie.

Nou is hierdie probleem in die verlede. Kyk hoe jy nou, met behulp van 'n enkele formule en 'n dinamiese skikking wat deur OFFSET teruggestuur word, alle rye vir 'n gegewe produk uit enige gesorteerde tabel kan onttrek:

Kom ons kyk na haar argumente:

  • A1 – beginsel (verwysingspunt)
  • ПОИСКПОЗ(F2;A2:A30;0) – berekening van die verskuiwing van die beginsel af – na die eerste gevind kool.
  • 0 – verskuiwing van die “venster” na regs relatief tot die beginsel
  • СЧЁТЕСЛИ(A2:A30;F2) – berekening van die hoogte van die teruggekeerde “venster” – die aantal reëls waar daar kool is.
  • 4 — grootte van die “venster” horisontaal, dws voer 4 kolomme uit

Nuwe funksies vir dinamiese skikkings

Benewens die ondersteuning van die dinamiese skikkingsmeganisme in ou funksies, is verskeie heeltemal nuwe funksies by Microsoft Excel gevoeg, spesifiek opgeskerp om met dinamiese skikkings te werk. Dit is veral:

  • GRAAD (SORTEER) – sorteer die invoerreeks en produseer 'n dinamiese skikking op die uitset
  • SORTPO (SORTEER VOLGENS) - kan een reeks volgens waardes van 'n ander sorteer
  • FILTER (FILTER) – haal rye uit die bronreeks wat aan die gespesifiseerde voorwaardes voldoen
  • UNIK (UNIEK) - onttrek unieke waardes uit 'n reeks of verwyder duplikate
  • SLMASSIEF (RANDARRAY) – genereer 'n verskeidenheid ewekansige getalle van 'n gegewe grootte
  • NAGEBOORTE (VOLGORDE) — vorm 'n skikking uit 'n reeks getalle met 'n gegewe stap

Meer oor hulle - 'n bietjie later. Hulle is 'n aparte artikel (en nie een nie) werd vir deurdagte studie 🙂

Gevolgtrekkings

As jy alles hierbo gelees het, dan dink ek jy besef reeds die omvang van die veranderinge wat plaasgevind het. Soveel dinge in Excel kan nou makliker, makliker en meer logies gedoen word. Ek moet erken dat ek 'n bietjie geskok is oor hoeveel artikels nou hier, op hierdie webwerf en in my boeke reggestel sal moet word, maar ek is gereed om dit met 'n ligte hart te doen.

Opsomming van die resultate, voor- dinamiese skikkings, kan jy die volgende skryf:

  • Jy kan vergeet van die kombinasie Ctrl+Skuif+Tik. Excel sien nou geen verskil tussen "gewone formules" en "skikkingsformules" nie en behandel hulle op dieselfde manier.
  • Oor die funksie SUMPRODUCT (SOMPRODUK), wat voorheen gebruik is om skikkingsformules in te voer sonder Ctrl+Skuif+Tik jy kan ook vergeet – nou is dit maklik genoeg som и Tik.
  • Slim tabelle en bekende funksies (SOM, IF, VLOOKUP, SUMIFS, ens.) ondersteun nou ook ten volle of gedeeltelik dinamiese skikkings.
  • Daar is terugwaartse verenigbaarheid: as jy 'n werkboek met dinamiese skikkings in 'n ou weergawe van Excel oopmaak, sal dit in skikkingsformules verander (in krullerige hakies) en voortgaan om in die "ou styl" te werk.

Het 'n nommer gekry nadele:

  • Jy kan nie individuele rye, kolomme of selle uit 'n dinamiese skikking uitvee nie, dit wil sê dit leef as 'n enkele entiteit.
  • Jy kan nie 'n dinamiese skikking op die gewone manier deur sorteer nie Data – Sorteer (Data — Sorteer). Daar is nou 'n spesiale funksie hiervoor. GRAAD (SORTEER).
  • 'n Dinamiese reeks kan nie in 'n slimtabel verander word nie (maar jy kan 'n dinamiese reeks op grond van 'n slimtabel maak).

Natuurlik is dit nie die einde nie, en ek is seker Microsoft sal voortgaan om hierdie meganisme in die toekoms te verbeter.

Waar kan ek aflaai?

En laastens, die hoofvraag 🙂

Microsoft het eers in September 2018 op 'n konferensie 'n voorskou van dinamiese skikkings in Excel aangekondig en gewys Aansteek. In die volgende paar maande was daar 'n deeglike toetsing en inwerkingstelling van nuwe kenmerke, eerstens katte werknemers van Microsoft self, en dan op vrywillige toetsers uit die kring van Office Insiders. Hierdie jaar het die opdatering wat dinamiese skikkings byvoeg, geleidelik aan gewone Office 365-intekenare begin ontplooi. Ek het dit byvoorbeeld eers in Augustus met my Office 365 Pro Plus (Maandelikse gerig)-intekening ontvang.

As jou Excel nog nie dinamiese skikkings het nie, maar jy wil regtig daarmee werk, dan is daar die volgende opsies:

  • As jy 'n Office 365-intekening het, kan jy eenvoudig wag totdat hierdie opdatering jou bereik. Hoe vinnig dit gebeur, hang af van hoe gereeld opdaterings by jou kantoor afgelewer word (een keer per jaar, een keer elke ses maande, een keer per maand). As jy 'n korporatiewe rekenaar het, kan jy jou administrateur vra om opdaterings op te stel om meer gereeld afgelaai te word.
  • Jy kan by die geledere van daardie Office Insiders-toetsvrywilligers aansluit – dan sal jy die eerste wees om al die nuwe kenmerke en funksies te ontvang (maar daar is natuurlik 'n kans op groter foute in Excel).
  • As jy nie 'n intekening het nie, maar 'n selfstandige weergawe van Excel, sal jy ten minste moet wag tot die vrystelling van die volgende weergawe van Office en Excel in 2022. Gebruikers van sulke weergawes ontvang slegs sekuriteitsopdaterings en foutoplossings, en al die nuwe “goedjies” gaan nou net na Office 365-intekenare. Hartseer maar waar 🙂

In elk geval, wanneer dinamiese skikkings in jou Excel verskyn – na hierdie artikel, sal jy gereed wees daarvoor 🙂

  • Wat is skikkingsformules en hoe om dit in Excel te gebruik
  • Venster (reeks) opsomming met behulp van die OFFSET-funksie
  • 3 maniere om 'n tabel in Excel te transponeer

Lewer Kommentaar