В excel самая ранняя дата это

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

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

Самый простой способ отсортировать данные в Microsoft Excel по дате — отсортировать их в хронологическом (или обратном хронологическом) порядке. Это сортирует данные с самой ранней или самой поздней даты, начиная с первой, в зависимости от ваших предпочтений.

Для этого откройте книгу Excel и выберите свои данные. Вы можете сделать это с помощью мыши или трекпада или щелкнув ячейку в диапазоне и нажав Ctrl + A на клавиатуре.

Выбрав данные, нажмите кнопку «Сортировка и фильтр» на вкладке «Главная» панели ленты. Здесь вы можете сортировать значения в Excel различными способами, в том числе по дате.

В раскрывающемся меню «Сортировка и фильтр» у вас будут параметры для сортировки данных в порядке возрастания или убывания. Если Excel обнаруживает даты в выбранных вами данных, параметры должны отображаться как «Сортировать от старых к новым» или «Сортировать от новых к старым».

Если вы хотите отсортировать данные так, чтобы на первом месте была самая ранняя дата, выберите опцию «Сортировать от старых к новым». В противном случае нажмите «Сортировать от самых новых к старым», чтобы отсортировать данные так, чтобы сначала была последняя дата.

Если у вас возникли проблемы, выберите только столбец, содержащий даты, а затем нажмите «Главная»> «Сортировка и фильтр»> «Сортировать от старых к новым» или «Сортировать от новых к старым».

Microsoft Excel отобразит поле «Предупреждение о сортировке», в котором вас спросят, хотите ли вы отсортировать данные вокруг него (остальные ваши данные), чтобы гарантировать, что общие данные останутся нетронутыми, в противном случае будет отсортирован только столбец даты.

Убедитесь, что выделено «Развернуть выделение», а затем нажмите кнопку «Сортировать».

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

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

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

Вы можете сделать это с помощью функций МЕСЯЦ или ГОД. Он определяет номер месяца или года в дате и предоставляет его в отдельном столбце. Затем этот столбец можно отсортировать по возрастанию или убыванию.

Для этого создайте новый столбец под названием «Месяц» или «Дата» справа от существующих данных. В первой ячейке под меткой столбца введите = МЕСЯЦ (A2) или = ГОД (A2), где «A2» — это первая ячейка в диапазоне данных, содержащая дату.

Чтобы убедиться, что формула МЕСЯЦ или ДАТА используется для всего набора данных, дважды щелкните маленький зеленый квадратный значок в правом нижнем углу ячейки. Это автоматически дублирует формулу для всех других строк, где можно найти данные слева от нее.

Установив формулу МЕСЯЦ или ДАТА, выберите столбец «Месяц» или «Дата» и нажмите «Главная»> «Сортировка и фильтр».

В раскрывающемся меню выберите вариант «Сортировать от меньшего к большему» или «Сортировать от большего к меньшему». Если вы отсортируете от наименьшего к наибольшему, Excel отсортирует ваши данные так, чтобы первым был самый ранний год или месяц.

Точно так же Microsoft Excel будет отсортировать по последнему году или месяцу, если вы выберете вариант от большего к меньшему.

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

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

По умолчанию в Excel используется система дат 1900. Это означает, что числовое значение, лежащее в основе даты 1 января 1900 года, равно 1, у 2 января 1900 года — 2 и так далее. В Excel эти значения называются последовательными значениями, позволяющими использовать даты в вычислениях. Формат времени очень похож, но Excel обрабатывает время как десятичные доли, где 1 — это 24:00, или 00:00. Числовое значение 18:00 (в русской версии) равно 0,75, так как это три четверти от 24 часов.

Чтобы узнать числовое значение даты или времени, отформатируйте ячейку, содержащую значение, форматом Общий (General). Например, у даты и времени (в английской версии) 3/July/2002 3:00:00 РМ числовое значение 37440.625, где число после десятичной точки представляет время, а 37440 — последовательное значение для 3 июля 2002 года.

Сложение за пределами 24 часов

При помощи функции СУММ (SUM) или просто знака плюс время можно складывать. Таким образом, =SUM(A1:A5) даст нам общее количество часов в диапазоне А1 :А5, если эти ячейки содержат допустимые значения времени. Однако если Excel не дано никаких специальных указаний, он не складывает время за пределами 24 часов. Это происходит потому, что, когда значение времени превышает 24 часа (настоящее значение равно 1), оно переходит в новый день и отсчет начинается заново. Чтобы заставить Excel не переходить в новый день после каждых 24 часов, можно использовать формат ячеек 37:30:55 или пользовательский формат [ч]:MM:cc([h]:mm:ss).

Схожий формат можно применять для получения общего количества минут или секунд, Чтобы узнать полное количество минут, когда время равно 24:00, отформатируйте ячейку как [м] ([m]), и вы получите 1440. Чтобы получить общее количество секунд, используйте пользовательский формат [с] ([s]) и вы получите 86400.

Вычисление времени и даты

Если вы хотите использовать фактические значения времени в других вычислениях, помните о следующих «магических» числах: 60 — 60 минут или 60 секунд; 3600 — 60 секунд * 60 минут; 24 — 24 часа; 1440 — 60 минут * 24 часа; 86400 — 24 часа * 60 минут * 60 секунд.

