????????这种数据真的很难看懂:
? ? ? ? 一般会对其画折线图或者数据条,相比起来就非常直观:
? ? ? ? 但是每一列都要手动这样设置就非常累了,所以这里就用到了VBA宏(或者Pandas)。
? ? ? ? 从这里进入宏:
? ? ? ? 随便写一个宏名后点创建:
? ? ? ? 这里可以写宏代码:
? ? ? ? 最终的效果如图:????????
????????参考代码:
模块1:
Global History(1 To 5) As Variant
Global HistoryIndex As Integer
Sub SaveCurrentState(ws As Worksheet)
HistoryIndex = HistoryIndex Mod 5 + 1
History(HistoryIndex) = ws.UsedRange.Value
End Sub
Sub Undo(ws As Worksheet)
' 检查是否有历史记录可以撤销
If HistoryIndex <= 0 Then
MsgBox "No actions to undo.", vbInformation
Exit Sub
End If
' 检查是否有保存的历史状态
If IsEmpty(History(HistoryIndex)) Then
MsgBox "No history state to apply.", vbInformation
HistoryIndex = HistoryIndex - 1 ' 减少索引,防止重复警告
If HistoryIndex < 0 Then HistoryIndex = 0 ' 确保索引不会变成负数
Exit Sub
End If
' 应用历史状态
ws.UsedRange.Value = History(HistoryIndex)
History(HistoryIndex) = Empty ' 清除已经使用的历史记录
' 更新历史索引,为下一次撤销做准备
HistoryIndex = HistoryIndex - 1
If HistoryIndex < 0 Then HistoryIndex = 0 ' 确保索引不会变成负数
End Sub
Sub AutoFitColumns(ws As Worksheet)
ws.Cells.EntireColumn.AutoFit
End Sub
Sub CenterAlign(ws As Worksheet)
ws.Cells.HorizontalAlignment = xlCenter
ws.Cells.VerticalAlignment = xlCenter
End Sub
Sub ApplyDataBars(ws As Worksheet)
Dim lastCol As Integer
Dim lastRow As Integer
Dim col As Integer
Dim cell As Range
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
For col = 2 To lastCol
For Each cell In ws.Range(ws.Cells(2, col), ws.Cells(lastRow, col))
If IsEmpty(cell.Value) Then cell.Value = 0
Next cell
With ws.Range(ws.Cells(2, col), ws.Cells(lastRow, col))
.FormatConditions.AddDatabar
With .FormatConditions(.FormatConditions.Count)
.BarColor.Color = RGB(155, 194, 230)
.BarFillType = xlDataBarFillGradient
.Direction = xlContext
.ShowValue = True
End With
End With
Next col
End Sub
Sub 数据处理工具箱()
UserForm1.Show
End Sub
UserForm1:
Private Sub InitializeHistory()
Dim i As Integer
For i = 1 To 5
History(i) = Empty
Next i
HistoryIndex = 0
End Sub
Private Sub Button_Execute_Click()
Call InitializeHistory
Dim ws As Worksheet
Set ws = ActiveSheet
If CheckBox_AutoWidth.Value = True Then
Call AutoFitColumns(ws)
SaveCurrentState ActiveSheet
End If
If CheckBox_CenterAlign.Value = True Then
Call CenterAlign(ws)
SaveCurrentState ActiveSheet
End If
If CheckBox_DataBars.Value = True Then
Call ApplyDataBars(ws)
SaveCurrentState ActiveSheet
End If
End Sub
Private Sub Button_Undo_Click()
Undo ActiveSheet
End Sub
? ? ? ? 最后,导出模块,以便共享:
????????参考代码:
excel_file = f'dataset_statistics_{use_model}.xlsx'
with pd.ExcelWriter(excel_file, engine='xlsxwriter') as writer:
df.to_excel(writer, index=True, sheet_name='Sheet1')
workbook = writer.book
worksheet = writer.sheets['Sheet1']
for idx, col in enumerate(df.columns):
col_max_width = max(df[col].astype(str).str.len().max(), len(col))
worksheet.set_column(idx, idx, col_max_width)
for col_num in range(1, len(df.columns)):
worksheet.conditional_format(1, col_num, len(df), col_num,
{'type': 'data_bar',
'bar_color': '#A9CCE3',
'data_bar_2010': True})
????????效果如图:
? ? ? ? 比较粗糙,需要精调,没有上面VBA宏的结果好看。