Excel if функция: Функция ЕСЛИ — вложенные формулы и типовые ошибки

Функция Функция IFERROR | Справочные статьи Smartsheet

Возвращает первое значение, если оно не является ошибочным; в противном случае возвращает второе значение.

Пример использования

IFERROR([Имя задачи]15; «Требуются данные»)

Синтаксис

IFERROR(

  • value

  • value_if_error

)

  • value

    —

    значение, как правило, формула, позволяющая проверить наличие ошибки;

  • value_if_error

    —

    значение или формула, возвращаемые, если первое значение оказалось ошибочным.

Примечания об использовании

 

  • Использование этой функции похоже на функцию ISERROR, вложенную в функцию IF. Отличие заключается в том, что при работе с функцией IFERROR не требуется повторно вводить или проверять выражение.
  • Функция не подавляет ошибки, вызванные неверно сконструированными формулами. Примеры: #UNPARSEABLE, #CIRCULAR REFERENCE, #BLOCKED, #DATE EXPECTED, #CONTACT EXPECTED.
     

Примеры

В примере ниже используется информация из этой таблицы.

Номер строки

Предмет одежды

Артикул

Цена за ед.

Дата отправки

1

Футболка

C001

15,00 долл. США

05.03.2019

2

Брюки

C002

35,50 долл. США

20.03.2019

3

Куртка

C003

200,00 долл. США

27.02.2019

4

Куртка

C003

#DIVIDE BY ZERO

27.02.2019

 

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

 

Формула

Описание

Результат

=IFERROR(SUM([Цена за ед. ]:[Цена за ед.]), «Цена ошибочна»)

Суммирует значения в столбце Цена за ед. При наличии ошибки формула выдаёт значение Цена ошибочна.

Цена ошибочна

=COUNTIF([Дата отправки]:[Дата отправки], IFERROR(MONTH(@cell) = 3, » «))

Пересчитывает значения в столбце Дата отправки, если значение, указывающее на месяц, равняется 3 (март). При наличии ошибки в критерии формула выдаёт пустое значение.

Критерию отвечают строки 2 и 3 столбца Дата отправки.

2

=IFERROR(VLOOKUP(«Куртка», [Предмет одежды]:[Артикул], 2, false), » «)

Ищет значение Куртка в столбце Предмет одежды. Если формула его находит, то она выдаёт значение из столбца Артикул. При наличии ошибки (#No Match) выпадает пустое значение. 

C003

Остались вопросы?

Используйте шаблон Руководство по работе с формулами, чтобы просмотреть дополнительные ресурсы и изучить более 100 формул. Руководство содержит глоссарий, описывающий каждую функцию, обращение с которой вы сможете отработать на практике. Также в нём представлены примеры как часто используемых, так и более сложных функций.

Примеры реального использования функций можно посмотреть и в Сообществе Smartsheet. Там же можно задать интересующие вас вопросы другим пользователям программы.

Обратиться к Сообществу

VBA Excel. Оператор If…Then…Else и функция IIf

Однострочная и многострочная конструкции оператора If…Then…Else и функция IIf, используемые в коде VBA Excel — синтаксис, компоненты, примеры.

1.
Оператор If…Then…Else

1.1.
Однострочная конструкция

1. 1.1.
Синтаксис однострочной конструкции If…Then…

1.1.2.
Компоненты однострочной конструкции If…Then…

1.2.
Многострочная конструкция

1.2.1.
Синтаксис многострочной конструкции If…Then…Else

2.
Функция IIf

Оператор If…Then…Else

Оператор If…Then…Else предназначен для передачи управления одному из блоков операторов в зависимости от результатов проверяемых условий.

Однострочная конструкция

Оператор If…Then…Else может использоваться в однострочной конструкции без ключевых слов Else, End If.

Синтаксис однострочной конструкции If…Then…

If [условие] Then [операторы]

Компоненты однострочной конструкции

If…Then…

  • условие — числовое или строковое выражение, возвращающее логическое значение True или False;
  • операторы — блок операторов кода VBA Excel, который выполняется, если компонент условие возвращает значение True.

Если компонент условие возвращает значение False, блок операторов конструкции If…Then… пропускается и управление программой передается следующей строке кода.

Пример 1

1

2

3

4

5

6

Sub Primer1()

Dim d As Integer, a As String

d = InputBox(«Введите число от 1 до 20», «Пример 1», 1)

   If d > 10 Then a = «Число » & d & » больше 10″

MsgBox a

End Sub

Многострочная конструкция

Синтаксис многострочной конструкции If…Then…Else

1

2

3

4

5

6

7

8

If [условие] Then

[операторы]

ElseIf [условие] Then

[операторы]

—————-

Else

[операторы]

End If

Компоненты многострочной конструкции If…Then…Else:

  • условие — числовое или строковое выражение, следующее за ключевым словом If или ElseIf и возвращающее логическое значение True или False;
  • операторы — блок операторов кода VBA Excel, который выполняется, если компонент условие возвращает значение True;
  • пунктирная линия обозначает дополнительные структурные блоки из строки ElseIf [условие] Then и строки [операторы].

Если компонент условие возвращает значение False, следующий за ним блок операторов конструкции If…Then…Else пропускается и управление программой передается следующей строке кода.

Самый простой вариант многострочной конструкции If…Then…Else:

If [условие] Then

[операторы]

Else

[операторы]

End If

Пример 2

1

2

3

4

5

6

7

8

9

10

11

12

13

14

Sub Primer2()

Dim d As Integer, a As String

d = InputBox(«Введите число от 1 до 40», «Пример 2», 1)

   If d < 11 Then

      a = «Число » & d & » входит в первую десятку»

   ElseIf d > 10 And d < 21 Then

      a = «Число » & d & » входит во вторую десятку»

   ElseIf d > 20 And d < 31 Then

      a = «Число » & d & » входит в третью десятку»

   Else

      a = «Число » & d & » входит в четвертую десятку»

   End If

MsgBox a

End Sub

Функция IIf

Функция IIf проверяет заданное условие и возвращает значение в зависимости от результата проверки.

Синтаксис функции

IIf([условие], [если True], [если False])

Компоненты функции IIf
  • условие — числовое или строковое выражение, возвращающее логическое значение True или False;
  • если True — значение, которое возвращает функция IIf, если условие возвратило значение True;
  • если False — значение, которое возвращает функция IIf, если условие возвратило значение False.

Компоненты если True и если False могут быть выражениями, значения которых будут вычислены и возвращены.

Пример 3

1

2

3

4

5

6

7

8

9

Sub Primer3()

Dim d As Integer, a As String

Instr:

On Error Resume Next

d = InputBox(«Введите число от 1 до 20 и нажмите OK», «Пример 3», 1)

If d > 20 Then GoTo Instr

    a = IIf(d < 10, d & » — число однозначное», d & » — число двузначное»)

MsgBox a

End Sub

Пример 4
Стоит отметить, что не зависимо от того, выполняется условие или нет, функция IIf вычислит оба выражения в параметрах если True и если False:

1

2

3

4

5

6

7

8

9

10

11

Sub Primer4()

On Error GoTo Instr

Dim x, y

x = 10

y = 5

    MsgBox IIf(x = 10, x + 5, y + 10)

    MsgBox IIf(x = 10, x + 5, y / 0)

Exit Sub

Instr:

MsgBox «Произошла ошибка: » & Err. Description

End Sub

При нажатии кнопки «Cancel» или закрытии крестиком диалогового окна InputBox из первых двух примеров, генерируется ошибка, так как в этих случаях функция InputBox возвращает пустую строку. Присвоение пустой строки переменной d типа Integer вызывает ошибку. При нажатии кнопки «OK» диалогового окна, числа, вписанные в поле ввода в текстовом формате, VBA Excel автоматически преобразует в числовой формат переменной d. В третьем примере есть обработчик ошибок.

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

Как исправить ошибки в функции ЕСЛИ в Excel

Функция ЕСЛИ является краеугольным камнем многих сложных формул в Excel. Используя ЕСЛИ, вы можете установить условие и два выхода, когда это условие выполняется или нет. Более того, вы даже можете вкладывать эти функции ЕСЛИ друг в друга до тех пор, пока не будут соблюдены все необходимые вам критерии.

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

Понимание функции ЕСЛИ

Наиболее важным шагом в очистке вашей функции ЕСЛИ от ошибок является понимание функции и ее синтаксиса. ЕСЛИ — одна из самых популярных функций в Excel. Функция ЕСЛИ выполняет логическую проверку, а затем возвращает одно значение, когда результат логической проверки истинен, и другое значение, когда результат проверки оказывается ложным. Таким образом, функция ЕСЛИ состоит из трех частей:

  • логическая_проверка
  • значение_если_истина
  • значение_если_ложь

Когда вы соедините эти части вместе, вы получите функцию ЕСЛИ:

 ЕСЛИ(логическая_проверка, значение_если_истина, [значение_если_ложь])

value_if_false — это значение, возвращаемое, если условие не выполняется.

Пример функции IF

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

 =ЕСЛИ(B2=B3, A2&" & "&A3&" получили одинаковые оценки!", A2&" & "&A3&" не получили одинаковые оценки.")

Эта формула вызывает функцию ЕСЛИ и запускает логическая проверка B2=B3. Если результат теста положительный, то функция скажет, что у двух студентов одинаковый балл. В противном случае, если результат теста будет отрицательным, функция сообщит, что два студента получили разные баллы.

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

Пример вложенной функции ЕСЛИ

В спирали вложенных функций ЕСЛИ очень легко заблудиться, но вы можете освоить ее, поняв ее структуру. В этом примере списка у нас есть имена и баллы некоторых случайных студентов в тесте. Мы также хотим установить многоуровневую систему. Баллы выше 90 получают S+, от 80 до 90 получают S, от 70 до 80 получают A, а затем каждые 10 баллов поднимаются на одну букву.

Мы можем сделать это с помощью одной формулы, точнее, вложенной формулы ЕСЛИ. Формула будет следующей:

 = ЕСЛИ(B2>90, "S+", ЕСЛИ(B2>80, "S", ЕСЛИ(B2>70, "А", ЕСЛИ(B2>60, "В", ЕСЛИ(B2>50, " C", IF(B2>40, "D", IF(B2>30, "E", "F")))))))

Первоначальный логический тест в этой формуле должен проверить, выше ли оценка чем 90. Если это так, функция возвращает значение S+. Пока все хорошо, верно?

Но цепочка функций ЕСЛИ начинается с того, что value_if_false сама является функцией ЕСЛИ. Таким образом, если B2 не превышает 90, формула затем перейдет к следующей функции ЕСЛИ, чтобы проверить, превышает ли B2 значение 80. Если этот тест возвращает значение true, то значение B2 определенно находится между 80 и 9.0, поэтому формула возвращает значение S.

С другой стороны, если второй тест также возвращает false, то формула переходит к следующей функции ЕСЛИ, где она проверяет B2, чтобы увидеть, больше ли оно 70. формула будет продолжать тестировать B2 до тех пор, пока не получит положительный результат от одного из логических тестов или не достигнет конечной функции ЕСЛИ.

Исправление функции ЕСЛИ

В некоторых случаях ошибка возникает из-за того, что вы испортили саму функцию ЕСЛИ. Это означает, что есть ошибка либо в синтаксисе функции, либо в ячейках, на которые она ссылается.

Первое, что вы должны сделать, чтобы убедиться, что ваша функция работает правильно, это проверить запятые и скобки. Помните синтаксис функции ЕСЛИ и знайте, что функция переходит к следующему параметру с каждой запятой. Кроме того, не забудьте закрыть круглые скобки сразу после того, как вы определили значение_если_ложь.

Помимо самой функции ЕСЛИ, могут возникнуть проблемы с вложенными функциями. Вы можете вложить любую функцию в функцию ЕСЛИ либо в параметрах value_if_true, либо в параметрах _value_if_false. Ключевым моментом здесь является знание функций, которые вы используете, чтобы не испортить синтаксис или не удивить функцию недопустимым вводом.

Если с вашей формулой что-то не так, Excel сообщит вам об ошибке с помощью сокращения. Зная, что каждый из них означает, у вас будет меньше проблем с их исправлением. Вот четыре наиболее распространенные ошибки, связанные с функцией ЕСЛИ.

Ошибка

Описание

#DIV/0!

Ваша формула пытается разделить значение на ноль.

#ЗНАЧ!

Вы ввели в формулу неверный тип данных. Например. формула ожидает числа, но вы вводите в нее текст

#REF!

Справочные ячейки или ячейка формулы были перемещены. Ссылки в формуле больше не существуют.

#ИМЯ?

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

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

В этом примере у нас есть количество мужчин и женщин в парке в разные дни. У нас также есть столбец, показывающий, было ли соотношение женщин и мужчин больше 1 в этот день. Этого можно добиться с помощью функции ЕСЛИ.

 =ЕСЛИ(C2/B2>1, "Да", "Нет")

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

Исправление ошибок и настройка условий

ЕСЛИ — это популярная функция, которая выполняет логический тест, а затем возвращает два значения в зависимости от результатов этого теста. Функция ЕСЛИ позволяет создавать множество сложных формул в Excel, но также можно упустить из виду, что именно происходит в формуле, и получить множество ошибок.

Ключ к использованию функции ЕСЛИ без каких-либо ошибок состоит в том, чтобы сначала понять ее и ее синтаксис. Если вы вкладываете другие функции в функцию ЕСЛИ, вам также следует ознакомиться с этими функциями.

Функция Excel If

Функция ЕСЛИ — одна из самых простых и полезных функций в рабочей книге Excel. Он выполняет простой логический тест, который зависит от результата сравнения и возвращает одно значение, если результат ИСТИНА, или другое значение, если результат ЛОЖЬ.

  • Пример 1: Использование простой функции ЕСЛИ для чисел
  • Пример 2: Использование функции ЕСЛИ для текстовых значений
  • Пример 3: Использование функции ЕСЛИ для значений даты
  • Пример 4: Использование функции ЕСЛИ с функциями И и ИЛИ вместе
  • Пример 5: Использование вложенной функции ЕСЛИ

 

  • ЕСЛИ(проверить, если C2>=90, если верно — возвращает «Отлично», иначе
  • ЕСЛИ(проверить, если C2>=80, если верно — возвращает «Хорошо», иначе
  • ЕСЛИ(проверить, если C2>=60, если верно — возвращает «Среднее», если ложь — возвращает «Плохо»)))

Оператор Значение Пример Описание
> Больше =ЕСЛИ(A1>10, «ОК») Если число в ячейке A1 больше 10, формула возвращает «ОК»; в противном случае возвращается 0.
< Менее =ЕСЛИ(A1<10, "ОК", "") Если число в ячейке A1 меньше 10, формула возвращает «ОК»; в противном случае возвращается пустая ячейка.

Imacros | Все права защищены © 2021