Excel'de bir veri kümesine baştaki sıfırları eklemeniz gereken durumlar vardır. Excel'de Çalışan Kimlikleri veya İşlem Kimlikleri gibi kayıtları tutuyorsanız bu durum söz konusu olabilir.
Örneğin, aşağıda gösterildiği gibi veri kümenizde tutarlı bir görünüm elde etmek isteyebilirsiniz:
Bu öğreticide, Excel'de baştaki sıfırları eklemenin çeşitli yollarını öğreneceksiniz:
- Formatı Metne Dönüştürme
- Özel Sayı Biçimlendirmesini Kullanma
- Metin İşlevini Kullanma
- REPT/LEN işlevlerini kullanma
- VBA'yı kullanma
Bu yöntemlerin her birinin bazı avantajları ve dezavantajları vardır (her bölümde ele alınmıştır).
Bunların her birinin nasıl çalıştığını görelim.
Formatı Metne Dönüştürerek Başına Sıfırlar Ekleyin
Ne Zaman Kullanılır: Küçük bir sayısal veri kümeniz olduğunda ve bu düzenlemeyi manuel olarak yapmayı planlıyorsanız.
Aşağıda gösterildiği gibi pazarlama departmanının çalışan kimliklerine sahip olduğunuzu ve baştaki sıfırları ekleyerek bu Kimliklerin tutarlı görünmesini istediğinizi varsayalım.
Böylece, baştaki sıfırları (1 yerine 00001) girerek kimliği değiştirmeye çalışırsınız.
Ama hayrete düşürecek şekilde, Excel onu tekrar 1'e çevirir.
Bu, Excel'in 00001 ve 1'in aynı sayılar olduğunu ve aynı görüntüleme kurallarına uyması gerektiğini anlamasıyla gerçekleşir.
Şimdi sizin için ne kadar sinir bozucu olursa olsun, Excel'in nedenleri var.
Bu nedenle, Excel kurallarını bükmeden işi halletmek için, bu kuralın metin biçimlendirme için geçerli olmadığı gerçeğinden yararlanmanız gerekir.
İşte yapmanız gerekenler:
- Başına sıfırları manuel olarak eklemek istediğiniz hücreleri seçin.
- Ana Sayfa → Numara Grubu'na gidin ve açılır menüden Metin'i seçin.
Bu kadar!
Şimdi, baştaki sıfırları manuel olarak girdiğinizde, Excel kolayca uyacaktır.
Dikkat: Formatı Metne dönüştürdüğünüzde, bazı Excel işlevleri düzgün çalışmayacaktır. Örneğin, SUM/COUNT işlevi, metin biçiminde olduğu için hücreyi yok sayar.
Özel Sayı Biçimlendirmesini Kullanarak Başına Sıfırlar Ekleme
Ne Zaman Kullanılır: Sayısal bir veri kümeniz olduğunda ve sonucun sayısal (metin değil) olmasını istediğinizde.
Bir sayıyı belirli bir biçimde görüntülediğinizde, sayının temel değerini değiştirmez. Örneğin, 1000 sayısını 1000 veya 1.000 veya 1000.00 veya 001000 veya 26-09-1902 olarak görüntüleyebilirim (çift tarihler Excel'de arka uçtaki sayılardır).
Sayıyı göstermenin tüm farklı yollarında, sayının değeri asla değişmez. Değişen sadece görüntülenme şeklidir.
Başına sıfırlar eklemek için, temel değer değişmeden kalırken, bu şekilde gösterecek şekilde biçimlendirebiliriz.
Excel'de baştaki sıfırları eklemek için bu tekniği kullanma adımları şunlardır:
- Başına sıfır eklemek istediğiniz hücreleri seçin.
- Ana Sayfa → Numara Grubu'na gidin ve iletişim başlatıcısına tıklayın (sağ altta küçük bir eğik ok). Bu, Hücreleri Biçimlendir iletişim kutusunu açacaktır. Alternatif olarak klavye kısayolunu da kullanabilirsiniz: Control + 1.
- Hücreleri Biçimlendir iletişim kutusunda, Sayı sekmesinde, Kategori listesinde Özel'i seçin.
- Tür alanına 00000 girin
- Tamam'ı tıklayın.
Bunu yapmak, tüm sayıları her zaman beş basamaklı olarak görüntüler; sayı 5 basamaktan küçükse baştaki 0'lar otomatik olarak eklenir. Böylece 10, 00010 ve 100, 00100 olur.
Bu durumda altı sıfır kullandık, ancak verilerinizde daha fazla basamaklı sayılar varsa, formatı buna göre kullanmanız gerekir.
Not: Bu teknik yalnızca sayısal bir veri kümesi için işe yarar. A1, A2, A3 vb. gibi çalışan kimlikleriniz varsa, bunlar metindir ve yukarıda gösterildiği gibi özel biçimi uyguladığınızda değişmez.
TEXT İşlevini Kullanarak Başına Sıfırlar Ekleme
Ne Zaman Kullanılır: Sonucun metin olmasını istediğinizde.
METİN işlevi, değeri istediğiniz formata değiştirmenize olanak sağlar.
Örneğin 1'in 001 olarak görüntülenmesini istiyorsanız bunun için METİN işlevini kullanabilirsiniz.
Ancak, METİN işlevinin biçimi değiştireceğini ve onu METİN yapacağını unutmayın. Bu, 1'i 001 yaptığınızda, Excel'in yeni sonucu üç karakterli (tıpkı abc veya xyz gibi) metin olarak ele alacağı anlamına gelir.
METİN işlevini kullanarak baştaki sıfırları nasıl ekleyeceğiniz aşağıda açıklanmıştır:
- A sütununda sayılar varsa (örneğin A2:A100'den), ardından B2:B100'ü seçin ve aşağıdaki formülü girin:
=METİN(A2,000000″) - Formülü seçili tüm hücrelere uygulamak için Control + Enter tuşlarına basın.
Bu, tüm sayıları beş basamaklı olarak görüntüler, sayı 5 basamaktan küçükse baştaki 0'lar otomatik olarak eklenir.
Verileri metne dönüştürmenin bir yararı, artık bir çalışanın çalışan kimliğini kullanarak ayrıntılarını almak için DÜŞEYARA veya INDEX/MATCH gibi arama formüllerinde kullanabilmenizdir.
Not: Bu teknik yalnızca sayısal bir veri kümesi için işe yarar. A1, A2, A3 vb. gibi çalışan kimlikleriniz varsa, bunlar metindir ve yukarıda gösterildiği gibi özel biçimi uyguladığınızda değişmez.
REPT ve LEN İşlevlerini Kullanarak Başına Sıfırlar Ekleme
Ne Zaman Kullanılır: Sayısal/alfanümerik bir veri kümeniz olduğunda ve sonucun metin olmasını istediğinizde.
METİN işlevini kullanmanın dezavantajı, yalnızca sayısal verilerle çalışmasıydı. Ancak alfanümerik bir veri kümeniz varsa (örneğin A1, A2, A3 vb.), METİN işlevi başarısız olur.
Bu gibi durumlarda, REPT ve LEN işlevinin bir kombinasyonu hile yapar.
İşte nasıl yapılacağı:
- A sütununda sayılar varsa (örneğin A2:A100'den), ardından B2:B100'ü seçin ve aşağıdaki formülü girin:
=REPT(0,5-LEN(A2))&A2 - Formülü seçili tüm hücrelere uygulamak için Control + Enter tuşlarına basın.
Bu, tüm sayıları/dizeleri, gerektiğinde baştaki sıfırlarla 5 karakter uzunluğunda yapar.
İşte bu formül nasıl çalışır:
- LEN(A2) hücredeki dizi/sayıların uzunluğunu verir.
- =REPT(0,5-LEN(A2)) eklenmesi gereken 0 sayısını verir. Burada, veri kümemdeki maksimum dize/sayı uzunluğu olduğu için formülde 5 kullandım. Bunu verilerinize göre değiştirebilirsiniz.
- =REPT(0,5-LEN(A2))&A2, hücrenin değerine sıfır sayısını ekler. Örneğin, hücredeki değer 123 ise, bu 00123 değerini döndürür.
Özel İşlevi (VBA) Kullanarak Baştaki Sıfırları Ekleme
Excel'de baştaki sıfırları eklemek oldukça sık yapmanız gereken bir şeyse, özel bir işlev kullanmak iyi bir fikirdir.
Baştaki sıfırları eklemek için basit bir işlev oluşturacak VBA kodu:
'Code by Sumit Bansal, http://trumpexcel.com'dan Fonksiyon AddLeadingZeroes(ref As Range, Length As Integer) Dim i As Integer Dim Result As String Dim StrLen As Integer StrLen = Len(ref) For i = 1 To Length If i <= StrLen Then Result = Result & Mid(ref, i, 1) Else Result = "0" & Result End If Next i AddLeadingZeroes = Sonuç Bitiş Fonksiyonu
Bu kodu modül kodu penceresine eklemeniz yeterlidir ve onu diğer herhangi bir çalışma sayfası işlevi gibi kullanabilirsiniz.
Veya bunun için bir eklenti oluşturun ve iş arkadaşlarınızla paylaşabilirsiniz.
