2学习EXCEL的心得.docVIP

1、怎样设置数据重复输入 数据—有效性—设置选项卡—允许：自定义—公式：=COUNTIF(C:C,C5)=1 2、单元格数据只输入一次，就不能在更改，需密码才行 注：绿色区域的单元格只能输入一次数据Dim A Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, [a1:b55555,d1:f55555]) Is Nothing Then Exit Sub Application.EnableEvents = False If A Then b = InputBox(改变内容，请输入密码！) If b 123 Then MsgBox 密码错误，数据不能更改! Target = A End If End If Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub Else Target.Cells(1, 1).Select A = Target End If End Sub 方法二： Dim a Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count = 1 Then If Intersect(Target, [a1:b55555,d1:f55555]) Is Nothing Then Exit Sub Application.EnableEvents = False If a Then b = InputBox(改变内容，请输入密码！) If b 123 Then MsgBox 密码错误，数据不能更改! Target = a End If End If End If Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.Cells(1, 1).Select a = Target End Sub 方法三 Private Sub Worksheet_SelectionChange(ByVal Target As Range)? ? If Target.Count = 1 Then? ???If Intersect(Target, [a1:c10,e1:e10]) Is Nothing Then Exit Sub? ?? ?? ?If Target.Value = Then? ? ActiveSheet.Unprotect? ? Else? ? Dim bZ:? ? b = InputBox(请输入密码, 检查权限)? ?? ???If b = 123 Then? ?? ???Exit Sub? ?? ???Else? ?? ???If b = Then? ?? ???Cells(11, ActiveCell.Column).Select? ?? ???Exit Sub? ?? ???Else? ?? ?? ?? ???GoTo Z End If? ?? ?? ?? ?? ?End If End If End If End Sub 3、公式自动填充 1【可保护工作表】)在D列输入数据，10列和11列自动向下填充公式，此代码在工作表中 要在工作表保护后使用自动筛选功能,需要确保: 1,保护工作表时工作表处于自动筛选模式(即显示自动筛选的下拉按钮), 2,保护时指定允许筛选Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Unprotect 123 解除工作表保护 Application.ScreenUpdating = False Application.EnableEvents = False k = Range(c6556).End(xlUp).Row Range(Cells(6, 1), Cells(k, 2)).FillDown Range(Cells(6, 10), Cells(k, 10)).FillDown Application.E