tartalom
Mik azok a dinamikus tömbök
2018 szeptemberében a Microsoft kiadott egy frissítést, amely egy teljesen új eszközzel egészíti ki a Microsoft Excelt: dinamikus tömböket és 7 új funkciót a velük való munkavégzéshez. Ezek a dolgok túlzás nélkül gyökeresen megváltoztatják a képletekkel és függvényekkel való munkavégzés szokásos technikáját, és szó szerint minden felhasználót érintenek.
Vegyünk egy egyszerű példát a lényeg magyarázatára.
Tegyük fel, hogy van egy egyszerű táblázatunk a város hónapjaira vonatkozó adatokkal. Mi történik, ha kijelölünk egy üres cellát a lap jobb oldalán, és beírunk egy képletet, amely nem egy cellához, hanem közvetlenül egy tartományhoz kapcsolódik?
Az Excel összes korábbi verziójában a gombra kattintás után belép csak egy első B2 cella tartalmát kapnánk meg. Hogyan másképp?
Nos, vagy lehetséges lenne ezt a tartományt valamilyen összesítő függvénybe csomagolni, például =SUM(B2:C4), és kapni egy végösszeget.
Ha összetettebb műveletekre van szükségünk, mint egy primitív összeg, például egyedi értékek kinyerésére vagy a Top 3-ra, akkor a képletünket tömbképletként kell megadnunk egy billentyűparancs segítségével. Ctrl+műszak+belép.
Most minden más.
Most egy ilyen képlet beírása után egyszerűen rákattinthatunk belép – és ennek eredményeként azonnal megkapja az összes uXNUMXbuXNUMXb értéket, amelyre hivatkoztunk:
Ez nem varázslat, hanem a Microsoft Excel új dinamikus tömbjei. Üdvözöljük az új világban 🙂
A dinamikus tömbökkel végzett munka jellemzői
Technikailag a teljes dinamikus tömbünket az első G4-es cellában tároljuk, és kitölti a szükséges számú cellát jobbra és lefelé az adataival. Ha kiválaszt egy másik cellát a tömbben, akkor a képletsávban lévő hivatkozás inaktív lesz, jelezve, hogy az egyik „gyermek” cellában vagyunk:
Egy vagy több „gyermek” cella törlésének kísérlete nem vezet semmire – az Excel azonnal újraszámolja és kitölti azokat.
Ugyanakkor nyugodtan hivatkozhatunk ezekre a „gyermek” sejtekre más képletekben:
Ha egy tömb első celláját másolja (például G4-ről F8-ra), akkor a teljes tömb (hivatkozásai) ugyanabba az irányba mozog, mint a normál képletekben:
Ha mozgatni kell a tömböt, akkor elég lesz mozgatni (egérrel vagy ezek kombinációjával Ctrl+X, Ctrl+V), ismét csak az első főcella G4 – utána átkerül egy új helyre, és a teljes tömbünk ismét kibővül.
Ha a lapon máshol kell hivatkozni a létrehozott dinamikus tömbre, akkor használhatja a # speciális karaktert ("font") a vezető cella címe után:
Például most egyszerűen készíthet egy legördülő listát egy cellában, amely a létrehozott dinamikus tömbre hivatkozik:
Dinamikus tömb hibák
De mi történik, ha nincs elég hely a tömb kibővítéséhez, vagy ha az útjában már vannak más adatok által elfoglalt cellák? Ismerkedjen meg egy alapvetően új típusú hibákkal az Excelben – #ÁTRUHÁZÁS! (#BUKÁS!):
Mint mindig, ha a sárga gyémánttal és felkiáltójellel ellátott ikonra kattintunk, részletesebb magyarázatot kapunk a probléma forrásáról, és gyorsan megtaláljuk a zavaró cellákat:
Hasonló hibák fordulnak elő, ha a tömb lekerül a lapról, vagy összevont cellát talál. Ha eltávolítja az akadályt, akkor minden azonnal kijavításra kerül.
Dinamikus tömbök és intelligens táblák
Ha a dinamikus tömb egy billentyűkóddal létrehozott „okos” táblára mutat Ctrl+T vagy Kezdőlap – Táblázat formázása (Főoldal — Táblázat formázása), akkor a fő minőségét – az automatikus méretezést – is örökli.
Ha új adatokat ad hozzá alul vagy jobbra, az intelligens táblázat és a dinamikatartomány is automatikusan megnyúlik:
Van azonban egy korlátozás: nem használhatunk dinamikus tartomány hivatkozást az intelligens táblákon belüli fórumokban:
Dinamikus tömbök és egyéb Excel funkciók
Oké, mondod. Mindez érdekes és vicces. Nincs szükség a képlet manuális kiterjesztésére, mint korábban, az eredeti tartomány első cellájára hivatkozva lefelé és jobbra, meg minden. És ennyi?
Nem egészen.
A dinamikus tömbök nem csak egy újabb eszköz az Excelben. Most a Microsoft Excel szívébe (vagy agyába) ágyazták be – a számítási motorjába. Ez azt jelenti, hogy más, általunk ismert Excel-képletek és -függvények is támogatják a dinamikus tömbökkel való munkát. Nézzünk meg néhány példát, hogy képet kapjunk a bekövetkezett változások mélységéről.
Át kell ültetni
Egy tartomány transzponálásához (sorok és oszlopok felcseréléséhez) a Microsoft Excel mindig is rendelkezett beépített funkcióval TRANSP (TRASZPOZI). Használatához azonban először helyesen kell kiválasztani az eredmények tartományát (ha például 5×3 tartomány volt a bemenet, akkor a 3×5-öt kell kiválasztania), majd adja meg a funkciót, és nyomja meg a kombináció Ctrl+műszak+belép, mert csak tömbképlet módban működhetett.
Most már csak kiválaszthat egy cellát, beírhatja abba ugyanazt a képletet, és kattintson a normálra belép – a dinamikus tömb mindent megtesz magától:
Szorzótábla
Ezt a példát szoktam mondani, amikor arra kértek, hogy képzeljem el a tömbképletek előnyeit az Excelben. Most a teljes Pitagorasz-tábla kiszámításához elegendő az első B2 cellába állni, beírni egy képletet, amely megszoroz két tömböt (függőleges és vízszintes számkészlet 1...10), és egyszerűen kattintson a belép:
Ragasztás és tok átalakítás
A tömbök nem csak szorozhatók, hanem össze is ragaszthatók a szabványos & (és) operátorral. Tegyük fel, hogy két oszlopból ki kell vonnunk a kereszt- és vezetéknevet, és ki kell javítanunk az ugrásszerű kis- és nagybetűket az eredeti adatokban. Ezt egy rövid képlettel tesszük, amely a teljes tömböt alkotja, majd alkalmazzuk rá a függvényt PROPNACH (MEGFELELŐ)a nyilvántartás rendbetételéhez:
Összegzés Top 3
Tegyük fel, hogy van egy csomó számunk, amelyekből csökkenő sorrendbe rendezve a legjobb három eredményt szeretnénk származtatni. Most ez egyetlen képlet alapján történik, és ismét minden nélkül Ctrl+műszak+belép mint korábban:
Ha azt szeretné, hogy az eredmények ne oszlopban, hanem sorban legyenek elhelyezve, akkor ebben a képletben elegendő a kettőspontokat (sorelválasztó) pontosvesszőre (egy soron belüli elemelválasztó) helyettesíteni. Az Excel angol verziójában ezek az elválasztók pontosvesszők, illetve vesszők.
A VLOOKUP egyszerre több oszlopot bont ki
Funkciók VPR (VLOOKUP) most már nem egy, hanem egyszerre több oszlopból is lehívhatja az értékeket – csak adja meg a számukat (tetszőleges sorrendben) tömbként a függvény harmadik argumentumában:
Az OFFSET függvény dinamikus tömböt ad vissza
Az adatelemzés egyik legérdekesebb és leghasznosabb (a VLOOKUP után) függvénye a függvény ÁRTALMATLANÍTÁS (OFFSET), amelynek egy időben egy egész fejezetet szenteltem a könyvemben és egy cikket itt. A függvény megértésének és elsajátításának nehézsége mindig is az volt, hogy ennek eredményeként egy adattömböt (tartományt) adott vissza, de nem láthattuk, mert az Excel még mindig nem tudta, hogyan kell tömbökkel dolgozni.
Most ez a probléma a múlté. Nézze meg, hogyan lehet most egyetlen képlet és az OFFSET által visszaadott dinamikus tömb használatával kinyerni egy adott termék összes sorát bármely rendezett táblázatból:
Nézzük az érveit:
- A1 – kezdő cella (referenciapont)
- ПОИСКПОЗ(F2;A2:A30;0) – a kezdőcellától lefelé – az első talált káposztáig való eltolódás kiszámítása.
- 0 – az „ablak” eltolása jobbra a kezdőcellához képest
- СЧЁТЕСЛИ(A2:A30;F2) – a visszaadott „ablak” magasságának kiszámítása – azon sorok száma, ahol káposzta van.
- 4 — az „ablak” mérete vízszintesen, azaz 4 oszlop kimenete
Új funkciók a dinamikus tömbökhöz
Amellett, hogy a régi függvényekben támogatja a dinamikus tömbmechanizmust, több teljesen új funkció is bekerült a Microsoft Excelbe, amelyeket kifejezetten a dinamikus tömbökkel való munkavégzéshez lettek kihegyezve. Ezek különösen a következők:
- GRADE (FAJTA) – rendezi a bemeneti tartományt, és dinamikus tömböt állít elő a kimeneten
- SORTPO (SORREND) – egy tartományt egy másik érték alapján rendezhet
- FILTER (SZŰRŐ) – lekéri a megadott feltételeknek megfelelő sorokat a forrástartományból
- EGYEDI (EGYEDI) – egyedi értékeket von ki egy tartományból, vagy eltávolítja a duplikációkat
- SZLMASSZÍV (RANDARRAY) – adott méretű véletlen számokból álló tömböt generál
- MÉHLEPÉNY (SORREND) — adott lépésű számsorozatból tömböt alkot
Bővebben róluk – kicsit később. Megérnek egy külön cikket (és nem is egyet) az átgondolt tanulmányozáshoz 🙂
Következtetések
Ha elolvastad mindazt, amit fentebb írtunk, akkor szerintem már rájöttél a bekövetkezett változások mértékére. Az Excelben sok minden most könnyebben, könnyebben és logikusabban elvégezhető. Bevallom, kissé megdöbbentett, hogy most mennyi cikket kell majd javítani itt, ezen az oldalon és a könyveimben, de kész vagyok erre könnyű szívvel.
Összegezve az eredményeket, profik dinamikus tömbökhöz a következőket írhatja:
- Elfelejtheti a kombinációt Ctrl+műszak+belép. Az Excel már nem lát különbséget a „reguláris képletek” és a „tömbképletek” között, és ugyanúgy kezeli őket.
- A funkcióról SUMPRODUCT (ÖSSZES TERMÉK), amelyet korábban tömbképletek megadására használtak anélkül Ctrl+műszak+belép el is felejtheted – most már elég könnyű ÖSSZEG и belép.
- Az intelligens táblák és az ismert függvények (SUM, IF, VLOOKUP, SUMIFS stb.) most már részben vagy teljesen támogatják a dinamikus tömböket.
- Van visszamenőleges kompatibilitás: ha megnyit egy munkafüzetet dinamikus tömbökkel az Excel egy régi verziójában, akkor azok tömbképletekké alakulnak (kapcsos zárójelben), és továbbra is a „régi stílusban” működnek.
Találtam egy számot mínuszok:
- Egy dinamikus tömbből nem törölhet egyedi sorokat, oszlopokat vagy cellákat, azaz egyetlen entitásként él.
- A dinamikus tömbök nem rendezhetők a szokásos módon Adatok – Rendezés (Adatok – Rendezés). Erre most van egy speciális funkció. GRADE (FAJTA).
- A dinamikatartomány nem alakítható intelligens táblává (de létrehozhat dinamikatartományt egy intelligens tábla alapján).
Természetesen ez még nem a vége, és biztos vagyok benne, hogy a Microsoft a jövőben is tovább fogja fejleszteni ezt a mechanizmust.
Hol tudom letölteni?
És végül a fő kérdés 🙂
A Microsoft először 2018 szeptemberében egy konferencián jelentette be és mutatta be a dinamikus tömbök előnézetét Excelben Meggyullad. A következő néhány hónapban az új funkciók alapos tesztelése és befuttatása zajlott, először macskák maga a Microsoft alkalmazottai, majd önkéntes tesztelők az Office Insiderek köréből. Ebben az évben a dinamikus tömböket hozzáadó frissítés fokozatosan bevezetésre került az Office 365 rendszeres előfizetői számára. Én például csak augusztusban kaptam meg az Office 365 Pro Plus (Monthly Targeted) előfizetésemmel.
Ha az Excel még nem rendelkezik dinamikus tömbökkel, de nagyon szeretne velük dolgozni, akkor a következő lehetőségek állnak rendelkezésre:
- Ha rendelkezik Office 365-előfizetéssel, egyszerűen megvárhatja, amíg ez a frissítés megérkezik. Hogy ez milyen gyorsan történik, az attól függ, hogy milyen gyakran érkezik meg a frissítés az Office-ba (évente egyszer, félévente egyszer, havonta egyszer). Ha vállalati számítógépe van, megkérheti a rendszergazdát, hogy állítsa be a frissítések gyakrabban történő letöltését.
- Ön is csatlakozhat az Office Insider teszt önkénteseihez – akkor Ön lesz az első, aki megkapja az összes új szolgáltatást és funkciót (de természetesen az Excelben megvan az esély a hibásodásra).
- Ha nincs előfizetése, hanem az Excel dobozos önálló verziója, akkor legalább az Office és az Excel következő verziójának 2022-es megjelenéséig várnia kell. Az ilyen verziók felhasználói csak biztonsági frissítéseket és hibajavításokat kapnak, és minden új „jóság” mostantól csak az Office 365 előfizetőihez jut. Szomorú, de igaz 🙂
Mindenesetre, ha dinamikus tömbök jelennek meg az Excelben – e cikk után készen állsz rá 🙂
- Mik azok a tömbképletek és hogyan kell használni őket az Excelben
- Ablak (tartomány) összegzése az OFFSET funkció segítségével
- 3 módszer a táblázat átültetésére Excelben