excel表格使用技巧

《excel表格使用技巧》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

Excel强大的功能，你知多少？5^1c'J2Y#D#A._0a6h/f7J!g(H:B.S8^MicrosoftExcel是微软公司的办公软件Microsoftoffice的组件之一，Excel中大量的公式、函数、工具可以选择应用，可以实现许多的功能，给使用者提供了极大的方便。很难想象如果没有它，我们现在工作会处于什么状态。:|/@$|&h/@"'i本人在工作过程中，收集了一些方法和技巧，现分享给大家，欢迎补充！'/l"N%O0?$h一、合同到期提醒9J1i2_:p9c;R8h!r9c假如A1单元格显示签订合同时间2005-11-05+Z6l"]1l6V'B4lB1单元格显示合同到期时间2007-10-31(L0C4V#i4{[2E:d当前时间由系统提取出来假设当前日期为2007-10-11d7H)o!A3C想让C1单元格显示:在合同即将到期30天内提醒:"合同即将到期",且每日提醒最好弹出提醒对话框,点击确定当日不再提醒,点击取消当日在一定时间内继续提醒;c-~#Y#P#t6@;O=IF(ISERROR(DATEDIF(TODAY(),B2,"D")),"已过期",IF(DATEDIF(TODAY(),B2,"D")>30,"未到期",IF(DATEDIF(TODAY(),B2,"D")=30,"今天到期","差"&DATEDIF(TODAY(),B2,"D")&"天到期")8?8D7m7B%Z'P+M差多少天到期的合同如何让反应的文字以红色字体体现出来,这样好统计)v#v%w:E'c6Q;q 选中C列,格式>条件格式>公式>=AND(DATEDIF(TODAY(),B2,"D")<=30,DATEDIF(TODAY(),B2,"D")>0),再点击格式>在单元格格式对话框中选图案>选红色.确定。*@!y0~-n2f5j#a;A+]二、新税法下的个税计算公式'`&r/w.H:V#z+_公式=ROUND(MAX((应发工资-3500)*{3,10,20,25,30,35,45}/100-{0,21,111,201,551,1101,2701}*5,0),2)；其中速扣数的公倍数5也可以乘以到里面。1@6d+k(g$m6Z:C三、年龄（岁数）和工龄的计算公式9b)D:m:z.S*N%z,A公式=DATEDIF(I4,TODAY(),"y")&"年"&DATEDIF(I4,TODAY(),"ym")&"月"&DATEDIF(I4,TODAY(),"md")&"日"；其中I4为数据源。'y+Y$K$R'F.Z4[-A四、平均年龄计算公式+M$l4X9~)|-W+J:S0c)H公式=AVERAGE(TODAY()-H3:H24)/365；其中H3:H24为计算范围；注：更新范围时，单回车键不行，需要ctrl+shift+回车键！,u5P/L%A;X0t.uF0h#z,D%|&f9r.O6d:E4o2011年11月22日更新5h(S&G,`*Y.S8`/Z2xe)M!h!J&Q;E-S/l五、数据查询并定位+r&Q8k#~1q/]%/j+n1、O35=（输入查找内容）；9V2C,P6E6E%j2x#S2、O36=IF(ISERROR(MATCH(O35,sheet1!C:C,0)),"",MATCH(O35,sheet1!C:C,0))。（意思为显示的位置,其中C为数据列）；8z8O-H6M;f%N(c4]3、O37=HYPERLINK("#sheet1！C"&O36,IF(ISNUMBER(O36),"点击显示","没有找到"))。（意思是查询结果）；;x9N9U.O4|-|%Y注：可以应用在从大量数据中查找所需数据，当然你也可以通过Excel自带的查询工具（ctrl+f为快捷键）。2]0c-x%U-k4Q"V{"v'L;b)t2011年11月26日更新B/z*G$J5R8I)k2O1d8`/_.])S3W*Q6y六、如何在Excel中插入Flash时钟的? 9X*?&?Q动态时钟不是用函式运算、自动化功能制作出来的,这只是简单的插入Flash文挡的功能而已，而且只要你有Flash文件，任何人都可以轻松自行制作。+j2j!@4M"i"|0w制作方法：)d8H*y"E5M!]!S%V第1步首先打开一个空白Excel文件，点击“视图”→然后点选【控件工具箱】，→点击“其他控件”。9F"v0F9u(|8Q第2步然后再点击[ShockwaveFlashObject]项目，表示要插入Flash物件。-G5s[7j/[/a4G+e第3步接下来，鼠标会变成一个小十字，此时可以在Excel编辑区中画一个大小适中的方框，这个方框就是用来显示Flash时钟的内容的。;c0c6c7[3r;Q(h8g#@:a'uC第4步画好方框后，接着点击【属性】，准备设置属性。;S5i:E;T%M4W%X*C(D第5步出现「属性」对话框后，将DeviceFont设置成False；将Eebedmovie设置成True；将Enabled设置成True；将Locked设置成True；将Loop设置成True；将Menu设置成False；并在“Movie”右侧填入时钟的地址与名称。1`-M'g%K;bs7f第6步退出设计模式，全部完成。"w7J*P7d%r%G2i.S9r9^+v.P2q2011年11月29日更新#B*`"G)m'O4P3y*K*m*Q-Z;5o$m七、与身份证相关公式6{%h$_(g&x$c7P8G!h*i1、身份证验证公式=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为身份证数据源；8J1B/C6`-`$D(^5k5dr(~![-k4j,P4S2011年12月2日更新4c'R#Y;B+l;B7`2g.P8^+D,p+]9^!a(A4、提取出生日期;H7Q-C0G#F0F!T4W公式=TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2-1),8),"#-##-##")；:k"Ui/v:}*H3v;]2`5、提取年龄（整岁）/@+|*uK2@7H(?公式=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)；-D&}3L2v1y!`$n:-v2011年12月9日更新,`+M2~8v/|B5o$s0^-Bn$Z3E#b.A'n%M八、选中单元格的行与列变颜色9O:A8j+U/X3|"Y*X8e:]4U1、条件格式里用公式里填，应用于=$1:$65536(n,z:h*{5M 公式=(ROW()=CELL("row"))+(COLUMN()=CELL("col"))1oy6Q-c!mvba编辑器里填（用ALT+F11调出）：5y5j7u7G#v!K1d@5OPrivateSubWorksheet_SelectionChange(ByValTargetAsRange)!p7y.k2P-})_2x7r6F4rCalculate9h0W.p4[0b%m&i8xEndSub7k*~4Z.J8C%Q3T"K颜色在格式里自己调；9O*D#h9P:y:W7V'H2、或者直接在vba编辑器里填：%D&/e'k2W8L(J$f4IPrivateSubWorksheet_SelectionChange(ByValTargetAsExcel.Range)&6b%h%_2s2MOnErrorResumeNext-S4S*H-Z4?3U9K4P/aCells.FormatConditions.Delete1l.z*k0Z4Y(W;t-CiColor=Int(50*Rnd()+2)/w:k2C3d5F)wWithTarget.EntireRow.FormatConditions8c5I9T3j-Y.Delete8c/C%f!~/M+A.AddxlExpression,,"TRUE"){:q#c$]#u+d(Fl.Item(1).Interior.ColorIndex=iColor/E*W9_/W(R!i;w$c(mEndWith2|"e'w'U*O,N1V8a0H"]%X(|WithTarget.EntireColumn.FormatConditions#n:T6X.M"t1Q4Y(~.Delete1O*d/~0t8{6[6^,W)M.AddxlExpression,,"TRUE"$Q)u,p'm1f5h#{.Item(1).Interior.ColorIndex=iColor#k&m&h8x$@0y:V7xEndWith1t$W(E*c!p5_EndSub/k)N6B2K#U'|9s6|;Z1J.u(@4q#[0Q2011年12月26日更新25L%u(?%P&[.[8H(@-l/u!w1_,G/i5o1@)V在应用“第八”的第二条后，发现文件就不能再编辑，慎用之。6[2`5r'j%[$g;l)g6?,H+J2kd(2011年最后一天更新4A.h,A6g'J$[*V-g4x7c)Z5D(Y"y:Z4T九、重复数据去除2c*E/H4X7r"P)V从大量数据中，去除重复记录，保证数据不重复，可以利用excel自带工具"高级筛选“，假设数据区域为B列，打开高级筛选，选择列表区域，勾选”选择不重复的记录“。如下图：5Q-x/^1I4A'j5Q!]gs-q+O,_2B1B"^,H,N-P#x'`(c'q1{#Q/i!O6aZp&B6q1_2L%S!I2012年第一次更新（1月2日）0B7M)j2r9Z!g:J"@8'w3H;h.?!e-h 十、个税倒推公式(配合第二条使用）7zG/q/B/T'k0[*i公式=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为税后数据。(d1C&k)r-?-V#o!u"B7K:h%}4^+p&T2012年1月14日更新|9uD/v-C&`/|0l6K/Q/8y;~8p)K$r)b1f十一、用身份证号提取户籍地:t;b:K1_'h3W2S,`公式=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是身份证数据。2q#A/G)R&x!g:u-y*H6G:e9]/l8Y(f(t:K'g6h;y)s2012年2月24日更新'm8C,Z*~;^-x%W"m.?9U7n;e)r+|"Y,M7U十二、去除单元格中的空格字符/^7Q9`!o#p :["~/d0r1X4W(S选择整列，点击菜单“数据—分列”，第一步选“分隔符号”，第二步选择“空格”，第三步选择“文本”，完成。3v.f$?5q5_/g&U2012年3月2日更新1Ve&N:e#[十三、此工作簿包含其他数据源的链接，是否需要更新？E5h-c/Q(v&d有的时候，当我们打开XLS工作表文件时，会看到这样的对话框提示（如下图）：时间长了或者数据公式多的时候，不知道这个需不需要更新，而且每次都这样提示，觉得挺烦的。解决方法：1、编辑”→“链接”→“查看链接源”，之后您就可以对链接源进行编辑了，是想更改链接还是删除链接以断开数据源，都可以操作的。　　2、查找工作表中已经引用链接的单元格。按CTRL+F键，输入“*.XLS”，然后查找，在结果中全选，就可以看到工作表中哪些单元格引用了链接，再进行相关操作。;K"n/[.E!t"})Z"O;g;g/F-v;N2012年3月10日更新97_-i1D(w/p$P0U[$z;~(z'X'b%A 十四、自定义下拉菜单1、选中要做下拉列表的单元格。2、在菜单中选择：数据－有效性－［设置］－（允许）：序列；（来源）：输入内容，用,（逗号）分隔。如下图：*L(|)a*M,x0u%l$~*_0L.r!w.X3h-E.U;J6U2012年3月28日更新+Q9Y5}:H:z*M+q;j2S!h$E'P十五、相关附件1、Office应用100例.rar2、Excel：人力资源管理篇.rar+n#I)E;A$[%l(~8V"([/T$Q这两个附件都超过了10M，有需要的加我QQ。%~.`*e;}2U'X$|1~-|3],b$A2Z#i:S2012年4月1日更新3X1U*Y0m'n"n-K2m/q十六、出生日期按照月日排序-Q1g,~:W+e$t'Y*T有些单位会给员工预定生日蛋糕，这就涉及到统计当月过生日员工的姓名和人数，但出生日期不能直接排序或筛选出所需的数据。现提供两种方法：;R#O8d0h,N3b*f1、利用“分列”功能。首先，在“出生日期” 列后增加两空列；其次，选中出生日期列，数据---分列---分隔符号，下一步，勾选其他输入“.”(日期连接符可能是“-”或者“/”)，下一步，完成。这三列单元格数字格式选择常规，而非日期。最后，进行排序即可。"m5x'},O1g2、利用函数。首先，在“出生日期”列后增加两空列；其次，在第一空列输入=MONTH(A1)，第二空列输入=DAY(A1)，下拉填充公式。注：第一空列是出生月份，第二空列是出生日，其中，A列为出生日期；最后，进行排序即可。!R!B6H4F2p*{!m8{.`!R9i'k&GM:D#C3a*Q4c"hU2012年6月26日更新这段时间事情比较多，一直没有更新，见谅！'o#]3D7J:i"W!e;Z十七、SUMIF的使用9@.q&[!?6P1~(k(m3W.pSUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中。例：同一种产品，在当月销售次数很多时，需要统计当月累加销售量，可以使用。具体操作如下：0|4_"d4D9|p"Wj1、公式=SUMIF('2011年'!A:A,C2,'11年'!B:B)；2}4D2T,d/o+P7T9K+?2、2011‘！为工作表名称；A列为产品名称；B列为产品销售数量；C2为统计产品名称；![;mB5]5T7t-M4y#j*l1?3、可以扩展到其他产品和月份统计；-e;{*~7k2G3L-K4、关键点是C列产品包含所有A列产品；4@%n/Z,t*l:]*K#F1R#S"x:N#d3N0t(b;D5f2012年7月13日更新 8u3w/n'r3h4{*J十八、回车键定位右单元格/J!`(o)a!{-W快速输入大量数据，点击回车键在默认下是向下移动，输入下一列数据时需要重新定位。1R7G6n!n.f解决方法：工具、选项、编辑、按Enter键后移动，选向右。"`-Z&[*M7S!_0|3P7Q9P/[,[&R7e2012年7月17日更新9S+M%v-i2V"QB1B84H*Y7Z'R8e&e2I十九、Round函数-r$T,?6h$X#s#C#6V9L　Round函数，返回按指定位数进行四舍五入的数值。4|3R"D9V0Z:pC4L　　ROUND(number,num_digits)参数　　number必需。要四舍五入的数字。%B1f!|(R-m,{3h(N-g2G'e　　num_digits必需。位数，按此位数对number参数进行四舍五入。&:J;B/_3]%M'{.I应用：在处理社会保险数据时，只采用保留两位小数，最后合计数和社保局征收数额是不一致的。用养老保险举例：公式=ROUND(F7*8%,2)，其中，F7缴费基数，8%为缴费比例。5o0L1M/i4n't0q#y;{g+O(B'z#m!`1v(d2012年12月18日更新*i3p4B"F4n!e#D-{.n3N.C"Ha-D%Y)m二十、插入对象4f#d"};8];K1G/m8u插入对象是插入选项中功能最多的，它可以插入系统中安装的许多文件类型。可以把多个文件整合到一个文件中，比较方便美观。&E!J+g5O&p/d"Dd&P%U,A6m!S,U;t +I2E&M!*H2{&Y,`;^2012年12月20日更新)`7z6{)j-p'[0b8m0])S!E&C0I_7l4g,p二十一、日期、星期.@4V:I'?2W.y6w(w利用函数显示当前日期和星期，如下：今天是2012年12月20日星期四日期表达为=TODAY()；星期表达为=TEXT(D2,"aaaa")，其中D2为日期单元格。:?&R*t"s'L3W8}#_"Q#K&p8z7M;d9H0C7K(U6r2013年1月4日更新7N:o3}(w%](C'r3B"M二十二、重新计算0f,J1A3c1U)p+t3X5z;x当使用数据的自动筛选时，实现从数据中找出符合筛选条件的记录。系统默认情况下，有时在表的左下角并不显示“在*条记录中找到*个”。7|(_,`2A+k1v1}(S+G,@解决之道：工具---选项---重新计算，自动重算改为手动重算即可。注意：使用完，改回，否则，数据不自动计算。3N)](d._-H(M*D7q4f%v'y2012年就这么过去了，还来不及太多的告别，有人欢喜有人忧，日子还得继续下去。2012，感谢有你---中人网及所有的“道友”们。我们未曾谋面，也不谈告别，我们并肩向前，不管有否“2012”。'f4C4W1O;d&w,P8]&T8u3m%{/G(B!%},e2X*}2013年1月21日更新-[6B-h9G#X,{,C.k;K二十三、查找数据并自动填充(?7|;{*q;|"Y/A%T,l$D如题，查找相应数值，填充到对应的单元格内。可以利用vlookup函数来表达。)Y83r4U8n(T4i=vlookup(查找值，查找区域，返回对应的列数，0）； (r/}4[8k4]-l-n查找值：可以直接输入""中，也可以是单元格的值$J*J(B9bs5x&K'`2@查找区域：查找值在区域的第一列中查找的区域0l)R(^$^.)?$t返回对应的列数：查找值所对应区域中向后第几列的值:r:P&J9A.^:U"A逻辑值：0/FALSE－查找值精确匹配TRUE－模糊匹配.U&b2`*E1i._%P'v3m&a例如：VLOOKUP(SHEET1!A1,SHEET2!B:E,3,0)'d.|!Y9p*u"_,@含义：在区域SHEET2表中的B:E列的第一列（B列）中查找SHEET1表中的A1的值，返回第三列（D列）对应的值，精确匹配。/q:a/|+o#W7]'@;o+V5y*b#F#d8L5n3Y2013年1月21日更新+o*Y$k(x$R!v*j)L.C-c二十四、快捷键!J#L1T']+r先说二十三“查找数据并自动填充”的问题，查找区域要求数值是唯一，否则只返回数值是第一个数值。问：怎么查找数值中最大或最小呢？我暂想到一个方法：先把查找区域的数值进行降序或升序排列；欢迎大家能提供更为简洁的方法。5J&C"kQd%X再说快捷键的，如何实现在单元格内换行，使用组合键：ALT+回车键即可实现。另外，有一个EXCEL常用快捷键文档，共享一下。-v;o)I#T*]!W0w"L/q/[#I5](P9D8^2013年3月4日更新:B2m3ud3G,L#R二十五、if函数9^4v0~,Z2d&Y,|含义：执行真假值判断，根据逻辑计算的真假值，返回不同结果。可以使用函数IF对数值和公式进行条件检测。格式是：=if(条件1,返回值1,返回值2)。多个嵌套的格式：=if(条件1,返回值1,if(条件2,返回值2,if(条件3,返回值3,返回值4)))。这里先写3层嵌套，4、5、6、7层同理。:j0OC!Q'V.R0@-g4_/p.w8U函数除了遵守一般函数的通用规则以外，还有其特有的注意事项。 4e1W:B8l)I3b-H1.括号必须成对，上下对应。;w6U/{/{%T6t8N/g4y0W/X2.if函数有N个条件则有N+1个结果，即若结果只有3种情况的，那么条件只要2个就够了。9p!|/F;J(e&x(Dq$o0c:h3.if函数最多允许出现8个返回值（结果），也就意味着，最多套用7个if。"n"r,K{;U5po%S%[4.多个if嵌套时，尽量使用同一种逻辑运算符。即：统一使用大于号或者统一使用小于号，避免出现不必要的错误。)e.`2Q0m)P+W:l例子：如果单元格数值小于800，输入0；800-2000，输入2，0,2000以上输入4；)v%X$I&C1l"z公式=IF(C12<800,0,IF(C12<2000,2,IF(C12>2000,4)))。%L#Dv1A'j1O#g6c2[4a2V)l9W0j.P2T;{2013年4月26日更新.n:n3g'N7u1};8F+?0L二十六、一键查找所有工作表数据G6V(B3U4u*}&G&P查询快捷键为Ctrl+F，但默认查找范围为当前工作表，假如有很多工作表情况下，依次查询比较麻烦。只需改变一下参数即可，具体操作技技巧：1.按Ctrl+F弹出查找窗口，点击窗口右侧的选项。2.下拉查找范围列表，在工作表和工作簿两个选项中选择工作簿，点击查找全部即可。