Ta bort rader baserat på ett cellvärde (eller skick) i Excel (enkel guide)

När du arbetar med stora datamängder kan du behöva snabbt ta bort rader baserat på cellvärdena i den (eller baserat på ett villkor).

Tänk till exempel på följande exempel:

1. Du har säljare och vill ta bort alla poster för en specifik region eller produkt.
2. Du vill radera alla poster där försäljningsvärdet är mindre än 100.
3. Du vill ta bort alla rader där det finns en tom cell.

Det finns flera sätt att skinna denna datakatt i Excel.

Metoden du väljer att radera raderna beror på hur din data är strukturerad och vad cellvärdet eller villkoret baseras på som du vill radera dessa rader.

I den här självstudien visar jag dig flera sätt att ta bort rader i Excel baserat på ett cellvärde eller ett villkor.

Filtrera rader baserat på värde/skick och sedan radera det

Ett av de snabbaste sätten att radera rader som innehåller ett visst värde eller uppfyller ett visst villkor är att filtrera dessa. När du har filtrerat data kan du ta bort alla dessa rader (medan de återstående raderna förblir intakta).

Excel -filtret är ganska mångsidigt och du kan filtrera utifrån många kriterier (t.ex. text, siffror, datum och färger)

Låt oss se två exempel där du kan filtrera raderna och ta bort dem.

Radera rader som innehåller en specifik text

Anta att du har en datauppsättning som visas nedan och du vill radera alla rader där regionen är i väst (i kolumn B).

Medan du i denna lilla dataset kan radera dessa rader manuellt, blir dina datamängder ofta enorma där raderingar manuellt inte är ett alternativ.

I så fall kan du filtrera alla poster där regionen är Mid-West och sedan ta bort alla dessa rader (samtidigt som de andra raderna är intakta).

Nedan följer stegen för att radera rader baserat på värdet (alla Mid-West-poster):

1. Markera en cell i datauppsättningen som du vill radera raderna från
2. Klicka på fliken Data
3. Klicka på ikonen Filter i gruppen "Sortera och filtrera". Detta kommer att tillämpa filter på alla rubrikceller i datamängden
4. Klicka på filterikonen i cellhuvudcellen i regionen (detta är en liten triangelikon nedåt till höger i cellen)
5. Avmarkera alla andra alternativ utom Mid-West-alternativet (ett snabbt sätt att göra detta är genom att klicka på alternativet Select All och sedan klicka på alternativet Mid-West). Detta filtrerar datauppsättningen och visar bara poster för Mid-West-regionen.
6. Markera alla filtrerade poster
7. Högerklicka på någon av de markerade cellerna och klicka på "Radera rad"
8. Klicka på OK i dialogrutan som öppnas. Vid denna tidpunkt ser du inga poster i datamängden.
9. Klicka på fliken Data och klicka på ikonen Filter. Detta tar bort filtret och du kommer att se alla poster utom de raderade.

Stegen ovan filtrerar först data baserat på ett cellvärde (eller kan vara ett annat villkor som efter/före ett datum eller större/mindre än ett tal). När du väl har posterna raderar du helt enkelt dessa.

Några användbara genvägar att veta för att påskynda processen:

1. Control + Shift + L för att applicera eller ta bort filtret
2. Kontroll + - (håll ned kontrollknappen och tryck på minustangenten) för att radera de markerade cellerna/raderna

I exemplet ovan hade jag bara fyra olika regioner och jag kunde manuellt välja och avmarkera det från filterlistan (i steg 5 ovan).

Om du har många kategorier/regioner kan du skriva namnet i fältet ovanför rutan (som har dessa regionnamn), och Excel visar bara de poster som matchar angiven text (som visas nedan). När du har texten baserad på vilken du vill filtrera trycker du på Enter.

Observera att när du tar bort en rad går allt du kan ha i andra celler i dessa rader förlorat. Ett sätt att komma runt detta är att skapa en kopia av data i ett annat kalkylblad och radera raderna i de kopierade data. När du är klar kopierar du tillbaka den i stället för originaldata.

Eller

Du kan använda metoderna som visas senare i den här självstudien (med hjälp av sorteringsmetoden eller Sök alla -metoden)

Ta bort rader baserat på ett numeriskt tillstånd

Precis som jag använde filtermetoden för att radera alla rader som innehåller texten Mid-West kan du också använda ett talvillkor (eller ett datumvillkor).

Anta till exempel att jag har nedanstående dataset och jag vill ta bort alla rader där försäljningsvärdet är mindre än 200.

Nedan följer stegen för att göra detta:

1. Markera valfri cell i data
2. Klicka på fliken Data
3. Klicka på ikonen Filter i gruppen "Sortera och filtrera". Detta kommer att tillämpa filter på alla rubrikceller i datamängden
4. Klicka på ikonen Filter i rubrikcellen Försäljning (det här är en liten nedåtpekande triangelikon högst upp till höger i cellen)
5. Håll muspekaren över alternativet Nummerfilter. Detta visar dig alla nummerrelaterade filteralternativ i Excel.
6. Klicka på alternativet "Mindre än".
7. I dialogrutan "Anpassad autofilter" som öppnas anger du värdet "200" i fältet
8. Klicka på OK. Detta filtrerar och visar bara de poster där försäljningsvärdet är mindre än 200
9. Markera alla filtrerade poster
10. Högerklicka på någon av cellerna och klicka på Ta bort rad
11. Klicka på OK i dialogrutan som öppnas. Vid denna tidpunkt ser du inga poster i datamängden.
12. Klicka på fliken Data och klicka på ikonen Filter. Detta tar bort filtret och du kommer att se alla poster utom de raderade.

Det finns många nummerfilter som du kan använda i Excel - till exempel mindre än/större än, lika/inte lika, mellan, topp 10, över eller under genomsnittet, etc.

Obs! Du kan också använda flera filter. Du kan till exempel ta bort alla rader där försäljningsvärdet är större än 200 men mindre än 500. I det här fallet måste du använda två filtervillkor. Dialogrutan Anpassad autofilter tillåter två filterkriterier (OCH såväl som OR).

Precis som nummerfiltren kan du också filtrera posterna utifrån datumet. Om du till exempel vill ta bort alla poster från det första kvartalet kan du göra det med samma steg ovan. När du arbetar med datumfilter visar Excel automatiskt relevanta filter (som visas nedan).

Även om filtrering är ett bra sätt att snabbt radera rader baserat på ett värde eller ett villkor, har det en nackdel - det raderar hela raden. I fallet nedan raderas till exempel all data som finns till höger om den filtrerade datauppsättningen.

Vad händer om jag bara vill ta bort poster från datamängden, men vill behålla resten av data intakt.

Du kan inte göra det med filtrering, men du kan göra det med sortering.

Sortera datauppsättningen och radera sedan raderna

Även om sortering är ett annat sätt att radera rader baserat på värde, men i de flesta fall är det bättre att använda filtermetoden som täcks ovan.

Denna sorteringsteknik rekommenderas endast när du vill radera cellerna med värdena och inte hela raderna.

Anta att du har en datauppsättning som visas nedan och att du vill ta bort alla poster där regionen är i väst.

Nedan följer stegen för att göra detta med hjälp av sortering:

1. Välj vilken cell som helst i data
2. Klicka på fliken Data
3. Klicka på ikonen Sortera i gruppen Sortera och filtrera.
4. Välj Region i kolumnen sortera efter kolumnen i dialogrutan Sortera som öppnas.
5. I alternativet Sortera på, se till att cellvärden är markerade
6. I alternativet Order väljer du A till Z (eller Z till A, spelar egentligen ingen roll).
7. Klicka på OK. Detta ger dig den sorterade datamängden enligt nedan (sorterad efter kolumn B).
8. Välj alla poster med regionen Mid-West (alla celler i raderna, inte bara regionkolumnen)
9. När du väl valt högerklickar du och klickar sedan på Ta bort. Detta öppnar dialogrutan Ta bort.
10. Se till att alternativet "Skifta upp celler" är markerat.
11. Klicka på OK.

Stegen ovan skulle radera alla poster där regionen var i väst, men det raderar inte hela raden. Så om du har data till höger eller vänster om din dataset kommer den att förbli oskadad.

I exemplet ovan har jag sorterat data baserat på cellvärdet, men du kan också använda samma steg för att sortera baserat på siffror, datum, cellfärg eller teckensnittsfärg, etc.

Här är en detaljerad guide om hur du sorterar data i Excel

Om du vill behålla den ursprungliga datauppsättningsordningen men tar bort posterna baserat på kriterier måste du ha ett sätt att sortera data tillbaka till den ursprungliga. För att göra detta, lägg till en kolumn med serienummer innan du sorterar data. När du är klar med att radera raderna/posterna sorterar du helt enkelt baserat på den här extra kolumnen du lade till.

Hitta och markera cellerna baserat på cellvärde och radera sedan raderna

Excel har en Sök och ersätt funktionalitet som kan vara bra när du vill hitta och markera celler med ett visst värde.

När du har markerat dessa celler kan du enkelt radera raderna.

Anta att du har datauppsättningen enligt nedan och att du vill ta bort alla rader där regionen är Mid-West.

Nedan följer stegen för att göra detta:

1. Välj hela datauppsättningen
2. Klicka på fliken Hem
3. I gruppen Redigering klickar du på alternativet "Sök och välj" och sedan på Sök (du kan också använda tangentbordsgenvägen Ctrl + F).
4. I dialogrutan Sök och ersätt anger du texten "Mid-West" i fältet "Hitta vad:".
5. Klicka på Hitta alla. Detta visar dig omedelbart alla förekomster av texten Mid-West som Excel kunde hitta.
6. Använd kortkommandot Ctrl + A för att markera alla celler som Excel hittade. Du kommer också att kunna se alla markerade celler i datamängden.
7. Högerklicka på någon av de markerade cellerna och klicka på Ta bort. Detta öppnar dialogrutan Ta bort.
8. Välj alternativet "Hel rad"
9. Klicka på OK.

Stegen ovan skulle radera alla celler där regionvärdet är Mid-West.

Obs! Eftersom Sök och ersätt kan hantera jokertecken kan du använda dessa när du hittar data i Excel. Om du till exempel vill radera alla rader där regionen antingen är Mid-West eller South-West, kan du använda ‘*Väst'Som texten att hitta i dialogrutan Sök och ersätt. Detta ger dig alla celler där texten slutar med ordet väst.

Ta bort alla rader med en tom cell

Om du vill ta bort alla rader där det finns tomma celler kan du enkelt göra detta med en inbyggd funktionalitet i Excel.

Det är Gå till specialceller alternativ - som låter dig snabbt välja alla tomma celler. Och när du väl har markerat alla tomma celler är det enkelt att ta bort dessa.

Anta att du har datauppsättningen enligt nedan och jag vill ta bort alla rader där jag inte har försäljningsvärdet.

Nedan följer stegen för att göra detta:

1. Välj hela datamängden (A1: D16 i det här fallet).
2. tryck på F5 nyckel. Detta öppnar dialogrutan "Gå till" (Du kan också få den här dialogrutan från Hem -> Redigering -> Sök och välj -> Gå till).
3. Klicka på knappen Special i dialogrutan "Gå till". Detta öppnar dialogrutan "Gå till special"
4. I dialogrutan Gå till special väljer du "Tomrum".
5. Klicka på OK.

Stegen ovan skulle välja alla celler som är tomma i datamängden.

När du har markerat de tomma cellerna högerklickar du på någon av cellerna och klickar på Ta bort.

I dialogrutan Ta bort väljer du alternativet "Hel rad" och klickar på OK. Detta raderar alla rader som har tomma celler.

Om du är intresserad av att lära dig mer om denna teknik skrev jag en detaljerad handledning om hur du tar bort rader med tomma celler. Den innehåller metoden "Gå till special" samt en VBA -metod för att radera rader med tomma celler.

Filtrera och ta bort rader baserat på cellvärde (med VBA)

Den sista metoden som jag ska visa dig inkluderar lite VBA.

Du kan använda den här metoden om du ofta behöver radera rader baserat på ett visst värde i en kolumn. Du kan lägga till VBA -koden en gång och lägga till den i din personliga makro -arbetsbok. På så sätt kommer den att vara tillgänglig för användning i alla dina Excel -arbetsböcker.

Denna kod fungerar på samma sätt som filtermetoden som täcks ovan (förutom det faktum att detta gör alla steg i backend och sparar några klick).

Anta att du har datauppsättningen enligt nedan och att du vill ta bort alla rader där regionen är Mid-West.

Nedan finns VBA -koden som gör detta.

Sub DeleteRowsWithSpecificText () 'Källa: https: //trumpexcel.com/delete-rows-based-on-cell-value/ ActiveCell.AutoFilter Field: = 2, Criteria1: = "Mid-West" ActiveSheet.AutoFilter.Range.Offset (1, 0) .Rows.SpecialCells (xlCellTypeVisible) .Delete End Sub

Koden ovan använder VBA Autofilter-metoden för att först filtrera raderna baserat på de angivna kriterierna (som är "Mid-West"), välj sedan alla filtrerade rader och ta bort det.

Observera att jag har använt Offset i koden ovan för att se till att min rubrikrad inte raderas.

Ovanstående kod fungerar inte om din data finns i en Excel -tabell. Anledningen till detta är att Excel betraktar en Excel -tabell som ett listobjekt. Så om du vill ta bort rader som finns i en tabell måste du ändra koden lite (täcks senare i den här självstudien).

Innan du tar bort raderna visas en uppmaning enligt nedan. Jag tycker att det är användbart eftersom det tillåter mig att dubbelkolla den filtrerade raden innan jag tar bort.

Kom ihåg att när du tar bort rader med VBA kan du inte ångra den här ändringen. Så använd det här bara när du är säker på att det fungerar som du vill. Det är också en bra idé att behålla en säkerhetskopia av data om något skulle gå fel.

Om dina data finns i en Excel -tabell använder du koden nedan för att radera rader med ett specifikt värde i den:

Sub DeleteRowsinTables () 'Källa: https: //trumpexcel.com/delete-rows-based-on-cell-value/ Dim Tbl As ListObject Set Tbl = ActiveSheet.ListObjects (1) ActiveCell.AutoFilter Field: = 2, Criteria1: = "Mid-West" Tbl.DataBodyRange.SpecialCells (xlCellTypeVisible) .Delete End Sub

Eftersom VBA anser Excel -tabellen som ett listaobjekt (och inte ett intervall), var jag tvungen att ändra koden i enlighet därmed.

Var ska man lägga VBA -koden?

Denna kod måste placeras i VB Editor -backend i en modul.

Nedan följer stegen som visar hur du gör detta:

1. Öppna arbetsboken där du vill lägga till den här koden.
2. Använd tangentbordsgenvägen ALT + F11 för att öppna fönstret VBA Editor.
3. I detta VBA Editor -fönster, till vänster, finns en "Project Explorer" -ruta (som listar alla arbetsböcker och kalkylarkobjekt). Högerklicka på ett objekt i arbetsboken (där du vill att den här koden ska fungera), håll markören över "Infoga" och klicka sedan på "Modul". Detta lägger till modulobjektet i arbetsboken och öppnar också modulkodfönstret till höger
4. Kopiera och klistra in ovanstående kod i modulfönstret (som visas till höger).

När du har koden i VB -redigeraren kan du köra koden med någon av metoderna nedan (se till att du har valt vilken cell som helst i datauppsättningen som du vill köra den här koden):

1. Välj vilken rad som helst i koden och tryck på F5.
2. Klicka på knappen Kör i verktygsfältet i VB Editor
3. Tilldela makrot till en knapp eller en form och kör den genom att klicka på den i själva kalkylbladet
4. Lägg till den i snabbåtkomstverktygsfältet och kör koden med ett enda klick.

Du kan läsa allt om hur man kör makrokoden i Excel i den här artikeln.

Obs! Eftersom arbetsboken innehåller en VBA-makrokod måste du spara den i det makroaktiverade formatet (xlsm).