EXCEL高级应用

1、EXCEL高级应用朱 理2021年11月18日河南.郑州 Tobacco Yonyou Software Co.,Ltd. 也许你已经在也许你已经在ExcelExcel中完成过上百张中完成过上百张财务报表，也许你已利用财务报表，也许你已利用ExcelExcel函数实现函数实现过上千次的复杂运算，也许你认为过上千次的复杂运算，也许你认为ExcelExcel也不过如此，甚至了无新意。但我们平也不过如此，甚至了无新意。但我们平日里无数次重复的得心应手的使用方法日里无数次重复的得心应手的使用方法只不过是只不过是ExcelExcel全部技巧的百分之一。本全部技巧的百分之一。本专题从专题从ExcelExc

2、el中的一些鲜为人知的技巧入中的一些鲜为人知的技巧入手，领略一下关于手，领略一下关于ExcelExcel的别样风情。的别样风情。 Tobacco Yonyou Software Co.,Ltd.函数函数VLOOKUP 存货编码存货名称产品价类 调拨价（条） 6901028045909云烟（软珍）一类烟121.676901028177092云烟（软苁蓉）二类烟88.856901028311526云烟（大紫）二类烟75.686901028177122云烟（12mg 苁蓉)二类烟71.086901028046893云烟（紫）三类烟54.626901028315432红塔山(硬经典100)三类烟54.

3、626901028045582云烟（红）三类烟40.86901028055345红河（硬甲）三类烟35.546901028055314红河（软甲）四类烟29.62产品编码产品名称产品价类销售量(万支） 调拨价（条） 销售收入（万元）6901028045909云烟（软珍）一类烟1,000.00 121.67 608.35 6901028177092云烟（软苁蓉）二类烟2,000.00 88.85 888.50 6901028311526云烟（大紫）二类烟232.00 75.68 87.79 6901028177122云烟（12mg 苁蓉)二类烟2,000.00 71.08 710.80 6901

4、028046893云烟（紫）三类烟15,000.00 54.62 4,096.50 - - - - - - - - - - 合计20,232.00 63.19 6,391.94 Tobacco Yonyou Software Co.,Ltd.EXCEL 统计数量问题统计数量问题D1 洋参10，枸杞子10，玉竹20，红枣10，甲鱼500，姜片 D2 首乌30，黑豆60，甲鱼500，红枣10，姜片 D3 制何首乌30，槐角，20，冬瓜皮20，生山楂30，乌龙茶3. D251 法半夏10，茯苓10，广橘皮6，竹茹10，天麻10，当归10，白术10，双钩藤10，刺蒺藜10，北柴胡5，炒枳实5，炙甘草3

