İnsanları Çan Eğrisine Yerleştirme - Excel İpuçları

İçindekiler

Huntsville'deki Jimmy, birkaç kişinin ortalama puanlarını gösteren bir çan eğrisi çizmek istiyor. Jimmy Power Excel Seminerim sırasında soruyu sorduğunda, YouTube'daki daha popüler videolarımdan birini düşündüm.

Podcast 1665 - Excel'de bir Çan Eğrisi Oluşturma'da, bir çan eğrisi oluşturmak için ortalama ve standart sapmayı hesaplamanız gerektiğini açıklıyorum. Sonra x ekseni boyunca varsayımsal bir insan popülasyonunu kapsayan 30 nokta oluşturuyorum. Bu videoda, bir ortalama etrafında -3 Standart Sapmadan + 3 Standart Sapmaya kadar uzanan bir video oluşturdum.

Örneğin, ortalamanız 50 ve standart sapmanız 10 ise, 70 ile 130 arasında değişen bir x ekseni oluşturabilirim. Her noktanın yüksekliği kullanılarak hesaplanır =NORM.DIST(x,mean,standard deviation,False).

Bir çan eğrisi oluştur

Yukarıdaki resimde, A10: A40'daki sayılar esasen "sahte veri noktaları" dır. Güzel bir eğri oluşturmak için 31 sayı üretiyorum. Yalnızca 7 veri noktası kullansaydım, eğri şöyle görünürdü:

Daha az veri noktası kullanarak çan eğrisi hala çalışıyor

Jimmy'nin veri seti için, çalışanlarının gerçek ortalama puanları esasen bir x ekseni boyunca olan noktalardır. Bunları bir çan eğrisine uydurmak için, her çalışanın boyunu veya Y değerini bulmanız gerekir.

Bu adımları takip et:

  1. Verileri sıralayın, böylece puanlar düşükten yükseğe doğru görünür.

    Verileri sıralayın
  2. ORTALAMA işlevini kullanarak bir Ortalama hesaplayın.
  3. STDSAPMA işlevini kullanarak bir Standart Sapma hesaplayın.

  4. Kullanarak puanların sağındaki Y değerini hesaplayın =NORM.DIST(L2,$H$2,$H$3,FALSE). Y değeri, çan eğrisi boyunca her bir kişinin noktasının yüksekliğini oluşturacaktır. NORM.DAĞ işlevi, ortalamanın yakınındaki kişileri, yukarı veya aşağıya yakın olanlardan daha yüksek bir konumda çizmeye özen gösterir.

    Bir dizi Y değeri oluşturun.
  5. Verilerinizi L1: M15'te seçin

  6. Excel'de kısa süre önce garip bir hata görünmeye başladı, bu nedenle başarılı olmak için Ekle sekmesindeki Tüm Grafikler'i seçin.

    İletişim kutusu başlatıcısı sizi tüm grafik türlerine götürür

    Grafik Ekle iletişim kutusunda, Tüm Grafikler sekmesine tıklayın. Sol taraftaki XY (Dağılım) seçeneğine tıklayın. Üstteki ikinci simgeyi seçin. Sağdaki önizlemeyi seçin.

    Grafiği seçmek için dört tıklama

    İlk zil eğriniz şöyle görünecek:

    Çan eğrisi

Çan eğrisini temizlemek için şu adımları izleyin:

  1. Başlığa tıklayın ve Sil tuşuna basın.
  2. Grafiğin altındaki Y ekseni boyunca herhangi bir sayıya çift tıklayın. Ekseni Biçimlendir paneli görünecektir.

  3. Minimum ve Maksimum için yeni değerler yazın. Buradaki aralık, grafikteki herkesi gösterecek kadar geniş olmalıdır. 50 ila 90 kullandım.

    Minimum ve maksimumu değiştirin
  4. Grafiğin kenarını sürükleyerek grafiği genişletin.
  5. Grafiğin sağındaki + simgesini tıklayın ve Veri Etiketleri'ni seçin. Etiketlerin henüz bir anlam ifade etmediğinden endişelenmeyin.
  6. Etiketleri Biçimlendir panelini açmak için bir etiketi çift tıklayın.
  7. Panelin üst kısmında dört simge vardır. Sütun grafiği gösteren simgeyi seçin.
  8. Panelin o bölümünü genişletmek için Etiket Seçenekleri'nin yanındaki oku tıklayın.
  9. Hücrelerden Değer Seçin. Etiketlerin konumunu soran bir iletişim kutusu görünecektir. K2: K15'teki isimleri seçin.

  10. Yine de Veri Etiketini Biçimlendir panelinde Y değerlerinin seçimini kaldırın. Adım 16'yı yapmadan önce Adım 15'i bitirmeniz önemlidir, aksi takdirde yanlışlıkla etiketleri kaldırırsınız.

    İsimlerin bulunduğu hücrelerden etiketleri alın.

Not

Hücrelerden etiket alma yeteneği Excel 2013'te eklenmiştir. Excel 2010 veya önceki bir sürümünü kullanıyorsanız, XY Chart Labeler eklentisini Rob Bovey'den indirin. (Google bulması için).

Bu noktada, birbirine çarpan herhangi bir grafik etiketiniz olup olmadığına bakın. Bunları düzeltmek için aşağıdaki adımları dikkatlice izleyin.

  1. Bir grafik etiketine tek tıklayın. Bu, tüm etiketleri seçer.
  2. Yalnızca o etiketi seçmek için başka bir etiketin üstünde bulunan etiketlerden birine bir kez tıklayın.
  3. Dört başlı bir ok görene kadar imleci etiketin çeşitli kısımlarının üzerinde gezdirin. Etikete tıklayın ve yeni bir konuma sürükleyin.

  4. Yalnızca tek bir etiket seçtikten sonra, o etiketi seçmek için herhangi bir başka etiketi tek tıklayabilirsiniz. Taşınması gereken diğer etiketler için tekrarlayın.

    Nihai tablo

Video izle

Video Metni

Podcast'ten Excel Öğrenin, Bölüm 2217: İnsanları Çan Eğrisine Yerleştirin.

Hey, İnternet'e tekrar hoş geldiniz, ben Bill Jelen. Alabama, Huntsville'deki seminerimde Jimmy'den bugünün sorusu. Jimmy'nin verileri var, bu verileri özetlemek ve ardından sonuçları çan eğrisine göre çizmek istiyor.

Peki? Şimdi, YouTube'daki en popüler videolarımdan biri şu: 1663 numaralı, Excel'de Çan Eğrisi Oluşturun. Ve bir ortalama ve bir standart sapma verildiğinde, düşük olanı, yani ortalamadan 3 kat daha az standart sapma ve yüksek - ortalamadan 3 kat daha fazla standart sapma-- ve burada bir dizi X değeri ve yüksekliği bulmak için şu işlevi kullanın: = X değerinin NORM.DAĞ, ortalama ve standart sapma, virgül yanlış (= NORM.DAĞ (A10, $ B $ 2, $ B $ 3, YANLIŞ)).

Ve düşünürseniz, bu video gerçekten güzel görünen bir eğri elde etmek için burada bir dizi sahte X değeri kullanıyor. Ve burada da aynı kavramı kullanacağız ama sahte X değerleri yerine, aslında insanları burada alacağız ve sonra yükseklik tam olarak aynı formül olacak. Peki.

Şimdi Jimmy bir pivot tablo oluşturmak istedi. Bu yüzden Ekleyeceğiz, PivotTable, buraya bu sayfaya koyun, Tamam'a tıklayın. Sol taraftaki insanlar ve ardından Ortalama Puanları. Pekala, Puan Toplamı ile başlıyor, orayı çift tıklayıp ortalamaya değiştireceğim. Harika. Şimdi, en altta, genel bir toplam istemiyorum - sağ tıklama ve Genel Toplamı Kaldır - ve bu İnsanları yüksekten alçağa düzenlemek istiyoruz ve bunu bir pivot tabloda yapmak çok kolay. Data, A'dan Z'ye - mükemmel. Peki. Şimdi, Podcast 1663'te yaptığımızın aynısını yapacağız ve bu bir ortalama ve bir standart sapma hesaplıyor. Yani ortalama, bu puanların ortalamasıdır ve bu puanların standart sapmasına eşittir. Peki. Artık bunu bildiğime göre, y-değerimi yaratabilirim.

