Difference between revisions of "Documentation/OOo3 User Guides/Calc Guide/Consolidating data"

From Apache OpenOffice Wiki
Jump to: navigation, search
(New page: {{DISPLAYTITLE:Consolidating data}} {{Documentation/CG3Ch7TOC |ShowPrevNext=block |PrevPage=Documentation/OOo3_User_Guides/Calc Guide/Data Analysis |NextPage=Documentation/OOo3_User_Guides...)
 
 
(5 intermediate revisions by one other user not shown)
Line 5: Line 5:
 
|NextPage=Documentation/OOo3_User_Guides/Calc Guide/Creating subtotals
 
|NextPage=Documentation/OOo3_User_Guides/Calc Guide/Creating subtotals
 
}}__notoc__
 
}}__notoc__
−
'''Data > Consolidate''' provides a graphical interface for copying data from one range of cells to another, then running one of a dozen functions on the data. During consolidation, the contents of cells from several sheets can be combined in one place.
+
'''Data > Consolidate''' provides a way to combine data from two or more ranges of cells into a new range while running one of several functions (such as Sum or Average) on the data. During consolidation, the contents of cells from several sheets can be combined in one place.
  
 
<ol>
 
<ol>
−
<li>Open the document that contains the cell ranges to be consolidated.</li>
+
<li>Open the document that contains the cell ranges to be consolidated.
−
<li>Choose '''Data > Consolidate''' to open the Consolidate dialog.</li>
+
<li>Choose '''Data > Consolidate''' to open the Consolidate dialog. The figure shows this dialog after making the changes described below.
  
 
[[Image:CG3Ch7F1.png|thumb|none|500px|''Defining the data to be consolidated'']]
 
[[Image:CG3Ch7F1.png|thumb|none|500px|''Defining the data to be consolidated'']]
  
−
<li>If the '''Source data range''' list contains named ranges, you can select a source cell range to consolidate with other areas.</li>
+
<li>The '''Source data range''' list contains any existing named ranges (created using '''Data > Define Range'''), so you can quickly select one to consolidate with other areas.
  
−
<p>If the source range is not named, click in the field to the right. and either type a reference for the first source data range or use the mouse to select the range on the sheet. (You may need to move the Consolidate dialog to reach the required cells.)
+
<p>If the source range is not named, click in the field to the right of the drop-down list and either type a reference for the first source data range or use the mouse to select the range on the sheet. (You may need to move the Consolidate dialog or click on the Shrink icon to reach the required cells.)</p>
  
−
<li>Click '''Add'''. The selected range now appears on the Consolidation ranges list.</li>
+
<li>Click '''Add'''. The selected range is added to the Consolidation ranges list.
−
<li>Select additional ranges and click '''Add''' after each selection.</li>
+
<li>Select additional ranges and click '''Add''' after each selection.
−
<li>Specify where you want to display the result by selecting a target range from the '''Copy results to''' box.</li>
+
<li>Specify where you want to display the result by selecting a target range from the '''Copy results to''' drop-down list.
  
−
<p>If the target range is not named, click in the field next to '''Copy results to''' and enter the reference of the target range or select the range using the mouse or position the cursor in the top left cell of the target range.
+
<p>
 +
If the target range is not named, click in the field next to '''Copy results to''' and enter the reference of the target range or select the range using the mouse or position the cursor in the top left cell of the target range. ''Copy results to'' takes only the first cell of the target range instead of the entire range as is the case for ''Source data range''.</p>
  
−
<li>Select a function from the Function list. The function specifies how the values of the consolidation ranges are linked. The Sum function is the default setting.</li>
+
<li>Select a function from the Function list. This specifies how the values of the consolidation ranges will be calculated. The default setting is Sum, which adds the corresponding cell values of the Source data range and gives the result in the target range.
  
−
<p>Most of the available functions are statistical (such as AVERAGE, MIN, MAX, STDEV), and the tool is most useful when you are working with the same data over and over.
+
<p>Most of the available functions are statistical (such as Average, Min, Max, Stdev), and the tool is most useful when you are working with the same data over and over.</p>
  
−
<li>Optionally click '''More''' in the Consolidate dialog to display additional settings.</li>
+
<li>At this point you can click '''More''' in the Consolidate dialog to access the following additional settings:
−
<ul>
+
 
−
<li>Select '''Link to source data''' to insert the formulas that generate the results in the target range, rather than the actual results. If you link the data, any values modified in the source range are automatically updated in the target range. The corresponding cell references in the target range are inserted in consecutive rows, which are automatically ordered and then hidden from view. Only the final result, based on the selected function, is displayed.</li>
+
*Select '''Link to source data''' to insert the formulas that generate the results into the target range, rather than the actual results. If you link the data, any values modified in the source range are automatically updated in the target range.
−
<li>Under '''Consolidate by''', select either ''Row labels'' or ''Column labels'' if the cells of the source data range are not to be consolidated corresponding to the identical position of the cell in the range, but instead according to a matching row label or column label. To consolidate by row labels or column labels, the label must be contained in the selected source ranges. The text in the labels must be identical, so that rows or columns can be accurately matched. If the row or column label does not match any that exist in the target range, it will be appended as a new row or column.</li>
+
{{Warn|The corresponding cell references in the target range are inserted in consecutive rows, which are automatically ordered and then hidden from view. Only the final result, based on the selected function, is displayed.}}
−
</ul>
+
*Under '''Consolidate by''', select either ''Row labels'' or ''Column labels'' if the cells of the source data range are not to be consolidated corresponding to the identical position of the cell in the range, but instead according to a matching row label or column label. To consolidate by row labels or column labels, the label must be contained in the selected source ranges. The text in the labels must be identical, so that rows or columns can be accurately matched. If the row or column label does not match any that exist in the target range, it is added to the target range as a new row or column.</li>
−
<li>Click '''OK''' to consolidate the ranges.</li>
+
<li>Click '''OK''' to consolidate the ranges.
 +
<li>If you are continually working with the same range, then you probably want to use '''Data > Create Range''' to give it a name.
 
</ol>
 
</ol>
−
 
+
The consolidation ranges and target range are saved as part of the document. If you later open a document in which consolidation has been defined, this data is still available.
−
If you are continually working with the same range, then you probably want to use '''Data > Create Range''' to give it a name.
+
−
 
+
−
The data from the consolidation ranges and target range are saved when you save the document. If you later open a document in which consolidation has been defined, this data will again be available.
+
  
  
 
{{CCBY}}
 
{{CCBY}}
 
[[Category: Calc Guide (Documentation)]]
 
[[Category: Calc Guide (Documentation)]]

Latest revision as of 22:10, 13 July 2018



Data > Consolidate provides a way to combine data from two or more ranges of cells into a new range while running one of several functions (such as Sum or Average) on the data. During consolidation, the contents of cells from several sheets can be combined in one place.

  1. Open the document that contains the cell ranges to be consolidated.
  2. Choose Data > Consolidate to open the Consolidate dialog. The figure shows this dialog after making the changes described below.
    Defining the data to be consolidated
  3. The Source data range list contains any existing named ranges (created using Data > Define Range), so you can quickly select one to consolidate with other areas.

    If the source range is not named, click in the field to the right of the drop-down list and either type a reference for the first source data range or use the mouse to select the range on the sheet. (You may need to move the Consolidate dialog or click on the Shrink icon to reach the required cells.)

  4. Click Add. The selected range is added to the Consolidation ranges list.
  5. Select additional ranges and click Add after each selection.
  6. Specify where you want to display the result by selecting a target range from the Copy results to drop-down list.

    If the target range is not named, click in the field next to Copy results to and enter the reference of the target range or select the range using the mouse or position the cursor in the top left cell of the target range. Copy results to takes only the first cell of the target range instead of the entire range as is the case for Source data range.

  7. Select a function from the Function list. This specifies how the values of the consolidation ranges will be calculated. The default setting is Sum, which adds the corresponding cell values of the Source data range and gives the result in the target range.

    Most of the available functions are statistical (such as Average, Min, Max, Stdev), and the tool is most useful when you are working with the same data over and over.

  8. At this point you can click More in the Consolidate dialog to access the following additional settings:
    • Select Link to source data to insert the formulas that generate the results into the target range, rather than the actual results. If you link the data, any values modified in the source range are automatically updated in the target range.
    Documentation caution.png The corresponding cell references in the target range are inserted in consecutive rows, which are automatically ordered and then hidden from view. Only the final result, based on the selected function, is displayed.
    • Under Consolidate by, select either Row labels or Column labels if the cells of the source data range are not to be consolidated corresponding to the identical position of the cell in the range, but instead according to a matching row label or column label. To consolidate by row labels or column labels, the label must be contained in the selected source ranges. The text in the labels must be identical, so that rows or columns can be accurately matched. If the row or column label does not match any that exist in the target range, it is added to the target range as a new row or column.
  9. Click OK to consolidate the ranges.
  10. If you are continually working with the same range, then you probably want to use Data > Create Range to give it a name.

The consolidation ranges and target range are saved as part of the document. If you later open a document in which consolidation has been defined, this data is still available.


Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY).
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/OOo3_User_Guides/Calc_Guide/Consolidating_data&oldid=242163"
Personal tools