В начале 2023 года Google представил несколько новых функций для Таблиц, включая восемь для работы с массивами. Используя эти функции, вы можете преобразовать массив в строку или столбец, создать новый массив из строки или столбца или добавить текущий массив.
Чтобы получить больше гибкости в работе с массивами и выйти за рамки базовой функции ARRAYFORMULA, давайте посмотрим, как использовать эти функции массива с формулы в Google Таблицах.
Совет. Некоторые из этих функций могут показаться вам знакомыми, если вы также используете Microsoft Excel.
Преобразование массива: TOROW и TOCOL
Если в вашем наборе данных есть массив, который вы хотите преобразовать в одну строку или столбец, вы можете использовать функции TOROW и TOCOL.
Синтаксис для каждой функции одинаков: TOROW(array, ignore, scan)и TOCOL(array, ignore, scan), где требуется только первый аргумент для обоих.
Давайте рассмотрим несколько примеров с использованием функций TOROW и TOCOL и их формул.
В этом первом примере мы возьмем наш массив от A1 до C3 и превратим его в строку, используя аргументы по умолчанию с помощью этой формулы:
=TOROW(A1:C3)
Как видите, массив теперь расположен в строке. Поскольку мы использовали аргумент scanпо умолчанию, функция читает слева направо (A, D, G), вниз, затем снова слева направо (B, E, H) до завершения — сканируется строка.
Чтобы читать массив по столбцу, а не по строкам, мы можем использовать Trueдля аргумента scan. Мы оставим аргумент ignoreпустым. Вот формула:
=TOROW(A1:C3,,TRUE)
Теперь вы видите, что функция читает массив сверху вниз (A, B, C), сверху вниз (D, E, F) и сверху вниз (G, H, I)..
Функция TOCOL работает таким же образом, но преобразует массив в столбец. Ниже приведена формула, использующая тот же диапазон от A1 до C3, с аргументами по умолчанию:
=TOCOL(A1:C3)
Опять же, используя значение по умолчанию для аргумента scan, функция читает слева направо и выдает результат как таковой.
Чтобы прочитать массив по столбцу, а не по строкам, вставьте Trueдля аргумента scanследующим образом:
=TOCOL(A1:C3,,TRUE)
Теперь вы видите, что вместо этого функция читает массив сверху вниз.
Создать новый массив из строк или столбцов: ВЫБЕРИТЕ СРОКИ и ВЫБЕРИТЕ ЭКОЛОГИ
Возможно, вы захотите создать новый массив из существующего. Это позволяет вам создать новый диапазон ячеек только с определенными значениями из другого. Для этого вы будете использовать CHOOSEROWS и CHOOSECOLS Функции Google Таблиц.
Синтаксис каждой функции аналогичен: CHOOSEROWS (array, row_num, row_num_opt)и CHOOSECOLS (array, col_num, col_num_opt), где требуются первые два аргумента для обоих.
Давайте рассмотрим несколько примеров использования CHOOSEROWS и CHOOSECOLS и их формул.
В этом первом примере мы будем использовать массив от A1 до B6. Мы хотим вернуть значения в строках 1, 2 и 6. Вот формула:
=CHOOSEROWS(A1:B6,1,2,6)
Как видите, мы получили эти три строки для создания нового массива.
В другом примере мы будем использовать тот же массив. На этот раз мы хотим вернуть строки 1, 2 и 6, но 2 и 6 в обратном порядке. Вы можете использовать положительные или отрицательные числа, чтобы получить тот же результат.
При использовании отрицательных чисел вы можете использовать следующую формулу:
=CHOOSEROWS(A1:B6,1,-1,-5).
Для пояснения: 1 – это первая возвращаемая строка, -1 – вторая возвращаемая строка, то есть первая строка, начинающаяся снизу, а -5 — пятая строка снизу.
При использовании положительных чисел вы можете использовать следующую формулу для получения того же результата:
=ВЫБРАТЬСЯ (A1:B6,1,6,2)
Функция CHOOSECOLS работает аналогично, за исключением того, что вы используете ее, когда хотите создать новый массив из столбцов, а не из строк.
Используя массив от A1 до D6, мы можем вернуть столбцы 1 (столбец A) и 4 (столбец D) с помощью этой формулы:
=ВЫБЕРИТЕ ECOLS(A1:D6,1,4)
Теперь у нас есть новый массив только с этими двумя столбцами.
В качестве другого примера мы будем использовать тот же массив, начиная со столбца 4. Затем мы сначала добавим столбцы 1 и 2 со столбцом 2 (столбец B). Вы можете использовать как положительные, так и отрицательные числа:
=ВЫБРАТЬ ECOLS(A1:D6,4,2,1)
=ВЫБЕРИТЕ ECOLS(A1:D6,4,-3,-4)
Как вы можете видеть на скриншоте выше, с формулами в ячейках, а не на панели формул, мы получаем один и тот же результат, используя оба варианта.
Примечание. Поскольку Google предлагает использовать отрицательные числа меняет расположение результатов, имейте это в виду, если вы не получаете правильные результаты с использованием положительных чисел.
Перенос для создания нового массива: WRAPROWS и WRAPCOLS
Если вы хотите создать новый массив из существующего, но обернуть столбцы или строки определенным количеством значений в каждом, вы можете использовать функции WRAPROWS и WRAPCOLS.
Синтаксис для каждой функции одинаков: WRAPROWS (диапазон, количество, заполнение)и WRAPCOLS (диапазон, количество, заполнение), где первые два аргумента — требуется для обоих.
Давайте рассмотрим несколько примеров с использованием функций WRAPROWS и WRAPCOLS и их формул..
В этом первом примере мы будем использовать диапазон ячеек от A1 до E1. Мы создадим новый массив, охватывающий строки с тремя значениями в каждой строке. Вот формула:
=WRAPROWS(A1:E1,3)
Как видите, у нас появился новый массив с правильным результатом, по три значения в каждой строке. Поскольку у нас есть пустая ячейка в массиве, отображается ошибка #Н/Д. В следующем примере мы будем использовать аргумент pad, чтобы заменить ошибку текстом «Нет». Вот формула:
=WRAPROWS(A1:E1,3, «Нет»)
Теперь вместо ошибки Google Таблиц мы видим слово.
Функция WRAPCOLS делает то же самое, создавая новый массив из существующего диапазона ячеек, но делает это путем переноса столбцов вместо строк.
Здесь мы будем использовать тот же массив от A1 до E3, заключая столбцы с тремя значениями в каждом столбце:
=WRAPCOLS(A1:E1,3)
Как и в примере с WRAPROWS, мы получаем правильный результат, но также и ошибку из-за пустой ячейки. С помощью этой формулы вы можете использовать аргумент pad, чтобы добавить слово «Пусто»:
=WRAPCOLS(A1:E1,3,»Пусто»)
Этот новый массив выглядит намного лучше со словом вместо ошибки.
Объединить для создания нового массива: HSTACK и VSTACK
Две последние функции, которые мы рассмотрим, предназначены для добавления массивов. С помощью HSTACK и VSTACK вы можете объединить два или более диапазонов ячеек, чтобы сформировать единый массив по горизонтали или по вертикали.
Синтаксис для каждой функции одинаков: HSTACK (диапазон1, диапазон2,…)и VSTACK (диапазон1, диапазон2,…),где только первый аргумент необходимый. Однако вы почти всегда будете использовать второй аргумент, который объединяет другой диапазон с первым.
Давайте рассмотрим несколько примеров использования HSTACK и VSTACK и их формул.
В этом первом примере мы объединим диапазоны от A1 до D2 и от A3 до D4, используя следующую формулу:.
=HSTACK(A1:D2,A3:D4)
Вы можете увидеть, как наши диапазоны данных объединены образуют единый горизонтальный массив.
В качестве примера функции VSTACK мы объединяем три диапазона. Используя следующую формулу, мы будем использовать диапазоны от A2 до C4, от A6 до C8 и от A10 до C12:
=VSTACK(A2:C4,A6:C8,A10:C12)
Теперь у нас есть один массив со всеми нашими данными, использующими формулу в одной ячейке.
Легко манипулировать массивами
Хотя вы можете использовать ФОРМУЛА МАССИВА в определенных ситуациях, например, с функцией СУММ или функцией ЕСЛИ, эти дополнительные формулы массива Google Таблиц могут сэкономить ваше время. Они помогут вам упорядочить таблицу так, как вы хотите, и с помощью единой формулы массива.
Чтобы получить больше подобных уроков, но с функциями, не связанными с массивами, посмотрите, как используйте СЧЁТЕСЛИ или Функция СУММЕСЛИ в Google Таблицах.
.