Slett rader basert på en celleverdi (eller tilstand) i Excel (enkel guide)

Når du arbeider med store datasett, kan det hende du må slette rader raskt basert på celleverdiene i det (eller basert på en betingelse).

Tenk for eksempel på følgende eksempler:

1. Du har salgsdata, og du vil slette alle postene for en bestemt region eller et bestemt produkt.
2. Du vil slette alle postene der salgsverdien er mindre enn 100.
3. Du vil slette alle radene der det er en tom celle.

Det er flere måter å flå denne datakatten i Excel.

Metoden du velger å slette radene vil avhenge av hvordan dataene dine er strukturert og hva celleverdien eller tilstanden er basert på som du vil slette disse radene.

I denne opplæringen vil jeg vise deg flere måter å slette rader i Excel basert på en celleverdi eller en betingelse.

Filtrer rader basert på verdi/tilstand og deretter slette den

En av de raskeste måtene å slette rader som inneholder en bestemt verdi eller oppfyller en gitt betingelse, er å filtrere disse. Når du har de filtrerte dataene, kan du slette alle disse radene (mens de resterende radene forblir intakte).

Excel -filteret er ganske allsidig, og du kan filtrere basert på mange kriterier (for eksempel tekst, tall, datoer og farger)

La oss se to eksempler der du kan filtrere radene og slette dem.

Slett rader som inneholder en bestemt tekst

Anta at du har et datasett som vist nedenfor, og du vil slette alle radene der regionen er Midtvest (i kolonne B).

Mens du i dette lille datasettet kan velge å slette disse radene manuelt, vil datasettene ofte være enorme der sletting av rader manuelt ikke vil være et alternativ.

I så fall kan du filtrere alle postene der regionen er Midt-Vest og deretter slette alle disse radene (mens de andre radene forblir intakte).

Nedenfor er trinnene for å slette rader basert på verdien (alle Mid-West-poster):

1. Velg en celle i datasettet du vil slette radene fra
2. Klikk på fanen Data
3. Klikk på Filter -ikonet i gruppen "Sorter og filtrer". Dette vil bruke filtre til alle overskriftscellene i datasettet
4. Klikk på Filter-ikonet i cellen i regionoverskriften (dette er et lite trekantikon nedover øverst til høyre i cellen)
5. Fjern markeringen av alle de andre alternativene bortsett fra alternativet Midtvest (en rask måte å gjøre dette på er å klikke på Velg alle og deretter klikke på Midtvest). Dette vil filtrere datasettet og bare vise deg poster for Midt-Vest-regionen.
6. Velg alle de filtrerte postene
7. Høyreklikk på en av de valgte cellene og klikk på "Slett rad"
8. Klikk på OK i dialogboksen som åpnes. På dette tidspunktet ser du ingen poster i datasettet.
9. Klikk på kategorien Data og klikk på Filter -ikonet. Dette vil fjerne filteret, og du vil se alle postene bortsett fra de slettede.

Trinnene ovenfor filtrerer først dataene basert på en celleverdi (eller kan være en annen tilstand, for eksempel etter/før en dato eller større/mindre enn et tall). Når du har postene, sletter du bare disse.

Noen nyttige snarveier å vite for å fremskynde prosessen:

1. Kontroll + Skift + L. for å påføre eller fjerne filteret
2. Kontroll + - (hold inne kontrolltasten og trykk på minustasten) for å slette de valgte cellene/radene

I eksemplet ovenfor hadde jeg bare fire forskjellige regioner, og jeg kunne manuelt velge og fjerne markeringen fra filterlisten (i trinn 5 ovenfor).

Hvis du har mange kategorier/regioner, kan du skrive inn navnet i feltet rett over boksen (som har disse regionnavnene), og Excel viser deg bare de postene som samsvarer med angitt tekst (som vist nedenfor). Når du har teksten basert på hvilken du vil filtrere, trykker du på Enter -tasten.

Vær oppmerksom på at når du sletter en rad, vil alt du måtte ha i andre celler i disse radene gå tapt. En måte å unngå dette på er å lage en kopi av dataene i et annet regneark og slette radene i de kopierte dataene. Når du er ferdig, kopierer du den tilbake i stedet for de originale dataene.

