時短エクセル（第48回）新関数「XLOOKUP」を一番分かりやすい例で学ぶ

　ここまで理解できたところで、実際に関数の使い方について見ていこう。関数式は最初に「=」（イコール）を入力、次に関数名を入力、そしてカッコの中に「引数」と呼ばれる値を入力する。引数が複数ある場合は「,（カンマ）」で区切る。すべて半角で入力する必要はあるが、大文字でも小文字でも構わない。以上がルールである。具体的に、「XLOOKUP」の関数式は以下である。

「XLOOKUP」の関数式
=XLOOKUP（検索値, 検索範囲, 戻り範囲, 見つからない場合, 一致モード, 検索モード）
「検索値」を「検索範囲」から探し、対応する項目を返す。見つからない場合に返すテキストや一致の種類、検索モードを指定することも可能。

　このように説明されると難しく感じるかもしれないが、6つの引数のうち、後ろの3つは省略可能だ。今回は後ろの2つは利用しないので、あまり気にしなくてよい。

[caption id="attachment_42499" align="aligncenter" width="600"] 1つ目の「検索値」には、どのデータに対して検索するかを指定する。キーにする値だ。この場合は「商品番号」なので、「B11」となる[/caption]

 

[caption id="attachment_42500" align="aligncenter" width="600"] 2つ目の「検索範囲」はどこを探すかを示すものなので、表の「商品番号」を指定する。このとき、表の場所がずれないようにドラッグをして範囲指定したのち、「F4」を押して絶対参照にしておく[/caption]

 

[caption id="attachment_42501" align="aligncenter" width="600"] 3つ目の「戻り範囲」は何を転記したいかを指定する。ここでは「商品名」と「単価」を転記したいので2列分を指定する。ここでも「F4」を押し、絶対参照にしておこう[/caption]

 

[caption id="attachment_42502" align="aligncenter" width="600"] 最後のカッコを閉じれば完成。「#N/A」と表示されているのはひとまず気にせずに、商品番号を入力してみよう[/caption]

 

[caption id="attachment_42503" align="aligncenter" width="600"] 「商品名」だけでなく「単価」のセルにも単価が入力される[/caption]

 

　通常、関数を入力したセルにしか戻り値は表示されないが、Excelには隣接するセルに値を配置する「スピル」という機能があり、それを利用している。この場合は商品名と単価のみだが、例えば顧客名簿などの場合は、顧客番号を入力するだけで顧客名、住所、電話番号、年齢などもすべて抜き出せる。

「#N/A」を非表示にする

　商品番号が未入力の場合、商品名のセルに「#N/A」と表示されるのが少々煩わしい。これを消去するには、4つ目の引数である「見つからない場合」を活用すればよい。

　現在の式に、4つ目の「見つからない場合」の引数を足してみよう。
=XLOOKUP(B11,$K$4:$K$12,$I$4:$J$12)
　↓
=XLOOKUP(B11,$K$4:$K$12,$I$4:$J$12,"")

　3つ目の引数の後に「,""」を追加する。「,」（カンマ）は引数と引数の間に付けるルール、「""」（ダブルクォーテーション）は、文字列を入力する際に用いるもので、例えば「"未入力"」とすれば、現在「#N/A」となっているところが「未入力」に置き換わる。「""」だけを入力すれば、何も文字列が表示されないということだ。

[caption id="attachment_42504" align="aligncenter" width="600"] 「""」で「#N/A」の表示も消えた[/caption]

 

　あとは、下方向にコピーをすればよい。なおここでは、数量を入力すると単価と数量を掛けた金額や小計、0.1を掛けた消費税などが表示されるような計算式や関数式を使っている。関数は一度フォーマットを作ってしまえば流用できるものがほとんどなので、後がラクになることを考えれば、積極的に使っていくようにしたい。

※解説用画面はMicrosoft 365のExcelを使用。一部メニュー名などが異なる場合がある
※第49回は2021年12月6日（月）公開

【ダウンロード】
「時短エクセル」　新関数「XLOOKUP」を一番分かりやすい例で学ぶ　のお試しファイルのダウンロードはこちらから（Excelファイル）