excel进阶应用.ppt

《excel进阶应用.ppt》由会员分享，可在线阅读，更多相关《excel进阶应用.ppt（45页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、EXCEL进阶应用用叶仁春叶仁春Mail:主要内容主要内容1常用基础技巧常用基础技巧 2公式函数应用公式函数应用 3图表生成图表生成 2009年9月华中科技大学现代教育技术中心1常用基础技巧常用基础技巧 各种类型数据的输入方法和技巧各种类型数据的输入方法和技巧A A打印设置打印设置C C复制粘贴技巧复制粘贴技巧B B2009年9月华中科技大学现代教育技术中心各种类型数据的输入方法和技巧各种类型数据的输入方法和技巧A A超长数字文本输入超长数字文本输入单元格输入前进行格式设置：单元格输入前进行格式设置：方法方法1:1.选中需输入的单元格选中需输入的单元格2.单击单击【格式格式】菜单，选择菜单，选

2、择【单元格单元格】3.选择选择【文本文本】2009年9月华中科技大学现代教育技术中心在输入数字时先输入一个半角的单引号，然后再输入数字。在输入数字时先输入一个半角的单引号，然后再输入数字。注意：单引号必须是英文状态下输入。注意：单引号必须是英文状态下输入。方法方法2:先输入先输入号，在输入数字号，在输入数字 2009年9月华中科技大学现代教育技术中心输入分数、负数输入分数、负数输入分数输入分数:在分数前输入在分数前输入“0”(零零)，并且在，并且在“0”和分子之和分子之间要有一个空格隔开间要有一个空格隔开。输入真分数时，先输入输入真分数时，先输入0，再，再输入分数：分子输入分数：分子/分母分母

3、单元格显示分子单元格显示分子/分母分母编辑栏中显示小数数值编辑栏中显示小数数值 输入假分数时会自动约分输入假分数时会自动约分2009年9月华中科技大学现代教育技术中心直接在单元格中输入分子直接在单元格中输入分子/分母形式，回车后则显示日期分母形式，回车后则显示日期输入分子输入分子/分母分母显示日期显示日期输入负数输入负数在单元格中输入负数时，可在负数前输入在单元格中输入负数时，可在负数前输入“-”作标识，也可作标识，也可将数字置在将数字置在()括号内来标识。括号内来标识。2009年9月华中科技大学现代教育技术中心输入日期、时间输入日期、时间可用多种格式来输入一个日期，可以用斜杠可用多种格式来输

4、入一个日期，可以用斜杠“/”或者或者“-”来分隔日期中的年、月、日部分。来分隔日期中的年、月、日部分。在单元格中插入当前日期，可以按键盘上的在单元格中插入当前日期，可以按键盘上的Ctrl+;组合键。组合键。用户可以按用户可以按24小时制输入，也可以按小时制输入，也可以按12小时制输入。小时制输入。例：要输入下午例：要输入下午3时时30分分38秒，用秒，用24小时制输入格式为：小时制输入格式为：3:30:38，而用，而用12小时制输入时间格式为：小时制输入时间格式为：3:30:38 p，注意，注意字母字母“p”和时间之间有一个空格。和时间之间有一个空格。如果要在单元格中插入当前时间，则按如果要在

5、单元格中插入当前时间，则按Ctrl+Shift+;键键。输入日期输入日期输入时间输入时间例：输入例：输入2009年年9月月12日，可用日，可用2009/9/12和和2009-9-12输入。输入。2009年9月华中科技大学现代教育技术中心根据初始值决定以后的填充项，用鼠标根据初始值决定以后的填充项，用鼠标左键左键点住初始值所在单元点住初始值所在单元的右下角，鼠标指针变为实心十字拖曳至填充的最后一个单元格，的右下角，鼠标指针变为实心十字拖曳至填充的最后一个单元格，即可完成。即可完成。单个单元格内容为纯字符、数字或单个单元格内容为纯字符、数字或公式，填充相当于数据复制。公式，填充相当于数据复制。单个

6、单元格内容为文字和数字混合单个单元格内容为文字和数字混合体，填充时文字不变，最右边的数体，填充时文字不变，最右边的数字递增。字递增。单个单元格内容为单个单元格内容为excel预设的填充预设的填充序列的一员，按预设序列填充。序列的一员，按预设序列填充。自动数据输入自动数据输入自动填充自动填充2009年9月华中科技大学现代教育技术中心根据初始值决定以后的填充项，用根据初始值决定以后的填充项，用鼠标鼠标右键右键点住初始值所在单元的右点住初始值所在单元的右下角，鼠标指针变为实心十字拖曳下角，鼠标指针变为实心十字拖曳至填充的最后一个单元格，即可完至填充的最后一个单元格，即可完成。成。产生一个序列产生一个

