Excel使用技巧（精选11篇）
Excel使用技巧 第1篇
主要功能：计算符合指定条件的单元格区域内的数值和。
使用格式：SUMIF(Range,Criteria[,Sum_Range])。
参数说明：Range代表条件判断的单元格区域;Criteria为指定条件表达式;Sum_Range代表需要计算的数值所在的单元格区域。在“条件数据区”查找满足“条件”的单元格，计算满足条件的单元格对应于“求和数据区”中数据的累加和。如果“求和数据区”省略，统计“条件数据区”满足条件的单元格中数据的累加和。
应用举例：如表1所示，如果在C15单元格中输入公式：=SUMIF(C3:C14,“>2000”)，把求和数据区省略，则确认后即可求出实发工资>2000元的人的工资和。但如果在C15单元格中输入公式：=SUMIF(B3:B14，“生产”，C3:C14)。则确认后即可求出生产部门的实发工资和。
特别提醒：SUMIF函数中的参数，条件数据区和条件必须是针对同一数据序列。使用此函数进行计算时，应先考虑条件是什么，找到条件了，条件数据区域就迎刃而解了。
2 COUNTIF函数
主要功能：统计某个单元格区域中符合指定条件的单元格数目。
使用格式：COUNTIF(Range,Criteria)。
应用举例：如表2所示，在C12单元格中计算工程师的人均工资，输入公式：=SUMIF(B3:B11，“工程师”，F3:F11)/COUNTIF(B3:B11，“工程师”)，利用SUMIF函数计算出所有工程师工资的总和，利用COUNTIF函数计算出该厂职工中工程师的人数，两者相除即得到所求结果。
参数说明：Range代表要统计的单元格区域;Criteria表示指定的条件表达式。
特别提醒：允许引用的单元格区域中有空白单元格出现。
3 RANK函数
主要功能：返回某一数值在一列数值中的相对于其他数值的排位。
使用格式：RANK(Number,ref,order)
参数说明：Number代表需要排序的数值;ref代表排序数值所处的单元格区域;order代表排序方式参数(如果为“0”或忽略，则按降序排名;如果为非“0”值，则按升序排名)。
应用举例：如表2所示，在G3单元格中输入公式：=RANK(F3，$F$3:$F$11,0)，确认后即可按降序得出职工编号为BM010123职工的工资总额在全厂中的排名结果。
特别提醒：在上述公式中，让Number参数采取了相对引用形式，而让ref参数采取了绝对引用形式(增加了一个“$”符号)，因为所有职工的排名都是相对于同一个单元格区域，这样设置后，选中G3单元格，将鼠标移至该单元格右下角，成细十字线状时(通常称之为“填充柄”)，按住左键向下拖拉，即可将上述公式快速复制到G列下面的单元格中，完成其他职工的排名统计。
4 IF函数
主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容。
使用格式：=IF(Logical_test,Value_if_true,Value_if_false)。
参数说明：Logical代表逻辑判断表达式;Value_if_true表示当判断条件为逻辑“真(TRUE)”时的显示内容;Value_if_false表示当判断条件为逻辑“假(FALSE)”时的显示内容。
应用举例：如表3所示，在H列等级1中将总成绩达270分以上的为“优秀”，210-270分的为“良好”，180-210分的为“及格”，180以下为“不及格”。在H3单元格中输入公式：=IF(F3>270，“优秀”，IF(F3>=210，“良好”，IF(F3>=180，“及格”，“不及格”)))，确认后即可得出所求结果。在I列等级2中将总成绩达240分以上并且平均分达90分以上的为“优秀”，总分大于180或者平均分大于80分的为“良好”，其他的为“及格”。在I3单元格中输入公式：=IF(AND(F3>240,G3>90)，“优秀”，IF(OR(F3>180,G3>80),“良好”，“及格”))。
特别提醒：IF函数可以嵌套使用，但最多可嵌套7层，超过7层就不管用了，在IF函数中加入and,or可以达到不同列单元格的“并且”，“或”条件的判断。
5 结束语
Excel是目前使用比较广泛的数据管理分析软件，通过以上案例，更加能体会到Excel函数的魅力，利用其提供的丰富函数，不需要深厚的计算机语言基础，不需要掌握很深的数学计算方法，不需要了解具体的求解技术细节，而是只要选择正确的函数，填写适当的参数，即可完成复杂的求解过程，处理日常工作事务更加快捷、高效。
摘要：Excel是目前使用比较广泛的数据管理分析软件,Excel中的函数不仅涉及面广,而且种类很多,功能也非常强大,利用其提供的丰富函数,可完成复杂的求解过程,使处理日常工作事务更加快捷、高效。
关键词：函数,公式,条件
参考文献
[1]何新权.全国计算机等级考试一级B教程.北京:高等教育出版社,2009.
Excel小技巧提高职场工作效率 第2篇
技巧一：实现跨行求和
有时我们会遇到间隔行列来求和的情况，例如在1年的时间中，如何找出所有的周一下的数据并求和呢？ 我们知道一个星期有7天的时间，那么在表格中，可以按照这个规律来找出处在同一星期的所有单元格。
首先确定起点的位置，例如A3单元格，3对7取余为3，在英文输入状态下输入公式“=SUM（（MOD（ROW（A3：A100），7）=3）*A3：A100）”，同时按下“Ctrl+Shift+Enter”组合键转换为数组公式，这个公式表示每7行取一个数据，然后将这些数据进行累加，“ROW（A3：A100）”表示从A3到A100的单元格区域，“MOD（）”的意思是模除，就是求余数，这里求{3，……，100}分别与7相除所得的余数；“MOD（ROW（A3：A100），7）=3”是判断行数是否正确，如果余数是3，结果为真（true），否则为假。如果某行结果为真，就参与A3：A100的求和运算，否则不参与运算。
技巧二：自动更改数据的数量级
在Excel中输入同类数据的时候，我们如果手动输入小数点（或添加0），不仅容易输错，也浪费了时间。那么，有没有一种方法可以让Excel自动输入小数点，我们只需要输入数字就可以呢？
解决的办法很简单，打开“Excel选项”对话框，在左侧导航栏选择“高级”选项卡，在右侧窗格勾选“自动插入小数点”复选框（如图1），在这里设置相应的位数，输入正数表示缩小，负数表示放大，例如输入“-2”，表示在输入的数字后面增加2个0，如果输入“+2”，小数点往前移动2位。
技巧三：分别复制上一个单元格内容
可能你经常需要复制前面刚刚输入的内容，直接复制、粘贴是最为简单的方法，但如果每次复制的内容并不相同，除了强行复制之外，有没有简单一些的方法呢？
操作很简单（如图2），选中A2单元格，按下“Ctrl+D”或“Ctrl+'”组合键即可（这里的“'”是单引号）。需要提醒的是，这个技巧只能在输入文字的单元格下面实现，而且不能隔行；如果需要向右复制，只要按下“Ctrl+R”组合键就可以实现向右填充，同样不能隔列。
技巧四：只提取中文或数字
某些时候，我们需要从混合区域提取出数字，而且不允许破坏原有的表格结构，除了手工操作之外，利用“定位条件”是一个非常不错的方法：
例如只需要保留除了数字之外的内容，首先选中相应的单元格区域，依次选择“编辑→查找和选择”，打开“定位条件”对话框，依次选择“常量→数字”，确认之后会自动定位指定区域中所有的数字（如图3），按下Delete键删除，剩余的就是我们需要保留的非数字内容了。如果只需要保留数字，只要依次选择“常量→文本”，删除所有文本字符即可。
Excel使用技巧 第3篇
例如:开封市的固定电话号码原来是7位,后来位数升级为8位,如果在电话簿中每个电话号码前面增加数字“2”,那是工作量很大,非常麻烦的一件事情。下面介绍一种省力省时的快速修改方法。
1)如图1所示,A列为对应办公室名称,B列为当前电话号码,C列为新的电话号码;
2)在C3单元格输入:="2"&B3,回车,就看到“2315876”对应的新号码是“22315876”;
3)此时把鼠标放到C3单元格右下角小方点的位置上,按着鼠标左键往下拖动直到结束再松开鼠标,所有的电话号码都自动更改完毕(如图2)。
2、用Excel做表格时,通常一个表头下边有几百行甚至几千行,打印以后除了第一页就没有表头不方便查看,怎样设置每页打出来都有表头呢?
要使打印出来的每一页都有表头,需要在EXCEL中设置顶端标题行,依次单击“页面布局”、“页面设置”,在弹出的对话框中单击“工作表”,在“打印区域”中单击右侧的红色箭头,用鼠标拖拉选择需要打印的区域;在“顶端标题行”选项中单击右侧红色箭头,用鼠标拖拉选择每页需要作为标题的一行(如果是左列为标题列,则在“左端标题列”中设置),这样打印出来的每页表格上方或者左侧都会有和第一页相同的表头。
3、如何为表格添加斜线?
通常我们做表格的时候需要使用到斜线,斜线表格的绘制在WORD中很容易实现,而EXCEL工作表中并没有自带斜线功能。但是我们可以使用绘图工具手动绘制斜线:单击插入-形状-最近使用的形状,单击“直线”,在需要绘制斜线的地方拖动鼠标绘制一条合适直线,调整方向和长度即可。在斜线的上方和下方分别使用文本框添加文字,方便文字的排版和移动,添加的文本框周围有边框,单击文本框的边框,然后单击格式-形状轮廓-无轮廓,就可以取消文本框的边框设置。
4、巧用“自动更正”功能实现快速录入。
在使用excel的过程中我发现"自动更正"功能除了自动修改输入错误,还有一个神奇的妙用,就是实现高频次长短语的快速录入。在我们录入的过程中经常会遇到有些比较长的短语或者字母数字汉子混合的一些短语频繁出现,那么反复录入这些短语的确是比较麻烦和头疼的事情,比如在对新乡医学院现代教育技术中心做介绍的文章中会多次出现“新乡医学院现代教育技术中心”等字样,我们事先把这个比较长的名词定义为一个短语“新医”,然后在“自动更正”中设置输入“新医”时自动替换成“新乡医学院现代教育技术中心”。具体方法是单击“文件”、“选项”、“校对”、“自动更正选项”,在“替换”框中录入“新医”,在“为”框中录入“新乡医学院现代教育技术中心”,然后单击“添加”、“确定”即可。
5、改变打印比例。
通常,Excel中的数据有很多列,当列数超过一页宽度的时候,在打印数据时,系统默认先打印工作表中左边列第一页的宽度,然后顺序打印右边的列。但经常我们需要把所有内容打印在一页内显示出来,那么可以采用以下方法:
单击菜单栏中的“视图”、“分页预览”,工作表中出现的蓝色的线就是分页符的位置,用鼠标拖动分页符可以改变分页符的位置,把垂直分页符直接拖到最右一列的右边,则所有的列就完全显示在一页当中。
参考文献
[1]王世平.Excel在学校教学中的应用技巧二例[J].时代教育,2008(3):149.
Excel使用技巧 第4篇
例如：在单位的人事资料，在excel中输入后，由于上级要求在原来的职称证书的号码全部再加两位，即要在每个人的证书号码前再添上两位数13，如果一个一个改的话实在太麻烦了，那么我们可以用下面的办法，省时又省力：
1)假设证书号在A列，在A列后点击鼠标右键，插入一列，为B列;
2)在B2单元格写入：=13A2后回车;
3)看到结果为13xxxxxxxxxxxxx了吗?鼠标放到B2位置，单元格的下方不是有一个小方点吗，按着鼠标左键往下拖动直到结束，当你放开鼠标左键时就全部都改好了。若是在原证书号后面加13则在B2单元格中写入：=A213后回车。
02、如何设置文件下拉窗口的最下面的最近运行的文件名个数?
打开工具，选选项，再选常规，在最近使用的文件清单下面的文件个数输入框中改变文件数目即可。若不在菜单中显示最近使用的文件名，则将最近使用的文件清单前的复选框去掉即可。
03、在EXCEL中输入如1-1、1-2之类的格式后它即变成1月1日，1月2日等日期形式，怎么办?
这是由于EXCEL自动识别为日期格式所造成，你只要点击主菜单的格式菜单，选单元格，再在数字菜单标签下把该单元格的格式设成文本格式就行了。
04、在EXCEL中如何使它象WORD一样的自动定时保存文件?
点击工具菜单自动保存项，设置自动保存文件夹的间隔时间。如果在工具菜单下没有自动保存菜单项，那么执行工具菜单下加载宏...选上自动保存，确定。然后进行设置即可。
05、用Excel做多页的表格时，怎样像Word的表格那样做一个标题，即每页的第一行(或几行)是一样的。但是不是用页眉来完成?
在EXCEL的文件菜单-页面设置-工作表-打印标题;可进行顶端或左端标题设置，通过按下折叠对话框按钮后，用鼠标划定范围即可，
这样Excel就会自动在各页上加上你划定的部分作为表头。
06、在Excel中如何设置加权平均?
加权平均在财务核算和统计工作中经常用到，并不是一项很复杂的计算，关键是要理解加权平均值其实就是总量值(如金额)除以总数量得出的单位平均值，而不是简单的将各个单位值(如单价)平均后得到的那个单位值。在Excel中可设置公式解决(其实就是一个除法算式)，分母是各个量值之和，分子是相应的各个数量之和，它的结果就是这些量值的加权平均值。
07、如果在一个Excel文件中含有多个工作表，如何将多个工作表一次设置成同样的页眉和页脚?如何才能一次打印多个工作表?
把鼠标移到工作表的名称处(若你没有特别设置的话，Excel自动设置的名称是sheet1、sheet2、sheet3.......)，然后点右键，在弹出的菜单中选择选择全部工作表的菜单项，这时你的所有操作都是针对全部工作表了，不管是设置页眉和页脚还是打印你工作表。
08、EXCEL中有序号一栏，由于对表格进行调整，序号全乱了,可要是手动一个一个改序号实在太慢太麻烦，用什么方法可以快速解决?
如果序号是不应随着表格其他内容的调整而发生变化的话，那么在制作EXCEL表格时就应将序号这一字段与其他字段分开，如在总分与排名之间空开一列，为了不影响显示美观，可将这一空的列字段设为隐藏，这样在调整表格(数据清单)的内容时就不会影响序号了。
09、用Excel做成的工资表，只有第一个人有工资条的条头(如编号、姓名、岗位工资.......)，想输出成工资条的形式。怎么做?
弹指如飞Excel高速输入的技巧 第5篇
在Excel工作表的单元格中，可以使用两种最基本的数据格式：常数和公式。常数是指文字、数字、日期和时间等数据，还可以包括逻辑值和错误值，每种数据都有它特定的格式和输入方法，为了使用户对输入数据有一个明确的认识，有必要来介绍一下在Excel中输入各种类型数据的方法和技巧。
一、输入文本
Excel单元格中的文本包括任何中西文文字或字母，以及数字、空格和非数字字符的组合，每个单元格中最多可容纳32000个字符数。虽然在Excel中输入文本和在其它应用程序中没有什么本质区别，但是还是有一些差异。比如我们在Word、PowerPoint的表格中，当在单元格中输入文本后，按回车键表示一个段落的结束，光标会自动移到本单元格中下一段落的开头；在Excel的单元格中输入文本时，按一下回车键却表示结束当前单元格的输入，光标会自动移到当前单元格的下一个单元格，出现这种情况时，如果你是想在单元格中分行，则必须在单元格中输入硬回车，即按住AIt键的同时按回车键。
二、输入分数
几乎在所有的文档中，分数格式通常用一道斜杠来分界分子与分母，其格式为“分子／分母”。在Excel中日期的输入方法也是用斜杠来区分年月日的，比如在单元格中输入“1/2”，按回车键则显示“1月2日”。为了避免将输入的分数与日期混淆，我们在单元格中输入分数时，要在分数前输入“0”(零)以示区别，并且在“0”和分子之间要有一个空格隔开，比如我们在输入1/2时，则应该输入“01/2”。如果在单元格中输入“81/2”，则在单元格中显示“8 1/2”，而在编辑栏中显示“8.5”。
三、输入负数
在单元格中输入负数时，可在负数前输入“一”作标识，也可将数字置在()括号内来标识，比如在单元格中输入“(88)”，按一下回车键，则会自动显示为“-88”。
四、输入小数
在输入小数时，用户可以向平常一样使用小数点，还可以利用逗号分隔千位、百万位等。当输入带有逗号的数字时，在编辑栏并不显示出来，而只在单元格中显示。当你需要输入大量带有固定小数位的数字，或带有固定位数的以“0”字符串结尾的数字时，可以采用下面的方法：选择“工具”、“选项”命令，打开“选项”对话框，单击“编辑”标签，选中“自动设置小数点”复选框，并在“位数”微调框中输入或选择要显示在小数点右面的位数，如果要在输入比较大的数字后自动添零，可指定一个负数值作为要添加的零的个数，比如要在单元格中输入“88”后自动添加3个零，变成“88 000”，就在“位数”微调框中输入“－3”，相反，如果要在输入“88”后自动添加3位小数，变成“0.088”，则要在“位数”微调框中输入“3”。另外，在完成输入带有小数位或结尾零字符串的数字后，应清除对“自动设置小数点”复选框的选定，以免影响后边的输入；如果只是要暂时取消在“自动设置小数点”中设置的选项，可以在输入数据时自带小数点。
五、输入货币值
Excel几乎支持所有的货币值，如人民币(￥)、英镑(￡)等。欧元出台以后，Excel2000完全支持显示、输入和打印欧元货币符号。用户可以很方便地在单元格中输入各种货币值，Excel会自动套用货币格式，在单元格中显示出来。如果要输入人民币符号，可以按住AIt键，然后在数字小键盘上按“0165”即可。
六、输入日期
Excel是将日期和时间视为数字处理的，它能够识别出大部分用普通表示方法输入的日期和时间格式。用户可以用多种格式来输入一个日期，可以用斜杠“/”或者“－”来分隔日期中的年、月、日部分。比如要输入“2001年12月1日”，可以在单元格中输入“2001/12/1”或者“2001-12-1”。如果要在单元格中插入当前日期，可以按键盘上的Ctrl+；组合键。
七、输入时间
Excel的使用技巧及常见问题 第6篇
一、快捷技巧
1、ALT+回车键, 实现单元格内换行
在工作表中进行操作时, 很多时候需要在同一单元格中输入两行或者更多行的数据, 而直接按下回车键, 会自动跳到下一行, 如何在同一单元格内实现换行呢?按下ALT+回车键即可。
2、SHIFT+鼠标拖动, 实现多行之间或多列之间数据的互换
在操作EXCEL数据时, 有时需要将行与行、列与列之间的数据互换, 比如将表1中的“数量”与“金额”列互换位置。
通常的作法是:
步骤1:选中其中一列 (如B2:B9) , 移动到其他位置 (如D2:D9) ;
步骤2:选中C2:C9, 移动到B2:B9;
步骤3:将D2:D9的数据移到C2:C9。
快捷方法:选中B2:B9, 按下SHIFT键的同时拖动鼠标到C2:C9的右侧, 等出现“I”形状且显示D2:D9时松开鼠标, 即可实现两列的互换。
关于行与行之间的互换同理可得。
二、不同工作表之间数据的操作
1、不同工作表之间数据的复制
在同一工作表之间进行数据的复制是非常简单的, 当需要将工作表上的数据复制到同一工作簿的另一工作表中, 有时会出错。如果原数据是由键盘输入的原始值, 复制过去不会出错, 反之, 如果原数据是由公式或函数计算得出的值, 复制过去往往会出错。
原因是:多数人习惯用“复制”后再“粘贴”的方法实现简单复制, 而当要复制的数据源为“由公式或函数计算得出的”值时, 在不改变Excel默认状态时, 粘贴的为单元格的原始输入数据 (即公式) , 而当这个公式被复制到另一工作表时, 公式所引用的单元格的数据发生变化, 就会出错。
解决方法:在复制数据之后, 执行编辑选择性粘贴值, 即可正确复制单元格最终显示的值。
2、不同工作表之间数据的计算
在同一工作表之间进行数据的计算是非常容易的, 而对不同工作表中的数据进行计算时, 就会有些不得以应手了。
如下图如示, 表1、2、3在同一工作簿中, 应计算表1中“数量”和“金额”的值 (等于表2和表3中对应值的和) 。
常规计算方法是:首先计算出单元格B3的值后, 再复制公式到B4:B9。这样计算的结果会出错。
解决方法:
步骤1:同时选中工作表106_1中的B3:B9, 输入“=”号;
步骤2:鼠标单击工作表106_2, 同时选中B3:B9后, 输入“+”号;
步骤3:鼠标单击工作表106_3, 同时选中B3:B9后, 按下“CTRL+ALT+ENTER”键, 即可完成操作。
三、多个函数的嵌套使用
学生的成绩管理历来是不熟悉数据处理的老师们的一大难题, 成绩管理不仅要求老师计算分门别类的名次、总分、平均分, 还有成绩等级等。对于老师来说如果缺少一种快捷的计算方法, 这项工作将要占用很多时间与精力。这里给大家介绍如何将多个函数嵌套使用。以输出“成绩等级”为例说明函数嵌套使用的优势:
仔细观察表5的要求, 思考如何输出等级。
本例中根据某一列数据, 输出另一列的值, 要用IF函数, 但作为IF函数的条件列本例中给出了两列 (“平时成绩”和“考试成绩”列) , 该如何表达同时满足两个条件呢, 马上会想到AND函数, 而将AND函数作为条件嵌套到IF函数中, 本例的问题就会轻而易举的解决了。函数表达式为=IF (AND (C4>=85, D4>=85) , "优秀", IF (AND (C4>=75, D4>=75) , "良好", IF (AND (C4>=60, D4>=60) , "合格", "不合格") ) ) 。
四、图表格式的快速设置
ALT键+鼠标移动图表, 可实现图表位置与单元格的绝对对齐。
实际工作中, 插入图表之后, 往往会随意将图表放在合适的位置即可, 很少有人会调整图表是否与某个单元格对齐。但在各类的EXCEL考试中, 却经常要求将其放在指定的位置, 如放在单元格A10:D25的区域中, 这时选中图表移动到左侧与A10单元格的左上角对齐, 然后在图表右下角进行缩放以使其与D25单元格的右下角对齐。要想将图表与这个区域大致对齐是很容易实现的, 然而要与单元格的边线绝对对齐却是鼠标所不好掌控的, 这时只需按下ALT键, 再稍稍移动图表即可与单元格边线轻松对齐。
参考文献
[1]唐艳云.论EXCEL中公式与函数的应用[J].电脑知识与技术, 2009 (18) .
[2]韩晶.EXCEL录入技巧[J].长治学院学报, 2008 (10) .
[3]姚文连.EXCEL文档和页面设置指南[J].电脑爱好者, 2009 (07) .
Excel实用技巧 第7篇
1 使用Excel筛选功能
有时我们需要完善客户信息，对公司发过来的总表需要查看哪些是我们要填的;有时我们也需要了解某村一个月用300 k Wh电能的共有多少户等，这时都要用到筛选功能。
Excel提供了两种筛选区域的命令。 (1) 在“数据”菜单中的“筛选”“自动筛选”，它适用于简单条件，如在公司总表中查看某供电所的信息，就可以点自动筛选后，在下拉小三角找到该供电所，选定后就会出现我们要的结果。 (2) “数据”菜单中的“筛选”“高级筛选”，适用于复杂条件。与排序不同，筛选并不重排区域，只是暂时隐藏不必显示的行。
2 批量删除空行
有时我们需要删除Excel工作簿中的空行，一般做法是将空行一一找出，然后删除。由于一般供电所里填的工作表空行很多，这样做就非常不方便。这时我们也可以利用“自动筛选”功能，把空行全部找到，然后一次性删除。具体做法：先在表中插入一个新的空行，然后按下Ctrl+A键，选择整个工作表，用鼠标单击“数据”菜单，选择“筛选”“自动筛选”命令。这时在每一列的顶部，都出现一个下拉列表框，在典型列的下拉列表框中选择“空白”，这样就可以将空行筛选出来，选中后将其删除。
3 快速、彻底清除单元格的内容
如果要删除单元格中的内容和它的格式和批注，就不能简单地应用选定该单元格，然后按Delete键的方法了。要彻底清除单元格内内容，可用以下方法：选定想要清除的单元格或单元格范围;单击“编辑”菜单中“清除”项中的“全部”命令，这些单元格就恢复了本来面目。
4 多张工作表中输入相同的内容
几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住Ctrl键，再单击窗口左下角的Sheet1, Sheet2来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其他工作表之中。输入完毕之后，再次按下键盘上的Ctrl键，然后使用鼠标左键单击所选择的多个工作表，解除这些工作表的联系，否则在一张表单中输入的数据会接着出现在选中的其他工作表内。
5 利用Ctrl+*选取文本
Excel动态图表制作技巧 第8篇
动态图表是利用Excel的函数、名称、空间等功能实现的交互展示图表[1]。与普通图表相比, 动态图表突破了空间限制, 能够展示出更多的数据信息;重新定义数据输入输出的方式, 将静态的传统的图表以更灵活、交互、实时的方式展现出来;实现对静态图表的批量制作, 大大节省制图的时间。
本文对excel动态图表的在制作中涉及的方法及技巧作出深入探讨。
一、利用公式和控件构建动态图表
使用公式和控件创建动态图表, 需要四个控件即数据源、函数、控件、图表共同合作。其中数据源的形式决定了数据的组织方式并决定了动图的交互展示的维度;从数据源中提取建图数据常用的函数有VLOOKUP、INDEX、OFFSET、CHOOSE、MATCH等函数;为使图表具有交互性, 需要在数据选择时设置有效性或在图表中插入进行数据切换和展示的触发组件, 如单选按钮、复选框、滚动条等控件;图表是数据展现的载体, 应根据数据的特点选择合适的图表类型来展现, 如柱状图、折线图、饼状图、条形图、面积图、散点图、股价图、曲面图、圆环图、气泡图、雷达图等, 不同的图表还可以根据布局和样式进行设置, 也可以几种图表组合表示[2]。
1、利用VLOOKUP函数组织数据源创建动态图表
本方法的关键步骤是创建如图3 所示的动态数据源表。首先, 将B3 单元格设置数据有效性为月份 (定义名称“月份”为1 月~12 月) , 生成如图4 所示的动态下拉列表。而图中的C6~C8 中使用查找函数VLOOKUP返回当月的销售额, 如 “C6==VLOOKUP ($B $3, $A $20:$D $31, A6+1, FALSE) ”, 即可生成动态数据源。由动态的数据源, 即可创建销售金额簇状柱形图如图2 所示。
2、利用控件创建简单的动态图表
原始数据如图5 所示:
动态查询效果图, 如下图6所示:
创建思路:
(1) 该动态图表使用了Activex控件- 组合框控件控制月份的显示。在此需添加辅助月份信息$K$2:$K$7, 设置Linked Cell到辅助单元格$K$1, 最大值为6、最小值为1, 与月份选择相对应。
(2) 在编辑图表的数据源时, 软件对数据源格式有限制, 不允许直接插入公式。故须事先定义好两个名称“销售额”、“销售表月份”, 作为“系列值”“系列名称”的数据源。
(3) 在定义数据源名称时, 应使用INDEX或OFFSET函数建立动态数据区域。如:“销售表月份=INDEX (销售表!$D$1:$I$1, , 销售表!$K$1) ”。
3、多控件联动创建复杂动态图表
控件之间的配合还可以实现相互联动, 从而实现更灵活的查询。如对同样的数据源图5 进行查询时, 通过单选按钮可以决定组合框链接哪个选项列表, 进而决定以哪个维度来展现数据。
创建思路:
(1) 使用单选按钮实现按人员查询和按月份查询, 并用IF来定义名称“查询方式”, 映射到辅助列:“查询方式=IF (销售表!$K$11=1, 销售表!$L$2:$L$9, 销售表!$K$2:$K$7) ”, 其中$K$11 是单选按钮链接到的单元格, $L$2:$L$9和$K$2:$K$7 为辅助列, 存放人员名单和月份信息。
(2) 使用组合框窗体控件, 进行具体显示项的选择。如图7, 在确认“按销售员查询”后, 选择“徐琴”查询各月份销售数据。
(3) 定义名称“销售数据源”、“横坐标”, 对数据源的图表项系列、水平轴标签的设定。如“销售数据源=IF ($K$11=1, OFFSET ($C$1, $L$11, 1, 1, 6) , OFFSET ($C$1, 1, $L$11, 8, 1) ) ”, 其中$L$11 为组合框窗体控件链接到的单元格, 实现数据源的动态选择。
在动态图表中, 控件相当于“发射器”, 在控件中做出选择时, 控件就发出“信号”。函数则起着“定位器”的作用, 根据控件的信号定位出作图需要引用的数据。其中, 查找与引用类的函数的熟练使用是动图创建的基础。
二、使用切片器创建动态图表
Excel 2010 版以后切片器的出现, 使动态图表的制作变得简单轻松。对于清单类型的数据表, 直接生成透视表 (如图9 所示) , 通过透视表变化出多个需要的表格或图表, 最后插入切片器 (如图10 所示) , 用这个切片器驱动多个透视图。
特别指出, Excel 2010 的切片器不支持表, 需要使用透视表。对于非清单数据的二维表格制作透视表的步骤这里不做赘述。
三、使用VBA创建动态图表
VBA的全称是Visual Basic for Application, 是基于Visual Basic for Windows发展而来的可视化编程工具。Exce VBA是指应用于Excel中、可扩展功能的VBA编程语言[3]。通过Exce VBA编程语言, 能够实现Excel中所有工具的功能, 创建图表也不例外。
对A1:D6 区域的数据, 使用VBA代码创建柱形图。当选择B1:D1 中任一单元格时 (如C1) , 图表显示如图12 所示柱形图:
在工作表中选择A2:A6 中的任一单元格 (如A6) , 图图13 表显示如所示柱形图:
由此实现图表的动态显示。行数据选择关键代码如图14 所示, 列数据选择类推。对于2010 以上版本的Excel, 包含VBA代码的文档不再允许被保存为.XLSX扩展名, 而要保存为基于XML且启用宏的.XLSM格式。
VBA代码创建动态图表的方法具有良好的可扩展性, 更利于在其他的平台上输出。
四、结语
1、无论哪种方法创建动态图表, 都需要有:决定显示方式的控件或操作、定位数据源的公式或语句, 才能在传统平淡的图表上加载神奇的动态效果。
2、插入表单控件、Activex控件及录制宏等操作需要首先启用功能区的开发工具选项卡。打开VBA窗口还可以使用<Alt+F11> 快捷键操作。
3、图表的展示在PPT应用中比较频繁。Excel动态图表可以导出为Flash格式, 插入PPT中进行动态演示。也可以将包含动态图表的Excel文件选择性链接入PPT (或者嵌入) , 在演示状态下点击该链接 (或图标) , 即可进入excel进行图表的动态演示。
参考文献
[1]徐军泰著.Excel动态图表制作与职场数据沟通[M].北京:机械工业出版社, 2014.1.
[2]张伟等著.基于EXCEL图表的专题图制作技巧[J].测绘与空间地理信息, 2014.10.
Excel的函数嵌套技巧 第9篇
1 Excel公式函数的特点
Excel作为一款日常办公用的表格处理软件,具有较强的使用实用性,集成了文字输入、数据整合、编辑处理和格式化等操作方式,以其强大的数据分析和处理能力为基础,将信息化处理、公式与函数的套用计算相协调处理。
Excel表格处理中的公式函数应用一般以“=”为开端,同时实现对单元格引用、运算符、数值、字符、函数等组成展开运算。Excel函数运算依据特定的函数式和特定的算法,通过预先设定的计算算法,将需要运算的相关参数进行代入处理,测算生成的数值不但为函数值,更作为较复杂数据分析的基础性依据。Excel函数运算能够应对复杂的数据处理,并将整个运算过程由繁到简进行处理。在一定的条件下,Excel公式函数还可以实现参数间的调换处理或是多数据混合运算,进一步减轻了使用者的工作量,最大程度上简化数据处理。
2 Excel的函数运用基础
Excel软件中包涵的公式种类较为繁杂,在此本文无法做到一一阐述,因而我们只针对其中较为重要的和较为常用的进行介绍。在Excel的课程教学中,函数讲解部分所占的比例较大,因此这一部分的介绍本文共分两个层次。
2.1 常用函数的应用
如图1所示,Excel中常用函数包括求和函数(SUM)、平均值(AVERAGE)、计数函数(COUNT)、最大值(MAX)、最小值(MIN)等。上述的函数类型在Excel的日常使用中频次高,且较为重要,因而同样也是教学工作的重点所在。当我们需要在Excel中对一个表格进行分析时,比如需要总结其中的最值时,需要首先选中对应单元格的数据,然后通过选中“fx”函数按钮,选择对应的函数公式,比如最大值(MAX)和最小值(MIN),这之后选择相应的数据区域,点击“确定”完成计算。
2.2 混合函数的应用
顾名思义,混合函数指的是通过对多种函数的参数及算法进行搭配使用,并通过“插入函数”的办法选择添加进而实现不同函数间的协调运算。例如,在一些单元格数据提取操作中,以身份证号码里的生日信息提取为例,身份证号码本身包涵18位数字,而我们需要的生日信息则包涵于7到14位数字,因而在提取中就需要使用MID函数。MID函数本身意为截取制定长度的字符,例如号码324001199801010120存放在C1单元格,则进行函数运算时我们就必须输入“=MID(C1,7,8)”,进而就能得到其中的日期值为19980101。另外,我们还需要使用DATE函数,将截取的数字值转化为日期的形式,此时组合函数的输入方法为“=DATE(MID(D1,7,4),MID(D1,11,2),MID(D1,13,2))”,将单元格格式改变为1998/01/01。
3 Excel的函数嵌套技巧
Excel表格中的函数嵌套技巧,本文的论述以IF逻辑函数为例,IF可以理解为考察参数值是否符合规定值,如果满足预先设定的条子,则会显示出一个数组。若不符合,则运算数值将返回另一个参数值之中。这一返回值可以是字符串,同时也可能是逻辑值(false&true)或是数值。例如根据学生的分数判断其成绩等级是属于优秀、良好、及格、差4种结果中的哪一种,90分以上为优秀,80分以上为良好,60分以上为合格,60分以下为差。若要判断的成绩在A2单元格,可以得到这样一个式子:“=IF(A2>=90,"优秀",IF(A2>=80,"良好",IF(A2>=60,"及格","差")))”,“A2>=90”是逻辑表达式,当满足条件是为优秀,不满足时为“IF(A2>=80,"良好",IF(A2>=60,"及格","差")))”这样一个表达式,又对这个表达式进行剖析,其实是一样的道理。注意每一个条件必须是互斥的,如最里面的表达式“IF(A2>=60,"及格","差")”这里A2>=60的意思是A2大于等于60并且小于80。
IF函数嵌套运用时使用者必须要注意公式录入时的引号与括号等符号是否使用到位,比如公式录入时双引号的使用必须要是英文状态,同时几重嵌套就需要几重括号。由于较多的符合公式和较为复杂的符号引用,在一部分的教学工作同样是十分重要的。另一方面,许多使用者在IF函数编写时同样需要避免死循环的现象出现。
4 结论
综上所述,Excel中的公式与函数应用具有十分积极的现实意义与教育教学意义,无论是日常办公处理或是院校教学中都占有十分重要的地位。因此相关教育教学工作中必须处理好函数嵌套使用,掌握必备的操作使用技巧,为日后的工作开展提供参考借鉴。
摘要：在Excel表格应用中,函数与公式是较为重要的环节,因此只有掌握一定的函数嵌套技巧,才能提升相应的工作学习效率。
关键词：Excel,公式与函数,嵌套技巧
参考文献
[1]胡雪峰.用Excel宏程序进行数据分类汇总[J].电脑编程技巧与维护,2014(22):85.
[2]刘升贵,黄敏,庄强兵.计算机应用基础[M].北京:机械工业出版社,2010.
Excel使用技巧 第10篇
[主题词]实战小技巧解决大问题
中国分类号：G642
Word和Excel是我们最常用的应用软件，有些功能并不为我们熟知，我们大多数人都不知不觉地遵循了“28原则”，这就是说“往往只有20%的人熟练掌握了80%的各项功能，而80%的人只知道和了解20%”。我们平日里无数次重复的得心应手的使用方法只不过是Excel全部技巧的百分之一。通过对下面几个问题，介绍几个Word和Excel应用小技巧（以2003版为例），帮大家能轻松解决工作学习中的实际应用问题，领略一下关于Word、Excel的别样风情。
一、“修订”与“批注”功能相结合实现电子作业批改
网络教学是信息技术与课程整合的主要方向，基于互联网资源的自主学习、网络协作学习是网络教学的主要形式，网络教学的特点之一就是学习资源、作业、学习成果数字化，随之带来的问题是如何方便地对电子作业进行批改和写评语。教师们对电子作业的批改还不是很适应，通常的做法是对电子作业直接进行修改，在作业的最后以不同的格式写一个评语，然后再发回给学生。但是这样做破坏了作业的本来面目，学生也不容易清楚地看到老师对那些内容作了批改，使学生感到电子作业的批改反而没纸质作业的批改来得直观。那么是否有比较方便、比纸质作业批改更直观的方法批改电子作业呢？其实Word就提供了这样的功能，用“修订”功能可以方便地对电子作业进行批改，用“批注”功能可以对作业中的任一部分内容写评语，两者相结合可以方便地实现电子作业的批改，效果比纸质作业的批改更加直观，而且还保持学生作业的原貌，学生可以有选择地接受或拒绝老师所作的修改。
电子作业批改的过程很简单，首先启动“修订”功能：选择“工具”→“修订”，或者在状态栏中双击“修订”标记，使该标记激活，文档就进入了修订状态；进入修订状态后，对文档进行编辑操作，会以修订标记来显示；如果要对作业的某一部分写评语，只要选中这部分内容，选择“插入”→“批注”，然后写评语内容。
学生收到批改过的作业，能直观地看到老师的批改情况和评语。如果学生觉得老师的批改有道理，只需在修改的内容上右击，出现快捷菜单，在快捷菜单中选择接受修订（当然也能拒绝修订）；如果学生觉得对老师的批改不理解，想与老师进一步讨论交流，也可以利用“修订”和“批注”进行修改，再发给老师，让老师再次批改，这样实现了一个作业多次批改，提高了学生作业的质量。如果要接受所有修订或拒绝所有修订可通过“审阅”工具栏操作，“审阅”工具栏在启动“修订”功能时自动显示在常用工具栏下方。
二、用“阅读版式”视图简化多媒体教学
教学环节信息技术的引入后，要求对教学内容进行教学设计，大部分老师用Word编写；然后再将教学设计中“教学过程”部分制作成PPT演示文稿或其他形式的课件，用于课堂多媒体教学。但课件制作增加了老师的工作量，能不能直接将Word用于多媒体教学。Word的“阅读版式”视图，具有较好的演示功能，可以代替PowerPoint演示文稿直接用于多媒体教学，提高了工作效率。
三、函数功能精确进行数字四舍五入
Excel提供了大量的函数，使得许多复杂问题变的简单。例如经常遇到的四舍五入问题，大多数人都会使用“格式栏”里的“增加小数位数”和“减少小数位数”来处理，虽然处理的数据看起来已经四舍五入了，但实际数据未发生变化，当进行数据运算时就会发生数据不一致性。但是如果使用函数处理四舍五入问题，就不会发生此类情况，方法如下：例如需要四舍五入的数据在A列，选择A1数据右边的空白单元格B1，选择“插入函数”中的“ROUND”函数，在“函数参数”界面中number中输入“A1”，num_digits中输入四舍五入的位数，点击确定，B1中会出现A1四舍五入后的数据。也可以直接在B1单元格中输入“=ROUND（A1，1）”，B1中会出现A1保留一位小数后的数据。将鼠标移到B1单元格的右下角的小黑方块上，这时鼠标就会变成一个黑色十字形，按住鼠标左键向下拖动，拖到A列数据结束，B列数据就为A列四舍五入后的数据，剪切B列数据，选中A列数据，点击右键选择“选择性粘贴”中的“数值”，点击“确定”，A列中的数据就会变成实实在在四舍五入后的数据，经的起数据运算的检验。例如：=ROUND（2.15，1）将2.15四舍五入到一个小数位，结果为2.2；=ROUND（-1.475，2）将-1.475四舍五入到两小数位结果为-1.48）；=ROUND（21.5，-1）将21.5四舍五入到小数点左侧一位结果为20。
四、快速批量删除空行
有时我们需要删除Excel工作薄中的空行，一般做法是将空行一一找出，然后删除。如果工作表的行数很多，这样做就非常不方便。我们可以利用“自动筛选”功能，把空行全部找到，然后一次性删除。做法：先在表中插入新的一个空行，然后按下Ctrl+A键，选择整个工作表，用鼠标单击“数据”菜单，选择“筛选”项中的“自動筛选”命令。这时在每一列的顶部，都出现一个下拉列表框，在下拉列表框中选择“空白”，直到页面内已看不到数据为止。在所有数据都被选中的情况下，单击“编辑”菜单，选择“删除行”命令，然后按“确定”按钮。这时所有的空行都已被删去，再单击“数据”菜单，选取“筛选”项中的“自动筛选”命令，工作表中的数据就全恢复了。
五、火速打印会议人员姓名台签
先取得与会者姓名，输人到一列多行的表格中，每个姓名一行。选中这一列后按下
[参考文献]
《Excel图表实战技巧精粹》人民邮电出版社2008年8月
高校中Excel的应用技巧 第11篇
学工部的刘老师在学期初遇到这样一个问题:在给学生上保险时, 保险公司要求把学生信息表中全体学生的出生年月一列全部变成中间带分隔符“-”形式的。假如出生列在E列, E2为19900103更改为1990-01-03, 如果单纯用设置单元格格式, 更改日期类型的话, 单元格会出现######这种错误;如果一个一个更改就太麻烦了, 我们用一个公式就可以解决这个问题, 先在E列后面插入一列, 在F2单元格中输入“=mid (e2, 1, 4) &-mid (e2, 5, 2) &-mid (e2, 7, 2) &”, 再敲击回车就变成了1990-01-03。最后拖动填充柄可使整列全部变为日期型。
2. 使用函数统计学生成绩
作为一名班主任, 每个学期末都要统计学生的成绩, 排出相应的名次。以下面的学生成绩表为例:
(1) 选中J3单元格, 输入公式“=SUM (C3:I3) ”, 用于计算第一位学生的总分, 拖动填充柄计算其他学生的总分
(2) 选中Q3单元格, 输入公式“=RANK (P3, P$3:P$35) ”, 用于计算第一位学生总成绩的名次, 拖动填充柄计算其他学生的名次。
3. 使用VLOOKUP查找函数匹配
在每年的助学贷款中, 有些同学的姓名和银行卡号在上交表格时都有, 但省里给的表格的姓名顺序和各系部统计的不一样, 要想填充上表中的数据就要用到一个VLOOKUP函数。假定各系部上交的工作表为SHEET1, A列为姓名, B列为银行卡号, 现要在省里给定表SHEET2表A列输入姓名, B列自动填充对应的银行卡号, 第一行为表头, B2单元格公式输入:=IF (A2="", "", IF (ISERROR (VLOOKUP (A2, SHEET1!A:B, 2, 0) ) , "查无此人", VLOOKUP (A2, SHEET1!A:B, 2, 0) ) ) , 公式可拖动填充柄向下复制。此处SHEET1=01, 01表为原始底。
另外有A1和A2两张EXCEL表, 两张表都有“姓名”一栏, 怎么显示出A2表中姓名与A1表姓名不同的部分呢?我们也可以用VLOOKUP这个函数。首先, 在A2表中插入一列空白列, 假设是B列, 又假设两张表的姓名都在第一栏, 那么在A2表的B2单元格中输入=VLOOKUP (A2, SHEET1!A:A, 1) , 然后按回车键, 只要是姓名不同的就会显示#N/A。
4. 统计某分数段的学生数
在期末考试后, 一般老师都要上交成绩分析表, 要统计出各分数段的学生人数, 像不及格人数有多少人, 60～70分数段有多少人, 70～80分数段有多少人, 80～90分数段有多少人以及90～100分数段有多少人。面对众多的数据, 我们在这里用DCOUNT函数轻松解决这一问题。如图所示, 在E9单元格中输入公式:=DCOUNT (A1:D11, "高数", E1:F2) , 确认后即可求出“高数”列中, 成绩大于等于70, 而小于80的数值单元格数目 (相当于此分数段人数) 。
5. 巧用合并功能
根据工作的需要, 有时想把B列的内容与C列的内容进行合并, 如果行数较少, 可以直接用“剪切”和“粘贴”来完成操作, 但如果有几万行, 这样就太麻烦了。解决的办法是:在C行后插入一个空列 (如果D列没有内容, 就直接在D列操作) , 在D 1中输入“=B1&C1”, D1列的内容就是B、C两列的和了。选中D1单元格, 用鼠标指向单元格右下角的小方块“■”, 当光标变成"+"后, 按住鼠标拖动光标向下拖到要合并的结尾行处, 就完成了B列和C列的合并。这时先不要忙着把B列和C列删除, 先要把D列的结果复制一下, 再用“选择性粘贴”命令, 将数据粘贴到一个空列上。这时再删掉B、C、D列的数据。
学生在用AutoCAD绘图时, 有人喜欢在EXCEL中存储坐标点, 在绘制曲线时调用这些参数。存放数据格式为“x, y”的形式, 首先在Excel中输入坐标值, 将x坐标值放入A列, y坐标值放入到B列, 然后利用“&”将A列和B列合并成C列, 在C1中输入:=A1&", "&B1, 此时C1中的数据形式就符合要求了, 再用鼠标向下拖动C1单元格, 完成对A列和B列的所有内容的合并 (如图所示) 。
要合并不同单元格的内容, 还有一种方法就是利用CONCATENATE函数, 此函数的作用是将若干文字串合并到一个字串中, 具体操作为“=CONCATENATE (B1, C1) ”。比如, 假设在某一河流生态调查工作表中, B2包含“物种”、B3包含“河豚鱼”, B7包含总数52, 那么:输入“=CONCATENATE ("本次河流生态调查结果:", B2, "", B3, "为", B7, "条/公里。") ”计算结果为:本次河流生态调查结果:河豚鱼物种为52条/公里。
6. 绘制函数图像
教数学的周老师想在EXCEL中快速准确地绘制一条函数曲线, 这就要用到图表功能了。以绘制y=lg (6+x^3) 的曲线为例, 其方法如下:在某张空白的工作表中, 先输入函数的自变量:在A列的A1格输入"X=", 表明这是自变量, 再在A列的A2及以后的格内逐次从小到大输入自变量的各个值;实际输入的时候, 通常应用等差数列输入法, 先输入前二个值, 定出自变量中数与数之间的步长, 然后选中A2和A3两个单元格, 使这二项变成一个带黑色边框的矩形, 再用鼠标指向这黑色矩形的右下角的小方块“■”, 当光标变成"+"后, 按住鼠标拖动光标到适当的位置, 就完成自变量的输入。在B列的B1格输入函数式的一般书面表达形式, y=lg (6+x^3) ;在B2格输入“=ABS (LOG10 (6+A2^3) ) ”, B2格内马上得出了计算的结果。这时, 再选中B2格, 让光标指向B2矩形右下角的“■”, 当光标变成"+"时按住光标沿B列拖动到适当的位置即完成函数值的计算。然后再点击工具栏上的“图表向导”按钮, 选择“X, Y散点图”, 然后在出现的“X, Y散点图”类型中选择“无数据点平滑线散点图”;此时可察看即将绘制的函数图像, 发现并不是我们所要的函数曲线, 单击“下一步”按钮, 选中“数据产生在列”项, 给出数据区域, 这时曲线就在我们面前了
7. 生成成绩条
常有班主任在学期末时面临“如何打印成绩条”这样的问题, 有不少人采取录制宏或VBA的方法来实现, 这对于一些非计算机专业的老师们来说有一定难度。出于此种考虑, 我在这里给出一种用函数实现的简便方法。
此处假定学生成绩保存在Sheet1工作表的A1至G64单元格区域中, 其中第1行为标题, 第2行为学科名称。切换到Sheet2工作表中, 选中A1单元格, 输入公式:=IF (MOD (ROW () , 3) =0, ″″, IF (0MOD?ROW () , 3 (=1, sheet1!A1, INDEX (sheet1!$A:$G, INT ( ( (ROW () +4) /3) +1) , COLUMN () ) ) ) 。再次选中A1单元格, 用“填充柄”将上述公式复制到B1至G1单元格中;然后, 再同时选中A1至G1单元格区域, 用“填充柄”将上述公式复制到A2至G185单元格中。这样, 成绩条基本成型, 下面再简单修饰一下即可。
调整好行高和列宽后, 同时选中A1至G2单元格区域 (第1位学生的成绩条区域) , 按“格式”工具栏“边框”右侧的下拉按钮, 在随后出现的边框列表中, 选中“所有框线”选项, 为选中的区域添加边框 (如果不需要边框, 可以不进行此步及下面的操作) 。
