Использование операторов И, ИЛИ и НЕ с условным форматированием
Вы также можете использовать операторы И, ИЛИ и НЕ в формулах условного форматирования. При этом вы можете опустить функцию ЕСЛИ.
На вкладке Главная выберите Условное форматирование > Создать правило. Затем выберите параметр Использовать формулу для определения форматируемых ячеек, введите формулу и примените формат.
Вот как будут выглядеть формулы для примеров с датами:
Если A2 больше B2, отформатировать ячейку, в противном случае не выполнять никаких действий.
=И(A3>B2;A3 B2;A4 B2)
Если A5 НЕ больше B2, отформатировать ячейку, в противном случае не выполнять никаких действий. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ. Если изменить формулу на =НЕ(B2>A5), она вернет значение ИСТИНА, а ячейка будет отформатирована.
Примечание: Распространенной ошибкой является ввод формулы в условное форматирование без знака равенства (=). В этом случае вы увидите, что диалоговое окно Условное форматирование добавит знак равенства и кавычки в формулу — =»OR(A4>B2,A4
Логические функции в Excel
В арсенале Excel существует набор логических функций, которые вы можете найти на вкладке Формулы в группе Библиотека функций в выпадающем меню Логические. Все логические функции возвращают ИСТИНА или ЛОЖЬ в зависимости от оценки. Ниже список функций с синтаксисом и кратким описанием.
ЕСЛИ(логическое_выражение; ; ) – Проверяет логическое_выражение и возвращает значение_если_истина в ячейку если оно равно ИСТИНА, иначе возвращает значение_если_ложь.
ЕСЛИОШИБКА(значение; значение_если_ошибка) – Возвращает значение аргумента другой логической функции не содержащей ошибку, иначе возвращает значение_если_ошибка.
ЕСНД(значение; значение_если_нд) – Возвращает указанное значение_если_нд, если выражение дает результат #Н/Д; в противном случае возвращает результат выажения.
И(логическое_значение1; ; …) – Проверяет логические_значения аргументов и возвращает ИСТИНА, если все они имеют значение ИСТИНА. Если хотя бы одно значение ложно, возвращает ЛОЖЬ.
ИЛИ(логическое_значение1; ; …) — Проверяет логические_значения аргументов и возвращает ИСТИНА, если хотя бы одно имеет значение ИСТИНА. Если все значения равны ЛОЖЬ, функция ИЛИ возвращает ЛОЖЬ.
ИСКЛИЛИ(логическое_значение1; ; …) – Проверяет логические_значения аргументов и возвращает ИСТИНА, если количество истинных значений четно, в противном случае возвращает ЛОЖЬ
ИСТИНА() – аргументов нет и возвращает значение ИСТИНА.
ЛОЖЬ() – аргументов нет и возвращает значение ЛОЖЬ.
НЕ(логическое_значение) – меняет значение ИСТИНА на ЛОЖЬ, и ЛОЖЬ на ИСТИНА.
Логические выражения и логические значения, используемые в функциях, обычно используют операторы сравнения (=, , = или ), которые возвращают ИСТИНА и ЛОЖЬ. Предположим, вы ввели следующую формулу:
В этой формуле Excel определяет, равны ли друг другу значения ячеек A1 и B1. Если равны, возвращает ИСТИНА, если нет – ЛОЖЬ. Затем Excel вычисляет второе логическое значение и возвращает ИСТИНА, если содержимое ячейки С1 больше или равно 500, иначе возвращает ЛОЖЬ.
После оценки логических значений, функция И сравнивает их и возвращает ИСТИНА, если оба аргумента имеют значение ИСТИНА. Однако, если хотя бы один аргумент равен ЛОЖЬ, функция И возвращает ЛОЖЬ.
Функция ЕСЛИ (часто используемая в программировании) дает Excel первые задатки разумного существа, которое может выбирать на основании полученных данных. В функции ЕСЛИ используется, так называемое, логическое выражение, от результата которого будет зависеть выбор Excel в пользу значение_если_истина или значение_если_ложь. Логическое выражение чаще всего использует операторы сравнения, которое возвращает либо ИСТИНА, либо ЛОЖЬ. Соответственно при логическом выражении равным ИСТИНА, Excel вернет значение, которое было установлено в аргументе значение_если_истина, в противном случае — значение_если_ложь.
Рассмотрим следующую формулу, которая в зависимости от значения в ячейке с условием, определяет по какой формуле выдавать результат:
Если значение ячейки E5 равняется Да, функция ЕСЛИ будет использовать аргумент значение_если_истина, которое говорит Excel, чтобы он увеличил Значение на 7,5%. Если ячейка E5 будет пустой или будет содержать что-нибудь отличное от Да, функция ЕСЛИ будет использовать аргумент значение_если_ложь, которое вернет значение ячейки D5 без изменений.
Т.е. аргументы значение_если_истина и значение_если_ложь функции ЕСЛИ могут содержать константы или выражения, результаты вычислений которых будут возвращены в ячейку с формулой ЕСЛИ.
Практический пример использования логических функций
В примере ниже попробуем частично использовать описанные выше функции для решения задачи, приближенной к реальной ситуации с расчетом премии, зависящей от определенных условий.
В качестве исходных данных – таблица со сведениями о работниках, в которой указан их пол и возраст.
Нам необходимо произвести расчет премии. Ключевые условия, от которых зависит размер премии:
- величина обычной премии, которую получат все сотрудники без исключения – 3 000 руб.;
- сотрудницам женского пола положена повышенная премия – 7 000 руб.;
- молодым сотрудникам (младше 1984 г. рождения) положена повышенная премия – 7 000 руб.;
Выполним необходимые расчеты, используя логические функции.
Встаем в первую ячейку столбца, в которой хотим посчитать размеры премий и щелкаем кнопку “Вставить функцию” (слева от сроки формул).
В открывшемся Мастере функций выбираем категорию “Логические”, затем в предложенном перечне операторов кликаем по строке “ЕСЛИ” и жмем OK.
Теперь нам нужно задать аргументы функции. Так как у нас не одно, а два условия получения повышенной премии, причем нужно, чтобы выполнялось хотя бы одно из них, чтобы задать логическое выражение, воспользуемся функцией ИЛИ. Находясь в поле для ввода значения аргумента “Лог_выражение” кликаем в основной рабочей области книги на небольшую стрелку вниз, расположенную в левой верхней части окна программы, где обычно отображается адрес ячейки. В открывшемся списке функций выбираем оператор ИЛИ, если он представлен в перечне (или можно кликнуть на пункт “Другие функции” и выбрать его в новом окне Мастера функций, как мы изначально сделали для выбора оператора ЕСЛИ).
Мы переключимся в окно аргументов функци ИЛИ
Здесь задаем наши условия получения премии в 7000 руб.:год рождения позже 1984 года;
пол – женский;
Теперь обращаем внимание на строку формул. Кликаем в ней на название первоначального оператора ЕСЛИ, чтобы переключиться в аргументы этой функции.
Заполняем аргументы функции и щелкаем OK:в значении “Истина” пишем цифру 7000;
в значении “Ложь” указываем цифру 3000;
Результат работы логических операторов отобразится в первой ячейке столбца, которую мы выбрали
Как мы можем видеть, окончательный вид формулы выглядит следующим образом: =ЕСЛИ(ИЛИ(C2>1984;D2=»жен.»);7000;3000) . Кстати, вместо использования Мастера функций можно было вручную составить и прописать данную формулу в требуемой ячейке.
Чтобы рассчитать премию для всех сотрудников, воспользуемся Маркером заполнения. Наведем курсор на правый нижний угол ячейки с формулой. После того, как курсор примет форму черного крестика (это и есть Маркер заполнения), зажимаем левую кнопку мыши и протягиваем выделение вниз, до последней ячейки столбца.
Все готово. Благодаря логическим операторам мы получили заполненные данные для столбца с премиями.
Простые примеры функции ЕСЛИ
В примере выше ячейка D2 содержит формулу: ЕСЛИ(C2 = Да, то вернуть 1, в противном случае вернуть 2)
В этом примере ячейка D2 содержит формулу: ЕСЛИ(C2 = 1, то вернуть текст «Да», в противном случае вернуть текст «Нет»). Как видите, функцию ЕСЛИ можно использовать для сравнения и текста, и значений. А еще с ее помощью можно оценивать ошибки. Вы можете не только проверять, равно ли одно значение другому, возвращая один результат, но и использовать математические операторы и выполнять дополнительные вычисления в зависимости от условий. Для выполнения нескольких сравнений можно использовать несколько вложенных функций ЕСЛИ.
B2;»Превышение бюджета»,»В пределах бюджета»)» />
=ЕСЛИ(C2>B2;»Превышение бюджета»;»В пределах бюджета»)
В примере выше функция ЕСЛИ в ячейке D2 означает: ЕСЛИ(C2 больше B2, то вернуть текст «Превышение бюджета», в противном случае вернуть текст «В пределах бюджета»)
B2;C2-B2;»»)» />
На рисунке выше мы возвращаем не текст, а результат математического вычисления. Формула в ячейке E2 означает: ЕСЛИ(значение «Фактические» больше значения «Плановые», то вычесть сумму «Плановые» из суммы «Фактические», в противном случае ничего не возвращать).
В этом примере формула в ячейке F7 означает: ЕСЛИ(E7 = «Да», то вычислить общую сумму в ячейке F5 и умножить на 8,25 %, в противном случае налога с продажи нет, поэтому вернуть 0)
Примечание: Если вы используете текст в формулах, заключайте его в кавычки (пример: «Текст»). Единственное исключение — слова ИСТИНА и ЛОЖЬ, которые Excel распознает автоматически.
Функция НЕ
Последняя логическая функция для обсуждения в этой статье – это функция NOT, и мы оставим самую простую последнюю. Хотя иногда поначалу бывает трудно увидеть, как функция использует «реальный мир».
Функция NOT меняет значение своего аргумента. Так что, если логическое значение ИСТИНА, тогда оно возвращает ЛОЖЬ. И если логическое значение ЛОЖЬ, оно вернет ИСТИНА.
Это будет легче объяснить на некоторых примерах.
Структура функции НЕ имеет вид;
= НЕ (логическое)
НЕ Функциональный Пример 1
В этом примере представьте, что у нас есть головной офис в Лондоне, а затем много других региональных сайтов.Мы хотим отобразить слово «Да», если на сайте есть что-то, кроме Лондона, и «Нет», если это Лондон.
Функция NOT была вложена в логический тест функции IF ниже, чтобы сторнировать ИСТИННЫЙ результат.
= IF (NOT (B2 = “London”), “Да”, “Нет”)
Это также может быть достигнуто с помощью логического оператора NOT. Ниже приведен пример.
= ЕСЛИ (В2 “Лондон”, “Да”, “Нет”)
НЕ Функциональный Пример 2
Функция NOT полезна при работе с информационными функциями в Excel. Это группа функций в Excel, которые что-то проверяют и возвращают TRUE, если проверка прошла успешно, и FALSE, если это не так.
Например, функция ISTEXT проверит, содержит ли ячейка текст, и вернет TRUE, если она есть, и FALSE, если нет. Функция NOT полезна, потому что она может отменить результат этих функций.
В приведенном ниже примере мы хотим заплатить продавцу 5% от суммы, которую он продает. Но если они ничего не перепродали, в ячейке есть слово «Нет», и это приведет к ошибке в формуле.
Функция ISTEXT используется для проверки наличия текста. Это возвращает TRUE, если текст есть, поэтому функция NOT переворачивает это на FALSE. И если ИФ выполняет свой расчет.
= ЕСЛИ (НЕ (ISTEXT (В2)), В2 * 5%, 0)
Овладение логическими функциями даст вам большое преимущество как пользователю Excel. Очень полезно иметь возможность проверять и сравнивать значения в ячейках и выполнять различные действия на основе этих результатов.
В этой статье рассматриваются лучшие логические функции, используемые сегодня. В последних версиях Excel появилось больше функций, добавленных в эту библиотеку, таких как функция XOR, упомянутая в этой статье. Будьте в курсе этих новых дополнений, вы будете впереди толпы.
Функция НЕ
Последняя логическая функция для обсуждения в этой статье — это функция NOT, и мы оставим самую простую последнюю. Хотя иногда поначалу бывает трудно увидеть использование этой функции в реальном мире.
Функция NOT меняет значение своего аргумента. Так что, если логическое значение ИСТИНА, тогда оно возвращает ЛОЖЬ. И если логическое значение ЛОЖЬ, оно вернет ИСТИНА.
Это будет легче объяснить на некоторых примерах.
Структура функции НЕ имеет вид;
= НЕ (логическое)
НЕ Функциональный Пример 1
В этом примере представьте, что у нас есть головной офис в Лондоне, а затем много других региональных сайтов. Мы хотим отобразить слово «Да», если на сайте есть что-то, кроме Лондона, и «Нет», если это Лондон.
Функция NOT была вложена в логический тест функции IF ниже, чтобы сторнировать ИСТИННЫЙ результат.
= ЕСЛИ (НЕ (B2 = "London"), "Да", "Нет")
Это также может быть достигнуто с помощью логического оператора NOT <>. Ниже приведен пример.
= ЕСЛИ (В2 <> "Лондон", "Да", "Нет")
НЕ Функциональный Пример 2
Функция NOT полезна при работе с информационными функциями в Excel. Это группа функций в Excel, которые что-то проверяют и возвращают TRUE, если проверка прошла успешно, и FALSE, если это не так.
Например, функция ISTEXT проверит, содержит ли ячейка текст, и вернет TRUE, если она есть, и FALSE, если нет. Функция NOT полезна, потому что она может отменить результат этих функций.
В приведенном ниже примере мы хотим заплатить продавцу 5% от суммы, которую он продает. Но если они ничего не перепродали, в ячейке есть слово «Нет», и это приведет к ошибке в формуле.
Функция ISTEXT используется для проверки наличия текста. Это возвращает TRUE, если текст есть, поэтому функция NOT переворачивает это на FALSE. И если ИФ выполняет свой расчет.
= ЕСЛИ (НЕ (ISTEXT (В2)), В2 * 5%, 0)
Овладение логическими функциями даст вам большое преимущество как пользователю Excel. Очень полезно иметь возможность проверять и сравнивать значения в ячейках и выполнять различные действия на основе этих результатов.
В этой статье рассматриваются лучшие логические функции, используемые сегодня. В последних версиях Excel появилось больше функций, добавленных в эту библиотеку, таких как функция XOR, упомянутая в этой статье. Будьте в курсе этих новых дополнений, вы будете впереди толпы.
Объединяем несколько условий.
Для описания условия в функции ЕСЛИ Excel позволяет использовать более сложные конструкции. Также можно использовать несколько условий. В этом случае мы также воспользуемся тем, что функции могут быть «вложены» друг в друга.
Чтобы объединить несколько условий в одно, мы используем логические функции ИЛИ и И. Рассмотрим простые примеры.
Пример 1
Функция ИЛИ возвращает ИСТИНА, если хотя бы одно из нескольких перечисленных в ней условий истинно.
= SE (OR (C2 = «Восток», C2 = «Юг»), «Экспорт», «Местный»)
Мы вставляем функцию ИЛИ как условие в функцию ЕСЛИ. В нашем случае, если регион покупателя — Восток или Юг, отгрузка считается экспортной.
Пример 2.
Давайте использовать несколько более сложных условий в функции ЕСЛИ.
Если регион продаж — Запад или Юг и количество больше 100, предоставляется скидка 10%.
= SE (И (ИЛИ (C2 = Запад, C2 = Юг), E2> 100), F2 * 0,1,0)
Функция И возвращает ИСТИНА, если все перечисленные в ней условия выполнены. Внутри функции И мы устанавливаем два условия:
- Регион — Запад или Юг
- Число больше 100.
Первый из них реализован так же, как и первый пример: ИЛИ (C2 = «Запад», C2 = «Юг»)
Во-вторых, здесь все очень просто: E2> 100
В строках 2, 3 и 5 выполняются оба условия. Эти покупатели получат скидку.
В строке 4 ни один из них не был выполнен. А в строке 6,7,8 выполняется только первая, но ее слишком мало. Следовательно, скидка будет нулевая.
Пример 3.
Конечно, эти несколько условий могут быть более сложными. Ведь логические функции можно «вкладывать» друг в друга.
Например, помимо вышеуказанного условия, скидка предоставляется только на темный шоколад.
Все наши ранее написанные условия, в свою очередь, становятся первым аргументом в новой функции И:
- Регион — Запад или Юг и число больше 100 (рассмотрено в примере 2)
- В названии шоколада присутствует слово «черный».
В результате мы получаем формулу ЕСЛИ с несколькими условиями:
= ЕСЛИ (И (ЕЧИСЛО (НАЙТИ («Черный»; D2)),И (ИЛИ (C2 = «Запад», C2 = «Юг»)), E2> 100), F2 * 0,1,0)
Функция НАЙТИ ищет точное совпадение. Если для нас не важен регистр символов в тексте, то вместо НАЙТИ можно использовать аналогичную функцию ТОЧНЫЙ.
= ЕСЛИ (И (ЕЧИСЛО (МАТЧ («черный»; D2)),И (ИЛИ (C2 = «Запад», C2 = «Юг»)), E2> 100), F2 * 0,1,0)
В результате количество вложенных условий в Excel может быть очень большим
Важно лишь строго соблюдать логическую последовательность их выполнения
Используйте функцию «Вычислить формулу»
В Windows вы можете использовать функцию «Вычислить формулу», чтобы шаг за шагом посмотреть, как Excel решает ваши формулы. Это отличный способ «увидеть» логический поток более сложных формул и устранить неполадки, если что-то не работает. Кнопку «Вычислить формулу» можно найти на ленте на вкладке Формулы.
Функция Вычислить формулу в ленте
На экране ниже показано «окно Вычисление формулы», открытое и готовое к работе. Каждый раз, когда вы нажимаете кнопку «Вычислить», вычисляется «следующий шаг» в формуле.
Окно Вычисление формулы
К сожалению, версия Excel для Mac не содержит этой функции, но вы можете использовать прием, описанный ниже.
Как перевернуть текст в excel
= Мир MS Excel/Статьи об Excel
- 1
- 2
- 3
| Приёмы работы с книгами, листами, диапазонами, ячейками |
| Приёмы работы с формулами |
| Настройки Excel |
| Инструменты Excel |
| Интеграция Excel с другими приложениями |
| Форматирование |
| Выпадающие списки |
| Примечания |
| Сводные таблицы |
| Гиперссылки |
| Excel и интернет |
| Excel для Windows и Excel для Mac OS |
Инструмент камера возвращает динамический снимок указанного диапазона. Динамический — это значит он изменяется при изменении диапазона, который мы сфотографировали. По умолчанию камера недоступна во всех версиях Excel. Для того что бы ей воспользоваться необходимо сначала вывести её на панель меню (в Excel версий до 2007) или на панель быстрого доступа (в Excel версий после 2003). Как это делается?
Для Excel версий до 2007:
Меню Сервис — пункт Настройка — вкладка Команды — категории Сервис — команды Камера — левой кнопкой мыши перетаскиваем иконку камеры на удобное место в меню.
Для Excel версий после 2003:
Кликаем правой кнопкой мыши по панели быстрого доступа — Настройка панели быстрого доступа — Выбрать команды из Все команды — Камера — Добавить — ОК.
Теперь, когда инструмент камера добавлен в меню/на панель быстрого доступа можно приступить к решению вопроса, озвученного в названии этой статьи: Как перевернуть текст в ячейке?Как я уже писал выше — это невозможно. Формулы и функции не работают с форматированием, ориентацию текста в диалоговом окне Формат ячеек можно изменять только в пределах от 90 до -90 градусов, в VBA нет функций, позволяющих это сделать. Остаётся только перевернуть монитор
Выделяем ячейку, которую надо скопировать. Левой кнопкой мыши кликаем на иконку инструмента камера. Окантовка выделенной ячейки станет активной (как при обычном копировании). Левой кнопкой мыши указываем место на листе, куда необходимо вставить снимок и он автоматически вставляется. Всё, мы получили динамический снимок ячейки. Остаётся только перевернуть его на 180 градусов и готово. На рисунке, в ячейке В2, Вы видите снимок ячейки А1:
Все изменения производимые в ячейке А1 будут отображаться в В2.
ПРИМЕЧАНИЕ: Можно так же «фотографировать» и один диапазон. Одновременно несколько ячеек/диапазонов или несмежные диапазоны инструмент камера не обрабатывает, о чём появится соответствующее сообщение, если Вы попытаетесь применить камеру к ним. С получившимся снимком можно работать как с обычным рисунком, внедрённым на лист, то есть, изменять его размеры, свойства, форму, устанавливать прозрачность, рамки и прочее.
Используйте ЕСЛИМН вместо вложенных ЕСЛИ
Если вы используете Excel 2016, у Office 365 есть новая функция, которую вы можете использовать вместо вложенных ЕСЛИ: функция ЕСЛИМН (IFS). Функция ЕСЛИМН (IFS) предоставляет специальную структуру для оценки нескольких условий без вложенности
Перепишем формулу из примера про оценки с использованием ЕСЛИМН:
Обратите внимание, в формуле всего одна пара скобок!
Что происходит, когда вы открываете электронную таблицу, которая использует функцию ЕСЛИМН (IFS) в более старой версии Excel? В Excel 2013 и 2010 (и я верю в Excel 2007, но не могу проверить) вы увидите «_xlfn» в ячейке. Ранее вычисленное значение все еще будет там, но если формула пересчитается, вы увидите ошибку #ИМЯ?.
ЕСЛИ
Это одна из самых важных функций в Excel. Как и в настоящем языке программирования, она проверяет какое-то условие, и если оно выполняется — пишет в ячейку что-то одно, а если нет — пишет что-то другое.
В общем виде она выглядит так:
=ЕСЛИ(условие; “значение_если_да”;”значение_если_нет”)
В условии может быть что угодно: сравнение ячеек, другие формулы, сравнения и математические команды — всё, что вам нужно проверить.
На практике можно сделать, например, так: пусть Excel проверяет возраст, и пишет документ, который в этом возрасте удостоверяет личность. До 14 лет это свидетельство о рождении, а после — паспорт. Для этого используем такую команду в ячейке:
Программа проверит, что стоит в предыдущей ячейке, и если там число меньше 14, то напишет «Свидетельство о рождении», а если уже есть 14 лет, то «Паспорт»:
Функция СУММЕСЛИМН
Данный оператор в Excel предназначен для вычисления суммы с использованием нескольких условий. К примеру, нужно определить заработную плату менеджеров, работающих в Южном филиале:
Добавляем строку с общей зарплатой менеджеров Южного филиала. Выделим ячейку, где будет показываться итог вычислений.
Кликаем по значку функции. В появившемся окне в поле поиска вводим «суммеслимн», нажимаем «Найти» и выбираем нужный оператор из списка.
В открывшемся окне необходимо заполнить аргументы функции. В «Диапазон_суммирования» указываем ячейки с заработной платой. «Диапазон_условия1» — ячейки с должностями сотрудников. «Условие1» = «менеджер», так как мы суммируем зарплату менеджеров. Теперь нужно учесть второе условие — взять менеджеров из Южного филиала. В «Диапазон_условия2» вводим ячейки с филиалами, «Условие2» = «Южный». Все аргументы определены, нажимаем «Ок».
В результате будет рассчитана общая зарплата всех менеджеров, работающих в Южном филиале.
Связь функции ЕСЛИ() с другими функциями использующие условия
EXCEL содержит также другие функции, которые можно применять для анализа данных с использованием условий. Например, для подсчета количества вхождений чисел в диапазоне ячеек используется функция СЧЁТЕСЛИ() , а для сложения значений, удовлетворяющих определенным условиям, используется функция СУММЕСЛИ() .
Функция ЕСЛИ() , как альтернативный вариант, может быть также использована для подсчета и сложения значений с использованием условий. Ниже приведены иллюстрирующие примеры.
Пусть данные находятся в диапазоне A6:A11 (см. файл примера)
Подсчитаем сумму значений, которые больше 10 с помощью функции СУММЕСЛИ() , записав =СУММЕСЛИ(A6:A11;»>10″) . Аналогичный результат (23) можно получить с помощью формулы массива =СУММ(ЕСЛИ(A6:A11>10;A6:A11)) (для ввода формулы в ячейку вместо ENTER нужно нажать CTRL+SHIFT+ENTER)
Теперь подсчитаем количество вхождений чисел больше 10 в диапазоне ячеек A6:A11 =СЧЁТЕСЛИ(A6:A11;»>10″) . Аналогичный результат (2) можно получить с помощью формулы массива =СЧЁТ(ЕСЛИ(A6:A11>10;A6:A11))
Теперь, когда принцип понятен, с помощью функции ЕСЛИ() можно конструировать и другие формулы с условиями. Например, нахождение минимального значения среди чисел больше 10: =МИН(ЕСЛИ(A6:A11>10;A6:A11))
Использование IF с функцией NOT
Последняя логическая функция, которую мы рассмотрим, — НЕ. Он смотрит на одно значение и определяет, совпадает ли оно с ячейкой, с которой сравнивает его. В моем примере у меня есть ячейка, содержащая слово Green. Excel смотрит на эту ячейку и сравнивает ее со словом Blue. Если он не синий, он возвращается в зеленый цвет, а если он синий, он возвращается в синий.
Преобразование содержимого ячейки с помощью операторов IF
Третье использование оператора IF в Excel — это преобразование содержимого ячейки во что-то еще, что мы можем использовать иначе, чем исходное содержимое. Часто результат формулы или выбор пользователя из раскрывающегося списка не в нужном вам формате. Вы можете использовать операторы IF для его преобразования.
Например, у меня есть раскрывающийся список, в котором пользователю предлагается выбрать ежемесячные или ежеквартальные платежи.
- Выпадающий список помещает 1 в ячейку L2, если пользователь выбирает ежемесячно, и 2 в L2, если пользователь выбирает квартал.
- Excel необходимо число, чтобы рассчитать количество ежегодных выплат по ипотеке пользователя, и оно будет равно 4, если ежеквартально, и 12, если ежемесячно.
Для этого я использую IF для преобразования 1 в 12 и 2 в 4 по следующей формуле:
= ЕСЛИ (L2 = 1; «12»; ЕСЛИ (L2 = 2; «4»))
Теперь Excel использует результат, который хранится в M2, для правильного расчета формул для процентов и основной суммы, выплачиваемых за каждый период.
Использование функции ЕСЛИОШИБКА
Последняя функция, которую мы рассмотрим сегодня, — это функция ЕСЛИОШИБКА. Это используется, если вы хотите изменить поведение Excel при возникновении ошибки. Часто, в зависимости от действий пользователя, Excel может предупредить вас об ожидаемой ошибке.
Предположим, кто-то использует мой калькулятор ипотеки, чтобы посмотреть ипотеку на срок более 35 лет. Затем они передумали и выбрали ипотеку на 20 лет. Все ячейки между 21 и 35 годами теперь будут отображать ошибку (ошибка #NUM). Чтобы подавить эту ошибку, мы вкладываем исходную формулу в ЕСЛИОШИБКА.
Исходная формула была:
= PPMT ($ R $ 2 / $ M $ 2, A35, $ M $ 2 * $ M $ 5, — $ F $ 16)
Добавляем ЕСЛИОШИБКУ так:
= ЕСЛИОШИБКА (PPMT ($ R $ 2 / $ M $ 2, A35, $ M $ 2 * $ M $ 5, — $ F $ 16), «»)
Что теперь произойдет, так это то, что если в этой ячейке есть ошибка, она будет отображать один пробел, а не ошибку.
Вы должны убедиться, что ваша формула работает правильно, прежде чем выполнять вложение с помощью ЕСЛИОШИБКА. В противном случае вы никогда не узнаете, вызывает ли он ошибку из-за опечатки или другой ошибки.
Совместное использование функций ЕСЛИ и ЕСЛИОШИБКА: вкратце
Мы рассмотрели функции ЕСЛИ и ЕСЛИОШИБКА и использовали их вместе с логическими функциями И, ИЛИ и НЕ. ЕСЛИ можно использовать:
- Чтобы сравнить содержимое ячейки.
- Чтобы преобразовать ячейку во что-то другое.
- И выполнить расчет ячейки на основе того, что в ней находится.
Используя функцию ЕСЛИ, мы просим Excel сравнить ячейку с тем, что нас интересует, и сделать одно, если оно истинно, и другое, если оно ложно. Итак, если у нас есть, например, ячейка, содержащая слово попугай, и мы хотим, чтобы Excel посмотрел на эту ячейку и вернул птицу, если ячейка содержит слово попугай, и вернул не птицу, если это не так, тогда мы можем использовать IF для этого. .
Мы также рассмотрели использование нескольких операторов IF в одной формуле, уделив особое внимание скобкам. В сочетании с другими логическими функциями мы увидели, что:
В сочетании с другими логическими функциями мы увидели, что:
- Если мы используем ЕСЛИ а также А ТАКЖЕ вместе Excel будет делать одно, если все условия соблюдены, и другое, если не все.
- Когда используешь ЕСЛИ а также ИЛИ ЖЕ. Excel будет делать одно, если выполняется одно из условий, и другое, если ни одно из условий не выполняется.
- С использованием ЕСЛИ а также НЕТ вместе Excel будет делать одно, если условие не выполняется, и другое, если оно есть.
Последней функцией, которую мы исследовали, была функция ЕСЛИОШИБКА. Это позволяет подавить ожидаемую ошибку или изменить текст, который Excel отображает при возникновении ошибки.
Большое спасибо за чтение, и я надеюсь, что теперь вы успешно используете очень универсальные функции ЕСЛИ в своих таблицах. Пожалуйста, не стесняйтесь оставлять любые комментарии, которые у вас могут быть ниже.
Эта статья точна и правдива, насколько известно автору. Контент предназначен только для информационных или развлекательных целей и не заменяет личного или профессионального совета по деловым, финансовым, юридическим или техническим вопросам.