Формула если в excel с датами
Чтобы суммировать значения в пределах определенного диапазона дат в Excel, вы можете применить формулу, основанную на функции СУММЕСЛИМН.
Как суммировать значения между двумя датами в Excel?
В этом примере суммируются значения в столбце D, если даты в столбце B находятся между двумя заданными датами, указанными в G5 и G6.
Общие формулы
Включая указанные даты
=SUMIFS(sum_range, date_range,">="&start_date, date_range, "< gt-block">Исключая указанные даты
SUMIFS(sum_range, date_range,">"&start_date, date_range, "<"&end_date)
аргументы
Sum_range: Диапазон ячеек для суммирования; Диапазон дат: Диапазон ячеек дат, который вы хотите оценить по start_date и end_date; Дата начала: Начальная дата диапазона дат, определяющего, какие ячейки будут суммироваться; Дата окончания: Дата окончания диапазона дат, определяющего, какие ячейки будут суммироваться.Как пользоваться этой формулой?
Выберите пустую ячейку, скопируйте или введите формулу ниже и нажмите Enter ключ для получения результата.
Сумма значений между двумя датами (включая эти две даты)
Сумма значений между двумя датами (исключая эти две даты)
SUMIFS(D5:D12, B5:B12,">"&G5, B5:B12, "<"&G6)
Внимание: В формулах вы можете изменить ячейки, содержащие дату начала и дату окончания, или напрямую ввести дату начала и дату окончания, заключенные в двойные кавычки.
Связанные функции
Функция СУММЕСЛИМН в Excel
Функция СУММЕСЛИ в Excel может помочь суммировать значения ячеек на основе нескольких критериев.
Родственные формулы
Сумма, если в ячейках есть звездочка
Чтобы суммировать ячейки, содержащие звездочку, которая рассматривается как буквальный символ, а не подстановочный знак, в этом руководстве объясняется формула, основанная на функции СУММЕСЛИ и тильде (
Суммируйте, если ячейки содержат как x, так и y
В этом пошаговом руководстве показано, как суммировать диапазон ячеек, если соответствующие ячейки содержат как x, так и y, с помощью функции СУММЕСЛИМН.
Суммировать, если ячейки содержат или равны x или y
Чтобы суммировать ячейки, если они содержат одно или другое значение или равны ему, в этом руководстве подробно представлены две формулы, которые помогут решить проблемы.
Суммировать, если ячейки равны или не равны определенному значению
В этом пошаговом руководстве объясняются две формулы для суммирования диапазона ячеек, которые равны или не равны определенному значению на основе функции СУММЕСЛИ.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.
Чтобы просмотреть более подробные сведения о функции, щелкните ее название в первом столбце.
Примечание: Маркер версии обозначает версию Excel, в которой она впервые появилась. В более ранних версиях эта функция отсутствует. Например, маркер версии 2013 означает, что данная функция доступна в выпуске Excel 2013 и всех последующих версиях.
Возвращает заданную дату в числовом формате.
Вычисляет количество дней, месяцев или лет между двумя датами. Эта функция полезна в формулах расчета возраста.
Преобразует дату из текстового формата в числовой.
Преобразует дату в числовом формате в день месяца.
ДНИ
Возвращает количество дней между двумя датами.
Вычисляет количество дней между двумя датами на основе 360-дневного года.
Возвращает дату в числовом формате, отстоящую на заданное число месяцев вперед или назад от начальной даты.
Возвращает дату в числовом формате для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев.
Преобразует дату в числовом формате в часы.
НОМНЕДЕЛИ.ISO
Возвращает номер недели по ISO для заданной даты.
Преобразует дату в числовом формате в минуты.
Преобразует дату в числовом формате в месяцы.
Возвращает количество полных рабочих дней между двумя датами.
ЧИСТРАБДНИ.МЕЖД
Возвращает количество полных рабочих дней в интервале между двумя датами, руководствуясь параметрами, указывающими выходные дни и их количество.
Возвращает текущую дату и время в числовом формате.
Преобразует дату в числовом формате в секунды.
Возвращает заданное время в числовом формате.
Преобразует время из текстового формата в числовой.
Возвращает текущую дату в числовом формате.
Преобразует дату в числовом формате в день недели.
Преобразует дату в числовом формате в число, которое указывает, на какую неделю года приходится дата.
Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней.
РАБДЕНЬ.МЕЖД
Возвращает числовое значение даты, предшествующей заданному количеству рабочих дней или следующей за ними, руководствуясь при этом параметрами, указывающими выходные дни и их количество.
Преобразует дату в числовом формате в год.
Возвращает долю года, которую составляет количество дней между начальной и конечной датами.
Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.
Функция ЕСЛИ - одна из самых простых и полезных функций в книге Excel. Он выполняет простой логический тест, который зависит от результата сравнения, и возвращает одно значение, если результат ИСТИНА, или другое значение, если результат ЛОЖЬ.
Синтаксис:
Синтаксис функции ЕСЛИ в Excel:
=IF ( logical_test , [ value_if_true ], [ value_if_false ])
Аргументы:
- 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 следующим образом:
=IF(C4-TODAY()>30, "Future date", IF(TODAY()-C4>30, "Older date", ""))
Пример 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:
=IF ( condition1 , result1 , IF ( condition2 , result2 , IF ( condition3 , result3 ,…)))Внимание: В Excel 2007 и более поздних версиях вы можете вложить до 64 функций ЕСЛИ в одну формулу, а в Excel 2003 и более ранних версиях можно использовать только 7 вложенных функций ЕСЛИ.
Случай 1. Вложенная функция ЕСЛИ для проверки нескольких условий:
Классическое использование функции вложенного ЕСЛИ - присвоение каждому учащемуся буквенной оценки на основе их оценок. Например, у вас есть таблица с учащимися и их оценками за экзамены, теперь вы хотите классифицировать оценки по следующим условиям:
Пожалуйста, примените эту формулу, и вы получите следующий результат: если оценка больше или равна 90, оценка будет «отлично», если оценка больше или равна 80, оценка будет «хорошо», если оценка больше или равно 60, оценка - «Средний», в противном случае - «Плохо».
=IF(C2>=90, "Excellent", IF(C2>=80, "Good", IF(C2>=60, "Medium", "Poor")))
Объяснение приведенной выше формулы:
Случай 2: Вложенная функция ЕСЛИ для расчета цены на основе количества:
Функцию вложенного ЕСЛИ также можно использовать для расчета цены продукта на основе количества.
Например, вы хотите предоставить покупателям ценовой разрыв в зависимости от количества, большего количества, которое они покупают, большей скидки, которую они получат, как показано на скриншоте ниже.
Поскольку общая цена равна количеству, умножьте цену, поэтому вы должны умножить указанное количество на значение, возвращаемое вложенными If. Пожалуйста, используйте эту формулу:
=D2*IF(D2>=101,16, IF(D2>=50, 21, IF(D2>=25, 26, IF( D2>=11, 30, IF(D2>=1, 39, "")))))
Внимание: Вы также можете использовать ссылки на ячейки для замены статических ценовых чисел, при изменении исходных данных вам не нужно обновлять формулу, используйте эту формулу: =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, один из которых содержит содержимое расписания, а другой - дату расписания. Теперь вы хотите найти следующую дату в расписании на основе сегодняшнего дня. Какую формулу вы можете использовать?
Если вы хотите следовать этому руководству, загрузите пример электронной таблицы.
Общая формула:
| MIN(IF(date_range>=TODAY(),date_range)) |
Синтаксис и аргументы
| Date_range: the cells that contain the schedule dates. |
Возвращаемое значение
Формула возвращает 5-значное число. В Excel дата хранится в виде серийных номеров для лучшего расчета. Вы можете отформатировать пятизначное число как формат даты, если хотите просмотреть дату напрямую.
Как работает эта формула
В таблице B3: C8 даты расписания перечислены в C3: C8, содержимое расписания указано в B3: B8, сегодня 2, чтобы найти следующую дату в списке расписания, используйте формулу массива ниже:
Нажмите клавиши Shift + Ctrl + Enter, чтобы получить правильный результат.
Затем, не снимая выделения с результата, перейдите к Главная вкладку и щелкните раскрывающийся список Формат номера выбрать Короткое свидание or Длинное свидание как вам нужно.
Теперь следующая дата в списке расписания найдена.
объяснение
Функция IF: используется для проверки определенного условия и возврата ИСТИНА или ЛОЖЬ.
Функция MIN: найти наименьшее значение в диапазоне или массиве.
Советы
Если вы хотите получить содержимое расписания на основе найденной даты, используйте приведенную ниже формулу
=INDEX($B$3:$B$8,MATCH(F2,$C$3:$C$8,0))
Нажмите Enter ключ.
Если вы хотите напрямую найти следующее содержимое расписания на основе сегодняшнего дня, воспользуйтесь формулой массива:
=INDEX($B$3:$B$8,MATCH(MIN(IF($C$3:$C8>=TODAY(),$C$3:$C$8)),$C$3:$C$8,0))
Нажмите Shift + Ctrl + Enter ключи.
Относительные формулы
В этом руководстве представлены формулы для возврата первого или последнего дня месяца с указанной даты в Excel.
В этом руководстве представлены формулы для поиска самой ранней и последней даты для каждой группы в Excel.
Подсчитать количество дней, месяцев или лет между двумя заданными датами будет легко в Excel после прочтения этого руководства.
В этом руководстве представлена формула для быстрого получения диапазона дат за неделю в Excel.
Относительные функции
Возвращает номер недели указанной даты в году.
Добавляет рабочие дни к заданной дате начала и возвращает рабочий день
Получить день недели в виде числа (от 1 до 7) от даты
Функция ДЕНЬ получает день в виде числа (от 1 до 31) от даты
Получить текущее время и дату
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.
Читайте также: