Rindu dzēšana, pamatojoties uz šūnas vērtību (vai stāvokli) programmā Excel (vienkāršs ceļvedis)

Strādājot ar lielām datu kopām, jums var būt nepieciešams ātri izdzēst rindas, pamatojoties uz šūnu vērtībām (vai nosacījumu).

Piemēram, apsveriet šādus piemērus:

1. Jums ir pārdošanas pārstāvja dati un vēlaties dzēst visus ierakstus konkrētam reģionam vai produktam.
2. Jūs vēlaties dzēst visus ierakstus, kuru pārdošanas vērtība ir mazāka par 100.
3. Jūs vēlaties izdzēst visas rindas, kurās ir tukša šūna.

Programmā Excel ir vairāki veidi, kā noņemt šo datu kaķi.

Rindu dzēšanas metode būs atkarīga no tā, kā jūsu dati ir strukturēti un kāda ir šūnas vērtība vai stāvoklis, pamatojoties uz kuru vēlaties dzēst šīs rindas.

Šajā apmācībā es parādīšu vairākus veidus, kā izdzēst rindas programmā Excel, pamatojoties uz šūnas vērtību vai nosacījumu.

Filtrējiet rindas, pamatojoties uz vērtību/nosacījumu, un pēc tam izdzēsiet to

Viens no ātrākajiem veidiem, kā izdzēst rindas, kas satur noteiktu vērtību vai atbilst noteiktam nosacījumam, ir to filtrēšana. Kad esat filtrējis datus, varat izdzēst visas šīs rindas (kamēr pārējās rindas paliek neskartas).

Excel filtrs ir diezgan universāls, un jūs varat filtrēt, pamatojoties uz daudziem kritērijiem (piemēram, tekstu, cipariem, datumiem un krāsām)

Apskatīsim divus piemērus, kur rindas var filtrēt un izdzēst.

Dzēst rindas, kurās ir noteikts teksts

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties dzēst visas rindas, kurās reģions atrodas Vidusrietumu daļā (B slejā).

Lai gan šajā mazajā datu kopā jūs varat izvēlēties manuāli dzēst šīs rindas, bieži vien jūsu datu kopas būs milzīgas, un manuāla rindu dzēšana nebūs iespējama.

Tādā gadījumā varat filtrēt visus ierakstus, kur reģions atrodas Vidusrietumos, un pēc tam izdzēst visas šīs rindas (vienlaikus saglabājot neskartas pārējās rindas).

Tālāk ir norādītas darbības, lai dzēstu rindas, pamatojoties uz vērtību (visi Mid-West ieraksti):

1. Datu kopā atlasiet jebkuru šūnu, no kuras vēlaties dzēst rindas
2. Noklikšķiniet uz cilnes Dati
3. Grupā “Kārtot un filtrēt” noklikšķiniet uz filtra ikonas. Tādējādi filtri tiks lietoti visām datu kopas galvenes šūnām
4. Noklikšķiniet uz ikonas Filtrs reģiona galvenes šūnā (šī ir maza uz leju vērsta trīsstūra ikona šūnas augšējā labajā stūrī)
5. Noņemiet atlasi visām pārējām opcijām, izņemot opciju Mid-West (ātrs veids, kā to izdarīt, noklikšķinot uz opcijas Select All (Atlasīt visu) un pēc tam uz Mid-West (Vidus-Rietumi)). Tas filtrēs datu kopu un parādīs tikai Mid-West reģiona ierakstus.
6. Atlasiet visus filtrētos ierakstus
7. Ar peles labo pogu noklikšķiniet uz jebkuras atlasītās šūnas un noklikšķiniet uz “Dzēst rindu”
8. Atvērtajā dialoglodziņā noklikšķiniet uz Labi. Šajā brīdī datu kopā nebūs redzami ieraksti.
9. Noklikšķiniet uz cilnes Dati un noklikšķiniet uz ikonas Filtrs. Tādējādi filtrs tiks noņemts, un jūs redzēsit visus ierakstus, izņemot izdzēstos.

Iepriekš minētās darbības vispirms filtrē datus, pamatojoties uz šūnas vērtību (vai var būt cits nosacījums, piemēram, pēc/pirms datuma vai lielāks/mazāks par skaitli). Kad esat ieguvis ierakstus, tos vienkārši izdzēsiet.

