Dynamesch Arrays an Excel

Wat sinn dynamesch Arrays

Am September 2018 huet Microsoft en Update verëffentlecht deen e komplett neit Tool zu Microsoft Excel bäidréit: Dynamic Arrays a 7 nei Funktiounen fir mat hinnen ze schaffen. Dës Saachen, ouni iwwerdreiwen, radikal änneren all üblech Technik vun Aarbecht mat Formelen a Funktiounen a Suerg, wuertwiertlech, all Benotzer.

Betruecht en einfacht Beispill fir d'Essenz z'erklären.

Ugeholl mir hunn en einfachen Dësch mat Daten iwwer Stadmonaten. Wat geschitt wa mir eng eidel Zell op der rietser Säit vum Blat auswielen an eng Formel anzeginn déi net op eng Zelle verbënnt, awer direkt op eng Rei?

An all fréiere Versioune vun Excel, nodeems Dir op klickt gitt mir géifen den Inhalt vun nëmmen enger éischter Zell B2 kréien. Wéi soss?

Gutt, oder et wier méiglech dës Gamme an eng Aart aggregéierend Funktioun wéi =SUM (B2: C4) ze wéckelen an e Grousstotal dofir ze kréien.

Wa mir méi komplex Operatioune brauche wéi eng primitiv Zomm, sou wéi d'Extraktioun vun eenzegaartege Wäerter oder Top 3, da musse mir eis Formel als Arrayformel mat enger Tastatur Ofkiirzung aginn Ctrl+Verréckelung+gitt.

Elo ass alles anescht.

Elo nodeems Dir esou eng Formel aginn hutt, kënne mir einfach op klickt gitt - a kritt als Resultat direkt all d'Wäerter uXNUMXbuXNUMXb op déi mir bezeechent hunn:

Dëst ass keng Magie, awer déi nei dynamesch Arrays déi Microsoft Excel elo huet. Wëllkomm op déi nei Welt 🙂

Features fir mat dynamesche Arrays ze schaffen

Technesch ass eis ganz dynamesch Array an der éischter Zell G4 gespäichert, déi erfuerderlech Zuel vun Zellen no riets an erof mat sengen Donnéeën fëllt. Wann Dir eng aner Zell an der Array auswielt, da wäert de Link an der Formelbar inaktiv sinn, wat weist datt mir an enger vun de "Kand" Zellen sinn:

E Versuch fir een oder méi "Kand" Zellen ze läschen wäert näischt zu näischt féieren - Excel wäert direkt nei berechnen a fëllen.

Zur selwechter Zäit kënne mir sécher op dës "Kand" Zellen an anere Formelen bezéien:

Wann Dir déi éischt Zelle vun engem Array kopéiert (zum Beispill vu G4 op F8), da wäert de ganze Array (seng Referenzen) an déiselwecht Richtung bewegen wéi a reguläre Formelen:

Wa mir d'Array musse réckelen, da wäert et genuch sinn ze bewegen (mat der Maus oder enger Kombinatioun vun Ctrl+X, Ctrl+V), erëm nëmmen déi éischt Haaptzell G4 - duerno gëtt et op eng nei Plaz transferéiert an eis ganz Array gëtt erëm erweidert.

Wann Dir soss anzwousch um Blat op déi erstallt dynamesch Array ze referenzéieren musst, da kënnt Dir de spezielle Charakter # ("Pound") no der Adress vu senger Haaptzell benotzen:

Zum Beispill, elo kënnt Dir einfach eng Dropdown-Lëscht an enger Zell maachen, déi op déi erstallt dynamesch Array bezitt:

Dynamesch Array Feeler

Awer wat geschitt wann et net genuch Plaz ass fir d'Array auszebauen, oder wann et Zellen sinn déi scho vun aneren Donnéeën op hirem Wee besat sinn? Trefft eng grondsätzlech nei Aart vu Feeler an Excel - #TRANSFER! (#SPILL!):

Wéi ëmmer, wa mir op d'Ikon mat engem gielen Diamant an engem Ausrufezeeche klickt, kréie mir eng méi detailléiert Erklärung vun der Quell vum Problem a mir kënne séier interferéierend Zellen fannen:

