办公室里关于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
![](attachments/month_0508/c2005816162825.jpg)
小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
![](attachments/month_0508/d200581616271.jpg)
图4
![](attachments/month_0508/c2005816162825.jpg)
小B:“哦,我从来没有留意过这个。那你讲的这些与我现在的问题有什么关系吗?”
小F:“大大的有关系。首先我们来看一下营业日报表的格式(如图5所示),以7月1日的报表为例,如果要在汇总表中取出M商品7月1日的销售数量应该录入公式=701!G6,要取出7月2日的则应录入=702!G6,如此类推,7月31日应录入=731!G6。”
图5
![](attachments/month_0508/f2005816164143.jpg)
小B:“我明白了,我在汇总表中依次录入这些公式就得出我要的东西了,是快了点,可是仍然显慢啊。”
小F:“好戏才刚刚开始呢,别着急。你看到这些公式有什么规律吗?等于号、感叹号、G6单元格都是固定,只有工作表名是变动且递增的。一看到递增(减)数组,就应该想起Excel的一个功能-“自动填充”,它解决有规律的数组最拿手了。“
接着,小F建立一个空表,录入了如图6所示数据。
图6
![](attachments/month_0508/r2005816164433.jpg)
小F:“首先在A2单元格中输入第一天日期701,然后在A3单元格中输入=A2+1,接着将鼠标挪至A3单元格的右下角,鼠标指针将会变成十字形,这说明Excel已经做好了自动填充的准备,按住鼠标左键往下拉,则会出现701,702……731这样的数组列。自此,我们的前期工作完成了大半了。”
小B:“真棒!”
小F:“嗯,自动填充是我解决Excel问题的第一板斧。当然,我有三板斧呢。下面一个一个说给你听。”
小F:“现在我们来自动生成从7月1日至7月31日M商品销售数量的公式列表。刚才我们不是注意到了该公式是常量等号、感叹号、G6以及变量工作表名组成的吗,可不可以想个办法把它们连接起来呢?Excel中有一个连接符号&就是起着个作用的。我给你演示一下怎么做吧。”
图7
![](attachments/month_0508/l2005816164725.jpg)
小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
![](attachments/month_0508/r2005816172919.jpg)
小B:“太神奇了,那我赶紧把这列公式拷入汇总表中。为了保持公式列表从Book1.xls中粘贴到长安店.xls中的样子不变,我需要用粘贴功能中的粘贴数值功能吧?我知道直接啃嗤C+啃嗤V就会把原来的公式、单元格格式等东西全部粘贴过来,而在这里我只需要粘贴数值就好了。哎呀,你看怎么得不出我要的结果呢,还是没有数据出现?如图9所示。”
图9
![](attachments/month_0508/62005816172154.jpg)
小F:“这次要用 到Excel的粘贴数值功能没错。这样吧,你依次每个单元格按一下回车数据就自动出来了。”
小B将光标定位到B5单元格,按了一下回车键,发现已经出现了数字10,她马上又问道:“不会吧,那我得按多少个回车,有没有更简便的方法呢?”
小F:“自然有,把公式列拷入纯文本文件中,然后从文本文件拷回至Excel的B列,就可以得到你要的数据了。如图10所示。”
图10
![](attachments/month_0508/r200581617231.jpg)
小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
![](attachments/month_0508/l2005816172511.gif)
小B:“哦,那你的第三板斧应该就是替换啦。”
小F:“你还挺聪明呢。我就只用这三板斧,对解决这种从多张工作表里取数据的问题是屡试不爽的。但使用这个办法时有一个前提,那就是这些表的行数、烈数以及行列代表的内容必须是完全一样的,如果不完全一样,就有点难办,但也不是没有办法,等你碰到了我再告诉你。”
小B:“我记住了,先解决了这个问题再说吧。”
[本日志由 Leon 于 2006-02-14 07:53 PM 编辑]
![](images/Cprevious.gif)
![](images/Cnext.gif)
![](images/From.gif)
![](images/icon_trackback.gif)
![](images/tag.gif)
![](images/notify.gif)
评论: 0 | 引用: 10 | 查看次数: 15874
发表评论
你没有权限发表评论!