Как выделить пустые строки в excel
Как в Excel удалить пустые строки
Работая с большими объемами данных в таблицах Excel, мы часто сталкиваемся с наличием пустых строк в Excel. Для того чтобы убрать пустые строки есть несколько способов:
Давайте рассмотрим каждый из них.
Как удалить пустые строки в Excel с помощью сортировки
Представим, что у нас есть таблица с данными продаж по разным странам и городам:
В этой таблице есть пустые строки. Наша задача их удалить. Мы можем сделать это поочередно, выделяя каждую пустую строку, и правой клавишей мыши удалять строки. Более простой способ это отсортировать пустые строки и затем одним движением их удалить.
Для того чтобы отсортировать данные нашей таблицы проделаем следующие шаги:
Если вы хотите удалить строки целиком, то я рекомендую выделять их отмечая левой клавишей мыши за порядковые номера строк. Так, система выделит все колонки в строке и вы за пару кликов сможете их удалить.
Как удалить пустые строки в Excel с помощью фильтрации
Для того чтобы удалить строки в той же самой таблице, но уже с помощью фильтрации, проделаем следующие шаги:
Важно! Для корректной фильтрации данных обязательно, перед нажатием кнопки «Фильтр», выделите область со значениями таблицы, включая пустые строки. Если этого не сделать — фильтрация данных таблицы будет осуществляться до первой пустой строки!
У этого способа удаления пустых строк есть минус, он заключается в том, что для их удаления вам необходимо отфильтровать каждую колонку. Делать это следует внимательно, дабы не удалить строки с данными.
Как убрать пустые строки в Excel с помощью выделения группы ячеек
Для удаления пустых строк можно воспользоваться инструментом выделения группы ячеек. Для этого:
Внимание! Будьте аккуратны с использованием данного способа! Удаляя выделенные ячейки, есть высокая вероятность того, что вы удалите строки с данными, например, в которых есть только одна пустая ячейка, а в соседних ячейках строки есть данные!
Как удалить пустые строки в Экселе с помощью вспомогательного столбца
Пожалуй, самый эффективный и надежный способ удаления пустых строк в Excel это создание вспомогательного столбца, рядом с таблицей.
Представим, что в нашей таблице с данными есть строки, в которых всего лишь одна ячейка пустая, а остальные ячейки в строке содержат данные (на примере ниже выделены ячейки выделены желтым цветом).
Используя раннее описанные способы, высока вероятность удалить строки с данными.
Поэтому, чтобы избежать потери данных при удалении пустых строк, создадим справа от таблицы столбец, в который вставим функцию СЧИТАТЬПУСТОТЫ, в ней укажем диапазон всех ячеек каждой строки таблицы и протянем формулу до конца таблицы:
Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!
Как удалить пустые строки в Excel. 4 варианта удаления пустых строк в таблице Эксель
При составлении таблиц в Microsoft Excel и работе с формулами часто образуются пустые строки, которые проблематично убрать. В данной статье будут описаны самые распространенные методы их удаления.
Как НЕ НАДО удалять пустые строки в Excel
Удаление пустых строк не рекомендуется выполнять через раздел «Найти и выделить». Данная команда удаляет сразу всю строку, в которой есть пустоты. В таком случае ячейки с нужной информацией также пропадут. Инструмент «Найти и выделить» актуален, когда пустой получилась целая строчка в таблице, и ее необходимо удалить. В такой ситуации необходимо проделать следующие шаги:
В Эксель пустые строки могут удаляться посредством сортировки таблицы либо установления фильтра. Однако такой способ также может привести к полному удалению строки с заполненными ячейками. Причем пострадать могут и соседние строчки или столбцы.
Обратите внимание! В Excel нельзя удалять пустые строки, выбирая отдельные ячейки в таблице во избежание потери важной информации. Лучше воспользоваться другими методами, которые будут описаны далее.
Формула для удаления пустых строк в Excel
Для выполнения поставленной задачи можно воспользоваться специальной формулой, которая работает во всех версиях программы: =ЕСЛИ(СЧЕТЗ(A2:E2)=0;»Пусто»;»»). А2 и Е2 — это первая и последняя ячейки строки, в которой необходимо убрать пустоты. Аналогичным образом выражение применяется для любого столбца таблицы. Пользователю необходимо поставить курсор мыши в свободную ячейку и прописать данную формулу в строке сверху главного меню. Далее эту формулу потребуется растянуть для всех строк или столбцов таблицы, зажав ЛКМ крестик в правом нижнем углу первой ячейки. После таких манипуляций пустые строчки пропадут.
Как удалить пустые строки в Excel с помощью Power Query
Power Query — это специальный инструмент, который присутствует в версиях Excel, начиная с 2013 года.
Важно! Такой инструмент изменяет форматирование таблицы, заливку ячеек, их размер, ориентацию.
Чтобы удалить пустые ячейки в таблицы, необходимо выполнить несколько простых шагов по алгоритму:
Дополнительная информация! После реализации рассмотренного способа формат отображения дат и чисел в таблице изменится.
Как удалить строки, если ячейка в определенном столбце пуста
После построения в некоторых столбцах таблицы могут появиться единичные пустые ячейки. От них легко избавиться методом пробелов, рассмотренного в начале статьи. В данной ситуации такой способ деинсталляции можно применить. Действовать нужно следующим образом:
Как удалить лишние строки под данными или скрыть их
В процессе заполнения таблицы в Excel часто появляются пустые строки, в которых, на самом деле, прописана скрытая информация. Лишние ячейки возможно удалить следующим образом:
Иногда пользователю не хочется удалять информацию из скрытых ячеек таблицы. В таком случае их можно скрыть. Скрытие строчек в Microsoft Office Excel осуществляется по следующей инструкции:
Как быстро удалить пустые строки в Excel с помощью VBA
VBA — это специальный язык программирования, предназначенный для автоматизации процессов в редакторах Microsoft Office. С его помощью можно будет в кратчайшие сроки удалить пустые ячейки в Excel, скопировав нужный код программы. Для реализации такого способа деинсталляции не обязательно быть программистом. Далее будет рассмотрено несколько команд, реализуемых на VBA в Эксель.
Макрос 1. Удалить пустые строки в выбранном диапазоне
Если выделить нужные строки и ввести данный код, то можно удалить все пустоты указанного диапазона. При этом пользователь не увидит никаких предупреждений, сообщений, всплывающих диалоговых окон и т.д. Увидеть код для удаления пустот в диапазоне можно на следующем изображении:
Код программы для деинсталляции пустых строчек в заданном диапазоне
Чтобы ввести макрос в Excel на языке программирования VBA и запустить его, необходимо:
Важно! Созданный макрос сохранится в Excel, и его не нужно будет вводить каждый раз для выполнения конкретного действия.
Макрос 2. Удалить все пустые строки в Excel
С помощью написанного кода можно деинсталлировать сразу все пустоты, которые находятся на активном листе Эксель. Для этого используется следующий код:
Макрос для удаления всех пустот на рабочем поле листа Excel
Макрос 3. Удалить строку, если ячейка пуста
Это метод пробелов, который был описан выше, реализуемый с помощью кода. Данный способ позволяет удалить всю строку, в которой присутствует одна или несколько пустых ячеек, а также избавиться от лишних строк. Представленный код будет выглядеть так:
Макрос для деинсталляции лишних строк с пустотами со всего рабочего листа
Перед запуском любого кода в Excel рекомендуется создать копию своей работы во избежание утери важных сведений в случае допущения ошибки.
Как добавить макрос в свою книгу
Для записи кодов в Excel создается своя книга. Выше был рассмотрен альтернативный метод создания и запуска макросов. Есть еще один способ, который заключается в выполнении следующих шагов:
Дополнительная информация! Чтобы активировать созданный макрос в следующий раз, необходимо зайти в книгу, выбрать соответствующую таблицу, зажать «Alt+F8» с клавиатуры, выбрать нужный код и щелкнуть по кнопке «Выполнить».
Как удалить пустые строки в Excel с помощью сортировки
Сортировка данных в таблице помогает удалить пустоты. Данная операция выполняется по алгоритму:
Как удалить пустые строки в Excel с помощью фильтрации
Процесс выполнения поставленной задачи подразделяется на следующие этапы:
Перед выполнением способа фильтрации данных понадобится выделить весь диапазон таблицы, иначе фильтрация будет осуществляться только до первой пустоты.
Обратите внимание! Для деинсталляции пустых ячеек рассмотренным выше способом пользователю потребуется аккуратно отфильтровать каждый столбец в таблице, чтобы не удалить нужную информацию.
Как убрать пустые строки в Excel с помощью выделения группы ячеек
В Эксель есть встроенный инструмент, с помощью которого можно группами выделять ячейки. С его помощью удаление пустот производится по алгоритму:
Как удалить пустые строки в Экселе с помощью вспомогательного столбца
Для деинсталляции пустот рядом с таблицей можно создать столбец, который будет выступать в роли вспомогательного элемента. Это один из самых быстрых и надежных способов удаления. Чтобы реализовать данный метод, требуется выполнить несколько простых манипуляций по пошаговой инструкции:
Важно! Данная формула выглядит следующим образом: «=СЧИТАТЬПУСТОТЫ(А6:D6)». Вместо букв в скобках указывается диапазон ячеек.
Заключение
Таким образом, существует несколько способов удаления пустых строк в Microsoft Excel, каждый из них актуален для конкретного случая. Чтобы разбираться в теме, необходимо внимательно ознакомиться с вышеизложенной информацией.
6 способов — как безопасно удалить лишние пустые строки в Excel
Это руководство научит вас нескольким простым приемам безопасного удаления нескольких пустых строк в Excel без потери информации.
Пустые строки в таблице — это проблема, с которой мы все время от времени сталкиваемся, особенно при объединении данных из разных источников или импорте информации из каких-то отчетов. Они могут мешать вашим расчетам, портить внешний вид таблицы, а их удаление вручную может быть длительным и подверженным ошибкам процессом.
В этой статье вы узнаете несколько простых и надежных методов удаления “белых пятен” на листах Excel.
Как НЕ НАДО удалять пустые строки в Excel
Есть несколько различных способов удаления пустых строк в Microsoft Excel. Но на удивление многие онлайн-ресурсы придерживаются в своих рекомендациях наиболее опасных из них.
Что плохого в этой технике? Она выбирает все пустоты в диапазоне данных. Но ведь в строке может быть, к примеру, только одна пустая ячейка, а в остальных будет записаны какие-то данные. И, следовательно, вы в итоге удалите множество строчек, которые содержат хотя бы одну пустую ячейку. Некоторые нужные данные, естественно, будут утеряны.
Специально не буду подробно описывать последовательность действий в этом методе, чтобы вы случайно его не повторили.
И ключевым в данном случае является вопрос: «Вы уверены, что все ячейки в вашей строке пустые? У вас точно нет таких, в которых по какой-то причине не заполнены одна или несколько ячеек, а остальные содержат данные?»
Просто запомните: «Не следует использовать метод поиска пустых ячеек, чтобы выделить и удалить пустые строки».
Встречаются также предложения удалять незаполненные строки при помощи сортировки таблицы или установления фильтра.
Но здесь мы сталкиваемся с той же опасностью. Если вы упорядочите данные по какому-то столбцу, к примеру, от меньшего к большему, то строчки с пустыми ячейками в этом столбце окажутся в самом низу. Но где гарантия того, что в других столбцах правее или левее также отсутствует информация? А если таблица достаточно большая, то и проверить это не так просто.
В итоге вы опять же рискуете удалить данные, в которых случайно оказался пропуск именно в этом столбце.
С фильтрацией примерно та же история. Вы можете установить фильтр по пустым ячейкам в каком-то конкретном столбце. Далее все может произойти так же, как в истории с сортировкой. Какие-то из ячеек могут оказаться заполнены нужной информацией. Чтобы обезопасить себя от случайной её потери, такой фильтр по пустым ячейкам нужно установить в каждом столбце. Хорошо, если их в вашей таблице штук 5. А если 20? А 30? Вряд ли у вас есть желание устанавливать, а затем снимать даже 10 фильтров.
Поэтому эти методы – это также не наш выбор.
Итог: если вы не хотите испортить или потерять данные, никогда не удаляйте пустые строки, выбирая каким-то способом отдельные пустые ячейки в вашей таблице. Вместо этого используйте один из наиболее продуманных подходов, обсуждаемых ниже.
Формула для удаления пустых строк в Excel
Формулу мы будем использовать в дополнительном столбце, который лучше всего расположить справа от вашей таблицы. Поясним на примере.
У нас имеется таблица с заказами от различных покупателей. В ней имеются пропуски. Часть ячеек не заполнена. Давайте избавимся от пустых строк, но при этом сохраним все прочие данные.
Если вы хотите точно знать, что удаляете, то используйте следующую формулу:
Введите эту формулу в F2 или любой другой свободный столбец в строке 2 и перетащите маркер заполнения, чтобы скопировать формулу вниз.
В результате у вас будет записано «Пусто» в незаполненных строках и ничего, если есть хотя бы одна ячейка с данными:
Логика формулы очевидна: вы подсчитываете заполненные ячейки с помощью функции СЧЁТЗ и используете оператор ЕСЛИ для возврата «Пусто» для нулевого результата. Если же найдена хоть какая-то информация в нужном диапазоне, то не возвращаем ничего.
Фактически, вы можете обойтись без ЕСЛИ:
В этом случае формула вернет ИСТИНА для пустых и ЛОЖЬ для заполненных строк.
Встречаются также аналогичные решения с использованием функции СЧИТАТЬПУСТОТЫ. В ней указываем диапазон ячеек и затем сравниваем с количеством столбцов.
Если достигнуто равенство, значит, число незаполненных клеток равно числу столбцов и данных в строке нет. Поэтому её можно пометить на удаление.
Как мы это удаление сделаем?
Разберем пошагово действия, как быстро удалить пустые строки:
Или же щелкните по номеру первой незаполненной строки на вертикальной шкале координат. При этом вся она должна подсветиться. Затем наберите на клавиатуре комбинацию Ctrl + Shift + 
(стрелка вниз). При этом будет выделена вся область до конца таблицы. Поле этого кликните по выделению правой кнопкой мыши и выберите из контекстного меню пункт «Удалить». Все выделенные строки будут удалены.
В результате мы имеем чистую таблицу без пробелов, но с сохранением всей информации. Частично заполненные строки остались в таблице, мы их не потеряли.
Как удалить пустые строки в Excel с помощью Power Query
Важное примечание! Этот метод работает со следующим предостережением: Power Query преобразует ваши данные в таблицу Excel и изменяет форматирование, такое как цвет заливки, границы и некоторые числовые форматы. Если для вас важно форматирование исходных данных, то лучше выбрать другой способ удаления.
Итак, давайте по порядку.
Как удалить строки, если ячейка в определенном столбце пуста
В начале этого урока мы предостерегали вас от удаления пустых строк путем выбора пробелов. Однако этот метод пригодится, если вы хотите удалить строки на основе пробелов в определенном столбце.
В качестве примера удалим все строки, в которых ячейка в столбце A не содержит никакой информации:
Готово! Строки, которые не имеют данных в столбце A, больше не существуют.
Такого же результата можно добиться, отфильтровав пробелы в ключевом столбце.
Как удалить лишние строки под данными или скрыть их.
Иногда строки, которые выглядят совершенно пустыми, на самом деле могут содержать пробелы, перевод строки или непечатаемые символы. Чтобы проверить, действительно ли последняя ячейка с данными является последней использованной ячейкой на листе, нажмите Ctrl + End. Если это привело вас к визуально свободной от данных строке под вашими данными, то с точки зрения Excel, в ней на самом деле что-то записано. Чтобы удалить эти лишние строки под таблицей, сделайте следующее:
Однако, удаление — это достаточно радикальный шаг. Поэтому, думаю, стоит поискать более «мягкие» варианты. К примеру, нет ничего, что могло бы помешать вам незаполненные строки либо столбцы просто скрыть. Вот простая инструкция:
Аналогичным образом вы можете скрыть неиспользуемые столбцы справа от ваших данных.
Как быстро удалить пустые строки в Excel с помощью VBA
Макрос 1. Удалить пустые строки в выбранном диапазоне.
Этот код VBA автоматически удаляет все пустые строки в выбранном диапазоне, не показывая пользователю никаких сообщений или диалоговых окон.
Чтобы дать пользователю возможность выбрать целевой диапазон после запуска макроса, используйте этот код:
После запуска макрос показывает вам поле ввода, вы указываете целевой диапазон и нажимаете OK:
Через мгновение все лишние строки в выбранном диапазоне будут удалены, а оставшиеся сдвинутся вверх.
Макрос 2. Удалить все пустые строки в Excel
Чтобы массово удалить все пустые строки на активном листе, макрос находит последнюю строку используемого диапазона (т.е. содержащую последнюю ячейку с данными), а затем переходит вверх, удаляя строчки, в которых нет никакой информации:
Макрос 3. Удалить строку, если ячейка пуста.
С помощью этого макроса вы можете удалить всю строку, если ячейка в указанном столбце не содержит данных.
Этот код проверяет столбец A на наличие пробелов. Чтобы удалить строки на основе другого столбца, замените «A» другой соответствующей буквой.
Чтобы удалить незаполненные строки в Excel с помощью макроса, вам нужно вставить код VBA в свою книгу.
Как добавить макрос в свою книгу?
Чтобы вставить макрос в книгу, выполните следующие действия:
Чтобы выполнить макрос в рабочей книге Excel, сделайте следующее:
Вы можете запустить один из следующих макросов:
Имейте в виду, что отменить удаление при помощи меню или же комбинации клавиш Ctrl+Z в данном случае будет невозможно. Поэтому рекомендую перед использованием макросов позаботиться о сохранении резервной копии ваших данных.
Самый быстрый способ удалить пустые строки в Excel
Читая предыдущие примеры, разве не казалось, что мы используем кувалду, чтобы расколоть орех? Существует способ буквально в два клика мышки удалить пустые строки в Excel.
При помощи надстройки Ultimate Suite вы можете удалить все пустые строки на листе:
Сравните это с первым рисунком, и вы без труда заметите те 3 строки, которые были удалены одним нажатием мышки. Согласитесь: это самый простой и быстрый способ удаления пустых строк среди всех прочих, рассмотренных в этой статье. Подробнее об инструменте удаления пустот в таблице вы можете прочитать здесь.
Чтобы узнать о других полезных функциях, включенных в надстройку Ultimate Suite, вы можете загрузить пробную версию.