エクセル入門
スピルと新関数の練習(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関数は、範囲または配列を検索し、見つかった最初の一致に対応する項目を返します。VLOOKUP関数とHLOOKUP関数の両方の機能を備え、さらに新しい機能まで追加された新関数です。VLOOKUP関数、HLOOKUP関数の引数は4個でしたが、XLOOKUP関数の引数はなんと6個あります。

②材料:「材料」シート」の材料を取得する
=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関数は、関数内で計算結果やセル範囲に名前を定義できます。これにより、数式の中間計算に名前を定義したり、後ろの引数で定義した名前を式に使う事が出来ます。これはプログラミングにおける変数と同じ機能になります。

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でユーザー定義関数を作成

ユーザー定義関数の作り方も参考にしてください。
マクロ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)
VBAのForNextは、同じ処理を繰り返し行うためのVBA構文です。繰り返し処理はループ処理とも呼ばれます。マクロでのループ処理の記述は何通りかありますが、まず最初に覚えるべきものが、今回説明するForNextです。
第114回.セル範囲⇔配列(マクロVBA高速化必須テクニック)
セル範囲をVariant型変数に入れる事で、配列を作成することができます。また、配列をセル範囲にまとめて出力する事も出来ます。これは、マクロ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はExcelの操作を自動化するマクロ機能で使われているプログラミング言語です。「VisualBasicApplications」の略になります。マクロVBA入門シリーズでは、始めはより詳しく丁寧に解説し、少しずつ難易度を上げることで無理なく学習を進められるようにしています。



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

XLOOKUP関数とスピル入門
スピルについて
FILTER関数(範囲をフィルター処理)
SORT関数、SORTBY関数(範囲を並べ替え)
UNIQUE関数(一意の値)
RANDARRAY関数(ランダム数値)
SEQUENCE関数(連続数値)
XLOOKUP関数(範囲を検索し一致する対応項目を返す)
XMATCH関数(範囲から値を検索し一致する相対位置)
LET関数(数式で変数を使う)
スピルと新関数の練習(XLOOKUP関数、LET関数、VBAまで)


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

import文(パッケージ・モジュールのインポート)|Python入門(9月24日)
例外処理(try文)とexception一覧|Python入門(9月23日)
リスト内包表記|Python入門(9月22日)
Pythonの引数は参照渡しだが・・・|Python入門(9月21日)
lambda(ラムダ式、無名関数)と三項演算子|Python入門(9月20日)
関数内関数(関数のネスト)とスコープ|Python入門(9月18日)
関数の定義(def文)と引数|Python入門(9月18日)
組み込み関数一覧|Python入門(9月17日)
辞書(dict型)|Python入門(9月16日)
入力規則への貼り付けを禁止する|VBA技術解説(9月16日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




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


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



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