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

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



就是还不知道如何引用Range中的每个值，请高手指点。）　　　　

解答：参数使用Range而函数值为Integer是可以的　　

用for　each　next循环思路也是对的，应该这样作：　　　

dim　rg　as　range　　　　

dim　ivalue　as　integer　　　　

for　each　rg　in　　参数区域　　

ivalue=ivalue＋rg。value　　　　

next　　　　

函数＝ivalue　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　CCXXV　　


…………………………………………………………Page　226……………………………………………………………

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



大概意思如此，但没有加入防错处理，你自己先试试看，有问题在问。　　

又问：试了一天，还是不行。　　　

Public　Function　x2（rng　As　Range）　As　Integer　　　　

Dim　rng　As　Range　　　　

Dim　ivalue　As　Integer　　　　

For　Each　rng　In　rng。Range　　　　

ivalue　=　ivalue　＋　rng。value　^　2　　　　

Next　　　　

x2　=　ivalue　　　　

End　Function　　　　

还望您的帮助。　　

解答：Public　Function　SUMX2（rng　As　Range）　As　Integer　　　　

'你的错误有几项：　　　

'1。函数名不能使用单元格位址的形式，否则在工作表中引用函数产生歧义，excel　以为你引用　

单元格　　　

'2。参数名与内部变量名冲突，rng本来是定义参数，在过程中不应出现重名变量　　　

'3。rng已被定义为range对象变量，实际意义是一range引用，不能再用rng。Range引用，range　

的range属性是什么呢，没有吧　　　

'函数我已经给你改了，基本能用　　　

Dim　rg　As　Range　　　　

Dim　ivalue　As　Integer　　　　

For　Each　rg　In　rng　　　　

ivalue　=　ivalue　＋　rg。value　^　2　　　　

Next　　　　

SUMX2　=　ivalue　　　　

End　Function　　　　

结果：调试成功！，非常感谢！　　　

005。判斷字符串的包含性　　

用什么命　令“abcdefg”是否包含“abc”？　　　　

解答：If　VBA。InStr（1；　〃abcdefg〃；　〃abc〃）　　0　Then　MsgBox　〃包含〃　　　　

006。利用背景实现套打的解决方案　　

利用背景套打主要在于数据打印位置的确定，关键就是要使图片和实物之间的尺寸保持一致，　

这里我引入一个中间参照物—空白表（只有表格线的表）。具体操作以套打支票为例说明：　　　

（1）将支票扫描成图片。　　　

（2）打印一个空白表，使其与支票尺寸一致（需反复调整打印，也可行、列分别打印）。　　　

（3）用“画图”的缩放功能调整图片大小，导入excel作背景，并使其与空白表大小一致（亦需反复　

调整导入，每次均用原图缩放，再另存为一个文件）。　　　

（4）根据图片背景调整好单元格，填入数据后套打支票，效果是匹配度达99％　。　　　

（5）　由于每次都是用原图缩放，故可取得缩放比例作为参数，再套打其他表格时，即可直接依　

参数缩放图片。　　

思路：因为空白表=支票，图片=空白表，所以图片=支票。　　

该方案已证实可行。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　CCXXVI　　


…………………………………………………………Page　227……………………………………………………………

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



007。宏放在worksheet和sheet及模块中各有什么区别？　　　

解答：放在thisworkbook或sheet中的宏与模块中的宏的主要区别是book或sheet中的过程函数　

只能是对象所专有的，不能在对象之外的任何地方调用（很显然不能声明Public过程，否则编　

译报错），而模块中声明Public过程函数可以在任何地方使用。　　

008。关于excel　问题　　

在excel　中如何用公式实现单元格内容递增？　　　

如：　AB12　　　　

AB13　　　　

AB14　　　　

。。。。。。。　　　　

AB100　　　　

条件是无法确定储存格中的内容的前面有多少个字符，也就是，可能是2个，也可能是3个，　

或者更多。　　

解答：為什麼要用公式呢？　　　

如　A1　=　AB12　　，只要你向下拉的眩�制就可以。　　

公式可參考　（條件是　AB12　　不可以是　AB02　，　處理　0　　為首的數字有困難，亦不可以只有英文　

字）　　　　

A1　=　AB12　　　　

A2　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　=　　

LEFT（A1；LEN（A1）…SUM（LEN（A1）…LEN（SUBSTITUTE（A1；｛〃0〃；〃1〃；〃2〃；〃3〃；〃4〃；〃5〃；〃6〃；〃7〃；〃8〃；〃9　

〃｝；〃〃））））　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　&　　

RIGHT（A1；SUM（LEN（A1）…LEN（SUBSTITUTE（A1；｛〃0〃；〃1〃；〃2〃；〃3〃；〃4〃；〃5〃；〃6〃；〃7〃；〃8〃；〃9〃｝；〃〃）））　

）＋1　　　　

（A1　=　AB12　　　　

公式　　　

=LEN（SUBSTITUTE（A1；｛〃0〃；〃1〃；〃2〃；〃3〃；〃4〃；〃5〃；〃6〃；〃7〃；〃8〃；〃9〃｝；〃〃））　　　　

答案看到的是　4　　，但其實它回傳一個數組　｛4；3；3；4；4；4；4；4；4；4｝　　　　

公式　　　

=LEN（A1）…LEN（SUBSTITUTE（A1；｛〃0〃；〃1〃；〃2〃；〃3〃；〃4〃；〃5〃；〃6〃；〃7〃；〃8〃；〃9〃｝；〃〃））　　　　

答案看到的是　0　　，但其實它回傳一個數組　｛0；1；1；0；0；0；0；0；0；0｝　　　　

公式　　　

=SUM（LEN（A1）…LEN（SUBSTITUTE（A1；｛〃0〃；〃1〃；〃2〃；〃3〃；〃4〃；〃5〃；〃6〃；〃7〃；〃8〃；〃9〃｝；〃〃）））　　是　　將　

｛0；1；1；0；0；0；0；0；0；0｝　　加總　　

=　2）　　　

009。给数组公式、VBA爱好者泼点冷水。　　

数组公式、VBA威力巨大，在某些情形下提高效率非常明显，但各有其弱点。数组公式在大数　

据的时候，运行速度慢得无法忍受。比如，我日常需要编制得几个报表，原始数据有4　－8万　

行，20——30列，用数组根本无法操作。倒是利用数据透视表及其他一些组合功能，可谓神　

速。而VBA主要适用与日常比较固定的一些工作，对于一些临时性工作而言，缺乏灵活性，有　

杀鸡用牛刀之嫌疑。因此，根据我个人多年工作经验的体会，能熟练地灵活运用EXCEL基本功　

能和常用函数，就可以高效地完成大部分日常工作。　　

我　　比　　较　　常　　用　地　　东　　西　　有　　：　数　　据　　透　　视　　表　，　　数　　据　——　　　　　　　　　　　　　　　　　有　效　　性　　，　

ctrl＋enter；index　；match；indirect；offset；if；vlookup；下拉列表框，绝对引用与相对引用，编辑——　

选择性粘贴（数值、乘除、转置等），图表，条件格式，定义名称，分列，填充等。　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　CCXXVII　　


…………………………………………………………Page　228……………………………………………………………

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



相反观点：数据透视表的计算是excel　中内置的，同样的计算次数速度与数组公式是一样的，　

数组公式计算慢有两个因素，一是公式的编写不合理，另一个主要的原因是数组公式要对所有　

的引用数据进行计算，不管这些数据是否有效。　　

VBA应该是最灵活的，在VBA　中结合数组公式是可以达到最佳目的的，可用VBA先分析出数组　

公式要用的有效引用区域，在辅助表中进行数组计算（这个速度比用VBA直接分析计算要快得　

多），再将结果记入需要的单元格中，然后删除辅助表。　　

其实你说的那些基本操作均可用VBA来做的，速度比手工做要快。　　

010。从式子抽取一小式子的问题？　　　

b1=sum（a1：a10）＋（10＋20）/4；怎么从中取出（10＋20）/4或其结果（即5）？用evaluate　、get。cell　

都不能取出。　　

解答：定义X=get。formula（B　1）得到B1的公式；再用MID、Right等函数截取　　

011。or可以用数组应用？　　　

有一个工作表，数据上万行，其中一列是我要分析的数值，数值比如为：0111，0112，0113，　

0114，0115，0116，0117中的任何一个。我要统计除0111，0113，0115之外的数据。公式：　

｛sum（if（or（sheet！A2：A1111=〃0111〃；sheet！a2：a1111=〃0113〃；sheet！a2：a1111=〃0115〃）；1；0））　

｝；可是统计数字和我筛选相加的不一样，用if层层选，可以。请问原因？　　　

解答：数组公式中用*、＋代替AND　、OR　　　　

｛sum（if（（sheet！A2：A1111=〃0111〃）＋（sheet！a2：a1111=〃0113〃）＋（sheet！a2：a1111=〃0115〃）；1；　

0））｝　　　　

012。countif表达式的格式　　

请问：我想找A1　：A15　中，值不为空的数目，用countif命令怎么写呢？　　　

解答1：应为counta（a1：a15）　。countif为找a1：a15　中，特定值的数目。　　

