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

Excel21 формула14 лайфхаков

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

Логика, текст и поиск

Средний

Условные формулы: 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_lookup
FALSE (или 0) — точное совпадение; TRUE — приблизительное
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

Где

lookup_array
столбец, в котором ищем
return_array
столбец, из которого берём результат
if_not_found
текст, если ничего не найдено (необязательно)

Продвинутый 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_mode
0 — точное (по умолчанию); -1 — точное или ближайшее меньшее; 1 — точное или ближайшее большее; 2 — подстановочные знаки * и ?
search_mode
1 — с первого до последнего (по умолчанию); -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) — маржинальный доход с одной чашки.

Профессиональный 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ₖ₋₁ · rPₖ = 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 / TVariance = A − TGrowth = (A − P) / PAchievement = A / TVariance = A − TGrowth = (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.