University:
Georgia Southern University
Course:
ENGR1121 | Computing in Mechanical Engineering
Academic year:

2024
Views:

340

Pages:

39
Author:

Jalen W.

Engineers often store information in lists. • a list is a collection of one or more columns of data, usually arranged next to one another. • The list may include both numerical data and alphanumeric data (i.e., words, names, and so on). • Thus, a professor may maintain a class roster in the form of a list containing student names in one column, semester averages (expressed as percentages) in the second column, and corresponding letter grades in the third column. • The columns may include headings in the first row. Each additional row will contain a complete set of data for one student; that is, a student name, semester average, and letter grade. • The information within each of these rows is often referred to as a record. Database operations • Once a list has been created, we can sort the list; that is, rearrange the rows so that the information in a designated column increases or decreases. • Or we can filter the data; that is, determine the rows for which the information in a designated column satisfies some particular criterion. • For example, most professors create class rosters with their students listed alphabetically by their last names. At the end of the semester, the professor will usually rearrange (sort) the student records by exam scores, from highest to lowest. In addition, the professor may want to retrieve (filter) the names of those students whose exam scores exceed a certain value or fall within a certain range. • Such procedures are generally referred to as database operations. CREATING A LIST IN EXCEL • identify a block of cells that can be used to store the required rows and columns. Often, it is most convenient to place the list in the upper-left portion of the worksheet so that there is room to expand the list into the worksheet (i.e., below and to the right). Each column should contain information of the same type. Each row should contain one individual record; thus, each row will include one value from each of the columns. • The top row is often used for column headings; in fact, two or more rows can be used for this purpose if the headings are lengthy. Excel is usually able to distinguish automatically between the column headings and the actual information within the columns. This distinction may be based upon differences in the data types (e.g., alphanumeric headings and numerical data) or appearance (e.g., selection of a different font; larger type; or the use of boldface, italic, or underlining in the headings). If the headings are recognized, Excel will automatically omit the column headings from its various database operations. Example 8.1 Creating a List in Excel Enter this information as a list within an Excel worksheet. Determine the population density (population per square mile) for each state, based upon the given information. Include a heading for each column SORTING DATA IN EXCEL Example 8.2 Sorting a List in Excel Click on a cell from column E within the list (in this case, cell E3). Select the Home tab within the Ribbon. Click on Sort & Filter within the Editing group, as shown in Fig. 8.2(a). Select Sort Largest to Smallest within the Sort & Filter menu sequential sorts (i.e., sorting with respect to one or more additional columns for those rows that have the same value within a given sort column) Example 8.3 Multiple Sort Criteria A computer parts supplier has the following inventory of memory modules: Enter the inventory list into an Excel worksheet. Sort the list by memory type, number of pins, and size, in that order. Use reverse alphabetical order for the memory type. Sort the number of pins in ascending (smallest to largest) order, and the size in descending (largest to smallest) order. FILTERING DATA IN EXCEL • Filtering shows only those records that satisfy the stated criteria. (The remaining records will be hidden but not deleted.) • You may use either the Simple or the Advanced filter features. filter the data using Simple • Select any cell within the list and then choose Sort & Filter/Filter from the Editing group within the Ribbon’s Home tab (see Fig. 8.2(a)). • Select any cell and then choose Filter from the Sort & Filter group within the Ribbon’s Data tab Example 8.4 Filtering a List in Excel • The 10 states having the highest population density. • Which state has its capital in Richmond. • Which states have areas exceeding 100,000 square miles. • Which states have populations between 10 and 20 million people. select an arbitrary cell within the population density list (e.g., cell F3) and then choose Filter from the Sort & Filter group within the Ribbon’s Data tab TABLES • Tables are similar to lists, and are processed in the same manner. • However, tables can be sorted, filtered, or automatically expanded with minimal effort. • They can also be given names. To convert a list to a table, first be sure that each column has a heading. To provide the table with a name, click on any cell within the table. The Design tab will then appear within the Ribbon. Select the Design tab and enter a name within the Properties group PIVOT TABLES • Ordinary lists and tables are often referred to as “flat” or “linear” because all of the information is entered in adjacent columns (or, less often, in adjacent rows). • Some lists, however, can be recast into a twodimensional structure • provide better insight into the interrelationships between the data. • lists that contain repeated data fields (i.e., multiple cells containing the same information). • These restructured two-dimensional lists are known as pivot tables. Example 8.5 Creating a Pivot Table in Excel click on any cell within the list (we could also have highlighted the entire list), and then select PivotTable from the Tables group within the Ribbon’s Insert tab. This results in the Create PivotTable dialog box drag Region from the PivotTable Fields dialog box to the portion of the empty template labeled Drop Row Fields Here drag State from the PivotTable Fields dialog box to the same area within the template Dragging Year from the PivotTable Fields menu to the portion of the template Finally, dragging Population from the PivotTable Fields dialog box to the portion of the template Pivot Charts • A pivot chart is a bar chart associated with the pivot table entries. • To create a pivot chart based upon all columns, click anywhere within the pivot table. Then, from the Ribbon’s Insert tab, select PivotChart from the Charts group. Choose from the various bar chart styles, and then click OK. The pivot chart will then appear automatically. pivot chart corresponding to the complete pivot table To create a pivot chart based upon selected columns, click on the downward-pointing arrow accompanying the overall column heading. Then select the desired columns and proceed…

Sorting and Filtering Data

of 39

Description

Struggling with your assignment and deadlines?

Free up your schedule!

Take 5 seconds to unlock