Excel - FILTERXML을 사용하여 문자열에서 하위 문자열 추출

Excel - FILTERXML을 사용하여 문자열에서 하위 문자열 추출

_배경

최근에는 구분된 문자열을 XML로 변경하여 Excel로 해석하고 관심 있는 하위 문자열을 검색한다는 개념에 익숙해지려고 노력하고 있습니다.이 기능은 Excel 2013부터 제공되었으며 Mac용 Excel 및 Excel Online에서는 사용할 수 없습니다.

구분된 문자열은 구분자로 공백을 사용하거나 문자열 내의 하위 문자열을 정의하기 위해 사용할 수 있는 다른 문자 조합을 의미합니다.예를 들어 다음과 같이 가정합니다.

ABC|123|DEF|456|XY-1A|ZY-2F|XY-3F|XY-4f|xyz|123

---

_질문.

많은 사람들이 n번째 요소를 얻는 방법을 알고 있습니다(예: =TRIM(MID(SUBSTITUTE(A1,"|",REPT(" ",LEN(A1))),3*LEN(A1)+1,LEN(A1)))456 ""를 포함한 기타 LEN(),MID(),FIND() 그 구성에서는 할 수 요?FILTERXML해당 서브스트링을 추출하고 전체 스트링을 청소하기 위해 보다 구체적인 기준을 사용할 수 있습니까?" " " , " " : " :

• 위치별 요소
• 숫자 또는 숫자 이외의 요소
• 서브스트링 자체를 포함하는 요소
• 서브스트링으로 시작하거나 끝나는 요소
• 대소문자 요소
• 수를 유지하는 요소
• 독자적인 가치관
• ...

의 ★★★FILTERXMLXPATH 1.0불행하게도 우리가 원하는 만큼 다양하지 않다는 뜻이죠또한 Excel은 재작업된 노드값을 반환할 수 없는 것으로 보이며, 보기 순서대로 노드를 선택할 수 있습니다.그러나 여전히 활용할 수 있는 기능은 상당히 많습니다.이에 대한 자세한 내용은 여기를 참조하십시오.

는 두 매개 변수를 . 즉, ㄴㄴㄴㄴㄴㄴㄴㄴㄴㄴㄴㄴㄴㄴㄴㄴㄴㄴㄴㄴㄴㄴㄴㄴ.=FILTERXML(<A string in valid XML format>,<A string in valid XPATH format>)

라고 칩시다A1에는 다음과 같은.ABC|123|DEF|456|XY-1A|ZY-2F|XY-3F|XY-4f|xyz|123하려면 , 「XML」을 사용합니다SUBSTITUTEend-tag 、 start-tag 、 start-tag 、 ----다다다다다다다다다다-----.따라서 주어진 예에 대해 유효한 XML 구성을 얻으려면 다음을 수행합니다.

"<t><s>"&SUBSTITUTE(A1,"|","</s><s>")&"</s></t>"

가독성을 위해 위의 구문을 플레이스 홀더라는 단어로 참조합니다.아래에는 다양한 유용한 기능이 있습니다.XPATH유효한 구조에서 노드를 필터링하기 위한 함수:

---

_{1) 모든 요소:}

=FILTERXML(<XML>,"//s")

_{품::ABC,123,DEF,456,XY-1A,ZY-2F,XY-3F,XY-4f,xyz ★★★★★★★★★★★★★★★★★」123 노드모든 노드)}

---

_{2) 위치별 요소:}

=FILTERXML(<XML>,"//s[position()=4]")

또는 다음 중 하나를 선택합니다.

=FILTERXML(<XML>,"//s[4]")

_{품::456) (4의 노드)}^†

=FILTERXML(<XML>,"//s[position()<4]")

_{품::ABC,123 ★★★★★★★★★★★★★★★★★」DEF < ( < 4 ）}

=FILTERXML(<XML>,"//s[position()=2 or position()>5]")

_{품::123,ZY-2F,XY-3F,XY-4f,xyz ★★★★★★★★★★★★★★★★★」123 2 5('2' > '5')}

=FILTERXML(<XML>,"//s[last()]")

_{품::123 인덱스의 )('노드}

=FILTERXML(<XML>,"//s[position() mod 2 = 1]")

_{품::ABC,DEF,XY-1A,XY-3F ★★★★★★★★★★★★★★★★★」xyz 노드홀수 노드)}

=FILTERXML(<XML>,"//s[position() mod 2 = 0]")

_{품::123,456,ZF-2F,XY-4f ★★★★★★★★★★★★★★★★★」123 노드노드)}

---

_{3) (비) 숫자 요소:}

=FILTERXML(<XML>,"//s[number()=.]")

또는 다음 중 하나를 선택합니다.

=FILTERXML(<XML>,"//s[.*0=0]")

_{품::123,456 , , , , 입니다.123 노드스위치 노드)}

=FILTERXML(<XML>,"//s[not(number()=.)]")

또는 다음 중 하나를 선택합니다.

=FILTERXML(<XML>,"//s[.*0!=0)]")

_{품::ABC,DEF,XY-1A,ZY-2F,XY-3F,XY-4f ★★★★★★★★★★★★★★★★★」xyz 노드비표준 노드')}

---

_{4) 다음을 포함하는 요소:}

=FILTERXML(<XML>,"//s[contains(., 'Y')]")

_{품::XY-1A,ZY-2F,XY-3F ★★★★★★★★★★★★★★★★★」XY-4f('Y' 함, 알))XPATH단, 대소문자를 구분하지 않습니다.xyz)}

=FILTERXML(<XML>,"//s[not(contains(., 'Y'))]")

_{품::ABC,123,DEF,456,xyz ★★★★★★★★★★★★★★★★★」123 ('Y' 、 'Y' 、 'Y' ）XPATH합니다.xyz)}

---

_{5) 시작 또는 종료가 다음과 같은 요소:}

=FILTERXML(<XML>,"//s[starts-with(., 'XY')]")

_{품::XY-1A,XY-3F ★★★★★★★★★★★★★★★★★」XY-4f'로 시작)}

=FILTERXML(<XML>,"//s[not(starts-with(., 'XY'))]")

_{품::ABC,123,DEF,456,ZY-2F,xyz ★★★★★★★★★★★★★★★★★」123XY')로}

=FILTERXML(<XML>,"//s[substring(., string-length(.) - string-length('F') +1) = 'F']")

_{품::DEF,ZY-2F ★★★★★★★★★★★★★★★★★」XY-3F, ('F'로 끝남, 알림)XPATH 1.0에서는, ★★★★★★★★★★★를 서포트하고 않습니다.ends-with)}

=FILTERXML(<XML>,"//s[not(substring(., string-length(.) - string-length('F') +1) = 'F')]")

_{품::ABC,123,456,XY-1A,XY-4f,xyz ★★★★★★★★★★★★★★★★★」123}

=FILTERXML(<XML>,"//s[starts-with(., 'X') and substring(., string-length(.) - string-length('A') +1) = 'A']")

_{품::XY-1A하여 '로)}

---

_{6) 대소문자 요소:}

=FILTERXML(<XML>,"//s[translate(.,'abcdefghijklmnopqrstuvwxyz','ABCDEFGHIJKLMNOPQRSTUVWXYZ')=.]")

_{품::ABC,123,DEF,456,XY-1A,ZY-2F,XY-3F ★★★★★★★★★★★★★★★★★」123 노드스위치 노드)}

=FILTERXML(<XML>,"//s[translate(.,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')=.]")

_{품::123,456,xyz ★★★★★★★★★★★★★★★★★」123 노드스위치 노드)}

_{메모: 유감스럽지만XPATH 1.0에서는, ★★★★★★★★★★★를 서포트하고 않습니다.upper-case() 않다lower-case(),,, 피회니다다다회이치}

---

_{7) 숫자를 포함하지 않는 요소:}

=FILTERXML(<XML>,"//s[translate(.,'1234567890','')!=.]")

_{품::123,456,XY-1A,ZY-2F,XY-3F,XY-4f ★★★★★★★★★★★★★★★★★」123 ('임의의 자리수')}

=FILTERXML(<XML>,"//s[translate(.,'1234567890','')=.]")

_{품::ABC,DEF ★★★★★★★★★★★★★★★★★」xyz하지 않음) (숫자 없음)}

=FILTERXML(<XML>,"//s[translate(.,'1234567890','')!=. and .*0!=0]")

_{품::XY-1A,ZY-2F,XY-3F ★★★★★★★★★★★★★★★★★」XY-4f하지 않음 (숫자 자체는 유지하지 않음)}

---

_{8) 고유 요소 또는 중복:}

=FILTERXML(<XML>,"//s[preceding::*=.]")

_{품::123 노드스위치 노드)}

=FILTERXML(<XML>,"//s[not(preceding::*=.)]")

_{품::ABC,123,DEF,456,XY-1A,ZY-2F,XY-3F,XY-4f ★★★★★★★★★★★★★★★★★」xyz 노드스위치 노드)}

=FILTERXML(<XML>,"//s[not(following::*=. or preceding::*=.)]")

_{품::ABC,DEF,456,XY-1A,ZY-2F,XY-3F ★★★★★★★★★★★★★★★★★」XY-4f 형제가 )}

---

_{9) 일정 길이의 요소:}

=FILTERXML(<XML>,"//s[string-length()=5]")

_{품::XY-1A,ZY-2F,XY-3F ★★★★★★★★★★★★★★★★★」XY-4f (5글자)}

=FILTERXML(<XML>,"//s[string-length()<4]")

_{품::ABC,123,DEF,456,xyz ★★★★★★★★★★★★★★★★★」123 (4글자 이상)}

---

_{10) 전/후를 기준으로 하는 요소:}

=FILTERXML(<XML>,"//s[preceding::*[1]='456']")

_{품::XY-1A456')}

=FILTERXML(<XML>,"//s[starts-with(preceding::*[1],'XY')]")

_{품::ZY-2F,XY-4f , , , , 입니다.xyz는 'XY)로 합니다).}

=FILTERXML(<XML>,"//s[following::*[1]='123']")

_{품::ABC , , , , 입니다.xyz는 ' ('123')}

=FILTERXML(<XML>,"//s[contains(following::*[1],'1')]")

_{품::ABC,456 , , , , 입니다.xyz에는 '1')이 포함되어 있습니다).}

=FILTERXML(<XML>,"//s[preceding::*='ABC' and following::*='XY-3F']")

또는 다음 중 하나를 선택합니다.

=FILTERXML(<XML>,"//s[.='ABC']/following::s[following::s='XY-3F']")    

_{품::123,DEF,456,XY-1A ★★★★★★★★★★★★★★★★★」ZY-2F('ABC 사이의 것}

---

_{11) 서브스트링에 근거한 요소:}

=FILTERXML(<XML>,"//s[substring-after(., '-') = '3F']")

_{품::XY-3F 뒤에 ' ('3F'로 끝나는 노드)}

=FILTERXML(<XML>,"//s[contains(substring-after(., '-') , 'F')]")

_{품::ZY-2F ★★★★★★★★★★★★★★★★★」XY-3F 뒤에 'F'를 포함하는 노드)}

=FILTERXML(<XML>,"//s[substring-before(., '-') = 'ZY']")

_{품::ZY-2F 앞에 'ZY'로 시작하는 노드)}

=FILTERXML(<XML>,"//s[contains(substring-before(., '-'), 'Y')]")

_{품::XY-1A,ZY-2F,XY-3F ★★★★★★★★★★★★★★★★★」XY-4f 앞에 'Y'를 포함하는 노드)}

---

_{12) 연결 기준 요소:}

=FILTERXML(<XML>,"//s[concat(., '|', following::*[1])='ZY-2F|XY-3F']")

_{품::ZY-2F되어 있고 는 ''(')와 같습니다)|'는 'ZY-2F|XY-3F'입니다).}

=FILTERXML(<XML>,"//s[contains(concat(., preceding::*[2]), 'FA')]")

_{품::DEF 시 는 'FA')}

---

_{13) 비어있거나비어 있지 않음:}

=FILTERXML(<XML>,"//s[count(node())>0]")

또는 다음 중 하나를 선택합니다.

=FILTERXML(<XML>,"//s[node()]")

_{품::ABC,123,DEF,456,XY-1A,ZY-2F,XY-3F,XY-4f,xyz ★★★★★★★★★★★★★★★★★」123 모든 )('비어 있지 않은 노드')}

=FILTERXML(<XML>,"//s[count(node())=0]")

또는 다음 중 하나를 선택합니다.

=FILTERXML(<XML>,"//s[not(node())]")

_{반품:없음(빈 노드 모두)}

---

_{14) 전 또는 후:}

=FILTERXML(<XML>,"//s[substring(., string-length(.) - string-length('F') +1) = 'F'][last()]/following::*")

_{품::XY-4f,xyz ★★★★★★★★★★★★★★★★★」123('F'로 끝나는 마지막 노드의 오른쪽에 있는 모든 노드)}

=FILTERXML(<XML>,"//s[substring(., string-length(.) - string-length('F') +1) = 'F'][1]/preceding::*")

_{품::ABC ★★★★★★★★★★★★★★★★★」123(첫 번째 노드 왼쪽에 있는 대문자 'F'로 끝나는 모든 노드)}

---

_{15) (선행 또는 후행) 및 자기:}

=FILTERXML(<XML>,"(//s[.*0!=0][last()]|//s[.*0!=0][last()]/preceding::*)")

_{품::ABC,123,DEF,456,XY-1A,ZY-2F,XY-3F,XY-4f ★★★★★★★★★★★★★★★★★」xyz)(' 숫자노드 표시')}^††

=FILTERXML(<XML>,"(//s[.*0=0][1]|//s[.*0=0][1]/following::*)")

_{품::123,DEF,456,XY-1A,ZY-2F,XY-3F,XY-4f,xyz ★★★★★★★★★★★★★★★★★」123모든 비노드 삭제')}

---

_{16) 최대 또는 최소:}

=FILTERXML(<XML>,"(//s[.*0=0][not(.<//s[.*0=0])])[1]")

_{품::456 노드를 )('수치')}

=FILTERXML(<XML>,"(//s[.*0=0][not(.>//s[.*0=0])])[1]")

_{품::123 노드를 )('수치')}

_{메모: 이는 #3에 따라 모든 숫자 노드를 반환하고 Excel을 사용하여 어레이를 후처리하는 것과 같습니다.MIN() ★★★★★★★★★★★★★★★★★」MAX()★★★★★★★★★★★★★★★★★★.}

---

위는 분명히 다음 예와 같은 가능성을 보여 줍니다.XPATH 1.0위의 다양한 기능을 조합하여 사용할 수 있습니다.저는 가장 많이 사용되는 현악기 기능을 다루려고 노력했습니다.부족한 것이 있으면 언제든지 코멘트를 주세요.

는 상당히 하지만, .FILTERXML문의하실 수 있습니다.이 수식은 다른 방법으로 사용할 노드의 배열을 반환합니다.많은 시간 동안 그것을 사용하곤 했어요.TEXTJOIN() ★★★★★★★★★★★★★★★★★」INDEX()하지만 다른 옵션은 결과를 유출하는 새로운 DA 기능이 될 것입니다.

" " " 에서 할 때는 .FILTERXML()앰퍼샌드 문자(&)와 왼쪽 꺽쇠 괄호(<)는 리터럴 형식으로 표시할 수 없습니다.각각 다음 중 하나로 대체해야 합니다.& ★★★★★★★★★★★★★★★★★」<또 다른 옵션은 ISO/IEC 10646 문자 코드를 사용하는 것입니다.& ★★★★★★★★★★★★★★★★★」<각각 다음과 같다.해석 후, 함수는 이러한 문자를 리터럴 형식으로 반환합니다.세미콜론으로 줄을 쪼개는 것은 말할 필요도 없이 까다로워졌다.

---

^‡_{개폐 각 괄호 사이의 구조인 각 술어는 주어진 노드리스트의 필터입니다.이러한 구조를 여러 개 쓰는 것은 사실 그러한 술어를 쓰는 것이다.}

^‡‡_{형제자매와 자기 자신보다 앞서가는 것은 쉬운 일이 아닙니다.그래서 나는 유니온 연산자를 이용했다.단, 이 경우 여러 개의 표현이 파라테시스 안에 있어야 합니다.정규 표현식에 대해 생각해 보면 캡처 그룹 내의 대체 표현과 매우 유사합니다.}

편집 22-3-2022:

「 」에 .TEXTSPLIT()이제 문자열을 수평 또는 수직 범위(또는 둘 다)로 똑바로 분할할 수 있습니다.값 배열로 분할할 수도 있습니다.'xpath'라고 합니다.따라서 아래 답변은 관련성이 있으므로 그대로 유지하겠습니다.

---

행방불명SPLIT()

할 수 위해 더 하게 쓴 입니다.SPLIT() 사용하여 FILTERXML()VBA를 사용하다이지만 ★★★★★★★★★★★★★★★★★★★★★★★★★★★★」LAMBDA()이 기능을 통해 독자적인 커스텀 기능을 만들 수 있습니다.이치노

「 」의 C1 말하면=SPLIT(A1,B1:B3,"")구분된 텍스트 값을 모양 순서대로 흘립니다., <고객명>님SPLIT() 회사 이름이에요.LAMBDA()" managername manager"에서:

=LAMBDA(txt,del,xpath,FILTERXML("<t><s>"&REDUCE(txt,del,LAMBDA(a,b,SUBSTITUTE(a,b,"</s><s>")))&"</s></t>","//s"&xpath))

보시다시피 이 기능에는 4개의 파라미터가 있습니다.

• txt. - 소스 값 - 소스 값.
• del - 구분해 주세요. - 구분해 주세요.작성 또는 참조.
• xpath에 따라 를 적용하기 xpath 표현이다. ::"[.*0=0]"숫자 하위 문자열만 반환합니다.
• FILTERXML("<t><s>"&REDUCE(txt,del,LAMBDA(c,d,SUBSTITUTE(c,d,"</s><s>")))&"</s></t>","//s"&xpath)

네 번째 파라미터는 앞의 3가지 파라미터가 모두 호출되어 메인포스트에서 설명한 것과 같은 구문을 작성합니다.가 스스로 않기 인 「MS」, 「MS」, 「」를 작성했습니다.SPLIT()3번으로 나누다

는 <고객명>에 집중되어 .SUBSTITUTE()파이프 교환하지만 여러 개의 구분 기호가 있으면 어떻게 될까요? 여러 개의 네스트된 것이 SUBSTITUTE()하죠? 이 기능을 하면 좋지 요?SPLIT()? 가 기?LAMBDA()이 기능을 재귀적으로 호출할 수 있고 MS는 이 기능을 수행하는 람다 관련 함수까지 생각해냈기 때문에 개인적으로 매우 흥미롭습니다.REDURE().이 함수는 이제 모든 딜리미터가 중첩된 부분을 통과할 때까지 모든 딜리미터를 반복적으로 처리합니다.LAMBDA()!

, 이제 만의 것을 냈습니다.SPLIT()다음 세 가지 매개 변수를 사용하여 기능을 수행합니다.

=SPLIT(<StringToBeSplited>,<YourDelimiters>,<OptionalXpath>)

이제 이 기능을 전체 워크북의 함수로 사용할 수 있습니다.맛있게 드세요!

언급URL : https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml