Проверка условий в Excel с If…Then…

Пьер Корнель

Угадай, сможешь ли ты, и выбери, посмеешь ли

Краткое руководство по VBA If Statement

В следующем коде показан простой пример использования оператора If в VBA

Если Sheet1.Range («A1») Value> 5, то Debug.Print «Значение больше 5». ElseIf Sheet1.Range («A1») Значение Что такое IF  и зачем оно тебе?

Оператор VBA If используется, чтобы позволить коду делать выбор при выполнении.

Часто вы захотите сделать выбор на основе данных, которые читает ваш макрос.

Например, вы можете захотеть читать только учащихся с оценками выше 70. При чтении каждого учащегося вы можете использовать оператор If для проверки оценок каждого учащегося.

Важное слово в последнем предложении — проверка. Оператор If используется для проверки значения, а затем для выполнения задачи на основе результатов этого теста.

Тестовые данные

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

Формат операторов VBA If Then

Формат оператора If Then следующий

Если [условие верно] Тогда

За ключевым словом If следует условие и ключевое слово Then

Всякий раз, когда вы используете оператор If Then, вы должны использовать соответствующий оператор End If.

Когда условие истинно, обрабатываются все строки между If Then и End If.

Если [условие истинно] Тогда [строки кода] [строки кода] [строки кода] Конец Если

Чтобы сделать код более читабельным, рекомендуется делать отступы между операторами If Then и End If.

Отступ между If и End If

Отступ означает просто переместить строку кода на одну вкладку вправо. Практическое правило — делать отступ между операторами начала и конца, например:

Ниже… Конец ниже
Если Тогда… Конец Если
Если То… ElseIf… Else… Endif
Для следующего
Делай, пока… Цикл
Выбрать дело… Конец дела

Чтобы сделать отступ в коде, вы можете выбрать строки отступа и нажать клавишу Tab.Нажатие клавиш Shift + Tab приведет к отступу кода, то есть переместит его на одну табуляцию влево.

Вы также можете использовать значки на панели инструментов Visual Basic для отступа в коде.

Если вы посмотрите примеры кода на этом сайте, вы увидите, что код имеет отступ.

Простой пример If Then

В следующем коде печатаются имена всех студентов, набравших больше 50 баллов.

Sub ChitatOcenki () Dim i As Long ‘Просмотрите столбцы оценок For i = 2–11’ Проверьте, больше ли баллов 50, если Sheet1.Range («C» & i). Value> 50 Then ‘Введите имя ученика в «Немедленное окно» (Ctrl + G) Debug.Print Sheet1.Range («A» & i) .Value & «» & Sheet1.Range («B» & i). Value End If Next End Sub

Полученные результаты:

• Василий Кочин
• Максим Бородин
• Дмитрий Маренин
• Олеся Клюева
• Евгений Яшин

Поэкспериментируйте с этим примером и проверьте значение или знак> и посмотрите, как меняются результаты.

Условия IF

Часть кода между ключевыми словами If и Then называется условием. Условие — это утверждение, которое возвращает истину или ложь. В основном они используются с циклами и операторами If. При создании условия используются такие знаки, как «>, ,> =,

Ниже приведены примеры условий:

Вы могли заметить x = 5 как условие. Не путать с x = 5 при использовании в качестве цели.

Когда «=» используется в условии, это означает, что «левая сторона равна правой стороне».

В следующей таблице показано, как знак равенства используется в условиях и назначениях.

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

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

[переменная] [=] [оцените эту часть]

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

[x] [=] [5]

[b] [=] [6 = 5]

[x] [=] [MyFunc (5,6)]

Использование If ElseIf

Оператор ElseIf позволяет выбрать один из нескольких вариантов. В приведенном ниже примере мы печатаем оценки, которые находятся в диапазоне.

Sub IspElseIf () Если оценок> = 85, то Debug.Print «Наивысший балл» ElseIf Marks> = 75 Then Debug.Print «Отлично» End If End Sub

важно понимать, что порядок важен. Сначала проверяется условие If.

Если истина, то печатается «Наивысший балл» и оператор If завершается.

Если это ложь, код переходит к следующему ElseIf и проверяет его статус.

Давайте поменяем местами If и ElseIf в последнем примере. Код теперь выглядит так

Sub IspElseIfNeverno () ‘Этот код неверен, потому что ElseIf никогда не будет истинным Если Marks> = 75 Then Debug.Print «Отлично» ElseIf Marks> = 85 Then’ Код никогда не попадет сюда Debug.Print «Best Score» End If End Sub

В этом случае мы сначала проверяем значение больше 75. Мы никогда не будем печатать «Наивысший результат», потому что, если значение больше 85, это вызовет первый оператор if.

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

