Как преобразовать дату в квартал в Excel
Обновлено: 06.07.2024
Если у вас есть даты, которые вы хотите преобразовать в квартал, возможно, вы заметили, что в Excel нет для этого функции. Поэтому мы покажем вам простую формулу, которую вы можете использовать в Excel для преобразования даты в квартал. Вам не нужно вычислять квартал от даты в Excel, потому что есть несколько способов сделать это. Сначала мы покажем, как извлечь кварталы из месяцев в Excel.
Оглавление
Найти квартал из месяца
В этом руководстве мы будем использовать приведенную ниже информацию. В году четыре квартала, и каждый квартал состоит из трех месяцев. Давайте узнаем, как найти квартал в каждом месяце, если у нас нет полной даты, записанной на листе Excel.
- Первый квартал – январь, февраль, март.
- Второй квартал — апрель, май, июнь
- Третий квартал – июль, август, сентябрь.
- Четвертый квартал – октябрь, ноябрь, декабрь.
Предположим, что ваши данные Excel выглядят так, как показано ниже. Здесь у нас есть только месяцы года, написанные в первом столбце.
Во втором столбце мы записали число (1–12) для каждого месяца, например, 2 для февраля и 8 для августа.
В третьем столбце показано вычисление второго столбца (число каждого месяца), разделенное на 3. Три — количество месяцев в квартале.
Преобразование даты в квартал в Excel
Теперь мы будем использовать функцию ОКРУГЛВВЕРХ, которая полезна и проста в использовании. Синтаксис ОКРУГЛВВЕРХ(число;число_цифр) состоит из двух цифр.
- дата или месяц
- цифры числа
В нашем случае мы написали список чисел для каждого месяца. Мы хотим найти квартал каждого месяца, поэтому мы используем ячейку третьего столбца (C2), которая представляет собой месяц, разделенный на 3; за которым следует ноль (0). Ноль означает, что число округляется без десятичных знаков. Например, 1,33 будет округлено до 2, что является ближайшим числом, которое считается вверх.
Расчет квартала в Excel по формуле ОКРУГЛВВЕРХ
Во-первых, мы объяснили, как настроить данные, чтобы найти квартал для каждого месяца года. Теперь вы обнаружите, что нет необходимости в полной настройке данных.
Чтобы найти квартал в каждом месяце, примените приведенную ниже формулу.
ОКРУГЛВВЕРХ(число месяца/3;0)
- Во-первых, вы пишете номер месяца. Например, 1 – январь, 2 – февраль.
- Деление на 3 (количество месяцев в квартале).
- Наконец, вы добавляете 0 в формулу, чтобы округлить до ближайшего числа без десятичных знаков. В четвертях нет необходимости использовать десятичные дроби, потому что нам нужно круглое число.
Поиск квартала от даты с помощью формулы ROUNDUP MONTH
Мы использовали формулу, чтобы найти квартал из месяца, но теперь мы будем извлекать квартал из даты. К счастью, вам не нужно рассчитывать квартал в Excel.
В нашем случае мы указали другой формат дат. Следовательно, чтобы найти квартал от каждой даты, нужно написать одну формулу. Нет необходимости писать номер каждого месяца. Формула, которую нужно использовать для нахождения квартала по дате, показана ниже.
=ОКРУГЛВВЕРХ(МЕСЯЦ(A2)/3;0)
Преобразование даты в квартал с помощью формулы
Если вы хотите написать четверть или Q перед каждым числом, вы можете легко это сделать. Вы можете использовать ту же формулу, что мы показали выше. Единственное, что вам нужно сделать, это написать текст перед формулой. Помните, что вы должны заключать слова в кавычки ("), иначе Excel не прочитает их правильно. Затем вы должны использовать символ, называемый амперсанд (&), также известный как символ и. Если вы хотите получить квартал из даты с текстом, примените приведенную ниже формулу.
или эта формула
Заключение
В этой статье показано, как рассчитать квартал каждого месяца года с помощью формулы. Вы также узнали, как преобразовать дату в квартал в Excel с помощью формул ОКРУГЛВВЕРХ и МЕСЯЦ. Теперь вы также можете написать текст перед каждым кварталом, чтобы сделать его более привлекательным.
Предположим, у вас есть список дат в столбце, и теперь вы хотите найти и получить кварталы по этим датам, как показано на снимке экрана ниже, как вы можете быстро обработать это в Excel?В этой статье будут представлены приемы работы с этой работой.
Найти и получить квартал с даты по формуле
Получить и найти квартал с заданной даты с помощью Kutools for Excel
Преобразование даты в формат квартала от Kutools для Excel
Найти и получить квартал от даты по формуле
Чтобы получить кварталы для заданных дат, вы можете использовать формулу.
<р>1. Выберите пустую ячейку рядом с датой, здесь я выбираю C1 и введите в нее эту формулу = ОКРУГЛВВЕРХ (МЕСЯЦ (A1)/3,0), затем нажмите клавишу Enter, чтобы получить относительный квартал.Совет. A1 – это дата, с которой вам нужно получить квартал, и вы можете изменить ее по своему усмотрению.
При необходимости вы можете перетащить маркер автозаполнения вниз к ячейкам, чтобы применить эту формулу.
Примечание:
Вы также можете использовать эту формулу ="Квартал"&ОКРУГЛВВЕРХ(МЕСЯЦ(A1)/3,0), чтобы получить это Вы также можете использовать эту формулу ="Квартал"&ОКРУГЛВВЕРХ(МЕСЯЦ(A1)/3,0), чтобы получить этот результат:
Получить и найти квартал с заданной даты с помощью Kutools for Excel
Если вы не знакомы с формулой, вы можете использовать функцию преобразования даты в квартал в Kutools for Excel, чтобы быстро получить соответствующий квартал даты.
После установки Kutools for Excel сделайте следующее: (Бесплатно загрузите Kutools for Excel сейчас!)
Совет: по умолчанию ссылка на ячейку будет отображаться в формуле как абсолютная. Чтобы использовать формулу для других ячеек с помощью дескриптора автозаполнения, вы можете изменить ее на относительную в разделе «Дата».< /p>
После того, как получен квартал первой даты, перетащите маркер автозаполнения по ячейкам, чтобы применить эту формулу по мере необходимости.
Преобразование даты в формат квартала с помощью Kutools for Excel
Если у вас есть Kutools for Excel, вы можете использовать утилиту «Применить форматирование даты», чтобы быстро преобразовать дату в любой формат даты по мере необходимости.
После установки Kutools for Excel сделайте следующее: (Бесплатно загрузите Kutools for Excel сейчас!)
<р>3. Нажмите «ОК», после чего выбранные даты будут преобразованы в нужное вам форматирование даты.
Excel имеет встроенные функции для возврата года, номера месяца, названия месяца, номера недели, дня недели или дня из любой ячейки, содержащей дату.Но нет встроенного способа вернуть номер четверти. С кварталами сложнее, потому что определение квартала зависит от даты окончания финансового года.
ЗА КАЛЕНДАРНЫЕ ГОДЫ
Если ваш финансовый год заканчивается 31 декабря, вы можете использовать краткую формулу. (На рис. 1 показаны функции Excel, которые можно использовать для преобразования даты в годы, месяцы, недели или дни недели.) Сначала используйте функцию =МЕСЯЦ() для преобразования даты в номер месяца. Разделите этот результат на 3. Даты в январе вернут 0,33, февраль 0,67, март 1,00, апрель 1,33 и так далее. Используя функцию =ROUNDUP(), вы получите 1 для января, февраля и марта; 2 с апреля по июнь; и так далее.
Чтобы отобразить квартал как "Q1" или "Qtr 1", соедините соответствующий текст перед результатом. На рис. 2 в ячейке A7 используется формула ="Q"&ОКРУГЛВВЕРХ(МЕСЯЦ(A2)/3,0)&"-"&ГОД(A2) для возврата "Q1-2017".
ЗА ДРУГИЕ ФИНАНСОВЫЕ ГОДЫ
Финансовые годы, заканчивающиеся 31 января, обычно используются в розничных компаниях. Государственные подрядчики могут использовать конец финансового года 30 сентября. Чтобы рассчитать финансовый квартал, сначала преобразуйте дату в номер месяца, используя =МЕСЯЦ(). Этот результат ограничен 12 целыми числами от 1 до 12.
Для преобразования номера месяца в номер квартала можно использовать формулу ВПР. Но формулу =ВПР(МЕСЯЦ(A2),,2) сложно запомнить и ввести. Константа массива внутри фигурных скобок содержит таблицу поиска из 5 строк и 2 столбцов.
Вместо этого функция =ВЫБОР() позволит вам указать 12 номеров кварталов, соответствующих месяцам с 1 по 12. Если ваш финансовый год заканчивается в январе, вы должны использовать =ВЫБОР(МЕСЯЦ(A2),4,1,1 ,1,2,2,2,3,3,3,4,4) . Для финансового года, заканчивающегося в марте, используйте =ВЫБОР(МЕСЯЦ(A2),4,4,4,1,1,1,2,2,2,3,3,3) .
Если вы предпочитаете отображать четверть как Q1 вместо 1, вы можете использовать амперсанд для объединения «Q» перед функцией ВЫБОР. Результаты показаны в ячейках A13 и A20 на рисунке 2, а формулы показаны в ячейках D13 и D20.
Присоединение финансового года к кварталу
В некоторых компаниях финансовый год определяется календарным годом, в котором приходится 12-й месяц года. Например, финансовый год, который длится с февраля 2017 г. по январь 2018 г., будет иметь кварталы с названиями Q1-2018 — Q4-2018. В других компаниях финансовый год может обозначаться как "17-18".
В случае, когда финансовый год является годом последнего месяца, используйте функцию IF(), чтобы проверить, меньше ли номер месяца, чем начальный месяц. Например, если финансовый год заканчивается в январе, финансовый год будет =ГОД(A2), если дата предшествует февралю, и =ГОД(A2) +1 в феврале и позже. Таким образом, чтобы включить финансовый год в квартал, можно использовать =ЕСЛИ(МЕСЯЦ(A2).
Для года, заканчивающегося в марте, вы должны изменить =ЕСЛИ(МЕСЯЦ(A2) . Для года, заканчивающегося в сентябре, вы должны проверить =ЕСЛИ(МЕСЯЦ(A2) .
Ячейка A21 на рис. 2 показывает, как объединить финансовый квартал и год, используя эту длинную формулу: ="Q"&ВЫБРАТЬ(МЕСЯЦ(A17),4,1,1,1,2,2,2,3, 3,3,4,4)&"-"&IF(MONTH(A17) . Это вернет квартал в формате "Q4-2017".
Если вместо этого вам нужно сказать 17-18, длинная формула станет еще длиннее. Просто чтобы получить часть формулы года, вы должны использовать: =ЕСЛИ(МЕСЯЦ(A2).
ВОЗВРАТ ТОЧКИ ВМЕСТО МЕСЯЦА
Недавно я провел семинар для IMA ® в Нэшвилле. Поскольку у правительства штата Теннесси финансовый год заканчивается в июне, они предпочитали отчитываться, используя номер периода, а не месяц. Июль — период 1, декабрь — период 6, январь — период 7 и так далее. В этом случае многие коллеги использовали таблицу ВПР справа от данных. Но они также могут использовать =ВЫБОР(МЕСЯЦ(A2),7,8,9,10,11,12,1,2,3,4,5,6) для достижения того же результата.
ДАТЫ НЕ МОЖЕТ БЫТЬ ТЕКСТОМ
Недостаток всех этих формул заключается в том, что вы сохраняете свои даты как настоящие даты, а не как текст. Быстрый способ проверить: нажмите Ctrl и клавишу Grave Accent (`), чтобы переключиться в режим «Показать формулы». Настоящие даты будут преобразованы в порядковый номер, например 42783. Если вы все еще видите текстовую дату, формулы не будут работать. Нажмите Ctrl+` еще раз, чтобы закрыть Показать формулы.
Недавно я наткнулся на таблицу, в которой менеджер хотел, чтобы даты отображались как 1 февраля 2017 года, 2 февраля 2017 года, 3 февраля 2017 года, 4 февраля 2017 года. "," и "th") преобразуют кардинальное числительное в порядковое. В то время как дескриптор заполнения Excel позволит вам легко создать столбец количественных чисел, формулы в этой статье становятся гораздо более сложными. Формула из 41 символа увеличилась до 2438 символов, чтобы обработать текстовые даты (см. рис. 3).
СФ ГОВОРИТ
Многие формулы, ссылающиеся на даты, меняют формат ячейки на дату. Планируйте отформатировать ячейки формул этого месяца как числовые.
Недавно я работал над проектом, в котором у меня были даты в Excel, и мне нужно было узнать, какой это квартал года, исходя из даты.
Для целей этого руководства рассмотрим следующее:
- Квартал 1 – январь, февраль и март.
- Квартал 2 – апрель, май и июнь.
- 3 квартал – июль, август и сентябрь.
- Четвертый квартал – октябрь, ноябрь и декабрь.
Если вы работаете с финансовыми данными, где квартал начинается с апреля, вы можете соответствующим образом изменить формулы (это также рассматривается далее в этом руководстве).
Теперь давайте посмотрим, как использовать простые формулы, чтобы узнать квартал по дате.
Это руководство охватывает:
Найти квартал от даты в Excel (календарный год)
Предположим, у вас есть набор данных, как показано ниже, и вы хотите рассчитать номер квартала для каждой даты.
Ниже приведена формула для этого:
Приведенная выше формула использует функцию МЕСЯЦ для получения значения месяца для каждой даты. Результатом этого будет 1 для января, 2 для февраля, 3 для марта и т. д.
Этот номер месяца делится на 3, чтобы для всех месяцев в 1-м квартале мы получили значение меньше или равное 1, а для всех месяцев во 2-м квартале мы получили значение больше 1 и меньше или равно 2, поэтому нет.
Затем используется функция ОКРУГЛВВЕРХ, чтобы получить одно и то же значение квартала для всех месяцев в этом квартале.
Найти квартал с даты в Excel (финансовый/отчетный год)
В бухгалтерском учете и финансовом анализе первый квартал начинается, а апрель и четвертый квартал заканчиваются в марте.
В таком случае нам нужно скорректировать нашу формулу, чтобы получить правильный результат.
Ниже приведена формула, которая дает правильное значение квартала для дат в финансовом году:
В этой формуле я использовал ту же функцию ОКРУГЛВВЕРХ (которую я использовал в предыдущем примере для расчета квартала календарного года) и вычел из нее 1.
Это дает правильный результат для всех кварталов, кроме квартала 4 (где он дает 0 вместо 4).
И чтобы решить эту простую проблему, я использую формулу ЕСЛИ, чтобы проверить, равно ли значение квартала 0 или нет, и если оно равно 0, то вместо этого я просто заставляю формулу возвращать 4.
Вы можете использовать ту же логику, чтобы получить значение квартала из даты, если ваш квартал начинается с июля или октября.
Вот как вы можете использовать простую формулу, чтобы получить значение квартала из даты в Excel.
Читайте также: