TOCOL и TOROW в Excel — преобразовать 2D в 1D

Таблица Microsoft Excel, показывающая функции TOCOL и TOROW с логотипом Excel.

Ключевые идеи

TOCOL превращает 2D-массив в один столбец; TOROW — в одну строку.
Аргумент игнорирования (b) управляет пустыми ячейками и ошибками: 1 — игнорировать пустые, 2 — игнорировать ошибки, 3 — оба.
Аргумент направления (c) управляет порядком обхода: по строкам (по умолчанию) или по столбцам (TRUE).
Комбинации с VSTACK/HSTACK, UNIQUE и SORT дают мощные способы нормализации данных.

Для каких версий Excel это работает

TOCOL и TOROW доступны в Excel 2024 и новее, в Microsoft 365 (подписка), в Excel для веба и в мобильных приложениях Excel на iOS/Android. Если вы работаете в старой неподписной версии Excel — функции не будут работать.

Содержание

Обзор синтаксиса и аргументов
Примеры: базовый, с фильтрацией пустых/ошибочных значений
Порядок обхода: по строкам или по столбцам
Объединение нескольких массивов: VSTACK и HSTACK
Уникальные и отсортированные списки: UNIQUE + SORT
Практические сценарии: аналитика, подготовка данных, отчёты
Плагины, альтернативы и миграция
Производительность и ограничения
Руководства: чек-листы, тест-кейсы, SOP, decision tree
Глоссарий и сводка

Обзор синтаксиса и аргументов

TOCOL:

=TOCOL(a,[b],[c])

TOROW:

=TOROW(a,[b],[c])

Параметры:

a — обязательный: массив или диапазон, который нужно преобразовать.
b — необязательный: поведение игнорирования значений. 0 или опущено — вернуть все значения. 1 — игнорировать пустые ячейки. 2 — игнорировать ошибки (например, #N/A). 3 — игнорировать и пустые, и ошибки.
c — необязательный: логическое значение, определяющее, как сканировать диапазон. По умолчанию Excel сканирует по строкам (слева направо, сверху вниз). TRUE заставляет сканировать по столбцам (сверху вниз, слева направо).

Короткая справка по использованию: если нужно получить «чистый» список без пустых и ошибок, укажите b = 3. Если порядок важен по столбцам — укажите c = TRUE.

Пример 1 — базовая трансформация 2D → 1D с TOCOL

Предположим, у вас есть диапазон с менеджерами магазинов B2:F17. Чтобы получить один столбец со всеми именами:

=TOCOL(B2:F17)

Результат — «динамический массив», который автоматически заполняет ячейки вниз от той, в которую вы ввели формулу. Динамический массив будет обновляться при изменении исходных данных.

Важно: результаты динамических массивов нельзя напрямую оформить как таблицу Excel (Ctrl+T) — если нужно, сначала скопируйте диапазон и вставьте как значения (Ctrl+C → Ctrl+Shift+V).

Диапазон менеджеров извлекается в один столбец функцией TOCOL.

Пример: исключение пустых и ошибок

Чтобы удалить пустые ячейки и ошибки из результата:

=TOCOL(B2:F17,3)

Если нужно сканировать не по строкам, а по столбцам (сверху вниз, сначала первый столбец, затем второй):

=TOCOL(B2:F17,3,TRUE)

TOCOL с аргументами для игнорирования пустых и поиска по столбцам.

Пример 2 — TOROW: 2D → 1D по строке

Для трансформации в одну строку используйте TOROW:

=TOROW(B2:F17)

Аналогично TOCOL, у TOROW есть те же опции b и c. Чтобы игнорировать пустые и ошибки и сканировать по столбцам:

=TOROW(B2:F17,3,TRUE)

TOROW превращает 2D массив в одну строку.

Объединение нескольких массивов: VSTACK и HSTACK

TOCOL/TOROW работают с одиночными массивами. Чтобы объединить несколько областей, используйте VSTACK (вертикально) или HSTACK (горизонтально), а затем применяйте TOCOL/TOROW к результату.

Пример: у вас два диапазона — Европа B2:F17 и Азия B20:F35. Сначала сложите их вертикально:

=VSTACK(B2:F17,B20:F35)

Затем примените TOCOL, чтобы получить все имена в одном столбце:

=TOCOL(VSTACK(B2:F17,B20:F35),3)

Если же нужно сложить рядом и затем сплющить:

=TOCOL(HSTACK(B2:F17,B20:F35),3)

Ключевая идея: порядок VSTACK/HSTACK влияет на итоговый порядок в TOCOL/TOROW.

VSTACK и HSTACK используются вместе с TOCOL для объединения массивов.

Получение уникального и отсортированного списка

Чтобы получить только уникальные значения из 2D массива и упорядочить их, объедините TOCOL/TOROW с UNIQUE и SORT.

Пример — список медалистов:

=UNIQUE(TOCOL(B2:F17))

Затем отсортируйте:

=SORT(UNIQUE(TOCOL(B2:F17)))

Чтобы сортировать в обратном порядке, добавьте аргумент порядка:

=SORT(UNIQUE(TOCOL(B2:F17)),,-1)

Если используете TOROW, некоторые функций по умолчанию ожидают столбец, поэтому код будет выглядеть так:

=SORT(UNIQUE(TOROW(B2:F17),TRUE),,,TRUE)

UNIQUE и SORT используются с TOCOL для получения уникального и отсортированного списка.

Практические сценарии использования

Подготовка данных перед импортом в BI-систему: нормализация значений в один столбец.
Создание динамического списка контактов, сотрудников или клиентов из разрозненных таблиц.
Упрощение фильтрации и условного форматирования — легче работать с 1D-диапазонами.
Подготовка справочников для проверки данных (валидация, сопоставление по ключу).
Формирование заголовков (TOROW) и последующее заполнение значений под ними.

Типичные ошибки и как их избежать

Important: перед применением TOCOL/TOROW убедитесь, что исходные диапазоны не содержат объединённых ячеек — это часто приводит к неожиданным результатам.

Ошибка: попытка вставить динамический массив внутрь существующей заполненной области. Решение: выберите пустую стартовую ячейку или очистите область.
Ошибка: использование TOCOL/TOROW в неподдерживаемой версии Excel. Решение: проверить версию (Office → Учетная запись) и обновиться до Microsoft 365 или Excel 2024.
Ошибка: ожидание, что результат будет автоматически таблицей Excel. Решение: при необходимости сделать статичные значения и только потом форматировать как таблицу.
Ошибка: средняя или плохая производительность при очень больших массивах. Решение: использовать фильтрацию в Power Query или разбить задачу.

Производительность и ограничения

Динамические массивы работают очень быстро для типичных рабочих листов (несколько тысяч ячеек). При десятках тысяч элементов формулы могут замедлять книгу.
VSTACK/HSTACK + TOCOL/TOROW создают временные массивы, которые потребляют память. Если у вас ограниченная оперативная память, используйте Power Query для больших ETL‑задач.
Динамические массивы не работают внутри старых объектных формул и некоторых надстроек.

Совет: для больших и повторяющихся преобразований рассмотрите Power Query — он эффективнее на больших наборах данных и удобнее в повторяемых пайплайнах.

Альтернативные подходы

Power Query — лучший выбор для трансформаций больших наборов данных и построения повторяемых ETL‑процессов.
TRANSPOSE — быстрый способ поменять строки и столбцы, но не сплющивает 2D в 1D.
VBA/Office Scripts — если нужно автоматизировать сложные или повторяющиеся операции и выполнять их вне формул.
Формулы на базе INDEX/SEQUENCE — можно вручную “сплющить” диапазон в старых версиях Excel, но это сложнее и менее устойчиво.

Миграция и совместимость

Если вы разрабатываете шаблоны для пользователей с разными версиями Excel, не используйте TOCOL/TOROW в книгах, которые должны открываться в Excel 2019 или более ранних версиях без подписки.
Для совместимости: создайте альтернативный сценарий с Power Query или скриптом, который выполняет ту же задачу и доступен в более старых версиях (если установлена надстройка Power Query).

Руководства и чек-листы

Чек-лист для аналитика перед применением TOCOL/TOROW

Проверить версию Excel (поддерживает ли TOCOL/TOROW).
Убедиться, что диапазон не содержит объединённых ячеек.
Решить, нужно ли игнорировать пустые и/или ошибки (b).
Определить требуемый порядок сканирования: по строкам или по столбцам (c).
Оценить объём данных и возможное влияние на производительность.
Решить: результат должен быть динамическим или статическим.

Чек-лист для разработчика отчётов

Использовать VSTACK/HSTACK для объединения диапазонов перед TOCOL/TOROW если нужно.
Комбинировать с UNIQUE и SORT для получения чистого списка.
При необходимости документировать формулы и добавлять комментарии в лист.
Если шаблон будет эксплуатироваться внешними пользователями, добавить контроль совместимости.

Playbook: шаг за шагом для распространённой задачи

Задача: Получить один столбец с уникальными, отсортированными именами менеджеров из двух регионов (Европа и Азия), игнорируя пустые и ошибки.

В свободной ячейке введите формулу:

=VSTACK(B2:F17,B20:F35)

Оберните результат в TOCOL и попросите игнорировать пустые/ошибки:

=TOCOL(VSTACK(B2:F17,B20:F35),3)

Добавьте UNIQUE и SORT:

=SORT(UNIQUE(TOCOL(VSTACK(B2:F17,B20:F35),3)))

Если требуется, скопируйте результат и вставьте как значения, чтобы получить статичный список.

Decision tree — какую функцию выбрать

flowchart TD
  A[Нужен одномерный результат?] -->|Да| B{Вы хотите строку или столбец?}
  B -->|Столбец| C[TOCOL]
  B -->|Строка| D[TOROW]
  C --> E{Несколько диапазонов?}
  D --> E
  E -->|Да| F{Нужно сложить сверху вниз или рядом?}
  F -->|Сверху вниз| G[VSTACK + TOCOL/TOROW]
  F -->|Рядом| H[HSTACK + TOCOL/TOROW]
  E -->|Нет| I[Прямая функция TOCOL/TOROW]
  I --> J{Игнорировать пустые/ошибки?}
  J -->|Да| K[Установить b = 1,2 или 3]
  J -->|Нет| L[Опустить b]
  K --> M{Порядок сканирования важен?}
  L --> M
  M -->|По столбцам| N[Установить c = TRUE]
  M -->|По строкам| O[Оставить c опущенным]

Тест-кейсы и критерии приёмки

Критерии приёмки для формулы, которая должна возвращать уникальный отсортированный столбец менеджеров:

Результат содержит все уникальные имена из обоих диапазонов.
Пустые ячейки и ошибки отсутствуют в выходном диапазоне.
Результат отсортирован в алфавитном порядке по возрастанию.
Внесение изменения в исходный диапазон автоматически отражается в результатах (если не были вставлены значения вручную).

Пример тест-кейса:

В исходных диапазонах добавить дубликат имени — проверить, что в результате он всё ещё появляется один раз.
Вставить несколько пустых ячеек — проверить, что они не появляются в результате с b = 3.
Вызвать ошибку в одной из ячеек (#N/A) — проверить, что она не появляется при b = 3.
Проверить порядок при изменении c = TRUE и c опущенном.

Примеры готовых формул (шаблоны)

Простой список из диапазона:

=TOCOL(A1:D10)

Удаление пустых и ошибок и сканирование по столбцам:

=TOCOL(A1:D10,3,TRUE)

Объединить два диапазона и получить уникальные отсортированные значения:

=SORT(UNIQUE(TOCOL(VSTACK(A1:D10,F1:J10),3)))

Получить одну строку для заголовков:

=TOROW(A1:D10,1)

Когда лучше не использовать TOCOL/TOROW (контрпримеры)

Очень большие таблицы (сотни тысяч строк): лучше Power Query или серверные ETL‑инструменты.
Требуется совместимость с устаревшими версиями Excel: используйте альтернативные подходы.
Если нужно сложная логика очистки данных (например, сопоставления/обогащения) — лучше Power Query или скрипты.

Security и приватность

Формулы работают на локальных данных в рабочей книге. Если ваша книга синхронизируется через облако (OneDrive, SharePoint), убедитесь, что политика доступа соответствует требованиям приватности и GDPR: не размещайте личные данные без согласия и не давайте лишние права на общий доступ к файлам.

Локальные советы и частые ошибки для русскоязычных пользователей

Разделители аргументов формул в Excel зависят от региональных настроек: в русской локали обычно используется точка с запятой ‘;’ вместо запятой ‘,’. Выражения выше приведены в английской нотации с запятыми; в русской версии будет:

=TOCOL(B2:F17;3;TRUE)

=SORT(UNIQUE(TOCOL(VSTACK(B2:F17;B20:F35);3)))

Названия функций в русской локали Excel могут быть переведены (в новых версиях Microsoft постепенно унифицирует англоязычные названия), но если у пользователей русская локаль старой версии — убедитесь, какая нотация принята у вашей аудитории.

Краткий глоссарий (1 строка каждый)

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

Дополнительные ресурсы и альтернативы

Power Query — рекомендован для ETL‑задач и больших наборов данных.
Office Scripts/VBA — автоматизация сценариев, особенно при интеграции с другими приложениями.
TRANSPOSE — если нужно поменять строки и столбцы, но не сплющивать массив.

Пользователь выбирает подписку Microsoft 365 на странице продукта.

Microsoft 365 Personal

Windows, MacOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 включает доступ к Office-приложениям, 1 ТБ OneDrive для хранения и другие сервисы. Убедитесь, что ваша подписка поддерживает онлайн‑функции и обновления для доступа к последним функциям Excel.

$100 at Microsoft

Краткое резюме

TOCOL и TOROW — простые и мощные инструменты для нормализации двумерных диапазонов в одномерные.
Используйте аргументы b и c для очистки данных и управления порядком обхода.
Комбинируйте с VSTACK/HSTACK, UNIQUE и SORT для гибких рабочих процессов.
Для очень больших наборов данных или повторяемых процессов рассмотрите Power Query или автоматизацию через скрипты.

Спасибо за внимание — применяйте эти приёмы в своих отчётах, чтобы сократить ручную подготовку данных и ускорить аналитику.