Məzmuna keç
Educora
AZ
AzərbaycancaEnglishРусскийTürkçe
Mənim səhifəmEducora StudioParametrlər

Düsturlar və asan yollar

Excel · 35

Bu kursdakı bütün düsturlar və yadda saxlamağın asan yolları bir səhifədə.

1. Məntiq, mətn və axtarış2. Qabaqcıl Excel: funksiyalar və analiz3. Peşəkar Excel: maliyyə, statistika və avtomatlaşdırma

1Məntiq, mətn və axtarışOrta

Şərtli düsturlar: IF, AND, OR

Dərsə keç
=IF(logical_test, value_if_true, value_if_false)
burada:
  • logical_testyoxlanılan şərt, məsələn B2>=50; nəticəsi TRUE və ya FALSE olur
  • value_if_trueşərt ödənəndə qaytarılan dəyər
  • value_if_falseşərt ödənməyəndə qaytarılan dəyər

Şərtə görə saymaq və toplamaq: COUNTIF, SUMIF

Dərsə keç
=COUNTIF(range, criteria)
burada:
  • rangeyoxlanılan xanalar
  • criteriaşərt: rəqəm, mətn, müqayisə və ya xana
=SUMIF(range, criteria, [sum_range])
burada:
  • rangeşərtin yoxlandığı xanalar
  • criteriaşərt (COUNTIF-dəki kimi)
  • sum_rangetoplanacaq xanalar; yazılmasa, range özü toplanır

Cədvəldə axtarış: VLOOKUP və XLOOKUP

Dərsə keç
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
burada:
  • lookup_valueaxtarılan dəyər, məsələn, məhsulun kodu
  • table_arraycədvəl; axtarış onun birinci sütununda aparılır
  • col_index_numcədvəlin neçənci sütunundan nəticə qaytarılsın (1, 2, 3…)
  • range_lookupFALSE (və ya 0) — dəqiq uyğunluq; TRUE — təxmini uyğunluq
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
burada:
  • lookup_arrayaxtarış aparılan sütun
  • return_arraynəticənin götürüldüyü sütun
  • if_not_foundtapılmayanda göstəriləcək mətn (istəyə görə)

2Qabaqcıl Excel: funksiyalar və analizİrəli

XLOOKUP dərindən və INDEX/MATCH

Dərsə keç
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
burada:
  • lookup_valueaxtarılan dəyər (kod, ad, məbləğ)
  • lookup_arrayaxtarış aparılan sütun və ya sətir
  • return_arraynəticənin götürüldüyü diapazon; bir neçə sütun da ola bilər
  • if_not_foundheç nə tapılmayanda qaytarılan dəyər; yazılmasa, #N/A
  • match_mode0 — dəqiq (standart); -1 — dəqiq və ya növbəti kiçik; 1 — dəqiq və ya növbəti böyük; 2 — * və ? şablonları
  • search_mode1 — əvvəldən sona (standart); -1 — sondan əvvələ; 2 və -2 — sıralanmış verilənlərdə ikili axtarış
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
burada:
  • MATCH(…, 0)dəyərin mövqeyi (1, 2, 3…); 0 — dəqiq uyğunluq
  • INDEX(range, n)diapazonun n-ci elementi

Dinamik massivlər: FILTER, SORT, UNIQUE, LET, LAMBDA

Dərsə keç
=SEQUENCE(rows, [columns], [start], [step])
burada:
  • rows, columnsneçə sətir və sütun doldurulsun (sütun standart olaraq 1)
  • start, stepbaşlanğıc dəyər və addım (hər ikisi standart olaraq 1)

Bir neçə şərtlə hesablama: COUNTIFS, SUMIFS, IFS, SWITCH

Dərsə keç
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
burada:
  • sum_rangetoplanan rəqəmlər — SUMIFS-də birinci arqumentdir
  • criteria_range, criteria«harada yoxlayırıq — nəyi yoxlayırıq» cütü; 127 cütə qədər

COUNTIFS yalnız cütlərdən ibarətdir; AVERAGEIFS isə SUMIFS kimi əvvəlcə orta qiyməti hesablanan diapazonu qəbul edir. Bütün şərtlər eyni vaxtda ödənməlidir (VƏ məntiqi).

Tarixlər və mətn: peşəkar funksiyalar

Dərsə keç

Pivot cədvəllər dərindən

Dərsə keç

Power Query: verilənləri idxal etmək və təmizləmək

Dərsə keç

«What-If» təhlili: Goal Seek, ssenarilər, Data Table və Solver

Dərsə keç
Profit = Q · (P − V) − F
burada:
  • Qsatılan fincanların sayı (ayda)
  • Pbir fincanın qiyməti, ₼
  • Vbir fincana düşən dəyişən xərc, ₼
  • Faylıq sabit xərclər, ₼

Profit = 0 qoysaq, zərərsizlik nöqtəsi alınır: Q* = F / (P − V). (P − V) bir fincanın «marjinal gəliridir».

3Peşəkar Excel: maliyyə, statistika və avtomatlaşdırmaUniversitet

Maliyyə funksiyaları: kredit, əmanət və investisiya

Dərsə keç
A = P · r / (1 − (1 + r)⁻ⁿ)A = P · r / (1 − (1 + r)⁻ⁿ)
burada:
  • Ahər dövrün ödənişi, ₼ (Excel-də PMT)
  • Pkredit məbləği (bugünkü dəyər), ₼
  • rbir dövrün faiz dərəcəsi: illik 12% aylıq ödənişdə 12% / 12 = 1% = 0,01
  • ndövrlərin sayı (aylar)

Excel: =PMT(rate, nper, pv, [fv], [type]); ödənişlər dövrün əvvəlində edilirsə, type = 1.

Iₖ = Bₖ₋₁ · r; Pₖ = A − Iₖ; Bₖ = Bₖ₋₁ − Pₖ
burada:
  • Iₖk-cı ayın faiz hissəsi, ₼ (IPMT)
  • Pₖk-cı ayın əsas borc hissəsi, ₼ (PPMT)
  • Bₖk-cı ödənişdən sonra qalan borc, ₼ (B₀ = P)

Excel: =IPMT(rate, per, nper, pv) və =PPMT(rate, per, nper, pv); hər ay IPMT + PPMT = PMT.

FV = A · ((1 + r)ⁿ − 1) / rFV = A · ((1 + r)ⁿ − 1) / r
burada:
  • FVn dövrdən sonra yığılan məbləğ, ₼
  • Ahər dövrün sonunda qoyulan məbləğ, ₼

Excel: =FV(rate, nper, pmt, [pv], [type]) və əksinə, gələcək axınların bugünkü dəyəri: =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şlanğıc investisiya (t = 0), ₼
  • Cₜt ilinin sonundakı pul axını, ₼
  • rdiskont dərəcəsi (kapitalın dəyəri)

NPV > 0 — layihə kapitalın dəyərindən artıq qazandırır. IRR — NPV-ni sıfıra bərabər edən dərəcədir; IRR > r olduqda layihə qəbul edilir.

Excel-də statistika: orta, səpələnmə, korrelyasiya və reqressiya

Dərsə keç
s = √( ∑(xᵢ − x̄)² / (n − 1) ); σ = √( ∑(xᵢ − μ)² / N )s = √( ∑(xᵢ − x̄)² / (n − 1) ); σ = √( ∑(xᵢ − μ)² / N )
burada:
  • sseçmə standart kənarlaşması — STDEV.S
  • σbaş məcmunun standart kənarlaşması — STDEV.P
  • n, Nseçmənin və baş məcmunun həcmi

n − 1 (Bessel düzəlişi): seçmənin ortası verilənlərə «ən yaxın» nöqtədir, ona görə kənarlaşmaların kvadratları sistematik olaraq lazım olduğundan kiçik çıxır; n − 1-ə bölmək bunu kompensasiya edir. Ballar üçün: 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̄)² və ∑(y − ȳ)²
  • Sₓᵧ∑(x − x̄)(y − ȳ) — birgə dəyişmə
  • rkorrelyasiya əmsalı, −1 ≤ r ≤ 1 — CORREL
  • b, ameyl (bal/saat) və kəsişmə (bal) — SLOPE, INTERCEPT

b və a ən kiçik kvadratlar üsulundan alınır: ∑(y − a − bx)² minimum olsun deyə a və b üzrə törəmələri sıfıra bərabər edirik. Nəticədə xətt həmişə (x̄, ȳ) nöqtəsindən keçir.

İdarəetmə panelləri və verilənlərin vizuallaşdırılması

Dərsə keç
Achievement = A / T; Variance = A − T; Growth = (A − P) / PAchievement = A / T; Variance = A − T; Growth = (A − P) / P
burada:
  • Afaktiki nəticə (məsələn, satış, ₼)
  • Tplan (hədəf), ₼
  • Pəvvəlki dövrün nəticəsi, ₼

İcra faizi və artım nisbətdir (faiz formatı), kənarlaşma isə manatladır. Kənarlaşmanın işarəsi vacibdir: xərclərdə mənfi kənarlaşma yaxşıdır, satışda — pis.

CAGR = (Vₙ / V₀)^(1/n) − 1CAGR = (Vₙ / V₀)^(1/n) − 1
burada:
  • V₀, Vₙbaşlanğıc və son dəyər, ₼
  • nillərin sayı (dövrlər arasındakı addımlar)

Orta illik mürəkkəb artım tempi: Vₙ = V₀ · (1 + g)ⁿ bərabərliyindən g-ni tapırıq. Excel: =(B6/B2)^(1/4)-1 və ya =RRI(4,B2,B6).

Makrolar və VBA: təkrarlanan işi avtomatlaşdır

Dərsə keç
Workbooks("Sales.xlsm").Worksheets("Data").Range("B2").Value = 1500
burada:
  • Workbooks(…)iş kitabı (fayl)
  • Worksheets(…)iş vərəqi
  • Range(…) / Cells(row, col)xana və ya diapazon; Cells(2, 2) = B2
  • .Value, .Font, .ClearContentsxassələr (nəyə malikdir) və metodlar (nə edir)

Yol böyükdən kiçiyə nöqtələrlə yazılır; aktiv kitab və vərəq nəzərdə tutulursa, sadəcə Range("B2") kifayətdir.

Power Pivot və verilənlər modeli: əlaqələr və DAX

Dərsə keç
Baku Sales := CALCULATE([Total Sales], Customers[City] = "Baku")
burada:
  • [Total Sales]əvvəlcədən yaradılmış ölçü: SUM(Sales[Amount])
  • CALCULATE(expr, filter…)ifadəni dəyişdirilmiş filtr kontekstində hesablayır: şəhər filtrini «Baku» ilə əvəz edir, digər filtrləri saxlayır

Ölçü yaratmaq: Power Pivot › Calculations › Measures › New Measure… (və ya PivotTable Fields panelində cədvələ sağ klik › Add Measure…).

Peşəkar iş kitabı: quruluş, audit, qoruma və Copilot

Dərsə keç
Gross = Net · (1 + VAT_Rate)
burada:
  • NetƏDV-siz məbləğ, ₼
  • VAT_Rateadlı giriş xanası, məsələn 18% = 0,18

Excel-də: =Net*(1+VAT_Rate) — düstur öz-özünü izah edir. 250 ₼ üçün: 250 · 1,18 = 295 ₼. Ümumi ƏDV: =SUM(Net)*VAT_Rate.