Основные принципы работы с датами и временем в Excel

Как обычно, кому надо быстро — смотрим видео. Подробности и нюансы — в тексте ниже:

Как вводить даты и время в Excel

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

Время вводится в ячейки с использованием двоеточия. Например

По желанию можно дополнительно уточнить количество секунд — вводя их также через двоеточие:

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

Быстрый ввод дат и времени

Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш Ctrl + Ж (или CTRL+SHIFT+4 если у вас другой системный язык по умолчанию).

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

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

Если нужно, чтобы в ячейке всегда была актуальная сегодняшняя дата — лучше воспользоваться функцией СЕГОДНЯ (TODAY) :

Как Excel на самом деле хранит и обрабатывает даты и время

Если выделить ячейку с датой и установить для нее Общий формат (правой кнопкой по ячейке Формат ячеек — вкладка Число — Общий), то можно увидеть интересную картинку:

То есть, с точки зрения Excel, 27.10.2012 15:42 = 41209,65417

На самом деле любую дату Excel хранит и обрабатывает именно так — как число с целой и дробной частью. Целая часть числа (41209) — это количество дней, прошедших с 1 января 1900 года (взято за точку отсчета) до текущей даты. А дробная часть (0,65417), соответственно, доля от суток (1сутки = 1,0)

Из всех этих фактов следуют два чисто практических вывода:

• Во-первых, Excel не умеет работать (без дополнительных настроек) с датами ранее 1 января 1900 года. Но это мы переживем! 😉
• Во-вторых, с датами и временем в Excel возможно выполнять любые математические операции. Именно потому, что на самом деле они — числа! А вот это уже раскрывает перед пользователем массу возможностей.

Количество дней между двумя датами

Считается простым вычитанием — из конечной даты вычитаем начальную и переводим результат в Общий (General) числовой формат, чтобы показать разницу в днях:

Количество рабочих дней между двумя датами

Количество полных лет, месяцев и дней между датами. Возраст в годах. Стаж.

Сдвиг даты на заданное количество дней

Поскольку одни сутки в системе отсчета даты Excel принимаются за единицу (см.выше), то для вычисления даты, отстоящей от заданной на, допустим, 20 дней, достаточно прибавить к дате это число.

Сдвиг даты на заданное количество рабочих дней

Эту операцию осуществляет функция РАБДЕНЬ (WORKDAY) . Она позволяет вычислить дату, отстоящую вперед или назад относительно начальной даты на нужное количество рабочих дней (с учетом выходных суббот и воскресений и государственных праздинков). Использование этой функции полностью аналогично применению функции ЧИСТРАБДНИ (NETWORKDAYS) описанной выше.

Вычисление дня недели

Вас не в понедельник родили? Нет? Уверены? Можно легко проверить при помощи функции ДЕНЬНЕД (WEEKDAY) из категории Дата и время.

Первый аргумент этой функции — ячейка с датой, второй — тип отсчета дней недели (самый удобный — 2).

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

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

Нюанс здесь только один. Если при сложении нескольких временных интервалов сумма получилась больше 24 часов, то Excel обнулит ее и начнет суммировать опять с нуля. Чтобы этого не происходило, нужно применить к итоговой ячейке формат 37:30:55:

Ссылки по теме

Функция «РАБДНИ» вычесляет выходные субботу, воскресенья и государственные праздники. А как сделать чтобы суббота была включена в рабочий день?

Посмотрите функцию РАБДЕНЬ.МЕЖД — она позволяет настроить рабочие дни в неделе (есть только в Excel 2010)

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

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

Посмотрите http://planetaexcel.ru/techniques/6/44/
Не оно?

Николай, подскажите, пожалуйста, по следующему моменту.
Есть таблица с датами в формате: 4 Февраля 1985. Как привести ячейки к виду: 04.02.1985. Изменение формата ячейки не помогает.

У вас, видимо, дата в виде текста. Попробуйте применить функцию ДАТАЗНАЧ — она преобразует «текстовую» дату в «настоящую».

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

Продублирую вопросик из форума — для функций РАБДЕНЬ и ЧИСТРАБДНИ можно ли:

1. ввести функцию сслыки на список праздников
2. сделать эту функцию доступной для любого файла Excel?

1. Можно сделать на отдельном (возможно — скрытом) листе список праздников, дать ему имя и использовать это имя в функциях РАБДЕНЬ и ЧИСТРАБДНИ
2. Можно добавить данные по праздникам в файл Personal, но тогда эти формулы не будут работать у других пользователей. Проще вставлять (копировать) отдельный лист с праздниками.

Спасибо Вам огромное Николай за очень полезные и познавательные ролики которые Вы выкладываете\

Спасибо на добром слове, Анатолий! Будем стараться и дальше вас радовать

Привет, Николай. Подскажите пож. в формуле =СУММЕСЛИМН(вых.контроль!$I$4:$I$3000;вых.контроль!$C$4:$C$3000;D30;вых.контроль!$B$4:$B$3000;»>$I$4″) где второй критерий суммирования — дата? возникает проблема: если, как написано в формуле идет ссылка на ячейку с датой суммирование не проходит, а если в формуле вместо >$I$4 прописать >24.05.2013 — то подсчет проходит и значение получается верное:oops:

Добрый день!
Есть такая задача: имею список событий, которые отсортированы по датам. Хочу сделать так чтобы ячейка с датой (либо вся строка) автоматически меняла цвет по мере приближения к заданной дате. Например за месяц ячейка зелёная, за 2 недели желтая, за неделю красная, прошло событие — цвет ещё какой-нибудь.
Как это сделать? Не смог найти информации по такому вопросу.

Вам поможет условное форматирование. В самом простом варианте:
выделяете ячейки с датами и открываете Главная — Условное форматирование — Правила выделения ячеек — Дата . В более сложном — там же Создать правило — Использовать формулу и ввести формулу проверки даты, например =(А1-СЕГОДНЯ())

А если задача состоит в том, чтобы подсвечивать водителя, если его автомобиль стоит без движения 6 часов и более. Буду очень признателен.

Сделать правило условного форматирования с формулой вида:
=(A1-ТДАТА())>=0,25
где А1 — ячейка с временем последней активности водителя
ТДАТА — функция, выдающая текущую дату-время
0,25 — длительность 6 часов в терминах Excel (1/4 часть суток)

Николай, спасибо за ответ. Формула не работает, потратил время, пробуя различные варианты. Как можно приложить пример? Может я что не так делаю.

Чуть не забыл, для расчётов надо использовать только 1 ячейку.

Можно не усложнять и использовать функцию ЧИСТРАБДНИ.МЕЖД — она все это умеет и не нужны никакие формулы массива и прочие ужасы.

Ну ладно. Все равно спасибо.
Мы легких путей не ищем. =)
Буду искать возможности расчёта через массив. =)

Ура! Все получилось. Использовала формулы ПОИСКПОЗ и ИНДЕКС, как в примере http://www.planetaexcel.ru/techniques/2/92/?sphrase_id=125638
Спасибо за отличный ресурс!

Не за что

Нужно вычислить кол-во часов между двумя ячейками, формат время.

Если в 2х ячейках время АМ
А1=ВРЕМЯ(2;15;0)
В1=ВРЕМЯ(8;0;0)
то =В1-А1 вычисляет нормально = 5,45 часов

но если в одной яч. время РМ после полудня, а в др. АМ до полудня
А1=ВРЕМЯ(23;0;0)
В1=ВРЕМЯ(7;0;0)
то =В1-А1 = ################ — отрицательные или слишком большие дата и время

почему так происходит и как исправить формулу, чтобы получить часы?

Марина, за 6 лет вам так никто и не ответил, но я нашел решение! Такая же проблема была.
Вот во всех этих ячейках, где ############# прописать формулу: =1-ABS(A1-B1) тогда будет реально вычисляться сколько часов прошло.

PS Надеюсь ещё не позно

Добрый день. По пункту — Быстрый ввод дат и времени. При протягивании ячейки зажатой правой кнопкой мыши, открывается окно выбора, но оно не активно. Что делать?

Добрый день! А если у меня таблица «продажи по датам» . В экселе можно как-то группировать данные помесячно, по годам.
Т.е. я понимаю, что надо сделать сводную таблицу и название столбцов сделать «Дата». А вот как сгруппировать по месяцам и годам, так чтобы именно столбцы назывались не конкретное число, а именно январь, февраль или если по годам, то 2012,2013 и т.д.
Спасибо за ответ.

ОТлично
с датами разобрался а вот с часами минутами секундами и долями секунд ну никак?
0:00:38:2 минус 0:01:06:8
равно?

Александр, если в ячейке А1 стоит дата (например 13.01.2014), а в ячейке А2 количество календарных дней (например 14, предполагается что дата из ячейки А1 тоже учитывается), то формула перекидывающая выходной день на предшествующую пятницу может выглядеть так:

А есть ли возможность указания допустим праздника в виде дд:мм, т.е. вообще без года. Пример: 8 марта — легко объясняет суть данного вопроса. Или надо создавать формулу, из даты выбирать день месяца, месяц, соединять их и сравнивать с неким текстом?

Даты без года не бывает (в Excel). Можно год спрятать форматом, но какой-то год обязательно должен быть.

Т.е. если надо сравнить две даты, например текущую и 8 Марта, то сначала надо из даты выбрать день и месяц и сравнивать уже их? По-другому не получится?

Подскажите пожалуйста есть ли возможность быстрого ввода даты и времени с помощью сочетания клавиш. Применение сочетания Ctrl + Ж или CTRL+SHIFT+4 позволяет вставить дату, но не вставляет значение времени.

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

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

Здравствуйте! Могли бы вы подсказать об использовании дат ранее 1900 года ? О каких дополнительных настройках шла речь?
Спасибо!

У Джона Уокенбаха есть в надстройке спец.функции для работы с датами до 1900 года, например http://j-walk.com/ss/excel/files/xdate.htm

Спасибо! А вы не могли бы объяснить, пожалуйста, как её применять? Потому что 1900 год, после подключения надстройки, он всё равно объявляет номером 1. Возможно, где то должна была появиться доп функция, но я её не углядел
Спасибо!

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

Если предположить, что преобразованное в дату число лежит в А1, то можно выполнить обратное преобразование с помощью вот такой формулы:
=ЗНАЧЕН(ДЕНЬ(A1)&»,»&МЕСЯЦ(A1))

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

Здравствуйте!
Подскажите как сделать так, чтоб указывало сколько в месяце дней, в отдельной ячейке?
Например, в одной ячейке указан месяц (Ноябрь 2014)

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

Основные принципы работы с датами и временем в Excel

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

Часы	Время	Доля	Значение
3	3:00	3/24	0,125
6	6:00	6/24	0,25
4	4:00	4/24	0,167
8	8:00	8/24	0,333
12	12:00	12/24	0,5
18	18:00	18/24	0,75
21	21:00	21/24	0,875

Входной интервал содержит нечисловые данные что делать?

Гистограмма распределения — это инструмент, позволяющий визуально оценить величину и характер разброса данных. Создадим гистограмму для непрерывной случайной величины с помощью встроенных средств MS EXCEL из надстройки Пакет анализа и в ручную с помощью функции ЧАСТОТА() и диаграммы.

Гистограмма (frequency histogram) – это столбиковая диаграмма MS EXCEL , в каждый столбик представляет пропорциональна количеству значений в ней (частоте наблюдений).

Гистограмма поможет визуально оценить распределение набора данных, если:

Построим гистограмму для набора данных, в котором содержатся значения непрерывной случайной величины . Набор данных (50 значений), а также рассмотренные примеры, можно взять на листе Гистограмма AT в файле примера. Данные содержатся в диапазоне А8:А57 .

Примечание : Для удобства написания формул для диапазона А8:А57 создан Именованный диапазон Исходные_данные.

Построение гистограммы с помощью надстройки Пакет анализа

Вызвав диалоговое окно надстройки Пакет анализа , выберите пункт Гистограмма и нажмите ОК.

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

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

Перед тем как анализировать полученный результат — отсортируйте исходный массив данных .

Как видно из рисунка, первый интервал включает только одно минимальное значение 113 (точнее, включены все значения меньшие или равные минимальному). Если бы в массиве было 2 или более значения 113, то в первый интервал попало бы соответствующее количество чисел (2 или более).

Размеры карманов одинаковы и равны 103,428571428571. Это значение можно получить так: =(МАКС( Исходные_данные )-МИН( Исходные_данные ))/7 где Исходные_данные – именованный диапазон , содержащий наши данные.

Почему 7? Дело в том, что количество интервалов гистограммы (карманов) зависит от количества данных и для его определения часто используется формула √n, где n – это количество данных в выборке. В нашем случае √n=√50=7,07 (всего 7 полноценных карманов, т.к. первый карман включает только значения равные минимальному).

Примечание : Похоже, что инструмент Гистограмма для подсчета общего количества интервалов (с учетом первого) использует формулу =ЦЕЛОЕ(КОРЕНЬ(СЧЕТ( Исходные_данные )))+1

Попробуйте, например, сравнить количество интервалов для диапазонов длиной 35 и 36 значений – оно будет отличаться на 1, а у 36 и 48 – будет одинаковым, т.к. функция ЦЕЛОЕ() округляет до ближайшего меньшего целого (ЦЕЛОЕ(КОРЕНЬ(35))=5 , а ЦЕЛОЕ(КОРЕНЬ(36))=6) .

