相见恨晚的Excel表格常用技巧.docx

相见恨晚的Excel表格常用技巧.docx

《相见恨晚的Excel表格常用技巧.docx》由会员分享，可在线阅读，更多相关《相见恨晚的Excel表格常用技巧.docx（14页珍藏版）》请在冰豆网上搜索。

相见恨晚的Excel表格常用技巧

相见恨晚的Excel表格常用技巧

1、让多页Excel表格自动打印统一表头

如果我们要打印出一个Excel工作表,而这张表格有多页,如何让第一页以后的每一页都自动加上与第一页相同的表头呢?

可以这么做:

单击“文件”菜单下的“页面设置”命令,弹出“页面设置”对话框;单击“工作表”选项卡,在“打印标题”下“顶端标题行”右边文本框中单击鼠标左键,出现闪烁光标后,在Excel表用鼠标左键选择表头所在行,最后选择“确定”即可。

这样,Excel就会自动为第一页后面的表格加上统一表头了。

2、冻结窗口（锁定Excel表头的设置要领）

当我们编辑过长或过宽的Excel工作表时,需要向下或向上滚动屏幕。

这时表头也会相应滚动,不能在屏幕上显示,于是我们搞不清要编辑的数据对应于表头的哪一个信息。

按下列方法可将表头锁定,使表头始终位于屏幕上的可视区域。

首先选定要锁定的表头,如果我们要将图中表头（1至3行）锁定,那么单击A4单元格,然后单击“窗口”菜单中的“冻结拆分窗口”命令,即可完成表头的冻结。

假如还需锁定表格左侧第一列,那么单击第一列和第三行交叉处的右下方单元格B4,以下操作步骤同前所述。

被冻结的窗口部分会以黑线与区分开来。

如果要取消表头锁定,则单击“窗口”菜单中的“撤消窗口冻结”命令。

3、Excel公式位置的引用（绝对地址和相对地址的引用）

一个引用位置代表工作表上的一个或者一组单元格,引用位置告诉Excel在哪些单元格中查找公式中要用的数值。

通过使用引用位置,我们可以在一个公式中使用工作表上不同部分的数据,也可以在几个公式中使用同一个单元格中的数值。

我们也可以引用同一个工作簿上其它工作表中的单元格,或者引用其它工作簿,也可以引用其他应用程序中的数据。

引用

其他工作簿中的单元格称为外部引用。

引用其他应用程序中的数据称为远程引用。

单元格引用位置基于工作表中的行号和列标。

3.1单元格地址的输入

在公式中输入单元格地址最准确的方法是使用单元格指针。

我们虽然可以输入一个完整的公式,但在输入过程中很可能有输入错误或者读错屏幕单元地址,例如,我们很可能将“B23”输入为“B22”。

因此,在我们将单元格指针指向正确的单元格时,实际上已经把活动的单元格地址移到公式中的相应位置了,从而也就避免了错误的发生。

在利用单元格指针输入单元格地址的时候,最得力的助手就是使用鼠标。

使用鼠标输入的过程如下:

（1）选择要输入公式的单元格,在编辑栏的输入框中输入一个等号“=”。

（2）用鼠标指向单元格地址,然后单击选中单元格地址。

（3）输入运算符号,如果输入完毕,按下“Enter”键或者单击编辑栏上的“确认”按钮。

如果没有输入完毕,则继续输入公式。

例如,我们要在单元格“B2”中输入公式“=A1+A2+C6”,则可将鼠标指向单元格“B2”,然后键入一个“=”号,接着将鼠标指向“A1”单击,再键入“+”号,重复这一过程直到将全部公式输入进去。

3.2相对地址引用

在输入公式的过程中,除非我们特别指明,Excel一般是使用相对地址来引用单元格的位置。

所谓相对地址是指:

当把一个含有单元格地址的公式拷贝到一个新的位置或者用一个公式填入一个范围时,公式中的单元格地址会随着改变。

例如在上一节中,输入的公式实际上代表了如下的含义:

将单元格“A1”中的内容放置到“B2”单元格中,然后分别和“A2”、“C6”单元格中的数字相加并把结果放回到“B2”单元格中。

使用相对引用就好像告诉一个向我们问路的人:

从现在的位置,向前再走三个路口就到了。

例如,我们将上例中的公式“=A1+A2+C6”分别拷贝到单元格“C2”、“D2”、“B3”和“B4”中。

图7-5显示了拷贝后的公式,从中看到相对引用的变化。

3.3绝对地址引用

在一般情况下,拷贝单元格地址时,是使用相对地址方式,但在某些情况下,我们不希望单元格地址变动。

在这种情况下,就必须使用绝对地址引用。

所谓绝对地址引用,就是指:

要把公式拷贝或者填入到新位置,并且使公式中的固定单元格地址保持不变。

在Excel中,是通过对单元格地址的“冻结”来达到此目的,也就是在列号和行号前面添加美元符号

“$”。

下面以图7-6来中的“b2”单元格来说明绝对地址引用。

例如,公式“=A1*A3”中的“A1”是不能改变的。

我们就必须使其变成绝对地址引用,公式改变为“=$A$1*A3”,当将公式拷贝时就不会被当作相对地址引用了,从图7-6的“C2”单元格可看到发生的变化。

3.4混合地址引用

在某些情况下,我们需要在拷贝公式时只有行保持或者只有列保持不变。

在这种情况下,就要使用混合地址引用。

所谓混合地址引用是指:

在一个单元格地址引用中,既有绝对地址引用,同时也包含有相对单元格地址引用。

例如,单元格地址“$A5”就表明保持“列”不发生变化,但“行”会随着新的拷贝位置发生变化;同理,单元格地址“A$5”表明保持“行”不发生变化,但“列”会随着新的拷贝位置发生变化。

图7-7是混合地址引用的范例。

3.5三维地址引用

前面我们学习过,MicrosoftExcel2000中文版的所有工作是以工作簿展开的。

比如,要对一年的12个月销售情况进行汇总,而这些数据是分布在12张工作表中的,要完成这些销售数据的汇总,就必须要能够读取（引用）在每张表格中的数据,这也就引出了“三维地址引用”这一新概念。

所谓三维地址引用是指:

在一本工作簿中从不同的工作表引用单元格。

三维引用的一般格式为:

工作表名!

:

单元格地址,工作表名后的“!

”是系统自动加上的。

例如我在第二张工作表的“B2”单元格输入公式“=Sheet1!

:

A1+A2”,则表明要引用工作表“Sheet1中的单元格?

B1?

和工作表Sheet2中的单元格?

B2?

相加,结果放到工作表Sheet2中的?

B2?

单元格。

利用三维地址引用,可以一次性将一本工作簿中指定的工作表的特定单元格进行汇总。

4、在一个图表中使用两种图表类型

有时候,为了更好地表达自己的观点,可能需要在一个图表中使用两种或者以上的图表类型。

在这种情况下,需要理解:

使用不同的图表类型是针对不同的数据系列而言的。

因此在具体操作时,要注意选择恰当数据系列进行图表类型的修改。

下面通过实际例子中,我们希望把“实际销售量”数据系列的图表类型修改为“折线图”。

（1）进入“组合使用图表类型”工作表,可以看到一个已经创建完成的图表,两个数据系列都使用的是柱形图表类型（图2）。

2）为了修改系列“实际销售量”的图表类型,下面我们要先选中系列“实际销售量”。

用鼠标单击任何一个代表实际销售量的棕色柱形图,可以看到四个棕色柱形图中间都出现了一个绿色小方块（图3）,同时可以在编辑窗口左上方的名称框中看到“系列?

实际…?

”（图4）,表明系列“实际销售量”已经被选中。

说明:

在图表中进行单击选择操作后,可以通过名称框查看到底选中了图表中的什么对象。

（3）在系列“实际销售量”被选中的情况下,把鼠标指针放在其中一个棕色柱形图上,单击鼠标右键,在弹出的菜单中选择“图表类型”命令（图5）,打开“图表类型”对话框。

（4）在“图表类型”对话框中选择“折线图”图表类型,单击确定后,可以看到系列“实际销售量”的图表类型变成“折线图”（图6）。

（5）这样,就实现了在一个图表中使用两种图表类型的目标。

通过这个例子,我们可以发现具体操作非常简单,关键要注意选中恰当的数据系列。

5、使用次坐标轴

有时候,一个图表中包含两个数据系列,但是它们之间的值相差很大,如果使用一个数值轴,那么另一个数据系列的差别情况可能显示得很不明显,在这种情况下,我们可以通过使用次坐标轴来改善另一个数据系列的显示情况。

（1）.进入“使用次坐标轴”工作表,可以看到系列“奖金”和系列“销售额”的数据值相差很大,所以,在使用一个数值轴的情况下,系列“奖金”的差别显示得很不明显（图7）。

（2）.下面我们决定让系列“奖金”使用次坐标轴。

用鼠标单击代表“奖金”的折线图中的红色小方块,这时三个红色小方块变成绿色,同时在“名称框”中可以看到系列“奖金”,这表示系列“奖金”已经被选中。

单击鼠标右键,在弹出的菜单中选择“数据系列格式”命令（图8）,打开“数据系列格式”对话框。

（3）进入“坐标轴”选项卡,点选“系列绘制在”区的“次坐标”单选框,单击“确定”按钮（图9）。

（4）.返回Excel编辑窗口后,可以看到图表中出现了两个坐标轴,代表销售额的原始坐标轴位于左侧,代表奖金的新的次坐标轴位于右侧。

由于对系列“奖金”使用了次坐标轴进行绘制,图表更好地显示它们之间的差异,同时,我们可以将左侧坐标轴上的销售额数值与右侧坐标轴上的销售奖金数值进行比较（图10）。

原文件下载

6、在Excel中设置字符上标和下标的方法

和Word不同,Excel的“格式”菜单中没有直接设置字体格式的命令,格式设置存在于“单元格格式”对话框中。

单元格中上下标的设置也需要在此对话框中进行,下面给大家简要介

绍一下:

（1）.选中单元格中要设置为上、下标的字符,在选中字符上单击右键,选择快捷菜单中的“设置单元格格式”命令,如图1。

（2）.这种方式打开的“单元格格式”对话框,只有“字体”一个选项卡,在其左下方的“特殊效果”区域,有“上标”、“下标”两个复选项,选择其一,如图2。

（3）.单击“确定”按钮,选中字符即变为指定的上标或下标。

注意:

对于文本字符,可按上述方法随意设置。

但对于数字型字符,如此设置上下标,并不能起作用,必须先将此单元格格式设置为文本型,或在设置上下标后（编辑状态下）在单元格字符的最前面加一个半角单引

号,这样才能完成正常设置。

此外,设置上标后数字的数值大小并不改变,例如,10的二次方,计算时不会作为“100”,仍将按“102”进行计算。

如图3。

另外,若要在Excel图表中标题和文本中设置上下标,其方法与上述相同。

比如设置符号（CaCO3）

7、在EXCEL中写说明

在EXCEL中写说明,可以这样="奖金"&C20&"元"（C20为奖励数值所在的单元格）。

如果是在同一工作表中引用呢,比如A2单元格中已用公式算出小计了,然后在A5单元格中输入“实际金额:

XXXX”,怎么能把A2单元格的小计直接引用在“实际金额:

”后面呢?

="实际金额:

"&A2&"元"

如果A2格式不符可用TEXT转换为需要的格式="实际金额:

"&text（A2,"0.00"）&"元"也可在A5单元格直接输入公式=A2设置单元格自定义格式:

"实际金额:

"0.00"元",这样A5单元格数值还可用于计算。

8、Excel数据分类汇总满足多种数据整理需求

图1所示是我们日常工作中经常接触到Excel二维数据表格,我们经常需要通过需要根据表中某列数据字段（如“工程类型”）对数据进行分类汇总。

下面我们针对三种不同的分类汇总需求,为大家介绍不同的解决方案。

这三种需求分别是:

既想分类汇总又想分类打印、不想分类打印只是想随时查看各类数据的明细和统计情况、不想打乱正常的流水式数据表格的数据顺序而是想随时查看各类数据的统计结果（此处假定将统计结果保存在另外一个工作表中）。

（图1）

需求一、既想分类汇总,又想分类打印

解决方案:

直接利用Excel内置的“分类汇总”功能来实现。

1、选中工程类型列的任意一个单元格,按一下“常用”工具栏上的“升序排序”或“降序排序”按钮,对数据进行一下排序。

注意:

使用“分类汇总”功能时,一定要按分类对象进行排序!

2、执行“数据→分类汇总”命令,打开“分类汇总”对话框（如图2）。

（图2）

3、将“分类字段”设置为“工程类型”;“汇总方式”设置为“求和”;“选定汇总项”为“面积”和“造

价”;再选中“每组数据分页”选项。

最后,确定返回。

4、分类汇总完成（图3是“综合办公”类的打印预览结果）。

（图3）

需求二、不想分类打印,只是想随时查看各类数据的明细和统计情况

解决方案:

利用Excel自身的“自动筛选”功能来实现。

1、任意选中数据表格中的某个单元格,执行“数据→筛选→自动筛选”命令,进入“自动筛选”状态。

2、分别选中F20

3、G203单元格（此处假定表格中共有200条数据）,输入公式:

=SUBTOTAL（9,F3:

F202）和=SUBTOTAL（9,G3:

G202）。

小提示:

此函数有一个特殊的功能,就是后面进行自动筛选后,被隐藏行的数据不会被统计到其中,达到分类统计的目的。

3、以后需要随时查看某类（如“经济住宅”）数据的明细和统计情况时,点击“工程类型”右侧的下拉按钮,在随后弹出的快捷菜单中（如图4）即可。

（图4）

需求三、如果我们不想打乱正常的流水式数据表格的数据顺序,而是想随时查看各类数据的统计结果（此处假定将统计结果保存在另外一个工作表中）

解决方案:

利用Excel的函数来实现。

1、切换到Sheet2工作表中,仿照图5的样式,制作好一个统计表格。

2、分别选中B

3、C3、D3单元格,输入公式:

=COUNTIF（Sheet1!

$E$3:

$E$202,A3）、=SUMIF（Sheet1!

$E$3:

$E$202,A3,Sheet1!

$F$3:

$F$202）、=SUMIF（Sheet1!

$E$3:

$E$202,A3,Sheet1!

$G$3:

$G$202）。

3、同时选中B3、C3、D3单元格,将鼠标移至D3单元格右下角成细十字线状时,按住左键向下拖拉至D10单元格,将上述公式复制至B4至D10单元格区域中。

4、选中B11单元格,输入公式:

=SUM（B3:

B10）,并仿照上面的操作,将此公式复制到C11和D11单元格中。

确认以后,各项统计数据即刻呈现在我们的面前（如图5）。

（图5）

9、如何只拷贝分类汇总后的数据至另一表单

在EXCEL里分类汇总后显示出来的汇总数据,如何把汇总后的数据复制到其他的EXCEL表里,不要详细数据,只要汇总后的,现在一复制就把里面的详细的内容复制出来了答:

点2级,选中区域,ALT+“;”（或是定位可见单元格:

编辑\定位\定位条件\选中可见单元格）,复制,粘贴.

10、如何在AutoCAD中导入Excel表格文件

1.选中excel中的表格,表格的边框要用细线,复制。

2.在cad中在编辑中的选择性粘贴中选autocad图元。

3.选择插入点,你会发现插入的表格线没有对齐,用反选选中全部的竖线（有很多横线也被选进来了,下一步我们将去选他们）,按住shift正选表格,注意不要把竖线全部包在里面,那样竖线也要被你去选了,现在只剩下竖线了,move他门对齐,trim到左上角那根长出去的线,表格画好了,而且和你手动一根一根画线,再填数字的一模一样。

4.现在还不完美,因为字的大小和你的图没有统一,现在我们来解决这个问题。

选择表格中的一个文字,看一下他的高度,用windows自带的计算器计算一下它和你想要的字的比例差多少,用scale缩放的你想要的文字大小。

如果你还想设制文字的宽度系数,为了制作出和你在excel里面看到对齐方式一样的数据,那就把表格先定义为块,插入块的时候选择x方向的系数,就是文字的宽度系数。

11、VLOOKUP的用法

“Lookup”的汉语意思是“查找”,在Excel中与“Lookup”相关的函数有三个:

VLOOKUP、HLOOKUO和LOOKUP。

下面介绍VLOOKUP函数的用法。

一、功能

在表格的首列查找指定的数据,并返回指定的数据所在行中的指定列处的数据。

二、语法

标准格式:

VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）解释:

VLOOKUP（…你要检索的内容或指定单元格?

…你要检索的范围,检索到内容时返回你检索表的第几列中的内容?

…真或假参数真代表查询的表已经排序,假代表没有排序?

）例:

VLOOKUP（A2,Sheet2!

$A1:

$B10,2,FALSE）

说明:

在表SHEET2中检索当前表中A2中的内容,如果检索到,就返回表SHEET2中B2中的内容,因为B2是表SHEET2中的第二列,所以VLOOKUP的第三个参数,使用2,表示如果满足条件,就返回查询表的第二列,最后的参数FALSE表示…假?

意思是被查询的表,没有排序,这种情况下,会从被查询的表中第一行开始,一直查询到结束。

三、语法解释

VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）可以写为:

VLOOKUP（需在第一列中查找的数据,需要在其中查找数据的数据表,需返回某列值的列号,逻辑值True或False）1.Lookup_value为“需在数据表第一列中查找的数据”,可以是数值、文本字符串或引用。

2.Table_array为“需要在其中查找数据的数据表”,可以使用单元格区域或区域名称等。

⑴如果range_lookup为TRUE或省略,则table_array的第一列中的数值必须按升序排列,否则,函数VLOOKUP不能返回正确的数值。

如果range_lookup为FALSE,table_array不必进行排序。

⑵Table_array的第一列中的数值可以为文本、数字或逻辑值。

若为文本时,不区分文本的大小写。

3.Col_index_num为table_array中待返回的匹配值的列序号。

Col_index_num为1时,返回table_array第一列中的数值;Col_index_num为2时,返回table_array第二列中的数值,以此类推。

如果Col_index_num小于1,函数VLOOKUP返回错误值#VALUE!

;如果Col_index_num大于table_array的列数,函数VLOOKUP返回错误值#REF!

。

4.Range_lookup为一逻辑值,指明函数VLOOKUP返回时是精确匹配还是近似匹配。

如果为TRUE或省略,则返回近似匹配值,也就是说,如果找不到精确匹配值,则返回小于lookup_value的最大数值;如果range_value为FALSE,函数VLOOKUP将返回精确

匹配值。

如果找不到,则返回错误值#N/A。

四、应用例子

ABCD

1编号姓名工资科室

22005001周杰伦2870办公室

32005002萧亚轩2750人事科

42005006郑智化2680供应科

52005010屠洪刚2980销售科

62005019孙楠2530财务科

72005036孟庭苇2200工会

A列已排序（第四个参数缺省或用TRUE）

VLOOKUP（2005001,A1:

D7,2,TRUE）等于“周杰伦”VLOOKUP（2005001,A1:

D7,3,TRUE）等于“2870”

VLOOKUP（2005001,A1:

D7,4,TRUE）等于“办公室”VLOOKUP（2005019,A1:

D7,2,TRUE）等于“孙楠”

VLOOKUP（2005036,A1:

D7,3,TRUE）等于“2200”

VLOOKUP（2005036,A1:

D7,4,TRUE）等于“工会”VLOOKUP（2005036,A1:

D7,4）等于“工会”

若A列没有排序,要得出正确的结果,第四个参数必须用FALAEVLOOKUP（2005001,A1:

D7,2,FALSE）等于“周杰伦”VLOOKUP（2005001,A1:

D7,3,FALSE）等于“2870”VLOOKUP（2005001,A1:

D7,4,FALSE）等于“办公室”VLOOKUP（2005019,A1:

D7,2,FALSE）等于“孙楠”VLOOKUP（2005036,A1:

D7,3,FALSE）等于“2200”VLOOKUP（2005036,A1:

D7,4,FALSE）等于“工会”