网页资讯视频图片知道文库贴吧地图采购
进入贴吧全吧搜索
excel吧 关注：281,024贴子：1,545,927

回复：函数问题你问我答，太难的不会

问题:怎么计算工资税,速算扣除数,个人所得税
解:
工资税具体算法自己去了解,大概意思是,工资-免税金额,超出的部分按照上表扣除
每个级别的工资有不同的计税比例
注意应激工资是15000元的话,并不是整个15000都按一个比例扣除
是0-3000部分,扣除3%,,,3000-12000部分扣除10%,,,12000-15000部分,扣除20%
如果按每个阶段计算,非常麻烦,那就可以用官方发布速算扣除数
例如15000金额,直接全部按照20%计算,那么0-12000部分,就多扣了税额,那么,减去速算扣除数,就是应缴税金
=LOOKUP(B2-5000,G3:H9)%
可以得到金额对应的税率
=LOOKUP(B2-5000,G3:I9)
可以得到对应的速算扣除数
然后金额*税率-速算扣除数,就是税额了
=IFERROR((B2-5000)*LOOKUP(B2-5000,$G$3:$H$9)%-LOOKUP(B2-5000,$G$3:$I$9),0)
可以参考这个视频
【《Excel你问我答挑战》已知开始结束日期,按每天不同单价计费,速算扣除法(工资税也是这样算的)】
https://www.bilibili.com/video/BV1xz4y1a7aH/?share_source=copy_web&vd_source=75cdf6caedfa204e0957b1c78cfb7ffb


=SUMIFS(OFFSET(A:A,,MATCH("奖金",$A$1:$C$1,0)-1),OFFSET(A:A,,MATCH("姓名",$A$1:$C$1,0)-1),H2)



问题:判断手机尾货类型,AABB靓号
解:
要判断尾号,我们就先把尾号的4个数字切片出来
=MID(A2,7+ROW($1:$4),1)
{"9";"9";"8";"8"}
这里会有重复数数字,我们需要先去重
=UNIQUE(MID(A2,7+ROW($1:$4),1))
{"9";"8"}
我们现在的得到的就是尾号数字集
我们在用4个尾号,在尾号集里查找所在位置
=MATCH(MID(A2,7+ROW($1:$4),1),UNIQUE(MID(A2,7+ROW($1:$4),1)),0)
{1;1;2;2}
可以得到每个尾号的代表数字,将12转成AB就是我们要的AABB格式了
可以把1234的数子,都+64,就可以得到代表ABCD的字码65 66 67 68
再char将字码转成文本字母
=CHAR(64+MATCH(MID(A2,7+ROW($1:$4),1),UNIQUE(MID(A2,7+ROW($1:$4),1)),0))
{"A";"A";"B";"B"}
再concat合并
=CONCAT(CHAR(64+MATCH(MID(A2,7+ROW($1:$4),1),UNIQUE(MID(A2,7+ROW($1:$4),1)),0)))
数组公式,按shift+Ctrl+回车




按shift+Ctrl+回车,部分版本不用




=OFFSET(IF(H2="A区",$B$1,$D$1),COUNTIF(H$1:H2,H2),0)


=PRODUCT(IFERROR(MMULT(IFERROR(--TEXTSPLIT(A1,"+",{"(",")","*"}),0.5),{1;1}),--TEXTSPLIT(A1,,"*")))




【《Excel你问我答挑战》 多对多查找VLOOKUP，依次返回多结果】 https://www.bilibili.com/video/BV1Rc411V7ZQ/?share_source=copy_web&vd_source=75cdf6caedfa204e0957b1c78cfb7ffb



问题:判断是否超时
A列是分钟数,B列是时分秒格式,判断是否超过分钟数
解;
这是很标准的时间运算
0:27:30这个时分秒,实际上他在表格内的数值是0.0190972222222222
也就是一天就记做1,,,,,27分30秒,占一天24小时中的比例,的小数就是他的数值
那我们也把39分钟的39转化成0:39:00对应的数值,就可以直接比较了
=A2/60/24
0.027083333
得到的数字,直接做大于小于的判断,就知道是否超时了
=B2>A2/60/24
这样可以得到true false的结果,在配合if返回文字提现
=IF(B2>A2/60/24,"超时","")
也可以把0:27:30的时间转成分钟数
=B2*60*24
27.5
这个就是分钟数,跟限制的分钟数对比,也可以
=IF(B2*60*24>A2,"超时","")



