시작 문자열을 기준으로 열을 필터링하고, 필터링된 셀에서 숫자를 읽고, 산술 연산을 수행하여 Excel에서 두 행의 요약을 생성합니다.

Question 1

어쩌면 이 짧은 공식 제안을 시도해 보세요.

1] 에서 F2수식은 가로 및 세로로 복사되었습니다.

=SUMPRODUCT(4*TEXT(SUBSTITUTE($B2:$C3,F$1,""),"0%;;;\0"))

2] 이전 질문도 이 동일한 공식으로 해결할 수 있습니다. A19:C23에 넣었습니다.

에서는 B19수식이 가로 및 세로로 복사되었습니다.

=SUMPRODUCT(0+TEXT(SUBSTITUTE(B$2:B$15,$A19,""),"0%;;;\0"))

그리고,

편집하다

0을 제거하려면

1] 셀 형식 사용 :

>> "회계" >> "기호"의 모든 수식 셀 형식에서 "없음" >> "소수점" 선택: "1"을 클릭합니다.

2] IFERROR(1(/1......),"")를 사용하여 F2 수식에 추가하고 >>가 됩니다.

=IFERROR(1/(1/SUMPRODUCT(4*TEXT(SUBSTITUTE($B2:$C3,F$1,""),"0%;;;\0"))),"")

Answer

어쩌면 이 짧은 공식 제안을 시도해 보세요.

1] 에서 F2수식은 가로 및 세로로 복사되었습니다.

=SUMPRODUCT(4*TEXT(SUBSTITUTE($B2:$C3,F$1,""),"0%;;;\0"))

2] 이전 질문도 이 동일한 공식으로 해결할 수 있습니다. A19:C23에 넣었습니다.

에서는 B19수식이 가로 및 세로로 복사되었습니다.

=SUMPRODUCT(0+TEXT(SUBSTITUTE(B$2:B$15,$A19,""),"0%;;;\0"))

그리고,

편집하다

0을 제거하려면

1] 셀 형식 사용 :

>> "회계" >> "기호"의 모든 수식 셀 형식에서 "없음" >> "소수점" 선택: "1"을 클릭합니다.

2] IFERROR(1(/1......),"")를 사용하여 F2 수식에 추가하고 >>가 됩니다.

=IFERROR(1/(1/SUMPRODUCT(4*TEXT(SUBSTITUTE($B2:$C3,F$1,""),"0%;;;\0"))),"")

Question 2

처리할 범위가 다차원이기 때문에 솔루션은 더 복잡합니다. (이전 질문에서는 단일 차원이었습니다.)

TEXTJOIN처리하는 한 가지 방법은 및 를 사용하여 수행할 수 있는 1차원 배열로 변환하는 것입니다 FILTERXML. TEXTJOIN함수에 의해 생성된 문자열은 글자 수를 초과할 수 없다는 제한이 있습니다 32,767. 이것이 문제가 된다면 VBA 또는 파워 쿼리 솔루션이 더 나을 수도 있습니다.

또한 FILTERXMLMac 버전이나 Excel Online에서는 사용할 수 없습니다.

해당 제한 사항이 적용되지 않는 경우

F2: =IFERROR(4*SUM(FILTERXML("<t><s>" & SUBSTITUTE(FILTERXML("<t><s>" &TEXTJOIN("</s><s>",TRUE,$B2:$C3) & "</s></t>","//s[starts-with(.,'" & F$1 & "')]")," ","</s><s>") & "</s></t>","//s[last()]")),"")

오른쪽 아래로 채우기
먼저 XML(을 사용하여 TEXTJOIN)을 만들어 각 셀에 대해 별도의 노드를 만듭니다.
- 우리의 인수는 행 1의 프로젝트 이름이 xPath있는 노드만 반환합니다 .start-with
- 그런 다음 마지막 노드를 반환하는 를 사용하여 공백으로 구분된 노드를 기반으로 SUBSTITUTE다른 노드를 만드는 데 사용합니다 .XMLxPath
- 수학적 연산을 수행합니다.

편집하다:

만약 당신이아니다함수 가 있으면 FILTERXML이 공식을 사용해 보세요(O365 Mac 또는 Windows 및 Excel 온라인)이는 다른 방법을 사용하여 처리를 위해 2D 배열을 1D 배열로 변환합니다.

=IFERROR(SUM(4*TRIM(RIGHT(
   SUBSTITUTE(LET(
        seq,IF(SEQUENCE(99)=1,1,(SEQUENCE(99)-1)*99),
        x,TRIM(MID(TEXTJOIN(REPT(" ",99),TRUE,$B2:$C3),seq,99)),
        y, FILTER(x,LEFT(x,LEN(F$1))=F$1),y),
   " ",REPT(" ",99)),99))),"")

이 수식에는 여전히 32,767자 제한이 있습니다. 해당 문제가 발생하는지 여부는 주로 프로젝트 이름에 공백이 몇 개 있는지에 따라 달라집니다. 계산 공식은 대략 다음과 같습니다.

a = 셀의 평균 문자 수
b = 셀의 공백 수
c = 셀 수
```
 (a+b*99) * c
```

15예를 들어 문자, 5공백, 셀이 있다면 대략 문자 24정도가 될 것입니다 12,000.

Answer