xls

基本操作

# 将文本格式的数字转换为实际的数字
=IFERROR(ABS(VALUE(A1))*4, "无效数据")

# VALUE函数用于将文本转换为数字
=VALUE(SUBSTITUTE(A1,"[^0-9.]",""))

# 去除多余空格
=trim("联系人")

# 文本 -> 日期带时间到秒
# 45761.3622 -> 2025-04-14 08:41:34 
=TEXT(A1, "yyyy-mm-dd hh:mm:ss")

批量编辑

调整行列

image.png

设置单元格格式

image.png

设置小数点后3位

image.png

单列

批次

image.png

除100

image.png

多列

拆分多列

image.png

数据到单列

image.png

除100

image.png

合并多列

image.png

排版

image.png

条件格式

在Excel中设置根据数值范围自动显示颜色的方法如下（以>85%显示红色，<50%显示绿色，其他显示红色为例）：

选择数据范围
选中需要设置格式的单元格区域（如B2:B10）。

设置>85%的红色规则

点击【开始】→【条件格式】→【新建规则】
选择【基于各自值设置单元格格式】
设置条件：单元格值 > 0.85 → 点击【格式】→ 字体颜色选红色 → 确定

设置<50%的绿色规则
再次点击【新建规则】
选择【单元格值 < 0.5】→ 格式设为绿色字体 → 确定

设置其他值规则
添加第三条规则：单元格值 <= 0.85 且 >= 0.5 → 格式设为红色字体

警戒库存

# 统计销量
一年 hg【20230101~20241231】，
半年 hb【20240201~20240731】

# 关键指标
每日销量：H2/360，H2/180
最低：每日销量 * 7
最高：每日销量 * 35

# excel 公式
# 向上取整
=ROUND(A1, 0)

# 文本相减
=RIGHT(D2,LEN(D2)-LEN(C2))

# [-0.50-0.75] -> [-0.50] [-0.75]
=LEFT(A1,FIND("-",A1,FIND("-",A1)+1)-1)
=MID(A1,FIND("-",A1,FIND("-",A1)+1),LEN(A1))

# [0.00-0.75] -> [0.00] [-0.75]
=LEFT(A1, FIND("-",A1) - 1)
=RIGHT(A1, LEN(A1) - FIND("-",A1))

# [+0.75-0.75] -> [+0.75] [-0.75]
=LEFT(A1,FIND("-",A1) - 1)
=RIGHT(A1,LEN(A1) - FIND("-",A1)+1)

佣金

# 总销售：(成品销售 + 车房销售 + 加工销售) - 退货 - 返利 - 折扣
O =SUM(F2:H2)-J2-L2-N5

# 总回款：回款 + 其他回款
P =K2+M5

# 抵扣比例：
• 如果 P2或O2为0，结果为0。
• 如果 0< P2/O2 < 1，结果为该比例保留两位小数。
• 如果 P2/O2 < 0 或 P2/O2 > 1，结果为1。
= IF(OR(Q2=0, P2 = 0), 0, IF(Q2/P2 < 1, IF(Q2/P2 < 0, 1, ROUND(Q2/P2,2)), 1))

VBA

# 宏开发模式
alt + F11 

# 保存
.xlsx 保存后，VBA的 .bas 文件丢失
.xlsm 才可以保存

计算

Sub CalculateAllCommission()

    '30s
    
    Dim startTime As Date
    Dim endTime As Date
    Dim executionTime As Double
    
    startTime = Now '记录开始时间
    
    '执行程序 - - - - - - - -
    
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long, j As Long
    
    'Rule
    Set ws = ThisWorkbook.Sheets("Rule")
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
        
    '范围区间
    Dim jp As Integer
    jp = 28
    
    Dim jj As Integer
    jj = 34
    
    '遍历月份
    For m = 2 To ThisWorkbook.Sheets.Count
        
        With ThisWorkbook.Sheets(m)
            
            Debug.Print ThisWorkbook.Sheets(m).Name
            
            Dim lastRow1 As Long
            
            Dim lastColumn1 As Long
            
            'month
            lastRow1 = .Cells(.Rows.Count, 1).End(xlUp).Row
            lastColumn1 = .Cells(lastRow1, Columns.Count).End(xlToLeft).Column
            
            ratioColumn = 24
            commissionColumn = 25
            
            '新增2列
            .Cells(1, ratioColumn).Value = "比例"
            .Cells(1, commissionColumn).Value = "佣金"
        
            '遍历明细
            For i = 2 To lastRow1 '数据从第2行开始
            
                '跳过 [加工订单, TS商户, 铺货订单, 赠品订单]
                If .Cells(i, "G").Value = "加工" Or InStr(.Cells(i, "A").Value, "TS") > 0 Or .Cells(i, "K").Value = "铺货订单" Or .Cells(i, "K").Value = "赠品订单" Then
                    
                    .Cells(i, ratioColumn).Value = ""
                    .Cells(i, commissionColumn).Value = ""
                    
                    GoTo NextIteration
                End If
                
                If .Cells(i, "M").Value <> "" And .Cells(i, "N").Value <> "" Then
                    '镜架  [型号 色号] 不为空
                    For j = jp + 1 To jj
                        '遍历Rule, 品牌前缀相同
                        'If Left(.Cells(i, "H").Value, 2) = Left(ws.Cells(j, "B").Value, 2) Then
                        If LCase(Left(.Cells(i, "H").Value, 2)) = LCase(Left(ws.Cells(j, "B").Value, 2)) Then
                            '比例赋值
                            .Cells(i, ratioColumn).Value = ws.Cells(j, "C").Value
                            '佣金计算
                            .Cells(i, commissionColumn).Value = Round(.Cells(i, 22).Value * .Cells(i, ratioColumn).Value, 2)
                            Exit For
                
                        End If
                            Next j
                    
                Else
                     '镜片
                    For j = 2 To jp
                        '遍历Rule
                        'If Left(.Cells(i, "H").Value, 2) = Left(ws.Cells(j, "B").Value, 2) Then
                        If LCase(Left(.Cells(i, "H").Value, 2)) = LCase(Left(ws.Cells(j, "B").Value, 2)) Then
                            
                            .Cells(i, ratioColumn).Value = ws.Cells(j, "C").Value
                            .Cells(i, commissionColumn).Value = Round(.Cells(i, 22).Value * .Cells(i, ratioColumn).Value, 2)
                            Exit For
                        End If
                    Next j
                End If
        
NextIteration:
        Next i
        
        End With
    Next m
    
    '执行完毕 - - - - - - - -
    endTime = Now '记录结束时间
    executionTime = DateDiff("s", startTime, endTime) '计算执行时间（以秒为单位）
    
    MsgBox "程序执行时间为：" & executionTime & " 秒。"
    
    
End Sub

汇总

Sub SumCommissionByName(saleName As String)

    '30s
    
    Dim startTime As Date
    Dim endTime As Date
    Dim executionTime As Double
    
    startTime = Now '记录开始时间
    
    '执行程序 - - - - - - - -
    
    Debug.Print "佣金计算：" & saleName
    
    Dim i As Long
    Dim sumValue As Double
    sumValue = 0
    
    Dim m As Integer
    '跳过Rule,从第二个工作表进行操作
    For m = 2 To ThisWorkbook.Sheets.Count
        
        With ThisWorkbook.Sheets(m)
            
            Dim lastRow As Long
            Dim monthValue As Double
            
            lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
            monthValue = 0
            
            For i = 1 To lastRow
                If .Cells(i, "C").Value = saleName Then
                    monthValue = monthValue + .Cells(i, "X").Value
                End If
            Next i
        End With
        
        Debug.Print ThisWorkbook.Sheets(m).Name & "：" & monthValue
        sumValue = sumValue + monthValue
    Next m
    
    Debug.Print "总和：" & sumValue
    
    
    '执行完毕 - - - - - - - -
    endTime = Now '记录结束时间
    executionTime = DateDiff("s", startTime, endTime) '计算执行时间（以秒为单位）
    
    'Debug.Print "程序执行时间为：" & executionTime & " 秒。"
    
End Sub


Sub GetSaleCommission()
    Dim myArray() As Variant
    myArray = Array("陈x", "谢x", "沈x", "王x") '创建数组
    Dim element As Variant
    For Each element In myArray
        'Debug.Print element
        SumCommissionByName (element)
    Next element
End Sub

折扣

=IFERROR(INDEX(Sheet2!E:E,MATCH(1,(Sheet2!B:B=B2)*(Sheet2!C:C=C2),0)),1)