業務で使われる表は、たいてい同じ2種類の列を持つようになります。ひとつは何かを引いてくる列 ― 商品コードが商品名と単価に変わる列です。もうひとつは一部だけを合計する列 ― この区分だけ、この期間だけ、この金額以上だけ。実際に書かれる数式の大半は、この2つで説明がつきます。
Excel の中でならどちらも難しくありません。問題は、その表をブラウザに持っていったときに何が起きるかです。土台がただのグリッドだと、参照の突き合わせも条件付き集計もアプリケーション側のコードで書き直すことになります。
この記事では、その両方を数式のまま成立させる小さな受注明細表を ReoGrid Web で組み立てます。あわせて、このエンジンに実装されていない2つの参照関数についても正直に書きます。
数式エンジンの組み込み関数109個は Pro の機能です。無料の Lite でも四則演算・比較・セル参照は動き、依存グラフも色分けされた数式エディタも使えますが、名前付き関数は一切登録されないため
=SUM(...)は#NAME?を返します。以下のコードはすべて@reogrid/proを前提としています。
作る表
1枚のシートに2つのブロックを置きます。右側が商品マスタ（コード・商品名・単価・区分）。左側が受注明細で、入力するのはコード（A列）と数量（D列）だけ、残りの列はすべて数式です。その下に、明細を区分ごとに集計するサマリを置きます。
マスタは単なるデータです。
import { createReogrid } from '@reogrid/pro';
const grid = createReogrid({ workspace: '#grid', licenseKey: 'YOUR_LICENSE_KEY' });
const ws = grid.worksheet;
ws.suspendRender();
ws.setGridSize(14, 10); // A..J ― 既定のシートは40行しかない
const MASTER: Array<[string, string, number, string]> = [
['A-100', 'ノートPCスタンド', 4800, 'ハードウェア'],
['A-200', 'ワイヤレスマウス', 2600, 'ハードウェア'],
['B-100', 'ライセンス（1年）', 18000, 'ソフトウェア'],
['B-200', '保守プラン', 32000, 'サービス'],
['C-100', 'ケーブルセット', 900, 'ハードウェア'],
];
['コード', '商品名', '単価', '区分'].forEach((h, i) =>
ws.cell(0, 6 + i).setValue(h).setStyle({ bold: true, backgroundColor: '#e2e8f0' }));
MASTER.forEach(([code, name, price, category], r) => {
ws.cell(1 + r, 6).setValue(code);
ws.cell(1 + r, 7).setValue(name);
ws.setCellInput(1 + r, 8, String(price));
ws.cell(1 + r, 9).setValue(category);
});
ws.range('I2:I6').setFormat('¥#,##0');setGridSize には一言添える価値があります。新しいシートは40行 × 26列で、その外側にはみ出したレンジはエラーにならず黙って無視されます。既定のままのシートで60行目にマスタを書き込んでも、例外は飛ばず、ただそこに無いだけです。埋める前にサイズを決めてください。
VLOOKUP と、その2つの弱点
VLOOKUP(値, 表, 列番号, [近似]) は表の先頭列を検索し、そこから右に数えて 列番号 番目の値を返します。
ws.setCellInput(1, 1, '=VLOOKUP($A2,$G$2:$J$6,2,FALSE)'); // 商品名 → ノートPCスタンド
ws.setCellInput(1, 2, '=VLOOKUP($A2,$G$2:$J$6,4,FALSE)'); // 区分 → ハードウェアこれで動きますし、自分で管理している表なら十分に妥当です。ただし2点、時間の経過に弱いところがあります。
ひとつは列番号です。これはレンジの左端から数えた「位置」なので、数式がマスタの現在の列並びを埋め込んでしまいます。マスタに列を1本挿入すれば ― 仕入先、税区分 ― それより右を指していた VLOOKUP はすべて別のフィールドを返します。エラーは出ません。数字が静かに変わるだけです。
もうひとつは第4引数です。省略すると近似一致になります。先頭列が昇順に並んでいる前提で、検索値を超えない最大の値の行を返す、という動作です。ソートされていない商品コードに対してこれをやると、それらしく見える間違った行が返ってきます。ほぼ常に必要なのは完全一致の FALSE で、しかもこれは書き忘れられる側の引数です。
XLOOKUP
XLOOKUP は検索する列と返す列を別々のレンジとして受け取ります。ずれる「番号」がそもそも存在しません。
// XLOOKUP(検索値, 検索範囲, 返す範囲, [見つからない場合], [一致モード], [検索モード])
ws.setCellInput(1, 1, '=XLOOKUP($A2,$G$2:$G$6,$H$2:$H$6,"該当なし")');
ws.setCellInput(1, 2, '=XLOOKUP($A2,$G$2:$G$6,$J$2:$J$6,"—")');他人が編集する表では、次の3点が効いてきます。
- 返す範囲を「数える」のではなく「名指し」する。 途中に列を挿入すれば両方のレンジが一緒にずれるので、数式は「商品名の列」という意味を保ちます。
- 既定が完全一致。
一致モードは0が既定です。-1/1は見つからないとき小さい側／大きい側の近い値にフォールバックし、2でワイルドカードが有効になります。曖昧さは明示的に選ぶものになりました。 - 第4引数が「空振りの受け皿」。
見つからない場合に渡した値が、#N/Aの代わりに返ります。
検索モード もあります。1 が先頭から（既定）、-1 が末尾から、±2 がソート済み前提の二分探索です。実用上ありがたいのは -1 で、追記していく価格履歴に対して最新の行を拾えます。
金額列は、手入力した数量と引いてきた単価を掛けます。既定値を 0 にしてあるので、未知のコードは合計を壊さず単に寄与しないだけになります。
ws.setCellInput(1, 4, '=D2*XLOOKUP($A2,$G$2:$G$6,$I$2:$I$6,0)');マスタに無いコード
表の7行目は X-999 で、マスタにわざと入れていません。これは例外的なケースではなく、前期の明細を貼り付けたら2つの型番が廃番になっていた、という火曜日の話です。
いちばん安いのは if_not_found で、参照そのものに閉じています。数式が単なる参照より複雑になったら、外側で包みます。
// IFNA は #N/A だけを捕まえる ― つまり「本当に見つからなかった」場合だけ
ws.setCellInput(1, 4, '=IFNA(D2*XLOOKUP($A2,$G$2:$G$6,$I$2:$I$6),0)');
// IFERROR は #VALUE! や #DIV/0! も含めて全部飲み込む
ws.setCellInput(1, 4, '=IFERROR(D2*XLOOKUP($A2,$G$2:$G$6,$I$2:$I$6),0)');IFNA を優先してください。 IFERROR は大きすぎるハンマーで、見たかったはずの不具合まで隠します。数量欄に文字列が入って出た #VALUE! はノイズではなく情報です。失敗を握りつぶすのではなく分岐したい場合は、ISNA / ISERR / ISERROR がいずれも登録済みで、ERROR.TYPE は数値のエラーコードを返します。
一部の行だけを合計する
各明細に金額が入れば、サマリは条件付き集計そのものです。単一条件の関数は、判定する範囲を先に取ります。
// SUMIF(判定範囲, 条件, [合計範囲]) ― C列で判定し、E列を合計する
ws.setCellInput(9, 1, '=SUMIF($C$2:$C$7,$A10,$E$2:$E$7)');
ws.setCellInput(9, 2, '=COUNTIF($C$2:$C$7,$A10)');*IFS 系は順序が逆で、集計する範囲が先、その後ろに「範囲・条件」の組が続きます。
// SUMIFS(合計範囲, 判定範囲1, 条件1, ...) ― ハードウェアかつ数量5以上
ws.setCellInput(13, 1, '=SUMIFS($E$2:$E$7,$C$2:$C$7,"ハードウェア",$D$2:$D$7,">=5")');
ws.setCellInput(13, 2, '=COUNTIFS($C$2:$C$7,"ハードウェア",$D$2:$D$7,">=5")');この SUMIF と SUMIFS の引数順の反転は Excel 由来の仕様であって当ライブラリ独自のものではありませんが、理由が分からないまま 0 が返る原因の第1位です。AVERAGEIF と AVERAGEIFS も同じ分かれ方をします。
criteria（条件）の記法
条件は、値そのものか、演算子を文字列の中に含めた形で書きます。
| 条件 | 一致するもの |
|---|---|
5 | 5 と等しい |
"ハードウェア" | その文字列と等しい（英字は大文字小文字を区別しない） |
">5" | 5 より大きい |
">=10" | 10 以上 |
"<>0" | 0 でない |
"a*" | a で始まる文字列 |
"?at" | 任意の1文字 + at |
比較演算子が文字列の内側にあるので、実行時に条件を組み立てるときは単なる連結で済みます。数式内なら '">=" & F1'、JavaScript 側なら普通のテンプレートリテラルです。
JavaScript で計算してしまわない理由
もちろん、これらをすべてアプリケーション側で計算し、確定した数値をセルに書き込むこともできます。違いが出るのは最初の1回の編集です。
依存関係は数式の解析時に記録されるので、あるセルを変更すると、その下流だけが再計算されます。
ws.cell('I4').value = '19800'; // ライセンス: ¥18,000 → ¥19,800
// E3 が再計算され、サマリのソフトウェア行が続き、合計が続くマスタの単価を1セル直せば、明細の金額も区分別小計も総合計も、その順に動きます。自前の再描画パスは要りません。利用者がマスタを直接編集したときも同じで、テーブルではなくスプレッドシートを出す意味はここにあります。
通常の入力経路を通らない一括ロードのあとは、グラフを1度作り直してください。
ws.rebuildFormulas();そして数式は、往復に耐えるものでもあります。このシートを ws.saveAsXlsx() で書き出せば、受け取った相手の Excel では XLOOKUP や SUMIFS が生きた数式として開きます。計算済みの値を書き込んでいた場合に届くのは、死んだ数値の並びです。
無いもの
参照系のうち INDIRECT と OFFSET は未実装です。どちらも評価時に参照を組み立てる関数で、つまり実行するまで「その数式が何を読むか」を依存グラフが知り得ません ― Excel でいう揮発性セルです。これは関数を1つ足す話ではなくグラフ側の変更なので、実装済みではなく予定という段階にあります。
実務上の置き換えはこうなります。
INDIRECTの用途はたいてい動的なシート名・レンジ名です。数式の文字列を JavaScript 側で組み立ててsetCellInputで入れてください。 シートの外側には本物のプログラミング言語があります。INDIRECTはもともと、それが無い環境での回避策でした。OFFSETの用途はたいてい移動する窓です。INDEXは登録済みで揮発性を必要とせず、同じことを表現できます ― 行がずれる窓なら=INDEX($E$2:$E$100,$F$1)です。
もう少し小さい穴もひとつ。単一セルの ROW(A5) は #VALUE! を返します。引数なしの ROW() / COLUMN() と、レンジを渡す形は動くので、行数を数えるなら ROWS($A$2:$A$7) が安全な書き方です。
はっきり書いておくと、ReoGrid Web には独自の数式関数を登録する公開 API はありません。カスタム関数は ReoGrid .NET 側の機能です。Web での拡張点は逆向き ― JavaScript からセルを読み書きする方向で、そちら側には既に好きなライブラリが揃っています。
まとめ
冒頭に挙げた2種類の列は、ブラウザ上の表が「スプレッドシート」なのか「Excel の皮をかぶったテーブル」なのかを分けるものです。参照はコードに意味を与え、条件付き集計は100行に意味を与えます。どちらもデータに貼り付いたセルの中にあって、誰かが編集した瞬間に再計算されるべきもので ― 実行を忘れないよう気をつける useEffect の中にあるべきものではありません。
まずは数式エンジンのドキュメントで109関数の一覧と条件記法の詳細を確認してください。この先は、クロスシート数式でマスタを本来あるべき別シートへ移し、集計を一覧ではなくクロス集計にしたくなったらピボットテーブルへ、そもそも未知の商品コードを打たせないためには入力規則へ進むのが自然な順序です。