Excel Filtre İşlevi - Örneklerle Açıklamalı + Video

Videoyu İzle - Excel FİLTRE İşlev Örnekleri

Office 365, XLOOKUP, SIRALAMA ve FİLTRE gibi bazı harika işlevler sunar.

Excel'deki verileri filtrelemeye gelince, Office 365 öncesi dünyada, çoğunlukla Excel'in yerleşik filtresine veya maksimumda Gelişmiş filtreye veya karmaşık SUMPRODUCT formüllerine bağımlıydık. Bir veri kümesinin bir bölümünü filtrelemeniz gerekiyorsa, bu genellikle karmaşık bir geçici çözümdü (burada ele aldığım bir şey).

Ancak yeni FILTER işleviyle, bir koşula göre veri kümesinin bir bölümünü hızla filtrelemek artık gerçekten çok kolay.

Ve bu derste size yeni FILTER fonksiyonunun ne kadar harika olduğunu ve bununla yapabileceğiniz bazı faydalı şeyleri göstereceğim.

Ancak örneklere geçmeden önce FILTER fonksiyonunun sözdizimini hızlıca öğrenelim.

Bu yeni özellikleri Excel'de edinmek isterseniz, şunları yapabilirsiniz: Office 365'e yükseltme (tüm özelliklere/formüllere erişmek için içeriden öğrenen programa katılın)

Excel Filtre İşlevi - Sözdizimi

FILTER işlevinin sözdizimi aşağıdadır:

=FİLTRE(dizi,içerir,[eğer_boşsa])

• dizi - bu, verilere sahip olduğunuz ve ondan bazı verileri filtrelemek istediğiniz hücre aralığıdır.
• Dahil etmek - bu, fonksiyona hangi kayıtların filtreleneceğini söyleyen koşuldur.
• [if_boş] - bu, FILTER işlevi tarafından hiçbir sonuç bulunamaması durumunda ne döndürüleceğini belirtebileceğiniz isteğe bağlı bir argümandır. Varsayılan olarak (belirtilmediğinde), #CALC! hata

Şimdi, bazı şaşırtıcı Filtre işlevi örneklerine ve yokluğunda oldukça karmaşık olan yapabileceği şeylere bir göz atalım.

Örnek dosyayı indirmek ve takip etmek için buraya tıklayın

Örnek 1: Verileri Tek Bir Kritere (Bölge) Göre Filtreleme

Aşağıda gösterildiği gibi bir veri kümeniz olduğunu ve tüm kayıtları yalnızca ABD için filtrelemek istediğinizi varsayalım.

Bunu yapacak FILTER formülü aşağıdadır:

=FİLTRE($A$2:$C$11,$B$2:$B$11="ABD")

Yukarıdaki formül dizi olarak veri kümesini kullanır ve koşul $B$2:$B$11=”US” şeklindedir.

Bu koşul, FILTER işlevinin B sütunundaki (bölgeye sahip olan) her hücreyi kontrol etmesini sağlar ve yalnızca bu kritere uyan kayıtlar filtrelenir.

Ayrıca, bu örnekte, aynı sayfada orijinal veriler ve filtrelenmiş veriler var, ancak bunları ayrı sayfalarda ve hatta çalışma kitaplarında da alabilirsiniz.

Filtre İşlevi, dinamik bir dizi olan bir sonuç döndürür (bu, bir değer döndürmek yerine diğer hücrelere yayılan bir dizi döndürdüğü anlamına gelir).

Bunun çalışması için sonucun boş olacağı bir alana sahip olmanız gerekir. Bu alandaki hücrelerin herhangi birinde (bu örnekte E2:G5) zaten içinde bir şey varsa, işlev size #SPILL hatası verecektir.

Ayrıca bu dinamik bir dizi olduğu için sonucun bir kısmını değiştiremezsiniz. Sonucu içeren tüm aralığı veya E2 hücresini (formülün girildiği yer) silebilirsiniz. Bunların her ikisi de ortaya çıkan tüm diziyi siler. Ancak herhangi bir hücreyi değiştiremezsiniz (veya silemezsiniz).

Yukarıdaki formülde, bölge değerini sabit kodladım, ancak bunu bir hücrede alabilir ve ardından bölge değerine sahip olan hücreye başvurabilirsiniz.

Örneğin, aşağıdaki örnekte, I2 hücresinde bölge değerine sahibim ve buna formülde başvurulur:

=FİLTRE($A$2:$C$11,$B$2:$B$11=I1)

Bu, formülü daha da kullanışlı hale getirir ve şimdi I2 hücresindeki bölge değerini değiştirebilirsiniz; filtre otomatik olarak değişecektir.

Ayrıca, I2 hücresinde, basitçe seçimi yapabileceğiniz ve filtrelenmiş verileri anında güncelleyebileceğiniz bir açılır listeye sahip olabilirsiniz.

