办公室里关于Excel的故事2-巧用三板斧解决多工作表取数问题
作者:Leon 日期:2005-08-16
这天下班后，小F到打卡钟前打完卡正准备回家，迎面碰上营运部新来的秘书小B从打印室报了厚厚一叠文件出来，于是习惯性的问了一句：“还不走啊？”
小B说：“惨了，我们经理刚才让我统计一下上月零售商品分品类的销售业绩，明天把数据交给他。我查了一下公司的信息系统，暂时没有这项功能，跑到信息部他们也不管。我只好打出所有分店上月的日报表，再一天天手工录入到Excel里统计一下了。今天怕是干上一通宵也完不了呀。”
小F：“你们营运部的日报表我天天都看，整理一个月的数据哪里需要这么长时间呀？就算一年的数据也不要一通宵啊。”
小B：“难道你有好办法？你帮我搞定了我请你吃饭。”
小F：“小Case啦。我的思路和你的思路一样，只不过我用Excel来自动取出数据，而你呢则是手工录入，自然速度是你的1000倍。”
小F：“首先，我要向你介绍一下Excel是如何引用数据的。如果要引用本工作表里的某个数据，格式是等于号+列号+行号，如图3所示。如果要引用非本工作表数据，格式是等于号+工作表名+英文感叹号+列号+行号，如图4所示。要注意的是，如果不输入单引号’，直接输入=710!D15，Excel照样也能取出正确的数据。”
图3
图4
小B：“哦，我从来没有留意过这个。那你讲的这些与我现在的问题有什么关系吗？”
小F：“大大的有关系。首先我们来看一下营业日报表的格式（如图5所示），以7月1日的报表为例，如果要在汇总表中取出M商品7月1日的销售数量应该录入公式=701!G6，要取出7月2日的则应录入=702!G6，如此类推，7月31日应录入=731!G6。”
图5
小B：“我明白了，我在汇总表中依次录入这些公式就得出我要的东西了，是快了点，可是仍然显慢啊。”
小F：“好戏才刚刚开始呢，别着急。你看到这些公式有什么规律吗？等于号、感叹号、G6单元格都是固定，只有工作表名是变动且递增的。一看到递增（减）数组，就应该想起Excel的一个功能－“自动填充”，它解决有规律的数组最拿手了。“
接着，小F建立一个空表，录入了如图6所示数据。
图6
小F：“首先在A2单元格中输入第一天日期701，然后在A3单元格中输入=A2+1，接着将鼠标挪至A3单元格的右下角，鼠标指针将会变成十字形，这说明Excel已经做好了自动填充的准备，按住鼠标左键往下拉，则会出现701，702……731这样的数组列。自此，我们的前期工作完成了大半了。”
小B：“真棒！”
小F：“嗯，自动填充是我解决Excel问题的第一板斧。当然，我有三板斧呢。下面一个一个说给你听。”
小F：“现在我们来自动生成从7月1日至7月31日M商品销售数量的公式列表。刚才我们不是注意到了该公式是常量等号、感叹号、G6以及变量工作表名组成的吗，可不可以想个办法把它们连接起来呢？Excel中有一个连接符号&就是起着个作用的。我给你演示一下怎么做吧。”
图7
小F：“如图7所示，回到刚才生成日期组的那张表，首先在A2单元格录入=，然后拷贝A2并粘贴至A3-A32单元格，请注意直接录入=后Excel会提示你继续录入数据，按回车略过即可。然后在C2中录入!G6，拷贝C2并粘贴至C3-C32单元格。你看，A2加上B2加上C2不就是=701!G6么？先输入=，然后鼠标点至A2，输入&，鼠标点至B2,再输入&，鼠标再点至C2，这样D2的公式就完成了。然后拷贝D2单元格粘贴至D32单元格，你看是不是生成了所需要的公式列表，把这列公式拷回汇总表就会得到你要的结果了。”
小B：“太神奇了。”
小F：“没错，小小的连字符&是我的第二板斧。”
小B：“不过我觉得还是有点麻烦，有没有更简单的办法？”
小F：“OK，其实不必输入图7中的A列及C列数据，如图8所示，我把所有的常量用双引号”括起来，然后与变量A列数据相连即可，生成的结果和图7中是一样的，方便多了吧。请注意常量一定是要包含在两个双引号内的，否则Excel会报错的。”
图8
小B：“太神奇了，那我赶紧把这列公式拷入汇总表中。为了保持公式列表从Book1.xls中粘贴到长安店.xls中的样子不变，我需要用粘贴功能中的粘贴数值功能吧？我知道直接啃嗤C+啃嗤V就会把原来的公式、单元格格式等东西全部粘贴过来，而在这里我只需要粘贴数值就好了。哎呀，你看怎么得不出我要的结果呢，还是没有数据出现？如图9所示。”
图9
小F：“这次要用 到Excel的粘贴数值功能没错。这样吧，你依次每个单元格按一下回车数据就自动出来了。”
小B将光标定位到B5单元格，按了一下回车键，发现已经出现了数字10，她马上又问道：“不会吧，那我得按多少个回车，有没有更简便的方法呢？”
小F：“自然有，把公式列拷入纯文本文件中，然后从文本文件拷回至Excel的B列，就可以得到你要的数据了。如图10所示。”
图10
小B：“太神奇了，那M商品的销售金额我是不是就可以用你刚才教我的自动填充来实现呢？按住鼠标左键选取B5至B35，待在B35右下角出现十字形时右拖鼠标至C列即可吧？”
小F：“没错，拖过去之后G列就变成了H列，正好是每日报表中M商品的销售金额。可是N商品怎么办呢？你再往右拖数据就不对了。”
小B：“哦，那我按照M商品的公式列表编辑方法从头再来这么一次编一下N商品的公式列表。”
小F：“你的思路没问题，可是不觉得太麻烦了吗？你应该看到了，N商品的所有公式与M商品的公式除了行号的差别外，其他的都是一样的。”
小B：“我看到了，如果能够有什么方法替换一下就好了，可是怎么替换呢？”
小F：“切换至刚才的记事本窗口，按CTRL+H调出替换功能，将所有的6替换成7。然后再将记事本中的内容拷回Excel汇总表中D列即可，如图11所示。”
图11
小B：“哦，那你的第三板斧应该就是替换啦。”
小F：“你还挺聪明呢。我就只用这三板斧，对解决这种从多张工作表里取数据的问题是屡试不爽的。但使用这个办法时有一个前提，那就是这些表的行数、烈数以及行列代表的内容必须是完全一样的，如果不完全一样，就有点难办，但也不是没有办法，等你碰到了我再告诉你。”
小B：“我记住了，先解决了这个问题再说吧。”
小B说：“惨了，我们经理刚才让我统计一下上月零售商品分品类的销售业绩，明天把数据交给他。我查了一下公司的信息系统，暂时没有这项功能，跑到信息部他们也不管。我只好打出所有分店上月的日报表，再一天天手工录入到Excel里统计一下了。今天怕是干上一通宵也完不了呀。”
小F：“你们营运部的日报表我天天都看，整理一个月的数据哪里需要这么长时间呀？就算一年的数据也不要一通宵啊。”
小B：“难道你有好办法？你帮我搞定了我请你吃饭。”
小F：“小Case啦。我的思路和你的思路一样，只不过我用Excel来自动取出数据，而你呢则是手工录入，自然速度是你的1000倍。”
小F：“首先，我要向你介绍一下Excel是如何引用数据的。如果要引用本工作表里的某个数据，格式是等于号+列号+行号，如图3所示。如果要引用非本工作表数据，格式是等于号+工作表名+英文感叹号+列号+行号，如图4所示。要注意的是，如果不输入单引号’，直接输入=710!D15，Excel照样也能取出正确的数据。”
图3
图4
小B：“哦，我从来没有留意过这个。那你讲的这些与我现在的问题有什么关系吗？”
小F：“大大的有关系。首先我们来看一下营业日报表的格式（如图5所示），以7月1日的报表为例，如果要在汇总表中取出M商品7月1日的销售数量应该录入公式=701!G6，要取出7月2日的则应录入=702!G6，如此类推，7月31日应录入=731!G6。”
图5
小B：“我明白了，我在汇总表中依次录入这些公式就得出我要的东西了，是快了点，可是仍然显慢啊。”
小F：“好戏才刚刚开始呢，别着急。你看到这些公式有什么规律吗？等于号、感叹号、G6单元格都是固定，只有工作表名是变动且递增的。一看到递增（减）数组，就应该想起Excel的一个功能－“自动填充”，它解决有规律的数组最拿手了。“
接着，小F建立一个空表，录入了如图6所示数据。
图6
小F：“首先在A2单元格中输入第一天日期701，然后在A3单元格中输入=A2+1，接着将鼠标挪至A3单元格的右下角，鼠标指针将会变成十字形，这说明Excel已经做好了自动填充的准备，按住鼠标左键往下拉，则会出现701，702……731这样的数组列。自此，我们的前期工作完成了大半了。”
小B：“真棒！”
小F：“嗯，自动填充是我解决Excel问题的第一板斧。当然，我有三板斧呢。下面一个一个说给你听。”
小F：“现在我们来自动生成从7月1日至7月31日M商品销售数量的公式列表。刚才我们不是注意到了该公式是常量等号、感叹号、G6以及变量工作表名组成的吗，可不可以想个办法把它们连接起来呢？Excel中有一个连接符号&就是起着个作用的。我给你演示一下怎么做吧。”
图7
小F：“如图7所示，回到刚才生成日期组的那张表，首先在A2单元格录入=，然后拷贝A2并粘贴至A3-A32单元格，请注意直接录入=后Excel会提示你继续录入数据，按回车略过即可。然后在C2中录入!G6，拷贝C2并粘贴至C3-C32单元格。你看，A2加上B2加上C2不就是=701!G6么？先输入=，然后鼠标点至A2，输入&，鼠标点至B2,再输入&，鼠标再点至C2，这样D2的公式就完成了。然后拷贝D2单元格粘贴至D32单元格，你看是不是生成了所需要的公式列表，把这列公式拷回汇总表就会得到你要的结果了。”
小B：“太神奇了。”
小F：“没错，小小的连字符&是我的第二板斧。”
小B：“不过我觉得还是有点麻烦，有没有更简单的办法？”
小F：“OK，其实不必输入图7中的A列及C列数据，如图8所示，我把所有的常量用双引号”括起来，然后与变量A列数据相连即可，生成的结果和图7中是一样的，方便多了吧。请注意常量一定是要包含在两个双引号内的，否则Excel会报错的。”
图8
小B：“太神奇了，那我赶紧把这列公式拷入汇总表中。为了保持公式列表从Book1.xls中粘贴到长安店.xls中的样子不变，我需要用粘贴功能中的粘贴数值功能吧？我知道直接啃嗤C+啃嗤V就会把原来的公式、单元格格式等东西全部粘贴过来，而在这里我只需要粘贴数值就好了。哎呀，你看怎么得不出我要的结果呢，还是没有数据出现？如图9所示。”
图9
小F：“这次要用 到Excel的粘贴数值功能没错。这样吧，你依次每个单元格按一下回车数据就自动出来了。”
小B将光标定位到B5单元格，按了一下回车键，发现已经出现了数字10，她马上又问道：“不会吧，那我得按多少个回车，有没有更简便的方法呢？”
小F：“自然有，把公式列拷入纯文本文件中，然后从文本文件拷回至Excel的B列，就可以得到你要的数据了。如图10所示。”
图10
小B：“太神奇了，那M商品的销售金额我是不是就可以用你刚才教我的自动填充来实现呢？按住鼠标左键选取B5至B35，待在B35右下角出现十字形时右拖鼠标至C列即可吧？”
小F：“没错，拖过去之后G列就变成了H列，正好是每日报表中M商品的销售金额。可是N商品怎么办呢？你再往右拖数据就不对了。”
小B：“哦，那我按照M商品的公式列表编辑方法从头再来这么一次编一下N商品的公式列表。”
小F：“你的思路没问题，可是不觉得太麻烦了吗？你应该看到了，N商品的所有公式与M商品的公式除了行号的差别外，其他的都是一样的。”
小B：“我看到了，如果能够有什么方法替换一下就好了，可是怎么替换呢？”
小F：“切换至刚才的记事本窗口，按CTRL+H调出替换功能，将所有的6替换成7。然后再将记事本中的内容拷回Excel汇总表中D列即可，如图11所示。”
图11
小B：“哦，那你的第三板斧应该就是替换啦。”
小F：“你还挺聪明呢。我就只用这三板斧，对解决这种从多张工作表里取数据的问题是屡试不爽的。但使用这个办法时有一个前提，那就是这些表的行数、烈数以及行列代表的内容必须是完全一样的，如果不完全一样，就有点难办，但也不是没有办法，等你碰到了我再告诉你。”
小B：“我记住了，先解决了这个问题再说吧。”
[本日志由 Leon 于 2006-02-14 07:53 PM 编辑]
上一篇: 办公室里关于Excel的故事1－Excel也是超级计算器
下一篇: 办公室里关于Excel的故事3-条件格式的妙用－编制排班表
文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags: 计算机 Excel 
相关日志:
评论: 0 | 引用: 10 | 查看次数: 15874
发表评论
你没有权限发表评论！
