设为首页收藏本站
开启辅助访问

Excel精英培训网

 找回密码
 注册
Excel精英培训网»论坛 › Excel 培训班 › Excel 函数初级班 › 【201502函初】练习十二 提交贴(已开贴)
数据透视表40+个常用小技巧，让你一次学会！
12
返回列表 发新帖
楼主: xk465592765

[通知] 【201502函初】练习十二 提交贴(已开贴)

[复制链接]
发表于 2015-11-5 16:27 | 显示全部楼层
工作中常用的Excel函数公式，全印在一张超大鼠标垫上
C03
  1. =TEXT(A2,"000")
  2. =TEXT(A3,"0.00")
  3. =TEXT(A4,">!0;<!0;=!0;")
  4. =TEXT(A5,">!0;<!0;=!0;")
  5. =TEXT(A6,">!0;<!0;=!0;")
  6. =TEXT(A7,"0;-0;;")
  7. =TEXT(A8,"0;-0;;")
  8. =TEXT(A9,"0;-0;;")
  9. =TEXT(A10,"0;;0")
  10. =TEXT(A11,"0;;0")
  11. =TEXT(A12,"0;;0")
  12. =TEXT(A13,";-0;0")
  13. =TEXT(A14,";-0;0")
  14. =TEXT(A15,";-0;0")
  15. =TEXT(A16,";0;")
  16. =TEXT(A17,";0;")
  17. =TEXT(A18,";0;")
  18. =TEXT(A19,"00.00%")
  19. =TEXT(SUBSTITUTE(A20,"percent","%"),"0.00")
  20. =TEXT(A21,"$$$$#,#.00")
  21. =TEXT(A22,"aaaa")
  22. =TEXT(A23,"dddd")
  23. =TEXT(A24,"现在时间：m/d [$$$$-409]h:mm AM/PM;@")
  24. =TEXT(A25,"[DBNum1]yyyy年mm月d日")
  25. =TEXT(HOUR(A26)*3600+MINUTE(A26)*60+SECOND(A26),"#")
  26. =TEXT(A27,"m月份产值")
  27. =TEXT(DATEDIF(A28,TODAY(),"Y"),"#年")
  28. =TEXT(MONTH(A29),"[>=7]下半年;上半年")
  29. =TEXT(A30,"00!层00!栏")
  30. =TEXT(A31,"本月我的工资#元")
  31. =TEXT(A32,"@@@@")
  32. =TEXT(A33,"@\;@\;@\;@\;@\;")
  33. =TEXT(0&A34-60,"[>=30]优;[>=20]良;及格;不及格")
  34. =TEXT(0&A35-60,"[>=30]优;[>=20]良;及格;不及格")
  35. =TEXT(0&A36-60,"[>=30]优;[>=20]良;及格;不及格")
  36. =TEXT(0&A37-60,"[>=30]优;[>=20]良;及格;不及格")
  37. =TEXT(A38,"0000k+000.000")
  38. =TEXT(A39,"存入;支出;平衡")
  39. =TEXT(A40,"存入;支出;平衡")
  40. =TEXT(A41,"存入;支出;平衡")
  41. =TEXT(INT(SUBSTITUTE(A42,"小时",)/24),"#天")&TEXT(MOD(SUBSTITUTE(A42,"小时",),24),"#小时")
复制代码
回复

使用道具 举报

工作中最常用的函数公式，全印在一张超大的鼠标垫上
发表于 2015-11-5 17:36 | 显示全部楼层
工作中常用的Excel函数公式，全印在一张超大鼠标垫上
201502函初：A10
D2单元格开始依次至D42
=TEXT(A2,"000")
=TEXT(A3,"0.00")
=TEXT(A4,">!0;<!0;0")
=TEXT(A5,">!0;<!0;0")
=TEXT(A6,">!0;<!0;0")
=TEXT(A7,"0;!-0;;")
=TEXT(A8,"0;!-0;;")
=TEXT(A9,"0;!-0;;")
=TEXT(A10,"0;;0")
=TEXT(A11,"0;;0")
=TEXT(A12,"0;;0")
=TEXT(A13,";!-0;0;")
=TEXT(A14,";!-0;0;")
=TEXT(A15,";!-0;0;")
=TEXT(A16,";0;;")
=TEXT(A17,";0;;")
=TEXT(A18,";0;;")
=TEXT(A19,"0.00%")
=SUBSTITUTE(A20,"percent","")/100
=TEXT(A21,"＄#,##0.00;＄-#,##0.00")
=TEXT(DAY(A22),"[$-804]aaaa")
=TEXT(WEEKDAY(A23),"dddd")
=TEXT(A24,"现在时间：[$-409]m/d h:mm AM/PM")
=TEXT(A25,"[DBnum1]yyyy年m月d日")
=TEXT(A26,"[s]")
=TEXT(A27,"m月份产值")
=TEXT(DAYS360(A28,TODAY()),"y年")
=TEXT(A29-DATEVALUE("2015-6-30"),"下半年;上半年")
=TEXT(A30,"00层00栏")
=TEXT(A31,"本月我的工资0元")
=TEXT(A32,"@@@@")
=TEXT(A33,"@!;@!;@!;@")
=TEXT(A34-60,"[>=30]优;[>=20]良;及格;不及格")
=TEXT(A35-60,"[>=30]优;[>=20]良;及格;不及格")
=TEXT(A36-60,"[>=30]优;[>=20]良;及格;不及格")
=TEXT(A37-60,"[>=30]优;[>=20]良;及格;不及格")
=TEXT(A38,"0000K+##0.00")
=TEXT(A39,"存入;支出;平衡")
=TEXT(A40,"存入;支出;平衡")
=TEXT(A41,"存入;支出;平衡")
=INT(2120/24)&"天"&MOD(2120,24)&"小时"
回复

使用道具 举报

沐颜
发表于 2015-11-5 18:04 | 显示全部楼层
工作中最常用的Excel函数公式，全印在一张鼠标垫上
  1. =TEXT(A2,"000")
复制代码
  1. =TEXT(A3,"0.00")
复制代码
  1. =TEXT(A4,">;<;=")&0
复制代码
  1. =TEXT(A7,"[=0] ")
复制代码
  1. =TEXT(A10,"[<0] ")
复制代码
  1. =TEXT(A13,";-0;0;@")
复制代码
  1. =TEXT(A16,";0;;@")
复制代码
  1. =TEXT(A19,"0.00%")
复制代码
  1. --SUBSTITUTE(A20,"percent","")/100
复制代码
  1. =TEXT(A21,"$#,##0.00")
复制代码
  1. =TEXT(A22,"aaaa")
复制代码
  1. =TEXT(A23,"dddd")
复制代码
  1. ="现在时间："&TEXT(A24,"mm/dd h:mm AM/PM")
复制代码
  1. =TEXT(A25,"[dbnum1]yyyy年m月d日")
复制代码
  1. =TEXT(A26,"[s]")
复制代码
  1. =TEXT(A27,"m月份产量")
复制代码
  1. =TEXT(DATEDIF(A28,NOW(),"Y"),"0年;;")
复制代码
  1. =TEXT(MONTH(A29),"[>6]下半年;上半年")
复制代码
  1. =TEXT(A30,"0层00栏")
复制代码
  1. =TEXT(A31,"本月我的工资0元")
复制代码
  1. =TEXT(A32,"@@@@")
复制代码
  1. =TEXT(A33,REPT("@\;",5))
复制代码
  1. =TEXT(TEXT(A34,"[>=90]优;[<60]不及格;0"),"[>=80]良;及格")
复制代码
  1. =TEXT(A38,"0K+000.000")
复制代码
  1. =TEXT(A39,"收入;支出;平衡")
复制代码
回复

使用道具 举报

fjmxwrs
发表于 2015-11-5 21:15 | 显示全部楼层
C11:fjmxwrs
  1. =TEXT(A2,"000")
  2. =TEXT(A3,"0.00")
  3. =TEXT(A4,">;<;=;")&"0"
  4. =TEXT(A7,"0;-0;;@")
  5. =TEXT(A10,"0;;0;@")
  6. =TEXT(A13,";-0;0;@")
  7. =TEXT(A16,";0;;@")
  8. =TEXT(A19,"0.00%")
  9. '英文百分比不会
  10. =TEXT(A21,"$0,000.00")
  11. =TEXT(A22,"aaaa")
  12. =TEXT(A23,"[$-802]aaaa")
复制代码
  1. =TEXT(A24,"现在时间:mm/dd h:m AM/PM")
  2. =TEXT(A25,"[DBNum1]yyyy年m月d日")
  3. =TEXT(A26,"[s]")
  4. =TEXT(A27,"m月份产值")
  5. =DATEDIF(A28,TODAY(),"y")&"年"
  6. =TEXT(MONTH(A29),"[>6]下半年;[<7]上半年")
  7. =TEXT(A30,"00层00栏")
  8. =TEXT(A31,"本月我的工资0元")
  9. =TEXT(A32,"@@@@")
  10. =REPT(A33&"；",4)&A33
  11. =LOOKUP(A34,{0,60,80,90},{"不及格","及格","良","优"})
  12. =TEXT(A38,"0K+???.000")
  13. =TEXT(A39,"存入;支出;平衡;@")
  14. =INT(LEFT(A42,LEN(A42)*2-LENB(A42))/24)&"天"&MOD(LEFT(A42,LEN(A42)*2-LENB(A42)),24)&"小时"
复制代码
回复

使用道具 举报

发表于 2015-11-5 23:47 | 显示全部楼层
A组长：绵花糖
D2=TEXT(A2,"000")
D3=ROUND(A3,2)
D4=TEXT(A4,">!0;<!0;=!0")
D5=TEXT(A5,">!0;<!0;=!0")
D6=TEXT(A6,">!0;<!0;=!0")
D7=TEXT(A7,"0;-0;")
D8=TEXT(A8,"0;-0;")
D9=TEXT(A9,"0;-0;")
D10=TEXT(A10,"0;;0")
D11=TEXT(A11,"0;;0")
D12=TEXT(A12,"0;;0")
D13=TEXT(A13,";-0;0")
D14=TEXT(A14,";-0;0")
D15=TEXT(A15,";-0;0")
D16=TEXT(A16,";0;")
D17=TEXT(A17,";0;")
D18=TEXT(A18,";0;")
D19=TEXT(A19,"0.00%")
D20=SUBSTITUTE(A20,"percent",)/100
D21=TEXT(A21,"$#,##0.00")
D22=TEXT(A22,"aaaa")
D23=TEXT(A23,"dddd")
D24=TEXT(A24,"现在时间：mm-dd h:m AM/PM")
D25=TEXT(A25,"[dbnum1]e年m月d日")
D26=TEXT(A26,"[s]")
D27=TEXT(A27,"m月份产值")
D28=DATEDIF(A28,TODAY(),"Y")&"年"
D29=TEXT(MONTH(A29),"[>6]下半年;[<=6]上半年")
D30=TEXT(A30,"00层00栏")
D31=TEXT(A31,"本月我的工资0元")
D32=REPT(A32,4)
D33=TEXT(A33,"@!;@!;@!;@!;")
D34=TEXT(0&(A34-59),"[>=30]优;[>=20]良;及格;不及格")
D35=TEXT(0&(A35-59),"[>=30]优;[>=20]良;及格;不及格")
D36=TEXT(0&(A36-59),"[>=30]优;[>=20]良;及格;不及格")
D37=TEXT(0&(A37-59),"[>=30]优;[>=20]良;及格;不及格")
D38=TEXT(A38,"0000K+000.000")
D39=TEXT(A39,"存入;平衡;支出")
D40=TEXT(A40,"存入;平衡;支出")
D41=TEXT(A41,"存入;平衡;支出")
D42=INT(SUBSTITUTE(A42,"小时",)/24)&"天"&MOD(SUBSTITUTE(A42,"小时",),24)&"小时"



回复

使用道具 举报

发表于 2015-11-6 21:00 | 显示全部楼层
函初[201502]A03： rlzcjrrmy
   =TEXT(A2,"000")
=TEXT(A3,"0.000")
=TEXT(A4,"[>0]>!0;[<0]<!0;=0")
=TEXT(A5,"[>0]>!0;[<0]<!0;=0")
=TEXT(A6,"[>0]>!0;[<0]<!0;=0")
=TEXT(A7,"[>0]0;[<0]-0;""""")
=TEXT(A8,"[>0]0;[<0]-0;""""")
=TEXT(A9,"[>0]0;[<0]-0;""""")
=TEXT(A10,"[>0]0;[<0]"""";0")
=TEXT(A11,"[>0]0;[<0]"""";0")
=TEXT(A12,"[>0]0;[<0]"""";0")
=TEXT(A13,"[>0]"""";[<0]-0;0")
=TEXT(A14,"[>0]"""";[<0]-0;0")
=TEXT(A15,"[>0]"""";[<0]-0;0")
=TEXT(A16,"[>0]"""";[<0]0;""""")
=TEXT(A17,"[>0]"""";[<0]0;""""")
=TEXT(A18,"[>0]"""";[<0]0;""""")
=TEXT(A19,"0.00%")
=TEXT(LEFT(A20,LEN(A20)-7),"!0!.00")
=TEXT(A21,"$#,##0.00;-$#,##0.00")
=TEXT(A22,"aaaa")
=TEXT(A23,"dddd")
=TEXT(A24,"""现在时间""!:mm/d h:mm AM/PM;@")
=TEXT(A25,"[dbnum1]yyyy年m月d日")
=TEXT(A26,"[s]")
=TEXT(A27,"m月份产值")
=DATEDIF(A28,NOW(),"y")&"年"
=TEXT(MONTH(A29),"[>6]下半年;上半年")
=TEXT(A30,"##层##栏")
=TEXT(A31,"本月我的工资G/通用格式元")
=TEXT(A32,"@@@@")
=TEXT(A33,"@"";""@"";""@"";""@"";""@")
=TEXT(0&(A34-60),"[>29]优;[>19]良;及格;不及格")
=TEXT(0&(A35-60),"[>29]优;[>19]良;及格;不及格")
=TEXT(0&(A36-60),"[>29]优;[>19]良;及格;不及格")
=TEXT(0&(A37-60),"[>29]优;[>19]良;及格;不及格")
=TEXT(A38,"K 0+000.???")
=TEXT(A39,"[>0]存入;[<0]支出;平衡")
=TEXT(A40,"[>0]存入;[<0]支出;平衡")
=TEXT(A41,"[>0]存入;[<0]支出;平衡")
=TEXT(LEFT(A42,LEN(A42)-2)/24,"0天")&TEXT(MOD(LEFT(A42,LEN(A42)-2),24),"0小时")
回复

使用道具 举报

o豆苗
发表于 2015-11-13 21:43 | 显示全部楼层
B09:o豆苗
  1. =TEXT(A2,"000")
复制代码
  1. =ROUND(A3,2)
复制代码
  1. =ROUND(A3,2)
复制代码
  1. =TEXT(A7,"0;-0;")
复制代码
  1. =TEXT(A10,"0;")
复制代码
  1. =TEXT(A13,";-0;0")
复制代码
  1. =TEXT(A16,";0")
复制代码
  1. =TEXT(A19,"#0.00%")
复制代码
  1. =TEXT(LEFT(A20,FIND("p",A20)-1),"!0!.00")
复制代码
  1. =TEXT(A21,"$#,##0.00")
复制代码
  1. =TEXT(A22,"aaaa")
复制代码
  1. =TEXT(A23,"dddd")
复制代码
  1. ="现在时间："&TEXT(A24,"mm/dd h:mm AM/PM")
复制代码
  1. =TEXT(A25,"[DBNum1]e年m月d日")
复制代码
  1. =TEXT(A26,"[s]")
复制代码
  1. =TEXT(A27,"m")&"月份产值"
复制代码
  1. =TEXT(ABS(YEAR(A28)-YEAR(TODAY())),"0年")
复制代码
  1. =TEXT(MONTH(A29),"[>6]下半年;上半年")
复制代码
  1. =TEXT(A30,"00层00栏")
复制代码
  1. =TEXT(A31,"本月我的工资0元")
复制代码
  1. =TEXT(A32,"@@@@")
复制代码
  1. =TEXT(A33,"@!;@!;@!;@!;@")
复制代码
  1. =TEXT(0&A34-60,"[>=30]优;[>=20]良;及格;差")
复制代码
  1. =TEXT(A38,"0k+000.000")
复制代码
  1. =TEXT(A39,"存入;支出;平衡")
复制代码
  1. =TEXT(2120/24/24,"[h]天h小时")
复制代码
回复

使用道具 举报

12
返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2025-8-16 05:41 , Processed in 0.464351 second(s), 7 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

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