Eller

Du kan bruke metodene som vises senere i denne opplæringen (ved hjelp av sorteringsmetoden eller Finn alle -metoden)

Slett rader basert på en numerisk tilstand

Akkurat som jeg brukte filtermetoden for å slette alle radene som inneholder teksten Midtvest, kan du også bruke en tallbetingelse (eller en datobetingelse).

Anta for eksempel at jeg har datasettet nedenfor, og jeg vil slette alle radene der salgsverdien er mindre enn 200.

Nedenfor er trinnene for å gjøre dette:

1. Velg en celle i dataene
2. Klikk på fanen Data
3. Klikk på Filter -ikonet i gruppen "Sorter og filtrer". Dette vil bruke filtre til alle overskriftscellene i datasettet
4. Klikk på Filter-ikonet i salgsoverskriftscellen (dette er et lite trekantikon nedover til høyre i cellen)
5. Hold markøren over alternativet Nummerfiltre. Dette viser deg alle tallrelaterte filteralternativer i Excel.
6. Klikk på alternativet 'Mindre enn'.
7. I dialogboksen "Egendefinert autofilter" som åpnes, angir du verdien "200" i feltet
8. Klikk OK. Dette filtrerer og viser bare postene der salgsverdien er mindre enn 200
9. Velg alle de filtrerte postene
10. Høyreklikk på en av cellene og klikk på Slett rad
11. Klikk på OK i dialogboksen som åpnes. På dette tidspunktet ser du ingen poster i datasettet.
12. Klikk på kategorien Data og klikk på Filter -ikonet. Dette vil fjerne filteret, og du vil se alle postene bortsett fra de slettede.

Det er mange tallfiltre du kan bruke i Excel - for eksempel mindre enn/større enn, lik/ikke lik, mellom, topp 10, over eller under gjennomsnittet, etc.

Merk: Du kan også bruke flere filtre. For eksempel kan du slette alle radene der salgsverdien er større enn 200 men mindre enn 500. I dette tilfellet må du bruke to filterbetingelser. Dialogboksen Egendefinert autofilter tillater to filterkriterier (OG og OR).

På samme måte som tallfiltrene, kan du også filtrere postene basert på datoen. For eksempel, hvis du vil fjerne alle postene i det første kvartalet, kan du gjøre det ved å bruke de samme trinnene ovenfor. Når du jobber med datofiltre, viser Excel deg automatisk relevante filtre (som vist nedenfor).

Selv om filtrering er en fin måte å raskt slette rader basert på en verdi eller en betingelse, har den en ulempe - den sletter hele raden. For eksempel, i tilfellet nedenfor, ville det slette alle dataene som er til høyre for det filtrerte datasettet.

Hva om jeg bare vil slette poster fra datasettet, men vil beholde de resterende dataene intakte.

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

Sorter datasettet og slett deretter radene

Selv om sortering er en annen måte å slette rader basert på verdi, men i de fleste tilfeller er det bedre å bruke filtermetoden som er dekket ovenfor.

Denne sorteringsteknikken anbefales bare når du vil slette cellene med verdiene og ikke hele radene.

Anta at du har et datasett som vist nedenfor, og du vil slette alle postene der regionen er Midt-vest.

Nedenfor er trinnene for å gjøre dette ved å bruke sortering:

1. Velg en celle i dataene
2. Klikk på fanen Data
3. Klikk på Sorter -ikonet i gruppen Sorter og filtrer.
4. Velg Region i sorterings -kolonnen i dialogboksen Sorter som åpnes.
5. I alternativet Sorter på må du kontrollere at celleverdier er valgt
6. I alternativet Bestill, velg A til Å (eller Z til A, spiller egentlig ingen rolle).
7. Klikk OK. Dette vil gi deg det sorterte datasettet som vist nedenfor (sortert etter kolonne B).
8. Velg alle postene med regionen Midt-vest (alle cellene i radene, ikke bare regionkolonnen)
9. Når du er valgt, høyreklikker du og klikker deretter på Slett. Dette åpner dialogboksen Slett.
10. Sørg for at alternativet "Skift celler opp" er valgt.
11. Klikk OK.

