EXCEL函数与公式经典教程2.docVIP

目 录 一、 数据有效性 2 1-1 数据有效性桩功演练 4 1-2 公式切入口 7 1-3 管理面板简介 10 1-4 有效性应用两三例 15 二、 筛选 17 2-1 07版筛选秀-让你一见倾心 17 2-2 高级筛选(筛选不重复系列) 19 2-3 条件语法与关键字 21 2-4 关键字之似曾相识 22 2-5 移花接木，倒打一钯 27 三、 回帖补充资料 29 3-1 学生名册 29 数据有效性 初识数据有效性 印象中，名称“谁人不识君”一节中提到过数据有效性的“序列”功能。那似乎只能通过编程才能达到的下拉列表效果，常常让初学者惊喜不已，进而乐此不疲,如下图所示。 上图中，我们看到选中单元格时会弹出一个小标签来提示录入，这是“数据有效性”的“输入信息”功能，另外从下拉列表中选取数据完成录入是“有效性条件”的“序列”功能，可以通过以下步骤进行设置： 菜单入口： 03：数据-数据有效性 07：“数据”选项卡-数据有效性 序列功能 “数据有效性”就是为了帮助用户提高录入的准确率，所以可以设置“输入信息”功能，算是打个招呼，又有“出错警告”作为强制，而核心自然是设置各种有效性规则，有内置的也可自己设定逻辑。而“序列”是内置方式中比较有灵性的一个，可以通过名称或单元格引用先设定一个可选范围，在这个范围内随便选，这样就大大提高了录入的准确率和录入效率。 “数据有效性”有个特点，即善打埋伏战，但不善策反。这个啥意思呢？就是我们先设置了“数据有效性”，然后输入数据，这时“数据有效性”是会执行使命的，该“警告”就会发出警告，该“禁止”就会弹出“禁止”信息框；但是，如果事先已经输入了数据，然后再马后炮进行“有效性”设置，这个时候“数据有效性”就不作为了，就是不具策反能力。 03版，07版都这样，不过可能考虑到了这部分原因，在07版中做了弥补。具体就是可以通过菜单命令，显示所有不符合有效性条件的项目，如下图所示。 千言万语又回到同一句话，“03版非常优秀，但07版更优秀，呵呵”。从上面我们可以看出，思想上微微一动，“数据有效性”的“圈释无效数据”功能还可以当“条件格式”用呀，大家说不是？ 在“数据有效性”中嵌入函数公式一般在两个地方，第一就是“序列”的数据源，另一个就是自定义，切入口如下图所示。下次再详细絮说。 初识数据有效性附件 数据有效性桩功演练 前文已让大家演练了普及桩功、名称桩功、条件格式桩功，其中条件格式桩功比较完整的展示了各个特性。盘点一下条件格式的桩功，关键就是三点：单元格引用格式的影响、宿主单元格与参照单元格之间的位置关系、批量扩展的规律。虽然可能会造成桩功堆砌的现象，斟酌再三我还是决定把数据有效性的桩功也演示一下。有了前面的桩功基础，尤其是条件格式的桩功基础，数据有效性的桩功就非常容易理解了，用心体会就会发现实质是一样的。 数据有效性基本功能中，我一般在“序列”的数据源和“自定义”中设计公式，以使该基本功能具有更多的灵性，下面也分别加以介绍。 “序列”数据源 在“序列”功能中，公式的作用在于返回一个单元格引用区域，将这个引用区域作为“序列”的数据源，以方面使用下拉菜单点选式录入。 序列桩功1 序列桩功2 序列桩功3 贴图 自定义： 自定义中的公式和条件格式中的公式一样，都是返回一个逻辑。不同的是，在条件格式中返回逻辑TRUE时执行相应的格式，而有效性中逻辑值TRUE表示“有效”，返回逻辑值“FALSE”时则表示无效，以此来提高录入的准确率。 自定义桩功1 自定义桩功2 自定义桩功3 自定义桩功中的第二个“兼济天下”，当然啦看上去更像是“狗拿耗子”，呵呵，这个是一个思想上的突破，这一点在条件格式中也提到过。 透露一些，虽然平时也用这些技巧，但并没有完全挨个的用遍，但即使如此我也觉得这些技巧是具有相通性的，它应该是能满足的。挨个演练有时是忙不过来的，但把握其中的规律以后，通过合理的逻辑演绎可以在思想上先拓展到更广的区域也是一个方法。先有了一个思路，具体碰到问题时就有了想法。 数据有效性桩功演练附件 公式切入口 在“数据有效性”中嵌入函数公式一般在两个地方，第一就是“序列”的数据源，另一个就是“自定义”，下面就分别来简要介绍一下。 “序列”数据源 在“名称之谁人不识君”中我们提到过，“一般情况下，无法引用其他工作表中的单元格区域作为数据源”。下面我们“重蹈覆辙”一次，不过发现有新的发现，简言之又是“03很经典，07更出色”。 在上图中我们看到，虽然无法使用鼠标点选方式为“有效性”添加来自其他工作表的数据，但是在07版中可以通过手工输入的方式来实现对其他工作表数据的引用，在03中是严格不行的。所以我觉得，这就称为“略显慈善的面孔”吧，毕竟是通融了一些。怀着憧憬的心情乘胜追击，“编辑公式时也手工输入其他工作表的标签，是