计算指定日期的周数
使用场景:
用于工作汇报、任务管理等需要获取指定日期对应的周数
计算指定日期对应的全年周数
可以通过WEEKNUM函数直接求出
WEEKNUM(日期控件)
//用于求指定日期对应的全年周数
预期效果:
当日期输入为2024年4月30日时,计算公式输出为:14,即该日为2024年第14周
计算指定日期对应的当月周数
方法说明:
先通过DATEDELTA函数求出当月1日,然后使用WEEKNUM函数,比对目标所在的周数和当月1日的周数,最终获取当月周数
WEEKNUM(日期控件)-WEEKNUM(DATEDELTA(日期控件,-DAY(日期控件)+1) )
//DATEDELTA(日期控件,-DAY(日期控件)+1) 用于求得当月1日的日期
//通过2次WEEKNUM函数比对出相差的周数,最终加1得出当月周数
预期效果:
2024年4月30日时,计算公式输出为:5,即该日为4月第5周
计算身份证号中的信息
使用场景:
如员工/会员信息表中通过身份证号获取生日信息等
计算身份证号中的生日
使用MID求出年月日
DATE(MID(身份证号,7,4),MID(身份证号,11,2),MID(身份证号,13,2))
// MID(身份证号,7,4)表示获取身份证中的年,date函数将获取到的年月日转成日期
预期效果:
当身份证号输入为000000201212120000日时,计算公式输出为:2012-12-12
识别身份证中的性别
方法说明:
先通过MID获取性别编号,再通过MOD函数判断性别
IF(LEN(身份证号) != 18, "",IF(MOD((MID(身份证号,17,1)),2) == 1,"男","女"))
//MID(身份证号,17,1)获取到性别编号
//MOD性别编号,单数即男性,偶数即女性
//LEN判断身份证号长度是否正常
预期效果:
身份证号输入000000000000000090时,计算公式输出为:男
识别身份证中的生肖
方法说明:
先通过MID获取性别编号,再通过MOD函数判断性别
DATE(MID(身份证号,7,4),MID(身份证号,11,2),MID(身份证号,13,2))
// 通过上面的日志获取出生日期,赋值到一个辅助日期字段
MOD(MOD(YEAR(出生日期)-2020,12) + 12,12)
// 计算与鼠年相差的年份的绝对值,赋值到一个辅助数字字段
// 需要两次求余,第一次求余得到的值可能为负数,需要加上12后转为整数后再次求余
IF(与鼠年相差的年份==0,"鼠",
IF(与鼠年相差的年份==1,"牛",
IF(与鼠年相差的年份==2,"虎",
IF(与鼠年相差的年份==3,"兔",
IF(与鼠年相差的年份==4,"龙",
IF(与鼠年相差的年份==5,"蛇",
IF(与鼠年相差的年份==6,"马",
IF(与鼠年相差的年份==7,"羊",
IF(与鼠年相差的年份==8,"猴",
IF(与鼠年相差的年份==9,"鸡",
IF(与鼠年相差的年份==10,"狗","猪")))))))))))
// 根据与鼠年相差的年份,获取到生肖
预期效果:
身份证号输入000000201200000000时,计算公式输出为:龙
识别身份证中的星座
方法说明:
先通过MID获取出生月日,再多重嵌套if判断星座
MID(身份证号,11,4)
//MID(身份证号,11,4)获取出生月日,赋值到一个辅助数字表单
IF(出生月日>=321 && 出生月日<=419, "白羊座",
IF(出生月日>=420 && 出生月日<=520, "金牛座",
IF(出生月日>=521 && 出生月日<=621, "双子座",
IF(出生月日>=622 && 出生月日<=722, "巨蟹座",
IF(出生月日>=723 && 出生月日<=822, "狮子座",
IF(出生月日>=823 && 出生月日<=922, "处女座",
IF(出生月日>=923 && 出生月日<=1023, "天秤座",
IF(出生月日>=1024 && 出生月日<=1122, "天蝎座",
IF(出生月日>=1123 && 出生月日<=1221, "射手座",
IF(出生月日>=120 && 出生月日<=218, "水瓶座",
IF(出生月日>=219 && 出生月日<=320, "双鱼座","摩羯座")))))))))))
// 多重嵌套if函数,判断星座
预期效果:
身份证号输入000000000008090000时,计算公式输出为:狮子座
识别身份证中的所属省份
方法说明:
先通过MID获取省份标志,再通过多重嵌套if判断省份
IF(MID(身份证号,1,2)==11,"北京市",
IF(MID(身份证号,1,2)==12,"天津市",
IF(MID(身份证号,1,2)==13,"河北省",
IF(MID(身份证号,1,2)==14,"山西省",
IF(MID(身份证号,1,2)==15,"内蒙古自治区",
IF(MID(身份证号,1,2)==21,"辽宁省",
IF(MID(身份证号,1,2)==22,"吉林省",
IF(MID(身份证号,1,2)==23,"黑龙江省",
IF(MID(身份证号,1,2)==31,"上海市",
IF(MID(身份证号,1,2)==32,"江苏省",
IF(MID(身份证号,1,2)==33,"浙江省",
IF(MID(身份证号,1,2)==34,"安徽省",
IF(MID(身份证号,1,2)==35,"福建省",
IF(MID(身份证号,1,2)==36,"江西省",
IF(MID(身份证号,1,2)==37,"山东省",
IF(MID(身份证号,1,2)==41,"河南省",
IF(MID(身份证号,1,2)==42,"湖北省",
IF(MID(身份证号,1,2)==43,"湖南省",
IF(MID(身份证号,1,2)==44,"广东省",
IF(MID(身份证号,1,2)==45,"广西壮族自治区",
IF(MID(身份证号,1,2)==46,"海南省",
IF(MID(身份证号,1,2)==50,"重庆市",
IF(MID(身份证号,1,2)==51,"四川省",
IF(MID(身份证号,1,2)==52,"贵州省",
IF(MID(身份证号,1,2)==53,"云南省 ",
IF(MID(身份证号,1,2)==54,"西藏自治区",
IF(MID(身份证号,1,2)==61,"陕西省",
IF(MID(身份证号,1,2)==62,"甘肃省",
IF(MID(身份证号,1,2)==63,"青海省",
IF(MID(身份证号,1,2)==64,"宁夏回族自治区",
IF(MID(身份证号,1,2)==65,"新疆维吾尔自治区",
IF(MID(身份证号,1,2)==83,"台湾",
IF(MID(身份证号,1,2)==81,"香港特别行政区",
IF(MID(身份证号,1,2)==82,"澳门特别行政区",""))))))))))))))))))))))))))))))))))
// MID(身份证号,1,2)获取省份标志,然后通过多重嵌套if判断省份
预期效果:
身份证号输入440000000000000000时,计算公式输出为:广东省
根据身份证获取年龄
方法说明:
先通过MID和DATE获取生日,再通过DATEDIF函数和TODAY函数获取生日距离今天的月数,除以12获取年龄,INT函数去掉小数部分
IF(LEN(身份证号)!=18,null,INT(DATEDIF(DATE(MID(身份证号,7,4),MID(身份证号,11,2),MID(身份证号,13,2)),TODAY(),"M") / 12))
// LEN函数判断身份证长度是否符合
// MID函数获取身份证中的年月日
// DATE函数将身份证中的年月日转成时间
// TODAY函数获取今天的日期
// DATEDIF函数获取两个时间相差的月数,除以12即可得到年龄
// INT函数将上述得到的年龄去掉小数部分
预期效果:
身份证号输入000000201204230000时,计算公式输出为:11
隐藏字符串
使用场景:
可用于隐秘信息脱敏
手机号脱敏
可以通过REPLACE函数对部分字符串进行替换
REPLACE(手机号,4,4,"****")
// 替换手机号第四位开始的4个字符串为4个型号
预期效果:
当手机号输入为13333333333时,计算公式输出为:133****3333
数学运算
使用场景:
常见的一些数学运算
简单四则运算
多重嵌套IF函数判断操作符
IF(ISEMPTY(参数1) || ISEMPTY(参数2),null,
IF(操作符=="+",SUM(参数1,参数2),
IF(操作符=="-",参数1-参数2,
IF(操作符=="*",参数1*参数2,
IF(参数2==0,null,
IF(操作符=="/",参数1/参数2,MOD(参数1,参数2)))))))
// 多重嵌套IF函数判断操作符和判断被除数是否为0
预期效果:
当参数1输入为100、操作符选择/,参数2输入为33时,计算公式输出为:3
余弦公式计算三角形面积
假设有边长a、b、c,及对应的角A、B、C,余弦公式:S=1/2abCosC,SinC=(a*a+b*b-c*c)/(2*a*b),CosC=SQRT(1-SinC*SinC)
IF(边长1>0&&边长2>0&&边长3>0&&(边长1+边长2)>边长3&&(边长1+边长3)>边长2&&(边长2+边长3)>边长1,
SQRT(1-((边长1*边长1+边长2*边长2-边长3*边长3)/(2*边长1*边长2)*(边长1*边长1+边长2*边长2-边长3*边长3)/(2*边长1*边长2)))*边长1*边长2/2,null)
// IF函数判断输入是否能构成一个三角形
//
预期效果:
当边长1、边长2、边长3分别输入为3、4、5时,计算公式输出为:6
多评委打分计算平均分
多评委打分,去掉最高分和最低分,最后算平均分(使用子表)
IF(COUNT(评委打分.分值)<=2,"",
ROUND((SUM(评委打分.分值)-MAX(评委打分.分值-MIN(评委打分.分值))/(COUNT(评委打分.分值)-2),2))
// COUNT函数算出子表的打分人数
// SUM函数算出子表打分的总数
// MAX函数算出子表中的最高分
// MAX函数算出子表中的最低分
// ROUND函数保留两位小数
预期效果:
当分数输入:1、2、3、4、5、6、7时,计算公式输出为:4.00
生活常见计算
使用场景:
生活常见的一些计算
梯度电费计算
多重嵌套IF函数判断梯度计算电费
IF(ISEMPTY(月份)||ISEMPTY(电量)||电量<0,null,
IF(SUBSTITUTE(月份,"月","")>=5&&SUBSTITUTE(月份,"月","")<=10,
IF(电量<=260,0.64*电量,
IF(电量<=600,260*0.64+(电量-260)*0.69,260*0.64+340*0.69+(电量-600)*0.94)),
IF(电量<=200,0.64*电量,
IF(电量<=400,400*0.64+(电量-200)*60.69,200*0.64+200*0.69+(电量-400)*0.94))))
// ISEMPTY函数判断输入是否争取
// SUBSTITUTE函数将选择的月份去掉月字,只保留数字
// 后面的嵌套IF函数判断梯度
预期效果:
当月份选择11月、电量输入274时,计算公式输出为:178.37
个人个税计算
多重嵌套IF函数计算个人每个月纳税
IF(ISEMPTY(缴税月份) ,0,(SUBSTITUTE(缴税月份,"月","") - 1) * (每月工资- 五险一金扣除- 每月专项扣除- 5000))
// 计算上月累计应纳所得税额,赋值到一个辅助数字字段中
IF(上月累计应纳所得税额<=36000,上月累计应纳所得税额*0.03,
IF(上月累计应纳所得税额<=144000,上月累计应纳所得税额* 0.1-2520,
IF(上月累计应纳所得税额<=300000,上月累计应纳所得税额*0.2-16920,
IF(上月累计应纳所得税额<=420000,上月累计应纳所得税额*0.25-31920,
IF(上月累计应纳所得税额<=660000,上月累计应纳所得税额*0.3-52920,
IF(上月累计应纳所得税额<=960000, 上月累计应纳所得税额*0.35-85920,
IF(上月累计应纳所得税额>960000,上月累计应纳所得税额*0.45-181920,0)))))))
// 计算上月累计已纳个税,赋值到一个辅助数字字段中
IF(ISEMPTY(缴税月份) ,0,(SUBSTITUTE(缴税月份,"月","")) * (每月工资- 五险一金扣除- 每月专项扣除- 5000))
// 计算含当月累计应纳所得税额,赋值到一个辅助数字字段中
IF(含当月累计应纳所得税额<=36000,含当月累计应纳所得税额*0.03,
IF(含当月累计应纳所得税额<=144000,含当月累计应纳所得税额*0.1-2520,
IF(含当月累计应纳所得税额<=300000,含当月累计应纳所得税额*0.2-16920,
IF(含当月累计应纳所得税额<=420000,含当月累计应纳所得税额*0.25-31920,
IF(含当月累计应纳所得税额<=660000,含当月累计应纳所得税额*0.3-52920,
IF(含当月累计应纳所得税额<=960000, 含当月累计应纳所得税额*0.35-85920,
IF(含当月累计应纳所得税额>960000,含当月累计应纳所得税额*0.45-181920,0)))))))
// 计算含当月月累计已纳个税,赋值到一个辅助数字字段中
含当月月累计已纳个税-上月累计已纳个税
// 计算当月需要缴纳的个税
每月工资-五险一金扣除-本月需缴纳个税
// 本月实发工资
预期效果:
当月份选择11月、工资输入输入8000、五险一金输入12时,当月需要缴纳个税计算公式输出为:31.2
当月份选择11月、工资输入输入8000、五险一金输入12时,当月实发工资计算公式输出为:7008.80
工龄计算
使用DATEDIF函数计算开始工作时间和结束时间的时间差,获取工龄
IF(DATEDIF(开始工作时间,结束工作时间,"d") > 0,INT(DATEDIF(DATEDELTA(结束工作时间,1),开始工作时间,"M")/12),null)
// 第一个DATEDIF函数判断是否开始时间和结束时间是否填反了
// DATEDELTA函数将结束时间+1天,是为了处理开始时间是4月20号,结束时间是4月19号这种情况
// 第二个DATEDIF函数计算开始工作时间和结束工作时间的月份,除以12得到工龄
// INT函数将工龄的小数去掉
预期效果:
当开始时间输入2024-04-20、结束时间输入2029-04-18时,当月需要缴纳个税计算公式输出为:4
当开始时间输入2024-04-20、结束时间输入2029-04-19时,当月需要缴纳个税计算公式输出为:5
当开始时间输入2024-04-20、结束时间输入2029-04-20时,当月需要缴纳个税计算公式输出为:5
发票金额拆分
使用MOD函数获取当前位数的值
IF(子表.金额>10000,MOD(子表.金额/10000,10),null)
// 以万位数为例,当金额大于10000时,先获取除以10000的商,再将商对 10 求余
INT(MOD((子表.金额-INT(子表.金额))*10))
// 以角为例,先使用INT函数去掉小数,然后将原来的金额减去INT函数后的值,获取到小数
// 再将得到的小数乘以10,再使用INT函数去掉小数,等到角的值
INT((子表.金额-INT(子表.金额)) * 100/ 10)
// 以分为例,先使用INT函数去掉小数,然后将原来的金额减去INT函数后的值,获取到小数
// 再将得到的小数乘以100,除以10,再使用INT函数去掉小数,等到分的值
预期效果:
无
将阿拉伯数字金额转中文大写金额
直接使用 UPPERMONEY 函数进行转换
UPPERMONEY(金额)
预期效果:
当数字控件金额输入12345时,计算公式输出为:壹万贰仟叁佰肆拾伍元整