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

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



公式或一个数值表达式，参数　2　和参数　3　可以是显示一个字符串、显示一个数值或显示某一公　

式的计算结果。参数之间用半角的“，”隔开，字符串也要用半角引号括住。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　429　　


…………………………………………………………Page　430……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　设工资表中“应纳税所得额”在　E　列，“应交个人所得税”在　H　列。我们要在　H　列的各行　

设置　IF　函数公式，由函数公式来对　E　列各行的应纳税工资进行判断，并自动套用适用税率和　

速算扣除数计算应纳税额。可在　H　列设置函数（以第2　行为例）：　　

　　　　IF（E2　》　100000，E2*0。45…15375，IF　（E2　》　80000，E2*0。40…10375，IF（E2　》　60000，　

E2*0。35…6375，IF（E2　》　40000，E2*0。30…3375，IF（E2　》　20000，E2*0。25…1375，IF（E2　》　5000，　

E2*0。20…375，IF（E2　》　2000，E2*0。15…125，IF（E2　》　500，E2*0。10…25，E2*0。05　）　）　）　）　）　）　）　）。　　

　　　　该函数十分亢长，其中嵌套了　7　个同样的　IF　函数，从第　2　个　IF　函数开始到最后是第　1　个　

嵌套函数，从第　3　个　IF　开始到最后是第　2　个嵌套函数〃〃，为帮助理解，我们将这些嵌套函　

数分别设为　X1、X2、〃〃于是将整个函数简化如下：　　

　　　　IF　（E2　》　100000，E2*0。45…15375，X1）　　

　　　　该函数意为：当E2　中工资额大于100000元时，H2　中计算出的应纳所得税额为E2*45％…15375，　

否则（指当工资额等于或小于　100000　元时），则　H2　应按　X1　的方法计算；把X1　展开：　　

　　　　IF　（E2　》　80000，E2*0。40…10375，X2）　　

　　　　其含义与上面相仿。最后一个嵌套函数　X7　展开为：　　

　　　　IF　（E2　》　500，E2*0。10…25，E2*0。05）　　

　　　　该函数意为：当　E2　中工资额大于　500　元时，H2　中计算出的应纳所得税额为E2*10％…25，否　

则（当工资额等于或小于　500　元时），H2　等于　E2*5％。　　

　　　　怎么样，明白了吗？够复杂的，好在计算个人所得税只有一种规定，将别人的公式搬过来　

用就是了。一般单位个人的工资不会高的太吓人，我们可根据人员的实际工资水平减少函数的　

嵌套数目简化运算。　　

　　　　学会灵活使用　IF　函数（图略）　　

　　　　从以上计算个人所得税的例子中可以看出，学会在　Excel　的工作表中运用这个函数设定各　

种条件，可实现许多特定的操作要求，起到事半功倍的作用。本文再举几个实例来讲解　IF　函　

数的使用方法，读者们可举一反三，在自己的　Excel　工作表中灵活运用。　　

例一　　

　　　　图一是一份会计记帐凭证，为了判断输入的数据借贷是否相等，我们在A8　中利用　IF　函数　

进行检测，选定　A8，双击“=”　，输入函数：　　

　　　　=IF　（C8＜＞D8，〃借贷不平〃，〃合　计〃）　　

　　　　当　C8=D8　时，不符合参数　1　的条件，在A8　中即显示参数　3　中的字符串“合计”　，当　C8≠　

D8　时，符合参数　1　的条件，A8　中显示的就是参数　2　中的字符串“借贷不平”，给予提示　　

例二　　

　　　　在图三的表中，要求当　C5：C12　区域中的数值为正数时，在D5：D12　区域中用相同的正数　

反映，当　C5：C12　区域中的数值为负数时，则要在　E5：E12　区域中用它们的绝对值反映。　　

　　　　在　D5　中建立函数如下：　=IF　（C5＞0，C5，〃0〃　）　　

　　　　在　E5　中建立函数如下：　=IF　（C5＜0，C5*　（－1），〃0〃　）　　

　　　　再将已建立的函数复制到其他相应的单元格中去即可。　　

　　　　如果在单元格中不想将“0”显示出来，则第三个参数可表示为“　〃　〃　”　　（如图四单元格　

E5　所示）。　　

　　　　在图五表中如果只要求计算盈利企业的利税总额则可在　F　列各单元格中建立函数如下（以　

F2　为例）：　　

　　　　=IF　（B2＞0，SUM　（B2：E2），〃〃　）　　

　　　　当　IF　函数的参数　1　中含有两个或两个以上的检测条件时，就要在参数中嵌套另外两个逻辑　

函数AND　和　OR　了。　　

例四　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　430　　


…………………………………………………………Page　431……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　设单元格　A4　的数值只能严格为正小数，当A4　符合此条件时，则在　B4　中显示该数值，否则　

提示错误信息。从　A4　的条件看它应同时满足＞0　且＜1　的要求，因而应结合运用AND　函数，在　

B4　中建立函数如下：　　

　　　　=IF　（AND　（0＜A4，A4＜1），A4，〃数值超出范围〃　）　　

例五　　

　　　　设单元格　A4　的数值应该是绝对值大于　10　的任何数值，当A4　符合此条件时，则在　B4　中显　

示该数值，否则提示错误信息。这里　A4　的条件既可＞10　又可＜－10，两个条件只要满足一个　

即可，因而应结合　OR　函数，在B4　中建立函数如下：　　

　　　　=IF　（OR　（10＜A4，A4＜－10），A4，〃数值超出范围〃　）　　

　　　　从以上例子可以看出，IF　函数是一个十分有用的函数，要用　Excel，不可不学　IF　函数。本　

文所举例子并不能包括它的所有用法，相信大家一定能发现　IF　函数的更多功能。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　如何计算表达式　　



比如，我在一个单元格（a1）内输入　　“　　　15＋1＋6　”，然后在另一个单元格（a2）内输入　　“　　=函数（a　

1）”　　

让　Excel　自动计算出　a1　　的结果　22　并显示在　a2　　中？请问　Excel　有这样的函数吗？　　



解答：将鼠标定位在　B1，插入→名称→定义：x=EVALUATE（A1）　，在B1　输入“＝X　”往下拖　

即可。这样，A　列为表达式，B　列为运算结果。　　



　　　　　　　　　　　　　　　　　　　　　　　如何求得某行或列不重复数字的个数　　



　　　解答：　　B5：Z5　行中不重复数字的个数：输入=SUM（1/COUNTIF（B5：Z5；B5：Z5））…1　后按　

CTRL＋SHIFT＋Enter　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　Excel　中　“摄影”的妙用　　



　　　1、　　准备“照相机”　　

　　　1）　　打开　Excel　的“工具”菜单，选择“　自定义”对话框。　　

　　　　　　　2）　　单击“命令”选项卡，在“类别”中选择“工具”，在右边“命令”列表中找到“摄影”，并　

且将其拖到工具栏的任意位置。　　

　　　　　　　2、给目标“拍照”　　

　　　　　　　假设我们要让　Sheet2　中的部分内容自动出现在Sheet1　中。　　

　　　　　　　1）　　拖动鼠标并且选择　Sheet2　中需要“拍照”的内容。　　

　　　　　　　2）　　用鼠标单击工具栏上准备好的“照相机”按钮，于是这个选定的区域就被“拍”了下　

来。　　

　　　　　　　3、粘贴“照片”　　

　　　　　　　1）　　打开　Sheet1　工作表。　　

　　　　　　　2）　　在需要显示“照片”的位置上单击鼠标左键，被“拍摄”的“照片”就立即粘贴过来了。　　

　　　　　　　在　Sheet2　中调整“照片”的各种格式，粘贴到　Sheet1　中的内容同步发生变化，而且因　

为插入的的确是一幅自动更新的图像文件，所以，“　图片”工具栏对这个照片也是有效的　　

　　　作者在此提供另一种方法：先复制要实现同步链接的图片，然后选中要另存的区域，按住　

SHIFT　键的同时选中编辑菜单中的粘贴链接图片，这样达到的效果是同样的，也会即时更新。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　431　　


…………………………………………………………Page　432……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　放大　USERFORM　幕相同大小　　



这样可以　　

Private　Sub　UserForm_Initialize（）　　

Application。WindowState　=　xlMaximized　　

