Как пропустить пустые строки в формуле Excel

Обновлено: 02.07.2024

На листе Microsoft Excel часто встречаются пустые строки. Большинство из них не нужны и занимают больше места, что увеличивает размер рабочего листа. Что совершенно не нужно. Итак, вы можете удалить эти пустые строки из своей книги. К счастью, Microsoft Excel предоставляет несколько способов удаления этих пустых строк из листа Excel. Но мы также можем использовать формулы, чтобы удалить их все. В связи с этим мы собираемся обсудить 5 примеров, касающихся формулы Excel для удаления пустых строк. Продолжайте читать!

Загрузить рабочую тетрадь

Вы можете скачать файл Excel по ссылке ниже и попрактиковаться вместе с ним.

5 примеров использования формулы для удаления пустых строк в Excel

Мы создали таблицу данных под названием "Лист оценок учащихся", чтобы продемонстрировать использование 5 формул для удаления пустых строк в Excel. Таблица данных имеет четыре столбца. Первый столбец содержит имя, второй, третий и четвертый столбцы содержат названия курсов, для которых зарезервированы оценки по этим курсам.

Итак, без дальнейших обсуждений, давайте сразу рассмотрим все методы один за другим.

Формула 1: удаление пустых строк в Excel с помощью функций ЕСЛИОШИБКА, ИНДЕКС, МАЛЕНЬКИЙ, ЕСЛИ, СТРОКА и СТРОКА

Мы создадим формулу массива, используя функции ЕСЛИОШИБКА, ИНДЕКС, МАЛЕНЬКИЙ, ЕСЛИ, СТРОКА и СТРОКИ. Эта функция массива удалит все пустые строки из таблицы данных в Excel.

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

Для этого выполните следующие действия:

❶ Вставьте следующую формулу массива в ячейку G5.

❷ Нажмите кнопку ВВОД.

❸ Теперь сначала перетащите значок маркера заполнения вверх в ячейку J5. Затем снова перетащите его в ячейку J11.

Это исключит все строки с пустыми ячейками, как показано на рисунке ниже:

Разбивка формулы

СТРОКА(B$5:B$13) ▶ начало с строки 5.
ROWS(B$5:B5) ▶ возвращает 1.
ROW(B$5:B$13)),ROWS(B$5:B5) ▶ указывает, что формула работает с первым столбцом 5-й строки рабочего листа.
МАЛЕНЬКИЙ(ЕСЛИ(C$5:C$13<>””,СТРОКА(C$5:C$13)),СТРОКИ(C$5:C5)) ▶ находит наименьший адрес ячейки среди 5-й строки таблицы данных.< /li>
=ЕСЛИОШИБКА(ИНДЕКС(B:B,МАЛЕНЬКИЙ(ЕСЛИ(B$5:B$13<>"",СТРОКА(B$5:B$13)),СТРОКИ(B$5:B5))), "") ▶ проверяет, есть ли пустые ячейки в 5-й строке. Если нет, то возвращается вся строка.

Формула 2: удаление пустых строк в Excel с помощью функций ЕСЛИ, СТРОК, СЧЁТ, ИНДЕКС, МАЛЕНЬКИЙ, СТРОКА и СТРОКА

Теперь мы все создадим еще одну формулу массива, которую можно использовать в качестве альтернативы предыдущей формуле. На этот раз мы будем использовать функции ЕСЛИ, СЧЕТЧИК, ИНДЕКС, МАЛЕНЬКИЙ, СТРОКА и СТРОКИ, чтобы сформулировать формулу массива.

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

❶ Выберите ячейку G5, чтобы вставить следующую формулу:

❷ Затем нажмите кнопку ВВОД, чтобы выполнить формулу.

❸ Наконец, сначала нарисуйте значок «Ручка заполнения» в столбце J. После этого снова перетащите его в конец столбца Math.

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

Разбивка формулы

