スピルと新関数の練習（XLOOKUP関数、LET関数、VBAまで）

公開日：2020-08-25　最終更新日：2020-08-26

スピルと新関数の練習（XLOOKUP関数、LET関数、VBAまで）

2019年Office365に追加された革新的な機能としてスピルがあります。
スピルとともに強力な新関数が登場しました。
その中でも特に使用頻度が高いと思われるXLOOKUP関数の練習をかねて、問題を作成しました。

スピルと新関数の基本については以下をお読みください。

XLOOKUP関数とスピル入門、スピルについて
FILTER関数、SORT関数、SORTBY関数
UNIQUE関数、RANDARRAY関数、SEQUENCE関数
XLOOKUP関数、XMATCH関数

まずは、VLOOKUP関数、INDEX関数、MATCH関数
これらの関数を個別にスピルで確認し、組み合わせて数式を完成させます。
そこから、XLOOKUP関数+OFFSET関数の組み合わせへ書き換えていきます。

さらに、まだ正式リリースされていませんがLET関数を使った数式に書き換えます。
そして今回は、さらに、VBAによるユーザー定義関数の作成までやります。

LET関数やVBAについては、必要なければ読み飛ばしてもらって構いませんが、
今は必要ないとしても、後学のためにも是非お読みになることをお勧めします。

スピルとXLOOKUP関数の練習問題

以下の3つのシートで構成されています。
※以下のデータの内容については深く考えないでください(笑)

※B列に数式を入れます。

※横にデータは増えていくことを前提とします。
※縦は最大100行まで入っている事とします。

※縦にデータが増えてていくことを前提とします。

「メニュー」シートのA列にメニューを入れたら、B列にkcalが表示されるように数式を入れます。

「メニュー」シートのA列が「酢豚」の場合、
「材料」シートの1行目で「酢豚」を探します。
D1に「酢豚」があるので、D列とE列が「酢豚」作成に必要な材料とグラム数になります。
材料として、
「酢」20gと「豚」80gが必要なことが分かります。
「カロリー」シートの列で「酢」と「豚」を探し、B列からkcal/100gを取得します。
「酢」は50(kcal/100g)、「豚」は700(kcal/100g)
「酢豚」のkcalは、
20/100*50 + 80/100*700 = 750kcal
これを導き出す数式を「メニュー」シートのB2セルに入れます。

従来の関数+スピルで数式を作る

順番に数式を作っていきます。
以下では、B1セルに入れる数式を作成します。
作成する数式に使う関数のひとつひとつを、その横のE列より右にいれてスピルで確認して行きます。

①列番号：メニューが「材料」シートの何列目にあるか

=MATCH(検査値,検査範囲,照合の種類)
=MATCH(A1,材料!$1:$1,0)

A1は「酢豚」なので、D1の4になります。

②材料：「材料」シート」の材料を取得する

=INDEX(配列,行番号,列番号)
=INDEX(材料!$2:$100,,MATCH(A1,材料!$1:$1,0))

$2:$100の4列になるので、

材料は最大100までとしています。
配列を材料!$2:$100としているので、データの入っていないセルは0と出力されます。
今回のサンプルであれば、1行目から指定して材料!$1:$100としても構いません。

先に取得した①列番号をINDEX関数の第3引数（列番号）に指定しています。
行番号を省略しているので配列の全行が取得されます。

スピルしていることを確認してください。
スピルしているという事は、複数の値（配列またはセル範囲）が返されているという事です。

③グラム：「材料」シート」のgを取得する

=INDEX(配列,行番号,列番号)
=INDEX(材料!$2:$100,,MATCH(A1,材料!$1:$1,0)+1)

$2:$100の4+1列になるので、

gは材料の1列右なので、先に取得した①列番号+1してから、
INDEX関数の第3引数（列番号）に指定しています。
行番号を省略しているので配列の全行が取得されます。

④カロリー：材料から「カロリー」シートのカロリーを取得する

=VLOOKUP(検索値,範囲,列番号,検索方法)
=VLOOKUP(INDEX(材料!$2:$100,,MATCH(A1,材料!$1:$1,0)),カロリー!A:B,2,0)

先に取得した②材料をVLOOKUP関数の検索値に指定し、
「カロリー」シートのA:Bの2列目を取得しています。

上図のF列の値がVLOOKUP関数の第1引数（検索値）に指定されていることになります。

以上の数式を組み合わせて式を完成させます

SUM(④カロリー*③グラム/100)
カロリーは(kcal/100g)なのでグラムは100で割ります。

ただし、ここまでの数式の結果を見て分かる通り、
④カロリーが#N/Aのエラー値が含まれています。
そこで、IFERROR関数でこれを回避します。

=SUM(
  IFERROR(
    VLOOKUP(INDEX(材料!$1:$100,,MATCH(A2,材料!$1:$1,0)),カロリー!A:B,2,0)
    * INDEX(材料!$1:$100,, MATCH(A2,材料!$1:$1,0)+1)/100,
  0))

このB1セルの数式をB2セル以降にコピーすれば完成です。

SUMPRODUCT関数に書き換えてみる

今回の場合はSUMと*で良いと思いますが、
掛け算の結果を合計する専用の関数としてとSUMPRODUCT関数があります。
SUM(A*B) → SUMPRODUCT(A,B)

=SUMPRODUCT(
    IFERROR(VLOOKUP(INDEX(材料!$2:$100,,MATCH(A1,材料!$1:$1,0)),カロリー!A:B,2,0),0),
    IFERROR(INDEX(材料!$2:$100,, MATCH(A1,材料!$1:$1,0)+1)/100,0)
)

今回の場合は、IFERRORはカロリーを取得するVLOOKUPの方にだけ指定すれば良いですが、
このような場合は、今後の修正等を考えて両方に指定しておいた方が良いでしょう。

縦にスピルさせることはできません

「メニュー」シートのA1を複数セル指定にすることはできません。
材料、グラム、カロリーを縦の配列で取得したいので、その入力値を縦のセル範囲（配列）で指定することができません。
今回の場合は、横のセル範囲（配列）を指定しても2次元配列作成が上手く動作しません。

これについては、XLOOUP関数でもLET関数でも同様です。

XLOOKUP関数に書き換える

INDEX関数+MATCH関数
VLOOKUP関数
これらをXLOOKUP関数に書き換えます。

XLOOKUP関数の基本については以下を参照してください。

XLOOKUP関数（範囲を検索し一致する対応項目を返す）

・XLOOKUP関数の書式・従来の関数の代わりとして・XLOOKUP関数をスピルさせる・見つからない場合・一致モードの使い方・検索モードの使い方・XLOOKUP関数をネストして戻り列を可変にする・XLOOKUP関数の戻りセル範囲を別の関数で使う・XLOOKUP関数が縦横に同時にスピルしないことについて・スピルと新関数の練習・XLOOKUP関数のVBA使用例・スピルによって新しく追加された関数

②材料：「材料」シート」の材料を取得する

=INDEX(材料!$2:$100,,MATCH(A1,材料!$1:$1,0))
これをXLOOKUPに書き換えます。

=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
=XLOOKUP(A1,材料!$1:$1,材料!$2:$100,0)

③グラム：「材料」シート」のgを取得する

=INDEX(材料!$2:$100,, MATCH(A1,材料!$1:$1,0)+1)
これをXLOOKUPに書き換えます。

=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
=OFFSET(XLOOKUP(A1,材料!$1:$1,材料!$2:$100,0),0,1)

XLOOKUP関数はセル範囲を返すのでOFFSET関数でずらすことができます。

④カロリー：材料から「カロリー」シートのカロリーを取得する

=VLOOKUP(INDEX(材料!$2:$100,,MATCH(A1,材料!$1:$1,0)),カロリー!A:B,2,0)
これをXLOOKUPに書き換えます。

=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
=XLOOKUP(XLOOKUP(A1,材料!$1:$1,材料!$2:$100,0),カロリー!A:A,カロリー!B:B,0)

以上の数式を組み合わせて式を完成させます

SUM(④カロリー*③グラム/100)

=SUM(
  IFERROR(
    VLOOKUP(INDEX(材料!$2:$100,,MATCH(A2,材料!$1:$1,0)),カロリー!A:B,2,0)
    * INDEX(材料!$2:$100,, MATCH(A2,材料!$1:$1,0)+1)/100,
  0)
)

これをXLOOKUP関数とOFFSET関数で書き直しました。

=SUM(
  IFERROR(
    XLOOKUP(XLOOKUP(A1,材料!$1:$1,材料!$2:$100,0),カロリー!A:A,カロリー!B:B,0)
    * OFFSET(XLOOKUP(A1,材料!$1:$1,材料!$2:$100,0),0,1)/100,
  0)
)

これもSUMPRODUCT関数に書き直すことができますが、先と同じなので省略します。

LET関数に書き換える

LET関数の基本については以下をお読みください。

LET関数（数式で変数を使う）

・LET関数の書式・LET関数の基本的使い方・LET関数の段階的作成・文字列分割での使用例・LET関数作成手順の参考例・LET関数の今後について

XLOOKUP関数+OFFSET関数
これで作成した数式をLET関数で書き換えます。

=SUM(
  IFERROR(
    XLOOKUP(XLOOKUP(A1,材料!$1:$1,材料!$2:$100,0),カロリー!A:A,カロリー!B:B,0)
    * OFFSET(XLOOKUP(A1,材料!$1:$1,材料!$2:$100,0),0,1)/100,
  0)
)

数式1つ1つに名前を付けていき、付けた名前を使って次の数式を作っていきます。
=LET(名前1,値1,名前2,値2,[名前3,値3,...],計算式)

=LET(
    メニュー,A1,
    材料,XLOOKUP(メニュー,材料!$1:$1,材料!$2:$100,0),
    グラム,OFFSET(材料,0,1),
    カロリー,XLOOKUP(材料,カロリー!A:A,カロリー!B:B,0),
    SUM(IFERROR(グラム/100*カロリー,0))
)

VBAでユーザー定義関数を作成

ユーザー定義関数の作り方も参考にしてください。

・簡単な例でユーザー定義関数を作ってみましょう・この関数の使い方・ユーザー定義関数の実践使用例


  
    
      Function kcal(メニュー範囲, 材料範囲, カロリー範囲)

　　Dim メニュー

　　メニュー = メニュー範囲.Value

　　

　　Dim rtn()

　　If Not IsArray(メニュー) Then

　　　　ReDim メニュー(1 To 1, 1 To 1)

　　　　メニュー(1, 1) = メニュー範囲

　　End If

　　ReDim rtn(LBound(メニュー) To UBound(メニュー))

　　

　　Dim 材料, グラム, カロリー

　　Dim i1, i2

　　

　　For i1 = LBound(メニュー) To UBound(メニュー)

　　　　On Error Resume Next

　　　　rtn(i1) = 0

　　　　i2 = WorksheetFunction.Match(メニュー(i1, 1), 材料範囲.Resize(1), 0)

　　　　If Not Err Then

　　　　　　材料 = 材料範囲.Columns(i2)

　　　　　　グラム = 材料範囲.Columns(i2 + 1)

　　　　　　For i2 = LBound(材料) + 1 To UBound(材料)

　　　　　　　　On Error Resume Next

　　　　　　　　カロリー = WorksheetFunction.VLookup(材料(i2, 1), カロリー範囲, 2, 0)

　　　　　　　　If Not Err Then

　　　　　　　　　　rtn(i1) = rtn(i1) + グラム(i2, 1) / 100 * カロリー

　　　　　　　　End If

　　　　　　Next

　　　　End If

　　Next

　　

　　kcal = WorksheetFunction.Transpose(rtn)

End Function

LET関数の数式と見比べてみてください。
非常に似ていることがわかると思います。（似せてVBAを書いたのではありますが）
VBAのセル範囲を取得する2次元配列とFor…Nextを理解すれば読めるVBAコードです。

第16回.繰り返し処理（For Next）

・For Next ステートメント・For Next 例文・For Next をステップイン実行で目で見て確認しましょう。・１行置きに処理する場合・Exit For ・For～Nextのネスト（入れ子）・最後に一言

第114回.セル範囲⇔配列（マクロVBA高速化必須テクニック）

・セル範囲⇔配列の基本VBA ・使用例・配列およびマクロVBAの高速化に関するページ

シートで使用するには、
=kcal(A1:A3,材料!1:100,カロリー!A:B)

関数と違いマクロVBAでは内部処理で配列を自由に扱えるので、今回の場合でも縦にスピルさせられます。

関数のユーザー定義に登録されます。

数式パレット（関数入力ダイアログ画面）も出ます

=kcal(
ここまで入力して、
Shift + F3
このショートカットで数式パレットが表示されます。

スピルと新関数の練習のまとめ

以下の流れで説明してきました。

1つ1つの関数でスピルを確認
↓
VLOOKUP関数+INDEX関数+MATCH関数で作成
↓
XLOOKUP関数+OFFSET関数に書き換え
↓
LET関数に書き換え
↓
VBAでユーザー定義関数の作成

まだマクロVBAを使ったことが無い方はVBA入門から始めてみてください。

・VBA学習の進め方について・1. VBAの基礎・基本：VBA入門・2. VBA入門に必要なVBEの基本的使い方・3. VBAプログラミングの基礎・基本・4. Excel各種機能とオブジェクトの理解：VBA入門・5. VBA初級からVBA中級を目指して・6. VBA入門の後日追加記事・7. VBA入門その後の学習について・「VBA入門」の記事を学校の授業もしくは企業研修でお使いになる場合

同じテーマ「エクセル入門」の記事

XLOOKUP関数（VLOOKUP関数を拡張した新関数）
XMATCH関数（MATCH関数を拡張した新関数）
LET関数（数式で変数を使う）
スピルと新関数の練習（XLOOKUP関数、LET関数、VBAまで）
スピルとは：スピル基礎から応用までの問題集
 スピルとは：スピル入門の問題と解説
 スピルとは：旧関数でスピルを使う問題と解説
 スピルとは：スピルの新関数を使う問題と解説
 スピルとは：ここまでの総合演習問題と解説
 LAMBDA以降の新関数について
 LAMBDA関数（カスタム関数の作成）

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.繰り返し処理（For Next)｜VBA入門
3.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
4.変数宣言のDimとデータ型｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門

ホーム
エクセル全般
エクセル入門
スピルと新関数の練習（XLOOKUP関数、LET関数、VBAまで）

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

エクセル入門
スピルと新関数の練習（XLOOKUP関数、LET関数、VBAまで）