Excel高级教程(珍藏).docx

《Excel高级教程(珍藏).docx》由会员分享，可在线阅读，更多相关《Excel高级教程(珍藏).docx（24页珍藏版）》请在课桌文档上搜索。

1、样的表头，在斑成果表中用“自动筛选”按班级号筛出各个班级的学生，并将其复制到相应的各个班级工作表即可。小提示：以上公式中的“8是要划分的班级个数,可以依据班级数的变更而修改,若分成n个班级，则总公式为“=1FMOD132*nnn-MOD13n+lIFMOD132*n=OIMOD132*n3、打印名单分好班级后,要把各班娘的学生名单打印出来，一个班最好用一张标准纸，可用分栏打印制决班级人数多的问遨.而Excel没有特地的分栏打印功能，我就利用Excel的公式制作出分栏打印效果，具体操作如卜,：先将某一班级名单按自己要求设置好,如页边距、表头、行高、字体等。在打印预览中,推断出每页的数据行数x（所

2、谓数据行，指表头除外的记录行）,回到一般视图下，友的右边空列中.从第一数据行起先埴充自然数序列1、2、3假设此列在K列,第一数据行为第3行.则在1.3单元格中填入公式“=mod（int（K3-Dx）,yN其中X为每页的数据总行数.y为分程的栏数，就分班而言，两栏就够用了），并向下埴充整个表.则该列出现了从O到yl的数，即给秘行计算出/该行所在的栏号，红制衣头到新的工作表或新建的工作簿中.在班级1：作表中也用“自动筛选”功能分别筛选出第。栏、第1栏第y-栏，并分别复制到新工件表中.再对各新工作表进行相应的设汽，如页边距、表头、行高、字体等，留意不要逐个工作去进行设附，按住“Clrr镀，用鼠标单击

3、面口中须要设时相同内容的各个不同工作表标签，在其中一个工作表中修改设置，另外的工作表中就能自动修改,全部工作表都设置好后，分栏打卬的各班名单就制作好了,川打印机打印出来便大功告成.此方法都保留了原表，有利于修改，如要变更班级数、打印栏数、斑页行数，只需变更n、x、y的值。另外,此方法用的是“自动加选功能，也可以用“高侬筛选”来完成，大家可以试一试，在Excel单元格中设置“斜线”效果我格中出现斜线是常常的事（如图I）,以前遇到这种状况要么把及格导入到WPSoffiCC或WOrd中处理，要么就是打印好了之后再用笔加上去,经过一乔投索,最终发觉了在EXCH中设置斜线的方法。图I-三姓名图21、实现

4、在单元格中分行的效果：我们首先在单元格中输入“性别，这时候按回车键的话，光标就会转到其他单元格中去了,所以必需按住“AM过的同时接回车键，光标转到下一行.接着输入“姓名然后再在一性别”前加入若干空格犍即可达到如图2的效果.5.排序：因为我们是无序录入的，录入完后，为了有序排列，就要利用排序功能了。选中去格的随意单元格，执行菜单栏上“工具，选项“，在弹出的对话框中的击“自定义序列”选项K,在左边框中选择新序列，单击“导入”后,选中表格中文件类型的所在区域,再按一次一导入“（如图2）,确定后我们会发觉原先无序的内容一下子变得彳r序了.排序之后,你会发觉序号因此被打乱了.没关系.在你排序之前，在序号

5、列之后捅入一空白列，任你怎么排序，序号都不会变更，不过，记得排序之用删除这列。将表格保存到新建的文件夹里并放在全部文档之前。好了，全部的整理工作都完成了，以终就是把文档刻录到光盘里保存了用Excel函数快速型!入部门名称这学期,我们单位正在申报省部级重点技工学校和市级文明单位,各部门都在主动打算资料.在打灯过程中,常常要输入各种表格进行数据的处理.大信的表格中都须要填入教职员工所在部门的名称,这对于一个有一百多名老师的学校，是一件特别繁域的工作.在工作中，我发觉了一个“偷懒的方法，现在把它写出来，和大家一起共享！下面我就以EXCCI2000中的一个“学校职工状况表”为例.来告知大家如何利用Ex

6、cel的数快速填入部门名称-如图I所示，要求城入相应部门代号后的所在部门名称.图11 .选取须要填入所在部门的范RilF4:向18.2 .单击格式工具栏上的【插入函数按地，接著在“拈贴函数”对话框中的“函数分类”栏选择“查找与引用”,“函数名”选择“1.OOKUP，单击确定按钮（如图2。XX九各班”与“九”之间的关联编辑栏里是“-九！XXz星力二星期二星期星期昭期五,第1节0000I0，血”00000第3节00000J第4节00000第5节00000r第6节00000第7节00000IO星期一星期二星期三星期四星期五星期六0w-00_wxifS三B是“=二！于是.我就想到了运用“昔换”功能.具

7、体做法是：首先设置好“一各班”与“一”之间的关联.然后宓制表“一各班”为“一各班(2)“、“一各班”弁分别将其更名为“二各班”、“三各班”接着启用“独换”操作,在“:各班”中将“一！”侨换为“二！”，在“三各班”中将“一！”防换为三！”以此类推，即可将全部“班课程表”及其关联快速制作完成。特殊说明，在“背换”操作中，键入的“！”号要用英文输入状态卜的号。奇妙处理Excel工作表表头当新建或打开一个已行的EXCd工作表时.都不行避开地会遇到表格表头的创建或修改,并且在衣格编辑过程中和衣格输出时也涉及表头的处理问超，我们可以利用一些技巧来更好地处理Excel发头”I.让表头自动埴耳当前月份和日期比

8、如表格标遨为“某公M6月份电费统计表”(如图).其中“6”为进行电费统计的当月月份,其次行为制表日期.假如该总位电费每月公布次，那么每月打开此表修改编辑时,都要对标题中的月份和制表Fl期进行修改，万一只改动了表内数据，而遗忘以上两个时间的改动，会出现去头时间与表内数据的冲突,我们实行一个一劳永逸的措施，随时打开此工作衣,让表头自动埴写当前月份和日期.这要借助Excel三个函数来实现,在标题单元格输入=试脸中学“&MONTH(TODAYO)&也”月份电费统计表T函数“MONTH(ToDAYO)”表示当前月份,在其次行制衣时间单元格输入“-TODAY。函数TODAY。”衣示当前”期，就出现图中结果

9、，2 .在“常规”选项卡中，选中“RICI引用方式”选巩3 .定义名称,将“引用位置“由“=GET.CE1.1.（6Sheell!RC-1D”即可。这里的RCbI含义是假如在当前的元格的同行前一列单元格中有公式结果.则在当前的元格中得到公式内容,即在含公式结果单元格的同行后一列单元格显示公式内容：假如构RC卜Il改为Re1,则在公式结果的同行前一列总元格显示公式内容.4 .假如“引刖位附.“中含有,RC-,则在含公式结果单元格的同行后一列单元格中输入JFormUIaUfRCSUIl”即可得到公式：假如“引用位置”中含有“Rqi，则在含公式结果单元格的同行前一列单元格中输入“=FormUIaof

10、KCSUItT卬可知到公式.提示：假如切要在含公式结果单元格的同行后数第2列中显示公式内容，则她要把“引用位匿”中的“RC-1”改为“RC-2已知公式，显示结果假设C列为输入的没有等号公式（假设CI为Fl+Bl）而相邻的D列是你须要存放公式计算结果的地方（即Dl显示AI和Bl单元格相加的结果.I.选中Dl,然后打开“插入噪单选标“名称”吩咐中的“定义”子吩附，出现“定义名称”对话框。2 .在“在当前工作表中的名称”输入栏中输入定义的名称“RcsuItotTomukr.在下方的Fl用位Ir编辑栏中输入*=EVA1.UATE（Shectl!C1,单击确认按钮退出.3 .在Dl中输入“-Rcsuho

11、fFomuH,然后选中按住右下角的址充柄向下拉动地充即可.提示：EVA1.UNrE是EeXCeI4.0版的宏表函数，EXCel2000和EXCel2002中还支持，但只可用于名称定义中。4 .填充后要按（F9J进行揖算.假如C列的公式有改动.也须要刚好按止列进行重算.巧施妙计，就能让公式和结果在EXCd中和平共处了，你也试武吧。避开Excel的误差微软的Excel软件在财务应用领域运刖得特别广泛、被很多总位用来处理财务数犯.笔者就是用EXCd来为单位做工资表的，不过帝者在运用过程中发觉，当用EXcd进行困难算术运算时，其计算出来的结果有时会与电子计算器的结果有些微小的差别,特殊是在进行多项除法

12、运算而留有余数的状况下，亢术误差”很简洁产生，究其缘由，就毡EXCel在除不尽的状况下，不会自动对数值进行四舍五人的操作,这在要求严格的财务报表上当然是不允许的，避开这种“误差”的途径有两种:1 .利用Round函数对中间值四舍五入EXCd中RoUnd函数的格式为：Round（ft,i.保留的小数位数）.它的作用就是对指定数值进行四舍五入的操作并保留指定小数位数，比如ROUM8.8888.2）的逆思就是对8.8888的分位阴亍四台五入、结果就是8.89,当然为工作便利，此南数括号内的数值也可以是单元格地址或别的算术公式,每个中间值经过这样处理后番到的最终运算结果就和计尊器算出来的一样了.2 .

13、正确设置EXCCI选项来避开“误差”上述函数方法虽然便利，不过遇上大型EXCeI衣格时，假如好个单元格都要设置这个函数，工作埴未免太巨大，这时我们就可以进入EXCd的I工具-4菜单选项-4重新计徵），在“工作海选项”处勾选,以显示值为准”,上述EXCCr误差”也可有效避开.而我认状况下这个小选项是没有被勾选的.把这三个字复制剪贴板,然后在斑，个填行村名的那,行的B列点,下肽标右键选扭“粘贴”在那里埴上一个“第I组，第三步：用鼠标点击选中A2“东山村”单元格.然后把取标单元格右下角（此时抿标变为单“十”字形，按住规标往下拖动，拖过的地方会被自动填上“东山村”字样.用同样的方法可以把其它村名和粗别

14、用我标“一拖了之”.填组别时你别担忧EXCCl会把组别全部填为“第1组”，只要你别把“笫I组”写成“第,组”，Excel会自动把它识别为序列进行处理。所以拖动“第I俎”时,填写的结果为“笫2组3第3组”填完这两个字段后，其它的数据可以接荷在EXCel中填写,也可等以后在数抵体软件中填写，反正劳动强度差不多.第四步：保存文件,假如你须要建立的是Access数据库，那么别管它，就用EXCeI默认的“Xs”格式保存下来。假如你须要建立的是FoXPg数据库，那么请以DbaSC4（.dbf）格式保存文件，第五步：假如须要的是AaeSS数据库，那么你还必需新建一个ACCeSS数据库，在“新建表”的对话框里

15、,你选择“导入表”然后在导入对话框中选择你刚刚存盘的“.Xis”文件.（什么？你找不到？！这个对话框默认的文件类型是MicrosoftAccess,H要你改为MicrosoftExcel就能找到了）,选和好导入文件后，你只要用意把个“第一行包含列标曲的复选框芯托辛要匚缝你不须要ID字段.你可以在ACCCSS向你举荐主关健字时拒绝选择“不要主关键字其余的你都可视而不见，只管按“下一步”宜至完成，导入完成后你可以打数据派进行运用或惚改.假如你须要的是FOXPm数据陈，那么更简沽，可以干脆用FoXPm打开上一步你存盘的”.db文件，依据须要进行一些诸如字段宽度、字段数据类型设置就可以运用了。说到这里

16、.你可能会说“呀，太筒沾了，我也会“，那么好了，我写这篇文章的目的就是要大家尽可能不再做那些很麻烦的事.“该偷懒时就偷懒”咻.Excel中三表“嵌套”成一表问区的提出：期末考试完后,学校领导要我出一份简报,以反映全校的教学状况（简报的式样见表一.我己钱在EXCCI中存储有：全校各班各科任课老师名单（见表二）、全校各班各科平均成果（见表三）、全校各班各科及格率（见表四）等基本数据，可以说只要把这后三张表的数据粽合到一起也就完成了简报的制作.全校有50K个班,考试科目又多.把上述数据再给S工作量之大是可想而知的.好在这三种发格的式样基本相同，于是我先采纳逐级逐科“笑制T粘贴”的方法来工作。但是这要

17、不断1也选、不断地或制、不断地在腐口间切换，费时费劲且易出错，如后三种表格能向FIaSh中的透亮图层一样相互嵌套就好了“，在这种理念的舞动卜，我大胆探究,最终找到了解抉Excel表格嵌套”的方法.解决的方法：怎样才能实现EXCCI中表格的嵌套”呢？方法其实很简洁,下向我们一起来看却吧！1. EXCd中新这一名为-简报”的文件，井按式样绘制表一。JXRMM(f)A)KWXA(Ptrfl)gqpf3!产Rq劣awrejfww:空”nrn斗门眩卡五号“1“Ift一-1.1.1.七IlAmlh3.q9j三o当我在短时间内将简报清样送刎领导手中时,他们的惊讶程度是可想而知的.扎实我想说:“这没什么,精彩

18、的还在以后呢！呵呵。单元格格式EID数字厂对界川字体I边根I图案文本对齐水平对齐QD自动软行幻缉J、字体堰充合井里元格始I保护I图I（5）多张工作表中输入相同的内容几个工作表中同一位置填入同一数据时,可以选中一张工作表,然后按住Cm键,内单击窗口左下角的SbeethSheeI2来干脆选择须要输入相同内容的多个工作去，接着在其中的随意一个工作表中输入这炖相同的数据，此时这些数据点自动出现在选中的其它工作表之中,输入完毕之后,再次按下犍盘上的Ctrl键.然后给川取标左雄单击所选择的多个工作表,耨除这些工作表的联系，否则在一张衣垠中怆入的数据会接着出现在选中的其它工作去内，12)=1.男二女T假如除

19、以2以后的余数是1.JE么B2中兀格显示为“男”，否1显示为“女”.【可华确认后，即可在B2单元格显示正确的性别信息，接下来就是选中填充柄干腌拖戋.如图2所示，现在这份报表无论是提取信息或是核对，都便利多了！req31CttA1W.1.A1。翼a10I*GIK.JdJJk_J1.AZ，.性别，身份证号码2052!0C,rz21】052”“，10”他卜2421“】2。6452：s20296年1。I-ER-TqI出生年月用炎【图1输入身份证号码2UumAneUaoreeuc.2)*2ttuar*2SpeVJ33j,19q4Q3,AXtl1111A姓名,BtlS1.-M*)身份还号码出生年月职称2机

20、历半32O52172C8C702433孔70105M6Q美T朱Q份32296SO10832C52U9n0M-F3”11,3205296我3031。眼_I.1图2提取个人信息*2jh1XzGs,3/用填充柄自动填充工作衰在ExCeI2000匚作衣中，假如须要在一行或，列单元格中填入安依次排列的项目序号，用Excel2(K)0的自动填充功能可以常助您快速怆入这些数据序列.而且还不简洁出描.你不会不知道什么是限制柄吧？选中一个表格,其右下角有一个实心的小方块，它就是限制柄，当鼠标放上去的时候便会变成实心的”+”。其方法：在第一单元格那输入其实数据，在下一个单元格那输入其次个数据，选定这两个中元格，将

21、光标指向单元格右卜方的埴充柄，沿着要埴充的方向拖动填充柄托过的单元格中会自动的按EXCd20(X)内部规定的序列进行填充,如下图二,在B3中输入“第1名”，然后拖动限制柄往下拉，松开鼠标时所拉过的区域就自动填充了“第2名、第3名”。在拖动限制柄的过程中我们也可以在鼠标的右边小说明中方到所填充的内容.假如运用反标4键拖动单元格境充柄，也可以实现自动填充.其方法是：在某单元格内输入数据.按住依标右犍.沿着要填充的方向拖动填充柄,此时就会弹出包含下列各项的快捷菜单(如图三)：狂制单元格、以序列方式填充、以格式填充、以位域充、以天数填充、以工作日填充、以年填充、等差序列、等比序列，.。此时，您可以依据

22、须要进行选择城充方式.明显，这种方法更有敏捷性。赛制单元格以序列方式组充(三)以格宓B充Q)以值熄充&)以天数组充)以工作日增元富)以月填充出)以年坤充Q1.)序列(E)Hfi=另外，双击埴充柄自动埴充工作表也是一个不错的方法自动填充数字序列和文本序列也可以不拖动填充柄通过单元格区域,而采纳双击填充柄的方法实现:对于数字序列的填克，要选定包含初始值单元格在内的两个通元格，然后双击填充柄，而刻于文本序列的填充，只嵩选定初始的单元格，仍旧后双击埴充柄，例如：要想在B2:Bu中输入数字序列“I、2、3.10,只须在B2中输入“I”,选定B2:BII,双Jl“充柄即可.假如须要在“C2:C7”中输入文

23、本序列”第1名、第2名、.第6名”,只须要在C2中输入“第I名”,选定C2单元格,双击填充柄即可.双击填充柄自动填充是一种更为快速的方法，要地充的序列的单元格区域越大，如闻过一屏的大区域，越能体现他的优越性.要留意的是.自动产生的序列数由A2向下直到遇到空白单元格为止的胞元格个数确定.例如，A9为空白单元格,那么此法仅在B2:B8中产生序列“1、2、37”.同样，若B5为空白单元格，那么仅在C2:C4中产生序列“第I名、第2名、笫3名“，Exed小技巧在EXed中快速互换数据在ExCd中，相邻两列或两行的数据假如要进行互换，般方法是用“复制”、粘贴”的方法来诳行，其实还有更快的方法。假定要把A

24、l和Bl的数据互换，先选取Al,接着将米标移到选取范围的边缘，光标的形态变成白色脩头状,这时先按住键盘上的Shm不放，再按住副标的左键,拖动鼠标到Bl右边框线上,这时在右边柢线上会出现一条虚线,先松开鼠标左犍后.再松开Shin键.数据便已经互换了.相邻行之间的数据互换如法炮制即可.入SUM函数有快法在EXeel工作去中，SUM（求和）函数是大家运用最为常见的函数之一。要输入该函数时，大家通常的做法是单击常用工具栏上的自动求和”（）按钮.其实,按住AIt搂.同时根入f”.同样可以输入SUM函数.而旦这免除你移动胡胡的麻烦，节约了时间，能够提高编辑效率”巧用IF的数核对录入的敷据在EXSI中输入大

25、量数据时，输入人员常因数据段大，看得头晕眼花，常常输错,结果还需人工校对几遍.但仍M不能保障完全正确.其实巧用Excel中的IF函数便能达到比人工校对效率更高.在蚪入的数据（如Cl单元格后面另增Dl与El唯元格，Dl单元格用作核对,在El单元格中输入=旗Cl=DIJ正确J错误）即可.那么我们先将C列中数据输完后，再在D列中杀输一邀,如E列中提示“错误”,则表示该行数据中C列或D列中有一项是格误的，Excel中动态公式的应用在EXCCl的某个工作表中,假如随时查看某个动态区域的统计结果，可以设计动态公式：在某个单元格中输入函数式：=SUMUNDlREeT（BIriNDlRECT（CI）1.假如须

26、要统计D2至H5I区域中数值的和（也可以进行其他计算），只要在Bl和CI单元格中分别输入D2和HSl,上述函数式所在的单元格中就显示出了相应的和.在Excel中播入拼者在我们运用EXCeI时，常常会遇到特别孤僻的字，但加上拼音注样就能使人行得更明白，具体的方法是：选中想要加入拼音注铎的表格，打开Excel的菜单栏中的“格式”菜单，选中,拼音信息/设置“项.在弹出的对话框中的“设世和,字体选项中.分别选择拼音的对齐方式和字体.最终选择“拼音信恩”子菜单中的“编组”，即可以在弟元格中输入拼音了.要近看拼音，可通过拼信息”子桀单中的“显示和除藏”项显示注样的拼音。在Excel中快逋入籍殊符号报表中常

27、用的“二。二”中的“O”.很多人都是将就地打印为二OO二、“二OO二”或二零零二”.其实这都是不正确的f是数字城.“OO”是英文字母”0“的大写，“零零”是中文数字的大写，而“，是中文数字的小写，它们之间都不匹配，羟过细诙摸索，能者发觉被忽视的中文输入法中的“软键盘”可以轻松地输入常用符号。打开中文输入法，先输入“二”，在中文输入法状态下,用取标右犍单击输入法的“软椀盘”标记,这里选“单位符号”,这样输入法的软键盘就被打开且定位到中文数字上，按迸盘上的Q键或用以标点由“软谜盘上的Q键,中文小写的“。”便被录入进来。在Excel中快速,入大写数字IV智能ABC黝入法”状态下.按住Shin健再按下

28、T徽不含引外松开Shift键.然后输入任一数字硬，就得到相对应的大写数字了.另外，财会人员常用的单位词,如“个、拾、佰、仟、万”等,同样可以借助于i键加上单位词相对应的汉语拼音第一个字母便利地得到（具体可森看“智能ABC输入法”的“帮助”菜单）.让Excel启动时自动打开工作，用户可自定义默认工作簿，以便每次启动EXcd时，均可自动打开此工作簿.方法是：自定义个新工作得，然后将它保存在X1.Start文件夹下.在C:Documeni$andSeUing$用户名ApplionionDaHlXMicrosoMExcel或C:ProgramFiIesKMicrosoftOfficeOfficelO下

29、就IiJ以找到X1.Starl文件夹。具体路径还要视你的系统安装盘符而定。彩色Excel表格显示打印两不谀假如将EXCel工作犬中的数据设置为彩色格式，而打印机为黑臼打印机，就么工作表中的彩色区域打印出来的效果可能会不志向。在不更改工作去颜色设置的状况下，可以诳行如下设置：1 .在“文件”菜单中,单击“页面设置”吩咐.2 .单击“工作表”选项，然后选择“打印”框下的“单色打印”项.假如运用的是彩色打印机，选择该选项可削减打印所需时间。3,单击“确定”按钮.现在,你的EXCCIi:作表在屏幕上存起来仍是赏心悦目,打印出的黑臼稿也能称心如意.感觉不错吧！巧用“选坏性粘贴”使行列互换假如AI-AK）单元格中有不同的内容，现在须要将这些内容分别排列在Al、B1、CI、D1.J1第元格中,即将原来在同一列中的内容重新排列在一行中.莫非要一个