内容
現時点では、Microsoft Excel には約 XNUMX のワークシート関数が [関数ウィザード] ウィンドウから使用できます – ボタン fx 数式バーで。 これは非常に優れたセットですが、遅かれ早かれほとんどすべてのユーザーが、このリストに必要な機能が含まれていないという状況に遭遇します。これは単に Excel にないためです。
これまで、この問題を解決する唯一の方法はマクロでした。つまり、Visual Basic で独自のユーザー定義関数 (UDF = User Defined Function) を作成することでした。これには適切なプログラミング スキルが必要であり、時にはまったく簡単ではありませんでした。 しかし、Office 365 の最新の更新により、状況は好転し、Excel に特別な「ラッパー」機能が追加されました。 ラムダ. その助けを借りて、独自の関数を作成するタスクが簡単かつ美しく解決されるようになりました。
次の例で、その使用の原則を見てみましょう。
ご存じのとおり、Excel には、特定の日付の日、月、週、年の数を判断できる日付解析関数がいくつかあります。 でも、なぜか必要になることも多い四半期の数字を決める関数がありませんよね？ この欠点を修正して作成しましょう ラムダ この問題を解決する独自の新機能。
ステップ 1. 式を書く
通常の方法で手動で必要なものを計算する数式をシート セルに書き込むという事実から始めましょう。 四半期番号の場合、これはたとえば次のように行うことができます。
ステップ 2. LAMBDA でのまとめとテスト
新しい LAMBDA 関数を適用し、式をラップします。 関数の構文は次のとおりです。
=ラムダ(変数1; 変数2; … 変数N ; 表現)
ここで、XNUMX つ以上の変数の名前が最初にリストされ、最後の引数は常にそれらを使用する数式または計算式です。 変数名は、セル アドレスのように見えたり、ドットを含んではいけません。
この場合、変数は XNUMX つだけです。つまり、四半期数を計算する日付です。 その変数を d と呼びましょう。 次に、式を関数でラップします ラムダ 元のセル A2 のアドレスを架空の変数名に置き換えると、次のようになります。
このような変換の後、セル A2 の元の日付が転送されないため、数式 (実際には正しい!) でエラーが発生し始めたことに注意してください。 テストと信頼のために、関数の後に引数を追加して引数を渡すことができます ラムダ 括弧内：
ステップ 3. 名前を作成する
次に、簡単で楽しい部分です。 我々が開きます 名前管理者 タブ 式 (数式 — ネーム マネージャー) ボタンで新しい名前を作成します 創造する （作成）. future 関数の名前を考えて入力します (たとえば、 ノムクヴァルタラ）、およびフィールドで リンク （参照） 数式バーから慎重にコピーして、関数を貼り付けます ラムダ、最後の引数なしのみ (A2):
すべての。 をクリックした後 OK 作成された関数は、このブックの任意のシートの任意のセルで使用できます。
他の本での使用
LAMBDA と動的配列
関数で作成されたカスタム関数 ラムダ 新しい動的配列とその関数 (フィルタ, ユニク, GRADE) が 2020 年に Microsoft Excel に追加されました。
XNUMX つのリストを比較し、それらの違い (最初のリストの要素で XNUMX 番目のリストにない要素) を返す新しいユーザー定義関数を作成したいとします。 ライフワークですね。 以前は、このためにどちらかの関数を使用していました VPR （VLOOKUP）、またはピボットテーブル、または Power Query クエリ。 これで、XNUMX つの式で実行できます。
英語版では次のようになります。
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
ここで関数 COUNTIF XNUMX 番目のリストの最初のリストの各要素の出現回数をカウントし、次に関数 フィルタ これらのオカレンスがなかった人だけを選択します。 この構造をラップすることで ラムダ それに基づいて名前付きの名前付き範囲を作成します。たとえば、 検索分布 – 動的配列の形式で XNUMX つのリストを比較した結果を返す便利な関数を取得します。
ソース データが通常のものではなく、「スマート」なテーブルである場合、関数は問題なく処理します。
もう XNUMX つの例は、テキストを XML に変換して動的に分割し、最近解析した FILTER.XML 関数を使用してセルごとに解析することです。 この複雑な式を毎回手動で再現しないようにするために、LAMBDA でラップし、それに基づいてダイナミック レンジを作成する方が簡単です。つまり、新しいコンパクトで便利な関数を作成して、名前を付けます。たとえば、RAZDTEXT:
この関数の最初の引数はソース テキストを含むセルで、XNUMX 番目の引数は区切り文字であり、水平方向の動的配列の形式で結果を返します。 関数コードは次のようになります。
=ラムダ(t;d; TRANSPOSE(FILTER.XML(“
例のリストは無限にあります。同じ長くて面倒な式を頻繁に入力しなければならない状況では、LAMBDA 関数を使用すると作業が大幅に簡単になります。
文字の再帰列挙
これまでのすべての例では、LAMBDA 関数の最も明白な側面を XNUMX つだけ示しました。長い数式をラップして入力を簡素化するための「ラッパー」としての使用です。 実際、LAMBDA には、それをほぼ完全なプログラミング言語に変える、より深い別の側面があります。
実際、LAMBDA 関数の基本的に重要な機能は、それらを実装する機能です。 再帰 – 計算の過程で関数がそれ自体を呼び出すときの計算のロジック。 習慣からすると不気味に聞こえるかもしれませんが、プログラミングでは再帰はよくあることです。 Visual Basic のマクロでも実装できるようになり、ご覧のように Excel に実装されました。 実際の例でこの手法を理解しようとしましょう。
ソーステキストから指定されたすべての文字を削除するユーザー定義関数を作成したいとします。 このような関数の有用性については、証明する必要はないと思います。散らかった入力データをその助けを借りてクリアするのは非常に便利ですよね?
ただし、前の非再帰的な例と比較すると、XNUMX つの困難が待ち構えています。
- コードの記述を開始する前に、関数の名前を考え出す必要があります。この名前は、関数自体を呼び出すために既に使用されているためです。
- そのような再帰関数をセルに入力し、(前に行ったように) LAMBDA の後に括弧で引数を指定してデバッグすることは機能しません。 すぐに「ゼロから」関数を作成する必要があります 名前管理者 (ネームマネージャー).
たとえば、CLEAN という関数を呼び出して、XNUMX つの引数を持たせます。消去するテキストと、テキスト文字列として除外される文字のリストです。
前に行ったように、タブで作成しましょう 式 в ネームマネージャー 名前付き範囲、CLEARという名前を付けてフィールドに入力します レンジ 次の構造：
=LAMBDA(t;d;IF(d=””;t;CLEAR(SUBSTITUTE(t;LEFT(d);””);MID(d;2;255)))
ここで、変数 t はクリアされる元のテキストであり、d は削除される文字のリストです。
それはすべて次のように機能します。
イテレーション1
ご想像のとおり、フラグメント SUBSTITUTE(t;LEFT(d);””) は、ソース テキスト t で削除されるセット d の左の文字の最初の文字を空のテキスト文字列に置き換えます。つまり、「あ」。 中間結果として、次のようになります。
Vsh zkz n 125ルーブル。
イテレーション2
次に、関数はそれ自体を呼び出し、入力 (最初の引数) として前のステップでクリーニングした後に残ったものを受け取ります。125 番目の引数は、最初の文字ではなく XNUMX 番目の文字から始まる除外文字の文字列です。つまり、「BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. 、」頭文字の「A」なし – これは MID 関数によって行われます。 前と同様に、関数は残りの文字 (B) の左から最初の文字を取得し、それに指定されたテキスト (Zkz n XNUMX ルーブル) でそれを空の文字列に置き換えます。中間結果として次のようになります。
125ルー。
イテレーション3
関数は再び自分自身を呼び出し、最初の引数として、前の繰り返しでクリアされるテキストの残りを受け取ります (Bsh zkz n 125 ru.)。そして、XNUMX 番目の引数として、XNUMX 文字だけ切り捨てられた除外文字のセットを受け取ります。左は「VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.」で、頭文字の「B」はありません。 次に、このセットから左から最初の文字 (B) を再度取得し、テキストから削除します。次のようになります。
sh zkz n 125 ru。
などなど - アイデアが得られることを願っています。 反復ごとに、削除される文字のリストは左側で切り捨てられ、セットから次の文字を検索して void に置き換えます。
すべての文字がなくなったら、ループを終了する必要があります。この役割は、関数によって実行されるだけです。 IF （IF）、私たちのデザインが包まれています。 削除する文字が残っていない場合 (d="")、関数はそれ自体を呼び出すのではなく、単にクリアするテキスト (変数 t) を最終的な形式で返す必要があります。
セルの再帰的反復
同様に、特定の範囲内のセルの再帰的列挙を実装できます。 という名前のラムダ関数を作成したいとします。 交換品リスト 与えられた参照リストに従ってソーステキストのフラグメントを大規模に置換します。 結果は次のようになります。
それらの。 私たちの機能で 交換品リスト 次の XNUMX つの引数があります。
- 処理するテキストを含むセル (ソース アドレス)
- ルックアップから検索する値を持つ列の最初のセル
- ルックアップからの置換値を持つ列の最初のセル
関数はディレクトリの上から下に移動し、左の列から順番にすべてのオプションを置き換えます 見つけるには 右の列から対応するオプションへ 代替. これは、次の再帰ラムダ関数で実装できます。
各反復でのシフト ダウンは、標準の Excel 関数によって実装されます。 廃棄 （オフセット）この場合、元の範囲、行シフト (1)、列シフト (0) の XNUMX つの引数があります。
ディレクトリの終わり (n = “”) に到達したらすぐに、再帰を終了する必要があります。自分自身の呼び出しを停止し、ソース テキスト変数 t のすべての置換後に蓄積されたものを表示します。
それで全部です。 複雑なマクロや Power Query クエリは必要ありません。タスク全体が XNUMX つの関数で解決されます。
- Excel の新しい動的配列関数の使用方法: FILTER、SORT、UNIC
- SUBSTITUTE 関数によるテキストの置換とクリーンアップ
- VBA でのマクロとユーザー定義関数 (UDF) の作成