前回はOFFSET関数の概要についてまとめました。
じゃあ実際どんな使い方ができるの？ってことで、今回の記事では2つのサンプルでOFFSET関数の使用例を紹介したいと思います。
Contents
会員別に単価があるときの合計金額の計算
例えばこんな表があったとします。
で、商品ごとの数量が決まっているときに、会員種別ごとに合計金額を出すにはどうするか、という例です。
合計金額を出すための考え方と準備
まず、どんな関数を使って解決できそうか考えます。
計算方法は、商品ごとの「数量×単価」の合計なので、前回使ったSUMPRODUCT関数が使えそうです。
そして、会員種別に応じて適用する単価の列を変えたら合計金額が出せそうだ、とこうなるわけです。
というわけで、会員種別を指定するセルを用意します。
例えばこんな感じに。
ここで会員ランクを0・1・2としているのには意味があります。
会員列のAを起点の列とすると、Aは0列目、Bは1列目、Cは2列目と対応しています。
では、どのような関数になるのか、見てみましょう。
合計金額を計算する関数
合計金額のセルにはこのような関数が入っています。
日本語訳するとこうでしょうか。
- 数量列×単価列（※OFFSET関数で指定）
- 単価列（OFFSET関数）
・C3セルを起点
・タテ移動無し（０）
・ヨコ移動はランク対応列（B10）
・移動したセルで縦5×横1の範囲を指定
これでランク別の合計金額を出す関数が完成しました。
計算結果の確認
では、これでランク別の計算結果を見てみましょう。
こんな感じで変わります。
ちなみに右端はランク外の数字を入れたケースですが、この場合「数量×０」となるので合計金額も０になります。
まずは「会員ランク別の単価表があるときの合計金額の出し方」でした。
地域を選べば市町村が勝手に変わるリストの作り方
たまにこんなリストありますよね。
地域を選ぶと、市町村のリストが該当する市町村に勝手に変わるというやつです。
ここではこのリストの作り方を見ていきます。
事前準備
まず、リストのもとになる表を用意します。
ここでは、地域別に行番号と市町村数をセットしておきます。これをOFFSET関数で指定するときに使うためです。
そして、次に地域名のリストを作ります。
こんな感じで地域名のリストができれば、地域名を検索値にして行番号・市町村数を返すセルを作ります。ここでは列で検索できるHLOOKUP関数を使います。
ここまでできれば準備完了です。
市町村リストの作成
リストを作る手順はさっきと同じですが、元の値（参照範囲）が全然違います。
そう、リスト範囲にOFFSET関数を使うことができるのです。
では、この中身について見ていきましょう。
このように、OFFSET関数を使うことでリストの範囲も自由自在に変えることができます。
まとめ
OFFSET関数について、今回は他の関数と組み合わせで、またリストにするときの使い方をまとめてみました。
この関数は単独では使い道があまりないのですが、このように他の関数や機能と組み合わせた時に真価を発揮する関数ではないでしょうか。
また、何か使い勝手のいい組み合わせが分かれば紹介していきたいと思います。
最後までお読みいただき、ありがとうございました。
