1.11. Справочник по Excel

ДЛЯ НОВИЧКОВНЕ ОБЯЗАТЕЛЬНОВ РАЗРАБОТКЕ

Всем

Справочник по Excel

1. Интерфейс и навигация

1.1 Основные области интерфейса

Элемент	Описание
Лента	Верхняя панель с вкладками: «Главная», «Вставка», «Макет страницы», «Формулы», «Данные», «Рецензирование», «Вид», «Разработчик» (опционально), «Дополнения».
Панель быстрого доступа	Сверху слева; можно добавлять команды вручную (Файл → Параметры → Панель быстрого доступа).
Строка формул	Отображает содержимое активной ячейки (значение или формулу); редактируется напрямую.
Имя ячейки / Панель имён	Слева от строки формул; отображает адрес активной ячейки (A1) или имя диапазона/таблицы. Позволяет быстро перейти к ячейке или ввести имя.
Строка состояния	Внизу окна; отображает сумму, среднее, количество выделенных ячеек (настраивается ПКМ → выбрать отображаемые данные).
Полосы прокрутки	Горизонтальная и вертикальная; при Ctrl + колесо мыши — масштабирование (если включено в параметрах).

1.2 Режимы ввода и редактирования

Режим	Активация	Поведение
Готов	Ячейка выделена, курсор вне строки формул	Стрелки перемещают выделение.
Ввод	Начат ввод текста/формулы (но Enter не нажат)	Стрелки перемещают курсор внутри строки ввода.
Редактирование	После нажатия F2 в готовом режиме	Стрелки перемещают курсор по символам формулы, а не по ячейкам. При редактировании формулы с ссылками — Ctrl + стрелка перемещает фокус по ячейкам-аргументам.

1.3 Ключевые горячие клавиши (все — при русской раскладке, без модификаторов Eng)

Комбинация	Действие
F1	Вызов справки.
F2	Режим редактирования содержимого ячейки (переход в строку формул с курсором в конце).
F3	Вставка имени (если имена определены).
F4	Циклическое изменение типа ссылки (A1 → $A$1 → A$1 → $A1 → A1) внутри формулы при редактировании.
F5	«Перейти» (диалоговое окно: ввод адреса или имени).
F9	Пересчёт всех листов (если ручной режим вычислений — пересчитывает выделенную формулу при редактировании).
Shift + F9	Пересчёт только активного листа.
Ctrl + F9	Минимизация окна книги.
Ctrl + ;	Вставка текущей даты (в формате даты, не текст).
Ctrl + Shift + ;	Вставка текущего времени (в формате времени, не текст).
Ctrl + ` (тильда, слева от «1»)	Переключение между отображением формул и значений (на всём листе).
Ctrl + A	Выделение: первый раз — текущая область данных; второй раз — весь лист.
Ctrl + C / V / X / Z / Y	Копировать / Вставить / Вырезать / Отменить / Повторить.
Ctrl + D	Заполнить вниз (копирование содержимого верхней ячейки в выделенные ниже).
Ctrl + R	Заполнить вправо.
Ctrl + E	Flash Fill (автозаполнение по шаблону, Excel ≥ 2013).
Ctrl + F	Поиск.
Ctrl + H	Замена.
Ctrl + G = F5	Перейти.
Ctrl + Home	Переход в A1 (или в первую непустую ячейку, если включено «Использовать клавиши перехода», Параметры → Дополнительно).
Ctrl + End	Переход в последнюю использованную ячейку (нижний правый угол диапазона).
Ctrl + стрелка	Переход к краю текущего блока данных (в направлении стрелки).
Ctrl + Shift + стрелка	Выделение до края блока данных.
Ctrl + T	Создание умной таблицы (диалог: указать диапазон и наличие заголовков).
Ctrl + Shift + L	Вкл/выкл фильтра (если диапазон не таблица).
Ctrl + /	Выделить только видимые ячейки (после фильтра/группировки).
Ctrl + / + Alt + ; (в англ. раскладке Alt + ;) — выделение только видимых ячеек. В русской раскладке: Alt + , (запятая рядом с правым Alt на большинстве клавиатур).
Ctrl + F3	Диспетчер имён (создание, редактирование, удаление имён).
Alt + =	Автосумма (СУММ) — предложение формулы по диапазону выше/слева.
Alt + Enter	Перенос строки внутри ячейки (только при выравнивании «Переносить по словам» или вручную).
Shift + F11	Вставка нового листа.
Alt + F1	Создание встроенного диаграммы из выделенных данных.

Примечание: Все комбинации работают в русскоязычной версии Excel при активной русской раскладке клавиатуры, если не указано иное.

---

2. Ячейки, диапазоны, имена

2.1 Адресация

Тип	Пример	Описание
Относительная	A1, B5	Смещение от текущей ячейки при копировании.
Абсолютная	$A$1	Фиксированная ссылка — не меняется при копировании.
Смешанная (фикс. столбец)	$A1	Столбец фиксирован, строка — относительна.
Смешанная (фикс. строка)	A$1	Строка фиксирована, столбец — относительный.
Внелистовая	Лист2!A1, 'Лист с пробелом'!$B$5	Ссылка на ячейку другого листа.
Внекнижная	[Книга2.xlsx]Лист1!A1	Ссылка на ячейку другой открытой книги.
Табличная (структурная)	Таблица1[Продажи], Таблица1[@Продажи]	Используется в «умных таблицах»; [@…] — текущая строка.

2.2 Именованные диапазоны

• Создаются через: Формулы → Диспетчер имён → Создать или Ctrl + F3.
• Имя должно начинаться с буквы, символа подчёркивания _ или обратной косой \, не содержать пробелов (можно использовать _ или .), не совпадать с адресом ячейки (например, R1C1 — недопустимо).
• Область действия: локальная (только текущий лист) или глобальная (вся книга).
• Использование в формулах: =СУММ(Продажи), =ВПР(A2;Товары;2;ЛОЖЬ).
• Можно назначать имена формулам, например:
  Имя: НДС13 → формула: =0.13
  Тогда: =A2*НДС13.

2.3 3D-ссылки

• Суммируют одинаковые диапазоны на нескольких листах:
  =СУММ(Лист1:Лист3!A1) — сумма ячейки A1 с листов от «Лист1» до «Лист3» включительно (если их порядок в книге — последовательный).
• Осторожно: При добавлении/удалении листов в диапазоне — ссылка может нарушиться.

---

3. Форматы данных

3.1 Встроенные категории (вкладка «Главная» → блок «Число»)

Категория	Подкатегории / особенности
Общий	Автоматическое определение: число, дата, текст. Не хранит форматирование.
Числовой	Разделитель тысяч, число знаков после запятой, отрицательные в скобках/красным.
Денежный	Символ валюты слева от числа, выравнивание по символу валюты в столбце.
Финансовый	Символ валюты у края ячейки (выравнивание столбца), ноль отображается как прочерк.
Дата	Множество встроенных форматов: ДД.ММ.ГГГГ, 14 янв., среда, Q1 2025. Хранится как целое число (1 = 01.01.1900).
Время	От ЧЧ:ММ до ДД.ММ.ГГГГ ЧЧ:ММ:СС. Хранится как дробная часть числа (1 = 24 часа).
Процентный	Умножает значение на 100 и добавляет %; 0,25 → 25%.
Дробный	Отображает 0,333 как 1/3 (с заданной точностью).
Экспоненциальный	Научная запись: 1,23E+10.
Текст	Принудительное хранение как текста (даже если содержимое — число). Ведёт себя как «кавычки» при вводе ('123).
Дополнительный	Телефон, ИНН, почтовый индекс и др. (зависит от региональных настроек Windows).

3.2 Пользовательские числовые форматы (ПНФ)

Доступ: Главная → Число → Другие числовые форматы → (категория) Другой.

Синтаксис: до четырёх секций, разделённых точкой с запятой ;:
Положительные;Отрицательные;Нулевые;Текст

Примеры:

• 0,00 "руб.";[Красный]-0,00 "руб.";0,00 "руб."
  Положительные и нули — «12,34 руб.», отрицательные — красным «-12,34 руб.».
• [>999]0,0 "тыс.";0
  Числа >999 — 1,2 тыс., иначе — 999.
• ДДДД, ДД ММММ ГГГГ
среда, 26 ноября 2025
• 000000
  Дополнение нулями слева до 6 знаков (ИНН, номер заказа).
• "Договор №"000000
Договор №000123
• [Часы]:ММ:СС
  Для суммирования времени >24 часов (иначе сброс после 24:00).

Коды:

Код	Значение
0	Обязательная цифра (выводит 0, если разряд пуст).
#	Необязательная цифра (не выводит 0, если разряд пуст).
?	Цифра с выравниванием по десятичной точке (для дробей).
.	Десятичная точка (локализуется — запятая в РФ).
,	Тысячный разделитель (при двух подряд — деление на 10⁶: 0,, "млн").
Д, М, Г, Ч, М, С	День, месяц, год, час, минута, секунда (в форматах даты/времени).
"текст"	Литеральный текст (не интерпретируется).
*	Повтор следующего символа до конца ячейки (например, @*. — выравнивание точек справа).
\_	Пробел по ширине следующего символа (для выравнивания валют).

---

4. Работа с листами и книгами

4.1 Типы листов

Тип	Описание
Рабочий лист	Основной тип — содержит ячейки.
Диаграмма	Отдельный лист только с диаграммой (устаревшее; сейчас диаграммы встраиваются).
Макрос (VBA)	Недоступен напрямую; модули хранятся в редакторе VBA (Alt + F11).

4.2 Операции с листами

• Переименование: двойной клик по ярлыку или ПКМ → Переименовать.
• Цвет ярлыка: ПКМ → Цвет ярлычка.
• Перемещение/копирование: ПКМ → Переместить или скопировать → выбрать книгу/положение.
  Shift + перетаскивание — копирование, Ctrl + перетаскивание — перемещение (внутри книги).
• Скрытие/отображение: ПКМ → Скрыть / ПКМ на ярлыке → Показать (если есть скрытые).
• Группировка листов: выделить несколько ярлыков (Shift/Ctrl) → любые изменения применяются ко всем. Отмена — клик по любому несгруппированному листу.

4.3 Работа с книгами

• Сохранение: .xlsx (стандарт), .xlsm (с макросами), .xlsb (бинарный, быстрее), .xls (совместимость), .csv (только активный лист, без формул).
• Параметры сохранения (Файл → Сохранить как → Другие параметры):
  • Сохранять только значения (без формул) — только при экспорте в CSV.
  • Шифрование паролем — шифрует всё содержимое (AES-256).
  • Пометка как окончательная — отключает редактирование (но легко обходится).
  • Проверка совместимости — при сохранении в .xls.

---

5. Функции Excel

Все функции приведены в русской локализации, с обязательными параметрами жирным, необязательными — курсивом.

5.1 Логические функции

Функция	Синтаксис	Описание
ИСТИНА()	=ИСТИНА()	Возвращает логическое ИСТИНА.
ЛОЖЬ()	=ЛОЖЬ()	Возвращает логическое ЛОЖЬ.
НЕ(логическое_значение)	=НЕ(A1>5)	Инвертирует логическое значение.
И(логическое_значение1; [логическое_значение2]; …)	=И(A1>0; B1<10)	Возвращает ИСТИНА, если все условия истинны. Макс. 255 аргументов.
ИЛИ(логическое_значение1; [логическое_значение2]; …)	=ИЛИ(A1="Да"; A1="OK")	Возвращает ИСТИНА, если хотя бы одно условие истинно.
ИСКЛИЛИ(логическое_значение1; [логическое_значение2]; …)	=ИСКЛИЛИ(A1;B1)	Исключающее ИЛИ («только одно»). Доступно с Excel 2013.
ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь)	=ЕСЛИ(A1>100; "Высокий"; "Низкий")	Условная проверка. Вложенные ЕСЛИ — до 64 уровней.
ЕСЛИОШИБКА(значение; значение_если_ошибка)	=ЕСЛИОШИБКА(A1/B1; "Деление на 0")	Возвращает альтернативное значение при любой ошибке.
ЕСЛИНАОШИБКА(значение; значение_если_не_ошибка)	=ЕСЛИНАОШИБКА(ВПР(...); "Нет в справочнике")	Обратная логика: срабатывает, если нет ошибки. Excel 2013+.
ПЕРЕКЛЮЧ(выражение; значение1; результат1; [значение2; результат2]; …; [значение_по_умолчанию])	=ПЕРЕКЛЮЧ(A1; "Янв"; 1; "Фев"; 2; "Неизвестно")	Аналог SWITCH. Excel 2016+.

5.2 Текстовые функции

Функция	Синтаксис	Описание
ТЕКСТ(значение; формат)	=ТЕКСТ(СЕГОДНЯ(); "ДД.ММ.ГГГГ")	Преобразует число/дату в текст по заданному формату.
ЗНАЧЕН(текст)	=ЗНАЧЕН("123,45")	Преобразует текст, представляющий число, в число.
СЦЕП(текст1; [текст2]; …)	=СЦЕП(A1; " "; B1)	Объединяет текст. Аналог &. Excel 2016+.
СЦЕПИТЬ(текст1; [текст2]; …)	=СЦЕПИТЬ(A1; "-"; B1)	Устаревший синоним СЦЕП.
ЛЕВСИМВ(текст; количество_знаков)	=ЛЕВСИМВ("иван@example.com"; 4) → "иван"	Возвращает левую часть текста.
ПРАВСИМВ(текст; количество_знаков)	=ПРАВСИМВ(A1; 3)	Правая часть.
СИМВОЛ(номер)	=СИМВОЛ(10)	Возвращает символ по коду ANSI (10 — перевод строки).
ПСТР(текст; начальная_позиция; количество_знаков)	=ПСТР(A1; 6; 7)	Извлекает подстроку.
НАЙТИ(искомый_текст; внутри_текста; [начальная_позиция])	=НАЙТИ("@"; A1)	Возвращает позицию подстроки (чувствительна к регистру). Ошибка, если не найдено.
ПОИСК(искомый_текст; внутри_текста; [начальная_позиция])	=ПОИСК("@"; A1)	То же, без учёта регистра.
ЗАМЕНИТЬ(старый_текст; начальная_позиция; число_знаков; новый_текст)	=ЗАМЕНИТЬ(A1; 1; 4; "user")	Замена по позиции.
ПОДСТАВИТЬ(текст; старый_текст; новый_текст; [номер_вхождения])	=ПОДСТАВИТЬ(A1; "@"; "[at]")	Замена по значению (все вхождения или указанное по номеру).
СЖПРОБЕЛЫ(текст)	=СЖПРОБЕЛЫ(" иван петров ") → "иван петров"	Удаляет лишние пробелы (оставляет по одному между словами).
ПЕЧСИМВ(текст)	=ПЕЧСИМВ(A1)	Удаляет непечатаемые символы (коды 0–31).
ДЛСТР(текст)	=ДЛСТР(A1)	Количество символов.
ПРОПНАЧ(текст)	=ПРОПНАЧ("иван иванов") → "Иван Иванов"	Каждое слово — с заглавной.
ПРОПИСН(текст) / СТРОЧН(текст)	=ПРОПИСН(A1)	В верхний / нижний регистр.
ФИКСИРОВАНН(число; [число_знаков]; [без_разделителей])	=ФИКСИРОВАНН(1234.567; 1; ИСТИНА) → "1234,6"	Форматирует число как текст с фикс. знаками и (опц.) без разделителей.

5.3 Дата и время

Функция	Синтаксис	Описание
СЕГОДНЯ()	=СЕГОДНЯ()	Текущая дата (без времени). Обновляется при пересчёте.
ТДАТА()	=ТДАТА()	Текущие дата и время.
ДАТА(год; месяц; день)	=ДАТА(2025;11;26)	Возвращает дату как число. Автокоррекция переполнения (например, месяц=13 → +1 год).
ГОД(дата), МЕСЯЦ(дата), ДЕНЬ(дата)	=ГОД(A1)	Извлекают компоненты даты.
ЧАС(время), МИНУТЫ(время), СЕКУНДЫ(время)	=МИНУТЫ(A1)	Компоненты времени.
ДАТАЗНАЧ(текст_даты)	=ДАТАЗНАЧ("26.11.2025")	Преобразует текст в дату.
ВРЕМЗНАЧ(текст_времени)	=ВРЕМЗНАЧ("14:30")	Преобразует текст во время.
ДЕНЬНЕД(дата; [тип])	=ДЕНЬНЕД(A1;2)	Номер дня недели: тип=1 → ВС=1, ПН=2 …; тип=2 → ПН=1, ВТ=2 … (рекомендуется тип=2).
ТЕКСТ(дата; "ДДД")	=ТЕКСТ(A1; "ДДД") → "ср"	Для получения названия дня: "ДДД" — короткое, "ДДДД" — полное.
РАБДЕНЬ(нач_дата; число_дней; [праздники])	=РАБДЕНЬ(A1;5;E1:E10)	Дата через N рабочих дней (исключая выходные и список праздников).
ЧИСТРАБДНИ(нач_дата; кон_дата; [праздники])	=ЧИСТРАБДНИ(A1;B1)	Количество рабочих дней между датами.
КОНМЕСЯЦА(дата; месяцы; [тип])	=КОНМЕСЯЦА(A1;0)	Последний день месяца (0 — текущий, 1 — следующий и т.д.). Тип=0 — включая выходные, тип=1 — последний рабочий день.

---

6. Математические функции

Функция	Синтаксис	Описание
СУММ(число1; [число2]; …)	=СУММ(A1:A10; C1:C5)	Суммирует числа, ссылки, диапазоны. Игнорирует текст и логические значения.
ПРОИЗВЕД(число1; [число2]; …)	=ПРОИЗВЕД(A1:A5)	Произведение аргументов. Аналог A1*A2*…*A5, но устойчив к ошибкам (не прерывается при #ЗНАЧ!).
СУММЕСЛИ(диапазон; критерий; [диапазон_суммирования])	=СУММЕСЛИ(A:A; "Яблоки"; B:B)	Сумма по условию. Если диапазон_суммирования не указан — суммируется сам диапазон.
СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; …)	=СУММЕСЛИМН(C:C; A:A; "Яблоки"; B:B; ">10")	Сумма по нескольким условиям. Все диапазоны должны быть одинаковой длины.
СУММПРОИЗВ(массив1; [массив2]; …)	=СУММПРОИЗВ(A1:A5; B1:B5)	Скалярное произведение векторов. Эффективен для условных вычислений без массивных формул: =СУММПРОИЗВ((A1:A10="Яблоки")*(B1:B10>10)*C1:C10).
ОКРУГЛ(число; число_знаков)	=ОКРУГЛ(3,14159; 2) → 3,14	Округление по математическим правилам (до ближайшего).
ОКРУГЛВВЕРХ(число; число_знаков)	=ОКРУГЛВВЕРХ(3,1; 0) → 4	Всегда вверх (от нуля).
ОКРУГЛВНИЗ(число; число_знаков)	=ОКРУГЛВНИЗ(-3,9; 0) → -3	Всегда вниз (к нулю).
ОТБР(число; [число_знаков])	=ОТБР(3,9) → 3	Усечение (отбрасывание дробной части), без округления.
ЦЕЛОЕ(число)	=ЦЕЛОЕ(-3,2) → -4	Наибольшее целое ≤ числу (не усечение для отрицательных!).
ОСТАТ(число; делитель)	=ОСТАТ(10; 3) → 1; =ОСТАТ(-10; 3) → 2	Остаток от деления. Всегда ≥0, если делитель > 0. Равен =число - делитель*ЦЕЛОЕ(число/делитель).
ЧАСТНОЕ(числитель; знаменатель)	=ЧАСТНОЕ(10; 3) → 3	Целая часть от деления (аналог ЦЕЛОЕ(числитель/знаменатель)).
СЛЧИС()	=СЛЧИС()	Случайное число ∈ [0; 1). Пересчитывается при каждом изменении листа.
СЛУЧМЕЖДУ(нижн; верхн)	=СЛУЧМЕЖДУ(1; 100)	Целое случайное число в диапазоне [нижн; верхн]. Excel 2007+.
СТЕПЕНЬ(число; степень)	=СТЕПЕНЬ(2; 10) → 1024	Возведение в степень. Эквивалент число^степень.
КОРЕНЬ(число)	=КОРЕНЬ(16) → 4	Квадратный корень (только для ≥0).
ABS(число)	=ABS(-5) → 5	Модуль.
ЗНАК(число)	=ЗНАК(-7) → -1	Возвращает -1, 0, 1.
ПИ()	=ПИ() → 3,14159265358979	Число π с точностью 15 знаков.
РАДИАНЫ(угол) / ГРАДУСЫ(угол)	=РАДИАНЫ(180) → ПИ()	Преобразование между градусами и радианами (для тригонометрии).
SIN(число), COS(число), TAN(число)	=SIN(ПИ()/2) → 1	Тригонометрические функции (аргумент — в радианах).

Важно: Все тригонометрические функции работают в радианах. Для углов в градусах используйте РАДИАНЫ().

---

7. Статистические функции

7.1 Основные меры центральной тенденции и разброса

Функция	Синтаксис	Описание
СРЗНАЧ(число1; [число2]; …)	=СРЗНАЧ(A1:A10)	Среднее арифметическое. Игнорирует текст и логические.
СРЗНАЧА(значение1; [значение2]; …)	=СРЗНАЧА(A1:A10)	Среднее с учётом текста (=0) и логических (ИСТИНА=1, ЛОЖЬ=0).
МЕДИАНА(число1; [число2]; …)	=МЕДИАНА(A1:A9)	Медиана (серединное значение в упорядоченном ряду).
МОДА.ОДН(число1; [число2]; …)	=МОДА.ОДН(A1:A100)	Наиболее часто встречающееся значение (одно). Если мод несколько — возвращает первое. Excel 2010+.
МОДА.НСК(число1; [число2]; …)	=МОДА.НСК(A1:A100)	Возвращает все моды в виде вертикального массива (Excel 365/2021 — автоматически разливается).
МИН(число1; [число2]; …)	=МИН(A1:A10)	Минимальное числовое значение. Игнорирует текст.
МИНА(значение1; [значение2]; …)	=МИНА(A1:A10)	Минимум с учётом текста (=0) и логических (ЛОЖЬ=0, ИСТИНА=1).
МАКС(число1; [число2]; …)	=МАКС(A1:A10)	Максимальное числовое значение.
МАКСА(значение1; [значение2]; …)	=МАКСА(A1:A10)	Аналогично МИНА, но максимум.
НАИМЕНЬШИЙ(массив; k)	=НАИМЕНЬШИЙ(A1:A10; 3)	k-ое наименьшее значение (k=1 → МИН).
НАИБОЛЬШИЙ(массив; k)	=НАИБОЛЬШИЙ(A1:A10; 2)	k-ое наибольшее значение (k=1 → МАКС).
ДИСП.Г(число1; [число2]; …)	=ДИСП.Г(A1:A10)	Дисперсия генеральной совокупности (деление на n).
ДИСП.В(число1; [число2]; …)	=ДИСП.В(A1:A10)	Дисперсия выборки (деление на n-1).
СТАНДОТКЛОН.Г(число1; [число2]; …)	=СТАНДОТКЛОН.Г(A1:A10)	Стандартное отклонение генеральной совокупности (=КОРЕНЬ(ДИСП.Г(...))).
СТАНДОТКЛОН.В(число1; [число2]; …)	=СТАНДОТКЛОН.В(A1:A10)	Стандартное отклонение выборки.

7.2 Частотные и ранговые функции

Функция	Синтаксис	Описание
СЧЁТ(значение1; [значение2]; …)	=СЧЁТ(A1:A10)	Количество числовых ячеек (включая даты, время, логические в числовом виде).
СЧЁТЗ(значение1; [значение2]; …)	=СЧЁТЗ(A1:A10)	Количество непустых ячеек (включая текст, ошибки, логические).
СЧИТАТЬПУСТОТЫ(диапазон)	=СЧИТАТЬПУСТОТЫ(A1:A10)	Количество пустых ячеек (включая "", но не ячейки с формулой, возвращающей "").
РАНГ.РВ(число; ссылка; [порядок])	=РАНГ.РВ(A2; A$2:A$100; 0)	Ранг числа в списке: 0 — по убыванию (макс=1), 1 — по возрастанию. При совпадениях — одинаковый ранг («с занижением»: 1, 1, 3). Excel 2010+.
РАНГ.СР(число; ссылка; [порядок])	=РАНГ.СР(A2; A$2:A$100; 0)	Ранг со средним значением при совпадениях (1,5; 1,5; 3).
ЧАСТОТА(массив_данных; массив_интервалов)	=ЧАСТОТА(A1:A100; {10;20;30})	Возвращает вертикальный массив количества значений ≤ каждому порогу. Требует ввода как формулы массива (Ctrl+Shift+Enter) в старых Excel; в Excel 365 — динамический массив.

---

8. Функции поиска и ссылок

8.1 Прямой и обратный поиск

Функция	Синтаксис	Описание
ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])	=ВПР("Яблоки"; A2:D100; 3; ЛОЖЬ)	Вертикальный поиск. ЛОЖЬ — точное совпадение; ИСТИНА — приблизительное (требует сортировки по первому столбцу).
ГПР(искомое_значение; таблица; номер_строки; [интервальный_просмотр])	=ГПР("Q1"; A1:D5; 3; ЛОЖЬ)	Горизонтальный поиск (редко используется).
ИНДЕКС(массив; номер_строки; [номер_столбца])	=ИНДЕКС(A2:D100; 5; 3) → значение в 5-й строке, 3-м столбце диапазона.
ПОИСКПОЗ(искомое_значение; просматриваемый_массив; [тип_сопоставления])	=ПОИСКПОЗ("Яблоки"; A2:A100; 0)	Возвращает номер позиции (в массиве), а не значение. Тип: 0 — точное, 1 — ≤ (сортировка по возрастанию), -1 — ≥ (сортировка по убыванию).

Рекомендация: Сочетание ИНДЕКС + ПОИСКПОЗ предпочтительнее ВПР:

• Не зависит от порядка столбцов;
• Может искать в любом направлении (влево включительно);
• Эффективнее при обновлении структуры таблицы.

Пример:
=ИНДЕКС(C2:C100; ПОИСКПОЗ("Яблоки"; A2:A100; 0))
эквивалентен =ВПР("Яблоки"; A2:C100; 3; ЛОЖЬ), но безопаснее.

8.2 Расширенные функции поиска (Excel 2016+)

Функция	Синтаксис	Описание
ПОИСКПОЗ(искомое_значение; массив; [тип]; [режим_поиска])	=ПОИСКПОЗ("Яблоки"; A2:A100; 0; 2)	Новая версия (Excel 365/2021): параметр режим_поиска (1 — с начала, -1 — с конца, 2 — двоичный поиск по возрастанию, -2 — по убыванию).
ВПР и ГПР устаревают в пользу XПОИСК (см. ниже).

8.3 Функции динамического поиска (Excel 365 / 2021)

Функция	Синтаксис	Описание
XПОИСК(искомое_значение; массив_или_диапазон; [если_не_найдено]; [режим_соответствия]; [режим_поиска])	=XПОИСК("Яблоки"; A2:A100; "Нет")	Универсальный аналог ПОИСКПОЗ, возвращает значение, а не позицию. Поддерживает регулярные выражения (через ~), поиск с конца, двоичный поиск.
ВЫБОР(номер; значение1; [значение2]; …)	=ВЫБОР(2; "Пн"; "Вт"; "Ср") → "Вт"	Возвращает значение из списка по индексу (1–254). Полезен для замены вложенных ЕСЛИ.
СМЕЩ(ссылка; смещение_по_строкам; смещение_по_столбцам; [высота]; [ширина])	=СУММ(СМЕЩ(A1; 1; 0; 10; 1))	Возвращает ссылку, смещённую от исходной. Часто используется в динамических диапазонах, но нестабильна (пересчитывается при любом изменении листа). Предпочтительнее ИНДЕКС.

8.4 Работа с гиперссылками и внешними данными

Функция	Синтаксис	Описание
ГИПЕРССЫЛКА(адрес; [дружественное_имя])	=ГИПЕРССЫЛКА("#'Лист2'!A1"; "К Листу2")	Создаёт кликабельную ссылку внутри книги или на внешний ресурс. # — внутренняя ссылка.
ДВССЫЛ(ссылка_как_текст; [стиль_A1])	=ДВССЫЛ("Лист"&A1&"!B2")	Преобразует текст в ссылку. Мощно, но нестабильна (пересчёт при любом изменении). Не работает в Excel Online.

---

9. Работа с массивами

9.1 Классические массивные формулы (Ctrl+Shift+Enter)

• До Excel 365 требовали ввода как формулы массива (Ctrl+Shift+Enter).
• Пример: вычислить сумму квадратов без промежуточного столбца:
  {=СУММ(A1:A10^2)} → ввести =СУММ(A1:A10^2), затем Ctrl+Shift+Enter.
  Excel добавляет фигурные скобки {} (нельзя вводить вручную).
• Ограничения:
  • Нельзя редактировать отдельную ячейку в результате;
  • При изменении размера — требуется пересоздание.

9.2 Динамические массивы (Excel 365 / 2021)

Новые функции, возвращающие массивы, автоматически «разливаются» в соседние ячейки (spill range). Если диапазон занят — ошибка #ЗАПОЛН!.

Функция	Синтаксис	Описание
ФИЛЬТР(массив; включить; [если_пусто])	=ФИЛЬТР(A2:C100; B2:B100>100; "Нет данных")	Возвращает отфильтрованные строки.
СОРТ(массив; [номер_столбца]; [порядок]; [по_строкам])	=СОРТ(A2:C100; 2; 1)	Сортирует по указанному столбцу (1 — по возрастанию).
СОРТПО(массив; по_массиву1; [порядок1]; …)	=СОРТПО(A2:A100; B2:B100; -1)	Сортирует один массив по значениям другого.
УНИКАЛЬН(массив; [по_строкам]; [встречается_только_один_раз])	=УНИКАЛЬН(A2:A100)	Извлекает уникальные значения. Второй параметр: ЛОЖЬ (по умолч.) — по столбцам, ИСТИНА — по строкам. Третий: ИСТИНА — только уникальные (не повторяющиеся).
ВЫБОРСТОЛБЦА(массив; номер_столбца1; [номер_столбца2]; …)	=ВЫБОРСТОЛБЦА(A2:C100; 3; 1)	Возвращает указанные столбцы в заданном порядке.
ВЫБОРСТРОКИ(массив; номер_строки1; [номер_строки2]; …)	=ВЫБОРСТРОКИ(A2:C100; 1; 5; 10)	Возвращает указанные строки.
ПОСЛ(массив; нач_строка; нач_столбец; [высота]; [ширина])	=ПОСЛ(A2:D100; 3; 2; 5; 2)	Извлекает подмассив (аналог ИНДЕКС для диапазонов).
РДИАГ(массив)	=РДИАГ(A1:D4)	Возвращает главную диагональ квадратной матрицы как столбец.
ТРАНСП(массив)	=ТРАНСП(A1:C5)	Транспонирует (строки ↔ столбцы). В динамических массивах — автоматический разлив.

9.3 Операторы для массивов

Оператор	Пример	Описание
@	=@A1:A10	Неявное пересечение (возвращает значение на пересечении строки/столбца текущей ячейки и диапазона). Используется Excel автоматически при необходимости скаляра.
{1;2;3}	{=A1:A3*{10;20;30}}	Вертикальный массив-константа.
{1:2:3}	{=A1:C1*{10:20:30}}	Горизонтальный массив-константа (в русской Excel — двоеточие :).
#	A1#	Ссылка на весь динамический диапазон (spill range), начиная с A1.

---

10. Области и пересечения

• Диапазон может состоять из нескольких областей, разделённых точкой с запятой в формуле:
  =СУММ(A1:A5; C1:C5; E1:E5)
• Пересечение двух диапазонов обозначается пробелом:
  =СУММ(B2:D10 B5:F8) → сумма ячеек на пересечении (B5:D8).
  Если пересечения нет — ошибка #ПУСТО!.
• Полезно при работе со строками/столбцами:
  =ИНДЕКС(A:E; 0; 3) → весь третий столбец (C:C);
  =ИНДЕКС(A:E; 5; 0) → вся пятая строка.

---

11. Финансовые функции

Общие соглашения:

• ПС (Приведённая стоимость) — текущая стоимость потока платежей (вложение: отрицательно, доход: положительно).
• БС (Будущая стоимость) — итоговая сумма после всех платежей.
• ПЛТ (Платёж) — регулярный платёж (аннуитет).
• Ставка — процент за период (годовая / число периодов в году).
• КПЕР — общее число периодов.
• Тип — 0 (платёж в конце периода, по умолчанию) или 1 (в начале).

Функция	Синтаксис	Описание
ПС(ставка; кпер; плт; [бс]; [тип])	=ПС(0,08/12; 12*5; -1000)	Текущая стоимость аннуитета (например, сумма кредита под 8% годовых, 5 лет, платёж 1000 ₽ в месяц).
БС(ставка; кпер; плт; [пс]; [тип])	=БС(0,1/12; 12*10; -5000; 0; 0)	Будущая стоимость вклада (5000 ₽/мес, 10 лет, 10% годовых).
ПЛТ(ставка; кпер; пс; [бс]; [тип])	=ПЛТ(0,09/12; 12*20; 2000000)	Ежемесячный платёж по ипотеке (2 млн, 9% годовых, 20 лет).
ОСПЛТ(ставка; период; кпер; пс; [бс]; [тип])	=ОСПЛТ(0,09/12; 1; 12*20; 2000000)	Основной долг в k-м платеже (без процентов).
ПРПЛТ(ставка; период; кпер; пс; [бс]; [тип])	=ПРПЛТ(0,09/12; 1; 12*20; 2000000)	Проценты в k-м платеже.
КПЕР(ставка; плт; пс; [бс]; [тип])	=КПЕР(0,12/12; -10000; 500000)	Сколько месяцев потребуется, чтобы погасить долг в 500 000 ₽ при платеже 10 000 ₽ и 12% годовых.
СТАВКА(кпер; плт; пс; [бс]; [тип]; [предположение])	=СТАВКА(60; -10000; 500000)	Эффективная процентная ставка (месячная).
ЧПС(ставка; значение1; [значение2]; …)	=ЧПС(0,1; B2:B6) + A1	Чистая приведённая стоимость неравномерных потоков. Не включает начальные инвестиции (добавляются отдельно).
ЧИСТНЗ(ставка; значения; даты)	=ЧИСТНЗ(0,1; A2:A6; B2:B6)	ЧПС с произвольными датами платежей (более точно, чем ЧПС). Excel 2007+.
ВСД(значения; [предположение])	=ВСД(A1:A6)	Внутренняя норма доходности (IRR) для равномерных периодов. Требует хотя бы один отрицательный и один положительный поток.
ЧИСТВНДОХ(значения; даты; [предположение])	=ЧИСТВНДОХ(A2:A6; B2:B6)	IRR с произвольными датами (XIRR). Excel 2007+.
АПЛ(стоимость; остаток; время_эксплуатации)	=АПЛ(100000; 10000; 5)	Амортизация линейным методом за период (SLN). (стоимость - остаток) / время.
АМГД(стоимость; остаток; время_эксплуатации; период)	=АМГД(100000; 10000; 5; 1)	Амортизация методом уменьшаемого остатка (SYD). Больше амортизации в начале.
ПУО(стоимость; остаток; время_эксплуатации; начальный_период; конечный_период; [коэффициент]; [без_переключения])	=ПУО(100000; 10000; 5; 0; 1)	Амортизация методом двойного уменьшаемого остатка (DB/DD). Коэффициент по умолчанию = 2 (для линейного переключения — без_переключения=ЛОЖЬ).
ДДОБ(стоимость; остаток; время_эксплуатации; период; [коэффициент])	=ДДОБ(100000; 10000; 5; 1)	То же, что ПУО, но для одного периода (DDB).

Важно: Все финансовые функции предполагают согласованность периодов: если ставка годовая — КПЕР должно быть в годах; если месячная — в месяцах.

---

12. Инженерные функции

12.1 Преобразование систем счисления

Функция	Синтаксис	Описание
ДВССЫЛ.ДВ(число; [разрядность])	=ДВССЫЛ.ДВ(10; 8) → 00001010	Десятичное → двоичное.
ДВССЫЛ.ВОСЬМ(число; [разрядность])	=ДВССЫЛ.ВОСЬМ(10) → 12	Десятичное → восьмеричное.
ДВССЫЛ.ШЕСТН(число; [разрядность])	=ДВССЫЛ.ШЕСТН(255) → FF	Десятичное → шестнадцатеричное.
ДВ.В(двоичное_число)	=ДВ.В("1010") → 10	Двоичное → десятичное.
ВОСЬМ.В(восьмеричное_число)	=ВОСЬМ.В("12") → 10	Восьмеричное → десятичное.
ШЕСТН.В(шестнадцатеричное_число)	=ШЕСТН.В("FF") → 255	Шестнадцатеричное → десятичное.
ДВ.ВОСЬМ(двоичное_число)	=ДВ.ВОСЬМ("1010") → 12	Двоичное → восьмеричное.
ДВ.ШЕСТН(двоичное_число)	=ДВ.ШЕСТН("1010") → A	Двоичное → шестнадцатеричное.
ВОСЬМ.ДВ(восьмеричное_число)	=ВОСЬМ.ДВ("12") → 1010	Восьмеричное → двоичное.
ВОСЬМ.ШЕСТН(восьмеричное_число)	=ВОСЬМ.ШЕСТН("12") → A	Восьмеричное → шестнадцатеричное.
ШЕСТН.ДВ(шестнадцатеричное_число)	=ШЕСТН.ДВ("A") → 1010	Шестнадцатеричное → двоичное.
ШЕСТН.ВОСЬМ(шестнадцатеричное_число)	=ШЕСТН.ВОСЬМ("A") → 12	Шестнадцатеричное → восьмеричное.

Поддерживают отрицательные числа (в дополнительном коде, 10/32/64 бита).
Диапазоны:

• Двоичное: −512 до 511 (10 бит), до −2⁶³+1 … 2⁶³−1 (64 бита).
• Шестнадцатеричное: до 40 шестнадцатеричных цифр (160 бит), но Excel использует 64-битную арифметику.

12.2 Комплексные числа

Функция	Синтаксис	Описание
КОМПЛЕКСН(действительная; мнимая; [суффикс])	=КОМПЛЕКСН(3; 4) → 3+4i	Создаёт комплексное число. Суффикс: "i" (по умолч.) или "j".
ДЕЙСТВ(комплексное_число)	=ДЕЙСТВ("3+4i") → 3	Действительная часть.
МНИМ(комплексное_число)	=МНИМ("3+4i") → 4	Мнимая часть.
МНИМ.СОПР(комплексное_число)	=МНИМ.СОПР("3+4i") → 3-4i	Комплексно-сопряжённое.
МНИМ.АРГУМЕНТ(комплексное_число)	=МНИМ.АРГУМЕНТ("3+4i") → 0,927 рад	Аргумент (угол в радианах).
МНИМ.МОДУЛЬ(комплексное_число)	=МНИМ.МОДУЛЬ("3+4i") → 5	Модуль (√(a²+b²)).
МНИМ.СЛОЖ(число1; [число2]; …)	=МНИМ.СЛОЖ("3+4i"; "1+2i") → 4+6i	Сложение.
МНИМ.РАЗН(число1; число2)	=МНИМ.РАЗН("3+4i"; "1+2i") → 2+2i	Вычитание.
МНИМ.ПРОИЗВ(число1; [число2]; …)	=МНИМ.ПРОИЗВ("3+4i"; "1+2i") → −5+10i	Умножение.
МНИМ.ДЕЛ(числитель; знаменатель)	=МНИМ.ДЕЛ("3+4i"; "1+2i") → 2,2−0,4i	Деление.
МНИМ.EXP(комплексное_число)	=МНИМ.EXP("1+i")	e^(a+bi) = e^a·(cos b + i·sin b).
МНИМ.LN(комплексное_число), МНИМ.LOG10, МНИМ.LOG2, МНИМ.СТЕПЕНЬ	—	Логарифмы и возведение в степень.

12.3 Прочие инженерные функции

Функция	Синтаксис	Описание
ИНЖДОБ(x)	=ИНЖДОБ(0,5)	Интегральный синус: ∫₀ˣ sin(t)/t dt.
ИНЖКОРЕНЬ(x)	=ИНЖКОРЕНЬ(2)	Обратная функция к ИНЖДОБ.
ПРЕОБР(число; исходная_единица; конечная_единица)	=ПРЕОБР(100; "см"; "м") → 1	Преобразование единиц измерения (длина, масса, время, давление, энергия, температура и др.). Полный список — в справке.
ДЕЛТА(число1; [число2])	=ДЕЛТА(5; 5) → 1; =ДЕЛТА(5) → 1	Функция Кронекера: 1, если равны; 0 — иначе. число2 по умолчанию = 0.
БЕССЕЛЬ.J(x; n)	=БЕССЕЛЬ.J(2; 1)	Функция Бесселя первого рода Jₙ(x).
БЕССЕЛЬ.Y(x; n)	=БЕССЕЛЬ.Y(2; 1)	Функция Бесселя второго рода Yₙ(x) (при x>0).
ГАММА(число)	=ГАММА(5) → 24 (4!)	Гамма-функция: Γ(n) = (n−1)! для целых > 0. Excel 2013+.
ОШИБКА.Ф(x) / ОШИБКА.ДОП(x)	=ОШИБКА.Ф(1)	Функция ошибок erf(x) и erfc(x) = 1 − erf(x). Excel 2010+.

---

13. Информационные функции

Функция	Синтаксис	Описание
ТИП.ОШ(значение)	=ТИП.ОШ(1/0) → 2	Возвращает код ошибки:
1 = #ПУСТО!,
2 = #ДЕЛ/0!,
3 = #ЗНАЧ!,
4 = #ССЫЛ!,
5 = #ЧИСЛО!,
6 = #Н/Д,
7 = #ИМЯ?,
8 = #ПУСТО! (пересечение пустых областей),
0 или #Н/Д — без ошибки.
ЕОШИБКА(значение)	=ЕОШИБКА(A1)	ИСТИНА, если значение — любая ошибка.
ЕНЕОШИБКА(значение)	=ЕНЕОШИБКА(A1)	ИСТИНА, если нет ошибки. Excel 2007+.
ЕНД(значение)	=ЕНД(ВПР("X";A:B;2;ЛОЖЬ))	ИСТИНА, только если #Н/Д.
ЕПУСТО(значение)	=ЕПУСТО(A1)	ИСТИНА, если ячейка действительно пуста (не "").
ЕНЕТЕКСТ(значение)	=ЕНЕТЕКСТ(A1)	ИСТИНА, если не текст (число, ошибка, логическое, пусто).
ЕТЕКСТ(значение)	=ЕТЕКСТ(A1)	ИСТИНА, если текст (включая "").
ЕЧИСЛО(значение)	=ЕЧИСЛО(A1)	ИСТИНА, если число (включая даты, время, логические в числовом виде).
ЕЛОГИЧ(значение)	=ЕЛОГИЧ(A1)	ИСТИНА, если ИСТИНА/ЛОЖЬ.
ЕФОРМУЛА(ссылка)	=ЕФОРМУЛА(A1)	ИСТИНА, если в ячейке формула. Excel 2013+.
ЯЧЕЙКА(вид_информации; [ссылка])	=ЯЧЕЙКА("адрес"; A1)	Возвращает информацию о ячейке:
"адрес", "строка", "столб", "содержимое", "тип" (b — пусто, l — текст, v — значение), "цвет" (1 — цвет текста задан вручную), "защита" и др.
ТИП(значение)	=ТИП(42) → 1; =ТИП("Текст") → 2	Тип данных:
1 = число,
2 = текст,
4 = логическое,
16 = ошибка,
64 = массив.
ИНФОРМ(тип)	=ИНФОРМ("ОС")	Системная информация:
"ОС" — Windows,
"ВЫПУСК" — версия Excel,
"ПОВТОР" — число итераций,
"ИТЕРАЦИИ" — включены ли итерации.
СОВПАД(текст1; текст2)	=СОВПАД("Яблоко"; "яблоко") → ИСТИНА	Сравнение без учёта регистра и форматирования. =ТЕКСТ1=ТЕКСТ2 — с учётом регистра.
ФОРМУЛА.ТЕКСТ(ссылка)	=ФОРМУЛА.ТЕКСТ(A1)	Возвращает формулу из ячейки как текст (если есть). Excel 2013+.
ЛИСТ(значение)	=ЛИСТ("Лист1")	Номер листа в книге (1 — первый). Excel 2013+.
ЛИСТЫ([ссылка])	=ЛИСТЫ()	Общее число листов в книге.

---

14. Функции для работы с базами данных

Требования к структуре данных («база»):

• Первая строка — заголовки полей (должны быть уникальны).
• Данные — однородные, без пустых строк внутри.
• Критерии задаются в отдельном диапазоне (минимум 2 строки: заголовок + условие).

Функция	Синтаксис	Описание
БДСУММ(база; поле; критерии)	=БДСУММ(A1:D100; "Продажи"; F1:F2)	Сумма по критериям. поле — имя столбца или номер (3).
БДСРЗНАЧ(база; поле; критерии)	—	Среднее по критериям.
БДМИН(база; поле; критерии)	—	Минимум.
БДМАКС(база; поле; критерии)	—	Максимум.
БДПРОИЗВЕД(база; поле; критерии)	—	Произведение.
БДСЧЁТ(база; [поле]; критерии)	—	Подсчёт числовых значений в поле по критериям. Если поле опущено — все строки.
БДСЧЁТА(база; [поле]; критерии)	—	Подсчёт непустых значений (включая текст).
БДПОЛЯ(база)	=БДПОЛЯ(A1:D1)	Количество полей (столбцов) в базе. Excel 2013+.

Пример критериев (диапазон F1:F3):
F1: "Товар"
F2: "=Яблоки"
F3: ">100" — для числового поля (например, "Цена" в соседнем столбце G1).
Для логического И — условия в одной строке (F2: "Яблоки", G2: ">100").
Для ИЛИ — условия в разных строках (F2: "Яблоки", F3: "Груши").

---

15. Функции совместимости (устаревшие)

Предназначены для обратной совместимости с Excel 2003 и ранее. В новых версиях рекомендуется использовать современные аналоги (см. Части 1–2).

Устаревшая (Excel ≤2003)	Современная (Excel ≥2007)	Примечание
ДИСП(число1; …)	ДИСП.В(число1; …)	Выборочная дисперсия.
ДИСПА(значение1; …)	ДИСПА(значение1; …)	То же, но с текстом/логикой.
ДИСПР(число1; …)	ДИСП.Г(число1; …)	Генеральная дисперсия.
СТАНДОТКЛОН(число1; …)	СТАНДОТКЛОН.В(число1; …)	Выборочное стандартное отклонение.
СТАНДОТКЛОНА(значение1; …)	СТАНДОТКЛОНА(значение1; …)	С текстом/логикой.
СТАНДОТКЛОНП(число1; …)	СТАНДОТКЛОН.Г(число1; …)	Генеральное.
РАНГ(число; ссылка; [порядок])	РАНГ.РВ(число; ссылка; [порядок])	«С занижением».
ПРОЦЕНТРАНГ(массив; x; [точность])	ПРОЦЕНТИЛЬ.ИСКЛ(массив; k)	Устаревшее поведение соответствует ПРОЦЕНТИЛЬ.ВКЛ. Excel 2010+.
МЕДИАНА.ПОЛИГ	—	Удалена в Excel 2010 (не имела практического применения).

---

16. Встроенные константы и системные значения

Константа	Значение / Описание
ИСТИНА	Логическая константа (не функция — без скобок). Альтернатива: 1=1.
ЛОЖЬ	Логическая константа. Альтернатива: 1=0.
ПИ()	3,14159265358979 (15 знаков).
E	Основание натурального логарифма: =EXP(1) → 2,71828182845905.
ЗНАЧ_ПУСТО	="" — пустая текстовая строка (не пустая ячейка!).
ИМЯ.ПОЛЬЗ()	Возвращает имя текущего пользователя Windows (через ИНФОРМ("пользователь")).
СЕГОДНЯ()	Текущая дата (без времени).
ТДАТА()	Текущие дата и время.
ОС()	Возвращает "Windows" (в Excel).

---

17. Приоритет операторов в формулах

Формулы вычисляются слева направо с учётом приоритета:

Приоритет	Оператор	Описание	Пример
1	: (двоеточие)	Оператор диапазона	A1:B10
2	(пробел)	Оператор пересечения	A1:C10 B5:D15
3	, (запятая)	Объединение областей	A1:A5, C1:C5
4	- (унарный минус)	Отрицание	=-A1
5	%	Процент	=A1% → A1/100
6	^	Возведение в степень	=2^3 → 8
7	* и /	Умножение и деление	=6/2*3 → 9 (слева направо)
8	+ и -	Сложение и вычитание	=10-3+2 → 9
9	&	Объединение текста	="A"&"B" → "AB"
10	= < > <= >= <>	Операторы сравнения	=A1>5 → ИСТИНА/ЛОЖЬ

Примечание: Скобки () изменяют порядок вычисления:
=6/2*3 → 9, но =6/(2*3) → 1.

---

18. Основы динамических массивов

18.1 Что такое динамический массив (spill)?

Начиная с Excel 365 (2018–2019), формулы, возвращающие массив, автоматически «разливаются» (spill) в соседние ячейки, образуя диапазон результата (spill range).

Пример:

=ПОСЛЕД(1; 5)

→ В ячейке ввода появляется 1, а справа автоматически заполняются 2, 3, 4, 5 (горизонтальный массив из 5 элементов).

18.2 Операторы и синтаксис

Элемент	Обозначение	Описание
#	A1#	Ссылка на весь spill-диапазон, начиная с A1. Эквивалент ДИНАММАССИВ(A1).
@	@A1:A10	Неявное пересечение (implicit intersection). Excel автоматически добавляет @, когда ожидается скаляр, а передан массив. Возвращает значение на пересечении строки/столбца текущей ячейки и диапазона.
{}	{=формула}	Не используется вручную в Excel 365. Ранее — признак формулы массива (Ctrl+Shift+Enter). Теперь фигурные скобки появляются только у устаревших массивных формул.

18.3 Ошибки, связанные с динамическими массивами

Ошибка	Причина	Решение
#ЗАПОЛН!	В spill-диапазоне есть непустая ячейка (включая "", формулу, форматирование).	Очистить мешающие ячейки; использовать СМЕЩ, ИНДЕКС, ВЫБОРСТРОКИ для ограничения размера.
#ССЫЛ!	Spill выходит за границы листа (например, массив 1048576×2).	Ограничить размер входных данных.
#ЧИСЛО!	Некорректный размер (например, ВЫБОРСТРОКИ(массив; 0)).	Проверить индексы — должны быть ≥1.

18.4 Контроль размера выходного массива

• Чтобы ограничить высоту/ширину, комбинируйте с ИНДЕКС, ПОСЛ, ВЫБОРСТРОКИ, ВЫБОРСТОЛБЦА.
• Чтобы подавить spill и получить только первое значение — используйте @ явно:
  =@ФИЛЬТР(A:A; B:B>10) → только первая подходящая строка.

---

19. Вспомогательные функции динамических массивов

Функция	Синтаксис	Описание	Пример
ПУСТО()	=ПУСТО()	Возвращает пустое значение, которое не занимает место в spill-диапазоне (в отличие от ""). Используется внутри ЕСЛИ, ФИЛЬТР для «прозрачного» исключения.	=ЕСЛИ(A1>10; A1; ПУСТО()) — при A1≤10 ячейка остаётся визуально пустой и не мешает spill’у.
ОДИН()	=ОДИН()	Возвращает #ЗНАЧ! — используется как заглушка для тестирования обработки ошибок. Эквивалент 1/0.	=ЕСЛИОШИБКА(ОДИН(); "Ошибка") → "Ошибка".
ИТОГИ(номер_функции; ссылка1; [ссылка2]; …)	=ИТОГИ(9; A1:A10)	Выполняет агрегацию (сумма, среднее и др.), игнорируя скрытые строки и вложенные ИТОГИ. Поддерживает динамические диапазоны.	номер_функции: 1=СРЗНАЧ, 2=СЧЁТ, 3=СЧЁТЗ, 4=МАКС, 5=МИН, 6=ПРОИЗВЕД, 7=СТАНДОТКЛОН.В, 8=СТАНДОТКЛОН.Г, 9=СУММ и т.д.
ДВСМЕЩ(ссылка; смещение_по_строкам; смещение_по_столбцам; [высота]; [ширина])	=ДВСМЕЩ(A1; 0; 0; СЧЁТ(A:A); 1)	Возвращает динамический диапазон, растягивающийся по данным. Нестабильная функция (пересчитывается при любом изменении), предпочтительнее замена через ИНДЕКС + СЧЁТ.	Используется в сводных таблицах для источника данных.

Важно: ДВСМЕЩ — volatile (нестабильна). Альтернатива:

=ИНДЕКС(A:A; 1):ИНДЕКС(A:A; СЧЁТ(A:A))

— стабильная, не вызывает лишнего пересчёта.

---

20. Расширенные функции динамических массивов

20.1 LET — локальное связывание переменных

Позволяет назначить имя промежуточному результату внутри формулы. Повышает читаемость и производительность (вычисление один раз).

Синтаксис:

=LET(имя1; значение1; [имя2; значение2; …]; вычисление)

Пример — расчёт дисперсии без повторных вычислений:

=LET(
  данные; A1:A100;
  ср; СРЗНАЧ(данные);
  n; СЧЁТ(данные);
  СУММ((данные - ср)^2) / (n - 1)
)

Поддерживает до 126 пар (имя; значение). Имя — корректный идентификатор (без пробелов, не число, не совпадает с функцией).

20.2 LAMBDA — пользовательские функции без VBA

Создаёт именованную или анонимную функцию, которую можно использовать в формулах.

Синтаксис:

=LAMBDA([параметр1; параметр2; …]; тело)

Пример 1 — анонимная (в формуле):

=LAMBDA(x; x^2 + 2*x + 1)(5)
→ 36

Пример 2 — именованная (в Диспетчере имён):
Имя: КВУР
Формула: =LAMBDA(a; b; c; (-b + КОРЕНЬ(b^2 - 4*a*c)) / (2*a))
Использование: =КВУР(1; -3; 2) → 2

Ограничения:

• Нельзя вызывать себя рекурсивно без LET.
• Нельзя изменять внешнее состояние (только вычисление).
• Максимум 253 параметра.

20.3 MAP — поэлементное преобразование массива

Применяет LAMBDA к каждому элементу одного или нескольких массивов.

Синтаксис:

=MAP(массив1; [массив2]; …; LAMBDA(парам1; [парам2]; …; выражение))

Пример — безопасное деление (без #ДЕЛ/0!):

=MAP(A1:A10; B1:B10; LAMBDA(x; y; ЕСЛИ(y=0; ПУСТО(); x/y)))

20.4 REDUCE — свёртка массива в скаляр

Накапливает результат, применяя LAMBDA к каждому элементу и промежуточному итогу.

Синтаксис:

=REDUCE([начальное_значение]; массив; LAMBDA(аккум; текущий; выражение))

Пример — произведение всех положительных чисел:

=REDUCE(1; A1:A10; LAMBDA(acc; x; ЕСЛИ(x>0; acc*x; acc)))

20.5 SCAN — пошаговая свёртка (возвращает массив промежуточных значений)

Аналог REDUCE, но возвращает все промежуточные состояния.

Пример — кумулятивная сумма:

=SCAN(0; A1:A10; LAMBDA(acc; x; acc + x))

→ {A1; A1+A2; A1+A2+A3; …}

20.6 MAKEARRAY — генерация массива по правилу

Создаёт массив заданного размера, заполняя его через LAMBDA.

Синтаксис:

=MAKEARRAY(строки; столбцы; LAMBDA(строка; столбец; значение))

Пример — таблица умножения 5×5:

=MAKEARRAY(5; 5; LAMBDA(i; j; i*j))

20.7 ВЫБОРСТРОКИ и ВЫБОРСТОЛБЦА (уже упоминались в Части 2, но важны для динамики)

Функция	Пример	Примечание
ВЫБОРСТРОКИ(массив; номер1; [номер2]; …)	=ВЫБОРСТРОКИ(A1:C10; 1; 5; 10)	Возвращает строки №1, 5, 10. Номера могут быть массивом: =ВЫБОРСТРОКИ(A1:C10; {1;3;5}).
ВЫБОРСТОЛБЦА(массив; номер1; [номер2]; …)	=ВЫБОРСТОЛБЦА(A1:C10; 3; 1)	Возвращает столбцы в порядке 3, затем 1.

20.8 ПОСТРОИТЕЛЬ.ЗАПРОСОВ (QUERY — упрощённый SQL)

Устаревшее название — ДВСМЕЩ + ФИЛЬТР + СОРТ, но в Excel нет встроенной функции QUERY.
⚠️ Важно: ПОСТРОИТЕЛЬ.ЗАПРОСОВ — это не функция Excel, а название инструмента в Power Query.
В формулах для имитации QUERY используют:

=ФИЛЬТР(СОРТ(массив; 2; 1); (столбец1="X") * (столбец2>100))

Или — через LAMBDA с именованием.

---

21. Обработка ошибок в динамических массивах

Классические функции (ЕСЛИОШИБКА, ЕСЛИНАОШИБКА) работают поэлементно в массивах.

Сценарий	Решение
Подавить все ошибки в массиве	=ЕСЛИОШИБКА(ФИЛЬТР(...); ПУСТО()) — оставляет только корректные строки.
Оставить только ошибки	=ФИЛЬТР(массив; ЕОШИБКА(массив)) — но массив должен быть ссылкой на spill-диапазон. Лучше:
=ФИЛЬТР(A1#; ЕОШИБКА(A1#))
Заменить #Н/Д на 0 в вычислениях	=НАИМЕНЬШИЙ(ЕСЛИОШИБКА(массив; ""); 1) — но "" нарушает spill. Правильно:
=НАИМЕНЬШИЙ(ЕСЛИОШИБКА(массив; ПУСТО()); 1)
Проверить, содержит ли массив ошибки	=СУММПРОИЗВ(--ЕНЕОШИБКА(A1#)) < СЧЁТЗ(A1#) → ИСТИНА, если есть ошибки.

Важно: ПУСТО() — единственный способ «исчезать» из массива без нарушения структуры. "" остаётся в массиве как текст.

---

22. Совместимость с Excel 2019 и более старыми версиями

Возможность	Excel 365 / 2021	Excel 2019	Excel ≤2016
Динамические массивы (ФИЛЬТР, СОРТ, УНИКАЛЬН)	✅ Полная поддержка	❌ Нет	❌ Нет
LAMBDA, LET, MAP, REDUCE	✅	❌	❌
ПУСТО()	✅	✅ (Excel 2021+)	❌ (появилась в Excel 365 2020)
ДВСМЕЩ, ИНДЕКС+ПОИСКПОЗ	✅	✅	✅
Формулы массива (Ctrl+Shift+Enter)	✅ (но не рекомендуются)	✅	✅
XПОИСК, ВЫБОРСТРОКИ, ВЫБОРСТОЛБЦА	✅	❌	❌

Рекомендации по совместимости:

1. Избегайте ФИЛЬТР, СОРТ, УНИКАЛЬН, если файл может открываться в Excel 2019. Замена:
   • Фильтрация → ИНДЕКС+ПОИСКПОЗ+НАИМЕНЬШИЙ в массивной формуле (Ctrl+Shift+Enter).
   • Уникальные значения → сводная таблица или УДАЛИТЬ_ДУБЛИКАТЫ (в Power Query).
2. LAMBDA и LET — только для внутреннего использования в 365. Для общего доступа — разворачивайте в классические формулы.
3. Power Query — единственный кроссплатформенный способ сложной обработки (доступен в Excel 2010+ через надстройку, встроен в 2016+).

---

23. Анти-паттерны и лучшие практики

Проблема	Риск	Решение
Использование ДВСМЕЩ, СМЕЩ, ДВССЫЛ	Нестабильность, замедление пересчёта	Замена на ИНДЕКС, ВЫБОР, именованные диапазоны.
Вложенные ЕСЛИ > 3 уровней	Нечитаемость, ошибки	Использовать ВЫБОР, ПОИСКПОЗ+ИНДЕКС, XПОИСК или LAMBDA.
Жёстко заданные диапазоны (A1:A1000)	Рост файла, ошибки при добавлении данных	Использовать «умные таблицы» (Ctrl+T) или динамические имена (=ИНДЕКС(A:A;1):ИНДЕКС(A:A;СЧЁТ(A:A))).
Смешение текста и чисел в столбце	Ошибки в СУММ, СРЗНАЧ, сводных	Разделение на столбцы; валидация данных.
Ручное форматирование вместо стилей	Нарушение единообразия	Использовать «Стили ячеек» и «Условное форматирование».
Хранение данных и формул в одном диапазоне	Риск повреждения логики	Разделение: исходные данные → обработка (таблицы/Power Query) → отчёт (формулы ссылок).

---