|
前言 1、计算所有单元格数值的绝对值之和 公式：=SUMPRODUCT(ABS(A1:A10)) 2、纵向生成A-AZ的序列 公式：=SUBSTITUTE(ADDRESS(1,ROW(A1),4),1,'') 公式写在任意单元格，然后下拉 3、随机生成1-6颗“★” 公式：=REPT('★',RANDBETWEEN(1,6)) 4、根据文本字符串指定字符“-”进行分列 公式：=TRIM(MID(SUBSTITUTE($A1,'-',REPT(' ',99)),99*COLUMN(A1)-98,99)) 公式右拉 5、根据身份证号码判断性别 公式：=IF(ISEVEN(--MID(A1,17,1)),'女','男') 6、获取字符串中第1个数字出现的位置 公式：=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)) 7、计算大于200的数值的总和 公式：=SUMIF(A1:A10,'>200') 8、计算姓名中包含“明”字的人员销量总和 公式：=SUMIF(A1:A10,'*明*',B1:B10) 9、建立一个指定网址的超链接 公式：=HYPERLINK('http://www.','我的Excel') 10、根据应纳税所得额计算个人所得税 公式：=MAX((B1-3500)*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,105,555,1005,2775,5505,13505},0) 11、根据个人所得税计算税前工资 公式：=IF(B2=0,'无法计算',MIN(3500 (B2 5*{0,21,111,201,551,1101,2701})/(5%*{0.6,2,4,5,6,7,9}))) 数组公式，CTRL SHIFT 回车键三键结束 12、根据税后工资计算税前工资 公式：=ROUND(MAX((B2-3500-{0,0,105,555,1005,2755,5505,13505})/(1-{0,0.03,0.1,0.2,0.25,0.3,0.35,0.45})) 3500,2) 13、根据税后工资计算个税 公式：=MAX((B2-3500-5*{0,21,111,201,551,1101,2701})/(1-5%*{0.6,2,4,5,6,7,9}) 3500-B2,0) 14、提取不重复数据 公式：=IFERROR(INDEX($A$2:$A$15,SMALL(IF(MATCH($A$2:$A$15,$A$2:$A$15,0)=ROW($1:$14),ROW($1:$14),9^9),ROW(A1))),'') 数组公式，需CTRL SHIFT 回车键三键结束，公式下拉 15、数值四舍五入保留2位小数 公式：=ROUND(A1,2) 16、数值向下取舍保留2位小数 公式：=ROUNDDOWN(A1,2) 17、数值向上取舍保留2位小数 公式：=ROUNDUP(A1,2) 18、数值向下取整（截尾取整） 公式：=INT(A1) 19、返回10年前的今天的日期 公式：=TEXT(EDATE(TODAY(),-120),'yyyy/m/d') 20、计算本月总天数 公式：=EDATE(TODAY(),1)-TODAY() 21、将数值按从小到大升序排列 公式：=SMALL($A$1:$A$10,ROW(A1)) 公式下拉 22、将数值按从大到小降序排列 公式：=LARGE($A$1:$A$10,ROW(A1)) 公式下拉 23、计算前三名的数值总和 公式：=SUM(LARGE(A1:A10,{1,2,3})) 24、提取以数字开头的字符串中的数字 公式：=LOOKUP(9^9,--LEFT(A1,ROW($1:$99))) 25、计算字符串中双字节字符个数（例如汉字） 公式：=LENB(A1)-LEN(A1) 26、计算字符串中单字节字符个数（例如数字或字母） 公式：=2*LEN(A1)-LENB(A1) 27、两列合并成一列（数据从第2行开始） 公式：=OFFSET($A$1,INT((ROW(A1)-1)/2) 1,MOD(ROW(A1)-1,2)) 28、计算此刻距离国庆还有多少个小时 公式：=TEXT('2017-10-1'-NOW(),'[h]') 29、将小写金额转换成人民币大写金额 公式：=SUBSTITUTE(IF(-RMB(A2,2),TEXT(A2,';负')&TEXT(INT(ABS(A2) 0.5%),'[dbnum2]G/通用格式元;;')&SUBSTITUTE(TEXT(RIGHT(RMB(A2,2),2),'[dbnum2]0角0分;;整'),'零分','整'),'零元整'),'零角',IF(A2^2<1,,'零')) 30、根据开工日期和指定工作日天数计算完工日期 公式：=TEXT(WORKDAY('2017-8-28',270),'yyyy-m-d') 31、计算分数大于60分的人员个数 公式：=COUNTIF(B2:B10,'>60') 32、计算不重复数据个数 公式：=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10)) 33、计算本月总天数 公式：=DAY(DATE(YEAR(TODAY()),MONTH(NOW()) 1,)) 34、随机生成2017年任意一天的日期 公式：=TEXT(RANDBETWEEN('2017-1-1','2017-12-31'),'yyyy-m-d') 35、判断本年是闰年还是平年 公式：=IF(COUNT('2-29'),'闰年','平年') 36、计算文本字符串中数字的个数 公式：=COUNT(-MID(A2,ROW($1:$99),1)) 数组公式CTRL SHIFT 回车键三键结束 37、根据身份证号码判断星座 公式：=LOOKUP(--MID(A2,11,4),{101,'摩羯座';120,'水瓶座';219,'双鱼座';321,'白羊座';420,'金牛座';521,'双子座';622,'巨蟹座';723,'狮子座';823,'处女座';923,'天秤座';1024,'天蝎座';1123,'射手座';1222,'摩羯座'}) 38、多条件查找 公式：=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)*(条件区域3=条件3)),结果区域) 39、提取字符串中任意位置的数值 公式：=LOOKUP(9^9,--LEFT(MID(A2,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A2&'0123456789')),99),ROW($1:$99))) 40、制作工资条公式 公式：=CHOOSE(MOD(ROW(A1)-1,3) 1,A$1,INDEX(A$2:A$11,INT((ROW(A1)-1)/3) 1),'') 公式右拉，然后下拉 41、计算本月总天数 公式：=DAY(EOMONTH(TODAY(),0)) 42、生成1,1,1,2,2,2,3,3,3…的循环序列 公式：=INT((ROW(A1)-1)/3) 1 任意位置输入公式，然后下拉 43、根据采购日期和付款天数设置提醒 公式：=TEXT(TODAY()-采购日期-付款协议天数,'已超出付款期限0天;距离付款期限还有0天;今天是最后付款期限') 44、判断今天是星期几 公式：=TEXT(TODAY(),'AAAA') 45、VLOOKUP函数反向查询 公式：=VLOOKUP(C1,IF({1,0},B1:B10,A1:A10),2,0) 46、生成1,2,3,1,2,3,1,2,3,…,1,2,3的循环序列 公式：=MOD(ROW(A1)-1,3) 1 47、九九乘法表公式 公式：=IF(AND(ROW(A1)>=COLUMN(A1),ROW(A1)<10),ROW(A1)&'x'&COLUMN(A1)&'='&ROW(A1)*COLUMN(A1),'') 48、计算大于等于500并且小于900的数字的个数 公式：=COUNTIFS(A1:A10,'>500',A1:A10,'<900') 49、计算大于等于500并且小于900的数字的总和 公式：=SUMIFS(A1:A10,A1:A10,'>500',A1:A10,'<900') 50、判断两个单元格内容是否一致（区分大小写字母） 公式：=EXACT(A2,B2) 51、根据身份证号码判断性别 公式：=IF(ISODD(--MID(B2,17,1)),'男','女') 52、根据日期判断属于第几季度 公式：=TEXT(LEN(2^MONTH(A2)),'第0季度') 53、多列数据合并成一列 公式：=INDIRECT(TEXT(SMALL(IF($A$1:$E$10<>'',ROW($1:$10)*100 COLUMN(A:E)),ROW(A1)),'r0c00'),0) 数组公式CTRL SHIFT 回车键三键结束，公式下拉 54、计算大于等于60分的分数的平均分 公式：=AVERAGEIF(A1:A10,'>=60') 55、计算双色球中奖概率 公式：=1/(COMBIN(33,6)*COMBIN(16,1)) 56、生成A-Z的26个英文字母 公式：=CHAR(ROW(A65)) 公式下拉 57、计算数字1-1000的总和 公式：=SUMPRODUCT(ROW(1:1000)) 58、计算两个日期之间的工作日天数 公式：=NETWORKDAYS(A1,B1) 59、计算两个日期之间间隔的天数 公式：=DATEDIF(A1,B1,'D') 60、计算两个日期之间间隔的月数 公式：=DATEDIF(A1,B1,'M') 61、计算两个日期之间间隔的年数 公式：=DATEDIF(A1,B1,'Y') 62、返回当前工作簿的名称 公式：=MID(CELL('filename'),FIND('[',CELL('filename')) 1,FIND(']',CELL('filename'))-FIND('[',CELL('filename'))-1) 63、计算不重复数据个数 公式：=SUM(N(FREQUENCY(A2:A11,A2:A11)>0)) 数组公式CTRL SHIFT 回车键三键结束 |
|
|