Trinnene ovenfor vil slette alle postene der regionen var Midt-Vest, men den sletter ikke hele raden. Så hvis du har data til høyre eller venstre for datasettet ditt, forblir det uskadd.

I eksemplet ovenfor har jeg sortert dataene basert på celleverdien, men du kan også bruke de samme trinnene til å sortere basert på tall, datoer, cellefarge eller skriftfarge, etc.

Her er en detaljert guide for hvordan du sorterer data i Excel

Hvis du vil beholde den opprinnelige datasettrekkefølgen, men fjerner postene basert på kriterier, må du ha en måte å sortere dataene tilbake til den opprinnelige. For å gjøre dette, legg til en kolonne med serienumre før du sorterer dataene. Når du er ferdig med å slette radene/postene, sorterer du bare basert på denne ekstra kolonnen du la til.

Finn og velg cellene basert på celleverdi, og slett deretter radene

Excel har en Finn og erstatt -funksjonalitet som kan være flott når du vil finne og velge celler med en bestemt verdi.

Når du har valgt disse cellene, kan du enkelt slette radene.

Anta at du har datasettet som vist nedenfor, og du vil slette alle radene der regionen er Midt-Vest.

Nedenfor er trinnene for å gjøre dette:

1. Velg hele datasettet
2. Klikk på kategorien Hjem
3. I redigeringsgruppen klikker du på "Finn og velg" -alternativet og klikker deretter på Finn (du kan også bruke hurtigtasten Control + F).
4. I dialogboksen Finn og erstatt skriver du inn teksten "Midtvesten" i feltet "Finn hva:".
5. Klikk på Finn alle. Dette vil umiddelbart vise deg alle forekomster av teksten Mid-West som Excel var i stand til å finne.
6. Bruk hurtigtasten Ctrl + A for å velge alle cellene som Excel fant. Du vil også kunne se alle de valgte cellene i datasettet.
7. Høyreklikk på en av de valgte cellene og klikk på Slett. Dette åpner dialogboksen Slett.
8. Velg alternativet "Hele raden"
9. Klikk OK.

Trinnene ovenfor vil slette alle cellene der regionverdien er Midt-vest.

Merk: Siden Find and Replace kan håndtere jokertegn, kan du bruke disse når du finner data i Excel. For eksempel, hvis du vil slette alle radene der regionen enten er Midt-Vest eller Sør-Vest, kan du bruke ‘*Vest‘Som teksten du finner i dialogboksen Finn og erstatt. Dette vil gi deg alle cellene der teksten slutter med ordet vest.

Slett alle rader med en tom celle

Hvis du vil slette alle radene der det er tomme celler, kan du enkelt gjøre dette med en innebygd funksjonalitet i Excel.

Det er Gå til spesialceller alternativ - som lar deg raskt velge alle de tomme cellene. Og når du har valgt alle de tomme cellene, er det veldig enkelt å slette disse.

Anta at du har datasettet som vist nedenfor, og jeg vil slette alle radene der jeg ikke har salgsverdien.

Nedenfor er trinnene for å gjøre dette:

1. Velg hele datasettet (A1: D16 i dette tilfellet).
2. trykk F5 nøkkel. Dette åpner dialogboksen "Gå til" (Du kan også få denne dialogboksen fra Hjem -> Redigering -> Finn og velg -> Gå til).
3. I dialogboksen "Gå til" klikker du på knappen Spesial. Dette åpner dialogboksen "Gå til spesiell"
4. Velg "Tomrom" i dialogboksen Gå til spesiell.
5. Klikk OK.

Trinnene ovenfor vil velge alle cellene som er tomme i datasettet.

Når du har valgt de tomme cellene, høyreklikker du på en av cellene og klikker på Slett.

I dialogboksen Slett velger du alternativet "Hele raden" og klikker OK. Dette vil slette alle radene som har tomme celler.

Hvis du er interessert i å lære mer om denne teknikken, skrev jeg en detaljert opplæring om hvordan du sletter rader med tomme celler. Den inkluderer metoden ‘Gå til spesiell’ samt en VBA -metode for å slette rader med tomme celler.

