Excel如何进行多层次联动数据有效性设置

谢谢邀请。我是汤帅，一个PPT自由设计师，承接PPT定制。

数据有效性对于大家而言并不陌生，而且很熟悉，但是运用自如的就是比较困难。在网上一搜索高级的数据有效性需要用到VBA进行设置，这无可厚非，毕竟口令能解决的不需要花费太多时间去专研不必要的方法，两者最终结果都一样。

下拉菜单有时候因为原始数据过多，它的作用就并不是很明显。选项过于繁复，理性输入人会舍弃下拉选取数据，而是选择直接输入数据，以节省选择时间，提高工作效率。所以此时数据有效性的弊端就凸显出来，如果能根据上一个选择而联动筛选出本次所想要的数据，这是非常理想的，也是本次想向大家介绍的一种方法。

回顾一下数据有效性的设置方法，步骤：数据→数据有效性→序列，来源，框选需要的区域。

移除点击此处添加图片说明文字

图1

然后直接进入本次主体，所谓联动，就是当我第一个选择了广东省，第二个可以根据第一个选择筛选出与第一个下支的数据，第三个可以根据第二个筛选出相应的数据，以此类推。本次利用的例子是关于服饰，以类型联动出颜色，再得到相应的金额。

移除点击此处添加图片说明文字

图2

一、设置一级数据有效性

第一步是一级的款型设置，本次数据有效性利用到的公式是=OFFSET($A$2,,,COUNTA(A:A)-1)。

这个公式利用OFFSET动态提取数据，利用定位作用，而且范围是A列非空行数个数。这里OFFSET函数对行和列没有设置值，意味着不向下和不向右取值。

移除点击此处添加图片说明文字

图3

二、设置二级联动数据有效性，增加颜色。

第二步是将款式的颜色作为数据源，不过本次的颜色可能有重复，有重复的，大家可以建立一个辅助列，将此数据进行删除重复项处理。本次数据有效性利用到的公式是=OFFSET($C$1,MATCH(G2,B:B,)-1,,COUNTIF(B:B,G2))。使用OFFSET动态提取C列颜色的数据，而且更加直观。唯一缺点是重复的数据需要增加辅助列进行筛选。

移除点击此处添加图片说明文字

图4

三、设置第三级联动数据有效性，增加价钱。

第三步是将款式的价钱作为数据源，不过本次的价钱可能有重复，有重复的数据，大家可以建立辅助列，将此数据进行删除重复项处理。

本次数据有效性利用的公式是=OFFSET($D$1,MATCH(H2,C:C,)-1,,COUNTIF(C:C,H2))。

移除点击此处添加图片说明文字

图5

Excel的技能是需要大家不断练习才能掌握，不经常练习大家很容易会淡忘。希望大家能互勉共同进步。

今日互动：你觉得还有其他的思路吗？

P.P.S：觉得回答得还行，就点赞吧！

我是汤帅，知名PPT定制设计师，职场技能优秀回答者。学点新鲜有趣的职场技能，关注我准没错。

>

Excel怎么设置只提取指定行中的数据？有些时候我们需要从一个excel文件中的数据库中提取指定的行或列中的数据。例如如图示，是国内所有上市公司的行业统计。但是现在我们只需要其中部分上市公司的行业统计，我们怎么办呢，下面分享一个技巧，需要的朋友可以参考下

有些时候我们需要从一个excel文件中的数据库中提取指定的行或列中的数据。例如如图示，是国内所有上市公司的行业统计。但是现在我们只需要其中部分上市公司的行业统计，我们怎么办呢，是一个个查找，然后复制吗，当然不是。我们可以做一个表格，只需要把我们需要的公司代码列复制进去就会自动显示其行业统计情况。也就是从浩如烟云的数据中，提取需要的数据对应的行中的数据。下面就以我刚刚解决的这个问题为示例详细说明一下。

1、打开源数据文件，把首行中的对应的数据，复制行到空白位置。

2、选中J2单元格，单击编辑栏中的"插入函数“，如图示。

3、弹出”插入函数“对话框，设置其类别为”逻辑“，并选择列表框中的IF选项。然后确定。

4、在弹出的”函数参数“对话框中，设置Logical_test参数为”I2=“”，将Value_if_true设置为“”，也就是空白内容。最重要的一步来了：将Value_if_false参数框中输入“VLOOKUP(I2,A2:B2846,2),然后确定。这个步骤是什么意思呢。就是用if函数判定I2单元格中的内容是不是不是空白。若是空白，那么J2单元格也是空白。若不是空白，就执行VLOOKUP函数。在A2:B2846区域中（因为源数据不改动，所以这里用的是相对引用）查找I2单元中的内容，找到后，在J2单元格中返回所找到的内容所在的行的第二列中的数据。

5、经过第4步后，我们在I2单元中输入任意证券代码就会在J2单元中显示其行业了。那么，现在我们需要的是在I列粘贴进去我们需要的证券代码后，其后会出现源数据所对应的第一行中的内容。所以要接着第6步。

6、选中J2单元格，注意是J2，不是I2哦.(I2是我们输入证券代码的地方），鼠标移动到单元格右下角，出现十字光标时，按住鼠标左键向右拖动到O2单元格。如图

7、这个时候你就会发现原来源数据中的C2-G2单元格中的内容就会自动填充到K2-O2单元格中。这个时候，如果我们需要的数据是按源数据中的数据从上到下排列的，就可以不用修改了。直接重复第6步中的方法，把J2-O2每个单元格下拉填充就完成了。如图示，先把需要的数据列复制到I列中，然后依次下拉填充。

8、但有时候，如果我们需要的不是按照源数据中的排序，比如说，回到我们现在用的这个例子，如果在I3中，输入000002就会出错。如图示。这是为什么呢，看一下J3中的公式就明白了。j3中的函数为VLOOKUP(I3,A3:B2847,2))，也就是搜索从A3到B2487区域中的内容。没有包含A2，所以我们输入000002在源数据中找不到。所以使用这个方法前，要先对需要的数据按源数据进行排序。不然，是不准确的。

一、已经完成的数据透视表，表名为TS001，移动复制，建立副本打√，选中TS001，如下图：

二、新建数据透视表表名为TS001（2），分析-更改数据源-选择一个表或区域，由于我是选定的002的表名，所有的数据表都是统一的列内容，告诉大家个技巧直接把'001'!$A:$R手动输入更改'002'!$A:$R，确定后，马上生成数据透视内容，不用再进去重新选择行列了。如下图：

之前我们创建数据透视表时都是手动选择数据源区域，这种选择方法无法实现数据源的动态获取功能，也就是说，如果数据源新增了一行或者新增了一列，我们还要重新选择数据源。

那么有没有办法可以自动选择数据源，让其跟随数据的增加而动态选取呢？当然有，方法还不少，今天介绍三种方法，大家可以根据自己的喜好选择适合的来应用。

动态数据源获取方法一：引用数据法。该方法不仅可以实现动态的选择当前数据透视表所在的工作簿的数据源，还能引用外部数据，也就是说，即使数据源不与数据透视表在一个工作簿，也能实现源与数据表的同步更新。

引用数据法只需6个步骤就能实现数据动态选取。具体操作方法参见上图。

其中步骤5我们选择的是数据透视表所在的工作簿，如果您找不到上图所示的文件，可以通过【浏览更多】选择，也可选择其它工作簿的工作表作为数据源，由于文字表述有限，这部分我会在视频中做详细说明。

需要注意的是，不管数据源在哪个工作簿，一定要保证数据源单独放在一个工作表中，工作表中不要再有其它数据，以免影响应用效果。

注意数据源标题不要有空格、特殊字符、合并单元格等。

动态数据源获取方法二：动态表格法。通过将数据源转换成表格，充分利用表格的自动扩展功能来实现数据源动态增加，动态选取。该方法简单易操作，是初学者的不二之选。

动态表格法只需5个步骤就能实现数据动态选取。方法参见上图。选取数据源的快捷方法为：CTRL+SHIFT+→+↓。注意数据源标题不要有空格、特殊字符、合并单元格等。

创建完动态表格，我们需要给表格命名以示区别。名称可以自定义，不要包含特殊字符。

表格命名完成后，创建数据透视表时只要在原来的数据源选择区域输入表格名称即可。

动态数据源获取方法三：函数名称法。利用OFFSET函数将数据源自定义名称，也可以实现动态选取，对于函数不是很好的小伙伴慎用此法。

函数名称法结合了函数和自定义名称两个技能，对于初学者来说可能比较难理解。具体操作步骤参见上图。

录入的公式如下：

=offset(销售清单!$A$1,0,0,counta(销售清单!$A:$A),counta(销售清单!$1:$1))

这种方法是比较传统的用法，记得刚进公司第一次接触数据透视表就用的此法，当时一脸蒙圈，不过后来搞清楚了怎么回事也不是这么难。关于OFFSET 函数的具体用法，之后我会在函数应用篇做详细讲解，想要学习的小伙伴可要随时关注课程的更新哦！

在工作中经常会遇到很多表格在数据输入的时候多次重复再输入一些产品名称、部门名称、性别等内容。这时就会想着如果能在这个单元格设置一个下拉选项进行选择多好啊！可恰巧这个不会设置，怎么办呢？

Excel表格中设置数据有效性（单元格设置下拉选项）可以提高我们数据输入的准确性，可以提高我们的工作效率。到底我们要怎么样设置Excel表格中的数据有效性（表格设置下拉选项）呢？今天通过一个小技巧跟大家分享Excel表格中如何设置数据有效性（表单元格设置下拉选项），希望对朋友们在工作中有所帮助！

1、选中部门下面的空单元格或整列——点击“数据”选项，选择“数据验证”下拉菜单下方的“数据验证”

2、在“数据验证”——“验证条件”——“允许”下框——选择“序列”

3、在“数据验证”——“验证条件”——“来源”下框输入部门名称，每个数据之间用英文状态下的逗号隔开并确定

4、设置完成后看下部门下面表格中下拉菜单的效果，我们再也不需要重复去输入部门名称了，只需要通过用鼠标点击操作即可选择！这样我们就可以提高工作效率！

如果我们的数据来源比较多的话，建议朋友们选择数据源区域。

今天的Excel表格中如何设置数据有效性（单元格设置下拉选项）小技巧分享，希望对大家工作中有所帮助！欢迎加入我们群一起交流学习！

我们常常使用Excel表格管理数据，例如物料管理，在一年的物料信息表中，怎样快速了解每个季度的销售情况呢？这就会用到数据透视表，数据透视表可以很容易地排列和汇总这些复杂数据。数据透视表怎样做呢？

一、插入数据透视表

1. 比如有以下物料管理表，首先点击表格左侧的绿色小三角，这样整张表格就会被选中，接着依次点击“插入”--》“数据透视表”。

2. 这里可以更改数据区域，也就是哪些数据需要用于汇总；也可以更改数据透视表的存放位置，默认是“新工作表”，建议保持不变，直接点击“确定”。

二、创建数据透视表

1. 这样就会看到一个新工作表，工作表上方多了一个“分析”菜单，用于修改数据透视表；左下方区域将显示数据透视表汇总结果；右下方两个区域用于创建数据透视表。

2. 现在将上图区域3中的字段拖动到区域4中，如图1-4所示，拖动完毕，左侧就会显示出数据汇总结果，这个结果显示出了不同季节不同价格的物料数量。为了让图更清晰些，这里添加了一些有颜色的矩形框和线条，相同颜色的矩形框和线条表明这些区域的内容是相对应的。例如将“进价”拖动到“列”中，左侧“列标签”就会显示不同的进价。

三、修改数据透视表

1. 数据透视表有筛选按钮的地方都可以点击下拉菜单进一步筛选。

2. 上述数据透视表中统计用的是“计数”，如果想了解不同物料不同进价的数量总和，可以依次点击“值”字段下方的“计数项：数量”--》“值字段设置”--》“求和”--》“确定”，这样就能看到图1-6的结果啦。同理，其他字段也可以用相同的方法进一步设置。“筛选”、“列”、“行”下的字段也可以有两个或更多。

3. 如果数据透视表制作完，数据有了新的变化，例如增加了、删除了或修改了，怎么办呢？很简单，点击数据透视表显示区域，接着点击“分析”--》“更改数据源”--》“更改数据透视源”--》修改数据区域--》“确定”。

以上就是数据透视表的基本制作方法，希望对您有帮助。

其实excel做柱形图，但是做成好看的柱形图就要花点心思了，那么今天教大家excel怎么做柱形图之后，还会花点心思怎么设置excel柱形图，让excel柱形图变得更直观更能表现数据吧。首先我们要了解excel柱形图的基本信息需要包含横向值与纵向值，也就是说我们再excel中的数据必须包含这两个基本值才行，如图，有两个基本值，一个是姓名，另一个是对应姓名的语文成绩。

接下来我们就可以插入柱形图了，鼠标先点击选择一个空白的单元格，这个单元格可以离上面的数据远一些，不然生成柱形图后会遮挡那些数据，然后点击插入菜单下的柱形图。

但这里我们就遇到一个问题，这里的柱形图这么多，我该选择哪一个呢，其实选择哪一个不重要，关键要和你的数据相关的柱形图就可以了，如果没办法选择，这里就选择最基本的簇状柱形图吧。

插入簇状柱形图之后这个图形是空白的，因为我们还没有添加数据进去，双击新建的簇状柱形图，然后点击工具栏中的选择数据按钮。

选择数据源窗口打开后，我们直接使用鼠标在excel上选定需要添加的数据，选择后数据就会自动被添加到数据源窗口中。

选择好数据源后，我们可以直接在数据源窗口中看到添加的柱形图的行和列了，如果我们需要将行和列的数据进行对换，直接点击切换行和列按钮就可以了。

没问题后点击确定，这样我们的基本的excel柱形图就做出来了。最后就是设计这块，很简单，我们点击选择柱形图上的各个单位后，就会在上方工具栏中看到对应的设计功能，灵活使用这些设计功能让你做的柱形图更美观吧。霸气走火就是我，我就是百度经验作者：ixlxt7Xzsl7，百度经验和百家号首发原创内容分享。

　　如何使用邮件合并？以制作成绩条为例，它的操作步骤是（EXCEL2003和WORD2003版）：

　　1、在EXCEL中创建数据源。如图：

　　2、打开WORD，点击“工具”菜单、“信函与邮件”、“显示邮件合并工具栏”。

　　3、在“邮件合并工具栏”上点“打开数据源”按钮，选择已创建好的成绩表。

　　4、在WORD中制作一个成绩条的样式。如图：

　　5、在成绩条中，将光标定位在“姓名”下方的单元格中，然后点“邮件合并”工具栏上的“插入域”按钮，在弹出的窗口中选择“姓名”字段，再点“插入”。据此可依次插入相应的字段。

　　6、将光标定位在表格下方，点“邮件合并工具栏”上的“插入WORD域”、“下一记录”。如图。

　　7、选中表格及表格下的“Next Record”，复制、粘贴。有多少学生，粘贴多少表格。

　　8、点击“邮件合并工具栏”上的“查看合并数据”，可生成所有成绩条。如图。

（本文内容由百度知道网友红旗雪贡献）

设置系列产生在行或列的方法有以下两种：

方法一：

选择已经生成的图表，在“图表工具”“设计”选项下，按“切换行/列”选项，一键切换数据系列；

方法二：

1、选项已经生成的图表，单击鼠标右键，“选择数据”；

2、在“选择数据源”编辑框中，点击“切换行/列”；

3、点击后，原“图例项（系列）”与“水平（分类）轴标签”位置互换。

（本文内容由百度知道网友贡献）

Excel数据源是我们日常工作中最常使用的数据格式，在使用Tableau连接其做数据分析时，很多人会遇到这样的问题：打开的Excel跟想象的不太一样，甚至没办法直接用来做数据分析。因此，我们需要学习如何整理Excel数据源。

为了更好的完成数据分析，Tableau通常建议您连接到未格式化的数据。也就是说，数据源可能是交叉表、聚合或包含其他无关信息。如果您的数据是这样的，就需要进行整理，因为Tableau无法查看或区分各个数据点与可能与数据相关的其他信息。

在大多数情况下，如果您有这样的数据，您可以直接从Tableau连接到它，并使用数据解释器或其他清洁选项来解决数据的有问题的结构。少部分情况下，您可能需要在Tableau连接之前直接编辑Excel数据。

以下是一些解决常见的格式问题的建议：

>>>>

以交叉表格式数据透视数据

当数据格式化为交叉表格式时，表是列方向的。在面向列的表中，变量存储为列标题。但是，Tableau针对行向数据进行了优化。在面向行的表中，变量存储在行值中。

例如，假设你有一个面向列的表格，它显示了小学生的数学，科学和历史分数。

Tableau经过优化，可连接到面向行的表，其中数学、科学和历史值组织在名为“主题”的列下，每位学生的分数组织在名为“分数”的列下。您可以通过手动编辑Excel数据来旋转行中的列。或者，从Tableau连接到Excel数据，然后使用枢纽选项。

删除预先汇总的数据

数据往往可以预先聚合。也就是说，数据可以包含和、平均值、中位数等。预聚合数据的一个常见例子是小计和总计的形式。小计和总计数据从原始数据计算，但不是原始数据本身的一部分。

例如，假设您有一个包含一小组小计信息的表。

在这种情况下，需要删除预聚合的数据。要在分析中使用小计和总计，请从表中手动删除此类信息。然后，从Tableau连接Excel数据，并使用总计选项计算小计和总计。或者，从Tableau连接到Excel数据，打开数据解释器，然后使用总计选项。

删除或排除介绍性文本

作为报告传递的Excel数据可以包含介绍性文本的标题或块。由于Tableau希望在表的第一行中使用列标题或行值，所以在分析过程中可能会导致问题。

例如，假设您有一个包含报表标题和日期的表。

在这种情况下，需要删除标题和日期信息。要使用报告的标题和日期，请执行以下操作之一：

从Excel数据手动删除此信息。然后从Tableau连接到Excel数据，并使用标题选项添加报告标题;

从Tableau连接到Excel数据，打开数据解释器，然后使用标题选项;

如果您无法从Excel数据中删除此信息，请创建命名范围并从Tableau连接到命名范围。

将分层标题平铺到单个行

一般来说，Tableau只希望Excel数据中的第一行包含列标题。包含多个列标题的数据在分析过程中可能会导致问题。

例如，假设您有一个包含一个主标题和多个子标题的表。

在这种情况下，标题的层次结构必须被平坦化或去除。为此，您可以直接在Excel数据中为层次结构中的每个标题手动创建一个新列。或者，从Tableau连接到Excel数据，然后打开数据解释器。验证您的标题是否正确平坦。

确保没有空白单元格：如果为分层标题创建新列，请确保新列中的每个单元格都包含值。

虽然您可能会为每一行重复相同的值，但重要的是每行都包含将数据与存储在分层标题中的数据相关联的数据。您必须手动从Excel数据中删除空白单元格。

删除空白行

要修复空白行，您必须从Excel数据中删除空行。

添加缺少的标题

确保没有丢失列标题。 要修复缺少的标题，您必须手动将丢失的标题直接添加到Excel数据。