excel - 验证还是不验证？

Question

我正在接受用户的输入，为此我可以给他选择。
例如，在“导体”列中，可以选择“铝或铜”

同样，在“绝缘子”列中，他可以选择“ XLPE或PVC”

现在，以上两个是我提供给用户的列表的简单版本，但是我的一些列表取决于以前的输入，为此，我正在使用datavalidation，如下所示：

=indirect($C5 & "_" & $D5) 

（例如：al_xlpe的命名范围）

假设C和D列引用了某些输入（这将导致前面定义2！命名范围）。

由于上述方法，我不得不使用很多命名范围（我的某些验证列表选项取决于4个或更多输入，它们的显示方式如下：

=indirect("col6" & "_" & col7 & "_" & col8 & "_" & col9) 

（例如al_xlpe_duct_3的另一个命名范围；其中可以有4个！）

我面临几个问题：


由于数据库可以随时扩展以获取包含4个以上输入的验证列表，因此需要4个！命名范围更改。
数据验证很容易丢失（一个主要问题）
我无法限制复制和粘贴，因为大多数用户将粘贴其他工作表中的数据（无法使用导入，因为列永远不会固定）
我无法使用列表框，因为可以输入任意数量的数据行，并且我需要在每一行中进行选择
无法使用MS Access进行数据库管理，因为我的工具适用于输入数据，并且我的大多数用户都不熟悉访问（此外，它不允许轻松复制数据粘贴）


有没有更好的方法？

Answer 1

在这个答案中，我提供了一种技术，这种技术在我开始从事程序员工作45年时非常流行，但是多年来，除了我自己，我还没有看到任何人将其用于常规应用程序中。我们从编译器开发中借用了该技术，但在形式上却少了很多。

在完整技术中，将分五个步骤：


设计一种以对人类方便的方式对规范进行编码的方法。
对规范进行编码。
设计一个或多个表以方便快速处理的方式保存规范。
设计并实现一个程序，以将人类格式转换为快速处理格式。
设计并实现一个程序来解释快速处理格式并执行所需的操作。


并非每个问题都需要全部五个步骤；有时，人工格式和快速处理格式可能相同。这听起来很复杂，但它使我们能够轻松，有效地解决许多复杂的问题。

在下面的工作表中，我对所需的验证类型进行了编码。

  |         A          |         B          |         C          |
--+--------------------+--------------------+--------------------+    
 1|Permitted           |Conditions -------------->               |
 2|C=V1|V2|V3|V4       |                    |                    |
 3|D=V5                |C=V1|V2             |                    |
 4|D=V6                |C=V3|V4             |                    |
 5|E=V7|V8             |D=V5                |C=V1                |
 6|E=V9|V10            |D=V5                |C=V2                |
 7|E=V11|V12           |D=V6                |C=V3                |
 8|E=V13|V14           |D=V6                |C=V4                |

在第2行中，我声明C列中的单元格可以采用V1或V2或V3或V4的值。

在第3行中，我声明D列中的单元格可以采用V5的值，但前提是同一行的C列具有V1或V2的值。

在第4行中，我为D列中具有其自身条件集的单元格声明了一个替代值。

在第5行中，我声明E列中的单元格可以采用V7或V8的值，但前提是同一行的D列的值为V5且该行的C列的值为V1。

我对您的要求还没有足够的了解，无法确定这是对您的验证要求的最好或完整的表示。但是，我希望您能理解这个想法，并且如果您喜欢这种技术，可以方便地提出自己的要求。

接下来，我需要定义此规范的快速处理形式。我已经设计了四个表并实现了下面的代码，该代码将人工工作表格式转换为快速处理格式，然后将这些表的内容输出到立即窗口中，准备放置在此答案中：

Rules per Column table 
C RR RR                = Column   First rule   Last rule
3  1  1
4  2  3
5  4  7

工作表中包含验证规则的三列分别是第3列（C），4列（D）和5列（E）。上表告诉我们，对于第3栏（C），规则1至1适用；对于第5栏（E），规则4至7适用。

Rule table              
I VV VV CC CC   = Index   First value   Last value   First condition   Last condition
1  1  4  1  0 
2  5  5  1  1 
3  8  8  2  2 
4 11 12  3  4 
5 15 16  5  6 
6 19 20  7  8 
7 23 24  9 10 

对于规则1，条件1到0适用，即没有条件。允许的值是“值”表中的条目1至4（V1，V2，V3和V4）。这对应于工作表中的第2行。

对于规则4，允许的值是“值”表中的条目11和12（V7和V8），并提供条件3至4。条件3是列4（D）必须等于“值”表中的条目13（V5）。条件4是列3（C）必须等于“值”表中的条目14（V1）。这对应于工作表中的第5行。

Condition table
 I C VV VV          = Index   Column   First value   Last value
 1 3  6  7
 2 3  9 10
 3 4 13 13
 4 3 14 14
 5 4 17 17
 6 3 18 18
 7 4 21 21
 8 3 22 22
 9 4 25 25
10 3 26 26

Value table                 Entries 1 to 26
E 1=V1  E 2=V2  E 3=V3  E 4=V4  E 5=V5  E 6=V1  E 7=V2  E 8=V6  E 9=V3  E10=V4   
E11=V7  E12=V8  E13=V5  E14=V1  E15=V9  E16=V10 E17=V5  E18=V2  E19=V11 E20=V12  
E21=V6  E22=V3  E23=V13 E24=V14 E25=V6  E26=V4

如果您不习惯通过链接表控制代码，那么可能需要一点时间才能完全理解。我遵循了一些规则的链接。再尝试几次，您会明白的。请注意，工作表的设计方式是使人易于维护，而这些表的设计目的是使计算机快速执行。

此编译过程可以在“工作表打开”例程中，也可以预编译并将表存储在工作簿中。这些表可以通过“工作表更改”例程执行，也可以用于在适当的单元格中计算和放置公式。

我希望我已经对此进行了充分的解释，以使您有所了解并确定该技术是否适合您的问题。可能会在必要时提出一些问题，我将扩大解释。

以下代码将人工格式转换为快速处理格式，然后将快速处理格式输出到立即窗口。

Option Explicit

  Type typColRule      ' Definition of entry in Rules per Column table 
    InxRule1 As Long   ' Index of first rule for this column. ) InxRule1 > InxRuleL 
    InxRuleL As Long   ' Index of last rule for this column.  ) if no rules for column
  End Type
  Type typRule         ' Definition of Rule table 
    InxValue1 As Long  ' Index of first permitted value for this rule
    InxValueL As Long  ' Index of last permitted value for this rule
    InxCond1 As Long   ' Index of first condition for this column. ) InxCond1 > InxCondL 
    InxCondL As Long   ' Index of last rule for this column.       ) if no rules for column
  End Type
  Type typCond         ' Definition of Condition table
    Col As Long        ' Column to which this condition applies
    InxValue1 As Long  ' Index of first permitted value for this condition
    InxValueL As Long  ' Index of last permitted value for this condition
  End Type

  ' ColRule is sized to (Min to Max) where Min is the lowest column validated
  ' and Max is the highest column validated.  ColRule(N).InxRule1 identifies
  ' the first rule in Rule for column N.  ColRule(N).InsRuleL identifies the
  ' last rule in Rule for column N.
  Dim ColRule() As typColRule

  ' There is one entry in Rule per validation row in worksheet "Validate".
  Dim Rule() As typRule

  ' There is one entry in ValueCell per value referenced in a permitted or
  ' a condition.
  Dim ValueCell() As String

  ' There is one entry in Cond per condition in worksheet "Validate"
  Dim Cond() As typCond

Sub CompileValidation()

  Dim ColCodeCrnt As String
  Dim ColNumCrnt As String
  Dim ColValCrnt As Long
  Dim ColValidateCrnt As Long
  Dim ColValMin As Long
  Dim ColValMax As Long
  Dim ConditionCrnt As String
  Dim InxCondCrnt As Long
  Dim InxRuleCrnt As Long
  Dim InxValueCellCrnt As Long
  Dim InxValueListCrnt As Long
  Dim NumCond As Long
  Dim NumValue As Long
  Dim PermittedCrnt As String
  Dim PosEqual As Long
  Dim RowValidateCrnt As Long
  Dim ValueList() As String

  With Worksheets("Validate")

    ' Determine the size of the arrays to which information will be
    ' compiled.  Find
    '   * The minimum and maximum columns subject to validated
    '   * Number of conditions
    '   * Number of values references
    ' This routine does not allow for blank rows or columns in the
    ' middle of worksheet "Validate".
    ColValMin = -1
    ColValMax = -1
    NumCond = 0
    NumValue = 0
    RowValidateCrnt = 2
    Do While True
      PermittedCrnt = .Cells(RowValidateCrnt, 1).Value
      If PermittedCrnt = "" Then
        Exit Do
      End If
      PosEqual = InStr(1, PermittedCrnt, "=")
      Debug.Assert PosEqual > 1
      ' Determine range of columns validated
      ColCodeCrnt = Mid(PermittedCrnt, 1, PosEqual - 1)
      ColNumCrnt = Range(ColCodeCrnt & "1").Column
      If ColValMin = -1 Then
        ColValMin = ColNumCrnt
      ElseIf ColValMin > ColNumCrnt Then
        ColValMin = ColNumCrnt
      End If
      If ColValMax = -1 Then
        ColValMax = ColNumCrnt
      ElseIf ColValMax < ColNumCrnt Then
        ColValMax = ColNumCrnt
      End If
      ' Determine number of conditions and number of values
      ValueList = Split(Mid(PermittedCrnt, PosEqual + 1), "|")
      NumValue = NumValue + UBound(ValueList) - LBound(ValueList) + 1
      ColValidateCrnt = 2
      Do While True
        ConditionCrnt = .Cells(RowValidateCrnt, ColValidateCrnt).Value
        If ConditionCrnt = "" Then
          Exit Do
        End If
        PosEqual = InStr(1, ConditionCrnt, "=")
        Debug.Assert PosEqual > 1
        ValueList = Split(Mid(ConditionCrnt, PosEqual + 1), "|")
        NumValue = NumValue + UBound(ValueList) - LBound(ValueList) + 1
        ColValidateCrnt = ColValidateCrnt + 1
      Loop
      NumCond = NumCond + ColValidateCrnt - 2
      RowValidateCrnt = RowValidateCrnt + 1
    Loop

    ' Size arrays
    ReDim ColRule(ColValMin To ColValMax)
    ReDim Rule(1 To RowValidateCrnt - 2)
    ReDim ValueCell(1 To NumValue)
    ReDim Cond(1 To NumCond)

    InxRuleCrnt = 0
    InxValueCellCrnt = 0
    InxCondCrnt = 0

    ' Extract rules in column number order
    For ColValCrnt = ColValMin To ColValMax
      ' The first rule for this column, if any, will be the
      ' next entry in the Rule table
      ColRule(ColValCrnt).InxRule1 = InxRuleCrnt + 1
      ' If there are no rules for this column, the last rule index
      ' will be less than the first rule undex
      ColRule(ColValCrnt).InxRuleL = InxRuleCrnt
      RowValidateCrnt = 2
      Do While True
        PermittedCrnt = .Cells(RowValidateCrnt, 1).Value
        If PermittedCrnt = "" Then
          Exit Do
        End If
        PosEqual = InStr(1, PermittedCrnt, "=")
        ColCodeCrnt = Mid(PermittedCrnt, 1, PosEqual - 1)
        ColNumCrnt = Range(ColCodeCrnt & "1").Column
        If ColNumCrnt = ColValCrnt Then
          ' This rule is for the current column
          InxRuleCrnt = InxRuleCrnt + 1
          ' This could be the last rule for this column so
          ' store its index against the column
          ColRule(ColValCrnt).InxRuleL = InxRuleCrnt
          ' The first value for this rule will be the next
          ' entry in the Value table
          Rule(InxRuleCrnt).InxValue1 = InxValueCellCrnt + 1
          ValueList = Split(Mid(PermittedCrnt, PosEqual + 1), "|")
          ' Save each permitted value in the Value table
          For InxValueListCrnt = LBound(ValueList) To UBound(ValueList)
            InxValueCellCrnt = InxValueCellCrnt + 1
            ValueCell(InxValueCellCrnt) = ValueList(InxValueListCrnt)
          Next
          ' Record the index of the last permitted value for this rule
          Rule(InxRuleCrnt).InxValueL = InxValueCellCrnt
          ' The first condition for this rule, if any, will be the next
          ' entry in the Condition table
          Rule(InxRuleCrnt).InxCond1 = InxCondCrnt + 1
          ' If there are no conditions for this rule, the last condition
          ' index will be less than the first condition undex
          Rule(InxRuleCrnt).InxCondL = InxCondCrnt
          ColValidateCrnt = 2
          Do While True
            ConditionCrnt = .Cells(RowValidateCrnt, ColValidateCrnt).Value
            If ConditionCrnt = "" Then
              Exit Do
            End If
            InxCondCrnt = InxCondCrnt + 1
            PosEqual = InStr(1, ConditionCrnt, "=")
            ColCodeCrnt = Mid(ConditionCrnt, 1, PosEqual - 1)
            ColNumCrnt = Range(ColCodeCrnt & "1").Column
            ' Store the column for this condition
            Cond(InxCondCrnt).Col = ColNumCrnt
            ' The first value for this condition will be the next
            ' entry in the Value table
            Cond(InxCondCrnt).InxValue1 = InxValueCellCrnt + 1
            ValueList = Split(Mid(ConditionCrnt, PosEqual + 1), "|")
            For InxValueListCrnt = LBound(ValueList) To UBound(ValueList)
              InxValueCellCrnt = InxValueCellCrnt + 1
              ValueCell(InxValueCellCrnt) = ValueList(InxValueListCrnt)
            Next
            ' Record last value for this condition
            Cond(InxCondCrnt).InxValueL = InxValueCellCrnt
            ColValidateCrnt = ColValidateCrnt + 1
          Loop
          ' Record last condition for this rule
          Rule(InxRuleCrnt).InxCondL = InxCondCrnt
        End If
        RowValidateCrnt = RowValidateCrnt + 1
      Loop
    Next
  End With

  Debug.Print "    Rules per Column table"
  Debug.Print "    C RR RR"
  For ColValCrnt = ColValMin To ColValMax
    Debug.Print "    " & ColValCrnt & " " & _
                Right(" " & ColRule(ColValCrnt).InxRule1, 2) & " " & _
                Right(" " & ColRule(ColValCrnt).InxRuleL, 2)
  Next
  Debug.Print
  Debug.Print "    Rule table"
  Debug.Print "    I VV VV CC CC"
  For InxRuleCrnt = 1 To UBound(Rule)
    Debug.Print "    " & InxRuleCrnt & " " & _
                         Right(" " & Rule(InxRuleCrnt).InxValue1, 2) & " " & _
                         Right(" " & Rule(InxRuleCrnt).InxValueL, 2) & " " & _
                         Right(" " & Rule(InxRuleCrnt).InxCond1, 2) & " " & _
                         Right(" " & Rule(InxRuleCrnt).InxCondL, 2) & " "
  Next
  Debug.Print
  Debug.Print "    Condition table"
  Debug.Print "     I C VV VV"
  For InxCondCrnt = 1 To UBound(Cond)
    Debug.Print "    " & Right(" " & InxCondCrnt, 2) & " " & _
                         Cond(InxCondCrnt).Col & " " & _
                         Right(" " & Cond(InxCondCrnt).InxValue1, 2) & " " & _
                         Right(" " & Cond(InxCondCrnt).InxValueL, 2)
  Next
  Debug.Print
  Debug.Print "    Value table"
  Debug.Print "    ";
  For InxValueCellCrnt = 1 To UBound(ValueCell)
    Debug.Print "E" & Right(" " & InxValueCellCrnt, 2) & "=" & _
                Left(ValueCell(InxValueCellCrnt) & "    ", 5);
    If (InxValueCellCrnt Mod 10) = 0 Then
      Debug.Print
      Debug.Print "    ";
    End If
  Next

结束子