Создание формулы массива, пригодной для использования с индексом
Я преобразовал результаты массива слов, разделенных запятыми, в полезные данные с помощью индексной формулы.
Индекс правильно перечисляет все данные. Однако если индексированный список превышает 33 слова, я получаю сообщение об ошибке Value и больше не могу манипулировать данными индекса.
Почему ограничение в 33 слова? Как я могу выйти за пределы этого?
Array formula: =TEXTJOIN(", ",TRUE,IF($B$2:$B$50000=1,$A$2:$A$50000, ""))
Index formula: =Index([Array],1)
Listing Index formula: =TRIM(MID(SUBSTITUTE([Index],",",REPT(" ",999)),(ROW(1:1)-1)*999+1,999))
Ожидаемые результаты: создание списка столбцов с неограниченным количеством результатов (в настоящее время работает до 33 результатов).
Фактические результаты: Формула массива генерирует список совпадающих слов, разделенных запятыми, из длинного списка слов (работает) Формула индекса преобразует этот список в строку, которой я могу манипулировать (работает) Формула Listing Index преобразует список, разделенный запятыми, в список столбцов (работает, если индексированный список <=33!)
Ответы 1
Вы превысили максимальное количество символов, которое может вернуть SUBSTITUTE. ЗАМЕНА может привести только к строке из 32 567 символов.
=SUBSTITUTE([Index], ",", REPT(" ", 999))
Приведенное выше возвращает #ЗНАЧ! если результирующая расширенная строка длиннее 32 567, максимальное значение короткого целого числа со знаком. Даже без текста 33 × 999 — это 32 967.
кстати¹, вы объединяете ,<space> в качестве разделителя, но разделяете объединенную строку на ,. Вы действительно должны выбрать один или другой.
кстати², если ваша строка множество меньше половины от 32 567, вы должны иметь возможность использовать вложенные функции ПОДСТАВКИ или комбинацию ЗАМЕНА/ЗАМЕНА, чтобы преодолеть ограничение в 33 элемента, но ограничения все равно будут. Вы не можете использовать формулы рабочего листа для «неограниченного количества результатов»; вам нужен VBA для более длинных строк.
Следующее сводит к минимуму расширение исходной строки, заменяя определенные экземпляры (четвертый аргумент функции ПОДСТАВИТЬ) на один «недопустимый» символ, который можно легко найти среди остальной части исходной строки.
=TRIM(REPLACE(REPLACE([index], FIND(CHAR(9), SUBSTITUTE([index], ", ", CHAR(9), ROW(1:1))), LEN([index]), ""), 1, IFERROR(FIND(CHAR(9), SUBSTITUTE([index], ", ", CHAR(9), ROW(1:1)-1)), 0), ""))
CHAR(9) — это символ табуляции, и хотя в конечном итоге можно принудительно вставить символ табуляции в ячейку, они обычно считаются «незаконным» содержимым ячейки, поэтому их использование в качестве уникального заполнителя довольно безопасно.
Следующая функция, определяемая пользователем, разделит любую часть строки практически любого размера.
Option Explicit
Function getPiece(str As String, ndx As Long, _
Optional delim As String = ", ")
getPiece = Split(str, delim)(ndx - 1)
End Function
'usage on worksheet with default `comma><space>` as the delimiter
=getPiece([Index], row(1:1))
Поместите приведенное выше в стандартный лист кода общедоступного модуля. Используйте Alt+F11, чтобы открыть VBE, затем Alt+I+M, чтобы вставить код модуля в проект.
Это потрясающе. Большое спасибо! Одна незначительная вещь - список теперь останавливается примерно после 230 символов:/
См. дополнения, сделанные выше.
Спасибо за объяснение. Строки массива значительно меньше 0,5 (32 567). Как я могу использовать вложенную подпрограмму / замену подпрограммы, чтобы преодолеть текущий лимит?