Excel: как вернуть пустую ячейку вместо нуля в формулах
Вы можете использовать следующий базовый синтаксис, чтобы вернуть пустую ячейку вместо нуля при использовании формул 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:
Пустые значения и значения NULL в надстройках Excel
Значения null и пустые строки имеют специальные применения в API JavaScript для Excel. Они используются для представления пустых ячеек, отсутствия форматирования или значений по умолчанию. В этом разделе описано использование значения null и пустой строки при получении и настройке свойств.
Входное значение null в двумерном массиве
В Excel диапазон представлен двумерным массивом, в котором первое измерение — это строки, а второе — столбцы. Чтобы задать значения, формат чисел или формулу только для определенных ячеек в диапазоне, укажите значения, формат чисел или формулу для этих ячеек в двумерном массиве, а для всех остальных ячеек в этом массиве укажите значение null .
Например, чтобы изменить формат чисел только для одной ячейки в диапазоне и сохранить существующий формат чисел для всех остальных ячеек в диапазоне, укажите новый формат чисел для ячейки, которую необходимо изменить, а для всех остальных ячеек укажите значение null . Во фрагменте кода ниже показано, как задать новый формат чисел для четвертой ячейки в диапазоне, при этом формат чисел для первых трех ячеек в диапазоне останется неизменным.
range.values = [['Eurasia', '29.96', '0.25', '15-Feb' ]]; range.numberFormat = [[null, null, null, 'm/d/yyyy;@']]; Входное значение null для свойства
null не является допустимым входным значением для одного свойства. Например, следующий фрагмент кода недопустим, так как свойству values диапазона нельзя присвоить значение null .
range.values = null; // This is not a valid snippet. Аналогично, указанный ниже фрагмент кода не является допустимым, так как null — недопустимое значение для свойства color .
range.format.fill.color = null; // This is not a valid snippet. Значения свойств null в ответе
Если в указанном диапазоне имеются другие значения, свойства форматирования, например size и color будут содержать значения null в ответе. Например, если вы получаете диапазон и загружаете его свойство format.font.color :
- Если у всех ячеек в диапазоне один и тот же цвет шрифта, свойство range.format.font.color указывает этот цвет.
- Если в диапазоне используется несколько цветов шрифтов, свойство range.format.font.color имеет значение null .
Пустое входное значение для свойства
Когда вы указываете пустое значение для свойства (то есть две кавычки подряд без других знаков между » ), это будет интерпретировано как инструкция по очистке или сбросу свойства. Например:
- Если вы укажете пустое значение для свойства values диапазона, содержимое диапазона будет очищено.
- Если вы укажете пустое значение для свойства numberFormat , формат чисел будет «сброшен» до формата General .
- Если вы укажете пустое значение для свойств formula и formulaLocale , значения формул будут очищены.
Значения пустых свойств в ответе
Для операций чтения пустое значение свойства в ответе (то есть две кавычки подряд без других знаков между » ) указывает, что ячейка не содержит данных или значения. В первом примере ниже первая и последняя ячейки в диапазоне не содержат данных. Во втором примере две первые ячейки в диапазоне не содержат формул.
range.values = [['', 'some', 'data', 'in', 'other', 'cells', '']]; range.formula = [['', '', '=Rand()']]; Совместная работа с нами на GitHub
Источник этого содержимого можно найти на GitHub, где также можно создавать и просматривать проблемы и запросы на вытягивание. Дополнительные сведения см. в нашем руководстве для участников.
Заполнение пустых ячеек значениями из соседних ячеек
Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек — по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками — например после копирования результатов сводных таблиц или выгрузок в Excel из внешних программ. Таким образом, возникает необходимость заполнить пустые ячейки таблицы значениями из верхних ячеек, то бишь.
| из |  |
сделать |  |
В общем случае, может возникнуть необходимость делать такое заполнение не только вниз, но и вверх, вправо и т.д. Давайте рассмотрим несколько способов реализовать такое.
Способ 1. Без макросов
Выделяем диапазон ячеек в первом столбце, который надо заполнить (в нашем примере, это A1:A12). Нажимаем клавишу F5 и затем кнопку Выделить (Special) и в появившемся окне выбираем Выделить пустые ячейки (Blanks) : 
Не снимая выделения, вводим в первую ячейку знак «равно» и щелкаем по предыдущей ячейке или жмём стрелку вверх (т.е. создаем ссылку на предыдущую ячейку, другими словами): 
И, наконец, чтобы ввести эту формулу сразу во все выделенные (пустые) ячейки нажимаем Ctrl + Enter вместо обычного Enter . И все! Просто и красиво. В качестве завершающего мазка я советовал бы заменить все созданные формулы на значения, ибо при сортировке или добавлении/удалении строк корректность формул может быть нарушена. Выделите все ячейки в первом столбце, скопируйте и тут же вставьте обратно с помощью Специальной вставки (Paste Special) в контекстом меню, выбрав параметр Значения (Values) . Так будет совсем хорошо.
Способ 2. Заполнение пустых ячеек макросом
Если подобную операцию вам приходится делать часто, то имеем смысл сделать для неё отдельный макрос, чтобы не повторять всю вышеперечисленную цепочку действий вручную. Для этого жмём 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.
Способ 3. Power Query
- Если данных много, то ручной способ с формулами или макросы могут заметно тормозить. Power Query сделает всё гораздо шустрее.
- При изменении исходных данных достаточно будет просто обновить запрос Power Query. В случае использования первых двух способов — всё делать заново.
Для загрузки нашего диапазона с данными в Power Query ему нужно либо дать имя (через вкладку Формулы — Диспетчер имен), либо превратить в «умную» таблицу командой Главная — Форматировать как таблицу (Home — Format as Table ) или сочетанием клавиш Ctrl + T :
После этого на вкладке Данные (Data) нажмем на кнопку Из таблицы / диапазона (From Table/Range) . Если у вас Excel 2010-2013 и Power Query установлена как отдельная надстройка, то вкладка будет называться, соответственно, Power Query.
В открывшемся редакторе запросов выделим столбец (или несколько столбцов, удерживая Ctrl ) и на вкладке Преобразование выберем команду Заполнить — Заполнить вниз (Transform — Fill — Fill Down) :
Вот и всё 🙂 Осталось готовую таблицу выгрузить обратно на лист Excel командой Главная — Закрыть и загрузить — Закрыть и загрузить в. (Home — Close&Load — Close&Load to. )
В дальнейшем, при изменении исходной таблицы, можно просто обновлять запрос правой кнопкой мыши или на вкладке Данные — Обновить всё (Data — Refresh All) .
Ссылки по теме
- Объединение двух столбцов с данными
- Быстрое выделение всех ячеек с формулами или константами
- Быстрое заполнение пустых ячеек макросом из надстройки PLEX
Как вставить пустое значение в excel
Подскажите, как вычислить посредством формулы, чтобы значение ячейки равнялось «пусто» (это те значения, которые имеют ячейки при открытии новой книги, а не ноль и не пустой текст «»)?
14.01.2009 15:20:00
кроме 0 и «» Эпустых» значений в экселе не бывает
девственные ячейки имеют значение «» 🙂
14.01.2009 15:30:48
девственные ячейки имеют значение «» 🙂
Уверены?
Проверьте функцией ЕПУСТО() девственную ячейку и ячейку с формулой =»»
14.01.2009 19:00:39
Уверены?
Проверьте функцией ЕПУСТО() девственную ячейку и ячейку с формулой =»»
уверен, во- первых, это уже не девственная ячейка, а вот вторых равенство =A1=»» выполняется
15.01.2009 13:09:32
Уверены?
Проверьте функцией ЕПУСТО() девственную ячейку и ячейку с формулой =»»
уверен, во- первых, это уже не девственная ячейка, а вот вторых равенство =A1=»» выполняется
Вы правы, что данное равенство выполняется. Но различие все же имеется при применении функции ЕПУСТО() — значения получаются разные. Попробуйте и увидите. Поэтому и пытаюсь разобраться.
15.01.2009 13:31:55
я попробывал и увидел)))
использыйте в формуле ковычки(типа если(A1 = «»;1;0) для проверки, а не епусто, тогда результат будет одинаков
Пользователь
Сообщений: 3280 Регистрация: 24.12.2012
15.01.2009 13:41:57
Можно вмешаться в Ваш высокоинтеллектуальный спор. ЕПУСТО() отличает 0, » » и «» последнее она и считает ИСТИНно пустым, но если очистить содержимое двух первых ЕПУСТО() станет истинной.. вобщем о девственности разговора не может быть.
15.01.2009 15:21:23
Посмотрите скриншот в прицепленном файле. ЕПУСТО() не считает =»» пустым. Ячейка А1 не никак не изменялась.
Прикрепленные файлы
- post_44822.jpg (12.7 КБ)
15.01.2009 15:45:00
Да конечно, эта функция не ошибается, ячейка действительно не пустая, а содержит формулу, возвращающую пустое значение. Я так понимаю, вам нужно проверять значение ячейки на результат (в данном случае это пустая строка), а не то является она пустой или нет. Я предложил решение, скажите чем оно вас не устраивает?
15.01.2009 17:35:31
Меня — устраивает. Просто разбираюсь и вникаю..))