Имейте формулы Excel, которые возвращают 0, делают результат пустым

У повторяющейся проблемы Excel у меня есть такие формулы, как INDEX(array,row,column), которые возвращают 0, когда нет результата, вместо того, чтобы возвращать пустое.

Каков наилучший способ изменить нулевой результат на пустой?

Вот подходы, которые я пробовал до сих пор:

1) Используя деление на ноль. Если INDEX возвращает 0, я вызываю ошибку, которую затем отфильтровываю.

=IFERROR(1/1/INDEX(A,B,C),"")

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

2) Использование пользовательского форматирования

0;-0;;@

CONS:
1) не может одновременно применять формат даты
2) Он не работает с условным форматированием, когда дело доходит до проверки пустых ячеек (все равно значение нуля, оно просто не показано)

3) Использование операторов IF

=IF((1/1/INDEX(A,B,C))<>"",(1/1/INDEX(A,B,C)),"")

CONS: Мессиальное повторение

Есть ли у кого-нибудь другие/лучшие идеи?

Ответы

Ответ 1

Вы можете создавать свои собственные пользовательские функции в модуле в Excel, например (из памяти, поэтому может потребоваться некоторая отладка, а синтаксис может также варьироваться в версиях Excel):

Public Function ZeroToBlank (x As Integer) As String
    If x = 0 then
        ZeroToBlank = ""
    Else
        ZeroToBlank = CStr(x)
    End If
End Function

Затем вы можете просто вставить =ZeroToBlank (Index (a,b,c)) в свою ячейку.

Вот хороший учебник по этой теме здесь.

Основные шаги:

  • Откройте редактор VB в Excel с помощью Tools -> Macro -> Visual Basic Editor.
  • Создайте новый модуль с Insert -> Module.
  • Введите вышеуказанную функцию в этот модуль.
  • В ячейках, где вы хотите вызвать эту функцию, введите формулу & tbsp;   =ZeroToBlank (<<whatever>>)
    где <<whatever>> - это значение, которое вы хотите использовать пустым для if он равен нулю.
  • Обратите внимание, что эта функция возвращает строку, поэтому, если вы хотите, чтобы она выглядела как число, вы можете захотеть правильно обосновать ячейки.

Обратите внимание, что могут быть незначительные изменения в зависимости от того, какая версия Excel у вас есть. Моя версия Excel 2002, которая, по общему признанию, довольно старая, но она все еще делает все, что мне нужно.

Ответ 2

Нормальным способом будет оператор IF, хотя и более простой, чем ваш пример:

=IF(INDEX(a,b,c),INDEX(a,b,c),"")

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

Ответ 3

Я не уверен, что он работает со всеми типами данных, но единственным решением, которое я нашел на данный момент, является проверка, если индекс возвращает пустой:

=IF(ISBLANK(INDEX(a,b,c)),"",INDEX(a,b,c))

Формула

=IF(INDEX(a,b,c),INDEX(a,b,c),"")

не работает с текстом

Ответ 4

Возможно, самый простой способ - добавить условие форматирования текста к формуле с помощью модификатора ?. Таким образом:

(formula to grab values) становится:

text((formula to grab values),"?")

Надеюсь, что это поможет.

Ответ 5

Если вы хотите, чтобы все нули на листе исчезли, перейдите в раздел "Параметры Excel", "Дополнительно", "Показать параметры для этой рабочей таблицы" и снимите "Показывать нуль в ячейках с нулевым значением значение". (Это навигационная система для Excel 2007, YMMV.)

Что касается вашего ответа (2), вы можете сохранить пару нажатий клавиш, набрав 0;-0; - насколько я могу судить, что эквивалентно 0;-0;;@. И наоборот, если вы хотите быть немного более общим, вы можете использовать формат General;-General;. Нет, это автоматически не обрабатывает даты, но, как указывает Барри, если вы ожидаете значения даты, вы можете использовать формат, например d-mmm-yyyy;;.

Ответ 6

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

Любая последующая формула, скорее всего, провалится через пробел, поэтому вы получите ошибку, которую вы бы захватили с помощью IFERROR() или предотвратить с помощью IF(sourcecell<>"";...), если вы будете использовать последнее, тогда тестирование на нуль будет равным количеству работы и беспорядка. Проверка для пустых ячеек становится проверкой на 0 ценные ячейки. (если эта работа работает для вас, объясните более конкретную проблему).

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

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

Ответ 7

Существует очень простой ответ на эту грязную проблему - функцию SUBSTITUTE. В приведенном выше примере:

=IF((1/1/INDEX(A,B,C))<>"",(1/1/INDEX(A,B,C)),"")

Можно переписать следующим образом:

=SUBSTITUTE((1/1/INDEX(A,B,C), " ", "")

Ответ 8

Я понял это, объединив строку EMPTY.

INDEX(tt_Attributes,MATCH([RowID],tt_Attributes[RowID],0),COLUMN(tt_Attributes[Long Description]) ) & ""

Ответ 9

=if(b2 = "", "", b2)

Это сработало для меня

Ответ 10

Используйте условное форматирование (вкладка "Главная", раздел стилей) и применяйте правило ячеек ярлыков (помещая 0 в ячейки "Формат", которые равны квадрату), но выберите пользовательский формат, а затем вкладку "Число". Выберите "Пользовательский стиль" и в поле "Тип":

0; -0;; @

Звучит сложно, но на самом деле просто.

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

Ответ 11

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

Пример: = IF (INDEX (a, b, c) = "0", "", INDEX (a, b, c))

Ответ 12

=IF(INDEX(a,b,c)="0","", INDEX(a,b,c)) работал у меня с незначительной модификацией. Исключение 0 и отсутствие пробелов между цитатами: =IF(INDEX(a,b,c)="","", INDEX(a,b,c))