Excel强大的功能，你知多少？

yytyuxi

本文版权属yytyuxi所有
转载请注明:
中人网论坛-yytyuxi-(链接地址:http://community.chinahrd.net/forum.php?mod=viewthread&tid=498770)

8 r, e6 h( G" I6 }9 O9 h

    Excel强大的功能，你知多少？

3 `: ~7 Z6 s/ [. t1 p       Microsoft Excel是微软公司的办公软件Microsoft office的组件之一，Excel中大量的公式、函数、工具可以选择应用，可以实现许多的功能，给使用者提供了极大的方便。 很难想象如果没有它，我们现在工作会处于什么状态。
$ r+ Y6 K' u9 {& {0 |  c& X      本人在工作过程中，收集了一些方法和技巧，现分享给大家，欢迎补充！
一、合同到期提醒
      假如 A1单元格显示签订合同时间 2005-11-05
       B1单元格显示合同到期时间  2007-10-31
& A5 b; W, b3 @8 k( q       当前时间由系统提取出来 假设当前日期为 2007-10-1
: n. h8 C$ d. A& T' G       想让C1单元格显示: 在合同即将到期30天内提醒:"合同即将到期", 且每日提醒 最好弹出提醒对话框,点击确定 当日不再提醒,点击取消当日在一定时间内继续提醒
+ P- ]4 i9 f8 _+ k* \7 ]=IF(ISERROR(DATEDIF(TODAY(),B2,"D")),"已过期",IF(DATEDIF(TODAY(),B2,"D")>30,"未到期",IF(DATEDIF(TODAY(),B2,"D")=30,"今天到期","差"&DATEDIF(TODAY(),B2,"D")&"天到期")
4 c0 V: h5 H2 ~  ^; J. `# [( V/ \       差多少天到期的合同 如何让反应的文字以红色字体体现出来,这样好统计
选中C列,格式>条件格式>公式>=AND(DATEDIF(TODAY(),B2,"D")<=30,DATEDIF(TODAY(),B2,"D")>0),再点击格式>在单元格格式对话框中选图案>选红色.确定。
/ u) n+ }* |- n7 E- r二、新税法下的个税计算公式
      公式=ROUND(MAX((应发工资-3500)*{3,10,20,25,30,35,45}/100-{0,21,111,201,551,1101,2701}*5,0),2)；其中速扣数的公倍数5也可以乘以到里面。
三、年龄（岁数）和工龄的计算公式
  g; t. ~% H0 p1 H0 D* f      公式=DATEDIF(I4,TODAY(),"y")&"年"&DATEDIF(I4,TODAY(),"ym")&"月"&DATEDIF(I4,TODAY(),"md")&"日"；其中I4为数据源。
四、平均年龄计算公式
      公式=AVERAGE(TODAY()-H3:H24)/365；其中H3:H24为计算范围；注：更新范围时，单回车键不行，需要ctrl+shift+回车键！
! g7 T& \9 D& O/ l+ ]! N
2 S1 h: d5 d) ?2011年11月22日更新

8 W+ T& L! e' C/ `. z% B, B  Y五、数据查询并定位
       1、O35=（输入查找内容）；
       2、O36=IF(ISERROR(MATCH(O35,sheet1!C:C,0)),"",MATCH(O35,sheet1!C:C,0))。（意思为显示的位置,其中C为数据列）；
. X: n+ |' n2 s0 M& ?4 Z      3、O37=HYPERLINK("#sheet1！C"&O36,IF(ISNUMBER(O36),"点击显示","没有找到"))。（意思是查询结果）；
      注：可以应用在从大量数据中查找所需数据，当然你也可以通过Excel自带的查询工具（ctrl+f为快捷键）。

8 C+ ?9 @3 l/ f# B+ [2011年11月26日更新
0 \( C1 c. L' b# k! v5 S
9 t- k8 F4 E! ]2 p1 v$ i+ q六、如何在Excel中插入Flash时钟的?
( K3 }0 g! _, @( w' e      动态时钟不是用函式运算、自动化功能制作出来的,这只是简单的插入Flash文挡的功能而已，而且只要你有Flash文件，任何人都可以轻松自行制作。
制作方法：
     第1步 首先打开一个空白Excel文件，点击“视图” → 然后点选【控件工具箱】，→点击“其他控件”。
; A  U0 \8 Y1 G- |7 ?$ h6 d  n     第2步 然后再点击[Shockwave Flash Object]项目，表示要插入Flash物件。
     第3步 接下来，鼠标会变成一个小十字，此时可以在Excel编辑区中画一个大小适中的方框，这个方框就是  用来显示Flash时钟的内容的。
     第4步 画好方框后，接着点击【属性】，准备设置属性。
     第5步 出现「属性」对话框后，将DeviceFont设置成False；将Eebedmovie设置成True；将Enabled设置成True；将Locked设置成True；将Loop设置成True；将Menu设置成False；并在“Movie”右侧填入时钟的地址与名称。
     第6步 退出设计模式，全部完成。
" U1 }/ M0 i3 S% \. H, \* }' H
7 Z: b2 T4 ^5 ^2011年11月29日更新
( [  M* g: k/ B6 |) @
4 x3 H  T4 {, e  ~# S七、与身份证相关公式
( }% b0 T( z- e# o2 a9 k6 ~! j

       1、身份证验证

       公式=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"))))；

      2、提取性别

       公式=CHOOSE(MOD(MID(A2,LEN(A2)/2+8,1),2)+1,"女","男")；或

       公式=IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)=1,"男","女")。

      3、判断生肖

       公式=CHOOSE(MOD(MID(A2,LEN(A2)/2,2),12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")；

      以上A2为身份证数据源；

2011年12月2日更新

     4、提取出生日期
: }6 g7 U% C) K8 P' m2 t       公式=TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2-1),8),"#-##-##")；
* ]7 F* s( L  Z- }. l" m7 [" ^/ Z      5、提取年龄（整岁）
0 T# s0 B7 j( x% j( |5 W) X

       公式=INT(DAYS360(TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2-1),8),"#-##-##"),TODAY())/360)；

      6、判断星座

       公式=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)；

      7、15位转换为18位

       公式=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)；

  2011年12月9日更新
! r3 }9 f* n0 G  V% y
八、选中单元格的行与列变颜色
4 I1 M" k! K1 K: Z      1、条件格式里用公式里填，应用于=$1: $65536
      公式=(ROW()=CELL("row")) + (COLUMN()=CELL("col"))
      vba编辑器里填（用ALT+F11调出）：
      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
5 t' d$ e: J& C7 N: ]      Calculate
+ A( J6 [+ q5 X4 k4 S6 f      End Sub
      颜色在格式里自己调；
      2、或者直接在  vba编辑器里填：
, O$ c0 h( P5 ?6 _( pPrivate Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error Resume Next
% y0 }+ }' w4 f+ K, I. ICells.FormatConditions.Delete
. ~; k) J% Z6 I0 \. Q& NiColor = Int(50 * Rnd() + 2)
With Target.EntireRow.FormatConditions
7 R# K3 S! n+ |8 a$ G$ r7 H.Delete
.Add xlExpression, , "TRUE"
.Item(1).Interior.ColorIndex = iColor
+ ^9 s% }. @: Q! ^$ B( h/ pEnd With
8 g8 V7 D4 h! H3 m. c5 fWith Target.EntireColumn.FormatConditions
.Delete
.Add xlExpression, , "TRUE"
.Item(1).Interior.ColorIndex = iColor
End With
End Sub

2 U1 U+ Y6 L! ^' }! `5 C& K4 B2011年12月26日更新
9 U- x2 x# |$ ^3 f
      在应用“第八”的第二条后，发现文件就不能再编辑，慎用之。

2011年最后一天更新

九、重复数据去除
       从大量数据中，去除重复记录，保证数据不重复，可以利用excel自带工具"高级筛选“，假设数据区域为B列，打开高级筛选，选择列表区域，勾选”选择不重复的记录“。如下图：
5 i7 _& t  r& u3 F& P/ X; y              
% J$ Z. T( _" I- q2 z

2012年第一次更新（1月2日）

十、个税倒推公式(配合第二条使用）
% j6 I. J7 @: W5 i8 k       公式=ROUND(MAX((A4-3500-{0,105,555,1005,2755,5505,13505})/(1-{0.03,0.1,0.2,0.25,0.3,0.35,0.45})+3500,A4),2)，其中A4为税后数据。

2012年1月14日更新
$ _- K$ s. E' |" u" M  F
十一、用身份证号提取户籍地
" @' f5 f1 E" K& D) k  @       公式=IF(B2="","/",IF(ISERROR(LOOKUP(VALUE(LEFT(B2,2)),{11,"北京";12,"天津";13,"河北";14,"山西";15,"内蒙古";21,"辽宁";22,"吉林";23,"黑龙江";31,"上海市";32,"江苏";33,"浙江";34,"安徽";35,"福建";36,"江西";37,"山东";41,"河南";42,"湖北";43,"湖南";44,"广东";45,"广西";46,"海南";50,"重庆";51,"四川";52,"贵州";53,"云南";54,"***";61,"陕西";62,"甘肃";63,"青海";64,"宁夏";65,"新疆";71,"台湾";81,"香港";82,"澳门";"","0"})<0),"",LOOKUP(VALUE(LEFT(B2,2)),{11,"北京";12,"天津";13,"河北";14,"山西";15,"内蒙";21,"辽宁";22,"吉林";23,"黑龙";31,"上海";32,"江苏";33,"浙江";34,"安徽";35,"福建";36,"江西";37,"山东";41,"河南";42,"湖北";43,"湖南";44,"广东";45,"广西";46,"海南";50,"重庆";51,"四川";52,"贵州";53,"云南";54,"***";61,"陕西";62,"甘肃";63,"青海";64,"宁夏";65,"新疆";71,"台湾";81,"香港";82,"澳门";"","0"})))，其中B2是身份证数据。

# O; A0 `# F+ |6 ], t- z2012年2月24日更新

十二、去除单元格中的空格字符

3 E  O! q& |+ M7 k- M2 v

       选择整列，点击菜单“数据—分列”，第一步选“分隔符号”，第二步选择“空格”，第三步选择“文本”，完成。

2012年3月2日更新

十三、此工作簿包含其他数据源的链接，是否需要更新？

       有的时候，当我们打开XLS工作表文件时，会看到这样的对话框提示（如下图）：

               

       时间长了或者数据公式多的时候，不知道这个需不需要更新，而且每次都这样提示，觉得挺烦的。

       解决方法：

       1、编辑”→“链接”→“查看链接源”，之后您就可以对链接源进行编辑了，是想更改链接还是删除链接以断开数据源，都可以操作的。

　　2、查找工作表中已经引用链接的单元格。按CTRL+F键，输入“*.XLS”，然后查找，在结果中全选，就可以看到工作表中哪些单元格引用了链接，再进行相关操作。
% `% \* S* @  S' b6 e5 @7 q
6 i+ r' D- y& B. z2012年3月10日更新
: E0 \+ k4 V- \4 t( a. ^4 S6 u5 l
- }6 c. t  Z% D& g5 @% W

十四、自定义下拉菜单

      1、选中要做下拉列表的单元格。

      2、在菜单中选择：数据－有效性－［设置］－（允许）：序列；（来源）：输入内容，用,（逗号）分隔。如下图：

! U( ?; B& H3 w2 [0 j
1 A& u' M( n/ O7 t( S- z, T( z

2012年3月28日更新

! a# i6 ?* P4 z

十五、相关附件

      1、Office应用100例.rar

      2、Excel：人力资源管理篇.rar
这两个附件都超过了10M，有需要的加我QQ。

' A6 _% c/ e2 f5 a

2012年4月1日更新

8 y# f, m% t( l十六、出生日期按照月日排序
      有些单位会给员工预定生日蛋糕，这就涉及到统计当月过生日员工的姓名和人数，但出生日期不能直接排序或筛选出所需的数据。现提供两种方法：
  b: i3 ?( a0 f  [+ b7 d7 M" |      1、利用“分列”功能。首先，在“出生日期”列后增加两空列；其次，选中出生日期列，数据---分列---分隔符号，下一步，勾选其他输入“.”(日期连接符可能是“-”或者“/”)，下一步，完成。这三列单元格数字格式选择常规，而非日期。最后，进行排序即可。
      2、利用函数。首先，在“出生日期”列后增加两空列；其次，在第一空列输入=MONTH(A1)，第二空列输入=DAY(A1)，下拉填充公式。注：第一空列是出生月份，第二空列是出生日，其中，A列为出生日期；最后，进行排序即可。
! a7 d" E4 Z; T# h5 j( {5 |

- p# }/ B  x) B% x8 {+ n7 t

2012年6月26日更新

       这段时间事情比较多，一直没有更新，见谅！
十七、SUMIF的使用
9 c, C" x- f" a* `" z3 T! A1 h       SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中。例：同一种产品，在当月销售次数很多时，需要统计当月累加销售量，可以使用。具体操作如下：
       1、公式=SUMIF('2011年'!A:A,C2,'11年'!B:B)；
7 u$ n8 b- y- U) w       2、2011‘！为工作表名称；A列为产品名称；B列为产品销售数量；C2为统计产品名称；
       3、可以扩展到其他产品和月份统计；
       4、关键点是C列产品包含所有A列产品；
# b' O8 {8 Z! f7 a3 v( ^
' l4 J/ @! o5 s- ~& j

2012年7月13日更新

   
十八、回车键定位右单元格
       快速输入大量数据，点击回车键在默认下是向下移动，输入下一列数据时需要重新定位。
       解决方法：工具、选项、编辑、按Enter键后移动，选向右。

2012年7月17日更新
+ V! Z; k! f6 T& Y

十九、Round 函数
' s+ Q3 \- M8 w- r6 I, u" d8 o　   Round 函数，返回按指定位数进行四舍五入的数值。
　　ROUND(number, num_digits)       参数　　number 必需。要四舍五入的数字。
4 f: C( v: y+ m+ x; {  \　　num_digits 必需。位数，按此位数对 number 参数进行四舍五入。
9 H. m, \+ w$ l2 O" K应用：在处理社会保险数据时，只采用保留两位小数，最后合计数和社保局征收数额是不一致的。用养老保险举例：公式=ROUND(F7*8%,2)，其中，F7缴费基数，8%为缴费比例。

2 J5 A! E' B& _6 b/ X; |8 n7 E

2012年12月18日更新
& b; W0 q1 }4 H" X6 L  M% Q! k
; v) e2 \4 L% t- ]9 g

二十、插入对象
4 m& N  T  J& \      插入对象是插入选项中功能最多的，它可以插入系统中安装的许多文件类型。可以把多个文件整合到一个文件中，比较方便美观。

2012年12月20日更新

二十一、日期、星期
      利用函数显示当前日期和星期，如下：

今天是

2012年12月20日

星期四

日期表达为=TODAY()；星期表达为=TEXT(D2,"aaaa")，其中D2为日期单元格。

2013年1月4日更新
二十二、重新计算
9 L% U6 J9 z% a# j# i      当使用数据的自动筛选时，实现从数据中找出符合筛选条件的记录。系统默认情况下，有时在表的左下角并不显示“在*条记录中找到*个”。
      解决之道：工具---选项---重新计算，自动重算改为手动重算即可。注意：使用完，改回，否则，数据不自动计算。
8 m' ~9 [. X1 P2 b7 i7 g* y
2012年就这么过去了，还来不及太多的告别，有人欢喜有人忧，日子还得继续下去。2012，感谢有你---中人网及所有的“道友”们。我们未曾谋面，也不谈告别，我们并肩向前，不管有否“2012”。
+ w5 g, x4 Z% P2 `& B
/ C$ R! \) e, O$ Q2013年1月21日更新
! n$ T4 ?# q" V6 ?二十三、查找数据并自动填充
/ f. e5 ~4 B9 G  C3 K  P( U( x       

        如题，查找相应数值，填充到对应的单元格内。可以利用vlookup函数来表达。
* f2 j: Z( f7 O2 x=vlookup(查找值，查找区域，返回对应的列数，0），查找值：可以直接输入" " 中，也可以是单元格的值；查找区域：查找值在区域的第一列中查找的区域；返回对应的列数：查找值所对应区域中向后第几列的值；逻辑值：0/FALSE－查找值精确匹配  TRUE－模糊匹配；例如：VLOOKUP(SHEET1!A1,SHEET2!B:E,3,0)；含义：在区域SHEET2表中的B:E列的第一列（B列）中查找 SHEET1表中的A1的值，返回第三列（D列）对应的值，精确匹配。
7 X7 m& ^3 E5 }. P2013年1月21日更新
0 |# \) Q3 v2 e2 \二十四、快捷键
      先说二十三“查找数据并自动填充”的问题，查找区域要求数值是唯一，否则只返回数值是第一个数值。问：怎么查找数值中最大或最小呢？我暂想到一个方法：先把查找区域的数值进行降序或升序排列；欢迎大家能提供更为简洁的方法。

      再说快捷键的，如何实现在单元格内换行，使用组合键：ALT+回车键即可实现。另外，有一个EXCEL常用快捷键文档，共享一下。
9 y5 i" L, m/ R7 }6 G$ Q: F. `& P8 O2013年3月4日更新
3 x/ ]; [7 X1 v+ m& i8 Q. X; I二十五、if函数

       含义：执行真假值判断，根据逻辑计算的真假值，返回不同结果。可以使用函数 IF 对数值和公式进行条件检测。格式是：=if(条件1,返回值1,返回值2)  。多个嵌套的格式：=if(条件1,返回值1,if(条件2,返回值2,if(条件3,返回值3,返回值4)))  。这里先写3层嵌套，4、5、6、7层同理。
      函数除了遵守一般函数的通用规则以外，还有其特有的注意事项。
5 K  |( w" ]/ j2 C      1. 括号必须成对，上下对应。
      2. if函数有N个条件则有N+1个结果，即若结果只有3种情况的，那么条件只要2个就够了。
      3 .if函数最多允许出现8个返回值（结果），也就意味着，最多套用7个if 。
      4 .多个if嵌套时，尽量使用同一种逻辑运算符。即：统一使用大于号或者统一使用小于号，避免出现不必要的错误。
3 N/ \5 Y. ~8 I9 W6 P7 g      例子：如果单元格数值小于800，输入0；800-2000，输入2，0,2000以上输入4；

       公式=IF(C12<800,0,IF(C12<2000,2,IF(C12>2000,4)))。

9 |9 L6 S" _2 U

2013年4月26日更新

二十六、一键查找所有工作表数据

% ?0 G3 e2 ?* \

       查询快捷键为Ctrl+F，但默认查找范围为当前工作表，假如有很多工作表情况下，依次查询比较麻烦。只需改变一下参数即可，具体操作技技巧：1.按Ctrl+F弹出查找窗口，点击窗口右侧的选项。2.下拉查找范围列表，在工作表和工作簿两个选项中选择工作簿，点击查找全部即可。

0 C" c1 T' g$ [8 W" Q& W0 O2014年4月14日更新
* q( ~9 U/ E4 B, @        一晃一年过去了嚎，时间可真快呀嚎！期间有不少人问问题的，抱歉没有能回复，希望多多包涵。

二十七、重复数据

       对大量数据进行重复检查，可以利用COUNTIF函数，Countif函数是Microsoft Excel中对指定区域中符合指定条件的单元格计数的一个函数。该函数的语法规则如下：

                    countif（range，criteria）

       参数：range 要计算其中非空单元格数目的区域。

       参数：criteria 以数字、表达式或文本形式定义的条件。

       例子：对身份证号码进行重复检查，公式=IF(COUNTIF(B:B,B2&"*")>1,"重复","")，其中B列为身份证号码数据，&"*"一定加上这个通配符，因为系统默认为15位。

     

2014年5月31日更新

        先祝道友们”双节“快乐，吃好、玩好、睡好。最近一直在出差，期间在处理数据过程遇到一个问题，如下文。
4 w& e6 W4 g; Q9 O. }6 A- J0 [

二十八、查找数值，返回前列值

/ r& B5 p+ O% ^. [& Z      之前在”二十三“分享过Vlookup函数，可以查找值自动填充，但其局限性是只能查找区域的第一列，返回也只能之后数列值。经过查询，有两种方法可以实现返回前列值。
2 J( t" B2 g. Z" h      1、=VLOOKUP(H1,IF({1,0},B1:B3,A1:A3),2,)，即是将A列值和B列值重新排列，两列顺序颠倒了；
3 G# H1 Q" _! p; p

      2、=INDEX(A:A,MATCH(H1,B:B,0))，释义： 据区域，序号），返回数据区域中指定序号的数据； mathc（查找值，查找区域，0）返回查找区域中查找值的序号。  

2 k: _5 L/ V5 `) k. c2014年10月29日更新

      转眼又到年底，时间真是杀人的刀呀，刀刀催人老。
5 [/ h! |2 b2 \
$ O! P* ?+ z* W  M二十九、VALUE 函数

       本次介绍一个函数value,该函数可以将代表数字的文本字符串转换成数字，其语法结构为：VALUE(text)；

VALUE函数只有一个参数text，表示需要转换成数值格式的文本。text参数可以用双引号直接引用文本，也可以引用其他单元格中的文本。可以应用在数据引用时的统计计算。

2016年10月22日更新

         二年过去了，不知不觉已转行快三年了，隔行如隔山，行行都有一片天空，祝愿坚守和离开本行人前程似锦。

三十、如何应用excel2010中数据透视表之日期分组

      我们常常要对一些明细数据进行分期汇总。如果用分类汇总会比较慢且结果显示不直观或着日期数据是具体到天但需要按年、月汇总。

步骤：1、先做好数据透视表；

2、在结果数据区域，点击右键，在弹出菜单中选择“创建组合”。在分组设置窗口中，点击“月”“年”后确定，完成。注意：原始数据如直接插入数据透视表，无法对日期字段创建分组。原因就在于日期列数据格式并不是EXCEl所认识的日期格式。两种解决办法：一、插入辅助日期列，在其他单元格输入公式“=DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))”，复制填充完成，即可对数据进行日期格式转换，为了保证数据的准确性，我们将辅助列结果复制到A列日期列，保留值和格式，最后删除辅助列，这里用到了日期转换函数DATE(YEAR,MONTH,DAY)，对应的年、月、日，分别通过字符串提取函数LEFT(A2,4)、MID(A2,6,2)、RIGHT(A2,2)从A2单元格提取，A2为原始数据。二、单击日期所在列的列标   【数据】---【分列】---【完成】。

        
) K2 E% h+ ~" H' P" _8 ^


+ r1 ~9 c; W% a4 i- f" d) d    有时间继续更新......

' x* \9 M# M3 N