Ștergeți rândurile pe baza unei valori (sau a unei stări) a celulei în Excel (Ghid ușor)

Când lucrați cu seturi de date mari, este posibil să aveți nevoie să ștergeți rapid rânduri pe baza valorilor celulei din acesta (sau pe baza unei condiții).

De exemplu, luați în considerare următoarele exemple:

1. Aveți date despre reprezentanții de vânzări și doriți să ștergeți toate înregistrările pentru o anumită regiune sau produs.
2. Doriți să ștergeți toate înregistrările pentru care valoarea vânzării este mai mică de 100.
3. Doriți să ștergeți toate rândurile în care există o celulă necompletată.

Există mai multe moduri de a jupui această pisică de date în Excel.

Metoda pe care o alegeți pentru a șterge rândurile va depinde de modul în care sunt structurate datele dvs. și de care este valoarea sau condiția celulei pe baza căreia doriți să ștergeți aceste rânduri.

În acest tutorial, vă voi arăta mai multe moduri de a șterge rânduri în Excel pe baza unei valori de celulă sau a unei condiții.

Filtrați rândurile în funcție de valoare / condiție și apoi ștergeți-le

Una dintre cele mai rapide modalități de a șterge rândurile care conțin o anumită valoare sau de a îndeplini o anumită condiție este de a le filtra. După ce aveți datele filtrate, puteți șterge toate aceste rânduri (în timp ce rândurile rămase rămân intacte).

Filtrul Excel este destul de versatil și puteți filtra în funcție de mai multe criterii (cum ar fi textul, numerele, datele și culorile)

Să vedem două exemple în care puteți filtra rândurile și le puteți șterge.

Ștergeți rândurile care conțin un anumit text

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să ștergeți toate rândurile în care regiunea este Mid-West (în Coloana B).

În timp ce în acest mic set de date puteți alege să ștergeți manual aceste rânduri, deseori seturile dvs. de date vor fi uriașe, atunci când ștergerea manuală a rândurilor nu va fi o opțiune.

În acest caz, puteți filtra toate înregistrările în care regiunea este Mid-West și apoi șterge toate aceste rânduri (păstrând în același timp celelalte rânduri intacte).

Mai jos sunt pașii pentru ștergerea rândurilor pe baza valorii (toate înregistrările Mid-West):

1. Selectați orice celulă din setul de date din care doriți să ștergeți rândurile
2. Faceți clic pe fila Date
3. În grupul „Sortare și filtrare”, faceți clic pe pictograma Filtru. Aceasta va aplica filtre tuturor celulelor de antet din setul de date
4. Faceți clic pe pictograma Filtru din celula antet Regiune (aceasta este o pictogramă mică triunghi orientată în jos în partea dreaptă sus a celulei)
5. Deselectați toate celelalte opțiuni, cu excepția opțiunii Mid-West (o modalitate rapidă de a face acest lucru este făcând clic pe opțiunea Select All și apoi făcând clic pe opțiunea Mid-West). Aceasta va filtra setul de date și vă va arăta numai înregistrări pentru regiunea Mid-West.
6. Selectați toate înregistrările filtrate
7. Faceți clic dreapta pe oricare dintre celulele selectate și faceți clic pe „Șterge rând”
8. În caseta de dialog care se deschide, faceți clic pe OK. În acest moment, nu veți vedea nicio înregistrare în setul de date.
9. Faceți clic pe fila Date și faceți clic pe pictograma Filtru. Aceasta va elimina filtrul și veți vedea toate înregistrările, cu excepția celor șterse.

Pașii de mai sus filtrează mai întâi datele pe baza valorii unei celule (sau pot fi alte condiții, cum ar fi după / înainte de o dată sau mai mare / mai mică decât un număr). După ce ai înregistrările, pur și simplu le ștergi.

Câteva comenzi rapide utile pentru a accelera procesul:

1. Control + Shift + L pentru a aplica sau elimina filtrul
2. Control + - (țineți apăsată tasta de control și apăsați tasta minus) pentru a șterge celulele / rândurile selectate

În exemplul de mai sus, am avut doar patru regiuni distincte și l-aș putea selecta și deselecta manual din lista Filtru (în pașii 5 de mai sus).

În cazul în care aveți o mulțime de categorii / regiuni, puteți introduce numele în câmpul chiar deasupra casetei (care are aceste nume de regiuni), iar Excel vă va arăta numai acele înregistrări care se potrivesc cu textul introdus (așa cum se arată mai jos). Odată ce aveți textul pe baza căruia doriți să filtrați, apăsați tasta Enter.

Rețineți că atunci când ștergeți un rând, tot ceea ce ați putea avea în alte celule din aceste rânduri se va pierde. O modalitate de a evita acest lucru este să creați o copie a datelor într-o altă foaie de lucru și să ștergeți rândurile din datele copiate. După ce ați terminat, copiați-l înapoi în locul datelor originale.

Sau

Puteți utiliza metodele prezentate mai târziu în acest tutorial (folosind metoda Sortare sau metoda Găsiți toate)

Ștergeți rândurile pe baza unei condiții numerice

Așa cum am folosit metoda de filtrare pentru a șterge toate rândurile care conțin textul Mid-West, puteți utiliza și o condiție de număr (sau o condiție de dată).

De exemplu, să presupunem că am setul de date de mai jos și vreau să șterg toate rândurile în care valoarea vânzării este mai mică de 200.

Mai jos sunt pașii pentru a face acest lucru:

1. Selectați orice celulă din date
2. Faceți clic pe fila Date
3. În grupul „Sortare și filtrare”, faceți clic pe pictograma Filtru. Aceasta va aplica filtre tuturor celulelor de antet din setul de date
4. Faceți clic pe pictograma Filtru din celula antet Vânzări (aceasta este o mică pictogramă triunghi orientată în jos în partea dreaptă sus a celulei)
5. Plasați cursorul peste opțiunea Filtre numerice. Aceasta vă va arăta toate opțiunile de filtrare legate de număr în Excel.
6. Faceți clic pe opțiunea „Mai puțin de”.
7. În caseta de dialog „Filtru automat personalizat” care se deschide, introduceți valoarea „200” în câmp
8. Faceți clic pe OK. Aceasta va filtra și afișa numai acele înregistrări în care valoarea vânzărilor este mai mică de 200
9. Selectați toate înregistrările filtrate
10. Faceți clic dreapta pe oricare dintre celule și faceți clic pe Șterge rând
11. În caseta de dialog care se deschide, faceți clic pe OK. În acest moment, nu veți vedea nicio înregistrare în setul de date.
12. Faceți clic pe fila Date și faceți clic pe pictograma Filtru. Aceasta va elimina filtrul și veți vedea toate înregistrările, cu excepția celor șterse.

Există multe filtre numerice pe care le puteți utiliza în Excel - cum ar fi mai mic decât / mai mare decât, egal / nu egal, între, top 10, peste sau sub medie etc.

Notă: Puteți utiliza și mai multe filtre. De exemplu, puteți șterge toate rândurile în care valoarea vânzărilor este mai mare de 200 dar mai mică de 500. În acest caz, trebuie să utilizați două condiții de filtrare. Caseta de dialog Custom Autofilter permite să aveți două criterii de filtrare (ȘI și OR).

La fel ca filtrele numerice, puteți filtra și înregistrările în funcție de dată. De exemplu, dacă doriți să eliminați toate înregistrările din primul trimestru, puteți face acest lucru utilizând aceiași pași de mai sus. Când lucrați cu filtre de dată, Excel vă arată automat filtrele relevante (așa cum se arată mai jos).

În timp ce filtrarea este o modalitate excelentă de a șterge rapid rândurile pe baza unei valori sau a unei condiții, are un dezavantaj - șterge întregul rând. De exemplu, în cazul de mai jos, ar șterge toate datele care se află în dreapta setului de date filtrat.

Ce se întâmplă dacă vreau doar să șterg înregistrările din setul de date, dar doresc să păstrez intacte datele rămase.

Nu puteți face asta cu filtrarea, dar puteți face asta cu sortarea.

Sortați setul de date și apoi ștergeți rândurile

Deși sortarea este un alt mod de a șterge rândurile pe baza valorii, dar în majoritatea cazurilor, este mai bine să folosiți metoda de filtrare prezentată mai sus.

Această tehnică de sortare este recomandată numai atunci când doriți să ștergeți celulele cu valorile și nu întregul rând.

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să ștergeți toate înregistrările în care regiunea este Mid-West.

Mai jos sunt pașii pentru a face acest lucru folosind sortarea:

1. Selectați orice celulă din date
2. Faceți clic pe fila Date
3. În grupul Sortare și filtrare, faceți clic pe pictograma Sortare.
4. În caseta de dialog Sortare care se deschide, selectați Regiune în sortarea după coloană.
5. În opțiunea Sortare pe, asigurați-vă că valorile celulei sunt selectate
6. În opțiunea Comandă, selectați de la A la Z (sau de la Z la A, nu contează cu adevărat).
7. Faceți clic pe OK. Acest lucru vă va oferi setul de date sortate așa cum se arată mai jos (sortate după coloana B).
8. Selectați toate înregistrările cu regiunea Mid-West (toate celulele din rânduri, nu doar coloana regiunii)
9. Odată selectat, faceți clic dreapta și apoi faceți clic pe Ștergere. Aceasta va deschide caseta de dialog Ștergere.
10. Asigurați-vă că este selectată opțiunea „Shift cells up”.
11. Faceți clic pe OK.

Pașii de mai sus ar șterge toate înregistrările în care regiunea era Mid-West, dar nu șterge întregul rând. Deci, dacă aveți date în dreapta sau în stânga setului de date, acestea vor rămâne nevătămate.

În exemplul de mai sus, am sortat datele pe baza valorii celulei, dar puteți utiliza, de asemenea, aceiași pași pentru a sorta în funcție de numere, date, culoarea celulei sau culoarea fontului etc.

Iată un ghid detaliat despre cum să sortați datele în Excel

În cazul în care doriți să păstrați ordinea setului de date original, dar să eliminați înregistrările pe baza criteriilor, trebuie să aveți o modalitate de a sorta datele înapoi la cea originală. Pentru a face acest lucru, adăugați o coloană cu numere de serie înainte de a sorta datele. După ce ați terminat ștergerea rândurilor / înregistrărilor, pur și simplu sortați pe baza acestei coloane suplimentare pe care ați adăugat-o.

Găsiți și selectați celulele pe baza valorii celulei și apoi ștergeți rândurile

Excel are o funcționalitate Găsiți și înlocuiți care poate fi excelentă atunci când doriți să găsiți și să selectați celule cu o anumită valoare.

După ce ați selectat aceste celule, puteți șterge cu ușurință rândurile.

Să presupunem că aveți setul de date așa cum se arată mai jos și doriți să ștergeți toate rândurile în care regiunea este Mid-West.

Mai jos sunt pașii pentru a face acest lucru:

1. Selectați întregul set de date
2. Faceți clic pe fila Acasă
3. În grupul Editare, faceți clic pe opțiunea „Găsiți și selectați”, apoi faceți clic pe Găsiți (puteți utiliza și comanda rapidă de la tastatură Control + F).
4. În caseta de dialog Găsiți și înlocuiți, introduceți textul „Mid-West” în câmpul „Găsiți ce:”.
5. Faceți clic pe Găsiți toate. Aceasta vă va arăta instantaneu toate instanțele textului Mid-West pe care Excel le-a putut găsi.
6. Utilizați comanda rapidă de la tastatură Control + A pentru a selecta toate celulele găsite de Excel. De asemenea, veți putea vedea toate celulele selectate din setul de date.
7. Faceți clic dreapta pe oricare dintre celulele selectate și faceți clic pe Ștergere. Aceasta va deschide caseta de dialog Ștergere.
8. Selectați opțiunea „Întreg rând”
9. Faceți clic pe OK.

Pașii de mai sus ar șterge toate celulele în care valoarea regiunii este Mid-West.

Notă: Deoarece Găsiți și înlocuiți poate gestiona caractere wild card, le puteți utiliza atunci când găsiți date în Excel. De exemplu, dacă doriți să ștergeți toate rândurile în care regiunea este fie Mid-West, fie South-West, puteți utiliza „* Vest„Ca textul de găsit în caseta de dialog Căutare și înlocuire. Acest lucru vă va oferi toate celulele în care textul se termină cu cuvântul Vest.

Ștergeți toate rândurile cu o celulă necompletată

În cazul în care doriți să ștergeți toate rândurile în care există celule goale, puteți face acest lucru cu ușurință cu o funcționalitate încorporată în Excel.

Este Accesați celulele speciale opțiune - care vă permite să selectați rapid toate celulele goale. Și după ce ați selectat toate celulele goale, ștergerea acestora este foarte simplă.

Să presupunem că aveți setul de date așa cum se arată mai jos și vreau să șterg toate rândurile în care nu am valoarea de vânzare.

Mai jos sunt pașii pentru a face acest lucru:

1. Selectați întregul set de date (A1: D16 în acest caz).
2. apasă pe F5 cheie. Aceasta va deschide caseta de dialog „Mergeți la” (Puteți obține această casetă de dialog și de la Acasă -> Editare -> Găsiți și selectați -> Mergeți la).
3. În caseta de dialog „Mergeți la”, faceți clic pe butonul Special. Aceasta va deschide caseta de dialog „Mergi la special”
4. În caseta de dialog Accesați special, selectați „Blanks”.
5. Faceți clic pe OK.

Pașii de mai sus ar selecta toate celulele care sunt goale în setul de date.

După ce ați selectat celulele goale, faceți clic dreapta pe oricare dintre celule și faceți clic pe Ștergere.

În caseta de dialog Ștergeți, selectați opțiunea „Întreg rând” și faceți clic pe OK. Aceasta va șterge toate rândurile care au celule goale în ea.

Dacă sunteți interesat să aflați mai multe despre această tehnică, am scris un tutorial detaliat despre cum să ștergeți rândurile cu celule goale. Include metoda „Mergeți la special”, precum și o metodă VBA pentru a șterge rânduri cu celule goale.

Filtrează și șterge rânduri pe baza valorii celulei (folosind VBA)

Ultima metodă pe care vă voi arăta o include un pic de VBA.

Puteți utiliza această metodă dacă de multe ori trebuie să ștergeți rânduri pe baza unei anumite valori dintr-o coloană. Puteți adăuga codul VBA o singură dată și adăugați-l în registrul de lucru Macro personal. În acest fel va fi disponibil pentru utilizare în toate registrele de lucru Excel.

Acest cod funcționează la fel ca metoda de filtrare prezentată mai sus (cu excepția faptului că aceasta face toți pașii din backend și vă economisește câteva clicuri).

Să presupunem că aveți setul de date așa cum se arată mai jos și doriți să ștergeți toate rândurile în care regiunea este Mid-West.

Mai jos este codul VBA care va face acest lucru.

Sub DeleteRowsWithSpecificText () 'Sursa: 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). Șterge End Sub

Codul de mai sus folosește metoda VBA Autofilter pentru a filtra mai întâi rândurile pe baza criteriilor specificate (care este „Mid-West”), apoi selectați toate rândurile filtrate și ștergeți-le.

Rețineți că am folosit Offset în codul de mai sus pentru a mă asigura că rândul meu de antet nu este șters.

Codul de mai sus nu funcționează dacă datele dvs. se află într-un tabel Excel. Motivul pentru aceasta este că Excel consideră un tabel Excel ca obiect de listă. Deci, dacă doriți să ștergeți rândurile care se află într-un tabel, trebuie să modificați puțin codul (tratat mai târziu în acest tutorial).

Înainte de a șterge rândurile, acesta vă va afișa o solicitare așa cum se arată mai jos. Mi se pare util acest lucru, deoarece îmi permite să verific dublu rândul filtrat înainte de ștergere.

Amintiți-vă că atunci când ștergeți rânduri folosind VBA, nu puteți anula această modificare. Așadar, utilizați acest lucru numai atunci când sunteți sigur că funcționează așa cum doriți. De asemenea, este o idee bună să păstrați o copie de rezervă a datelor, în cazul în care ceva nu merge bine.

În cazul în care datele dvs. se află într-un tabel Excel, utilizați codul de mai jos pentru a șterge rândurile cu o anumită valoare:

Sub DeleteRowsinTables () 'Sursa: 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). Șterge End Sub

Deoarece VBA consideră Tabelul Excel ca un obiect List (și nu un interval), a trebuit să schimb codul în consecință.

Unde să puneți codul VBA?

Acest cod trebuie plasat în backend-ul VB Editor al unui modul.

Mai jos sunt pașii care vă vor arăta cum să faceți acest lucru:

1. Deschideți registrul de lucru în care doriți să adăugați acest cod.
2. Utilizați comanda rapidă de la tastatură ALT + F11 pentru a deschide fereastra VBA Editor.
3. În această fereastră VBA Editor, în stânga, există un panou „Project Explorer” (care listează toate registrele de lucru și obiectele foilor de lucru). Faceți clic dreapta pe orice obiect din registrul de lucru (în care doriți să funcționeze acest cod), treceți cursorul peste „Insert” și apoi faceți clic pe „Module”. Aceasta va adăuga obiectul Module în registrul de lucru și va deschide, de asemenea, fereastra Codului modulului din dreapta
4. În fereastra modulului (care va apărea în dreapta), copiați și lipiți codul de mai sus.

După ce aveți codul în Editorul VB, puteți rula codul utilizând oricare dintre metodele de mai jos (asigurați-vă că ați selectat orice celulă din setul de date pe care doriți să rulați acest cod):

1. Selectați orice linie din cod și apăsați tasta F5.
2. Faceți clic pe butonul Executare din bara de instrumente din Editorul VB
3. Alocați macrocomanda unui buton sau unei forme și executați-o făcând clic pe ea în foaia de lucru
4. Adăugați-l la Bara de instrumente de acces rapid și rulați codul cu un singur clic.

Puteți citi totul despre cum să rulați codul macro în Excel în acest articol.

Notă: Deoarece registrul de lucru conține un cod macro VBA, trebuie să îl salvați în formatul macro-enabled (xlsm).