経理業務で海外での経費をすべて確定申告の通貨に換算する必要がある場合、すべての経費が発生した日付の為替レートを把握しておく必要があります。手動で入力する必要はありません。毎日の為替レートのリストを入手し、Excelにすべての経費の適切な換算率を調べさせることができます。また、部品番号で部品を検索したり、従業員番号で従業員を検索したり、国番号で国番号を検索したり、マスターレコードのキーを使って検索したり、現在のスプレッドシートにコピーするのが適切でないその他の検索を行う場合にも、VLOOKUP関数を使用できます。
しかし、それを年に 1 回だけ行う場合は、おそらく Excel の VLOOKUP 関数の使用方法を調べる必要があるでしょう。
Excel で 3 番目によく使用される関数 (SUM と AVERAGE に次いで) である VLOOKUP は複雑です。既定では近似一致を検索しますが、これが必要なことはほとんどないため、関数で FALSE を設定することを忘れないようにしてください。また、柔軟性もありません。数値を検索しているシートで列を追加または削除しても、SUM などの関数のように数式は更新されないため、VLOOKUP の数式を手動で変更する必要があります。日付などのインデックス列が左側に、レートなどの必要な値が右側になるようにソース データを並べ替える必要があります。データの順序が異なる 2 つ目のコピーがなければ、取引所が最悪または最高だった日を見つけることはできません。検索順序を変更することはできず、列ではなく行にある情報を検索する必要がある場合は、代わりに HLOOKUP などの別の関数を使用する必要があります。
VLOOKUP は、インデックス列、必要なデータを含む列、およびその間にあるその他の列をカバーする配列を作成するため、その間にあるデータについては考慮しない場合でも、パフォーマンスが低下する可能性があります。
画像: メアリー・ブランスコム/TechRepublic
新しいXLOOKUP関数は、これらの問題をすべて解決します。よりシンプルで柔軟性が高く、スプレッドシートの速度低下も抑えられます。行または列から結果を検索でき、データ列はインデックス列の右側にある必要はありません。XLOOKUPを複数の列に指定して、複数の情報を取得できます。例えば、従業員の名前と所属部署の両方を検索できます。一致するものが見つからない場合、デフォルトの#N/Aではなく、「名前が見つかりません」などのカスタムエラーを入力することもできます。検索順序と一致方法の両方をカスタマイズできます。返される結果は値ではなく参照であるため、別の数式に渡すことができます。
XLOOKUP には、検索に使用する用語、用語を検索する場所と、その用語に対して取得する必要があるデータ、検索するデータを配置する場所という少なくとも 3 つのパラメータが必要です。
最初の項である検索値には、検索する値を持つセル、名前のように数式に入力した値、あるいは計算式のような別の数式を指定できます。これはVLOOKUPと同じですが、配列内の複数のセルを連結することもできます。例えば、B1、B2、B3は、1つのセルだけを検索するのではなく、3つのセルすべてが一致する値を検索します。
しかし、VLOOKUPではA2:D400のような配列を使ってセルA2からA400を検索し、同じ行のD列から値を取得するのに対し、XLOOKUPでは検索範囲と戻り値の2つのパラメータを使用します。同じ検索であれば、A2:A400とD2:D400になります。1600個近くのセルを含む配列を作成する代わりに、XLOOKUPは800個弱のセルしか処理する必要がありません。検索元のスプレッドシートが大きいほど、パフォーマンスの違いは大きくなります。
さらに、インデックス列と結果が入っている列の間に新しい列を挿入すると、間違った列から結果が返されるため、VLOOKUP の場合のように数式が壊れるのではなく、数式が自動的に更新されます。
複数の列で一致する値を検索する場合は、& — C2:C200 & E2:E200 を使用して列を結合します。
売上高が最も高い店舗、ディスク容量を最も多く使用している店舗、休暇日数が最も少ない店舗などを調べたい場合は、検索値にMAXまたはMINの数式を使用します。これはVLOOKUPよりも柔軟性が高く、検索に使用する値が常に左端の列になるようにデータを並べ替える必要がないためです。XLOOKUPは左、右、上、下など、どの列または行を検索するかを指定するだけで済みます。
同じスプレッドシート内でこれらの数値を確認する場合は、表の合計行を使用して合計を最大値または最小値に設定するか、条件付き書式を使用して情報を抽出するだけで済みます。ただし、その最小値または最大値を取得して別のスプレッドシートで使用したい場合は、ルックアップ関数が必要です。
複数の情報を同じ検索で取得したい場合でも配列を使用できますが、取得したい情報ごとに1回の検索を行うのではなく、1回の検索ですべてを取得できます。つまり、列Cの従業員名と列Dの部署を検索するには、戻り値の配列にC2:D400と入力します。
標準的な検索順序とエラーで問題なければ、ルックアップを実行するのに必要なのはそれだけです。VLOOKUPよりも分かりやすい数式です。また、ルックアップをカスタマイズすることも可能です。
「名前が見つかりません」や「この日付の為替レートがありません」などのカスタム エラーや、一致するものがなかったことを #N/A よりも明確に説明するエラーが必要な場合は、それを 4 番目のパラメータ ([if_not_found] と呼ばれます) として引用符で囲んで入力します。
通常は、名前や特定の日の為替レートなど、正確な答えを検索します。しかし、給与がどの税率区分に該当するかを調べるなど、数値がスケールのどこに位置するかを調べることもできます。その場合、完全に一致するものを見つけられない可能性があります。5番目のパラメータ[match_mode]を1に設定すると、完全に一致するものがない場合は次に大きい結果が取得され、-1を指定すると次に小さい項目が取得されます。税率区分に該当する給与を一致させる場合は、税率区分が最大額まで適用されるため、次に大きい結果と一致させる必要があります。つまり、30,000ポンドの給与には、50,000ポンドまで適用される20%の税率を検索する必要があります。つまり、次に大きい結果で一致させると、その税率が得られます。
参照: 開発者になる方法: チートシート (TechRepublic)
match_mode を 2 に設定すると、Excel でよく使われるワイルドカード「?」と「*」を使って一致対象を指定できます。例えば、「South*」で「Southeast」と「Southeast」を検索したり、「*west」で「Northwest」と「Southwest」を検索したりできます。
XLOOKUP のデフォルトの検索は上から下です。最初の一致が見つかるまでリストの下から検索する場合は、6 番目のパラメータ [search_mode] を -1 に設定します。
機能から機能へ
XLOOKUP関数をネストすることで、最上行と左列のラベルを参照してテーブルからセルを抽出することができます。最初のXLOOKUP関数を使用して最初のラベルを検索し、別のXLOOKUP関数を戻り値配列として入力して2番目のラベルを検索すると、それらのラベルが交差するセルの内容が結果として得られます。（これはINDEX関数とMATCH関数を使用するのと似ていますが、さらに2つの関数を学ぶ必要はありません。）最も簡単な方法は、結果を表示したいセルの上と横に同じラベルを配置し、それらのラベルを使用して一致させることです。
XLOOKUPから返される結果は、セル内の値のコピーではなく参照であるため、別の数式に渡すこともできます。XLOOKUPを2つ実行する場合、結果を配列に変換し、SUM関数を使って2つのセルの間のすべてのセルを合計するか、MAX関数を使って2つのセルの間の最大値を求めることができます。こうすることで、月初と月末の為替レートを調べ、最も高いレートを表示できます。
XLOOKUPは現在ベータ版です。Office Insidersプログラムに登録されている方のみご利用いただけます。Microsoftは9月初旬からすべてのInsider向けにXLOOKUPのフライトを開始しましたが、まだすべてのユーザーに届いていない可能性がありますので、表示されない場合は引き続きご確認ください。既にご利用の場合は、最終機能としてリリースされる前に変更される可能性があることにご注意ください。XLOOKUPが導入されて以来、[not_found]パラメータが追加されました（当初は6番目のパラメータでしたが、その後4番目のパラメータに変更されたため、空のパラメータを入力する必要がなくなりました）。
リリース後は、Office 365 サブスクリプションの一部としてご利用いただけ、その後、Excel の次期正式版がリリースされる際にもご利用いただけるようになります。そのため、Excel 2019 の正式ライセンスを既にご購入いただいている場合は、他のサブスクリプション機能と同様に、アップグレードするまでご利用いただけません。常に最新の Office 機能をいち早くご利用になりたい場合は、Office 365 サブスクリプションが最適です。
