- Советы по Excel
- Функции Excel
- Формулы Excel
- Excel Диаграммы
- Советы по словам
- Советы по Outlook
Мы представили способ рассчитать средние значения за день / месяц / квартал / час с помощью сводной таблицы в Excel раньше, но этот метод не может рассчитать средние значения за неделю. Здесь я расскажу, как легко рассчитывать средние за неделю с помощью столбцов справки в Excel.
- Рассчитывайте средние за неделю с помощью функций Excel
- Пакетный расчет всех недельных средних значений с помощью Kutools for Excel
Рассчитывайте средние за неделю с помощью функций Excel
Предположим, у нас есть таблица покупок, показанная на следующем снимке экрана, и мы возьмем эту таблицу в качестве примера, чтобы подробно описать способ расчета средних за неделю.
Шаг 1. Помимо исходной таблицы покупок введите НеделяNUM в ячейке D1, а затем введите формулу = НЕДЕЛ. (A2,2) (Примечание: A2 — это ячейка с датой покупки в столбце «Дата / время») в ячейку D2, а затем перетащите маркер заполнения в нужный нам диапазон. Теперь номера недель отображаются в столбце WeekNUM. Смотрите скриншот:
Шаг 2: Этот шаг не является обязательным. Если даты покупки охватывают несколько лет, вы можете рассчитать средние за неделю в том же году. Войти Год в ячейке E1 и введите формулу = ГОД (A2) (Примечание: A2 — это ячейка с датой покупки в столбце «Дата / время») в ячейку E2, а затем перетащите маркер заполнения в нужный нам диапазон.
Шаг 3: В ячейке F1 введите Средняя, а затем введите формулу =IF(AND(D2=D1,E2=E1),»»,AVERAGEIFS($C$2:$C$39,$D$2:$D$39,D2,$E$2:$E$39,E2)) в ячейку F2, а затем перетащите маркер заполнения в нужный нам диапазон.
Ноты:
(1) Если две покупки были совершены на одной неделе одного года, формула = ЕСЛИ (И (D2 = D1, E2 = E1), «», СРЕДНЕЕСЛИМН ($ C $ 2: $ C $ 39, $ D $ 2: $ D $ 39, D2, $ E $ 2: $ E $ 39, E2)) вернет пустое значение; в противном случае будет возвращено среднее значение за неделю.
(2) И в этой формуле D1 и D2 — это ячейки с номерами недель, E1 и E2 — это ячейки с годами, $ C $ 2: $ C $ 39 — это диапазон, который вы рассчитываете в среднем за неделю, $ D $ 2: $ D $ 39 — это Столбец WeekNUM вы будете оценивать по номерам недель, $ E $ 2: $ E $ 39 — это столбец Год, который вы будете оценивать по годам, и вы можете изменить их в зависимости от ваших потребностей.
(3) Если вы хотите рассчитать средние за неделю, но игнорируя годы, вы можете ввести формулу =AVERAGEIF($D$2:$D$39,D2,$C$2:$C$39) в ячейку F2 и перетащите маркер заполнения в нужный диапазон. И вы увидите средние значения за неделю, как показано на скриншоте ниже:
Пакетный расчет всех недельных средних значений с помощью Kutools for Excel
Этот метод представит Kutools for Excel’s К фактическому полезности и Расширенные ряды комбинирования утилита для массового расчета всех средних значений за неделю.
1. Помимо исходной таблицы покупок введите Неделя в ячейке D1, а затем введите формулу = НЕДЕЛ. (A2,2) (Примечание: A2 — это ячейка даты в столбце «Дата / время») в ячейку D2, а затем перетащите маркер заполнения в нужный нам диапазон.
2. Выберите таблицу покупок с новым столбцом «Неделя» и нажмите кнопку Кутулс > Содержание > Расширенные ряды комбинирования.
3. В открывшемся диалоговом окне «Объединить строки на основе столбца» вам необходимо (см. Снимок экрана ниже):
(1) Щелкните столбец Фрукты в списке столбцов и щелкните значок Сочетать > Запятая;
(2) Щелкните столбец Сумма, затем щелкните значок Рассчитать > Средняя;
(3) Щелкните столбец «Неделя» и щелкните значок Основной ключ;
(4) Щелкните значок Ok кнопку.
Пока все средние значения за неделю рассчитываются с соответствующими фруктами, перечисленными помимо средних значений, как показано на скриншоте ниже:
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now
Демонстрация: вычисление среднего за неделю в Excel
Статьи по теме:
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (0)
Оценок пока нет. Оцените первым!
Подписывайтесь на Нас
Copyright © 2009 —
www.extendoffice.ком. | Все права защищены. Питаться от ExtendOffice, | Карта сайта
Microsoft и логотип Office являются товарными знаками или зарегистрированными товарными знаками Microsoft Corporation в США и / или других странах.
|
YouRock  Пользователь Сообщений: 161 |
добрый день , уважаемые форумчане и гуру экселя! Прикрепленные файлы
50 times helped — they forgot, 1 time refused — they memorized |
|
JayBhagavan  Пользователь Сообщений: 11833 ПОЛ: МУЖСКОЙ | Win10x64, MSO2019x64 |
Здравия. В файле покажите результат того, что Вы хотите получить, который Вы сделаете вручную. Пока мало понятно, что надо. <#0> |
|
kim  Пользователь Сообщений: 3139 Игорь |
YouRock
, скажите, а Вы названия месяцев в своей книге всегда будете писать на суахили? Или может быть применена другая экзотика? Почему бы даты сразу не писать как даты, применяя соответствующий формат ячейки? |
|
YouRock Пользователь Сообщений: 161 |
#4 28.06.2017 13:55:22
дело в том что я хочу иметь универсальный файл на все года,если даты пропишу как даты, то нужно будет каждый год менять формат . 50 times helped — they forgot, 1 time refused — they memorized |
||
|
YouRock Пользователь Сообщений: 161 |
#5 28.06.2017 14:02:54
я постарался выложить свою мысль,нужно среднее значение с ПН по ВС даже переходящие из месяца в месяц . если в месяце 30 дней, значит 31й день не должно брать, даже если он есть в табличке . Прикрепленные файлы
50 times helped — they forgot, 1 time refused — they memorized |
||
|
_Boroda_  Пользователь Сообщений: 1496 Контакты см. в профиле |
Если заполнить таблицу на 12 месяцев и написать номера недель без слова «неделя», то такой вариант Изменено: _Boroda_ — 28.06.2017 15:13:09 Скажи мне, кудесник, любимец ба’гов… |
|
Юрий М  Модератор Сообщений: 60570 Контакты см. в профиле |
#7 28.06.2017 15:17:20
А не четверг? ) |
||
|
_Boroda_ Пользователь Сообщений: 1496 Контакты см. в профиле |
#8 28.06.2017 15:42:05 По стандарту ISO 8601 конечно четверг, но у ТС в условии
поэтому я посчитал с первого понедельника Скажи мне, кудесник, любимец ба’гов… |
||
|
Сергей П  Пользователь Сообщений: 67 |
#9 28.06.2017 16:03:41 Если перевести таблицу в плоскую получится примерно так. Изменено: Сергей П — 28.06.2017 16:07:05 |
Содержание
- Стандартный способ вычисления
- Вычисление с помощью Мастера функций
- Панель формул
- Ручной ввод функции
- Расчет среднего значения по условию
- Вопросы и ответы
В процессе различных расчетов и работы с данными довольно часто требуется подсчитать их среднее значение. Оно рассчитывается путем сложения чисел и деления общей суммы на их количество. Давайте выясним, как вычислить среднее значение набора чисел при помощи программы Microsoft Excel различными способами.
Стандартный способ вычисления
Самый простой и известный способ найти среднее арифметическое набора чисел — это воспользоваться специальной кнопкой на ленте Microsoft Excel. Выделяем диапазон чисел, расположенных в столбце или в строке документа. Находясь во вкладке «Главная», жмем на кнопку «Автосумма», которая расположена на ленте в блоке инструментов «Редактирование». Из выпадающее списка выбираем пункт «Среднее».
После этого, с помощью функции «СРЗНАЧ», производится расчет. В ячейку под выделенным столбцом, или справа от выделенной строки, выводится средняя арифметическая данного набора чисел.
Этот способ хорош простотой и удобством. Но, у него имеются и существенные недостатки. С помощью этого способа можно произвести подсчет среднего значения только тех чисел, которые располагаются в ряд в одном столбце, или в одной строке. А вот, с массивом ячеек, или с разрозненными ячейками на листе, с помощью этого способа работать нельзя.
Например, если выделить два столбца, и вышеописанным способом вычислить среднее арифметическое, то ответ будет дан для каждого столбца в отдельности, а не для всего массива ячеек.
Вычисление с помощью Мастера функций
Для случаев, когда нужно подсчитать среднюю арифметическую массива ячеек, или разрозненных ячеек, можно использовать Мастер функций. Он применяет все ту же функцию «СРЗНАЧ», известную нам по первому методу вычисления, но делает это несколько другим способом.
Кликаем по ячейке, где хотим, чтобы выводился результат подсчета среднего значения. Жмем на кнопку «Вставить функцию», которая размещена слева от строки формул. Либо же, набираем на клавиатуре комбинацию Shift+F3.
Запускается Мастер функций. В списке представленных функций ищем «СРЗНАЧ». Выделяем его, и жмем на кнопку «OK».
Открывается окно аргументов данной функции. В поля «Число» вводятся аргументы функции. Это могут быть как обычные числа, так и адреса ячеек, где эти числа расположены. Если вам неудобно вводить адреса ячеек вручную, то следует нажать на кнопку расположенную справа от поля ввода данных.
После этого, окно аргументов функции свернется, а вы сможете выделить ту группу ячеек на листе, которую берете для расчета. Затем, опять нажимаете на кнопку слева от поля ввода данных, чтобы вернуться в окно аргументов функции.
Если вы хотите подсчитать среднее арифметическое между числами, находящимися в разрозненных группах ячеек, то те же самые действия, о которых говорилось выше, проделывайте в поле «Число 2». И так до тех пор, пока все нужные группы ячеек не будут выделены.
После этого, жмите на кнопку «OK».
Результат расчета среднего арифметического будет выделен в ту ячейку, которую вы выделили перед запуском Мастера функций.
Панель формул
Существует ещё третий способ запустить функцию «СРЗНАЧ». Для этого, переходим во вкладку «Формулы». Выделяем ячейку, в которой будет выводиться результат. После этого, в группе инструментов «Библиотека функций» на ленте жмем на кнопку «Другие функции». Появляется список, в котором нужно последовательно перейти по пунктам «Статистические» и «СРЗНАЧ».
Затем, запускается точно такое же окно аргументов функции, как и при использовании Мастера функций, работу в котором мы подробно описали выше.
Дальнейшие действия точно такие же.
Ручной ввод функции
Но, не забывайте, что всегда при желании можно ввести функцию «СРЗНАЧ» вручную. Она будет иметь следующий шаблон: «=СРЗНАЧ(адрес_диапазона_ячеек(число); адрес_диапазона_ячеек(число)).
Конечно, этот способ не такой удобный, как предыдущие, и требует держать в голове пользователя определенные формулы, но он более гибкий.
Расчет среднего значения по условию
Кроме обычного расчета среднего значения, имеется возможность подсчета среднего значения по условию. В этом случае, в расчет будут браться только те числа из выбранного диапазона, которые соответствуют определенному условию. Например, если эти числа больше или меньше конкретно установленного значения.
Для этих целей, используется функция «СРЗНАЧЕСЛИ». Как и функцию «СРЗНАЧ», запустить её можно через Мастер функций, из панели формул, или при помощи ручного ввода в ячейку. После того, как открылось окно аргументов функции, нужно ввести её параметры. В поле «Диапазон» вводим диапазон ячеек, значения которых будут участвовать в определении среднего арифметического числа. Делаем это тем же способом, как и с функцией «СРЗНАЧ».
А вот, в поле «Условие» мы должны указать конкретное значение, числа больше или меньше которого будут участвовать в расчете. Это можно сделать при помощи знаков сравнения. Например, мы взяли выражение «>=15000». То есть, для расчета будут браться только ячейки диапазона, в которых находятся числа большие или равные 15000. При необходимости, вместо конкретного числа, тут можно указать адрес ячейки, в которой расположено соответствующее число.
Поле «Диапазон усреднения» не обязательно для заполнения. Ввод в него данных является обязательным только при использовании ячеек с текстовым содержимым.
Когда все данные введены, жмем на кнопку «OK».
После этого, в предварительно выбранную ячейку выводится результат расчета среднего арифметического числа для выбранного диапазона, за исключением ячеек, данные которых не отвечают условиям.
Как видим, в программе Microsoft Excel существует целый ряд инструментов, с помощью которых можно рассчитать среднее значение выбранного ряда чисел. Более того, существует функция, которая автоматически отбирает числа из диапазона, не соответствующие заранее установленному пользователем критерию. Это делает вычисления в приложении Microsoft Excel ещё более удобными для пользователей.
|
Суммировать результаты каждой недели и вывести среднее знач. |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Среднедневная выручка, среднемесячная температура воздуха, средняя заработная плата… Вычисление среднего значения требуется во всех сферах, и самый быстрый способ — конечно, в excel.
- Как вычислить среднее арифметическое значение чисел
- Вычисление среднего значения для несвязанных диапазонов ячеек
- Особенность функции СРЗНАЧ
- Вычисление среднего значения с отрицательными числами
- Среднее значение по условию
- Среднее значение по нескольким условиям
Будет полезно перед прочтением:
Как вычислить среднее арифметическое значение чисел
В Excel вычисление среднего значения происходит при помощи функции СРЗНАЧ (англ. AVERAGE). Эта функция считает среднее арифметическое числовых значений.
Синтаксис функции:
=СРЗНАЧ (Число 1; Число 2;…;Число n)
Чаще всего в качестве аргументов функции СРЗНАЧ используется ссылка на диапазон ячеек. Диапазоном может быть строка, столбец или массив ячеек в Excel. Можно указать несколько не связанных между собой диапазонов. Также возможно указать ссылки на отдельные ячейки. В качестве аргументов могут выступать и просто числа, введенные с клавиатуры, но это практически не используется.
Давайте рассмотрим пример вычисления среднего арифметического значения в Excel.
В таблице представлены данные о дневном пробеге автомобиля за неделю. Нужно рассчитать среднее значение, т.е. среднедневной пробег.
Есть 3 способа воспользоваться функцией СРЗНАЧ:
Способ 1. При помощи встроенного инструмента. Ставим курсор под набором чисел, и переходим на вкладку Главная — блок Редактирование — нажимаем стрелочку возле значка Автосумма — Среднее.
На картинке видно, что Excel самостоятельно определил диапазон, который нужно усреднить. Он выделен бегущим выделением. Если этот диапазон выделен неверно, то, до нажатия Enter выделите правильный диапазон и только после этого нажмите Enter.
Способ 2. При помощи мастера функций.
Также выделите ячейку, где нужно получить среднее арифметическое. После нажмите кнопку Fx на панели инструментов (рядом со строкой формул), появится окно Вызов функции. Здесь в категории Статистические можно найти функцию СРЗНАЧ и вызвать мастер формул. И далее, установив курсор в поле Число 1, выделить диапазон ячеек и нажать Ок.
Способ 3. Выделить ячейку и ввести в строке формул:
=СРЗНАЧ(C2:C8)
Вычисление среднего значения для несвязанных диапазонов ячеек
На том же примере давайте рассчитаем среднедневной пробег для 25-26 окт, и для 30-31 окт (т.е. за 4 дня, идущие не подряд).
Сделаем это при помощи строки формул. Установим курсор в итоговую ячейку и введем в строке формул:
= СРЗНАЧ(
Далее выделим первый диапазон (цифры напротив дат 25-26 окт). Теперь нажмем клавишу Ctrl и, не отпуская ее, выделим второй диапазон. Закроем скобку и нажмем Enter. Результат на картинке.
Особенность функции СРЗНАЧ
Стоит учитывать, что функция СРЗНАЧ работает только с числовыми данными. Текстовые и пустые ячейки она просто игнорирует.
На картинке показан расчет среднего значения для трех чисел: 100, 200 и 300 — это число 200.
Сотрем значение 300. Среднее значение стало 150, потому что у нас осталось только два числа (100 и 200).
(100 + 200) / 2 = 150
А теперь вместо пустой ячейки укажем 0 (ноль). Среднее значение стало 100.
Почему так? Дело в том, что пустую ячейку функция СРЗНАЧ не считает нулем. Следовательно, она ее просто игнорирует и берет в расчет 2 числа (100 и 200). А ноль эта функция считает таким же числом, как 100 и 200, потому расчет идет исходя из трех чисел (100, 200, 0):
(100 + 200 + 0) / 3 = 100
Вычисление среднего значения с отрицательными числами
Иногда у пользователей Excel возникает вопрос: возможно ли посчитать среднее арифметическое число, если в диапазоне есть и положительные, и отрицательные значения? Конечно, можно!
Здесь работает обычное правило математики. Ведь что такое среднее — это сумма значений, деленная на их количество.
Пример:
Давайте поймем, как получился результат:
(5 + (-2) + 9 + (-8)) / 4 = (5 — 2 + 9 — 
= 4 / 4 = 1
Результат может быть и отрицательным, если сумма в скобках будет меньше нуля.
Среднее значение по условию
Для случаев, когда нужно усреднить не все значения, а только отвечающие условию, существует функция СРЗНАЧЕСЛИ (англ. AVERAGEIF). Условие может быть как числовое, так и текстовое.
Функция СРЗНАЧЕСЛИ имеет следующий синтаксис:
=СРЗНАЧЕСЛИ(Диапазон; Условие; [диапазон усреднения])
где Диапазон — это диапазон ячеек, которые должны соответствовать условию;
Условие — значение, которому должны соответствовать ячейки. Если условие включает в себя операторы сравнения, то их нужно указывать в кавычках, например, «<=30». Оператор “=” можно не указывают, в этом случае нужно написать просто число без кавычек.
[диапазон усреднения] — указывается, если он не совпадает с диапазоном условий, необязательный аргумент.
1) Среднее значение по числовому условию.
У нас есть таблица сотрудников, в которых указан их пол и возраст.
Нужно посчитать средний возраст сотрудников младшей возрастной группы (до 30 лет). В строке формул напишем:
=СРЗНАЧЕСЛИ(C2:C8;»<=30″)
Получим возраст 25,333.
В этом примере С2:С8 — это диапазон условия и одновременно диапазон усреднения. Поэтому третий аргумент функции не указываем.
2) Среднее значение по текстовому условию.
Посчитаем средний возраст женщин. Формула будет следующая:
=СРЗНАЧЕСЛИ(B2:B8;»Женский»;C2:C8)
В данном случае B2:B8 — диапазон условия, в котором excel ищет условие “Женский”, а C2:C8 — диапазон усреднения.
Обратите внимание, что эта функция не учитывает регистр букв. Если бы в этом примере мы написали “женский” с маленькой буквы, то формула бы так же сработала.
Также условие можно (и нужно!) указывать не в кавычках, а ссылкой на ячейку, в которую оно будет записано.
Среднее значение по нескольким условиям
Для этой цели используют функцию СРЗНАЧЕСЛИМН (англ. AVEGAREIFS).
Синтаксис функции:
=СРЗНАЧЕСЛИМН(Диапазон усреднения; Диапазон условия 1; Условие 1; Диапазон условия 2; Условие 2;…)
В отличие от функции СРЗНАЧЕСЛИ, у функции СРЗНАЧЕСЛИМН диапазон усреднения обязательно указывать всегда.
Рассмотрим пример. В таблице количество проданных товаров разными продавцами. Задача найти среднемесячное количество продаж Яблок у продавца Смирнов.
В строке формул напишем:
=СРЗНАЧЕСЛИМН(E2:E9;C2:C9;C12;D2:D9;D12)
На следующей картинке показан алгоритм работы формулы.
Диапазон усреднения — в данном примере это количество товара, ячейки E2:E9.
Диапазон условия 1 — наименование товара.
Условие 1 — ссылка на ячейку с наименованием товара.
Диапазон условия 2 — столбец с фамилиями продавцов.
Условие 2 — ссылка на ячейку с фамилией продавца.
В качестве условия можно прямо в формуле вместо ссылок указывать значение в кавычках (если текстовое) или просто значение (если это число).
Количество вложенных условий должно быть от 1 до 127.
Также полезно будет познакомиться со статьей:
Вам может быть интересно: