Excel word ppt office使用技巧大全(DOC格式)-第10部分

快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部! 如果本书没有阅读完，想下次继续接着阅读，可使用上方 "收藏到我的浏览器" 功能 和 "加入书签" 功能！




…………………………………………………………Page　89……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　Excel_word_ppt_使用技巧大全（完全版）　　



〃……〃　=　〃=〃　90、【选择性粘贴】里的【跳过空单元】到底有什么用啊？　跳过空格是指：　跳过　

剪切板上的空格；只复制；并保持他们的位置；粘贴到其他地方。　而不是指被粘贴的单元格。　这　

个功能非常有用，可以防止空白单元格替换原来的数据　　　

91、DATEDIF计算两个日期之间的天数、月数或年数。提供此函数是为了与　Lotus　1…2…3　兼容。　　

语法　DATEDIF（start_date；end_date；unit）　Start_date　为一个日期，它代表时间段内的第一　

个日期或起始日期。日期有多种输入方法：带引号的文本串（例如　〃2001/1/30〃）、系列数（例　

如，如果使用　1900　日期系统则　36921　代表　2001　年　1　月　30　日）或其他公式或函数的结果　

　（例如，DATEVALUE（〃2001/1/30〃））。有关日期系列数的详细信息，请参阅　NOW。　End_date　为　

一个日期，它代表时间段内的最后一个日期或结束日期。　Unit　为所需信息的返回类型。　Unit　

返回〃Y〃时间段中的整年数。〃M〃时间段中的整月数。〃D〃时间段中的天数。〃MD〃start_date　与　　

end_date　日期中天数的差。忽略日期中的月和年。〃YM〃start_date　与　end_date　日期中月数　

的差。忽略日期中的日和年。〃YD〃start_date　与　end_date　日期中天数的差。忽略日期中的　

年。　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　LXXXIX　　


…………………………………………………………Page　90……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　Excel_word_ppt_使用技巧大全（完全版）　　



说明　Microsoft　Excel　按顺序的系列数保存日期，这样就可以对其进行计算。如果工作簿使　

用　1900　日期系统，则　Excel　会将　1900　年　1　月　1　日保存为系列数　1。而如果工作簿使用　　

1904　日期系统，则　Excel　会将　1904　年　1　月　1　日保存为系列数　0，（而将　1904　年　1　月　2　　

日保存为系列数　1）。例如，在　1900　日期系统中　Excel　将　1998　年　1　月　1　日保存为系列　

数　35796，因为该日期距离　1900　年　1　月　1　日为　35795　天。请查阅　Microsoft　Excel　如何　

存储日期和时间。　Excel　for　Windows　和　Excel　for　Macintosh　使用不同的默认日期系统。　

有关详细信息，请参阅　NOW。示例　DATEDIF（〃2001/1/1〃；〃2003/1/1〃；〃Y〃）　等于　2，即时间段　

中有两个整年。　DATEDIF（〃2001/6/1〃；〃2002/8/15〃；〃D〃）　等于　440，即在　2001　年　6　月　1　日　

和　2002　年　8　月　15　日之间有　440　天。　DATEDIF（〃2001/6/1〃；〃2002/8/15〃；〃YD〃）　等于　75，　

即　在　　　6　　月　　　1　　日　与　　　8　　月　　　15　　日　之　间　有　　　75　　天　，　忽　略　日　期　中　的　年　。　　

DATEDIF（〃2001/6/1〃；〃2002/8/15〃；〃MD〃）　等于　14，即开始日期　1　和结束日期　15　之间的差，　

忽略日期中的年和月。　92、如何在EXCEL表格的一个单元格中设定校验。比如，单元格均为手　

工输入，某单元格必须等于其他几个单元格之和，如不等，则该单元格显示为红色。　用条件　

格式公式=D1SUM（A1：C1）　93、A1＇=abcabca　求a在单元格＇A1＇内出现次数？　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　XC　　


…………………………………………………………Page　91……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　Excel_word_ppt_使用技巧大全（完全版）　　



=LEN（A1）…LEN（SUBSTITUTE（A1；　　〃a〃；　　〃〃））　　94　　、　20040404　　如　何　转　为　2004…4…4　　

=text（a1；〃0000…00…00〃）　　　　　　　TEXT　　　　　　　　　（　　　　　　　　Z12；〃？？？？…？？…？？〃　　　　　　）　　

=LEFT（〃20040404〃；4）&SUBSTITUTE（RIGHT（〃20040404〃；4）；0；〃…〃）　使用分列最好　95、假如A2　

单元格有内容；就在E2单元格里面填上今天的日期。〃2005…5…30〃；如果明天；就填明天的日　

期。2005…5…31，但昨天的日期。〃2005…5…30〃；不改变。　建议使用VBA，用循环引用也可以：　工　

具—选项—重新计算—迭代计算—最多迭代次数1　=if（a2=〃〃；〃〃；if（e2=〃〃；now（）；e2））　96、　

当你的领导或者同事要使用你的机器发现面目全非；自定义太多弄的自己都头大了；这时你想　

恢复EXcel的本色却急的满头大汗？　这时最能解决这些问题的就是用户设置保存向导了。（要提　

前做）　在程序………OFFICE工具中可以找到它；他有保存本机设置和把设置应用到本机两个选项。　

多保存几个；方便在不同的个性菜单中切换　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　XCI　　


…………………………………………………………Page　92……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Excel_word_ppt_使用技巧大全（完全版）　　



97、我现在要在一个22位的数值中；提取其中第10；11；12位的数字；不知道可有什么好的方法　　

=MID（A1；10；3）　我想计算总米数=卷长*卷数；　但由于原数据库中卷长带有单位　（如：1000m）；　　

可不可以不需去掉单位直接用函数得出总米数？　=substitute（a2；〃m〃；）*b2　98、在某格中输入　

一串数字，如何使它能象输入密码一样显示******呢？　如果需要输入负数、文本时，也出现　

******，则自定义格式为：　　＊＊；＊＊；＊＊；＊＊　99、用函数得出带完整路径的文件名　　

=CELL（〃filename〃）　100、―定义名称‖　的方法解除嵌套函数的限制　EXCEL中一个众所周知的限　

制　是　你　不　能　嵌套　超过　7　层　函　数　。　例　如下　面　的　公　式　是错　误的　；　因　为　限　制被　超过　。　　

=IF（Sheet1！A4=1；11；IF（Sheet1！A4=2；22；IF（Sheet1！A4=3；33；　　

IF（Sheet1！A4=4；44；IF（Sheet1！A4=5；55；IF（Sheet1！A4=4；44；　　

IF（Sheet1！A4=5；55；IF（Sheet1！A4=6；66；IF（A4=7；77；FALSE））））））））　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　XCII　　


…………………………………………………………Page　93……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Excel_word_ppt_使用技巧大全（完全版）　　



通常的方法；如果你有一个公式超过这个限制；你会考虑用VBA代替。然而；如果你不想使用VBA；　

你可以通过对公式的一部分‖定义名称‖来解决这种限制。　我们利用一个IF嵌套公式来测试　IF　　

A4　=　1　Then　11　Else　If　A4　=　2　Then　22　Else　If　A4　=　3　Then　33　Else　If　A4　=　4　Then　44　。。。　　

Else　If　A4　=　13　Then　130　Else　〃Not　Found〃　当然在实践应用中；我们最好利用VLOOKUP这个　

函数去实现结果；但这里我们的目的是做个演示。首先；我们定义一个名叫‖ONE　TO　SIX‖　的名称；　　

里　面　包　括　公　式　：　　=IF（Sheet1！A4=1；11；IF（Sheet1！A4=2；22；IF（Sheet1！A4=3；33；　　

IF（Sheet1！A4=4；44；IF（Sheet1！A4=5；55；IF（Sheet1！A4=4；44；　　

IF（Sheet1！A4=5；55；IF（Sheet1！A4=6；66；FALSE））））））））　　接　着　；　再　定　义　另　一　个　名　

叫　　　　　‖SEVERTOTHIRTEEN‖　　　　　的　　　　　名　　　　　称　　　　；　　　　里　　　　　面　　　　　包　　　　　括　　　　　公　　　　　式　　　　　：　　

=IF（Sheet1！A4=7；77；IF（Sheet1！A4=8；88；IF（Sheet1！A4=9；99；　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　XCIII　　


…………………………………………………………Page　94……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Excel_word_ppt_使用技巧大全（完全版）　　



IF（Sheet1！A4=10；100；IF（Sheet1！A4=11；110；IF（Sheet1！A4=12；120；　　

IF（Sheet1！A4=13；130；〃NotFound〃）））））））　　最　后　；　在　B4　单　元　格　中　输　入　下　面　的　公　式　：　　

=IF（OneToSix；OneToSix；SevenToThirteen）　这样就解决了被嵌套函数的限制。因为公式中没　

有一个单独的部分超过限制；即使是‖各个组成部分的集合‖也没有超过限制。　当你编制一个超　

过限制的嵌套函数时可以使用这种方法。————————zhdi　　　

101、工作表名设为变量　=INDIRECT（〃月份！〃&ADDRESS（ROW（）；COLUMN（）））　此公式放在A1单元　

格，则是对　1月A1单元格的引用，如　1月A1单元格为　10，则返回10。　=INDIRECT（〃月份　

〃&E1&〃！〃&ADDRESS（ROW（）；COLUMN（）））　此公式将工作表名设为变量，变量为当前表的E1单元　

格的值，如E1为2，则引用月份2表中相对应的值。　　102、如何把―2005年5月1　日‖转换成　

―20050501‖　　　　　？　　　　　　　　　　=YEAR（A1）&TEXT（MONTH（A1）；〃00〃）&TEXT（DAY（A1）；〃00〃　　　　　　）　　

YEAR（A1）&IF（MONTH（A1）邮件　

收件人…》以附件形式发送收件人一栏请填写：邮箱地址，；点击发送可以了。　或新建邮件；收件　

人一栏请填写：　邮箱地址；然后用鼠标把那个　excel　文件拖到我的名字下面就可以点击发送　

了。　110、求非空单元格数量　公式计算出来的数据，COUNTA不能用的（否则空字符也计算进去　

了）　=COUNTIF（E3：E65536；〃？*〃）　强制计数至少1个字符的单元格数。　　　

111　、　（精　）　　动态求和公式　；　自A　列　A1　单元格到当前行前面一行的单元格求和　。　　

=SUM（INDIRECT（〃A1：A〃&ROW（）…1））　112、在使用SUM函数在单元格中输入超过30个参数时，你　

将得到一个〃参数太多〃的错误提示。　解决办法：在引用参数的两边多加一个括号，这时，SUM　

把括号内的最多可达254个参数当成一个处理。（主要是受公式长度限制，理论上可以达到无数　

个）　113如果您觉得公式复杂了，或是不直观　偶给你提供两个捷径：　方法一：选择公式单元　

格…〉菜单栏…〉＇工具＇…〉＇公式审核＇…〉＇公式求值＇　然后按照导向提示，一步步地观察公式　

求值的过程。（2003版适用）　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　XCVIII　　


…………………………………………………………Page　99……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　Excel_word_ppt_使用技巧大全（完全版）　　



方法二：选择公式单元格…〉选取编辑栏或F2…〉用鼠标置亮公式中你想查　看的那段…〉按F9，　

会显示该段的计算结果。　114请问有没有返回工作表名称的函数？　插入—　名称—　定义：　　

sh=replace（get。document（1）；1；find（〃＇〃；get。document（1））；）&t（now（））　单元格输入=sh　　

GET。DOCUMENT是宏表函数，当数据变动时无法自动计算，now　是易失性函数，任何变动都　

会强制计算，宏表函数所以加上now　就可以自动重算了，T　用来将now　产生的数值　

转　　　化　　　为　　　空　　文　　　本　　　。　　　　　　115　　、　　　显　　示　　　当　　　前　　　工　　　作　　　表　　　的　　表　　　名　　

=MID（CELL（〃filename〃）；FIND（〃＇〃；CELL（〃filename〃））＋1；255）　116　、我要　自动生成数据在　

K29＋010~K29＋600之间的数据（显示为K29＋XXX）　=TEXT（INT（RAND（）*591）＋10；〃K29＋000〃）　117、　

我想把列P1~P60　的数据引用到行U1~CB1　，本来想用公式=P1拉动复制，结果不行。　　

U1=INDIRECT（〃P〃&COLUMN（）…20），向右拉　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　XCIX　　


…………………………………………………………Page　100……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Excel_word_ppt_使用技巧大全（完全版）　　



118、　姓名　外语　数学　王　86　李　95　张　80　姓名　外语　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　C　　


