エクセル関数応用<br />セルの参照範囲を可変にする（OFFSET,COUNTA,MATCH）
エクセル関数応用<br />セルの参照範囲を可変にする（OFFSET,COUNTA,MATCH）

エクセル関数応用セルの参照範囲を可変にする（OFFSET,COUNTA,MATCH）

数式の参照範囲を可変対応させます。つまり、入力データにより自動で拡張・縮小されるように関数を設定します。エクセルの表に、単純に集計等の計算式を入れてしまうと、集計範囲は固定になっているため、データの追加時に計算範囲が違ってきてしまう事になります。

COUNTA($A:$A) A列にあるデータの個数です。 1行目が見出しになっていますので、1引いています。 上の図では、11になります。 従って11-1=10が、OFFSETの高さになります。 B列でもよいです、ここでは日付を基準にしているだけです。 つまり、 B2から、高さ10、幅1のセル範囲になります。 つまり、 B2～B11になります。 結局は、 =SUM(B2:B11) となるわけです。

COUNTAでデータの個数を取得し、セル範囲を決定していますので、 データの追加・削除に自動で対応されるようになります。 開始日(E3)と終了日(E3)に名前定義とリストを設定 日付の範囲に名前定義を作成
  1. 「名前」に「日付」と入力。
  2. 2007以降の場合、範囲は「ブック」、2003にはありません。
  3. 「参照範囲」に「=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)」
  4. OK」
=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1) COUNTAでA列のデータの個数を取得し、 A2からデータ個数分のセル範囲を決定しています。 E3とE4にリストを設定
  1. E3、E4セルを選択
  2. [データ]-[入力規則]
  3. [設定]の[入力値の種類]で[リスト]を選択
  4. [元の値] に「日付」と入力。F3を押すと名前定義の一覧から選択できます。
  5. 「OK」
期間合計(E5)の数式 =SUM(OFFSET(B2,MATCH(E3,日付,0)-1,0,MATCH(E4,日付,0)-MATCH(E3,日付,0)+1,1)) 少々長い数式ですね。 この一番外側のOFFSETは、 B2のセルを、開始日までずらし、終了日までの高さのセル範囲を求めています。 作成手順を詳しく見てみましょう。 作る時は、以下のように順番に作成します。 =SUM(B4:B8) まずは、手作業で、SUM関数を入れてみます。 このB4:B8を可変にすれば良いわけです。B4:B8のセル範囲は適当で良いです、この範囲を可変にするのですから。 =SUM(B4:B8) ↓ =SUM(OFFSET(B2,2,0,5,1)) B4:B8をOFFSETに置き換えます。 引数は、とりあえず定数で入れてみます。 計算結果を見て、正しいことを確認しておきます。 =SUM(OFFSET(B2,2,0,5,1)) ↓ =SUM(OFFSET(B2,MATCH(E3,A2:A11,0)-1,0,5,1)) OFFSETの引数で開始位置である2をMATCHに置き換えます。 つねに、計算結果を見て、正しいことを確認します。

=SUM(OFFSET(B2,MATCH(E3,A2:A11,0)-1,0,5,1)) ↓ =SUM(OFFSET(B2,MATCH(E3,A2:A11,0)-1,0, MATCH(E4,A2:A11,0)-MATCH(E3,A2:A11,0)+1,1))

OFFSETの引数で開始位置である5をMATCHに置き換えます。 5=終了日の位置-終了日の位置+1

=SUM(OFFSET(B2,MATCH(E3,A2:A11,0)-1,0, MATCH(E4,A2:A11,0)-MATCH(E3,A2:A11,0)+1,1)) ↓=SUM(OFFSET(B2,MATCH(E3,日付,0)-1,0, MATCH(E4,日付,0)-MATCH(E3,日付,0)+1,1))

A2:A11を、日付の可変範囲指定に変更します。 日付の可変範囲の数式は、 OFFSET(A2,0,0,COUNTA(A:A)-1,1) ですが、これは、 日付の範囲に名前定義を作成 ここで、「日付」と名前定義してあります。 つまり、A2:A11を日付に置き換えてやれば良いという事になります。

これで期間合計(E5)の数式が完成です。

上記説明では、 数式を見やすくするために、絶対参照である$を省略しています。 絶対参照は、最初から入れられれば良いですが、 $をいれると、どうしても数式が見づらくなるので後回しにしても良いでしょう。 数式が完成した後に、まとめて$を入れても良いでしょう。

短く易しい数式で

途中の数式では、長く複雑に感じる場合も出てきますが、 関数のネスト（入れ子）が多いだけで、一つ一つの関数は難しいものではありません。 エクセルを使う一人一人にとって必要な関数は、そんなに多くないはずです。 必要なことは、その関数をいかに組み合わせるかにかかっています。 整理して、順序よく、考えれば、難しいく見える数式も作成できるようになります。

ただし、難しく長い数式を作ったからと言って、決してそれがすごい事ではありません。 本来は、難しい数式を作らなくても良いようにシートを工夫するべきことです。 同じことを行っているのなら、なるべく短く易しい数式の方が良いはずです。 その為には、 作業列を使って数式を細かい単位で完成させる等の工夫をしてください。

数式.作業セル、計算セル 同じテーマ「エクセル関数応用」の記事 新着記事 NEW ・・・新着記事一覧を見る アクセスランキング ・・・ ランキング一覧を見る このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。 掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。 本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。 This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.