Работа с весами в excel

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

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

Расчет простого среднего

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

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

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

Средневзвешенное — это среднее с учетом различной ценности (веса, важности) каждого из элементов.

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

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

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

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

Расчет средневзвешенного значения

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

Так что берем Машу, а Иван пусть поступает в институт физкультуры ;)

Расчет средневзвешенного в сводной таблице

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

Умная таблица с данными

Обратите внимание, что я преобразовал ее в «умную» таблицу с помощью команды Главная — Форматировать как таблицу (Home — Format as Table) и дал ей на вкладке Конструктор (Design) имя Data.

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

=(691*10 + 632*12 + 957*26)/(10+12+26) = 820,33

То есть мы суммируем стоимости всех сделок (цена каждой сделки умножается на количество по сделке) и потом делим получившееся число на общее количество этого товара.

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

Выбор типа функции для расчета поля

В этом списке есть среднее, но нет средневзвешенного :(

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

Таблица данных со вспомогательным столбцом

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

Сводная с данными для деления

Останется поделить одно на другое, но сделать это, вроде бы, простое математическое действие внутри сводной не так просто. Придется либо добавлять в сводную вычисляемое поле (вкладка Анализ — Поля, элементы, наборы — Вычисляемое поле), либо считать обычной формулой в соседних ячейках или привлекать функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GET.PIVOT.DATA), о которой я уже писал. А если завтра изменятся размеры сводной (ассортимент товаров), то все эти формулы придется вручную корректировать.

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

Расчет средневзвешенного в сводной таблице с помощью Power Pivot и языка DAX

Если у вас Excel 2013-2016, то в него встроен супермощный инструмент для анализа данных — надстройка Power Pivot, по сравнению с которой сводные таблицы с их возможностями — как счеты против калькулятора. Если у вас Excel 2010, то эту надстройку можно совершенно бесплатно скачать с сайта Microsoft и тоже себе установить. С помощью Power Pivot расчет средневзвешенного (и других невозможных в обычных сводных штук) очень сильно упрощается.

1. Для начала, загрузим нашу таблицу в Power Pivot. Это можно сделать на вкладке Power Pivot кнопкой Добавить в модель данных (Add to Data Model). Откроется окно Power Pivot и в нем появится наша таблица.

2. Затем щелкните мышью в строку формул и введите туда формулу для расчета средневзвешенного:

Расчет средневзвешенного на DAX в Power Pivot

Несколько нюансов по формуле:

  • В Power Pivot есть свой встроенный язык с набором функций, инструментов и определенным синтаксисом, который называется DAX. Так что можно сказать, что эта формула — на языке DAX.
  • Здесь WA — это название вычисляемого поля (в Power Pivot они еще называются меры), которое вы придумываете сами (я называл WA, имея ввиду Weighted Average — «средневзвешенное» по-английски).
  • Обратите внимание, что после WA идет не равно, как в обычном Excel, а двоеточие и равно.
  • При вводе формулы будут выпадать подсказки — используйте их.
  • После завершения ввода формулы нужно нажать Enter, как и в обычном Excel.

3. Теперь строим сводную. Для этого в окне Power Pivot выберите на вкладке Главная — Сводная таблица (Home — Pivot Table). Вы автоматически вернетесь в окно Excel и увидите привычный интерфейс построения сводной таблицы и список полей на панели справа. Осталось закинуть поле Наименование в область строк, а нашу созданную формулой меру WA в область значений — и задача решена:

Сводная с вычисленной мерой

Вот так — красиво и изящно.

Общая мораль: если вы много и часто работаете со сводными таблицами и вам их возможности «тесноваты» — копайте в сторону Power Pivot и DAX — и будет вам счастье!

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

  • Что умеет функция СУММПРОИЗВ на самом деле
  • Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
  • Настройка вычислений в сводных таблицах
Источник

What to Know

  • Syntax: =SUMPRODUCT(Array1,Array2,Array3,…Array255).
  • In a weighted average calculation, some numbers in the range are worth more than others.
  • The SUMPRODUCT weighting formula is a non-standard formula that must be typed in a cell to work.

This article explains how to use the SUMPRODUCT function to calculate a weighted average in Microsoft Excel formulas.

Enter the Weighting Formula

Like most other functions in Excel, SUMPRODUCT can be entered into a worksheet using the Functions Library in the Formulas tab. Because the weighting formula in this example uses SUMPRODUCT in a non-standard way (the function’s result is divided by the weight factor), the weighting formula must be typed into a worksheet cell.

The example shown in this article calculates the weighted average for a student’s final mark using the SUMPRODUCT function.

The function accomplishes this by:

  • Multiplying the various marks by their individual weight factor.
  • Adding the products of these multiplication operations together.
  • Dividing the above sum by the total of the weighting factor 7 (1+1+2+3) for the four assessments.

To enter the SUMPRODUCT formula to compute a weighted average, open a blank worksheet, enter the data in rows 1 through 6 from the image above, and follow these steps:

  1. Select cell C7 to make it the active cell (this is the location where the student’s final mark will display).

  2. Type the formula =SUMPRODUCT(B3:B6,C3:C6)/(1+1+2+3) into the cell. The formula appears in the Formula Bar.

  3. Press the Enter key on the keyboard.

  4. The answer 78.6 appears in cell C7 (your answer may have more decimal places).

The unweighted average for the same four marks would be 76.5. Because the student had better results for his midterm and final exams, weighing the average helped improve the overall grade.

How the SUMPRODUCT Function Works

Typically, when you calculate an average or arithmetic mean, each number has equal value or weight. The average is calculated by adding a range of numbers together and then dividing this total by the number of values in the range. A weighted average, on the other hand, considers one or more numbers in the range to be worth more or have a greater weight than the other numbers.

SUMPRODUCT multiplies the elements of two or more arrays and then adds the products to calculate weighted averages. For example, certain marks in school, such as midterm and final exams, are usually worth more than regular tests or assignments. If averaging is used to calculate a student’s final mark, the midterms and final exams would be given greater weights.

In a situation in which two arrays with four elements each are entered as arguments for the SUMPRODUCT function:

  • The first element of array1 is multiplied by the first element in array2.
  • The second element of array1 is multiplied by the second element of array2.
  • The third element of array1 is multiplied by the third element of array2.
  • The fourth element of array1 is multiplied by the fourth element of array2.

Next, the products of the four multiplication operations are summed and returned by the function as the result.

SUMPRODUCT Syntax and Arguments

A function’s syntax refers to the layout of the function and includes the function’s name, brackets, and arguments. The syntax for the SUMPRODUCT function is: 

=SUMPRODUCT(Array1,Array2,Array3,...Array255)

The arguments for the SUMPRODUCT function are:

  • Array1: The first array argument (required).
  • Array2,Array3,…Array255: Additional (optional) arrays, up to 255. With two or more arrays, the function multiplies the elements of each array together and then adds the results.

The array elements can be cell references to the location of the data in the worksheet or numbers separated by arithmetic operators, such as the plus (+) or minus (-) signs. If you enter numbers that are not separated by operators, Excel treats them as text data.

Array arguments must have the same number of elements in each array. If not, SUMPRODUCT returns the #VALUE! error value. If any array elements are not numbers, such as text data, SUMPRODUCT treats them as zeros.

SUMPRODUCT Formula Variations

To emphasize that the results of the SUMPRODUCT function are divided by the sum of the weights for each assessment group, the divisor (the part doing the dividing) is entered as: 

(1+1+2+3)

