Подсчет чисел или дат на основе условия
Чтобы подсчитать числа или даты, которые соответствуют одному условию (например, равны, больше, меньше, больше или равно, меньше или меньше), используйте функцию СЧЁТЕСЛИ . Чтобы подсчитать числа или даты, которые попадают в диапазон (например, больше 9000 и в то же время меньше 22500), можно использовать функцию СЧЁТЕСЛИ. Кроме того, можно также использовать SUMPRODUCT.
Пример
Примечание: Вам потребуется настроить приведенные здесь ссылки на формулы ячеек в зависимости от места и способа копирования этих примеров на лист Excel.
Сложение или вычитание дат
Предположим, что вы хотите добавить две недели к сроку, установленному в календарном плане проекта, и определить новую дату окончания, или выяснить по списку задач проекта, сколько времени потребуется для выполнения одной задачи. Можно сложить или вычесть дни, отталкиваясь от даты, с помощью простой формулы или использовать функции листа, предназначенные для работы с датами в Excel.
Добавление или вычитание дней из даты
Предположим, что ваш счет выставляется во вторую пятницу каждого месяца. Вы хотите перевести средства на свой расчетный счет, чтобы эти средства поступили за 15 календарных дней до этой даты, поэтому вы вычитаете 15 дней из даты выполнения. В следующем примере вы узнаете, как добавлять и вычитать даты путем ввода положительных или отрицательных чисел.
- Введите даты выполнения в столбце A.
- Введите число дней для добавления или вычитания в столбце B. Вы можете ввести отрицательное число, чтобы вычесть дни из даты начала, и положительное число для добавления к дате.
- В ячейке C2 введите =A2+B2 и при необходимости скопируйте вниз.
Добавление или вычитание месяцев из даты с помощью функции EDATE
Функцию EDATE можно использовать для быстрого добавления или вычитания месяцев из даты.
Для функции EDATE требуются два аргумента: дата начала и количество месяцев, которые нужно добавить или вычесть. Чтобы вычесть месяцы, введите отрицательное число в качестве второго аргумента. Например, =EDATE(«15.09.19»,-5) возвращает 15.04.19.
- В этом примере можно ввести начальные даты в столбце A.
- Введите число месяцев для добавления или вычитания в столбце B. Чтобы указать, следует ли вычесть месяц, можно ввести знак минуса (-) перед числом (например, -1).
- Введите =EDATE(A2,B2) в ячейку C2 и при необходимости скопируйте его.
- В зависимости от формата ячеек, содержащих введенные формулы, Excel может отображать результаты в виде серийных номеров. Например, 8 февраля 2019 г. может отображаться как 43504.
- В Excel даты хранятся в виде порядковых номеров, что позволяет использовать их в вычислениях. По умолчанию 1 января 1900 года является серийным номером 1, а 1 января 2010 года — серийным номером 40179, так как после 1 января 1900 года это 40 178 дней.
- Если результаты отображаются в виде серийных номеров, выберите нужные ячейки и выполните следующие действия:
- Нажмите клавиши CTRL+1 , чтобы открыть диалоговое окно Формат ячеек , и откройте вкладку Число .
- В разделе Категория щелкните Дата, выберите нужный формат даты и нажмите кнопку ОК. Значения в ячейках должны отобразиться как даты.
Добавление или вычитание лет из даты
В этом примере мы добавляем и вычитаем годы из начальной даты с помощью следующей формулы:
=ДАТА(ГОД(A2)+B2;МЕСЯЦ(A2);ДЕНЬ(A2))
Принцип работы формулы:
- Функция YEAR просматривает дату в ячейке A2 и возвращает значение 2019. Затем он добавляет 3 года из ячейки B2, в результате чего в 2022 году.
- Функции MONTH и DAY возвращают только исходные значения из ячейки A2, но функция DATE требует их.
- Наконец, функция DATE объединяет эти три значения в дату, которая будет 3 года в будущем — 08.02.22.
Добавление или вычитание сочетания дней, месяцев и лет к дате или из нее
В этом примере мы добавляем и вычитаем годы, месяцы и дни из начальной даты с помощью следующей формулы:
=ДАТА(ГОД(A2)+B2;МЕСЯЦ(A2)+C2;ДЕНЬ(A2)+D2)
Принцип работы формулы:
- Функция YEAR просматривает дату в ячейке A2 и возвращает значение 2019. Затем он добавляет 1 год из ячейки B2, что приводит к 2020 году.
- Функция MONTH возвращает значение 6, а затем добавляет к нему 7 из ячейки C2. Это интересно, так как 6 + 7 = 13, то есть 1 год и 1 месяц. В этом случае формула распознает это и автоматически добавит еще один год к результату, наталкивая его с 2020 по 2021 год.
- Функция DAY возвращает значение 8 и добавляет к нему значение 15. Это будет работать аналогично части формулы MONTH, если вы перейдете по количеству дней в заданном месяце.
- Затем функция DATE объединяет эти три значения в дату, которая составляет 1 год, 7 месяцев и 15 дней в будущем — 23.01.21.
Ниже приведены некоторые способы использования формул или функций листа, которые работают с датами для выполнения таких действий, как поиск влияния на расписание проекта при добавлении двух недель или времени, необходимого для выполнения задачи.
Предположим, что ваша учетная запись имеет 30-дневный период выставления счетов, и вы хотите, чтобы средства на счете были за 15 дней до даты выставления счетов в марте 2013 года. Вот как это сделать, используя формулу или функцию для работы с датами.
- В ячейке A1 введите 13.08.02.
- В ячейке B1 введите =A1-15.
- В ячейке C1 введите =A1+30.
- В ячейке D1 введите =C1-15.
Добавление месяцев к дате
Мы будем использовать функцию EDATE , и вам потребуется дата начала и количество месяцев, которые вы хотите добавить. Вот как добавить 16 месяцев к 24.10.13:
- В ячейке A1 введите 24.10.13.
- В ячейке B1 введите =EDATE(A1,16).
- Чтобы отформатировать результаты как даты, выберите ячейку B1. Щелкните стрелку рядом с полем Числовой формат, >Короткая дата.
Вычитание месяцев из даты
Мы будем использовать ту же функцию EDATE для вычитания месяцев из даты.
Введите дату в ячейке A1, а в ячейке B1 введите формулу =EDATE(15.04.2013,-5).
Здесь мы указываем значение даты начала, указывая дату, заключенную в кавычки.
Вы также можете просто ссылаться на ячейку, содержащую значение даты, или с помощью формулы =EDATE(A1,-5)для того же результата.
Дополнительные примеры
Добавление лет к дате или вычитание лет из даты
Добавленные или вычитаемые годы
Как вычислить дату в Excel
Дата и время в Excel – это числа сформатированные специальным образом. Датой является целая часть числа, а время (часы и минуты) – это дробная часть.
По умолчанию число 1 соответствует дате 01 января 1900 года. То есть каждая дата – это количество дней прошедших от 01.01.1900. На данном уроке детально рассмотрим даты, а на следующих уроках – время.
Как в Excel посчитать дни между датами?
Так как дата является числом, значит можно проводить с ним математические вычислительные и расчетные операции. Посчитать количество дней между двумя датами Excel не составляет особых проблем. Для наглядного примера сначала выполним сложение, а потом вычитание дат. Для этого:
- На чистом листе в ячейку А1 введите текущею дату нажав CTRL+;.
- В ячейке A2 введите промежуточный период в днях, например 127.
- В ячейку A3 введите формулу:=A1+A2.
Обратите внимание, формат «Дата» автоматически присвоился для ячейки A3. Не сложно догадаться, чтобы посчитать разницу в датах в Excel нужно от новейшей даты отнять старшую дату. В ячейке B1 введите формулу:=A3-A1. Соответственно мы получаем количество дней между этими двумя датами.
Вычисление возраста по дате рождения в Excel
Теперь мы научимся вычислять возраст по дате рождения:
- На новый лист в ячейки A1:A3 введите даты: 18.04.1985; 17.08.1977; 08.12.1992
- В ячейки B1:B3 проставьте текущею дату.
- Теперь нужно воспользоваться функцией по преобразованию количества дней в количество лет. Для этого вручную введите в диапазон C1:C3 следующее значение: =РАЗНДАТ(A1;B1;»y»).
Таким образом, применение функции позволило нам точно вычислить возраст по дате рождения в Excel.
Внимание! Чтобы перевести дни в года не достаточно формулы: =(B1-A1)/365. Более того даже если мы знаем что 1 сутки = 0,0027397260273973 года, то формула:=(B1-A1)*0,0027397260273973 так же не даст точного результата.
Дни в года наиболее точно преобразует функция:=РАЗНДАТ(). Английская версия =DATEDIF(). Вы не найдете ее в списке мастера функций (SHIFT+F3). Но если просто введете ее в строку формул, то она сработает.
Функция РАЗНДАТ поддерживает несколько параметров:
Параметр Описание «d» Количество полных дней «m» Количество полных месяцев «y» Количество полных лет «ym» Количество полных месяцев без учета лет «md» Количество дней без учета месяцев и лет «yd» Количество дней без учета лет Проиллюстрируем пример использования нескольких параметров:
Внимание! Чтобы функция:=РАЗНДАТ() работала без ошибок, следите за тем, чтобы начальная дата была старше конечной даты.
Ввод даты в ячейку Excel
Целью данного урока является пример математических операций с датами. Так же, мы убедимся, что для Excel тип данных дата является числом.
Заполните таблицу датами, так как показано на рисунке:
Разные способы ввода дат. В столбце А – способ ввода, а в столбце B – результат отображения.
Обратите внимание, что в формате ячеек по умолчанию «Общий», даты также как и число выравниваются по правой стороне, а текст по левой. Значение в ячейке B4 распознается программой как текст.
В ячейке B7 Excel сам присвоил текущий год (сейчас 2015-ый) по умолчанию. Это видно при отображении содержимого ячеек в строке формул. Обратите внимание, как изначально вводилось значение в A7.
Вычисление даты Excel
На чистом листе в ячейки А1:B1 введите 01.01.1900, а в ячейках A2:B2 вводим 01.01.1901. Теперь меняем формат ячеек на «числовой» в выделенном диапазоне B1:B2. Для этого можно нажать комбинацию клавиш CTRL+SHIFT+1.
B1 теперь содержит число 1, а B2 – 367. То есть прошел один високосный год (366 дней) и 1 день.
Способ отображения даты можно задать с помощью диалогового окна «Формат ячеек». Для его вызова нажмите: CTRL+1. На закладке «Число» выберите в списке «Числовые форматы» — «Дата». В разделе «Тип» отображаются самые популярные форматы для отображения дат.
На следующем уроке мы на готовых примерах будем работать со временем и периодами суток.
Вычитание дат
Вы можете легко найти разницу двух дат, определить формат результата и произвести вычисление на основе списка дат. Например, можно быстро вычесть одну дату из другой или вычислить длительность задач в расписании проекта.
Чтобы научиться вычислять даты в расписании проекта, используйте предоставленные образцы данных и описанные ниже процедуры.
Копирование примера данных
-
Скопируйте приведенные ниже примеры данных в ячейку A1 на пустом листе.
Задача Дата начала Дата окончания Длительность Создание плана проекта 08.06.2011 20.09.2011 Этап 1. 21.09.2011 02.06.2012 Этап 2. 03.06.2012 10.12.2014 Этап 3. 11.12.2014 20.04.2017 Вычисление разницы в днях
Определение количества дней между двумя датами
- Выделите ячейку D2 — первую пустую ячейку в столбце «Длительность».
- Введите =C2-B2 и нажмите клавишу RETURN. В Excel возвращается результат в виде количества дней между двумя датами (104).
- Выберите ячейку D2.
- Перетащите маркер заполнения
Вычисление количества рабочих дней между двумя датами
- Выделите ячейку D2 — первую пустую ячейку в столбце «Длительность». Если ячейка не пуста, в меню Правка выберите пункты Очистить и Все.
- Выполните одно из указанных ниже действий.
- В Excel для Mac 2011 на вкладке Формулы в разделе Функция щелкните Построитель формул.
-
В Excel 2016 для Mac на вкладке Формулы щелкните Вставить функцию.
- Выделите ячейку D2 — первую пустую ячейку в столбце «Длительность». Если ячейка не пуста, в меню Правка выберите пункты Очистить и Все.
- В ячейке D2 введите =МЕСЯЦ(C2)-МЕСЯЦ(B2), а затем нажмите клавишу RETURN. Результат — 3.
- Выберите ячейку D3. Если ячейка не пуста, в меню Правка выберите пункты Очистить и Все.
- В ячейке D3 введите =(ГОД(C3)-ГОД(B3))*12+МЕСЯЦ(C3)-МЕСЯЦ(B3), а затем нажмите клавишу RETURN. Результат — 9.
- Выберите ячейку D4. Если ячейка не пуста, в меню Правка выберите пункты Очистить и Все.
- В ячейке D4 введите =ГОД(C4)-ГОД(B4), а затем нажмите клавишу RETURN. Результат — 2.
, чтобы скопировать формулу в оставшиеся строки. Excel автоматически изменяет ссылки на ячейки, чтобы включить правильные значения для каждой строки.
Совет: Подробнее об этих формулах, в том числе о том, как учитывать в них праздники, читайте в статьях Функция ЧИСТРАБДНИ и Функция ЧИСТРАБДНИ.МЕЖД.
Вычисление количества месяцев между двумя датами одного и того же года
В Excel месяцы представлены в виде значений от 1 до 12, что позволяет легко вычесть более раннюю дату из более поздней, если они приходятся на один год. Найдите значение месяца для каждой из дат с помощью функции МЕСЯЦ, а затем вычислите разницу между значениями.
Вычисление количества месяцев между двумя датами разных лет
Чтобы определить количество месяцев между датами, которые приходятся на разные года, можно использовать формулу, которая определяет число лет между двумя датами, преобразует его в число месяцев и учитывает дополнительные месяцы, чтобы получить точный результат. Для вычисления используются функции МЕСЯЦ и ГОД.
Вычисление количества лет между двумя датами
В Excel года представлены в виде значений. Найдите значение года для каждой из дат с помощью функции ГОД, а затем вычислите разницу между значениями.