动态数据有效性.xlsVIP

Sheet3 Sheet2 Sheet1 部门 部门_不重复 * 人员 人员_本部门 1.00 人员_本部门2 1.00 部门 人员 部门1 赵六 部门2 李四 王五 部门1 张三 部门2 王五 部门3 部门4 风格 回复 发动 有人 地方 高大 德国 然而 孤儿 几个 发过 看见 理解 /dispbbs.asp?boardID=1ID=162180page=1px=0 部门1 张三 理解 中间表 部门 人员 原始表 可以建立在原始表所在工作表上，也可以建立在另外一个工作表上。 可引用实际工作表区域。 如果用手工建立这个 ，那么，可以跳过第一步，直接看第二步。 第一步　建立中间表 1 部门 =OFFSET(Sheet1!$A$6,,,COUNTA(Sheet1!$A$6:$A$65536)) 部门_不重复 人员 =OFFSET(部门,,1) 定义两个名称：部门和人员，分别是原始表中部门、人员数据所在的区域。 2 * D5 =INDEX(部门,MATCH(,COUNTIF($C$5:C5,部门),)) 数组公式，按 Ctrl+Shift+Enter 结束，并把公式往右复制，具体区域大小根据实际情况作估算。 3 D6 =INDEX(人员,MATCH(,COUNTIF($D$5:D5,人员)+(部门D$5),)) 还是数组公式。比上一个公式，增加了一个部门的条件限制，注意D使用的是相对引用。 * 提取不重复值的方法很多，可根据自己的习惯或可理解程度任选其中一种。 这个公式如果你还不理解，可点击D6单元格，执行菜单：工具-公式审核-公式求值。 在公式求值对话框中，一步一步点击“求值”，观察它的分步计算过程。 * 在下面的公式中直接引用数据区域也行。 第二步　引用动态区域 动态引用 从D5单元格开始是确定的，你的中间有建立在哪里，这个单元格就在那里。 无关，我们只要有 这一步与 就能把事办了，无论你的中间表是从何而来。 $D$5:$J$5也是确定的，你的公式复制到哪，这个区域就引用到哪。 唯一不确定的，即是唯一动态的，就是列数，我们用函数Countif()可以返回一个确定的值。 COUNTIF($D$5:$J$5,*)中，第二个参数使用通配符，用俗语说，就是查找任何值， 可是，虽说是任何值，偏偏就不包含错误值，这不是我们的福气么？ 本来万事俱备，又有东风祭起。Offset()何不费事给我们返回准确的单元格区域。 =OFFSET($D$5,,,,COUNTIF($D$5:$J$5,*)) 但在某一时刻，它又是确定的，我们要做的工作，就是找出其中的规律，把动态转换成静态。 引用指定部门的包含所有人员的单元格区域 引用包含部门的单元格区域。 ★ 所谓动态区域，无非是起始行、起始列、行数、列数的不确定， 选择该区域，点击“数据-有效性”，在打开窗口中选择“序列”，输入上面的公式，确定。 如果你第一次接触这东西，请举手，很多人愿意一步一步引导你到你掌握为止。 刚才引用的是已知一行的若干列。 也别太灰心，它只有两个未知数，我们已知的有：开始行是第6行，引用区域都是1列。 提取部门的不重复值，写入D5:J5。 按部门分类，提取人员列的不重复值，写入D6:J13。 为L6:L13设置数据有效性 为L6:L13设置数据有效性，引用的是同一个区域，如本例，都是D5:G5。 现在为M6:M13设置数据有效性。 要达到的效果是，当L列改变单元格的值时，M列引用的区域也要跟着改变。 换句话说，现在引用的是中间表中某一列的若干行。你一听就明白，有两个未知数。 把公式定义为一个名称 =OFFSET(Sheet1!$D$5,,,,COUNTIF($D$5:$J$5,*)) 4 定义为名称以后，在“数据-有效性”的“来源”中，可以使用名称：“=部门_不重复”。 以M6单元格为例，依据L6的值，我们可以用Match()在D5:G5中找到它们的位置： MATCH(L6,部门_不重复,) L6变了，这个函数返回的值也跟着改变，两个值形影相随，就像我和ExcelHome一样。 =OFFSET(Sheet1!$C$6,,MATCH(L6,部门_不重复,),COUNTIF(部门,L6),1) 列确定了，假如是“部门3”－F列，接着我们要知道，这列中F6以下有效的行数是多少。 即是包含有多少“人员”，包含了多少“部门3”的人员。 换个思路想一想，我们数一数“部门”区域中有多少个“部门3”就行了。公式是： 由Offste()负责调度，让以上参数都动起来，我们把公式输入到M列看看： 像下面示例一样，我们在编辑框中用鼠标选取嵌套中的函数，按F9计算它的值， 继续计算： 还可以选取全部公式，按F9，得出最后的结果。 把公式输入到M6不是必经步骤，这样做了，你可以对公式认识得透彻一点，就这