Excel FİLTRE işlevi nasıl kullanılır?

Office 365, XLOOKUP, SIRALAMA ve FİLTRE gibi bazı harika özellikler getiriyor.

Office 365 öncesi dünyada Excel'de verileri filtrelerken, çoğunlukla Excel'in yerleşik filtrelerine veya en gelişmiş filtrelere veya karmaşık SUMPRODUCT formüllerine güvendik.Veri kümesinin bir bölümünü filtrelemeniz gerekiyorsa, bu genellikle karmaşık bir geçici çözümdür.

Ancak yeni FİLTRE özelliğiyle, koşullara göre bir veri kümesinin parçalarını hızla filtrelemek artık gerçekten çok kolay.

Bu eğitimde size yeni FİLTRE özelliğinin ne kadar harika olduğunu ve onunla yapabileceğiniz bazı yararlı şeyleri göstereceğim.

Ancak örneğe geçmeden önce FILTER fonksiyonunun sözdizimine hızlıca bir göz atalım.

Bu yeni özellikleri Excel'de edinmek istiyorsanız, şunları yapabilirsiniz:Office 365'e yükseltin(Tüm özelliklere/formüllere erişmek için dahili programa katılın)

Excel Filtre İşlevi – Sözdizimi

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

=FILTER(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 - Bu, fonksiyona hangi kayıtların filtreleneceğini söyleyen koşuldur.
• [if_boş] – Bu, FILTER işlevi hiçbir sonuç bulamazsa neyin döndürüleceğini belirtebileceğiniz isteğe bağlı bir parametredir.Varsayılan olarak (belirtilmediğinde) #CALC!hata

Şimdi filtre işlevlerinin bazı şaşırtıcı örneklerine ve onsuz çok karmaşık olan ne yapabileceğine bakalım.

Örnek 1: Verileri tek bir koşula (bölgeye) göre filtreleyin

Aşağıdaki gibi bir veri kümeniz olduğunu ve yalnızca Amerika Birleşik Devletleri'ndeki tüm kayıtları filtrelemek istediğinizi varsayalım.

İşte bunu yapan FILTER formülü:

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

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

Bu koşul, FILTER işlevinin B sütunundaki (aralığa sahip hücreler) her hücreyi incelemesine ve yalnızca bu koşulla eşleşen kayıtları filtrelemesine neden olur.

Ayrıca bu örnekte, orijinal ve filtrelenmiş verileri aynı sayfaya koydum, ancak bunları ayrı sayfalara ve hatta çalışma kitaplarına da koyabilirsiniz.

Filtre işlevi, dinamik bir sonuç dizisi döndürür (yani, bir değer döndürmek yerine, diğer hücrelere taşan bir dizi döndürür).

Bunun için sonucun boş olduğu bir alana sahip olmanız gerekir.Aralıktaki herhangi bir hücrede zaten bir şey var (bu örnekte E2:G5) ve işlev size #SPILL hatası verecektir.

Ayrıca, bu dinamik bir dizi olduğu için sonucun bir kısmını değiştiremezsiniz.Sonuç veya E2 hücresiyle (formülün girildiği yer) tüm aralığı silebilirsiniz.Bunların ikisi de tüm sonuç dizisini silecektir.Ancak tek bir hücreyi değiştiremezsiniz (veya silemezsiniz).

Yukarıdaki formülde, aralık değerini sabit kodladım, ancak bunu bir hücreye koyabilir ve aralık değeriyle o hücreye başvurabilirsiniz.

Örneğin, aşağıdaki örnekte, I2 hücresindeki aralık değerine sahibim ve ardından formülde buna başvuruyorum:

=FILTER($A$2:$C$11,$B$2:$B$11=I1)

Bu, formülü daha da kullanışlı hale getirir, şimdi sadece I2 hücresindeki aralık değerini değiştirmeniz yeterlidir ve filtre otomatik olarak değişecektir.

Ayrıca, I2 hücresinde sadece bir seçim yapabileceğiniz ve filtrelenmiş verileri anında güncelleyebileceğiniz bir açılır menüye sahip olabilirsiniz.

Örnek 2: Verileri bir kritere göre filtreleyin (büyük veya küçük)

Ayrıca karşılaştırma operatörlerini filtre işlevlerinde kullanabilir ve belirli bir değerden büyük veya küçük tüm kayıtları çıkarabilirsiniz.

Örneğin, aşağıda gösterilen veri kümesine sahip olduğunuzu ve 10000'in üzerinde satışı olan tüm kayıtları filtrelemek istediğinizi varsayalım.

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

=FILTER($A$2:$C$11,($C$2:$C$11>10000))

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

Formül, C sütunundaki değer için her kaydı kontrol eder.Değer 10000'den büyükse filtrelenir, aksi takdirde yoksayılır.

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

=FILTER($A$2:$C$11,($C$2:$C$11<10000))

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

=FILTER($A$2:$C$11,($C$2:$C$11>=LARGE(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.Ardından, satışları üçüncü en büyük değere eşit veya daha büyük olan tüm kayıtları almak için bu değeri bir FILTER işlevi koşulunda kullanın.

Örnek 3: Birden çok koşul kullanarak verileri filtreleme (VE)

Aşağıdaki veri kümesine sahip olduğunuzu ve Amerika Birleşik Devletleri'ndeki 10000'den büyük satış değerine sahip tüm kayıtları filtrelemek istediğinizi varsayalım.

Bu bir VE koşulu, iki şeyi kontrol etmeniz gerekiyor - bölgenin ABD'de olması ve satışların 10000'in üzerinde olması gerekiyor.Yalnızca bir koşul karşılanırsa, sonuçlar filtrelenmemelidir.

Bölge olarak ABD ve 10000'den fazla satışı olan kayıtları filtreleyecek filtre formülü:

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

請注意，標準（稱為包含參數）是 ($B$2:$B$11=”US”)*($C$2:$C$11>10000)

İki koşul kullandığımdan ve ikisinin de doğru olması gerektiğinden, iki koşulu 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 1 yalnızca her iki koşul da karşılanırsa döndürülür.

Eşleşen kayıt yoksa, işlev #CALC!hata.

Anlamlı bir şey döndürmek istiyorsanız (hata değil), aşağıdaki gibi bir formül kullanabilirsiniz:

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

Burada, eşleşen kayıt bulunmadığında kullanılan üçüncü parametre olarak "bulunamadı" kullandım.

Örnek 4: Birden çok ölçüt (VEYA) kullanarak verileri filtreleme

VEYA koşullarını kontrol etmek için FİLTRE işlevindeki "içerir" parametresini de değiştirebilirsiniz (burada herhangi bir koşul doğru olabilir).

Örneğin, aşağıda gösterilen veri kümesine sahip olduğunuzu ve ülkenin Amerika Birleşik Devletleri veya Kanada olduğu kayıtları filtrelemek istediğinizi varsayalım.

İşte bunu yapmak için formül:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+($B$2:$B$11="Canada"))

Yukarıdaki formülde, toplama operatörünü kullanarak sadece iki koşulu ekliyorum.Bu koşulların her biri DOĞRU ve YANLIŞ dizisini döndürdüğü için, koşullardan herhangi biri karşılanırsa DOĞRU olacak birleşik bir dizi ekleyebilirim.

Diğer bir örnek, ülkenin Amerika Birleşik Devletleri olduğu veya satış değerinin 10000'in üzerinde 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="US")+(C2:C11>10000))

NOT: FİLTRE işlevinde VE koşulunu kullanırken çarpma operatörünü (*) ve VEYA koşulunu kullanırken toplama operatörünü (+) kullanın.

Örnek 5: Ortalamanın üstündeki/altındaki kayıtlar için verileri filtreleyin

Ortalamanın üstünde veya altında değerlere sahip kayıtları filtrelemek ve çıkarmak için FİLTRE işlevindeki formülleri kullanabilirsiniz.

Örneğin, aşağıda gösterilen veri kümeniz olduğunu ve ortalamanın üzerinde bir satış değerine sahip tüm kayıtları filtrelemek istediğinizi varsayalım.

Bunu aşağıdaki formülle yapabilirsiniz:

=FILTER($A$2:$C$11,C2:C11>AVERAGE(C2:C11))

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

=FILTER($A$2:$C$11,C2:C11<AVERAGE(C2:C11))

Örnek 6: Yalnızca çift kayıtları (veya tek kayıtları) filtreleyin

Çift veya tek satırlardaki tüm kayıtları hızlı bir şekilde filtrelemeniz ve ayıklamanız gerekiyorsa, bunu yapmak için FİLTRE işlevini kullanabilirsiniz.

Bunu yapmak için FILTER işlevinde satır numarasını kontrol etmeniz ve yalnızca satır numarası koşuluyla eşleşen satır numaralarını filtrelemeniz gerekir.

Diyelim ki aşağıdaki gibi bir veri kümeniz var ve ben sadece bu veri kümesinden çift kayıtları çıkarmak istiyorum.

İşte bunu yapmak için formül:

=FILTER($A$2:$C$11,MOD(ROW(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ğundan, satır numaralarını ikinci satırı ilk kayıt olarak ele alacak şekilde ayarlayan SATIR(A2:A11) bölümünden 1 çıkardığımı unutmayın.

Aynı şekilde, tüm tek kayıtları aşağıdaki formülle filtreleyebilirsiniz:

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

Örnek 7: Bir formül kullanarak filtrelenmiş verileri sıralama

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

Örneğin, bir veri kümesini filtrelemek için FİLTRE işlevini kullanırsanız, sıralanmış sonuçları almak için SIRALAMA işlevini kullanabilirsiniz.

Diyelim ki aşağıda gösterildiği gibi bir veri kümeniz var ve 10000 üzeri satışları olan tüm kayıtları filtrelemek istiyorsunuz.Elde edilen verilerin satışa göre sıralanmasını sağlamak için SIRALA işlevini bu işlevle birlikte kullanabilirsiniz.

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

=SORT(FILTER($A$2:$C$11,($C$2:$C$11>10000)),3,-1)

Yukarıdaki işlev, satışları 10000'den fazla olan C sütunundaki verileri almak için FILTER işlevini kullanır.Ardından, verileri satışlara göre sıralamak için SIRALA işlevinde FILTER işlevi tarafından döndürülen diziyi kullanın.

SORT işlevindeki ikinci parametre 3'tür ve üçüncü sütuna göre sıralama yapılır.Dördüncü parametre, verileri azalan düzende sıralayan -1'dir.

Bunlar, Excel'de FILTER işlevini kullanmanın 7 örneğidir.

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