Excel补充资料.docx
《Excel补充资料.docx》由会员分享，可在线阅读，更多相关《Excel补充资料.docx（23页珍藏版）》请在冰豆网上搜索。
Excel补充资料
兰州大学管理学院运筹学课
Excel补充资料
陈士成编
二0一一年
Excel补充资料
一、工作薄、工作表
工作薄≡一个文件≡多个工作表（如帐薄≡多个帐表）
１.同时打开多个Excel工作薄方法
依次点击“文件→打开”，每打开一个Excel文件，就是打开一个工作薄。
２.利用电子表格Excel在一个工作薄中制作多个工作表
默认为三个工作表，从屏幕下方的状态栏可以看到，点该栏的工作表名名，可以切换当前工作表。
点“插入”－“工作表”创建新工作表。
对于2003版本，一个工作薄最多可以有256个工作表，每个工作表最多可以有65536行，256列单元格表。
一个工作薄中无论有多少个有效的工作表，但每次只能有一个工作表是激活的（在前台，是可以操作的，称作当前工作表），工作表的激活方法是点状态栏中的标示。
但在当前工作表中随时可以调用非当前工作表中的数据（用“引用”方式）。
３.表格线与网格线
表格线：
是实线，用工具栏设置，如图１：
图１ 工具栏
网格线：
是虚线，用菜单设置。
点击--工具-选项-视图-网格线设定。
4.前景色与背景色
如图１。
二、单元格
１、选定
选择
操作
一个单元格
单击该单元格或按箭头键，移至该单元格。
单元格区域
单击该区域中的第一个单元格，然后拖至最后一个单元格，或者在按住Shift的同时按箭头键以扩展选定区域。
工作表中的所有单元格
单击“全选”按钮。
要选择整个工作表，还可以按Ctrl+A。
不相邻的单元格或单元格区域
选择第一个单元格或单元格区域，然后在按住Ctrl的同时选择其他单元格或区域。
整行或整列
单击行标题或列标题。
①行标题
② 列标题
相邻行或列
在行标题或列标题间拖动鼠标。
或者选择第一行或第一列，然后在按住Shift的同时选择最后一行或最后一列。
不相邻的行或列
单击选定区域中第一行的行标题或第一列的列标题，然后在按住Ctrl的同时单击要添加到选定区域中的其他行的行标题或其他列的列标题。
行或列中的第一个或最后一个单元格
选择行或列中的一个单元格，然后按Ctrl+箭头键（对于行，请使用向右键或向左键；对于列，请使用向上键或向下键）。
工作表中第一个或最后一个单元格
按Ctrl+Home可选择工作表或Excel列表中的第一个单元格。
按Ctrl+End可选择工作表或Excel列表中最后一个包含数据或格式设置的单元格。
工作表中最后一个单元格（右下角）的单元格区域
Ctrl+Shift+End
２、单元格的引用
单元格是用为录入、存放、显示和处理数据的地方，同时还可以给每个单元格取一个独立的名字，做为变量来使用（象宾馆里的房间），这里称作单元格的引用。
Excel中的单元格命名是按行、列号来确定的，其中所有行号都是数字（1-65536），所有列号都是用大写的英语言字母（A-IV（IV=9×26+22=256））。
（1）相对引用
“列号”+“行号”
A5：
表示第5行第一列的那个单元格。
（2）绝对引用
[$]+“列号”+[$]+“行号”
$A5：
表示第5行第一列的那个单元格。
但复制时其列号永远是1，而行号随复制的位置而变；
A$5：
表示第5行第一列的那个单元格。
但复制时其行号永远是5，而列号随复制的位置而变；
$A$5：
表示第5行第一列的那个单元格。
但复制时其行号、列号都不能再变。
根本区别：
根本区别：
$+“行号”
$+“列号”
绝对引用
常量
３、合并与拆分单元格
（1）合并单元格
根据网格线显示的表线，选中所合并的区域，点鼠标右键，弹出快捷菜单，选“设置单元格格式”，如图2：
选“合并单元格”，复选框显示标记。
图2：
设置单元格格式快捷菜单
单元格合并后，无论是表格线或网格线都被重设。
（2）拆分单元格
Excel表格处理与word的表格处理不同，它没有专门命令处理单元格的拆分，若要对单元格进行拆分，就要先解除单元格的合并（选中已合并的单元格后，在图2“合并单元格”，将复选框显示标记清除），使单元格处于原状，再重新合并。
４、字符格式
在图2的快捷菜单中，选“字体”，显示图3的对话框：
图3：
设置单元格格式的“字体”对话框
从而可以设置字体、字型、字号、下划线及上下标等。
５、行宽与列宽
分别可以在标示行或标示列修改单元格行或列的宽度。
选中标示行（可以是单个或是连续多个,被选中的标示会变为蓝色），点右键，显示快捷菜单如图4：
图4：
设置行高的快捷菜单
选“行高”，再按弹出的对话框操作。
选中标示行（可以是单个或是连续多个,被选中的标示会变为蓝色），点右键，显示快捷菜单如图4：
图5：
设置列宽的快捷菜单
选“列宽”，再按弹出的对话框操作。
6、插入行、列
插入行、列时分别按图4或图5的快捷菜单中的“插入”选项。
则实现已选标示中的行数或列数，在其末端插入相应的空的行数或列数。
三、表达式
1、数据类型
我们最常用的数据类型有两种：
数值型和字符型。
默认类型为所有数值都是数值型，所有字符都是字符型，但对于数值，可以转换为字符型（用函数）。
数字可以设置其格式，如图3，选“数字”，就弹出对话框图6：
图6：
设置数字格式对话框
2、单元格变量
单元格既可以作为存储数据的空间，可以将单元格地址作为变量来使用。
变量是用于临时保存数据的地方，可以随时更换变量中的数据（称做“赋值”），对变量中数据的处理（如参加运算）都是只对“变量名”来操作的。
如单元格变量而言，对单元格中数据的处理，只用对单元格变量的地址处理就行。
3、函数
函数就一段封装好的程序段，用于实现一个完整而固定的功能。
函数的表述形式：
每个函数都有一个函数名，而输入数据都是放在函数名后面的括弧里。
函数放在什么地方，其函数值（结果）就显示在什么地方。
函数的特点是都必须要有一组（可以是一个或是多个）初始的输入数据，其结果是一组（一个或多个）输出数据。
Excel-2003中的函数集有672个。
这里介绍15组最常用的函数，很难说其中的哪一个函数就绝对最常用，但这么多年来人们的经验总结，一些函数总是会重复出现的。
这些函数是最基本的，但应用面却非常广。
学会这些基本函数可以让使用者的工作事半功倍。
（1）SUM
加法。
是最基本的数学运算之一。
SUM最多可以拥有30个参数（若多于30个，则只计算前30个参数的和），其中的参数可以是单个数字也可以是一组数字（组合参数如B3：
F5）。
因此SUM的加法运算功能十分强大。
语法结构：
=sum（number1,number2,…）
功能一：
统计一个单元格区域：
=sum（A1:
A12）
功能二：
统计多个单元格区域：
=sum（A1:
A12,B1:
B12）
（2）AVERAGE
虽然Average是一个统计平均数的函数，。
与求和函数一样，至多可以拥有30个参数，参数可以是数字，或者单元格区域。
语法结构：
=AVERAGE（number1,number2,…）
功能一：
使用一个单元格区域：
=AVERAGE（A1:
A12）
功能二：
使用多个单元格区域：
=AVERAGE（A1:
A12,B1:
B12）
（3）COUNT
COUNT函数用于统计含有数字的单元格的个数。
需要特别注意的是：
COUNT函数不会将数字相加，而只是统计总共有多少个单元格（数字）。
如选中的区域中含有10个数字的列表，则COUNT函数返回的结果是10，不管这些数字的实际总和是多少。
COUNT函数也可以添加至多30个参数，这些参数可以是单元格、单元格引用，甚或数字本身。
COUNT函数会忽略非数字的值。
例如，如果A1:
A10是COUNT函数的参数，但是其中只有两个单元格含有数字，那么COUNT函数返回的值是2。
语法结构：
=COUNT（cellreference1,cellreference2,…）
功能一：
使用单元格区域作为参数
=COUNT（A1:
A12）
功能二：
多个单元格区域
=COUNT（A1:
A12,B1:
B12）
（4）INT和ROUND
INT函数和ROUND函数都是将一个数字的小数部分删除，两者的区别是如何删除小数部分。
INT函数是无条件的将小数部分删除。
也就是说INT函数总是向下舍去小数部分。
例如：
INT（12.05）=12，INT（12.95）=12。
另外，INT（-5.1）和INT（-5.9）都是等于-6，而不是-5，因为-6才是-5.1和-5.9向下舍入的数字。
使用INT函数请一定要注意这个方面。
函数只有一个参数，
语法结构：
=INT（number）
ROUND函数是将一个数字的小数部分四舍五入。
该函数有两个参数：
需要计算的数字和需要四舍五入的小数位数。
例如，5.6284可以四舍五入成5.628，5.63，5.6，或只是6。
round的英文意思就是四舍五入。
int是integer的缩略，整数的意思。
语法结构：
=ROUND（number,小数位数）
（5）IF
IF函数的作用是判断一个条件，然后根据判断的结果返回已规定的两个值之中的一个值。
条件判断的结果必须返回一个或TRUE或FALSE的值，注意这里的TRUE或FALSE不是正确和错误的意思，而是逻辑上的真与假的意思。
例如：
给出的条件是B25>C30，如果实际情况是TRUE，那么IF函数就返回第二个参数的值；如果是FALSE，则返回第三个参数的值。
IF函数还常常用来检验数学计算，避免出现不必要的错误。
最常用的是用来检验分母是否为0，然后再进行除法运算。
这样就可以避免出现#DIV/0!
的错误提示了。
IF函数其中一个伟大之处是其结果可以为空值。
如果你只希望出现一个判断结果，双引号间不输入任何值就会得出一个空值，如：
=IF（B1>B2,B2,“”）。
IF函数的语法结构：
=IF（logicaltest,valueiftrue,valueiffalse）
（6）NOW和TODAY
NOW函数根据计算机现在的系统时间返回相应的日期和时间。
TODAY函数则只返回日期。
NOW函数常用在返回文件的打印日期和时间上，应用这个函数，打印出来的文件就会显示“打印时间：
10/24/201110:
15”。
TODAY函数则常用来计算过去到“今天”总共有多少天的计算上。
例如，项目到今天总共进行多少天了？
在一个单元格上输入开始日期，另一个单元格输入公式减去TODAY得到的日期，得出的数字就是项目进行的天数。
NOW函数和TODAY函数都没有参数。
语法结构：
=NOW（）、=TODAY（）
请注意可能需要更改单元格的格式，才能正确显示所需要的日期和时间格式。
（7）HLOOKUP和VLOOKUP
函数HLOOKUP和VLOOKUP都是用来在表格中查找数据。
所谓的表格是指用户预先定义的行和列区域。
这两个函数的第一个参数是需要查找的值，如果在表格中查找到这个值，则返回一个不同的值。
具体来说，HLOOKUP返回的值与需要查找的值在同一列上，而VLOOKUP返回的值与需要查找的值在同一行上。
两个函数的语法公式是：
=HLOOKUP（lookupvalue,tablearea,row,matchtype）
A
B
C
1
Axles
Bearings
Bolts
2
4
4
9
3
5
7
10
4
6
8
11
5
公式
说明（结果）
6
=HLOOKUP（"Axles",A1:
C4,2）
在首行查找Axles，并返回同列中第2行的值。
（4）
7
=HLOOKUP（"Bearings",A1:
C4,3）
在首行查找Bearings，并返回同列中第3行的值。
（7）
8
=HLOOKUP（"B",A1:
C4,3）
在首行查找B，并返回同列中第3行的值。
由于B不是精确匹配，因此将使用小于B的最大值Axles。
（5）
9
=HLOOKUP（"Bolts",A1:
C4,4）
在首行查找Bolts，并返回同列中第4行的值。
（11）
10
=HLOOKUP（3,{1,2,3;"a","b","c";"d","e","f"},2）
在数组常量的第一行中查找3，并返回同列中第2行的值。
（c）
=VLOOKUP（lookupvalue,tableare,column,matchtype）
A
B
C
1
密度
粘度
温度
2
0.457
3.55
500
3
0.525
3.25
400
4
0.616
2.93
300
5
0.675
2.75
250
6
0.746
2.57
200
7
0.835
2.38
150
8
0.946
2.17
100
9
1.09
1.95
50
10
1.29
1.71
0
11
公式
说明（结果）
12
=VLOOKUP（1,A2:
C10,2）
使用近似匹配搜索A列中的值1，在A列中找到小于等于1的最大值0.946，然后返回同一行中B列的值。
（2.17）
13
=VLOOKUP（1,A2:
C10,3）
使用近似匹配搜索A列中的值1，在A列中找到小于等于1的最大值0.946，然后返回同一行中C列的值。
（100）
14
=VLOOKUP（.7,A2:
C10,3）
使用精确匹配在A列中搜索值0.7。
因为A列中没有精确匹配的值，所以返回一个错误值。
（#N/A）
15
=VLOOKUP（0.1,A2:
C10,2）
使用近似匹配在A列中搜索值0.1。
因为0.1小于A列中最小的值，所以返回一个错误值。
（#N/A）
A
B
C
1
货品ID
货品
成本
2
ST-340
童车
￥145.67
3
BI-567
围嘴
￥3.56
4
DI-328
尿布
￥21.45
5
WI-989
柔湿纸巾
￥5.12
6
AS-469
吸出器
￥2.56
7
公式
说明（结果）
8
=VLOOKUP（"DI-328",A2:
D6,3,FALSE）*（1+VLOOKUP（"DI-328",A2:
D6,4,FALSE））
涨幅加上成本，计算尿布的零售价。
（￥28.96）
9
=（VLOOKUP（"WI-989",A2:
D6,3,FALSE）*（1+VLOOKUP（"WI-989",A2:
D6,4,FALSE）））*（1-20%）
零售价减去指定折扣，计算柔湿纸巾的销售价格。
（￥5.73）
10
=IF（VLOOKUP（A2,A2:
D6,3,FALSE）>=20,"涨幅为"&100*VLOOKUP（A2,A2:
D6,4,FALSE）&"%","成本低于￥20.00"）
如果某一货品的成本大于或等于￥20.00，则显示字符串“涨幅为nn%”；否则，显示字符串“成本低于￥20.00”。
（涨幅为30%）
11
=IF（VLOOKUP（A3,A2:
D6,3,FALSE）>=20,"涨幅为:
"&100*VLOOKUP（A3,A2:
D6,4,FALSE）&"%","成本为￥"&VLOOKUP（A3,A2:
D6,3,FALSE））
如果某一货品的成本大于或等于￥20.00，则显示字符串“涨幅为nn%”；否则，显示字符串“成本为￥n.nn”。
（成本为￥3.56）
说明：
参数matchtype取“FALSE”是在没精确相等的值中查近似值。
取“TRUE”是在没精确相等的值时返回“#N/A”以表示查不到。
省略该参数默认为“FALSE”
（8）ISNUMBER
判断表达式参数的数据是否为数值型，如“15”是一个数字，但“十五”则是汉字。
返回TRUE或FALSE。
语法结构：
=ISNUMBER（value）
（9）MIN和MAX
函数MIN和MAX是在单元格区域中找到最大和最小的数值。
两个函数可以拥有30个参数，而参数可以是单元格或单元格区域。
语法结构：
=MAX（number1,number2,…）、=MIN（number1,number2,…）
功能一：
使用单元格区域：
=MAX（A1:
A12）
功能二、使用多个单元格区域：
=MAX（A1:
A12,B1:
B12）
（10）SUMIF和COUNTIF
SUMIF和COUNTIF函数分别根据条件汇总或计算单元格个数。
Excel的计算功能因此大大增强。
SUMIF函数有三个参数：
应用判断要求的范围；实际的判断要求；需要汇总的区域。
这里实际需要汇总的区域可以不是应用判断要求的区域。
因此除了可以用SUMIF函数回答“十月份的出货量”这样的问题外，还可以回答“列表中大于100的数的总和是多少？
”。
语法结构：
=SUMIF（range,criteria,sum_range）
SUMIF的第三个参数可以忽略。
第三个参数忽略的时候，第一个参数应用条件判断的单元格区域就会用来作为需要求和的区域。
COUNTIF函数用来计算单元格区域内符合条件的单元格个数。
如果其中一个单元格的值符合条件，则返回值是1，而不管单元格里面的值是多少。
COUNTIF函数只有两个参数：
需要计算的单元格区域，计算的条件。
语法结构：
COUNTIF（range,criteria）
（11）COLUMN、ROW
语法结构：
=COLUMN（单元格）
功能一：
显示所引用单元格的列标号值。
语法结构：
COLUMN（）
功能二显示所当前单元格的列标号值。
ROW（单元格）:
显示所引用单元格的行标号值。
ROW（）:
显示所当前单元格的行标号值。
（12）CELLS
语法结构：
=CELL（info_type,reference）
功能：
返回引用区域左上角单元格的格式、位置或内容等信息。
其中：
info_type=“type”，reference是返回格中的数据类型相对应的文本值。
如果单元格为空，则返回“b”。
如果单元格包含文本常量，则返回“l”；如果单元格包含其他内容，则返回“v”。
（13）SUMPRODUCT
语法结构：
=SUMPRODUCT（array1,[array2],[array3],...）
功能：
在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。
其中：
Array1 必需。
其相应元素需要进行相乘并求和的第一个数组参数。
Array2,array3,... 可选。
2到255个数组参数，其相应元素需要进行相乘并求和。
注意：
数组参数必须具有相同的维数，否则，函数SUMPRODUCT将返回错误值#VALUE!
。
函数SUMPRODUCT将非数值型的数组元素作为0处理。
（14）TEXT、VALUE
语法结构：
TEXT（value,format_text）
功能：
将数值转换为按指定数字格式表示的文本。
其中：
value 为数值、计算结果为数字值的公式，或对包含数字值的单元格的引用。
Format_text 为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式。
注意：
Format_text不能包含星号（*）。
通过“格式”菜单调用“单元格”命令，然后在“数字”选项卡上设置单元格的格式，只会更改单元格的格式而不会影响其中的数值。
使用函数TEXT可以将数值转换为带格式的文本，而其结果将不再作为数字参与计算。
VALUE（text）：
将代表数字的文本字符串转换成数字
注意：
Text可以是MicrosoftExcel中可识别的任意常数、日期或时间格式。
如果Text不为这些格式，则函数VALUE返回错误值#VALUE!
。
通常不需要在公式中使用函数VALUE，Excel可以自动在需要时将文本转换为数字。
提供此函数是为了与其他电子表格程序兼容。
（15）FORECAST、SLOPE、INTERCEPT
FORECAST函数
语法结构：
FORECAST（x,known_y's,known_x's）
功能：
根据已有的数值计算或预测未来值。
此预测值为基于给定的x值推导出的y值。
已知的数值为已有的x值和y值，再利用线性回归对新值进行预测。
可以使用该函数对未来销售额、库存需求或消费趋势进行预测。
其中：
X 为需要进行预测的数据点。
Known_y's 为因变量数组或数据区域。
Known_x's 为自变量数组或数据区域。
注意：
如果x为非数值型，函数FORECAST返回错误值#VALUE!
。
如果known_y's和known_x's为空或含有不同个数的数据点，函数FORECAST返回错误值#N/A。
如果known_x's的方差为零，函数FORECAST返回错误值#DIV/0!
。
函数FORECAST的计算公式为a+bx，式中：
实例：
SLOPE函数
语法结构：
SLOPE（known_y's,known_x's）
功能：
返回根据known_y's和known_x's中的数据点拟合的线性回归直线的斜率。
斜率为直线上任意两点的重直距离与水平距离的比值，也就是回归直线的变化率。
其中：
Known_y's 为数值型因变量数据点数组或单元格区域。
Known_x's 为自变量数据点集合。
注意：
参数可以是数字，或者是包含数字的名称、数组或引用。
如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。
如果known_y's和known_x's为空或其数据点个数不同，函数SLOPE返回错误值#N/A。
回归直线的斜率计算公式如下：
INTERCEPT函数
语法结构：
INTERCEPT（known_y's,known_x's）
功能：
利用现有的x值与y值计算直线与y轴的截距。
截距为穿过已知的known_x's和known_y's数据点的线性回归线与y轴的交点。
当自变量为0（零）时，使用INTERCEPT函数可以决定因变量的值。
例如，当所有的数据点都是在室温或更高的温度下取得的，可以用INTERCEPT函数预测在0°C时金属的电阻。
其中：
Known_y's 为因变的观察值或数据集合。
Known_x's 为自变的观察值或数据集合。
注意：
参数可以是数字，或者是包含数字的名称、数组或引用。
如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。
如果known_y's和known_x's所包含的数据点个数不相等或不包含任何数据点，则函数INTE
升级会员 