excelの新関数XLOOKUP関数で必要な列だけを取り出すテク

excelの新関数XLOOKUP関数で必要な列だけを取り出すテク

"excel2021420-252-1" 

XLOOKUP関数で大きな表から特定の列だけを取り出す?!

　普段の業務で、大きなExcel表を扱わなければならないことはよくありますよね。列の数が多く、横に長い表だと、参照したい情報が一画面に表示されないことも多いと思います。そんな時、皆さんならどうしますか。

　ウィンドウ枠を固定して見出し列などの常に参照したい列を固定表示できるようにしたり、不要な列を非表示したりするかもしれません。確かに、これらの方法もよいですが、実はXLOOKUP関数を使って、大きな表の中から参照したい列だけを抜き出して表示することができます。大きな表を扱うことの多い読者は、覚えておくときっと役に立つでしょう。

　XLOOKUP関数はMicrosoft 365（旧称Office 365）に新しく登場した関数で、VLOOKUP関数で使いにくかった点が改善されています。また、VLOOKUP関数とHLOOKUP関数を兼ね備えた関数であるともいわれます。VLOOKUP関数は縦方向（Vertical）にデータを検索し、HLOOKUP関数は横方向（Horizontal）に検索するのが特徴です。今回の目的を実現するには、XLOOKUP関数のHLOOKUPの特徴を利用します。また、今回、もう1つ重要なのが、「スピル」という機能です。スピルという言葉には、「こぼれる」や「あふれる」という意味があります。スピルの機能を利用すると、1つのセルに数式を入力すると、隣接する複数のセルに「こぼれるように」計算結果が表示されるようになるのです。

　先ほど、XLOOKUP関数は、VLOOKUP関数とHLOOKUP関数を兼ね備えた関数であると紹介しましたが、実際の業務でHLOOKUP関数を使ったことのある人は少ないのではないでしょうか。よって今回の記事では、まずHLOOKUP関数の使い方から解説し、そのあと、XLOOKUP関数を使って、大きな表の中から特定の列だけを抜き出す方法を解説します。

HLOOKUP関数を使ってみよう

　HLOOKUP関数は、指定したデータのリストから検索条件を満たすデータを、excel横方向に検索して表示する関数です。次のような「納品表」で、A列に入力された品番（①）をもとに、右側の「商品表」（②）から商品名を取り出して、B列（③）に自動的に表示することができます。

"excel2021420-252-2" 

　

HLOOKUP関数は、次のような書式で記述します。

"excel2021420-252-3" 

　実際にB列（「納品表」の「商品名」列）に入力する数式を見ながら解説していきます。ここでは、セルB3には「=HLOOKUP(A3,$H$2:$J$4,2,FALSE)」（④）という数式を入力します。これは、セルA3（⑤）に入力された値を商品表（セル範囲H2:J4）（⑥）の上端の行から検索し、合致するデータと同じ列にある、範囲内で上から2番目の行の値（商品名）（⑦）を取り出して表示する数式です。

"excel2021420-252-4" 

　数式が入力できたら、［Enter］キーを押して数式を確定します。

　すると、品番「A103」に対応する商品名「ボールペン黒 10本セット」（⑧）が表示されましたね。

"excel2021420-252-5" 

　ここまでで、HLOOKUP関数の使い方を理解できたでしょうか。次の項では、いよいよ本題に入ります。

XLOOKUP関数を使って特定の列を抜き出そう

　excel「〇〇商事従業員名簿」の例でやってみましょう。この名簿は、列数が多く、一画面にすべての情報を表示できません。そこで、この表の中から必要な列だけを抜き出してみましょう。今回は、B列の「姓」、D列の「所属」、L列の「電話番号」のデータ（①）を抜き出してみます。

"excel2021420-252-6" 

　「〇〇商事従業員名簿」の右側のスペースに、抽出したデータを表示する欄を準備しておきます（②）。なお、説明しやすくするために、E～J列とM～O列は非表示にしています。

"excel2021420-252-7" 

　XLOOKUP関数の書式は、次のとおりです。

"excel2021420-252-8" 

　たくさん引数があって難しそうに見えるかもしれませんが、最初の3つの引数以外は省略可能です。今回も、指定するのは最初の3つの引数だけです。

　では、セルQ3に数式を入力します。XLOOKUP関数を使って、セルQ2の値（ここでは「姓」）をセル範囲A2:L2から検索し、該当するデータを表示する式を作成していきます。まず、セルQ3に「=XLOOKUP(」と入力したあとで、検索値となる「Q2」を入力し、「,」（カンマ）を入力します（③）。

"excel2021420-252-9" 

　excel続けて、検索値（セルQ2の値）を検索するセル範囲（A2:L2）を指定します。ここでは、セル範囲は絶対参照で「$A$2:$L$2」と入力し、「,」（カンマ）を入れます（④）。

"excel2021420-252-10" 

　最後に、戻り範囲を指定します。戻り範囲には、この名簿全体（セル範囲A3:L47）を指定します。ここでも、セル範囲は絶対参照で「$A$3:$L$47」と入力します。以降の引数は省略し、「)」（カッコ）を入力します（⑤）。

"excel2021420-252-11" 

　数式を入力できたら、［Enter］キーを押して、数式を確定します。

　すると、式を入力したセルQ3だけでなく、表内の一番下のセル（セルQ47）まで該当するデータが自動的に表示されました（⑥）！

"excel2021420-252-12" 

　これがスピルという機能の効果です。セルQ3に入力した数式の結果が、隣接する複数のセルにも「こぼれるように」表示されたというわけです。

　オートフィルを使って、この数式を隣のセルにもコピーしましょう（⑦）。

"excel2021420-252-13" 

　すると、「所属」列や「電話番号」列にも一瞬でデータが表示されました（⑧）。簡単に「名簿」から3列分のデータが抽出できましたね。

"excel2021420-252-14" 

　今回の例で、実際に数式を入力したのはたった1カ所です。それだけで、これらのセルに一気にデータを入力できてしまいました。最初は、引数の指定方法など、難しく感じるかもしれませんが、一度覚えてしまえば、仕事の効率は格段に上がること間違いなしです。

新関数XLOOKUP関数を使いこなそう

　今回は、Microsoft 365（旧称Office 365）に新しく登場したXLOOKUP関数を使って、大きな表から特定の列だけ抽出して表示する方法を解説しました。本連載では、今回の記事を含め、数回にわたり、XLOOKUP関数の特徴や便利な使い方について解説してきました。皆さんには、この新しい関数のあふれる可能性に、少しだけ触れていただけたのではないでしょうか。ぜひ一度、試してみてくださいね！