Daha önceki makalede belirlediğiniz kritere göre tüm satırı nasıl renklendirmeyi anlatmıştık. Bu makalede daha kullanışlı tüm satır renklendirme işlemini anlatmaya çalışacağım.
Önceki makalede listenin aynısını kullanacağız. Amacımız aşağıdaki listede Marka kolonunda değerleri Veri Doğrulama ile ayrı bir hücrede Açılır Kutu yapacağız. Hangi Marka seçilirse o marka olan değerdeki satır renklendirme işlemi yapacak.
Marka Listesini Veri Doğrulama İle Boş Hücreye Ekle
Marka alanındaki verilerin tamamını seçiyoruz ve kopyalıyouz. Boş bir yere yapıştırıyoruz. Hala veriler seçili iken Veri Sekmesinden – Yinelenenleri Kaldır seçiyoruz. Böylece Mükerrer olan kayıtları tek kayıt hale getiriyoruz.
Bu işlemden sonra tablodaki başlıkların üstüne boş bir satır ekleyip Marka başlığını yazıyoruz. Sonra I2 hücresine gelip daha önce oluşturduğumuz marka listesini Veri Doğrulama ile seçilir hale getireceğiz.
Bunu yapmak için I2 hücresindeyken Veri Sekmesi – Veri Doğrulama seçiyoruz. İzin Verilen alana Liste seçip verileri aşağıdaki gibi seçiyoruz.
Böylece Marka alanı filtre yapmış gibi tablonun başında seçilebilir hale getirdik.
Koşullu Biçimlendirme satır renklendirme için Formül Yazıyoruz
Daha önceki makalelerimden alışkın olanlar bilirler. Koşullu Biçimlendirme ve Veri Doğrulama da formül yazacaksam önce hücrede yazıp sonucunu görüyorum, sonra direk Koşullu Biçimlendirmeye veya Veri Doğrulamaya yapışıtıyorum. Çünkü önemli olan formülde yazdığım değil, çıkan sonucun DOĞRU ya da YANLIŞ yani mantıksal değer üretmesi Bu makale de de öyle yapacağız.
MBUL Fonksiyonu
MBUL Fonksiyonu aradığınız değerin hücrede olup olmadığını kontrol eder. (BUL Fonksiyonu da bulur. Ancak ben büyük Küçük harf duyarlılığı olsun istiyorum. Siz isterseniz BUL Fonksiyonunu da kullanabilirsiniz.)
Bul_Metin : Veri Doğrulama da tek kayıtla seçilen hücre olan I2’yi seçiyoruz. Böylece tabloda aradığımız değerin I2 hücresi olduğunu gösterdik.
Metin : Bu alana tablonun ilk satırını seçeceğiz. Yani B4 hücresini. Formülü bu alanda yazdıktan sonra Formül Kopyalama ile tüm tabloya uygulayacağız.
Herşey Tamam ama Hücre Sabitlemeyi Unuttuk
Tabloya aşağıya doğru yapıştırdığımızda I1 hücresi sabit kalması gerekiyor. Bunun için I1 hücresindeyken F4 tuşuna basıp adresi $I$1 şekilde düzeltiyoruz.
B4 değerini de Tüm Satırı Renklendir makalemizde olduğu gibi $B4 olarak değiştiriyoruz. Amacımız Koşullu Biçimlendirmeyi tüm tabloya yapıştırırken B kolonu değişmesin ama 4’ün önünde de $ işareti koymayarak ta satırlarda artış olmasını istedik. Detay için daha önceki makaleye göz atınız. Formül aşağıdaki gibi olacak şekilde değişiklikleri yapınız.
Formülü Test Edelim
Veri Doğrulama alanından farklı markaları seçelim. Formüldeki değişiklikleri gözlemleyelim.
Yukarıda görüldüğü gibi seçtiğimiz değere ait formülün sonucu 1 oluyor. Kritere uymayan satırlar ise #DEĞER! hatası veriyor. Bu formülden faydalanabiliriz. Koşullu Biçimlendirme ve Veri Doğrulama’da formüllerle bir şekilde 1 veya 0 ya da mantıksal DOĞRU veya YANLIŞ değeri değeri üretirseniz çalışacaktır.
Bu bilgiye ilaveten size özel bir teknikte söyleyim. Koşullu Biçimlendirme ve Veri Doğrulamada Pozitif yani 1,2,3,… değerler gelirse DOĞRU, #DEĞER! hatası alırsa YANLIŞ değeri olarak algılar. İşte biz yaptığımız formülde bu özelliğini kullanacağız. Yani 1 çıkan değerlerin tüm satırlarını renklendirmiş olacağız. #DEĞER! hatası olanlar ise renklenmeyecektir.
satır renklendirme için Formülü Koşullu Biçimlendirme’ye Yapıştır
Formülümüzü yazdık. Kontrolünü yaptık. Şimdi tek yapmamız gereken tablonun ilk satırındaki formülü kopyalıyoruz.
Tablodaki verilerin tamamını seçiyoruz. Koşullu Biçimlendirme gelip Yeni Kural’ı seçiyoruz.
Seçenek olarak en alttaki Formül Kullan seçeneğini seçiyoruz. Kopyaladığımız formülü aynen yapıştırıyoruz.
Biçimlendir bölümünden de koşul sağladığında nasıl bir şekil istiyorsanız o şekli seçin. Ben Zemin Kırmızı yaptım. İşlem bu kadar.
Sonuç
Aşağıda görüldüğü üzere hangi değeri seçerseniz formül kısmı 1 olanların Tüm satırı kırmızı, #DEĞER! olanlar ise renklenmemiş şekilde görülüyor.
Sizde bu örnekle farklı yöntemler, formüller kullanarak yapabilirsiniz. Fikirlerinizi sorularınızı bizimle paylaşmayı unutmayın.
Not : J hücresine yazdığımız formülün Koşullu Biçimlendirme veya Veri Doğrulama ile bir ilgisi yoktur. Mantığın anlaşılabilmesi için yazdım. J kolonunu silebilirsiniz.
Makalede kullandığımız çalışma kitabını buradan indirebilirsiniz.#MBUL #Koşullu Biçimlendirme #veridoğrulama #datavalidation #metinselfonksiyon #textFunction #excelFunction #excelFormul #advancedExcel #ileriExcel #excel #excelDersi #excelKursu #excelEğitimi
İlgili Aramalar
- excelde hücre değerine göre renklendirme
- excel açılır liste renklendirme
- excel koşullu biçimlendirme satır renklendirme
- excelde koşullu biçimlendirme ile tüm satırı renklendirme
- excel başka hücre değerine göre koşullu biçimlendirme