From 874e9a0fe9a3ae594966f17792c615332bbc722b Mon Sep 17 00:00:00 2001 From: Olivier Hallot Date: Wed, 25 Mar 2020 22:03:38 -0300 Subject: tdf#126923 Add guide for subtotals tool Change-Id: I941214be272d6e959c1129b074891bb6f229ba26 Reviewed-on: https://gerrit.libreoffice.org/c/help/+/91079 Tested-by: Jenkins Reviewed-by: Olivier Hallot --- source/auxiliary/scalc.tree | 27 +++++++------ source/text/scalc/01/12050000.xhp | 6 ++- source/text/scalc/01/12050100.xhp | 35 ++++------------- source/text/scalc/01/12050200.xhp | 28 +++++++------- source/text/scalc/guide/subtotaltool.xhp | 66 ++++++++++++++++++++++++++++++++ 5 files changed, 105 insertions(+), 57 deletions(-) create mode 100644 source/text/scalc/guide/subtotaltool.xhp (limited to 'source') diff --git a/source/auxiliary/scalc.tree b/source/auxiliary/scalc.tree index 52f2631dce..a82517becb 100644 --- a/source/auxiliary/scalc.tree +++ b/source/auxiliary/scalc.tree @@ -140,19 +140,22 @@ Filtering Pivot Tables Selecting Pivot Table Output Ranges Updating Pivot Tables - - - Pivot Chart - Creating Pivot Charts - Editing Pivot Charts - Filtering Pivot Charts - Updating Pivot Charts - Deleting Pivot Charts - - + + +Pivot Chart +Creating Pivot Charts +Editing Pivot Charts +Filtering Pivot Charts +Updating Pivot Charts +Deleting Pivot Charts + + Using Scenarios - - + + +Using Subtotals Tool + + Addresses and References, Absolute and Relative Referencing a Cell in Another Document References to Other Sheets and Referencing URLs diff --git a/source/text/scalc/01/12050000.xhp b/source/text/scalc/01/12050000.xhp index f9a1f947f9..8b6ed83112 100644 --- a/source/text/scalc/01/12050000.xhp +++ b/source/text/scalc/01/12050000.xhp @@ -30,7 +30,7 @@
-

Subtotals

+

Subtotals

Calculates subtotals for the columns that you select. $[officename] uses the SUM function to automatically calculate the subtotal and grand total values in a labeled range. You can also use other functions to perform the calculation. $[officename] automatically recognizes a defined database area when you place the cursor in it.
@@ -42,6 +42,8 @@

Delete

Deletes the subtotal rows in the selected area. - +
+ +
diff --git a/source/text/scalc/01/12050100.xhp b/source/text/scalc/01/12050100.xhp index 4c317808f9..eee5400783 100644 --- a/source/text/scalc/01/12050100.xhp +++ b/source/text/scalc/01/12050100.xhp @@ -1,6 +1,4 @@ - - - @@ -33,41 +30,23 @@
-1st, 2nd, 3rd Group +

1st, 2nd, 3rd Group

Specify the settings for up to three subtotal groups. Each tab has the same layout.
-To insert subtotal values into a table: - - -Ensure that the columns of the table have labels. - - -Select the table or the area in the table that you want to calculate subtotals for, and then choose Data – Subtotals. - - -In the Group By box, select the column that you want to add the subtotals to. - - -In the Calculate subtotals for box, select the check boxes for the columns containing the values that you want to subtotal. - - -In the Use function box, select the function that you want to use to calculate the subtotals. - - -Click OK. - - -Group by +

Group by

Select the column that you want to control the subtotal calculation process. If the contents of the selected column change, the subtotals are automatically recalculated. -Calculate subtotals for +

Calculate subtotals for

Select the column(s) containing the values that you want to subtotal. -Use function +

Use function

Select the mathematical function that you want to use to calculate the subtotals. +
+ +
 diff --git a/source/text/scalc/01/12050200.xhp b/source/text/scalc/01/12050200.xhp index 6c178aa17e..ab8b830c5f 100644 --- a/source/text/scalc/01/12050200.xhp +++ b/source/text/scalc/01/12050200.xhp @@ -1,7 +1,4 @@ - - - - - - + Options @@ -34,27 +29,30 @@ -Options +

Options

Specify the settings for calculating and presenting subtotals.
-Page break between groups +

Page break between groups

Inserts a new page after each group of subtotaled data. -Case sensitive +

Case sensitive

Recalculates subtotals when you change the case of a data label. -Pre-sort area according to groups +

Pre-sort area according to groups

Sorts the area that you selected in the Group by box of the Group tabs according to the columns that you selected. -Sort -Include formats +

Sort

+

Include formats

Considers formatting attributes when sorting. -Custom sort order +

Custom sort order

Uses a custom sorting order that you defined in the Options dialog box at %PRODUCTNAME Calc - Sort Lists. -Ascending +

Ascending

Sorts beginning with the lowest value. You can define the sort rules on Data - Sort - Options. You define the default on %PRODUCTNAME - PreferencesTools - Options - Language settings - Languages. -Descending +

Descending

Sorts beginning with the highest value. You can define the sort rules on Data - Sort - Options. You define the default on %PRODUCTNAME - PreferencesTools - Options - Language settings - Languages. +
+ +
diff --git a/source/text/scalc/guide/subtotaltool.xhp b/source/text/scalc/guide/subtotaltool.xhp new file mode 100644 index 0000000000..9762007409 --- /dev/null +++ b/source/text/scalc/guide/subtotaltool.xhp @@ -0,0 +1,66 @@ + + + + + + + Using Subtotals Tool + /text/scalc/guide/subtotaltool.xhp + + + + + subtotals;using subtotals tool + +
+

Using Subtotal Tool

+ Calc offers the Subtotals tool as a more comprehensive alternative to the SUBTOTAL function. In contrast to SUBTOTAL, which only works on a single array, the Subtotals tool can create subtotals for up to three arrays arranged in labeled columns. It also groups subtotals by category and sorts them automatically, thereby eliminating the need to apply AutoFilters and filter categories by hand. +
+
+ +
+

Using the Subtotals tool

+To insert subtotal values into a worksheet: + + + Select the cell range for the subtotals that you want to calculate, and remember to include the column heading labels. Alternatively, click on a single cell within your data to allow Calc to automatically identify the range. + + + Select Data > Subtotals from the Menu bar to open the Subtotals dialog. + + + In the Group by drop-down list on the first Group page, select a column by its label. Entries in the cell range from step 1 will be grouped and sorted by matching values in this column. + + + In the Calculate subtotals for box on the first Group page, select a column containing values to be subtotaled. If you later change values in this column, Calc will automatically recalculate the subtotals. + + + In the Use function box on the first Group page, select a function to calculate the subtotals for the column selected in step 4. + + + Repeat steps 4 and 5 to create subtotals for other columns on the first Group page. + + + You can create two more subtotal categories by using the second Group and third Group pages and repeating steps 3 to 6. If you do not want to add more groups, then leave the Group by list for each page set to “- none -”. + + + Click OK. Calc will add subtotal and grand total rows to your cell range. + + +

Subtotal outline

+ +When you use the Subtotals tool, Calc inserts an outline to the left of the row number column. This outline represents the hierarchical structure of your subtotals, and can be used to hide or show data at different levels in the hierarchy using the numbered column indicators at the top of the outline or the group indicators, denoted by plus (+) and minus (-) signs. +This feature is useful if you have many subtotals, as you can simply hide low-level details, such as individual entries, to produce a high-level summary of your data. +To turn off outlines, select Data > Group and Outline > Remove Outline from the Menu bar. To reinstate them, select Data > Group and Outline > AutoOutline. +
+ +
+ + -- cgit