EXCEL: разделение значений, разделенных запятыми, в динамическом диапазоне
У меня есть таблица со значениями, разделенными запятыми, например
Это динамический диапазон. Мне нужно разделить значение, потому что мне нужно искать значения в поисках определенного значения. Для этого я использовал TEXTSPLIT, но он не работает с динамическими диапазонами. TEXTSPLIT возвращает только первое значение, если я использую диапазон в качестве первого параметра.
=TEXTSPLIT(B2:B3;",")
это возвращает
У меня это работает с параметром «Данные» > «Текст в столбцы», но мне это нужно в формуле.
В конечном итоге мне нужно отфильтровать строки и показать только определенную строку на основе динамического числа. Формула, которую я использовал:
=IFERROR(INDEX(
FILTER(B2:B3;
(IFERROR(ISNUMBER(SEARCH(2;B2:B3));FALSE))
)
;;1);"")
Это соответствует только первому значению в значении, разделенном запятыми. Поэтому я попробовал другую формулу.
=IFERROR(INDEX(
FILTER(B2:B3;
(IFERROR(ISNUMBER(SEARCH(2;B2:B3));FALSE))
)
;;1);"")
Это работает, единственная проблема в том, что если я ищу цифру 1, то также попадает цифра 10.
Ответы 4
=FILTER(B1:B5,MAP(B1:B5,LAMBDA(a,OR(VALUE(TEXTSPLIT(a,","))=2))))
Возможны разные маршруты, но из того, что вы записали, я понимаю, что вам нужно присутствие определенного динамического числа. У вас уже есть ms365 и хороший ответ другого пользователя на основе рекурсии с использованием LAMBDA(). Однако есть еще два варианта:
=FILTER(B1:B2,1-ISERR(FIND(","&4&",",","&B1:B2&",")))
Другой вариант — переключиться на БЕТА-канал и посмотреть, доступен ли REGEXTEST():
=FILTER(B1:B2,REGEXTEST(B1:B2,"\b"&4&"\b"))
@BobPikaar, это действительно не так. Я только что отредактировал снимок экрана, где искал цифру 4. Вы уверены, что в этих строках, разделенных запятыми, нет пробелов?
Спасибо @JvdV, это действительно так для первого решения. Я использовал метод SUBSTITUTE, чтобы избавиться от пробелов.
Нет необходимости, просто добавьте пробел и запятую: =FILTER(B1:B2,1-ISERR(FIND(", "&4&", ",", "&B1:B2&", ")))
Это работает в обоих случаях (с пробелом или без): =FILTER(P5:Q6;1-ISERR(FIND(","&3&","; ","&SUBSTITUTE(Q5:Q6;" ";"")&",")))
@BobPikaar, ах, у тебя смесь разных разделителей. Конечно, хорошая альтернатива.
Спасибо @user11222393,
в моем случае я получил следующую оптимизированную формулу
=IFERROR(INDEX(
FILTER(A1:B2;
IFERROR(MAP(B1:B2;LAMBDA(a;OR(VALUE(TEXTSPLIT(SUBSTITUTE(a; " "; "");","))=2)));FALSE)
);;1);"")
У вас уже есть несколько полезных ответов.
Я хотел поделиться формулой, позволяющей справиться с вашим ограничением TEXTSPLIT и возвращать только начальное значение в том случае, если вы используете его для диапазона ячеек, а не для одной ячейки:
=LET(b,B1:B2,
MAKEARRAY(ROWS(b),MAX(LEN(b)-LEN(SUBSTITUTE(b,",",""))+1),
LAMBDA(r,c,
INDEX(TEXTSPLIT(INDEX(b,r),","),,c))))
Вы также можете использовать комбинацию TEXTJOIN и TEXTSPLIT, но это не лучшая практика, поскольку для этого требуется, чтобы все значения были объединены как одно, чтобы снова разделить, что может легко достичь максимального ограничения символов TEXTJOIN:
=TEXTSPLIT(TEXTJOIN(";",,B1:B2),",",";")
Видите ли вы какие-либо преимущества или уникальные преимущества в своем решении по сравнению с другими ответами?
@BobPikaar, уникальность по сравнению с другими ответами заключается в том, что этот ответ сосредоточен на выдаче результата TEXTSPLIT (как описано в названии вашего вопроса + начале вашего вопроса). Другие решения обходят проблему, достигая описанного вами конечного результата, используя другую логику или используя MAP для сравнения строки с результатами разделения MAPPED. Поэтому мой ответ – не достичь конечной цели, а решить поставленную вами проблему.
Спасибо за разъяснения. Я сохраню это.
Первое решение, как и моя предыдущая проблема, работает только тогда, когда значение, разделенное запятыми, начинается с числа, которое вы ищете. Второе решение мне не помогло.