Расчет среднего значения в программе microsoft excel

Поиск среднего арифметического

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

Способ 1: Рассчитать среднее значение через «Мастер функций»

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

1. Выделить любую ячейку в таблице нажать кнопку «Вставить функция».

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

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

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

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

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

Способ 2: Автоматический подсчет результата в выделенных ячейках

В этом способе расчет среднего арифметического осуществляется буквально за пару кликов мышью. Очень удобно для любого количества чисел.

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

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

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

Способ 3: Использование панели формул

Еще один способ перейти в окно функции:

1. Выбрать вкладку «Формулы», навести курсор мыши на «Другие функции», выбрать «Статические» и «СРЗНАЧ».

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

Нажать «ОК» и увидеть результат в свободной ячейке.

Самый быстрый способ, при котором не нужно долго искать в меню необходимы пункты.

Способ 4: Ручной ввод

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

1. Найти строку для ввода разнообразных формул.

Ввести необходимые числа согласно шаблону: =СРЗНАЧ(адрес_диапазона_ячеек(число); адрес_диапазона_ячеек(число))

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

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

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

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

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

Потом появится еще одно окно, в котором можно осуществить ввод аргументов функции СРЗНАЧ.

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

4

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

Каждый документ содержит панель формул, которая меняется в зависимости от того, какую ячейку выбрать. Если формула там есть, то она там будет записана. Если формула отсутствует, то там тогда будет отображаться просто значение ячейки (например, если там записан просто текст). На этом скриншоте видно конкретный пример, как может использоваться строка ввода формул. С ее помощью можно посмотреть на то, какая формула кроется за определенным числом (13,2) на примере, а также отредактировать аргументы. Или вообще убрать старую формулу и ввести новую. Или убрать все формулы, а оставить пустое значение или число. Возможностей у нее много достаточно. Можно выбрать любую, которая поможет выполнить поставленную задачу.

Ручной ввод функций

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

Мы введем ее вручную.

=СРЗНАЧ(A1:B1;F1:H1)

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

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

Расчет среднего значения по условию

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

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

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

Конечная формула будет следующей:

 =СРЗНАЧЕСЛИ(A1:A8;”>=10″)

5

В результате, получится такое значение.

6

Разберем аргументы этой функции более подробно.

1. Диапазон. Это непосредственно тот диапазон, в котором будет содержаться набор критериев.
2. Условие. Это непосредственно условие. То есть, значение должно как-то соотноситься с критерием. В нашем случае оно должно быть больше или равно 10.
3. Диапазон усреднения. Необязательный аргумент, который используется если значения, для которых нужно искать среднее арифметическое, находятся в другом месте, а не непосредственно являются критериями. 

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

Если в массиве есть числа в текстовом формате

В случае, если число введено в ячейку с текстовым форматом (см. ячейку А6), то это значение воспринимается функцией СРЗНАЧ() как текст и игнорируется. Поэтому, не смотря на то, что в диапазоне A5:A15 11 значений, для функции СРЗНАЧ() их всего 10, т.е. n в этом случае =10. Среднее будет равно 20,4 (см. файл примера ).

Функция СРЗНАЧA() ведет себя по другому: числа в текстовом формате интерпретируются ею как 0, впрочем, как и все остальные текстовые значения, включая значение Пустой текст «». Поэтому, для функции СРЗНАЧА() n =11, а среднее будет равно 18,545454.

Чтобы числа в текстовом формате также учитывались при вычислении среднего, то нужно записать формулу массива =СРЗНАЧ(A5:A15+0) . В этом случае среднее будет равно 18,818181, т.к. n=11, а число в текстовом формате будет интерпретировано как 3.

Примечание: Чтобы перевести числа из текстового формата в числовой см. статью Преобразование ЧИСЕЛ из ТЕКСТового формата в ЧИСЛОвой (Часть 1. Преобразование формулами).

Примечание: о вычислении средневзвешенного значения см. статью Средневзвешенная цена в MS EXCEL

Как в эксель посчитать процент от суммы числа в столбце?

Можно сделать наглядную таблицу продаж, в которой будет отражен процент продаж каждого товара в общей доле. Делают такие таблицы по горизонтали, можно и по вертикали. Мне по душе вертикальные — это когда ИТОГИ расположены внизу таблицы. Нам требуется вычислить товары-лидеры продаж.

У нас есть список товаров, сумма продаж каждого за месяц и итоговая сумма всех продаж за месяц. Нужно разделить «сумму продаж за месяц» на «ИТОГО» и умножить на сто. Вводим формулу в графу лидеры продаж в первой строке:

У меня графа «Лидеры продаж» в процентном формате и ее итоговая графа то же. Учитываю этот момент

Обратите внимание! В формуле перед итоговой ячейкой ставим знак $ и перед номером строки ставим $ — это будет абсолютный адрес (клавиша Shift +4)

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

Протягиваем формулу мышью по графе вниз до «итогов» и получаем готовую таблицу если все сделали правильно:

Теперь с ней можно работать дальше — отсортировать результаты по убыванию, использовать ее данные в формулах при разработке следующих таблиц.

Как найти среднее арифметическое чисел?

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

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

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

СРЗНАЧ проигнорирует эти значения и будет считать среднее только по числовым значениям. И это уже может быть некорректным. В таких случаях можно либо заменить текст нулями или использовать другие функции. Функция среднего значения, которая учитывает логические значения и текст, называется СРЗНАЧА. В попытке выяснить, кто из менеджеров лучше управляет запасами, вы решили проанализировать стоки последних шести недель.

На первый взгляд среднее значение стока показывает, что оба менеджера работают одинаково. В нашем примере, мы воспользовались функцией Excel СТАНДОТКЛОН, чтобы рассчитать показатель стандартного отклонения вместе со средним.

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

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

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

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

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

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

Проценты в Экселе (Excel)

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

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

Как рассчитать среднее значение между двумя датами в Excel?

В 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 : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 30 дней.

Перейти к загрузкеБесплатная пробная версия 30 днейпокупкаPayPal / MyCommerce

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

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

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

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

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

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

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

Чтобы ввести в ячейку дату – не нужно знать её значение в числовом формате. Просто запишите дату в таком виде, как это принято в вашем регионе. Например, я пользуюсь такой записью: 01.05.2016. Программа автоматически вычисляет число даты и применяет к ней формат «Дата». Но если вы использовали комбинацию, которую Эксель не распознаёт – он сохранит данные, как текст. Тогда вы не сможете использовать функции обработки дат и выполнять математические операции.

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

• 05.2016
• 5.16
• 01/05/2016
• 05/2016
• Какой-то другой, экспериментируйте

И всё же, дата – это не совсем обычное число. Когда вы форматируете числовую ячейку, на листе содержимое отображается с заданным форматом, а в строке формул – в своём исходном виде. Если же вы форматируете дату, в строке формул будет не соответствующее ей число, а дата в «Кратком формате».

Дата в строке формул Эксель

Способ с помощью Мастера функций

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

1. Нажав левую кнопку мыши, выделите ячейку, в которой хотите видеть результат вычислений.
2. Откройте окно Мастера функций, нажав по кнопке «Вставить функцию», расположенной слева от строки формул либо использовав горячие клавиши Shift+F3.
3. В появившемся окне отыщите в списке строку «СРЗНАЧ», выделите ее и нажмите кнопку «ОК».
4. Появится новое окно для ввода аргументов функции. В нем вы увидите два поля: «Число1» и «Число2».
5. В первое поле введите адреса ячеек, в которых расположены числовые значения для расчета. Сделать это можно как вручную, так и с помощью специального инструмента. Во втором случае нажмите по кнопке, расположенной в правой части поля для ввода. Окно Мастера свернется и вам необходимо будет выделить мышкой ячейки для расчета.
6. Если другой диапазон ячеек с данными находится в другом месте листа, тогда укажите его в поле «Число2».
7. Проделайте ввод данных, пока не укажете все необходимые.
8. Нажмите кнопку «ОК».

По завершении ввода окно Мастера закроется, а в ячейке, которую вы выделяли в самом начале, появится результат вычислений. Теперь вы знаете второй способ, как рассчитать среднее арифметическое в Excel. Но далеко не последний, поэтому двигаемся дальше.

Основная идея

Предположим, что мы с вами сидим в приемно-экзаменационной комиссии и оцениваем абитуриентов, которые хотят поступить в наш ВУЗ. Оценки по различным предметам у наших кандидатов следующие:

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

Первое, что обычно приходит в голову – это рассчитать классический средний балл с помощью стандартной функции Excel СРЗНАЧ

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

Возникает вопрос: а как присвоить нашим предметам различную важность (ценность), чтобы учитывать ее при расчете среднего? И вот тут на помощь приходит средневзвешенное значение

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

В бизнесе средневзвешенное часто используется в таких задачах, как:

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

Расчет средневзвешенного формулами

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

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

Функция СУММПРОИЗВ (SUMPRODUCT) попарно перемножает друг на друга ячейки в двух указанных диапазонах – оценки абитурента и вес каждого предмета – а затем суммирует все полученные произведения

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

Как вычислять среднее значение ряда чисел

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

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

• ​ значение, например ячейку​

  ​ на листе​ Не выделяйте заголовки строк​ среднего качества всех​, чтобы открыть панель​ по A7 (значения​ вас уделить пару​ некоторых ситуациях ручной​ специального инструмента. Во​В нем вам необходимо​=СУММПРОИЗВ(A2:A4;B2:B4)/СУММ(B2:B4)​

• ​ они могут быть​

  ​ оперативнее обеспечивать вас​ строки? Чтобы ее​ х.​ с оценкой качества​СУММПРОИЗВ​ A8, которая находится​Выберите ячейку, в которой​ или столбцов (1, 2, 3… A,​ изделий, у которых​Построитель формул​ в столбце «Качество​

• ​ секунд и сообщить,​

  ​ ее ввод во​ втором случае нажмите​ кликнуть по пункту​Деление общей стоимости всех​ разными.​ актуальными справочными материалами​

​ можно было скопировать-вставить​Если значения расположены в​ выше 5″.​.​ слева ячейки с​ должно отображаться среднее​ B, C…) при​ оценка качества выше​

​.​

1. ​ изделия»).​ помогла ли она​ много раз ускорит​ по кнопке, расположенной​
2. ​ «Среднее».​​ трех покупок на​

   ​Выполните следующие действия.​ на вашем языке.​​ в нужную ячейку​ ​ диапазоне​На вкладке​​В разделе​​ текстом «Среднее качество​​ значение, например ячейку​ ​ копировании данных примера​ 5.​

Вычисление среднего значения ячеек, расположенных вразброс

​В списке​На вкладке​​ вам, с помощью​

​ процесс расчета.​ в правой части​Как только вы это​

​ общее количество приобретенных​	​Щелкните ячейку ниже или​
​ Эта страница переведена​	​ на другой строке?​A5:A15​
​Формулы​	​Аргументы​ изделия» в примере​ A8, которая находится​
​ на пустой лист.​	​Выделите ячейку A10, расположенную​построителя формул​Формулы​ кнопок внизу страницы.​Чтобы разобраться во всех​

Вычисление среднего взвешенного значения

​ поля для ввода.​ сделаете, в ячейке​​ единиц товара (24,66)​

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

​щелкните стрелку рядом​ Для удобства также​ нюансах, необходимо посмотреть​

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

Формула в эксель среднее арифметическое

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

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

Формулы для средневзвешенного значения в Excel

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

Пример 1. Функция СУММ.

Если у вас есть базовые знания о ней , приведенная ниже формула вряд ли потребует какого-либо объяснения:

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

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

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

В этом случае вам лучше использовать функцию СУММПРОИЗВ (SUMPRODUCT в английской версии). Об этом – ниже.

Пример 2. Функция СУММПРОИЗВ

Она идеально подходит для нашей задачи, так как предназначена для сложения произведений чисел. А это именно то, что нам нужно. 

Таким образом, вместо умножения каждого числа на показатель его значимости по отдельности, вы предоставляете два массива в формуле СУММПРОИЗВ (в этом контексте массив представляет собой непрерывный диапазон ячеек), а затем делите результат на итог сложения весов:

Предполагая, что величины для усреднения находятся в ячейках B2: B6, а показатели значимости — в ячейках C2: C6, наша формула будет такой:

Итак, формула умножает 1- е число в массиве 1 на 1- е  в массиве 2 (в данном примере 91 * 0,1), а затем перемножает 2- е число в массиве 1 на 2- е  в массиве 2 (85 * 0,15). в этом примере) и так далее. Когда все умножения выполнены, Эксель складывает произведения. Затем делим полученное на итог весов.

Чтобы убедиться, что функция СУММПРОИЗВ дает правильный результат, сравните ее с формулой СУММ из предыдущего примера, и вы увидите, что числа идентичны.

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

Это формула массива, не забудьте, что вводить ее нужно при помощи комбинации клавиш ++.

Но при использовании функции СУММ или СУММПРОИЗВ веса совершенно не обязательно должны составлять 100%. Однако, они также не должны быть обязательно выражены в процентах. 

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

Видите, в этом случае мы обошлись без процентов.

Пример 3. Средневзвешенная цена.

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

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

Ну, это все о формуле средневзвешенного значения в Excel. 

Рекомендуем также:

Определение среднего значения по условию

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

Допустим, нам нужно посчитать среднее значение только по положительным числам, т.е. тем, которые больше нуля. В этом случае, нас выручит функция СРЗНАЧЕСЛИ.

1. Встаем в результирующую ячейку и жмем кнопку “Вставить функцию” (fx) слева от строки формул.
2. В Мастере функций выбираем категорию “Статистические”, кликаем по оператору “СРЗНАЧЕСЛИ” и жмем ОК.
3. Откроются аргументы функции, после заполнения которых кликаем OK:
   • в значении аргумента “Диапазон” указываем (вручную или выделив с помощью левой кнопки мыши в самой таблице) требуемую область ячеек;
4. в значении аргумента “Условие”, соответственно, задаем наше условие попадания ячеек из отмеченного диапазона в общий расчет. В нашем случае, это выражение “>0”. Вместо конкретного числа, в случае необходимости, в условии можно указать адрес ячейки, содержащей числовое значение.
5. поле аргумента “Диапазон_усреднения” можно оставить пустим, так как его обязательное заполнение требуется только при работе с текстовыми данными.
6. Среднее значение с учетом заданного нами условия отбора ячеек отобразилось в выдранной ячейке.

Дата в Excel: всё проще, чем кажется

Многие считают, что дата в ячейке – это особенный тип данных, с которым тяжело обходиться. Это не так. Дата в Майкрософт Эксель – это обычное число, представленное в формате «Дата». Это порядковое число, обозначающее, сколько прошло дней от 0 января 1900 года. Например, если записать в ячейке число 30, и выбрать ему формат дата, получится число 30.01.1900.

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

Прибавление числа к Дате в Эксель

Есть небольшие ограничения – Excel работает с датами в диапазоне с 0 января 1900 года по 31 декабря 9999. Даты вне этого диапазона Excel записывает, как текст. Существенное ограничение? Мне так не кажется. В своей практике я не работал с датами, выходящими за эти пределы. Но если это необходимо, можно обрабатывать более ранние и поздние даты с помощью текстовых функций. Например, разделить такую дату на год, месяц и день с помощью функций извлечения части текста. Потом эти данные можно обрабатывать раздельно.

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

Среднее квадратическое отклонение: формула в Excel

Как нетрудно догадаться, формула СРЗНАЧ умеет считать только среднюю арифметическую простую, то есть все складывает и делит на количество слагаемых (за вычетом количества пустых ячеек).

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

Вообще, одни и те же задачи в Экселе можно решать разными способами, что делает табличный процессор очень гибким и практичным. Для этого есть готовая формула СРЗНАЧЕСЛИ. Есть и такая возможность – функция ПРОМЕЖУТОЧНЫЕ ИТОГИ. В параметре выбора формулы следует поставить 1 (а не 9, как в случае с суммированием).

Однако то, что описано выше встречается в 90% случаев и вполне достаточно для успешного применения. Среднее арифметическое в excel. Таблицы Excel, как нельзя лучше подходят для всяких вычислений. Мы даже не задумываемся, какой мощный инструмент находится на наших компьютерах, а значит, и не используем его в полную силу. Многие родители думают, что компьютер – это просто дорогая игрушка.