Если знаки> = 75 И знаки = 85 И знаки Давайте расширим исходный код. Вы можете использовать столько инструкций ElseIf, сколько захотите. Мы добавим больше, чтобы соответствовать всем нашим рейтингам.

Использование If Else

Заявление Else используется как ловушка для всех. В основном это означает «если бы не было условий» или «все остальное». В предыдущем примере кода мы не включили оператор печати для метки ошибки. Мы можем добавить его с помощью Else.

Sub IspElse () Если оценки> = 85, то Debug.Print «Лучший результат», ElseIf Marks> = 75 Then Debug.Print «Отлично», ElseIf Marks> = 55 Then Debug.Print «Good» ElseIf Marks> = 40 Then Debug.Print «Удовлетворительно» Иначе »Для всех остальных оценок Debug.Print« Failed »End If End Sub

Так что, если это не один из других типов, то это провал.

Мы напишем код, используя наши образцы данных, и распечатаем студента и его классификацию.

Sub DobClass () ‘получить последнюю строку Dim startRow As Long, lastRow As Long startRow = 2 lastRow = Sheet1.Cells (Sheet1.Rows.Count, 1) .End (xlUp) .Row Dim i As Long, Marks as Long Dim sClass As String ‘Пройти по столбцам оценок For i = startRow To lastRow Marks = Sheet1.Range («C» & i) .Value’ Проверить оценки и присвоить соответствующий рейтинг Если Marks> = 85 Then sClass = «Highest Score» ElseIf Marks> = 75 Затем sClass = «Отлично» ElseIf Marks> = 55 Then sClass = «Good» ElseIf Marks> = 40 Then sClass = «Satisfactory» Else ‘Для всех остальных оценок sClass = «Failed» End If’ Запишите класс в столбец E Sheet1. Диапазон («E» & i) .Value = sClass Next End Sub

Результаты такие: в столбце E — выставление оценок

Используя If And/If Or

В операторе If может быть несколько условий. Ключевые слова And и Or в VBA позволяют использовать несколько условий.

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

Давайте еще раз взглянем на наши образцы данных. Теперь мы хотим вывести всех студентов, набравших от 50 до 80 баллов.

Мы используем And, чтобы добавить дополнительное условие. Код гласит: Если оценка больше или равна 50 и меньше 75, введите имя учащегося.

Sub ProverkaStrokiOcenok () Dim i As Long, mark As Long For i = от 2 до 11 ‘Сохраняет оценки для текущих оценок учащихся = Sheet1.Range («C» & i). Value’ Проверяет, если оценки больше 50 и меньше 75 Если вы отмечаете> = 50 и отмечает Отображать имя и фамилию в результатах:

• Дмитрий Маренин
• Олеся Клюева
• Евгений Яшин

В нашем следующем примере мы хотим знать, кто из учеников изучал историю или геометрию. Итак, в этом случае предположим, изучал ли студент историю ИЛИ если он изучал геометрию (Ctrl + G).

Sub ChitatObektOcenki () Dim i As Long, mark As Long ‘Пропустить столбцы знаков For i = 2 До 11 mark = Sheet1.Range («D» & i). Value’ Проверяет, больше ли баллов 50 и меньше 80 Если признаки = «История» или признаки = «Геометрия» Тогда ‘Введите имя и фамилию в непосредственном окне (Ctrl + G) Debug.Print Sheet1.Interval («A» & i) .Value & «» & Sheet1. Диапазон («B» и i). Значение End If Next End Sub

Полученные результаты:

• Василий Кочин
• Александр Грохотов
• Дмитрий Маренин
• Николай Куликов
• Олеся Клюева
• Наталья Теплых
• Дмитрий Андреев

Использование более чем одного из этих условий часто является источником ошибок. Запомните практическое правило: старайтесь делать это как можно проще.

Использование SE AND

А работает это так:

Вы заметите, что E истинно только при соблюдении всех условий.

Использование SE O

Ключевое слово OR работает следующим образом

Вы заметите, что ИЛИ ложно только тогда, когда все условия ложны.

Комбинирование And и Or может затруднить чтение кода и вызвать ошибки. Использование круглых скобок может прояснить условия.

Sub OrSAd () Dim subject As String, flags As Long object = «History» flags = 5 If (object = «Geometry» Or object = «History») And flags> = 6 Then Debug.Print «TRUE» Else Debug.Print «FALSE» End If End Sub

Использование IF NOT

Также есть оператор НЕ. Возвращает результат, противоположный условию.

Следующие две строки кода эквивалентны.

Если он набрал = 40 Тогда

а также

Если это правда, то если не ложь, то

а также

Если ложь, то если не правда, то

Заключение условия в круглые скобки упрощает чтение кода

Если нет (знаки> = 40), то

Обычно Not используется при проверке, установлен ли объект. Возьмем, к примеру, рабочий лист. Здесь мы объявляем рабочий лист.

Dim mySheet As Worksheet »Здесь есть код

Мы хотим проверить mySheet перед его использованием. Мы можем проверить, ничего ли.

Если mySheet ничего не значит, тогда

Невозможно проверить, есть ли это что-то, так как есть много разных способов, которыми это может быть. Поэтому мы используем НЕ с ничем.

Если не мой лист — ничто, тогда

Если вы находите это немного запутанным, вы можете использовать круглые скобки, как здесь

Если нет (mySheet Is Nothing), то

Функция IIF

VBA имеет функцию, аналогичную функции If в Excel. В Excel вы часто используете функцию If следующим образом:

= ЕСЛИ (F2 = «», «», F1 / F2)

Формат

= Если (условие, действие, если ИСТИНА, действие, если ЛОЖЬ).

VBA имеет функцию IIf, которая работает точно так же. Посмотрим на пример. В следующем коде мы используем IIf для проверки значения переменной val. Если значение больше 10, мы печатаем TRUE, иначе мы печатаем FALSE.

Sub ProveritVal () Dim result As Boolean Dim val As Long ‘Print TRUE val = 11 result = IIf (val> 10, TRUE, FALSE) Debug.Print result’ print FALSE val = 5 result = IIf (val> 10, TRUE, FALSE) Debug Print result End Sub

В нашем следующем примере мы хотим напечатать «Удовлетворен» или «Не прошел» рядом с каждым учеником в зависимости от их оценки. В первом фрагменте кода мы будем использовать для этого обычный оператор If на языке VBA.

Sub ProveritDiapazonOcenok () Dim i As Long, mark As Long For i = 2–11 ‘Сохраняет оценки для оценок текущего учащегося = Sheet1.Range («C» & i) .Value’ Проверяет, сдал ли учащийся или нет Если оценки> = 40 Then ‘Запишите имена для столбца F Sheet1.Range («E» & i) = «Satisfactory» Else Sheet1.Range («E» & i) = «Failed» End If Next End Sub

В следующем фрагменте кода мы будем использовать функцию IIf. Код здесь намного чище.

Sub ProveritDiapazonOcenok () Dim i As Long, пометить As Long For i = 2–11 ‘Сохраняет оценки для текущих оценок учащегося = Sheet1.Interval («C» & i)’ Проверяет, сдал ли учащийся Sheet1.Interval («E» & i) .Value = IIf (знаки> = 40, «Удовлетворительно», «Неудачно») Next End Sub

Функция IIf очень полезна в простых случаях, когда вы имеете дело с двумя возможными вариантами.

Использование вложенного IIf

Вы также можете вкладывать операторы IIf, как в Excel. Это означает использование результата одного IIf с другим. Давайте добавим еще один тип результата к нашим предыдущим примерам. Теперь мы хотим напечатать «Отлично», «Удовлетворительно» или «Неудовлетворительно» для каждого ученика.

Используя обычный VBA, мы бы сделали это так

Sub ProveritRezultatiTip2 () Dim i As Long, mark As Long For i = 2–11 ‘Сохраняет оценки для текущих оценок учащегося = Sheet1.Interval («C» & i) .Value Если оценки> = 75 Then Sheet1.Interval («E «& i) .Value =» Отлично «ElseIf mark> = 40 Then ‘Запишите имена для столбца F Sheet1.Range (» E «& i) .Value =» Satisfactory «Else Sheet1.Range (» E «& i) Значение = «Неудачно» Конец Если Следующий Конец Подп

Используя вложенный IIf, мы могли бы сделать это так

Sub IspNestedIIF () Dim i As Long, отмечает As Long, результат как String Для i = от 2 до 11 mark = Sheet1.Range («C» ei). Value result = IIf (mark> = 55, «OK», IIf (знаки> = 40, «Удовлетворительно», «Неудачно»)) Sheet1.Interval («E» & i) .Value = result Next End Sub

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

На что обращать внимание

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

В приведенном ниже примере мы хотим разделить на результат, если он не равен нулю. Если это ноль, мы хотим вернуть ноль.

оценки = 0 всего = IIf (баллы = 0, 0, 60 / баллы)

Однако, когда высота равна нулю, код сгенерирует ошибку «Делить на ноль». Это потому, что он оценивает как истинные, так и ложные утверждения. Вот ложное утверждение, например. (60 / баллов) оценивается как ошибка, потому что оценки равны нулю.

Если мы используем обычный оператор IF, он выполнит только соответствующую строку.

знаки = 0 Если знаки = 0 Тогда ‘Выполнять эту строку только тогда, когда знаки равны нулю total = 0 Иначе’ Выполнять только эту строку, когда знаки не являются общим числом ноль = 60 / знаков Конец Если

Это также означает, что если у вас есть функции для ИСТИНА и ЛОЖЬ, оба будут выполнены. Таким образом, IIF будет выполнять обе функции, даже если использует только одно возвращаемое значение. Например:

‘Обе функции будут выполняться каждый раз, когда total = IIf (mark = 0, Func1, Func2)

SE v IIf

Так что лучше?

В этом случае вы можете видеть, что IIf короче и аккуратнее. Однако, если условия усложняются, лучше всего использовать обычный оператор If. Обратной стороной IIf является то, что он малоизвестен, поэтому другие пользователи могут не понимать его так же, как код, написанный с помощью обычного оператора if.

Кроме того, как мы обсуждали в последнем разделе, IIF всегда оценивает части ИСТИНА и ЛОЖЬ, поэтому, если вы имеете дело с большим количеством данных, оператор IF будет работать быстрее.

Мое практическое правило — использовать IIf, когда он легко читается и не требует вызовов функций. В более сложных случаях используйте обычный оператор If.

Использование Select Case

Оператор Select Case — это альтернативный способ написания статистики If с большим количеством ElseIf. Вы найдете этот тип оператора в популярных языках программирования, где он называется оператором переключения. Например, Java, C #, C ++ и Javascript имеют оператор switch.

Формат

Выбор случая [переменная] Случай [условие 1] Случай [условие 2] Случай [условие n] Случай Другое Конец Выбрать

Давайте возьмем наш пример DobClass сверху и перепишем его с помощью оператора Select Case.

Sub DobavitClass () ‘получить последнюю строку Dim startRow As Long, lastRow As Long startRow = 2 lastRow = Sheet1.Cells (Sheet1.Rows.Count, 1) .End (xlUp) .Row Dim i As Long, Marks as Long Dim sClass As String ‘Пройти по столбцам оценок For i = startRow To lastRow Marks = Sheet1.Range («C» & i) .Value’ Проверить оценки и присвоить соответствующий рейтинг Если Marks> = 85 Then sClass = «Самый высокий балл» ElseIf Marks> = 75 Затем sClass = «Отлично» ElseIf Marks> = 55 Then sClass = «Good» ElseIf Marks> = 40 Then sClass = «Satisfactory» Else ‘Для всех остальных оценок sClass = «Failed» End If’ Запишите класс в столбец E Sheet1. Диапазон («E» & i) .Value = sClass Next End Sub

Ниже приведен тот же код, который использует оператор Select Case. Главное, что вы заметите, это то, что мы используем «Case 85 to 100» вместо «знаков> = 85 и знаков = 85 И знаков Sub DobavitClassSSelect () ‘получить первую и последнюю строку Dim firstRow As Long, lastRow As Long firstRow = 2 lastRow = Cells (Cells.Rows.Count, 1) .End (xlUp) .Row Dim i As Long, отметить As Long Dim sClass As String ‘Пропустить столбцы знаков For i = firstRow To lastRow mark = Sheet1.Range («C» & i) .Value’ Выбрать знаки и ранжировать соответственно Выбрать Case mark Case 85 To 100 sClass = «Наивысший балл» Case 75 To 84 sClass = «Отлично» Вариант с 55 по 74 sClass = «Хороший» Случай с 40 по 54 sClass = «Удовлетворительно» Случай Иначе ‘Для всех других оценок sClass = «Неудачный» Конец Выбрать’ Запишите класс в столбец E Sheet1.Range (» E «& i) .Value = sClass Next End Sub

Случай использования

вы можете переписать оператор select в том же формате, что и исходный ElseIf. Вы можете использовать Is с Case.

Отметить регистр Case Is> = 85 sClass = «Самый высокий балл» Case Is> = 75 sClass = «Отлично» Case Is> = 55 sClass = «Good» Case Is> = 40 sClass = «Satisfactory» Case Else ‘Для всех другие sClass = «Failed» End Select

Вы можете использовать Is для проверки нескольких значений. В следующем коде мы проверяем, равны ли баллы 5, 7 или 9.

Sub TestNeskZnach () подписывает тусклый цвет As Long Marks = 7 Select Casemarks Case Is = 5, 7, 9 Debug.Print True Case Else Debug.Print False End Select End Sub

Попробуйте это упражнение

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

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

В столбцах с H по L перечислите всех учащихся, получивших оценки по этому предмету. Мы хотим классифицировать их результат как положительный или отрицательный. Оценка ниже 40 — неудача, оценка 40 или выше — успешный результат.

Столбец H: Имя

Столбец I: Фамилия

Столбец J: Баллы

Столбец H: объект

Столбец I: тип результата: прошел или не прошел

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

Ответ на упражнение

В следующем коде показано, как выполнить предыдущее упражнение.

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