Преобразование массива в вектор и обратно в Excel: ПОСТОЛБЦ (TOCOL), ПОСТРОК (TOROW), СВЕРНСТОЛБЦ (WRAPCOLS) и СВЕРНСТРОК (WRAPROWS)
Функции ПОСТОЛБЦ (TOCOL) и ПОСТРОК (TOROW) в Excel позволяют эффективно преобразовывать прямоугольные массивы данных в удобные для анализа одномерные списки, что особенно полезно при работе с динамическими массивами и подготовке данных для дальнейшей обработки.
Синтаксис обратного преобразования: =СВЕРНСТОЛБЦ(вектор;высота;[заполнитель]) и =СВЕРНСТРОК(вектор;ширина;[заполнитель]). Например, для вектора 1…6 аргумент 2 даёт у WRAPCOLS массив 2×3: первая строка 1, 3, 5, вторая 2, 4, 6; у WRAPROWS — массив 3×2 со строками 1, 2; 3, 4; 5, 6. Вход должен быть одной строкой или столбцом, недостающие позиции по умолчанию заполняются #Н/Д; третий аргумент может заменить заполнитель.
Аргумент «игнорировать»: 0 — сохранить всё (по умолчанию), 1 — пропустить пустые ячейки, 2 — ошибки, 3 — пустые ячейки и ошибки. Числовой ноль не является пустой ячейкой; формула, возвращающая пустую строку, тоже требует отдельного внимания при очистке.
Используемые здесь функции преобразования массивов доступны в Microsoft 365, Excel 2024 для Windows/Mac и Excel для Интернета; в Excel 2016/2019/2021 полного набора нет. Формулу разлива размещайте вне таблицы Excel, оставляя место для результата. Русские и английские примеры ниже используют стандартные разделители аргументов ; и , соответственно.
Эта статья особенно полезна тем, кто ищет формулы в Excel, хочет быстрее разобраться в функциях и применять рабочие примеры без путаницы между русской и английской версиями Excel.
Эти инструменты незаменимы, когда вам нужно "развернуть" таблицу в столбец или строку, пропустить пустые ячейки или изменить порядок чтения данных.
Как использовать ПОСТОЛБЦ (TOCOL) и ПОСТРОК (TOROW)
Каждая функция принимает массив или диапазон в качестве первого аргумента. Дополнительные параметры позволяют гибко управлять результатом.
- ПОСТОЛБЦ (TOCOL) преобразует диапазон в один столбец.
- ПОСТРОК (TOROW) преобразует диапазон в одну строку.
- Необязательный аргумент
Ignoreуказывает Excel пропускать пустые ячейки, ошибки или и то, и другое. - Аргумент
Scan_By_Columnуправляет порядком чтения данных: по строкам (по умолчанию,ЛОЖЬ) или по столбцам (ИСТИНА).
Рассмотрим три примера применения Русская версия: =ПОСТОЛБЦ(A2:B6)English version: =TOCOL(A2:B6) к диапазону с пустой ячейкой в B6:
- Русская версия:
=ПОСТОЛБЦ(A2:B6)English version:=TOCOL(A2:B6)в ячейке D2 вернёт ноль на месте пустой ячейки (D11). - Русская версия:
=ПОСТОЛБЦ(A2:B6;1)English version:=TOCOL(A2:B6,1)в E2 исключит пустые ячейки из результата. - Русская версия:
=ПОСТОЛБЦ(A2:B6;1;ИСТИНА)English version:=TOCOL(A2:B6,1,TRUE)в F2 изменит порядок чтения на по столбцам: сначала все значения из столбца A, затем из B.
Исключение конкретных строк из результата
Что если нужно исключить не пустые ячейки, а конкретное значение, например, "Обед", которое находится в третьей строке обоих столбцов? Можно скомбинировать функции:
=ПОСТОЛБЦ(ВЫБОРСТРОК(A2:B6;1;2;4;5))=TOCOL(CHOOSEROWS(A2:B6,1,2,4,5))Функция CHOOSEROWS выбирает только указанные строки (1, 2, 4, 5), которые затем передаются в ПОСТОЛБЦ (TOCOL).
Обратное преобразование: СВЕРНСТОЛБЦ (WRAPCOLS) и СВЕРНСТРОК (WRAPROWS)
После того как данные собраны в один столбец или строку, может потребоваться вернуть их в табличный формат. Для этого используйте функции СВЕРНСТОЛБЦ (WRAPCOLS) и СВЕРНСТРОК (WRAPROWS).
- СВЕРНСТОЛБЦ (WRAPCOLS) заполняет результат по столбцам; второй аргумент задаёт число элементов в каждом столбце (высоту).
- СВЕРНСТРОК (WRAPROWS) заполняет результат по строкам; второй аргумент задаёт число элементов в каждой строке (ширину).
Эти функции идеально подходят для реструктуризации данных, например, для случайного перемешивания списка или создания новой таблицы из обработанного вектора. Подробный пример их использования можно найти в руководстве по перемешиванию колоды карт.
Освоив ПОСТОЛБЦ (TOCOL), ПОСТРОК (TOROW), СВЕРНСТОЛБЦ (WRAPCOLS) и СВЕРНСТРОК (WRAPROWS), вы значительно расширите свои возможности по манипуляции данными в Excel, сделав процессы очистки и преобразования информации более эффективными и автоматизированными.