实用工作小技巧（办公软件，持续更新）

EXCEL 

需求：将一串<=8个byte 长度的数字转为十六进制，并将低4个byte作为小数，其余高位作为整数部分。

=HEX2DEC(LEFT(DEC2HEX(BE2),(LEN(DEC2HEX(BE2))-8)))+HEX2DEC(RIGHT(DEC2HEX(BE2),8))/1000000000

用途，对于部分有特殊格式的时间戳，低位为ns，高位为s，进行时间戳的整合。

当要分析的数据是16进制的时候，可以直接用下面的函数。

=HEX2DEC(LEFT(R2,(LEN(R2)-8)))+HEX2DEC(RIGHT(R2,8))/1000000000

需求：

比较两个EXCEL 文档的差异性，并将不同点标识出来。

说明：如下宏文件原用于更新的信号矩阵SDB的差异性对比，用于其他文档，里面的参数需要更新。直接在VBA运行。

Sub CompareExcelFiles()

    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim cell1 As Range, cell2 As Range
    Dim i As Long, lastRow1 As Long, j As Long, lastRow2 As Long, k As Long
    
    
   
    Set wb1 = Workbooks.Open("D:\file1.xlsx")
    
  
    Set wb2 = Workbooks.Open("D:\file2.xlsx")
    
    
    ' Set the "matrix" worksheet in both workbooks as the active worksheet
    Set ws1 = wb1.Worksheets("matrix")
    Set ws2 = wb2.Worksheets("matrix")
    
    ' Determine the last row of data in column M of the first workbook
    lastRow1 = ws1.Cells(Rows.Count, 13).End(xlUp).Row
    lastRow2 = ws2.Cells(Rows.Count, 13).End(xlUp).Row

    
    ' Loop through each row in column M and compare the cell contents between the two workbooks
    For i = 2 To lastRow1
        Set cell1 = ws1.Cells(i, 6)
        For j = 2 To lastRow2
         Set cell2 = ws2.Cells(j, 6)
        If StrComp(Trim(ws1.Cells(i, 2).Value), Trim(ws2.Cells(j, 2).Value), vbTextCompare) = 0 _
                And StrComp(Trim(ws1.Cells(i, 4).Value), Trim(ws2.Cells(j, 4).Value), vbTextCompare) = 0 _
                And StrComp(cell1.Value, cell2.Value, vbTextCompare) = 0 _
            Then
          If Not Rows(i).Cells.SpecialCells(xlCellTypeVisible).EntireRow.Hidden Then
                For k = 1 To 25
                    If StrComp(Trim(ws1.Cells(i, k).Value), Trim(ws2.Cells(j, k).Value), vbTextCompare) = 0 _
                    Then
                        ' The row contents are different between the two workbooks, so color the cells in the first workbook to highlight the differences
                        ws1.Cells(i, k).Interior.ColorIndex = 6 ' Change the background color of cell A
                    End If
                Next k
                End If
        
        End If
        Next j
    Next i
    
    ' Close the two Excel files
    wb1.Close SaveChanges:=True ' Save changes made to the first workbook
    wb2.Close False

End Sub