Odstranit řádky na základě hodnoty buňky (nebo podmínky) v aplikaci Excel (snadný průvodce)

Při práci s velkými datovými sadami může být potřeba rychle odstranit řádky na základě hodnot buněk v ní (nebo na základě podmínky).

Zvažte například následující příklady:

1. Máte data obchodního zástupce a chcete odstranit všechny záznamy pro konkrétní oblast nebo produkt.
2. Chcete odstranit všechny záznamy, kde je hodnota prodeje nižší než 100.
3. Chcete odstranit všechny řádky, kde je prázdná buňka.

Existuje několik způsobů, jak stáhnout tuto datovou kočku v aplikaci Excel.

Metoda, kterou se rozhodnete odstranit řádky, bude záviset na tom, jak jsou vaše data strukturována a jaká je hodnota buňky nebo podmínka, na základě kterých chcete tyto řádky odstranit.

V tomto tutoriálu vám ukážu několik způsobů, jak odstranit řádky v aplikaci Excel na základě hodnoty buňky nebo podmínky.

Filtrujte řádky podle hodnoty/podmínky a poté jej odstraňte

Jedním z nejrychlejších způsobů, jak odstranit řádky obsahující konkrétní hodnotu nebo splnit danou podmínku, je filtrovat je. Jakmile budete mít filtrovaná data, můžete všechny tyto řádky odstranit (zatímco zbývající řádky zůstanou nedotčené).

Filtr aplikace Excel je velmi univerzální a můžete filtrovat na základě mnoha kritérií (například textu, čísel, dat a barev)

Podívejme se na dva příklady, kde můžete filtrovat řádky a odstraňovat je.

Odstraňte řádky, které obsahují konkrétní text

Předpokládejme, že máte sadu dat, jak je uvedeno níže, a chcete odstranit všechny řádky, kde je oblast Mid-west (ve sloupci B).

Zatímco v této malé datové sadě se můžete rozhodnout tyto řádky odstranit ručně, často budou vaše datové sady obrovské, kde ruční odstraňování řádků nebude možné.

V takovém případě můžete filtrovat všechny záznamy, kde je oblast Mid-West, a poté odstranit všechny tyto řádky (zatímco ostatní řádky zůstanou nedotčené).

Níže jsou uvedeny kroky k odstranění řádků na základě hodnoty (všechny záznamy Mid-West):

1. Vyberte libovolnou buňku v datové sadě, ze které chcete odstranit řádky
2. Klikněte na kartu Data
3. Ve skupině „Seřadit a filtrovat“ klikněte na ikonu Filtr. To použije filtry na všechny buňky záhlaví v datové sadě
4. Klikněte na ikonu Filtr v buňce záhlaví oblasti (toto je malá ikona trojúhelníku směřující dolů v pravé horní části buňky)
5. Zrušte výběr všech ostatních možností kromě možnosti Středozápad (rychlý způsob, jak toho dosáhnout, je kliknutím na možnost Vybrat vše a poté kliknutím na možnost Středozápad). Tím se filtruje datová sada a zobrazí se pouze záznamy pro oblast Středozápad.
6. Vyberte všechny filtrované záznamy
7. Klikněte pravým tlačítkem na některou z vybraných buněk a klikněte na „Odstranit řádek“
8. V dialogovém okně, které se otevře, klikněte na OK. V tomto okamžiku v datové sadě neuvidíte žádné záznamy.
9. Klikněte na kartu Data a poté na ikonu Filtr. Tím odstraníte filtr a uvidíte všechny záznamy kromě odstraněných.

Výše uvedené kroky nejprve filtrují data na základě hodnoty buňky (nebo to může být jiná podmínka, například po/před datem nebo větší/menší než číslo). Jakmile máte záznamy, jednoduše je smažete.

Některé užitečné zkratky, které je třeba znát, aby se proces urychlil:

1. Control + Shift + L použít nebo vyjmout filtr
2. Ovládání + - (podržte ovládací klávesu a stiskněte klávesu minus) pro odstranění vybraných buněk/řádků

Ve výše uvedeném příkladu jsem měl pouze čtyři odlišné oblasti a mohl jsem je ručně vybrat a zrušit výběr ze seznamu Filtr (v krocích 5 výše).

V případě, že máte mnoho kategorií/oblastí, můžete zadat název do pole přímo nad pole (které obsahuje tyto názvy regionů) a Excel vám zobrazí pouze ty záznamy, které odpovídají zadanému textu (jak je uvedeno níže). Až budete mít text, podle kterého chcete filtrovat, stiskněte klávesu Enter.

Všimněte si, že když odstraníte řádek, vše, co můžete mít v jiných buňkách v těchto řádcích, bude ztraceno. Jedním ze způsobů, jak to obejít, je vytvořit kopii dat v jiném listu a odstranit řádky ve zkopírovaných datech. Až budete hotovi, zkopírujte jej zpět na místo původních dat.

Nebo

Můžete použít metody uvedené dále v tomto kurzu (pomocí metody řazení nebo metody Najít vše)

Odstranit řádky na základě číselné podmínky

Stejně jako jsem použil metodu filtru k odstranění všech řádků, které obsahují text Mid-West, můžete použít také číselnou podmínku (nebo podmínku data).

Předpokládejme například, že mám níže uvedenou datovou sadu a chci odstranit všechny řádky, kde je prodejní hodnota nižší než 200.

Níže jsou uvedeny kroky, jak to provést:

1. Vyberte libovolnou buňku v datech
2. Klikněte na kartu Data
3. Ve skupině „Seřadit a filtrovat“ klikněte na ikonu Filtr. To použije filtry na všechny buňky záhlaví v datové sadě
4. Klikněte na ikonu Filtr v buňce záhlaví Prodej (jedná se o malou ikonu trojúhelníku směřující dolů v pravé horní části buňky)
5. Umístěte kurzor na možnost Filtry čísel. To vám ukáže všechny možnosti filtrování související s čísly v aplikaci Excel.
6. Klikněte na možnost „Méně než“.
7. V dialogovém okně „Vlastní automatický filtr“, které se otevře, zadejte do pole hodnotu „200“
8. Klikněte na OK. To bude filtrovat a zobrazovat pouze ty záznamy, kde je hodnota prodeje nižší než 200
9. Vyberte všechny filtrované záznamy
10. Klikněte pravým tlačítkem na libovolnou buňku a klikněte na Odstranit řádek
11. V dialogovém okně, které se otevře, klikněte na OK. V tomto okamžiku v datové sadě neuvidíte žádné záznamy.
12. Klikněte na kartu Data a poté na ikonu Filtr. Tím odstraníte filtr a uvidíte všechny záznamy kromě odstraněných.

V aplikaci Excel můžete použít mnoho číselných filtrů - například menší než/větší než, rovné/nerovná se, mezi, top 10, nad nebo pod průměrem atd.

Poznámka: Můžete také použít více filtrů. Můžete například odstranit všechny řádky, kde je prodejní hodnota větší než 200, ale menší než 500. V takovém případě musíte použít dvě podmínky filtru. Dialogové okno Vlastní automatický filtr umožňuje mít dvě kritéria filtru (A stejně jako NEBO).

Stejně jako číselné filtry můžete také filtrovat záznamy na základě data. Pokud například chcete odebrat všechny záznamy za první čtvrtletí, můžete to provést pomocí stejných kroků výše. Když pracujete s filtry data, Excel vám automaticky zobrazí relevantní filtry (jak je uvedeno níže).

Zatímco filtrování je skvělý způsob, jak rychle odstranit řádky na základě hodnoty nebo podmínky, má jednu nevýhodu - odstraní celý řádek. Například v níže uvedeném případě by odstranil všechna data, která jsou napravo od filtrované datové sady.

Co když chci pouze odstranit záznamy z datové sady, ale chci zachovat zbývající data nedotčená.

To nemůžete dělat s filtrováním, ale můžete to udělat s tříděním.

Seřaďte datovou sadu a poté odstraňte řádky

Třídění je sice dalším způsobem, jak odstranit řádky na základě hodnoty, ale ve většině případů je lepší použít výše uvedenou metodu filtrování.

Tato metoda třídění se doporučuje pouze v případě, že chcete odstranit buňky s hodnotami a ne celé řádky.

Předpokládejme, že máte datovou sadu, jak je ukázáno níže, a chcete odstranit všechny záznamy, kde je region Mid-west.

Níže jsou uvedeny kroky, jak to provést pomocí řazení:

1. Vyberte libovolnou buňku v datech
2. Klikněte na kartu Data
3. Ve skupině Sort & Filter klikněte na ikonu Sort.
4. V dialogovém okně Seřadit, které se otevře, vyberte oblast ve sloupci řazení.
5. V možnosti Sort on se ujistěte, že jsou vybrány hodnoty buněk
6. V možnosti Pořadí vyberte A až Z (nebo Z na A, na tom nezáleží).
7. Klikněte na OK. Tím získáte seřazený soubor dat, jak je uvedeno níže (seřazeno podle sloupce B).
8. Vyberte všechny záznamy s regionem Mid-West (všechny buňky v řádcích, nejen sloupec oblasti)
9. Po výběru klikněte pravým tlačítkem a poté klikněte na Odstranit. Otevře se dialogové okno Odstranit.
10. Ujistěte se, že je vybrána možnost „Posunout buňky nahoru“.
11. Klikněte na OK.

Výše uvedené kroky by odstranily všechny záznamy, kde byl region Mid-West, ale neodstraní celý řádek. Pokud tedy máte data vpravo nebo vlevo od datové sady, zůstanou nepoškozená.

Ve výše uvedeném příkladu jsem data seřadil podle hodnoty buňky, ale stejné kroky můžete použít také k řazení podle čísel, dat, barvy buňky nebo barvy písma atd.

Zde je podrobný návod, jak třídit data v Excelu

V případě, že chcete zachovat původní pořadí datových sad, ale odstranit záznamy na základě kritérií, musíte mít způsob, jak data seřadit zpět na původní. Chcete -li to provést, přidejte před seřazením dat sloupec se sériovými čísly. Až budete s odstraňováním řádků/záznamů hotovi, jednoduše seřaďte podle tohoto extra sloupce, který jste přidali.

Najděte a vyberte buňky podle hodnoty buňky a poté odstraňte řádky

Excel má funkci Najít a nahradit, která může být skvělá, když chcete najít a vybrat buňky s konkrétní hodnotou.

Jakmile vyberete tyto buňky, můžete řádky snadno odstranit.

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete odstranit všechny řádky, kde je oblast Mid-West.

Níže jsou uvedeny kroky, jak to provést:

1. Vyberte celou datovou sadu
2. Klikněte na kartu Domů
3. Ve skupině Úpravy klikněte na možnost „Najít a vybrat“ a poté klikněte na Najít (můžete také použít klávesovou zkratku Control + F).
4. V dialogovém okně Najít a nahradit zadejte do pole „Najít co:“ text „Středozápad“.
5. Klikněte na Najít vše. To vám okamžitě ukáže všechny instance textu Mid-West, které Excel dokázal najít.
6. Pomocí klávesové zkratky Control + A vyberte všechny buňky, které Excel našel. Rovněž budete moci zobrazit všechny vybrané buňky v datové sadě.
7. Klikněte pravým tlačítkem na některou z vybraných buněk a klikněte na Odstranit. Otevře se dialogové okno Odstranit.
8. Vyberte možnost „Celý řádek“
9. Klikněte na OK.

Výše uvedené kroky by odstranily všechny buňky, kde je hodnota oblasti Mid-west.

Poznámka: Protože funkce Najít a nahradit dokáže zpracovávat zástupné znaky, můžete je použít při hledání dat v aplikaci Excel. Pokud například chcete odstranit všechny řádky, kde je oblast buď Středozápad nebo Jihozápad, můžete použít ‘*Západ‘Jako text k nalezení v dialogovém okně Najít a nahradit. Tím získáte všechny buňky, kde text končí slovem Západ.

Odstranit všechny řádky s prázdnou buňkou

V případě, že chcete odstranit všechny řádky, kde jsou prázdné buňky, můžete to snadno provést pomocí vestavěné funkce v aplikaci Excel.

To je Přejít na speciální buňky možnost - která vám umožní rychle vybrat všechny prázdné buňky. A jakmile vyberete všechny prázdné buňky, jejich odstranění je velmi jednoduché.

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chci odstranit všechny řádky, kde nemám prodejní hodnotu.

Níže jsou uvedeny kroky, jak to provést:

1. Vyberte celou datovou sadu (v tomto případě A1: D16).
2. zmáčkni F5 klíč. Otevře se dialogové okno „Přejít na“ (Toto dialogové okno můžete také získat z nabídky Domů -> Úpravy -> Najít a vybrat -> Přejít na).
3. V dialogovém okně „Přejít na“ klikněte na tlačítko Speciální. Otevře se dialogové okno „Přejít na speciální“
4. V dialogovém okně Přejít na speciální vyberte „Prázdná místa“.
5. Klikněte na OK.

Výše uvedené kroky by vybrali všechny buňky, které jsou v datové sadě prázdné.

Jakmile vyberete prázdné buňky, klikněte pravým tlačítkem na libovolnou buňku a klikněte na Odstranit.

V dialogovém okně Odstranit vyberte možnost „Celý řádek“ a klikněte na OK. Tím odstraníte všechny řádky, které obsahují prázdné buňky.

Pokud máte zájem dozvědět se více o této technice, napsal jsem podrobný návod, jak odstranit řádky s prázdnými buňkami. Obsahuje metodu „Přejít na speciální“ a metodu VBA k odstranění řádků s prázdnými buňkami.

Filtrování a mazání řádků podle hodnoty buňky (pomocí VBA)

Poslední metoda, kterou vám ukážu, obsahuje trochu VBA.

Tuto metodu můžete použít, pokud často potřebujete odstranit řádky na základě konkrétní hodnoty ve sloupci. Kód VBA můžete přidat jednou a přidat jej do svého osobního sešitu maker. Tímto způsobem bude k dispozici pro použití ve všech vašich sešitech aplikace Excel.

Tento kód funguje stejným způsobem jako výše uvedená metoda Filtr (kromě skutečnosti, že se tím provedou všechny kroky v backendu a ušetří se vám kliknutí).

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete odstranit všechny řádky, kde je oblast Mid-West.

Níže je uveden kód VBA, který to provede.

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). Řádky. Speciální buňky (xlCellTypeVisible). Odstranit konec Sub

Výše uvedený kód používá metodu VBA Autofilter k prvnímu filtrování řádků na základě zadaných kritérií (což je „Mid-West“), poté vyberte všechny filtrované řádky a odstraňte je.

Všimněte si, že jsem použil Offset ve výše uvedeném kódu, abych se ujistil, že můj řádek záhlaví není odstraněn.

Výše uvedený kód nefunguje, pokud jsou vaše data v tabulce aplikace Excel. Důvodem je to, že Excel považuje tabulku aplikace Excel za objekt seznamu. Pokud tedy chcete odstranit řádky, které jsou v tabulce, musíte kód trochu upravit (popsáno dále v tomto kurzu).

Před odstraněním řádků se zobrazí výzva, jak je uvedeno níže. Považuji to za užitečné, protože mi to umožňuje před odstraněním znovu zkontrolovat filtrovaný řádek.

Pamatujte, že když odstraníte řádky pomocí VBA, nemůžete tuto změnu vrátit zpět. Použijte to tedy pouze tehdy, když jste si jisti, že to funguje tak, jak chcete. Je také dobré mít záložní kopii dat pro případ, že by se něco pokazilo.

V případě, že jsou vaše data v tabulce Excel, použijte níže uvedený kód k odstranění řádků s konkrétní 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: = "Mid-West" Tbl.DataBodyRange.SpecialCells (xlCellTypeVisible) .Delete End Sub

Vzhledem k tomu, že VBA považuje tabulku Excel za objekt seznamu (a nikoli za rozsah), musel jsem podle toho změnit kód.

Kam vložit kód VBA?

Tento kód je třeba umístit do backendu editoru VB v modulu.

Níže jsou uvedeny kroky, které vám ukážou, jak to provést:

1. Otevřete sešit, do kterého chcete přidat tento kód.
2. Pomocí klávesové zkratky ALT + F11 otevřete okno editoru VBA.
3. V tomto okně editoru VBA vlevo je podokno „Project Explorer“ (které uvádí všechny objekty sešitů a pracovních listů). Klikněte pravým tlačítkem na libovolný objekt v sešitu (ve kterém chcete, aby tento kód fungoval), najeďte kurzorem na „Vložit“ a poté klikněte na „Modul“. Tím se do sešitu přidá objekt Module a také se otevře okno Kód modulu vpravo
4. V okně modulu (které se zobrazí vpravo) zkopírujte a vložte výše uvedený kód.

Jakmile máte kód v editoru VB, můžete jej spustit pomocí kterékoli z níže uvedených metod (ujistěte se, že jste vybrali libovolnou buňku v datové sadě, na které chcete tento kód spustit):

1. Vyberte libovolný řádek v kódu a stiskněte klávesu F5.
2. Klikněte na tlačítko Spustit na panelu nástrojů v editoru VB
3. Přiřaďte makro k tlačítku nebo tvaru a spusťte ho kliknutím na něj v samotném listu
4. Přidejte jej na panel nástrojů Rychlý přístup a spusťte kód jediným kliknutím.

V tomto článku si můžete přečíst vše o tom, jak spustit kód makra v aplikaci Excel.

Poznámka: Protože sešit obsahuje kód makra VBA, musíte jej uložit ve formátu s povolenými makry (xlsm).