Telefonszámunk: 1-472-0679

Önjavító sorszámozás képlettel

2020-12-07 - horvimi - Megtekintések száma: 1 625 - Kategória: Általános tippek, Képletek
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.

Sorszámozás feladat

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.

Sorszámozás mellette

Probléma: Ha az egyik tétel üres, akkor ott elromlik a számozás folytonossága.

Első képlet üres tétel

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:

Sorszámozás növelése indirekt módon

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!

Frissítés!

Kálmán lenti hozzászólása alapján leírom a SOR függvényes megoldást is. Talán ez a legjobb.

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

Sorszámozás_SOR_fuggvennyel

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

Táblázattá alakításnál teljesen automatikus

Ha táblázattá alakítjuk (Beszúrás -> Táblázat), akkor a sorok beszúrásakor a képlet magától képződik az új sorban, tehát a sorszámozás beszúráskor is öngyógyító lesz

 

Lássuk videón a téma körüljárását

4 hozzászólás
  1. 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…:-)

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

Vélemény, hozzászólás?