Ähnlech Feeler wäerte geschéien wann d'Array vum Blat ofgeet oder eng fusionéiert Zelle trefft. Wann Dir den Hindernis läscht, da gëtt alles direkt op der Flucht korrigéiert.

Dynamesch Arrays a Smart Dëscher

Wann déi dynamesch Array op eng "Smart" Dësch weist, déi vun enger Tastatur Ofkiirzung erstallt gëtt Ctrl+T oder duerch Home - Format als Dësch (Home - Format als Table), da wäert et och seng Haaptqualitéit ierwen - automatesch Gréisst.

Wann Dir nei Donnéeën no ënnen oder riets bäidréit, gëtt de Smart Dësch an déi dynamesch Gamme och automatesch ausdehnen:

Wéi och ëmmer, et gëtt eng Begrenzung: mir kënnen net eng dynamesch Gamme Referenz an Forumelen an engem Smart Dësch benotzen:

Dynamesch Arrays an aner Excel Features

Okay, sot Dir. All dëst ass interessant a witzeg. Kee Besoin, wéi virdrun, d'Formel manuell ze strecken mat enger Referenz op déi éischt Zelle vum urspréngleche Beräich erof an no riets an all dat. An dat ass alles?

Net zimlech.

Dynamesch Arrays sinn net nëmmen en anert Tool an Excel. Elo si se am Häerz (oder Gehir) vu Microsoft Excel agebaut - säi Berechnungsmotor. Dëst bedeit datt aner Excel Formelen a Funktiounen, déi eis vertraut sinn, elo och d'Aarbecht mat dynamesche Arrays ënnerstëtzen. Loosst eis e puer Beispiller kucken fir Iech eng Iddi ze ginn iwwer d'Tiefe vun den Ännerungen déi stattfonnt hunn.

Transposéierter

Fir e Beräich ze transposéieren (Reihen a Kolonnen austauschen) huet Microsoft Excel ëmmer eng agebaute Funktioun TRANSP (TRANSPOSE). Wéi och ëmmer, fir se ze benotzen, musst Dir als éischt d'Band fir d'Resultater korrekt auswielen (zum Beispill, wann den Input e Beräich vu 5 × 3 war, da musst Dir 3 × 5 gewielt hunn), gitt dann d'Funktioun an dréckt op Kombinatioun Ctrl+Verréckelung+gitt, well et nëmmen am Array Formel Modus funktionéiere konnt.

Elo kënnt Dir nëmmen eng Zell auswielen, gitt déiselwecht Formel an a klickt op déi normal gitt - dynamesch Array wäert alles selwer maachen:

Multiplikatiounstabell

Dëst ass d'Beispill, dat ech benotzt gi fir wann ech gefrot gi sinn d'Virdeeler vun Arrayformelen an Excel ze visualiséieren. Elo, fir de ganzen Pythagorean Dësch ze berechnen, ass et genuch fir an der éischter Zelle B2 ze stoen, gitt do eng Formel déi zwee Arrays multiplizéiert (vertikal an horizontal Set vun Zuelen 1..10) a klickt einfach op gitt:

Kleeblatt a Fall Konversioun

Arrays kënnen net nëmmen multiplizéiert ginn, awer och zesumme mam Standardoperateur & (ampersand) gekollt ginn. Ugeholl mir mussen den Virnumm an de Familljennumm aus zwou Kolonnen extrahéieren an de Sprangfall an den ursprénglechen Donnéeën korrigéieren. Mir maachen dat mat enger kuerzer Formel déi de ganzen Array bildt, an da gëlle mir d'Funktioun op PROPNACH (PROPER)fir de Register opzeraumen:

Conclusioun Top 3

Ugeholl datt mir eng Rëtsch Zuelen hunn, aus deenen mir déi dräi Top Resultater wëllen ofleeden, se an ofstigend Uerdnung arrangéieren. Elo gëtt dat duerch eng Formel gemaach an erëm ouni Ctrl+Verréckelung+gitt wéi virdrun:

Wann Dir wëllt datt d'Resultater net an enger Kolonn plazéiert ginn, mee an enger Zeil, da geet et duer fir d'Kolonen (Linn-Separator) an dëser Formel mat engem Semikolon (Element-Separator bannent enger Zeil) ze ersetzen. An der englescher Versioun vun Excel sinn dës Separatoren semikolonen a Kommaen, respektiv.

VLOOKUP extrahéiert verschidde Kolonnen gläichzäiteg

Funktiounen VPR (VLOOKUP) elo kënnt Dir Wäerter net vun engem, mee aus e puer Kolonnen gläichzäiteg zéien - gitt just hir Zuelen (an all gewënschten Uerdnung) als Array am drëtten Argument vun der Funktioun:

OFFSET Funktioun déi eng dynamesch Array zréckginn

Eng vun den interessantsten an nëtzlechsten (no VLOOKUP) Funktiounen fir Datenanalyse ass d'Funktioun VERSTÄNNEGUNG (OFFSET), deem ech eng Kéier e ganzt Kapitel a mengem Buch an en Artikel hei gewidmet hunn. D'Schwieregkeet fir dës Funktioun ze verstoen an ze beherrschen war ëmmer datt et eng Array (Gamme) vun Daten als Resultat zréckginn, awer mir konnten et net gesinn, well Excel nach ëmmer net wousst wéi een mat Arrays aus der Këscht funktionnéiert.

Elo ass dëse Problem an der Vergaangenheet. Kuckt wéi Dir elo, andeems Dir eng eenzeg Formel an eng dynamesch Array benotzt, déi vum OFFSET zréckkomm ass, all Zeilen fir e bestëmmte Produkt aus all zortéierten Dësch extrahéiert:

Loosst eis hir Argumenter kucken:

• A1 - Startzelle (Referenzpunkt)
• ПОИСКПОЗ(F2;A2:A30;0) – Berechnung vun der Verréckelung vun der Startzell erof – op den éischte fonnte Kabes.
• 0 - Verréckelung vun der "Fënster" no riets relativ zu der Startzelle
  
• СЧЁТЕСЛИ(A2:A30;F2) - Berechnung vun der Héicht vun der zréck "Fënster" - d'Zuel vun de Linnen wou et Kabes ass.
• 4 - Gréisst vun der "Fënster" horizontal, dh Ausgab 4 Kolonnen

Nei Funktiounen fir dynamesch Arrays

Zousätzlech fir den dynamesche Array-Mechanismus an alen Funktiounen z'ënnerstëtzen, sinn e puer komplett nei Funktiounen zu Microsoft Excel bäigefüügt, speziell geschärft fir mat dynamesche Arrays ze schaffen. Besonnesch sinn dës:

• Schouljoer (SORT) - sortéiert den Inputbereich a produzéiert eng dynamesch Array op der Ausgab
  
• SORTPO (SORTBY) - kann ee Beräich no Wäerter vun engem aneren sortéieren
  
• FILTER (FILTER) - recuperéiert Reihen aus der Quellberäich déi de spezifizéierte Konditioune erfëllen
• UNIK (UNIQUE) - Extrait eenzegaarteg Wäerter aus enger Rei oder läscht Duplikaten
  
• SLMASSIV (RANDARRAY) - generéiert eng Rei vun zoufälleg Zuelen vun enger bestëmmter Gréisst
• NËMMEN (SEQUENCE) - bildt eng Array aus enger Sequenz vun Zuelen mat engem bestëmmte Schrëtt
  

Méi iwwer hinnen - e bësse méi spéit. Si wäert eng separat Artikel (an net een) fir Duerchduechte Etude 🙂

Conclusiounen

Wann Dir alles uewendriwwer gelies hutt, da mengen ech, datt Dir schonn d'Skala vun den Ännerungen erkennt, déi stattfonnt hunn. Sou vill Saachen an Excel kënnen elo méi einfach, méi einfach a méi logesch gemaach ginn. Ech muss zouginn, datt ech e bësse schockéiert sinn wéivill Artikelen elo hei, op dësem Site an a menge Bicher korrigéiert musse ginn, mee ech si prett dat mat engem liichtem Häerz ze maachen.

Zesummefaassend d'Resultater, pluses dynamesch Arrays, kënnt Dir déi folgend schreiwen:

• Dir kënnt d'Kombinatioun vergiessen Ctrl+Verréckelung+gitt. Excel gesäit elo keen Ënnerscheed tëscht "normale Formelen" an "Array Formelen" a behandelt se déiselwecht Manéier.
• Iwwer d'Funktioun SUMPRODUCT (SUMPRODUKT), déi virdru benotzt gouf fir Arrayformelen anzeginn ouni Ctrl+Verréckelung+gitt Dir kënnt och vergiessen - elo ass et einfach genuch SUM и gitt.
• Smart Dëscher a vertraute Funktiounen (SUM, IF, VLOOKUP, SUMIFS, etc.) ënnerstëtzen elo och komplett oder deelweis dynamesch Arrays.
• Et gëtt Réckkompatibilitéit: wann Dir e Workbook mat dynamesche Arrays an enger aler Versioun vun Excel opmaacht, ginn se an Arrayformelen (a Curly Klammeren) a schaffe weider am "ale Stil".

Fannt eng Zuel Minusen:

• Dir kënnt net eenzel Reihen, Kolonnen oder Zellen aus enger dynamescher Array läschen, dh et lieft als eenzeg Entitéit.
• Dir kënnt net eng dynamesch Array op déi üblech Manéier sortéieren Daten - Sortéieren (Daten - Sort). Et gëtt elo eng speziell Funktioun dofir. Schouljoer (SORT).
• Eng dynamesch Palette kann net an e Smart Dësch ëmgewandelt ginn (mee Dir kënnt en dynamesche Beräich baséieren op engem Smart Dësch).
  

Natierlech ass dëst net d'Enn, an ech si sécher datt Microsoft dëse Mechanismus an Zukunft wäert verbesseren.

Wou kann ech download?

An endlech, d'Haaptfro 🙂

Microsoft huet fir d'éischt ugekënnegt a gewisen eng Virschau vun dynamesche Arrays an Excel zréck am September 2018 op enger Konferenz ee richteg. An den nächste Méint gouf et eng grëndlech Testen a Lafen vun neie Featuren, fir d'éischt Kazen Mataarbechter vu Microsoft selwer, an dann op fräiwëlleg Tester aus dem Krees vun Office Insiders. Dëst Joer huet d'Aktualiséierung, déi dynamesch Arrays bäigefüügt, lues a lues un normale Office 365 Abonnenten ausgerullt ginn. Zum Beispill hunn ech et nëmmen am August mat mengem Office 365 Pro Plus (Monthly Targeted) Abonnement kritt.

Wann Ären Excel nach keng dynamesch Arrays huet, awer Dir wëllt wierklech mat hinnen schaffen, da ginn et déi folgend Optiounen:

• Wann Dir en Office 365 Abonnement hutt, kënnt Dir einfach waarden bis dësen Update Iech erreecht. Wéi séier dat geschitt hänkt dovun of wéi dacks Updates op Ärem Office geliwwert ginn (eemol am Joer, eemol all sechs Méint, eemol am Mount). Wann Dir e Firmen-PC hutt, kënnt Dir Ären Administrator froen Updates opzestellen fir méi dacks erofzelueden.
  
• Dir kënnt an d'Ränge vun dësen Office Insiders Test Fräiwëlleger bäitrieden - da sidd Dir déi éischt fir all déi nei Features a Funktiounen ze kréien (awer et ass eng Chance fir méi Buggy an Excel, natierlech).
• Wann Dir keen Abonnement hutt, awer eng Këschte Standalone Versioun vun Excel, da musst Dir op d'mannst op d'Verëffentlechung vun der nächster Versioun vun Office an Excel am Joer 2022 waarden. D'Benotzer vun esou Versioune kréien nëmme Sécherheetsupdates a Bugfixes, an all déi nei "Goodies" ginn elo nëmmen un Office 365 Abonnenten. Traureg awer wouer 🙂
  

Op alle Fall, wann dynamesch Arrays an Ärem Excel erscheinen - no dësem Artikel sidd Dir prett dofir 🙂

• Wat sinn Arrayformelen a wéi se se an Excel benotzen
• Fënster (Gamme) Summatioun mat der OFFSET Funktioun
• 3 Weeër fir eng Tabell an Excel ze transposéieren