Excel Formülleri Hile Sayfası: Orta Düzey Kılavuz

Demek Excel'in temellerinde ustalaşmayı başardınız - tebrikler! Şimdi, işleri bir adım öteye götürmeye ve daha gelişmiş şeylere dalmaya karar verdiniz, değil mi? Pekala, şanslısınız çünkü tam size göre kullanışlı bir ara Excel kopya kağıdı hazırladık!

Bu Microsoft Excel ara kopya sayfası, sayısal analiz, metin işleme ve karmaşık tarih ve saat mantığındaki becerilerinizi geliştirecek çok çeşitli konuları kapsar.

Excel Ara Hile Sayfasını bir masaüstü referansı olarak alarak, Microsoft Excel'in daha güçlü formüllerine ve işlevlerine derinlemesine dalabilirsiniz.

Şimdi, okumaya devam ettiğinizden emin olun, ancak aşağıdaki kopya kağıdını da yazdırın ve kaydedin!

Başlayalım!

Excel-formulas-cheat-sheet-intermediate-1 İndir

İçindekiler

• Orta Düzey Matematik Formülleri
  • Koşullu Mantık
• Ara İstatistiksel Formüller
  • 1. MINA ve MAXA
  • 2. COUNTA ve COUNTIF
• Finansal Analiz için Excel Formülleri
  • 1. ÜRÜN İşlevi
  • 2. BÖLÜM ve GÜNLÜK İşlevleri
  • Yatırım Senaryoları için Fonksiyonlar
• Ara Excel Tarih ve Saat Formülleri
  • 1. Ara Ay Fonksiyonları
  • 2. Ara Hafta Fonksiyonları
  • 3. Ara Zaman Fonksiyonları
• Koşullu ve Mantıksal Cebir için Excel İşlevleri
  • 1. EĞER İşlevi
  • 2. VE İşlevi
  • 3. VEYA İşlevi
  • 4. DEĞİL İşlevi
• Başvuru Hücreleri
  • 1. DOLAYLI İşlev
  • 2. DİZİN İşlevi
  • 3. OFSET İşlevi
• Ara Metin Formülleri
  • 1. METİN BİRLEŞTİRME İşlevi
  • 2. DEĞİŞTİR İşlevi
  • 3. İKAME Fonksiyonu
• Ara Arama Formülleri
• Microsoft Excel Hatalarını İşleme Formülleri
  • 1. IFERROR İşlevi
  • 2. IFNA İşlevi
• Formül ve İşlev Kısayolları
• Ara Hücre Biçimlendirmesi
• Son düşünceler

Orta Düzey Matematik Formülleri

Acemi kopya sayfamız, TOPLA işlevi gibi en yaygın başlangıç ​​matematik işlevlerini kapsıyordu. Orta düzey kullanıcılar, bu daha karmaşık işlevlere aşina olmalıdır:

• ABS(), bir sayının mutlak değerini döndürür.

• SQRT(), bir sayının karekökünü döndürür

• RAND(), 0 ile 1 arasında rastgele bir sayı sağlar

Koşullu Mantık

Orta düzeydeki kullanıcılar, bu işlevleri kullanarak birden çok koşula dayalı matematik hesaplamalarını nasıl yapacaklarını da bilmelidirler:

• ÇOKETOPLA(toplam_aralığı, ölçüt_aralığı1, ölçüt1, [….])

• EĞERSAY(ölçüt_aralığı1, ölçüt1, [ölçüt_aralığı2, ölçüt2], …)

• ORTALAMAEĞER(ölçüt_aralığı1, ölçüt1, [ölçüt_aralığı2, ölçüt2], …)

İşlevler, sağlanan ölçütleri değerlendirir ve mantıksal bir DOĞRU veya YANLIŞ değeri üretir.

Diyelim ki A, B ve C sütunlarında aşağıdaki satış verileriniz var:

Kırmızı tişört satışlarının toplamını hesaplamak için ÇOKETOPLA formülünü kullanın:

=ÇOKETOPLA(C2:C6, A2:A6, “Tişört”, B2:B6, “Kırmızı”)

Kırmızı tişörtlü satır sayısını saymak için COUNTIFS formülünü kullanın:

=EĞERSAY(C2:C6, A2:A6, "Tişört", B2:B6, "Kırmızı")

Kırmızı tişörtlerin ortalama satışını hesaplamak için ORTALAMAIFS formülünü kullanın:

=ORTALAMAEĞER(C2:C6, A2:A6, “Tişört”, B2:B6, “Kırmızı”)

Ara İstatistiksel Formüller

MIN ve MAX gibi temel işlevlerin yanı sıra, Excel, orta düzey kullanıcılar için daha geniş bir istatistiksel formül yelpazesine sahiptir. İşte en kullanışlı olanlardan bazıları:

• MİNA

• MAXA

• SAYI

• EĞERSAY

1. MINA ve MAXA

Daha yaygın olarak kullanılan MIN ve MAX işlevleri, metin değerlerini yok sayar.

Alternatif MINA ve MAXA işlevleri, en yüksek veya en düşük değerleri ararken hem metni hem de sayıları dikkate alır. Metin değerleri sıfırmış gibi değerlendirilir.

Farklılıklar aşağıdaki resimde gösterilmiştir. İlk satır minimum değeri 10 olarak değerlendirirken, ikinci satır metin değerinin varlığından dolayı 0 olarak değerlendirir.

2. COUNTA ve COUNTIF

COUNTA işlevi, bir aralıktaki boş olmayan hücrelerin sayısını saymak için kullanılır .

EĞERSAY işlevi, belirli bir koşulu veya ölçütü karşılayan bir aralık içindeki hücre sayısını saymak için kullanıldığından daha az belirgindir.

Bir sütundaki farklı değerlerin sayısını saymak için de bu işlevleri kullanabilirsiniz. Bu video bunun nasıl yapılacağını gösterir.

Finansal Analiz için Excel Formülleri

Yatırımları tahmin etmek gibi finansal analizler yaparken kullanmak isteyeceğiniz birkaç formül vardır:

• ÜRÜN(sayı1, [sayı2…])

• BÖLÜM(pay, payda)

• LOG(sayı; [temel])

1. ÜRÜN İşlevi

Beş yıl sonra (B3 hücresi) yıllık %4 faiz oranıyla (B2 hücresi) 1.000 ABD doları tutarındaki ilk yatırımın (B1 hücresi) gelecekteki değerini hesaplamak istediğinizi varsayalım.

Gelecekteki değeri şu formülle hesaplamak için ÇARPIM işlevini kullanın:

=B1 * ÇARPIM(1 + B2)^B3

Bu formül, yatırımınızın gelecekteki değerini geri getirecektir.

2. BÖLÜM ve GÜNLÜK İşlevleri

Bir sonraki adım, bir yatırımın verilen faiz oranında değerinin ikiye katlanmasının kaç yıl süreceğini hesaplamaktır.

Bunu aşağıdaki gibi hesaplamak için BÖLÜM işlevini LOG işleviyle birlikte kullanın:

=BÖLÜM(LOG(2) / LOG(1 + B2), 1)

Bu formül, örnek verilerle 17 döndürecek ve yıllık %4 faiz oranıyla yatırımın değerinin ikiye katlanmasının 17 yıl süreceğini gösteriyor.

Bu resimde bir Excel çalışma sayfasındaki formüller gösterilmektedir :

Yatırım Senaryoları için Fonksiyonlar

Bunlar, belirli mali senaryolar için uygun olan bazı işlevlerdir. Bunlardan biri tam olarak ihtiyacınız olan şey olabilir:

• NPV – NPV (Net Bugünkü Değer) işlevi, iskonto oranına dayalı bir dizi gelecekteki nakit akışına dayalı olarak bir yatırımın net bugünkü değerini hesaplar.

• ACCRINT – ACCRINT işlevi, periyodik faiz ödeyen bir menkul kıymetin tahakkuk eden faizini hesaplar. Bu, son ödeme tarihinden belirli bir takas tarihine kadar bir menkul kıymet üzerinden kazanılan faizi belirlemek için kullanışlıdır.

• INTRATE – INTRATE işlevi, tamamen yatırılmış bir menkul kıymet için faiz oranını hesaplar.

• PMT – PMT işlevi, bir borç menkul kıymetindeki toplam ödemeyi hesaplar.

• IRR – IRR işlevi, dahili getiri oranı sağlar.

• GETİRİ – GETİRİ işlevi, faiz oranı, nominal değer ve vadeye dayalı olarak bir menkul kıymetin getirisini sağlar.

Ara Excel Tarih ve Saat Formülleri

Excel'deki temel tarih ve saat işlevleri, geçerli tarih için ŞİMDİ ve BUGÜN işlevlerini içerir. Orta düzey kullanıcılar, aşağıdakileri kullanarak belirli bir tarihten bileşenleri nasıl çıkaracaklarını da bilmelidir:

• GÜN(tarih)

• Ay Tarihi)

• YIL(tarih)

=AY(“23 Nisan 2023″) formülü, 4. ay için 4 sonucunu döndürür. Benzer şekilde, GÜN ve YIL işlevleri sırasıyla 23 ve 2023'ü döndürür.

1. Ara Ay Fonksiyonları

Orta düzey kullanıcılar bazen bir tarihe ay ekleme veya çıkarma ve ayın sonunu bulma ile uğraşırlar .

• EDATE(başlangıç_tarihi, ay_sayısı)

• EOMONTH(başlangıç_tarihi; ay_sayısı)

Örneğin, =GÜZEL(“23 Nisan 2023”, 2) formülü iki ay sonraki tarihi hesaplayacaktır.

=EOAY(23 Nisan 2023”, 2) formülü, iki ay sonraki ayın sonunu hesaplar.

Sonuç, Haziran ayında yalnızca otuz gün olduğunu hesaba katan “30 Haziran 2023” dür. 3 ay belirtirseniz sonuç “31 Temmuz 2023” olacaktır.

2. Ara Hafta Fonksiyonları

Bir tarih aralığındaki tüm iş günlerinin sayısını saymak için TOPLA ve HAFTANIN GÜNÜ işlevlerinin bir birleşimini kullanabilirsiniz.

WEEKDAY işlevi, belirli bir tarih için haftanın gününü döndürür. İkinci parametre, numaralandırma sistemini varsayılan olarak Pazar'dan Cumartesi'ye kadar belirler.

B1:B40 hücre aralığında bir dizi tarih varsa, iş günlerini hesaplamak için şu formülü kullanın:

=TOPLA(–(HAFTAGÜNÜ(B1:B40,2)>5))

3. Ara Zaman Fonksiyonları

YEARFRAC işlevi, iki tarih arasındaki bir yılın kesirini hesaplar.

TIMEVALUE işlevi, metin olarak temsil edilen bir zamanı 24 saatlik bir günün oranını temsil eden bir ondalık sayıya dönüştürmek için kullanılır.

Sonuç, 1'in tam 24 saatlik bir günü, 0,5'in 12 saati, 0,25'in 6 saati vb. temsil ettiği bir Excel seri numarasıdır.

A1 hücresinde “17:45” metin biçiminde bir süreniz olduğunu ve onu ondalık sayıya dönüştürmek istediğinizi varsayalım . Bu formül:

=ZAMAN DEĞERİ(A1)

0,74 değeri, 24 saatlik günün saat altıdan on beş dakika önce geçen oranını temsil eder.

Zaman hücresini saat biçiminden metin biçimine değiştirmek için alın. Daha net olması için bunu yüzde olarak biçimlendirmek isteyebilirsiniz.

Koşullu ve Mantıksal Cebir için Excel İşlevleri

Koşullu ve mantıksal işlevler, Excel'de karar verme için gereklidir. Aşağıdaki işlevler, güçlü mantık için daha temel Excel formülleriyle birleştirilebilir:

1. EĞER(koşul, eğer_doğruysa değer, yanlışsa_değer):

2. VE(koşul1, koşul2, …)

3. VEYA(koşul1, koşul2, …)

4. DEĞİL(koşul1, koşul2, …)

EĞER işlevi bir koşulu değerlendirir ve koşulun doğru veya yanlış olmasına bağlı olarak farklı değerler döndürür.

AND, OR ve NOT işlevleri, hangi koşulların doğru olduğunu kontrol eder ve buna göre kararlar vermenizi sağlar.

Daha önce kullandığımız satış verilerine dayanan bazı örnekler.

1. EĞER İşlevi

Öğelerin satışları 15'in altındaysa "Düşük Satışlar" metnini görüntülemek istediğinizi varsayalım. Satışlar daha yüksekse, yeni bir sütunda "Yüksek Satışlar" görüntülemek istiyorsunuz.

Bu formülü kullanın ve diğer satırlara kopyalayın:

=EĞER(C2<15, "Düşük Satışlar", "Yüksek Satışlar")

2. VE İşlevi

En az 15 satışı olan tişörtler için DOĞRU veya YANLIŞ görüntülemek istediğinizi varsayalım. Bu formülü kullanın:

=VE(A3=”Tişört”, C3>=15)

3. VEYA İşlevi

Birden çok koşulu test etmek istediğinizi varsayalım ve koşullardan herhangi biri karşılanırsa DOĞRU, aksi takdirde YANLIŞ değerini döndürün, o zaman VEYA işlevini kullanırsınız.

Örneğin, A1 ve B1 hücrelerinde bir testin puanları varsa ve puanlardan herhangi birinin 80'in üzerinde olup olmadığını bilmek istiyorsak, şunu kullanabiliriz: = OR(A1>80, B1>80) . Bu, puanlardan biri (veya her ikisi) 80'in üzerindeyse DOĞRU ve her ikisi de 80 veya daha azsa YANLIŞ döndürür.

4. DEĞİL İşlevi

Öğenin kapşonlu olup olmadığını kontrol etmek istediğinizi varsayalım. Bu formülü kullanın:

=NOT(A2=”Kapşonlu”)

Başvuru Hücreleri

Yeni başlayan biri , $A$1 veya B2 gibi mutlak ve göreli referansları kullanmaya hızla alışacaktır . Ara kullanıcılar, dolaylı referanslar, indeksler ve ofsetler kullanmaya aşina olmalıdır:

1. DOLAYLI(başv_metni)

2. DİZİN(aralık; satır_sayısı; sütun_sayısı)

3. KAYDIR(referans_hücre, satırlar, sütunlar):

1. DOLAYLI İşlev

DOLAYLI işlevi, metin olarak girilen belirtilen hücre başvurusunun değerini döndürür. B3 hücresine başvurmak için şu formülü kullanın:

=DOLAYLI(“B3”)

Basit hücre referanslarını kullanmanın avantajı, bu işlevin size dinamik bir referans vermesidir. Bu, elektronik tablonun yapısı değiştiğinde, örneğin bir satır silindiğinde, Excel'in referansı otomatik olarak güncellediği anlamına gelir.

2. DİZİN İşlevi

DİZİN işlevi, satır ve sütun numaralarına dayalı olarak belirli bir aralıktaki hücrelere başvurmak için kullanılır.

Altı satırlık bir tabloda B3 hücresine başvurmak için şu formülü kullanın:

=DİZİN(B1:B6, 3)

3. OFSET İşlevi

KAYDIR işlevi, bir referans hücreden belirli sayıda satır ve sütun uzakta olan bir hücre veya aralık döndürür.

A1'den iki hücre boyunca ve bir hücre aşağıda olan hücre değerini görüntülemek istiyorsanız, şu formülü kullanın:

=OFFSET(A1, 2, 1)

İşte uygulamadaki formüller:

Ara Metin Formülleri

Yeni başlayanlar, bir dizenin sol tarafından bir veya daha fazla karakter çıkaran SOL gibi metin işlevlerini bilmelidir. Uzman bir kullanıcı, aşağıdaki gibi işlevlere aşina olmalıdır:

1. METİN BİRLEŞTİR(sınırlayıcı, yoksay_boş, metin1, [metin2, …])

2. DEĞİŞTİR(eski_metin, başlangıç_sayısı, karakter_sayısı, yeni_metin)

3. İKAME(metin; eski_metin; yeni_metin; [örnek_sayısı])

1. METİN BİRLEŞTİRME İşlevi

TEXTJOIN işlevi, hücreleri sağladığınız sınırlayıcıyla birleştirir. Boş hücrelerin nerede yok sayılacağını da belirleyebilirsiniz.

A sütunundaki bazı öğelerin virgülle ayrılmış bir listesini oluşturmak için şu formülü kullanın:

=METİN BİRLEŞTİRME(“, “, DOĞRU, A2:A4)

2. DEĞİŞTİR İşlevi

replace işlevi, hedefe takas etmek istediğiniz dizenin başlangıç ​​konumunu ve uzunluğunu belirtmenizi sağlar.

Bir dizenin ilk iki karakterini "XX" ile değiştirmek için şu formülü kullanın:

=METİN BİRLEŞTİR(A2, 1, 2, “XX”)

3. İKAME Fonksiyonu

replace işlevi, bir metin dizesinin başka bir metin dizesindeki belirtilen oluşumlarını yeni bir metin dizesiyle değiştirir.

"Kot" kelimesinin geçtiği yerleri "Tayt" ile değiştirmek için şu formülü kullanın:

=YEDEKLEŞTİR(A3, “Kot”, “Tayt”)

İşte uygulamadaki formüller:

Ara Arama Formülleri

DÜŞEYARA ve KAÇINCI gibi yaygın arama işlevleri, yeni başlayanların kopya kağıdında ele alınmıştır.

Orta düzeydeki kullanıcılar, SEÇ arama işlevine aşina olmalıdır:

• SEÇ(dizin_sayısı, değer1, [değer2, …])

CHOOSE işlevi, belirtilen dizin numarasına dayalı olarak bir değerler listesinden bir değer döndürür.

1, 2 veya 3 olarak etiketlenmiş bir tişört beden sütununuz olduğunu varsayalım. Aynı satırda sırasıyla küçük, orta ve büyük bir kategori görüntülemek istiyorsunuz. Bu formülü kullanın:

=SEÇİM(A1, “Küçük”, “Orta”, “Büyük”)

Microsoft Excel Hatalarını İşleme Formülleri

Acemi hile sayfası, Excel ile çalışırken göreceğiniz tipik hata mesajlarını listeledi. Orta düzey kullanıcılar, hataları incelikle işlemek için formülleri kullanabilmelidir.

1. IFERROR(değer; if_error)

2. IFNA(değer; değer_eğer_na)

1. IFERROR İşlevi

Sıfıra bölme hataları yaygındır, ancak Excel'in varsayılan #DIV/0! her kullanıcı için açık olmayacaktır.

"Sıfıra bölünemez" standart hatasını şu formülle değiştirmek için EĞERHATA işlevini kullanın:

=EĞERHATA(A1/B1, “Sıfıra bölünemez”)

Bu resim, üçüncü satırdaki ham hatayı ve dördüncü satırı işleyen IFERROR işlevini göstermektedir.

2. IFNA İşlevi

Excel'deki IFNA işlevi, #N/A hatalarını yakalamak ve işlemek için kullanılır.

Bu hile sayfasının başka bir bölümü SEÇ işlevini gösterdi. Dizin numarası bir tamsayı değilse, bu işlev bir N/A hatası döndürür.

Bir tişört bedeninin 1,5 olarak girildiğini ve kullanıcının yanlışlıkla bunun küçük ve orta arasında bir kategori göstereceğini düşündüğünü varsayalım. Bunun yerine, bir #YOK hatası gösterecektir .

Daha yararlı bir hata sağlamak için şu formülü kullanın:

=IFNA(CHOOSE(A2, "Düşük", "Orta", "Yüksek"), "Geçersiz dizin")

Formül ve İşlev Kısayolları

Çalışma sayfanızla daha verimli çalışmanızı sağlayabilecek birkaç klavye kısayolu vardır.

• F2 : Etkin hücreyi düzenleyin ve ekleme noktasını hücre içeriğinin sonuna konumlandırın.

• F9 : Bir formülün seçilen bölümünün sonucunu hesaplayın ve görüntüleyin.

• Ctrl + Shift + Enter : Bir dizi formülü girin.

• Shift + F3 : İşlev Ekle iletişim kutusunu açar.

• Esc : Bir formül girişini iptal edin ve orijinal hücre içeriğine geri dönün.

Bu Excel kısayollarından bazıları tüm dillerde veya klavye düzenlerinde bulunmayabilir.

Ara Hücre Biçimlendirmesi

Finansal analizde, sayıları, tarihleri ​​ve para birimlerini içeren hücreler için uygun biçimlendirme çok önemlidir. Hücreleri biçimlendirmek için şu adımları izleyin:

1. Biçimlendirmek istediğiniz hücreleri seçin.

2. Seçilen hücrelere sağ tıklayın ve "Hücreleri Biçimlendir"i seçin.

3. Uygun kategoriyi (örn. Sayı, Para Birimi, Tarih) seçin ve istenen formatı uygulayın.

Finansta dikkate alınması gereken bazı yaygın biçimlendirme türleri şunları içerir:

• Para Birimi : 1.234,56 Dolar

• Yüzde : %12,34

• Muhasebe : (1.234,00$)

• Tarih : 10-Mayıs-2023

Son düşünceler

Bu kopya kağıdında vurgulanan çeşitli formüller ve işlevler konusunda daha rahat hale geldikçe , giderek daha karmaşık hale gelen görevlerin ve projelerin üstesinden gelmek için kendinizi daha donanımlı bulacaksınız.

Alıştırma, herhangi bir beceride ustalaşmanın anahtarıdır, bu nedenle günlük işlerinizde bu formülleri denemekten korkmayın. Uzmanlığınızı başlangıç ​​seviyesinden orta seviyeye getirecekler.

Bu, Excel'in ara işlevlerinin işinizi basitleştirmek, analizlerinizi kolaylaştırmak ve verilerinizden içgörüler ortaya çıkarmanıza yardımcı olmak için yapabileceklerinin yalnızca bir kısmıdır.

Gerçek sihir, hile sayfamızdaki işlevleri birleştirmeye başladığınızda ve bunları benzersiz ihtiyaçlarınıza göre uyarladığınızda gerçekleşir. Yani, burada durma. Keşfetmeye, denemeye devam edin ve Excel'in yalnızca bir araç değil, ezber bozan bir şey olduğunu göreceksiniz!

İşleri bir sonraki seviyeye taşımak istiyor gibi mi hissediyorsunuz? Excel Gelişmiş Formüller Hile Sayfamıza bakın .