Excel Data Analysis 简明教程

Excel Data Analysis - Inquire

你可以使用 Inquire 来−

You can use Inquire to −

  1. Compare two workbooks.

  2. Analyze a workbook for problems or inconsistencies.

  3. View the links between workbooks.

  4. View the links between worksheets.

  5. View the relationships between cells.

  6. Clean excess cell formatting.

  7. Manage passwords.

INQUIRE 选项卡将位于功能区中。如果你在功能区上找到了 INQUIRE 选项卡，则可以跳到下一部分。

The INQUIRE tab will be on the Ribbon. If you find the INQUIRE tab on the Ribbon, you can skip to the next section.

如果你在功能区上找不到 INQUIRE 选项卡，请激活 Inquire 加载项。

If you do not find the INQUIRE tab on the Ribbon, make the Inquire Add-in active.

  1. Click File > Options.

  2. In Excel Options window, click on Add-Ins.

  3. In Manage box, click on COM Add-ins.

  4. Click Go.

inquire tab

将显示 COM 加载项对话框。

COM Add-Ins dialog box appears.

com addins

  1. Check the Inquire box.

  2. Click OK. Now, Inquire Add-In is active. You will find the INQUIRE tab on the Ribbon.

INQUIRE Commands

我们来了解一下 INQUIRE 命令。

Let us learn about the INQUIRE commands.

单击 INQUIRE 选项卡。您将找到以下命令 -

Click the INQUIRE tab. You will find the following commands −

  1. Workbook Analysis

  2. Workbook Relationship

  3. Worksheet Relationship

  4. Cell Relationship

  5. Compare Files

  6. Clean Excess Cell Formatting

  7. Workbook Passwords

inquire commands

Comparing Two Workbooks

您可以逐单元格比较两个工作簿，找出差异（如果存在），例如第二个工作簿与第一个工作簿相比发生变化。

You can compare two workbooks cell by cell and find the differences, if any, in terms of changes in the second workbook as compared to the first.

请按以下步骤操作 -

Follow the below given steps −

  1. Open two workbooks that you want to compare.

  2. Click on INQUIRE tab on the Ribbon.

  3. Click on Compare Files in the Compare group.

comparing two workbooks

  1. The Select Files To Compare dialog box appears.

  2. Check the file names displayed in the boxes next to Compare and To.

select files to compare

  1. If any File name that is displayed is not the one you wanted, click the down-arrow next to that File name.

  2. Only the workbooks that are open will be displayed.

workbooks displayed

  1. Select the File.

  2. Check if the order of Files in Compare and To is OK.

compare file

  1. If the order is not OK, click Swap Files. The order of Files in Compare and To gets changed.

  2. Click Compare.

click compare

比较结果将显示在两个窗格网格中 -

The results of the comparison appear in a two-pane grid −

  1. The workbook on the left corresponds to the "Compare" file you have chosen.

  2. The workbook on the right corresponds to the "To" file you have chosen.

工作簿对比工作簿中变更的详细信息将显示在这些两个网格下面的窗格中。变更内容将根据变更类型以颜色突出显示。突出显示颜色的图例显示在下左窗格中。

Details of changes in workbook-To as compared to Workbook-Compare appear in a pane below these two grids. The changes are highlighted by color, depending on the kind of change. The legend for the highlight colors appears in the lower-left pane.

highlight changes

单击功能区上的 Resize Cells to Fit 查看比较和到工作簿中的单元格内容。两个工作簿中的单元格均已调整大小，以便内容可见。

Click Resize Cells to Fit on the Ribbon to view the cell contents in the Compare and To workbooks. The cells in both the workbooks are resized so that the contents are visible.

resize cells to fit

在功能区的导出组中单击导出结果。

Click the Export Results in the Export group on the Ribbon.

export results

此时将出现另存为对话框。可以将结果另存为 Excel 工作簿。请注意，仅 .xlsx 文件类型可用。

The Save As dialog box appears. You can save the results to an Excel workbook. Note that only .xlsx file type is available.

save as dialog box

如果需要在其他应用程序中使用结果，可以通过复制到剪贴板来实现。

If you need the results in another application, you can do it by copying it to Clipboard.

在功能区的导出组中单击将结果复制到剪贴板。

Click Copy Results to Clipboard in the Export group on the Ribbon.

copy results

粘贴到所需的应用程序中即可。

Paste in an application you want.

Creating an Interactive Report

可以使用 Workbook Analysis 命令创建一个交互式报告，该报告可以显示关于工作簿及其结构、公式、单元格、区域和警告的详细信息。

You can use the Workbook Analysis command to create an interactive report that can show detailed information about the workbook and its Structure, Formulas, Cells, Ranges and Warnings.

  1. Click the Inquire tab on the Ribbon.

  2. Click Workbook Analysis in the Report group.

workbook analysis

在工作簿分析完成后，将显示报表。

The report will be displayed after the Workbook Analysis is completed.

workbook analysis completed

该报表包含以下六个类别 −

The Report has the following six categories −

  1. Summary − General information about the structure and content of the workbook.

  2. Workbook (with subcategories) − General workbook statistics.

  3. Formulas (with subcategories) − Specific information about formulae in the workbook.

  4. Cells (with subcategories) − Specific information about cells in the workbook.

  5. Ranges (with subcategories) − Specific information about ranges in the workbook.

  6. Warnings − Several types of warnings about workbook structure and content.

report category

选择某一类别可以获得更多有关该类别的信息。

Selecting a category gives you more information about that category.

选中公式选项。此时将显示公式的子类别。

Check the Formulas option. The sub-categories of formulas will be displayed.

check formula option

将在正在分析的工作簿中观察到以下内容 −

You will observe the following in the workbook that you are analyzing −

  1. All formulas are 224 in number.

  2. With numeric values, they are 224 in number.

  3. Click Sub-Category With numeric values.

在结果窗格中，对于带有数字值的公式的每个单元格，将显示工作表名称、单元格地址和公式。

In the Results pane, for each of the cells with formula with numeric values, worksheet name, cell address and formula are displayed.

results pane

单击 Excel 导出按钮。将出现另存为对话框。

Click the Excel Export button. The Save As dialog box appears.

excel export button

  1. Save the Report as an Excel file.

  2. Load Export File button appears next to Excel Export button.

  3. Click on Load Export File button.

load export file

保存的报告 Excel 工作簿将打开，您能清晰地查看工作簿的分析结果。

The saved Report Excel workbook opens and you can clearly view the workbook analysis results.

workbook analysis results

Viewing with Diagrams

您可以通过链接创建的互动式图表来查看工作簿关系、工作表关系和单元格关系。这些链接显示了图表中各节点之间的相互关系。您可以拖动链接或节点进行排列，并将它们对齐以查看无论您要查找什么。

You can view workbook relationships, worksheet relationships and cell relationships with interactive diagrams created by links. The links show the dependencies between the nodes in the diagram. You can drag the links or nodes to arrange them and align them to view whatever you are looking for.

Viewing Workbook Relationships

您可以使用通过文件之间的连接（链接）创建的工作簿关系图来查看工作簿相互依赖的互动式图形地图。

You can have an interactive, graphical map of workbook dependencies created by connections (links) between files using workbook relationship diagram.

图表中的链接类型可以包括其他工作簿、Access 数据库、文本文档、HTML 页面、SQL Server 数据库和其他数据源。

The types of links in the diagram can include other workbooks, Access databases, text files, HTML pages, SQL Server databases and other data sources.

  1. Click the INQUIRE tab on the Ribbon.

  2. Click Workbook Relationship in the Diagram group.

viewing workbook relationships

将显示工作簿关系图，显示与不同数据源相链接的工作簿。

The Workbook Relationship Diagram appears, showing links of the workbook with different Data Sources.

workbook relationship diagram

Viewing Worksheet Relationships

您可以使用工作表关系图创建相同工作簿中、或在其他工作簿中工作表之间的连接（链接）的互动式图形地图。

