Содержание

Как найти и удалить пустые строки в Microsoft Excel

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

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

Вот несколько способов найти и удалить пустые строки в Excel. Используйте то, что вам удобнее или удобнее для вашей конкретной простыни.

Удалить пустые строки в Excel

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

Выберите строку и выполните одно из следующих действий:

Щелкните правой кнопкой мыши и выберите Удалить или Удалить строку.
Нажмите Удалить >Удалить строки листа в ленте на Дом таб.
Используйте сочетание клавиш Ctrl + Дефис (-) в Windows или Контроль + Дефис (-) на Mac.

Если вы заметили несколько пустых строк, вы можете удалить их все сразу. Удерживайте нажатой клавишу Ctrl / Control при выборе каждой строки. Если строки смежные, вы можете перетащить курсор через диапазон, чтобы выбрать их. Затем используйте одно из вышеуказанных действий, чтобы удалить их.

Найти пустые строки в Excel

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

Найдите пустые строки с помощью фильтра

Один из способов легко найти пустые строки — использовать фильтр в Excel.

Выберите столбец или заголовок столбца в диапазоне ячеек, содержащем пустые строки.
Перейти к Дом вкладку, щелкните Сортировать и фильтровать, и выберите Фильтр.
Это ставит стрелка фильтра рядом с заголовком каждого столбца в вашем диапазоне. Щелкните одну из этих стрелок, чтобы открыть параметры фильтра.
Под опцией поиска нажмите Выбрать все чтобы отменить выбор всех параметров. Затем отметьте только вариант для Заготовки.
Нажмите хорошо применить фильтр.

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

Выделите строки отдельно или используйте клавишу Ctrl, чтобы выбрать их. Не надо перетащите, чтобы выбрать их, потому что это будет включать скрытые данные. Затем используйте любой из вышеперечисленных способов, чтобы удалить их.

Найдите пустые строки с помощью сортировки

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

Выделите ячейки или строки, перетащив через них курсор.
Перейти к Дом вкладку и щелкните Сортировать и фильтровать.
Выберите один из первых двух вариантов сортировки, Сортировать от А до Я или Сортировать от Я до А.

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

Найти пустые строки с помощью поиска

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

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

Найти: оставьте это поле пустым.
Внутри: Выбрать Лист.
Поиск: Выбрать По рядам.
Посмотреть: Выбрать Значения.
Установите флажок для Соответствие всему содержимому ячейки.

Нажмите Найти все.
Вы увидите пустые строки, перечисленные в результатах в нижней части окна. Чтобы выделить все эти результаты на листе, выделите их все с помощью Ctrl + A.
Затем вы можете нажать Закрывать в окне «Найти и заменить».

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

Чтобы пометить их для удаления, выберите Цвет заливки в разделе «Шрифт» на ленте, а также на Дом таб. Теперь вы можете щелкнуть в любом месте, не теряя найденные пробелы, а затем удалить эти строки.

Найдите пустые строки с помощью функции «Перейти к специальному»

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

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

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

Легко устраняйте пустые строки в Microsoft Excel

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

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

[expert_bq id=»1570″]введите одно место в найти , оставить заменить на пустой и убедитесь, что у вас есть матч всей ячейки содержание галочкой в опциях. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] поэтому в excel я пытаюсь избавиться от пустых ячеек между моими ячейками, в которых есть информация, используя F5 для поиска пустых ячеек, затем Ctrl + — для их удаления и сдвига ячеек вверх. Но когда я пытаюсь это сделать, он говорит мне, что «клеток не найдено».

Excel если пусто то другая ячейка — IT Новости из мира ПК

Выделите ячейки или строки, перетащив через них курсор.
Перейти к Дом вкладку и щелкните Сортировать и фильтровать.
Выберите один из первых двух вариантов сортировки, Сортировать от А до Я или Сортировать от Я до А.

Откройте «visual basic viewer» (Alt + F11) Найдите интересующую книгу в проекте explorer и дважды щелкните ее (или щелкните правой кнопкой мыши и выберите просмотреть код). Это откроет окно «view code». Выберите «Workbook» в раскрывающемся меню (Общие) и «SheetCalculate» в раскрывающемся меню (Объявления).

Разберем несколько вариантов как можно убрать нули в ячейках в Excel заменив их либо на пустое поле, либо на альтернативные нулю символы (например, прочерк).

Приветствую всех, дорогие читатели блога TutorExcel.Ru!

Обработка таблиц с числовыми данными практически неотъемлемая часть работы в Excel для многих пользователей программы.

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

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

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

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

Предположим, что у нас имеется таблица с числами (большого размера, чтобы наглядно оценить различие во внешнем виде), где в частности есть достаточно много нулей, и на ее примере попытаемся удалить ненулевые значения:

На всем листе

Если нам нужно убрать нули в каждой без исключения ячейке листа, то перейдем в панели вкладок Файл -> Параметры -> Дополнительно (так как эти настройки относятся в целом к работе со всей книгой):

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

Нажимаем OK и в результате исходная таблица приобретает следующий вид:

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

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

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

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

Частично эту проблему может решить следующий вариант.

В выделенных ячейках

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

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

В начале давайте вспомним, что любое число в Excel имеет формат отображения в виде маски A;B;C;D, где A, B, C, D — формат записи и точка с запятой, отделяющая их друг от друга:

A — запись когда число положительное;
B — запись когда число отрицательное;
C — запись когда число равно нулю;
D — запись если в ячейке не число, а текст (обычно для чисел не используется).

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

Так как наша задача в удалении именно нуля из записи, то нам нужно в маске прописать как будет выглядеть третий параметр (часть C из маски), а заодно и первый (часть A, положительное число) со вторым (часть B, отрицательное число).

Поэтому, чтобы убрать нули из выделенных ячеек, щелкаем по ним правой кнопкой мыши и в контекстном меню выбираем Формат ячеек -> Число, а далее среди форматов переходим во Все форматы:

Затем в маске прописываем формат отображения нуля, вместо него либо ничего не пишем (маска # ##0;- # ##0; чтобы ячейка стала пустой), либо пишем заменяющий символ (маска # ##0;-# ##0;»-«, чтобы в ячейке стоял прочерк), и нажимаем OK.

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

В формулах

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

Давайте для исходной таблицы пропишем отклонение между периодами с помощью функции ЕСЛИ.
Вместо стандартной формулы =A1-B1 пропишем =ЕСЛИ(A1-B1=0;»-«;A1-B1):

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

В сводных таблицах

Для начала пусть у нас имеется простая и небольшая таблица с данными (при этом в таблице не все ячейки будут заполнены), на основе которой мы построим сводную таблицу:

Задача удаления нулей из сводной таблицы можно условно разделить на 2 направления:

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

Так как первый вариант мы уже детально разбирали выше, то давайте поподробнее остановимся на втором, в котором также выделим 2 способа, чтобы убрать нули из таблицы:

Создание и применение фильтра

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

Теперь щелкаем по значку фильтра для поля Количество и в выпадающем списке снимаем галочку напротив значений 0 или (пусто), тем самым исключив эти значения из отображения:

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

Настройка параметров сводной таблицы

С помощью настройки параметров сводной таблицы мы также можем удалить нули из ячеек. Во вкладке Работа со сводными таблицами выбираем Анализ -> Сводная таблица -> Параметры -> Макет и формат:

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

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

Спасибо за внимание!
Если у вас есть вопросы или мысли по теме статьи — обязательно спрашивайте и пишите в комментариях, не стесняйтесь.

[expert_bq id=»1570″]Лог_ выражение напишем В2 20000 Значение_ если_ истина В2 0,85 В пустое поле Значение_ если_ ложь необходимо встать курсором и в поле имени слева от строки формул выбрать функцию ЕСЛИ. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Мне нужно вернуть пустую ячейку из формулы Excel, но похоже, что Excel обрабатывает пустую строку или ссылку на пустую ячейку иначе, чем настоящая пустая ячейка. Так что по сути мне нужно что-то вроде

Эксель Если Ячейка не Пустая то Формула • В выделенных ячейках

Функция ЕСЛИ в Excel –

A — запись когда число положительное;
B — запись когда число отрицательное;
C — запись когда число равно нулю;
D — запись если в ячейке не число, а текст (обычно для чисел не используется).

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

Вернуть пустую ячейку из формулы в Excel

Мне нужно вернуть пустую ячейку из Excel formula, но похоже, что Excel обрабатывает пустую строку или ссылку на пустую ячейку иначе, чем истинную пустую ячейку. Так что, по сути, мне нужно что-то вроде

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

EDIT: как и было рекомендовано, я попытался вернуть NA(), но для моих целей это тоже не сработало. Есть ли способ сделать это с VB?

EDIT: я создаю рабочий лист, который извлекает данные из других рабочих листов и форматируется в соответствии с очень специфическими требованиями приложения, импортирующего данные в базу данных. У меня нет доступа к изменению реализации этого приложения, и оно терпит неудачу, если значение «» вместо фактически пустого.

17 ответов

В Excel кто-нибудь знает, как смотреть вверх из данной строки, чтобы найти последнюю пустую ячейку? У меня есть странно отформатированный экспорт excel, из которого я пытаюсь отчитаться. Однако я застрял, пытаясь найти эффективную формулу для поиска данных. Вот 2 примера экспортированного формата.

Мой asp.net mvc работает на windows azure веб-сайте на windows cloud. У меня есть лист excel с ячейками, которые принимают входные данные, и еще одна ячейка, основанная на формуле, которая вычисляется на основе входных данных. Я могу использовать открытый код xml sdk c#, чтобы успешно выполнить.

Результатом формулы в ячейке в Excel должно быть число, текст, логическое (логическое) значение или ошибка. Не существует типа значения ячейки формулы «empty» или «blank».

Одна из практик, которую я видел, заключается в использовании NA() и ISNA(), но это может действительно решить вашу проблему, так как существует большая разница в том, как NA() обрабатывается другими функциями (SUM(NA()) — это #N/A, а СУММА(A1) равна 0, если A1 пуста).

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

Все, что вам нужно , это настроить именованный диапазон, скажем GetTrueBlank , и вы сможете использовать следующий шаблон, как и в вашем вопросе:

Шаг 1. Поместите этот код в модуль VBA.

Шаг 2. В ячейке Sheet1 в ячейке A1 добавьте именованный диапазон GetTrueBlank со следующей формулой:

Вот и все. Дальнейших шагов нет. Просто используйте формулу самоуничтожения. Поместите в ячейку, скажем B2 , следующую формулу:

Приведенная выше формула в B2 будет иметь значение trueblank, если вы введете 0 в A2 .

В приведенном выше примере вычисление формулы в trueblank приводит к пустой ячейке. Проверка ячейки с формулой ISBLANK положительно приводит к TRUE. Это харакири. Формула исчезает из ячейки при выполнении условия. Цель достигнута, хотя вы, вероятно, захотите, чтобы формула не исчезла.

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

То, что я пытаюсь сделать, должно быть легко, но нуб во мне показывает это. Я создаю базу данных в excel, используя форму dataentry на другом листе. При нажатии кнопки “enter” запускается макрос, который помещает информацию из листа dataentry в базу данных Excel. Проблема, конечно, в том, что.

Мне также нужна была таблица, которая является источником для графика, и я не хотел, чтобы какие-либо пустые или нулевые ячейки создавали точку на графике. Это правда, что вам нужно установить свойство графика, выбрать данные, скрытые и пустые ячейки на «show empty cells as Gaps» (нажмите переключатель). Это первый шаг.

Это действительно дает требуемый график без точек, когда возникает недопустимое значение ячейки. Конечно , это оставляет все ячейки с этим недопустимым значением для чтения #N/A , и это грязно.

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

Я сделал «copy» всех данных и вставил данные как «values».
Затем я выделил вставленные данные и сделал «replace» ( Ctrl — H ) пустых ячеек с какой-то буквой, я выбрал q , так как его нигде не было в моем листе данных.
Наконец, я сделал еще один «replace» и заменил q ничем.

Этот трехэтапный процесс превратил все «empty» клеток в «blank» клеток». Я попытался объединить шаги 2 & 3, просто заменив пустую ячейку пустой ячейкой, но это не сработало-мне пришлось заменить пустую ячейку каким-то реальным текстом, а затем заменить этот текст пустой ячейкой.

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

если вам нужна пустая ячейка, верните 0 вместо «» и THEN

установите формат чисел для ячеек следующим образом, где вам нужно будет придумать, что вы хотите для положительных и отрицательных чисел (первые два элемента разделены точками с запятой). В моем случае цифры, которые у меня были, были 0, 1, 2. и я хотел, чтобы 0 оказался пустым. (Я так и не понял, для чего использовался параметр text, но, похоже, он был необходим).

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

Вау, удивительное количество людей неправильно истолковали этот вопрос. Легко сделать так, чтобы клетка выглядела пустой. Проблема в том, что если вам нужно, чтобы ячейка была пустой, формулы Excel не могут возвращать «no value», а могут возвращать только значение. Возврат нуля, пробела или даже «» — это значение.

Таким образом, вы должны вернуть «magic value», а затем заменить его без значения с помощью поиска и замены или с помощью скрипта VBA. В то время как вы могли бы использовать пробел или «», я бы посоветовал использовать очевидное значение, такое как «NODATE», «NONUMBER» или «XXXXX», чтобы вы могли легко увидеть, где это происходит — довольно трудно найти «» в электронной таблице.

Используйте COUNTBLANK(B1)>0 вместо ISBLANK(B1) в операторе IF .

В отличие от ISBLANK() , COUNTBLANK() считает «» пустым и возвращает 1.

Так много ответов, которые возвращают значение, которое LOOKS пустое, но на самом деле не является пустой ячейкой as, как было запрошено.

Вставьте следующий код (основанный на ответе J.T. Граймс) внутри функции Workbook_SheetCalculate

NB: Этот процесс подобен скальпелю. Он удалит все содержимое любых ячеек, которые оцениваются с ошибкой #N/A, так что имейте в виду. Они уйдут, и вы не сможете вернуть их, не вернув формулу, которую они раньше содержали.

NB2: Очевидно, вам нужно включить macros, когда вы открываете файл, иначе он не будет работать, и ошибки #N/A останутся неотделенными

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

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

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

То,что я использовал, было небольшим взломом. Я использовал T(1), который вернул пустую ячейку. T-это функция в excel, которая возвращает свой аргумент, если это строка, а в противном случае пустая ячейка. Итак, что вы можете сделать, это:

Я использовал следующую работу, чтобы сделать мой excel более чистым:

Когда вы делаете какие-либо вычисления, «»даст вам ошибку, поэтому вы хотите рассматривать его как число, поэтому я использовал вложенный if statement, чтобы вернуть 0 вместо»», а затем, если результат равен 0, это уравнение вернет «»

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

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

Я не знаю, работает ли это с другими functions. I, которые не пробовали. Я использую Excel 2007 для достижения этой цели.

Чтобы на самом деле получить IF(A1=»»,,), чтобы вернуться как true, в одной и той же ячейке должно быть два поиска, разделенных &..Простой способ обойти это-сделать второй поиск ячейкой, которая находится в конце строки и всегда будет пустой.

Он использует функцию ISBLANK , чтобы проверить, действительно ли ячейка пуста в операторе IF . Если в ячейке есть что-то, A1 в этом примере, даже символ SPACE, , то ячейка не является EMPTY , и вычисление приведет к результату. Это позволит избежать появления ошибок в расчетах до тех пор, пока у вас не появятся цифры для работы.

Если ячейка равна EMPTY , то в ячейке расчета не будут отображаться ошибки из расчета. Если ячейка NOT EMPTY , то будут показаны результаты расчета. Это вызовет ошибку, если ваши данные плохие, страшный #DIV/0 !

Измените ссылки на ячейки и формулу по мере необходимости.

Ответ положительный — вы не можете использовать функцию =IF() и оставить ячейку пустой. «Looks empty»-это не то же самое, что пустой. Жаль, что две кавычки спина к спине не дают пустой ячейки, не стирая формулу.

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

Вернуть пустую ячейку из формулы в Excel — CodeRoad

Я сделал «copy» всех данных и вставил данные как «values».
Затем я выделил вставленные данные и сделал «replace» ( Ctrl — H ) пустых ячеек с какой-то буквой, я выбрал q , так как его нигде не было в моем листе данных.
Наконец, я сделал еще один «replace» и заменил q ничем.

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

Дополнительные данные → Похожие темы → Все про Exel → Как вставить значения → Как объединить ячейки → Как вставить форматы → Дополнительные данные → Вставить формулы→ Аргументы функции