…………………………………………………………Page　101……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Excel_word_ppt_使用技巧大全（完全版）　　



数学　李　95　90　张　80　80　王　86　75　将表1的姓名导入自定义序列，在表2按此自定义序列排序　　

或使用vlookup公式　表1的C2　=vlookup（a2；表2！a：c；3；0）　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　CI　　


…………………………………………………………Page　102……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Excel_word_ppt_使用技巧大全（完全版）　　



＇求助＇如何将2个排列顺序不同的表统一一下　119、如果你要在A3的前面插入100行；可以这样：　　

在名称框输入　3：103…………回车……………ctrl＋shift＋〃＋〃　（大键盘）　120、VBA从哪学起　1、录制宏；　　

2、数据类型（主是整型和字符串型）；　3、程序结构（主要是分支，判断，循环）；　4、EXCEL　

对象（单元格，工作表，工作薄）　　　

121、如何实现求平均值时只对不等于零的数求均值？　average（if（a1：a5》0；a1：a5））　122、D1　

输入姓名，A列中相同的姓名按设定和条件格式显示　=find（D1；A1）　123、excel中插入/名称　

/指定（应用）（标志）的用途是什么？　1、指定的用法　例如：在A1：A3中输入了AAA，BBB，CCC　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　CII　　


…………………………………………………………Page　103……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Excel_word_ppt_使用技巧大全（完全版）　　



选中A1：D3》插入》名称》指定》最左列》确定　这样就一次性定义了3个名称　AAA=B1：D1　　

BBB=B2：D2　CCC=B3：C3　2、应用的用法　在A4输入　=sum（B1：D1；B3：D3）　然后　插入》名称》应用　　

会将公式中的引用用相应的名称替换。　124、请教如何筛选出特殊颜色的数据。　定义名　

称。X=GET。CELL（24；单元格）；辅助列=X　按辅助列筛选　125、如何快速查看名称　插入………名称　

………粘贴………名称清单（粘贴列表）　126、COUNTIF函数的16种公式设置　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　CIII　　


…………………………………………………………Page　104……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Excel_word_ppt_使用技巧大全（完全版）　　



1、返加包含值12的单元格数量　=COUNTIF（DATA；12）　2、返回包含负值的单元格数量　=COUNTIF　

　（DATA，〃5〃）　5、返回等于单元格A1中内容的单元格数量　=COUNTIF（DATA，　

A1）　6、返回大于单元格A1中内容的单元格数量　=COUNTIF　（DATA，―》‖&A1）　7、返回包含文　

本内容的单元格数量　=COUNTIF　（DATA，―*‖　）　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　CIV　　


…………………………………………………………Page　105……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　Excel_word_ppt_使用技巧大全（完全版）　　



8、返回包含三个字符内容的单元格数量　=COUNITF　（DATA，―？？？‖　）　9、返回包含单词〃GOOD〃（不　

分大小写）内容的单元格数量　=COUNTIF　（DATA，―GOOD‖　）　10、返回在文本中任何位置包含单　

词〃GOOD〃字符内容的单元格数量　=COUNTIF　（DATA，―*GOOD*‖　）　11、返回包含以单词〃AB〃（不　

分大小写）开头内容的单元格数量　=COUNTIF　（DATA，―AB*‖　）　12、返回包含当前日期的单元格　

数量　=COUNTIF　（DATA，TODAY（）　）　13、返回大于平均值的单元格数量　=COUNTIF　（DATA，　

〃》〃&AVERAGE（DATA））　14、返回平均值上面超过三个标准误差的值的单元格数量　=COUNTIF　

　（DATA，―》〃&AVERAGE（DATA）＋STDEV（DATA）*3）　15、返回包含值为或…3的单元格数量　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　CV　　


…………………………………………………………Page　106……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　Excel_word_ppt_使用技巧大全（完全版）　　



=COUNTIF（DATA，3）＋COUNIF（DATA，…3）　16、返回包含值；逻辑值为TRUE的单元格数量　=COUNTIF　

　（DATA，TRUE）　127、日期分列　如有一列格式如2005…7…8的数据，要将其分为2005　8　7，方　

法为：数据_分列，…号作分隔符，列数据格式…文本，再将年份转为数值。　128、查询时查不　

到数据的原因　可能是格式不一致。　129、120，000显示为12。0　自定义格式：　#！。0；　130、121