　　　　　　With　Me　　

　　　　　　　　　　。Top　=　Application。Top　　

　　　　　　　　　　。Left　=　Application。Left　　

　　　　　　　　　　。Height　=　Application。Height　　

　　　　　　　　　　。Width　=　Application。Width　　

　　　　　　End　With　　

End　Sub　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　如何用　excel　做双对数坐标图　　



你可以先按一般刻度坐标作图；然后右击图表区的网格线；在右键菜单中选择〃网格线格式〃；然　

后选择对数刻度即可。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　使用　excel　拨接连上网　　



以下宏可以实现拨号连接上网：　　

Declare　　Function　　RasEnumEntries　　Lib　　〃RasApi32。DLL〃　　Alias　　〃RasEnumEntriesA〃　　（ByVal　　

reserved　As　String；　ByVal　lpszPhonebook　As　String；　lprasentryname　As　Any；　lpcb　As　Long；　　

lpcEntries　As　Long）　As　Long　　

Const　RAS_MAXENTRYNAME　=　256　　

Type　RASENTRYNAME　　

dwSize　As　Long　　

szEntryName（RAS_MAXENTRYNAME）　As　Byte　　

End　Type　　

Sub　　连线项目（）　　

Dim　s　As　Long；　l　As　Long；　ln　As　Long；　ab　As　String　　

Dim　R（255）　As　RASENTRYNAME　　

R（0）。dwSize　=　LenB（R（0））　　

s　=　RAS_MAXENTRYNAME　*　R（0）。dwSize　　

Call　RasEnumEntries（vbNullString；　vbNullString；　R（0）；　s；　ln）　　

If　ln　=　0　Then　　

MsgBox　〃无拨接网路帐号！〃　　

Else　　

MsgBox　〃已找到拨接网路帐号！〃　　

For　l　=　0　To　ln　　1　　

ab　=　StrConv（R（l）。szEntryName（）；　vbUnicode）　　

MsgBox　〃拨接网路名称　　：　〃　&　Left（ab；　InStr（ab；　Chr（0））　　1）　　

Next　　

End　If　　

End　Sub　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　432　　


…………………………………………………………Page　433……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　获取硬盘的序列号　　



Sub　xiaogang（）　　'获取硬盘的序列号　　

Dim　drive　　

drive　=　〃d：〃　　

MsgBox　　　　　　　　UCase（Replace（drive；　　　　　　　　　　　〃：〃；　　　　〃〃））　　　　＋　　　　　　〃　　　　盘　　的　　序　　列　　号　　是　　　　　　　　　　　　　　　　　　〃　　　　＋　　

Format（CreateObject（〃Scripting。FileSystemObject〃）。GetDrive（drive）。SerialNumber）　　

End　Sub　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　返回用户名　　



Public　Declare　Function　GetUserName　Lib　〃advapi32。dll〃　_　　

Alias　〃GetUserNameA〃　（ByVal　lpBuffer　As　String；　nSize　As　Long）　As　Long　　

　　

Function　ReturnUserName（）　As　String　　

'　returns　the　NT　Domain　User　Name　　

Dim　rString　As　String　*　255；　sLen　As　Long；　tString　As　String　　

　　　　　　tString　=　〃〃　　

　　　　　　On　Error　Resume　Next　　

　　　　　　sLen　=　GetUserName（rString；　255）　　

　　　　　　sLen　=　InStr（1；　rString；　Chr（0））　　

　　　　　　If　sLen　》　0　Then　　

　　　　　　　　　　tString　=　Left（rString；　sLen　　1）　　

　　　　　　Else　　

　　　　　　　　　　tString　=　rString　　

　　　　　　End　If　　

　　　　　　On　Error　GoTo　0　　

　　　　　　ReturnUserName　=　UCase（Trim（tString））　　

End　Function　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　将　UserForm　与　Excel　分开　　



Option　Explicit　　

Private　　Declare　　Function　　SetWindowPos　　Lib　　〃user32〃　　（ByVal　　hwnd　　As　　Long；　　ByVal　　

hWndInsertAfter　As　Long；　ByVal　X　As　Long；　ByVal　Y　As　Long；　ByVal　cx　As　Long；　ByVal　cy　As　　

