Как объединять, изменять форму и размер массивов в Excel

Иногда работа с массивами или смежными диапазонами ячеек в Microsoft Excel может быть сложной задачей. Если вы хотите объединить, изменить форму или размер массива, вы можете выбрать из набора функций, которые могут охватывать множество ситуаций.

Примечание. Эти 11 функций являются новыми для Excel по состоянию на август 2022 года. Они постепенно развертываются для пользователей Excel, начиная с участников программы предварительной оценки Office.

Объединить массивы

Объединение данных в электронной таблице может быть затруднено. С помощью функций VSTACK и HSTACK вы можете складывать массивы вертикально и горизонтально.

СВЯЗАННЫЕ С: Как объединить данные из электронных таблиц в Microsoft Excel

Синтаксис каждой функции такой же, как VSTACK(array1, array2,...) и HSTACK(array1, array2,...) только с одним обязательным массивом и другими необязательными.

Чтобы объединить массивы в ячейках с B2 по F3 и с H2 по L3 по вертикали, используйте эту формулу для функции VSTACK:

 =ВСТЕК(B2:F3,H2:L3) 

Вместо этого, чтобы объединить те же самые массивы по горизонтали, используйте эту формулу для функции HSTACK:

 =HSTACK(B2:F3,H2:L3) 

Изменить массивы

Если вы хотите не объединять массивы, а изменять их форму, есть четыре функции, которые вы можете использовать.

СВЯЗАННЫЕ С: 12 основных функций Excel, которые должен знать каждый

Преобразование массива в строку или столбец

Во-первых, функции TOROW и TOCOL позволяют сформировать массив в виде строки или столбца. Синтаксис каждого из них: TOROW(array, ignore, by_column) и TOCOL(array, ignore, by_column) .

• Игнорировать : чтобы игнорировать определенные типы данных, введите 1 для пробелов, 2 для ошибок или 3 для пробелов и ошибок. Значение по умолчанию равно 0, чтобы игнорировать никакие значения.
• By_column : Используйте этот аргумент для сканирования массива по столбцу с использованием TRUE. Если аргумент не указан, по умолчанию используется значение FALSE, при котором массив сканируется по строкам. Это определяет, как упорядочены значения.

Чтобы преобразовать массив от B2 до F3 в строку, используйте эту формулу с функцией TOROW:

 =ЗАПОЛНИТЬ(B2:F3) 

Чтобы вместо этого преобразовать тот же массив в столбец, используйте функцию TOCOL со следующей формулой:

 =ТОКОЛ(B2:F3) 

Преобразование строки или столбца в массив

Чтобы сделать обратное вышеописанному и преобразовать строку или столбец в массив, вы можете использовать WRAPROWS и WRAPCOLS. Синтаксис каждого из них: WRAPROWS(reference, wrap_count, pad) и WRAPCOLS(reference, wrap_count, pad) где reference представляет собой группу ячеек.

• Wrap_count : количество значений для каждой строки или столбца.
• Pad : значение для отображения для Pad (пустая ячейка).

Чтобы преобразовать ячейки с B2 по K2 в двумерный массив путем переноса строк, используйте функцию WRAPROWS. С помощью этой формулы ячейки оборачиваются с использованием трех значений в строке с «пустым» в качестве pad .

 =WRAPROWS(B2:K2,3;"пусто") 

Чтобы преобразовать одни и те же ячейки в двумерный массив путем переноса столбцов, используйте функцию WRAPCOLS. С помощью этой формулы ячейки оборачиваются с использованием трех значений на столбец со словом «пусто» в качестве pad .

 =WRAPCOLS(B2:K2,3;"пусто") 

Изменить размер массивов

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

СВЯЗАННЫЕ: 13 основных функций Excel для ввода данных

Взять или удалить строки или столбцы

С помощью функции TAKE вы сохраняете указанное вами количество строк или столбцов. С помощью функции DROP вы делаете обратное и удаляете указанное вами количество строк или столбцов. Вы будете использовать положительные числа, чтобы брать или удалять элементы из начала массива, и отрицательные числа, чтобы брать или удалять элементы из конца.

Синтаксис каждого из них: TAKE(array, rows, columns) и DROP(array, rows, columns) где вам нужен хотя бы один из двух вторых аргументов; rows или columns .

Чтобы сохранить первые две строки в массиве от B2 до F5, используйте TAKE с аргументом rows . Вот формула:

 =ВЗЯТЬ(B2:F5,2) 

Чтобы сохранить первые два столбца в том же массиве, вместо этого используйте аргумент columns :

 =ВЗЯТЬ(B2:F5,,2) 

Чтобы удалить первые две строки в массиве от B2 до F5, используйте DROP с аргументом rows и следующей формулой:

 =УДАЛИТЬ(B2:F5,2) 

Чтобы удалить первые два столбца в том же массиве, используйте вместо этого аргумент columns и эту формулу:

 =УДАЛИТЬ(B2:F5,,2) 

Сохраняйте определенное количество строк или столбцов

Чтобы выбрать точные номера строк и столбцов, которые вы хотите сохранить в массиве, вы должны использовать функции CHOOSEROWS и CHOOSECOLS.

Синтаксис каждого из них: CHOOSEROWS(array, row_num1, row_num2,...) и CHOOSECOLS(array, column_num1, column_num2,...) где требуются первые два аргумента. Вы можете добавить больше номеров строк и столбцов, если хотите.

Чтобы вернуть строки 2 и 4 из массива от B2 до F5, вы должны использовать функцию CHOOSEROWS и эту формулу:

 =ВЫБРАТЬСТРОКИ(B2:F5,2,4) 

Чтобы вернуть столбцы 3 и 5 из одного и того же массива, вы должны использовать функцию CHOOSECOLS с этой формулой:

 =ВЫБОРЦЕНОК(B2:F5,3,5) 

Примечание. Не забудьте использовать номера строк или столбцов для массива, а не для листа.

Расширение массива до определенных размеров

Возможно, вы планируете добавить больше данных в свой массив, поэтому хотите сделать его определенного размера, чтобы добавить границу или использовать условное форматирование. С помощью функции EXPAND вы вводите количество строк и столбцов, которые должен покрыть ваш массив.

СВЯЗАННЫЕ С: Как добавить и изменить границы ячеек в Excel

Синтаксис функции: EXPAND(array, rows, columns, pad) где отсутствующие аргументы rows или columns означают, что они не будут расширяться. При желании вы можете pad значение заполнения для пустых ячеек.

Чтобы расширить массив от B2 до F5, чтобы охватить 10 строк и 10 столбцов, вы должны использовать эту формулу:

 =РАСШИРИТЬ(B2:F5,10,10) 

Чтобы расширить тот же массив до тех же размеров и включить pad поле, используйте эту формулу:

 =РАСШИРИТЬ(B2:F5,10,10;"пусто") 

Совет: хотя аргумент pad является необязательным, вы можете предпочесть его отображению ошибки, как показано выше.

Эти 11 функций дают вам больший контроль над вашими массивами в Microsoft Excel, чем когда-либо. Попробуйте их и посмотрите, сделают ли они то, что вам нужно.

СВЯЗАННЫЕ С: Как исправить распространенные ошибки формул в Microsoft Excel