Как посчитать среднюю дату в экселе

Обновлено: 15.01.2025

Дата и время в Excel – это числа сформатированные специальным образом. Датой является целая часть числа, а время (часы и минуты) – это дробная часть.

По умолчанию число 1 соответствует дате 01 января 1900 года. То есть каждая дата – это количество дней прошедших от 01.01.1900. На данном уроке детально рассмотрим даты, а на следующих уроках – время.

Как в Excel посчитать дни между датами?

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

На чистом листе в ячейку А1 введите текущею дату нажав CTRL+;.
В ячейке A2 введите промежуточный период в днях, например 127.
В ячейку A3 введите формулу:=A1+A2.

Обратите внимание, формат «Дата» автоматически присвоился для ячейки A3. Не сложно догадаться, чтобы посчитать разницу в датах в Excel нужно от новейшей даты отнять старшую дату. В ячейке B1 введите формулу:=A3-A1. Соответственно мы получаем количество дней между этими двумя датами.

Вычисление возраста по дате рождения в Excel

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

На новый лист в ячейки A1:A3 введите даты: 18.04.1985; 17.08.1977; 08.12.1992
В ячейки B1:B3 проставьте текущею дату.
Теперь нужно воспользоваться функцией по преобразованию количества дней в количество лет. Для этого вручную введите в диапазон C1:C3 следующее значение: =РАЗНДАТ(A1;B1;"y").

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

Внимание! Чтобы перевести дни в года не достаточно формулы: =(B1-A1)/365. Более того даже если мы знаем что 1 сутки = 0,0027397260273973 года, то формула:=(B1-A1)*0,0027397260273973 так же не даст точного результата.

Дни в года наиболее точно преобразует функция:=РАЗНДАТ(). Английская версия =DATEDIF(). Вы не найдете ее в списке мастера функций (SHIFT+F3). Но если просто введете ее в строку формул, то она сработает.

Функция РАЗНДАТ поддерживает несколько параметров:

Параметр	Описание
"d"	Количество полных дней
"m"	Количество полных месяцев
"y"	Количество полных лет
"ym"	Количество полных месяцев без учета лет
"md"	Количество дней без учета месяцев и лет
"yd"	Количество дней без учета лет

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

Внимание! Чтобы функция:=РАЗНДАТ() работала без ошибок, следите за тем, чтобы начальная дата была старше конечной даты.

Ввод даты в ячейку Excel

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

Заполните таблицу датами, так как показано на рисунке:

Разные способы ввода дат. В столбце А – способ ввода, а в столбце B – результат отображения.

Обратите внимание, что в формате ячеек по умолчанию «Общий», даты также как и число выравниваются по правой стороне, а текст по левой. Значение в ячейке B4 распознается программой как текст.

В ячейке B7 Excel сам присвоил текущий год (сейчас 2015-ый) по умолчанию. Это видно при отображении содержимого ячеек в строке формул. Обратите внимание, как изначально вводилось значение в A7.

Вычисление даты Excel

На чистом листе в ячейки А1:B1 введите 01.01.1900, а в ячейках A2:B2 вводим 01.01.1901. Теперь меняем формат ячеек на «числовой» в выделенном диапазоне B1:B2. Для этого можно нажать комбинацию клавиш CTRL+SHIFT+1.

B1 теперь содержит число 1, а B2 – 367. То есть прошел один високосный год (366 дней) и 1 день.

Способ отображения даты можно задать с помощью диалогового окна «Формат ячеек». Для его вызова нажмите: CTRL+1. На закладке «Число» выберите в списке «Числовые форматы» - «Дата». В разделе «Тип» отображаются самые популярные форматы для отображения дат.

На следующем уроке мы на готовых примерах будем работать со временем и периодами суток.

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

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

Например, у меня есть следующий диапазон данных, и теперь мне нужно усреднить числа в столбце B между 11 и 01 столбца A.

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

Введите эту формулу: =AVERAGE(IF((A2:A15>=E1)*(A2:A15<=E2),B2:B15)) в конкретную пустую ячейку, а затем нажмите Shift + Ctrl + Enter вместе, чтобы получить правильный результат, см. снимок экрана:

Ноты:

1. Кроме приведенной выше формулы массива, вот еще обычная формула: =SUMPRODUCT(--(A2:A15>=E1),--(A2:A15<=E2),B2:B15)/SUMPRODUCT(--(A2:A15>=E1),--(A2:A15<=E2)) , а затем просто нажмите Enter ключ.

2. В приведенной выше формуле A2: A15 это диапазон дат, по которому вы хотите усреднить числа, B2: B15 диапазон данных, среднее значение которого вы хотите вычислить, E1 и E2 указывает дату начала и дату окончания, которые вы хотите использовать.

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

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

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Выберите столбец даты, который вы хотите усреднить между двумя датами, затем щелкните Kutools > Выберите > Выбрать определенные ячейки, см. снимок экрана:

2. В Выбрать определенные ячейки диалоговое окно, выберите Весь ряд из Тип выбора раздел, а затем выберите Больше или равно и Меньше или равно условия и введите конкретную дату рядом с ними отдельно, см. снимок экрана:

3. Затем были выбраны строки между двумя конкретными датами, скопируйте и вставьте строки на другой лист, а затем примените = Среднее (B1: B6) формулу для расчета результата, см. снимок экрана:

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

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

Какова бы ни была ваша проблема, один из приведенных ниже примеров в Excel наверняка подскажет вам решение.

Простой калькулятор дней.

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

Хотите узнать формулу, которая рассчитала эти результаты? Это так же просто, как =B3-B2:)

Обратите внимание, что нужно четко знать, что именно вы хотите подсчитать: «дни включительно» или «дни между».

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

Для этого выберите клетку или диапазон ячеек с числами, которые вы хотите представить как даты, и нажмите комбинацию клавиш Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». На вкладке «Числовые форматы» в пункте «Дата», выберите подходящий для вас внешний вид в разделе «Тип» и нажмите кнопку «ОК».

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

Как рассчитать разницу в днях?

Самый простой способ рассчитать дни между датами в Эксель - вычесть одну из другой:

= Более новая - Более старая

Как мы уже сказали ранее, чтобы узнать, сколько времени пройдет в периоде между ячейками В3 и B2, используйте следующую формулу:

где В2 - более ранняя, а B3 - более.

Результатом подсчета является целое число, которое представляет количество дней внутри временного интервала.

Однако, будьте здесь внимательны: при подобном подсчете отработанных дней в том случае, если человек работал и в первый, и в последний день, то 1 день будет «потерян». Добавьте в вашу формулу +1 день.

Как работает эта формула?

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

В нашем примере формула в C5 на самом деле вычитает 43892 (числовое значение 2 марта 2020г.) из 43900 (числовое значение 10 марта 2020г.) и возвращает результат 8 дней.

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

Рассчитать разницу между датами в Excel с помощью РАЗНДАТ (DATEDIF).

Другой способ — как в Экселе посчитать дни в заданном временном интервале — использование функции РАЗНДАТ (в английском варианте – DATEDIF), которая специально разработана для расчета разности во времени и представления её в различных единицах: дни, месяцы и годы.

А вот ее синтаксис:

дата_нач и дата_кон - две временных отметки, разницу между которыми необходимо рассчитать,

аргумент – указывает, как представить результат; может иметь одно из следующих значений:

Y Число полных лет.
M Число полных месяцев.
D Число дней.
MD Разница в днях (значения месяца и года не учитываются).
YM Разница в месяцах (значения дня и года не учитываются).
YD Разница в днях, игнорируя значения года.

Чтобы получить количество дней между двумя датами, укажите момент начала в первом аргументе, срок окончания во втором, и «d» в последнем третьем:

РАЗНДАТ(дата_нач; дата_кон; "d")

DATEDIF (start_date, end_date, "d")

Примечание. РАЗНДАТ (DATEDIF) - недокументированная функция, то есть ее нет в мастере функций в Excel. Вы не сможете ее вставить при помощи кнопки fx или меню Вставка — Функция. Чтобы создать формулу РАЗНДАТ в таблице Экселя, вам нужно вписать ее название руками и ввести все аргументы вручную, подсказок ввода не будет.

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

Ссылки на ячейки. Например, следующая формула вернет количество дней между A3 и B3:

Текстовые выражения. Excel понимает даты в различных форматах, например, «2 март 2020», «20.03.2020», «20/03/2020», «2020/03/20», «20-03-2020», «2020-03-20» и т.д.

Вот примеры расчета количества дней во временных промежутках:

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

Порядковые номера. Поскольку Microsoft Excel хранит каждую дату как порядковый номер, начинающийся с 1 января 1900 года, вы можете поместить числа, соответствующие датам, непосредственно в формулу. Хотя этот метод полностью поддерживается, все же он не является надежным, поскольку нумерация различается в разных компьютерных системах (Windows и MacOS).

В системе дат 1900 года следующая формула является еще одним способом вычисления месяцев по условиям из предыдущего примера (2 марта 2020 года и 10 марта 2020 года):

Результаты выполнения других функций. Например, следующая формула подсчитывает, срок между сегодня (18 марта 2020г.) и 9 мая 2020 года.

1. Разница в днях.

В нашем примере формула расчета в D3 выглядит следующим образом:

Обратите также внимание, что при простом вычитании дат, которые также содержат время, мы получаем в результате дробное число. Так произошло в С5 и С6. Что делать с такими дробями – поговорим чуть позже. А вот время здесь просто игнорируется. В этом вы можете убедиться в диапазоне D5:I6.

Аналогичный результат можно получить формулами:

Время, то есть дробная часть, будет отброшено.

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

Более подробно о расчете рабочих дней читайте здесь.

2. Разница в полных месяцах.

Обращаемся к ячейке Е3:

Обратите внимание, выражение вернет количество полных месяцев. В нашем примере 13 месяцев и 7 дней «округлены» ровно до 13. И последний день интервала, как мы уже говорили, эта функция не учитывает. В частности,

возвращает результат 0, так как последний день не включается в расчет. Вы это видите в ячейке E7. Это также будет важно при расчете стажа и отработанного времени. Учитывайте эту особенность и просто добавляйте 1 дополнительный день!

Формула может быть заменена альтернативным выражением:

3. Разница в полных годах.

Формула в ячейке F3 вернет количество полных лет:

Опять же, считается только полный год, остальное отбрасывается.

Ну и альтернативный способ расчета —

4. Разница в полных месяцах без учета лет.

Используем в I3 аргумент "YM".

Второй параметр и конечная точка у нас – 10.04.2020 г. Поскольку годы мы здесь игнорируем, то год первого параметра также становится 2020. И мы теперь сравниваем 10.04.2020 и 2.03.2020г.

Результат – 1 полный месяц.

А вот если начало, предположим, будет 01.09.2015г.? Ведь ее нельзя модифицировать до 01.09.2020, поскольку тогда функция выдаст ошибку. И программа ее меняет на 01.09.2019. Результат здесь – 7 полных месяцев.

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

Ну и альтернативный расчет –

5. Разница в днях без учета месяцев и лет.

Здесь немного сложнее. Итак, в G3 пишем:

Разберем, как получен результат в G3. Поскольку для нас не важны месяц и год, мы в начальной дате меняем их на текущие. То есть вместо 02.03.2019 рассматриваем 02.04.2020. Сравниваем с 10.04.2020 и получаем 8 дней. Есть ли в таком результате смысл? Вряд ли. Поэтому и не рекомендуется применение этого аргумента как отдельного расчета.

Быть может, поэтому РАЗНДАТ и нет в списке официальных функций Excel.

6. Разница в днях без учета лет.

То же самое, что и в предыдущем аргументе. Считаем количество суток между двумя точками без учета лет.

Для начального момента — 02.03.2019 и конечного – 10.04.2020, разница в 39 дней выглядит явной ошибкой. Аналогично – между 01.09.2015 и 10.04.2020 совсем не 222 дня.

Следовательно, использование этого аргумента также не рекомендуем.

А зачем же всё это нужно? Ответ читайте в следующем разделе.

Как посчитать разницу в днях, месяцах и годах.

Чтобы подсчитать, сколько полных лет, месяцев и дней составляет разница "от" и "до" с использованием одной формулы, вы просто объединяете три функции в одно выражение:

Если вы не хотите отображать нулевые значения лет, месяцев и дней, то выражение можно изменить, добавив условие ЕСЛИ:

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

=ЕСЛИ(РАЗНДАТ(B2;B3;"y");РАЗНДАТ(B2;B3;"y")&" "&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;B3;"y")-11;100);9);10);"[<1]\го\д;[<4]\го\да;лет")&" ";)& ЕСЛИ(РАЗНДАТ(B2;B3;"ym");РАЗНДАТ(B2;B3;"ym")&" меся"&ТЕКСТ(ОСТАТ(РАЗНДАТ(B2;B3;"ym")-1; 11);"[<1]ц;[<4]ца;цев")&" ";)& ЕСЛИ(РАЗНДАТ(B2;B3;"md");РАЗНДАТ(B2;B3;"md")&" д"&ТЕКСТ(ОСТАТ(МАКС(ОСТАТ(РАЗНДАТ(B2;B3;"md")-11;100);9); 10);"[<1]ень;[<4]ня;ней");)

Выглядит угрожающе, но зато работает.

Подсчет с помощью функции ДНИ

У пользователей Excel 2013 и 2016 есть еще один удивительно простой способ посчитать количество дней во временном интервале — функция ДНИ.

Обратите внимание, что по сравнению с РАЗНДАТ, функция ДНИ требует указания дат в обратном порядке.

Итак, наше выражение принимает вид:

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

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

Как рассчитать количество дней между сегодня и другой датой.

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

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

=СЕГОДНЯ() – Дата_в_прошлом

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

=Дата_в_будущем - СЕГОДНЯ()

В качестве примера, давайте посчитаем разницу между текущим моментом времени и более ранним - в A4 (сегодня 18 марта 2020г.):

А теперь давайте выясним, сколько осталось от сегодняшнего дня (18.03.2020г.) до более позднего срока:

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

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

Как показано в первой части этого руководства, функция РАЗНДАТ (DATEDIF) — довольно универсальная, подходящая для самых разных целей. Однако есть один существенный недостаток — она не документирована Microsoft, то есть вы не найдете РАЗНДАТ в списке функций и не увидите всплывающих подсказок для аргументов, когда начнете вводить формулу в ячейку. Чтобы иметь возможность использовать функцию РАЗНДАТ в своих таблицах, вы должны запомнить ее синтаксис и ввести все аргументы вручную, что может занять много времени и привести к ошибкам, особенно у начинающих.

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

Выберите ячейку, в которую вы хотите вставить формулу.
Перейдите на вкладку Ablebits Tools и нажмите кнопку Мастер даты и времени (Date & Time Wizard).
Появится диалоговое окно мастера даты и времени, где вы переходите на вкладку «Разница (Difference)» и вводите необходимые данные для расчёта:
- Щелкните поле Дата 1 и выберите ячейку, содержащую первую дату.
- Щелкните поле Дата 2 и укажите ячейку со второй датой.
- Выберите желаемую единицу или комбинацию единиц времени из раскрывающегося меню «Разница в (Difference in)» . При этом программа позволяет предварительно просмотреть результат в поле и формулу в ячейке.

Инструмент может рассчитать разницу между двумя датами в 15 форматах:

Годы
Месяцы
Недели
Дни
Годы + месяцы
Годы + недели
Годы + дни
Месяцы + недели
Месяцы + дни
Недели + дни
Годы + месяцы + недели
Годы + месяцы + дни
Годы + недели + дни
Месяцы + недели + дни
Годы + месяцы + недели + дни

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

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

К сожалению, метки даты и времени вставляются по-английски. Однако, поскольку надстройка формирует для вас не текст, а формулу расчета (что вы и видите на скриншоте), то откорректировать формулу и пользоваться в дальнейшем ее русской версией не слишком сложно.

Да и вряд ли вы самостоятельно напишете такую сложную конструкцию 😊

Для наиболее удобного представления результатов доступно еще несколько дополнительных опций:

Исключить годы и / или месяцы из расчетов. (Годы и / или месяцы просто будут отброшены).
Показывать или не показывать текстовые метки времени, такие как дни, месяцы, недели и годы .
Показывать или не показывать нулевые единицы (0 месяцев, например) .
Возвращает результаты как отрицательные значения, если Дата 1 (дата начала) больше, чем Дата 2 (дата окончания). Думаю, вы сталкивались с ситуацией, когда попытка вычесть из более ранней даты более позднюю приводила к ошибке. Здесь вы просто получите отрицательную разность.

Более подробная информация доступна здесь.

Преимущества использования мастера формул даты и времени

Помимо скорости и простоты, Мастер даты и времени предоставляет еще несколько преимуществ:

В отличие от обычной формулы РАЗНДАТ, для формулы, созданной мастером, не важно, какая из двух дат меньше, а какая больше. Разница всегда рассчитывается идеально, даже если Дата 1 (дата начала) больше, чем Дата 2 (дата окончания).
Мастер поддерживает все возможные единицы измерения (дни, недели, месяцы и годы) и позволяет вам выбирать подходящий вариант из 11 различных комбинаций этих единиц.
Формулы, которые мастер создает для вас, являются обычными формулами Excel, поэтому вы можете редактировать, копировать или перемещать их как обычно. Вы также можете поделиться своими таблицами с другими людьми, и все формулы останутся на своих местах, даже если у кого-то нет Ultimate Suite в их Excel.

Если вам интересно протестировать этот калькулятор дат, а также открыть для себя еще 60 надстроек для упрощения работы и экономии времени в Excel, то можете загрузить пробную версию Ultimate Suite. Если вам понравились инструменты и вы решили купить лицензию, не пропустите это специальное предложение для читателей нашего сайта.

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

Также рекомендуем статьи о работе с датами:

Как вывести месяц из даты - На примерах мы покажем, как получить месяц из даты в таблицах Excel, преобразовать число в его название и наоборот, а также многое другое. Думаю, вы уже знаете, что дата в… Функция Excel СЕГОДНЯ — синтаксис и основные применения - Функция СЕГОДНЯ в Excel делает именно то, на что указывает ее имя - возвращает сегодняшнюю дату. Синтаксис ее настолько прост, насколько это вообще возможно - у нее вообще нет аргументов. Всякий… Как быстро вставить сегодняшнюю дату в Excel? - Это руководство показывает различные способы ввода дат в Excel. Узнайте, как вставить сегодняшнюю дату и время в виде статической метки времени или динамических значений, как автоматически заполнять столбец или строку… Количество рабочих дней между двумя датами в Excel - Довольно распространенная задача: определить количество рабочих дней в период между двумя датами – это частный случай расчета числа дней, который мы уже рассматривали ранее. Тем не менее, в Excel для… 8 способов — как посчитать возраст в Excel по дате рождения. - В Excel нет специальной функции, чтобы посчитать возраст, однако существует несколько различных способов его определения на основе даты рождения. Вы узнаете несколько приемов, как рассчитать его в количестве полных лет,… Как преобразовать текст в дату в Excel или извлечь ее из сложной фразы? - Сейчас вы научитесь использовать функции Excel для преобразования текста в дату. Вы также узнаете, как это сделать без использования формул. Поскольку Excel - не единственное приложение, которое вы используете, иногда…

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

Расчет количества дней

Прежде, чем начать работать с датами, нужно отформатировать ячейки под данный формат. В большинстве случаев, при введении комплекта символов, похожего на дату, ячейка сама переформатируется. Но лучше все-таки сделать это вручную, чтобы подстраховать себя от неожиданностей.

«Формат ячейки…»

Ctrl+1

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

Способ 1: простое вычисление

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

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

Способ 2: функция РАЗНДАТ

Для вычисления разности в датах можно также применять специальную функцию РАЗНДАТ. Проблема в том, что в списке Мастера функций её нет, поэтому придется вводить формулу вручную. Её синтаксис выглядит следующим образом:

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

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

Способ 3: вычисление количеств рабочих дней

В Экселе также имеется возможность произвести вычисление рабочих дней между двумя датами, то есть, исключая выходные и праздничные. Для этого используется функция ЧИСТРАБНИ. В отличие от предыдущего оператора, она присутствует в списке Мастера функций. Синтаксис у этой функции следующий:

В этой функции основные аргументы, такие же, как и у оператора РАЗНДАТ – начальная и конечная дата. Кроме того, имеется необязательный аргумент «Праздники».

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

«Вставить функцию»

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

Как видим, программа Excel предоставляет своим пользователем довольно удобный инструментарий для расчета количества дней между двумя датами. При этом, если нужно рассчитать просто разницу в днях, то более оптимальным вариантом будет применение простой формулы вычитания, а не использование функции РАЗНДАТ. А вот если требуется, например, подсчитать количество рабочих дней, то тут на помощь придет функция ЧИСТРАБДНИ. То есть, как всегда, пользователю следует определиться с инструментом выполнения после того, как он поставил конкретную задачу.

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

ПРИМЕР 1. Необходимо определить среднее значение продаж, превышающее 70000 рублей.

Ставим равно (=) и пишем функцию СРЗНАЧЕСЛИ. Открывает круглую скобку и вводим параметры функции.

Мы будем выполнять оценку и вычислять среднее число для одного и того же диапазона ячеек. С (С2 по С7) в столбце продажи.

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

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

Функция оценивает сколько ячеек в столбце продажи соответствуют условию >70000. Таких ячеек 3 (С3, С5, С7). Затем функция вычисляет для них усредненный показатель.

Стандартный способ вычисления

Самый простой и известный способ найти среднее арифметическое набора чисел — это воспользоваться специальной кнопкой на ленте Microsoft Excel. Выделяем диапазон чисел, расположенных в столбце или в строке документа. Находясь во вкладке «Главная», жмем на кнопку «Автосумма», которая расположена на ленте в блоке инструментов «Редактирование». Из выпадающее списка выбираем пункт «Среднее».

После этого, с помощью функции «СРЗНАЧ», производится расчет. В ячейку под выделенным столбцом, или справа от выделенной строки, выводится средняя арифметическая данного набора чисел.

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

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

Вычисление с помощью Мастера функций

Для случаев, когда нужно подсчитать среднюю арифметическую массива ячеек, или разрозненных ячеек, можно использовать Мастер функций. Он применяет все ту же функцию «СРЗНАЧ», известную нам по первому методу вычисления, но делает это несколько другим способом.

Кликаем по ячейке, где хотим, чтобы выводился результат подсчета среднего значения. Жмем на кнопку «Вставить функцию», которая размещена слева от строки формул. Либо же, набираем на клавиатуре комбинацию Shift+F3.

Запускается Мастер функций. В списке представленных функций ищем «СРЗНАЧ». Выделяем его, и жмем на кнопку «OK».

Открывается окно аргументов данной функции. В поля «Число» вводятся аргументы функции. Это могут быть как обычные числа, так и адреса ячеек, где эти числа расположены. Если вам неудобно вводить адреса ячеек вручную, то следует нажать на кнопку расположенную справа от поля ввода данных.

После этого, окно аргументов функции свернется, а вы сможете выделить ту группу ячеек на листе, которую берете для расчета. Затем, опять нажимаете на кнопку слева от поля ввода данных, чтобы вернуться в окно аргументов функции.

Если вы хотите подсчитать среднее арифметическое между числами, находящимися в разрозненных группах ячеек, то те же самые действия, о которых говорилось выше, проделывайте в поле «Число 2». И так до тех пор, пока все нужные группы ячеек не будут выделены.

После этого, жмите на кнопку «OK».

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

Панель формул

Существует ещё третий способ запустить функцию «СРЗНАЧ». Для этого, переходим во вкладку «Формулы». Выделяем ячейку, в которой будет выводиться результат. После этого, в группе инструментов «Библиотека функций» на ленте жмем на кнопку «Другие функции». Появляется список, в котором нужно последовательно перейти по пунктам «Статистические» и «СРЗНАЧ».

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

Дальнейшие действия точно такие же.

Вычисление среднего значения через встроенный инструмент в Excel

Выполнить задачу можно выделив нужный свод данных и на главной странице кликнув на «Функции» и выбрав «Среднее».

Готово система добавила ниже ячейку со средним значением.

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

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

Среднее значение через формулу в Эксель

Сделать задачу также можно через стандартную формулу для чего прописываем в ячейке:

Что возвращает функция

Возвращает число, соответствующее среднему арифметическому от заданного диапазона данных.

Синтаксис

=AVERAGE(number1, [number2], …) – английская версия

=СРЗНАЧ(число1;[число2];…) – русская версия

Примеры использования функции СРЗНАЧ в Excel

Функция СРЗНАЧ относится к группе «Статистические». Поэтому для вызова данной функции в Excel необходимо выбрать инструмент: «Формулы»-«Библиотека функций»-«Другие функции»-«Статистические»-«СРЗНАЧ». Или вызвать диалоговое окно «Вставка функции» (SHIFT+F3) и выбрать из выпадающего списка «Категория:» опцию «Статистические». После чего в поле «Выберите функцию:» будет доступен список категории со статистическими функциями где и находится СРЗНАЧ, а также СРЗНАЧА.

Если есть диапазон ячеек B2:B8 с числами, то формула =СРЗНАЧ(B2:B8) будет возвращать среднее значение заданных чисел в данном диапазоне:

Синтаксис использования следующий: =СРЗНАЧ(число1; [число2]; …), где первое число обязательный аргумент, а все последующие аргументы (вплоть до числа 255) необязательны для заполнена. То есть количество выбранных исходных диапазонов не может превышать больше чем 255:

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

Аргументы функции СРЗНАЧ могут быть представлены не только числами, но и именами или ссылками на конкретный диапазон (ячейку), содержащий число. Учитывается логическое значение и текстовое представление числа, которое непосредственно введено в список аргументов.

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

Результат выполнения функции в примере на картинке ниже – это число 4, т.к. логические и текстовые объекты игнорируются. Поэтому:

(5 + 7 + 0 + 4) / 4 = 4

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

Результаты еще 4-х задач сведены в таблицу ниже:

Использование арифметического выражения

Как мы знаем, среднее значение равняется сумме чисел, разделенных на их количество. Данную формулу можно использовать и в Экселе.

Встаем в нужную ячейку, ставим знак “равно” и пишем арифметическое выражение по следующем принципу:
=(Число1+Число2+Число3. )/Количество_слагаемых .
Примечание: в качестве числа может быть указано как конкретное числовое значение, так и ссылка на ячейку. В нашем случае, давайте попробуем посчитать среднее значение чисел в ячейках B2,C2,D2 и E2.
Конечный вид формулы следующий: =(B2+E2+D2+E2)/4 .
Когда все готово, жмем Enter, чтобы получить результат.

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

Инструменты на ленте

Данный метод основан на использовании специального инструмента на ленте программы. Вот как это работает:

Выделяем диапазон ячеек с числовыми данными, для которых мы хотим определить среднее значение.
Переходим во вкладку “Главная” (если находимся не в ней). В разделе инструментов “Редактирование” находим значок “Автосумма” и щелкаем по небольшой стрелке вниз рядом с ним. В раскрывшемся перечне кликаем по варианту “Среднее”.
Сразу же под выделенным диапазоном отобразится результат, который и является средним значением по всем отмеченным ячейкам.
Если мы перейдем в ячейку с результатом, то в строке формул увидим, какая функция была использована программой для расчетов – это оператор СРЗНАЧ, аргументами которого является выделенный нами диапазон ячеек.

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

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

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

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

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

Альтернативный способ использования “Среднее” на ленте:

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

Ввод функции в ячейку вручную

Как и все остальные функции, формулу СРЗНАЧ с нужными аргументами можно сразу же прописать в нужной ячейке.

В общем, синтаксис функции СРЗНАЧ выглядит так:

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

Просто встаем в нужную ячейку и, поставив знак “равно”, пишем формулу, перечислив аргументы через символ “точка с запятой”. Вот, как это выглядит со ссылками на ячейки в нашем случае. Допустим, мы решили включить в подсчет всю первую строку и только три значения из второй:

Когда формула полностью готова, нажимаем клавишу Enter и получаем готовый результат.

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

Соответствие английских и русских формул в Excel

Функции баз данных — Database

Функции даты и времени — Date & Time

Инженерные функции — Engineering

Финансовые функции — Financial

Проверка свойств и значений и Информационные функции — Information

Логические функции — Logical

Ссылки и массивы — Lookup & Reference

Математические и тригонометрические функции — Math & Trig

Статистические функции — Statistical

Текстовые функции — Text

Заключение

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

Читайте также: