如何实现两个EXCEL表格相互查找并填充相应的内容-
发布时间:2023-05-16 10:27:41
领先的指标可以帮助您更准确地进行预测。和 互相关性可以帮助您确定领先指标。这是在Excel中自动计算和显示互相关的方法。一切似乎都很简单…要改善对销售或其他衡量指标的预测,您只需找到领先指标…与关键衡量指标高度相关但有时差的衡量指标。然后,您可以使用这些领先指标作为预测的基础。但是,当您尝试使其全部工作时,这个简单的想法可能会成为巨大的挑战。Excel中的互相关例如,假设此图表中的蓝色Data1线显示您在广告上花费的钱。并假设红色的Data2行显示了您的销售额。乍一看,您似乎真的需要更改广告策略,因为……当您花费更多的广告费用时,您的销售额将大大下降,并且,…当您减少广告支出时,您的销售额就会大幅增长。如果您想更精确地进行分析,则可以使用Excel的CORREL函数来了解Data1和Data2的相关系数为-.50。也就是说,如下图所示,您的广告和销售价值在很大程度上呈负相关。但是,这还不是故事的结局。您花在广告上的钱可能是几个月后销售额的领先指标。该图显示了从该角度进行的分析…与Excel中两个月时滞的关联在此,该表显示了与十一个不同时移相关的相关性。相关性最高的版本偏移+2个月。也就是说,已经计算了相关性,其中销售(Data2)比广告支出(Data1)提前了两个月。也就是说,广告支出可能是两个月后销售业绩的良好领先指标。但是,这种解释并不是唯一的一种解释,如下图所示:互相关结果的Excel图表在这里,我们看到销售业绩可能是三个月后广告支出的领先指标。这可能是因为当销售额上升或下降时,市场营销部门决定在广告上花费更多或更少。两种解释都得到高度相关性的支持。但是现实是您必须正确解释这样的分析告诉您的内容。即使这样,分析也绝对可以为您提供决策依据的其他事实。因此,在发出该警告的情况下,让我们进行分析。互相关数据示例互相关工作簿**我的工作簿包含两个相关的工作表:数据和报表。此图显示了数据工作表Date,Data1和Data2列包含显示的值。DateText列包含一些公式,这些公式返回要在图表中显示的文本。这是显示的单元格的第一个公式:E5: = TEXT(B5,“ mmm”)&CHAR(13)&“’”&TEXT(B5,“ yy”)此公式的CHAR(13)节返回回车符。Excel不在E列中显示此字符。但是,当文本显示在图表中时,此字符会导致年份文本换行到月份文本下方的第二行。NumRows单元格返回表中的行数。这样做是因为它使用了COUNT函数,该函数仅计算数字。我们将在此工作簿中的多个动态范围名称中引用此值。这是该单元格的公式:C1: = COUNT(B:B)要命名此单元格,请选择范围B1:C1,然后按Ctrl + Shift + F3或选择“公式”,“定义的名称”,“从选择中创建”以启动“创建名称”对话框。确保仅选中左列;然后选择确定。另外,在“报告”工作表中,设置此处显示的两个单元格,然后使用“创建名称”对话框将名称Shift分配给单元格B1。(当我像Shift单元格一样设置一个具有设置的单元格时,通常会给它填充黄色。)
设置动态范围名称自动执行互相关计算的关键是设置动态范围名称,这些名称将在输入更多数据时扩展,或者根据Shift值对数据进行移位。使用NumRows单元格可以轻松设置动态范围名称,该名称会扩展为包括可能添加到上数据图中第25行以下的其他数据行。要在下面定义名字,请首先将“日期”名称的公式复制到剪贴板。然后选择“公式”,“定义的名称”,“定义的名称”(或按Ctrl + Alt + F3)以启动“新名称”对话框。在“新名称”对话框中,输入“ 日期” 作为名称;将复制的公式粘贴到“引用至编辑”框中;然后选择确定。对其余的每个名称重复此过程。日期数据1数据2日期文本= OFFSET(数据!$ B $ 4,1,0,NumRows,1)= OFFSET(数据!$ C $ 4,1,0,NumRows,1)= OFFSET(数据!$ D $ 4,1,0,NumRows,1 )= OFFSET(数据!$ E $ 4,1,0,NumRows,1)现在,您需要设置动态范围名称,以根据Shift值的符号而变化的方式移动它们引用的数据。使用“新名称”对话框来定义每个名称。s.Data1Ns.Data1Ps.Data2Ns.Data2Ps.DateText1Ns.DateText1Ps.DateText2Ns.DateText2P= OFFSET(数据1,-Shift,0,NumRows + Shift,1)= OFFSET(数据1,,0,NumRows -Shift,1)= OFFSET(数据2,0,0,NumRows + Shift,1)= OFFSET(数据2 ,Shift,0,NumRows-Shift,1)= OFFSET(DateText,-Shift,0,NumRows + Shift,1)= OFFSET(DateText,0,0,NumRows-Shift,1)= OFFSET(DateText,0,0 ,NumRows + Shift,1)= OFFSET(DateText,Shift,0,NumRows-Shift,1)在这些范围名称中,“ s”。表示名称正在 转移您的数据;“ N”表示当Shift值为负时使用该名称;“ P”表示当Shift值为正时使用该名称。最终的动态范围名称是我们绘制并在计算中使用的名称。由于Excel错误在绘制以“ c”(我们希望用于“图表”)的范围名称时存在问题,因此我们以“ g”(用于“ graph”)开始。g.Data1g.Data2g.DateText1g.DateText2= IF(Shift<0,s.Data1N,s.Data1P)= IF(Shift <0,s.Data2N,s.Data2P)= IF(Shift <0,s.DateText1N,s.DateText1P)= IF(Shift <0 0,s.DateText2N,s.DateText2P)现在,在定义了动态名称的情况下,您可以建立一个数据表来计算互相关。设置Excel数据表该图显示了整个报告区域。J和K列中的数据表计算互相关值。与Excel数据表的互相关要设置数据表,首先输入在J7:J17范围内显示的移位值。然后在显示的单元格中输入此公式:K6: = CORREL(g.Data1,g.Data2)该公式返回所示两个动态范围的相关系数。当然,这些范围会随时间变化…取决于Shift单元格中的值。接下来,选择范围J6:K17,然后选择“数据”,“数据工具”,“假设分析”,“数据表”以启动“数据表”对话框。在该对话框的“列输入单元格”中,输入…= Shift…这是包含当前值的单元格名称,该当前值表示数据移动的月数。然后,选择“确定”后,Excel将J列的每个选定值放入Shift单元格,在K6单元格中计算公式,然后将公式的结果写入K列的相邻单元格中。最后,要将范围名称分配给数据表以便于参考,请输入表底部显示的文本;选择范围J7:K18; 按Ctrl + Shift + F3; 在对话框中,确保仅选中“底部行”;然后选择确定。设置班次的数据验证列表
Shift单元格使用Excel的“数据验证列表”功能仅允许数据表中列出的Shift值。要进行设置,请首先选择单元格B1。现在选择数据,数据工具,数据验证,数据验证。在“设置”选项卡中,在“允许”部分中选择“列表”。对于Source,输入…= ShiftVal…,这是您分配给数据表中移位值列表的范围名称。
猜你喜欢
- 很多人在使用wps表格录入数据的过程中,多多少少会有一些带有小数的,如果主需要保留两位小数,该怎么设置呢?下面是小编整理的wps表格设置保留
- 建一个自己的文件夹,这样自己的文件都放在里头,不容易弄乱,找起来也方便,本文我们来操作一个练习,学习一下如何新建一个文件夹,不会的朋友可以参
- Apple Watch不仅对跟踪健康状况或使用Apple Pay有用:如果用户有兼容的Mac,还可以使用自动解锁:此功能(需要macOS S
- 经常使用电脑的用户都了解,win7系统蓝屏是常见的故障,主要是由硬件、软件、驱动等多种因素因素引起。最近有Win7系统用户在开机的时候蓝屏,
- 如果小伙伴们在手机上需要连接电脑的时候都会需要打开usb调试这项功能才能实现,但是不少小伙伴还不知道该怎么打开,今天就为大家带来了打开的方法
- 完成PPT2016中文本框的输入后,往往需要对文本框中文字的格式进行设置,包括设置文本的字体、字号和颜色等。同时,对于文本框中的文字段落,还
- 怎么解决个人所得税app人脸识别失败的问题?个人所得税app是一款官方收税管理、个税申报系统手机应用,可以给用户提供税务申报。有用户反映,在
- 很多人都用过计算机等级考试模拟软件,不过这个软件经常出现提示错误COMDLG32.OCX,这是怎么回事?系统部落为大家解答如何处理计算机等级
- 我们在使用word编辑的时候,经常需要插入表格,如果表格比较长跨页了的话,如何设置打印的时候让表头在每一页都出现呢,今天,小编就介绍一下表头
- 很多使用win10系统的用户不知道进行磁盘清理的设置在什么地方,磁盘使用久了以后想清理其中垃圾,释放磁盘空间。今天小编教大家如何在属性和控制
- Word2010怎么设置自定义外观界面?在升级换代后,Word2010文字和表格处理功能更强大,外观界面更美观,功能键的布局也更合理。下面小
- IT之家 6 月 11 日消息,据 onMSFT 报道,如果你曾经想表达对在 Word 网页版中处理的文档中留下的评论“喜欢”,那么现在可以
- 电脑win10默认网关一般是多少呢?很多小伙伴不知道。那么我们该怎么看自己的默认网关是多少呢?我们一般可以用两种简单的方式查看默认网关。接下
- Win10系统具有许多特殊功能。它是强大和有用的。例如,win10可以在普通计算机模式和平板电脑模式之间切换。让我们看看这项黑色技术是如何工
- WPS怎么画人的头像图形?WPS中想要画一个人的头像,该怎么绘制呢?下面我们就来看看详细的教程,很简单,需要的朋友可以参考下WPS文字是一款
- Win11怎么取消登录账户?现在很多用户都会在自己电脑上设置自己的账户,保护系统文件的安全,Win11系统也不例外,然而这个操作很麻烦很多用
- Win10系统一直提示通知已重置你的默认浏览器,某个应用导致你的默认浏览器设置出现问题,因此它已重置为 Microsoft Edge。这是什
- 鸿蒙系统是华为自主研制的操作系统,很多的小伙伴不了解不知道它的系统是不是基于linux,这里小编为大家带来了鸿蒙系统是否基于linux详细介
- 最近逛论坛时发现一个问题,觉得这个问题是win7用户经常会遇到的问题,就把这个问题转到这边与大家一起分享.有网友咨询:有时候,在我想要删除一
- win7用户可以使用自带的远程桌面功能控制另外一台电脑进行操作,但是有用户发现自己在远程的时候,一直卡在“正在配置远程会话”的界面,这是怎么