Örnek 2: Verileri Bir Kritere Göre Filtreleme (Fazlası veya Azı)

Ayrıca, filtre işlevi içinde karşılaştırma operatörlerini kullanabilir ve belirli bir değerden daha fazla veya daha az olan tüm kayıtları çıkarabilirsiniz.

Örneğin, aşağıda gösterildiği gibi bir veri kümeniz olduğunu ve satış değerinin 10000'den fazla olduğu tüm kayıtları filtrelemek istediğinizi varsayalım.

Aşağıdaki formül bunu yapabilir:

=FİLTRE($A$2:$C$11,($C$2:$C$11>10000))

Dizi bağımsız değişkeni tüm veri kümesine başvurur ve bu durumda koşul ($C$2:$C$11>10000) şeklindedir.

Formül, Sütun C'deki değer için her kaydı kontrol eder. Değer 10000'den büyükse filtrelenir, yoksa yoksayılır.

10000'den küçük tüm kayıtları almak istiyorsanız aşağıdaki formülü kullanabilirsiniz:

=FİLTRE($A$2:$C$11,($C$2:$C$11<10000))

FİLTRE formülüyle de daha yaratıcı olabilirsiniz. Örneğin, satış değerine göre ilk üç kaydı filtrelemek istiyorsanız aşağıdaki formülü kullanabilirsiniz:

=FİLTRE($A$2:$C$11,($C$2:$C$11>=BÜYÜK(C2:C11,3)))

Yukarıdaki formül, veri kümesindeki üçüncü en büyük değeri elde etmek için BÜYÜK işlevini kullanır. Bu değer daha sonra, satış değerinin üçüncü en büyük değere eşit veya daha fazla olduğu tüm kayıtları almak için FİLTRE işlevi ölçütlerinde kullanılır.

Örnek dosyayı indirmek ve takip etmek için buraya tıklayın

Örnek 3: Verileri Birden Çok Kriter (VE) ile Filtreleme

Aşağıdaki veri setine sahip olduğunuzu ve satış değerinin 10000'den fazla olduğu ABD için tüm kayıtları filtrelemek istediğinizi varsayalım.

Bu, iki şeyi kontrol etmeniz gereken bir AND koşuludur - bölgenin ABD'ye ve satışların 10000'den fazla olması gerekir. Yalnızca bir koşul karşılanırsa, sonuçlar filtrelenmemelidir.

Bölge olarak ABD ve 10000'den fazla satış olan kayıtları filtreleyecek FİLTRE formülü aşağıdadır:

=FILTER($A$2:$C$11,($B$2:$B$11="ABD")*($C$2:$C$11>10000))

Ölçütü (include argümanı olarak adlandırılır) ($B$2:$B$11=”US”)*($C$2:$C$11>10000) olduğuna dikkat edin.

İki koşul kullandığımdan ve ikisinin de doğru olması gerektiğinden, bu iki kriteri birleştirmek için çarpma operatörünü kullandım. Bu, 0'lar ve 1'lerden oluşan bir dizi döndürür, burada yalnızca her iki koşul da karşılandığında 1 döndürülür.

Kriterleri karşılayan kayıt olmaması durumunda fonksiyon #CALC! hata.

Ve (hata yerine) anlamlı bir şey döndürmek isterseniz, aşağıda gösterildiği gibi bir formül kullanabilirsiniz:

=FILTER($A$2:$C$11,($B$2:$B$11="ABD")*($C$2:$C$11>10000),"Hiçbir Şey Bulunamadı")

Burada, kriterlere uyan kayıt bulunamadığında kullanılan üçüncü argüman olarak “Bulunamadı” kullandım.

Örnek 4: Birden Çok Kriterli Verileri Filtreleme (VEYA)

Bir VEYA ölçütü olup olmadığını (verilen koşullardan herhangi birinin doğru olabileceği durumlarda) kontrol etmek için FİLTRE işlevindeki 'include' argümanını da değiştirebilirsiniz.

Örneğin, aşağıda gösterildiği gibi veri kümeniz olduğunu ve ülkenin ABD veya Kanada olduğu kayıtları filtrelemek istediğinizi varsayalım.

Bunu yapacak formül aşağıdadır:

=FILTER($A$2:$C$11,($B$2:$B$11="ABD")+($B$2:$B$11="Kanada"))

Yukarıdaki formülde, toplama operatörünü kullanarak iki koşulu basitçe eklediğimi unutmayın. Bu koşulların her biri bir DOĞRU ve YANLIŞ dizisi döndürdüğünden, koşullardan herhangi biri karşılandığında TRUE olan birleşik bir dizi elde etmek için ekleme yapabilirim.

Başka bir örnek, ülkenin ABD olduğu veya satış değerinin 10000'den fazla olduğu tüm kayıtları filtrelemek istediğinizde olabilir.

Aşağıdaki formül bunu yapacaktır:

=FILTER($A$2:$C$11,($B$2:$B$11="ABD")+(C2:C11>10000))

Not: Bir FİLTRE işlevinde AND ölçütü kullanırken çarpma operatörünü (*) ve VEYA ölçütü kullanırken toplama operatörünü (+) kullanın.

Örnek 5: Ortalama Kayıtların Üstüne/Altına Çıkmak İçin Verileri Filtreleme

Değerin ortalamanın üstünde veya altında olduğu kayıtları filtrelemek ve ayıklamak için FİLTRE işlevindeki formülleri kullanabilirsiniz.

Örneğin, aşağıdaki gibi bir veri kümeniz olduğunu ve satış değerinin ortalamanın üzerinde olduğu tüm kayıtları filtrelemek istediğinizi varsayalım.

Bunu aşağıdaki formülü kullanarak yapabilirsiniz:

=FİLTRE($A$2:$C$11,C2:C11>ORTALAMA(C2:C11))

Benzer şekilde, ortalamanın altı için aşağıdaki formülü kullanabilirsiniz:

=FİLTRE($A$2:$C$11,C2:C11<>

Örnek dosyayı indirmek ve takip etmek için buraya tıklayın

Örnek 6: Yalnızca ÇİFT Sayı Kayıtlarını (veya TEK Sayı Kayıtlarını) Filtreleme

Çift sayılı satırlardan veya tek sayılı satırlardan tüm kayıtları hızlı bir şekilde filtrelemeniz ve ayıklamanız gerekiyorsa, bunu FİLTRE işleviyle yapabilirsiniz.

Bunu yapmak için FİLTRE işlevindeki satır numarasını kontrol etmeniz ve yalnızca satır numarası kriterlerini karşılayan satır numaralarını filtrelemeniz gerekir.

Diyelim ki aşağıda gösterildiği gibi bir veri setiniz var ve ben bu veri setinden sadece çift sayılı kayıtları çıkarmak istiyorum.

Bunu yapacak formül aşağıdadır:

=FILTER($A$2:$C$11,MOD(SATIR(A2:A11)-1,2)=0)

Yukarıdaki formül, her kaydın satır numarasını (SATIR işlevi tarafından verilen) kontrol etmek için MOD işlevini kullanır.

MOD(SATIR(A2:A11)-1,2)=0 formülü, satır numarası çift olduğunda DOĞRU, tek olduğunda YANLIŞ değerini döndürür. İlk kayıt ikinci satırda olduğu için SATIR(A2:A11) kısmından 1 çıkardığımı ve bunun satır numarasını ikinci satırı ilk kayıt olarak kabul edecek şekilde ayarladığını unutmayın.

Benzer şekilde, aşağıdaki formülü kullanarak tüm tek sayılı kayıtları filtreleyebilirsiniz:

=FILTER($A$2:$C$11,MOD(SATIR(A2:A11)-1,2)=1)

Örnek 7: Filtrelenmiş Verileri Formülle Sıralayın

FİLTRE işlevini diğer işlevlerle birlikte kullanmak çok daha fazlasını yapmamızı sağlar.

Örneğin, FILTER işlevini kullanarak bir veri kümesini filtrelerseniz, zaten sıralanmış olan sonucu elde etmek için SIRALAMA işlevini onunla birlikte kullanabilirsiniz.

Aşağıda gösterildiği gibi bir veri kümeniz olduğunu ve satış değerinin 10000'den fazla olduğu tüm kayıtları filtrelemek istediğinizi varsayalım. Sonuç verilerinin satış değerine göre sıralandığından emin olmak için SIRALA işlevini işlevle birlikte kullanabilirsiniz.

Aşağıdaki formül bunu yapacaktır:

=SIRALA(FİLTRE($A$2:$C$11,($C$2:$C$11>10000)),3,-1)

Yukarıdaki işlev, C sütunundaki satış değerinin 10000'den fazla olduğu verileri almak için FİLTRE işlevini kullanır. FİLTRE işlevi tarafından döndürülen bu dizi, daha sonra, bu verileri satış değerine göre sıralamak için SIRALAMA işlevi içinde kullanılır.

SORT işlevindeki ikinci argüman, üçüncü sütuna göre sıralamak için 3'tür. Ve dördüncü argüman, bu verileri azalan düzende sıralamak için -1'dir.

Örnek dosyayı indirmek için buraya tıklayın

Bunlar Excel'de FILTER işlevini kullanmak için 7 örnektir.

Umarım bu öğreticiyi faydalı bulmuşsunuzdur!

Aşağıdaki Excel eğitimlerini de beğenebilirsiniz:

1. Excel'de Kalın Yazı Tipi Biçimlendirmesiyle Hücreleri Filtreleme
2. Dinamik Excel Filtresi Arama Kutusu
3. Excel'de Özet Tablodaki Verileri Filtreleme