Excel’de herhangi bir liste oluşturmak istediğinizde ilk olarak otomatik olarak bir girdi numarası oluşturmanız gerekir. Bu yazımda böyle bir listede otomatik olarak numara nasıl oluşturulabilir ona değinmek istiyorum.
Bir uyarıda bulunayım, oldukça uzun bir yazı olacak. Temelde basit formüller kullanacağız, ancak birden fazla şart olduğundan birden fazla kontrol yapmamız gerekecek ve bu kontrollere göre farklı değerler elde etmemiz gerekecek. Bu yüzden adım adım kontrol ederek ilerlemenizde fayda var. Örnekte verdiğim her bir formülü farklı hücrelere yazarak doğruluğundan emin olduktan sonra ilerlemeniz iyi bir yöntem olacaktır.
1. Hazırlık
Örnek olması için ben basit, 4 sütundan oluşan bir liste hazırlıyorum. Numaraları otomatik olarak oluşturacağımdan tabloda her hangi bir veri yok. Ek olarak ben bu listeyi hazırladıktan sonra Excel Tablo’su haline getirdim ve ismini de “Formlar” olarak değiştirdim. Bunu yapmamdaki amaç girdiğim formülün her satırda otomatik olarak yeniden oluşturulmasını istemem.
2. Uygulama
a. Numara Formatı
Ben bu uygulama için her sene baştan başlayan bir numaralandırma yapacağım, bunun için de yılın son iki hanesini kullanacağım. Siz isterseniz daha farklı bir sistem kullanabilirsiniz. Kullanacağım format FRM_YIL_#### şeklinde olacak. Son 4 haneyi sıfırdan başlayacak şekilde artan sırada bir numara olacak şekilde ayarlayacağım.
b. Ana Formül
Numara verilirken B2 hücresinde veri olup olmadığını kontrol edeceğim Bu örnekte tablo kullandığım için bu verinin olduğu sütun [Hazırlayan] olacaktır. Eğer buraya veri girildi ise otomatik olarak numara verilerek A sütununda ilgili satırda gösterilecek. Bunu yapacak işlev EĞER işlevi:
=EĞER([@Hazırlayan]<>””;”NUMARA VER”;””)
Bu formül bizim ana formülümüz. Eğer B2 hücresine veri girilmiş ise “NUMARA VER” kısmını gösterecek, eğer veri yok ise herhangi bir değer gösterilmeyecek. Şimdilik oldukça kısa ancak “NUMARA VER” kısmını dolduracağız.
Eğer siz tüm bilgiler girildikten sonra numara verilsin istiyorsanız BAĞ_DEĞ_DOLU_SAY ile listenizdeki sütun adedi ile o satıra girilmiş veri adedini kıyaslayarak ta bir formül yazabilirsiniz. Örnek olarak:
=EĞER(BAĞ_DEĞ_DOLU_SAY(Formlar[@[Hazırlayan]:[İçerik]])=3;”Numara Ver”;””)
Not: Siz tablo başlıklarını kullanmak istemezseniz ilk formülde [@Hazırlayan] yerine $B2, ikinci formüldeki BAĞ_DEĞ_DOLU_SAY kısmında da $B2:$D2 aralığını kullanabilirsiniz.
c. Yılın Son İki Hanesini Alma
İlk yapmamız gereken değer, içinde bulunduğumuz yılın son iki hanesini almak. İlk kullanacağımız işlev BUGÜN işlevi:
=BUGÜN()
Bu işlev ile bugünün tarihini alacağız. Sonrasında bu tarihin sadece yıl kısmını çekeceğiz. Bunun için de YIL işlevini kullanacağız
=YIL(BUGÜN())
En sonunda bu değerin en sağında yer alan 2 haneyi çekeceğiz:
=SAĞDAN(YIL(BUGÜN());2)
Bu formülü yazdığımızda elimizde bulunduğumuz yılın son iki hanesi olacak, tabii metin olarak.
Bu formülü şimdilik bir yere yazıp kaydedelim, daha sonra işimize yarayacak.
d. Önceki Satırın Kontrolü
Bir önceki form numarası bulunduğumuz satırın bir üstünde, bu da 2. satır için $A1 hücresi. Burada dikkate almamız gereken konu FRM_YIL_#### formatına göre 5. harften itibaren yılın son iki rakamı var. Eğer bu rakam yoksa veya bulduğum yılın son iki rakamında farklı ise yeni bir numara vereceğim. Bu karşılaştırma için önce aradaki değeri bulalım:
=PARÇAAL($A1;5;2)
A1 hücresinde “Form No” ifadesi olduğundanformül ” N” değerini geri döndürecek. Şimdi bunu bir önceki adımda bulduğum yılın son iki hanesi ile kıyaslayalım:
=PARÇAAL($A1;5;2)<>SAĞDAN(YIL(BUGÜN));2)
e. Yeni Numara Verme
Yeni numara vermek için daha önce bulduğumuz daha önce bulduğumuz yılın son iki hanesini alarak başına “FRM_”, sonuna “0001” rakamını ekleyeceğiz, bunun için de metin ekleme operatörünü kullanacağız:
=”FRM_”&SAĞDAN(YIL(BUGÜN());2)&”_0001″
Bu formülü yazdığınızda elinizde “FRM_20_0001” gibi bir değer olacaktır, ortadaki 20 değeri içinde bulunduğunuz yılın son iki hanesi olacaktır.
f. Artan Numara Verme
=(SAĞDAN($A1;4)*1)+1
Sonrasında bulduğumuz rakama “0” ları eklememiz lazım. Rakam uzunluğum 4 hane. Bulduğum rakamın toplam hane sayısını bu değerden çıkarırsam toplam kaç tane “0” eklemem gerektiğini bulurum
=4-UZUNLUK(SAĞDAN($A1;4)*1+1
İstediğim kadar “0” Eklemek için YİNELE işlevini kullanabilirim:
=YİNELE(“0”;4-UZUNLUK(SAĞDAN($A1;4)*1+1))
Bu formül ile tek haneli bir rakam elde etti ise “000” şeklinde bir metin olacaktır. Buna bulduğum rakamı da eklersem istediğim rakamı metin olarak elde etmiş olurum
=YİNELE(“0”;4-UZUNLUK(SAĞDAN($A1;4)*1+1))&SAĞDAN($A1;4)*1+1
Örnek olarak eğer bir önce verdiğim numara “0010” ise artık elimde “0011” rakamı olacaktır.
Sonrasında bana “FRM_YIL_” değeri lazım. Bunu bir önceki satırdaki form numarasının ilk 6 hanesini alarak bulabiliriz:
=SOLDAN($A1;6)
Yeni numarama için bulduğumuz bu iki değeri birbirine eklememiz yeterli:
=SOLDAN($A1;7)&YİNELE(“0”;4-UZUNLUK(SAĞDAN($A1;4)*1+1))&SAĞDAN($A1;4)*1+1
g. Formülü Birleştirme
Formülü birleştirirken, içten dışa doğru ilerleyeceğim. İlk şartım bir önceki satırda verdiğim numaranın kontrolü ve eğer aynı yıl içinde değilsek ya da ara başlık gibi numara içermeyen bir bir satıra denk geldi isek baştan numara vermek:
=EĞER(PARÇAAL($A1;5;2)<>SAĞDAN(YIL(BUGÜN));2);”İLK NUMARA”;”ARTAN NUMARA”)
İlk numara kısmına e. maddesinde gösterdiğim fromülü yazacağım:
=EĞER(PARÇAAL($A1;5;2)<>SAĞDAN(YIL(BUGÜN));2); “FRM_”&SAĞDAN(YIL(BUGÜN());2)&”_0001”; “ARTAN NUMARA”)
Artan numara kısmına da f maddesinde elde ettiğim formülü ekleyeceğim:
=EĞER(PARÇAAL($A1;5;2)<>SAĞDAN(YIL(BUGÜN());2); “FRM_”&SAĞDAN(YIL(BUGÜN());2)&”_0001”; SOLDAN($A1;7)&YİNELE(“0”;4-UZUNLUK(SAĞDAN($A1;4)*1+1))&SAĞDAN($A1;4)*1+1)
Bütün bunları ana formülümüz ile birleştirelim:
=EĞER([@Hazırlayan]<>””;EĞER(PARÇAAL($A1;5;2)<>SAĞDAN(YIL(BUGÜN());2); “FRM_”&SAĞDAN(YIL(BUGÜN());2)&”_0001”; SOLDAN($A1;7)&YİNELE(“0”;4-UZUNLUK(SAĞDAN($A1;4)*1+1))&SAĞDAN($A1;4)*1+1);””)
Evet arkadaşlar, böylece otomatik numaralandırma formülümüzü tamamlamış olduk. Çalışma şeklini aşağıda görebilirsiniz:
Erol bey merhabalar, benim Excel’de yapmak istediğim şöyle bir durum var, bütün gün internette aradım ama bir türlü istediğime denk gelecek bir çözüm bulamadım yardımcı olabilirseniz müteşekkir kalırım;
*Müstahsiller adında bir çalışma sayfamız var, burada müstahsil isimlerinin, ürünlerin ve bölgelerin adlarının yazılı olduğu sütunlar var.
*Günlük gelen ürünlerin girildiği bir başka çalışma sayfamız daha var, burada ise müstahsil, ürün ve bölge adları günlük manuel giriliyor.
Benim yapmak istediğim ise günlük sayfasındaki bölge sütununa, müstahsil ve ürün adına göre, diğer sayfadan(Müstahsiller) otomatik olarak bölge adlarını çekmek. Her gün tek tek aynı müstahsil ve ürünün bölgelerini tekrardan yazmak zorunda kalmamak.
Umarım anlatabilmişimdir, yardımcı olabilirseniz şimdiden teşekkürler.
Biraz daha açayım; aynı müstahsil farklı bölgelerden farklı ürünler gönderiyor olabilir. Örneğin Bekir adındaki müstahsil İzmir’den Enginar, Manisa’dan pancar, Uşak’tan hıyar gönderiyor olsun. Günlük giriş yapılan sayfadaki müstahsil sütunundaki hücreye Bekir, ürün sütunundaki hücreye enginar yazdığımda bölge sütunundaki hücreye de İzmir gelsin istiyorum. Bunu yapabilmem için bölge sütunundaki hücrelere nasıl bir formül yazayım ki Müstahsiller sayfasından otomatik olarak istediğim veriyi çeksin?
Merhaba,
Eğer Excel 365 1918 ve sonrasını kullanıyorsanız benzersiz işlevi ile listedeki her bir elemandan sadece bir tane olacak şekilde bir liste hazırlayabilirsiniz (http://excelileharikalar.com/index.php/2020/01/30/benzersiz-islevi/). Eğer daha eski bir Excel sürümü kullanıyorsanız (http://excelileharikalar.com/index.php/2018/11/04/formul-ile-benzersiz-degerleri-bulma/) ile bu işlemi Yapabilirsiniz. Sonrasında http://excelileharikalar.com/index.php/2019/04/24/tum-eslesenleri-cekme/ ile her bir girdi için tüm eşleşen değerleri çekeibilirsiniz.
Daha hızlı bir çözüm ise Power Query ile Müstahsiller tablosunu almak, Müstahsillerin isimleri olan sütun başlığına sağ tıklayarak “Diğer Sütunları Kaldır” ile sadece bu sütunu bırakın ve Giriş sekmesindeki “Satırları Kaldır” seçeneğinden “Yinelenenleri Kaldır” işlevini seçin. Bu her bir isimden tek bir tane olan bir sorgu oluşturacaktır. Bunu “Kapat ve Yükle” simgesindeki “Kapat ve Hedefe Yükle” seçeneğine tıklayarak gelen pencereden “Yalnızca Bağlantı Oluştur” seçeneği ile bir bağlantı oluşturun. Sonra Müstahsiller tablosu ile bu sorguyu http://excelileharikalar.com/index.php/2020/04/17/power-query-ile-tablolari-birlestirme/ yazımda anlattığım gibi birleştirin. Bu sayede her bir müstahsillin yolladığı ürünleri otomatik olarak çekmiş olursunuz. Daha sonra ana tablonuz değiştiğinde yapmanız gereken bu sorguları kopyalayarak sadece kaynak kısmını güncellemek.
Saygılarımla,
Zahmet edip cevap verdiğiniz için teşekkürler ancak yapmak istediğimi tam anlatamadım galiba çünkü verdiğiniz cevaplar yapmaya çalıştığım şeye çok uzak. Ama eminim bunları da başka bir yerde kullanabilirim. Yine de teşekkürler.
Eğer sadece tek bir kişinin herhangi bir bölgeden yolladığı ürünü bulmak istiyorsanız:
http://excelileharikalar.com/index.php/2018/11/17/cok-kistasli-arama-indis-kaydir-ile/
Merhaba
a1 hücresine veri girdikten sonra b1,b2,b3,b4 bulunan isimlerin yanına sıra numarası nasıl verebiliriz? Yani ben a1 e veri girdikçe b hücresindeki kişilere sırayla dağıtım yapsın
Merhaba,
Varolan bir veriyi değiştirmeniz mümkün değil. En fazla sütunun yanına bir sütun daha açar değeri oraya yazarsınız, sonra da bu iki değeri farklı bir sütunda birleştirerek istediğiniz değeri elde edersiniz.
Değerli çalışmalarınız için teşekkür ederim. Bu formül aynen olduğu şekilde bana gerekiyordu. Kendi istediğim tabloya uygulayamadım. AD hatası veriyor. Bende sizin excel’in aynısını oluşturarak çözümlemek istedim ancak aynı exceli oluşturmama rağmen ad hatası vermeye devam ediyor. Sorun ne olabilir_?
Merhaba,
Ben bu yazıda bir Tablo kullandım ve tablodaki alan adlarını kullandım. Eğer sizde bir tablo yok ise formüllerdeki alan adlar hata verecektir. Bunun yerine hücre adresleri kullanmanız gerekir.
Saygılarımla,
Cevabınız için teşekkür ederim. Aynı şekilde tablo oluşturdum ve üst bilgi satırı içerdiğini seçtim. Başka ne gibi bir sorun olabilir? Office 365 kullanıyorum.
Merhaba,
Burada sorun çıkaracak tek kısım “[@Hazırlayan]” kısmı. Buradaki ad, tablonun ilgili alanını ifade ediyor. Sizin tablonuzda bu başlıkta bir sütun var mı?
Saygılarımla,