Я намерен преобразовать эти имена из списка в матрицу диапазонов, как показано на изображении. При отладке формулы я хочу, чтобы она отображала мне именно массив диапазонов строк и столбцов. Есть ли способ преобразовать эти имена в диапазон, подобный этому?
решение1
Если вам нужна текстовая строка, то используйте:
="={"""&TEXTJOIN(""",""",,SUBSTITUTE($A$2:$A$5," ","""/""")) & """}"
Если вам нужен реальный массив, который будет использоваться где-то еще, то используйте:
IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(TRANSPOSE($A$2:$A$5)," ","</b><b>")&"</b></a>","//b["&ROW($A$1:$A$4)&"]"),"")
Это создаст массив 4x4, который можно использовать в другой формуле. В зависимости от версии, использование этого в другой формуле потребует использования Ctrl-Shift-Enter вместо Enter при выходе из режима редактирования, чтобы принудительно применить формулу массива.
Я не знаю, как сделать то, что вам нужно, F9и отобразить массив в строке формул.
решение2
Ну, буквальную строку, за исключением опечатки «JONH», которую я просто не собираюсь воспроизводить, можно получить с помощью формулы Скотта Крейнера, немного измененной (и я использовал ее SEQUENCE()вместо ROWS()трюка, надо использовать новые функции, чтобы привыкнуть к ним, а?), а затем использовать ее в качестве основы для некоторой работы со строками:
="={"""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(B234:B237),SEQUENCE(4)))&""","""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(C234:C237),SEQUENCE(4)))&""","""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(D234:D237),SEQUENCE(4)))&""","""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(E234:E237),SEQUENCE(4)))&"""}"
Имейте в виду, что хотя это и дает буквальный желаемый результат, на самом деле результатНЕТконстанта массива с точки зрения Excel и НЕ будет использоваться им именно так, как это F9было бы в результате.
Это просто какая-то текстовая строка для всех забот Excel. Поэтому использование ее внутри формулы, скорее всего, приведет к неудаче или успеху по чистой случайности. Возможно, это также будет работать правильно для некоторых применений. Но это никоим образом не то же самое или эквивалентно Excel, поскольку константа массива внутреннего использования, выделяющая что-то и нажимающая F9создает.
Тем не менее, это уродливо и раздуто. Я не слежу за веткой в комментариях, я полагаю, поэтому не понимаю, почему Скотт Крейнер использовал TRANSPOSE()то, что заставило меня сделать то же самое, поскольку, похоже, желаемый вывод — слева направо (комментарий выше с "A234 = "Jonh",...), поэтому, убрав его, а затем настроив его A2:A5 на A1:A4 (или удалив строку для моих экспериментов), я получаю:
=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(($A$234:$A$237)," ","</b><b>")&"</b></a>","//b["&SEQUENCE(1,4)&"]"),"")
для его формулы и переработав мою, а затем смешав его, чтобы использовать единую формулу, я получаю:
="={"""&TEXTJOIN("""\""",,B234:E234)&""","""&TEXTJOIN("""\""",,B235:E235)&""","""&TEXTJOIN("""\""",,B236:E236)&""","""&TEXTJOIN("""\""",,B237:E237)&"""}"
который, кстати, поскольку он шаблонный, повторяющийся по конструкции, можно использовать старые CONCATENATE()или union operatorдневные приемы, чтобы построить его чрезвычайно, аккуратно подгоняя строки и столбцы перед тем, как окончательно объединить, вставить специально как значение, затем поставить "=" перед ним, чтобы сделать его формулой. Теперь еще проще. Для данных, которые не находятся в четырех крошечных строках и менее чем в пяти элементах.
Итак, это дает желаемую результирующую строку, хотя, опять же, это не тот результат, который Excel будет рассматривать как тот же самый, что и константа массива, которую вы видите после использования, F9как указано выше.
И измененная формула Скотта Крейнера затем дала бы результат отдельной ячейки, описанный в комментарии. Который сам по себе мог бы быть использован внутри формулы, чтобы позволить Excel создать эту внутреннюю константу массива. Но НЕ, я думаю, с помощью строковой формулы (которая является тупиком, с точки зрения вычислений), а скорееиспользуя формулу Скотта Крейнера (скорректированную) внутри любой формулы, которая вам нужна в so Excel will evaluate it to the desiredконстанте массива, а затем, вместо того, чтобы заполнять эти 16 ячеек, передать его вверх по цепочке вычислений внутри этой формулы.
Только что понял, что это «Скотт Тенорман» (или что-то вроде того) из «Южного Парка», тот, которому Картман... мстит... Меня больше не смущает повторение «Скотт Крейнер» снова и снова!