Rivien poistaminen solun arvon (tai ehdon) perusteella Excelissä (Helppo opas)

Kun käsittelet suuria tietojoukkoja, saatat joutua poistamaan rivit nopeasti niiden soluarvojen (tai ehdon) perusteella.

Harkitse esimerkiksi seuraavia esimerkkejä:

1. Sinulla on myyntiedustajatietoja ja haluat poistaa kaikki tietueet tietystä alueesta tai tuotteesta.
2. Haluat poistaa kaikki tietueet, joiden myyntiarvo on alle 100.
3. Haluat poistaa kaikki rivit, joissa on tyhjä solu.

Excelissä on useita tapoja kuoria tämä tietokissa.

Rivien poistamismenetelmä riippuu tietojen rakenteesta ja solun arvosta tai ehdosta, jonka perusteella haluat poistaa nämä rivit.

Tässä opetusohjelmassa näytän sinulle useita tapoja poistaa rivejä Excelissä solun arvon tai ehdon perusteella.

Suodata rivit arvon/ehdon perusteella ja poista se sitten

Yksi nopeimmista tavoista poistaa tietyn arvon sisältävät tai tietyn ehdon täyttävät rivit on suodattaa ne. Kun sinulla on suodatetut tiedot, voit poistaa kaikki nämä rivit (kun taas loput rivit pysyvät ennallaan).

Excel -suodatin on melko monipuolinen ja voit suodattaa monien kriteerien perusteella (kuten teksti, numerot, päivämäärät ja värit)

Katsotaanpa kaksi esimerkkiä, joissa voit suodattaa rivit ja poistaa ne.

Poista tietyn tekstin sisältävät rivit

Oletetaan, että sinulla on alla olevan kuvan mukainen tietojoukko ja haluat poistaa kaikki rivit, joilla alue on Keski-länsi (sarakkeessa B).

Tässä pienessä tietojoukossa voit poistaa nämä rivit manuaalisesti, mutta usein tietojoukot ovat valtavia, jolloin rivien poistaminen manuaalisesti ei ole vaihtoehto.

Tässä tapauksessa voit suodattaa kaikki tietueet, joissa alue on Keski-Länsi, ja poistaa sitten kaikki nämä rivit (pitäen muut rivit ennallaan).

Alla on vaiheet rivien poistamiseksi arvon perusteella (kaikki Keskilännen tietueet):

1. Valitse mikä tahansa tietojoukon solu, josta haluat poistaa rivit
2. Napsauta Data -välilehteä
3. Napsauta Lajittele ja suodata -ryhmässä Suodatin -kuvaketta. Tämä käyttää suodattimia kaikkiin tietojoukon otsikkosoluihin
4. Napsauta Suodatin-kuvaketta Alue-otsikkosolussa (tämä on pieni alaspäin osoittava kolmion kuvake solun oikeassa yläkulmassa)
5. Poista kaikkien muiden vaihtoehtojen valinta, paitsi Keski-Länsi-vaihtoehto (nopea tapa tehdä tämä napsauttamalla Valitse kaikki -vaihtoehtoa ja napsauttamalla sitten Keski-Länsi-vaihtoehtoa). Tämä suodattaa tietojoukon ja näyttää vain Keskilännen alueen tietueet.
6. Valitse kaikki suodatetut tietueet
7. Napsauta hiiren kakkospainikkeella mitä tahansa valittuja soluja ja napsauta Poista rivi
8. Napsauta avautuvassa valintaikkunassa OK. Tässä vaiheessa tietojoukossa ei näy tietueita.
9. Napsauta Tiedot -välilehteä ja napsauta Suodatin -kuvaketta. Tämä poistaa suodattimen ja näet kaikki tietueet paitsi poistetut.

Yllä olevat vaiheet suodattavat tiedot ensin solun arvon perusteella (tai voivat olla muita ehtoja, kuten päivämäärän jälkeen/ennen tai suurempi tai pienempi kuin luku). Kun olet tallentanut tietueet, voit yksinkertaisesti poistaa ne.

