Функции
Сортировка: дата создания дата изменения просмотры комментарии
Склейка диапазонов новыми функциями VSTACK и HSTACK
Как склеить (друг под другом или друг рядом с другом) несколько таблиц (в том числе с нескольких листов) с помощью новых функций Office 365 — ВСТОЛБИК (VSTACK) и ГСТОЛБИК (HSTACK).
Моделирование лотереи в Excel
Считаем вероятность выигрыша в лотерею, частоты выпадения шаров и моделируем игру по различным стратегиям с помощью макроса.
LAMBDA — новая суперфункция Excel
Подробный разбор с примерами новой функции LAMBDA, позволяющей создавать свои собственные функции в Microsoft Excel (включая реализацию даже рекурсивных вычислений).
Поиск последнего вхождения (инвертированный ВПР)
Все стандартные функции поиска (ВПР, ГПР, ПОИСКПОЗ и т.д.) ищут только сверху-вниз и слева-направо. Что же делать, если нужно реализовать обратный поиск совпадений, т.
Обманчивая простота функции ПОСЛЕД (SEQUENCE)
Разбор на примерах возможностей новой функции ПОСЛЕД (SEQUENCE) — генератора числовых последовательностей из последнего обновления Office 365 с динамическими массивами.
ВПР и числа-как-текст
Как научить функцию ВПР (VLOOKUP) искать значения, когда в исходных данных встречаются «числа-как-текст», что приводит к ошибкам #Н/Д.
Самый быстрый ВПР
Тест скорости 7 разных вариантов реализации поиска и подстановки данных из одной таблицы в другую: ВПР, ИНДЕКС+ПОИСКПОЗ, ПРОСМОТРХ, СУММЕСЛИ и т.д. Кто будет самым быстрым?
Функция ПРОСМОТРX — наследник ВПР
Подробный разбор новой функции ПРОСМОТРX (XLOOKUP), которая приходит на смену классической ВПР (VLOOKUP).
Функции динамических массивов: СОРТ, ФИЛЬТР и УНИК
Разбор на живых примерах трех главных функций динамических массивов в новом вычислительном движке Excel: СОРТ, ФИЛЬТР и УНИК + их сочетаний для решения практических задач.
Быстрый прогноз функцией ПРЕДСКАЗ (FORECAST)
Вычисление простого прогноза по линейному тренду с помощью функции ПРЕДСКАЗ (FORECAST).
Превращение текста в число функцией Ч (N)
Как добавить невидимый текстовый комментарий к формуле в ячейке с помощью функции преобразованиия Ч(N).
Разбор функции ДВССЫЛ (INDIRECT) на примерах
Подробный разбор на примерах нюансов и особенностей функции ДВССЫЛ (INDIRECT), позволяющей реализовать косвенные текстовые ссылки.
Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)
Подробный разбор функции замены текста ПОДСТАВИТЬ (SUBSTITUTE) и примеров ее использования для удаления неразрывных пробелов, подсчета количества слов, извлечения первых двух слов из текста.
Суммирование по множеству условий функцией БДСУММ (DSUM)
Как использовать одну из малоизвестных, но крайне полезных функций БДСУММ (DSUM) для выборочного суммирования по одному или нескольким сложным условиям (еще и связанным между собой И-ИЛИ).
Удаление лишних пробелов функцией СЖПРОБЕЛЫ (TRIM) и формулами
Как удалить из текста лишние пробелы в начале, в конце или между словами с помощью функции СЖПРОБЕЛЫ (TRIM) или формул.
Анализ топовых значений функциями НАИБОЛЬШИЙ и НАИМЕНЬШИЙ
Как использовать функции НАИБОЛЬШИЙ (LARGE) и НАИМЕНЬШИЙ (SMALL) для поиска предельных значений, составления рейтингов и ТОПов, сортировки формулами и др.
Частотный анализ по интервалам функцией ЧАСТОТА (FREQUENCY)
Как быстро подсчитать количество значений, попадающих в заданные интервалы «от-до» с помощью функции ЧАСТОТА (FREQUENCY) и формул массива.
Создание внутренних и внешних ссылок функцией ГИПЕРССЫЛКА
Максимально полное описание всех возможностей функции ГИПЕРССЫЛКА, умеющей создавать ссылки на ячейки внутри книги, внешние файлы, веб-страницы и т.д.
Получение элемента из набора по номеру функцией ВЫБОР (CHOOSE)
Как с помощью функции ВЫБОР (CHOOSE) реализовать выборку элементов из набора, работу с массивами, динамические итоги и склейку диапазонов.
Как не забивать гвозди микроскопом с функцией СУММПРОИЗВ
Для чего (на самом деле!) нужна функция СУММПРОИЗВ (SUMPRODUCT) и какие замечательные вещи она умеет делать (включая выборочный подсчет из ЗАКРЫТОГО файла).
Поиск точных совпадений с учетом регистра функцией СОВПАД (EXACT)
Большинство функций Excel не различают строчные и прописные буквы. Если же это необходимо, то сможет помочь функция СОВПАД (EXACT), умеющая сравнивать данные с учетом регистра.
Извлечение информации о ячейке функцией ЯЧЕЙКА (CELL)
Как получить подробную информацию о ячейке и ее параметры с помощью функции ЯЧЕЙКА (CELL). Используя ее можно определять тип введенных в ячейку данных, ее формат, наличие защиты и т.п.
Визуализация значками с функцией СИМВОЛ (CHAR)
Как выводить нестандартные символы и пиктограммы с помощью функции СИМВОЛ (CHAR) для визуализации количества проданного товара, динамики роста-падения и т.
Перехват ошибок в формулах функцией ЕСЛИОШИБКА (IFERROR)
Как перехватывать любые, возникающие не по вашей вине, ошибки в формулах, и заменять их на что-то более полезное и удобное с помощью функции ЕСЛИОШИБКА (IFERROR).
Превращение текстовой даты в полноценную функцией ДАТАЗНАЧ (DATEVALUE)
Как превратить в полноценную дату текстовые ее написания вида «8 мар 13», «2017.3.8», «09 сентябрь 2016» и т.д. с помощью функции ДАТАЗНАЧ.
Суммирование по «окну» на листе функцией СМЕЩ (OFFSET)
Как использовать функцию СМЕЩ (OFFSET), чтобы суммировать из динамического диапазона-«окна» на листе с заранее неизвестными размерами и положением.
Поиск позиции элемента в списке с ПОИСКПОЗ (MATCH)
Как использовать функцию ПОИСКПОЗ (MATCH) для поиска позиции нужного элемента в списке, первой или последней текстовой ячейки или ячеек с заданным значением в диапазоне.
Левый ВПР
Как реализовать в Excel аналог функции ВПР (VLOOKUP), который будет выдавать значения левее поискового столбца.
5 вариантов использования функции ИНДЕКС (INDEX)
Подробный разбор всех пяти вариантов применения мегамощной функции ИНДЕКС (INDEX): от простого поиска данных в столбце, до двумерного поиска в нескольких таблицах и создания авторастягивающихся диапазонов.
Поиск последней непустой ячейки в строке или столбце функцией ПРОСМОТР
Как найти значение последней непустой ячейки в строке или столбце таблицы Excel с помощью функции ПРОСМОТР (LOOKUP).
Поиск ближайшего рабочего дня функцией РАБДЕНЬ (WORKDAY)
Что если при расчете сроков нужная вам дата выпадет на выходные? Как найти ближайший рабочий день к заданной дате, учитывая выходные и праздники?
Поиск минимального или максимального значения по условию
Как найти в диапазоне чисел минимальное или максимальное значение по условию с помощью функций МИНЕСЛИ и МАКСЕСЛИ, с помощью формул массива, функции ДМИН или сводной таблицы.
Трехмерный поиск по нескольким листам (ВПР 3D)
Как реализовать поиск по трем измерениям, т.е. нахождение нужного листа в книге, а затем вывод содержимого ячейки с пересечения заданной строки и столбца.
Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Если вы когда-нибудь пытались сослаться на ячейку сводной таблицы, то должны были встречать эту функцию (от которой большинство шарахается). Зачем она нужна на самом деле и в каких случаях она может здорово помочь?
Преобразование формул в значения
6 способов преобразовать формулы в ячейках листа Excel в значения. Как для выделенного диапазона, так и сразу для всего листа или даже целой книги.
Выборочные вычисления по одному или нескольким критериям
Несколько способов решить одну из наиболее распространенных задач при работе в Microsoft Excel — вычислить итоги (сумму, среднее, количество и т.
д.) только для тех строк в таблице, которые удовлетворяют заданному условию или набору из нескольких условий.
Поиск ближайшего числа
Как проверить попадание параметра в один из заданных диапазонов и, при этом, обойтись без громоздких вложенных проверок с помощью кучи функций ЕСЛИ (IF)?
Использование функции ВПР (VLOOKUP) для подстановки значений
Одна из самых мощных и красивых функций в Excel — функция ВПР (VLOOKUP), позволяющая автоматически находить нужные значения в таблице. О том, как ее правильно использовать — просто и понятно.
Вычисление возраста или стажа функцией РАЗНДАТ (DATEDIF)
Как точно вычислить возраст/стаж человека (или любой другой интервал времени между началом и концом) в полных годах, месяцах или днях с помощью недокументированной функции Excel РАЗНДАТ (DATEDIFF).
3 способа склеить текст из нескольких ячеек
Как собрать текст из нескольких ячеек в одну с помощью функций СЦЕП, СЦЕПИТЬ, ОБЪЕДИНИТЬ и объединять ячейки без потери текста всех ячеек кроме верхней левой специальным макросом.
Номер недели по дате функцией НОМНЕДЕЛИ
Как определить номер рабочей недели для любой заданной даты (по ГОСТ, по ISO и т.д.) с помощью формул и функций НОМНЕДЕЛИ (WEEKNUM) и НОМНЕДЕЛИ.ISO (WEEKNUM.ISO)
Многоразовый ВПР (VLOOKUP)
Как извлечь ВСЕ данные из таблицы по заданному критерию. Функция ВПР (VLOOKUP) находит значение только по первому совпадению, а нужно вытащить все. Поможет хитрая формула массива!
Подробнее…
Конвертирование величин функцией ПРЕОБР (CONVERT)
Сколько грамм в двух унциях и дюймов в пяти метрах? Сколько минут в неделе и грамм в столовой ложке? Статья о том как использовать малоизвестную функцию ПРЕОБР (CONVERT) для конвертации из одной системы мер в другую в Excel.
ВПР (VLOOKUP) с учетом регистра
Как при помощи формулы массива сделать аналог функции ВПР (VLOOKUP), который при поиске будет учитывать регистр и различать строчные и прописные символы.
Двумерный поиск в таблице (ВПР 2D)
Как искать и выбирать нужные данные из двумерной таблицы, т.е. производить выборку не по одному параметру (как функции ВПР или ГПР), а по двум сразу.
Функция СЕГОДНЯ в Microsoft Excel
Функция СЕГОДНЯ – одна из самых простых в Excel и даже не имеет аргументов. Ее предназначение – вывод в ячейке сегодняшней даты, то есть той, которая установлена непосредственно в системе. Функцию можно комбинировать с другими или использовать как самостоятельную.
Применение функции СЕГОДНЯ в Excel
Разберемся с тем, как работает функция СЕГОДНЯ, с чем не должно возникнуть трудностей даже у начинающих пользователей Excel. Введите в пустой клетке =СЕГОДНЯ() и нажмите Enter для применения. Вы увидите, что на экране появилась текущая дата в стандартном формате ячейки.
Если же числа не соответствуют дате, понадобится изменить формат ячейки на «Дата» через раздел «Число» на главной вкладке программы.
Далее рассмотрю несколько простых примеров того, как можно комбинировать СЕГОДНЯ с другими функциями в Excel. Например, вы можете вычислить возраст человека, зная его год рождения, для чего введите =ГОД(СЕГОДНЯ())-2000, где 2000 – тот самый год. Вместо этого числа можно подставить номер ячейки, где стоит год рождения. Как результат вы увидите текущий возраст человека.
Отмечу, что при подобном и некоторых других комбинированиях формат ячейки должен быть «Общий», иначе результат не будет соответствовать действительности.
Можно узнать, какое число будет через несколько дней, для чего к СЕГОДНЯ нужно добавить указанное количество дней через +, но в этом случае формат ячейки должен остаться «Дата», ведь вас интересует точный результат, включая день, месяц и год. Точно так же работает и вычитание указанного количества дней от текущей даты.
Если нужно вывести только сегодняшний день, используйте строку =ДЕНЬ(СЕГОДНЯ()), а для месяца =МЕСЯЦ(СЕГОДНЯ()), формат ячейки сохраняйте общим, ведь необходимо отобразить только одно число.
Теперь разберу более сложный вариант комбинирования функций, например, сколько дней пройдет от сегодняшнего дня до указанной даты. В этом случае дату нужно указывать как текст, поэтому комбинировать ее с другими значениями в ячейках не получится. Введите =ДАТАЗНАЧ(«01.01.2000»)-СЕГОДНЯ(), где указанную дату замените на свою.
Итогом вычисления функции вы увидите, через сколько дней наступит указанная дата. Так вы можете вести собственный календарь или заполнять различные расписания.
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Подписаться
Настройка функции СЕГОДНЯ
С использованием рассматриваемой функции все понятно, теперь давайте немного остановимся на ее настройках.
Для начала можно затронуть тему изменения формата отображения даты путем настройки ячеек. Для этого выделите их необходимое количество и щелкните правой кнопкой мыши.
-
Из появившегося контекстного меню выберите пункт «Формат ячеек».
-
Выберите числовой формат «Дата» и ознакомьтесь с доступными типами. Среди них вы запросто найдете подходящий.
-
После применений изменений вы увидите, что в выделенных ячейках тип даты поменялся, но настройки не коснулись других клеток, которые не попали в выделение.
Следующая настройка – автоматическое вычисление в книге. Дело в том, что если эта настройка отключена, сегодняшняя дата всегда будет оставаться такой же, как и в день создания самой функции. Автоматическое вычисление пересчитает все значения и отобразит текущую дату.
-
Для изменения настроек откройте вкладку «Файл» и перейдите в «Параметры».
-
В разделе «Формулы» установите маркер параметра вычислений в книге на «Автоматически».
-
Если не хотите, чтобы таблица обновлялась сама, отметьте «Вручную» и отключите пересчет при сохранении.
Учитывайте, что при ручном вычислении вам понадобится выбрать ячейку с функцией и снова нажать Enter, чтобы данные обновились.
В этой статье, помимо функции СЕГОДНЯ, были разобраны и другие операторы работы с датой. Используйте их все по необходимости и комбинируйте между собой, чтобы упростить взаимодействие с книгой и всегда видеть на экране правильный день, месяц и год.
ФункцияИ в Excel — формула, примеры, как использовать?
Функция И в Excel — это логическая функция, которая проверяет несколько условий и возвращает «истина» или «ложь» в зависимости от того, выполнены они или нет.
Формула функции И: «=И(логический1;[логический2]…)», где «логический1» — это первое условие для оценки. Вы можете использовать это изображение на своем веб-сайте, в шаблонах и т. д. Пожалуйста, предоставьте нам ссылку на авторство. Как указать авторство? Ссылка на статью должна быть гиперссылкой
Например:
Источник: Функция И в Excel (wallstreetmojo.com)
Содержание
- Функция И в Excel
- Синтаксис функции И
- Характеристики функции И
- Выход1 функции И
- Как использовать функцию И в Excel?
- Пример №1 — функция И
- Пример №2 — функция И с вложенной функцией ЕСЛИ
- Пример №3 — функция И с вложенной функцией ЕСЛИ
- Гнездование и функция в Excel
- Пример № 4 — Недреянный и функция
- Ограничения и функции
- Часто задаваемые вопросы
- Рекомендуемые статьи
Синтаксис Синтаксиса и функция
9003 следующим образом:Функция принимает следующие аргументы:
- Логическая 1: Это первое условие или логическое значение для оценки.
- Логический 2: Это второе условие или логическое значение для оценки.
«Логическая 1» является обязательным аргументом, а «логическая 2» — необязательным аргументом.
Характеристики функции И
- Возвращает «истина», если все условия или логические значения оцениваются как «истина».
- Возвращает «ложь», если любое из условий или логических значений оценивается как ложное.
- Может иметь больше логических значений в зависимости от ситуации и требований.
- Он обрабатывает нулевое значение как «ложь», а все ненулевые значения как «истина» при оценке чисел.
- Игнорирует пустые ячейки, указанные в качестве аргумента.
- Часто используется в сочетании с другими функциями ExcelФункции ExcelФункции Excel помогают пользователям экономить время и вести обширные рабочие листы. Существует более 100 функций Excel, классифицированных как финансовые, логические, текстовые, даты и время, поиск и справочная информация, математические, статистические и информационные функции. Подробнее, такие как ЕСЛИ, ИЛИ и т. д.
Подробнее, такие как ЕСЛИ, ИЛИ и т. д.Вывод функции И
Вывод в различных ситуациях дается следующим образом:
Вывод при оценке чисел дается следующим образом:
Как использовать функцию И в Excel?
Использовать функцию И легко. Давайте разберемся с его работой на нескольких примерах.
Вы можете скачать этот шаблон Excel функции И здесь – Шаблон Excel функции И
Пример №1 – Функция И
В игре три уровня и десять игроков. Чтобы стать победителем, игрок должен пройти все три уровня. Игрок проигрывает, если он / она терпит неудачу на любом из трех уровней.
Результаты игроков на разных уровнях приведены в следующей таблице. Мы обязаны определить победителя.
Применяем формулу AND в столбце E.
Результат формулы появляется в столбце E.
Игрок 2 и игрок 10 прошли все уровни. Поскольку все логические условия для этих двух игроков соблюдены, функция И дает результат «истина».
Остальные игроки не смогли пройти все три уровня.
Если какой-либо из уровней не очищен, функция И возвращает «ложь».
Пример #2. Функция И с вложенной функцией ЕСЛИ
Рассмотрим следующий пример.
У нас есть оценки (в процентах) десяти учеников школы. Мы должны определить оценку, полученную каждым студентом в соответствии с заданными критериями.
Учащийся получает «А+», если он/она набирает более 90%. Если процент больше или равен 80%, но меньше или равен 90%, присваивается оценка «А».
Если процент меньше 40%, учащийся не проходит. Аналогичным образом, оценки для различных процентов приведены в следующей таблице.
Применим следующую формулу.
«=ЕСЛИ(B2>90″,A+»,ЕСЛИ(И(B2<=90,B2>80)»,A»,ЕСЛИ(И(B2<=80,B2>75)»,B+», ЕСЛИ(И(B2<=75,B2>70),»B»,ЕСЛИ(И(B2<=70,B2>60)»,C+»,ЕСЛИ(И(B2<=60,B2>50), ”C”,IF(AND(B2<=50,B2>40)”,D”,”FAIL”)))))))”
Мы используем вложенную функцию IFВложенная функция IFВ Excel вложенная функция if означает использование другая логическая или условная функция с функцией if для проверки нескольких условий.
Например, если необходимо проверить два условия, мы можем использовать логические функции И или ИЛИ, в зависимости от ситуации, или мы можем использовать другие условные функции, чтобы проверить еще больше условий, если внутри одного условия. Подробнее с несколькими функциями И. для подсчета оценок. Последний позволяет тестировать два условия вместе.
Синтаксис функции ЕСЛИ выглядит следующим образом:
«=ЕСЛИ(логическая_проверка,[значение_если_истина],[значение_если_ложь])»
Функция ЕСЛИ возвращает «истина», если условие выполнено, иначе возвращает «ложь». ”
Первый логический тест — «B2>90». Если это условие «верно», присваивается оценка «А+». Если это условие «ложно», функция ЕСЛИ оценивает следующее условие.
Следующий логический тестЛогический тестЛогический тест в Excel приводит к аналитическому результату, либо истинному, либо ложному. Оператор равенства «=» является наиболее часто используемой логической проверкой. Подробнее: «B2<=90, B2>80».
Если это условие «верно», присваивается оценка «А». Если это условие «ложно», оценивается следующее утверждение. Точно так же функция ЕСЛИ проверяет каждое условие, указанное в формуле.
Последний логический тест — «B2<=50, B2>40». Если это условие «верно», присваивается оценка «D», в противном случае учащийся не сдается.
Применяем формулу ко всем категориям учащихся, как показано на следующем изображении.
Вывод формулы показан на следующем изображении.
Пример № 3. Функция И с вложенной функцией ЕСЛИ
Имена сотрудников и доход от продаж Доход от продаж Доход от продаж относится к доходу, полученному любым хозяйствующим субъектом путем продажи своих товаров или предоставления своих услуг в ходе своей обычной деятельности. Об этом сообщается ежегодно, ежеквартально или ежемесячно, в зависимости от случая, в отчете о прибылях и убытках / отчете о прибылях и убытках коммерческого предприятия. Более подробная информация, созданная ими для организации, показана на следующем изображении.
Каждому сотруднику предоставляется денежное вознаграждение в зависимости от общего объема продаж, сделанного им.
Мы должны рассчитать поощрения всех сотрудников на основе их результатов.
Критерии стимулирования, которым следует организация, приведены в следующей таблице.
Применим следующую формулу.
«=ЕСЛИ(И(B2>=3000),400,ЕСЛИ(И(B2>=2000,B2<3000),200,ЕСЛИ(И(B2>=1500,B2<2000),150,ЕСЛИ( AND(B2>=1000,B2<1500),100,0))))”
Мы используем несколько ЕСЛИ Несколько ЕСЛИ В Excel несколько условий ЕСЛИ — это операторы ЕСЛИ, содержащиеся в другом операторе ЕСЛИ. Они используются для проверки нескольких условий одновременно и возвращают разные значения. Дополнительные операторы ЕСЛИ могут быть включены в аргументы «значение, если правда» и «значение, если ложь» стандартной формулы ЕСЛИ. Подробнее и несколько функций И для расчета поощрений, полученных всеми сотрудниками, как показано в следующей таблице.
Роман получает доход от продаж в размере 3000 долларов.
Таким образом, он получает поощрительную сумму в размере 400 долларов.
Доход Дэвида и Тома составляет 500 и 600 долларов соответственно. Чтобы иметь право на поощрение, требуется минимальный объем продаж в размере 1000 долларов США. Следовательно, они не получают никакого стимула.
Вложение функции И в Excel
Вложенная функция означает использование функции внутри другой функции Excel. В Excel допускается вложенность функций до 64 уровней.
Пример № 4. Вложенная функция И
У нас есть список кандидатов, желающих вступить в армию при соблюдении определенных условий. Критерии приемлемости указаны ниже:
- Возраст должен быть больше или равен 18, но меньше 35 лет.
- Рост должен быть больше 167 см.
- Зрение должно быть нормальным.
- Кандидат должен выполнить долгосрочную задачу.
Нам нужно найти кандидатов, которые имеют право служить в армии.
Чтобы оценить кандидатов по заданным параметрам, мы используем вложенную функцию И.
Применим следующую формулу.
«=И(B2=»Нормально»,C2>167,D2=«Успешно»,И(E2>=18,E2<35))»
Мы одновременно оцениваем несколько логических условий. Мы также проверяем, находится ли возраст в пределах установленного предела или нет. Итак, мы используем функцию И внутри другой функции И.
Вывод формулы показан на следующем изображении.
Кандидаты Ральф, Алекс и Скотт проходят критерии отбора. Следовательно, их вывод о приемлемости (в столбце F) является «верным». Остальные кандидаты не имеют права служить в армии.
Ограничения функции И
Ниже перечислены ограничения:
- В Excel 2007 и более поздних версиях функция И может проверять до 255 аргументов при условии, что длина формулы не превышает 8 162 символов.
- В Excel 2003 и предыдущих версиях функция И может проверять до 30 аргументов, если длина формулы не превышает 1024 символов.
- Функция И возвращает «#ЗНАЧ! ошибка#ЗНАЧ! Ошибка № ЗНАЧЕНИЕ! Ошибка в Excel означает, что в справочной ячейке пользователь либо ввел неверную формулу, либо использовал неправильный тип данных (в основном числовые данные). Иногда трудно определить тип ошибки, стоящей за этой ошибкой, если логические условия передаются в виде текста или если ни один из аргументов не дает логического значения.
- Функция И возвращает «#ЗНАЧ! ошибка», если все предоставленные аргументы являются пустыми ячейками.
Иногда трудно определить тип ошибки, стоящей за этой ошибкой, если логические условия передаются в виде текста или если ни один из аргументов не дает логического значения.На следующих двух изображениях показаны выходные данные функции И, когда в качестве аргумента предоставляется пустая ячейка и текстовая строка.
Часто задаваемые вопросы
#1 — Когда следует использовать функцию И Excel
Функция ИЛИ помогает сравнить два значения или оператора. Функция И объединяется с функцией ИЛИ по следующим формулам:
Функция используется в следующих ситуациях:
— Используется, когда необходимо проверить несколько условий одновременно.
— Используется, когда нужно убедиться, что все условия соблюдены.
— используется с функцией ЕСЛИ, когда необходимо проверить более одного условия.
— используется с функцией ЕСЛИФункция ЕСЛИФункция ЕСЛИ в Excel оценивает, выполняется ли заданное условие, и возвращает значение в зависимости от того, является ли результат «истинным» или «ложным». Это условная функция Excel, которая возвращает результат на основе выполнения или невыполнения заданных критериев.
читать далее, чтобы найти все значения между двумя заданными значениями.
— используется с функцией ИЛИФункция ИЛИФункция ИЛИ в Excel используется для проверки различных условий, что позволяет сравнивать два значения или утверждения в Excel. Если хотя бы один из аргументов или условий оценивается как ИСТИНА, он вернет ИСТИНА. Точно так же, если все аргументы или условия равны FALSE, он вернет FASLE.Подробнее для сравнения значений.
#2 – Как функция И используется с функцией ИЛИ в Excel?
Функция ИЛИ помогает сравнить два значения или оператора. Функция И объединяется с функцией ИЛИ по следующим формулам:
«=И(ИЛИ(Условие1,Условие2),Условие3)»
«=И(ИЛИ(Условие1,Условие2),ИЛИ(Условие3,Условие4)»
«=ИЛИ(И(Условие1,Условие2),Условие3)»
«=ИЛИ(И(Условие1,Условие2),И(Условие3,Условие4))»
#3 – В чем разница между функциями И, ЕСЛИ и ИЛИ в Excel?
Разница между тремя функций формулируется следующим образом:
— Функция И помогает оценить несколько условий одновременно.
Она возвращает «истина», когда все условия истинны, иначе возвращает «ложь».0008 — Функция ЕСЛИ помогает сравнить значение с ожидаемым пользователем результатом. Он возвращает конкретные значения для «истинных» и «ложных» результатов.
— Функция ИЛИ помогает сравнить два значения или два оператора. Он возвращает «true», когда выполняется хотя бы одно из указанных условий. Он возвращает «ложь», если все логические значения оцениваются как ложные.
- Функция И проверяет несколько условий и возвращает «истина» или «ложь» в зависимости от того, выполнены они или нет.
- Функция И возвращает «истина», если все условия оцениваются как «истина», и возвращает «ложь», если какое-либо из условий оценивается как «ложь».
- Функция И обрабатывает нулевое значение как «ложь».
- Функция И может проверять до 255 аргументов в последних версиях Excel.
- Функция И возвращает «#ЗНАЧ! ошибка», если логические условия передаются в виде текста.
- Формула функции ЕСЛИ: «=ЕСЛИ(логическая_проверка,[значение_если_истина],[значение_если_ложь])».
Рекомендуемые статьи
Это руководство по функции И в Excel. Здесь мы обсуждаем, как использовать формулу И в Excel вместе с примерами и загружаемыми шаблонами Excel. Вы также можете посмотреть на эти полезные функции в Excel —
- Сводная таблица Excel Добавить столбецСводная таблица Excel Добавить столбецДобавить столбец сводной таблицы помогает добавить новый столбец в сводную таблицу.Подробнее числа из одной системы измерения в другую систему измерения.Подробнее
- Тип данных VBA Boolean Тип данных VBA BooleanBoolean — это встроенный тип данных в VBA, используемый для логических ссылок или логических переменных. Значение, которое содержит этот тип данных, равно TRUE или FALSE и используется для логического сравнения. Объявление этого типа данных аналогично всем другим типам данных.Подробнее
- Формула ЕСЛИ И в ExcelФормула ЕСЛИ И в ExcelФормула ЕСЛИ И в Excel представляет собой комбинацию двух различных логических функций, часто вложенных друг в друга, что позволяет пользователю оценивать несколько условий с помощью функций И. Основываясь на выводе функции И, функция ЕСЛИ возвращает значение «истина» или «ложь» соответственно.
читать далее
Основываясь на выводе функции И, функция ЕСЛИ возвращает значение «истина» или «ложь» соответственно.
читать далееИ, ИЛИ, исключающее ИЛИ и НЕ
В учебнике объясняется сущность логических функций Excel И, ИЛИ, исключающее ИЛИ и НЕ, а также приводятся примеры формул, демонстрирующие их распространенное и изобретательское использование.
На прошлой неделе мы познакомились с логическими операторами Excel, которые используются для сравнения данных в разных ячейках. Сегодня вы увидите, как расширить использование логических операторов и создать более сложные тесты для выполнения более сложных вычислений. В этом вам помогут логические функции Excel, такие как AND, OR, XOR и NOT.
- Логические функции Excel — обзор
- Excel И функция
- Функция ИЛИ в Excel
- Excel функция XOR
- НЕ работает в Excel
Логические функции Excel — обзор
Microsoft Excel предоставляет 4 логические функции для работы с логическими значениями.
Функции И, ИЛИ, исключающее ИЛИ и НЕ. Вы используете эти функции, когда хотите выполнить более одного сравнения в своей формуле или проверить несколько условий вместо одного. Как и логические операторы, логические функции Excel возвращают либо ИСТИНА, либо ЛОЖЬ при оценке их аргументов.
В следующей таблице приведены краткие сведения о том, что делает каждая логическая функция, чтобы помочь вам выбрать правильную формулу для конкретной задачи.
| Функция | Описание | Пример формулы | Формула Описание |
| И | Возвращает ИСТИНА, если все аргументы оцениваются как ИСТИНА. | =И(A2>=10, B2<5) | Формула возвращает ИСТИНА, если значение в ячейке A2 больше или равно 10, а значение в ячейке B2 меньше 5, в противном случае — ЛОЖЬ. |
| ИЛИ | Возвращает ИСТИНА, если любой аргумент имеет значение ИСТИНА. | =ИЛИ(A2>=10, B2<5) | Формула возвращает значение ИСТИНА, если A2 больше или равно 10 или B2 меньше 5, или выполняются оба условия. Если ни одно из условий не выполнено, формула возвращает ЛОЖЬ. |
| Исключающее ИЛИ | Возвращает логическое исключающее ИЛИ всех аргументов. | =исключающее ИЛИ(A2>=10, B2<5) | Формула возвращает значение ИСТИНА, если либо A2 больше или равно 10, либо B2 меньше 5. Если ни одно из условий не выполняется или выполняются оба условия, формула возвращает значение FALSE. |
| НЕ | Возвращает инвертированное логическое значение своего аргумента. т.е. Если аргумент ЛОЖЬ, то возвращается ИСТИНА и наоборот. | = НЕ(А2>=10) | Формула возвращает ЛОЖЬ, если значение в ячейке A1 больше или равно 10; ИСТИНА в противном случае. |
В дополнение к четырем логическим функциям, описанным выше, Microsoft Excel предоставляет 3 «условные» функции — ЕСЛИ, ЕСЛИОШИБКА и ЕСЛИНА.
Логические функции Excel — факты и цифры
- В аргументах логических функций можно использовать ссылки на ячейки, числовые и текстовые значения, логические значения, операторы сравнения и другие функции Excel. Однако все аргументы должны оцениваться как логические значения TRUE или FALSE или ссылки или массивы, содержащие логические значения.
- Если аргумент логической функции содержит любые пустых ячеек , такие значения игнорируются. Если все аргументы являются пустыми ячейками, формула возвращает #ЗНАЧ! ошибка.
- Если аргумент логической функции содержит числа, то ноль оценивается как ЛОЖЬ, а все остальные числа, включая отрицательные числа, оцениваются как ИСТИНА. Например, если ячейки A1:A5 содержат числа, формула =И(A1:A5) вернет значение ИСТИНА, если ни одна из ячеек не содержит 0, и ЛОЖЬ в противном случае.
- Логическая функция возвращает #ЗНАЧ! ошибка, если ни один из аргументов не имеет логических значений.
- Логическая функция возвращает #ИМЯ? ошибка, если вы допустили ошибку в имени функции или попытались использовать функцию в более ранней версии Excel, которая ее не поддерживает. Например, функцию XOR можно использовать только в Excel 2016 и 2013.
- В Excel 2007 и более поздних версиях в логическую функцию можно включить до 255 аргументов при условии, что общая длина формулы не превышает 8 192 символов. В Excel 2003 и более ранних версиях вы можете указать до 30 аргументов, а общая длина вашей формулы не должна превышать 1024 символа.
Однако все аргументы должны оцениваться как логические значения TRUE или FALSE или ссылки или массивы, содержащие логические значения.
Использование функции И в Excel
Функция И является наиболее популярным членом семейства логических функций. Это удобно, когда вам нужно протестировать несколько условий и убедиться, что все они соблюдены. Технически функция И проверяет заданные вами условия и возвращает значение ИСТИНА, если все условия оцениваются как ИСТИНА, в противном случае — ЛОЖЬ.
Синтаксис функции И в Excel следующий:
И(логический1, [логический2], …)
Где логическое — это условие, которое вы хотите проверить, которое может быть оценено как ИСТИНА или ЛОЖЬ. Первое условие (логическое1) является обязательным, последующие условия необязательны.
А теперь давайте рассмотрим несколько примеров формул, которые демонстрируют, как использовать функции И в формулах Excel.
| Формула | Описание |
=И(A2="Бананы", B2>C2) | Возвращает TRUE, если A2 содержит «бананы», а B2 больше, чем C2, в противном случае FALSE. |
=И(B2>20, B2=C2) | Возвращает TRUE, если B2 больше 20 и B2 равно C2, иначе FALSE. |
=И(A2="Бананы", B2>=30, B2>C2) | Возвращает ИСТИНА, если A2 содержит «бананы», B2 больше или равно 30 и B2 больше C2, в противном случае FALSE. |
Функция И в Excel - общее использование
Сама по себе функция И в Excel не очень интересна и имеет узкую полезность. Но в сочетании с другими функциями Excel И может значительно расширить возможности ваших рабочих листов.
Одним из наиболее распространенных применений функции И в Excel является аргумент логическая_проверка функции ЕСЛИ для проверки нескольких условий вместо одного.
Например, вы можете вложить любую из приведенных выше функций И внутрь функции ЕСЛИ и получить результат, аналогичный этому:
=ЕСЛИ(И(A2="Бананы", B2>C2), "Хорошо", "Плохо")
Дополнительные примеры формул ЕСЛИ/И см. в его руководстве: Функция ЕСЛИ в Excel с несколькими И условия.
Формула Excel для условия МЕЖДУ
Если вам нужно создать в Excel формулу между, которая выбирает все значения между заданными двумя значениями, обычно используется функция ЕСЛИ с И в логическом тесте.
Например, у вас есть 3 значения в столбцах A, B и C, и вы хотите знать, находится ли значение в столбце A между значениями B и C. Чтобы составить такую формулу, достаточно функции ЕСЛИ с вложенным И и парой операторов сравнения:
Формула для проверки, находится ли X между Y и Z, включительно:
=ЕСЛИ(И(A2>=B2,A2<=C2),"Да", "Нет")
Формула для проверки, находится ли X находится между Y и Z, не включительно:
=ЕСЛИ(И(A2>B2, A2
Как показано на снимке экрана выше, формула отлично работает для все типы данных - числа, даты и текстовые значения.
При сравнении текстовых значений формула проверяет их посимвольно в алфавитном порядке. Например, указано, что Яблоки не находятся между Абрикос и Бананы , потому что вторая буква «р» в Яблоки стоит перед «r» в Абрикос . Дополнительные сведения см. в разделе Использование операторов сравнения Excel с текстовыми значениями.
Как видите, формула ЕСЛИ/И проста, быстра и практически универсальна. Я говорю «почти», потому что это не относится к одному сценарию. Из приведенной выше формулы следует, что значение в столбце B меньше, чем в столбце C, т. е. столбец B всегда содержит значение нижней границы, а C — значение верхней границы. По этой причине формула возвращает " № "для строки 6, где A6 имеет 12, B6 - 15 и C6 - 3, а также для строки 8, где A8 - 24 ноября, B8 - 26 декабря и C8 - 21 октября.
Но что, если Вы хотите, чтобы формула между интервалами работала правильно независимо от того, где находятся значения нижней и верхней границы? В этом случае используйте функцию МЕДИАНА Excel, которая возвращает медиану заданных чисел (т.
е. число в середине набора
Итак, если заменить И в логическом тесте функции ЕСЛИ на МЕДИАНУ, формула будет выглядеть так:
=ЕСЛИ(A2=МЕДИАНА(A2:C2),"Да","Нет")
И вы получите следующие результаты:
Как видите, функция МЕДИАНА отлично работает для чисел и дат , но возвращает #ЧИСЛО! ошибка для текстовых значений. Увы, никто не идеален 🙂
Если вам нужна идеальная формула между, которая работает для текстовых значений, а также для чисел и дат, вам придется построить более сложный логический текст, используя функции И / ИЛИ, как это :
=ЕСЛИ(ИЛИ(И(A2>B2, A2
Использование функции ИЛИ в Excel
Как а также И, функция Excel ИЛИ — это базовая логическая функция, которая используется для сравнения двух значений или утверждений. Разница в том, что функция ИЛИ возвращает ИСТИНА, если хотя бы один из аргументов принимает значение ИСТИНА, и возвращает ЛОЖЬ, если все аргументы равны ЛОЖЬ.
Функция ИЛИ доступна во всех версиях Excel 2016 - 2000.
Синтаксис функции ИЛИ Excel очень похож на И:
ИЛИ(логический1, [логический2], …)
Где логичный — это то, что вы хотите проверить, что может быть либо ИСТИНА, либо ЛОЖЬ. Первое логическое обязательно, дополнительные условия (до 255 в современных версиях Excel) необязательны.
А теперь давайте напишем несколько формул, чтобы вы почувствовали, как работает функция ИЛИ в Excel.
| Формула | Описание |
= ИЛИ (A2 = «бананы», A2 = «апельсины») | Возвращает ИСТИНА, если A2 содержит "бананы" или "апельсины", в противном случае - ЛОЖЬ. |
=ИЛИ(B2>=40, C2>=20) | Возвращает TRUE, если B2 больше или равно 40 или C2 больше или равно 20, в противном случае FALSE. |
=ИЛИ(B2=" ", C2="") | Возвращает TRUE, если либо B2, либо C2 пусты, либо и то, и другое, в противном случае FALSE. |
Как и функция И в Excel, операция ИЛИ широко используется для расширения возможностей других функций Excel, которые выполняют логические тесты, например. функция ЕСЛИ. Вот лишь пара примеров:
ЕСЛИ функция с вложенным ИЛИ
= ЕСЛИ(ИЛИ(B2>30, C2>20), "Хорошо", "Плохо")
Формула возвращает " Хорошо ", если число в ячейке B3 больше 30 или число в C2 больше 20, иначе " Bad ".
Excel Функции И/ИЛИ в одной формуле
Естественно, ничто не мешает вам использовать обе функции, И и ИЛИ, в одной формуле, если этого требует ваша бизнес-логика. Могут быть бесконечные вариации таких формул, которые сводятся к следующим основным шаблонам:
=И(ИЛИ(Вывод1, Вывод2), Вывод3)
=И(ИЛИ(Вывод1, Вывод2), ИЛИ(Вывод3, Вывод4)
=ИЛИ(И(Вывод1, Вывод2), Вывод3 )
=ИЛИ(И(Условие1,Условие2), И(Условие3,Условие4))
Например, если вы хотите узнать, какие партии бананов и апельсинов распроданы, т.
е. номер "В наличии" ( столбец B) равен числу «Продано» (столбец C), следующая формула ИЛИ/И может быстро показать это вам:
=ИЛИ(И(A2="бананы", B2=C2), И(A2="апельсины", B2=C2))
Функция ИЛИ в условном форматировании Excel
=ИЛИ($B2 ="", $C2="")
Правило с приведенной выше формулой ИЛИ выделяет строки, содержащие пустую ячейку либо в столбце B, либо в столбце C, либо в обоих столбцах.
Дополнительные сведения о формулах условного форматирования см. в следующих статьях:
- Формулы условного форматирования Excel
- Изменение цвета строки на основе значения ячейки
- Изменение цвета ячейки на основе значения другой ячейки
- Как выделить каждую вторую строку в Excel
Использование функции XOR в Excel
В Excel 2013 Microsoft представила функцию XOR, которая является логической функцией Исключающее ИЛИ . Этот термин определенно знаком тем из вас, кто хоть немного знаком с каким-либо языком программирования или информатикой в целом.
Для тех, кто этого не делает, концепция «Исключающее ИЛИ» может быть немного трудной для понимания поначалу, но, надеюсь, приведенное ниже объяснение, иллюстрированное примерами формул, поможет.
Синтаксис функции XOR идентичен синтаксис OR:
XOR(логический1, [логический2],…)
Требуется первый логический оператор (логический 1), дополнительные логические значения необязательны. Вы можете проверить до 254 условий в одной формуле, и это могут быть логические значения, массивы или ссылки, которые оцениваются как ИСТИНА или ЛОЖЬ.
В простейшей версии формула XOR содержит всего 2 логических оператора и возвращает:
- ИСТИНА, если любой из аргументов имеет значение ИСТИНА.
- ЛОЖЬ, если оба аргумента ИСТИНА или ни один из них не ИСТИНА.
Это может быть легче понять из примеров формул:
| Формула | Результат | Описание |
=исключающее ИЛИ(1>0, 2<1) | ИСТИНА | Возвращает ИСТИНА, поскольку первый аргумент равен ИСТИНА, а второй -й аргумент — ЛОЖЬ. |
=исключающее ИЛИ(1<0, 2<1) | ЛОЖЬ | Возвращает ЛОЖЬ, так как оба аргумента равны ЛОЖЬ. |
=исключающее ИЛИ(1>0, 2>1) | ЛОЖЬ | Возвращает ЛОЖЬ, поскольку оба аргумента имеют значение ИСТИНА. |
При добавлении большего количества логических операторов функция XOR в Excel возвращает:
- ИСТИНА, если нечетное число аргументов оценивается как ИСТИНА;
- ЛОЖЬ, если общее количество утверждений ИСТИНА четно, или если все утверждения ложны.
На приведенном ниже снимке экрана показана точка:
Если вы не знаете, как можно применить функцию Excel XOR к реальному сценарию, рассмотрите следующий пример. Предположим, у вас есть таблица участников и их результатов за первые 2 игры. Вы хотите узнать, кто из плательщиков должен сыграть в игру 3 rd , исходя из следующих условий:
- Участники, выигравшие игру 1 и 2, автоматически переходят в следующий раунд и не должны играть в игру 3.
- Участники, проигравшие обе первые игры, выбывают из игры и не участвуют в игре 3.
- Участники, выигравшие Игру 1 или Игру 2, должны сыграть Игру 3, чтобы определить, кто пройдет в следующий раунд, а кто нет.
Простая формула XOR работает именно так, как нам нужно:
=XOR(B2="Выиграл", C2="Выиграл")
И если вы вложите эту функцию XOR в логическую проверку формулы IF, вы получите еще более ощутимые результаты:
=ЕСЛИ(ИСКЛЮЧАЮЩЕЕ ИЛИ(B2="Выиграл", C2="Выиграл"), "Да", "Нет")
Использование функции НЕ в Excel
Функция НЕ является одной из самых простых функций Excel в термины синтаксиса:
НЕ (логическое)
Вы используете функцию НЕ в Excel, чтобы инвертировать значение ее аргумента. Другими словами, если логический результат равен ЛОЖЬ, функция НЕ возвращает значение ИСТИНА, и наоборот. Например, обе приведенные ниже формулы возвращают ЛОЖЬ:
= НЕ(ИСТИНА)
=НЕ(2*2=4)
Зачем хотеть получать такие нелепые результаты? В некоторых случаях вам может быть интереснее узнать, когда определенное условие не выполняется, чем когда оно выполняется.
Например, при просмотре списка одежды вы можете исключить какой-то цвет, который вам не подходит. Я не особенно люблю черный цвет, поэтому использую следующую формулу:
=НЕ(C2="черный")
Как обычно, в Microsoft Excel есть несколько способов сделать что-то, и того же результата можно добиться, используя оператор Not equal to: =C2<>"black".
Если вы хотите проверить несколько условий в одной формуле, вы можете использовать НЕ в сочетании с функцией И или ИЛИ. Например, если вы хотите исключить черный и белый цвета, формула будет выглядеть так:
= НЕ(ИЛИ(C2="черный", C2="белый"))
иметь черное пальто, в то время как черная куртка или шуба на спине могут быть рассмотрены, вы должны использовать НЕ в сочетании с функцией Excel И:
= НЕ(И(C2="черный", B2="пальто") )
Еще одно распространенное использование функции НЕ в Excel — изменение поведения какой-либо другой функции. Например, вы можете комбинировать функции НЕ и ЕПУСТО, чтобы создать формулу НЕПУСТЬ, которой нет в Microsoft Excel.