Функция Excel TOOW для преобразования диапазона или массива в строку
Быстрый способ превратить диапазон ячеек в одну строку с помощью команды ГРЕСТИ функция.
Microsoft Excel 365 представил несколько новых функций для выполнения различных манипуляций с массивами. С TOROW вы можете выполнять преобразования диапазона в строку в кратчайшие сроки. Вот список задач, которые может выполнять эта новая функция:
Функция Excel ТОРОВ
Функция TOROW в Excel используется для преобразования массива или диапазона ячеек в одну строку.
Функция принимает в общей сложности три аргумента, из которых требуется только первый.
ТОРУ(массив, [ignore], [scan_by_column])
Где:
Массив (обязательно) — массив или диапазон для преобразования в одну строку.
Игнорировать (необязательно) — определяет, следует ли игнорировать пробелы и/или ошибки. Может принимать одно из следующих значений:
- 0 или опущено (по умолчанию) — сохранить все значения
- 1 – игнорировать пробелы
- 2 – игнорировать ошибки
- 3 – игнорировать пробелы и ошибки
Scan_by_column (необязательно) — определяет, как сканировать массив:
- FALSE или опущено (по умолчанию) – сканирование массива по горизонтали построчно.
- TRUE – сканирование массива по столбцам по вертикали.
Советы:
- Чтобы преобразовать массив в один столбец, используйте функцию TOCOL.
- Чтобы выполнить обратное преобразование строки в массив, используйте либо функцию WRAPCOLS для переноса в столбцы, либо функцию WRAPROWS для переноса массива в строки.
- Чтобы превратить строки в столбцы, используйте функцию ТРАНСП.
ТОРОВ доступность
TOROW — это новая функция, которая поддерживается только в Excel для Microsoft 365 (для Windows и Mac) и Excel для Интернета.
Базовая формула ТОРОВ в Excel
Чтобы выполнить простое преобразование диапазона в строку, используйте формулу TOROW в ее базовой форме. Для этого вам нужно определить только первый аргумент (массив).
Например, чтобы превратить двумерный массив, состоящий из 3 столбцов и 3 строк, в одну строку, используйте следующую формулу:
=ВОРОТ(A3:C6)
Вы вводите формулу только в одну ячейку (в нашем случае A10), и она автоматически распределяется по стольким ячейкам, сколько необходимо для хранения всех результатов. В терминах Excel выходной диапазон, обведенный тонкой синей рамкой, называется диапазоном разлива.
Как работает эта формула:
Сначала предоставленный диапазон ячеек преобразуется в двумерный массив. Обратите внимание на столбцы, разделенные запятыми, и строки, разделенные точкой с запятой:
{“Яблоко”,”Банан”,”Вишня”;1,2,3;4,5,6;7,8,9}
Затем функция TOROW считывает массив слева направо и преобразует его в одномерный горизонтальный массив:
{“Яблоко”,”Банан”,”Вишня”,1,2,3,4,5,6,7,8,9}
Результат попадает в ячейку А10, откуда выливается в соседнюю ячейку справа.
Преобразовать диапазон в строку, игнорируя пробелы и ошибки
По умолчанию функция TOROW сохраняет все значения из исходного массива, включая пустые ячейки и ошибки. В выводе вместо пустых ячеек появляются нулевые значения, что может сбивать с толку.
Чтобы исключить пробелы, установите аргумент ignore равным 1:
=ВОРОТ(A3:C5, 1)
Чтобы игнорировать ошибки, установите аргумент ignore равным 2:
=ВОРОТ(A3:C5, 2)
Чтобы пропустить и пробелы, и ошибки, используйте 3 в качестве аргумента игнорирования:
=ВОРОТ(A3:C5, 3)
На изображении ниже показаны все три сценария в действии:
Чтение массива по горизонтали или по вертикали
По умолчанию функция TOROW обрабатывает массив горизонтально слева направо. Чтобы сканировать значения по столбцу сверху вниз, вы устанавливаете 3-й аргумент (scan_by_column) в TRUE или 1.
Например, чтобы прочитать исходный диапазон по строкам, формула в E3 выглядит следующим образом:
=ВОРОТ(A3:C5)
Для сканирования диапазона по столбцу формула в E8 выглядит так:
=ЗАПИСЬ(A3:C5, ,ИСТИНА)
В обоих случаях результирующие массивы имеют одинаковый размер, но значения расположены в другом порядке.
Объединить несколько диапазонов в одну строку
Чтобы объединить несколько несмежных диапазонов в одну строку, вы сначала складываете их по горизонтали или вертикали в единый массив с помощью HSTACK или VSTACK соответственно, а затем с помощью функции TOROW преобразуете объединенный массив в строку.
В зависимости от вашей бизнес-логики одну из следующих формул выполнит задачу.
Складывать массивы по горизонтали и преобразовывать по строкам
С первым диапазоном в A3:C4 и вторым диапазоном в A8:C9, приведенная ниже формула будет складывать два диапазона по горизонтали в один массив, а затем преобразовывать его в строку, считывающую значения слева направо. Результат в E3 на изображении ниже.
=ЗАПИСЬ(СТЕК(A3:C4, A8:C9))
Складывать массивы по горизонтали и преобразовывать по столбцам
Чтобы прочитать сложенный массив вертикально сверху вниз, вы устанавливаете 3-й аргумент TOROW в TRUE, как показано в E5 на изображении ниже:
=ТОРГ(HSTACK(A3:C4, A8:C9), ,ИСТИНА)
Складывать массивы по вертикали и преобразовывать по строкам
Чтобы добавить каждый последующий массив в конец предыдущего массива и прочитать объединенный массив по горизонтали, используйте формулу в E12:
=ТОРГ(ВСТЭК(A3:C4, A8:C9))
Стек массивов по вертикали и преобразование по столбцу
Чтобы добавить каждый последующий массив в конец предыдущего и просмотреть объединенный массив по вертикали, формула выглядит так:
=ТОРГ(ВСТЭК(A3:C4, A8:C9), ,ИСТИНА)
Чтобы лучше понять логику, обратите внимание на разный порядок значений в результирующих массивах:
Извлечение уникальных значений из диапазона в строку
Начиная с Microsoft Excel 2016, у нас есть замечательная функция с именем UNIQUE, которая может легко получать уникальные значения из одного столбца или строки. Однако он не может обрабатывать массивы из нескольких столбцов. Чтобы обойти это ограничение, используйте функции UNIQUE и TOROW вместе.
Например, чтобы извлечь все разные (отличающиеся) значения из диапазона A2:C7 и поместить результаты в одну строку, используйте следующую формулу:
=УНИКАЛЬНЫЙ(ТОРОВ(A2:C7), ИСТИНА)
Поскольку TOROW возвращает одномерный горизонтальный массив, мы устанавливаем для второго аргумента (by_col) UNIQUE значение TRUE, чтобы сравнивать столбцы друг с другом.
Если вы хотите, чтобы результаты были расположены в алфавитном порядке, оберните приведенную выше формулу в функцию SORT:
= СОРТИРОВКА (УНИКАЛЬНАЯ (ТОЧКА (A2: C7), ИСТИНА), , , ИСТИНА )
Как и в случае с UNIQUE, аргумент by_col функции SORT также имеет значение TRUE.
Альтернатива ТОРОВ для Excel 365 – 2010
В версиях Excel, где функция TOROW недоступна, вы можете преобразовать диапазон в одну строку, используя комбинацию нескольких различных функций, которые работают в более старых версиях. Эти решения сложнее, но они работают.
Для сканирования диапазона по горизонтали используется общая формула:
ИНДЕКС(диапазон, ЧАСТНОЕ(СТОЛБЦ (A1)-1, СТОЛБЦ(диапазон))+1, ОСТАТ(СТОЛБЦ(A1)-1, СТОЛБЦ(диапазон))+1)
Для сканирования диапазона по вертикали используется общая формула:
ИНДЕКС(диапазон, ОСТАТ(СТОЛБЦ(A1)-1, СТОЛБЦ(диапазон))+1, ЧАСТНОЕ(СТОЛБЦ (A1)-1, СТОЛБЦ(диапазон))+1)
Для нашего примера набора данных в формате A3:C5 формулы имеют следующий вид:
Чтобы просмотреть диапазон по строке:
=ИНДЕКС($A$3:$C$5, ЧАСТНОЕ(СТОЛБЦ(A1)-1, СТОЛБЦ($A$3:$C$5))+1, MOD(СТОЛБЦ(A1)-1, СТОЛБЦ($A$3:$ 5 канадских долларов))+1)
Эта формула является альтернативой функции TOROW с третьим аргументом, установленным в FALSE или опущенным:
=ВОРОТ(A3:C5)
Чтобы просмотреть диапазон по столбцу:
=ИНДЕКС($A$3:$C$5, MOD(СТОЛБЦ(A1)-1, СТОЛБЦ($A$3:$C$5))+1, ЧАСТНОЕ(СТОЛБЦ(A1)-1, СТОЛБЦ($A$3:$ 5 канадских долларов))+1)
Эта формула эквивалентна функции TOROW с третьим аргументом, равным TRUE:
=ЗАПИСЬ(A3:C5, ,ИСТИНА)
Обратите внимание, что в отличие от функции динамического массива TOOW, эти традиционные формулы следует вводить в каждую ячейку, в которой должны отображаться результаты. В нашем случае первая формула (по строке) идет в E3 и копируется через M3. Вторая формула (по столбцу) попадает в E8 и перетаскивается через M8.
Чтобы формулы копировались правильно, мы блокируем диапазон с помощью абсолютных ссылок ($A$3:$C$5). Также подойдет именованный диапазон.
Если вы скопировали формулы в большее количество ячеек, чем нужно, #ССЫЛКА! ошибка появится в “лишних” ячейках. Чтобы исправить это, оберните формулу функцией ЕСЛИОШИБКА следующим образом:
=ЕСЛИОШИБКА(ИНДЕКС($A$3:$C$5, ЧАСТНОЕ(СТОЛБЦ(A1)-1, СТОЛБЦ($A$3:$C$5))+1, MOD(СТОЛБЦ(A1)-1, СТОЛБЦ($A$3) :$C$5))+1), “”)
Как работают эти формулы
Ниже приведена подробная разбивка первой формулы, которая упорядочивает значения по строкам:
=ИНДЕКС($A$3:$C$5, ЧАСТНОЕ(СТОЛБЦ(A1)-1, СТОЛБЦ($A$3:$C$5))+1, MOD(СТОЛБЦ(A1)-1, СТОЛБЦ($A$3:$ 5 канадских долларов))+1)
В основе формулы мы используем функцию ИНДЕКС, чтобы получить значение ячейки на основе ее относительного положения в диапазоне.
Номер строки рассчитывается по этой формуле:
ЧАСТНОЕ(СТОЛБЦ(A1)-1, СТОЛБЦ($A$3:$C$5))+1
Идея состоит в том, чтобы создать повторяющуюся числовую последовательность, такую как 1,1,1,2,2,2,3,3,3, …, где каждое число повторяется столько раз, сколько столбцов в исходном диапазоне. И вот как мы это делаем:
ЧАСТНОЕ возвращает целую часть деления.
В качестве числителя мы используем COLUMN(A1)-1, который возвращает порядковый номер от 0 в первой ячейке, где введена формула, до n (общее количество значений в диапазоне минус 1) в последней ячейке, где введена формула. . В этом примере у нас есть 0 в E2 и 8 в M3.
В качестве знаменателя мы используем COLUMNS($A$3:$C$5)). Это возвращает постоянное число, равное количеству столбцов в вашем диапазоне (3 в нашем случае).
В результате функция ЧАСТНОЕ возвращает 0 в первых 3 ячейках (E3:G3), к которым мы добавляем 1, поэтому номер строки равен 1.
Для следующих 3 ячеек (H3:J3) ЧАСТНОЕ возвращает 1, а +1 дает номер строки 2. И так далее.
Чтобы вычислить номер столбца, вы строите соответствующую числовую последовательность, используя функцию ОСТАТ:
MOD(СТОЛБЦ(A1)-1, СТОЛБЦ($A$3:$C$5))+1
Поскольку в нашем диапазоне 3 столбца, последовательность должна выглядеть так: 1,2,3,1,2,3,…
Функция ОСТАТ возвращает остаток после деления.
В E3 MOD(СТОЛБЦ(A1)-1, СТОЛБЦ($A$3:$C$5))+1
становится
МОД(1-1, 3)+1)
и возвращает 1.
В F3, MOD(СТОЛБЦ(B1)-1, СТОЛБЦ($A$3:$C$5))+1
становится
МОД(2-1, 3)+1)
и возвращает 2.
Как только номера строки и столбца установлены, ИНДЕКС легко извлекает значение на пересечении этой строки и столбца.
В E3 ИНДЕКС($A$3:$C$5, 1, 1) возвращает значение из 1-й строки и 1-го столбца указанного диапазона, т. е. из ячейки A3.
В F3 ИНДЕКС($A$3:$C$5, 1, 2) возвращает значение из 1-й строки и 2-го столбца, т.е. из ячейки B3.
И так далее.
Вторая формула, сканирующая диапазон по столбцу, работает аналогичным образом. Разница в том, что мы используем MOD для вычисления номера строки и QUOTIENT для определения номера столбца.
Функция ТОРО не работает
Если функция TOROW приводит к ошибке, это, скорее всего, вызвано одной из следующих причин:
#ИМЯ? ошибка
С большинством функций Excel #NAME? ошибка является явным признаком того, что имя функции написано с ошибкой. С TOROW это также может означать, что функция недоступна в вашем Excel. Если ваша версия Excel отличается от 365, попробуйте использовать ТОРОВ альтернатива.
#номер ошибки
Ошибка #ЧИСЛО указывает на то, что возвращаемый массив не помещается в строку. Чаще всего это происходит, когда вы ссылаетесь на целые столбцы и/или строки вместо меньшего диапазона.
#ИГРА ошибка
В большинстве случаев ошибка #SPILL предполагает, что в строке, в которой вы ввели формулу, недостаточно пустых ячеек для переноса результатов. Если соседние ячейки визуально пусты, убедитесь, что в них нет пробелов и других непечатных символов. Дополнительные сведения см. в статье Что означает ошибка #SPILL в Excel.
Вот как вы используете функцию TOROW в Excel для преобразования двумерного массива или диапазона в одну строку. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Функция Excel ТОРОВ – примеры формул (файл .xlsx)
