Sorok törlése cellaérték (vagy állapot) alapján az Excelben (Egyszerű útmutató)

Ha nagy adathalmazokkal dolgozik, előfordulhat, hogy gyorsan törölnie kell a sorokat a benne található cellaértékek (vagy egy feltétel) alapján.

Tekintsük például az alábbi példákat:

1. Rendelkezik értékesítési képviselői adatokkal, és törölni szeretné egy adott régió vagy termék összes rekordját.
2. Törölni szeretné az összes rekordot, ahol az értékesítési érték kevesebb, mint 100.
3. Törölni kíván minden sort, ahol üres cella található.

Ennek az adatmacskának a megnyúzása az Excelben többféle módon is elvégezhető.

A sorok törlésének módja attól függ, hogy az adatok hogyan vannak felépítve, és milyen cellaérték vagy feltétel alapján kívánja törölni ezeket a sorokat.

Ebben az oktatóanyagban többféleképpen mutatom be, hogyan törölhet sorokat az Excelben cellaérték vagy feltétel alapján.

Szűrje a sorokat érték/állapot alapján, majd törölje

Az adott értéket tartalmazó vagy adott feltételnek megfelelő sorok törlésének egyik leggyorsabb módja azok szűrése. A szűrt adatok birtokában törölheti ezeket a sorokat (míg a többi sor érintetlen marad).

Az Excel szűrő meglehetősen sokoldalú, és számos kritérium alapján szűrhet (például szöveg, számok, dátumok és színek)

Nézzünk két példát, ahol szűrheti és törölheti a sorokat.

Adott szöveget tartalmazó sorok törlése

Tegyük fel, hogy rendelkezik adathalmazzal az alábbiak szerint, és törölni szeretné az összes olyan sort, ahol a régió Közép-Nyugat (a B oszlopban) található.

Bár ebben a kis adatkészletben manuálisan törölheti ezeket a sorokat, gyakran az adathalmazok hatalmasak lesznek, ahol a sorok manuális törlése nem lesz lehetőség.

Ebben az esetben szűrheti az összes rekordot, ahol a régió Közép-Nyugat, majd törölheti ezeket a sorokat (miközben a többi sor érintetlen marad).

Az alábbiakban bemutatjuk a sorok érték szerinti törlésének lépéseit (minden közép-nyugati rekord):

1. Válassza ki az adatkészlet bármely celláját, amelyből törölni szeretné a sorokat
2. Kattintson az Adatok fülre
3. A „Rendezés és szűrés” csoportban kattintson a Szűrő ikonra. Ez szűrőket alkalmaz az adatkészlet összes fejléccellájára
4. Kattintson a Szűrő ikonra a Régió fejléc cellájában (ez egy kis lefelé mutató háromszög ikon a cella jobb felső sarkában)
5. Törölje az összes többi lehetőség kijelölését, kivéve a Közép-Nyugat opciót (ennek gyors módja az Összes kijelölése, majd a Közép-Nyugat lehetőségre kattintás). Ez szűrni fogja az adatkészletet, és csak a közép-nyugati régió rekordjait jeleníti meg.
6. Válassza ki az összes szűrt rekordot
7. Kattintson a jobb gombbal a kijelölt cellák bármelyikére, majd kattintson a "Sor törlése" gombra
8. A megnyíló párbeszédpanelen kattintson az OK gombra. Ezen a ponton nem lát rekordokat az adatkészletben.
9. Kattintson az Adatok fülre, majd a Szűrő ikonra. Ezzel eltávolítja a szűrőt, és látni fogja az összes rekordot a törölt kivételével.

A fenti lépések először szűrik az adatokat egy cellaérték alapján (vagy lehetnek más feltételek, például dátum után/előtt, vagy nagyobb/kisebb, mint egy szám). Ha megvannak a rekordok, egyszerűen törölje ezeket.

Néhány hasznos billentyűparancs a folyamat felgyorsítása érdekében:

1. Control + Shift + L a szűrő felhelyezéséhez vagy eltávolításához
2. Control + - (tartsa lenyomva a vezérlőgombot, és nyomja meg a mínusz gombot) a kijelölt cellák/sorok törléséhez

A fenti példában csak négy különböző régióm volt, és manuálisan választhattam ki és szüntettem meg a szűrést a szűrő listából (a fenti 5. lépésben).

Abban az esetben, ha sok kategóriája/régiója van, akkor írja be a nevet a mező fölötti mezőbe (amely rendelkezik ezekkel a régiónevekkel), és az Excel csak azokat a rekordokat jeleníti meg, amelyek megfelelnek a beírt szövegnek (az alábbiak szerint). Ha megvan a szűrni kívánt szöveg, nyomja meg az Enter billentyűt.

Vegye figyelembe, hogy egy sor törlésekor elveszik minden, ami a sorok többi cellájában található. Az egyik módja annak, hogy ezt megkerülje, ha másolatot készít az adatokról egy másik munkalapon, és törli a másolt adatok sorait. Ha elkészült, másolja vissza az eredeti adatok helyére.

Vagy

Használhatja az oktatóanyag későbbi módszereit (a Rendezés módszer vagy az Összes keresése módszer használatával)

Sorok törlése numerikus feltétel alapján

Ahogy a szűrési módszerrel töröltem a Közép-Nyugat szöveget tartalmazó összes sort, használhat számfeltételt (vagy dátumfeltételt).

Tegyük fel például, hogy rendelkezem az alábbi adatkészlettel, és törölni akarom az összes sort, ahol az értékesítési érték kevesebb, mint 200.

Ennek lépései az alábbiakban találhatók:

1. Válassza ki az adatok bármely celláját
2. Kattintson az Adatok fülre
3. A „Rendezés és szűrés” csoportban kattintson a Szűrő ikonra. Ez szűrőket alkalmaz az adatkészlet összes fejléccellájára
4. Kattintson a Szűrő ikonra az Értékesítés fejléc cellában (ez egy kis lefelé mutató háromszög ikon a cella jobb felső sarkában)
5. Vigye a kurzort a Számszűrők opció fölé. Ez megmutatja az összes számmal kapcsolatos szűrési lehetőséget az Excelben.
6. Kattintson a „Kevesebb mint” lehetőségre.
7. A megnyíló „Egyéni automatikus szűrő” párbeszédpanelen írja be a „200” értéket a mezőbe
8. Kattintson az OK gombra. Ez csak azokat a rekordokat fogja szűrni és megjeleníteni, ahol az értékesítési érték kevesebb, mint 200
9. Válassza ki az összes szűrt rekordot
10. Kattintson a jobb gombbal bármelyik cellára, és kattintson a Sor törlése gombra
11. A megnyíló párbeszédpanelen kattintson az OK gombra. Ezen a ponton nem lát rekordokat az adatkészletben.
12. Kattintson az Adatok fülre, majd a Szűrő ikonra. Ez eltávolítja a szűrőt, és látni fogja az összes rekordot, kivéve a törölteket.

Az Excelben számos számszűrőt használhat - például kisebb/nagyobb, egyenlő/nem egyenlő, 10 legjobb között, átlag felett vagy alatt stb.

Megjegyzés: Több szűrőt is használhat. Például törölheti az összes sort, ahol az értékesítési érték nagyobb, mint 200, de kevesebb, mint 500. Ebben az esetben két szűrési feltételt kell használnia. Az Egyéni automatikus szűrő párbeszédpanel lehetővé teszi két szűrési feltétel (ÉS valamint VAGY) megadását.

A számszűrőkhöz hasonlóan a dátum alapján is szűrheti a rekordokat. Például, ha el szeretné távolítani az első negyedév összes rekordját, akkor ezt a fenti lépések végrehajtásával teheti meg. Amikor dátumszűrőkkel dolgozik, az Excel automatikusan megjeleníti a releváns szűrőket (az alábbiak szerint).

Míg a szűrés nagyszerű módja annak, hogy gyorsan törölje a sorokat egy érték vagy feltétel alapján, egy hátránya van - törli az egész sort. Például az alábbi esetben törli a szűrt adatkészlet jobb oldalán található összes adatot.

Mi a teendő, ha csak rekordokat akarok törölni az adatkészletből, de a fennmaradó adatokat érintetlenül akarom tartani.

Ezt szűréssel nem teheti meg, de rendezéssel igen.

Rendezze az adatkészletet, majd törölje a sorokat

Bár a rendezés egy másik módja a sorok érték szerinti törlésének, de a legtöbb esetben jobb, ha a fent leírt szűrési módszert használja.

Ez a rendezési technika csak akkor ajánlott, ha az értékekkel rendelkező cellákat szeretné törölni, nem pedig a teljes sorokat.

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, és törölni szeretné az összes rekordot, ahol a régió Közép-Nyugat.

Az alábbiakban bemutatjuk a lépéseket a rendezés használatával:

1. Válassza ki az adatok bármely celláját
2. Kattintson az Adatok fülre
3. A Rendezés és szűrés csoportban kattintson a Rendezés ikonra.
4. A megnyíló Rendezés párbeszédpanelen válassza a Régió lehetőséget a rendezés oszlopban.
5. A Rendezés opcióban győződjön meg arról, hogy a Cellaértékek ki van választva
6. A Rendelés opcióban válassza az A -tól Z -ig (vagy Z -től A -ig, nem igazán számít).
7. Kattintson az OK gombra. Ez megadja az alább látható rendezett adatkészletet (B oszlop szerint rendezve).
8. Válassza ki az összes rekordot a közép-nyugati régióval (a sorok összes cellája, nem csak a régió oszlop)
9. Miután kiválasztotta, kattintson a jobb gombbal, majd kattintson a Törlés gombra. Ezzel megnyílik a Törlés párbeszédpanel.
10. Győződjön meg arról, hogy a „Cellák felfelé tolása” lehetőség ki van választva.
11. Kattintson az OK gombra.

A fenti lépések törölnek minden olyan rekordot, ahol a régió Közép-Nyugat volt, de nem törli a teljes sort. Tehát, ha van adat az adatkészlet jobb vagy bal oldalán, az sértetlen marad.

A fenti példában az adatokat a cella értéke alapján rendeztem, de ugyanezeket a lépéseket használhatja a számok, dátumok, cella- vagy betűszín stb. Alapján történő rendezéshez is.

Itt található egy részletes útmutató az adatok Excelben történő rendezéséhez

Ha meg szeretné tartani az eredeti adatkészlet sorrendjét, de a rekordokat kritériumok alapján szeretné eltávolítani, akkor módot kell biztosítani arra, hogy az adatokat visszaállítsa az eredetihez. Ehhez adjon hozzá egy oszlopot sorszámokkal az adatok rendezése előtt. Miután befejezte a sorok/rekordok törlését, egyszerűen rendezze a sort a hozzáadott extra oszlop alapján.

Keresse meg és válassza ki a cellákat a cellaérték alapján, majd törölje a sorokat

Az Excel Find and Replace funkcióval rendelkezik, amely nagyszerű lehet, ha egy adott értékű cellákat szeretne megtalálni és kijelölni.

Miután kiválasztotta ezeket a cellákat, könnyen törölheti a sorokat.

Tegyük fel, hogy rendelkezik az alábbi adatkészlettel, és törölni szeretné az összes olyan sort, ahol a régió Közép-Nyugat.

Ennek lépései az alábbiakban találhatók:

1. Válassza ki a teljes adatkészletet
2. Kattintson a Kezdőlap fülre
3. A Szerkesztés csoportban kattintson a „Keresés és kiválasztás” lehetőségre, majd a Keresés gombra (használhatja a Control + F billentyűparancsot is).
4. A Keresés és csere párbeszédpanelen írja be a „Közép-Nyugat” szöveget a „Mit keressen:” mezőbe.
5. Kattintson az Összes keresése gombra. Ez azonnal megmutatja a Mid-West szöveg összes példányát, amelyet az Excel talált.
6. A Control + A billentyűparanccsal jelölje ki az Excel által talált összes cellát. Ezenkívül látni fogja az adatkészlet összes kijelölt celláját.
7. Kattintson a jobb gombbal a kijelölt cellák bármelyikére, majd kattintson a Törlés gombra. Ezzel megnyílik a Törlés párbeszédpanel.
8. Válassza a „Teljes sor” lehetőséget
9. Kattintson az OK gombra.

A fenti lépések törölnek minden cellát, ahol a régió értéke Közép-Nyugat.

Megjegyzés: Mivel a Find and Replace képes helyettesítő karaktereket kezelni, ezeket használhatja az adatok Excelben történő megkereséséhez. Például, ha törölni szeretné az összes olyan sort, ahol a régió Közép-Nyugat vagy Dél-Nyugat, akkor használja a*Nyugat„A Keresés és csere párbeszédpanelen keresendő szövegként. Ez megadja az összes cellát, ahol a szöveg a West szóval végződik.

Töröljön minden sort üres cellával

Ha törölni szeretné az összes olyan sort, ahol üres cellák vannak, ezt könnyen megteheti az Excel beépített funkciójával.

Ez a Ugrás a speciális cellákhoz opció - amely lehetővé teszi az összes üres cella gyors kiválasztását. És miután kiválasztotta az összes üres cellát, ezek törlése szuper egyszerű.

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, és törölni akarom az összes sort, ahol nincs értékesítési értékem.

Ennek lépései az alábbiakban találhatók:

1. Válassza ki a teljes adatkészletet (ebben az esetben A1: D16).
2. megnyomni a F5 kulcs. Ezzel megnyílik az „Ugrás” párbeszédpanel (Ezt a párbeszédpanelt a Kezdőlap -> Szerkesztés -> Keresés és kiválasztás -> Ugrás menüpontból is megkaphatja).
3. Az „Ugrás” párbeszédpanelen kattintson a Speciális gombra. Ekkor megnyílik a „Go to Special” párbeszédpanel
4. Az Ugrás a speciális párbeszédpanelen válassza az „Üres” lehetőséget.
5. Kattintson az OK gombra.

A fenti lépések kiválasztják az összes cellát, amely üres az adatkészletben.

Miután kiválasztotta az üres cellákat, kattintson jobb gombbal bármelyik cellára, majd kattintson a Törlés gombra.

A Törlés párbeszédpanelen válassza a „Teljes sor” lehetőséget, majd kattintson az OK gombra. Ezzel törli az összes sort, amelyben üres cellák vannak.

Ha többet szeretne megtudni erről a technikáról, írtam egy részletes oktatóanyagot az üres cellákkal rendelkező sorok törléséről. Ez magában foglalja a „Go to Special” módszert, valamint egy VBA módszert az üres cellákkal rendelkező sorok törléséhez.

Sorok szűrése és törlése cellaérték alapján (VBA használatával)

Az utolsó módszer, amelyet megmutatok, tartalmaz egy kis VBA -t.

Ezt a módszert akkor használhatja, ha gyakran törölnie kell sorokat egy adott oszlop értéke alapján. Egyszer hozzáadhatja a VBA kódot, és hozzáadhatja a személyes makró munkafüzetéhez. Így az összes Excel -munkafüzetben használható lesz.

Ez a kód ugyanúgy működik, mint a fent ismertetett szűrési módszer (kivéve azt a tényt, hogy ez elvégzi az összes lépést a háttérben, és megkímél néhány kattintástól).

Tegyük fel, hogy rendelkezik az alábbi adatkészlettel, és törölni szeretné az összes olyan sort, ahol a régió Közép-Nyugat.

Az alábbiakban látható a VBA kód, amely ezt megteszi.

Sub DeleteRowsWithSpecificText () 'Forrás: https: //trumpexcel.com/delete-rows-based-on-cell-value/ ActiveCell.AutoFilter Field: = 2, Criteria1: = "Mid-West" ActiveSheet.AutoFilter.Range.Offset (1, 0). Sorok.Speciális cellák (xlCellTypeVisible). Törlés

