Excel word ppt office使用技巧大全(DOC格式)-第38部分
按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
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。讨论一下取最后一个单词的方法
例如现在在A1 中有一句“M。 Henry Jackey”，如何用函数将最后的一个单词取出来呢？ 当然，
我们现在是知道最后的单词是6个字符，可以用Right（A1；6）来计算，但如果最后一个单词的字
符数是不定的呢，如果做呢？ 请大家试下有几种方法。
解答：方法1、用一列公式填充
CCXL
…………………………………………………………Page 241……………………………………………………………
Excel_word_ppt_使用技巧大全（完全版）
=IF（LEFT（RIGHT（A1；ROW（））；1）=CHAR（32）；RIGHT（A1；ROW（）…1）；“”）
方 法 2 、 =MID（A1；FIND（〃 *〃；SUBSTITUTE（A1；〃 〃；〃 *〃；LEN（A1）…LEN（SUBSTITUTE（A1；〃
〃；〃〃））））＋1；LEN（A1）…FIND（〃 〃；A1））
方法3、用自定义函数当然方便，而且简单。
Function xx（n As String） As String
n = Application。Trim（n）
lastone = Right（n； Len（n） InStrRev（n； 〃 〃））
xx = lastone
End Function
方法4 、=IF（ISERROR（SEARCH（〃〃；TRIM（LEFT（B1））））；RIGHT（A1；ROW（））；〃〃）拖出来的第一
个字符就行。
方 法 5 、 ｛=RIGHT（A1；LEN（A1）…MAX（（MID（A1；ROW（INDIRECT（〃1：〃&LEN（A1）））；1）=〃
〃）*ROW（INDIRECT（〃1：〃&LEN（A1）））））｝
嫌长就（假定最长100字符）
｛=RIGHT（A1；LEN（A1）…MAX（（MID（A1；ROW（1：100）；1）=〃 〃）*ROW（1：100）））｝
045。如何获取工作表中某一列有多少条记录？
因为每一列的的记录都不一样多，所以我想获得每一列各有多少条记录，怎么做？
解答：RecordNumbers=Application。COUNTA（A：A）
或者：Private Sub UserForm_Activate（）
x = Sheet1。UsedRange。Rows。Count
x1 = Sheet1。CountA（c4； cx）
也可以：Sub aa（）
MsgBox （Application。CountA（Range（〃A：A〃）））
End Sub
还可以：Sub aa（）
x = Sheet1。UsedRange。Rows。Count
MsgBox （Application。CountA（Range（〃c3：cx〃）））
End Sub
这样也行：用下面的方法可测出任一列使用的行数
a=Sheet1。range（〃b1〃）。End（xlDown）。Row 。
总结：
1。Sub aa（）
MsgBox （Application。CountA（Range（〃C：C〃）））
End Sub
结果永远都是1或者3，可是实际上记录有600多条
2。
Sub aa（）
Worksheets（〃sheet1〃）。Activate
Range（〃c2〃）。Select
x1 = 〃=COUNTA（sheet1！C）〃
MsgBox x1
End Sub
这个是看fhj 示例的文件录制成宏改的，不过运行结果永远是 =counta（sheet1！c）
3。
CCXLI
…………………………………………………………Page 242……………………………………………………………
Excel_word_ppt_使用技巧大全（完全版）
Sub aa（）
x1 = 〃=COUNTA（sheet1！C）〃
MsgBox x1
end sub
提示和前面的一样。
4。其实已经试了几十种方法了。还是错的。作为公式时，是可以使用。但是却
无法把获得的值赋值给一个变量。除非是先写到一个单元格里，再重新读出来。
不过我觉得太麻烦了。而且写的时候会修改工作表。不是很恰当。
解答：Application。CountA（Range（〃C：C〃））返回除去无值单元格的所有单元格的数量。
Sheet1。range（〃C1〃）。End（xlDown）。Row返回第一次遇到空单元格前的单元格的数量。
（注：当C列有空白单元格时用：
myEndRow=sheets（〃sheet1〃）。range（〃C65536〃）。End（xlUp）。row）
结论：Sub aa（）
x1 = Sheet1。Range（〃C3〃）。End（xlDown）。Row
MsgBox x1
end sub
这就对了。谢谢各位！
回应：推荐你用
Columns（1）。SpecialCells（xlCellTypeConstants）。Count
045。如何禁止输入空格
在Excel中如何通过编辑“有效数据”来禁止录入空格？烦请大侠们费心解答。不胜感激。
解答：有效性公式。=COUNTIF（A1；〃* *〃）=0
（注：COUNTIF（A1；〃* *〃） 在单元格有空格时结果为1；没有空格时结果为0
如希望第一位不能输入空格：countif（a1；〃 *〃）=0
如希望最后一位不能输入空格：countif（a1；〃* 〃）=0）
046。如何判断单元格中单词的数量？
比如我在A1 中输入“you are a good boy”如何判断单词为5个？
解答：=LEN（E12）…LEN（SUBSTITUTE（E12；〃 〃；〃〃））＋1
（注：方法很巧妙用trim把前后的空格去掉。如果有标点符号或者两个词之间的空格数大于1个
就不好办了）
047。如何取数
表一有数据，要求表二中数据为取一行表一数据，空一行。
解答：
Sub test（）
On Error Resume Next
Application。ScreenUpdating = False
For i = 1 To Sheets（1）。UsedRange。Rows。Count
Sheets（1）。Rows（VBA。Trim（VBA。Str（i）） ＋ 〃：〃 ＋ VBA。Trim（VBA。Str（i）））。Copy
Sheets（2）。Activate
CCXLII
…………………………………………………………Page 243……………………………………………………………
Excel_word_ppt_使用技巧大全（完全版）
Sheets（2）。Rows（VBA。Trim（VBA。Str（i * 2 1）） ＋ 〃：〃 ＋ VBA。Trim（VBA。Str（i * 2 1）））。Select
ActiveSheet。Paste
Next i
Application。ScreenUpdating = True
End Sub
048。如何通过VBA编程将符合条件的数据库记录输入到EXCEL中
现在有access格式的数据表 TEST
货号 货名 规格 单价。。。。
1…01 货品1 1M 250。00
1…02 货品2 4Kg 100。00
。。。。。。。。。。。。。。。。
N…99 货品N 999 999。99
现在我想在EXCEL的单元格中输入货号，通过VBA代码自动从数据表中查找出相应的记录，并
在相邻的列分别自动录入货品、规格、单价等内容，从而实现EXCEL 自动数据录入。请问这VBA
代码应如何写？谢谢！
解答：Private Sub Worksheet_Change（ByVal Target As Range）
Dim Rs As New ADODB。Recordset
Dim Query As String
Dim Cnn As String
With Application
。ScreenUpdating = False