You can use Worksheet Relationship Diagram to create an interactive, graphical map of connections (links) between worksheets in the same workbook and /or worksheets in other workbooks.

  1. Click the INQUIRE tab on the Ribbon.

  2. Click Worksheet Relationship in the Diagram group.

viewing worksheet relationships

将显示工作表关系图，显示相同工作簿以及其他工作簿中工作表之间的链接。

The Worksheet Relationship Diagram appears, showing links between the worksheets in the same workbook, and in other workbooks.

二者之间的区别可以通过箭头方向来识别。

The difference between these two can be identified by the direction of the arrows.

arrow direction

Viewing Cell Relationships

你可以使用单元格关系图来获取所有链接的详细、互动地图，从选定的单元格到其他工作表甚至其他工作簿的单元格。

You can use the Cell Relationship Diagram to get a detailed, interactive map of all links from a selected cell to cells in other worksheets or even other workbooks.

  1. Click the INQUIRE tab on the Ribbon.

  2. Click Cell Relationship in the Diagram group.

cell relationship

单元格关系图选项对话框出现。

The Cell Relationship Diagram Options dialog box appears.

  1. Check Span sheets and Span workbooks.

  2. Select Trace both under Trace cell precedents and Trace cell dependents.

  3. Under Initial number of expansion levels, select limited and type 5 in the box next to it.

  4. Click OK.

cell relationship diagram

单元格关系图出现，显示基于你选择的选项的选择单元格和同一工作表、同一工作簿和其他工作簿中的单元格之间的链接。

The Cell Relationship Diagram appears, showing links between the selected cell and the cells in the same worksheet, same workbook and in other workbooks, based on the options you have chosen.

links between selected cells and cells

单击缩放。你可以清楚地查看节点。

Click Zoom. You can view the nodes clearly.

view nodes

Cleaning Excess Cell Formatting

当你发现一个工作簿加载得比较慢，或者变为了非常大的大小，它可能应用了不需要的行或列格式（例如，条件格式化包含少于15个值的整个列）。

When you find that a workbook is getting loaded slowly, or has become huge in size, it might have formatting applied to rows and/or columns that is not needed (for example, conditional formatting an entire column that has less than 15 values).

你可以使用清除多余的单元格格式化命令来删除多余的格式化并大幅减小文件大小。这还可以提升Excel的运行速度。

You can use the Clean Excess Cell Formatting command to remove excess formatting and greatly reduce the file size. This also results in improving Excel’s speed.

在清除多余的单元格格式化之前，创建你的Excel文件的备份副本，因为在某些情况下此流程可能增加你的文件大小，并且没有办法撤销更改。

Before cleaning the excess cell formatting, create a backup copy of your Excel file because there are certain cases where this process may increase your file size, and there is no way to undo the change.

  1. Click the INQUIRE tab on the Ribbon.

  2. Click Clean Excess Cell Formatting in the Miscellaneous group.

cleaning excess cell formattingformatting

清除多余的单元格格式化对话框出现。在 Apply to 框中选择所有工作表。

The Clean Excess Cell Formatting dialog box appears. Choose All Sheets in the Apply to box

choose sheets

你将收到一条关于保存更改的消息。单击确定。

You will get a message about saving changes. Click OK.

saving changes

Managing Passwords of Files

如果你为受密码保护的工作簿使用工作簿分析或比较文件命令，你无需在每次打开那些文件时输入密码。这可以通过使用密码管理器来实现。

If you are using Workbook Analysis or Compare Files commands for workbooks that are password protected, you can avoid having to type the password each time those files are opened. This is possible with using Password Manager.

  1. Click the INQUIRE tab on the Ribbon.

  2. Click Workbook Passwords in the Miscellaneous group.

managing passwords of files

密码管理器对话框出现。单击添加按钮添加你的工作簿的密码。

The Password Manager Dialog box appears. Click the Add button to add passwords of your workbooks.

password manager

还需要为已添加的密码添加密码说明。

Add password descriptions also, for the passwords you added.

add password

下次当你需要使用其中的任何文件时，你不必输入密码。

Next time when you need to use any of these files for comparing or analyzing, you do not have to key in the passwords.