A fenti kód a VBA Autofilter módszerrel először kiszűri a sorokat a megadott feltételek alapján (ami „Közép-Nyugat”), majd válassza ki az összes szűrt sort, és törölje azt.

Ne feledje, hogy a fenti kódban az eltolást használtam annak biztosítására, hogy a fejléc sora ne törlődjön.

A fenti kód nem működik, ha az adatok Excel táblázatban vannak. Ennek az az oka, hogy az Excel az Excel táblázatot listaobjektumnak tekinti. Tehát ha törölni szeretné a táblázatban található sorokat, módosítania kell egy kicsit a kódot (ebben az oktatóanyagban később szerepel).

A sorok törlése előtt megjelenik egy prompt az alábbiak szerint. Ezt hasznosnak találom, mivel lehetővé teszi, hogy a szűrés előtt kétszer ellenőrizzem a szűrt sort.

Ne feledje, hogy ha törli a sorokat a VBA használatával, nem vonhatja vissza ezt a módosítást. Tehát csak akkor használja ezt, ha biztos abban, hogy ez a kívánt módon működik. Ezenkívül célszerű megőrizni az adatok biztonsági másolatát arra az esetre, ha valami baj történne.

Ha az adatok Excel táblázatban vannak, az alábbi kóddal törölheti a meghatározott értékű sorokat:

Sub DeleteRowsinTables () 'Forrás: https: //trumpexcel.com/delete-rows-based-on-cell-value/ Dim Tbl ListObject Set Tbl = ActiveSheet.ListObjects (1) ActiveCell.AutoFilter mező: = 2, Kritérium1: = "Mid-West" Tbl.DataBodyRange.SpecialCells (xlCellTypeVisible) .Delete End Sub

Mivel a VBA az Excel Table -t List objektumnak (és nem tartománynak) tekinti, ennek megfelelően kellett módosítanom a kódot.

Hová tegye a VBA kódot?

Ezt a kódot el kell helyezni a modul VB Editor hátterébe.

Az alábbiakban bemutatjuk, hogyan kell ezt megtenni:

1. Nyissa meg azt a munkafüzetet, amelyhez hozzáadni kívánja ezt a kódot.
2. Az ALT + F11 billentyűparanccsal nyissa meg a VBA szerkesztő ablakát.
3. Ebben a VBA -szerkesztő ablakban a bal oldalon található a „Project Explorer” panel (amely felsorolja az összes munkafüzetet és munkalap -objektumot). Kattintson a jobb gombbal a munkafüzet bármelyik objektumára (amelyben szeretné, hogy ez a kód működjön), vigye a kurzort a „Beszúrás” fölé, majd kattintson a „Modul” gombra. Ez hozzáadja a Modul objektumot a munkafüzethez, és megnyitja a Modul kód ablakot is a jobb oldalon
4. A modul ablakában (amely a jobb oldalon jelenik meg) másolja ki és illessze be a fenti kódot.

Miután megtalálta a kódot a VB szerkesztőben, az alábbi módszerek bármelyikével futtathatja a kódot (győződjön meg arról, hogy kiválasztotta a cellát az adatkészletben, amelyen futtatni szeretné ezt a kódot):

1. Válassza ki a kód bármelyik sorát, és nyomja meg az F5 billentyűt.
2. Kattintson a Futtatás gombra a VB szerkesztő eszköztárában
3. Rendelje hozzá a makrót egy gombhoz vagy alakzathoz, és futtassa úgy, hogy rákattint a munkalapon
4. Adja hozzá a gyorselérési eszköztárhoz, és futtassa a kódot egyetlen kattintással.

A makrókód Excelben történő futtatásáról ebben a cikkben olvashat.

Megjegyzés: Mivel a munkafüzet VBA makrókódot tartalmaz, el kell mentenie a makró-engedélyezett formátumban (xlsm).