FORMÜLLE METNİ SÜTUNLARA DÖNÜŞTÜRÜN (Text to Columns)

FORMÜLLE METNİ SÜTUNLARA DÖNÜŞTÜR

Excel’de belli ayırıcılarla birleştirilmiş bir metni sütunlara ayırıp tablo formatına dönüştürmek için genellikle Veri / Data menüsündeki Metni Sütunlara Dönüştür  / Text to Columns özelliğini kullanıyoruz. Bu yazımızda bu işlemi formüllerle nasıl yapabileceğimizi aktarıyor olacağım.

İlk etapta şöyle bir tablomuz olduğunu düşünelim;

Bu tablodaki Ad Soyad, Adet, Stok vb. gibi verileri tablodaki ilgili alanlara aktarmak istiyoruz.
O hâlde bunu yapmanın, özellikle formüllerle iç içe olan kişiler için güzel bir yöntemini paylaşayım. Bu işi; çok geniş ve uzun bir formül yazmaya gerek kalmadan üç tane formülü iç içe kullanarak çözebiliyoruz.

Kullanılacak formüller sırasıyla bunlar olacak;

  • DEVRİKDÖNÜŞÜM  > TRANSPOSE
  • XMLFİTRELE > FILTERXML
  • YERİNEKOY > SUBSTITUTE

Bu işin sırrı aslında; XMLFİLTRELE formülü ile sanal diyebileceğimiz bir xml yapısı oluşturup oradan veri almak diyebiliriz.
Şimdi web sitelerinde ya da online hmtl editörlerde tr ve td tagları ile oluşturduğumuz table (tablo) yapılarını düşünün. Bu tablo yapısını Excel‘deki her bir satır için formülle basitçe oluşturacağız.

Tabii ki bu arada tablo yapısını tr ve td tagları ile nasıl oluşturulduğu ile ilgili ufak bir örnek göstereyim; “

metin1metin2metin3

”  işte biz de bu yapıyı formülle oluşturduktan sonra XMLFİLTRELE formülü ile her bir td tagındaki veriyi alıp sütunlara aktarıyor olacağız.

Baktığımız zaman, ana tablomuzdaki metin içerisinde kullanılan ayırıcımız noktalı virgül olarak görünüyor, biz de bu ayırıcı yerine tablo yapısındaki td taglarını yazdırır isek yukarıdaki xml yapısını oluşturmuş oluruz. Noktalı virgül yerine td taglarını yazabilmek için mâlumunuz YERİNEKOY / SUBSTITUTE fonksiyonunu kullanmak gerekecek.

Oluşacak formülü adımlayacak olursak öncelikle bu şekilde başlamak iyi olacak.

=”

“&YERİNEKOY(A2;”;”;””)&”

”

Yukarıdaki formülü tamamladığımızda bize bu şekilde bir çıktı verecek.

 

Ali ÖZCAN5012000AA101B

 

İşte bu bizim veri alacağımız verinin tam hâlidir; bu noktadan sonra geriye, XMLFİLTRELE fonksiyonu ile genel metindeki td taglarının içerisindeki verileri alacağımızı belirtmek ve tüm metni satır boyunca aktarılmasını sağlamak kalıyor.

Aşağıdaki formülü kullanarak td taglarındaki verileri hücrelere aktarmış oluruz.

=XMLFİLTRELE(“

“&YERİNEKOY(A2;”;”;””)&”

“;”//td”))

Son aşamada artık alt alta dökülen verileri, yan yana sütunlara ayırmak için ise DEVRİK_DÖNÜŞÜM / TRANSPOSE fonksiyonunu genel formülün başına yazıyor olacağız.

=DEVRİK_DÖNÜŞÜM(XMLFİLTRELE(“

“&YERİNEKOY(A2;”;”;””)&”

“;”//td”))

Ve nihayetinde verileri tıpkı Metni Sütunlara Dönüştür / Text to Columns özelliği ile yatığımız gibi formüllerle de verileri bu şekilde sütunlara ayırmış olduk.

Excel’de İşlerinizi Kolaylaştıracak Üç Muhteşem Özellik

SIRAYI DEĞİŞTİR (TRANSPOSE) YAPIŞTIR

Çalışma sayfanızda sütunlardaki verileri döndürüp satırlar halinde yeniden düzenlemek istiyorsanız; yani satır başlıklarını sütun, sütun başlıklarını da satır yapmak istiyorsanız Sırayı Değiştir özelliğini kullanabilirsiniz. Eskiden bunu yapabilmenin yolu DEVRİK_DÖNÜŞÜM formülünü kullanmaktan geçiyordu ancak bu özellik sütunlardaki verilerin hızla satırlara yani tersine dönüşmesini sağlar.

Örneğin ülkelerin yıllara göre düzenlenmiş gelirlerini içeren bir listeniz olduğunu varsayalım. Bu listede satırda bulunan ülke adları ile sütunda bulunan yılların yerlerinin değişmesini istiyorsunuz. Listeyi kopyaladıktan sonra bir hücreye sağ tıklayıp yapıştırma seçeneklerinden Sırayı Değiştir’i seçerek satır-sütun dönüşümünü sağlayabilirsiniz.

 

BİÇİM BOYACISINA ÇİFT TIKLAMA

Renk, yazı tipi, yazı boyutu ve kenarlık gibi biçimlerin aynısını birden çok metin veya hücreye hızla uygulamak için Giriş sekmesinin altındaki Pano grubundan Biçim Boyacısı‘nı kullanabilirsiniz. Biçim boyacısı, seçilen kelimenin veya hücrenin biçimlerini kopyalayıp diğerlerine yapıştırmak olarak düşünülebilir. Bu özelliğe bir kez tıklandığında biçimlendirme özelliği bir kere kullanılırken çift tıklandığında biçim boyacısından çıkana kadar kullanmaya devam edilir. Biçim boyacısından çıkmak için biçim boyacısı simgesine tıklayabilir ya da ESC tuşuna basabilirsiniz.

Ayrıca biçim boyacısını Excel, Word, PowerPoint, OneNote ve Outlook’ta da tüm özellikleriyle birlikte rahatlıkla kullanabilirsiniz.

 

ÖZEL GİT

Git

işleviyle doğrudan bir sayfaya, satıra, işleve ya da belgenizdeki başka bir yere çok kolay bir şekilde gidebilirsiniz. Özel Git seçeneğini ise daha spesifik özelliklere sahip hücreleri göstermek, seçmek ve gitmek için kullanabilirsiniz. Formüllü hücreler; veri doğrulama, koşullu biçimlendirme içeren hücreler; boşluk içeren ya da gizlenen hücrelere kolayca erişebilirsiniz.

Örneğin; formül içeren hücreleri bulmak için klavye üzerinden f5 tuşuna basıp gelen Git ekranından Özel Git’i seçerek ya da Giriş sekmesinin altında bulunan Düzenleme grubundan Bul ve Seç’in ardından ise Özel Git seçilerek gelen ekrandan Formüller aktif hale getirilir. Dilerseniz sonrasında seçilen hücreleri biçimlendirebilirsiniz. Böylece belgenizdeki formüllü hücreleri çok kolay bir şekilde görebilirsiniz.

 

Bu üç özellik sayesinde birçok kez uğraşmak zorunda kaldığınız işleri sadece birkaç tıkla yapabilmek artık mümkün. Excel’in işlerinizi kolaylaştırmak için  yanınızda olduğunu unutmayın.

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