Sürekli kullanım ile Microsoft Excel izlemek veya bütçeleme Veya envanter, muhtemelen mevcut işlevler ve bunların formülleri hakkında çok şey öğreneceksiniz. Ancak yeni bir şey keşfetmek kafa karıştırıcı veya göz korkutucu olabilir. Bu kılavuz, yeni alanlara dalmaya hazır olduğunuzda kullanabileceğiniz birkaç yararlı işlevi ve bunların formüllerini sunar. İşte bilmeniz gereken 5 kullanışlı gelişmiş Excel formülü.
1. E-tablonuzdaki değerleri bulun: XLOOKUP
Büyük bir e-tablodaki diğer verilere dayalı verileri aramak istediğinizde XLOOKUP, başvuracağınız işlevdir. ondan başka VLOOKUP Yalnızca soldan sağa değerleri arayabilen XLOOKUP, soldan sağa veya sağdan sola değerleri arayabiliyor, bu da onu daha esnek bir araç yapıyor.
Not: Bu yazı itibariyle, XLOOKUP yalnızca Microsoft 365 aboneleri tarafından kullanılabilir.Diğer Excel sürümleri için, sonraki bölümde DİZİN ve KAÇINCI birleşimine bakın.
Fonksiyon formülü:
XLOOKUP(değer_arama, arama_aralığı, dönüş_aralığı, bulunamadı_bulunma_modu, arama_modu)
Yalnızca ilk üç bağımsız değişken gereklidir. Aşağıda listelenen her bağımsız değişkenin açıklamasıdır:
- Arama_değeri: Aranacak değer.
- Arama_aralığı: Aranacak değeri içeren aralık.
- Dönüş_aralığı: Döndürülecek değeri içeren aralık.
- Bulunamadı: Değer bulunamazsa döndürülecek metin. "#N/A" atlanırsa varsayılan değerdir.
- Maç_modu: Tam eşleşme için "0" ve bulunamayanlar için "#N/A" (atlanırsa varsayılan), tam eşleşme için "1" ve hiçbiri bulunamazsa sonraki daha küçük öğe, tam eşleşme ve daha büyük için "-1" kullanan eşleme türü Bulunamadıysa Sonraki öğesi veya soru işareti, yıldız işareti veya dalga işareti kullanan bir joker karakterle eşleştirmek için '2'.
- Arama modu: İlk öğeden başlamak için "1" (atlanırsa varsayılan), son öğeden başlamak için "-1", "arama_aralığı" artan sırada "2" veya "arama_aralığı" olduğunda "-2" ile arama modu azalan düzen.
Örneğin, 2. segment için satış arıyoruz, bu nedenle aşağıdaki formülü kullanıyoruz:
=XLOOKUP(2,A19:A24,D19:D24)
Formülü parçalara ayırırsak, 2 "aranan_değer", A19:A24 "aranan_aralık" ve D19:D24 "dönüş_aralığı" bağımsız değişkenidir.
Sonuç olan 74000, sektör 2 için doğru karşılık gelen değerdir.
Bir hücrede sabit yerine bir değer aradığınız ve ardından o hücredeki değeri her değiştirdiğinizde güncellenmiş sonucu aldığınız daha dinamik bir şey istiyorsanız, aşağıdaki örneğe bakın:
"Aranan_değer" 2'yi F19 hücresiyle değiştiriyoruz:
=XLOOKUP(F19,A19:A24,D19:D24)
F2 hücresine 19 girdiğimizde 74000 sonucunu alıyoruz ve 5 gibi farklı bir değer girersek otomatik olarak güncellenen sonucu yani 61000'i alıyoruz.
Argümanı dahil edebilirsiniz "bulunamadı" Böylece, eşleşme bulunamazsa bir hata mesajına bakmanıza gerek kalmaz. Argümana "yanıt verme" ifadesini ekliyoruz.
=XLOOKUP(F19,A19:A24,D19:D24,"No dice")
F19 hücresine eşleşmeyen bir değer girildiğinde "#NA" yerine mesajınızı görürsünüz.
2. Bir sayfada alternatif değeri bulun: INDEX ve MATCH
XLOOKUP size değerleri aramak için güçlü bir yol sunarken, şu anda yalnızca Microsoft 365 Excel aboneleri tarafından kullanılabilir. Excel'in farklı bir sürümünü kullanıyorsanız, aynı şeyi yapmak için INDEX ve MATCH'ı kullanabilirsiniz.
İNDİS fonksiyonu ile formülde girdiğiniz konuma göre hücredeki değer döndürülür. INDEX formülü:
DİZİN(aralık; satır_numarası; sütun_numarası)
KAÇINCI işlevi ile formülde girdiğiniz değerin konumu döndürülür. KAÇINCI formül:
MATCH(değer; aralık; eşleşme_türü)
Bu iki işlevi ve formüllerini birleştirmek için, KAÇINCI formülünü INDEX formülüne arama yeri olarak (“satır_numarası” ve “sütun_numarası”) koyun.
Yukarıdaki XLOOKUP örneğindeki verilerin aynısını kullanarak, F19 hücresine girdiğimiz segment satışlarını görmek istiyoruz. Formül:
=INDEX(D19:D24,MATCH(F19,A19:A24))
Bu formülü parçalara ayırmak için, INDEX ve onun "aralık" bağımsız değişkeni olan D19:D24 ile başlıyoruz. Bu, istediğimiz sonucu içeren aralıktır.
KAÇINCI formülü, "değer" bağımsız değişkeni olarak F19'u ve bu değeri içeren "aralık" bağımsız değişkeni olarak A19:A24'ü kullanır.
F4 hücresine 19 girdiğimizde doğru olan 75000 sonucunu alıyoruz.
F19 hücresine 6 gibi farklı bir sektör girersek, formül otomatik olarak 58000 doğru değerini verecek şekilde güncellenir.
3. KRİTER İLE TOPLA VEYA SAY: SUMIF, SUMIFS, COUNTIF, COUNTIFS
Excel'deki TOPLA ve SAY işlevleri, sayıları toplamak ve hücreleri saymak için tasarlanmıştır, ancak basit matematik işlemleriyle sınırlıdır. Bu işlevlerdeki varyasyonlar, ölçüt kullanarak toplamanıza veya saymanıza olanak tanır. Örneğin, yalnızca 12'den büyük sayıları ekleyebilir veya Bill adını içeren hücreleri sayabilirsiniz.
ETOPLA ve EĞERSAY ile bir koşulla toplayabilir veya sayabilirsiniz ve ÇOKETOPLA ve EĞERSAY ile birden fazla koşulla toplayabilir veya sayabilirsiniz. Her biri için sözdizimi aşağıda listelenmiştir:
SUMIF
ETOPLA(aralık; ölçüt; toplam_aralık)
COUNTIF
EĞERSAY(aralık; koşul)
ÇOKETOPLA
ÇOKETOPLA(toplam_aralık, koşul_aralığı1, koşul1, koşul_aralığı2, koşul2,…)
COUNTIFS
EĞERSAY(koşul_aralığı1, koşul1, koşul_aralığı2, koşul2,…)
Her işlevin bir örneği için aşağıya bakın.
2'den büyük sayılar için yalnızca B7 ila B12 aralığımızdaki sayıları toplamak için TOPLA işlevini ve şu formülü kullanırız:
=TOPLA(B2:B7;">12")
Sonuç 31'dir, çünkü formül veri kümesinde 16'den büyük olan yalnızca iki sayı olan 15 ve 12'i toplamıştır.
Aşağıdaki örnekte, B12 ile B2 arasındaki aynı aralıkta 7'den küçük değerlere sahip hücreleri aşağıdaki formülle sayıyoruz:
=EĞERSAY(B2:B7;"<12")
Bu formülün sonucu 4'tür, çünkü bu, aralıktaki 12'den küçük değerlere sahip hücre sayısıdır.
ÇOKETOPLA ile ele alalım ve iki koşul kullanalım. B2 ila B7 hücrelerindeki sayıları yalnızca C2 ila C7 hücrelerinde haber bültenimize (Y) abone olanlar ve D2 ila D7 hücrelerinde şehri San Diego olan kişiler için eklemek için aşağıdaki formülü kullanırız:
=SUMIFS(B2:B7,C2:C7,"Y",D2:D7,"San Diego")
Bu formülün sonucu 19'dur, çünkü yalnızca iki müşteride haber bülteni için Y ve şehir için San Diego vardır. Bill Brown'ın 11'i ve Sue Smith'in 8'i olmak üzere toplam 19'u var.
COUNTIFS işlevini kullanan bir örnek için, yukarıda açıklanan aynı iki koşula sahip hücrelerin sayısını sayarız. Haber bültenine (Y) abone olan müşterilerin sayısını ve onların San Diego şehrini sayıyoruz.
=EĞERSAY(C2:C7;"Y";D2:D7;"San Diego")
Beklendiği gibi, San Diego'nun tek haber bülteni aboneliği olan Bill Brown ve Sue Smith için puan 2 oldu.
4. Verileri farklı koşullar altında test edin: IFS
Excel'in EĞER işlevi, verileri koşullara göre test etmek için güçlü bir araçtır. Örneğin, bunu bir öğrencinin sayısal puanı için bir harf notu veya satış görevlisi ikramiyesini almaya yetecek kadar gelir getirdiyse "evet" ve getirmediyse "hayır" olarak görüntülemek için kullanabilirsiniz.
EĞER işleviyle ilgili sorun, verilerinizi birden çok ölçüte göre test etmek için tüm EĞER deyimlerini bir araya getirmeniz gerekmesidir. Bu grup karışıklığını gidermek için IFS işlevini kullanın.
Yukarıdaki ÇOKETOPLA ve EĞERSAY'a benzer şekilde, IFS, formülünüze net ve okunması kolay bir şekilde birden çok ölçüt eklemenizi sağlar.
Sözdizimi, 1 adede kadar farklı kıyaslamayı test edebileceğiniz IFS(test1, if_test2_true, test2, if_test127_true, …) şeklindedir.
Örnek senaryolarımızdan birini kullanarak, ödüllerden bahsetmeye başladık. B2 hücresindeki miktar 20000'den büyükse, "Evet". Değilse, göster "hayır". Formül şöyle olur:
=IFS(B2>20000,"Evet",B2<20000,"Hayır")
İlk testte B2'deki değer 20000'den büyük ise true ise sonuç “evet” olur. İkinci testte B2'deki değer 20000'den küçükse sonuç true ise "hayır".
Neyse ki satış elemanımız ödülünü aldı. 21000 adet satışı olduğu için girildi. "Evet" bonus sütununda.
Örneğimiz gibi bir listeniz varsa formülü kopyalayın. Hücrenin sağ alt köşesindeki doldurma tutamacını diğer satış görevlilerinin kalan hücrelerine sürükleyin. Formül, farklı hücre referanslarını barındıracak şekilde otomatik olarak güncellenir.
IFS'den başka bir örnek için, haftanın her günü için bir görev yöneticileri listemiz var. D2 hücresinde geçerli gün görüntülendiğinde, ilgili yöneticinin adı E2 hücresinde görüntülenir. Formül şöyle olur:
=IFS(D2="Monday",B2,D2="Tuesday",B3,D2="Wednesday",B4,D2="Thursday",B5,D2="Friday",B6)
Bu, D2 hücresindeki değer Pazartesi ise, adı B2 hücresinde görüntüle, D2 hücresindeki değer Salı ise, B3 hücresindeki adı görüntüle ve D2 hücresindeki değer Çarşamba ise, hücredeki adı görüntüle B3 vb.
D2 hücresinde haftanın gününü değiştirdiğinizde, E2 hücresinde görevli yöneticinin ilgili adı görüntülenir.
Formülü önceden oluşturmak için az miktarda çalışmayla, Excel sayfanızda bazı otomasyonların meyvelerini toplayabilirsiniz.
5. Birden çok kriter kullanarak filtreleyin: filtreleyin
Bir Excel kullanıcısı olarak, uygulamanın yerleşik bir filtre özelliği ile geldiğini muhtemelen biliyorsunuzdur. Ancak, verilerinizi bir koşul veya hatta birden çok ölçüt kullanarak filtrelemek istiyorsanız, filtre işlevini kullanmanız gerekecektir. Bu esnek işlevsellik ve bağımsız değişkenleri, büyük veri kümelerini yalnızca birkaç dakika içinde daraltmanıza yardımcı olabilir.
İşlevin sözdizimi FILTER(aralık, aralık=ölçüt, if_empty) şeklindedir; burada veri kümesinin yalnızca ilk iki bağımsız değişkeni artı ölçüt ve kapsayıcı aralığı gerekir. Üçüncü bağımsız değişken, özellikle formül sıfır sonucu döndürmezse, örneğin bir şeyi döndürmek istiyorsanız kullanışlıdır. "Veri yok" أو "yok bir şey".
Birden çok ölçüt eklemeden önce FİLTRE işlevinin tek bir koşulla nasıl çalıştığını bilmek önemlidir. Örneğin, Harold Hill'in A2'den C9'a kadar olan hücrelerindeki verileri B2'den B9'a kadar filtreleyebilirsiniz. Bunun formülü:
=FİLTRE(A2:C9,B2:B9="Harold Tepesi")
Harold Hill için iki sonucumuz var. kolay değil mi?
Formüle daha fazla terim ekleyelim. VE için yıldız işareti (*) ve OR için artı işareti (+) kullanılarak birden çok kriter eklenir.
Örneğin, ikisini de filtreliyoruz Harold Tepesi و Elektronik aşağıdaki formülü kullanarak:
=FILTER(A2:C9,(B2:B9="Harold Hill")*(A2:A9="Electronics"))
Formülü bölmek için, A2:C9 veri kümesidir, B2: B9 = "Harold Tepesi" ilk koşuldur ve yıldız işareti AND'yi temsil eder ve A2: A9 = “Elektronik” ikinci koşuldur.
Filtremizle, formülün Harold Hill ve Electronics olmak üzere iki koşulla eşleşmesi gereken bir sonuç aldık.
Başka bir örnekte, giysilere veya arabalara göre filtreleme yapabilirsiniz:
=FILTER(A2:C9,(A2:A9="Apparel")+(A2:A9="Automotive"))
Bu formüle göre bölerek, A2:C9 veri kümesidir, A2: A9 = "Kıyafet" ilk koşuldur ve artı işareti OR'yi temsil eder ve A2: A9 = "Otomatik" ikinci koşuldur.
Bu sefer, formülün iki koşuldan biriyle eşleşmesi gereken iki sonuç aldık - her ikisiyle değil.
Sık sorulan sorular
S1: Bir işlevin formül yapısını doğrudan Excel'de nasıl bulabilirim?
Cevap: Excel, ihtiyacınız olan işlevselliği bulmak için yerleşik bir araç sağlar. Bununla, o işlevin sözdiziminin sözdizimini görebilirsiniz.
Boş bir hücre seçin, Formüller sekmesine gidin ve şeridin sol tarafındaki İşlev Ekle'yi seçin. İşi üstteki arama kutusuna yazın ve Git'e tıklayın. İstediğiniz işlevi gördüğünüzde, açıklamasını ve sözdizimini kutunun altında görmek için onu seçin.
S2: Düzgün çalışmayan bir formülde nasıl hata ayıklayabilirim?
Cevap: Karşılaştığınız formülü seçin ve Formüller sekmesine gidin. Şeridin Biçim Denetimi bölümünde "Biçim Değerlendirmesi"ni seçin.
Formülünüzü, Formülü Değerlendir penceresinde altı çizili olarak göreceksiniz. Altı çizili kısmın hesaplanmasını görmek için "Değerlendir"e tıklayın. Formülün her bölümünden sonuca gitmek için bu işleme devam edin. Bu, formülün nasıl işlendiğini ve hatanın nerede olabileceğini görmenizi sağlar.
S3: Neden bir formül hatası aldığımı nasıl anlarım?
Cevap: Oluşturduğunuz bir formülde hata gördüğünüzde daha fazla bilgi almak için iki şey yapabilirsiniz. Önce, hücrenin yanında görünen hata düğmesini tıklayın, ardından görüntülenen kenar çubuğunda Microsoft'tan daha fazla ayrıntı almak için Bu Hatayla İlgili Yardım'ı seçin.
Alternatif olarak, yukarıda gösterilen formül değerlendirme penceresini açmak için Hesaplama Adımlarını Göster'i seçin. Hesaplamanın sonucunu ve hatanın nedenini belirlemenize yardımcı olacak şekilde oraya ulaşma sürecini göreceksiniz.
