000818.ru

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

Как найти первый / последний день или рабочий день месяца в Excel?

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

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

Постановление правительства уточняет праздничные и выходные дни на 2021 год.

Календарь в Excel

Как группировать даты в сводных таблицах в Excel

Рассмотрим следующий сценарий:

Он включает данные по дате, магазину и региону (восток, запад, север и юг). В этом наборе данных более 300 строк и 4 столбцов.

Вот простая сводная таблица, созданная на основании этих данных:

Эта сводная таблица показывает продажи по датам, но она не очень полезна, так как показывает все 300+ дат. В этом случае было бы лучше сгруппировать даты по году, кварталу и/или месяцу.

Для загрузки данных следуйте инструкциям.

Группировка по годам в сводной таблице

Приведенный выше набор данных показывает даты за два года (2014 и 2015).

Вот шаги, которые необходимо выполнить, чтобы сгруппировать эти даты по годам:

  • Выберите любую ячейку в столбце «Дата» сводной таблицы.
  • Перейдите в Инструменты сводной таблицы -> Анализировать -> Группа -> Выбор группы.
  • В диалоговом окне «Группировка» выберите «Годы».
    • При группировании дат вы можете выбрать несколько вариантов. По умолчанию опция «Месяцы» уже выбрана. Вы можете выбрать дополнительную опцию вместе с Месяцем. Чтобы отменить выбор месяца, просто щелкните по нему.
    • Он выбирает дату "Начало" и "Дата окончания" на основе исходных данных. Если хотите, можете их изменить.

    Здесь приведена сводная таблица по годам.

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

    Группировка по кварталам в сводной таблице

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

    Вот обзор того, как их можно сгруппировать по округам:

    • Выберите любую ячейку в столбце «Дата» сводной таблицы.
    • Перейдите в Инструменты сводной таблицы -> Анализировать -> Группа -> Выбор группы.
    • В диалоговом окне «Группировка» выберите «Кварталы» и отмените выбор любых других выбранных опций.
    • Щелкните ОК.

    Сводный график будет отображаться по кварталам.

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

    В реальной жизни, вероятно, следовало бы анализировать эти кварталы для каждого года отдельно. С этой целью :

    • Выберите любую ячейку в столбце «Дата» сводной таблицы.
    • Перейдите в Инструменты сводной таблицы -> Анализировать -> Группа -> Выбор группы.
    • В диалоговом окне «Группировка» выберите «Кварталы» и «Годы». Вы можете выбрать более одного варианта, просто щелкнув его.
    • Щелкните ОК.

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

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

    Когда вы группируете даты по более чем одной временной группе, происходит нечто интересное. Если вы посмотрите на список полей, вы увидите, что новое поле было добавлено автоматически. В данном случае это годы.

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

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

    Просто перетащите поле «Год» из области «Строки» в область «Столбцы».

    Группировка по месяцам в сводной таблице

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

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

    Как это происходит:

    • Выберите любую ячейку в столбце «Дата» сводной таблицы.
    • Перейдите в Инструменты сводной таблицы -> Анализировать -> Группа -> Выбор группы.
    • В диалоговом окне «Группировка» выберите «Месяцы» и «Годы». Вы можете выбрать более одного варианта, просто щелкнув его.
    • Щелкните ОК.

    Следующий график обобщает данные, группируя их по полю даты:

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

    Группировка по неделям в сводной таблице

    При анализе таких данных, как продажи в магазине или посещаемость сайта, целесообразно анализировать их еженедельно.

    При работе с датами в кросс-таблицах группировка дат по неделям несколько отличается от группировки по месяцам, кварталам или годам.

    Вот как можно сгруппировать даты по неделям:

    • Выберите любую ячейку в столбце «Дата» сводной таблицы.
    • Перейдите в Инструменты сводной таблицы -> Анализировать -> Группа -> Выбор группы.
    • В диалоговом окне «Группировка» выберите «Дни» и отмените выбор любых других выбранных опций. Как только вы это сделаете, вы заметите, что становится доступной опция «Количество дней» (внизу справа).
      • Встроенная возможность группировки по неделям отсутствует. Вы должны сгруппировать по дням и указать количество дней для группировки.
      • Обратите внимание, что для этого необходимо выбрать «Только дни».
      • В этом случае вы можете начать дату 30 декабря 2013 года или 6 января 2014 года (оба понедельника). (оба понедельника).

      Это сгруппирует даты по неделям, как показано на рисунке ниже:

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

      • Когда даты группируются с помощью этого метода, их нельзя группировать с помощью других параметров (например, месяцев, кварталов или лет).
      • Вычисляемое поле/элемент не работает при группировке по дням.

      Группировка по секундам / часам / минутам в сводной таблице

      При работе с большими объемами данных (например, данными центра обработки вызовов) можно группировать их по минутам, часам или секундам.

      С помощью этой же процедуры можно группировать данные по секундам, минутам или часам.

      Рассмотрим следующие данные колл-центра:

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

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

      • Создайте сводную таблицу с датой в области строк и решенным в области значений.
      • Выберите любую ячейку в столбце «Дата» сводной таблицы.
      • Перейдите в Инструменты сводной таблицы -> Анализировать -> Группа -> Выбор группы.
      • В диалоговом окне Группировка выберите Часы.
      • Щелкните ОК.

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

      Вы можете видеть, что метки строк — 09, 10 и т.д. …. т.е. часы в течение дня. 09 означает 9 утра, а 18 — 6 вечера. Используя эту сводную таблицу, легко заметить, что большинство соединений разрешено между 1-2 часами ночи в течение дня.

      То же самое относится и к группировке дат по секундам и минутам.

      Месяцы между двумя датами

      Наиболее сложным является расчет количества месяцев, поскольку оно может округляться в большую или меньшую сторону в зависимости от того, является ли месяц полным или неполным. Однако в Excel есть функция MONTHS, но она очень ограничена, поскольку при расчете разницы будет учитываться только месяц, а не год. Это означает, что он полезен только для расчета разницы между двумя месяцами в одном и том же году.

      Поскольку это не имеет смысла, давайте рассмотрим некоторые формулы, которые дадут нам правильный ответ. Microsoft предоставила их здесь, но если вам лень переходить по ссылке, я также привел их ниже.

      Здесь есть несколько очень длинных и сложных формул, и вам действительно не нужно понимать, что происходит. Вот формулы в Excel:

      месячная разница форумла

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

      показать формулы

      Итак, каков конечный результат двух формул в нашем примере? Давайте посмотрим ниже:

      разница месяцев

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

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

      Сдвиг даты на N лет

      Дата транспонирования на N лет

      Сначала мы используем функции DAY, MONTH и YEAR, чтобы разложить дату на составляющие, затем собираем ее с помощью функции DATE, но сначала добавляем нужное число к году. В английской версии это =DATE(YEAR(A1)+B1;MONTH(A1);DAY(A1)) .

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

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

      Сложение и вычитание дней из даты

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

      Запишите даты выполнения в колонку А.

      В столбце B введите количество дней, которые вы хотите добавить или вычесть. Чтобы вычесть дни из начальной даты и прибавить их, введите отрицательное число.

      В ячейке C2 напишите = a2 + b2 и при необходимости скопируйте вниз.

      Сложение и вычитание месяцев из даты с помощью функции ДАТАМЕС

      С помощью DATAMES можно быстро добавлять и вычитать месяцы из даты или к дате.

      Для функции ДАТАМЕС требуется два аргумента: Дата начала и количество месяцев, которые нужно сложить или вычесть. Чтобы вычесть месяцы, введите в качестве второго аргумента отрицательное число. Например, = ДАТАМЕС ("9/15/19";-5) возвращает 4 /15/19.

      В этом примере вы можете ввести даты начала в столбце A.

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

      Введите = DATAMES (A2; B2) в ячейку C2 и при необходимости скопируйте вниз.

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

      Вычисления можно проводить с датами, хранящимися в Excel в виде порядковых номеров. Порядковый номер по умолчанию равен 1, а 1 января 2010 года — это порядковый номер 40179, поскольку через 40 178 дней после 1 января 1900 года.

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

      Нажмите CTRL + 1, чтобы открыть диалоговое окно Формат ячеек, затем откройте вкладку Число.

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

      Сложение и вычитание лет из даты

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

      Принцип работы формулы эффективен:

      Когда используется функция year, она отображает дату в ячейке A2, возвращая год 2019. Затем к ячейке B2 прибавляется 3 года, в результате чего получается 2022 год.

      Использование функций Месяц и День вернет исходные значения из ячейки a2, однако они необходимы, если вы хотите использовать функцию Дата.

      Наконец, функция Date объединяет эти три значения в дату, состоящую из трех лет в будущем, то есть 02/08/22.

      Сложение и вычитание сочетаний дней, месяцев и лет до и после даты

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

      = ДАТА (ГОД (A2) + B2; МЕСЯЦ (A2) + C2, ДЕНЬ (A2) + D2)

      Принцип, лежащий в основе этой формулы, работает:

      Эта функция преобразует дату в ячейке A2 в 2019 год и возвращает ее. В итоге получается 2020 год, когда она добавляет 1 год к ячейке B2.

      Функция Month возвращает 6, а затем добавляет 7 из ячейки C2. Это будет интересно, потому что 6 + 7 = 13, т.е. 1 год и 1 месяц. В этом случае формула рассчитает это и автоматически добавит дополнительный год к результату, так что он изменится с 2020 на 2021.

      Функция Day возвращает число 8, и к нему прибавляется 15. Аналогично, часть формулы, которая подсчитывает дни в данном месяце, будет работать.

      Комбинируя эти три значения, функция Date выдает будущую дату, состоящую из 1 года, 7 месяцев и 15 дней будущего — 01/23/21.

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

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

      В ячейку a1 впишите 2/8/13.

      В ячейку B1 введите =A1-15.

      В ячейку С1 введите =A1+30.

      В ячейку D1 введите =С1-15.

      Добавление месяцев к дате

      Мы используем функцию DATAMES, и вам нужна начальная дата и количество месяцев, которые вы хотите добавить. Здесь показано, как добавить 16 месяцев к 24/10/13:

      Введите 10/24/13 в ячейку а1.

      Затем введите = DATAMES (a1; 16) в ячейку В1.

      Чтобы отформатировать результаты в виде дат, выделите ячейку B1. Нажмите стрелку рядом с пунктом Формат чисел>Короткая дата.

      Вычитание месяцев из даты

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

      Даты вводятся в ячейки a1 и B1 и вводится формула = DATAMES (4/15/2013,-5).

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

      Кроме того, вы можете обратиться к значению даты напрямую, используя ячейку, содержащую это значение, или формулу = DATAMES (a1,-5) для получения того же результата.

      Режимы построения табеля учета рабочего времени 2022

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

      • Режим «Стандартный». Для каждой записи сотрудника выделяется только одна строка под заполнение активностей. Нет разделения часов на дневные и ночные. Отлично подойдет для организаций, работники в которых трудятся по обычному дневному графику без суточных смен и дежурств или учет ночных часов в сменах и дежурствах не принципиален.
      • Режим «День/Ночь». Для каждой записи сотрудника выделяется две строки: «день ч.» и «ночь ч.». Заполняться могут как обе строки одновременно, так и по раздельности. В данном табеле можно совмещать ведение графиков как работников, которые трудятся по стандартному графику, так и работников, которые работают посменно.
      • Режим «День/Ночь+». Для каждой записи сотрудника выделяется три строки: «отметка», «день ч.» и «ночь ч.». Отличается от режима «День/Ночь» наличием дополнительной строки «отметка». Используется в том случае, если необходимо указать тип графика, по которому трудится работник, или для обозначения объекта, на котором ведутся работы, или любое другое обозначение на усмотрение работодателя.
      голоса
      Рейтинг статьи
      Читайте так же:
      Как найти первую числовую ячейку в диапазоне Excel?
Ссылка на основную публикацию
Adblock
detector