Грэм использует формулу, которая ссылается на две ячейки, B1 и C1. Все, что делает формула, это возвращает значение одной из ячеек, как в = IF (A1 = 1, IF (B1> C1, B1, C1)). И B1, и C1 обычно содержат даты, но иногда одна или обе даты могут быть пустыми. Если они оба пусты, возвращаемое значение (которое равно 0, потому что они пусты) отображается как 1/0/1900. Когда обе ячейки пусты, Грэм хотел бы, чтобы формула возвращала пустое значение, а не 0.

Есть несколько способов удовлетворить эту потребность. Однако сначала давайте начнем с примера формулы Грэма:

=IF(A1=1, IF(B1>C1, B1, C1))

Формула является немного «неполной», так как в ней не указано, что должно быть возвращено, если A1 не содержит значения 1. Как написано, если A1 содержит 2 (или любое другое значение, кроме 1), то формула возвращает «FALSE. «. Итак, давайте немного изменим формулу, чтобы, если A1 не равно 1, она возвращала «пустое» значение, например:

=IF(A1=1, IF(B1>C1, B1, C1), "")

Далее важно понять, что происходит в «B1> C1»

сравнение. Даты, конечно, хранятся внутри как числовые значения — как порядковые номера. Если B1 содержит дату более позднюю, чем C1, то серийный номер в B1 будет больше, чем серийный номер в C1.

Помимо этого, характеристики данных в B1 и C1 определяют, как работает сравнение (>).

  1. Если обе ячейки содержат числовые значения (включая даты), то сравнение работает должным образом.

  2. Если одна из ячеек содержит текстовое значение, то обе ячейки обрабатываются так, как если бы они содержали текстовые значения, даже если одна из них содержит числовое значение.

  3. Если одна из ячеек пуста, то эта ячейка рассматривается как содержащая значение 0.

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

Итак, предположим, что в формуле Грэма ячейка A1 содержит значение 1, ячейка B1 пуста, а ячейка C1 пуста. Вот как формула «переводится» в Excel:

=IF(A1=1, IF(B1>C1, B1, C1), "")

=IF(1=1, IF(B1>C1, B1, C1), "")

=IF(B1>C1, B1, C1)

=IF(0>0, B1, C1)

=C1 =0

Вот почему Грэм видит 0, возвращаемый формулой, а когда 0 считается порядковым номером даты, он отображается как 1/0/00 (или 1/0/1900).

Чтобы избежать этого, вам нужно проверить, пусты ли оба B1 и C1.

Есть несколько способов сделать это. Рассмотрим этот вариант его формулы:

=IF(A1=1, IF((B1+C1=0), "", IF(B1>C1, B1, C1)), "")

Этот вариант добавляет оператор IF, чтобы проверить, равен ли B1, добавленный к C1, 0, что будет, если они оба пусты, поскольку Excel считает пустые значения и 0 эквивалентными в этом контексте. Недостатком является то, что если B1 или C1 содержат текстовое значение, то формула возвращает ошибку #VALUE.

Лучшим вариантом может быть следующий:

=IF(A1=1, IF(AND(B1=0,C1=0), "", IF(B1>C1, B1, C1)), "")

В этом воплощении оператор IF использует функцию AND, чтобы определить, равны ли оба B1 и C1 0. Это также решает потенциальную проблему ошибки #VALUE.

Если вы действительно хотите проверить, пусты ли и B1, и C1, вам придется полагаться на другой подход. Один из способов — использовать функцию СЧЁТ:

=IF(A1=1, IF(COUNTA(B1:C1)=0, "", IF(B1>C1, B1, C1)), "")

Если вы точно знаете, что и B1, и C1 никогда не будут содержать текстовых значений одновременно, вы также можете использовать функцию COUNT вместо функции COUNTA в приведенной выше формуле.

Другой подход — использовать функцию СЧИТАТЬПУСТОТЫ таким же образом; он возвращает количество пустых ячеек в диапазоне:

=IF(A1=1, IF(COUNTBLANK(B1:C1)=2, "", IF(B1>C1, B1, C1)), "")

Аналогичный вариант — использовать функцию ISBLANK (которая возвращает TRUE, если ячейка пуста) вместе с функцией AND:

=IF(A1=1, IF(AND(ISBLANK(B1), ISBLANK(C1)), "", IF(B1>C1, B1, C1)), "")

Вы также можете заставить Excel оценивать B1 и C1, как если бы они содержали текст, как это сделано здесь:

=IF(A1=1, IF(B1&C1="", "", IF(B1>C1, B1, C1)), "")

Эта формула не будет работать должным образом, если B1 или C1 содержат один пробел, поэтому вы можете добавить в смесь функцию TRIM:

=IF(A1=1, IF(TRIM(B1&C1)="", "", IF(B1>C1, B1, C1)), "")

В любой из рассмотренных выше формул вы также можете изменить оператор IF, который возвращает B1 или C1 с помощью функции MAX, следующим образом:

=IF(A1=1, IF(TRIM(B1&C1)="", "", MAX(B1:C1)), "")

Однако есть одно предостережение, если вы решите это сделать: функция MAX считает все текстовые значения эквивалентными 0. Таким образом, если ячейка B1 содержит «abc», а ячейка C1 содержит 1, то используется оператор сравнения «больше» (> ) считает «abc» больше 1, но MAX считает, что 1 больше «abc».

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

m/d/yyyy;;

Обратите внимание на две точки с запятой в конце формата. Они говорят Excel ничего не отображать, если значение в ячейке отрицательное или нулевое. Используя этот формат, вы никогда не увидите дату 1/0/1900; ячейка будет пустой.

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

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (10386) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.