【教程】通过Excel宏/Pandas两种方法来自动添加渐变数据条

小锋学长生活大爆炸

发布于 2024-01-12 08:44:32

1570

发布于 2024-01-12 08:44:32

文章被收录于专栏：小锋学长生活大爆炸

这种数据真的很难看懂：

一般会对其画折线图或者数据条，相比起来就非常直观：

但是每一列都要手动这样设置就非常累了，所以这里就用到了VBA宏(或者Pandas)。

VBA宏方法

从这里进入宏：

随便写一个宏名后点创建：

这里可以写宏代码：

最终的效果如图：

参考代码：

模块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

最后，导出模块，以便共享：

Pandas方法

参考代码：

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})

效果如图：