エクセル リスト05

エクセルの理解シリーズ

５．データの入力規則のリストで「元の値」に使用する関数を理解する

５−６．“ワンセット関数”のSUM関数との複合（ネスト）

 ＯＦＦＳＥＴ関数とＣＯＵＮＴＡ関数をネストした
ＯＦＦＳＥＴ（セル番地，０，０，ＣＯＵＮＴＡ（セル範囲）−１，１）
を“ワンセット範囲参照関数”と命名します。

たとえば、一番簡単なセル参照として
「＝Ａ１」を
”ワンセット関数”で表すと
「＝ＯＦＦＳＥＴ（＄Ａ＄１，０，０，ＣＯＵＮＴＡ（＄Ａ＄１：＄Ａ＄２）−１，１）」
すなわち、「＝ＯＦＦＳＥＴ（＄Ａ＄１，０，０，１，１）」です。

次に、
「＝Ａ１：Ａ２」を
”ワンセット関数”で表すと
「＝ＯＦＦＳＥＴ（＄Ａ＄１，０，０，ＣＯＵＮＴＡ（＄Ａ＄１：＄Ａ＄３）−１，１）」
すなわち、「＝ＯＦＦＳＥＴ（＄Ａ＄１，０，０，２，１）」です。

さらに、
「＝Ａ１：Ａ３」を
”ワンセット関数”で表すと
「＝ＯＦＦＳＥＴ（＄Ａ＄１，０，０，ＣＯＵＮＴＡ（＄Ａ＄１：＄Ａ＄４）−１，１）」
すなわち、「＝ＯＦＦＳＥＴ（＄Ａ＄１，０，０，３，１）」です。

並べてみると、

「＝ＯＦＦＳＥＴ（＄Ａ＄１，０，０，１，１）」
「＝ＯＦＦＳＥＴ（＄Ａ＄１，０，０，２，１）」
「＝ＯＦＦＳＥＴ（＄Ａ＄１，０，０，３，１）」

変わっているのは後から２番目のCOUNTA(セル範囲)-1の数値だけです。
それは「高さ（セル範囲の行の数）」です。

そこで、これを応用してみましょう。

サム関数（参照範囲）を
↓
サム関数（ワンセット関数）に
してみましょう。

ワンセット関数の「高さ（セル範囲の行の数）」を
１つの「セル番地」に置き換えて、
そのセルに入力された値を参照して範囲を決めます。
 

1.		左図のような表を作ります。
	ｓum(offset(c2,0,0,a13,1　と入力し、
2.
3.		式の最後に 「括弧閉じる“）”」を １つだけ 入力し ↓ エンターキーを押しますと、
4.		エラーメッセージが出ても ↓ はい
	※上のような、エラー メッセージが出ます。 サンプルの数式を良く見てみますと、 最後の「括弧閉じる“）”」は“））”２つになっています。 このような、括弧やカンマの入力ミスは そのまま[はい（Ｙ）] をクリックしますと、 後で自動的に「修正を摘要」してくれます。
5.		Ｃ１３セルには エラーマーク ＃ＲＥＦ！ が表示されています。
6.		Ａ１３セルに 「３」と入力し、
7.		エンターキーで確定しますと、ちゃんと上位３位までの合計が表示されます。
8.		Ａ１３セルに 「５」と入力しますと、上位５位までの合計が表示されます。

 

※	ＳＵＭ関数の引数（セル範囲）として、「高さ」をセル参照にしたＯＦＦＳＥＴ関数を利用すると、部分合計を指定することができると理解して下さい。 《ここがポイント》“ワンセット関数”は関数なのですが、 “セルまたはセル範囲”を表すものだと理解して下さい。
トップページに戻る		前へ（５−５．）	次へ（５−７．）