Microsoft Excel Kullanarak Z-Skoru Nasıl Hesaplanır

Z-Skoru, belirli bir değerin tüm veri setinin ortalamasından kaç standart sapma olduğunu size söyleyen istatistiksel bir değerdir. Verilerinizin ortalamasını ve standart sapmasını hesaplamak için ORTALAMA ve STDEV.S veya STDEV.P formüllerini kullanabilir ve ardından her bir değerin Z-Skorunu belirlemek için bu sonuçları kullanabilirsiniz.

Z-Skoru nedir ve ORTALAMA, STDEV.S ve STDEV.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 bulunduğu ortalamadan standart sapmaların sayısı olarak tanımlanır. Genel formül şöyle görünür:

 =(DataPoint-ORTALAMA(DataSet))/STDEV(DataSet)

İşte açıklığa kavuşturmak için bir örnek. Farklı öğretmenler tarafından öğretilen iki Cebir öğrencisinin test sonuçlarını karşılaştırmak istediğinizi varsayalım. Biliyorsunuz, birinci öğrenci bir sınıfta final sınavından %95 aldı ve diğer sınıftaki öğrenci %87 aldı.

İ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 dayalı olarak hesaplayabilirsiniz. İki öğrencinin Z-Skorlarının karşılaştırılması, %87 puan alan öğrencinin sınıfının geri kalanına kıyasla, %98 puan alan öğrenciden sınıfının geri kalanına kıyasla daha iyi olduğunu ortaya çıkarabilir.

İhtiyacınız olan ilk istatistiksel değer 'ortalama'dır ve Excel'in “ORTALAMA” işlevi bu değeri hesaplar. Bir hücre aralığındaki tüm değerleri toplar ve bu toplamı sayısal değerleri içeren hücre sayısına böler (boş hücreleri yok sayar).

İhtiyacımız olan diğer istatistiksel değer 'standart sapma'dı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 “STDEV” işlevi vardı. Excel 2010, bunu standart sapmayı hesaplayan iki işleve ayırdı:

• STDEV.S: Bu işlev, önceki "STDEV" işleviyle aynıdır. Verileri bir popülasyonun 'örneği' olarak ele alırken standart sapmayı hesaplar. Bir popülasyonun bir ö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.
• STDEV.P: Bu işlev, verileri popülasyonun tamamı olarak ele alırken standart sapmayı hesaplar. Bütün bir popülasyon, dünyadaki tüm sivrisinekler veya belirli bir modelin üretim sürecindeki her araba gibi bir şey olacaktır.

Hangisini seçtiğiniz, veri kümenize bağlıdır. Fark genellikle küçük olacaktır, ancak “STDEV.P” fonksiyonunun sonucu her zaman aynı veri seti için “STDEV.S” fonksiyonunun sonucundan daha küçük olacaktır. Verilerde daha fazla değişkenlik olduğunu varsaymak daha tutucu bir yaklaşımdır.

Bir Örneğe Bakalım

Örneğimiz için, “ORTALAMA”, “STDEV.S” ve “STDEV.P” işlevlerinin sonuçlarını depolamak için iki sütun (“Değerler” ve “Z-Skor”) ve üç “yardımcı” hücremiz var. "Değerler" sütunu, 500 civarında ortalanmış on rastgele sayı içerir ve "Z-Skoru" sütunu, 'yardımcı' hücrelerde saklanan sonuçları kullanarak Z-Skorunu hesaplayacağımız yerdir.

İlk olarak “ORTALAMA” fonksiyonunu kullanarak değerlerin ortalamasını hesaplayacağız. “ORTALAMA” fonksiyonunun sonucunu saklayacağınız hücreyi seçin.

Aşağıdaki formülü yazın ve enter'a basın -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” üzerine tıklayın.

İşlev Bağımsız Değişkenleri penceresinde, "Sayı1" alanı için giriş 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.

Ardından, “STDEV.S” veya “STDEV.P” fonksiyonunu kullanarak değerlerin standart sapmasını hesaplamamız gerekiyor. Bu örnekte, “STDEV.S” ile başlayarak her iki değeri de nasıl hesaplayacağınızı göstereceğiz. Sonucun saklanacağı hücreyi seçin.

“STDEV.S” fonksiyonunu kullanarak standart sapmayı hesaplamak için bu formülü yazın ve Enter'a basın (veya “Formüller” menüsünden ona erişin).

 =STDEV.S(E3:E12)

Fonksiyona “Formüller” menüsünden erişmek için “Diğer Fonksiyonlar” 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 giriş olarak "Değerler" sütunundaki tüm hücreleri seçin. Burada da “Sayı2” alanı için endişelenmenize gerek yok.

Şimdi "Tamam"a basın.

Ardından, “STDEV.P” fonksiyonunu kullanarak standart sapmayı hesaplayacağız. Sonucun saklanacağı hücreyi seçin.

“STDEV.P” fonksiyonunu kullanarak standart sapmayı hesaplamak için bu formülü yazın ve Enter'a basın (veya “Formüller” menüsünden ona erişin).

=STDEV.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 “STDEV.P” formülüne tıklayın.

İşlev Bağımsız Değişkenleri penceresinde, "Sayı1" alanı için giriş 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 “STDEV.S” veya “STDEV.P” işlevlerinin sonuçlarını içeren hücrelere başvuran basit bir formül kullanabiliriz.

“Z-Skoru” sütunundaki ilk hücreyi seçin. Bu örnek için “STDEV.S” işlevinin sonucunu kullanacağız, ancak “STDEV.P” sonucunu da kullanabilirsiniz.

Aşağıdaki formülü yazın ve Enter'a basın:

 =(E3-$G$3)/$H$3

Alternatif olarak, formülü girmek için yazmak yerine aşağıdaki adımları kullanabilirsiniz:

1. F3 hücresine tıklayın ve =(
2. E3 hücresini seçin. ( Sol ok tuşuna bir kez basabilir veya fareyi kullanabilirsiniz)
3. Eksi işaretini yazın -
4. G3 hücresini seçin, ardından hücreye 'mutlak' bir referans yapmak üzere “$” karakterlerini eklemek için F4'e basın (“G3” > “ $ G $ 3″ > “G $ 3″ > “ $ G3″ > F4 tuşuna basmaya devam ederseniz “G3”
5. Tip )/
6. H3 (veya "STDEV.P" kullanıyorsanız I3) hücresini seçin ve iki "$" karakterini eklemek için F4 tuşuna basın.
7. Enter'a bas

İlk değer için Z-Skoru hesaplanmıştır. Ortalamanın 0.15945 standart sapma altında. 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 olduğunu 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ü seçili diğer tüm hücrelere kopyalayan Ctrl+D tuşlarına basın.

Artık formül tüm hücrelere 'dolduruldu' ve her biri, "$" karakterleri nedeniyle her zaman doğru "ORTALAMA" ve "STDEV.S" veya "STDEV.P" hücrelerine başvuracaktır. Hata alırsanız geri dönün ve girdiğiniz formülde “$” karakterlerinin yer aldığından emin olun.

'Yardımcı' Hücreleri kullanmadan Z-Skorunu Hesaplama

Yardımcı hücreler, "ORTALAMA", "STDEV.S" ve "STDEV.P" işlevlerinin sonuçlarını depolayanlar gibi bir sonucu depolar. Yararlı olabilirler ama 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 bir tane:

 =(Değer-ORTALAMA(Değerler))/STDEV.S(Değerler)

Ve biri “STEV.P” işlevini kullanan:

 =(Değer-ORTALAMA(Değerler))/STDEV.P(Değerler)

İşlevlerde “Değerler” için hücre aralıklarını girerken, mutlak referanslar eklediğinizden emin olun (F4 kullanarak “$”), böylece 'doldururken' farklı bir aralığın ortalama veya standart sapmasını hesaplamazsınız. Her formüldeki hücrelerin sayısı.

Büyük bir veri kümeniz varsa, yardımcı hücreleri kullanmak daha verimli olabilir, çünkü her seferinde “ORTALAMA” ve “STDEV.S” veya “STDEV.P” işlevlerinin sonucunu hesaplamaz, işlemci kaynaklarından tasarruf sağlar ve sonuçları hesaplamak için gereken süreyi hızlandırır.

Ayrıca, "$G$3" depolamak için daha az bayt ve yüklemek için "ORTALAMA($E$3:$E$12)"den daha az RAM gerektirir. Bu önemlidir çünkü Excel'in standart 32 bit sürümü 2 GB RAM ile sınırlıdır (64 bit sürümün ne kadar RAM kullanılabileceği konusunda herhangi bir sınırlaması yoktur).