Как удалить ненужные строки в excel по критерию

REDMOND

Как удалить строки в Excel по условию? Удаление и скрытие пустых строк и строк, содержащих заданное значение

Прежде чем перейти к теме статьи, разберемся, с чем мы будем иметь дело. Посмотрим на иерархию объектов в Excel. Рабочая книга (Workbook) – это файл Excel, содержащий рабочие листы. Рабочий лист (Worksheet), в свою очередь, представляет собой электронную таблицу, основной тип документа, который используется приложением Excel для хранения и обработки данных. Рабочие листы — это таблицы, состоящие из ячеек, организованных в столбцы и строки и являются частью рабочей книги. Основным элементом электронной таблицы является ячейка. Именно в ячейках содержится информация – текст, значения, формулы. Каждая ячейка имеет свой адрес – номер строки и номер столбца, на пересечении которых находится ячейка. В этой статье более подробно остановимся на строках.

Строки в Excel и основные действия с ними

Количество строк в Excel 2003 – 65 536, а число строк в Excel 2007 и Excel 2010 – уже 1 048 576. Наиболее частыми действиями со строками являются копирование, вставка, добавление новых строк, удаление и скрытие. Без особого труда можно преобразовать Excel строку в столбец и наоборот. Такое преобразование называется транспонированием . Для преобразования строки в столбец необходимо скопировать данные строки, после чего нажать правую кнопку мыши и из контекстного меню выбрать пункт «Специальная вставка…». В появившемся диалоговом окне «Специальная вставка» поставить галочку в поле «транспонировать» и нажать кнопку ОК.

как транспонировать строку в столбец

Все манипуляции со строками не представляют какой-либо особой сложности, но задача существенно усложняется, когда работать приходится с большими объемами данных и в особых условиях, когда к примеру нельзя воспользоваться стандартными средствами Excel, такими как сортировка. В таких случаях на помощь как всегда приходят макросы VBA. С помощью макросов VBA Excel строки легко поддаются обработке, можно например скрыть все пустые строк и или удалить строки содержащие заданный текст.

Далее рассмотрим инструмент, в котором реализовано программное скрытие и удаление пустых строк, а также скрытие и удаление любых строк, по заданным пользователем условиям, при этом все параметры для работы программы задаются не в редакторе программного кода, а в диалоговом окне, что очень удобно. Надстройка работает как с видимыми так и со скрытыми и отфильтрованными строками .

Надстройка для удаления и скрытия строк по условию

1) Скрывать либо удалять строки, подходящие под заданное условие;

2) скрывать либо удалять строки, находящиеся ниже или выше строк, подходящих под заданное условие;

3) скрывать либо удалять заданное количество строк для каждого действия;

4) скрывать либо удалять разное количество строк для каждого действия, если количество строк указано в отдельном столбце;

5) выбирать одно из нескольких условий для искомых значений (совпадает, не совпадает, содержит, не содержит, начинается, не начинается, заканчивается, не заканчивается);

6) находить пустые строки и строки, содержащие заданный текст (либо массив из текстовых значений);

7) осуществлять одновременный поиск различных текстовых выражений, внесенных в поле для ввода текста с использованием знака-разделителя. В качестве знака-разделителя используется (;) точка с запятой;

8) выбирать различные диапазоны для обработки данных;

REDMOND

9) ограничивать выбранные диапазоны номерами строк сверху и/или снизу для избежания повреждения шапок таблиц и строк с разного рода промежуточными итогами;

10) осуществлять поиск текста в строках как с учетом регистра, так и без.

Диалоговое окно надстройки

По изображению диалогового окна можно получить представление о том, какие возможности предоставляются надстройкой — это удаление/скрытие пустых строк и строк, подходящих под заданные условия.

Как скрыть или удалить строки по условию?

На выбор пользователя макрос может удалять/скрывать пустые строки, а может на выбор пользователя скрывать либо удалять строки, удовлетворяющие заданным условиям, например таким как содержание заданного текста, либо отсутствием такового, может учитывать регистр при поиске текста в строках, а может не учитывать. Для того, чтобы при удалении/скрытии в таблицах не пострадала шапка или промежуточные результаты, введено дополнительное ограничение по номерам строк, при помощи которого можно задавать диапазон для действия макроса. Если же диапазон не задан пользователем, то макрос работает со строками так называемого используемого диапазона, то есть со строки, в которой содержится первая заполненная информацией ячейка, до строки с последней заполненной ячейкой. Пользователю также предлагается ограничить обработку данных либо активным листом, либо всеми листами активной рабочей книги.

При помощи надстройки можно производить действия не только с самими строками, которые подходят под заданное условие, но и со строками, которые находятся выше или ниже этих строк. Кроме того, предусмотрена возможность задать количество строк для каждого действия.

Как скрывать или удалять разное количество строк?

Если в диалоговом окне надстройки установить флажок в поле "Разное количество строк", то числовое значение в поле "Для каждого действия, шт." меняется на номер столбца, в котором содержатся значения для каждого действия. В этом случае за каждое действие будет скрыто или удалено столько строк, сколько указано в соответствующей строке заданного столбца.

raznoe kolichestvo udalyaemyh strok

Как скрывать или удалять строки со служебными символами?

При необходимости удаления или скрытия строк со служебными символами, такими как звездочка или знак вопроса, в строке поиска необходимо перед искомым знаком поставить символ "

" тильда, это сигнализирует приложению Excel о том, что следующий за тильдой знак перестает выполнять свои служебные функции и становится обычным символом.

Удалить ненужные столбцы и строки по условию

Меню «Удалить диапазоны» в надстройке !SEMTools

При работе с данными в Excel часто приходится очищать данные от ненужных строк и столбцов. Задача тривиальна, когда строк, попадающих под паттерн для удаления, немного. Если же у вас десятки и сотни тысяч строк в массиве, а паттернов, например, несколько, задача перестает быть простой.

Что такое паттерны? Это признаки, по которым вы делаете выборку строк или столбцов, которые будете удалять.

Примеры таких паттернов:

  • пустота столбца или строки,
  • наличие в столбце пустых ячеек
  • определенного символа или нескольких символов
  • нескольких сочетаний букв
  • нескольких слов
  • нескольких словосочетаний

Для тех, кто ценит время и ищет методы быстрого решения проблем, были созданы инструменты по удалению строк и столбцов в !SEMTools. Все макросы в этом меню работают в бесплатной версии, поэтому смело переходите в раздел Скачать !SEMTools, устанавливайте надстройку и пользуйтесь — они довольно удобны. Ниже краткое описание всех возможностей:

  • Удалить полностью пустые столбцы и строки на листе:
  • удалить пустые столбцы
  • удалить пустые строки
  • Удалить всю строку по условию
    • Отсутствия чего-либо: (Если ячейка выделенного столбца не содержит…)
      • Ничего (пустая)
      • Кириллицу
      • Латиницу
      • Цифры
      • Конкретное слово
      • Слово, удовлетворяющее определенному паттерну
      • Ни одно из слов из списка
      • Наличия чего-либо (Если ячейка выделенного столбца содержит…)
        • Латиницу
        • Цифры
        • Конкретное слово
        • Слово, удовлетворяющее определенному паттерну
        • Любое слово из списка
        • REDMOND

          Ссылка на основную публикацию