Генерация списка номеров с разделителями

Question

Есть ли в Excel функция, которая может генерировать список чисел, как это:

1|2|3|4|5

как показано на рисунке ниже:

Я хотел бы иметь возможность обеспечить начало и конец, что-то вроде =NUMBERSRANGE(1,50) . Нет необходимости принимать данные из двух других ячеек.

Я провел небольшое исследование, и вот моя первая функция Excel:

Function RANGENUMBERS(D7, E7)
For i = D7 To E7
    RANGENUMBERS = i & "|"
Next
End Function

Как бы я пошел и объединить число из цикла For, чтобы получить желаемый результат?

Answer 1

Для этого требуется TEXTJOIN() :

Массив введите (Ctrl+Shift+Enter) следующую формулу (не забудьте удалить { и }):

{=TEXTJOIN("|",TRUE,ROW(INDEX(C:C,1):INDEX(C:C,5)))}

Хотя это работает как есть, предпочтительно заменить C:C на столбец, в который введена формула. Таким образом, формула не будет нарушена, если столбец C будет удален.

Обратите внимание, что TEXTJOIN() доступен только в Excel 2016. В более ранних версиях Excel требуется UDF с полизаполнением. (См. Этот пост для основного.)

Также обратите внимание, что это лучшее решение, чем вездесущая альтернатива, которая использует функцию INDIRECT() ,

{=TEXTJOIN("|",TRUE,ROW(INDIRECT("1:5")))}

так как INDIRECT() является изменчивым и заставляет лист вычислять медленнее.

Также легче изменить версию INDEX чтобы конечные точки были ссылками, а не жестко закодированными значениями.

Answer 2

Аналогично телефонному номеру и маскированию zip+4, если номер всегда будет состоять из 5 цифр, то как насчет использования пользовательского формата номера, такого как: # | # | # | # | #