Daži noderīgi īsceļi, kas jāzina, lai paātrinātu procesu:

1. Control + Shift + L lai lietotu vai noņemtu filtru
2. Control + - (turiet nospiestu vadības taustiņu un nospiediet mīnusa taustiņu), lai izdzēstu atlasītās šūnas/rindas

Iepriekš minētajā piemērā man bija tikai četri atšķirīgi reģioni, un es to varēju manuāli atlasīt un noņemt atlasi no filtru saraksta (5. darbībā iepriekš).

Ja jums ir daudz kategoriju/reģionu, varat ierakstīt nosaukumu laukā tieši virs lodziņa (ar šiem reģionu nosaukumiem), un programma Excel parādīs tikai tos ierakstus, kas atbilst ievadītajam tekstam (kā parādīts zemāk). Kad jums ir teksts, pēc kura vēlaties filtrēt, nospiediet taustiņu Enter.

Ņemiet vērā, ka, izdzēšot rindu, tiks zaudēts viss, kas var būt citās šo rindu šūnās. Viens veids, kā to novērst, ir izveidot datu kopiju citā darblapā un izdzēst kopētās datu rindas. Kad esat pabeidzis, kopējiet to atpakaļ sākotnējo datu vietā.

Vai

Jūs varat izmantot metodes, kas parādītas vēlāk šajā apmācībā (izmantojot metodi Kārtot vai Atrast visu)

Dzēst rindas, pamatojoties uz skaitlisko stāvokli

Tāpat kā es izmantoju filtrēšanas metodi, lai izdzēstu visas rindas, kas satur tekstu Mid-West, varat izmantot arī skaitļa nosacījumu (vai datuma nosacījumu).

Piemēram, pieņemsim, ka man ir zemāk esošā datu kopa un es vēlos dzēst visas rindas, kurās pārdošanas vērtība ir mazāka par 200.

Tālāk ir norādītas darbības, kā to izdarīt:

1. Datos atlasiet jebkuru šūnu
2. Noklikšķiniet uz cilnes Dati
3. Grupā “Kārtot un filtrēt” noklikšķiniet uz filtra ikonas. Tādējādi filtri tiks lietoti visām datu kopas galvenes šūnām
4. Noklikšķiniet uz ikonas Filtrs pārdošanas galvenes šūnā (šī ir maza lejup vērsta trīsstūra ikona šūnas augšējā labajā stūrī)
5. Virziet kursoru virs opcijas Ciparu filtri. Tas parādīs visas ar numuru saistītās filtra opcijas programmā Excel.
6. Noklikšķiniet uz opcijas “Mazāk nekā”.
7. Atvērtajā dialoglodziņā “Pielāgots automātiskais filtrs” ievadiet laukā vērtību “200”
8. Noklikšķiniet uz Labi. Tas filtrēs un parādīs tikai tos ierakstus, kuru pārdošanas vērtība ir mazāka par 200
9. Atlasiet visus filtrētos ierakstus
10. Ar peles labo pogu noklikšķiniet uz jebkuras šūnas un noklikšķiniet uz Dzēst rindu
11. Atvērtajā dialoglodziņā noklikšķiniet uz Labi. Šajā brīdī datu kopā nebūs redzami ieraksti.
12. Noklikšķiniet uz cilnes Dati un noklikšķiniet uz ikonas Filtrs. Tādējādi filtrs tiks noņemts, un jūs redzēsit visus ierakstus, izņemot izdzēstos.

Programmā Excel var izmantot daudzus skaitļu filtrus, piemēram, mazāk nekā/lielāks nekā, vienāds/nav vienāds, starp, top 10, virs vai zem vidējā līmeņa utt.

Piezīme. Varat arī izmantot vairākus filtrus. Piemēram, varat izdzēst visas rindas, kuru pārdošanas vērtība ir lielāka par 200, bet mazāka par 500. Šajā gadījumā jāizmanto divi filtra nosacījumi. Dialoglodziņā Pielāgots automātiskais filtrs ir pieejami divi filtra kritēriji (UN, kā arī VAI).

Tāpat kā skaitļu filtrus, varat arī filtrēt ierakstus, pamatojoties uz datumu. Piemēram, ja vēlaties noņemt visus pirmā ceturkšņa ierakstus, varat to izdarīt, veicot tās pašas darbības. Strādājot ar datuma filtriem, programma Excel automātiski parāda atbilstošos filtrus (kā parādīts zemāk).

Lai gan filtrēšana ir lielisks veids, kā ātri izdzēst rindas, pamatojoties uz vērtību vai nosacījumu, tam ir viens trūkums - tas izdzēš visu rindu. Piemēram, tālāk norādītajā gadījumā tas izdzēstu visus datus, kas atrodas pa labi no filtrētās datu kopas.

Ko darīt, ja es vēlos tikai izdzēst ierakstus no datu kopas, bet vēlos saglabāt neskartus atlikušos datus.

Ar filtrēšanu to nevar izdarīt, bet ar šķirošanu.

Kārtojiet datu kopu un pēc tam izdzēsiet rindas

Lai gan šķirošana ir vēl viens veids, kā izdzēst rindas, pamatojoties uz vērtību, bet vairumā gadījumu labāk ir izmantot iepriekš aprakstīto filtrēšanas metodi.

Šī kārtošanas metode ir ieteicama tikai tad, ja vēlaties dzēst šūnas ar vērtībām, nevis visas rindas.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties dzēst visus ierakstus, kuros reģions atrodas Vidusrietumu daļā.

Tālāk ir norādītas darbības, lai to izdarītu, izmantojot kārtošanu:

1. Datos atlasiet jebkuru šūnu
2. Noklikšķiniet uz cilnes Dati
3. Grupā Kārtot un filtrēt noklikšķiniet uz ikonas Kārtot.
4. Atvērtajā dialoglodziņā Kārtot kolonnā kārtot pēc atlasiet Reģions.
5. Opcijā Kārtot ieslēdziet, lai būtu atlasīta Šūnu vērtības
6. Pasūtījuma opcijā atlasiet no A līdz Z (vai no Z līdz A, tam nav īsti nozīmes).
7. Noklikšķiniet uz Labi. Tādējādi tiks parādīta sakārtotā datu kopa, kā parādīts zemāk (sakārtota pēc kolonnas B).
8. Atlasiet visus ierakstus ar reģionu Mid-West (visas rindu šūnas, nevis tikai apgabala kolonna)
9. Kad esat izvēlējies, ar peles labo pogu noklikšķiniet un pēc tam noklikšķiniet uz Dzēst. Tiks atvērts dialoglodziņš Dzēst.
10. Pārliecinieties, vai ir atlasīta opcija “Pārvietot šūnas uz augšu”.
11. Noklikšķiniet uz Labi.

Iepriekš minētās darbības izdzēsīs visus ierakstus, kuros reģions atradās Vidusrietumos, taču tas neizdzēš visu rindu. Tātad, ja jūsu datu kopas labajā vai kreisajā pusē ir kādi dati, tie paliks neskarti.

Iepriekš minētajā piemērā es esmu sakārtojis datus, pamatojoties uz šūnas vērtību, taču jūs varat arī izmantot tās pašas darbības, lai kārtotu, pamatojoties uz skaitļiem, datumiem, šūnu krāsu vai fonta krāsu utt.

Šeit ir detalizēts ceļvedis par datu kārtošanu programmā Excel

Ja vēlaties saglabāt sākotnējo datu kopu secību, bet noņemt ierakstus, pamatojoties uz kritērijiem, jums ir jābūt iespējai kārtot datus atpakaļ uz sākotnējo. Lai to izdarītu, pirms datu kārtošanas pievienojiet kolonnu ar sērijas numuriem. Kad esat pabeidzis rindu/ierakstu dzēšanu, vienkārši kārtojiet, izmantojot šo pievienoto papildu kolonnu.

Atrodiet un atlasiet šūnas, pamatojoties uz šūnu vērtību, un pēc tam izdzēsiet rindas

Programmai Excel ir funkcija Atrast un aizstāt, kas var būt lieliski, ja vēlaties atrast un atlasīt šūnas ar noteiktu vērtību.

Kad esat atlasījis šīs šūnas, varat viegli izdzēst rindas.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties dzēst visas rindas, kurās reģions atrodas Vidusrietumos.

Tālāk ir norādītas darbības, kā to izdarīt:

1. Atlasiet visu datu kopu
2. Noklikšķiniet uz cilnes Sākums
3. Rediģēšanas grupā noklikšķiniet uz opcijas “Atrast un atlasīt” un pēc tam uz Atrast (varat izmantot arī īsinājumtaustiņu Control + F).
4. Dialoglodziņā Atrast un aizstāt laukā Atrast ko ievadiet tekstu “Vidusrietumi”.
5. Noklikšķiniet uz Atrast visu. Tas uzreiz parādīs visus Mid-West teksta gadījumus, kurus Excel varēja atrast.
6. Izmantojiet īsinājumtaustiņu Control + A, lai atlasītu visas Excel atrastās šūnas. Jūs arī varēsit redzēt visas atlasītās šūnas datu kopā.
7. Ar peles labo pogu noklikšķiniet uz jebkuras atlasītās šūnas un noklikšķiniet uz Dzēst. Tiks atvērts dialoglodziņš Dzēst.
8. Atlasiet opciju “Visa rinda”
9. Noklikšķiniet uz Labi.

Iepriekš minētās darbības izdzēsīs visas šūnas, kurās reģiona vērtība ir Vidusrietumi.

Piezīme. Tā kā funkcija Atrast un aizstāt var apstrādāt aizstājējzīmes, varat tās izmantot, atrodot datus programmā Excel. Piemēram, ja vēlaties izdzēst visas rindas, kurās reģions atrodas Vidusrietumu vai Dienvidrietumu virzienā, varat izmantot “*Rietumi“Kā tekstu, kas atrodams dialoglodziņā Atrast un aizstāt. Tādējādi tiks parādītas visas šūnas, kurās teksts beidzas ar vārdu Rietumi.

Dzēst visas rindas ar tukšu šūnu

Ja vēlaties izdzēst visas rindas, kurās ir tukšas šūnas, varat to viegli izdarīt, izmantojot Excel iebūvēto funkcionalitāti.

Tas ir Doties uz īpašām šūnām opcija - kas ļauj ātri atlasīt visas tukšās šūnas. Un, kad esat atlasījis visas tukšās šūnas, to dzēšana ir ļoti vienkārša.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un es vēlos izdzēst visas rindas, kurās man nav pārdošanas vērtības.

Tālāk ir norādītas darbības, kā to izdarīt:

1. Atlasiet visu datu kopu (šajā gadījumā A1: D16).
2. Nospiediet F5 taustiņu. Tiks atvērts dialoglodziņš “Iet uz” (šo dialoglodziņu var iegūt arī no Sākums -> Rediģēšana -> Atrast un atlasīt -> Atvērt).
3. Dialoglodziņā “Iet uz” noklikšķiniet uz pogas Īpašais. Tiks atvērts dialoglodziņš “Doties uz īpašo”
4. Dialoglodziņā Pāriet uz īpašiem atlasiet “Tukšas vietas”.
5. Noklikšķiniet uz Labi.

Iepriekš minētās darbības atlasītu visas šūnas, kas datu kopā ir tukšas.

Kad esat atlasījis tukšās šūnas, ar peles labo pogu noklikšķiniet uz jebkuras šūnas un noklikšķiniet uz Dzēst.

Dialoglodziņā Dzēst atlasiet opciju “Visa rinda” un noklikšķiniet uz Labi. Tādējādi tiks dzēstas visas rindas, kurās ir tukšas šūnas.

Ja jūs interesē uzzināt vairāk par šo paņēmienu, es uzrakstīju detalizētu apmācību par to, kā izdzēst rindas ar tukšām šūnām. Tas ietver “Go to Special” metodi, kā arī VBA metodi rindu ar tukšām šūnām dzēšanai.

Filtrējiet un dzēsiet rindas, pamatojoties uz šūnas vērtību (izmantojot VBA)

Pēdējā metode, kuru es jums parādīšu, ietver nedaudz VBA.

Šo metodi varat izmantot, ja bieži vien ir nepieciešams dzēst rindas, pamatojoties uz noteiktu slejas vērtību. VBA kodu varat pievienot vienu reizi un pievienot personiskajai makro darbgrāmatai. Tādā veidā tas būs pieejams lietošanai visās jūsu Excel darbgrāmatās.

Šis kods darbojas tāpat kā iepriekš aprakstītā filtrēšanas metode (izņemot faktu, ka tas veic visas aizmugurējās programmas darbības un ietaupa dažus klikšķus).

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties dzēst visas rindas, kurās reģions atrodas Vidusrietumos.

Zemāk ir VBA kods, kas to darīs.

Sub DeleteRowsWithSpecificText () 'Avots: 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). Dzēst beigu apakšdaļu

Iepriekš minētais kods izmanto VBA automātiskā filtra metodi, lai vispirms filtrētu rindas, pamatojoties uz norādītajiem kritērijiem (kas ir “Mid-West”), pēc tam atlasiet visas filtrētās rindas un izdzēsiet to.

Ņemiet vērā, ka iepriekšējā kodā esmu izmantojis nobīdi, lai pārliecinātos, ka mana galvenes rinda nav izdzēsta.

Iepriekš minētais kods nedarbojas, ja jūsu dati ir Excel tabulā. Iemesls tam ir tas, ka Excel uzskata Excel tabulu par saraksta objektu. Tātad, ja vēlaties izdzēst tabulā esošās rindas, jums ir nedaudz jāmaina kods (apskatīts vēlāk šajā apmācībā).

Pirms rindu dzēšanas tas parādīs uzvedni, kā parādīts zemāk. Tas man šķiet noderīgi, jo ļauj pirms dzēšanas vēlreiz pārbaudīt filtrēto rindu.

Atcerieties, ka, dzēšot rindas, izmantojot VBA, šīs izmaiņas nevar atsaukt. Tāpēc izmantojiet to tikai tad, ja esat pārliecināts, ka tas darbojas tā, kā vēlaties. Tāpat ir laba ideja paturēt datu rezerves kopiju, ja vien kaut kas noiet greizi.

Ja jūsu dati ir Excel tabulā, izmantojiet tālāk norādīto kodu, lai dzēstu rindas ar noteiktu vērtību:

Sub DeleteRowsinTables () 'Avots: https: //trumpexcel.com/delete-rows-based-on-cell-value/ Dim Tbl kā ListObject Set Tbl = ActiveSheet.ListObjects (1) ActiveCell.AutoFilter lauks: = 2, Kritērijs1: = "Mid-West" Tbl.DataBodyRange.SpecialCells (xlCellTypeVisible) .Delete End Sub

Tā kā VBA uzskata Excel tabulu par saraksta objektu (nevis diapazonu), man bija attiecīgi jāmaina kods.

Kur ievietot VBA kodu?

Šis kods ir jāievieto moduļa VB redaktora aizmugurē.

Tālāk ir norādītas darbības, kas parādīs, kā to izdarīt:

1. Atveriet darbgrāmatu, kurā vēlaties pievienot šo kodu.
2. Izmantojiet īsinājumtaustiņu ALT + F11, lai atvērtu VBA redaktora logu.
3. Šajā VBA redaktora logā kreisajā pusē ir rūts “Project Explorer” (kurā uzskaitītas visas darbgrāmatas un darblapu objekti). Ar peles labo pogu noklikšķiniet uz jebkura darbgrāmatas objekta (kurā vēlaties, lai šis kods darbotos), virziet kursoru virs “Ievietot” un pēc tam noklikšķiniet uz “Modulis”. Tas pievienos Moduļa objektu darbgrāmatai un arī atvērs moduļa koda logu labajā pusē
4. Moduļa logā (kas parādīsies labajā pusē) nokopējiet un ielīmējiet iepriekš minēto kodu.

Kad esat ievadījis kodu VB redaktorā, varat palaist kodu, izmantojot kādu no tālāk norādītajām metodēm (pārliecinieties, vai esat atlasījis jebkuru šūnu datu kopā, kurā vēlaties palaist šo kodu):

1. Atlasiet jebkuru koda rindu un nospiediet taustiņu F5.
2. VB redaktora rīkjoslā noklikšķiniet uz pogas Palaist
3. Piešķiriet makro pogai vai formai un palaidiet to, noklikšķinot uz tā pašā darblapā
4. Pievienojiet to ātrās piekļuves rīkjoslai un palaidiet kodu ar vienu klikšķi.

Šajā rakstā varat izlasīt visu par makro koda palaišanu programmā Excel.

Piezīme .: Tā kā darbgrāmatā ir VBA makro kods, tas jāsaglabā makro iespējotā formātā (xlsm).