Формули Excel і XLOOKUP: практичний урок

Data Analyst Professional · Модуль 2 · Урок 6 із 51

Формули, lookup і контроль помилок

Сильна формула не просто повертає число. Вона має зрозумілу логіку, правильні посилання, явний match mode, контроль denominator і перевірку того, що помилка не була прихована під порожньою клітинкою.

90–120 хвFormula auditМінітест: 3 питання

Спочатку правило — потім формула

Бізнес-правило	Можливий інструмент	Перевірка
Сума net revenue для регіону й періоду	SUMIFS / SUM(FILTER())	Контрольний підсумок і граничні дати
Кількість унікальних замовлень	UNIQUE + COUNTA або pivot distinct where supported	Grain і дублікати order_id
Категорія за product_id	XLOOKUP / INDEX+MATCH	Унікальність lookup key і not found count
Статус за кількома умовами	IF/IFS, AND/OR або lookup rule table	Truth table для edge cases
Безпечна частка	IF(denominator=0, …) або IFERROR лише для очікуваної помилки	Окремий zero/blank case

Посилання: relative, absolute, structured

Relative

`B2` змінюється при копіюванні. Добре для рядкових обчислень, якщо структура стабільна.

Absolute / mixed

`$B$2`, `$B2`, `B$2` фіксують різні координати. Помилка тут може тихо зсунути коефіцієнт.

Structured

`Sales[net_revenue]` і `[@region]` описують поле й рядок. Назви полегшують review, але не замінюють перевірку grain.

Lookup: ключ важливіший за функцію

XLOOKUP шукає `lookup_value` у `lookup_array` і повертає значення з `return_array`; exact match є стандартним режимом у сучасних версіях. Але жодна функція не вирішить невизначений ключ.

Перевірте lookup key

Чи required він, чи унікальний у довіднику, чи один тип і формат?

Використовуйте exact match

Approximate match потрібен лише для явних інтервалів із коректно відсортованою таблицею.

Рахуйте not found

Невідповідності — це quality signal, а не причина мовчки повертати zero.

Контролюйте дублікати

Перший знайдений рядок може приховати суперечливі відповідності.

=XLOOKUP([@product_id], ProductMap[product_id], ProductMap[category], "NOT_FOUND", 0)Контроль:
COUNTIF(CALC[category], "NOT_FOUND") = 0
COUNTIF(ProductMap[product_id], current_id) <= 1

IFERROR: ловіть очікуване, не все

Ризик

`IFERROR(formula,"")` приховає #N/A, #VALUE!, неправильне посилання й інші дефекти однаково. Звіт виглядатиме чистим, але причина зникне.

Краще

Обробіть очікуваний case явно: denominator zero, lookup not found або missing input. Решту помилок залиште видимими у CHECKS.

SUMIFS/COUNTIFS: умова має межі

Для періоду записуйте lower і upper bound без двозначності. Якщо timestamp містить час, умова `<= end_date` може пропустити події після 00:00. Надійний шаблон часто використовує `>= start` і `< next_period_start`.

Псевдологіка:
SUMIFS(net_revenue,
order_datetime, ">=" & period_start,
order_datetime, "<"  & next_period_start,
region, selected_region,
status, "PAID")

Formula audit

Прочитайте формулу як речення. Чи відповідає вона правилу?
Перевірте один рядок вручну. Включно з edge case.
Trace precedents/dependents. Чи правильні source й діапазон?
Порівняйте totals. До і після lookup/filter/calculation.
Порахуйте errors. NOT_FOUND, blanks, duplicates і denominator zero.
Зафіксуйте версію. Не затирайте перевірений файл неконтрольованою правкою.

Практика: reconciliation calculator

Створіть довідник продуктів із навмисним duplicate key і знайдіть його.
Додайте exact XLOOKUP із явним `NOT_FOUND`.
Порахуйте SUMIFS за двома критеріями й half-open date interval.
Створіть safe rate з окремим denominator-zero status.
Додайте CHECKS: source rows, lookup misses, duplicate keys, source total, calculated total і delta.

Офіційні довідки

Готовність до тесту

Ви можете пояснити поведінку посилань, перевірити lookup key, не приховувати всі помилки через IFERROR і звірити source total з результатом формул.