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

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



2、rank　函数（=RANK（A2：A11；A2：A11；0）（假设数据在　A2：A11　单元格，下同）　　

3、使用　contif　函数进行排列“=countif（a2：a11；〃》〃&a2）＋1〃　　



　　　　　　　　　　　　　　　　　　　　　　　　有无办法让　B2　所在行都呈红色字体　　



解答：假设你有一个　B　列和一个　A1　的值，你的目的是，如果B2＝A1　的话，整个B　列都为红色　

显示！　　

设置如下：先选定整个　b　列，也就是在　B　列列标处单击（废话～^_^），选择格式－条件格式　　

出现条件格式对话框，单击左边的下拉列表，里面只有两项，单元格数值和公式，选中公式，　

右边就可以输入任何可以返回逻辑值的公式了。输入这个公式=（B2=A1）。千万注意要用绝　

对引用，因为如果是相对的，excel　又自作主张的一个一个判断了，就没有作用了。　（绝对正　

确并且好用）　　



　　　　　现有　12　个工作表；是　12　张发票；建立一个汇总表；将发票号和金额汇总显示在一张表里　　



（发票号和金额在每张表的相同位置）。　　

解答：在　A1　输入　　=INDIRECT（〃sheet〃&ROW（）&〃！d3〃）　　

在　B1　输入　　=INDIRECT（〃sheet〃&ROW（）&〃！d10〃）　　

再选择　A1：B1　往下复制到第　12　行。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　经验技巧　　



按　“Ctrl＋~”可以一次显示所有公式（而不是计算结果）。再按一次回到计算结果。（程香宙）　　



　　　　　　　在一个不对称的区域中如（b1：G7）中找到A　行一组数据中的某个数并自动变红　　



解答：其实也很简单；你只要选定你的　b1：g7，设置它的条件格式为=（COUNTIF（A1：A7；b1））　　

注意；b1　为相对引用；这里输入所选区域的第一个取值，那样你的所选区域会自动填充。达到你　

要的效果。　（好）　　



　　　　　　　　　　不借助第三列而直接用函数或公式一步得到　sum（a2/b2；a3/b3；〃）的结果　　



解答：输入=sum（a1：a100/b1：b100），按　ctrl＋shift＋Enter。　　



　　　　　　　　　　　　　　　　　　请问要如何算出每个月有几个星期一、二、三〃。日　　



解答：为简单起见；表格需作一下调整；将　〃星期日〃　移到　C1；其后依次；这也符合规则　（请参阅　

函　　　　数　　　：　　　　WEEKDAY（））。　　。　　　　在　　　　　　　C2　　　键　　　入　　　　数　　　组　　　　公　　　式　　　　：　　

｛=SUM（IF（WEEKDAY（DATE（A2；B2；ROW（INDIRECT（〃A1：A〃　　　　　　　　　　　　　　　　　　　&　　

DAY（DATE（A2；B2＋1；1）…1）））））=COLUMN（）…2；1））｝　，向右复制、向下复制。公式解释一　

点：ROW（INDIRECT（〃A1：A〃　&　DAY（DATE（A2；B2＋1；1）…1）））实际上是从　1　号测试到本月的　

最后一天。如需要；公式可再作精简。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　351　　


…………………………………………………………Page　352……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　让隐藏的列或行不参预计算　　



解答：使用　subtotal　函数；详细用法参见帮助。　　



　　　　　　　　　　　　　　　　　　　　　　　一次删完　Excel　里面多出很多的空白行　　



解答：1、用分面预览看看　　

2、用自动筛选然后删除　　

3、用自动筛选，选择一列用非空白，空白行就看不到了，打印也不会打出来。但是实际上还　

是在的，不算删除。或者用自动筛选选择空白将空白行全显出来一次删完也可以。　　

4、先插入一列，在这一列中输入自然数序列，然后以任一列排序，排序完后删除数据后面的　

空行，再以刚才输入的一列排序，排序后删除刚才插入的一列。　　



　　　　表　1、表　2　分别有　20　个人的基本情况和其中　10　个人的名字，让表　1　的数据自动填充到表　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　2　　



答：1、用　lookup　函数即可。要保证　20　人不重名；　　

2、假设表　1　的D　列对应表　2　的E　列。E2　的公式：=VLOOKUP（B2；Sheet1！B：D；3；FALSE）　　



　　　　　　　　　　　　　　使用　vlookup　函数返回#N/A　符号时将此符号用　0　或空格来代替　　



答：这样处理：　=IF（ISNA（VLOOKUP（C13；A1：B10；2））；0；VLOOKUP（C13；A1：B10；2））　　

或：IF（ISERROR（vlookup（a1；e1：g10；2；0））；0；vlookup（a1；e1：g10；2；0））。　　



　　　　　　　　　　　　　　　　　　　　通过条件格式将小计和总计的行设为不同的颜色　　



答：输入=RIGHT（RC；1）=〃计〃；设定字体、边框、图案；确定。　　



　　　　　　　　　　　　　　　　复制隐藏后的表格到一个新表格中使被隐藏的内容不显示　　



答：crtl＋g－选可见单位格－复制－粘贴。　　



　　　　　　　　　　　　　　　　　　　　　　　如何将一个工作簿中的一个　Sheet　隐藏　　



答：1、选“格式”……“工作表”………“隐藏”　　

2、使用VBA　这样隐藏后在使用工作表保护。　　

Alt＋F11…………Ctrl＋G…………出现立即执行窗口，在此窗口内执行　　

Sheet1。Visible　=　xlSheetVeryHidden　　

这样隐藏后　sheet　在格式………工作表…………取消隐藏是看不见的。　　

问：方法2　更好哦，如何恢复呢？　　

答：sheet1。Visible　=xlSheetVisible　　



　　　　　　　　　　　　　　　　　　　　　　　　　　工具菜单与视图中的工具栏不同　　



屏蔽工具菜单宏　　

sub　notool（）　　

MenuBars（xlWorksheet）。Menus（〃工具〃）。Delete　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　352　　


…………………………………………………………Page　353……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



end　sub　　

解除屏蔽　　

sub　yestool（）　　

MenuBars（xlWorksheet）。reset　　

end　sub　　　

Alt＋F11　进入　VBA　编辑、插入模块、将上面宏复制到模块、运行宏。OK　　



　　　　　　　　　　　　　　　　　　　　　　　　　　查找并填写符合条件的单元格内容　　



我在工作中需快速复制每行多个数据（单元格）中最小值所对应的“标题名”，如　E6　是　C6：　

Y6　中的最小值，所对应的标题是E5　单元格“某某公司”，要将其（某某公司）复制到　B6　单元　

格中，以此类推的复制很多很多行的内容。如果是手工一个一个查找与复制，实在是太慢太笨　

了，能否使用一个简单的公式计算呢？　　

答：B6　单元格〃=INDEX（C5：Y5；MATCH（MIN（C6：Y6）；C6：Y6；0））〃　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　填写空白行　　



我有个同事在一张空白表依次输入数据，为了省事她把和上一格内容相同的的省略不输，输了　

近　200　行。后来又觉得不够正式，想把空白的地方补上。她来问我怎么办好。当然依次填充也　

行，但我觉得烦（如果有　2000　行怎么办呵呵）我想了一个不是办法的办法：在　A　列和　B　列旁　

各插入一列，现在就有　ABCDE　列，我在　B2　中复制了　A2　中的内容，然后在　B3　中输入公式：　

IF（A3=0；B2；A3）；然后往上往下复制公式。这样就填满了。如法炮制　D　列后隐藏　AB　列感觉就可　

以了。可是也烦啊，谁有更好的办法？　　

答：1、Sub　feifjeifjeifjeifjeifjiefjiejfiejf（）　　

For　i　=　2　To　ActiveSheet。Range（〃a1〃）。CurrentRegion。Rows。Count　　

If　IsEmpty（Cells（i；　1））　Then　　

Cells（i；　1）。FormulaR1C1　=　Cells（i　1；　1）。Value　　

End　If　　　

Next　　

End　Sub　　

2、筛选出空白行，输入公式=INDIRECT（〃a〃&ROW（）…1），填充　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　制订下月计划并显示为中文　　



我在五月份做六月份的计划，为减少工作量和更改的麻烦，我做模板并使用了公式=〃计划期：　

