org-spreadsheet-intro.org 8.8 KB

This short tutorial will go through the basics that you need to know if you want to use Org as a spreadsheet system.

Let's start with a this very simple table:

Student Maths Physics
Bertrand 13 09
Henri 15 14
Arnold 17 13

Which is the output of this table in Org:

:| Student | Maths | Physics | :|----------+-------+---------| :| Bertrand | 13 | 09 | :| Henri | 15 | 14 | :| Arnold | 17 | 13 |

The purpose of this tutorial is to understand how to get from this simple table to this one, where we have the mean per student and per discipline:

Student Maths Physics Mean
Bertrand 13 09 11
Henri 15 14 14.5
Arnold 17 13 15
Means 15 12 13.5

Getting acquainted with references

Let's start with the mean per student.

:| Student | Maths | Physics | Mean | :|----------+-------+---------+-----------| :| Bertrand | 13 | 09 | [Formula] | :| Henri | 15 | 14 | | :| Arnold | 17 | 13 | |

Before being able to insert a formula in [Formula], you need to know how to refer to a row, a column or a single field.

The easiest way to learn about references is to type C-c ? while you are in a field.

For example, if you are in the [Formula] field, C-c ? will tell you: line @2, col $4, ref @2$4 or D2, meaning that you are on the second row (or line) of the fourth column, and the reference for this field is either @2$4 or D2.

At any moment, if your lost in rows and columns, you can always turn on the reference visualization grid with =C-c }=:

../images/bzg/reference_visualization.jpg

Your first formula

Put the cursor in the (empty) [Formula] field. Now start typing this: =vmean($2..$3). This means: calculate the mean for fields from the second to the third field in this row. If you prefer the other notation, enter this: =vmean(B&..C&), where the & character stands for in this row (this is implicit in the previous notation.)

While still in the row, hit C-c C-c=: you should observe two things: the formula has been replaced by the result of the calculation and a new line starting with =#+TBLFM has been inserted at the bottom of the table.

The #+TBLFM line contains all the formulas for the table above, and you should be careful while editing it manually.

Column formulas vs. field formulas

Ok, so now we have this table:

:| Student | Maths | Physics | Mean | :|----------+-------+---------+------| :| Bertrand | 13 | 09 | 11 | :| Henri | 15 | 14 | | :| Arnold | 17 | 13 | | :#+TBLFM: @2$4=vmean($2..$3)

But what we really want is to compute the formulas for all fields in the column named "Mean" -- in other words, we really want a column formula not a field formula.

To replace the formula with a column formula, go back to the field where it has been defined and type =vmean($2..$3). Note that the only difference with what you've inserted previously is that the formula is prefixed by = instead of :=. When you're done, do a =C-c C-c= in the field: you should be prompted whether you want to replace the formula with a column formula.

Once you agree with this, the value in the field should be the same than before (namely 11) and you can now update all the fields in this column by reapplying all formulas with C-u C-c * (or C-c C-c if you're on the #+TBLFM line.)

So now we have this table

:| Student | Maths | Physics | Mean | :|----------+-------+---------+------| :| Bertrand | 13 | 09 | 11 | :| Henri | 15 | 14 | 14.5 | :| Arnold | 17 | 13 | 15 | :#+TBLFM: $4=vmean($2..$3)

As our single formula in #+TBLFM now applies to the entire column, there is no reference to any row. The formula was applied for the =@2$4= field, and is now applied for the $4 column.

Interactively edit formulas

For now we have been defining formulas by inserting them directly in the table cells: typing = in a field starts the definition for a column formula and typing := starts a definition for a field formula.

If you prefer, you can edit formulas /in the minibuffer/: use C-c == for editing column formulas or =C-u C-c = for field formulas.

And finally, you can edit formulas more interactively in a dedicated buffer by typing  C-c ' . This new buffer lists all the formulas for the table at point and provides facilities to edit the references.

When the cursor is above areference, the corresponding field in the table get highlighted. Nice! But you can do more than that: you can actually select the reference by using the =S-= keys.

Note: you might worry that moving a table's column with =M-= or a table's row with M-<up/down> might confuse the references in the #+TBLFM line, but each move automagically updates the references in this line.

Calc and Elisp formulas

The default syntax for formulas is the one of Calc, the GNU Emacs package for doing computations.

Excerpt from the Calc manual about algebraic-style formulas:

:Algebraic formulas use the operators `+', `-', `*', `/', and `^'. You :can use parentheses to make the order of evaluation clear. In the :absence of parentheses, `^' is evaluated first, then `*', then `/', :then finally `+' and `-'. For example, the expression


:2 + 3*4*5 / 6*7^8 - 9


:is equivalent to


:2 + ((3*4*5) / (6*(7^8)) - 9

In org tables, you can use references instead of values for performing the computation. Pretty simple.

But what if you want to use Emacs lisp code instead of Calc?

Lets say for example that you want to associate each student with a decimal of the Pi number, depending on their mean across maths and physics.

For this you need to tell Org about the value you consider to be the value of the Pi number. You can do this by adding this line:

:#+CONSTANTS: pi=3.14159265358979323846

Then you can define an Emacs lisp formula like this one:

:$5='(substring (number-to-string $pi) (round $4) (1+ (round $4)));N

Ahem. Let's parse this:

  • = '(=: starts an Emacs lisp formula
  • (substring S A B)=: get a substring of string =S between A and
  • =B=
  • =(number-to-string $pi)=: convert the constant "Pi" into a string
  • (round $4)=: get the rounded value of the value in column =$4
  • =;N=: consider the values of fields to be numeric values, not strings.

If the mean of a student is 10, this formula returns the tenth decimal of Pi.

Debugging formulas

So now our table is this one:

:| Student | Maths | Physics | Mean | Pi number | :|----------+-------+---------+------+-----------| :| Bertrand | 13 | 09 | 11 | 5 | :| Henri | 15 | 14 | 14.5 | 7 | :| Arnold | 17 | 13 | 15 | 9 | :#+TBLFM: $4=vmean($2..$3)::$5='(substring (number-to-string $pi) (round $4) (1+ (round $4)));N

If you come back to this table and feel lazy about undestanding what the Emacs lisp function does, you might want to debbug the formulas.

Turn the formulas debugger on with C-c { and hit C-c C-c in a field or C-u C-c * anywhere on this table: this will open a new buffer with details about each step of each computation.

Substitution history of formula
Orig:   '(substring (number-to-string $pi) (round $4) (1+ (round $4)));N
$xyz->  '(substring (number-to-string 3.14159265358979323846) (round $4) (1+ (round $4)))
@r$c->  '(substring (number-to-string 3.14159265358979323846) (round $4) (1+ (round $4)))
$1->    '(substring (number-to-string 3.14159265358979323846) (round 11) (1+ (round 11)))
Result: 5
Format: NONE
Final:  5

And much, much more...

I hope you already imagine the power of using Org as a spreadsheet system. But you can do a lot more than that! Using relative references, defining names for columns, define parameters for formulas, define fields that should be automatically recalculated, etc. Have a look to the advanced features in Org-mode manual.