问题:将ABCD乱序选项排序成顺序的
多行数据,分别按列排序
解
如果有SORT函数的话,可以直接排序
=SORT(B2:E2,,1,1)
就可以接排序出ABCD的顺序
方案2
{"C.答案","B.答案","A.答案","D.答案"}
先把答案中的选项中的第一个字符切片出来
=LEFT(B2:E2,1)
{"C","B","A","D"}
文本的话,不能做small排序,那我们就将ABCD转成字码后在从小到大提取
=CODE(LEFT(B2:E2,1))
{67,66,65,68}
这样就可以排序了,但是直接排序没有意义
我们需要将本身的列号也加权一起,再排序
=CODE(LEFT(B2:E2,1))*100+COLUMN(B2:E2)
{6702,6603,6504,6805}
这样加权后,每个数字的前两位就是ABCD的字码,后面是列号
在从小到大提取
=SMALL(CODE(LEFT(B2:E2,1))*100+COLUMN(B2:E2),COLUMN(A:D))
{6504,6603,6702,6805}
这样就将列号,根据字码排序成ABCD的顺序了
我们在取出每个数字的最后一位数字
=MOD(SMALL(CODE(LEFT(B2:E2,1))*100+COLUMN(B2:E2),COLUMN(A:D)),100)
{4,3,2,5}
得到的数字就是按ABCD排序后的列号,index提取对应内容即可
=INDEX(A2:E2,MOD(SMALL(CODE(LEFT(B2:E2,1))*100+COLUMN(B2:E2),COLUMN(A:D)),100))
部分版本需要按shift+Ctrl+回车
完结撒花



问题:多条件vlookup查询不同列
根据不同列的进价返回对应出价
解:
查询喂食器时,需要先找到喂食器在原数据中的哪一行,取出对应的进出价格
=OFFSET($B$1:$E$1,MATCH(H2,A:A,0)-1,)
{102,1002,202,2002}
可以得到对应的进出价格,但是我们的查询目标是进价,出价数字会对我们干扰,那就把进价/1,出价/0,出价就会报错排除掉了
={102,1002,202,2002}/{1,0,1,0}
{102,#DIV/0!,202,#DIV/0!}
这里只剩下进货价,在查询进价在在哪一列
=MATCH(I2,{102,#DIV/0!,202,#DIV/0!},0)
这样就能知道进价在哪一列,那么出货价就下一列
那对应的出货价,就可以用offset,偏移产品所在行,出价所在列就行了
=OFFSET($A$1,MATCH(H2,A:A,0)-1,MATCH(I2,OFFSET($B$1:$E$1,MATCH(H2,A:A,0)-1,)/{1,0,1,0},0)+1)
部分版本需要按shift+Ctrl+回车



问题:生成20个1~10,20个11~100的随机数,且打乱
一步到位方法
思路跟左边基本相同
用if,根据row(1:40)的序号,
序号小于20的就生成1-10的随机数
序号大于20的就生成11-100的随机数
同C列效果,返回在生成40个随机数,同F列
但是我们需要把CF列合成一组数据
那就用加权法,F列*100000+C列
得到加权数后,在small从小到大输出,同右图
在mod取最后的尾数即可
=MOD(SMALL(RANDBETWEEN(IF(ROW($1:$40)<=20,ROW($1:$40)^0,ROW($1:$40)^0*11),IF(ROW($1:$40)<=20,10,100))+RANDBETWEEN(ROW($1:$40)^0,999)*100000,ROW($1:$40)),100000)


=IF(ISERROR(FIND(C2,VLOOKUP(MID(B2,1,4)&"*",B3:F$15,5,0))),C2&",","")&IFERROR(VLOOKUP(MID(B2,1,4)&"*",B3:F$15,5,0),"")


扫二维码下载贴吧客户端

下载贴吧APP
看高清直播、视频！
分享到: