단일 셀에 범위 그룹의 최대 합계 쓰기

2024-7-13 • tag-icon

microsoft-excel worksheet-function

단일 셀에 범위 그룹의 최대 합계 쓰기

수년 동안의 월별 개수를 나타내는 셀 범위와 주요 요약 열에 대한 다음과 같은 인위적인 예를 고려하십시오.

.|A    |B      |C    |D    |E    |F    |G    |H    |I    |J    |K    |...
-+-----+-------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----
1|     |Most/3M|Jan19|Feb19|Mar19|Apr19|May19|Jun19|Jul19|Aug19|Sep19|...
2|Foo  |     14|    1|    3|    7|    3|    4|    2|    1|    3|    1|...
3|Bar  |     13|    3|    4|    1|    2|    2|    2|    9|    1|    1|...
4|Baz  |     18|    2|    3|    8|    7|    3|    2|    3|    7|    1|...

의 해당 요약 열은 B:B3개월 기간 중 가장 높은 수를 나타냅니다.순진하게사용:

=MAX(SUM($C2:$E2),SUM($D2:$F2),SUM($E2:$G2),
     SUM($F2:$H2),SUM($G2:$I2),SUM($H2:$J2),
     SUM($I2:$K2))

...그리고 나서 채워지지만 유지해야 하는 것은 매우 짜증나는 일입니다. 값이 가장 오른쪽 부분(예: , )에 추가됨에 따라 L:L수식 은 및 등 M:M으로 업데이트되어야 합니다.SUM($J2:$L2)SUM($K2:$M2)

한 가지 대안은 다음과 같습니다.

={LARGE(($C2:$I2 + $D2:$J2 + $E2:$K2), 1)}

...그렇지 않아상당히너무 안타깝습니다. , to 및 to :$I2로 변경하는 것은 3개월 동안 합리적이며 다음 과 같이 끔찍한 사용으로 파생될 수 있습니다.:$J2:$J2:$K2:$K2:$L2INDIRECT

={LARGE(INDIRECT("R[0]C3:R[0]C"&COUNTA(OFFSET($C2,0,0,1,2000)),FALSE)+
        INDIRECT("R[0]C4:R[0]C"&COUNTA(OFFSET($C2,0,0,1,2000))+1,FALSE)+
        INDIRECT("R[0]C5:R[0]C"&COUNTA(OFFSET($C2,0,0,1,2000))+2,FALSE), 1)}

그러나 이는 창 크기가 증가함에 따라(즉, 더 많은 +절을 추가해야 함) 및/또는 창 크기를 구성할 수 있도록 의도된 경우(예: 사용자 정의 숫자를 사용하여 B1실제로는 value 에 불과함) 고통스럽습니다. 3형식 "Most/"0"M")

나는 아마도 다음 중 하나를 생각했습니다.

={MAX(SUM(OFFSET($C2:$I2,0,0,1,3)))}
={LARGE(SUM(OFFSET($C2:$I2,0,0,1,3)), 1)}

...여기서는 에 주어진 범위와 그룹 크기만 업데이트하면 됩니다 . 예를 들어 다음 과 같은 OFFSET또 다른 끔찍한 을 사용하여 이를 동적으로 파생시키려는 의도로 :INDIRECT

=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&
                           (COUNTA(OFFSET($C2,0,0,1,2000))+COLUMN($C:$C)-B$1),
                           FALSE),
                  0,0,1,B$1)),
       1)

사용할 때수식 평가위의 괴물을 통과하기 위해정상양식에 따르면 실제로는 다음과 같이 해결됩니다.

=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&(9+3-B$1),FALSE),0,0,1,3)),1)
=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&(12-3),FALSE),0,0,1,3)),1)
=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C9",FALSE),0,0,1,3)),1)
=LARGE(SUM(OFFSET($C$2:$I$2,0,0,1,3)),1)
=LARGE(SUM($C$2:$E$2),1)
=LARGE(11,1)
=11

...그래서 주어진 범위는 OFFSET배열로 확장되지 않고 대신 해당 범위의 왼쪽 상단 셀이 직접 사용되는 것으로 보입니다 OFFSET.

~ 안에정렬형식(예 CTRLENTER: ) 대신 결국 다음과 같이 해결됩니다.

=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&(9+{3}-B$1),FALSE),0,0,1,B$1)),1)
=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&({12}-3),FALSE),0,0,1,B$1)),1)
=LARGE(SUM(OFFSET(INDIRECT({"R[0]C3:R[0]C9"},FALSE),0,0,1,B$1)),1)
=LARGE(SUM(OFFSET({#VALUE!},0,0,1,B$1)),1)
=LARGE(SUM(OFFSET({#VALUE!},0,0,1,3)),1)
=LARGE(SUM({#VALUE!}),1)
=LARGE(1,1)
=1

...그래서 배열 확장이 너무 일찍(즉, 의 결과로 COLUMNS($C:$C)) 발생하여 나중에 INDIRECT호출이 중단되는 것 같습니다.

( LARGE와 결과는 모두 동일합니다 . 설명서에 배열이 필요하고 단일 값의 무제한 매개변수 목록이 필요하다고 나와 있기 때문에 MAX사용했습니다 .)LARGEMAX

VBA를 사용하지 않거나 시트 레이아웃을 변경하지 않는 환경 설정이 제공됩니다.

어떤 트릭이라도힘SUM(OFFSET)에서 사용할 수 있는 배열을 방출 하도록 올바른 위치에 배열 확장이 있습니까 LARGE?
데이터가 시트 오른쪽에 추가될 때 공식을 최소한으로 수정해야 하는 대체 접근 방식이 있습니까?

(PS. 저는 O365/Insider가 아닌 Excel 2019를 사용하고 있기 때문에 아직 동적 배열/물건이 없습니다. SEQUENCE이는 관련성이 있을 것 같습니다. 이에 의존하는 답변은 후손에게 유용할 수 있지만 그렇지 않을 것입니다. 당장 필요한 것은 해결하지 못해요...)

답변1

Helper Data와 SUM Large 3 Quarter 값으로 구성된 연중 가장 높은 3분기 SUM을 구하는 다음 방법을 제안하고 싶습니다.

작동 방식:

관련 월의 날짜를 입력하고 Row 1셀 형식을 적용합니다 mmm-yy.
Rnage에 Quarter의 이름을 삽입합니다 T9:T12.

Cell 의 수식을 U9작성하세요.

=IF(ROW(A1)>=5,"",(SUMPRODUCT((ROUNDUP(MONTH(T$1:AE$1)/3,0)=ROW(A1))*(T$2:AE$2))))

주의

ROUNDUP(MONTH(T$1:AE$1)/3,0)=Row(A1)반환 1하고 는 월이 속한 때입니다 0s.11분기, & 0와 같은 다른 분기에 대해서는 그렇지 않은 경우 등이 있습니다 .234
SUMPRODUCT, 눈꺼풀을 치지 않고 이러한 모든 범위의 데이터를 처리합니다.
=ROW(A1))1을 반환 Q1하고 down 을 나타냅니다 2, 3 & 4.
=IF(ROW(A1)>=5,""Blank수식이 4 이후의 분기 값을 가져올 수 없는 경우를 반환합니다 .

각 분기에 대한 데이터를 얻은 후 이제 셀에 수식을 입력하여 U14SUM 3의 가장 큰 값을 입력합니다.

{=SUM(LARGE(U$9:U$12,{1,2,3}))}

주의

배열(CSE) 형식의 수식을 더 잘 사용하므로 다음으로 마무리해야 합니다.Ctrl+Shift+Enter, 그렇지 않으면 배열이 아닌 수식으로도 작동합니다.

필요에 따라 수식의 셀 참조를 조정합니다.

관련 정보