Excel VBA中日期格式不被SUMIF/VLOOKUP识别的解决方法

你的核心问题是：当前代码通过字符串拼接生成的"日期"本质是文本类型，而非Excel能识别的日期数据类型，所以SUMIF、VLOOKUP这类依赖数值匹配的函数无法正常工作。以下是具体解决方案：

关键修改思路 #

Excel的日期本质是序列号（比如2023/11/1对应45255），只有生成真正的日期值，函数才能正确匹配。需要把意大利语月份名转换成数字，再用DateSerial函数生成标准日期。

修改后的完整代码 #

Sub Recupera_dati()
    Dim dtToday As Date
    Dim Anno As String
    Dim Url As String
    Dim sQry As String
    Dim Riga2 As Integer
    Dim MeseNum As Integer ' 存储月份对应的数字
    
    Riga2 = 2
    Anno = "2023"
    Mese = "Novembre"
    
    ' 将意大利语月份转换为数字
    Select Case Mese
        Case "Gennaio": MeseNum = 1
        Case "Febbraio": MeseNum = 2
        Case "Marzo": MeseNum = 3
        Case "Aprile": MeseNum = 4
        Case "Maggio": MeseNum = 5
        Case "Giugno": MeseNum = 6
        Case "Luglio": MeseNum = 7
        Case "Agosto": MeseNum = 8
        Case "Settembre": MeseNum = 9
        Case "Ottobre": MeseNum = 10
        Case "Novembre": MeseNum = 11
        Case "Dicembre": MeseNum = 12
    End Select
    
    Url = "https://www.ilmeteo.it/portale/archivio-meteo/Momo/" & Anno & "/" & Mese
    sQry = Mese & Anno
    ActiveWorkbook.Queries.Add Name:=sQry, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Origine = Web.Page(Web.Contents(""" & Url & """))," & Chr(13) & "" & Chr(10) & _
        "    Data2 = Origine{2}[Data]," & Chr(13) & "" & Chr(10) & _
        "    #""Modificato tipo"" = Table.TransformColumnTypes(Data2,{{""Giorno"", Int64.Type}, {""T Media"", type text}, " & _
        "{""T min"", type text}, {""T max"", type text}, {""Precip.", type text}, {""Umidit¨¤", type text}, {""Vento Max"", type text}," & _
        " {""Raffica"", type text}, {""Fenomeni"", type text}, {""Info"", type text}})" & Chr(13) & "" & Chr(10) & "in" & _
        "    #""Modificato tipo"""
    
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & sQry & ";Extended Properties="""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & sQry & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = sQry
        .Refresh BackgroundQuery:=False
    End With

    Range("A1").EntireColumn.Insert
    Range("A1").Value = "Data"
    ' 先设置整列格式，避免循环中重复设置
    Columns("A:A").NumberFormat = "dd/mm/yyyy"

    While Worksheets(1).Cells(Riga2, 2) <> ""
        ' 使用DateSerial生成真正的日期值
        Worksheets(1).Cells(Riga2, 1).Value = DateSerial(CInt(Anno), MeseNum, CInt(Worksheets(1).Cells(Riga2, 2).Value))
        Riga2 = Riga2 + 1
    Wend
End Sub

核心修改点说明 #

• 月份转数字：新增Select Case逻辑，把意大利语月份名转换成对应的数字（比如"Novembre"转11），这是生成正确日期的前提。
• 生成标准日期：用DateSerial(年, 月, 日)函数创建真正的日期数据类型，而非字符串拼接，这样单元格存储的是日期序列号，能被SUMIF/VLOOKUP识别。
• 优化格式设置：提前设置整列的日期格式，避免循环中重复操作，提升效率。

内容的提问来源于stack exchange，提问作者Inuraghe