【VBAリファレンス】エクセル入門LAMBDA以降の新関数の問題集

スポンサーリンク

概要

Microsoft Excelは、その強力なデータ分析・処理能力で、ビジネスシーンはもとより、学術研究、個人の生産性向上など、幅広い分野で活用されています。近年、Excelの関数機能は目覚ましい進化を遂げており、特にLAMBDA関数以降に登場した新しい関数群は、従来のExcelの常識を覆すほどの柔軟性と表現力を提供しています。これらの新関数は、複雑な処理を簡潔に記述することを可能にし、より高度な分析や自動化を実現する上で不可欠な存在となりつつあります。

しかし、これらの新関数は、その登場からまだ日が浅く、多くのユーザーにとって学習コストが高いのが現状です。特に、LAMBDA関数のように、関数内でさらに関数を定義するという概念は、従来のExcel関数に慣れ親しんだユーザーにとっては、習得に時間がかかる場合があります。また、これらの新関数を効果的に活用するための実践的な問題集や、具体的な応用例が不足しているという声も多く聞かれます。

本記事では、LAMBDA関数以降に登場した主要な新関数に焦点を当て、それらの基本的な使い方から、より実践的な応用例、そして実際の業務で直面しうる課題に対する解決策までを、豊富な問題演習とともに解説していきます。読者の皆様が、これらの新関数をスムーズに習得し、Excelの可能性を最大限に引き出せるようになることを目指します。

詳細解説

LAMBDA関数以降の新関数群は、Excelの関数記述のパラダイムを大きく変えるものでした。ここでは、特に重要度の高い関数に絞って、その特徴と使い方を解説します。

1. LAMBDA関数

LAMBDA関数は、カスタム関数を作成するための関数です。これにより、複雑な計算ロジックを一度定義し、名前を付けて再利用できるようになります。

* **構文:** `LAMBDA(引数1, [引数2], …, 計算式)`
* **特徴:**
* 名前付きセル（数式）として定義することで、ワークシート関数のように呼び出せます。
* 再帰処理を記述することも可能です。
* 他の新関数（LET関数など）と組み合わせることで、さらに強力な機能を発揮します。
* **例:** 2つの数値の合計を計算するLAMBDA関数を定義します。
* 「名前の管理」で新規作成し、名前を「MySum」、参照を `=LAMBDA(a, b, a+b)` とします。
* セルに `=MySum(10, 20)` と入力すると、`30` が返ります。

2. LET関数

LET関数は、計算の途中で使用する中間的な値や計算結果に名前を付け、それらを後続の計算で再利用するための関数です。これにより、数式の可読性と効率性を大幅に向上させることができます。

* **構文:** `LET(名前1, 値1, [名前2, 値2, …], 計算式)`
* **特徴:**
* 複雑な数式を、より分かりやすいステップに分解できます。
* 同じ計算を何度も繰り返す必要がなくなり、パフォーマンスが向上します。
* LAMBDA関数と組み合わせることで、より高度なカスタム関数を作成できます。
* **例:** 2つの数値の合計と差を計算し、その積を求める場合。
* `=LET(a, 10, b, 5, sum_val, a+b, diff_val, a-b, sum_val*diff_val)`
* この例では、`a` に `10`、`b` に `5` を割り当て、`sum_val` に合計 `a+b`、`diff_val` に差 `a-b` を割り当て、最後に `sum_val` と `diff_val` の積を計算しています。結果は `75` です。

3. FILTER関数

FILTER関数は、指定した条件に一致する行または列を抽出する関数です。配列を返す動的配列関数の一つです。

* **構文:** `FILTER(配列, 含む, [空の場合])`
* **特徴:**
* 条件に一致するデータを動的に抽出するため、元データが変更されても結果が自動的に更新されます。
* 複数の条件を組み合わせることも可能です。
* **例:** A列に商品名、B列に価格が入力されているリストから、価格が1000円以上の商品を抽出します。
* `=FILTER(A2:B10, B2:B10>=1000, “該当なし”)`
* この例では、A2:B10の範囲から、B列の値が1000以上の行を抽出し、該当するデータがない場合は「該当なし」と表示します。

4. SORT関数

SORT関数は、指定した配列を指定した列を基準に並べ替える関数です。これも動的配列関数です。

* **構文:** `SORT(配列, 並べ替えインデックス, [並べ替え順序], [列並べ替え])`
* **特徴:**
* 元データを変更せずに、並べ替えられた結果を動的に表示できます。
* 昇順・降順の指定が可能です。
* **例:** 上記FILTER関数で抽出した商品リストを、価格の安い順に並べ替えます。
* `=SORT(FILTER(A2:B10, B2:B10>=1000, “該当なし”), 2, 1)`
* この例では、FILTER関数で抽出した結果（第1引数）を、2番目の列（価格）を昇順（1）で並べ替えています。

5. UNIQUE関数

UNIQUE関数は、指定した配列または範囲から重複しない一意の値のリストを返します。

* **構文:** `UNIQUE(配列, 列単位, [一度だけ])`
* **特徴:**
* 大量のデータから重複を取り除き、リスト化する際に非常に便利です。
* フィルタリングや集計の前処理としてよく利用されます。
* **例:** A列に重複する商品名が入力されているリストから、一意の商品名リストを作成します。
* `=UNIQUE(A2:A10)`
* この例では、A2:A10の範囲から重複する商品名を削除し、一意の商品名のみをリストアップします。

6. SEQUENCE関数

SEQUENCE関数は、指定した行数と列数で連番の配列を生成します。

* **構文:** `SEQUENCE(行, [列], [開始値], [ステップ])`
* **特徴:**
* 連番のリストを動的に生成できます。
* 他の関数と組み合わせることで、複雑なデータ構造の生成や、インデックスの動的な生成などに活用できます。
* **例:** 1から10までの連番を生成します。
* `=SEQUENCE(10)`
* この例では、10行1列の配列として1から10までの連番を生成します。

7. RANDARRAY関数

RANDARRAY関数は、指定した行数と列数でランダムな数値の配列を生成します。

* **構文:** `RANDARRAY(行, [列], [最小値], [最大値], [整数])`
* **特徴:**
* テストデータやシミュレーション用のデータを動的に生成するのに役立ちます。
* 最小値、最大値、整数の指定が可能です。
* **例:** 5行3列の、1から100までのランダムな整数を生成します。
* `=RANDARRAY(5, 3, 1, 100, TRUE)`
* この例では、5行3列の範囲に、1以上100以下のランダムな整数を生成します。

サンプルコード

ここでは、上記で解説した新関数を組み合わせた、より実践的な問題とその解決策を提示します。

問題1：特定カテゴリの商品の合計金額を計算するカスタム関数を作成する

**シナリオ:** 商品リスト（商品名、カテゴリ、単価、数量）があり、特定のカテゴリに属する商品の合計金額（単価 × 数量）を計算したい。この処理をLAMBDA関数でカスタム関数化する。

**データ例:**

| 商品名 | カテゴリ | 単価 | 数量 |
| :——- | :——- | :— | :— |
| りんご | 果物 | 150 | 10 |
| バナナ | 果物 | 100 | 20 |
| にんじん | 野菜 | 80 | 15 |
| じゃがいも | 野菜 | 70 | 25 |
| みかん | 果物 | 120 | 12 |

**解答:**

1. **名前の管理**を開き、新規作成します。
2. **名前:** `CalculateCategoryTotal`
3. **参照:** `=LAMBDA(data_range, category_to_sum, LET(
filtered_data, FILTER(data_range, INDEX(data_range, 0, 2)=category_to_sum, “”),
IF(ISERR(filtered_data), 0, SUM(INDEX(filtered_data, 0, 3)*INDEX(filtered_data, 0, 4)))
))`
* `data_range`: 商品リストの範囲（例: `A2:D6`）
* `category_to_sum`: 集計したいカテゴリ名（例: `”果物”`）
* `FILTER` 関数で指定カテゴリのデータを抽出。
* `INDEX` 関数で単価（3列目）と数量（4列目）を抽出し、掛け合わせる。
* `SUM` 関数で合計金額を計算。
* `IF(ISERR(filtered_data), 0, …)` で、該当カテゴリがない場合にエラーとならないように0を返す。

**使用例:**
セルに `=CalculateCategoryTotal(A2:D6, “果物”)` と入力すると、果物の合計金額 `4,200` が返ります。


=LAMBDA(data_range, category_to_sum, LET(
    filtered_data, FILTER(data_range, INDEX(data_range, 0, 2)=category_to_sum, ""),
    IF(ISERR(filtered_data), 0, SUM(INDEX(filtered_data, 0, 3)*INDEX(filtered_data, 0, 4)))
))

問題2：重複する顧客リストから、最新の購入履歴を持つ顧客を抽出し、その購入金額を計算する

**シナリオ:** 顧客ID、購入日、購入金額のリストがあり、顧客IDは重複している。各顧客IDごとに最新の購入日とその購入金額を抽出し、さらにその合計金額を計算したい。

**データ例:**

| 顧客ID | 購入日 | 購入金額 |
| :—– | :——— | :——- |
| C001 | 2023/01/15 | 5000 |
| C002 | 2023/02/20 | 8000 |
| C001 | 2023/03/10 | 6000 |
| C003 | 2023/01/25 | 4000 |
| C002 | 2023/04/05 | 7000 |
| C001 | 2023/02/01 | 5500 |

**解答:**

