Birçok kişi Excel'i bir veritabanı olarak kullanmaya çalışıyor. Bir veritabanı olarak çalışabilirken, bir veritabanı programında çok kolay olan bazı görevler Excel'de oldukça karmaşıktır. Bu görevlerden biri, ortak bir alana göre iki listeyi eşleştirmektir; bu, Excel DÜŞEYARA kullanılarak kolayca gerçekleştirilebilir. DÜŞEYARA işlevinin son derece yararlı olduğunu göreceksiniz, bu nedenle bu işlevi ne zaman ve nasıl kullanacağınızla ilgili bir örneğe bakın.
Seyahat acentenizin size çalışanlarınızın seyahat ettiği tüm yerlerin bir ay sonu raporu gönderdiğini varsayalım. Rapor, şehir adları yerine havaalanı kodlarını kullanır. Yalnızca kod yerine gerçek şehir adını kolayca girebilmeniz yararlı olacaktır.
İnternette, her havaalanı kodu için şehir adını gösteren bir liste bulup içeri aktarabilirsiniz.
Ancak, bu bilgiyi rapordaki her kayıtta nasıl elde edersiniz?
- DÜŞEYARA işlevini kullanın. DÜŞEYARA "Dikey Arama" anlamına gelir. En soldaki sütundaki anahtar alanıyla bir veri listesine sahip olduğunuz her zaman kullanılabilir.
- İşlevi yazmaya başlayın
=VLOOKUP(. İşlevle ilgili yardım almak için Ctrl + A tuşlarına basın. - DÜŞEYARA dört parametreye ihtiyaç duyar. Birincisi, orijinal rapordaki şehir kodudur. Bu örnekte, bu D4 hücresi olacaktır.
- Sonraki parametre, arama tablonuzun bulunduğu aralıktır. Aralığı vurgulayın. Aralığın mutlak olmasını sağlamak için F4'ü kullandığınızdan emin olun. (Mutlak bir referans, hem sütun numarasından hem de satır numarasından önce bir dolar işaretine sahiptir. Formül kopyalandığında, referans I3: J351'i göstermeye devam edecektir.
- 3. parametre Excel'e şehir adının hangi sütunda bulunduğunu söyler. I3: J351 aralığında, şehir adı 2. sütunda yer alır. Bu parametre için 2 girin.
- 4. parametre, Excel'e "kapat" eşleşmesinin uygun olup olmadığını söyler. Bu durumda değildir, bu yüzden False girin.
- Formülü tamamlamak için Tamam'ı tıklayın. Formülü aşağı kopyalamak için dolgu tutamacını sürükleyin.
- Mutlak formülleri dikkatlice girdiğiniz için, hedef şehri almak için E sütununu D sütununa kopyalayabilirsiniz. Bu durumda, tüm kalkışlar Toronto'daki Pearson Uluslararası Havaalanı'ndan yapılır.
Bu örnek mükemmel bir şekilde çalışsa da, izleyiciler DÜŞEYARA kullandığında, bu genellikle farklı kaynaklardan gelen listeleri eşleştirdikleri anlamına gelir. Listeler farklı kaynaklardan geldiğinde, listelerin eşleşmesini zorlaştıran her zaman ince farklılıklar olabilir. İşte neyin yanlış gidebileceğine ve bunların nasıl düzeltileceğine dair üç örnek.
-
Bir listede kısa çizgiler var, diğer listede yok.
=SUBSTITUTE()Çizgileri kaldırmak için işlevi kullanın . DÜŞEYARA'yı ilk denediğinizde N / A hataları alacaksınız.Kısa çizgileri bir formülle kaldırmak için YERİNE ALMA formülünü kullanın. 3 bağımsız değişken kullanın. İlk bağımsız değişken, değeri içeren hücredir. Bir sonraki argüman, değiştirmek istediğiniz metindir. Son argüman, ikame metindir. Bu durumda, kısa çizgileri hiçbir şeye dönüştürmek istemezsiniz, dolayısıyla formül de öyle
=SUBSTITUTE(A4,"-","").Açıklamayı almak için bu işlevi DÜŞEYARA içinde kaydırabilirsiniz.
-
Bu ince ama çok yaygındır. Listelerden birinde, girişten sonra bir boşluk vardır. Fazla boşlukları kaldırmak için = TRIM () kullanın. Formülü ilk kez girdiğinizde, tüm yanıtların N / A hataları olduğunu görürsünüz. Değerlerin listede olduğundan ve formülde her şeyin iyi göründüğünden eminsiniz.
Kontrol edilmesi gereken standart bir şey, arama değerinin bulunduğu hücreye gitmektir. Hücreyi Düzenleme moduna geçirmek için F2 tuşuna basın. Düzenleme moduna girdikten sonra, imlecin son harften bir boşluk uzakta olduğunu görebilirsiniz. Bu, girişin sonunda bir boşluk olduğunu gösterir.
Sorunu çözmek için KIRP işlevini kullanın.
=TRIM(D4)baştaki boşlukları, sondaki boşlukları kaldıracak ve dahili çift boşlukları tek bir boşlukla değiştirecektir. Bu durumda TRIM, takip eden boşluğu kaldırmak için mükemmel çalışır.=VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)formüldür. -
Gösteri notlarında bir bonus ipucundan bahsetmiştim: Eksik değerler için # N / A sonucunun bir boşlukla nasıl değiştirileceği. Arama değeriniz arama tablosunda değilse, DÜŞEYARA bir N / A hatası döndürür.
Bu formül
=ISNA(), formülün sonucunun N / A hatası olup olmadığını tespit etmek için işlevi kullanır . Hatayı alırsanız, IF işlevindeki 2. bağımsız değişken Excel'e istediğiniz metni girmesini söyleyecektir.=IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))
DÜŞEYARA, veri listelerini eşleştirirken zaman kazanmanızı sağlar. Temel kullanımı öğrenmek için zaman ayırın ve Excel'de çok daha güçlü görevler gerçekleştirebileceksiniz.
