脳log[20220402]

2022年04月02日 (土) Excel 2007 日記 (前回>20220324)。テーブルと名前付き範囲の関係。テーブルとオートコレクトの関係。「Excelでテーブルに数式をセットする際の注意点 | ∞ワークス」 ならばテーブルとは？■テーブルの列にサブクエリの結果を付け加えたい。たとえばキーと日付のペアが記録されているテーブルがあったとして、キーを追加したときにキーに対応する前回の日付を表示する第３の列を用意したい。このテーブルは待ち行列なのであって、適切なインターバルを空けるために前回の日付を参照してフィルタリングするために、同じテーブルを参照する再帰的なサブクエリを発行して列を付け加えたい。VLOOKUP では最初に見つかった行の値になる。DMAX では CRITERIA 引数部分に構造化参照が使えると書いていない。集計表としてピボットテーブルを作成しておいて VLOOKUP でピックアップしようとした。「ピボットテーブルとVLOOKUP関数を組み合わせて使う【Excelの応用】 | Howpon[ハウポン]」ピボットテーブル名ってテーブル名と互換性があるような見た目をしてるけど管理された名前付き範囲ではないのね。シート名とセルの絶対参照を検索範囲にして VLOOKUP しないといけない。実は日付だけでなく文字列フィールドも集計の対象のひとつなのであって何か書いてあれば駄目フラグとして扱うつもりなのだけど、ピボットテーブルの集計関数(最大値)は最初に数値化してしまうらしく文字列ではなく 0 が集計されるのだった。ピボットテーブルは名前の扱いも集計関数も不都合なので結局 Microsoft Query を通して集計表を作成しておくことにした。SQL を直接編集すれば GROUP BY も MAX 関数も使えるし、文字列フィールドの最大値はきちんと文字列が返ってきた。この SQL はデータソースが解釈、実行するらしいのだけど、ソースが TSV ファイルや Excel ブックの場合は Microsoft {Text|Excel} Driver が実行しているのかどうか。フォーマットに関する情報しかない。「Text File Format (Text File Driver) - Open Database Connectivity (ODBC) | Microsoft Docs」■どんどん深みにはまっている感覚がある。何がいけないって、まずフォントや色や文字の配置といった時間が余ったときの仕上げ行程にふさわしい操作がありとあらゆる機会に目に入り目当ての操作を隠し答えのない脇道へと誘惑する。次に自由度の高さ。制約が乏しくありとあらゆる部分でアドホックに手を入れてデータや構造を壊せてしまう。そんな方眼紙や自由帳のような自由はいらない。■複合グラフ。新しいバージョンでのような導線がないだけであって Excel 2007 でも作れる。「Excel 2007 / 2010 / 2013 / 2016 で複合グラフを作る｜クリエアナブキのちょこテク」 データ系列の書式設定を自力で見つけることはできなかったよ。■GetPivotData 関数の存在に気がついた(オプション項目で目にしていたけど関数名だと思わなかった)。列番号ではなく列の名前と値を使って目当てのセルを特定できるのが特長なのかな。セルを特定するのに行と列の２次元とは限らないところが難しい。基本的には総計が返る。フィールド名と値のペアを引数に追加するごとにより限定された小計が返る、という感じ。要するにこうだ。最初に集計項目を選び、追加のパラメータでいくつか属性を絞り、積集合部分の集計値が返る。このクエリに高速に答えられるのがいわゆる Wavelet ～ というデータ構造なのだろうか。クエリごとにインデックスを用意するならデータベースでおなじみだという BTree でいいんだろうけど、Excel はクエリがプログラマブルだから予め個々のインデックスを用意しておけないと思う。専用の関数だけど名前で対象のピボットテーブルが特定できるわけではないらしくやっぱり番地で参照しないといけない。しかし目当ての集計値を特定するのには番地を必要とせずピボットテーブルの表示状態に左右されないところが GetPivotData が必要とされた理由なのかな。■INDIRECT 関数がおもしろそう(どんな悪さができるかという意味で)。eval です。■複数のセルの値を条件にして VLOOKUP する方法。検索していくつか見つかったページはどれもセルの内容を連結した検索用の列を追加する方法だった。予想はしてた。■複数セルに一括入力。選択、入力して Ctrl+Enter。■予めソートしておくから VLOOKUP に二分探索で完全一致検索をしてほしい。TRUE(lower_bound)/FALSE(線形探索) の二択では足りない。■Microsoft Query はテーブルを認識していない？ オプションでテーブルとシステムテーブルの両方ともにチェックを入れるとシートやクエリの名前が出てくるけどテーブル名は含まれていない。あと外部プログラムだからブックを保存しないとクエリ結果が古いブックに基づいたものになってしまう。やっぱり新しい Excel で Power Query が使いたいんだよなあ。「LOOKUP の結果を表として扱いたい。俺が書きたいのは SQL の SELECT 文だ。特定のセルを左上の頂点とする複数行複数列の範囲に収まるように、クエリ結果を上書きまたは下に押し出し挿入または右に押し出し挿入したい」と書いていたものもスピル(覆水盆に返らずの Spill)がそれっぽいらしいし。■最初は VLOOKUP が良さそうに見えてもいずれ必ず INDEX+MATCH に置き換えられる運命。検索キーより右側にある列の値しか拾えないとか、降順のデータから効率良く検索できないとかの、VLOOKUP の制約が表データと適合しなくなったらそうする他ない。