Eurotehnik.ru

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

Как определить день недели по дате в Excel

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

  1. К примеру, мы имеет ячейку таблички с указанием какой-либо определенной даты.
  1. Жмем по этой ячейке ПКМ. На экране отобразилось небольшое контекстное меню. Находим элемент под названием «Формат ячеек…» и щелкаем по нему левой кнопкой мышки.
  1. Мы оказались в окошке под названием «Формат ячеек». Перемещаемся в раздел «Число». В небольшом списке «Числовые форматы» выбираем пункт «(все форматы)». Смотрим на надпись «Тип:». Жмем левой кнопкой мыши на поле для ввода, располагающееся ниже этой надписи. Вбиваем сюда следующее значение: «ДДДД». После проведения всех манипуляций жмем на «ОК».
  1. Готово! В итоге мы сделали так, что дата в ячейке таблички превратилась в наименование для недели. Выбираем нажатием левой кнопки мышки эту ячейку и смотрим в строчку для ввода формул. Здесь отображается сама исходная дата.

Важно! Можно заменить значение «ДДДД» на «ДДД». В результате в ячейке будет выведен день в сокращенном виде. Предварительный просмотр можно осуществить в окошке редактирования в строчке под названием «Образец».

kak-opredelit-den-nedeli-po-date-v-excel

5

1. Отображение дня недели через формат ячеек

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

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

  1. Например, у нас есть ячейка таблицы, в которой указана какая-то конкретная дата.Отображение дня недели через формат ячеек
  2. Щелкаем по ячейке с датой правой кнопкой мыши. Затем в появившемся списке команд кликаем по строке “Формат ячеек…”.Отображение дня недели через формат ячеек
  3. В открывшемся окне переходим во вкладку “Число” (если вдруг по какой-то причине мы сразу не оказались в ней). В перечне “Числовые форматы” кликаем по варианту “(все форматы)”. После этого обращаем внимание на поле “Тип”, кликаем по области ввода значений и пишем в ней “ДДДД” (без кавычек). После этого нажимаем OK, чтобы сохранить изменения и закрыть окно форматирования.Отображение дня недели через формат ячеек
  4. В результате дата в ячейке таблицы будет преобразована в название дня недели согласно календарю. Для того, чтобы увидеть саму дату, отмечаем эту ячейку, и в поле для ввода формул будет отображена исходное значение в формате даты.Отображение дня недели через формат ячеек

Примечание: Вместо “ДДДД” можно задать формат “ДДД” (т.е. 3 буквы, а не 4), и в этом случае день недели будет выведен в ячейку в сокращенном виде. Посмотреть, как это будет выглядеть в итоге можно сразу же в окне форматирования в поле “Образец”.

Отображение дня недели через формат ячеек

Способ 1. Неделя содержит первый четверг года

Давайте начнем с более популярного варианта, а именно, посчитаем номер недели для российского календаря.

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


Алгоритм расчета:

  • К вычисляемой дате (напомню, что дата в Excel хранится в числовом виде) прибавляем 3 дня и вычитаем номер дня недели (1 для понедельника, 2 для вторника и т.д.); Этим шагом мы добьемся, что для понедельника, вторника и среды вычисляемая дата увеличится, а для остальных дней (четверга, пятницы, субботы и воскресенья) уменьшится, что и позволит добиться включения первого четверга года в первую неделю.
  • Далее возьмем остаток от деления на 365,25 (средняя длительность года с учетом високосного года); Этой операцией мы получим количество дней в текущем году для вычисляемой даты.
  • Делим полученный результат делим на 7; Переводим дни в недели.
  • Прибавляем 1 и берем целую часть числа.

Начиная с версии Excel 2013 добавлена стандартная функция НОМНЕДЕЛИ.ISO (по стандарту ИСО), которую можно применить вместо выше приведенной формулы.
Синтаксис и описание функции НОМНЕДЕЛИ.ISO (в английской версии WEEKNUM.ISO):

НОМНЕДЕЛИ.ISO(дата)
Возвращает номер недели в году по стандарту ISO для указанной даты.

  • Дата(обязательный аргумент) — код даты-времени.

Проверим работу функции на тех же датах, что и в предыдущем примере:


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

Способ 1. Неделя содержит первый четверг года

Давайте начнем с более популярного варианта, а именно, посчитаем номер недели для российского календаря.

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

Формула номера недели по дате

Алгоритм расчета:

  • К вычисляемой дате (напомню, что дата в Excel хранится в числовом виде) прибавляем 3 дня и вычитаем номер дня недели (1 для понедельника, 2 для вторника и т.д.); Этим шагом мы добьемся, что для понедельника, вторника и среды вычисляемая дата увеличится, а для остальных дней (четверга, пятницы, субботы и воскресенья) уменьшится, что и позволит добиться включения первого четверга года в первую неделю.
  • Далее возьмем остаток от деления на 365,25 (средняя длительность года с учетом високосного года); Этой операцией мы получим количество дней в текущем году для вычисляемой даты.
  • Делим полученный результат делим на 7; Переводим дни в недели.
  • Прибавляем 1 и берем целую часть числа.

Начиная с версии Excel 2013 добавлена стандартная функция НОМНЕДЕЛИ.ISO (по стандарту ИСО), которую можно применить вместо выше приведенной формулы.
Синтаксис и описание функции НОМНЕДЕЛИ.ISO (в английской версии WEEKNUM.ISO):

НОМНЕДЕЛИ.ISO(дата)
Возвращает номер недели в году по стандарту ISO для указанной даты.

  • Дата(обязательный аргумент) — код даты-времени.

Проверим работу функции на тех же датах, что и в предыдущем примере:

Функция НОМНЕДЕЛИ.ISO (WEEKNUM.ISO)

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

DAX функции YEARFRAC, WEEKDAY и WEEKNUM в Power BI и PowerPivot

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

Антон БудуевПриветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы рассмотрим 3 функции, которые относятся к категории функций дат и времени в DAX — YEARFRAC, WEEKDAY и WEEKNUM в Power BI и PowerPivot.

Рассмотрим каждую функцию в отдельности.

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

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

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

DAX функция YEARFRAC в Power BI и PowerPivot

YEARFRAC () — вычисляет долю указанного периода дат в целом году.

Где, «Стартовая Дата» и «Конечная Дата» — даты в формате datetime, а «Базис» — способ вычисления дня (необязательный параметр).

  • параметр = 0 — американский стандарт (NASD), 30 дней / 60 дней
  • параметр = 1 — фактический период / фактический период
  • параметр = 2 — фактический период / 360 дней
  • параметр = 3 — фактический период / 365 дней
  • параметр = 4 — европейский стандарт, 30 дней / 360 дней

Рассмотрим пример формулы на основе DAX функции YEARFRAC.

В данной формуле начальную и конечную даты мы задали при помощи DAX функции DATE. Период между этими двумя датами составляет ровно 1 месяц. В качестве базиса (способа вычисления дня) я взял европейский стандарт (4).

Как результат, формула на основе YEARFRAC вывела долю этого периода относительно целого года = 8.33 %:

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

У созданной меры в Power BI Desktop нужно поменять формат на % и вывести два знака после запятой.

DAX функция WEEKDAY в Power BI и PowerPivot

WEEKDAY () — возвращает день недели в формате чисел 1-7 (0-6). По умолчанию неделя начинается с воскресенья (1) и заканчивается субботой (7).

  • Дата — дата в формате datetime. Дату необходимо вводить формулой DATE() или другим выражением, возвращающим формат datetime
  • Начало Недели (по умолчанию — 1):
    1 — начало недели в воскресенье (1) и конец в субботу (7) ;
    2 — начало недели в понедельник (1) и конец в воскресенье (7);
    3 — начало недели в понедельник (0) и конец в воскресенье (6)

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

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

В Power BI имеется исходная таблица с датами, где 1 января 2018 — понедельник:

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

Добавим в эту таблицу 2 вычисляемых столбца на основе следующих формул с участием DAX функции WEEKDAY:

То есть, в первой формуле начало недели начинается в воскресенье (1), а во второй формуле — в понедельник (1).

В итоге, результат будет таким:

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

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

DAX функция WEEKNUM в Power BI и PowerPivot

WEEKNUM () — возвращает номер недели года (относительно начала года).

  • Дата — дата в формате datetime. Дату необходимо вводить формулой DATE() или другим выражением, возвращающим формат datetime
  • Начало Недели:
    1 — неделя начинается с воскресенья (по умолчанию);
    2 — неделя начинается с понедельника

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

Как и в функции WEEKDAY, в качестве параметров даты в WEEKNUM, также можно вставить столбец со значениями даты. Давайте рассмотрим такой пример формулы.

