Excel'de Normal Dağılım Çan Eğrisi Nasıl Oluşturulur

Bu öğretici, Excel'in tüm sürümlerinde normal dağılım çan eğrisinin nasıl oluşturulacağını gösterecektir: 2007, 2010, 2013, 2016 ve 2022.

İstatistikte, bir çan eğrisi (standart normal dağılım veya Gauss eğrisi olarak da bilinir), verilerin belirli bir veri kümesindeki bir merkez değeri veya ortalama etrafında kümelenme eğilimini gösteren simetrik bir grafiktir.

Y ekseni, veri kümesinde meydana gelen belirli bir değerin göreceli olasılığını temsil ederken, x ekseni, çan şeklinde bir eğri oluşturmak için değerleri grafik üzerinde çizer, dolayısıyla adı.

Grafik, belirli bir değerin beklenen varyasyonun parçası olup olmadığını veya istatistiksel olarak anlamlı olup olmadığını analiz etmemize yardımcı olur ve bu nedenle daha yakından incelenmesi gerekir.

Excel'in sunabileceği yerleşik çözümler olmadığından, bunu kendiniz çizmeniz gerekecek. Bu nedenle, yalnızca birkaç tıklamayla gelişmiş Excel çizelgeleri oluşturmanıza olanak tanıyan bir araç olan Grafik Oluşturucu Eklentisini geliştirdik.

Bu adım adım öğreticide, Excel'de sıfırdan bir normal dağılım çan eğrisi oluşturmayı öğreneceksiniz:

Bir Gauss eğrisi çizmek için iki şeyi bilmeniz gerekir:

• Ortalama (standart ölçüm olarak da bilinir). Bu, eğrinin merkezini belirler ve bu da eğrinin konumunu karakterize eder.
• Standart sapma (SD) ölçümlerden. Bu, verilerinizin normal dağılımdaki dağılımını veya düz İngilizce olarak eğrinin ne kadar geniş olması gerektiğini tanımlar. Örneğin, yukarıda gösterilen çan eğrisinde, ortalamanın bir standart sapması, 53 ile 85 arasındaki sınav puanları arasındaki aralığı temsil eder.

SD ne kadar düşükse, eğri o kadar uzun olur ve verileriniz o kadar az yayılır ve bunun tersi de geçerlidir.

Herhangi bir normal dağılım eğrisine uygulanabilecek 68-95-99.7 kuralından bahsetmeye değer, yani verilerinizin kabaca %68'i ortalamadan bir SD uzağa, %95'i iki SD içine ve %99.7'si içine yerleştirilecektir. üç SD.

Artık temelleri bildiğinize göre, teoriden pratiğe geçelim.

Başlarken

Örnekleme amacıyla, 200 öğrencinin test puanlarına sahip olduğunuzu ve onlara "bir eğri üzerinde" not vermek istediğinizi varsayalım, yani öğrencilerin notları, sınıfın geri kalanına göre göreceli performanslarına dayalı olacaktır:

Adım 1: Ortalamayı bulun.

Tipik olarak, başlangıçtan itibaren ortalama ve SD değerleri verilir, ancak durum böyle değilse, bu değerleri sadece birkaç basit adımda kolayca hesaplayabilirsiniz. Önce ortalamayı ele alalım.

Ortalama, bir örneğin veya veri popülasyonunun ortalama değerini gösterdiğinden, ORTALAMA işlevini kullanarak standart ölçümünüzü bulabilirsiniz.

Herhangi bir boş hücreye aşağıdaki formülü yazın (F1 bu örnekte) gerçek verilerinizin yanında (sütun A ve B) veri kümesindeki sınav puanlarının ortalamasını hesaplamak için:

1	=ORTALAMA(B2:B201)

Kısa bir not: Çoğu zaman, formül çıktısını toplamanız gerekebilir. Bunu yapmak için, aşağıdaki gibi ROUND işlevine sarmanız yeterlidir:

1	=YUVARLAK(ORTALAMA(B2:B201),0)

Adım #2: Standart sapmayı bulun.

Bir aşağı, bir gidecek. Neyse ki, Excel'in sizin için standart sapmayı bulmanın tüm kirli işlerini yapacak özel bir işlevi vardır:

1	=STDEV.P(B2:B201)

Yine formül, belirtilen hücre aralığındaki tüm değerleri alır (B2: B201) ve standart sapmasını hesaplar - çıktıyı da yuvarlamayı unutmayın.

1	=YUVARLAK(STDEV.P(B2:B201),0)

Adım #3: Eğri için x ekseni değerlerini ayarlayın.

Temel olarak, grafik, düzgün bir eğri oluşturmak için bir çizgiyle birleştirilen çok sayıda aralıktan (bunları adım olarak düşünün) oluşur.

Bizim durumumuzda, x ekseni değerleri belirli bir sınav puanını göstermek için kullanılacak, y ekseni değerleri ise bize bir öğrencinin sınavda o puanı alma olasılığını söyleyecektir.

Teknik olarak, istediğiniz kadar aralık ekleyebilirsiniz; daha sonra yatay eksen ölçeğini değiştirerek fazla verileri zahmetsizce silebilirsiniz. Sadece üç standart sapmayı içerecek bir aralık seçtiğinizden emin olun.

Birinden bir saymaya başlayalım (çünkü bir öğrencinin sınavdan olumsuz not alması mümkün değildir) ve 150'ye kadar çıkalım - 150 ya da 1500 fark etmez - başka bir yardımcı masa kurmak için.

1. Grafik verilerinin altındaki herhangi bir boş hücreyi seçin (örneğin E 4) ve yazın “1,” ilk aralığı tanımlayan değer.
2. Şuraya gidin: Ev sekme.
3. İçinde düzenleme grup, "Doldurmak.”
4. Altında "dizi içinde," Seçme "Kolon.”
5. İçin "Adım değeri," tip “1.” Bu değer, Excel son aralığa ulaşana kadar otomatik olarak eklenecek artışları belirler.
6. İçin "Dur değeri," tip "150,” son aralığı temsil eden değeri seçin ve “TAMAM.”

Mucizevi bir şekilde, E sütununda 149 hücre (E5: E153) 2'den 150'ye kadar olan değerlerle doldurulmuştur.

NOT: Ekran görüntülerinde gösterildiği gibi orijinal veri hücrelerini gizlemeyin.. Aksi takdirde, teknik çalışmayacaktır.

Adım #4: Her x ekseni değeri için normal dağılım değerlerini hesaplayın.

Şimdi, her bir aralık için normal dağılım değerlerini (bir öğrencinin belirli bir x ekseni değeriyle temsil edilen belirli bir sınav puanı alma olasılığını) bulun. Neyse ki, Excel'in tüm bu hesaplamaları sizin için yapacak gücü var: NORM.DAĞ işlevi.

Sağdaki hücreye aşağıdaki formülü yazın (F4) ilk aralığınızın (E 4):

1	=NORM.DAĞ(E4,$F$1,$F$2,YANLIŞ)

Buna göre ayarlamanıza yardımcı olacak kodu çözülmüş sürüm:

