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

1 目 录 一、一、 数据有效性数据有效性.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 2 一、一、数据有效性数据有效性 初识数据有效性 印象中，名称“谁人不识君”一节中提到过数据有效性的“序列”功能。那似乎只能通过编程才能达 到的下拉列表效果，常常让初学者惊喜不已，进而乐此不疲,如下图所示。 上图中，我们看到选中单元格时会弹出一个小标签来提示录入，这是“数据有效性”的“输入信息” 功能，另外从下拉列表中选取数据完成录入是“有效性条件”的“序列”功能，可以通过以下步骤进行设置： 菜单入口： 03：数据：数据-数据有效性数据有效性 07：“数据数据”选项卡选项卡-数据有效性数据有效性 序列功能序列功能 3 “数据有效性”就是为了帮助用户提高录入的准确率，所以可以设置“输入信息”功能，算是打个 招呼，又有“出错警告”作为强制，而核心自然是设置各种有效性规则，有内置的也可自己设定逻辑。而 “序列”是内置方式中比较有灵性的一个，可以通过名称或单元格引用先设定一个可选范围，在这个范围内 随便选，这样就大大提高了录入的准确率和录入效率。 “数据有效性”有个特点，即善打埋伏战，但不善策反。这个啥意思呢？就是我们先设置了“数据有 效性” ，然后输入数据，这时“数据有效性”是会执行使命的，该“警告”就会发出警告，该“禁止”就会 弹出“禁止”信息框；但是，如果事先已经输入了数据，然后再马后炮进行“有效性”设置，这个时候“数 据有效性”就不作为了，就是不具策反能力。 03 版，版，07 版都这样，不过可能考虑到了这部分原因，在版都这样，不过可能考虑到了这部分原因，在 07 版中做了弥补。具体就是可以通过菜单命版中做了弥补。具体就是可以通过菜单命 令，显示所有不符合有效性条件的项目，如下图所示。令，显示所有不符合有效性条件的项目，如下图所示。 4 千言万语又回到同一句话， “03 版非常优秀，但 07 版更优秀，呵呵” 。从上面我们可以看出，思想上微 微一动， “数据有效性”的“圈释无效数据”功能还可以当“条件格式”用呀，大家说不是？ 在“数据有效性”中嵌入函数公式一般在两个地方，第一就是“序列”的数据源，另一个就是自定义， 切入口如下图所示。下次再详细絮说。 初识数据有效性附件 .rar 1-1 数据有效性桩功演练数据有效性桩功演练 前文已让大家演练了普及桩功、名称桩功、条件格式桩功，其中条件格式桩功比较完整的展示了各个 特性。盘点一下条件格式的桩功，关键就是三点：单元格引用格式的影响、宿主单元格与参照单元格之间的 位置关系、批量扩展的规律。虽然可能会造成桩功堆砌的现象，斟酌再三我还是决定把数据有效性的桩功也 演示一下。有了前面的桩功基础，尤其是条件格式的桩功基础，数据有效性的桩功就非常容易理解了，用心 体会就会发现实质是一样的。 数据有效性基本功能中，我一般在“序列”的数据源和“自定义”中设计公式，以使该基本功能具有 更多的灵性，下面也分别加以介绍。 “序列序列”数据源数据源 在“序列”功能中，公式的作用在于返回一个单元格引用区域，将这个引用区域作为“序列”的数据 源，以方面使用下拉菜单点选式录入。 序列桩功序列桩功 1 5 序列桩功序列桩功 2 序列桩功序列桩功 3 6 贴图 自定义： 自定义中的公式和条件格式中的公式一样，都是返回一个逻辑。不同的是，在条件格式中返回逻辑 TRUE 时执行相应的格式，而有效性中逻辑值 TRUE 表示“有效” ，返回逻辑值“FALSE”时则表示无效，以此 来提高录入的准确率。 自定义桩功自定义桩功 1 自定义桩功自定义桩功 2 7 自定义桩功自定义桩功 3 自定义桩功中的第二个“兼济天下” ，当然啦看上去更像是“狗拿耗子” ，呵呵，这个是一个思想上的 突破，这一点在条件格式中也提到过。 透露一些，虽然平时也用这些技巧，但并没有完全挨个的用遍，但即使如此我也觉得这些技巧是具有 相通性的，它应该是能满足的。挨个演练有时是忙不过来的，但把握其中的规律以后，通过合理的逻辑演绎 可以在思想上先拓展到更广的区域也是一个方法。先有了一个思路，具体碰到问题时就有了想法。 数据有效性桩功演练附件 1-2 公式切入口公式切入口 8 在“数据有效性”中嵌入函数公式一般在两个地方，第一就是“序列”的数据源，另一个就是“自定 义” ，下面就分别来简要介绍一下。 “序列序列”数据源数据源 在“名称之谁人不识君”中我们提到过， “一般情况下，无法引用其他工作表中的单元格区域作为 数据源” 。下面我们“重蹈覆辙”一次，不过发现有新的发现，简言之又是“03 很经典，07 更出色” 。 在上图中我们看到，虽然无法使用鼠标点选方式为“有效性”添加来自其他工作表的数据，但是在 07 版中可以通过手工输入的方式来实现对其他工作表数据的引用，在 03 中是严格不行的。所以我觉得，这就 称为“略显慈善的面孔”吧，毕竟是通融了一些。怀着憧憬的心情乘胜追击， “编辑公式时也手工输入其他 工作表的标签，是否也能.？”并不是每一次尝试都会有收获，在“数据源文本框”中尝试输入引用其他工 作表的数据时依然被棒喝叫停，看下图所示。 那怎么办呢？有两种方法，一种致刚，一种致柔。一种致刚，一种致柔。 “致刚致刚” ，怎么解？，怎么解？就是大模大样的突破这道防线。就是使用“名称”封装公式么， “名称”号称“谁 人不识君” ，因此有了名称的通行证自然可以畅通阻了，如下图所示。 9 “致刚”的其实也不算新知识了，在“名称”一节中已经透露了，那什么又是“致柔”的呢？龙能 升能隐，升则飞腾于宇宙之间，隐则潜伏于波涛之内。我们这里就是讲把工作表标签隐藏起来。隐芥藏身， 此处的芥就是 INDIRECT 函数。INDIRECT 函数就是返回文本字符串参数所指向的单元格引用。具体“隐芥藏 身”的过程如下： 其实，真正起到“隐芥藏身”作用的是“双引号” ，双引号也是一种功能性字符，它的作用就是抹杀所 引用字符的功能性，只体现其文本字符的属性，如此工作表标签就不会上层对象所识别了。这也算是另辟蹊 径吧，但在复杂的公式中“隐芥藏身”的程度会受到考验，本人还是建议使用名称。 此外， “序列”的公式有很大限制，不能使用数组公式，这也是一大遗憾。前文鼓吹“条件格式公式” 的强大就是针对这一点的。 自定义自定义 10 有效性自定义中的公式是很强大的，这里是指它可以使用数组公式。自定义中的公式要求和条件格式 中的要求是基本一致的，就是要返回逻辑值。返回 TRUE，表示该情况有效，返回 FALSE 表示该情况无效。 逻辑要写的漂亮，一方面就是要把前面的桩功吃透，另一方面就是数组公式的功力。功力的问题以后再补吧， 首先要把桩功吃透，比如桩功中的“恩泽天下（不独扫自家门前雪） ”也即狗拿耗子的桩功尤其容易忽视。 (2010 年 7 月编辑，似乎在数组公式的判决上 EXCEL 无法识别） 2010 年 7 月编辑，发现有效性可以判决数组公式，但有些情况特别，正在求证中. 公式切入口附件 .rar 1-3 管理面板简介管理面板简介 对比 03 版和 07 版的“数据有效性”对话框，并没有发现有任何改动。唯一相关的改进是在对话框之 外，在 07 功能区上多了“圈释无效数据”和“清除无效数据标示圈”这两个按钮，如下图所示。其效果已 经在“初识有效性”中展示过，这里就不再赘述了。 其实，个人认为数据有效性还是可以有质的改进的。比如它的有效性条件可以“或”起来，这样应该 更加灵活了。 “数据有效性”对话框有四个选项卡，我们依次介绍吧。为了看上去能有点行云流水的感觉，我不免 又要堆砌点图了，呵呵（以下介绍均以 07 版为准，03 版中大致适用，但并未一一测试） 。 “设置设置”选项卡选项卡 11 “设置”选项卡中主要是一个“有效性条件”区域。单击“允许”下拉列表，可以看到有丰富的规则， 如所示，选择不同的规则，面板布局会有相应的调整，如下图所示： 当然，这一点只要大家使用一下就能有深刻的体会。那， “允许”下拉列表右侧“忽略空值”复选框又 是什么作用呢？看一下图吧，上眼 12 从上图我们可以看出，所谓的“忽略空值”是指在单元格编辑状态中退出时，对单元格内容是否为空 进行的一种判断。如果，勾选了“忽略空值”复选框，则这该情况不报错，是忽略空值的；如果取消勾选了 改该复选框，表示该情况是不允许的，是不忽略空值的。注意，这里是指在单元格编辑状态中退出时单元格 内容的情况，选中单元格，按键删除单元格内容可不在讨论之列。 明白了“忽略空值”复选框的功能，自然会将目标瞄准左下角的“对有相同设置的.”复选框感兴趣了。 我们还是先看图吧。 通过上图我们大致了解了“有效性扩展”的功能。我们可看到，当选中的单元格并没有设置有效性时， 那个“对有相同设置的.”复选框是灰色无效的。这好理解，自身没有有效性设置，就无从和自己相同了。 此外，需要强调一下的是，这里所谓的“相同设置”可不仅仅限于“设置”选项卡中的设置，而是要求整四 个选项卡（设置、输入信息、出错警告、输入法模式）内的设置都保持相同才行。 同时我们也看到，当勾选“对有相同设置的.”复选框时，所以具有相同有效性设置的单元格会被选中， 这一点有时可以利用一下，其功能就等效于“定位”菜单中的定位有效性，如下图所示。 输入信息选项卡输入信息选项卡 13 输入信息选项卡的内容还是比较容易理解，具体效果我们通过一个图一带而过吧。 出错告警选项卡出错告警选项卡 左上角“输入无效数据时显示出错警告”复选框，这个功能就非常明确了。勾选了，那么下面的设置 时有效的，取消勾选，那么下面的设置就将无效。此外， “样式”有三种类型：停止、警告、信息。停止， 14 最严，不给任何通融的机会；警告，告知不符合规则，可选选择更改或继续；信息，只起提醒作用。具体效 果我们就看图吧。 输入法模式选项卡输入法模式选项卡 输入法模式有三种，随意、打开、关闭（英文模式） 。此处，关闭（英文模式）意思是如果当前输入法 为中文，那么关闭中文输入法，使之处于英文模式。打开，如果当前输入法为中文，那么使用中文输入法， 如果当前输入法为英文，那么即使打开也不会切换至中文，详细效果见图。对于需要输入公式的单元格设置 “关闭”比较适合。 15 在输入公式时我是尽量使用鼠标点选的技巧的。在自定义名称时，我一般都是先在页面中把公式输入 完成后，才粘帖在定义名称对称框里的，因为这样可以通过 F9 等进行公式的检测。 现在我通过对公式的修改已经能做到改变不同的单元格及工作表名称，通过点击超链接可以链接到不 同的地址，但在文档中可显示的文字位置的显示文字仍然达不到示例中的效果。 全部清除全部清除 清除整个对话框中的设置。 1-4 有效性应用两三例有效性应用两三例 “学而时习之，不亦说乎” ，我们就来演练一下有效性吧。这里演练三个应用：防止无效空格录入、防 止重复录入、多重下拉菜单设计。 防止无效空格录入防止无效空格录入 这时一个比较常用的有效性控制技巧，目的是为了规范数据录入。因为无效空格一般是误输入引起的。 这里的无效空格是指位于中文字符串首部或尾部的空格，英文单词除用于间隔的单个空格之外的空格。 有一个函数 TRIM 正好可以用来清除这些空格。可惜中文的间隔空格它并不能清除，不过实际问题时我 们可以灵活应用函数来达到符合要求的目的。 根据数据有效性-自定义公式的特性：返回逻辑 TRUE 表示允许，返回逻辑 FALSE 表示禁止，我们可以来 设计公式。大家可以先自己尝试一下，如何达到这样的效果 。 目的：体会自定义公式的特点：返回一个逻辑值。 16 防止重复录入防止重复录入 一般来说，数据录入是按从上往下、从左往右的。在这个前提下我们来尝试写一个防止重复录入的有 效性公式，来体会桩功的应用，如要达到以下图所示的效果： 这里的有效性设置公式为：=COUNTIF($A$8:A8,A8)=1，当然可以用更简单的=COUNTIF($A$8:$A$15,A8)=1 来替代，不过这里就是为了让大家体会一下单元格引用方式带来的影响。 多重下拉菜单设计多重下拉菜单设计 大家看一下，如果把基础数据整理好，定义好名称以后，可以用一个简单的 indirect 函数来实现，而且 17 想几层都可以。 有效性应用两三例附件 .rar 二、二、筛选筛选 2-1 07 版筛选秀版筛选秀-让你一见倾心让你一见倾心 筛选，这是一个很强大的数据处理功能。筛选结合排序，应该是基础阶段处理数据的主要工具了。03 版的筛选已经非常强大了，但就怕“货比货”呀。我们来看一下日期序列的筛选选项在两种版本中的对比。 还需要过多的言语吗？你觉得这也没什么大不了？继续 ing. 18 嗯，也没什么大不了的，你刚才就已经意识到了？那好，继续轰炸 ing. 好了，上面也只是从日期序列来看筛选选项的，07 版中筛选选项会根据所选序列进行调整，以尽可能 地满足用户的需求。而筛选处已经进行了人性化的组合，这些都是非常实用的贴心的改进，如下图 19 再看一个小细节吧，我觉得这点真的是很有用，就是对某一个字段筛选过、排序过会留下标记，如下 图所示。这个标记真的非常贴心，尤其字段比较多，筛选过多个字段，又想把某个字段复原就显得很有用 （03 网往往找不到到底哪些字段筛选过了，可能就要去除筛选重新来过） 。 小结：07 版中将筛选和排序放在同一个下拉菜单，这就体现了“任务式”菜单的风格，给工作带来很 大的便利。 07 版中可以进行颜色排序和筛选，这一突破大大方便的用户，当然也使 03 版的一些技巧成为了“历 史” 。 07 版中一些丰富的选项更是让 03 版的同学瞠目，我想很多原本需要函数公式实现的，现在只要轻轻 一点就搞定了，这些方面 07 版做的非常好。 筛选之一见钟情附件 .rar 2-2 高级筛选高级筛选(筛选不重复系列筛选不重复系列) 前一节主要秀了一下 07 版的筛选，如果真的用心去体会一下能感觉到 07 的贴心的。很多原 本需要函数技巧实现的功能，现在被预置了，只要轻轻点击就可以了。有关这些操作以及自定义筛选的内容 大家多练习一下就可以了，相对还是比较简单的。本节主要讲一下高级筛选。 不过本人平时高级筛选用的实在不是很多，但我会将我所知道的都托盘而出。其中难免有很 多不足之处，到时请大家指正或补充吧，先谢谢了。 和函数公式相比，基本菜单功能有一个显著的优势：效率高。这一点在海量数据的时候尤其 明显。所以，我觉得能用基本菜单功能实现的，就不要强迫自己使用函数公式来解决。毕竟我们的目标是解 决问题，不是秀函数公式能力，希望你也能认同，呵呵。下面我们就来分享一下高级筛选的几个知识点。 筛选不重复记录 高级筛选的这个功能是我平时用的最多的，不过平时往往是针对单列求不重复记录的。个人 感觉， “筛选不重复记录”这个提法也许不是很恰当，在将结果复制到其他单元格时称为“合并重复记录” 可能更恰当，如果在原区域进行筛选，也许称为“筛选首次出现的记录”更恰当。当然啦，不管名称怎么取， 我们能理解他的功能就是了。现在分别演示一下对单列的“筛选不重复记录”和多列的“筛选不重复记录” 。 20 从上面的动态图我们可以看到，EXCEL 并不是把重复的项目删除了，而是把相同的多个项目合并成一个 项目了，所以有上面的评论。 上面，我们用两列来替代多列了，原理相同的。我们这里可以看到，所谓的重复不重复，这里的讲的 标的不是一个字段的元素，而是对整条记录来讲的。 另外，高级筛选有一个特点，筛选过后没有下拉按钮，以至于会出现“强隐藏”的情况。 “强 隐藏”？，什么词呀？刚编的，看看效果吧。有时“强隐藏”会干扰你的，一时想不起到底怎么形成的，还 以为是保护工作表了，呵呵。这个时候，取消筛选就可以接触“强隐藏”了。上图，看效果。 21 当然啦，上图只是提供了一种解除“强隐藏”的方式，只要把握取消“筛选”就可以了，方法不限。 关键就是要心里有这个印象，否则真的会不明白怎么无法取消隐藏了，呵呵。 条件关键字条件关键字 条件区域中可以植入公式，也可以直接使用“关键字” 。论坛中或其他资料中应该有这些关键 字的逻辑作用的。比如，,筛选非空记录，,=,=.名字自创，很不规范，大家自己注意，呵呵） 有了“屏蔽” 、 “精确筛选”的观摩，大家一定觉得“排序”运算符也就过过场，不会有什么 新意。嗯，也差不多吧，不过我们还是看看吧，也许还真能让你眼镜一亮.眼镜可要扶好了。 25 通过上图，我想大家一定明白我怎么改名成“排序运算符”了。我感觉，如果提“大于、小于”这样 的名称，会直接在脑海中发生微妙的变化，然后就限定了在“数值”上打转。用排序，就能冲开这层束缚. 这里也就是思维的一个突破。当然啦，这个我只是稍微测试过，这个经验还是比较嫩的，欢饮大家一起测试。 “大于、小于”还能对文本进行处理？跳出“筛选”条件关键字，我们回到普通的单元格， 上图，演示。 从上图我们可以看出文本确实可以进行“比较” ，大家如果感性兴趣还会发现这个比较大小的规则应该 就是排序的规则。此外，我也给大家来了一个玩笑，MAX 来求最大的文本，呵呵。从此处我们可以看出， 并不是每一个猜想都会有收获的。猜想和验证必须一步一个脚印，相辅相成。 纯纯“字符串样式字符串样式”关键字关键字 纯“字符串样式”关键字，那么很自然的通用形式就是“字符串样式”了。这里要提到一点， 很关键的， “纯字符串样式”关键字是种模糊方式，这里的模糊是指他只用来限定记录的起始段字符。于是 “纯字符串样式”的 ABC,就等于了 “精确筛选”的 =ABC*。看图吧，还是比较简单的。 26 看了上图，我们可以发现，纯字符串样式是直接可以用精确筛选来替换了，方式就是在“精确筛选” 的字符串样式后添加一个 通配符 * 就是了。 通配符通配符 通配符，呵呵。不知道该说现在才讲通配符晚了点呢，还是水到渠成呀，呵呵。见仁见智吧， 我想大家已经知道通配符的作用了吧。而且对于 ？ 代表 一个字符，* 代买任意个字符（包括空字符）这 个应该也很熟悉。现在来讲一点通配符的受限性：对数值来说，通配符没有意义。就是说如果是数值，那么 53* 并不能替代 535353，这一点在所有用统配符的地方都适用。 （准确性还需要大家一起配合验证） 。这个 是最最重要的一点了，还是看个图吧。 此外，对于这些符号还要强调一下是功能性的，就是要用半角方式输入的。 筛选至少还有三块内容，我们的函数公式还没露脸呢，呵呵。不过春节前打算暂时不更新了，有其他 事务要赶。不过也不一定哟，呵呵。 关键字之似曾相识附件 2 27 26.rar 这块内容忘了一点，就是波浪符（） （数字 1 左侧的那个） ，如果要查找统配符本身，那么就需要告诉 EXCEL 某个通配符是不能当功能性字符的，要当成普通字符，这个怎么办呢，就是在对应的通配符之前加一 个 波浪符。具体大家可以自己在附件中测试一下。权威，什么叫权威，看书看的吗？我感觉我们自己动手， 多测试，得到的东西能指导实践，禁得住考验，那么就是权威的。 2-5 移花接木，倒打一钯移花接木，倒打一钯 有了前文“关键字”的介绍，大家一定对关键字的灵活性有了切身的体会。关键字，英文 “criteria” ，这个单词大家是不是也似曾相识呢？给你 5 秒钟，呵呵。提示，参数列表。当然拉，可能大家 确实没有接触。在统计函数，SUMIF，COUNTIF 中的第二参数就是这个 criteria，关键字。是巧合吗？乌龟大 师说，There is no accident!那么他们之间有什么渊源吗？我们能用来做些什么？ 既然都是关键字，那么我们有枣没枣打它个一钯子，看看是否他们的逻辑特性是否可以嫁接 过去，就是标题所指的“移花接木” 。 展示展示 1 在上图中，我直接把高级筛选中学到的“”和“”的筛选意义直接用到了 SUMIF 函数的第二参数， criteria 中。这个确实比较简单，但能突破大家的思维。 展示展示 2 28 在上图中，我尝试了通配符?，发觉在 SUMIF 这个功能和高级筛选中不同，高级筛选中 纯字符串的 形式是模糊的，即 “表达式 a” 效果 等同于 “=表达式 a*” 。由于可以取代，所以我也一直觉得使用严禁 的 “=表达式 a*”比较好。在 SUMIF 中，即使不使用 = 也完全使用了 精确匹配，我觉得这个真是不谋而 合呀。但也给我们提了个醒，在移花接木时还是要多试试，掌握性情。 展示展示 3 在上图中，我们对代码执行了比较特殊的限定，就是要求把代码为 C,D,E,F 的对应的数据求和。对初级 的同学来说，这还是挺麻烦的，一个一个列出来自然不是最佳的，这里妙用了一把 排序 （比较运算）符， 并巧妙使用 减法 实现了 逻辑与的操作。妙处，需要好好体会。 这课，内容就这么点了。要点，用新知识去撞击老知识，让他们擦出火花，善于“移花接木” 吧，把知识用活了。 移花接木-倒打一钯附件 -.rar 29 三、三、回帖补充资料回帖补充资料 3-1 学生名册学生名册 定义名称是 Excel 里一个非常常用也非常有用的操作，他可以使你的某些操作变得更简便、简洁。看完 胡剑版主的贴子后，我有很多收获。现将自己的学习心得简单整理如下。 学生名册附件 一、指定：一、指定： 当你点击“插入名称指定”后，会弹出四个选项，如图： 他是给你选定的工作表区域指定名称的名字，你可以选择其中的一个、多个或全部选项。 比如： 30 当我们选择了定义首行后，实际上就创建了 9 个名称，按 ctrl+f3 可以看到，9 个名称的名字分别为选中 区域第一行的：编号，学校，年级，班级，姓名，语文，数学，品德，总分 而每个名称所代替的区域都是该字段下的行区域数据。 相应的其他选项也是一样，无非就是把行变成列，名称的名字从第一行变成第一列，或最后一列，最 后一行而已。 尝试一下就可以知道。 = 二、定义二、定义 “定义”用来快速定义单元格或单元格区域的名称。 当你点击“插入名称定义”后，会出现一个定义名称的对话框： 31 在“当前工作薄中的名称”输入要定义的名称的名称，如“姓名” 、 “语文”等，名称可以自己定义。 在“引用位置”处输入名称引用的单元格或单元格区域的地址，也可以将光标定格在“引用位置”框 里，拖动鼠标在工作表中进行选择！这里的引用一般情况下是用绝对引用，如果根据需要，要用相对引用的， 也可以进行相应的更改！ 还是前面的例子，如果我们只需要定义“学校”的名称，则点“插入名称定义” ，也可以按快 捷键 ctrl+f3 打开它，设置为： 点击“确定”即可完成对名称的定义。 当定义名称后，选定名称引用单元格或单元格区域时会在工具栏的名称框里显示出你所定义的名称。 当在公式里对单元格进行引用时也可