На початку 2023 р.Google представив кілька нових функційдля аркушів, у тому числі вісім для роботи з масивами. Використовуючи ці функції, ви можете перетворити масив на рядок або стовпець, створити новий масив із рядка чи стовпця або додати поточний масив.
Маючи більшу гнучкість для роботи з масивами та виходячи за рамки базової функції ARRAYFORMULA, давайте подивимося, як використовувати ці функції масиву з формулами в Google Таблицях.
Зміст
Підказка: деякі з цих функцій можуть здатися вам знайомими, якщо ви також використовуєте Microsoft Excel.
Перетворення масиву: TOROW і TOCOL
Якщо у вашому наборі даних є масив, який потрібно перетворити на один рядок або стовпець, ви можете скористатися функціями TOROW і TOCOL.
Синтаксис для кожної функції однаковий,TOROW(масив, ігнорувати, сканувати)іTOCOL(масив, ігнорувати, сканувати)де для обох потрібен лише перший аргумент.
- Масив: масив, який потрібно перетворити, у форматі «A1:D4».
- Ігнорувати: за замовчуванням параметри не ігноруються (0), але ви можете використовувати 1, щоб ігнорувати пробіли, 2, щоб ігнорувати помилки, або 3, щоб ігнорувати пробіли та помилки.
- Сканувати: цей аргумент визначає, як читати значення в масиві. За замовчуванням функція сканує за рядками або за допомогою значення False, але ви можете використовувати True для сканування за стовпцями, якщо хочете.
Давайте розглянемо кілька прикладів із використанням функцій TOROW і TOCOL та їхніх формул.
У цьому першому прикладі ми візьмемо наш масив від A1 до C3 і перетворимо його на рядок, використовуючи стандартні аргументи за цією формулою:
=ТРЕК(A1:C3)
Як ви бачите, масив тепер у рядку. Тому що ми використовували значення за замовчуваннямскануванняаргумент, функція читає зліва направо (A, D, G), вниз, потім знову зліва направо (B, E, H) до завершення — сканування по рядках.
Щоб прочитати масив за стовпцями замість рядків, ми можемо використовуватиправдадляскануванняаргумент. Ми залишимоігноруватиаргумент пустий. Ось формула:
=TOROW(A1:C3,;ІСТИНА)
Тепер ви бачите, що функція читає масив зверху вниз (A, B, C), зверху вниз (D, E, F) і зверху вниз (G, H, I).
Функція TOCOL працює так само, але перетворює масив на стовпець. Використовуючи той самий діапазон, від A1 до C3, ось формула з аргументами за замовчуванням:
=TOCOL(A1:C3)
Знову ж таки, використовуючи значення за замовчуванням дляскануванняаргумент, функція читає зліва направо та надає результат як такий.
Щоб читати масив за стовпцями, а не за рядками, вставтеправдадляскануваннятакий аргумент:
=TOCOL(A1:C3,;ІСТИНА)
Тепер ви бачите, що функція читає масив зверху вниз.
пов'язані:Маєте проблеми з формулами? Ознайомтеся з нашим посібником із усунення помилок аналізу формул Google Таблиць.
Створіть новий масив із рядків або стовпців: CHOOSEROWS і CHOOSECOLS
Ви можете створити новий масив із існуючого. Це дає змогу створити новий діапазон комірок лише з конкретними значеннями з іншого. Для цього ви будете використовувати функції Google Sheets CHOOSEROWS і CHOOSECOLS.
Синтаксис для кожної функції подібний,CHOOSEROWS (масив, row_num, row_num_opt)іCHOOSECOLS (масив, номер_стовпця, номер_стовпця_опція),де перші два аргументи потрібні для обох.
- Масив: існуючий масив у форматі «A1:D4».
- Номер_рядкаабоCol_num: номер першого рядка або стовпця, який потрібно повернути.
- Номер_рядка_опціяабоCol_num_opt: номери додаткових рядків або стовпців, які потрібно повернути. Google пропонує васвикористовувати від’ємні числащоб повернути рядки знизу вгору або стовпці справа наліво.
Давайте розглянемо кілька прикладів використання CHOOSEROWS і CHOOSECOLS та їхніх формул.
У цьому першому прикладі ми будемо використовувати масив від A1 до B6. Ми хочемо повернути значення в рядках 1, 2 і 6. Ось формула:
Рекомендовано прочитати:Як використовувати ChatGPT у Google Таблицях із GPT для Таблиць і Документів
=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».
- Граф: кількість комірок для кожного рядка або стовпця.
- Pad: ви можете використовувати цей аргумент, щоб розмістити текст або окреме значення в порожніх комірках. Це замінить помилку #N/A, яку ви отримаєте для порожніх клітинок. Включіть текст або значення в лапки.
Давайте розглянемо кілька прикладів використання функцій WRAPROWS і WRAPCOLS та їхніх формул.
У цьому першому прикладі ми будемо використовувати діапазон клітинок від A1 до E1. Ми створимо новий масив, обгортаючи рядки з трьома значеннями в кожному рядку. Ось формула:
=WRAPROWS(A1:E1;3)
Як бачите, у нас є новий масив із правильним результатом, по три значення в кожному рядку. Оскільки ми маємо порожню клітинку в масиві, відображається помилка #N/A. Для наступного прикладу ми використаємоколодкааргумент для заміни помилки текстом «Немає». Ось формула:
=WRAPROWS(A1:E1;3;”Немає”)
Тепер ми можемо побачити слово замість помилки Google Таблиць.
Функція WRAPCOLS робить те саме, створюючи новий масив із наявного діапазону комірок, але обгортаючи стовпці замість рядків.
Тут ми використаємо той самий масив від A1 до E3, обернувши стовпці трьома значеннями в кожному стовпці:
=WRAPCOLS(A1:E1;3)
Подібно до прикладу WRAPROWS, ми отримуємо правильний результат, але також помилку через порожню клітинку. За допомогою цієї формули ви можете використовуватиколодкааргумент для додавання слова «Порожній»:
=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)
Тепер ми маємо один масив з усіма нашими даними за допомогою формули в одній клітинці.
Легко маніпулюйте масивами
Поки можна використовуватиФОРМУЛА МАСИВАу певних ситуаціях, наприклад із функцією SUM або функцією IF, ці додаткові формули масиву Google Таблиць можуть заощадити ваш час. Вони допоможуть вам організувати свій аркуш саме так, як ви хочете, і за допомогою однієї формули масиву.
Щоб отримати більше подібних посібників, але з функціями, не пов’язаними з масивами, подивіться, як використовувати функцію COUNTIF або SUMIF у Google Таблицях.