Каждая транзакция проводиться в какое-то время или период, а потом привязывается к конкретной дате. В Excel дата – это преобразованные целые числа. То есть каждая дата имеет свое целое число, например, 01.01.1900 – это число 1, а 02.01.1900 – это число 2 и т.д. Определение годов, месяцев и дней – это ничто иное как соответствующий тип форматирования для очередных числовых значений. По этой причине даже простейшие операции с датами выполняемые в Excel (например, сортировка) оказываются весьма проблематичными.

Сортировка в Excel по дате и месяцу

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

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

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

Полезный совет! В Excel большинство задач имеют несколько решений. Для автоматического нормирования столбцов в Excel можно воспользоваться правой кнопкой мышки. Для этого достаточно только лишь навести курсор на маркер курсора клавиатуры (в ячейке A2) и удерживая только правую кнопку мышки провести маркер вдоль столбца. После того как отпустить правую клавишу мышки, автоматически появиться контекстное меню из, которого нужно выбрать опцию «Заполнить». И столбец автоматически заполниться последовательностью номеров, аналогично первому способу автозаполнения.

Теперь пришла очередь для создания вспомогательных столбцов, которые существенно помогут качественно сортировать список транзакций. Необходимо выполнить несколько простых действий:

1. Ячейки D1, E1, F1 заполните названиями заголовков: «Год», «Месяц», «День».
2. Соответственно каждому столбцу введите под заголовками соответствующие функции и скопируйте их вдоль каждого столбца:

• D1: =ГОД(B2);
• E1: =МЕСЯЦ(B2);
• F1: =ДЕНЬ(B2).

В итоге мы должны получить следующий результат:

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

Допустим мы хотим выполнить сортировку дат транзакций по месяцам. В данном случае порядок дней и годов – не имеют значения. Для этого просто перейдите на любую ячейку столбца «Месяц» (E) и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Сортировка по возрастанию».

Теперь, чтобы сбросить сортировку и привести данные таблицы в изначальный вид перейдите на любую ячейку столбца «№п/п» (A) и вы снова выберите тот же инструмент «Сортировка по возрастанию».

Как сделать сортировку дат по нескольким условиям в Excel

А теперь можно приступать к сложной сортировки дат по нескольким условиям. Задание следующее – транзакции должны быть отсортированы в следующем порядком:

1. Года по возрастанию.
2. Месяцы в период определенных лет – по убыванию.
3. Дни в периоды определенных месяцев – по убыванию.

Способ реализации поставленной задачи:

В результате мы выполнили сложную сортировку дат по нескольким условиям:

Для сортирования значений таблицы в формате «Дата» Excel предоставляет опции в выпадающих списках такие как «От старых к новым» и «От новых к старым». Но на практике при работе с большими объемами данных результат не всегда оправдывает ожидания. Так как для программы Excel даты – это целые числа безопаснее и эффективнее сортировать их описанным методом в данной статье.

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

Использование автозаполнения

Наиболее эффективный способ ввести несколько дат, который не требует никаких формул, - использование функции автоматического заполнения. Введите первую дату, а затем перетащите маркер заполнения ячейки, нажимая при этом правую кнопку мыши. Отпустите кнопку мыши и выберите команду контекстного меню Заполнить по месяцам (рис. 108.1).

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

Использование формул

Еще один способ ввести ряд дат основан на использовании формул. Преимущество формулы (а не функции автозаполнения) состоит в том, что вы можете изменить первую дату, а все остальные обновятся автоматически. Введите начальную дату в ячейку, а затем используйте формулы (копируя вниз по столбцу) для создания дополнительных дат. В следующих примерах предположим, что вы ввели первую дату из серии в ячейку А1, а формулу - в ячейку А2. Затем можно скопировать эту формулу вниз по столбцу столько раз, сколько это необходимо.

Для генерации серии дат, разделенных семью днями, используйте такую формулу: =A1+7 .

Для создания серии дат, разделенных одним месяцем, используйте следующую формулу: =ДАТА(ГОД(A1);МЕСЯЦ(A1)+1;ДЕНЬ(A1))

Для создания серии дат, разделенных одним годом, используйте эту формулу: =ДАТА(ГОД(A1)+1;МЕСЯЦ(A1);ДЕНЬ(A1)

Для создания серии, включающей только будние дни (все дни, кроме субботы и воскресенья), используйте следующую формулу. Она предполагает, что дата в ячейке А1 не является выходным днем: =ЕСЛИ(ДЕНЬНЕД(A1)=6;A1+3;A1+1) . Эта формула возвращает серийный номер даты, так что вам нужно отформатировать ячейки для отображения дат.

В таблицах Excel предусмотрена возможность работы с различными видами текстовой и числовой информации. Доступна и обработка дат. При этом может возникнуть потребность вычленения из общего значения конкретного числа, например, года. Для этого существует отдельные функции: ГОД, МЕСЯЦ, ДЕНЬ и ДЕНЬНЕД.

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

Таблицы Excel хранят даты, которые представлены в качестве последовательности числовых значений. Начинается она с 1 января 1900 года. Этой дате будет соответствовать число 1. При этом 1 января 2009 года заложено в таблицах, как число 39813. Именно такое количество дней между двумя обозначенными датами.

Функция ГОД используется аналогично смежным:

• МЕСЯЦ;
• ДЕНЬ;

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

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

ГОД(адрес ячейки с датой в числовом формате)

Аргумент функции является обязательным для заполнения. Он может быть заменен на «дата_в_числовом_формате». В примерах ниже, вы сможете наглядно увидеть это. Важно помнить, что при отображении даты в качестве текста (автоматическая ориентация по левому краю ячейки), функция ГОД не будет выполнена. Ее результатом станет отображение #ЗНАЧ. Поэтому форматируемые даты должны быть представлены в числовом варианте. Дни, месяцы и год могут быть разделены точкой, слешем или запятой.

Рассмотрим пример работы с функцией ГОД в Excel. Если нам нужно получить год из исходной даты нам не поможет функция ПРАВСИМВ так как она не работает с датами, а только лишь текстовыми и числовыми значениями. Чтобы отделить год, месяц или день от полной даты для этого в Excel предусмотрены функции для работы с датами.

Пример: Есть таблица с перечнем дат и в каждой из них необходимо отделить значение только года.

Введем исходные данные в Excel.

Для решения поставленной задачи, необходимо в ячейки столбца B ввести формулу:

ГОД (адрес ячейки, из даты которой нужно вычленить значение года)

В результате мы извлекаем года из каждой даты.

Аналогичный пример работы функции МЕСЯЦ в Excel:

Пример работы c функциями ДЕНЬ и ДЕНЬНЕД. Функция ДЕНЬ получает вычислить из даты число любого дня:

Функция ДЕНЬНЕД возвращает номер дня недели (1-понедельник, 2-второник… и т.д.) для любой даты:

Во втором опциональном аргументе функции ДЕНЬНЕД следует указать число 2 для нашего формата отсчета дня недели (с понедельника-1 по восркесенье-7):

Если пропустить второй необязательный для заполнения аргумент, тогда будет использоваться формат по умолчанию (английский с воскресенья-1 по суботу-7).

Создадим формулу из комбинаций функций ИНДЕКС и ДЕНЬНЕД:

Получим более понятный вид реализации данной функции.

Примеры практического применения функций для работы с датами

Эти примитивные функции очень полезны при группировки данных по: годам, месяцам, дням недели и конкретным дням.

Допустим у нас имеется простой отчет по продажам:

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

Теперь у нас есть инструмент для работы с этим отчетом по продажам. Мы можем фильтровать и сегментировать данные по определенным критериям времени:

Кроме того, можно составить гистограмму для анализа самых продаваемых дней недели, понять на какой день недели приходится наибольшее количество продаж:

В таком виде очень удобно сегментировать отчеты по продажам за длительные, средние и короткие периоды времени.

Стоит сразу отметить что для того чтобы получить разницу между двумя датами нам не поможет ни одна из выше описанных функций. Для данной задачи следует воспользоваться специально предназначенной функцией РАЗНДАТ:

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

| урок 2 | урок 3 | урок 4 | урок 5

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

Ввод дат и времени в Excel

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

Excel предлагает несколько форматов для ввода временных данных. Если Вы примените такой формат, Excel автоматически преобразует введенную дату (или время) в порядковое число (долю суток) и применит к ячейке формат Даты (или Времени), установленные по умолчанию.

На рисунке ниже представлена таблица вариантов ввода даты и времени, которые поддерживает Excel. В левой колонке показаны значения, которые нужно вводить в ячейку, а в правой – результат преобразования. Стоит отметить, что датам, вводимым без указания года, присваивается текущий год, а именно выставленный в настройках Вашей операционной системы.

Это не все возможные варианты, которые поддерживает Excel. Но даже этих вариантов Вам будет предостаточно.

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

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

Получить доступ ко всем возможным форматам Вы можете на вкладке Число диалогового окна Формат ячеек . В категории Дата приведены встроенные форматы дат:

Для того чтобы применить форматирование к ячейке, достаточно выбрать нужный формат в разделе Тип и нажать ОК . Нужное форматирование будет применено. Если встроенных числовых форматов Вам будет недостаточно, то можно воспользоваться категорией Все форматы . Здесь Вы также сможете найти много всего интересного.

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

Как видите все достаточно просто!

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