Check-moscow.ru

Финансы и учет
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как добавить / вычесть недели до даты в Excel?

Как в Power BI и Power Pivot сдвинуть даты? DAX функции DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR

( Нажмите на соответствующий раздел, чтобы перейти в него):

Антон БудуевПриветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы поговорим о том, как в Power BI и Power Pivot сдвинуть даты вперед или назад для сравнения текущих показателей с прошлыми или будущими. А конкретно, разберем функции, отвечающие в DAX за этот процесс — DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR.

Давайте рассмотрим каждую из этих функций более подробно.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

DAX функция DATEADD в Power BI и Power Pivot

DATEADD () — создает таблицу со столбцом из дат, сдвинутых назад (в прошлое) или вперед (в будущее) на заданное количество интервалов от даты текущего контекста.

  • [Дата] — столбец из дат или выражений, возвращающих даты
  • Количество Интервалов — целое число, характеризующее количество интервалов, которое нужно добавить (вычесть) к дате в текущем контексте. Если указано положительное число — то интервалы добавляются, если указано отрицательное число — то интервалы вычитаются
  • Интервал — тип интервала: year (год), quarter (квартал), month (месяц), day (день)

! — Для правильной работы функции необходимо в качестве ее первого параметра [Дата] использовать столбец из таблицы непрерывных дат в Power BI, то есть, создавать отдельную связанную таблицу «Календарь» с непрерывным перечислением всех дат.

Давайте узнаем больше о функции DAX DATEADD, посмотрев несколько формул.

В Power BI Desktop имеется таблица с датами «Календарь»:

Календарь дат

Для формул, которые сравнивают текущие значения с прошлыми, необходимо каким-то образом найти соответствующую дату в прошлом, относительно текущей даты. Для этого мы будем использовать рассматриваемую функцию DATEADD.

И для примера того, как она сдвигает даты, создадим в таблице «Календарь» вычисляемый столбец на основе следующей формулы:

В первом параметре этой формулы мы указали ссылку на колонку даты в календаре (календарь должен быть связан с таблицей фактов, по которой будут вычисляться показатели).

Во втором параметре — количество интервалов, на которое нужно сдвинуть даты, причем значение отрицательное, то есть, сдвиг будет в прошлое.

В третьем — тип самого интервала (день).

В результате применения формулы на основе DATEADD колонка даты будет перемещена на два дня назад во времени:

Результат работы формулы в Power BI на основе DAX функции DATEADD

Соответственно, текущая дата в колонке [Дата] соответствует дате за последние два дня (например, текущая дата 5 января соответствует дате 3 января).

Формула будет выглядеть следующим образом, если мы укажем положительное число интервалов:

Тогда перемены наступят в будущем:

Функция DATEADD - сдвиг даты в будущее

Другими словами, каждая из текущих дат в колонке [Дата] представляет собой дату из будущего на один день вперед (например, текущая дата 1 января указывает на дату в будущем 2 января).

DAX функция PARALLELPERIOD в Power BI и Power Pivot

PARALLELPERIOD () — возвращает таблицу из дат, смещенных во времени вперед или назад параллельно текущей дате в текущем контексте на заданное количество интервалов.

  • [Дата] — столбец из дат или выражений, возвращающих даты
  • Количество Интервалов — целое число, характеризующее количество интервалов, которое нужно добавить (вычесть) к дате в текущем контексте. Если указано положительное число — то интервалы добавляются, если указано отрицательное число — то интервалы вычитаются
  • Интервал — тип интервала: year (год), quarter (квартал), month (месяц)

! — Для правильной работы функции необходимо в качестве ее первого параметра [Дата] использовать столбец из таблицы непрерывных дат в Power BI, то есть, создавать отдельную связанную таблицу «Календарь» с непрерывным перечислением всех дат.

В целом, функции DATEADD и PARALLELPERIOD в основном одинаковы, за исключением того, что

  1. PARALLELPERIOD не имеет диапазона дней
  2. PARALLELPERIOD возвращает весь параллельный период (например, с начала до конца месяца), тогда как DATEADD специально возвращает только диапазон, указанный в исходном столбце даты.
Читайте так же:
Как выбрать случайные имена из списка в Excel?

Например, изначальный набор дат будет отфильтрован датами от 16 до 25 октября. И если мы обеими функциями попытаемся возвратить набор дат на 1 месяц назад, то DATEADD возвратит даты только с 16 до 25 сентября, а PARALLELPERIOD — уже возвратит даты всего предыдущего месяца от начала и до конца, то есть, с 1 по 30 сентября.

Давайте рассмотрим разницу между DATEADD и PARALLELPERIOD на конкретном практическом примере.

В PowerBI Desktop доступен первоначальный лист продаж (с 16 января 2018 года по 31 декабря 2018 года):

Исходная таблица продаж

Задача состоит в следующем — в рамках сводной таблицы, напротив текущего месяца, получить сумму продаж за предыдущий месяц. Для этого подойдут обе рассматриваемые выше функции.

Для корректной работы DATEADD и PARALLELPERIOD мы не можем ссылаться на столбец дат, который находится в таблице продаж. Для них нужна совершенно отдельная таблица неразрывных дат «Календарь». В разборе синтаксисов функций я об этом писал (напоминаю, как создавать таблицы неразрывных дат (календари) разбирается в этой статье).

Итак, в модели данных я создал отдельный «Календарь» и связал его со столбцом [Дата Продаж] в таблице «Продажи»:

Как в Power BI и Power Pivot сдвинуть даты? DAX функции DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR

Теперь вы можете начать создавать формулы:

Обе формулы, которые мы записали выше, вычисляют сумму прибыли за предыдущий месяц:

  • за суммирование отвечает функция SUM, которая суммирует все значения в столбце [СуммаПродаж]
  • за перемещение периода отвечают функции DATEADD и PARALLELPERIOD, которые перемещают даты из календаря на 1 месяц назад, причем, напомню, «Календарь» связан с таблицей «Продажи» по столбцам дат
  • и, за само вычисление суммы, под условием перемещения дат на месяц назад, отвечает DAX функция CALCULATE, внутрь которой помещены все функции, участвующие в работе

Давайте посмотрим, как эти формулы ведут себя на рабочем столе Power BI:

Результат работы формул в Power BI Desktop на основе DAX функций DATEADD и PARALLELPERIOD

Для текущего месяца (например, февраля) формулы дают сумму продаж за предыдущий месяц (январь).

Две функции, DATEADD и PARALLELPERIOD, пока работают одинаково, так в чем же различия между ними?

А различие в том, что DATEADD — возвращает дату, которая была месяц назад. А PARALLELPERIOD весь период, который был месяц назад (в данном случае, период равен месяцу, так как в параметрах указан MONTH).

Давайте это подтвердим на нашем примере и в созданной матрице спустимся в иерархии дат до дней, тогда мы тут же заметим разницу в работе этих двух функций (для удобства демонстрации я создал две копии визуализаций: на первой показан январь по дням, на второй — февраль по дням):

Различие функций DATEADD и PARALLELPERIOD

То есть теперь, когда мы перешли к отображению дней в матрице, DATEADD возвращает для каждого текущего дня сумму продаж за тот же день, но за предыдущий месяц. А PARALLELPERIOD возвращает для каждого текущего дня не сумму продаж за тот же день из предыдущего месяца, а сумму продаж за весь предыдущий период (в данном случае месяц).

DAX функция SAMEPERIODLASTYEAR в Power BI и Power Pivot

SAMEPERIODLASTYEAR () — возвращает таблицу из дат, смещенных на 1 год назад относительно дат текущего контекста.

SAMEPERIODLASTYEAR — это фактически упрощенная версия функции DAX DATEADD, а именно упрощенная версия с определенными параметрами:

В Power BI эта функция нужна только для упрощения написания кода DAX.

Следующие формулы основаны на практическом применении этих двух функций:

Работа формул в Power BI на основе DAX функций DATEADD и SAMEPERIODLASTYEAR

S AMEPERIODLASTYEAR, как и DATEADD, выводит значение продаж за январь предыдущего года по формуле, основанной на SAMEPERIODLASTYEAR. Поэтому, если нам нужно вычислить значения за предыдущий год, мы должны просто использовать SAMEPERIODLASTYEAR, так как это быстрее и легче кодировать в Power BI.

В этой статье рассматриваются функции DAX DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR, которые перемещают период данных в прошлое или будущее в Power BI и Power Pivot.

Единственное, напоминаю, что для корректной работы этих функций, в качестве столбца дат требуется указывать совершенно отдельную в модели данных таблицу «Календарь» с неразрывными датами.

Оцените статью:

  1. 5
  2. 4
  3. 3
  4. 2
  5. 1

[Экспресс-видеокурс] Быстрый старт в языке DAX

Антон БудуевУспехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

В комментариях ниже, пожалуйста, задавайте вопросы по содержанию этой статьи. Я обязательно отвечу вам. И вообще, просто оставляйте там свои отзывы, я буду очень рад.

Не забудьте поделиться этой статьей с друзьями в социальных сетях, возможно, кому-то она будет полезна.

Читайте так же:
Как выполнить преобразование между формулой и текстом / значением

Понравился материал статьи?
Избранные закладкиДобавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

Что еще посмотреть / почитать?

DAX функция AVERAGE

Среднее значение в DAX: функции AVERAGE, AVERAGEA и AVERAGEX в Power BI и Power Pivot

Вывод таблицы ТОП через меру

Как вычислить таблицу Top 3 в Power BI с помощью меры вместо вычисляемой таблицы?

DAX функции DATE, DATEVALUE, YEAR, MONTH и DAY

Функции даты в DAX: DATE, DATE VALUE, ГОД, МЕСЯЦ, ДЕНЬ и СЕГОДНЯ в Power BI и Power Pivot

Еще одна интересная функция из серии дата-время — это ЧИСТРАБДНИ(), с ее помощью возможно посчитать Количество рабочих дней между датами. Но есть и многим неизвестная сторона этой функции: можно считать количество дней с учетом праздников. Даты праздников можно задать вручную. Это очень удобно. А так же рассмотрим функцию РАБДЕНЬ(), она поможет отсчитать кол-во рабочих дней от начальной даты. Подробнее о них внутри статьи.

Как считать количество рабочих дней между датами?

Чтобы подсчитать количество дней между двумя датами, можно вычесть из большей даты меньшую, ведь даты и время в Excel — это просто числа в нужном формате. Чтобы подсчитать это количество рабочих дней, существует специальная крайне полезная функция =ЧИСТРАБДНИ(). Как ей пользоваться?

Функция ЧИСТРАБДНИ. Реквизиты и пример

Для начала разберем состав функции и ее детали.

Состав реквизита можно увидеть на исходном рисунке

нач_дата — дата, с которой начинаем расчет.

кон_дата — дата, до которой нужно произвести расчет.

праздники — необязательный реквизит. Список дат, который надо исключить из рабочих дней календаря. Реквизит может состоять из диапазона дат или массива. Как пользоваться можно посмотреть ниже.

Вы можете использовать RABDEN для отсчета количества рабочих дней от заданной даты.

Функция РАБДЕНЬ. Реквизиты и пример

Количество рабочих дней между датами

Нач_дата — дата, с которой начинаем расчет.

число_дней — количество дней, которые должны пройти с начальной даты

праздники — такой же реквизит как и ЧИСТРАБДНИ

Учет праздников — скрытая супер возможность этой формулы, очень рекомендуем ее использовать:

Как считать рабочие дни с учетом праздников?

Для обоих функций, как вы заметили, есть реквизит ‘Праздники’. В нем можно указать, какие даты будут считаться праздниками (соответственно, не учитывая выходные). Например так:

chistrabdni2

Еще удобнее создать именованный пляж и дать ему курортное название, чтобы он хорошо выглядел.

Ввод даты в ячейку Excel

Цель этого урока — привести пример математических операций с датами. Кроме того, мы убедимся, что для Excel типом данных даты является число.

Заполните таблицу датами, показанными на рисунке:

Способы ввода дат.

Различные способы ввода дат. Колонка A — метод ввода, а колонка B — результат отображения.

