Özel Biçimlendirme kodlarının en faydalı kullanımları!

Merhaba,

Bu makalemde Excel’de özel biçimlendirme kodları ile verilerimizi nasıl farklı şekillerde gösterebileceğimizi anlatacağım.

Excel’de verilerimizi farklı bir şekilde göstermek için Hücreleri Biçimlendir’i kullanırız. Excel’in Giriş sekmesinin Sayı grubunda hücre içeriklerimizi Sayı, Tarih, % Yüzde, Para birimi, Ondalık vb. şeklinde gösterebileceğimiz hazır biçimlendirme formatları vardır.


Ancak bazen verilerimize farklı şekillerde görmek için farklı biçimlendirmeler uygulamak isteriz o zaman bu biçimlendirme kodlarını bizim yazmamız gerekir. Böyle durumlarda herhangi bir Excel hücresinde sağ tıklayınca Hücreleri Biçimlendir’i seçersek açılan pencereden isteğe uyarlanmış kendi biçimlendirme kodlarımızı kullanabiliriz.


Veya aşağıdaki gibi sayı grubunun sağ kösesinde ki işaretine tıklayınca da Hücreleri Biçimlendir penceresini açabiliriz. Ya da CTRL+1 kısayolu ile de Hücreleri Biçimlendir penceresini açabilirsiniz.


İsteğe uyarlanmış biçimlendirmede hücrenin içeriği değişmez yalnızca görünen formatı değişir. Özellikle sayısal değerlerini yuvarlanmasında bu durum sizleri yanıltmasın. Excel hücrenin orijinal değerini arka planda tutar ama görseldeki değer yuvarlatıldığından farklı görünebilir. Özellikle hesaplamalarda Excel hücrenin gerçek değerini baz alır.

Özellikle biçimlendirme kodlarında ağırlıklı olarak aşağıdaki karakterler kullanılır;

  • Sayısal değerler için # ve 0 (Sıfır) karakterleri
  • Metinsel değerler için @ karakteri
  • Mtinler çift tırnaklar içinde yazılır. Ör. # “adet”
    gibi.
  • Renkler için Kırmızı, Yeşil, Sarı, Mavi vs. İngilizce ise Red, Green, Yellow, Blue gibi.
  • Tarih Saat değerleri için g,a,y,s,d,n (Gün,Ay,Yıl,Saat,Dakika,Saniye) karakterleri kullanılır. Office İngilizce kullanılıyor ise d,m,y,h,m,s karakterleri kullanılır.

Aşağıdaki tabloda, Giriş sekmesinin Sayı grubunda bulunan kullanılabilir sayı biçimlerinin özeti verilmektedir.

