Excel и Google Workspace / Базовые формулы Excel
EXPAND в Excel: увеличить массив до заданного размера
EXPAND увеличивает массив до заданного числа строк и столбцов, заполняя новые ячейки pad_with или #N/A по умолчанию. Уменьшение размера даёт #VALUE!.
Формула
Восемь добавленных позиций отмечены дефисами.
Обозначения
- $A2:B3$
- исходный массив 2×2, не применяется
- $4$
- итоговое число строк, строка
- $3$
- итоговое число столбцов, столбец
- $"-"$
- текстовый заполнитель новых позиций, не применяется
Условия применения
Функция поддерживается в Microsoft 365 и Excel 2024 для Windows и Mac согласно справке Microsoft. Целевые размеры не должны быть меньше исходных. В примере английское имя, запятые между аргументами и текстовый заполнитель в кавычках; для разлива нужно свободное место.
Ограничения
EXPAND не обрезает массив и не размножает данные циклически. При размере меньше исходного возникает #VALUE!, без pad_with новые ячейки содержат #N/A. Заполнитель обозначает добавленные позиции, но не заменяет ошибки или пустые значения внутри исходного массива.
Подробное объяснение
EXPAND сохраняет исходный прямоугольник в левом верхнем углу результата. Аргументы размеров задают итоговую высоту и ширину, а не число добавляемых строк и столбцов. Если исходник имеет две строки, значение rows=4 добавляет две строки.
Новые позиции появляются справа от старых столбцов и ниже старых строк. Их число можно проверить как площадь нового прямоугольника минус площадь исходного. Для перехода 2×2 в 4×3 это 12−4=8 ячеек, а не десять.
Аргумент pad_with определяет содержимое добавленных ячеек. Если он не задан, используется #N/A. Дефис удобен для отображения отсутствия, ноль имеет числовой смысл, а пустая строка выглядит пустой, но является результатом формулы. Выбор влияет на последующие вычисления.
Попытка задать меньше строк или столбцов не означает обрезание. EXPAND возвращает ошибку размера. Для получения части массива используют другие функции, например TAKE или DROP; расширение и выбор подмассива отвечают на разные вопросы.
Внутренние значения исходного блока не меняются. Если там уже есть ошибка, заполнитель её не лечит. Поэтому проверка должна отдельно подтвердить сохранность исходных координат и количество новых позиций, а не только общий размер разлива.
Как пользоваться формулой
- Измерьте исходную высоту и ширину массива и выберите целевые размеры, не меньшие исходных значений.
- Определите смысл добавленных позиций и выберите заполнитель, отличимый от реального нулевого показателя.
- Введите EXPAND с итоговыми размерами в свободную область и проверьте исходный блок слева сверху.
- Пересчитайте добавленные ячейки как разность площадей прямоугольников и сверьте нижний правый угол.
Историческая справка
EXPAND относится к группе функций работы с массивами, анонсированной Microsoft в 2022 году. Появление явного расширения отражает переход от ручного заполнения соседних ячеек к вычислению прямоугольных результатов одной формулой. В отличие от функций выборки строк и столбцов, EXPAND сохраняет исходный блок целиком и задаёт форму окружающей области. В текущей документации эти различия закреплены правилами размера и отдельным аргументом заполнителя. История функции относится к развитию Excel и его языка динамических массивов, а не к открытию новой арифметической закономерности. Подсчёт добавленных ячеек остаётся обычной разностью площадей прямоугольников; программная функция лишь применяет выбранную форму и значение заполнения к табличным данным.
Историческая линия формулы
EXPAND — документированная функция Microsoft Excel для расширения формы массива. Её нельзя считать универсальным правилом изменения таблиц во всех программах. Арифметика числа добавленных ячеек элементарна, а поведение заполнителя и ошибок задаётся конкретной реализацией.
Пример
Диапазон A2:B3 содержит строки [10,20] и [30,40]. Нужно показать его в блоке четыре строки на три столбца, обозначив отсутствующие позиции дефисом. Формула =EXPAND(A2:B3,4,3,"-") даёт строки [10,20,-], [30,40,-], [-,-,-], [-,-,-]. Всего 4·3=12 позиций; исходных 2·2=4, поэтому добавлено 8. Проверка по частям: два дефиса справа от первых двух строк и шесть в двух новых строках дают 2+6=8. Числа 10,20,30,40 остаются на прежних относительных координатах. Дефисы здесь текстовые маркеры отсутствия, а не отрицательные числа и не пропущенные значения в исходных данных.
Частая ошибка
Не трактуйте rows и columns как количество добавляемых позиций. Не считайте правый нижний угол дважды при подсчёте новых ячеек. Не ожидайте обрезания при меньшем размере. Не используйте ноль как заполнитель, если он будет ошибочно принят за измеренное значение. Не ждите замены ошибок внутри исходного блока.
Практика
Задачи с решением
Дополнение до квадрата
Условие. Массив 2×2 расширяют до 3×3 заполнителем 0. Сколько нулевых позиций добавится?
Решение. Новый блок содержит 9 ячеек, исходный 4. Разность 9−4=5: две справа и три снизу.
Ответ. 5 новых ячеек
Уменьшение высоты
Условие. Что произойдёт при EXPAND(A2:B3,1,3,"-")?
Решение. Исходник имеет две строки, а запрошена одна. Уменьшение размера запрещено для EXPAND.
Ответ. Ошибка #VALUE!
Дополнительные источники
- https://support.microsoft.com/en-us/excel/functions/expand-function
- https://techcommunity.microsoft.com/blog/excelblog/announcing-new-text-and-array-functions/3186066
Связанные формулы
Excel и Google Workspace
TAKE в Excel: выбрать строки и столбцы массива
TAKE возвращает заданное число соседних строк или столбцов с начала или конца массива. Знак rows и columns задаёт сторону выбора, а исходная матрица и порядок её элементов сохраняются.
Excel и Google Workspace
DROP в Excel: исключить строки и столбцы массива
DROP удаляет заданное число соседних строк или столбцов с начала или конца массива и возвращает оставшуюся прямоугольную часть. Исходный диапазон при этом не изменяется.
Excel и Google Workspace
WRAPROWS в Excel: собрать список в строки фиксированной длины
WRAPROWS раскладывает одномерный вектор по строкам с заданным числом элементов, а недостающие позиции заполняет выбранным значением.