Long；　ByVal　wFlags　As　Long）　As　Long　　

Private　Declare　Function　FindWindow　Lib　〃user32〃　Alias　〃FindWindowA〃　（ByVal　lpClassName　　

As　String；　ByVal　lpWindowName　As　String）　As　Long　　

Private　Declare　Function　ClipCursor　Lib　〃user32〃　（lpRect　As　Any）　As　Long　　

Private　Declare　Function　SetForegroundWindow　Lib　〃user32〃　（ByVal　hwnd　As　Long）　As　Long　　

Const　ID　=　〃123〃　　

　　

Private　Sub　mandButton1_Click（）　　

If　TextBox1　=　ID　Then　　

　　　　　　Unload　UserForm1　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　433　　


…………………………………………………………Page　434……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　　　Exit　Sub　　

Else　　

　　　　　　UserForm1。Height　=　UserForm1。Height　30　　

　　　　　　UserForm1。Width　=　UserForm1。Width　30　　

　　　　　　TextBox1　=　〃〃　　

　　　　　　TextBox1。SetFocus　　

End　If　　

If　UserForm1。Height　《　80　Then　　

　　MsgBox　〃不知道密碼就不要再撐了！按〃〃X〃〃離開吧！〃　　

End　If　　

End　Sub　　

　　

Private　Sub　mandButton2_Click（）　　

　　　　　　TextBox1　=　〃〃　　

　　　　　　TextBox1。SetFocus　　

End　Sub　　

　　

Private　Sub　UserForm_Initialize（）　　

Application。EnableCancelKey　=　xlDisabled　　

SetWindowPos　hWndForm；　…1；　0&；　0&；　0&；　0&；　3　　　　　　'讓視窗保持最上層　　

Call　SetForegroundWindow（hWndForm）　　　　'讓視窗取得焦點　　

　　

End　Sub　　

Function　hWndForm（）　As　Long　　

　　　　　　　　hWndForm　=　FindWindow（〃ThunderDFrame〃；　UserForm1。Caption）　'UserForm　　

End　Function　　

　　

　　

Private　Sub　UserForm_QueryClose（Cancel　As　Integer；　CloseMode　As　Integer）　　

If　Not　TextBox1　=　ID　Then　　

ThisWorkbook。Saved　=　True　　

ThisWorkbook。Close　　

End　If　　

SetWindowPos　hWndForm；　…2；　0&；　0&；　0&；　0&；　3　　

Application。EnableCancelKey　=　xlInterrupt　　

End　Sub　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　如何用　API　获得当前登录用户　　



Option　Explicit　　

Private　Declare　Function　GetUserName　Lib　〃advapi32。dll〃　Alias　〃GetUserNameA〃　_　　

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　（ByVal　lpBuffer　As　String；　_　　

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　nSize　As　Long）　As　Long　　

　　

Sub　Get_User_Name（）　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　434　　


…………………………………………………………Page　435……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　　　　　

　　　　　　Dim　lpBuff　As　String　*　25　　

　　　　　　Dim　ret　As　Long；　UserName　As　String　　

　　　　　　ret　=　GetUserName（lpBuff；　25）　　

　　　　　　UserName　=　Left（lpBuff；　InStr（lpBuff；　Chr（0））　　1）　　

　　　　　　MsgBox　UserName　　

　　　　　　End　Sub　　



　　　　　　　　　　　　　　　　　　　　　　　　　　怎样才能实现在输入重复的数据时自动跳到重复处提示呢　　



方法　1：在数据/有效性/　自定义中输入此公式：　　　=COUNTIF（A：A；A1）=1　当输入相同号码时；会拒　

绝输入。　　

方法　2　：Private　Sub　Worksheet_Change（ByVal　Target　As　Range）　　

　　　　On　Error　Resume　Next　　

　　　　If　Target。Column　=　　1　Then　　

　　　　　　　　If　Target。value　　〃〃　Then　　

　　　　　　　　　　　　Set　c　=　Sheet1。Range（〃A1：A〃　&　Target。Offset（…1；　0）。Row）。Find（Target。value；　LookIn：=xlval　

ue；　Lookat：=xlWhole）　　

