Как использовать формулы массива в Google Sheets

Опубликовано: 2023-06-10

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

Обладая большей гибкостью для работы с массивами и выходя за рамки базовой функции МАССИВФОРМУЛА, давайте посмотрим, как использовать эти функции массива с формулами в Google Таблицах.

Оглавление

    Совет : Некоторые из этих функций могут показаться вам знакомыми, если вы также используете Microsoft Excel.

    Преобразование массива: TOROW и TOCOL

    Если в вашем наборе данных есть массив, который вы хотите преобразовать в одну строку или столбец, вы можете использовать функции TOROW и TOCOL.

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

    • Массив : массив, который вы хотите преобразовать, в формате «A1:D4».
    • Ignore : по умолчанию никакие параметры не игнорируются (0), но вы можете использовать 1 для игнорирования пробелов, 2 для игнорирования ошибок или 3 для игнорирования пробелов и ошибок.
    • Scan : этот аргумент определяет, как читать значения в массиве. По умолчанию функция сканирует по строкам или использует значение False, но вы можете использовать True для сканирования по столбцам, если хотите.

    Давайте рассмотрим несколько примеров с использованием функций TOROW и TOCOL и их формул.

    В этом первом примере мы возьмем наш массив от A1 до C3 и превратим его в строку, используя аргументы по умолчанию с помощью этой формулы:

    =ВОРОТ(A1:C3)

    Как видите, массив теперь находится в строке. Поскольку мы использовали аргумент сканирования по умолчанию, функция читает слева направо (A, D, G), вниз, затем снова слева направо (B, E, H) до завершения — сканирование по строке.

    Чтобы прочитать массив по столбцу, а не по строке, мы можем использовать True для аргумента сканирования . Мы оставим аргумент игнорирования пустым. Вот формула:

    =ЗАВЕРШЕНО(A1:C3,,ИСТИНА)

    Теперь вы видите, что функция считывает массив сверху вниз (A, B, C), сверху вниз (D, E, F) и сверху вниз (G, H, I).

    Функция TOCOL работает так же, но преобразует массив в столбец. Используя тот же диапазон, от A1 до C3, вот формула с аргументами по умолчанию:

    =ТОКОЛ(A1:C3)

    Опять же, используя значение по умолчанию для аргумента сканирования , функция читает слева направо и предоставляет результат как таковой.

    Чтобы прочитать массив по столбцу, а не по строке, вставьте True для аргумента сканирования следующим образом:

    =ТОКОЛ(A1:C3,,ИСТИНА)

    Теперь вы видите, что вместо этого функция читает массив сверху вниз.

    Создайте новый массив из строк или столбцов: CHOOSEROWS и CHOOSECOLS

    Вы можете создать новый массив из существующего. Это позволяет вам создать новый диапазон ячеек только с определенными значениями из другого. Для этого вы будете использовать функции CHOOSEROWS и CHOOSECOLS Google Sheets.

    Синтаксис для каждой функции аналогичен: CHOOSEROWS (массив, номер_строки, номер_строки_opt) и CHOOSECOLS (массив, номер_столбца, номер_столбца_opt), где первые два аргумента требуются для обеих функций.

    • Массив : существующий массив в формате «A1:D4».
    • Row_num или Col_num : номер первой строки или столбца, который вы хотите вернуть.
    • Row_num_opt или Col_num_opt : числа для дополнительных строк или столбцов, которые вы хотите вернуть. Google предлагает использовать отрицательные числа для возврата строк снизу вверх или столбцов справа налево.

    Давайте рассмотрим несколько примеров с использованием CHOOSEROWS и CHOOSECOLS и их формул.

    В этом первом примере мы будем использовать массив от A1 до B6. Мы хотим вернуть значения в строках 1, 2 и 6. Вот формула:

    =ВЫБРАТЬСТРОКИ(A1:B6,1,2,6)

    Как видите, мы получили эти три строки для создания нашего нового массива.

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

    Используя отрицательные числа, вы должны использовать эту формулу:

    =ВЫБРАТЬСТРОКИ(A1:B6,1,-1,-5)

    Чтобы объяснить, 1 — это первая возвращаемая строка, -1 — это вторая возвращаемая строка, которая является первой строкой, начинающейся снизу, а -5 — это пятая строка снизу.

    Используя положительные числа, вы использовали бы эту формулу для получения того же результата:

    =ВЫБРАТЬСТРОКИ(A1:B6,1,6,2)

    Функция CHOOSECOLS работает аналогично, за исключением того, что вы используете ее, когда хотите создать новый массив из столбцов, а не из строк.

    Используя массив от A1 до D6, мы можем вернуть столбцы 1 (столбец A) и 4 (столбец D) с помощью этой формулы:

    =ВЫБОРСЕКТОРОВ(A1:D6,1,4)

    Теперь у нас есть новый массив только с этими двумя столбцами.

    В качестве другого примера мы будем использовать тот же массив, начиная со столбца 4. Затем мы сначала добавим столбцы 1 и 2 со 2 (столбец B). Вы можете использовать как положительные, так и отрицательные числа:

    =ВЫБОРЦЕНОК(A1:D6,4,2,1)

    =ВЫБОРСЕКТОРОВ(A1:D6,4,-3,-4)

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

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

    Обернуть для создания нового массива: WRAPROWS и WRAPCOLS

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

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

    • Диапазон : существующий диапазон ячеек, который вы хотите использовать для массива, в формате «A1: D4».
    • Количество : количество ячеек для каждой строки или столбца.
    • Pad : этот аргумент можно использовать для размещения текста или отдельного значения в пустых ячейках. Это заменяет ошибку #N/A, которую вы получите для пустых ячеек. Включите текст или значение в кавычки.

    Давайте рассмотрим несколько примеров с использованием функций WRAPROWS и WRAPCOLS и их формул.

    В этом первом примере мы будем использовать диапазон ячеек от A1 до E1. Мы создадим новый массив, охватывающий строки с тремя значениями в каждой строке. Вот формула:

    =ВРАПРУСЫ(A1:E1,3)

    Как видите, у нас есть новый массив с правильным результатом, по три значения в каждой строке. Поскольку у нас есть пустая ячейка в массиве, отображается ошибка #Н/Д. В следующем примере мы будем использовать аргумент pad , чтобы заменить ошибку текстом «Нет». Вот формула:

    =WRAPROWS(A1:E1,3"Нет")

    Теперь мы можем видеть слово вместо ошибки Google Sheets.

    Функция WRAPCOLS делает то же самое, создавая новый массив из существующего диапазона ячеек, но делает это путем переноса столбцов вместо строк.

    Здесь мы будем использовать тот же массив, от A1 до E3, оборачивая столбцы тремя значениями в каждом столбце:

    =ОБОРОТСОЛНЦ(A1:E1,3)

    Как и в случае с WRAPROWS, мы получаем правильный результат, но также и ошибку из-за пустой ячейки. С помощью этой формулы вы можете использовать аргумент pad для добавления слова «Empty»:

    =WRAPCOLS(A1:E1,3,"Пустой")

    Этот новый массив выглядит намного лучше со словом вместо ошибки.

    Объедините для создания нового массива: HSTACK и VSTACK

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

    Синтаксис для каждой функции одинаков: HSTACK (диапазон1, диапазон2,…) и VSTACK (диапазон1, диапазон2,…), где требуется только первый аргумент. Однако вы почти всегда будете использовать второй аргумент, который объединяет другой диапазон с первым.

    • Range1 : первый диапазон ячеек, который вы хотите использовать для массива, в формате «A1:D4».
    • Range2,… : второй диапазон ячеек, который вы хотите добавить к первому для создания массива. Вы можете комбинировать более двух диапазонов ячеек.

    Давайте рассмотрим несколько примеров с использованием HSTACK и VSTACK и их формул.

    В этом первом примере мы объединим диапазоны от A1 до D2 с A3 до D4, используя эту формулу:

    =ЧСТЭК(A1:D2,A3:D4)

    Вы можете видеть наши диапазоны данных, объединенные в один горизонтальный массив.

    Для примера функции VSTACK мы объединим три диапазона. Используя следующую формулу, мы будем использовать диапазоны от A2 до C4, от A6 до C8 и от A10 до C12:

    =ВСТЭК(A2:C4,A6:C8,A10:C12)

    Теперь у нас есть один массив со всеми нашими данными, использующими формулу в одной ячейке.

    Управляйте массивами с легкостью

    Хотя вы можете использовать ФОРМУЛУ МАССИВА в определенных ситуациях, например, с функцией СУММ или функцией ЕСЛИ, эти дополнительные формулы массива Google Sheets могут сэкономить ваше время. Они помогут вам упорядочить лист именно так, как вы хотите, и с помощью одной формулы массива.

    Для получения дополнительных руководств, подобных этому, но с функциями, отличными от массива, посмотрите, как использовать функцию СЧЁТЕСЛИ или СУММЕСЛИ в Google Sheets.