Biçim Açıklama
Genel Bir sayı yazdığınızda Excel’in uyguladığı varsayılan sayı biçimi. Genel biçim ile biçimlendirilmiş sayılar, çoğunlukla tam olarak yazdığınız biçimde görüntülenir. Ancak hücre, sayının tamamını gösterebilecek genişlikte değilse, Genel biçimi ondalık sayıları yuvarlar. Genel sayı biçimi ayrıca, büyük sayılar (12 veya daha çok basamaktan oluşan) için bilimsel (üslü) gösterim kullanır.
Sayı Sayıların genel görüntülenişi için kullanılır. Kullanmak istediğiniz ondalık basamak sayısını, binlik ayırıcı kullanmak isteyip istemediğinizi ve negatif sayıları nasıl görüntülemek istediğinizi belirleyebilirsiniz.
Para birimi Genel para değerleri için kullanılır ve sayılarla varsayılan para birimi simgesini görüntüler. Kullanmak istediğiniz ondalık basamak sayısını, binlik ayırıcı kullanmak isteyip istemediğinizi ve negatif sayıları nasıl görüntülemek istediğinizi belirleyebilirsiniz.
Finansal Bu da para değerleri için kullanılır ancak, bir sütundaki para birimi simgelerini ve sayıların ondalık hanelerini hizalar.
Tarih Tarih ve saat seri numaralarını, belirlediğiniz tür ve yerel ayara (konum) göre tarih değerleri olarak görüntüler. Yıldız işareti (*) ile başlayan tarih biçimleri, Denetim Masası’nda belirlenen bölgesel tarih ve saat ayarlarındaki değişikliklere yanıt verir. Yıldız işareti olmayan biçimler Denetim Masası ayarlarından etkilenmez.
Saat Tarih ve saat seri numaralarını, belirlediğiniz tür ve yerel ayara (konum) göre saat değerleri olarak görüntüler. Yıldız işareti (*) ile başlayan saat biçimleri, Denetim Masası’nda belirlenen bölgesel tarih ve saat ayarlarındaki değişikliklere yanıt verir. Yıldız işareti olmayan biçimler Denetim Masası ayarlarından etkilenmez.
Yüzde Hücre değerini 100’le çarpar ve sonucu yüzde simgesiyle (%) görüntüler. Kullanmak istediğiniz ondalık basamak sayısını belirleyebilirsiniz.
Kesir Sayıyı, belirlediğiniz kesir türüne göre kesir olarak görüntüler.
Bilimsel Sayıyı, sayının bir kısmının yerine E+n kullanarak üslü gösterimle görüntüler. Burada E (Üs), kendinden önceki sayıyı 10 üssü n ile çarpar. Örneğin 2 ondalık basamaklı Bilimsel biçimi, 12345678901 sayısını, 1,23 çarpı 10 üssü 10 anlamına gelen 1,23E+10 olarak görüntüler. Kullanmak istediğiniz ondalık basamak sayısını belirleyebilirsiniz.
Metin Hücre içeriğini metin olarak kabul eder ve sayı yazdığınızda bile içeriği tam olarak yazdığınız gibi görüntüler.
Özel Sayıyı; posta kodu, telefon numarası veya Sosyal Güvenlik numarası olarak görüntüler.
Özel Var olan bir sayı biçimi kod kopyasını değiştirmenize olanak sağlar. Sayı biçim kodları listesine eklenen özel sayı biçimi oluşturmak için bu biçimi kullanın. 200 ve 250 özel sayı biçimleri arasındaki, bilgisayarınızda yüklü Excel’in dil sürümüne bağlı olarak ekleyebilirsiniz.

Şimdi aşağıdaki birçok örnek ile konumuzu iyice pekiştirelim.

1- Değerleri % Formata Dönüştürme

  • % işareti sayıyı yüzdeye çevirir, virgülden sonra koyduğunuz 0 (sıfır) adedi virgülden sonra gösterilecek basamak sayısını belirtir.


Not: Eğer ondalık ayracınız virgül değil nokta ise format kodunu 0.0% olarak yazmalısınız.

2- Virgülden Sonra Gösterilecek Basamak Sayısı

  • Virgülden sonra koyduğunuz 0 (sıfır) adedi virgülden sonra gösterilecek basamak sayısını belirtir.


  • Virgülden sonra 0 (sıfır) koymadık sayıyı yuvarlayarak tek basamaklı değer yazdı.


Not: Eğer ondalık ayracınız virgül değil nokta ise format kodunu 0.0 olarak yazmalısınız.

3- Bindelik Ayracı

  • #.##0 her binlik dilimde bindelik ayracı kullan.


Not: Eğer bindelik ayracınız nokta değil virgül ise #,##0 olarak yazmalısınız.

  • _) Büyük rakamların tamamını sığdırmak için hücre genişliğini arttırır.


  • Formatın soluna gelen her bir . (nokta) işareti sayıdan üç sıfır atar. iki nokta koyar isek altı sıfır atmış oluruz.


Not 1 : Eğer bindelik ayracınız nokta değil virgül ise #,##0,, olarak yazmalısınız.

Not 2 : Burada sayıyı bölmüyoruz görsel olarak sıfır atılmış halini görüyoruz.

4- Sayının Yanına Text Yazma (10 Adet, 100 Milyon Gibi)

  • Tırnak işareti ile sayının yanına metin, birim yazabiliriz.


Not: Sayının yanında metin olması matematiksel işlem yapmamızı engellemez.

5- Sayının Soluna Sıfır Getirme

  • Her bir sıfır girilen rakamın soluna getirilerek rakamı sıfır sayısı dolacak şekilde tamamlar.