The overall weighting formula could be simplified by entering the number 7 (the sum of the weights) as the divisor. The formula would then be: 

=SUMPRODUCT(B3:B6,C3:C6)/7

This choice is fine if the number of elements in the weighting array is small and they can easily be added together, but it becomes less effective as the number of elements in the weighting array increases, making their addition more difficult.

Another option, and probably the best choice, given that it uses cell references rather than numbers in totaling the divisor, would be to use the SUM function to total the divisor. The formula is then: 

=SUMPRODUCT(B3:B6,C3:C6)/SUM(B3:B6)

It is usually best to enter cell references rather than actual numbers into formulas. This simplifies updating them if the formula’s data changes.

For example, if the weighting factors for Assignments is changed to 0.5 in the example and Tests is changed to 1.5, the first two forms of the formula need to be edited manually to correct the divisor.

In the third variation, only the data in cells B3 and B4 need to be updated, and the formula recalculates the result.

Thanks for letting us know!

Get the Latest Tech News Delivered Every Day

Subscribe

Источник

Microsoft Excel

трюки • приёмы • решения

Как в Excel рассчитать средневзвешенное значение

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

На рис. 96.1 показан обычный лист, который содержит цены на бензин в течение 30 дней. Например, цена составляла $2,48 первые пять дней. Затем она снизилась до $2,41 в течение двух дней. Далее цена еще снизилась до $2,39 в течение трех дней и т. д.

Рис. 96.1. Формула в ячейке В16 вычисляет средневзвешенную цену на бензин

Ячейка В15 содержит формулу, которая использует функцию СРЗНАЧ: =СРЗНАЧ(B4:B13) . Если вы проанализируете ее, то увидите, что формула не возвращает точный результат. Скорее, цены должны быть взвешены по фактору количества дней, в течение которых каждая цена была в силе. Другими словами, средневзвешенное является соответствующим типом расчета. Следующая формула, указанная в ячейке В16 , делает то, что нужно: =СУММПРОИЗВ(B4:B13;C4:C13)/СУММ(C4:C13) .

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

Как в excel рассчитать средневзвешенное значение

Вычисляем средневзвешенные значения при помощи СУММПРОИЗВ

​Смотрите также​ те ячейки, из​ быть поставлены определенные​ с набором функций,​ легко переключить поле​​ у каждой из​​выберите пункт​Выделите ячейку A9, расположенную​ располагаются построчно, выделите​Выделите приведенный ниже образец​.​ должно отображаться среднее​ чисел; ряд весов)/СУММ​ что угодно, что​ В данном случае​​(SUMPRODUCT), то увидели,​​ ячеек​

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

Подготавливаем таблицу

​ нужную нам функцию,​оценка прогресса по проекту,​.​ с текстом «Средняя​ в конце строки​Примечание:​построителя формул​ A8, которая находится​Автор: Дарья Осиповская​ и повлиять на​ равна 30.​ деле она считает​, в котором содержатся​ в очень простую​

​ среднего значения.​ чисел в Excel​ DAX. Так что​ щелкнув по нему​ когда у задач​В списке​ цена изделия».​ и щелкните стрелку​ Не выделяйте заголовки строк​дважды щелкните функцию​ слева ячейки с​Примечание:​ конечный результат.​Часто в рамках школьного​ вот что:​ веса. Между этими​ задачу – просто​

Вводим формулу

​В результате вычисления функции​ считают с помощью​ можно сказать, что​​ правой кнопкой мыши​​ не равный вес​построителя формул​На вкладке​ рядом с кнопкой​ или столбцов (1, 2, 3. A,​СУММ​

​ текстом «Среднее качество​Мы стараемся как​​Средневзвешенное значение соотносится со​​ курса изучают и​=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)​ аргументами должен стоять​ используйте функцию​

​ получаем следующее значение:​
​ статистических функций. Можно​

​ эта формула -​​ и выбрав команду​​ и важность​дважды щелкните функцию​Формулы​Автосумма​ B, C. ) при​.​​ изделия» в примере​​ можно оперативнее обеспечивать​

​ средним арифметическим, рассмотренным​
​ среднее геометрическое. Расчет​

​К счастью, нам не​ разделитель точка с​​СРЗНАЧ​​Внимание! Для текстового критерия​ также вручную ввести​ на языке DAX.​Итоги по (Summarize Values​оценка персонала по набору​СРЗНАЧЕСЛИ​в разделе​

​.​
​ копировании данных примера​

​Выделите диапазон в поле​ данных.​ вас актуальными справочными​ в начале статьи.​​ данного значения базируется​​ нужно писать такую​ запятой (запятая). Когда​(AVERAGE). Но что​ (условия) диапазон усреднения​

​ собственную формулу. Рассмотрим​Здесь WA — это​ By)​​ навыков (компетенций) с​​.​Функция​​Существует два способа расчета​​ на пустой лист.​

​число1​
​На вкладке​

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

​:​
​ разной значимостью для​

​Совет:​​выберите пункт​​ среднего для ячеек,​Выбор примеров данных в​, нажмите кнопку DELETE​Формулы​​ языке. Эта страница​​ как уже было​

Как это работает

​ n-ной степени из​СУММПРОИЗВ​ закрываем скобки:​​ значения имеют больший​​Как посчитать средний процент​Чтобы найти среднее арифметическое,​ (в Power Pivot​​В этом списке есть​​ требуемой должности​ Чтобы быстро найти функцию,​Построитель формул​ которые не следуют​ справке​ и выделите на​щелкните стрелку рядом​ переведена автоматически, поэтому​ сказано, учитывает также​ произведения n-членов. Если​(SUMPRODUCT) делает всё​​=СУММПРОИЗВ(B2:B9;C2:C9)​​ вес, чем другие?​ в Excel? Для​​ необходимо сложить все​​ они еще называются​ среднее, но нет​

​и т.д.​ начните вводить ее​.​ одна за другой.​Качество изделия​ листе ячейки с​ с кнопкой​ ее текст может​ вес каждого числа,​​ брать те же​​ это автоматически.​=SUMPRODUCT(B2:B9,C2:C9)​​ Например, на многих​​ этой цели подойдут​ числа в наборе​​меры​​ средневзвешенного :(​Добавим к нашей таблице​

​ имя в поле​В списке​ Первый способ позволяет​Цена за единицу​ C2 по C7​Автосумма​

​ содержать неточности и​ использованного в расчетах.​ числа: 27, 22,​​Сама по себе функция​​Теперь добавим вторую часть​ курсах тесты имеют​ функции СУММПРОИЗВ и​ и разделить сумму​

​), которое вы придумываете​

​Можно частично решить проблему,​ еще один столбец,​Поиск функции​​построителя формул​​ быстро узнать среднее​Количество заказанных изделий​

​ (значения в столбце​​, выберите пункт​​ грамматические ошибки. Для​ Помимо этого существуют​​ 34 и 37,​​СУММПРОИЗВ​ нашей формулы, которая​ больший вес, чем​​ СУММ. Таблица для​​ на количество. Например,​​ сами (я называл​​ если добавить в​ где укажем некие​. Например, начните вводить​​дважды щелкните функцию​​ значение, не вводя​

​10​ «Количество изделий»).​Среднее​ нас важно, чтобы​ также средневзвешенное геометрическое​ то результат вычислений​(SUMPRODUCT) возвращает нам​ поделит результат вычисляемый​ задания. Для таких​ примера:​ оценки школьника по​ WA, имея ввиду​ исходную таблицу вспомогательный​ безразмерные баллы важности​СРЗНАЧЕСЛИ​СУММПРОИЗВ​ формулу. Второй способ​35​Теперь в строке формул​и нажмите RETURN.​​ эта статья была​​ и гармоническое значения.​​ будет равен 29,4.​​ огромное число –​​ функцией​​ случаев необходимо рассчитывать​​Как мы узнали средневзвешенную​​ информатике: 3, 4,​ Weighted Average -​ столбец, где будет​

​ каждого предмета по​.​
​.​
​ предполагает использование функции​

Средневзвешенное значение — что это и как его вычислить?

​ должна содержаться следующая​Щелкните ячейку, которая содержит​ вам полезна. Просим​Имеется еще одна интересная​Среднее гармоническое в общеобразовательной​10450​СУММПРОИЗВ​среднее взвешенное​ цену?​ 3, 5, 5.​ «средневзвешенное» по-английски).​ считаться стоимость каждой​

Средние величины: смысл и различия

​ шкале, например, от​В разделе​Совет:​ СРЗНАЧ для расчета​7​ формула: =СУММПРОИЗВ(B2:B7;C2:C7)/СУММ(C2:C7).​ только что найденное​ вас уделить пару​ разновидность, используемая в​ школе обычно не​. В этот момент​(SUMPRODUCT) на сумму​

​.​Формула: =СУММПРОИЗВ(C2:C12;B2:B12)/СУММ(C2:C12).​ Что выходит за​Обратите внимание, что после​ сделки:​ 0 до 9​Аргументы​ Чтобы быстро найти функцию,​ среднего значения и​25​Нажмите клавишу RETURN.​ среднее значение (ячейка​ секунд и сообщить,​ рядах чисел. Речь​ является предметом изучения.​ включается вторая часть​ весов. Позже мы​В Excel нет функции​С помощью формулы СУММПРОИЗВ​

​ четверть: 4. Мы​ WA идет не​Теперь можно рядом закинуть​ при поступлении на​щелкните поле рядом​ начните вводить ее​ позволяет вывести его​750​В этой формуле общая​ A8 в этом​ помогла ли она​ идет о взвешенном​

​ Тем не менее​ формулы:​ обсудим, почему это​ для расчёта средневзвешенного​ мы узнаем общую​ нашли среднее арифметическое​ равно, как в​ в область значений​ наш факультет программирования.​ с надписью​ имя в поле​​ на листе.​​9​​ стоимость всех заказов​​ примере).​​ вам, с помощью​​ скользящем среднем значении.​ оно используется довольно​/СУММ(C2:C9)​ важно.​ значения, зато есть​

Средневзвешенное значение: особенности

​ выручку после реализации​ по формуле: =(3+4+3+5+5)/5.​ обычном Excel, а​ стоимость и количество​ Затем расчитаем средневзвешенный​диапазон​Поиск функции​Быстрый расчет среднего​30​ делится на общее​В строке формул отобразится​ кнопок внизу страницы.​ Именно на его​ часто. Эта величина​или​Чтобы выполнить операцию деления,​ функция, которая сделает​ всего количества товара.​

​Как это быстро сделать​ двоеточие и равно.​ — и мы​ бал для каждого​и выделите на​. Например, начните вводить​Выделите ячейки, для которых​200​ количество заказанных изделий,​ формула, такая как​ Для удобства также​ основе рассчитываются тренды.​ обратна среднему арифметическому​/SUM(C2:C9)​ продолжаем уже введённую​ за Вас большую​ А функция СУММ​ с помощью функций​При вводе формулы будут​ получим почти то,​ абитурента, т.е. среднее​ листе ячейки с​СУММПРОИЗВ​ вы хотите найти​10​ в результате чего​=СРЗНАЧ(A2:A7)​ приводим ссылку на​ Помимо самих значений​ и рассчитывается как​, которая возвращает результат​ формулу символом​ часть работы:​ — сумирует количесвто​ Excel? Возьмем для​ выпадать подсказки -​ что требуется:​ с учетом веса​ A2 по A7​.​ среднее значение. Например,​

​40​ получается средневзвешенная стоимость​.​ оригинал (на английском​ и их веса​ частное от n​ в нормальный диапазон​/​СУММПРОИЗВ​ товара. Поделив общую​ примера ряд случайных​

Разновидности

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

​ — количества значений​ оценок, давая ответ​(прямой слеш), а​(SUMPRODUCT). И даже,​ выручку от реализации​ чисел в строке:​После завершения ввода формулы​ другое, но сделать​ нам формула будет​ «Цена за единицу»).​Аргументы​ A4 и A7.​8​ 29,38297872.​строке формул​В Excel существует несколько​

​ периодичность. И при​ и суммы 1/a​83,6​ далее записываем функцию​ если Вы никогда​ товара на общее​

Способы расчета

​Ставим курсор в ячейку​ нужно нажать Enter,​ это, вроде бы,​ выглядеть так:​В разделе​щелкните поле рядом​Совет:​27​Вы можете создать формулу,​

​выделите содержимое между​ способов найти среднее​ вычислении среднего значения​

Средневзвешенная цена в MS EXCEL

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

Пусть дана таблица продаж партий одного товара (см. файл примера ). В каждой партии указано количество проданного товара (столбец А) и его цена (столбец В).

Найдем средневзвешенную цену. В отличие от средней цены, вычисляемой по формуле СРЗНАЧ(B2:B8) , в средневзвешенной учитываются «вес» каждой цены (в нашем случае в качестве веса выступают значения из столбца Количество). Т.е. если продали одну крупную партию товара по очень низкой цене (строка 2), а другие небольшие партии по высокой, то не смотря, что средняя цена будет высокой, средневзвешенная цена будет смещена в сторону низкой цены.

Средневзвешенная цена вычисляется по формуле.
=СУММПРОИЗВ(B2:B8;A2:A8)/СУММ(A2:A8)

Если в столбце «весов» (А) будут содержаться одинаковые значения, то средняя и средневзвешенная цены совпадут.

Средневзвешенная цена с условием

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

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

Формула для вычисления средневзвешенной цены для Поставщика1:

К аргументам функции СУММПРОИЗВ() добавился 3-й аргумент: —($B$7:$B$13=B17)

Если выделить это выражение и нажать F9, то получим массив <1:1:1:1:0:0:0>. Т.е. значение 1 будет только в строках, у которых в столбце поставщик указан Поставщик1. Теперь сумма произведений не будет учитывать цены от другого поставщика, т.к. будут умножены на 0. Сумма весов для Поставщика1 вычисляется по формуле СУММЕСЛИ($B$7:$B$13;B17;$D$7) .

Решение приведено в файле примера на листе Пример2 .

Расчет средневзвешенного значения в Excel

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

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

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

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

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

Средневзвешенное — это среднее с учетом различной ценности (веса, важности) каждого из элементов.

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

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

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

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

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

Так что берем Машу, а Иван пусть поступает в институт физкультуры 😉

Расчет средневзвешенного в сводной таблице

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

Обратите внимание, что я преобразовал ее в «умную» таблицу с помощью команды Главная — Форматировать как таблицу (Home — Format as Table) и дал ей на вкладке Конструктор (Design) имя Data.

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

=(691*10 + 632*12 + 957*26)/(10+12+26) = 820,33

То есть мы суммируем стоимости всех сделок (цена каждой сделки умножается на количество по сделке) и потом делим получившееся число на общее количество этого товара.

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

В этом списке есть среднее, но нет средневзвешенного 🙁

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

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

Останется поделить одно на другое, но сделать это, вроде бы, простое математическое действие внутри сводной не так просто. Придется либо добавлять в сводную вычисляемое поле (вкладка Анализ — Поля, элементы, наборы — Вычисляемое поле), либо считать обычной формулой в соседних ячейках или привлекать функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GET.PIVOT.DATA) , о которой я уже писал. А если завтра изменятся размеры сводной (ассортимент товаров), то все эти формулы придется вручную корректировать.

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

Расчет средневзвешенного в сводной таблице с помощью Power Pivot и языка DAX

Если у вас Excel 2013-2016, то в него встроен супермощный инструмент для анализа данных — надстройка Power Pivot, по сравнению с которой сводные таблицы с их возможностями — как счеты против калькулятора. Если у вас Excel 2010, то эту надстройку можно совершенно бесплатно скачать с сайта Microsoft и тоже себе установить. С помощью Power Pivot расчет средневзвешенного (и других невозможных в обычных сводных штук) очень сильно упрощается.

1. Для начала, загрузим нашу таблицу в Power Pivot. Это можно сделать на вкладке Power Pivot кнопкой Добавить в модель данных (Add to Data Model) . Откроется окно Power Pivot и в нем появится наша таблица.

2. Затем щелкните мышью в строку формул и введите туда формулу для расчета средневзвешенного:

Несколько нюансов по формуле:

  • В Power Pivot есть свой встроенный язык с набором функций, инструментов и определенным синтаксисом, который называется DAX. Так что можно сказать, что эта формула — на языке DAX.
  • Здесь WA — это название вычисляемого поля (в Power Pivot они еще называются меры), которое вы придумываете сами (я называл WA, имея ввиду Weighted Average — «средневзвешенное» по-английски).
  • Обратите внимание, что после WA идет не равно, как в обычном Excel, а двоеточие и равно.
  • При вводе формулы будут выпадать подсказки — используйте их.
  • После завершения ввода формулы нужно нажать Enter , как и в обычном Excel.

3. Теперь строим сводную. Для этого в окне Power Pivot выберите на вкладке Главная — Сводная таблица (Home — Pivot Table). Вы автоматически вернетесь в окно Excel и увидите привычный интерфейс построения сводной таблицы и список полей на панели справа. Осталось закинуть поле Наименование в область строк, а нашу созданную формулой меру WA в область значений — и задача решена:

Вот так — красиво и изящно.

Общая мораль: если вы много и часто работаете со сводными таблицами и вам их возможности «тесноваты» — копайте в сторону Power Pivot и DAX — и будет вам счастье!

Расчет средневзвешенной процентной ставки портфеля в Excel

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

Пример формулы для расчета средневзвешенной процентной ставки в Excel

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

С целью вычисления средневзвешенной процентной ставки доля для каждого инвестиционного объекта в общей стоимости портфеля умножается на процентную ставку доходности. Функция СУММПРОИЗВ идеально подходит для перемножения двух наборов данных (массивов) с последующим суммированием результатов. Функция может иметь максимальное количество аргументом до 255, разделенных точкой с запятой. Но в данной формуле необходимо использовать только лишь 2 аргумента.

В первом аргументе указаны стоимости всех инвестиций, разделенных на их сумму, что дает пять процентных значений, представляющих вес каждой инвестиции в портфеле. На фонд «Pioneer Акции Восточной Европы» приходиться доля 17%, которая была вычислена в результате деления сумм 72021,35 на 423 655,02. Второй аргумент функции содержит процентные ставки доходности по каждой инвестиции. Функция СУММПРОИЗВ умножает каждый элемент с первого аргумента на соответствующий элемент со второго аргумента. Элемент B2/B7 умножается на C2, элемент B3/B7 на C3 и т.д. После перемножения всех пяти элементов функция суммирует результаты.

Если бы для вычисления средней процентной ставки доходности была просто использована функция СРЗНАЧ, в результате ее вычислений мы получили бы значение 5,906%. Это на самом деле меньшее значение чем показатель средневзвешенной процентной ставки портфеля. Например, инвестиция «Фонд Казна Top Brands» имеет большой процент доходности, как и большую долю в инвестиционном портфеле чем другие позиции.

Как рассчитать средневзвешенную процентную ставку в Excel пошагово

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

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

Источник

Содержание

  1. Использование описательной статистики
  2. Подключение «Пакета анализа»
  3. Размах вариации
  4. Вычисление коэффициента вариации
  5. Шаг 1: расчет стандартного отклонения
  6. Шаг 2: расчет среднего арифметического
  7. Шаг 3: нахождение коэффициента вариации
  8. Простая формула для расчета объема выборки
  9. Пример расчета объема выборки
  10. Задачи о генеральной доле
  11. По части судить о целом
  12. Как рассчитать объем выборки
  13. Как определить статистические выбросы и сделать выборку для их удаления в Excel
  14. Способ 1: применение расширенного автофильтра
  15. Способ 2: применение формулы массива
  16. СРЗНАЧ()
  17. СРЗНАЧЕСЛИ()
  18. МАКС()
  19. МИН()

Использование описательной статистики

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

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

  • Медиана;
  • Мода;
  • Дисперсия;
  • Среднее;
  • Стандартное отклонение;
  • Стандартная ошибка;
  • Асимметричность и др.

Рассмотрим, как работает данный инструмент на примере Excel 2010, хотя данный алгоритм применим также в Excel 2007 и в более поздних версиях данной программы.

Подключение «Пакета анализа»

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

  1. Переходим во вкладку «Файл». Далее производим перемещение в пункт «Параметры».

  2. В активировавшемся окне параметров перемещаемся в подраздел «Надстройки». В самой нижней части окна находится поле «Управление». Нужно в нем переставить переключатель в позицию «Надстройки Excel», если он находится в другом положении. Вслед за этим жмем на кнопку «Перейти…».

  3. Запускается окно стандартных надстроек Excel. Около наименования «Пакет анализа» ставим флажок. Затем жмем на кнопку «OK».

После вышеуказанных действий надстройка Пакет анализа будет активирована и станет доступной во вкладке «Данные» Эксель. Теперь мы сможем использовать на практике инструменты описательной статистики.

Размах вариации

Размах вариации – разница между максимальным и минимальным значением:

Ниже приведена графическая интерпретация размаха вариации.

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

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

Вычисление коэффициента вариации

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

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

Шаг 1: расчет стандартного отклонения

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

Синтаксис данных функций выглядит соответствующим образом:

= СТАНДОТКЛОН(Число1;Число2;…)
= СТАНДОТКЛОН.Г(Число1;Число2;…)
= СТАНДОТКЛОН.В(Число1;Число2;…)

  1. Для того, чтобы рассчитать стандартное отклонение, выделяем любую свободную ячейку на листе, которая удобна вам для того, чтобы выводить в неё результаты расчетов. Щелкаем по кнопке «Вставить функцию». Она имеет внешний вид пиктограммы и расположена слева от строки формул.

Выполняется активация Мастера функций, который запускается в виде отдельного окна с перечнем аргументов. Переходим в категорию «Статистические» или «Полный алфавитный перечень». Выбираем наименование «СТАНДОТКЛОН.Г» или «СТАНДОТКЛОН.В», в зависимости от того, по генеральной совокупности или по выборке следует произвести расчет. Жмем на кнопку «OK».

Открывается окно аргументов данной функции. Оно может иметь от 1 до 255 полей, в которых могут содержаться, как конкретные числа, так и ссылки на ячейки или диапазоны. Ставим курсор в поле «Число1». Мышью выделяем на листе тот диапазон значений, который нужно обработать. Если таких областей несколько и они не смежные между собой, то координаты следующей указываем в поле «Число2» и т.д. Когда все нужные данные введены, жмем на кнопку «OK»

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

    • Шаг 2: расчет среднего арифметического

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

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

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

    Запускается окно аргументов СРЗНАЧ. Аргументы полностью идентичны тем, что и у операторов группы СТАНДОТКЛОН. То есть, в их качестве могут выступать как отдельные числовые величины, так и ссылки. Устанавливаем курсор в поле «Число1». Так же, как и в предыдущем случае, выделяем на листе нужную нам совокупность ячеек. После того, как их координаты были занесены в поле окна аргументов, жмем на кнопку «OK».

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

    • Шаг 3: нахождение коэффициента вариации

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

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

    Снова возвращаемся к ячейке для вывода результата. Активируем её двойным щелчком левой кнопки мыши. Ставим в ней знак «=». Выделяем элемент, в котором расположен итог вычисления стандартного отклонения. Кликаем по кнопке «разделить» (/) на клавиатуре. Далее выделяем ячейку, в которой располагается среднее арифметическое заданного числового ряда. Для того, чтобы произвести расчет и вывести значение, щёлкаем по кнопке Enter на клавиатуре.

  • Как видим, результат расчета выведен на экран.

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

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

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

  • После этого, чтобы рассчитать значение и показать результат на экране монитора, щелкаем по кнопке Enter.

    • Существует условное разграничение. Считается, что если показатель коэффициента вариации менее 33%, то совокупность чисел однородная. В обратном случае её принято характеризовать, как неоднородную.

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

    Разделы: Математика

    • Совершенствование умений и навыков нахождения статистических характеристик случайной величины, работа с расчетами в Excel;
    • применение информационно коммутативных технологий для анализа данных; работа с различными информационными носителями.
    1. Сегодня мы научимся рассчитывать статистические характеристики для больших по объему выборок, используя возможности современных компьютерных технологий.
    2. Для начала вспомним:

    – что называется случайной величиной? (Случайной величиной называют переменную величину, которая в зависимости от исхода испытания принимает одно значение из множества возможных значений.)

    – Какие виды случайных величин мы знаем? (Дискретные, непрерывные.)

    – Приведите примеры непрерывных случайных величин (рост дерева), дискретных случайных величин (количество учеников в классе).

    – Какие статистические характеристики случайных величин мы знаем (мода, медиана, среднее выборочное значение, размах ряда).

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

    1. Рассмотрим, применение инструментов Excel для решения статистических задач на конкретном примере.

    Пример. Проведена проверка в 100 компаниях. Даны значения количества работающих в компании (чел.):

    23 25 24 25 30 24 30 26 28 26
    32 33 31 31 25 33 25 29 30 28
    23 30 29 24 33 30 30 28 26 25
    26 29 27 29 26 28 27 26 29 28
    29 30 27 30 28 32 28 26 30 26
    31 27 30 27 33 28 26 30 31 29
    27 30 30 29 27 26 28 31 29 28
    33 27 30 33 26 31 34 28 32 22
    29 30 27 29 34 29 32 29 29 30
    29 29 36 29 29 34 23 28 24 28    		 рассчитать числовые характеристики:

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

    1. Занести данные в EXCEL, каждое число в отдельную ячейку.

    23 25 24 25 30 24 30 26 28 26
    32 33 31 31 25 33 25 29 30 28
    23 30 29 24 33 30 30 28 26 25
    26 29 27 29 26 28 27 26 29 28
    29 30 27 30 28 32 28 26 30 26
    31 27 30 27 33 28 26 30 31 29
    27 30 30 29 27 26 28 31 29 28
    33 27 30 33 26 31 34 28 32 22
    29 30 27 29 34 29 32 29 29 30
    29 29 36 29 29 34 23 28 24 28

    2. Для расчета числовых характеристик используем опцию Вставка – Функция. И в появившемся окне в строке категория выберем – статистические, в списке: МОДА

    В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:

    Нажимаем клавишу ОК. Получили Мо = 29 (чел) – Фирм у которых в штате 29 человек больше всего.

    Используя тот же путь вычисляем медиану.

    Вставка – Функция – Статистические – Медиана.

    В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:

    Нажимаем клавишу ОК. Получили Ме = 29 (чел) – среднее значение сотрудников в фирме.

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

    Вставка – Функция – Статистические – МАКС.

    В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:

    Нажимаем клавишу ОК. Получили наибольшее значение = 36.

    Вставка – Функция – Статистические – МИН.

    В поле Число 1 ставим курсор и мышкой выделяем нашу таблицу:

    Нажимаем клавишу ОК. Получили наименьшее значение = 22.

    36 – 22 = 14 (чел) – разница между фирмой с наибольшим штатом сотрудников и фирмой с наименьшим штатом сотрудников.

    Для построения диаграммы и полигона частот необходимо задать закон распределения, т.е. составить таблицу значений случайной величины и соответствующих им частот. Мы ухе знаем, что наименьшее число сотрудников в фирме = 22, а наибольшее = 36. Составим таблицу, в которой значения xi случайной величины меняются от 22 до 36 включительно шагом 1.

    xi 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
    ni

    Чтобы сосчитать частоту каждого значения воспользуемся

    Вставка – Функция – Статистические – СЧЕТЕСЛИ.

    В окне Диапазон ставим курсор и выделяем нашу выборку, а в окне Критерий ставим число 22

    Нажимаем клавишу ОК, получаем значение 1, т.е. число 22 в нашей выборке встречается 1 раз и его частота =1. Аналогичным образом заполняем всю таблицу.

    xi 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
    ni 1 3 4 5 11 9 13 18 16 6 4 6 3 0 1

    Для проверки вычисляем объем выборки, сумму частот (Вставка – Функция – Математические – СУММА). Должно получиться 100 (количество всех фирм).

    Чтобы построить полигон частот выделяем таблицу – Вставка – Диаграмма – Стандартные – Точечная (точечная диаграмма на которой значения соединены отрезками)

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

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

    Диаграмма – Стандартные – Круговая.

    Диаграмма – Стандартные – Гистограмма.

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

    Простая формула для расчета объема выборки

    где: n – объем выборки;

    z – нормированное отклонение, определяемое исходя из выбранного уровня доверительности. Этот показатель характеризует возможность, вероятность попадания ответов в специальный – доверительный интервал. На практике уровень доверительности часто принимают за 95% или 99%. Тогда значения z будут соответственно 1,96 и 2,58;

    p – вариация для выборки, в долях. По сути, p – это вероятность того, что респонденты выберут той или иной вариант ответа. Допустим, если мы считаем, что четверть опрашиваемых выберут ответ «Да», то p будет равно 25%, то есть p = 0,25;

    q = (1 – p);

    e – допустимая ошибка, в долях.

    Пример расчета объема выборки

    Компания планирует провести социологическое исследование с целью выявить долю курящих лиц в населении города. Для этого сотрудники компании будут задавать прохожим один вопрос: «Вы курите?». Возможных вариантов ответа, таким образом, только два: «Да» и «Нет».

    Объем выборки в этом случае рассчитывается следующим образом. Уровень доверительности принимается за 95%, тогда нормированное отклонение z = 1,96. Вариацию принимаем за 50%, то есть условно считаем, что половина респондентов может ответить на вопрос о том, курят ли они – «Да». Тогда p = 0,5. Отсюда находим q = 1 – p = 1 – 0,5 = 0,5. Допустимую ошибку выборки принимаем за 10%, то есть e = 0,1.

    Подставляем эти данные в формулу и считаем:

    Получаем объем выборки n = 96 человек.

    Задачи о генеральной доле

    На вопрос «Накрывает ли доверительный интервал заданное значение p0?» — можно ответить, проверив статистическую гипотезу H0:p=p0. При этом предполагается, что опыты проводятся по схеме испытаний Бернулли (независимы, вероятность p появления события А постоянна). По выборке объема n определяют относительную частоту p* появления события A: где m — количество появлений события А в серии из n испытаний. Для проверки гипотезы H0 используется статистика, имеющая при достаточно большом объеме выборки стандартное нормальное распределение (табл. 1).
    Таблица 1 – Гипотезы о генеральной доле

    Гипотеза

    		 H0:p=p0 H0:p1=p2
    Предположения Схема испытаний Бернулли Схема испытаний Бернулли
    Оценки по выборке
    Статистика K
    Распределение статистики K Стандартное нормальное N(0,1) Стандартное нормальное N(0,1)

    Пример №1. С помощью случайного повторного отбора руководство фирмы провело выборочный опрос 900 своих служащих. Среди опрошенных оказалось 270 женщин. Постройте доверительный интервал, с вероятностью 0.95 накрывающий истинную долю женщин во всем коллективе фирмы.
    Решение. По условию выборочная доля женщин составляет (относительная частота женщин среди всех опрошенных). Так как отбор является повторным, и объем выборки велик (n=900) предельная ошибка выборки определяется по формуле
    (относительная частота женщин среди всех опрошенных). Так как отбор является повторным, и объем выборки велик (n=900) предельная ошибка выборки определяется по формуле

    Значение uкр находим по таблице функции Лапласа из соотношения 2Ф(uкр)=γ, т.е. Функция Лапласа (приложение 1) принимает значение 0.475 при uкр=1.96. Следовательно, предельная ошибка Функция Лапласа (приложение 1) принимает значение 0.475 при uкр=1.96. Следовательно, предельная ошибка и искомый доверительный интервал
    (p – ε, p + ε) = (0.3 – 0.18; 0.3 + 0.18) = (0.12; 0.48)
    Итак, с вероятностью 0.95 можно гарантировать, что доля женщин во всем коллективе фирмы находится в интервале от 0.12 до 0.48.

    Пример №2. Владелец автостоянки считает день «удачным», если автостоянка заполнена более, чем на 80 %. В течение года было проведено 40 проверок автостоянки, из которых 24 оказались «удачными». С вероятностью 0.98 найдите доверительный интервал для оценки истинной доли «удачных» дней в течение года.
    Решение. Выборочная доля «удачных» дней составляет
    По таблице функции Лапласа найдем значение uкр при заданной
    доверительной вероятности
    По таблице функции Лапласа найдем значение uкр при заданной
    доверительной вероятности
    Ф(2.23) = 0.49, uкр = 2.33.
    Считая отбор бесповторным (т.е. две проверки в один день не проводилось), найдем предельную ошибку:
    где n=40, N = 365 (дней). Отсюда
    где n=40, N = 365 (дней). Отсюда
    и доверительный интервал для генеральной доли: (p – ε, p + ε) = (0.6 – 0.17; 0.6 + 0.17) = (0.43; 0.77)
    С вероятностью 0.98 можно ожидать, что доля «удачных» дней в течение года находится в интервале от 0.43 до 0.77.

    Пример №3. Проверив 2500 изделий в партии, обнаружили, что 400 изделий высшего сорта, а n–m – нет. Сколько надо проверить изделий, чтобы с уверенностью 95% определить долю высшего сорта с точностью до 0.01?
    Решение ищем по формуле определения численности выборки для повторного отбора.

    Ф(t) = γ/2 = 0.95/2 = 0.475 и этому значению по таблице Лапласа соответствует t=1.96
    Выборочная доля w = 0.16; ошибка выборки ε = 0.01

    Пример №4. Партия изделий принимается, если вероятность того, что изделие окажется соответствующим стандарту, составляет не менее 0.97. Среди случайно отобранных 200 изделий проверяемой партии оказалось 193 соответствующих стандарту. Можно ли на уровне значимости α=0,02 принять партию?
    Решение. Сформулируем основную и альтернативную гипотезы.
    H0:p=p0=0,97 — неизвестная генеральная доля p равна заданному значению p0=0,97. Применительно к условию — вероятность того, что деталь из проверяемой партии окажется соответствующей стандарту, равна 0.97; т.е. партию изделий можно принять.
    H1:p<0,97 – вероятность того, что деталь из проверяемой партии окажется соответствующей стандарту, меньше 0.97; т.е. партию изделий нельзя принять. При такой альтернативной гипотезе критическая область будет левосторонней.
    Наблюдаемое значение статистики K (таблица) вычислим при заданных значениях p0=0,97, n=200, m=193


    Критическое значение находим по таблице функции Лапласа из равенства


    По условию α=0,02 отсюда Ф(Ккр)=0,48 и Ккр=2,05. Критическая область левосторонняя, т.е. является интервалом (-∞;-Kkp)= (-∞;-2,05). Наблюдаемое значение Кнабл=-0,415 не принадлежит критической области, следовательно, на данном уровне значимости нет оснований отклонять основную гипотезу. Партию изделий принять можно.

    Пример №5. Два завода изготавливают однотипные детали. Для оценки их качества сделаны выборки из продукции этих заводов и получены следующие результаты. Среди 200 отобранных изделий первого завода оказалось 20 бракованных, среди 300 изделий второго завода — 15 бракованных.
    На уровне значимости 0.025 выяснить, имеется ли существенное различие в качестве изготавливаемых этими заводами деталей.
    Решение. Это задача о сравнении генеральных долей двух совокупностей. Сформулируем основную и альтернативную гипотезы.
    H0:p1=p2 — генеральные доли равны. Применительно к условию — вероятность появления бракованного изделия в продукции первого завода равна вероятности появления бракованного изделия в продукции второго завода (качество продукции одинаково).
    H0:p1≠p2 — заводы изготавливают детали разного качества.
    Для вычисления наблюдаемого значения статистики K (таблица) рассчитаем оценки по выборке.


    Наблюдаемое значение равно


    Так как альтернативная гипотеза двусторонняя, то критическое значение статистики K≈ N(0,1) находим по таблице функции Лапласа из равенства
    Так как альтернативная гипотеза двусторонняя, то критическое значение статистики K≈ N(0,1) находим по таблице функции Лапласа из равенства
    По условию α=0,025 отсюда Ф(Ккр)=0,4875 и Ккр=2,24. При двусторонней альтернативе область допустимых значений имеет вид (-2,24;2,24). Наблюдаемое значение Kнабл=2,15 попадает в этот интервал, т.е. на данном уровне значимости нет оснований отвергать основную гипотезу. Заводы изготавливают изделия одинакового качества.

    По части судить о целом

    О возможности судить о целом по части миру рассказал российский математик П.Л. Чебышев. «Закон больших чисел» простым языком можно сформулировать так: количественные закономерности массовых явлений проявляются только при

    достаточном числе наблюдений

    . Чем больше выборка, тем лучше случайные отклонения компенсируют друг друга и проявляется общая тенденция.
    А.М. Ляпунов чуть позже сформулировал центральную предельную теорему. Она стала фундаментом для создания формул, которые позволяют рассчитать вероятность ошибки (при оценке среднего по выборке) и размер выборки, необходимый для достижения заданной точности.
    Строгие формулировки:

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

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

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

    Как рассчитать объем выборки

    Достаточный размер выборки зависит от следующих составляющих:

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

    ЗАПОМНИТЕ
    Объем выборки зависит от изменчивости признака и планируемой строгости эксперимента

    Формулы для расчета объема выборки:

    Формулы расчета объема выборки

    Ошибка выборки значительно возрастает, когда наблюдений меньше ста. Для исследований в которых используется 30-100 объектов применяется особая статистическая методология: критерии, основанные на распределении Стьюдента или бутстрэп-анализ. И наконец, статистика совсем слаба, когда наблюдений меньше 30.

    График зависимости ошибки выборки от ее объема при оценке доли признака в г.с.

    Чем больше неопределенность, тем больше ошибка. Максимальная неопределенность при оценке доли — 50% (например, 50% респондентов считают концепцию хорошей, а другие 50% плохой). Если 90% опрошенных концепция понравится — это, наоборот, пример согласованности. В таких случаях оценить долю признака по выборке проще.

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

    Первым шагом в поиске значений выбросов статистики является определение статистического центра диапазона данных. С этой целью необходимо сначала определить границы первого и третьего квартала. Определение границ квартала – значит разделение данных на 4 равные группы, которые содержат по 25% данных каждая. Группа, содержащая 25% наибольших значений, называется первым квартилем.

    Границы квартилей в Excel можно легко определить с помощью простой функции КВАРТИЛЬ. Данная функция имеет 2 аргумента: диапазон данных и номер для получения желаемого квартиля.

    В примере показанному на рисунке ниже значения в ячейках E1 и E2 содержат показатели первого и третьего квартиля данных в диапазоне ячеек B2:B19:

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

    В этом месте возникает вопрос, как сильно данное значение может отличаться от среднего значения 50% данных и оставаться все еще в пределах нормы? Статистические аналитики соглашаются с тем, что для определения нижней и верхней границы диапазона данных можно смело использовать коэффициент расширения 1,5 умножив на значение межквартильного диапазона. То есть:

    1. Нижняя граница диапазона данных равна: значение первого квартиля – межкваритльный диапазон * 1,5.
    2. Верхняя граница диапазона данных равна: значение третьего квартиля + расширенных диапазон * 1,5.

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

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

    Способ 1: применение расширенного автофильтра

    Наиболее простым способом произвести отбор является применение расширенного автофильтра. Рассмотрим, как это сделать на конкретном примере.

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

      Есть возможность поступить и по-другому. Для этого после выделения области на листе перемещаемся во вкладку «Данные». Щелкаем по кнопке «Фильтр», которая размещена на ленте в группе «Сортировка и фильтр».

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

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

      Давайте в качестве примера зададим условие так, чтобы отобрать только значения, по которым сумма выручки превышает 10000 рублей. Устанавливаем переключатель в позицию «Больше». В правое поле вписываем значение «10000». Чтобы произвести выполнение действия, щелкаем по кнопке «OK».

    4. Как видим, после фильтрации остались только строчки, в которых сумма выручки превышает 10000 рублей.

    5. Но в этом же столбце мы можем добавить и второе условие. Для этого опять возвращаемся в окно пользовательской фильтрации. Как видим, в его нижней части есть ещё один переключатель условия и соответствующее ему поле для ввода. Давайте установим теперь верхнюю границу отбора в 15000 рублей. Для этого выставляем переключатель в позицию «Меньше», а в поле справа вписываем значение «15000».

      Кроме того, существует ещё переключатель условий. У него два положения «И» и «ИЛИ». По умолчанию он установлен в первом положении. Это означает, что в выборке останутся только строчки, которые удовлетворяют обоим ограничениям. Если он будет выставлен в положение «ИЛИ», то тогда останутся значения, которые подходят под любое из двух условий. В нашем случае нужно выставить переключатель в положение «И», то есть, оставить данную настройку по умолчанию. После того, как все значения введены, щелкаем по кнопке «OK».

    6. Теперь в таблице остались только строчки, в которых сумма выручки не меньше 10000 рублей, но не превышает 15000 рублей.

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

    8. Снова запускается окно пользовательского автофильтра. Выполним отбор результатов в таблице с 4 по 6 мая 2016 года включительно. В переключателе выбора условий, как видим, ещё больше вариантов, чем для числового формата. Выбираем позицию «После или равно». В поле справа устанавливаем значение «04.05.2016». В нижнем блоке устанавливаем переключатель в позицию «До или равно». В правом поле вписываем значение «06.05.2016». Переключатель совместимости условий оставляем в положении по умолчанию – «И». Для того, чтобы применить фильтрацию в действии, жмем на кнопку «OK».

    9. Как видим, наш список ещё больше сократился. Теперь в нем оставлены только строчки, в которых сумма выручки варьируется от 10000 до 15000 рублей за период с 04.05 по 06.05.2016 включительно.

    10. Мы можем сбросить фильтрацию в одном из столбцов. Сделаем это для значений выручки. Кликаем по значку автофильтра в соответствующем столбце. В выпадающем списке щелкаем по пункту «Удалить фильтр».

    11. Как видим, после этих действий, выборка по сумме выручки будет отключена, а останется только отбор по датам (с 04.05.2016 по 06.05.2016).

    12. В данной таблице имеется ещё одна колонка – «Наименование». В ней содержатся данные в текстовом формате. Посмотрим, как сформировать выборку с помощью фильтрации по этим значениям.

      Кликаем по значку фильтра в наименовании столбца. Последовательно переходим по наименованиям списка «Текстовые фильтры» и «Настраиваемый фильтр…».

    13. Опять открывается окно пользовательского автофильтра. Давайте сделаем выборку по наименованиям «Картофель» и «Мясо». В первом блоке переключатель условий устанавливаем в позицию «Равно». В поле справа от него вписываем слово «Картофель». Переключатель нижнего блока так же ставим в позицию «Равно». В поле напротив него делаем запись – «Мясо». И вот далее мы выполняем то, чего ранее не делали: устанавливаем переключатель совместимости условий в позицию «ИЛИ». Теперь строчка, содержащая любое из указанных условий, будет выводиться на экран. Щелкаем по кнопке «OK».

    14. Как видим, в новой выборке существуют ограничения по дате (с 04.05.2016 по 06.05.2016) и по наименованию (картофель и мясо). По сумме выручки ограничений нет.

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

      Второй вариант предполагает переход во вкладку «Главная». Там выполняем щелчок на ленте по кнопке «Сортировка и фильтр» в блоке «Редактирование». В активировавшемся списке нажимаем на кнопку «Фильтр».

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

    Способ 2: применение формулы массива

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

    1. На том же листе создаем пустую таблицу с такими же наименованиями столбцов в шапке, что и у исходника.

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

      =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

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

    3. Так как это формула массива, то для того, чтобы применить её в действии, нужно нажимать не кнопку Enter, а сочетание клавиш Ctrl+Shift+Enter. Делаем это.

    4. Выделив второй столбец с датами и установив курсор в строку формул, вводим следующее выражение:

      =ИНДЕКС(B2:B29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

      Жмем сочетание клавиш Ctrl+Shift+Enter.

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

      =ИНДЕКС(C2:C29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

      Опять набираем сочетание клавиш Ctrl+Shift+Enter.

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

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

    7. В открывшемся окне форматирования открываем вкладку «Число». В блоке «Числовые форматы» выделяем значение «Дата». В правой части окна можно выбрать желаемый тип отображения даты. После того, как настройки выставлены, жмем на кнопку «OK».

    8. Теперь дата отображается корректно. Но, как видим, вся нижняя часть таблицы заполнена ячейками, которые содержат ошибочное значение «#ЧИСЛО!». По сути, это те ячейки, данных из выборки для которых не хватило. Более привлекательно было бы, если бы они отображались вообще пустыми. Для этих целей воспользуемся условным форматированием. Выделяем все ячейки таблицы, кроме шапки. Находясь во вкладке «Главная» кликаем по кнопке «Условное форматирование», которая находится в блоке инструментов «Стили». В появившемся списке выбираем пункт «Создать правило…».

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

    10. В запустившемся окне форматирования переходим во вкладку «Шрифт» и в соответствующем поле выбираем белый цвет. После этих действий щелкаем по кнопке «OK».

    11. На кнопку с точно таким же названием жмем после возвращения в окно создания условий.

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

    СРЗНАЧ()

    Статистическая функция СРЗНАЧ возвращает среднее арифметическое своих аргументов.

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

    Если в рассчитываемом диапазоне встречаются пустые или содержащие текст ячейки, то они игнорируются. В примере ниже среднее ищется по четырем ячейкам, т.е. (4+15+11+22)/4 = 13

    Если необходимо вычислить среднее, учитывая все ячейки диапазона, то можно воспользоваться статистической функцией СРЗНАЧА. В следующем примере среднее ищется уже по 6 ячейкам, т.е. (4+15+11+22)/6 = 8,6(6).

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

    СРЗНАЧЕСЛИ()

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

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

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

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

    МАКС()

    Статистическая функция МАКС возвращает наибольшее значение в диапазоне ячеек:

    МИН()

    Статистическая функция МИН возвращает наименьшее значение в диапазоне ячеек:

    Источники

    • https://lumpics.ru/descriptive-statistics-in-excel/
    • https://statanaliz.info/statistica/opisanie-dannyx/variatsiya-razmakh-srednee-linejnoe-otklonenie/
    • https://www.hd01.ru/info/kak-poschitat-razmah-v-excel/
    • http://galyautdinov.ru/post/formula-vyborki-prostaya
    • https://math.semestr.ru/group/interval-estimation-share.php
    • https://tidydata.ru/sample-size
    • https://exceltable.com/formuly/raschet-statisticheskih-vybrosov
    • https://lumpics.ru/how-to-make-a-sample-in-excel/
    • https://office-guru.ru/excel/statisticheskie-funkcii-excel-kotorye-neobhodimo-znat-96.html
    Источник

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

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

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

    Чтобы найти среднее арифметическое, необходимо сложить все числа в наборе и разделить сумму на количество. Например, оценки школьника по информатике: 3, 4, 3, 5, 5. Что выходит за четверть: 4. Мы нашли среднее арифметическое по формуле: =(3+4+3+5+5)/5.

    Как это быстро сделать с помощью функций Excel? Возьмем для примера ряд случайных чисел в строке:

    Случайные числа.

    1. Ставим курсор в ячейку А2 (под набором чисел). В главном меню – инструмент «Редактирование» — кнопка «Сумма». Выбираем опцию «Среднее». После нажатия в активной ячейке появляется формула. Выделяем диапазон: A1:H1 и нажимаем ВВОД.

      2. Вызов функции СРЗНАЧ.
      Указание диапазона.

    2. В основе второго метода тот же принцип нахождения среднего арифметического. Но функцию СРЗНАЧ мы вызовем по-другому. С помощью мастера функций (кнопка fx или комбинация клавиш SHIFT+F3).

      3. Мастер функций.

    3. Третий способ вызова функции СРЗНАЧ из панели: «Формула»-«Формула»-«Другие функции»-«Статические»-«СРЗНАЧ».

      4. Закладка Формула.

    Или: сделаем активной ячейку и просто вручную впишем формулу: =СРЗНАЧ(A1:A8).

    Теперь посмотрим, что еще умеет функция СРЗНАЧ.

    Два диапазона в формуле.

    Найдем среднее арифметическое двух первых и трех последних чисел. Формула: =СРЗНАЧ(A1:B1;F1:H1). Результат:

    

    Среднее значение по условию

    Условием для нахождения среднего арифметического может быть числовой критерий или текстовый. Будем использовать функцию: =СРЗНАЧЕСЛИ().

    Найти среднее арифметическое чисел, которые больше или равны 10.

    Функция: =СРЗНАЧЕСЛИ(A1:A8;»>=10″)

    Функция СРЗНАЧЕСЛИ.
    Результат использования функции СРЗНАЧЕСЛИ по условию «>=10»:
    Результат функции СРЗНАЧЕСЛИ.

    Третий аргумент – «Диапазон усреднения» — опущен. Во-первых, он не обязателен. Во-вторых, анализируемый программой диапазон содержит ТОЛЬКО числовые значения. В ячейках, указанных в первом аргументе, и будет производиться поиск по прописанному во втором аргументе условию.

    Внимание! Критерий поиска можно указать в ячейке. А в формуле сделать на нее ссылку.

    Найдем среднее значение чисел по текстовому критерию. Например, средние продажи товара «столы».

    Отчет по продажам.

    Функция будет выглядеть так: =СРЗНАЧЕСЛИ($A$2:$A$12;A7;$B$2:$B$12). Диапазон – столбец с наименованиями товаров. Критерий поиска – ссылка на ячейку со словом «столы» (можно вместо ссылки A7 вставить само слово «столы»). Диапазон усреднения – те ячейки, из которых будут браться данные для расчета среднего значения.

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

    Результат функции СРЗНАЧЕСЛИ.

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

    Как посчитать средневзвешенную цену в Excel?

    Как посчитать средний процент в Excel? Для этой цели подойдут функции СУММПРОИЗВ и СУММ. Таблица для примера:

    Исходный прайс-лист.

    Как мы узнали средневзвешенную цену?

    Формула: =СУММПРОИЗВ(C2:C12;B2:B12)/СУММ(C2:C12).

    Средневзвешенная цена.

    С помощью формулы СУММПРОИЗВ мы узнаем общую выручку после реализации всего количества товара. А функция СУММ — сумирует количесвто товара. Поделив общую выручку от реализации товара на общее количество единиц товара, мы нашли средневзвешенную цену. Этот показатель учитывает «вес» каждой цены. Ее долю в общей массе значений.

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

    Различают среднеквадратическое отклонение по генеральной совокупности и по выборке. В первом случае это корень из генеральной дисперсии. Во втором – из выборочной дисперсии.

    Для расчета этого статистического показателя составляется формула дисперсии. Из нее извлекается корень. Но в Excel существует готовая функция для нахождения среднеквадратического отклонения.

    СТАНДОТКЛОНП.

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

    среднеквадратическое отклонение / среднее арифметическое значение

    Формула в Excel выглядит следующим образом:

    СТАНДОТКЛОНП (диапазон значений) / СРЗНАЧ (диапазон значений).

    Коэффициент вариации считается в процентах. Поэтому в ячейке устанавливаем процентный формат.

    Источник

    Понравилась статья? Поделить с друзьями:

    А вот еще интересные статьи:

  • Работа с абсолютными числами в excel
  • Работа с версиями word
  • Работа с абзацем текста в microsoft word
  • Работа с буквами excel
  • Работа с бюджетом в excel

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии