Birçok insan bunu bilir ve sıklıkla kullanır. Ancak aynı zamanda iki önemli dezavantajı vardır, örneğin "Sol DÜŞEYARA" nasıl yapılır. DÜŞEYARA'nın kendisini yalnızca hızlı bir şekilde bir şeyler yapmam gerektiğinde kullanırım. "Normal" kullanım için dosyalarda, INDEX ve SEARCH yapılarını yapıyorum. Neden daha iyi?

1. Bir veri tablosuna sütun eklerken, formülün kendisindeki sütun numarasını değiştirmeniz gerekmez (DÜŞEYARA'da olduğu gibi). Sütun otomatik olarak hareket edecek
2. DÜŞEYARA'yı tam tersi şekilde yapabilirsiniz, yani. tablodan sağdan sola doğru bir seçim yapın. DÜŞEYARA'da ilk sütun her zaman aranabilir olmalıdır.

Tüm bunlar nasıl yapılır, aşağıda okuyun :)

dan bir örnek kullanacağım. Bu yapının DÜŞEYARA karşı uygulamasını daha iyi anlamak için:

Formülü ilk gördüğünüzde doldurmak ne kadar kolay?

İlk olarak, ilk tablonun nerede olduğuna ve verilerin nereden alınacağına karar verin! Adım adım ne doldurulacağı.

1. Formülü içine yerleştirin istenilen alan tablolar
2. $ G: $ G yerine, değerin bulunması gereken hücreleri koyun ve buna göre sonuçta görünmelidir. Soyadı arıyoruz, bu nedenle ilk tabloda soyadı sütununu arayın.
3. Hücreye hangi değerlerin döndürülmesi gerektiğine bağlı olarak $ J: $ J ile değiştirin. Araca bağlı olarak Soyadlara ihtiyacımız var - değerlerin etrafında görünmesi gereken araçları yerleştiriyoruz.
4. $ H: $ H yerine, karşılık gelen değeri bulmanız gereken sütunu doldurun. Onlar. Soyadı araca göre arıyoruz, bu da orijinal tabloya araçla birlikte bir sütun eklediğimiz anlamına geliyor.

Formüldeki $ işaretlerinin neden okunduğunu merak ediyorsanız

İNDEKS ve ARAMA. Bu fonksiyonlar nelerdir?

INDEX ve SEARCH, diğerleriyle birlikte mükemmel sonuçlar veren çok güçlü işlevlerdir.

INDEX (dizi; satır_sayısı; sütun_sayısı)

Belirtilen satır ve sütunun kesişimindeki değeri döndürür belirli bir aralık... Onlar. başlangıçta iki boyutlu dizilerle çalışır.

J1: K4 veri dizisini seçip satır ve sütun numaralarını ikiye ayarlayarak karşılık gelen değeri elde ettik.

Orijinal formüle bakıldığında

İNDEKS ($ G: $ G; ARAMA ($ J: $ J; $ H: $ H; 0); 1)

İkinci argüman (satır numarası) yerine SEARCH formülümüz olduğunu göreceğiz. Burada ne yapıyor?

SEARCH, değere göre bir aramadır. İşlev, bir satırda veya sütunda verilen bir değeri arar ve onu döndürür. seri numarası(aralığın başından itibaren). Onlar. INDEX fonksiyonunun ikinci argümanında ihtiyacımız olan araç numarasını buluyoruz, örneğin 2 gibi numarasını alıyoruz.

Ve zaten tek boyutlu $ G: $ G dizisinde, satır numarası = 2 olan hücreyi buluyoruz. Bu, J sütunundaki her hücre için işe yarayacaktır.

Bu çok karmaşık bir tasarım değil gibi görünüyor, ancak yukarıda yazdığım gibi çok etkili. Çünkü DÜŞEYARA'da olduğu gibi istediğiniz değerin sayısını sürekli değiştirmek zorunda değilsiniz ve hem sağa hem de sola arama yapabilirsiniz :)

Hala sorularınız varsa yorum yazın.

Her zamanki gibi!

Makalemizi sosyal ağlarınızda paylaşın:

İşlev DÜŞEYARA (DÜŞEYARA) Excel'de bir tablodaki verileri bulmak ve başka bir tablodaki verilerle karşılaştırmak için kullanılır.

İndirmek için aşağıdaki butona tıklayın Excel dosyası işlevin nasıl çalıştığına dair örneklerle DÜŞEYARA :

Video eğitimi: Excel'de DÜŞEYARA işlevi nasıl kullanılır?

Aptallar için Excel'de DÜŞEYARA işlevi

Aşağıdaki örnekte, sağdaki tabloda, öğrenci Sidorov'un matematik sınavının sonuçlarını değiştirmek istiyoruz. Soldaki tablodan tüm öğrencilerin verilerini biliyoruz. Formülü kullanarak ihtiyacımız olan verileri değiştirebiliriz.

Sözdizimi

= DÜŞEYARA (arama_değeri, tablo_dizisi, sütun_index_num,) - İngilizce versiyon

= DÜŞEYARA (arama_değeri, tablo, sütun_sayısı, [aralık_görünüm])- Rus versiyonu

fonksiyon argümanları

• aranan_değer (arama_değeri) Tablonun en sol sütunundan aranacak değerdir. Bir değer, bir hücre referansı veya Metin dizesi... Öğrenci örneğinde bunlar soyadlarıdır;
• tablo_dizisi (tablo) Aranacak veri aralığıdır. Bir hücre aralığına veya adlandırılmış bir aralığa referans olabilir. Öğrenci tablosu örneğinde, bu, öğrencilerin notlarını ve adlarını içeren tablonun tamamı olacaktır;
• col_index (sütun_sayısı)- bu, istenen değerin elde edileceği veri aralığındaki sütunun sıra sayısıdır;
• ([aralık_görünüm])- Bu argüman, aramadaki veri eşleşmesinin kesinliğini gösterir. Tam ise “0”, yaklaşık bir eşleşme ise “1” girin.

ek bilgi

• gerekli verilerin eşleşmesi tam ve yaklaşık olabilir;
• Yaklaşık veri doğruluğu için eşleştirirken, tablolardaki verilerin azalan düzende (yüksekten düşüğe) sıralandığından emin olun. Aksi takdirde maçın sonucu yanlış olacaktır;
• yaklaşık doğruluk için verileri karşılaştırırken:
  - fonksiyon istenen değeri bulamazsa, arama değerinden daha küçük olacak en büyük değeri döndürür;
  - fonksiyon eşleştirme sırasında # N / A hatası veriyorsa, istenen değer, istenen aralıktaki en küçük değerden küçüktür;
  - İstediğiniz değerler için joker karakterler kullanabilirsiniz.

Excel'de DÜŞEYARA kullanma örnekleri

Örnek 1. Bir öğrenci için sınav sonucu aranıyor

Aşağıdaki örnekte, öğrencilerin listesi tablonun sol sütununda gösterilmektedir. Tablonun sağ tarafında bu öğrencilerin final sınav puanları gösterilmektedir.

Öğrenci Petrov'un Fizikte hangi notu aldığını bulma göreviyle karşı karşıyayız.

Aşağıdaki formülü kullanarak bulabiliriz:

= DÜŞEYARA (“Petrov”, $ A $ 3: $ E $ 10,3,0)- İngilizce versiyon

= DÜŞEYARA (“Petrov”; $ A $ 3: $ E $ 10; 3; 0) - Rus versiyonu

Yukarıdaki formülün dört argümanı vardır:

• "Petrov"- soyadı, aradığımız veriler;
• $ A $ 3: $ E $ 10- sınav sonuçları ile veri aralığı;
• “3” - Fizikte değerlendirmenin değeri ile veri aralığı sütununun sıra numarası;
• “0” - istenen değerin tam eşleşmesi.

Aşağıda, fonksiyonumuzun nasıl çalıştığı örnek olarak açıkça gösterilmiştir.

İlk olarak, "Petrov" adı için veri aralığının en soldaki sütununa bakar. Arama yukarıdan aşağıya doğru gerçekleşir.

Excel, veri aralığında "Petrov" ile tam bir eşleşme bulur bulmaz, sistem, istenen verileri görüntülemek için sağa, üçüncü sütuna "adım atar".

Bu yaklaşımı kullanarak, her öğrenci ve konu için herhangi bir veri alabilirsiniz.

Örneğin, Kimya'da öğrenci Peskov sınavının sonuçlarını bulmak için formüle ihtiyacımız var:

= DÜŞEYARA (“Kumlar”, 20 $ Bir $: $ 28.4.0) - İngilizce versiyon

= DÜŞEYARA (“Kumlar”; $ A $ 20: $ E $ 28; 4; 0)- Rus versiyonu

Yukarıdaki örnekte öğrencinin soyadı ikili alıntı... Ayrıca, istediğiniz değere sahip bir hücre referansı kullanabilirsiniz. Bir hücre başvurusu, formülü dinamik hale getirir.

Örneğin, soyadı olan bir hücreniz varsa ve bir matematik notu arıyorsanız, öğrencinin adı değiştiğinde sonuç otomatik olarak güncellenir (aşağıda gösterildiği gibi):

Bir öğrencinin soyadı, veri aralığında olmayan bir arama argümanı olarak girilirse, fonksiyon bir hata üretecektir.

Örnek 2. İki faktörlü veri arama

Yukarıdaki örnekte, "column_number" özniteliği olarak veri sütununun sıra numarasını manuel olarak belirledik.

Peki ya aramayı dinamik hale getirmek istiyorsak? Öğrencinin adını ve konu adını girmek istersek ve işlev otomatik olarak gerekli verileri çıkarsa ne olur? Bunu yapmak için iki faktörlü bir arama kullanmamız gerekiyor.

Aşağıda iki faktörlü arama örneği verilmiştir:

İki faktörlü bir arama formülü yapmak için sütunlardan veri seçimini dinamik hale getirmek önemlidir. Bu nedenle, kullanıcı konunun adını değiştirdiğinde: Fizik, Matematik, vb., formülde veri sütununun karşılık gelen özelliği değişir.

Bunu yapmak için, sütun sıralamasından sorumlu bir argüman olarak bir fonksiyona ihtiyacımız var.

İki faktörlü arama formülü şöyle görünür:

- İngilizce versiyon

- Rus versiyonu

Yukarıdaki formül, veri sütununun sıra sayısından sorumlu olan bir işlevi kullanır. öznenin adını arama değeri olarak alır (“H4” hücresinde) ve konumunu “A2: E2” hücre aralığında döndürür.

"Matematik" belirtirseniz, "Matematik", "B2" hücresinde (bu dizideki ikinci) olduğundan Excel "2" döndürür.

Örnek 3. İki faktörlü arama için bir açılır liste kullanma

Örnek # 2'deki yöntemi kullanarak çok şey yapmamız gerekiyor kendi emeğiyle... Özellikle büyük miktarda veriyle çalışıyorsanız, hata yapma ve çok zaman kaybetme riski yüksektir.

Burada, arama için değerler olarak açılır listeleri kullanmak daha iyidir (örneğimizde, bunlar öğrencilerin adlarıdır ve Akademik konular).

Açılır listeden seçtiğiniz verilere bağlı olarak, işlev aşağıdaki örnekte gösterildiği gibi gerekli verileri otomatik olarak görüntüler:

Bu yaklaşım, büyük veri listelerini kolayca işleyebileceğiniz bir gösterge panosu oluşturmanıza olanak tanır.

Aşağıda böyle bir tablonun nasıl yapılacağına bakacağız.

Bunu yapmak için Örnek # 2'deki formüle ihtiyacımız var:

= DÜŞEYARA ($ G $ 5, $ A $ 4: $ E $ 12, MAÇ ($ H $ 4, $ A $ 3: $ E $ 3.0), 0) - İngilizce versiyon

= DÜŞEYARA ($ G $ 5; $ A $ 4: $ E $ 12; MAÇ ($ H $ 4; $ A $ 3: $ E $ 3,0); 0)- Rus versiyonu

Arama verileri açılır listelere dönüştürülmüştür.

Bir açılır liste oluşturmak için şunlara ihtiyacınız vardır:

• Açılır listeyi oluşturmak istediğiniz hücreyi seçin. Örneğimizde “G4” hücresinde öğrencilerin isimlerini belirttik.
• Veri sekmesine gidin -> Veri Doğrulama.
• Veri Doğrulama iletişim kutusundaki Ayarlar sekmesinde Liste'yi seçin ve açılır listeden İzin Ver'i seçin.
• Kaynakta $ A $ 3 seçin: $ A $ 10
• "Tamam" ı tıklayın.

Şimdi “G4” hücresinde bir açılır listeniz olacak. Aynı şekilde, öğeler için “H3” içinde oluşturabilirsiniz.

Örnek 4. DÜŞEYARA verileri için üç faktörlü arama

Üç faktörlü arama nedir?

Örnek 2'de, farklı konulardaki öğrenciler için notları olan bir elektronik tablo kullandık. Notu almak için iki değişken kullandığımızdan (öğrenci adı ve konu) bu, iki faktörlü bir arama örneğidir.

Şimdi, yıl sonuna kadar öğrencinin üç seviye sınavı geçtiğini varsayalım: Giriş, Yarı Yıllık ve Final Sınavı.

Üç yönlü arama, belirli bir sınav düzeyine sahip bir konuda öğrencinin notlarını alma yeteneğidir.

İşte 3 yönlü arama örneği:

Yukarıdaki örnekte, işlev verileri üç durumda arar. farklı tablolar verilerle ("Giriş", "Yarı Yıllık" ve "Final Sınavı") ve belirli bir sınav düzeyinde belirli bir konuda öğrencinin notlarının değerlerini döndürür.

Bu tür hesaplamalar için formül bize yardımcı olacaktır:

= DÜŞEYARA (G5, SEÇ (EĞER (H3 = ”Giriş”, 1, EĞER (H3 = ”Yarı Yıllık”, 2,3)), $ A $ 4: $ E $ 12, $ A $ 16: $ E $ 24, $ A $ 28 : $ E $ 36), MAÇ (H4, $ A $ 3: $ E $ 3.0), 0) - İngilizce versiyon

= DÜŞEYARA (G5, SEÇİM (IF (H3 = ”Giriş”; 1; IF (H3 = ”Yarı-Yıllık”; 2; 3)); $ A $ 4: $ E $ 12; $ A $ 16: $ E $ 24; $ A $ 28 : $ E $ 36); ARAMA (H4; $ A $ 3: $ E $ 3; 0); 0)- Rus versiyonu

Bu formül, verilerin seçildiğinden emin olmak için SEÇ işlevini kullanır. doğru tablo... Formülün SEÇ bölümünü inceleyelim:

SEÇ (IF (H3 = ”Giriş”, 1, IF (H3 = ”Yarı Yıllık”, 2,3)), $ A $ 4: $ E $ 12, $ A $ 16: $ E $ 24, $ A 28 $: $ E $ 36), MAÇ (H4, $A $ 3: $E $ 3.0), 0) - İngilizce versiyon

SEÇİM (IF (H3 = ”Giriş”; 1; IF (H3 = ”Yarı Yıllık”; 2; 3)); $ A $ 4: $ E $ 12; $ A $ 16: $ E $ 24; $ A 28 $: $ E $ 36) ; ARAMA (H4; $ A $ 3: $ E $ 3; 0); 0)- Rus versiyonu

Formülün ilk argümanı (IF (H3 = "Giriş", 1, IF (H3 = "Yarıyıllık", 2,3) veya (IF (H3 = "Giriş"; 1; IF (H3 = "Yarı yıllık"; 2; 3) “H3” hücresini kontrol eder ve hangi muayene seviyesine başvurulduğunu belirler. "Giriş" ise, işlev, giriş sınavı notlarını içeren $ A $ 4: $ E $ 12 aralığındaki verileri döndürür.

Altı Aylık düzey seçilirse, sistem $ A $ 16: $ E $ 24 aralığındaki verileri döndürür, aksi takdirde $ A $ 28: $ E $ 36 aralığındaki verileri döndürür.

Bu yaklaşım, tablo dizisini dinamik ve üç faktörlü hale getirir.

Örnek 5. DÜŞEYARA işlevini kullanarak son sütun değerini alın

Kullanarak DÜŞEYARA listeden son sayıyı hesaplayabilirsiniz.

Excel'de kullanabileceğiniz en büyük pozitif sayı 9.99999999999999E + 307... Bu aynı zamanda en çok büyük oda odada arama DÜŞEYARA Ayrıca 9.99999999999999E + 307 .

Böyle bir hesaplamaya ihtiyacınız olacağını hiç sanmıyorum. Büyük sayı... Ama onunla listedeki son numarayı alabiliriz.

Aşağıda gösterildiği gibi bir veri kümeniz (A1: A14 hücre aralığında) olduğunu ve listedeki son hücreden sayıyı almak istediğinizi varsayalım.

Bunun için aşağıdaki formül bizim için uygundur:

= DÜŞEYARA (9.99999999999999E + 307, $ A $ 1: $ A $ 14, DOĞRU)- İngilizce versiyon

= DÜŞEYARA (9.99999999999999E + 307; 1 ABD Doları 1: 14 ABD Doları; DOĞRU)

Yukarıdaki formülün veri eşleşmesinin yaklaşık kesinliğini kullandığını unutmayın (ancak listeyi yeniden sıralamaya gerek yoktur).

Veri eşleşmesinin yaklaşık kesinliği ile işlev bu şekilde çalışır. O bakıyor sol sütun yukarıdan aşağıya.

• Tam bir eşleşme bulursa, o değeri döndürür;
• Arama değerinden daha büyük bir sayı bulursa => üstündeki hücredeki sayıyı döndürür;
• Arama değeri listedeki tüm sayılardan büyükse, işlev listeden son sayıyı döndürür.

Örneğimizde üçüncü senaryo çalışıyor.

Sayı 9.99999999999999E + 307 bu, bir arama terimi olarak kullanıldığında Excel'de kullanılabilecek en büyük sayıdır, ardından işlev DÜŞEYARA (DÜŞEYARA) listeden son numarayı döndürür.

Aynı şekilde, sonuncuyu geri döndürmek için bu prensibi kullanabilirsiniz. metin öğesi listeden. İşte bunu yapabilecek bir formül:

= DÜŞEYARA ("yayay", $ A $ 1: $ A $ 14,1, DOĞRU) - İngilizce versiyon

= DÜŞEYARA ("yay", $ A $ 1: $ A $ 14; 1, DOĞRU)- Rus versiyonu

Aynı mantık burada da geçerlidir. Sistem listedeki tüm isimlere bakar. "yayaya" en büyük metin değeri olduğundan => hesaplamanın sonucu veri listesinden en uç değer olacaktır.

Örnek 6 Joker Karakterler ve DÜŞEYARA Kullanarak Kısmi Arama

Excel'deki joker karakterler birçok durumda faydalı olabilir.

Aradığınız verilerle tam olarak eşleşmeyen bir listede bir değer aramanız gerektiğinde kısmi arama gereklidir.

Örneğin, aşağıda gösterildiği gibi bir veri kümeniz var ve listede bir “ABC” şirketi bulmak istiyorsunuz, ancak listede “ABC” yerine sadece “ABC Ltd” var.

"A" sütununda tam eşleşme olmadığı için arama değeri olarak "ABC" kullanamazsınız. Yaklaşık bir eşleşme genellikle hatalı sonuçlara yol açar ve listenin artan düzende sıralanmasını gerektirir.

Ancak, joker karakteri şurada kullanabilirsiniz: DÜŞEYARA (DÜŞEYARA) Verileri daha iyi eşleştirmek için.

Aşağıdaki formülü “D2” hücresine girin ve diğer hücrelere sürükleyin:

= DÜŞEYARA (“*” & C2 & ”*”, $ A $ 2: $ A $ 8,1, YANLIŞ)- İngilizce versiyon

= DÜŞEYARA (“*” & C2 & ”*”; $ A $ 2: $ A $ 8; 1; YANLIŞ) - Rus versiyonu

Nasıl çalışır?

Yukarıdaki formülde her iki taraftaki arama değerine yıldız ekledik. Bu tür "yıldızları" belirterek Excel'e, istenen verilerde değerin hem başında hem de sonunda başka kelimelerin, harflerin veya sayıların bulunabileceği bir arama yapmaya hazır olduğunuzu bildirirsiniz.

Böylece, bir arama yaparken Excel, “C2” hücresinden (bizim örneğimizde) kelimeyi içeren bir değeri aramanın gerekli olduğunu anlayacaktır.

Örneğin, “C2” hücresi “ABC” şirket adını içerir, bu nedenle işlev “A2: A8” veri aralığındaki değerlere bakar ve “ABC” arar. “ABC Ltd” de “ABC” kelimesini içerdiğinden “A2” hücresiyle eşleşir. “ABC”nin solunda veya sağında herhangi bir karakter olup olmaması önemli değil.

Not: DÜŞEYARA her zaman eşleşen ve daha fazla aramayı bırakan ilk değeri döndürür. Bu nedenle, bir şirket adınız varsa “ABC Ltd”. ve “ABC Corporation” listede, sırayla ilkini döndürür ve gerisini görmezden gelir.

Örnek 7. DÜŞEYARA işlevi, arama değerindeki eşleşmeye rağmen bir hata döndürür

Eşleşen bir arama değeri olduğunu ve işlevin bir hata döndürdüğünü görmek sizi çıldırtabilir.

Örneğin, tablonuzda bariz bir eşleşme (Ivan) var, ancak sistem hala bir hata veriyor.

Tablodaki değerlere daha derinden baktığınızda, veri aralığında “Ivan” değerinin bir boşlukla yazıldığını fark edebilirsiniz. Aradığınız veri aralığında değerlerden önce, kelimelerden sonra veya kelimeler arasında boşluk varsa, işlev bunlarla eşleşmeyecektir.

Bir çözüm var ve adı - KIRMIZI işlevi... O her şeyi kaldırır ekstra boşluklar anlamlarda.

Aşağıda, veri aralığınızdaki fazladan boşluklara rağmen verilerinizi eşleştirmenize yardımcı olacak bir formül bulunmaktadır.

= DÜŞEYARA (“Ivan”, KESME ($ A $ 2: $ A $ 15), 1.0)- İngilizce versiyon

= DÜŞEYARA (“Ivan”, KESME ($ A $ 2: $ A $ 15), 1, 0) - Rus versiyonu

kullanırken TRIM işlevinin çalışması için DÜŞEYARA (DÜŞEYARA), yazdıktan sonra formülü girmelisiniz. Anahtarları girin klavyede ve kombinasyonu kullanarak Ctrl tuşları+ Üst Karakter + Girin.

Örnek 8. Excel'de çeşitli koşullarla DÜŞEYARA işlevi

Temel biçimindeki bir işlev, tek bir arama değeri arayabilir ve belirli bir veri aralığından karşılık gelen verileri döndürebilir.

Ama çoğu zaman kullanmamız gerekiyor DÜŞEYARA birden fazla arama kriteri ile.

Üç seviyeli sınav için öğrenci sınavlarının sonuçlarını içeren bir tablonuz olduğunu varsayalım: "Giriş", "Yarı Yıllık" ve "Final Sınavı":

Bir işlevi kullanarak belirli bir öğrenci, konu ve sınav düzeyi için verileri eşleştirin DÜŞEYARA- kolay bir iş değil çünkü öğrenci ve konu ile ilgili verileri karşılaştırmak ilk maçın sonucunu vereceğinden ancak bu sonucun doğru ve tutarlı olacağı gerçeğinden uzaktır. gerekli seviye sınav.

Aşağıdaki örnekte gösterildiği gibi bir yardımcı sütun oluşturarak öğrenci + konu adı + sınav düzeyine göre arama yapabilirsiniz:

Yardımcı sütun sayesinde, verileri karşılaştırırken fonksiyonun gereksiz bir muayene seviyesi için veri döndürmesini önlemiş olduk.

Artık yardımcı sütun değerlerini arama değerleri olarak kullanabilirsiniz.

Aşağıda, e-tablonuzdaki verileri sınav düzeyine göre bir öğrenci listesiyle karşılaştırmanıza yardımcı olacak bir formül bulunmaktadır.

= DÜŞEYARA ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)- İngilizce versiyon

= DÜŞEYARA ($ F3 & ”|” & G $ 2; $ C $ 2: $ D $ 19; 2; 0)- Rus versiyonu

Bu formül ile yardımcı sütunda kontrol ettiğimiz arama değerini almak ve verileri almak için öğrencinin adı ve sınav seviyesini birleştirdik.

Örnek 9. DÜŞEYARA işlevini kullanarak hataları işleme

İşlev, verilen veri aralığındaki bir arama değeriyle her eşleşmediğinde bir hata döndürür.

Excel, hata mesajlarını belirttiğiniz metinle veya Sayısal değer“Hayır”, “Mevcut değil”, “Veri yok” vb. yazın.

Aşağıdaki örnekte, öğrenci Ivan'ın sınav sonuçlarını bir tablodan karşılaştırmaya çalışıyoruz. Ancak tabloda Ivan ile ilgili herhangi bir veri olmadığı için fonksiyon hata veriyor.

Hata mesajını daha resmi bir anlamla değiştirmek için fonksiyon

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 arıyor. 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.
DÜŞEYARA ilkesini biraz daha iyi anlamak için, bazı şeylerle başlamak daha iyidir. 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. Artı merhemde birkaç tane daha uçar: sadece tam isimler 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 ... Aralıklı görüntüleme ile biraz sonra 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 tabloda metin olarak yazılırsa olacaktır. (genellikle solda üst köşe yeşil bir üçgen belirir), 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 göründüğü 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ütununun hücrelerine 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" & "*" =>
"Ivanov *"
Aranacak çok değer varsa çok kullanışlıdır.
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ğinizden tamamen farklı olacaktır. İ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) yalnızca iki sütun içerir - B ve C. Sütun 3'ün sayısını (sayfadaki sayı nedir) belirtmeye çalışırsanız, 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 kullanıcılar Excel sürümleri 2007 ve üzeri, EĞERHATA kullanabilirsiniz:
= 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 olarak koyarız. Her şey bunun üzerine inşa edilmiştir. INDEX, Dizinin belirtilen satırında (Row_Number) ve birden fazla sütun varsa belirtilen sütunda (Column_number) 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, istenen 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'un ayrıca DÜŞEYARA'ya göre bir avantajı daha vardır. 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); ORTA (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, kriterin bulunduğu doğrunun konumunu arar. değere eşitÇizgide. 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))