6- Pozitif, Negatif Sayılar ve Sıfır’ın Biçimlendirilmesi

  • Formatın başına köşeli parantez ile getireceğimiz renk adları değeri renklendirir.

3 koşullu renklendirme yapalım. Bunun için hücre formatı önüne köşeli parantez içinde renk adı girilmeli.


7- Belli Bir Koşula Bağlı Biçimlendirme

  • [<30][green]0;[>30][blue]0;”-“30 dan küçük ise yeşil, büyük ise mavi, 30 ise “-” olsun.


8- Tarih Gösterimi

  • Eğer Ingilizce Excel kullanıyorsanız tarih formülleri için

(gün) yerine d (day)

a (ay) yerine m (month) yazmalısınız.

yıl ve year ayni harfle başladığı için değişiklik yapmanıza gerek yok.


9- Diğer Biçimlendirmeler

  • @ işareti hücre içindeki değeri simgeler, * işareti bir sonra gelen karakteri hücre genişliği kadar yaz anlamına gelir.


  • 0 (sıfır) sayısı ile kaç basamak sonrası ayıracağımızı belirttik, ile de ayraç tipini koyduk.


Not: Hücreleri metin biçimine getirerek de istediğimiz birimi rakamın yanına yazabiliriz. Ancak metin biçimindeki hücreler üzerinde toplam, çıkarma gibi sayısal işlemler yapamayız.

Yukardaki örneklerden anlaşılacağı üzere Hücre Biçimlendirme kodları hücrelerin gerçek değerlerini değiştirmez sadece farklı bir şekilde görüntülenmesini sağlar.
Bir başka makalede görüşmek üzere hoşça kalın.

Koşullu Biçimlendirme ile Tüm Satırınızı Farkedilir Hale Getirin!

Koşullu Biçimlendirme’de bir koşula göre hücrelerinizi hazır şablonlar kullanarak kolayca biçimlendirebilirsiniz. Mevcut olmayan biçimler için ise yeni biçimlendirme kuralı oluşturmanız gerekir. Tüm satırı biçimlendirme işlemi de hazır şablonlarda bulunmadığı için yeni kural oluşturulmalıdır. Bu işlem ile verilerin fazla olduğu listelerde satırın ilk hücresinden son hücresine kadar biçimlendirme yapılacağından o satırın bir değerine bakılmak istendiğinde hangi satırda olduğu gibi kargaşaların kolayca önüne geçilir.

Şimdi Excel Eğitimlerimizde de anlattığımız  Koşullu Biçimlendirme ile tüm satırı biçimlendirme işlemini verilerin fazla olduğu ve aranan ürünün hangi firmalarca üretildiğini gösteren bir örnek ile inceleyelim.

1.Adım: B sütununda ürün adlarının, H sütununda ise Üretici Firmaların bulunduğu listeyi seçtikten sonra Giriş sekmesinin altında bulunan Stiller grubundan Koşullu Biçimlendirme’yi ve ardından ise “Yeni Kural”ı seçiyoruz.

2.Adım: Açılan pencereden “Biçimlendirilecek hücreleri belirlemek için formül kullan” seçeneği ile ilgili alana formül yazarak işlem gerçekleştirilmelidir. Listede ürün adları B sütununda bulunmaktadır ve ürün sütununda “Atkı” ürününün bulunduğu satırları biçimlendirmek için B sütununu içeren uygun formülü yazıyoruz. Sabitleme işlemiyle hem sütun hem satır için $ işareti gelmektedir. Biçimlendirmenin tüm satırlarda kontrol edilmesi için sütunun sabitleme işaretini sabit tutup satırın işaretini kaldırıyoruz.

3.Adım: Biçimlendirme butonuyla da formülün doğru olduğu satırlara uygulanacak olan biçimler seçilir. Örneğimizde; dolgusunu turuncu, yazı tipini ise beyaz ve kalın olarak seçelim.

4.Adım: Tamam’a tıklayıp ilerledikten sonra Koşullu Biçimlendirme satırlara uygulanacaktır.

Siz de verilerinizin fazla olduğu çalışma sayfalarınızda aynı satırdaki bilgilere erişmekte zorluk yaşıyorsanız, satır sütun adlarına her seferinde tekrar bakmak zorunda kalıyorsanız, görselliğe önem verip daha dikkat çekici olmasını istiyorsanızsanız Koşullu Biçimlendirme ile tüm satırı biçimlendirmek işinize çok yarayacaktır.

Başka bir makalede görüşmek üzere…

İç İçe Eğerler Yazmak İçin Kahve Molanızı İptal Etmenize Gerek Yok; Gelin Düşeyara’yı Deneyin

Bu makalemizde Düşeyara ve İç içe Eğer ‘i çeşitli durumlar için karşılaştırarak bazı durumlarda hangisinin daha kullanışlı olacağını göreceğiz.

İK departmanının iş yerindeki çalışma sürelerine göre yıllık izin sayılarını yazmak istediği bir listede veya ürünler için kategorilerine göre gelecek komisyon miktarlarını eşleştirmek istediğinizde hangi yolu tercih edersiniz? Özellikle formül kullanmaya yeni başlayanlardansanız aklınıza ilk olarak İç içe Eğer fonksiyonu yazmak gelebilir. Peki bu tip durumlarda Düşeyara fonksiyonunu kullanmayı hiç düşündünüz mü?

Şimdi Düşeyara ve İç içe Eğer ‘i karşılaştırmaya başlayalım. İyi okumalar!

       Elimizde, satış kategorilerine göre komisyon miktarlarını   gösteren bir listemiz var. Burada, satış kategorilerine göre   komisyon miktarlarını iki fonksiyonu da kullanarak   bulacağız.

 

İki fonksiyonu karşılaştırırken ilk dikkat çeken kısım fonksiyonların uzunlukları olsa gerek. Bu kısa liste için 9 eğer fonksiyonunu iç içe yazmamız gerekir. İç içe eğer yazarken veri girişleri için ne kadar parantez, noktalı virgül, çift tırnak kullanmanız gerektiğini bir düşünün. Güzel bir kahve molanıza mâl olabilir. Öte yandan Düşeyara fonksiyonunu tek adımda kullanabiliriz.

 

Herhangi bir hücrede değişiklik yapıldığında Düşeyara fonksiyonu sorgulamada herhangi bir sıkıntı çekmeyecek, listeye yeni veri eklendiğinde aranılan verinin sonucunu vermeye devam edecektir. Ancak yazdığınız iç içe eğer fonksiyonuna veri girişini hücre isimlerini belirterek değil de, aşağıdaki örnekte yüzde değerlerinin formüle yazılmış olması gibi manuel girdiyseniz her değişen yüzdel bilgisini fonksiyonun içine tekrar yazmanız gerekecektir. Ayrıca manuel giriş yapmak basit harf hataların risklerini arttırabilir.

 

Ayrıca, listeye yeni bir satır eklendiğinde Eğer fonksiyonuna bu değerler otomatik olarak gelmez. Fonksiyona yeni değerleri sizin girmeniz gerekir. Düşeyara için, liste içine yeni bir satır eklediğinizde listeyi tekrar belirtmenize gerek yoktur. Liste içine eklendiği için o satırı da listenin bir parçası olarak görür ve aranılan verinin sonucunu getirmeye devam eder. Liste sonuna satır eklerseniz, Düşeyara’da seçimin dışında kaldığı için listenin devamı gibi algılayamaz. Bu sorunun çözümü için listeyi tabloya dönüştürüp dinamik hale getiririz. Yeni satırları hemen tablo bitiminden eklerseniz, dinamik tablo çalışma prensibi gereği satırları tablo alanı içine alır ve Düşeyara da arama sonuçlarında onları da gösterir.

 

Peki listeyi başka yere taşırsak ne olur? Listeyi farklı bir yere taşıdığımızda Düşeyara, çalışma mantığı gereği listenin bulunduğu yeni yerini fonksiyonda güncelleyecektir ve istediğiniz işlemi gerçekleştirmeye devam edecektir.

 

Şu ana kadar Düşeyara’nın İç İçe Eğer’den daha avantajlı olduğu durumları gördük. İç İçe Eğer’in de Düşeyara’ya göre tercih edilebileceği bir nokta elbette mevcuttur. Eğer’e karşı Düşeyara’nın dezavantajı Düşeyara’nın çalışma prensibi gereği aramaya başlanan sütunun sağındaki değerleri verip solundaki değerleri veremeyişidir. İç İçe Eğer ise istediğiniz hücreyi seçerek herhangi bir işlem yapmanıza olanak sağlar. Elbette sütunların yerlerini değiştirip Düşeyara kullanabilecek hale getirebilirsiniz. 😉 

Herhangi bir listede bir değer sorgulaması yapılıyorsa Düşeyara,
İç İçe Eğer’e göre oldukça pratik bir fonksiyondur. İç içe Eğer yazarken “Nerede kaldım ben?” gibi bir durumla karşılaşırken; Düşeyara fonksiyonunu yazarken dikkatinizin dağılmasından önce fonksiyonu çoktan yazmış ve uygulamış olursunuz.

Ve son bir bilgi daha, listedeki bir değeri, belirlenen aralıklara uymasına göre bir tanım vermek istersek Düşeyara’da aralık bak kısmına 1 yazarak bu işlemi gerçekleştirebiliriz. Bu konuyu anlattığımız bir sonraki makalemizde görüşünceye kadar hoşça kalın.

Excel listelerinizdeki mükerrer kayıtları kolayca kaldırın!

      Excel ileri eğitim konularından Yinelenenleri Kaldır işlevi, Excel listelerinizdeki mükerrer kayıtları sizin yerinize bulabilir ve onları çok hızlı ve kolay bir şekilde kaldırarak verilerinizi benzersiz hale getirebilir!

      İşlem çok basit!

     Mükerrer kayıtları kaldırmak istediğimiz liste seçilir ya da listenin içinde bir hücreye tıklanır.

     Veri Sekmesi-> Veri Araçları Grubundan-> Yinelenenleri Kaldır butonu tıklanır.

Listemizdeki veriler, kişisel bilgiler olduğundan satır boyunca birbirleriyle ilişkilidirler. Listede Ad Soyad, Unvan ve İl bilgilerinin birebir aynı olduğu başka satırlar varsa, kaldırması için açılan Yinelenenleri Kaldır penceresindeki Sütunlar alanından Ad Soyad, Unvan, İl sütunlarının her biri seçilir ve Tamam butonu tıklanır.

 

Bu işlem sonunda yinelenen değerler kaldırılmıştır.

 

 Yinelenenleri Kaldır penceresini inceleyelim:    

Tümünü Seç butonu seçili olduğunda tüm sütun değerleri birbirleriyle ilişkili olarak değerlendirilerek, benzersiz değerlere ulaşılır.
Tüm Seçimleri Kaldır butonu sütun seçimlerini kaldırır.
Verilerimde üst bilgi var seçeneği ise sütun başlıklarımızı temsil eder. Sütun başlıklarınız varsa bu seçeneği seçeriz. Aksi halde başlıklarla veriler arasında aynı olan değerler denk gelirse başlık veri olarak kabul edilir ve kaldırma işlemine dahil edilir.
Sütunlar penceresinden mükerrerliğinin kontrol edilmesini istediğimiz sütunları seçebiliriz.

 

Tablo içerisinde bir veya birkaç sütun seçilerek yinelenenleri kaldırma işlemi yapmaya başlandığında ilgili seçimin doğruluğunu sınamak için Yinelenenleri Kaldır Uyarısı Penceresi açılır. Bu ekranda Seçimi genişlet ve Geçerli seçimle devam et isimlerinde iki seçenek vardır. Aşağıdaki gibi uygulanabilirler…

 

  1. Seçimi genişlet ; Eğer verilerimizin yukardaki örnekte olduğu gibi, birbiri ile bağı varsa, mutlaka seçimi genişlet diyerek ilgili listenin tamamının seçilmesi sağlanmalıdır, böylece kaldırma işlemi tüm satırlar için gerçekleşir.    

     

    Ardından Yinelenenleri Kaldır penceresi açılır. Buradan yinelenmesini istemediğimiz sütunun başlığını seçeriz. Ad Soyad ve Unvan alanlarının benzeşmesine bakmaksızın, yalnızca il bazında mükerrer olan kayıtların kaldırması için yalnızca İl seçeneğini işaretleriz.  

      

    Açılan pencerede mükerrer değerlerin ve benzersiz değerlerin sayısı karşımıza gelir. Bu işlem sonucunda il bazında mükerrer değerler kaldırılmış olacaktır.    

  2. Bu işleme,  Geçerli seçimle devam et  ile işleme devam edilirse, yinelenenleri kaldır penceresinde  yalnızca il sütunu görünür ve bu şekilde işlem yapıldığında, sütunlar birbirlerinden bağımsız şekilde değerlendirilerek yalnız il sütunundaki yinelenen değerler kaldırılır ve verileriniz arasındaki veri bütünlüğünün bozulmasına neden olur. Ancak, eğer listenizdeki sütunların değerleri  birbirleriyle ilişkili değilse, yalnızca tek sütun seçerek  ilgili sütun üzerinden kaldırma işlemi yapabilirsiniz.

 

 

 

 

Office 365 ile gelen MaxIfs ve MinIfs Fonksiyonları

Office 365 ile gelen MaxIfs ve MinIfs fonksiyonları Excel kullanıcıları için büyük kolaylıklar sağlıyor…

Bu makalemizde Office 365 ile yeni gelen Maxifs ve Minifs fonksiyonlarını detaylı bir şekilde inceleyeceğiz.

Bu fonksiyonların çıkışı ile eskiden Excel eğitimlerinde tek seferde yapamadığımız bu fonksiyon yapısını artık tek seferde yazabileceğiz.

Birçok Excel kullanıcısının ihtiyacı olan bu fonksiyonları oluşturmak için normal Excel fonksiyonları ile yazmak oldukça zahmetlidir. Özellikle bu fonksiyonları normal Excel fonksiyonları ile yazmak için Dizi fonksiyonlar dediğimiz fonksiyon yapılarını kullanarak uzunca formüller yazmamız gerekiyor. Ayrıca bu yapıyı başka bir şekilde de PivotTable ile elde edebiliyoruz ama buda bir fonksiyon değil hazır bir PivotTable özelliğidir…

SumIfs (ÇokEtopla) mantığına çok benzer şekilde çalışan bu fonksiyonlar ile işlemlerimizi yapmak artık çok daha kolaydır. Bunun yanı sıra bilmeniz gereken bir konuda bu fonksiyonları sadece Office 365 aboneliğiniz varsa kullanılabilirsiniz.

MAXIFS (ÇokEğerMak) işlevi, verilen koşullar veya ölçütler grubu tarafından belirtilen hücreler arasında maksimum değeri döndürür.

MINIFS (ÇokEğerMin) işlevi, verilen koşullar veya ölçütler grubu tarafından belirtilen hücreler arasında minumun değeri döndürür.

Sözdizimi

MAXIFS (max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

MINIFS (max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Argüman Açıklama
Maks_range
(gerekli)
Maksimum belirlenecek gerçek hücre aralığı.
Criteria_range1
(gerekli)
Kriterlere göre değerlendirilecek hücreler dizisi.
Criteria1
(zorunlu)
Kriterler, hangi hücrelerin maksimum veya minumun olarak değerlendirileceğini tanımlayan bir sayı, ifade veya metin biçimindedir.
Criteria_range2,
Criteria2, … (isteğe bağlı)
Ek aralıklar ve bunlarla ilişkili kriterler. 126 aralık / ölçüt çiftine kadar girebilirsiniz.

Bu fonksiyonları kullanırken Max_range ve Criteria_range ifadelerinin boyutu ve şekli aynı olmalıdır aksi takdirde bu işlevler #DEĞER! hatasını döndürür.
Aşağıdaki örnekler ile fonksiyonumuzu pekiştirelim.
Örnek 1: Ali isimli kişinin yapmış olduğu en yüksek Satış Adet’ini bulalım.

Örnek 2: Ali isimli kişinin İstanbul’da yapmış olduğu en yüksek Satış Adet’ini bulalım.

Başka bir makalede görüşmek dileğiyle,

Hoşçakalın.