Вооружившись этими магическими числами и предыдущей информацией, намного проще манипулировать временем и датами. Взглянем на следующие примеры и посмотрим, что они обозначают (предполагаем, что время записано в ячейке А1). Если у вас есть число 5.50 и вы хотите получить 5:30 или 5:30 a.m., используйте следующую формулу: =А1/24. Необходимо указать подходящий формат!

Если время должно выглядеть как 17:30 или 5:30 p.m., используйте следующую формулу: =(А1/24)+0.5.

Чтобы получить противоположное значение, то есть десятичное время из настоящего времени, воспользуйтесь формулой =А1*24.

Если в ячейке содержится настоящая дата и настоящее время (например, 22/Jan/03 15:36), а вы хотите получить только дату, используйте следующую формулу: =INT(A1), в русской версии Excel =ЦЕЛОЕ(А1).

Чтобы получить только время: =A1-INT(A1), в русской версии Excel =А1-ЦЕЛОЕ(А1) или =MOO(A1;1), в русской версии Excel =OCTAT(A1;1). И вновь необходим подходящий формат.

Чтобы найти разность между двумя датами, воспользуйтесь формулой =DATEDIF(A1;A2;"d"), где А1 — это более ранняя дата. Получим количество дней между двумя датами. В качестве результата можно также указать «m» или «у», то есть месяцы или годы. (В действительности функция DATEDIF в Excel 97 не документирована и является функцией Lotus 123.)

Если более ранние дата или время неизвестны, помогут функции МИН (MIN) и МАКС (МАХ). Например, чтобы наверняка получить правильный результат, можно воспользоваться такой функцией: =DATEDIF(MIN(Al;A2); MAX(Al,A2),"d"), в русской версии Excel: =DATEDIF(MИН(Al;A2);MAKC(A1;A2);"d").

При работе со временем может также понадобиться учитывать начальное и конечное время. Например, начальное время — это 8:50 p.m. в ячейке А1, а конечное время — 9:50 a.m. в ячейке А2. Если вы вычтете начальное время из конечного (=А2-А1), получите в ответе ######, так как Excel по умолчанию не работает с отрицательными значениями времени. Подробнее о том, как работать с отрицательными значениями времени, — в разделе «Трюк № 74. Отображение отрицательных значений времени».

Иначе это ограничение можно обойти двумя способами, гарантировав положительный результат: =MAX(A1;A2)-MIN(A1;A2), в русской версии Excel =МАКС(А1;А2)-МИН(А1;А2) или =A1-A2+IF(A1>A2,1), в русской версии Excel =А1-А2+ЕСЛИ(А1>А2;1).

Можно также приказать Excel прибавить любое количество дней, месяцев или лет к любой дате: =DATE(YEAR(A1)+value1;MONTH(Al)+value2;DAY(Al)+value3), в русской версии Excel =ДАТА(ГОД(А1)+value1;,МЕСЯЦ(А1)+value2;ДЕНЬ(А1)+value3).

Чтобы добавить один месяц к дате в ячейке А1, воспользуйтесь формулой =DATE(YEAR(A1);MONTH(A1)+1;DAY(AD), в русской версии Excel =ДАТА(ГОД(А1);МЕСЯЦ(А1)+1;ДЕНЬ(А1)).

В Excel реализовано и несколько дополнительных функций, являющихся частью надстройки Analysis ToolPak. Выберите команду Файл → Надстройки (File → Add-Ins) и установите флажок Пакет анализа (Analysis ToolPak) и, если появится сообщение с вопросом, нужно ли установить эту надстройку, ответьте согласием. Станут доступны дополнительные функции, такие, как ДАТАМЕС (EDATE), КОНЕЦМЕСЯЦА (EMONTH), ЧИСТРАБДНИ (NETWORKDAYS) и WEEKNUM. Все эти функции можно найти в категории Дата и время (Date & Time) диалогового окна мастера функций. Их легко применять, сложнее узнать, что эти функции существуют, и привлечь их к делу.

Настоящие даты и время

Иногда в таблицах с импортированными данными (или данными, введенными неправильно) даты и время отображаются как текст, а не как настоящие числа. Это можно легко распознать, немного расширив столбцы, выделив столбец, выбрав команду Главная → Ячейки → Выравнивание (Home → Cells → Alignment) и для параметра По горизонтали (Horizontal) выбрав значение По значению (General) (это формат ячеек по умолчанию). Щелкните кнопку ОК и внимательно просмотрите даты и время. Если какие-либо значения не выровнены по правому краю, Excel не считает их датами.

Чтобы исправить эту ошибку, сначала скопируйте любую пустую ячейку, затем выделите столбец и отформатируйте его, выбрав любой формат даты или времени. Не снимая выделение столбца, выберите команду Главная → Специальная вставка → Значение → OK (Home → Paste Special → Value → Add).Tenepb Excel будет преобразовывать все текстовые даты и время в настоящие даты и время. Возможно, вам придется еще раз изменить форматирование. Еще один простой способ — ссылаться на ячейки так: =А1+0 или =А1*1.

Ошибка даты?

Excel ошибочно предполагает, что 1900 год был високосным годом (Добавим, он был последним годом XIX века, а не первым XX). Это означает, что внутренняя система дат Excel считает, что существовал день 29 февраля 1900 года, тогда как его не было! Самое невероятное — Microsoft сделала это намеренно, по крайней мере, они так утверждают.