Функция Excel EXPAND для расширения массива до указанных размеров
В этом руководстве представлена новая функция динамического массива Excel 365, которая может расширять массив до нужных размеров с выбранным вами значением.
Хотите увеличить массив до указанного количества строк или столбцов, чтобы все аналогичные массивы на вашем листе имели одинаковый размер? В Excel 365 это можно сделать с помощью одной простой формулы. Познакомьтесь с совершенно новой функцией EXPAND, которая может увеличивать массив до любых заданных вами размеров.
Функция РАСШИРЕНИЯ Excel
Функция EXPAND в Excel предназначена для заполнения массива значением по вашему выбору до указанных размеров строки и столбца.
Синтаксис включает следующие аргументы:
РАСШИРИТЬ(массив, строки, [columns], [pad_with])
Где:
Массив (обязательно) — исходный массив.
Rows (необязательно) — количество строк в возвращаемом массиве. Если он опущен, новые строки не добавляются, и должен быть установлен аргумент столбцов.
Столбцы (необязательно) — количество столбцов в возвращаемом массиве. Если он опущен, новые столбцы не добавляются, и должен быть установлен аргумент rows.
Pad_with – значение для заполнения новых ячеек. Если опущено, по умолчанию используется #N/A.
Кончик. Чтобы удалить ненужные строки или столбцы из массива, используйте функцию DROP.
Наличие функции РАСШИРИТЬ
В настоящее время функция РАСШИРИТЬ доступна в Excel для Microsoft 365 (Windows и Mac) и Excel для Интернета.
Как использовать функцию РАСШИРИТЬ в Excel
Чтобы увеличить массив до нужного размера, постройте формулу EXPAND следующим образом:
- Для массива укажите диапазон ячеек или массив значений, возвращаемых другой формулой.
- Для строк и столбцов укажите положительные числа, превышающие количество строк и столбцов в исходном массиве. Обратите внимание, что эти аргументы определяют размеры результирующего массива, а не количество добавляемых строк или столбцов. В каждой конкретной формуле должны быть заданы либо строки, либо столбцы. Пропущенный аргумент по умолчанию соответствует соответствующему измерению (строкам или столбцам) исходного массива.
- Для pad_with укажите значение для заполнения новых ячеек, добавленных в массив. Если это текстовое значение, заключите его в двойные кавычки. Если это числовое значение, используйте его без кавычек. Если не указано, новые ячейки будут дополнены ошибкой #Н/Д.
Будучи функцией динамического массива, EXPAND необходимо вводить только в одну ячейку, и она автоматически распределяет результаты по стольким ячейкам, сколько указано в аргументах строк и столбцов.
Например, чтобы расширить массив C6:D13 до 12 строк и 3 столбцов, используйте следующую формулу:
=РАСШИРИТЬ(C6:D13, 12, 3)
Поскольку аргумент pad_with не задан, приведенная выше формула дополнит вновь добавленные ячейки ошибкой #N/A. Чтобы изменить поведение по умолчанию, укажите любое значение для аргумента pad_with. Например, вы можете дополнить новые значения массива дефисом:
=РАСШИРИТЬ(C6:D13, 12, 3, “-“)
Ниже вы найдете еще несколько примеров использования функции РАСШИРИТЬ в Excel для увеличения массива в определенном направлении.
Расширить массив до определенного количества строк
Чтобы расширить массив до нужного количества строк, определите аргумент rows и опустите столбцы.
Например, чтобы увеличить исходный массив до 12 строк, используйте следующую формулу:
=РАСШИРИТЬ(C6:D13, 12, , “-“)
Поскольку столбцы не должны расширяться, мы вводим запятую после строк, а после запятой вводим значение заполнения.
Расширить массив до определенного количества столбцов
Чтобы добавить больше столбцов в массив, определите аргумент столбцов и опустите строки.
Например, вот как вы можете расширить исходный массив вправо, чтобы он имел всего 4 столбца:
=РАСШИРИТЬ(A4:C15, , 4, “-“)
Как расширить массив влево и вверх
Функция EXPAND предназначена для расширения массива вправо и вниз. Чтобы добавить ячейки слева и сверху от исходного массива, вам придется разработать собственное решение.
Предположим, у вас есть массив значений в ячейке B4:C12. Вы стремитесь добавить 3 строки выше и 1 столбец слева от исходного массива. Чтобы лучше понять механику, мы сначала выполним каждую задачу по отдельности, а затем соберем все части вместе.
Добавить столбцы слева
Чтобы расширить массив по горизонтали, вы можете использовать функцию HSTACK, чтобы добавить заданное количество пустых столбцов слева от вашего массива. В этом примере мы добавляем 1 пустой столбец, используя эту формулу:
=ЧСТЭК(D4:D12, B4:C12)
Обратите внимание, что пустой массив, который вы складываете (D4:D12), должен иметь то же количество строк, что и исходный массив, и должен быть указан первым в формуле. В результате слева от исходного массива добавляется столбец с нулевыми значениями.
Добавить строки выше
Чтобы расширить массив по вертикали, используйте функцию VSTACK, чтобы поместить определенное количество пустых строк над первой строкой исходного массива.
В нашем случае следующие два диапазона с одинаковым количеством столбцов складываются друг в друга, причем первым идет пустой массив:
=ВСТЕК(B14:C16, B4:C12)
В результате в начало исходного массива добавляются 3 строки с нулевыми значениями.
Добавьте строки выше и столбцы слева
Чтобы расширить массив по вертикали и горизонтали, вложите функции VSTACK и HSTACK друг в друга. В нашем случае формула принимает такой вид:
=ВСТЕК(B14:D16, ВСТЭК(D4:D12, B4:C12))
И выдает такой результат:
Заполнить массив вверх и влево пользовательским значением
По умолчанию функции VSTACK и HSTACK возвращают нули вместо пустых ячеек. Чтобы заполнить новые ячейки пользовательским значением, вложите комбинацию ЕСЛИ и ЕПУСТО внутри каждой функции стека, которая заменит пробелы указанным вами значением.
Например, чтобы новые ячейки были дополнены дефисом, используйте следующую формулу:
=VSTACK(ЕСЛИ(ПУСТО(B14:D16),”-“, B14:D16), HSTACK(ЕСЛИ(ПУСТО(D4:D12),”-“, D4:D12), B4:C12))
Чтобы расширить массив пустыми ячейками, используйте эту формулу:
=VSTACK(ЕСЛИ(ПУСТО(B14:D16),””, B14:D16), HSTACK(ЕСЛИ(ПУСТО(D4:D12),””, D4:D12), B4:C12))
Разделить строку и расширить массив
В этом примере показано, как использовать функции EXPAND и TEXTSPLIT вместе, чтобы разделить текстовую строку по определенному разделителю в отдельных столбцах, а затем увеличить окончательный массив до нужного размера.
Допустим, у вас есть столбец строк, подобный столбцу B на изображении ниже. Каждая строка имеет несколько значений, разделенных определенным разделителем (в нашем случае это запятая и пробел). Ваша цель — разбить каждую строку на отдельные ячейки, чтобы все результирующие массивы имели одинаковое количество столбцов.
Для выполнения задачи введите эту формулу в E4:
=РАСШИРИТЬ(РАЗДЕЛЕНИЕ ТЕКСТА(B4, “, “), , СТОЛБЦЫ(E3:H3), “-“)
В этой формуле:
- TEXTSPLIT разделяет значения в B4, используя запятую и пробел (“,”) в качестве разделителя.
- COLUMNS вычисляет количество столбцов в выходном массиве.
- EXPAND расширяет возвращаемый массив вправо, дополняя отсутствующие значения дефисами (“-“).
Перетащите формулу вниз через E15, и вы получите такой результат:
Функция РАСШИРИТЬ не работает
Если функция EXPAND работает некорректно в Excel или выдает ошибку, следующие советы помогут определить причину и устранить ее.
#ЦЕНИТЬ! ошибка
Происходит, если значение, используемое для аргумента строк или столбцов, меньше общего числа строк или столбцов исходного массива.
#Н/Д ошибка
Появляется в заполненных ячейках, если аргумент pad_with не задан.
#НАЗВАНИЕ? ошибка
Происходит, если имя функции написано с ошибкой или функция EXPAND недоступна в вашей версии Excel. В настоящее время он поддерживается только в Excel 365 и Excel в Интернете.
#ИГРА! ошибка
Происходит, когда недостаточно пустых ячеек для вывода развернутого массива. В большинстве случаев очистка мешающих ячеек сразу устраняет ошибку. Дополнительные сведения см. в разделе Как исправить ошибку #SPILL! ошибка в экселе.
Вот как можно использовать функцию EXPAND в Excel, чтобы расширить массив до такого количества строк и столбцов, которое требует ваша бизнес-логика. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Формула Excel EXPAND – примеры (файл .xlsx)
