Excel: Düşeyara Formülü Kullanımı Kılavuzu 32 Madde [2 şablon örnek dosya içerir]16 min read

Excel düşeyara formülü en çok iki karışık listeyi karşılaştırmak için kullanılır.

Düşeyara formülü o kadar çok kullanırız ki düşünmeden uygularız çoğu zaman.

Excel düşeyara formülü microsoft’un sitesine göre en çok kullanılan 10 formüller arasında.

Bir senaryo üzerinden anlatacağım;

Excel düşeyara formülü amacı nedir?

Bu iki listenin ortak bir sütunu olur ama karşısındaki veriler aynı sırada değildir.

Sütunlardaki hücre sayıları çok fazla olduğu için de elle karşılaştırmak istemezsiniz.

Bu noktada excel düşeyara formülü yardıma koşuyor.

Yukarıdaki iki liste karşılaştırmasında iki soru aklınıza belirebilir.

• Müşterinin istediği stok miktarı depomda mevcut mu?
• Müşterinin kendi yazdığı fiyat benim sistemimdeki ile aynı mı? (Bazenmüşteriler eski fiyatı yazarlar)

Bu iki soruyu tek tek ürünleri elle karşılaştırmadan 1 dakikadan az bir sürede cevaplayabiliriz. Düşeyara formülü ile iki liste arasında karşılaştırma yapacağız.

Excel şablon çalışma dosyasını indirin.

Düşeyara formülünün nasıl çalıştığını biraz anlatayım;

Düşeyara formülü kurulumu

1- Temel kurulum

Aşağıdaki örnekte müşteri talebinin karşısına stokların yeterli olup olmadığını görmek için yan taraftaki stokları getireceğiz.

Basit bir düşeyara formülü kullanalım.

Formül şu şekilde;

=DÜŞEYARA(G3;B:D;3;YANLIŞ)

Bu formül G3 Hücresinin emsalini soldaki B:D sütun aralığında arar ve en solda G3 emsalini bulduğunda karşısındaki bu aralıktaki 3. Sütunun içerdiği değeri karşısına getirir.

Formülü kurup enter’a bastığınızda sol sütunlardaki veri sağ taraftaki veri sütunlarına kopyalanır.

Sağ alt köşesine çift tıklatın

Aynı formülü tüm sütuna uygulayacaktır.

Bir de müşterinin istediği fiyatları da sipariş listesine aktaralım.

Yine aynı fomülü yan hücreye uygulayıp tüm sütuna çoğaltacağız.

Bu kez aradığımız veri soldaki emsal sütunların 2 nolu sütunu olduğu için formüle B:C;2 yazıyoruz. Yani B-C sütun aralığının 2 nolu sütununu ara demek istiyoruz.

Karşımıza tüm sütuna uygulanmış tablo çıkıyor.

İş hayatında bu kadar sık kullandığımız bir formülü bazı personel çok çabuk kavrarken bazı excel kullanıcıları ise çok uzun zamanda zor kavrıyor. Düşeyara formülünün kurgusunu iyice anlarsanız, pratiklik süresini kısaltabilirsiniz.

Excel yardımcı olmak adına size 4 veriyi alırken yol gösteren isimleri yazıyor.

Düşeyara formülü şu şekilde kurgulanıyor.

=DÜŞEYARA(G3;B:D;3;YANLIŞ)

• Aranan değer: G3= karşısına bir veri getirmek istediğiniz sütunun ilk hücresi
• Tablo dizisi: B:D = diğer emsal aldığınız, veri aradığınız sütun aralığı
• Sütun indis sayısı: 3 = veri aldığınız sütun aralığında aradığınız verinin sütun sayısı
• [aralık bak] :0 veya YANLIŞ = verilerin tam eşleşmesini istemek

2- Düşeyara formülü fonksiyon kutusuyla kurulumu

Düşeyara formülünü hücreye doğrudan yazarak da kullanabilirsiniz fakt isterseniz FX butonuna basıp kutucuktan düşeyara yazarak formülü bulun ve tamam’a tıklayın.

Düşeyara kutucuğunu şöyle kullanacağız;