Обратите внимание, что в формате ячеек по умолчанию «Общий» даты, а также числа выравниваются по правой стороне, а текст — по левой. Ячейка B4 распознается программой как текст.

В ячейке B7 Excel сам по умолчанию установил текущий год (в настоящее время 2015). Это видно, когда содержимое ячейки отображается в строке формул. Обратите внимание, как первоначально было введено значение в A7.

Глава 14. Управление датами в Power Pivot

Перевод книги Роба Колли продолжается в этой статье. Формулы DAX для Power Pivot. Рекомендуется читать главы по порядку, поскольку они не являются независимыми.

Различают стандартный и пользовательский календарь. Эта глава посвящена первому варианту. Под стандартным календарем мы понимаем календарь со следующими свойствами:

  • В феврале 28 дней (29 в високосные годы), а во всех остальных месяцах 30 или 31 день.
  • Кварталы состоят из трех последовательных месяцев
  • В году 365 дней (366 в високосном году)
  • В любом месяце этого года может быть больше или меньше выходных дней (или любых других дней), чем в том же месяце предыдущего года. Другими словами, стандартный календарь — это календарь, который вешается на стену.

Ris. 14.1. Fragment tablitsy Kalendar v Power Pivot

Рис. 14.1. Фрагмент таблицы «Календарь» в Power Pivot

Скачать записку в формате Word или pdf, примеры в формате Excel

Функции анализа времени Power Pivot работают в предположении, что используется стандартный календарь.

Читайте так же:
Как добавить дни к дате, включая или исключая выходные и праздничные дни в Excel?

С другой стороны, стандартное расписание создает множество, часто неприемлемых, проблем:

  1. Часто бывает «несправедливо» сравнивать этот месяц с предыдущим, например, когда в предыдущем месяце был 31 день, а в этом только 30. Действительно ли мы на 3% хуже в этом месяце, или это просто из-за другого количества дней?
  2. Даже два месяца одинаковой продолжительности часто не подходят для сравнения, потому что в них разное количество выходных дней.
  3. Иногда единица измерения не совпадает с настенным календарем, например, семестр в науке и сезон в спорте.
  4. Иногда (например, в науке) мы хотим сравнить именно одинаковые периоды времени, а не календарные периоды.

По меньшей мере половина организаций оценивают себя с помощью пользовательских календарей. Розничные компании, например, очень чувствительны к первым двум вопросам. Поэтому мы поговорим о пользовательском календаре позже.

Создание календаря

При работе с датами создайте отдельную таблицу в Power Pivot (ее не обязательно называть календарем, но так удобнее). Существует множество способов его создания. Вот несколько вариантов:

  1. Импорт из базы данных, например, из этой.
  2. Создайте в Excel, затем загрузите в Power Pivot. Может возникнуть проблема, если вам нужен динамический календарь (например, на текущую дату). Используйте генератор календаря в Excel (требуется русификация).
  3. Генерировать с помощью Power Query, см. здесь и здесь.
  4. Импортируйте календарь из Azure DataMarket (или из любого другого места в Интернете). Существует по крайней мере одна электронная таблица календаря, доступная для бесплатного скачивания, созданная Бояном Пеневом, см. здесь.

Свойства таблицы Календарь

  • Содержит как минимум один столбец с типом данных Дата.
  • Содержит одну строку для каждого дня.
  • Содержит все последовательные даты, без пробелов (даже если вы не работаете по выходным, эти дни должны быть в календаре).
  • Связан с таблицей данных (например, Sales)
  • Содержит множество столбцов для всех мыслимых группировок и меток, например, номер недели/месяца/квартала, день недели, праздник, выходной и т.д. (но если вы минималист, ничто не мешает вам ограничиться одним столбцом с датами).
  • В идеале, охватите только соответствующие даты. Если ваш бизнес открылся в 2001 году, зачем начинать календарь с 2000 года? А если сегодня 13 января 2019 года, то какой смысл иметь на календаре 14 января 2019 года? Это одно из самых сложных требований. Поэтому мы предпочитаем получать календарь из базы данных. Это необязательно, но со временем пригодится.

