Как удалить пустые строки в Эксель

Как правильно и быстро очистить ячейки в Excel?

Categories: Оформление/ Работа с текстом/ Работа с файлом

На самом деле, я бы разделил вопрос статьи на 3 части

  1. Как очистить форматы ячеек?
  2. Как очистить данные в ячейках Excel?
  3. Очистка/удаление прочих объектов, таких как диаграммы, автофигуры или сводные таблицы

Как быстро очистить ячейки в Excel от форматов?

Вам прислали файл сильно разукрашенный по цветам и его нужно очистить от форматов? Сделать это очень просто: выделяете нужный вам диапазон данных или весь лист сразу (нажав на уголок в левом верхнем углу таблиц).

Теперь ищите на ленте задач на вкладке Главная раздел Редактирование — в нем жмите кнопку Очистить

На выбор у вас будет несколько пунктов. Вы сможете

  1. Очистить все — т.е. удалить все данные на листе и форматы в том числе
  2. Очистить форматы — это как раз то, что нужно для примера
  3. Очистить содержимое — очищаем данные в ячейках, не трогая форматы
  4. Очистить примечания — удобная функция, если кто-то сильно зарецензировал ваш лист и оставил много примечаний. Появилась относительно недавно.
  5. Очистить гиперссылки — совсем новая возможность, появившаяся в 2016 версии. Позволяет очистить все ссылки на листе. Очень удобно, если вы скопировали данные из интернета, например, из Википедии, почти всегда копируется множество гиперссылок.

Если вы уже нажали пункт 2, то все ваши форматы очистились.

Но часто к форматам причисляют и условное форматирование. Мы знаем, что такое форматирование очень сильно грузит действия на листе, поэтому я не советую делать слишком много условий при помощи этой функции. Но если вы получили переформатированную книгу (как правило за счет копирования данных с условным форматированием много раз), то нужно знать, как его удалить.

Опять выделяете нужную область или лист. Идете Главная — раздел Стили — Условное форматирование — Удалить правила и например Удалить правила со всего листа.

Все, условные форматирования тоже удалились!

Как очистить данные в ячейках Excel? Очистка данных по условиям

Как вы знаете, удалить данные/информацию в ячейках Excel можно:

  • нажатием кнопки Delete на клавиатуре, после выделения нужной области
  • нажатием кнопки Удалить содержимое Главная — раздел Редактирование — в нем жмите кнопку Очистить содержимое (см. описание выше)
  • можно удалить не только данные, но и сами ячейки. Выделив, к примеру, строку, кликнуть правой кнопкой мыши и нажав кнопку Удалить (она же Ctrl + кнопка минус)

Помимо этого можно удалить данные и по нужным условиям.

Самое простое решение — сперва отобрать данные для удаления автофильтром. После чего вы можете удалить в выбранных ячейках все, что вам нужно — формат, данные или даже сами ячейки (Ctrl + кнопка минус)

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

Удаление объектов, очистка листов от диаграмм или прочих добавленных объектов

Чтобы удалить диаграмму, или автофигуры, нужно сперва ее выбрать, а затем удалить кнопкой Delete или через правую кнопку мыши — Удалить (см. выше). Но как быть, если вам необходимо удалить сразу несколько объектов — зажмите Ctrl  и выберите нужные объекты. Если объектов очень много, то придется воспользоваться макросом, например, предложенном в пункте 4, этой статьи.

Так же объекты можно удалить, если вы перейдете в меню Главная — раздел Редактирование — Найти и выделить — пункт Выделение группы ячеек — Объекты. Так можно очистить ячейки в Excel от объектов.

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

Если же вам необходим специальный макрос для удаления специфических объектов, то пишите в комментариях или мне на почту!

Поделитесь нашей статьей в ваших соцсетях:

Автор: · Опубликовано 11.05.2018 · Обновлено 08.02.2019

Excel – один из лучших редакторов работы с таблицами. Причем они могут быть практически неограниченных размеров (разумеется, пределы есть, но на практике вы до них никогда не дойдёте). Иногда бывают ситуации, когда среди большого количества ячеек образуются пустоты (вследствие удаления, переноса данных и т. д.), и от них приходится избавляться. Но не все знают, что для этой цели существует большое количество способов. В данной статье мы рассмотрим, как в редакторе Excel удалить пустые строки и при этом не уничтожить другие данные.

В качестве примера мы будем использовать следующий набор чисел.

Метод сортировки

Данный способ работает очень просто. Для этого вам необходимо сделать следующие шаги.

  1. Выделяем область, в которой содержатся наши значения.
  2. Переходим в меню в раздел «Главная».
  3. Кликаем на инструмент «Сортировка».
  4. Выбираем указанный пункт.
  1. Благодаря этому вы увидите, что столбец теперь содержит только цифры. Все лишние пробелы оказались в самом конце, а все нужные значения переместились в начало.

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

Использование фильтра

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

  1. Для начала необходимо выделить нужные значения.
  2. Откройте вкладку «Данные».
  3. Воспользуйтесь инструментом «Фильтр».
  1. После этого вы увидите, что на самом первом значении появилась иконка выпадающего списка. Нажмите на неё.
  2. Появится меню, в котором вы сможете определить, какие именно значения должны заполнить ваш документ. Данный список будет содержать все уникальные значения, которые находились в выделенной области.
  3. Вам нужно убрать галочку возле слова «Пусто». Для сохранения изменений необходимо нажать на «OK».
  1. После этого вы увидите, что пустые ячейки сразу же исчезли.
  1. На самом деле они никуда не удалились. Они просто стали скрытыми. Обратите внимание на то, что теперь в левой части листа имеются выделенные синим цветом ячейки. Это означает, что между ними есть еще какие-то значения, но пользователь решил их скрыть.

Удаление группы ячеек

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

  1. Для начала выделите все нужные вам строки.
  2. Откройте на панели инструментов раздел «Главная».
  3. Кликните на иконку поиска.
  4. Выберите указанный пункт.
  1. После этого появится окно, в котором вам нужно указать, что именно вы хотите выделить. В нашем случае необходимо выбрать пункт «пустые ячейки». Затем нажимаем на «OK».
  1. Благодаря этому вы сможете выделить все пустые клетки, которые находятся в указанной области.
  2. Нажмите на кнопку «Удалить».
  3. Затем выберите указанный пункт.
  1. В результате этого вы увидите, что длина столбца сократилась, а все пустые строки исчезли. При этом никакие клетки не оказались скрытыми.

Предупреждение

Обратите внимание на то, что этот метод рабочий, но он подходит только для обработки простых данных. Если у вас в таблице имеется большое количество столбцов, то при удалении ячеек всё может перепутаться.

Данный вариант быстрый и автоматический, и он не будет «разбираться», в каких именно строках есть пробелы. Инструмент уничтожит всё подряд, а то что находилось ниже, просто поднимется вверх. То есть при удалении группы ячеек появляется высокий риск того, что кое-что пропадет бесследно.

Другими словами, главное условие при применении этого способа – использовать максимально простой массив данных.

Как удалить дубликаты

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

  1. Выделяем нужную область данных.
  2. Открываем уже знакомую нам вкладку на панели.
  3. Кликаем на указанный инструмент.
  1. Сразу после этих действий откроется окно, в котором можно будет указать нужные вам «Колонны». В нашем случае у нас используется только один «Столбец D».
  2. Для продолжения кликаем на кнопку «OK».
  1. Вследствие этого вы увидите сообщение, в котором вам скажут, сколько именно дубликатов было удалено.

Обратите внимание на то, что один пробел в столбце всё равно останется, поскольку удаляются только копии. Последняя оставшаяся пустая клетка всегда уникальная.

  1. Кликните на «OK».
  1. Результат получится вот таким.

Использование макросов

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

  1. Скачиваем файл (ссылка выше). Переходим в Эксель и нажимаем на горячие клавиши ALT+F11. В результате этого вы увидите редактор VBA. Нажмите на главное меню «Insert». После этого выберите пункт «Module».
  1. Скопируйте скачанный код в появившееся окно.
  1. Нажмите на иконку сохранить (либо нажмите на горячие клавиши Ctrl+S).
  1. Нажмите на кнопку «Нет».
  1. Укажите нужный формат. Затем кликните на кнопку «Сохранить».
  1. Закрываем редактор.
  1. Выделите какие-нибудь соседние ячейки. Укажите категорию «Определенные пользователем». Выберите функцию «NoBlanks». Нажмите на кнопку «OK».
  1. Выделите ваш массив данных.
  1. Нажмите на сочетание клавиш Ctrl+Shift+Enter. Вы увидите, что данные скопировались удачно и без пустых клеток.

Версии Excel

Все эти методы подходят только для актуальных программ (2007, 2010, 2013 и 2016 годов). Старый Эксель 2003 может повторить практически все описанные методы. Только пункты меню находятся немного в других местах.

В качестве примера рассмотрим метод сортировки.

  1. Выделите несколько ячеек.
  2. Кликните на меню «Данные».
  3. Выберите инструмент «Сортировка».
  1. Благодаря этому вы увидите окошко, в котором ничего заполнять не нужно. Просто нажмите на «OK».
  1. В итоге все пробелы переместятся в конец. Благодаря этому вы избавитесь от пустых строк.

Заключение

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

Если у вас возникли какие-нибудь проблемы, скорее всего, вы что-то делаете не так. Например, используете не тот инструмент или выделяете не те ячейки. Если проблемы с макросом, то попробуйте перепроверить целостность кода. Возможно, вы его скопировали не полностью.

Видеоинструкция

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

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

image

Удаление скрытых строк является прекрасным примером. В более старых версиях Excel это было невозможно. К счастью, Microsoft решила добавить его в версии 2007 и новее. Тем не менее, как это работает, загадка для многих.

Есть два способа удаления скрытых строк в Excel. Давайте пройдемся по ним.

Использование функции проверки документа

Функция «Инспектор документов» доступна в Excel, Word, PowerPoint и Visio. Это лучший способ обнаружить и удалить любые скрытые данные, которые могут присутствовать в документе. Это полезно, когда вам нужно поделиться документами с другими людьми и хотите убедиться, что в документе нет сюрпризов.

В Excel удаление скрытых строк и столбцов является простой задачей. Вот что вам нужно сделать:

  1. Откройте книгу и выберите «Файл»> «Информация».
  2. Нажмите кнопку «Проверить наличие проблем», затем выберите «Проверить документ».
  3. В диалоговом окне «Инспектор документов» убедитесь, что выбраны «Скрытые строки и столбцы».
  4. Нажмите Осмотреть
  5. Инспектор документов покажет вам отчет, который покажет, есть ли скрытые строки и столбцы. Если есть, перейдите к Удалить все, затем нажмите Отмена.

Эту функцию можно найти в том же месте в Excel 2013 и 2016. Интерфейс версии 2010 выглядит немного иначе, но путь к инспектору документов тот же. Если вы используете Excel 2007, вы можете найти эту опцию, нажав кнопку Office, а затем перейдите в раздел «Подготовка»> «Проверить документ».

Нет никакой разницы в самой функции, поэтому это обеспечит отсутствие скрытых строк и столбцов независимо от вашей версии Excel.

Использование кода VBA

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

  1. Откройте файл Excel и нажмите Alt + F11, чтобы открыть редактор VBA.
  2. Пойдите, чтобы Вставить> Модуль.
  3. Когда появится окно модуля, вставьте в него следующий код:
  1. Нажмите F5, чтобы запустить код.

Это удалит все скрытые строки и столбцы с листа, над которым вы работаете. Если их не много, вы можете повторить этот процесс, чтобы в кратчайшие сроки очистить их от всей книги.

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

Использование стороннего программного обеспечения

Существует множество сторонних решений, которые могут помочь вам мгновенно удалить скрытые строки и столбцы. Они обычно служат расширением Excel, добавляя дополнительные параметры на панель инструментов. Помимо этого, они также предлагают дополнительные функции, которые могут быть трудно найти, такие как:

  1. Пакетные флажки удаления
  2. Пакетное удаление опционных кнопок
  3. Удалить пустые строки,
  4. Пакетное удаление всех макросов

Они могут оказаться очень полезными, если вы являетесь опытным пользователем Excel и ищете быстрые решения общих проблем, которые Microsoft еще предстоит решить должным образом.

Последнее слово

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

Если вам нравится программирование, выполнение кода VBA является более удобным решением, которое вы можете сделать, даже если у вас нет опыта программирования. Все, что вам нужно сделать, это вырезать и вставить правильные команды.

Наконец, вы можете выбрать стороннее решение, которое поможет вам максимально использовать возможности Excel. Они могут стать отличным обновлением программного обеспечения и значительно упростить его использование.

  • 9-02-2018, 11:00
  • 20100

Программы В» Excel

Лишние строки – головная боль для многих пользователей – они приносят массу неудобств при перемещении по документу, больший диапазон ячеек усложняет работу в конце концов – лишние строки в таблице – не эстетичная работа. В Microsoft Excel есть несколько способов, которые помогут избавиться от лишних строк и привести формат таблицы в стандартный, приятный глазу вид. Избавиться от лишних пустых строк можно с помощью контекстного меню программного обеспечения. Достаточно лишь выбрать строки, которые планируется удалить, и нажать на вторую кнопку мышки, которая откроет нужное нам контекстное меню. В новом диалоговом окне выбираем «Удалить…». Данный оператор можно вызвать нажатием на клавиши «Ctrl+-» В окне выбора удаления ячеек достаточно указать, какие позиции нужно удалить – ячейки, со сдвигом влево, вправо, всю строку или столбец, и нажать «Ок». 2. Второй способ – удаление с помощью функции удалить Альтернативным вариантом классического удаления является внутренняя функция Excel. Достаточно выделить, ячейку, диапазон ячеек, столбцы или колонки, которые необходимо удалить, и перейти во вкладку «Главная» — «Удалить». В функции «Удалить» уже можно выбрать, какие непосредственно действия необходимо выполнить без открытия дополнительных диалоговых окон. 3. Третий способ – удаление с помощью сортировки Когда ячейки находятся в непосредственной близости или близком расположении – удаление первыми двумя способами не составит труда. Но что делать, когда пустые ячейки расположены по всей таблице, которая имеет внушительные размеры? Можно использовать вариант с сортировкой. Для этого достаточно выбрать всю рабочую область, а у нас это таблица со значениями, вызвать контекстное меню и выбрать функцию «Сортировка» — «Сортировка от минимального к максимальному». Эта функция автоматически переместит все ячейки без значений в самый конец нашей таблицы — теперь их легко можно удалить уже известными способами, описанными выше. Стоит учесть, что сортировка изменит порядок расположения ячеек. Если же этот порядок важен – необходимо выполнить несколько простых шагов. Вставим новый столбец и пронумеруем все его ячейки по порядку, установив значения первым двум ячейкам и протянув маркер заполнения по всему столбцу. Далее выполняем сортировку «От минимального к максимальному», удаляем опущенные вниз пустые ячейки, и делаем такую же сортировку вставленного ранее пронумерованного столбца. Как видим, весь порядок ячеек вернулся к прежнему, и мы избавились от пустых ячеек. Теперь этот столбец можно смело удалять способами, которые уже известны, и наслаждаться результатом выполненной работы. 4. Четвертый способ – фильтр и его применение Так же избавиться от пустых нежелательных в нашей таблице ячеек можно с помощью использования встроенных фильтров в Excel. Для начала выделим весь диапазон ячеек, у нас это вся таблица, далее переместимся по вкладкам «Главная» — «Сортировка и фильтр» — «Фильтр». В правой части наименования каждого из столбцов был установлен фильтр, который позволит отображать только выбранные ячейки в фильтре. Выбираем любой столбец, пусть им будет «Дата» и снимаем указатель с области «(Пустые)» и применяем фильтр. Как можно увидеть, пустые ячейки без значений исчезли с поля зрения. Таким же способом можно в фильтре снять указатели со всех пунктов и выбрать только «(Пустые)». На экране отобразятся пустые лишь ячейки. Их можно удалить уже известными способами, после чего указать фильтру показывать все ячейки. 5. Пятый способ – выделение группы ячеек Выделение группы ячеек дает возможность оперировать с позициями ячеек разных групп. Достаточно лишь указать какие именно ячейки необходимы в контекстном меню. Выделим всю рабочую область и нажмем на «Главная» — «Найти и выделить». Здесь указываем на функцию «Выделение группы ячеек». Укажем в новом диалоговом окне открытой функции выберем, чтобы нам показывались лишь ячейки без значений и применим процедуру. У нас все пустые ячейки стали выделенными. Теперь их можно смело удалять. Таким довольно простым и быстрым способом можно избавиться от пустых ячеек в довольно больших и сложных таблицах.

Что делает макрос: Работа с Excel достаточно долгая, и вы увидите, что пустые строки часто могут вызвать хаос на многих уровнях. Они могут вызывать проблемы с формулами, ввести риск при копировании и вставке, а иногда вызывают странное поведение в сводных таблицах. Если вы вручную пытались выискать и удалить пустые строки в ваших данных, этот макрос может помочь автоматизировать эту задачу.

Содержание

Как макрос работает

В этом макросе, мы используем свойство UsedRange объекта ActiveSheet, чтобы определить диапазон c которым мы работаем. Свойство UsedRange дает диапазон, который охватывает ячейки, которые были использованы для ввода данных. Затем устанавливается счетчик, который начинается в последней строке используемого диапазона, чтобы проверить, вся ли строка пуста. Если вся строка действительно пуста, мы убираем строку. Мы продолжаем делать то же самое для каждого цикла, каждый раз увеличивая счетчик на предыдущей строке.

Код макроса

 Sub UdalitPustieStroki() 'Шаг 1: Объявляем переменные Dim MyRange As Range Dim iCounter As Long 'Шаг 2: Указываем диапазон Set MyRange = ActiveSheet.UsedRange 'Шаг 3: Начинаем обратный цикл For iCounter = MyRange.Rows.Count To 1 Step -1 'Шаг 4: Если строка пустая, удаляем ее If Application.CountA(Rows(iCounter).EntireRow) = 0 Then Rows(iCounter).Delete End If 'Шаг 5: Увеличиваем счетчик Next iCounter End Sub 

Как этот код работает

  1. Макрос первым объявляет две переменные. Первая переменная является переменной объекта называется MyRange. Это переменная объект, который определяет наш целевой диапазон. Другая переменная является переменной Long Integer называется iCounter. Эта переменная служит инкрементного счетчика.
  2. На шаге 2 макрос заполняет переменную MyRange со свойством UsedRange объекта ActiveSheet. Свойство UsedRange дает диапазон, который охватывает клетки, которые были использованы для ввода данных. Обратите внимание, что, если мы хотим указать фактический диапазон или именованный диапазон, мы могли бы просто ввести свое название —
  3. На этом этапе макрос устанавливает параметры для инкрементного счетчика, чтобы начать на максимальном количестве для диапазона (MyRange.Rows.Count) и заканчивается в 1 (первая строка выбранного диапазона). Обратите внимание, что мы используем Шаг-1 классификатор. Поскольку мы указываем шаг -1, Excel знает, что мы будем увеличивать счетчик в обратном направлении, двигаясь назад один шаг на каждой итерации. В целом, Шаг 3 говорит Excel, чтобы начать в последней строке выбранного диапазона, двигаясь назад, пока он не дойдет до первой строки диапазона. При работе с диапазоном, вы можете явно вызвать какую-либо конкретную строку в диапазоне, передавая номер индекса строки к коллекции Rows диапазона. Например, диапазон («D6: D17»). Ряды (5) указывает на пятой строки диапазона D6: D17.
  4. На шаге 4, макрос использует переменную iCounter как номер индекса для коллекции Строки MyRange. Это помогает точно определить какой именно ряд мы обрабатываем в текущем цикле. Макрос проверяет, являются ли ячейки в этой строке пустыми. Если они пусты, макрос удаляет всю строку.
  5. На шаге 5, макрос возвращается к началу цикла, увеличивая счетчик.

Как использовать

  1. Активируйте редактор Visual Basic, нажав ALT + F11.
  2. Щелкните правой кнопкой мыши personal.xlb в окне Project.
  3. Выберите Insert➜Module.
  4. Введите или вставьте код.

Оцените статью
Рейтинг автора
5
Материал подготовил
Илья Коршунов
Наш эксперт
Написано статей
134
А как считаете Вы?
Напишите в комментариях, что вы думаете – согласны
ли со статьей или есть что добавить?
Добавить комментарий