VBA

 

几种从数据库读取数据生成excel文件的比较

OFFICE精英俱乐部 『 VBA交流 』 我的自学收集资料

以下是近段时间从不同地方收集到的一些学习资料,希望对新手有借鉴作用,同时感谢各位对我的支持和帮助,先发60小点,代码可能不完全正确,请指正.
全部显示
1,Application.CommandBars("Worksheet Menu Bar").Enabled = false
2,cells(activecell.row,"b").value '活动单元格所在行B列单元格中的值
3,Sub CheckSheet()'如果当前工作薄中没有名为kk的工作表的话,就增加一张名为kk的工作表,并将其排在工作表从左至右顺序排列的最左边的位置,即排在第一的位置
Dim shtSheet As Worksheet
For Each shtSheet In Sheets
If shtSheet.Name = "KK" Then Exit Sub
Next shtSheet
Set shtSheet = Sheets.Add(Before:=Sheets(1))
shtSheet.Name = "KK"
End Sub
4,Sheet1.ListBox1.List = Array("一月", "二月", "三月", "四月")'一次性增加项目
5,Sheet2.Rows(1).Value = Sheet1.Rows(1).Value'将一个表中的一行全部拷贝到另一个表中
6,Sub pro_cell()'将此代码放入sheet1,则me=sheet1,主要是认识me
Me.Unprotect
Cells.Locked = False
Range("D11:E11").Locked = True
Me.Protect
End Sub
7,Application.CommandBars("Ply").Enabled = False'工作表标签上快捷菜单失效
8,Sub aa()'把B1到B12单元格的数据填入c1到c12
For i = 1 To 12
Range("C" & i) = Range("B" & i)
Next i
End Sub
9,ActiveCell.AddComment
Selection.Font.Size = 12'在点选的单元格插入批注,字体为12号
10,Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
End Sub
11,ScrollArea 属性
参阅应用于示例特性以 A1 样式的区域引用形式返回或设置允许滚动的区域.用户不能选定滚动区域之外的单元格.String 类型,可读写.
说明
可将本属性设置为空字符串 ("") 以允许对整张工作表内所有单元格的选定.
示例
本示例设置第一张工作表的滚动区域.
Worksheets(1).ScrollArea = "a1:f10"
12\if application.max([a1:e1])=10 then
msgbox""
commandbutton1.enabled=false
'A1—E1最大的数值达到10时,自动弹出对话框,并冻结按钮
12,本示例将更改的单元格的颜色设为蓝色.
Private Sub Worksheet_Change(ByVal Target as Range)
Target.Font.ColorIndex = 5
End Sub
13,Sub test()'求和
Dim rng As Range, rng2 As Range
For Each rng In ActiveSheet.UsedRange.Columns
Set rng2 = Range(Cells(1, rng.Column), Cells(Cells(65536, rng.Column).End(xlUp).Row, rng.Column))
rng2.Cells(rng2.Cells.Count).Offset(1, 0) = WorksheetFunction.Sum(rng2)
Next rng
End Sub
14,将工作薄中的全部n张工作表都在sheet1中建上链接
Sub test2()
Dim Pt As Range
Dim i As Integer
With Sheet1
Set Pt = .Range("a1")
For i = 2 To ThisWorkbook.Worksheets.Count
.Hyperlinks.Add Anchor:=Pt, Address:="", SubAddress:=Worksheets(i).Name & "!A1"
Set Pt = Pt.Offset(1, 0)
Next i
End With
End Sub
15,保存所有打开的工作簿,然后退出 Microsoft Excel.
For Each w In Application.Workbooks
w.Save
Next w
Application.Quit
16,让form标题栏上的关闭按钮失效
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode 1 Then Cancel = True
End Sub
17,Sub countsh()'获得工作表的总数
MsgBox Sheets.Count
End Sub
18,Sub IE()'打开个人网页
ActiveWorkbook.FollowHyperlink "about:blank"
SendKeys "{F4}ykk1976.anyp.cn{ENTER}", True
End Sub
19,Sub delback()'一次性删除工作簿中所有工作表的背景
For Each shtSheet In Sheets
shtSheet.SetBackgroundPicture Filename:=""
Next shtSheet
End Sub
20,[a1].formula="=b1+c1"'A1中设定公式为=B1+C1
21,Private Sub CommandButton1_Click()'将A1到C6中大于=3的数依次放入E列
Dim i As Long
r = 1
For Each i In Range("a1:c6")
If i > =3 Then Cells(r, 5) = i: r = r + 1
Next
End Sub
22,Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)'显示带数字的表名
b = Split(Sh.Name, "(")
On Error GoTo ss
num = CInt(Left(b(1), Len(b(1)) - 1))
If num >= 1 And num < 20 Then
MsgBox Sh.Name
End If
Exit Sub
ss:
MsgBox "error", 16, ""
End Sub
23,Sub Test()'选择所有工作表名以"业报"开头的工作表或头两个字是业报的报表名引用
Set Sh = ActiveSheet
If Left(Sh.Name, 2) = "业报" Then ' 或if sh.name like"业报*"then
MsgBox "你成功了", 64, ""
End If
End Sub
24,1.建立文件夹的方法
MkDir "D:\Music"
2.打开文件夹的方法
ActiveWorkbook.FollowHyperlink Address:="D:\Music", NewWindow:=True
25,在当前工作表翻页
Application.SendKeys "{PGUP}", True
Application.SendKeys "{PGDN}", True
或者
ActiveWindow.LargeScroll Down:=1
ActiveWindow.LargeScroll Down:=-1
26,当Target = "*小计"时如何写,*代表任何字符.
if instr(target.value,"小计")0 then
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Value Like "*小计" Then MsgBox "OK"
End Sub
27,ActiveCell.FormulaR1C1 = "=SUM(R[1]C:R[14]C,R[59]C:R[78]C)"
这是相对引用的写法:根据推算你的函数是放在"AD6"单元格
你的函数:=SUM(R[1]C:R[14]C 中的 "R"表示行 "C"表示列.
R[1]表示"AD6+1行",C表示"列没有变化,就是同列"那么:R[1]C就表示AD7
同理,R[14]表示AD6+14行,表示:AD20.以此类推.
28,Private Sub CommandButton1_Click()'将A1到C6中大于=3的数依次放入E列
Dim i As Long
Dim iRng As Range
For Each iRng In Sheets(1).Range("a1:c6")
If iRng.Value >= 3 Then
i = i + 1
Sheets(1).Range("E" & i).Value = iRng.Value
End If
Next
End Sub
29,工作表中的窗体按钮禁用后,按钮形状不变,字体不变,从外表上无法看出其已禁用,如何设置属性使其像控件按纽那样明显的禁用
With ActiveSheet.Buttons(1)
.Enabled = False
ActiveSheet.Shapes(.Caption).DrawingObject.Font.ColorIndex = 15
End With
复原的方法
With ActiveSheet.Buttons(1)
.Enabled = True
ActiveSheet.Shapes(.Caption).DrawingObject.Font.ColorIndex = xlAutomatic
End With
30,Private Sub Worksheet_SelectionChange(ByVal Target As Range'选定A1时要输入密码
If Target.Address = "$A$1" Then
A = InputBox("请输入密码", "officefans")
If A = 1 Then [A1].Select Else [A2].Select
End If
End Sub
31,如何将工作薄中的命名单元格成批删除!
Dim Item As Name
For Each Item In ActiveWorkbook.Names
Item.Delete
Next Item
32,平时只能看到表1,如要看表2和表3,只能通过表1的链接打开,且表2和表3回到表1后,又不可见.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$3" Then '当点击"$A$3"单元格时...
Sheet2.Visible = 1 '取消隐藏
Sheet2.Activate '激活
ActiveSheet.Range("A1").Select
End If
If Target.Address = "$A$6" Then
Sheet3.Visible = 1 '取消隐藏
Sheet3.Activate
ActiveSheet.Range("A1").Select
End If
End Sub
33,将a2单元格内容替换为a1内容
ActiveCell.Replace What:=[a2], Replacement:=[a1]
34,如果是要填入名称,则:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Selection.Value = ComboBox1.column(1)
End Sub
如果是要填入代码和名称的组合 :
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Selection.Value = cstr(ComboBox1.column(0))+" "+combobox1.column(1)
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Selection.Value = ComboBox1.Value
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'target.row 代表行号
'target.column 代表列号
i=target.row '获取行号
j=target.column '获取列号
End Sub
35,当激活工作表时,本示例对 A1:A10 区域进行排序.
Private Sub Worksheet_Activate()
Range("a1:a10").Sort Key1:=Range("a1"), Order:=xlAscending
End Sub
36,BeforePrint 事件
参阅应用于示例特性在打印指定工作簿(或者其中的任何内容)之前,产生此事件.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel 当事件产生时为 False.如果该事件过程将本参数设为 True,则当该过程运行结束之后不打印工作簿.
示例
本示例在打印之前对当前活动工作簿的所有工作表重新计算.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
For Each wk in Worksheets
wk.Calculate
Next
End Sub
37,Open 事件
每次打开工作簿时,本示例都最大化 Microsoft Excel 窗口.
Private Sub Workbook_Open()
Application.WindowState = xlMaximized
End Sub
38,ActiveSheet 属性
参阅应用于示例特性返回一对象,该对象代表活动工作簿中的,或者指定的窗口或工作簿中的活动工作表(最上面的工作表).只读.如果没有活动的工作表,则返回 Nothing.
说明
如果未给出对象识别符,本属性返回活动工作簿中的活动工作表.
如果某一工作簿在若干个窗口中出现,那么该工作簿的 ActiveSheet 属性在不同窗口中可能不同.
示例
本示例显示活动工作表的名称.
MsgBox "The name of the active sheet is " & ActiveSheet.Name
39,Calculate 方法
参阅应用于示例特性计算所有打开的工作簿,工作簿中的一张特定的工作表或者工作表中指定区域的单元格,如下表所示:
要计算 依照本示例
所有打开的工作簿 Application.Calculate (或只是 Calculate)
指定工作表 指定工作表
指定区域 Worksheets(1).Rows(2).Calculate
expression.Calculate
expression 对于 Application 对象可选,对于 Worksheet 对象和 Range 对象必需.该表达式返回"应用于"列表中的对象之一.
示例
本示例计算 Sheet1 已用区域中 A 列,B 列和 C 列的公式.
Worksheets("Sheet1").UsedRange.Columns("A:C").Calculate
程序的核心是算法问题
40,End 属性
参阅应用于示例特性返回一个 Range 对象,该对象代表包含源区域的区域尾端的单元格.等同于按键 End+ 向上键,End+ 向下键,End+ 向左键或 End+ 向右键.Range 对象,只读.
expression.End(Direction)
expression 必需. 该表达式返回"应用于"列表中的对象之一.
Direction XlDirection 类型,必需.所要移动的方向.
XlDirection 可为 XlDirection 常量之一.
xlDown
xlToRight
xlToLeft
xlUp
示例
本示例选定包含单元格 B4 的区域中 B 列顶端的单元格.
Range("B4").End(xlUp).Select
本示例选定包含单元格 B4 的区域中第 4 行尾端的单元格.
Range("B4").End(xlToRight).Select
本示例将选定区域从单元格 B4 延伸至第四行最后一个包含数据的单元格.
Worksheets("Sheet1").Activate
Range("B4", Range("B4").End(xlToRight)).Select
41,应用于 CellFormat 和 Range 对象的 Locked 属性.
本示例解除对 Sheet1 中 A1:G37 区域单元格的锁定,以便当该工作表受保护时也可对这些单元格进行修改.
Worksheets("Sheet1").Range("A1:G37").Locked = False
Worksheets("Sheet1").Protect
42,Next 属性
参阅应用于示例特性返回一个 Chart,Range 或 Worksheet 对象,该对象代表下一个工作表或单元格.只读.
说明
如果指定对象为区域,则本属性的作用是仿效 Tab,但本属性只是返回下一单元格,并不选定它.
在处于保护状态的工作表中,本属性返回下一个未锁定单元格.在未保护的工作表中,本属性总是返回紧靠指定单元格右边的单元格.
示例
本示例选定 sheet1 中下一个未锁定单元格.如果 sheet1 未保护,选定的单元格将是紧靠活动单元格右边的单元格.
Worksheets("Sheet1").Activate
ActiveCell.Next.Select
43,想通过target来设置(A1:A10)区域内有改动,就发生此事件.不知道如何
if target.row = 1 and target.column <=10 then
Sub 列举菜单项()
Dim r, s, i As Integer
r = 1
For i = 1 To CommandBars.Count
ActiveSheet.Cells(r, 1) = "CommandBars(" & i & ").Name:" & CommandBars(i).Name
r = r + 1
For s = 1 To CommandBars(i).Controls.Count
ActiveSheet.Cells(r, 1) = s & "," & CommandBars(i).Controls(s).Caption
r = r + 1
Next
Next
End Sub
44,本示例设置 Microsoft Excel 每当打开包含链接的文件时,询问用户是否更新链接.
Application.AskToUpdateLinks = True
45,自定义函数
Public Function Now1()
Dim string1 As String
string1 = VBA.Date
Now1 = string1
End Function
46,复制
Sub copy1()
Sheet2.Range("C5:C10").Copy Sheet1.Range("C5:C10")
End Sub
47,如何统计表中sheet的个数
msgbox sheets.count
Columns("G:G").Select
48, Selection.EntireColumn.Hidden = True
这样隐藏有个毛病,如何解决 如果A1:G1单元格合并的话,就把A:G列均隐藏了.
Columns("G:G").EntireColumn.Hidden = True
49,在VBA中引用excel函数的方法
1). Worksheets("Sheet1").Range("A1").Formula = "=$A$4+$A$10"
2). Sheet1.Cells(1,1).Formula = "=" & Sheets(iii).Name & "!R1C4"
在宏中用R1C1方式写时表格1的A1中会在写为"=Sheet2!$D$1"
用这种方式,想用什么函数就用什么函数.
50,选定下(上)一个工作表
sheets(activesheet.index-1).select
sheets(activesheet.index+1).select
51,Private Sub Workbook_Open()
ActiveWindow.DisplayWorkbookTabs = False '取消工作表标签
Application.CommandBars("Sheet").Controls(1).Enabled = False '格式_工作表不能重命名
Application.CommandBars.FindControl(ID:=889).Enabled = False '右键菜单不能重命名
End Sub
52, [a65536].End(xlUp'A列从下往上第一个非空的单元格
53,Sub macro()
Set rng = Range("C11:F13") 定义RNG为一个单元格区域
For Each cel In rng 定义CEL为RNG中的一个任一单元格
colo = cel.Interior.ColorIndex 定义 COLO 为单元格CEL的填充颜色
If colo -4142 Then 如果COLO的值不等于-4142
iR = [b65536].End(xlUp).Row + 1 IR等于B列数据区域的行数+1
If [a65535].End(xlUp).Value Cells(cel.Row, 2) Then Cells(iR, 1) = Cells(cel.Row, 2)
如果A列最后一个非空值单元格 不等于Cells(cel.Row, 2) 的值 那么单元格Cells(iR, 1) 的值等于Cells(cel.Row, 2) 的值 CEL.ROW是C11:F13中任意单元格的行号
Cells(iR, 2) = Cells(10, cel.Column)
Cells(iR, 3) = cel.Value
Cells(iR, 4) = IIf(colo = 36, "Yellow", "Red") Cells(iR, 4) 的值如果colo = 36那么值为"Yellow",否则值为"RED"
next
End Sub
54,Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'**********运行数据日志记录**********
Dim rng As Range
If ActiveSheet.Name "主界面" And ActiveSheet.Name "目录索引" Then
For Each rng In Target.Cells
Changecell = ActiveSheet.Name & ",单元格:" & rng.Address(0, 0) & ",更改为:" & rng.value & ".更改时间:" & Now
CritOrAddtext
Next
End If
End Sub
55,ActiveSheet.Unprotect '撤销当前工作表保护
If ActiveSheet.Name "主界面" And ActiveSheet.Name "目录索引" And Target.Row > 3 Then '行变色
On Error Resume Next
[ChangColor_With].FormatConditions.Delete
Target.EntireRow.Name = "ChangColor_With"
With [ChangColor_With].FormatConditions
.Delete
.Add xlExpression, , "TRUE"
.Item(1).Interior.Color = 4
End With
End If
ActiveSheet.Protect
56,在C1中弄个下拉无表,实际是有效性,你可以选择A1:A4为C1单元格有效性的序列数据源,如果说C1不与A1:A4在同一表,则不能这么用,应当先对A1:A4命名,然后把数据源改为名称.
57,自动增加工作表
进入宏命令编辑窗口,在Sub 自动增加工作表()命令后依次键入如下宏命令内容:
Dim i&, userinto
i = 0
userinto = InputBox("输入插入工作表数量:")
If IsNumeric(userinto) = True Then
Do Until i = userinto
Worksheets.Add
i = i + 1
Loop
End If
End Sub
58,方法一(共享级锁定):
1,先对EXCEL文件进行一般的VBAProject"工程密码保护.
2,打开要保护的文件,选择:工具--->保护--->保护并共享工作簿--->以追踪修订方式共享-->输入密码-->保存文件.
完成后,当你打开"VBAProject"工程属性时,就将会提示:"工程不可看!"
方法二(推荐,破坏型锁定):
用16进制编辑工具,如WinHex,Ultraedit-32(可到此下载)等,再历害点的人完全可以用debug命令来做......用以上软件打开EXCEL文件,查找定位以下地方:
ID="{00000000-0000-0000-0000-000000000000}" 注:实际显示不会全部为0
此时,你只要将其中的字节随便修改一下即可.保存再打开,就会发现大功告成!
当然,在修改前最好做好你的文档备份.至于恢复只要将改动过的地方还原即可(只要你记住了呵呵).
顺便说一句,这种方法仍然是可破解的,因为加密总是相对的 .
59,Sub AddComments()
'自动对ActiveSheet所有有公式格位加上注解
Set RG = Cells.SpecialCells(xlCellTypeFormulas)
For Each c In RG
c.AddComment
c.Comment.Text Text:=c.Formula
Next c
End Sub
Sub De_Comments()
'自动消除所有注解
Set RG = Cells.SpecialCells(xlCellTypeFormulas)
For Each c In RG
c.ClearComments
Next c
End Sub
60,如何在Excel里使用定时器
www.aspsky.net 2002-3-12 20:53:27 动网先锋
用过 Excel 97 里的加载宏 "定时保存" 吗 可惜它的源程序是加密的,现在就上传一篇介绍实现它的文档.
在 Office 里有个方法是 application.ontime ,具体函数如下:
expression.OnTime(EarliestTime, Procedure, LatestTime, Schedule)
如果想进一步了解,请参阅 Excel 的帮助.
这个函数是用来安排一个过程在将来的特定时间运行,(可为某个日期的指定时间,也可为指定的时间段之后).通过这个函数我们就可以在 Excel 里编写自己的定时程序了.下面就举两个例子来说明它.
1.在下午 17:00:00 的时候显示一个对话框.
Sub Run_it()
Application.OnTime TimeValue("17:00:00"), "Show_my_msg"
'设置定时器在 17:00:00 激活,激活后运行 Show_my_msg .
End Sub
Sub Show_my_msg()
msg = MsgBox("现在是 17:00:00 !", vbInformation, "自定义信息")
End Sub
2.模仿 Excel 97 里的 "自动保存宏",在这里定时 5 秒出现一次
Sub auto_open()
MsgBox "欢迎你,在这篇文档里,每 5 秒出现一次保存的提示!", vbInformation, "请注意!"
Call runtimer '打开文档时自动运行
End Sub
Sub runtimer()
Application.OnTime Now + TimeValue("00:00:05"), "saveit"
' Now + TimeValue("00:15:00") 指定在当前时间过 5 秒钟开始运行 Saveit 这个过程.
End Sub
Sub SaveIt()
msg = MsgBox("朋友,你已经工作很久了,现在就存盘吗 " & Chr(13) _
& "选择是:立刻存盘" & Chr(13) _
& "选择否:暂不存盘" & Chr(13) _
& "选择取消:不再出现这个提示", vbYesNoCancel + 64, "休息一会吧!")
'提示用户保存当前活动文档.
If msg = vbYes Then ActiveWorkbook.Save Else If msg = vbCancel Then Exit Sub
Call runtimer '如果用户没有选择取消就再次调用 Runtimer
End Sub
以上只是两个简单的例子,有兴趣的话,可以利用 Application.Ontime 这个函数写出更多更有用的定时程序.
Sub Show_my_msg()
msg = MsgBox("现在是 17:00:00 !", vbInformation, "自定义信息")
End Sub
2.模仿 Excel 97 里的 "自动保存宏",在这里定时 5 秒出现一次
Sub auto_open()
MsgBox "欢迎你,在这篇文档里,每 5 秒出现一次保存的提示!", vbInformation, "请注意!"
Call runtimer '打开文档时自动运行
End Sub
Sub runtimer()
Application.OnTime Now + TimeValue("00:00:05"), "saveit"
' Now + TimeValue("00:15:00") 指定在当前时间过 5 秒钟开始运行 Saveit 这个过程.
End Sub
个人主页:http://ykk1976.anyp.cn
接上面
61,Excel 最重要的应用就是利用公式进行计算.无论输入是纯粹的数字运算,还是引用其他单元格计算,只要在一个单元格中输入公式,就能得到结果.这个直接显示结果的设计对于绝大多数场合来说都是适用的,但某些情况下就不那么让人满意了.比如说在做工程施工的预结算编写,使用Excel,既要写出工程量的计算式,也要看到它的结果,于是这样相同的公式在Excel里面要填两次,一次在文本格式的单元格中输入公式,一次是在数据格式的单元格中输入公式让Excel计算结果.如何既能看到公式又能看到结果呢 这个问题笔者认为可以从两个方面考虑:一种方法是所谓"已知结果,显示公式",先在数据格式单元格中输入公式让Excel计算结果,然后在相邻的单元格中看到公式;另一种方法所谓"已知公式,显示结果",就是先在一个文本格式的单元格中输入公式,在相邻的单元格中看到结果.★已知结果,显示公式
假设C列为通过公式计算得到的结果(假设C1为"=A1+B1",或者直接是数字运算"=2+3"),而相邻的D列是你需要显示公式的地方(即D1应该显示为"=A1+B1"或者"=2+3").
1. 打开"工具"菜单选择"选项"命令,出现"选项"对话框.
2. 在"常规"选项卡中,选中"R1C1引用方式"选项.
3. 定义名称,将"引用位置"由"=GET.CELL(6,Sheet1!RC[-1])"即可.这里的RC[-1]含义是如果在当前单元格的同行前一列单元格中有公式结果,则在当前单元格中得到公式内容,即在含公式结果单元格的同行后一列单元格显示公式内容;如果将RC[-1]改为RC[1],则在公式结果的同行前一列单元格显示公式内容.
4. 如果"引用位置"中含有"RC[-1]",则在含公式结果单元格的同行后一列单元格中输入"=FormulaofResult"即可得到公式;如果"引用位置"中含有"RC[1]",则在含公式结果单元格的同行前一列单元格中输入"=FormulaofResult"即可得到公式.
提示:如果想要在含公式结果单元格的同行后数第2列中显示公式内容,则需要把"引用位置"中的"RC-1"改为"RC-2".
★已知公式,显示结果
假设C列为输入的没有等号公式(假设C1为"A1+B1"),而相邻的D列是你需要存放公式计算结果的地方(即D1显示A1和B1单元格相加的结果).
1. 选中D1,然后打开"插入"菜单选择"名称"命令中的"定义"子命令,出现"定义名称"对话框.
2. 在"在当前工作表中的名称"输入栏中输入定义的名称"ResultofFomula",在下方的"引用位置"编辑栏中输入"=EVALUATE(Sheet1!C1)",单击[确认]按钮退出.
3. 在 D1中输入"=ResultofFomula",然后选中按住右下角的填充柄向下拉动填充即可.
提示:EVALUATE 是Eexcel 4.0版的宏表函数,Excel 2000和Excel 2002中还支持,但只可用于名称定义中.
4. 填充后要按[F9]进行重算,如果C列的公式有改动,也需要及时按[F9]进行重算.
巧施妙计,就能让公式和结果在Excel中和平共处了,你也试试吧.
62,http://www.yesky.com/20010724/190094_3.shtml Microsoft Excel是一款功能非常强大的电子表格软件.它可以轻松地完成数据的各类数学运算,并用各种二维或三维图形形象地表示出来,从而大大简化了数据的处理工作.但若仅利用Excel的常用功能来处理较复杂的数据,可能仍需进行大量的人工操作.但Excel的强大远远超过人们的想象--宏的引入使其具有了无限的扩展性,因而可以很好地解决复杂数据的处理问题.
随着支持Windows的应用程序的不断增多和功能的不断增强,越来越多的程序增加了宏处理来方便用户的自由扩展.但初期各应用程序所采用的宏语言并不统一,这样用户每使用一种应用程序时都得重新学习一种宏语言.为了统一各种应用程序下的宏,Microsoft推出了VBA(Visual Basic for Applications)语言.
VBA是从流行的Visual Basic编程语言中派生出来的一种面向应用程序的语言,它适用于各种Windows应用程序,可以解决各应用程序的宏语言不统一的问题.除此之外,使用VBA语言还有如下优点:1,VBA是一种通用程序语言,通过它不仅可以共享Microsoft相关的各种软件(如Excel,Word,Access)……,而且随着其它的一些软件(如大名鼎鼎的AutoCAD2000)等对VBA的支持,这些软件也已进入到了VBA的控制范围;2,可以将用VBA编写的程序复制到Visual Basic中调试并运行,从而实现用Visual Basic来控制有关的应用程序;3,VBA提供的大量内部函数大大简化了用户的操作.
对于而今的宏,不仅语言统一规范,而且其功能也已非常强大.但在大多数介绍Excel的"傻瓜书","指南","入门与提高"等参考书中往往略过不提,或浅浅带过,读者从中获得的有关知识往往不足以应付处理复杂数据的需求.为了完成工作,就让我们一起来学习"宏"的妙用吧.
一),宏的自学
首先需要明确的是,本文不可能教会您关于宏的所有内容.您需要学会利用"录制宏"的方法来学习宏:点击Excel"工具"下拉菜单中"宏"下 quot;录制新宏",此后可象平时一样进行有关操作,待完成后停止录制.然后再点击"工具"下拉菜单中"宏"下"宏"的"编辑"选项即可打开刚才所录制的宏的Visual Basic源程序,并且可以在此时的"帮助"下拉菜单中获得有关的编程帮助.对录制宏进行修改不仅可以学习宏的使用,还能大大简化宏的编写.
二),基本概念
为了学习Excel中的宏,我们需要先了解以下一些基本概念.
1,工作簿:Workbooks,Workbook,ActiveWorkbook,ThisWorkbook
Workbooks集合包含Excel中所有当前打开的Excel工作簿,亦即所有打开的Excel文件;Workbook对应Workbooks中的成员,即其中的Excel文件;ActiveWorkbook代表当前处于活动状态的工作簿,即当前显示的Excel文件;ThisWorkbook代表其中有Visual Basic代码正在运行的工作簿.
在具体使用中可用Workbooks(index)来引用Workbook对象,其中index为工作簿名称或编号;如Workbooks(1),Workbooks("年度报表.xls").而编号按照创建或打开工作簿的顺序来确定,第一个打开的工作簿编号为1,第二个打开的工作簿为2…….
2,工作表:Worksheets,Worksheet,ActiveSheet
Worksheets集合包含工作簿中所有的工作表,即一个Excel文件中的所有数据表页;而Worksheet则代表其中的一个工作表;ActiveSheet代表当前处于的活动状态工作表,即当前显示的一个工作表.
可用Worksheets(index)来引用Worksheet对象,其中index为工作表名称或索引号;如Worksheets(1),Worksheets("第一季度数据").工作表索引号表明该工作表在工作表标签中的位置:第一个(最左边的)工作表的索引号为1,最后一个(最右边的)为Worksheets.Count.需要注意的是:在使用过程中Excel会自动重排工作表索引号,保持按照其在工作表标签中的从左至右排列,工作表的索引号递增.因此,由于可能进行的工作表添加或删除,工作表索引号不一定始终保持不变. 3,图表:Chart ,Charts,ChartObject,ChartObjects,ActiveChart
Chart代表工作簿中的图表.该图表既可为嵌入式图表(包含在ChartObject中),也可为一个分开的(单独的)图表工作表.
Charts代表指定工作簿或活动工作簿中所有图表工作表的集合,但不包括嵌入式在工作表或对话框编辑表中的图表.使用Charts(index) 可引用单个Chart图表,其中index是该图表工作表的索引号或名称;如Charts(1),Charts("销售图表").图表工作表的索引号表示图表工作表在工作簿的工作表标签栏上的位置.Charts(1)是工作簿中第一个(最左边的)图表工作表;Charts(Charts.Count)为最后一个(最右边的)图表工作表.
ChartObject代表工作表中的嵌入式图表,其作用是作为Chart对象的容器.利用ChartObject可以控制工作表上嵌入式图表的外观和尺寸.
ChartObjects代表指定的图表工作表,对话框编辑表或工作表上所有嵌入式图表的集合.可由ChartObjects(index)引用单个ChartObject,其中index为嵌入式图表的编号或名称.如Worksheets("Sheet1").ChartObjects(1),Worksheets("sheet1").ChartObjects("chart1")分别对应"Sheet1"工作表中的第一个嵌入式图表,以及名为"Chart1"的嵌入式图表.
ActiveChart可以引用活动状态下的图表,不论该图表是图表工作表,或嵌入式图表.而对于图表工作表为活动工作表时,还可以通过ActiveSheet属性引用之.
4,单元格:Cells,ActiveCell,Range,Areas
Cells(row,column)代表单个单元格,其中row为行号,column为列号.如可以用Cells(1,1),Cells(10,4)来引用"A1","D10" 单元格.ActiveCell代表活动工作表的活动单元格,或指定工作表的活动单元格.
Range代表工作表中的某一单元格,某一行,某一列,某一选定区域(该选定区域可包含一个或若干连续单元格区域)或者某一三维区域.
可用Range(arg)来引用单元格或单元格区域,其中arg可为单元格号,单元格号范围,单元格区域名称.如Range("A5"),Range("A1:H8"),Range("Criteria").虽然可用Range("A1")返回单元格A1,但用Cells更方便,因为此时可用变量指定行和列.
可将Range与Cells结合起来使用,如Range(Cells(1,1),Cells(10,10))代表单元格区域"A1:J10";而expression.Cells(row,column)返回单元格区域中的一部分,其中expression是返回Range的表达式,row和column为相对于该区域的左上角偏移量.如由Range("C5:C10").Cells(1,1)引用单元格C5.
Areas为选定区域内的连续单元格块的集合,其成员是Range对象.而其中的每个Range对象代表选定区域内与其它部分相分离的一个连续单元格块.某些操作不能在选定区域内的多个单元格块上同时执行;必须在选定区域内的单元格块数Areas.Count上循环,对每个单独的单元格块分别执行该操作.此时,可用Areas(index)从集合中返回单个Range对象,其中index为单元格块编号;如Areas(1).
5, 行与列:Rows,Columns,Row,Column
Rows,Columns分别代表活动工作表,单元格区域范围Range,指定工作表中的所有行数,列数.对于一个多选单元格区域范围Range的Rows,Columns,只返回该范围中第一个区域的行数,列数.例如,如果Range对象有两个区域(areas)A1:B2和C3:D4,Rows.Count返回2而不是4.
可通过Rows(行号),Columns(列号)来引用相应的行与列;如Rows(3),Columns(4)分别对应第三行,D列.
利用Rows,Column可以获得区域中第一块的第一行行号,第一列列号,所得值均以十进制数表示.
三),处理单元格
1,直接赋值与引用
将变量,常量值直接赋给单元格,或将单元格的值直接赋给变量,常量,这是在Excel中最简单的单元格赋值及引用方法.如下例将工作表"Sheet1"A1单元格的值赋给Integer变量I,并将I+1的值赋给当前工作表中的B1单元格:
Dim I As Integer
I=Worksheets("Sheet1").Cells(1,1)
Cells(1,2).Select '选定B1单元格,使其成为当前单元格
ActiveCell=I+1 '以I+1为当前单元格赋值
2,用公式赋值
在宏的使用中,可能会更多地用公式来给单元格赋值.如下例将相对于活动单元格左侧第4列,向上第6行至向上第2行的单元格数值之和赋给活动单元格(以本行,本列为第0行,0列):
ActiveCell.Formula="=AVERAGE(R[-6]C[-4]:R[-2]C[-4])"
3,引用其它工作表中的单元格
当赋值公式中需要引用其它工作表中的单元格时,在被引用的单元格前加上"工作表名!"即可.如以下即在赋值中引用了"Sheet1"工作表中的A1至A4单元格:
Range("E10").Formula="=SUM(Sheet1!R1C1:R4C1)"
但需注意的是:当被引用的工作表名中含有某些可能引起公式歧义的字符时,需要用单引号'将工作表名括起来.如:
Worksheets("Sheet1").ActiveCell.Formula="=Max('1-1剖面'!D3:D5)"
4,引用其它工作簿中的单元格
在被引用单元格所在工作表名前加上"[工作簿名]",即可引用其它工作簿中的单元格.如:
ActiveCell.Formula="=MAX([Book1.xls]Sheet3!R1C:RC[4])"
同样需注意的是:当被引用的工作簿名中含有某些可能引起公式歧义的字符时,需要用中括号"[","]"及单引号'将工作簿名括起来.如:
Cells(1,2).Formula="=MIN('[1995-2000总结.xls]1995-1996年'! $A$1:$A$6)"
5,避免循环引用
在上述公式赋值过程中,应避免在公式中引用被赋值的单元格,防止循环引用错误.
6,添加批注
可按如下方法格给单元格添加批注:
Dim 批注文本 As String
批注文本="批注示例" '准备批注文本
ActiveCell.AddComment '添加批注
ActiveCell.Comment.Text Text:=临时 '写入批注文本
ActiveCell.Comment.Visible=False '隐藏批注
7,添加,删除,复制,剪切,粘贴单元格
Range("D10").Insert Shift:=xlToRight '在D10单元格处添加一新单元格,原D10格右移
Range("C2").Insert Shift:=xlDown '在C2单元格处添加一新单元格,原C2格下移
Rows(2).EntireRow.Insert '在第2行前添加一空白行,原第2行下移
Columns(3).EntireColumn.Insert '在C列前添加一空白列,原C列右移
Columns("A:D").Delete Shift:=xlToLeft '删除A列至D列,其右侧列左移
Rows("3:5").Delete Shift:=xlUp '删除第3行至第5行,其下方行上移
Range("B2").EntireRow.Delete '删除第2行
Range("C4").EntireColumn.Delete '删除C列
Range("B10:C13").Copy '复制B10至C13单元格区域
Cells(1,2).Cut '剪切B1单元格
Range("D10").Select
ActiveSheet.Paste '自D10单元格起粘贴剪贴板中的内容
四),图表
1,工作表图表
以下为一添加工作表图表的实例.
Charts.Add after:=Worksheets("Sheet1") '在"Sheet1"工作表之后添加新图表工作表
ActiveChart.ChartType=xlXYScatterSmooth '图表类型为XY平滑线散点图
ActiveChart.SetSourceData Source:=Sheets("结点坐标").Range("A1:B69"), PlotBy:= _
xlColumns '图表数据来源于"结点坐标"工作表的A1至B69单元格,且按列绘图.
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "节点坐标" '图表标题"节点坐标"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "x" 'x轴标题"x"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "y" 'y轴标题"y"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True '显示x轴主网格线,默认情况下为显示
.HasMinorGridlines = True '显示x轴次网格线,默认情况下为不显示
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True '标出x轴主网格值,默认情况下为标注
.HasMinorGridlines = False '取消x轴次网格值标注,默认情况下为不标注
End With
ActiveChart.Legend.Position = xlRight '图例显示在图表右侧
2,嵌入式图表
嵌入式图表仅在添加方式及引用格式上与工作表图表有所不同,而对图表的设置基本类似.详见下例.
Set嵌入表=ActiveSheet.ChartObjects.Add(0,0,200,300) '在当前工作表(0,0)坐标处添加宽200,高300的嵌入式图表
嵌入表.Chart.ChartType = xlColumnClustered '图表类型为簇状柱形图
嵌入表.Chart.SetSourceData Source:=Sheets(1).Range("A2:B2"), PlotBy:=xlRows '设置图表数据来源
With 嵌入表.Chart
.HasTitle = False '无图表标题
.Axes(xlCategory, xlPrimary).HasTitle = False '无x轴标题
.Axes(xlValue, xlPrimary).HasTitle = False '无y轴标题
End With
五),工作表
1,添加
Sheets.Add before:=Sheets(1) '在第1工作表前添加新工作表
Sheets.Add after:=Sheets(Sheets.Count) '在最后工作表后添加新工作表
2,移动
ActiveSheet.Move before:=Sheets(2) '将当前工作表移动至第2工作表之前
3,命名
ActiveSheet.Name="工作表名" '将当前工作表命名为"工作表名"
4,删除
可以用以下语句删除当前工作表.
ActiveSheet.Delete
但在删除前Excel会自动弹出提示框,需在用户确认后方可执行删除.为避免这一干扰,可以先用以下语句关闭Excel的警告提示.
Application.DisplayAlerts = False
在删除完成后,再重新打开Excel的警告提示
Application.DisplayAlerts = True
六),工作簿
Excel的宏对工作簿的操作主要为保存.
Dim 存盘文件名 As String
ActiveWorkbook.Save '保存当前工作簿
存盘文件名="工作表名"
ActiveWorkbook.SaveAs Filename:= 存盘文件名 '当前工作簿另存为"工作表名.xls"
在另存时,若指定的存盘文件名不包含路径,则保存在该工作簿的打开目录下.而若此存盘文件已存在,也可用关闭Excel警告提示的方法以免其自动弹出提示框.
63,用VBA的几个误区
1,想通过Excel+vba来编程发财,不管怎么说,目前这种可能性很小.
2,录制宏后的代码不进行简化,这种情况初学者常犯;或者不屑于用录制宏,这种相反.
3,喜欢用.select,.copy,.active等,如果不是迫不得已,还是不要用这些东西好.
4,不喜欢用公式,函数,什么都喜欢用程序;活用公式函数是我们学EXCEL中重要的重点,学会嵌套着用,很多事情都可以解决.譬如表1的A1单元格如果要等于表2的A列中的某个单元格,很多人都是用程序来直接赋值,数量少这也可以,但如果数量很多的话,为何不试用一下offset呢 偏移都指向同一个单元格,就当是一个变量,改一下这个单元格的值,表1用offset的单元格的值就全部改了.
5,设计输入数据的界面竟然用用户窗口+文本框之类的东西,天,我为什么要用EXCEL,原因之一不就是图个方便吗 !直接在工作表中操作就是输入数据最好的方法,如果一些功能你不想给别人用,顶多你屏蔽掉吧.
6,关闭程序的时候很喜欢退出整个EXCEL,这样自我感觉更象一个完整的程序.其实完全有必要考虑其他用户是否也在用EXCEL,所以,必须做好一下判断,不要自作聪明全部退出来,还帮忙把每个工作簿都保存一下.
7,不喜欢用with,总是workbook("").worksheet("").range("").***一行行排得挺整齐的,还说都用复制,其实也不麻烦.
8,总想把VBA+EXCEL做得象一个程序一样,其实,就因为它不独立,所以才更加为我们喜爱,不是吗
64,用过VB的人都应该知道如何声明变量,在VBA中声明变量和VB中是完全一样的!
使用Dim语句
Dim a as integer '声明A为整形变量
Dim a '声明A为变体变量
Dim a as string '声明A为字符串变量
Dim a,b,c as currency '声明A,b,c为货币变量
声明变量可以是:Byte,Boolean,Integer,Long,Currency,Single,Double,Decimal(当前不支持),Date,String(只限变长字符串),String * length(定长字符串),Object,Variant,用户定义类型或对象类型.
强制声明变量
Option Explicit
说明:该语句必在任何过程之前出现在模块中.
声明常数,用来代替文字值.
Const
' 常数的默认状态是 Private.
Const My = 456
' 声明 Public 常数.
Public Const MyString = "HELP"
' 声明 Private Integer 常数.
Private Const MyInt As Integer = 5
' 在同一行里声明多个常数.
Const MyStr = "Hello", MyDouble As Double = 3.4567
在EXCEL97中,有一个十分好的功能,他就是把鼠标放置在一个有效数据单元格中,执行该段代码,你就可以将连在一起的一片数据全部选中.只要将该段代码加入到你的模块中.
Sub My_Select
Selection.CurrentRegion.Select
End sub
删除当前单元格中数据的前后空格.
sub my_trim
Trim(ActiveCell.Value)
end sub
使单元格位移
sub my_offset
ActiveCell.Offset(0, 1).Select'当前单元格向左移动一格
ActiveCell.Offset(0, -1).Select'当前单元格向右移动一格
ActiveCell.Offset(1 , 0).Select'当前单元格向下移动一格
ActiveCell.Offset(-1 , 0).Select'当前单元格向上移动一格
end sub
如果上述程序产生错误那是因为单元格不能移动,为了解除上述错误,我们可以往
sub my_offset 之下加一段代码 on error resume next
注意以下代码都不再添加 sub "代码名称" 和end sub请自己添加!
给当前单元格赋值:
ActiveCell.Value = "你好!!!"
给特定单元格加入一段代码:
例如:在A1单元格中插入"HELLO"
Range("a1").value="hello"
又如:你现在的工作簿在sheet1上,你要往sheet2的A1单元格中插入"HELLO"
1.sheets("sheet2").select
range("a1").value="hello"
或2.Sheets("sheet1").Range("a1").Value = "hello"
说明:
1.sheet2被打开,然后在将"HELLO"放入到A1单元格中.
2.sheet2不被打开,将"HELLO"放入到A1单元格中.
隐藏工作表
'隐藏SHEET1这张工作表
sheets("sheet1").Visible=False
'显示SHEET1这张工作表
sheets("sheet1").Visible=True
有时候我们想把所有的EXCEL中的SHEET都打印预览,请使用该段代码,它将在你现有的工作簿中循环,直到最后一个工作簿结束循环预览.
Dim my As Worksheet
For Each my In Worksheets
my.PrintPreview
Next my
得到当前单元格的地址
msgbox ActiveCell.Address
得到当前日期及时间
msgbox date & chr(13) & time
保护工作簿
ActiveSheet.Protect
取消保护工作簿
ActiveSheet.Unprotect
给当前工作簿改名为 "liu"
ActiveSheet.Name = "liu"
打开一个应用程序
AppActivate (Shell("C:\WINDOWS\CALC.EXE"))
增加一个工作簿
Worksheets.Add
删除当前工作簿
activesheet.delete
打开一个文件
Workbooks.Open FileName:="C:\My Documents\Book2.xls"
关闭当前工作簿
ActiveWindow.Close
当前单元格定为:左对齐
Selection.HorizontalAlignment = xlLeft
当前单元格定为:中心对齐
Selection.HorizontalAlignment = xlCenter
当前单元格定为:右对齐
Selection.HorizontalAlignment = xlRight
当前单元格为百分号风格
Selection.Style = "Percent"
当前单元格字体为粗体
Selection.Font.Bold = True
当前单元格字体为斜体
Selection.Font.Italic = True
当前单元格字体为宋体20号字
With Selection.Font
.Name = "宋体"
.Size = 20
End With
With 语句
With 对象
.描述
End With
让你的机器发出响声
BEEP
清除单元格中所有文字,批注,格式,所有的东西!
ActiveCell.Clear
测试选择状态的单元格的行数
MsgBox Selection.Rows.Count
测试选择状态的单元格的列数
MsgBox Selection.Columns.Count
测试选择状态的单元格的地址
Selection.Address
让所有的错误不再发生
ON ERROR RESUME NEXT
产生错误时让错误转到另一个地方
on error goto l
'code
l:
'code
删除一个文件
kill "c:\1.txt"
定制自己的状态栏
Application.StatusBar = "现在时刻: " & Time
恢复自己的状态栏
Application.StatusBar = false
在运行期执行一个宏
Application.Run macro:="text"
滚动窗口到a1的位置
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
定制系统日期
Dim MyDate, MyDay
MyDate = #12/12/69#
MyDay = Day(MyDate)
今天的年限
Dim MyDate, MyYear
MyDate = Date
MyYear = Year(MyDate)
MsgBox MyYear
产生一个inputbox
InputBox ("Enter number of months to add")
得到一个文件名:
Dim kk As String
kk = Application.GetOpenFilename("EXCEL (*.XLS), *.XLS", Title:="提示:请打开一个EXCEL文件:")
msgbox kk
打开zoom显示比例对话框
Application.Dialogs(xlDialogZoom).Show
激活字体对话框
Application.Dialogs(xlDialogActiveCellFont).Show
打开另存对话框
Dim kk As String
kk = Application.GetSaveAsFilename("excel (*.xls), *.xls")
Workbooks.Open kk
此段代码寻找字符串中特定字符的位置,需要建立一个窗体,并在窗体中,放入TEXTBOX1,TEXTBOX2和TEXTBOX3(3个文本框)及COMMANDBUTTON1(按钮)
Private Sub CommandButton1_Click()
a = TextBox1.Text
b = TextBox2.Text
aa = Len(a)
i = 1
Dim YY As String
If b = "" Then Exit Sub
Do
If InStr(i, a, b, vbTextCompare) = 0 Then Exit Do
kk = InStr(i, a, b, vbTextCompare)
YY = YY & CStr(kk) & "/"
i = kk + 1
Loop While aa >= i
TextBox3.Text = YY
End Sub
ActiveX控件在Excel97中的运用
Excel97在工作表或图表上可使用ActiveX控件,根据我使用的体会,在工作上处理控件时,必须注意和了解如下事项: (一)用Excel5.0/95工作簿文件格式保存Excel97工作簿时,将选择ActiveX控件信息.当用户通过双击鼠标来编辑内嵌在其它应用程序文档中的Excel97工作簿时,该工作簿上的控件将不会正常工作.如果用户是通过用右键单击工作簿,然后选中快捷菜单上的"打开"命令来编辑工作簿的话,工作簿上的控件就能正常工作了.
(二)当ActiveX控件处于激活状态时,将禁用某些MicrosoftExcelVisualBasic方法和属性.例如,当某一控件激活时,就不能使用Sort方法,故下述按钮单击事件处理过程中的代码将失败(因为用户单击按钮后,该按钮就处于激活状态).
PrivateSubCommandButton1Click
Range(〃a1:a10〃)SortKey1:=Range(〃a1〃)
EndSub
解决办法是通过选激活工作表上其它元素的方法来绕过这种问题.例如,可用下列代码对单元格区域排序:
PrivateSubCommandButton1Click
Range(〃a1〃)Activate
Range(〃a1:a10〃)SortKey1:=Range(〃a1〃)
CommandButton1Activate
End Sub
(三)在MicrosoftExcel中,用OLEObjects集合中的OLEObject对象代表ActiveX控件.如果要用编程的方式向工作表添加ActiveX控件,可用OLEObjects集合的Add方法.例如向第一张工作表添加命令按钮.
Worksheets(1)OLEObjectsAdd〃FormsCommandButton1〃,_
Left:=10,Top:=10,Height:=20,Width:=100
因为ActiveX控件也可用OLEObjects集合中的OLEObject对象代表,所以也可用该集合中的对象来设置控件的属性.例如要设置控件"CommandBotton1"的"左边位置"属性.
Worksheets(1)OLEObjects(〃CommandButton1〃)Left=10
那些不属于OLEObject对象属性的控件属性,可通过由Object属性返回的实际控件对象来设置.例如要设置控件"CommandButton1"的标题.
Worksheets(1)OLEObjects(〃CommandButton1〃)
ObjectCaption=〃runme〃
因为所有的OLE对象也是Shapes集合的成员,所以也可用该集合设置若干控件的属性.例如要对齐第一张工作表上所有控件的左边框.
ForEachsInWorksheets(1)Shapes
IfsType=msoOLEControlObjectThensLeft=10
Next
请注意,当在控件所在工作表的类模块之外使用控件的名称时,必须用工作表的名称限定该控件的名称.在工作表上ActiveX控件的事件处理过程中,Me关键字所指向的是工作表,而非控件
65,在Excel中利用VBA创建多级选单
Excel是我们常用的报表处理软件之一,对于大多数人来说只是使用它进行打印报表,没有注意其他功能,其实利用Excel内嵌的 VBA 语言完全可以快速开发出自己企业的应用系统来,而且应用系统界面与其它专业编程语言相当相似.下面笔者简单通过一个实例说明如何利用VBA创建多级选单.
首先,我们对Excel中两个重要的内置函数auto_open() 和 auto_close()作一简单说明.
auto_open() :在打开工作簿时系统将自动执行该函数,因此我们可以在该函数中调用自己应用程序的选单函数以及其它需要初始化设置的函数及宏语句;
auto_close():在关闭工作簿时系统将自动执行该函数.所以我们需要在该函数中放置删除用户自定义选单语句,否则只有退出Excel才能恢复EXCEL的系统选单.
在以下语句中,我们定义了选单设置函数 OpenMyMenu(),用于设置多级选单,其他有关说明见程序内注释,详细代码如下:
Sub OpenMyMenu() ′自定义多级选单函数
On Error Resume Next ′忽略错误
MenuBars("MyMenu").Delete ′删除自定义选单
MenuBars.Add ("MyMenu") ′自定义选单项
Sheets("sheet1").Select
MenuBars("MyMenu").Menus.Add Caption:="金融"
′增加第一个选单项"金融"
′以下三句为在"金融"选单下增加"银行法,货币政策和条例"三项选单项
MenuBars("MyMenu").Menus("金融").MenuItems.Add Caption:="银行法", OnAction:="银行法"
MenuBars("MyMenu").Menus("金融").MenuItems.Add Caption:="货币政策", OnAction:="货币政策"
MenuBars("MyMenu").Menus("金融").MenuItems.Add Caption:="条例", OnAction:="条例"
′以下为创建如图所示的多级选单
MenuBars("MyMenu").Menus.Add Caption:="经济" '建立选单项"经济"
′以下三句为在"经济"选单下增加"农业,工业和第三产业"三项选单项
MenuBars("MyMenu").Menus("经济").MenuItems.Add Caption:="农业", OnAction:="农业"
MenuBars("MyMenu").Menus("经济").MenuItems.Add Caption:="工业", OnAction:="工业"
MenuBars("MyMenu").Menus("经济").MenuItems.AddMenu Caption:="第三产业"
′以下三句为在"第三产业"选单下增加"概况,范畴"二项选单项和"饮食服务业"子选单
MenuBars("MyMenu").Menus("经济").MenuItems("第三产业").MenuItems.Add Caption:="概况", OnAction:="概况"
MenuBars("MyMenu").Menus("经济").MenuItems("第三产业").MenuItems.Add Caption:="范畴", OnAction:="范畴"
MenuBars("MyMenu").Menus("经济").MenuItems("第三产业").MenuItems.AddMenu Caption:="饮食服务业"
′以下二句为在"饮食服务业"选单下增加"酒店1,酒店2"二项选单项
MenuBars("MyMenu").Menus("经济").MenuItems("第三产业").MenuItems("饮食服务业").MenuItems.Add Caption:="酒店1", OnAction:="酒店1"
MenuBars("MyMenu").Menus("经济").MenuItems("第三产业").MenuItems("饮食服务业").MenuItems.Add Caption:="酒店2", OnAction:="酒店2"
MenuBars("MyMenu").Activate ′激活自定义选单
End Sub
Sub auto_open() ′系统自动打开运行宏
OpenMyMenu ′调用用户选单函数
End Sub
Sub auto_close() ′系统自动关闭运行宏
On Error Resume Next ′忽略错误
MenuBars("MyMenu").Delete ′删除自定义选单
End Sub
读者可以在自己的工作簿选单"工具"中的"宏"下,创建以上三个函数并将以上函数语句拷贝到其中即可运行.
66,.用VBA(编程)保护Excel文档
VBA(Visual Basic for Application)是Excel应用程序中功能非常强大的编程语言,为了规范不同的用户对Excel应用程序的访问能力,需要对Excel文档及有关的数据进行有效的保护, 这里根据自己及同行们的体会,从以下二个方面介绍用VBA编程法实现对Excel文档的保护.
对工作簿的保护
1.利用VBA中Workbook对象的SaveAs方法实现对工作簿的保护, 下面就对SaveAs有 关的参量作一介绍:
Filename:该字符串表示要保存的文件名. 可包含完整路径. 如果不指定路径,Microsoft Excel 将文件保存到当前文件夹.
FileFormat: 可选,文件的保存格式.
Password :为一个区分大小写的字符串(不超过15个字符) ,用于指定文件的保护密码.
WriteResPassword: 该字符串表示文件的写保护密码. 如果文件保存时带有密码,但打开文件时不输入密码,则该文件以只读模式打开.
ReadOnlyRecommended :如果为True则在打开文件时显示一条信息,提示该文件以只读模式打开.
下例就是在Excel应用程序中添加一工作簿,将工作簿按常规文件格式存为"C:\pj\obj\经济评价.xls"文件,并给该文件指定保护密码"12"以及写保护密码"23".
Sub 保护工作簿()
NewWorkbook = Workbooks.add
NewWorkbook.SaveAs FileName: ="C: \pj\obj\经济评价.xls", FileFormat: =
XlNormal,Password:="12",WriteResPassword:=〃23〃
End sub
2.利用VBA中Workbook对象的Protect方法对工作簿的结构和窗口进行保护,Workbook对象的Protect方法带有以下三个参量:
Password: 为加在工作表或工作簿上区分大小写的密码字符串. 如果省略本参数,不用密码就可以取消对该工作簿的保护.否则,取消对该工作表或工作簿的保护时必须提供该密码.如果忘记了密码,就无法取消对该工作表或工作簿的保护.最好在安全的 地方保存一份密码及其对应文档名的列表.
Structure: 若为True则保护工作簿结构(工作簿中工作表的相对位置).默认值为False.
Windows 若为True则保护工作簿窗口.
下例就是对一名为"经济评价.xls"的工作簿实现"结构"和"窗口"保护:
Sub 保护工作簿()
Workbooks (〃经济评价〃) .Protect Password: ="1234", Structure: =True,
Windows:=True
End sub
Workbook对象的Unprotect方法用于取消对工作簿的保护.Unprotect方法只有一个参量,就是保护工作表时所用的口令.
3.对工作簿进行隐藏保护,可使他人无法看到其对应的窗口.操作方法如下:
在VBA中使用Workbook对象下面的Windows对象的Visible属性对工作簿进行隐藏和取消隐藏,Visible属性的值可取"True"和"False"两种.
下面程序代码完成对工作簿"book.xls"的隐藏:
Sub 隐藏工作簿()
Workbooks("book").Activate
ActiveWindow.Visible = False
End sub
或:
Sub 隐藏工作簿()
Workbooks("book").Windows(1).Visible=False
End sub
对工作表的保护
1.对工作表实现口令保护利用VBA调用Worksheet对象的Protect方法对工作表进行保护,Protect带有以下参量:
Password 用于保护工作表的口令.
Drawingobjects 若为True,则对工作表中的Drawingobjects对象进行保护,缺省值为True.
Contents 若为True,则对单元格内容进行保护,缺省值为True.
下面程序代码完成对工作表"基础数据表"的保护:
Sub 保护工作表()
Worksheets(〃基础数据表〃).Protect Password:="1234"
End sub
2.对工作表实现隐藏保护,使他人无法看到工作表:
利用VBA设置Worksheet对象的Visible属性来隐藏工作表;Visible属性的值为以下三个值中的一个:
True 工作表为显示状态.
False 工作表为隐藏状态.
XlVerHidden 工作表为隐藏状态,且用户不能通过"取消隐藏"对话框将其改为显示状态.当Visible的值为XlVerHidden时,只能利用VBA将其重新设置为True.
Sub 隐藏工作表()
Worksheets("基础数据表").Visible=False
End sub
67,求:将所选区域中的数值全部转化为"万元"的最简代码.
求:将所选区域中的数值全部转化为"万元"的最简代码.
或能完成此功能的最便捷的命令操作.
写了一个,抛砖引玉:
Sub convt()
Dim cel As Range
Dim dec As Variant
Application.EnableEvents = False
yesorno = MsgBox("确实将区域所有数值转换为"万元" ", vbYesNo + vbQuestion + vbDefaultButton1)
If yesorno = vbYes Then
1: dec = Application.InputBox("请输入小数位数:", Default:=0, Type:=1)
If dec = "" Then
GoTo 1
End If
For Each cel In Selection
If IsNumeric(cel.Value) Then
'cel = (Round(cel.Value / 10000, 2)) & "万元"
cel = (Round(cel.Value / 10000, dec))
End If
Next
ElseIf yesorno = vbNo Then
Exit Sub
End If
End Sub
以下当为最简代码吧.
Sub Macro2()
Selection.NumberFormatLocal = "#"".""#,"
End Sub
68,在VBA开发环境中,点击工具――附加控件,出现如下窗口:

