tartalom
Vannak Excelben olyan táblázatok, amelyekben adatok átméretezhetők, azaz a sorok (oszlopok) száma növekedhet vagy csökkenhet a munka során? Ha az asztal mérete „lebeg”, akkor folyamatosan figyelnie kell ezt a pillanatot és javítania kell:
- táblázatunkra hivatkozó jelentésképletek hivatkozásait
- pivot táblák kezdeti tartományai, amelyek a táblázatunk szerint épülnek fel
- táblázatunk alapján felépített diagramok kezdeti tartományai
- tartományok a táblázatunkat adatforrásként használó legördülő menükhöz
Mindez összesen nem hagy majd unatkozni 😉
Sokkal kényelmesebb és helyesebb lesz egy dinamikus „gumi” tartomány létrehozása, amely mérete automatikusan igazodik az adatsorok és oszlopok tényleges számához. Ennek megvalósítása többféleképpen lehetséges.
1. módszer. Intelligens asztal
Jelölje ki a cellák tartományát, és válasszon a lapról Kezdőlap – Formázás táblázatként (Kezdőlap – Formázás táblázatként):
Ha nincs szüksége a táblázathoz mellékhatásként hozzáadott csíkos kialakításra, akkor a megjelenő lapon kikapcsolhatja Konstruktor (tervező). Minden így elkészített tábla kap egy nevet, amelyet a lap ugyanazon a helyén kényelmesebbre lehet cserélni Konstruktor (tervező) a területen Tábla neve (A táblázat neve).
Most már használhatunk dinamikus hivatkozásokat az „okos asztalunkhoz”:
- Táblázat 1 – hivatkozás a teljes táblázatra, kivéve a fejléc sort (A2:D5)
- 1. táblázat[#Összes] – link a teljes táblázathoz (A1:D5)
- 1. táblázat [Péter] – hivatkozás egy tartományoszlopra az első cellafejléc nélkül (C2:C5)
- 1. táblázat[#Headers] – link a „fejléchez” az oszlopok nevével (A1:D1)
Az ilyen hivatkozások kiválóan működnek képletekben, például:
= SUM (1. táblázat [Moszkva]) – a „Moszkva” oszlop összegének kiszámítása
or
=VPR(F5;Táblázat 1;3;0) – keresse meg a táblázatban a hónapot az F5 cellából, és adja ki a szentpétervári összeget (mi az a VLOOKUP?)
Az ilyen hivatkozások sikeresen használhatók pivot táblák létrehozásakor a lapon történő kiválasztással Beszúrás – Kimutatás (Insert – Pivot Table) és adatforrásként adja meg az intelligens tábla nevét:
Ha kiválaszt egy ilyen táblázat egy töredékét (például az első két oszlopot), és bármilyen típusú diagramot hoz létre, akkor új sorok hozzáadásakor automatikusan hozzáadódnak a diagramhoz.
Legördülő listák létrehozásakor az intelligens táblaelemekre mutató közvetlen hivatkozások nem használhatók, de ezt a korlátozást egy taktikai trükk segítségével könnyedén megkerülheti – használja a funkciót KÖZVETETT (KÖZVETETT), amely a szöveget linkgé alakítja:
Azok. egy intelligens táblázatra mutató hivatkozás szövegkarakterlánc formájában (idézőjelben!) teljes értékű hivatkozássá válik, és a legördülő lista normál esetben érzékeli.
2. módszer: Dinamikus nevű tartomány
Ha az adatok intelligens táblává alakítása valamilyen okból nem kívánatos, akkor használhat egy kicsit bonyolultabb, de sokkal finomabb és sokoldalúbb módszert – hozzon létre egy dinamikus névtartományt az Excelben, amely a táblázatunkra hivatkozik. Ezután, mint egy intelligens tábla esetében, szabadon használhatja a létrehozott tartomány nevét bármilyen képletben, jelentésben, diagramban stb. Kezdjük egy egyszerű példával:
Feladat: hozzon létre egy dinamikus nevű tartományt, amely városok listájára hivatkozik, és új városok hozzáadásakor vagy törlésekor automatikusan megnyújtja és csökkenti a méretét.
Két beépített Excel-függvényre lesz szükségünk bármely verzióban − POICPOZ (MÉRKŐZÉS) a tartomány utolsó cellájának meghatározásához, és INDEX (INDEX) dinamikus kapcsolat létrehozásához.
Az utolsó cella megkeresése a MATCH segítségével
EGYEZÉS(keresési_érték, tartomány, egyezési_típus) – egy függvény, amely egy adott értéket keres egy tartományban (sorban vagy oszlopban), és visszaadja annak a cellának a sorszámát, ahol megtalálta. Például a MATCH(“March”;A1:A5;0) képlet eredményeként a 4-es számot adja vissza, mivel a „March” szó az A1:A5 oszlop negyedik cellájában található. Az utolsó függvény argumentum Match_Type = 0 azt jelenti, hogy pontos egyezést keresünk. Ha ez az argumentum nincs megadva, akkor a függvény a legközelebbi legkisebb érték keresési módba kapcsol – pontosan ezzel lehet sikeresen megkeresni a tömbünk utolsó foglalt celláját.
A trükk lényege egyszerű. A MATCH felülről lefelé keresi a cellákat, és elméletileg le kell állítania, amikor megtalálja az adott értékhez legközelebbi legkisebb értéket. Ha olyan értéket ad meg, amely nyilvánvalóan nagyobb, mint a táblázatban elérhető bármely érték, akkor a MATCH eléri a táblázat legvégét, nem talál semmit, és megadja az utoljára kitöltött cella sorszámát. És szükségünk van rá!
Ha csak számok vannak a tömbünkben, akkor a kívánt értékként megadhatunk egy számot, amely nyilvánvalóan nagyobb, mint a táblázatban szereplők bármelyike:
Garanciaként a 9E + 307 számot használhatja (9-szer 10 307 hatványáig, azaz 9 307 nullával) – ez a maximális szám, amellyel az Excel elvileg dolgozni tud.
Ha az oszlopunkban szöveges értékek találhatók, akkor a lehető legnagyobb szám megfelelőjeként beillesztheti a REPEAT(“i”, 255) konstrukciót – egy 255 betűből álló szövegsort „i” – az utolsó betű Az ábécé. Mivel az Excel valójában összehasonlítja a karakterkódokat kereséskor, a táblázatunkban szereplő bármely szöveg technikailag „kisebb” lesz, mint egy ilyen hosszú „yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy” sor:
Hivatkozás létrehozása az INDEX segítségével
Most, hogy ismerjük a táblázat utolsó nem üres elemének pozícióját, hátra van, hogy a teljes tartományunkhoz kapcsolódjunk. Ehhez a következő függvényt használjuk:
INDEX(tartomány; sor_száma; oszlop_száma)
Sor- és oszlopszámonként adja meg a cella tartalmát a tartományból, azaz például az =INDEX(A1:D5;3;4) függvény a táblázatunkban városokkal és hónapokkal az előző módszerből 1240-et ad – a tartalom a 3. sorból és a 4. oszlopból, azaz a D3 cellákból. Ha csak egy oszlop van, akkor annak száma elhagyható, azaz az INDEX(A2:A6;3) képlet a „Samara”-t adja az utolsó képernyőképen.
És van egy nem teljesen nyilvánvaló árnyalat: ha az INDEX-et szokás szerint nem csak az = jel utáni cellába írjuk be, hanem a kettőspont utáni tartományra való hivatkozás utolsó részeként használjuk, akkor már nem ad ki. a cella tartalma, de a címe! Így egy olyan képlet, mint a $A$2:INDEX($A$2:$A$100;3) hivatkozást ad az A2:A4 tartományra a kimeneten.
És itt jön be a MATCH függvény, amelyet beszúrunk az INDEX-be, hogy dinamikusan meghatározzuk a lista végét:
=$A$2:INDEX($A$2:$100; MATCH(REP("I";255);A2:A100))
Hozzon létre egy elnevezett tartományt
Marad az egészet egyetlen egésszé csomagolni. Nyisson meg egy lapot képlet (Képletek) És kattintson a gombra Névkezelő (Névkezelő). A megnyíló ablakban kattintson a gombra Teremt (új), írja be a tartomány nevét és képletét a mezőbe Választék (Referencia):
Már csak rá kell kattintani OK és a kész tartomány bármilyen képletben, legördülő listában vagy diagramban használható.
- A VLOOKUP függvény használata táblák és keresési értékek összekapcsolásához
- Automatikusan feltöltődő legördülő lista létrehozása
- Hogyan hozzunk létre pivot táblát nagy mennyiségű adat elemzéséhez