Sort List Entries
Sorting table entries is the ordering of table rows by some characteristic, for example ordering the list of names by alphabet, shops by their numbers, etc. Sorting characteristic: surname, workshop number - is called sort key. As a rule, the standard sorting is performed according to the increase (decrease) in character codes of sort keys. In some cases it is necessary to make a non-standard sort, in particular, but the names of the months or days of the week.
With standard sort , the cursor is placed in any cell in the list, the Sort command is run in the Data panel. In the Sort Range dialog box, you specify several sort keys with the ascending or descending & quot ;. The sort keys are the names of the list fields (column names of the table) (Figure 10.17).
Fig. 10.17. List sorting options
Sorting ascending is done in the following order:
• numbers - ordering from the smallest negative to the largest positive;
• date and time - ordering from the earliest to the latest date;
• text - first the numbers entered as text, and then plain text (ascending the values of the character codes);
• logical - first FALSE, and then TRUE.
Sorting in descending order is done in the reverse order, with empty cells displayed at the end of the list.
When sorting, the hidden rows or columns of the table are not moved. To sort a list of four or more keys, the list is first sorted by the three lowest keys, and then by the remaining keys in order of importance.
Filtering table entries
Selecting the list entries for the specified conditions is called filtering. There are auto filter for the simplest record selection conditions and filtering using the advanced filter for complex selection conditions.
The cursor is placed in the list area, and then the Data/Filter/AutoFilter commands convert the list field names to a list control control.
The options are selected after you press ▼ in the column headers.
Filtering conditions are specified in the form of an exact value or conditional expression (Figure 10.18). The exact value for the comparison is selected from the drop-down list box.
The advanced filter is specified by the menu commands Data/Filter/Advanced filter. Outside the main list, a condition range is generated on the list sheet or another sheet of the same or another workbook, which includes: the names of the condition columns, the condition rows for the selection of records. The composition of the column fields of the list in the range of conditions is indicated in an arbitrary order.
Filtering conditions placed in one line of the range are combined with the AND logic function; and the conditions specified in different lines - by the function OR . Empty condition lines are not allowed.
A range of conditions can contain computed criteria. In this case, it is filled in accordance with the following rules:
• the column header for the calculated condition range criterion must not match the names of the list fields (may not be filled);
• In the condition line, you enter a formula that computes the logical constant (TRUE, FALSE) relative to the first record in the list;
• The formula includes links to the cells in the list. On the left side of the formula (before the comparison sign), the cell references of the list must be relative, on the right of the formula - absolute. Links to cells outside the list must always be absolute.
Fig. 10.18. Setting conditions for a custom auto-filter
Pivot TablePivot table Excel provides the formation of summary information of various kinds without preliminary sorting of data.
The data sources for forming a summary table are:
• Excel list (DB);
• external data source in any convertible format (text file containing table data, relational database);
• multiple cell ranges for one or more worksheets for one or more workbooks;
• Another summary table.
The pivot table is created using the PivotTable Wizard, , called by the Pivot Table command in the Paste panel.
For graphical representation of data, Excel provides the user with a significant set of capabilities. You can create charts on the same worksheet as the table, or on a separate worksheet sheet called the chart sheet . A chart created on one worksheet with a table is called embedded.
To build charts in Excel, use the Chart Wizard and the Diagrams panel. The diagram wizard allows you to build several types of graphs, for each of which you can choose to modify the main version of the diagram.
The main components of the diagrams are shown in Fig. 10.19.
Depending on the selected chart type, you can get a different display of the data.
Bar charts and histograms can be used to illustrate the relationship of individual values or the dynamics of data changes over a period of time.
Graph reflects trends in data over time.
Fig. 10.19. Basic components of charts in MS Excel
Pie charts are designed to show the relationship between parts and the whole.The
Pie Chart characterizes the relationship between the numerical values of several series of data and represents two groups of numbers in the form of a single series of points, often used to represent scientific data.
Chart with areas highlights the amount of data change over time, showing the sum of the entered values, and also demonstrates the contribution of individual values to the total amount.
The ring diagram shows the contribution of each element to the total amount, but unlike a pie chart, it can contain several rows of data (each ring is a separate row).The
Pie chart allows you to compare common values from several series of data.
Surface chart is used to find the best combination of two sets of data.
The bubble diagram is a kind of a dot chart where two values determine the position of the bubble, and the third is its size.The stock chart is often used to display stock prices, exchange rates, determine temperature changes, and display scientific data.
In addition, you can build charts the so-called nonstandard type, allowing you to combine different types of views in one chart data. These charts are called mixed . You can use either one of the built-in non-standard chart types, or create your own.