Ridade kustutamine Exceli lahtri väärtuse (või tingimuse) alusel (lihtne juhend)

Suurte andmekogumitega töötades võib tekkida vajadus ridu kiiresti kustutada, lähtudes selles olevate lahtrite väärtustest (või tingimusest).

Näiteks kaaluge järgmisi näiteid.

1. Teil on müügiesindaja andmed ja soovite kustutada kõik konkreetse piirkonna või toote kirjed.
2. Soovite kustutada kõik kirjed, mille müügi väärtus on alla 100.
3. Soovite kustutada kõik read, kus on tühi lahter.

Selle andmekassi koorimiseks Excelis on mitu võimalust.

Ridade kustutamise meetod sõltub sellest, kuidas teie andmed on üles ehitatud ja milline on lahtri väärtus või tingimus, mille alusel soovite need read kustutada.

Selles õpetuses näitan teile mitu võimalust Exceli ridade kustutamiseks lahtri väärtuse või tingimuse alusel.

Filtreerige read väärtuse/seisundi alusel ja seejärel kustutage see

Üks kiiremaid viise konkreetse väärtuse sisaldavate ridade kustutamiseks või teatud tingimuste täitmiseks on nende filtreerimine. Kui olete filtreeritud andmed saanud, saate kõik need read kustutada (ülejäänud read jäävad puutumata).

Exceli filter on üsna mitmekülgne ja saate filtreerida paljude kriteeriumide (nt teksti, numbrite, kuupäevade ja värvide) alusel

Vaatame kahte näidet, kus saate ridu filtreerida ja kustutada.

Kustutage read, mis sisaldavad konkreetset teksti

Oletame, et teil on allpool näidatud andmekogum ja soovite kustutada kõik read, kus piirkond asub Kesk-lääne pool (veerus B).

Kuigi selles väikeses andmekogumis saate need read käsitsi kustutada, on teie andmekogumid sageli tohutud, kus ridade käsitsi kustutamine pole valik.

Sellisel juhul saate filtreerida kõik kirjed, kus piirkond asub Kesk-Läänes, ja seejärel kustutada kõik need read (hoides samas ülejäänud read puutumata).

Allpool on toodud sammud ridade kustutamiseks väärtuse alusel (kõik Kesk-Lääne kirjed):

1. Valige andmekogumist suvaline lahter, millest soovite read kustutada
2. Klõpsake vahekaarti Andmed
3. Klõpsake rühmas „Sorteeri ja filtreeri” ikooni Filter. See rakendab filtrid kõigile andmestiku päise lahtritele
4. Klõpsake päise lahtris Piirkond ikooni Filter (see on väike allapoole suunatud kolmnurga ikoon lahtri paremas ülanurgas)
5. Tühistage kõigi muude valikute valik, välja arvatud suvand Kesk-Lääs (kiire viis seda teha on klõpsates suvandil Vali kõik ja seejärel valikul Kesk-Lääs). See filtreerib andmestiku ja näitab teile ainult Kesk-Lääne piirkonna kirjeid.
6. Valige kõik filtreeritud kirjed
7. Paremklõpsake mõnda valitud lahtrit ja klõpsake nuppu „Kustuta rida”
8. Avanevas dialoogiboksis klõpsake nuppu OK. Sel hetkel ei näe andmestikus kirjeid.
9. Klõpsake vahekaarti Andmed ja klõpsake ikooni Filter. See eemaldab filtri ja näete kõiki kirjeid, välja arvatud kustutatud.

Ülaltoodud sammud filtreerivad kõigepealt andmed lahtri väärtuse alusel (või võivad need olla muud tingimused, näiteks pärast/enne kuupäeva või suuremat/väiksemat numbrit). Kui olete kirjed saanud, kustutate need lihtsalt.

Mõned kasulikud otseteed protsessi kiirendamiseks:

1. Control + tõstuklahv + L filtri paigaldamiseks või eemaldamiseks
2. Control + - (hoidke all juhtklahvi ja vajutage miinusklahvi) valitud lahtrite/ridade kustutamiseks

Ülaltoodud näites oli mul ainult neli erinevat piirkonda ja ma sain selle filtrite loendist käsitsi valida ja valiku tühistada (ülaltoodud sammudes 5).

Kui teil on palju kategooriaid/piirkondi, võite sisestada nime kasti kohal olevale väljale (millel on need piirkondade nimed) ja Excel näitab teile ainult neid kirjeid, mis vastavad sisestatud tekstile (nagu allpool näidatud). Kui teil on tekst, mille alusel soovite filtreerida, vajutage sisestusklahvi.

Pange tähele, et rea kustutamisel kaob kõik, mis nende ridade teistes lahtrites võib olla. Üks võimalus sellest mööda pääseda on luua koopia teistest töölehtedest ja kustutada kopeeritud andmete read. Kui see on tehtud, kopeerige see algandmete asemele tagasi.

Või

Saate kasutada selles juhendis hiljem näidatud meetodeid (kasutades sortimismeetodit või meetodit Leia kõik)

Kustuta read numbrilise seisundi alusel

Nii nagu ma kasutasin filtrimeetodit, et kustutada kõik read, mis sisaldavad teksti Mid-West, saate kasutada ka numbritingimust (või kuupäeva tingimust).

Oletame näiteks, et mul on allpool olev andmekogum ja ma tahan kustutada kõik read, mille müügiväärtus on väiksem kui 200.

Allpool on toodud sammud selle tegemiseks.

1. Valige andmetes ükskõik milline lahter
2. Klõpsake vahekaarti Andmed
3. Klõpsake rühmas „Sorteeri ja filtreeri” ikooni Filter. See rakendab filtrid kõigile andmestiku päise lahtritele
4. Klõpsake müügi päise lahtris filtriikooni (see on väike allapoole suunatud kolmnurga ikoon lahtri paremas ülanurgas)
5. Hõljutage kursorit valiku Number Filters kohal. See näitab teile Excelis kõiki numbritega seotud filtrivalikuid.
6. Klõpsake valikul „Vähem kui”.
7. Avanevas dialoogiboksis „Kohandatud automaatne filter” sisestage väljale väärtus „200”
8. Klõpsake nuppu OK. See filtreerib ja näitab ainult neid kirjeid, mille müügiväärtus on alla 200
9. Valige kõik filtreeritud kirjed
10. Paremklõpsake mõnda lahtrit ja klõpsake nuppu Kustuta rida
11. Avanevas dialoogiboksis klõpsake nuppu OK. Sel hetkel ei näe andmestikus kirjeid.
12. Klõpsake vahekaarti Andmed ja klõpsake ikooni Filter. See eemaldab filtri ja näete kõiki kirjeid, välja arvatud kustutatud.

Excelis saate kasutada palju numbrifiltreid - näiteks vähem kui/suurem, võrdne/ei võrdu, 10 parima vahel, üle või alla keskmise jne.

Märkus. Võite kasutada ka mitut filtrit. Näiteks saate kustutada kõik read, mille müügiväärtus on suurem kui 200, kuid alla 500. Sellisel juhul peate kasutama kahte filtritingimust. Kohandatud automaatse filtri dialoogiboks võimaldab kasutada kahte filtrikriteeriumi (JA samuti VÕI).

Nii nagu numbrifiltrid, saate kirjeid filtreerida ka kuupäeva alusel. Näiteks kui soovite eemaldada kõik esimese kvartali kirjed, saate seda teha ülaltoodud samme kasutades. Kuupäevafiltritega töötades kuvab Excel teile automaatselt asjakohased filtrid (nagu allpool näidatud).

Kuigi filtreerimine on suurepärane võimalus väärtuste või tingimuste põhjal ridu kiiresti kustutada, on sellel üks puudus - see kustutab kogu rea. Näiteks allpool oleval juhul kustutaks see kõik andmed, mis asuvad filtreeritud andmestikust paremal.

Mis siis, kui ma tahan kustutada andmestikust ainult kirjeid, kuid soovin allesjäänud andmed puutumatuna hoida.

Filtreerimisega ei saa seda teha, aga sorteerimisega.

Sorteerige andmekogum ja seejärel kustutage read

Kuigi sortimine on veel üks viis väärtuste põhjal ridade kustutamiseks, on enamikul juhtudel parem kasutada ülalkirjeldatud filtreerimismeetodit.

Seda sorteerimistehnikat soovitatakse kasutada ainult siis, kui soovite kustutada väärtustega lahtrid, mitte terveid ridu.

Oletame, et teil on allpool näidatud andmekogum ja soovite kustutada kõik kirjed, kus piirkond asub Kesk-Läänes.

Sorteerimise abil saate seda teha järgmiselt.

1. Valige andmetes ükskõik milline lahter
2. Klõpsake vahekaarti Andmed
3. Klõpsake rühmas Sortimine ja filtreerimine ikooni Sortimine.
4. Avanevas dialoogiboksis Sortimine valige veerus sorteerimine piirkond.
5. Veenduge, et suvandis Sortimine oleks valitud Lahtrite väärtused
6. Valige suvandis Tellimine A kuni Z (või Z kuni A, pole tegelikult oluline).
7. Klõpsake nuppu OK. See annab teile sorteeritud andmekogumi, nagu on näidatud allpool (sortitud veeru B järgi).
8. Valige kõik kirjed piirkonnaga Kesk-Lääs (kõikide ridade lahtrid, mitte ainult piirkonna veerg)
9. Kui see on valitud, paremklõpsake ja seejärel klõpsake nuppu Kustuta. See avab dialoogiboksi Kustuta.
10. Veenduge, et oleks valitud suvand „Lahtrite nihutamine üles”.
11. Klõpsake nuppu OK.

Ülaltoodud toimingud kustutaksid kõik kirjed, kus piirkond oli Kesk-Lääs, kuid see ei kustuta tervet rida. Seega, kui teil on andmekogumi paremal või vasakul andmeid, jääb see kahjustamata.

Ülaltoodud näites olen andmed sorteerinud lahtri väärtuse alusel, kuid samu samme saate kasutada ka sortimiseks numbrite, kuupäevade, lahtri- või fondivärvi jne alusel.

Siin on üksikasjalik juhend Exceli andmete sortimise kohta

Kui soovite säilitada esialgse andmekogumi järjekorra, kuid eemaldada kirjed kriteeriumide alusel, peab teil olema võimalus sortida andmed tagasi algsele. Selleks lisage enne andmete sortimist veerg seerianumbritega. Kui olete ridade/kirjete kustutamise lõpetanud, sorteerige lihtsalt selle lisatud lisaveeru abil.

Leidke ja valige lahtrid lahtri väärtuse alusel ja seejärel kustutage read

Excelil on funktsioon Otsi ja asenda, mis võib olla suurepärane, kui soovite leida ja valida kindla väärtusega lahtreid.

Kui olete need lahtrid valinud, saate ridu hõlpsalt kustutada.

Oletame, et teil on allpool näidatud andmekogum ja soovite kustutada kõik read, kus piirkond asub Kesk-Läänes.

Allpool on toodud sammud selle tegemiseks.

1. Valige kogu andmekogum
2. Klõpsake vahekaarti Avaleht
3. Klõpsake rühmas Redigeerimine valikut „Otsi ja vali” ja seejärel nuppu Otsi (saate kasutada ka kiirklahvi Control + F).
4. Sisestage dialoogiboksis Otsi ja asenda väljale „Leia:” tekst „Kesk-Lääs”.
5. Klõpsake nuppu Leia kõik. See näitab teile koheselt kõiki Kesk-Lääne teksti eksemplare, mida Excel suutis leida.
6. Kasutage klaviatuuri otseteed Control + A, et valida kõik lahtrid, mille Excel leidis. Samuti saate näha kõiki andmestiku valitud lahtreid.
7. Paremklõpsake mõnda valitud lahtrit ja klõpsake nuppu Kustuta. See avab dialoogiboksi Kustuta.
8. Valige suvand „Kogu rida”
9. Klõpsake nuppu OK.

Ülaltoodud sammud kustutaksid kõik lahtrid, kus piirkonna väärtus on Kesk-lääs.

Märkus. Kuna otsi ja asenda saab hakkama metamärkidega, saate neid Excelis andmete leidmisel kasutada. Näiteks kui soovite kustutada kõik read, kus piirkond asub kas Kesk- või Edela-piirkonnas, võite kasutada*Lääs„Tekstina, mida otsida dialoogiboksist Otsi ja asenda. See annab teile kõik lahtrid, kus tekst lõpeb sõnaga West.

Kustutage kõik read tühja lahtriga

Kui soovite kustutada kõik read, kus on tühjad lahtrid, saate seda hõlpsalt teha Exceli sisseehitatud funktsionaalsusega.

See on Mine spetsiaalsetesse rakkudesse suvand - mis võimaldab kiiresti valida kõik tühjad lahtrid. Ja kui olete kõik tühjad lahtrid valinud, on nende kustutamine ülilihtne.

Oletame, et teil on allpool näidatud andmekogum ja ma tahan kustutada kõik read, kus mul pole müügiväärtust.

Allpool on toodud sammud selle tegemiseks.

1. Valige kogu andmekogum (antud juhul A1: D16).
2. Vajutage F5 võti. See avab dialoogiboksi „Mine” (selle dialoogiboksi saate ka avalehelt -> redigeerimine -> otsi ja vali -> mine).
3. Klõpsake dialoogiboksis „Mine” nuppu Eriline. See avab dialoogiboksi „Mine erile”
4. Valige dialoogiboksis Mine eriliiki „Toorikud”.
5. Klõpsake nuppu OK.

Ülaltoodud toimingud valiksid kõik andmestiku tühjad lahtrid.

Kui olete tühjad lahtrid valinud, paremklõpsake mõnda lahtrit ja klõpsake nuppu Kustuta.

Valige dialoogiboksis Kustuta suvand „Kogu rida” ja klõpsake nuppu OK. See kustutab kõik read, millel on tühjad lahtrid.