解答2：=ROWS（A1：A15）*COLUMNS（A1：A15）…COUNTIF（A1：A15；〃〃）　　　　

=ROWS（A1：A15）*COLUMNS（A1：A15）…COUNTBLANK（A1：A15）　　　　

解答3：直接用count（a1：a15）不是更好吗！　　　

013。删除字符串中某个字符的函数是什么？删除字符串中某个字符的函数是什么？　　　

举例：字符串“i　love　you　a！〃想删除a字面，应该用什么函数实现？还有就是在字符串中某个位　

置加入某个字符用什么函数呢？　　　

解答：如果有一定的规律，可以用Replace函数。例如：在A1单元格已有的字符串”123467〃中　

加入个5变为“　1234567”。可以这样做：=replace（a1；5；；〃5〃）　　　　

另一方法：用CONCATENATE函数。　　

例如：a5单元格里的数据是“asdfhjkl〃；在另外的单元格了输入下面的函数　　

CONCATENATE（LEFT（A5；4）；〃l〃；RIGHT（A5；4））　，得到的结果就是”asdflhjkl〃；然后用“选择性粘　

贴，粘贴数值”粘贴回a5单元格就可以了。　　

014。两表合一实例　　

问题提出：怎样把两个表（有相同的字段）怎样合并成一个表？　　　

思路：用CountIf（）函数对表1进行判断，如果其值为0，则表示没以重复，再将表2中和表1不　

重复的数据复制到表1中，从而实现两表合一。　　

解题的方法：　　　

Sub　dd（）　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　CCXXVIII　　


…………………………………………………………Page　229……………………………………………………………

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



b　=　Sheets（2）。＇a1＇。CurrentRegion。Rows。Count　＋　1　　　　

〃判断表2的行数　　

For　i　=　3　To　b　　　　

a　=　Sheets（1）。＇a1＇。CurrentRegion。Rows。Count　＋　1　　　　

〃判断表1的行数　　

c　=　Sheets（2）。＇a1＇。CurrentRegion。Columns。Count　　　　

〃判断表2的列数　　

If　Application。WorksheetFunction。CountIf（Sheets（1）。＇b1：b1000＇；　Sheets（2）。Cells（i；　2））　=　　0　　

Then　　　　

Sheets（2）。Range（Sheets（2）。Cells（i；　1）；　Sheets（2）。Cells（i；　c））。Copy　Sheets（1）。Cells（a；　1）　　　　

〃将表2中与表1不重复的数据复制到表1中　　

End　If　　　　

Next　　　　

End　Sub　　　　

015。有没有办法把加载宏内置到Excel文件里？　　　

因为用了　Networkdays　　函数，用到了分析工具库，但是还要发给别人，怎么办？　　　

解答：试试在〃Thisworkbook〃中写如下语句：　　　

Private　Sub　Workbook_Open（）　　　　

Application。RegisterXLL　Filename：=　_　　　　

〃Office安装路径OfficeLibraryAnalysisANALYS32。XLL〃　　　　

End　Sub　　　　

又问：Office安装路径怎么写呀？大家不一定都装在C盘上。　　

解答：试试：Application。Path　&　〃LibraryAnalysisANALYS32。XLL〃　　　　

046。如何在userform上显示最大化与最小化按钮　　

解答：　　　

利用API　　　　

Option　Explicit　　　　

Private　　Declare　　Function　　GetWindowLong　　Lib　　〃user32〃　　Alias　　〃GetWindowLongA〃　　（ByVal　　

hWnd　As　Long；　ByVal　nIndex　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　SetWindowLong　Lib　〃user32〃　Alias　〃SetWindowLongA〃　（ByVal　hWnd　　

As　Long；　ByVal　nIndex　As　Long；　ByVal　dwNewLong　As　Long）　As　Long　　　　

Private　Const　GWL_STYLE　=　（…16）　　　

Private　Const　WS_THICKFRAME　As　Long　=　&H40000　'（恢复大小）　　　　

Private　Const　WS_MINIMIZEBOX　As　Long　=　&H20000　'（最小化）　　　　

Private　Const　WS_MAXIMIZEBOX　As　Long　=　&H10000　'（最大化）　　　　

Private　Sub　UserForm_Initialize（）　　　　

Dim　hWndForm　As　Long　　　　

Dim　IStyle　As　Long　　　　

hWndForm　=　FindWindow（〃ThunderDFrame〃；　Me。Caption）　　　　

IStyle　=　GetWindowLong（hWndForm；　GWL_STYLE）　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　CCXXIX　　


…………………………………………………………Page　230……………………………………………………………

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



IStyle　=　IStyle　Or　WS_THICKFRAME　'还原　　　

IStyle　=　IStyle　Or　WS_MINIMIZEBOX　'最小化　　　

IStyle　=　IStyle　Or　WS_MAXIMIZEBOX　'最大化　　　

SetWindowLong　hWndForm；　GWL_STYLE；　IStyle　　　　

End　Sub　　　　

017。这个判断代码怎么写　　

在A列输入日期，如果所输入日期为1月1　日或5月1　日则B列相关单元格＋1；其他日期＋0；这要用　

到什么函数？代码怎么写？谢谢！　　　

解答：用IF函数或用Worksheet_Change事件　　

Private　Sub　Worksheet_Change（ByVal　Target　As　Range）　　　　

If　Target。Column　=　1　Then　　　　

If　IsDate（Target）　Then　　　　

If　（Month（Target）　=　1　And　Day（Target）　=　1）　Or　（Month（Target）　=　5　And　Day（Target）　=　1）　　

Then　　　　

Target。Offset（0；　1）　=　Target。Offset（0；　1）　＋　1　　　　

End　If　　　　

End　If　　　　

End　If　　　　

End　Sub　　　　

018。这个汇总表拆分程序怎么写，高手帮忙！　　　

要将总表里的数据按工作单位字段拆分成数个分表（每个单位一张表格，标签名字为工作单位）　

这个程序怎么编写，请高手指点。如果记录增多或字段增多（但拆分字段不增）这个程序又应　

该怎样改写，请高手稍微讲解一下，应为我不是为这一个表，还想用到别的工作表中，谢谢！　　　

解答：Sub　Add_data（sht_Name）　'找出要取资料的区域　　　

Dim　i　As　Integer；　j　As　Integer；　row_d　As　Integer　　　　

Dim　First_row　As　Integer；　Last_row　As　Integer　　　　

On　Error　Resume　Next　　　　

With　Sheets（〃总表〃）　　　　

i　=　1　　　　

Do　Until　。Cells（i；　3）。value　=　sht_Name　　　　

i　=　i　＋　1　　　　

Loop　　　　

First_row　=　i　　　　

j　=　First_row　　　　

Do　Until　。Cells（j；　3）　　sht_Name　　　　

j　=　j　＋　1　　　　

Loop　　　　

Last_row　=　j　　1　　　

End　With　　　　

Sheets（〃总表〃）。Range（Cells（First_row；　1）；　Cells（Last_row；　12））。Select　　　　

Selection。Copy　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　CCXXX　　


…………………………………………………………Page　231……………………………………………………………

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



Sheets（sht_Name）。Select　　　　

Range（〃A2〃）。Select　　　　

ActiveSheet。Paste　　　　

With　ActiveSheet　　　　

row_d　=　。Range（〃A2〃）。End（xlDown）。Row　＋　1　　　　

Range（〃B〃　&　row_d）。value　=　〃合计〃　　　　

For　i　=　5　To　11　　　　

Cells（row_d；　i）。value　=　Application。WorksheetFunction。Sum（Range（Cells（2；　i）；　Cells（row_d　　

1；　i）））　　　

Next　i　　　　

End　With　　　　

Sheets（〃总表〃）。Activate　　　　

Range（〃A2〃）。Select　　　　

End　Sub　　　　

020。这个公式应该怎么写？　　　

我想统计所有物料编码的第一个字符为a的库存数量的总和，这个公式应该怎么写？A列为物　

料编码；B列为库存数量。　　

解答：=SUMIF（A：A；〃a*〃；B：B）　　　

021。样修改此宏？　　　

下面的宏是k版主帮我写的，从文件夹内汇入其他工作表表格。汇入范围为第五行、第L列。　　

如汇入范围改为第三行、第R列。　　

怎样修改此宏？　　　

Public　Sub　Feed_in2（）　　　　

Dim　Row_dn；　Row_dn1；　i；　j；　k；　m　As　Integer　　　　

Dim　Path1；　Str1　As　String　　　　

Dim　wb　As　Workbook　　　　

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

Path1　=　Application。ActiveWorkbook。Path　　　　

Str1　=　ActiveWorkbook。Name　　　　

k　=　5　　　　

With　Application　　　　

。EnableEvents　=　False　　　

。ScreenUpdating　=　False　　　

If　Row_dn　》=　5　Then　　　　

Range（〃B5：L〃　&　Row_dn）。ClearContents　　　　

End　If　　　　

With　。FileSearch　　　　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　CCXXXI　　


…………………………………………………………Page　232……………………………………………………………

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



。NewSearch　　　

。LookIn　=　Path1　　　

。FileType　=　msoFileTypeExcelWorkbooks　　　

If　。Execute