上海外贸网站开发,引流推广营销,中国最近军事新闻视频,响应式模板网站模板下载这种数据真的很难看懂#xff1a; 一般会对其画折线图或者数据条#xff0c;相比起来就非常直观#xff1a; 但是每一列都要手动这样设置就非常累了#xff0c;所以这里就用到了VBA宏(或者Pandas)。
VBA宏方法 从这里进入宏#xff1a; 随便写一个宏名后点创建#xff1… 这种数据真的很难看懂 一般会对其画折线图或者数据条相比起来就非常直观 但是每一列都要手动这样设置就非常累了所以这里就用到了VBA宏(或者Pandas)。
VBA宏方法 从这里进入宏 随便写一个宏名后点创建 这里可以写宏代码 最终的效果如图 参考代码
模块1
Global History(1 To 5) As Variant
Global HistoryIndex As IntegerSub SaveCurrentState(ws As Worksheet)HistoryIndex HistoryIndex Mod 5 1History(HistoryIndex) ws.UsedRange.Value
End SubSub Undo(ws As Worksheet) 检查是否有历史记录可以撤销If HistoryIndex 0 ThenMsgBox No actions to undo., vbInformationExit SubEnd If 检查是否有保存的历史状态If IsEmpty(History(HistoryIndex)) ThenMsgBox No history state to apply., vbInformationHistoryIndex HistoryIndex - 1 减少索引防止重复警告If HistoryIndex 0 Then HistoryIndex 0 确保索引不会变成负数Exit SubEnd If 应用历史状态ws.UsedRange.Value History(HistoryIndex)History(HistoryIndex) Empty 清除已经使用的历史记录 更新历史索引为下一次撤销做准备HistoryIndex HistoryIndex - 1If HistoryIndex 0 Then HistoryIndex 0 确保索引不会变成负数
End SubSub AutoFitColumns(ws As Worksheet)ws.Cells.EntireColumn.AutoFit
End SubSub CenterAlign(ws As Worksheet)ws.Cells.HorizontalAlignment xlCenterws.Cells.VerticalAlignment xlCenter
End SubSub ApplyDataBars(ws As Worksheet)Dim lastCol As IntegerDim lastRow As IntegerDim col As IntegerDim cell As RangelastCol ws.Cells(1, ws.Columns.Count).End(xlToLeft).ColumnlastRow ws.Cells(ws.Rows.Count, 1).End(xlUp).RowFor col 2 To lastColFor Each cell In ws.Range(ws.Cells(2, col), ws.Cells(lastRow, col))If IsEmpty(cell.Value) Then cell.Value 0Next cellWith ws.Range(ws.Cells(2, col), ws.Cells(lastRow, col)).FormatConditions.AddDatabarWith .FormatConditions(.FormatConditions.Count).BarColor.Color RGB(155, 194, 230).BarFillType xlDataBarFillGradient.Direction xlContext.ShowValue TrueEnd WithEnd WithNext col
End SubSub 数据处理工具箱()UserForm1.Show
End SubUserForm1
Private Sub InitializeHistory()Dim i As IntegerFor i 1 To 5History(i) EmptyNext iHistoryIndex 0
End SubPrivate Sub Button_Execute_Click()Call InitializeHistoryDim ws As WorksheetSet ws ActiveSheetIf CheckBox_AutoWidth.Value True ThenCall AutoFitColumns(ws)SaveCurrentState ActiveSheetEnd IfIf CheckBox_CenterAlign.Value True ThenCall CenterAlign(ws)SaveCurrentState ActiveSheetEnd IfIf CheckBox_DataBars.Value True ThenCall ApplyDataBars(ws)SaveCurrentState ActiveSheetEnd If
End SubPrivate Sub Button_Undo_Click()Undo ActiveSheet
End Sub 最后导出模块以便共享 Pandas方法 参考代码
excel_file fdataset_statistics_{use_model}.xlsx
with pd.ExcelWriter(excel_file, enginexlsxwriter) as writer:df.to_excel(writer, indexTrue, sheet_nameSheet1)workbook writer.bookworksheet 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宏的结果好看。