在可选控件中选择microsoft orogressbar control,再点击确定,这时,你会发现在你的工具箱中增加了进度条工具,如图:
下面说说进度条的用法:
属性:Max: 设置进度条控件的最大值
Min: 设置进度条控件的最小值
Value:设置进度条控件对象的当前值
Scrolling: 进度条的式样,即一格一格的进度条或是没有间隔的进度条.
例子:Private Sub UserForm_Activate()
For i = 1 to 5000
ProgressBar1.Max = 5000 '设置进度条控件的最大值
ProgressBar1.Value = I '进度条控件对象的当前值
next
End Sub
在程序中加入进度条的办法是将ProgressBar1.Max=最大值,ProgressBar1.Value = 当前值放入循环中去.
69,Excel是一个优秀的电子表格软件,如果你编的程序需要以报表的形式显示最终结果,它会是个不错的选择.你可以通过VB控制Excel显示数据表格.如果你不愿意深入了解Excel这些琐碎的细节,也不是没有权宜之计:你可以打开Excel中的工具→宏→录制新宏,然后进行手工操作,结束后把所录的宏代码贴进你的VB程序就行.这样得到的程序一般都能正常运行,但其中的宏代码往往不够简洁,效率和可读性都不高.
Excel编程碰到的第一个问题是表头.有时表头的形式比较复杂,需要横向或纵向合并单元格.请放心,只要没有斜杠,Excel都能应付得了.
例如合并A2~A5这4个单元格,你录制的宏代码会是这样:
Range("A2:A5").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
而自己编程只要一句 Range.("A2:A5").mergecells=True 就可以解决问题.
表头形式定了,再就是表头的内容.如果单元格中的文本长度超过了列宽,往往只能显示部分内容,行尾那一格的内容则会"越境"进入右边那个空白单元格,很不美观.这个问题可以通过在程序中设置列宽加以解决.
Columns(14).columnwidth=12 '设置第14列列宽为12(缺省列宽为8.38)
如果你不愿意劳神去逐列估计实际所需的列宽,干脆来一行
Columns("a:i").autofit 'a到i列自动调整列宽
让Excel随机应变吧.
但也许你不喜欢这种方法,认为表头撑大了列宽,弄得浏览一张小表格还得向右滚动,太不方便了.要是能保持默认列宽,让文本自动换行就好了.没问题,Excel包你满意.
Rows(3).WrapText=True '让第三行各单元格中的文本自动换行
不过你最好再加一句 Rows(3) .VerticalAlignment = xlTop 让表头自动向上对齐,这样比较符合习惯.
你还可以给表头打上底色,让你的读者不至于看了打哈欠.
Rows(2). Interior .ColorIndex = 5 设置第2行底色为蓝色
再给表格的标题上色,这样更醒目一点.
Rows(1).Font.ColorIndex=4
表头完成后该填数据了,一个一个地填实在是太 ,如果你的数据是存放在一个二维数组中,那问题就简单多了.
Dim Data(3,4)
………… '数据处理
Range("a2:d4").Value=Data
这样可以一次填入一个表的所有数据,够快了吧!不过提醒一句,Range对象大小最好与数组匹配,小了无法显示所有数据,大了则会在空白单元格只填入"N/A"表示没有取得数据.
如果需要在结果中显示多个同样规格的数据表,想在Range对象中加入循环变量,这也好办.
Dim cell11,cell2
Dim Data(3,4)
…………
For I =1 to 40
………… '数据处理
Set cell1=Worksheets("Sheet1").Cells(5*I-4,1)
Set cell2=Worksheets("Sheet1").Cells(5*I-2,4)
Worksheets("Sheet1").Range(cell1,cell2).value=Data
Next I
表格填完了,现在该打表格线了,以下几条语句可以满足你的要求:
With Worksheets("Sheet1").Range(cell1,cell2).borders
.LineStyle=xlContinuous
.weight=xlThin
End With

71,1.文件菜单栏(Excel自带的)
文件就叫Commandbars("File"),你可以在打开命令后加个菜单,类似的,编辑就是Commandbars("Edit"),视图就是Commandbars("View"),这些不会因语言版本不一样而不同,全部是一样的.
2.快捷菜单栏
好像那些打印啊,打开文件啊,居中对齐啊那些呢,叫Commandbars("Standard"),那些字体大小啊,颜色啊,就叫("Formatting").
3.右键
Excel的右键也是可以更改的,名叫:Commandbars("Cell")
4.自定义菜单
类似于数据透视表的那些浮动的窗体,不用名字,用Commandbars.add就可以追加了,最简单了.如果不能运行,记得加个Application.在Commandbars的前面.
72,由于公司合同较多,每次缴印花税时,计算每份印花税所需要的各面额印花的张数是一件比较繁琐的事.如:这份合同需要缴纳23元的印花税则需要买两张10元,一张2元,一张1元面额的印花.幸好有Excel这个财会人员的得力工具,我编写了一段宏代码来自动帮我计算各面额印花的张数,既快捷又方便.想到还有很多同行要做这项工作,所以写下来与大家共享.具体操作如下:
新建一个Excel文件存为stampduty.xls,进入宏编辑器(Alt+F11)在ThisWorkbook中加入以下代码:
'印花税各面额印花张数计算程序 BY-RAY DENG
'用途:已知印花税额,计算各面额印花张数
'使用方法:做好工具条后,只要选择要计算的原始数据,点击"印花税面额计算"工具条即可.
' (请不要选择全列,否则无效的计算太多.)
Sub stamp_duty()
Static flagcal As Integer '计算标志,首次计算时清空计算结果页,否则在结果页追加计算结果
Static rowbegain As Integer '计算结果页上可用行号,计算结果追加时从这里开始
Dim filename As String '打开的需要计算数据的文件名
Dim moneytype(7) As Single '定义面额
Dim money As Single
Dim billno As Integer '票额张数
filename = ActiveWorkbook.Name '记录当前打开的文件名
If filename = VBAProject.ThisWorkbook.Name Then '不要在自身这个文件中操作
MsgBox "请选择其它文件中的数据!", vbInformation + vbOKOnly
Exit Sub
End If
Application.ScreenUpdating = False '将屏幕更新关闭,可加快宏的运行速度
billno = 0 '计数清0
moneytype(0) = 100 '印花税面额为0.5-100,以0.5为舍入标准
moneytype(1) = 50
moneytype(2) = 10
moneytype(3) = 5
moneytype(4) = 2
moneytype(5) = 1
moneytype(6) = 0.5
VBAProject.ThisWorkbook.Sheets(1).Activate '打开结果页
If flagcal = 0 Then '如果为第一次计算,则清空结果页
Cells.Select
Selection.ClearContents
Range("A1").Select
End If
ActiveSheet.Cells(1, 1) = "Origin DATA" '先写上表头
For i = 0 To 6
ActiveSheet.Cells(1, i + 2) = moneytype(i)
Next i
Workbooks(filename).Activate '转到要计算的原始数据数据文件
rowno = ActiveWindow.RangeSelection.Rows.Count '所选区域有几行
rowstart = ActiveWindow.RangeSelection.Row '起始行
colstart = ActiveWindow.RangeSelection.Column '起始列
j = rowbegain '结果页中写结果的起始行
For i = 1 To rowno '开始读原始数据
origindata = Cells(i + rowstart - 1, colstart) '读原始数据
'广州印花税尾数处理方法:过0.5进1,不足舍去,刚好为0.5及其倍数则不变 If origindata * 100 Mod 50 0 Then
money = Round(origindata, 0) '以0.5为界限作四舍五入
Else
money = origindata '整数及尾数刚好为0.5不作处理
End If
VBAProject.ThisWorkbook.Sheets(1).Activate '转到结果页
ActiveSheet.Cells(i + 1 + j, 1) = origindata '在第一列写入原始数据
Workbooks(filename).Activate ''转到要计算的原始数据数据文件
For k = 0 To 6 '循环计算各面额所需张数
While money >= moneytype(k)
money = money - moneytype(k)
billno = billno + 1
Wend
VBAProject.ThisWorkbook.Sheets(1).Activate
ActiveSheet.Cells(i + 1 + j, k + 2) = billno '在相应位置写入该面额张数
billno = 0 '清0准备计算下一面额
Workbooks(filename).Activate
Next k
rowbegain = rowbegain + 1 '结果页中写结果的起始行下移一行
Next i
flagcal = flagcal + 1 '计算次数累加
rowbegain = rowbegain + 1 '添加一空行区分不同次数的结果
Application.ScreenUpdating = True '恢复屏幕刷新
VBAProject.ThisWorkbook.Sheets(1).Activate '打开结果页
End Sub
编好后存盘,退出宏编辑器.开始构建一个工具栏:[工具]—[自定义],点击[工具栏]---[新建],工具栏名称定为"印花税",这时会出现 ,点击[命令],将 "命令"列表中任意图标拖动到这个工具栏中,在刚拖过来的图标上点右键,选择[命名],将其改为"印花税面额计算",选择[总是只用文字],这时会出现 ,在"印花税面额计算"上点一次右键,选择[指定宏],选择"ThisWorkbook.stamp_duty" ,点[确定]即可.以后要计算时,只要选好原始数据,点击该工具栏即可.
73,Sub AA()
Sheet1.Visible = 2 '深度隐藏
Sheet1.Visible = -1 '显示
End Sub
74,你在"workbook"的 Private Sub Workbook_BeforeClose(Cancel As Boolean) 中加入如下句子:myNowTime = Format(Now, "yymmdd" & "-" & "hhmmss")
ActiveWorkbook.SaveAs Filename:=currPath & myNowTime & ".xls", AddToMru:=False
'("currPath" 为你的路径及文件名称)
这样你保存文件可以年,月,日来实现.甚至可以小时,分,秒来保存.对于在编程序的来讲比较有用.如果你不需要"小时,分及秒",可把后面的 [& "-" & "hhmmss")]取消即可.
75,我们日常上送,下发的报表材料,通知等都要加盖公章,如果把这项工作交给Excel或Word来完成,我们的工作就轻松多了.
第一步:制作公章图案
首先我们要做出一个公章的图案,最简单的办法是把公章图案扫描到电脑中,然后处理成透明的GIF图像.我们也可以直接用Excel来制作:把绘图工具打开,选中"椭圆"工具,在按下"Shift"键的同时拖开鼠标,就可以得到一个正圆了.双击这个正圆打开"设置自选图形格式"对话框,在"颜色与线条"标签中,填充颜色选"无填充颜色",线条颜色设为红色,选3磅粗的单线形(图).公章的文字用艺术字来制作,填充颜色和线条颜色都用红色,并设成无阴影产.弧形文字和水平文字要分开来做,在做弧形文字时,把艺术字拖到圆形的上方,在艺术字工具中选"艺术字形状-细上弯弧",按住黄色的四方块往下拉,再作适当的调整,就可以做出公章里的圆弧形的文字了.公章中间还有一个红五星,用"自选图形"的星形就可以做出来了,填充颜色和线条颜色用红色.最后,按住"Shift"键把组成公章的文字,图形全部选上,执行右键菜单中的"组合"命令,一个公章就做好了.
如何把做好的公章保存出来 这里有一方法:把工作表另存为Web页,然后到保存目录中找到*.files的文件夹,里面有一个GIF图片,这就是刚才做好的公章图案了,它的背景是透明的,我们把它改名为gongzhang.gif保存下来即可.
第二步:添加"盖章"按钮
接下来我们给Excel添加一个盖章按钮,当一个工作表做好后,点击这个盖章按钮,就可以为我们盖上公章了.
先把公章图形复制出来(用来粘贴作为按钮的图标),然后打开"工具-自定义"对话框,选中"命令"标签,在"类别"栏中找到"宏",在右边的"命令"栏里就会出现一项"自定义按钮".用鼠标把这个笑脸图标拖出到菜单栏或工具栏上放下,在笑脸图标上击右键,在弹出的菜单中把"命名"处的文字改为"加盖公章".接下来点击"粘贴按钮图标"这个命令,就可以用刚和复制的公章图形来代替笑脸图标了.把鼠标移下来选中"分配超级链接-插入图片",然后在"请键入文件名称或Web页名称"栏里输入公章图片gongzhang.gif的文件名及路径,然后按"确定"返回.
好了,看到"加盖公章"这个按钮了吧,点击一下看看,呵呵,页面上就盖上一个鲜红的公章了,用鼠标可以把它拖到任意的地方.在Word文档中加盖公章的方法与此大同小异,大家可以自己试一试.
76,如何使某一个固定的菜单项无效
Application.CommandBars("File").Controls(6).Enabled = False
Controls(6)中的6就是所在菜单栏的行数
我觉得这样可能更可靠:
Dim i As Integer
For i = 1 To Application.CommandBars("File").Controls.Count
If Application.CommandBars("File").Controls(i).Caption = "另存为(&A)..." Then
Application.CommandBars("File").Controls(i).Enabled = False
End If
Next i
77,请教大家,我想在EXCEL文件退出时,另存为当前目录下的子目录BAK中,比如EXCEL文件在C盘,就另存为C:\BAK子目录下,但EXCEL文件的位置不确定,不知如何写语句
注意先引用"Microsoft Scripting Runtime"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' 在本文件要关闭前,执行备份动作.
If Not ActiveWorkbook.Saved Then Exit Sub ' 若原文件未存档,备份档也不存.
ThisPath = ThisWorkbook.Path
' 假定备份文件夹之名称为"BAK",若本文件是备份档,则不需再备份.
If Len(Application.WorksheetFunction.Substitute(ThisPath, "BAK", "")) 0 Then
If (GetAttr(Bak) And vbDirectory) = vbDirectory Then GoTo 3 ' 若已存在,跳到下一步.
End If
MkDir Bak ' 建立备份文件夹.
' 改变目前路径到备份文件夹路径,并备份之.
3 ChDir Bak
Application.EnableEvents = False ' 避免执行BeforeSave事件.
Application.DisplayAlerts = False ' 避免显示是否要覆盖原备份文件之讯息.
ActiveWorkbook.SaveAs
ChDir ThisPath ' 改变目前路径回到原文件夹路径.
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
上述程式有个漏洞,就是在本文件做过更动,且存档过了,但最后一次的更动未存档,则 If Not ActiveWorkbook.Saved Then Exit Sub 这个判断会造成不存备份档之错误决定.
若想弥补上述错误,将触发的事件改成Workbook_BeforeSave,却会造成Excel关闭的错误.
78,文件保存为以某一单元格中的值为文件名的宏怎么写
用命令: ActiveWorkbook.SaveCopyAs Str(Range("Sheet1!A1")) + ".xls"
79,Offset 属性
参阅应用于示例特性应用于 Range 对象的 Offset 属性.
返回一个 Range 对象,该对象代表某个指定区域以外的区域.只读.
expression.Offset(RowOffset, ColumnOffset)
expression 必需.该表达式返回一个 Range 对象.
RowOffset Variant 类型,可选.区域偏移的行数(正值,负值或 0(零)).正值表示向下偏移,负值表示向上偏移,默认值为 0.
ColumnOffset Variant 类型,可选.区域偏移的列数(正值,负值或 0(零)).正值表示向右偏移,负值表示向左偏移,默认值为 0.
应用于 TickLabels 对象的 Offset 属性.
返回或设置各级别标签之间的距离以及第一级标签与坐标轴之间的距离.其默认距离为百分之百,代表坐标轴标签与坐标轴之间的默认距离.其值可以为从 0 到 1000 之间的一个整数百分比,表示相对于坐标轴标签的字体大小.Long 类型,可读写.
expression.Offset
expression 必需.该表达式返回一个 TickLabels 对象.
示例
应用于 Range 对象.
本示例激活 Sheet1 上活动单元格向右偏移三列,向下偏移三行处的单元格.
Worksheets("Sheet1").Activate
ActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate
本示例假定 Sheet1 中包含一个具有标题行的表格.本示例先选定该表格,但并不选择标题行.运行本示例之前,活动单元格必须位于表格中.
Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count).Select
应用于 TickLabels 对象.
如果偏移量小于 500,则本示例将 Chart1 中数值轴上标签之间的距离设置为当前距离的两倍.
With Charts("Chart1").Axes(xlValue).TickLabels
If .Offset 15 then
b=10
else
b=100
end if
可改为如下if语句
b=iif(a.15,10,100)
93,F12用于启动另存为对话框
94,x=shell(calc.exe,1)可打开计算器程序
95,call语句格式:Call[()]如果过程本身没有参数,则实参和括号可省略,并报参数放在括号中,另一个调用Sub过程的方法是: []比前一个少了Call和括号,子过程调用语句的实参在数目,类型,排列上与子过程定义语句的形式参数表一致
96,自定义函数一例,求abc三个数的平均
Private Function Passed(By Val a As integer, By Val b As integer, By Val c As integer)
ave=(a+b+c)/3
End Sub
在定义时必须向函数过程名赋值,而子程序名不能赋值
97,在工作表任意单元格输入=Cell("filename")可获得文件的完整路径,文件名和工作表名
98,在打开Excel文件时按住Shift键,将不运行VBA过程,可防止宏病毒,单击文件 关闭命令,在点关闭时按住Shift键将在不运行VBA过程的情况下关闭工作簿,可防止关闭时自动运行的宏病毒.
99,让一个变量得到单元格A1到A5的总和(变量设为X),
X=Sum(Range("A1:A5")) 错
X=Application.WorksheetFunction.Sum(Range("A1:A5"))正确
也就是说必须通过Application的WorksheetFunction属性间接调用工作表函数
100,Rem和'是注释符,注释语句是非执行语句,要养成对代码注释的习惯.

101,ActiveSheet.Next.Select选择活动工作表下一张工作表,
ActiveSheet.Previous.Select选上一张
102,单元格B2:B10数值不全为空用IF语句表达
For Each cl in ActiveSheet.Range("B2:B10")
If cl.Value "" then
msgbox "有非空单元格"
Exit For
endif
next
103,用Range引用单元格和单元格区域
Range("A1") 单元格A1
Range("A1:B5") 从单元格A1到B5区域
Range("A1:B5 ,B1:B7") 多块的选定区域
Range("A:A") A列
Range("1:1") 第一行
Range("A:C") A列到C列的区域
Range("1:5") 第1行到第5行的区域
Range("1:1,3:3") 第1,行
Range("A:A,C:C") A列,C列
104,用Cells及编号引用单元格
Cells(6,1)A6单元格
如果对工作表用Cells属性时不指定索引,表示引用工作表上的所有单元格,下例清除活动工作簿中工作表Sheet1上所有单元格的内容
Worksheets("sheet1").Cells.ClearContents
105,可用变量代入单元格索引值,故Cells属性非常适用于在单元格区域中循环,如:
For counter=1 To 20
Worksheets("sheet1").Cells(counter,3).value=counter
Next counter
106,引用行或列
Rows(1) 第1行
Rows 所有行
Columns(1) 第1列
Columns("C") 第3列
Columns 工作表上所有列
106,可用方括号将A1样式的引用或命名区域的名称括起来,作为Range属性的快捷方式,这样就不必键入Range 和引号,如
Worksheets("sheet1").[A1:B2].clearContents
[MyRange].Value=30
107,用Offset处理按相对于其他单元格的某一位置的常用办法是使用Offset属性,本例将活动工作表上活动单元格下一行和右边三列的单元格的内容设置为下划线,如:
ActiveCell.Offset(1,3).font.Underline=XlDouble
108,把别的工作表Sheet2数据,读到当前工作表的方法列举
1)[A1]=Sheet2.[A1] 把Sheet2A1单元格的数据,读到A1单元格
2)[A2:A4]=Sheet2.[B1] 把Sheet2单元格B1的数据读到A2:到A4单元格
3)Range(B1")=Sheet2.Range("B1") 把Sheet2工作表单元格B1数据,读到B1单元格
4)Range("C1:C3")=Sheet2.Range("C1") 把Sheet2工作表单元格C1数据,读到C1:C3
5)Cells(1,4)=Sheet2Cells(1,4) 把Sheet2工作表单元格D1数据,读到D1 单元格
6)Range(Cells(1,5),Cells(5,5)=Sheet2.Cells(1,5) 把sheet2工作表单元格E1数据,读到E1:E5单元格
7)Selection.Value=Sheet2.[F1] 把Sheet2 工作表单元格[F1]数据,读到任何你点选的单元格
109,Sub前有个Private表示是私有子程序,这个子程序不会出现在"宏"对话框中
110,Sub test()
ActiveSheet.Calculate
End Sub重算活动工作表
111,编程前应该尽可能地多了解Excel对象的属性,方法
112,每一个Excel对象的属性,方法的调用都要通过OLE连接的一个或多个调用,这些OLE调用都是需要时间的,减少使用对象引用能加快VBA代码的运行
113,使用With语句
Workbooks(1).Sheets(1).Range("A1:A1000").font.Name="Pay"
Workbooks(1).Sheets(1).Range("A1:A1000").Font.Fontstyle="Bold"…
改用With语句则运行速度加快
例:With Workbooks(1).Sheets(1).Range("A1:A1000").font
.Name="Pay"
.Fontstyle= Bold"
…
End With
114,用set设置对象变量,以减少对象的访问,如:
Set MyRange=Workbooks(1).Sheets(1)
Mysheet.Range("A1").Value=100
Mysheet.Range("A2").Value=200
比直接用Workbooks(1).Sheets(1). Range("A1").Value=100
Workbooks(1).Sheets(1). Range("A2").Value=200运行快
115,在循环中要尽是减少对象的访问
For k=1 To 100
Sheets("sheet1").select
Cells(k,1).value=Cells(1,1).Value
Next k
更快的代码是
set TheValue=Cells(1,1).Value
Sheets("sheet1").select
For k=1 To 100
Cells(k,1).value=TheValue
116,减少对象的激活和选择
如果你是通过录制宏的来学习VBA的程序里一定充满了对象的激活和选择,如Workbooks(XXX).active ,Sheets(XXX).Select ,Range(XXX).Select等,但事实上大多数情况下这些操作不是必须的,如
Sheets("sheet1").Select
Range("A1").Value=100
Range("A2").Value=200
可改为With sheets("Sheet3")
.Range ("A1")=100
.Range ("A2")=200
117,关闭屏幕更新是提高运行速度的最有效的办法,推荐使用
Application.ScreenUpdate=False
程序运行后再改回来
118,VBA中默认的数据类型是Variant,你必须选择使用何种数据类型,因为Variant数据类型占用存储空间较大(16或22字节)而且它将影响程序的性能,Vba必须识别Variant类型的变量中存储了何种数据类型.
119,再列一个自定义函数计算价格为10%为运费的简单函数例子
Public Function Shipping(Price)
Shipping=Price*0.1
End Function
如还是不懂的话,将上述过程复制到模块中,然后在工作表任意单元格中输入=Shipping(C1)你就会明白
120,ActiveWindow.DisplyGridlines=False 此句用来关闭网格线.
122,Private Sub Calendar1_Click()
ActiveCell = Me.Calendar1.Value
End Sub
123,设置日历控件字号
Private Sub UserForm_Initialize()
Me.Calendar1.GridFont.Size = 14
End Sub
124,以下是从金刚金作品里提出来的部份事件代码,做得很好,值得借鉴学习:
Private Sub Workbook_Activate()
MsgBox "工作簿被切换为作用工作簿", vbInformation, "Workbook_Activate"
End Sub
Private Sub Workbook_AddinInstall()
MsgBox "激活新的加载宏时", vbInformation, "Workbook_AddinInstall"
End Sub
Private Sub Workbook_AddinUninstall()
MsgBox "取消以前选取的加载宏时", vbInformation, "Workbook_AddinUninstall"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "工作簿被关闭之前", vbInformation, "Workbook_BeforeClose"
End Sub
Private Sub Workbook_BeforePrint(Cancel As Boolean)
MsgBox "工作簿打印之前", vbInformation, "Workbook_BeforePrint"
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "工作簿进行保存之前", vbInformation, "Workbook_BeforeSave"
End Sub
Private Sub Workbook_Deactivate()
MsgBox "工作簿切换为非作用工作簿", vbInformation, "Workbook_Deactivate"
End Sub
Private Sub Workbook_NewSheet(ByVal Sh As Object)
MsgBox "新建工作表", vbInformation, "Workbook_NewSheet"
End Sub
Private Sub Workbook_Open()
MsgBox "打开工作簿", vbInformation, "Workbook_Open"
End Sub
Private Sub Workbook_PivotTableCloseConnection(ByVal Target As PivotTable)
MsgBox "数据透视表关闭与其数据源的连接之后", vbInformation, "Workbook_PivotTableCloseConnection"
End Sub
Private Sub Workbook_PivotTableOpenConnection(ByVal Target As PivotTable)
MsgBox "数据透视表打开与其数据源的连接之后", vbInformation, "Workbook_PivotTableOpenConnection"
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
MsgBox "工作表" & Sh.Name & "切换为作用工作表", vbInformation, "Workbook_SheetActivate"
End Sub
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
MsgBox "任一单元格双击鼠标之后", vbInformation, "Workbook_SheetBeforeDoubleClick"
End Sub
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
MsgBox "任一单元格单击鼠标右键之后", vbInformation, "Workbook_SheetBeforeRightClick"
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
MsgBox "工作表内容进行重算之后", vbInformation, "Workbook_SheetCalculate"
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox "在工作表内进行不同的操作", vbInformation, "Workbook_SheetChange"
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
MsgBox "工作表" & Sh.Name & "切换为非作用工作表", vbInformation, "Workbook_SheetDeactivate"
End Sub
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
MsgBox "按下Excel超链接之后", vbInformation, "Workbook_SheetFollowHyperlink"
End Sub
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
MsgBox "数据透视表更新之后", vbInformation, "Workbook_SheetPivotTableUpdate"
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox "工作表里选取不同单元格范围时", vbInformation, "Workbook_SheetSelectionChange"
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
MsgBox "工作簿切换为作用工作簿", vbInformation, "Workbook_WindowActivate"
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
MsgBox "工作簿切换为非作用工作簿", vbInformation, "Workbook_WindowDeactivate"
End Sub
Private Sub Workbook_WindowResize(ByVal Wn As Window)
MsgBox "工作簿被打开或窗口最大化,最小化之后", vbInformation, "Workbook_WindowResize"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "选取不同单元格范围时", vbInformation, "Worksheet_SelectionChange"
End Sub
125,Rem 选定单元格的个数
Sub t()
MsgBox Selection.Cells.Count
End Sub
126,Application.OnKey "{F11}", "ccc"禁用F11
127,'选中A列除A1外的第一个非空单元格
[a65536].End(xlUp).Offset(1, 0).Select
128,Private Sub CommandButton1_Click()
On Error GoTo ad '注意后面的ad,这是错误处理的用法
i = Range("a1").Value
Sheets(CStr(i)).Select
ret = MsgBox("是否覆盖", vbYesNo, "")
If ret = vbYes Then
Range("A5:C7").Copy Sheets(CStr(i)).Range("A1") '重点在copy之后
Else
Sheets.Add.Name = i & ".2"
Range("A5:C7").Copy Sheets(CStr(i & ".2")).Range("A1")
End If
End
ad:
Sheets.Add.Name = i '工作表名的变化是可见的
Range("A5:C7").Copy Sheets(CStr(i)).Range("A1")
End Sub
129,用变量取代Sheet1中的"1"
For i = 1 To 3
Sheets("sheet" & i).Range("A2") = "1234"
Next
下面两组程序摘自杨开科的"我的自学收集资料".我都分别进行了试用,"14,将工作薄中的全部n张工作表都在sheet1中建上链接"这段程序试用成功,但有个建议:当工作表超过一定数量时(例如超过30张),第31张则在B列反映,如此类推.
第二组程序我则没有那么幸运了,试用不成功,不知是什么原因.请指教!
14,将工作薄中的全部n张工作表都在sheet1中建上链接
Sub test2()
Dim Pt As Range
Dim i As Integer
With Sheet1
Set Pt = .Range("a1")
For i = 2 To ThisWorkbook.Worksheets.Count
.Hyperlinks.Add Anchor:=Pt, Address:="", SubAddress:=Worksheets(i).Name & "!A1"
Set Pt = Pt.Offset(1, 0)
Next i
End With
End Sub
32,平时只能看到表1,如要看表2和表3,只能通过表1的链接打开,且表2和表3回到表1后,又不可见.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$3" Then '当点击"$A$3"单元格时...
Sheet2.Visible = 1 '取消隐藏
Sheet2.Activate '激活
ActiveSheet.Range("A1").Select
End If
If Target.Address = "$A$6" Then
Sheet3.Visible = 1 '取消隐藏
Sheet3.Activate
ActiveSheet.Range("A1").Select
End If
End Sub
[ Last edited by 吾识野 on 2004-9-4 at 12:08 ]
55楼说
吾识野:将下列代码加入sheet2,sheet3
Private Sub Worksheet_Deactivate()
Me.Visible = False
End Sub
56楼说 向EXE文件里导入数据时出错 请帮助解决!!改日请你吃小酒!
向EXE文件里导入数据时出错(文件是xls文件编译的EXE文件)!
Sub 导入()
Dim Filename
Filename = Application.GetOpenFilename(FileFilter:="Excel文件(*.xla; *.xls),*.xla;*.xls", Title:="选择Excel文档") '选择要导入的xls文件
If Filename = False Then Exit Sub
Workbooks.Open Filename, Password:=sy '文件名有口令 */可能有错/*
Rows("1:350").Select '导入行
Application.CutCopyMode = False
Selection.Copy
Windows("基层版V2.0").Activate '导入的目标文件 *正确
Sheets("SJ").Select '表有
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
' Workbooks("10401.xls").Close SaveChanges:=False '***要关闭上面选择的文件(文件名很多都不同样)////如何解决
End Sub
Excel基础应用实例教程
杨开科整理
1,我在单元格中填上"="现在是"&NOW()",想显示目前的时间,为什么会变成"今天是38645.6480",有什么办法解决吗
方法1=now()单元格设置为你想显示的方式,如:现在是yyyy年m月d日h点m分.
方法2,直接用TEXT()函数:=TEXT(NOW(),"现在是yyyy年m月d日h点m分")
第一种方法较好,可以参与计算.
=TEXT(NOW(),"现在是 yyyy/mm/dd h:mm AM/PM")
显示现在是 2005/04/14 4:42 PM
2,自定义单元格格式 [=0]"男";[=1]"女"; 则可实现输入0显示为"男".输入1显示为"女".
3,excel里面如何添加自定函数帮助信息呢
在VBE打开对象浏览器,找到自定函数,右键——属性中添加
4,怎么解决Excel自动将0开头的数字自动删除0
是从"获取外部数据"导入的吗 在第3步可以选择"列数据格式"为文本,这样零就不会被删除了.
5,打开多个EXCEL文档,照理应该在状态栏显示多个打开的文档,以便各文档互相切换,但现在只能显示一个文档,必须关掉一个才能显示另一个,关掉一个再显示另一个,不知何故
可以从"窗口"菜单中切换窗口.
或者改回你原来的样子:工具/选项/视图,选中任务栏中的窗格.
6,目的:表中>50000的单元格红色显示.做法:选择整张表,在条件格式命令中,设置了">50000以红色填充单元格"的条件,出现的问题:表头(数值为文本)的单元格也呈红色显示.我知道,原因是因为区域选择得不对,如果只选择数字区域不会出现这种情况,如果表结构简单,则好处理,如果表格结构复杂,这样选择就很麻烦.有没有办法选择整张表,但是表头(数值为文本)的单元格不被条件格式.
答:条件格式设置公式=--A1>50000
问=--A1>50000中的--代表什么意思,
答:转变为数值.与+0,*1,是一样的效果.
7,,如何打印行号列标
答:文件菜单-----页面设置---工作表----在打印选项中的行号列标前打勾.
8,如何打印不连续区域
答:按CTRL键不松,选取区域,再点文件菜单中的打印区域--设置打印区域.
9,打印时怎样自动隐去被0除的错误提示值
答:页面设置—工作表,错误值打印为空白
10,如何设置A1当工作表打印页数为1页时,A1=1,打印页数为2页时,A2=2,...
答:插入名称a=GET.DOCUMENT(50, "Sheet1")&T(NOW()),在A1输入=a
11,Add More Levels of Undo to Excel for Windows
增加excel的后悔次数!!!excel默认的是16次,不信你试试!!!
现在我改成30次.
到注册表(不知道,在开始运行里输入regedit回车即可)
到以下位置!!!!我的是office2003!!!!在11.0处可能有所不同!
[HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options]
新建dword值 键名为UndoHistory (双击名称)值为10进制,输入数值为30.即可
12,我想在几千个数据中列出没有重复的数据
答:数据_筛选_高级筛选_选择不重复记录.
13,我需要保留一位小数,不管后面是什么数字,超过5或不超过5,都向前进一位.
例如:329.99----->330.00
329.84------------>329.90
329.86------------>329.90
答:=roundup(*,2)或=round(a1+0.04,1)
14,D列为字符型值,转换为数型的的值.如何进行操作
答: 复制任一空单元格.选中D列,选择性粘贴,加.
15,在Excel中如何输入带有上,下标的符号
答:选中,设置单元格格式,上标或下标.
16,怎样使一个单元格里的数据,变成每个数字占一个单元格啊
答:《数据》->分列->选中固定宽度,点击《下一步》,在预览分列效果中的数字中间依次点击,点击《完成》按钮,OK!
17,将一列文本格式的数据转换为常规
答:数据—分列,一直点下一步,最后一步选常规就行了.
复制(空单元格)或1,选择性粘贴加或乘除.
18,小于10以下用红色,以上用蓝色标识单元格
答:自定义格式 [红色][10]
也可设定两组条件格式.
19,如何用函数来获取单元格地址
答:=ADDRESS(ROW(),COLUMN())
20,求A1:B10中A列等于1的对应B列中的最小值
答:=min(if(a1:a10=1,b1:b10))
输入后按ctrl+shift+enter完成.
21,怎样定义格式表示如00062920020001,00062920020002只输入001,002
答:格式----单元格----自定义----"00062920020"@----确定
22,如何统计A1:A10,D1:D10中的人数
答:=COUNTA(A1:A10,D1:D10)
23,A2单元格为 2005-3-24 10:00:00 想在B2单元格通过公式转换成 2005-3-24 23:59:59 如何转
①=(TEXT(A2,"yyyy-m-d")&" 23:59:59")*1
然后设置为日期格式
②=INT(A2)+"23:59:59"
再把单元格格式设置一下.
③=INT(A2+1)-"0:0:1"
24,我用方向键上下左右怎么不是移动一个单元格,而是向左或向下滚动一屏,好奇怪啊,平时都好好的,有没有解决的办法
答:是不是按下了ScrollLock键.
25,复制粘贴中回车键的妙用
1, 先选要复制的目标单元格,复制后,直接选要粘贴的单元格,回车OK;
2,先选要复制的目标单元格,复制后,选定要粘贴的区域,回车OK;
3,先选要复制的目标单元格,复制后,选定要粘贴的不连续单元格,回车OK.
26,摄影功能
用摄影功能可以使影像与原区域保持一样的内容,也就是说,原单元格区域内容改变时,影像也会跟着改变,是个很好用的功能.
27,定义名称的妙处
名称的定义是EXCEL的一基础的技能,可是,如果你掌握了,它将给你带来非常实惠的妙处!
1. 如何定义名称
插入 – 名称 – 定义
2. 定义名称
建议使用简单易记的名称,不可使用类似A1…的名称,因为它会和单元格的引用混淆.还有很多无效的名称,系统会自动提示你.
引用位置:可以是工作表中的任意单元格,可以是公式,也可以是文本.
在引用工作表单元格或者公式的时候,绝对引用和相对引用是有很大区别的,注意体会他们的区别 – 和在工作表中直接使用公式时的引用道理是一样的.
3. 定义名称的妙处1 – 减少输入的工作量
如果你在一个文档中要输入很多相同的文本,建议使用名称.例如:定义DATA = "I LOVE YOU, EXCEL!",你在任何单元格中输入"=DATA",都会显示"I LOVE YOU, EXCEL!"
4. 定义名称的妙处2 – 在一个公式中出现多次相同的字段
例如公式=IF(ISERROR(IF(A1>B1,A1/B1,A1)),"", IF(A1>B1,A1/B1,A1)),这里你就可以将IF(A1>B1,A1/B1,A1)定义成名称"A_B",你的公式便简化为=IF(ISERROR(A_B),"",A_B)
5. 定义名称的妙处3 – 超出某些公式的嵌套
例如IF函数的嵌套最多为七重,这时定义为多个名称就可以解决问题了.也许有人要说,使用辅助单元格也可以.当然可以,不过辅助单元格要防止被无意间被删除.
6. 定义名称的妙处4 – 字符数超过一个单元格允许的最大量
名称的引用位置中的字符最大允许量也是有限制的,你可以分割为两个或多个名称.同上所述,辅助单元格也可以解决此问题,不过不如名称方便.
7. 定义名称的妙处5 – 某些EXCEL函数只能在名称中使用
例如由公式计算结果的函数,在A1中输入'=1+2+3,然后定义名称 RESULT = EVALUATE(Sheet1!$A1),最后你在B1中写入=RESULT,B1就会显示6了.
还有GET.CELL函数也只能在名称中使用,请参考相关资料.
8. 定义名称的妙处6 – 图片的自动更新连接
例如你想要在一周内每天有不同的图片出现在你的文档中,具体做法是:
8.1 找7张图片分别放在SHEET1 A1至A7单元格中,调整单元格和图片大小,使之恰好合适
8.2 定义名称MYPIC = OFFSET(SHEET1!$A$1,WEEKDAY(TODAY(),1)-1,0,1,1)
8.3 控件工具箱 – 文字框,在编辑栏中将EMBED("Forms.TextBox.1","")改成MYPIC就大功告成了.
这里如果不使用名称,应该是不行的.
此外,名称和其他,例如数据有效性的联合使用,会有更多意想不到的结果.
28,
29,第一列每个单元格的开头都包括4个空格,如何才能快速删除呢
查找替换最方便
30,如何快速地将表格中的所有空格用0填充 其中空格的分布无规律!
选中数据所在区域 》定位》空值》输入0》ctrl+enter
31,我在1行～10行中间有5个隐藏的行,现在选择1行～10行-复制,然后到另一张表格,右键单击一单元格,粘贴,那5个隐藏的行也出现了,请问怎样不让这5个隐藏的行出现呢
答:Ctrl+*
工具,自定义_编辑_选定可见单元格.
32,在某个单元格中(如A1)输入一个四则运算表达式"3*(2+5)+6/3",要求函数格式 MyFun(A1)返回计算结果
Function MyFun(X As Range)
MyFun = Evaluate(X.Formula)
End Function
33,MATCH
返回在指定方式下与指定数值匹配的数组(数组:用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式.数组区域共用一个公式;数组常量是用作参数的一组常量.)中元素的相应位置.如果需要找出匹配元素的位置而不是匹配元素本身,则应该使用 MATCH 函数而不是 LOOKUP 函数.
语法

34,显示比例小于40%即出现了"名称"
35,如何求出一个人到某指定日期的周岁
=DATEDIF(起始日期,结束日期,"Y")
36,假如A栏里有任一单元格有"$"字符串,则等于1,否则等于0 公式如何写
=IF(COUNTIF(A:A,"*$*")>0,1,0)
=(countif((A:A,*$*)>0)+0
37,如何限制单元数值上限(有公式的情况下)
规定上限数值后,公式中任何数值改变都不会让此数值变化更大,超过限制数值
=Min(公式,10),不超过10
38,在A1单元格中输入AA, B1中要得到第几列.
=COLUMN(INDIRECT(A1&"1"))
39,"++"以及"--"分别代表什么意思
"++" = "=+"
"--" = "="
40,【选择性粘贴】里的【跳过空单元】到底有什么用啊
跳过空格是指: 跳过剪切板上的空格,只复制,并保持他们的位置,粘贴到其他地方. 而不是指被粘贴的单元格.
这个功能非常有用,可以防止空白单元格替换原来的数据
40,DATEDIF计算两个日期之间的天数,月数或年数.提供此函数是为了与 Lotus 1-2-3 兼容.
语法
DATEDIF(start_date,end_date,unit)
Start_date 为一个日期,它代表时间段内的第一个日期或起始日期.日期有多种输入方法:带引号的文本串(例如 "2001/1/30"),系列数(例如,如果使用 1900 日期系统则 36921 代表 2001 年 1 月 30 日)或其他公式或函数的结果(例如,DATEVALUE("2001/1/30")).有关日期系列数的详细信息,请参阅 NOW.
End_date 为一个日期,它代表时间段内的最后一个日期或结束日期.
Unit 为所需信息的返回类型.
Unit返回"Y"时间段中的整年数."M"时间段中的整月数."D"时间段中的天数."MD"start_date 与 end_date 日期中天数的差.忽略日期中的月和年."YM"start_date 与 end_date 日期中月数的差.忽略日期中的日和年."YD"start_date 与 end_date 日期中天数的差.忽略日期中的年.
说明
Microsoft Excel 按顺序的系列数保存日期,这样就可以对其进行计算.如果工作簿使用 1900 日期系统,则 Excel 会将 1900 年 1 月 1 日保存为系列数 1.而如果工作簿使用 1904 日期系统,则 Excel 会将 1904 年 1 月 1 日保存为系列数 0,(而将 1904 年 1 月 2 日保存为系列数 1).例如,在 1900 日期系统中 Excel 将 1998 年 1 月 1 日保存为系列数 35796,因为该日期距离 1900 年 1 月 1 日为 35795 天.请查阅 Microsoft Excel 如何存储日期和时间.
Excel for Windows 和 Excel for Macintosh 使用不同的默认日期系统.有关详细信息,请参阅 NOW. 示例
DATEDIF("2001/1/1","2003/1/1","Y") 等于 2,即时间段中有两个整年.
DATEDIF("2001/6/1","2002/8/15","D") 等于 440,即在 2001 年 6 月 1 日和 2002 年 8 月 15 日之间有 440 天.
DATEDIF("2001/6/1","2002/8/15","YD") 等于 75,即在 6 月 1 日与 8 月 15 日之间有 75 天,忽略日期中的年.
DATEDIF("2001/6/1","2002/8/15","MD") 等于 14,即开始日期 1 和结束日期 15 之间的差,忽略日期中的年和月.
41,如何在EXCEL表格的一个单元格中设定校验.比如,单元格均为手工输入,某单元格必须等于其他几个单元格之和,如不等,则该单元格显示为红色.
用条件格式公式=D1SUM($A1:$C1)
42,A1]=abcabca
求a在单元格[A1]内出现次数
=LEN(A1)-LEN(SUBSTITUTE(A1, "a", ""))
43,20040404如何转为2004-4-4
=text(a1,"0000-00-00")
TEXT(Z12," - - ")
=LEFT("20040404",4)&SUBSTITUTE(RIGHT("20040404",4),0,"-")
使用分列最好
44,假如A2单元格有内容,就在E2单元格里面填上今天的日期."2005-5-30",如果明天,就填明天的日期.2005-5-31,但昨天的日期."2005-5-30",不改变.
建议使用VBA,用循环引用也可以:
工具—选项—重新计算—迭代计算—最多迭代次数1
=if(a2="","",if(e2="",now(),e2))
45,当你的领导或者同事要使用你的机器发现面目全非,自定义太多弄的自己都头大了,这时你想恢复EXcel的本色却急的满头大汗
这时最能解决这些问题的就是用户设置保存向导了.(要提前做)
在程序---OFFICE工具中可以找到它,他有保存本机设置和把设置应用到本机两个选项.多保存几个,方便在不同的个性菜单中切换
46,我现在要在一个22位的数值中,提取其中第10,11,12位的数字,不知道可有什么好的方法
=MID(A1,10,3)
我想计算总米数=卷长*卷数, 但由于原数据库中卷长带有单位(如:1000m), 可不可以不需去掉单位直接用函数得出总米数
=substitute(a2,"m",)*b2
47,在某格中输入一串数字,如何使它能象输入密码一样显示******呢
如果需要输入负数,文本时,也出现******,则自定义格式为:
**;**;**;**
48,用函数得出带完整路径的文件名
=CELL("filename")
49,"定义名称"的方法解除嵌套函数的限制
EXCEL中一个众所周知的限制是你不能嵌套超过7层函数.例如下面的公式是错误的,因为限制被超过.
=IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33,
IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=4,44,
IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=6,66,IF($A$4=7,77,FALSE))))))))
通常的方法,如果你有一个公式超过这个限制,你会考虑用VBA代替.然而,如果你不想使用VBA,你可以通过对公式的一部分"定义名称"来解决这种限制.
我们利用一个IF嵌套公式来测试
IF A4 = 1 Then 11
Else If A4 = 2 Then 22
Else If A4 = 3 Then 33
Else If A4 = 4 Then 44
...
Else If A4 = 13 Then 130 Else "Not Found"
当然在实践应用中,我们最好利用VLOOKUP这个函数去实现结果,但这里我们的目的是做个演示.首先,我们定义一个名叫"ONE TO SIX"的名称, 里面包括公式:
=IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33,
IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=4,44,
IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=6,66,FALSE))))))))
接着,再定义另一个名叫"SEVERTOTHIRTEEN"的名称,里面包括公式:
=IF(Sheet1!$A$4=7,77,IF(Sheet1!$A$4=8,88,IF(Sheet1!$A$4=9,99,
IF(Sheet1!$A$4=10,100,IF(Sheet1!$A$4=11,110,IF(Sheet1!$A$4=12,120,
IF(Sheet1!$A$4=13,130,"NotFound")))))))
最后,在B4单元格中输入下面的公式:
=IF(OneToSix,OneToSix,SevenToThirteen)
这样就解决了被嵌套函数的限制.因为公式中没有一个单独的部分超过限制,即使是"各个组成部分的集合"也没有超过限制.
当你编制一个超过限制的嵌套函数时可以使用这种方法.————————zhdi
50,A1:A10数字显为文本格式时,如何求和
=SUMPRODUCT(A1:A10+0)
51,工作表名设为变量
=INDIRECT("月份!"&ADDRESS(ROW(),COLUMN()))
此公式放在A1单元格,则是对1月A1单元格的引用,如1月A1单元格为10,则返回10.
=INDIRECT("月份"&E$1&"!"&ADDRESS(ROW(),COLUMN()))
此公式将工作表名设为变量,变量为当前表的E1单元格的值,如E1为2,则引用月份2表中相对应的值.
52,如何把"2005年5月1日"转换成"20050501"
=YEAR(A1)&TEXT(MONTH(A1),"00")&TEXT(DAY(A1),"00" )
YEAR(A1)&IF(MONTH(A1)<10,"0"&MONTH(A1),MONTH(A1))&IF(DAY(DAY(A1)发送->邮件收件人->以附件形式发送收件人一栏请填写:邮箱地址,,点击发送可以了.
或新建邮件,收件人一栏请填写: 邮箱地址,然后用鼠标把那个 excel 文件拖到我的名字下面就可以点击发送了.
60,求非空单元格数量
公式计算出来的数据,COUNTA不能用的(否则空字符也计算进去了)
=COUNTIF($E$3:$E$65536," *")
强制计数至少1个字符的单元格数.
61,(精) 动态求和公式,自A列A1单元格到当前行前面一行的单元格求和.
=SUM(INDIRECT("A1:A"&ROW()-1))
62,在使用SUM函数在单元格中输入超过30个参数时,你将得到一个"参数太多"的错误提示.
解决办法:在引用参数的两边多加一个括号,这时,SUM把括号内的最多可达254个参数当成一个处理.(主要是受公式长度限制,理论上可以达到无数个)
63如果您觉得公式复杂了,或是不直观
偶给你提供两个捷径:
方法一:选择公式单元格-〉菜单栏-〉[工具]-〉[公式审核]-〉[公式求值]
然后按照导向提示,一步步地观察公式求值的过程.(2003版适用)
方法二:选择公式单元格-〉选取编辑栏或F2-〉用鼠标置亮公式中你想查
看的那段-〉按F9,会显示该段的计算结果.
64请问有没有返回工作表名称的函数
插入—名称—定义:
sh=replace(get.document(1),1,find("]",get.document(1)),)&t(now())
单元格输入=sh
GET.DOCUMENT是宏表函数,当数据变动时无法自动计算,now()是易失性函数,任何变动都会强制计算,宏表函数所以加上now()就可以自动重算了,T()用来将now()产生的数值转化为空文本.
65,显示当前工作表的表名
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
66,我要自动生成数据在K29+010~K29+600之间的数据(显示为K29+XXX)
=TEXT(INT(RAND()*591)+10,"K29+000")
67,我想把列P1~P60的数据引用到行U1~CB1,本来想用公式=$P1拉动复制,结果不行.
U1=INDIRECT("P"&COLUMN()-20),向右拉
68,
姓名
外语
数学
王
86
李
95
张
80
姓名
外语
数学
李
95
90
张
80
80
王
86
75
将表1的姓名导入自定义序列,在表2按此自定义序列排序
或使用vlookup公式
表1的C2
=vlookup(a2,表2!a:c,3,0)
如何将2个排列顺序不同的表统一一下
69,如果你要在A3的前面插入100行,可以这样:
在名称框输入 3:103----回车-----ctrl+shift+"+"(大键盘)
70,VBA从哪学起
1,录制宏;
2,数据类型(主是整型和字符串型);
3,程序结构(主要是分支,判断,循环);
4,EXCEL对象(单元格,工作表,工作薄)
71,如何实现求平均值时只对不等于零的数求均值
average(if(a1:a5>0,a1:a5))
72,D1输入姓名,A列中相同的姓名按设定和条件格式显示
=find(D1,$A$1)
73,excel中插入/名称/指定(应用)(标志)的用途是什么
1,指定的用法
例如:在A1:A3中输入了AAA,BBB,CCC
选中A1:D3》插入》名称》指定》最左列》确定
这样就一次性定义了3个名称
AAA=B1:D1
BBB=B2:D2
CCC=B3:C3
2,应用的用法
在A4输入 =sum(B1:D1,B3:D3)
然后 插入》名称》应用
会将公式中的引用用相应的名称替换.
74,请教如何筛选出特殊颜色的数据.
定义名称.X=GET.CELL(24,单元格),辅助列=X
按辅助列筛选
75,如何快速查看名称
插入---名称---粘贴---名称清单(粘贴列表)
76,COUNTIF函数的16种公式设置
1,返加包含值12的单元格数量
=COUNTIF(DATA,12)
2,返回包含负值的单元格数量
=COUNTIF(DATA,"<0")
3,返回不等于0的单元格数量
=COUNTIF(DATA,"0")
4,返回大于5的单元格数量
=COUNTIF(DATA,">5")
5,返回等于单元格A1中内容的单元格数量
=COUNTIF(DATA,A1)
6,返回大于单元格A1中内容的单元格数量
=COUNTIF(DATA,">"&A1)
7,返回包含文本内容的单元格数量
=COUNTIF(DATA,"*")
8,返回包含三个字符内容的单元格数量
=COUNITF(DATA," ")
9,返回包含单词"GOOD"(不分大小写)内容的单元格数量
=COUNTIF(DATA,"GOOD")
10,返回在文本中任何位置包含单词"GOOD"字符内容的单元格数量
=COUNTIF(DATA,"*GOOD*")
11,返回包含以单词"AB"(不分大小写)开头内容的单元格数量
=COUNTIF(DATA,"AB*")
12,返回包含当前日期的单元格数量
=COUNTIF(DATA,TODAY())
13,返回大于平均值的单元格数量
=COUNTIF(DATA,">"&AVERAGE(DATA))
14,返回平均值上面超过三个标准误差的值的单元格数量
=COUNTIF(DATA,">"&AVERAGE(DATA)+STDEV(DATA)*3)
15,返回包含值为或-3的单元格数量
=COUNTIF(DATA,3)+COUNIF(DATA,-3)
16,返回包含值;逻辑值为TRUE的单元格数量
=COUNTIF(DATA,TRUE)
77,日期分列
如有一列格式如2005-7-8的数据,要将其分为2005 8 7,方法为:数据_分列,-号作分隔符,列数据格式-文本,再将年份转为数值.
78,查询时查不到数据的原因
可能是格式不一致.
79,120,000显示为12.0
自定义格式:
#!.0,
80,121,999显示为12.2
#!.#,
81,大写的零,就是那个大的圈圈如何输入
1,在特殊格式中,中文小写是可以显示"○"的.
2, ABC和紫光拼音输入法下,输入字母i(小写)和0就会显示大写的"○",入如果是输入年份也可以用字母o代替(大写).
3,如果用 智能ABC 输入法,按V,再按1,再按=,选择.
82,计算两个日期之间的天数
=DATEDIF(起始日期,结束日期,"D")
http://club.excelhome.net/dispbbs.asp boardID=1&ID=86150&page=1
83,对于一些不可打印的字符(在Excel显示中类似空格),直接用替换方法不容易去掉.
可以这么做:
=SUBSTITUTE(CLEAN(A1)," ","")
84,123
0123
75223
比如说上述的数字,我想在它们前面加上38910104,而且位置短的数字,会自动补0
变成如下:
3891010400123
3891010400123
3891010475223)
=TEXT(A1,"3891010400000")
85,VLOOKUP查找函数,不如用INDEX和MATCH函数查找,特别对于大型工作表,VLOOKUP函数的公式看起来短一些,但是由于它是查找函数,每执行1次,它就要搜索它的第2个参数区域,速度慢.
而用INDEX和MATCH函数速度明显比VLOOKUP快.
86,如果在Sheet1中A 列的单元格中数据的部分字符包含在Sheet2的A列中,则提取Sheet2对应的B列数据到Sheet1的B列中.否则显示空白.
=IF(COUNTIF(Sheet2!A:A,"*"&A1&"*"),VLOOKUP("*"&A1&"*",Sheet2!A:B,2,0),"")
87,find函数
=if(iserr(find("某某",A1)),"没找到","找到")
88,我有一列数据,比如A1:A100,现在我想这样计算,我想在B1单元格求和,但是要是变量,比如我在C1单元格中输入数据5,那么B1求出的结果就是A1:A5,如果我在C1单元格中输入数据10,那么B1求出的结果就是A1:A10, 请问怎么实现求和的范围随C1变化
B1=SUM(INDIRECT("A1:A"&C1))
89,图片批注
右击带批注的单元格》编辑批注》在批注边框上右击》设置批注格式》颜色与线条》单击"颜色"》填充效果》图片》选择图片.
你了解工作簿和工作表限制吗
很多人认为,一个工作簿文件最大可存在256个工作表,其实,这是错误的,请看:
打开的工作簿个数 受可用内存和系统资源的限制
工作表大小 65,536 行乘以 256 列
列宽 255 个字符
行高 409 磅
分页符个数 水平方向 1000 个;垂直方向 1000 个
单元格内容(文本)的长度 32,767 个字符.单元格中只能显示 1,024 个字符;而编辑栏中可以显示全部 32,767 个字符.
工作簿中的工作表个数 受 可用内存的限制(默认为 3 个工作表)
工作簿中的颜色数 56
工作簿中的单元格样式种类 4,000
工作簿中命名视图个数 受可用内存限制
自定义数字格式种类 受可用内存限制
工作簿中的名称个数 受可用内存限制
工作簿中的窗口个数 受系统资源限制
窗口中的窗格个数 4
链接的工作表个数 受可用内存限制
方案个数 受可用内存的限制;汇总报表只显示前 251 个方案
方案中的可变单元格个数 32
规划求解中的可调单元格个数 200
自定义函数个数 受可用内存限制
缩放范围 10% 到 400%
报表个数 受可用内存限制
排序引用的个数 单个排序中为 3,如果使用连续排序则没有限制
撤消层次 16
数据窗体中的字段个数 32
工作簿中的自定义工具栏个数 受可用内存限制
自定义工具栏按钮个数 受可用内存限制
1, 号
说明:表示要查找的内容与问号所在的位置相同的单一字符
示例:fyu 可查找fyu1,fyuv等,此列中的1和v均用 代替.
2,*号
说明:表示要查找的内容与问号所在的位置相同的多个字符
示例:fyu*可查找fyu123,fyuv55u等,此列中的123和v55u均用*代替.
3,~波形符号
说明:查找一个问号,星号或波形符号,可在~后跟 ,*或~
示例:fyu~ 可查找fyu
学会了通配符查找,在很多时候是很省力的,不妨试一试.
在单元格中同时显示公式与结果的方法
1,工具_选项_窗口选项_勾选公式选项
2,在A列显示公式,B列显示结果,操作方法如下:
在B2单元格输入=1+2*3,此时将显示7,为计算结果
选择A2单元格,插入_名称_定义,输入名称如mFormula,引用位置处输入=Get.Cell(6,Sheet1!B2),
此后可在A列中要显示公式的单元格输入=mFormula,可取得B列相对应单元格的公式.
3,在A列中输入了公式,要在B列显示运算结果,操作方法如下:
选择B2单元格,插入_名称_定义,输入名称如Result引用位置处输入=Evaluate(Sheet1!A2),表示计算左边单元格的公式,
此后即可在B列中要计算结果的单元格中输入=Result,或从B2用填充句柄复制,即可取得从A列相对应单元格的公式.
说明:Evaluate()是Excel 4.0版的宏表函数,excel 2000以上版本还支持,但只可用于名称定义中;Get.Cell用于获取单元格或单元格区域的属性,格式为:Get.Cell(类型值,引用区域).
Excel使用技巧集
[转贴]自369信合网
2.1 输入编辑技巧(1)
2.1.1 从Word表格文本中引入数据
请问可以将Word表格的文本内容引入到Excel工作表中吗 具体如何实现
答:可以通过"选择性粘贴"命令来实现.具体操作如下:先利用"复制"命令将Word表格文本内容添加到系统剪贴板中,然后在Excel工作表中定位到对应位置.单击"编辑"菜单中的"选择性粘贴"命令,再选择"方式"下的"文本"项,最后单击"确定"按钮即可.
2.1.2 快速实现整块数据的移动
在工作中常常需要移动单元格中的数据,请问有比"粘贴"操作更快捷的方法吗
答:可以直接采用拖动的方法.操作过程如下:首先选择要移动的数据(注意必须是连续的区域).然后移动鼠标到边框处,当鼠标指针变成一个四个箭头标志时,按住"Shift"键的同时按下鼠标左键,拖动鼠标至要移动的目的区域(可以从鼠标指针右下方的黄色提示框中获知是否到达目标位置),放开鼠标左键即完成移动.
2.1.3 固定数据输入时的焦点
在数据输入时,每当我们按下"Enter"键后,光标就会自动跳转到下一个单元格中.因此在进行数据的反复验证中,就必须反复地来重新定位焦点.请问有什么方法来固定数据输入的焦点吗
答:要实现上述要求并不难.输入数据前,在按住"Ctrl"键的同时单击选择该单元格.当单元格边框的边很细的时候,再开始数据的输入.这样不管按下多少次"Enter"键,光标始终都不会移动到其他单元格.如果要移动到下一单元格,可以使用方向键来完成.
当然如果当前工作表中要实行数据验证的单元格很多时,每次都按上述方法来操作未免有些麻烦.那么可以试试以下方法来实现一劳永逸.单击"工具"菜单中的"选项"命令,在弹出的设置框中单击"编辑"选项卡,然后取消选择"按Enter键后移动"复选框,最后单击"确定"按钮即可.
2.1.4 实现以"0"开头的数字输入
在Excel单元格中,输入一个以"0"开头的数据后,往往在显示时会自动把"0"消除掉.请问有什么办法来让"0"不被消掉吗
答:要保留数字开头的"0",其实是非常简单的.只要在输入数据前先输入一个"' "(单引号),这样跟在后面的以"0"开头的数字的"0"就不会被系统自动消除.
2.1.5 将整列数据转换为矩阵形式
请问如何将工作表中的整列数据转换成用矩阵来表示
答:要实现一列数据转化成一个矩阵,必须保证该列数据的个数L要等于该矩阵行数M和列数N的乘积,即L=M×N.然后按如下操作:选择该列的前N个数据,将其"复制"到剪贴板.再单击矩阵第一行的第一个单元格,然后从"编辑"菜单中执行"选择性粘贴"命令,在弹出的对话框中选择"转置"复选框,最后再单击"确定"按钮即把前N数据转换成了矩阵的第一行.接下来再选择该列中的下一组N个数据,执行"复制".再将光标定位到矩阵的第二行的第一个单元格,执行"转置"操作.这样经过M次的"转置"后就把数据全部转换为矩阵形式了.
2.1.6 利用命令按钮快速实现合并
对行或者列进行合并操作是编辑工作表时经常会遇到的,可每次都要执行好几个步骤才能完成,请问有没有更快捷的办法
答:其实系统为我们提供了合并单元格的工具按钮,只是它不是默认的工具栏按钮而已,可以按以下操作自行添加:单击"工具"菜单栏中的"自定义"命令,再选择"命令"选项卡,在左边的"类别"列表中选择"格式"后,在右边的"命令"选择列表中就可以找到"合并单元格","跨越合并"以及"撤销合并单元格"的命令按钮.
直接用鼠标将它们拖到工具栏上的合适位置即可.这样下次要执行合并操作时只要按对应的命令按钮就可以快速完成了.
2.1.7 让单元格数据斜向排版
有些工作表文件中的单元格数据是斜方向显示的,请问这个效果该如何实现
答:要实现这个效果并不难,因为这是系统自身提供的一种排版模式.只需要找到相应的命令按钮.方法如下:单击"工具"菜单中的"自定义"命令,在"命令"选项卡的"类别"列表中选择"格式"项,在"命令"列表中找到"顺时针斜排"和"逆时针斜排"两个命令按钮,将它们拖到工具栏的合适位置.这样只要选择需要斜排的单元格,然后执行命令按钮就可以实现数据的斜向排版.
2.1.8 自动检测输入数据的合法性
由于特殊需要,我们对单元格的数据输入要求必须按统一格式来定义.请问可以在用户输入数据时自动检测其一致性吗
答:只要为需要的单元格或工作表设置数据有效性检查即可.先选择需要进行该设置的单元格,然后在"数据"菜单中找到"有效性"命令,单击执行它.在弹出的对话框中选择"设置"选项卡.再从"允许"下拉列表框中选择要设置的类型,如"整数","小数","日期"等.如果对数据输入的大小或范围有要求的话,还可以对"数据"项进行设置.
当然还可以在"出错警告"和"输入法模式"选项卡中为单元格设置提示的类型和默认的输入法等.最后单击"确定"按钮.这样设置后,当用户输入的数据不符合你的设置要求时,系统会自动弹出一个"输入值非法"的提示对话框来提醒用户.
2.1.9 通过剪贴板为单元格添加数据
为什么利用"复制"和"粘贴"命令来为单元格中添加信息时,在菜单中的"粘贴"命令项有时是灰色的,没办法操作 请问该怎么办
答:想通过"粘贴"对单元格内容进行添加数据是无法实现的.但是可以利用"剪贴板"来实现.首先要通过"编辑"菜单中的"Office剪贴板"命令,调出剪贴板查看程序,然后将要添加的数据"复制"到剪贴板上,这时可以在右边的剪贴板上看到该数据.接着将光标定位到要添加的单元格中,再单击剪贴板中刚才复制好的数据条目,数据就会自动的添加到单元格中了.
2.1.10 直接公式输入法
在输入公式或函数时,每次都要在前面带个"="号,实在麻烦.请问可以直接输入公式吗
答:当然可以,只要稍微设置一下就可以实现了.具体步骤如下:先单击"工具"菜单中的"选项"命令.再在弹出的选项对话框中选择"1-2-3的帮助"选项卡,选择"转换Lotus 1-2-3公式"复选框.
最后单击"确定"按钮即完成设置.不过需要注意的是,这样设置后必须保证输入的公式全部都是数字,不能包含有引用单元格的其他符号.否则必须加上"="号.
2.1.11 让文本输入自动适应单元格长度
在Excel单元格中输入文字时,即使输入的文字长度超过单元格长度,系统也不会自动换行,而只是显示为一行.请问可以让系统自动根据单元格长度来调整文本的行数吗
答:其实这就是文本的自动换行问题,要实现起来是很容易的.
在输入文本时,当到达单元格最右边时,同时按下"Alt+Enter"组合键,这样系统会自动加宽单元格的宽度,而文本也会自动换到下一行.如果觉得每次按组合键太麻烦,可以执行如下设置来一劳永逸:首先单击工作表最左上角的行,列相交的空白处,选择工作表中的所有单元格(当然也可以按下"Ctrl+A"组合键来选择).然后单击"格式"菜单中的"单元格"命令,再在弹出的"单元格格式"对话框中选择"对齐"选项卡,选择"自动换行"复选框.最后单击"确定"按钮即可完成.如果只要实现部分单元格的自动换行,可以先选定这些单元格,再执行上述操作就可以了.
2.1.12 利用"填充柄"快速输入相同数据
在编辑工作表时,有时整行或整列需要输入的数据都一样,很显然如果一个一个单元格的输入实在太麻烦了.请问有什么快捷的方法吗
答:可以利用鼠标拖动"填充柄"来实现快速输入.具体操作如下:首先在第一个单元格中输入需要的数据,然后单击选中该单元格,再移动鼠标指针至该单元格右下角的填充柄处,当指针变为小的黑色"+"字型时,按住左键,同时根据需要按行或者列方向拖动鼠标,选中所有要输入相同数据的行或者列的单元格,最后松开鼠标即可.这样数据就会自动复制到刚才选中的所有单元格了.不过上述方法只适用于输入的是文本信息.如果要重复填充时间或日期数据时,使用上述方法填充的将会是一个按升序方式产生的数据序列.这时可以先按住"Ctrl"键,然后再拖动填充柄,填充的数据才不会改变.
2.1.13 利用组合键在单元格中快速定位
在修改工作表中的单元格数据时,如果要在单元格中增加数据的话,每次都要先用鼠标定位,然后再用键盘输入,这样来回地切换很不方便.请问有什么键盘组合键吗
答:当然有,可以用键盘的方向键来选择单元格,然后按下"F2"键.这样单元格就会自动转换输入模式,同时光标也自动定位在该单元格的最后面,直接输入数据就可以了.
2.1.14 在信息输入前就给予提示
希望在用户为单元格输入信息时,系统能自动的给予一些必要的提示,这样不但可以减少信息输入的错误,还可以减少修改所花费的时间.请问该如何实现
答:可以按如下操作:首先选择需要给予输入提示信息的所有单元格.然后执行"数据"菜单中的"有效性"命令,在弹出的对话框中选择"输入信息"选项卡.接着在"标题"和"输入信息"文本框中输入提示信息的标题和内容即可.
2.1.15 正确显示百分数
在单元格中输入一个百分数(如10%),按下回车键后显示的却是0.1.请问该如何设置才能正确显示百分数
答:出现这种情况的原因是因为所输入单元格的数据被强制定义成数值类型了,只要更改其类型为"常规"或"百分数"即可.操作如下:选择该单元格,然后单击"格式"菜单中的"单元格"命令,在弹出的对话框中选择"数字"选项卡,再在"分类"栏中把其类型改为上述类型中的一种即可.
2.1.16 巧做设置,使公式更加明了
在工作表的公式中,我们经常是通过单元格标号来引用数据的,如 "F2=A2+B2+C2+D2".这样很难一下子了解公式具体的内容.有没有方法用文字来取代单元格的符号,让公式更直观些呢
答:这个想法的确很不错,当然也是可以实现的.可以按下列步骤来操作:例如在一个特定的数据库工作表中,一般每一列数据都是同一个字段下的.可以先为每一列数据设置好相应字段名(如:语文,数学,外语,总分等).再单击"工具"菜单中的"选项"命令,选择"重新计算"选项卡.在"工作簿选项"区域中选择"接受公式标志"复选框.
最后单击"确定"按钮.这样如果要利用公式计算时,可以直接在公式中输入字段名称来表示了,例如:总分=语文+数学+外语.
2.1.17 在连续单元格中自动输入
等比数据序列
请问有什么好的方法来输入一个较大的等比序列吗
答:可以通过填充的方法来实现.具体操作如下:首先在第一个单元格中输入该序列的起始值,然后通过拖动操作选择要填充的所有单元格.再依次单击"编辑"菜单中的"填充"项下的"序列"命令.在弹出的"序列"对话框中,再选择"类型"中"等比序列"单选按钮,再在"步长"文本框中输入等比序列的比值.最后还要在"终止值"文本框中输入一个数字.不一定要是该序列的最后一个值,只要是一个比最后一个数大的数字就可以了.最后单击"确定"按钮即可.这样系统就自动将序列按照要求填充完毕.
2.1.18 选择大范围的单元格区域
由于屏幕大小的局限性,如果利用鼠标拖动操作,总是无法一次性准确地选择大于屏幕显示范围的单元格区域.请问有什么别的好方法吗
答:当然有.可以试试如下操作:首先在"名称"框(就是公式输入栏fx左边的输入框)中输入该操作区域的起始单元格名称代号,然后输入该操作区域的最后一个单元格名称代号,中间用":"冒号分开.最后按下"Enter"键,这样以这两个单元格为对角的长方形区域就会被快速的选定.
2.1.19 为修改后的工作表添加批注
想在对工作表文档进行修改的同时,在修改处添加批注,以方便日后查阅.请问该如何操作
答:为修改添加批注是很容易实现的.具体操作如下:首先选择已经修改过的单元格,然后单击"插入"菜单中的"插入批注"命令.这时在该单元格旁边会弹出一个黄色的文本输入框,输入框最上边会自动显示系统安装时使用的用户名字,当然也可以改为当前使用者的名字.在光标处就可以输入要添加的批注了.输入完成后鼠标单击任意位置,批注文本框就会自动隐藏起来.这时在该单元格的右上角部位会多出一个红色的小三角形符号,当鼠标移动至该单元格上时,会自动弹出刚才添加的批注内容.同时还可以用鼠标右键单击该单元格,在弹出的菜单中通过"编辑批注"和"清除批注"命令来进行其他的修改操作.
2.1.20 隐藏行或列的其他方法
请问要实现行或者列的隐藏有什么其他好的方法吗
答:要实现行或者列的隐藏除了利用"隐藏"命令外,还有以下几种方法可以实现.其中最简单的就是直接拖动鼠标来实现.先移动鼠标指针至要隐藏的行或者列的最下边或最右边,当鼠标指针变成了一个黑色的带有上下箭头的"+"字状时.按下鼠标左键,然后拖动鼠标使其不可以见即可.当然也可以通过键盘组合键来操作完成:按下组合键"Ctrl+9"隐藏的是行;按下组合键"Ctrl+0"隐藏列.如果要取消隐藏,只需要再同时按下"Shift"键,即同时按下"Ctrl+Shift+9","Ctrl+Shift+0"组合键.
当然如果要隐藏的行或者列比较多时,用这上述两种方法还是显得有点麻烦,这时可以试试利用"Shift+Alt+→(右方向键)"组合键来隐藏.首先利用"Ctrl"键来选择所有要隐藏的行或者列,然后同时按下"Shift+Alt+→(右方向键)"组合键,这时会弹出"创建组"对话框,询问要创建的组是行还是列.如何隐藏的是行就选择"行"单选按钮,如果隐藏的是列就选择"列"单选按钮.最后单击"确定"按钮完成.这时在工作表中行或者列的序号处会产生一条黑线,它将刚才选择的所有行或列连接起来,同时在末端会有一个"-"符号.单击该符号,则所选的行或者列就会组成一个组,从而达到隐藏的效果.同时"-"号会变成"+"号.要恢复隐藏,只要再单击该"+"号即可.或者按下数字"1"或"2"也可以实现.如果要取消分组模式.只需同时按下"Shift+Alt+←(左方向键)"组合即可.
2.1.21 恢复被隐藏的行或列的快捷方式
请问除了使用"取消隐藏"命令外,还有什么更快捷的方法来实现找出已经被隐藏的行或列吗
答:要取消隐藏的行其实有更简单的方法.例如要恢复被隐藏的第2行,可以执行如下操作:首先将鼠标指针移动到第1行和第3行之间的位置,当鼠标的指针变成横向为"="号,纵向为黑色实心双箭头的十字形状时,执行用鼠标左键双击操作,这样被隐藏的行就会显示出来了.这种方法比用"取消隐藏"命令要快捷的多.取消列的隐藏方法同上所述.
2.1.22 在常规格式下输入分数
当在工作表的单元格中输入如"2/5,6/7"等形式的分数时.系统都会自动将其转换为日期格式.所以每次都要先将单元格格式设置为"分数"后才可以正确显示.难道就没有办法可以在常规格式下直接输入分数吗
答:在"常规"模式实现分数的输入是可以实现的,而且还是非常简单的.只要在输入分数前,先输入"0+空格符".然后再在后面输入分数即可,如输入"0□2/3"(□表示空格)即正确显示为"2/3".需要注意的是,利用此方法输入的分数的分母不能超过99,否则输入结果显示将被替换为分母小于或等于99的分数.如输入"2/101",系统会将其转换为近似值"1/50".
2.1.23 在单元格中自动输入时间和日期
日期和时间的输入形式太麻烦,而且有时记不清楚.请问有什么方法让系统自动输入时间和日期吗
答:可以执行以下方法来实现:选中需输入的单元格,直接按下"Ctrl+;"组合键可输入当前日期;如果直接按下"Ctrl+Shift+;"组合键即可输入当前时间;当然也可以在单元格中先输入其他文字然后再按以上组合键,如先输入"当前时间为:"再按下"Ctrl+ Shift+;"组合键,就会在单元格中显示"当前时间为:16:27"这样的结果.
以上方法美中不足的是输入的时间和日期是固定不变的.如果希望日期,时间随当前系统的日期,时间自动更新,则可以利用函数来实现.输入"=today()"得到当前系统日期的函数,输入"=now()"得到当前系统时间和日期的函数.
2.1.24 移动单元格中公式计算的结果
一般地,可以通过在单元格中输入公式自动计算出数据的结果.但是将这个计算结果移动到其他单元格时,结果都变成了"0",这是怎么回事 该如何解决
答:可以通过以下操作来解决:单击"工具"菜单中的"选项"命令,再选择"重新计算"选项卡.选择"手动重算"复选框,同时取消选择"保存前自动重算"复选框,使其无效,最后单击"确定"按钮即可.这样就可以通过"复制"和"粘贴"命令来正确移动公式计算出的结果了.
2.1.25 以图片格式插入Word中的文本内容
请问在不用截图软件的情况下,可以直接将Word文档中的某些文本内容以图片格式插入工作表的单元格中吗
答:当然可以,可以直接利用系统的内置功能实现文本内容到图片格式的转换.具体操作如下:在Word编辑界面,先选定需要插入的文字内容,然后利用"复制"命令将其复制到系统剪贴板中.然后在Excel环境下,选定要插入该图片的单元格,再按住"Shift"键,同时单击"编辑"菜单中的"粘贴图片"命令.这样即可将剪贴板中的内容转换成图片格式并插入到工作表中.如果选择的是"粘贴链接图片"命令,则将图片和工作表建立链接关系.
2.1.26 求和函数的快捷输入法
求和函数"SUM"可能是我们在工作表文档中使用最多的函数了.请问有什么好办法来快速输入吗
答:其实"SUM"函数不必每次都直接输入的,可以单击工具栏中的"∑"符号按钮来快速输入.当然还有更快捷的键盘输入,先选择单元格,然后按下"Alt+="组合键即可.这样不但可以快速输入函数名称,同时还能智能地确认函数的参数.
2.1.27 快速创建任意大小的连续数据矩阵
要创建一个较大的数据矩阵,且矩阵中的数据是一个有规律的等差序列,请问有什么好的方法来自动实现吗
答:要创建这样的有规律的数据矩阵,可以试试以下所述的方法:假设要创建一个M×N的矩阵,而且矩阵的第一个单元格是A1.先在A1单元格中输入矩阵的第一个数据元素,再在A2单元格中输入下一个数据元素.然后再定位到B1单元格,在其中输入矩阵的第N+1个数据元素,在B2单元格中输入第N+2个数据元素,这样也就确定了该矩阵的前两行和前两列的四个数据.接下来用鼠标选择这四个单元格,移动鼠标指针至该单元格的右下角填充柄处,当指针变成一个实心的黑色小十字状时,按下鼠标左键延行方向拖动至矩阵的最后一列.松开鼠标后,会生成一个2行N列的数据矩阵.同样的再选择该矩阵,移动指针到该矩阵的右下角填充柄处,当鼠标再次变成一个实心的黑色十字状时,延列方向执行拖动操作至第M行.这样整个矩阵就自动填充完了.
2.1.28 组合键方式为单元格文字
添加双下划线
系统提供的下划线按钮只能为单元格文字设置下划线.而如果要设置双下划线,则要利用菜单命令才能完成,请问有更快捷的方法吗
答:当然有,设置双下划线可以直接利用键盘快捷完成.方法就是先按住"Shift"键,然后再单击工具栏上的下划线按钮.
2.1.29 为单元格文字添加汉语拼音
为什么利用"格式"菜单中"拼音信息"下的"编辑"命令为单元格汉字加注汉语拼音后,系统什么都没显示
答:那是因为系统默认的格式是将拼音隐藏的.只要再执行"拼音信息"下的"显示或隐藏"命令就可以看到了.或者可以先执行"显示或隐藏"命令,然后再利用"编辑"命令来输入拼音信息.同时还可以通过"设置"命令来对加注的拼音进行字体大小,对齐方式等相关的设置.
2.1.30 快速选中所有数据类型相同的单元格
要选择数据类型都是"数字"的单元格来进行操作,可是这些单元格都是分散的,请问有什么好的方法把它们全部找出来吗
答:可以利用"定位"命令来快速地找到这些单元格.具体操作如下:执行"编辑"菜单中的"定位"命令.在弹出的"定位"对话框中,单击"定位条件"按钮.接着会弹出一个"定位条件"对话框,根据需要,选择设置好要查找的单元格类型.例如先选择"常量"项,然后再复选上"数字"项,最后单击"确定"按钮完成即可.这样符合上述条件的单元格全部会被选中.
2.1.31 利用"记录单"为行或列添加数据
如果工作表文档中数据较多,由于屏幕大小的限制,在输入超过显示区域单元格的数据时,一般都是要利用滚动条来定位显示区域.这样操作往往不是很准确,请问有更好的方法来实现吗
答:可以利用"记录单"方式来直接输入数据,而无需理会单元格位置的变化.不过具体操作时,行和列的输入稍有不同,下面一一来说明:
对于列数据的输入.先单击列标题来选择整列,然后单击"数据"菜单中的"记录单"命令,这时系统会弹出一个对话框,直接单击"确定"按钮,在弹出的另一对话框中进行数据输入即可,输入完成后,按"Enter"键自动跳转至下一空白单元格中(当然也可以单击滚动条下边的黑色三角符号来跳转),继续输入数据.输入完成后单击"关闭"按钮完成即可.
如果是要按行方向来输入数据,操作与前面稍有不同.首先不能利用单击行标记的方法来选择整行,否则会因为字段太多而出错,所以只能用拖动操作来选择.在数据输入时,不能用"Enter"键来跳转到下一个空白单元格,因为如果按此键,系统会跳转到下一行来输入数据,但是可以用"Tab"键来跳转.
最后需要共同注意的是,不管是行输入或是列输入数据,由于系统默认是将第一行和第一列作为记录名称和字段名称,所以所有输入的数据都是从第二行和第二列开始的.
2.1.32 不输入公式直接查看结果
当要计算工作表中的数据时,一般都是利用公式或函数来得到结果.可是假如仅仅只是想查看一下结果,并不需要在单元格中建立记录数据.请问有什么办法实现吗
答:可以选择要计算结果的所有单元格,然后看看编辑窗口最下方的状态栏上,是不是自动显示了"求和= "的字样呢 如果还想查看其他的运算结果,只需移动鼠标指针到状态栏任意区域,然后用鼠标右键单击,在弹出的菜单中单击要进行相应的运算操作命令,在状态栏就会显示相应的计算结果.这些操作包括:均值,计数,计数值,求和等.
2.1.33 批量为单元格数据添加单位
在许多的场合中,我们都需要为单元格数据添加单位,如果每输入一个数据,就添加一个单位,这样操作起来实在太麻烦费事了.请问可以在数据输入后一次性批量完成吗
答:可以按以下操作来实现批量输入单位:选中所有要添加单位的单元格,然后单击"格式"菜单中的"单元格"命令,在弹出的对话框中选择"数字"选项卡,然后在"分类"列表中选择"自定义"项.再在右边的"类型"列表选择框中选择适合的数字格式(例如:如果是小数形式,则选择"0.00"),在"类型"文本框中的该数字格式后添加"单位名称"(如:"元",注意是英文输入法中的引号).
最后单击"确定"按钮返回即可.这样刚才选择的所有单元格数据后都自动加上设置好的单位了.
2.1.34 启用记忆功能输入单元格数据
在一些网页填写注册信息时,如果输入的内容以前曾经输入过,只要输入前面一个或几个字符,系统就会自动输入其余的内容.请问在Excel的单元格数据输入有这种功能吗
答:这种功能叫做自动记忆功能.在Excel中也是可以实现的.具体设置如下:单击"工具"菜单中的"选项"命令,在弹出的对话框中单击"编辑"选项卡,选择"记忆式键入"复选框.最后单击"确定"按钮即可.
2.1.35 利用"选择性粘贴"命令将文本
格式转化为数值
在通过导入操作得到的工作表数据中,许多数据格式都是文本格式的,无法利用函数或公式来直接进行运算.但是如果一个一个地改又很麻烦,请问有什么更好的办法来实现转换吗
答:对这种通过特殊途径得到的数据文档,可以通过以下方法来实现快速批量转换格式:先在该数据文档的空白单元格中输入一个数值型数据如"1",然后利用"复制"命令将其复制到剪贴板中.接着选择所有需要格式转换的单元格,再单击"编辑"菜单中的"选择性粘贴"命令,在弹出的"选择性粘贴"对话框中选择"运算"项下的"乘"或者"除"单选按钮.
最后单击"确定"按钮完成即可.这样,所有的单元格都会转换为数值格式了.
2.1.36 去除单元格中的"0"
有时在一个工作表中,有许多的"0"值的单元格,这些单元格没有什么实际意义,而且还影响整个工作表的美观,请问可以将这些"0"去掉,而又不影响工作表的数据的完整吗
答:要想去掉这些"0"值单元格,而又不影响工作表数据的完整性,就只有将它们隐藏起来.具体实现方法如下:单击"工具"菜单中的"选项"命令,在弹出的对话框中选择"视图"选项卡,在"窗口选项"区域中取消选择"零值"复选框,最后单击"确定"按钮完成即可.
当然这样工作表中所有的"0"都会被隐藏起来.如果只希望隐藏部分单元格中的"0",可以执行以下操作:首先选择要隐藏的单元格,然后单击"格式"菜单中的"单元格"命令.在弹出的对话框中选择"数字"选项卡,再在"分类"列表框中选择"自定义"项,在右边的"类型"输入框中输入"0;0;;@"(全部符号都在英文输入法状态输入,双引号不要输入).最后单击"确定"按钮完成即可.
2.1.37 在数据录入时自动切换输入法
由于工作表中数据类型的复杂和多变性,所以常常要在各种输入法之间来回切换,请问可以实现一定位到某个单元格系统就会自动切换到相应的输入法吗
答:可以通过下面的设置来实现它:首先选中使用同一种输入法的所有单元格,再单击执行"数据"菜单上的"有效性"命令,在弹出的对话框中选择"输入法模式"选项卡,在"输入法模式"下拉列表中,根据需要进行设置.如要使用中文字符的话,就选择"打开",如要使用英文输入模式,就选择"关闭(英文模式)".
2.1.38 消除运算结果的系统误差
在对一些带小数的数据进行统计中,系统计算后得出的结果往往和我们运算得到的值有一定的出入.为什么出现这种问题 该如何解决呢
答:出现这种问题的原因是,在默认情况下,系统对中间运算得出的带有小数结果的显示都是经过了四舍五入操作后给出的近似值.而在其他如求和,求差等运算过程中,使用的是更精确的实际值,所以和用屏幕显示值进行运算得出的结果是有出入的.要想解决这一问题,只要更改默认设置即可,具体操作如下:单击"工具"菜单中的"选项"命令,再选择"重新计算"选项卡,找到并取消选择"以显示值为准(P)"复选框,最后单击"确定"按钮完成.这样系统就会直接依据显示结果来进行运算了.
2.1.39 为数据输入设置下拉选择列表
为了统一输入的格式,有时希望用户在输入数据时,只需要选择设置好的单元格内容即可,而不要自行重新输入.有没有办法为单元格设置一个供选择的类似下拉列表的选择框
答:这是完全可以的.具体操作如下:首先要选择需要建立自动选择列表的单元格,然后单击"数据"菜单栏中的"有效性"命令.再选择"设置"选项卡,在"允许"下拉列表中选择"序列"项.这时对话框会增加"来源"项,在其下面的输入框中,输入供用户选择的序列.不同的选择项用","号分开(是在英文输入法状态下的逗号).如输入"满意,一般,不满意.
最后单击"确定"按钮完成即可.这样设置后,当用鼠标单击单元格时,在单元格右边会出现一个向下的黑色箭头,单击该箭头就会弹出一个选择输入列表了.
2.1.40 自动填充等差序列
在工作表中为数据库添加记录序列号是经常要执行的操作.可是有时如果记录太多,一个一个地输入就会显得费事.请问可以让系统自动完成吗
答:要输入一些这样有规律的数据,使用以下方法将会节省许多的时间和精力:首先在起始单元格中输入第一个数据,例如输入"1".然后在第二个单元格中输入第二个数据,如输入"3",这两个数据的大小差就是这个数据序列等差比.选择这两个单元格,移动指针至该区域的右下角的填充柄处.当鼠标变成一个黑色的实心十字形状时,按住鼠标左键执行拖动操作,到最后一个单元格处放手.这样系统就自动按照等差比值填充好其他单元格.如果要设置的一个等差比为"1"的数据序列,只要输入起始的第一个数据,然后按住"Ctrl"键,再移动鼠标这至填充柄处,这时鼠标指针会变成两个小的黑色十字状.接着再执行与上述同样的拖动操作即可完成.
2.1.41 为不相连的单元格快速输入相同信息
由于单元格的不连续分布,无法利用前面介绍的方法来快速填充,请问有什么其他的方法来实现吗
答:如果要输入相同内容的单元格不连续,还可以使用下面的方法来实现快速输入:首先按住"Ctrl"键来选择好所有的单元格,然后将光标定位到编辑栏(就是"fx"图标后面的输入栏)中,输入需要的数据.输入完成后按住"Ctrl"键不放,然后再按下"Enter"键,这样输入的数据就会自动填充到所有刚才选择的单元格.
2.1.42 在多个工作表中同时输入相同数据
如果要在不同的工作表中输入相同的内容,有什么快捷的方法吗
答:可以试试以下方法来实现:先按住"Ctrl"键,然后用鼠标单击左下脚的工作表名称来选定所有的工作表.这样所选择的工作表就会自动成为一个"成组工作表".只要在任意一个工作表中输入数据,其他的工作表也会增加相同的数据内容.如果要取消"成组工作表"模式,只要在任一工作表名称上单击鼠标右键,在弹出的菜单中选择"取消成组工作表"选项即可.
2.2.1 如何在公式中引用其他工作表
单元格数据
公式中一般可以用单元格符号来引用单元格的内容,但是都是在同一个工作表中操作的.如果要在当前工作表公式中引用别的工作表中的单元格,那该如何实现呢
答:要引用其他工作表的单元格可以使用以下方法格式来表示:工作表名称+"!"+单元格名称.如要将Sheet1工作表中的A1单元格的数据和Sheet2工作表中的B1单元格的数据相加,可以表示为:"Sheet1!A1+Sheet2!B1".
2.2.2 同时对多个单元格执行相同运算
请问有什么好的方法来实现一步完成对多个单元格数据执行同样的运算吗
答:可以执行如下操作:假设要对多个单元格执行全部加"1"的相同操作.首先在空白单元格中输入要执行运算的操作数"1",再利用"复制"按钮将其添加到剪贴板.然后选择所有要进行运算的单元格,再单击"编辑"菜单中的"选择性粘贴"命令.在弹出的对话框中单击选择"运算"选项区域下的"加"单选按钮.
最后单击"确定"按钮完成.
2.2.3 利用单步执行检查公式错误
Excel中提供的函数是十分丰富的.但是在使用一些比较复制的嵌套函数时,一旦出现错误,要找到错误原因是比较困难的.请问有什么好的方法来查找公式函数中的错误吗
答:可以利用"公式求值"功能来一步一步执行函数,这是一个准确找出错误的好办法.由于"公式求值"不是系统默认的按钮,所以需要先将其添加到工具栏.单击"工具"菜单中的"自定义"命令,在弹出的对话框中的选择"命令"选项卡.再在"类别"列表中选择"工具"项,在"命令"列表中选择"公式求值"项,最后将其拖到工具栏上.接下来选择包含函数的单元格,然后单击"公式求值"按钮.在弹出的对话框中,会用下划线表示公式中的执行步骤.
通过"求值"按钮,可以一步步地执行公式,同时观察下划线表达式的运算结果是否正确,从而找出公式的错误之处.
2.2.4 快捷输入函数参数
系统提供的函数一般都有好多不同的参数,请问如何在输入函数时能快速地查阅该函数的各个参数功能呢
答:可以利用组合键来实现:先在编辑栏中输入函数,完成后按下"Ctrl+A"组合键,系统就会自动弹出该函数的参数输入选择框,可以直接利用鼠标单击来选择各个参数.
2.2.5 函数中快速引用单元格
在函数使用时,常常需要用单元格名称来引用该单元格的数据.如果要引用的单元格太多,太散的话,那么逐个输入就会很麻烦.请问有什么简捷的方法吗
答:遇到这种情况时,可以试试下面的方法,利用鼠标直接选取引用的单元格.具体操作如下:以SUM函数为例.我们在公式编辑栏中直接输入"=SUM()",然后再将光标定位至小括号内.接着按住"Ctrl"键,在工作表中利用鼠标选择所有参与运算的单元格.这时会发现,所有被选择的单元格都自动的填入了函数中,并用","自动分隔开.输入完成后按"Enter"键结束即可.
2.2.6 组合键隐藏单元格中的公式
如果要隐藏单元格中的公式,除了用系统命令外,还有什么更快捷的方法吗
答:可以使用以下组合键来隐藏公式.选择要隐藏公式的单元格,然后按下"Ctrl+`"(数字"1"键左边的那个按键)组合键即可.如果要再次显示隐藏的公式,只要再按一次上述组合键即可重新恢复显示.
2.2.7 快速找到所需要的函数
函数应用是Excel中经常要使用的.可是如果对系统提供的函数不是很熟悉,请问有什么办法可以快速找到需要的函数吗
答:对于没学习过计算机编程的人来说,系统提供的函数的确是一个比较头痛的问题.不过使用下述方法可以非常容易地找到你需要的函数:假如需要利用函数对工作表数据进行排序操作,可以先单击工具栏的"插入函数"按钮,在弹出的对话框的"搜索函数"项下面直接输入所要的函数功能,如直接输入"排序"两个字.然后单击"转到"按钮,在下面的"选择函数"对话框中就会列出好几条用于排序的函数.单击某个函数,在对话框最下面就会显示该函数的具体功能
如果觉得还不够详细,还可以单击"有关该函数的帮助"链接来查看更详细的描述.这样就再也不会为不懂函数而头痛了.
2.2.8 利用"数据透视表"对工作表中
数据进行统计
对工作表中数据进行统计是经常需要的.一般情况我们都是使用菜单命令或函数来进行数据的统计的.可是如果要统计的工作表中记录很多,而且需要统计的项目也很多时,使用这种方法就显得力不从心了.请问还有什么更好的方法来实现吗
答:对于一些大型的工作表文件,而且统计数据的条件比较复制的情况下,可以使用数据透视表来进行统计,这种方法不但功能强大,而且操作起来比用函数,公式简单多了.下面就来统计一个包含"操行评定","性别","姓名"等字段名称的数据文件中,操行评定各个级别中男生与女生的人数.具体操作如下:首先打开工作表,选择整个工作表数据,然后单击"数据"菜单中的"数据透视表和数据透视图…"命令项.接着按"数据透视表和数据透视图向导"提示进行操作.在弹出的步骤一设置对话框中的"请指定待分析数据的数据源类型"中选择"Microsoft Excel数据列表或数据库"项.在"所需创建的报表类型"中选择"数据透视表"项,然后单击"下一步"按钮.接着会弹出第二个对话框,直接单击"下一步"按钮.在弹出第三个对话框中,在"数据透视表显示位置"选择第一项"新建工作表",最后单击"完成"按钮.然后从右边的"数据透视表字段列表"中将"性别"字段拖至"请将行字段拖至此处";将"操行评定" 字段拖至"请将列字段拖至此处";将"姓名"字段拖至"请将数据项拖至此处".这样系统将自动统计出所有操行评定级别的男生和女生的数量.
这样统计出的结果将作为一个新的工作表保存在当前文件中,方便随时查阅.
2.2.9 开启函数输入时的参数提示
平时在手动输入函数时,在输入过程中屏幕会显示一个提示条,可不知为什么现在没有了.请问该如何开启这种提示功能
答:要开启函数的参数提示功能可以执行以下操作:单击"工具"菜单中的"选项"命令,在弹出的对话框中选择"常规"选项卡,然后选择"函数工具提示"复选框即可.
2.2.10 矩阵型数据区域求和的快速实现
在一个比较大的工作表中,需要对输入的数据进行横向和纵向的求和,请问有什么快捷的方法吗
答:对于这种矩阵形状的数据进行行和列的求和,可以使用如下快捷的方法:如要对数据矩阵进行横向求和,首先用鼠标拖动操作选择整个数据矩阵,同时还要多选择一空白列.然后单击工具栏上的"∑"按钮.这样会发现多选择的那一个空白列中已经自动填入了对应行求和得到的结果了.同样,如果要按列方向求和,则只要多选择一行;如果要同时对行和列进行求和,就多选择一行和一列,最后单击"∑"按钮即可.不过用这种方法来求和时,必须保证所有的单元格都是直接输入的数据,不能是通过函数式计算得到的数据.否则在该单元格前的所有单元格数据都不会参与求和.
2.3.1 将单元格中的文本链接到图表文本框
希望系统在图标文本框中显示某个单元格中的内容,同时还要保证它们的修改保持同步,这样可以实现吗
答:这个是完全可以实现的,只要将该单元格与图表文本框建立链接关系就行.具体操作如下:首先单击选中该图表,然后在系统的编辑栏中输入一个"="符号.再单击选中需要链接的单元格,最后按下"Enter"键即可完成.此时在图表中会自动生成一个文本框,内容就是刚才选中的单元格中的内容.如果下次要修改该单元格的内容时,图表中文本框的内容也会相应地被修改.
2.3.2 重新设置系统默认的图表
当用组合键创建图表时,系统总是给出一个相同类型的图表.请问可以重新设置系统默认给出的图表类型吗
答:要修改系统的这种默认图表的类型,可以执行以下操作:首先选择一个创建好的图表,然后单击鼠标右键,在弹出的菜单中选择"图表类型"选项,再在弹出的对话框中选择一种希望的图表类型,当然也可以选择自己设置定义的类型,然后单击"设置为默认图表"按钮完成即可.
2.3.3 准确选择图表中元素的技巧
图表中的元素是很多的,而且它们的位置常常连接紧密,元素的覆盖范围又小,所以用鼠标直接单击选取实在不容易选中.请问有什么更好的办法吗
答:鼠标对选择图表元素实在有点不太方便,但是如果用键盘来操作就会又快又准.具体操作如下:首先利用鼠标左键双击该图表,使其处于编辑状态,然后利用上,下方向键来选择不同的元素组,利用左,右方向键在相同组的元素