Изтриване на редове въз основа на клетъчна стойност (или условие) в Excel (лесно ръководство)

Когато работите с големи набори от данни, може да се наложи бързо да изтриете редове въз основа на стойностите на клетката в него (или въз основа на условие).

Например, разгледайте следните примери:

1. Имате данни за търговски представители и искате да изтриете всички записи за конкретен регион или продукт.
2. Искате да изтриете всички записи, при които стойността на продажбата е по -малка от 100.
3. Искате да изтриете всички редове, където има празна клетка.

Има няколко начина да премахнете тази котка с данни в Excel.

Методът, който избирате за изтриване на редовете, ще зависи от това как са структурирани данните ви и каква е стойността или условието на клетката, въз основа на която искате да изтриете тези редове.

В този урок ще ви покажа няколко начина за изтриване на редове в Excel въз основа на стойност на клетка или условие.

Филтрирайте редовете въз основа на стойност/състояние и след това го изтрийте

Един от най -бързите начини за изтриване на редове, които съдържат конкретна стойност или изпълняват дадено условие, е да ги филтрирате. След като филтрирате данните, можете да изтриете всички тези редове (докато останалите редове остават непокътнати).

Excel филтърът е доста гъвкав и можете да филтрирате въз основа на много критерии (като текст, числа, дати и цветове)

Нека видим два примера, където можете да филтрирате редовете и да ги изтриете.

Изтриване на редове, които съдържат определен текст

Да предположим, че имате набор от данни, както е показано по-долу и искате да изтриете всички редове, където регионът е Среден запад (в колона В).

Въпреки че в този малък набор от данни можете да изберете да изтриете тези редове ръчно, често вашите набори от данни ще бъдат огромни, където изтриването на редове ръчно няма да бъде опция.

В този случай можете да филтрирате всички записи, където регионът е Среден Запад и след това да изтриете всички тези редове (като запазите останалите редове непокътнати).

По-долу са стъпките за изтриване на редове въз основа на стойността (всички записи на Средния Запад):

1. Изберете всяка клетка в набора от данни, от която искате да изтриете редовете
2. Щракнете върху раздела Данни
3. В групата „Сортиране и филтриране“ кликнете върху иконата Филтър. Това ще приложи филтри към всички клетки на заглавките в набора от данни
4. Кликнете върху иконата Филтър в заглавната клетка на региона (това е малка икона с триъгълник, насочена надолу в горния десен ъгъл на клетката)
5. Отменете всички останали опции с изключение на опцията Среден Запад (бърз начин да направите това е като щракнете върху опцията Избери всички и след това щракнете върху опцията Среден Запад). Това ще филтрира набора от данни и ще ви покаже само записи за региона на Средния Запад.
6. Изберете всички филтрирани записи
7. Щракнете с десния бутон върху някоя от избраните клетки и кликнете върху „Изтриване на ред“
8. В диалоговия прозорец, който се отваря, щракнете върху OK. На този етап няма да виждате записи в набора от данни.
9. Щракнете върху раздела Данни и щракнете върху иконата Филтър. Това ще премахне филтъра и ще видите всички записи, с изключение на изтритите.

Горните стъпки първо филтрират данните въз основа на стойността на клетката (или може да бъде друго условие, като след/преди дата или по -голямо/по -малко от число). След като имате записите, просто ги изтривате.

Някои полезни преки пътища, които трябва да знаете, за да ускорите процеса:

1. Control + Shift + L за да приложите или премахнете филтъра
2. Control + - (задръжте клавиша за управление и натиснете клавиша минус), за да изтриете избраните клетки/редове

В горния пример имах само четири отделни региона и можех ръчно да го избера и премахна от списъка с филтри (в стъпки 5 по -горе).

В случай, че имате много категории/региони, можете да въведете името в полето точно над полето (което има тези имена на региони) и Excel ще ви покаже само тези записи, които съответстват на въведения текст (както е показано по -долу). След като имате текст, въз основа на който искате да филтрирате, натиснете клавиша Enter.

Обърнете внимание, че когато изтриете ред, всичко, което може да имате в други клетки в тези редове, ще бъде загубено. Един от начините да заобиколите това е да създадете копие на данните в друг работен лист и да изтриете редовете в копираните данни. След като го направите, копирайте го обратно на първоначалните данни.

Или

Можете да използвате методите, показани по -късно в този урок (като използвате метода на сортиране или метода Find All)

Изтриване на редове въз основа на числово условие

Точно както използвах метода на филтъра, за да изтрия всички редове, които съдържат текста Mid-West, можете да използвате и числово условие (или условие за дата).

Да предположим например, че имам набора от данни по -долу и искам да изтрия всички редове, където стойността на продажбата е по -малка от 200.

По -долу са описани стъпките за това:

1. Изберете всяка клетка в данните
2. Щракнете върху раздела Данни
3. В групата „Сортиране и филтриране“ кликнете върху иконата Филтър. Това ще приложи филтри към всички клетки на заглавките в набора от данни
4. Кликнете върху иконата Филтър в заглавната клетка на Sales (това е малка икона с триъгълник, насочена надолу в горния десен ъгъл на клетката)
5. Задръжте курсора на мишката върху опцията Числови филтри. Това ще ви покаже всички опции за филтриране, свързани с числата в Excel.
6. Кликнете върху опцията „По -малко от“.
7. В диалоговия прозорец „Персонализиран автофилтър“, който се отваря, въведете стойността „200“ в полето
8. Щракнете върху OK. Това ще филтрира и показва само тези записи, при които стойността на продажбите е по -малка от 200
9. Изберете всички филтрирани записи
10. Щракнете с десния бутон върху някоя от клетките и кликнете върху Изтриване на ред
11. В диалоговия прозорец, който се отваря, щракнете върху OK. На този етап няма да виждате записи в набора от данни.
12. Щракнете върху раздела Данни и щракнете върху иконата Филтър. Това ще премахне филтъра и ще видите всички записи, с изключение на изтритите.

Има много филтри за числа, които можете да използвате в Excel - например по -малко от/по -голямо от, равно/не равно, между, топ 10, над или под средното и т.н.

Забележка: Можете да използвате и няколко филтъра. Например, можете да изтриете всички редове, където стойността на продажбите е по -голяма от 200, но по -малка от 500. В този случай трябва да използвате две условия за филтриране. Диалоговият прозорец Персонализиран автофилтър позволява да имате два критерия за филтриране (И както и ИЛИ).

Точно както филтрите за числа, можете също да филтрирате записите въз основа на датата. Например, ако искате да премахнете всички записи за първото тримесечие, можете да направите това, като използвате същите стъпки по -горе. Когато работите с филтри за дата, Excel автоматично ви показва съответните филтри (както е показано по -долу).

Въпреки че филтрирането е чудесен начин за бързо изтриване на редове въз основа на стойност или условие, той има един недостатък - изтрива целия ред. Например, в случая по -долу, той ще изтрие всички данни, които са вдясно от филтрирания набор от данни.

Ами ако искам само да изтрия записи от набора от данни, но искам да запазя останалите данни непокътнати.

Не можете да направите това с филтриране, но можете да го направите със сортиране.

Сортирайте набора от данни и след това изтрийте редовете

Въпреки че сортирането е друг начин за изтриване на редове въз основа на стойността, но в повечето случаи е по -добре да използвате метода на филтриране, обхванат по -горе.

Тази техника на сортиране се препоръчва само когато искате да изтриете клетките със стойностите, а не цели редове.

Да предположим, че имате набор от данни, както е показано по-долу, и искате да изтриете всички записи, където регионът е Среден запад.

По -долу са описани стъпките за това чрез сортиране:

1. Изберете всяка клетка в данните
2. Щракнете върху раздела Данни
3. В групата Сортиране и филтриране щракнете върху иконата Сортиране.
4. В диалоговия прозорец Сортиране, който се отваря, изберете Регион в колоната за сортиране по.
5. В опцията Sort on се уверете, че е избрана Cell Values
6. В опцията Поръчка изберете от A до Z (или от Z до A, всъщност няма значение).
7. Щракнете върху OK. Това ще ви даде сортирания набор от данни, както е показано по -долу (сортирано по колона B).
8. Изберете всички записи с региона Среден Запад (всички клетки в редовете, а не само колоната регион)
9. След като изберете, щракнете с десния бутон и след това върху Изтриване. Това ще отвори диалоговия прозорец Изтриване.
10. Уверете се, че е избрана опцията „Преместване на клетките нагоре“.
11. Щракнете върху OK.

Горните стъпки биха изтрили всички записи, където регионът е бил Среден Запад, но не изтрива целия ред. Така че, ако имате някакви данни отдясно или отляво на вашия набор от данни, те ще останат невредими.

В горния пример съм сортирал данните въз основа на стойността на клетката, но можете също да използвате същите стъпки, за да сортирате въз основа на числа, дати, цвят на клетката или цвят на шрифта и т.н.

Ето подробно ръководство за това как да сортирате данни в Excel

В случай, че искате да запазите реда на първоначалния набор от данни, но да премахнете записите въз основа на критерии, трябва да имате начин да сортирате данните обратно към първоначалния. За да направите това, добавете колона със серийни номера, преди да сортирате данните. След като приключите с изтриването на редовете/записите, просто сортирайте въз основа на тази допълнителна колона, която сте добавили.

Намерете и изберете клетките въз основа на стойността на клетката и след това изтрийте редовете

Excel има функция за намиране и замяна, която може да бъде чудесна, когато искате да намерите и изберете клетки с конкретна стойност.

След като сте избрали тези клетки, можете лесно да изтриете редовете.

Да предположим, че имате набора от данни, както е показано по-долу, и искате да изтриете всички редове, където регионът е Среден Запад.

По -долу са описани стъпките за това:

1. Изберете целия набор от данни
2. Щракнете върху раздела Начало
3. В групата Редактиране щракнете върху опцията „Намиране и избор“ и след това щракнете върху Намиране (можете също да използвате клавишната комбинация Control + F).
4. В диалоговия прозорец Намиране и замяна въведете текста „Среден Запад“ в полето „Намерете какво:“.
5. Щракнете върху Намери всички. Това незабавно ще ви покаже всички случаи на текста Mid-West, които Excel успя да намери.
6. Използвайте клавишната комбинация Control + A, за да изберете всички клетки, които Excel е намерил. Също така ще можете да видите всички избрани клетки в набора от данни.
7. Щракнете с десния бутон върху някоя от избраните клетки и щракнете върху Изтриване. Това ще отвори диалоговия прозорец Изтриване.
8. Изберете опцията „Цял ред“
9. Щракнете върху OK.

Горните стъпки биха изтрили всички клетки, където стойността на региона е среден запад.

Забележка: Тъй като функцията „Намери и замени“ може да обработва символи с заместващи знаци, можете да ги използвате при намиране на данни в Excel. Например, ако искате да изтриете всички редове, където регионът е или Среден Запад, или Югозапад, можете да използвате „*Запад„Като текст за намиране в диалоговия прозорец Намиране и замяна. Това ще ви даде всички клетки, където текстът завършва с думата West.

Изтрийте всички редове с празна клетка

В случай, че искате да изтриете всички редове, където има празни клетки, можете лесно да направите това с вградена функционалност в Excel.

Това е Отидете до специални клетки опция - която ви позволява бързо да изберете всички празни клетки. И след като сте избрали всички празни клетки, изтриването им е супер просто.

Да предположим, че имате набора от данни, както е показано по -долу и искам да изтрия всички редове, където нямам стойността на продажбата.

По -долу са описани стъпките за това:

1. Изберете целия набор от данни (A1: D16 в този случай).
2. Натисни F5 ключ. Това ще отвори диалоговия прозорец „Отиди до“ (Можете също да получите този диалогов прозорец от Начало -> Редактиране -> Намиране и избор -> Отиди на).
3. В диалоговия прозорец „Към“ кликнете върху бутона Специален. Това ще отвори диалоговия прозорец „Отиди на специално“
4. В диалоговия прозорец Отиди на Специално изберете „Празни места“.
5. Щракнете върху OK.

Горните стъпки биха избрали всички клетки, които са празни в набора от данни.

След като изберете празните клетки, щракнете с десния бутон върху някоя от клетките и кликнете върху Изтриване.

В диалоговия прозорец Изтриване изберете опцията „Цял ред“ и щракнете върху OK. Това ще изтрие всички редове, в които има празни клетки.

Ако се интересувате да научите повече за тази техника, написах подробен урок за това как да изтриете редове с празни клетки. Той включва метода „Go To Special“, както и VBA метод за изтриване на редове с празни клетки.

Филтриране и изтриване на редове въз основа на стойността на клетката (използвайки VBA)

Последният метод, който ще ви покажа, включва малко VBA.

Можете да използвате този метод, ако често се налага да изтривате редове въз основа на конкретна стойност в колона. Можете да добавите VBA кода веднъж и да го добавите към вашата работна книга за лични макроси. По този начин той ще бъде достъпен за използване във всички ваши работни книги на Excel.

Този код работи по същия начин като метода Filter, обхванат по -горе (с изключение на факта, че това прави всички стъпки в бекенда и ви спестява няколко кликвания).

Да предположим, че имате набора от данни, както е показано по-долу, и искате да изтриете всички редове, където регионът е Среден Запад.

По -долу е кодът VBA, който ще направи това.

Sub DeleteRowsWithSpecificText () 'Източник: https: //trumpexcel.com/delete-rows-based-on-cell-value/ ActiveCell.AutoFilter Поле: = 2, Criteria1: = "Среден Запад" ActiveSheet.AutoFilter.Range.Offset (1, 0) .Rows.SpecialCells (xlCellTypeVisible).

Горният код използва метода на VBA Autofilter, за да филтрира първо редовете въз основа на посочените критерии (който е „Среден Запад“), след което избира всички филтрирани редове и го изтрива.

Обърнете внимание, че съм използвал Offset в горния код, за да се уверя, че редът на заглавката ми не е изтрит.

Горният код не работи, ако данните ви са в таблица на Excel. Причината за това е, че Excel разглежда таблица на Excel като обект от списък. Така че, ако искате да изтриете редове, които са в таблица, трябва да промените кода малко (обхванат по -късно в този урок).

Преди да изтриете редовете, той ще ви покаже подкана, както е показано по -долу. Намирам това за полезно, тъй като ми позволява да проверя отново филтрирания ред преди изтриването.

Не забравяйте, че когато изтривате редове с помощта на VBA, не можете да отмените тази промяна. Затова използвайте това само когато сте сигурни, че това работи така, както искате. Също така е добра идея да запазите резервно копие на данните само в случай, че нещо се обърка.

В случай, че вашите данни са в таблица на Excel, използвайте кода по -долу, за да изтриете редове със специфична стойност в него:

Sub DeleteRowsinTables () 'Източник: https: //trumpexcel.com/delete-rows-based-on-cell-value/ Dim Tbl As ListObject Set Tbl = ActiveSheet.ListObjects (1) Поле ActiveCell.AutoFilter: = 2, Критерии 1: = "Среден Запад" Tbl.DataBodyRange.SpecialCells (xlCellTypeVisible).

Тъй като VBA разглежда Excel Table като обект List (а не диапазон), трябваше съответно да променя кода.

Къде да поставите VBA кода?

Този код трябва да бъде поставен в бекенда на VB Editor в модул.

По -долу са стъпките, които ще ви покажат как да направите това:

1. Отворете работната книга, в която искате да добавите този код.
2. Използвайте клавишната комбинация ALT + F11, за да отворите прозореца на VBA Editor.
3. В този прозорец на VBA Editor вляво има панел „Project Explorer“ (който изброява всички работни книги и обекти на работни листове). Щракнете с десния бутон върху всеки обект в работната книга (в който искате този код да работи), задръжте курсора на мишката върху „Вмъкване“ и след това щракнете върху „Модул“. Това ще добави обекта Module към работната книга и също така ще отвори прозореца с код на модула вдясно
4. В прозореца на модула (който ще се появи вдясно) копирайте и поставете горния код.

След като имате кода във VB Editor, можете да стартирате кода, като използвате някой от методите по -долу (уверете се, че сте избрали всяка клетка в набора от данни, на която искате да стартирате този код):

1. Изберете всеки ред в кода и натиснете клавиша F5.
2. Щракнете върху бутона Run в лентата с инструменти в VB Editor
3. Присвойте макроса на бутон или форма и го стартирайте, като щракнете върху него в самия работен лист
4. Добавете го към лентата с инструменти за бърз достъп и стартирайте кода с едно щракване.

Можете да прочетете всичко за това как да стартирате макрокода в Excel в тази статия.

Забележка: Тъй като работната книга съдържа VBA макро код, трябва да го запишете във формат, разрешен за макроси (xlsm).