Pekala, burada yapacağımız birkaç şey var. Öncelikle, bir pivot tablodan bir pivot tablo (dağılım grafiği) oluşturamazsınız. Yani tüm bu verileri kopyalayacağım ve bunu = D2 ile yapacağım. Bunları göstermek için fareyi veya ok tuşlarını kullanmamaya dikkat ettiğime dikkat edin. Ve böylece burada değerlerimiz var. Bunlar X değerleri olacak, Y değeri = NORM.DAĞ olacak, işte x değeri, virgül, ortalama, bu sayı için, kilitlemek için F4'e basacağım; standart sapma için bu sayı bu, tekrar kilitlemek için F4'e basın ve kümülatif YANLIŞ. (= NORM.DAĞ (K2, $ H $ 2, $ H $ 3, YANLIŞ)) Ve bunu kopyalamak için çift tıklayacağız. Peki. Ve sonra etiketleri seçmeyin,sadece XY'yi seçin, biz de çizgileri olan bir dağılım grafiği ekleyeceğiz - eğri çizgileri veya biraz düz çizgileri olanı seçebilirsiniz. Burada, bunun gibi eğri çizgilerle gideceğim. Ve şimdi tüm Halkımızı bir çan eğrisine yerleştirdik.

Peki. Şimdi, bazı şeyler - bazı biçimlendirme türü şeyler - burada yapacağız: Öncelikle, ölçek boyunca buraya çift tıklayın ve en düşük sayımız muhtemelen 50 civarında bir yerde gibi görünüyor - bu yüzden bir min. 50 ayarlayın - ve en büyük sayımız - en büyük sayımız - 88 - bu yüzden maksimum 90 ayarlayacağım. Pekala. Ve şimdi, bu noktaları etiketlemeliyiz. Excel 2013 veya daha yenisindeyseniz, bunu yapmak kolaydır; ancak Excel'in daha eski bir sürümündeyseniz, bu nokta etiketlerinin grafikte olmayan bir yerden gelmesini sağlamak için geri dönüp Rob Bovey'nin Grafik Etiketleyici eklentisini kullanmanız gerekecektir. Pekala, buradan başlıyoruz. Veri Etiketleri ekleyeceğiz ve bu sayılar ekliyor ve korkunç görünüyorlar. Buraya gelip Daha Fazla Seçenek, Etiket Seçenekleri istediğimi söyleyeceğim.ve Hücrelerden Değer - Hücrelerden Değer almak istiyorum. Peki? Dolayısıyla, Hücre aralığı tam orada, Tamam'ı tıklayın. Y değerinin işaretini kaldırmadan önce hücrelerden Değer kullanmak çok önemli. İyi görünmeye başlar. Ben bundan kurtulacağım. Şimdi, buradaki tüm anahtar - çünkü birbirinin üzerine yazan bazı insanlarınız var - grafiği olabildiğince büyük yapmaya çalışmaktır. Orada başlığa ihtiyacımız yok. Neden? Sil şunu. Ve hala Kelly, Lou ve Andy ve Flo'nun neredeyse aynı yerde olduklarını görüyorum; Jared ve-- Pekala. Öyleyse şimdi, bu üst üste gelenler sinir bozucu olacak. Ancak bir etikete tıkladığımızda, tüm etiketleri seçtik ve sonra tekrar bir etikete tıkladık ve sadece tek bir etiket seçtik. Peki? Peki şimdi. çok dikkatli. Andy'yi deneyin ve tıklayın ve Andy'yi sola doğru sürükleyin.Görünüşe göre Jared ve Ike birlikteler, bu yüzden artık tek etiket seçim modundayım, daha kolay. Ve sonra Kelly ve Lou, onları bu şekilde sürükleyin. Belki Lou'yu fazla taşımayan daha iyi bir yer vardır, hatta burada her iki tarafa da sürükleyebilirim. Pekala, elimizde ne var? Bir sürü veriyle başladık, bir pivot tablo oluşturduk, ortalama ve standart sapmayı bulduk, bu da sadece yüksekliği - bu puanların her biri için Y konumu ve bunların yüksekliği, umarım, insanları parabol şeklinde güzel bir çan eğrisine sokacağız, bunun gibi.Pekala, elimizde ne var? Bir sürü veriyle başladık, bir pivot tablo oluşturduk, ortalama ve standart sapmayı bulduk, bu da sadece yüksekliği - bu puanların her biri için Y konumu ve bunların yüksekliği, umarım, insanları parabol şeklinde güzel bir çan eğrisine sokacağız, bunun gibi.Pekala, elimizde ne var? Bir sürü veriyle başladık, bir pivot tablo oluşturduk, ortalama ve standart sapmayı bulduk, bu da sadece yüksekliği - bu puanların her biri için Y konumu ve bunların yüksekliği, umarım, insanları parabol şeklinde güzel bir çan eğrisine sokacağız, bunun gibi.

Jimmy'nin bu sorusunu seviyorum, bu soru bu kitapta yok, ama bu kitabı bir dahaki sefere yazdığımda olacak. Bunu eklemem gerek-- bu harika bir istek ve küçük bir numara. Çan eğrileri Excel'de çok popülerdir.

Ancak kitabım LIVe, Tüm Zamanların En Harika 54 Excel İpucu'na bir göz atın.

Pekala, bu bölümün özeti: Huntsville'den Jimmy, insanları çan eğrisine göre ayarlamak istiyor. Bu yüzden, ortalama puanı bulmak için bir pivot tablo kullanıyoruz, pivot tabloları skorlara göre sıraladık - yüksekten düşüğe düzenlenmiş - en alttaki genel toplamdan kurtulun - bunlar esasen X değerleri olacak - ve ardından yana doğru, bu puanların ortalama ve standart sapmasını hesaplayın ve verileri pivot tablodan yeni bir aralığa kopyalamak için formülleri kullanın, çünkü bir pivot tablo ile kesişen bir XY grafiğiniz olamaz. X değerinin = NORM.DAĞ, ortalama, standart sapma, virgül YANLIŞ ile her kişi için bir y-değeri hesaplayın; Düzgün çizgiler içeren bir XY dağılım grafiği oluşturun - Excel 2010 veya önceki bir sürümseniz, Ron Bovey'in Grafik Etiketleyici eklentisini kullanacaksınız. Bunu google'da aratacağım çünküRob'un URL'sini değiştirmesi durumunda, burada yanlış URL'yi istemiyorum. Excel 2013'te Veri Etiketleri vardı, Hücrelerden, adları belirtin ve ardından bazı ayarlamalar - ölçeği alt kısımda değiştirin, bunları ve Maks.

Çalışma kitabını bugünün videosundan indirmek için YouTube açıklamasındaki URL'yi kullanın. Huntsville'deki bu harika soru için Jimmy'ye teşekkür etmek istiyorum ve uğradığınız için size teşekkür etmek istiyorum. Bir dahaki sefere başka bir internet yayını için görüşürüz.

Excel Dosyasını İndirin

Excel dosyasını indirmek için: place-people-on-bell-curve.xlsx

Huntsville'deki Jimmy'ye bugünün sorusu için teşekkürler!

Excel Günün Düşüncesi

Excel Master arkadaşlarıma Excel hakkında tavsiyelerini sordum. Bugünün düşüncesi:

"Excel'i geçen ay manuel yeniden hesaplama moduna koyduysanız, artık power pivot zamanı (bir daha asla manuel moda ihtiyacınız olmayacak)"

Rob Collie

Ilginç makaleler...