Hyödyllisiä pikanäppäimiä prosessin nopeuttamiseksi:

1. Ctrl + Vaihto + L suodattimen käyttämiseksi tai poistamiseksi
2. Control + - (pidä ohjausnäppäintä painettuna ja paina miinusnäppäintä) poistaaksesi valitut solut/rivit

Yllä olevassa esimerkissä minulla oli vain neljä erillistä aluetta, ja voisin valita sen manuaalisesti ja poistaa sen valinnan Suodatin -luettelosta (edellä vaiheessa 5).

Jos sinulla on paljon luokkia/alueita, voit kirjoittaa nimen suoraan ruudun yläpuolella olevaan kenttään (jossa on nämä alueiden nimet), ja Excel näyttää vain ne tietueet, jotka vastaavat syötettyä tekstiä (kuten alla). Kun sinulla on suodatettava teksti, paina Enter -näppäintä.

Huomaa, että kun poistat rivin, kaikki näiden rivien muissa soluissa olevat tiedot menetetään. Yksi tapa kiertää tämä on luoda kopio toisessa laskentataulukossa olevista tiedoista ja poistaa kopioidun datan rivit. Kun olet valmis, kopioi se takaisin alkuperäisten tietojen tilalle.

Tai

Voit käyttää myöhemmin tässä opetusohjelmassa esitettyjä menetelmiä (käyttämällä lajittelumenetelmää tai Etsi kaikki -menetelmää)

Poista rivit numeerisen ehdon perusteella

Aivan kuten poistin suodatinmenetelmällä kaikki rivit, jotka sisältävät tekstin Mid-West, voit käyttää myös numeroehtoa (tai päivämääräehtoa).

Oletetaan esimerkiksi, että minulla on alla oleva tietojoukko ja haluan poistaa kaikki rivit, joiden myyntiarvo on alle 200.

Alla on vaiheet tämän tekemiseen:

1. Valitse mikä tahansa solu tiedoista
2. Napsauta Data -välilehteä
3. Napsauta Lajittele ja suodata -ryhmässä Suodatin -kuvaketta. Tämä käyttää suodattimia kaikkiin tietojoukon otsikkosoluihin
4. Napsauta Suodatin-kuvaketta Myynti-otsikkosolussa (tämä on pieni alaspäin osoittava kolmion kuvake solun oikeassa yläkulmassa)
5. Vie hiiri Numerosuodattimet -vaihtoehdon päälle. Tämä näyttää kaikki numeroihin liittyvät suodatinvaihtoehdot Excelissä.
6. Napsauta vaihtoehtoa "alle".
7. Kirjoita avautuvaan mukautetun automaattisen suodattimen valintaikkunaan arvo 200
8. Napsauta OK. Tämä suodattaa ja näyttää vain ne tietueet, joiden myyntiarvo on alle 200
9. Valitse kaikki suodatetut tietueet
10. Napsauta hiiren kakkospainikkeella mitä tahansa solua ja napsauta Poista rivi
11. Napsauta avautuvassa valintaikkunassa OK. Tässä vaiheessa tietojoukossa ei näy tietueita.
12. Napsauta Tiedot -välilehteä ja napsauta Suodatin -kuvaketta. Tämä poistaa suodattimen ja näet kaikki tietueet paitsi poistetut.

Excelissä voi käyttää monia numerosuodattimia - kuten pienempi/suurempi kuin, yhtä/ei ole sama, top 10, keskiarvon ylä- tai alapuolella jne.

Huomautus: Voit käyttää myös useita suodattimia. Voit esimerkiksi poistaa kaikki rivit, joiden myyntiarvo on yli 200 mutta alle 500. Tässä tapauksessa sinun on käytettävä kahta suodatusehtoa. Mukautetun automaattisen suodattimen valintaikkunassa on kaksi suodatusehtoa (JA sekä TAI).

Aivan kuten numerosuodattimet, voit myös suodattaa tietueet päivämäärän perusteella. Jos haluat esimerkiksi poistaa kaikki ensimmäisen vuosineljänneksen tietueet, voit tehdä sen käyttämällä samoja yllä olevia vaiheita. Kun työskentelet päivämääräsuodattimien kanssa, Excel näyttää automaattisesti asiaankuuluvat suodattimet (kuten alla).

Vaikka suodatus on loistava tapa poistaa rivit nopeasti arvon tai ehdon perusteella, sillä on yksi haittapuoli - se poistaa koko rivin. Esimerkiksi alla olevassa tapauksessa se poistaa kaikki suodatetun tietojoukon oikealla puolella olevat tiedot.

Entä jos haluan vain poistaa tietueet tietojoukosta, mutta haluan pitää loput tiedot ennallaan.

Et voi tehdä sitä suodattamalla, mutta voit tehdä sen lajittelemalla.

Lajittele tietojoukko ja poista sitten rivit

Vaikka lajittelu on toinen tapa poistaa rivejä arvon perusteella, mutta useimmissa tapauksissa sinun on parempi käyttää yllä kuvattua suodatusmenetelmää.

Tätä lajittelutekniikkaa suositellaan vain, jos haluat poistaa solut, joissa on arvot, ei koko riviä.

Oletetaan, että sinulla on alla näkyvä tietojoukko ja haluat poistaa kaikki tietueet, joissa alue on Keski-länsi.

Alla on vaiheet tämän tekemiseen lajittelun avulla:

1. Valitse mikä tahansa solu tiedoista
2. Napsauta Data -välilehteä
3. Napsauta Lajittele ja suodata -ryhmässä Lajittele -kuvaketta.
4. Valitse avautuvassa Lajittele -valintaikkunassa Lajittele sarakkeessa -alue.
5. Varmista, että Lajittele -vaihtoehdossa Solun arvot on valittuna
6. Valitse tilausvaihtoehdosta A - Z (tai Z - A, sillä ei ole väliä).
7. Napsauta OK. Tämä antaa sinulle lajitellun tietojoukon alla kuvatulla tavalla (lajiteltu sarakkeen B mukaan).
8. Valitse kaikki tietueet, joiden alue on Keski-Länsi (kaikki rivien solut, ei vain alue -sarake)
9. Kun olet valinnut, napsauta hiiren kakkospainikkeella ja napsauta sitten Poista. Tämä avaa Poista -valintaikkunan.
10. Varmista, että Vaihda solut ylös -vaihtoehto on valittuna.
11. Napsauta OK.

Yllä olevat vaiheet poistaisivat kaikki tietueet, joissa alue oli Keski-Länsi, mutta se ei poista koko riviä. Joten jos sinulla on tietoja tietojoukon oikealla tai vasemmalla puolella, se pysyy vahingoittumattomana.

Yllä olevassa esimerkissä olen lajitellut tiedot solun arvon perusteella, mutta voit myös käyttää samoja vaiheita lajitteluun numeroiden, päivämäärien, solun värin tai kirjasimen värin jne. Perusteella.

Tässä on yksityiskohtainen opas tietojen lajitteluun Excelissä

Jos haluat säilyttää alkuperäisen tietojoukon järjestyksen mutta poistaa tietueet ehtojen perusteella, sinulla on oltava tapa järjestää tiedot takaisin alkuperäiseen. Voit tehdä tämän lisäämällä sarakkeen sarjanumeroilla ennen tietojen lajittelua. Kun olet poistanut rivit/tietueet, lajittele yksinkertaisesti käyttämällä tätä lisättyä saraketta.

Etsi ja valitse solut solun arvon perusteella ja poista sitten rivit

Excelissä on Etsi ja korvaa -toiminto, joka voi olla hyvä, kun haluat löytää ja valita soluja, joilla on tietty arvo.

Kun olet valinnut nämä solut, voit poistaa rivit helposti.

Oletetaan, että sinulla on alla näkyvä tietojoukko ja haluat poistaa kaikki rivit, joilla alue on Keski-Länsi.

Alla on vaiheet tämän tekemiseen:

1. Valitse koko tietojoukko
2. Napsauta Etusivu -välilehteä
3. Napsauta muokkausryhmässä Etsi ja valitse -vaihtoehtoa ja valitse sitten Etsi (voit käyttää myös pikanäppäintä Control + F).
4. Kirjoita Etsi ja korvaa -valintaikkunaan teksti "Keskilänsi" Etsi mitä: -kenttään.
5. Napsauta Etsi kaikki. Tämä näyttää sinulle välittömästi kaikki Mid-West-tekstin esiintymät, jotka Excel löysi.
6. Käytä näppäinyhdistelmää Control + A valitaksesi kaikki Excelin löytämät solut. Voit myös nähdä kaikki tietojoukon valitut solut.
7. Napsauta hiiren kakkospainikkeella mitä tahansa valittuja soluja ja napsauta Poista. Tämä avaa Poista -valintaikkunan.
8. Valitse Koko rivi -vaihtoehto
9. Napsauta OK.

Yllä olevat vaiheet poistaisivat kaikki solut, joissa alueen arvo on Keskilänsi.

Huomautus: Koska Find and Replace voi käsitellä jokerimerkkejä, voit käyttää niitä etsiessäsi tietoja Excelistä. Jos haluat esimerkiksi poistaa kaikki rivit, joilla alue on joko Keskilänsi tai Lounais, voit käyttää*Länsi"Etsi ja korvaa -valintaikkunan tekstinä. Tämä antaa sinulle kaikki solut, joissa teksti päättyy sanaan West.

Poista kaikki rivit tyhjällä solulla

Jos haluat poistaa kaikki rivit, joissa on tyhjiä soluja, voit tehdä tämän helposti Excelin sisäänrakennetulla toiminnolla.

Se on Siirry erityisiin soluihin vaihtoehto - jonka avulla voit valita nopeasti kaikki tyhjät solut. Ja kun olet valinnut kaikki tyhjät solut, niiden poistaminen on erittäin yksinkertaista.

Oletetaan, että sinulla on alla näkyvä tietojoukko ja haluan poistaa kaikki rivit, joilla minulla ei ole myynti -arvoa.

Alla on vaiheet tämän tekemiseen:

1. Valitse koko tietojoukko (tässä tapauksessa A1: D16).
2. paina F5 näppäintä. Tämä avaa Siirry -valintaikkunan (Voit saada tämän valintaikkunan myös Koti -> Muokkaus -> Etsi ja valitse -> Siirry).
3. Napsauta Siirry -valintaikkunassa Erikoispainiketta. Tämä avaa "Siirry erityiseen" -valintaikkunan
4. Valitse Siirry erikoisvalintaikkunaan "Tyhjät".
5. Napsauta OK.

Yllä olevat vaiheet valitsevat kaikki solut, jotka ovat tyhjiä tietojoukossa.

Kun olet valinnut tyhjät solut, napsauta hiiren kakkospainikkeella mitä tahansa solua ja napsauta Poista.

Valitse Poista -valintaikkunassa Koko rivi -vaihtoehto ja napsauta OK. Tämä poistaa kaikki rivit, joissa on tyhjiä soluja.

Jos olet kiinnostunut oppimaan lisää tästä tekniikasta, kirjoitin yksityiskohtaisen opetusohjelman tyhjien solujen rivien poistamisesta. Se sisältää Go to Special -menetelmän sekä VBA -menetelmän tyhjien solujen rivien poistamiseksi.

Suodata ja poista rivejä solun arvon perusteella (VBA: n avulla)

Viimeinen menetelmä, jonka näytän sinulle, sisältää hieman VBA: ta.

Voit käyttää tätä menetelmää, jos sinun on usein poistettava rivit sarakkeen tietyn arvon perusteella. Voit lisätä VBA -koodin kerran ja lisätä sen henkilökohtaiseen makro -työkirjaasi. Näin se on käytettävissä kaikissa Excel -työkirjoissa.

Tämä koodi toimii samalla tavalla kuin yllä kuvattu suodatinmenetelmä (paitsi että tämä suorittaa kaikki taustaohjelman vaiheet ja säästää joitain napsautuksia).

Oletetaan, että sinulla on alla näkyvä tietojoukko ja haluat poistaa kaikki rivit, joilla alue on Keski-Länsi.

Alla on VBA -koodi, joka tekee tämän.

Sub DeleteRowsWithSpecificText () 'Lähde: 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

Yllä oleva koodi käyttää VBA-automaattisuodatinmenetelmää suodattaakseen ensin rivit määritettyjen ehtojen perusteella (joka on "Mid-West"), valitse sitten kaikki suodatetut rivit ja poista se.

Huomaa, että olen käyttänyt Offsetia yllä olevassa koodissa varmistaakseni, että otsikkoriviäni ei poisteta.

Yllä oleva koodi ei toimi, jos tietosi ovat Excel -taulukossa. Syynä tähän on se, että Excel pitää Excel -taulukkoa luettelo -objektina. Joten jos haluat poistaa taulukon rivejä, sinun on muokattava koodia hieman (käsitelty myöhemmin tässä opetusohjelmassa).

Ennen kuin poistat rivit, se näyttää alla olevan kehotteen. Pidän tätä hyödyllisenä, koska sen avulla voin tarkistaa suodatetun rivin ennen poistamista.

Muista, että kun poistat rivejä VBA: n avulla, et voi kumota tätä muutosta. Joten käytä tätä vain, kun olet varma, että tämä toimii haluamallasi tavalla. Lisäksi on hyvä säilyttää varmuuskopio tiedoista siltä varalta, että jokin menee pieleen.

Jos tiedot ovat Excel -taulukossa, poista alla olevan koodin avulla rivit, joilla on tietty arvo:

Sub DeleteRowsinTables () 'Lähde: 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

Koska VBA pitää Excel -taulukkoa luettelo -objektina (eikä alueena), minun piti vaihtaa koodi vastaavasti.

Minne VBA -koodi laitetaan?

Tämä koodi on sijoitettava moduulin VB Editor -taustaohjelmaan.

Alla on vaiheet, jotka osoittavat, miten tämä tehdään:

1. Avaa työkirja, johon haluat lisätä tämän koodin.
2. Avaa VBA Editor -ikkuna pikanäppäimellä ALT + F11.
3. Tässä VBA Editor -ikkunan vasemmalla puolella on Project Explorer -ruutu (joka luettelee kaikki työkirjat ja laskentataulukko -objektit). Napsauta hiiren kakkospainikkeella mitä tahansa työkirjan objektia (jossa haluat tämän koodin toimivan), vie hiiri Lisää-kohdan päälle ja napsauta sitten Moduuli. Tämä lisää moduuliobjektin työkirjaan ja avaa myös moduulin koodi -ikkunan oikealla puolella
4. Kopioi ja liitä yllä oleva koodi moduuli -ikkunassa (joka näkyy oikealla).

Kun olet saanut koodin VB Editorissa, voit suorittaa koodin jollakin seuraavista tavoista (varmista, että olet valinnut minkä tahansa solun tietojoukosta, jolle haluat suorittaa tämän koodin):

1. Valitse mikä tahansa rivi koodista ja paina F5 -näppäintä.
2. Napsauta VB -editorin työkalupalkin Suorita -painiketta
3. Määritä makro painikkeelle tai muodolle ja suorita se napsauttamalla sitä laskentataulukossa
4. Lisää se Pikatyökalupalkkiin ja suorita koodi yhdellä napsautuksella.

Tässä artikkelissa voit lukea kaiken makrokoodin suorittamisesta Excelissä.

Huomautus: Koska työkirja sisältää VBA-makrokoodin, sinun on tallennettava se makrotoimintoon (xlsm).