эксель номер квартала по дате
Как преобразовать дату в кварталы Excel для бюджетного года
Большинство из нас работает в фирмах, в которых бюджетный год начинается не в январе, а в октябре, апреле или в любом другом месяце. В таких фирмах номер квартала бюджетного года нельзя вычислять таким же способом как вычисляются традиционные кварталы календарного года.
Формула преобразования даты в кварталы финансового года
Ниже на рисунке приведен пример формулы, которая с помощью функции =ВЫБОР() вычисляет квартал для любого финансового периода на основе указанных исходных дат. В данном примере вычисляются кварталы для бюджетного года, который начинается с октября месяца.
Идея данной формулы заключается в применении функции ВЫБОР со списком номеров кварталов, указанные в желаемом нам порядке.
Формула в ячейке B2 приводит к тому, что Excel извлекает из указанной даты порядковый номер месяца в календарному году с помощью функции =МЕСЯЦ(). А потом уже на его основе функция ВЫБОР выбирает соответственный ему номер квартала финансового года указанному в списке кварталов. В данном случае, когда из даты извлекается месяц январь функция «МЕСЯЦ» возвращает число 1 (январь является первым месяцем в календарном году). На первой позиции списка кварталов бюджетного года находится значение 2. Таким образом январь у нас является месяцем второго квартала в бюджетном году фирмы.
С помощью функции =КВАРТАЛ() можно украсить результаты вычисления формул, преобразовав арабские числа в римские. Целая формула:
В финансовом мире кварталы принято отображать римскими числами I-IV.
Теперь допустим в фирме бюджетный год начинается с апреля месяца, тогда формула будет выглядеть следующим образом:
Легко понять принцип действия формулы и поменять порядок значений номеров кварталов в списке в соответствии с тем порядком, в котором должны быть расположены номера кварталов начиная от месяца начала финансового периода. То есть на 4 месяце (4-я позиция в списке) должно быть значение 1. Это значит, что апрель месяца является началом года финансовой деятельности данной фирмы.
Пример работы функции ВЫБОР в Excel
Первым (обязательным для заполнения) аргументом функции ВЫБОР является индекс. Это число в диапазоне от 1-го и до последнего номера значения в списке, который определяется через последующие аргументы функции. Максимально допустимое число аргументов – 255. Индекс только указывает, который аргумент должен возращен через функцию.
Только первый из аргументов значений списка должен быть обязательным для заполнения. Остальные 254 аргументы дополняют список выбора и определяют значение, возвращаемое в зависимости от индекса. Если индекс имеет числовое значение 1, возвращается первая позиция списка, а если 3 – третья и т.д.
3 функции для определения номера квартала по дате в Excel
Несмотря на то что в Excel не предусмотрена функция КВАРТАЛ определить номер квартала по дате можно с помощью функций ВЫБР, ИНДЕКС и ОКРУГЛВВЕРХ. Рассмотрим все на простых конкретных примерах.
Определение квартала с использованием функции ВЫБОР в Excel
Функция ВЫБОР мало известна, но очень полезна. Что она делает? Конечно же, выбирает. Она выбирает указанное нами значение среди списка аргументов. Среди этих аргументов может быть все: числа; текст; диапазоны
И это то, что функция ВЫБОР от нас требует: номер аргумента, который должен отобразиться и, собственно, значения всех аргументов.
Таким образом, в ячейке E4 пишем следующую функцию:
Чтобы применить данную формулу на практике будем использовать функцию МЕСЯЦ для определения порядкового номера месяца для исходной даты:
Теперь каждому номеру месяца присвоим порядковый номер квартала используя функцию ВЫБОР:
Как видно на рисунке данное решение реализуется 1 + двенадцатью аргументами с логической последовательностью номеров квартала.
Как выбрать номер квартала из даты функцией ИНДЕКС и МЕСЯЦ?
Такой же принцип можно использовать с помощью функции ИНДЕКС:
Как определить квартал из даты функциями ОКРУГЛВВЕРХ и МЕСЯЦ?
Простой формулой определяем квартал для каждого месяца и даты с помощью функции ОКРУГЛВВЕРХ:
Как видно на рисунке все три способа без ошибочно возвращают одни и те же номера кварталов по исходным датам.
Определение номеров нестандартных кварталов по дате
Функции ВЫБОР или ИНДЕКС имеют одно преимущество над ранее описанным методом с функцией ОКРУГЛВВЕРХ. Их можно использовать, когда в нашей компании применяется нестандартный расчетный год. Когда я пишу «нестандартный расчетный год», то имею в виду следующее: год может начинаться с другого месяца, а не с января. Даже с июля. ИНДЕКС и ВЫБОР выглядели бы тогда следующим образом:
Для наших данных результат был бы следующим:
С функцией ОКРУГЛВВЕРХ это было бы сложнее. И тут простое решение, без головной боли. И функция простая для запоминания и без запоминания какой-либо логики.
Эксель номер квартала по дате
Просматривая функции категории Дата и время, можно увидеть различные функции, чтобы определить день, месяц или год. Однако, среди стандартного перечня нет функции, позволяющей определить номер квартала по дате. Раз нет — сделаем сами! Для этого необходима обычная дата.
Первый способ — используем функции МЕСЯЦ, ЕСЛИ, И.
Определяем значение месяца с помощью функции МЕСЯЦ, а затем в функции ЕСЛИ, описываем 4 условия, каждое из которых отвечает за принадлежность того или иного месяца к определенному кварталу:
Второй способ — использование функций МЕСЯЦ, ЦЕЛОЕ.
Т.к. квартал — это каждые 3 последующих месяца в году, то можно с помощью функции МЕСЯЦ определить значение месяца, а затем к текущему значению месяца прибавить значение 2. Если полученную сумму разделить на 3 (количество месяцев в квартале), то целая часть числа будет соответствовать значению квартала:
Или, собрав всё в одну формулу, получаем простое решение:
Оставьте комментарий!
На сообщение “Как определить номер квартала по дате” комментариев 6
Все гениальное — просто!
Вася, и так возможно, только с маленькой поправкой =ОКРУГЛВВЕРХ(МЕСЯЦ(A1)/3;0)
Спасибо, помогли! А то писать всю формула расчета лень было=)
Второй способ мне нравится гораздо больше! Странно, что до сих пор нет такой функции в программе.
СТранно, что до сих пор нет такой функции в программе. Спасибо за более короткое решение, а то я часто делал через если, т.к. других функций не знал 🙁
Эксель номер квартала по дате
Как рассчитать квартал и год от даты в Excel?
Если у вас есть столбец даты, теперь вы хотите преобразовать дату в квартал и год только для получения следующего результата. Есть ли какие-нибудь быстрые способы решить эту проблему в Excel?
Преобразование даты в квартал и год с помощью формулы
Вот простая формула, которая поможет вам рассчитать квартал и год с заданной даты, пожалуйста, сделайте следующее:
Введите следующую формулу в пустую ячейку, в которую вы хотите вывести результат вычисления, а затем перетащите дескриптор заполнения вниз к ячейкам, чтобы применить эту формулу, и дата будет отображаться в формате квартала и года, см. Снимок экрана:
Преобразование даты в квартал только с формулой
Если вы просто хотите получить квартал с заданной даты, примените следующую формулу:
Затем перетащите дескриптор заполнения вниз к ячейкам для заполнения этой формулы, отображается только квартал на основе даты, см. Снимок экрана:
Преобразуйте дату в квартал и год с помощью удобной функции
Если у вас есть Kutools for Excel, С его Преобразовать дату в квартал вы можете получить формат квартала и года из определенных дат, не запоминая никаких формул.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Нажмите, чтобы выбрать ячейку, в которую вы хотите вывести результат, см. Снимок экрана:
2, Затем нажмите Kutools > Формула Помощник > Формула Помощник, см. снимок экрана:
3. В Помощник по формулам диалоговом окне выполните следующие действия:
4. А затем нажмите Ok Кнопка, будет вычислен первый результат, затем перетащите маркер заполнения для заполнения формулы в другие ячейки, см. снимок экрана:
Преобразование квартала и года на дату с помощью формулы
Иногда вам может потребоваться преобразовать ячейки формата квартала и года в обычную дату, которая является первым днем квартала, как показано ниже:
Чтобы получить первый день квартала из ячейки квартала и года, примените следующую формулу:
Затем перетащите маркер заполнения вниз к ячейкам для заполнения этой формулы, и все первые дни квартала будут извлечены одновременно, см. Снимок экрана:
Эксель номер квартала по дате
Как в Excel отсортировать даты по кварталам?
В этой статье говорится о сортировке дат только по кварталам с игнорированием года и месяца в Excel. Вот два способа сделать это.
Kutools для Excel Расширенная сортировка Утилита предоставляет нам довольно простой способ быстрой сортировки ячеек по дням недели, как показано на скриншоте ниже. Эта утилита также поддерживает множество гибких методов сортировки, таких как сортировка по длине текста, сортировка по фамилии, сортировка по абсолютному значению и т. Д. Полнофункциональная бесплатная 30-дневная пробная версия!
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Сортировка дат по кварталу с помощью вспомогательного столбца в Excel
Мы можем вставить вспомогательный столбец для перечисления соответствующих кварталов дат, а затем отсортировать диапазон по кварталу. Пожалуйста, сделайте следующее:
1. Добавьте новый столбец помимо исходного диапазона и введите Четверть как заголовок столбца.
2. Во второй ячейке нового столбца введите формулу = ОКРУГЛ ВВЕРХ (МЕСЯЦ (A2) / 3,0) в него, а затем перетащите маркер заливки в нужный диапазон.
Теперь столбец Quarter добавлен, как показано на скриншоте ниже:
3. Выберите новый столбец «Квартал» и нажмите одну кнопку сортировки на Данные меню.
4. Появится диалоговое окно с предупреждением о сортировке. Продолжайте проверять Расширить выбор и нажмите Сортировать кнопку.
Теперь вы увидите, что определенный диапазон отсортирован по кварталу, как показано ниже:
5. Удалите столбец «Квартал», щелкнув столбец правой кнопкой мыши и выбрав Удалить из контекстного меню.
Сортировка дат по кварталу с помощью Kutools for Excel
Если у вас установлен Kutools for Excel, его Расширенная сортировка Утилита может помочь вам легко сортировать даты по кварталам, игнорируя год и месяц. Пожалуйста, сделайте следующее:
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
1. Выберите диапазон, в котором вы будете сортировать даты по кварталам, и щелкните значок Предприятие > Расширенная сортировка.
2. В появившемся диалоговом окне Advanced Sort вам необходимо:
(1) В Колонка в раскрывающемся списке укажите столбец даты, который вы будете сортировать по кварталам.
(2) Щелкните значок Сортировать по раскрывающийся список и выберите Четверть.
(3) Укажите порядок сортировки в заказ выпадающий список.
3. Нажмите Ok кнопка. Затем вы увидите, что выбранный диапазон отсортирован по четверти, как показано ниже:
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now