1. **最新の購入履歴を持つ顧客を抽出:**
* まず、一意の顧客IDリストを作成します: `=UNIQUE(A2:A7)`
* 次に、各顧客IDの最大購入日を求めます。`MAXIFS` 関数が便利です。
* `=MAXIFS(B2:B7, A2:A7, UNIQUE(A2:A7))` （購入日）
* さらに、その最大購入日に対応する購入金額を求めます。`XLOOKUP` 関数が強力です。
* `=XLOOKUP(MAXIFS(B2:B7, A2:A7, UNIQUE(A2:A7)), B2:B7, C2:C7, “”, 0, -1)` （購入金額 – 検索モードを完全一致、検索モードを最後から最初へ）
* `XLOOKUP` の検索モード `-1` は、検索モードを「完全一致、次候補なし、ワイルドカード文字検索」から「完全一致、次候補なし、検索モードを最後から最初へ」に変更します。
* これらを`LET`関数でまとめます。
* `=LET(
customer_ids, UNIQUE(A2:A7),
latest_purchase_dates, MAXIFS(B2:B7, A2:A7, customer_ids),
latest_purchase_amounts, XLOOKUP(latest_purchase_dates, B2:B7, C2:C7, “”, 0, -1),
HSTACK(customer_ids, latest_purchase_dates, latest_purchase_amounts)
)`
* `HSTACK` 関数で顧客ID、購入日、購入金額を横に結合します。

2. **最新購入履歴の合計金額を計算:**
* 上記で求めた `latest_purchase_amounts` を `SUM` 関数で合計します。
* `=LET(
customer_ids, UNIQUE(A2:A7),
latest_purchase_dates, MAXIFS(B2:B7, A2:A7, customer_ids),
latest_purchase_amounts, XLOOKUP(latest_purchase_dates, B2:B7, C2:C7, “”, 0, -1),
SUM(latest_purchase_amounts)
)`
* 結果は `19000` となります。


-- 最新の購入履歴を持つ顧客リストを抽出 --
=LET(
    customer_ids, UNIQUE(A2:A7),
    latest_purchase_dates, MAXIFS(B2:B7, A2:A7, customer_ids),
    latest_purchase_amounts, XLOOKUP(latest_purchase_dates, B2:B7, C2:C7, "", 0, -1),
    HSTACK(customer_ids, latest_purchase_dates, latest_purchase_amounts)
)

-- 最新購入履歴の合計金額を計算 --
=LET(
    customer_ids, UNIQUE(A2:A7),
    latest_purchase_dates, MAXIFS(B2:B7, A2:A7, customer_ids),
    latest_purchase_amounts, XLOOKUP(latest_purchase_dates, B2:B7, C2:C7, "", 0, -1),
    SUM(latest_purchase_amounts)
)

### 実務アドバイス

1. **段階的な学習:** 新関数は強力ですが、一度にすべてを習得しようとすると混乱します。まずはLAMBDA, LET, FILTER, UNIQUEあたりから始め、必要に応じて他の関数を学んでいくのがおすすめです。
2. **名前の管理の活用:** LAMBDA関数で作成したカスタム関数や、LET関数で定義した中間変数に名前を付けることで、数式の可読性が劇的に向上します。複雑な数式を記述する際には、積極的に名前の管理を活用しましょう。
3. **動的配列関数の理解:** FILTER, SORT, UNIQUEなどの動的配列関数は、結果が複数のセルに展開されます。この挙動を理解し、意図しない「スピル」エラー（他のデータに重なってしまう現象）が発生しないように、十分なスペースを確保して数式を入力することが重要です。
4. **エラー処理の重要性:** FILTER関数などでデータが見つからなかった場合や、XLOOKUP関数で値が見つからなかった場合など、エラーが発生する可能性のある箇所では、IFERROR関数やLET関数内のIF文などで適切にエラー処理を行いましょう。これにより、予期せぬエラーでブック全体が停止するのを防ぎます。
5. **パフォーマンスの考慮:** 非常に大規模なデータセットに対してこれらの新関数を使用する場合、パフォーマンスに影響が出ることがあります。特に、再帰的なLAMBDA関数や、ネストされた動的配列関数は、計算に時間がかかる可能性があります。可能であれば、LET関数で中間結果をキャッシュしたり、データ構造を見直したりすることで、パフォーマンスを改善できないか検討しましょう。
6. **バージョン互換性:** これらの新関数は、比較的新しいバージョンのExcel（Microsoft 365など）で利用可能です。古いバージョンのExcelでは利用できないため、共有する相手のExcelのバージョンに注意が必要です。

まとめ

LAMBDA関数以降の新関数群は、Excelの関数機能を飛躍的に進化させました。LAMBDA関数によるカスタム関数の作成、LET関数による数式の可読性と効率性の向上、FILTER, SORT, UNIQUEといった動的配列関数による柔軟なデータ操作は、これまでVBAやPower Queryに頼らざるを得なかった多くの処理を、Excelの標準機能だけで実現可能にしました。

スポンサーリンク
タイトルとURLをコピーしました