Як використовувати формули масивів у Google Таблицях

На початку 2023 року Google представив кілька нових функцій для Таблиць, зокрема вісім для роботи з масивами. Використовуючи ці функції, ви можете перетворити масив на рядок або стовпець, створити новий масив із рядка чи стовпця або додати поточний масив.

Маючи більшу гнучкість для роботи з масивами та виходячи за рамки базової функції ARRAYFORMULA, давайте розглянемо, як використовувати ці функції масиву з формулами в Google Таблицях .

Зміст

Порада : деякі з цих функцій можуть здатися вам знайомими, якщо ви також використовуєте Microsoft Excel.

Перетворення масиву: TOROW і TOCOL

Якщо у вашому наборі даних є масив, який потрібно перетворити на один рядок або стовпець, ви можете скористатися функціями TOROW і TOCOL.

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

• Масив : масив, який потрібно перетворити, у форматі «A1:D4».
• Ігнорувати : за замовчуванням параметри не ігноруються (0), але ви можете використовувати 1, щоб ігнорувати пробіли, 2, щоб ігнорувати помилки, або 3, щоб ігнорувати пробіли та помилки.
• Сканувати : цей аргумент визначає, як читати значення в масиві. За замовчуванням функція сканує за рядками або за допомогою значення False, але ви можете використовувати True для сканування за стовпцями, якщо хочете.

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

У цьому першому прикладі ми візьмемо наш масив від A1 до C3 і перетворимо його на рядок, використовуючи стандартні аргументи за цією формулою:

=TOROW(A1:C3)

Як ви бачите, масив тепер у рядку. Оскільки ми використали аргумент сканування за замовчуванням, функція читає зліва направо (A, D, G), вниз, потім знову зліва направо (B, E, H) до завершення — сканування по рядках.

Щоб читати масив за стовпцями, а не за рядками, ми можемо використати True для аргументу scan . Ми залишимо аргумент ігнорувати порожнім. Ось формула:

=TOROW(A1:C3,;ІСТИНА)

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

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

=TOCOL(A1:C3)

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

Щоб прочитати масив за стовпцями, а не за рядками, вставте True для аргументу scan таким чином:

=TOCOL(A1:C3,;ІСТИНА)

Тепер ви бачите, що функція читає масив зверху вниз.

Створіть новий масив із рядків або стовпців: CHOOSEROWS і CHOOSECOLS

Ви можете створити новий масив із існуючого. Це дає змогу створити новий діапазон комірок лише з конкретними значеннями з іншого. Для цього ви скористаєтеся функціями Google Sheets CHOOSEROWS і CHOOSECOLS .

Синтаксис для кожної функції подібний, CHOOSEROWS (масив, номер_ряду, номер_ряду_опція) і CHOOSECOLS (масив, номер_стовпця, номер_стовпця), де перші два аргументи потрібні для обох.

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

Давайте розглянемо кілька прикладів використання 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 – це п’ятий рядок знизу.

Використовуючи додатні числа, ви б використали цю формулу, щоб отримати той самий результат:

=CHOOSEROWS(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».
• Підрахунок : кількість комірок для кожного рядка чи стовпця.
• Блокнот : ви можете використовувати цей аргумент, щоб розмістити текст або окреме значення в порожніх клітинках. Це замінить помилку #N/A, яку ви отримаєте для порожніх клітинок. Включіть текст або значення в лапки.

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

У цьому першому прикладі ми будемо використовувати діапазон клітинок від A1 до E1. Ми створимо новий масив, обгортаючи рядки з трьома значеннями в кожному рядку. Ось формула:

=WRAPROWS(A1:E1;3)

Як бачите, у нас є новий масив із правильним результатом, по три значення в кожному рядку. Оскільки ми маємо порожню клітинку в масиві, відображається помилка #N/A. У наступному прикладі ми використаємо аргумент pad , щоб замінити помилку текстом «Немає». Ось формула:

=WRAPROWS(A1:E1;3;”Немає”)

Тепер ми можемо побачити слово замість помилки Google Таблиць.

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

Тут ми використаємо той самий масив від A1 до E3, обернувши стовпці трьома значеннями в кожному стовпці:

=WRAPCOLS(A1:E1;3)

Подібно до прикладу WRAPROWS, ми отримуємо правильний результат, але також помилку через порожню клітинку. За допомогою цієї формули ви можете використовувати аргумент pad , щоб додати слово «Empty»:

=WRAPCOLS(A1:E1;3;”Пусто”)

Цей новий масив виглядає набагато краще зі словом замість помилки.

Об’єднайте, щоб створити новий масив: HSTACK і VSTACK

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

Синтаксис для кожної функції однаковий, HSTACK (діапазон1, діапазон2,…) і VSTACK (діапазон1, діапазон2,…), де потрібен лише перший аргумент. Однак ви майже завжди використовуватимете другий аргумент, який поєднує інший діапазон із першим.

• Діапазон1 : перший діапазон клітинок, який ви хочете використовувати для масиву, у форматі «A1:D4».
• Діапазон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)

Тепер ми маємо один масив з усіма нашими даними за допомогою формули в одній клітинці.

Легко маніпулюйте масивами

Хоча ви можете використовувати ARRAYFORMULA в певних ситуаціях, наприклад із функцією SUM або функцією IF, ці додаткові формули масиву Google Таблиць можуть заощадити ваш час. Вони допоможуть вам організувати свій аркуш саме так, як ви хочете, і за допомогою однієї формули масиву.

Щоб отримати більше подібних посібників, але з функціями, не пов’язаними з масивами, подивіться, як використовувати функцію COUNTIF або SUMIF у Google Таблицях .