Разбор ячейки и возврат связанных значений в другую ячейку

Question

В Excel 2016 я пытаюсь написать либо формулу массива, либо функцию vba, чтобы выполнить следующее:

1.) Пользователь вводит строку (имя проекта) в ячейку (ячейка D1 в прикрепленном примере)

2.) Ячейки в столбце B будут заполнены числом (ями), связанными со строкой имени проекта, в зависимости от содержимого столбца A, разделенного запятыми.

Столбец A содержит некоторое количество записей, разделенных запятыми. Каждая запись содержит имя проекта и связанный номер, разделенные символом %

Например, первая строка в примере изображения содержит три записи:

Project1 1234
Project2 2345
Project3 5678

В ячейке указывается «Проект1% 1234, Проект2% 2345, Проект3% 5678»

Я приложил пример изображения, которое содержит пустую версию моей проблемы, а ниже - желаемый результат, если пользователь войдет в Project1 . Я надеюсь, что это хорошо иллюстрирует проблему, но если нет, я могу уточнить.

Возможно ли это сделать с помощью формулы массива или для этого потребуется пользовательская функция в vba?

Answer 1

Да, это возможно только с помощью формулы массива. Хотя это не совсем просто.

Массив введите (Ctrl+Shift+Enter) следующую формулу в B2 и скопируйте-вставьте / заполните столбец:

{=TEXTJOIN(",",TRUE,LEFT(TRIM(MID(SUBSTITUTE(A2,$D$1&"%",REPT(" ",LEN(A2))),(ROW(INDEX(B:B,1):INDEX(B:B,LEN(A2)/2))-1)*LEN(A2)+1,LEN(A2))),IFERROR(FIND(",",TRIM(MID(SUBSTITUTE(","&A2&",",$D$1&"%",REPT(" ",LEN(A2))),(ROW(INDEX(B:B,1):INDEX(B:B,LEN(A2)/2))-1)*LEN(A2)+1,LEN(A2))))-1,0)))}

---

Предварительно подтвержденная формула выглядит следующим образом:

{=
TEXTJOIN(
  ",",
  TRUE,
  LEFT(
    TRIM(MID(SUBSTITUTE(A2,$D$1&"%",REPT(" ",LEN(A2))),(ROW(INDEX(B:B,1):INDEX(B:B,LEN(A2)/2))-1)*LEN(A2)+1,LEN(A2))),
    IFERROR(
      FIND(
        ",",
        TRIM(MID(SUBSTITUTE(","&A2&",",$D$1&"%",REPT(" ",LEN(A2))),(ROW(INDEX(B:B,1):INDEX(B:B,LEN(A2)/2))-1)*LEN(A2)+1,LEN(A2)))
      )-1,
      0
    )
  )
)}

Объяснение:

Формула работает, заменяя имя проекта плюс разделитель в целевой строке длинной последовательностью пробелов, а затем "разбивая" строку на массив кусков, каждый из которых начинается с номера проекта соответствующего проекта. Это вывод функций TRIM(…) .

Затем извлекается самая левая часть каждого фрагмента до первой запятой, то есть номера проекта, но не включая ее.

Наконец, номера проектов объединяются с помощью функции TEXTJOIN() .

Часть IFERROR в IFERROR(FIND(",",TRIM(…))-1,0) требуется для удаления ошибок, возвращаемых при сбое функции FIND() , а получающиеся пустые строки игнорируются TEXTJOIN() .

Заметки:

• Предварительно подтвержденная формула действительно работает, если введена.

Answer 2

Поскольку я не видел ответа robinCTS вовремя, я закончил тем, что написал функцию vba, которая решает эту проблему. Я подумал, что поделюсь этим, хотя это немного неуклюже, на случай, если это поможет кому-то с такой же проблемой.

Function ParseK(celltxt As String, userin As String) As String
Dim project_name As String
Dim number As String
Dim final_result As String
Dim string_array() As String
ReDim string_array(5)
Dim i As Variant
string_array = Split(celltxt, ",")
For Each i In string_array
    number = Right(i, Len(i) - InStr(i, "%"))
    project_name = Left(i, InStr(i, "%") - 1)
    If InStr(project_name, userin) > 0 Or project_name = userin Then final_result = final_result & number & ","

Next i
ParseK = final_result
End Function