En İyi 10 Gelişmiş Excel Formülü ve İşlevlerinin Listesi
# 1 - Excel'de DÜŞEYARA Formülü
Bu gelişmiş excel işlevi, excel'de en çok kullanılan formüllerden biridir. Esas olarak, bu formülün basitliğinden ve bu tablolarda ortak bir değişkene sahip olan diğer tablolardan belirli bir değeri aramadaki uygulamasından kaynaklanmaktadır. Bir şirketin Çalışan maaşı ve adının birincil sütun olduğu Çalışan Kimliği ile ilgili ayrıntıları içeren iki tablonuz olduğunu varsayalım. Maaşı Tablo A'daki Tablo B'den almak istiyorsunuz.
DÜŞEYARA'yı aşağıdaki gibi kullanabilirsiniz.
Bu gelişmiş excel formülünü Çalışan Maaşı sütununun diğer hücrelerine uyguladığımızda aşağıdaki tablodan sonuçlanacaktır.
Formülü hücrelerin geri kalanına sürükleyin.
DÜŞEYARA'nın üç ana sınırlaması vardır:
- Değerini başka bir tablodan doldurmak istediğiniz sütunun sağında bir birincil sütununuz olamaz. Bu durumda, Çalışan Maaşı sütunu Çalışan Kimliğinden önce olamaz.
- Tablo B'deki birincil sütunda bulunan yinelenen değerlerde, ilk değer hücrede doldurulur.
- Veritabanına yeni bir sütun eklerseniz (Ör. Tablo B'de Çalışan Maaşından önce yeni bir sütun ekleyin), formülün çıktısı, formülde bahsettiğiniz bir pozisyona bağlı olarak farklı olabilir (Yukarıdaki durumda, sonuç boş olurdu)
# 2 - Excel'de INDEX Formülü
Bu gelişmiş excel formülü, belirli bir tablodaki bir hücrenin değerini satırların, sütunların veya her ikisinin sayısını belirterek elde etmek için kullanılır. Örn. Beşinci gözlemde bir çalışanın ismini almak için , veriler aşağıdadır.
Gelişmiş excel formülünü aşağıdaki gibi kullanabiliriz:
Aynı INDEX formülü, satır boyunca değer elde etmek için kullanılabilir. Hem satır hem de sütun numarasını kullanırken, sözdizimi şuna benzer:
Yukarıdaki formül "Rajesh Ved" döndürür.
Not: 5. satıra başka bir satır eklerseniz formül "Chandan Kale" yi döndürür. Bu nedenle çıktı, veri tablosunda zaman içinde meydana gelen herhangi bir değişikliğe bağlı olacaktır.
# 3 - Excel'deki MATCH Formülü
Bu Excel gelişmiş formülü, verilen aralıkta belirli bir dize veya sayı olduğunda satır veya sütun numarasını döndürür. Aşağıdaki örnekte, Çalışan Adı sütununda "Rajesh Ved" bulmaya çalışıyoruz.
Formül aşağıda verildiği gibi olacaktır:
KAÇINCI işlevi, değer olarak 5 döndürür.
3 rd argümanı tam eşlemesi için kullanılmaktadır. Gereksinimlerinize göre +1 ve -1 de kullanabilirsiniz.
Not: DÜŞEYARA sınırlamasının üstesinden gelmek için INDEX ve MATCH birleştirilebilir.
# 4 - Excel'de EĞER VE Formül
Bazı kısıtlamalara göre bayrakların oluşturulması gereken birçok durum vardır. Hepimiz IF'nin temel sözdizimine aşinayız. Bu gelişmiş excel IF işlevini, mevcut bir alandaki bazı kısıtlamalara göre yeni bir alan oluşturmak için kullanıyoruz. Ama ya bir bayrak oluştururken birden fazla sütun kullanmamız gerekirse? Örn. Aşağıdaki durumda, maaşı 50.000'den yüksek ancak çalışan kimliği 3'ten büyük olan tüm çalışanları işaretlemek istiyoruz.
Bu gibi durumlarda IF AND kullanırdık. Lütfen aynı ekran görüntüsünü aşağıda bulabilirsiniz.
Sonucu 0 olarak döndürecektir.
AND kullanarak birden çok sütuna dayalı bir bayrak oluşturmak için birçok koşulumuz veya kısıtımız olabilir.
# 5 - Excel'de EĞER VEYA Formülü
Benzer şekilde, birçok koşuldan birini yerine getirmemiz gerekirse, OR işlevini AND yerine excel'de de kullanabiliriz.
Yukarıdaki durumlarda, bir koşuldan herhangi biri yerine getirilirse, hücreyi 1 veya 0 olarak doldururuz. Ayrıca 1 veya 0'ı çift tırnaklı (“”) bazı alt dizelerle değiştirebiliriz.
# 6 - Excel'de SUMIF Formülü
Bazı analizlerde, toplama veya sayma işlevini uygularken bazı gözlemleri filtrelemeniz gerekebilir. Bu gibi durumlarda, excel'deki bu gelişmiş excel SUMIF işlevi kurtarmamızdadır. Bu gelişmiş excel formülünde verilen belirli koşullara göre tüm gözlemleri filtreler ve özetler. Örn. Ya sadece 3'ten büyük çalışan kimliği olan çalışanların maaşlarının toplamını bilmek istiyorsak.
SUMIFS Formülünü Uygulayarak:
Formül, sonuçları 322000 olarak döndürür.
SUMIF yerine COUNTIF kullanırken, organizasyonda 3'ten büyük çalışan kimliği olan çalışanların sayısını da sayabiliriz.
# 7 - Excel'de Formülü BİRLEŞTİR
Bu excel gelişmiş işlevi, birden çok varyantla kullanılabilen formüllerden biridir. Bu gelişmiş excel formülü, birkaç metin dizesini tek bir metin dizesinde birleştirmemize yardımcı olur. Ör. Çalışan Kimliği ve Çalışan Adını tek bir sütunda göstermek istiyorsak.
Bunu yapmak için bu CONCATENATE formülünü kullanabiliriz.
Yukarıdaki formül "1Aman Gupta" ile sonuçlanacaktır.
ID ve NAME arasına tek bir tire koyarak bir varyant daha elde edebiliriz. Örn. CONCATENATE (B3, "-", C3) "1-Aman Gupta" ile sonuçlanacaktır. Excel değerindeki ARA, birden fazla değişkenin karışımı olduğunda bunu DÜŞEYARA işlevinde de kullanabiliriz.
# 8 - Excel'de SOL, ORTA ve SAĞ Formül
Bu gelişmiş excel formülünü, belirli bir dizeden belirli bir alt dizeyi çıkarmak için kullanabiliriz. Gereksinimlerimize göre kullanılabilir. Örn. Çalışan Adı'ndan ilk 5 karakteri çıkarmak istersek, Excel'de sütun adı ve ikinci parametre 5 olmak üzere SOL formülünü kullanabiliriz.
Çıktı aşağıda verilmiştir:
RIGHT formülünün Excel'deki uygulaması da aynıdır. Sadece karaktere dizenin sağından bakıyor olurduk. Ancak, excel'de bir MID işlevi olması durumunda, gerekli metin dizesinin başlangıç konumunu ve dizenin uzunluğunu vermeliyiz.
# 9 - Excel'de OFSET Formülü
TOPLA veya ORTALAMA gibi diğer işlevlerin bir kombinasyonu ile bu gelişmiş excel işlevi, hesaplamalara dinamik bir dokunuş sağlayabilir. En iyi, mevcut bir veritabanına sürekli satırlar eklediğimiz durumlarda kullanılır. OFSET Excel bize referans hücre, satır sayısı ve sütunlardan bahsetmemiz gereken bir aralık sağlar. Örn. Çalışan kimliğine göre sıralanmış çalışanlarımızın maaşının olduğu şirkette ilk 5 çalışanın ortalamasını hesaplamak istersek, aşağıdakileri yapabiliriz. Aşağıdaki hesaplama bize her zaman maaşlı ödeme verecektir.
- Bize ilk 5 çalışanın maaşlarının toplamını verecek.
# 10 - Excel'de TRIM Formülü
Bu gelişmiş excel formülü, metindeki önemsiz boşlukları temizlemek için kullanılır. Örn. Bir ismin başındaki boşlukları kaldırmak istersek, bunu excel'de aşağıdaki gibi TRIM fonksiyonunu kullanarak kullanabiliriz:
Ortaya çıkan çıktı, Chandan'dan önce boşluksuz "Chandan Kale" olacaktır.