Итак, наш календарь показан на рисунке 14.1. Давайте свяжем его с таблицей Sales, используя колонки Date:

Ris. 14.2. Ustanovlena svyaz tablitsy Sales s Calendar

Рис. 14.2. Таблица продаж связана с Календарем; чтобы увеличить изображение, щелкните на нем правой кнопкой мыши и выберите Открыть изображение в новой вкладке

OrderDate в таблице Sales должен содержать тип данных «дата» (но это не обязательно). В то же время столбец Date в таблице Calendar должен иметь тип данных «дата».

Ris. 14.3. Obnovlennyj vid diagrammy svyazej

Рис. 14.3. Обновленный вид диаграммы связей; календарь теперь является третьей таблицей поиска (подстановка)

Поиск в календаре работает так же, как и в обычных таблицах поиска:

Ris. 14.4. Svodnaya tablitsa prodazh po dnyam nedeli

Рисунок 14.4: Итоговая таблица продаж по дням недели

Но здесь есть свои особенности. Чтобы правильно фильтровать данные с помощью таблицы Календаря, необходимо открыть таблицу в Power Pivot, выбрать вкладку Конструктор и нажать кнопку Отметить как таблицу дат. В результате вы сможете применять специальные параметры фильтрации для дат в сводной таблице в Excel:

Ris. 14.5. Power Pivot soobshhil Excel chto eto tablitsa dat

Рисунок 14.5. Power Pivot сообщил Excel, что это таблица дат, поэтому Excel контекстно предложил «правильные» фильтры.

Если вы собираетесь использовать столбец другого типа данных (не дату) в таблице данных (например, Sales) для ссылки на календарь, вы должны пометить его как столбец даты, иначе многие функции, описанные ниже, не будут работать:

Ris. 14.6. Stolbtsu OrderDate v tablitse Sales prisvoen tip dannyh Data

Рисунок 14.6: Столбцу OrderDate в таблице Sales был назначен тип данных

В дополнение к таблице Календарь вы также найдете таблицу События. Power Pivot предлагает множество функций, связанных со временем:

Ris. 14.7. Funktsii DAX otnosyashhihsya k date i vremeni

Рис. 14.7. Функции даты и времени DAX; некоторые из них совпадают с обычными функциями Excel, но большинство из них являются новыми

Давайте начнем с функции DATESYTD(). Первоначальная сводная таблица :

Ris. 14.8. Prodazhi po mesyatsam za 2004 g.

Рисунок 14.8: Объем продаж в месяц в 2004 году

Добавьте новый показатель, который подсчитывает продажи с начала года по текущую дату:

[Total Sales YTD] =CALCULATE ([Total Sales]; DATESYTD(Calendar[Date]))

Читайте так же:
Как вывести все содержимое ячейки в Excel?

Ris. 14.9. Novaya mera pokazyvaet prodazhi narastayushhim itogom

Рис. 14.9. Новый показатель показывает продажи нарастающим итогом

Как это работает? Показатель рассчитывается для каждой ячейки сводной таблицы отдельно, как обычно. Например, для значения, выделенного на рисунке выше, DATESYTD() определяет последнюю дату в текущем контексте фильтра (31 марта 2004 года), а затем «разворачивает» контекст фильтра назад от этой даты к первой дате года (01 января 2004 года). DATESYTD() затем изменяет контекст фильтра: «взять продажи за период 01.01-31.03.2004».

Удалите элемент [Всего продаж] и перетащите год в область столбцов:

Ris. 14.10. Mera vedet sebya ne sovsem kak ozhidalos no v tselom logichno

Рисунок 14.10. Итоги строк ведут себя не совсем так, как ожидалось, но в целом логично.

Синтаксис функции DATESYTD()

DATESYTD([Дата]; «Конец Года»)

[Дата] – столбец из дат или выражений, возвращающих даты; «Конец Года» – текстовая дата, записанная в виде «01/05» («день/месяц»). Определяет дату окончания года (по умолчанию 31 декабря). Необязательный параметр. Он позволяет, например, рассматривать 30 июня как последний день года, что иногда встречается в финансовых календарях:

Как добавить или вычесть дату и время в Microsoft Excel?

Вы, вероятно, знаете, как легко складывать и вычитать числа в Excel. Вы когда-нибудь задавались вопросом, как добавить или вычесть дату и время в Microsoft Excel? Excel хранит дату и время в виде порядковых номеров и использует их в расчетах. Поэтому, если вы понимаете, как Excel работает с датой и временем, вам будет легко понять, как использовать их в расчетах. В этой статье мы рассмотрим, как использовать функции даты и времени, а также как добавить или вычесть дату и время в Microsoft Excel.

Сериализация информации о дате и времени в Excel

В Excel используются две системы дат: одна начинается с 1 января 1900 года, а другая — с 1 января 1904 года. Последние версии Excel, как для Windows, так и для Mac, по умолчанию используют систему дат 1900. В этой статье мы объясним все функции с системой дат 1900. Процесс вычислений остается таким же, даже если вы используете систему дат 1904, в то время как внутренняя сериализация дат в Excel может измениться.

При использовании системы дат 1900 года Excel устанавливает дату на 01.01.1900 (1 января 1900 года) и присваивает номер сериализации 1. В этом примере 01.10.1900 (10 января 1900 года) будет преобразовано в 10 в Excel, а 31.03.2018 (31 марта 2018 года) будет сериализовано как 43190.

Точно так же информация о времени сохраняется внутри как десятичная. Например, 0,5 означает полдень, 0,25 — 6 утра и 0,75 — 6 вечера в Excel.

Теперь давайте посчитаем 31 марта 2018 года, в 9:45 утра. Откройте рабочий лист Excel и введите в ячейку 31 марта 2018 года. Щелкните правой кнопкой мыши и выберите Формат ячеек….. На вкладке Число выберите формат даты (за исключением времени) для этой ячейки. В другой ячейке введите 31-март-2018 09:45:00 и отформатируйте эту ячейку как дату с временем.

Теперь скопируйте эти две ячейки и вставьте их во второй ряд. Выделите обе ячейки, щелкните правой кнопкой мыши и отформатируйте их как простые числа. Вы можете выбрать отсутствие десятичных знаков для первой ячейки и 5 десятичных знаков для второй ячейки. Оно должно выглядеть так, как показано ниже, 31 марта 18 9:45 = 43190.40625 в Excel.

Преобразование даты в числа

Преобразование дат в числа

Просто введите 43190.40625 в ячейку и выберите формат даты с временем. В результате Excel автоматически преобразует число в формат даты в 9:45 утра 31 марта 18 года.

Помните, что число без десятичной точки в формате времени считается как 00:00:00. В Excel временем считается только десятичное число.

Формат даты, числа и времени в Excel

Формат даты, номера и времени в Excel

Расчет даты и времени в Excel

Мы добавим несколько дней к существующей дате. В приведенном выше примере, добавив десять дней к 01/01/2018, вы получите 1/11/2018. Введите 01/01/2018 в ячейку B3, введите = 10 + B3 в ячейку B4 и нажмите Enter. Вы увидите результат 1/11/2018.

Добавление дней к дате

Добавление дней к дате

Простое добавление двух дат может привести к абсолютному сложению в Excel, что не имеет смысла. Например, если вы добавите 31/31/18 и 3/1/18, получится 4/2/36. Но вы можете вычесть две даты и показать разницу дней в числовом формате. Например, 3/1/18 — 1/31/18 покажет результат как 29 дней.

Читайте так же:
Как выбрать случайные ячейки из списка в таблицах Google?

Вычитание двух дат

Вычитание двух дат

Как и в случае с датами, вы можете складывать или вычитать две ячейки в формате времени. Убедитесь, что вы всегда вычитаете меньшее значение из более высокого значения, а формат ячеек соответствует времени. Например, 18:00 — 15:00 должно привести к 3:00.

Сложение и вычитание времени

Сложение и вычитание времени

Вместо того чтобы работать со ссылкой на ячейку, вы можете использовать прямую дату или время в кавычках для выполнения расчета, как показано ниже:

= «24.12.2018 ″ -« 12.05.2018 ″ (в результате вы получите 19)
= «20:00» — «C9» (в результате вы увидите 18:45, поскольку ячейка C9 имеет значение 1:15)

Использование СЕЙЧАС () и СЕГОДНЯ () для расчетов

Кроме того, вы можете использовать две функции для определения даты и времени. Now () используется для добавления или вычитания времени из текущего времени, а Today () используется для добавления или вычитания даты из текущей даты. Вот несколько примеров:

Вы должны отформатировать ячейку в формате времени и поместить время сложения или вычитания в двойные кавычки, прежде чем использовать функцию NOW ().

= NOW () возвращает текущее время

= СЕЙЧАС () — «02:00 ″ вычитает два часа из текущего времени.

= СЕЙЧАС () + «03:00» добавит три часа к текущему времени.

Прежде чем использовать TODAY (), убедитесь, что ячейка отформатирована в формате даты:

= TODAY () возвращает значение текущей даты.

= СЕГОДНЯ () — 3 вычитает три дня из сегодняшнего дня

= TODAY () + 5 прибавит пять дней к сегодняшнему дню

Основные функции даты

В Microsoft Excel есть следующие функции даты — СЕГОДНЯ (), ГОД (), МЕСЯЦ (), ДЕНЬ () и ДЕНЬ НЕДЕЛИ (). Пример использования показан ниже. Введите функцию = СЕГОДНЯ () и нажмите клавишу ввода в ячейке C4. Вы можете использовать все остальные функции со ссылкой на ячейку C4.

Функции даты в Excel

Функции работы с данными в Excel

Функция WEEKDAY() имеет дополнительный тип возвращаемого параметра. Это необходимо для определения первого рабочего дня недели. Если вы введете = WEEKDAY (C4,2) в примере выше, то в качестве ответа получите 1. Это потому, что 2 означает, что неделя начинается с понедельника по воскресенье. Поэтому в управляющей ячейке понедельник возвращается как 1, что означает, что это первый рабочий день.

Функция буднего дня Excel

Функция «Глаз» в Excel

Существует также функция DAYS (), которая вычисляет количество дней между любыми двумя датами. Синтаксис: = DAYS (конечная_дата, начальная_дата).

Основные функции времени

Подобно функциям даты, Excel также поддерживает различные функции времени — СЕЙЧАС (), ЧАС (), МИНУТА () и СЕКУНДА ().

Функции времени в Excel

Функции времени в Excel

Функции ДАТА и ВРЕМЯ

Функция ДАТА просто используется для сбора года, месяца и даты из разных ссылочных ячеек и отображения их в формате даты. Точно так же ВРЕМЯ — это функция для объединения часов, минут и секунд из разностных эталонных ячеек.

Пример функции даты и времени

Пример функции «Дата и время

Использование функции DATEDIF для вычитания даты в Excel

Помимо всех формальных функций даты и времени, Excel также поддерживает неформальную функцию DATEDIF. Согласно документу поддержки Microsoft, эта функция все еще работает для обработки старых книг, перенесенных в Excel из Lotus 1-2-3. Однако она может давать ошибочные результаты в различных ситуациях, поэтому ее использование не рекомендуется.

Если вы хотите узнать, сколько лет, месяцев или дней находится между двумя датами, то можно воспользоваться функцией DIVIDE. Синтаксис выглядит следующим образом:

= ДИСТАНЦИЯ (дата начала, дата окончания, единица измерения)

Стоимость единицы продукции может быть одной из следующих величин:

  • Y — количество полных лет между начальной и конечной датами.
  • M — количество полных месяцев
  • D — количество дней
  • MD — количество дней между начальной и конечной датами без учета лет и месяцев. Это значение обычно дает неверные результаты.
  • YM — количество месяцев между датами начала и окончания, без учета дней и лет.
  • YD — количество дней между двумя заданными датами без учета лет.

Используйте DATEDIF для вычитания дат:

Использование функции РАЗНДАТ в Excel

Использование функции СОЕДИНЕНИЕ в Excel

Поскольку DATEDIF является вспомогательной функцией для целей миграции, при вводе функции в ячейку не появляется текст помощи формулы.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector