Excel Data Analysis 简明教程

Excel Data Analysis - Tables

Table 是结构化数据的矩形区域。其主要功能如下 −

A Table is a rectangular range of structured data. The key features are −

  1. Each row in the table corresponds to a single record of the data. Example - Employee information.

  2. Each column contains a specific piece of information. Exmaple - The columns can contain data such as name, employee number, hire date, salary, department, etc.

  3. The top row describes the information contained in each column and is referred to as header row.

  4. Each entry in the top row is referred to as column header.

table

您可以创建并使用 Excel 表格轻松管理和分析数据。此外，借助 Excel 表格，您还可以获得内置的筛选、排序和行阴影功能，以便轻松进行报告活动。

You can create and use an Excel table to manage and analyze data easily. Further, with Excel Tables you get built-in Filtering, Sorting, and Row Shading that ease your reporting activities.

此外，Excel 对执行在表格上的操作做出智能响应。例如，您在列中有公式或根据表格中的数据创建图表。当您向表格中添加更多数据（即更多的行）时，Excel 会将公式扩展到新的数据并且图表会自动展开。

Further, Excel responds to the actions performed on a table intelligently. For example, you have a formula in a column or you have created a chart based on the data in the table. When you add more data to the table (i.e., more rows), Excel extends the formula to the new data and the chart expands automatically.

Difference between Tables and Ranges

以下是表格和范围之间的差异 −

Following are the differences between a table and range −

  1. A table is a more structured way of working with data than a range.

  2. You can convert a range into a table and Excel automatically provides − a Table NameColumn Header NamesFormatting to the Data (Cell Color and Font Color) for better Visualization

表格提供了范围没有的其他功能。它们是 −

Tables provide additional features that are not available for ranges. These are −

  1. Excel provides table tools in the ribbon ranging from properties to styles.

  2. Excel automatically provides a Filter button in each column header to sort the data or filter the table such that only rows that meet your defined criteria are displayed.

  3. If you have multiple rows in a table, and you scroll down the sheet so that the header row disappears, the column letters in the worksheet are replaced by the table headers.

  4. When you place a formula in any cell in a column of the table, it gets propagated to all the cells in that column.

  5. You can use table name and column header names in the formulas, without having to use cell references or creating range names.

  6. You can extend the table size by adding more rows or more columns by clicking and dragging the small triangular control at the lower-right corner of the lower-right cell.

  7. You can create and use slicers for a table for filtering data.

您将在本章学习所有这些功能。

You will learn about all these Features in this Chapter.

Create Table

若要从工作表中的数据创建表格，请按照给定的步骤操作 −

To create a table from the data you have on the worksheet, follow the given steps −

Step 1 - 选择要包括在表中的单元格区域。单元格可以包含数据或为空。以下范围包含 290 行员工数据。数据的顶部有标题。

Step 1 − Select the Range of Cells that you want to include in the Table. Cells can contain data or can be empty. The following Range has 290 rows of employee data. The top row of the data has headers.

create table

Step 2 - 在 Insert 选项卡的“表格”组中，单击“表格”。出现 Create Table 对话框。在 Where is the data for your table? 框中选中正确的所选数据范围。

Step 2 − Under the Insert tab, in the Tables group, click Tables. The Create Table dialog box appears. Check that the data range selected in the Where is the data for your table? Box is correct.

insert tab

Step 3 - 如果所选范围的顶部有要作为表标题使用的数据，请选中 My table has headers 框。

Step 3 − Check the My table has headers box if the top row of the selected Range contains data that you want to use as the Table Headers.

Note - 如果不选中该框，则表将有标题 - Column1、Column2、…

Note − If you do not check this box, your table will have Headers – Column1, Column2, …

Step 4 - 单击“确定”。

Step 4 − Click OK.

create table dialog box

范围使用默认样式转换为表格。

Range is converted to Table with the default Style.

table with default size

Step 5 - 你还可以通过单击该范围内的任意位置并按下 Ctrl+T 将范围转换成表格。一个 Create Table 对话框会出现，然后你可以重复上面给出的步骤。

Step 5 − You can also convert a range to a table by clicking anywhere on the range and pressing Ctrl+T. A Create Table dialog box appears and then you can repeat the steps as given above.

Table Name

Excel 会为每一个创建的表格分配一个名称。

Excel assigns a name to every table that is created.

Step 1 - 若要查看刚创建的表格的名称，请单击表格，然后单击功能区上的 table tools – design 选项卡。

Step 1 − To look at the name of the table you just created, click table, click on table tools – design tab on the Ribbon.

Step 2 - 在 Properties 组的 Table Name 框中，会显示你的表名。

Step 2 − In the Properties group, in the Table Name box, your Table Name will be displayed.

table name box

Step 3 - 可编辑该表名，使其更有意义。

Step 3 − You can edit this Table Name to make it more meaningful to your data.

Step 4 - 单击表名框。清除该名称并输入 Emp_Data。

Step 4 − Click the Table Name box. Clear the Name and type Emp_Data.

Note - 命名范围的语法规则适用于表名。

Note − The syntax rules of range names are applicable to table names.

syntax rules applicable

Managing Names in a Table

可以管理表名，就像使用名称管理器管理命名范围一样。

You can manage table names just similar to how you manage range names with Name Manager.

  1. Click the Table.

  2. Click Name Manager in the Defined Names group on Formulas tab.

Name Manager 对话框会出现，你可以在工作簿中找到 Table Names 。

The Name Manager dialog box appears and you can find the Table Names in your workbook.

managing names in table

可以在 Name Manager 对话框中通过 New 选项 Edit 一个 Table Name 或添加注释。但是，你无法更改 Refers to 中的范围。

You can Edit a Table Name or add a comment with New option in the Name Manager dialog box. However, you cannot change the range in Refers to.

edit table name

可以使用列标题来使用它们进行公式、图表等。

You can Create Names with column headers to use them in formulas, charts, etc.

  1. Click the Column Header EmployeeID in the Table.

  2. Click Name Manager.

  3. Click New in the Name Manager dialog box.

将出现 New Name 对话框。

The New Name dialog box appears.

在名称框中，您可以找到列标题，在 Refers to 框中，您将找到 Emp_Data[[#Headers],[EmployeeID]] 。

In the Name box, you can find the Column Header, and in the Refers to box,you will find Emp_Data[[#Headers],[EmployeeID]].

name box

您观察到，在表中定义名称，这是快速的方式。

As you observe, this is a quick way of defining Names in a Table.

Table Headers replacing Column Letters

在表中使用更多数据行时，可能必须向下滚动以查看那些行中的数据。

When you are working with more number of rows of data in a table, you may have to scroll down to look at the data in those rows.

然而，在这样做时，您还需要表头来识别哪个值属于哪一列。Excel 会自动提供一种流畅的方法来执行此操作。在滚动浏览数据时，工作表本身的列字母会转换为表头。

However, while doing so, you also require the table headers to identify which value belongs to which column. Excel automatically provides a smooth way of doing this. As you scroll down your data, the column letters of the worksheet themselves get converted to table headers.

在下面给出的工作表中，列字母按原样显示，表头在第 2 行。从 290 行数据中可以看到 21 行。

In the worksheet given below, the column letters are appearing as they are and the table headers are in row 2. 21 rows of 290 rows of data are visible.

column letters

向下滚动以查看表行 25 至 35。表头将替换表列的列字母。其他列字母仍然保留原样。

Scroll down to see the table rows 25 – 35. The table headers will replace the column letters for the table columns. Other column letters remain as they are.

column letters replaced

Propagation of a Formula in a Table

在下面给出的表中，假设您需要包括每个员工的年龄。

In the table given below, suppose you want to include the age of each employee.

Step 1 − 在 Birthdate 列的右侧插入一列。在列标题中键入 Age。

Step 1 − Insert a column to the right of the column Birthdate. Type Age in the Column Header.

Step 2 − 在该空列中的任意单元格中，键入公式 = DAYS ([@BirthDate], TODAY ()) ，然后按 Enter。

Step 2 − In any of the Cells in that empty column, type the Formula, =DAYS ([@BirthDate], TODAY ()) and Press Enter.

formula propagation

该公式将自动传播到表中的该列的其他单元格。

The formula propagates automatically to the other cells in that column of the table.

formula propagates automatically

Resize Table

您可以调整表的大小以添加或删除行/列。

You can resize a table to add or remove rows/columns.

考虑以下包含 1-15 批次总分数的学生成绩表。

Consider the following table Student_Marks that contains Total Marks for Batches 1 - 15.

resize table

假设您想添加三个批次 16-18 和一列包含通过率。

Suppose you want to add three more batches 16 – 18 and a column containing pass percentage.

  1. Click the table.

  2. Drag the blue-color control at the lower-right, downwards to include three more rows in the table.

add batches

  1. Again drag the blue-color control at the lower-right, sideways to include one more column in the table.

您的表如下所示。您还可以检查在名称管理器对话框中包含在表中的范围 −

Your table looks as follows. You can also check the range included in the table in the Name Manager dialog box −

check range

Remove Duplicates

当您从不同来源收集数据时，您可能会出现重复的值。在继续分析之前，您需要删除重复值。

When you gather data from different sources, you probably can have duplicate values. You need to remove the duplicate values before going further with analysis.

查看以下数据，其中包含各种品牌的不同产品的相关信息。假设您希望从该数据中删除重复项。

Look at the following data where you have information about various products of various brands. Suppose, you want to remove duplicates from this data.

remove duplicates

  1. Click the table.

  2. On the DESIGN tab, click Remove Duplicates in the Tools group on the Ribbon. The Remove Duplicates dialog box appears.

design tab

列标题在“删除重复项”对话框中的“列”下显示。

The column headers appear under columns in the Remove Duplicates dialog box.

  1. Check the column headers depending on which column you want to remove the duplicates and click OK.

有关删除了多少含重复值的行列以及剩余多少唯一值，您会收到一条消息。清除后的数据将显示在表中。

You will get a message on how many rows with duplicate values are removed and how many unique values remain. The cleaned data will be displayed in the table.

cleaned data

您还可以使用功能区“数据”工作表下“编辑”组中的 Remove Duplicates 删除重复项。

You can also remove duplicates with Remove Duplicates in the Data Tools group under DATA tab on the Ribbon.

Convert to Range

您可以将表格转换为 Range 。

You can convert a table to a Range.

  1. Click the table.

  2. Click Convert to Range in the Tools group, under the Design tab on the Ribbon.

convert to range

将收到一条消息，询问您是否要将表格转换为区域。在您使用 Yes 进行确认后，该表格将被转换为区域。

You will get a message asking you if you want to convert the table to a Range. After you confirm with Yes, the table will be converted to Range.

table converted to range

Table Style Options

您可以选择多种 Table Styles 选项。如果需要突出显示行/列，可以使用这些选项。

You have several options of Table Styles to choose. These options can be used if you need to highlight a Row / Column.

table style options

您可以选中/取消选中这些框以查看您的表格的外观。最后，您可以决定哪些选项适合您的数据。

You can check / uncheck these boxes to see how your table looks. Finally, you can decide on what options suit your data.

建议 Table Style Options 仅用于突出显示数据中的重要信息，而不是使其变得花哨，在数据分析中并不需要这一点。

It is advised that the Table Style Options be used only to project important information in your data rather than making it colorful, which is not needed in data analysis.

Table Styles

您可以选择多种表格样式。根据想要在表格中显示数据所需的色调和图案，可以使用这些样式。

You have several table styles to choose from. These styles can be used depending on what color and pattern you want to display your data in the table.

table styles

将鼠标移到这些样式上以查看表格样式的预览。最后，您可以决定哪些样式适合您的数据。

Move your mouse on these styles to have a preview of your table with the styles. Finally, you can decide on what style suit your data.

建议 Table Styles 仅用于以一种展示方式突出显示数据中的重要信息，而不是使其变得花哨，在数据分析中并不需要这一点。

It is advised that the Table Styles be used only to project important information in your data in a presentable way rather than making it colorful, which is not needed in data analysis.

Slicers for Tables

如果您使用的是 Excel 2013 或 Excel 2016，您可以使用 Slicers 过滤表格中的数据。

If you are using Excel 2013 or Excel 2016, you can use Slicers for filtering data in your table.

有关如何对表格使用切片器的详细信息，请参阅本教程中 Filtering 章节。

For details on how to use Slicers for Tables, refer the chapter on Filtering in this tutorial.