FİLTRE/FILTER FONKSİYONU

Fonksiyon Tanıtımı

Filtre fonksiyonu tanıştığım andan itibaren favorim olan bir fonksiyon. Onunla neler yapabileceğinizi öğrendiğinizde sizin de etkileneceğinizden eminim.

Fonksiyonun ana işlevi veri aralıklarını sizin tanımladığınız kriterlere göre filtrelemektir.

Örneğin elimizde aşağıdaki gibi bir tablo olduğunu varsayalım.

Bu tabloda sadece Kitap Türü’nün “Roman” olduğu verileri incelemek istediğimizi düşünelim. Normalde bunun için Excel’de hali hazırda giriş sekmesinin düzenleme alanında bulunan filtre aracını kullanabiliriz.(Ctrl+shift+L kısayolu)

Basitçe ifade edersek Filtre fonksiyonu bu aracın formülize edilmiş halidir. Ancak Filtre fonksiyonu ile çok daha kapsamlı işlemler yapabilmekteyiz.

Filtre İle Tek Kritere Göre Listeleme

Sayfa 1’deki  ana tablodan sadece Kitap Türü “Roman” olanları filtreleyip Sayfa 2’ye yazdırmak istediğimizi varsayalım.Bunun için yazmamız gereken formül aşağıdaki gibidir.Şimdi adım adım parametreleri inceleyelim.

=FİLTRE(Sayfa1!A:K;(Sayfa1!C:C=”Roman”)

Adım 1: Filtrelemek istediğiniz tabloyu seçiniz. (Sayfa 1’deki ana tablo tepeden tüm sütunları alacak şekilde seçilir)

 =FİLTRE(Sayfa1!A:K;(Sayfa1!C:C=”Roman”)

Adım 2: Ana tablodan filtreleyeceğiniz ölçütün olduğu sütunu seçiniz ve filtrelemek istediğiniz ölçütü giriniz.

=FİLTRE(Sayfa1!A:K;(Sayfa1!C:C=”Roman”)

Kitap Türü Roman olanları filtreleyeceğimiz için önce Kitap türü sütununu seçtik (C sütunu) ve sonrasında sadece roman olanların gelmesini istediğimiz için “Roman” değerini girdik.

İşte sonuç, Sayfa2’de (formülü yazdığımız sayfa) sadece kitap türü roman olanları içeren bir tablo görüyoruz. Ancak fonksiyon başlıkları getirmez. Başlıkları da görmek istiyorsanız manuel getirmeniz gerekir.

Filtre İçinde “+” İle Birden Fazla Kriterden Herhangi Birinin Karşılanması

Peki sadece tek ölçüt üzerinden mi filtreleme yapabiliriz? Tabii ki hayır. Nasıl filtre aracında birden fazla ölçütü seçerek filtreleme yapabiliyorsak filtre fonksiyonu ile de aynı kategoride birden fazla ölçüt için filtreleme yapabiliriz. Bunun için her kriter arasına “+” işareti koymamız yeterlidir.

Ölçüt belirleme noktasında kadar formülde aynı şekilde ilerliyoruz.

Adım 1: Filtrelemek istediğiniz tabloyu seçiniz.

Adım 2: Filtreleyeceğiniz ölçütün olduğu sütunu seçiniz ve filtrelemek istediğiniz birinci ölçütü giriniz.

Adım 3: Birinci ölçütten sonra “+” işareti koyarak aynı şekilde ölçütün olduğu sütunu seçiniz ve filtrelemek istediğiniz ikinci ölçütü giriniz.

Örneğin kitap türü roman ya da öykü olan verileri filtrelemek istiyorsak formülümüz aşağıdaki gibidir:

=FİLTRE(Sayfa1!A:K;(Sayfa1!C:C=”Roman”)+(Sayfa1!C:C=”Öykü”))

Bu şekilde “+” koyarak istediğiniz kadar ölçüt ekleyebilirsiniz.

Filtre İçinde “*” İle Birden Fazla Kriterin Aynı Anda Karşılanması

Peki birden fazla sütundaki verileri filtrelemek istiyorsam ne yapmalıyım? Örneğin kitap türü roman olan ama aynı zamanda dili de Türkçe olan verileri incelemek istediğimizde bunu nasıl yapacağız?

Bunun için her kriter arasına “*” işareti koymamız yeterlidir.

(Kitap türü=”Roman” )*(Orijinal Dil=”Türkçe”) olarak yazabiliriz.

Ölçüt belirleme noktasında kadar formülde aynı şekilde ilerliyoruz.

Adım 1: Filtrelemek istediğiniz tabloyu seçiniz.

Adım 2: Filtreleyeceğiniz ilk ölçütün olduğu sütunu seçiniz ve filtrelemek istediğiniz birinci ölçütü giriniz.

Adım 3: Birinci ölçütten sonra “*” işareti koyarak filtreleyeceğiniz ikinci ölçütün olduğu sütunu seçiniz ve filtrelemek istediğiniz ikinci ölçütü giriniz.

Formülümüz aşağıdaki gibidir:

=FİLTRE(Sayfa1!A:K;(Sayfa1!C:C=”Roman”)*(Sayfa1!D:D=”Türkçe”))

Yine kriterler arasına “*” koymaya devam ederek bu filtreleme işlemini daha da özelleştirebilirsiniz.

Filtre İçinde “<>” İle Belirli Bir Kritere Eşit Olmayanlara Göre Listeleme

Peki istenilen kriter değil de istenmeyen bir kriter üzerinden nasıl bir filtreleme yaparız?

Örneğin Orijinal Dili Türkçe olmayanları getirmek istersek nasıl yaparız?

Excel’de eşit olmayan demek için “<>” işaretini kullanırız.

Ölçüt belirleme noktasında kadar formülde aynı şekilde ilerliyoruz.

Adım 1: Filtrelemek istediğiniz tabloyu seçiniz.

Adım 2: Ölçütünüzü içeren sütunu seçiniz ve ölçütünüzün olmasını değil olmamasını istediğiniz için “=” yerine “<>” yazarak ölçütünüzü giriniz.

=FİLTRE(Sayfa1!A:K;Sayfa1!D:D<>”Türkçe”)

Bu işlem bize tabloda Türkçe olanlar hariç kalan tüm değerleri getirir.

Filtre İçinde Filtre ve Süslü Parantez {} ile Belirli Sütunları Listeleme

Filtre fonksiyonu arka planda bizlere ana tablodan seçtiğimiz filtreleme ölçütlerine göre yeni sanal bir tablo oluşturur. Bu sanal tabloda sadece görmek istediğimiz sütunların gelmesini istiyorsak filtre içinde filtre uygulamamız gerekir.

Örneğin tablodan sadece Roman olanları getirmek istiyorsak ama tüm sütunlara da ihtiyacımız yoksa sadece belirli sütunların gelmesini istiyorsak  aşağıdaki gibi bir formül yazmamız gerekir.

Ölçüt belirleme noktasında kadar formülde aynı şekilde ilerliyoruz.

Adım 1: Filtrelemek istediğiniz tabloyu seçiniz.

Adım 2: Filtreleyeceğiniz ölçütün olduğu sütunu seçiniz ve filtrelemek istediğiniz ölçütü giriniz.

=FİLTRE(data!A:K;data!C:C=”Roman”)

Adım 3: Buraya kadar ilk örnekte yaptığımız gibi sadece kitap türü=roman olan verilerin olduğu bir tablo göreceğiz.

Ama tablodaki sadece Kitap ismi,Satış Adedi ve Satış Tutarı sütunları ile çalışmak istiyoruz. O zaman bu yeni oluşan sanal tabloyu tekrar bir filtreleme işlemi içerisine alıyoruz.

Adım 4: Formülü tekrar filtre parantezine alarak süslü parantez açıp görmek istediğimiz sütunlara “1” görmek istemediğimiz sütunlara da “0” değerini yazıyoruz.

=FİLTRE(FİLTRE(Sayfa1!A:K;Sayfa1!C:C=”Roman”);{1;0;0;0;0;0;0;0;1;1;0})

Sütunseç (Choosecols) İçinde Filtre İle Belirli Sütunları Listeleme

Yukarıdaki örnekte süslü parantez içinde 1 ve 0 yazarak ek bir filtreleme işlemi uygulamıştık. Ancak çok fazla sütun içeren bir tabloda sürekli adım adım 0 ve 1 yazmak pek de pratik bir yöntem değil. Tam bu noktada “Sütunseç” fonksiyonu imdadımıza yetişiyor.

Yukarıda sadece 1. Kitap İsmi , 9.Satış Adedi ve 10. Satış Tutarı sütunlarını getirmek istediğimiz için 1,9 ve 10. Sütunlarda 1, diğerlerinde ise 0 yazmıştık.

{1;0;0;0;0;0;0;0;1;1;0}

Sütun seç fonksiyonunda ise sadece getirmek istediğimiz sütunların numarasını yazmamız yeterlidir.

Adım 1: Buraya kadar ilk örnekte yaptığımız gibi sadece kitap türü=roman olan verilerin olduğu bir tablo oluşturacağız.

Adım 2: Sütunseç parantezine alarak sadece görmek istediğimiz (1,9 ve 10) sütunlarının numarasını gireceğiz.

=SÜTUNSEÇ(FİLTRE(Sayfa1!A:K;Sayfa1!C:C=”Roman”);1;9;10)

Yine aynı tabloyu elde etmiş olduk.

Filtre İle Sıralama

Burada hatırlamamız gereken husus filtre fonksiyonunun bize arka planda bir sanal tablo oluşturmasıdır. Oluşturduğumuz tablo sanal bir tablo olduğu için ve filtre fonksiyonu bir dizi fonksiyonu olduğu için formül yazdığımız hücre dışında hiçbir hücreye müdahale edemeyiz. Değerler sadece bir gösterimdir ve veri orada gerçekten varmış gibi işlem yapamayız.

Bu durumda bu sanal tabloda sıralama da yapamayız. O halde kitap türü roman olanları filtrelediğimiz bir tabloda satış adetlerini büyükten küçüğe görmek istersek bunu nasıl yaparız?

Bu noktada filtre ile sıralama fonksiyonunu beraber kullanabiliriz.

Adım 1: Kitap türü roman olanları filtrelediğimiz fonksiyonu yazalım.

=FİLTRE(Sayfa1!A:K;Sayfa1!C:C=”Roman”)

Adım 2 : Sırala parantezine alalım. Hangi sütuna göre sıralamak istiyorsak o sütunun numarasını yazalım(bu örnekte 9.sütundaki satış adetlerini sıralayacağımız için 9 yazıyoruz)

=SIRALA(FİLTRE(Sayfa1!A:K;Sayfa1!C:C=”Roman”);9)

Adım 3 :Büyükten küçüğe sıralamak istiyorsak “-1” , küçükten büyüğe sıralamak istiyorsak “1” yazarız.[Hiçbir şey yazmazsak otomatik küçükten büyüğe (1 varsayarak) sıralama yapar

Büyükten Küçüğe Sıralama

=SIRALA(FİLTRE(Sayfa1!A:K;Sayfa1!C:C=”Roman”);9;-1)

Küçükten Büyüğe Sıralama

=SIRALA(FİLTRE(Sayfa1!A:K;Sayfa1!C:C=”Roman”);9;1)

Büyük & Sırala & Filtre ile İlk n Değeri Listeleme

Her zaman sadece filtreleme yaptığımız sütunu oluşan sanal tabloya dahil etmek zorunda değiliz. Örneğin sadece Satış adedini görmek istiyorsak ve bunu sadece kitap türü roman olanlar için istiyorsak aşağıdaki formülü yazarız.

=FİLTRE(Sayfa1!I:I;Sayfa1!C:C=”Roman”)

Bu formülde demek istenilen ;

Ana tabloda I sütunundaki Satış Adetlerini getir ama sadece C sütunundaki Roman olanları filtreleyerek getir.

Böylece aşağıdaki gibi kitap türü roman olanların satış adetlerinin olduğu tek sütunluk tabloyu elde ederiz.

Bu tabloyu sıralamak için “sırala “ parantezine alırız. Sadece tek sütun olduğu için [sıralama_dizini] kısmına 1 yazmamız yeterlidir. -1 yazarak azalan sıralama yapabiliriz ve aşağıdaki tabloyu elde ederiz.

=SIRALA(FİLTRE(Sayfa1!I:I;Sayfa1!C:C=”Roman”);1;-1)

Burada sadece satış adedi en yüksek 3 romanı görmek istersem “Büyük” parantezine alırım

=BÜYÜK(SIRALA(FİLTRE(Sayfa1!I:I;Sayfa1!C:C=”Roman”);1;-1);{1;2;3})

Bunu alt alta yazılı görmek isterseniz devrik dönüşüm parantezine alabilirsiniz

=DEVRİK_DÖNÜŞÜM(BÜYÜK(SIRALA(FİLTRE(Sayfa1!I:I;Sayfa1!C:C=”Roman”);1;-1);{1;2;3}))

Topla & Büyük & Sırala & Filtre ile İlk n Değerin Toplamını Alma

Satış Adedi en yüksek 3 romanın toplamını görmek isterseniz de son olarak topla parantezine alıp bu üç değerin toplamını yazdırabilirsiniz.

=TOPLA(DEVRİK_DÖNÜŞÜM(BÜYÜK(SIRALA(FİLTRE(Sayfa1!I:I;Sayfa1!C:C=”Roman”);1;-1);{1;2;3})))

Sadece toplam görmek isterseniz “devrik dönüşüm” kısmını eklemenize gerek yoktur aşağıdaki formülü de uygulayabilirsiniz.

=TOPLA(BÜYÜK(SIRALA(FİLTRE(Sayfa1!I:I;Sayfa1!C:C=”Roman”);1;-1);{1;2;3}))

Filtre Fonksiyonunun Çoklu Düşeyara Gibi Kullanımı

Örneğin Abdulsamet Çıkıkcı adlı yazarın tüm kitaplarını görmek istiyoruz. Kitap isimlerini yazara göre filtreleyerek getirmek istiyoruz. Bu durumda ;

Adım 1 : Getirmek istediğim sütun Kitap İsimleri olduğu için ana tablodan A sütununu (Kitap İsmi sütununu seçeriz)

Adım 2 : Sadece Abdulsamet Çıkıkcı’nın kitaplarının gelmesini istediğimiz için B sütunu (Yazar) = Abdulsamet Çıkıkcı olarak filtreleme ölçütümüzü yazarız.

=FİLTRE(Sayfa1!A:A;Sayfa1!B:B=”Abdulsamet Çıkıkcı”)

Bağ_Değ_Dolu_Say (Counta) & Benzersiz (Unique) & Filtre ile Benzersiz Değerlerin Sayılması

Peki bir yazarın kaç farklı dilde kitabı olduğunu görmek istersek?

Abdulsamet Çıkıkcı için baktığımızda ana tablo aşağıdaki gibidir

Arapça = 2

İngilizce = 3

Fransızca = 1

Rusça = 1

Türkçe = 1

Farsça = 1

Toplamda 9 kitabı olan Abdulsamet Çıkıkcı sadece 6 farklı dilde eser sahibidir.

Bunu sadece dilleri filtreleyerek inceleyelim. Orijinal dilleri ,yazar isimleri Abdulsamet Çıkıkcı olacak şekilde filtrelediğimizde aşağıdaki tabloyu görürüz. Yinelenen değerleri de kırmızı dolguyla görmektesiniz. Biz burada kaç farklı dilde eser var bunu bulmak istiyoruz o zaman ilk olarak yinelenen değerlerden kurtulmamız gerekir, her benzersiz değeri tek seferde saymalıyız. Bunun için benzersiz fonksiyonunu kullanırız.

Fonksiyonu benzersiz parantezine aldığımızda aşağıdaki tabloyu elde ederiz. “6” şeklinde net bir rakam istiyorsak da bu değerleri saydırmamız gerekir. Bunun için de Bağ_Değ_Dolu_Say fonksiyonuna ihtiyaç duyarız.

Fonksiyonu Bağ_Değ_Dolu_Say parantezine aldığımızda 6 rakamına ulaşabiliriz.

Yazar: Kübra Kuru