Difference between revisions of "Documentation/How Tos/Using Data Ranges, Data Sort and Data Filters"

From Apache OpenOffice Wiki
Jump to: navigation, search
m (Advanced Filter)
 
(24 intermediate revisions by 8 users not shown)
Line 1: Line 1:
{{Documentation/CheckedAccuracy|fpe}}
+
[[fr:Documentation/FR/Calc/Definir_une_plage_de_donnees]]
 +
[[ru:RU/ht/00000007]]
 +
 
 +
<div style="border:1px solid #CCAA77; background-color: #FFFFAA; padding: 4px; text-align: left; margin-bottom: 20px; width: 350px;">
 +
[[Image:documentation_needshelp.png|80px]] This article needs help
 +
----
 +
<div style="font-size:x-small;">
 +
See Talk page. [[User:TJFrazier|TJ]] ([[User_talk:TJFrazier|Talk]] | [[Special:Contributions/TJFrazier|Contribs]]) 08:35, 29 March 2011 (UTC)
 +
</div>
 +
</div><!--
 +
Eng: This pattern involved second parameter (any non-empty). This parameter is used to make paper, in which the template is applied, was not classified as "drafts".
 +
This parameter is introduced for use in the documentation page "Documentation/Dashboard/Wiki_Editing_Policy", which describes the use of this template.
 +
Application of the parameter on other pages is STRICTLY PROHIBITED!
 +
For this reason, this comment is not imposed in the instruction.
 +
 
 +
Rus: В данном шаблоне задействован второй параметр (любой непустой). Параметр применяется для того, чтобы статья, в которой применён шаблон, не была отнесена к категории "черновиков".
 +
Данный параметр введён для использования на странице документации RU/wiki_editing_policy, где описывается применение данного шаблона.
 +
Применение второго параметра на других страницах СТРОГО ЗАПРЕЩЕНО!
 +
По этой причине настоящий комментарий не вынесен в инструкцию.
 +
-->
 +
{{#if: x |&nbsp;|[[Category: Documentation/NeedsRework]]}}
 +
 
  
 
<!--
 
<!--
Line 12: Line 33:
 
//-->
 
//-->
  
 
+
=Using Data Ranges, Data Sort and Data Filters=
=Defining a Data Range for Use with the Filter and Sort Functions=
+
  
 
==Defining your Ranges==
 
==Defining your Ranges==
Line 23: Line 43:
 
# Enter your data and organize as you usually do,
 
# Enter your data and organize as you usually do,
 
# Highlight what you want to become your data range; don't forget to include your column headers,
 
# Highlight what you want to become your data range; don't forget to include your column headers,
# click on '''Data > Define range...'''<br>You will see the range that you have selected highlighted in the background.
+
# click on {{menu|Data|Define range…}}<br>You will see the range that you have selected highlighted in the background.
# Give a '''name''' to the range designated in the '''Range''' field.<br>If the range isn't what you want click the icon next to the '''Range''' field and select another range.
+
# Give a '''name''' to the range designated in the '''Range''' field.<br>If the range isn't what you want, click the icon next to the '''Range''' field and select another range.
# Click the '''More''' button to see additional options:
+
# Click the {{button|More}} button to see additional options:
 
#* Don't forget to check the box '''Contains column labels''', so that your headers are recognized when you carry out sorting
 
#* Don't forget to check the box '''Contains column labels''', so that your headers are recognized when you carry out sorting
 
#* The '''Insert or delete cells''' box allows you to insert or delete data in the range selection
 
#* The '''Insert or delete cells''' box allows you to insert or delete data in the range selection
#* The '''Keep formatting''' boy tells OpenOffice.org to keep the formatting of any new data that you might subsequently enter.  
+
#* The '''Keep formatting''' box tells {{AOo}} to keep the formatting of any new data that you might subsequently enter.  
 
#* The '''Don't save imported data''' box refers to data that you may have called from a database, so these data will not be saved with your file.
 
#* The '''Don't save imported data''' box refers to data that you may have called from a database, so these data will not be saved with your file.
# Click '''OK''' to close the dialog box and return to your document.
+
# Click {{button|OK}} to close the dialog box and return to your document.
  
You can define as many ranges as you want; all you have to do is repeat the procedure explained above, and click the '''Add''' button in the dialog box. The same applies if you want to delete a range, select the range you want and click on the corresponding button in the dialog box.
+
You can define as many ranges as you want; all you have to do is repeat the procedure explained above, and click the {{button|Add}} button in the dialog box. The same applies if you want to delete a range, select the range you want and click on the corresponding button in the dialog box.
 
{{Spacer|50}}
 
{{Spacer|50}}
  
Line 39: Line 59:
 
Once you have entered your ranges, select the one that you wish to work with as follows:
 
Once you have entered your ranges, select the one that you wish to work with as follows:
  
# Select '''Data > Select range''' from the menu.
+
# Select {{menu|Data|Select range}} from the menu.
# Select a data range and click '''OK'''<br>The data range will appear highlighted in your document.
+
# Select a data range and click {{button|OK}}<br>The data range will appear highlighted in your document.
 
{{Spacer|50}}
 
{{Spacer|50}}
  
 
==Sorting Data==
 
==Sorting Data==
  
Here is the range we're going to work on:
+
Here is the range we're going to work on:<br>
 
+
[[Image:doc_howto_datarange_testrange01.png|370px]]<br>
[[Image:doc_howto_datarange_testrange.png|370px]]
+
  
 
To sort your data:  
 
To sort your data:  
  
 
# Select the range that you want to work with
 
# Select the range that you want to work with
# Select '''Data > Sort''' from the menu<br>[[Image:doc_howto_datarange_datasort.png|350px]]<br> On the '''Sort Criteria''' tab you can define up to 3 criteria. Here, we've decided to do an ascending sort, first by Gender, then by Age.
+
# Select {{menu|Data|Sort}} from the menu<br>[[Image:doc_howto_datarange_datasort.png|350px]]<br> On the '''Sort Criteria''' tab you can define up to 3 criteria. Here, we've decided to do an ascending sort, first by Gender, then by Age.
 
# Specify the sort options on the '''Options ''' tab<br>[[Image:doc_howto_datarange_datasort-2.png|350px]]<br>All of these options will enable you to edit your sort operation in the same table or a similar table at the area you have specified previously (in our example, we chose to copy the result just below the first table).
 
# Specify the sort options on the '''Options ''' tab<br>[[Image:doc_howto_datarange_datasort-2.png|350px]]<br>All of these options will enable you to edit your sort operation in the same table or a similar table at the area you have specified previously (in our example, we chose to copy the result just below the first table).
  
Line 67: Line 86:
  
 
# Select the range that you want to work with
 
# Select the range that you want to work with
# Select '''Data > Filter > Standard Filter...''' from the menu<br>[[Image:doc_howto_datarange_standardfilter.png|460px]]
+
# Select {{menu|Data|Filter|Standard Filter…}} from the menu<br>[[Image:doc_howto_datarange_standardfilter.png|460px]]
 
#* Fill in the data field names on which you want to base your filter by selecting them in the drop-down menu.
 
#* Fill in the data field names on which you want to base your filter by selecting them in the drop-down menu.
 
#* Here again, you can use up to 3 criteria with Boolean operators (and, or) and other conditions. The value is represented by the data on which the filter is to be carried out in relation to the field name chosen.
 
#* Here again, you can use up to 3 criteria with Boolean operators (and, or) and other conditions. The value is represented by the data on which the filter is to be carried out in relation to the field name chosen.
#* The '''More''' button, enables you to edit a table, taking into account regular expressions, case sensitivity or duplicates.  
+
#* The {{button|More}} button, enables you to edit a table, taking into account regular expressions, case sensitivity or duplicates.  
  
 
Here's the result:
 
Here's the result:
Line 82: Line 101:
  
 
# Select the range of data, including column names.
 
# Select the range of data, including column names.
# Select '''Data > Filter > Autofilter''' from the menu<br>[[Image:doc_howto_datarange_autofilter.png|390px]]
+
# Select {{menu|Data|Filter|Autofilter}} from the menu<br>[[Image:doc_howto_datarange_autofilter.png|390px]]
  
 
There is a small button with an arrow next to each field name. Click on the one next to the field '''Name''' to see what it does.
 
There is a small button with an arrow next to each field name. Click on the one next to the field '''Name''' to see what it does.
Line 88: Line 107:
 
[[Image:doc_howto_datarange_autofilter-2.png|70px]]
 
[[Image:doc_howto_datarange_autofilter-2.png|70px]]
  
As you will have noticed, the list of names represents the filter criteria and you can apply them differently to each column. If you select '''Standard''', the '''Standard Filter''' dialog appears.
+
As you will have noticed, the list of names represents the filter criteria, and you can apply them differently to each column. If you select '''Standard''', the '''Standard Filter''' dialog appears.
  
 
Let's click on Dupont and observe the result obtained: The criteria corresponding to the name Dupont, and only Dupont, are displayed.
 
Let's click on Dupont and observe the result obtained: The criteria corresponding to the name Dupont, and only Dupont, are displayed.
Line 95: Line 114:
  
 
# Select the range
 
# Select the range
# Select '''Data > Filter > Autofilter''' from the menu
+
# Select {{menu|Data|Filter|Autofilter}} from the menu
 
{{Spacer|50}}
 
{{Spacer|50}}
  
Line 119: Line 138:
  
 
# Select the data range to which the filter should apply.
 
# Select the data range to which the filter should apply.
# Select '''Data > Filter > Advanced Filter''' from the menu.<br>[[Image:doc_howto_datarange_advfilter.png|440px]]
+
# Select {{menu|Data|Filter|Advanced Filter}} from the menu.<br>[[Image:doc_howto_datarange_advfilter.png|440px]]
 
# In the window that appears, select the array that you defined at rows 10 to 12 using the selection button.
 
# In the window that appears, select the array that you defined at rows 10 to 12 using the selection button.
# In the options (click '''More'''), also indicate where you want your filtered data to appear (we put it at row 17)
+
# In the options (click {{button|More}}), also indicate where you want your filtered data to appear (we put it at row 17)
# Click '''OK'''.
+
# Click {{button|OK}}.
 +
 +
{{FDL}}
  
 
+
[[Category:Calc]]
{{FDL}}
+
[[Category:Documentation/How Tos/Calc]]

Latest revision as of 13:34, 11 February 2021


Documentation needshelp.png This article needs help


See Talk page. TJ (Talk | Contribs) 08:35, 29 March 2011 (UTC)

 


Using Data Ranges, Data Sort and Data Filters

Defining your Ranges

Working with data ranges enables you to select them very easily, carry out sorting operations, or filter the data using the column and row headers, and thereby avoid risky manual data manipulation.

Doc howto datarange definerange.png
  1. Enter your data and organize as you usually do,
  2. Highlight what you want to become your data range; don't forget to include your column headers,
  3. click on Data → Define range…
    You will see the range that you have selected highlighted in the background.
  4. Give a name to the range designated in the Range field.
    If the range isn't what you want, click the icon next to the Range field and select another range.
  5. Click the  More  button to see additional options:
    • Don't forget to check the box Contains column labels, so that your headers are recognized when you carry out sorting
    • The Insert or delete cells box allows you to insert or delete data in the range selection
    • The Keep formatting box tells Apache OpenOffice to keep the formatting of any new data that you might subsequently enter.
    • The Don't save imported data box refers to data that you may have called from a database, so these data will not be saved with your file.
  6. Click  OK  to close the dialog box and return to your document.

You can define as many ranges as you want; all you have to do is repeat the procedure explained above, and click the  Add  button in the dialog box. The same applies if you want to delete a range, select the range you want and click on the corresponding button in the dialog box.

Selecting a Range

Once you have entered your ranges, select the one that you wish to work with as follows:

  1. Select Data → Select range from the menu.
  2. Select a data range and click  OK 
    The data range will appear highlighted in your document.

Sorting Data

Here is the range we're going to work on:
Doc howto datarange testrange01.png

To sort your data:

  1. Select the range that you want to work with
  2. Select Data → Sort from the menu
    Doc howto datarange datasort.png
    On the Sort Criteria tab you can define up to 3 criteria. Here, we've decided to do an ascending sort, first by Gender, then by Age.
  3. Specify the sort options on the Options tab
    Doc howto datarange datasort-2.png
    All of these options will enable you to edit your sort operation in the same table or a similar table at the area you have specified previously (in our example, we chose to copy the result just below the first table).

Here is the result :

Doc howto datarange datasortresult.png

Filtering Data

Standard Filter

The use of filters is as simple as sorting:

  1. Select the range that you want to work with
  2. Select Data → Filter → Standard Filter… from the menu
    Doc howto datarange standardfilter.png
    • Fill in the data field names on which you want to base your filter by selecting them in the drop-down menu.
    • Here again, you can use up to 3 criteria with Boolean operators (and, or) and other conditions. The value is represented by the data on which the filter is to be carried out in relation to the field name chosen.
    • The  More  button, enables you to edit a table, taking into account regular expressions, case sensitivity or duplicates.

Here's the result:

Doc howto datarange standardfilterresult.png

Autofilter

The Autofilter is slightly different from the standard filter. In order to understand what it does, let's use it and see what we get.

  1. Select the range of data, including column names.
  2. Select Data → Filter → Autofilter from the menu
    Doc howto datarange autofilter.png

There is a small button with an arrow next to each field name. Click on the one next to the field Name to see what it does.

Doc howto datarange autofilter-2.png

As you will have noticed, the list of names represents the filter criteria, and you can apply them differently to each column. If you select Standard, the Standard Filter dialog appears.

Let's click on Dupont and observe the result obtained: The criteria corresponding to the name Dupont, and only Dupont, are displayed.

To cancel the filter operation:

  1. Select the range
  2. Select Data → Filter → Autofilter from the menu

Advanced Filter

The advanced filter is a filter that lets you use more than 3 filter criteria, up to a maximum of 8.

In order to use this filter, you have to create an array in which you'll enter the criteria. Let's start from the example we already have:

Doc howto datarange testrange.png

  1. Copy the row with the field names of your range (Name, Age...) into empty cells on your sheet, for example at row 10
  2. Enter your sort criteria, under each column. Note that criteria on a same 'row' are combined by an OR, while criteria in the same column are combined as AND. In order to enter names with the equals sign, don't forget to put in quotation marks (). Here's the result

Doc howto datarange advfilterdefine.png

In this example, we are searching for people whose age is less than 25 AND (since the criterion appears under the preceding one) whose salary is greater than 35000. Here are the results after applying the filter:

Doc howto datarange advfilterresult.png

So, after having created your array

  1. Select the data range to which the filter should apply.
  2. Select Data → Filter → Advanced Filter from the menu.
    Doc howto datarange advfilter.png
  3. In the window that appears, select the array that you defined at rows 10 to 12 using the selection button.
  4. In the options (click  More ), also indicate where you want your filtered data to appear (we put it at row 17)
  5. Click  OK .
Content on this page is licensed under the GNU Free Documentation License (FDL).
Personal tools
In other languages