Microsoft Excel Kullanarak Z Skoru Nasıl Hesaplanır
Z Skoru, belirli bir değerin tüm veri kümesinin ortalamasından kaç standart sapma olduğunu söyleyen istatistiksel bir değerdir. Verilerinizin ortalamasını ve standart sapmasını hesaplamak için ORTALAMA ve STDSAPMA.S veya STDSAPMA.P formüllerini kullanabilir ve ardından bu sonuçları her bir değerin Z-Puanını belirlemek için kullanabilirsiniz.
Z Puanı nedir ve ORTALAMA, STDSAPMA.S ve STDSAPMA.P işlevleri ne işe yarar?
Z Skoru, iki farklı veri kümesindeki değerleri karşılaştırmanın basit bir yoludur. Bir veri noktasının yattığı ortalamadan uzaktaki standart sapmaların sayısı olarak tanımlanır. Genel formül şuna benzer:
= (DataPoint-ORTALAMA (Veri Kümesi)) / STDSAPMA (Veri Kümesi)
İşte netleştirmeye yardımcı olacak bir örnek. Farklı öğretmenler tarafından öğretilen iki Cebir öğrencisinin test sonuçlarını karşılaştırmak istediğinizi varsayalım. İlk öğrencinin bir sınıfta final sınavından% 95 aldığını ve diğer sınıftaki öğrencinin% 87 puan aldığını biliyorsunuz.
İlk bakışta% 95 notu daha etkileyici, ama ya ikinci sınıfın öğretmeni daha zor bir sınav verdiyse? Her öğrencinin puanının Z-Puanını, her sınıftaki ortalama puanlara ve her sınıftaki puanların standart sapmasına göre hesaplayabilirsiniz. İki öğrencinin Z-Puanlarının karşılaştırılması,% 87 puan alan öğrencinin,% 98 puan alan öğrencinin sınıfının geri kalanına göre daha iyi performans gösterdiğini ortaya çıkarabilir.
İhtiyacınız olan ilk istatistiksel değer 'ortalama'dır ve Excel'in "ORTALAMA" işlevi bu değeri hesaplar. Yalnızca bir hücre aralığındaki tüm değerleri toplar ve bu toplamı sayısal değerler içeren hücre sayısına böler (boş hücreleri yok sayar).
İhtiyacımız olan diğer istatistiksel değer 'standart sapmadır' ve Excel'in standart sapmayı biraz farklı şekillerde hesaplamak için iki farklı işlevi vardır.
Excel'in önceki sürümlerinde yalnızca, verileri bir popülasyonun 'örneği' olarak ele alırken standart sapmayı hesaplayan "STDSAPMA" işlevi vardı. Excel 2010 bunu standart sapmayı hesaplayan iki işleve ayırdı:
- STDSAPMA: Bu işlev, önceki "STDSAPMA" işleviyle aynıdır. Verileri bir popülasyonun 'örneği' olarak ele alırken standart sapmayı hesaplar. Bir popülasyon örneği, bir araştırma projesi için toplanan belirli sivrisinekler veya bir kenara bırakılan ve çarpışma güvenliği testi için kullanılan arabalar gibi bir şey olabilir.
- STDSAPMA.P: Bu işlev, verileri tüm popülasyon olarak ele alırken standart sapmayı hesaplar. Bütün bir popülasyon, dünyadaki tüm sivrisinekler veya belirli bir modelin üretimindeki her araba gibi bir şey olacaktır.
Seçtiğiniz veri kümenize bağlıdır. Fark genellikle küçük olacaktır, ancak "STDSAPMA.P" işlevinin sonucu, aynı veri kümesi için "STDSAPMA.S" işlevinin sonucundan her zaman daha küçük olacaktır. Verilerde daha fazla değişkenlik olduğunu varsaymak daha muhafazakar bir yaklaşımdır.
Bir Örneğe Bakalım
Örneğimiz için, "ORTALAMA", "STDSAPMA" ve "STDSAPMA.P" işlevlerinin sonuçlarını depolamak için iki sütunumuz ("Değerler" ve "Z Skoru") ve üç "yardımcı" hücremiz var. "Değerler" sütunu, 500 civarında ortalanmış on rastgele sayı içerir ve "Z-Puanı" sütunu, "yardımcı" hücrelerde depolanan sonuçları kullanarak Z-Puanını hesaplayacağımız yerdir.
İlk olarak, "ORTALAMA" işlevini kullanarak değerlerin ortalamasını hesaplayacağız. "ORTALAMA" işlevinin sonucunu kaydedeceğiniz hücreyi seçin.
Aşağıdaki formülü ve basında Tipi girmek -veya- “Formüller” menüsünü kullanın.
= ORTALAMA (E2: E13)
İşleve "Formüller" menüsünden erişmek için, "Diğer İşlevler" açılır menüsünü seçin, "İstatistiksel" seçeneğini seçin ve ardından "ORTALAMA" yı tıklayın.
İşlev Bağımsız Değişkenleri penceresinde, "Sayı1" alanı için girdi olarak "Değerler" sütunundaki tüm hücreleri seçin. "Sayı2" alanı için endişelenmenize gerek yok.
Şimdi "Tamam" a basın.
Daha sonra, "STDSAPMA.S" veya "STDSAPMA.P" işlevini kullanarak değerlerin standart sapmasını hesaplamamız gerekir. Bu örnekte, "STDSAPMA" ile başlayarak her iki değeri de nasıl hesaplayacağınızı göstereceğiz. Sonucun depolanacağı hücreyi seçin.
Standart sapmayı "STDSAPMA" işlevini kullanarak hesaplamak için, bu formülü yazın ve Enter tuşuna basın (veya "Formüller" menüsünden erişin).
= STDSAPMA.S (E3: E12)
İşleve "Formüller" menüsünden erişmek için, "Diğer İşlevler" açılır menüsünü seçin, "İstatistiksel" seçeneğini seçin, biraz aşağı kaydırın ve ardından "STDEV.S" komutunu tıklayın.
İşlev Bağımsız Değişkenleri penceresinde, "Sayı1" alanı için girdi olarak "Değerler" sütunundaki tüm hücreleri seçin. Buradaki "Sayı2" alanı için de endişelenmenize gerek yok.
Şimdi "Tamam" a basın.
Ardından, “STDSAPMA.P” işlevini kullanarak standart sapmayı hesaplayacağız. Sonucun depolanacağı hücreyi seçin.
Standart sapmayı "STDSAPMA.P" işlevini kullanarak hesaplamak için, bu formülü yazın ve Enter tuşuna basın (veya "Formüller" menüsünden erişin).
= STDSAPMA.P (E3: E12)
İşleve "Formüller" menüsünden erişmek için, "Diğer İşlevler" açılır menüsünü seçin, "İstatistiksel" seçeneğini seçin, biraz aşağı kaydırın ve ardından "STDSAPMA.P" formülünü tıklayın.
İşlev Bağımsız Değişkenleri penceresinde, "Sayı1" alanı için girdi olarak "Değerler" sütunundaki tüm hücreleri seçin. Yine, "Sayı2" alanı için endişelenmenize gerek kalmayacak.
Şimdi "Tamam" a basın.
Artık verilerimizin ortalamasını ve standart sapmasını hesapladığımıza göre, Z-Skorunu hesaplamak için ihtiyacımız olan her şeye sahibiz. "ORTALAMA" ve "STDSAPMA" veya "STDSAPMA.P" işlevlerinin sonuçlarını içeren hücrelere başvuran basit bir formül kullanabiliriz.
"Z-Puanı" sütunundaki ilk hücreyi seçin. Bu örnek için "STDSAPMA.S" işlevinin sonucunu kullanacağız, ancak "STDSAPMA.P" sonucunu da kullanabilirsiniz.
Aşağıdaki formülü yazın ve Enter tuşuna basın:
= (E3- $ G $ 3) / $ H $ 3
Alternatif olarak, formülü yazmak yerine girmek için aşağıdaki adımları kullanabilirsiniz:
- F3 hücresine tıklayın ve yazın
=( - E3 hücresini seçin. ( Sol ok tuşuna bir kez basabilir veya fareyi kullanabilirsiniz)
- Eksi işaretini yazın
- - G3 hücresini seçin ve hücreye 'mutlak' bir referans yapmak üzere "$" karakterlerini eklemek için F4'e basın ("G3"> " $ G $ 3 ″>" G $ 3 ″> " $ G3 ″> F4'e basmaya devam ederseniz "G3" )
- Tür
)/ - H3 hücresini seçin (veya “STDEV.P” kullanıyorsanız I3) ve iki “$” karakter eklemek için F4 tuşuna basın .
- Enter tuşuna basın
Z-Skoru ilk değer için hesaplanmıştır. Ortalamanın altında 0.15945 standart sapmadır. Sonuçları kontrol etmek için, standart sapmayı bu sonuçla (6.271629 * -0.15945) çarpabilir ve sonucun değer ile ortalama (499-500) arasındaki farka eşit olup olmadığını kontrol edebilirsiniz. Her iki sonuç da eşittir, bu nedenle değer anlamlıdır.
Geri kalan değerlerin Z-Skorlarını hesaplayalım. Formülü içeren hücreden başlayarak tüm 'Z Skoru' sütununu vurgulayın.
Üst hücredeki formülü diğer tüm seçili hücreler boyunca kopyalayan Ctrl + D tuşlarına basın.
Artık formül tüm hücrelere 'doldurulmuştur' ve her biri, "$" karakterlerinden dolayı her zaman doğru "ORTALAMA" ve "STDSAPMA" veya "STDSAPMA.P" hücrelerine başvuracaktır. Hata alırsanız geri dönün ve girdiğiniz formüle "$" karakterlerinin eklendiğinden emin olun.
'Yardımcı' Hücreleri Kullanmadan Z-Skorunu Hesaplama
Yardımcı hücreler "ORTALAMA", "STDSAPMA" ve "STDSAPMA.P" işlevlerinin sonuçlarını depolayanlar gibi bir sonucu depolar. Yararlı olabilirler ancak her zaman gerekli değildirler. Bunun yerine aşağıdaki genelleştirilmiş formülleri kullanarak bir Z-Skoru hesaplarken bunları tamamen atlayabilirsiniz.
İşte "STDEV.S" işlevini kullanan biri:
= (Değer-ORTALAMA (Değerler)) / STDSAPMA.S (Değerler)
Ve "STEV.P" işlevini kullanan:
= (Değer-ORTALAMA (Değerler)) / STDSAPMA.P (Değerler)
İşlevlerde "Değerler" için hücre aralıklarını girerken, mutlak referanslar (F4 kullanarak "$") eklediğinizden emin olun, böylece "doldururken" farklı bir aralığın ortalamasını veya standart sapmasını hesaplamazsınız her formülde hücre sayısı.
Büyük bir veri kümeniz varsa, yardımcı hücreleri kullanmak daha verimli olabilir çünkü her seferinde "ORTALAMA" ve "STDSAPMA" veya "STDSAPMA.P" işlevlerinin sonucunu hesaplamayarak işlemci kaynaklarından ve sonuçları hesaplamak için gereken süreyi hızlandırmak.
Ayrıca, "$ G $ 3", "ORTALAMA ($ E $ 3: $ E $ 12)" den daha az bayt depolar ve daha az RAM alır. Bu önemlidir çünkü Excel'in standart 32 bit sürümü 2 GB RAM ile sınırlıdır (64 bit sürümde ne kadar RAM kullanılabileceği konusunda herhangi bir sınırlama yoktur).
