Cross Tabulation (Advanced)

This page is about the way how to summarize with all kinds of data as advanced version.

  • Calculate the summary.
  • Summary with formula

Calculate the summary.

Display total summary

  1. Open 'Action set setting' dialog.
  2. In 'Cross-tabulate the data of the sheet', check 'Setting tabulation method' in the frame of vertical and horizontal axis.
../../../../_images/img_01.en23.png

After checking 'Total display' in vertical axis, total summary row is displayed and after checking 'Total display' in horizontal axis,Total column is displayed.

  1. Save Action set.

Tip

Total row and Total column is displayed like the following.

../../../../_images/img_02.en21.png

Display subtotal summary.

  1. Open 'Action set setting' dialog.
  2. In 'Cross-tabulate the data of the sheet', click 'Vertical axis' link button or 'Horizontal axis' link button.
../../../../_images/img_03.en15.png
  1. In 'Setting tabulation items', check 'Subtotal display' next to the field to display, and then click 'OK' button
../../../../_images/img_04.en14.png

After checking 'Subtotal display' in Vertical axis, Subtotal row is displayed and after checking 'Subtotal display' in Horizontal axis, Subtotal column is displayed.

Tip

In above example, 'Month'(C column) as horizontal axis and 'Store'(A column) is chosen and then Subtotal of 'Month' is set to display.,

../../../../_images/img_11.en4.png
  1. Save Action set.

Tip

Subtotal row and column is displayed like the following.

  • The case of displayed 'Month' and 'Goods' as vertical axis, 'Store' as horizontal axis, Subtotal row of 'Month'.
../../../../_images/img_05.en13.png
  • The case of displayed 'Goods' as vertical axis, 'Month' and 'Shop' as horizontal axis, Subtotal row of 'Month'.
../../../../_images/img_06.en9.png

Summary with formula

  1. Open 'Action set setting' dialog.
  2. In 'Cross-tabulate the data of the sheet' action, Click 'Data' link button.
../../../../_images/img_07.en8.png
  1. Open 'Setting datas', choose 'Formula' at the field to use formula.
../../../../_images/img_08.en5.png
  1. Set the summary result by using the formula.
../../../../_images/img_09.en5.png

Note

  • In formula, you can use only 'Header name', arithmetic operators and functions.
  • Set formula without '=' at the initial character.

Tip

Above case is the example to set 'Sales' column(D column) and 'Profit' column(E column) as target summary and then set to display the value as Profit ratio.

../../../../_images/img_12.en3.png
  1. Save Action set.

Tip

The result by the formula and function is as follows.

../../../../_images/img_10.en4.png

Related keywords

poivot table, pivottable