Перейти к содержанию
Educora
RU
AzərbaycancaEnglishРусскийTürkçe
Моё обучениеEducora StudioНастройки

Формулы и лайфхаки

Excel · 35

Все формулы этого курса и простые способы их запомнить — на одной странице.

1. Логика, текст и поиск2. Продвинутый Excel: функции и анализ3. Профессиональный Excel: финансы, статистика и автоматизация

1Логика, текст и поискСредний

Условные формулы: IF, AND, OR

К уроку
=IF(logical_test, value_if_true, value_if_false)
где:
  • logical_testпроверяемое условие, например B2>=50; его результат — TRUE или FALSE
  • value_if_trueзначение, если условие выполняется
  • value_if_falseзначение, если условие не выполняется

Подсчёт и суммирование по условию: COUNTIF, SUMIF

К уроку
=COUNTIF(range, criteria)
где:
  • rangeпроверяемые ячейки
  • criteriaусловие: число, текст, сравнение или ячейка
=SUMIF(range, criteria, [sum_range])
где:
  • rangeячейки, по которым проверяется условие
  • criteriaусловие (как в COUNTIF)
  • sum_rangeячейки для суммирования; если не указаны, суммируется сам range

Поиск в таблице: VLOOKUP и XLOOKUP

К уроку
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
где:
  • lookup_valueискомое значение, например код товара
  • table_arrayтаблица; поиск идёт в её первом столбце
  • col_index_numиз какого по счёту столбца таблицы вернуть результат (1, 2, 3…)
  • range_lookupFALSE (или 0) — точное совпадение; TRUE — приблизительное
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
где:
  • lookup_arrayстолбец, в котором ищем
  • return_arrayстолбец, из которого берём результат
  • if_not_foundтекст, если ничего не найдено (необязательно)

2Продвинутый Excel: функции и анализПродвинутый

XLOOKUP в деталях и INDEX/MATCH

К уроку
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
где:
  • lookup_valueискомое значение (код, имя, сумма)
  • lookup_arrayстолбец или строка, где идёт поиск
  • return_arrayдиапазон, откуда берётся результат; может состоять из нескольких столбцов
  • if_not_foundчто вернуть, если ничего не найдено; если не указано — #N/A
  • match_mode0 — точное (по умолчанию); -1 — точное или ближайшее меньшее; 1 — точное или ближайшее большее; 2 — подстановочные знаки * и ?
  • search_mode1 — с первого до последнего (по умолчанию); -1 — с последнего к первому; 2 и -2 — двоичный поиск по отсортированным данным
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
где:
  • MATCH(…, 0)позиция значения (1, 2, 3…); 0 — точное совпадение
  • INDEX(range, n)n-й элемент диапазона

Динамические массивы: FILTER, SORT, UNIQUE, LET, LAMBDA

К уроку
=SEQUENCE(rows, [columns], [start], [step])
где:
  • rows, columnsсколько строк и столбцов заполнить (столбцов по умолчанию 1)
  • start, stepначальное значение и шаг (оба по умолчанию 1)

Расчёты по нескольким условиям: COUNTIFS, SUMIFS, IFS, SWITCH

К уроку
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
где:
  • sum_rangeскладываемые числа — в SUMIFS это первый аргумент
  • criteria_range, criteriaпара «где проверяем — что проверяем»; до 127 пар

COUNTIFS состоит только из пар; AVERAGEIFS, как и SUMIFS, сначала принимает диапазон для усреднения. Все условия должны выполняться одновременно (логика И).

Даты и текст: профессиональные функции

К уроку

Сводные таблицы в деталях

К уроку

Power Query: импорт и очистка данных

К уроку

Анализ «что если»: Goal Seek, сценарии, таблицы данных и Solver

К уроку
Profit = Q · (P − V) − F
где:
  • Qчисло проданных чашек в месяц
  • Pцена чашки, ₼
  • Vпеременные затраты на чашку, ₼
  • Fпостоянные затраты в месяц, ₼

Если положить Profit = 0, получим точку безубыточности: Q* = F / (P − V). (P − V) — маржинальный доход с одной чашки.

3Профессиональный Excel: финансы, статистика и автоматизацияУниверситет

Финансовые функции: кредиты, накопления и инвестиции

К уроку
A = P · r / (1 − (1 + r)⁻ⁿ)A = P · r / (1 − (1 + r)⁻ⁿ)
где:
  • Aплатёж за период, ₼ (в Excel — PMT)
  • Pсумма кредита (приведённая стоимость), ₼
  • rставка за один период: 12% годовых при ежемесячных платежах — это 12% / 12 = 1% = 0,01
  • nчисло периодов (месяцев)

Excel: =PMT(rate, nper, pv, [fv], [type]); type = 1, если платёж вносится в начале периода.

Iₖ = Bₖ₋₁ · r; Pₖ = A − Iₖ; Bₖ = Bₖ₋₁ − Pₖ
где:
  • Iₖпроцентная часть k-го платежа, ₼ (IPMT)
  • Pₖчасть k-го платежа в счёт основного долга, ₼ (PPMT)
  • Bₖостаток долга после k-го платежа, ₼ (B₀ = P)

Excel: =IPMT(rate, per, nper, pv) и =PPMT(rate, per, nper, pv); каждый месяц IPMT + PPMT = PMT.

FV = A · ((1 + r)ⁿ − 1) / rFV = A · ((1 + r)ⁿ − 1) / r
где:
  • FVнакопленная сумма через n периодов, ₼
  • Aсумма, вносимая в конце каждого периода, ₼

Excel: =FV(rate, nper, pmt, [pv], [type]), и обратная задача — сегодняшняя стоимость будущих потоков: =PV(rate, nper, pmt, [fv], [type]).

NPV = −C₀ + ∑ₜ₌₁ⁿ Cₜ / (1 + r)ᵗ; IRR: NPV(IRR) = 0NPV = −C₀ + ∑ₜ₌₁ⁿ Cₜ / (1 + r)ᵗ; IRR: NPV(IRR) = 0
где:
  • C₀начальные вложения (t = 0), ₼
  • Cₜденежный поток в конце года t, ₼
  • rставка дисконтирования (стоимость капитала)

NPV > 0 — проект зарабатывает больше стоимости капитала. IRR — ставка, при которой NPV равна нулю; проект принимают, если IRR > r.

Статистика в Excel: средние, разброс, корреляция и регрессия

К уроку
s = √( ∑(xᵢ − x̄)² / (n − 1) ); σ = √( ∑(xᵢ − μ)² / N )s = √( ∑(xᵢ − x̄)² / (n − 1) ); σ = √( ∑(xᵢ − μ)² / N )
где:
  • sвыборочное стандартное отклонение — STDEV.S
  • σстандартное отклонение генеральной совокупности — STDEV.P
  • n, Nобъём выборки и генеральной совокупности

n − 1 (поправка Бесселя): выборочное среднее — «ближайшая» к данным точка, поэтому сумма квадратов отклонений систематически занижена; деление на n − 1 это компенсирует. Для баллов: 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
где:
  • Sₓₓ, Sᵧᵧ∑(x − x̄)² и ∑(y − ȳ)²
  • Sₓᵧ∑(x − x̄)(y − ȳ) — совместная изменчивость
  • rкоэффициент корреляции, −1 ≤ r ≤ 1 — CORREL
  • b, aнаклон (баллов в час) и свободный член (баллы) — SLOPE, INTERCEPT

b и a получают методом наименьших квадратов: приравнивают к нулю производные ∑(y − a − bx)² по a и b. Поэтому линия всегда проходит через точку (x̄, ȳ).

Дашборды и визуализация данных

К уроку
Achievement = A / T; Variance = A − T; Growth = (A − P) / PAchievement = A / T; Variance = A − T; Growth = (A − P) / P
где:
  • Aфактический результат (например, продажи, ₼)
  • Tплан (цель), ₼
  • Pрезультат предыдущего периода, ₼

Выполнение плана и рост — отношения (процентный формат), отклонение — в манатах. Знак важен: для затрат отрицательное отклонение — хорошо, для продаж — плохо.

CAGR = (Vₙ / V₀)^(1/n) − 1CAGR = (Vₙ / V₀)^(1/n) − 1
где:
  • V₀, Vₙначальное и конечное значение, ₼
  • nчисло лет (шагов между периодами)

Среднегодовой сложный темп роста получаем, решив Vₙ = V₀ · (1 + g)ⁿ относительно g. Excel: =(B6/B2)^(1/4)-1 или =RRI(4,B2,B6).

Макросы и VBA: автоматизируй повторяющуюся работу

К уроку
Workbooks("Sales.xlsm").Worksheets("Data").Range("B2").Value = 1500
где:
  • Workbooks(…)книга (файл)
  • Worksheets(…)лист
  • Range(…) / Cells(row, col)ячейка или диапазон; Cells(2, 2) = B2
  • .Value, .Font, .ClearContentsсвойства (что есть) и методы (что делает)

Путь записывают от большего к меньшему через точки; если имеются в виду активные книга и лист, достаточно Range("B2").

Power Pivot и модель данных: связи и DAX

К уроку
Baku Sales := CALCULATE([Total Sales], Customers[City] = "Baku")
где:
  • [Total Sales]ранее созданная мера: SUM(Sales[Amount])
  • CALCULATE(expr, filter…)вычисляет выражение в изменённом контексте фильтра: заменяет фильтр по городу на «Baku», остальные фильтры сохраняет

Создание меры: Power Pivot › Calculations › Measures › New Measure… (или правый щелчок по таблице в панели PivotTable Fields › Add Measure…).

Профессиональная книга: структура, аудит, защита и Copilot

К уроку
Gross = Net · (1 + VAT_Rate)
где:
  • Netсумма без НДС, ₼
  • VAT_Rateименованная входная ячейка, например 18% = 0,18

В Excel: =Net*(1+VAT_Rate) — формула объясняет сама себя. Для 250 ₼: 250 · 1,18 = 295 ₼. Весь НДС: =SUM(Net)*VAT_Rate.