Функция 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 и альтернативные решения в более ранних версиях для преобразования двумерного массива в один столбец. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!