用VBA OR PYTHON, PYTHON SCRIPT短但你要識用先得
如果你D格上十萬會有排LOAD
=======================================
瞭解您的需求,您希望修改程式碼以便支援多個工作表。下面我將分別提供VBA和Python的程式碼示例。
對於VBA,您可以新增另一個循環來遍歷工作簿里的所有工作表。
vba
Sub CompareMultipleWorksheets()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim r As Long, c As Integer
Dim cell1 As Range, cell2 As Range
Dim diffCount As Long, wsCount As Integer
Set wb1 = Workbooks("Workbook1.xlsx")
Set wb2 = Workbooks("Workbook2.xlsx")
wsCount = wb1.Sheets.Count
If wsCount <> wb2.Sheets.Count Then
MsgBox "工作簿中的工作表數量不同。"
Exit Sub
End If
For i = 1 To wsCount
Set ws1 = wb1.Sheets(i)
Set ws2 = wb2.Sheets(i)
For r = 1 To ws1.UsedRange.Rows.Count
For c = 1 To ws1.UsedRange.Columns.Count
Set cell1 = ws1.Cells(r, c)
Set cell2 = ws2.Cells(r, c)
If cell1.Value <> cell2.Value Then
diffCount = diffCount + 1
Debug.Print "工作表: " & ws1.Name & " - 不一樣的單元格: " & cell1.Address & " 值: " & cell1.Value & " | " & cell2.Value
End If
Next c
Next r
Next i
If diffCount = 0 Then
MsgBox "所有工作表均沒有差異。"
Else
MsgBox "工作表中發現 " & diffCount & " 處差異,請檢視立即視窗。"
End If
End Sub
對於Python,您可以使用循環來檢查每個工作表中的差異。
python
import pandas as pd
from openpyxl import load_workbook
# 載入工作簿
wb1 = load_workbook(filename='Workbook1.xlsx')
wb2 = load_workbook(filename='Workbook2.xlsx')
# 確保兩個工作簿的工作表數量相同
if len(wb1.sheetnames) != len(wb2.sheetnames):
print("工作簿中的工作表數量不同。")
else:
# 遍歷每個工作表
for sheet_name in wb1.sheetnames:
df1 = pd.read_excel('Workbook1.xlsx', sheet_name=sheet_name)
df2 = pd.read_excel('Workbook2.xlsx', sheet_name=sheet_name)
# 比較兩個DataFrame對像
diff = (df1 != df2) & ~(df1.isnull() & df2.isnull())
# 列印不一樣的儲存格的位置和值
for row in diff.any(axis=1).nonzero()[0]:
for col in diff.iloc[row].nonzero()[0]:
cell1 = df1.iat[row, col]
cell2 = df2.iat[row, col]
if cell1 != cell2:
print(f'工作表: {sheet_name} - 不一樣的儲存格: ({row+1}, {col+1}) 值: {cell1} | {cell2}')
請確保在執行以上兩段程式碼之前,您已經打開了相應的工作簿,並且在Python程式碼中已經安裝了所需的庫。