エクセルの関数で多用されるのは、
・sum
・average
・count
・lookup
ではないでしょうか。（偏見）
特にlookup系はvlookupなど馴染みがあるのでは。
今回は、lookup関数の中で最も使われているであろうvlookupより使いやすくなった新しい関数「xlookup」についてです。
何が便利になったの？
端的に言うと、「vlookupは一番左を探して、合致するものがあったら表示」なので、検索する列を左にする＝検索列より左側のものを検索できないこと、また「合致するものがあった行の、何番目を表示する」際に数え間違えたりする場合も多いですよね。
これが「xlookupは検索列で探して、合致するものがあったら指定した列の該当する行のセルを表示する」ので、「指定した列」が検索列の左にあろうと問題ないのです。他にも色々良くなったところがありますが、今回はまずxlookupってどんな関数でどう指定するんだ、と言う基本的なことをご説明します。
基本的な書式
商品マスタって、普通は
コード｜商品名｜単価など
と一番左に商品CDがありますよね。ただ、商品点数が多いと「商品コードなんていちいち覚えてないのに、発注書などには書かないといけない」なんて場合もあるでしょう。その場合、商品名を記入して商品コードが表示されると便利ですが、vlookupでは検索される列が一番左という縛りがあったためindexやmatch関数などを駆使して長い式を入れる必要がありました。
xlookupはこれを解決しています。
下画像を見て下さい。今まで通り、商品コードが一番左、そこから商品名などが続くごく一般的なテーブルに対して検索をかけるようになっています。
基本的な書式は、
=xlookup（ このデータで , この範囲を調べ , この列の結果を返す ）
です。
調べたいのはコードなので、CDの下で緑になってるセルに関数を入れましょう。
画像の場合なら、名称の下にある青くなってる「秋田杉30」と入ったセル（I2）のデータで、赤いセル範囲（C2:C15）を調べ、紫のセル範囲（B2:B15）から結果を返す、となります。
関数で表すと、
=xlookup(I2,C2:C15,B2,B15)です。
こんな簡単に、検査する列の左側にある列から結果を反映させられます。
列の範囲を合致させるように注意
気をつけなければならないのは、
調べる範囲と、結果を返すための範囲の高さ（データ量）が同じでなければならない
ことです。
ここでも画像で見てみましょう。
調べる範囲がC2からC16で、その結果どの範囲から返すかというのがB2からB15です。
今言った、調べる範囲と結果を返すための範囲が異なっています。
するとこのように、#VALUE!となってしまいます。
このエラーを回避するのに最も効率的なのは、「そもそも調べる・結果を返す」列の範囲をいちいち指定せず「列まるごと」にしてしまえば良いということです。
つまり、C2:C15などのように行番号をいれず、C:Cのように列だけ指定してしまいます。
これはxlookupに限ったことではありませんが、テーブルの下に別のテーブルを作ることがないとわかっているのであれば、行番号なしで列全体を指定範囲とするのは割と色々な場面で有効活用できます。覚えておいてください。
データが見つからない場合のオプション
Vlookupでは「合致するデータがない場合に『なし』と表示したい」時は、IF関数やiserror関数と組み合わせて、
=if(iserror(vlookup(検索値,検索範囲,結果範囲)),”なし”,vlookup(検索値,検索範囲,結果範囲))のように面倒臭い関数を入れていましたが、xlookupはオプションで用意されています。
画像にあるように、
=xlookup（ このデータで＝検索値 ,
この範囲を調べ＝検索範囲 ,
この列の結果を返す＝戻り範囲 ）
の直後に、[見つからない場合]があり、ここに見つからなかった時に表示するものを書いておきます。
「なし」と表示したい場合は文字列を表すダブルクォーテーション（”）で囲んで、”なし”と入れましょう。
すると上の画像のように、データ範囲中に指定したデータと合致するものがなかった場合「なし」と表示されます。
これが関数の組み合わせ（ネスト）なしで一発入力できるようになったのはとても便利ですね。
一致モードのオプション
次に指定できるオプションは「調べたいデータと一致するかどうかをどこまで厳格にやる？」です。
つまり、
「完全に一致してなければNGだ」
「完全に一致してるものがなければ、それに最も近い小さいものを」
「完全に一致してるものがなければ、それに最も近い大きいものを」
「ワイルドカード検索を有効にする」
という４種類です。
それぞれ、
2ワイルドカード検索
| 一致モード | 完全一致 |
|---|---|
| 0 （指定しなければこれ） |
完全一致 |
| -1 | 完全一致していればそれ、そうでなければそれに近い小さいもの |
| 1 | 完全一致していればそれ、そうでなければそれに近い大きいもの |
になります。
ワイルドカードで検索する
さて、この検索オプションの中で気になるのはワイルドカードです。
これは「調べたいものの一部を指定して、それを含むものを検索する」機能です。
つまり、「*杉」とあれば「秋田杉」や「ヒマラヤ杉」を検索してくれます。
ワイルドカードはアスタリスク「*」をつけてください。そうでなければ、いくらオプションで「2」を指定してもエラーとなります。
検索したいものにワイルドカード文字（*）を入れていないとエラー
入れていると検索してくれる
エクセルもAkrosで
いかがでしたか？
本当はもっと便利な機能がいっぱいありますが、今回は基本の基本、関数の書き方ということでここまで。
AkrosはWebデザインスクールですが、実はエクセルなどのビジネス系ソフトも学べます。
もちろんマンツーマンレッスンが可能です。
オフィス系ソフトをもっと使いこなして業務効率化したい、ネット上だけで勉強すると色々な情報がありすぎるし、一つの情報では実現できないからたくさん見るとそれぞれで言ってることや表現が違うからわかりづらいし、という方はぜひAkrosへご相談ください。
