Bir tablodaki verileri bulmak ve başka bir tabloya çıkarmak için Excel DÜŞEYARA işlevinin tüm sırları

2024 Yazar: Malcolm Clapton | [email protected]. Son düzenleme: 2023-12-17 04:13

Makaleyi okuduktan sonra, yalnızca bir Excel elektronik tablosundaki verileri nasıl bulacağınızı ve başka birine nasıl çıkaracağınızı değil, aynı zamanda DÜŞEYARA işleviyle birlikte kullanılabilecek teknikleri de öğreneceksiniz.

Excel'de çalışırken, çoğu zaman bir tablodaki verileri bulmaya ve başka bir tabloya çıkarmaya ihtiyaç vardır. Bunu nasıl yapacağınızı hala bilmiyorsanız, makaleyi okuduktan sonra, yalnızca bunun nasıl yapıldığını öğrenmekle kalmayacak, aynı zamanda sistemden maksimum performansı hangi koşullar altında sıkıştırabileceğinizi de öğreneceksiniz. DÜŞEYARA işleviyle birlikte kullanılması gereken çok etkili tekniklerin çoğu göz önünde bulundurulur.

DÜŞEYARA işlevini yıllardır kullanıyor olsanız bile, yüksek olasılıkla bu makale sizin için yararlı olacak ve sizi kayıtsız bırakmayacaktır. Örneğin, bir BT uzmanı ve ardından BT başkanı olarak 15 yıldır DÜŞEYARA kullanıyorum, ancak tüm nüanslarla ancak insanlara Excel'i profesyonel olarak öğretmeye başladığımda başa çıkabildim.

DÜŞEYARA kısaltmasıdır vdikey NSinceleme. Aynı şekilde DÜŞEYARA - Dikey ARAMA. İşlevin adı bize, sütunlarda (yatay olarak - sütunlar üzerinde yineleme ve satırı sabitleme) değil, tablonun satırlarında (dikey olarak - satırlar üzerinde yineleme ve sütunu sabitleme) arama yaptığını ima eder. DÜŞEYARA'nın bir kız kardeşi olduğu unutulmamalıdır - asla kuğu olmayacak çirkin bir ördek yavrusu - bu YATAYARA işlevidir. HLOOKUP, DÜŞEYARA'nın aksine yatay aramalar yapar, ancak Excel kavramı (ve aslında veri organizasyonu kavramı), tablolarınızın daha az sütuna ve daha fazla satıra sahip olduğu anlamına gelir. Bu nedenle, sütunlara göre birçok kez satırlara göre arama yapmamız gerekiyor. HLO işlevini Excel'de çok sık kullanıyorsanız, bu hayatta bir şey anlamamış olabilirsiniz.

Sözdizimi

DÜŞEYARA işlevinin dört parametresi vardır:

= DÜŞEYARA (;; [;]), Burada:

- istenen değer (nadiren) veya istenen değeri içeren bir hücreye referans (vakaların büyük çoğunluğu);

- parametre değerinin aranacağı İLK (!) sütununda bir hücre aralığına (iki boyutlu dizi) referans;

- değerin döndürüleceği aralıktaki sütun numarası;

- bu, aralığın ilk sütununun artan düzende sıralanıp sıralanmadığı sorusunu yanıtlayan çok önemli bir parametredir. Dizi sıralanırsa, DOĞRU veya 1 değerini belirtiriz, aksi takdirde - YANLIŞ veya 0. Bu parametre atlanırsa, varsayılan olarak 1 olur.

DÜŞEYARA işlevini bilenlerin çoğunun, dördüncü parametrenin açıklamasını okuduktan sonra, onu biraz farklı bir biçimde görmeye alışkın oldukları için rahatsız hissedebileceklerine bahse girerim: genellikle tam bir eşleşme hakkında konuşurlar. arama (YANLIŞ veya 0) veya aralık taraması (DOĞRU veya 1).

Şimdi, sonuna kadar söylenenlerin anlamını hissedene kadar bir sonraki paragrafı birkaç kez zorlamanız ve okumanız gerekiyor. Orada her kelime önemlidir. Örnekler anlamanıza yardımcı olacaktır.

DÜŞEYARA formülü tam olarak nasıl çalışır?

• Formül türü I. Son parametre atlanırsa veya 1'e eşit olarak belirtilirse, DÜŞEYARA ilk sütunun artan düzende sıralandığını varsayar, bu nedenle arama şu satırda durur: istenenden daha büyük değeri içeren satırın hemen önüne gelir … Böyle bir dize bulunamazsa, aralığın son satırı döndürülür.

  

• Formül II. Son parametre 0 olarak belirtilirse, DÜŞEYARA dizinin ilk sütununu sırayla tarar ve parametre ile ilk tam eşleşme bulunduğunda aramayı hemen durdurur, aksi takdirde # N / A (# N / A) hata kodu Geri döndü.

  

Formüller iş akışları

VPR tip I

VPR tip II

I formunun formülleri için sonuçlar

1. Değerleri aralıklar arasında dağıtmak için formüller kullanılabilir.
2. İlk sütun yinelenen değerler içeriyorsa ve doğru sıralanırsa, yinelenen değerlere sahip satırların sonuncusu döndürülür.
3. Açıkça ilk sütunun içerebileceğinden daha büyük bir değer ararsanız, tablonun oldukça değerli olabilecek son satırını kolayca bulabilirsiniz.
4. Bu görünüm, yalnızca istenen değerden küçük veya ona eşit bir değer bulamazsa # N / A hatasını döndürür.
5. Diziniz sıralanmamışsa formülün yanlış değerler verdiğini anlamak oldukça güçtür.

Tip II formüller için sonuçlar

İstenen değer dizinin ilk sütununda birden çok kez ortaya çıkarsa, formül sonraki veri alımı için ilk satırı seçecektir.

DÜŞEYARA performansı

Yazının doruk noktasına ulaştınız. Görünüşe göre, son parametre olarak sıfır veya bir belirtsem ne fark eder? Temel olarak, herkes sıfırı gösterir, çünkü bu oldukça pratiktir: dizinin ilk sütununu sıralamak için endişelenmenize gerek yok, değerin bulunup bulunmadığını hemen görebilirsiniz. Ancak sayfanızda birkaç bin DÜŞEYARA formülünüz varsa, DÜŞEYARA II'nin yavaş olduğunu fark edeceksiniz. Aynı zamanda, genellikle herkes düşünmeye başlar:

• Daha güçlü bir bilgisayara ihtiyacım var;
• Daha hızlı bir formüle ihtiyacım var, örneğin, çoğu kişi INDEX + MATCH'ı biliyor, bu da güya %5-10 kadar daha hızlı.

Ve birkaç kişi, I tipi DÜŞEYARA'yı kullanmaya başladığınızda ve ilk sütunun herhangi bir şekilde sıralandığından emin olduğunuzda, DÜŞEYARA'nın hızının 57 kat artacağını düşünüyor. Kelimelerle yazıyorum - ELLİ YEDİ KEZ! %57 değil, %5700. Bu gerçeği oldukça güvenilir bir şekilde kontrol ettim.

Bu kadar hızlı çalışmanın sırrı, ikili arama (yarılama yöntemi, dikotomi yöntemi) olarak adlandırılan sıralı bir dizi üzerinde son derece verimli bir arama algoritmasının uygulanabilmesi gerçeğinde yatmaktadır. Bu nedenle, I tipi DÜŞEYARA bunu uygular ve II tipi DÜŞEYARA, herhangi bir optimizasyon olmadan arama yapar. Aynısı, benzer bir parametre içeren KAÇINCI işlevi ve yalnızca sıralanmış dizilerde çalışan ve Lotus 1-2-3 ile uyumluluk için Excel'de bulunan ARA işlevi için de geçerlidir.

Formülün dezavantajları

DÜŞEYARA'nın dezavantajları açıktır: ilk olarak, belirtilen dizinin yalnızca ilk sütununda ve ikinci olarak yalnızca bu sütunun sağında arama yapar. Ve anladığınız gibi, gerekli bilgileri içeren sütun, bakacağımız sütunun solunda olabilir. Daha önce bahsedilen INDEX + KAÇINCI formül kombinasyonu bu dezavantajdan yoksundur, bu da onu DÜŞEYARA (DÜŞEYARA) ile karşılaştırıldığında tablolardan veri çıkarmak için en esnek çözüm haline getirir.

Formülü gerçek hayatta uygulamanın bazı yönleri

Aralık arama

Bir aralık aramasının klasik bir örneği, sipariş boyutuna göre bir indirim belirleme görevidir.

Metin dizelerini ara

Tabii ki DÜŞEYARA yalnızca sayıları değil aynı zamanda metni de arar. Formülün karakter durumu arasında ayrım yapmadığı akılda tutulmalıdır. Joker karakterler kullanıyorsanız, bulanık bir arama düzenleyebilirsiniz. İki joker karakter vardır: "?" - bir metin dizesindeki herhangi bir karakteri değiştirir, "*" - herhangi bir sayıdaki herhangi bir karakteri değiştirir.

Dövüş alanları

Soru, genellikle arama yaparken fazladan boşluk sorununun nasıl çözüleceği sorusu ortaya çıkar. Arama tablosu hala bunlardan temizlenebilirse, DÜŞEYARA formülünün ilk parametresi her zaman size bağlı değildir. Bu nedenle, hücrelerin fazla boşluklarla tıkanma riski varsa, bunu temizlemek için TRIM işlevini kullanabilirsiniz.

Farklı veri formatı

DÜŞEYARA işlevinin ilk parametresi, sayı içeren ancak hücrede metin olarak saklanan bir hücreye başvuruyorsa ve dizinin ilk sütunu doğru biçimde sayılar içeriyorsa, arama başarısız olur. Bunun tersi durum da mümkündür. Sorun, parametre 1'i gerekli formata çevirerek kolayca çözülebilir:

= DÜŞEYARA (−− D7; Ürünler! $ A $ 2: $ C $ 5; 3; 0) - D7 metin içeriyorsa ve tablo sayılar içeriyorsa;

= DÜŞEYARA (D7 & ""); Ürünler $ A $ 2: $ C $ 5; 3; 0) - ve tam tersi.

Bu arada, metni aynı anda birkaç şekilde bir sayıya çevirebilirsiniz, şunları seçin:

• Çift olumsuzlama -D7.
• Bir D7 * 1 ile çarpma.
• Sıfır ekleme D7 + 0.
• İlk güce yükseltme D7 ^ 1.

Bir sayıyı metne dönüştürme, Excel'i veri türünü dönüştürmeye zorlayan boş bir dizeyle birleştirme yoluyla yapılır.

# N / A nasıl bastırılır

EĞERHATA işleviyle bunu yapmak çok uygundur.

Örneğin: = EĞERHATA (DÜŞEYARA (D7; Ürünler! $ A $ 2: $ C $ 5; 3; 0); "").

DÜŞEYARA # N / A hata kodunu döndürürse, EĞERHATA onu durduracak ve parametre 2'yi (bu durumda boş bir dize) değiştirecektir ve herhangi bir hata oluşmazsa, bu işlev hiç yokmuş gibi davranacaktır, ancak yalnızca normal sonuç döndüren DÜŞEYARA vardır.

Dizi

Genellikle dizinin referansını mutlak yapmayı unuturlar ve diziyi uzatırken "yüzer". A2: C5 yerine $ A $ 2: $ C $ 5 kullanmayı unutmayın.

Referans dizisini çalışma kitabının ayrı bir sayfasına yerleştirmek iyi bir fikirdir. Ayağa kalkmaz ve daha güvenli olur.

Daha da iyi bir fikir, bu diziyi adlandırılmış bir aralık olarak bildirmek olacaktır.

Birçok kullanıcı, bir dizi belirtirken, tüm sütunları belirten A: C gibi bir yapı kullanır. Bu yaklaşımın var olma hakkı vardır, çünkü dizinizin gerekli tüm dizeleri içerdiği gerçeğini takip etme zorunluluğundan kurtulmuş olursunuz. Sayfaya orijinal diziyle satır eklerseniz, A: C olarak belirtilen aralığın ayarlanması gerekmez. Elbette, bu sözdizimsel yapı, Excel'i aralığı tam olarak belirtmekten biraz daha fazla iş yapmaya zorlar, ancak bu ek yük ihmal edilebilir. Saniyenin yüzde birinden bahsediyoruz.

Eh, dehanın eşiğinde - diziyi formda düzenlemek.

Çıkarılacak sütunu belirtmek için COLUMN işlevini kullanma

DÜŞEYARA kullanarak veri aldığınız tablo, arama tablosuyla aynı yapıya sahipse, ancak yalnızca daha az satır içeriyorsa, alınacak sütunların sayısını otomatik olarak hesaplamak için DÜŞEYARA'daki SÜTUN () işlevini kullanabilirsiniz. Bu durumda, tüm DÜŞEYARA formülleri aynı olacaktır (otomatik olarak değişen ilk parametreye göre ayarlanmıştır)! İlk parametrenin mutlak bir sütun koordinatına sahip olduğuna dikkat edin.

& "|" & ile bileşik anahtar oluşturma

Aynı anda birkaç sütunda arama yapmak gerekirse, arama için bir bileşik anahtar yapmak gerekir. Dönüş değeri metinsel değilse ("Kod" alanında olduğu gibi), ancak sayısal olsaydı, bunun için daha uygun ETOPLA formülü uygun olurdu ve bileşik sütun anahtarına hiç gerek olmazdı.

Bu benim bir Lifehacker için yazdığım ilk makale. Beğendiyseniz, sizi ziyaret etmeye davet ediyorum ve ayrıca DÜŞEYARA işlevini ve benzerlerini kullanmanın sırlarıyla ilgili yorumları memnuniyetle okuyorum. Teşekkürler.:)