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

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



*D1选择A1时，要求在G1　中自动跳出B1的内容，　选A2时，自动跳出B2的内容*余此类推。　　

解答：G1公式：=Vlookup（D1；A1：B9；2；0）　　　

又问：假设，有C列中也有数据，我要在G1　中显示C列中的数据，该怎么算？　　　

解答：G1公式：=Vlookup（D1；A1：B9；3；0）　　　

026。　　向上填充的快捷键是什么？我只会向下填充的快捷键，向上…向左…向右的都是什么呢？　　　

解答：向上…Alt＋E；I；U　。向左…Alt＋E；I；L　。向右…CTRL＋R　　　　

027。下方单元格上移，包含该单元格的公式不要变化　　

哪位高手帮帮忙！我试验了很久也没找到解决的办法：　　　

能不能做到删除单元格以后，下方单元格上移，包含该单元格的公式不要变化。或者是：按住　

shift拖动单元格，使两个单元格互相交换位置以后，包含该单元格的公式不要发生变化。注意，　

用加的办法是不能解决这个问题的，如公式改为：=SUM（A1：A9）；经上述操作后，结果　

还是一样。　　

解答：=SUM（INDIRECT（〃A1：A10〃））　　　　

新问题：但是还有一个问题：我这一列有2000多个数据，似乎不能通过拖动的办法将公式复　

制200遍，达到每10个1求和的结果。　　

解　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　答　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　：　

=IF（MOD（ROW（）；10）0；〃〃；SUM（OFFSET（INDIRECT（ADDRESS（ROW（）；COLUMN（）；））；；…1；…1　

0；）））　　　　

028。一列中删除重复数据的方法　　

例如在C2：C500　中有重复数据。在D2中　=COUNTIF（C2：C100；C2）　　计算出　C2在此列中的出　

现次数；然后复制公式到整列；最后删除在D列中大于1的行即可。　　　

029。哪为大侠来帮忙关于VBA　的问题　　

小弟想同时对excel工作簿下的几个工作表进行插入图表的操作！这几个工作表中已经在相同　

的位置区域内输入了数据。　　语言如下：　运行显示　〃下表越界〃　　　　　　　　　　　　　　　　　　　　　　　（下划线的地方）。请问高手　

又什么办法解决，或者可以用其它的方法。　　

sub　biaoge（）　　　　

for　a　=　1　to　3　　　　

sheets（〃sheet（a）〃）。select　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　CCXXXIII　　


…………………………………………………………Page　234……………………………………………………………

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



charts。add　　　　

activechart。applycustomtype　charttype：=xlbuiltin；　typename：=〃两轴线…柱图〃　　　　

activechart。setsourcedata　source：=sheets（〃sheet　（a）〃）。range（〃a1：j3〃）；　plotby：=xlrows　　　　

activechart。location　where：=xllocationasobject；　name：=〃sheet（a）〃　activechart。hasdatatable　　

=　true　　　

activechart。datatable。showlegendkey　=　true　　　　

activechart。legend。select　　　　

selection。delete　　　　

next　a　　　　

end　sub　　　　

解答：sheets（〃sheet（a）〃）。select是错的。可以用sheets（〃Sheet_Name〃）。select　。　　

029。比较大小　　

例如512。03；我用函数取了这个数的最后两个数03用他与10比较；结果总是显示03》10；不知道　

是什么原因；请高手指点；谢谢！！！　　　

解答：取后两位数结果是文本型；对比可用right（a1；2）*1》10或者用：value（right（a1；2））》10也　

可　　

030。讨论：用RANGE和CELLS选择单元格　　

EXCEL的基本元素就是单元格，第一步就是要学会操作单元格了，列举两种方式。　　

SUB　RANGE（）　〃用RANGE选择B5单元格　　　

RANGE（“B5”）。SELECT　　　　

END　SUB　　　　

SUB　CELLS（）　〃用CELLS选择B5单元格　　　

CELLS（5；2）。SELECT　　　　

END　SUB　　　　

RANGE编程时无法变化，CELLS可以通过变量选择单元格。　　

回应1：RANGE　　一样方便；　　甚至更方便。　　实际使用中可以用一变量　　

srArea=〃B〃　&　i　　　　

RANGE（srArea）。SELECT　　　　

srArea=〃金额〃　'　　一命名为金额　的单元格/　区域　　