7、序列2009年9月华中科技大学现代教育技术中心方法一：利用方法一：利用“Microsoft Barcode Control 9.0”控件控件。执行“视图工具栏控件工具箱”命令，在弹出的控件工具栏中单击“其它控件”选项，在出现的命令选项列表中选择“Microsoft Barcode Control 9.0”，这样就可以在工作表中插入一个条形码，若要修改条形码下端的数字，可右击插入的条形码，从弹出的快捷菜单中，选择“属性”命令，在弹出的“属性”设置对话框中的Value值中输入需要的条形码数字 1.单击单击【视图视图】菜单菜单 2.选择选择【工具栏工具栏】3.选择选择【控件工具箱控件工具箱】4.单击

8、单击【其他工具其他工具】5.选择选择【Microsoft Barcode Control 9.0】输入条形码输入条形码2009年9月华中科技大学现代教育技术中心右击条码区域右击条码区域 设置条码样式等属性设置条码样式等属性 条码属性设置：条码属性设置：设置设置linkedCell等属性后，取消等属性后，取消编辑状态才能显示结果。编辑状态才能显示结果。设置条码链接等属性设置条码链接等属性 2009年9月华中科技大学现代教育技术中心方法二：利用字体文件方法二：利用字体文件2.2.在在excelexcel中将需生成条码的单元格数字字体设置成中将需生成条码的单元格数字字体设置成ccode39ccode

9、39即可。即可。1.1.将字体文件将字体文件(ConnectCode39.ttf(ConnectCode39.ttf等等)拷贝到拷贝到c:windowsfontsc:windowsfonts文件夹下。文件夹下。将需条码显示的数据利用公式转换成将需条码显示的数据利用公式转换成*数值数值*格式格式设置单元格字体为设置单元格字体为CCode39设置成设置成 *数值数值*格式是为了条码枪更好的识别，单纯显示条码格式是为了条码枪更好的识别，单纯显示条码可直接用数值。可直接用数值。可大批量直接设置条码。可大批量直接设置条码。2009年9月华中科技大学现代教育技术中心复制粘贴技巧复制粘贴技巧B B粘贴：直接

10、将复制的源数据粘贴：直接将复制的源数据 直接粘贴在目标位置。直接粘贴在目标位置。选择性粘贴：可根据需要选择粘贴内容。选择性粘贴：可根据需要选择粘贴内容。Excel中粘贴分为粘贴和选择性粘贴中粘贴分为粘贴和选择性粘贴。2009年9月华中科技大学现代教育技术中心打印设置打印设置C C可通过文件可通过文件|打印区域打印区域-设置打印区域设置打印区域(取消打印区域取消打印区域)，并通，并通过视图过视图|分页预览来调整分页符位置。分页预览来调整分页符位置。1.2.3.可通过视图可通过视图|页眉和页脚设置打印的标题和页脚。页眉和页脚设置打印的标题和页脚。2009年9月华中科技大学现代教育技术中心2公式函数

11、应用公式函数应用 公式与函数输入方法公式与函数输入方法A A常用字符串函数使用常用字符串函数使用C C单元格引用单元格引用B B2009年9月华中科技大学现代教育技术中心1.确定单元格确定单元格2.激活编辑栏激活编辑栏3.3.输入公式：输入公式：=X+2*X*Y=X+2*X*Y1.2.3.输入公式步骤输入公式步骤:输入函数输入函数 须先输入等号须先输入等号“=”公式中不能含空格公式中不能含空格公式与函数输入方法公式与函数输入方法A A单击单击【公式公式】按钮按钮 2009年9月华中科技大学现代教育技术中心单元格作为一个整体以单元格地址的描述形式参与运算称单元格作为一个整体以单元格地址的描述形式

12、参与运算称为单元格引用。为单元格引用。相对引用相对引用绝对引用绝对引用混合引用混合引用单元格引用单元格引用引用方式引用方式单元格引用单元格引用B B2009年9月华中科技大学现代教育技术中心例：在例：在G2中输入公式中输入公式“B2+C2+D2+E2+F2”相对引用是指当把一个含有单元格地址的公式相对引用是指当把一个含有单元格地址的公式复制到一个新的位置或者用一个公式填入一个复制到一个新的位置或者用一个公式填入一个区域时，公式中的单元格地址会随着改变。区域时，公式中的单元格地址会随着改变。相对引用相对引用2009年9月华中科技大学现代教育技术中心例：例：在在G2中输入公式中输入公式“$B$2+

13、$C$2+$D$2+$E$2+$F$2”设置绝对地址设置绝对地址需在行号和列需在行号和列号前加符号号前加符号“$”绝对引用是指在把公式复制绝对引用是指在把公式复制或填入到新位置时，使其中或填入到新位置时，使其中的单元格地址保持不变。的单元格地址保持不变。绝对引用绝对引用2009年9月华中科技大学现代教育技术中心例：在例：在D14中输入公式中输入公式“D$13*$C14”混合引用是指在一个单元格混合引用是指在一个单元格地址中，既有绝对地址引用地址中，既有绝对地址引用又有相对地址引用。又有相对地址引用。混合引用混合引用2009年9月华中科技大学现代教育技术中心常用字符串函数使用常用字符串函数使用

14、C C字符串合并字符串合并 常用字符提取函数常用字符提取函数 2009年9月华中科技大学现代教育技术中心字符串合并字符串合并 CONCATENATE(text1,text2,.)将若干字符串合并到一个字符串中。将若干字符串合并到一个字符串中。合并字符串也可用合并字符串也可用“&”。2009年9月华中科技大学现代教育技术中心LEFT(text,num_chars)LEFT从字符串最左端提取指定数量的字符。从字符串最左端提取指定数量的字符。RIGHT(text,num_chars)RIGHT从字符串最右端提取指定数量的字符。从字符串最右端提取指定数量的字符。MID(text,start_num,n

15、um_chars)MID 从字符串任意位置提取指定数量字符。从字符串任意位置提取指定数量字符。LEN(text)LEN 返回字符串的字符个数。返回字符串的字符个数。常用字符提取函数常用字符提取函数 2009年9月华中科技大学现代教育技术中心提取提取A3单元格单元格右起起右起起4个字符个字符提取提取A3单元格单元格左起起左起起3个字符个字符提取提取A3单元格第单元格第11个字个字符到字符串末尾的字符符到字符串末尾的字符IF(LEN(B2)=15,19,)&MID(B2,7,6+(LEN(B2)=18)*2)从身份证号码中提取出生日期从身份证号码中提取出生日期2009年9月华中科技大学现代教育技术

16、中心3图表生成图表生成 图表特点图表特点A A创建图表创建图表B B2009年9月华中科技大学现代教育技术中心图表特点图表特点A A直观形象直观形象 种类丰富种类丰富 1.柱形图柱形图2.条形图条形图3.折线图折线图4.饼图饼图5.XY散点图散点图6.面积图面积图7.圆环图圆环图 8.雷达图雷达图 9.曲面图曲面图10.气泡图气泡图11.股价图股价图12.圆柱图圆柱图13.圆锥图圆锥图14.棱锥图棱锥图还有还有20种内置自定义图表类型。种内置自定义图表类型。2009年9月华中科技大学现代教育技术中心双向联动双向联动 图表自动随数据变化而变化，反之数据也随图标的变化而图表自动随数据变化而变化，反

17、之数据也随图标的变化而变化。变化。需在选项中设置为自动重算。需在选项中设置为自动重算。1.2.2009年9月华中科技大学现代教育技术中心二维坐标二维坐标 虽然虽然excel中提供了一些三维图表类型，但实质上还是二维平面中提供了一些三维图表类型，但实质上还是二维平面坐标系下建立的图表。坐标系下建立的图表。X轴分类轴，轴分类轴，Y轴为系列轴，只有轴为系列轴，只有Z轴为数值轴，若用平面坐标轴为数值轴，若用平面坐标轴也可表现出来。轴也可表现出来。2009年9月华中科技大学现代教育技术中心创建图表创建图表B B图表创建方法图表创建方法 1依次单击依次单击【插入插入】-【图表图表】命令，打开图表向导对命令

18、，打开图表向导对话框，插入图表。话框，插入图表。1.2.2009年9月华中科技大学现代教育技术中心2直接单击常用工具栏上直接单击常用工具栏上【插入图表插入图表】按钮，打开图表按钮，打开图表向导对话框，插入图表。向导对话框，插入图表。3选中目标数据区域，按选中目标数据区域，按键创建图表并新建图表键创建图表并新建图表工作表。工作表。4单击单击【图表图表】-【图表类型图表类型】命令，打开图表向导对命令，打开图表向导对话框，插入图表。话框，插入图表。1.2.2009年9月华中科技大学现代教育技术中心图表创建步骤图表创建步骤 Step 1选定需创建图表的数据系列。选定需创建图表的数据系列。Step 2根

19、据图表创建方法，打开根据图表创建方法，打开【图表创建向导图表创建向导】，选，选择图表类型。择图表类型。选择多行数据，在图表中含多个选择多行数据，在图表中含多个数据系列数据点。数据系列数据点。2009年9月华中科技大学现代教育技术中心Step 3选定需创建图表的数据系列。选定需创建图表的数据系列。可添加数据系列，并设定数据可添加数据系列，并设定数据2009年9月华中科技大学现代教育技术中心Step 4设置设置【图表选项图表选项】设置图标、轴标题设置图标、轴标题 设置坐标轴设置坐标轴 设置网络线设置网络线 设置图例属性设置图例属性 设置数据标签设置数据标签 2009年9月华中科技大学现代教育技术中

20、心Step 5设定图表位置。设定图表位置。图表创建在新的工作表中图表创建在新的工作表中 图表创建并插入所选定的工作表中图表创建并插入所选定的工作表中 2009年9月华中科技大学现代教育技术中心示例：利用自动筛选创建图表示例：利用自动筛选创建图表 Step 1输入数据输入数据 1.2.3.Step 2设定工作表为自动筛选。设定工作表为自动筛选。2009年9月华中科技大学现代教育技术中心Step 3设定工作表为自动筛选。设定工作表为自动筛选。1.2.3.2009年9月华中科技大学现代教育技术中心Step 3选定列选定列C2：C65、D2：D65为图标创建数据系列。为图标创建数据系列。Step 4创

21、建图表，并选择图表类型为柱状图创建图表，并选择图表类型为柱状图 C1:C65 D1:D65 1.2.2009年9月华中科技大学现代教育技术中心Step 5设置图表属性设置图表属性 在图表任意位置单击右键，出现菜单，可分别设置图表区格在图表任意位置单击右键，出现菜单，可分别设置图表区格式、图表类型、定义源数据，设置图表选项等属性。式、图表类型、定义源数据，设置图表选项等属性。设置属性菜单。设置属性菜单。也可在图表相应位置双击，设置相应属性也可在图表相应位置双击，设置相应属性。双击柱状图，可在数据系列格式对话框中设置图形充效果双击柱状图，可在数据系列格式对话框中设置图形充效果。若为折线图或散点图，

22、可在图形曲线上设置趋势线若为折线图或散点图，可在图形曲线上设置趋势线。2009年9月华中科技大学现代教育技术中心示例：利用邮件合并批量打印成绩单示例：利用邮件合并批量打印成绩单 Excel表格中最好不单独输入标题。表格中最好不单独输入标题。Step 1建立数据源：在建立数据源：在excel中输入成绩单信息数据并中输入成绩单信息数据并保存。保存。2009年9月华中科技大学现代教育技术中心Step 2创建主文档：在创建主文档：在word中创建需输出的成绩单文本。中创建需输出的成绩单文本。Step 3在在word中单击中单击【工具工具】|【信函与邮件信函与邮件】-【显显示邮件合并工具栏示邮件合并工具

23、栏】。邮件合并主要用到邮件合并主要用到【打开数据源打开数据源】、【插入域插入域】、【查查看合并数据看合并数据】和和【合并到新文档合并到新文档】按钮。按钮。2009年9月华中科技大学现代教育技术中心Step 4单击工具栏单击工具栏【打开数据源打开数据源】，选择，选择excel文件并选文件并选定定sheet页。页。Step 5单击单击【收件人收件人】按钮，选择需打印的名单，不需按钮，选择需打印的名单，不需要的姓名前要的姓名前去掉去掉。2009年9月华中科技大学现代教育技术中心Step 6光标移动到所需位置，单击光标移动到所需位置，单击【插入域插入域】按钮，选按钮，选择域名，依次完成各域设置。择域名，依次完成各域设置。域插入完成后，可单击域插入完成后，可单击【查看合并数据查看合并数据】，显示，显示实际数据实际数据。2009年9月华中科技大学现代教育技术中心Step 7检查无误后，单击检查无误后，单击【合并到新文档合并到新文档】，完成所选，完成所选名单的成绩单。名单的成绩单。去除小数误差方法：右击域，选择切换域代码，输入去除小数误差方法：右击域，选择切换域代码，输入#.0,使数据保留一位小数。使数据保留一位小数。2009年9月华中科技大学现代教育技术中心谢谢 谢谢 大大 家家！2009年9月华中科技大学现代教育技术中心