Excel’de Yetkinleşiyoruz-Pivot Table
Pivot Tablosu
Pivot Table(Pivot Tablosu) ile Veri Analizi
Bizlere verilen sorulara cevap bulmak küçük bir veri setinde kolaydır ancak veri setimiz büyüdükçe bilgiye ulaşma süremiz de o kadar artar. Tam da bu noktada Pivot Tablosu devreye girmektedir.
Pivot Table(Pivot Tablosu),bir veri yığınından bilgi ve öngörüleri hızla çıkarmak ve bunlarla çalışmak için önemli bir araçtır.
Excel’de pivot tablosu ile veri çözümlemesi yapmak mümkündür. Bu yazımızda 2014 yılında İstanbul’da bulunan bir üniversiteye yerleşen öğrencilerin bilgilerini içeren örnek veri setimizi kullanarak pivot tablosu ile bize verilen sorulara nasıl hızlı ve kolay yanıt verebileceğimizi inceleyeceğiz.
İlk olarak veri setimizi tanıyarak başlayalım:
Index: Üniversitenin listeyi hazırlarken uygulamış olduğu sıra
Başarı Sırası: Öğrencilerin üniversite sınavındaki başarı sırası
Mezuniyet Yılı: Öğrencilerin liseden mezun oldukları yıl
Cinsiyet: Öğrencilerin cinsiyeti
Tercih Sırası: Öğrencilerin bu üniversiteyi kaçıncı sırada tercih ettikleri
Puan Türü: Öğrencilerin hangi puan türüyle üniversiteye giriş yaptıkları
Bölüm Adı: Öğrencilerin girdiği bölüm
Burs: Öğrencilerin kazandıkları burs
Okul: Öğrencilerin mezun oldukları lise
İl: Öğrencilerin yaşadığı il
Veri setimiz 518 öğrencinin bilgilerini içeren büyük bir veridir. Bu veya bundan daha büyük bir veri setinde cevabına ulaşmak istediğimiz verileri bulma imkanımız zor olacağından pivot tablosu kullanmamız işimizi kolaylaştıracaktır.
Pivot Tablosu Oluşturma:
Öncelikle tüm verimizi Ctrl + A veya Ctrl + Shift + , → Ctrl + Shift + ↓ okları ile seçmemiz gerekiyor.
Daha sonra Insert(Ekle)tuşuna basıp Pivot Table(Pivot Tablosu)seçiyoruz.
Çıkan ekranda uygulayacağımız veri aralığı ve pivot tablosunu yeni bir sayfada açmak isteyip istemediğimiz soruluyor. Çalışmamızda yeni bir sayfa açmayı tercih ettik ama siz isterseniz yeni bir sayfa açmadan da pivot tablosu oluşturabilirsiniz.
Tüm bu anlattığımız adımları video ile görelim:
Öğrencilerin yüzde kaçı erkek?
Pivot tablosunda Values kısmına nümerik olmayan bir veri eklemeliyiz. Bu bölüme okul, bölüm adı, puan türü, cinsiyet verilerini ekleyebiliriz. Örnek olması adına okul verisini atıyoruz. Öğrencilerin cinsiyeti üzerine bir soru olduğu için Rows veya Columns kısımlarına cinsiyet verisini atıyoruz.
Karşımıza çıkan tabloda 518 öğrencinin 280'ninin kadın,238'inin erkek olduğu sonucuna ulaşıyoruz.
Bize öğrencilerin yüzde kaçının erkek öğrenci olduğu sorulduğu için Erkek öğrenci sayısı/Toplam öğrencisi sayısı işlemini Excel üzerinde gerçekleştiriyoruz. Çıkan sonuç virgül ile ifade edildiği için bunu Percent Style seçeneği ile yüzde olarak ifade edebiliriz.
Tüm bu anlattığımız adımları video ile görelim:
Öğrencilerin yüzde kaçı İstanbul’dan?
Pivot tablosunda Values kısmına nümerik olmayan bir değer atıyoruz. Örnek olarak yeniden okul verisini atayabiliriz. İl bazlı bir soru sorulduğu için Rows kısmına İl verisini atıyoruz.
Karşımıza çıkan tabloda Row labels sütununda illeri, Count of Okul sütununda ise o illerden gelen öğrenci sayısını görüntülüyoruz. Sorulan soruda öğrencilerin yüzden kaçının İstanbul’dan geldiği sorulduğu için Count of School sütunundaki ilk veriye tıklayıp Data(Veri) kısmından bu verilerin büyükten küçüğe sıralanması seçeneğini seçiyoruz. En üst sırada İstanbul’dan gelen öğrenci sayısı 321 olarak karşımıza çıkıyor bu aynı zamanda üniversiteye en çok İstanbul’dan öğrenci geldiğinin de cevabı.
Öğrencilerin yüzde kaçının İstanbul’dan geldiğini bulabilmemiz için İstanbul’dan Gelen Öğrenci Sayısı/Toplam Öğrenci Sayısı işlemini yapmamız gerekiyor. Tablonun sağ köşesinde sütuna gelip =İstanbul’dan Gelen Öğrenci Sayısının bulunduğu sütun/Toplam Öğrenci Sayısının bulunduğu sütun işlemi ile sonuca ulaşabiliriz. Sonuç 0,619691 olarak karşımıza çıkıyor.
Son adımda, istenen değer bizden yüzde olarak istendiği için 0,619691 değerinin bulunduğu sütuna tıklayıp Data(Veri) kısmından Percent Style’a tıklayarak %62 değerine ulaşıyoruz.
Tüm bu anlattığımız adımları video ile görelim:
İstanbul dışından gelen öğrencilerin yüzde kaçı kadın?
Pivot tablosunda Values kısmına nümerik olmayan bir değer atıyoruz. Örnek olarak yeniden okul verisini atayabiliriz. Bu kez hem il bazında hem de cinsiyet bazında bir sonuca ulaşmak istediğimiz için Rows kısmına il verisini, Columns kısmına cinsiyet verisini atıyoruz.
Bu aşamada diğer sorulardan farklı olarak bir filtreleme işlemi yapmamız gerekiyor. İstanbul dışından gelen öğrencilere ulaşmak için karşımıza çıkan tabloda Row Labels sütunundaki il değerlerinde İstanbul hariç tüm illeri seçiyoruz. Bunu kısaca Select All seçeneği işaretliyken sadece İstanbul’u çıkararak yapabiliriz.
Şu an karşımızda İstanbul hariç tüm illerden gelen erkek öğrenci sayısını ve kadın öğrenci sayısını ayrı ayrı gözlemliyoruz.
İstanbul dışından gelen öğrencilerin yüzde kaçının kadın olduğunu bulabilmemiz için İstanbul hariç diğer illerin değerlerini içeren tablomuzda kadın öğrenci sayısı/toplam öğrenci sayısı işlemini yapmamız gerekiyor. Tablonun sağ köşesinde sütuna gelip =Toplam Kadın Öğrencinin bulunduğu sütun/Toplam Öğrenci Sayısının bulunduğu sütun işlemi ile sonuca ulaşabiliriz. Sonuç 0,553299 olarak karşımıza çıkıyor.
Son adımda istenen değer bizden yüzde olarak istendiği için 0,553299 değerinin bulunduğu sütuna tıklayıp Home(Giriş) kısmından Percent Style’a tıklayarak %55 değerine ulaşıyoruz.
Tüm bu anlattığımız adımları video ile görelim:
Kaç şehirden öğrenci gelmiş?
Pivot tablosunda Values kısmına nümerik olmayan bir değer atıyoruz. Örnek olarak yeniden okul verisini atayabiliriz. Şehir bazında bir soru sorulduğu için Rows kısmına il değerini atıyoruz.
Karşımıza çıkan tabloda hangi şehirden kaç öğrencinin geldiği değerleri bulunuyor.
Kaç şehirden öğrenci geldiğini bulmak için Row Labels sütununda bulunan ilk İl’e tıklıyoruz ve Ctrl + Shift +↓ komutu ile tüm illeri seçebiliriz ancak bu şekilde Grand Total satırı da il olarak sayılmış olacaktır. Bu yüzden komutun ardından Shift + ↑ komutu ile bir adım yukarı çıkarak sadece illeri seçmiş oluyoruz.
Excel sayfasının sağ tarafından kaç tane değeri seçtiğimize ulaşabiliriz. Böylelikle kaç şehirden öğrenci geldi sorusuna 51 yanıtını kolaylıkla verebiliriz.
Tüm bu anlattığımız adımları video ile görelim:
İstanbul’dan sonra en çok öğrenci gönderen 3 il hangileri?
Pivot tablosunda Values kısmına nümerik olmayan bir değer atıyoruz. Örnek olarak yeniden okul verisini atayabiliriz. İl bazında bir soru sorulduğu için Rows kısmına il değerini atıyoruz.
İstanbul’dan sonra en çok öğrenci gönderen il sorulduğu için aslında bizden bu aşamada bir sıralama yapmamız isteniyor. Count of OKUL sütunundaki ilk değere tıklıyoruz. Data(Veri) kısmına gelip büyükten küçüğe sıralama seçeneğini seçiyoruz. Böylelikle İstanbul’dan sonra en çok öğrenci gönderen 3 il: Antalya, Bursa, Balıkesir olarak karşımıza çıkıyor.
Tüm bu anlattığımız adımları video ile görelim:
Öğrencilerin mezuniyet yılı dağılımı ne?
Pivot tablosunda Values kısmına nümerik olmayan bir değer atıyoruz. Örnek olarak yeniden okul verisini atayabiliriz. Mezuniyet Yılı bazında bir soru sorulduğu için Rows kısmına mezuniyet yılı değerini atıyoruz.
Bize bu soruda dağılım sorulduğu için bu işlem için oluşan tablodaki Row Labels sütunundaki bir değere sağ tıklayıp Group(Grup) seçeneğini seçiyoruz. Karşımıza hangi değerle başlayacağımız(Starting at) hangi değerle biteceğine(Ending at) ve kaçarlı gruplayacağımız(by) seçenekleri çıkıyor. Burada 3'erli gruplandırmak istediğimiz için by değerine 3 yazıyoruz.
Bu işlemler sonunda sonuca kolaylıkla ulaşıyoruz.
Tüm bu anlattığımız adımları video ile görelim:
Mühendislik öğrencilerinin mezuniyet yılları dağılımı ne?
Bu soruda diğer sorudan farklı olarak bölüm bazında bir filtreleme yapmamız gerekiyor. Yukarıdaki soruda bulunan tabloya ek olarak Filters kısmına bölüm adı verisini atıyoruz.
Karşıma çıkan filtreleme seçeneğinden mühendislik bölümleri olan;bilgisayar, elektrik, endüstri, inşaat, makine değerlerini seçiyoruz. Bu şekilde mühendislik öğrencilerinin mezuniyet yılı dağılımına şu şekilde ulaşabiliriz.
Tüm bu anlattığımız adımları video ile görelim:
Endüstri Mühendisliği öğrencilerinin yüzde kaçı kadın?
Pivot tablosunda Values kısmına nümerik olmayan bir değer atıyoruz. Örnek olarak yeniden okul verisini atayabiliriz. Bu kez hem bölüm adı bazında hem de cinsiyet bazında bir sonuca ulaşmak istediğimiz için Rows kısmına bölüm adı verisini, Columns kısmına cinsiyet verisini atıyoruz.
Oluşan tabloda bölüm bazında kaç erkek ve kaç kadın öğrencinin olduğunu gözlemleyebiliyoruz. Ancak bizim sorumuzda Endüstri Mühendisliği bölümü özelinde bir soru sorulduğu için Row Labels seçeneğinden Endüstri filtrelemesini yapıyoruz. Karşımıza Endüstri Mühendisliği bölümünde kaç kadın öğrenci kaç erkek öğrenci bulunduğuna dair değerler çıkıyor.
Endüstri Mühendisliği öğrencilerinin yüzde kaçının kadın olduğuna tablonun sağ tarafındaki sütuna gelip =Endüsri Mühendisliğindeki Toplam Kadın Öğrenci Sayısının bulunduğu sütun/Endüstri Mühendisliğindeki Toplam Öğrenci Sayısının bulunduğu sütun işlemi ile sonuca ulaşabiliriz. Sonuç 0,4375 olarak karşımıza çıkıyor.
Son adımda istenen değer bizden yüzde olarak istendiği için 0,4375 değerinin bulunduğu sütuna tıklayıp Home(Giriş) kısmından Percent Style’a tıklayarak %44 değerine ulaşıyoruz.
Tüm bu anlattığımız adımları video ile görelim:
Hukuk fakültesine en çok öğrenci gönderen iki lise?
Pivot tablosunda Values kısmına nümerik olmayan bir değer atıyoruz. Örnek olarak yeniden okul verisini atayabiliriz. Okul bazında bir soru sorulduğu için Rows kısmına da okul değerini atıyoruz.
Hukuk fakültesi özelinde bir soru sorulduğu için bölüm bazlı bir filtreleme yapmamız gerekiyor. Bu yüzden Filters kısmına da bölüm adı değerini atayıp buradan hukuk seçeneğini seçiyoruz.
Karşımıza hukuk fakültesine hangi liseden kaç öğrenci geldiğini gösteren tablo çıkıyor. Bu tablo için de en çok öğrenci gönderen 2 liseyi bulmak istediğimiz için Count of Okul sütunundaki verileri Data(Veri) kısmına gelerek büyükten küçüğe doğru sırala seçeneği ile sıralıyoruz. Bunun sonucunda BALIKESİR SIRRI YIRCALI ANADOLU LİSESİ ve ÖZEL MEF LİSESİ’nin 4'er öğrenci göndererek hukuk fakültesine en çok öğrenci gönderen liseler olduğu sonucuna ulaşıyoruz.
Tüm bu anlattığımız adımları video ile görelim:
Öğrencilerin yüzde kaçı ilk 3 tercihine yerleşti?
Pivot tablosunda Values kısmına nümerik olmayan bir değer atıyoruz. Örnek olarak yeniden okul verisini atayabiliriz. Tercih sırası bazında bir soru sorulduğu için rows kısmına da tercih sırası değerini atıyoruz.
Bu işlemler sonrasında karşımıza 1'den 28'e kadar bir tercih sırası ve bu değerlerde kaç öğrenci olduğunu içeren tablo çıkıyor. Biz ilk 3 tercihe ulaşmak istediğimiz için Row Labels sütunundaki bir değere sağ tıklayıp Group(Grup) seçeneğini seçiyoruz. Karşımıza hangi değerle başlayacağımız(Starting at) hangi değerle biteceğine(Ending at) ve kaçarlı gruplayacağımız(by) seçenekleri çıkıyor. Burada 3'erli gruplandırmak istediğimiz için by değerine 3 yazıyoruz. Böylelikle ilk 3 tercihine yerleşen öğrenci sayısına ulaşmış oluyoruz:
1–3 : 143 kişi
Tüm bu anlattığımız adımları video ile görelim:
Sizler de bu veri seti üzerinde çalışmak isterseniz link üzerinden 2014 Yerleşenler Örneği dosyasına ulaşabilirsiniz: http://bit.ly/yetgenexcelegitimidosyaları
Bu örneğin aynı zamanda videolu anlatımına bu link üzerinden ulaşabilirsiniz: https://bit.ly/2ODZRpf
Daha fazla pratik için bizleri takipte kalın!
Teşekkürler :)
