Bu yazımızda Excel'de iki sayı arasındaki değeri ARA nasıl yapılır öğreneceğiz.
Senaryo:
Bir tabloda tek bir kriterle değer aramak kolaydır. DÜŞEYARA'yı basitçe kullanabiliriz. Ancak, verilerinizde eşleşecek birden çok sütun ölçütü varsa ve bir değeri eşleştirmek için birden çok sütunda aramamız gerekiyorsa ne yapabiliriz. Excel'in farklı arama sürümlerini kullanarak bu sorunun nasıl çözülebileceğini öğrenelim.
iki sayı arasındaki ARA değeri
DÜŞEYARA formülünü kullanma
DÜŞEYARA yaklaşık eşleşmesinde, bir değer bulunamazsa, arama değerinden küçük olan son değer eşleştirilir ve verilen sütun dizinindeki değer döndürülür.
İki sayı arasındaki ARA değeri için genel formül:
| = DÜŞEYARA (değer, tablo, aranan_kol , 1 ) |
Ve Vlookup ile ilgili bir şey daha, sütundaki değeri arar ve sütun dizisindeki değeri bulamazsa, eşleşir ve tablo dizisindeki bu değerden küçük olan değeri döndürür.
Not : DÜŞEYARA, aralık arama değişkenini atlarsak, varsayılan olarak yaklaşık bir eşleşme yapar. Yaklaşık bir eşleşme yapmak istediğinizde ve tablo dizinizi artan düzende sıraladığınızda yaklaşık eşleşme yararlıdır.
İNDEKS ve KAÇINCI formülünü kullanma
İNDEKS ve KAÇINCI formülü yukarıdakiyle aynı işi yapar ancak farklı sözdizimi ile. Ancak, yalnızca hangi işlevlerde rahat olduğunuzu seçmeniz gerekiyorsa
İki sayı arasındaki ARA değerine Genel Formül
| =INDEX(arama_aralığı,MATCH(1,INDEX((kriter1=aralık1)*(ölçüt2=aralık2),0,1),0)) |
aranan_aralık: içinden değer almak istediğiniz aralık.
Ölçüt1, Ölçüt2, Ölçüt N: Bunlar, aralık1, aralık2 ve Aralık N'de eşleştirmek istediğiniz kriterlerdir. En fazla 270 kriteriniz olabilir - aralık çiftleri.
Aralık1, aralık2, aralıkN : Bunlar, ilgili kriterlerinize uyacağınız aralıklardır.
Örnek :
Bütün bunları anlamak kafa karıştırıcı olabilir. Bir örnek kullanarak işlevin nasıl kullanılacağını anlayalım. Burada bir testte Öğrenci Kimliği ve ilgili notları var. Şimdi not sistemi tablosuna bakarak her öğrencinin notlarını almamız gerekiyordu.
Bunu yapmak için, DÜŞEYARA işlevinin bir özniteliğini kullanacağız; bu, DÜŞEYARA işlevi tam eşleşmeyi bulamazsa, yalnızca tabloda yaklaşık eşleşmeyi arar ve yalnızca işlevin son argümanı aşağıdakilerden biriyse NS veya 1.
Notlar / puan tablosu artan sırada olmalıdır
Formülü kullanın:
| = DÜŞEYARA ( B2 , tablo , 2 , 1 ) |
O nasıl çalışır?
Vlookup işlevi, işaretler sütununda 40 değerini arar ve eşleşirse karşılık gelen değeri döndürür. Eşleşmiyorsa, işlev arama değerinden (yani 40) daha küçük değeri arar ve sonucunu döndürür.
Burada tablo_dizisi aralık olarak adlandırılır. tablo formülde ve B2 hücre referansı olarak verilmiştir.
Gördüğünüz gibi ilk öğrencinin notunu aldık. Şimdi diğer tüm Notları almak için kısayol kullanacağız Ctrl + D veya excel'de hücreyi aşağı sürükle seçeneğini kullanın.
DÜŞEYARA işlevini kullanan sınıfın tüm Notları burada.
Çalışan tablosundaki iki sayı arasındaki ARA değeri
Bir organizasyondaki tüm çalışanların detaylarını ayrı bir sayfada içeren bir tablomuz var. İlk sütun, bu çalışanların kimliğini içerir. Bu tabloya emp_data adını verdim.
Şimdi arama sayfamın, kimliği B3 hücresine yazılan çalışanın bilgilerini alması gerekiyor. B3'ü kimlik olarak adlandırdım.
Kolay anlaşılması için tüm sütun başlıkları emp_data tablosuyla tam olarak aynı sıradadır.
Şimdi, B3'te yazılan çalışan kimliğinin bölgesini almak için aşağıdaki formülü C3 hücresine yazın.
| =DÜŞEYARA(ID,Emp_Data,2,0) |
Bu, 1-1830456593 numaralı çalışanın bölgesini döndürür, çünkü veritabanındaki 2 numaralı sütun, çalışanların bölgesini içerir.
Bu formülü diğer hücrelere kopyalayın ve çalışanların tüm bilgilerini almak için formüldeki sütun numarasını değiştirin.
Belirtilen ID ile ilgili tüm bilgileri Hücre B3'te görebilirsiniz. B3 hücresine hangi id'yi yazarsanız yazın, formülde herhangi bir değişiklik yapılmadan tüm bilgiler gelecektir.
Bu nasıl çalışıyor?
Zor bir şey yok. Kimlik aramak için DÜŞEYARA işlevini kullanıyoruz ve ardından belirtilen sütunu alıyoruz. DÜŞEYARA'yı daha iyi anlamak için bu tür verileri kullanarak DÜŞEYARA alıştırması yapın.
Başlıkları Kullanarak Çalışan Verilerini Alın
Yukarıdaki örnekte, tüm sütunları aynı sırada düzenledik, ancak bazen yüzlerce sütuna sahip bir veritabanınız olacak. Bu gibi durumlarda, çalışan bilgilerini alma yöntemi iyi olmayacaktır. Formülün sütun başlığına bakabilmesi ve çalışan tablosundan o sütundan veri alabilmesi daha iyi olacaktır.
Bu nedenle, sütun başlığını kullanarak tablodan değer almak için 2 yönlü bir arama yöntemi kullanacağız veya dinamik sütun DÜŞEYARA diyeceğiz.
Bu formülü C3 hücresinde kullanın ve kalan hücrelere kopyalayın. Formülde hiçbir şeyi değiştirmenize gerek yok, her şey thd emp_data'dan alınacak.
| =DÜŞEYARA(ID,Emp_Data,MATCH(C2,Emp_Data_Headers,0),0) |
Bu formül, eşleşen sütunlardan tüm bilgileri alır. Rapordaki başlıkları karıştırabilirsiniz, bu herhangi bir fark yaratmaz. Yukarıdaki hücrede hangi başlık yazılıysa, ilgili verileri içerir.
Bu nasıl çalışıyor?
Bu sadece dinamik bir DÜŞEYARA'dır. Bununla ilgili buradan okuyabilirsiniz. Burada açıklarsam çok büyük bir yazı olur.
Kısmi Eşleme ile Çalışan Kimliğini Al
Bir çalışanın kimliğini tam olarak hatırlamıyor olabilirsiniz, ancak yine de bazı kimlik bilgilerini almak isteyebilirsiniz. Bu gibi durumlarda kısmi eşleşme DÜŞEYARA en iyi çözümdür.
Örneğin, bir kimliğin 2345 içerdiğini biliyorsam, ancak tüm kimliği bilmiyorum. Bu sayıyı Hücre C3'e girersem çıktısı gibi olacaktır.
Hiçbir şey alamıyoruz. Tabloda hiçbir şey 2345 ile eşleşmediğinden. Yukarıdaki formülü bu şekilde değiştirin.
| =DÜŞEYARA("*"&İD&"*",Emp_Data,KİBRİT(C2,Emp_Data_Headers,0),0) |
Bunu tüm satıra kopyalayın. Ve şimdi bu numarayı içeren ilk çalışanın bilgilerine sahipsiniz.
Emp Kimliği sütununda eşleşen numarayı içeren ilk kimliği alacağımızı lütfen unutmayın. Başka bir kimlik aynı numarayı içeriyorsa, bu formül o çalışanın bilgilerini almaz.
Aynı numarayı içeren tüm çalışan kimliklerini almak istiyorsanız, eşleşen tüm değerleri arayan formülü kullanın.
İNDEKS ve KAÇINCI formülünü kullanma
Burada bir veri tablomuz var. Rezervasyon Tarihi, Oluşturucu ve Alan kullanarak müşterinin Adını çekmek istiyorum. Yani burada üç kriterim ve bir arama aralığım var.
Bu formülü I4 hücresine yazın enter tuşuna basın.
| =INDEX(E2:E16,MATCH(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0)) |
Nasıl çalışır:
EXCEL'de INDEX ve MATCH işlevinin nasıl çalıştığını zaten biliyoruz, bu yüzden bunu burada açıklamayacağım. Burada kullandığımız hileden bahsedeceğiz.
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16): Ana kısım şudur. Bu ifadenin her bölümü bir true false dizisi döndürür.
Boole değerleri çarpıldığında 0 ve 1 dizisini döndürürler. Çarpma bir AND operatörü olarak çalışır. Hense, tüm değerler yalnızca doğru olduğunda 1 başka 0 döndürür
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16) Bu tamamen geri dönecek
| {YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;DOĞRU;DOĞRU;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ}*
{YANLIŞ;YANLIŞ;YANLIŞ;DOĞRU;DOĞRU;DOĞRU;DOĞRU;DOĞRU;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;DOĞRU}* {YANLIŞ;YANLIŞ;YANLIŞ;DOĞRU;YANLIŞ;YANLIŞ;YANLIŞ;DOĞRU;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ} |
Hangisine çevirecek
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): INDEX İşlevi aynı diziyi döndürür ({0;0;0;0;0; 0;0;1;0;0;0;0;0;0;0}) arama dizisi olarak KAÇINCI işlevine.
KAÇINCI(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): KAÇINCI işlevi {0;0;0 dizisinde 1'i arar; 0;0;0;0;1;0;0;0;0;0;0;0} ve dizide bulunan ilk 1'in indeks numarasını döndürür.Burada 8'dir.
INDEX(E2:E16,MATCH(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0)): Son olarak, INDEX geri dönecektir bulunan indekste (8) verilen aralıktaki (E2:E16) değer.
Excel'deki formülü kullanan tüm gözlem notları burada
Notlar:
- Puanlar / puanlar tablosu sütunu artan sırada olmalıdır, aksi takdirde fonksiyon yanlış sonuç verebilir.
- DÜŞEYARA işlevi, Tablo_dizisinin ilk satırındaki değeri arar ve yalnızca aranan_aralığın sağındaki karşılık gelen değerleri çıkarır.
- DÜŞEYARA işlevinin son argümanı, yaklaşık eşleşmeyi elde etmek için DOĞRU veya 1 olarak ayarlanmalıdır.
- DÜŞEYARA işlevi, çalışma kitabı adresi geçersiz veya yanlışsa bir hata döndürür.
- Değer eşleşmezse DÜŞEYARA işlevi bir hata döndürür.
Excel'de iki sayı arasındaki değerin nasıl aranacağı ile ilgili bu makalenin açıklayıcı olmasını umuyoruz. Değerleri hesaplama ve ilgili Excel formülleri hakkında daha fazla makaleyi burada bulabilirsiniz. Bloglarımızı beğendiyseniz, Facebook'ta arkadaşlarınızla paylaşın. Ayrıca bizi Twitter ve Facebook'ta da takip edebilirsiniz. Sizden haber almayı çok isteriz, işimizi nasıl iyileştirebileceğimizi, tamamlayabileceğimizi veya yenileyebileceğimizi ve sizin için daha iyi hale getirebileceğimizi bize bildirin. E-posta sitesinde bize yazın.
Veri Doğrulamada Vlookup işlevini kullanmanın yolu : Excel'deki Veri doğrulama formül kutusunu kullanarak kullanıcıları arama tablosundaki değerlere izin verecek şekilde kısıtlayın. Veri doğrulamadaki formül kutusu, gerekli kısıtlama türünü seçmenize olanak tanır.
Excel'de En Son Fiyat Nasıl Alınır : Herhangi bir işte fiyatları güncellemek yaygındır ve herhangi bir satın alma veya satış için en son fiyatları kullanmak bir zorunluluktur. Excel'deki bir listeden en son fiyatı almak için ARA işlevini kullanırız. ARA işlevi en son fiyatı getirir.
Excel'de notu hesaplamak için DÜŞEYARA işlevi : Notları hesaplamak için IF ve IFS kullanabileceğiniz tek işlevler değildir. DÜŞEYARA bu tür koşullu hesaplamalar için daha verimli ve dinamiktir. DÜŞEYARA kullanarak notları hesaplamak için bu formülü kullanabiliriz.
Excel DÜŞEYARA Hakkında 17 Şey : DÜŞEYARA en yaygın olarak eşleşen değerleri almak için kullanılır, ancak DÜŞEYARA bundan çok daha fazlasını yapabilir. İşte DÜŞEYARA hakkında etkili bir şekilde kullanmak için bilmeniz gereken 17 şey.
Excel'deki Bir Listeden İlk Metni ARA : DÜŞEYARA işlevi, joker karakterlerle sorunsuz çalışır. Bunu, Excel'deki belirli bir listeden ilk metin değerini çıkarmak için kullanabiliriz. İşte genel formül.
Listedeki son değere sahip ARAMA tarihi : Son değeri içeren tarihi almak için ARA işlevini kullanırız. Bu işlev, bir vektördeki son değeri içeren hücreyi kontrol eder ve ardından tarihi döndürmek için bu başvuruyu kullanır.
Popüler Makaleler :
Verimliliğinizi Artıracak 50 Excel Kısayolu : Excel'deki görevlerinizde daha hızlı olun. Bu kısayollar, Excel'deki iş verimliliğinizi artırmanıza yardımcı olacaktır.
Excel'de DÜŞEYARA İşlevi nasıl kullanılır? : Bu, farklı aralıklardan ve sayfalardan değerleri aramak için kullanılan excel'in en çok kullanılan ve popüler işlevlerinden biridir.
Excel'de EĞER İşlevi nasıl kullanılır? : Excel'deki EĞER ifadesi koşulu kontrol eder ve koşul DOĞRU ise belirli bir değer veya YANLIŞ ise başka bir belirli değer döndürür.
Excel'de SUMIF İşlevi nasıl kullanılır? : Bu, başka bir gösterge panosu temel işlevidir. Bu, belirli koşullardaki değerleri özetlemenize yardımcı olur.
Excel'de COUNTIF İşlevi nasıl kullanılır? : Bu şaşırtıcı işlevi kullanarak değerleri koşullarla sayın. Belirli değerleri saymak için verilerinizi filtrelemeniz gerekmez. Gösterge tablonuzu hazırlamak için Countif işlevi gereklidir.