Hivatkozott Excel verzio: Excel 2010 Excel 2013 Excel 2016 Office365
Az utólagos sorszámozás meglehetősen gyakori feladat. A táblázatunkat az elején vagy a a végén egy új oszlopban egy növekvő, legtöbbször 1-től kezdődő sorszámmal szeretnénk ellátni.
Ezt elég sokféleképpen meg lehet oldani. A megoldások két kategóriába sorolhatók: Értékekkel, illetve képlettel történő kitöltések. Lássuk a szokásos megoldásokat:
- Első két sorszám beírása, majd kijelölés és lehúzás vagy dupla klikk
- Első sorszám beírás, majd Ctrl-lal lehúzás
- Első két sorszám beírása, majd jobb gombbal lehúzás és kitöltés sorozattal
- Kitöltés sorozattal a menüből
- Első sorszám beírása, majd a következőbe képlet: legyen egyenlő az előző + 1 és leküldés az aljáig
- Biztosan van még…
Mi a baj ezekkel?
Addig semmi, amíg nem kezdünk el törölni vagy hozzáadni újabb sorokat. Főleg akkor van gond, amikor a belsejében történik mindez és nem a végén. Ha pl. törlünk egy sort, megszakad a folyamatos sorszámozás, kiesik egy elem. Az értékekkel kitöltött sorszámozást a kiesett sorszámtól újra kell képezni. Milyen jó lenne, ha létezne valamilyen önjavító képlet, ami automatikusan helyreállítja a sorszámozás. Létezik ilyen?
Önjavító sorszámozás képletekkel
Mellette lévő oszlop kitöltött cellái alapján
A sorszám oszlop első cellájába írunk egy képletet, ami megszámolja a mellette lévő, sorszámozandó oszlop kitöltött celláinak a számát az elejétől az aktuális sorig, majd leküldjük az aljáig. Ha törlünk egy sort, magától kijavítja a sorszámozást. Ha hozzáadunk egy sort a belsejéhez, akkor csak a hozzáadás helyén kell lehúzni a képletet az új sorra, a többi már jó lesz. A használt technikát az „Elejétől az aktuális sorig” neveztem el, és egy korábbi cikkben bővebben olvashattok róla.
Probléma: Ha az egyik tétel üres, akkor ott elromlik a számozás folytonossága.
Felette lévő sorszámok alapján
Itt továbbfejlesztjük az előtte lévő plusz egy képletet, hogy a sor törlés és hozzáadás a lehető legkisebb plusz munkát okozza.
Ez is egy korábbi cikken alapul, ahol leírtam, hogy hogyan lehet hivatkozni mindig az előző sorra, akkor is, ha beszúrunk egy újat. A sima képletek a sor beszúrás miatt megváltoznak, és ami eddig eggyel felette lévőre hivatkozott, az a kettővel felette lévőre fog. Ez legtöbbször jó nekünk, de nem mindig, pl. most sem.
Adjuk meg a sorszám oszlop első tételét, alá pedig írjuk be ezt a képletet:
Ez a D oszlopban a megelőző cella értékéhez hozzáad egyet úgy, hogy nem érzékeny a beszúrt sorokra. Emiatt ha sort szúrunk be, csak a beszúrás helyén kell lehúzni a képletet. Ha törlünk egy sort, azt automatikusan javítja.
Probléma: Ha másik oszlopba kerül a képlet, akkor az oszlop betűjét javítani kell!
Megoldás SOR() függvénnyel
A SOR() függvény visszaadja a paraméterben megadott cella sorszámát. Paraméter nélkül pedig az aktuális sort adja, ahová a függvényt írtuk
Nézzük ezt a képletet
Az aktuális sorból kivonja a lefixált fejléc cella sorát. Kezdetben ez 1 lesz, és lefelé húzva növekszik.
- Sor törlésnél önjavít,
- Beszúrásnál csak a beszúrás helyén kell lehúzni,
- Az egész tartomány mozgatható, elé is beszúrható sor vagy oszlop
Kedves Imi!
Köszönjük, szuper! Mint mindig, most is tanultam pár dolgot Tőled!:)
Kérdésem, mi a helyzet a SOR() függvénnyel? Én általában ezt szoktam használni, és rendelkezik azokkal az előnyökkel (sor törlés, beszúrás), amit a videóban mondtál, ráadásul nincs volatile függvény. És könnyen testre szabható, pl. azt akarom, hogy az A3 cellától lefelé, mondjuik 10-től induljon a sorszámozás, akkor csak 7-t hozzá kell adni a képlethez.
Köszönettel:
Kálmán
Szia!
Igen, a SOR() függvény is tökéletes, de csak akkor, ha paraméter nélkül használod. A kezdő sorszám definiálása az izgalmas, utána gyönyörűen működik.
Ha a 3. sorban van az első sorszámozandó adat, akkor =SOR()-2-vel indul, és le lehet húzni. Törlés, beszúrás, üres tétel mind szuperál!
Lehet, hogy csinálok a tutorial szöveges részébe egy UPDATE részt.
Köszönöm, hogy hozzáadtad…:-)
Kedves Imi!
Én erre a helyzetre a következő képletet használom (A2. cella tartalma, és lehúzva a táblázat aljáig):
=HA(B2=””;””;MAX($A$1:A1)+1)
Ez a cella fölötti oszlop legnagyobb értékét keresi, és ehez ad hozzá 1-et. Ha meg üres cellát talál a B oszlopban, akkor azt a sort üresen hagyja. Sor törléskor/beszúráskor ezt is újra kell számoltatni.
Üdv: Feri
Szia!
Ez ugye akkor lehet jó, ha sorszám nélkül akarod hagyni az üres tételeket.
A MAX fv. használata tetszik.
Imre