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

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

  = IF (some_condition, EMPTY (), some_value)  

Я пытался делать такие вещи как

  = IF (some_condition, "", some_value)  

и

  = IF (some_condition ,, some_value)  

и предполагая, что B1 — пустая ячейка

  = IF (some_condition,  B1, some_value)  

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

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

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


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

Примерно так:

  Для каждой ячейки в SomeRange If (cell.value = SomeTest) Then cell.ClearContentsNext  

У Excel нет возможности сделать это.

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

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

6


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

Возможно, что аннигилирующая формула будет иметь значение trueblank, если условие выполнено. Он проходит проверку формулы ISBLANK .

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

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

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

  Функция Delete_UDF (rng) ThisWorkbook.Application.Volatile rng.Value = "" Конечная функция  

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

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

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

  = IF (A2 = 0, GetTrueBlank, A2)  

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

Вы можете загрузить демонстрационный файл здесь .

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

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

Я встречал примеры получения истинного пустого значения в качестве результата формулы, представленные командой FrankensTeam здесь: https://sites.google.com/site/e90e50/excel-formula-to-change -значение-другой-ячейки

5


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

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

Затем в ячейках, которые могут привести к нежелательному результату, поместите формулу в оператор IF с NA () такие результаты, как = IF ($ A8> TODAY (), NA (), * формула для построения графика *)

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

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

2


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

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

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

2


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

  • там, где вы хотите пустую ячейку, верните 0 вместо "" и THEN

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

      0; 0; ""; "text" @  

2


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

2


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

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

1


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

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

1


Так много ответов, которые возвращают значение, которое ВЫГЛЯДИТ пусто, но на самом деле не является пустой ячейкой as, как было запрошено …

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

Откройте «визуальную базовую программу просмотра» (Alt + F11). Найдите интересующую книгу в проводнике проекта. и дважды щелкните его (или щелкните правой кнопкой мыши и выберите код просмотра). Это откроет окно «просмотр кода». Выберите «Рабочая книга» в раскрывающемся меню (Общие) и «SheetCalculate» в раскрывающемся меню (Объявления).

Вставьте следующий код (на основе ответа JT Grimes) в функцию Workbook_SheetCalculate

  Для каждой ячейки в Sh.UsedRange.Cells Если IsError (cell.Value) Then If (cell.Value = CVErr (xlErrNA)) Then cell.ClearContents End If Next  

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

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

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

1


Этот ответ не полностью касается 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 rEnd Sub  

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


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

  = IF (condition, T (1), value)  

2


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

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

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

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

1


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

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

  = HLOOKUP («Значение поиска», массив, СТРОКА,  FALSE)  

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

  = (HLOOKUP ("Значение поиска",  Array, ROW, FALSE))  

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

Edit

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


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

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

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

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

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

1


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

1


Я вырезал одинарные кавычки, поэтому столбец с телефонным номером, например + 1-800-123-4567, не приводил к вычислению и давал отрицательное число. Я попытался удалить их в пустых ячейках, запретите цитату, затем нажмите и эту проблему (столбец F). Намного проще просто вызвать текст в исходной ячейке и вуаля !:

  = IF (F2 = "'", "", TEXT (F2, ""))  


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

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

  = ЕСЛИОШИБКА  (MID (Q2, FIND ("{", Q2), FIND ("}", Q2) - FIND ("{", Q2) + 1), "")  



Использование IF для проверки того, что ячейка пуста

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

В этом случае мы используем IF с функцией ISBLANK:

  • = IF (ISBLANK (D2), «Blank», «Not Bl ank «)

Что говорит: IF (D2 пусто, затем возвращается» Пусто «, в противном случае -» Не пусто «) . Вы также можете легко использовать свою собственную формулу для условия «Not Blank». В следующем примере мы используем «» вместо ISBLANK. «» По существу означает «ничего».

= IF (D3 = «», » Пусто »,« Не пустое »)

В этой формуле указано, что ЕСЛИ (D3 — ничего, тогда вернуть« Пусто », иначе« Не пусто ») . Вот пример очень распространенного метода использования «» для предотвращения вычисления формулы, если зависимая ячейка пуста:

  • = IF (D3 = «», «», YourFormula ())

    ЕСЛИ (D3 ничего не возвращает, тогда ничего не возвращает, иначе вычислите формулу).

Оцените статью
clickpad.ru
Добавить комментарий