Veri doğrulama listesi (Data Validation List), bir hücreye yalnızca önceden belirlediğiniz değerlerin açılır listeden girilmesini sağlayan özelliktir. Böylece aynı şehir ya da ürün adı farklı yazımlarla kaydedilmez; kayıtlar düzenli kalır, süzme ve toplama işlemleri hatasız çalışır. Listeyi Veri > Veri Araçları > Veri Doğrulama (Data > Data Tools > Data Validation) yoluyla kurarsınız.
Taşan diziyi kaynak yapmak için formülün ilk hücresine diyez işaretiyle başvurun; örneğin formül E2 hücresindeyse:
İkinci örnekte bu yöntemin tablosunu görebilirsiniz.
Formül taşma aralığına yazdığından sonuç alanının altındaki hücrelerin boş olması gerekir. FİLTRE işlevi Microsoft 365 ve Excel 2021 sürümlerinde bulunur.
F2 hücresinden bir şehir seçtiğinizde üç sonuç birlikte güncellenir. Seçili şehir için kayıt sayısı 4, toplam satış tutarı 46.800,00 ve ortalama tutar 11.700,00 olarak hesaplanır. Başka bir şehir seçtiğinizde formülleri değiştirmeniz gerekmez.
E2 hücresindeki formül listenin ilk değerini (İnsan Kaynakları) verir ve diğer departmanları altındaki hücrelere taşırır. Bu ilk departmanda çalışan sayısı 1 olarak görünür. Başka bir hücreye açılır liste tanımlarken Kaynak kutusuna E2 hücresinin taşma aralığına başvuruyu yazın (bölümde gösterilen diyezli başvuru); böylece yeni bir departman eklendiğinde liste kendiliğinden genişler. Verilerinizi tabloya çevirirseniz formülün başvurduğu aralık da yeni kayıtlarla büyür.
F2 hücresindeki tek formül, E2'de seçili kategoriye ait ürünleri aşağı doğru taşırır; ilk ürün Çamaşır Deterjanı olarak görünür. E2'den başka bir kategori seçtiğinizde liste kendiliğinden yenilenir. Taşma alanındaki hücreler boş olmazsa Excel #TAŞMA! hatası verir.
Excel'de açılır liste nasıl oluşturulur
Önce liste değerlerini bir sütuna alt alta yazın, sonra listeyi uygulayacağınız hücreleri seçin. Veri > Veri Araçları > Veri Doğrulama (Data > Data Tools > Data Validation) penceresinde şunları ayarlayın:- İzin Ver: Liste (List) seçeneğini seçin.
- Kaynak: (Source) Liste aralığını fareyle seçin ya da değerleri liste ayırıcısıyla ayırarak yazın. Türkçe bölgesel ayarlarda ayırıcı genellikle noktalı virgüldür; yazılan kaynak en fazla 255 karakter olabilir.
- Hücre içi açılır liste: (In-cell dropdown) kutusu işaretli olmalıdır; işareti kaldırırsanız hücrede ok görünmez.
- Hata Uyarısı: (Error Alert) sekmesinde Dur (Stop) stilini seçerseniz liste dışı değer girilemez; Uyarı (Warning) ve Bilgi (Information) stilleri girişe izin verir.
- Giriş İletisi: (Input Message) hücre seçildiğinde kısa bir yönerge gösterir.
Liste kaynağını otomatik genişletme
Liste aralığını sabit seçerseniz sonradan eklediğiniz değerler açılır listeye girmez. Bunu üç yolla çözebilirsiniz:- Tablo: Liste sütununu Ekle > Tablo (Insert > Table) ile tabloya çevirin; tablo yeni satırlarla birlikte büyür.
- Ad: Formüller > Tanımlı Adlar > Ad Yöneticisi (Formulas > Defined Names > Name Manager) ile yeni bir ad tanımlayın ve Başvuru yeri kutusunda tablonun ilgili sütununu fareyle seçin. Doğrulama kutusunda tablo başvurusu doğrudan çalışmadığı için ad bir köprü görevi görür.
- Taşan dizi: Benzersiz değerleri bir formülle üretip taşma aralığını kaynak olarak kullanın. Bu yöntem taşma özelliği bulunan Microsoft 365 ve Excel 2021 gibi sürümlerde çalışır.
Excel formülü
=Sehirler
Excel formülü
=$E$2#Açılır listeyle kayıtları süzme
Açılır liste yalnızca hücreye yapılan girişi denetler; kayıtları kendisi süzmez. Süzme için iki yol vardır:- Otomatik filtre: Tabloya tıklayıp Veri > Sırala ve Filtre > Filtre (Data > Sort & Filter > Filter) komutunu kullanın; sütun başlığındaki okla değer seçerek kayıtları daraltırsınız. Tablo kullanıyorsanız Tablo Tasarımı > Dilimleyici Ekle (Table Design > Insert Slicer) ile düğmeli süzme de ekleyebilirsiniz.
- Formülle süzme: Açılır liste hücresini ölçüt olarak kullanın. EĞERSAY, ETOPLA ve EĞERORTALAMA seçilen değere göre sayı, toplam ve ortalama verir; FİLTRE işlevi ise eşleşen kayıtları ayrı bir alana listeler.
Formül taşma aralığına yazdığından sonuç alanının altındaki hücrelerin boş olması gerekir. FİLTRE işlevi Microsoft 365 ve Excel 2021 sürümlerinde bulunur.
1. Seçilen şehre göre kayıt sayısı ve toplam
Satış kayıtları A1:C11 aralığındadır. F2 hücresine Veri Doğrulama ile Liste kuralı verin ve Kaynak kutusuna şehir adlarını liste ayırıcısıyla ayırarak yazın. F3, F4 ve F5 hücreleri F2'de seçilen şehre göre hesaplanır.Veri Doğrulama Listeleriyle Kayıtları Düzenleme ve Süzme
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Personel | Şehir | Satış Tutarı | Ölçüt | Sonuç | |
| 2 | Ayşe Demir | Ankara | 12.500,00 | Seçilen şehir | Ankara | |
| 3 | Mehmet Kaya | İzmir | 9.800,00 | Kayıt sayısı | 4 | |
| 4 | Zeynep Arslan | Ankara | 15.200,00 | Toplam tutar | 46.800,00 | |
| 5 | Can Yıldız | Bursa | 7.400,00 | Ortalama tutar | 11.700,00 | |
| 6 | Elif Şahin | İzmir | 11.300,00 | |||
| 7 | Burak Çelik | Ankara | 8.900,00 | |||
| 8 | Selin Aydın | İstanbul | 18.600,00 | |||
| 9 | Hakan Öztürk | Bursa | 6.700,00 | |||
| 10 | Deniz Korkmaz | İstanbul | 14.100,00 | |||
| 11 | Gül Polat | Ankara | 10.200,00 |
Şehir SatışlarıExcelDepo v1.0.0
2. Kendiliğinden güncellenen departman listesi
Personel kayıtları A1:C11 aralığındadır. E2 hücresindeki tek formül departman adlarını tekrarsız ve sıralı olarak aşağı doğru taşırır; E3:E5 hücreleri boş kalmalıdır. F sütunu her departmandaki personel sayısını verir.Veri Doğrulama Listeleriyle Kayıtları Düzenleme ve Süzme
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Personel | Departman | Kıdem (Yıl) | Departman Listesi | Personel Sayısı | |
| 2 | Ahmet Yılmaz | Satış | 5 | İnsan Kaynakları | 1 | |
| 3 | Fatma Koç | Muhasebe | 8 | Muhasebe | 2 | |
| 4 | Oğuz Tan | Üretim | 3 | Satış | 4 | |
| 5 | Merve Acar | İnsan Kaynakları | 6 | Üretim | 3 | |
| 6 | Kemal Erdem | Satış | 2 | |||
| 7 | Nur Güneş | Üretim | 9 | |||
| 8 | Barış Aksoy | Muhasebe | 4 | |||
| 9 | Ece Türk | Satış | 7 | |||
| 10 | Tolga Bulut | Üretim | 1 | |||
| 11 | Seda Yalçın | Satış | 10 |
Dinamik ListeExcelDepo v1.0.0
3. Seçilen kategoriye göre kayıtları süzme
Ürün kayıtları A1:C11 aralığındadır. E2 hücresine Veri Doğrulama ile Liste kuralı verin ve Kaynak kutusuna Gıda, Temizlik ve Kırtasiye kategorilerini yazın. F2 hücresindeki FİLTRE formülü E2'de seçilen kategorinin ürünlerini aşağı doğru listeler; F3 ve altındaki hücreler boş olmalıdır.Veri Doğrulama Listeleriyle Kayıtları Düzenleme ve Süzme
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Ürün | Kategori | Stok | Seçilen Kategori | Süzülen Ürünler | |
| 2 | Zeytinyağı | Gıda | 120 | Temizlik | Çamaşır Deterjanı | |
| 3 | Çamaşır Deterjanı | Temizlik | 85 | Bulaşık Sıvısı | ||
| 4 | Defter | Kırtasiye | 300 | Yüzey Temizleyici | ||
| 5 | Makarna | Gıda | 450 | |||
| 6 | Bulaşık Sıvısı | Temizlik | 140 | |||
| 7 | Kalem Seti | Kırtasiye | 210 | |||
| 8 | Pirinç | Gıda | 380 | |||
| 9 | Yüzey Temizleyici | Temizlik | 60 | |||
| 10 | Dosya Klasörü | Kırtasiye | 175 | |||
| 11 | Çay | Gıda | 260 |
Stok SüzmeExcelDepo v1.0.0
Sık sorulan sorular
- Listeye yeni değer ekledim, açılır listede görünmüyor. Neden? Kaynak sabit bir aralığı gösteriyordur. Listeyi tabloya çevirip ad tanımlayın ya da taşan dizi kullanın.
- Başka sayfadaki listeyi kaynak yapabilir miyim? Evet. Güncel Excel sürümlerinde Kaynak kutusundayken diğer sayfaya geçip aralığı doğrudan seçebilirsiniz; ad tanımlamak da aynı işi görür.
- Doğrulamayı nasıl kaldırırım? Hücreleri seçip Veri Doğrulama penceresini açın ve Tümünü Temizle (Clear All) düğmesine tıklayın.
- Yapıştırılan değerler de denetlenir mi? Hayır. Kopyalayıp yapıştırma hücrenin doğrulama kuralını bozabilir. Veri Doğrulama düğmesinin açılır menüsündeki Geçersiz Verileri Daire İçine Al (Circle Invalid Data) komutu liste dışı değerleri bulmanıza yardımcı olur.
Kullanırken dikkat edin
- Yapıştırma doğrulamayı atlar: Başka bir yerden kopyalayıp yapıştırdığınız değerler listede olmasa bile kabul edilir ve hücrenin doğrulama kuralı silinebilir. Toplu veri aktarırken sonradan Geçersiz Verileri Daire İçine Al (Circle Invalid Data) komutuyla kayıtları denetleyin.
- Yazım ve büyük küçük harf farkı: Liste değerleri sonunda boşluk içeriyorsa ya da kaynakta farklı yazılmışsa süzme ve sayma işlevleri bu kayıtları ayrı değer sayar. Değerleri yazılı liste olarak girdiğinizde doğrulama büyük küçük harfe duyarlıdır; aralık başvurusunda duyarlı değildir.
- Sürüm farkları: FİLTRE, BENZERSİZ ve SIRALA işlevleri ile taşan dizi başvurusu Microsoft 365 ve Excel 2021 gibi dinamik dizi destekleyen sürümlerde çalışır. Eski sürümlerde açılır liste ve otomatik filtre kullanın; sayı ve toplam için EĞERSAY ile ETOPLA yeterlidir.
- Hata uyarısı stilini bilinçli seçin: Dur stili liste dışı girişi tamamen engeller. Uyarı ya da Bilgi stili seçerseniz kullanıcı onay vererek listede olmayan değer girebilir; bu durum süzme sonuçlarını bozabilir.
- İlgili konular:
- Özellikler: Veri Doğrulama, Filtre, Tablo, Dilimleyici, Ad Yöneticisi
- İşlevler: FİLTRE, BENZERSİZ, SIRALA, EĞERSAY, ETOPLA, EĞERORTALAMA, DOLAYLI
Ekli dosyalar
Son düzenleme:
