2017-12-21 ,

org-mode スプレッドシート(表計算)での参照の書き方まとめ

org-modeで表計算が出来るということはよくご存じかと思いますが、細かいところがよく分からないという方も多いのではないでしょうか。特にフィールド(いわゆるセル)を式内から参照する書き方は広く使われている表計算ソフトとは異なるのでなかなか覚えられない、普段使うところだけ覚えてるということも多いと思います。なので今回はorg-modeのスプレッドシート機能の内、「参照」に的を絞ってまとめてみました。

参考: Org Mode マニュアル 3.5.1 参照 References (The Org Manual)

表内のフィールドや範囲等を参照する方法

フィールド参照

例 意味
@0 現在の行
$0 現在の列
@1 1行目
$2 2列目
@1$2 1行目2列目
@-1 直上の行
$-1 左隣の列
@-1$+3 現在のフィールドから上に1行、右に3列
@< 最初の行
@> 最後の行
$< 最初の列
$> 最後の列
@>$2 最終行の2列目
@>$> 最終行の最終列(右下)
@<<< 最初の行から下へ数えて3行目(@3)
@>> 最後の行から上へ数えて2行目
$<< 最初の列から右へ数えて2列目($2)
$>>> 最後の列から左へ数えて3列目
@I 一つ目の水平線
@-I 現在の行から上にある最初の水平線
@+II 現在の行から下にある2番目の水平線
@III+2 三つ目の水平線から下にある二番目の行
@III-1 三つ目の水平線の直上の行
  • @行$列
  • 0は現在
  • 符号が付くと相対指定
  • <が先頭で>が末尾
  • @Iは水平線
  • < > I は複数連続で書くとその個数分だけズレる

範囲参照

例 意味
@2$3..@3$5 「2行目の3列目」から「3行目の5列目」までの矩形範囲(6フィールド)
@2$1..@>>$> 「2行目の1列目」から「下から2行目の右端列」までの矩形範囲
@I..@II 一つ目の水平線から二つ目の水平線までにある行、現在の列

始点.. 終点 の形で 始点 から 終点 までの 矩形範囲 を指す。 始点 と 終点 はそれぞれフィールドへの参照。

式の右辺に書いた場合範囲内の値を含む ベクトル になる。(例えば @1$1..@2$2 なら4要素のベクトルになる)

式の左辺に書いた場合計算で書き換える対象を表す。(@1=1行目の全列の式, $1=1列目の全行の式, @1$1..@2$2=左上2x2(4マス)の式)

※式の 左辺 に範囲参照を書く時 始点 には水平線参照(@I 等)は書けない(ダメな例: @I+1$>..@II-1$>=foo)。 終点 にはなぜか書ける(書ける例: @2$>..@II-1$>=foo)。(参考: 水平線参照を左辺には書けないのはなぜ？)

名前参照

例 意味
$name nameという名前の何かを参照

参照できる物:

リモート参照

例 意味
remote(table1, @>$>) table1という名前の表の最終行最終列(右下)にあるフィールドを参照。

#+NAME: 名前 を表の直前に書くとその表を名前で参照できる。

フィールド座標

例 意味
@# 現在の(計算対象の)行番号に展開される
$# 現在の(計算対象の)列番号に展開される
@@# @ ( @# ) と解釈。つまり@現在の行番号
$$# $ ( $# ) と解釈。つまり$現在の列番号
@$# @ ( $# ) と解釈。つまり@現在の列番号
$@# $ ( @# ) と解釈。つまり$現在の行番号

例1:列名と範囲参照

#+NAME: purchase2016
|   | 品名   | 単価 |     個数 |    計 |
| ! | item   | unit | quantity | total |
|---+--------+------+----------+-------|
| # | りんご |  128 |        5 |   640 |
| # | みかん |  200 |        3 |   600 |
|---+--------+------+----------+-------|
| # |        |      |          |  1240 |
#+TBLFM: @3$>..@II$>=$unit*$quantity::@>$>=vsum(@I..@II)

! の行は列名となる。列名は $unit*$quantity のように使える。 $3*$4 より幾分分かりやすい。 # の行は自動再計算(フィールド入力後TABやRETで全体を再計算する)対象となる。その他にも同じような方法でフィールド名やパラメータを定義する機能がある。(参考: Org Mode マニュアル 3.5.8 一歩進んだ機能 Advanced features (The Org Manual)) 

@3$>..@II$>=$unit*$quantity

式の左辺にある @3$>..@II$> は「3行目の最後の列」から「2本目の水平線までの行の最後の列」までの範囲を表す。つまり右端の小計を入れる部分(右下の合計部分を除く)を指す。水平線参照だけを使って @I$>..@II$> のようには(左辺には)書けない(エラー:"Can't assign to hline relative reference")。列名を使って @3$total..@II$total のようにも書けなかった。 @<<<$>..@>>$> のようには書ける。 

@>$>=vsum(@I..@II)

@I..@II は現在の列の一つ目の水平線から二つ目の水平線の間の範囲を表す。範囲内の値を要素とするベクトルが作られ、vsumでその合計が計算される。結果は @>$> つまり一番右下のフィールドに格納する。

今回は代入先の範囲が重ならないように厳密に指定して書いたが、 #+TBLFM: $>=$unit*$quantity::@>$>=vsum(@I..@II) のように書いても構わない。 $> は右端の全てのフィールド(ヘッダー以外)を意味するので右下( @>$> )と重なってしまうように見えるが、列指定よりフィールド指定が優先されることが決まっている。(参考: Org Mode マニュアル 3.5.5 列の数式 Column formulas (The Org Manual))

例2:リモート参照

| 税抜き | 税率 | 税込み |
|--------+------+--------|
|   1240 | 1.05 |   1302 |
|   1240 | 1.08 |   1339 |
#+TBLFM: $1=remote(purchase2016,@>$>)::$3=floor($1*$2)

remote(表名, 参照) で別表の値を取り出せる。

この例ではpurchase2016という名前の表(例1)の一番右下のフィールド(@>$>)を1列目($1)へ代入している。

例3: リモート参照(表の名前を計算時に決める)

purchase2016とpurchase2017の二つの表があるとき、この二つの表の合計部分(一番右下のフィールド)を別の表から参照したいとする。 

#+NAME: purchase2016
|   | 品名   | 単価 |     個数 |    計 |
| ! | item   | unit | quantity | total |
|---+--------+------+----------+-------|
| # | りんご |  128 |        5 |   640 |
| # | みかん |  200 |        3 |   600 |
|---+--------+------+----------+-------|
| # |        |      |          |  1240 |
#+TBLFM: @3$>..@II$>=$unit*$quantity::@>$>=vsum(@I..@II)

#+NAME: purchase2017
|   | 品名   | 単価 |     個数 |    計 |
| ! | item   | unit | quantity | total |
|---+--------+------+----------+-------|
| # | りんご |  128 |        3 |   384 |
| # | みかん |  200 |        8 |  1600 |
|---+--------+------+----------+-------|
|   |        |      |          |  1984 |
#+TBLFM: @3$>..@II$>=$unit*$quantity::@5$5=vsum(@I..II)

列に表名を入れれば次のように書ける。 

|   表名       | 金額 |
|--------------+------|
| purchase2016 | 1240 |
| purchase2017 | 1984 |
#+TBLFM: @<<$>..@>$>=remote($1,@>$>)

remoteの第一引数である $1 は先に展開されるのに対して第二引数である @>$> は展開されない(1984に置き換わらない)のが興味深い。

表名を表の中に書くのはかっこ悪いので年から表名文字列を生成してリモート参照したいなら次のようにする。 

|   年 | 金額 |
|------+------|
| 2016 | 1240 |
| 2017 | 1984 |
#+TBLFM: @<<$>..@>$>='(org-table-get-remote-range (concat "purchase" $1) (string ?@ ?> ?$ ?>))

remote(表名, 参照) の書き方では文字列の結合等はできない(リモート参照の展開タイミング的に)ので、Elisp式を使っている。

(string ?@ ?> ?$ ?>) の部分は "@>$>" とは書けない。展開のタイミング的に。

(参考: emacs - Org mode spreadsheet programmatic remote references - Stack Overflow)

例4: 結果の行内への埋め込み

行内コードブロックを使えば表の中の値を文書中の行内に埋め込める。 

#+NAME: ex4
| 品名   | 金額 |
|--------+------|
| りんご |  123 |
| みかん |   89 |
|--------+------|
|        |  212 |
#+TBLFM: @>$>=vsum(@I..@II)

合計は src_emacs-lisp[:results raw]{(org-table-get-remote-range "ex4" "@>$>")} 円です。

参考: Org Mode: Using table formula outside of a table? - Emacs Stack Exchange

例5: 連番

| 番号 | 番号4桁 |
|------+---------|
|    0 |    0000 |
|    1 |    0001 |
|    2 |    0002 |
|    3 |    0003 |
#+TBLFM: $1=@#-2::$2='(format "%04d" (- @# 2))

例6: 文字列の参照

基本的にElisp式で行った方が良い。Calcで扱えないこともないが非ASCIIは怪しい？ 

| 参照する文字列→      | abc                  | りんご                     >
| ↓式                  | ↓結果               | ↓結果                     >
|-----------------------+----------------------+---------------------------->
| @1                    | abc                  | #ERROR                     >
| "@1"                  | [40, 97, 98, 99, 41] | [40, 12426, 12435, 12372, 4>
| string("@1")          | (abc)                | string([40, 12426, 12435, 1>
| '(identity @1)        | abc                  | りんご                     >
| '(format "%sです" @1) | abcです              | りんごです                 >
#+TBLFM: @3$2=@1::@3$3=@1::@4$2="@1"::@4$3="@1"::@5$2=string("@1")::@5$3=string("@1")::@6$2='(identity @1)::@6$3='(identity @1)::@7$2='(format "%sです" @1)::@7$3='(format "%sです" @1)::@8$2=string("$s1")

水平線(hline)参照の謎

水平線参照をフィールド参照として使うとどうなるか

単に @I と書いた場合どこを参照するのか。 

|       | 10 |
|-------+----|
|       | 11 |
|       | 12 |
|-------+----|
| ↓式  |    |
| @I-1  | 10 |
| @I    | 11 |
| @I+0  | 11 |
| @I+1  | 11 |
| @I+2  | 12 |
| @II-1 | 12 |
#+TBLFM: @5$2=@I-1::@6$2=@I::@7$2=@I+0::@8$2=@I+1::@9$2=@I+2::@10$2=@II-1

水平線の直下の行を参照する模様。意図した動作かは不明。 @I+1 と書いた方が安心？

水平線参照を左辺には書けないのはなぜ？

範囲参照や例1にも書いたが、水平線(hline)への参照(@I 等)は式の左辺に書けない。

例えば次の良くあるような例では、小計を集計するのに「一つ目の水平線から二つ目の水平線までの行、右端列」つまり @I$>..@II$> と左辺に書けると便利である。 

|        | 重量 | 個数 | 計 |
|--------+------+------+----|
| みかん |   30 |    2 |    |
| りんご |   50 |    3 |    |
|--------+------+------+----|
|        |      |      |    |
#+TBLFM: @I$>..@II$>=$-2*$-1

しかし実際に書くとエラーになる。 

Can't assign to hline relative reference

なぜ書けないのかメーリングリストを"left hline"で検索してみた。

長年未解決で放置中らしい。

リモート参照とフィールド座標の展開規則

例3でも触れたがremoteの第二引数は別表の参照前に展開されないように見える。

しかしマニュアルには次のような例がある。(Field coordinates in formulas)

@3 = 2 * remote(FOO, @1$$#)
Insert the doubled value of each column of row 1 of the table named FOO into row 3 of the current table.

この例の @1$$# はremote()を解決する前に @1$1 , @1$2 , @1$3 のように参照元の列の番号に展開されなければならないはずだ。 

#+NAME: remote-ref-tbl1
| 1 |  2 |  3 |  4 |
| 5 |  6 |  7 |  8 |
| 9 | 10 | 11 | 12 |

| 2 | 4 | 6 |
#+TBLFM: @1=2*remote(remote-ref-tbl1, @1$$#)

ちなみに「現在の列」を表したいなら $0 やそもそも列を省略すれば良さそうだが、実際にやると次のようになる。 

| 2 | 2 | 2 |
#+TBLFM: @1=2*remote(remote-ref-tbl1, @1$0)

マニュアルにもリモート参照の第二引数は絶対参照か名前参照でなければならないと書いてあるのでこの書き方はできないのだろう。

org-table-eval-formula 関数を見ると、 @# 等のフィールド座標表記はリモート参照よりも前に解決しているようだ。つまり、remoteとその引数の展開は次のような順番になっている。

  1. フィールド座標(@# $#)だけ先に解決(数字へ置き換え)
  2. 第一引数を現在の表上で解決し、得られた名前に従って参照先となる表を見つける
  3. 見つけた表上で1の結果得られた第二引数の参照を解決する

そもそもこの remote(表名, 参照) という表記は関数の計算などではなく文字列の置き換えに過ぎない。なので展開の規則はCalcともElispとも異なる。 実際マニュアルの例にもあるとおりElisp式内でも同じように書ける。

$2 = '(identity remote(FOO, @@#$1))
Copy text or values of each row of column 1 of the table named FOO into column 2 of the current table.
© 2024 AKIYAMA Kouhei, all rights reserved.