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

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



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



***************************　　　　

Sub　DD（）　　　　

On　Error　GoTo　999　　　　

Range（〃H2：H200〃）　=　〃〃　　　　

For　I　=　　1　To　99　'假設有產品編號　99　種　　　　

A　=　〃0〃　&　Trim（Str（I））　'A001~A020　　　　

Range（〃B：B〃）。Select　'　　　　

Cells。Find（What：=A；　After：=ActiveCell；　LookIn：=xlFormulas；　LookAt　_　　　　

：=xlPart；　SearchOrder：=xlByRows；　SearchDirection：=xlUp；　MatchCase：=　_　　　　

False）。Activate　'找最後一筆　　　　

B　=　ActiveCell　　　　

If　Cells（ActiveCell。Row；　6）　=　1　Then　'是否為連續　3　筆　PASS　　　　

Range（〃H65535〃）。End（xlUp）。Offset（1）。Select　'找最後一筆　　　　

ActiveCell　=　B　'免檢資料放在　H　欄　　　　

End　If　　　　

Next　I　　　　

999　　　　

＇A1＇。Select　　　　

End　Sub　　　　

方法　3　：　　

小修改；不使用公式；只使用VBA　　　　

Sub　DD（）　　　　

On　Error　GoTo　999　　　　

Range（〃H2：H200〃）　=　〃〃　　　　

For　I　=　1　To　99　'假设有产品编号　99　种　　　　

A　=　〃0〃　&　Trim（Str（I））　'A001~A099　　　　

Range（〃B：B〃）。Select　'产品编号　　　　

Cells。Find（What：=A；　After：=ActiveCell；　LookIn：=xlFormulas；　LookAt　_　　　　

：=xlPart；　SearchOrder：=xlByRows；　SearchDirection：=xlUp；　MatchCase：=　_　　　　

False）。Activate　'找最后一笔　　　　

If　ActiveCell。Row　》=　3　Then　　　　

B　=　ActiveCell　　　　

S1　=　UCase（ActiveCell。Offset（0；　3））　'最后第　1　笔来货结果　　　　

S2　=　UCase（ActiveCell。Offset（…1；　3））　'最后第　2　笔来货结果　　　　

S3　=　UCase（ActiveCell。Offset（…2；　3））　'最后第　3　笔来货结果　　　　

N1　=　ActiveCell。Offset（0；　0）　'最后第　1　笔产品编号　　　　

N2　=　ActiveCell。Offset（…1；　0）　　　　

N3　=　ActiveCell。Offset（…2；　0）　　　　

If　S1　=　〃PASS〃　And　S2　=　〃PASS〃　And　S3　=　〃PASS〃　And　N1　=　N2　And　N1　=　N3　Then　'是否为连续　

3　笔　PASS　　　　

Range（〃H65535〃）。End（xlUp）。Offset（1）。Select　'找最后一笔　　　　

ActiveCell　=　B　'免检资料放在　H　栏　　　　

End　If　　　　

End　If　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　422　　


…………………………………………………………Page　423……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



Next　I　　　　

999　　　　

Range（〃A1〃）。Select　　　　

End　Sub　　　　

方法　4　：　　

Sub　checkparts（）　　　　

'On　Error　Resume　Next　　　　

Dim　checksize　As　Integer　　　　

Dim　samePN　As　Integer　　　　

Dim　id　As　Integer　　　　

Dim　acceptcount　As　Integer　　　　

Dim　firstPN；　nextPN　As　String　　　　

Dim　vendor　As　String　　　　

checksize　=　InputBox（〃Enter　the　number　consecutive　records　to　check　for　each　PN〃）　　　　

Columns（〃G：I〃）。Select　　　　

Selection。ClearContents　　　　

Range（〃B1〃）。Select　　　　

id　=　1　　　　

While　ActiveCell。Value　　〃〃　　　　

firstPN　=　Trim（ActiveCell。Value）　　　　

samePN　=　1　　　　

nextPN　=　Trim（ActiveCell。Offset（samePN；　0）。Value）　　　　

vendor　=　Trim（ActiveCell。Offset（0；　3）。Value）　　　　

Do　While　nextPN　=　firstPN　　　　

samePN　=　samePN　＋　1　　　　

nextPN　=　Trim（ActiveCell。Offset（samePN；　0）。Value）　　　　

Loop　　　　

If　samePN　》=　checksize　Then　　　　

For　acceptcount　=　0　To　checksize　　1　　　　

If　UCase（Trim（ActiveCell。Offset（acceptcount；　2）。Value））　　〃ACCEPTED〃　Then　Exit　For　　　　

Next　acceptcount　　　　

If　acceptcount　=　checksize　Then　　　　

Cells（id；　7）　=　id　　　　

Cells（id；　8）　=　firstPN　　　　

Cells（id；　9）　=　vendor　　　　

id　=　id　＋　1　　　　

End　If　　　　

End　If　　　　

ActiveCell。Offset（samePN）。Select　　　　

Wend　　　　

Columns（〃G：I〃）。Select　　　　

With　Selection。Font　　　　

。Name　=　〃Arial〃　　　　

。Size　=　9　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　423　　


…………………………………………………………Page　424……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



。Strikethrough　=　False　　　　

。Supers　cript　=　False　　　　

。Subs　cript　=　False　　　　

。OutlineFont　=　False　　　　

。Shadow　=　False　　　　

。Underline　=　xlUnderlineStyleNone　　　　

。ColorIndex　=　xlAutomatic　　　　

End　With　　　　

With　Selection　　　　

。HorizontalAlignment　=　xlLeft　　　　

。VerticalAlignment　=　xlBottom　　　　

。WrapText　=　False　　　　

。Orientation　=　0　　　　

。AddIndent　=　False　　　　

。ShrinkToFit　=　False　　　　

。MergeCells　=　False　　　　

End　With　　　　

Selection。Columns。AutoFit　　　　

Range（〃G1〃）。Select　　　　

ActiveWindow。SmallScroll　ToRight：=2　　　　

End　Sub　　



　　　　　　　　　　　　　　　　　　　　　如何用不同颜色的字体，将手式输入与公式计算结果区分开来　　



解答　1：可以用条件格式，如　A1　格，则条件格式公式如下：　　=CELL（〃contents〃；A1）　　

解答　2　：用自定义函数了：　　

　Function　IsFormula（cl）　　

adrs　=　cl。Address　　

If　Left（Range（adrs）。Formula；　1）　=　〃=〃　Then　　

IsFormula　=　True　　

Else　　

IsFormula　=　False　　

End　If　　

End　Function　　　

条件格式公式为：　　

=IsFormula（A1）　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　怎样在电话号码书中提取手机号码　　



解答：假设　a1=3322211；a2=13322121122；a3=2503225　　

a4=｛=sum（（len（a1：a3）=11）*（a1：a3））｝　　

完善：建议再加一个第一位是〃1〃的判断条件；　以免长途超过　11　位的情况　　

｛=SUM（（LEN（A1：A3）=11）*（A1：A3）*（LEFT（A1：A3；1）=〃1〃））｝　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　424　　


…………………………………………………………Page　425……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　是不是用数组函数解决？请教各位　　



a1=aa；　b1=apple　　

a2=bb；b2=orange　　

。。。。。　　

if（a11=aa；　则　b11=apple　　

if（a11=bb；　则　b11=orange　　

。。。。。。　　

是不是用数组函数解决？请教各位。　　

解答：　　　

如果对应关系比较多，先在一个工作表中分两列输入查找值及查找结果，如在工作表　SHEET2　

中的A　列输入查找值，B　列输入对应的结果，在另一个工作表的　B1　格中用公式　　

=VLOOKUP（A1；SHEET2！A：B；2；0）　　

即可按该表的A1　格中的值，在　sheet2　表的　A　列中查找并返回对应　B　列的值。　　

如果对应关系不多，可用公式　　

=VLOOKUP（A1；｛〃aa〃；〃apple〃；〃bb〃；〃orange〃；。。。。。。｝；2；0）　　

注意其中的；号。　　　

　　　　又问：在　SHEET1　！B2　输入　　

　　　　=IF（A1=〃〃；〃〃；if（没有匹配；〃这是新货号〃；vlookup（a1；sheet2！a：b；2；0）））　　

　　　　如何实现上面要求。（没有匹配；〃这是新货号〃；）　　

解答：ISNA（VLOOKUP（。。。。））　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　关于日期的格式化问题　　



我要接收汇总数据；出生年月有的写成“58。5　　”有的写成〃58。05〃　　我现在需要统一为　1958。05（常　

规型）怎样实现。　　

解答：　

=value（IF（LEN（A1）》5；LEFT（A1；4）&〃。〃&TEXT（RIGHT（A1；LEN（A1）…5）；〃00〃）；〃19〃&LEFT（A1；2）　

&〃。〃&TEXT（RIGHT（A1；LEN（A1）…3）；〃00〃）））　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　列的跳跃求和　　



若有　20　列（只有一行），需没间隔　3　列求和，该公式如何做？　　　

前面行跳跃求和的公式不管用。　　　

解答：假设　a1　至　t1　为数据（共有　20　列），在任意单元格中输入公式：　

=SUM（IF（MOD（TRANSPOSE（ROW（1：20））；3）=0；（a1：t1））　　。按　ctrl＋shift＋enter　结束即可求出　

每隔三行之和。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　EXECL　默认起始工作表的函数　　



我编制的　Exexl　工作薄中有若干个工作表；名称分别为〃参数设置〃、“凭证汇总”、“报表”等等。　

我的问题是，在任意一个工作表名下保存工作薄后，下次重新打开时，首页默认是“参数设置”。　

请问怎么样设置。　　

解答：Private　Sub　Workbook_Open（）　　

Sheets（〃参数设置〃）。Select　　

End　Sub　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　425　　


…………………………………………………………Page　426……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　怎样一次性把科学记数改成文本？　　



解答：b1=TEXT（A1；〃#〃）　。向下填充（#号格式符，代表数字）　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　excel　的六大“条件”功能　　　　



　　　　EXCEL97/2000，除了具有强大的表格功能外，更具有强大的数据统计与处理功能，尤其　



是使用其　“条件”功能，常常能收到事半功倍的效果，在此笔者就同大家谈谈　EXCEL　的条件　



功能（为方便起见，笔者在此以如图　1　包含工程基本情况的二维表格为例）。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

　　　　　　　　一、条件求和。　　　

　　　　1、单条件求和：统计　c1　公司施工的工程总建筑面积，并将结果放在　e18　单元格中，我们　

只要在　e18　单元格中输入公式“=sumif　（d2：d17；〃c1　公司〃；e2：e17）”即完成这一统计。　　　

　　　　友情提醒：如果对　excel　的函数不太熟悉，在单元格中直接输入公式有困难，我们可以用　

　“插入函数”命令（或直接按工具栏上的“粘贴函数”命令按钮），选中你需要的函数后，按　

其提示操作即可完成公式的输入。　　　

　　　　2、多条件求和：统计　c2　公司施工的质量等级为“合格”的工程总建筑面积，并将结果放　

在　e19　单元格中，我们用“条件求和”功能来实现：　　

　　　　①选“工具→向导→条件求和”命令（若没有此命令选项，可以用“加载宏”的方式来增　

加这一命令选项），在弹出的对话框中，按右下带“―”号的按钮（此时对话框变成类似工具　

条的窗口形式浮于桌面上），用鼠标选定　d1：i17　区域，并按窗口右边带红色箭头的按钮（恢　

复对话框状态）。　　　

　　　　②按“下一步”，在弹出的对话框中，按“求和列”右边的下拉按钮选中“建筑面积”项，　

再分别按“条件列、运算符、比较值”右边的下拉按钮，依次选中“施工单位”、“=”（默　

认）、“c2　公司”选项，最后按“添加条件”按钮。重复前述操作，将“条件列、运算符、　

比较值”设置为“质量等级”、“=”、“合格”，并按“添加条件”按钮。　　　

　　　　③两次点击“下一步”，在弹出的对话框中，按右下带“―”号的按钮，用鼠标选定　e19　

单元格，并按窗口右边带红色箭头的按钮。　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　426　　


…………………………………………………………Page　427……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　④按“完成”按钮，此时符合条件的汇总结果将自动、准确地显示在　e19　单元格中。　　　

　　　友情提醒：上述操作实际上是输入了一个数组公式，我们也可以先在　e19　单元格中直接输　

入公式：=sum（if（d2：d17=〃c2　公司〃；if（i2：i17=〃合格〃；e2：e17））），然后在按住　ctrl＋shift　

键（非常关键！！！）的同时按下　enter　键，也可以达到上述多条件求和之目的。　　　

　（“条件求和”的另一招。。。　=SUMPRODUCT（B9：C14；MOD（B9：C14＋1；2））　　　



用　Sunif（）　设置求和条件比较困难的时候，可以考虑上面的公式：对　B9：C14　　中的偶数进行求　

和。　　



扩展：可以考虑将　MOD（）　替换成相应的条件。）　　



　　　　二、条件计数。　　　

　　　　统计质量等级为“合格”工程的数目，并将结果存放在　i18　单元格中，在　i18　单元格中输　

入公式：=countif（i2：i17；〃合格〃），当按下确定按钮后，统计结果――数字　5　即自动在　i18　

单元格中显示出来。　　　

　　　　三、条件格式。　　　

　　　　将工程造价在　500　万元（含　500　万元）以上的工程造价数值以红颜色显示出来：　　　

　　　　①选中　f2　至　f17　单元格；　　　

　　　②用“格式→条件格式”命令，打开“条件格式”对话框；　　　

　　　　③按第二个方框旁的下拉按钮，选中“大于或等于”选项，再在后面的方框中输入数字　

500；　　　

　　　④按上述对话框中的“格式”按钮，打开“单元格格式”对话框，在“文字”卡片下，按　

　“颜色”旁的下拉按钮，将文字颜色设置成红色后，按“确定”按钮关闭“单元格格式”对话　

框，回到“条件格式”对话框中；　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

　　　　⑤按“确定”按钮。　　　

　　　　此时造价大于　500　万元的工程，其造价数值将以红色显示在单元格中。　　　

　　　　友情提醒：继续按上述“条件格式”对话框中的“添加”按钮，可以设置多级“条件”，　

如“工程造价大于　500　万元以红色显示，大于　1000　万元以蓝色显示”等（可以对一个单元格　

设置三个条件）。　　　

　　　　四、条件函数。　　　

　　　　我们在对竣工工程观感质量进行评定后，当观感得分率超过　85％，该工程质量等级可以评　

定为“优良”。　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　427　　


…………………………………………………………Page　428……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　①在　i2　单元格中输入公式：=if（h2》=85；〃优良〃；if（h2》0；〃合格〃；〃未竣工〃））；　　　

　　　　②选中　i2　单元格，将鼠标移至该单元格右下角成细十字线时（我们称之为“填充柄”），　

按住左键向下拖拉，将上公式复制到　i　列下面的单元格中；　　　

　　　　③以后当工程竣工后，我们将该工程的观感得分率填入　h　列内相应的单元格中，则质量等　

级自动显示在相应工程的　i　列单元格中（若没有填观感得分率，则　i　列相应单元格中显示“未　

竣工”字样）。　　　

　　　　友情提醒：在第①步输入公式时，条件判断数字按从大到小的顺序排列，否则会出错。　　　

　　　　五、条件查找。　　　

　　　　当工程基本情况表中登记的工程非常之多时，如果我们用普通浏览的方式查找某个具体的　

工程是非常困难的，此时我们可以用“查找”功能来快速定位：　　　

　　　　①　用“编辑查找”命令，打开“查找”对话框；　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

　　　　②在“查找内容”下面的方框中输入该工程的关键词；　　　

　　　　③对“搜索方式、搜索范围”作适当设置后，连续按“查找下一个”按钮，符合模糊条件　

的单元格依次被选中，即可快速查找到某个具体的工程。　　　

　　　　友情提醒：在“查找内容”下面的方框中输入的内容不一定非得与某一单元格数值完成相　

同的文本，如查找“c1　公司”的某个工程，只要输入“c1”即可以进行模糊查找了。　　　

　　　　六、条件筛选。　　　

　　　　如果要打印　c1　公司施工的工程基本情况一览表，我们可以用“自动筛选”功能来实现：　　　

　　　　①选中表格中的任一单元格，用“数据→筛选→自动筛选”命令，启动“自动筛选”功能　

　（列标题旁出现一个下拉按钮）；　　　

　　　　②按“施工单位”旁的下拉按钮，选中　c1　公司，则表格中只显示　c1　公司所施工的工程；　　　

　　　　③接上打印机，装上打印纸，按“打印”按钮，即可打印出　c1　公司施工的工程基本情况　

一览表；　　　

　　　④打印结束后，重复第①的操作关闭“自动筛选”功能，则全部工程又重新显示在表格中。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

　　　　友情提醒：此处的“条件筛选”可以进行“多级”筛选，如再上述筛选的基础上，再按“进　

度”旁的下拉按钮，选中“在建”选项，则表格中只剩下“c1　公司”施工的所有“在建”工　

程。　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　428　　


…………………………………………………………Page　429……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　其实，excel　的条件功能还有很多，如果有感兴趣的朋友不妨一试，若发现其什么更好的　

功能，别忘了告诉一声，我在此先谢过了。　　



　　　　　　　　　　　　　　　　　　　　　　　从个人所得税的计算谈　IF　函数的使用　　



　　　　最近，国家采取提高公务员和职工的工资水平，低收入阶层的收入的政策以启动消费推动　

经济增长，相当一部分人工资收入达到和超过了交纳个人所得税的水平。根据我国税法的规定，　

个人所得税是采用超额累进税率分段计算，　　

　　　　见表一：　　



　　　级数　　　　　　　　　　　　　　应纳税所得额　　　　　　　　　　　　　　　　税率　％　　　速算扣除数　　



　　　　1　　不超过　500　元的　　　　　　　　　　　　　　　　　　　　　　　　　　　5　　　　　　　　0　　



　　　　2　　超过　500　元　2000　元的部分　　　　　　　　　　　　　　　　　　10　　　　　　　　25　　



　　　　3　　超过　2000　元　5000　元的部分　　　　　　　　　　　　　　　　　15　　　　　　　125　　



　　　　4　　超过　5000　元　20000　元的部分　　　　　　　　　　　　　　　　20　　　　　　　375　　



　　　　5　　超过　20000　元　40000　元的部分　　　　　　　　　　　　　　　25　　　　　　　1375　　



　　　　6　　超过　40000　元　60000　元的部分　　　　　　　　　　　　　　　30　　　　　　　3375　　



　　　　7　　超过　60000　元　80000　元的部分　　　　　　　　　　　　　　　35　　　　　　　6375　　



　　　　8　　超过　80000　元　100000　元的部分　　　　　　　　　　　　　　40　　　　　　　10375　　



　　　　9　　超过　100000　元的部分　　　　　　　　　　　　　　　　　　　　　　45　　　　　　　15375　　



　　　　这时，用　Excel　的　IF　函数来进行计算是再方便不过的了。对原工资表只要增加“应纳税所　

得额”和“应交个人所得税”两列就可以，“应纳税所得额”根据“应发工资”数扣除　800　

元和其他可免税的部分计算（这一列也可省略），“应交个人所得税”设在工资表的应扣金额　

部分，将其象“代扣房租”等等项目一样作为工资表中的一个扣除项目，计算个人所得税的公　

式就设置在此列。　　

　　　　Excel　中的逻辑函数　IF　一个适用范围很广，功能极强的函数，IF　函数在工作表中的用途是　

用于对数值和公式进行条件检测，然后根据不同的检测结果，返回不同的结果（执行不同的操　

作命令）。它的这种功能用来判断应纳税所得额的适用税率再合适不过。　　

　理解　IF　函数在计算个人所得税中的用法：IF　函数的语法：IF　（参数1，参数　2，参数　3）　　

　　　　该函数的含义是在单元格中以参数　1　为条件进行检测，当检测结果符合参数　1　时，执行参　

数　2　的命令，反之则执行参数　3　的命令。其中：参数　1　为函数执行检测的条件，它一般是一个　

公式或一个数值表达式