Filtrer og slett rader basert på celleverdi (ved bruk av VBA)

Den siste metoden jeg skal vise deg inkluderer en liten bit VBA.

Du kan bruke denne metoden hvis du ofte trenger å slette rader basert på en bestemt verdi i en kolonne. Du kan legge til VBA -koden en gang og legge den til din personlige makro -arbeidsbok. På denne måten vil den være tilgjengelig for bruk i alle Excel -arbeidsbøkene dine.

Denne koden fungerer på samme måte som filtermetoden som er dekket ovenfor (bortsett fra at dette gjør alle trinnene i backend og sparer deg for noen klikk).

Anta at du har datasettet som vist nedenfor, og du vil slette alle radene der regionen er Midt-Vest.

Nedenfor er VBA -koden som vil gjøre dette.

Sub DeleteRowsWithSpecificText () 'Kilde: https: //trumpexcel.com/delete-rows-based-on-cell-value/ ActiveCell.AutoFilter Field: = 2, Criteria1: = "Midt-vest" ActiveSheet.AutoFilter.Range.Offset (1, 0) .Rows.SpecialCells (xlCellTypeVisible) .Slett Slutt Sub

Koden ovenfor bruker VBA Autofilter-metoden til først å filtrere radene basert på de angitte kriteriene (som er ‘Midt-vest’), og deretter velge alle de filtrerte radene og slette den.

Vær oppmerksom på at jeg har brukt Offset i koden ovenfor for å sikre at overskriftsraden min ikke blir slettet.

Koden ovenfor fungerer ikke hvis dataene er i en Excel -tabell. Grunnen til dette er at Excel anser en Excel -tabell som et listeobjekt. Så hvis du vil slette rader i en tabell, må du endre koden litt (dekket senere i denne opplæringen).

Før du sletter radene, viser den deg en melding som vist nedenfor. Jeg synes dette er nyttig, ettersom det lar meg dobbeltsjekke den filtrerte raden før jeg sletter den.

Husk at når du sletter rader med VBA, kan du ikke angre denne endringen. Så bruk dette bare når du er sikker på at dette fungerer som du vil. Det er også en god idé å beholde en sikkerhetskopi av dataene hvis noe skulle gå galt.

Hvis dataene er i en Excel -tabell, bruker du koden nedenfor for å slette rader med en bestemt verdi 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: = "Midt-vest" Tbl.DataBodyRange.SpecialCells (xlCellTypeVisible). Slett slutten sub

Siden VBA anser Excel -tabellen som et listeobjekt (og ikke et område), måtte jeg endre koden tilsvarende.

Hvor legger jeg VBA -koden?

Denne koden må plasseres i VB Editor backend i en modul.

Nedenfor er trinnene som viser deg hvordan du gjør dette:

1. Åpne arbeidsboken der du vil legge til denne koden.
2. Bruk hurtigtasten ALT + F11 for å åpne VBA Editor -vinduet.
3. I dette VBA Editor -vinduet, til venstre, er det en "Project Explorer" -rute (som viser alle arbeidsbøkene og regnearkobjektene). Høyreklikk på et hvilket som helst objekt i arbeidsboken (der du vil at denne koden skal fungere), hold markøren over "Sett inn" og klikk deretter på "Modul". Dette vil legge til modulobjektet i arbeidsboken og også åpne modulkodevinduet til høyre
4. I modulvinduet (som vises til høyre), kopier og lim inn koden ovenfor.

Når du har koden i VB Editor, kan du kjøre koden ved å bruke en av metodene nedenfor (sørg for at du har valgt cellen i datasettet du vil kjøre denne koden på):

1. Velg en linje i koden og trykk på F5 -tasten.
2. Klikk på Kjør -knappen på verktøylinjen i VB Editor
3. Tilordne makroen til en knapp eller en figur og kjør den ved å klikke på den i selve regnearket
4. Legg den til i verktøylinjen for hurtig tilgang og kjør koden med et enkelt klikk.

Du kan lese alt om hvordan du kjører makrokoden i Excel i denne artikkelen.

Merk: Siden arbeidsboken inneholder en VBA-makrokode, må du lagre den i det makroaktiverte formatet (xlsm).