一条简洁的金额大写函数

=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单元格个税，太神奇了！

身份证号码校验

=IF(LEN(A3)=18,IF(LOOKUP(MOD(MID(A3,1,1)*7+MID(A3,2,1)*9+MID(A3,3,1)*10+MID(A3,4,1)*5+MID(A3,5,1)*8+MID(A3,6,1)*4+MID(A3,7,1)*2+MID(A3,8,1)*1+MID(A3,9,1)*6+MID(A3,10,1)*3+MID(A3,11,1)*7+MID(A3,12,1)*9+MID(A3,13,1)*10+MID(A3,14,1)*5+MID(A3,15,1)*8+MID(A3,16,1)*4+MID(A3,17,1)*2,11),{0,1,2,3,4,5,6,7,8,9,10},{"1","0","X","9","8","7","6","5","4","3","2"})=MID(A3,18,1),"正确","身份号码错误"),"非18位身份证号码")

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

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