Slet rækker baseret på en celleværdi (eller tilstand) i Excel (let vejledning)

Når du arbejder med store datasæt, har du muligvis brug for hurtigt at slette rækker baseret på celleværdierne i det (eller baseret på en betingelse).

Overvej f.eks. Følgende eksempler:

1. Du har sælgerdata, og du vil slette alle optegnelser for en bestemt region eller et bestemt produkt.
2. Du vil slette alle de poster, hvor salgsværdien er mindre end 100.
3. Du vil slette alle rækker, hvor der er en tom celle.

Der er flere måder at huden denne datakat på i Excel.

Den metode, du vælger at slette rækkerne, afhænger af, hvordan dine data er struktureret, og hvad er celleværdien eller tilstanden, som du vil slette disse rækker på.

I denne vejledning viser jeg dig flere måder at slette rækker i Excel baseret på en celleværdi eller en betingelse.

Filtrer rækker baseret på værdi/tilstand og derefter slette den

En af de hurtigste måder at slette rækker, der indeholder en bestemt værdi eller opfylder en given betingelse, er at filtrere disse. Når du har de filtrerede data, kan du slette alle disse rækker (mens de resterende rækker forbliver intakte).

Excel -filter er ret alsidigt, og du kan filtrere ud fra mange kriterier (såsom tekst, tal, datoer og farver)

Lad os se to eksempler, hvor du kan filtrere rækkerne og slette dem.

Slet rækker, der indeholder en bestemt tekst

Antag, at du har et datasæt som vist herunder, og du vil slette alle de rækker, hvor regionen er midtvest (i kolonne B).

Mens du i dette lille datasæt kan vælge at slette disse rækker manuelt, vil dine datasæt ofte være enorme, hvor det ikke er en mulighed at slette rækker manuelt.

I så fald kan du filtrere alle de poster, hvor regionen er midt-vest, og derefter slette alle disse rækker (samtidig med at de andre rækker forbliver intakte).

Nedenfor er trinene til at slette rækker baseret på værdien (alle Mid-West-poster):

1. Vælg en celle i datasættet, hvorfra du vil slette rækker
2. Klik på fanen Data
3. Klik på filterikonet i gruppen 'Sorter og filtrer'. Dette vil anvende filtre til alle overskriftscellerne i datasættet
4. Klik på filterikonet i cellen Regionoverskrift (dette er et lille trekant-ikon nedadvendt øverst til højre i cellen)
5. Fravælg alle de andre muligheder undtagen Mid-West-indstillingen (en hurtig måde at gøre dette på er ved at klikke på indstillingen Vælg alle og derefter klikke på indstillingen Midtvest). Dette filtrerer datasættet og viser dig kun poster for Mid-West-regionen.
6. Vælg alle de filtrerede poster
7. Højreklik på en af ​​de markerede celler og klik på 'Slet række'
8. Klik på OK i dialogboksen, der åbnes. På dette tidspunkt ser du ingen poster i datasættet.
9. Klik på fanen Data, og klik på ikonet Filter. Dette fjerner filteret, og du vil se alle posterne undtagen de slettede.

Ovenstående trin filtrerer først dataene baseret på en celleværdi (eller kan være en anden tilstand, f.eks. Efter/før en dato eller større/mindre end et tal). Når du har optegnelserne, sletter du simpelthen disse.

Nogle nyttige genveje at vide for at fremskynde processen:

1. Control + Shift + L at anvende eller fjerne filteret
2. Control + - (hold kontroltasten nede, og tryk på minustasten) for at slette de markerede celler/rækker

I ovenstående eksempel havde jeg kun fire forskellige områder, og jeg kunne manuelt vælge og fravælge det fra filterlisten (i trin 5 ovenfor).

Hvis du har mange kategorier/regioner, kan du skrive navnet i feltet lige over feltet (der har disse regionnavne), og Excel viser dig kun de poster, der matcher indtastet tekst (som vist nedenfor). Når du har teksten baseret på, som du vil filtrere, skal du trykke på Enter -tasten.

Bemærk, at når du sletter en række, går alt, hvad du måtte have i andre celler i disse rækker, tabt. En måde at komme uden om dette er at oprette en kopi af dataene i et andet regneark og slette rækker i de kopierede data. Når det er gjort, skal du kopiere det tilbage i stedet for de originale data.

Eller

Du kan bruge de metoder, der vises senere i denne vejledning (ved hjælp af sorteringsmetoden eller Find alle -metoden)

Slet rækker baseret på en numerisk tilstand

Ligesom jeg brugte filtermetoden til at slette alle de rækker, der indeholder teksten Midt-vest, kan du også bruge en talbetingelse (eller en datotilstand).

Antag for eksempel, at jeg har nedenstående datasæt, og jeg vil slette alle de rækker, hvor salgsværdien er mindre end 200.

Nedenfor er trinene for at gøre dette:

1. Vælg en celle i dataene
2. Klik på fanen Data
3. Klik på filterikonet i gruppen 'Sorter og filtrer'. Dette vil anvende filtre til alle overskriftscellerne i datasættet
4. Klik på Filter-ikonet i salgsoverskriftscellen (dette er et lille trekant-ikon nedadvendt øverst til højre i cellen)
5. Hold markøren over indstillingen Talefiltre. Dette viser dig alle de nummerrelaterede filterindstillinger i Excel.
6. Klik på indstillingen 'Mindre end'.
7. I dialogboksen 'Brugerdefineret autofilter', der åbnes, skal du indtaste værdien '200' i feltet
8. Klik på OK. Dette filtrerer og viser kun de poster, hvor salgsværdien er mindre end 200
9. Vælg alle de filtrerede poster
10. Højreklik på en af ​​cellerne, og klik på Slet række
11. Klik på OK i dialogboksen, der åbnes. På dette tidspunkt ser du ingen poster i datasættet.
12. Klik på fanen Data, og klik på ikonet Filter. Dette fjerner filteret, og du vil se alle posterne undtagen de slettede.

Der er mange talfiltre, som du kan bruge i Excel - såsom mindre end/større end, lig/ikke lig, mellem, top 10, over eller under gennemsnittet osv.

Bemærk: Du kan også bruge flere filtre. Du kan f.eks. Slette alle de rækker, hvor salgsværdien er større end 200 men mindre end 500. I dette tilfælde skal du bruge to filterbetingelser. Dialogboksen Brugerdefineret autofilter tillader to filterkriterier (OG såvel som OR).

Ligesom talfiltrene kan du også filtrere posterne baseret på datoen. Hvis du f.eks. Vil fjerne alle poster i det første kvartal, kan du gøre det ved at bruge de samme trin ovenfor. Når du arbejder med datofiltre, viser Excel dig automatisk relevante filtre (som vist nedenfor).

Selvom filtrering er en fantastisk måde at hurtigt slette rækker baseret på en værdi eller en betingelse, har den en ulempe - den sletter hele rækken. For eksempel ville det i nedenstående tilfælde slette alle de data, der er til højre for det filtrerede datasæt.

Hvad hvis jeg kun vil slette poster fra datasættet, men vil beholde de resterende data intakte.

Du kan ikke gøre det med filtrering, men du kan gøre det med sortering.

Sorter datasættet og derefter slette rækker

Selvom sortering er en anden måde at slette rækker baseret på værdi, men i de fleste tilfælde har du det bedre med at bruge filtermetoden, der er dækket ovenfor.

Denne sorteringsteknik anbefales kun, når du vil slette cellerne med værdierne og ikke hele rækker.

Antag, at du har et datasæt som vist nedenfor, og du vil slette alle de poster, hvor regionen er Midt-vest.

Nedenfor er trinene til at gøre dette ved hjælp af sortering:

1. Vælg en celle i dataene
2. Klik på fanen Data
3. Klik på ikonet Sorter i gruppen Sorter og filtrer.
4. Vælg Region i kolonnen Sorter efter i dialogboksen Sorter, der åbnes.
5. I indstillingen Sorter efter skal du kontrollere, at celleværdier er valgt
6. I ordreindstilling vælges A til Z (eller Z til A, betyder ikke rigtig noget).
7. Klik på OK. Dette giver dig det sorterede datasæt som vist nedenfor (sorteret efter kolonne B).
8. Vælg alle poster med regionen Midtvest (alle cellerne i rækkerne, ikke kun regionkolonnen)
9. Når du er valgt, skal du højreklikke og derefter klikke på Slet. Dette åbner dialogboksen Slet.
10. Sørg for, at 'Skift celler op' er valgt.
11. Klik på OK.

Ovenstående trin ville slette alle de poster, hvor regionen var Midt-Vest, men det sletter ikke hele rækken. Så hvis du har data til højre eller venstre for dit datasæt, forbliver det uskadt.

I eksemplet ovenfor har jeg sorteret data baseret på celleværdien, men du kan også bruge de samme trin til at sortere baseret på tal, datoer, cellefarve eller skrifttypefarve osv.

Her er en detaljeret guide til, hvordan du sorterer data i Excel

Hvis du vil beholde den oprindelige datasætrækkefølge, men fjerner posterne baseret på kriterier, skal du have en måde at sortere dataene tilbage til den originale. For at gøre dette skal du tilføje en kolonne med serienumre, før du sorterer dataene. Når du er færdig med at slette rækker/poster, skal du blot sortere baseret på denne ekstra kolonne, du har tilføjet.

Find og vælg cellerne baseret på celleværdi og derefter slette rækker

Excel har en Find og erstat -funktionalitet, der kan være fantastisk, når du vil finde og markere celler med en bestemt værdi.

Når du har valgt disse celler, kan du nemt slette rækker.

Antag, at du har datasættet som vist herunder, og du vil slette alle de rækker, hvor regionen er midt-vest.

Nedenfor er trinene for at gøre dette:

1. Vælg hele datasættet
2. Klik på fanen Startside
3. I gruppen Redigering skal du klikke på indstillingen ‘Find & Select’ og derefter klikke på Find (du kan også bruge tastaturgenvejen Control + F).
4. I dialogboksen Find og erstat skal du indtaste teksten 'Midtvest' i feltet 'Find hvad:'.
5. Klik på Find alle. Dette vil øjeblikkeligt vise dig alle de forekomster af teksten Mid-West, som Excel var i stand til at finde.
6. Brug tastaturgenvejen Ctrl + A til at markere alle de celler, Excel fandt. Du vil også kunne se alle de valgte celler i datasættet.
7. Højreklik på en af ​​de markerede celler, og klik på Slet. Dette åbner dialogboksen Slet.
8. Vælg indstillingen 'Hele rækken'
9. Klik på OK.

Ovenstående trin ville slette alle cellerne, hvor regionsværdien er Midt-vest.

Bemærk: Da Find og erstat kan håndtere jokertegn, kan du bruge disse, når du finder data i Excel. Hvis du f.eks. Vil slette alle de rækker, hvor regionen enten er Midtvest eller Sydvest, kan du bruge ‘*Vest‘Som teksten, der skal findes i dialogboksen Find og erstat. Dette giver dig alle cellerne, hvor teksten ender med ordet vest.

Slet alle rækker med en tom celle

Hvis du vil slette alle de rækker, hvor der er tomme celler, kan du nemt gøre dette med en indbygget funktionalitet i Excel.

Det er Gå til specielle celler option - som giver dig mulighed for hurtigt at vælge alle de tomme celler. Og når du har markeret alle de tomme celler, er det superenkelt at slette disse.

Antag, at du har datasættet som vist nedenfor, og jeg vil slette alle de rækker, hvor jeg ikke har salgsværdien.

Nedenfor er trinene for at gøre dette:

1. Vælg hele datasættet (A1: D16 i dette tilfælde).
2. Tryk på F5 nøgle. Dette åbner dialogboksen 'Gå til' (Du kan også få denne dialogboks fra Hjem -> Redigering -> Find og vælg -> Gå til).
3. Klik på knappen Special i dialogboksen 'Gå til'. Dette åbner dialogboksen 'Gå til speciel'
4. Vælg 'Emner' i dialogboksen Gå til speciel.
5. Klik på OK.

Ovenstående trin ville markere alle de celler, der er tomme i datasættet.

Når du har markeret de tomme celler, skal du højreklikke på en af ​​cellerne og klikke på Slet.

I dialogboksen Slet skal du vælge "Hele rækken" og klikke på OK. Dette vil slette alle rækker, der har tomme celler i det.

Hvis du er interesseret i at lære mere om denne teknik, skrev jeg en detaljeret vejledning i, hvordan du sletter rækker med tomme celler. Det inkluderer metoden 'Gå til speciel' samt en VBA -metode til at slette rækker med tomme celler.

Filtrer og slet rækker baseret på celleværdi (ved hjælp af VBA)

Den sidste metode, som jeg vil vise dig, indeholder en lille smule VBA.

Du kan bruge denne metode, hvis du ofte skal slette rækker baseret på en bestemt værdi i en kolonne. Du kan tilføje VBA -koden en gang og tilføje den til din personlige makro -projektmappe. På denne måde vil den være tilgængelig til brug i alle dine Excel -projektmapper.

Denne kode fungerer på samme måde som filtermetoden, der er dækket ovenfor (bortset fra at dette udfører alle trin i backend og sparer dig for nogle klik).

Antag, at du har datasættet som vist nedenfor, og du vil slette alle de rækker, hvor regionen er midt-vest.

Nedenfor er VBA -koden, der gør dette.

Sub DeleteRowsWithSpecificText () 'Kilde: 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) .Slet Slut Sub

Ovenstående kode bruger VBA Autofilter-metoden til først at filtrere rækkerne baseret på de angivne kriterier (som er 'Midtvest'), derefter markere alle de filtrerede rækker og slette den.

Bemærk, at jeg har brugt Offset i ovenstående kode for at sikre, at min header -række ikke slettes.

Ovenstående kode fungerer ikke, hvis dine data er i en Excel -tabel. Årsagen til dette er, at Excel betragter en Excel -tabel som et listeobjekt. Så hvis du vil slette rækker i en tabel, skal du ændre koden lidt (dækket senere i denne vejledning).

Inden du sletter rækkerne, viser den dig en prompt som vist herunder. Jeg finder dette nyttigt, da det giver mig mulighed for at dobbelttjekke den filtrerede række, før jeg sletter.

Husk, at når du sletter rækker ved hjælp af VBA, kan du ikke fortryde denne ændring. Så brug dette kun, når du er sikker på, at det fungerer, som du vil. Det er også en god idé at gemme en sikkerhedskopi af dataene, hvis noget går galt.

Hvis dine data er i en Excel -tabel, skal du bruge nedenstående kode til at slette rækker med en bestemt værdi i den:

Sub DeleteRowsinTables () 'Kilde: 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) .Slet Slut Sub

Da VBA betragter Excel Table som et listeobjekt (og ikke et område), var jeg nødt til at ændre koden i overensstemmelse hermed.

Hvor placeres VBA -koden?

Denne kode skal placeres i VB Editor -backend i et modul.

Nedenfor er trinene, der viser dig, hvordan du gør dette:

1. Åbn den projektmappe, hvor du vil tilføje denne kode.
2. Brug tastaturgenvejen ALT + F11 til at åbne vinduet VBA Editor.
3. I dette VBA Editor -vindue til venstre er der en "Project Explorer" -rude (som viser alle projektmapper og regnearkobjekter). Højreklik på ethvert objekt i projektmappen (hvor du vil have denne kode til at fungere), hold markøren over 'Indsæt', og klik derefter på 'Modul'. Dette tilføjer modulobjektet til projektmappen og åbner også modulkodevinduet til højre
4. I modulvinduet (der vises til højre), kopier og indsæt ovenstående kode.

Når du har koden i VB Editor, kan du køre koden ved hjælp af en af ​​nedenstående metoder (sørg for at have den valgte celle i det datasæt, som du vil køre denne kode på):

1. Vælg en hvilken som helst linje i koden, og tryk på F5 -tasten.
2. Klik på knappen Kør i værktøjslinjen i VB Editor
3. Tildel makroen til en knap eller en figur, og kør den ved at klikke på den i selve regnearket
4. Føj den til værktøjslinjen Hurtig adgang, og kør koden med et enkelt klik.

Du kan læse alt om, hvordan du kører makrokoden i Excel i denne artikel.

Bemærk: Da projektmappen indeholder en VBA-makrokode, skal du gemme den i det makroaktiverede format (xlsm).