Excel公式如何写？有套路!.docxVIP

Excel公式怎么写？有套路！ 原创?2016-08-30?King?秋叶PPT 你将学习到以下内容，以及一个很重要的核心套路！ ? 快速填充法提取字符? 辅助行生成递增序数? 逻辑判断函数 if? 合并文本连字符 ? 文本提取函数 left 和 len? 文本合并函数 concact 滴~滴~滴滴~~~ 右下角闪动的企鹅显示，又有一条新的QQ消息。 正在码字的我心里咯噔了一下，忍不住打开消息窗口，原来是一条求助信息： 咦，Excel问题，怎么跑来高颜值PPT的群里问？当时我的第一反应是拒绝的。 但是转念一想，抽空解个题正好可以换换脑，放松一下（柯南附体）。于是开始在脑子里飞速运转，如何解决这个文本转换问题。 【问题】 如何将K3-K5表示的文本，转换成K3,K4,K5表示的字符串？ 聪明的你，会选择下面哪个方案呢？ A：手动输入B：分列-函数上中间空缺序数-再合成C：提取起始值和截止值，据此生成连续的数字，然后合成文本D：一个数组公式，一步到位E：一定有其他黑技术 条条大路通罗马！ 同一个问题，可能每个人的解法都不一样，而这也是Excel 最有意思地方。 像今天遇到的问题，如果要转换 3-5 个，手工改一改并不难。但是如果有10个甚至上百个，工作量可不得了。 要是用上函数公式就能一劳永逸，轻松搞定。 然而很多表哥表妹对函数公式还了解不深，在面对此类任务时，一下子就方了。“这个函数公式得怎么写来啊~” 使用函数误区：必须用一个公式、一步到位地计算出最终结果 在面对复杂的问题时，有效的策略是：层层设问分解，自问自答解决。 既可以正向推导，从初始状态出发；也可以逆向分析，从目标结果倒推。 如果我们能够通过环环相扣的问题寻找线索，复杂的大问题就会化整为零，迎刃而解。 那么针对这一次的问题，我们来试试方法C——分解问题，再写公式！问题分解 下面就来看，每一步如何实现。 ??提取起始结束数值 ??生成连续数字 使用公式之前，有必要先搞清楚单元格引用方式：相对引用和绝对引用。 同一个公式，其引用位置会随着填充单元格的位移而自动变化。比如将B2的公式向下填充后，引用位置自动的从A2变成了A3和A4，此为相对引用： 如果加上$符号就就，给行、列加上了一把锁，变成绝对引用。 比如锁定行号以后，同样是将B2的公式向下填充，公式的行号再也不变，结果全部等于A2。 4种单元格引用方式(按F4键可以循环切换)： A2 ? ??—?相对引用，不锁定行列$A$2 — 绝对引用，同时锁定行和列?$A2 ??—?混合引用，只锁定列A$2 ??— 混合引用，只锁定行 利用此原理，借助一个从0-N的辅助序列，就能依据上一步提取的起始值x，快速生成一批递增的数据系列： 但是此公式仍有一个问题：K3-K5，只要3,4,5三个值就够了。怎么把多余的6,7去掉呢？ 可以把公式结果和上一步提取y值比较，比如第一行的数据如果小于或等于5，就显示，大于五就等于空值，用一个if函数就可以搞定： ??英文字母和逗号 ? ? ? ??合并文本 公式用在单个字符连接比较方便，但是要合并整个区域内的文本，还是Concat函数最便捷： ??去掉末端顿号 这里用到两个常用的文本提取函数，语法是： 于是，去掉最后一个顿号字符，只需计算总长度，然后从左侧算起提取减总长度-1个字符的文本 搞定！ 转换上千行的文本，只要几个辅助列和几个公式就能秒杀。要用到的，仅仅是Excel中常见的一些用法：? 快速填充法提取字符? 辅助行生成递增序数? 逻辑判断函数 if? 合并文本连字符 ? 文本提取函数 left 和 len? 文本合并函数 concact 此解决方案并不是唯一的最优解，如果对函数了解更加深入，你还可以尝试用其他函数来解决。 这个案例不一定每个人会遇到，实际工作中的数据表充满变数，死记硬背操作步骤并没有什么卵用。 ?但是下面的套路可以帮助我们见招拆招，你一定能用上：? ? 对比差距：认清目标，观察对比目标起点；? 层层分解：化整为零，层层设问逼近目标；? 各个击破：小量测试，逐项验证优化调整；? 批量套用：验证成功，批量填充套用公式。 “我就是想不到要怎么分解，怎么办呢？”“能够想到思路，却不知道用哪个函数，怎么办呢？” 如果还有这样的疑问，大体是因为两个原因： ? 不会互联网搜索，俗称搜商不足； ? 看得不够，不知道有哪些常用函数和基本用法（80%是因为第一条）。