Если установить галочку напротив поля Парето (отсортированная гистограмма) , то к таблице с частотами будет добавлена таблица с отсортированными по убыванию частотами.

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

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

Для нашего набора данных установим размер кармана равным 100 и первый карман возьмем равным 150.

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

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

Порядок действий при построении гистограммы в этом случае следующий:

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

В наших расчетах для определения количества интервалов мы будем пользоваться формулой =ЦЕЛОЕ(КОРЕНЬ(n))+1 .

Примечание : Кроме использованного выше правила (число карманов = √n), используется ряд других эмпирических правил, например, правило Стёрджеса (Sturges): число карманов =1+log2(n). Это обусловлено тем, что например, для n=5000, количество интервалов по формуле √n будет равно 70, а правило Стёрджеса рекомендует более приемлемое количество — 13.

Расчет ширины интервала и таблица интервалов приведены в файле примера на листе Гистограмма . Для вычисления количества значений, попадающих в каждый интервал, использована формула массива на основе функции ЧАСТОТА() . О вводе этой функции см. статью Функция ЧАСТОТА() — Подсчет ЧИСЛОвых значений в MS EXCEL .

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

Примечание : О построении и настройке макета диаграмм см. статью Основы построения диаграмм в MS EXCEL .

На этом графике каждый столбец представляет собой число значений исходного массива, меньших или равных правой границе соответствующего интервала. Это очень удобно, т.к., например, из графика сразу видно, что 90% значений (45 из 50) меньше чем 495.

СОВЕТ : О построении двумерной гистограммы см. статью Двумерная гистограмма в MS EXCEL .

Примечание : Альтернативой графику накопленной частоты может служить Кривая процентилей , которая рассмотрена в статье про Процентили .

Примечание : Когда количество значений в выборке недостаточно для построения полноценной гистограммы может быть полезна Блочная диаграмма (иногда она называется Диаграмма размаха или Ящик с усами ).

2 способа корреляционного анализа в Microsoft Excel

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

Суть корреляционного анализа

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

Расчет коэффициента корреляции

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

Способ 1: определение корреляции через Мастер функций

Одним из способов, с помощью которого можно провести корреляционный анализ, является использование функции КОРРЕЛ. Сама функция имеет общий вид КОРРЕЛ(массив1;массив2).

В поле «Массив2» нужно внести координаты второго столбца. У нас это затраты на рекламу. Точно так же, как и в предыдущем случае, заносим данные в поле.

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

Способ 2: вычисление корреляции с помощью пакета анализа

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

Параметр «Группирование» оставляем без изменений – «По столбцам», так как у нас группы данных разбиты именно на два столбца. Если бы они были разбиты построчно, то тогда следовало бы переставить переключатель в позицию «По строкам».

В параметрах вывода по умолчанию установлен пункт «Новый рабочий лист», то есть, данные будут выводиться на другом листе. Можно изменить место, переставив переключатель. Это может быть текущий лист (тогда вы должны будете указать координаты ячеек вывода информации) или новая рабочая книга (файл).

Так как место вывода результатов анализа было оставлено по умолчанию, мы перемещаемся на новый лист. Как видим, тут указан коэффициент корреляции. Естественно, он тот же, что и при использовании первого способа – 0,97. Это объясняется тем, что оба варианта выполняют одни и те же вычисления, просто произвести их можно разными способами.

Доверительные интервалы весьма важны для осознания приобретенных данных и принятия решений по ним. Чтоб высчитать ДИ для дискретной пропорции, употребляют SigmaXL> Шаблоны и калькуляторы> Главные статистические шаблоны> 1 интервал доверительных отношений. Перед тем как отыскать доверительный интервал в excel, делают последующие деяния:

Excel частота попадания в интервал — Все о Windows 10

• Во-первых, Excel не умеет работать (без дополнительных настроек) с датами ранее 1 января 1900 года. Но это мы переживем! 😉
• Во-вторых, с датами и временем в Excel возможно выполнять любые математические операции. Именно потому, что на самом деле они — числа! А вот это уже раскрывает перед пользователем массу возможностей.

Опции, перенесенные на панель быстрого доступа, будут доступны при работе со всеми вашими книгами Excel (хотя можно ее настроить и отдельно для любой книги). Так что если пользуетесь какими-то командами и инструментами постоянно — добавляйте их туда.