ROW(B$5:B$13) ▶ указывает, что учитываются строки с 5-й по 13-ю.
ROWS(B$5:B5) ▶ указывает на первую ячейку всей строки.
ИНДЕКС(B:B,МАЛЕНЬКИЙ(ЕСЛИ(B$5:B$13<>””,СТРОКА(B$5:B$13)),СТРОКИ(B$5:B5))) ▶ проверяет 5-ю строку на наличие пробелов клетки. Поскольку в 5-й строке нет пустых ячеек, возвращается первый элемент 5-й строки, то есть Рикки Бен.
=ЕСЛИ(СТРОКИ(B$5:B5)>СЧЁТ(B:B)",",ИНДЕКС(B:B,МАЛЕНЬКИЙ(ЕСЛИ(B$5:B$13<>"",СТРОКА(B$5:B $13)),ROWS(B$5:B5)))) ▶ проверяет наличие пустых строк во всей таблице и возвращает строки, в которых нет пустых ячеек.

Формула 3: очистка пустых строк в Excel с помощью функции ФИЛЬТР

Мы можем использовать функцию ФИЛЬТР, чтобы просто отфильтровать все пустые строки на листе Excel. Эта функция является функцией динамического массива.Это означает, что вы выполняете эту функцию только в одной ячейке, а затем эта функция автоматически охватит все соответствующие ячейки, в которых должен быть результат формулы.

Чтобы сделать это, выполните указанные ниже действия.

❶ Введите следующую формулу в ячейку G5.

❷ После этого нажмите кнопку ВВОД.

Разбивка формулы

(B5:B14<>”” ) ▶ проверяет наличие пустых ячеек в столбце B.
(C5:C14<>””) ▶ найдите пустые ячейки в столбце C.
(D5:D14<>””) ▶ находит пустую ячейку в столбце D.
(E5:E14<>””) ▶ проверяет столбец E на наличие пустых ячеек.
=ФИЛЬТР(B5:E14,(B5:B14<>"")*(C5:C14<>"")*(D5:D14<>"")*(E5:E14<>"")) ▶ отфильтровать все пустые ячейки, оставив только строки без этих пустых ячеек.

Формула 4: пропустить пустые строки в Excel с помощью функции COUNTBLANT

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

Для этого выполните следующие действия:

❶ Прежде всего введите следующую формулу в ячейку F5.

❷ Затем нажмите кнопку ВВОД.

Эта формула вернет значение 0, так как в пятой строке нет пустых ячеек.

❸ После этого перетащите значок маркера заполнения в конец столбца "Пробелы".

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

❹ Теперь выберите всю таблицу данных и нажмите CTRL + SHIFT + L.

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

❺ После этого нажмите на раскрывающийся значок заголовка Blanks.

❻ Снимите отметку с опций, отличных от 0, в данном случае 4.

❼ Наконец, нажмите кнопку ОК.

Эти шаги отфильтруют все пустые строки и сохранят только полные строки, как показано на изображении ниже:

Формула 5: исключение пустых строк в Excel с помощью функций ЕСЛИ, И и ЕПУСТО

В этом разделе мы будем использовать функции ЕСЛИ, И и ЕПУСТО, чтобы указать, какая строка пуста, а какая нет. Используя эту функцию, мы пометим строки с пустыми ячейками как пустые, а строки без одной пустой ячейки — как непустые.

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

Чтобы сделать это, выполните указанные ниже действия.

❶ Введите следующую формулу в ячейку F5.

❷ Нажмите кнопку ВВОД.

❸ Затем нарисуйте значок маркера заполнения в конце столбца "Пробелы".

❹ После этого выберите всю таблицу данных.

❺ Теперь нажмите CTRL + SHIFT + L, чтобы применить функцию фильтра к таблице данных.

❻ Нажмите значок раскрывающегося списка в углу заголовка таблицы "Пробелы".

❼ Теперь снимите флажок «Пусто» в списке.

❽ Наконец, нажмите кнопку ОК.

После выполнения всех шагов у вас будет таблица данных без пустых строк, как показано на рисунке ниже:

Что нужно помнить

📌 Нажмите CTRL + SHIFT + ENTER, чтобы вставить формулы массива.

📌 Функция ФИЛЬТР доступна только в версии Excel 365.

Заключение

Подводя итог, мы обсудили 5 формул для удаления пустых строк в Excel. Вам рекомендуется скачать практическую рабочую тетрадь, прилагаемую к этой статье, и практиковать все методы с ней.И не стесняйтесь задавать любые вопросы в разделе комментариев ниже. Мы постараемся ответить на все соответствующие запросы как можно скорее. Посетите наш веб-сайт Exceldemy, чтобы узнать больше.

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

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

Мне удалось сделать эту формулу для пропуска пустых ячеек, я подтверждаю это с помощью CTRL+ALT+DEL:
=IF(ROWS(DJ$2:DJ2)>COUNTA(DJ$2:DJ$301);"" ;ИНДЕКС(DJ$2:DJ$301;МАЛЕНЬКИЙ(ЕСЛИ(DJ$2:DJ$301<>AX$18;СТРОКА(DJ$2:DJ$301)-СТРОКА(DJ$2)+1);СТРОКИ(DJ$2:DJ2))) )

Факты об Excel

Настроить панель быстрого доступа. Из «Все команды» добавьте «Произнести ячейки» или «Проговорить ячейки при вводе» в QAT. Выберите ячейки. Нажмите "Проговорить ячейки".

Аладин Акюрек

MrExcel MVP

Марка85

Новый участник

У меня есть данные в двух столбцах, и каждая строка представляет человека. Колонны DJ и FG. Мне нужно отфильтровать первый столбец и удалить пробелы и удалить ячейки, которые не имеют аналога в столбце FG.

Аладин Акюрек

MrExcel MVP

Марка85

Новый участник

Аладин Акюрек

MrExcel MVP

Отсутствует скобка.

=ЕСЛИОШИБКА(ИНДЕКС($DJ$2:$DJ$301,МАЛЕНЬКИЙ(ЕСЛИ(1-($DJ$2:$DJ$301="")),ЕСЛИ(1-($FG$2:$FG$301="" ),СТРОКА($DJ$2:$DJ$301)-СТРОКА($DJ$2)+1)), СТРОКА($FI$2:FI2)) ,"")

Марка85

Новый участник

Аладин Акюрек

MrExcel MVP

Мими1111

Новый участник

Я знаю, что эта тема закрыта уже два года, но я подумала, что вы могли бы помочь. У меня есть рабочий лист с тремя столбцами. В первом столбце (столбец A) есть числа, во втором — текст, а в третьем — опять числа. В книге много пустых ячеек, которые я хотел бы пропустить. Справочным столбцом является столбец B, так как это столбец, в котором содержится вся информация, тогда как иногда столбец A и столбец C не обязательно содержат информацию (что нормально). Однако затем эти ячейки все равно должны соотноситься с текстом в столбце B, даже если они пусты (см. ниже).

В результате мне нужна формула, которая создает одну согласованную таблицу (по сути, копию столбцов A, B и C) без пустых строк. Я нашел вашу функцию пропуска массива и безуспешно пытался ее настроить (таблица выглядит точно так же (как слева) без каких-либо удаленных пустых ячеек. Честно говоря, я даже не уверен, что говорит функция. Функции массива за пределами моих возможностей Excel.

Не могли бы вы помочь, пожалуйста? Я вставил его как новую тему на форуме, но получил только один ответ, в котором предлагался VBA (чего я не хочу делать, поскольку он должен быть прозрачным. В конце концов, рецензент должен увидеть, как была сделана таблица). ).

Это функция, которую я использовал в столбце E (поле "Задача" справа)

В этом кратком совете я объясню, почему удаление строк Excel с помощью выбора пустых ячеек -> удаление строки не является хорошей идеей, и покажу вам 3 быстрых и правильных способа удалить пустые строки без разрушения ваших данных. Все решения работают в Excel 2019, 2016, 2013 и более ранних версиях.

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

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

Никогда не удаляйте пустые строки, выбирая пустые ячейки

