Excel使用技巧

, , , , , ,*,Excel,的使用技巧,快速填入相同的内容,快速复制上一单元格,求字符串中某字符出现的次数,如何在,EXCEL,中设置单元格编辑权限（保护部分单元格）,Excel,中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色,EXCEL,中如何控制每列数据的长度并避免重复录入,去掉默认的表格线,增加工作簿的页数,相对地址、绝对地址、混合地址,数据的输入,输入相同数据的两种情况,数据的行列对调,跨表操作数据,两次选定单元格,两列数据巧合并,用快捷键设置单元格文本,自动调整为合适的列宽,绘制斜线表头,Excel,做成绩表时自动实现同分同名次,按,Shift,键 把,Excel,单元格区域转换为图片,制作漂亮的工资条,图表编辑操作,1.,快速填入相同内容,选中多个单元格后，输入字符，然后按,Ctrl+Enter,组合键，即可在选中的每个单元格中填入上述相同的字符。,2.,快速复制上一单元格,选中下面的单元格，按,Ctrl+ ,（,为西文的单引号）组合键，即可将上一单元格的内容快速复制下来。,3.,求字符串中某字符出现的次数,例如：求,A1,单元格中字符“,a”,出现的次数：,=LEN(A1)-LEN(SUBSTITUTE(A1,“a” ,“ ”),LEN(text),：返回文本字符串的个数,SUBSTITUTE,（,text,old_text,new_text,instance_num,）,:,将字符串中的部分字符串以新字符串替换。,4.,如何在,EXCEL,中设置单元格编辑权限（保护部分单元格）,先选定所有单元格，点“格式”“单元格”“保护”，取消“锁定”前面的“”。,再选定你要保护的单元格，点“格式”“单元格”“保护”，在“锁定”前面打上“”。,3.,点“工具”“保护”“保护工作表”，输入两次密码，点两次“确定”即可。,5. Excel,中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色,比如：,A11,时，,C1,显示红色,0A11,时，,C1,显示绿色,A11,点“格式” ,“,字体”“颜色”，点击红色后点“确定”。,2.,条件,2,设为：公式,=AND,（,A10,A11,）,点“格式” “字体”“颜色”，点击绿色后点“确定”。,3.,条件,3,设为：公式,=A11”,点“格式”一“字体”一“颜色”，选定红色后点两次“确定”。,COUNTIF(range,criteria),：计算某个区域中满足给定条件的单元,格数目。,这样设定好后，输入数据如果长度不对会有提示，如果数据重复字体将会变成红色。,7.,去掉默认的表格线,单击“工具”菜单中的“选项”，再单击对话框中的“视图”，找到“网格线”，使之失效（将左边的“,”,去掉）。,8.,增加工作簿的页数,单击“插入”菜单下的“工作表”，即可在底部看到一个新名称,的工作表。一个工作簿最多可以有,255,张工作表。,9.,相对地址、绝对地址、混合地址,相对地址的形式是地址直接用列号行号组成。,绝对地址的形式为：,$A$1,$B$1,。,有些公式引用单元格是用混合型地址。,10.,数据的输入,日期和时间的输入,当前系统日期的输入：,ctrl +,；,当前系统时间的输入：,ctrl + shift +,；,12,小时制输入时间，在时间数字后空一格，并键入,字母,a,或,p,，例如，,8,：,00 a,改变日期格式在“格式,-,单元格”中修改,Word,里：,Alt+Shift+D,：当前日期,Alt+Shift+T,：当前时间,数值的输入,负数输入：直接输入负号和数字，或用圆括号输入。,分数输入：先输入,0,和一个空格，再输入分数。直接输入分数,则认为是日期。,输入据用自动设置小数点或尾随零的数字。在“工具一选项一,编辑”选项卡中设置自动小数点,公式的输入,公式以等号“,=”,开头，,Excel,有很多可利用的公式。如果想在工作簿中看到的是公式，而不是具体的值，则在“工具一选项一视图”选项卡中，选中“窗口选项”下的“公式”复选框。注意，这样在打印的时候打印的也是公式。,11.,输入相同数据的两种情况,同时在多个单元格中输入相同的数据。,方法：选择要输入数据的单元格，如果不连续可以使用,Ctrl,键,进行选择，然后输入数据，再按下,Ctrl+Enter,键就可以了。,同时在多张工作表中输入或编辑相同的数据选中多个工作表后,后在相应单元格中输入数据，选择多个工作表的方法也是使用,Ctrl,或,Shift,键。,12.,数据的行列对调,选取要对调的数据范围，复制。,在目的单元格上单击鼠标右键，选择“选择性粘贴”，在对话,框中选择“转置”，确定。,13.,跨表操作数据,设有名称为,sheet1,、,sheet2,和,sheet3,的三张工作表，现要用,sheet1,的,D8,单元格的内容乘以,40%,，再加上,sheet2,的,B8,单元格内容乘以,60%,作为,sheet3,的,A8,单元格的内容。则应该在,sheet3,的,A8,单元格输入以下算式：,=sheet1,！,D8*40%+sheet2,！,B8*60%,。,14.,两次选定单元格,有时，我们需要在某个单元格内连续输入多个测试值，以查看引用此单元格的其他单元格的效果。但每次输入一个值后按,Enter,键，活动单元格默认下移一个单元格，非常不便。单击鼠标选定单元格，然后按住,Ctrl,键再次单击鼠标选定此单元格（此时，单元格周围将出现实线框）。,15.,两列数据巧合并,在,EXCEL,表格中，如果有两列数据需要合并起来，如果是数字的话，一般情况下可以使用四则运算的方法进行计算；但是如果有一列是文字，另一列是数字（或者文字），那么可以先在这两列后面的一个空列中单击，并输入,=,号，然后输入第一列的第一个单元格的相对引用名，输入,&,号，然后再输入第二个列单元格的相对引用名，最后按一下回车键即可。然后，鼠标移动到这个单元格的拖动柄上，向下拖动知道需要合并的数据最后一行。,16.,用快捷键设置单元格文本,在,Excel,中，熟练使用快捷键可以帮助我们设置单元格文,本。在选择好要设置格式的单元格后，如果要设置为粗体，可,以按,Ctrl + B,；如果要设置为斜体，可以按,Ctrl + I,；如果要设,置为下划线，可以按,Ctrl + U,。,17.,自动调整为合适的列宽,在,Excel,中，如果一个单元格中文字内容过长，就无法为系统全部显示出来，手工调整又很麻烦。这时可以将鼠标移动到该列的列标与下一列的交界处，当鼠标指针变为两个方面的箭头时，双击，这时系统就会把列宽设置为该列中最宽的那个单元格的宽度。,18.,绘制斜线表头,在,Excel,经常需要绘制斜线表头，简单的两分式的表头可以先把表格绘制好，并在需要绘制表头的单元格中单击，然后单击格式菜单下的单元格命令项，在单元格格式对话框中点开边框选项卡，然后根据绘制的需要单击外边框和下面的边框线。,19. Excel,做成绩表时自动实现同分同名次,在用,Excel,统计成绩时，我们一般会根据成绩高低进行排序，然后按序列自动填充出名次。这种方法得出的名次与总分没有关联，即使成绩相同，也会填充出不同的名次。如果数据较少，我们可以采用手动的方法将成绩相同的人员改成相同的名次，但数据较多时就很麻烦了。,经过实践，以下三种方法可以自动实现同分同名次的操作。假设有,1000,名考生，字段位置如下图所示。,成绩相同，名次不同,1.,使用排位函数,RANK,在,L2,单元格中输入函数“,=RANK(K2,K$2:K$1001,0)”,，回车后,L2,单元格中会出现名次“,1”,，然后选定,L2,，拖动其填充柄向下填充即可自动实现。,该函数的功能是返回一个数字,(,上述函数中的,K2,，此处采用相对应用，填充时随行的变化而变化,),在数字列表,(,上述函数中的,K$2:K$1001,，此处采用绝对引用，填充时不发生变化,),中的排位。,数字的排位是其大小与列表中其他值的比值。该函数在使用时，即使总分没有排序，它也可以直接求出所对应总分的名次,;,如果总分已排过序，则数字的排位就是它当前的位置。上述函数中最后的“,0”,指明排位的方式，当其为,0,或省略时，,Excel,对数字的排位是按照降序排列的。,如果不为零，,Excel,对数字的排位则是按照升序排列的。该函数对重复数的排位相同，而下一名次则将前面的重复数计算在内。,2.,使用计数函数,COUNTIF,在,L2,单元格中输入函数,“,=COUNTIF(K$2:K$1001,“”&K2)+1”,，,然后向下自动填充即可实现该操作。,该函数的功能是计算区域中满足给定条件的单元格个数。上述函数中采用绝对引用指定计算区域,K$2:K$1001,，名次所对应的单元格,K2,采用相对引用。即求出在,K2:K1001,区域中大于名次所对应总分的单元格的个数，然后加上,1,就可以得出该单元格中数值的名次。该操作不管有几个重复的，大于它的个数却是固定的，所以重复数据的名次也就是相同的了。同,RANK,函数一样，该函数不管是否已经按总分进行排序均可进行上述操作。,3.,采用,IF,语句加,COUNT,函数,该操作与上述两种方法不同，必须先将总分按照降序排列。然后将第一名的名次“,1”,输入，再在,L3,单元格中输入函数“,=IF(K3=K2,L2,COUNT($K$2:K3)”,，确定后向下自动填充也可实现同分同名次的操作。,COUNT,函数的功能是返回指定区域中数字单元格的个数。因为,K,列全是数字单元格，所以该总分处在第几位上名次就是几。上述语句的意思就是如果当前总分和上一个总分相同，则输入上一个总分的名次，否则执行,COUNT,语句，统计个数。,20.,按,Shift,键 把,Excel,单元格区域转换为图片,Shift,键在,Excel,里有这样的妙处：在按下,Shift,键的同时点击,“,编辑,”,菜单，原来的复制和粘贴选项就会变成,“,复制图片,”,和,“,粘贴图片,”,。利用这一功能，我们就能把选定的单元格区域方便地转换为图片。,具体操作方法如下：,首先选中需要复制成图片的单元格区域，然后按住,Shift,键，依次选择,“,编辑复制图片,”,命令，接着弹出,“,复制图片,”,窗口，选择,“,图片,”,单选项后点击,“,确定,”,按钮。这时选定的表格区域就已经被复制成图片了。,再到需要使用这张图片的地方选择“粘贴”命令即可,(,或者按,Shift,键再选择“编辑粘贴图片”命令,),，比如将其在,Word,中粘贴。,另外，如果选择“如打印效果”单选项，那么被转换为图片的数据区域是按照打印效果来处理的，比如设置有粗边框，那么粘贴出来的图片上就会有粗边框。,21.,制作漂亮的工资条,新建一,Excel,文件，在,sheet1,中存放工资表的原始数据，假设有,O,列。第一行是工资项目，从第二行开始是每个人的工资,。,在,sheet2,中我们来设置工资条。根据实际情况，工资条由三行构成，一行对应工资项目，一行对应一个人的工资数据，然后是一个空行用来方便切割。这样三行构成一个工资条。工资项目处在行号除以,3,余数为,1,的行上；空行处在行号能整除,3,的行上。以上两行不难设置，关键是工资数据行，牵扯到,sheet1,与,sheet2,中数据的对应，经分析不难看出“,sheet1,中的数据行,=INT(sheet2,中的数据行,+4)/3)”,。,这样我们在,sheet2,的,A1,单元格中输入公式“,=IF(MOD(ROW(),3)=0,”“,IF(MOD(ROW(),3)=1,Sheet1!A$1,INDEX(Sheet1!$A:$N,INT(ROW()+4)/3),COLUMN()”,。确认后选择,A1,单元格，把鼠标放在,A1,单元格的右下角，鼠标变成“,+”,时，向右拖动鼠标自动填充至,O,列，这样工资条中的第一行就出来了。选定,A1,：,O1,，把鼠标放在,O1,单元格的右下角，鼠标再次变成“,+”,时，向下拖动鼠标自动填充到数据的最后一行，工资条就全部制作完成了。,IF(logical_test,value_if_ture,value_if_false),：判断一个条件是否满足，如果满,足返回一个值，如果不满足则返回另一个值。,MOD(number,divisor):,返回两数相除的余数。,INDEX(arraym,row_num,column_num):,在给定的单元格区域中，返回特定行,列交叉处单元格的值或引用。,Array,：单元格区域或数组常量；,row_num,：数,组或引用中要返回值的行序号。如果忽略，则必须有,column_num,参数；,column_num,：数组或引用中要返回值的列序号。如果忽略，则必须有,row_num,参数。,INT(number),：将数值向下取整数为最接近的整数。,该公式运用,IF,函数，对,MOD,函数所取的引用行号与,3,的余数进行判断。如果余数为,0,，则产生一个空行；如果余数为,1,，则固定取,sheet1,中第一行的内容；否则运用,INDEX,函数和,INT,函数来取,Sheet1,对应行上的数。,最后来设置一下格式，选定,A1,：,O2,设上表格线，空行不设。然后选定,A1,：,O3,，拖动,N3,的填充柄向下自动填充，这样有数据的有表格线，没有数据的没有表格线。最后调整一下页边距，千万别把一个工资条打在两页上。怎么样，还满意吧,?,22.,图表编辑操作,1.,创建图表,2.,编辑图表,3.,图表的保存,1.,创建图表,创建图表的操作步骤：,(1),在工作表中选择数据区域。按住“,Ctrl”,键同时拖动鼠标，,可以选择数据区域中不连续的单元格区域。,(2),单击常用工具栏的图表向导按钮，屏幕出现图表向导第,步对话框。,(3),选择柱形图中的“簇状柱形图”，单击“下一步”按钮，屏,幕出现图表向导的第步 。,(4),该对话框有“数据区域”和“系列”两个选项卡。“数据区域”选项,卡用于确定图表所包含的数据区域。在数据区域栏中显示目前包,含在图表中的所有数据单元格的范围。“系列”选项卡用于修改数,据系列的名称、数值和分类轴标志。,(5),选择系列产生在“列”，单击“下一步”按钮，弹出图向导的第,步对话框，输入内容。,该对话框有,6,个选项卡,(6),单击“下一步”按钮，屏幕出现图表向导的第步对话框。,该对话框是用来设置图表的位置的。即选择是作为新的工,作表插入，还是作为对象嵌入。,(7),选择好位置后，单击“完成”按钮，创建的图表如图所示。,2.,编辑图表,图表工具栏的使用方法：选择“视图”菜单中级联“工具,栏”的“图表”选项 。,按列,按行,数据表,图例,图表类型,图表对象格式,图表对象,a.,图表的移动与缩放,b.,图表对象的修改,3.,图表的保存,保存工作薄，则工作表中创建的图表即保存在相应工作,薄中。,本次讲座到此结束，,谢谢观看！,