Merhaba sevgili okuyucu!

Bu yazıda muhtemelen en çok bilinenlerden birini açıklamak istiyorum. faydalı fonksiyonlar Excel'de bir DÜŞEYARA işlevidir. Bir fonksiyon hakkında çok şey söylenebilir, ancak ilk kayda değer şey, fonksiyonun en karmaşık ve en az anlaşılır olanlardan biri olmasıdır.

Makalede basit tutmaya çalışacağım ve erişilebilir dil DÜŞEYARA işlevinin nasıl çalıştığını açıklayın ve özelliklerini, açıklamasını ve sözdizimini örneklerle gösterin.

Peki bu fonksiyon tam olarak nedir, ne işe yarar ve nasıl yazılır. DÜŞEYARA işlevinin çalışmasıyla ilgili en basit açıklama, benzersiz bir tanımlayıcı, onunla ilişkili belirli bilgiler için belirttiğiniz ölçüte göre değerler listesinde arama yapar.

DÜŞEYARA işlevinin adını deşifre ederseniz, ilk harfle bu işlevin nasıl çalıştığı netleşir, B anlamına gelir "Dikey", yani, içindeki değerleri arar, ancak sahip olacağımız yatay listeler için. DÜŞEYARA işlevi ilk kez Excel 2000 ile birlikte kullanıma sunuldu.
Excel'deki DÜŞEYARA işlevi aşağıdaki sözdizimine sahiptir:

= DÜŞEYARA (_arama değeri _; _ tablo_; _ sütun numarası_; _ [aralık görünümü] _), nerede:

• aranan_değer- bu tam olarak aramamız gereken değerdir ve herhangi bir değer olabilir: sayı, tarih, metin, istenen değeri içeren bir hücreye başvuru veya başka bir formülle elde edilen değer;
• tablo- bunlar çeşitli veriler içeren iki veya daha fazla sütundur, bu arada işlev, aramadaki karakterlerin durumunu dikkate almaz;
• sütun_numarası- bu, bulunan satırda bulunan, değerin elde edileceği belirtilen aralıktaki sütunun numarasıdır;
• interval_view- bu parametre tam olarak neyi arayacağımızı belirler; tam bir eşleşme için argüman eşit olacaktır YANLIŞ veya yaklaşık bir eşleşme, argüman olur NS... Bu parametre isteğe bağlıdır, ancak yine de önemlidir. Aşağıdaki örneklerde tam ve yaklaşık eşleşme formüllerinin nasıl oluşturulacağını göstereceğim.

Şimdi, bir örnek kullanarak, pratikte DÜŞEYARA işlevi geçerli sayfada arama yapmak için çok nadiren kullanıldığından, başka bir sayfada bazı verilerin nasıl aranacağına bakalım:

DÜŞEYARA ("GM"; $ A $ 5: $ B $ 10; 2)

Formül, geçerli sayfada A sütununda "GM" metnini arar.
Tavsiye! "Tablo" argümanını kullanırken, (bu, $ işaretli hücre adresidir) gibi bir seçeneğin kullanılması arzu edilir. Bu durumda, arama aralığı sabit olacak ve formül kopyalandığında değişmeyecektir.

Değer bulma örneğine, DÜŞEYARA işlevinin başka bir çalışma kitabında nasıl çalıştığına bakalım:

DÜŞEYARA ("GM"; [Dosyaya giden yol] Temel! A2: B10; 2)

Gördüğünüz gibi, karmaşık bir şey yok, argüman sadece karmaşıklaştı. "tablo", şimdi adına sahip bir dosyaya bir bağlantı eklendi.

Tavsiye! İki dosya içeren bir formül girmek daha iyidir, birinde DÜŞEYARA işlevine girersiniz ve "tablo" argümanına ulaştığınızda, ikinci dosyaya gidin ve fareyle aramanız gereken aralığı seçin.

Ayrıca ne zaman bir örnek düşünebilirsiniz Kesin değer ne kullanacağımızı bilmiyoruz DÜŞEYARA formülü joker karakterler. Bazen belirli bir şey değil, ortak özelliklere sahip bir şey bulmamız gerekiyor, bir seçenek olarak birçok kanepemiz var. farklı modeller, diğer mobilyalar gibi, ama sadece onları seçmemiz gerekiyor.

DÜŞEYARA işlevi, değiştirildiğinde aşağıdaki karakterleri kullanabilir:

• "?" (soru işareti) - herhangi bir karakteri değiştirmenize izin verir;
• "*" (Yıldız)- herhangi bir sayı ve karakter dizisini değiştirecektir.

DÜŞEYARA (“A *”; $ A $ 2: $ B $ 10; 1; YANLIŞ)

Tavsiye! DÜŞEYARA işlevinin doğru çalışması için dördüncü argüman olarak "YANLIŞ" parametresini kullanmanız gerekir.
DÜŞEYARA işlevinin sözdiziminde tam veya yaklaşık eşleşme konusuna zaten değindiysek, o zaman buna daha yakından bakalım:

• eğer argüman "Aralık görünümü" eşittir "UZANMAK", bu durumda formül, arama değeri bağımsız değişkeniyle tam bir eşleşme arar. Formül, "gerekli değer" argümanına karşılık gelen iki veya daha fazla değerle karşılaşırsa, listeden ilki seçilir, eşleşme bulunamaması durumunda formül geri döner;
• eğer argüman "Aralık görünümü" anlamı var "NS" formül yaklaşık bir eşleşme arayacak, daha doğrusu, işlev önce tam bir eşleşme arayacak ve ancak o zaman onu bulamayan yaklaşık bir eşleşme seçecektir.

Tavsiye!"Aralık görünümü" bağımsız değişkeninin "DOĞRU" olması veya belirtilmemesi durumunda, ilk sütundaki değerin küçükten büyüğe doğru olması gerekir. Aksi takdirde, DÜŞEYARA işlevinden hatalı bir sonuç çıkma olasılığı vardır.

Tam bir arama yapıldığında DÜŞEYARA işlevinin nasıl çalıştığını düşünelim. Örneğin hangi arabanın 200 km/s hızla hareket ettiğini bulmaya çalışalım. Bu formülün sizin için zor olmayacağını düşünüyorum:

= DÜŞEYARA (200, $ A $ 2: $ B $ 15, 2, YANLIŞ)

Birkaç 200 değerimiz olmasına rağmen, yalnızca bir tane aldık, çünkü DÜŞEYARA işlevi tam olarak eşleşirse, sistem yalnızca belirtilen aralıkta bulunan ilk değeri kullanır.
Şimdi, yaklaşık bir değer eşleşmesi için DÜŞEYARA işlevinin nasıl çalıştığını test edelim. 260 km / s hızla giden bir araba arayalım. ilk yaptığın şey ne zaman "Aralık görünümü" eşittir "NS"- değer aralığınızı artan düzende ilk sütuna göre sıralıyorsunuz. DÜŞEYARA işlevi aşağıdakileri bulduğu için bu gerekli ve önemlidir. en yüksek değer itibaren verilen koşul ve ardından arama durur. Sıralama tavsiyesine uymazsanız, sonuç bir mesaj veya başka garip sonuçlar olacaktır.

Arama yapmak için aşağıdaki DÜŞEYARA kullanıyoruz:

= DÜŞEYARA (260; A $ 2: $ B $ 15; 2; DOĞRU)

Dolayısıyla, formülümüz 240 değerini döndürdü, ayrıca 270 değerine sahip olmamıza ve bu değer daha yakın gibi görünmesine rağmen, ancak DÜŞEYARA işlevinin çalışma özelliği, tam bir eşleşme kullanıldığında en çok arama yapmasıdır. büyük önem listede, ancak onu aşmıyor.
Yazıda incelediğim örneklerin DÜŞEYARA işlevinin nasıl çalıştığı, nasıl kullanıldığı ve ne için kullanılması gerektiği ile ilgili her şeyi anlamanıza yardımcı olacağını düşünüyorum.

Yine de, diğer makalelerde ele alacağımız çok daha gelişmiş örnekler var.

Ve hepsi benim için! Umarım yukarıdakilerin tümü sizin için açıktır. Geri kalan yorumlar için çok minnettar olurum, çünkü bu okunabilirliğin bir göstergesidir ve yeni makaleler yazmaya ilham verir! Okuduklarınızı arkadaşlarınızla paylaşın ve beğenin!

Excel editörü çok güçlü program tablolarla çalışmak için. Bazen büyük miktarda veriyle çalışmanız gerekebilir. Bu gibi durumlarda çeşitli bilgi alma araçları kullanılır. Excel'deki DÜŞEYARA işlevi, bu amaç için en popüler olanlardan biridir. Daha yakından bakalım.

Çoğu kullanıcı, DÜŞEYARA'nın Dikey Görünüm anlamına geldiğini bilmez. İngilizce'de işleve "DÜŞEYARA" denir, bu da "Dikey ARA" anlamına gelir.

fonksiyon nasıl kullanılır

Bu aracın nasıl çalıştığını anlamak için aşağıdaki adımları izlemeniz gerekir.

1. Bir tür bilgi alımı yapabileceğiniz bir tablo oluşturun.

1. Formülleri göstermek için kullanacağımız birkaç alan ekleyelim.

1. "Soyadı aranıyor" alanına tablodaki seçeneklerden istediğinizi giriniz.
2. Ardından bir sonraki hücreye gidip "Ekle işlevi" penceresini çağırıyoruz.
3. "Tam alfabetik liste" kategorisini seçiyoruz.
4. İhtiyacımız olan "DÜŞEYARA" işlevini buluyoruz. Devam etmek için "Tamam" düğmesine tıklayın.

1. Daha sonra "Fonksiyon Argümanlarını" belirlememiz istenecek:
   • "Arama ifadesi" alanında, gerekli soyadını yazdığımız hücrenin bağlantısını belirtin.
   • "Tablo" alanını doldurmak için tüm verilerimizi fare ile seçmeniz yeterlidir. Bağlantı otomatik olarak eklenecektir.
   • "Sütun numarası" sütununda 2 sayısını belirtiyoruz, çünkü bizim durumumuzda isim ikinci sütunda.
   • Son alan "0" veya "1" ("YANLIŞ" ve "DOĞRU") değerlerini alabilir. "0" belirtirseniz, düzenleyici belirtilen kriterlere göre tam bir eşleşme arayacaktır. "1" ise, arama sırasında tam eşleşmeler dikkate alınmayacaktır.
2. Kaydetmek için "Tamam" düğmesine tıklayın.

1. Sonuç olarak, "Tomara" adını aldık. Yani, her şey doğru.

Şimdi geri kalan alanlar için aynı formülü kullanmanız gerekiyor. Ctrl + C ve Ctrl + V kullanarak bir hücrenin basit bir şekilde kopyalanması, kullandığımız için çalışmayacaktır. göreceli bağlantılar ve her seferinde sütun numarası değişecektir.

Her şeyin doğru çalışması için aşağıdakileri yapmanız gerekir:

1. İlk işleve sahip hücreye tıklayın.
2. Formül giriş satırına gidin.
3. Metni Ctrl + C ile kopyalayın.

1. Sonraki alanı aktif hale getirin.
2. Formül giriş satırına tekrar gidin.
3. Ctrl + V kısayol tuşlarına basın.

Editörün fonksiyon argümanlarındaki referansları değiştirmemesinin tek yolu budur.

1. Ardından sütun numarasını istenen sayıya değiştiriyoruz. Bizim durumumuzda 3. Enter tuşuna basın.

1. Bu sayede "Doğum yılı" sütunundaki verilerin doğru bir şekilde belirlendiğini görebiliriz.

1. Bundan sonra, son alan için aynı işlemleri tekrarlıyoruz, ancak gerekli sütunun numarasını düzeltiyoruz.

Bu işlevin ilkesi, Excel'in belirttiğiniz alanda bir parça araması ve ardından bu dizeden herhangi bir değer döndürebilmesidir. sadece belirtmeniz yeterli seri numarası Belirtilen aralıktaki sütun.

Yani, numaralandırma, sayfanın başından değil, belirtilen hücre alanının başlangıcından başlar.

Verileri karşılaştırmak için DÜŞEYARA nasıl kullanılır?

Bu olasılığı iki tablo kullanarak göstereceğiz. İkinci sayfada çalışan verilerini güncellediğimizi düşünelim. Bizim mücadelemiz tam olarak neyin değiştiğini bulmak. Bunu yapmak için aşağıdakileri yapmanız gerekecektir.

1. Tam olarak aynı tabloya sahip ikinci bir sayfa ekleyelim (Ctrl + C ve Ctrl + V kısayol tuşları kullanılarak kopyalanır).
2. Stajyerleri Junior Çalışan konumuna terfi ettirin. Bu bilgi karşılaştırma için başlangıç ​​noktası olacaktır.

1. Eski tablomuza bir sütun daha ekleyelim.

1. Yeni sütunun ilk hücresine gidiyoruz ve oraya aşağıdaki formülü giriyoruz.

= DÜŞEYARA ($ B $ 3: $ B $ 11; Sayfa2! B $ 3: $ E $ 11; 4; YANLIŞ)

Anlamı:

• $ B $ 3: $ B $ 11 - arama için ilk sütunun tüm değerleri kullanılır (mutlak referanslar kullanılır);
• Sayfa2! - bu değerler belirtilen adla sayfada aranmalıdır;
• $ B $ 3: $ E $ 11 - aranacak tablo (hücre aralığı);
• 4 - belirtilen veri alanındaki sütun numarası;
• YANLIŞ - tam eşleşmeleri arayın.

1. Formülü belirttiğimiz yerde yeni bilgiler görüntülenecektir.
2. Sonuç aşağıdaki gibi olacaktır.

1. Şimdi bu formülü hücrelerin geri kalanında çoğaltın. Bunu yapmak için, fareyi orijinal hücrenin sağ alt köşesinde sürüklemeniz gerekir.

1. Sonuç olarak, tüm yeni pozisyonlar beklendiği gibi kopyalandığından, yazdığımız formülün doğru çalıştığını göreceğiz.

Artık hangi çalışanın kayıtlarının değiştiğini kolayca belirleyebiliyoruz.

Bu işlevin tek dezavantajı, DÜŞEYARA'nın birden çok koşulla çalışamamasıdır.

Bu gibi durumlarda, farklı kullanmanız gerekir. ek sütunlar birden çok sütundaki bilgileri birleştiren. Ve çirkin görünüyor ve çok uygun değil.

DÜŞEYARA işlevi ve açılır listeler

Bu iki aracı aynı anda kullanma örneklerine bakalım. Bunu yapmak için şu adımları izleyin.

1. Soyadın seçildiği hücreye gidin.
2. Veri sekmesine tıklayın.
3. Belirtilen araca tıklayın ve "Veri Doğrulama" seçeneğini seçin.

Koşullu biçimlendirme (5)
Listeler ve Aralıklar (5)
Makrolar (VBA prosedürleri) (63)
Çeşitli (39)
Excel hataları ve aksaklıkları (3)

Başka bir tabloda veya DÜŞEYARA gücünde bir değer nasıl bulunur?

Aslında bu yazıda sadece olasılıklardan değil, olasılıklardan da bahsetmek istiyorum. DÜŞEYARA işlevleri, ama ben de dokunmak istiyorum ARAMA, DÜŞEYARA ile çok yakından ilgili bir işlev olarak. Bu özelliklerin her birinin hem artıları hem de eksileri vardır. Özetle DÜŞEYARA, bir sütunda yer alan değerler kümesi içerisinde belirttiğimiz belirli bir değeri arar. Belki de çoğu zaman DÜŞEYARA ihtiyacı, verileri karşılaştırmanız, başka bir tabloda veri bulmanız, bazı kriterlere dayalı olarak bir tablodan diğerine veri eklemeniz vb. gerektiğinde ortaya çıkar.
İlkeyi biraz daha iyi anlamak için VPR'nin çalışması bir şeyle başlamak daha iyi pratik örnek... Şöyle bir tablo var:
şekil 1

ve ilk tablodan her soyadı için ikinci tarihte değiştirilmesi gerekir. Üç kayıt için bu bir sorun değil ve bunu elle yapmak - her şey açık. Ancak gerçek hayatta bunlar binlerce kayıt içeren tablolardır ve manuel ikame aramaları bir saatten fazla sürebilir. Ayrıca merhemde birkaç tane daha uçar: sadece tam adlar her iki tabloda da tamamen farklı bir sırayla düzenlenmiş değildir ve tablolardaki kayıtların sayısı farklıdır, bu nedenle tablolar da farklı sayfalarda / kitaplarda bulunur. El değiştirmenin bir seçenek olmadığına sizi ikna ettiğime inanıyorum. Fakat DÜŞEYARA (DÜŞEYARA) burada yeri doldurulamaz olacak. Bu durumda, pratik olarak hiçbir şey yapılması gerekmeyecek - sadece ikinci tablonun C sütununun ilk hücresine yazın. (ilk tablodaki tarihleri ​​​​değiştirmeniz gereken yer) bu formül:
= DÜŞEYARA ($ A2; Sayfa1! $ A $ 2: $ C $ 4; 3; 0)
Formülü doğrudan hücreye yazabilir veya kategoride seçerek fonksiyon yöneticisini kullanarak yazabilirsiniz. Referanslar ve diziler DÜŞEYARA ve gerekli kriterleri ayrı ayrı belirterek. Şimdi kopyala ( Ctrl+C) formülü içeren hücre, verilerin sonuna kadar C sütunundaki tüm hücreleri seçin ve ( Ctrl+V).

İlk olarak, temel çalışma prensibi: DÜŞEYARA, bağımsız değişken tarafından belirtilen değeri Tablo bağımsız değişkeninin ilk sütununda arar. aranan_değer ... Gerekli değer bulunduğunda, işlev, bulunan değerin karşısındaki değeri, ancak bağımsız değişken tarafından belirtilen sütundan döndürür. Column_number ... Biraz sonra aralıklı görüntüleme ile ilgileneceğiz. DÜŞEYARA yalnızca bir değer döndürebilir - ölçütle eşleşen ilk değer. İstenen değer bulunamazsa (tabloda değil), o zaman fonksiyonun sonucu # Yok ... Bundan korkmayın - hatta yararlıdır. Hangi kayıtların eksik olduğunu tam olarak bileceksiniz ve böylece iki tabloyu birbiriyle karşılaştırabilirsiniz. Bazen şunu görürsünüz: veriler her iki tabloda da bulunur, ancak DÜŞEYARA # N / A verir. Bu, tablolarınızdaki verilerin aynı olmadığı anlamına gelir. Bazılarında fazladan göze çarpmayan boşluklar vardır (genellikle değerden önce veya sonra) veya Kiril karakterleri Latin karakterleriyle karıştırılır. Aynı şekilde # Yok Kriterler sayı ise ve istenilen tabloya metin olarak yazılırsa olacaktır. (genellikle solda üst köşe böyle bir hücre yeşil bir üçgen görünür), ve finalde - sayılar olarak. Ya da tam tersi.

DÜŞEYARA bağımsız değişkenlerinin açıklaması
$ A2 - bağımsız değişken aranan_değer(hadi diyelim kriter kısa olmak). Aradığımız şey bu. Onlar. ikinci tablonun ilk kaydı için S.A. Petrov olacak. Burada ya doğrudan kriterin metnini belirtebilirsiniz (bu durumda, tırnak içinde olmalıdır - = DÜŞEYARA ("Petrov SA"; Sayfa1! $ A $ 2: $ C $ 4; 3; 0) veya bir bağlantı bu metni içeren bir hücreye (örnek fonksiyonda olduğu gibi)... Küçük bir nüans var: joker karakterler de kullanabilirsiniz: "*" ve "?". Bu, yalnızca bir dizenin bir kısmından değerler bulmanız gerekiyorsa çok kullanışlıdır. Örneğin, "Petrov S.A" tam olarak giremezsiniz, ancak yalnızca soyadı ve yıldız işareti - "Petrov *" girebilirsiniz. Ardından "Petrov" ile başlayan herhangi bir kayıt görüntülenecektir. "Petrov" soyadının satırın herhangi bir yerinde bulunduğu bir kayıt bulmanız gerekiyorsa, bunu şu şekilde belirtebilirsiniz: "* Petrov *". Petrov soyadını bulmak istiyorsanız ve adın ve soyadı ne olursa olsun (tam ad Ivanov II şeklinde yazılmışsa), o zaman işte sadece şu: "Ivanov?.?" ... Genellikle her satırın kendi değerini belirtmesi gerekir (A sütununda Soyadları ve hepsini bulmanız gerekir). Bu durumda, A sütunundaki hücrelere yapılan referanslar her zaman belirtilir, örneğin, A1 hücresinde şöyle yazılır: Ivanov. Ivanov'un başka bir tabloda olduğu da biliniyor, ancak soyadından sonra hem adı hem de soyadı (veya başka bir şey) yazılabilir. Ancak sadece soyadıyla başlayan satırı bulmamız gerekiyor. O zaman yazmak gerekir Aşağıdaki şekilde: A1 & "*". Bu girdi "Ivanov *" ile eşdeğer olacaktır. A1, Ivanov'un ve işaretinin (&) iki metin değerini tek bir dizede birleştirmek için kullanıldığını söylüyor. Tırnak içinde yıldız işareti (formül içindeki metnin olması gerektiği gibi). Böylece şunu elde ederiz:
A1 & "*" =>
"İvanov" & "*" =>
"İvanov *"
Çok sayıda arama değeri varsa çok uygundur.
Dizede en azından bir yerde bir kelime olup olmadığını belirlemeniz gerekiyorsa, her iki tarafa da yıldız işareti koyun: "*" & A1 & "*"

Sayfa1! $ A $ 2: $ C $ 4 - argüman tablo... Bir hücre aralığını belirtir. Yalnızca aralık, ilk veri hücresinden en son hücreye kadar olan verileri içermelidir. Örnekte belirtilen aralık olmak zorunda değildir. 100 satır varsa, Sayfa1! $ A $ 2: $ C $ 100. Üç şeyi hatırlamak önemlidir: Birincisi, tablo her zaman aradığınız sütunla başlamalıdır kriter ... Ve başka bir şey yok. Aksi takdirde hiçbir şey bulunamaz veya sonuç beklediğiniz gibi olmaz. İkincisi: argüman tablo "sabitlenmiş" olmalı ... Bunun anlamı ne. Dolar işaretlerine bakın - $? Bu demirleme (daha doğrusu buna denir mutlak referans aralık başına)... Nasıl yapılır. Bağlantı metnini vurgula (yalnızca bir aralık - bir ölçüt) ve bas F4 hem sütun adının hem de satır numarasının önünde dolar görünene kadar. Bu yapılmazsa, formülü kopyalarken Tablo argümanı "dışarı çıkar" ve sonuç yine yanlış olur. Ve sonuncusu - tablo, ilkinden (aradığımız) sonuncuya (değerleri döndürmemiz gereken) sütunlar içermelidir. örnekte Sayfa1! $ A $ 2: $ C $ 4- bu, D (4) sütunundan değer döndürmenin mümkün olmayacağı anlamına gelir, çünkü tabloda sadece üç sütun var.

3 - Column_number... Burada sadece argümandaki sütun numarasını belirtiyoruz tablo, sonuç olarak değiştirmemiz gereken değerler. Örnekte, bu Kabul Tarihidir - yani. sütun numarası 3. Bir departmana ihtiyaç duyulsaydı, o zaman 2'yi gösterirdik ve sadece bir tablonun soyadlarının diğerinde olup olmadığını karşılaştırmamız gerekirse, o zaman 1'i de gösterebilirdik. Önemli: argüman. Column_number bağımsız değişkendeki sütun sayısını geçmemelidir tablo ... Aksi takdirde, formülün sonucu bir hata olacaktır. #BAĞLANTI!... Örneğin, $ B $ 2: $ C $ 4 aralığı belirtilmişse ve C sütunundan veri döndürmeniz gerekiyorsa, 2 doğrudur. argüman tablo($ B $ 2: $ C $ 4) sadece iki sütun içerir - B ve C. Sütun 3'ün sayısını (sayfadaki sayı nedir) belirlemeye çalışırsak, o zaman bir hata alırız. #BAĞLANTI! dan beri belirtilen aralıkta yalnızca üçüncü bir sütun yoktur.

Pratik ipucu: Tablo argümanında çok fazla sütun varsa ve son sütundan sonucu döndürmeniz gerekiyorsa, bunların sayısını hesaplamanız hiç gerekli değildir. Bunu şu şekilde belirtebilirsiniz: = DÜŞEYARA ($ A2; Sayfa1! $ A $ 2: $ C $ 4; SAYICOLUMN (Sayfa1! $ A $ 2: $ C $ 4); 0). bu arada bu durum Sayfa1! ayrıca gereksiz olarak kaldırılabilir: = DÜŞEYARA ($ A2; Sayfa1! $ A $ 2: $ C $ 4; NUMBERCOLUMN ($ A $ 2: $ C $ 4); 0).

0 - Aralık_görünümüçok ilginç bir argümandır. DOĞRU veya YANLIŞ olabilir. Soru hemen ortaya çıkıyor: formülümde neden 0 var? Her şey çok basit - Formüllerdeki Excel, 0'ı YANLIŞ ve 1'i DOĞRU olarak algılayabilir. DÜŞEYARA'da belirtirseniz bu parametre 0 veya YANLIŞ'a eşitse, belirtilen Ölçütle tam eşleşme aranacaktır. Bunun joker karakterlerle ("*" ve "?") ilgisi yoktur. 1 veya DOĞRU kullanırsanız (veya varsayılan olarak DOĞRU olduğundan son argümanı hiç belirtmezseniz), o zaman ... Bu çok uzun bir hikaye. Kısacası - DÜŞEYARA, eşleşen en benzer değeri arayacaktır. kriter ... Bazen çok yardımcı olur. Ancak, bu parametreyi kullanırsanız, Tablo bağımsız değişkenindeki liste artan düzende sıralanmalıdır. Sıralamanın yalnızca Interval_View argümanı DOĞRU veya 1 ise gerekli olduğuna dikkatinizi çekerim. 0 veya YANLIŞ ise sıralama gerekli değildir.

Birçoğu muhtemelen resimde tam ad için bölümleri karıştırdığımı fark etti. Bu bir kayıt hatası değildir. Makaleye eklenen örnek, bağımsız değişkeni manuel olarak değiştirmeden hem bunları hem de tarihleri ​​tek bir formülle nasıl değiştirebileceğinizi gösterir. Column_number... Bana böyle bir örnek işe yarayabilir gibi geldi.

DÜŞEYARA'da # N / A (# N / A) hatası nasıl önlenir?
Henüz sık sorun- çoğu eşleşme bulunamazsa # N / A sonucunu görmek istemez. Etrafta dolaşmak kolaydır:
= EĞER (UND (DÜŞEYARA ($ A2; Sayfa1! $ A $ 2: $ C $ 4; 3; 0)); ""; DÜŞEYARA ($ A2; Sayfa1! $ A $ 2: $ C $ 4; 3; 0)))
Şimdi DÜŞEYARA bir eşleşme bulamazsa, hücre boş olacaktır.
Ve Excel 2007 ve sonraki sürümlerinin kullanıcıları EĞERHATA kullanabilir:
= EĞERHATA (DÜŞEYARA ($ A2; Sayfa1! $ A $ 2: $ C $ 4; 3,0); "")

vaat edilen ARAMA

Bu işlev, parametre tarafından belirtilen değeri arar. aranan_değer tartışmada Görünüm_dizisi... Ve fonksiyonun sonucu, bulunan değerin konum numarasıdır. görüntülenebilir_dizi... Değerin kendisi değil, konum numarasıdır. Prensip olarak, aynı ayrıntıda açıklamayacağım, çünkü ana noktalar tamamen aynı. Yukarıdaki tabloya uygulamak isteseydik şöyle olurdu:
= ARAMA ($ A2; Sayfa1! $ A $ 2: $ A $ 4; 0)
$ A2 - Aranan_değer. Burada her şey DÜŞEYARA ile tamamen aynı. Joker karakterlere de izin verilir ve tamamen aynı tasarımdadır.

Sayfa1! $ A $ 2: $ A $ 4 - Bakılacak dizi. DÜŞEYARA'dan temel farkı, yalnızca bir sütunlu bir dizi belirtebilmenizdir. Bu, arayacağımız sütun olmalıdır. aranan_değer ... Birden fazla sütun belirtmeye çalışırsanız, işlev bir hata döndürür.

Match_type (0) - DÜŞEYARA ile aynı Aralık_görünümü ... Aynı özelliklerle. Yalnızca istenen veya en büyüğünden en küçüğünü arama yeteneğinde farklılık gösterir. Ancak bu yazıda bunun üzerinde durmayacağım.

Ana sıralanmış. Ancak konum numarasını değil, değerin kendisini döndürmemiz gerekiyor. MAÇ anlamına gelir saf formu bize uymuyor. En azından kendi başına. Ancak INDEX işleviyle birlikte kullanırsanız, ihtiyacımız olan şey bu ve daha da fazlası.
= İNDEKS (Sayfa1! $ A $ 2: $C $ 4; ARAMA ($ A2; Sayfa1! $A $ 2: $A $ 4; 0); 2)
Bu formül, DÜŞEYARA ile aynı sonucu döndürür.

INDEX işlevi bağımsız değişkenleri
Sayfa1! $ A $ 2: $ C $ 4 - Dizi. Bu argüman olarak, değerleri almak istediğimiz aralığı belirtiyoruz. Bir veya birkaç sütun olabilir. Yalnızca bir sütun varsa, işlevin son argümanının belirtilmesine gerek yoktur. Bu arada - bu argüman, KAÇINCI işlevinin Look-up_array argümanında belirttiğimiz argümanla hiç örtüşmeyebilir.

Sırada RowNumber ve ColumnNumber var. Dizideki konum numarasını bize döndüren SEARCH yerine Line_Number için koyarız. Her şey bunun üzerine inşa edilmiştir. INDEX, Array'in belirtilen satırında (Row_num) ve birden fazla sütun varsa belirtilen sütunda (Column_num) bulunan Array değerini döndürür. Bu pakette, INDEX işlevinin Dizi bağımsız değişkenindeki satır sayısı ile KAÇINCI işlevinin Arama_dizi bağımsız değişkenindeki satır sayısının eşleşmesi gerektiğini bilmek önemlidir. Ve aynı çizgiden başlayın. Başka hedefler peşinde koşmazsanız, bu normal durumlarda geçerlidir.
DÜŞEYARA durumunda olduğu gibi, gerekli değer bulunamazsa INDEX # N / A döndürür. Ve bu tür hataları aynı şekilde çözebilirsiniz:
Excel'in tüm sürümleri için (2003 ve önceki sürümler dahil):
= IF (UND (SEARCH ($ A2; Sayfa1! $ A $ 2: $ A $ 4; 0)); ""; INDEX (Sayfa1! $ A $ 2: $ C $ 4; ARAMA ($ A2; Sayfa1! $ A $ 2: $ A $ 4; 0); 2))
2007 ve üzeri sürümler için:
= EĞERHATA (INDEX (Sayfa1! $ A $ 2: $ C $ 4; ARAMA ($ A2; Sayfa1! $ A $ 2: $A $ 4; 0); 2); "")

255 karakterden uzun kriterlerle çalışma
INDEX-POISKPOS ayrıca VLOOKUP'a göre bir avantaja daha sahiptir. Mesele şu ki, DÜŞEYARA değerleri arayamaz satır uzunluğu 255'ten fazla karakter içeren... Bu nadiren olur, ama olur. Elbette DÜŞEYARA'yı aldatabilir ve kriteri kesebilirsiniz:
= DÜŞEYARA (ORTA ($ A2; 1; 255); MID (Sayfa1! $ A $ 2: $ C $ 4; 1; 255); 3; 0)
ama bu bir dizi formülüdür. Ayrıca, böyle bir formül her zaman istenen sonucu döndürmez. İlk 255 karakter tablodaki ilk 255 karakterle aynıysa ve ardından karakterler farklıysa, formül bunu artık görmez. Ve formül yalnızca döner metin değerleri, sayıların döndürülmesi gereken durumlarda çok uygun değildir.

Bu nedenle, zor bir formül kullanmak daha iyidir:
= İNDEKS (Sayfa1! $ A $ 2: $C $ 4; SUMPRODUCT (ARAMA (DOĞRU; Sayfa1! $A $ 2: $A $ 4 = $A2; 0)); 2)
Burada okunabilirlik için formüllerde aynı aralıkları kullandım, ancak indirme örneğinde burada gösterilenlerden farklılar.
Formülün kendisi, SUMPRODUCT işlevinin içindeki bazı işlevleri büyük hesaplamalara dönüştürme yeteneği üzerine kurulmuştur. Bu durumda MATCH, ölçütün dizgedeki değere eşit olduğu dizgenin konumunu arar. Artık burada joker karakterleri kullanamayacaksınız.

Makaleye eklenen örnekte, açıklanan tüm durumların kullanımına ilişkin örnekler ve INDEX ve MATCH'ın bazen DÜŞEYARA'ya neden tercih edildiğinin bir örneğini bulacaksınız.

Örneği indir

(26.0 KiB, 13.776 indirme)

Makale yardımcı oldu mu? Bağlantıyı arkadaşlarınızla paylaşın! video dersleri

("Alt çubuk" :( "textstyle": "static", "textpositionstatic": "alt", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slayt "," texteffecteasing ":"easyOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "sağ", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "sağ", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500 textcss " :" görüntü: blok; dolgu: 12 piksel; metin hizalama: sol; "," textbgcss ":" görüntü: blok; konum: mutlak; üst: 0 piksel; sol: 0 piksel; genişlik: %100; yükseklik: %100 ; arka plan -renk: # 333333; opaklık: 0,6; filtre: a lpha (opaklık = 60); "," titlecss ":" ekran: blok; pozisyon: göreceli; yazı tipi: kalın 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; renk: #fff; "," açıklamacss ":" ekran: blok; pozisyon: göreceli; yazı tipi: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; renk: #fff; üst kenar boşluğu: 8px; "," buttoncss ":" ekran: blok; pozisyon: göreceli; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: yok! önemli; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40))

Microsoft Excel - harika araç verilerle çalışmak için. Olasılıklar çok büyük, potansiyel aslında çok az çalışılıyor, çünkü komut yazma kuralları profesyoneller için bile zorluklara neden oluyor. Ama çok çalışırsan, burada inanılmaz şeyler yapabileceğin ortaya çıkıyor. İşlev Excel DÜŞEYARA- veri işleme araçlarından biri, şimdi bunun hakkında konuşacağız.

Excel'de DÜŞEYARA nedir - kapsam

Birden çok tabloyu işlerken, genellikle şurada bulunur: farklı kitaplar, değerlerin anlamlarını kaybetmemesine ve sürecin otomatikleşmesine dikkat ederek verileri birinden diğerine aktarmak gerekli hale gelir. Excel'deki mekanizma basit ve hızlı çalışmalıdır.

Örneğin bir şirketimiz var. Ivan Ivanovich orada çalışıyor. Bir sayfada maaşının değeri, diğerinde muhasebe departmanının para cezası için maaşından kestiği para miktarı saklanır. Tüm değerleri tek bir belgede birleştirmek gerekir. Başka bir örnek, iki tablo vardır: fiyatlar ve depo. Birinde, mendillerin maliyeti, diğerinde miktarları belirtilir. Miktar ve fiyatı tek bir yerde buluşturmak gerekiyor.

İşletmenin sadece iki veya üç çalışanının olduğu veya bir düzine kadar malın olduğu durumlarda, her şeyi manuel olarak yapabilirsiniz. Gerekli özenle, bir kişi hatasız çalışacaktır. Ama işlenecek değerler varsa örneğin bin, iş otomasyonu gerekir. Bunun için Excel'de bir DÜŞEYARA (DÜŞEYARA) vardır.

Netlik için örnekler: Tablo 1, 2'de - ilk veriler, tablo 3 - ne elde edilmelidir.

İlk veri tablosu 1

Birleşik veri tablosu 3

AD SOYAD.	Z.P.	İyi
İvanov	20 000 ₽	38 000 ₽
Petrov	19 000 ₽	12 000 ₽
Sidorov	21 000 ₽	200 ₽

Excel'de DÜŞEYARA işlevi - nasıl kullanılır

Tablo 1'in son halini alması için içine sütun başlığını giriyoruz, örneğin "Ceza". Aslında bu isteğe bağlıdır, herhangi bir metin yazabilir veya boş bırakabilirsiniz. İşlev, başka bir tabloda bulunan değerin görünmesi gereken alanda fareye tıklayarak da çalışacaktır.

Şimdi fonksiyonu çağırmamız gerekiyor. Bu farklı şekillerde yapılabilir:

DÜŞEYARA işlevi için değerlerin doldurulması gereklidir.

Sonuç açıktır - tablo 3'te (yukarıya bakın).

DÜŞEYARA - iki koşulla çalışma talimatları

Arama kriterlerini bir koşulla değil, aynı anda birkaç - iki sütunla belirtmenin gerekli olduğu durumlarda, DÜŞEYARA işlevi de iş için kullanılabilir. Bunun için ilk veriler kesinleştirilmelidir.

Örnek, tablo 4'teki fiyatı tablo 5'ten eklemek gereklidir.

Telefon özellikleri tablosu 4

Örnek telefonlarda seçilmiştir, ancak verilerin kesinlikle herhangi biri olabileceği açıktır. Tablolardan da görebileceğiniz gibi telefonların markaları farklı değil ancak RAM ve Kamera farklı. Özet veri oluşturmak için telefonları marka ve RAM'e göre seçmemiz gerekiyor. DÜŞEYARA işlevinin birkaç koşulda çalışması için sütunları koşullarla birleştirmeniz gerekir.

En soldaki sütunu ekleyin. Örneğin, buna "Birlik" diyelim. Değerlerin ilk hücresinde B 2 var, "= B 2 & C 2" yapısını yazıyoruz. Mouse ile çoğaltıyoruz. Tablo 6'daki gibi çıkıyor.

Telefon özellikleri tablosu 6

birlik	İsim	Veri deposu	Fiyat
ZTE 0,5	ZTE	0,5	1 990 ₽
ZTE1	ZTE	1	3 099 ₽
DNS1	DNS	1	3 100 ₽
DNS 0,5	DNS	0,5	2 240 ₽
Alcatel 1	Alcatel	1	4 500 ₽
Alcatel 256	Alcatel	256	450 ₽

Tablo 5'i de aynı şekilde işliyoruz. Sonra bir koşulu aramak için DÜŞEYARA işlevini kullanırız. Koşul, birleştirilmiş sütunlardan alınan verilerdir. DÜŞEYARA işlevinde verilerin geldiği sütunun numarasının değişeceğini unutmayın. Fonksiyonu uyguladıktan sonra iki koşula göre bir seçim alacaksınız. Sütunları bitişik sütunlar yerine telefon markası ve kamera ile birleştirebilirsiniz.

Nasıl kullanılacağını video eğitimini izleyin DÜŞEYARA işlevi aptallar için Excel'de:

Excel'deki DÜŞEYARA işlevinin kullanımı kolaydır ve veri işleme olanakları çok büyüktür.

Excel'deki DÜŞEYARA işlevi, bir tablodaki verilerin ikinci tablonun ilgili hücrelerine yeniden düzenlenmesini sağlar. İngilizce adı DÜŞEYARA'dır.

Çok kullanışlı ve sık kullanılan. Çünkü aralıkları on binlerce adla manuel olarak eşleştirmek sorunludur.

Excel'de DÜŞEYARA işlevi nasıl kullanılır?

Diyelim ki konteyner ve ambalaj üretimi için bir işletmenin deposuna belirli miktarda malzeme geldi.

Malzemelerin maliyeti fiyat listesindedir. Bu ayrı bir tablo.

Depoda alınan malzemelerin maliyetini bulmanız gerekir. Bunu yapmak için, ikinci tablodaki fiyatı birincisine değiştirmeniz gerekir. Ve sıradan çarpma yoluyla aradığımız şeyi bulacağız.

Eylemlerin algoritması:

Tamam'ı tıklayın. Ve sonra işlevi tüm sütun üzerinde "çarpıyoruz": fare ile sağ alt köşeyi tutup aşağı sürükleriz. İstenilen sonucu alıyoruz.

Artık malzemelerin maliyetini bulmak zor olmayacak: miktar * fiyat.

DÜŞEYARA iki tabloyu birbirine bağladı. Fiyat listesi değişirse, depoya alınan (bugün alınan) malzemelerin maliyeti de değişecektir. Bunu önlemek için "Özel Yapıştır" seçeneğini kullanın.

1. Fiyatların eklendiği sütunu seçin.
2. Sağ fare düğmesi - "Kopyala".
3. Seçimi kaldırmadan, sağ düğme fare - "Özel Yapıştır".
4. "Değerler"in yanındaki kutuyu işaretleyin. TAMAM.

Hücrelerdeki formül kaybolacaktır. Sadece değerler kalacaktır.



DÜŞEYARA kullanarak iki tablonun hızlı karşılaştırması

İşlev, büyük tablolardaki değerleri eşleştirmeye yardımcı olur. Diyelim ki fiyat değişti. Eski fiyatları yeni fiyatlar ile karşılaştırmamız gerekiyor.

Bu şekilde sunulan veriler karşılaştırılabilir. Sayısal ve yüzde farkını bulun.

Excel'de birden çok koşulla DÜŞEYARA işlevi

Şimdiye kadar analiz için yalnızca bir koşul sunduk - malzemenin adı. Ancak pratikte, genellikle birkaç aralığı verilerle karşılaştırmak ve 2., 3. vb. için bir değer seçmek gerekir. kriterler.

Örnek tablo:

Oluklu mukavvanın OJSC Vostok'tan getirildiği fiyatı bulmamız gerektiğini varsayalım. Malzeme adına ve tedarikçiye göre arama yapmak için iki koşul belirlemeniz gerekir.

Birkaç öğenin tek bir tedarikçiden gelmesi, meseleyi karmaşıklaştırıyor.

Formülü ayrıntılı olarak ele alalım:

1. Ne arıyoruz.
2. Nereye bakıyoruz.
3. Hangi verileri alıyoruz.

DÜŞEYARA işlevi ve açılır liste

Diyelim ki bir açılır liste şeklinde yapılmış bazı verilerimiz var. Örneğimizde - "Malzemeler". Bir isim seçtiğinizde fiyat görünecek şekilde işlevi yapılandırmak gerekir.

İlk önce, bir açılır liste yapalım:

Şimdi, belirli bir malzemeyi seçtiğinizde, fiyat sütununda karşılık gelen rakamın göründüğünden emin olmanız gerekir. İmleci E9 hücresine koyarız (fiyatın görünmesi gereken yer).

1. "İşlev Sihirbazı"nı açın ve DÜŞEYARA'yı seçin.
2. İlk argüman - "Arama değeri" - açılır listeli bir hücre. Tablo - malzeme ve fiyatların adlarını içeren bir aralık. Sütun, sırasıyla, 2. Fonksiyon aşağıdaki formu almıştır: .
3. ENTER'a basıyoruz ve sonucun tadını çıkarıyoruz.

Malzemeyi değiştiriyoruz - fiyat değişiyor:

Excel'de DÜŞEYARA işleviyle bir açılır liste bu şekilde çalışır. Her şey otomatik olarak gerçekleşir. Saniyeler içinde. Her şey hızlı ve verimli çalışır. Sadece bu fonksiyonla ilgilenmeniz gerekiyor.