Расчет среднего отклонения в excel. Дисперсия и стандартное отклонение в MS EXCEL

PRO отмычки

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

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

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

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

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

Я сам пользовался всеми этими приспособлениями для отпирания замков. Кстати, обучение начал только когда мне в руки попал механический "пистолет". После этого у меня появился интерес отпереть замок старым, "дедовским" способом. Скажу так, я отпирал замки лишь для развлечения и на занятиях со своими экспертами и стал делать это за считанные секунды. Правда отпирал я старые отечественные замки (они не отличались точностью производства), да нынешние Китайские (в том числе и АПЕКС). Ну а теперь подумайте, за сколько секунд вор откроет вашу дверь?

Да обойдут Вас беды стороной!

Способ первый:

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

Затем нужно определить, c какой стороны расположены цилиндры – схема 2, и вставить изготовленную отмычку до упора в замочную скважину, изогнутой стороной к цилиндрам и попытаться провернуть по часовой стрелке, резкими, но аккуратными движениями, стараясь не погнуть. Если попытка не удалась, повторите все действие заново, но так, чтобы "узор" изготовляемой отмычки не совпадал с предыдущим.

Несколько полезных советов:

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

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

Изготовителем первой «профессиональной» отмычки стал американский изобретатель Альфред Хоббс, получивший на свои отмычки несколько патентов и в 1851 году основавший компанию Hobbs Hart & Co. Ltd, торговавшую отмычками.

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

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

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

Как и из чего сделать отмычки

Чего для этого нам надо

  1. дрель или станок, а лучше точило;
  2. образивный круг (если только дрель) (Фото1);
  3. расклодной метр (Фото2) или подходящую пластину толщиной 0,5-0,7мм (главное чтобы была из хорошего металла и не слишком гнулась, например нож, каленая пластина или пружина подходящая по размеру) из неё мы будем делать саму отмычку;
  4. малинький шестигранник из него мы будем делать вороток;
  5. испытуймый (личину от замка любую подходящую, но лучше какую-нибудь корейскую так как проще начинать тренировку);
  6. тески (без них очень неудобно будет);
  7. терпение и руки из правильного места.

Начнем!

Для начала надо выбрать какую отмычку мы хоти сделать (их огромный выбор). Мы же будем делать одну из трёх самых простых и самых распространенных. Разбираем метр для этого надо сточить по бокам клёпки только их не выбрасывать они нам ещё пригодятся (см.Фото3).

Следующий этап это обработка звена метра обрабатываем его по размерам приведённым на Фото4. Обработка ведётся с помощью насадки камня (см.Фото1) главное не перегревать звено метра. Слишком длинную рабочую часть делать не стоит, так как она может сломаться, а нам надо всего чтобы отмычка доставала до последнего солдатика (см.Фото5).

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

Теперь готовые отмычки скрепляем по три штуки всё теми же клёпками, но можно и не скреплять как вам удобней (см.Фото6).

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

Берём шестигранник 3 или подходящий пруток из хорошего металла и делаем “кочергу” (см.Фото7).

Так же можно сделать толкатель из ключа (см.Фото8) мы просто удаляем рабочую часть оставляем только верхнею часть ключа.

Рассмотрим другие виды замков, отмычек к ним и способы вскрытия без отмычек.

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

Открывается он обычно двумя способами:

  • 1. Свёртышем делается он из насадок (бит) фирмы KRAFTOOL или аналогичных известных фирм, но не из таких как STAYER и аналогичных ширпотребных.Можно сделать его из прутков хорошего сплава проверяется он методом тыка. Лучше всего, конечно, сделать его из титана достать его можно у токарей и обработать там же. Выглядит свёртыш см. Фото9 как заготовка ключа с шестигранником на конце под головку. Обработка бит всё тем же камнем см.Фото1 и самое главное температура бит при обработке не должна превышать 80 градусов, так как это приведёт к отпуску металла. После обработки, готовый свёртыш надо отшлифовать делается это для того, чтобы он не сломался. Представьте если на стекле сделать царапину и стукнуть, то стекло сломается в этом месте на свёртыше тоже самое если осталась царапина, то он именно в этом месте свернётся, Проверенно! Ну как им пользоваться всем понятно. Вставляем свёртыш до упора в замок надеваем головку и крутим по часовой стрелке.
  • 2. С помощью съемника. Покупаются болты-саморезы. Саморез вкручиваются в диски до упора так чтобы головка болта выступала из замка на сантиметр делается это для того чтобы зацепился съемник. Съемник выглядит так см.Фото10. Этим же способом можно открыть английский замок (штифтовой).

Рассмотрим навесные замки

Ну тут всё просто открывать навесные замки нет смысла если его можно обойти. Берём разводной ключ длинной эдак сантиметров 40 чем длиннее тем легче им орудовать. Зажимаем одно из душек двери и перегибаем его.

Бамбинг этот метод изобрели ещё в 1950 году, основывался он на простом методе. Все мы играли в бильярд, если мы ударим по шару 1, то мы ему отдаем импульс удара. Шар 1 ударяясь об 2 шар останавливается, а 2 начинает движение. Как мы знаем у цилиндровых замков есть по два солдатика в каждой ячейки. Если мы ударим по верхним солдатикам то они останутся на месте а нижние уйдут в низ до упора на несколько нано секунд. Если мы в этот момент начнём поворачивать то замок откроется это несложно как покажется с первого раза. По этому методу и придумали пистолеты пружины которые ударяли по солдатикам. Но совсем недавно придумали гениальную вещь BUMP-ключ см.Фото11.

Теперь объясню смысл переделки. Нам нужно открыть замок, какой ни будь хорошей фирмы достаём ключ от таково же замка протачиваем канавки на максимальную длину секрета, теперь стачиваем носик и упор ключа, делается это для того чтобы ключ после того как мы его вставили оставлял зазор между ключом и цилиндром 0,5-1,0мм. Теперь если мы ударим по торцу ключа то он уйдёт на 0,5-1,0мм в цилиндр заставляя солдатики опуститься вниз. Передовая нижним солдатикам энергию и в этот момент мы поворачиваем ключ. Этим способом можно открыть даже Mul-T-Lock и абсолютно любые цилиндрические замки с вертикальной и горизонтальной нарезкой секретов.

Удачи в ваших начинаниях и не нарушайте закон!

Оригинал статьи на ангийском

In order to be able to open a lock it is important to have the right tools for the job.
This section will teach you what tools are required for the particular lock you are attempting to open.

The majority of warded locks come in the form of padlocks. The tools needed for such a lock can either be made or purchased at low cost. These being skeleton keys. This type of lock is the only type which skeleton keys are available for. At this point it should be noted what a skeleton key actually is in order to dispel any myths about them.
A skeleton key is simply a key which only consists of enough metal to open the lock. With the warded lock, with reference to the mechanisms section, it can be seen that part of the lock does not move, i.e. the actual ward. This is where a key which only has enough metal to operate the locking part, comes in. The diagram below illustrates the function and operation of the skeleton key.

As can be seen, part of the key has been filed away preventing the key from being stopped by the ward, thus allowing the remaining end section to come into contact with the locking mechanism and open the lock.

These skeleton keys can be bought quite cheaply, however, they can also be made with little difficulty. A skeleton key for a particular make of a warded lock which opens other locks in the same series can be made quite easily by removing part of the key which would be obstructed by the ward. The diagram below shows how a key to a lock can be made to open other locks of the same series and possibly warded locks of other manufacturers.

The following illustration depicts other skeleton keys which can be manufactured.

Pin Tumbler & Wafer

The tools required to open these two types of locks are similar and will therefore be looked at together.

Unfortunately, contrary to popular belief, there are no skeleton keys which open this type of lock as is depeicted in films, but is more down to skill and practise.

Hook Picks

These are for the use of "pure picking" as will be explained in the techniques section.

All that is needed here is to understand what they are and to appreciate that they come in a variety of sizes.

Rakes come in a variety of shapes and sizes. The diagram below shows some fo the major ones.

Turning Tools/Tension Wrench

A vital requirement in the picking of these types of locks.

Bypass Pick

This tool can be easily made by grinding a hacksaw blade to a tapered point similar to that shown below.

Lifter Pick

The size of this will be determined by the lever lock to be picked.

Turning Tool/Tension Wrench

This tool, although has the same name as that used for pin and wafer locks, is very different in construction. Both by way of shape and strength of metal used. The reason for this will be discussed in the techniques section.

Combination Brief Case

Combination Probe

This type of probe doesn"t require the strength of the similar bypass pick used for pin tumbler locks but instead must be extremely thin.

This probe is for the sole purpose of opening brief case combination locks.

The probe which I constructed for this use was made from a feeler guage. In particular a number 12

Constructing Picks

Although picks are relatively cheap to purchase it can often be quite difficult obtaining them and finding outlets which will sell them to you.

This need not be a serious problem as adequate picks can be constructed by oneself with a little hardwork and patience.

In order to make a pick it is a vital requirement that you have a grinder, as files will not shape the metal required to make a strong and long lasting pick.

This brings us to an important issue, i.e., what metal should be used and where can it be obtained? Well for a pick to be of any use it must fulfil two main criteria. It must be strong and it must also be thin, (to get past any fancy key hole which stands between you and the mechanism).

Such metal can easily be found in the form of hacksaw blades. These can be marked into the shape of picks by use of a permanent pen and can then be ground carefully to the shape required. This metal is extremely useful and can be used to construct the hook picks, rakes, bypass pick and lever lock lifter pick.

Feeler guage sets, as available from hardware or automobile shops, also provide metal which can be used. Although some of these are only useful for shims they can be used for picks or probes, (especially combination probe), depending on their strength and thickness.

The metal used for turning tools both for pintumbler/wafer and lever will vary. For a turning tool used to open a pin tumbler or wafer lock any springy metal which can be bent to the shape illustrated above, i.e, the straight forward simple turning tool type A, without the tool losing its shape will be sufficient.

The lever lock turning tool must be constructed from a much more rigid and stronger metal. It should be such that once in the required shape will not bend as it must be able to exert a strong turning force.



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

Хочется сказать что не обязательно делать отмычку по шаблону, скачанному с интернета. Можно придумать собственную, неповторимую модель. Нужно понимать как отмычка открывает замок, и при ее создании руководится именно этим.


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


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

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

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

наждак




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



так называемый гравер типа «Дримель», без него никак!



Дрель



и войлочный круг с пастой «ГОИ», не обязательно но рекомендуемо

Так же пару стандартных инструментов типа молоток, и т.д.

И конечно же два основных, без которых ничего не выйдет это:МОЗГИ
И РУКИ



желательно прямые! :)

Отмычки для вскрытия «Английских» цилиндров.



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



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

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




Для удобства на ручке использовал термо-усадочную трубку (продается на радио рынке).Впринцепи по цилиндрам пока все, потому, что это все есть в интернете.

Ключ-отмычка для «Тубулярных» замков.

Видео, где показывают этот ключ тоже море, но как его сделать из подручных материалов и нормально, я не встречал. Для изготовления нам понадобится любой тубулярный ключ, желательно с замком, я приобрел навесной фирмы «Lex» (какой был).



Также нам понадобится обойма скрепок из степлера канцелярских или строительных



Н а ключе нам надо выбрать семь пазов (если замок семи пиновый). Это можно сделать как «дремелом» так и в ручную плоским надфилем.



Главное чтоб продольные пазы были плоскими а не полукруглыми, и не переборщите с глубиной. Она должна быть не глубже той, что была. Далее мы отрезаем от обоймы скрепок по три, чтоб не рассыпались и перекусываем пополам. «Штыри» готовы. После этого надо выбрать радиальный паз для резинки, это делается для лучшего прижатия «штырей» скрепок. Саму резинку я отрезал от шланга диаметром 7мм (внутренний), 12мм (наружный) и толщиной 4мм. Размеры не обязательно соблюдать, важно что б «штыри» плотно были прижаты, можно даже использовать резинку для денег.

В конечном итоге у нас должен получится вот такай ключ-отмычка:



Ну а как им пользоваться думаю вы знаете, а если не знаете, ищите в интернете (там есть).

Отмычка-декодер для дисковых замков.

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

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



Идем дальше.




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



Родную ось мы меняем на подходящий прут диаметром 8мм, или даже болт диаметром 9мм с шляпкой, у которого зашлифовываем резьбу. Далее на конце сверлим два сквозных отверстия и нарезаем резьбу (я использовал резьбу диаметром 4мм) для зажатия и центровки манипулятора.
Для натяга можно взять трубку диаметром 8мм и срезать половину на длину в 2мм.Он должен зажиматься цангой спереди микрометра. Хотя я делал натяг из цельного куска прута со смещением центра, для чего думайте сами. В качестве манипулятора была использована стальная, упругая проволка диаметром 1.5мм, с конца чуть расклепана что б не проворачивалась.

Отмычки для сувальдных замков.

Я думаю многие видели отмычки для сувальдных, я имею в виду «фирменные». Так вот трубки я нашел 5мм и даже 4мм в диаметре, а как сам натяг, присандалить что б надежно было да еще и в домашних условиях так и не придумал, но тратить по 300$ тоже не хочеться. И я решил для пробы сделать такую отмычку:



Как видите в ней нет ничего сложного. По середине ключа выбран паз (фрезером с насадкой: обрезной диск) для манипулятора почти до центра. Манипулятор прикручен тонкой стальной проволкой дабы не выскочил из паза. Само собой оставлен натяг с минимальным подъемом. Эта отмычка проста и довольно надежна. Но хотелось более универсальной и эстетичной.



