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 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.
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