Brisanje redaka na temelju vrijednosti ćelije (ili stanja) u Excelu (jednostavan vodič)

Prilikom rada s velikim skupovima podataka možda ćete morati brzo izbrisati retke na temelju vrijednosti ćelija u njemu (ili na temelju uvjeta).

Na primjer, razmotrite sljedeće primjere:

1. Imate podatke prodajnog predstavnika i želite izbrisati sve zapise za određenu regiju ili proizvod.
2. Želite izbrisati sve zapise u kojima je prodajna vrijednost manja od 100.
3. Želite izbrisati sve retke u kojima postoji prazna ćelija.

Postoji nekoliko načina da skinite ovu podatkovnu mačku u Excel.

Metoda koju odaberete za brisanje redaka ovisit će o tome kako su vaši podaci strukturirani i koja je vrijednost ćelije ili uvjet na temelju kojih želite izbrisati ove retke.

U ovom vodiču pokazat ću vam više načina za brisanje redaka u Excelu na temelju vrijednosti ćelije ili uvjeta.

Filtrirajte redove na temelju vrijednosti/stanja, a zatim ih izbrišite

Jedan od najbržih načina brisanja redaka koji sadrže određenu vrijednost ili ispunjavaju zadani uvjet je njihovo filtriranje. Nakon što filtrirate podatke, možete izbrisati sve ove retke (dok preostali retci ostaju netaknuti).

Excel filtar je prilično svestran i možete filtrirati na temelju mnogih kriterija (poput teksta, brojeva, datuma i boja)

Pogledajmo dva primjera gdje možete filtrirati retke i izbrisati ih.

Izbriši retke koji sadrže određeni tekst

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite izbrisati sve retke u kojima je regija Srednji zapad (u stupcu B).

Iako u ovom malom skupu podataka možete odabrati brisanje ovih redaka ručno, često će vaši skupovi podataka biti ogromni gdje ručno brisanje redaka neće biti opcija.

U tom slučaju možete filtrirati sve zapise u kojima je regija Srednji zapad, a zatim izbrisati sve te retke (a ostale retke zadržati netaknutima).

U nastavku su navedeni koraci za brisanje redaka na temelju vrijednosti (svi zapisi na Srednjem zapadu):

1. Odaberite bilo koju ćeliju u skupu podataka iz koje želite izbrisati retke
2. Kliknite karticu Podaci
3. U grupi ‘Sortiraj i filtriraj’ kliknite ikonu Filtriraj. To će primijeniti filtre na sve ćelije zaglavlja u skupu podataka
4. Kliknite ikonu Filtriraj u ćeliji zaglavlja regije (ovo je mala ikona trokuta usmjerena prema dolje u gornjem desnom kutu ćelije)
5. Poništite odabir svih drugih opcija osim opcije Mid-West (brz način da to učinite je klikom na opciju Select All, a zatim klikom na opciju Mid-West). Ovo će filtrirati skup podataka i prikazati samo zapise za regiju Srednjeg zapada.
6. Odaberite sve filtrirane zapise
7. Desnom tipkom miša kliknite bilo koju od odabranih ćelija i kliknite "Izbriši redak"
8. U dijaloškom okviru koji se otvori kliknite U redu. U ovom trenutku nećete vidjeti zapise u skupu podataka.
9. Kliknite karticu Podaci i kliknite ikonu Filter. Time ćete ukloniti filtar i vidjet ćete sve zapise osim izbrisanih.

Gore navedeni koraci prvo filtriraju podatke na temelju vrijednosti ćelije (ili mogu biti drugi uvjeti, primjerice nakon/prije datuma ili veći/manji od broja). Nakon što imate zapise, jednostavno ih brišete.

Neki korisni prečaci koje trebate znati za ubrzanje procesa:

1. Control + Shift + L za primjenu ili uklanjanje filtra
2. Kontrola + - (držite kontrolnu tipku i pritisnite tipku minus) da biste izbrisali odabrane ćelije/retke

U gornjem primjeru imao sam samo četiri različite regije i mogao sam ga ručno odabrati i poništiti odabir s popisa filtera (u gore navedenim koracima 5).

U slučaju da imate mnogo kategorija/regija, možete upisati naziv u polje desno iznad okvira (koje ima te nazive regija), a Excel će vam pokazati samo one zapise koji odgovaraju unesenom tekstu (kao što je prikazano u nastavku). Kada dobijete tekst na temelju kojeg želite filtrirati, pritisnite tipku Enter.

Imajte na umu da će se prilikom brisanja retka izgubiti sve što imate u drugim ćelijama u tim redovima. Jedan od načina da to zaobiđete je stvaranje kopije podataka na drugom radnom listu i brisanje redaka u kopiranim podacima. Kad završite, kopirajte ga na mjesto izvornih podataka.

Ili

Možete koristiti metode prikazane kasnije u ovom vodiču (pomoću metode Sortiranje ili Metode pronađi sve)

Brisanje redaka na temelju numeričkog uvjeta

Baš kao što sam metodom filtriranja izbrisao sve retke koji sadrže tekst Srednji zapad, možete upotrijebiti i brojčani uvjet (ili uvjet datuma).

Na primjer, pretpostavimo da imam niz podataka u nastavku i želim izbrisati sve retke u kojima je prodajna vrijednost manja od 200.

U nastavku su navedeni koraci za to:

1. Odaberite bilo koju ćeliju u podacima
2. Kliknite karticu Podaci
3. U grupi ‘Sortiraj i filtriraj’ kliknite ikonu Filtriraj. To će primijeniti filtre na sve ćelije zaglavlja u skupu podataka
4. Kliknite ikonu Filtriraj u ćeliji zaglavlja prodaje (ovo je mala ikona trokuta usmjerena prema dolje u gornjem desnom kutu ćelije)
5. Zadržite pokazivač iznad opcije Filteri brojeva. Ovo će vam pokazati sve mogućnosti filtriranja povezane s brojevima u Excelu.
6. Kliknite opciju "Manje od".
7. U dijaloški okvir "Prilagođeni autofilter" koji se otvori unesite vrijednost "200" u polje
8. Pritisnite U redu. To će filtrirati i prikazati samo one zapise u kojima je prodajna vrijednost manja od 200
9. Odaberite sve filtrirane zapise
10. Desnom tipkom miša kliknite bilo koju ćeliju i kliknite Izbriši redak
11. U dijaloškom okviru koji se otvori kliknite U redu. U ovom trenutku nećete vidjeti zapise u skupu podataka.
12. Kliknite karticu Podaci i kliknite ikonu Filter. Time ćete ukloniti filtar i vidjet ćete sve zapise osim izbrisanih.

Postoji mnogo filtera s brojevima koje možete koristiti u Excelu - kao što su manje od/veće od, jednako/nije jednako, između, prvih 10, iznad ili ispod prosjeka itd.

Napomena: Možete koristiti i više filtera. Na primjer, možete izbrisati sve retke u kojima je prodajna vrijednost veća od 200, ali manja od 500. U ovom slučaju morate koristiti dva uvjeta filtra. Dijaloški okvir Prilagođeni autofilter omogućuje dva kriterija filtra (I kao i ILI).

Baš kao i filtri s brojevima, možete i filtrirati zapise na temelju datuma. Na primjer, ako želite ukloniti sve zapise prvog tromjesečja, to možete učiniti pomoću istih gore navedenih koraka. Kada radite s filterima datuma, Excel vam automatski prikazuje relevantne filtre (kao što je prikazano u nastavku).

Iako je filtriranje odličan način za brzo brisanje redaka na temelju vrijednosti ili uvjeta, ima jedan nedostatak - briše cijeli redak. Na primjer, u donjem će slučaju izbrisati sve podatke koji se nalaze desno od filtriranog skupa podataka.

Što ako samo želim izbrisati zapise iz skupa podataka, ali želim zadržati preostale podatke netaknutim.

Ne možete to učiniti filtriranjem, ali to možete učiniti sortiranjem.

Sortirajte skup podataka, a zatim izbrišite retke

Iako je sortiranje još jedan način brisanja redaka na temelju vrijednosti, u većini slučajeva bolje je koristiti gore opisanu metodu filtriranja.

Ova tehnika razvrstavanja preporučuje se samo kada želite izbrisati ćelije s vrijednostima, a ne cijele retke.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite izbrisati sve zapise gdje je regija Srednji zapad.

U nastavku su navedeni koraci za to pomoću sortiranja:

1. Odaberite bilo koju ćeliju u podacima
2. Kliknite karticu Podaci
3. U grupi Sortiranje i filtriranje kliknite ikonu Sortiraj.
4. U dijaloškom okviru Sortiranje koji se otvori odaberite Regija u stupcu razvrstavanje po.
5. U opciji Sortiraj uključeno provjerite je li odabrana vrijednost ćelija
6. U opciji Narudžba odaberite A do Z (ili Z do A, zapravo nije važno).
7. Pritisnite U redu. To će vam dati razvrstani skup podataka kao što je prikazano u nastavku (sortirano prema stupcu B).
8. Odaberite sve zapise s regijom Srednji zapad (sve ćelije u redovima, a ne samo stupac regije)
9. Nakon odabira kliknite desnom tipkom miša, a zatim kliknite Izbriši. Ovo će otvoriti dijaloški okvir Izbriši.
10. Provjerite je li odabrana opcija "Pomicanje ćelija prema gore".
11. Pritisnite U redu.

Gore navedeni koraci izbrisali bi sve zapise gdje je regija bila Srednji zapad, ali ne brišu cijeli redak. Dakle, ako imate podatke s desne ili lijeve strane vašeg skupa podataka, oni će ostati neozlijeđeni.

U gornjem primjeru razvrstao sam podatke na temelju vrijednosti ćelije, ali iste korake možete koristiti i za sortiranje na temelju brojeva, datuma, boje ćelije ili boje fonta itd.

Evo detaljnog vodiča o sortiranju podataka u Excelu

U slučaju da želite zadržati izvorni redoslijed skupova podataka, ali ukloniti zapise na temelju kriterija, morate imati način da podatke sortirate natrag prema izvornom. Da biste to učinili, prije sortiranja podataka dodajte stupac sa serijskim brojevima. Kada završite s brisanjem redaka/zapisa, jednostavno ih sortirajte pomoću ovog dodatnog stupca koji ste dodali.

Pronađite i odaberite ćelije na temelju vrijednosti ćelije, a zatim izbrišite retke

Excel ima funkciju Pronađi i zamijeni koja može biti izvrsna kada želite pronaći i odabrati ćelije određene vrijednosti.

Nakon što odaberete ove ćelije, retke možete jednostavno izbrisati.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite izbrisati sve retke u kojima je regija Srednji zapad.

U nastavku su navedeni koraci za to:

1. Odaberite cijeli skup podataka
2. Kliknite karticu Početna
3. U grupi Uređivanje kliknite opciju ‘Pronađi i odaberi’, a zatim kliknite Nađi (možete koristiti i tipkovnički prečac Control + F).
4. U dijaloški okvir Pronađi i zamijeni unesite tekst "Srednji zapad" u polje "Pronađi što:".
5. Kliknite na Pronađi sve. Ovo će vam trenutno pokazati sve primjere teksta Mid-West koje je Excel uspio pronaći.
6. Pomoću tipkovnog prečaca Control + A odaberite sve ćelije koje je Excel pronašao. Također ćete moći vidjeti sve odabrane ćelije u skupu podataka.
7. Desnom tipkom miša kliknite bilo koju od odabranih ćelija i kliknite Izbriši. Ovo će otvoriti dijaloški okvir Izbriši.
8. Odaberite opciju "Cijeli red"
9. Pritisnite U redu.

Gore navedeni koraci izbrisali bi sve ćelije u kojima je vrijednost regije Srednji zapad.

Napomena: Budući da značajka Nađi i zamijeni može rukovati zamjenskim znakovima, možete ih koristiti pri pronalaženju podataka u Excelu. Na primjer, ako želite izbrisati sve retke u kojima je regija Srednji zapad ili Jugozapad, možete upotrijebiti ‘*Zapad‘Kao tekst za pronalaženje u dijaloškom okviru Pronađi i zamijeni. To će vam dati sve ćelije u kojima tekst završava riječju Zapad.

Brisanje svih redova s ​​praznom ćelijom

U slučaju da želite izbrisati sve retke u kojima postoje prazne ćelije, to možete lako učiniti pomoću ugrađene funkcije u Excelu.

To je Idi na posebne ćelije opcija - koja vam omogućuje da brzo odaberete sve prazne ćelije. A nakon što odaberete sve prazne ćelije, njihovo je brisanje vrlo jednostavno.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želim izbrisati sve retke u kojima nemam prodajnu vrijednost.

U nastavku su navedeni koraci za to:

1. Odaberite cijeli skup podataka (A1: D16 u ovom slučaju).
2. pritisni F5 ključ. Otvorit će se dijaloški okvir "Idi na" (Ovaj dijaloški okvir možete dobiti i s početne stranice -> Uređivanje -> Pronađi i odaberi -> Idi na).
3. U dijaloškom okviru "Idi na" kliknite gumb Posebna. Ovo će otvoriti dijaloški okvir "Idi na posebno"
4. U dijaloškom okviru Idi na posebno odaberite "Praznine".
5. Pritisnite U redu.

Gore navedeni koraci odabrali bi sve ćelije koje su prazne u skupu podataka.

Nakon što odaberete prazne ćelije, desnom tipkom miša kliknite bilo koju od ćelija i kliknite Izbriši.

U dijaloškom okviru Izbriši odaberite opciju ‘Cijeli red’ i kliknite U redu. Time će se izbrisati svi retci koji imaju prazne ćelije.

Ako vas zanima više o ovoj tehnici, napisao sam detaljan vodič o tome kako izbrisati retke s praznim ćelijama. Uključuje metodu 'Idi na posebno' kao i VBA metodu za brisanje redaka s praznim ćelijama.