1	=NORM.DAĞ([ilk aralık],[ortalama(mutlak referans)],[standart sapma(mutlak referans),YANLIŞ)

Kalan aralıklar için formülü zahmetsizce uygulayabilmeniz için ortalama ve SD değerlerini kilitlersiniz (E5: E153).

Şimdi, formülü hücrelerin geri kalanına kopyalamak için doldurma tutamacına çift tıklayın (F5:F153).

Adım 5: Düz çizgilerle bir dağılım grafiği oluşturun.

Son olarak, çan eğrisini oluşturma zamanı geldi:

1. x ve y ekseni değerlerini içeren yardımcı tabloda herhangi bir değeri seçin (E4:F153).
2. şuraya git Sokmak sekme.
3. Tıkla "Dağılım (X, Y) veya Kabarcık Grafiği Ekle" buton.
4. Seçmek "Düzgün Çizgilerle Dağılım.

Adım #6: Etiket tablosunu ayarlayın.

Teknik olarak, çan eğriniz var. Ancak onu tanımlayan herhangi bir veri olmadığı için okumak zor olurdu.

Ortalamanın altındaki ve üstündeki tüm standart sapma değerlerini gösteren etiketleri ekleyerek normal dağılımı daha bilgilendirici hale getirelim (bunları bunun yerine z puanlarını göstermek için de kullanabilirsiniz).

Bunun için aşağıdaki gibi başka bir yardımcı tablo oluşturun:

İlk önce, Ortalama değeri kopyalayın (F1) X-Değeri sütunundaki ilgili hücrenin yanında (I5).

Ardından, bu basit formülü girerek ortalamanın altındaki standart sapma değerlerini hesaplayın. hücre I4:

1

=I5-$F$2

Basitçe söylemek gerekirse, formül, önceki standart sapma değerlerinin toplamını ortalamadan çıkarır. Şimdi, formülü kalan iki hücreye kopyalamak için doldurma tutamacını yukarı doğru sürükleyin (I2: I3).

Ayna formülünü kullanarak ortalamanın üzerindeki standart sapmalar için aynı işlemi tekrarlayın:

1

=I5+$F$2

Aynı şekilde, diğer iki hücre için formülü uygulayın (I7: I8).

Son olarak, y ekseni etiket değerlerini doldurun (J2:J8) yatay eksen üzerine yerleştirilen veri işaretçilerini istediğiniz gibi sıfırlarla.

Adım #7: Etiket verilerini grafiğe ekleyin.

Şimdi, hazırladığınız tüm verileri ekleyin. Grafik grafiğine sağ tıklayın ve “Veri'yi seçin.”

Açılan iletişim kutusunda “Ekle.”

Yardımcı tablodan ilgili hücre aralıklarını vurgulayın-I2: I8 için "Seri X değerleri" ve J2:J8 için "Y Serisi değerleri”-ve tıklayın "TAMAM.”

Adım #8: Etiket serisinin grafik türünü değiştirin.

Bir sonraki adımımız, yeni eklenen serilerin grafik türünü değiştirerek veri işaretçilerinin nokta olarak görünmesini sağlamak. Bunu yapmak için, grafik grafiğine sağ tıklayın ve “Grafik Türünü Değiştirin.”

Ardından, bir birleşik grafik tasarlayın:

1. Şuraya gidin: kombo sekme.
2. İçin Seri "Seri2" değiştirmek "Grafik tipi" ile "Dağıt.”
   • Not: "Seri1"olarak kalır"Düzgün Çizgilerle Dağılım” Bazen Excel yaptığınızda bunu değiştirir. kombo Ayrıca "Seri1” İkincil Eksen'e itilmez - grafik türünün yanındaki onay kutusu işaretlenmemelidir.
3. Tıklamak "Tamam.”

Adım 9: Yatay eksen ölçeğini değiştirin.

Yatay eksen ölçeğini ayarlayarak grafiği çan eğrisi üzerinde ortalayın. Yatay eksene sağ tıklayın ve “Biçim Ekseni"menüden.

Görev bölmesi göründüğünde aşağıdakileri yapın:

• şuraya git Eksen Seçenekleri sekme.
• Yı kur Minimum Sınırlar değer "15.”
• Yı kur Maksimum Sınırlar değer "125.”

Eksen ölçek aralığını uygun gördüğünüz şekilde ayarlayabilirsiniz, ancak standart sapma aralıklarını bildiğiniz için, eğrinin "kuyruğu"nu göstermek için Sınır değerlerini üçüncü standart sapmalarınızın her birinden biraz uzağa ayarlayın.

Adım #10: Özel veri etiketlerini yerleştirin ve konumlandırın.

Grafiğinizi parlatırken, özel veri etiketlerini eklediğinizden emin olun. İlk önce, temsil eden herhangi bir noktaya sağ tıklayın Seri "Seri2" seçin ve “Veri Etiketleri ekleyin.”

Ardından, varsayılan etiketleri önceden ayarlamış olduğunuz etiketlerle değiştirin ve bunları veri işaretçilerinin üzerine yerleştirin.

1. Herhangi birine sağ tıklayın Seri "Seri2" veri etiketi.
2. Seçme "Veri Etiketlerini Biçimlendirin.”
3. Görev bölmesinde, Etiket Seçenekleri sekme.
4. kontrol edin”X Değeri" kutu.
5. " seçeneğinin işaretini kaldırınY Değeri" kutu.
6. Altında "Etiket Konumu," Seç "Üstünde.”

Ayrıca, artık kılavuz çizgilerini kaldırabilirsiniz (üzerlerine sağ tıklayın > Sil).

Adım #11: Veri işaretçilerini yeniden renklendirin (isteğe bağlı).

Son olarak, grafik stilinize uymalarına yardımcı olmak için noktaları yeniden renklendirin.

1. Herhangi birine sağ tıklayın Seri "Seri2" veri etiketi.
2. Tıkla "Doldurmak" buton.
3. Görünen paletten renginizi seçin.

Ayrıca, noktaların etrafındaki kenarlıkları kaldırın:

1. Aynı veri işaretçisine tekrar sağ tıklayın ve “anahat.”
2. Seçmek "Anahat Yok.”

Adım 12: Dikey çizgiler ekleyin (isteğe bağlı).

Son bir düzenleme olarak, SD değerlerini vurgulamaya yardımcı olması için grafiğe dikey çizgiler ekleyebilirsiniz.

• Grafik grafiğini seçin (bu şekilde, çizgiler doğrudan grafiğe eklenecektir).
• şuraya git Sokmak sekme.
• Tıkla "şekiller" buton.
• Seçmek "Hat.”

basılı tutun "VARDİYA" Her noktadan her çizginin çan eğrisiyle buluştuğu yere mükemmel dikey çizgiler çizmek için fareyi sürüklerken tuşuna basın.

Grafik başlığını değiştirin ve gelişmiş çan eğriniz değerli dağıtım verilerinizi göstermeye hazır.

Ve işte böyle yapıyorsun. Artık herhangi bir veri kümesini seçebilir ve bu basit adımları izleyerek normal bir dağılım çan eğrisi oluşturabilirsiniz!