Каждая транзакция проводиться в какое-то время или период, а потом привязывается к конкретной дате. В Excel дата – это преобразованные целые числа. То есть каждая дата имеет свое целое число, например, 01.01.1900 – это число 1, а 02.01.1900 – это число 2 и т.д. Определение годов, месяцев и дней – это ничто иное как соответствующий тип форматирования для очередных числовых значений. По этой причине даже простейшие операции с датами выполняемые в Excel (например, сортировка) оказываются весьма проблематичными.
Сортировка в Excel по дате и месяцу
На примере таблицы изображенной ниже на рисунке мы покажем, в какой способ ее подготовить для комфортной сортировки дат. Наш список транзакций с датами охватывает диапазон ячеек B1:C15.
В первую очередь создадим столбец, в котором пронумеруем все транзакции. Считайте это действие как хороший тон для любого вида сортировки. Ведь в такие случаи при неправильной перетасовки данных мы всегда имеем возможность вернуться в первоначальный вид таблицы. Для этого:
В результате чего столбец автоматически заполниться последовательностью номеров транзакций от 1 до 14.
Полезный совет! В Excel большинство задач имеют несколько решений. Для автоматического нормирования столбцов в Excel можно воспользоваться правой кнопкой мышки. Для этого достаточно только лишь навести курсор на маркер курсора клавиатуры (в ячейке A2) и удерживая только правую кнопку мышки провести маркер вдоль столбца. После того как отпустить правую клавишу мышки, автоматически появиться контекстное меню из, которого нужно выбрать опцию «Заполнить». И столбец автоматически заполниться последовательностью номеров, аналогично первому способу автозаполнения.
Теперь пришла очередь для создания вспомогательных столбцов, которые существенно помогут качественно сортировать список транзакций. Необходимо выполнить несколько простых действий:
- Ячейки D1, E1, F1 заполните названиями заголовков: «Год», «Месяц», «День».
- Соответственно каждому столбцу введите под заголовками соответствующие функции и скопируйте их вдоль каждого столбца:
- D1: =ГОД(B2);
- E1: =МЕСЯЦ(B2);
- F1: =ДЕНЬ(B2).
В итоге мы должны получить следующий результат:
Наша таблица готова и предоставляет возможность выполнять многостороннюю сортировку транзакций по датам. Сначала выполним тестовую сортировку, чтобы убедиться в том, как все работает.
Допустим мы хотим выполнить сортировку дат транзакций по месяцам. В данном случае порядок дней и годов – не имеют значения. Для этого просто перейдите на любую ячейку столбца «Месяц» (E) и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Сортировка по возрастанию».
Теперь, чтобы сбросить сортировку и привести данные таблицы в изначальный вид перейдите на любую ячейку столбца «№п/п» (A) и вы снова выберите тот же инструмент «Сортировка по возрастанию».
Как сделать сортировку дат по нескольким условиям в Excel
А теперь можно приступать к сложной сортировки дат по нескольким условиям. Задание следующее – транзакции должны быть отсортированы в следующем порядком:
- Года по возрастанию.
- Месяцы в период определенных лет – по убыванию.
- Дни в периоды определенных месяцев – по убыванию.
Способ реализации поставленной задачи:
В результате мы выполнили сложную сортировку дат по нескольким условиям:
Для сортирования значений таблицы в формате «Дата» Excel предоставляет опции в выпадающих списках такие как «От старых к новым» и «От новых к старым». Но на практике при работе с большими объемами данных результат не всегда оправдывает ожидания. Так как для программы Excel даты – это целые числа безопаснее и эффективнее сортировать их описанным методом в данной статье.
Самый простой и быстрый способ ввести в ячейку текущую дату или время – это нажать комбинацию горячих клавиш CTRL+«;» (текущая дата) и CTRL+SHIFT+«;» (текущее время).
Гораздо эффективнее использовать функцию СЕГОДНЯ(). Ведь она не только устанавливает, но и автоматически обновляет значение ячейки каждый день без участия пользователя.
Как поставить текущую дату в Excel
Чтобы вставить текущую дату в Excel воспользуйтесь функцией СЕГОДНЯ(). Для этого выберите инструмент «Формулы»-«Дата и время»-«СЕГОДНЯ». Данная функция не имеет аргументов, поэтому вы можете просто ввести в ячейку: «=СЕГОДНЯ()» и нажать ВВОД.
Текущая дата в ячейке:
Если же необходимо чтобы в ячейке автоматически обновлялось значение не только текущей даты, но и времени тогда лучше использовать функцию «=ТДАТА()».
Текущая дата и время в ячейке.
Как установить текущую дату в Excel на колонтитулах
Вставка текущей даты в Excel реализуется несколькими способами:
- Задав параметры колонтитулов. Преимущество данного способа в том, что текущая дата и время проставляются сразу на все страницы одновременно.
- Используя функцию СЕГОДНЯ().
- Используя комбинацию горячих клавиш CTRL+; – для установки текущей даты и CTRL+SHIFT+; – для установки текущего времени. Недостаток – в данном способе не будет автоматически обновляться значение ячейки на текущие показатели, при открытии документа. Но в некоторых случаях данных недостаток является преимуществом.
- С помощью VBA макросов используя в коде программы функции: Date();Time();Now() .
Колонтитулы позволяют установить текущую дату и время в верхних или нижних частях страниц документа, который будет выводиться на принтер. Кроме того, колонтитул позволяет нам пронумеровать все страницы документа.
Чтобы сделать текущую дату в Excel и нумерацию страниц с помощью колонтитулов сделайте так:
Колонтитулы позволяют нам не только устанавливать даты и нумерации страниц. Так же можно добавить место для подписи ответственного лица за отчет. Например, отредактируем теперь левую нижнюю часть страницы в области колонтитулов.
Даты часто являются важной частью анализа данных. Часто вы задаете вопросы, например: когда был приобретен продукт, как долго будет выполняться задача в проекте или Каков средний доход за финансовый квартал? Правильная ввод дат является обязательным условием для обеспечения точности результатов. Однако для правильного понимания этих результатов необходимо форматировать даты, чтобы их было проще понять.
Важно: Поскольку правила, определяющие способ интерпретации дат с помощью любой программы вычисления, сложны, вы должны быть как можно более конкретными датами при каждом вводе. Это обеспечит максимально возможную точность расчетов дат.
Сведения о вычислениях и форматах дат
Excel хранит даты в виде последовательных чисел, которые называются последовательными значениями. Например, в Excel для Windows 1 января 1900 является порядковым номером 1, а 1 января 2008 - порядковым номером 39448, так как это 39 448 дня после 1 января 1900 г.
Excel хранит значения времени как десятичные дроби, так как время рассматривается как часть суток. Десятичное число - это значение в диапазоне от 0 (нуля) до 0,99999999, представляющее собой время от 0:00:00 (12:00:00 утра) до 23:59:59 (11:59:59 вечера).
Так как значения даты и времени являются значениями, их можно добавить, вычесть и включить в другие вычисления. Вы можете просматривать дату в виде порядкового значения и времени в виде десятичной дроби, изменяя формат ячейки, содержащей дату или время, в общий формат.
Сведения о двух системах дат
В Excel для Mac и Excel для Windows поддерживаются системы дат 1900 и 1904. Система дат по умолчанию для Excel для Windows - 1900; а система дат по умолчанию для Excel для Mac - 1904.
Первоначально приложение Excel для Windows основано на системе дат 1900, поскольку оно позволило улучшить совместимость с другими программами для работы с электронными таблицами, разработанными для операционной системы MS-DOS и Microsoft Windows, и поэтому она стала стандартной системой дат. Первоначально приложение Excel для Mac основано на системе дат 1904, поскольку оно позволило улучшить совместимость с ранними компьютерами Macintosh, которые не поддерживали даты до 2 января 1904 г., поэтому она стала системой дат по умолчанию.
В приведенной ниже таблице показаны первая дата и Дата последнего периода для каждой системы дат и последовательного значения, связанные с каждой датой.
Поскольку в двух системах дат используются разные начальные дни, одинаковая дата представлена различными порядковыми номерами в каждой системе дат. Например, 5 июля 2007 могут иметь два различных последовательных значения в зависимости от используемой системы дат.
Разница между двумя системами дат составляет 1 462 дней; Это значит, что порядковый номер даты в системе 1900 даты всегда равен 1 462 суток, чем последовательное значение той же даты в системе дат 1904. И наоборот, порядковый номер даты в системе дат 1904 всегда составляет 1 462 дней меньше, чем последовательное значение той же даты в системе дат 1900. 1 462 дней равно 4 годам и одному дню (включая один високосный день).
Изменение способа интерпретации года, состоящего из двух цифр
Важно: Чтобы значения года интерпретировались правильно, введите четыре цифры (например, 2001, а не 01). Вводя четырехзначные года, Excel не будет интерпретировать этот столети.
Если вы ввели дату, состоящий из двух цифр года, в текстовой ячейке или текстовом аргументе в функции, например "= год" ("1/1/31"), Excel интерпретирует год следующим образом:
00 – 29 интерпретируется как годы с 2000 по 2029. Например, при вводе даты 5/28/19 Excel считает, что дата может быть 28 мая 2019 г.
от 30 до 99 интерпретируется как годы с 1930 по 1999. Например, при вводе даты 5/28/98 Excel считает, что дата может быть 28 мая 1998 г.
В Microsoft Windows можно изменить способ интерпретации двух цифр года для всех установленных программ Windows.
Windows 10
Панель управления , а затем выберите Панель управления .
В разделе часы, язык и регион щелкните .
Щелкните значок Язык и региональные стандарты .
В диалоговом окне регион нажмите кнопку Дополнительные параметры .
Откройте вкладку Дата .
В поле
Нажмите кнопку ОК .
Windows 8
Поиск Поиск ), введите в поле поиска элемент Панель управления Панель управления .
В разделе часы, язык и регион щелкните .
В диалоговом окне регион нажмите кнопку Дополнительные параметры .
Откройте вкладку Дата .
В поле после ввода двух цифр года интерпретируйте его как год между рамками, изменяя верхний предел для века.
По мере изменения верхнего лимита год автоматически меняется на нижний предел.
Нажмите кнопку ОК .
Windows 7
Нажмите кнопку Пуск и выберите пункт Панель управления .
Выберите пункт .
В диалоговом окне регион нажмите кнопку Дополнительные параметры .
Откройте вкладку Дата .
В поле после ввода двух цифр года интерпретируйте его как год между рамками, изменяя верхний предел для века.
По мере изменения верхнего лимита год автоматически меняется на нижний предел.
Нажмите кнопку ОК .
Изменение формата даты, используемого по умолчанию, для отображения года четырьмя цифрами
По умолчанию при вводе дат в книге даты форматируются для отображения двух цифр года. Когда вы изменяете формат даты, установленный по умолчанию, на другой формат с помощью этой процедуры, отображение дат, ранее введенных в книгу, будет изменено на новый формат, пока не отформатировано в диалоговом окне " Формат ячеек " "(на вкладке Главная в группе число нажмите кнопку вызова диалогового окна ).
Windows 10
В поле поиска на панели задач введите Панель управления , а затем выберите Панель управления .
В разделе часы, язык и регион щелкните изменить форматы даты, времени и чисел .
Щелкните значок Язык и региональные стандарты .
В диалоговом окне регион нажмите кнопку Дополнительные параметры .
Откройте вкладку Дата .
В кратком списке Формат даты
Нажмите кнопку ОК .
Windows 8
Проведите пальцем от правого края экрана, нажмите кнопку Поиск (или, если вы используете мышь, наведите указатель мыши на правый верхний угол экрана, переместите его вниз и нажмите кнопку Поиск ), введите в поле поиска элемент Панель управления , а затем нажмите или Выберите Панель управления .
В разделе часы, язык и регион щелкните изменить форматы даты, времени или чисел .
В диалоговом окне регион нажмите кнопку Дополнительные параметры .
Откройте вкладку Дата .
В кратком списке Формат даты выберите формат, использующий четыре цифры для года (гггг).
Нажмите кнопку ОК .
Windows 7
Нажмите кнопку Пуск и выберите пункт Панель управления .
Выберите пункт язык и региональные стандарты .
В диалоговом окне регион нажмите кнопку Дополнительные параметры .
Откройте вкладку Дата .
В кратком списке Формат даты выберите формат, использующий четыре цифры для года (гггг).
Нажмите кнопку ОК .
Изменение системы дат в Excel
Система дат автоматически меняется при открытии документа, созданного на другой платформе. Например, если вы работаете в Excel и открыли документ, созданный в Excel для Mac, флажок " система дат 1904 " установлен автоматически.
Вы можете изменить систему дат, выполнив указанные ниже действия.
Выберите Файл > Параметры > Дополнительно .
В разделе при расчете этой книги выберите нужную книгу, а затем установите или снимите флажок использовать систему дат 1904 .
Проблема: возникают проблемы с датами между книгами, использующими разные системы дат
Вы можете столкнуться с проблемами при копировании и вставке дат, а также при создании внешних ссылок между книгами на основе двух разных систем дат. Даты могут выводиться на четыре года и более ранние или более поздние, чем ожидаемая дата. Эти проблемы могут возникнуть в том случае, если вы используете Excel для Windows, Excel для Mac или и то, и другое.
Например, если вы копируете дату 5 июля 2007 из книги, в которой используется система дат 1900, а затем вставляете ее в книгу, в которой используется система дат 1904, дата отображается как 5 июля 2011 г., что составляет 1 462 дней позже. Кроме того, если вы копируете дату 5 июля 2007 из книги, в которой используется система дат 1904, а затем вставляете ее в книгу, в которой используется система дат 1900, дата будет состоять из 4 июля 2003 г., что составляет 1 462 дней раньше. Общие сведения можно найти в разделе системы дат в Excel .
Исправление ошибки при копировании и вставке
Чтобы установить дату как четыре года и один день позже, добавьте в нее 1 462. Пример
Sheet1!$A$1+1462
Чтобы установить дату в виде четырех лет и более ранней версии, вычтите 1 462 из нее. Пример
Sheet1!$A$1-1462
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте Excel User Voice
Создадим последовательности дат и времен различных видов: 01.01.09, 01.02.09, 01.03.09, ..., янв, апр, июл, ..., пн, вт, ср, ..., 1 кв., 2 кв.,..., 09:00, 10:00, 11:00, ... и пр.
Т.к. каждому значению даты соответствует определенное число (см. статью ), то подходы для формирования последовательностей, изложенные в статье , применимы и для дат. Однако, имеются и свои особенности.
Последовательность 01.01.09, 01.02.09, 01.03.09 (первые дни месяцев) можно сформировать формулой =ДАТАМЕС(B2;СТРОКА(A1)) , в ячейке B2 должна находиться дата - первый элемент последовательности (01.01.09 ).
Эту же последовательность можно сформировать используя правую клавишу мыши. Пусть в ячейку A2 введено значение 01.01.09 . Выделим ячейку A2 . Удерживая правую клавишу мыши, скопируем , значение из A2 в ячейки ниже. После того, как отпустим правую клавишу мыши, появится контекстное меню, в котором нужно выбрать пункт Заполнить по месяцам .
Изменив формат ячеек, содержащих последовательность 01.01.09, 01.02.09, 01.03.09, на МММ (см. статью ) получим последовательность янв, фев, мар, .. .
Эту же последовательность можно ввести используя список автозаполения Кнопка Офис/ Параметры Excel/ Основные/ Основные параметры работы с Excel/ Изменить списки (введите янв , затем Маркером заполнения скопируйте вниз).
Ячейки будут содержать не даты, как в предыдущем случае, а текстовые значения.
Аналогичным образом можно сформировать последовательность дней недели пн, вт, ср, ...
Последовательность кварталов 1 кв., 2 кв.,... можно сформировать используя идеи из статьи .
Используя инструмент , можно создать последовательности только рабочих дней. А также с шагом месяц и год.
Последовательность первых месяцев кварталов янв, апр, июл, окт, янв, ... можно создать введя в две ячейки первые два элемента последовательности (янв, апр ), затем (предварительно выделив их) скопировать вниз маркером заполнения . Ячейки будут содержать текстовые значения. Чтобы ячейки содержали даты, используйте формулу =ДАТАМЕС($G$16;(СТРОКА(A2)-СТРОКА($A$1))*3) Предполагается, что последовательность начинается с ячейки G16 , формулу нужно ввести в ячейку G17 (см. файл примера ).
Временную последовательность 09:00, 10:00, 11:00, ... можно сформировать используя . Пусть в ячейку A2 введено значение 09 :00 . Выделим ячейку A2 . Скопируем Маркером заполнения , значение из A2 в ячейки ниже. Последовательность будет сформирована.
Если требуется сформировать временную последовательность с шагом 15 минут (09:00, 09:15, 09:30, ... ), то можно использовать формулу =B15+1/24/60*15 (Предполагается, что последовательность начинается с ячейки B15 , формулу нужно ввести в B16 ). Формула вернет результат в формате даты.
Другая формула =ТЕКСТ(B15+1/24/60*15;"чч:мм") вернет результат в текстовом формате.
В таблицах Excel предусмотрена возможность работы с различными видами текстовой и числовой информации. Доступна и обработка дат. При этом может возникнуть потребность вычленения из общего значения конкретного числа, например, года. Для этого существует отдельные функции: ГОД, МЕСЯЦ, ДЕНЬ и ДЕНЬНЕД.
Примеры использования функций для обработки даты в Excel
Таблицы Excel хранят даты, которые представлены в качестве последовательности числовых значений. Начинается она с 1 января 1900 года. Этой дате будет соответствовать число 1. При этом 1 января 2009 года заложено в таблицах, как число 39813. Именно такое количество дней между двумя обозначенными датами.
Функция ГОД используется аналогично смежным:
- МЕСЯЦ;
- ДЕНЬ;
Все они отображают числовые значения, соответствующие григорианскому календарю. Даже если в таблице Excel для отображения введенной даты был выбран календарь Хиджра, то при вычленении года и других составных значений посредством функций, приложение представит число, которое является эквивалентом по григорианской системе летоисчисления.
Чтобы воспользоваться функцией ГОД, нужно ввести в ячейку следующую формулу функции с одним аргументом:
ГОД(адрес ячейки с датой в числовом формате)
Аргумент функции является обязательным для заполнения. Он может быть заменен на «дата_в_числовом_формате». В примерах ниже, вы сможете наглядно увидеть это. Важно помнить, что при отображении даты в качестве текста (автоматическая ориентация по левому краю ячейки), функция ГОД не будет выполнена. Ее результатом станет отображение #ЗНАЧ. Поэтому форматируемые даты должны быть представлены в числовом варианте. Дни, месяцы и год могут быть разделены точкой, слешем или запятой.
Рассмотрим пример работы с функцией ГОД в Excel. Если нам нужно получить год из исходной даты нам не поможет функция ПРАВСИМВ так как она не работает с датами, а только лишь текстовыми и числовыми значениями. Чтобы отделить год, месяц или день от полной даты для этого в Excel предусмотрены функции для работы с датами.
Пример: Есть таблица с перечнем дат и в каждой из них необходимо отделить значение только года.
Введем исходные данные в Excel.
Для решения поставленной задачи, необходимо в ячейки столбца B ввести формулу:
ГОД (адрес ячейки, из даты которой нужно вычленить значение года)
В результате мы извлекаем года из каждой даты.
Аналогичный пример работы функции МЕСЯЦ в Excel:
Пример работы c функциями ДЕНЬ и ДЕНЬНЕД. Функция ДЕНЬ получает вычислить из даты число любого дня:
Функция ДЕНЬНЕД возвращает номер дня недели (1-понедельник, 2-второник… и т.д.) для любой даты:
Во втором опциональном аргументе функции ДЕНЬНЕД следует указать число 2 для нашего формата отсчета дня недели (с понедельника-1 по восркесенье-7):
Если пропустить второй необязательный для заполнения аргумент, тогда будет использоваться формат по умолчанию (английский с воскресенья-1 по суботу-7).
Создадим формулу из комбинаций функций ИНДЕКС и ДЕНЬНЕД:
Получим более понятный вид реализации данной функции.
Примеры практического применения функций для работы с датами
Эти примитивные функции очень полезны при группировки данных по: годам, месяцам, дням недели и конкретным дням.
Допустим у нас имеется простой отчет по продажам:
Нам нужно быстро организовать данные для визуального анализа без использования сводных таблиц. Для этого приведем отчет в таблицу где можно удобно и быстро группировать данные по годам месяцам и дням недели:
Теперь у нас есть инструмент для работы с этим отчетом по продажам. Мы можем фильтровать и сегментировать данные по определенным критериям времени:
Кроме того, можно составить гистограмму для анализа самых продаваемых дней недели, понять на какой день недели приходится наибольшее количество продаж:
В таком виде очень удобно сегментировать отчеты по продажам за длительные, средние и короткие периоды времени.
Стоит сразу отметить что для того чтобы получить разницу между двумя датами нам не поможет ни одна из выше описанных функций. Для данной задачи следует воспользоваться специально предназначенной функцией РАЗНДАТ:
Тип значений в ячейках «дата» требует особого подхода при обработке данных. Поэтому следует использовать соответствующие данному типу функции в Excel.
Windows