Тут использована такая же болванка ключа, с тем же пазом, но с чуть другим натягом без нулевого подъема. Заодно пошел в дело латунный шестигранник, валявшийся в сарае, в первом были просверлены отверстия: вдоль диаметр 5мм(для болванки) и поперек с резьбой диаметр 4мм(для болтов дабы зажать болванку). Во втором так же два отверстия: вдоль 2мм(для манипулятора) и поперек с резьбой диаметр 4мм(для болта, дабы зажать манипулятор) Эта отмычка считается универсальной, но требует немного больше навыков чем предыдущая.

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

Необходимо вмешательство менеджмента для выявления причин отклонений.

Для построения контрольной карты я использую исходные данные, среднее значение (μ) и стандартное отклонение (σ). В Excel: μ = СРЗНАЧ($F$3:$F$15), σ = СТАНДОТКЛОН($F$3:$F$15)

Сама контрольная карта включает: исходные данные, среднее значение (μ), нижнюю контрольную границу (μ – 2σ) и верхнюю контрольную границу (μ + 2σ):

Скачать заметку в формате , примеры в формате

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

Чтобы добавить линию тренду выделите на графике ряд с данными (в нашем примере – зеленые точки), кликните правой кнопкой мыши и выберите опцию «Добавить линию тренда». В открывшемся окне «Формат линии тренда», поэкспериментируйте с опциями. Я остановился на линейном тренде.

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

Линию тренда Excel позволяет построить с помощью функции ПРЕДСКАЗ. Нам потребуется дополнительный ряд А3:А15, чтобы известные значения Х были непрерывным рядом (номера кварталов такой непрерывный ряд не образуют). Вместо среднего значения в столбце Н вводим функцию ПРЕДСКАЗ:

Стандартное отклонение σ (функция СТАНДОТКЛОН в Excel) вычисляется по формуле:

К сожалению, я не нашел в Excel функции для такого определения стандартного отклонения (по отношению к тренду). Задачу можно решить с помощью формулы массива. Кто не знаком с формулами массива, предлагаю сначала почитать .

Формула массива может возвращать одно значение или массив. В нашем случае формула массива вернет одно значение:

Давайте подробнее изучим, как работает формула массива в ячейке G3

СУММ(($F$3:$F$15-$H$3:$H$15)^2) определяет сумму квадратов разностей; фактически формула считает следующую сумму = (F3 – H3) 2 + (F4 – H4) 2 + … + (F15 – H15) 2

СЧЁТЗ($F$3:$F$15) – число значений в диапазоне F3:F15

КОРЕНЬ(СУММ(($F$3:$F$15-$H$3:$H$15)^2)/(СЧЁТЗ($F$3:$F$15)-1)) = σ

Значение 6,2% есть точка нижней контрольной границы = 8,3% – 2 σ

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

H4 – 2*КОРЕНЬ(СУММ(($F$3:$F$15-$H$3:$H$15)^2)/(СЧЁТЗ($F$3:$F$15)-1))

необходимо нажать не Enter, а Ctrl + Shift + Enter. Не пытайтесь ввести фигурные скобки с клавиатуры – формула массива не заработает. Если требуется отредактировать формулу массива, сделайте это так же, как и с обычной формулой, но опять же по окончании редактирования нажмите не Enter, а Ctrl + Shift + Enter.

Формулу массива, возвращающую одно значение, можно «протаскивать», как и обычную формулу.

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

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

Вычислим в MS EXCEL дисперсию и стандартное отклонение выборки. Также вычислим дисперсию случайной величины, если известно ее распределение.

Сначала рассмотрим дисперсию , затем стандартное отклонение .

Дисперсия выборки

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

Все 3 формулы математически эквивалентны.

Из первой формулы видно, что дисперсия выборки это сумма квадратов отклонений каждого значения в массиве от среднего , деленная на размер выборки минус 1.

дисперсии выборки используется функция ДИСП() , англ. название VAR, т.е. VARiance. С версии MS EXCEL 2010 рекомендуется использовать ее аналог ДИСП.В() , англ. название VARS, т.е. Sample VARiance. Кроме того, начиная с версии MS EXCEL 2010 присутствует функция ДИСП.Г(), англ. название VARP, т.е. Population VARiance, которая вычисляет дисперсию для генеральной совокупности . Все отличие сводится к знаменателю: вместо n-1 как у ДИСП.В() , у ДИСП.Г() в знаменателе просто n. До MS EXCEL 2010 для вычисления дисперсии генеральной совокупности использовалась функция ДИСПР() .

Дисперсию выборки
=КВАДРОТКЛ(Выборка)/(СЧЁТ(Выборка)-1)
=(СУММКВ(Выборка)-СЧЁТ(Выборка)*СРЗНАЧ(Выборка)^2)/ (СЧЁТ(Выборка)-1) – обычная формула
=СУММ((Выборка -СРЗНАЧ(Выборка))^2)/ (СЧЁТ(Выборка)-1 ) –

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

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

Дисперсия случайной величины

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

Для дисперсии случайной величины Х часто используют обозначение Var(Х). Дисперсия равна квадрата отклонения от среднего E(X): Var(Х)=E[(X-E(X)) 2 ]

дисперсия вычисляется по формуле:

где x i – значение, которое может принимать случайная величина, а μ – среднее значение (), р(x) – вероятность, что случайная величина примет значение х.

Если случайная величина имеет , то дисперсия вычисляется по формуле:

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

Некоторые свойства дисперсии :

Var(Х+a)=Var(Х), где Х - случайная величина, а - константа.

Var(aХ)=a 2 Var(X)

Var(Х)=E[(X-E(X)) 2 ]=E=E(X 2)-E(2*X*E(X))+(E(X)) 2 =E(X 2)-2*E(X)*E(X)+(E(X)) 2 =E(X 2)-(E(X)) 2

Это свойство дисперсии используется в статье про линейную регрессию .

Var(Х+Y)=Var(Х) + Var(Y) + 2*Cov(Х;Y), где Х и Y - случайные величины, Cov(Х;Y) - ковариация этих случайных величин.

Если случайные величины независимы (independent), то их ковариация равна 0, и, следовательно, Var(Х+Y)=Var(Х)+Var(Y). Это свойство дисперсии используется при выводе .

Покажем, что для независимых величин Var(Х-Y)=Var(Х+Y). Действительно, Var(Х-Y)= Var(Х-Y)= Var(Х+(-Y))= Var(Х)+Var(-Y)= Var(Х)+Var(-Y)= Var(Х)+(-1) 2 Var(Y)= Var(Х)+Var(Y)= Var(Х+Y). Это свойство дисперсии используется для построения .

Стандартное отклонение выборки

Стандартное отклонение выборки - это мера того, насколько широко разбросаны значения в выборке относительно их .

По определению, стандартное отклонение равно квадратному корню из дисперсии :

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

Вычислим стандартное отклонение для 2-х выборок: (1; 5; 9) и (1001; 1005; 1009). В обоих случаях, s=4. Очевидно, что отношение величины стандартного отклонения к значениям массива у выборок существенно отличается. Для таких случаев используется Коэффициент вариации (Coefficient of Variation, CV) - отношение Стандартного отклонения к среднему арифметическому , выраженного в процентах.

В MS EXCEL 2007 и более ранних версиях для вычисления Стандартного отклонения выборки используется функция =СТАНДОТКЛОН() , англ. название STDEV, т.е. STandard DEViation. С версии MS EXCEL 2010 рекомендуется использовать ее аналог =СТАНДОТКЛОН.В() , англ. название STDEV.S, т.е. Sample STandard DEViation.

Кроме того, начиная с версии MS EXCEL 2010 присутствует функция СТАНДОТКЛОН.Г() , англ. название STDEV.P, т.е. Population STandard DEViation, которая вычисляет стандартное отклонение для генеральной совокупности . Все отличие сводится к знаменателю: вместо n-1 как у СТАНДОТКЛОН.В() , у СТАНДОТКЛОН.Г() в знаменателе просто n.

Стандартное отклонение можно также вычислить непосредственно по нижеуказанным формулам (см. файл примера )
=КОРЕНЬ(КВАДРОТКЛ(Выборка)/(СЧЁТ(Выборка)-1))
=КОРЕНЬ((СУММКВ(Выборка)-СЧЁТ(Выборка)*СРЗНАЧ(Выборка)^2)/(СЧЁТ(Выборка)-1))

Другие меры разброса

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

Функция СРОТКЛ() является также мерой разброса множества данных. Функция СРОТКЛ() вычисляет среднее абсолютных значений отклонений значений от среднего . Эта функция вернет тот же результат, что и формула =СУММПРОИЗВ(ABS(Выборка-СРЗНАЧ(Выборка)))/СЧЁТ(Выборка) , где Выборка - ссылка на диапазон, содержащий массив значений выборки.

Вычисления в функции СРОТКЛ () производятся по формуле: