想做个销售表格，用三级有效性菜单，请问如何做呢？

じ灬ぷ鄒皮

想做个销售表格，用三级有效性菜单，请问如何做呢？我给你们一个数据库（例子），在销售工作表里，A列有效性是数据库的产品，B列有效性是产品品质（优差），C列自动配对产品优差的价格，谢谢，请看表格 会清楚点

じ灬ぷ鄒皮

表格在这里 忘记添加上去，请看表格

安全为主

じ灬ぷ鄒皮 发表于 2012-12-14 10:07
表格在这里 忘记添加上去，请看表格

这个c列可以直接用sumproduct函数来匹配的，不需要下拉。{:soso_e112:}

efme

添加一个辅助列

840205910

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
Set d = CreateObject("scripting.dictionary")
arr = Sheet1.UsedRange
For i = 2 To UBound(arr)
d(arr(i, 1)) = ""
Next
k = d.keys
For i = 0 To d.Count - 1
  f = f & k(i) & ","
Next
With Target.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=f
End With
ElseIf Target.Column = 2 Then
Set d = CreateObject("scripting.dictionary")
arr = Sheet1.UsedRange
For i = 2 To UBound(arr)
  If arr(i, 1) = Target.Offset(0, -1).Value Then
  d(arr(i, 2)) = ""
  End If
Next
k = d.keys
For i = 0 To d.Count - 1
  f = f & k(i) & ","
Next
With Target.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=f
End With
ElseIf Target.Column = 3 Then
Set d = CreateObject("scripting.dictionary")
arr = Sheet1.UsedRange
For i = 2 To UBound(arr)
  If arr(i, 1) = Target.Offset(0, -2).Value And arr(i, 2) = Target.Offset(0, -1).Value Then
  d(arr(i, 3)) = ""
  End If
Next
k = d.keys
For i = 0 To d.Count - 1
  f = f & k(i) & ","
Next
With Target.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=f
End With
End If
End Sub

840205910

新建 Microsoft Excel 工作表.zip (12.49 KB, 下载次数: 134)

2012-12-14 10:39 上传

点击文件名下载附件

じ灬ぷ鄒皮

3楼 请问sumproduct函数怎么用，新鸟，谢谢，4L 你的方法好像也不行，你重新看看

じ灬ぷ鄒皮

请问840205910 ，你的表格要怎么做成的，能说一下简单的过程吗

840205910

用宏代码写的，
利用字典的关键词做数据有效性

10307951261

留个脚印学习