Формула Excel для определения 3-й «полной» недели месяца

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

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

С уважением, Кристофер


1

Использование A19 в качестве справочных данных, поскольку в нем указано 1-е число желаемого месяца:

  = A19 + CHOOSE  (WEEKDAY (a19-1), 1,7,6,5,4,3,2) +15  

Предполагается, что первая полная неделя начинается в понедельник или раньше. .

Улучшите это ответ
Создан 30 янв. в 22:19.
  • Спасибо, Скотт ! Это дало мне отличное решение для дальнейшего развития. Я изменил следующее, чтобы получить согласованные даты в таблице

    = DATE ($ A $ 16, MONTH ([@ Date]), 1) + CHOOSE (WEEKDAY (DATE ($ A $ 16, MONTH ([@ Date]), 1) -1), 1,7,6,5,4,3,2) +15

    Затем я добавил if заявление для подтверждения даты и добавления самого события в таблицу.

    = IF ([@ Date] = DATE ($ A $ 16, MONTH ([@ Date]), 1) + CHOOSE (WEEKDAY (DATE ($ A $ 16, MONTH ( [@Date]), 1) -1), 1,7,6,5,4,3,2) +15, "TownHall", "") — Кристофер Пенленд, 31 янв. 53

добавить комментарий |

Использование A19 в качестве справочных данных, поскольку в нем указано 1-е число желаемого месяца:

  = A19 + CHOOSE (WEEKDAY (a19-1), 1,7,6,5,4,3,2) +15  

Предполагается, что первый полный неделя начинается в понедельник или раньше.


0

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

= DATE ($ A $ 16, MONTH ([@ Date]), 1) + ВЫБРАТЬ (ДЕНЬ НЕДЕЛИ (ДАТА (16 долларов США; МЕСЯЦ ([@ Date]); 1) -1); 1,7,6,5,4,3,2) +15

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

= IF ([@ Date] = ДАТА (16 долларов США; МЕСЯЦ ([@ Дата]); 1) + ВЫБРАТЬ (ДЕНЬ НЕДЕЛИ (ДАТА (16 долларов США; МЕСЯЦ ([@ Дата]); 1) -1); 1,7,6,5; 4,3,2) +15, "TownHall", "")

Улучшите этот ответ
ответил 31 янв., 14:11
добавить комментарий |

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

= DATE ($ A $ 16, MONTH ([@ Date]), 1) + ВЫБРАТЬ (ДЕНЬ НЕДЕЛИ (ДАТА (16 долларов США; МЕСЯЦ ([@ Date]); 1) -1); 1,7,6,5,4,3,2) +15

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

= IF ([@ Date] = ДАТА (16 долларов США; МЕСЯЦ ([@ Дата]); 1) + ВЫБРАТЬ (ДЕНЬ НЕДЕЛИ (ДАТА (16 долларов США; МЕСЯЦ ([@ Дата]); 1) -1); 1,7,6,5; 4,3,2) +15, "TownHall", "")



Excel: сводная таблица по дням недели и месяцам

Технология |

Автор Билл Джелен

1 марта 2019 г.
0 комментариев

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

В прошлом вы могли использовать такие формулы, как = ТЕКСТ (A2, «DDDD»), чтобы добавить новый столбец к исходным данным, чтобы получить день недели. Новая функция «Столбец из примеров» в Power Query предоставляет новое, более эффективное решение. К сожалению, эта функция ограничена версиями Excel 2016 для Windows и более поздними версиями.

Начните с набора данных, который включает столбец даты. Перейдите на главную, Форматировать как таблицу, чтобы объявить данные в виде таблицы. Затем, выделив одну ячейку в данных, перейдите на вкладку «Данные». В разделе «Получить и преобразовать данные» нажмите «Из таблицы/диапазона».

Откроется редактор Power Query. Щелкните заголовок столбца Дата. На вкладке «Добавить столбец» выберите «Столбец из примеров».

Дата в первой строке данных — 1 января 2018 г., то есть понедельник. Столбец из примеров создает новый столбец Column1 в правой части экрана. Введите в поле «Понедельник», и Power Query предложит «День недели с даты».

Нажмите Enter, и в окно предварительного просмотра данных будет добавлен новый столбец с указанием соответствующего дня недели для каждую дату в таблице. В строке формул отобразится функция Power Query, использованная для создания столбца: Date.DayofWeekName ([Date]). Эта формула иллюстрирует одну вещь, которая может расстраивать ветеранов Excel: формулы в Power Query чувствительны к регистру. В Excel формулу можно набрать как = текст (A2, «DDDD»), как = TEXT (A2, «dddd») или даже как = TeXt (a2, «ddDd») и вернуть результаты. Но в Power Query необходимо использовать соответствующие заглавные буквы. Но поскольку столбец из примеров сам генерирует формулу, вам не нужно беспокоиться о том, чтобы запомнить правильный регистр.

Повторите технику« Столбец из примеров », чтобы получить столбец, содержащий название месяца. В строке формул отобразится формула Date.MonthName ([Date]).

ЕСЛИ У ВАС НЕТ КОЛОНКИ ИЗ ПРИМЕРОВ

Функциональность «Столбец из примеров» была добавлена ​​для клиентов Office 365 в прошлом году. Если у вас нет этого значка в Power Query, вы можете использовать значок настраиваемого столбца и построить формулу. Введите = Date.DayofWeekName (. Затем найдите поле даты в списке полей. Дважды щелкните, чтобы вставить поле в формулу. Введите закрывающую скобку и нажмите OK.

После того, как у вас появятся новые поля дня недели и месяца в Power Query, вы можете вернуть данные в Excel. Вместо использования кнопки «Закрыть и загрузить» на вкладке «Главная» откройте раскрывающееся меню «Закрыть и загрузить» и выберите «Закрыть и загрузить в…», чтобы открыть диалоговое окно «Импорт данных».

Щелкните Только создать соединение. Щелкните Добавить эти данные в модель данных, а затем ОК. После этого вы вернетесь в Excel. Новая панель запросов и подключений появится вдоль правого края Excel.

Перейдите в пустую область рабочего листа, где вы хотите, чтобы отображалась сводная таблица. Выберите «Вставка», «Сводная таблица». Диалоговое окно «Создание сводной таблицы» будет предварительно загружено для использования модели данных этой книги. Нажмите кнопку ОК.

На панели «Поля сводной таблицы» будут представлены два элемента с именем «Таблица1». Найдите тот, у которого есть оранжевый цилиндрический значок. Это значок, который указывает версию таблицы, содержащей дополнительные поля.

Создайте сводную таблицу с указанием дня недели, месяца и продаж.

Сортировка сводных таблиц

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

Щелкните стрелки раскрывающегося списка в ячейках B1 и A2. В каждом случае выбирайте Дополнительные параметры сортировки. В диалоговом окне «Сортировка» выберите «По возрастанию по дням недели», затем нажмите кнопку «Дополнительные параметры» в нижнем левом углу диалогового окна. В открывшемся диалоговом окне «Дополнительные параметры сортировки» снимите флажок «Сортировка автоматически при каждом обновлении отчета». Затем вы можете выбрать соответствующий список из раскрывающегося списка «Первый порядок сортировки ключей».

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

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

ПРЕИМУЩЕСТВА POWER QUERY

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

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

СФ ГОВОРИТ

Столбец Power Query из примеров похож на Flash Fill в Excel.

0 Комментариев нет

v>

Отменить ответ

Вам также может понравиться

Excel: объединение множества файлов CSV

Excel: история запасов и обменных курсов

Excel: в Office 365 добавлены вырезанные люди и многое другое

Excel: повышение производительности

Оцените статью
logicle.ru
Добавить комментарий