ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 53055|回复: 123

[原创] 【辅助列解决实际问题】

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2014-10-27 14:39 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:公式基础
本帖最后由 wangg913 于 2015-6-9 16:58 编辑

函数,绝对是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, 下载次数: 2441)


评分

14

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-10-27 14:43 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册

【辅助列解决实际问题_系列1】利用多单元格仿迭代进行字符串提取

本帖最后由 cleverzhzhf 于 2014-10-27 15:22 编辑

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

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

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


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







评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-10-27 14:45 | 显示全部楼层
仅挑选其中的一个公式来解读:
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)
这里来通过判断逻辑,是把当前单元格左侧的那一个替换为空,还是原封不动的引用左侧那一个单元格。

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

评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-10-27 14:49 | 显示全部楼层

【辅助列解决实际问题_系列2】中国式排名

本帖最后由 cleverzhzhf 于 2015-5-25 15:48 编辑

原帖地址: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)


TA的精华主题

TA的得分主题

 楼主| 发表于 2014-10-27 14:55 | 显示全部楼层
公式解读:
前提思想:中国式排名,其实就是把重复的数字去除,然后把自己与其他去重复后的数字进行比较的过程。
一、=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),根据个人习惯而异)

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-10-27 14:58 | 显示全部楼层

【辅助列解决实际问题_系列3】分级差求累进费率

本帖最后由 cleverzhzhf 于 2015-5-25 15:49 编辑

原帖地址: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))))
复制代码

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-10-27 15:01 | 显示全部楼层
方法一:
诸葛亮销售业绩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)
这就是一个常见的对应项乘积的和,便能够得到最终的提成结果了

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-10-27 15:01 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 cleverzhzhf 于 2015-2-3 00:10 编辑

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


速减系数:
<方法一>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)


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

点评

减速就是速算扣除数的意思,直接=B25*(C25-C24)+E24 就可以  发表于 2014-10-29 09:56

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-10-27 15:07 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
方法三:
反正这个速减数是一个一劳永逸的事情,只要第一次把这些数字确定好,以后用的时候都会方便,所以完全不介意第一次做的时候稍微麻烦一点。
结合以上方法一和方法二的思想:

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)

TA的精华主题

TA的得分主题

 楼主| 发表于 2014-10-27 15:09 | 显示全部楼层

【辅助列解决实际问题_系列4】随机生成不重复数字

本帖最后由 cleverzhzhf 于 2015-5-25 15:50 编辑

原帖地址: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())))
复制代码
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-28 17:01 , Processed in 0.054447 second(s), 10 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表