基于非相等时间变量合并Excel文件：匹配最近时间值需求

Got it, let's tackle this time-matching problem. Since you need to pair each time in your second dataset with the closest 10-minute interval time from the first dataset (instead of strict equality), here are three practical methods that work better than basic VLOOKUP or simple Power Query matches:

Method 1: Excel Array Formula (Great for Small Datasets) #

If your dataset isn't huge, you can use a combination of INDEX, MATCH, and ABS to calculate the smallest time difference and pull the matching value.

Setup: #

• Let’s say your first 10-minute interval times are in Sheet1!A:A
• Your second dataset’s times are in Sheet2!A:A
• You want to output the closest matching time in Sheet2!B:B

Formula: #

=INDEX(Sheet1!$A:$A, MATCH(MIN(ABS(Sheet1!$A:$A - Sheet2!A1)), ABS(Sheet1!$A:$A - Sheet2!A1), 0))

Notes: #

• For Excel 2019 or earlier: Enter the formula and press Ctrl+Shift+Enter to run it as an array formula.
• For Excel 365/2021: Just press Enter—dynamic arrays handle this automatically.
• If two times are equally close (e.g., a time exactly halfway between two 10-minute intervals), this returns the earlier of the two. To get the later one, adjust the MATCH logic to find the last occurrence instead of the first.
• To pull additional columns from Sheet1 (like values linked to the time), modify the INDEX range (e.g., Sheet1!$A:$B instead of Sheet1!$A:$A) and adjust the column index if needed.

Method 2: Power Query (Best for Large Datasets) #

Power Query can handle this efficiently with custom functions, even for big datasets. Here's how to set it up:

1. Import both tables into Power Query:
   Go to Data > Get Data > From File > From Workbook and select each file, loading them into Power Query Editor.

2. Prepare the source table:
   Keep only the time column (and any other columns you want to merge) from your first 10-minute interval table. Let’s name this table IntervalTimes.

3. Add a custom column to your target table:
   In your second dataset’s table (let’s call this RawTimes), go to Add Column > Custom Column and use the List.MinBy function to find the closest time:

   = List.MinBy(IntervalTimes[Time], each Duration.Abs(_ - [RawTime]))
   

   Replace IntervalTimes[Time] with your source table’s time column name, and [RawTime] with your target table’s time column name.

4. Expand the custom column:
   Click the expand icon next to the new custom column, select the columns you want to merge (like the matched time and its associated values), and click OK.

5. Load back to Excel:
   Go to Home > Close & Load to export the merged data to a new worksheet.

Method 3: VBA Macro (Automated Batch Processing) #

If you need to repeat this process regularly, a VBA macro can automate the matching.

Macro Code: #

Sub MatchClosestTime()
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim sourceRange As Range, targetCell As Range
    Dim closestTime As Date, minDiff As Double, currentDiff As Double
    Dim sourceCell As Range
    
    ' Set your worksheet names here
    Set wsSource = ThisWorkbook.Sheets("Sheet1") ' 10-minute interval times
    Set wsTarget = ThisWorkbook.Sheets("Sheet2") ' Raw times to match
    
    ' Define the range of times in the source sheet
    Set sourceRange = wsSource.Range("A2:A" & wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row)
    
    ' Loop through each time in the target sheet
    For Each targetCell In wsTarget.Range("A2:A" & wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Row)
        minDiff = 999999 ' Initialize with a large number
        For Each sourceCell In sourceRange
            currentDiff = Abs(sourceCell.Value - targetCell.Value)
            ' Update closest time if current difference is smaller
            If currentDiff < minDiff Then
                minDiff = currentDiff
                closestTime = sourceCell.Value
                ' Optional: Store the row number if you need to pull other columns
                ' sourceRow = sourceCell.Row
            End If
        Next sourceCell
        
        ' Write the closest time to the cell next to the target time
        targetCell.Offset(0, 1).Value = closestTime
        
        ' Optional: Pull a value from column B of the source sheet
        ' targetCell.Offset(0, 2).Value = wsSource.Cells(sourceRow, "B").Value
    Next targetCell
End Sub

How to Use: #

1. Press Alt+F11 to open the VBA Editor.
2. Insert a new module (Insert > Module).
3. Paste the code above, adjust the worksheet names and column references to match your files.
4. Run the macro (F5) or assign it to a button in Excel for easy access.

Key Notes for All Methods: #

• Ensure time columns are formatted correctly: Both datasets’ time columns should be set to a Time or Date/Time format (not text) to avoid calculation errors.
• Handle ties: If a time is exactly between two 10-minute intervals, adjust the logic (e.g., in the formula, use MATCH(1, (ABS(...)=MIN(...))*1, 0) and sort the source times descending to get the later match).

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