До сих пор, чтобы отфильтровать таблицу, убрать дубли или отсортировать список, ты либо включал(а) фильтр руками, либо писал(а) формулу в одну ячейку и тянул(а) её вниз. В Excel 365 есть функции, которые делают это ОДНОЙ формулой сразу для целого диапазона — без ручных фильтров и без протягивания.
К концу темы ты сможешь:
— написать FILTER, чтобы отобрать нужные строки одной формулой;
— использовать UNIQUE, в том числе чтобы найти значения, встретившиеся РОВНО один раз;
— отсортировать результат через SORT и SORTBY (в том числе по чужому столбцу);
— сгенерировать ряд чисел через SEQUENCE;
— прочитать ссылку с # (разлив) и понять, откуда берётся ошибка #ПЕРЕПОЛНЕНИЕ!;
— узнать оператор @ и объяснить, зачем Excel добавляет его сам.
Вспомни: в главе 5 ты писал(а) код VBA, чтобы автоматизировать поиск и фильтрацию (тема 5.4). Здесь та же идея — «обработать много строк одной командой», — но без единой строчки кода: всё это делают обычные формулы листа.
Зачем формуле возвращать сразу много значений
Раньше в Excel формула, которая должна была вернуть НЕСКОЛЬКО значений сразу (а не одно число), вводилась особым способом — сочетанием клавиш Ctrl+Shift+Enter (такие формулы называют CSE-формулами, формулами массива). У них был жёсткий недостаток: результат имел ФИКСИРОВАННЫЙ размер, заданный заранее, — если данных стало больше или меньше, формулу приходилось перестраивать вручную.
В Excel 365 это в прошлом. Формула, которая возвращает массив значений, вводится ОБЫЧНЫМ Enter, а результат сам «разливается» (spill) на нужное количество соседних ячеек — сколько понадобится, столько и займёт. Функции ниже (FILTER, UNIQUE, SORT, SORTBY, SEQUENCE) — это и есть формулы динамического массива.
Проверь свою версию. Все функции этой темы — часть Excel 365 (подписка Microsoft 365). Если на работе или дома у тебя Excel 2016/2019/2021 (разовая покупка, без подписки) — этих функций там нет: попытка ввести
=FILTER(...)вернёт ошибку #ИМЯ? («имя не распознано»), а не подскажет причину. Если увидел(а) #ИМЯ? при вводе формулы из этой темы — сначала проверь версию (Файл → Учётная запись → О программе Excel), это не ошибка в формуле.
Таблица-пример (диапазон A2:C6), используется в объяснениях ниже: A B C 1 Товар Категория Цена 2 Ноутбук Электроника 45000 3 Мышь Электроника 800 4 Стол Мебель 12000 5 Стул Мебель 3000 6 Монитор Электроника 15000
Контрольный вопрос. Чем формула динамического массива (Excel 365) отличается от старой формулы массива (Ctrl+Shift+Enter)?
Подсказка: Подумай, что раньше приходилось нажимать особым сочетанием клавиш — и что стало не нужно.
Онлайн-проверка ответа появится позже
FILTER — выбираем нужные строки одной формулой
FILTER(array, include, [if_empty]) отбирает из array только те строки, где условие include истинно. Например, чтобы получить из таблицы выше только строки с категорией «Электроника»:
=FILTER(A2:C6, B2:B6="Электроника")Ноутбук Электроника 45000
Мышь Электроника 800
Монитор Электроника 15000Третий аргумент
[if_empty]— необязательный: что вернуть, если ни одна строка не подошла. Если условию не соответствует НИ ОДНА строка и[if_empty]не задан, Excel не может вернуть «пустой массив» и показывает ошибку #CALC!.
Задание. Что нужно вписать вместо пропуска? Ответ дай в виде ссылки на диапазон и условия, как в примере из материала (диапазон=»значение»).
Исходный код для этого задания:
Таблица «Сотрудники» в диапазоне A2:C7: столбцы Имя, Отдел, Зарплата. Нужна формула, которая вернёт только сотрудников отдела «Продажи»: =FILTER(A2:C7, ____)Подсказка: Условие сравнивает столбец «Отдел» (B) с нужным текстом — так же, как в примере темы сравнивали столбец «Категория» со значением «Электроника».
✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.
Онлайн-проверка ответа появится позже
Задание. Что вернёт эта формула? Впиши код ошибки.
Исходный код для этого задания:
=FILTER(A2:C7, B2:B7="Бухгалтерия") (в отделах компании бухгалтерии нет вообще, и третий аргумент не указан)Подсказка: Ни одна строка не подошла под условие, а вернуть «ничего» одной ячейкой FILTER не умеет.
✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.
Онлайн-проверка ответа появится позже
UNIQUE — только нужные значения
UNIQUE(array, [by_col], [exactly_once]) возвращает уникальные значения из диапазона. Для столбца «Категория» из таблицы выше (Электроника, Электроника, Мебель, Мебель, Электроника):
=UNIQUE(B2:B6)Электроника
МебельТретий аргумент
[exactly_once]— это НЕ то же самое, что «убрать дубликаты». Если поставить его в ИСТИНА, формула вернёт только значения, встретившиеся РОВНО один раз, — и «Электроника», и «Мебель» встречались НЕСКОЛЬКО раз, поэтому=UNIQUE(B2:B6, ЛОЖЬ, ИСТИНА)для этого столбца вернёт пустой результат: ни одна категория не встретилась ровно один раз.
Контрольный вопрос. Список гостей мероприятия: Аня, Борис, Аня, Виктор. Чем результат =UNIQUE(диапазон) будет отличаться от =UNIQUE(диапазон, ЛОЖЬ, ИСТИНА)?
Подсказка: Аня встретилась в списке дважды — подумай, что с ней сделает каждый из двух вариантов формулы.
Онлайн-проверка ответа появится позже
Задание. Что нужно вписать вместо пропуска?
Исходный код для этого задания:
Список посетителей выставки в A2:A20 (некоторые имена повторяются). Нужна формула, которая вернёт только тех, кто был на выставке РОВНО один раз: =UNIQUE(A2:A20, ЛОЖЬ, ____)Подсказка: Речь о том самом необязательном третьем аргументе — что он включает?
✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.
Онлайн-проверка ответа появится позже
SORT и SORTBY — сортируем результат сразу
SORT(array, [sort_index], [sort_order], [by_col]) сортирует диапазон ПО ЕГО ЖЕ СОБСТВЕННЫМ значениям: sort_index — номер столбца/строки внутри самого array, по которому сортируем. Например, отсортировать таблицу товаров по цене (3-й столбец), по убыванию:
=SORT(A2:C6, 3, -1)Ноутбук Электроника 45000
Монитор Электроника 15000
Стол Мебель 12000
Стул Мебель 3000
Мышь Электроника 800SORTBY(array, by_array1, [sort_order1], …) устроена иначе: она сортирует
arrayпо значениям ИЗ ДРУГОГО диапазонаby_array1, который может вообще не входить в результат. Например, отсортировать только названия товаров (A2:A6) по цене (C2:C6), хотя цена в результат не попадёт:
=SORTBY(A2:A6, C2:C6, -1)Ноутбук
Монитор
Стол
Стул
МышьКонтрольный вопрос. В чём главное отличие SORTBY от SORT?
Подсказка: Посмотри, откуда каждая функция берёт диапазон, по которому определяется порядок.
Онлайн-проверка ответа появится позже
Задание. Что нужно вписать на месте двух первых аргументов (через запятую, без пробелов)?
Исходный код для этого задания:
Таблица «Ученики»: A2:A9 — имена, B2:B9 — средний балл. Нужна формула, которая вернёт список ИМЁН (только столбец A), отсортированный по баллу (столбец B) по убыванию: =SORTBY(____, ____, -1)Подсказка: Первый аргумент — что возвращаем (имена); второй — по значениям ИЗ КАКОГО столбца сортируем (баллы).
✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.
Онлайн-проверка ответа появится позже
Задание. На каком месте по счёту (1, 2, 3…) окажется заказ 104 в результате этой формулы? Впиши число.
Исходный код для этого задания:
Таблица «Заказы»: A2:A6 — номер заказа (101,102,103,104,105), B2:B6 — сумма (2000, 500, 8000, 1200, 300). =SORTBY(A2:A6, B2:B6, -1)Подсказка: Сортировка идёт по убыванию суммы — сначала расставь все пять сумм от большей к меньшей, потом найди место заказа 104 среди них.
✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.
Онлайн-проверка ответа появится позже
SEQUENCE и разлив: работаем с результатом как с одним целым
SEQUENCE(rows, [columns], [start], [step]) генерирует ряд чисел нужного размера. Например, таблица 4 строки × 5 столбцов с числами от 1 до 20:
=SEQUENCE(4,5)1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20Когда формула «разливается» на несколько ячеек, к ней можно обратиться КАК К ОДНОМУ ЦЕЛОМУ — знаком # в конце ссылки на первую ячейку результата. Если в A2 стоит
=SEQUENCE(10)(разливается в A2:A11), то=СУММ(A2#)посчитает сумму всех 10 значений — и если формула в A2 изменится, скажем, на=SEQUENCE(20), ссылка через # сама подстроится под новый размер, без ручной правки диапазона.
Ошибка #ПЕРЕПОЛНЕНИЕ! (#SPILL!) возникает, когда формуле есть что «разлить», но на пути разлива уже что-то есть — Excel не может продавить результат через занятые ячейки. Excel подсвечивает мешающие ячейки; после их очистки или переноса формула разливается как задумано.
Задание. Напиши формулу целиком.
Исходный код для этого задания:
Нужна формула, которая создаст таблицу чисел из 3 строк и 2 столбцов, начиная с числа 100 с шагом 10.Подсказка: Порядок аргументов: строки, столбцы, начальное число, шаг.
✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.
Онлайн-проверка ответа появится позже
Задание. Что нужно вписать вместо пропуска?
Исходный код для этого задания:
В ячейке D2 стоит формула =SEQUENCE(12), она разливается в D2:D13. Нужна формула в другой ячейке, которая посчитает сумму всех 12 значений и будет сама подстраиваться, если размер SEQUENCE изменится. =СУММ(____)Подсказка: Нужна ссылка на ВЕСЬ разлившийся диапазон, а не только на первую ячейку D2.
✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.
Онлайн-проверка ответа появится позже
Задание. Какую ошибку покажет Excel в E5? Впиши код ошибки.
Исходный код для этого задания:
В ячейке E5 стоит формула динамического массива, которая должна разлиться на 3 ячейки вниз (E5:E7). Но в E6 уже вручную вписано число 10.Подсказка: На пути разлива стоит непустая ячейка — вспомни название этой ошибки.
✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.
Онлайн-проверка ответа появится позже
Неявное пересечение (@) — когда Excel незаметно возвращает старое поведение
Иногда при открытии старой формулы (написанной ещё до динамических массивов) Excel сам добавляет перед функцией символ @ — оператор неявного пересечения. Это сделано для обратной совместимости: старая формула должна продолжать возвращать ОДНО значение, как и раньше, а не внезапно «разлиться» на соседние ячейки.
=@OFFSET(A1:A2,1,1) ' с @ — одно значение, в одну ячейку
=OFFSET(A1:A2,1,1) ' без @ — весь диапазон-результат, разливаетсяЗадание. Какой символ нужно дописать перед OFFSET, чтобы вернуть только одно значение?
Исходный код для этого задания:
Формула =OFFSET(A1:A2,1,1) в новом Excel возвращает диапазон и разливается на соседние ячейки. Нужно, чтобы формула, как раньше, возвращала только ОДНО значение и не разливалась.Подсказка: Перечитай, как называется эта секция темы, и вспомни, какой символ Excel сам подставляет в старые формулы при открытии.
✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.
Онлайн-проверка ответа появится позже
Итог
Итог. FILTER(array, include, [if_empty]) отбирает строки по условию; без if_empty и без совпадений вернёт #CALC!. UNIQUE(array, [by_col], [exactly_once]) убирает дубли, а с exactly_once=ИСТИНА — оставляет только значения, встретившиеся РОВНО один раз (это не одно и то же). SORT сортирует массив по значениям из него же самого, SORTBY — по значениям из ДРУГОГО диапазона, и умеет несколько ключей сразу. SEQUENCE(rows, [columns], [start], [step]) генерирует ряд чисел. Результат формулы динамического массива «разливается» на соседние ячейки; ссылка с # (например, A2#) обращается ко всему разлившемуся диапазону сразу; #ПЕРЕПОЛНЕНИЕ! — когда путь разлива чем-то занят. Оператор @ возвращает старое поведение «одно значение вместо разлива» — Excel добавляет его сам в старые формулы.
Бонус для Google Таблиц
Если работаешь в Google Таблицах, а не в Excel. Полных аналогов FILTER/UNIQUE/SORT/SORTBY/SEQUENCE в Google Таблицах тоже есть (те же имена и почти та же логика), но есть и свои инструменты:
ARRAYFORMULA(выражение)заставляет одну формулу обработать сразу целый диапазон (например,=ARRAYFORMULA(A1:C1+A2:C2));QUERY(данные, запрос, [заголовки])— SQL-подобный язык запросов прямо в ячейке (например,=QUERY(A2:E6,"select avg(A) pivot B"));IMPORTRANGE(url_таблицы, "диапазон")подтягивает данные из ДРУГОЙ Google-таблицы по ссылке — при первом обращении появится ошибка #REF! с кнопкой «Разрешить доступ», без клика по ней формула не заработает.
← Назад: Глава 5 · ↑ В начало урока · ⌂ В начало курса · Вперёд: Google Apps Script →
