範囲のグループの最大合計を1つのセルに書き込む

2024-7-13 • tag-icon

microsoft-excel worksheet-function

範囲のグループの最大合計を1つのセルに書き込む

次のような、数年にわたる月ごとのカウントを表すセルの範囲と先頭の概要列の、不自然な例を考えてみましょう。

.|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)}

...そうではないとても:$I2非常に悪いのは、をに:$J2、:$J2をに:$K2、:$K2をに変更することが3 か月の期間に妥当であり、それ自体がの恐ろしい使用法で次のように:$L2導き出される可能性があるからです。INDIRECT

={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単なる値)に基づいて構成可能である場合、これは困難になります。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
シートの右側にデータが追加されるにつれて、数式をゼロから最小限に修正する必要がある、私の方法に代わるアプローチはありますか?

(追記: 私は O365/Insider ではなく Excel 2019 を使用しているため、まだ動的配列などがありません。SEQUENCEこれは関係してくるかもしれません。それに依存する回答は後世には役立つかもしれませんが、私の当面のニーズを解決するものではありません...)

答え1

ヘルパーデータと SUM Large 3 四半期の値で構成され、年間で最も高い 3 四半期の合計を取得する次の方法を提案します。

使い方：

関連する月の日付を入力してRow 1セルの書式を適用しますmmm-yy。
Rnage に四半期の名前を入力しますT9:T12。

セルに数式を入力し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、月が属する場合0s1Q1およびが0そうでない場合など、他の四半期（2、3&など）についても同様に当てはまります4。
SUMPRODUCTは、瞬きもせずにこれらすべての範囲のデータを処理します。
=ROW(A1))1 を返し、Q1以下同様に下を表します2, 3 & 4。
=IF(ROW(A1)>=5,""Blank数式が 4 以降の四半期の値を取得できない場合に返されます。

各四半期のデータを取得したら、セルに数式を入力してU14、3 つの最大値を合計します。

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

注意

配列（CSE）形式で数式を使用する方が良いので、次のように終了する必要があります。Ctrl+Shift+Enterそれ以外の場合は、非配列数式としても機能します。

必要に応じて数式内のセル参照を調整します。

関連情報