Odstráňte riadky na základe hodnoty bunky (alebo podmienky) v programe Excel (jednoduchý sprievodca)

Pri práci s veľkými množinami údajov môže byť potrebné rýchlo odstrániť riadky na základe hodnôt buniek v nich (alebo na základe podmienky).

Zvážte napríklad nasledujúce príklady:

1. Máte údaje obchodného zástupcu a chcete odstrániť všetky záznamy pre konkrétny región alebo produkt.
2. Chcete odstrániť všetky záznamy, kde je predajná hodnota nižšia ako 100.
3. Chcete odstrániť všetky riadky, kde je prázdna bunka.

V programe Excel existuje niekoľko spôsobov, ako stiahnuť túto údajovú mačku.

Metóda, ktorú sa rozhodnete odstrániť, bude závisieť od toho, ako sú vaše údaje štruktúrované a aká je hodnota alebo podmienka bunky, na základe ktorých chcete tieto riadky odstrániť.

V tomto tutoriále vám ukážem niekoľko spôsobov, ako odstrániť riadky v programe Excel na základe hodnoty bunky alebo podmienky.

Filtrujte riadky podľa hodnoty/podmienky a potom ich odstráňte

Jedným z najrýchlejších spôsobov, ako odstrániť riadky obsahujúce konkrétnu hodnotu alebo splniť danú podmienku, je ich filtrovať. Keď budete mať filtrované údaje, môžete všetky tieto riadky odstrániť (pričom zostávajúce riadky zostanú nedotknuté).

Excel filter je veľmi univerzálny a môžete filtrovať na základe mnohých kritérií (napríklad text, čísla, dátumy a farby)

Pozrime sa na dva príklady, kde môžete filtrovať riadky a odstrániť ich.

Odstráňte riadky, ktoré obsahujú konkrétny text

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete odstrániť všetky riadky, v ktorých je región Stredozápad (v stĺpci B).

Aj keď sa v tejto malej množine údajov môžete rozhodnúť odstrániť tieto riadky ručne, často budú vaše súbory údajov obrovské, kde manuálne odstraňovanie riadkov nebude možné.

V takom prípade môžete filtrovať všetky záznamy, kde je región stredozápad, a potom odstrániť všetky tieto riadky (pričom ostatné riadky ponecháte nedotknuté).

Nasledujú kroky na odstránenie riadkov na základe hodnoty (všetky záznamy o stredozápade):

1. Vyberte ľubovoľnú bunku v množine údajov, z ktorej chcete odstrániť riadky
2. Kliknite na kartu Údaje
3. V skupine „Zoradiť a filtrovať“ kliknite na ikonu Filter. Toto použije filtre na všetky bunky hlavičky v množine údajov
4. Kliknite na ikonu Filter v bunke hlavičky regiónu (jedná sa o malú ikonu trojuholníka smerujúceho nadol v pravom hornom rohu bunky)
5. Zrušte výber všetkých ostatných možností okrem možnosti Stredozápad (rýchly spôsob, ako to urobiť, je kliknúť na možnosť Vybrať všetko a potom kliknúť na možnosť Stredozápad). Toto bude filtrovať množinu údajov a zobrazovať vám iba záznamy pre stredozápadný región.
6. Vyberte všetky filtrované záznamy
7. Kliknite pravým tlačidlom myši na ktorúkoľvek z vybratých buniek a kliknite na položku „Odstrániť riadok“
8. V dialógovom okne, ktoré sa otvorí, kliknite na tlačidlo OK. V tomto okamihu v množine údajov neuvidíte žiadne záznamy.
9. Kliknite na kartu Údaje a potom na ikonu Filter. Týmto odstránite filter a uvidíte všetky záznamy okrem odstránených.

Vyššie uvedené kroky najskôr filtrujú údaje na základe hodnoty bunky (alebo to môžu byť iné podmienky, ako napríklad po/pred dátumom alebo vyššie/menej ako číslo). Keď máte záznamy, jednoducho ich odstránite.

Niekoľko užitočných skratiek, ktoré je potrebné urýchliť:

1. Ctrl + Shift + L použiť alebo odstrániť filter
2. Ovládanie + - (podržaním ovládacieho klávesu a stlačením klávesu mínus) odstránite vybraté bunky/riadky

Vo vyššie uvedenom príklade som mal iba štyri odlišné oblasti a mohol som ich manuálne vybrať a zrušiť výber zo zoznamu filtrov (v krokoch 5 vyššie).

V prípade, že máte veľa kategórií/oblastí, môžete zadať názov do poľa priamo nad poľom (ktoré obsahuje tieto názvy regiónov) a Excel vám zobrazí iba tie záznamy, ktoré zodpovedajú zadanému textu (ako je uvedené nižšie). Keď máte text, na základe ktorého chcete filtrovať, stlačte kláves Enter.

Upozorňujeme, že keď odstránite riadok, stratí sa všetko, čo môžete mať v iných bunkách v týchto riadkoch. Jedným zo spôsobov, ako to obísť, je vytvoriť kópiu údajov v inom pracovnom hárku a odstrániť riadky v skopírovaných údajoch. Po dokončení ho skopírujte späť na miesto pôvodných údajov.

Alebo

Môžete použiť metódy uvedené ďalej v tomto návode (pomocou metódy zoradenia alebo metódy nájsť všetky)

Odstráňte riadky na základe číselných podmienok

Rovnako ako som použil metódu filtrovania na odstránenie všetkých riadkov, ktoré obsahujú text Stredozápad, môžete použiť aj číselnú podmienku (alebo podmienku dátumu).

Predpokladajme napríklad, že mám nižšie uvedenú množinu údajov a chcem odstrániť všetky riadky, v ktorých je hodnota predaja nižšia ako 200.

Nasledujú kroky, ako to urobiť:

1. Vyberte ľubovoľnú bunku v údajoch
2. Kliknite na kartu Údaje
3. V skupine „Zoradiť a filtrovať“ kliknite na ikonu Filter. To použije filtre na všetky bunky hlavičky v množine údajov
4. Kliknite na ikonu Filter v bunke hlavičky Predaj (jedná sa o malú ikonu trojuholníka smerujúceho nadol v pravom hornom rohu bunky)
5. Ukážte kurzorom na možnosť Filtre čísel. To vám ukáže všetky možnosti filtra súvisiace s číslom v programe Excel.
6. Kliknite na možnosť „Menej ako“.
7. V dialógovom okne „Vlastný automatický filter“, ktoré sa otvorí, zadajte do poľa hodnotu „200“
8. Kliknite na tlačidlo OK. Toto bude filtrovať a zobrazovať iba tie záznamy, kde je hodnota predaja nižšia ako 200
9. Vyberte všetky filtrované záznamy
10. Kliknite pravým tlačidlom myši na ľubovoľnú bunku a kliknite na položku Odstrániť riadok
11. V dialógovom okne, ktoré sa otvorí, kliknite na tlačidlo OK. V tomto okamihu v množine údajov neuvidíte žiadne záznamy.
12. Kliknite na kartu Údaje a potom na ikonu Filter. Týmto odstránite filter a uvidíte všetky záznamy okrem odstránených.

V programe Excel môžete použiť mnoho číselných filtrov - napríklad menší/väčší ako, rovný/nerovný, medzi 10 najlepších, nad alebo pod priemerom atď.

Poznámka: Môžete použiť aj viac filtrov. Môžete napríklad odstrániť všetky riadky, v ktorých je hodnota predaja väčšia ako 200, ale menšia ako 500. V takom prípade musíte použiť dve podmienky filtra. Dialógové okno Vlastný automatický filter umožňuje mať dve filtračné kritériá (A rovnako ako ALEBO).

Rovnako ako filtre s číslami, môžete filtrovať záznamy aj podľa dátumu. Ak napríklad chcete odstrániť všetky záznamy za prvý štvrťrok, môžete to urobiť pomocou rovnakých krokov vyššie. Keď pracujete s filtrami dátumu, Excel vám automaticky zobrazí relevantné filtre (ako je uvedené nižšie).

Aj keď je filtrovanie skvelý spôsob, ako rýchlo odstrániť riadky na základe hodnoty alebo podmienky, má jednu nevýhodu - odstráni celý riadok. V nižšie uvedenom prípade by napríklad vymazal všetky údaje, ktoré sú napravo od filtrovanej množiny údajov.

Čo keď chcem iba odstrániť záznamy z množiny údajov, ale chcem, aby zostávajúce údaje zostali nedotknuté.

Pomocou filtrovania to nemôžete urobiť, ale môžete to urobiť zoradením.

Zoraďte množinu údajov a potom odstráňte riadky

Aj keď je triedenie ďalším spôsobom odstraňovania riadkov podľa hodnoty, vo väčšine prípadov je však lepšie použiť vyššie uvedenú metódu filtrovania.

Táto metóda triedenia sa odporúča iba vtedy, ak chcete odstrániť bunky s hodnotami, a nie celé riadky.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete odstrániť všetky záznamy, kde je región Stredozápad.

Nasledujú kroky, ako to urobiť pomocou triedenia:

1. Vyberte ľubovoľnú bunku v údajoch
2. Kliknite na kartu Údaje
3. V skupine Zoradiť a filtrovať kliknite na ikonu Zoradiť.
4. V dialógovom okne Zoradiť, ktoré sa otvorí, vyberte oblasť v stĺpci zoradiť.
5. V možnosti Zoradiť pri uistite sa, že je vybratá hodnota buniek
6. V možnosti Poradie vyberte A až Z (alebo Z až A, na tom nezáleží).
7. Kliknite na tlačidlo OK. Získate tak setriedený súbor údajov, ako je uvedené nižšie (zoradené podľa stĺpca B).
8. Vyberte všetky záznamy s regiónom Stredozápad (všetky bunky v riadkoch, nielen stĺpec oblasti)
9. Po výbere kliknite pravým tlačidlom myši a potom kliknite na položku Odstrániť. Otvorí sa dialógové okno Odstrániť.
10. Uistite sa, že je vybratá možnosť „Posunúť bunky nahor“.
11. Kliknite na tlačidlo OK.

Vyššie uvedené kroky by odstránili všetky záznamy, kde bol región Stredozápad, ale neodstráni celý riadok. Ak teda máte údaje napravo alebo naľavo od súboru údajov, zostanú nepoškodené.

Vo vyššie uvedenom príklade som zoradil údaje podľa hodnoty bunky, ale rovnaké kroky môžete použiť aj na zoradenie podľa čísel, dátumov, farby bunky alebo farby písma atď.

Tu je podrobný návod na triedenie údajov v programe Excel

V prípade, že chcete zachovať pôvodný poriadok množiny údajov, ale odstrániť záznamy na základe kritérií, musíte mať spôsob, ako triediť údaje späť k pôvodnému. Za týmto účelom pred zoradením údajov pridajte stĺpec so sériovými číslami. Keď ste s odstraňovaním riadkov/záznamov skončili, jednoducho zoraďte podľa tohto extra stĺpca, ktorý ste pridali.

Nájdite a vyberte bunky podľa hodnoty bunky a potom odstráňte riadky

Excel má funkciu Hľadať a nahradiť, ktorá môže byť skvelá, keď chcete nájsť a vybrať bunky s konkrétnou hodnotou.

Hneď ako vyberiete tieto bunky, môžete riadky ľahko odstrániť.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete odstrániť všetky riadky, v ktorých je región Stredozápad.

Nasledujú kroky, ako to urobiť:

1. Vyberte celú množinu údajov
2. Kliknite na kartu Domov
3. V skupine Úpravy kliknite na možnosť „Nájsť a vybrať“ a potom kliknite na položku Hľadať (môžete tiež použiť klávesovú skratku Ctrl + F).
4. V dialógovom okne Hľadať a nahradiť zadajte do poľa „Čo nájsť:“ text „Stredozápad“.
5. Kliknite na položku Nájsť všetko. To vám okamžite ukáže všetky inštancie textu Mid-West, ktoré bol Excel schopný nájsť.
6. Pomocou klávesovej skratky Ctrl + A vyberte všetky bunky, ktoré Excel našiel. Budete tiež môcť vidieť všetky vybraté bunky v množine údajov.
7. Kliknite pravým tlačidlom myši na ktorúkoľvek z vybratých buniek a kliknite na položku Odstrániť. Otvorí sa dialógové okno Odstrániť.
8. Vyberte možnosť „Celý riadok“
9. Kliknite na tlačidlo OK.

Vyššie uvedené kroky odstránia všetky bunky, v ktorých je hodnota oblasti stredozápadná.

Poznámka: Pretože funkcia Nájsť a nahradiť dokáže spracovať zástupné znaky, môžete ich použiť pri vyhľadávaní údajov v programe Excel. Ak napríklad chcete odstrániť všetky riadky, v ktorých je región stredozápadný alebo juhozápadný, môžete použiť príkaz „*Západ„Ako text, ktorý sa má nájsť v dialógovom okne Hľadať a nahradiť. Tým získate všetky bunky, kde text končí slovom Západ.

Odstráňte všetky riadky s prázdnou bunkou

V prípade, že chcete odstrániť všetky riadky, kde sú prázdne bunky, môžete to ľahko urobiť pomocou vstavanej funkcie v programe Excel.

To je Prejdite na špeciálne bunky možnosť - ktorá vám umožní rýchlo vybrať všetky prázdne bunky. Akonáhle vyberiete všetky prázdne bunky, ich odstránenie je veľmi jednoduché.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcem odstrániť všetky riadky, v ktorých nemám predajnú hodnotu.

Nasledujú kroky, ako to urobiť:

1. Vyberte celý súbor údajov (v tomto prípade A1: D16).
2. Stlačte tlačidlo F5 kľúč. Otvorí sa dialógové okno „Prejsť na“ (Toto dialógové okno môžete získať aj z domovskej stránky -> Úpravy -> Nájsť a vybrať -> Prejsť na).
3. V dialógovom okne „Prejsť na“ kliknite na tlačidlo Špeciálne. Otvorí sa dialógové okno „Prejsť na špeciálne“
4. V dialógovom okne Prejsť na špeciálne vyberte položku „Prázdne miesta“.
5. Kliknite na tlačidlo OK.

Vyššie uvedené kroky by vybrali všetky bunky, ktoré sú v množine údajov prázdne.

Keď máte vybraté prázdne bunky, kliknite pravým tlačidlom myši na ktorúkoľvek z buniek a kliknite na položku Odstrániť.

V dialógovom okne Odstrániť vyberte možnosť „Celý riadok“ a kliknite na tlačidlo OK. Odstránia sa tým všetky riadky, v ktorých sú prázdne bunky.

Ak máte záujem dozvedieť sa viac o tejto technike, napísal som podrobný návod, ako odstrániť riadky s prázdnymi bunkami. Obsahuje metódu „Prejsť na špeciálne“ a metódu VBA na odstránenie riadkov s prázdnymi bunkami.

Filtrovať a odstraňovať riadky podľa hodnoty bunky (pomocou jazyka VBA)

Posledná metóda, ktorú vám ukážem, zahŕňa trochu VBA.

Túto metódu môžete použiť, ak často potrebujete odstrániť riadky na základe konkrétnej hodnoty v stĺpci. Kód VBA môžete pridať raz a pridať ho do svojho osobného zošita makra. Vďaka tomu bude k dispozícii na použitie vo všetkých vašich zošitoch programu Excel.

Tento kód funguje rovnako ako vyššie uvedená metóda filtra (okrem skutočnosti, že sa tým vykonajú všetky kroky v backende a ušetrí vám to niekoľko kliknutí).

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete odstrániť všetky riadky, v ktorých je región Stredozápad.

Nasleduje kód VBA, ktorý to urobí.

Sub DeleteRowsWithSpecificText () 'Zdroj: https: //trumpexcel.com/delete-rows-based-on-cell-value/ ActiveCell.AutoFilter Pole: = 2, Criteria1: = "Mid-West" ActiveSheet.AutoFilter.Range.Offset (1, 0). Riadky. Špeciálne bunky (xlCellTypeVisible). Odstrániť koncovú podkapitolu

Vyššie uvedený kód používa metódu automatického filtrovania VBA na prvé filtrovanie riadkov na základe zadaných kritérií (čo je „stredozápad“), potom vyberte všetky filtrované riadky a odstráňte ich.

Všimnite si toho, že vo vyššie uvedenom kóde som použil Ofset, aby som sa ubezpečil, že môj riadok hlavičky nie je odstránený.

Vyššie uvedený kód nefunguje, ak sú vaše údaje v tabuľke programu Excel. Dôvodom je to, že Excel považuje tabuľku programu Excel za objekt zoznamu. Ak teda chcete odstrániť riadky, ktoré sú v tabuľke, musíte kód trochu upraviť (popísané ďalej v tomto návode).

Pred odstránením riadkov sa vám zobrazí výzva, ako je uvedené nižšie. Považujem to za užitočné, pretože mi to umožňuje pred odstránením znova skontrolovať filtrovaný riadok.

Pamätajte si, že keď odstraňujete riadky pomocou VBA, nemôžete túto zmenu vrátiť späť. Používajte to teda iba vtedy, ak ste si istí, že to funguje tak, ako chcete. Je tiež vhodné ponechať si záložnú kópiu údajov pre prípad, že sa niečo pokazí.

V prípade, že sú vaše údaje v tabuľke programu Excel, pomocou nižšie uvedeného kódu odstráňte riadky s konkrétnou hodnotou:

Sub DeleteRowsinTables () 'Zdroj: https: //trumpexcel.com/delete-rows-based-on-cell-value/ Dim Tbl As ListObject Set Tbl = ActiveSheet.ListObjects (1) ActiveCell.AutoFilter Field: = 2, Criteria1: = "Stredozápad" Tbl.DataBodyRange.SpecialCells (xlCellTypeVisible). Vymazať koniec Sub

Pretože VBA považuje tabuľku Excel za objekt zoznamu (a nie za rozsah), musel som kód zodpovedajúcim spôsobom zmeniť.

Kam vložiť kód VBA?

Tento kód je potrebné vložiť do backendu VB editora v module.

Nasledujú kroky, ktoré vám ukážu, ako to urobiť:

1. Otvorte zošit, do ktorého chcete pridať tento kód.
2. Okno editora VBA otvoríte pomocou klávesovej skratky ALT + F11.
3. V tomto okne editora VBA je vľavo tabla „Project Explorer“ (ktorá obsahuje zoznam všetkých objektov zošitov a pracovných hárkov). Kliknite pravým tlačidlom myši na ľubovoľný objekt v zošite (v ktorom má tento kód fungovať), umiestnite kurzor na položku „Vložiť“ a potom kliknite na položku „Modul“. Tým sa objekt Module pridá do zošita a tiež sa otvorí okno kódu modulu vpravo
4. V okne modulu (ktoré sa zobrazí vpravo) skopírujte a prilepte vyššie uvedený kód.

Akonáhle budete mať kód v editore VB, môžete ho spustiť pomocou ľubovoľného z nižšie uvedených spôsobov (uistite sa, že máte vybratú akúkoľvek bunku v množine údajov, na ktorej chcete tento kód spustiť):

1. Vyberte ľubovoľný riadok v kóde a stlačte kláves F5.
2. Kliknite na tlačidlo Spustiť na paneli s nástrojmi v editore VB
3. Priraďte makro k tlačidlu alebo tvaru a spustite ho kliknutím na samotný hárok
4. Pridajte ho na panel s nástrojmi Rýchly prístup a spustite kód jediným kliknutím.

Všetko o tom, ako spustiť kód makra v programe Excel, si môžete prečítať v tomto článku.

Poznámka: Keďže zošit obsahuje kód makra VBA, musíte ho uložiť vo formáte s povolenými makrami (xlsm).