☰

Microsoft Excel 2010 - 邮政编码范围

tag-icon • tag-icon worksheet-function microsoft-excel-2010
Microsoft Excel 2010 - 邮政编码范围

我需要知道如何让 Excel 了解邮政编码区域。

例如，我在 A2 中有一个起始邮政编码，在 B2 中有一个范围结束，在 B3 中有一个该地区的价格范围，我如何才能获得一个公式来检查范围内的邮政编码并从 B3 中获取正确的价格范围以完成价格？ Vlookup 只理解起始邮政编码，如果它与手头的邮政编码不 100% 匹配，它就不会带来任何东西。我可以使用什么公式来解决这类问题？

答案1

使用 VBA 的操作方法如下：

Public Sub AddCarriage()
  Dim intCOUNTER As Integer
  Dim intCOUNTER2 As Integer
  Dim intLASTROW As Integer
  Dim intPCNUMBER As Integer
  Dim strPCTEXT As String
  Dim intLENGTH As Integer
  Dim intPOS As Integer
  Dim strFIRST As String

' Get the last row of postcodes containing data
  intLASTROW = Range("A65536").End(xlUp).Row

' Get the postcode for the delivery address
  strPCTEXT = Range("G1").Text

' Check if the postcode is all numeric (European?) or UK based
  If IsNumeric(Range("G1").Text) Then
    intPCNUMBER = Range("G1").Text
  Else
' Separate the numbers from the letters in a UK postcode
    intPOS = InStr(1, strPCTEXT, " ")
    If intPOS > 0 Then
      intLENGTH = intPOS - 1
      strFIRST = Left(Range("G1").Text, intPOS - 1)
      For intCOUNTER = 1 To intLENGTH
        If IsNumeric(Mid(strFIRST, intCOUNTER, 1)) Then
          strPCTEXT = Left(strFIRST, intCOUNTER - 1)
          intPCNUMBER = Mid(strFIRST, intCOUNTER, (intLENGTH - intCOUNTER) + 1)
          Exit For
        End If
      Next
    Else
      strPCTEXT = Range("G1").Text
    End If
  End If

  For intCOUNTER = 1 To intLASTROW
    If IsNumeric(Range("A" & intCOUNTER).Text) Then
      For intCOUNTER2 = Range("A" & intCOUNTER).Text To Range("B" & intCOUNTER).Text
        If intCOUNTER2 = intPCNUMBER Then Range("H1").Value = Range("E" & intCOUNTER).Value
      Next
    Else
      If Left(Range("A" & intCOUNTER).Text, Len(strPCTEXT)) = strPCTEXT Then
        For intCOUNTER2 = Mid(Range("A" & intCOUNTER).Text, Len(strPCTEXT) + 1, Len(intPCNUMBER)) To Mid(Range("B" & intCOUNTER).Text, Len(strPCTEXT) + 1, Len(intPCNUMBER))
          If intCOUNTER2 = intPCNUMBER Then Range("H1").Value = Range("E" & intCOUNTER).Text
        Next
      End If
    End If
  Next

' Row E contains the delivery cost for that postcode range
' Cell "H1" is the cell that the delivery cost is to be entered in to
End Sub

显然，在我的示例中，运费与输入的邮政编码在同一张表上。您需要添加相关查询，才能让此查询与您已有的查询配合使用。

相关内容