エクセルの真髄 というサイトから引用
MATCH関数
INDEX関数
OFFSET関数
この３つの関数です
そこで、VLOOKUP関数も含めて、これらの関数を徹底解説します。
まず、以下の表で説明します。
D2の検索値で、A列を検索し、一致した行のB列を取り出します。
この場合は、VLOOKUPを使用する事が通常です。
VLOOKUPが使用出来ないような場合に、他の関数を使って同様の機能を実現する必要が出てきます。
その時の基本として、VLOOKUPを他の関数で実現する、さまざまな方法を紹介します。
まずは、基本のVLOOKUP関数の説明をした後で、
INDEXとMATCHの組み合わせ
OFFSETとMATCHの組み合わせ
順に説明します。
VLOOKUP関数
=VLOOKUP($D$2,$A$2:$B$7,2,FALSE)
VLOOKUP関数は、
指定された範囲の 1 列目で特定の値を検索し、その範囲内の別の列の同じ行にある値を返します。
VLOOKUP(検索値,範囲,列番号,検索の型)
検索値
範囲の左端の列で検索する値を指定します。検索値には、値またはセル参照を指定します。
範囲
セル範囲を指定します。範囲の左端の列の値が、検索値で検索される値です。
列番号
範囲内で目的のデータが入力されている列を、左端からの列数で指定します。
検索の型
TRUEまたは省略すると、検索値未満の最大値が使用されます。昇順に並べ替えておく必要があります。
FALSEを指定すると、検索値と完全に一致する値だけが検索されます。検索値と一致する値が見つからない場合は、#N/Aエラー値が返されます。
上の数式は、
検索値”B02″（D2の値）で、
範囲（A2～B7）の左端の列（A列）を検索し、
一致した行の、
2列目のデータを取得しています。
つまり、
“B02″（D2）で、A列を探し、一致した5行目（A5）の、2列目（B5）を取得しています。
INDEX関数とMATCH関数の組み合わせ
=INDEX($A$2:$B$7,MATCH($D$2,$A$2:$A$7,0),2)
MATCH関数は、
指定された照合の型に従って検査範囲内を検索し、検査値と一致する要素の、配列内での相対的な位置を表す数値を返します。
MATCH(検査値,検査範囲,照合の型)
検査値
表の中で必要な項目を検索するために使用する値を指定します。
検査範囲
検査する隣接したセル範囲を指定します。
検索の型
-1、0、1 の数値のいずれかを指定します。
1を指定すると、検査値以下の最大の値が検索されます。
0を指定すると、検査値に一致する値のみが検索の対象となります。
-1を指定すると、検査値以上の最小の値が検索されます。
省略すると1を指定したものと見なされます。
上の数式は、
検索値”B02″（D2の値）で、
範囲（A2～A7）を検査し、
一致した行の、A2からの位置（A2が1、A3が2）である4が返されます。
つまり、
“B02″（D2）で、A列を探し、一致した5行目（A5）の、A2からの位置である4を返します。
この4を使用して、以下のINDEX関数で値を取得します。
INDEX関数は、
行番号と列番号で指定されるテーブルまたは配列の要素の値を返します。
INDEX(配列,行番号,列番号)
配列
セル範囲または配列定数を指定します。
配列が1行または1列のみの場合、それぞれ行番号または列番号を省略することができます。
行番号
配列の中にあり、値を返す行を数値で指定します。
行番号を省略した場合は、必ず列番号を指定する必要があります。
列番号
配列の中にあり、値を返す列を数値で指定します。
列番号を省略した場合は、必ず行番号を指定する必要があります。
上の数式は、
範囲（A2～A7）の、4（MATCHの結果）番目の行、2番目の列、セルB5の値が取得されます。
つまり、
A2～A7の範囲で、上から4番目、左から2番目のセル（B5）を取得します。
OFFSET関数とMATCH関数の組み合わせ
=OFFSET($A$2,MATCH($D$2,$A$2:$A$7,0)-1,1,1,1)
MATCH関数は、INDEXとMATCHの組み合わせと同じです。
OFFSET関数は、
基準のセルまたはセル範囲から指定された行数と列数だけシフトした位置にある高さと幅のセルまたはセル範囲の参照 (オフセット参照) を返します。
OFFSET(基準,行数,列数,高さ,幅)
基準
基準となるセル範囲の参照を指定します。
行数
基準の左上隅のセルを上方向または下方向へシフトする距離を行数単位で指定します。
列数
基準の左上隅のセルを左方向または右方向へシフトする距離を列数単位で指定します。
高さ
オフセット参照の行数を指定します。高さは正の数である必要があります。
幅
オフセット参照の列数を指定します。幅は正の数である必要があります。
上の数式は、
基準（A2）から、3（MATCHの結果4-1）下方向へシフトし、1右にシフトした、B5の値が取得されます。
つまり、
A2から、下に3、右に1ずれた、B5を取得します。
ここでは、高さ1、幅1を指定していますので、１つのセルが取得されています。
OFFSETの使い方としては特殊な使い方になっています。
通常は、高さ、幅を指定して、セル範囲を返す関数として使用します。
ただし、OFFSET関数は、単独で使う事はほとんど無いでしょう、
他の関数との組み合わせで力を発揮する関数です。
VLOOKUPと同様の事を他の関数で実現する方法としては、
上記以外では、配列を使用した方法もあります。
配列による方法は、「VLOOKUPを他の関数でやる方法 」をご覧下さい。
MATCH関数
INDEX関数
OFFSET関数
この３つの関数が自在に使いこなせれば、作成するエクセルの幅がぐっと広がります。
—————————————————————————————————————
配列による方法 VLOOKUPを他の関数でやる方法 。
—————————————————————————————————————
セルの参照範囲を可変にする（OFFSET,COUNTA,MATCH）
数式の参照範囲を可変対応させます、つまり、入力データにより自動で拡張・縮小されるように関数を設定します。
エクセルの表には、集計等の計算式を入れますが、
その集計範囲は固定になっているため、
データの追加時には注意が必要になります。
参照しているセル範囲の途中に、行挿入、列挿入しなければ、参照範囲は広がりません。
しかし、これはあまりにも操作性が良くないです。
参照範囲を可変にする為に使用する関数は、
OFFSET関数
COUNTA関数
MATCH関数
上記の関数について良く理解してからお読みください。
また、
徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)
こちらも参考にして下さい。
以下の表を作成します。
E2には、全期間の売上合計
E3、E4には、日付の、入力規則のリストを設定
E5には、指定期間の売上合計
これらを、12行目以降にデータを追加しても、自動で参照範囲が変更されるように設定します。
まずは、使用する関数の説明です。
OFFSET関数
基準のセルまたはセル範囲から指定された行数と列数だけシフトした位置にある高さと幅のセルまたはセル範囲の参照 (オフセット参照) を返します。
OFFSET(基準,行数,列数,高さ,幅)
基準
基準となるセル範囲の参照を指定します。
行数
基準の左上隅のセルを上方向または下方向へシフトする距離を行数単位で指定します。
列数
基準の左上隅のセルを左方向または右方向へシフトする距離を列数単位で指定します。
高さ
オフセット参照の行数を指定します。高さは正の数である必要があります。
幅
オフセット参照の列数を指定します。幅は正の数である必要があります。
MATCH関数は、
指定された照合の型に従って検査範囲内を検索し、検査値と一致する要素の、配列内での相対的な位置を表す数値を返します。
MATCH(検査値,検査範囲,照合の型)
検査値
表の中で必要な項目を検索するために使用する値を指定します。
検査範囲
検査する隣接したセル範囲を指定します。
検索の型
-1、0、1 の数値のいずれかを指定します。
1を指定すると、検査値以下の最大の値が検索されます。
0を指定すると、検査値に一致する値のみが検索の対象となります。
-1を指定すると、検査値以上の最小の値が検索されます。
省略すると1を指定したものと見なされます。
COUNTA関数は、
セル範囲に含まれる空白ではないセルの個数を返します。
COUNTA 関数では、エラー値や空の文字列 (“”) を含め、すべての種類のデータを含むセルが計算の対象となります。
COUNTA(値1, [値2], …)
値1
必須。計算対象として含める値を表す 1 つ目の引数。
値2, …
省略可能。計算対象として含める値を表す追加の引数。引数は、最大 255 個まで指定できます。
SUM関数
これは省略します、よろしいですよね。
では、実際に
全合計（E3）
開始日(E4)
終了日(E5)
期間合計(E5)
それぞれの設定方法です。
全合計（E3）
=SUM(OFFSET($B$2,0,0,COUNTA($A:$A)-1,1))
$B$2,0,0
B2から、下に0、右に0移動、
つまり、B2になります。
COUNTA($A:$A)
A列にあるデータの個数です。
1行目が見出しになっていますので、1引いています。
上の図では、11になります。
従って11-1=10が、OFFSETの高さになります。
B列でもよいです、ここでは日付を基準にしているだけです。
つまり、
B2から、高さ10、幅1のセル範囲になります。
つまり、
B2～B11になります。
結局は、
=SUM(B2:B11)
となるわけです。
COUNTAでデータの個数を取得し、セル範囲を決定していますので、
データの追加・削除に自動で対応されるようになります。
開始日(E4)、終了日(E5)
まずは、名前定義を作成します。
- Ctrl+F3で名前定義を起動し、新規作成。メニュー等からの起動はバージョン毎に違います。
- 「名前」に「日付」と入力。
- 2007以降の場合、範囲は「ブック」、2003にはありません。
- 「参照範囲」に「=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)」
- OK」
=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)
COUNTAでA列のデータの個数を取得し、
A2からデータ個数分のセル範囲を決定しています。
E4、E5にリストを設定
- E4、E5セルを選択
- [データ]-[入力規則]
- [設定]の[入力値の種類]で[リスト]を選択
- [元の値] に「日付」と入力。F3を押すと名前定義の一覧から選択できます。
- 「OK」
期間合計(E5)
=SUM(OFFSET($B$2,
MATCH($E$3,OFFSET($A$2,0,0,COUNTA($A:$A)-1,1),0)-1,0,
MATCH($E$4,OFFSET($A$2,0,0,COUNTA($A:$A)-1,1),0)-
MATCH($E$3,OFFSET($A$2,0,0,COUNTA($A:$A)-1,1),0)+1,1))
かなり長い数式ですね、ちょっと笑えます。
この一番外側のOFFSETは、
B2のセルを、開始日までずらし、終了日までの高さのセル範囲を求めています。
作る時は、以下のように順番に作成します。
=SUM(B4:B8)
まずは、手作業で、SUM関数を入れてみます。
このB4:B8を可変にすれば良いわけです。
↓
=SUM(OFFSET(B2,2,0,5,1))
B4:B8をOFFSETに置き換えます。
引数は、とりあえず定数で入れてみます。
計算結果を見て、正しいことを確認しておきます。
↓
=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,MATCH(E4,A2:A11,0)-MATCH(E3,A2:A11,0)+1,1))
OFFSETの引数で開始位置である5をMATCHに置き換えます。
5=終了日の位置-終了日の位置+1
↓
=SUM(OFFSET(B2,MATCH(E3,OFFSET(A2,0,0,COUNTA(A:A)-1,1),0)-1,0,
MATCH(E4,FFSET(A2,0,0,COUNTA(A:A)-1,1),0)-
MATCH(E3,OFFSET(A2,0,0,COUNTA(A:A)-1,1),0)+1,1))
A2:A11を
OFFSET(A2,0,0,COUNTA(A:A)-1,1)
で置き換えます。
見やすくするために、絶対参照である$を省略しています。
絶対参照は、最初から入れられれば良いですが、
難しければ、すべて作成した後に、まとめて入れても良いでしょう。
最後の数式は、かなり長く複雑に感じますが、
関数のネスト（入れ子）が多いだけで、一つ一つの関数は難しいものではありません。
エクセルを使う一人一人にとって必要な関数は、そんなに多くないはずです。
必要なことは、その関数をいかに組み合わせるかにかかっています。
整理して、順序よく、考えれば、難しいく見える数式も作成できるようになります。
//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js
(adsbygoogle = window.adsbygoogle || []).push({
google_ad_client: “ca-pub-5658029825489984”,
enable_page_level_ads: true
});
コメント