excel使用技巧.docx
《excel使用技巧.docx》由会员分享，可在线阅读，更多相关《excel使用技巧.docx（42页珍藏版）》请在冰点文库上搜索。
excel使用技巧
目录
1.EXCEL实用操作-3-
1.1EXCEL常用快捷键-3-
1.2EXCEL常用函数与公式-4-
1.2.1SUM函数-4-
1.2.2SUMIF函数-5-
1.2.3SUMPRDUCT函数-7-
1.2.4POWER函数-9-
1.2.5COLUMN函数-10-
1.2.6Row函数-10-
1.2.7Vlookup函数-11-
1.2.8逻辑判断（IF、AND、OR）函数-14-
1.2.9统计数量的COUNT、COUNTA、COUNTIF函数-16-
1.2.10字符串操作的CHAR、LEET、RIGHT、MID函数-18-
1.2.11数值计算的FLOOR、INT、MOD、ROUND-20-
1.2.12求日期的DATE、YEAR、MONTH、DAY、NOW、WEEKDAY-23-
1.3EXCEL公式-24-
1.3.1名称管理器-24-
1.3.2数组公式-27-
1.3.3EXCEL公式应用实例-27-
1.4EXCEL常用操作技巧-30-
1.4.1启动Excel程序的方法-30-
1.4.2EXCEL取消隐藏列-30-
1.4.3全选工作表-30-
1.4.4按住CTRL键-30-
1.4.5在工作表中快速移动-31-
1.4.6填充柄（即选定区域右下角的小黑方块）的妙用-31-
1.4.7快速隐藏光标所在的行或列-32-
1.4.8值为0的单元格将全部显示成空白-32-
1.4.9固定表格输入内容-32-
1.5Excel其他使用技巧-34-
1.5.1如何快速选取工作表中所有包含公式的单元格-34-
1.5.2如何在不同单元格中快速输入同一数内容-34-
1.5.3只记得函数的名称，但记不清函数的参数了-34-
1.5.4如何把选定的一个或多个单元格拖放至新的位置-35-
1.5.5如何快速地复制单元格的格式-35-
1.5.6如何为表格添加斜线-35-
1.5.7如何快速地将数字作为文本输入-36-
1.5.8如何在一个与自定义函数驻留工作簿不同的工作簿内的工作表公式中调用自定义函数-36-
1.5.9如果你的工作表中已有某个序列项，想把它定义成自动填充序列以备后用-36-
1.5.10如何在公式中快速输入不连续的单元格地址-36-
1.5.11在Excel中用Average函数计算单元格的平均值的，值为0的单元格也包含在内。
有没有办法在计算平均值时排除值为0的单元格-37-
2.中低压配电网“十二五”规划表格程序使用说明-38-
2.1Excel宏安全性设置-38-
2.2使用简要说明-38-
3.地市公司配电网“十二五”规划表格程序使用说明-40-
3.1基础数据表-40-
3.2电力需求预测-40-
1.EXCEL实用操作
1.1EXCEL常用快捷键
快捷键
功能
（1）在工作表中移动
CTRL+箭头键
移动到当前数据区域的边缘
HOME
移动到行首
CTRL+HOME
移动到工作表的开头
CTRL+END
移动到工作表的最后一个单元格
CTRL+PAGEDOWN
移动到工作簿中下一个工作表
CTRL+PAGEUP
移动到工作簿中前一个工作表
F5
显示“定位”对话框
SHIFT+F5
显示“查找”对话框
SHIFT+F4
重复上一次“查找”操作
（2）预览和打印文档
CTRL+P
显示“打印”对话框
（3）工作表、图表和宏
SHIFT+F11
插入新工作表
F11或ALT+F1
创建使用当前区域的图表
ALT+F8
显示“宏”对话框
ALT+F11
显示“VisualBasic编辑器”
CTRL+F11
插入MicrosoftExcel4.0宏工作表
CTRL+PAGEDOWN
移动到工作簿中的下一个工作表
CTRL+PAGEUP
移动到工作簿中的上一个工作表
（4）在工作表中输入数据
ENTER
完成单元格输入并在选定区域中下移
ALT+ENTER
在单元格中折行
SHIFT+ENTER
完成单元格输入并在选定区域中上移
TAB
完成单元格输入并在选定区域中右移
SHIFT+TAB
完成单元格输入并在选定区域中左移
ESC
取消单元格输入
CTRL+DELETE
删除插入点到行末的文本
箭头键
向上下左右移动一个字符
F4或CTRL+Y
重复最后一次操作
SHIFT+F2
编辑单元格批注
CTRL+D
向下填充
CTRL+R
向右填充
CTRL+F3
定义名称
（5）设置数据格式
CTRL+1
显示“单元格格式”对话框
CTRL+B
应用或取消字体加粗格式
CTRL+I
应用或取消字体倾斜格式
CTRL+U
应用或取消下划线格式
CTRL+5
应用或取消删除线格式
CTRL+9
隐藏行
CTRL+SHIFT+（左括号
取消隐藏行
CTRL+0（零）
隐藏列
CTRL+SHIFT+）右括号
取消隐藏列
（6）编辑数据
F2
编辑活动单元格并将插入点放置到线条末尾
F3
将定义的名称粘贴到公式中
CTRL+SHIFT+ENTER
将公式作为数组公式输入
CTRL+A
在公式中键入函数名之后，显示公式选项板
CTRL+SHIFT+A
在公式中键入函数名后为该函数插入变量名和括号
（7）插入、删除和复制选中区域
CTRL+C
复制选定区域
CTRL+X
剪切选定区域
CTRL+V
粘贴选定区域
DELETE
清除选定区域的内容
CTRL+连字符
删除选定区域
CTRL+Z
撤消最后一次操作
CTRL+SHIFT+加号
插入空白单元格
（8）选择单元格、列或行
CTRL+SHIFT+*（星号）
选定当前单元格周围的区域
SHIFT+箭头键
将选定区域扩展一个单元格宽度
CTRL+SHIFT+箭头键
选定区域扩展到单元格同行同列的最后非空单元格
CTRL+SHIFT+HOME
将选定区域扩展到工作表的开始
CTRL+SHIFT+END
选定区域扩展到工作表的最后一个使用的单元格
CTRL+SPACEBAR
选定整列
SHIFT+SPACEBAR
选定整行
CTRL+SHIFT+SPACEBAR
选定了一个对象，选定工作表上的所有对象
CTRL+6
在隐藏对象、显示对象与对象占位符之间切换
CTRL+7
显示或隐藏“常用”工具栏
F8
使用箭头键启动扩展选中区域的功能
1.2EXCEL常用函数与公式
1.2.1SUM函数
计算所选单元格区域的所有数字之和。
语法：
SUM（number1，number2，...）
SUM的参数范围为1—255个。
示例：
例1：
=SUM（x，y，m，n，……）
这种形式适用于多种情况，如当所求数值不在一行或者一列，散布在一张表格的不同位置或在不同表格上。
如要求B2，C4，A5，D2，D4，E5的和时，
单元格=sum（B2，C4，A5，D2，D4，E5）
例2：
=sum（A1：
A100）
这种形式适用于所要求和的数值在同一行或者列上，数值较多的情况。
要计算B列自B1到B200的和，则：
表格=sum（B1：
B200）
1.2.2SUMIF函数
对区域（区域：
工作表上的两个或多个单元格。
区域中的单元格可以相邻或不相邻）中符合指定条件的值求和。
语法：
SUMIF（range，criteria，[sum_range]）
SUMIF函数语法具有以下参数（参数：
为操作、事件、方法、属性、函数或过程提供信息的值。
）：
range必需。
用于条件计算的单元格区域。
每个区域中的单元格都必须是数字或名称、数组或包含数字的引用。
空值和文本值将被忽略。
criteria必需。
用于确定对哪些单元格求和的条件，其形式可以为数字、表达式、单元格引用、文本或函数。
例如，条件可以表示为32、">32"、B5、32、"32"、"苹果"或TODAY（）。
要点：
任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号（"）括起来。
如果条件为数字，则无需使用双引号。
sum_range可选。
要求和的实际单元格（如果要对未在range参数中指定的单元格求和）。
如果sum_range参数被省略，Excel会对在range参数中指定的单元格（即应用条件的单元格）求和。
示例：
例1：
需要让负载率大于70的数值相加：
公式=SUMIF（G2:
G8，">70"）
算法：
求G2到G8之间所有大于70的数值的和，结果是143。
例2：
对表格中市委线线路所有负载率求和：
公式=SUMIF（C2:
C9，"市委线"，G2:
G8），结果是71。
算法：
求C2到C9之间所有的市委线线路负载率之和。
例３：
利用SUMIF左右求和，如下表所示
求1、2、3月的实际费用之和，公式=SUMIF（B$2:
G$2，B$2，B3:
G3）。
例４：
SUMIF还可以对多个条件进行求和，如下表所示
对上述商品中的手机和冰箱的入库数量进行求和，公式＝SUM（SUMIF（A1:
A9，{"手机"，"冰箱"}，C2:
C9）），其中SUM是对手机和冰箱的入库数量进行二次求和。
1.2.3SUMPRDUCT函数
1）计算工作表内多列中对应值相乘后的和，
语法：
SUMPRDUCT（array1，array2，array3，…）
其中，array1，array2，array3，…为2到30个数组，其相应元素需要进行相乘并求和。
注：
数组参数必须具有相同的维数，否则，函数SUMPRDUCT将返回错误值#VALUE。
该函数将非数值型的数组元素作为0处理。
示例：
计算array1=A2：
B4、array2=C2：
D4四列对应数据乘积的和。
公式=SUMPRDUCT（A2：
B4，C2：
D4）
算法：
两个数组的所有元素对应相乘，然后把乘积相加，即3*2+4*7+8*6+6*7+1*5+9*3，计算结果为156。
2）使用SUMPRDUCT进行多条件计数
语法：
SUMPRDUCT（（条件1）*（条件2）*（条件3）*…（条件n））
例1：
用SUMPRDUCT计算符合一个条件的数据个数
统计图中C列中公用的个数是多少，公式在数据统计区域中的U3单元格编辑的公式如下：
=SUMPRDUCT（（$C$3:
$C$10=T3）*1）
算法：
本公式共有一个条件，就是计算C3：
C10中公用的个数，相应的公式为：
$C$3:
$C$10=T3，当条件为一个时，需在条件后*1。
相应结果数据表如下图所示：
例2：
用SUMPRDUCT计算符合两个及以上条件的数据个数
统计图中公、专用分别是直供区、趸售区的个数。
公式：
=SUMPRDUCT（（$C$3:
$C$10=X3）*（$D$3:
$D$10=W4））
算法：
条件1为是否为公用，写成公式如下：
$C$3:
$C$10=X3；条件2为是否为直供区，写成公式如下：
$D$3:
$D$10=W4。
两个条件相乘，便是同时满足两个条件的个数，相应结果数据表如下图所示：
1.2.4POWER函数
对数字进行幂运算。
语法：
POWER（number，power）
示例：
语句POWER（D3，3）的结果是27，含义为3的三次方等于27。
1.2.5Vlookup函数
在表格或数值数组（可以理解为表格中的一个区域）的首列查找指定的数值，并由此返回表格或数组中该数值所在行中指定列处的数值。
语法：
VLOOKUP（查找值，区域，列序号，逻辑值）
“查找值”：
为需要在数组第一列中查找的数值，它可以是数值、引用或文字符串。
“区域”：
数组所在的区域，如“B2:
E10”，也可以使用对区域或区域名称的引用，例如数据库或数据清单。
“列序号”：
即希望区域（数组）中待返回的匹配值的列序号，为1时，返回第一列中的数值，为2时，返回第二列中的数值，以此类推；若列序号小于1，函数VLOOKUP返回错误值#VALUE!
；如果大于区域的列数，函数VLOOKUP返回错误值#REF!
。
“逻辑值”：
为TRUE或FALSE。
它指明函数VLOOKUP返回时是精确匹配还是近似匹配。
如果为TRUE、1或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于“查找值”的最大数值；如果“逻辑值”为FALSE或0，函数VLOOKUP将返回精确匹配值。
如果找不到，则返回错误值#N/A。
如果“查找值”为文本时，“逻辑值”一般应为FALSE。
另外：
·如果“查找值”小于“区域”第一列中的最小数值，函数VLOOKUP返回错误值#N/A。
·如果函数VLOOKUP找不到“查找值”且“逻辑值”为FALSE，函数VLOOKUP返回错误值#N/A。
示例：
首先在U3单元格输入“=Vlookup（”，此时Excel就会提示4个参数。
第一个参数，佛东II线对应的是U3，这里就输入“T3”；
第二个参数，输入要查找的区域（绝对引用），即“$B$3:
$G$10”；
第三个参数，“架空导线型号”是区域的第六列，所以这里输入“6”。
第四个参数，因为我们要精确的查找工号，所以写上FAlSE。
公式=VLOOKUP（T3，$B$3:
$G$10，6，FALSE），使用填充柄填充其他单元格即可完成查找操作。
注意事项：
1）VLOOKUP的语法：
VLOOKUP函数的完整语法是：
VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）
1．括号里有四个参数，是必需的。
最后一个参数range_lookup是个逻辑值，我们常常输入一个0字，或者False;其实也可以输入一个1字，或者true。
2．Lookup_value是一个很重要的参数，它可以是数值、文字字符串、或参照地址。
我们常常用的是参照地址。
用这个参数时，有两点要特别提醒：
A）参照地址的单元格格式类别与去搜寻的单元格格式的类别要一致，否则有时明明看到有资料，却得不到。
特别是参照地址的值是数字时，最为明显，若搜寻的单元格格式类别为文字，虽然看起来都是123，但是就是抓不出东西。
而且格式类别在未输入数据时就要先确定好，如果数据都输入进去了，发现格式不符，为时已晚，若还想去抓，则需重新输入。
B）我们在使用参照地址时，有时需要将lookup_value的值固定在一个格子内，而又要使用下拉方式（或复制）将函数添加到新的单元格中去，此时，可用“$”固定单元格。
比如始终要以D5格式来抓数据，则可以把D5锁定，即$D$5，则不论你如何拉、复制，函数始终都会以D5的值来抓数据。
3．Table_array是搜寻的范围，col_index_num是范围内的栏数。
Col_index_num不能小于1，其实等于1也没有什么实际用的。
如果出现一个这样的错误的值#REF!
，则可能是col_index_num的值超过范围的总字段数。
2）VLOOKUP的错误值处理：
如果找不到数据，函数总会传回一个这样的错误值#N/A，这错误值也很有用的。
比方说，如果我们想这样来作处理：
如果找到的话，就传回相应的值，如果找不到的话，我就自动设定它的值等于0，那函数就可以写成这样：
=if（iserror（vlookup（1，2，3，0））=true，0，vlookup（1，2，3，0））
即如果VLOOKUP函数返回的值是个错误值的话（找不到数据），就等于0，否则，就等于VLOOKUP函数返回的值（即找到的相应的值）。
注：
iserror函数。
语法是iserror（value），即判断括号内的值是否为错误值，如果是，就等于true，不是，就等于false。
if函数，语法是if（条件判断式，结果1，结果2）。
如果条件判断式是对的，就执行结果1，否则就执行结果2。
1.2.6逻辑判断（IF、AND、OR）函数
1.2.6.1IF函数
根据对指定的条件计算结果为TRUE或FALSE，返回不同的结果。
可以使用IF对数值和公式执行条件检测。
语法：
IF（logical_test（逻辑检查），value_if_true（条件成立为真时的结果），value_if_false（条件成立为假时的结果））
示例：
IF（G2>60，1，0）。
数字
算法：
如果G1大于60，则表格显示1，否则显示0。
1.2.6.2AND函数
所有参数的计算结果为TRUE时，返回TRUE；任何一个参数的计算结果为FALSE，即返回FALSE。
语法：
AND（logical1，[logical2]，...）
示例：
AND（G2>60，G3>60）。
算法：
根据上表的数据则表格的结果显示FALSE;如果语句是AND（G2>60，G3>50）则表格的结果显示TRUE。
IF与AND可一起使用，用以判断某个数值是否满足某一条件，并以自身设定的条件显示。
语法：
=IF（AND（判断条件），显示结果））
示例：
线路运行年限
运行年限
23
20年以上
7
6-10年
13
11-15年
2
0-5年
10
6-10年
12
11-15年
=IF（AND（A2<=5，A2>=0），"0-5年"，IF（AND（A2<=10，A2>5），"6-10年"，IF（AND（A2<=15，A2>10），"11-15年"，IF（AND（A2<=20，A2>15），"16-20年"，IF（（A2>20），"20年以上"）））））
1.2.6.3OR函数
在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE；所有参数的逻辑值为FALSE，即返回FALSE。
语法：
OR（logical1，logical2，...）
示例：
OR（G2>60，G3>60），根据上表的数据，表格的结果显示TRUE。
1.2.7统计数量的COUNT、COUNTA、COUNTIF函数
1.2.7.1COUNT函数
语法：
COUNT（value1，value2，...）
Value1，value2，...是包含或引用各种类型数据的参数（1～30个），但只有数字类型的数据才被计数。
函数COUNT在计数时，将把数字、空值、逻辑值、日期或以文字代表的数计算进去；但是错误值或其他无法转化成数字的文字则被忽略。
如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组中或引用的空单元格、逻辑值、文字或错误值都将忽略。
示例：
1、公式=COUNT（B1，D1），即计算B1和D1两个单元格中有几个数字（不包括C1单元格），若逗号改用冒号，则计算从B1单元格到D1单元格中数字的个数了。
2、公式=COUNT（"B1"，"D1"，"123"，"hello"），结果为0，因为加了引号后，是字符而不是数字
3、如果A1为1，A5为3，A7为2，其他均为空，则：
A:
公式=COUNT（A1:
A7）等于3
注：
计算出A1到A7中，数字的个数
B:
公式=COUNT（A1:
A7，2）等于4
注：
计算A1到A7单元格和数字2一起，一共是多少个数字（A1到A7中有3个，加上数字2，一共4个）
1.2.7.2COUNTA函数
返回参数列表中非空值的单元格个数。
利用函数COUNTA可以计算单元格区域或数组中包含数据的单元格个数。
语法：
COUNTA（value1，value2，...）
Value1，value2，...为所要计算的值，参数个数为1到30个。
在这种情况下，参数值可以是任何类型，它们可以包括空字符（""），但不包括空白单元格。
如果参数是数组或单元格引用，则数组或引用中的空白单元格将被忽略。
如果不需要统计逻辑值、文字或错误值，可使用函数COUNT。
示例：
如公式=COUNTA（A2：
A8），意为计算此列数据中非空单元格的个数，为6个。
如公式=COUNTA（A1:
A7，2），意思为计算此列数据中非空单元格以及包含数值2的单元格个数，为7个。
如公式=COUNTA（A1:
A7，“Two”），是为计算上列数据中非空单元格以及值“Two”的个数，为7个。
1.2.7.3COUNTIF函数
用以统计单元格区域中满足给定条件的单元格的个数。
语法：
COUNTIF（rage，criteria）
参数range表示需要统计其中满足条件的单元格数目的单元格区域；criteria表示指定的统计条件，其形式可以为数字、表达式、单元格引用或文本。
在运用COUNTIF函数时要注意，当参数criteria为表达式或文本时，必须用引号引起来，否则将提示出错。
示例：
公式=COUNTIF（A2:
A5，"苹果"），意思是计算第一列中苹果所在单元格的个数，结果为2个。
公式=COUNTIF（B2:
B5，">55"），其实是计算第二列中值大于55的单元格个数，结果为2个。
1.2.8字符串操作的CHAR、LEET、RIGHT、MID函数
1.2.8.1CHAR函数
返回对应于数字代码的字符，可将其他类型计算机文件中的代码转换为字符。
语法：
CHAR（number）
Number是用于转换的字符代码，介于1到255之间。
使用的是当前计算机字符集中的字符。
示例：
=CHAR（65）显示字符集中的第65个字符（A）
=CHAR（33）显示字符集中的第33个字符（!
）
1.2.8.2LEFT函数
得到字符串左部指定个数的字符。
语法：
LEFT（string，n）
string指定要提取子串的字符串。
n指定子串长度返回值String。
函数执行成功时返回string字符串左边n个字符，发生错误时返回空字符串（""）。
如果任何参数的值为NULL，LEFT（）函数返回NULL。
如果n的值大于string字符串的长度，那么LEFT（）函数返回整个string字符串，但并不增加其它字符。
示例：
AnyString="HelloWorld"'定义字符串。
MyStr=Left（AnyString，1）'返回"H"。
MyStr=Left（AnyString，12）'返回"HelloWorld"。
1.2.8.3RIGHT函数
功能是从字符串右端取指定个数字符。
语法：
RIGHT（string，n）。
string：
string类型，指定要提取子串的字符串。
n：
long类型，指定子串长度返回值String。
函数执行成功时返回string字符串右边n个字符，发生错误时返回空字符串（""）。
如果任何参数的值为NULL，RIGHT（）函数返回NULL。
如果n的值大于string字符串的长度，那么RIGHT（）函数返回整个string字符串，但并不增加其它字
升级会员 