Aranan değer: karşısına diğer tablodan rakam getirmek istediğimiz hücre;

Tablo dizisi: Ortak sütun ve karşısına getireceğimiz veri sütununa kadar seçiyoruz

Sütun indis sayısı: veri sütununun kaçıncı sütun olduğu

Aralık bak: 0 veya yanlış yazın. Tam eşleşme demektir.

3- Düşeyara formülü soldan sağa çalışır.

Yani en solda ortak bir sütun olmalıdır. O sütunda eşleşenlerin karşısına diğer sütundan veri çekeceğiz.

Ortak sütunları en sola alıyoruz. Birbiriyle eşleştirmemiz gereken iki sütunun solda olması her zaman önemli çünkü düşeyara formülü soldan sağa çalışıyor. Bazen bilgi işlem departmanı size çok sütunlu veri verir ve emsal almak istediğiniz sütunlar birinde başta birinde ortada olabilir. O sütunları en sola alarak çalışmanız gerekebilir.

Eğer emsal aldığınız ortak sütunu yer değiştirmek isteyebilirsiniz.

Fakat sütunu yer değiştirirseniz formül çalışmaz hale gelir.

4- F4 ile veri grubu sabitleme

Düşeyara formülünü uygularken iki veri setiniz varsa ve sadece 1 tanesinde arama yapmak istiyorsanız veri alma bölümünü sabitlemelisiniz.

Örneğin klasik düşeyara formülünü kurdunuz ve alt satırlara kopyaladınız. Fakat sadece belli bir bölümde arama yapmak istiyor ve alt verilerde arama yapmak istemiyorsanız klasik, sabitlenmemiş formül kayma yapar ve formül hatalı veri getirir.

Örneğin formül kayma yapması demek alt satırlardan da veri almaya çalışması demektir.

Excelde formüllerin doğası böyle. Eğer sütunun tamamı değil bir bölgesinden veri almak isterseniz A6:B12 aralığını yani veri aldığınız alanı tıklayıp F4’e tıklayın. F4 veriye $ işareti getirecektir.

F4 e basmak ve $ işaretini kullanmak veri alınan yeri sabitleyecektir.

Hangi hücreye tıkalrsanız tıklayın tablo dizisi sabit kalacaktır.

5- Tekrar eden veriler

Düşeyara formülü tablo dizisini tararken aranan değere emsal 2 veya daha fazla veri bulabilir.

Bu durumda düşeyara formülü mekaniği ilk değerin karşısındaki veriyi diğer tabloya taşır.

6- Pivot tablolarda düşeyara

Normal bir veri aralığının yanına düşeyaradan veri arayarak çekecekseniz yine veri aralığı gibi pivot tablonun olduğu sütunları seçerek düşeyara formülünü çalıştırabilirsiniz.

7- Farklı sekmelerde düşeyara formülü

Bir veri eşleşmesi yapmak için düşeyara formülünü çalıştırmak istediğinizde ölçü alacağınız diğer tablonun aynı sayfada olmasına gerek yoktur. Farklı bir sekmede yani sayfada olabilir.

Yukarıdaki örnekteki gibi tablo dizini bu makalede indireceğiniz çalışma dosyasındaki 5 nolu sayfadan veri alıyor. Düşeyara formülünün bulunduğu sayfa ise 7 nolu sayfa.

8- Sekmelerde tablo ismiyle düşeyara

Düşeyara formülünü kurarken tablo dizinini sütunlrdan veya F4 ile seçerek belirlemeyi öğrendik. Şimdi de farklı sekmedeki bir tabloyu hiç görmeden tablo ismiyle düşeyara formülünü kurabiliriz.

Örneğin;

Bir veri aralığını tablo nesnesine dönüştürelim.

Ve adını “ürünkodu” olarak belirleyelim

Düşeyara formülünü yazarken daha “ür” yazdığımızda bile excel ürünkodu tablosunu mu demek istedin diye seçenek sunuyor.

Ürün kodu tablo butonuna tıklayıp formüle devam edebilirsiniz.

9- Farklı versiyonlarda düşeyara formülü

Düşeyara formülü tüm sütunu seçtiğinizde iki farklı versiyonun satır sayısı farklı olduğundan çalışmaz ve uyarı verebilir Verileri aynı excel çalışma kitabına toplarsanız çalışmaya devam eder.

10- İç içe düşeyara formülü

Düşeyara yı başka bir formülle de kulalnabilirsiniz. En bilindik olanı da EĞERHATA formülüdür. Bir eşleşme aradığınızda ve her satıra eşleşme bulamadığınızda excel bu hüçreleri hatalı olarak görüyor ve #YOK  gibi bir hata veriyor. Böyle durumlarda hata mesajı yerine sıfır yazdırabilirsiniz. Çünkü dip toplam alıp ne kadarı eşleştiğini görmek isteyebilirsiniz. Dip toplam almak istediğinizde hata mesajları toplam almanıza izin vermeyecektir.

Düşeyara formülünü kurduktan sonra formülü kapatmadan başına gelip eğerhata verirse 0 yaz şartını koyacağız. Formül şu şekilde olacak

Bu durumda veriyi bulamadığında yerine sıfır yazacak.

11- İki tablo dizisinde düşeyara formülü kullanımı

Bir eşleşme için bir tabloyu tablo dizisi olarak kullanmayı biliyoruz. Fakat elinizde eşleşme arayacağınız iki tablo varsa ikisini birleştirmeden formülde küçük bir ayar ile iki tabloyu birden tablo dizisi olarak tanımlayabiliriz.

Önce bir tabloyu seçip F4 ile sabitliyoruz ve diğer tabloyu seçmeden önce “:” iki nokta üstüste koyuyoruz ve alttaki tabloyu da seçiyoruz. Bu şekilde iki tabloyu da tablo dizini olarak atamış oluyoruz. Düşeyara formülünün gerisi aynı kurgu.

12- Metin ve sayı formatı

İki tablo dizinini karşılaştıracağımız zaman ikisinin de aynı formatta olması gerekiyor. Biri metin biri sayı olduğunda eşleşme olmuyor.

Formülü yukarıdaki gibi kurgulasak da

Aşağıdaki gibi eşleşme maalesef olmayacaktır.

Ürün kodu sütunu formatı bir tabloda rakam iken diğer tabloda metin olduğundan düşeyara formülü çalışamıyor.

Sağ taraftaki metin biçiminde olan ürün kodu sütununu seçip, sağ tıklayıp hücre biçimlendir dediğimizde bir kutucuk çıkacak.

Metin olan formatı sayıya çevirdiğinizde formül otomatik olarak çalışacak ve eşleşme gerçekleşecek.

*Bonus: Yardımcı Formüller

Sayıyaçevir

Hücre biçimi Bir metin olarak saklanan veriyi sayı formatına çevirmenin yöntemlerinden biridir.

=SAYIYAÇEVİR(metin) formülü ile basitçe kurgulanabilir.

Metneçevir

Sayı veya tarih içeren bir hücrenin içindeki veriyi görünümünü değiştirmeden metin formatına değiştirir. Formül kurgusu şu şekildedir;

= METNEÇEVİR( değer ; biçim_metni )

Değer: çevirmek istediğiniz değer

Biçim metni: görünmesini istediğiniz hücre biçimi. “gg.aa.yyyy” gibi.

*Not: Yeni biçimi yazarken tırnak işareti içine almalısınız.

Benzer biçimlendirmeyi CTRL+1 deki hücre biçimlendir kutucuğundan da yapabilirsiniz.

13- Düşeyara formülü yaklaşık arama

Şimdiye kadar hep tam eşleşme uyguladık. Fakat düşeyara formülü belirlediğimiz belli aralıkları da okuyup verinin yanına getirebiliyor. Örneğin;

Düşük , orta ve yüksek aralıkları belirlediniz. Rakamsal aralıklar belirlediiz. Verilen siparişleri de bu aralıklarla eşleştireceksiniz.

Yukarıdaki formülü şu şekilde kuruyoruz.

=DÜŞEYARA(B5;G:H;2;DOĞRU)

• Aranan değer: B5 (sipariş adeti)
• Tablo dizisi: G:H (aralıkları yazdığınız yer)
• Sütun indis sayısı: 2 (düşük , orta , yüksek)
• Aralık bak: doğru (yaklaşık eşleşme veya 1)

Formül aralıkların isimlerini siparişlerle eşleştirip sipariş rakamlarının yanına getiriyor.

14- Çoklu sütunlarda düşeyara formülü sütun sayısı yardımı

Eğer iki veya üç sütunluysa aradığınız derin kaçıncı sütunda olduğunu bulmak oldukça kolay. Fakat çok sütunlu verilerde elle tek tek saymak hataya sebep olabilir.

Excel size saymayı kolaylaştıran bir ipucu gösterir.

Yukarıdaki tabloda tablo dizisini seçerken sağ üst köşede bilgi kutucuğu çıkıyor. Orada kaçıncı sütuna kadar seçtiğinizi gösteriyor.

15- Çoklu sütunlarda sütun sayısını yazma

Çok sütunlu büyük tablolarda sütunlara önceden sıra numarası verirseniz de saymayı kolaylaştırabilirsiniz.

Formülü kurarken de aşağıdaki gibi satırı sabitleyerek kurarsanız sağa çektiğinizde formül sabit kalacaktır.

=DÜŞEYARA($L3;$B:$I;D$1;0)

16- Dinamik listelerde düşeyara formülü

Bazı dosyalara dinamik bir liste oluşturursunuz ve dinamik liste değiştikçe aşağıdaki ona bağlı veriler de değişsin istersiniz.

Düşeyarayı dinamik kurgulamak için dinamik listedeki veriye F4 ile formülü sabitliyoruz.

Veri değiştikçe düşeyara formülünün aradığı değer aynı kalıyor.

Fiyat için kurduğunuz formulü aşağıdaki tedarikçi sütunu için de kuruyorsunuz.

Listeden ürün değiştirdikçe aşağıdaki fiyat ve tedarikçi de değişiyor.

17- Yatayara formülü kullanımı

Yatayara formülü tabloların eksenlerinin farklı olduğu zamanlarda düşeyara formülü ile aynı mantıkta çalışan bir formüldür. Düşeyara formülü ile sütunlarda arama yaparken yatayara formülü ile satırlarda arama yaparsınız.

Aynı veriye sahip tabloların eksenlerinin değiştiğini düşünün ve yukarıdaki gibi şekillendiğini düşünün.

=YATAYARA(A4;M3:S9;7;YANLIŞ)

18- İndis formülü kullanımı

İndis (Index) bir tabloda satır ve sütun sayısını girerek kesişimindeki veriyi tespit etmek için kullanılır.

Örneğin; aşağıdaki tabloda ürün #7 nin rengi beyaz hücresini formül ile tespit ediyoruz.

Özellikle büyük satırlı ve sütunlu tablolarda hem sütunu hem de satırı seçip ortak düşen rakamı tespit etmek yerine önünüzdeki hücreye bu veriyi tek formülle getirebiliyorsunuz.

Aşağıdaki örnekte 39 fatura kesilmiş ve 300 ün üzerinde ürün var. Oluşan matris oldukça büyük.

İndis formülünün kurulumu şu şekilde;

=İNDİS(C9:AW329;45;25)

Verinin aranacağı alan : C9:AW329

Satır sayısı: 45

Sütun sayısı: 25

Bu kurulum şu şekilde işliyor;

Seçilen (C9:AW329) aralığındaki 45. Sayır ile 25. Sütunun kesiştiği hücre.

Aynı tabloda başka bir örnek daha yaptım;

Bu formülde de sonunda 1 göreceksiniz. Eğer tabloyla çalışıyorsanız 1 yazsanız da yazmasanız da aynı sonucu veriyor. Fakat iki tablo ile çalışıyorsanız ve 2 yazarsanız, 2 nolu tablodaki aynı şartları sağlayan hücreyi bulur.

İndis formülü + Kaçıncı formülü

İndis formülünün kurgusunda hatırlarsanız kaçıncı satır ve kaçıncı sütun olduğunu bizim yazmamız gerekiyordu. Her defasında büyük tablolarda bu tespitleri yapmak zaman alabiliyor. Kısaltmak için indis formülüne kaçıncı formulü ile destek veriyoruz ve o verinin kaçıncı satırda olduğunu kaçıncı formülünün bulmasını sağlıyoruz.

Örneğin;

Sadece indisi kullansaydık formül şu şekilde olacaktı

=İNDİS(C9:AW329;45;25)

Fakat biz satırları ve sütunları kendimiz aramak istemiyoruz, 45 veya 25 yazmak istemiyoruz. Onun yerine bu metin kaçıncı hücredeyse git onu yaz demek istiyoruz. Bu durumda 45 yerine ve 25 yerine kaçıncı formulünü kullanıyoruz.

Yeni formül şu şekilde oluyor;

=İNDİS(C9:AW329;KAÇINCI(G2;C9:C329;0);KAÇINCI(F3;C8:AW8;0);1)

Açıklaması ise şu şekilde;

KAÇINCI(G2;C9:C329;0)

• Aranan değer: G2 (İstediğiniz bir hücreyi seçebilirsiniz.
• Aranan dizi: C9:C329. Kullandığınız veri
• Eşleştir tür: 0. Tam eşleşme

19- Düşey ara formulü ile indis + kaçıncı formülü kombinasyonu farkları

Excel düşeyara formülü #hataları ve çalışmama sebepleri

Excel düşeyara formülü çok kullanışlı olsa da bazen ufak dikkatsizliklerden dolayı yanlış sonuç verebilir. Daha dikkatli olarak bu hataları hemen düzeltebilirsiniz. Aşağıdaki 10+ hata çıkabilecek senaryolara dikkat ettiğinizde düşeyara formülü hatalarını hemen bulur ve düzeltebilirsiniz.

Örnek çalışma dosyasını indirmek için tıklayın

20- Hücre biçimi metin vs. rakam uyuşmazlığı (#YOK hatası)

Bu, düzeltilmesi çok kolay olan bir biçimlendirme hatasıdır!

“#YOK” hatası alırsanız ve düşeyaranız için kullanılan benzersiz değerin yanında bir ünlem işareti varsa (aşağıdaki ekran görüntüsünde B2 hücresine bakın), ünlem işareti kutusuna tıklayıp “Sayıya Dönüştür” seçeneğine tıklamanız yeterlidir.

Bu, hücrenin biçimini “Metin “den “Sayı “ya değiştirecektir ve düşeyara çalışacaktır. Yine de çalışmazsa, bu sayfada ele alınan diğer #YOK hataları için okumaya devam edin.

21- düşeyara sondaki boşluklar hatası (başka bir #YOK hatası)

Bazı şirket raporlama sistemleri otomatik olarak, rapordaki her benzersiz değerin sonunda bir boşluk bulunan raporlar oluşturur ve bu da düşeyaranın başarısız olmasına neden olur. Bu, daha önce hiç karşılaşmadıysanız fark edilmesi en zor “#YOK” hatasıdır, çünkü çıplak gözle bakıldığında her şey normal “görünür”! Fakat 1 boşluk bile farklı olsa düşeyara formülü verileri eşleştiremez.

Aşağıdaki tablodaki benzersiz değerler B sütunundadır. Sıra 1 konumunda olan B2 hücresini vurguladım – araç çubuğunda, “1” sayısının C sütununun ÜSTÜNDE gösterildiği yerde, düşeyaranın başarısız olmasına ve D sütununda bir “#YOK” hatası oluşturmasına neden olan sonda bir boşluk var (kırmızı çizgiye bakın).

Bunun için bir dizi çözüm bulunmaktadır:

2a) “Veri – ‘Metni sütunlara dönüştür ” çözümü.

Araç çubuğunda “Veri “ye gidin,

Şimdi – ve bu önemli bir adımdır – arama değerlerinizin bulunduğu sütunu vurgulayın.  Bu sitedeki ana örnekte, arama değerleri B sütunundadır.

Ardından araç çubuğundaki “Sütunlara Metin” simgesine tıklayın.

Açılan pencerede “İleri “ye tıklayın (dosya türü olarak “Sınırlandırılmış” bıraktığınızdan emin olun).

Bir sonraki pencerede “Boşluk” onay kutusuna tıklayın (aşağıdaki ekran görüntüsüne bakın). “Boşluk” onay kutusuna tıkladığınızda “Ardışık sınırlayıcıları bir olarak değerlendir” onay kutusu otomatik olarak işaretlenecektir. Bu sorun değil. Aşağıdaki ekran görüntüsünde “İleri “ye tıklamanız yeterlidir. Ardından “Son “a tıklayın ve hepsi bu kadar!

2b) boşluğu kaldırmak için hücreye tıklayarak ve hücrenin sonunda “backspace” tuşuna basarak sondaki boşluğu kaldırın.

2c) hücrenin yanındaki ünlem işaretine tıklayın ve ardından “sayıya dönüştür” seçeneğine tıklayın.

22- yanlış veri aralığı seçimi sebebiyle düşeyara #YOK hatası

Eğer #YOK hatası birden çok ve sıralı ise belki de sabitlemeden veri seçiminiz kaymış olabilir. Fomülün olduğu hücreye tıklarsanız formül veriyi nereden aldığını size gösterecektir.

23- tabloda eksik veri nedeniyle düşeyara #YOK hatası

Bu durum ya aradığınız veri mevcut olmadığından (1, 3 ve 5 sayılarının ikinci tabloda yer almadığı aşağıdaki tabloya bakınız) ya da formülün gerçek değer yerine “#YOK” değerini döndürmesinden kaynaklanacaktır. İlk tabloda 1, 3 ve 5 numaralı pozisyonlardaki koşucular için para ödülünün gerçek değer yerine “#YOK” olduğunu fark edeceksiniz), bu nedenle formül aradığınız sonuç yerine “#YOK” döndürür. Eksik hücreleri doldurduğunuzda düşeyara verileri bulacaktır.

24- aranan değerin hatalı veya boş hücre olması

Aşağıdaki ekran görüntüsünde yer alan formülde, formülü görebilirsiniz

=DÜŞEYARA(M2,H:J,3,FALSE)

Birlikte bir düşeeyara oluşturan 4 farklı argümana sahiptir:

Ancak, bu formülde yanlış olan şey ‘arama değeri’dir – B2 olması gerekirken L2’dir. L2 her iki tablodan da uzakta – sarı renkle vurgulanan hücreye bakın. Dolayısıyla, bu durumda L2’yi B2 olarak değiştirmek formülün çalışmasını sağlayacaktır, yani hata formülün ‘arama değeri’ olarak da bilinen ilk kısmıdır.

25- düşeyara #YOK! hatası

Bu durum, verileri aradığınız dosya, elektronik tablo veya tablo dizisi silinmişse meydana gelecektir. Bunun bir örneği aşağıda yer almaktadır; burada H ila J sütunlarını sildim ve yukarıdaki örnekte açıklanan vlookup #YOK! hatası döndürüyor.

26- düşeyara #BAŞV! Hatası sebebi: tablo dizisi hatalı

Aşağıdaki ekran görüntüsünde, tablo dizisi 3 yerine 2 sütundan oluşmaktadır, yani H’den J’ye yerine H’den I’ya.

Formül, J sütunundaki “Para Ödülü” değerlerini çıkarmaya çalışıyor, bu nedenle formülün bu sütundaki bilgileri yakalamak için J sütununa kadar gitmesi gerekiyor!

Aksi takdirde formül çalışmayacaktır! Bu durumda I harfini J olarak değiştirmek formülün çalışmasını sağlayacaktır.

27- aranan değerin birkaç kez listelenmiş olması

– Böyle bir durumda, bunun nedeni genellikle benzersiz değerinizin referans tablonuzda birden fazla kez listelenmiş olmasıdır. Ayrıca bu değere farklı değerler atanmış olabilir. Aşağıdaki ekran görüntüsü ne demek istediğimi göstermektedir.

– “4” sayısı sağ taraftaki tabloda H sütununda iki kez görünmektedir, bu nedenle düşeyara formülü bu tabloda “4” sayısına atanan ilk değeri döndürür, bu da J sütununda 7.000 (kırmızıyla vurgulanmıştır) olur. Bu satır silinirse, düşeyara formülü bu tabloda “4” sayısına atanan bir sonraki değeri döndürür, bu da 17.000 (altta kırmızıyla vurgulanmıştır) olur.

28- hesaplama yapmıyor sadece düşeyara formülü gözüküyor

Düşeyaranız çalışmıyorsa ve formülü şu şekilde döndürüyorsa (bkz. D2 hücresi)

o zaman yapmanız gereken

– formülün çalışmadığı hücreye sol tıklayın

– sağ tıklayın ve ‘hücreleri biçimlendir’i seçin

– geçerli formatın ‘metin’ olduğunu fark edeceksiniz

– bunu ‘genel’ olarak değiştirin

– Tamam’a tıklayın

– ardından F2 tuşuna basın

– ardından ENTER tuşuna basın. enter tuşuna basmadan önce F2 tuşuna bastığınızdan emin olmanız gerekir, aksi takdirde bu işlem çalışmaz.

– Bunu yaptıktan sonra sorun çözülecektir ve gerekirse formülü sütundaki diğer hücrelere sürükleyebilirsiniz.

29- #AD? Hatası – formüldeki eksik ‘argüman’ nedeniyle

Yukarıdaki örnekte, “yanlış” yerine “yanlı” olan ve sonunda “ş” harfi bulunan son sözcük dışında formüldeki her şey düzgündür.

Bu nedenle, formüllerde sözcük kullanıyorsanız, bunları doğru yazmanız önemlidir, aksi takdirde #AD? hatası alırsınız.

30- Farklı excel versiyonlarında iki dosya ile çalışırken hata alabilirsiniz (2010 vs 2003)

– Excel 2007 veya 2010 dosya(lar)ında [2003 DEĞİL], ‘dosya’ya ve ardından ‘farklı kaydet’e tıklayın, ardından ‘farklı kaydet’ açılır penceresinin altındaki ‘farklı kaydet türü’ açılır menüsünde, dosya türünü ‘Excel çalışma kitabı (*.xlsx)’den – aşağıdaki ekran görüntüsüne bakın – ‘Excel 97-2003 Çalışma Kitabı (.xls)’ olarak değiştirin. Daha sonra aşağıdaki ikinci ekran görüntüsünde gösterilen hata mesajını alacaksınız ve bu noktada ‘evet’e tıklayacaksınız. Dosyalar arasındaki düşeyaranız artık çalışacaktır!

31- Bir sütun eklendiğinde

Sütun indis sayısı, DÜŞEYARA işlevi tarafından bir kayıt hakkında hangi bilgilerin döndürüleceğini girmek için kullanılır. Bu bir dizin numarası olarak girildiği için çok kalıcı değildir. Tabloya yeni bir sütun eklenirse, DÜŞEYARA’nızın çalışmasını durdurabilir. Aşağıdaki görüntü böyle bir senaryoyu göstermektedir.

Miktar sütun 3’teydi, ancak yeni bir sütun eklendikten sonra sütun 4 oldu. Ancak DÜŞEYARA otomatik olarak güncellenmez.

Çözüm:

1) Formüldeki sütun indis sayısını güncellediğinizde formül tekrar düzgün çalışmaya başlayacaktır.

2) Sütunu güncel tutmak için düşeyara ile birlikte sütunsay formülünü de kullanabilirsiniz.

*Bonus formüller

Satır formülü

Başvurunun satır sayısını verir.

Satırsay formülü

Bir dizi veya başvurudaki satır sayısını verir. Bir tablodaki verileri seçip satırsay formülü ile satırları saydırabilirsiniz. Tek tek numara vermez, tamamındaki satır sayısını verir.

Sütun sayısı

Başvurunun sütun sayısını verir.

Sütun formülü sütun silindiğinde kendini güncelleyen bir yapıdadır. Sütun sayılarını sütun formülü ile takip edebilirsiniz.

Sütunsay formülü

Satırsay gibi bir tablodaki başlıkların yani sütunların sayısını verir. Tek tek numara atamaz toplam sütun sayısını verir.

