Blog

Dinamikus tartományok Excelben: mikor használjon OFFSET függvényt vagy táblázatot

Tartsa naprakészen az összesítést és a legördülő listát új soroknál. Ismerje meg az OFFSET és a COUNTA működését, az üres azonosítók hatását és az Excel-táblázat használatát.

Új sor beszúrásakor a kiemelt hivatkozási tartomány lefelé bővül.

Új terméket tesz a listába, de az összeg és a legördülő lista nem frissül. Ha a hivatkozás továbbra is A2:A10, a tizenegyedik sor kívül marad. Dinamikus tartománnyal az Excel maga határozza meg a lista végét, így nem kell átírni a képleteket.

Magasság és szélesség meghatározása az OFFSET függvénnyel

Ha az adatok folytonosak, számolja meg a fejléceket és a termékazonosítókat az OFFSET magasságának és szélességének megadásához. Készítse el a mintát a Sheet1 lapon, az összegző képletet pedig egy másikon adja meg. A japán fejléceket pontosan másolja, hogy működjenek a későbbi strukturált hivatkozások.

Cella A oszlop B oszlop C oszlop
1. sor 商品ID (termékazonosító) 商品名 (terméknév) 数量 (mennyiség)
2. sor A01 Toll 4
3. sor A02 Jegyzetfüzet 6
=SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),COUNTA(Sheet1!$A$1:$Z$1)))

Induljon az A1 cellából eltolás nélkül. Az A oszlop kitöltött celláinak száma adja a magasságot, az első sor fejléceinek száma a szélességet. A háromszor hármas tartomány A1:C3, az összeg 10. Adja a negyedik sorhoz az A03 azonosítót, a Vonalzó nevet és a 2 mennyiséget. A tartomány A1:C4 lesz, az összeg 12. Az azonosítók szövegesek, és a számolt tartományokban nincs más adat. A Microsoft OFFSET-referenciája

Törölje csak az azonosítót az A3 cellából. Az A oszlopban három cella számít: a fejléc az A1, valamint az azonosítók az A2 és az A4 cellában. Ezért az OFFSET az első három sort adja vissza, A1:C3. A harmadik sor 6-os mennyisége benne marad, a negyedik sor 2-es mennyisége kiesik. Az összeg újra 10.

Az A3 üres. Az OFFSET tartománya A1:C3, az összeg 10. A harmadik sor 6-os értéke benne van, a negyedik sor 2-es értéke nincs benne.
A zöld keret az OFFSET által visszaadott A1:C3 tartományt jelzi a Windows Excelben.

Egy középen üres fejléc miatt az utolsó oszlopok kimaradhatnak. A COUNTA az üres karakterláncot ("") visszaadó képletek celláit is megszámolja. Meglévő munkafüzetben tartsa folytonosan kitöltve a fejléceket és az azonosítókat, és ellenőrizze a számlálás határait és az üres cellákat. Az üres mennyiség nem módosítja a magasságot, ha van azonosító. A Microsoft COUNTA-referenciája

Ha a fejlécek a második sorban kezdődnek

Ha az első sor a jelentés címe, a lista kezdődjön az A2 cellánál. A magasságot és szélességet is a második sortól számolja, hogy a cím kimaradjon. A mintát egy sorral lejjebb tolva a fejléc és két termék tartománya A2:C4, az összeg továbbra is 10.

=SUM(OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000),COUNTA(Sheet1!$A$2:$Z$2)))

Új sorokkal automatikusan bővülő táblázat használata

Új listánál jelölje ki az adatokat a fejlécekkel együtt, majd válassza a Beszúrás → Táblázat parancsot. Nevezze a táblázatot Items névre. A következő képlet a táblázaton kívülről összegzi a mennyiségeket. Új sorok hozzáadásakor az összegzett tartomány is bővül. A Microsoft útmutatója a strukturált hivatkozásokhoz

=SUM(Items[数量])

Csak a termékazonosítóval rendelkező sorok összegzése

Ha az A1:C4 tartományból Items táblázat lesz, a fenti képlet az A3 üressége ellenére 4 + 6 + 2 értéket összegzi, így 12-t ad. Csak az azonosítós sorokhoz használja a SUMIF függvényt, azzal a feltétellel, hogy az azonosító nem üres. A példában a második és negyedik sor 4 + 2 értéke ad 6-ot. A Microsoft SUMIF-referenciája

=SUMIF(Items[商品ID],"<>",Items[数量])

A legördülő lista összekapcsolása a táblázattal

Egy másik lapon a terméknevek kiválasztásához nyissa meg az asztali Excelben a Képletek → Névkezelő panelt. Hozza létre a munkafüzet hatókörű ItemChoices nevet, a Hivatkozás mezőbe pedig írja be: =Items[商品名]. A beviteli cella adatérvényesítésében válassza a Lista lehetőséget, forrásnak adja meg a =ItemChoices értéket. A fejléc kimarad, a táblázat végén hozzáadott sorok pedig új lehetőségeket adnak. Az üres választási lehetőségek elkerüléséhez töltse ki az új sorok nevét. A Microsoft útmutatója legördülő listákhoz