номер квартала в Excel

Удобная формула для вычисления номера квартала по дате в Excel

Определение квартала с использованием функции ВЫБОР в Excel

Функция ВЫБОР мало известна, но очень полезна. Что она делает? Конечно же, выбирает. Она выбирает указанное нами значение среди списка аргументов. Среди этих аргументов может быть все: числа; текст; диапазоны

Потребовалось некоторое время, чтобы убедиться в полезности данной функции, но в случае с определением квартала – она огромна. Здесь каждые 3 месяца относятся к следующему кварталу. То есть, схема будет такой:

Месяцы и кварталы.

Здесь принимаем, что каждый номер месяца – это номер аргумента функции ВЫБОР, который мы хотим отобразить. То есть, январь это 1, май – 2, и, например, ноябрь – 4.

И это то, что функция ВЫБОР от нас требует: номер аргумента, который должен отобразиться и, собственно, значения всех аргументов.

Таким образом, в ячейке E4 пишем следующую функцию:

=ВЫБОР(D4;1;1;1;2;2;2;3;3;3;4;4;4)

ВЫБОР.

Чтобы применить данную формулу на практике будем использовать функцию МЕСЯЦ для определения порядкового номера месяца для исходной даты:

МЕСЯЦ.

Теперь каждому номеру месяца присвоим порядковый номер квартала используя функцию ВЫБОР:

МЕСЯЦ.

Как видно на рисунке данное решение реализуется 1 + двенадцатью аргументами с логической последовательностью номеров квартала.

Как выбрать номер квартала из даты функцией ИНДЕКС и МЕСЯЦ?

Такой же принцип можно использовать с помощью функции ИНДЕКС:

=ИНДЕКС({1;1;1;2;2;2;3;3;3;4;4;4};1;B2)

ИНДЕКС.

Как определить квартал из даты функциями ОКРУГЛВВЕРХ и МЕСЯЦ?

Простой формулой определяем квартал для каждого месяца и даты с помощью функции ОКРУГЛВВЕРХ:

=ОКРУГЛВВЕРХ(B2/3;0)

ОКРУГЛВВЕРХ.

Как видно на рисунке все три способа без ошибочно возвращают одни и те же номера кварталов по исходным датам.

Шаг 1

Введите набор данных в диапазон ячеек в Excel в порядке от наименьшего к наибольшему, для которого вы хотите определить квартили. Например, щелкните в ячейке A1. Введите «2», «3», «4», «5», «5», «6», «8» и «9» в ячейках с A1 по A8 соответственно. Нажмите «Enter» после ввода в каждую ячейку.

Использование ручки заполнения

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

  2. Щелкните в нижнем правом углу ячейки маркер заполнения  Маркер заполнения и, удерживая, перетащите его, чтобы заполнить остальные ячейки в ряду. Маркер заполнения можно перетаскивать вверх, вниз и по горизонтали в пределах листа.

  3. Создание списка последовательных дат с помощью маркера заполнения

Аргументы

  • Year (Год) – значение года, которое важно отобразить в дате;
  • Month (Месяц) – значение месяца, которое важно отобразить в дате;
  • Day (День) – значение дня, которое важно отобразить в дате.

Номер квартала по дате

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

В английской версии Excel эта формула выглядит =INT((MONTH(A1)+2)/3)

Функция МЕСЯЦ в данном случае извлекает номер месяца для даты, а ЦЕЛОЕ — округляет результат до целого.

REDMOND

Если вам нужны римские (I, II, III, IV), а не арабские цифры, то к формуле можно добавить соответствующую функцию:

Код на VBA

Function НОМКВАРТАЛА(ByVal ДАТА As Date) As Integer Dim m As Integer m = Month(ДАТА) Select Case m Case 1 To 3 НОМКВАРТАЛА = 1 Case 4 To 6 НОМКВАРТАЛА = 2 Case 7 To 9 НОМКВАРТАЛА = 3 Case Else НОМКВАРТАЛА = 4 End SelectEnd Function

Образец файла

➜ загрузите этот образец файла отсюда, чтобы узнать больше

Как поставить неизменную отметку времени автоматически формулами?

Допустим, у вас есть список товаров в столбце A, и, как только один из них будет отправлен заказчику, вы вводите «Да» в колонке «Доставка», то есть в столбце B. Как только «Да» появится там, вы хотите автоматически зафиксировать в колонке С время, когда это произошло. И менять его уже не нужно.

Для этого мы попробуем использовать вложенную функцию ИЛИ с циклическими ссылками во второй ее части:

=ЕСЛИ(B2=”Да”; ЕСЛИ(C2=””;ТДАТА(); C2); “”)

Где B – это колонка подтверждения доставки, а C2 – это ячейка, в которую вы вводите формулу и где в конечном итоге появится статичная отметка времени.

2020-04-05_3.png

В приведенной выше формуле первая функция ЕСЛИ проверяет B2 на наличие слова «Да» (или любого другого текста, который вы решите ввести). И если указанный текст присутствует, она запускает вторую функцию ЕСЛИ. В противном случае возвращает пустое значение. Вторая ЕСЛИ – это циклическая формула, которая заставляет функцию ТДАТА() возвращать сегодняшний день и время, только если в C2 еще ничего не записано. А если там уже что-то есть, то ничего не изменится, сохранив таким образом все существующие метки.

О работе с функцией ЕСЛИ читайте более подробно здесь.

Если вместо проверки какого-либо конкретного слова вы хотите, чтобы временная метка появлялась, когда вы хоть что-нибудь пишете в указанную ячейку (это может быть любое число, текст или дата), то немного изменим первую функцию ЕСЛИ для проверки непустой ячейки:

=ЕСЛИ(B2<>””; ЕСЛИ(C2=””;ТДАТА(); C2); “”)

Примечание. Чтобы эта формула работала, вы должны разрешить циклические вычисления на своем рабочем листе (вкладка Файл – параметры – Формулы – Включить интерактивные вычисления). Также имейте в виду, что в основном не рекомендуется делать так, чтобы ячейка ссылалась сама на себя, то есть создавать циклические ссылки. И если вы решите использовать это решение в своих таблицах, то это на ваш страх и риск.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Как преобразовать дату в кварталы Excel для бюджетного года

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

Складывать и вычитать календарные дни

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

Например, вам необходимо создать резерв по сомнительным долгам в налоговом учете. В том числе нужно просчитать, когда у покупателя возникнет задолженность со сроком 45 дней после дня реализации. Для этого в одну ячейку внесите дату отгрузки. К примеру, это ячейка D2. Тогда формула будет выглядеть так: =D2+45. Вычитаются дни по аналогичному принципу. Главное, чтобы ячейка с датой, к которой будете прибавлять число, имела правильный формат. Чтобы это проверить, нажмите правой кнопкой мыши на ячейку, выберите «Формат ячеек» и удостоверьтесь, что установлен формат «Дата».

Как выглядит формат ячейки в Excel

scale_1200

Таким же образом можно посчитать и количество дней между двумя датами. Просто вычтите из более поздней даты более раннюю. Результат Excel покажет в виде числа, поэтому ячейку с итогом переведите в общий формат: вместо «Дата» выберите «Общий».

К примеру, необходимо посчитать, сколько календарных дней пройдет с 05.11.2019 по 31.12.2019. Для этого введите эти даты в разные ячейки, а в отдельной ячейке поставьте знак «=». Затем вычтите из декабрьской даты ноябрьскую. Получится 56 дней. Помните, что в этом случае в подсчет войдет последний день, но не войдет первый. Если вам необходимо, чтобы итог включал оба дня, прибавьте к формуле единицу. Если же, наоборот, нужно посчитать количество дней без учета обеих дат, то единицу необходимо вычесть.

Способы записи

В том случае, если необходимо записать номер квартала в другом виде, например с использованием римских цифр, то можно дополнить формулу функцией РИМСКОЕ:

Способы записи номера квартала

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

Как разбить страницу на четыре части в Microsoft Word

Как разбить страницу на четыре части в Microsoft Word

Microsoft Word предоставляет вам очень стандартную страницу при открытии нового документа, но инструменты программы позволяют настраивать ваши элементы разными способами. Стол…

Как разбить столбец данных в MS Excel

Как разбить столбец данных в MS Excel

Разделите столбец данных в Microsoft Excel с помощью мастера преобразования текста в столбцы. Например, один столбец может содержать имена и фамилии, а вы хотите иметь два …

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...