Как преобразовать месяцы в годы и месяцы в Excel?

Формат даты в Excel, календарь в Excel, надстройка samradDatePicker

Формат даты используется для отображения дат и времени, представленных числами. В Excel система дат устроена так, что за точку отсчета берется 1 января 1900 года, а число 1 представляет собой количество дней, прошедших с тех пор. Значение времени является частью даты и хранится в виде десятичной дроби. С математической точки зрения, значениями даты и времени можно манипулировать, поскольку они являются числами.

Изменение порядкового номера месяца на его название в Excel

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

где $A$2:$A$13 – это, конечно, диапазон с названиями месяцев, а B2 — ячейка с порядковым номером в году, для которого мы ищем его название.

По схожему принципу с функцией ИНДЕКС работает другая функция, которую вы можете использовать — ВЫБОР. Разница состоит в том, что она не использует ячейки листа, в которых хранятся названия месяцев. Названия месяцев просто даем в качестве аргумента функции.

Функция SELECT принимает в качестве аргументов номер индекса (который указывает, какой элемент списка значений возвращать в качестве результата) и список значений. Те, кого интересуют подробности, могут, конечно, загрузить файл примера.

Если в качестве списка значений указать названия месяцев (по очередности), а в качестве индекса — ячейку с номером месяца, то функция вернет соответствующее ему название для каждого заданного числа.

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

Способ-4. Формулой (без какой-либо предварительной подготовки):

Вы можете попытаться восстановить уже поврежденные числа с помощью формулы

Формула в ячейке G3

Или в русском варианте :

=1*ЕСЛИ(ЯЧЕЙКА("формат";D3)="G"; ПОДСТАВИТЬ(D3;".";","); ТЕКСТ(D3;"М,ГГГГ"))

Формула выглядит следующим образом:

CELL() (ЯЧЕЙКА()) – функция из категории «Информационные». Если первый ее аргумент (тип информации) указан “format”, то результат работы функции — "G" — для текста или чисел и "D3" — для дат.

Если ячейка содержит текст, SUBSTITUTE() заменяет точку на запятую и, умножая на 1 в начале формулы (применяя арифметическую операцию), преобразует текст, состоящий из цифр и запятых, в цифру.

По умолчанию функции TEXT() преобразуют ячейки даты в текст на основе кода формата, указанного во втором аргументе. Умножение такого текста на 1 (в начале формулы) легко преобразует его в число.

Используя код формата в функции TEXT, нужно учитывать то, что и даже в англоязычной версии, в зависимости от региональных настроек, вместо “M,YYYY”, возможно, нужно указывать “М,ГГГГ” (где “M” – кириллическое).

ВАЖНО: формула не является универсальным решением.

Это работает, если после точки стоит 3 или более цифр.

Что если мы имеем следующее:

В первых трех ячейках уже цифры до точки воспринимаются как номер дня (а не номер месяца), а цифры после точки – как номер месяца (а не номер года).

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

Если в первом случае можно было бы еще добавить проверку дополнительных условий в формуле, то в последнем – исходные значения безвозвратно утеряны и узнать какие они были – уже не представляется возможным. Очевидно, в таком случае, единственный вариант – это не допустить автоматического преобразования «как бы чисел» в даты, то есть указанный выше способ 3 – чуть ли не единственное решение (не считая кардинальных изменений региональных настроек).

Такие и подобные тонкости работы в Excel мы прорабатываем на курсе "Excel бизнес-анализ и прогнозирование"

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

Автор: Евгений Довженко, генеральный директор и тренер DATAbi

Способ 1. Получить месяц из даты с помощью функции МЕСЯЦ в Excel

Протягиваем формулу и получаем месяц из даты в виде цифры. 5 — это месяц май, 8 — это август и так далее.

Иногда требуется получить месяц из даты в формате текста: "Январь, Февраль, Март. " в этом случае воспользуемся другой функцией.

Excel works!

Функции ГОД, МЕСЯЦ, ДЕНЬ в Excel — это классика. Регулярно пользуюсь ими и вам советую! Если вам нужно быстро сгруппировать данные по месяцам, сделать график, какой день недели самый загруженный или в каком году были наибольшие продажи, вам необходимо знать об этих функциях. Обо всем по порядку.

Функция ГОД в Excel

Пример. Дана таблица данных о продажах (см. выше), и вам нужно найти данные о продажах по годам.

В отдельном столбце записываем функцию ГОД, как показано на картинке выше. У этой функции только один аргумент — дата в числовом формате (именно в числовом формате — если формула не работает проверьте верно ли задана дата). Соответственно результатом работы формулы будет номер года в числовом значении.

В колонке I годовая сумма затем рассчитывается по методу СУММЕСЛИ

Функция МЕСЯЦ в Excel

В приведенном выше примере нам нужны только данные за март. Столбец F — это номер месяца. Теперь функция «Месяц в Excel» вернет номер месяца года. Если это июль, то результатом будет число 7.

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

Это легко сделать, используя автофильтр

Функция ДЕНЬ в Excel

Функция ДЕНЬ работает точно так же как и две предыдущих — она считает порядковый номер дня этой даты в месяце. Т.е. если сегодня 22.07, число 22 получаем именно функцией ДЕНЬ:

D AYNED было бы еще интереснее. Выходное число генерируется из числа, задающего день недели.

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

После чего мы можем построить график — в какой день недели было больше всего продаж.

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

Для функции ДЕНЬНЕД есть небольшая хитрость — это дополнительный аргумент, если оставить это поле пустым, то дни недели будут считаться по американской системе (воскресенье первый день недели), а если поставить 2, то по европейской — к которой мы привыкли.

Несжатый файл, содержащий все 3 функции и примеры графиков, доступен для скачивания.

Статьи, которые будут полезны вместе с этой статьей. Функции ГОД, МЕСЯЦ, ДЕНЬ в Excel:

• Как группировать данные по дням в сводной таблице
• Как подсчитать количество дней между датами, включая количество рабочих дней
• Функция TODAY и все что с ней связано.

Время и дата очень обширные темы в Excel. Возможно, я что-то забыл описать — пишите, пожалуйста, вопросы в комментариях.