RANGE（srArea）。SELECT　　　　

回应2：我觉得各有长处，如果有变量需要循环判断，用Cells相对比较简单，但是有时候固定　

区域的，命名后用Range更灵活。　　

回应3：没错。　　帮助中也是推荐　CELL　　的。　　　

灵活性来讲；　RANGE　　要强多了；　　而且使用时可以通过　。　　提取符快速读取它的属性和方法。　　　

另外；　　对于可变更的工作表；　　用　RANGE　　来操作命名区域将增加程序的弹性。　　　

比如工作中插入一行/列；　　VBA　　中用　CELL　　就可能导致运行操作错误；　　而　RANGE（srArea）　　作　

为指定区域；　　可适应单元格的这类变更。　　　

031。关于FileSystemObject的引用　　

请问各路高手，有人可以为我指点一下filesystemobject引用的详细说明，特别是fileexists方法　

的实例。　　　

解答：Sub　testing（）　　　　

'先判断文件是否存在，是则删除之　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　CCXXXIV　　


…………………………………………………………Page　235……………………………………………………………

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



Dim　strmyfile　As　String　　　　

strmyfile　=　〃d：book1。xls〃　　　　

If　filetoFind（strmyfile）　Then　　　　

Kill　strmyfile　　　　

End　If　　　　

End　Sub　　　　

Function　filetoFind（fileName　As　String）　As　Boolean　　　　

Dim　fsobj　As　Object　　　　

Set　fsobj　=　CreateObject（〃Scripting。FileSystemObject〃）　　　　

If　fsobj。fileexists（fileName）　Then　　　　

filetoFind　=　True　　　　

End　If　　　　

End　Function　　　　

在帮助文件中是这样描述的：FileSystemObject　　对象　　

描述：提供对计算机文件系统的访问。　　　

语法：Scripting。FileSystemObject　　　　

说明：下面的代码举例说明了如何使用　FileSystemObject　　返回一个　TextStream　　对象，该对　

象是可读并可写的：　　　

Set　fs　=　CreateObject（〃Scripting。FileSystemObject〃）　　　　

Set　a　=　fs。CreateTextFile（〃c：testfile。txt〃；　True）　　　　

a。WriteLine（〃This　is　a　test。〃）　　　　

a。Close　　　　

在上面列出的代码中，CreateObject　　函数返回　FileSystemObject　（fs）　。CreateTextFile　　方法接　

着创建文件作为一个　TextStream　　对象（a），而　WriteLine　　方法则向创建的文本文件中写入一　

行文本。Close　　方法刷新缓冲区并关闭文件。　　

FileExists　　方法　　

描述：如果指定的文件存在，返回　True　，若不存在，则返回　False。　　

语法：object。FileExists（filespec）　　　　

FileExists　　方法语法有如下几部分：　　　

部分　描述：object　　必需的。始终是一个　FileSystemObject　　的名字。　　

filespec　　必需的。要确定是否存在的文件的名字。如果认为文件不在当前文件夹中，必须提供　

一个完整的路径说明（绝对的或相对的）。　　

032。excel时间函数2（菜鸟教程）　　　　

这一贴说明时间函数，time；hour，minute；second的用法。　　

time　的计算过程：　　　

time（hour；minute；second），time地返回值为0…0。99999999之间的数值，它的计算方法如下：　　　

hour的范围：0…24　　　　

minute的范围：0…59　　　　

second的范围：0…59　　　　

在满足以上输入范围的时候：time（hour；minute；second）=hour/24＋minute/（24*60）＋second/　

　（24*60*60）。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　CCXXXV　　


…………………………………………………………Page　236……………………………………………………………

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



如：tiem（05　，34；29）=0。232280092592593。如何计算的呢？　　　

5/24＋34/　　（24*60）＋29/　　（24*60*60）=0。208333333333333＋0。0236111111111111　　　　

＋0。000335648148148148=0。232280092592593。　　

在帮助文件里还有hour；minute；second不再范围情况，这时候，如何计算的呢？　　　

1、second/60；除的整数为minute；mod（second；60）为second　　　　

2、minute/60，除的整数为hour；mod（minute；60）为minute　　　　

3、hour/24，mod（hour；24）为hour　　　　

最后再用hour/24＋minute/（24*60）＋second/　　（24*60*60）计算。　　

帮助中的例子：time（0；0；2000）=0。023148如何算的呢？　　　

2000/60=33　mod（2000；60）=20　　　　

time（0；0；2000）=time（0；33；20）=0/24＋33/（24*60）=20/（24*60*60）=0。023148　　　　

呵呵，其实没有什么用，会用这个函数就可以可，如何算的就不必在意了！！！　　　

033。年月日的问题　　　

EXCEL表格中年月有时候输入不对，（早已记录过大量数据，改写麻烦。）比如198001，意　

思是1980年1月，可是设置单元格式日期只有年月日，没有年月。怎么做？　　　

解　答　：　插　入　一　辅　助　列　，　假　设　198001　在　E1　，　F=IF（MID（E1；5；1）=〃0〃；LEFT（E1；4）&〃　年　

〃&RIGHT（E1；1）&〃月〃；LEFT（E1；4）&〃年〃&RIGHT（E1；2）&〃月〃）　　　　

试一下。　　

又问：198001能否改为1980…1？或者1980年1月改为1980…1？　　　

解答：f1=IF（MID（e1；5；1）=〃0〃；LEFT（e1；4）&〃…〃&RIGHT（e1；1）；LEFT（e1；4）&〃…〃&RIGHT（e1；2））　　　　

或者更简单一些：=LEFT（A6；4）&〃…〃&value（RIGHT（A6；2））　　（数据在a6单元格）　　　

也可以这样：=date（mid（e1；1；4）；mdi（e1；5；2）；1）这样会显示为1980…1…1，然后可以随意设置成　

相应的日期格式。　　

034。请帮忙解释一个公式　　

=LEFT（A1；（SEARCHB（〃？〃；A1）…1）/2）这是我在站内过去的帖子里看到的一个公式，用于提取前　

文后数中的文字部分，非常好用。请教这个公式中最后两步的意义是什么？另外，当A1是“　1234　

个”的格式时，当如何提取其中的文字呢？　　　

解答：1、公式的含义是：查找第一个半角字符出现的位置＇SEARCHB（〃？〃；A1）＇，减去1后除以　

2，就是文字的字符数目，将其提取出来。　　

2、=RIGHT（A1；LENB（A1）…LEN（A1））　　　　

035。关于宏和程序　　

我现在已经用excel编了一个较完整的程序，并且能够给源程序加密码，实现〃工程不可见〃，　

但是我发现在vba编辑环境里还能看到我的大部分宏，虽然说不能编辑，但能运行，请问如何　

隐藏起来。　　　

解答：不用模块函数；重写成类或放到workbook　中；或在程序中直接将菜单宏隐藏。或者：新建　

类，然后将模块中的程序拷贝到类，提示：找不到宏。　　

又问：我现在已经能做到屏蔽调alt＋F11键了，虽然不能看到我的宏程序，但是依然可以运行　

我的宏，请高手做答，如何隐藏起我的宏。　　

解答：在宏的声明前加Private。　　

036。请教多条件求和的问题　　

大家好，我是个新手，想向大家请教指定多条件求和的函数公式。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　CCXXXVI　　


…………………………………………………………Page　237……………………………………………………………

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



譬如，有一张工作表有4列标题：品名，数量，日期，签收人。　　

若我想求，符合条件为：品名为A　，日期为Y　，签收人为B的数量之和。　　

该用那个函数公式？　　　

解答：=IF（A2=〃a〃；IF（B2=〃03。10。22〃；COUNTIF（D：D；D2）；〃时间无〃）；〃无〃）　　　　

A列品名，B列日期，C列数量，D列签收人用if　　嵌套。　　

或者：数组公式　　　

｛=sum（（a1：a100=品名）*（c1：c100=　日期）（d1：d100=签收人）*（B1：B100））｝　　　　

也可以：｛=SUM（（（A1：A100）=〃a〃）*（（B1：B100）=〃03。10。22〃））｝　　　　

037。请教关于星期的计算？　　　

如何通过输入一个日期：2003…10…20即可得到该天在本年度的第几个星期？　　　

解答：使用　WEEKNUM　　函数。　　

如：=WEEKNUM（A1）　　　

=WEEKNUM（TODAY（））　　　

或者：日期在a1　　　　

