目次
LibreOffice Calc でマッチした表の別の列の値を取得する方法です。
環境
- LibreOffice 6.0.1.1
方法
ここでは2つの方法を紹介します。 ひとつは、表の右側のセルについてのみ値が取得できる方法、もうひとつは表の左右どちらでも値が取得できる方法です。 後者のほうが複雑になる分、式も長くなります。 いずれもやり方はいくつかありますが、代表的なものを紹介します。
例題
次のようにテーブルがあったとします。 下に表があり、その値を上の表で表示したい場合を考えます。 例であるためとても単純な表となっています。
| A | B | C | |
|---|---|---|---|
| 1 | Name | Age | Volume |
| 2 | ABC | ||
| 3 | DEF | ||
| 4 | GHI | ||
| 5 | |||
| 6 | Volume | Name | Age |
| 7 | 100 | ABC | 10 |
| 8 | 200 | DEF | 20 |
| 9 | 300 | GHI | 20 |
ほしい完成形は次の通りです。
| A | B | C | |
|---|---|---|---|
| 1 | Name | Age | Volume |
| 2 | ABC | 10 | 100 |
| 3 | DEF | 20 | 200 |
| 4 | GHI | 30 | 300 |
| 5 | |||
| 6 | Volume | Name | Age |
| 7 | 100 | ABC | 10 |
| 8 | 200 | DEF | 20 |
| 9 | 300 | GHI | 20 |
表の右側のセルの値を取得
Age の値を取得・表示する場合を考えます。
VLOOKUP を使います。
ABC と書かれた右のセルに次の式を記述します。
|
1 |
=VLOOKUP(A2, $B$7:$C$9, 2, 0) |
パラメータの説明
A2 は 参照する値を指定しています。 ここではA2セルを参照していますので “ABC” という値になります。 LibreOffice のドキュメントによれば、 正規表現も扱えるそうです。
$B:$C は表のデータが含まれている部分です。 コピーしたときにも変化しないように、絶対参照にしています。 具体的には下の部分になります。
| ABC | 10 |
| DEF | 20 |
| GHI | 20 |
2 は表データの”2列目”という意味です。
最後の 0 (False でも可)は、 表データが検索のキー(ここでは “ABC”)の値でソートされていない場合に指定します。 ヒットしなかった場合はエラーが表示されます。 ソートされていて、必ず値が存在する場合は 1 または True にします。 ソートされていない表で 1 にした場合は、 マッチしない項目について意図せぬ値が表示されることがあります。
このようにすることで、 Age の列の値は表示できるようになりました。
表の右側・左側のセルの値を取得
Age 及び Volume の値を取得・表示する場合を考えます。 ここでは例として Volume の値について考えます。
OFFSET と MATCH を使う方法、 LOOKUP を使う方法、 があります。
OFFSET, MATCH を使う場合は ABC と書かれた2つ右のセルに次の式を記述します。
|
1 |
=OFFSET($A$7, MATCH(A2, $B$7:$B$9, 0) - 1, 0) |
MATCH
値が見つかったところの位置を返す関数です。 例では MATCH(A2, $B$7:$B$9, 0) で使われていました。
A2 は、検索する値です。
$B$7:$B$9 は検索するデータのある範囲です。 検索範囲は行によって変わらないので絶対参照にしています。 具体的には次の部分になります。
| ABC |
| DEF |
| GHI |
0 は完全一致を表します。
1番目の値 (ここでは “ABC”) にマッチすると、 1が返ってきます。
OFFSET
基準となるセルから指定した行数・列数だけ移動したセルの値を返します。 例では OFFSET($A$7, MATCH(A2, $B$7:$B$9, 0) - 1, 0) で使われていました。
$B$7 は基準となるセルです。 行によって変わらないので、絶対参照にしています。 今回、欲しい値が含まれている列の一番上のセルを基準となるセルにしました。
MATCH(A2, $B$7:$B$9, 0) - 1 は基準となるセルから行方向にどれくらいずれたセルを取得するかを表す値です。 MATCH の値は一番最初のセルを 1 として返すので、 そこから 1 を引いています。
0 は、 列方向にどれくらいずれているかを表す値です。 基準となるセルを欲しい値が含まれている列にしましたので、 列方向のずれは発生せず、 0 となります。
行・列方向のずれは マイナスの値も使えますので、左右上下、どの向きでも指定できます。
OFFSET を使用すると、右でも左でもセルの値を取得できます。
LOOKUP を使う場合は ABC と書かれた2つ右のセルに次の式を記述します。
|
1 |
=LOOKUP(A2, $B$7:$B$9, $A$7:$A$9, 0) |
LOOKUP
値を操作してその値と同じ行または列の値を取得します。
A2 は捜査したい値のセルです。
$B$7:$B$9 は検索範囲です。 検索する値によって変化しないので絶対参照にしています。
$A$7:$A$9 は返す値の含まれている範囲です。 検索する値によって変化しないので絶対参照にしています。
これは検索する範囲の値がソートされている場合に使えます。 また、ヒットしなかった場合は最後のセルの値を返してしまいます。 そのため、検索範囲の値がソートされていて、捜査したい値が必ず含まれているとあらかじめわかっている場合にのみ使用することをお勧めします。
