Как сделать пустую ячейку в excel

Обновлено: 05.07.2024

И самое удивительное - если выделить такую ячейку вручную и нажать Delete (или вкладка Главная -группа Редактирование -Очистить содержимое) - то ячейка становится действительно пустой и с ней начинают работать формулы и другие функции Excel как с реально пустой.
Здесь возникает главный вопрос: что с этой ячейкой не так, если там и до Delete ничего не было?
А не так с ней вот что: практически во всех программах есть такое понятие строковых(текстовых) данных, как строка нулевой длины(еще её часто называют нулевая строка. В Visual Basic for Application такая строка обозначается константой vbNullString , в ячейках её можно встретить как сочетание двух кавычек подряд - "" ). Визуально такой текст себя никак не проявляет, однако это все же текст. А любой текст это уже не пусто, но и не число.
Откуда может появится такой текст в ячейках? Здесь несколько вариантов:

Он есть в ячейках изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе(вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет - они просто заполняются строкой нулевой длины.
в ячейке была записана формула, результатом которой стал текст нулевой длины. Самый простой пример такой формулы:
=ЕСЛИ( A1 =1;10;"")
=IF(A1=1,10,"")
в итоге, если в ячейке A1 записано любое значение, отличное от 1 формула вернет строку нулевой длины. И если впоследствии формулу заменять значением(Как удалить в ячейке формулу, оставив значения?), то получим нашу псевдо пустую ячейку.
Если формулы создаются Вами и в дальнейшем планируются производить с этими ячейками математические действия, то лучше вместо "" ставить 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: Файл -Параметры -Дополнительно -Показывать нули в ячейках, которые содержат нулевые значения

А если такой файл делали не Вы - он достался "по наследству" или это выгрузка из другой программы, что делать тогда? Я предлагаю такой вот нехитрый код, который во всех выделенных ячейках заменит строки нулевой длины на нормальные пустые ячейки:

Sub ReplaceNullString() Dim rR As Range, rF As Range, rC As Range Dim avR, lr As Long, lc As Long Set rR = Intersect(ActiveSheet.UsedRange, Selection) On Error Resume Next Set rR = rR.SpecialCells(xlCellTypeConstants) On Error GoTo 0 If rR Is Nothing Then MsgBox "В выделенных ячейках нет значений!", vbInformation, "www.excel-vba.ru" Exit Sub End If Set rF = rR.Find(vbNullString, , xlFormulas, xlWhole) If Not rF Is Nothing Then avR = rR.Value If Not IsArray(avR) Then Redim avR(1 to 1, 1 to 1) avR(1,1) = rR.Value end if For lr = 1 To UBound(avR, 1) For lc = 1 To UBound(avR, 2) If avR(lr, lc) = "" Then rR.Item(lr, lc).Value = Empty Next lc Next lr MsgBox "Строки нулевой длины заменены", vbInformation, "www.excel-vba.ru" Exit Sub End If MsgBox "Строк нулевой длины на листе нет или лист защищен", vbInformation, "www.excel-vba.ru" End Sub

Как это работает:
если раньше никогда не работали с макросами, то рекомендую ознакомиться со статьями:
Что такое макрос и где его искать?
Что такое модуль? Какие бывают модули?

создаем в книге новый стандартный модуль: Alt+F11 -Insert -Module()
копируем в созданный модуль приведенный выше код
выделяем нужный диапазон(если надо заменить на всем листе - то можно выделить все ячейки листа или целиком нужные столбцы - программа сама определить нужные данные)
нажимаем Alt+F8 и в появившемся окне выбираем ReplaceNullString

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

Есть разные способы решения этой проблемы. Я покажу вам несколько быстрых и один ОЧЕНЬ быстрый способ заполнить пустые ячейки значениями.

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

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

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

Формула Excel для заполнения пустых ячеек значениями, стоящими выше / ниже

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

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

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

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

Формула (=A2) показывает, что A3 получит значение из A2, и будет заполнена предыдущим значением.

Нажмите Ctrl + Enter , чтобы автоматически вставить формулу сразу во все выделенные позиции.

Ну вот! Теперь каждая выделенная ячейка ссылается на ячейку, находящуюся над ней.

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

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

Заполните пустые ячейки нулями или другим определенным значением

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

Способ 1.

Выделите пустые ячейки, как мы уже делали.
Нажмите F2 для активации режима редактирования в строке формул. Или просто кликните туда мышкой.
Введите желаемое число или текст.
Нажмите Ctrl + Enter .

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

Способ 2.

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

Заполнение пустых ячеек при помощи макроса VBA.

Если подобную операцию вам приходится делать часто, то имеет смысл создать для неё отдельный макрос, чтобы не повторять всю вышеперечисленную цепочку действий вручную. Для этого жмём Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), чтобы открыть редактор VBA, затем вставляем туда новый пустой модуль через меню Insert – Module. Далее копируем или вводим туда вот такой короткий код:

Sub Fill_Blanks()
For Each cell In Selection
If IsEmpty(cell) Then cell.Value = cell.Offset(-1, 0).Value
Next cell
End Sub

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

Для удобства, можно назначить этому макросу сочетание клавиш или даже поместить его в Личную Книгу Макросов (Personal Macro Workbook), чтобы он был доступен при работе в любом вашем файле Excel.

Какой бы способ вы ни выбрали, заполнение таблицы Excel займет у вас буквально минуту.

Как быстро заполнить пустые ячейки без использования формул.

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

Поэтому постараемся привести таблицу к стандартному виду, заполнив все пустоты и разъединив ранее объединенные области.

Перейдите на ленте на вкладку AblebitsTools.

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

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

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

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

Проверьте это! Загрузите полнофункциональную пробную версию надстройки Fill Blank Cells и посмотрите, как она может сэкономить вам много времени и сил.

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

Быстрое удаление пустых столбцов в Excel - В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых столбцов в Excel не может…

Как полностью или частично зафиксировать ячейку в формуле - При написании формулы Excel знак $ в ссылке на ячейку сбивает с толку многих пользователей. Но объяснение очень простое: это всего лишь способ ее зафиксировать. Знак доллара в данном случае служит только одной цели - он указывает,…

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

Относительные и абсолютные ссылки – как создать и изменить - В руководстве объясняется, что такое адрес ячейки, как правильно записывать абсолютные и относительные ссылки в Excel, как ссылаться на ячейку на другом листе и многое другое. Ссылка на ячейки Excel, как бы просто она ни…

6 способов быстро транспонировать таблицу - В этой статье показано, как столбец можно превратить в строку в Excel с помощью функции ТРАНСП, специальной вставки, кода VBA или же специального инструмента. Иначе говоря, мы научимся транспонировать таблицу. В этой статье вы найдете…

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

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

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

6 способов — как безопасно удалить лишние пустые строки в Excel - Это руководство научит вас нескольким простым приемам безопасного удаления нескольких пустых строк в Excel без потери информации. Пустые строки в таблице — это проблема, с которой мы все время от времени сталкиваемся, особенно при объединении…

Существует несколько способов для того, чтобы ввести данные в ячейки в Excel:

Выбрать нужную ячейку — Напечатать необходимые данные — Нажать Enter или Tab.
Выбрать нужную ячейку — Напечатать необходимые данные в строке формул — Нажать Enter.

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

Выделение ячеек

Для выделения отдельной ячейки в Excel необходимо кликнуть по этой ячейке.

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

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

В этом видео-уроке мы рассказали о выделении диапазонов данных в Excel:

Добавление ячеек

Добавление ячеек в Excel осуществляется следующим образом:

1. Выбрать ячейку, рядом с которой необходимо вставить еще одну ячейку;

2. На вкладке Главная, в группе Ячейки выбрать Вставить — Вставить ячейки:

3. В появившемся окне выбрать один из пунктов:

Добавить ячейки, со сдвигом вправо;
Добавить ячейки, со сдвигом вниз.

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

Удаление ячеек

Удаление ячеек в Excel осуществляется одним из следующих способов:

1. Выбрать ячейку, которую необходимо удалить.

На вкладке Главная, в группе Ячейки выбрать Удалить — Удалить ячейки:

В появившемся окне выбрать один из пунктов:

Удалить ячейки, со сдвигом влево;
Удалить ячейки, со сдвигом вверх.

Выделенная ячейка будет удалено, а ячейки правее/ниже будут сдвинуты в выбранном вами направлении.

2. Кликнуть правой кнопкой мыши по ячейке, которую необходимо удалить.

В появившемся окне выбрать один из пунктов:

Удалить ячейки, со сдвигом влево;
Удалить ячейки, со сдвигом вверх.

Выделенная ячейка будет удалено, а ячейки правее/ниже будут сдвинуты в выбранном вами направлении.

Копирование, вставка и перемещение ячеек

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

Также ячейку можно скопировать нажатием клавиш Ctrl+C,

либо вырезать (Ctrl+X) если на старом месте данные больше не нужны,

после чего вставить скопированные/вырезанные данные в нужном месте (Ctrl+V)

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

Объединение ячеек

Excel позволяет объединить несколько ячеек в одну.

Для этого необходимо выбрать ячейки, которые должны быть объединены, затем на вкладке Главная в группе Выравнивание нажать на кнопку Объединить и поместить в центре:

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

Сравните таблицу до и после объединения ячеек A1 и B1:

До объединения

После объединения

По нажатию на треугольник, расположенный в правой части кнопки Объединить и поместить в центре вам будут также доступны дополнительные опции:

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

Размеры ячеек

Ширина столбцов

Для того, чтобы изменить ширину столбца в Excel, необходимо выполнить одно из следующих действий:

1. Потянуть за правый край того столбца, который вы хотите увеличить или уменьшить:

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

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

Высота строк

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

1. Потянуть за нижний край той строки, которую вы хотите увеличить или уменьшить:

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

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

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

Воспользовавшись кнопкой Формат, расположенной на вкладке Главная в группе Ячейки, вы сможете:

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

Игнорировать пустые ячейки при условном форматировании в Excel
Игнорировать нулевые ячейки в условном форматировании в Excel

Вас может заинтересовать:

Нажмите Kutools > Вставить > Заполнить пустые ячейки . Этот Kutools for Excel Заполнить пустые ячейки может быстро заполнить все пустые ячейки значением выше или фиксированным значением в выбранном диапазоне. См. Снимок экрана ниже:

Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 60 дней. Загрузите бесплатную пробную версию

Вкладка Office Включите редактирование и просмотр с вкладками в Office и сделайте вашу работу намного проще …

Повторно использовать что угодно: добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния: несколько книг и листов в одну; Объединить несколько ячеек/строк/столбцов без потери данных; Объедините повторяющиеся строки и суммируйте.
Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Из одной книги в несколько файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
Вставить пропуск скрытых/отфильтрованных строк; Подсчет и сумма по цвету фона; Массовая отправка персонализированных писем нескольким получателям.
Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделе, дню, частоте и т. Д. Фильтр жирным шрифтом, формулами, комментарием …
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Игнорировать пустые ячейки при условном форматировании в Excel

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Сэкономьте 50% времени, и уменьшайте тысячи щелчков мышью для вас каждый день!

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

1. Продолжайте оставаться в диалоговом окне Диспетчер правил условного форматирования , затем нажмите кнопку Новое правило . См. снимок экрана:

Примечание : диалоговое окно Диспетчер правил условного форматирования можно открыть, нажав Условное форматирование > Управление правилами на вкладке Главная .

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

Метод 1

a. В поле Выберите тип правила выберите Форматировать только ячейки, содержащие ;
b. Выберите Пробелы в раскрывающемся списке Форматировать только ячейки с помощью ;
c. Не выбирайте какой-либо формат и нажмите кнопку OK . См. Снимок экрана:

Метод 2

3. Затем он возвращается в диалоговое окно Диспетчер правил условного форматирования . Независимо от того, какой метод вы используете для игнорирования пробелов, вам необходимо установить флажок Stop If True в этом диалоговом окне, а затем нажать кнопку OK . См. Снимок экрана:

Затем выбранные ячейки форматируются за исключением пробелы.

Игнорировать нулевые ячейки при условном форматировании в Excel

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

1. Выберите диапазон B2: B12, затем нажмите Условное форматирование > Новое правило на вкладке Главная ..

2. В диалоговом окне Изменить правило форматирования вам необходимо:

Примечание : вам необходимо изменить диапазон ячеек в формуле в соответствии с вашими потребностями.

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

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