Brisanje vrstic na podlagi vrednosti celice (ali stanja) v Excelu (enostaven vodnik)

Pri delu z velikimi nabori podatkov boste morda morali hitro izbrisati vrstice na podlagi vrednosti celic v njej (ali na podlagi pogoja).

Na primer, razmislite o naslednjih primerih:

1. Imate podatke o prodajnih predstavnikih in želite izbrisati vse zapise za določeno regijo ali izdelek.
2. Želite izbrisati vse zapise, pri katerih je prodajna vrednost manjša od 100.
3. Želite izbrisati vse vrstice, kjer je prazna celica.

Ta podatkovna mačka v Excelu lahko odlepite na več načinov.

Način brisanja vrstic je odvisen od tega, kako so podatki strukturirani in kakšna je vrednost celice ali pogoj, na podlagi katerega želite izbrisati te vrstice.

V tej vadnici vam bom pokazal več načinov brisanja vrstic v Excelu glede na vrednost celice ali pogoj.

Filtrirajte vrstice glede na vrednost/stanje in jih nato izbrišite

Eden najhitrejših načinov za brisanje vrstic, ki vsebujejo določeno vrednost ali izpolnjujejo dani pogoj, je njihovo filtriranje. Ko filtrirate podatke, lahko izbrišete vse te vrstice (preostale pa ostanejo nedotaknjene).

Excelov filter je vsestranski in ga lahko filtrirate na podlagi številnih meril (na primer besedila, številk, datumov in barv)

Poglejmo dva primera, kjer lahko filtrirate vrstice in jih izbrišete.

Izbriši vrstice, ki vsebujejo določeno besedilo

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite izbrisati vse vrstice, kjer je regija Srednji zahod (v stolpcu B).

Čeprav lahko v tem majhnem naboru podatkov ročno izbrišete te vrstice, bodo vaši nabori podatkov pogosto veliki, kjer ročno brisanje vrstic ne bo možno.

V tem primeru lahko filtrirate vse zapise, kjer je regija Srednji zahod, in nato izbrišete vse te vrstice (ostale vrstice pa ostanejo nedotaknjene).

Spodaj so navedeni koraki za brisanje vrstic na podlagi vrednosti (vsi zapisi na Srednjem zahodu):

1. Izberite katero koli celico v nizu podatkov, iz katere želite izbrisati vrstice
2. Kliknite zavihek Podatki
3. V skupini »Razvrsti in filtriraj« kliknite ikono Filtriraj. S tem bodo filtri uporabljeni za vse celice glav v naboru podatkov
4. Kliknite ikono Filtriraj v celici z glavo regije (to je majhna ikona trikotnika navzdol v zgornjem desnem kotu celice)
5. Prekličite izbiro vseh drugih možnosti, razen možnosti Srednji zahod (hiter način za to je, da kliknete možnost Izberi vse in nato možnost Srednji zahod). To bo filtriralo nabor podatkov in prikazalo samo zapise za regijo Srednjega zahoda.
6. Izberite vse filtrirane zapise
7. Z desno miškino tipko kliknite katero koli od izbranih celic in kliknite »Izbriši vrstico«
8. V pogovornem oknu, ki se odpre, kliknite V redu. Na tej točki v nizu podatkov ne boste videli nobenih zapisov.
9. Kliknite zavihek Podatki in kliknite ikono Filter. S tem boste odstranili filter in videli boste vse zapise, razen izbrisanih.

Zgornji koraki najprej filtrirajo podatke glede na vrednost celice (ali pa so lahko drugi pogoji, na primer za/pred datumom ali večji/manjši od števila). Ko imate zapise, jih preprosto izbrišete.

Nekaj ​​koristnih bližnjic za pospešitev postopka:

1. Control + Shift + L uporabiti ali odstraniti filter
2. Control + - (držite tipko za upravljanje in pritisnite tipko minus), če želite izbrisati izbrane celice/vrstice

V zgornjem primeru sem imel samo štiri različne regije in sem jih lahko ročno izbral in preklical izbor s seznama filtrov (v zgornjih korakih 5).

Če imate veliko kategorij/regij, lahko vnesete ime v polje tik nad poljem (ki ima ta imena regij) in Excel vam bo pokazal samo tiste zapise, ki se ujemajo z vnesenim besedilom (kot je prikazano spodaj). Ko imate besedilo, ki ga želite filtrirati, pritisnite tipko Enter.

Upoštevajte, da bo pri brisanju vrstice izgubljeno vse, kar imate v drugih celicah v teh vrsticah. Eden od načinov za rešitev tega je ustvarjanje kopije podatkov na drugem delovnem listu in brisanje vrstic v kopiranih podatkih. Ko končate, ga kopirajte namesto prvotnih podatkov.

Or

Uporabite lahko metode, prikazane pozneje v tej vadnici (z uporabo metode razvrščanja ali metode Najdi vse)

Brisanje vrstic na podlagi številskega pogoja

Tako kot sem z metodo filtriranja izbrisal vse vrstice, ki vsebujejo besedilo Srednji zahod, lahko uporabite tudi številčni pogoj (ali datumski pogoj).

Recimo, da imam spodnji nabor podatkov in želim izbrisati vse vrstice, kjer je prodajna vrednost manjša od 200.

Spodaj so navedeni koraki za to:

1. Izberite katero koli celico v podatkih
2. Kliknite zavihek Podatki
3. V skupini »Razvrsti in filtriraj« kliknite ikono Filtriraj. S tem bodo filtri uporabljeni za vse celice glav v naboru podatkov
4. Kliknite ikono Filtriraj v celici glave prodaje (to je majhna ikona trikotnika navzdol v zgornjem desnem kotu celice)
5. Premaknite kazalec na možnost Številčni filtri. To vam bo prikazalo vse možnosti filtriranja, povezane s številkami, v Excelu.
6. Kliknite možnost »Manj kot«.
7. V pogovornem oknu »Samodejni filter po meri«, ki se odpre, v polje vnesite vrednost »200«
8. Kliknite V redu. To bo filtriralo in prikazalo samo tiste zapise, pri katerih je vrednost prodaje manjša od 200
9. Izberite vse filtrirane zapise
10. Z desno miškino tipko kliknite katero koli celico in kliknite Izbriši vrstico
11. V pogovornem oknu, ki se odpre, kliknite V redu. Na tej točki v nizu podatkov ne boste videli nobenih zapisov.
12. Kliknite zavihek Podatki in kliknite ikono Filter. S tem boste odstranili filter in videli boste vse zapise, razen izbrisanih.

V Excelu lahko uporabite številne filtre številk - na primer manj kot/več kot, enako/ni enako, med, top 10, nad ali pod povprečjem itd.

Opomba: Uporabite lahko tudi več filtrov. Na primer, lahko izbrišete vse vrstice, kjer je prodajna vrednost večja od 200, vendar manjša od 500. V tem primeru morate uporabiti dva pogoja filtra. Pogovorno okno Samodejni filter po meri omogoča dva merila filtra (AND in OR).

Tako kot številske filtre lahko tudi zapise filtrirate glede na datum. Če želite na primer odstraniti vse zapise prvega četrtletja, lahko to storite z istimi zgornjimi koraki. Ko delate s filtri datumov, vam Excel samodejno prikaže ustrezne filtre (kot je prikazano spodaj).

Čeprav je filtriranje odličen način za hitro brisanje vrstic na podlagi vrednosti ali pogoja, ima eno pomanjkljivost - izbriše celotno vrstico. Na primer, v spodnjem primeru bi izbrisali vse podatke, ki so desno od filtriranega nabora podatkov.

Kaj pa, če želim samo izbrisati zapise iz nabora podatkov, vendar želim ohraniti preostale podatke nedotaknjene.

S filtriranjem tega ne morete storiti, lahko pa z razvrščanjem.

Razvrstite nabor podatkov in nato izbrišite vrstice

Čeprav je razvrščanje še en način za brisanje vrstic na podlagi vrednosti, je v večini primerov bolje uporabiti zgoraj opisano metodo filtriranja.

Ta tehnika razvrščanja je priporočljiva le, če želite izbrisati celice z vrednostmi in ne celotnih vrstic.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite izbrisati vse zapise, kjer je regija Srednji zahod.

Spodaj so navedeni koraki za to z razvrščanjem:

1. Izberite katero koli celico v podatkih
2. Kliknite zavihek Podatki
3. V skupini Razvrsti in filtriraj kliknite ikono Razvrsti.
4. V pogovornem oknu Razvrsti, ki se odpre, izberite Regija v stolpcu razvrsti po.
5. Pri možnosti Razvrsti pri, preverite, ali je izbrana Cell Values
6. V možnosti Naroči izberite od A do Z (ali od Z do A, v resnici ni pomembno).
7. Kliknite V redu. Tako boste dobili razvrščen niz podatkov, kot je prikazano spodaj (razvrščeno po stolpcu B).
8. Izberite vse zapise z regijo Srednji zahod (vse celice v vrsticah, ne le stolpec regije)
9. Ko ste izbrani, kliknite z desno tipko miške in nato kliknite Izbriši. S tem se odpre pogovorno okno Izbriši.
10. Prepričajte se, da je izbrana možnost »Premik celic navzgor«.
11. Kliknite V redu.

