Excel'de DÜŞEYARA Nasıl Kullanılır
DÜŞEYARA, Excel'in en kullanışlı işlevlerinden biridir ve aynı zamanda en az anlaşılanlardan biridir. Bu makalede, DÜŞEYARA gerçek hayattan bir örnek yoluyla açıklığa kavuşturulmaktadır. Hayali bir şirket için kullanılabilir bir Fatura Şablonu oluşturacağız .
DÜŞEYARA bir Excel işlevidir . Bu makale, okuyucunun Excel işlevlerini geçmişte anladığını ve TOPLA, ORTALAMA ve BUGÜN gibi temel işlevleri kullanabileceğini varsayacaktır. En yaygın kullanımında, DÜŞEYARA bir veritabanı işlevidir, yani veritabanı tablolarıyla veya daha basit bir şekilde bir Excel çalışma sayfasındaki şeylerin listeleriyle çalışır . Ne tür şeyler? Eh, herhangi türden bir şey. Çalışanların, ürünlerin veya müşterilerin bir listesini veya CD koleksiyonunuzdaki CD'leri veya gece gökyüzündeki yıldızları içeren bir çalışma sayfanız olabilir. Gerçekten önemli değil.
İşte bir liste veya veritabanı örneği. Bu durumda, hayali şirketimizin sattığı ürünlerin bir listesi:
Genellikle bunun gibi listeler, listedeki her öğe için bir tür benzersiz tanımlayıcıya sahiptir. Bu durumda, benzersiz tanımlayıcı "Ürün Kodu" sütunundadır. Not: DÜŞEYARA işlevinin bir veritabanı / listeyle çalışması için, bu listenin benzersiz tanımlayıcıyı (veya "anahtar" veya "Kimlik") içeren bir sütuna sahip olması ve bu sütunun tablodaki ilk sütun olması gerekir . Yukarıdaki örnek veri tabanımız bu kriteri karşılamaktadır.
DÜŞEYARA kullanmanın en zor kısmı, tam olarak ne için olduğunu anlamaktır. Öyleyse önce bunu netleştirebilecek miyiz görelim:
DÜŞEYARA, benzersiz tanımlayıcının sağlanan bir örneğine dayalı olarak bir veritabanından / listeden bilgi alır.
Yukarıdaki örnekte, DÜŞEYARA işlevini bir öğe kodu ile başka bir e-tabloya eklersiniz ve size ya karşılık gelen öğenin açıklamasını, fiyatını ya da orijinal belgenizde açıklandığı gibi kullanılabilirliğini ("Stokta" miktarı) döndürür liste. Bu bilgilerden hangisini size geri iletecek? Buna formülü oluştururken karar vermelisin.
İhtiyacınız olan tek şey veritabanından bir bilgi parçasıysa, içinde DÜŞEYARA işlevi olan bir formül oluşturmaya gitmeniz çok zahmetli olur. Tipik olarak, şablon gibi yeniden kullanılabilir bir elektronik tabloda bu tür bir işlevselliği kullanırsınız. Bir kişi geçerli bir ürün kodunu her girdiğinde, sistem ilgili ürünle ilgili tüm gerekli bilgileri alır.
Bunun bir örneğini oluşturalım: Hayali şirketimizde tekrar tekrar kullanabileceğimiz bir Fatura Şablonu .
Önce Excel'i başlatıyoruz ve kendimize boş bir fatura oluşturuyoruz:
İşleyiş şekli şöyledir: Fatura şablonunu kullanan kişi "A" sütununa bir dizi ürün kodu girecek ve sistem her bir ürünün açıklamasını ve fiyatını ürün veritabanımızdan alacaktır. Bu bilgi, her bir kalem için satır toplamını hesaplamak için kullanılacaktır (geçerli bir miktar girdiğimiz varsayılarak).
Bu örneği basit tutmak amacıyla, ürün veritabanını aynı çalışma kitabında ayrı bir sayfada bulacağız:
Gerçekte, ürün veritabanının ayrı bir çalışma kitabında bulunması daha olasıdır. Veritabanının aynı sayfada, farklı bir sayfada veya tamamen farklı bir çalışma kitabında yer almasını gerçekten önemsemeyen DÜŞEYARA işlevinde çok az fark yaratır.
Bu nedenle, şuna benzeyen ürün veritabanımızı oluşturduk:
Yazmak üzere olduğumuz DÜŞEYARA formülünü test etmek için, önce boş faturamızın A11 hücresine geçerli bir ürün kodu giriyoruz:
Ardından, aktif hücreyi DÜŞEYARA ile veritabanından alınan bilgilerin depolanmasını istediğimiz hücreye taşırız. İlginç bir şekilde, çoğu insanın yanlış yaptığı adım budur. Daha fazla açıklamak için: A11 hücresindeki öğe koduna karşılık gelen açıklamayı alacak bir DÜŞEYARA formülü oluşturmak üzereyiz. Bu açıklamayı aldığımızda nereye koymasını istiyoruz? Elbette B11 hücresinde. DÜŞEYARA formülünü burada yazıyoruz: B11 hücresinde. Şimdi B11 hücresini seçin.
Excel'in sunduğu tüm kullanılabilir işlevlerin listesini bulmamız gerekir, böylece DÜŞEYARA'yı seçebilir ve formülü tamamlarken biraz yardım alabiliriz. Bu, önce Formüller sekmesine ve ardından İşlev Ekle'ye tıklanarak bulunur :
Excel'de bulunan işlevlerden herhangi birini seçmemizi sağlayan bir kutu görünür.
Aradığımızı bulmak için "arama" gibi bir arama terimi yazabiliriz (çünkü ilgilendiğimiz işlev bir arama işlevidir). Sistem bize Excel'deki aramayla ilgili tüm işlevlerin bir listesini döndürür. DÜŞEYARA , listedeki ikinci konumdur . Bunu seçin ve Tamam'a tıklayın .
Fonksiyon Argümanlar tüm bizi isteyen görünür kutu argümanlar (ya parametreler DÜŞEYARA işlevini tamamlamak için gerekli). Bu kutuyu bize aşağıdaki soruları soran bir işlev olarak düşünebilirsiniz:
- Veritabanında hangi benzersiz tanımlayıcıyı arıyorsunuz?
- Veritabanı nerede?
- Veritabanından benzersiz tanımlayıcıyla ilişkili hangi bilgi parçasını sizin için almasını istiyorsunuz?
İlk üç bağımsız değişken, zorunlu bağımsız değişkenler olduklarını gösterecek şekilde kalın olarak gösterilir (DÜŞEYARA işlevi bunlar olmadan tamamlanmaz ve geçerli bir değer döndürmez). Dördüncü argüman kalın değildir, yani isteğe bağlıdır:
Argümanları yukarıdan aşağıya sırayla tamamlayacağız.
Tamamlamamız gereken ilk argüman Lookup_value argümanıdır. İşlev , tanımını döndürmesi gereken benzersiz tanımlayıcıyı ( bu durumda öğe kodu ) nerede bulacağını söylememize ihtiyaç duyar . Daha önce girdiğimiz ürün kodunu seçmeliyiz (A11'de).
İlk argümanın sağındaki seçici simgesine tıklayın:
Ardından, öğe kodunu (A11) içeren hücreye bir kez tıklayın ve Enter tuşuna basın :
"A11" değeri ilk bağımsız değişkene eklenir.
Şimdi Table_array argümanı için bir değer girmemiz gerekiyor . Diğer bir deyişle, DÜŞEYARA'ya veritabanını / listeyi nerede bulacağını söylememiz gerekir. İkinci argümanın yanındaki seçici simgesine tıklayın:
Şimdi veritabanını / listeyi bulun ve başlık satırı hariç tüm listeyi seçin. Örneğimizde, veritabanı ayrı bir çalışma sayfasında yer almaktadır, bu nedenle önce bu çalışma sayfası sekmesine tıklıyoruz:
Ardından, başlık satırı hariç tüm veritabanını seçiyoruz:
… Ve Enter tuşuna basın . Veritabanını temsil eden hücre aralığı (bu durumda “'Ürün Veritabanı”! A2: D7 ”) bizim için otomatik olarak ikinci bağımsız değişkene girilir.
Şimdi üçüncü argüman olan Col_index_num'u girmemiz gerekiyor . Bu argümanı, DÜŞEYARA veri tabanından hangi bilgi parçasının A11'deki ürün kodumuzla ilişkilendirildiğini, bize geri dönmek istediğimizi belirtmek için kullanırız. Bu özel örnekte, öğenin açıklamasının bize iade edilmesini istiyoruz. Veritabanı çalışma sayfasına bakarsanız, "Açıklama" sütununun veritabanındaki ikinci sütun olduğunu fark edeceksiniz . Bu, Col_index_num kutusuna bir "2" değeri girmemiz gerektiği anlamına gelir :
Buraya "2" girmediğimizi not etmek önemlidir, çünkü "Açıklama" sütunu o çalışma sayfasındaki B sütunundadır. Veritabanı çalışma sayfasının K sütununda başlarsa, bu alana yine bir "2" girecektik çünkü "Açıklama" sütunu, "Tablo_dizisi" ni belirtirken seçtiğimiz hücre kümesindeki ikinci sütundur.
Son olarak, son DÜŞEYARA argümanına, Range_lookup'a bir değer girip girmemeye karar vermemiz gerekiyor . Bu bağımsız değişken, doğru veya yanlış bir değer gerektirir veya boş bırakılmalıdır. Veritabanları ile DÜŞEYARA kullanırken (zamanın% 90'ında olduğu gibi), bu argümana ne koyacağınıza karar vermenin yolu aşağıdaki gibi düşünülebilir:
Veritabanının ilk sütunu (benzersiz tanımlayıcıları içeren sütun) artan sırada alfabetik / sayısal olarak sıralanırsa, bu bağımsız değişkene bir true değeri girmek veya boş bırakmak mümkündür.
Veritabanının ilk sütun ise değil sıralanabilir veya azalan şekilde sıralanmış oluyor, o zaman gerekir değerini girin YANLıŞ bu tartışma içine
Bizim veritabanı ilk sütun gibi değil dizildi, biz girmek yanlış bu tartışma içine:
Bu kadar! DÜŞEYARA ihtiyacımız olan değeri döndürmek için gereken tüm bilgileri girdik. Click OK ürün kodu “R99245” tekabül açıklama doğru hücre B11 içine girildiğini düğmesi ve haber:
Bizim için oluşturulan formül şu şekildedir:
Biz girerseniz farklı açıklama hücre değişiklikleri yeni ürün kodu eşleştirmek için: hücreye A11 içine ürün kodu, biz DÜŞEYARA işlevi gücünü görmeye başlayacaksınız:
Öğenin fiyatını E11 hücresine döndürmek için benzer adımlar uygulayabiliriz . Yeni formülün E11 hücresinde oluşturulması gerektiğini unutmayın. Sonuç şöyle görünecek:
… Ve formül şöyle görünecek:
İki formül arasındaki tek farkın, üçüncü bağımsız değişkenin ( Sütun_indis_sayısı ) “2” den “3” e değişmesi olduğuna dikkat edin (çünkü verilerin veritabanındaki 3. sütundan alınmasını istiyoruz).
Bu öğelerden 2'sini almaya karar verseydik, D11 hücresine bir “2” girecektik. Daha sonra satır toplamını elde etmek için F11 hücresine basit bir formül gireriz:
= D11 * E1
… Buna benzeyen…
Fatura Şablonunun Tamamlanması
Şimdiye kadar DÜŞEYARA hakkında çok şey öğrendik. Aslında, bu makalede öğreneceğimiz her şeyi öğrendik. DÜŞEYARA'nın veritabanlarının yanı sıra başka durumlarda da kullanılabileceğini unutmamak önemlidir. Bu daha az yaygındır ve gelecekteki Nasıl Yapılır Geek makalelerinde ele alınabilir.
Fatura şablonumuz henüz tamamlanmadı. Tamamlamak için aşağıdakileri yapardık:
- Örnek öğe kodunu A11 hücresinden ve "2" hücresinden D11 hücresinden kaldırırdık. Bu, yeni oluşturduğumuz DÜŞEYARA formüllerimizin hata mesajları görüntülemesine neden olur:
Excel’in IF () ve ISBLANK () işlevlerini makul bir şekilde kullanarak bunu düzeltebiliriz . Formülümüzü buradan değiştiriyoruz… = DÜŞEYARA (A11, 'Ürün Veritabanı'! A2: D7,2, YANLIŞ) … buna… = EĞER (ISBLANK (A11), ””, DÜŞEYARA (A11, 'Ürün Veritabanı'! A2) : D7,2, YANLIŞ)) - B11, E11 ve F11 hücrelerindeki formülleri faturanın geri kalan kalem satırlarına kopyalardık. Bunu yaparsak, ortaya çıkan formüllerin artık veritabanı tablosuna doğru şekilde başvurmayacağını unutmayın. Bunu, veritabanı için hücre referanslarını mutlak hücre referanslarına değiştirerek düzeltebiliriz . Alternatif - ve hatta daha iyi - bir yaratabilecek aralık adı (örneğin, “Ürünler” olarak) tüm ürün veritabanı için ve hücre başvuruları yerine bu aralık adını kullanır. Formül bundan… = EĞER (ISBLANK (A11), ””, DÜŞEYARA (A11, 'Ürün Veritabanı'! A2: D7,2, YANLIŞ)) … buna… = EĞER (ISBLANK (A11), ”” şeklinde değişir. , DÜŞEYARA (A11, Ürünler, 2, YANLIŞ)) … ve sonra formülleri fatura kalemi satırlarının geri kalanına kopyalayın.
- Muhtemelen “kilit” hücreler bizim formüller (ya da daha doğrusu ihtiva edeceğini kilidini diğer hücreleri) ve sonra birisi faturaya doldurmak için geldiğinde özenle inşa formüller yanlışlıkla yazılmaz sağlamak amacıyla, çalışma sayfasını korumak.
- Dosyayı , şirketimizdeki herkes tarafından yeniden kullanılabilmesi için şablon olarak kaydederdik
Kendimizi gerçekten zeki hissediyorsak , başka bir çalışma sayfasında tüm müşterilerimiz için bir veri tabanı oluşturur ve ardından B6, B7 ve B8 hücrelerinde müşterinin adını ve adresini otomatik olarak doldurmak için F5 hücresine girilen müşteri kimliğini kullanırdık.
DÜŞEYARA ile pratik yapmak isterseniz veya sonuçta ortaya çıkan Fatura Şablonumuzu görmek isterseniz, buradan indirebilirsiniz.
