Bu makale, bir pivot tablo kullanarak verilerin nasıl analiz edileceğini tartışacaktır. Eğitim için bu linkte bulunan tabloyu kullanabilirsiniz (simple table.xlsx).
Bir pivot tablo kullanarak analiz etmeniz gerekebilecek ilk şey, alt toplamları toplamaktır. Örneğimizde bu, her bir tarih için tüm mağazaların satış hacimlerini hesaplama ihtiyacı olabilir.
Bunu yapmak için, pivot tablonun herhangi bir satır başlığına tıklayın (örneğimizde bunlar alanlardır) Tarih, satış noktası ve Telefon markası) ve açılan sekmelerde Pivot tablolarla çalışma sekmeye git Seçenekler. Üzerindeki düğmeye basmanız gerekiyor. Alan Seçenekleri grup içinde aktif alan.
Açılan pencerede ilk sekme sekme olacaktır.
Böyle bir yer işaretinin olmaması, satır başlığını seçmediğiniz anlamına gelir, yani imleç sayısal bir değere sahip bir hücreye yerleştirilir.
Yer imi Alt toplamlar ve filtreler Ara toplamları görüntülemek için bir koşul seçebilirsiniz. Aşağıdaki koşullar sunulmaktadır:
Otomatik ara toplamı ayarlayarak, her koşul için alt toplamları içeren aşağıdaki tabloyu elde ederiz:
Komutla ara toplamlar ayarlanıyorsa Alan Seçenekleri, görünür sonuçlar vermiyor, komutu kullanarak ara toplamları görüntülemek için ayarları kontrol edin ara toplamlar gruplar Düzen sekmeler yapıcı.
Diyelim ki satış noktaları için ara toplamları gizleyerek yalnızca tarihler için ara toplamları görüntülemek istiyoruz. Bunu yapmak için, mağazanın adını içeren tablonun herhangi bir alanını tıklayın ve içerik menüsünü çağırın. İçinde, koşulun işaretini kaldırmanız gerekir Ara toplam: satış noktası. Gördüğümüz gibi, ara toplamlar yalnızca tarihler için kalır:
Bir PivotTable'daki verileri daha iyi anlamak için sıralamak genellikle gereklidir. Bunu yapmak için, sıralamak istediğiniz alanı seçin, sekmeye gidin Genel, grup içinde düzenleme düğmeye tıklayın Sırala ve filtrele ve ihtiyacınız olan sıralama koşullarını ayarlayın.
Bir pivot tablodaki bilgileri analiz etmek için çok kullanışlı bir özellik, verileri gruplama yeteneğidir. Örneğin, satışlarımızı ayın haftasına göre gruplandırmamız gerekiyor. Bunu yapmak için ilk haftaya dahil olan tarihleri seçin (15.05-21.05):
Lütfen seçim kolaylığı için, mağaza adının bulunduğu hücrenin sol tarafındaki + düğmesini kullanarak bireysel mağazalara ait verileri daralttığımızı unutmayın.
Ardından, komutu çalıştırmanız gerekir Seçime göre gruplandır gruplar Grup sekmeler Seçenekler. Tabloda, alanın bulunduğu yeni bir sütun görünecektir. Grup 1 seçtiğimiz alanları birleştirecek.
Geriye kalan tek şey, hücreyi düzenleyerek grup adını yeniden adlandırmaktır:
Grubu çözmek için sadece komutu kullanın Grubu çöz aynı gruptan, gruplandırılacak alanı seçtikten sonra. Pivot tablo oluşturma koşuluna dahil ettiğimiz bir alanın grubunu çözemeyeceğinizi lütfen unutmayın - örneğin, alan Satış noktası veya tarih.
Tablomuzdaki bilgileri analiz etmemize yardımcı olacak verileri görüntülemenin başka bir yolunu düşünelim. Örneğin, gelir hacmini parasal olarak değil, tüm satış dönemi için toplam gelirin yüzdesi olarak bilmemiz gerekir.
Bunu yapmak için, pivot tablomuzun Gelir sütunundaki herhangi bir hücreyi seçin. Bundan sonra komutu çalıştırmanız gerekir Alan Seçenekleri grup içinde aktif alan sekmeler Seçenekler.
Açılan iletişim kutusunda sekmeye gidin Ek Hesaplamalar ve açılır menüden seçin Sütuna göre toplamın yüzdesi. OK butonuna tıkladıktan sonra tablomuz aşağıdaki gibi olacaktır.
Tablodaki veriler yüzde olarak görüntülenmeyecekse, hücrelerin sayı biçimi ayarlarını kontrol edin (bu, iletişim kutusunda hemen yapılabilir). Alan Seçenekleri butona tıklayarak sayısal biçim veya bağlam menüsünden ilgili pencereyi çağırarak).
Excel 2007'de çok sayıda satır veya kayıt içeren veri listelerini (veri tabloları) çözümlemek için genellikle PivotTable'lar gibi araçlar kullanılır. PivotTable'lar, Excel 2007 listelerindeki verileri görüntülemeyi, değiştirmeyi ve özetlemeyi çok daha kolaylaştırır.
Excel'in girilen verilerle basit bir veri kümesi olarak değil, bir veri tablosu olarak çalışması için tablo olarak biçimlendirilmesi gerekir. Bunu yapmak için Ekle sekmesindeki Tablo düğmesini tıklayın. Tablo Oluştur iletişim kutusunda (Şekil 1), tablonun yerleştirileceği istenen aralığı belirtin ve Başlıklı Tablo onay kutusunu seçin.
Pirinç. bir.
Biçimlendirme bu aralığa uygulanacaktır, yani. tablonun varsayılan olarak ayarlanan hızlı stili uygulanırken, bağlamsal sekme Tasarım'da bulunan "Tablolarla çalışma" genel adı altındaki bağlamsal araçlar etkinleştirilir. Bir tabloyu biçimlendirmek için Giriş sekmesindeki Tablo Olarak Biçimlendir aracını da kullanabilirsiniz.
Pirinç. 2.
Bu tablo üzerinde bir rapor oluşturmak için güçlü "Pivot Table" aracını kullanmanız önerilir. Bu aracı veri listelerine veya veri tablolarına uygulamak için, örneğin "Stokta stok" tablosundaki hücre gibi veri tablosu hücrelerinden birinin etkinleştirilmesi gerekir. Daha sonra "Tablo" grubunda "Ekle" sekmesinde bulunan "Pivot Table" butonuna tıklayın (Şekil 3).
Pirinç. 3.
Açılan Özet Tablo Oluştur iletişim kutusunda, bir tablo veya aralık seçmeniz (vurgulamanız) ve raporu nereye yerleştirmek istediğinizi belirtmeniz (tercihen yeni bir sayfada), ardından Tamam'ı tıklamanız gerekir. Özel bir pivot tablo sihirbazı açılacaktır (Şekil 4).
Pirinç. dört.
Raporun görüntüsü (PivotTable1) çalışma sayfasının sol tarafında görüntülenir ve pivot tablo oluşturmaya yönelik araçlar sayfanın sağ tarafında bulunur: dört boş alan ve bir alan listesi. Bir rapor oluşturmak için, sağ tarafta gerekli alanları pivot tablonun ilgili alanlarına sürükleyin: "Rapor Filtresi", "Sütun Adları", "Satır Adları" ve "Değerler".
Örneğin, Depo numarası, Ad, Fiyat (UAH) alanlarını seçer ve bunları uygun alanlara sürüklerseniz: "Sütun adları", "Satır adları" ve "Değerler", bu alanlarda ekranda görüntülenir. Sağ Taraf. Bu durumda, çalışma sayfasının sol tarafında bir pivot tablo veya rapor oluşturulacaktır (Şekil 5).
Pirinç. 5.
"Değerler" alanında, örneğin toplama (Fiyat alanına göre Toplam) gibi bazı matematiksel hesaplamaların yapıldığına dikkat edilmelidir. Hesaplama türünü değiştirmek için "Değerler" alanında "Fiyata Göre Tutar" alanına sol tıklayın ve açılan menüden "Değer alanı ayarları" komutunu seçin, ardından "Değer alanı ayarları" iletişim kutusunda , gerekli işlevi seçin ve Tamam düğmesine tıklayın.
Pivot tablonun yapısını değiştirmek için sayfanın sağ tarafındaki alanları pivot tablonun başka bir alanına sürükleyin veya silin. Bir alanı silmek için tablonun dışına sürüklemeniz gerektiğine dikkat edilmelidir.
Excel 2007'de verileri analiz etmeye yönelik güçlü araçlar şunlardır:
What-if analiz araçları, Veri Araçları grubundaki Veri sekmesine ve Analiz grubundaki Veri sekmesine Çözüm Bul'a yerleştirilir.
Parametre seçimi, bir argümanın bir fonksiyonu için parametre seçimi problemine bir çözüm sağlar. Scenario Manager, senaryolar oluşturmak (önce farklı durumlar için birkaç senaryo eklemek) ve ardından süreci tahmin etmek ve senaryo hakkında bir rapor oluşturmak için senaryoları gözden geçirmek için tasarlanmıştır.
"Çözüm arama" programı, karmaşık denklem sistemlerini, doğrusal ve doğrusal olmayan optimizasyon problemlerini çözmek için tasarlanmıştır. Çözücü eklentisi, yinelemeli yöntemlere dayanır.
Excel yalnızca bir elektronik tablo düzenleyicisi değil, aynı zamanda çeşitli matematiksel ve istatistiksel hesaplamalar için güçlü bir araçtır. Uygulama, bu görevler için tasarlanmış çok sayıda işleve sahiptir. Ancak, bu özelliklerin tümü varsayılan olarak etkin değildir. Araç setinin ait olduğu bu tür gizli işlevlerdir. "Veri analizi". Nasıl etkinleştirilebileceğini öğrenelim.
Fonksiyonun sağladığı özelliklerden yararlanmak için "Veri analizi", araç grubunu etkinleştirmeniz gerekiyor "Analiz Paketi" Microsoft Excel Tercihleri'ndeki belirli adımları izleyerek. Bu eylemlerin algoritması, programın 2010, 2013 ve 2016 sürümleri için hemen hemen aynıdır ve 2007 sürümünde sadece küçük farklılıklar vardır.
Bu adımları tamamladıktan sonra, belirtilen işlev etkinleştirilecek ve araç takımı Excel şeridinde mevcut olacaktır.
Artık gruptaki araçlardan herhangi birini başlatabiliriz "Veri analizi".
Her işlevde çalışmanın kendi eylem algoritması vardır. Grubun bazı araçlarını kullanma "Veri analizi" ayrı derslerde anlatılmıştır.
Gördüğünüz gibi, araç kutusu "Analiz Paketi" ve varsayılan olarak etkinleştirilmemiştir, etkinleştirme işlemi oldukça basittir. Aynı zamanda, net bir eylem algoritması bilmeden, kullanıcının bu çok kullanışlı istatistiksel işlevi hızlı bir şekilde etkinleştirmesi olası değildir.
GÖREV 1
MS Excel'de istatistiksel veri analizi
Çalışmanın amacı: MS Excel'in yerleşik işlevlerini kullanarak istatistiksel verilerin nasıl işleneceğini öğrenmek; Analysis Pack ve araçlarının özelliklerini keşfedin: " Rastgele Sayı Üretimi», "Grafik çubuğu" , " Tanımlayıcı istatistikler" işleme hızı ölçümleri örneğinde.
"Araçların hızının ölçülmesi" ("Yolların araştırılması ve tasarımı" disiplininde) laboratuvar çalışması yönergelerine uygun olarak, Excel programında matematiksel istatistik yöntemlerini kullanarak deneysel ölçüm verilerini işleyin. Ne için:
1. Yerleşik işlevleri kullanarak istatistiksel özellikleri hesaplayın: - Vmin hareket hızının minimum değeri;
Hareket hızının maksimum değeri Vmax; - Vav hareket hızının ortalama değeri;
Standart sapma;
Ortalama Sp'nin standart sapması;
Öğrenci katsayısı (güven aralığını belirlemek için) t; - Р = 0.95 için güven aralığı.
2. Aracı kullanarak istatistiksel özellikleri alın "Tanımlayıcı istatistikler"Ek paketten" Veri Analizi ".
3. Hız dağılımının bir histogramını oluşturun.
4. Kümülatif bir eğri oluşturun (kümülatif frekans eğrisi).
5. Hareket hızının dağılımı için teorik bir eğri oluşturun.
Yeterli miktarda başlangıç verisi (hız ölçüm sonuçları) elde etmek için, “ Rastgele sayı üretimi» "Veri Analizi" eklentileri.
pp gerçekleştirirken 3 ve 4, normal dağılım yasasını gösteren en simetrik histogramı elde etmenizi sağlayan hız aralığını (“cep” - Excel terminolojisinde) seçin.
Ekli BasicsPC1-Student.xls dosyasında örnek bir uygulama verilmiştir.
Yönergeler
Bazı X değerlerini ölçen bir dizi 10 deney yaptığımızı varsayalım. Tablo 1. "Deney İşleme" sayfasının yaklaşık görünümü
D ve E sütunlarındaki girişler, hangi özellikleri hesaplayacağımızı anlamanıza yardımcı olacak ipuçlarıdır. F sütunu şimdilik boş olmalı, formüllerimiz içine yerleştirilecektir.
Deney sayısını n hesaplayarak sonuçları işlemeye başlayalım.
Değerlerin sayısını belirlemek için COUNT adlı özel bir işlev kullanılır. Fonksiyonlu bir formül girmek için, "Ekle" - "Fonksiyon" menüsü veya araç çubuğundaki f x etiketli düğme aracılığıyla "İşlev Ekle" komutuyla başlatılan İşlev Sihirbazı'nı kullanın.
Sonucun olması gereken F6 hücresine tıklayıp İşlev Sihirbazını başlatalım.
Çalışmanın ilk adımı (şekil 1) istenen işlevi seçmeye yarar.
Deneysel verileri işlemek için istatistiksel fonksiyonlar kullanılır. Bu nedenle, her şeyden önce, kategoriler listesinde " İstatistiksel" kategorisini seçin. İkinci pencerede istatistiksel işlevlerin bir listesi görünür.
İşlev listesi alfabetik olarak sıralanmıştır, bu da ihtiyacımız olan COUNT işlevini bulmayı kolaylaştırır ("Argüman listesindeki sayıları sayar").
Bu işlev vurgulanmış durumdayken Tamam düğmesine basın ve 2. adıma gidin.
İkinci adım (Şekil 2), fonksiyon argümanlarını ayarlamak için kullanılır.
COUNT işlevinin, hangi sayıları yeniden hesaplaması gerektiğini veya bu sayıların hangi hücrelerde bulunduğunu belirtmesi gerekir. Bir dizi deneyi işlemenin sonraki iki aşaması benzer şekilde gerçekleştirilir.
F7 hücresinde, ORTALAMA işlevi kullanılarak örneğin ortalama değeri, F8 hücresinde STDEV işlevi kullanılarak örneğin standart sapması hesaplanır. .
Aynı hücre aralığı, bu işlevler için bağımsız değişken görevi görür.
Güven aralığını hesaplamak için Student katsayısının belirlenmesi gerekir. Hata olasılığına (genel olarak verilen %95 güvenilirlikle, hata olasılığı %5'tir) ve serbestlik derecesi sayısına bağlıdır n-1 ).
Öğrenci katsayısını bulmak için, istatistiksel Excel işlevi STUDISTRIP ("Öğrenci dağılımının tersi") kullanılır. Bu işlevin bir özelliği, ilk argümanın, %5 (veya 0.05) sayısının ilgili pencereye klavyeden girilmesidir. İkincisi için, n değerinin bulunduğu hücrenin adresini belirtin, ardından pencereye “-1” ekleyin. " F6-1" girişini alıyoruz.
Güven aralığını bulmak için olağan çarpma formülü kullanılır. Elbette harfler yerine Student katsayısının ve ortalamanın standart sapmasının bulunduğu hücre adresleri olmalıdır. Kural olarak, güven aralığının değeri anlamlı bir rakama yuvarlanır, ortalama için ortamın aynı sırası olmalıdır. Bu nedenle, nihai sonuç şu şekilde yazılabilir: %95 güvenilirlikle, X = 14.80 ± 0.05. Sonuç olarak, X: = CI / X cf (formül: “=F11/F7”) belirlenmesindeki bağıl hatayı hesaplıyoruz. Göreceli hatanın değeri genellikle yüzde olarak ifade edilir, bizde %0.3 var.
Görev 2 ve 3, "Analiz Paketi" eklentisi kullanılarak gerçekleştirilir (Araçlar .Veri Analizi Histogram menüsünden).
Eklentiyi kurmak için Araçlar Eklentiler menüsünü arayın ve kurulum için önerilen eklentiler listesinden "Analiz Paketi"ni seçin (bkz. Eklentileri yükleme
Computer.doc'ta Excel).