=INT（（A1…DATE（YEAR（A1）；1；0）＋WEEKDAY（DATE（YEAR（A1）；1；0）；1）＋7…WEEKDAY（A1；1））/7）　　　　

也可以用VBA　：　　　

'under　the　iso　standard；　a　week　always　begins　on　a　monday；　and　ends　on　a　sunday。　　　　

'the　first　week　of　a　year　is　that　week　which　contains　the　first　thursday　of　the　year；　　　　

'or；　equivalently；　contains　jan…4。　　　　

'　　　　

public　function　isoweeknum（anydate　as　date；　_　　　　

optional　whichformat　as　variant）　as　integer　　　　

'　　　　

'　whichformat：　missing　or　　2　then　returns　week　number；　　　　

'　=　2　then　yyww　　　　

'　　　　

dim　thisyear　as　integer　　　　

dim　previousyearstart　as　date　　　　

dim　thisyearstart　as　date　　　　

dim　nextyearstart　as　date　　　　

dim　yearnum　as　integer　　　　

thisyear　=　year（anydate）　　　　

thisyearstart　=　yearstart（thisyear）　　　　

previousyearstart　=　yearstart（thisyear　　1）　　　

nextyearstart　=　yearstart（thisyear　＋　1）　　　　

select　case　anydate　　　　

case　is　》=　nextyearstart　　　　

isoweeknum　=　（anydate　nextyearstart）　　7　＋　1　　　　

yearnum　=　year（anydate）　＋　1　　　　

case　is　《　thisyearstart　　　　

isoweeknum　=　（anydate　previousyearstart）　　7　＋　1　　　　

yearnum　=　year（anydate）　　1　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　CCXXXVII　　


…………………………………………………………Page　238……………………………………………………………

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



case　else　　　　

isoweeknum　=　（anydate　thisyearstart）　　7　＋　1　　　　

yearnum　=　year（anydate）　　　　

end　select　　　　

if　ismissing（whichformat）　then　　　　

exit　function　　　　

end　if　　　　

if　whichformat　=　2　then　　　　

isoweeknum　=　cint（format（right（yearnum；　2）；　〃00〃）　&　_　　　　

format（isoweeknum；　〃00〃））　　　　

end　if　　　　

end　function　　　　

public　function　yearstart（whichyear　as　integer）　as　date　　　　

dim　weekday　as　integer　　　　

dim　newyear　as　date　　　　

newyear　=　dateserial（whichyear；　1；　1）　　　　

weekday　=　（newyear　2）　mod　7　　　　

if　weekday　《　4　then　　　　

yearstart　=　newyear　weekday　　　　

else　　　　

yearstart　=　newyear　weekday　＋　7　　　　

end　if　　　　

end　function　　　　

038。请教日期的转换问题　　

我的程序里有这样一段代码：　　　

Dim　str　As　Date　　　　

str=now　　　　

Sheet1。Cells（1；　〃A〃）　=　str　　　　

运行后在单元格里显示　　

2003/11/13　15：19：45　　　　

但我想让它显示成如下的格式：　　　

　２００３年１１月１３日（小时，分，秒去掉）　　　

我用year　　（str）想单独取得年的值，但显示1905/06/25　0：00：00　　　

请问有什么好的方法可以实现这种转换吗？　　　

解答：　　　

Dim　str　As　Date　　　　

str=now　　　　

Sheet1。Cells（1；　〃A〃）　=　format（str；〃yyyy年mm月dd　日〃）　　　　

039。如何用vba实现删除最右边的字符　　

1月、2月、3月。。。。。。。。。。。10月、11月、12月　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　CCXXXVIII　　


…………………………………………………………Page　239……………………………………………………………

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



请问如何用vba实现把“月”删除只提取：1、2、3。。。。。。。10、11、12。　　

解答：Sub　abc（）　　　　

Dim　a　As　Integer　　　　

Dim　b　As　String　　　　

Dim　c　As　String　　　　

c　=　〃〃　　　　

For　a　=　1　To　Len（b）　　　　

c　=　c　&　IIf（Mid（b；　a；　1）　　〃月〃；　Mid（b；　a；　1）；　〃〃）　　　　

Next　　　　

MsgBox　c　　　　

End　Sub　　　　

或者：　　　

A1=　1月、2月、3月、4月、5月、6月、7月、8月、9月、10月、11月、12月　　

＇A1＇　=　Application。WorksheetFunction。Substitute（＇A1＇；　〃月〃；　〃〃）　　　　

040。请问如何定义相对定位的名称　　

我想定义一个各个工作表（一个工作薄内）使用的名称。该名称为相对定位，　　　

如我在sheet1表的B2中该名称是　sheet1　　表的A2　，我在sheet2表的B2中时该名称是sheet2表　

的A2单元格，可我在定义名称时它总是加上工作表名。　　

解答：=offset（indirect（address（row（）；column（）；））；；…1；；）　　　

041。请问如何替换？　　　

有很多条这样的记录：******（212）；****（315）；*********（658）　。如何只保留括号里的数字，　

*号是汉字。　　

解答：设数据在A30单元格　=MID（A30；FIND（〃（〃；A30）＋1；LEN（A30）…FIND（〃（〃；A30）…1）　　　

IF　　你的数据都是要求记录中最后面的三码数字　　

可以试着用简单的方式解决　　　

=RIGHT（A1；3）　　　

又问：我是要合并，你却要拆分！你能告诉我怎样将两列：即“数字列”和“文字列”合并成一列？　　　

解答：试试这个：　　　

Sub　Join（）　'将选择的行几个单元格数值合并到一列的一个单元格　　　

Application。ScreenUpdating　=　False　　　　

Application。Calculation　=　xlCalculationManual　　　　

On　Error　Resume　Next　　　　

Dim　iRows　As　Long；　mRow　As　Long；　ir　As　Long；　ic　As　Long　　　　

iRows　=　Selection。Rows。Count　　　　

Set　lastcell　=　Cells。SpecialCells（xlLastCell）　　　　

mRow　=　lastcell。Row　　　　

If　mRow　《　iRows　Then　iRows　=　mRow　'not　best　but　better　than　nothing　　　　

iCols　=　Selection。Columns。Count　　　　

For　ir　=　1　To　iRows　　　　

newcell　=　Trim（Selection。Item（ir；　1）。value）　　　　

For　ic　=　2　To　iCols　　　　

trimmed　=　Trim（Selection。Item（ir；　ic）。value）　　　　

If　Len（trimmed）　　0　Then　newcell　=　newcell　&　〃　〃　&　trimmed　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　CCXXXIX　　


…………………………………………………………Page　240……………………………………………………………

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



Selection。Item（ir；　ic）　=　〃〃　　　　

Next　ic　　　　

Selection。Item（ir；　1）。value　=　newcell　　　　

Next　ir　　　　

Application。Calculation　=　xlCalculationAutomatic　　　　

Application。ScreenUpdating　=　True　　　　

End　Sub　　　　

042。求教合并单元格区域的连续读取方法　　

求教：1、如何选定连续的合并单元格区域；2、如何连续读取合并单元格中的内容。　　

解答：Public　Sub　adre（）　　　　

Dim　cell　As　Range　　　　

Dim　iRow_dn1　As　Integer　　　　

iRow_dn1　=　＇B65536＇。End（xlUp）。Row　　　　

Set　av1　=　Range（〃B3：B〃　&　iRow_dn1）　　　　

For　Each　cell　In　av1　　　　

If　cell　　〃〃　Then　　　　

MsgBox　cell。Address　&　〃　　等於　〃　&　〃　　o　〃　&　cell　&　〃　　§〃　　　　

End　If　　　　

Next　　　　

End　Sub　　　　

043。求一公式　　

sheet1　sheet2　　　　

A　B　C　A　B　C　　　　

1　　产品代码产品名　生产机器名　产品代码　产品名　生产机器名　　　

2　012354　a203　1m炉　225894　nj033　　？　　　

3　214345　b4032　　发泡炉　056894　kkl001　　？　　　

4　225894　nj033　1m炉　214345　b4032　　？　　　

5　056894　kkl001　　发泡炉　　

6　124589　lli002　1m炉　　

SHEET1是一张源资料表，而SHEET2是一个生产计划表的一部分。　　

请问：　　　

我求SHEET2中的A列中产品代码相对应的C列的”生产机器名“　。　　

这个公式怎么写？　　　

解答：Sheet2的C2格公式为：=VLOOKUP（A2；SHEET1！A：C；3；0）　　　

044。讨论一下取最后一个单词的方法　　

例如