LibreOffice Calc: マッチした表の別の列の値を取得する


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 と書かれた右のセルに次の式を記述します。

パラメータの説明

A2 は 参照する値を指定しています。 ここではA2セルを参照していますので “ABC” という値になります。 LibreOffice のドキュメントによれば、 正規表現も扱えるそうです。

$B:$C は表のデータが含まれている部分です。 コピーしたときにも変化しないように、絶対参照にしています。 具体的には下の部分になります。

ABC 10
DEF 20
GHI 20

2 は表データの”2列目”という意味です。

最後の 0 (False でも可)は、 表データが検索のキー(ここでは “ABC”)の値でソートされていない場合に指定します。 ヒットしなかった場合はエラーが表示されます。 ソートされていて、必ず値が存在する場合は 1 または True にします。 ソートされていない表で 1 にした場合は、 マッチしない項目について意図せぬ値が表示されることがあります。

このようにすることで、 Age の列の値は表示できるようになりました。

表の右側・左側のセルの値を取得

Age 及び Volume の値を取得・表示する場合を考えます。 ここでは例として Volume の値について考えます。

OFFSETMATCH を使う方法、 LOOKUP を使う方法、 があります。

OFFSET, MATCH を使う場合は ABC と書かれた2つ右のセルに次の式を記述します。

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つ右のセルに次の式を記述します。

LOOKUP

値を操作してその値と同じ行または列の値を取得します。

A2 は捜査したい値のセルです。

$B$7:$B$9 は検索範囲です。 検索する値によって変化しないので絶対参照にしています。

$A$7:$A$9 は返す値の含まれている範囲です。 検索する値によって変化しないので絶対参照にしています。

これは検索する範囲の値がソートされている場合に使えます。 また、ヒットしなかった場合は最後のセルの値を返してしまいます。 そのため、検索範囲の値がソートされていて、捜査したい値が必ず含まれているとあらかじめわかっている場合にのみ使用することをお勧めします。