Формула если в excel с датами
Из этого обзора вы узнаете несколько быстрых и простых способов подсчета, сколько дней проходит между двумя датами в Excel.
Вам интересно, сколько составляет разница в днях, месяцах и годах между двумя датами? Может быть, вам нужно знать количество дней между сегодняшним днем и какой-либо точкой в прошлом или будущем?
Какова бы ни была ваша проблема, один из приведенных ниже примеров в Excel наверняка подскажет вам решение.
Простой калькулятор дней.
Если вы ищете быстрый ответ, просто укажите две даты в соответствующих ячейках, и наш калькулятор покажет вам, сколько суток между ними:
Хотите узнать формулу, которая рассчитала эти результаты? Это так же просто, как =B3-B2:)
Обратите внимание, что нужно четко знать, что именно вы хотите подсчитать: «дни включительно» или «дни между».
Кроме того, прежде чем выполнять операции с датой-временем, нужно установить правильный формат в ячейках вашей таблицы. Чаще всего Эксель сам подбирает нужный формат представления данных, но все же лучше подстраховаться.
Для этого выберите клетку или диапазон ячеек с числами, которые вы хотите представить как даты, и нажмите комбинацию клавиш Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». На вкладке «Числовые форматы» в пункте «Дата», выберите подходящий для вас внешний вид в разделе «Тип» и нажмите кнопку «ОК».
Далее вы найдете подробное объяснение того, как работает эта формула, и узнаете несколько других способов вычисления.
Как рассчитать разницу в днях?
Самый простой способ рассчитать дни между датами в Эксель - вычесть одну из другой:
= Более новая - Более старая
Как мы уже сказали ранее, чтобы узнать, сколько времени пройдет в периоде между ячейками В3 и B2, используйте следующую формулу:
где В2 - более ранняя, а B3 - более.
Результатом подсчета является целое число, которое представляет количество дней внутри временного интервала.
Однако, будьте здесь внимательны: при подобном подсчете отработанных дней в том случае, если человек работал и в первый, и в последний день, то 1 день будет «потерян». Добавьте в вашу формулу +1 день.
Как работает эта формула?
Как вы, вероятно, знаете, Microsoft Excel хранит даты в виде порядковых номеров, начинающихся с 1 января 1900 года, которому соответствует номер 1. В этой системе 2 января 1900 года сохраняется как число 2, 3 января 1900 года как 3 и так далее. Таким образом, вычитая одну дату из другой, вы фактически вычитаете целые числа, представляющие их.
В нашем примере формула в C5 на самом деле вычитает 43892 (числовое значение 2 марта 2020г.) из 43900 (числовое значение 10 марта 2020г.) и возвращает результат 8 дней.
Положительный момент этого метода в том, что он отлично работает во всех случаях, независимо от того, какая дата старше, а какая новее. Если вы вычитаете более позднюю дату из более ранней, то формула просто возвращает разницу в виде отрицательного числа.
Рассчитать разницу между датами в Excel с помощью РАЗНДАТ (DATEDIF).
Другой способ — как в Экселе посчитать дни в заданном временном интервале — использование функции РАЗНДАТ (в английском варианте – DATEDIF), которая специально разработана для расчета разности во времени и представления её в различных единицах: дни, месяцы и годы.
А вот ее синтаксис:
дата_нач и дата_кон - две временных отметки, разницу между которыми необходимо рассчитать,
аргумент – указывает, как представить результат; может иметь одно из следующих значений:
- Y Число полных лет.
- M Число полных месяцев.
- D Число дней.
- MD Разница в днях (значения месяца и года не учитываются).
- YM Разница в месяцах (значения дня и года не учитываются).
- YD Разница в днях, игнорируя значения года.
Чтобы получить количество дней между двумя датами, укажите момент начала в первом аргументе, срок окончания во втором, и «d» в последнем третьем:
РАЗНДАТ(дата_нач; дата_кон; "d")
DATEDIF (start_date, end_date, "d")
Примечание. РАЗНДАТ (DATEDIF) - недокументированная функция, то есть ее нет в мастере функций в Excel. Вы не сможете ее вставить при помощи кнопки fx или меню Вставка — Функция. Чтобы создать формулу РАЗНДАТ в таблице Экселя, вам нужно вписать ее название руками и ввести все аргументы вручную, подсказок ввода не будет.
В ней вы можете указывать исходные данные различными способами, такими как:
- Ссылки на ячейки. Например, следующая формула вернет количество дней между A3 и B3:
Текстовые выражения. Excel понимает даты в различных форматах, например, «2 март 2020», «20.03.2020», «20/03/2020», «2020/03/20», «20-03-2020», «2020-03-20» и т.д.
Вот примеры расчета количества дней во временных промежутках:
Естественно, эти значения могут быть записаны и в ячейках, а формула на них будет ссылаться.
- Порядковые номера. Поскольку Microsoft Excel хранит каждую дату как порядковый номер, начинающийся с 1 января 1900 года, вы можете поместить числа, соответствующие датам, непосредственно в формулу. Хотя этот метод полностью поддерживается, все же он не является надежным, поскольку нумерация различается в разных компьютерных системах (Windows и MacOS).
В системе дат 1900 года следующая формула является еще одним способом вычисления месяцев по условиям из предыдущего примера (2 марта 2020 года и 10 марта 2020 года):
- Результаты выполнения других функций. Например, следующая формула подсчитывает, срок между сегодня (18 марта 2020г.) и 9 мая 2020 года.
1. Разница в днях.
В нашем примере формула расчета в D3 выглядит следующим образом:
Обратите также внимание, что при простом вычитании дат, которые также содержат время, мы получаем в результате дробное число. Так произошло в С5 и С6. Что делать с такими дробями – поговорим чуть позже. А вот время здесь просто игнорируется. В этом вы можете убедиться в диапазоне D5:I6.
Аналогичный результат можно получить формулами:
Время, то есть дробная часть, будет отброшено.
Кстати, если нужно подсчитать только рабочие дни, то следует использовать выражение
Более подробно о расчете рабочих дней читайте здесь.
2. Разница в полных месяцах.
Обращаемся к ячейке Е3:
Обратите внимание, выражение вернет количество полных месяцев. В нашем примере 13 месяцев и 7 дней «округлены» ровно до 13. И последний день интервала, как мы уже говорили, эта функция не учитывает. В частности,
возвращает результат 0, так как последний день не включается в расчет. Вы это видите в ячейке E7. Это также будет важно при расчете стажа и отработанного времени. Учитывайте эту особенность и просто добавляйте 1 дополнительный день!
Формула может быть заменена альтернативным выражением:
3. Разница в полных годах.
Формула в ячейке F3 вернет количество полных лет:
Опять же, считается только полный год, остальное отбрасывается.
Ну и альтернативный способ расчета —
4. Разница в полных месяцах без учета лет.
Используем в I3 аргумент "YM".
Второй параметр и конечная точка у нас – 10.04.2020 г. Поскольку годы мы здесь игнорируем, то год первого параметра также становится 2020. И мы теперь сравниваем 10.04.2020 и 2.03.2020г.
Результат – 1 полный месяц.
А вот если начало, предположим, будет 01.09.2015г.? Ведь ее нельзя модифицировать до 01.09.2020, поскольку тогда функция выдаст ошибку. И программа ее меняет на 01.09.2019. Результат здесь – 7 полных месяцев.
Я очень сомневаюсь, что кто-то будет проводить подобный расчет. Но он нам будет нужен, чтобы подсчитать количество дней, месяцев и лет между двумя точками. Об этом мы поговорим чуть ниже.
Ну и альтернативный расчет –
5. Разница в днях без учета месяцев и лет.
Здесь немного сложнее. Итак, в G3 пишем:
Разберем, как получен результат в G3. Поскольку для нас не важны месяц и год, мы в начальной дате меняем их на текущие. То есть вместо 02.03.2019 рассматриваем 02.04.2020. Сравниваем с 10.04.2020 и получаем 8 дней. Есть ли в таком результате смысл? Вряд ли. Поэтому и не рекомендуется применение этого аргумента как отдельного расчета.
Быть может, поэтому РАЗНДАТ и нет в списке официальных функций Excel.
6. Разница в днях без учета лет.
То же самое, что и в предыдущем аргументе. Считаем количество суток между двумя точками без учета лет.
Для начального момента — 02.03.2019 и конечного – 10.04.2020, разница в 39 дней выглядит явной ошибкой. Аналогично – между 01.09.2015 и 10.04.2020 совсем не 222 дня.
Следовательно, использование этого аргумента также не рекомендуем.
А зачем же всё это нужно? Ответ читайте в следующем разделе.
Как посчитать разницу в днях, месяцах и годах.
Чтобы подсчитать, сколько полных лет, месяцев и дней составляет разница "от" и "до" с использованием одной формулы, вы просто объединяете три функции в одно выражение:
Если вы не хотите отображать нулевые значения лет, месяцев и дней, то выражение можно изменить, добавив условие ЕСЛИ:
А вот еще один более сложный, но зато более универсальный вариант расчета разницы в днях, месяцах и годах с учетом склонений и падежей:
Выглядит угрожающе, но зато работает.
Подсчет с помощью функции ДНИ
У пользователей Excel 2013 и 2016 есть еще один удивительно простой способ посчитать количество дней во временном интервале — функция ДНИ.
Обратите внимание, что по сравнению с РАЗНДАТ, функция ДНИ требует указания дат в обратном порядке.
Итак, наше выражение принимает вид:
Как и обычное вычитание, оно возвращает разницу в виде положительного или отрицательного числа, в зависимости от того, больше или меньше срок окончания, чем начало:
Думаю, в большинстве случаев можно использовать простое вычитание. Возможно, при этом только нужно будет изменить формат ячейки, убрав дробную часть.
Как рассчитать количество дней между сегодня и другой датой.
Для этого расчета вы можете использовать любую из формул, описанных выше, и применить функцию СЕГОДНЯ (TODAY в английском варианте) в качестве одного из аргументов.
Чтобы рассчитать количество прошедших дней с определенного момента, то есть между прошлым и сегодняшним днем:
=СЕГОДНЯ() – Дата_в_прошлом
Для подсчета количества дней, оставшихся до наступления какого-то события, то есть между будущим временем и сегодняшним днем:
=Дата_в_будущем - СЕГОДНЯ()
В качестве примера, давайте посчитаем разницу между текущим моментом времени и более ранним - в A4 (сегодня 18 марта 2020г.):
А теперь давайте выясним, сколько осталось от сегодняшнего дня (18.03.2020г.) до более позднего срока:
Ну а если не менять выражение по сравнению с предыдущим расчетом, то просто получите отрицательное количество дней.
Мастер даты и времени - простой способ создавать формулы разницы дат в Excel
Как показано в первой части этого руководства, функция РАЗНДАТ (DATEDIF) — довольно универсальная, подходящая для самых разных целей. Однако есть один существенный недостаток — она не документирована Microsoft, то есть вы не найдете РАЗНДАТ в списке функций и не увидите всплывающих подсказок для аргументов, когда начнете вводить формулу в ячейку. Чтобы иметь возможность использовать функцию РАЗНДАТ в своих таблицах, вы должны запомнить ее синтаксис и ввести все аргументы вручную, что может занять много времени и привести к ошибкам, особенно у начинающих.
Надстройка Ultimate Suite радикально меняет это положение, поскольку там имеется мастер даты и времени, который может мгновенно создать практически любую формулу разницы дат. Если вы не уверены, какую формулу использовать, позвольте мастеру даты и времени вычислить количество дней между двумя датами за вас Вот каким образом это можно сделать:
- Выберите ячейку, в которую вы хотите вставить формулу.
- Перейдите на вкладку Ablebits Tools и нажмите кнопку Мастер даты и времени (Date & Time Wizard).
- Появится диалоговое окно мастера даты и времени, где вы переходите на вкладку «Разница (Difference)» и вводите необходимые данные для расчёта:
- Щелкните поле Дата 1 и выберите ячейку, содержащую первую дату.
- Щелкните поле Дата 2 и укажите ячейку со второй датой.
- Выберите желаемую единицу или комбинацию единиц времени из раскрывающегося меню «Разница в (Difference in)» . При этом программа позволяет предварительно просмотреть результат в поле и формулу в ячейке.
Инструмент может рассчитать разницу между двумя датами в 15 форматах:
- Годы
- Месяцы
- Недели
- Дни
- Годы + месяцы
- Годы + недели
- Годы + дни
- Месяцы + недели
- Месяцы + дни
- Недели + дни
- Годы + месяцы + недели
- Годы + месяцы + дни
- Годы + недели + дни
- Месяцы + недели + дни
- Годы + месяцы + недели + дни
- Если вас устраивает то, что вы увидели, нажмите кнопку «Вставить формулу (InsertFormula)», в противном случае попробуйте другие единицы измерения.
После того, как формула вставлена в выбранную ячейку, вы можете скопировать ее вниз по столбцу, как обычно дважды щелкнув или перетащив маркер заполнения.
К сожалению, метки даты и времени вставляются по-английски. Однако, поскольку надстройка формирует для вас не текст, а формулу расчета (что вы и видите на скриншоте), то откорректировать формулу и пользоваться в дальнейшем ее русской версией не слишком сложно.
Да и вряд ли вы самостоятельно напишете такую сложную конструкцию 😊
Для наиболее удобного представления результатов доступно еще несколько дополнительных опций:
- Исключить годы и / или месяцы из расчетов. (Годы и / или месяцы просто будут отброшены).
- Показывать или не показывать текстовые метки времени, такие как дни, месяцы, недели и годы .
- Показывать или не показывать нулевые единицы (0 месяцев, например) .
- Возвращает результаты как отрицательные значения, если Дата 1 (дата начала) больше, чем Дата 2 (дата окончания). Думаю, вы сталкивались с ситуацией, когда попытка вычесть из более ранней даты более позднюю приводила к ошибке. Здесь вы просто получите отрицательную разность.
Более подробная информация доступна здесь.
Преимущества использования мастера формул даты и времени
Помимо скорости и простоты, Мастер даты и времени предоставляет еще несколько преимуществ:
- В отличие от обычной формулы РАЗНДАТ, для формулы, созданной мастером, не важно, какая из двух дат меньше, а какая больше. Разница всегда рассчитывается идеально, даже если Дата 1 (дата начала) больше, чем Дата 2 (дата окончания).
- Мастер поддерживает все возможные единицы измерения (дни, недели, месяцы и годы) и позволяет вам выбирать подходящий вариант из 11 различных комбинаций этих единиц.
- Формулы, которые мастер создает для вас, являются обычными формулами Excel, поэтому вы можете редактировать, копировать или перемещать их как обычно. Вы также можете поделиться своими таблицами с другими людьми, и все формулы останутся на своих местах, даже если у кого-то нет Ultimate Suite в их Excel.
Если вам интересно протестировать этот калькулятор дат, а также открыть для себя еще 60 надстроек для упрощения работы и экономии времени в Excel, то можете загрузить пробную версию Ultimate Suite. Если вам понравились инструменты и вы решили купить лицензию, не пропустите это специальное предложение для читателей нашего сайта.
Думаю, теперь операции с датами стали для вас намного проще и понятнее. Если вы не нашли ответ на интересующий вас вопрос, ознакомьтесь с дополнительными материалами, ссылки на которые вы видите чуть ниже. Или напишите нам в комментариях – постараемся ответить.
Также рекомендуем статьи о работе с датами:
Как вывести месяц из даты - На примерах мы покажем, как получить месяц из даты в таблицах Excel, преобразовать число в его название и наоборот, а также многое другое. Думаю, вы уже знаете, что дата в…
Функция Excel СЕГОДНЯ — синтаксис и основные применения - Функция СЕГОДНЯ в Excel делает именно то, на что указывает ее имя - возвращает сегодняшнюю дату. Синтаксис ее настолько прост, насколько это вообще возможно - у нее вообще нет аргументов. Всякий…
Как быстро вставить сегодняшнюю дату в Excel? - Это руководство показывает различные способы ввода дат в Excel. Узнайте, как вставить сегодняшнюю дату и время в виде статической метки времени или динамических значений, как автоматически заполнять столбец или строку…
Количество рабочих дней между двумя датами в Excel - Довольно распространенная задача: определить количество рабочих дней в период между двумя датами – это частный случай расчета числа дней, который мы уже рассматривали ранее. Тем не менее, в Excel для…
8 способов — как посчитать возраст в Excel по дате рождения. - В Excel нет специальной функции, чтобы посчитать возраст, однако существует несколько различных способов его определения на основе даты рождения. Вы узнаете несколько приемов, как рассчитать его в количестве полных лет,…
Как преобразовать текст в дату в Excel или извлечь ее из сложной фразы? - Сейчас вы научитесь использовать функции Excel для преобразования текста в дату. Вы также узнаете, как это сделать без использования формул. Поскольку Excel - не единственное приложение, которое вы используете, иногда…
Excel IF функция
Функция ЕСЛИ - одна из самых простых и полезных функций в книге Excel. Он выполняет простой логический тест, который зависит от результата сравнения, и возвращает одно значение, если результат ИСТИНА, или другое значение, если результат ЛОЖЬ.
Синтаксис:
Синтаксис функции ЕСЛИ в Excel:
Аргументы:
- logical_test : Необходимые. Это условие, которое вы хотите проверить.
- value_if_true : Необязательный. Конкретное значение, которое вы хотите вернуть, если результат логического_теста - ИСТИНА.
- value_if_false : Необязательный. Значение, которое вы хотите вернуть, если результат логического_теста ЛОЖЬ.
Заметки:
1. Если значение_если_истина опущено:
2. Если значение_если_ложь опущено:
Вернуть:
Проверка на конкретное условие, возвращает соответствующее значение, которое вы указали для ИСТИНА или ЛОЖЬ.
Примеры:
Пример 1: Использование простой функции ЕСЛИ для чисел
Например, предположим, что вы хотите протестировать список значений, если значение больше определенного значения 100, отображается текст «Хорошо», если нет, возвращается текст «Плохо».
Введите приведенную ниже формулу, и вы получите результат, который вам нужен.
Пример 2: Использование функции ЕСЛИ для текстовых значений
Случай 1: функция ЕСЛИ для текстовых значений без учета регистра:
Здесь у меня есть таблица со списком задач и статусом завершения, теперь я хочу знать, какие задачи нужно продолжить, а какие нет. Когда текст в столбце C будет заполнен, будет отображаться «Нет», в противном случае будет возвращено «Да».
Примените следующую формулу, теперь ячейка вернет «Нет», если текст в столбце C отображается как «завершено», независимо от того, в верхнем или нижнем регистре; если другой текст в столбце C, будет возвращено «Да». Смотрите скриншот:
Случай 2: функция ЕСЛИ для текстовых значений с учетом регистра:
Чтобы проверить текстовые значения с учетом регистра, вы должны объединить функцию ЕСЛИ с функцией ТОЧНЫЙ, примените формулу ниже, тогда будет распознан только текст с точным соответствием, и вы получите следующий результат, который хотите:
Случай 3: функция ЕСЛИ для текстовых значений с частичным соответствием:
Иногда вам нужно проверить значения ячеек на основе частичного текста, в этом случае вы должны использовать функции ЕСЛИ, ЕЧИСЛО и ПОИСК вместе.
Например, если вы хотите проверить ячейки, содержащие текст «comp», а затем вернуть соответствующие значения, примените формулу ниже. И вы получите результат, как показано на скриншоте ниже:
Ноты:
- 1. Функция ПОИСК применяется к тексту без учета регистра, если вы хотите проверить текст с учетом регистра, вам следует заменить функцию ПОИСК функцией НАЙТИ. Как это: =IF(ISNUMBER(FIND("comp",C2)), "No", "Yes")
- 2. Текстовые значения в качестве параметров в формулах ЕСЛИ, вы должны заключить их в «двойные кавычки».
Пример 3: Использование функции ЕСЛИ для значений даты
Случай 1: функция ЕСЛИ для дат для сравнения дат с определенной датой:
Если вы хотите сравнить даты, чтобы проверить, больше ли они или меньше определенной даты, функция ЕСЛИ также может оказать вам услугу. Поскольку функция ЕСЛИ не может распознать формат даты, вам следует объединить с ней функцию ДАТАЗНАЧ.
Примените эту формулу, когда дата больше 4 апреля 15 г., будет возвращено «Да», в противном случае формула вернет текст «Нет», см. Снимок экрана:
Внимание: В приведенной выше формуле вы можете использовать ссылку на ячейку напрямую, без использования функции DATEVALUE. Как это: =IF(D4>$D$1, "Yes", "No") .
Случай 2: функция ЕСЛИ для дат для проверки дат больше или меньше 30 дней:
Если вы хотите определить даты, которые больше или меньше 30 дней от текущей даты, вы можете объединить функцию СЕГОДНЯ с функцией ЕСЛИ.
Пожалуйста, введите эту формулу:
Определите дату старше 30 дней: =IF(TODAY()-C4>30,"Older date","")
Определите дату, превышающую 30 дней: =IF(C4-TODAY()>30, "Future date", "")
Примечание: Если вы хотите поместить оба результата в один столбец, вам нужно использовать вложенную функцию IF следующим образом:
Пример 4: Использование функции ЕСЛИ с функциями И, ИЛИ вместе
Мы часто используем сочетание функций ЕСЛИ, И, ИЛИ в Excel.
Случай 1. Использование функции ЕСЛИ с функциями И для проверки выполнения всех условий:
Я хочу проверить, выполняются ли все установленные мной условия, например: B4 - красный, C4 - маленький и D4> 200. Если все условия TURE, отметьте результат как «Да»; Если любое из условий - ЛОЖЬ, верните «Нет».
Пожалуйста, примените эту формулу, и вы получите результат, как показано на следующем скриншоте:
Случай 2: Использование функции ЕСЛИ с функциями ИЛИ для проверки выполнения любого из условий:
Вы также можете использовать функции ЕСЛИ и ИЛИ, чтобы проверить, выполняется ли какое-либо из условий, например, я хочу определить, содержит ли ячейка в столбце B текст «Синий» или «Красный», если какой-либо текст в столбце B, Отображается Да, в противном случае возвращается Нет.
Здесь вы должны применить эту формулу, и будет показан результат ниже:
Случай 3: Использование функции ЕСЛИ вместе с функциями И и ИЛИ:
В этом примере я объединю функцию ЕСЛИ с функциями И и ИЛИ одновременно. Предположим, вы должны проверить следующие условия:
- Condition 1: Column B = “Red” and Column D > 300;
- Condition 2: Column B = “Blue” and Column D > 300.
Если выполняется одно из вышеуказанных условий, возвращается Match, в противном случае - No.
Воспользуйтесь этой формулой, и вы получите следующий результат, который вам нужен:
Пример 5: Использование вложенной функции ЕСЛИ
Функция ЕСЛИ используется для проверки условия и возврата одного значения, если условие выполняется, и другого значения, если оно не выполняется. Но иногда вам нужно проверять более одного условия одновременно и возвращать разные значения, вы можете использовать Nested IF для решения этой задачи.
Вложенный оператор IF, который объединяет несколько условий IF, это означает размещение оператора IF внутри другого оператора IF и повторение этого процесса несколько раз.
Синтаксис функции вложенного ЕСЛИ в Excel:
Внимание: В Excel 2007 и более поздних версиях вы можете вложить до 64 функций ЕСЛИ в одну формулу, а в Excel 2003 и более ранних версиях можно использовать только 7 вложенных функций ЕСЛИ.
Случай 1. Вложенная функция ЕСЛИ для проверки нескольких условий:
Классическое использование функции вложенного ЕСЛИ - присвоение каждому учащемуся буквенной оценки на основе их оценок. Например, у вас есть таблица с учащимися и их оценками за экзамены, теперь вы хотите классифицировать оценки по следующим условиям:
Пожалуйста, примените эту формулу, и вы получите следующий результат: если оценка больше или равна 90, оценка будет «отлично», если оценка больше или равна 80, оценка будет «хорошо», если оценка больше или равно 60, оценка - «Средний», в противном случае - «Плохо».
Объяснение приведенной выше формулы:
Случай 2: Вложенная функция ЕСЛИ для расчета цены на основе количества:
Функцию вложенного ЕСЛИ также можно использовать для расчета цены продукта на основе количества.
Например, вы хотите предоставить покупателям ценовой разрыв в зависимости от количества, большего количества, которое они покупают, большей скидки, которую они получат, как показано на скриншоте ниже.
Поскольку общая цена равна количеству, умножьте цену, поэтому вы должны умножить указанное количество на значение, возвращаемое вложенными If. Пожалуйста, используйте эту формулу:
Внимание: Вы также можете использовать ссылки на ячейки для замены статических ценовых чисел, при изменении исходных данных вам не нужно обновлять формулу, используйте эту формулу: =D2*IF(D2>=101, B6, IF(D2>=50, B5, IF(D2>=25, B4, IF( D2>=11, B3, IF(D2>=1, B2, "")))))
Советы: Используя функцию ЕСЛИ для построения теста, вы можете использовать следующие логические операторы:
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Функция ЕСЛИ — одна из самых популярных функций в Excel. Она позволяет выполнять логические сравнения значений и ожидаемых результатов.
Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно.
Например, функция =ЕСЛИ(C2="Да";1;2) означает следующее: ЕСЛИ(С2="Да", то вернуть 1, в противном случае вернуть 2).
Функция ЕСЛИ, одна из логических функций, служит для возвращения разных значений в зависимости от того, соблюдается ли условие.
ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])
Имя аргумента
лог_выражение (обязательно)
Условие, которое нужно проверить.
значение_если_истина (обязательно)
Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.
значение_если_ложь (необязательно)
Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.
Простые примеры функции ЕСЛИ
В примере выше ячейка D2 содержит формулу: ЕСЛИ(C2 = Да, то вернуть 1, в противном случае вернуть 2)
В этом примере ячейка D2 содержит формулу: ЕСЛИ(C2 = 1, то вернуть текст "Да", в противном случае вернуть текст "Нет"). Как видите, функцию ЕСЛИ можно использовать для сравнения и текста, и значений. А еще с ее помощью можно оценивать ошибки. Вы можете не только проверять, равно ли одно значение другому, возвращая один результат, но и использовать математические операторы и выполнять дополнительные вычисления в зависимости от условий. Для выполнения нескольких сравнений можно использовать несколько вложенных функций ЕСЛИ.
=ЕСЛИ(C2>B2;"Превышение бюджета";"В пределах бюджета")
В примере выше функция ЕСЛИ в ячейке D2 означает: ЕСЛИ(C2 больше B2, то вернуть текст "Превышение бюджета", в противном случае вернуть текст "В пределах бюджета")
На рисунке выше мы возвращаем не текст, а результат математического вычисления. Формула в ячейке E2 означает: ЕСЛИ(значение "Фактические" больше значения "Плановые", то вычесть сумму "Плановые" из суммы "Фактические", в противном случае ничего не возвращать).
В этом примере формула в ячейке F7 означает: ЕСЛИ(E7 = "Да", то вычислить общую сумму в ячейке F5 и умножить на 8,25 %, в противном случае налога с продажи нет, поэтому вернуть 0)
Примечание: Если вы используете текст в формулах, заключайте его в кавычки (пример: "Текст"). Единственное исключение — слова ИСТИНА и ЛОЖЬ, которые Excel распознает автоматически.
Распространенные неполадки
0 (ноль) в ячейке
Не указан аргумент значение_если_истина или значение_если_ложь. Чтобы возвращать правильное значение, добавьте текст двух аргументов или значение ИСТИНА/ЛОЖЬ.
Как правило, это указывает на ошибку в формуле.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Чтобы просмотреть более подробные сведения о функции, щелкните ее название в первом столбце.
Примечание: Маркер версии обозначает версию Excel, в которой она впервые появилась. В более ранних версиях эта функция отсутствует. Например, маркер версии 2013 означает, что данная функция доступна в выпуске Excel 2013 и всех последующих версиях.
Возвращает заданную дату в числовом формате.
Вычисляет количество дней, месяцев или лет между двумя датами. Эта функция полезна в формулах расчета возраста.
Преобразует дату из текстового формата в числовой.
Преобразует дату в числовом формате в день месяца.
ДНИ
Возвращает количество дней между двумя датами.
Вычисляет количество дней между двумя датами на основе 360-дневного года.
Возвращает дату в числовом формате, отстоящую на заданное число месяцев вперед или назад от начальной даты.
Возвращает дату в числовом формате для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев.
Преобразует дату в числовом формате в часы.
НОМНЕДЕЛИ.ISO
Возвращает номер недели по ISO для заданной даты.
Преобразует дату в числовом формате в минуты.
Преобразует дату в числовом формате в месяцы.
Возвращает количество полных рабочих дней между двумя датами.
ЧИСТРАБДНИ.МЕЖД
Возвращает количество полных рабочих дней в интервале между двумя датами, руководствуясь параметрами, указывающими выходные дни и их количество.
Возвращает текущую дату и время в числовом формате.
Преобразует дату в числовом формате в секунды.
Возвращает заданное время в числовом формате.
Преобразует время из текстового формата в числовой.
Возвращает текущую дату в числовом формате.
Преобразует дату в числовом формате в день недели.
Преобразует дату в числовом формате в число, которое указывает, на какую неделю года приходится дата.
Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней.
РАБДЕНЬ.МЕЖД
Возвращает числовое значение даты, предшествующей заданному количеству рабочих дней или следующей за ними, руководствуясь при этом параметрами, указывающими выходные дни и их количество.
Преобразует дату в числовом формате в год.
Возвращает долю года, которую составляет количество дней между начальной и конечной датами.
Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.
Как определить, находится ли дата между двумя датами или выходными в Excel?
Иногда вам может потребоваться определить или проверить, находится ли дата между двумя датами или выходными в Excel. Эта статья покажет вам следующие хитрые способы проверить, находится ли дата между двумя датами или выходными в Excel.
Quickly determine if a date falls between two given dates:
The Select Specific Cells utility of Kutools for Excel can help you quickly determine if dates in a specific range are falling between two given dates. Find them and select them easily. Download the full feature 60-day free trail of Kutools for Excel now!
Определите, находится ли дата между двумя датами в Excel
Предположим, вам нужно определить, попадают ли даты в столбце A между 7 и 1. пожалуйста, сделайте следующее:
1. В пустой ячейке с надписью «Ячейка B2» скопируйте и вставьте в нее приведенную ниже формулу и нажмите Enter ключ.
Внимание: Эта формула проверяет, находится ли дата между 7 и 1. Если дата попадает в этот период, она вернет дату; если дата не попадает в этот период, он вернет текст НЕПРАВДА.
2. Продолжая выбирать ячейку результата, перетащите маркер заполнения вниз, чтобы получить все результаты. Смотрите скриншот:
Теперь вы можете определить, попадает ли дата в указанный диапазон дат или нет.
Определите, находится ли дата между двумя датами в Excel с помощью Kutools for Excel
Здесь вам порекомендуют потрясающий инструмент - Выбрать определенные ячейки полезности Kutools for Excel. Этот инструмент поможет вам не только узнать все даты, которые попадают между двумя заданными датами, но и сразу выбрать все соответствующие ячейки дат.
Перед его применением необходимо сначала скачайте и установите.
1. Выберите диапазон дат, по которым вы хотите определить, попадают ли они между двумя датами, а затем щелкните Кутулс > Выберите > Выбрать определенные ячейки. Смотрите скриншот:
2. в Выбрать определенные ячейки в диалоговом окне выберите Ячейка вариант в Тип выбора раздел, а затем укажите Больше и Менее даты и, наконец, нажмите OK кнопку.
Вы можете видеть, что ячейки даты, которые находятся между двумя датами, выбираются немедленно. Смотрите скриншот:
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Определите, приходится ли дата на выходные с формулами и кодом VBA
Вы можете определить, приходится ли дата в столбце A на выходные, выполнив следующие действия:
Метод А. Использование формулы для проверки того, приходится ли свидание на выходные.
1. В пустой ячейке скопируйте и вставьте в нее приведенную ниже формулу и нажмите Enter ключ.
=IF(OR(WEEKDAY(A2)=1,WEEKDAY(A2)=7),A2,FALSE)
Эта формула определяет, приходится ли свидание на выходные или нет. Если дата выпадает на выходные, она вернет дату; если дата не выпадает на выходные, она вернет текст НЕПРАВДА.
2. Продолжайте выбирать ячейку результата, затем перетащите маркер заполнения вниз, чтобы получить все результаты.
Метод Б. Использование пользовательской функции для проверки того, приходится ли свидание на выходные.
1. нажмите ALT + F11 вместе, чтобы открыть окно Microsoft Visual Basic для приложений.
2. В окне Microsoft Visual Basic для приложений щелкните Вставить >> Модулии вставьте следующий макрос в окно модуля.
3. Нажмите одновременно клавиши Alt + Q, чтобы закрыть окно Microsoft Visual Basic для приложений.
4. В пустой ячейке введите формулу в строку формул и нажмите кнопку Enter ключ.
=IsWeekend(A2)
Если он возвращает текст Правда, дата в ячейке A2 - выходной; и если он возвращает текст Ложь, дата в ячейке A2 не приходится на выходные.
Определите, приходится ли свидание на выходные, с помощью замечательного инструмента
На самом деле вы можете преобразовать все даты в название дня недели, а затем проверить выходные дни на основе субботы или воскресенья. Здесь Применить формат даты полезности Kutools for Excel может помочь вам легко решить проблему.
Перед его применением необходимо сначала скачайте и установите.
1. Выберите диапазон дат и нажмите Кутулс > Формат > Применить форматирование даты. Смотрите скриншот:
2. в Применить форматирование даты диалоговое окно, выберите среда в Форматирование даты поле, а затем щелкните OK кнопку.
Теперь выбранные даты сразу конвертируются в дни недели. Вы можете определить, приходится ли свидание на выходные или нет, напрямую по его содержанию. Смотрите скриншот:
Заметки:
- Преобразованные результаты работали непосредственно с исходными данными;
- Эта утилита поддерживает расстегиватьCtrl + Z».
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Статьи по теме:
Преобразование даты в день недели, месяц, название года или число в Excel
Говорит, что вы вводите дату в одну ячейку, и отображается как 12/13/2015. Есть ли способ показать только месяц или день недели или текст названия месяца или дня недели, например, декабрь или воскресенье? Методы, описанные в этой статье, могут помочь вам легко преобразовать или отформатировать любые даты, чтобы в Excel отображалось только название дня недели или месяца.
Быстро конвертируйте дату рождения в возраст в Excel
Например, вы получаете ряд различных данных о дате рождения в Excel, и вам нужно преобразовать эти даты рождения, чтобы отобразить их точное значение возраста в Excel, как бы вы хотели выяснить? В этой статье перечислены несколько советов, как легко преобразовать дату рождения в возраст в Excel.
Сравните даты, если они больше другой даты в Excel
Предположим, у вас есть список дат и вы хотите сравнить эти даты с указанной датой, чтобы узнать дату, которая больше указанной даты в списке, что бы вы сделали? В этой статье мы покажем вам методы сравнения дат, если они больше другой даты в Excel.
Сумма значений между двумя диапазонами дат в Excel
Когда на вашем листе есть два списка, один - это список дат, а другой - список значений. И вы хотите суммировать значения только между двумя диапазонами дат, например, суммировать значения между 3/4/2014 и 5/10/2014, как вы можете быстро их вычислить? Методы, описанные в этой статье, окажут вам услугу.
Добавить дни к дате, включая выходные и праздничные дни или исключая их, в Excel
В этой статье говорится о добавлении дней к заданной дате, исключая выходные и праздничные дни, что означает добавление рабочих дней (с понедельника по пятницу) только в Excel.
Определите, находится ли дата между двумя датами или выходными с помощью Kutools for Excel
Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 60 дней. Загрузите бесплатную пробную версию прямо сейчас!
Читайте также: