EXCEL函数与公式经典教程2

1目录一、数据有效性..................................................................................................................................................21-1数据有效性桩功演练.........................................................................................................................................41-2公式切入口.........................................................................................................................................................71-3管理面板简介...................................................................................................................................................101-4有效性应用两三例...........................................................................................................................................15二、筛选.............................................................................................................................................................172-107版筛选秀-让你一见倾心..............................................................................................................................172-2高级筛选(筛选不重复系列).............................................................................................................................192-3条件语法与关键字...........................................................................................................................................212-4关键字之似曾相识...........................................................................................................................................222-5移花接木，倒打一钯.......................................................................................................................................27三、回帖补充资料.............................................................................................................................................293-1学生名册...........................................................................................................................................................292一、数据有效性初识数据有效性印象中，名称“谁人不识君”一节中提到过数据有效性的“序列”功能。那似乎只能通过编程才能达到的下拉列表效果，常常让初学者惊喜不已，进而乐此不疲,如下图所示。上图中，我们看到选中单元格时会弹出一个小标签来提示录入，这是“数据有效性”的“输入信息”功能，另外从下拉列表中选取数据完成录入是“有效性条件”的“序列”功能，可以通过以下步骤进行设置：菜单入口：03：数据-数据有效性07：“数据”选项卡-数据有效性序列功能3“数据有效性”就是为了帮助用户提高录入的准确率，所以可以设置“输入信息”功能，算是打个招呼，又有“出错警告”作为强制，而核心自然是设置各种有效性规则，有内置的也可自己设定逻辑。而“序列”是内置方式中比较有灵性的一个，可以通过名称或单元格引用先设定一个可选范围，在这个范围内随便选，这样就大大提高了录入的准确率和录入效率。“数据有效性”有个特点，即善打埋伏战，但不善策反。这个啥意思呢？就是我们先设置了“数据有效性”，然后输入数据，这时“数据有效性”是会执行使命的，该“警告”就会发出警告，该“禁止”就会弹出“禁止”信息框；但是，如果事先已经输入了数据，然后再马后炮进行“有效性”设置，这个时候“数据有效性”就不作为了，就是不具策反能力。03版，07版都这样，不过可能考虑到了这部分原因，在07版中做了弥补。具体就是可以通过菜单命令，显示所有不符合有效性条件的项目，如下图所示。4千言万语又回到同一句话，“03版非常优秀，但07版更优秀，呵呵”。从上面我们可以看出，思想上微微一动，“数据有效性”的“圈释无效数据”功能还可以当“条件格式”用呀，大家说不是？在“数据有效性”中嵌入函数公式一般在两个地方，第一就是“序列”的数据源，另一个就是自定义，切入口如下图所示。下次再详细絮说。初识数据有效性附件1-1数据有效性桩功演练前文已让大家演练了普及桩功、名称桩功、条件格式桩功，其中条件格式桩功比较完整的展示了各个特性。盘点一下条件格式的桩功，关键就是三点：单元格引用格式的影响、宿主单元格与参照单元格之间的位置关系、批量扩展的规律。虽然可能会造成桩功堆砌的现象，斟酌再三我还是决定把数据有效性的桩功也演示一下。有了前面的桩功基础，尤其是条件格式的桩功基础，数据有效性的桩功就非常容易理解了，用心体会就会发现实质是一样的。数据有效性基本功能中，我一般在“序列”的数据源和“自定义”中设计公式，以使该基本功能具有更多的灵性，下面也分别加以介绍。“序列”数据源在“序列”功能中，公式的作用在于返回一个单元格引用区域，将这个引用区域作为“序列”的数据源，以方面使用下拉菜单点选式录入。序列桩功15序列桩功2序列桩功36贴图自定义：自定义中的公式和条件格式中的公式一样，都是返回一个逻辑。不同的是，在条件格式中返回逻辑TRUE时执行相应的格式，而有效性中逻辑值TRUE表示“有效”，返回逻辑值“FALSE”时则表示无效，以此来提高录入的准确率。自定义桩功1自定义桩功27自定义桩功3自定义桩功中的第二个“兼济天下”，当然啦看上去更像是“狗拿耗子”，呵呵，这个是一个思想上的突破，这一点在条件格式中也提到过。透露一些，虽然平时也用这些技巧，但并没有完全挨个的用遍，但即使如此我也觉得这些技巧是具有相通性的，它应该是能满足的。挨个演练有时是忙不过来的，但把握其中的规律以后，通过合理的逻辑演绎可以在思想上先拓展到更广的区域也是一个方法。先有了一个思路，具体碰到问题时就有了想法。数据有效性桩功演练附件1-2公式切入口8在“数据有效性”中嵌入函数公式一般在两个地方，第一就是“序列”的数据源，另一个就是“自定义”，下面就分别来简要介绍一下。“序列”数据源在“名称之谁人不识君”中我们提到过，“一般情况下，无法引用其他工作表中的单元格区域作为数据源”。下面我们“重蹈覆辙”一次，不过发现有新的发现，简言之又是“03很经典，07更出色”。在上图中我们看到，虽然无法使用鼠标点选方式为“有效性”添加来自其他工作表的数据，但是在07版中可以通过手工输入的方式来实现对其他工作表数据的引用，在03中是严格不行的。所以我觉得，这就称为“略显慈善的面孔”吧，毕竟是通融了一些。怀着憧憬的心情乘胜追击，“编辑公式时也手工输入其他工作表的标签，是否也能...？”并不是每一次尝试都会有收获，在“数据源文本框”中尝试输入引用其他工作表的数据时依然被棒喝叫停，看下图所示。那怎么办呢？有两种方法，一种致刚，一种致柔。“致刚”，怎么解？就是大模大样的突破这道防线。就是使用“名称”封装公式么，“名称”号称“谁人不识君”，因此有了名称的通行证自然可以畅通阻了，如下图所示。9“致刚”的其实也不算新知识了，在“名称”一节中已经透露了，那什么又是“致柔”的呢？龙能升能隐，升则飞腾于宇宙之间，隐则潜伏于波涛之内。我们这里就是讲把工作表标签隐藏起来。隐芥藏身，此处的芥就是INDIRECT函数。INDIRECT函数就是返回文本字符串参数所指向的单元格引用。具体“隐芥藏身”的过程如下：其实，真正起到“隐芥藏身”作用的是“双引号”，双引号也是一种功能性字符，它的作用就是抹杀所引用字符的功能性，只体现其文本字符的属性，如此工作表标签就不会上层对象所识别了。这也算是另辟蹊径吧，但在复杂的公式中“隐芥藏身”的程度会受到考验，本人还是建议使用名称。此外，“序列”的公式有很大限制，不能使用数组公式，这也是一大遗憾。前文鼓吹“条件格式公式”的强大就是针对这一点的。自定义10有效性自定义中的公式是很强大的，这里是指它可以使用数组公式。自定义中的公式要求和条件格式中的要求是基本一致的，就是要返回逻辑值。返回TRUE，表示该情况有效，返回FALSE表示该情况无效。逻辑要写的漂亮，一方面就是要把前面的桩功吃透，另一方面就是数组公式的功力。功力的问题以后再补吧，首先要把桩功吃透，比如桩功中的“恩泽天下（不独扫自家门前雪）”也即狗拿耗子的桩功尤其容易忽视。(2010年7月编辑，似乎在数组公式的判决上EXCEL无法识别）2010年7月编辑，发现有效性可以判决数组公式，但有些情况特别，正在求证中....公式切入口附件1-3管理面板简介对比03版和07版的“数据有效性”对话框，并没有发现有任何改动。唯一相关的改进是在对话框之外，在07功能区上多了“圈释无效数据”和“清除无效数据标示圈”这两个按钮，如下图所示。其效果已经在“初识有效性”中展示过，这里就不再赘述了。其实，个人认为数据有效性还是可以有质的改进的。比如它的有效性条件可以“或”起来，这样应该更加灵活了。“数据有效性”对话框有四个选项卡，我们依次介绍吧。为了看上去能有点行云流水的感觉，我不免又要堆砌点图了，呵呵（以下介绍均以07版为准，03版中大致适用，但并未一一测试）。“设置”选项卡11“设置”选项卡中主要是一个“有效性条件”区域。单击“允许”下拉列表，可以看到有丰富的规则，如所示，选择不同的规则，面板布局会有相应的调整，如下图所示：当然，这一点只要大家使用一下就能有深刻的体会。那，“允许”下拉列表右侧“忽略空值”复选框又是什么作用呢？看一下图吧，上眼12从上图我们可以看出，所谓的“忽略空值”是指在单元格编辑状态中退出时，对单元格内容是否为空进行的一种判断