スプレッドシートにウィンドウ関数を追加する

Adding window functions to a spreadsheet

Running totals, cumulative balances, and nearest matches: formulas between rows that stay correct.

多くの人が初期の頃に書いたであろう数式です。ExcelやGoogle Sheetsのような従来の表計算ソフトに、1行に1つのトランザクションを記録し、ランニングバランスを維持します。

［画像: Google Docs screenshot showing a basic running balance formula: =C3+B4］

この数式を下にコピーすると、機能します。各残高は、その上の残高に自身の行の金額を加えたものです。

ここで1月11日のレシートを見つけて、3行目と4行目の間に挿入したとします。新しい行には数式がないため、それを入力するか、上からコピーします。しかし、その下の残高は=C3+B5となります。行がシフトしたとき、参照は同じセルを指すように調整されましたが、そのセルはもはや上の行ではありませんでした。新しいトランザクションは、エラーを示すことなくスキップされてしまいます。残高は、その行から間違ったままになります。

代わりに1行を削除すると、その下の数式は#REF!となり、それ以降のすべての残高も同様になります。

［画像: Google Docs screenshot showing a reference error after a row is deleted.］

これらはバグではありません。従来の表計算ソフトにとって、「前のトランザクション」とは「上のセル」を意味します。誰も何も並べ替えない限り、この二つは一致します。

Surely there’s a better way?

コツを知っていれば、より良い方法があります。Vertex42は、編集に耐えるように「上のセル」を指すためにOFFSET()や相対的な名前付き範囲を提案しています。Exceljetは、Excel 365で列全体に単一のSCAN()数式を提案しています。

データベース基盤を持つスプレッドシートライクなツールはどうでしょうか？ AirtableやBaserowでは、数式はそれが含まれるレコードしか参照できません。NocoDBやNotionデータベースも同様です。AirtableやBaserowでランニングトータルの方法を尋ねる人々は、スクリプトや、手動で各レコードを前のレコードにリンクするように指示されます。

これらのツールは、「上の行」を全く持たないことで問題を回避しています。しかし、その必要性は存在します：ランニングバランス、患者の最後の訪問からの日数、各出荷後の在庫などです。

SQLにはウィンドウ関数という答えがあります。前の行と次の行のためのLAGとLEAD、またはランニングトータルのためのSUM(amount) OVER (PARTITION BY account ORDER BY date)があります。これらも機能しますが、クエリを書く開発者向けの高度なツールであり、テーブルで作業する人々向けではありません。

The actual better way: PREVIOUS()

［画像: Decorative image of geese flying in a line over water.］

Running totals, or flying totals? Photo by Sherlock Jin.

「前の行」で本当に意味しているのは、「日付による前のトランザクション」です。ですから、そのように正確に言いましょう。Gristでは、ランニングバランス、または任意の累積合計は以下のようになります。

prev_balance = PREVIOUS(rec, order_by="Date").Running_Balance or 0
prev_balance + $Amount

PREVIOUS(rec, order_by="Date")はテーブルを「Date」でソートし、現在のレコード（rec）を見つけ、前のレコードを返します。それはレコード全体（セルだけではない）なので、.Running_Balanceはそのレコードの残高を取得します。最初のトランザクションには前のものがないため、その残高は空になり、or 0がそれを処理します。

［画像: Grist screenshot showing prev_balance formula in Running Balance column.］

順序はビューではなく、数式の中にあります。どこにでもトランザクションを挿入すると、日付順に配置されます。トランザクションを削除すると、残高がその周りに閉じます。テーブルを「Amount」でソートしたり、フィルタリングしたりしても、各レコードは日付ごとの残高を保持し続けます。なぜなら、それが要求したことだからです。

クレジットカード口座には複数のカード会員がいる場合があり、それぞれが独自の残高を望むかもしれません。データを人ごとにテーブルに分割することを考えるかもしれません。そうしないでください。1つのテーブルを維持し、具体的に指定してください。

prev_balance = PREVIOUS(rec, group_by="Card_Member", order_by="Date").Balance or 0
prev_balance + $Amount

PREVIOUS()は今や、同じカード会員のトランザクションの中で、日付による前のトランザクションを見つけます。

そして、順序は数式（order_by）内にあるため、複数のランニングトータルを並べて持つことを妨げるものは何もありません：口座全体のもの、カード会員ごとのもの、毎月リセットされるものなどです。各列は独自のカウントを保持します。カード会員にリンクされ、日付でソートされたビューを追加すると、カード会員を選択してそのトランザクションを表示でき、残高が行ごとに加算されていきます。

［画像: Grist screenshot showing a linked view filtering a specific member's transactions with a running balance.］

PREVIOUS()には2つの兄弟がいます。NEXT()は逆方向を見ます。RANK()は各レコードに順序内での位置を与えます。例えば、RANK(rec, order_by="-Amount") <= 10は最も大きい10個のトランザクションをマークします。ヘルプ記事では、これら3つすべてについて詳しく説明しています。

Nearest match, latest record, and effective rate

ルックアップが順序を認識すると、「前」はあなたが尋ねることができることの一つにすぎません。

人々の給与率が時間とともに変化し、その履歴を記録しているとしましょう：Berthaは2019年8月30日から時給110ドル、2020年3月24日から125ドルを稼ぎます。2020年2月の給与エントリーは、その日付で有効だったレート、つまりその日付以前に開始された最新のレートを必要とします。

rates = Rates.lookupRecords(Person=$Person, order_by="Rate_Start")
rates.find.le($Date).Hourly_Rate

lookupRecords()はBerthaのレートを、開始日でソートして取得します。find.le()は、給与日付以下（leはless than or equal to）の最後のレートを見つけます。

このことのどれも日付に特化したものではありません。コミッションティアも同様です：5,000ドルを販売すると7%、10,000ドルを販売すると10%などです。

rates = Commission_Rates.lookupRecords(order_by="Sales_Target")
rates.find.le($Sales_Amount).Commission_Rate

［画像: Grist screenshot showing salespeople with applicable commission rates, as well as a separate table showing all commission rates by sales target.］

ExcelやGoogle Sheetsのユーザーはこれを近似一致として知っています：最後の引数にTRUEを指定したVLOOKUP()です。Microsoft自身のドキュメントによると、最初の列はソートされている必要があり、そうでない場合、「返される値は予期しないものになる可能性があります。」エラーではなく、異なる結果になります。Gristでは、順序が数式の一部です。画面上の表示方法に関わらず、ティアを自分でソートします。

これら2行は、日常的な最も近い一致（「近似一致」とも呼ばれる）の質問の多くに答えます。

• 特定の日付で有効な給与率
• 販売額が該当するコミッションティア
• 小包の重量に対する配送料
• 所得に対する税率
• 購入日の為替レート
• 注文サイズに対する数量割引

そして最も単純な質問（グループ内の最初または最後のレコード）については、find()さえ必要ありません。顧客の最新の注文はこちらです。

Orders.lookupOne(Customer=$id, order_by="-Date")

マイナス記号は降順を意味するため、最初の一致が最新です。

The need for speed

Gristの数式はPythonなので、すべてを手書きで書くこともできます：レコードを取得し、それらをループ処理し、正しいものを選びます。そのコストを確認する必要があります。

スキャンによって行われるランニングトータル、SUM(r.Amount for r in Transactions.all if r.Date <= $Date)は、すべてのレコードに対してテーブル全体を見ます。1,000件のトランザクションでは、100万ステップになります。20,000件では、4億ステップです。

20,000件のトランザクションのテストテーブルでは、PREVIOUS()は数秒でランニングバランスの列全体を計算しました。スキャンバージョンは5分後も実行中でした。

順序付きルックアップはスキャンしません。レコードをソートされた状態に保ち、バイナリ検索で要求されたものを見つけます。これは高速な方法です。

All caught up

1月11日のレシートを追加してランニングトータルが壊れたことを覚えていますか？ Gristでは、どこにでも追加できます。日付順に配置され、それ以降のすべての残高が再計算されます。新しい行の残高は正しく、各カード会員の残高も同様です。数式を下にコピーしたり、隣接する行を確認したり、エラーを修正したりする必要はありません。

それが、意味するところを言う数式から得られるものです：それらは自己更新し、常に正しいです。

---

詳細については、ヘルプ記事Running totals, PREVIOUS, and ordered lookupsをご覧ください。これらの例のウォークスルーについては、ウェビナーAdvanced formulas with ordered lookupsをご覧ください。

［画像:］

Dmitry Sagalovskiy, Co-founder and Head of Product