Excel veri doğrulama: Excel'de veri doğrulama ekleme, kullanma, kopyalama ve kaldırma
Excel'de Veri Doğrulama, bir kullanıcının hücreye ne girebileceğini sınırlamak için kullanabileceğiniz kullanışlı bir özelliktir. Örneğin, veri doğrulama özelliği, metin dizelerinin uzunluğunu veya belirli karakterlerle başlayan / biten metni veya girilecek benzersiz değerleri vb. sınırlamanıza yardımcı olabilir.
Bu eğitimde, Excel'de veri doğrulamanın nasıl ekleneceği, kullanılacağı ve kaldırılacağı hakkında konuşacağız, bu özelliğin bazı temel ve gelişmiş işlemleri de ayrıntılı olarak gösterilecektir.
İçindekiler:
1. Excel'de veri doğrulama nedir?
2. Excel'de veri doğrulama nasıl eklenir?
3. Veri doğrulama için temel örnekler
- 3.1 Tam sayılar ve ondalık sayılar için veri doğrulama
- 3.2 Tarih ve saat için veri doğrulama
- 3.3 Metin uzunluğu için veri doğrulama
- 3.4 Veri doğrulama listesi (açılır liste)
4. Veri doğrulama için gelişmiş özel kurallar
- 4.1 Veri doğrulama yalnızca sayılara veya metinlere izin verir
- Veri Doğrulama işleviyle yalnızca sayıların girilmesine izin ver
- Veri Doğrulama işleviyle yalnızca metin dizelerinin girilmesine izin ver
- 4.2 Veri doğrulama yalnızca alfasayısal değerlere izin verir
- 4.3 Veri doğrulama, metnin belirli karakterlerle başlamasına veya bitmesine izin verir
- 4.4 Veri doğrulama izin girişleri belirli bir metin içermeli / içermemelidir
- 4.5 Veri doğrulama yalnızca benzersiz değerlere izin verir
- 4.6 Veri doğrulama yalnızca büyük harf / küçük harf / uygun harfe izin verir
- 4.7 Veri doğrulama, başka bir listede var olan/olmayan değerlere izin verir
- 4.8 Veri doğrulama yalnızca telefon numarası biçiminin girilmesini zorunlu kılar
- 4.9 Veri doğrulama yalnızca girilecek e-posta adreslerini zorunlu kılar
- 4.10 Veri doğrulama yalnızca girilecek IP adreslerini zorlar
- 4.11 Toplam değeri aşan veri doğrulama kısıtlama değerleri
- 4.12 Veri doğrulama, başka bir hücreye dayalı hücre girişini kısıtlar
- 4.13 Veri doğrulama, yalnızca hafta içi veya hafta sonlarının girilmesine izin verir
- 4.14 Veri doğrulama, bugünün tarihine göre girilen tarihe izin verir
- 4.15 Veri doğrulama, geçerli zamana dayalı olarak girilen zamana izin verir
- 4.16 Veri doğrulama, belirli veya cari yılın tarihi
- 4.17 Veri doğrulama, geçerli hafta veya aydaki tarih
5. Excel'de veri doğrulama nasıl düzenlenir?
6. Excel'de veri doğrulamalı hücreler nasıl bulunur ve seçilir?
7. Veri doğrulama kuralı diğer hücrelere nasıl kopyalanır?
8. Excel'de geçersiz girişleri daire içine almak için veri doğrulama nasıl kullanılır?
1. Excel'de veri doğrulama nedir?
The veri Doğrulama özelliği, çalışma sayfanızdaki girdi içeriğini kısıtlamanıza yardımcı olabilir. Normalde, seçili hücreler listesine yalnızca belirli türde verilerin girilmesini önlemek veya buna izin vermek için bazı doğrulama kuralları oluşturabilirsiniz.
Veri Doğrulama özelliğinin bazı temel kullanımları:
|
2. Excel'de veri doğrulama nasıl eklenir?
Excel çalışma sayfasında, aşağıdaki adımlarla veri doğrulama ekleyebilirsiniz:
1. Veri doğrulamasını ayarlamak istediğiniz hücrelerin listesini seçin ve ardından Veri > veri Doğrulama > veri Doğrulama, ekran görüntüsüne bakın:
2. In veri Doğrulama iletişim kutusunun altında Ayarlar sekmesinde, lütfen kendi doğrulama kurallarınızı oluşturun. ölçüt kutularında aşağıdaki türlerden herhangi birini sağlayabilirsiniz:
- Değerler: Numaraları doğrudan ölçüt kutularına yazın;
- Hücre referansı: Çalışma sayfasındaki veya başka bir çalışma sayfasındaki bir hücreye başvuru;
- Formüller: Koşullar olarak daha karmaşık formüller oluşturun.
Örnek olarak, sadece 100 ile 1000 arasındaki tam sayıların girilmesine izin veren bir kural oluşturacağım, burada kriterleri aşağıda gösterilen ekran görüntüsü gibi ayarlayın:
3. Koşulları yapılandırdıktan sonra şuraya gidebilirsiniz: giriş mesajı or Hata Uyarısı Doğrulama hücreleri için giriş mesajını veya hata uyarısını istediğiniz gibi ayarlamak için sekmesine tıklayın. (Uyarıyı ayarlamak istemiyorsanız, lütfen OK doğrudan bitirmek için.)
3.1) Giriş mesajı ekleyin (isteğe bağlı):
Veri doğrulama içeren bir hücreyi seçerken görünen bir mesaj oluşturabilirsiniz. Bu mesaj, kullanıcıya hücreye ne girebileceklerini hatırlatmaya yardımcı olur.
Git giriş mesajı sekmesine gidin ve aşağıdakileri yapın:
|
Şimdi, doğrulanmış bir hücre seçtiğinizde, aşağıdaki gibi bir mesaj kutusu gösterilecektir:
3.2) Anlamlı hata mesajları oluşturun (isteğe bağlı):
Giriş mesajı oluşturmaya ek olarak, veri doğrulama ile bir hücreye geçersiz veriler girildiğinde hata uyarıları da gösterebilirsiniz.
Git Hata Uyarısı sekmesinin sekmesi veri Doğrulama iletişim kutusu, lütfen şu şekilde yapın:
|
Ve şimdi, geçersiz bir değer girildiğinde, aşağıda gösterilen ekran görüntüsü gibi mesaj uyarı kutusu açılacaktır:
dur seçenek: tıklayabilirsiniz Tekrar dene başka bir değer yazmak için veya İptal etmek girişi kaldırmak için
uyarı seçenek: tıklayın Evet geçersiz girişi girmek için, Yok hayır değiştirmek için veya İptal etmek girişi kaldırmak için
Bilgi seçenek: tıklayın OK geçersiz girişi girmek veya İptal etmek girişi kaldırmak için
not: Menüde kendi özel mesajınızı ayarlamazsanız, Hata Uyarısı kutu, varsayılan dur uyarı istemi kutusu aşağıda gösterildiği gibi görüntülenecektir:
3. Veri doğrulama için temel örnekler
Bu Veri Doğrulama özelliğini kullanırken, veri doğrulamayı ayarlamanız için size sağlanan 8 yerleşik seçenek vardır. Örneğin: herhangi bir değer, tam sayılar ve ondalık sayılar, tarih ve saat, liste, metin uzunluğu ve özel formül. Bu bölümde, Excel'deki bazı yerleşik seçeneklerin nasıl kullanılacağını tartışacağız.
3.1 Tam sayılar ve ondalık sayılar için veri doğrulama
1. Yalnızca tam sayılara veya ondalık sayılara izin vermek istediğiniz hücrelerin listesini seçin ve ardından Veri > veri Doğrulama > veri Doğrulama.
2. In veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, aşağıdaki işlemleri yapın:
- İlgili öğeyi seçin Bütün sayı or Ondalık içinde izin vermek açılır kutu.
- Ardından, ihtiyacınız olan kriterlerden birini seçin. Veri kutu (Bu örnekte, arasında seçenek).
- İpuçları: Kriterler şunları içerir: arasında, arasında değil, eşittir, eşit değil, büyüktür, küçüktür, büyüktür veya eşittir, küçüktür veya eşittir.
- Ardından, asgari ve Maksimum ihtiyacınız olan değerler (0 ile 1 arasındaki sayıları istiyorum).
- Sonunda, tıklayın OK düğmesine basın.
3. Şimdi, seçtiğiniz hücrelere yalnızca 0'dan 100'e kadar tam sayıların girilmesine izin verilir.
3.2 Tarih ve saat için veri doğrulama
Girilecek belirli bir tarihi veya saati doğrulamak için bunu kullanmak kolaydır. veri Doğrulamalütfen aşağıdaki işlemleri yapın:
1. Yalnızca belirli tarihlere veya saatlere izin vermek istediğiniz hücrelerin listesini seçin ve ardından Veri > veri Doğrulama > veri Doğrulama.
2. In veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, aşağıdaki işlemleri yapın:
- İlgili öğeyi seçin Tarih or Zaman içinde izin vermek açılır kutu.
- Ardından, ihtiyacınız olan kriterlerden birini seçin. Veri kutu (Burada seçiyorum daha büyük seçenek).
- İpuçları: Kriterler şunları içerir: arasında, arasında değil, eşittir, eşit değil, büyüktür, küçüktür, büyüktür veya eşittir, küçüktür veya eşittir.
- Ardından, Başlangıç tarihi ihtiyacınız var (8/20/2021'den büyük tarihleri istiyorum).
- Sonunda, tıklayın OK düğmesine basın.
3. Artık, seçtiğiniz hücrelere yalnızca 8 tarihinden daha büyük tarihlerin girilmesine izin verilmektedir.
3.3 Metin uzunluğu için veri doğrulama
Bir hücreye yazılabilecek karakter sayısını sınırlamanız gerekiyorsa. Örneğin, belirli bir aralık için içeriği en fazla 10 karakterle sınırlamak için bu veri Doğrulama ayrıca sana bir iyilik yapabilir.
1. Metin uzunluğunu sınırlamak istediğiniz hücrelerin listesini seçin ve ardından Veri > veri Doğrulama > veri Doğrulama.
2. In veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, aşağıdaki işlemleri yapın:
- seç Metin uzunluğu itibaren izin vermek açılır kutu.
- Ardından, ihtiyacınız olan kriterlerden birini seçin. Veri kutu (Bu örnekte, daha kısa seçenek).
- İpuçları: Kriterler şunları içerir: arasında, arasında değil, eşittir, eşit değil, büyüktür, küçüktür, büyüktür veya eşittir, küçüktür veya eşittir.
- Ardından, Maksimum sınırlamanız gereken sayı (metin uzunluğunun 10 karakterden fazla olmamasını istiyorum).
- Sonunda, tıklayın OK düğmesine basın.
3. Şimdi, seçilen hücreler yalnızca metin dizesinin 10 karakterden az yazılmasına izin veriyor.
3.4 Veri doğrulama listesi (açılır liste)
Bu güçlü ile veri Doğrulama özelliği sayesinde hücrelerde de hızlı ve kolay bir şekilde açılır liste oluşturabilirsiniz. Lütfen şu şekilde yapın:
1. Açılır listenin ekleneceği hedef hücreleri seçin ve ardından Veri > veri Doğrulama > veri Doğrulama.
2. In veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, aşağıdaki işlemleri yapın:
- seç Liste itibaren izin vermek açılır liste.
- içinde Kaynak metin kutusuna virgülle ayırarak liste öğelerini doğrudan yazın. Örneğin, kullanıcı girişini üç seçenekle sınırlamak için Başlatılmadı, Devam Ediyor, Tamamlandı yazın veya açılır menüyü eklemek için değerleri içeren bir hücre listesi seçebilirsiniz.
- Sonunda, tıklayın OK düğmesine basın.
3. Şimdi, aşağıda gösterilen ekran görüntüsü gibi hücrelere açılır liste oluşturuldu:
Açılır listenin daha detaylı bilgilerini öğrenmek için tıklayın…
4. Veri doğrulama için gelişmiş özel kurallar
Bu bölümde, sorun türlerinizi çözmek için bazı gelişmiş özel veri doğrulama kurallarının nasıl oluşturulacağını anlatacağım, örneğin: yalnızca sayılara veya metin dizelerine, yalnızca benzersiz değerlere, yalnızca belirtilen telefon numaralarına, e-posta adreslerine izin verecek doğrulama formülleri oluşturun. .
4.1 Veri doğrulama yalnızca sayılara veya metinlere izin verir
Veri Doğrulama işleviyle yalnızca sayıların girilmesine izin ver
Bir hücre aralığında yalnızca sayılara izin vermek için lütfen şunu yapın:
1. Yalnızca sayıların girilmesini istediğiniz hücre aralığını seçin.
2. tıklayın Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, lütfen aşağıdaki işlemleri yapın:
- seç görenek itibaren izin vermek açılır liste.
- Ve sonra şu formülü girin: = ISNUMBER (A2) içine formül Metin kutusu. (A2 sınırlamak istediğiniz seçili aralığın ilk hücresidir)
- Tıkla OK Bu iletişim kutusunu kapatmak için düğmesine basın.
3. Artık seçilen hücrelere sadece sayılar girilebilir.
not: Bu NUMARA işlevi, doğrulanmış hücrelerde tamsayılar, ondalık sayılar, kesirler, tarihler ve saatler dahil olmak üzere herhangi bir sayısal değere izin verir.
Veri Doğrulama işleviyle yalnızca metin dizelerinin girilmesine izin ver
Hücre girişlerini yalnızca metinle sınırlamak için veri Doğrulama dayalı özel bir formüle sahip özellik İSTEK işlevi, lütfen şu şekilde yapın:
1. Yalnızca metin dizelerinin girilmesini istediğiniz hücre aralığını seçin.
2. tıklayın Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, lütfen aşağıdaki işlemleri yapın:
- seç görenek itibaren izin vermek açılır liste.
- Ve sonra şu formülü girin: = ISTEXT (A2) içine formül Metin kutusu. (A2 sınırlamak istediğiniz seçili aralığın ilk hücresidir)
- Tıkla OK Bu iletişim kutusunu kapatmak için düğmesine basın.
3. Artık, belirli hücrelere veri girerken, yalnızca metin biçimindeki verilere izin verilebilir.
4.2 Veri doğrulama yalnızca alfasayısal değerlere izin verir
Bazı amaçlar için, sadece alfabe ve sayısal değerlerin girilmesine izin vermek, ancak ~,%,$, boşluk vb. gibi özel karakterleri kısıtlamak istiyorsunuz, bu bölüm size bazı püf noktaları tanıtacaktır.
Veri Doğrulama işleviyle yalnızca alfasayısal değerlere izin ver
Özel karakterleri engellemek, ancak yalnızca alfanümerik değerlere izin vermek için, içine özel bir formül oluşturmalısınız. veri Doğrulama işlevi, lütfen aşağıdaki işlemleri yapın:
1. Yalnızca alfasayısal değerlerin girilmesini istediğiniz hücre aralığını seçin.
2. tıklayın Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, lütfen aşağıdaki işlemleri yapın:
- seç görenek itibaren izin vermek açılır liste.
- Ardından aşağıdaki formülü kutucuğa giriniz. formül Metin kutusu.
- =IF(A2="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))
- Tıkla OK Bu iletişim kutusunu kapatmak için düğmesine basın.
not: Yukarıdaki formüllerde, A2 sınırlamak istediğiniz seçili aralığın ilk hücresidir.
3. Şimdi, yalnızca alfabe ve sayısal değerlerin girilmesine izin veriliyor ve aşağıda gösterilen ekran görüntüsü gibi yazarken özel karakterler kısıtlanacak:
Harika bir özelliğe sahip yalnızca alfasayısal değerlere izin verin
Yukarıdaki formül anlamamız ve hatırlamamız için karmaşık olabilir, burada kullanışlı bir özellik tanıtacağım - Yazmayı Önle of Kutools for Excel, bu özellik sayesinde bu işi kolaylıkla hızlı bir şekilde çözebilirsiniz.
Kurduktan sonra Kutools for Excellütfen şunu yapın:
1. Yalnızca alfasayısal değerlerin girilmesini istediğiniz hücre aralığını seçin.
2. Daha sonra, Kutools > Yazmayı Önle > Yazmayı Önle, ekran görüntüsüne bakın:
3. Dışarı fırladı Yazmayı Önle iletişim kutusunu seçin Özel karakterlerde yazmayı engelle seçenek, ekran görüntüsüne bakın:
4. Daha sonra, Ok düğmesini tıklayın ve aşağıdaki istem kutularında Evet > OK işlemi bitirmek için. Şimdi, seçilen hücrelerde yalnızca alfabe ve sayısal değerlere izin verilir, bkz. ekran görüntüsü:
4.3 Veri doğrulama, metinlerin belirli karakterlerle başlamasına veya bitmesine izin verir
Belirli bir aralıktaki tüm değerlerin belirli bir karakter veya alt dize ile başlaması veya bitmesi gerekiyorsa, TAM, SOL, SAĞ veya EĞERSAY işlevine dayalı özel bir formülle veri doğrulamayı kullanabilirsiniz.
Metinlerin yalnızca bir koşulla belirli karakterlerle başlamasına veya bitmesine izin ver
Örneğin, belirli hücrelere metin dizelerini girerken metinlerin “CN” ile başlamasını veya bitmesini istiyorum, lütfen şunu yapın:
1. Yalnızca belirli karakterlerle başlayan veya biten metinlere izin veren bir hücre aralığı seçin.
2. Daha sonra, Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, lütfen aşağıdaki işlemleri yapın:
- seç görenek itibaren izin vermek açılır liste.
- Ardından aşağıdaki formülü kutucuğa giriniz. formül Metin kutusu.
- Begin with: =EXACT(LEFT(A2,2),"CN")
End with: =EXACT(RIGHT(A2,2),"CN") - Tıkla OK Bu iletişim kutusunu kapatmak için düğmesine basın.
not: Yukarıdaki formüllerde, A2 seçilen aralığın ilk hücresi, sayı 2 belirttiğiniz karakter sayısıdır, CN başlamak veya bitirmek istediğiniz metindir.
3. Şu andan itibaren, seçilen hücrelere yalnızca belirtilen karakterlerle başlayan veya biten metin dizesi girilebilir. Aksi takdirde, aşağıda gösterilen ekran görüntüsü gibi size hatırlatmak için bir uyarı uyarısı çıkacaktır:
İpuçları: Yukarıdaki formüller büyük/küçük harfe duyarlıdır, büyük/küçük harfe duyarlı olmanız gerekmiyorsa lütfen aşağıdaki CONTIF formüllerini uygulayın:
Begin with (non case sensitive): =COUNTIF(A2,"CN*")
End with (non case sensitive): =COUNTIF(A2,"*CN")
not: Yıldız işareti *, bir veya daha fazla karakterle eşleşen bir joker karakterdir.
Metinlerin birden çok kritere sahip belirli karakterlerle başlamasına veya bitmesine izin ver (VEYA mantığı)
Örneğin, metinlerin aşağıdaki ekran görüntüsü gibi “CN” veya “UK” ile başlamasını veya bitmesini istiyorsanız, bir artı (+) işareti kullanarak başka bir EXACT örneğini eklemeniz gerekir. Lütfen aşağıdaki adımlarla yapın:
1. Yalnızca birden çok ölçütle başlayan veya biten metinlere izin veren bir hücre aralığı seçin.
2. Daha sonra, Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, lütfen aşağıdaki işlemleri yapın:
- seç görenek itibaren izin vermek açılır liste.
- Ardından aşağıdaki formülü kutucuğa giriniz. formül Metin kutusu.
- Begin with: =EXACT(LEFT(A2,2),"CN")+EXACT(LEFT(A2,2),"UK")
End with: =EXACT(RIGHT(A2,2),"CN")+EXACT(RIGHT(A2,2),"UK") - Tıkla OK Bu iletişim kutusunu kapatmak için düğmesine basın.
not: Yukarıdaki formüllerde, A2 seçilen aralığın ilk hücresi, sayı 2 belirttiğiniz karakter sayısıdır, CN ve UK başlamak veya bitirmek istediğiniz belirli metinlerdir.
3. Artık, seçilen hücrelere yalnızca belirtilen karakterlerle başlayan veya biten metin dizesi girilebilir.
İpuçları: Büyük/küçük harf duyarlılığını yok saymak için lütfen aşağıdaki CONTIF formüllerini uygulayın:
Begin with (non case sensitive): =COUNTIF(A2,"CN*")+COUNTIF(A2,"UK*")
End with (non case sensitive): =COUNTIF(A2,"*CN")+COUNTIF(A2,"*UK")
not: Yıldız işareti *, bir veya daha fazla karakterle eşleşen bir joker karakterdir.
4.4 Veri doğrulama izin girişleri belirli bir metin içermeli / içermemelidir
Bu bölümde, değerlerin Excel'de belirli bir alt dize veya birçok alt dizeden birini içermesi veya içermemesi gerektiğine izin vermek için Veri Doğrulamasının nasıl uygulanacağı hakkında konuşacağım.
İzin verilen girişler, belirli metinlerden birini veya birini içermelidir
İzin verilen girişler belirli bir metin içermelidir
Belirli bir metin dizesi içeren girişlere izin vermek için, örneğin, girilen tüm değerler, aşağıda gösterilen ekran görüntüsü gibi “KTE” metnini içermelidir, BUL ve ISNUMARA işlevlerine dayalı özel bir formülle veri doğrulamasını uygulayabilirsiniz. Lütfen şu şekilde yapın:
1. Yalnızca belirli metni içeren metinlere izin veren bir hücre aralığı seçin.
2. Daha sonra, Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, lütfen aşağıdaki işlemleri yapın:
- seç görenek İzin Ver açılır listesinden.
- Ardından, aşağıdaki formüllerden birini girin formül Metin kutusu.
- =ISNUMBER(FIND("KTE",A2)) (Case sensitive)
=ISNUMBER(SEARCH("KTE",A2)) (Non case sensitive) - Tıkla OK Bu iletişim kutusunu kapatmak için düğmesine basın.
not: Yukarıdaki formüllerde, A2 seçilen aralığın ilk hücresi, metin KTE girdilerin içermesi gereken metin dizesidir.
3. Şimdi, girilen değer tasarlanan metni içermediğinde, bir uyarı istemi kutusu çıkacaktır.
İzin verilen girişler birçok özel metinden birini içermelidir
Yukarıdaki formül yalnızca bir metin dizesi için çalışır, aşağıdaki ekran görüntüsü gibi hücrelerde birçok metin dizesinden herhangi birine izin verilmesi gerekiyorsa, bir formül oluşturmak için SUMPRODUCT, BUL ve ISNUMBER işlevlerini birlikte kullanmalısınız.
1. Yalnızca birçok öğeden herhangi birini içeren metinlere izin veren bir hücre aralığı seçin.
2. Daha sonra, Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, lütfen aşağıdaki işlemleri yapın:
- seç görenek itibaren izin vermek açılır liste.
- Ardından, aşağıdaki formüllerden ihtiyacınız olan birini girin formül Metin kutusu.
- =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))>0 (Case sensitive)
=SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0 (Non case sensitive) - Ve sonra tıklayın OK iletişim kutusunu kapatmak için
not: Yukarıdaki formüllerde, A2 seçilen aralığın ilk hücresidir, C2: C4 girdilerin herhangi birini içermesine izin vermek istediğiniz değerlerin listesidir.
3. Ve şimdi, belirli listedeki değerlerden herhangi birini içeren girişler girilebilir.
İzin verilen girişler, belirli metinlerden birini veya birini içermemelidir
İzin verilen girişler belirli bir metin içermemelidir
Girişlerin belirli bir metin içermemesi gerektiğini doğrulamak için, örneğin bir hücrede “KTE” metnini içermemesi gereken değerlere izin vermek için, bir veri doğrulama kuralı oluşturmak için ISERROR ve BUL işlevlerini kullanabilirsiniz. Lütfen şu şekilde yapın:
1. Yalnızca belirli metni içermeyen metinlere izin veren bir hücre aralığı seçin.
2. Daha sonra, Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, lütfen aşağıdaki işlemleri yapın:
- seç görenek itibaren izin vermek açılır liste.
- Ardından, aşağıdaki formüllerden birini girin formül Metin kutusu.
- =ISERROR(FIND("KTE",A2)) (Case sensitive)
=ISERROR(SEARCH("KTE",A2)) (Non case sensitive) - Tıkla OK Bu iletişim kutusunu kapatmak için düğmesine basın.
not: Yukarıdaki formüllerde, A2 seçilen aralığın ilk hücresi, metin KTE girdilerin içermemesi gereken metin dizesidir.
3. Artık belirli metni içeren girişlerin girilmesi engellenecektir.
İzin verilen girişler birçok özel metinden birini içermemelidir
Bir listedeki birçok metin dizisinden birinin aşağıdaki ekran görüntüsü gibi girilmesini önlemek için şu şekilde yapmalısınız:
1. Bazı metinlerin engellenmesini istediğiniz hücre aralığını seçin.
2. Daha sonra, Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, lütfen aşağıdaki işlemleri yapın:
- seç görenek itibaren izin vermek açılır liste.
- Ardından, aşağıdaki formülü girin formül Metin kutusu.
- =SUMPRODUCT(--ISNUMBER(FIND($C$2:$C$4,A2)))=0 (Case sensitive)
=SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))=0 (Non case sensitive) - Ve sonra tıklayın OK iletişim kutusunu kapatmak için
not: Yukarıdaki formüllerde, A2 seçilen aralığın ilk hücresidir, C2: C4 girdiler bunlardan herhangi birini içeriyorsa önlemek istediğiniz değerlerin listesidir.
3. Bundan sonra, belirli metinlerden herhangi birini içeren girişlerin girilmesi engellenecektir.
4.5 Veri doğrulama yalnızca benzersiz değerlere izin verir
Bir hücre aralığına yinelenen verilerin girilmesini önlemek istiyorsanız, bu bölümde bu görevi Excel'de çözmek için bazı hızlı yöntemler tanıtılacaktır.
Veri Doğrulama işleviyle yalnızca benzersiz değerlere izin ver
Normalde, EĞERSAY işlevine dayalı özel bir formüle sahip Veri Doğrulama özelliği size yardımcı olabilir, lütfen aşağıdaki adımları uygulayın:
1. Yalnızca benzersiz değerlerin girilmesini istediğiniz hücreleri veya sütunu seçin.
2. Daha sonra, Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, aşağıdaki işlemleri yapın:
- seç görenek itibaren izin vermek açılır liste.
- Ardından aşağıdaki formülü kutucuğa giriniz. formül Metin kutusu.
- =COUNTIF($A$2:$A$9,A2)=1
- Tıkla OK Bu iletişim kutusunu kapatmak için düğmesine basın.
not: Yukarıdaki formülde, A2: A9 yalnızca benzersiz değerlere izin vermek istediğiniz hücre aralığıdır ve A2 seçilen aralığın ilk hücresidir.
3. Şimdi, yalnızca benzersiz değerlerin girilmesine izin verilir ve yinelenen veriler girildiğinde bir uyarı mesajı çıkar, bkz. ekran görüntüsü:
VBA koduyla yalnızca benzersiz değerlere izin ver
Aşağıdaki VBA kodu, yinelenen değerlerin girilmesini önlemenize de yardımcı olabilir, lütfen şu şekilde yapın:
1. Yalnızca benzersiz değerlere izin vermek istediğiniz sayfa sekmesine sağ tıklayın ve Kodu Görüntüle bağlam menüsünden, açılır pencerede Uygulamalar için Microsoft Visual Basic penceresi, lütfen aşağıdaki kodu boş Modüle kopyalayıp yapıştırın:
VBA kodu: Yalnızca bir hücre aralığında benzersiz değerlere izin verin:
Private Sub Worksheet_Change(ByVal Target As Range) 'Updateby Extendoffice Dim xRg As Range, iLong, fLong As Long If Not Intersect(Target, Me.[A1:A100]) Is Nothing Then Application.EnableEvents = False For Each xRg In Target With xRg If (.Value <> "") Then If WorksheetFunction.CountIf(Me.[A:A], .Value) > 1 Then iLong = .Interior.ColorIndex fLong = .Font.ColorIndex .Interior.ColorIndex = 3 .Font.ColorIndex = 6 MsgBox "Duplicate Entry !", vbCritical, "Kutools for Excel" .ClearContents .Interior.ColorIndex = iLong .Font.ColorIndex = fLong End If End If End With Next Application.EnableEvents = True End If End Sub
not: Yukarıdaki kodda, A1: A100 ve A: A sütundaki yinelenmesini önlemek istediğiniz hücrelerdir, lütfen bunları ihtiyacınıza göre değiştirin.
2. Ardından, bu kodu kaydedin ve kapatın, şimdi, A1:A100 hücresine yinelenen değer girerken, aşağıda gösterilen ekran görüntüsü gibi bir uyarı istemi kutusu açılır:
Kullanışlı bir özellik ile yalnızca benzersiz değerlere izin verin
Eğer varsa Kutools for Excel, Onun ile Yinelemeyi Önleyin özelliği, yalnızca birkaç tıklamayla bir dizi hücre için yinelemeleri önlemek için veri doğrulamayı ayarlayabilirsiniz.
Kurduktan sonra Kutools for Excellütfen şunu yapın:
1. Yinelenen değerleri önlemek, ancak yalnızca benzersiz verilere izin vermek istediğiniz hücre aralığını seçin.
2. Daha sonra, Kutools > Yazmayı Önle > Yinelemeyi Önleyin, ekran görüntüsüne bakın:
3. Ve bu özelliği uygularsanız Veri Doğrulamanın kaldırılacağını hatırlatmak için bir uyarı mesajı çıkacaktır, tıklayın. Evet ve aşağıdaki istem kutusunda, OK, ekran görüntülerine bakın:
4. Şimdi, belirttiğiniz hücrelere bazı yinelenen veriler girdiğinizde, yinelenen verilerin geçerli olmadığını size hatırlatmak için bir istem kutusu görüntülenir, ekran görüntüsüne bakın:
4.6 Veri doğrulama yalnızca büyük harf / küçük harf / uygun harfe izin verir
Bu Veri Doğrulama güçlü bir özelliktir, ayrıca bir kullanıcının bir hücre aralığında yalnızca büyük harf, küçük harf veya uygun harf girişlerini girmesine izin vermeye yardımcı olabilir. Lütfen aşağıdaki adımlarla yapın:
1. Yalnızca büyük harf, küçük harf veya uygun harf metninin girilmesini istediğiniz hücre aralığını seçin.
2. Daha sonra, Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, aşağıdaki işlemleri yapın:
- seç görenek itibaren izin vermek açılır liste.
- Ardından, ihtiyacınız olan aşağıdaki formüllerden birini girin formül Metin kutusu.
- =AND(EXACT(A2,UPPER(A2)),ISTEXT(A2)) (only allow uppercase text)
=AND(EXACT(A2,LOWER(A2)),ISTEXT(A2)) (only allow lowercase text)
=AND(EXACT(A2,PROPER(A2)),ISTEXT(A2)) (only allow proper case text) - Tıkla OK Bu iletişim kutusunu kapatmak için düğmesine basın.
not: Yukarıdaki formülde, A2 kullanmak istediğiniz sütunun ilk hücresidir.
3. Artık yalnızca oluşturduğunuz kurala uyan girişler kabul edilecektir.
4.7 Veri doğrulama, başka bir listede var olan/olmayan değerlere izin verir
Başka bir listede var olan veya olmayan değerlerin bir hücre aralığına girilmesine izin vermek çoğumuz için acı verici bir sorun olabilir. Aslında, bununla başa çıkmak için EĞERSAY işlevine dayalı basit bir formülle veri doğrulama özelliğini kullanabilirsiniz.
Örneğin, aşağıda gösterilen ekran görüntüsü gibi bir hücre aralığına yalnızca C2:C4 aralığındaki değerlerin girilmesini istiyorum, bu işi çözmek için lütfen şunu yapın:
1. Veri doğrulamasını uygulamak istediğiniz hücre aralığını seçin.
2. Daha sonra, Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, aşağıdaki işlemleri yapın:
- seç görenek itibaren izin vermek açılır liste.
- Ardından, ihtiyacınız olan aşağıdaki formüllerden birini girin formül Metin kutusu.
- =COUNTIF($C$2:$C$4,A2)>0 (only allow values exist in another column)
=COUNTIF($C$2:$C$4,A2)=0 (prevent values exist in another column) - Tıkla OK Bu iletişim kutusunu kapatmak için düğmesine basın.
not: Yukarıdaki formülde, A2 kullanmak istediğiniz sütunun ilk hücresidir, C2: C4 girişlerden biriyse, önlemek veya izin vermek istediğiniz değerlerin listesidir.
3. Artık sadece sizin oluşturduğunuz kurala uygun girişler girilebilir, diğerleri engellenmiş olur.
4.8 Veri doğrulama yalnızca telefon numarası biçiminin girilmesini zorunlu kılar
Şirket çalışanlarınızın bilgilerini girdiğinizde, telefon numaralarının hızlı ve doğru girilmesini sağlamak için bir sütuna telefon numarasını yazmanız gerekir, bu durumda telefon numaraları için veri doğrulama ayarlayabilirsiniz. Örneğin, telefon numarasının (123) 456-7890 biçimindeki bir çalışma sayfasına girmesine izin verilmesini istiyorum, bu bölümde bu görevi çözmek için iki hızlı numara tanıtılacaktır.
Veri Doğrulama işleviyle yalnızca telefon numarası biçimini zorla
Yalnızca belirli telefon numarası biçiminin girilmesine izin vermek için lütfen şunu yapın:
1. Belirli bir telefon numarası biçiminin girilmesini istediğiniz hücrelerin listesini seçin ve ardından sağ tıklayın, seçin. biçim Hücreler bağlam menüsünden, ekran görüntüsüne bakın:
2. In biçim Hücreler iletişim kutusunun altında Numara sekmesini seçin görenek solda Kategoriler liste kutusunu seçin ve ardından Tip metin kutusuna ihtiyacınız olan telefon numarası biçimini girin, örneğin, bunu kullanacağım (###) ### - #### biçim, ekran görüntüsüne bakın:
3. Daha sonra, OK iletişim kutusunu kapatmak için
4. Hücreleri biçimlendirdikten sonra, hücreleri seçmeye devam edin ve ardından veri Doğrulama tıklayarak iletişim kutusu Veri > veri Doğrulama > veri Doğrulama, açılan iletişim kutusunda, Ayarlar sekmesi, aşağıdaki işlemleri yapın:
- seç görenek itibaren izin vermek açılır liste.
- Ve sonra bu formülü girin = VE (ISNUMBER (A2), LEN (A2) = 10) Formül metin kutusuna.
- Tıkla OK Bu iletişim kutusunu kapatmak için düğmesine basın.
not: Yukarıdaki formülde, A2 telefon numarasını doğrulamak istediğiniz sütunun ilk hücresidir.
5. Şimdi, 10 basamaklı bir sayı girdiğinizde, ihtiyacınız olduğunda otomatik olarak belirli telefon numarası biçimine dönüştürülecektir, ekran görüntülerine bakın:
not: Girilen sayı 10 hane değilse, size hatırlatmak için bir uyarı mesaj kutusu açılır, bkz. ekran görüntüsü:
Kullanışlı bir özellik ile yalnızca telefon numarası biçimini zorla
Kutools for Excel'S Telefon Numarasını Doğrula özelliği ayrıca yalnızca birkaç tıklamayla yalnızca telefon numarası biçimini girmeye zorlamanıza yardımcı olabilir.
Kurduktan sonra Kutools for Excellütfen şunu yapın:
1. Yalnızca belirli telefon numarasına izin veren hücrelerin listesini seçin, ardından Kutools > Yazmayı Önle > Telefon Numarasını Doğrula, ekran görüntüsüne bakın:
2. In Telefon Numaranız iletişim kutusunda, ihtiyacınız olan belirli telefon numarası biçimini seçin veya düğmesine tıklayarak kendi biçimlendirmenizi oluşturabilirsiniz. Ekle düğmesi, ekran görüntüsüne bakın:
3. Telefon numarası biçimlendirmesini seçtikten veya ayarladıktan sonra, OK, şimdi yalnızca belirli biçimlendirmeye sahip telefon numarası girilebilir, aksi takdirde size hatırlatmak için bir uyarı mesajı açılır, ekran görüntüsüne bakın:
4.9 Veri doğrulama yalnızca girilecek e-posta adreslerini zorunlu kılar
Bazı yanlış e-posta adresleri biçimlendirmesinin girilmesini önlemek için bir çalışma sayfasının bir sütununa birden çok e-posta adresi yazmanız gerektiğini varsayarsak, normalde yalnızca e-posta adreslerinin biçimlendirilmesine izin verecek bir veri doğrulama kuralı belirleyebilirsiniz.
Veri Doğrulama işleviyle yalnızca E-posta adresleri biçimini zorla
Veri Doğrulama özelliğini özel bir formülle kullanarak geçersiz e-posta adreslerinin hızlı bir şekilde girilmesini önlemek için bir kural oluşturabilirsiniz, lütfen şu şekilde yapın:
1. Yalnızca e-posta adreslerinin girilmesini istediğiniz hücreleri seçin ve ardından Veri > veri Doğrulama > veri Doğrulama.
2. Dışarı fırladı veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, aşağıdaki işlemleri yapın:
- seç görenek itibaren izin vermek açılır liste.
- Ve sonra bu formülü girin = ISNUMBER (MAÇ ("*@*.?*", A2,0)) içine formül Metin kutusu.
- Tıkla OK Bu iletişim kutusunu kapatmak için düğmesine basın.
not: Yukarıdaki formülde, A2 kullanmak istediğiniz sütunun ilk hücresidir.
3. Şimdi, girilen metin e-posta adresi biçiminde değilse, size hatırlatmak için bir uyarı mesaj kutusu açılır, ekran görüntüsüne bakın:
Kullanışlı bir özellik ile yalnızca E-posta adresleri biçimini zorunlu kılın
Kutools for Excel inanılmaz bir özelliği destekler – E-posta adresini doğrula, bu yardımcı program ile tek tıkla geçersiz e-posta adreslerini engelleyebilirsiniz.
Kurduktan sonra Kutools for Excellütfen aşağıdaki işlemleri yapın:
1. Yalnızca e-posta adreslerinin girilmesine izin verdiğiniz hücreleri seçin ve ardından Kutools > Yazmayı Önle > E-posta adresini doğrula. Ekran görüntüsüne bakın:
2. Ardından, yalnızca e-posta adresi biçimlendirmesinin girilmesine izin verir, aksi takdirde, size hatırlatmak için bir uyarı mesajı kutusu açılır, ekran görüntüsüne bakın:
4.10 Veri doğrulama yalnızca girilecek IP adreslerini zorlar
Bu bölümde, bir dizi hücrede yalnızca IP adreslerini kabul etmek için veri doğrulamasını ayarlamak için bazı hızlı püf noktaları tanıtacağım.
Veri Doğrulama işleviyle yalnızca IP adresleri biçimini zorla
Belirli bir hücre aralığına yalnızca IP adreslerinin yazılmasına izin verin, lütfen şu şekilde yapın:
1. Yalnızca IP adresinin girilmesini istediğiniz hücreleri seçin ve ardından Veri > veri Doğrulama > veri Doğrulama.
2. Dışarı fırladı veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, aşağıdaki işlemleri yapın:
- seç görenek itibaren izin vermek açılır liste.
- Ardından aşağıdaki formülü kutucuğa giriniz. formül Metin kutusu.
- =AND((LEN(A2)-LEN(SUBSTITUTE(A2,".","")))=3,ISNUMBER(SUBSTITUTE(A2,".","")+0))
- Tıkla OK Bu iletişim kutusunu kapatmak için düğmesine basın.
Not: Yukarıdaki formülde, A2 kullanmak istediğiniz sütunun ilk hücresidir.
3. Şimdi, hücreye geçersiz bir IP adresi girerseniz, aşağıdaki ekran görüntüsü gibi size hatırlatmak için bir uyarı mesajı kutusu çıkacaktır:
VBA koduyla yalnızca IP adresleri biçimini zorla
Burada, aşağıdaki VBA kodu da yalnızca IP adreslerinin girilmesine izin verilmesine ve diğer girişlerin kısıtlanmasına yardımcı olabilir, lütfen şu şekilde yapın:
1. Sayfa sekmesine sağ tıklayın ve tıklayın Kodu Görüntüle açılışta içerik menüsünden Uygulamalar için Microsoft Visual Basic penceresi, aşağıdaki VBA kodunu içine kopyalayın.
VBA kodu: hücreleri yalnızca IP adresini kabul edecek şekilde doğrulayın
Private Sub Worksheet_Change(ByVal Target As Range) 'Update by ExtendOffice Dim xArrIp() As String Dim xIntIP1, xIntIP2, xIntIP3, xIntIP4 As Integer If Intersect(Target, Range("A2:A10")) Is Nothing Then Exit Sub Else If Target = "" Then Exit Sub End If xArrIp = Split(Target.Text, ".") If UBound(xArrIp) <> 3 Then GoTo EIP Else xIntIP1 = CInt(xArrIp(0)) xIntIP2 = CInt(xArrIp(1)) xIntIP3 = CInt(xArrIp(2)) xIntIP4 = CInt(xArrIp(3)) If (xIntIP1 < 1) Or (xIntIP1 > 255) _ Or (xIntIP2 < 1) Or (xIntIP2 > 255) _ Or (xIntIP3 < 1) Or (xIntIP3 > 255) _ Or (xIntIP4 < 1) Or (xIntIP4 > 255) Then GoTo EIP End If End If End If Exit Sub EIP: MsgBox "Please enter correct IP address" Target = "" End Sub
not: Yukarıdaki kodda, A2: A10 yalnızca IP adreslerini kabul etmek istediğiniz hücre aralığıdır.
2. Ardından bu kodu kaydedin ve kapatın, şimdi yalnızca doğru IP adresleri belirli hücrelere girilmesine izin veriyor.
Kolay bir özellik ile yalnızca IP adresleri biçimini zorlayın
Eğer varsa Kutools for Excel çalışma kitabınızda yüklü, onun IP Adresini Doğrula özelliği de bu görevi çözmenize yardımcı olabilir.
Kurduktan sonra Kutools for Excellütfen şunu yapın:
1. Yalnızca IP adreslerinin girilmesine izin verdiğiniz hücreleri seçin ve ardından Kutools > Yazmayı Önle > IP Adresini Doğrula. Ekran görüntüsüne bakın:
2. Bu özelliği uyguladıktan sonra, artık sadece IP adresinin girilmesine izin verilmektedir, aksi takdirde, size hatırlatmak için bir uyarı mesajı kutusu açılacaktır, ekran görüntüsüne bakın:
4.11 Toplam değeri aşan veri doğrulama kısıtlama değerleri
Aylık bir gider raporunuz olduğunu ve bütçe toplamının 18000 $ olduğunu varsayalım, şimdi, aşağıdaki ekran görüntüsünde gösterildiği gibi gider listesindeki toplam tutarın önceden ayarlanmış toplam 18000 $'ı aşmamasına ihtiyacınız var. Bu durumda, değerlerin toplamının önceden ayarlanmış bir toplamı aşmasını önlemek için SUM işlevini kullanarak bir veri doğrulama kuralı oluşturabilirsiniz.
1. Değerlerin sınırlandırılmasını istediğiniz hücrelerin listesini seçin.
2. Sonra tıklayın Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, aşağıdaki işlemleri yapın:
- seç görenek itibaren izin vermek açılır liste.
- Ardından aşağıdaki formülü kutucuğa giriniz. formül Metin kutusu.
- =SUM($B$2:$B$7)<=18000
- Tıkla OK Bu iletişim kutusunu kapatmak için düğmesine basın.
not: Yukarıdaki formülde, B2: B7 girişleri sınırlamak istediğiniz hücre aralığıdır.
3. Şimdi, B2:B7 aralığındaki değerleri girerken, değerlerin toplamı 18000 dolardan azsa doğrulama başarılı olur. Herhangi bir değer toplamı 18000 doları aşarsa, size hatırlatmak için bir uyarı mesajı kutusu açılır.
4.12 Veri doğrulama, başka bir hücreye dayalı hücre girişini kısıtlar
Bir hücre listesindeki veri girişlerini başka bir hücredeki değere göre sınırlamak istediğinizde, Veri Doğrulama özelliği bu işi de çözmenize yardımcı olabilir. Örneğin, C1 hücresi "Evet" metniyse, A2:A9 aralığının herhangi bir şey girmesine izin verilir, ancak C1 hücresi başka bir metinse, aşağıda gösterilen ekran görüntüleri gibi A2:A9 aralığına hiçbir şey girilmesine izin verilmez. :
Bu çözümü çözmek için lütfen şu şekilde yapın:
1. Değerlerin sınırlandırılmasını istediğiniz hücrelerin listesini seçin.
2. Sonra tıklayın Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, aşağıdaki işlemleri yapın:
- seç görenek itibaren izin vermek açılır liste.
- Ardından aşağıdaki formülü kutucuğa giriniz. formül Metin kutusu.
- =$C$1="Yes"
- Tıkla OK Bu iletişim kutusunu kapatmak için düğmesine basın.
not: Yukarıdaki formülde, C1 hücre, kullanmak istediğiniz belirli metni ve “ metnini içeriyor mu?Evet”, hücreleri temel alarak sınırlamak istediğiniz metindir, lütfen bunları ihtiyacınıza göre değiştirin.
3. Şimdi, C1 hücresinde "Evet" metni varsa, A2:A9 aralığına herhangi bir şey girilebilir, C1 hücresinde başka metin varsa, herhangi bir değer giremezsiniz, aşağıdaki demoya bakın:
4.13 Veri doğrulama, yalnızca hafta içi veya hafta sonlarının girilmesine izin verir
Hücre listesine yalnızca hafta içi (Pazartesi'den Cuma'ya) veya hafta sonlarına (Cumartesi ve Pazar) girmeniz gerekiyorsa, veri Doğrulama ayrıca size yardımcı olabilir, lütfen aşağıdaki adımları uygulayın:
1. Hafta içi veya hafta içi günlerin girilmesini istediğiniz hücrelerin listesini seçin.
2. Sonra tıklayın Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, aşağıdaki işlemleri yapın:
- seç görenek itibaren izin vermek açılır liste.
- Ardından, aşağıdaki formüllerden birini girin formül metin kutusu ihtiyacınız olduğu gibi.
- =WEEKDAY(A2,2)<6 (allow only weekdays)
=WEEKDAY(A2,2)>5 (allow only weekends) - Tıkla OK Bu iletişim kutusunu kapatmak için düğmesine basın.
not: Yukarıdaki formülde, A2 kullanmak istediğiniz sütunun ilk hücresidir.
3. Artık, ihtiyacınıza göre belirli hücrelere yalnızca hafta içi veya hafta sonu tarihini girebilirsiniz.
4.14 Veri doğrulama, bugünün tarihine göre girilen tarihe izin verir
Bazen, hücre listesine yalnızca bugünden büyük veya küçük tarihlerin girilmesine izin vermeniz gerekebilir. NS veri Doğrulama özelliği ile BUGÜN işlevi size bir iyilik yapabilir. Lütfen şu şekilde yapın:
1. Yalnızca gelecekteki tarihin (bugünden daha büyük bir tarih) girilmesini istediğiniz hücrelerin listesini seçin.
2. Sonra tıklayın Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, aşağıdaki işlemleri yapın:
- seç görenek itibaren izin vermek açılır liste.
- Ardından aşağıdaki formülü kutucuğa giriniz. formül Metin kutusu.
- =A2>Today()
- Tıkla OK Bu iletişim kutusunu kapatmak için düğmesine basın.
not: Yukarıdaki formülde, A2 kullanmak istediğiniz sütunun ilk hücresidir.
3. Şimdi, hücrelere yalnızca bugünün tarihinden daha büyük tarihler girilebilir, aksi takdirde, size hatırlatmak için bir uyarı mesajı kutusu açılır, ekran görüntüsüne bakın:
İpuçları:
1. Geçmiş tarihin (günümüzden daha az) girilmesine izin vermek için lütfen aşağıdaki formülü Veri Doğrulamasına uygulayın:
=A2<Today()
2. Sonraki 30 gün içindeki tarihler gibi belirli bir tarih aralığındaki tarihin girilmesine izin verin, lütfen aşağıdaki formülü Veri Doğrulamasına girin:
=AND(A2>TODAY(),A2<=(TODAY()+30))
4.15 Veri doğrulama, geçerli zamana dayalı olarak girilen zamana izin verir
Örneğin, geçerli zamana dayalı verileri doğrulamak istiyorsanız, hücrelere yalnızca geçerli saatten önceki veya sonraki zamanlar yazılabilir. Kendi veri doğrulama formülünüzü oluşturabilirsiniz, lütfen şu şekilde yapın:
1. Yalnızca geçerli saatten önceki veya sonraki zamanların girilmesini istediğiniz hücrelerin listesini seçin.
2. Sonra tıklayın Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, aşağıdaki işlemleri yapın:
- seç Zaman itibaren izin vermek açılır liste.
- Sonra seç daha kısa yalnızca geçerli zamandan önceki zamanlara izin vermek için veya daha büyük şu anki saatten sonraki sürelere izin vermek için Veri yıkılmak.
- Ve sonra Bitiş zamanı or Başlangıç saati kutusuna aşağıdaki formülü girin:
- =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
- Tıkla OK Bu iletişim kutusunu kapatmak için düğmesine basın.
not: Yukarıdaki formülde, A2 kullanmak istediğiniz sütunun ilk hücresidir.
3. Şimdi, belirli hücrelere yalnızca geçerli saatten önceki veya sonraki zamanlar girilebilir.
4.16 Veri doğrulama, belirli veya cari yılın tarihi
Yalnızca belirli bir yıldaki veya cari yıldaki tarihlerin girilmesine izin vermek için, YIL işlevine dayalı özel bir formülle veri doğrulamayı kullanabilirsiniz.
1. Yalnızca belirli bir yıldaki tarihlerin girilmesini istediğiniz hücrelerin listesini seçin.
2. Sonra tıklayın Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, aşağıdaki işlemleri yapın:
- seç görenek itibaren izin vermek açılır liste.
- Ardından aşağıdaki formülü kutucuğa giriniz. formül Metin kutusu.
- =YEAR(A2)=2020
- Tıkla OK Bu iletişim kutusunu kapatmak için düğmesine basın.
not: Yukarıdaki formülde, A2 kullanmak istediğiniz sütunun ilk hücresidir, 2020 kısıtlamak istediğiniz yıl sayısıdır.
3. Ardından, yalnızca 2020 yılındaki tarihler girilebilir, değilse, aşağıda gösterilen ekran görüntüsü gibi bir uyarı mesajı kutusu açılır:
İpuçları:
Yalnızca geçerli yıldaki tarihlere izin vermek için aşağıdaki formülü veri doğrulamaya uygulayabilirsiniz:
=YEAR(A2)=YEAR(TODAY())
4.17 Veri doğrulama, geçerli hafta veya aydaki tarih
Kullanıcının belirli hücrelere geçerli haftanın veya ayın tarihlerini girmesine izin vermek istiyorsanız, bu bölüm Excel'de bu görevle başa çıkmak için bazı formüller sunacaktır.
Geçerli haftanın tarihini girmeye izin ver
1. Yalnızca geçerli haftadaki tarihlerin girilmesini istediğiniz hücrelerin listesini seçin.
2. Sonra tıklayın Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, aşağıdaki işlemleri yapın:
- seç Tarih itibaren izin vermek açılır liste.
- Ve sonra seçin arasında itibaren Veri yıkılmak.
- içinde Başlangıç tarihi metin kutusu, şu formülü girin: =BUGÜN()-HAFTAGÜN(BUGÜN(),3)
- içinde Bitiş tarihi metin kutusu, şu formülü girin: =BUGÜN()-HAFTAGÜN(BUGÜN(),3)+6
- Sonunda, tıklayın OK düğmesine basın.
3. Ardından, yalnızca geçerli hafta içindeki tarihler girilebilir, aşağıdaki ekran görüntüsü gibi diğer tarihler engellenecektir:
Geçerli ayın tarihini girmeye izin ver
Yalnızca içinde bulunulan ayın tarihlerinin girilmesine izin vermek için lütfen aşağıdakileri yapın:
1. Yalnızca geçerli aydaki tarihlerin girilmesini istediğiniz hücrelerin listesini seçin.
2. Sonra tıklayın Veri > veri Doğrulama > veri Doğrulama, dışarı çıkmış veri Doğrulama iletişim kutusunun altında Ayarlar sekmesi, aşağıdaki işlemleri yapın:
- seç Tarih itibaren izin vermek açılır liste.
- Ve ardından, aşağıdakiler arasından seçim yapın: Veri yıkılmak.
- içinde Başlangıç tarihi metin kutusu, şu formülü girin: =TARİH(YIL(BUGÜN()),AY(BUGÜN()),1)
- içinde Bitiş tarihi metin kutusu, şu formülü girin: =TARİH(YIL(BUGÜN()),AY(BUGÜN()),GÜN(TARİH(YIL(BUGÜN()),AY(BUGÜN())+1,1)-1))
- Sonunda, tıklayın OK düğmesine basın.
3. Artık sadece içinde bulunulan ayın tarihleri seçilen hücrelere girilmesine izin veriyor.
5. Excel'de veri doğrulama nasıl düzenlenir?
Mevcut veri doğrulama kuralını düzenlemek veya değiştirmek için lütfen aşağıdaki adımları izleyin:
1. Veri doğrulama kuralına sahip hücrelerden herhangi birini seçin.
2. Daha sonra, Veri > veri Doğrulama > veri Doğrulama gitmek için veri Doğrulama iletişim kutusunda, kutuda kuralları ihtiyacınıza göre düzenleyin veya değiştirin ve ardından Bu değişiklikleri aynı ayarlarla diğer tüm hücrelere uygula bu yeni kuralı orijinal doğrulama ölçütleriyle diğer tüm hücrelere uygulama seçeneği. Ekran görüntüsüne bakın:
3. tıklayın OK değişiklikleri kaydetmek için.
6. Excel'de veri doğrulamalı hücreler nasıl bulunur ve seçilir?
Çalışma sayfanızda birden çok veri doğrulama kuralı oluşturduysanız, şimdi veri doğrulama kurallarını uygulayan hücreleri bulmanız ve seçmeniz gerekir. Özelliğe Git komutu, her türlü veri doğrulamasını veya belirli bir veri doğrulama türünü seçmenize yardımcı olabilir.
1. Bulmak istediğiniz çalışma sayfasını etkinleştirin ve veri doğrulaması olan hücreleri seçin.
2. Daha sonra, Ana Sayfa > Bul ve Seç > Özelliğe Git, ekran görüntüsüne bakın:
3. In Özelliğe Git iletişim kutusunu seçin Veri doğrulama > Türkiye, ekran görüntüsüne bakın:
4. Ve mevcut çalışma sayfasında veri doğrulaması olan tüm hücreler bir kerede seçildi.
İpuçları: Yalnızca belirli bir veri doğrulama türü seçmek istiyorsanız, lütfen önce öğrenmek istediğiniz belirli veri doğrulamasını içeren bir hücre seçin, ardından Özelliğe Git iletişim kutusunu seçin ve Veri doğrulama > Aynı.
7. Veri doğrulama kuralı diğer hücrelere nasıl kopyalanır?
Bir hücre listesi için bir veri doğrulama kuralı oluşturduğunuzu ve şimdi aynı veri doğrulama kuralını diğer hücrelere uygulamanız gerektiğini varsayalım. Kuralı yeniden oluşturmak yerine, mevcut kuralı kopyalayıp diğer hücrelere hızlı ve kolay bir şekilde yapıştırabilirsiniz.
1. Kullanmak istediğiniz doğrulama kuralına sahip bir hücreyi tıklatıp seçin ve ardından Ctrl + C kopyalamak için.
2. Ardından, bitişik olmayan birden çok hücreyi seçmek için doğrulamak istediğiniz hücreleri seçin, Ctrl hücreleri seçerken tuşuna basın.
3. Ardından, seçimi sağ tıklayın, seçin Special yapıştırmak seçenek, ekran görüntüsüne bakın:
4. In Special yapıştırmak iletişim kutusunu seçin Onaylama seçenek, ekran görüntüsüne bakın:
5. tıklayın OK düğmesi, şimdi doğrulama kuralı yeni hücrelere kopyalanır.
8. Excel'de geçersiz girişleri daire içine almak için veri doğrulama nasıl kullanılır?
Bazen mevcut veriler için veri doğrulama kuralları oluşturmanız gerekebilir, bu durumda hücre aralığında bazı geçersiz veriler görünebilir. Geçersiz veriler nasıl kontrol edilir ve değiştirilir? Excel'de, şunları kullanabilirsiniz: Geçersiz Verileri Daire içine al geçersiz verileri kırmızı bir daire ile vurgulama özelliği.
İhtiyacınız olan geçersiz verileri daire içine almak için veri Doğrulama veri aralığı için bir kural belirleme özelliği. Lütfen aşağıdaki adımlarla yapın:
1. Geçersiz verileri daire içine almak istediğiniz veri aralığını seçin.
2. Daha sonra, Veri > veri Doğrulama > veri Doğrulama, In veri Doğrulama iletişim kutusunda, doğrulama kuralını ihtiyacınıza göre ayarlayın, örneğin, burada 500'den büyük değerleri doğrulayacağım, ekran görüntüsüne bakın:
3. Daha sonra, OK iletişim kutusunu kapatmak için Veri doğrulama kuralını ayarladıktan sonra, Veri > veri Doğrulama > Geçersiz Verileri Daire içine al, ardından 500'den küçük tüm geçersiz değerler kırmızı oval ile daire içine alınmıştır. Ekran görüntülerine bakın:
Notlar:
- 1. Geçersiz bir veriyi düzeltir düzeltmez kırmızı daire otomatik olarak kaybolacaktır.
- 2. Bu Geçersiz Verileri Daire içine al özellik yalnızca en fazla 255 hücreyi daire içine alabilir. Geçerli çalışma kitabını kaydettiğinizde, tüm kırmızı daireler kaldırılacaktır.
- 3. Bu daireler yazdırılamaz.
- 4. Kırmızı daireleri tıklayarak da kaldırabilirsiniz. Veri > veri Doğrulama > Doğrulama Çevrelerini Temizle.
9. Excel'de veri doğrulama nasıl kaldırılır?
Bir dizi hücreden, geçerli çalışma sayfasından veya tüm çalışma kitabından veri doğrulama kurallarını kaldırmak için aşağıdaki yöntemler size bir iyilik yapabilir.
Veri doğrulama işleviyle seçilen aralıktaki veri doğrulamasını kaldırın
1. Kaldırmak istediğiniz veri doğrulaması olan hücreleri seçin.
2. Sonra tıklayın Veri > veri Doğrulama > veri Doğrulama, açılan iletişim kutusunda, Ayarlar sekmesini tıklayın Tümünü temizle düğmesi, ekran görüntüsüne bakın:
3. Daha sonra, OK bu iletişim kutusunu kapatmak için düğmesine basın. Ve seçilen aralığa uygulanan veri doğrulama kuralı bir kerede kaldırıldı.
İpuçları: Mevcut çalışma sayfasından veri doğrulamasını kaldırmak için lütfen önce tüm sayfayı seçin ve ardından yukarıdaki adımları uygulayın.
Kullanışlı bir özellik ile seçilen aralıktaki veri doğrulamasını kaldırın
Eğer varsa Kutools for Excelonun Veri Doğrulama Kısıtlamalarını Temizle özelliği, seçilen aralıktan veya tüm çalışma sayfasından veri doğrulama kurallarının kaldırılmasına da yardımcı olabilir.
Kurduktan sonra Kutools for Excellütfen şunu yapın:
1. Kaldırmak istediğiniz veri doğrulamasını içeren hücre aralığını veya tüm çalışma sayfasını seçin.
2. Daha sonra, Kutools > Yazmayı Önle > Veri Doğrulama Kısıtlamalarını Temizle, ekran görüntüsüne bakın:
3. Açılan bilgi istemi kutusunda, OK, ve veri doğrulama kuralı ihtiyaç duyduğunuz şekilde temizlendi.
VBA koduyla tüm çalışma sayfalarından veri doğrulamasını kaldırın
Tüm çalışma kitabından veri doğrulama kurallarını kaldırmak için, çok sayıda çalışma sayfası varsa yukarıdaki yöntemler zaman alıcı olacaktır, burada, aşağıdaki kod bu görevle hızlı bir şekilde başa çıkmanıza yardımcı olabilir.
1. Basılı tutun ALT + F11 tuşlarını açmak için Uygulamalar için Microsoft Visual Basic pencere.
2. Sonra tıklayın Ekle > modülve aşağıdaki makroyu modül pencere.
VBA kodu: Tüm çalışma sayfalarındaki veri doğrulama kurallarını kaldırın:
Sub RemoveDataValidation()
'Updateby Extendoffice
Dim xwsh As Worksheet
For Each xwsh In ActiveWorkbook.Worksheets
xwsh.Cells.Validation.Delete
Next xwsh
End Sub
3. Daha sonra, tuşuna basın. F5 anahtarı bu kodu çalıştırmak için ve tüm veri doğrulama kuralları tüm çalışma kitabından hemen silindi.
En İyi Ofis Üretkenliği Araçları
Kutools for Excel Sorunlarınızın Çoğunu Çözer ve Verimliliğinizi% 80 Artırır
- Süper Formül Çubuğu (birden çok metin ve formül satırını kolayca düzenleyin); Okuma Düzeni (çok sayıda hücreyi kolayca okuyun ve düzenleyin); Filtrelenmiş Aralığa Yapıştır...
- Hücreleri / Satırları / Sütunları Birleştirme ve Verilerin Saklanması; Bölünmüş Hücre İçeriği; Yinelenen Satırları ve Toplam / Ortalamayı Birleştirme... Yinelenen Hücreleri Önleyin; Aralıkları Karşılaştır...
- Yinelenen veya Benzersiz'i seçin Satırlar; Boş Satırları Seçin (tüm hücreler boştur); Süper Bul ve Bulanık Bul Birçok Çalışma Kitabında; Rastgele Seçim ...
- Tam kopya Formül referansını değiştirmeden Birden Çok Hücre; Otomatik Referans Oluştur Birden Çok Sayfaya; Madde İşaretleri Ekle, Onay Kutuları ve daha fazlası ...
- Sık Kullanılan ve Hızlı Eklenen Formüller, Aralıklar, Grafikler ve Resimler; Hücreleri Şifrele şifre ile; Posta Listesi Oluşturun ve e-posta gönder ...
- Metni Çıkar, Metin Ekle, Konuma Göre Kaldır, Alanı Kaldır; Sayfalama Alt Toplamları Oluşturma ve Yazdırma; Hücre İçeriği ve Yorumları Arasında Dönüştür...
- Süper Filtre (filtre şemalarını kaydedin ve diğer sayfalara uygulayın); Gelişmiş Sıralama ay / hafta / gün, sıklık ve daha fazlasına göre; Özel Filtre kalın, italik ...
- Çalışma Kitaplarını ve Çalışma Sayfalarını Birleştirin; Tabloları anahtar sütunlara göre birleştirin; Verileri Birden Çok Sayfaya Bölme; Toplu dönüştürme xls, xlsx ve PDF...
- Pivot Tablo Gruplaması hafta numarası, haftanın günü ve daha fazlası ... Kilidi Açılmış, Kilitli Hücreleri Göster farklı renklerle; Formülü / Adı Olan Hücreleri Vurgulayın...
- Word, Excel, PowerPoint'te sekmeli düzenlemeyi ve okumayı etkinleştirin, Publisher, Access, Visio ve Project.
- Yeni pencereler yerine aynı pencerenin yeni sekmelerinde birden çok belge açın ve oluşturun.
- Üretkenliğinizi% 50 artırır ve her gün sizin için yüzlerce fare tıklamasını azaltır!