Формулы и лайфхаки
Excel21 формула14 лайфхаков
Все формулы этого курса и простые способы их запомнить, на одной странице.
Логика, текст и поиск
СреднийУсловные формулы: IF, AND, OR
К урокуГде
- logical_test
- проверяемое условие, например
B2>=50; его результат — TRUE или FALSE - value_if_true
- значение, если условие выполняется
- value_if_false
- значение, если условие не выполняется
Подсчёт и суммирование по условию: COUNTIF, SUMIF
К урокуГде
- range
- проверяемые ячейки
- criteria
- условие: число, текст, сравнение или ячейка
Где
- range
- ячейки, по которым проверяется условие
- criteria
- условие (как в COUNTIF)
- sum_range
- ячейки для суммирования; если не указаны, суммируется сам
range
Поиск в таблице: VLOOKUP и XLOOKUP
К урокуГде
- lookup_value
- искомое значение, например код товара
- table_array
- таблица; поиск идёт в её первом столбце
- col_index_num
- из какого по счёту столбца таблицы вернуть результат (1, 2, 3…)
- range_lookup
FALSE(или 0) — точное совпадение;TRUE— приблизительное
Где
- lookup_array
- столбец, в котором ищем
- return_array
- столбец, из которого берём результат
- if_not_found
- текст, если ничего не найдено (необязательно)
Продвинутый Excel: функции и анализ
ПродвинутыйXLOOKUP в деталях и INDEX/MATCH
К урокуГде
- lookup_value
- искомое значение (код, имя, сумма)
- lookup_array
- столбец или строка, где идёт поиск
- return_array
- диапазон, откуда берётся результат; может состоять из нескольких столбцов
- if_not_found
- что вернуть, если ничего не найдено; если не указано — #N/A
- match_mode
- 0 — точное (по умолчанию); -1 — точное или ближайшее меньшее; 1 — точное или ближайшее большее; 2 — подстановочные знаки
*и? - search_mode
- 1 — с первого до последнего (по умолчанию); -1 — с последнего к первому; 2 и -2 — двоичный поиск по отсортированным данным
Где
- MATCH(…, 0)
- позиция значения (1, 2, 3…); 0 — точное совпадение
- INDEX(range, n)
- n-й элемент диапазона
Динамические массивы: FILTER, SORT, UNIQUE, LET, LAMBDA
К урокуГде
- rows, columns
- сколько строк и столбцов заполнить (столбцов по умолчанию 1)
- start, step
- начальное значение и шаг (оба по умолчанию 1)
Расчёты по нескольким условиям: COUNTIFS, SUMIFS, IFS, SWITCH
К урокуГде
- sum_range
- складываемые числа — в SUMIFS это первый аргумент
- criteria_range, criteria
- пара «где проверяем — что проверяем»; до 127 пар
COUNTIFS состоит только из пар; AVERAGEIFS, как и SUMIFS, сначала принимает диапазон для усреднения. Все условия должны выполняться одновременно (логика И).
Даты и текст: профессиональные функции
К урокуСводные таблицы в деталях
К урокуPower Query: импорт и очистка данных
К урокуАнализ «что если»: Goal Seek, сценарии, таблицы данных и Solver
К урокуГде
- Q
- число проданных чашек в месяц
- P
- цена чашки, ₼
- V
- переменные затраты на чашку, ₼
- F
- постоянные затраты в месяц, ₼
Если положить Profit = 0, получим точку безубыточности: Q* = F / (P − V). (P − V) — маржинальный доход с одной чашки.
Профессиональный Excel: финансы, статистика и автоматизация
УниверситетФинансовые функции: кредиты, накопления и инвестиции
К урокуГде
- A
- платёж за период, ₼ (в Excel —
PMT) - P
- сумма кредита (приведённая стоимость), ₼
- r
- ставка за один период: 12% годовых при ежемесячных платежах — это 12% / 12 = 1% = 0,01
- n
- число периодов (месяцев)
Excel: =PMT(rate, nper, pv, [fv], [type]); type = 1, если платёж вносится в начале периода.
Где
- 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
- накопленная сумма через n периодов, ₼
- A
- сумма, вносимая в конце каждого периода, ₼
Excel: =FV(rate, nper, pmt, [pv], [type]), и обратная задача — сегодняшняя стоимость будущих потоков: =PV(rate, nper, pmt, [fv], [type]).
Где
- C₀
- начальные вложения (t = 0), ₼
- Cₜ
- денежный поток в конце года t, ₼
- r
- ставка дисконтирования (стоимость капитала)
NPV > 0 — проект зарабатывает больше стоимости капитала. IRR — ставка, при которой NPV равна нулю; проект принимают, если IRR > r.
Статистика в Excel: средние, разброс, корреляция и регрессия
К урокуГде
- s
- выборочное стандартное отклонение —
STDEV.S - σ
- стандартное отклонение генеральной совокупности —
STDEV.P - n, N
- объём выборки и генеральной совокупности
n − 1 (поправка Бесселя): выборочное среднее — «ближайшая» к данным точка, поэтому сумма квадратов отклонений систематически занижена; деление на n − 1 это компенсирует. Для баллов: s = 13,802, σ = 12,911.
Где
- 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̄, ȳ).
Дашборды и визуализация данных
К урокуГде
- A
- фактический результат (например, продажи, ₼)
- T
- план (цель), ₼
- P
- результат предыдущего периода, ₼
Выполнение плана и рост — отношения (процентный формат), отклонение — в манатах. Знак важен: для затрат отрицательное отклонение — хорошо, для продаж — плохо.
Где
- V₀, Vₙ
- начальное и конечное значение, ₼
- n
- число лет (шагов между периодами)
Среднегодовой сложный темп роста получаем, решив Vₙ = V₀ · (1 + g)ⁿ относительно g. Excel: =(B6/B2)^(1/4)-1 или =RRI(4,B2,B6).
Макросы и VBA: автоматизируй повторяющуюся работу
К урокуГде
- Workbooks(…)
- книга (файл)
- Worksheets(…)
- лист
- Range(…) / Cells(row, col)
- ячейка или диапазон;
Cells(2, 2)= B2 - .Value, .Font, .ClearContents
- свойства (что есть) и методы (что делает)
Путь записывают от большего к меньшему через точки; если имеются в виду активные книга и лист, достаточно Range("B2").
Power Pivot и модель данных: связи и DAX
К урокуГде
- [Total Sales]
- ранее созданная мера:
SUM(Sales[Amount]) - CALCULATE(expr, filter…)
- вычисляет выражение в изменённом контексте фильтра: заменяет фильтр по городу на «Baku», остальные фильтры сохраняет
Создание меры: Power Pivot › Calculations › Measures › New Measure… (или правый щелчок по таблице в панели PivotTable Fields › Add Measure…).
Профессиональная книга: структура, аудит, защита и Copilot
К урокуГде
- Net
- сумма без НДС, ₼
- VAT_Rate
- именованная входная ячейка, например 18% = 0,18
В Excel: =Net*(1+VAT_Rate) — формула объясняет сама себя. Для 250 ₼: 250 · 1,18 = 295 ₼. Весь НДС: =SUM(Net)*VAT_Rate.