一条简洁的金额大写函数

=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A1,2),TEXT(A1,";负")&TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A1,2),2),"[dbnum2]0角0分;;整"),),"零角",IF(A1^2<1,,"零")),"零分","整")

利用上述函数可大写 A1 单元格金额,减轻手动输入的痛苦。

一条简洁的个税函数

=ROUND(MAX((D3-3500)*{3;10;20;25;30;35;45}%-5*{0;21;111;201;551;1101;2701},0),2)

求取D3单元格个税,太神奇了!

数据透视表批量添加求和项

Sub AddAllFieldsValues()

Dim I As Long
Dim pt As PivotTable
Dim ptField As PivotField
    
Application.ScreenUpdating = False

For Each pt In ActiveSheet.PivotTables
        
    For I = 1 To pt.PivotFields.Count
            
    With pt.PivotFields(I)
              
        If .Orientation = 0 Then .Orientation = xlDataField
        
    End With
        
    Next
    
For Each ptField In pt.DataFields
    
    With ptField
    
        .Function = xlSum
        .Caption = "求和项:" & .SourceName
         
    End With
     
    Next
Next
     
Application.ScreenUpdating = True

End Sub