EXCEL的函数与公式学习资料.doc

此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 目 录 初识函数初识函数 2 1 1 初学 07 版的相关问题 2 1 2 桩功练习 3 1 3 函数初识 3 1 4 自学函数 6 1 5 编辑公式之公式中的字符 9 1 6 编辑公式之鼠标点选 10 1 7 编辑公式之函数提示工具 11 1 8 编辑函数公式的策略 13 1 9 审核公式 15 1 10 飘逸的剪切 17 1 11 璀璨的项链 灵动的挂坠 19 1 12 格式转换 21 1 13 功能性符号的作用 24 1 14 形参表达中的玄机 25 1 15 参数类型的玄机续 31 1 16 闲谈错误值 34 1 17 闲谈错误值 续 39 1 18 玉不琢不成器 42 1 19 君君臣臣 父父子子 49 二 二 嵌入式应用开篇嵌入式应用开篇 51 2 1 名称 52 2 2 用活名称的秘密 58 2 3 再识名称 名花无主 60 2 4 再识名称之谁人不识君 64 2 5 栖息地 67 2 6 谁人不识君补充及用活名称的秘密演练 73 2 7 SUMIF 复杂逻辑解析 插曲 76 2 8 谁人不识君 相约在 公式记忆式键入 78 三 三 条件格式条件格式 79 3 1 条件格式秀 79 3 2 条件格式桩功之单元格引用格式 82 3 3 条件格式桩功 2 83 3 4 桩功点评及思维上的进阶 84 3 5 管理器简介 86 3 6 绚丽落幕 92 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 初识函数初识函数 1 1 初学初学 07 版的相关问题版的相关问题 早期版本的文件优先使用 2003 版打开 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 1 2 桩功练习桩功练习 练拳不练功 到老一场空 连太极是从桩功开始的 这里我也借鉴一下 此处介绍的这个 桩功 是贯穿整个函数公式的学练进程的 算是一种基 础的能量流 主要是要体会 领悟公式中单元格的引用格式对公式在复制 拖拉 以及日后在条件格式 数据有效性 名称中使用时的规律性 这个 是比较容易领悟的 但需要一定的演练才能将妙处谨记在心 进而能所心 所欲的使用 新建一个工作簿 并在其中一个工作表中码上如下文本 体悟公式中单元 格的不同引用方式给公式复制 拖拉带来的影响 如下图所示 2 桩功练习附件 2 r 1 3 函数初识函数初识 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 函数 是意味着我们必须编程吗 初识函数的时候我自己确实有过这样的担心 因为大学时学编程语言做的 最多的就是写函数 于是听到函数一词还是挺紧张的 不过接触函数以后 我欣喜地发现此处的函数公式不是编程 更像是一堆积木 我们要做的就 是根据逻辑将这些积木进行拼装组合 函数可以看成一个处理器或说一个黑箱 我们只要关心它的输入与输出 对里面的具体实现我们无需关心 这些黑箱能对输入进行某种处理 最后 输出结果 这些输入就叫参数 概念上类似数学里函数概念中的自变量 各参数之间用半角的逗号 来划分 输出就是函数返回值 而黑箱可以用 一对半角状态的括号 来表示 比较形象 一来是将参数包裹 起来 而来就是表达内部处理对外层函数 涉及嵌套时 来说是不可见的 是透明的 这点比较重要 当然拉 人有姓 树有名 为了对这些黑箱 起个标识作用 就在括号之前贴上了 标签 这个标签就是函数名 函 数名除用于标识外还起到一定的功能描述作用 当然啦 就像一个人回到古代 即使用尽最贴切的比喻也难以让古人对三 明治有真切的认识 最好的最直接的方式就是带块三明治 然后让古人尝 一口 然后告诉他 这就是三明治 如此 即使了解不是太全面 那也一 定真切 现在我们就来咬一口我们的三明治 拿 vlookup 函数开刀 大家可以下载附件 以更好的理解 同时看下图 请尝试对以上概念进行对号 以明晰概念 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 当然拉 这只是让大家看了下三明治的解剖图 要真正咬它一口还请劳驾 下载附件 完成里面的任务 相信这会给你带来真切的体会 学习函数 重要的一点就是练 直接去做例子 这回给你带来丰富的感 性认识 当然 光练还不够 还需要悟 悟 具体怎么做呢 最初级的 可以用自己的语言 结合其参数来描述这个函数的功能 使得看到这个函 数 就能理解各参数的意义 不用去记住整个语法 但碰到了就能知道怎 么使用 切记 不要去背 只要学会对应就行了 尽可能地减轻记忆的负 担 上眼 下图就是给大家准备的三明治 呵呵 咬它一口吧 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 3 初识函数附件 3 rar 1 4 自学函数自学函数 如果把编辑公式比作造句 那么函数好比组成句子的词汇 如果在一个句 子中遇上一两只拦路虎就会对理解造成一定影响 虽然可以揣摩 但毕竟 有点隔靴搔痒 尤其大家刚学函数 碰到这样或那样的拦路虎的几率还是 非常大的 那怎么办呢 读书时对付此类拦路虎的方式就是备一本字典 现在也差不多 不过我们 用的是帮助 怎么用呢 比较简单 比脑筋急转弯 把大象装进冰箱 要 容易那么一点 少一步 把 大象装进冰箱 大致要三步 分别为打开冰箱 将大象放进冰箱 关 上冰箱 我们这里只要两步 定位函数 调出对应的帮助 哈哈 等于没讲 呵呵 在 初识函数 中提过 函数就是一个黑箱 定位函数就是要找到那 个黑箱 而黑箱就是那一对括号 所以第一步要做的就是将光 标定位到对应函数的括号内部 此时 EXCEL 就会弹出对应的 函数提示 工具 这个东西非常重要 是编辑公式的趁手利器 就是一个 函数结 构示意 如下图所示 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 单击其中的函数名 就会弹出对应的帮助文档 如下图所示 不过现在想来 初次见面还是要重视一下 大略看一下整个语法讲解 也是好的 是否掌握倒在其次 主要混个脸熟 碍于时间 就不在上图标 示中修改了 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 当然 要查看函数帮助 还可以直接按 F1 调出帮助窗口 然后再搜索 栏中输入对应的函数名即可 当然啦 方法不在于多 大家只要选一种自 己的方式即可 本人喜欢直接在单元格公式中定位对应函数 然后单击 函数提示工具 中的函数名的方式来查看对应的帮助 查看帮助时尽量以解决当前问题为度 不要想着把帮助中的各种细节 都掌握 欲速则不达 运用函数以解决实际问题为首要 当实际应用时发 现新的问题 这时带着问题再看帮助就能牢牢的记住其中细节的意义 进 而提高对这个函数的认识 多磨砺几遍 这些细节就不需要去记了 不用 记就不会忘 要真正咬一口三明治 那么还请下载附件 直接在单元格上演练一把 里面虽然有很多函数你不认识 没有关系 你可以揣摩 也可以查找帮助 尽可能的克服这些拦路虎 碰到哪个函数就消化那个函数 当然这里的消 化层次定位在能应用其基本功能 掌握了 自学函数 的方式 那么应用时只要记住函数名就可以了 函数名一般与其功能对应 因此记忆量锐减 得到函数名我们就能顺藤摸 瓜掌握这个函数的基本用法 当然啦 最好是看到这个函数 根据函数提 示工具就能摸索着使用这个函数 如果每次都要求助帮助 那么在思路上 就会不断打断 4 自学函数附件 4 rar 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 1 5 编辑公式之公式中的字符编辑公式之公式中的字符 公式中功能性的字符 比如表征黑箱的左右括号 间隔参数的逗号 用来封装文本的双引号 等等 最好都使用半角状态的字符 虽然 有些时候 EXCEL 会帮我们进行智能的转换 但养成好习惯还是值得的 实 战时 将输入法切换至英文状态即可 而这一法则 即 功能性字符使用 半角状态的字符 不仅仅适用于公式 养成习惯必将免去不少麻烦 初学时 另一个常犯的错误就是 卖个关子 看这个公式 大家看了就 知道 也许你也同样犯过错误 呵呵 if a1 60 及格 不及格 刚开始 我左看 右看我 上看 下看 可是我怎么看都没有发现哪 里不一般 不一般 但是 Excel 返回错误值 NAME EXCEL 是说 公式里用了名称 NAME 但它怎么就没发现定义过这 个名称 问我是否搞错了 当然 刚开始 看到错误是不会去理会的 错 了就直接盯着公式看 看看哪里是否多了什么头发丝 呵呵 其实 其实 这里 及格 不及格 这两个参数少了双引号来封装 当缺少双引号封装 时 EXCEL 就会将其当成 名称 处理 而检索 名称 列表如果未发现 有此名称 就报上面的错误了 这种错误初学时会犯 即使熟练后待头昏脑胀时依旧会犯 而且还查 不出错误来 呵呵 所以这个习惯一定要养成 顺便透露一下 真正的 名称 在编辑公式时字体是彩色的 那些单元格引用也是彩色的 所以鲜 亮的一般是没有问题 我们查错误时可以把重点放在那些黑白的字符上 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 看看是否缺了双引号 另一种可能就是把函数名拼错了 不过同样符 合上面的方向 函数名都是黑白的 1 6 编辑公式之鼠标点选编辑公式之鼠标点选 编辑公式 初学函数时会发觉编辑公式是一件头疼的事情 码字 尤 其要输入大量类似 A 1 的单元格引用地址时 更是叫苦不迭 其实 编辑公式并非一兵一卒都需要纯手工输入 鼠标 函数提示工具 复制 粘贴 F3 粘贴名称 先可以不理会这个概念 等手段都可以极大的提升 编辑效率 例如上图 F1 单元格中的公式 A1 C1 E1 这是一个非常简单的公 式 但如果每个字符都需要手工输入 那么也是件繁琐的事情 当遇到公 式字符几十甚至上百时更是件苦参事 我相信很多同学不怕苦 这很好 但有些事情还出力不讨好的 对于上百字符的公式 如果纯手工码字方式 来编辑 那么输入差错就会让人崩溃 公式会不断报错 其实 这个公式可以如此编辑 F1 中输入 进入公式编辑状态 用鼠标单击单元格 A1 此时字符 A1 被直接添加至 F1 单元格中光标活动位 置 此时公式变成 A1 而活动光标顺延其后 这里的关键就是使用鼠 标单击动作替代了输入字符 A1 这样极大的提高了输入准确率 尤其当需 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 要变换单元格引用格式 结合 F4 功能键 就能简化 的录入 依照该 方法 可以比较快捷准确的录入上述公式 详见动态图 使用鼠标点选单元格来提取单元格地址 除了便捷外还有一个特点就 是格式准确 其格式准确的优点在下文介绍 INDIRECT 函数时还会讲到 是 一项非常实用的技巧 5 编辑公式附件 5 rar 1 7 编辑公式之函数提示工具编辑公式之函数提示工具 手术台可以用来砧板 但只把手术台拿来当砧板就不能物尽所用了 前文介绍 通过 函数提示工具 的函数名可以用来访问函数帮助 但 函数提示工具 的作用却远不止于此 编辑公式时 在输完函数名及其左扩号时 对应的 函数提示工具 就会弹将出来 如果没有那很可能是你输入的函数名有误 这个也很重要 不是吗 呵呵 并且第一 形式参数 字体加粗变黑 当输入 后 第二 形式参数 加粗变黑 这一点在编辑嵌套关系比较复杂的公式时相 当有用 有定位指示的作用 否则自己都会迷茫 不知道在编辑哪个内容 了 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 请看下图 注意输入函数的 时弹出对应 函数提示工具 当输入 函数的 时关闭对应的 函数提示工具 如果有 弹出外层函数的 函 数提示工具 当输入参数分隔符 号时 相应的提示工具的形式参数 字体加粗变黑 以上这点只要心细留意就会发觉 作用方向为编辑公式对 函数提示 工具 的影响 反方向的应用同样具有极强的实用性 单击 函数提示工 具 上的形式参数 就会选定对应的参数字符 这是一项非常有用的功能 不仅可以用来审查嵌套层次是否符合编辑思路 如下图所示 同时使得可以直接使用 CTRL C 来复制代码段 如果在同一公式中对某 段代码要多次使用 那么这是相当有用的 虽然可以使用鼠标来手动选中 目标代码 但这大大增加了出错的概率 而且会打断思路 这是个小技 巧 但非常实用 如下图所示 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 插一句 让我在没有 函数提示工具 的 EXCEL 版本上编辑公式 我 常常崩溃 写完公式后就被拖入无尽的查错恶梦中 F3 功能键 按 F3 功能键就能弹出当前可用的名称 名称 可以理解 为一个变量 封装了一个公式 然后就可以直接使用鼠标选取需要的名 称到正在编辑的公式中了 这么做同样是提高了录入时的准确率 同时 另一个好处依然是不会打断思路 因为不用去思考到底为某个特定功能的 公式取了什么名字 这里提到了名称 蜗牛为梦想而生 对于名称我的感觉就是 名称 为函数公式而生 大家先有个印象 以后碰到了要和名称多亲多近 搞好 关系 名称用多了 名称会保佑你的 最后再介绍一个功能键 F2 F2 的功能就是进入编辑状态 虽然在单 元格编辑公式时显示不出有什么用 当在基本菜单功能的对话框编辑公式 时就比较有用 省去了腾出手去控制鼠标进入对应公式编辑框中 这看上 去没有什么 但相信我 以后你会觉得很好用的 1 8 编辑函数公式的策略编辑函数公式的策略 关于编辑函数公式前面已讲了函数公式中的字符选用 码字 审核结 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 构的技巧 这里将一下策略 对于比较简短公式策略师用不到的 当需要 编辑的公式比较复杂 需要中间打断一下的时候策略就比较有用 这里的策略是指构建函数公式时的一种停顿处理 在编辑一个复杂的 公式时 如果没有编辑完成 但又已经编辑了很大一段 这个时候如果确 实需要停顿一下也是很正常的 但这个时候按公式编辑栏的 吧 直接报 错 按 吧 先前努力付之东流 进退不是 甚为苦恼呀 如果单纯是需要停顿 有一个比较简单的方法 方法简单 但比较解 决问题 在公式前导符 之前插入一个空格即可 如此 单元格的格 式属性就变了 原先是功能性的公式 现在就变成了普通文本 这样就为 中场休息提供了保障 也许有些同学会说 倒 这也算技巧 呵呵 确实比较简单 但还是挺有用的 第二 不是时间上需要停顿 而是思维上需要停顿 这个也是很正常 的 在写一个比较负责的条件公式时 分支多 层次深 很难一下子把公 式全都书写到位的 这个时候可以使用 空文本 的方式先来填充 或者 使用一段有意义的字符串先来占一个位 这个也是非常重要的 这样就在 思维上将一个复杂的问题切割成几块来解决 降低了思维上的难度 在编写函数公式的过程中 及时将完整的可重复利用的模块用名称封 装起来也是值得提倡的 可以先有个印象 以后介绍名称后可以尝试 在 书写复杂的公式时 你会对这句话有深一度的了解 那时很可能先把小的 逻辑模块封装好 再使用这些小模块来堆砌我们的函数公式的 此处没有补上贴切的图 征集呀 因为根据经验 后来自己是没有心 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 思处理细节的 不过也不一定 呵呵 1 9 审核公式审核公式 公式编辑完成 但 EXCEL 一个劲的报错 这个时候就需要审核公式了 这时的主要问题就是函数结构层次错误 具体的说就是那些左右括号所在 的位置和预想的不同 导致语法审核不通过 比如说 IF 函数它应该有三 个参数 那意味着它的括号内部应该只有 2 个逗号 如果由于某种查错导 致一个括号内有 4 个参数那么它就会报错了 这时就要审核函数公式的逻辑层次了 或板块层次 使用什么工具呢 函数提示工具 可以从最外层函数开始 利用单击形式参数直接选 中对应代码块的特性 查看各形式参数的结构布局是否符合要求 然后依 次逐层剥茧 很快就能找到错误所在了 这是个小技术 但非常有效 多 使用几次提高信心后效果更佳 此处征集使用该方法解决嵌套错误的实例 当然 要尽量避免这类错误还是有方法的 就是养成良好的书写习惯 每输入一个函数 输入左括号后直接输入右括号 然后再填充参数 这样 就会大大降低此类错误的发生 当然 没个人都有自己的喜欢 这个不强 求 如果审核通过 但返回值与预想有差别 那么依然使用以上手段 首 选查看函数结构是否符合要求 如果这一步通过没有问题 那么就需要使 用新式武器了 F9 功能键 这就是论坛中常提到了独孤九剑 对 F9 独孤九剑 如果没有感性认识的话 请看下图 这是热心的 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 Fehr 提供的 这里提一下 在 EXCEL 中对于公式审核有很多工具 我曾经也使用过 其中的 公式求值 但接触 F9 功能键后我就只用该工具了 理由 简单 方便 让我觉得自己是一个剑客 而不是一个机械化工具的操作员 工具 的关键不是在于它有多强大 而是用得趁手 简单有效 伏羲造八卦 只 用了 3 位二进制数字 用了最简单的符号 因为太过复杂的东西对大多数 人来说是没有意义的 这里也是一样 至少到目前为止 所有的审核 纠 错我就只使用以上这两个主要工具 又为大家节省了很多时间爱你学习哪些复杂的审核工具 好在我们只 求实战 如果考试的话估计要吃亏的 但考试真那么重要吗 F9 的使用依然是和手术台 函数提示工具 密不可分的 手术台结合 鼠标为其定位攻击方向 锁定目标 直接抹黑选定目标 然后直接 F9 执 行运算 查看是否返回错误值 或者返回值是否合理 F9 和手术台绝对是 天生一对 可谓人为鱼肉 我为刀俎呀 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 此外 作为一个剑客 还需要犀利的目光 进入单元格编辑状态时 公式中的各成分会着上五彩的霞光 同时长城内外 大河上下 目光所及 那些被公式引用的单元格及单元格区域也将淡妆浓抹 光艳照人 凭借这 些可以查看 公式的引用区域是否正确 公式成分是否有误 这里提到的工具相当简单 但假以时日不断磨练很快就能练就一身厉 害的厨艺 为你的函数公式历程增添光彩 需补充一些图片让其更加精彩 配合使用 函数提示工具 和 F9 可以将逐层对逻辑块求值 这里可能 又涉及一个回退的问题 就是怎么将 F9 求值结果返回为原始公式代码 记得可以使用 CTRL Z 另一个更彻底地是按公式编辑栏中的 叉叉 这样 一次性返回到原始状态 即纯函数公式状态 其中那个 单击 叉叉 可 以用 ESC 键替代 这些都是小技巧 但很实用 1 10 飘逸的剪切飘逸的剪切 在前文桩功练习中 我们充分体会了公式复制时 其引用的单元格地 址会根据不同的引用方式而随之变化 经过 函数初识 篇的附件练习 相信这一特性你已经掌握得差不多了 这里再分享一个有趣的事情 以 剪切 粘贴 方式 记住是剪切而不是复制 来对数据源进行重新布局 这个时候我们会发现这个动作居然可以直接修改宿主单元格的公式 奇哉 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 怪哉 也真是太好了 不需要因为稍稍的变动而一一修改目标公式了 如图所示 看到这个现象 你有什么想法吗 感觉 奇怪 奇怪 但就是没有 什么想法 这里可能蕴藏着很深沉 很本质的东西 大家可以想想 猜想 遐想 当然 这个对于成为函数高手没有多大关系 但有点想象力总是好 的 生活就不会无趣了 猜想的东西往往是错的 但只有大胆的猜想才能发现新的东西 这个 是更重要的 这里我抛砖引玉一下 这个也是刚刚想到的 1 目标公式在提取引用单元格的值时 并不是直接从工作表上按地址行 列来取的 2 引用单元格和目标公式之间是通过第三方关联起来的 这个第三方估 计是内存 3 引用单元格 1 链接 1 对应内存 链接 2 目标公式 剪切时只是链接 1 得到了更新 链接 2 是没有断的 所以公式能自动更新单元格引用地址 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 4 内存中一定包含行列信息 仅供参考与讨论 本节 大家只要感受一下剪切的飘逸即可 到时省得大面积修改公式 希望你喜欢 6 飘逸的剪切附件 6 rar 1 11 璀璨的项链 灵动的挂坠璀璨的项链 灵动的挂坠 此处征集赞美词 先胡诌一段 你是不世的红绳 为珍珠玛瑙牵线 你是绝世的搭扣 成就璀璨的珠 链 你是电 你是光 你是唯一的神话 我只爱你 you are my super star 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 是你 成就了函数公式的节节贯穿 变幻莫测 充满灵气 先罗列一下在函数公式中可以出现的独立成分 函数 运算式 单元 格引用 名称 字符串 及运算符等 除 运算符 以外 其他成分可以 直接使用 2 3 4 5 6 7 8 9 10 5 想想 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 应该也对吧 但 EXCEL 就会报错 这个时候你 就很快就会意识到可能第一参数直接写这样的 常数数组 是不对的 这当然是得益于归一化原则的好处 就这里一个地方不同 人家用单元 格地址是对的 你用常数数组不对 那就是常数数组不对了 而这里的 根源就是 COUNTIF 的第一参数是要求 range 的 这个时候可能有朋友会说 不错不错 但这样的错误一般不会去犯 恩 这是最直接的情况 当然很少回去犯 即使犯了也很快会意识到错误 所在 但完成一个任务 函数经常是像积木一样灵活组合的 就是说 那 个常数数组你是不会直接去用 但如果嵌套时 COUNTIF 的第一参数是由 其他函数的返回值充当的 这个时候这个错误就具有隐秘性了 不太容易 发现 如 我们使用嵌套 依然是条件计数 COUNTIF OFFSET A1 10 5 这里 OFFSET 函数返回的是一个单元格区域引用 所以结果正确 OFFSET 如果不熟悉 那么建议使用前文介绍 自学函数 一文自我修炼一下 这 个函数很灵活 在初级阶段你回爱上他的 呵呵 另一个公式 countif match A1 A10 A1 A10 0 5 这里 MATCH 函 数返回结果作为 COUNTIF 的第一参 数 但这个返回结果的属性是纯数组 没有单元格引用的属性 所以 报错 在这个情况下要迅速扎到出错根源相对就难了 需要一定的积累 所 以 我觉得通过本节大家多留心 知道有这么回事 那么出现此类错误时 可能就会想到了 那就会大大节省你的时间 本节就是留心留意函数参数类型 重点关注 range reference 的参数类 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 型 这些是有限制的 爱她 更要了解她 强迫她做他不能做的事情显然 是要搞僵的 呵呵 另外 本节提到的 归一性 法则很有用 尤其在纠错 统计分析某些 规律的时候 续 形参类型为 array 数组 vector 向量 其实就是一维的数组 就是说 也是数组 就比较灵活了 用单元格区域引用可以 用常数数组可以 用 数组公式传递的内存数组也可以 这就大大的加强了这类函数的功能 因 为函数要实现某种功能主要还是要组合运用的 要实现复杂的功能的更是 数组来数组去的 因此参数是否能接受内存数组将大大影响这个函数在数 组公式应用中的地位的 vlookup lookup value table array col index num range lookup 这里 的 table array 就比较好伺候了 大家可以看下图 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 上图表达的是根据 姓名 返回 学号 这里函数有点吃不透不要紧 这 里的重点是说 array 类型的参数比较好招呼 这里用 CHOOSE 函数返回了 一个 array 完成的任务是让 姓名 位于第一列 功能性描述的参数也不限定非得使用单元格区域 使用功能性的词来 描述参数也许正是因为 Excel 没有必要强调他非得是单元格引用吧 这类 参数可以试探性使用常数数组或内存数组以实现数组应用 这话听上去没 有什么 但看一下你会比较有感觉的 本贴续的部分两个公式都比较难 有兴致的同学可以揣摩一下 如果 有点领悟也算是数组公式有点启蒙了 呵呵 上面列了一个 VLOOKUP 函数的语法结构提示 也许有同学会感叹 胡 版好耐心 一个一个码字 而且丝毫不差 佩服 其实这里有一个 快捷方式将这个 函数提示公式 直接复制到单元格的 具体过程就是在调 出 函数提示工具 时按 组合键 如下图 上眼 呵呵 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 上图中应改为组合键 参数类型附件 1 15 参数类型的玄机续 参数类型的玄机续 形参类型为 array 数组 vector 向量 其实就是一维的数组 就是说 也是数组 就比较灵活了 用单元格区域引用可以 用常数数组可以 用 数组公式传递的内存数组也可以 这就大大的加强了这类函数的功能 因 为函数要实现某种功能主要还是要组合运用的 要实现复杂的功能的更是 数组来数组去的 因此参数是否能接受内存数组将大大影响这个函数在数 组公式应用中的地位的 vlookup lookup value table array col index num range lookup 这里 的 table array 就比较好伺候了 大家可以看下图 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 上图表达的是根据 姓名 返回 学号 这里函数有点吃不透不要紧 这 里的重点是说 array 类型的参数比较好招呼 这里用 CHOOSE 函数返回了 一个 array 完成的任务是让 姓名 位于第一列 功能性描述的参数也不限定非得使用单元格区域 使用功能性的词来 描述参数也许正是因为 Excel 没有必要强调他非得是单元格引用吧 这类 参数可以试探性使用常数数组或内存数组以实现数组应用 这话听上去没 有什么 但看一下你会比较有感觉的 本贴续的部分两个公式都比较难 有兴致的同学可以揣摩一下 如果 有点领悟也算是数组公式有点启蒙了 呵呵 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 上面列了一个 VLOOKUP 函数的语法结构提示 也许有同学会感叹 胡版好耐心 一个一个码字 而且丝毫不差 佩服 其实这里有 一个快捷方式将这个 函数提示公式 直接复制到单元格的 具体过程就是 在调出 函数提示工具 时按 组合键 如下图 上眼 呵 呵 上图中快捷键应该为 Ctrl Shift A 有空立马改 算了不改了 算是清晰 地记录吧 呵呵 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 参数类型的玄机附件 rar 1 16 闲谈错误值闲谈错误值 错误值 错误值 相信很多朋友都不陌生 尤其当一次次的报错却找不到根源 甚至连让我们挥剑 F9 除魔的机会都不给时 这种挫败感更是让我们记 忆犹新 首先 我想说 错误值 是朋友不是敌人 虽然它的直率和坦诚常常 让我们接受不了 但毕竟忠言逆耳么 至少它及时告诉我错了 这也是个 重要的信息 信息社会就要充分利用信息 首先 我们来看一下 实战技 巧精粹 中罗列的错误值 看看这些信息是否可以帮助我们更快的找到病 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 因 从而对症下药 春花 秋月 挨个过过场 我们来点评一下 呵呵 时间值列宽不够 这个简单 大概大家也接触过 一般将鼠标指向列标边沿 当指针变 成左右箭头时进行双击 列宽会自动调整到合适大小 也就恢复真 容了 如下图所示 使用了 负 的日期或时间 这也不难 看到返回值为 号并列 如果不是列宽问题 那么我会直接 给它一剑 使用 F9 功能键求整个公式的返回值 看看到底是什么值 本情 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 况显然是负值了 而单元格格式设置了日期类型 这种原因也许可以扩 展为在单元格格式为日期类型前提下 单元格数据不符合日期的数值要求 VALUE 使用的参数或操作数类型错误使用的参数或操作数类型错误 说真的 这个原因看上去很明白 但不是很真切 所以我觉得还是得 去咬一口 呵呵 任意选一个单元格 输入 文本来运算 1 应该算操 作数类型错误吧 当然这个也太 BT 了 实战不可能这么写的 呵 呵 但可以感受一下其所谓的 操作数类型 错误 如下图所示 当然 出现 VALUE 值的另一个可能是没有以 Ctrl Shift Enter 三键方 式输入数组公式 所以 我看到 value 第一个反应就是进入单元格编辑 状态 然后按一下 Ctrl Shift Enter 如果依然报错再深入分析 DIV 0 这个就不用讲了吧 简直就是一个象形字 除数为零 太简单了 再 讲就侮辱大家的智商了 呵呵 NAME Excel 未识别公式中的文本未识别公式中的文本 这个也有点象形字的意味 NAME 名字 有误 这个前面讲过 一般 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 不是函数名拼写错误就是字符串忘了加双引号 以至于 Excel 找不到对应 的函数或名称 N A 数值对函数或公式不可用数值对函数或公式不可用 这个描述又是那种看上去明确但不是太真切 详细的大家可以查帮助 如果嫌烦那就屈就听我比较片面的理解吧 缺少数据时就会出现这个 N A 其他的原因没什么印象 比如选中一列 5 个单元格 输入 row 1 4 按 这个时候最底下的一个单元格就返回 N A 因为 row 1 4 1 2 3 4 只有 4 个数据 所以没有数据分配给第 5 个数据 这就是缺少数据 如下图 REF 引用的单元格无效引用的单元格无效 在讲格式类型时提过 有种类型是 refenrence 引用 这样简写就成了 REF 在其后面意味深长的加一个 绝不是表示感谢 而是一种激愤情 绪 如是可理解为 引用呢 你的引用无效 这种情况一般也简单的 进入公式的编辑状态 这个时候就能看到很 多参数的位置出现了 REF 很明显公式中的这种 REF 绝不会是我们自己输 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 入的 一般是在公式拖拉复制的过程中产生的 举个例子 如果我们在 A18 单元格中输入公式 H2 能说明问题就行 就用最简单的了 呵呵 然后往上拖拉复制 这个 A17 的公式自然变成 H1 那么 A16 的公式呢 H0 显然 Excel 也不知道该怎么表达了 所以就是用 REF 来 报错了 如下图所示 另外一种稍微隐蔽点 就是 REF 不是由单元格引用变异来的 而是 由引用函数返回来的 如公式 OFFSET H 2 ROW A2 在往下拖拉复 制时就会报 REF 错误 但进入编辑状态却看不到直接的 REF 如下图所 示 这里大家不用太认真的去记 有个印象就行 后面会介绍一般的处理 过程 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 NUM 公式或函数中使用无效数字值公式或函数中使用无效数字值 这个没有什么印象 大家可以查帮助 字面理解可能是 参数类型 不 对 需要 数值 NUM 却未被满足 于是它就开始叫嚣 注意数值格式 呵呵 NULL 指定并不相交的两个区域的交集 相交运算符为空格 这个与其说是一个错误 不如说是一种返回结果 可以直接加以利用 因为它表示两个区域不相交 显然这也是一种明确可利用的结果 这里值得一提的倒是交叉运算 既然碰到了 就做个介绍 如下图所 示 待续 简谈错误值附件 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 rar 1 17 闲谈错误值闲谈错误值 续续 在前文的 闲谈错误值 中 我们首先指出了 错误值 是友不是敌 这个基本立场 随后对各种 错误值 进行了点评 以期让大家对 错误 值 有个概要了解 比如 看到 能想到和日期有关 看到 REF 想到 和引用有关 看到 NAME 就想到函数名拼错或字符串遗漏双引号等等 这里我们讲一下对 错误值 进行跟踪追击的一般思路 及其对错误 值的利用 我目前从事的工作是和告警相关的 厂商在上报告警时有这么 一个普适的规律 一个模块的众多信号量中只要有一个告警 则整个模块 就显示告警 当一个设备的众多模块中有一个告警 则这个设备就显示告 警 当一个机房的众多设备中有一个设备告警 这个机房就显示告警 错误值 也是一种告警 所以我尝试将这个规律移植到公式上报 错误 值 的机制上 如果把一个机房等价成一个公式 那么我们所谓的跟踪 错误值 就 是要找到那个最底层的告警信号 于是 首先查看机房中哪个设备在告警 定位故障设备后再定位哪个模块在告警 这样顺藤摸瓜 就是让故障点无 处可逃 在公式中就是先查看错误值根源自公式的哪个并列成分 假设这 个成分是一个多层嵌套的函数 那么就再查看是其哪个参数报错 这个参 数值本身可能又是一个嵌套函数 但不管如何 这样层层剥茧的方式一定 可以找到真凶 然后加以根除 在搜索错误值时使用的武器还是 F9 不过这里需要不断的配合 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 这样才能找到目标 然后再进入目标内部查找更精确地目标 此处征集一个插图 用来表示逐层剥茧的纠错过程 一时想不起好的 例子 惭愧 这里再提一个技巧 一般练习时是碰不到的 但真正实际使用时可能 就会碰到 我们使用 F9 来核查对应的逻辑块是否是错误值 但当 F9 的执 行对象返回的字符过多时就无法进行正常显示 这个时候就到了前文所说 的 连让我们挥剑 F9 除魔的机会都不给了 怎么办呢 最彻底的方 法就是把这个模块单独拎出来 让他显示在单元格区域中 这招还是很有 用滴 是公式编辑中组装 拆卸的思想 不过暂时你可能也用不上 留个 印象就行了 呵呵 上面是纠错的一般思路 当然要练就好的嗅觉还是要多练 积累 下 面我再将一下对 错误值 的利用 错误值 还能利用索 也许你会这 么问 是的 错误值 也是一种信息 就像面无表情也是一种表情 如 上文介绍的 NULL 其实他也明确的提供了有效信息 没有交集 然后我 们可以根据这个判断进行后续处理 这里介绍一个函数 ISERROR 分解一下 IS 是 ERROR 错误 合 起来就是 是错误值 如果参数 一般就是一个复杂函数 确实是错误 值 那么 ISERROR 函数就返回 TRUE 否则就返回 FALSE 这个很好理解 使用这个函数可以用来 美化 公式 如下图所示 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 上图要达到的逻辑是 根据 F3 的 姓名 值在左表姓名列中进行匹配 MATCH 返回对应的位置 再根据这个位置返回学历列的学历值 如 果匹配不到 就会出现错误值 N A 就比较不美观 于是可以使用 IF ISERROR 处理公式 匹配不到时的处理 处理公式 这样的结构来 美化 大家可以下载附件好好练下手 也熟悉一下前文介绍的 函数提示 工具 F9 Ctrl Z 的组合应用 简谈错误值 续附件 rar 纠错是需要一点想象力的 比如你的错误值 N A 缺数据 如果能解 读出 EXCEL 的意思 vlookup value 值 在表格中并未出现 就会尝试去 核对两个数据了 我一般会 单元格 1 单元格 2 先来看看是否相等 其实要查错误 一般的过程就是把公式的执行过程去 走一遍 利用 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 F9 这样就能找到症结了 这里 VLOOKUP 查找 你就收工 CTRL F 用查 找功能找到对应的有空格的数据 如果数据量多的话就要查找功能了 本 列就不用跑流程了 然后比对一下是否相同 呵呵 1 18 玉不琢不成器玉不琢不成器 玉不琢不成器 这里是指复杂完美的公式不是一蹴而就的 而是不 断修改 雕琢出来的 初学函数公式时我也感叹 那些函数公式的高手真是太了不起了 这 么长的公式可以直接写出来 佩服佩服 但现在我想 除了个别已经成型 的函数组合应用外 碰到新问题一般都是一步一步雕琢出来的 这个过程 需要活跃的思维 熟练的公式编辑技能 是公式综合编辑能力的体现 纯语言描述非常空洞 我们就那一个具体的实例吧 所要完成的任务 如下图 所示 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 具体讲就是要把左图转换成三列 第一列为 X 值 第二列为 Y 值 第 三列 X Y 值所对应的数据 要求在 H2 单元格写入公式 然后向左向下拖 拉单元格公式进行填充完成三列 写公式 尤其只是为了更好更快的解决问题 随时定义名称是一个很 好的习惯 增加可读性便于维护 下面我们来定义几个名称 顺便让大家 接触一下名称 不至于后文中提高时过于突兀 X 引用地址为 B 1 F 1 Y 引用地址为 A 2 A 10 DATA 引用地址 为 B 2 F 10 具体过程如下图所示 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 当然 定位名称的方式有多种 这只是其中最通用的一种 如果是 03 版的 原理也是一样的 菜单入口为 插入 名称 定义命名 如此定义名称后 就可以直接使用 X Y DATA 来直接指代那些单元格了 编辑公式时更是可以利用 F3 功能键来快捷准确的录入了 顺便补一个利 用 F3 编辑公式的贴图吧 呵呵 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 图中停顿处是按了 F3 调出了 粘贴名称 对话框 好了 回到正题 我们的任务是要完成三列 那么先分解一下 先写 第一列 步骤 1 在 H2 单元格 INDEX X ROW 1 值为 A 满足要求 然后往 下拖拉复制 这个时候马上会发现 H3 单元格的值为 B 不符合 这个时 候很容易想到 INDEX 函数的第三参数必须重复 9 次 就是 Y 数组的个数 ROWS Y 步骤 2 修改 H2 单元格 INDEX X ROW 1 ROWS Y 这个时候返 回显然是错误值 此时马上会发现第三参数小于 1 修改时直接添加 1 并且也马上会想到不是整数 修改时外套 INT 于是再修改 步骤 3 修改 H2 单元格 INDEX X INT 1 ROW 1 ROWS Y 然后 再往下拖拉公式 开始都正常 但是在 H10 单元格时返回值变成了 B 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 而不是预想的 A 此时 仔细分析 第三参数 INT 1 ROW 1 ROWS Y int 1 10 1 9 2 这个时候就是希望在 ROW 10 时能等于 1 因此也不难想到再修改 将 ROW 替换为 ROW 0 5 步骤 4 修改 H2 单元格公式 INDEX X INT 1 ROW 1 0 5 ROWS Y 整个编辑过程可以如下图所示 当然啦 实际编辑时没有那么快速的 会有点停顿思考的 呵呵 大家也看到这里的关键就是抠出 INDEX 的第三参数 就是表格中 DATA 对于那个的列 这样第一列就算完成了 第二列 大家可以根据这个思路尝试一步一步雕琢 预祝大家成功 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 呵呵 最后 I2 的公式为 INDEX Y IF MOD ROW 1 ROWS Y MOD ROW 1 ROWS Y ROWS Y 关键 1 他是循环的 于是不难想到用 MOD 由于 MOD 会 出现 0 我们把这个出现 0 时换成 ROWS Y 就行了 其实 这里的关键是抠出 INDEX 的第二个参数 就是表格中 DATA 对应 的那个行 第三列 行列都扣出来了 第三列就最简单了 最后 J2 INDEX DATA IF MOD ROW 1 ROWS Y MOD ROW 1 ROWS Y ROWS Y INT 1 ROW 1 0 5 ROWS Y 其中 INDEX 的第二参数使用了第二列公式中 INDEX 的第二参数 第三 参数使用了第一列公式中 INDEX 的第三参数 完成了吗 也许有同学会问 不是最后要合成一个公式吗 那还不简 单 用 IF 判断所在列 然后填写对应的公式就可以了 这里我再秀一下 IF 函数 顺便大家再熟悉一下名称定义 引用地址是公式 呵呵 将 H2 中的公式定义为 X 公式 步骤 1 选中 H2 单元格 进入编辑状态 复制整个公式 步骤 2 开启 定义名称 对话框 或者直接进入 名称管理器 对话 框 新建名称 名称名称 如 X 公式 然后在 引用地址 文本框中粘 贴刚才 H2 中的公式 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 按以上介绍方法依次定义 Y 公式 DATA 公式 然后在 H2 单元格中输入 IF COLUMN 8 X 公式 IF COLUMN 9 Y 公式 DATA 公式 看图 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 大家不要太看重这个例子本身 这里主要讲函数公式不是很一蹴而就 的 是不断修改雕琢的 当然里面有些东西你没怎么接触的话正好熟悉熟 悉 比如定义名称什么 这倒是很有用的 呵呵 玉不琢不成器附件 rar 玉不琢不成器 IF 提高 2003 附件 IF 2003 rar 1 19 君君臣臣君君臣臣 父父子子父父子子 不知道大家是否听说过 OSI 网络七层协议 就是那些什么 物理层 数据链路层 传输层 的结构体系 可能大家一听到这些层的第一感觉 就复杂 其实这是一种创造性的思想 用来对付更加复杂的数据传递和处 理的 在这个体系中 每个层只要完成特定的任务即可 人际关系也非常简 单 就是从上一层接收数据 处理数据 把未尽事宜交给下一层 这样对 维护管理就非常有利 如果大家对这个例子不熟悉 那么我再讲一个例子 例子原型采自 明朝那些事 作为一个村长 每天的工作就是从村东走到村西进行巡 此文档收集于网络 如有侵权 请联系网站删除 此文档仅供学习与交流 视 看看李家门是否忘了关 孙家小两口是否在吵架 然后有重要事情就 向乡长报告 作为乡长 每隔一天对各个村进行巡视 听取各村长的报告 然后有重要事情就向县令报告 这样一级一级 最后就到丞相 皇上那里 了 在这里 每一个级别的官职就相当与一个 OSI 结构中的层 我们也可 以对应成公式中的一个函数 如果某函数 A 充当了另一个函数 B 的参数 那么函数 B 就可看成函数 A 的父函数 如果函数 A 的某个参数又是由另一 个函数 C 充当的 那么函数 C 又是函数 A 的子函数了 如果函数 A 有多个 子函数 如 C1 C2 那么他们之间就是兄弟函数了 啰嗦了很多 关键是抓住一点 这个是本节的关键 而且在日后编写 复杂的数组公式时对理清数组结构非常有帮助 看一个公式 每一次焦点 都放在一个函数上 只要关心它的父函数与子函数即可 而它的爷爷函数 和孙子函数 以及兄弟函数等都不用去理会 这样就能有效的集中精力进 行重点分析 模块化思想是层次体系的基础 层次体系是对模块的组织 我们这里 先来谈谈模块化思想 学一个函数 首先是要了解函数的功能 了解参数 之间的逻辑关系 这样写参数时就能根据功能自然的把参数对号入座 对 函数理解也就深刻牢固了 但如果要把函数用活 随心所欲的嵌套各种函 数来实现复杂的功能就要改变视角 所谓改变视角就是要着眼函数的各个 参数 把参数看成一个个独立的功能模块 以模块为单位来逐个填写函数 这样思路一下就会开阔很多 否则往往会被函数本身限制住 这里就是要 有一种活字印刷的思想 以 VLOOKUP 函数为例 最常用的功能就是