Zgornji koraki bi izbrisali vse zapise, kjer je bila regija Srednji zahod, vendar ne izbrišejo celotne vrstice. Torej, če imate podatke na desni ali levi strani nabora podatkov, bodo ostali nepoškodovani.

V zgornjem primeru sem podatke razvrstil glede na vrednost celice, vendar lahko z istimi koraki razvrstite tudi glede na številke, datume, barvo celice ali barvo pisave itd.

Tukaj je podroben vodnik o tem, kako razvrstiti podatke v Excelu

Če želite ohraniti izvirni vrstni red nabora podatkov, vendar zapise odstraniti na podlagi meril, morate imeti način, da podatke razvrstite nazaj v prvotno. Če želite to narediti, pred razvrščanjem podatkov dodajte stolpec s serijskimi številkami. Ko končate z brisanjem vrstic/zapisov, preprosto razvrstite na podlagi tega dodatnega stolpca, ki ste ga dodali.

Poiščite in izberite celice glede na vrednost celice in nato izbrišite vrstice

Excel ima funkcijo Najdi in zamenjaj, ki je lahko odlična, če želite poiskati in izbrati celice z določeno vrednostjo.

Ko izberete te celice, lahko vrstice enostavno izbrišete.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite izbrisati vse vrstice, kjer je regija Srednji zahod.

Spodaj so navedeni koraki za to:

1. Izberite celoten nabor podatkov
2. Kliknite zavihek Domov
3. V skupini Urejanje kliknite možnost »Poišči in izberi« in nato kliknite Najdi (uporabite lahko tudi bližnjico na tipkovnici Control + F).
4. V pogovornem oknu Najdi in zamenjaj v polje »Poišči kaj:« vnesite besedilo »Srednji zahod«.
5. Kliknite Poišči vse. To vam bo takoj pokazalo vse primere besedila Mid-West, ki jih je Excel našel.
6. Z bližnjico na tipkovnici Control + A izberite vse celice, ki jih je Excel našel. Prav tako boste lahko videli vse izbrane celice v naboru podatkov.
7. Z desno miškino tipko kliknite katero koli od izbranih celic in kliknite Izbriši. S tem se odpre pogovorno okno Izbriši.
8. Izberite možnost »Cela vrstica«
9. Kliknite V redu.

Zgornji koraki bi izbrisali vse celice, kjer je vrednost regije Srednji zahod.

Opomba: Ker funkcija Najdi in zamenjaj lahko obravnava nadomestne znake, jih lahko uporabite pri iskanju podatkov v Excelu. Na primer, če želite izbrisati vse vrstice, kjer je regija Srednji zahod ali Jugozahod, lahko uporabite »*Zahod"Kot besedilo v pogovornem oknu Najdi in zamenjaj. Tako boste dobili vse celice, kjer se besedilo konča z besedo West.

Izbriši vse vrstice s prazno celico

Če želite izbrisati vse vrstice, kjer so prazne celice, lahko to preprosto storite z vgrajeno funkcionalnostjo v Excelu.

To je Pojdi v posebne celice možnost - ki vam omogoča hiter izbor vseh praznih celic. Ko izberete vse prazne celice, jih je zelo preprosto izbrisati.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želim izbrisati vse vrstice, kjer nimam prodajne vrednosti.

Spodaj so navedeni koraki za to:

1. Izberite celoten nabor podatkov (v tem primeru A1: D16).
2. Pritisnite tipko F5 ključ. S tem se odpre pogovorno okno »Pojdi na« (To pogovorno okno lahko dobite tudi od Domov -> Urejanje -> Poišči in izberite -> Pojdi na).
3. V pogovornem oknu »Pojdi na« kliknite gumb Poseben. S tem se odpre pogovorno okno »Pojdi na posebno«
4. V pogovornem oknu Pojdi na posebno izberite »Praznine«.
5. Kliknite V redu.

Zgornji koraki bi izbrali vse prazne celice v naboru podatkov.

Ko izberete prazne celice, z desno miškino tipko kliknite katero koli celico in kliknite Izbriši.

V pogovornem oknu Izbriši izberite možnost »Cela vrstica« in kliknite V redu. S tem boste izbrisali vse vrstice, ki imajo prazne celice.

Če vas zanima več o tej tehniki, sem napisal podroben vodič o brisanju vrstic s praznimi celicami. Vključuje metodo »Pojdi na posebno« in tudi metodo VBA za brisanje vrstic s praznimi celicami.

Filtriranje in brisanje vrstic na podlagi vrednosti celice (z uporabo VBA)

Zadnja metoda, ki vam jo bom pokazal, vključuje malo VBA.

To metodo lahko uporabite, če morate pogosto izbrisati vrstice na podlagi določene vrednosti v stolpcu. Kodo VBA lahko dodate enkrat in jo dodate v svoj delovni zvezek Personal Macro. Tako bo na voljo za uporabo v vseh delovnih zvezkih programa Excel.

Ta koda deluje na enak način kot zgoraj opisana metoda filtriranja (razen dejstva, da to naredi vse korake v zaledju in vam prihrani nekaj klikov).

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite izbrisati vse vrstice, kjer je regija Srednji zahod.

Spodaj je koda VBA, ki bo to naredila.

Sub DeleteRowsWithSpecificText () 'Vir: 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

Zgornja koda uporablja metodo samodejnega filtriranja VBA, da najprej filtrira vrstice na podlagi podanih meril (kar je »Srednji zahod«), nato izbere vse filtrirane vrstice in jih izbriše.

Upoštevajte, da sem v zgornji kodi uporabil Offset, da se prepričam, da moja vrstica glave ni izbrisana.

Zgornja koda ne deluje, če so vaši podatki v Excelovi tabeli. Razlog za to je, da Excel obravnava Excelovo tabelo kot objekt seznama. Torej, če želite izbrisati vrstice, ki so v tabeli, morate kodo nekoliko spremeniti (obravnavano kasneje v tej vadnici).

Pred brisanjem vrstic vam bo prikazan poziv, kot je prikazano spodaj. To se mi zdi koristno, saj mi omogoča, da pred brisanjem dvakrat preverim filtrirano vrstico.

Ne pozabite, da pri brisanju vrstic z VBA te spremembe ne morete razveljaviti. Zato uporabite to le, če ste prepričani, da deluje tako, kot želite. Prav tako je dobro, da shranite varnostno kopijo podatkov, samo če gre kaj narobe.

Če so vaši podatki v Excelovi tabeli, uporabite spodnjo kodo za brisanje vrstic z določeno vrednostjo:

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

Ker VBA meni Excelovo tabelo kot objekt seznama (in ne obseg), sem moral kodo ustrezno spremeniti.

Kam vstaviti kodo VBA?

To kodo je treba postaviti v ozadje urejevalnika VB v modulu.

Spodaj so navedeni koraki, ki vam bodo pokazali, kako to storiti:

1. Odprite delovni zvezek, v katerega želite dodati to kodo.
2. Z bližnjico na tipkovnici ALT + F11 odprite okno urejevalnika VBA.
3. V tem oknu urejevalnika VBA na levi strani je podokno »Raziskovalec projektov« (v katerem so navedeni vsi delovni zvezki in predmeti delovnih listov). Z desno miškino tipko kliknite kateri koli predmet v delovnem zvezku (v katerem želite, da ta koda deluje), premaknite kazalec na "Vstavi" in nato kliknite "Modul". To bo dodalo predmet Modul v delovni zvezek in odprlo tudi okno kode modula na desni
4. V oknu modula (ki se prikaže na desni) kopirajte in prilepite zgornjo kodo.

Ko imate kodo v urejevalniku VB, jo lahko zaženete s katero koli od spodnjih metod (preverite, ali ste izbrali katero koli celico v naboru podatkov, na kateri želite zagnati to kodo):

1. Izberite poljubno vrstico v kodi in pritisnite tipko F5.
2. V orodni vrstici v urejevalniku VB kliknite gumb Zaženi
3. Dodelite makro gumbu ali obliki in ga zaženite tako, da ga kliknete na samem delovnem listu
4. Dodajte ga v orodno vrstico za hitri dostop in kodo zaženite z enim klikom.

V tem članku lahko preberete vse o tem, kako zaženete kodo makra v Excelu.

Opomba: Ker delovni zvezek vsebuje kodo makra VBA, jo morate shraniti v obliki, ki omogoča makro (xlsm).