Во всем Интернете можно встретить следующий совет по удалению пустых строк:

Выделите данные с первой до последней ячейки.
Нажмите F5, чтобы открыть диалоговое окно Перейти к.
В диалоговом окне нажмите кнопку Специальные….
В диалоговом окне "Перейти к специальному" выберите переключатель "Пробелы" и нажмите ОК.
Щелкните правой кнопкой мыши любую выделенную ячейку и выберите "Удалить…".
В диалоговом окне "Удалить" выберите "Вся строка" и нажмите Вся строка.

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

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

Например, у нас есть таблица клиентов, всего 6 строк. Мы хотим удалить строки 3 и 5, потому что они пусты.

Выполните указанные выше действия, и вы получите следующее:

Строка 4 (Роджер) также отсутствует, поскольку ячейка D4 в столбце "Источник трафика" пуста: (

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

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

Удалить пустые строки с помощью ключевого столбца

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

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

Удалить пустые строки, если в вашей таблице нет ключевого столбца

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

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

Добавьте столбец "Пробелы" в конец таблицы и вставьте следующую формулу в первую ячейку столбца: =СЧИТАТЬПУСТОТЫ(A2:C2) .
Эта формула, как следует из ее названия, подсчитывает пустые ячейки в указанном диапазоне, A2 и C2 — это первая и последняя ячейки текущей строки соответственно.
Скопируйте формулу по всему столбцу. Пошаговые инструкции см. в разделе, как ввести одну и ту же формулу во все выбранные ячейки за раз.
Теперь у нас есть ключевой столбец в нашей таблице :). Примените фильтр к столбцу «Пробелы» (см. пошаговые инструкции выше), чтобы отобразить только строки с максимальным значением (3). Число 3 означает, что все ячейки в определенной строке пусты.
Затем выберите все отфильтрованные строки и удалите целые строки, как описано выше.
В результате удаляется пустая строка (строка 5), все остальные строки (с пустыми ячейками и без них) остаются на месте.
Теперь вы можете удалить вспомогательный столбец. Или вы можете применить к столбцу новый фильтр, чтобы отобразить только те строки, в которых есть одна или несколько пустых ячеек.
Для этого снимите флажок "0" и нажмите ОК.

Самый быстрый способ удалить все пустые строки — инструмент «Удалить пробелы»

Самый быстрый и безупречный способ удаления пустых строк – инструмент "Удалить пробелы", входящий в состав Ultimate Suite for Excel.

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

Как удалить пустые строки за 4 простых шага

Добавив Ultimate Suite на ленту Excel, вы можете сделать следующее:

Вот оно!Всего несколько кликов и у вас чистая таблица, все пустые строки исчезли, а порядок строк не нарушен!

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

Формула 1: игнорировать пустые ячейки в одном столбце

Эта конкретная формула добавляет 10 к значению в ячейке A2, только если значение в ячейке A2 не пустое.

Формула 2: игнорировать пустые ячейки в нескольких столбцах

Эта конкретная формула складывает значения в ячейках A2 и B2, только если обе ячейки не пусты.

В следующих примерах показано, как использовать каждую формулу на практике.

Пример 1. Игнорирование пустых ячеек в одном столбце

Предположим, у нас есть следующие данные в Excel, которые показывают очки, набранные различными баскетболистами:

Теперь предположим, что мы используем следующую формулу, чтобы добавить 10 к каждому значению в столбце A:

На следующем снимке экрана показано, как использовать эту формулу:

Обратите внимание, что 10 добавляется к каждой ячейке в столбце A, даже если ячейка в столбце A пуста.

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

На следующем снимке экрана показано, как использовать эту формулу:

Обратите внимание, что 10 добавляется только к каждой ячейке в столбце A, которая не пуста.

Пример 2. Игнорирование пустых ячеек в нескольких столбцах

Предположим, у нас есть следующие данные в Excel, которые показывают набранные очки и подборы, сделанные различными баскетболистами:

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

На следующем снимке экрана показано, как использовать эту формулу:

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

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

Читайте также: