Excel'de standart sapma nasıl hesaplanır? Rastgele bir değişkenin varyansı

İstatistikler çok sayıda gösterge kullanır ve bunlardan biri Excel'deki varyansın hesaplanmasıdır. Bunu kendiniz manuel olarak yaparsanız çok zaman alır ve birçok hata yapabilirsiniz. Bugün matematiksel formülleri nasıl parçalara ayıracağımıza bakacağız. basit işlevler. Her şeyi birkaç dakika içinde yapmanızı sağlayacak en basit, en hızlı ve en uygun hesaplama yöntemlerinden bazılarına göz atalım.

Varyansı hesapla

Varyans rastgele değişken bir rastgele değişkenin matematiksel beklentisinden sapmasının karesinin matematiksel beklentisidir.

Genel nüfusa göre hesaplıyoruz

Matı hesaplamak için. Programın DISP.G işlevini kullanması bekleniyor ve sözdizimi şu şekilde görünüyor: “=DISP.G(Sayı1;Sayı2;…)”.

En fazla 255 bağımsız değişken kullanılabilir, daha fazla değil. Argümanlar olabilir asal sayılar veya belirtildikleri hücrelere bağlantılar. Microsoft Excel'de varyansın nasıl hesaplanacağına bakalım:

1. İlk adım, hesaplama sonucunun görüntüleneceği hücreyi seçmek ve ardından "Fonksiyon Ekle" düğmesine tıklamaktır.

2. İşlev yönetimi kabuğu açılacaktır. Orada “İstatistiksel” veya “Tam alfabetik liste” kategorisinde olabilecek “DISP.G” işlevini aramanız gerekir. Bulduğunda onu seçin ve “Tamam”a tıklayın.

3. Fonksiyon argümanlarını içeren bir pencere açılacaktır. İçinde “Numara 1” satırını seçmeniz ve sayfada sayı serisini içeren hücre aralığını seçmeniz gerekir.

4. Bundan sonra hesaplama sonuçları fonksiyonun girildiği hücrede görüntülenecektir.

Excel'de varyansı bu şekilde kolayca bulabilirsiniz.

Örneğe göre hesaplamalar yapıyoruz

Bu durumda Excel'deki örnek varyansı paydayla hesaplanır. toplam sayı sayılar, ama bir eksik. Bu, sözdizimi =DISP.V(Sayı1;Sayı2;...) olan DISP.V özel işlevi kullanılarak daha küçük bir hata için yapılır. Eylem algoritması:

• Önceki yöntemde olduğu gibi sonuç için bir hücre seçmeniz gerekir.
• İşlev Sihirbazında “Tam Alfabetik Liste” veya “İstatistiksel” kategorisi altında “DISP.B”yi bulmalısınız.

• Daha sonra bir pencere açılacak ve önceki yöntemdeki gibi ilerlemelisiniz.

Video: Excel'de varyansı hesaplama

Çözüm

Excel'de varyans, manuel olarak yapmaktan çok daha basit, çok daha hızlı ve daha rahat hesaplanır, çünkü matematiksel beklenti işlevi oldukça karmaşıktır ve hesaplanması çok fazla zaman ve çaba gerektirebilir.

Tünaydın

Bu yazımda STANDART DEVAL fonksiyonunu kullanarak Excel'de standart sapmanın nasıl çalıştığına bakmaya karar verdim. Uzun zamandır açıklama yapmadım veya yorum yapmadım, ayrıca bu, çalışanlar için çok yararlı bir işlev olduğu için yüksek matematik. Ve öğrencilere yardım etmek kutsaldır; bu konuda ustalaşmanın ne kadar zor olduğunu kendi deneyimlerimden biliyorum. Gerçekte standart sapma fonksiyonları, satılan ürünlerin istikrarını belirlemek, fiyatları oluşturmak, ürün çeşitlerini ayarlamak veya oluşturmak ve satışlarınıza ilişkin eşit derecede yararlı diğer analizler yapmak için kullanılabilir.

Excel bu varyans fonksiyonunun çeşitli varyasyonlarını kullanır:

Matematik teorisi

İlk olarak, bir fonksiyonun matematik dilinde nasıl tanımlanabileceğine dair teori hakkında biraz bilgi verelim standart sapma Excel'de kullanmak için, örneğin satış istatistikleri verilerini analiz etmek için, ancak daha sonra buna daha fazla değinmek için. Hemen uyarıyorum, bir sürü anlaşılmaz kelime yazacağım...)))), metinde aşağıda bir şey varsa hemen bakın pratik uygulama programda.

Standart sapma tam olarak ne işe yarar? Varyansının tarafsız bir tahminine dayanarak, bir X rastgele değişkeninin matematiksel beklentisine göre standart sapmasını tahmin eder. Kabul ediyorum, kafa karıştırıcı gelebilir ama öğrencilerin aslında neden bahsettiğimizi anlayacaklarını düşünüyorum!

Öncelikle "standart sapmayı" belirlememiz gerekiyor, daha sonra "standart sapmayı" hesaplamak için formül bize bu konuda yardımcı olacaktır: Formül şu şekilde açıklanabilir: Rastgele bir değişkenin ölçümleriyle aynı birimlerde ölçülecektir ve standart aritmetik ortalama hata hesaplanırken, güven aralıkları oluşturulurken, istatistik hipotezleri test edilirken veya doğrusal bir analiz yapılırken kullanılır. bağımsız değişkenler arasındaki ilişki. Fonksiyon şu şekilde tanımlanır: karekök bağımsız değişkenlerin varyansından.

Artık tanımlayabiliriz ve standart sapma bir rastgele değişken X'in standart sapmasının, varyansının tarafsız bir tahminine dayanan matematiksel perspektifine göre analizidir. Formül şu şekilde yazılmıştır:
Her iki tahminin de taraflı olduğunu belirtmek isterim. Genel durumlarda tarafsız bir tahmin yapmak mümkün değildir. Ancak tarafsız varyansın tahminine dayanan bir tahmin tutarlı olacaktır.

Excel'de pratik uygulama

O halde artık sıkıcı teoriden uzaklaşalım ve STANDART DEVAL fonksiyonunun nasıl çalıştığını pratikte görelim. Excel'deki standart sapma fonksiyonunun tüm varyasyonlarını dikkate almayacağım; bir tanesi yeterli, ancak örneklerde. Örnek olarak satış istikrarı istatistiklerinin nasıl belirlendiğine bakalım.

Öncelikle fonksiyonun yazılışına bakın, gördüğünüz gibi çok basit:

STANDART SAPMA.Г(_sayı1_;_sayı2_; ….), burada:

Şimdi örnek bir dosya oluşturalım ve buna dayanarak bu işlevin nasıl çalıştığını düşünelim. Analitik hesaplamalar yapmak için en az üç değerin kullanılması gerektiğinden, prensip olarak herhangi bir istatistiksel analizde olduğu gibi, şartlı olarak 3 dönem aldım, bu bir yıl, bir çeyrek, bir ay veya bir hafta olabilir. Benim durumumda - bir ay. Maksimum güvenilirlik için, mümkün olduğu kadar çok, ancak üçten az olmamak üzere süre almanızı öneririm. Tablodaki tüm veriler, formülün işleyişinin ve işlevselliğinin netliği açısından çok basittir.

Öncelikle aylık ortalama değeri hesaplamamız gerekiyor. Bunun için ORTALAMA fonksiyonunu kullanacağız ve şu formülü elde edeceğiz: = ORTALAMA(C4:E4).
Artık aslında her dönem için ürünün satışlarına girmemiz gereken değerinde STANDARDEVAL.G fonksiyonunu kullanarak standart sapmayı bulabiliriz. Sonuç şu biçimde bir formül olacaktır: =STANDART SAPMA.Г(C4;D4;E4).
Neyse işin yarısı tamamlandı. Bir sonraki adım, ortalama değere, standart sapmaya bölünüp sonucun yüzdeye dönüştürülmesiyle elde edilen “Varyasyon”u oluşturmaktır. Aşağıdaki tabloyu alıyoruz:
Temel hesaplamalar tamamlandı, geriye kalan tek şey satışların istikrarlı olup olmadığını anlamak. %10'luk sapmaların istikrarlı kabul edildiğini, %10'dan %25'e kadar olanların küçük sapmalar olduğunu, ancak %25'in üzerindeki herhangi bir şeyin artık istikrarlı olmadığını varsayalım. Koşullara göre sonuç elde etmek için mantıksal bir sonuç kullanacağız ve sonucu elde etmek için formülü yazacağız:

EĞER(H4<0,1;"стабильно";ЕСЛИ(H4<0,25;"нормально";"не стабильно"))

Tüm aralıklar netlik sağlamak amacıyla alınmıştır; görevleriniz tamamen farklı koşullara sahip olabilir.
Veri görselleştirmesini geliştirmek için, tablonuzda binlerce konum olduğunda, ihtiyaç duyduğunuz belirli koşulları uygulama fırsatından yararlanmalısınız veya belirli seçenekleri bir renk şemasıyla vurgulamak için kullanmalısınız, bu çok açık olacaktır.

Öncelikle koşullu biçimlendirme uygulayacaklarınızı seçin. “Ana Sayfa” kontrol panelinde “Koşullu Biçimlendirme”yi seçin ve açılır menüden “Hücreleri vurgulama kuralları”nı seçin ve ardından “Metin içerir...” menü öğesini tıklayın. Koşullarınızı gireceğiniz bir iletişim kutusu görüntülenir.

Koşulları yazdıktan sonra örneğin “sabit” - yeşil, “normal” - sarı ve “kararsız” - kırmızı olarak karşımıza ilk olarak neye dikkat etmeniz gerektiğini görebileceğiniz güzel ve anlaşılır bir tablo çıkıyor.

STDSAPMA.Y işlevi için VBA'yı kullanma

İlgilenen herkes makroları kullanarak hesaplamalarını otomatikleştirebilir ve aşağıdaki işlevi kullanabilir:

Fonksiyon MyStDevP(Arr) Dim x, aCnt&, aSum#, aAver#, tmp# Her x İçin Arr'da aSum = aSum + x "dizi elemanlarının toplamını hesapla aCnt = aCnt + 1 "eleman sayısını hesapla Sonraki x aAver = aSum / aCnt "her biri için ortalama değer x In Arr tmp = tmp + (x - aAver) ^ 2 "dizi elemanları ile ortalama değer arasındaki farkın karelerinin toplamını hesaplar Next x MyStDevP = Sqr(tmp / aCnt) ) "STANDARDEV.G() Son Fonksiyonunu hesapla

İşlev MyStDevP(Arr) Dim x , aCnt & , aSum #, aAver#, tmp# Her x için Arr'da aToplam = aToplam + x "dizi öğelerinin toplamını hesapla

İstatistikte kullanılan birçok gösterge arasında varyans hesaplamasını vurgulamak gerekir. Bu hesaplamayı manuel olarak yapmanın oldukça sıkıcı bir iş olduğunu belirtmek gerekir. Neyse ki Excel, hesaplama prosedürünü otomatikleştirmenize olanak tanıyan işlevlere sahiptir. Bu araçlarla çalışmanın algoritmasını bulalım.

Dağılım, matematiksel beklentiden sapmaların ortalama karesi olan varyasyonun bir göstergesidir. Böylece sayıların ortalama değer etrafındaki yayılımını ifade eder. Varyansın hesaplanması hem genel popülasyon hem de örneklem için yapılabilir.

Yöntem 1: nüfusa dayalı hesaplama

Bu göstergeyi genel nüfus için Excel'de hesaplamak için işlevi kullanın. DISP.G. Bu ifadenin sözdizimi aşağıdaki gibidir:

DISP.G(Sayı1;Sayı2;…)

Toplamda 1'den 255'e kadar argüman kullanılabilir. Bağımsız değişkenler sayısal değerler olabileceği gibi içerdikleri hücrelere referanslar da olabilir.

Sayısal verilerle bir aralık için bu değerin nasıl hesaplanacağını görelim.

Yöntem 2: numuneye göre hesaplama

Popülasyona dayalı bir değer hesaplamanın aksine, bir örneklem hesaplanırken payda, sayıların toplam sayısını değil, bir eksiğini gösterir. Bu, hata düzeltme amacıyla yapılır. Excel, bu tür hesaplamalar için tasarlanmış özel bir işlev olan DISP.V'de bu nüansı dikkate alır. Sözdizimi aşağıdaki formülle temsil edilir:

DISP.B(Sayı1;Sayı2;…)

Önceki fonksiyonda olduğu gibi argüman sayısı da 1 ile 255 arasında değişebilir.

Gördüğünüz gibi Excel programı varyansın hesaplanmasını büyük ölçüde kolaylaştırabilir. Bu istatistik uygulama tarafından popülasyondan veya örneklemden hesaplanabilir. Bu durumda, tüm kullanıcı eylemleri aslında işlenecek sayı aralığının belirlenmesine indirgenir ve asıl işi Excel kendisi yapar. Tabii ki, bu önemli miktarda kullanıcı zamanından tasarruf sağlayacaktır.

İstatistiksel analizin ana araçlarından biri standart sapmanın hesaplanmasıdır. Bu gösterge, bir numunenin veya popülasyonun standart sapmasını tahmin etmenize olanak tanır. Excel'de standart sapma formülünün nasıl kullanılacağını öğrenelim.

Hemen standart sapmanın ne olduğunu ve formülünün nasıl göründüğünü belirleyelim. Bu miktar, serideki tüm miktarlar ile bunların aritmetik ortalaması arasındaki farkın karelerinin aritmetik ortalamasının kareköküdür. Bu göstergenin aynı adı vardır - standart sapma. Her iki isim de tamamen eşdeğerdir.

Ancak doğal olarak Excel'de kullanıcının bunu hesaplaması gerekmez, çünkü program onun için her şeyi yapar. Excel'de standart sapmanın nasıl hesaplanacağını öğrenelim.

Excel'de hesaplama

Belirtilen değeri Excel'de iki özel işlevi kullanarak hesaplayabilirsiniz. STDSAPMA.V(örnek popülasyona dayalı olarak) ve STDEV.G(genel nüfusa göre). Çalışma prensibi kesinlikle aynıdır, ancak aşağıda tartışacağımız üç şekilde çağrılabilirler.

Yöntem 1: İşlev Sihirbazı

Yöntem 2: Formüller Sekmesi

Yöntem 3: Formülü el ile girme

Ayrıca argümanlar penceresini hiç çağırmanıza gerek kalmayacak bir yol da var. Bunu yapmak için formülü manuel olarak girmeniz gerekir.

Gördüğünüz gibi Excel'de standart sapmayı hesaplama mekanizması çok basittir. Kullanıcının yalnızca popülasyondaki sayıları veya bunları içeren hücrelere yapılan referansları girmesi gerekir. Tüm hesaplamalar programın kendisi tarafından gerçekleştirilir. Hesaplanan göstergenin ne olduğunu ve hesaplama sonuçlarının pratikte nasıl uygulanabileceğini anlamak çok daha zordur. Ancak bunu anlamak zaten yazılımla çalışmayı öğrenmekten çok istatistik alanıyla ilgilidir.

Excel'de ortalama değeri bulmak için (sayısal, metin, yüzde veya başka bir değer olması fark etmez) birçok işlev vardır. Ve her birinin kendine has özellikleri ve avantajları var. Aslında bu görevde belirli koşullar belirlenebilir.

Örneğin Excel'deki bir sayı serisinin ortalama değerleri istatistiksel işlevler kullanılarak hesaplanır. Ayrıca kendi formülünüzü manuel olarak da girebilirsiniz. Çeşitli seçenekleri düşünelim.

Sayıların aritmetik ortalaması nasıl bulunur?

Aritmetik ortalamayı bulmak için kümedeki tüm sayıları toplamanız ve toplamı miktara bölmeniz gerekir. Örneğin bir öğrencinin bilgisayar bilimleri notları: 3, 4, 3, 5, 5. Çeyreğe neler dahil: 4. Aritmetik ortalamayı şu formülü kullanarak bulduk: =(3+4+3+5+5) /5.

Excel işlevlerini kullanarak bunu hızlı bir şekilde nasıl yapabilirim? Örneğin bir dizedeki bir dizi rastgele sayıyı ele alalım:

Veya: aktif hücreyi oluşturun ve formülü manuel olarak girin: =ORTALAMA(A1:A8).

Şimdi ORTALAMA fonksiyonunun başka neler yapabileceğini görelim.

İlk iki ve son üç sayının aritmetik ortalamasını bulalım. Formül: =ORTALAMA(A1:B1;F1:H1). Sonuç:



Durum ortalaması

Aritmetik ortalamayı bulma koşulu sayısal bir kriter veya metin olabilir. =ORTALAMAEĞER() fonksiyonunu kullanacağız.

10'dan büyük veya ona eşit olan sayıların aritmetik ortalamasını bulun.

İşlev: =EĞERORTALAMA(A1:A8;">=10")

">=10" koşulu altında EĞERORTALAMA işlevini kullanmanın sonucu:

Üçüncü argüman – “Ortalama aralık” – atlanmıştır. Öncelikle buna gerek yok. İkinci olarak, program tarafından analiz edilen aralık SADECE sayısal değerleri içerir. İlk argümanda belirtilen hücreler, ikinci argümanda belirtilen koşula göre aranacaktır.

Dikkat! Arama kriteri hücrede belirtilebilir. Ve formülde buna bir bağlantı yapın.

Metin kriterini kullanarak sayıların ortalama değerini bulalım. Örneğin “masa” ürününün ortalama satışları.

İşlev şu şekilde görünecektir: =ORTALAMAEĞER($A$2:$A$12,A7,$B$2:$B$12). Aralık – ürün adlarını içeren bir sütun. Arama kriteri, "tablolar" kelimesini içeren bir hücreye bağlantıdır (A7 bağlantısı yerine "tablolar" kelimesini ekleyebilirsiniz). Ortalama aralığı – ortalama değeri hesaplamak için verilerin alınacağı hücreler.

Fonksiyonun hesaplanması sonucunda aşağıdaki değeri elde ederiz:

Dikkat! Bir metin kriteri (koşul) için ortalama aralığının belirtilmesi gerekir.

Excel'de ağırlıklı ortalama fiyat nasıl hesaplanır?

Ağırlıklı ortalama fiyatı nasıl öğrendik?

Formül: =TOPLAÇARP(C2:C12;B2:B12)/TOPLA(C2:C12).

SUMproduct formülünü kullanarak mal miktarının tamamını sattıktan sonra toplam geliri buluyoruz. SUM işlevi de malların miktarını özetler. Mal satışından elde edilen toplam geliri, toplam mal adedine bölerek ağırlıklı ortalama fiyatı bulduk. Bu gösterge her fiyatın “ağırlığını” dikkate alır. Toplam değerler kütlesindeki payı.

Standart sapma: Excel'deki formül

Genel popülasyon ve örneklem için standart sapmalar vardır. İlk durumda, bu genel varyansın köküdür. İkincisinde ise örneklem varyansından.

Bu istatistiksel göstergeyi hesaplamak için bir dağılım formülü derlenir. Kök ondan çıkarılır. Ancak Excel'de standart sapmayı bulmak için hazır bir işlev vardır.

Standart sapma kaynak verinin ölçeğine bağlıdır. Bu, analiz edilen aralığın varyasyonunun mecazi bir temsili için yeterli değildir. Göreli veri dağılımı düzeyini elde etmek için değişim katsayısı hesaplanır:

standart sapma / aritmetik ortalama

Excel'deki formül şuna benzer:

STDSAPMA (değer aralığı) / ORTALAMA (değer aralığı).

Değişim katsayısı yüzde olarak hesaplanır. Bu nedenle hücredeki yüzde formatını ayarladık.