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-Puanını belirlemek için bu sonuçları kullanabilirsiniz..

Z-puanı nedir ve ORTALAMA, STDEV.S ve STDEV.P işlevlerinin işlevi nedir?

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-ORTALAMA (veri kümesi)) / STDSAP (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ı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ş.

İ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 böler (boş hücreleri yok sayar).

İ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 fonksiyona böldü:

• 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 kaza güvenliği testi için bir kenara bırakılmış ve kullanılmış arabalar gibi bir şey olabilir..
• STDEV.P: Bu fonksiyon, verileri tüm popülasyon olarak kabul ederken 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 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” 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: 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 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 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)

“Formüller” menüsünden işleve 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.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 “Number2” 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 yazın = (
2. E3 hücresini seçin. (Tuşuna basabilirsiniz. sol ok anahtar bir kez veya fareyi kullanın)
3. Eksi işaretini yazın -
4. G3 hücresini seçin ve ardından F4 hücreye 'mutlak' bir başvuru yapmak için “$” karakterlerini eklemek (“G3”> “$G,$3 ">“ G$3 ">“$G3 ">“ G3 ”tuşuna basmaya devam ederseniz F4)
5. tip ) /
6. H3 hücresini seçin (veya “STDEV.P” kullanıyorsanız I3) ve düğmesine basın. F4 iki "$" karakteri eklemek için.
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ı olur.

Geri kalan değerlerin Z-Skorlarını hesaplayalım. Formülü içeren hücreden başlayarak tüm 'Z-Score' sütununu vurgula.

Üstteki hücrede bulunan formülü diğer tüm seçilen 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 genel formülleri kullanarak bunları tamamen atlayabilirsiniz..

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

= (Değere ORTALAMA (Değerler)) / STDEV.S (Değerler)

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

= (Değere ORTALAMA (Değerler)) / STDEV.P (Değerler)

İş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ırmak.

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)..