Excel办公软件从菜鸟到高手实训教程.docx
《Excel办公软件从菜鸟到高手实训教程.docx》由会员分享，可在线阅读，更多相关《Excel办公软件从菜鸟到高手实训教程.docx（11页珍藏版）》请在冰豆网上搜索。
Excel办公软件从菜鸟到高手实训教程
Excel办公软件从菜鸟到高手实训教程
第一招：
大海捞针（Vlookup函数）
招如其名。
此招用来在一个茫茫的数据源中，自动让电脑找出你要的某个数据的相关资料并填在指定的地方。
也是就是，可以让电脑在一个表格或指定的一个区域中查找某一指定的值，并由此返回该值相对应当前行中指定列处的数值。
此招还有相应的变化，分别为lookup和Hlookup两式。
当查找的数据是水平排列时，可以使用函数HLOOKUP代替函数VLOOKUP。
但用到的情况比较少，这里不做介绍。
例如：
你有一个工作表，上面近万项货品名称，每项货品的代码，价格，购买日期等。
如你要做另一份报表，且其中部分货品在这份表中己有数据，则可以利用此招，只输入货品的名称或者代码，其余的让电脑自动查找并返回相应的价格，购买日期等。
使用语法
VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）
Lookup_value为需要查找的值。
Lookup_value可以为数值、引用或文本字符串。
Table_array为需要在其中查找数据的数据表。
可以使用对区域或区域名称的引用，例如数据库或列表。
如果range_lookup为TRUE，则table_array的第一列中的数值必须按升序排列：
…、-2、-1、0、1、2、…、-Z、FALSE、TRUE；否则，函数VLOOKUP不能返回正确的数值。
如果range_lookup为FALSE，table_array不必进行排序。
通过在“数据”菜单中的“排序”中选择“升序”，可将数值按升序排列。
Table_array的第一列中的数值可以为文本、数字或逻辑值。
文本不区分大小写。
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!
。
Range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。
如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果range_value为FALSE，函数VLOOKUP将返回精确匹配值。
如果找不到，则返回错误值#N/A。
说明
如果函数VLOOKUP找不到lookup_value，且range_lookup为TRUE，则使用小于等于lookup_value的最大值。
如果lookup_value小于table_array第一列中的最小数值，函数VLOOKUP返回错误值#N/A。
如果函数VLOOKUP找不到lookup_value且range_lookup为FALSE，函数VLOOKUP返回错误值#N/A。
应用示例：
上图中，为方便比较，我将原始数据区域放在了同一工作表中（E1:
F5）,实际使用时，原始数据可以在不同的工作表，甚至不同的工作簿（即不同的Excel文件）。
当被查找的内容与原始内容在不同的工作表，table_array前面需加上工作表的名称，写法为“表名!
”区域范围，如“Sheet2!
$A$1:
$B$12”，而若在不同的工作簿，则还得加上文件名，如“[文件名]sheet1!
$A$1:
$B$12”。
详细解释
公式“=Vlookup（A2,$E$2:
$F$5,2,FALSE）”中A2表示要查找的值为A2单元格的内容，即“Apple”,“$E$2:
$F$5”告诉电脑，应该去$E$2:
$F$5这个数据区域中查找，“2”表示找到后，应传回该区域第二列的值，即数量列，最后“FALSE”参数系统，查找区域内容未进行排序，需使用精确查找，找不到就算了，不返回近似匹配值。
特别要注意的是，通常我们都是使用鼠标拖动的方法来填充公式，而拖动时，Excel对公式中区域的引用，处理方法是不一样的。
如果是相对参照，即栏名列号前没有“$”符号，则Excel会对该区域作相对位移，如上栏是E2:
B5,拖到下栏后，即会自动成为E3:
B6，这种处理方法在很多公式中是必要的，但在这个公式中却是致命的，因为它更改了查找的原始数据的区域，导致实际上包含有的数据，因己不在查找的区域中而漏网。
这也是很多用户在实际应用中犯的错误，引致查找结果不真实。
要解决这个问题，我们可以利用Excel对区域引用的第二种方法：
绝对参照。
即在栏名列号前加上“$”，这样，系统就不会作相对的位移，无论怎样拖，区域范围都不变。
（在很多情况下，我们会使用“名称”来代替直接的区域指定方式，使用更为方便。
这一内容将在其他章节中介绍）
相对参照与绝对参照的写法，可以让电脑作自动转换。
方法是，先将当前单元格定位在要修改的单元格上，然后在资料编辑列，用鼠标涂黑（英文的说法叫Highlight）要转换的部分，再按“F4”即可。
见下图：
通过上图可以看出，能找到的，系统己自动填入了找到的值，如Apple&cherry，对于找不到的（Plum&Pear），则显示#N/A。
第二招：
左右逢源（If函数）
此招用来对某一条件执行的真假值进行判断，根据逻辑计算的真假值，返回不同结果。
如果结果为真，则返回一个真，如果为假，则返回另一值，可谓左右逢源。
使用语法
IF（logical_test,value_if_true,value_if_false）
Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。
例如，A1>=60就是一个逻辑表达式，如果单元格A1中的值大于或等于60，表达式即为TRUE，否则为FALSE。
本参数可使用任何比较运算符。
Value_if_truelogical_test为TRUE时返回的值。
例如，如果本参数为文本字符串“预算内”而且logical_test参数值为TRUE，则IF函数将显示文本“预算内”。
如果logical_test为TRUE而value_if_true为空，则本参数返回0（零）。
如果要显示TRUE，则请为本参数使用逻辑值TRUE。
Value_if_true也可以是其他公式。
Value_if_falselogical_test为FALSE时返回的值。
例如，如果本参数为文本字符串“超出预算”而且logical_test参数值为FALSE，则IF函数将显示文本“超出预算”。
如果logical_test为FALSE且忽略了Value_if_false（即value_if_true后没有逗号），则会返回逻辑值FALSE。
如果logical_test为FALSE且Value_if_false为空（即value_if_true后有逗号，并紧跟着右括号），则本参数返回0（零）。
Value_if_false也可以是其他公式。
说明
函数IF最多可以嵌套七层，用value_if_false及value_if_true参数可以构造复杂的检测条件。
在计算参数value_if_true和value_if_false后，函数IF返回相应语句执行后的返回值。
应用示例：
第三招：
投石问路（IS函数）
此招用来对某个单元格的当前值的类型进行判断，以便知道其类型后，再采取下一部行动，因此称为投石问路。
IS函数共有九个工作表函数。
概括为IS类函数，可以检验数值的类型并根据参数取值返回TRUE或FALSE。
例如，如果数值为对空白单元格的引用，函数ISBLANK返回逻辑值TRUE，否则返回FALSE。
使用语法
ISBLANK（value）
ISERR（value）
ISERROR（value）
ISLOGICAL（value）
ISNA（value）
ISNONTEXT（value）
ISNUMBER（value）
ISREF（value）
ISTEXT（value）
Value为需要进行检验的数值。
分别为：
空白（空白单元格）、错误值、逻辑值、文本、数字、引用值或对于以上任意参数的名称引用。
应用示例：
详细解释
公式“=ISBLANK（A1）”，表示对A1单元格是否为空进行判断。
如是是空的，则返回“True”值，如果不为空，则返回“False）的值。
上面示例图中，B1及B2单元格中的函数分别对A1及A2单元格进行是否为空白的判断。
结果显示一个为真，一个为假。
OK，我们己经学了三招，现在我们要将这三招组合起来，自创一招新招。
第四招：
瞒天过海（自创组合招数）
我们来看学第一招时用的例子，
对于找不到的项目，系统显示#N/A，但这样的报告交给上司，未免太难看了些。
用什么方法，可以让其不显示出错误值呢？
对了，先来一招投石问路，对系统返回的值做一个判断，看看系统到底找到没有。
再来一招左右逢源，对于找到的就显示原值，找不到的，就干脆让它显示空白（当然，也可让设置其他的值如No等），岂不妙哉？
因此，对于原单一公式：
=VLOOKUP（A2,$E$2:
$F$5,2,FALSE），可以结合IF和IS函数来使用。
大家刚才看到，对于投石问路，共有九种变化，其中第三式（ISERROR）或第五式（ISNA）均适合这种情况，可以使用。
因此，组合后的公式就变成：
=IF（ISNA（VLOOKUP（D2,$G$2:
$H$5,2,FALSE））,"",VLOOKUP（D2,$G$2:
$H$5,2,FALSE））
或
=IF（ISERROR（VLOOKUP（D3,$G$2:
$H$5,2,FALSE））,"",VLOOKUP（D3,$G$2:
$H$5,2,FALSE））
下图显示了这种情况。
红框中用的就是组合的公式，而其中的Plum&Pear没有再显示难看的#N/A，报表因此漂亮多了。
因为是公式，只要写好第一个单元格的公式，其余的一拖就好了。
好了，新创的这招叫什么呢？
就叫瞒天过海吧！
第五招：
留头去尾（Left函数）
此招用来对原始数据进行截取。
截取的方式是从第一个字符开始，截取用户指定长度的内容。
例如：
在一个工作表中，某一列的资料是地址，录有省、市、街道等。
如果你想插多一列，加入省份的资料，以便进行省份筛选，则可用该函数自动进行截取，而无需人工输入。
使用语法
LEFT（text,num_chars）
Text是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。
Num_chars指定要由LEFT所提取的字符数。
Num_chars必须大于或等于0。
如果num_chars大于文本长度，则LEFT返回所有文本。
如果省略num_chars，则假定其为1。
应用示例：
上图中，对含有不同地方的数据，利用“Left”函数，非常简单分离出了他们的省份。
详细解释
公式“=Left（A2,3）”中A2表示要截取的数据为A2单元格的内容“广东省东莞市东城区…”，“3”表示从第一位开始，共截取3个字符，因此系统返回“广东省”。
第六招：
去头留尾（Right函数）
此招与上招刚好相反，截取的方式是从最后一个字符开始，从后往前截取用户指定长度的内容。
使用语法
RIGHTB（text,num_bytes）
Text是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。
Num_chars指定希望RIGHT提取的字符数。
注意：
Num_chars必须大于或等于0。
如果num_chars大于文本长度，则RIGHT返回所有文本。
如果忽略num_chars，则假定其为1。
应用示例：
详细解释
公式“=Right（A2,8）”中A2表示要截取的数据为A2单元格的内容“广东省东莞市…电话：
”，“8”表示从最后一位开始，共截取8个字符，因此系统返回“”。
尽管原始数据长短不齐，但我们只关心最后的8位电话号码。
第七招：
掐头去尾（MID函数）
与上面的两招不同，此招既不从第一位开始截取，也不从最后一位开始截取，而是由用户自行指定开始的位置和字符的长度。
因此，若用户指定从第一位开始，便和Left函数一样。
使用语法
MID（text,start_num,num_chars）
Text是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。
Start_num是文本中要提取的第一个字符的位置。
文本中第一个字符的start_num为1，以此类推。
Num_chars指定希望MID从文本中返回字符的个数。
注意：
如果start_num大于文本长度，则MID返回空文本（""）。
如果start_num小于文本长度，但start_num加上num_chars超过了文本的长度，则MID只返回至多直到文本末尾的字符。
如果start_num小于1，则MID返回错误值#VALUE!
。
如果num_chars是负数，则MID返回错误值#VALUE!
。
如果num_bytes是负数，则MIDB返回错误值#VALUE!
。
应用示例：
详细解释
公式“=MID（A2,7,8）”中A2表示要截取的数据为A2单元格的内容“**********”，“7”表示从第7位开始，共截取8个字符，因此系统返回用户想截取的生日时间“”。
OK，我们己经学了三招，但读者可能己经发现，实际工作中，原始资料并不会如此整齐地出现，让我们很容易的用上面的三招去截取。
比如说，第三招示例中，我用的都是18位的身份证号码，但实际上，很多人仍在使用15位的身份号码，这样一来，因原始数据长度不一致，导致在截取时，便会截错。
再如我们的第一个例子，我们截的是3位，但实际中，有的省份名称本身就有3位，因此对这种情况，简单的套用就无法取得正确的内容。
如我在第一篇中所说，在实际的工作使用中，单一公式常常都是不够的，而需要使用组合招数。
例如刚才的身份证号码不同长度问题，我们可以在招式中，加入对位数的判断，如果长度是18位，则取8位，如果是15位的，则取6位。
还记得前面我们学过左右逢源吗？
这一招可是相当的实用哦，我们经常会用到。
另外，下面我再介绍两招，用来对单元格的内容进行判断。
一个是“瞎子摸象”（Find函数），让用户对单元格内容中指定的字符进行定位，以确认其位置。
当位置被确认后，截取就是轻而易举的一件事情了。
另一招是“鲁班神尺”（Len函数），让用户对单元格内容的长度进行测量，得出其长度后，再做相应的截取处理。
第八招：
瞎子摸象（Find函数）
此招用来对原始数据中某个字符串进行定位，以确定其位置。
因为该招进行定位时，总是从指定位置开始，返回找到的第一个匹配字符串的位置，而不管其后是否还有相匹配的字符串，有点像瞎子摸象，摸到哪就说哪，因此取名“瞎子摸象”。
使用语法
FIND（find_text,within_text,start_num）
Find_text是要查找的文本。
Within_text是包含要查找文本的文本。
Start_num指定开始进行查找的字符。
within_text中的首字符是编号为1的字符。
如果忽略start_num，则假设其为1。
注意：
使用start_num可跳过指定数目的字符。
例如，假定使用文本字符串“AYF0093.YoungMensApparel”，如果要查找文本字符串中说明部分的第一个“Y”的编号，则可将start_num设置为8，这样就不会查找文本的序列号部分。
FIND将从第8个字符开始查找，而在下一个字符处即可找到find_text，于是返回编号9。
FIND总是从within_text的起始处返回字符编号，如果start_num大于1，也会对跳过的字符进行计数。
如果find_text是空文本（""），则FIND则会返回数值1。
Find_text中不能包含通配符。
如果within_text中没有find_text，则FIND返回错误值#VALUE!
。
如果start_num不大于0，则FIND返回错误值#VALUE!
。
如果start_num大于within_text的长度，则FIND返回错误值#VALUE!
。
应用示例：
上图中，对含有不同地方的数据，利用“Find”函数，非常简单地确定了“省”出现的位置。
详细解释
公式“=FIND（"省",A2）”中，“省”表示要查找的文本为“省”，（实际使用中，也可以很长的一串字符）。
要找查找的对象是A2单元格的内容“广东省东莞市东城区…”，因为没有指定起始位置，所以系统从第一位开始。
返回的“3”，表示“省“字在第三位。
而“黑龙江省哈尔滨市…”则返回4。
与Find类似，Search函数也有相同的功能。
它们的区别是，Find区分大小写，而Search不分大小写（当被查找的文本为英文时）。
另外，在Excel中，对文本进行处理的很多函数都提供了一个特别用来处理双字节字符（如中文，日文）的函数，一般是在原函数后加“B”，如FIND,就有一个FINDB。
之前讲过的LEFT,相对应的就是LEFTB等。
其实，我们在实际应用中，使用不带“B”的函数就足够了。
如果你想使用带“B”的函数，则要特别注意，尤其是在组合运用函数时，其中一个函数使用带“B”的形式，则其它有带“B”形式的函数，全部都要使用其带“B”的形式，否则结果极可能是错的。
第九招：
鲁班神尺（Len函数）
此招用来对单元格内容的长度进行测量，得出其长度后，再做相应的处理。
使用语法
LEN（text）
Text是要查找其长度的文本。
空格将作为字符进行计数。
应用示例：
详细解释
公式“=Len（A2）”中A2表示要查找长度的数据为A2单元格的内容“**********”，系统测量后，返回长度“18”。
好了，我们己经学完了“瞎子摸象”和“鲁班神尺”，再加上上次所学的三招（留头去尾－Left函数，去头留尾－Right函数和掐头去尾－Mid函数），对一些文本的处理，我们就能将其玩弄与股掌之上。
下面我们举一个例子，做一次实际操作。
假如你有一堆公司员工的身份证号码登记表，但却没有单独的员工出生年月日这一栏，而偏偏员工的生日资料你很需要。
一个一个的将其抄出来？
？
这未免太花时间吧！
别急，利用上面的几招，不出一分钟你就能够将其“生产”出来。
第一步的思路，就是利用“掐头去尾”函数，把身份证号码的前后内容去掉，留下中间的出生日期。
但因为身份证号有两种长度（15位及18位），直接套用，一定会截错。
于是，我们得加上“鲁班神尺”，先量出其长度，再加上“左右逢源”这招，对不同的长度号码，我们做不同的截取，问题就解决了。
上图中，两种颜色的数据长度是不一致的，但利用公式，我们很简单的把生日数据截出来了。
公式“=IF（LEN（A2）=15,"19"&MID（A2,7,6）,MID（A2,7,8））”中，我们利用IF函数，用Len函数对A2的长度进行判断，如果等于15，则返回“"19"&MID（A2,7,6）”，表示如果为15位的身份证号码，就在其之前补上“19”（注：
“＆”符号在Excel中，用来把两个数据合并在一起），然后对A2单元格中的数据从第7位开始，截6位出来，合在一起刚好8位。
如果不是15位，则返回“MID（A2,7,8））”，表示直接在A2单元格的数据中，从第7位开始，截取8位出来。
做完第一个公式后，不管下面还有几千或几万个数据，一拖到底即可。
对于要求比较简单的用户，得到这个结果己经够用了。
但实际上，这个取出来的数据，并不是日期格式的。
因此，就无法像对待日期那样处理它，如更改日期格式，或设置条件格式化，让当天为生日的数据显示为红色等。
要让其变为日期，其实也是很简单。
只是其中一招我们还未介绍－Datevalue，同时，取出的数据，也需额外加上分隔符，让系统识别。
我先把公式列在这里，有兴趣的用户可以试试。
=DATEVALUE（IF（LEN（A2）=15,"19"&MID（A2,7,2）&"-"&MID（A2,9,2）&"-"&MID（A2,11,2）,MID（A2,7,4）&"-"&MID（A2,11,2）&"-"&MID（A2,13,2）））
记住：
公式得出的结果，是一个时间序列号，日期格式你们可自行设置。
下图中，C3及C5单元格的“30720，30034”就是因没设置日期格式而直接显示序列值。
下面我们再来看一个使用了“瞎子摸象”函数的例子。
假如有这样一串数据，格式类似BillGates（****）Peng（****），我们需要取出其中的邮件地址部分。
因为其邮件地址时长时短，因此，无法直接截取，单用Len函数也无法实现。
但根据观查发现，邮件地址起始于“）”后，因此我们可以利用“瞎子摸象”Find函数，先定出每个数据中的“）”位置，再用Len量出整个数据长度，相减之后，就是邮件地址的长度，这样，用“去头留尾”函数就可将需要的数据取出。
公式为：
=RIGHT（A2,LEN（A2）-FIND（"）",A2））。
1.打开那篇包含着重复记录的Excel文件
2.选中所有记录，点击“数据”菜单→“筛选→高级筛选”命令
3.勾选“选择不重复的记录”复选框，然后将筛选方式更改为“将筛选结果复制到其他位置”并设置好目标区域
【小提示】默认的“在原有位置显示筛选结果”一项虽然也能得到不重复的记录，但这毕竟只是筛选，Excel只是将重复的记录隐藏罢了，并不能完全达到我们的目的
4.点击“确定”之后，新位置上出现的记录便是已经筛选好的了。
然后，我们再通过手工的方式清除
升级会员 