〃&YEAR（NOW（））&〃年〃&（MONTH（NOW（））＋1）&〃月〃，结果如A1　所示　计划期：2002　年　6　月　　（现在　

的系统日期是　2002　年　5　月）。　如果我想自动得到如　A2　中的结果　计划期：二○○年六月　，请　

问要如何做才行，我设置了单元格的日期格式还是不行。　　

解答：　1、先设置单元格格式为〃二○○二年六月　〃那种类型，然后用如下公式：　

=DATE（YEAR（NOW（））；（MONTH（NOW　（））＋1）；20）就可以了。　　

2、使用这个函数吧！　=EDATE（NOW（）；1）。单元格格式应设置为：日期…………一九九七年三月。　　

3、设置单元格格式为：＇DBNum1＇〃计划期：〃yyyy〃年〃m〃月〃　，然后直接输入日期值（如　2002/11）　

即可。　　

输入公式也可以。如=today（）＋30，可以得到下个月的月份。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　353　　


…………………………………………………………Page　354……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　&的用法　　



有　E44　单元格，我希望　总计：=SUM（E45：E49）　就是想让它经过自动求和后在一个单元格内显　

示　总计：120　。　　

解答：有多种方法实现，详细如下：　　

1、=〃总计：〃&sum（e45：e49）　　

2、把　E44　格式设为〃总计：〃#0。00；〃总计：〃…#0。00；〃总计：〃0。00；@　　

3、将　E44　单元格格式自定义为　〃总计：〃0。000　即可；方便对　E44　的引用计算　　

4、=CONCATENATE（〃合计：〃；SUM（e45：e49））　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　有　5　行数据在每行上面个插入　1　行　　



　　　　解答：1、在最左边插入一列，然后输入　1、2、3、4、5、1、2、3、4、5，并以此列进行　

排序，在第一行上面再插入一行，删除刚刚插入的列。　　

　　　　2、使用　Ctrl＋鼠标一行一行选定，然后插入行。　　

　　　　3、sub　插入行　　

for　i=1　to　6　　

if　cells（i；1）。value　　Cells（i　＋　1；　1）　And　Cells（i；　1）　　〃〃）　Then　　

Rows（i　＋　1）。Insert　　

end　if　　

next　i　　

end　sub　　　



　　　　　　　　　　　　　　　　　　　　　　　　　可以检查一张表里是否有漏重的数字吗　　



答：漏值：　｛=IF（SUM（（R1C1：R10C4=〃〃）*1）》0；〃有漏值〃；〃无漏值〃）｝　　

重复值：　｛=SUM（SUM（（漏值！R1C1：R10C4=漏值！RC）*1））｝　　

｛=IF（MAX（R1C1：R10C4）》1；〃有重复值〃；〃无重复值〃）｝　　

使用下面公式更方便：　　

找重复值………………｛=IF（SUM（（COUNTIF（R1C1：R10C4；R1C1：R10C4）》1）*1）》1；〃有重复值〃；〃无重　

复值〃）｝　　

找　漏　值………………｛=IF（SUM（（R1C1：R10C4=〃〃）*1）》0；〃有漏值〃；〃无漏值〃）｝　　

注意：这两个公式均为数组，输入时应同时按　Ctrl＋Shift＋Enter。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　怎样将单元格中的公式转换为数值　　



解答：选中公式的一部分，按　F9　键　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　条件求和　　



有这样一个表格　　

A　　　　　　　　B　　　　　　　　　　C　　　　

1　2　3　/2　2　3　/3　2　3　/4　2　3　/5　2　3/6　2　3　/如何才能求出满足　A　列中大于　2　且小于　5　的B　列和　C　列数　

值的和，要求　B　列和　C　列的值相加。用　sumif　函数似乎条件中只能设定为＞2　，而不能同时设　

定＜5，而且在求和时只能　B　列相加，不能把　B　列和　C　列满足条件的值加起来。这个问题能　

不能只用函数，不用数组公式解决。请各位指教。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　354　　


…………………………………………………………Page　355……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



解答：1　用公式：=SUM（IF（（A1：A6》2）*（A1：A62；IF（A2：A72；IF（A2：A　

73000；　Q7》0。5）；　〃〃；　P7）　　



意思是：如果　R7　单元格中的数值大于　3000，并且　Q7　单元格中的数值大于　0。5，则在K7　单元　

格中显示空白，否则显示出　P7　单元格中的数据。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　统计数据问题一例　　



各位朋友，如果我想统计　50　个数据中大于某个值的数据个数，（这个值是在使用时才输入某个　

单元格的），请问用什么函数，如何实现，谢谢。　　　　

如数据单元格为　A1　：E10　，值的单元格为A11　。　　

答：1、使用下面的数组公式：　　　｛=SUM（IF（A1：E10》A11；1））｝　　

2　、输入以下函数：　　=COUNTIF（A1　：E10；〃》〃&A11）　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　关于条件求和问题！　　



有　A　，B　，C，三列数据，如果A　列符合要求，求　B1*C1＋B2*C2＋。。。。。。？　　

答：使用数组公式：　　　｛=SUM（（R2C1：R13C1=〃ab〃）*（R2C2：R13C2）*（R2C3：R13C3））｝　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　请教关于条件乖积的求和问题　　



A　列为部门名称，B　列为姓名，C　列为日工资额（如　20。00）　，D　列为月出勤天数，我想在另一汇　

总表中汇总出各部门员工月工资总额（即：相应部门对应的　C*D　之和）　。请问如何解决？　　

解答：1、=SUM（（A4：A10=〃甲部门〃）*（C4：C13）*（D4：D13））　　

假设你的　a　列存放部门名称，你的　b　列存放员工姓名，C　列存放日工资，D　列存放天数。　　

计算“甲部门”的工资总额。　　

注意，这是数组公式，输入完毕后按　ctrl＋shift＋回车　　

问：我试着把区域引用改为整列，出现错误，请指点！　　

=SUM（（date！A：A=〃　甲部门〃）*（date！E：E）*（date！F：F））　　

答：经试验，不能用整列方式，　　

你可以适当的调整一个比较大的区域如　a2：a100　a1　为标题行　　

因为如果参与计算的　e　列和　f　列区域出现文本，也会发生错误。一定要把计算区域的标题行去　

掉，并且保证不再数值区域出现文本。　　

=SUM（（date！A2：A100=〃　甲部门〃）*（date！C2：C100）*（date！D2：D100））　　

这个公式没有错误　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　文件修复　　



Excel　文件是一个工作簿，一般可以包含　255　个工作表，每个工作表中可以包含大量的数据。　

如果一个　Excel　文件部分受损，不能正常打开，该怎么办呢？这里向大家介绍两种解救方法。　　

　　　　手动处理　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　355　　


…………………………………………………………Page　356……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　进入　Word　，打开要修复的XLS　文件，如果　Excel　只有一个工作表，会自动以表形式装入　

Word　，若文件是由多个工作表组成，每次只能打开一个工作表。打开后，先将文件中损坏的　

数据删除。　　

　　　　用鼠标选中［表格］→［转换］→［表格转文本＇，注意可用“，”间隔符或其它分隔符，　

另存为一个　TXT　文本文件。在　Excel　中直接打开该文本文件，在打开时，Excel　会提示文本导　

入向导，一般情况下只要直接点击［下一步］即可，打开后另存为其它的　Excel　文件即可。　　

　　　　注意：这种修复的方法是利用　Word　的直接读取Excel　文件的功能实现，该方法在文件头　

没有损坏，只是文件内容有损坏的情况下比较有效；对文件头已经损坏的　Excel　文件，此方法　

可能不成功，必须借助于其它方法。　　

　　　　用　Excel　修复工具　　

　　　　Concept　　　Data　公司提供了一个专门用于修复　Excel　　　　　　　　　　　　　　　　损坏文件的修复工具　——　

ExcelRecovery，能够对　Excel　5。0/97/2000　的文件进行恢复处理，大家可以先下载该软件的免费　

演示版试用，仅有　674KB，下载地址　http：//download。sina。。cn　。软件安装后，可以自动将　

Excel　　的修复程序加在　Excel　　应用程序中，在“文件”菜单下多出一项“Recovery　”选项，若　

有损坏的文件，可以用该选项进行修复。另一个下载地　http：//skycn。/soft/7432。html　　

　　　　　　　　使用方法是：　　

　　　　　　　　1、打开　Excel；　　

　　　　　　　　2、单击“文件”菜单下的“Recover”选项，Excel　Recovery　对话框将打开　　

　　　　　　　　3、指定要修复的　Excel　文件，然后按＇Recover＇按钮；　　

　　　　　　　　4、自动修复；　　

　　　　　　　　5、另存为一个新的文件名，即可完成文件的修复工作。　　

　　　　　　　　注意：该演示版本只能修复普通文件，不能修复带　Visual　Basic　代码、图表以及包　

含口令的　Excel　文件，如果需要修复这些文件，请进行注册。　　

　　　　　　　　另外要提醒大家的是：保存在软盘中的文件极易损坏，受损后可用以上方法修复，　

但如果损坏的文件位于磁盘　0　磁道时，就必须先修复软盘，再用上述方法修复　Excel　文件。　　

　　　　另外，考虑到未注册版本的使用限制，我找到如下破解页面，你自己再研究研究　　

　　　　Results　4　of　about　ExcelRecovery　　　

　　　　URL：　http：//cracks。am/cracks/e4。html　　　



　　　　　o　ExcelRecovery　v3。0　　　



　　　　　o　ExcelRecovery　v2。2　　　



　　　　URL：　http：//thecrack：8080/db/list。php。。。　　



　　　　　o　2064。　ExcelRecovery　2。2　12　Kb　　　



　　　　　o　2065。　ExcelRecovery　2。2。1　12　Kb　　　



　　　　　o　2066。　ExcelRecovery　v3。0　（SirCrack）　12　Kb　　　



　　　　URL：　http：//crck。passwd。ws/e。htm　　　



　　　　　o　ExcelRecovery　v3。0　11　Kb　　　



　　　　URL：　http：//cracks。allseek。info/crc/？p=E5　　　



　　　　　o　1255。　ExcelRecovery　3。0　by　SirCrack　＇　11　Kb　＇　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　356　　


…………………………………………………………Page　357……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　显示隐藏的工作表　　



有个朋友给了我一个　EXCEL　程序，里面只有　2　个　SHEET，但是我见到其中一个　SHEET　还引　

用了本文件另外一个工作表的内容，我用　ALT＋F11　打开　VB　编辑器又可以看到那个隐藏了的　

工作表，　　　　

请问怎么能看到那工作表呢？　　

答：Sheets（〃name_of_sheet〃）。Visible　=　True　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　这样一列如何筛选出含　201　的　　　



县一高　2014555　/便民　201　号　　/县城　301　号/201414441/301745555/20145122　/柏良　201　/柏良　301　　

答：假设你的资料在　B　列，且起始行为第四行，即　B4　起始单元格，终止与　11　行处，即　B11　，　

则在　A　列输入公式　IF（ISERROR（FIND（〃201〃；B4））；A3；A3＋1）；然后在　D　列输入顺序数字，比如　1　

到　10，在　E　列输入公式　vlookup（d4；a4：b11；2；1》2）　。注意A3　为　0　。或：自动筛选…》单击下　

拉列表…》custom（　自定义）…》在条件中选〃包含〃；在右边的文字框输入〃201〃…》OK　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　两个日期相差的月份数　　



使用　Datedif（　日期1，日期2　，〃m〃）函数。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　用函数实现连续相加　　



我有一公式是这样的：=sum（B1＋B2＋B5＋B8＋B11＋B14＋B17＋B20）　　　　

也就是前两个是连续相加，后边的是每隔　3　个相加，不知有没有简单的公式。　　

答：｛=SUM（（MOD（ROW（B1：B20）；3）=2）*B1：B20）＋B1｝　　



　　　　　　　　　　　　　　　把计算结果为负值的显示为红色取整并在数字后面加上“仟元“　　



把单元格格式改为#；##0〃仟元〃；＇红色＇…#；##0〃仟元〃　　

如果不要负号可以写成#；##0〃仟元〃；＇红色＇#；##0〃仟元〃　　



　　　　　　　　　　　　　　　　　　　　　比较A、B　两列数据并在　A　列中包含　B　列的数据删除　　



工作表中　A　列数据是原始数据