org-modeの表(テーブル)で集計する方法(orgtbl-aggregateの使い方)

org-modeのスプレッドシート(表計算)機能は便利ですが、データをグループ化して集計する機能が欠けています。

Excelであればピボットテーブルや小計機能を使えば超簡単ですが、org-modeでやろうとするとどうしたものか困ってしまいます。ソースコードブロック(babel)で何とかする方法もありますが、ここでは orgtbl-aggregate という外部パッケージを使う方法を説明します。

orgtbl-aggregateの入手先

orgtbl-aggregateは次の場所で開発されているようです。

tbanel/orgaggregate: Aggregates tables in Org mode

MELPA等でも配布されているようです。

一点だけ注意。日本語(全角文字)を使用すると表が乱れる不具合がありました(2022-11-03現在)。例によって文字列に対してstring-width関数を使用していないことが原因です。 (length cellnp) や (length cell) と書かれている部分を (string-width cellnp) や (string-width cell) に変更したら解消しました。

基本的な使い方

適当な表

適当な表を用意しました。

#+NAME: 20221103-payments
| 大分類 | 小分類 | 支払先       | 目的           |  金額 |
|--------+--------+--------------+----------------+-------|
| 交通費 | 鉄道   | JR東日本     | 立川-甲府      |  1690 |
| 交通費 | バス   | 山梨交通     | 甲府駅-広河原  |  1990 |
| 交通費 | バス   | 山梨交通     | 利用者協力金   |   300 |
| 宿泊費 | 幕営   | 白根御池小屋 | テント1人1泊   |  1000 |
| 食費   | 飲料   | 白根御池小屋 | 麦茶           |   500 |
| 宿泊費 | 幕営   | 北岳肩の小屋 | テント1人1泊   |  1000 |
| 食費   | 飲料   | 北岳肩の小屋 | なっちゃん     |   600 |
| 食費   | 飲料   | 北岳肩の小屋 | 水1L           |   200 |
| 食費   | 菓子   | 白根御池小屋 | 信玄アイス     |   800 |
| 交通費 | バス   | 山梨交通     | 広河原-甲府駅  |  1990 |
| 交通費 | バス   | 山梨交通     | 利用者協力金   |   300 |
| 交通費 | 鉄道   | JR東日本     | 甲府-立川      |  1690 |
|--------+--------+--------------+----------------+-------|
|        |        |              |                | 12060 |
#+TBLFM: @>$5=vsum(@I..@II)

20221103-paymentsという名前が付いたorg-modeの表です。これは6月末に北岳へ行った時に支払ったお金の一覧です。

右下に合計金額を書き加えてあります。これが少々面倒を引き起こします。

基本

大まかにどんなものにどのくらいの費用がかかったのでしょうか。

何も考えずに最低限の指定をしてみます。 #+BEGIN: ～ #+END: は C-c C-x x で挿入できます。aggregate、テーブル名、集計する列などを入力します。その後で C-c C-c を押すと集計を実行します。

#+BEGIN: aggregate :table "20221103-payments" :cols "大分類 vsum(金額)"
| 大分類 | vsum(金額) |
|--------+------------|
| 交通費 |       7960 |
| 宿泊費 |       2000 |
| 食費   |       2100 |
|        |      12060 |
#+END:

概ね良いのですが、合計の部分(12060)が一緒くたになっているのが気になります。元の表から合計の行を消しても良いのですが、それは残したいものとしましょう。計算を水平線(hline)で囲まれた範囲に限定できないものでしょうか。

隠れた列「hline」

実は hline という列が指定できます。元の表には無い列ですが、何個目の水平線の後かを表します(一つ目の水平線は見出しとの区切りのため数えません)。

#+BEGIN: aggregate :table "20221103-payments" :cols "hline 大分類 vsum(金額)"
| hline | 大分類 | vsum(金額) |
|-------+--------+------------|
|     0 | 交通費 |       7960 |
|     0 | 宿泊費 |       2000 |
|     0 | 食費   |       2100 |
|     1 |        |      12060 |
#+END:

条件抽出

hlineが0の行だけを抽出すれば合計部分を排除できます。それには :cond (equal hline "0") を指定します。 "0" は文字列です。

#+BEGIN: aggregate :table "20221103-payments" :cols "大分類 vsum(金額)" :cond (equal hline "0")
| 大分類 | vsum(金額) |
|--------+------------|
| 交通費 |       7960 |
| 宿泊費 |       2000 |
| 食費   |       2100 |
#+END:

列名変更

列名の vsum(金額) というのはちょっと式っぽいので、単純に 計 にしてみます。 ;'計' を指定します。列名が変わります。

#+BEGIN: aggregate :table "20221103-payments" :cols "大分類 vsum(金額);'計'" :cond (equal hline "0")
| 大分類 |   計 |
|--------+------|
| 交通費 | 7960 |
| 宿泊費 | 2000 |
| 食費   | 2100 |
#+END:

ソート

金額が大きい順にソートしてみます。 ;^N を指定すると数値降順にソートされます(小文字で昇順。a/Aアルファベット順、n/N数値順、t/T時間順)。

#+BEGIN: aggregate :table "20221103-payments" :cols "大分類 vsum(金額);'計';^N" :cond (equal hline "0")
| 大分類 |   計 |
|--------+------|
| 交通費 | 7960 |
| 食費   | 2100 |
| 宿泊費 | 2000 |
#+END:

本記事の趣旨とは関係ない余談ですが、やはり交通費が一番かかります。食費には自宅からの持ち込み分は含まれていません。宿泊費が安いのはひとえにテント泊だからですが、装備を用意するのに多額の費用がかかります(山小屋に泊まるのと比べれば十分元は取れます)。

追加処理

最後に合計を付け加えてみます。ちょっと面倒です。

#+BEGIN: aggregate :table "20221103-payments" :cols "大分類 vsum(金額);'計';^N" :cond (equal hline "0") :post (lambda (table) `(,@table hline ("合計" ,(cl-loop for line in table sum (if (eq line 'hline) 0 (string-to-number (nth 1 line)))))))
| 大分類 |    計 |
|--------+-------|
| 交通費 |  7960 |
| 食費   |  2100 |
| 宿泊費 |  2000 |
|--------+-------|
| 合計   | 12060 |
#+END:

:post の後の部分は整形すると次のようになっています。

(lambda (table)
  `(,@table
    hline
    ("合計" ,(cl-loop for line in table
                      sum (if (eq line 'hline)
                              0
                            (string-to-number (nth 1 line)))))))

tableは行のリストで、その要素は列のリストかシンボルhlineです。

このコードは末尾にhlineと合計の行を付け加えます。

単純に上2行(見出しとhline)を無視するだけであればcl-loop部分は次のようにしても良かったかもしれません。

(cl-loop for line in (cddr table)
         sum (string-to-number (nth 1 line)))

結果の埋め込み

元の表もそうですが、合計が入るとデータとしては少々扱いづらい表になってしまいます。合計は表の中ではなく別の場所に記載するというのも手でしょう。

インラインソースコードブロックを使えば文書中に結果を埋め込むことも出来ます。

合計: src_elisp[:var tbl=20221103-payments[0:-2,-1] :colnames yes :hlines no :eval no-export ]{(apply #'+ tbl)} {{{results(=12060=)}}}円

わざわざ計算しなくても表の右下から持ってきたいだけなら次のようにしても大丈夫です。(集計するブロックに名前を付けてそれを参照しても大丈夫です)

合計: src_elisp[:var tbl=20221103-payments[-1,-1] :eval no-export]{tbl} {{{results(=12060=)}}}円

ちなみに[-1,-1]の部分は参照先の一部を取り出すための表記です。詳しくは次の辺りをご覧下さい。

別の観点

もちろん集計するキーはどの列でも構いません。

支払先毎の金額を出してみます。

#+BEGIN: aggregate :table "20221103-payments" :cols "支払先 vsum(金額);^N" :cond (equal hline "0")
| 支払先       | vsum(金額) |
|--------------+------------|
| 山梨交通     |       4580 |
| JR東日本     |       3380 |
| 白根御池小屋 |       2300 |
| 北岳肩の小屋 |       1800 |
#+END:

hlineの出力

合計の前に水平線を出すには、 :hline を指定するという手もあります。

#+BEGIN: aggregate :table "20221103-payments" :cols "hline;^n 大分類 vsum(金額);^N" :hline 1
| hline | 大分類 | vsum(金額) |
|-------+--------+------------|
|     0 | 交通費 |       7960 |
|     0 | 食費   |       2100 |
|     0 | 宿泊費 |       2000 |
|-------+--------+------------|
|     1 |        |      12060 |
#+END:

単純に金額だけで(降順)ソートすると合計の12060が先頭に来てしまいます。「hline」列を金額より前にソートすることで合計が下に居続けます。

その上で :hline 1 を指定すると、1列目(hline)の変わり目に水平線が入ります。

hlineの列は邪魔なので ;<> を指定することで非表示にできます。

#+BEGIN: aggregate :table "20221103-payments" :cols "hline;<>;^n 大分類 vsum(金額);^N" :hline 1
| 大分類 | vsum(金額) |
|--------+------------|
| 交通費 |       7960 |
| 食費   |       2100 |
| 宿泊費 |       2000 |
|--------+------------|
|        |      12060 |
#+END:

:hlineの後の数字は列数です。左から指定された列数の列が同じ行をグループ化し、その間に水平線を挿入します。値がソートされていないと正しく挿入できない、と思います。

#+BEGIN: aggregate :table "20221103-payments" :cols "大分類;^a 支払先;^a 小分類;^a vsum(金額);^N" :cond (equal hline "0") :hline 1
| 大分類 | 支払先       | 小分類 | vsum(金額) |
|--------+--------------+--------+------------|
| 交通費 | JR東日本     | 鉄道   |       3380 |
| 交通費 | 山梨交通     | バス   |       4580 |
|--------+--------------+--------+------------|
| 宿泊費 | 北岳肩の小屋 | 幕営   |       1000 |
| 宿泊費 | 白根御池小屋 | 幕営   |       1000 |
|--------+--------------+--------+------------|
| 食費   | 北岳肩の小屋 | 飲料   |        800 |
| 食費   | 白根御池小屋 | 菓子   |        800 |
| 食費   | 白根御池小屋 | 飲料   |        500 |
#+END:

#+BEGIN: aggregate :table "20221103-payments" :cols "大分類;^a 支払先;^a 小分類;^a vsum(金額);^N" :cond (equal hline "0") :hline 2
| 大分類 | 支払先       | 小分類 | vsum(金額) |
|--------+--------------+--------+------------|
| 交通費 | JR東日本     | 鉄道   |       3380 |
|--------+--------------+--------+------------|
| 交通費 | 山梨交通     | バス   |       4580 |
|--------+--------------+--------+------------|
| 宿泊費 | 北岳肩の小屋 | 幕営   |       1000 |
|--------+--------------+--------+------------|
| 宿泊費 | 白根御池小屋 | 幕営   |       1000 |
|--------+--------------+--------+------------|
| 食費   | 北岳肩の小屋 | 飲料   |        800 |
|--------+--------------+--------+------------|
| 食費   | 白根御池小屋 | 菓子   |        800 |
| 食費   | 白根御池小屋 | 飲料   |        500 |
#+END:

orgtbl-aggregateを使わない方法(ソースコードブロックで集計する)

orgtbl-aggregate を使わないのであればソースコードブロックを使うのが普通だと思います。環境に依存せずに使えるのはEmacs Lispだけです。

#+begin_src elisp :var tbl=20221103-payments :colnames no :hlines yes
(let ((key-col 0)
      (value-col 4)
      (start-row 2)
      body)
  ;; 集計
  (cl-loop for line in (nthcdr start-row tbl)
           until (eq line 'hline)
           for key = (nth key-col line)
           for value = (nth value-col line)
           do (cl-incf (alist-get key body 0 nil #'equal) value))
  ;; ドット対をリストに変換
  (setq body (mapcar (lambda (x) (list (car x) (cdr x))) body))
  ;; ソート
  (setq body (sort body (lambda (a b) (> (cadr a) (cadr b) ))))

  `(;; 見出し
    (,(nth key-col (car tbl)) ,(nth value-col (car tbl)))
    ;; 水平線
    hline
    ;; データ本体
    ,@body
    ;; 水平線
    hline
    ;; 合計
    ("合計" ,(cl-loop for (key value) in body sum value))
  ))
#+end_src

#+RESULTS:
| 大分類 |  金額 |
|--------+-------|
| 交通費 |  7960 |
| 食費   |  2100 |
| 宿泊費 |  2000 |
|--------+-------|
| 合計   | 12060 |

あらかじめ関数にでもしておかない限り面倒くさくて仕方がありません。Rなど他の言語を使えば多少はマシかもしれませんが。

自分用の集計用関数を定義しておけば良いのでしょうが汎用性を持たせるのには手間がかかります。それなら orgtbl-aggregate のダイナミックブロックで必要なことが十分手軽に出来ます。