定期的に弄る必要に迫られる表計算

シート作り不定期

まずExcelじゃなくてLibreOfficeなので念のため。表計算の関数部分なので、たぶんExcelでも同じだとは思う。

「このキャラは10まで消化」「こいつは6まで」のような収支表、つまりスタート地点固定かつ、途中抜けの無いタイプの総和（の総和）を出したかったのだが、
例えば「=SUM(R2C3:R3C[R[-1]C])」みたいな変数入れ子表記で「R1C1表記なら相対参照で余裕だろうフフン！」などと高を括っていたところ、見事に返り討ちに合う。どうもRC相対参照の[]の中身をRC相対参照にする、みたいな汚い書き方は駄目らしい。

さてどうしよう、と思って探していたら、OFFSET(基準点セル, 進む行数, 進む列数)というピッタリの関数を発見した。セル参照そのものを返す関数のようなので、これだけで全てが解決するに至る。

先例で言えば「=SUM(R2C3:OFFSET(R2C3,1,R[-1]C-3)」みたいな書き方で良いはず。

でも何か、この流れ見覚えあるような・・・。（多分同じことを、忘れては発見し覚え、また忘れる、ということを数年サイクルで何回もやっている）

まあ、結局は冒頭の前提を破壊し、「途中抜け」アリでも計算できるようにシートを改造することになるのだが。

書式とか色々弄る

○✕で成否を表示しつつ、セルの中身は1か0みたいな条件付き書式を作りたかったのだが、どうも無理らしく途方に暮れていると、今度は書式設定のフォーマットを見て閃く。

曰く書式のコードは「(正数の時);(負数の時);(ゼロの時);(テキスト)」という形式になっているらしいので、
「"○"; ;"✕";」とかいうズルい感じの書式を作ったら、ちゃんと○✕だけ表示してくれるようになった。

で、どうしたいのかと言えば、セルの実態である1 or 0と、そのセルの収入とを掛け算して、IFのBOOLEANのように使いたかったわけですね。

こうなるとwhile(){}みたいなループが無いと実装できないのだが、どうもREDUCEやLAMBDAとかいう新しい関数はLibreには来てないようなので、結局は全件手入力とかいうアナログでお茶を濁したりなど。

これじゃ達成できてもつまらんので、Excel Onlineで実際に入力して試してみたが、書式弄れず、RC表記への変更が出来そうにないなどお話にならない。しかし唯一REDUCE、LAMBDA、MAP関数が使えるという利点を活かし、
「=SUM(MAP(C4:Q4,$C$3:$Q$3,LAMBDA(x,y,x*y)))」
みたいな数式で「積の総和」を得ることに成功した。
どうもMAPというのは「配列」を作るようなので、所謂「範囲」と同等の物が生成されているらしい（数値の単位的な意味で）。なので、一度「範囲」を生成してから、総和を得るSUMにそのまま投げ込んでやれば、範囲の総和を得るのと同じように計算してくれるようだ。

（最初は「MAPをREDUCEに投げ込み繰り返し加算させる」とかいう無駄なことをしていたが、範囲と同じならSUMで良いじゃん、と気付きこうなった）

この後調べたら、SUMPRODUCT関数とかいう、まさにこの計算をするためだけの関数があったので、割と徒労であった。
まあ、再帰やら繰り返しやらの手段を得たという意味では、無駄になるという訳でもないが。

できたのでヨシ。