Filtrelemeye uygun öğe sayısı sınırlıdır. Excel, öğe sayısının 999'u (satır sayısını değil) aştığı sütunları filtreleyemez.
999'dan fazla öğe olduğunda filtrelemek için gelişmiş filtreyi kullanın.
Gelişmiş bir filtre oluşturmak için Microsoft Excel'de “OFFSET” ve “COUNTA” fonksiyonlarını kullanacağız.
COUNTA: Değerleri içeren hücre sayısının sayısını döndürür.
“COUNTA” fonksiyonunun sözdizimi: =COUNTA (değer1, değer2, değer3….)
Örnek: A1:A5 aralığında, A2, A3 ve A5 hücreleri değerleri içerir ve A1 ve A4 hücreleri boştur. A6 hücresini seçin ve formülü yazın-
=COUNTA(A1:A5) fonksiyon geri dönecek 3
TELAFİ ETMEK: Başka bir aralıktan veya hücreden bir dizi satır ve sütunu kaydıran bir aralığa başvuru döndürür.
OFFSET işlevinin sözdizimi: =OFFSET (referans, satırlar, sütunlar, yükseklik, genişlik)
Referans:- Bu, mahsup etmek istediğiniz hücre veya aralıktır.
Taşınacak Satırlar ve Sütunlar: - Başlangıç noktasından hareket ettirmek istediğiniz satır sayısı ve bunların ikisi de pozitif, negatif veya sıfır olabilir.
Yükseklik ve genişlik: - Bu, döndürmek istediğiniz aralığın boyutudur. Bu isteğe bağlı bir alandır.
Excel'deki Ofset işlevini anlamak için bir örnek alalım.
A1:D10 aralığında verilerimiz var. Sütun A Ürün Kodu'nu, Sütun B Miktar'ı, C sütunu ürün başına maliyeti ve D sütunu Toplam maliyeti içerir. E2 hücresindeki C5 hücresinin değerini döndürmemiz gerekiyor.
İstenen sonucu elde etmek için aşağıda belirtilen adımları izlememiz gerekir.
- E2 hücresini seçin ve formülü yazın.
- =OFFSET(A1,4,2,1,1)ve klavyede Enter tuşuna basın.
- İşlev, C5 hücresinin değerini döndürür.
Bu örnekte, C5 hücresinden E2'ye kadar olan değeri elde etmemiz gerekiyor. Referans hücremiz, A1 ve C5 aralığındaki ilk hücredir ve A1'den 4 satır aşağıda ve 2 sütun sağdadır. Bu nedenle formül =OFFSET(A1,4,2,1,1) veya =OFFSET(A1,4,2) (1,1 isteğe bağlı olduğundan) şeklindedir.
Şimdi dinamik bir listedeki son değeri almak için bir örnek alalım.
Bir aralıkta ülke isimlerimiz var. Şimdi, bu listeye daha fazla ülke eklersek, açılır listede otomatik olarak mevcut olmalıdır.
Gelişmiş filtre hazırlamak için aşağıdaki adımları izleyin: -
- B2 hücresini seçin.
- Veri sekmesine gidin, Veri araçları grubundan Veri Doğrulama'yı seçin.
- “Veri Doğrulama” iletişim kutusu görünecektir. "Ayarlar" sekmesinde, İzin Ver açılır listesinden "Özel"i seçin.
- Formül kutusu etkinleştirilecektir.
- Formülü bu kutuya yazın.
- =OFFSET(A:A,1,0,COUNTA(A:A)-1,1).
- Tamam'a tıklayın.
- Bu aşamada en son güncellenen hücre A11'dir.
- Veri Doğrulamanın düzgün çalışıp çalışmadığını kontrol etmek için A12 hücresine bir şehir adı ekleyin.
A12'ye bir girdi ekler eklemez, açılır listeye eklenecektir.
Bu, Microsoft Excel'de 999 öğeden daha fazla giriş ekleyebilmemizin yoludur.