Функция Excel TOCOL для преобразования диапазона в один столбец

Простой способ преобразовать массив или диапазон в столбец с помощью функции TOCOL.

Возможность переносить данные из столбцов в строки и наоборот уже давно есть в Excel. Но преобразовать диапазон ячеек в один столбец оказалось сложной задачей. Теперь это, наконец, меняется. Microsoft представила новую функцию под названием TOCOL, которая может мгновенно выполнять преобразование массива в столбец. Ниже приведен список задач, которые с легкостью может решить эта новая функция.

Как использовать функцию TOCOL в Excel – примеры формул

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

Преобразовать массив в столбец, игнорируя пробелы и ошибки

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

В результирующем массиве пустые ячейки представлены нулями, что может сбивать с толку, особенно если исходный массив имеет 0 значений. Решение состоит в том, чтобы пропустить пробелы. Для этого вы устанавливаете 2-й аргумент равным 1:

=ТОКОЛ(A2:C5, 1)

Чтобы игнорировать ошибки, установите для второго аргумента значение 2:

=ТОКОЛ(A2:C5, 2)

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

=ТОКОЛ(A2:C5, 3)

Сканировать массив горизонтально или вертикально

С аргументом scan_by_column по умолчанию (FALSE или опущен) функция TOCOL сканирует массив по горизонтали по строкам. Чтобы обрабатывать значения по столбцу, установите для этого аргумента значение TRUE или 1. Например:

=ТОКОЛ(A2:C5, ,ИСТИНА)

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

Объединение нескольких диапазонов в один столбец

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

Предполагая, что первый диапазон — это A2:C4, а второй — A8:C9, формула примет следующий вид:

=TOCOL(VSTACK(A2:C4, A8:C9))

Эта формула демонстрирует поведение по умолчанию — объединенные массивы считываются по горизонтали слева направо, как показано в столбце E на изображении ниже.

Чтобы читать значения по вертикали сверху вниз, вы устанавливаете 3-й аргумент TOCOL в TRUE:

=TOCOL(VSTACK(A2:C4, A8:C9), ,ИСТИНА)

Обратите внимание, что в этом случае формула сначала возвращает значения из столбца A обоих массивов, затем из столбца B и так далее. Причина в том, что TOCOL сканирует единый сложенный массив, а не исходные отдельные диапазоны.

Если ваша бизнес-логика требует размещения исходных диапазонов по горизонтали, а не по вертикали, используйте функцию HSTACK вместо VSTACK.

Чтобы добавить каждый последующий массив справа от предыдущего массива и прочитать объединенные массивы по горизонтали, используйте формулу:

=TOCOL(HSTACK(A2:C4, A8:C10))

Чтобы добавить каждый последующий массив справа от предыдущего массива и просмотреть объединенные массивы по вертикали, используйте формулу:

=TOCOL(HSTACK(A2:C4, A8:C10), ,ИСТИНА)

Извлечение уникальных значений из диапазона, состоящего из нескольких столбцов

Функция Excel UNIQUE может легко находить уникальные значения в одном столбце или строке, а также возвращать уникальные строки, но не может извлекать уникальные значения из массива с несколькими столбцами. Решение состоит в том, чтобы использовать его вместе с функцией TOCOL.

Например, чтобы извлечь все разные (отличающиеся) значения из диапазона A2:C7, используйте следующую формулу:

=УНИКАЛЬНЫЙ(ТОКОЛ(A2:C7))

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

=СОРТИРОВКА(УНИКАЛЬНАЯ(ТОКОЛ(A2:C7)))

Как преобразовать диапазон в столбец в Excel 365 – 2010

В версиях Excel, где функция TOCOL не поддерживается, существует несколько альтернативных способов преобразования диапазона ячеек в столбец. Эти решения довольно сложны, но в любом случае работают.

Чтобы прочитать диапазон по строке:

ИНДЕКС(диапазон, ЧАСТНОЕ(СТРОКА(A1)-1, СТОЛБЦ(диапазон))+1, ОСТАТ(СТРОКА(A1)-1, СТОЛБЦ(диапазон))+1)

Чтобы прочитать диапазон по столбцу:

ИНДЕКС(диапазон, ОСТАТ(СТРОКА(A1)-1, СТРОКИ(диапазон))+1, ЧАСТНОЕ(СТРОКА(A1)-1, СТРОКИ(диапазон))+1)

Для нашего примера набора данных формулы следующие:

Для сканирования диапазона по горизонтали слева направо:

=ИНДЕКС($A$2:$C$5, ЧАСТНОЕ(СТРОКА(A1)-1, СТОЛБЦЫ($A$2:$C$5))+1, MOD(СТРОКА(A1)-1, СТОЛБЦ($A$2:$) 5 канадских долларов))+1)

Эта формула эквивалентна функции TOCOL с третьим аргументом, установленным в FALSE или опущенным:

=ТОКОЛ(A2:C5)

Для сканирования диапазона по вертикали сверху вниз:

=ИНДЕКС($A$2:$C$5, MOD(СТРОКА(A1)-1, СТРОКИ($A$2:$C$5))+1, ЧАСТНОЕ(СТРОКА(A1)-1, СТРОКИ($A$2:$ 5 канадских долларов))+1)

Эта формула сравнима с функцией TOCOL с третьим аргументом, установленным в TRUE:

=ТОКОЛ(A2:C5, ,ИСТИНА)

В отличие от TOCOL, альтернативные формулы следует вводить в каждую ячейку, где должны отображаться результаты. В нашем случае формулы идут в ячейки E2 (по строке) и G2 (по столбцу), а затем копируются вниз до 13 строки.

Если формулы копируются в большее количество строк, чем необходимо, возникает ошибка #ССЫЛКА! ошибка появится в “лишних” ячейках. Чтобы этого не произошло, вы можете вложить формулы в функцию ЕСЛИОШИБКА следующим образом:

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$C$5, ЧАСТНОЕ(СТРОКА(A1)-1, СТОЛБЦЫ($A$2:$C$5))+1, MOD(СТРОКА(A1)-1, СТОЛБЦ($A$2) :$C$5))+1), “”)

Обратите внимание, что для правильного копирования формул мы блокируем диапазон, используя абсолютные ссылки на ячейки ($A$2:$C$5). Вместо этого вы можете использовать именованный диапазон.

Как работают эти формулы

Ниже приведена подробная разбивка первой формулы, которая упорядочивает ячейки по строкам:

=ИНДЕКС($A$2:$C$5, ЧАСТНОЕ(СТРОКА(A1)-1, СТОЛБЦЫ($A$2:$C$5))+1, MOD(СТРОКА(A1)-1, СТОЛБЦ($A$2:$) 5 канадских долларов))+1)

Идея состоит в том, чтобы использовать функцию ИНДЕКС для возврата значения определенной ячейки на основе ее относительных номеров строк и столбцов в диапазоне.

Номер строки рассчитывается с помощью этой комбинации:

ЧАСТНОЕ(СТРОКА(A1)-1, СТОЛБЦЫ($A$2:$C$5))+1

ЧАСТНОЕ возвращает целую часть деления.

В качестве числителя вы используете ROW(A1)-1, который возвращает порядковый номер от 0 в E2 (первая ячейка, где введена формула) до 11 в E13 (последняя ячейка, где введена формула).

Знаменатель, полученный COLUMNS($A$2:$C$5)) постоянен и равен количеству столбцов в вашем диапазоне (3 в нашем случае).

Теперь, если вы проверите результат ЧАСТНОГО для первых 3 ячеек (E2:E4), вы увидите, что он равен 0 (поскольку целая часть деления равна нулю). Добавление 1 дает строку номер 1.

Для следующих 3 ячеек (E5:E5) ЧАСТНОЕ возвращает 1, а операция +1 возвращает строку с номером 2. И так далее.

Другими словами, эта часть формулы создает повторяющуюся числовую последовательность, такую ​​как 1,1,1,2,2,2,3,3,3,4,4,4,… Каждое число повторяется столько раз, сколько имеется столбцы в вашем диапазоне.

Чтобы вычислить номер столбца, вы строите соответствующую числовую последовательность, используя функцию ОСТАТ:

MOD(СТРОКА(A1)-1, СТОЛБЦЫ($A$2:$C$5))+1

Поскольку в нашем диапазоне 3 столбца (A2:C5), последовательность должна быть 1,2,3,1,2,3,…

Функция ОСТАТ возвращает остаток после деления.

В E2 MOD(СТРОКА(A1)-1, СТОЛБЦЫ($A$2:$C$5))+1)

становится

МОД(1-1, 3)+1)

и возвращает 1.

В E3 MOD(СТРОКА(A2)-1, СТОЛБЦЫ($A$2:$C$5))+1)

становится

МОД(2-1, 3)+1)

и возвращает 2.

С установленными номерами строк и столбцов ИНДЕКС без проблем получает требуемое значение.

В E2 ИНДЕКС($A$2:$C$5, 1, 1) возвращает значение из 1-й строки и 1-го столбца указанного диапазона, т. е. из ячейки A2.

В E3 ИНДЕКС($A$2:$C$5, 1, 2) возвращает значение из 1-й строки и 2-го столбца, т.е. из ячейки B2.

И так далее.

Вторая формула, сканирующая диапазон по столбцу, работает аналогичным образом. Разница в том, что он использует MOD для получения номера строки и QUOTIENT для получения номера столбца.

Вот как вы можете использовать функцию TOCOL в Excel 365 и альтернативные решения в более ранних версиях для преобразования двумерного массива в один столбец. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!