　　　　　　　　　　　　If　Not　c　Is　Nothing　Then　　

　　　　　　　　　　　　　　　　MsgBox　〃该号码已有了〃　　

　　　　　　　　　　　　　　　　c。Select　　

　　　　　　　　　　　　　　　　Target。value　=　〃〃　　

　　　　　　　　　　　　End　If　　

　　　　　　　　End　If　　

　　　　End　If　　

End　Sub　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　用公式表示当前单元格所在行的第一列的值　　



解答：cells（所在行行号，1）。value　或者用公式=INDIRECT（〃a〃&ROW（））　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　怎样将列号，转变为对应的字母　　



如：列号　9，对应的字母应该是〃I〃；列号　27　，对应的字母应该是〃AA〃。　　

解答：Function　ColumnLetter（ColumnNumber　As　Integer）　As　String　　

　　　　If　ColumnNumber　《　　1　Or　ColumnNumber　》　256　Then　　

　　　　　　　　MsgBox　〃Invalid　Column　Number〃　　

　　　　　　　　Exit　Function　　

　　　　ElseIf　ColumnNumber　》　26　Then　　

　　　　　　　　ColumnLetter　=　Chr（Int（（ColumnNumber　　1）　/　26）　＋　64）　&　_　　

　　　　　　　　　　　　Chr（（（ColumnNumber　　1）　Mod　26）　＋　65）　　

　　　　Else　　

　　　　　　　　　　　　ColumnLetter　=　Chr（ColumnNumber　＋　64）　　

　　　　End　If　　

End　Function　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　435　　


…………………………………………………………Page　436……………………………………………………………

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　



　　　　　　　　　　　　　　　　　　　　　　EXCEL　能否按照单元格内文字的颜色排序　　



EXCEL　能否按照单元格内文字的颜色排序，或把相同颜色的行集中到一起显示。　　

　　　　解答：方法一，1。以VBA　判斷　colorindex，加輔助欄實現；2。不以程式輔助要實現………》　

不可能。　　

　　　　方法二，不用　VBA　也可以的；可用　get。cell（24）　　EXCEL　宏函数定义名称；辅助列还是需　

要的。　　



　　　　　　　　　　　　　　　　　　　怎么能让一个加载宏监控所有打开的　excel　文件　　



我们可以在　thisworkbook_open　或　sheet1_activate　中加入自己的代码从而监视本文件中的各　

个事件，执行指定的代码。但如果你编写的是一个加载宏，你所要监视的文件就不单单是当　

前的文件了，而是所有打开的文件。但加载宏在后台运行时，用户可能会新打开或关闭文件　

等执行各种操作，从而可能会出现错误。　　

为了实现加载宏在后台对所有操作进行监控，我把　Excel　帮助翻个底朝天，终于实现了这种效　

果。这几天看到有些朋友也存在这中问题，将自己的一点心得拿出来与大家分享。　　

要实现这种效果，首先要定义一个　Application　类　　

在　VBA　项目中添加一个类模块　AppEventCls　，进行声明：　　

Public　WithEvents　App　As　Application　　

这时在代码编辑窗口上面的对象下拉框中就多出了一个新的对象“App”　，选中“App”　，右边的事　

件下拉框中可以看到对应的事件“NewWorkbook”　，“SheetActivate”等，哈！这就是我们所要的！　

选中“NewWorkbook”　，在代码编辑窗口中出现：　　

Private　Sub　App_NewWorkbook（ByVal　Wb　As　Workbook）　　

End　Sub　　

添加代码：　　

MsgBox　〃Hey！　You　opened　a　new　workbook！〃　　

现在可以执行了吗？噢，还要等一下，我们必须先定义一个属于这个类的对象。　　

在项目中添加一个新的模块　　

添加对象的定义　　

Dim　MyApp　As　New　AppEventCls　　

在自动运行过程中指定对象　　

Public　Sub　Auto_Open（）　　

　　　　　　Set　MyApp。App　=　Application　　

End　Sub　　

将文件存为加载宏，如“ControlApp。xla”　，然后就可以欣赏自己的成果了！　　

关闭此文件，然后加载刚保存的加载宏。打开一个文件试试。　　

在类模块中的其他事件中加入代码试试，可以看到这个加载