Microsoft Excel Kullanarak Z Puanı Nasıl Hesaplanır?

Z-Score, belirli bir değerin tüm veri kümesinin 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 daha sonra her bir değerin Z-Skorunu belirlemek için bu sonuçları kullanabilirsiniz.

Z-Puanı nedir ve ORTALAMA, STDEV.S ve STDEV.P işlevleri nelerdir?

Z-Score, iki farklı veri setindeki değerleri karşılaştırmanın basit bir yoludur. Bir veri noktasının yattığı ortalamadan uzaktaki standart sapma sayısı olarak tanımlanır. Genel formül şöyle görünür:

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

İş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ında% 95, diğer sınıfta ise% 87 puan aldığını biliyorsunuz.

İlk bakışta,% 95 notu daha etkileyicidir, ancak ya ikinci sınıf öğretmeni daha zor bir sınav verdiyse? Her bir öğrencinin puanının Z-Skorunu, her bir sınıftaki ortalama puanlara ve her bir sınıftaki puanların standart sapmasına dayanarak hesaplayabilirsiniz. İki öğrencinin Z puanlarının karşılaştırılması,% 87 puan alan öğrencinin sınıfının geri kalanına göre% 98 puan alan öğrenciden daha iyi olduğunu ortaya koymuştur.

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

İhtiyacımız olan diğer istatistiksel değer ise “standart sapma” dır ve Excel standart sapmayı biraz farklı şekillerde hesaplamak için iki farklı fonksiyona sahiptir.

Excel'in önceki sürümlerinde yalnızca verileri bir popülasyonun 'örneği' olarak değerlendirirken standart sapmayı hesaplayan “STDEV” işlevi vardı. Excel 2010 bunu standart sapmayı hesaplayan iki işleve ayırdı:

  • STDEV.S: Bu fonksiyon önceki “STDEV” fonksiyonuyla aynıdır. Verileri bir popülasyonun 'örneği' olarak ele alırken standart sapmayı hesaplar. Bir nüfusun bir örneği, bir araştırma projesi için toplanan belirli sivrisinekler veya çarpma güvenliği testleri için bir kenara konan ve kullanılan arabalar gibi bir şey olabilir.
  • STDEV.P: Bu fonksiyon, verileri tüm popülasyon olarak değerlendirirken standart sapmayı hesaplar. Tüm bir popülasyon, dünyadaki tüm sivrisinekler veya belirli bir modelin yapımındaki her araba gibi bir şey olacaktır.

Seçtiğiniz veri kümenize dayanı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 muhafazakar bir yaklaşımdır.

Bir örneğe bakalım

Örneğimiz için, “ORTALAMA”, “STDEV.S” ve “STDEV.P” fonksiyonlarının 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 rasgele sayı içerir ve “Z-Score” sütunu, 'yardımcı' hücrelerde depolanan sonuçları kullanarak Z-Puanını hesaplayacağımız yerdir.

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

Aşağıdaki formülü girin ve enter tuşuna basın veya “Formüller” menüsünü kullanın.

=AVERAGE(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” seçeneğini tıklayın.

İşlev Bağımsız Değişkenleri penceresinde, “Değerler” sütunundaki hücrelerin tümünü “Sayı1” alanı için girdi olarak seçin. “Number2” alanı için endişelenmenize gerek yok.

Şimdi “Tamam” a basın.

Daha sonra, “STDEV.S” veya “STDEV.P” işlevini kullanarak değerlerin standart sapmasını hesaplamamız gerekir. Bu örnekte, her iki değeri de “STDEV.S” ile başlayarak nasıl hesaplayacağınızı göstereceğiz. Sonucun kaydedileceği hücreyi seçin.

“STDEV.S” işlevini kullanarak standart sapmayı hesaplamak için bu formülü yazıp Enter tuşuna basın (veya “Formüller” menüsünden erişin).

=STDEV.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 sonra “STDEV.S” komutunu tıklayın.

İşlev Bağımsız Değişkenleri penceresinde, “Değerler” sütunundaki hücrelerin tümünü “Sayı1” alanı için girdi olarak seçin. Buradaki “Sayı2” alanı için de endişelenmenize gerek yok.

Şimdi “Tamam” a basın.

Daha sonra “STDEV.P” fonksiyonunu kullanarak standart sapmayı hesaplayacağız. Sonucun kaydedileceği hücreyi seçin.

“STDEV.P” işlevini kullanarak standart sapmayı hesaplamak için bu formülü yazıp Enter tuşuna basın (veya “Formüller” menüsünden erişin).

= STDEV.P (E3: E 12)

İş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ünü tıklayın.

İşlev Bağımsız Değişkenleri penceresinde, “Değerler” sütunundaki hücrelerin tümünü “Sayı1” alanı için girdi olarak seçin. Yine, “2 Numara” alanı için endişelenmenize gerek yok.

Şimdi “Tamam” a basın.

Artık verilerimizin ortalama ve standart sapmasını hesapladığımızda, Z-Score'u hesaplamak için ihtiyacımız olan her şeye sahibiz. “ORTALAMA” ve “STDEV.S” veya “STDEV.P” fonksiyonlarının sonuçlarını içeren hücrelere başvuran basit bir formül kullanabiliriz.

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

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

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

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

  1. F3 hücresini 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. Hücre için 'mutlak' bir referans yapmak üzere “$” karakterlerini eklemek için G4 hücresini seçin ve ardından F4 tuşuna basın (“G3”> “ $ G $ 3 ″>“ G $ 3 ″> “ $ G3 ″> F4’e basmaya devam ederseniz “G3”
  5. Tür )/
  6. H3 hücresini seçin (veya “STDEV.P” kullanıyorsanız I3) ve iki “$” karakterini eklemek için F4 tuşuna basın.
  7. Enter'a bas

Z-Score ilk değer için hesaplandı. Bu, ortalamanın altında 0.15945 standart sapmadır. Sonuçları kontrol etmek için, standart sapmayı bu sonuçla çarpın (6.271629 * -0.15945) 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-Score' sütununu vurgulayın.

Üstteki hücredeki formülü seçilen diğer tüm hücrelere kopyalayan Ctrl + D tuşlarına basın.

Şimdi formül, tüm hücrelere 'dolduruldu' ve her biri “$” karakterleri nedeniyle her zaman “ORTALAMA” ve “STDEV.S” veya “STDEV.P” hücrelerine referans verecek. Hata alırsanız geri dönün ve “$” karakterlerinin girdiğiniz formüle dahil edildiğinden 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 ancak her zaman gerekli değildir. Bir Z-Skoru hesaplarken aşağıdaki genelleştirilmiş formülleri kullanarak bunları tamamen atlayabilirsiniz.

İşte “STDEV.S” işlevini kullananlardan biri:

=(Value-AVERAGE(Values))/STDEV.S(Values)

Ve bir "STEV.P" işlevini kullanarak:

=(Value-AVERAGE(Values))/STDEV.P(Values)

İşlevlerdeki “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 ortalama veya standart sapmasını hesaplamıyorsunuzdur 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ını korur ve sonuçları hesaplamak için harcanan zamanı hızlandırın.

Ayrıca, “$ G $ 3”, depolamak için daha az bayt alır ve “ORTALAMA ($ E $ 3: $ E $ 12). Bu, Excel'in standart 32 bit sürümü 2 GB RAM ile sınırlandırıldığından önemlidir (64 bit sürümde RAM'in ne kadar kullanılabileceği konusunda herhangi bir sınırlama yoktur).