少々特殊ですが、一つのシートに連続した請求明細が存在します。その中から指定した宛先の請求合計を抽出することになりました。
シート例
次のように 2 名の宛先とその明細が一つのシートに存在します。
| A | B | C | |
| 1 | 名前 | 山田花子 | |
| 2 | 品物 | 金額 | |
| 3 | 1 | バナナ | 100 |
| 4 | 2 | リンゴ | 150 |
| 5 | 3 | みかん | 50 |
| 6 | 4 | ||
| 7 | 5 | ||
| 8 | 合計 | 300 | |
| 9 | |||
| 10 | 名前 | 田中一郎 | |
| 11 | 品物 | 金額 | |
| 12 | 1 | バナナ | 100 |
| 13 | 2 | みかん | 50 |
| 14 | 3 | メロン | 500 |
| 15 | 4 | パイナップル | 200 |
| 16 | 5 | ||
| 17 | 合計 | 850 |
抽出例
この表からそれぞれの宛先の合計金額を抽出します。
<山田花子さんの合計値を抽出する計算式> =OFFSET($C$1,MATCH("山田花子",B:B,0)+6,0)
<田中一郎さんの合計値を抽出する計算式> =OFFSET($C$1,MATCH("田中一郎",B:B,0)+6,0)
説明
一つ一つ説明します。
1. MATCH("山田花子",B:B,0) は、指定した検索値の位置を決定します。具体的には B:B 列から "山田花子" が完全一致する位置を算出します。この場合は 1 番目でヒットしますので 1 が返されます。
同様に MATCH("田中一郎",B:B,0) の場合は、10 が返されます。エクセルの行数と一致していることが分かります。
2. 次に合計セルの位置を決定するために OFFSET 関数を使います。MATCH("山田花子",B:B,0) で 1 が返されますので、山田花子さんの OFFSET 関数は OFFSET($C$1,1+6,0) に展開されます。これは $C$1 から 行方向に 7 (1+6) 行、列方向に 0 列移動することを意味します。これで合計値が入っているセルが指定されます。
同様に田中一郎さんの場合は、OFFSET($C$1,10+6,0) に展開され、対応した合計セルが指定されます。
ちなみに、OFFSET 関数内で、行 に 6 を加算していますが、これは名前と合計行の間隔です。つまり、名前の行位置を見つけて、そこから合計行がどのくらい離れているかがカギになります。
これで、山田花子さん、田中一郎さん、それぞれの合計値 300 円と 850 円を抽出することができます。