Расчет коэффициента вариации бетона в excel

Обновлено: 07.05.2024

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

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

Как рассчитать коэффициент вариации в Excel

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

CV – коэффициент вариации;
σ – среднеквадратическое отклонение по выборке;
ǩ – среднеарифметическое значение разброса значений.

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

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

В Excel не существует встроенной функции для расчета коэффициента вариации. Но можно найти частное от стандартного отклонения и среднего арифметического значения. Рассмотрим на примере.

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

Наглядно это можно продемонстрировать на графике:

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

Значение коэффициента для компании А – 33%, что свидетельствует об относительной однородности ряда. Формула расчета коэффициента вариации в Excel:

Сравните: для компании В коэффициент вариации составил 50%: ряд не является однородным, данные значительно разбросаны относительно среднего значения.

Интерпретация результатов

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

Ожидаемая доходность ценных бумаг составит:

Среднеквадратическое отклонение доходности для активов компании А и В составляет:

Ценные бумаги компании В имеют более высокую ожидаемую доходность. Они превышают ожидаемую доходность компании А в 1,14 раза. Но и инвестировать в активы предприятия В рискованнее. Риск выше в 1,7 раза. Как сопоставить акции с разной ожидаемой доходностью и различным уровнем риска?

Для сопоставления активов двух компаний рассчитан коэффициент вариации доходности. Показатель для предприятия В – 50%, для предприятия А – 33%. Риск инвестирования в ценные бумаги фирмы В выше в 1,54 раза (50% / 33%). Это означает, что акции компании А имеют лучшее соотношение риск / доходность. Следовательно, предпочтительнее вложить средства именно в них.

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

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

Определение среднего квадратичного отклонения

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

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

Расчет в Excel

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

Способ 1: мастер функций

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

Способ 2: вкладка «Формулы»

Также рассчитать значение среднеквадратичного отклонения можно через вкладку «Формулы».

«Формулы»

Способ 3: ручной ввод формулы

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

=СТАНДОТКЛОН.Г(число1(адрес_ячейки1); число2(адрес_ячейки2);…)
или
=СТАНДОТКЛОН.В(число1(адрес_ячейки1); число2(адрес_ячейки2);…).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

«Главная»

«Число»

«Процентный»

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

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

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

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

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

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

У меня несколько вопросов по производству и оценке качества бетонной смеси. Буду признателен за ответы, особенно если они будут подкреплены ссылками на соответствующие ГОСТы и другие нормативные документы.

1. ГОСТ 27006-86 требует (при отсутствии статистики по однородности, в начале производства) принятия требуемой прочности бетона при коэф. вариации 13,5. Возможно ли, при полной уверенности в своем производстве (на свой страх и риск) принять требуемую прочность бетона, скажем, при коэф. вариации 6 или 7. Затем в процессе производства получить необхдимые статистические данные, в требуемом ГОСТ объеме, и подтвердить данный коэф. Вариации «задним числом»?
2. Вопрос по определению характеристик однородности (п. 3, ГОСТ 18105-86). Согласно этого пункта ГОСТ, ср. квад. отклонение и коэффициент вариации определяются для каждой партии отдельно, потом определяется средний коэффициент вариации. При этом получается так что отклонение в результатах между партиями не учитывается. То есть, к примеру, партия 1 имеет результаты 30 и 31, вторая 40 и 41 и третья 50 и 51 для одного и того же бетона (просто написал нереальные цифры от фанаря, для примера) при этом в расчет идут размах 1, 1 и 1. В итоге средний коэффициент вариации очень маленький! Но межпартионный разброс то огромный! Как быть? Или я что-то здесь не правильно считаю?
3. При испытании кубов на заводе, среднее значение прочности должно быть не меньше требуемой (с учетом коэф. Вариации) (п. 5.2, ГОСТ 18105-86). Как быть когда высверливаем образцы из самой конструкции, с чем сравнивать полученную среднюю прочность образцов? С нормируемой прочностью? То есть, к примеру для бетона класса В30, результаты тестов (цилиндры высверленные из конструкции) переводим при помощи коэффициента в кубическую прочность, и сравниваем с 30мПа?
Спасибо!

инженер технолог строитель

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

По 1 вопросу - думаю нельзя, если руководствоваться ГОСТом .Если вы только начинаете работать, что вам мешает за месяц получить требуемые коэффициенты?
По 2 вопросу - ну и пусть огромный - вам главное получить прочность не ниже требуемой, т.к. все что ниже не проходит, а что выше. Это с вас хозяин спросит за перерасход цемента.В ГОСТ 10106 еще были верхняя предупредительная граница, нижняя, но в новом их по-моему убрали
По 3 вопросу- думаю, что керны нужно воспринимать как образцы хранившиеся с конструкцией и выполнять с их прочностью требуемые статистические расчеты.

serge0ev, спасибо за ответ,

по первому вопросу согласен, проблема в том что наши субчики (Турки) у гостах особо неразбираются и уже сделали таким образом, ищим пути отмазаться перед заказчиком теперь. бетон уже залит 12 000 кубов )) проблема думаю может возникнуть с первым бетоном, который без расчетного коэф-та вариации выпускался.. в последствии то они его получили и он очень низкий от 2х до 6.5 (для разных бет. заводов..)

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

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

коллеги, если кто то еще поделится соображениями по этому поводу, буду очень благодарен!

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

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

по третему тоже пришел к такому же выводу.. для начала 30 кернов,

Партия (группа) монолитных конструкций подлежит приемке, если фактический класс бетона в партии и каждой отдельной конструкции этой партии Вф не ниже проектного класса бетона В (Вф ≥ В).(По ГОСТ 53231) А количество образцов для определения Вф не обязательно 30.

2. Вопрос по определению характеристик однородности (п. 3, ГОСТ 18105-86). Согласно этого пункта ГОСТ, ср. квад. отклонение и коэффициент вариации определяются для каждой партии отдельно, потом определяется средний коэффициент вариации. При этом получается так что отклонение в результатах между партиями не учитывается. То есть, к примеру, партия 1 имеет результаты 30 и 31, вторая 40 и 41 и третья 50 и 51 для одного и того же бетона (просто написал нереальные цифры от фанаря, для примера) при этом в расчет идут размах 1, 1 и 1. В итоге средний коэффициент вариации очень маленький! Но межпартионный разброс то огромный! Как быть? Или я что-то здесь не правильно считаю?

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

в ГОСТе определение "партии" расплывчато, следовательно принимаем наиболее нам подходящий вариант (то есть дающий наилучшие результаты) партия равна 24 часам производства бетона. Прилагаю эксель файл для 4х заводов (2й - 5й листы)
http://files.mail.ru/PGTWJN ,
использована методика ГОСТ 18105 для определения стат. данных и требуемой прочности. . ОН на вражеском языке, но по формулам видно что где и что как определяется. Разброс довольно ощутимый "на глаз" но при этом к-т вариации очень не плохой получается. Я так понимаю ГОСТ позволяет "поиграть" с определением "партии" для получения как раз более оптимальных результатов.. к примеру, при определении партии как недельного объема бетона (что тоже не противоречит ГОСТ) к-т вариации привысил 10%.. (для тех где сейчас 2%) т.е. в 5 раз вырос. RomaV поделись своей мозговрывательной прогой )) потому как мой мозг так же во взрывоопасном состоянии )))

serge0ev, Согласно ГОСТ 18105-86 (строительство в Туркменистане, там ГОСТ 5321 не принят, думаю что принципиально ничего не изменилось), цитата
"[FONT=Times New Roman]3.1. Продолжительность анализируемого периода для определения характеристик однородности бетона устанавливают от одной недели до 2 мес. Число единичных значений прочности бетона в течение этого периода должно составлять не менее 30[/FONT]"
следовательно для определния собственных "стройплощадочных" стат. данных нужно испытать мин. 30 кернов.. Но можно использовать к-т вариации завода, особенно если он такой хороший получился как в моем случае (если я его конечно правильно посчитал) цитата
"[FONT=Times New Roman]3.6. При контроле прочности бетона на строительной площадке коэффициент вариации прочности бетона принимают по документу о качестве бетонной смеси предприятия-изготовителя.[/FONT]"
Следовательно брать ту же "требуемую прочность" с завода, расчитанную по заводскому к-ту вариации и использовать ее как критерий оценки соответствия. т.е. что меньше получается по кернам, то не соответствует заявленному классу. При этом действительно кол-во кубов должно быть минимум 4 с партии, то есть два единичных значения по два кубика на каждое.. итого 4 кубика .. правильно?

Нет, там не "расплывчато", а "мудрёно" и путается с другими определениями (единичное значение прочности, контролируемый период и т.п.).
Вот условия включения какой либо массы бетона (пробы) в партию №XXX-ZZ (применительно к БСУ):
1) - один номинальный состав (п. 2.1 ГОСТ 18105-86)
2) - изготовлено от 1 смены до 1 недели (п. 2.1 ГОСТ 18105-86)
3) - отбирается не менее 2-х проб для каждой партии (п. 2.2 ГОСТ 18105-86)
4) - отбирается не менее 1-й пробы в сутки для каждой партии (п. 2.2 ГОСТ 18105-86)

Удобно партией считать бетон за неделю (условие 2 выполнено), при этом пробы берём каждые сутки (условие 4 выполнено), условие 3 выполняется само сабой т.к. проб пулучится не меньше 7 (по одной пробе в сут.) при ежедневном выпуске. Всё это делается для каждого состава (условие 1 выполнено)!
Если партией считать бетон за сутки, то получится минимум 2 пробы (по две пробы в сутки из условия 3).

Из каждой пробы изготавливают серию образцов по ГОСТ 10180—90 (кол-во образцов в серии от 2-х до 6-ти, определяется по этому же ГОСТу, п.2.1.3.). Результат испытания серии будет являтся единичным результатом Ri (п. 2.7 ГОСТ 18105-86).
У тебя здесь ошибка - за еденичный результат принят один образец (кубик), а не серия.

Теперь надо выбрать анлизируемый период, что бы колличество Ri было не меньше 30. Тут всё зависит от того сколько проб набирается, наберёш за неделю больше 30, считай период неделей и включай туда партии за этот период (это может быть и одна партия). Я брал месяц, но и то не по всем составам получалось 30 проб набрать.

Дальше в зависимости от кол-ва проб (больше или меньше 6 - число n ) считаешь Sm по формулам 2 или 3 (ГОСТ 18105-86) соответсвенно.
Ты считаешь только по формуле 3, т.к. еденичных заначений Ri принимаешь не больне шести, но ошибочны сами значения.

А общий лист расчётов для чего?

P/S/ Прогу пока не дам, давай разбиратся пока так.

RomaV, спасибо за подробный ответ! Ошибку понял, исправил.. Прилагаю обновленный вариант для одного завода. Общую таблицу начинали делать до меня, похоже вели статистику общую для всех БСУ.. что я так понимаю не правильно и разделил для каждого БСУ отдельно..
Партии у нас имеют переменную продолжительность (от одного дня до недели), ГОСТ я так понял это позволяет, лишь бы уклавываться в те требования о которых ты писал.. Переменная продолжительность для получения лучшего результата.
Касательно 4го пункта в твоем письме, у нас иногда были простои БРУ и бетон не выпускался, ну и естественно пробы не брались.. получилось что в одной партии есть «разрывы» в несколько дней.. Не противоречит ли это требованиям ГОСТ 18105-86 ?

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

Касательно 4го пункта в твоем письме, у нас иногда были простои БРУ и бетон не выпускался, ну и естественно пробы не брались.. получилось что в одной партии есть «разрывы» в несколько дней.. Не противоречит ли это требованиям ГОСТ 18105-86 ?

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

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

Для всех БСУ нельзя так как п. 2.1 ГОСТ 18105-86 требует чтобы бетон был изготовлен на одном технологическом комплексе.

Вот файл в Excel. Расчёт идёт на листе "Вариация" (надо нажать кнопку "Обновить данные расчёта"), данные берутся с листа "Жулнал испытаний". В ячейку с номером состава нужно ввести номер (по журналу) по какому составу надо считать вариацию (здесь у меня достаточно данных только по составу №141). Там где даты над кнопкой (лист "Вариация") - это начало и конец анализируемого периода (щёлкаешь туда - появится календарь).
Для работы программы нужно что бы в Excel был разрешён запуск макросов.

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

Вычисление дисперсии

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

Способ 1: расчет по генеральной совокупности

Для расчета данного показателя в Excel по генеральной совокупности применяется функция ДИСП.Г. Синтаксис этого выражения имеет следующий вид:

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

Посмотрим, как вычислить это значение для диапазона с числовыми данными.

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

Способ 2: расчет по выборке

В отличие от вычисления значения по генеральной совокупности, в расчете по выборке в знаменателе указывается не общее количество чисел, а на одно меньше. Это делается в целях коррекции погрешности. Эксель учитывает данный нюанс в специальной функции, которая предназначена для данного вида вычисления – ДИСП.В. Её синтаксис представлен следующей формулой:

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

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

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

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

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

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

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