İçeriğe geç
Educora
TR
AzərbaycancaEnglishРусскийTürkçe
ÖğrenmemEducora StudioAyarlar

Formüller ve kolay yollar

Excel · 35

Bu kurstaki tüm formüller ve onları hatırlamanın kolay yolları tek sayfada.

1. Mantık, metin ve arama2. İleri düzey Excel: işlevler ve analiz3. Profesyonel Excel: finans, istatistik ve otomasyon

1Mantık, metin ve aramaOrta

Koşullu formüller: IF, AND, OR

Derse git
=IF(logical_test, value_if_true, value_if_false)
burada:
  • logical_testkontrol edilen koşul, örneğin B2>=50; sonucu TRUE ya da FALSE olur
  • value_if_truekoşul sağlandığında döndürülen değer
  • value_if_falsekoşul sağlanmadığında döndürülen değer

Ölçüte göre sayma ve toplama: COUNTIF, SUMIF

Derse git
=COUNTIF(range, criteria)
burada:
  • rangekontrol edilen hücreler
  • criteriaölçüt: sayı, metin, karşılaştırma ya da hücre
=SUMIF(range, criteria, [sum_range])
burada:
  • rangekoşulun kontrol edildiği hücreler
  • criteriaölçüt (COUNTIF'teki gibi)
  • sum_rangetoplanacak hücreler; yazılmazsa range'in kendisi toplanır

Tabloda arama: VLOOKUP ve XLOOKUP

Derse git
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
burada:
  • lookup_valuearanan değer, örneğin ürün kodu
  • table_arraytablo; arama onun ilk sütununda yapılır
  • col_index_numsonucun tablonun kaçıncı sütunundan döndürüleceği (1, 2, 3…)
  • range_lookupFALSE (ya da 0) — tam eşleşme; TRUE — yaklaşık eşleşme
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
burada:
  • lookup_arrayaramanın yapıldığı sütun
  • return_arraysonucun alındığı sütun
  • if_not_foundhiçbir şey bulunamazsa gösterilecek metin (isteğe bağlı)

2İleri düzey Excel: işlevler ve analizİleri

Derinlemesine XLOOKUP ve INDEX/MATCH

Derse git
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
burada:
  • lookup_valuearanan değer (kod, ad, tutar)
  • lookup_arrayaramanın yapıldığı sütun ya da satır
  • return_arraysonucun alındığı aralık; birden fazla sütun da olabilir
  • if_not_foundhiçbir şey bulunamazsa döndürülecek değer; yazılmazsa #N/A
  • match_mode0 — tam (varsayılan); -1 — tam ya da bir sonraki küçük; 1 — tam ya da bir sonraki büyük; 2 — * ve ? joker karakterleri
  • search_mode1 — ilkten sona (varsayılan); -1 — sondan ilke; 2 ve -2 — sıralı verilerde ikili arama
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
burada:
  • MATCH(…, 0)değerin konumu (1, 2, 3…); 0 tam eşleşme demektir
  • INDEX(range, n)aralığın n'inci öğesi

Dinamik diziler: FILTER, SORT, UNIQUE, LET, LAMBDA

Derse git
=SEQUENCE(rows, [columns], [start], [step])
burada:
  • rows, columnskaç satır ve sütun doldurulacağı (sütun varsayılan olarak 1)
  • start, stepbaşlangıç değeri ve adım (ikisi de varsayılan olarak 1)

Çok ölçütlü formüller: COUNTIFS, SUMIFS, IFS, SWITCH

Derse git
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
burada:
  • sum_rangetoplanacak sayılar; SUMIFS'te bu ilk bağımsız değişkendir
  • criteria_range, criteria“nerede kontrol edilecek — ne kontrol edilecek” çifti; en fazla 127 çift

COUNTIFS yalnızca çiftlerden oluşur; AVERAGEIFS ise SUMIFS gibi önce ortalaması alınacak aralığı alır. Tüm koşullar aynı anda sağlanmalıdır (VE mantığı).

Tarihler ve metin: profesyonel işlevler

Derse git

Derinlemesine Özet Tablolar

Derse git

Power Query: verileri içe aktarma ve temizleme

Derse git

Durum analizi: Goal Seek, senaryolar, Veri Tabloları ve Çözücü

Derse git
Profit = Q · (P − V) − F
burada:
  • Qayda satılan fincan sayısı
  • Pfincan başına fiyat, ₼
  • Vfincan başına değişken maliyet, ₼
  • Faylık sabit giderler, ₼

Profit = 0 alınırsa başa baş noktası bulunur: Q* = F / (P − V). (P − V) fincan başına katkı payıdır.

3Profesyonel Excel: finans, istatistik ve otomasyonÜniversite

Finansal işlevler: krediler, birikim ve yatırımlar

Derse git
A = P · r / (1 − (1 + r)⁻ⁿ)A = P · r / (1 − (1 + r)⁻ⁿ)
burada:
  • Adönem başına ödeme, ₼ (Excel'de PMT)
  • Pkredi tutarı (bugünkü değer), ₼
  • rdönem başına faiz oranı: aylık ödemede yıllık %12, %12 / 12 = %1 = 0,01 olur
  • ndönem sayısı (ay)

Excel: =PMT(rate, nper, pv, [fv], [type]); ödemeler dönem başında yapılıyorsa type = 1.

Iₖ = Bₖ₋₁ · r; Pₖ = A − Iₖ; Bₖ = Bₖ₋₁ − Pₖ
burada:
  • Iₖk'ıncı taksitin faiz kısmı, ₼ (IPMT)
  • Pₖk'ıncı taksitin anapara kısmı, ₼ (PPMT)
  • Bₖk'ıncı ödemeden sonra kalan borç, ₼ (B₀ = P)

Excel: =IPMT(rate, per, nper, pv) ve =PPMT(rate, per, nper, pv); her ay IPMT + PPMT = PMT.

FV = A · ((1 + r)ⁿ − 1) / rFV = A · ((1 + r)ⁿ − 1) / r
burada:
  • FVn dönem sonra biriken tutar, ₼
  • Aher dönem sonunda yatırılan tutar, ₼

Excel: =FV(rate, nper, pmt, [pv], [type]); tersi, yani gelecekteki akışların bugünkü değeri: =PV(rate, nper, pmt, [fv], [type]).

NPV = −C₀ + ∑ₜ₌₁ⁿ Cₜ / (1 + r)ᵗ; IRR: NPV(IRR) = 0NPV = −C₀ + ∑ₜ₌₁ⁿ Cₜ / (1 + r)ᵗ; IRR: NPV(IRR) = 0
burada:
  • C₀başlangıç yatırımı (t = 0), ₼
  • Cₜt yılı sonundaki nakit akışı, ₼
  • riskonto oranı (sermaye maliyeti)

NPV > 0, projenin sermaye maliyetinden fazla kazandırdığı anlamına gelir. IRR, NPV'yi sıfır yapan orandır; IRR > r ise proje kabul edilir.

Excel'de istatistik: ortalamalar, yayılım, korelasyon ve regresyon

Derse git
s = √( ∑(xᵢ − x̄)² / (n − 1) ); σ = √( ∑(xᵢ − μ)² / N )s = √( ∑(xᵢ − x̄)² / (n − 1) ); σ = √( ∑(xᵢ − μ)² / N )
burada:
  • sörneklem standart sapması — STDEV.S
  • σanakütle standart sapması — STDEV.P
  • n, Nörneklem ve anakütle büyüklüğü

n − 1 (Bessel düzeltmesi): örneklem ortalaması verilere “en yakın” noktadır; bu yüzden sapmaların kareleri sistematik olarak küçük çıkar, n − 1'e bölmek bunu telafi eder. Puanlar için: s = 13,802, σ = 12,911.

r = Sₓᵧ / √(Sₓₓ · Sᵧᵧ); b = Sₓᵧ / Sₓₓ; a = ȳ − b · x̄; ŷ = a + b · xr = Sₓᵧ / √(Sₓₓ · Sᵧᵧ); b = Sₓᵧ / Sₓₓ; a = ȳ − b · x̄; ŷ = a + b · x
burada:
  • Sₓₓ, Sᵧᵧ∑(x − x̄)² ve ∑(y − ȳ)²
  • Sₓᵧ∑(x − x̄)(y − ȳ) — birlikte değişim
  • rkorelasyon katsayısı, −1 ≤ r ≤ 1 — CORREL
  • b, aeğim (saat başına puan) ve kesişim (puan) — SLOPE, INTERCEPT

b ve a en küçük kareler yönteminden gelir: ∑(y − a − bx)²'nin a ve b'ye göre türevleri sıfıra eşitlenir. Sonuçta doğru her zaman (x̄, ȳ) noktasından geçer.

Gösterge panoları ve veri görselleştirme

Derse git
Achievement = A / T; Variance = A − T; Growth = (A − P) / PAchievement = A / T; Variance = A − T; Growth = (A − P) / P
burada:
  • Agerçekleşen sonuç (örneğin satış, ₼)
  • Thedef (plan), ₼
  • Pönceki dönemin sonucu, ₼

Gerçekleşme ve büyüme orandır (yüzde biçimi), sapma ise manat cinsindendir. İşaret önemlidir: giderlerde negatif sapma iyidir, satışlarda kötüdür.

CAGR = (Vₙ / V₀)^(1/n) − 1CAGR = (Vₙ / V₀)^(1/n) − 1
burada:
  • V₀, Vₙbaşlangıç ve bitiş değeri, ₼
  • nyıl sayısı (dönemler arasındaki adımlar)

Yıllık bileşik büyüme oranı, Vₙ = V₀ · (1 + g)ⁿ eşitliğini g için çözerek bulunur. Excel: =(B6/B2)^(1/4)-1 ya da =RRI(4,B2,B6).

Makrolar ve VBA: tekrarlanan işi otomatikleştir

Derse git
Workbooks("Sales.xlsm").Worksheets("Data").Range("B2").Value = 1500
burada:
  • Workbooks(…)çalışma kitabı (dosya)
  • Worksheets(…)çalışma sayfası
  • Range(…) / Cells(row, col)hücre ya da aralık; Cells(2, 2) = B2
  • .Value, .Font, .ClearContentsözellikler (neye sahip) ve yöntemler (ne yapar)

Yol büyükten küçüğe noktalarla yazılır; etkin kitap ve sayfa kastediliyorsa yalnızca Range("B2") yeterlidir.

Power Pivot ve Veri Modeli: ilişkiler ve DAX

Derse git
Baku Sales := CALCULATE([Total Sales], Customers[City] = "Baku")
burada:
  • [Total Sales]önceden oluşturulmuş bir ölçü: SUM(Sales[Amount])
  • CALCULATE(expr, filter…)ifadeyi değiştirilmiş bir filtre bağlamında hesaplar: şehir filtresini “Baku” ile değiştirir, diğer filtreleri korur

Ölçü oluşturmak: Power Pivot › Calculations › Measures › New Measure… (ya da PivotTable Fields bölmesinde tabloya sağ tık › Add Measure…).

Profesyonel çalışma kitabı: yapı, denetim, koruma ve Copilot

Derse git
Gross = Net · (1 + VAT_Rate)
burada:
  • NetKDV'siz tutar, ₼
  • VAT_Rateadlandırılmış girdi hücresi, örneğin %18 = 0,18

Excel'de: =Net*(1+VAT_Rate); formül kendini açıklar. 250 ₼ için: 250 · 1,18 = 295 ₼. Toplam KDV: =SUM(Net)*VAT_Rate.