REFERANS FORMÜLLERİ MATCH (KAÇINCI)

MATCH (KAÇINCI) fonksiyonu tanımladığımız alanda (aynı düzlemdeki, satır veya sütun) aramak istediğimiz değerin kaçıncı sıradaki veri olduğu bilgisini döndürür.

yukarıdaki örneğimizde "İzmir" verisi C2:G2 dizininde kaçıncı veri olduğunu öğrenmek istedik ve bize 3 sonucunu döndürdü , alanı C2:G2 yerinde D2:G2 olarak tanımlasak 2 sonucunu alırdık.

Alan tanımını C2:G7 olarak yapsa idik hata alırdık formül ya aynı satır ya da aynı sütun düzleminde çalışır.

Örnekte İzmir'i arattığım için yazarken "" içinde yazdım bilgisayar ayarlarınızın aksi olmadığı durumlarda sabitler daima çift tırnak içinde yazılır bu excel formül kullanımının olmazsa olmaz kurallarındandır.

Formülün ensonundaki match type ise eşleşme türüdür birebir aramak istediğim veriyi bulmak istiyorsam buraya "0" yazmalı veya eact match özelliğini seçmeliyim eğer aradığım değer rakam ise o zaman büyük veya küçük seçeneklerini kullanabilirim tabi lazımsa :)

REFERANS FORMÜLLERİ INDEX (İNDİS)

İndex formülünün offset formülünden en önemli farkı offset formülünde referans noktası bir hücredir (bir hücre adresi) ve bu referans noktasından istediğimiz sütun veya sütun kadar uzak mesafedeki hücre değerini bulmakkken indexde bizim istediğimiz alan içerisinde hareket etmesini sağlarız.İndex de alan kısıtı vardır ve sütun satır numaraları "-" (Eksi) değer olarak kullanılamaz. aşağıdaki örneklerde konuyu anlamanız daha kolay olacaktır.

=INDEX(Array,rownum;column num)  formülü kullanırken önce alanı belirtiriz belirttiğimiz alanın sol üst köşesindeki hücre referans noktasıdır, sonra da bu noktadan kaç satır aşağı ve kaç sütun sağa gideceğimizi belirtiriz.

yukarıdaki örnekte kırmızı çizgi ile index formülüne hareket edeceği alanı belirttik ve sol üst köşe yani "B2" hücresi referans noktamızdır.Referans noktası (1,1) dir yani seçili alandaki birinci satır ve birinci sütunun kesişme noktasıdır, dolayısı ile referans noktasından iki satır aşağı demek Ocak verisinin olduğu satır ikinci sütun ise Ankara verisinin olduğu sütundur.

Seçmiş olduğumuz alan 6 Satır ve 6 sütundan oluşmaktadır.Biz "Adana" nın Mayıs ayındaki verisini bulmak istersek formülümüzü =INDEX(B2:G7:6;6) olarak yazmamız gerekirdi.

Formülümüzü bu alanın dışında tanımlamaya çalışırsak örneğin;=INDEX(B2:G7:7;8)   #REF!  sonucu alırız çünkü yukarıda da bahsettiğim gibi seçili alanımız 6 satır ve 6 sütundan oluşmaktadır bu alanın dışında bir değer bulamayız index formülü ile.

İndex,match ve offset formülleri birlikte kullanıldığında son kullanıcılar için anlam kazanır MATCH fonksiyonunu anlattıktan sonra bu tarz örnekler paylaşacağım sizlerle.

REFERANS FORMÜLLERİ OFFSET(KAYDIR)

Vlookup (Düşeyara) 'nın yetersiz kaldığı yerde referans formülleri Offset,Match,index,choose formülleri kullanılır.

Offset formülünün çalışma mantığı bir referans noktasından (Hücre) kaç satır aşağıya/yukarıya kaç sütun sağa/sola gidileceği esasına dayanır.formülün içeriğine bakarsanız bir de yükseklik ve genişlik ister ancak bu özellik sadece offset formülünü başka formüllerle kullanacağımız zaman lazım olur.

referans noktasından üst satırlara doğru gitmek istersek "-" rakam, alt satırlara doğru gitmek istersek sadece rakam girmemiz gerekir, referans noktasından soldaki sütunlara gitmek istersek "-" rakam, sağdaki sütunlara gitmek istersek ise "+" koymaksızın sütun sayısını belirtmemiz yeterlidir.

örneğin yukarıdaki örnek tabloda =OFFSET(C3;1;0) c3 hücresinin bulunduğu sütunda kal ve 1 birim alt satırdaki veriyi bul getir demektir ki sonuç "ŞUBAT" olacaktır.

=OFFSET(E7;-1;1) örneğinde ise E7 hücresinden bir satır yukarı çık ve bir sütun sağa git ve oradaki değeri göster demektir ve "2.303" değerini gösterir.

Biraz daha komplex örneklere devam edelim;

Örneklere bakarsanız Sum ve offsetin bir arada kullanıldığı 3 farklı versiyon yeralmaktadır.

Toplam formülü ile birlikte yüksekli ve derinlik özelliğini de kullanalım;

örnekleri sade yapmaya çalıştım anlamanız kolay olsun diye.

ORTALAMA (AVERAGE) FORMÜLLERİ

Ortalama ile ilgili formüller;
ORTALAMA (AVERAGE) : Bu formül seçili alanda (veya formülde belirtilen sınırlar arasındaki) yer alan rakamsal (numeric) değerlerin ortalamasını alır.
Seçili alandaki rakamsal değerlerin toplamının seçili alandaki rakamsal değerlerin adedine bölümüdür.
ORTALAMAA (AVERAGEA) : Bu formül seçili alanda yer alan rakamsal olan olmayan tüm değerlerin ortalamasını alır.
Seçili alandaki rakamsal değerlerin toplamının seçili alandaki dolu hücrelerin adedine bölümüdür.Örneğimize bakarsanız iki formül arasındaki farkı görebilirsiniz.
EĞERORTALAMA (AVERAGEIF) : Bir kritere bağlı olarak istediğimiz alandaki değerlerin ortalamasını hesaplamamızı sağlar
ÇOKEĞERORTALAMA (AVERAGEIFS) : Birden farklı kritere göre seçili alandaki rakamsal değerlerin ortalamasını getirir.
VSEÇORT(DAVERAGE) : İstenilen kritere göre ortalama hesaplar.Kriterlerin data deseni seçili alanla aynı olmalıdır. Database formülleri pek kullanmadığımız ama sınırsız kriterde işlemler yapabileceğimiz formüllerdir. Bu formüllerin kullanımında dikkat edilmesi gereken kurallar vardır örneğin bu formülleri kullanırken işlem yapacağımız tablomuzda mutlaka ilk satırda sütun isimlerinin olması gerekir.Kriter alanımızda da bu sütun başlıklarının birebir aynı isimde olması gerekmektedir. işlem yapacağımız sütun örneğin tutar,adet vs formül içine "" (çift tırnak işareti) ile yazılmalı veya referans adresi girilmelidir.
ORTSAP(AVEDEV) : Seçili alandaki rakamsal değerlerin ortalama sapmasını hesaplar

HLOOKUP (YATAYARA)

HLOOOKUP (YATAYARA) 

Vlookup'ın tam tersi olan bu formülde dikey yerine yatay olarak arama yapılır ve eşlenik getirilir.

Yukarıdaki örnekde Personel kelimesi b2:d2 alanında aranır ve bulunduktan sonra 4.satırdaki veriyi getirir.

VLOOKUP (DÜŞEYARA)

VLOOOKUP (DÜŞEYARA) Bence excelin icat ettiği formüller içinde en faydalı Top Ten listesindeki formüllerden biri.İnanın bu formülü ilk öğrendiğimde exceli yeniden keşfettiğimi düşünmüştüm ( Bu çok uzun yıllar önceydi :). Beni tanımayanlar için ilerleyen zamanlarda excelde kod yazmayı anlattığımda excelin gerçek keşfini de anlatacağım. Icebergin suyun altındaki kısmını tanıtmaya çalışacağım, benim gibi 25 yıllık iş hayatında sürekli excelle çalışan (ya da excelle çalışmak isteyenler ) için atlanılan bir seviyedir VLOOKUP . Öncelikle vlookup ne işe yarar özellikle unique (tekrarlanmayan veriler) in olduğu alanlarda binlerce satırda dahi istediğimiz kriterdeki veriyi bulup onun sağındaki istediğimiz kaçıncı sütuna denk gelen hücredeki veriyi bulmamızı sağlar. Verilerimiz tekrar eden datalardan oluşuyorsa; ki burası önemli ,belirttiğimiz kriterdeki ilk kayıtın sağındaki istediğimiz kolonda yer alan veriyi getirir.

Vlookup(ArananDeğer;Alan;Sıra;Yönerge) Hangi değeri bulmak istiyorsak onu giriyoruz benim örneğimde F2 Yani Ali bulmak istediğimiz değerin formatı text olmalıdır (Bu önemli yoksa formül çalışmaz) Arama yapmak istediğimiz alanı seçiyoruz B2:D9. Bu formülün en önemli özelliklerinden biri ,arama yapmak istediğimiz alan arama yapmak istediğimiz kriterin olduğu kolondan başlamalı daha sonra istediğimiz değeri bulduğunda bu değerin sağından kaçıncı sütundaki hücredeki veriyi getireceğini belirtiriz en sonda da benim örneğimde DOĞRU yazıyor,rakamla yazsam 0 veya 1 yazmalıydım 0 veya 1 yazmak veya YANLIŞ veya DOĞRU yazmak arasındaki fark şu arattığınız kriterin birebir aynısını getirmek için 0 veya YANLIŞ arattığımız kriterin hücrenini çinde geçmesi yeterli diyorsanız 1 veya DOĞRU seçmeli veya yazmalısınız.Örneğimde göreceğiniz gibi arattığım Hasan birinci örnekte DOĞRU ‘yu seçtim kriterim Hasan olmasına rağmen HasanAli'nin sağındaki hücredeki değeri getirdi çünkü içinde Hasan geçen ilk kaydı buldu.İkinci örnekte ise YANLIŞ' ı seçtim ve birebir Hasan olan verinin sağındaki hücredeki değeri gösterdi.Ancak içinde Hasan geçen bir veri olmasa idi excel ona yakın olduğunu düşündüğü bir değer getirir.

DCOUNT ve COUNTIF Fonksiyonları

COUNTIF (EĞERSAY) Tanımladığımız alanda istediğimiz kriterde kaç adet kayıt olduğunu sayar.Örneğimizde görüleceği üzere belirttiğimiz alan da kaç hücrede "Hasan"  verisi olduğunu sayar.Bu yazımda dikkat edilmesi gereken tırnak içinde yazdığımız kriter yerine , bir hücreyi de referans gösterebiliriz.

COUNTIFS (ÇOKEĞERSAY) Tanımladığımız alanda istediğimiz kriterlerde kaç adet kayıt olduğunu sayar.Örneğimizde Hasan'ın Marmara bölgesinde yaptığı satış adedi formülüze edilmiştir.Ben iki kritere göre saydırdım ancak siz onlarca kriter için yapabilirsiniz.

DCOUNT (VSEÇSAY) Tanımladığımız alanda (Kriter belirleyeceğimiz tüm alanlar seçili olmalı) İstediğimiz kriter/Kriterlere göre kaç adet veri olduğunu sayar.Burada dikkat edilmesi gereken ; saymak istediğimiz alan numeric veriden oluşmalı ve saymak istediğimiz alanla ilgili kriter belirlememiş olmamızdır.

DCOUNTA (VSEÇSAYDOLU) Tanımladığımız alanda (Kriter belirleyeceğimiz tüm alanlar seçili olmalı) Kullanım şekli DCOUNT la aynı olmasına rağmen E9 hücresine girdiğim değere ve iki formülün sonuçlarına bakarsanız farkı anlarsınız. DCOUNTA formülünde saymak istediğimiz alandaki verilerin numerik olması şart değildir text verisi de olsa sayar (boş olmaması kaydı ile)