中人网

标题: Excel强大的功能，你知多少？-转载 [打印本页]
作者: 糖糖2003    时间: 2011-12-12 12:54
标题: Excel强大的功能，你知多少？-转载
本帖最后由 糖糖2003 于 2011-12-12 13:09 编辑
  K- {% l1 R0 e$ N. C2 b# p% D% D& Y- m! K0 U+ t5 S2 Q
Microsoft Excel是微软公司的办公软件Microsoft office的组件之一，Excel中大量的公式函数可以应用选择，可以实现许多方便的功能，给使用者方便。 很难想象如果没有它，我们现在工作会处于什么状态。9 E: z& m1 N) l+ O% B( X# `" _7 x9 F
! s0 G) F8 T" O. s& w7 @
在工作中，学习收集了一些方法，现分享给大家，欢迎补充并指出不足之处噢！
, d9 b6 A8 G6 _+ a有几个自己都没有用过，不知效果如何，大家抽空可以一起试试~~
1 K/ {6 J1 F0 H7 q+ D" l
! T4 O$ y2 u) a一、合同到期提醒" c; Y1 \( ~; ]$ M4 {& E) I3 G
      假如 A1单元格显示签订合同时间 2005-11-05 6 C2 y' m4 \' j, u5 N7 T5 O* n6 q2 s
       B1单元格显示合同到期时间  2007-10-31
# `9 X) i# j3 A5 g. U   当前时间由系统提取出来 假设当前日期为 2007-10-14 B2 [  ?3 \/ c# K4 ?
     想让C1单元格显示: 在合同即将到期30天内提醒:"合同即将到期", 且每日提醒 最好弹出提醒对话框,点击确定 当日不再提醒,点击取消当日在一定时间内继续提醒
; e" m6 P6 \6 ~8 b) y6 t( d6 w=IF(ISERROR(DATEDIF(TODAY(),B2,"D")),"已过期",IF(DATEDIF(TODAY(),B2,"D")>30,"未到期",IF(DATEDIF(TODAY(),B2,"D")=30,"今天到期","差"DATEDIF(TODAY(),B2,"D")&"天到期")
% v) B, }1 J$ |% Z差多少天到期的合同 如何让反应的文字以红色字体体现出来,这样好统计2 R' R5 L5 @" \- l2 M  |
选中C列,格式>条件格式>公式>=AND(DATEDIF(TODAY(),B2,"D")<=30,DATEDIF(TODAY(),B2,"D")>0),再点击格式>在单元格格式对话框中选图案>选红色.确定
* u+ ^1 c) _# ?7 r% H8 T5 M+ a% c, N+ x! J
二、新税法下的个税计算公式
/ Y' N$ Q$ u$ D: e: A  E6 c* P8 S公式=ROUND(MAX((应发工资-3500)*{3,10,20,25,30,35,45}/100-{0,21,111,201,551,1101,2701}*5,0),2)；其中速扣数的公倍数5也可以乘以到里面
" q5 B1 w# [2 A2 W' r9 X1 v- Y3 O9 x
三、年龄（岁数）和工龄的计算公式
* E/ Y/ t: z8 X+ ?9 S公式=DATEDIF(I4,TODAY(),"y")"年"&DATEDIF(I4,TODAY(),"ym")&"月"&DATEDIF(I4,TODAY(),"md")&"日"；其中I4为数据源
& S, y2 E( S4 H0 r+ `8 U! _1 i, }- E9 t9 L. _  _
四、平均年龄计算公式
' q9 m% D( ?; _) K! _7 v公式=AVERAGE(TODAY()-H3:H24)/365；其中H3:H24为计算范围；注：更新范围时，单回车键不行，需要ctrl+shift+回车键
) i, o! W9 ?' j5 h" o3 n6 d3 E
' d. G! c6 }0 F五、数据查询并定位
/ J/ I3 r( K# J: ^' P1、O35=（输入查找内容）；) M" ^" v  B3 G. O
2、O36=IF(ISERROR(MATCH(O35,sheet1!C:C,0)),"",MATCH(O35,sheet1!C:C,0))。（意思为显示的位置,其中C为数据列）；! G5 x2 @3 @1 v
3、O37=HYPERLINK("#sheet1！C"&O36,IF(ISNUMBER(O36),"点击显示","没有找到"))。（意思是查询结果）；* ~: ^: l" K! [; A/ X7 n" O3 P
注：可以应用在从大量数据中查找所需数据，当然你也可以通过Excel自带的查询工具（ctrl+f为快捷键）。9 w- Q0 C. k  Z

8 ~+ {5 x8 \; a. C2 n& j六、如何在Excel中插入Flash时钟的?
: @, M6 _$ V0 B4 ], \动态时钟不是用函式运算、自动化功能制作出来的,这只是简单的插入Flash文挡的功能而已，而且只要你有Flash文件，任何人都可以轻松自行制作
  b* q) r5 Z  i+ O* J9 H制作方法
" z* h" h6 y6 ~; K% y' n/ P% I第1步 首先打开一个空白Excel文件，点击“视图” → 然后点选【控件工具箱】，→点击“其他控件”。如下图：
! W8 P8 ]4 U8 n- _/ K6 @. ?* M第2步 然后再点击[Shockwave Flash Object]项目，表示要插入Flash物件
) t9 F9 h! V+ N9 `/ y第3步 接下来，鼠标会变成一个小十字，此时可以在Excel编辑区中画一个大小适中的方框，这个方框就是  用来显示Flash时钟的内容的
6 w1 f' l4 e3 B- p9 I第4步 画好方框后，接着点击【属性】，准备设置属性。
- A9 V3 v9 Q# z  p/ o第5步 出现「属性」对话框后，将DeviceFont设置成False；将Eebedmovie设置成True；将Enabled设置成True；将Locked设置成True；将Loop设置成True；将Menu设置成False；并在“Movie”右侧填入时钟的地址与名称
& ^4 R- L0 ]! n7 `; c, h2 i第6步 退出设计模式，全部完成
. k7 B: S9 g0 l8 v# {; k2 w- t) m1 g) `7 f8 F$ c. k
七、与身份证相关公式! x! t* a" D& |# @
1、身份证验证
: S6 a& }( X! P公式=IF(LEN(A2)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2),IF(LEN(C3)=15,ISNUMBER(--TEXT(19&MID(C3,7,6),"#-00-00"))))
3 K2 E. n& z) Q% z1 ?+ A/ S9 k! K) x" t* P. ]& {; B& w
2、提取性别 ; c  k7 I' ?4 D* _0 |
公式=CHOOSE(MOD(MID(A2,LEN(A2)/2+8,1),2)+1,"女","男")；或! L8 y: U8 K/ D5 [2 s  Q4 k
公式=IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)=1,"男","女")。
) o: X) r& |2 [* I% Y2 I& q+ I7 N$ [+ |5 P8 ~: @: ~
3、判断生肖1 S* \) ~( b- ?; ]& |. l  l8 w' s
公式=CHOOSE(MOD(MID(A2,LEN(A2)/2,2),12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")；9 B# x& K+ \; V: Y, _) ?
以上A2为身份证数据源
# i( e2 T2 `; S
& N/ h. w* V/ q: P1 s  [4、提取出生日期* \( D4 O1 }9 o
公式=TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2-1),8),"#-##-##")；
6 P# J  g; u1 b! a$ t. p7 U0 B) `6 h: W- [6 `: L! l: Y
5、提取年龄（整岁）, Z$ i  s8 c, ]: X9 A: y
公式=INT(DAYS360(TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2-1),8),"#-##-##"),TODAY())/360)；0 n9 J1 @* o& ~
  |4 ]+ c! h% i; Y
6、判断星座
( \# t2 g$ k( |" L8 ?8 V公式=VLOOKUP(VALUE("1900-"&TEXT(MID(A2,LEN(A2)/2+2,4),"#-##")),{1,"摩羯座";21,"水瓶座";50,"双鱼座";81,"白羊座";112,"金牛座";143,"双子座";174,"巨蟹座";205,"狮子座";236,"处女座";268,"天秤座";298,"天蝎座";328,"人马座";357,"摩羯座"},2,TRUE)；
. \. S  c& z; X6 I* X0 e9 P# X' j1 W* D: u' y4 O( Z8 t; v
7、15位转换为18位
8 C) d3 Z9 F7 T. S6 N; @$ M! s公式=IF(LEN(C2)=15,REPLACE(C2,7,,19)&MID("10X98765432",MOD(SUMPRODUCT(MID(REPLACE(C2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),C2)；$ x& v6 {5 c3 A  U9 _& r' W
* s; m& I( c# M! a0 b6 V
作者: yytyuxi    时间: 2011-12-12 13:04
本帖最后由 yytyuxi 于 2011-12-12 13:39 编辑 " O+ j6 g/ I) D- d) E+ {" f

/ `* w! H3 d" d- T糖糖，怎么盗用我的帖子呀！也不注明转载地址呀？/ B( \, Q5 u' ]" ~6 o: h
原帖地址链接：Excel强大的功能，你知多少？ 现已有更新的啦。) j( u& l! E/ {8 i9 E- H' m
作者: sawasdee    时间: 2011-12-12 13:13
抢个板凳' M. e! S" ~! y7 j
太实用了，谢谢糖主
作者: so_so    时间: 2011-12-12 13:15
哇。。。判断星座都有
作者: 千羽凌波    时间: 2011-12-12 13:19
很实用~   谢谢了
作者: zhenxiaocc    时间: 2011-12-12 13:25
谢谢分享！帖子同样强大！
作者: yytyuxi    时间: 2011-12-12 13:55
yytyuxi 发表于 2011-12-12 13:04 - {7 ~$ i3 c3 w, _0 T3 H
糖糖，怎么盗用我的帖子呀！也不注明转载地址呀？
. f4 q) b2 C" L' G原帖地址链接：Excel强大的功能，你知多少？ 现已有更新 ...

$ ]! x. p2 w# f8 g) S, u1 G要粘全粘，给大家分享。
作者: 大内总管    时间: 2011-12-12 14:23
收藏学习！
作者: jiaoxiake1988    时间: 2011-12-12 14:24
有用，多谢多谢
作者: 大茆    时间: 2011-12-12 15:11
这个好，顶一个，顺手学习用用
作者: 结爱依    时间: 2011-12-12 15:26
收藏
作者: Audy.zhang    时间: 2011-12-12 15:31
很实用~   谢谢了
作者: 幽幽果    时间: 2011-12-12 15:42
谢谢分享!
作者: redstard    时间: 2011-12-12 16:07
很好很强大，学习下。
作者: _天之痕__    时间: 2011-12-12 21:32
貌似有点难 哈哈~~~
作者: skytear    时间: 2011-12-12 21:54
感觉有点复杂。
作者: wpw72    时间: 2011-12-13 18:46
不错不错，学习中！！！！！！！
作者: qiuyedaoqing    时间: 2011-12-15 14:52
呵呵，很多都现在在用呢、
作者: Amaral    时间: 2012-2-10 11:19
太强大了...特别是合同到期提醒
作者: ce2007    时间: 2012-2-22 22:05
谢谢
作者: 薇纳斯    时间: 2012-3-8 11:23
很多在用，但不是自己写的公式，自己搞不出来
作者: 克己修身    时间: 2012-3-8 11:45
很实用，谢谢分享！
作者: guchy88    时间: 2012-3-16 12:08
有点晕，呵呵
作者: mininihr    时间: 2012-3-16 15:07
好贴，好贴！！
作者: 饺子09    时间: 2012-3-16 16:02
很实用~   谢谢了
作者: 随心微笑    时间: 2013-8-27 15:33
很值得分享   学习
作者: 玉面小飞龙    时间: 2013-10-13 19:03
顶一个
作者: 姠ㄖ葵の嶶笶    时间: 2013-10-15 14:36
非常有用，收着啦 哈哈
3 L6 W2 a1 c' q; ?0 g: W
作者: cherrylinw    时间: 2013-11-17 11:34
太实用了，谢谢楼主，万分感谢！！
作者: crystalweng    时间: 2013-11-29 15:53
还有星座……这绝对可以的
作者: SIYECAO88    时间: 2014-3-7 11:21
哈哈，已经转过啦，谢谢楼主
作者: xuelai    时间: 2014-4-24 16:24
赞一个呢
作者: fuzingzing    时间: 2014-5-26 11:17
很实用，谢谢分享！
作者: 怡君    时间: 2014-5-30 17:44
原来这么强大的功能，收藏慢慢学习。
作者: hema7    时间: 2014-6-15 01:04
很好很强大，学习下。
作者: 圣人无常师    时间: 2014-9-4 10:03
谢谢分享~~~
作者: quitesky    时间: 2015-10-22 17:03
功能很强大，感谢分享。
作者: 蒲公英的说说    时间: 2015-10-24 14:08
鼓掌鼓掌，很全面呀



欢迎光临 中人网 (http://bbs.chinahrd.net/) Powered by Discuz! X2.5