# Полный курс по Excel: от базовой арифметики до динамических массивов

Источник: https://www.youtube.com/watch?v=gvhsKtjAmgc
Канал: freeCodeCamp.org
Опубликовано: 01.09.2026

---

Серджио, бывший программный инженер Amazon и основатель платформы Formula Wars, представляет интенсивный курс по работе в Microsoft Excel. В рамках обучения он объединяет теоретическую базу с практическим подходом, акцентируя внимание на том, что формулы — это фундаментальный язык, на котором «говорит» Excel для создания дашбордов и автоматизации рабочих процессов.

## 🧮 Основы формул: операторы и логика вычислений
[[JUMP:0:05:08]]

Работа в Excel начинается с понимания разницы между формулой и функцией. Формула — это любое выражение, которое сообщает программе, что именно нужно вычислить, и всегда начинается со знака равенства [05:13]. Функция же — это встроенный инструмент (например, `SUM` или `IF`), который может быть частью формулы [05:26].

В Excel используются несколько типов операторов:

*   **Арифметические операторы:** сложение (`+`), вычитание (`-`), умножение (`*`), деление (`/`), возведение в степень (`^`) и процент (`%`) [06:20].
*   **Операторы сравнения:** проверка на равенство (`=`), больше чем (`>`), меньше чем (`<`), а также комбинированные знаки «больше или равно» (`>=`) и «меньше или равно» (`<=`) [07:03].
*   **Текстовый оператор:** амперсанд (`&`) используется для объединения (конкатенации) строк текста. Важно помнить, что любой текст внутри формулы должен быть заключен в кавычки, иначе возникнет ошибка `#NAME?` [08:00].

При вычислении длинных формул Excel придерживается строгого порядка операций, известного под аббревиатурой PEMDAS: скобки, экспоненты, умножение/деление, сложение/вычитание [09:34]. Если приоритет операций одинаков, расчет идет слева направо [09:48]. Серджио подчеркивает, что скобки позволяют «вырезать» часть задачи, решить её первой и затем продолжить вычисление остальной формулы [10:30].

## 🔗 Искусство ссылок: относительные, абсолютные и смешанные
[[JUMP:0:13:34]]

Одной из главных особенностей Excel является возможность копирования формул без необходимости их переписывания. Это обеспечивается механизмом ссылок:

*   **Относительные ссылки:** По умолчанию Excel сохраняет позицию ячейки относительно формулы. Если вы скопируете формулу на строку ниже, ссылки в ней также сместятся на строку ниже [14:18].
*   **Абсолютные ссылки:** Используются, когда нужно зафиксировать конкретную ячейку (например, ставку налога или комиссионный процент). Для этого используется знак доллара: `$A$1` [15:38]. Такой адрес не изменится при копировании формулы в любое место таблицы.
*   **Смешанные ссылки:** Фиксируют только строку (`A$1`) или только столбец (`$A1`). По мнению ведущего, это критически важный навык для создания сложных таблиц, где одно значение должно оставаться в своем столбце, а другое — в своей строке [17:29].

## 🧩 Анатомия функций и обработка ошибок
[[JUMP:0:18:42]]

Любая функция состоит из двух частей: имени (глагола) и аргументов (входных данных) [18:44]. Аргументы могут быть обязательными и необязательными (последние в подсказках Excel выделяются квадратными скобками) [20:10]. При работе с необязательными аргументами важно соблюдать их позицию в формуле, даже если некоторые из них остаются пустыми [22:06].

Для поддержания стабильности таблиц необходимо понимать природу типичных ошибок [23:58]:

*   `#DIV/0!`: попытка деления на ноль.
*   `#VALUE!`: неверный тип данных (например, попытка сложить число с текстом).
*   `#NAME?`: опечатка в названии функции или нехватка кавычек в тексте.
*   `#REF!`: ссылка на ячейку, которая была удалена или находится за пределами книги.
*   `#N/A`: значение не найдено (часто встречается в функциях поиска).
*   `#NUM!`: недопустимый числовой результат (например, квадратный корень из отрицательного числа) [26:59].

Для обработки этих ситуаций Серджио рекомендует использовать функции `IFERROR` и `IFNA`. По его словам, в моделях с большим количеством поисковых функций `IFNA` предпочтительнее, так как она позволяет отличить отсутствие данных от реальной ошибки в логике формул [28:33].

## ⚖️ Логические функции и условия
[[JUMP:0:28:59]]

Логические формулы строятся по принципу: задать вопрос (истина/ложь) и определить результат для каждого случая.

*   `IF`: Базовая функция для разделения на два сценария (например, «сдал» или «не сдал» экзамен) [29:29].
*   `AND` и `OR`: Позволяют объединять несколько условий. `AND` требует выполнения всех условий, `OR` — хотя бы одного из них [30:22].
*   `NOT`: Инвертирует результат (превращает Истину в Ложь) [31:52].
*   `IFS`: Современная замена вложенным функциям `IF`. Она проверяет условия по порядку и возвращает результат первого истинного теста [32:59]. Ведущий советует начинать проверку с самых высоких пороговых значений, чтобы логика работала корректно [33:35].

## 📊 Агрегация данных и условия «IFS»
[[JUMP:0:34:38]]

Функции агрегации превращают массивы данных в краткие сводки. К базовым относятся `SUM`, `AVERAGE`, `MEDIAN`, `MIN` и `MAX` [35:00]. Отдельное внимание уделяется функциям подсчета:

*   `COUNT`: считает только ячейки с числами.
*   `COUNTA`: считает все непустые ячейки (включая текст).
*   `COUNTBLANK`: считает только пустые ячейки [37:42].

Условная агрегация (функции, заканчивающиеся на `IFS`) позволяет суммировать или считать данные только при выполнении определенных критериев [42:41]. Серджио отмечает, что хотя существуют старые версии функций (например, `SUMIF` без «S»), лучше всегда использовать версии `IFS`, так как они универсальны и позволяют легко добавлять новые условия [45:34].

## 🔍 Функции поиска: эпоха XLOOKUP
[[JUMP:0:49:25]]

Серджио называет `XLOOKUP` своим любимым инструментом и «швейцарским армейским ножом» для поиска в Excel [04:34]. В отличие от классического `VLOOKUP`, эта функция:

1.  Может искать данные слева от ключевого столбца [50:24].
2.  Не требует сортировки данных [56:28].
3.  Имеет встроенный аргумент для обработки ошибок («если не найдено») [51:57].
4.  Может возвращать сразу несколько столбцов данных (динамический разлив) [52:39].

Также обсуждается связка `INDEX` и `XMATCH`, которая, по мнению автора, остается актуальной для двумерного поиска (на пересечении строки и столбца), делая логику формулы более явной [1:01:51]. Несмотря на наличие устаревших функций вроде `VLOOKUP` и `HLOOKUP`, ведущий рекомендует использовать их только для чтения старых файлов, отдавая предпочтение современным альтернативам в новых проектах [1:05:55].

## 📝 Работа с текстом и нормализация данных
[[JUMP:1:06:21]]

Грязные данные — частая проблема, решаемая нормализацией. Основные инструменты:

*   `TRIM`: удаляет лишние пробелы в начале, конце и между словами [1:07:03].
*   `LOWER`, `UPPER`, `PROPER`: меняют регистр текста [1:07:33].
*   `SUBSTITUTE` и `REPLACE`: первая заменяет конкретные символы, вторая — символы на определенных позициях [1:08:11].
*   `TEXT`: превращает числа или даты в текст с заданным форматированием (например, превращает серийный номер даты в название месяца) [1:09:46].

Для извлечения частей текста используются функции `LEFT`, `RIGHT` и `MID`, которые часто комбинируются с функциями поиска `FIND` или `SEARCH` для автоматического определения границ фрагментов (например, извлечение логина из email-адреса) [1:14:10].

## 📅 Логика дат в Excel
[[JUMP:1:15:10]]

Важно понимать, что для Excel любая дата — это просто порядковое число (серийный номер), где единица соответствует 1 января 1900 года [1:15:55]. Это позволяет проводить с датами арифметические операции.

Ключевые функции:

*   `DATE`: собирает дату из отдельных чисел года, месяца и дня [1:16:47].
*   `WORKDAY` и `NETWORKDAYS`: вычисляют сроки и количество рабочих дней, автоматически исключая выходные и заданные праздники [1:19:17].
*   `EOMONTH` и `EDATE`: помогают перемещаться по календарю на заданное количество месяцев вперед или назад, учитывая разную длину месяцев [1:21:41].

## 🚀 Динамические массивы: будущее Excel
[[JUMP:1:23:54]]

Современные функции динамических массивов кардинально меняют подход к работе, позволяя одной формуле заполнять сразу целый диапазон ячеек (эффект «разлива» или *spill*) [1:24:11].

*   `FILTER`: отбирает данные по условиям. При этом несколько условий объединяются через умножение (аналог логического И) [1:26:50].
*   `SORT` и `SORTBY`: позволяют сортировать данные внутри формул. `SORTBY` мощнее, так как позволяет сортировать по скрытым столбцам или по нескольким уровням сразу [1:31:52].
*   `UNIQUE`: извлекает список уникальных значений. С аргументом `exactly_once` можно найти значения, которые встречаются в исходном списке строго один раз [1:37:19].

Серджио объясняет, что при использовании таких функций важно оставлять пустое место под ними, иначе возникнет ошибка `#SPILL!` — сигнал о том, что результату мешают существующие данные в соседних ячейках [1:39:35].