Kui soovite selle tehnika kohta rohkem teada saada, kirjutasin üksikasjaliku õpetuse tühjade lahtritega ridade kustutamiseks. See sisaldab meetodit „Mine erile” ja VBA -meetodit tühjade lahtritega ridade kustutamiseks.

Filtreeri ja kustuta ridu lahtri väärtuse alusel (kasutades VBA -d)

Viimane meetod, mida ma teile näitan, sisaldab natuke VBA -d.

Seda meetodit saate kasutada, kui peate sageli veergude kindla väärtuse põhjal ridu kustutama. Saate lisada VBA koodi üks kord ja lisada selle oma isikliku makro töövihikusse. Nii on see saadaval kasutamiseks kõigis teie Exceli töövihikutes.

See kood töötab samamoodi nagu ülalkirjeldatud filtreerimismeetod (välja arvatud asjaolu, et see teeb kõik taustaprogrammi toimingud ja säästab mõningaid klikke).

Oletame, et teil on allpool näidatud andmekogum ja soovite kustutada kõik read, kus piirkond asub Kesk-Läänes.

Allpool on VBA kood, mis seda teeb.

Sub DeleteRowsWithSpecificText () 'Allikas: https: //trumpexcel.com/delete-rows-based-on-cell-value/ ActiveCell.AutoFilter Field: = 2, Criteria1: = "Mid-West" ActiveSheet.AutoFilter.Range.Offset (1, 0). Rivid.SpecialCells (xlCellTypeVisible). Kustuta lõpp -alam

Ülaltoodud kood kasutab VBA automaatse filtri meetodit, et esmalt filtreerida read määratud kriteeriumide alusel (mis on „Mid-West”), seejärel valida kõik filtreeritud read ja see kustutada.

Pange tähele, et olen ülaltoodud koodis kasutanud Offsetit, et veenduda, et minu päise rida ei kustutata.

Ülaltoodud kood ei tööta, kui teie andmed on Exceli tabelis. Selle põhjuseks on asjaolu, et Excel peab Exceli tabelit loendiobjektiks. Seega, kui soovite kustutada tabelis olevaid ridu, peate koodi veidi muutma (seda käsitletakse selles juhendis hiljem).

Enne ridade kustutamist kuvatakse teile viip, nagu allpool näidatud. Minu arvates on see kasulik, kuna see võimaldab mul enne kustutamist filtreeritud rida uuesti kontrollida.

Pidage meeles, et kui kustutate ridu VBA abil, ei saa te seda muudatust tagasi võtta. Nii et kasutage seda ainult siis, kui olete kindel, et see töötab soovitud viisil. Samuti on hea mõte hoida andmetest varukoopia igaks juhuks, kui midagi valesti läheb.

Kui teie andmed on Exceli tabelis, kasutage konkreetse väärtusega ridade kustutamiseks allolevat koodi:

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

Kuna VBA peab Exceli tabelit loendiobjektiks (mitte vahemikuks), pidin koodi vastavalt muutma.

Kuhu VBA kood panna?

See kood tuleb paigutada mooduli VB Editori taustaprogrammi.

Allpool on toodud sammud, mis näitavad teile, kuidas seda teha.

1. Avage töövihik, kuhu soovite selle koodi lisada.
2. Kasutage VBA redaktori akna avamiseks kiirklahvi ALT + F11.
3. Selles VBA redaktori aknas vasakul on paan „Project Explorer” (kus on loetletud kõik töövihikud ja töölehtede objektid). Paremklõpsake töövihiku mis tahes objektil (milles soovite, et see kood töötaks), hõljutage kursorit nupu „Lisa” kohal ja seejärel klõpsake nuppu „Moodul”. See lisab töövihikusse objekti Moodul ja avab paremal ka mooduli koodi akna
4. Kopeerige ja kleepige ülaltoodud kood mooduli aknas (mis kuvatakse paremal).

Kui olete koodi VB -redaktorisse lisanud, saate koodi käivitada, kasutades mõnda järgmistest meetoditest (veenduge, et olete valinud selle andmekogumi mis tahes lahtri, millel soovite seda koodi käivitada):

1. Valige koodist mis tahes rida ja vajutage klahvi F5.
2. Klõpsake VB redaktori tööriistaribal nuppu Käivita
3. Määrake makro nupule või kujundile ja käivitage see, klõpsates sellel töölehel
4. Lisage see kiirpääsu tööriistaribale ja käivitage kood ühe klõpsuga.

Sellest artiklist saate lugeda, kuidas makrokoodi Excelis käivitada.

Märkus. Kuna töövihik sisaldab VBA makrokoodi, peate selle salvestama makrofunktsiooniga vormingus (xlsm).