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%是因为第一条）。

本文来源：https://www.2haoxitong.net/k/doc/c4c2ad4beef9aef8941ea76e58fafab068dc4413.html