どうも、きいちろーです。

今回はSpreadSheetやExcelで、元の紹介者が誰なのかがわかるという関数を紹介します。

MLMなどのネットワークビジネスや紹介ビジネス（リファーラルマーケティング）などで「この人はもともと誰の紹介だっけ？」ということがあり、その大元の紹介者の名前を瞬時に出すという関数です。

顧客名とスポンサーの欄がついているシートで作成できます。

A列は顧客でB列はその紹介者としたリストがあり、D1セルに顧客名を入力するとE1セルに紹介元の名前が自動で出てきます。

つまり関数はE1セルに入っており、例えばD1セルにJunkoを入れるとE1セルには紹介元のTesが表示されます。

今回はそのやり方を載せます。

再帰的な関数

ここでは再帰的な関数を利用します。

再帰的な関数というのは、関数の中に同じ関数を呼び出して、何度も何度も同じ関数を繰り返し呼び続け、最終的に最も深いところについたらその結果を表示するというものです。

関数の中に関数を呼ぶというとIF関数のかっこの中にまたIF関数を組み込むという入れ子のやり方を思い浮かべるかもですが、入れ子とは全く違います。

入れ子の場合、組み込んだ関数の数だけ実行することになりますが、再帰的な関数は答えを見つけるまで2回でも100回でも自動で繰り返してくれます。

名前付き関数

ではE1セルでその関数をお見せすると、=ROOT_PERSON(D1) と書かれています。

ROOT_PERSON は私が作ったオリジナルの関数です。

SpreadSheetにはオリジナル関数が作れるよう「名前付き関数」という機能がついてますのでそれを使います。（Excelにも同じような機能があります）。

SpreadSheetのメニューの「データ」から「名前付き関数」を選択するとできます。

ここではROOT_PERSON(person)という関数があります。

ROOT_PERSON 関数の中身はこうなっています。

名前付き関数の詳細にはオリジナル関数の名前を付けます。名前の付け方に気を付けて好きな名前を付けてください。

引数のプレースホルダには引数となる文字を入れます。引数とは関数に入れるための変数で、SUM関数でいうとSUM(引数)とかっこの中が引数となります。ここではpersonとしてますが名前は好きに付けてOKです。

数式の定義には関数の実態が入りますのでここが重要になります。

=if(vlookup(person,'シート1'!$A:$B,2,false)="",person,ROOT_PERSON(vlookup(person,'シート1'!$A:$B,2,false)))

ここではIF関数とVLOOKUP関数とROOT_PERSON関数を使っていますが、ポイントはROOT_PERSON関数の中にまたROOT_PERSON関数を使っていることと、その引数にVLOOKUP関数を使って紹介者を引数にしていることであり、これが再帰的な状態を作ることになります。

これだとよくわからないと思うので、一つ一つ解説します。

関数の定義と再帰的な関数

まず初めに、IF関数の最初の部分を見てみます。

=if(vlookup(person,'シート1'!$A:$B,2,false)="",person,…

IF関数の条件式としてVLOOKUP関数を使っており、VLOOKUPの結果が空白だった場合は真の処理をするというものです。

VLOOKUP関数は顧客の紹介者を調べるために使います。引数をperson（顧客、紹介された人）とし、対象のリストはシート1のA列とB列の2列とし、抽出するデータとなる2は2番目の列のことでB列（Sponsor、紹介した人の列）となり、falseは固定値として記入します。

このVLOOKUP関数の結果が空白だった場合は真偽値の真となり、真の処理としてpersonが返されることになり、つまり紹介者（Sponsor）がいない場合はその人が元の紹介者となります。

次にIF関数の真偽の結果の、偽の部分を解説します。

…,ROOT_PERSON(vlookup(person,'シート1'!$A:$B,2,false)))

偽の場合はまたROOT_PERSON関数を使って処理をすることになります。

この中の引数にVLOOKUP関数を使ってpersonの紹介者（Sponsor）を入れることで、再度ROOT_PERSON関数を使って紹介者を検索するので、これが再帰的な関数となります。

ROOT_PERSON関数を最初から言うと、まずIF関数で顧客（name）の紹介者（Sponsor）が空白かどうかをVLOOKUP関数で調べ、空白の場合はその顧客が元の紹介者として表示され、そうでない場合はROOT_PERSON関数に今度は顧客の紹介者を入れて処理することになり、またその紹介者の紹介者が空白かどうかを調べ、空白でない場合はまたROOT_PERSON関数で紹介者の紹介者を入れて処理をし、そうやって紹介者が空白になるまで延々とROOT_PERSON関数を呼び出していくというわけです。

関数の使い方

作成されたROOT_PERSON関数を使うときは普通の関数と一緒でイコールで初めてかっこの中に参照したいセルを指定します。

E1セルの中身はこちらです。

=ROOT_PERSON(D1)

D1セルには紹介された人となり、その結果がもともとの紹介者となります。

まとめ

元の紹介者を顧客リストから調べるには再帰的な関数を作るのがよく、そのためには名前付き関数でオリジナルの関数を作り、それを再帰的に延々と自関数を呼び出す仕組みにすると可能となります。

通常このような再帰的な関数はプログラム（GASやVBAなど）では作られるものですが、これをSpreadSheetの関数で作ろうと思ったのはLAMBDA（ラムダ）関数でできないかと思ったからです。

LAMBDA関数はオリジナル関数を作るための関数なのですが、名前がつかない無名関数なので利用できず、結局名前付き関数の機能を使って利用したところできたのでシェアに至った次第です。

他にも、紹介者をツリー構造で知りたい場合があると思いますが、その場合はもうプログラムで作るか、VLOOKUP関数を多用して作るといいと思います。

以上です。