[Excel] Match関数とOffset関数を組合せて表から「検索」する

表の中からIDに該当する値を検索するには、Vlookup関数を使うのが一般的です。
しかし、Vlookup関数では、検索列が表の左端にある必要があります。

Match関数とOffset関数を組み合わせると、Vlookupよりも柔軟な検索ができます。

1. 表から逆参照したい場合

たとえば、以下のような予約データから顧客IDを元に予約時刻を逆算してみます。

予約IDなら検索列が左端にあるので、VLookUp関数が使えます。
しかし、顧客IDは左端ではないので使えません。

• Match関数で検索値の行を見つける
• Offset関数でその位置から目的のデータがある場所までずらす

まずは、同じシート内で逆参照の式を作っていきます。
IDが表の何行目にあるのかを調べます。

2. Match関数

Excelの Match関数は、配列内で指定した値を検索し、その相対的な位置を返す関数です。

=MATCH(101, $C$2:$C$5, 0) は、IDが並んだ配列から何番目に 値101 があるのかを求めます。
結果は 3 になります。

=MATCH(検索値, 検索範囲, [検索タイプ])

Match関数の検索タイプは、0を使うのが定番です。
1, -1 を使うと違った段階的な範囲検索もできますが、それはまたの機会に。

3. Offset関数

配列の中の何個目なのかがわかると、予約時刻の列で下に進んでいきます。

ExcelのOffset関数は、指定したセルや範囲を基準として、そこから一定の行数と列数だけ離れた位置にある別のセルや範囲を参照することができます。

たとえば、簡単な例では
=OFFSET(A1, 2, 1) は、A1セルから2行下、1列右のセル（B3）を参照します。

今回の場合は、
=OFFSET($B$1, B9, 0)は、B1セルを基準に、B9行下のセルを参照します。
結果は、「0.416666667」になります。
このセルを表示形式を「時刻」に変更すると「10:00」になります。

=OFFSET(基準セル, 行数, 列数, [高さ], [幅])

今回は高さ・幅を省いて 1つのセルに限定しています。

Offset関数はセル範囲を返すこともでき、vlookup関数などほかの関数の「範囲」に使うこともできます。

4. 組み合せた関数式

1つのセルにMatch関数とOffset関数を組み合わせた式を入れることもできます。

B9 = MATCH(101, $C$2:$C$5, 0)
B10 = OFFSET($B$1, B9, 0)

これらをまとめると、

=OFFSET($B$1, MATCH(101, $C$2:$C$5, 0), 0)

このようになります。

下の式の「B9」のところに、上の式を入れればいいんだね。

ただし、式は長く複雑になります。
はじめのうちは中間結果を計算するセルを用意しておく方がわかりやすいと思います。
不要ならその行や列を非表示にすることもできるので。

5. 顧客ごとの予約一覧表を計算する

次の問題は、さきほどの予約データをもとに顧客IDごとの予約時刻を計算していきます。

顧客IDで並べ替えればいいんじゃないの？

この表だけではそうですが、顧客がほかにもある場合があるからです。

こちらもどうぞ。

[Excel] 小数点以下を切り捨てる関数（INT, RoundDown）

小数点以下を切り捨てるだけなら INT関数が手軽です。切り捨ての桁を変えたいときには RoundDown関数が役立ちます。切り捨てにはINT関数かRoundDown関数小数点以下を切り捨てるには、INT関数かRoundDown関数を使います。INT関数は、「整数（Integer）」にする関数です。与えた値を最も近い整数に切り捨てます。RoundDown関数は、「切り捨て（RoundDown）」るです。小数第何位で切り捨てるのか、桁数を選択できます。桁数にマイナスも指定すると、...

chiilabo.com

[Excel] 数式はコピーしてこそ役に立つ【セル参照】

Excel は「表計算ソフト」です。数式を活用してこそ、その真価を発揮します。とくに数式は「自動的に再計算される」性質があり、うまく再利用すると計算の手間をぐんと減らせます。逆にいうと、再利用しなければ 数式の良さを活かすことはできません。数式の利点は「再利用」「表計算」には「電卓」と違って ２つの特長があります。▶ 自動的に再計算されること▶ 便利な関数を利用できること電卓は、計算のたびに手入力が必要です。しかし、Excelでは、一度 計算式を入力しておけば、似たような計算...

chiilabo.com

QRコードを読み込むと、関連記事を確認できます。