Серджио, бывший программный инженер Amazon и основатель платформы Formula Wars, представляет интенсивный курс по работе в Microsoft Excel. В рамках обучения он объединяет теоретическую базу с практическим подходом, акцентируя внимание на том, что формулы — это фундаментальный язык, на котором «говорит» Excel для создания дашбордов и автоматизации рабочих процессов.
🧮 Основы формул: операторы и логика вычислений 0:05:08
Работа в Excel начинается с понимания разницы между формулой и функцией. Формула — это любое выражение, которое сообщает программе, что именно нужно вычислить, и всегда начинается со знака равенства . Функция же — это встроенный инструмент (например, SUM или IF), который может быть частью формулы .
В Excel используются несколько типов операторов:
- Арифметические операторы: сложение (
+), вычитание (-), умножение (*), деление (/), возведение в степень (^) и процент (%) . - Операторы сравнения: проверка на равенство (
=), больше чем (>), меньше чем (<), а также комбинированные знаки «больше или равно» (>=) и «меньше или равно» (<=) . - Текстовый оператор: амперсанд (
&) используется для объединения (конкатенации) строк текста. Важно помнить, что любой текст внутри формулы должен быть заключен в кавычки, иначе возникнет ошибка#NAME?.
При вычислении длинных формул Excel придерживается строгого порядка операций, известного под аббревиатурой PEMDAS: скобки, экспоненты, умножение/деление, сложение/вычитание . Если приоритет операций одинаков, расчет идет слева направо . Серджио подчеркивает, что скобки позволяют «вырезать» часть задачи, решить её первой и затем продолжить вычисление остальной формулы .
🔗 Искусство ссылок: относительные, абсолютные и смешанные 0:13:34
Одной из главных особенностей Excel является возможность копирования формул без необходимости их переписывания. Это обеспечивается механизмом ссылок:
- Относительные ссылки: По умолчанию Excel сохраняет позицию ячейки относительно формулы. Если вы скопируете формулу на строку ниже, ссылки в ней также сместятся на строку ниже .
- Абсолютные ссылки: Используются, когда нужно зафиксировать конкретную ячейку (например, ставку налога или комиссионный процент). Для этого используется знак доллара:
$A$1. Такой адрес не изменится при копировании формулы в любое место таблицы. - Смешанные ссылки: Фиксируют только строку (
A$1) или только столбец ($A1). По мнению ведущего, это критически важный навык для создания сложных таблиц, где одно значение должно оставаться в своем столбце, а другое — в своей строке .
🧩 Анатомия функций и обработка ошибок 0:18:42
Любая функция состоит из двух частей: имени (глагола) и аргументов (входных данных) . Аргументы могут быть обязательными и необязательными (последние в подсказках Excel выделяются квадратными скобками) . При работе с необязательными аргументами важно соблюдать их позицию в формуле, даже если некоторые из них остаются пустыми .
Для поддержания стабильности таблиц необходимо понимать природу типичных ошибок :
#DIV/0!: попытка деления на ноль.#VALUE!: неверный тип данных (например, попытка сложить число с текстом).#NAME?: опечатка в названии функции или нехватка кавычек в тексте.#REF!: ссылка на ячейку, которая была удалена или находится за пределами книги.#N/A: значение не найдено (часто встречается в функциях поиска).#NUM!: недопустимый числовой результат (например, квадратный корень из отрицательного числа) .
Для обработки этих ситуаций Серджио рекомендует использовать функции IFERROR и IFNA. По его словам, в моделях с большим количеством поисковых функций IFNA предпочтительнее, так как она позволяет отличить отсутствие данных от реальной ошибки в логике формул .
⚖️ Логические функции и условия 0:28:59
Логические формулы строятся по принципу: задать вопрос (истина/ложь) и определить результат для каждого случая.
IF: Базовая функция для разделения на два сценария (например, «сдал» или «не сдал» экзамен) .ANDиOR: Позволяют объединять несколько условий.ANDтребует выполнения всех условий,OR— хотя бы одного из них .NOT: Инвертирует результат (превращает Истину в Ложь) .IFS: Современная замена вложенным функциямIF. Она проверяет условия по порядку и возвращает результат первого истинного теста . Ведущий советует начинать проверку с самых высоких пороговых значений, чтобы логика работала корректно .
📊 Агрегация данных и условия «IFS» 0:34:38
Функции агрегации превращают массивы данных в краткие сводки. К базовым относятся SUM, AVERAGE, MEDIAN, MIN и MAX . Отдельное внимание уделяется функциям подсчета:
COUNT: считает только ячейки с числами.COUNTA: считает все непустые ячейки (включая текст).COUNTBLANK: считает только пустые ячейки .
Условная агрегация (функции, заканчивающиеся на IFS) позволяет суммировать или считать данные только при выполнении определенных критериев . Серджио отмечает, что хотя существуют старые версии функций (например, SUMIF без «S»), лучше всегда использовать версии IFS, так как они универсальны и позволяют легко добавлять новые условия .
🔍 Функции поиска: эпоха XLOOKUP 0:49:25
Серджио называет XLOOKUP своим любимым инструментом и «швейцарским армейским ножом» для поиска в Excel . В отличие от классического VLOOKUP, эта функция:
- Может искать данные слева от ключевого столбца .
- Не требует сортировки данных .
- Имеет встроенный аргумент для обработки ошибок («если не найдено») .
- Может возвращать сразу несколько столбцов данных (динамический разлив) .
Также обсуждается связка INDEX и XMATCH, которая, по мнению автора, остается актуальной для двумерного поиска (на пересечении строки и столбца), делая логику формулы более явной . Несмотря на наличие устаревших функций вроде VLOOKUP и HLOOKUP, ведущий рекомендует использовать их только для чтения старых файлов, отдавая предпочтение современным альтернативам в новых проектах .
📝 Работа с текстом и нормализация данных 1:06:21
Грязные данные — частая проблема, решаемая нормализацией. Основные инструменты:
TRIM: удаляет лишние пробелы в начале, конце и между словами .LOWER,UPPER,PROPER: меняют регистр текста .SUBSTITUTEиREPLACE: первая заменяет конкретные символы, вторая — символы на определенных позициях .TEXT: превращает числа или даты в текст с заданным форматированием (например, превращает серийный номер даты в название месяца) .
Для извлечения частей текста используются функции LEFT, RIGHT и MID, которые часто комбинируются с функциями поиска FIND или SEARCH для автоматического определения границ фрагментов (например, извлечение логина из email-адреса) .
📅 Логика дат в Excel 1:15:10
Важно понимать, что для Excel любая дата — это просто порядковое число (серийный номер), где единица соответствует 1 января 1900 года . Это позволяет проводить с датами арифметические операции.
Ключевые функции:
DATE: собирает дату из отдельных чисел года, месяца и дня .WORKDAYиNETWORKDAYS: вычисляют сроки и количество рабочих дней, автоматически исключая выходные и заданные праздники .EOMONTHиEDATE: помогают перемещаться по календарю на заданное количество месяцев вперед или назад, учитывая разную длину месяцев .
🚀 Динамические массивы: будущее Excel 1:23:54
Современные функции динамических массивов кардинально меняют подход к работе, позволяя одной формуле заполнять сразу целый диапазон ячеек (эффект «разлива» или spill) .
FILTER: отбирает данные по условиям. При этом несколько условий объединяются через умножение (аналог логического И) .SORTиSORTBY: позволяют сортировать данные внутри формул.SORTBYмощнее, так как позволяет сортировать по скрытым столбцам или по нескольким уровням сразу .UNIQUE: извлекает список уникальных значений. С аргументомexactly_onceможно найти значения, которые встречаются в исходном списке строго один раз .
Серджио объясняет, что при использовании таких функций важно оставлять пустое место под ними, иначе возникнет ошибка #SPILL! — сигнал о том, что результату мешают существующие данные в соседних ячейках .
