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

Excelの初心者向け入門解説
最終更新日:2020-08-26

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


Excel エクセル スピル 新関数 XLOOKUP関数 LET関数

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


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

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


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

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

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

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

Excel エクセル スピル 新関数 XLOOKUP関数 LET関数

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

Excel エクセル スピル 新関数 XLOOKUP関数 LET関数

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

Excel エクセル スピル 新関数 XLOOKUP関数 LET関数

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

「メニュー」シートの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)

エクセル Excel サンプル画像

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

Excel エクセル スピル 新関数 XLOOKUP関数 LET関数

②材料:「材料」シート」の材料を取得する
=INDEX(配列,行番号,列番号)
=INDEX(材料!$2:$100,,MATCH(A1,材料!$1:$1,0))

Excel エクセル スピル 新関数 XLOOKUP関数 LET関数

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

Excel エクセル スピル 新関数 XLOOKUP関数 LET関数

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

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

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

③グラム:「材料」シート」のgを取得する
=INDEX(配列,行番号,列番号)
=INDEX(材料!$2:$100,,MATCH(A1,材料!$1:$1,0)+1)

Excel エクセル スピル 新関数 XLOOKUP関数 LET関数

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

Excel エクセル スピル 新関数 XLOOKUP関数 LET関数

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

④カロリー:材料から「カロリー」シートのカロリーを取得する
=VLOOKUP(検索値,範囲,列番号,検索方法)
=VLOOKUP(INDEX(材料!$2:$100,,MATCH(A1,材料!$1:$1,0)),カロリー!A:B,2,0)

Excel エクセル スピル 新関数 XLOOKUP関数 LET関数

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

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

Excel エクセル スピル 新関数 XLOOKUP関数 LET関数

以上の数式を組み合わせて式を完成させます
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)

Excel エクセル スピル 新関数 XLOOKUP関数 LET関数

③グラム:「材料」シート」のgを取得する
=INDEX(材料!$2:$100,, MATCH(A1,材料!$1:$1,0)+1)
これをXLOOKUPに書き換えます。

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

Excel エクセル スピル 新関数 XLOOKUP関数 LET関数

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)

Excel エクセル スピル 新関数 XLOOKUP関数 LET関数

以上の数式を組み合わせて式を完成させます
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 をステップ イン実行で目で見て確認しましょう。 ・1行置きに処理する場合 ・Exit For ・For~Nextのネスト(入れ子) ・最後に一言
第114回.セル範囲⇔配列(マクロVBA高速化必須テクニック)
・セル範囲⇔配列の基本VBA ・使用例 ・配列およびマクロVBAの高速化に関するページ

シートで使用するには、
=kcal(A1:A3,材料!1:100,カロリー!A:B)
関数と違いマクロVBAでは内部処理で配列を自由に扱えるので、今回の場合でも縦にスピルさせられます。

関数のユーザー定義に登録されます。
Excel エクセル スピル 新関数 XLOOKUP関数 LET関数

数式パレット(関数入力ダイアログ画面)も出ます
Excel エクセル スピル 新関数 XLOOKUP関数 LET関数

=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入門その後の学習について



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

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


新着記事NEW ・・・新着記事一覧を見る

TOROW関数(配列を横1行の配列にして返す)|エクセル入門(2022-10-31)
TOCOL関数(配列を縦1列の配列にして返す)|エクセル入門(2022-10-31)
CHOOSECOLS関数(配列から複数の指定された列を返す)|エクセル入門(2022-10-29)
CHOOSEROWS関数(配列から複数の指定された行を返す)|エクセル入門(2022-10-29)
WorksheetFunctionの効率的な使い方とスピル新関数の利用|VBA入門(2022-10-27)
VSTACK関数(配列を縦方向に順に追加・結合)|エクセル入門(2022-10-25)
HSTACK関数(配列を横方向に順に追加・結合)|エクセル入門(2022-10-25)
LAMBDA以降の新関数の問題と解説(配列操作関数編)|エクセル入門(2022-10-24)
LAMBDA以降の新関数の問題と解説(ヘルパー関数編)|エクセル入門(2022-10-24)
LAMBDA以降の新関数の問題集|エクセル入門(2022-10-24)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Excelショートカットキー一覧|Excelリファレンス
7.並べ替え(Sort)|VBA入門
8.マクロって何?VBAって何?|VBA入門
9.エクセルVBAでのシート指定方法|VBA技術解説
10.ExcelマクロVBAの基礎を学習する方法|エクセルの神髄




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


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



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