Sütunsay formülü düşeyara formülü ile kullanıldığında dinamik olduğundan arada sütun silindiğinde düşeyara hata vermez.

32- Tabloya yeni satır eklendiğinde

Tabloya daha fazla satır eklendikçe, bu ekstra satırların dahil edilmesini sağlamak için DÜŞEYARA’nın güncellenmesi gerekebilir. Aşağıdaki görüntüde, meyve öğesi için tüm tabloyu kontrol etmeyen bir DÜŞEYARA gösterilmektedir.

Çözüm 1

Tablo dizisini elle güncellemek.

Çözüm 2

Tablo dizisini tablo nesnesine çevimek.

Aralığı bir tablo (Excel 2007+) veya dinamik bir aralık adı olarak biçimlendirmeyi düşünün. Bu teknikler, DÜŞEYARA işlevinizin her zaman tablonun tamamını kontrol etmesini sağlayacaktır.

Aralığı tablo olarak biçimlendirmek için, tablo_dizisi için kullanmak istediğiniz hücre aralığını seçin ve Giriş > Tablo Olarak Biçimlendir’e tıklayın ve galeriden bir stil seçin. Tablo Araçları altındaki Tasarım sekmesine tıklayın ve sağlanan kutuda tablo adını değiştirin.

Düşeyara formülü artık tüm tabloya bakar ve eklenen satırları otomatik dahil eder.

Diğer yardımcı excel formülleri

Lira formülü

Metneçevir işlevlerinden biri olan LİRA işlevi, ondalıkların belirttiğiniz basamak sayısına yuvarlanarak para birimi biçimini kullanarak sayıyı metne dönüştürür.

LİRA işlevinin söz diziminde aşağıdaki bağımsız değişkenler bulunur:

• Sayı    Gerekli. Bir sayı, sayı içeren bir hücre başvurusu veya sayı hesaplayan bir formüldür.
• Onluklar İsteğe bağlı. Ondalık işaretinin sağındaki basamak sayısıdır. Bu negatifse, sayı ondalık ayırıcının soluna yuvarlanmış olur. Onluklar yoksayılırsa 2 olacağı varsayılır.

Tür formülü

Değerin tipini verir. Başka bir işlevin davranışı belli bir hücredeki değerin tipine bağlı olduğu zaman TÜR işlevini kullanın.

Rakam ve metin en çok karşımıza çıkanlar olduğu için aşağıdaki örnekleri inceleyebilirsiniz.

ESAYIYSA formülü

Bir sayı veya sayısal değer içeren bir hücreye başvuruysa DOĞRU, aksi halde YANLIŞ sonucunu döndürür.

EMETİNSE formülü

Formül, bir metin değeri veya metin değeri içeren bir hücreye başvuruysa DOĞRU, aksi halde YANLIŞ sonucunu döndürür.

EMETİNDEĞİLSE formülü

Bir metin içeren bir hücreye başvuruysa YANLIŞ, aksi halde DOĞRU sonucunu döndürür. Değer boş bir hücreye başvuru olduğunda EMETİNDEĞİLSE işlevi DOĞRU sonucunu döndürür.

EBOŞSA formülü

Hücre boşsa veya boş bir hücreye başvuruysa DOĞRU, veri veya verilere başvuru içeriyorsa YANLIŞ sonucunu döndürür.

EHATALIYSA formülü

Değer, #SAYI/0!, #YOK, #AD?,#BOŞ!, #SAYI!, #DEĞER! ve #BAŞV! dahil olmak üzere herhangi bir hataysa EHATALIYSA, DOĞRU sonucunu döndürür.

Özet olarak

Düşey ara formülü excelin en çok kullanılan 10 formülünden biri. Düşeyara uzun listelerde çok büyük bir zahmeti ortadan kaldırıp veriyi doğrudan sizin önünüze getirmesi müthiş bir kolaylık. Düşeyarasız excel düşünülemez. Siz de yukarıdaki örnekleri ve kullanımın her adımını iyice anladığınızda excelde müthiş bir hız kazanacaksınız.

Microsoft’un sitesinde düşeyara formülünü inceleyebilirsiniz.