Excel'de metin olarak depolanan sayıları vlookup ile nasıl bulabilirim?
Excel'de VLOOKUP kullanırken, bir arama değeri metin olarak depolanırken arama sütununda sayılar olması veya bunun tersi durumunda, formatların uyuşmazlığına rastlanabilir. Bu da aramaların başarısız olmasına veya hatalara yol açabilir. Bu format uyuşmazlığı, özellikle veriler dış kaynaklardan geldiğinde, içe aktarıldığında veya büyük ve işbirlikçi veri setleriyle çalışıldığında sık karşılaşılan bir sorundur. Bu uyuşmazlıkları çözmek, VLOOKUP'un beklendiği gibi çalışmasını ve doğru bilgileri almanızı sağlamada önemlidir. Bu adım adım kılavuz, çalışma kitabınızdaki sayıların nasıl saklandığına bakılmaksızın güvenilir ve doğru aramalar sağlamak için bu format tutarsızlıklarını gidermek üzere birkaç pratik çözüm sunar.
Bu makale, bu tür hataları ele almak için etkili yolları göstermektedir; formül ayarlamalarını, Excel'in yerleşik araçlarını ve toplu veya otomatik işleme yönelik VBA otomasyonunu içermektedir. Ayrıca her yöntemin avantajlarını ve dikkat edilmesi gereken noktalarını da tartışıyoruz, böylece senaryonuz için en uygun yaklaşımı seçebilirsiniz.
- Formüllerle metin olarak depolanan sayıları vlookup ile bulma
- Kutools for Excel ile hızlıca format uyuşmazlıklarını düzeltin
- VBA Makrosu: VLOOKUP Öncesi Formatları Standardize Etme
- Diğer Yerleşik Excel Yöntemleri: Veri Formatlarını Düzeltmek İçin 'Metni Sütunlara Ayır' Özelliğini Kullanın
Formüllerle metin olarak depolanan sayıları vlookup ile bulma
Arama verilerinizde, bir yerde metin olarak depolanan sayılar varsa ve başka bir yerde gerçek sayılar varsa, VLOOKUP bu format tutarsızlığı nedeniyle eşleşmeleri bulamayabilir. En doğrudan çözümlerden biri, arama değerini veya arama sütununu anında tutarlı bir formata dönüştüren Excel formüllerini kullanmaktır. Bu yaklaşım çoğu çalışma sayfası işlemi için iyi sonuç verir, uygulaması kolaydır ve orijinal verileriniz değişmeden kalır.
Örneğin, arama değeri metin olarak depolanıyorsa ve tablodaki ilgili alan sayı olarak biçimlendirilmişse, VALUE fonksiyonunu VLOOKUP formülü içindeki metni sayıya dönüştürmek için kullanabilirsiniz.
Sonucu görüntülemek istediğiniz boş bir hücreye aşağıdaki formülü girin:
=VLOOKUP(VALUE(G1),A2:D15,2,FALSE)Formülü girdikten sonra, aşağıdaki ekran görüntüsünde gösterildiği gibi ölçütlerinize karşılık gelen değeri almak için Enter tuşuna basın:
Parametre açıklamaları ve ipuçları:
- G1: Aramak istediğiniz değeri içeren hücre (metin veya sayı olabilir).
- A2:D15: Arama sütununu ve geri döndürmek istediğiniz bilgileri içeren sütunları içeren veri tablonuzun aralığı.
- 2: Geri döndürmek istediğiniz sonucun (tablodaki en soldaki sütundan itibaren) sütun numarası.
Arama değerlerinde önde veya sonda boşluklar olduğuna dikkat edin çünkü bunlar da aramaların başarısız olmasına neden olabilir. Verileriniz fazladan boşluklar içeriyorsa TRIM fonksiyonunu kullanmayı düşünebilirsiniz.
Arama değeri gerçek bir sayıysa (sayı formatı), ancak tablodaki ilgili alan metin olarak depolanıyorsa, aramayı yapmadan önce sayıyı metne çevirmeniz gerekir. TEXT fonksiyonu bu senaryo için uygundur:
=VLOOKUP(TEXT(G1,0),A2:D15,2,FALSE)Bunu hedef hücrenize girin, Enter'a basın ve aşağıdaki gibi doğru sonuç geri dönecektir:
Burada TEXT fonksiyonu içindeki sayı format kodu “0”, eşleştirme öncesinde sayınızın düz metin değerine dönüştürülmesini sağlar.
Arama değerlerinizin muhtemel formatlarından emin değilseniz veya arama sütununuzda hem metin hem de sayıların olmasını bekliyorsanız, tüm olasılıkları sorunsuz bir şekilde ele almak için IFERROR fonksiyonunu kullanarak her iki yaklaşımı da iç içe geçirebilirsiniz:
=IFERROR(VLOOKUP(VALUE(G1),A2:D15,2,0),VLOOKUP(TEXT(G1,0),A2:D15,2,0))Bu formülü sonuç hücresine girin. İlk önce değerinizi sayıya çevirerek aramayı deneyecek; eğer bu başarısız olursa (örneğin, değer bir sayıya dönüştürülemezse), daha sonra değerinizi metne çevirip tekrar arayacaktır. Bu özellikle karmaşık formatlara sahip veri setlerinde veya veri giriş standartlarının düzgün olmadığı paylaşılan dosyalarda çok faydalıdır.
Yukarıdaki formüllerden herhangi birini girdikten sonra, birden fazla arama değerine uygulamak isterseniz formülü bitişik hücrelere kopyalamayı unutmayın - sadece hücreyi seçin, dolgu tanıtıcısını aşağı çekin veya gerektiği gibi Ctrl+C ve Ctrl+V kullanın. Büyük tablolar için bu formülleri kullanmak, orijinal veritabanınızı değiştirmeden güvenilir eşleşmeleri sağlar.
Bu yöntem, çoğu çalışma sayfasına dayalı arama için esnek ve evrensel olarak uygulanabilir bir çözüm sunar. Ancak, çok büyük veri setleri için veya birçok kaydı otomatik olarak işlemek gerektiğinde, daha fazla verimlilik için VBA gibi otomasyon araçlarını kullanmayı düşünebilirsiniz.
Kutools for Excel ile hızlıca format uyuşmazlıklarını düzeltin
Eğer daha hızlı, formülsüz bir çözüm tercih ediyorsanız, Kutools for Excel, Metin ve Sayı Arasında Dönüştür adlı kullanıcı dostu bir araç sunar. Bu özellik, birkaç tıklamayla metin olarak depolanan sayıları gerçek sayılara veya tam tersine dönüştürmenizi sağlar. Özellikle VLOOKUP veya MATCH gibi aramaları yapmadan önce format sorunlarını çözmede oldukça yararlıdır.
Kutools for Excel'i yükledikten sonra lütfen şu adımları izleyin.
- Problemli verilerinizi içeren aralığı seçin (örneğin, metin olarak depolanan sayılar).
- “Kutools” > “Metin” > “Metin ve Sayı Arasında Dönüştür”e gidin.
- Açılan iletişim kutusunda:
- Metin olarak biçimlendirilmiş sayılar nedeniyle arama hatalarını düzeltiyorsanız “Metni sayıya dönüştür” seçeneğini seçin. (Veya arama değerleri metin olarak depolanıyorsa “Sayıyı metne dönüştür” seçeneğini seçin.)
- Veri formatını hemen dönüştürmek için “Tamam” düğmesine tıklayın.
- Metin olarak biçimlendirilmiş sayılar nedeniyle arama hatalarını düzeltiyorsanız “Metni sayıya dönüştür” seçeneğini seçin.
Metinleri sayılara dönüştürdükten sonra, dönüştürülen hücreler gerçek sayılar gibi davranacak ve tutarsızlık için yeşil üçgen göstergeleri artık görünmeyecektir.
Bu yaklaşım yardımcı sütunlara, formüllere veya VBA'ya gerek kalmadan hızlı temizlik için idealdir, ardından VLOOKUP uygulanabilir.
Kutools for Excel - Excel'i 300'den fazla temel araçla güçlendirin. Sürekli ücretsiz AI özelliklerinden yararlanın! Hemen Edinin
VBA Makrosu: VLOOKUP Öncesi Formatları Standardize Etme
Büyük veri setleriyle düzenli olarak çalışan, harici kaynaklı dosyalar alan veya tekrarlayan otomasyon gerektiren kullanıcılar için basit bir VBA makrosu kullanarak hem arama değeri sütunundaki hem de arama tablosu sütunundaki veri formatını programlı olarak standardize edebilirsiniz. Bu şekilde, VLOOKUP'u çalıştırmadan önce tüm verilerin sayılara veya metne dönüştürüldüğünden emin olursunuz, format uyuşmazlığı nedeniyle eşleştirme hatalarını ortadan kaldırırsınız. VBA, özellikle toplu işleme için kullanışlıdır, manuel ayarlamaları azaltır ve otomasyon yoluyla veri tutarlığını sağlar.
Avantajlar: Büyük veri aralıkları veya sık tekrarlanan iş akışları için formatlama otomasyonu; eksik veya tutarsız formatlama riskini en aza indirir; tekrarlayan görevler için uygundur.
Dezavantajlar: Makro kısıtlamaları olan veya VBA makrosu kullanımına aşina olmayan kullanıcılar için uygun değildir.
Hücre formatlarını standardize etmek için bir makro kullanmanın nasıl yapılacağına dair adımlar:
1. Geliştirici sekmesine gidin ve Visual Basic'e tıklayarak VBA düzenleyicisini açın. Yeni pencerede Ekle > Modül'e tıklayın ve ardından aşağıdaki kodu modül alanına kopyalayıp yapıştırın:
Sub StandardizeLookupFormats()
' Ask the user to select the lookup column and choose a target format
Dim rng As Range
Dim userChoice As Integer
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select the range to standardize (lookup or data column):", xTitleId, Type:=8)
If rng Is Nothing Then Exit Sub
userChoice = MsgBox("Convert selected data to Number? (Click Yes to convert to Number, No to convert to Text)", vbYesNoCancel, xTitleId)
If userChoice = vbYes Then
For Each cell In rng
If IsNumeric(cell.Value) Then
cell.Value = Val(cell.Value)
cell.NumberFormat = "General"
End If
Next
ElseIf userChoice = vbNo Then
For Each cell In rng
If Not IsEmpty(cell.Value) Then
cell.Value = CStr(cell.Value)
cell.NumberFormat = "@"
End If
Next
Else
Exit Sub
End If
End Sub2. VBA düzenleyicisini kapatın. Makroyu çalıştırmak için Excel'e dönün, Alt+F8'e basın, StandardizeLookupFormats'ı seçin ve Çalıştır'a tıklayın.
İşlem detayları ve ipuçları:
- Bu makro, standardize etmek istediğiniz sütunu (LOOKUP aralığınız veya TABLE aralığınız) seçmenizi isteyecektir.
- Seçimden sonra, aralığı sayılara mı (Evet’e tıklayın) yoksa metne mi (Hayır’a tıklayın) dönüştürmek istediğinizi soracaktır. VLOOKUP'un güvenilir bir şekilde eşleşme yapmasını sağlamak için hem arama hem de tablo sütunlarınız için aynı formatı seçin.
- Bu makroyu çalıştırdıktan sonra, sonuçlar hemen görünmezse çalışma sayfasını yeniden hesaplamak için (F9 tuşuna basın) veya VLOOKUP formüllerinizi yeniden uygulamanız gerekebilir.
- Makroların devre dışı bırakıldığına dair bir hata alırsanız, devam etmeden önce Excel ayarlarınızda makroları etkinleştirin.
Bu çözüm, tekrarlayan veri içe aktarmaları veya VLOOKUP veya diğer arama işlemlerini uygulamadan önce büyük veri setlerindeki tutarsız sütunları temizlemek için idealdir.
Diğer Yerleşik Excel Yöntemleri: Veri Formatlarını Düzeltmek İçin 'Metni Sütunlara Ayır' Özelliğini Kullanın
Excel'de sayı ve metin formatlarını hizalamak için hızlı bir yöntem Metni Sütunlara Ayır özelliğini kullanmaktır. Bu yerleşik araç genellikle verileri bölmek için kullanılır ancak ayrıca formül düzenleme olmadan bir format dönüşümünü zorlayabilir, bu da tek seferlik bir düzeltme yapmak istediğinizde veya basit listelerle uğraşırken kullanışlıdır.
Avantajlar: Çok kolay, formül veya kod gerektirmez, orijinal veri yapısını korur; Dezavantajlar: Tek seferlik düzeltmeler için uygundur, veriler değişirse otomatik olarak güncellenmez.
Bir sütundaki metin olarak depolanan sayıları (veya tersini) dönüştürmek için bu yöntemi kullanmak için:
- Format uyuşmazlığı şüphesi olan sütunu seçin (örneğin, arama sütununuz veya VLOOKUP tarafından başvurulan sütun).
- Veri sekmesinde, Metni Sütunlara Ayır'a tıklayın.
- Sihirbazda, Delimited (sınırlayıcı) seçeneğini işaretleyin ve Sonraki'ye tıklayın.
- Tüm sınırlayıcı onay kutularının işaretini kaldırın (verileri bölmüyorsunuz); Sonraki'ye tıklayın.
- Sütun Veri Formatı'nda, Excel'in sayıları sayılara tanımasını zorlamak için Genel'i seçin veya sayıları metne dönüştürmek için Metin'i seçin.
- İşlemi tamamlamak için Tamam'a tıklayın.
İşlem tamamlandığında, verilerinizin formatı zorla sayı veya metin olarak hizalanmış olacaktır, bu da VLOOKUP uyuşmazlıklarını çözer. Dönüşümün beklendiği gibi çalıştığından emin olmak için birkaç hücre kontrol edin. Gerekirse, maksimum tutarlılık elde etmek için hem arama sütununuz hem de arama değerleriniz için işlemi tekrarlayın.
Pratik hatırlatmalar: “Metni Sütunlara Ayır” özelliği verileri doğrudan değiştirir, bu nedenle hemen sağda mevcut verileriniz varsa hücre içeriklerinin üzerine yazabilir. Emin değilseniz sütununuzu boş bir alana kopyalamayı düşünün ve toplu veri araçlarını kullanmadan önce dosyanızın bir yedeğini kaydedin.
En İyi Ofis Verimlilik Araçları
Kutools for Excel ile Excel becerilerinizi güçlendirin ve benzersiz bir verimlilik deneyimi yaşayın. Kutools for Excel, üretkenliği artırmak ve zamandan tasarruf etmek için300'den fazla Gelişmiş Özellik sunuyor. İhtiyacınız olan özelliği almak için buraya tıklayın...
Office Tab, Ofis uygulamalarına sekmeli arayüz kazandırır ve işinizi çok daha kolaylaştırır.
- Word, Excel, PowerPoint'te sekmeli düzenleme ve okuma işlevini etkinleştirin.
- Yeni pencereler yerine aynı pencerede yeni sekmelerde birden fazla belge açıp oluşturun.
- Verimliliğinizi %50 artırır ve her gün yüzlerce mouse tıklaması azaltır!
Tüm Kutools eklentileri. Tek kurulum
Kutools for Office paketi, Excel, Word, Outlook & PowerPoint için eklentileri ve Office Tab Pro'yu bir araya getirir; Office uygulamalarında çalışan ekipler için ideal bir çözümdür.
- Hepsi bir arada paket — Excel, Word, Outlook & PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans — dakikalar içinde kurulun (MSI hazır)
- Birlikte daha verimli — Ofis uygulamalarında hızlı üretkenlik
- 30 günlük tam özellikli deneme — kayıt yok, kredi kartı yok
- En iyi değer — tek tek eklenti almak yerine tasarruf edin