Filtriranje i brisanje redaka na temelju vrijednosti ćelije (pomoću VBA)

Posljednja metoda koju ću vam pokazati uključuje malo VBA.

Ovu metodu možete koristiti ako često trebate brisati retke na temelju određene vrijednosti u stupcu. VBA kôd možete dodati jednom i dodati u svoju radnu knjigu Personal Macro. Na taj će način biti dostupan za upotrebu u svim vašim radnim knjigama programa Excel.

Ovaj kôd funkcionira na isti način kao gore opisana metoda filtriranja (osim činjenice da se time izvršavaju svi koraci u pozadini i štedi vam nekoliko klikova).

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite izbrisati sve retke u kojima je regija Srednji zapad.

Ispod je VBA kod koji će to učiniti.

Sub DeleteRowsWithSpecificText () 'Izvor: https: //trumpexcel.com/delete-rows-based-on-cell-value/ ActiveCell.AutoFilter Polje: = 2, Kriterij1: = "Srednji zapad" ActiveSheet.AutoFilter.Range.Offset (1, 0) .Rows.SpecialCells (xlCellTypeVisible) .Delete End Sub

Gornji kôd koristi metodu VBA Autofilter za prvo filtriranje redaka na temelju navedenih kriterija (što je "Srednji zapad"), zatim odabir svih filtriranih redaka i brisanje.

Imajte na umu da sam u gornjem kodu upotrijebio Offset kako bih se uvjerio da moj redak zaglavlja nije izbrisan.

Gornji kôd ne radi ako su vaši podaci u Excel tablici. Razlog tome je što Excel Excel tablicu smatra objektom popisa. Dakle, ako želite izbrisati retke koji su u tablici, morate malo izmijeniti kôd (obrađeno kasnije u ovom vodiču).

Prije brisanja redaka prikazat će se upit kao što je prikazano u nastavku. Smatram da je ovo korisno jer mi omogućuje da dvaput provjerim filtrirani redak prije brisanja.

Upamtite da kada izbrišete retke pomoću VBA -a, ovu promjenu ne možete poništiti. Stoga koristite ovo samo ako ste sigurni da ovo radi onako kako želite. Također, dobra je ideja zadržati sigurnosnu kopiju podataka samo u slučaju da nešto pođe po zlu.

U slučaju da se vaši podaci nalaze u Excelovoj tablici, upotrijebite donji kod za brisanje redaka s određenom vrijednošću:

Sub DeleteRowsinTables () 'Izvor: https: //trumpexcel.com/delete-rows-based-on-cell-value/ Dim Tbl As ListObject Set Tbl = ActiveSheet.ListObjects (1) ActiveCell.AutoFilter polje: = 2, Kriterij1: = "Srednji zapad" Tbl.DataBodyRange.SpecialCells (xlCellTypeVisible) .Delete End Sub

Budući da VBA smatra Excel tablicu objektom popisa (a ne rasponom), morao sam prema tome promijeniti kôd.

Gdje staviti VBA kôd?

Ovaj kôd treba staviti u pozadinu VB Editor -a u modulu.

Ispod su koraci koji će vam pokazati kako to učiniti:

1. Otvorite radnu knjigu u koju želite dodati ovaj kôd.
2. Pomoću tipkovnih prečaca ALT + F11 otvorite prozor VBA Editor.
3. U ovom prozoru VBA Editor, s lijeve strane, nalazi se okno 'Project Explorer' (koje navodi sve radne knjige i objekte radnih listova). Desnom tipkom miša kliknite bilo koji objekt u radnoj knjizi (u kojem želite da ovaj kôd radi), zadržite pokazivač iznad "Umetni", a zatim kliknite na "Modul". Ovo će dodati objekt Modula u Radnu knjigu i otvoriti prozor koda modula s desne strane
4. U prozoru modula (koji će se pojaviti s desne strane) kopirajte i zalijepite gornji kôd.

Nakon što imate kôd u VB Editoru, možete ga pokrenuti pomoću bilo koje od dolje navedenih metoda (provjerite jeste li odabrali bilo koju ćeliju u skupu podataka na kojoj želite pokrenuti ovaj kôd):

1. Odaberite bilo koji redak unutar koda i pritisnite tipku F5.
2. Pritisnite gumb Pokreni na alatnoj traci u VB Editoru
3. Dodijelite makronaredbu gumbu ili obliku i pokrenite ga klikom na njega na samom radnom listu
4. Dodajte ga na alatnu traku za brzi pristup i pokrenite kôd jednim klikom.

U ovom članku možete pročitati sve o tome kako pokrenuti makro kôd u Excelu.

Napomena: Budući da radna knjiga sadrži VBA makro kôd, morate ga spremiti u formatu omogućenom za makroe (xlsm).