В Power BI Desktop имеется исходная таблица с датами, где 1 апреля 2018 — это воскресенье:

Исходная таблица в Power BI Desktop

Добавим в эту таблицу 2 вычисляемых столбца на основе следующих формул с участием DAX функции WEEKNUM:

Исходя из синтаксиса, в первой формуле начало недели начинается в воскресенье, а во второй формуле — в понедельник.

В итоге, результат будет таким:

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

Так как в столбце, рассчитанном на основе первой формулы, начало недели в воскресенье, а в таблице у нас представлены 3 дня — воскресенье (1 апреля), понедельник (2 апреля), вторник (3 апреля), то у этих всех 3-х дней один номер недели = 14.

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

На этом, с разбором функций YEARFRAC, WEEKDAY и WEEKNUM в Power BI и PowerPivot, в данной статье все.

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

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

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

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

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

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

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

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

DAX функции TOTALYTD, TOTALQTD и TOTALMTD в Power BI

TOTALYTD, TOTALQTD и TOTALMTD — DAX функции категории time intelligence, вычисляющие выражение в текущем периоде в Power BI и Power Pivot

DAX функции MAX и MIN

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

DAX функции VALUES и DISTINCT

Функции таблиц уникальных значений в DAX: VALUES и DISTINCT в Power BI и Power Pivot

Первый день месяца в Excel

Для формулы, преобразующей текущую или другую дату в первый день месяца, используем функции «ДАТА», «ГОД» и «МЕСЯЦ»:

  • Первый день текущего месяца от текущей даты: =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1)
  • Первый день прошлого месяца от текущей даты: =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())-1;1)
  • Первый день текущего месяца предыдущего года от текущей даты: =ДАТА(ГОД(СЕГОДНЯ())-1;МЕСЯЦ(СЕГОДНЯ());1)
  • Первый день месяца от произвольной даты, записанной в ячейку «A1»: =ДАТА(ГОД(A1);МЕСЯЦ(A1);1)

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

Последний день месяца в Excel

Для определения последнего месяца от текущей или произвольной даты в Excel, начиная с версии 2007 года, существует функция «КОНМЕСЯЦА» с двумя аргументами: «Начальная дата» и «Количество месяцев». Если «Количество месяцев» равно 0, то будет возвращен последний день того месяца, который содержится в аргументе «Начальная дата».

Примеры для Excel 2007-2016:

  • Последний день текущего месяца от текущей даты: =КОНМЕСЯЦА(СЕГОДНЯ();0)
  • Последний день прошлого месяца от текущей даты: =КОНМЕСЯЦА(СЕГОДНЯ();-1)
  • Последний день текущего месяца предыдущего года от текущей даты: =КОНМЕСЯЦА(СЕГОДНЯ();-12)
  • Последний день месяца от произвольной даты, записанной в ячейку «A1»: =КОНМЕСЯЦА(A1;0)

Функция «КОНМЕСЯЦА» возвращает значение в числовом виде, поэтому для правильного отображения последнего дня месяца следует к ячейке с формулой применить формат даты.

В версиях Excel по 2003 год функция «КОНМЕСЯЦА» по-умолчанию отсутствует, поэтому будем применять формулу, составленную, как и для первого дня месяца, из функций «ДАТА», «ГОД» и «МЕСЯЦ».

Примеры для Excel 2000-2003:

  • Последний день текущего месяца от текущей даты: =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;0)
  • Последний день прошлого месяца от текущей даты: =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());0)
  • Последний день текущего месяца предыдущего года от текущей даты: =ДАТА(ГОД(СЕГОДНЯ())-1;МЕСЯЦ(СЕГОДНЯ())+1;0)
  • Последний день месяца от произвольной даты, записанной в ячейку «A1»: =ДАТА(ГОД(A1);МЕСЯЦ(A1)+1;0)

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

Как вариант, для определения последнего дня месяца можно использовать формулу, возвращающую первый день следующего месяца, из значения которой вычитается единица: =ДАТА(ГОД(A1);МЕСЯЦ(A1)+1;1)-1

Определение первого и последнего дня месяца имеет большую актуальность для автоматического заполнения интервала дат в диалоговых формах с помощью VBA Excel.

голоса
Рейтинг статьи
Читайте так же:
Мифнс вход в личный кабинет
Ссылка на основную публикацию
Adblock
detector