Независимо от того, являетесь ли вы опытным экспертом или начинающим пользователем электронных таблиц, вам стоит ознакомиться с этим руководством по использованию операторов IF в Excel
Все знают, насколько универсален оператор IF в скриптовых программах, но знаете ли вы, что ту же логику можно использовать внутри ячеек Excel?
Основное определение оператора IF в программе заключается в том, что он позволяет вывести что-то определенное на основе результатов нескольких входов. Вы можете выполнить совершенно другие вычисления, основываясь на результатах других вычислений. Можно выполнить условное форматирование. Можно даже основывать вывод на поиске строк в ячейках ввода
Если это звучит сложно, не волнуйтесь. Давайте рассмотрим несколько креативных способов использования операторов IF в Excel
Что такое оператор IF в Excel?
Когда большинство людей думают об использовании оператора IF в Excel, они думают о VBA. Это происходит потому, что оператор IF обычно представляет собой логику, которая используется в мире программирования. Однако вы можете использовать ту же логику программирования прямо в ячейке электронной таблицы
Набрав в ячейке ‘=IF(‘, вы увидите, как должен выглядеть синтаксис вашего оператора IF, чтобы функционировать правильно. Основное требование – это просто ‘логический тест’. По умолчанию вывод в ячейку будет TRUE или FALSE, но вы можете настроить его, включив в функцию дополнительные параметры
Как работает базовая функция ЕСЛИ
Сначала давайте рассмотрим базовую функцию ЕСЛИ. В приведенном выше примере электронной таблицы у меня есть четыре действия, которые я регистрирую в журнале, связанные с моим автомобилем. Я записываю дату, когда происходит любое из четырех событий: замена масла, ремонт автомобиля, регистрация или продление страховки
Допустим, если в столбце ‘Ремонт’ стоит ‘ДА’, то я хочу, чтобы тип события имел значение ‘РЕМОНТ’. В противном случае он должен быть ‘NON-REPAIR’. Логика этого оператора IF очень проста:
IFC2'YES''Repair''Non-Repair'
Заполнение всего столбца этой формулой дает следующие результаты:
Это полезная логика, но в данном конкретном случае она не имеет особого смысла. Достаточно посмотреть на колонку ‘Отремонтировано’, чтобы определить, была ли эта дата связана с ремонтом или нет
Итак, давайте рассмотрим некоторые более продвинутые операторы функции IF, чтобы увидеть, можем ли мы сделать этот столбец немного более полезным
Операторы AND и IF
Как и в обычной программе, иногда для того, чтобы проверить два или три условия, которые зависят друг от друга, необходимо использовать логику AND. То же самое верно и здесь
Давайте определим два новых типа событий: Запланированное или Незапланированное
В данном примере мы сосредоточимся только на столбце Замена масла. Я знаю, что обычно я планирую замену масла на 2-й день каждого месяца. Любая замена масла, не приходящаяся на второй день месяца, является незапланированной заменой масла
Чтобы определить их, нам нужно использовать логику AND следующим образом:
IFANDDAYA2)=B2'YES'),'Planned''Unplanned'
Результаты выглядят следующим образом:
Это работает отлично, но, как вы можете видеть, есть небольшой логический недостаток. Это работает для отображения замены масла в ожидаемые даты – они отображаются как ‘Запланировано’. Но когда столбец ‘Замена масла’ пуст, вывод также должен быть пустым. В таких случаях возвращать результат не имеет смысла, поскольку никакой замены масла никогда не происходило
Чтобы добиться этого, мы перейдем к следующему продвинутому уроку по функции ЕСЛИ: вложенные операторы ЕСЛИ
Вложенные операторы ЕСЛИ
Основываясь на последней функции, вам нужно добавить еще один оператор IF внутри исходного оператора IF. Оно должно возвращать пустое значение, если исходная ячейка ‘Замена масла’ пуста
Вот как выглядит этот оператор:
IFISBLANKB2),''IFANDDAYA2)=B2'YES'),'Planned''Unplanned'))
Теперь оператор начинает выглядеть немного сложным, но на самом деле это не так, если присмотреться. Первый оператор IF проверяет, является ли ячейка в столбце B пустой. Если да, то возвращается пробел или ”
Если ячейка не пустая, то вставьте тот же оператор IF, который мы использовали в разделе выше, в часть False первого оператора IF. Таким образом, вы проверяете и записываете результаты о дате замены масла только в том случае, если замена масла действительно имела место. В противном случае ячейка будет пустой
Как вы можете себе представить, это может стать ужасно сложным. Поэтому при вложении операторов IF всегда делайте по одному шагу за раз. Проверяйте логику отдельных операторов IF, прежде чем начинать их вложение друг в друга. Потому что, как только у вас появится несколько таких вложенных операторов, их устранение может превратиться в настоящий кошмар
Операторы ИЛИ
Теперь мы собираемся усилить логику. Допустим, на этот раз я хочу вернуть ‘Годовое обслуживание’, если одновременно была произведена замена масла или ремонт, совмещенный с регистрацией или страхованием, но просто ‘Текущее обслуживание’, если была произведена только замена масла. Это звучит сложно, но при правильной логике оператора IF это совсем не сложно
Такая логика требует комбинации вложенного оператора IF и пары операторов OR. Вот как будет выглядеть этот оператор:
IFORB2'YES'C2'YES'),IFORD2'YES'E2'YES'),'Yearly Maintenance''Routine Maintenance'),''
Вот как выглядят результаты:
Поразительно, какой сложный анализ можно выполнить, просто комбинируя различные логические операторы внутри вложенных операторов IF
Результаты, основанные на диапазонах значений
Часто бывает очень полезно преобразовать диапазоны значений в какой-либо текстовый результат. Это может быть просто преобразование температуры от 0 до 50 градусов F в ‘Холодно’, от 50 до 80 – в ‘Тепло’, а все, что выше 80 – в ‘Жарко’
Учителя, вероятно, больше всего нуждаются в этой логике из-за буквенных оценок. В следующем примере мы рассмотрим, как преобразовать числовое значение в текст на основе именно такого диапазона
Допустим, учитель использует следующие диапазоны для определения буквенных оценок:
- От 90 до 100 – пятерка.
- От 80 до 90 – B.
- от 70 до 80 – C.
- От 60 до 70 – D.
- Меньше 60 – F.
Вот как будет выглядеть такой многовложенный оператор ‘ИФ’:
IFB2>89'A'IFB2>79'B'IFB2>69'C'IFB2>59'D''F'))))
Каждое гнездо – это следующий диапазон в серии. Нужно быть очень внимательным, чтобы завершить оператор правильным количеством скобок, иначе функция не будет работать правильно
Вот как выглядит полученный лист:
Как видите, это позволяет представить любое число в виде описательной строки. Она также будет автоматически обновляться, если числовое значение на листе изменится
Использование логики IF-THEN является мощным средством
Как программист, вы уже знаете силу операторов IF. Они позволяют автоматизировать логический анализ любого вычисления. Это очень мощный инструмент в языке сценариев, но, как вы видите, он не менее эффективен и в ячейках таблицы Excel
Проявив немного творчества, вы можете сделать некоторые впечатляющие вещи с помощью логики оператора IF и других формул в Excel
Какую уникальную логику вы придумали, используя операторы IF в Excel? Поделитесь своими идеями и советами в разделе комментариев ниже!
Комментировать