Разберем несколько вариантов как можно убрать нули в ячейках в Excel заменив их либо на пустое поле, либо на альтернативные нулю символы (например, прочерк).
Приветствую всех, дорогие читатели блога TutorExcel.Ru!
Обработка таблиц с числовыми данными практически неотъемлемая часть работы в 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 или (пусто), тем самым исключив эти значения из отображения:
Как мы видим строки, где находились нулевые значения, скрылись, что нам и требовалось.
Перейдем к следующему варианту.
Настройка параметров сводной таблицы
С помощью настройки параметров сводной таблицы мы также можем удалить нули из ячеек. Во вкладке Работа со сводными таблицами выбираем Анализ -> Сводная таблица -> Параметры -> Макет и формат:
В разделе Формат нас интересует пункт Для пустых ячеек отображать, чтобы убрать нули в сводной таблице, нужно поставить галочку в соответствующем поле и оставить поле справа пустым.
В этом случае получается, что пустые ячейки из исходной таблицы подтянулись в сводную, а уже затем мы задали конкретный формат (пустое поле) для их отображения. В итоге получаем:
На этом все!
Скачать файл с примером.
Спасибо за внимание!
Если у вас есть вопросы или мысли по теме статьи — обязательно спрашивайте и пишите в комментариях, не стесняйтесь.
Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!
Поделиться с друзьями:
Поиск по сайту:
Russian (Pусский) translation by Ellen Nelson (you can also view the original English article)
Когда вы работаете в Excel с большими таблицами данных, вполне вероятно, что многие из ваших данных, могут оказаться нулями.
Это обычное явление, но это может сделать вашу таблицу трудной для чтения и навигации. Нам не нужно менять все эти нули и удалять их из данных, чтобы очистить нашу таблицу.
Вместо этого мы можем просто изменить то, как они отображаются. Вместо того, чтобы показывать все эти нули, мы могли бы удалить их одним простым способом в Excel. Давайте научимся.
Как быстро отобразить ячейки Excel пустыми, если в них нуль
Примечание: просмотрите этот короткий видеоурок или следуйте шагам ниже, которые дополняют это видео.
1. Откройте файл Excel с нулевыми ячейками
Мы начинаем с файла Excel с многочисленными ячейками 0, которые мы хотим превратить в пустые.
2. Как отключить отображение нулей в Excel
Теперь перейдите на вкладку Файл в Excel. Найдите кнопку Параметры с левой стороны и, давайте, нажмите на неё.
Теперь измените параметр Дополнительно с левой стороны. Прокрутим вниз. И посреди этих параметров, идём в поле, которое говорит: Показывать нули в ячейках, которые содержат нулевые значения. Этот параметр управляет настройками для всей книги. Нам нужно снять этот флажок и, теперь, давайте нажмем OK.
3. Теперь Excel показывает нулевые ячейки как пустые
Когда мы вернемся к книге, вы увидите, что все нули теперь скрыты. Нулевое значение всё ещё находится внутри ячейки, но Excel меняет способ его отображения, и ячейки выглядят пустыми. Это также может быть полезно, например, для сводной таблицы.
Закругляемся!
Теперь вы знаете, как изменить Excel так, что если ячейка равна 0, её можно сделать пустой при необходимости. Этот вариант полезен для того, чтобы электронные таблицы были более читабельными и сосредоточенными на значимых данных.
Более полезные учебники по Excel на Envato Tuts+
Найдите исчерпывающие уроки по Excel на Envato Tuts+, которые помогут вам лучше научиться работать с данными в электронных таблицах. У нас также есть быстрые серии по Excel в 60-секундной, чтобы быстрее изучить больше инструментов Excel.
Вот несколько уроков по Excel, на которые можно перейти в настоящее время:
Помните: каждый инструмент Microsoft Excel, который вы изучаете, позволяют вам создавать более мощные электронные таблицы.
Skip to content
В этой статье вы узнаете, как выбрать сразу все пустые ячейки в электронной таблице Excel и заполнить их значением, находящимся выше или ниже, нулями или же любым другим шаблоном.
Заполнять пустоты или нет? Этот вопрос часто касается пустых ячеек в таблицах Excel. С одной стороны, ваша таблица выглядит аккуратнее и читабельнее, если вы не загромождаете ее повторяющимися значениями. С другой стороны, пустые ячейки могут вызвать проблемы при сортировке, фильтрации данных или создании сводной таблицы. В этом случае вам желательно заполнить все поля.
Таким образом, мой ответ — «Заполнить». А теперь посмотрим, как это сделать.
- Как быстро выделить пустые ячейки
- Заполняем значениями сверху или снизу при помощи формулы
- Как заменить пустые ячейки нулями либо произвольными значениями
- Используем простой макрос VBA
- Как быстро заполнить пустые ячейки не используя формулы.
Есть разные способы решения этой проблемы. Я покажу вам несколько быстрых и один ОЧЕНЬ быстрый способ заполнить пустые ячейки значениями.
Как выделить пустые ячейки на листах Excel.
Перед тем, как заполнить пустоты в таблице Excel, сначала нужно их выделить. Если у вас большая таблица с десятками незаполненных областей, разбросанными по ней, то потребуется много времени, чтобы сделать это вручную. Вот быстрый приём для выбора пустых ячеек.
- Выберите столбцы или строки, в которых вы хотите заполнить пустоты.
- Нажмите
Ctrl + Gили жеF5для отображения диалогового окна “Перейти”. - Щелкните по кнопке «Выделить».
- Выберите «Пустые ячейки».
- Далее выберите, что будем выделять. Например, формулы, комментарии, константы, пробелы и т. д.
- Установите переключатель «Пустые ячейки» и нажмите «ОК».
Теперь выделены только пустые ячейки из выбранного диапазона, и вы готовы к следующему шагу.
Формула Excel для заполнения пустых ячеек значениями, стоящими выше / ниже
Выбрав пустые ячейки в таблице, вы можете заполнить их значениями, стоящими сверху или снизу, или же просто вставить какое-то определенное содержимое.
Если вы собираетесь заполнить пробелы значением из ближайшей заполненной ячейки выше или ниже, вам нужно ввести очень простую формулу в одну из пустых ячеек. Затем просто скопируйте ее во все остальные. Вот как это сделать.
- Выделите все незаполненные ячейки, как описано выше.
- Нажмите
F2или просто поместите курсор в строку формул, чтобы начать писать формулу в активной ячейке.
Как видно на скриншоте ниже, активная ячейка – A3, то есть по умолчанию это самая левая верхняя из всех незаполненных.
- Введите знак равенства (=).
- Наведите курсор на ячейку, находящуюся выше или ниже, с помощью клавиши со стрелкой вверх или вниз или просто кликните по ней мышкой.
Формула (=A2) показывает, что A3 получит значение из A2, и будет заполнена предыдущим значением.
- Нажмите
Ctrl + Enter, чтобы автоматически вставить формулу сразу во все выделенные позиции.
Ну вот! Теперь каждая выделенная ячейка ссылается на ячейку, находящуюся над ней.
Поэтому рекомендую не останавливаться и сразу после ввода формул заменить их на значения. Выполните следующие простые шаги:
- У вас выделены все ячейки с формулами, которые вы только что ввели и хотите преобразовать.
- Нажмите
Ctrl + Cили жеCtrl + Ins, чтобы копировать формулы и их результаты в буфер обмена. - Нажмите
Shift + F10а потомV, чтобы вставить обратно в выделенные позиции только значения.Shift + F10 + V— это самый быстрый способ использовать диалог Excel «Специальная вставка».
Заполните пустые ячейки нулями или другим определенным значением
Что, если вам нужно заполнить все пробелы в таблице нулями, любым другим числом или просто одинаковыми данными? Вот два способа решить эту проблему.
Способ 1.
- Выделите пустые ячейки, как мы уже делали.
- Нажмите
F2для активации режима редактирования в строке формул. Или просто кликните туда мышкой. - Введите желаемое число или текст.
- Нажмите
Ctrl + Enter.
Несколько секунд — и все пустые ячейки одинаково заполнены введенным вами словом, символом либо нулями при необходимости.
Способ 2.
- Выделите диапазон с пустыми ячейками.
- Нажмите
Ctrl + Hдля отображения диалогового окна «Найти и заменить». Или используйте меню. - В этом окне перейдите на вкладку «Заменить».
- Оставьте поле «Найти» пустым и введите необходимое значение в текстовое поле «Заменить на».
- Щелкните » Заменить все».
Пустые ячейки будут заполнены значением, которое вы указали.
Заполнение пустых ячеек при помощи макроса 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 займет у вас буквально минуту.
Как быстро заполнить пустые ячейки без использования формул.
Если вы не хотите иметь дело с формулами каждый раз, когда заполняете пустоты в вашей таблице, то можете использовать очень полезную надстройку Ultimate Suite для Excel, созданную разработчиками Ablebits. Входящая в неё утилита «Заполнить пустые ячейки» автоматически копирует в пустые клетки таблицы значение из первой заполненной ячейки снизу или сверху. Далее мы рассмотрим, как это работает.
Вот наши данные о продажах в разрезе менеджеров и регионов. Некоторые из продавцов работали в нескольких регионах, сведения об их продажах записаны друг под другом. Также объединены ячейки месяцев. Таблица выглядит достаточно читаемо. Однако, если нужно будет отфильтровать или просуммировать данные по менеджерам, или же найти сумму продаж по региону за определенный месяц, то сделать это будет весьма затруднительно. Этому будут мешать пустые и объединенные ячейки.
Поэтому постараемся привести таблицу к стандартному виду, заполнив все пустоты и разъединив ранее объединенные области.
Перейдите на ленте на вкладку AblebitsTools.
- Установите курсор в любую ячейку таблицы, в которой вам нужно заполнить пустые ячейки.
- Щелкните значок «Заполнить пустые ячейки (Fill Blank Cells)».
На экране появится окно надстройки, в котором перечислены все столбцы и указаны параметры заполнения.
- Снимите отметку со столбцов, в которых нет пустых ячеек.
- Выберите действие из раскрывающегося списка в правом нижнем углу окна.
Если вы хотите заполнить пустые поля значением из ячейки, находящейся выше, выберите параметр «Заполнить ячейки вниз (Fill cells downwards)». Если вы хотите скопировать содержимое из ячейки ниже, выберите в этом же выпадающем списке «Заполнить ячейки вверх (Fill cells upwards)». В нашем случае выбираем заполнение вниз.
- Нажмите кнопку Заполнить (Fill).
Готово! 
В отличие от рассмотренных выше способов, здесь пустые ячейки заполнены не одним и тем же значением, а разными, которые гораздо больше подходят для ваших данных. Правильное заполнение этой даже такой небольшой таблицы потребовало бы от вас достаточно существенных затрат времени. А надстройка позволяет это сделать буквально в пару кликов.
Помимо заполнения пустых ячеек, этот инструмент также разделил объединенные ячейки. В таком виде таблица вполне пригодна для фильтрации данных, различных подсчетов, формирования сводной таблицы на ее основе.
Проверьте это! Загрузите полнофункциональную пробную версию надстройки Fill Blank Cells и посмотрите, как она может сэкономить вам много времени и сил.
Теперь вы знаете приемы замены пустых ячеек в таблице разными значениями. Я уверен, что вам не составит труда сделать это при помощи любого из рассмотренных способов.
Как сделать диаграмму Ганта — Думаю, каждый пользователь Excel знает, что такое диаграмма и как ее создать. Однако один вид графиков остается достаточно сложным для многих — это диаграмма Ганта. В этом кратком руководстве я постараюсь показать основные функции диаграммы Ганта, покажу… 
Как сделать автозаполнение в Excel — В этой статье рассматривается функция автозаполнения Excel. Вы узнаете, как заполнять ряды чисел, дат и других данных, создавать и использовать настраиваемые списки в Excel. Эта статья также позволяет вам убедиться, что вы знаете все о маркере заполнения,… 
Проверка данных в Excel: как сделать, использовать и убрать — Мы рассмотрим, как выполнять проверку данных в Excel: создавать правила проверки для чисел, дат или текстовых значений, создавать списки проверки данных, копировать проверку данных в другие ячейки, находить недопустимые записи, исправлять и удалять проверку данных.… 
Быстрое удаление пустых столбцов в Excel — В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых столбцов в Excel не может… 
Как полностью или частично зафиксировать ячейку в формуле — При написании формулы Excel знак $ в ссылке на ячейку сбивает с толку многих пользователей. Но объяснение очень простое: это всего лишь способ ее зафиксировать. Знак доллара в данном случае служит только одной цели — он указывает,… 
Чем отличается абсолютная, относительная и смешанная адресация — Важность ссылки на ячейки Excel трудно переоценить. Ссылка включает в себя адрес, из которого вы хотите получить информацию. При этом используются два основных вида адресации – абсолютная и относительная. Они могут применяться в разных комбинациях… 
6 способов быстро транспонировать таблицу — В этой статье показано, как столбец можно превратить в строку в Excel с помощью функции ТРАНСП, специальной вставки, кода VBA или же специального инструмента. Иначе говоря, мы научимся транспонировать таблицу. В этой статье вы найдете… 4 способа быстро убрать перенос строки в ячейках Excel — В этом совете вы найдете 4 совета для удаления символа переноса строки из ячеек Excel. Вы также узнаете, как заменять разрывы строк другими символами. Все решения работают с Excel 2019, 2016, 2013 и более ранними версиями. Перенос…
Отображение и скрытие нулевых значений
Вы можете сами настроить отображение нулевых значений в ячейке или использовать в таблице набор стандартов форматирования, которые требуют скрывать нулевые значения. Отображать и скрывать нулевые значения можно разными способами.
Потребность отображать нулевые значения (0) на листах возникает не всегда. Требуют ли стандарты форматирования или собственные предпочтения отображать или скрывать нулевые значения, имеется несколько способов реализации всех этих требований.
Скрытие и отображение всех нулевых значений на листе
-
Выберите Файл > Параметры > Дополнительно.
-
В группе Показать параметры для следующего листа выберите лист, после чего выполните одно из указанных ниже действий.
-
Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения.
-
Чтобы отображать нулевые значения в виде пустых ячеек, снимите флажок Показывать нули в ячейках, которые содержат нулевые значения.
-
Скрытие нулевых значений в выделенных ячейках
Эти действия скрывают нулевые значения в выбранных ячейках с помощью числовых форматов. Скрытые значения отображаются только в панели формул и не печатаются. Если значение в одной из этих ячеек изменится на неосвое число, то значение отобразится в ячейке, а формат значения будет аналогиен общему числовому формату.
-
Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.
-
Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек.
-
Щелкните Число > Все форматы.
-
В поле Тип введите выражение 0;-0;;@ и нажмите кнопку ОК.
Отображение скрытых значений.
-
Выделите ячейки со скрытыми нулевыми значениями.
-
Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек.
-
Для применения числового формата, определенного по умолчанию, выберите Число > Общий и нажмите кнопку ОК.
Скрытие нулевых значений, возвращенных формулой
-
Выделите ячейку, содержащую нулевое (0) значение.
-
На вкладке Главная щелкните стрелку рядом с кнопкой Условное форматирование и выберите «Правила выделения ячеек» > «Равно».
-
В левом поле введите 0.
-
В правом поле выберите Пользовательский формат.
-
В поле Формат ячейки откройте вкладку Шрифт.
-
В списке Цвет выберите белый цвет и нажмите кнопку ОК.
Отображение нулей в виде пробелов или тире
Для решения этой задачи воспользуйтесь функцией ЕСЛИ.
Если ячейка содержит нулевые значения, для возврата пустой ячейки используйте формулу, например такую:
-
=ЕСЛИ(A2-A3=0;»»;A2-A3)
Вот как читать формулу. Если результат вычисления (A2-A3) равен «0», ничего не отображается, в том числе и «0» (это указывается двойными кавычками «»). В противном случае отображается результат вычисления A2-A3. Если вам нужно не оставлять ячейки пустыми, но отображать не «0», а что-то другое, между двойными кавычками вставьте дефис «-» или другой символ.
Скрытие нулевых значений в отчете сводной таблицы
-
Выберите отчет сводной таблицы.
-
На вкладке Анализ в группе Сводная таблица щелкните стрелку рядом с командой Параметры и выберите пункт Параметры.
-
Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.
-
Изменение отображения ошибки В поле Формат установите флажок Для ошибок отображать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
-
Изменение отображения пустой ячейки Установите флажок Для пустых ячеек отображать. Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.
-
К началу страницы
Потребность отображать нулевые значения (0) на листах возникает не всегда. Требуют ли стандарты форматирования или собственные предпочтения отображать или скрывать нулевые значения, имеется несколько способов реализации всех этих требований.
Отображение и скрытие всех нулевых значений на листе
-
Выберите Файл > Параметры > Дополнительно.
-
В группе Показать параметры для следующего листа выберите лист, после чего выполните одно из указанных ниже действий.
-
Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения.
-
Чтобы отображать нулевые значения в виде пустых ячеек, снимите флажок Показывать нули в ячейках, которые содержат нулевые значения.
-
Скрытие нулевых значений в выделенных ячейках с помощью числового формата
Эти действия позволяют скрыть нулевые значения в выделенных ячейках. Если значение в одной из ячеек станет ненулевым, его формат будет аналогичен общему числовому формату.
-
Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.
-
Вы можете нажать клавиши CTRL+1 или на вкладке Главная щелкнуть Формат > Формат ячеек.
-
В списке Категория выберите элемент Пользовательский.
-
В поле Тип введите 0;-0;;@
Примечания:
-
Скрытые значения отображаются только в строке формул или в ячейке, если вы редактируете ее содержимое. Эти значения не печатаются.
-
Чтобы снова отобразить скрытые значения, выделите ячейки, а затем нажмите клавиши CTRL+1 или на вкладке Главная в группе Ячейки наведите указатель мыши на элемент Формат и выберите Формат ячеек. Чтобы применить числовой формат по умолчанию, в списке Категория выберите Общий. Чтобы снова отобразить дату и время, выберите подходящий формат даты и времени на вкладке Число.
Скрытие нулевых значений, возвращенных формулой, с помощью условного форматирования
-
Выделите ячейку, содержащую нулевое (0) значение.
-
На вкладке Главная в группе Стили щелкните стрелку рядом с элементом Условное форматирование, наведите указатель на элемент Правила выделения ячеек и выберите вариант Равно.
-
В левом поле введите 0.
-
В правом поле выберите Пользовательский формат.
-
В диалоговом окне Формат ячеек откройте вкладку Шрифт.
-
В поле Цвет выберите белый цвет.
Использование формулы для отображения нулей в виде пробелов или тире
Для выполнения этой задачи используйте функцию ЕСЛИ.
Пример
Чтобы этот пример проще было понять, скопируйте его на пустой лист.
|
|
Дополнительные сведения об использовании этой функции см. в статье Функция ЕСЛИ.
Скрытие нулевых значений в отчете сводной таблицы
-
Щелкните отчет сводной таблицы.
-
На вкладке Параметры в группе Параметры сводной таблицы щелкните стрелку рядом с командой Параметры и выберите пункт Параметры.
-
Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.
Изменение способа отображения ошибок. В поле Формат установите флажок Для ошибок отображать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
Изменение способа отображения пустых ячеек. Установите флажок Для пустых ячеек отображать. Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.
К началу страницы
Потребность отображать нулевые значения (0) на листах возникает не всегда. Требуют ли стандарты форматирования или собственные предпочтения отображать или скрывать нулевые значения, имеется несколько способов реализации всех этих требований.
Отображение и скрытие всех нулевых значений на листе
-
Нажмите кнопку Microsoft Office
, Excel параметры, а затем выберите категорию Дополнительные параметры. -
В группе Показать параметры для следующего листа выберите лист, после чего выполните одно из указанных ниже действий.
-
Чтобы отображать в ячейках нулевые значения (0), установите флажок Показывать нули в ячейках, которые содержат нулевые значения.
-
Чтобы отображать нулевые значения в виде пустых ячеек, снимите флажок Показывать нули в ячейках, которые содержат нулевые значения.
-
, Excel параметры, а затем выберите категорию Дополнительные параметры.Скрытие нулевых значений в выделенных ячейках с помощью числового формата
Эти действия позволяют скрыть нулевые значения в выделенных ячейках. Если значение в одной из ячеек станет ненулевым, его формат будет аналогичен общему числовому формату.
-
Выделите ячейки, содержащие нулевые значения (0), которые требуется скрыть.
-
Вы можете нажать клавиши CTRL+1 или на вкладке Главная в группе Ячейки щелкнуть Формат > Формат ячеек.
-
В списке Категория выберите элемент Пользовательский.
-
В поле Тип введите 0;-0;;@
Примечания:
-
Скрытые значения отображаются только в
или в ячейке, если вы редактируете ячейку, и не печатаются. -
Чтобы снова отобразить скрытые значения, выделите ячейки, а затем на вкладке Главная в группе Ячейки наведите указатель мыши на элемент Формат и выберите Формат ячеек. Чтобы применить числовой формат по умолчанию, в списке Категория выберите Общий. Чтобы снова отобразить дату и время, выберите подходящий формат даты и времени на вкладке Число.
или в ячейке, если вы редактируете ячейку, и не печатаются.Скрытие нулевых значений, возвращенных формулой, с помощью условного форматирования
-
Выделите ячейку, содержащую нулевое (0) значение.
-
На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите «Правила выделения ячеек» > «Равно».
-
В левом поле введите 0.
-
В правом поле выберите Пользовательский формат.
-
В диалоговом окне Формат ячеек откройте вкладку Шрифт.
-
В поле Цвет выберите белый цвет.
Использование формулы для отображения нулей в виде пробелов или тире
Для выполнения этой задачи используйте функцию ЕСЛИ.
Пример
Чтобы этот пример проще было понять, скопируйте его на пустой лист.
Копирование примера
-
Выделите пример, приведенный в этой статье.
Важно: Не выделяйте заголовки строк или столбцов.
Выделение примера в справке
-
Нажмите клавиши CTRL+C.
-
В Excel создайте пустую книгу или лист.
-
Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.
Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1.
-
Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе «Зависимости формул» нажмите кнопку Показать формулы.
Скопировав пример на пустой лист, вы можете настроить его так, как вам нужно.
|
|
Дополнительные сведения об использовании этой функции см. в статье Функция ЕСЛИ.
Скрытие нулевых значений в отчете сводной таблицы
-
Щелкните отчет сводной таблицы.
-
На вкладке Параметры в группе Параметры сводной таблицы щелкните стрелку рядом с командой Параметры и выберите пункт Параметры.
-
Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.
Изменение способа отображения ошибок. В поле Формат установите флажок Для ошибок отображать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
Изменение способа отображения пустых ячеек. Установите флажок Для пустых ячеек отображать. Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.
См. также
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул
Поиск ошибок в формулах
Сочетания клавиш и горячие клавиши в Excel
Функции Excel (по алфавиту)
Функции Excel (по категориям)
Нужна дополнительная помощь?
Вы когда-нибудь пробовали преобразовать нулевые ячейки в пустые в Excel? Теперь я расскажу вам несколько быстрых способов пакетного преобразования нулевой ячейки в пустые в Excel.
Преобразование нуля в пустой с помощью функции поиска и замены
Преобразование нуля в пустое с помощью VBA
Преобразовать ноль в пустой с помощью Kutools for Excel
Преобразование нуля в пустой с помощью функции поиска и замены
Функция «Найти и заменить» очень полезна в Excel, вы можете использовать ее, чтобы сначала найти все нулевые ячейки, а затем заменить их пустыми.
1. Нажмите Ctrl + F для отображения Найти и заменить Диалог.
2. в Найти и заменить диалоговое окно, нажмите Замените вкладка и введите 0 в Найти то, что текстовое окно, пространство в Заменить диалоговое окно, затем щелкните Опции развернуть диалог и проверить Соответствие всему содержимому ячейки. Смотрите скриншоты:
3. Затем нажмите Заменить все, и появится диалоговое окно, в котором сообщается, сколько ячеек было заменено. Смотрите скриншот:
4. Нажмите OK. И вы можете видеть, что нулевые ячейки были заменены пустыми.
5. Нажмите Закрыть кнопка для выхода из Найти и заменить Диалог.
Преобразование нуля в пустое с помощью VBA
Если вы знакомы с VBA, вы можете использовать приведенный ниже код VBA для преобразования нуля в пустую ячейку.
1. Нажмите Alt + F11 открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модули , чтобы открыть окно модуля, затем скопируйте в окно следующий код VBA.
VBA: преобразовать нулевые ячейки в пустые.
Sub ChangeZero()
'Updateby20140708
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
If Rng.Value = 0 Then
Rng.Value = ""
End If
Next
End Sub
3. Нажмите Run или F5 клавишу на клавиатуре, чтобы запустить этот код, и KutoolsforExcel Появится диалоговое окно для выбора диапазона для замены нулевых ячеек. Смотрите скриншот:
4. Нажмите OK, и все нулевые ячейки в выделенном фрагменте преобразуются в пустые ячейки.
Преобразовать ноль в пустой с помощью Kutools for Excel
Наблюдения и советы этой статьи мы подготовили на основании опыта команды Выбрать определенные ячейки полезности Kutools for Excel может помочь вам выбрать все ячейки с нулем в выбранном диапазоне. Выделив все ячейки с нулем, вы можете удалить их, нажав клавишу Delete. Пожалуйста, сделайте следующее.
1. Выберите диапазон с нулевыми ячейками, который вы хотите преобразовать в пустые. А затем нажмите Кутулс > Выберите > Выбрать определенные ячейки. Смотрите скриншот:
2. в Выбрать определенные ячейки диалоговое окно, выберите Ячейка в Тип выбора раздел, а затем выберите Равно из раскрывающегося списка в Конкретный тип раздел, введите 0 в следующее поле. Наконец нажмите кнопку OK кнопка. Смотрите скриншот:
3. После этого сразу выбираются все ячейки с нулем, нажмите Удалить клавишу на клавиатуре, чтобы очистить все выделенные ячейки. Вы можете видеть, что все нули преобразованы в пустые, как показано на скриншоте ниже.
Если вы хотите получить бесплатную пробную версию (30-день) этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Преобразовать ноль в пустой с помощью Kutools for Excel
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
На чтение 4 мин Опубликовано 07.03.2020
Из этой статьи Вы узнаете способ, как выделить разом все пустые ячейки на листе Excel и заполнить их значениями из ячеек выше (ниже), нулями или любыми другими значениями.
Заполнять или не заполнять? – этот вопрос часто возникает в отношении пустых ячеек в таблицах Excel. С одной стороны, таблица выглядит аккуратнее и более читабельной, когда Вы не загромождаете её повторяющимися значениями. С другой стороны, пустые ячейки в Excel могут привести к проблемам во время сортировки, фильтрации данных или при создании сводной таблицы. В таком случае Вам придётся заполнить все пустые ячейки. Существуют разные способы для решения этой проблемы. Я покажу Вам несколько быстрых способов заполнить пустые ячейки различными значениями в Excel 2010 и 2013.
Итак, моим ответом будет – заполнять! Давайте посмотрим, как мы сможем это сделать.
- Как выделить пустые ячейки на листе Excel
- Формула Excel для заполнения пустых ячеек значениями из ячеек выше (ниже)
- Заполняем пустые ячейки нулями или другим заданным значением
Содержание
- Как выделить пустые ячейки на листе Excel
- Формула для заполнения пустых ячеек значениями из ячеек выше (ниже)
- Заполняем пустые ячейки нулями или другим заданным значением
- Способ 1
- Способ 2
Как выделить пустые ячейки на листе Excel
Прежде чем заполнять пустые ячейки в Excel, их нужно выделить. Если у Вас большая таблица с дюжинами пустых ячеек, разбросанных по всей таблице, то Вы потратите вечность, если будете делать это вручную. Вот быстрый способ, как выделить пустые ячейки:
- Выделите столбцы или строки, в которых требуется заполнить пустоты.
- Нажмите Ctrl+G или F5, чтобы отобразить диалоговое окно Go To (Переход).
- Нажмите кнопку Special (Выделить).
Замечание: Если Вы вдруг забыли сочетание клавиш, откройте вкладку Home (Главная) и в разделе Editing (Редактирование) из выпадающего меню Find & Select (Найти и выделить) выберите команду Go To Special (Выделить группу ячеек). На экране появится то же диалоговое окно.
- Команда Go To Special (Выделить группу ячеек) позволяет выбрать ячейки определённого типа, например, ячейки, содержащие формулы, примечания, константы, пустые ячейки и так далее.
- Выберите параметр Blanks (Пустые ячейки) и нажмите ОК.Теперь в выбранном диапазоне выделены только пустые ячейки и всё готово к следующему шагу.
Команда Go To Special (Выделить группу ячеек) позволяет выбрать ячейки определённого типа, например, ячейки, содержащие формулы, примечания, константы, пустые ячейки и так далее.
Теперь в выбранном диапазоне выделены только пустые ячейки и всё готово к следующему шагу.
Формула для заполнения пустых ячеек значениями из ячеек выше (ниже)
После того как Вы выделили пустые ячейки в таблице, их можно заполнить значениями из ячеек сверху, снизу или вставить определённое значение.
Если Вы собираетесь заполнить пропуски значениями из ближайшей не пустой ячейки сверху или снизу, то потребуется ввести в одну из пустых ячеек очень простую формулу. Затем просто скопируйте её во все пустые ячейки. Как это сделать – читайте далее.
- Выделите все пустые ячейки.
- Нажмите F2 или просто поместите курсор в строку формул, чтобы приступить к вводу формулы в активную ячейку. Как видно на снимке экрана выше, активна ячейка C4.
- Введите знак равенства (=).
- Укажите ячейку, находящуюся выше или ниже, нажав стрелку вверх или вниз, или просто кликнув по ней.Формула (=C3) показывает, что в ячейке C4 появится значение из ячейки C3.
- Нажмите Ctrl+Enter, чтобы скопировать формулу во все выделенные ячейки.
Формула (=C3) показывает, что в ячейке C4 появится значение из ячейки C3.
Отлично! Теперь в каждой выделенной ячейке содержится ссылка на ячейку, расположенную над ней.
Замечание: Не забывайте, что все ячейки, которые были пустыми, теперь содержат формулы. Если Вы хотите сохранить порядок в таблице, то лучше заменить эти формулы значениями. В противном случае, можно получить путаницу при выполнении сортировки или при вводе новых данных.
Заполняем пустые ячейки нулями или другим заданным значением
Что если Вам нужно заполнить все пустые ячейки в Вашей таблице нулями или другими числовыми или текстовыми значениями? Далее показаны два способа решения этой задачи.
Способ 1
- Выделите все пустые ячейки
- Нажмите F2, чтобы ввести значение в активную ячейку.
- Введите нужное число или текст.
- Нажмите Ctrl+Enter.
За несколько секунд Вы заполнили все пустые ячейки нужным значением.
Способ 2
- Выделите диапазон, содержащий пустые ячейки.
- Нажмите Ctrl+H, чтобы появилось диалоговое окно Find & Replace (Найти и заменить).
- Перейдите на вкладку Replace (Заменить).
- Оставьте поле Find what (Найти) пустым и введите нужное значение в поле Replace with (Заменить на).
- Нажмите кнопку Replace All (Заменить все).
Какой бы способ Вы ни выбрали, обработка всей таблицы Excel займёт не больше минуты!
Теперь Вы знаете приёмы, как заполнить пустые ячейки различными значениями в Excel 2013. Уверен, для Вас не составит труда сделать это как при помощи простой формулы, так и при помощи инструмента Find & Replace (Найти и заменить).
Оцените качество статьи. Нам важно ваше мнение:
Часто в больших таблицах для придания им более читабельного вида данные, повторяющиеся подряд для нескольких ячеек, не заполняют. Или в столбцах с числовыми значениями присутствуют пустые ячейки вместо нулей.
Для анализа такие данные едва пригодны. Если нужно создать на основе таких таблиц сводную, произвести сортировку или фильтрацию данных, потребуется вначале заполнить все пустые ячейки. Рассмотрим ситуации подробнее на примерах.
Как заполнить пустые ячейки в Excel значениями непустых ячеек, стоящих сверху над ними?
Стандартными средствами Excel это делается в несколько шагов:
- Создается фильтр по столбцу,
- В первой пустой ячейке прописывается формула, ссылающаяся на предыдущую ячейку, например, в ячейке A3 будет формула “=A2”,
- Копируем ячейку с формулой,
- Отфильтровываем только пустые ячейки столбца,
- Выделяем их и вставляем в них формулу сочетанием Ctrl + V,
- Снимаем фильтр,
- Выделяем весь столбец и удаляем формулы, оставляя значения.
У данного решения есть очевидные минусы:
- это рутинная многоступенчатая операция, отнимающая время;
- если ячейки столбца имеют разное форматирование, при копировании ячейки с формулой может потребоваться его восстанавливать, т.к. вместе с формулой ячейки получат в наследство и форматирование исходной;
- при этом штатными методами Excel вставить в отфильтрованные ячейки только формулы невозможно.
Заполнить пустые ячейки в 2 клика
Настройка !SEMTools позволяет решить задачу в пару мгновений, не меняя форматирование пустых ячеек. Процедура находится в меню «Изменить ячейки» в группе «ИЗМЕНИТЬ».
Заполнить пустые ячейки значением ниже
Процедура заполнения ячеек значениями нижестоящих производится аналогично описанной выше. На практике такая организация данных встречается реже, тем не менее, смотрите пример:
Заполнить пустые ячейки нулями
Заполнение пустых ячеек нулями — несложная операция. Можно произвести ее с помощью поиска и замены, выделив диапазон и вызвав диалог сочетанием клавиш Ctrl + H. Конфигурация замены должна быть такой, как на картинке — ищем ячейки целиком, в которых ничего нет, и заменяем пустоты на нули.
С помощью надстройки !SEMTools, тем не менее, это можно сделать еще быстрее:
Теперь нет необходимости прописывать сложные формулы в Excel.
!SEMTools поможет автоматизировать процессы и решит ваши задачи за пару кликов!
17 авг. 2022 г.
читать 1 мин
Вы можете использовать следующий базовый синтаксис, чтобы вернуть пустую ячейку вместо нуля при использовании формул Excel:
=IF( B2 / C2 =0, "", B2 / C2 )
Эта конкретная формула пытается разделить значение в ячейке B2 на значение в ячейке C2 .
Если результат равен нулю, Excel возвращает пустое значение. В противном случае Excel возвращает результат деления B2 на C2 .
В следующем примере показано, как использовать эту формулу на практике.
Пример: вернуть пустую ячейку вместо нуля в формуле Excel
Предположим, у нас есть следующий набор данных, который показывает общий объем продаж и возвратов для различных продуктов:
Предположим, мы вводим следующую формулу в ячейку D2 , чтобы рассчитать коэффициент возврата для продукта A:
= B2 / C2
Если мы скопируем и вставим эту формулу в каждую ячейку столбца D, мы увидим, что некоторые результаты равны нулю:
Чтобы вернуть пустое значение вместо нуля, мы можем ввести следующую формулу в ячейку D2 :
=IF( B2 / C2 =0, "", B2 / C2 )
Затем мы можем скопировать и вставить эту формулу в каждую оставшуюся ячейку в столбце D:
Обратите внимание, что каждая ячейка, в которой формула возвращала бы ноль, теперь вместо этого возвращает пустое значение.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Как игнорировать значения #N/A при использовании формул в Excel
Как заменить значения #N/A в Excel
Как исправить ошибку #ИМЯ в Excel
Написано
Замечательно! Вы успешно подписались.
Добро пожаловать обратно! Вы успешно вошли
Вы успешно подписались на кодкамп.
Срок действия вашей ссылки истек.
Ура! Проверьте свою электронную почту на наличие волшебной ссылки для входа.
Успех! Ваша платежная информация обновлена.
Ваша платежная информация не была обновлена.