의미론적 '머리글', '하위 머리글' 및 '소계' 행이 있는 위의 샘플 시트가 주어지면 현재 셀을 기준으로 이전 하위 머리글 행을 찾는 수식을 결정하려고 합니다. 예를 들어, 수식을 에 입력하면 F5행을 찾고 2, 에 입력하면 F17행을 찾습니다 13.
행은 조건에 따라 값의 존재 여부에 따라 머리글, 하위 머리글 또는 부분합으로 형식이 지정되거나 H열에 S지정 T됩니다 . $A:$A즉, 하위 머리글 행 n은 행입니다 $An = "S". 이제 이 개념을 내 공식으로 확장하고 싶습니다.
헤더 행 다음에는 항상 하위 헤더가 옵니다. 따라서 헤더와 하위 헤더의 순서가 뒤바뀌는 것에 대해 걱정할 필요가 없습니다.
나는 다음을 시도했습니다 :
=MATCH("S", OFFSET($A5, 0, 0, -ROW($A5), 1), 0)이는 항상 row 를 반환합니다
2.MATCH첫 번째세트에서 일치하며OFFSET높이를 제한할 수 없습니다(즉, 이전 하위 헤더 위치를 알 수 없기 때문에 반복적으로).{=LARGE(MATCH("S", OFFSET($A5, 0, 0, -ROW($A5), 1), 0), 1)}이는 또한 를 반환합니다 . 왜냐하면 배열 컨텍스트(예: ++ 사용 )
2에서도 여전히 첫 번째 결과만 반환하기 때문입니다.CtrlAltEnterMATCH=LARGE(IF(OFFSET($A5, 0, 0, -ROW($A5), 1)="S", ROW(OFFSET($A5, 0, 0, -ROW($A5), 1))), 1)여기서는 배열을 기대하지 않으므로 를 반환하므로
0일치 하지 않는 단일 값으로 확장되고 숫자로 처리됩니다 .IFOFFSET($A5, 0, 0, -ROW($A5), 1)0"S"LARGEFALSE{=LARGE(IF(OFFSET($A5, 0, 0, -ROW($A5), 1)="S", ROW(OFFSET($A5, 0, 0, -ROW($A5), 1))), 1)}#VALUE배열 확장이 너무 일찍 발생하여 유효한 숫자 매개 변수 가 아닌-ROW($A5)array 로 남기 때문에 이 반환됩니다 . 비트가 아닌 배열이기를 원했지만 Excel에서는 구별할 수 없습니다.-{5}heightOFFSETIF(OFFSET(...)="S",...)-ROW($A5)
현재 Excel 2010을 대상으로 하고 있습니다. 이전 버전은 적용할 수 없습니다(향후 호환성은 보너스임). *.xlsx보다 *.xlsm 파일을 배포하는 것이 더 어렵기 때문에 VBA를 피하려고 합니다(게다가 VBA를 사용하여 배포하는 방법을 이미 알고 있습니다).
제가 시도할 수 있는 다른 것이 있나요?
답변1
이를 수행하는 쉬운 방법은 절대/상대 혼합 공식을 속이고 사용하는 것입니다. 이는 셀에 입력된 배열 수식(CTRL+SHIFT+ENTER로 입력)이지만 B4행 4의 어느 위치에나 들어갈 수 있습니다. 표시된 행 번호를 반환합니다 S.
=MAX(IF($A$1:A4="S",ROW($A$1:A4)))
복사하면 참조의 두 번째 부분이 B4 and A4늘어납니다. 이렇게 하면 가장 큰 일치 항목이 있는 행을 얻을 수 있습니다.~ 위에현재 행. F4해당 범위를 입력/선택한 후 을 사용하여 해당 수식을 더 빠르게 입력할 수 있습니다 . 그러면 모든 선택 사항에 대해 달러 기호가 순환됩니다.
범위 사진
수식을 바꾸는 데 사용됩니다.
질문을 조금 읽은 후(그리고 @SteveTaylor의 편집 내용에 따라) 수식을 업데이트하는 데 사용되는 것 같습니다. 위에서 반환된 행을 사용하여 INDEX합계할 데이터 범위를 얻을 수 있습니다. 대체할 수 있는 수식 2개가 표시됩니다.
- 레이블이 지정된 각 데이터 행에 대한 총 계산입니다. 이 경우 위의 소계 행을 동적으로 참조할 수 있습니다.
- 소계 행에 대한 총계 계산입니다. 이 경우 위에서 합산되는 값은 동적으로 참조될 수 있습니다.
단일 행 데이터의 경우 F3배열 수식으로 시작하여 수식을 사용할 수 있습니다. SUMPRODUCT2개 이상의 열로 이동하는 것이 훨씬 쉬워지는 사용으로 전환했습니다 .
=C3*SUMPRODUCT(INDEX(D:E,MAX(IF($A$1:A3="S",ROW($A$1:A3))),),D3:E3)
총 행 수식의 경우 에서 시작하여 F11다시 배열 수식을 사용할 수 있습니다.
=SUM(F10:INDEX($F$1:F10, 1+MAX(IF($A$1:A11="S",ROW($A$1:A11)))))
하나의 공식으로 모든 것을 지배하고 싶다면! 그런 다음 이를 IF열의 값을 기반으로 중첩하여 결합할 수 있습니다 A. 여기에 F2복사할 수 있는 배열 수식이 있습니다 .
=IF(
A2="S",
SUM(D2:E2),
IF(A2="T",
SUM(F1:INDEX($F$1:F1, 1+MAX(IF($A$1:A2="S",ROW($A$1:A2))))),
C2*SUMPRODUCT(INDEX(D:E,MAX(IF($A$1:A2="S",ROW($A$1:A2))),),D2:E2)))
이 수식은 빈 행과 "데이터" 행을 구분하지 않습니다. 현재는 스페이서 행에 대해 0을 반환하는데 이는 괜찮습니다.
결과 및 공식 그림두 개의 데이터 블록에 대해.
