Saturs
Kas ir dinamiskie masīvi
2018. gada septembrī Microsoft izlaida atjauninājumu, kas programmai Microsoft Excel pievieno pilnīgi jaunu rīku: dinamiskos masīvus un 7 jaunas funkcijas darbam ar tiem. Šīs lietas bez pārspīlējumiem radikāli maina visu ierasto paņēmienu darbā ar formulām un funkcijām un uztrauc burtiski katru lietotāju.
Apsveriet vienkāršu piemēru, lai izskaidrotu būtību.
Pieņemsim, ka mums ir vienkārša tabula ar datiem par pilsētu mēnešiem. Kas notiks, ja lapas labajā pusē atlasīsim jebkuru tukšu šūnu un ievadīsim tajā formulu, kas saistās nevis ar vienu šūnu, bet gan uzreiz uz diapazonu?
Visās iepriekšējās Excel versijās pēc noklikšķināšanas uz ienākt mēs iegūtu tikai vienas pirmās šūnas B2 saturu. Kā gan citādi?
Vai arī šo diapazonu varētu ietīt kaut kādā apkopojošā funkcijā, piemēram, =SUM(B2:C4) un iegūt par to kopējo summu.
Ja mums būtu vajadzīgas sarežģītākas darbības nekā primitīva summa, piemēram, unikālu vērtību vai populārāko 3 izvilkšana, mums sava formula būtu jāievada kā masīva formula, izmantojot īsinājumtaustiņu. Ctrl+maiņa+ienākt.
Tagad viss ir savādāk.
Tagad pēc šādas formulas ievadīšanas mēs varam vienkārši noklikšķināt uz ienākt - un rezultātā nekavējoties iegūstiet visas vērtības uXNUMXbuXNUMXb, uz kurām mēs atsaucāmies:
Tā nav maģija, bet gan jaunie dinamiskie masīvi, kas tagad ir programmā Microsoft Excel. Laipni lūdzam jaunajā pasaulē 🙂
Iezīmes darbam ar dinamiskiem masīviem
Tehniski viss mūsu dinamiskais masīvs tiek glabāts pirmajā šūnā G4, aizpildot nepieciešamo šūnu skaitu pa labi un uz leju ar saviem datiem. Ja masīvā atlasāt kādu citu šūnu, saite formulu joslā būs neaktīva, parādot, ka atrodamies vienā no “bērnu” šūnām:
Mēģinājums izdzēst vienu vai vairākas “bērnu” šūnas ne pie kā nenovedīs – Excel tās nekavējoties pārrēķinās un aizpildīs.
Tajā pašā laikā mēs varam droši atsaukties uz šīm “bērnu” šūnām citās formulās:
Ja kopējot masīva pirmo šūnu (piemēram, no G4 uz F8), viss masīvs (tā atsauces) pārvietosies tajā pašā virzienā kā parastajās formulās:
Ja mums ir jāpārvieto masīvs, tad pietiks ar pārvietošanu (ar peli vai to kombināciju Ctrl+X, Ctrl+V), atkal tikai pirmā galvenā šūna G4 – pēc tās tā tiks pārcelta uz jaunu vietu un atkal tiks paplašināts viss mūsu masīvs.
Ja jums ir jāatsaucas kaut kur citur lapā uz izveidoto dinamisko masīvu, varat izmantot īpašo rakstzīmi # (“mārciņa”) aiz tās galvenās šūnas adreses:
Piemēram, tagad jūs varat viegli izveidot nolaižamo sarakstu šūnā, kas attiecas uz izveidoto dinamisko masīvu:
Dinamiskā masīva kļūdas
Bet kas notiek, ja nav pietiekami daudz vietas, lai paplašinātu masīvu, vai ja tās ceļā jau ir šūnas, kuras jau aizņem citi dati? Iepazīstieties ar principiāli jaunu kļūdu veidu programmā Excel - #PĀRSKATS! (#SPILL!):
Kā vienmēr, noklikšķinot uz ikonas ar dzeltenu dimantu un izsaukuma zīmi, mēs iegūsim detalizētāku problēmas avota skaidrojumu un varēsim ātri atrast traucējošās šūnas:
Līdzīgas kļūdas radīsies, ja masīvs iziet no lapas vai nonāk sapludinātā šūnā. Ja jūs noņemsit šķērsli, tad viss nekavējoties tiks izlabots.
Dinamiski masīvi un viedās tabulas
Ja dinamiskais masīvs norāda uz “viedo” tabulu, kas izveidota ar īsinājumtaustiņu Ctrl+T vai Sākums — formatējiet kā tabulu (Sākums — formatēt kā tabulu), tad tas pārmantos arī savu galveno kvalitāti – automātisko izmēru noteikšanu.
Pievienojot jaunus datus apakšā vai labajā pusē, arī viedā tabula un dinamiskais diapazons automātiski izstiepsies:
Tomēr ir viens ierobežojums: viedās tabulas forumos nevar izmantot dinamiskā diapazona atsauci:
Dinamiskie masīvi un citas Excel funkcijas
Labi, tu saki. Tas viss ir interesanti un smieklīgi. Nav nepieciešams, tāpat kā iepriekš, manuāli izstiept formulu ar atsauci uz sākotnējā diapazona pirmo šūnu uz leju un pa labi, un tas viss. Un tas arī viss?
Ne gluži.
Dinamiskie masīvi nav tikai vēl viens Excel rīks. Tagad tie ir iegulti pašā Microsoft Excel sirdī (vai smadzenēs) - tā aprēķinu dzinējā. Tas nozīmē, ka arī citas mums zināmās Excel formulas un funkcijas atbalsta darbu ar dinamiskiem masīviem. Apskatīsim dažus piemērus, lai sniegtu priekšstatu par notikušo izmaiņu dziļumu.
Transpose
Lai transponētu diapazonu (apmainīt rindas un kolonnas), programmā Microsoft Excel vienmēr ir bijusi iebūvēta funkcija TRANSPS (TRANSPOZĒT). Tomēr, lai to izmantotu, vispirms ir pareizi jāizvēlas rezultātu diapazons (piemēram, ja ievade bija diapazons 5×3, tad jābūt atlasītam 3×5), pēc tam ievadiet funkciju un nospiediet taustiņu kombinācija Ctrl+maiņa+ienākt, jo tas varētu darboties tikai masīva formulas režīmā.
Tagad jūs varat vienkārši atlasīt vienu šūnu, ievadīt tajā to pašu formulu un noklikšķināt uz parastās ienākt – dinamiskais masīvs visu darīs pats:
Reizināšanas tabula
Šis ir piemērs, kuru es mēdzu sniegt, kad man lūdza vizualizēt masīvu formulu priekšrocības programmā Excel. Tagad, lai aprēķinātu visu Pitagora tabulu, pietiek stāvēt pirmajā šūnā B2, ievadīt tur formulu, kas reizina divus masīvus (vertikālā un horizontālā skaitļu kopa 1..10) un vienkārši noklikšķiniet uz ienākt:
Līmēšana un korpusa pārveidošana
Masīvus var ne tikai pavairot, bet arī salīmēt kopā ar standarta operatoru & (ampersand). Pieņemsim, ka mums ir jāizņem vārds un uzvārds no divām kolonnām un jāizlabo lecošais reģistrs sākotnējos datos. Mēs to darām ar vienu īsu formulu, kas veido visu masīvu, un pēc tam tam piemērojam funkciju PROPNACH (PIEKĀRTA)lai sakārtotu reģistru:
Secinājums Top 3
Pieņemsim, ka mums ir virkne skaitļu, no kuriem mēs vēlamies iegūt trīs labākos rezultātus, sakārtojot tos dilstošā secībā. Tagad tas tiek darīts pēc vienas formulas un atkal bez jebkādas Ctrl+maiņa+ienākt tāpat kā iepriekš:
Ja vēlaties, lai rezultāti tiktu ievietoti nevis kolonnā, bet rindā, tad šajā formulā pietiek aizstāt kolus (rindas atdalītāju) ar semikolu (elementu atdalītājs vienas rindas ietvaros). Programmas Excel angļu valodas versijā šie atdalītāji ir attiecīgi semikoli un komats.
VLOOKUP izvelk vairākas kolonnas vienlaikus
Funkcijas VPR (MEKLĒT) tagad jūs varat izvilkt vērtības nevis no vienas, bet no vairākām kolonnām vienlaikus - vienkārši norādiet to numurus (jebkurā vēlamajā secībā) kā masīvu funkcijas trešajā argumentā:
Funkcija OFFSET, kas atgriež dinamisku masīvu
Viena no interesantākajām un noderīgākajām (pēc VLOOKUP) datu analīzes funkcijām ir funkcija APGLABĀŠANA (OFFSET), kam savulaik veltīju veselu nodaļu savā grāmatā un rakstu šeit. Grūtības šīs funkcijas izpratnē un apgūšanā vienmēr ir bijušas tādas, ka tā rezultātā atgrieza datu masīvu (diapazonu), taču mēs to nevarējām redzēt, jo Excel joprojām nezināja, kā strādāt ar masīviem, kas izņemti no kastes.
Tagad šī problēma ir pagātnē. Skatiet, kā tagad, izmantojot vienu formulu un dinamisko masīvu, ko atgriež OFFSET, varat iegūt visas rindas konkrētajam produktam no jebkuras sakārtotas tabulas:
Apskatīsim viņas argumentus:
- A1 - sākuma šūna (atskaites punkts)
- ПОИСКПОЗ(F2;A2:A30;0) – nobīdes aprēķins no starta šūnas uz leju – uz pirmo atrasto kāpostu.
- 0 – “loga” nobīde pa labi attiecībā pret sākuma šūnu
- СЧЁТЕСЛИ(A2:A30;F2) – atdotā “loga” augstuma aprēķins – rindu skaits, kur ir kāposti.
- 4 — “loga” izmērs horizontāli, ti, izvada 4 kolonnas
Jaunas funkcijas dinamiskajiem masīviem
Papildus dinamiskā masīva mehānisma atbalstam vecajās funkcijās Microsoft Excel ir pievienotas vairākas pilnīgi jaunas funkcijas, kas ir īpaši asinātas darbam ar dinamiskiem masīviem. Jo īpaši tie ir:
- GRADE (KĀRTOT) – sakārto ievades diapazonu un izvadē izveido dinamisku masīvu
- SORTPO (KĀRTOT PĒC) – var kārtot vienu diapazonu pēc vērtībām no cita
- FILTER (FILTER) – izgūst rindas no avota diapazona, kas atbilst norādītajiem nosacījumiem
- UNIK (UNIKĀLS) – izvelk unikālas vērtības no diapazona vai noņem dublikātus
- SLMASĪVS (RANDARRAY) – ģenerē noteikta izmēra nejaušu skaitļu masīvu
- PĒC DZIMŠANAS (SEQUENCE) — veido masīvu no skaitļu virknes ar noteiktu soli
Vairāk par tiem – nedaudz vēlāk. Tie ir atsevišķa raksta (un ne viena) vērti pārdomātai izpētei 🙂
secinājumi
Ja esi izlasījis visu augstāk rakstīto, tad domāju, ka tu jau apzinies notikušo pārmaiņu mērogu. Tik daudzas lietas programmā Excel tagad var paveikt vienkāršāk, vienkāršāk un loģiskāk. Jāatzīst, ka esmu nedaudz šokēts par to, cik daudz rakstu tagad būs jālabo šeit, šajā vietnē un manās grāmatās, bet esmu gatavs to darīt ar vieglu sirdi.
Apkopojot rezultātus, plusi dinamiskos masīvus, varat rakstīt:
- Jūs varat aizmirst par kombināciju Ctrl+maiņa+ienākt. Programma Excel tagad neredz atšķirību starp “parastajām formulām” un “masīva formulām”, un tās tiek apstrādātas vienādi.
- Par funkciju SUMPRODUCT (SUMPRODUCT), kas iepriekš tika izmantots, lai ievadītu masīva formulas bez Ctrl+maiņa+ienākt var arī aizmirst – tagad tas ir pietiekami vienkārši SUM и ienākt.
- Viedās tabulas un pazīstamās funkcijas (SUM, IF, VLOOKUP, SUMIFS utt.) tagad arī pilnībā vai daļēji atbalsta dinamiskos masīvus.
- Pastāv atgriezeniskā savietojamība: ja vecajā Excel versijā atverat darbgrāmatu ar dinamiskiem masīviem, tie pārvērtīsies par masīvu formulām (cirtainās iekavās) un turpinās darboties “vecajā stilā”.
Atrada kādu numuru mīnusi:
- No dinamiskā masīva nevar izdzēst atsevišķas rindas, kolonnas vai šūnas, ti, tas darbojas kā viena vienība.
- Jūs nevarat kārtot dinamisko masīvu parastajā veidā Dati – kārtošana (Dati — Kārtot). Tagad tam ir īpaša funkcija. GRADE (KĀRTOT).
- Dinamisko diapazonu nevar pārvērst par viedo tabulu (taču jūs varat izveidot dinamisko diapazonu, pamatojoties uz viedo tabulu).
Protams, tās vēl nav beigas, un esmu pārliecināts, ka Microsoft turpinās uzlabot šo mehānismu arī turpmāk.
Kur es varu lejupielādēt?
Un visbeidzot galvenais jautājums 🙂
Microsoft pirmo reizi paziņoja un parādīja dinamisko masīvu priekšskatījumu programmā Excel 2018. gada septembrī konferencē Aizdedzināt. Dažu nākamo mēnešu laikā tika veikta rūpīga jaunu funkciju testēšana un palaišana kaķi paša Microsoft darbinieki un pēc tam brīvprātīgie testētāji no Office Insiders loka. Šogad atjauninājums, kas pievieno dinamiskos masīvus, pakāpeniski tika ieviests parastajiem Office 365 abonentiem. Piemēram, es to saņēmu tikai augustā ar savu Office 365 Pro Plus (Mēneša mērķa) abonementu.
Ja jūsu Excel vēl nav dinamisku masīvu, bet jūs patiešām vēlaties ar tiem strādāt, ir šādas iespējas:
- Ja jums ir Office 365 abonements, varat vienkārši pagaidīt, līdz jūs sasniegs šis atjauninājums. Tas, cik ātri tas notiek, ir atkarīgs no tā, cik bieži jūsu Office tiek piegādāti atjauninājumi (reizi gadā, reizi sešos mēnešos, reizi mēnesī). Ja jums ir korporatīvais dators, varat lūgt administratoram iestatīt atjauninājumus, lai tie tiktu lejupielādēti biežāk.
- Jūs varat pievienoties šo Office Insiders testa brīvprātīgo rindām – tad jūs būsiet pirmais, kas saņems visas jaunās iespējas un funkcijas (taču, protams, programmā Excel ir palielināta kļūda).
- Ja jums nav abonementa, bet gan atsevišķa Excel versija, jums būs jāgaida vismaz līdz nākamās Office un Excel versijas izlaišanai 2022. gadā. Šādu versiju lietotāji saņem tikai drošības atjauninājumus un kļūdu labojumus, un visi jaunie “labumi” tagad nonāk tikai Office 365 abonentiem. Skumji, bet patiesi 🙂
Jebkurā gadījumā, kad jūsu Excel programmā parādīsies dinamiskie masīvi - pēc šī raksta jūs būsiet tam gatavs 🙂
- Kas ir masīvu formulas un kā tās izmantot programmā Excel
- Loga (diapazona) summēšana, izmantojot funkciju OFFSET
- 3 veidi, kā transponēt tabulu programmā Excel