17 авг. 2022 г.
читать 2 мин
Вы можете использовать следующую формулу для расчета среднего значения по дате в электронной таблице Excel:
=AVERAGEIF( A1:A10 , C1 , B1:B10 )
Эта конкретная формула вычисляет среднее значение в диапазоне ячеек B1:B10 , только если соответствующие ячейки в диапазоне A1:A10 равны дате в ячейке C1 .
В следующем примере показано, как использовать эту формулу на практике.
Пример: вычислить среднее значение по дате в Excel
Предположим, у нас есть следующий набор данных, который показывает продажи некоторого продукта в разные даты:
Теперь предположим, что мы хотим рассчитать средний объем продаж по дате.
Чтобы создать список уникальных дат, мы можем использовать следующую формулу:
=SORT(UNIQUE( A2:A15 ))
Мы введем эту формулу в ячейку D2 :
По умолчанию Excel преобразует дату в количество дней с 01.01.1900.
Чтобы преобразовать эти числа в распознаваемый формат даты, просто выделите диапазон ячеек D2:D6 , затем щелкните стрелку раскрывающегося списка формата и выберите «Короткая дата» :
Даты будут преобразованы в распознаваемый формат даты:
Затем мы можем использовать следующую формулу для расчета средних продаж по дате:
=AVERAGEIF( $A$2:$A$15 , D2 , $B$2:$B$15 )
Мы введем эту формулу в ячейку E2 , затем скопируем и вставим ее в каждую оставшуюся ячейку в столбце E:
Из вывода мы видим:
- Средний объем продаж на 04.01.2022 составляет 5 .
- Средний объем продаж на 07.01.2022 – 6333 .
- Средний объем продаж на 07.02.2022 — 6333 .
И так далее.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Как считать по месяцам в Excel
Как считать по годам в Excel
Как подсчитать, если ячейки содержат текст в Excel
Написано
Замечательно! Вы успешно подписались.
Добро пожаловать обратно! Вы успешно вошли
Вы успешно подписались на кодкамп.
Срок действия вашей ссылки истек.
Ура! Проверьте свою электронную почту на наличие волшебной ссылки для входа.
Успех! Ваша платежная информация обновлена.
Ваша платежная информация не была обновлена.
В Excel мы можем подсчитывать значения суммы между двумя заданными датами диапазона данных, чтобы усреднить числа между двумя датами, также можно решить с помощью некоторых формул. Здесь я расскажу о том, как рассчитать среднее значение между двумя датами в Excel?
Вычислить среднее значение между двумя датами с помощью формул
Вычислить среднее между двумя датами с Kutools for 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. Выберите столбец даты, который вы хотите усреднить между двумя датами, затем щелкните Кутулс > Выберите > Выбрать определенные ячейки, см. снимок экрана:
2. В Выбрать определенные ячейки диалоговое окно, выберите Весь ряд из Тип выбора раздел, а затем выберите Больше или равно и Меньше или равно условия и введите конкретную дату рядом с ними отдельно, см. снимок экрана:
3. Затем были выбраны строки между двумя конкретными датами, скопируйте и вставьте строки на другой лист, а затем примените = Среднее (B1: B6) формулу для расчета результата, см. снимок экрана:
Скачать и бесплатную пробную версию Kutools for Excel Сейчас !
Демонстрация: вычислить среднее значение между двумя датами с помощью Kutools for Excel
Статьи по теме:
Как усреднить по дню недели в Excel?
Как усреднить ячейки на основе нескольких критериев в Excel?
Как усреднить каждые 5 строк или столбцов в Excel?
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
СРЗНАЧЕСЛИМН (функция СРЗНАЧЕСЛИМН)
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Еще…Меньше
В этой статье описаны синтаксис формулы и использование функции СРЗНАЧЕСЛИМН в Microsoft Excel.
Описание
Возвращает среднее значение (среднее арифметическое) всех ячеек, которые соответствуют нескольким условиям.
Синтаксис
СРЗНАЧЕСЛИМН(диапазон_усреднения;диапазон_условий1;условие1;[диапазон_условий2;условие2];…)
Аргументы функции СРЗНАЧЕСЛИМН указаны ниже.
-
Диапазон_усреднения: обязательный. Одна или несколько ячеек для вычисления среднего с числами или именами, массивами или ссылками, содержащими числа.
-
Диапазон_условий1, диапазон_условий2, … параметр «диапазон_условий1» — обязательный, остальные диапазоны условий — нет. От 1 до 127 интервалов, в которых проверяется соответствующее условие.
-
Условие1, условие2, … Параметр «условие1» является обязательным, остальные условия — нет. От 1 до 127 условий в форме числа, выражения, ссылки на ячейку или текста, определяющих ячейки, для которых будет вычисляться среднее. Например, условие может быть выражено следующим образом: 32, «32», «>32», «яблоки» или B4.
Замечания
-
Если «диапазон_усреднения» является пустым или текстовым значением, то функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.
-
Если ячейка в диапазоне условий пустая, функция СРЗНАЧЕСЛИМН обрабатывает ее как ячейку со значением 0.
-
Ячейки в диапазоне, которые содержат значение ИСТИНА, оцениваются как 1; ячейки в диапазоне, которые содержат значение ЛОЖЬ, оцениваются как 0 (ноль).
-
Каждая ячейка в аргументе «диапазон_усреднения» используется в вычислении среднего значения, только если все указанные для этой ячейки условия истинны.
-
В отличие от аргументов диапазона и условия в функции СРЗНАЧЕСЛИ, в функции СРЗНАЧЕСЛИМН каждый диапазон_условий должен быть одного размера и формы с диапазоном_суммирования.
-
Если ячейки в параметре «диапазон_усреднения» не могут быть преобразованы в численные значения, функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.
-
Если нет ячеек, которые соответствуют условиям, функция СРЗНАЧЕСЛИМН возвращает значение ошибки #ДЕЛ/0!.
-
В этом аргументе можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, то перед ними следует поставить знак тильды (~).
Примечание: Функция СРЗНАЧЕСЛИМН измеряет среднее значение распределения, то есть расположение центра набора чисел в статистическом распределении. Существует три наиболее распространенных способа определения среднего значения:
-
Среднее значение — это среднее арифметическое, которое вычисляется путем сложения набора чисел с последующим делением полученной суммы на их количество. Например, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.
-
Медиана — это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Например, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.
-
Мода — это число, наиболее часто встречающееся в данном наборе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.
При симметричном распределении множества чисел все три значения центральной тенденции будут совпадать. При смещенном распределении множества чисел значения могут быть разными.
Примеры
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
|
Студент |
Первый |
Второй |
Последний |
|---|---|---|---|
|
Тест |
Тест |
Экзамен |
|
|
Оценка |
Оценка |
Оценка |
|
|
Климов |
75 |
85 |
87 |
|
Покровская |
94 |
80 |
88 |
|
Жданов |
86 |
93 |
Не выполнено |
|
Быков |
Не выполнено |
75 |
75 |
|
Формула |
Описание |
Результат |
|
|
=СРЗНАЧЕСЛИМН(B2:B5; B2:B5; «>70»; B2:B5; «<90») |
Средняя оценка за первый тест у всех студентов, которая находится в промежутке от 70 до 90 баллов (80,5). Оценка «Не выполнено» не учитывается при расчете, так как она не является числовым значением. |
75 |
|
|
=СРЗНАЧЕСЛИМН(C2:C5;C2:C5;»>95″) |
Средняя оценка за второй тест у всех студентов, которая выше 95 баллов. Так как нет оценок выше 95 баллов, возвращается значение #ДЕЛ/0!. |
#ДЕЛ/0! |
|
|
=СРЗНАЧЕСЛИМН(D2:D5;D2:D5,»<>неудовлетворительно»;D2:D5;»>80″) |
Средняя оценка за последний экзамен для всех студентов, которая выше 80 баллов (87,5). Оценка «Не выполнено» не учитывается при расчете, так как она не является числовым значением. |
87,5 |
Пример 2
|
Тип |
Цена |
Город |
Количество спален |
Гараж? |
|---|---|---|---|---|
|
Коттедж |
230000 |
Иркутск |
3 |
Нет |
|
Теремок |
197000 |
Омск |
2 |
Да |
|
Вилла |
345678 |
Омск |
4 |
Да |
|
Два этажа роскоши |
321900 |
Иркутск |
2 |
Да |
|
Вилла Тюдор |
450000 |
Омск |
5 |
Да |
|
Колониальная классика |
395000 |
Омск |
4 |
Нет |
|
Формула |
Описание |
Результат |
||
|
=СРЗНАЧЕСЛИМН(B2:B7; C2:C7; «Омск»; D2:D7; «>2»; E2:E7; «Да») |
Средняя цена дома в Омске как минимум с тремя спальнями и гаражом (397839) |
397839 |
||
|
=СРЗНАЧЕСЛИМН(B2:B7; C2:C7; «Иркутск»; D2:D7; «<=3»; E2:E7; «Нет») |
Средняя цена дома в Иркутске не более чем с тремя спальнями без гаража |
230000 |
Нужна дополнительная помощь?
SanDara
Гость
#1
21.10.2010 10:39:48
Мне надо найти среднее значение по дате столбца D(данные).
Даты идут не последовательно(в мае несколько дней,потом вдруг может пойти август),количество строк по датам может быть разным.
Понимаю как делать это в ручную,но данных очень много,хотелось бы автоматизации.
Боюсь, что не совсем внятно объяснила, прикрепляю пример.
С уважением Сардана.
Прикрепленные файлы
- post_166343.xls (16.5 КБ)
В этом учебном материале по Excel мы рассмотрим примеры того как вычислить среднее значение по месяцам.
Общая формула
|
=СРЗНАЧЕСЛИМН(Значения;Даты;«>=»&A1;Даты;«<=»&КОНМЕСЯЦА(A1)) |
Описание
Для усреднения значений по месяцам вы можете использовать формулу, основанную на функции СРЗНАЧЕСЛИМН, с помощью функции КОНМЕСЯЦА. В показанном примере формула в ячейке F4 имеет следующий вид:
|
=СРЗНАЧЕСЛИМН(Суммы;Даты;«>=»&F5;Даты;«<=»&КОНМЕСЯЦА(F5;0)) |
В этой формуле используются именованные диапазоны «Суммы» (D5:D14) и «Даты» (C5:C14).
Пояснение
Функция СРЗНАЧЕСЛИМН может усреднять диапазоны на основе нескольких условий. В этом случае мы настраиваем СРЗНАЧЕСЛИМН на усреднение сумм по месяцам, используя два условия:
- даты совпадения больше или равны первому дню месяца,
- даты совпадений меньше или равны последнему дню месяца.
Если бы мы жестко закодировали даты января 2020 года в формуле с помощью функции ДАТА, это выглядело бы так.
|
=СРЗНАЧЕСЛИМН(Суммы;Даты;«>=»&ДАТА(2020;1;1);Даты;«<=»&ДАТА(2020;1;31)) |
Но мы не хотим жестко запрограммировать даты, мы хотим, чтобы Excel генерировал эти даты за нас. Обычно это неудобно, потому что если вы добавляете названия месяцев в виде текста (например, «Январь», «Февраль», «Март» и т.д.). В столбце F, вам придется приложить дополнительные усилия, чтобы создать даты, которые можно использовать в качестве условий.
Однако в этом случае мы используем простой трюк, чтобы упростить задачу: в столбце F вместо ввода названий месяцев мы добавляем фактические даты первого числа каждого месяца (01.01.2020, 01.02.2020, 01.03.2020 и т.д.) И используйте произвольный формат даты («[$-ru-RU]ММММ;@») для отображения названий месяцев.
Это упрощает построение необходимых нам условий для СРЗНАЧЕСЛИМН. Чтобы сопоставить даты, которые больше или равны первому числу месяца, мы используем ">="&E4
И для сопоставления дат, меньших или равных последнему дню месяца, мы используем "<="&КОНМЕСЯЦА(E4;0)
КОНМЕСЯЦА автоматически возвращает последний день того же месяца, потому что мы указываем ноль в качестве аргумента месяцев.
Примечание: конкатенация с амперсандом (&) необходима при построении условий на основе ссылки на ячейку.