Hepimiz ne kadar faydalı olduğunu biliyoruz. Muhtemelen Excel'deki her şeyin yarısı onunla yapılır. ancak bu işlev bir takım sınırlamalara sahiptir. Örneğin, DÜŞEYARA, bir tabloda yalnızca en soldaki sütunu arar veya yalnızca bir koşulu arar. Ama ya iki koşulu karşılayan bir değer döndürmemiz gerekirse. Bu durumda, bazı hilelere başvurmamız gerekecek. Bugünün makalesinde bu hileler hakkında konuşacağız.

Bu nedenle, iki koşullu bir joker karakter işlevi oluşturmak için dört seçeneği ele alacağız:

1. SUMPRODUCT işlevini kullanma

Pekala, en basitinden başlayacağız.

Ek bir sütun kullanma

Çoğu durumda karmaşık problemler küçük parçalara bölündüğünde daha kolay ve yönetilebilir hale gelir. Aynısı, Excel'de formüller oluştururken de geçerlidir.

Klasik bir örneğe bakalım. Ay ve şehir bazında satışların olduğu bir tablomuz var. Ve iki koşula karşılık gelen satışların değerini belirlememiz gerekiyor: ay - Şubat ve şehir - Samara.

DÜŞEYARA işlevini klasik biçimde kullanmak, yalnızca bir koşulla eşleşen bir değer döndürebileceğinden bize yardımcı olmaz. Ek bir sütun, Ay ve Şehir sütunlarının değerlerini birleştirdiğimiz durumdan çıkmamıza yardımcı olacaktır. Bunu yapmak için A2 hücresine = B2 & C2 formülünü yazın ve bu formülü A13 hücresine genişletin. Artık istediğimiz değeri döndürmek için A sütunundaki değerleri kullanabiliriz. Formülü G3 hücresine yazıyoruz:

DÜŞEYARA (G1A2: D13; 4; 0)

Bu formül, G1 ve G2 hücrelerindeki iki koşulu bir satırda birleştirir ve A sütununda tarar. gerekli kondisyon bulunduğunda formül, A1: D13 tablosunun dördüncü sütunundaki değeri döndürür, yani. kolon Satış.

Yeni bir arama tablosu oluşturmak için SEÇ işlevini kullanma

Eğer herhangi bir nedenle kullanım ek sütun bizim için bir seçenek değil, kullanabiliriz.

SELECT işlevini kullanmak, oluşturmak anlamına gelir. yeni masa sütun değerlerinin nerede olduğunu görmek için Ay ve Şehir zaten birleştirildi. Formülümüz şöyle görünecek:

DÜŞEYARA (G1 SEÇİMİ ((1; 2); B2: B13 ve C2: C13; D2: D13); 2; 0)

Bu formülün ana noktası, iki şeyi yapan SEÇİM bölümünde ((1; 2); B2: B13 & C2: C13; D2: D13) yatmaktadır:

1. Sütun değerlerini birleştirir Ay ve Şehir bir dizide: JanMoscow, FebMoscow ...
2. İki diziyi iki sütunlu bir tabloda birleştirir.

Bu işlevin sonucu şuna benzeyen bir tablo olacaktır:

Formül artık daha net.

ÖNEMLİ: Bir dizi formülü kullandığımızdan, programın niyetimizi bilmesini sağlamak için formülü girmeyi bitirdiğinizde Ctrl + Shift + Enter tuşlarına basın. Bu tuş kombinasyonuna bastıktan sonra program otomatik olarak yüklenecektir. diş telleri formülün başında ve sonunda.

INDEX ve SEARCH işlevlerini kullanma

Ele alacağımız üçüncü yöntem de bir dizi formülünün kullanımını içerir ve INDEX ve SEARCH fonksiyonlarını kullanır.

Formül şöyle görünecek.

İNDEKS (D2: D13; ARAMA (1, (B2: B13 = G1) * (C2: C13 = G2); 0))

Bu formülün her bir parçasının ne yaptığına bir göz atalım.

İlk olarak, KAÇINCI işlevini düşünün (1; (B2: B13 = G1) * (C2: C13 = G2); 0). Bu durumda, G1 hücresinin değerini B2: B13 aralığındaki her hücre değeriyle sırayla karşılaştırır ve değerler eşleşirse DOĞRU, değilse YANLIŞ döndürür. Aynı karşılaştırma G2 hücresinin değeri ve C2:C13 aralığı ile yapılır. Daha sonra, bu TRUE ve FALSE dizilerinin ikisini de karşılaştırırız. DOĞRU * DOĞRU kombinasyonu bize 1 (DOĞRU) sonucunu verir. Nasıl çalıştığını daha net bir şekilde açıklamaya yardımcı olmak için aşağıdaki resme bir göz atalım.

Şimdi her iki koşulu da sağlayan dizenin nerede olduğunu söyleyebiliriz. KAÇINCI işlevi, sonuçtaki dizide 1. konumu arar ve biri altıncı satırda göründüğü için 6 değerini döndürür. Ardından, INDEX işlevi, D2: D13 aralığının altıncı satırının değerini döndürür.

SUMPRODUCT'i kullanma

En güçlülerinden biri Excel formülleri... Bu formülle ilgili ayrı bir makalem bile var. Birden çok koşul kullanmanın dördüncü yolu, SUMPRODUCT işleviyle bir formül yazmaktır. Ve şöyle görünecek:

TOPLAÇ ((B2: B13 = G1) * (C2: C13 = G2); D2: D13)

Bu formülün çalışma prensibi, önceki yaklaşımın çalışma prensibine benzer. G1 ve G2 hücrelerinin değerlerini sırasıyla B2: B13 ve C2: C13 aralıklarıyla karşılaştıran sanal bir tablo oluşturulur. Daha sonra bu dizilerin her ikisi de karşılaştırılır ve her iki koşulun eşleştiği satıra bir atanan birler ve sıfırlardan oluşan bir dizi elde edilir. Daha sonra bu sanal dizi, D2: D13 aralığı ile çarpılır. Sanal dizimiz altıncı satırda yalnızca bir birim olacağı için formül 189 değerini döndürecektir.

D2:D13 aralığında metin değerleri varsa bu fonksiyon çalışmayacaktır.

Nasıl çalıştığını anlamak için bu formül, SUMPROIZ işlevi hakkındaki makaleyi okumanızı tavsiye ederim.

TOPLAM

Peki hangi yolu kullanmalı? Hepsi stabil çalışsa da ben ilk yöntemi tercih ediyorum. Günlük işlerimde, anlaşılması kolay ve değiştirilebilir dosyalarla çalışmayı tercih ederim. Bu gereksinimlerin her ikisi de ilk yaklaşımın koşullarını karşılar.

Formülleri daha iyi anlamak için, bugünün makalesinde demonte edebilirsiniz.

Diyelim ki malların fiyat listesini içeren bir tablomuz var. Görev, Siparişler tablosunu doldurmaktır.

Bu sorunu çözmek için kullanacağız DÜŞEYARA işlevi... Excel'de çözmek için kullanılabilecek başka işlevler vardır. bu görev ama analiz yapıyoruz Excel'deki en popüler ve sık kullanılan DÜŞEYARA işlevi.

Bu nedenle, örneğimizdeki sorunu çözmek için önce sütunu doldurmamız gerekiyor. "İLE BİRLİKTE" sipariş tablosunda, yani "Fiyat listesi" tablosunda malların fiyatını bulun ve ardından maliyeti bulmak için fiyatı mal sayısıyla çarpın.

Excel'deki DÜŞEYARA işlevinin sözdizimi

DÜŞEYARA (arama_değeri, tablo, sütun_sayısı, [aralık_görünüm])

DÜŞEYARA işlevi tablonun en soldaki sütununda bir değer arar ve aynı satırın belirtilen sütunundaki hücrenin değerini döndürür.

Örneğimizi kullanarak fonksiyonu analiz edelim. C3 hücresine oranları girin ve formülü yazın = DÜŞEYARA (A3; $ F $ 2: $ H $ 22; 3; 0)

v ingilizce versiyon= DÜŞEYARA (A3; $ F $ 2: $ H $ 22; 3; 0)

DÜŞEYARA (İngilizce DÜŞEYARA) içeren bu formülde:

A3 - aranan_değer... Bizim durumumuzda, bu "Kova", yani "Fiyat Listesi" tablosunda "kova" yı bulmamız gerekiyor.

$ F $ 2: $ H $ 22 - tablo... Örneğimizde bu "Fiyat Listesi" tablosudur (F2: H22). Bu tablonun aralığında, formülü esnettiğimizde aşağı hareket etmemesi için onu sabitlemek için $ işaretleri eklenmiştir. Excel'deki dolar işareti, göreli bir aralığı mutlak bir aralığa dönüştürür.

3 – sütun numarası... Bizim durumumuzda bu, “3” sayısıdır, çünkü fiyat “Fiyat Listemiz” tablomuzun üçüncü sütunundadır.

0 - aralık görünümü. Yalnızca iki 0 veya 1: 0 değeri alabilir - tam eşleşme arar, 1 - yaklaşık. Vakaların %99'unda aramanız gerekir Kesin değer(bizim durumumuzda "Kova" kelimesini aramamız gerekiyor). Bu nedenle, 0 sayısı neredeyse her zaman belirtilir.

Böylece, Excel DÜŞEYARA mantığıörneğimizde, aşağıdaki. İşlev, tablonun en sol sütununda ("Fiyat listesi") istenen değeri ("kova") arar, bulduktan sonra - belirtilen sütunda bulunan hücrenin değerini döndürür aynı çizgi, yani fiyat 120 ruble.

Ondan sonra hücreye git D3 ve malın değerini bulur. formülü reçete etmek = C3 * B3 yani, ürünün fiyatını miktarla çarpıyoruz.

Ayrıca, ürünlerin geri kalanı için formüllerin otomatik olarak ayarlanması için formülü aşağı çekmeniz gerekir. Bunu yapmak için, sağ alt köşeden gerilmesi ve aşağı çekilmesi gereken her iki hücreyi seçin (bkz. Şekil 3)