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

Excel не имеет никакого способа сделать это.

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

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

Да, это возможно.

можно иметь аннигилирующую формулу, оценивающую trueblank, если условие выполнено. Он проходит испытание ISBLANK формулы. Этот, казалось бы, невозможный трюк я нашел в коллекции FrankensTeam (ссылка ниже).

после настройки 3 ступени, вы сможете использовать именованный диапазон GetTrueBlank, так же, как вы хотите в своем ответе:

=IF(A1 = "Hello world", GetTrueBlank, A1)

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

Function Delete_UDF(rng)
    ThisWorkbook.Application.Volatile
    rng.Value = ""
End Function

два замечания о коде VBA.

1. не вводите в заблуждение rng.Value="" по имени Ярость GetTrueBlank наконец-то будет trueblank, а не пустая строка, как в двойной кавычке ="".
2. в моих тестах, первая строка кода ThisWorkbook.Application.Volatile первоначально используемый FrankensTeam оказался ненужным.

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

=EVALUATE("Delete_UDF("&CELL("address",Sheet1!A1)&")")

Шаг 3. используйте формулу самоуничтожения. Сажают в камеру, говорят B2 следующая формула:

=IF(A2=0,GetTrueBlank,A2)

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

вы можете скачать демонстрационный файл.

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

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

I пришли по примерам получения trueblank в результате формулы reaveled FrankensTeam здесь: https://sites.google.com/site/e90e50/excel-formula-to-change-the-value-of-another-cell

может, это обман, но это работает!

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

затем в ячейках, которые могут привести к результату, который вы не хотите отображать, поместите формулу В оператор IF с помощью NA() результаты, такие как =IF($A8>TODAY(),NA(), *formula to be plotted*)

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

чтобы очистить это, Выберите ячейку, которая может содержать недопустимое значение, а затем выберите Условное форматирование - новое правило. Выберите "Форматировать только ячейки, которые содержат" и под описанием правила выберите "ошибки" из выпадающего списка. Затем в разделе формат выберите шрифт-цвет-белый (или любой другой цвет фона). Нажмите на различные кнопки, чтобы выйти, и вы должны увидеть, что ячейки с недопустимыми данными выглядят пустыми (они на самом деле содержат #N/A но белый текст на белом фоне выглядит пустым.), Связанное график также не отображает неверные значения.

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

1. Я сделал " копию "всех данных и вставил данные как"значения".
2. затем я выделил вставленные данные и сделал "заменить" (Ctrl -H) пустые ячейки с какой-то буквой, Я выбрал q Так как его нигде не было на моих данных лист.
3. наконец, я сделал еще одну "замену", и заменил q ни с чем.

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

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

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

в отличие от ISBLANK(),COUNTBLANK() считает "" как пустой и возвращает 1.

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

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

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

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

по запросу, если вы действительно хотите формулу, которая возвращает пустую ячейку. Это возможно через VBA. Итак, вот код, чтобы сделать именно это. Начните с написания формулы, чтобы вернуть ошибку #N / A везде, где вы хотите, чтобы ячейки были пустыми. Затем мое решение автоматически очищает все ячейки, которые содержат эту ошибку #N/A. Конечно, вы можете изменить код, чтобы автоматическое удаление содержимого ячеек на основе всего, что вам нравится.

откройте "Visual basic viewer" (Alt + F11) Найдите интересующую книгу в обозревателе проектов и дважды щелкните ее (или щелкните правой кнопкой мыши и выберите Просмотр кода). Откроется окно "Просмотр кода". Выберите " рабочая книга "в раскрывающемся меню (Общие) и" SheetCalculate " в раскрывающемся меню (объявления).

вставить следующий код (в зависимости от ответа Т. Дж. Граймс) внутри Workbook_SheetCalculate функция

    For Each cell In Sh.UsedRange.Cells
        If IsError(cell.Value) Then
            If (cell.Value = CVErr(xlErrNA)) Then cell.ClearContents
        End If
    Next

сохраните файл в виде книги с поддержкой макросов

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

NB2: очевидно, вам нужно включить макросы при открытии файла иначе он не будет работать и # n / a ошибки останутся неотделенными

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

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

Sub clearBlanks()
    Dim r As Range
    For Each r In Selection.Cells
        If Len(r.Text) = 0 Then
            r.Clear
        End If
    Next r
End Sub

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

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

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

=IF((IF(A5="",0,A5)+IF(B5="",0,B5)) = 0, "",(IF(A5="",0,A5)+IF(B5="",0,B5)))

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

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

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

    =HLOOKUP("Lookup Value",Array,ROW,FALSE)

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

    =(HLOOKUP("Lookup Value",Array,ROW,FALSE))

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

редактировать

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

Ну пока это лучшее, что я мог придумать.

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

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

=IF(ISBLANK(A1)," ",STDEV(B5:B14))

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

ответ положительный - вы не можете использовать функцию =IF() и оставить ячейку пустой. "Вроде никого" не является пустым. Жаль, что две кавычки спина к спине не дают пустую ячейку, не стирая формулу.

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

я использовал эту формулу:

=IFERROR(MID(Q2, FIND("{",Q2), FIND("}",Q2) - FIND("{",Q2) + 1), "")

похоже, что простой ответ, который работает, был пропущен.

назовите пустую ячейку в вашей электронной таблице BlankCell.

возвращение BlankCell в вашей формуле, например,

=ISBLANK(IFNA(VLOOKUP(A2,SomeTable,2,False),BlankCell))

возвращает TRUE Если ISBLANK(VLOOKUP(A2,SomeTable,2,False)) и TRUE.