Eurotehnik.ru

Бытовая Техника "Евротехник"
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как сделать диапазон дат в excel

Как сделать диапазон дат в excel?

Исходные: A1 — начальная дата, В1 — длительность проекта в днях.

Протянуть формулу вниз.

Формулы строк, которые ниже требуемого диапазона дат, покажут 0 (ноль).

Нули можно скрыть:

закладка Файл-Параметры-Дополнительно-Для_листа…, снять галку отображения нулевых значений.

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

Для периода, заданного в месяцах или годах, нужно определить количество дней в периоде или последнюю дату.

Например, для месяцев полная формула для А2:

Если показывать только даты помесячно:

Для уменьшения вычислений определение последней даты лучше вывести в отдельню ячейку и в формулах ссылаться на нее. Вариант:

Макросом.

Исходные: A1 — начальная дата, В1 — длительность проекта в месяцах.

Диапазон вставки: столбец Е — порядковый номер, столбец F — дата.

Формируется список дат с шагом 1 месяц:

Глава 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 работают в предположении, что используется стандартный календарь.

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

  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 г? Это одно из самых сложных требований. Вот почему нам нравится получать календарь из базы данных. Это необязательно, но со временем вы найдете это очень полезным.
Читайте так же:
Где находятся стили в фотошопе cs6

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

Ris. 14.2. Ustanovlena svyaz tablitsy Sales s Calendar

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

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

Ris. 14.3. Obnovlennyj vid diagrammy svyazej

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

Календарь работает, как обычная таблица поиска:

Ris. 14.4. Svodnaya tablitsa prodazh po dnyam nedeli

Рис. 14.4. Сводная таблица продаж по дням недели

Но есть и особенности. Во-первых, чтобы фильтрация данных с помощью таблицы Calendar была корректной, активируйте таблицу в Power Pivot, перейдите на вкладку Конструктор и кликните Пометить как таблицу дат. После этого в сводной таблице в Excel при выборе фильтра, вы получите специальные параметры фильтрации дат:

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

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

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

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

Рис. 14.6. Столбцу OrderDate в таблице Sales присвоен тип данных Дата

Вторая особенность таблицы Calendar. 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]))

Ris. 14.9. Novaya mera pokazyvaet prodazhi narastayushhim itogom

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

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

Удалите [Total Sales] и перетащите год в область Столбцы:

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

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

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

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

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

Читайте так же:
Где в ворде карандаш для рисования

Ограничение таблиц из дат в Power BI и Power Pivot: DAX функции DATESBETWEEN и DATESINPERIOD

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

Антон БудуевПриветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы обсудим DAX функции DATESBETWEEN и DATESINPERIOD, создающие в Power BI и PowerPivot ограниченные таблицы из дат.

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

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

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

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

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

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

DATESBETWEEN () — ограничитель календаря по произвольным датам. Создает ограниченную таблицу из дат, начиная со стартовой и заканчивая конечной датами, расположенными в исходном столбце.

  • [Дата] — столбец дат в исходной таблице
  • Стартовая и Конечная Даты — дата в формате datetime

! — Стартовые и конечные даты, указанные в параметрах функции, входят в состав выводимых дат.

Пример формулы на основе DAX функции DATESBETWEEN.

В Power BI имеется исходная таблица «Продажи», содержащая даты и сумму продаж за 2 года с 1.01.2017 по 31.12.2018. Но, продажи имеются не за каждый день. Обратите внимание, что 30 декабря 2018 года отсутствует, потому что продаж не было:

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

В определенных расчетах нам могут понадобиться даты этих продаж, но не все, а ограниченные, например, только за 2018 год. Ограничить даты можно многими способами, начиная от пользовательских фильтров в разделе «Отчеты» в Power BI Desktop, заканчивая разнообразными формулами и функциями внутри самого языка DAX.

Так как в этой статье мы разбираем DATESBETWEEN, то давайте разберем пример ограничения при помощи этой функции, тем более, что она, как раз, для этого и существует.

Создадим во вкладке «Моделирование» в Power BI Desktop вычисляемую таблицу по следующей формуле на основе DATESBETWEEN:

Этой формулой мы создали таблицу с датами продаж, ограниченными только 2018 годом:

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

Обратите внимание, что DATESBETWEEN возвратила именно тот набор дат, который был в исходном столбце, то есть, 30 декабря 2018 года в этой таблице тоже нет.

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

DATESINPERIOD () — ограничитель календаря по типовым интервалам. Создает ограниченную таблицу из дат, начиная со стартовой даты, расположенной в исходном столбце и продолжая в течение указанного типового интервала (количеством дней, месяцев, кварталов, лет).

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

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

Продолжая рассматривать практический пример, который мы разбирали с первой функцией выше, давайте напишем, формулу, ограничивающую даты продажи 2018 годом, но теперь, на основе DAX функции DATESINPERIOD:

В первом параметре мы указали исходный столбец, во втором — стартовую дату, с которой нужно начать ограничение, в третьем — количество периодов, в нашем случае, 12. Ну и, в четвертом — наименование самого периода — я выбрал «MONTH», то есть, месяц.

Результатом выполнения этой формулы на основе DATESINPERIOD, получился столбец из дат, начиная с 1 января 2018 года и далее, продолжая на период в 12 месяцев, до 31 декабря 2018 года:

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

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

Сами по себе DATESBETWEEN и DATESINPERIOD обычно просто так не используют. А вставляют их в другие формулы в сочетании с другими функциями.

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

На этом, с функциями ограничивающими таблицу дат в Power BI и Power Pivot, в этой статье все.

Пожалуйста, оцените статью:

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

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

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

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

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

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

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

DAX функции GROUPBY и SUMMARIZECOLUMNS

Функции сводных таблиц в DAX: GROUPBY и SUMMARIZECOLUMNS в Power BI и Power Pivot

DAX функции MAX и MIN

DAX функции MAX, MAXA, MAXX и MIN, MINA, MINX (для Power BI и Power Pivot)

DAX функции FIND и SEARCH в Power BI

Как в Power BI (Power Pivot) найти текст в составе другого текста? DAX функции FIND и SEARCH

Ввод и изменение длительности (единиц времени)

Для ввода и быстрого редактирования единиц времени в ячейках можно использовать клавиатуру длительности. Например, 3ч 40м 15с (или 3 часа 40 минут 15 секунд).

Коснитесь ячейки, затем (если клавиатура скрыта) коснитесь кнопки «Показать клавиатуру»внизу экрана.

Коснитесь кнопки клавиатуры «Дата и время»в верхней части клавиатуры, затем коснитесь «Длительность» в левой части клавиатуры.

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

Например, чтобы ввести «2 недели», коснитесь «2», затем коснитесь «W».

Чтобы изменить значения, коснитесь значка «+» или «-» на кнопке в верхней части клавиатуры для единицы, которую нужно изменить (неделя, день, время и т. д.).

Читайте так же:
Мил ру личный кабинет военнослужащего войти

При каждом касании значение единицы увеличивается на 1.

Повторите эти действия для каждого значения, которое хотите изменить.

Чтобы перейти к другой ячейке, коснитесь клавиша следующей строкиили клавиша следующего столбца/

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

Как показано на скриншоте ниже, как можно рассчитать интервал дней, недель, месяцев или лет между двумя заданными датами в Excel? Эта статья покажет вам формулы для этого.

Вычислить интервал дней / недель / месяцев / лет между двумя датами с помощью формул

Пожалуйста, примените приведенные ниже формулы для вычисления интервала дней, недель, месяцев или лет между двумя заданными датами в Excel.

Рассчитать интервал дней

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

=DATEDIF(A2, B2, «d»)

Рассчитать интервал недель

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

=ROUNDDOWN((DATEDIF(A2, B2, «d») / 7), 0)

Рассчитать интервал месяцев

Для расчета интервала в месяцах между двумя заданными датами, пожалуйста, примените формулу ниже.

=DATEDIF(A2, B2, «m»)

Рассчитать интервал лет

Эта формула может помочь для получения интервальных лет.

=DATEDIF(A2,B2,»y»)

Внимание: В формулах ячейки A2 и B2 содержат заданную дату начала и дату окончания. Пожалуйста, измените их по своему усмотрению.

Легко рассчитайте интервалы дней / месяцев / лет между двумя датами с помощью удивительного инструмента

Здесь рекомендую Помощник по дате и времени особенность Kutools for Excel. С помощью этой функции вы можете легко рассчитать интервал дней, месяцев или лет между двумя датами всего за несколько кликов.

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

1. Выберите пустую ячейку для вывода результата, а затем щелкните Kutools > Формула Помощник > Помощник по дате и времени. Смотрите скриншот:

2. в Помощник по дате и времени диалоговое окно, настройте следующим образом.

Советы: Если вы хотите рассчитать интервальные дни, выберите день вариант, для расчета интервальных месяцев выберите месяц, для расчета интервальных лет выберите год.

3. Затем результат заносится в выбранную ячейку, а формула создается автоматически. Вы можете перетащить маркер заполнения в другие ячейки, чтобы получить результаты.

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

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

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

Чтобы ввести дату в Excel, используйте символы «/» или «-«. Чтобы ввести время, используйте «:» (двоеточие). Вы можете ввести дату и время в одной ячейке.

Примечание: На рисунках данной статьи представлены даты в формате США и ряда других стран, где сперва идут месяцы, а затем дни. Тип формата зависит от ваших региональных настроек Windows. Здесь вы можете получить дополнительную информацию о форматах даты и времени.

Как Excel обрабатывает время

Программа Excel «воспринимает» дату и время как обычное число. Электронная таблица преобразует подобные данные, приравнивая сутки к единице. В результате значение времени представляет собой долю от единицы. К примеру, 12.00 — это 0,5.

Читайте так же:
Двухуровневый выпадающий список в excel

Значение даты электронная таблица преобразует в число, равное количеству дней от 1 января 1900 года (так решили разработчики) до заданной даты. Например, при преобразовании даты 13.04.1987 получается число 31880. То есть от 1.01.1900 прошло 31 880 дней.

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

Пример функции ДАТА

Построение значение даты, составляя его из отдельных элементов-чисел.

Синтаксис: год; месяц, день.

Все аргументы обязательные. Их можно задать числами или ссылками на ячейки с соответствующими числовыми данными: для года — от 1900 до 9999; для месяца — от 1 до 12; для дня — от 1 до 31.

Если для аргумента «День» задать большее число (чем количество дней в указанном месяце), то лишние дни перейдут на следующий месяц. Например, указав для декабря 32 дня, получим в результате 1 января.

Пример использования функции:

Зададим большее количество дней для июня:

Примеры использования в качестве аргументов ссылок на ячейки:

Функция РАЗНДАТ в Excel

Возвращает разницу между двумя датами.

  • начальная дата;
  • конечная дата;
  • код, обозначающий единицы подсчета (дни, месяцы, годы и др.).

Способы измерения интервалов между заданными датами:

  • для отображения результата в днях — «d»;
  • в месяцах — «m»;
  • в годах — «y»;
  • в месяцах без учета лет — «ym»;
  • в днях без учета месяцев и лет — «md»;
  • в днях без учета лет — «yd».

В некоторых версиях Excel при использовании последних двух аргументов («md», «yd») функция может выдать ошибочное значение. Лучше применять альтернативные формулы.

Примеры действия функции РАЗНДАТ:

В версии Excel 2007 данной функции нет в справочнике, но она работает. Хотя результаты лучше проверять, т.к. возможны огрехи.

Функция ГОД в Excel

Возвращает год как целое число (от 1900 до 9999), который соответствует заданной дате. В структуре функции только один аргумент — дата в числовом формате. Аргумент должен быть введен посредством функции ДАТА или представлять результат вычисления других формул.

Пример использования функции ГОД:

Функция МЕСЯЦ в Excel: пример

Возвращает месяц как целое число (от 1 до 12) для заданной в числовом формате даты. Аргумент — дата месяца, который необходимо отобразить, в числовом формате. Даты в текстовом формате функция обрабатывает неправильно.

Примеры использования функции МЕСЯЦ:

Примеры функций ДЕНЬ, ДЕНЬНЕД и НОМНЕДЕЛИ в Excel

Возвращает день как целое число (от 1 до 31) для заданной в числовом формате даты. Аргумент — дата дня, который нужно найти, в числовом формате.

Чтобы вернуть порядковый номер дня недели для указанной даты, можно применить функцию ДЕНЬНЕД:

По умолчанию функция считает воскресенье первым днем недели.

Для отображения порядкового номера недели для указанной даты применяется функция НОМНЕДЕЛИ:

Дата 24.05.2015 приходится на 22 неделю в году. Неделя начинается с воскресенья (по умолчанию).

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

Для указания текущей даты используется функция СЕГОДНЯ (не имеет аргументов). Чтобы отобразить текущее время и дату, применяется функция ТДАТА ().

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