Долгие годы в Excel действовало простое правило: одна формула — одна ячейка. Для задачи «покажи все заказы из Баку отдельным списком» требовались фильтр, копирование или громоздкие формулы массива. В Microsoft 365 есть динамические массивы: пишешь одну формулу, результат сам «разливается» на нужное число ячеек и обновляется при изменении источника.
Во всём уроке используем одну таблицу продаж. В A1:D9 заголовки Seller, Region, Product, Amount и 8 заказов: Aysel (Baku, Laptop, 1200), Murad (Ganja, Phone, 650), Leyla (Baku, Phone, 700), Elvin (Sumgait, Laptop, 1100), Nigar (Baku, Tablet, 480), Rauf (Ganja, Laptop, 1250), Aysel (Baku, Tablet, 520), Murad (Ganja, Laptop, 1150). Суммы в манатах.
Разлив (spill) и оператор `#`
Диапазон разливаОпределение
Ячейки, которые занимает результат формулы динамического массива. Формула записана только в первой ячейке, остальные обведены синей рамкой и вручную не редактируются.
- Введи в F2
=UNIQUE(B2:B9)— в F2:F4 появятся Baku, Ganja, Sumgait. F2#— ссылка на весь диапазон разлива:=COUNTA(F2#)→ 3. Появится новый регион —F2#вырастет вместе с ним.- Введи в G2
=SUMIF(B2:B9,F2#,D2:D9)— одна формула даёт три суммы: 2900, 3050, 1100. - Если в формуле из старой книги стоит
@, это значит «взять одно значение» (неявное пересечение).
Чтобы читать дальше, зарегистрируйся бесплатно
Остальные разделы урока, тест и заметки открываются с аккаунтом Educora. Регистрация занимает минуту, а прогресс сохраняется на всех твоих устройствах.
Фильтр, сортировка, уникальные значения: FILTER, SORT, SORTBY, UNIQUE
| Формула | Результат (разливается) |
|---|---|
=FILTER(A2:D9,B2:B9="Baku") | 4 заказа Баку: Aysel 1200, Leyla 700, Nigar 480, Aysel 520 |
=FILTER(A2:D9,B2:B9="Shaki","Заказов нет") | «Заказов нет» (без 3-го аргумента — #CALC!) |
=SORT(A2:D9,4,-1) | вся таблица по сумме по убыванию: Rauf 1250, Aysel 1200, Murad 1150… |
=SORTBY(A2:A9,D2:D9,-1) | только имена, но отсортированные по сумме (столбца сортировки в результате нет) |
=UNIQUE(A2:A9) | 6 продавцов: Aysel, Murad, Leyla, Elvin, Nigar, Rauf |
=UNIQUE(A2:A9,,TRUE) | только встречающиеся ровно один раз: Leyla, Elvin, Nigar, Rauf |
=SORT(UNIQUE(C2:C9)) | Laptop, Phone, Tablet |
=TAKE(SORT(A2:D9,4,-1),3) | 3 крупнейших заказа (топ-3) |
Несколько условий в FILTER
Задача
1) Покажи заказы из Баку дороже 600 ₼. 2) Покажи все заказы Гянджи или заказы планшетов (Tablet) из любого региона.
Показать решениеСкрыть решение
Решение
1) И = умножение:
=FILTER(A2:D9,(B2:B9="Baku")*(D2:D9>600)) → Aysel 1200 и Leyla 700 (2 строки).2) ИЛИ = сложение:
=FILTER(A2:D9,(B2:B9="Ganja")+(C2:C9="Tablet")) → Murad 650, Nigar 480, Rauf 1250, Aysel 520, Murad 1150 (5 строк).Каждая скобка даёт массив из 8 единиц и нулей; остаются строки, где результат не равен 0.
Интерактив
SEQUENCE и LET
Где
- rows, columns
- сколько строк и столбцов заполнить (столбцов по умолчанию 1)
- start, step
- начальное значение и шаг (оба по умолчанию 1)
=SEQUENCE(5)→ 1, 2, 3, 4, 5 (для нумерации строк).=SEQUENCE(3,4)→ блок 3 × 4 с числами от 1 до 12.=SEQUENCE(7,1,DATE(2026,9,28))→ даты одной недели, начиная с 28.09.2026 (задай ячейкам формат даты).=SEQUENCE(10,1,10,-1)→ обратный отсчёт от 10 до 1.
LET позволяет создавать переменные внутри формулы: =LET(имя1, значение1, имя2, значение2, …, результат). Если один и тот же фрагмент встречается в формуле несколько раз, он вычисляется только однажды, а формула становится читаемой.
Средний заказ Баку через LET
Задача
Найди среднюю сумму заказов Баку, используя LET, чтобы не писать FILTER дважды.
Показать решениеСкрыть решение
Решение
=SUM(FILTER(D2:D9,B2:B9="Baku"))/COUNT(FILTER(D2:D9,B2:B9="Baku")) — FILTER вычисляется дважды.С LET:
=LET(baku,FILTER(D2:D9,B2:B9="Baku"),SUM(baku)/COUNT(baku)).baku = {1200; 700; 480; 520}, сумма 2900, количество 4.Результат: 2900 / 4 = 725 ₼.
Подсказка: клавишами
Alt+Enter каждую пару LET можно перенести на новую строку в строке формул.LAMBDA: создай свою функцию
Проверь формулу в ячейке
Введи
=LAMBDA(price,vat,price*(1+vat))(100,0.18)— с аргументами в последних скобках результат равен 118 (цена с НДС 18%).Дай ей имя
Открой
Formulas › Defined Names › Name Manager › New.Name:WITHVAT,Refers to:=LAMBDA(price,vat,price*(1+vat)). НажмиOK.Используй как обычную функцию
Теперь в любом месте книги можно писать
=WITHVAT(D2,18%). Если логика изменится, исправишь её один раз вName Manager.
Name Manager (здесь хранятся имена LAMBDA)Ctrl+F3Запомни
- Формула динамического массива пишется в одной ячейке и разливается в соседние; на весь результат ссылаются как
F2#. - #SPILL! — на пути что-то есть, есть объединённые ячейки или формула стоит внутри таблицы Excel.
- В FILTER условие И записывают через
*, ИЛИ — через+; 3-й аргумент обрабатывает пустой результат. - SORT сортирует по номеру столбца, SORTBY — по другому диапазону; UNIQUE убирает повторы.
- LET создаёт переменные, а LAMBDA вместе с
Name Managerпозволяет создать свою именованную функцию.