2014年06月22日

OFFSET関数で関数の引数の範囲を設定してみた/Excel/検索関数

Excelの関数で関数の引数の範囲を設定してみます。
OFFSET関数を使用します。
20140618_00_OFFSET関数.jpg

例えば、こんなシートがあって黄色の範囲(“1”が入力されている範囲)をOFFSET関数で設定して、合計をSUM関数で求めてみます。
20140618_001.JPG

まず、普通にSUM関数で合計を求めてみます。
SUM関数では、合計を求める対象の範囲を引数としてカッコの中に設定します。
20140618_002.JPG

と、合計は一目瞭然 “6”になりました。
20140618_003.JPG

次に、範囲をOFFSET関数で設定します。
予め、SUM関数の引数をセルに入力しておきます。
行数:基準セルから設定範囲までの行数。基準セルはここでは“1行目”で、範囲は“3行目”からなので、“4行-1行”で“3”になります。
列数:これも“行数”と同じ考え方で“C列-A列”で“2”
高さ:範囲の行数なので“3”
幅:範囲の列数なので“2”
20140618_004.JPG

まず、“=SUM()”と入力します。
20140618_005.JPG

カッコの中にカーソルを置いて、「数式」「検索/行列」から「OFFSET」を選択します。(SUM関数の中にOFFSET関数をネスト(入れ子)させます)
20140618_006.JPG

「関数の引数」画面が開きます。
参照:基準のセルを設定します。ここでは“A1”です。
行数:さっき予め入力したセルを指定します。
列数:どうじょー(同上)
高さ:〃(同じ)
幅:ちょんちょん(〃)
20140618_007.JPG

と、“6”になりました。
20140618_008.JPG

んで、次に応用例です。あまり良い例ではないかもですが、、
こんな50件のデータがあって、1から、指定した数だけの合計を求めてみます。
20140618_009.JPG

参照:基準セル位置を1のデータが入力されているセル“C4”を指定します。
行数:基準セルから開始する範囲なので入力は省略します。
列数:基準セルの列の範囲なので入力は省略します。
高さ:合計する範囲の行数“10”を入力してあるセル“B54”を指定します。
幅:C列だけの範囲なので入力は省略します。
20140618_010.JPG

セルの入力内容はこうなります。
20140618_011.JPG

このOFFSET関数をSUMの中に入れます。
20140618_012.JPG

と、1から10までの合計が求められました。
20140618_013.JPG

例えば、11までの合計にしたければ、“行数”にしたセルを“11”にするだけで、1から11までの合計に変わります。
20140618_014.JPG

んで、応用例その2
“B54”のセルに“COUNT関数”でデータの件数を求めておけば、
20140618_015.JPG

“50”件の合計が求められました。
20140618_016.JPG

と言うわけで、SUM関数にネストさせていろいろ説明しましたが、範囲を引数にする全ての関数に応用ができまので、きっと画期的な応用ができるハズっ!です。。

それから、ここでは説明のために「数式」の「関数ライブラリ」から関数を選択しましたが、あらかじめ関数が分かっている場合は式を入力するセルに直接“OFFSET()”と入力します。
“O”と入力すると推定機能が働いて“O”で始まる関数が表示されるのでそこから選択することができます。


関連ページ
関数一覧(カテゴリ別)
関数一覧(名前別)


posted by haku1569 at 00:44| Excel関数 | このブログの読者になる | 更新情報をチェックする
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。