5、，石菖蒲3，灵磁石15 D项目中均为各种食疗方（其中有纯中药，有药食两用的品种，有普通食物）；现在知道药食两用的品种为：丁香、茴香、山药、杏仁.（省略不写，数目多） 知道食物的品种为：猪肉、鸡、冬瓜.（省略不写，数目多） 现在需要求出D项各食疗方中，各种药食两用品种出现的次数（即丁香出现了多少次，茴香出现了多少次.），各种纯中药品种出现的次数，各种食物出现的次数 解决方法解决方法统计很简单，如统计丁香出现的次数。 =COUNTIF(D1:D251,*丁香*) 但这样要一个一个做公式。所以要将药食两用品及食物品种做个列表，比如E列为药食两用品,G列为食物品种,则: F1=COUNTIF($D$1

6、:$D$251,*&E1&*) H1=COUNTIF($D$1:$D$251,*&G1&*) 向下复制公式 Tobacco Yonyou Software Co.,Ltd.sumif用法图解用法图解sumif：根据条件求和。产品价类产品系列销售量(万支） 调拨价（条） 销售收入（万元）一类烟1,000.00 121.67 608.35 二类烟4,232.00 79.73 1,687.09 三类烟15,000.00 54.62 4,096.50 四类烟0.00 - 0.00 五类烟0.00 - 0.00 云烟20,232.00 63.19 6,391.94 红河0

7、.00 - 0.00 红塔山0.00 - 0.00 Tobacco Yonyou Software Co.,Ltd.EXCEL中将银行卡号中的空格去除的解决方案中将银行卡号中的空格去除的解决方案一、问题的提出：一、问题的提出：在工作中经常会遇到这种情况：一个EXCEL表格中的一列数据为银行卡号，工作人员输入的时候为了便于看清楚，就隔几位加一空格将数据按规范分开。但报给银行时却不需要这个空格，而且必须将空格去掉。如下图： Tobacco Yonyou Software Co.,Ltd.EXCEL中将银行卡号中的空格去除的解决方案中将银行卡号中的空格去除的解决方案二、问题解决方案：二、问题解决方案

8、：1、思路1：将空格全部替换成空。利用EXCEL中查找替换功能。在试验中出现以下问题，用替换功能将空格替换后，卡号全部变成科学记数格式。如下图： 思路不变，换个方式方法：1)复制需要转换的卡号；2)新建一个文本文档，将复制的卡号粘贴过去，再利用里面的替换功能将空格去掉；3)在EXCEL中将卡号那列格式设置为文本格式(关键中的关键)；4)将文本文档中的卡号粘贴到EXCEL中。OK，成功! Tobacco Yonyou Software Co.,Ltd.2、思路2：用函数(主要用到的函数是取字符和将字符连接起来的函数)函数公式如下：=CONCATENATE(MID(C1,1,4),MID(C1,6

9、,4),MID(C1,11,4),MID(C1,16,4)。呵呵，此方法相对复杂一些，较笨一些。其中有个致命局限就是卡号那列数据必须统一，即规范化，有一定的规律性。例如，如果其中有一部分是存折号，那就不太适用了。就需要先排序，再利用公式。OK，再次成功! 3、思路3：还是用函数，不过此函数非彼函数。也能一步到位，而且没有局限性。函数公式如下：=SUBSTITUTE(源值, ,)，关键点：第一个双引号中间有一空格哦，否则也会失败哦。 各位好好总结一下，有时对工作、对提高工作效率很有用的哦。EXCEL中将银行卡号中的空格去除的解决方案中将银行卡号中的空格去除的解决方案 Tobacco Yonyou

10、 Software Co.,Ltd.将单元格数字转换成万元将单元格数字转换成万元怎样将EXCEL整张表中的单位是元的所有数字，变成万元的数字？ 在表格之外的某个单元格输入10000，鼠标右击该单元格点“复制”，用鼠标选定你表格的数据范围，点鼠标右键，点“选择性粘贴”，选中“除”和“跳过空单元格”，点“确定”，再删除你输入10000的那个单元格。如果转换后小数位过多，可选定所有数据范围，点“格式”/“单元格”/“数字”，将“分类”设定为“数值”，并根据你的要求设定“小数位数”，点“确定”退出即可。 Tobacco Yonyou Software Co.,Ltd.用用Excel 2010数据有效性

11、拒绝错误数据数据有效性拒绝错误数据Excel强大的制表功能，给我们的工作带来了方便，但是在表格数据录入过程中难免会出错，一不小心就会录入一些错误的数据，比如重复的身份证号码，超出范围的无效数据等。其实，只要合理设置数据有效性规则，就可以避免错误。下面咱们通过两个实例，体验Excel 2010数据有效性的妙用，excel2013 叫做“数据验证”实例一：拒绝录入重复数据实例一：拒绝录入重复数据 身份证号码、工作证编号等个人ID都是唯一的，不允许重复，如果在Excel录入重复的ID，就会给信息管理带来不便，我们可以通过设置Excel 2010的数据有效性，拒绝录入重复数据。 运行Excel 201

12、0，切换到“数据”功能区，选中需要录入数据的列(如：A列)，单击数据有效性按钮，弹出“数据有效性”窗口。 Tobacco Yonyou Software Co.,Ltd.用用Excel 2010数据有效性拒绝错误数据数据有效性拒绝错误数据切换到“设置”选项卡，打下“允许”下拉框，选择“自定义”，在“公式”栏中输入“=countif(a:a,a1)=1”（不含双引号，在英文半角状态下输入）。切换到“出错警告”选项卡，选择出错出错警告信息的样式，填写标题和错误信息，最后单击“确定”按钮，完成数据有效性设置。 Tobacco Yonyou Software Co.,Ltd.用用Excel 2010数

13、据有效性拒绝错误数据数据有效性拒绝错误数据这样，在A列中输入身份证等信息，当输入的信息重复时，Excel立刻弹出错误警告，提示我们输入有误。 Tobacco Yonyou Software Co.,Ltd.用用Excel 2010数据有效性拒绝错误数据数据有效性拒绝错误数据这时，只要单击“否”，关闭提示消息框，重新输入正确的数据，就可以避免录入重复的数据。注：如果是整个表中都不允许录入重复的数据，则其中公式为注：如果是整个表中都不允许录入重复的数据，则其中公式为=COUNTIF($1:$65535,A1)=1(不含双引号不含双引号)。 这里的这里的$1:$65535表示对全工表示对全工作表范围

14、进行重复检查作表范围进行重复检查 Tobacco Yonyou Software Co.,Ltd.用用Excel 2010数据有效性拒绝错误数据数据有效性拒绝错误数据实例二：快速揪出无效数据实例二：快速揪出无效数据用Excel处理数据，有些数据是有范围限制的，比如以百分制记分的考试成绩必须是0100之间的某个数据，录入此范围之外的数据就是无效数据，如果采用人工审核的方法，要从浩瀚的数据中找到无效数据是件麻烦事，我们可以用Excel 2010的数据有效性，快速揪出表格中的无效数据。用Excel 2010打开一份需要进行审核的Excel表格，选中需要审核的区域，切换到“数据”功能区，单击数据有效性

15、按钮，弹出数据有效性窗口，切换到“设置”选项卡，打开“允许”下拉框，选择“小数”，打开“数据”下拉框，选择“介于”，最小值设为0，最大值设为100，单击“确定”按钮（如图5）。 Tobacco Yonyou Software Co.,Ltd.用用Excel 2010数据有效性拒绝错误数据数据有效性拒绝错误数据设置好数据有效性规则后，单击“数据”功能区，数据有效性按钮右侧的“”，从下拉菜单中选择“圈释无效数据”，表格中所有无效数据被一个红色的椭圆形圈释出来，错误数据一目了然。以上我们通过两个实例讲解了Excel 2010数据有效性的妙用。其实，这只是冰山一角，数据有效性的还有很多其它方面的应用，

16、有待大家在实际使用过程中去发掘。 Tobacco Yonyou Software Co.,Ltd.EXCEL公式运用公式运用(FIND,ISERROR,NOT,IF)要想在某一单元格中寻找是否包含某一个字符或字符串的函数。如：A1 B1abdcdikf要在B1中输入公式得到如果A1中含有cd字符串，则B1值为true，不含则为false。可以用公式B1=NOT(ISERROR(FIND(cd,A1)。或者B1值为1或0，则可用公式B1=IF(ISERROR(FIND(cd,A1),0,1)。见下图： Tobacco Yonyou Software Co.,Ltd.让不同类型数据用不同颜色显示让

17、不同类型数据用不同颜色显示 在工资表中，如果想让大于等于在工资表中，如果想让大于等于20002000元的工资总额以元的工资总额以“红色红色”显显示，大于等于示，大于等于15001500元的工资总额以元的工资总额以“蓝色蓝色”显示，低于显示，低于10001000元的工资元的工资总额以总额以“棕色棕色”显示，其它以显示，其它以“黑色黑色”显示，我们可以这样设置。显示，我们可以这样设置。 1.1.打开打开“工资表工资表”工作簿，选中工作簿，选中“工资总额工资总额”所在列，执行所在列，执行“格格式式条件格式条件格式”命令，打开命令，打开“条件格式条件格式”对话框。单击第二个方框右对话框。单击第二个方框

18、右侧的下拉按钮，选中侧的下拉按钮，选中“大于或等于大于或等于”选项，在后面的方框中输入数值选项，在后面的方框中输入数值“2000”2000”。单击。单击“格式格式”按钮，打开按钮，打开“单元格格式单元格格式”对话框，将对话框，将“字字体体”的的“颜色颜色”设置为设置为“红色红色”。 2.2.按按“添加添加”按钮，并仿照上面的操作设置好其它条件按钮，并仿照上面的操作设置好其它条件( (大于等于大于等于15001500，字体设置为，字体设置为“蓝色蓝色”；小于；小于10001000，字体设置为，字体设置为“棕色棕色”) )。 3.3.设置完成后，按下设置完成后，按下“确定确定”按钮。按钮。 看看工

19、资表吧，工资总额的数据是不是按你的要求以不同颜色显看看工资表吧，工资总额的数据是不是按你的要求以不同颜色显示出来了。示出来了。 Tobacco Yonyou Software Co.,Ltd.建立分类下拉列表填充项建立分类下拉列表填充项我们常常要将企业的名称输入到表格中，为了保持名称的一致性，利用我们常常要将企业的名称输入到表格中，为了保持名称的一致性，利用“数据有数据有效性效性”功能建了一个分类下拉列表填充项。功能建了一个分类下拉列表填充项。 1.1.在在Sheet2Sheet2中，将企业名称按类别中，将企业名称按类别( (如如“工业企业工业企业”、“商业企业商业企业”、“个个体企业体企业”

20、等等) )分别输入不同列中，建立一个企业名称数据库。分别输入不同列中，建立一个企业名称数据库。 2.2.选中选中A A列列(“(“工业企业工业企业”名称所在列名称所在列) )，在，在“名称名称”栏内，输入栏内，输入“工业企业工业企业”字符后，按字符后，按“回车回车”键进行确认。键进行确认。 仿照上面的操作，将仿照上面的操作，将B B、CC列分别命名为列分别命名为“商业企业商业企业”、“个体企个体企业业” 3.3.切换到切换到Sheet1Sheet1中，选中需要输入中，选中需要输入“企业类别企业类别”的列的列( (如如C C列列) )，执行，执行“数据数据有效性有效性”命令，打开命令，打开“数据

21、有效性数据有效性”对话框。在对话框。在“设置设置”标签中，单击标签中，单击“允许允许”右侧的下拉按钮，选中右侧的下拉按钮，选中“序列序列”选项，在下面的选项，在下面的“来源来源”方框中，输入方框中，输入“工业企工业企业业”，“商业企业商业企业”，“个体企业个体企业”序列序列( (各元素之间用英文逗号隔开各元素之间用英文逗号隔开) )，确，确定退出。定退出。 再选中需要输入企业名称的列再选中需要输入企业名称的列( (如如D D列列) )，再打开，再打开“数据有效性数据有效性”对话框，选对话框，选中中“序列序列”选项后，在选项后，在“来源来源”方框中输入公式：方框中输入公式：=INDIRECT(C

22、1)=INDIRECT(C1)，确定退出。，确定退出。 4.4.选中选中C C列任意单元格列任意单元格( (如如C4)C4)，单击右侧下拉按钮，选择相应的，单击右侧下拉按钮，选择相应的“企业类别企业类别”填入单元格中。然后选中该单元格对应的填入单元格中。然后选中该单元格对应的D D列单元格列单元格( (如如D4)D4)，单击下拉按钮，即，单击下拉按钮，即可从相应类别的企业名称列表中选择需要的企业名称填入该单元格中。可从相应类别的企业名称列表中选择需要的企业名称填入该单元格中。 提示：在以后打印报表时，如果不需要打印提示：在以后打印报表时，如果不需要打印“企业类别企业类别”列，可以选中该列，列，

23、可以选中该列，右击鼠标，选右击鼠标，选“隐藏隐藏”选项，将该列隐藏起来即可。选项，将该列隐藏起来即可。 Tobacco Yonyou Software Co.,Ltd.建立建立“常用文档常用文档”新菜单新菜单 在菜单栏上新建一个在菜单栏上新建一个“常用文档常用文档”菜单，将常用的工作簿文档添加到其中，菜单，将常用的工作簿文档添加到其中，方便随时调用。方便随时调用。 1.1.在工具栏空白处右击鼠标，选在工具栏空白处右击鼠标，选“自定义自定义”选项，打开选项，打开“自定义自定义”对话框。对话框。在在“命令命令”标签中，选中标签中，选中“类别类别”下的下的“新菜单新菜单”项，再将项，再将“命令命令”

24、下面的下面的“新新菜单菜单”拖到菜单栏。拖到菜单栏。 按按“更改所选内容更改所选内容”按钮，在弹出菜单的按钮，在弹出菜单的“命名命名”框中输入一个名称框中输入一个名称( (如如“常常用文档用文档”) )。 2.2.再在再在“类别类别”下面任选一项下面任选一项( (如如“插入插入”选项选项) )，在右边，在右边“命令命令”下面任选下面任选一项一项( (如如“超链接超链接”选项选项) )，将它拖到新菜单，将它拖到新菜单( (常用文档常用文档) )中，并仿照上面的操作对中，并仿照上面的操作对它进行命名它进行命名( (如如“工资表工资表”等等) )，建立第一个工作簿文档列表名称。，建立第一个工作簿文档

25、列表名称。 重复上面的操作，多添加几个文档列表名称。重复上面的操作，多添加几个文档列表名称。 3.3.选中选中“常用文档常用文档”菜单中某个菜单项菜单中某个菜单项( (如如“工资表工资表”等等) )，右击鼠标，在弹，右击鼠标，在弹出的快捷菜单中，选出的快捷菜单中，选“分配超链接分配超链接打开打开”选项，打开选项，打开“分配超链接分配超链接”对话框。对话框。通过按通过按“查找范围查找范围”右侧的下拉按钮，定位到相应的工作簿右侧的下拉按钮，定位到相应的工作簿( (如如“工资工资.xls”.xls”等等) )文件夹，并选中该工作簿文档。文件夹，并选中该工作簿文档。 重复上面的操作，将菜单项和与它对应

26、的工作簿文档超链接起来。重复上面的操作，将菜单项和与它对应的工作簿文档超链接起来。 4.4.以后需要打开以后需要打开“常用文档常用文档”菜单中的某个工作簿文档时，只要展开菜单中的某个工作簿文档时，只要展开“常用常用文档文档”菜单，单击其中的相应选项即可。菜单，单击其中的相应选项即可。 提示：尽管我们将提示：尽管我们将“超链接超链接”选项拖到了选项拖到了“常用文档常用文档”菜单中，但并不影响菜单中，但并不影响“插入插入”菜单中菜单中“超链接超链接”菜单项和菜单项和“常用常用”工具栏上的工具栏上的“插入超链接插入超链接”按钮的按钮的功能。功能。 Tobacco Yonyou Software Co

27、.,Ltd.让数据按需排序让数据按需排序 如果你要将员工按其所在的部门进行排序，这些部门名称的有关信息如果你要将员工按其所在的部门进行排序，这些部门名称的有关信息不是按拼音顺序，也不是按笔画顺序，怎么办不是按拼音顺序，也不是按笔画顺序，怎么办? ?可采用自定义序列来排序。可采用自定义序列来排序。 1.1.执行执行“格式格式选项选项”命令，打开命令，打开“选项选项”对话框，进入对话框，进入“自定义序自定义序列列”标签中，在标签中，在“输入序列输入序列”下面的方框中输入部门排序的序列下面的方框中输入部门排序的序列( (如如“机关机关, ,车队车队, ,一车间一车间, ,二车间二车间, ,三车间三车

28、间”等等) )，单击，单击“添加添加”和和“确定确定”按钮退出。按钮退出。 2. 2.选中选中“部门部门”列中任意一个单元格，执行列中任意一个单元格，执行“数据数据排序排序”命令，打命令，打开开“排序排序”对话框，单击对话框，单击“选项选项”按钮，弹出按钮，弹出“排序选项排序选项”对话框，按其对话框，按其中的下拉按钮，选中刚才自定义的序列，按两次中的下拉按钮，选中刚才自定义的序列，按两次“确定确定”按钮返回，所有按钮返回，所有数据就按要求进行了排序。数据就按要求进行了排序。 Tobacco Yonyou Software Co.,Ltd.让中、英文输入法智能化地出现让中、英文输入法智能化地出现

29、 在编辑表格时，有的单元格中要输入英文，有的单元格中要输入中文，反复在编辑表格时，有的单元格中要输入英文，有的单元格中要输入中文，反复切换输入法实在不方便，何不设置一下，让输入法智能化地调整呢切换输入法实在不方便，何不设置一下，让输入法智能化地调整呢? ? 选中需要输入中文的单元格区域，执行选中需要输入中文的单元格区域，执行“数据数据有效性有效性”命令，打开命令，打开“数据数据有效性有效性”对话框，切换到对话框，切换到“输入法模式输入法模式”标签下，按标签下，按“模式模式”右侧的下拉按钮，右侧的下拉按钮，选中选中“打开打开”选项后，选项后，“确定确定”退出。退出。 以后当选中需要输入中文的单元

30、格区域中任意一个单元格时，中文输入法以后当选中需要输入中文的单元格区域中任意一个单元格时，中文输入法( (输入法列表中的第输入法列表中的第1 1个中文输入法个中文输入法) )自动打开，当选中其它单元格时，中文输入自动打开，当选中其它单元格时，中文输入法自动关闭。法自动关闭。 Tobacco Yonyou Software Co.,Ltd.在在Excel中自定义函数中自定义函数 Excel Excel函数虽然丰富，但并不能满足我们的所有需要。我们可以自定义一个函函数虽然丰富，但并不能满足我们的所有需要。我们可以自定义一个函数，来完成一些特定的运算。下面，我们就来自定义一个计算梯形面积的函数：数，

31、来完成一些特定的运算。下面，我们就来自定义一个计算梯形面积的函数： 1.1.执行执行“工具（视图）工具（视图）宏宏Visual BasicVisual Basic编辑器编辑器”菜单命令菜单命令( (或按或按“Alt+F11”Alt+F11”快捷键快捷键) )，打开，打开Visual BasicVisual Basic编辑窗口。编辑窗口。 2.2.在窗口中，执行在窗口中，执行“插入插入模块模块”菜单命令，插入一个新的模块菜单命令，插入一个新的模块模块模块1 1。 3.3.在右边的在右边的“代码窗口代码窗口”中输入以下代码：中输入以下代码： Function S(a, b, h)Function

32、S(a, b, h)S = h S = h * * (a + b) / 2 (a + b) / 2End Function End Function 4.4.关闭窗口，自定义函数完成。关闭窗口，自定义函数完成。 以后可以像使用内置函数一样使用自定义函数。以后可以像使用内置函数一样使用自定义函数。 提示：用上面方法自定义的函数通常只能在相应的工作簿中使用。提示：用上面方法自定义的函数通常只能在相应的工作簿中使用。 Tobacco Yonyou Software Co.,Ltd.表头下面衬张图片表头下面衬张图片 为工作表添加的背景，是衬在整个工作表下面的，能不能只衬在表头下面为工作表添加的背景，是

33、衬在整个工作表下面的，能不能只衬在表头下面呢呢? ? 1.1.执行执行“格式格式工作表工作表背景背景” 命令，打开命令，打开“工作表背景工作表背景”对话框，选中需要对话框，选中需要作为背景的图片后，按下作为背景的图片后，按下“插入插入”按钮，将图片衬于整个工作表下面。按钮，将图片衬于整个工作表下面。 ( (页面页面布局布局- -背景背景- -来自文件来自文件) ) 2.2.在按住在按住CtrlCtrl键的同时，用鼠标在不需要衬图片的单元格键的同时，用鼠标在不需要衬图片的单元格( (区域区域) )中拖拉，同时中拖拉，同时选中这些单元格选中这些单元格( (区域区域) )。 3.3.按按“格式格式”

34、工具栏上的工具栏上的“填充颜色填充颜色”右侧的下拉按钮，在随后出现的右侧的下拉按钮，在随后出现的“调色调色板板”中，选中中，选中“白色白色”。经过这样的设置以后，留下的单元格下面衬上了图片，。经过这样的设置以后，留下的单元格下面衬上了图片，而上述选中的单元格而上述选中的单元格( (区域区域) )下面就没有衬图片了下面就没有衬图片了( (其实，是图片被其实，是图片被“白色白色”遮盖遮盖了了) )。 提示：衬在单元格下面的图片是不支持打印的。提示：衬在单元格下面的图片是不支持打印的。 Tobacco Yonyou Software Co.,Ltd.用连字符用连字符“&”来合并文本来合并文本

35、 如果我们想将多列的内容合并到一列中，不需要利用函数，一个小小的连字符如果我们想将多列的内容合并到一列中，不需要利用函数，一个小小的连字符“&”&”就能将它搞定就能将它搞定( (此处假定将此处假定将B B、C C、D D列合并到一列中列合并到一列中) )。 1.1.在在D D列后面插入两个空列列后面插入两个空列(E(E、F F列列) )，然后在，然后在D1D1单元格中输入公式：单元格中输入公式：=B1&C1&D1=B1&C1&D1。 2.2.再次选中再次选中D1D1单元格，用单元格，用“填充柄填充柄”将上述公式复制到将上述公式复制到D D列下面的

36、单元格中，列下面的单元格中，B B、C C、D D列的内容即被合并到列的内容即被合并到E E列对应的单元格中。列对应的单元格中。 3.3.选中选中E E列，执行列，执行“复制复制”操作，然后选中操作，然后选中F F列，执行列，执行“编辑编辑选择性粘贴选择性粘贴”命令，命令，打开打开“选择性粘贴选择性粘贴”对话框，选中其中的对话框，选中其中的“数值数值”选项，按下选项，按下“确定确定”按钮，按钮，E E列列的内容的内容( (不是公式不是公式) )即被复制到即被复制到F F列中。列中。 4.4.将将B B、C C、D D、E E列删除，完成合并工作。列删除，完成合并工作。 提示：完成第提示：完成第

37、1 1、2 2步的操作，合并效果已经实现，但此时如果删除步的操作，合并效果已经实现，但此时如果删除B B、C C、D D列，公列，公式会出现错误。故须进行第式会出现错误。故须进行第3 3步操作，将公式转换为不变的步操作，将公式转换为不变的“值值”。 Tobacco Yonyou Software Co.,Ltd.EXCEL文本连接函数文本连接函数函数为函数为CONCATENATE (text1,text2,.) Text1, text2, . 为为 2 到到 255 个将要合并成单个文本项的文本项。这些个将要合并成单个文本项的文本项。这些文本项可以为文本字符串、数字或对单个单元格的引用。文本项

38、可以为文本字符串、数字或对单个单元格的引用。 Tobacco Yonyou Software Co.,Ltd.同时查看不同工作表中多个单元格内的数据同时查看不同工作表中多个单元格内的数据有时，我们编辑某个工作表有时，我们编辑某个工作表(Sheet1)(Sheet1)时，需要查看其它工作表中时，需要查看其它工作表中(Sheet2(Sheet2、Sheet3)Sheet3)某个单元格的内容，可以利用某个单元格的内容，可以利用ExcelExcel的的“监视窗口监视窗口”功能来实现。功能来实现。 执行执行“视图视图工具栏工具栏监视窗口监视窗口”( (公式公式- -监视窗口监视窗口) )命令，打开命令，

39、打开“监视窗监视窗口口”，单击其中的，单击其中的“添加监视添加监视”按钮，展开按钮，展开“添加监视点添加监视点”对话框，用鼠标选对话框，用鼠标选中需要查看的单元格后，再单击中需要查看的单元格后，再单击“添加添加”按钮。重复前述操作，添加其它按钮。重复前述操作，添加其它“监监视点视点”。 以后，无论在哪个工作表中，只要打开以后，无论在哪个工作表中，只要打开“监视窗口监视窗口”，即可查看所有被监，即可查看所有被监视点单元格内的数据和相关信息。视点单元格内的数据和相关信息。 Tobacco Yonyou Software Co.,Ltd.控制特定单元格输入文本的长度控制特定单元格输入文本的长度 你能

40、想象当你在该输入四位数的单元格中却填入了一个两位数，或者在该输入文你能想象当你在该输入四位数的单元格中却填入了一个两位数，或者在该输入文字的单元格中你却输入了数字的时候，字的单元格中你却输入了数字的时候，ExcelExcel就能自动判断、即时分析并弹出警就能自动判断、即时分析并弹出警告，那该多好啊告，那该多好啊! !要实现这一功能，对要实现这一功能，对ExcelExcel来说，也并不难。来说，也并不难。 例如我们将光标定位到一个登记例如我们将光标定位到一个登记“年份年份”的单元格中，为了输入的统一和计的单元格中，为了输入的统一和计算的方便，我们希望算的方便，我们希望“年份年份”都用一个四位数来

41、表示。所以，我们可以单击都用一个四位数来表示。所以，我们可以单击“数数据据”菜单的菜单的“有效性有效性”选项。在选项。在“设置设置”卡片卡片“有效性条件有效性条件”的的“允许允许”下拉菜下拉菜单中选择单中选择“文本长度文本长度”。然后在。然后在“数据数据”下拉菜单中选择下拉菜单中选择“等于等于”，且，且“长度长度”为为“4”4”。同时，我们再来到。同时，我们再来到“出错警告出错警告”卡片中，将卡片中，将“输入无效数据时显示的输入无效数据时显示的出错警告出错警告”设为设为“停止停止”，并在，并在“标题标题”和和“错误信息错误信息”栏中分别填入栏中分别填入“输入文输入文本非法本非法!”!”和和“请

42、输入四位数年份。请输入四位数年份。”字样。字样。 很显然，当如果有人在该单元格中输入的不是一个四位数时，很显然，当如果有人在该单元格中输入的不是一个四位数时，ExcelExcel就会弹就会弹出示的警告对话框，告诉你出错原因，并直到你输入了正确出示的警告对话框，告诉你出错原因，并直到你输入了正确“样式样式”的数值后方的数值后方可继续录入。神奇吧可继续录入。神奇吧? ?其实，在其实，在ExcelExcel的的“数据有效性数据有效性”判断中，还有许多特殊判断中，还有许多特殊类型的数据格式可选，比如类型的数据格式可选，比如“文本类型文本类型”啊，啊，“序列大小序列大小”啊，啊，“时间远近时间远近”啊，

43、啊，如你有兴趣，何不自作主张，自己设计一种检测标准，让你的如你有兴趣，何不自作主张，自己设计一种检测标准，让你的ExcelExcel展示出与众展示出与众不同的光彩呢。不同的光彩呢。（2013 2013 数据数据- -数据验证）数据验证） Tobacco Yonyou Software Co.,Ltd.成组填充多张表格的固定单元格成组填充多张表格的固定单元格 我们知道每次打开我们知道每次打开ExcelExcel，软件总是默认打开多张工作表。由此就可看出，软件总是默认打开多张工作表。由此就可看出ExcelExcel除除了拥有强大的单张表格的处理能力，更适合在多张相互关联的表格中协调工作。了拥有强大

44、的单张表格的处理能力，更适合在多张相互关联的表格中协调工作。要协调关联，当然首先就需要同步输入。因此，在很多情况下，都会需要同时在要协调关联，当然首先就需要同步输入。因此，在很多情况下，都会需要同时在多张表格的相同单元格中输入同样的内容。多张表格的相同单元格中输入同样的内容。 那么如何对表格进行成组编辑呢那么如何对表格进行成组编辑呢? ?首先我们单击第一个工作表的标签名首先我们单击第一个工作表的标签名“Sheet1”Sheet1”，然后按住，然后按住ShiftShift键，单击最后一张表格的标签名键，单击最后一张表格的标签名“Sheet3”(Sheet3”(如果我如果我们想关联的表格不在一起，

45、可以按住们想关联的表格不在一起，可以按住CtrlCtrl键进行点选键进行点选) )。此时，我们看到。此时，我们看到ExcelExcel的的标题栏上的名称出现了标题栏上的名称出现了“工作组工作组”字样，我们就可以进行对工作组的编辑工作了。字样，我们就可以进行对工作组的编辑工作了。在需要一次输入多张表格内容的单元格中随便写点什么，我们发现，在需要一次输入多张表格内容的单元格中随便写点什么，我们发现，“工作组工作组”中所有表格的同一位置都显示出相应内容了。中所有表格的同一位置都显示出相应内容了。 但是，仅仅同步输入是远远不够的。比如，我们需要将多张表格中相同位置但是，仅仅同步输入是远远不够的。比如，

46、我们需要将多张表格中相同位置的数据统一改变格式该怎么办呢的数据统一改变格式该怎么办呢? ?首先，我们得改变第一张表格的数据格式，再首先，我们得改变第一张表格的数据格式，再单击单击“编辑编辑”菜单的菜单的“填充填充”选项，然后在其子菜单中选择选项，然后在其子菜单中选择“至同组工作表至同组工作表”。这时，这时，ExcelExcel会弹出会弹出“填充成组工作表填充成组工作表”的对话框，在这里我们选择的对话框，在这里我们选择“格式格式”一一项，点项，点“确定确定”后，同组中所有表格该位置的数据格式都改变了。后，同组中所有表格该位置的数据格式都改变了。 Tobacco Yonyou Software C

47、o.,Ltd.用特殊符号补齐位数用特殊符号补齐位数和财务打过交道的人都知道，在账面填充时有一种约定俗成的和财务打过交道的人都知道，在账面填充时有一种约定俗成的“安全填写法安全填写法”，那，那就是将金额中的空位补齐，或者在款项数据的前面加上就是将金额中的空位补齐，或者在款项数据的前面加上“$”$”之类的符号。其实，之类的符号。其实，在在ExcelExcel中也有类似的输入方法，那就是中也有类似的输入方法，那就是“REPT”REPT”函数。它的基本格式是函数。它的基本格式是“=REPT(“=REPT(“特殊符号特殊符号”，填充位数，填充位数)”)”。 比如，我们要在中比如，我们要在中A2A2单元格

48、里的数字结尾处用单元格里的数字结尾处用“#”#”号填充至号填充至1616位，就只须将位，就只须将公式改为公式改为“=(A2&REPT(#,16-LEN(A2)”=(A2&REPT(#,16-LEN(A2)”即可；如果我们要将即可；如果我们要将A3A3单元格中的单元格中的数字从左侧用数字从左侧用“#”#”号填充至号填充至1616位，就要改为位，就要改为“=REPT(#,16-LEN(A3)&A3”=REPT(#,16-LEN(A3)&A3”；另外，如果我们想用另外，如果我们想用“#”#”号将号将A4A4中的数值从两侧填充，则需要改为中的数值从两侧填充，则需要改为“

49、=REPT(#,8-LEN(A4)/2)&A4&REPT(#)8-LEN(A4)/2)”=REPT(#,8-LEN(A4)/2)&A4&REPT(#)8-LEN(A4)/2)”；如果你还嫌不够；如果你还嫌不够专业，要在专业，要在A5A5单元格数字的顶头加上单元格数字的顶头加上“$”$”符号的话，那就改为：符号的话，那就改为：“=(TEXT(A5,$#,#0.00(&REPT(#,16-=(TEXT(A5,$#,#0.00(&REPT(#,16-LEN(TEXT(A5,$#,#0.00)”LEN(TEXT(A5,$#,#0.00)”，一定能满足你的

50、要求。，一定能满足你的要求。 Tobacco Yonyou Software Co.,Ltd.EXCEL中数字排名解决方法中数字排名解决方法如上图，根据G列的总分大小，在H列排出校名次。解决方法：=RANK(G2,$G$2:$G$17) 问题：I列需要根据G列的总分大小分别求出在班级里的排名？ Tobacco Yonyou Software Co.,Ltd.根据身份证号码计算年龄根据身份证号码计算年龄此例以此例以18位身份证号码为准位身份证号码为准 =YEAR(TODAY()VALUE(MID(A1,7,4)1+IF(OR(VALUE(MID(A1,11,2)MONTH(TODAY(),AND

51、(VALUE(MID(A1,11,2)=MONTH(TODAY(),VALUE(MID(A1,13,2)=DAY(TODAY(),1,0) Tobacco Yonyou Software Co.,Ltd.根据身份证号码计算出性别根据身份证号码计算出性别 此例无论此例无论18位或位或15位身份证号码均适用位身份证号码均适用 =IF(LEN(A1)=18,IF(MID(A1,17,1)/2=INT(MID(A1,17,1)/2),女女,男男),IF(MID(A1,15,1)/2=INT(MID(A1,15,1)/2),女女,男男) 身份证号码倒数第身份证号码倒数第2位如果为偶数则表示女性，为奇数则

52、表示男性位如果为偶数则表示女性，为奇数则表示男性 公式解释：首先判断表达式公式解释：首先判断表达式LEN(H2)=18是否成立，如果成立，说是否成立，如果成立，说明此人的身份证号码是明此人的身份证号码是18位数，则此公式的值为位数，则此公式的值为IF(MID(H2,17,1)/2=INT(MID(H2,17,1)/2),女女,男男)，而在这个表达式，而在这个表达式中如果表达式中如果表达式MID(H2,17,1)/2=INT(MID(H2,17,1)/2)成立，则说明第成立，则说明第17位是偶数，那么此公式的值为位是偶数，那么此公式的值为“女女”，反之此公式的值就为，反之此公式的值就为“男男”。同理，如果表达式同理，如果表达式LEN(H2)=18不成立，说明此人的身份证号码是不成立，说明此人的身份证号码是15位位数，则此公式的值为数，则此公式的值为IF(MID(H2,15,1)/2=INT(MID(H2,15,1)/2),女女,男男)，而此表达式中如果，而此表达式中如果MID(H2,15,1)/2=INT(MID(H2,15,1)/2)成立，则成