如何实现两个EXCEL表格相互查找并填充相应的内容Excel学习网

 

 

 

 

在关于自动互相关的讨论的第1部分中，我解释了如何在J和K列中设置以下所示的互相关数据表。

 

现在是时候设置范围A3：H17中显示的实际报告了。

 

（您可以在这里下载工作簿。）

 图一 

 互相关Excel报告

 

设置报告标题

 

上方的单元格A3包含显示的报告标题的文本；因此只需输入。

 

互相关数据该公式返回报表标题右侧的文本，用于显示的单元格：

 

H3：    =“ Shift =”＆Shift＆“，Correlation =”“＆TEXT（$ K $ 6，”＃。00“）

 

右对齐单元格H3以定位公式返回的文本。

 

每当您更改Shift值时，数据表第一行中的单元格K6都会计算该新Shift值的相关性...而不会更改数据表中的结果。 

 

因此，单元格H3中的公式可以轻松在标题区域显示Shift值及其结果相关值。

 

字幕公式会告诉读者图表显示的内容。在此处显示的单元格中输入它：

 

A4：    = IF（Shift = 0，“数据1和数据2具有相同的时间段。”，“将数据1与已移位“＆IF（Shift <0，“返回”，“提前”）的Data2比较＆”按“＆ABS（Shift）＆”月。”）

 

 

要创建图表，首先要绘制原始数据图表。也就是说，首先在数据工作表中选择范围B4：D25，如右上方所示。然后选择“插入”，“图表”，“折线”，“折线...”，这将生成一个看起来像这样的丑陋图表：

 

 

初步图表

 

要开始此图表的转换，请将图表剪切并粘贴到“报告”页面中标题区域下方的区域。

 

要将图例移至图表顶部，请右键单击图例；选择格式图例；然后在“格式选项”选项卡中，选择“顶部”；然后选择关闭。

 

现在是时候编辑您的 SERIES公式了。

 

首先，将工作簿另存为CrossCorr.xlsx，这将使以下说明更易于遵循。然后，完成后，您可以根据需要命名工作簿。

 

现在，选择图表中的蓝线，并用以下公式替换其在公式栏中的SERIES公式：

 

 

然后选择红线并将其SERIES公式替换为：

 

= SERIES（数据！$ D $ 4，CrossCorr.xlsx！g.DateText2，CrossCorr.xlsx！g.Data2,2）

 

现在，您的图表应如下所示：

 

关联图，步骤2

 

选择X轴，然后按Ctrl + 1启动其格式对话框或侧面板。在“轴选项”选项卡中，将“标签之间的间隔”设置为2个单位，然后将轴放置在“刻度线”上。

 

选择红线并按Ctrl + 1，以启动“格式对话框”或“侧面板”。在“系列选项”选项卡中，指定“辅助轴”。完成此操作后，Excel会在绘图区域的右侧显示红线的轴值。

 

选择图表中的任何项目后，选择“图表工具”，“轴”，“轴”，“辅助水平轴”，“显示从左到右轴”。完成后，Excel将在绘图区域的顶部垂直对齐显示日期。要正确对齐它们，请选择轴，然后按Ctrl + 1以启动“设置轴格式”对话框或侧面板；在“对齐”选项卡中，找到“自定义角度”设置。单击一次其向上箭头，然后单击一次其向下箭头，这会将自定义角度设置为零度。

 

然后，在“轴选项”选项卡中，将轴放置在“刻度”上。之后，您的图表应如下所示：

 

相关图3

 

现在我们需要设置颜色以标记轴。

 

选择蓝线，然后按Ctrl + 1以启动其格式对话框或侧面板。在“线条颜色”选项卡中，选择“实线”。然后指定深蓝色的颜色。单击红线，选择“实线”，然后指定深红色的颜色。

 

选择左垂直轴，然后在“主页”，“字体”组中，将字体颜色设置为“深蓝色”。然后按Ctrl + b分配粗体。然后对底部水平轴标签执行相同的操作。

 

现在，对顶部和右侧水平轴标签重复这些步骤，但是指定深红色字体而不是深蓝色。

 

要删除图表周围的边框，请单击边框，然后按Ctrl + 1以启动“设置图表区域格式”对话框或侧面板。在“边框颜色”选项卡中，选择“无线”。

 

最后，移动图表并调整其大小，使其位于报表标题下方。这样做时，它应该看起来像这样：

 

 

从F列开始的表以易于阅读的格式显示数据表中的数据。

 

首先，在显示的单元格中输入这些公式...

 

F7：     = J7 

G7：    = K7 

H7：    = K7

 

...然后将这些公式复制到其列中。

 

要格式化表中的F列，请为Shift值分配一个粗体。

 

要使用其数据栏格式化G列，请选择范围G7：G17。选择“主页”，“样式”，“条件格式”，“数据栏”，“实体填充”，“更多规则...”，这将启动“新格式规则”对话框。在条形外观下，选择负值和轴。

 

在“负值和轴设置”对话框中，对于“负条形填充颜色”设置，选择中等深灰色的填充颜色。然后选择确定，这将返回到“新格式规则”对话框。

 

在“条形外观”部分中，为填充色选择相同的中深灰色。然后选中“仅显示栏”选项，然后选择“确定”。

 

要格式化表中的H列，请选择范围H7：H17，按Ctrl + 1以启动“格式化单元格”对话框。在数字选项卡中，选择数字类别。指定两个小数位。然后选择确定。

 

标注为显示的列，进入ShiftVal 细胞F6和相关及细胞G6。为了使“ Correl”位于G和H上方，请勿合并单元格G6和H6。而是选择范围G6：H6，然后按Ctrl + 1启动“设置单元格格式”对话框。在“路线”选项卡的“水平”下拉列表框中，选择“跨选区居中”。然后选择确定。

 

最后，应用上图所示的边框，填充，字体颜色和粗体设置。