【辅助列解决实际问题】

cleverzhzhf

函数，绝对是Excel各项功能之魂，他能够帮助使用者提升工作效率，提高工作准确性。
然而在面临一些稍微复杂些的情况时候，单纯的追求一个公式解决战斗，往往会比VBA还要困难，这都是那些函数大神们追求的函数之美，但并不完全适用于工作当中。

在我们的日常实际使用中，“辅助列”会更加的实际一些，“以空间换时间”，把你的思路在表格中一步步的呈现出来，最后再获得结果。
甚至有些单一公式根本无法解决的问题，也可以通过辅助列很好的搞定。
这时候，你最需要的事情，其实只有一件：合理布局。

以下是根据我的多年个人使用以及网上回帖时候，总结出来的较为常见的，不适宜单一公式解决的问题。

【辅助列解决实际问题_系列1】利用多单元格仿迭代进行字符串提取——2楼
【辅助列解决实际问题_系列2】中国式排名——4楼
【辅助列解决实际问题_系列3】分级差求累进费率——6楼
【辅助列解决实际问题_系列4】随机生成不重复数字——10楼
【辅助列解决实际问题_系列5】不连续文本连接——12楼
【辅助列解决实际问题_系列6】多条件查找——14楼
【辅助列解决实际问题_系列7】提取不重复值——16楼
【辅助列解决实际问题_系列8】按颜色求和——18楼
【辅助列解决实际问题_系列9】对合并单元格项目求和——21楼
【辅助列解决实际问题_系列10】辅助透视表——24楼

合集附件，包含所有专题的相关数据，但不包含公式讲解。讲解内容请参考本帖各楼层：
辅助列解决实际问题_合集.zip (1.16 MB, 下载次数: 2455)

2014-10-28 08:49 上传

点击文件名下载附件

cleverzhzhf

原帖地址：http://club.excelhome.net/thread-1147617-1-1.html

提取单元格中的字符串，是一个永恒的话题，都有怎样的解决方案呢？
一、一个公式
高手们在根据各种数据特性、函数特性，来追求一个公式解决战斗
不得不说，真的看得人热血沸腾，真心精彩。
但是对于初学的人，以及实际工作中需要应用的人，又是如此难以掌握。
二、VBA
VBA几乎可以说是万能的，如果你会VBA，恰好还懂得正则的时候，哇，使用自定义函数那就没有问题了！
可真的没问题了吗？如果你帮MM做的这个工具，MM可能闪着大眼睛问你，代码贴在哪？我的电脑里看不到“模块”呀？怎么启用宏呀？
三、WORD
这是一个偏门，但是不得不说WORD的查找替换时真心强大！
可是，WORD的弱势是你每次都要做一遍。无法实现自动化。
四、迭代计算
这其实是一个强大但是难于实际运用的工具。每次还要设置启用迭代，估计帮别人讲懂迭代还不如讲懂VBA来的容易

本文采用的方法，就是在仿迭代：
Excel2007以上版本，一共有16384列，打着滚的用，估计你都用不完。所以在自己设计的表格靠右一些的部分，有效运用辅助列，然后把这些列隐藏就完事大吉了。

利用多单元格仿迭代进行字符串提取.rar (17.38 KB)

cleverzhzhf

仅挑选其中的一个公式来解读：
E4的公式为：

1. =IF(AND(MID($D4,COLUMN(A:A),1)>="阿",MID($D4,COLUMN(A:A),1)<="做"),SUBSTITUTE(D4,MID($D4,COLUMN(A:A),1),),D4)

复制代码

然后向右一直拖动到任意格，本文随机拖动到AN4。

一、MID($D4,COLUMN(A:A),1)
务必注意，这里是固定$D4格，因为要逐一判断原字符串的每一个字符。
用Column(A:A)，这样可以实现拖动后，依次生成1、2、3、……的一串数字

二、AND(MID($D4,COLUMN(A:A),1)>="阿",MID($D4,COLUMN(A:A),1)<="做")
文字也是可以比较大小的，“阿”和“做”几乎是汉字中的第一个和最后一个汉字（见新华字典），也可以参考其他帖子使用“吖”和“々”。
这里是为了确定，所提取的字符是否为汉字。

三、SUBSTITUTE(D4,MID($D4,COLUMN(A:A),1),)
这里就是逐一的替换，把原来字符串中的相应的字符，替换为空。注意，Substitute的第一个参数D4，是不固定的，迭代的思想就在这里。

四、IF(AND条件,SUBSTITUTE(D4,MID($D4,COLUMN(A:A),1),),D4)
这里来通过判断逻辑，是把当前单元格左侧的那一个替换为空，还是原封不动的引用左侧那一个单元格。

五、将公式一直向右拖动
这样就形成了“迭代”的最终效果。

cleverzhzhf

原帖地址：http://club.excelhome.net/thread-1147649-1-1.html

所谓中国式排名，就是相同的成绩的同学，排名相同，然后再下一个人名次的数字继续连续，例如：
考试分数：张三100，李四99，王五99，赵六98
中国式排名：张三1，李四2，王五2，赵六3
Excel默认排名：张三1，李四2，王五2，赵六4

10名同学共同考试，由于还没有减负教育，故还要排名。都是中国人，所以相约使用中国式排名。
公式要咋样呢，数组公式如下：

1. =SUM(IF($C$2:$C$11>C2,1/COUNTIF($C$2:$C$11,$C$2:$C$11)))+1

复制代码

供参考链接：http://club.excelhome.net/thread-1042833-1-1.html

然后汉献帝比较刺儿头，又看不懂，“不行！换个公式！”
于是，罗贯中为了他，使用了简易的辅助列方案：

1. D2：=IF(MATCH(C2,C:C,)=ROW(),C2)
   
2. E2：=COUNTIF(D:D,">"&C2)+1

复制代码

中国式排名.rar (7.1 KB)

cleverzhzhf

公式解读：
前提思想：中国式排名，其实就是把重复的数字去除，然后把自己与其他去重复后的数字进行比较的过程。
一、=IF(MATCH(C2,C:C,)=ROW(),C2)
1、MATCH(C2,C:C,)=ROW()
这是一个比较经典的判断重复的方式
首先，通过MATCH，来判断C2的数字在C列第一次出现的位置
然后，用这个第一个出现的位置，与自己所在的行号作比较：
    如果相等：说明该数字，是在此列第一次出现
    如果不等：说明该数字，在靠上的位置，之前出现过，不是第一次出现
2、IF(条件，C2)
如果是第一次出现，则返回C2的值，相当于照抄过来
如果不是第一次出现，这里面用了一个技巧，省略IF的第三个参数，使得返回结果为FALSE，在countif中，FALSE不参与计算，不会影响到最后结果

二、=COUNTIF(D:D,">"&C2)+1
1、COUNTIF(D:D,">"&C2)
这里很简单，就是看看辅助列当中，有几个比自己大的数值，自己就应该排第几
2、COUNTIF(D:D,">"&C2)+1
由于排名是从1开始，而不是0，所以+1调整
（也可以COUNTIF(D:D,">="&C2)，根据个人习惯而异）

cleverzhzhf

原帖地址：http://club.excelhome.net/thread-1148617-1-1.html

分级差累进税率，是很多公司常用的提成方式，最经典的使用地方莫过于税率，这个让大家“痛恨”的家伙。
今天就来讨论一下这个东西的实际使用方式。

三国公司有这样的一个销售提成政策：
销售业绩在0-10万之间，提成5%
销售业绩在10-20万之间，超过10万的部分提成8%
销售业绩在20-40万之间，超过20万的部分提成15%
销售业绩在40-80万之间，超过40万的部分提成25%
销售业绩超过80万的，超过80万的部分提成40%

题目描述参考如下：


当我们只需要处理一个值的时候，这时候多做两个辅助列，看着既清晰又简单：


当需要处理值为多个时，一个个来这样手动，简直是累死活人哦~~
这时候最常用的就是采用速减系数的方式：

分级差求累进费率.rar (9.58 KB)

---

一个公式解决问题，J24数组公式：

1. =SUM(TEXT(I24-$B$12:$B$16,"0;\0")*($C$12:$C$16-N(IF({1},$C$11:$C$15))))

复制代码

cleverzhzhf

方法一：
诸葛亮销售业绩100万提成：(10-0)*5%+(20-10)*8%+(40-20)*15%+(80-40)*25%+(100-80)*40%=22.3
曹操销售业绩为30万提成：(10-0)*5%+(20-10)*8%+(30-20)*15%=2.8
由此可以看出来，当不在的那一档提成时候，就直接用这个级差乘以相应的提成比例；
当业绩处于某一档的时候，用这个业绩减去当前所处的级别金额，如(30-20).

D12：=IF(B13="","",B13-B12)
公式上很好理解，这样为了算出来每两个级差之间的差值。
当B17-B16时候，由于B17没有值，如果硬减，则会出现负数，对整体结果有影响，所有用“空”来处理。
（也可以在B17放一个很大很大的数字，只要是销售业绩达不到的数值即可，如9999）

E12：=MAX(MIN($I$12-B12,D12),0)
首先是MIN($I$12-B12,D12)，这个是要做决定，究竟是使用级差，还是用目标值减去级别金额
其次是MAX(MIN($I$12-B12,D12),0)，对于(30-40)这种得到负值的，明显不是我们不需要的数字，所以就是把小于0的值全部放弃。

J12：=SUMPRODUCT(E12:E16,C12:C16)
这就是一个常见的对应项乘积的和，便能够得到最终的提成结果了

cleverzhzhf

方法二：
与方法一的思想不同，这个是要先按照目前达到的最高比例算提成，然后再把之前的级别多提的部分减去。而这个减去的值就是速减系数。


速减系数：
<方法一>D24：=IFERROR(SUM(($B24:B$25-$B23:B$24)*(C24-$C23:C$24)),0)
这是一个数组公式，得到了速减系数。其实真正有意义是从D25开始的：
=IFERROR(SUM(($B$25:B25-$B$24:B24)*(C25-$C$24:C24)),0)

分别用每一个级差去乘以，当前级别费率与相应级差的费率的差值，然后再把这些数字求和，就是相应的多算出来的提成。
如果读不懂，无所谓，只需要背下来即可，记得这是求得速减系数的方式。

<方法二>E24：=B24*(C24-N(C23))+E23
当被上面第一个复杂的数组公式打败的时候，其实沉下心来，速减系数算的就是相邻两级之间的差值。
其实正常写法是=B24*(C24-C23)+D23，只不过因为C23一般来讲是标题，直接减会报错。而N函数可以将所有文本转化为数字0，方便做减法。


提成：
J24：=I24*LOOKUP(I24,$B$24:$C$28)-LOOKUP(I24,$B$24:$D$28)
K24：=MAX(I24*$C$24:$C$28-$D$24:$D$28)

这两个公式无需多讲，都是较为基本的用法。根据个人理解状况和习惯记忆。

cleverzhzhf

方法三：
反正这个速减数是一个一劳永逸的事情，只要第一次把这些数字确定好，以后用的时候都会方便，所以完全不介意第一次做的时候稍微麻烦一点。
结合以上方法一和方法二的思想：

1、先使用以上方法一，分别求得临界点的实际提成，得到一组数字（或者直接用草稿纸加计算器算，一般来讲也不会很麻烦）：
    0、0.5、1.3、4.3、14.3

2、使用方法二的思想，用销售提成的级别分别乘以相应的提成比例，如：0*5%、10*8%、20*15%……得到一组数字：
    0、0.8、3、10、32

3、继续思考以上方法二的思想，用两者相减，便得到一组速减数：
    0、0.3、1.7、5.7、17.7

4、把这一组数填写到D24:D28，然后使用方法二的J4公式：
    J24：=I24*LOOKUP(I24,$B$24:$C$28)-LOOKUP(I24,$B$24:$D$28)
    或者：=MAX(I24*$C$24:$C$28-$D$24:$D$28)

cleverzhzhf

原帖地址：http://club.excelhome.net/thread-1148639-1-1.html

随机值是一个常见问题，可以很简单的函数：=RAND()
可是很多时候，我们希望出来的数值不要是小数，而是整数，那怎么办呢？
=RANDBETWEEN(1,100)
或：=INT(RAND()*100+1)

由于范围相对比较小，这样的随机出来的结果，很有可能就会出现重复值，那该如何解决呢？
很多前辈都有过精彩解答，也有一个公式搞定，甚是精彩！但我们今天的目标就是快速、简单的解决实际问题。
老办法，辅助列哦~~

来看看三国公司年会的抽奖吧：

随机生成不重复数字.rar (9.96 KB)

---

其他参考贴：
[分享]纯粹用数组公式生成不重复随机数  ——wddn
http://club.excelhome.net/thread-330784-1-1.html

1. =INDEX(A$1:A$51,LARGE(IF(ISNA(MATCH(A$2:A$51,D$1:D1,)),ROW(A$2:A$51)),1+INT(ROWS(A1:A$50)*RAND())))

复制代码