Kısa bir süre önce büyük miktarda veriyi kolayca özetlemek için Excel'de özet işlevleri nasıl kullanılır tarihinde bir makale yazdım, ancak bu makale çalışma sayfasındaki tüm verileri hesaba kattı. Yalnızca bir veri alt kümesine bakmak ve verilerin alt kümesini özetlemek isterseniz ne olur?
Excel'de, filtrenizle uyuşmayan satırları gizleyen sütunlarda filtreler oluşturabilirsiniz. Buna ek olarak, verileri yalnızca filtrelenmiş verileri kullanarak özetlemek için Excel'deki özel işlevleri de kullanabilirsiniz.
Bu makalede, Excel'de filtreler oluşturma adımlarını ve aynı zamanda yerleşik kullanımını da kullanacağım. filtrelenmiş verileri özetleyen işlevler.
Excel'de Basit Filtreler Oluşturma
Excel'de basit filtreler ve karmaşık filtreler oluşturabilirsiniz. Basit filtrelerle başlayalım. Filtrelerle çalışırken, etiketler için her zaman en üstte bir satır olmalıdır. Bu sıranın olması şart değil ama filtrelerle çalışmayı biraz daha kolaylaştırıyor.
Yukarıda, bazı sahte verilerim var. Şehirsütununda bir filtre oluşturmak istiyorum. Excel'de, bu gerçekten çok kolay. Devam edin ve şeridin Verisekmesini tıklayın ve ardından Filtredüğmesini tıklayın. Sayfadaki verileri seçmek zorunda kalmazsınız veya ilk satırda da tıklayabilirsiniz.
Filtreyi tıklattığınızda, her sütun İlk satırda otomatik olarak sağdaki küçük bir açılır kapanır düğmesi eklenir.
Şimdi devam edin ve aşağı açılan oka tıklayın. Şehir sütunu. Aşağıda açıklayacağım birkaç farklı seçenek göreceksiniz.
Üstte, tüm satırları hızlı bir şekilde sıralayabilirsiniz. Şehir sütunundaki değerler. Verileri sıraladığınızda, yalnızca tüm satırları şehir sütunundaki değerleri değil taşıyacağını unutmayın. Bu, verilerinizin tıpkı önceki gibi olduğu gibi kalmasını sağlar.
Ayrıca, önüne en çok ID adı verilen bir sütun eklemeniz ve bir satırdan bir veya daha fazla satır eklemeniz iyi bir fikirdir. çalışma kağıdı. Bu şekilde, her zaman kimlik sütununa göre sıralayabilir ve verilerinizi sizin için önemliyse, orijinal olarak aynı sırada geri alabilirsiniz.
Gördüğünüz gibi, e-tablodaki tüm veriler artık Şehir sütunundaki değerlere göre sıralanmaktadır. Şimdiye kadar, hiçbir satır gizlenmez. Şimdi, filtre iletişim kutusunun altındaki onay kutularını inceleyelim. Örneğimde, Şehir sütununda yalnızca üç benzersiz değere sahibim ve bu üç kişi listede görünecek.
Devam ettim ve işaretlenmedim iki şehir ve bir tane kontrol edildi. Şimdi sadece 8 satırlık veri gösteriyorum ve geri kalanlar gizlendi. En soldaki satır numaralarını kontrol ederseniz, filtrelenmiş verilere baktığınızı kolayca anlayabilirsiniz. Kaç satırın gizlendiğine bağlı olarak, birkaç ekstra yatay çizgi görürsünüz ve sayıların rengi mavi olur.
Şimdi, sayıyı daha da azaltmak için ikinci bir sütuna filtre uygulamak istediğimi söyleyelim sonuçların C sütununda, her bir ailede toplam üye sayısına sahibim ve yalnızca ikiden fazla üyesi olan ailelerin sonuçlarını görmek istiyorum.
Devam edin ve C Sütunundaki açılır oku tıklayın. Sütundaki her benzersiz değer için aynı onay kutularını göreceksiniz. Ancak, bu durumda, Numara Filtreleri'ni tıklayıp Daha Büyük' yi tıklayın. Gördüğünüz gibi, bir dizi başka seçenek de var.
Yeni bir iletişim kutusu açılır ve burada değeri yazabilirsiniz. filtre için. Ayrıca, bir VEYA OR işleviyle birden fazla ölçüt de ekleyebilirsiniz. Örneğin 2'den büyük ve 5'e eşit olmayan satırlar istediğinizi söyleyebiliriz.
Şimdi aşağıdayım Sadece 5 satırlık veri: sadece New Orleans'tan ve 3 veya daha fazla üyeden oluşan aileler. Yeterince kolay? Bir sütundaki bir filtreyi, açılır menüden tıklayarak ve ardından "Sütun Adı" Filtresini Temizlebağlantısını tıklayarak kolayca temizleyebileceğinizi unutmayın.
Bu, Excel'deki basit filtreler için konuyla ilgili. Kullanımı oldukça kolaydır ve sonuçlar oldukça düzdür. Şimdi Gelişmişfiltreler iletişim kutusunu kullanarak karmaşık filtrelere bir göz atalım.
Excel'de Gelişmiş Filtreler Oluşturma
Daha gelişmiş filtreler oluşturmak isterseniz, Gelişmişfiltre iletişim kutusunu kullanmanız gerekir. Örneğin, New Orleans'ta yaşayan tüm aileleri ailelerinde VEYAailesindeki tüm aileleri VEYAailesinde 3'ten fazla üyesi bulunan tüm aileleri görmek istediğimi varsayalımyalnızca .EDUbiten e-posta adresine sahip olanlar. Artık bunu basit bir filtreyle yapamazsınız.
Bunu yapmak için Excel sayfasını biraz farklı şekilde kurmamız gerekiyor. Devam edin ve veri kümenizin üzerine birkaç satır ekleyin ve başlık etiketlerini tam olarak aşağıda gösterildiği gibi ilk satıra kopyalayın.
Şimdi Gelişmiş filtreler nasıl çalışır. Öncelikle ölçütlerinizi en üstteki sütunlara yazmanız ve ardından Sırala ve amp; altındaki Gelişmişdüğmesini tıklamanız gerekir. Verisekmesindefiltrele
Peki, bu hücrelere tam olarak ne yazabiliriz? Tamam, şimdi örneğimizle başlayalım. Sadece New Orleans veya Clarksville'den veri görmek istiyoruz, bu yüzden bunları E2 ve E3 hücrelerine yazalım.
Farklı değerlere yazdığınızda satırlar, OR anlamına gelir. Şimdi, ikiden fazla üyeye ve 3'ten fazla üyeye sahip Clarksville ailelerine sahip New Orleans ailelerini istiyoruz. Bunu yapmak için C2’de & gt; 2, C3’te & gt; 3yazın.
2 ve New Orleans aynı sırada olduğundan, bir AND operatörü olacak. Aynısı yukarıdaki 3. satır için de geçerlidir. Son olarak, sadece .EDU e-posta adresini biten aileleri istiyoruz. Bunu yapmak için, hem D2 hem de D3'e *. Eduyazın. * Sembolü herhangi bir sayıda karakter anlamına gelir.
Bunu yaptıktan sonra, veri kümenizdeki herhangi bir yeri tıklayın ve ardından Gelişmiş seçeneğini tıklayın.düğmesi. Liste Korualanı, Gelişmiş düğmesini tıklamadan önce tıklama yaptığınız için otomatik olarak veri kümenizi bulur. Şimdi Ölçüt aralığıdüğmesinin sağındaki küçük küçük düğmeyi tıklayın.
A1'den E3'e kadar her şeyi seçin ve ardından Gelişmiş Filtre'ye geri dönmek için aynı düğmeye tekrar tıklayın. Tamam'ı tıklayın ve verileriniz şimdi filtrelenmelidir!
Gördüğünüz gibi, şimdi tüm bu kriterlere uyan sadece 3 sonuç var. Ölçüt aralığı için etiketlerin, bunun çalışması için tam olarak veri kümesi etiketleriyle eşleşmesi gerektiğini unutmayın.
Açıkça bu yöntemi kullanarak çok daha karmaşık sorgular oluşturabilirsiniz, bu yüzden onunla oynayın İstediğiniz sonuçları almak için. Son olarak, toplama işlevlerini filtrelenmiş verilere uygulama hakkında konuşalım.
Filtrelenmiş Verileri Özetleme
Şimdi, filtrelenmiş verilerimin üzerindeki aile üyelerini toplamak istiyorum. bunu yapmaktan mı geçti? Peki, şeridimizdeki Sildüğmesini tıklayarak filtremizi temizleyelim. Endişelenmeyin, Advanced (Gelişmiş) düğmesine tıklatarak ve tekrar OK düğmesine basarak tekrar gelişmiş filtreyi uygulamak çok kolay.
Veri kümemizin altında, Toplamadlı bir hücre ekleyelim ve ardından toplam aile üyelerini toplamak için bir toplam işlevi ekleyelim. Örneğimde = SUM (C7: C31)yazdım.
Yani ailemde toplam 78 üye var. Şimdi devam edelim ve Gelişmiş filtremizi tekrar uygulayıp neler olduğunu görün.
Whoops! Doğru numarayı göstermek yerine, 11, hala toplamın 78 olduğunu görüyorum! Neden? Peki, SUM işlevi gizli satırları görmezden gelmez, bu yüzden hala tüm satırları kullanarak hesaplama yapıyor. Neyse ki, gizli satırları yok saymak için kullanabileceğiniz birkaç fonksiyon var.
Birincisi SUBTOTAL. Bu özel işlevlerden herhangi birini kullanmadan önce filtrenizi temizlemeniz ve ardından işlevi yazmanız istenecektir.
Filtre temizlendikten sonra devam edip = SUBTOTAL (ve bir grup seçenekle bir açılır kutu göreceksiniz. Bu işlevi kullanarak, önce bir sayı kullanarak kullanmak istediğiniz toplama işlevinin türünü seçersiniz.
Örneğimizde kullanmak istiyorum SUM, bu yüzden 9 sayısını yazabilir veya açılır listeden tıklayabilirim. Sonra virgül yazıp hücre aralığını seç.
Enter'a bastığınızda, 78'in değeri daha önce olduğu gibi görmeniz gerekir. Ancak, şimdi filtreyi tekrar uygularsanız, 11'i göreceğiz!
Mükemmel! Tam olarak istediğimiz budur.Şimdi filtrelerinizi ayarlayabilir ve değer her zaman sadece şu anda gösterilen satırları yansıtacaktır.
SUBTOTAL işleviyle hemen hemen aynı şekilde çalışan ikinci işlev AGGREGATE. Diğer bir deyişle, AGGREGATE işlevinde gizli satırları yok saymak istediğinizi belirtmeniz gereken başka bir parametre vardır.
İlk parametre Kullanmak istediğiniz toplama fonksiyonudur ve SUBTOTAL ile olduğu gibi, 9 SUM fonksiyonunu temsil eder. İkinci seçenek, gizli satırları yok saymak için 5 yazmanız gereken yerdir. Son parametre aynıdır ve hücre aralığıdır.
Ayrıca, AGGREGATE işlevini kullan ve MODE, MEDIAN, AVERAGE vb. gibi diğer işlevlerin daha ayrıntılı olarak nasıl yapılacağını öğrenmek için özet işlevler hakkındaki makalemi okuyabilirsiniz.
Umarım Makale, Excel'de filtre oluşturmak ve kullanmak için iyi bir başlangıç noktası sağlar. Herhangi bir sorunuz varsa, yorum göndermekten çekinmeyin. Tadını çıkarın!