Formüller ve kolay yollar

Excel21 formül14 kolay yol

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

Mantık, metin ve arama

Orta

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

Derse git
=IF(logical_test, value_if_true, value_if_false)

Burada

logical_test
kontrol edilen koşul, örneğin B2>=50; sonucu TRUE ya da FALSE olur
value_if_true
koşul sağlandığında döndürülen değer
value_if_false
koş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

range
kontrol edilen hücreler
criteria
ölçüt: sayı, metin, karşılaştırma ya da hücre
=SUMIF(range, criteria, [sum_range])

Burada

range
koşulun kontrol edildiği hücreler
criteria
ölçüt (COUNTIF'teki gibi)
sum_range
toplanacak 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_value
aranan değer, örneğin ürün kodu
table_array
tablo; arama onun ilk sütununda yapılır
col_index_num
sonucun tablonun kaçıncı sütunundan döndürüleceği (1, 2, 3…)
range_lookup
FALSE (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_array
aramanın yapıldığı sütun
return_array
sonucun alındığı sütun
if_not_found
hiçbir şey bulunamazsa gösterilecek metin (isteğe bağlı)

İ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_value
aranan değer (kod, ad, tutar)
lookup_array
aramanın yapıldığı sütun ya da satır
return_array
sonucun alındığı aralık; birden fazla sütun da olabilir
if_not_found
hiçbir şey bulunamazsa döndürülecek değer; yazılmazsa #N/A
match_mode
0 — 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_mode
1 — 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, columns
kaç satır ve sütun doldurulacağı (sütun varsayılan olarak 1)
start, step
baş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_range
toplanacak 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

Q
ayda satılan fincan sayısı
P
fincan başına fiyat, ₼
V
fincan başına değişken maliyet, ₼
F
aylı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.

Profesyonel 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

A
dönem başına ödeme, ₼ (Excel'de PMT)
P
kredi tutarı (bugünkü değer), ₼
r
dönem başına faiz oranı: aylık ödemede yıllık %12, %12 / 12 = %1 = 0,01 olur
n
dönem sayısı (ay)

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

Iₖ = Bₖ₋₁ · rPₖ = 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

FV
n dönem sonra biriken tutar, ₼
A
her 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ışı, ₼
r
iskonto 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
r
korelasyon katsayısı, −1 ≤ r ≤ 1 — CORREL
b, a
eğ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 / TVariance = A − TGrowth = (A − P) / PAchievement = A / TVariance = A − TGrowth = (A − P) / P

Burada

A
gerçekleşen sonuç (örneğin satış, ₼)
T
hedef (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, ₼
n
yı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

Net
KDV'siz tutar, ₼
VAT_Rate
adlandı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.