Public wb_data As Excel.Workbook
Sub test()
result = Array("G18", "G32", "G42", "G45", "G56", "G58", "G71", "N13", "N16", "N17", "N18", "N31", "N33", "N42", "N44", "N46", "N47", "N48", "N60", "N61", "N62", "N63", "N64", "N65")
mole_arr = Array(16, 8, 119, 125, 29, 160, 95, 152, 143, 146, 149, 11, 248, 190, 86, 122, 125, 89, 190, 222, 218, 226, 230, 234) '分子
deno_arr = Array(163, 5, 182, 182, 5, 168, 98, 137, 140, 140, 140, 5, 242, 186, 83, 182, 182, 80, 186, 186, 186, 186, 186, 186) '分母
fix_result = Array("G11", "G13", "G21", "G22", "G48", "G59", "G60", "G61", "G64", "G66", "G75", "N22", "N24", "N28", "N29")
fix_arr = Array(176, 140, 137, 50, 80, 2, 182, 198, 202, 104, 26, 50, 56, 41, 38)
Dim cell_result As Variant, mole_result As Variant, deno_result As Variant, single_result As Variant, mole_result_1 As Variant, mole_result_2 As Variant, deno_result_1 As Variant, deno_result_2 As Variant, deno_result_3 As Variant, deno_result_4 As Variant
Dim path_add As String, file_name As String, file_suffix As String, file_add As String
path_add = "C:\Users\mikowoo\Desktop\EPSreport\result\"
fix_suffix = ".xlsx"
'Dim wb_data As Excel.Workbook
Set wb_data = Workbooks.Open("C:\Users\mikowoo\Desktop\EPSreport\data.xlsx")
For x = 5 To 7
file_name = wb_data.Sheets(1).Cells(x, 1).Value
ThisWorkbook.Sheets(3).Range("I2").Value = wb_data.Sheets(1).Cells(x, 1).Value
'正常两组数据相除
len_array = UBound(result) - LBound(result) '+1
For y = 0 To len_array '-1
Select Case True
Case wb_data.Sheets(1).Cells(x, mole_arr(y)).Value = "" And wb_data.Sheets(1).Cells(x, mole_arr(y) + 1).Value <> "" And wb_data.Sheets(1).Cells(x, mole_arr(y) + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, mole_arr(y) + 1).Value + wb_data.Sheets(1).Cells(x, mole_arr(y) + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, mole_arr(y)).Value <> "" And wb_data.Sheets(1).Cells(x, mole_arr(y) + 1).Value = "" And wb_data.Sheets(1).Cells(x, mole_arr(y) + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, mole_arr(y)).Value + wb_data.Sheets(1).Cells(x, mole_arr(y) + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, mole_arr(y)).Value <> "" And wb_data.Sheets(1).Cells(x, mole_arr(y) + 1).Value <> "" And wb_data.Sheets(1).Cells(x, mole_arr(y) + 2).Value = ""
mole_result = (wb_data.Sheets(1).Cells(x, mole_arr(y)).Value + wb_data.Sheets(1).Cells(x, mole_arr(y) + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, mole_arr(y)).Value = "" And wb_data.Sheets(1).Cells(x, mole_arr(y) + 1).Value = "" And wb_data.Sheets(1).Cells(x, mole_arr(y) + 2).Value <> ""
mole_result = wb_data.Sheets(1).Cells(x, mole_arr(y) + 2).Value
Case wb_data.Sheets(1).Cells(x, mole_arr(y)).Value <> "" And wb_data.Sheets(1).Cells(x, mole_arr(y) + 1).Value = "" And wb_data.Sheets(1).Cells(x, mole_arr(y) + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, mole_arr(y)).Value
Case wb_data.Sheets(1).Cells(x, mole_arr(y)).Value = "" And wb_data.Sheets(1).Cells(x, mole_arr(y) + 1).Value <> "" And wb_data.Sheets(1).Cells(x, mole_arr(y) + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, mole_arr(y) + 1).Value
Case wb_data.Sheets(1).Cells(x, mole_arr(y)).Value = "" And wb_data.Sheets(1).Cells(x, mole_arr(y) + 1).Value = "" And wb_data.Sheets(1).Cells(x, mole_arr(y) + 2).Value = ""
mole_result = "没有数据"
Case wb_data.Sheets(1).Cells(x, mole_arr(y)).Value <> "" And wb_data.Sheets(1).Cells(x, mole_arr(y) + 1).Value <> "" And wb_data.Sheets(1).Cells(x, mole_arr(y) + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, mole_arr(y)).Value + wb_data.Sheets(1).Cells(x, mole_arr(y) + 1).Value + wb_data.Sheets(1).Cells(x, mole_arr(y) + 2).Value) / 3
End Select
Select Case True
Case wb_data.Sheets(1).Cells(x, deno_arr(y)).Value = "" And wb_data.Sheets(1).Cells(x, deno_arr(y) + 1).Value <> "" And wb_data.Sheets(1).Cells(x, deno_arr(y) + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, deno_arr(y) + 1).Value + wb_data.Sheets(1).Cells(x, deno_arr(y) + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, deno_arr(y)).Value <> "" And wb_data.Sheets(1).Cells(x, deno_arr(y) + 1).Value = "" And wb_data.Sheets(1).Cells(x, deno_arr(y) + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, deno_arr(y)).Value + wb_data.Sheets(1).Cells(x, deno_arr(y) + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, deno_arr(y)).Value <> "" And wb_data.Sheets(1).Cells(x, deno_arr(y) + 1).Value <> "" And wb_data.Sheets(1).Cells(x, deno_arr(y) + 2).Value = ""
deno_result = (wb_data.Sheets(1).Cells(x, deno_arr(y)).Value + wb_data.Sheets(1).Cells(x, deno_arr(y) + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, deno_arr(y)).Value = "" And wb_data.Sheets(1).Cells(x, deno_arr(y) + 1).Value = "" And wb_data.Sheets(1).Cells(x, deno_arr(y) + 2).Value <> ""
deno_result = wb_data.Sheets(1).Cells(x, deno_arr(y) + 2).Value
Case wb_data.Sheets(1).Cells(x, deno_arr(y)).Value <> "" And wb_data.Sheets(1).Cells(x, deno_arr(y) + 1).Value = "" And wb_data.Sheets(1).Cells(x, deno_arr(y) + 2).Value = ""
deno_result = wb_data.Sheets(1).Cells(x, deno_arr(y)).Value
Case wb_data.Sheets(1).Cells(x, deno_arr(y)).Value = "" And wb_data.Sheets(1).Cells(x, deno_arr(y) + 1).Value <> "" And wb_data.Sheets(1).Cells(x, deno_arr(y) + 2).Value = ""
deno_result = wb_data.Sheets(1).Cells(x, deno_arr(y) + 1).Value
Case wb_data.Sheets(1).Cells(x, deno_arr(y)).Value = "" And wb_data.Sheets(1).Cells(x, deno_arr(y) + 1).Value = "" And wb_data.Sheets(1).Cells(x, deno_arr(y) + 2).Value = ""
deno_result = "没有数据"
Case wb_data.Sheets(1).Cells(x, deno_arr(y)).Value <> "" And wb_data.Sheets(1).Cells(x, deno_arr(y) + 1).Value <> "" And wb_data.Sheets(1).Cells(x, deno_arr(y) + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, deno_arr(y)).Value + wb_data.Sheets(1).Cells(x, deno_arr(y) + 1).Value + wb_data.Sheets(1).Cells(x, deno_arr(y) + 2).Value) / 3
End Select
If mole_result = "没有数据" Or deno_result = "没有数据" Then
cell_result = "缺失数据无法得出结果"
Else
cell_result = mole_result / deno_result
End If
ThisWorkbook.Sheets(3).Range(result(y)).Value = cell_result
'mole_result = (wb_data.Sheets(1).Cells(x, mole_arr(y)).Value + wb_data.Sheets(1).Cells(x, mole_arr(y) + 1).Value + wb_data.Sheets(1).Cells(x, mole_arr(y) + 2).Value) / 3
'deno_result = (wb_data.Sheets(1).Cells(x, deno_arr(y)).Value + wb_data.Sheets(1).Cells(x, deno_arr(y) + 1).Value + wb_data.Sheets(1).Cells(x, deno_arr(y) + 2).Value) / 3
'cell_result = mole_result / deno_result
Next y
'三个数据求平均数
len_array = UBound(fix_result) - LBound(fix_result) '+1
For Z = 0 To len_array
Select Case True
Case wb_data.Sheets(1).Cells(x, fix_arr(Z)).Value = "" And wb_data.Sheets(1).Cells(x, fix_arr(Z) + 1).Value <> "" And wb_data.Sheets(1).Cells(x, fix_arr(Z) + 2).Value <> ""
single_result = (wb_data.Sheets(1).Cells(x, fix_arr(Z) + 1).Value + wb_data.Sheets(1).Cells(x, fix_arr(Z) + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, fix_arr(Z)).Value <> "" And wb_data.Sheets(1).Cells(x, fix_arr(Z) + 1).Value = "" And wb_data.Sheets(1).Cells(x, fix_arr(Z) + 2).Value <> ""
single_result = (wb_data.Sheets(1).Cells(x, fix_arr(Z)).Value + wb_data.Sheets(1).Cells(x, fix_arr(Z) + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, fix_arr(Z)).Value <> "" And wb_data.Sheets(1).Cells(x, fix_arr(Z) + 1).Value <> "" And wb_data.Sheets(1).Cells(x, fix_arr(Z) + 2).Value = ""
single_result = (wb_data.Sheets(1).Cells(x, fix_arr(Z)).Value + wb_data.Sheets(1).Cells(x, fix_arr(Z) + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, fix_arr(Z)).Value = "" And wb_data.Sheets(1).Cells(x, fix_arr(Z) + 1).Value = "" And wb_data.Sheets(1).Cells(x, fix_arr(Z) + 2).Value <> ""
single_result = wb_data.Sheets(1).Cells(x, fix_arr(Z) + 2).Value
Case wb_data.Sheets(1).Cells(x, fix_arr(Z)).Value <> "" And wb_data.Sheets(1).Cells(x, fix_arr(Z) + 1).Value = "" And wb_data.Sheets(1).Cells(x, fix_arr(Z) + 2).Value = ""
single_result = wb_data.Sheets(1).Cells(x, fix_arr(Z)).Value
Case wb_data.Sheets(1).Cells(x, fix_arr(Z)).Value = "" And wb_data.Sheets(1).Cells(x, fix_arr(Z) + 1).Value <> "" And wb_data.Sheets(1).Cells(x, fix_arr(Z) + 2).Value = ""
single_result = wb_data.Sheets(1).Cells(x, fix_arr(Z) + 1).Value
Case wb_data.Sheets(1).Cells(x, fix_arr(Z)).Value = "" And wb_data.Sheets(1).Cells(x, fix_arr(Z) + 1).Value = "" And wb_data.Sheets(1).Cells(x, fix_arr(Z) + 2).Value = ""
single_result = "没有数据"
Case wb_data.Sheets(1).Cells(x, fix_arr(Z)).Value <> "" And wb_data.Sheets(1).Cells(x, fix_arr(Z) + 1).Value <> "" And wb_data.Sheets(1).Cells(x, fix_arr(Z) + 2).Value <> ""
single_result = (wb_data.Sheets(1).Cells(x, fix_arr(Z)).Value + wb_data.Sheets(1).Cells(x, fix_arr(Z) + 1).Value + wb_data.Sheets(1).Cells(x, fix_arr(Z) + 2).Value) / 3
End Select
If single_result = "没有数据" Then
cell_result = "缺失数据无法得出结果"
Else
cell_result = single_result
End If
ThisWorkbook.Sheets(3).Range(fix_result(Z)).Value = cell_result
Next Z
'单一值
ThisWorkbook.Sheets(3).Range("G12") = wb_data.Sheets(1).Cells(x, 178).Value
'三包外客户占比
If ThisWorkbook.Sheets(3).Range("N13").Value = "缺失数据无法得出结果" Then
ThisWorkbook.Sheets(3).Range("N14").Value = "缺失数据无法得出结果"
Else
ThisWorkbook.Sheets(3).Range("N14").Value = 1 - ThisWorkbook.Sheets(3).Range("N13").Value
End If
Call Ratio_1(x)
Call Ratio_2(x)
Call Ratio_3(x)
Call Ratio_4(x)
Call Ratio_5(x)
Call Ratio_6(x)
Call Ratio_7(x)
Call Ratio_8(x)
Call Ratio_9(x)
Call Ratio_10(x)
Call Ratio_11(x)
Call Ratio_12(x)
Call Ratio_13(x)
'文件命名
file_add = path_add + file_name + file_suffix
Application.DisplayAlerts = False ' 取消提示“无法在未启用宏的工作簿中保存一下功能:”
ThisWorkbook.SaveAs (file_add)
Debug.Print "第" & "个经销店" & file_name & "处理完毕"
Next x
'ThisWorkbook.Sheets(3).Range("G11").Value = wb_data.Sheets(3).Range(mole_arr(0)).Value / wb_data.Sheets(3).Range(deno_arr(0)).Value
wb_data.Close
ThisWorkbook.Close
End Sub
Sub Ratio_1(x)
'客户粘度系数
y = 182
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 140
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
If mole_result = "没有数据" Or deno_result = "没有数据" Then
cell_result = "缺失数据无法得出结果"
Else
cell_result = (mole_result * 12) / deno_result
End If
ThisWorkbook.Sheets(3).Range("G16").Value = cell_result
End Sub
Sub Ratio_2(x)
'首次到店客户量--可作为单独求值得模板
y = 5
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 8
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
If mole_result = "没有数据" Or deno_result = "没有数据" Then
cell_result = "缺失数据无法得出结果"
Else
cell_result = mole_result - deno_result
End If
ThisWorkbook.Sheets(3).Range("G27").Value = cell_result
End Sub
Sub Ratio_3(x)
'活动线索总量
y = 38
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 41
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
If mole_result = "没有数据" Or deno_result = "没有数据" Then
cell_result = "缺失数据无法得出结果"
Else
cell_result = mole_result + deno_result
End If
ThisWorkbook.Sheets(3).Range("G28").Value = cell_result
End Sub
Sub Ratio_4(x)
'市场活动频次
y = 32
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 35
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
If mole_result = "没有数据" Or deno_result = "没有数据" Then
cell_result = "缺失数据无法得出结果"
Else
cell_result = mole_result + deno_result
End If
ThisWorkbook.Sheets(3).Range("G30").Value = cell_result
End Sub
Sub Ratio_5(x)
'首次到店客流占比
y = 5
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_1 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_1 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_1 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_1 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_1 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 8
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_2 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_2 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_2 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_2 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_2 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 5
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
If mole_result_1 = "没有数据" Or mole_result_2 = "没有数据" Or deno_result = "没有数据" Then
cell_result = "缺失数据无法得出结果"
Else
cell_result = (mole_result_1 - mole_result_2) / deno_result
End If
ThisWorkbook.Sheets(3).Range("G31").Value = cell_result
End Sub
Sub Ratio_6(x)
'成交率
y = 160
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_1 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_1 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_1 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_1 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_1 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 164
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_2 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_2 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_2 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_2 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_2 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 38
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 41
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 50
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_3 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_3 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_3 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_3 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_3 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_3 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_3 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_3 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 62
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_4 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_4 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_4 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_4 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_4 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_4 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_4 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_4 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
If mole_result_1 = "没有数据" Or mole_result_2 = "没有数据" Or deno_result_1 = "没有数据" Or deno_result_2 = "没有数据" Or deno_result_3 = "没有数据" Or deno_result_4 = "没有数据" Then
cell_result = "缺失数据无法得出结果"
Else
cell_result = (mole_result_1 + mole_result_2) / (deno_result_1 + deno_result_2 + deno_result_3 + deno_result_4)
End If
ThisWorkbook.Sheets(3).Range("G35").Value = cell_result
End Sub
Sub Ratio_7(x)
'网店销占比
y = 44
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 160
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 164
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
If mole_result = "没有数据" Or deno_result_1 = "没有数据" Or deno_result_2 = "没有数据" Then
cell_result = "缺失数据无法得出结果"
Else
cell_result = mole_result / (deno_result_1 + deno_result_2)
End If
ThisWorkbook.Sheets(3).Range("G39").Value = cell_result
End Sub
Sub Ratio_8(x)
'二网销售占比
y = 257
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_1 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_1 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_1 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_1 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_1 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 260
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_2 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_2 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_2 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_2 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result_2 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 160
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 164
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
If mole_result_1 = "没有数据" Or mole_result_2 = "没有数据" Or deno_result_1 = "没有数据" Or deno_result_2 = "没有数据" Then
cell_result = "缺失数据无法得出结果"
Else
cell_result = (mole_result_1 + mole_result_2) / (deno_result_1 + deno_result_2)
End If
ThisWorkbook.Sheets(3).Range("G40").Value = cell_result
End Sub
Sub Ratio_9(x)
'新保渗透率
y = 101
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 160
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 164
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
If mole_result = "没有数据" Or deno_result_1 = "没有数据" Or deno_result_2 = "没有数据" Then
cell_result = "缺失数据无法得出结果"
Else
cell_result = mole_result / (deno_result_1 + deno_result_2)
End If
ThisWorkbook.Sheets(3).Range("G74").Value = cell_result
End Sub
Sub Ratio_10(x)
'保有客户推荐率
y = 71
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 160
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 164
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
If mole_result = "没有数据" Or deno_result_1 = "没有数据" Or deno_result_2 = "没有数据" Then
cell_result = "缺失数据无法得出结果"
Else
cell_result = mole_result / (deno_result_1 + deno_result_2)
End If
ThisWorkbook.Sheets(3).Range("N19").Value = cell_result
End Sub
Sub Ratio_11(x)
'新增线索到店转化率
y = 53
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 160
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 164
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
If mole_result = "没有数据" Or deno_result_1 = "没有数据" Or deno_result_2 = "没有数据" Then
cell_result = "缺失数据无法得出结果"
Else
cell_result = mole_result / (deno_result_1 + deno_result_2)
End If
ThisWorkbook.Sheets(3).Range("N23").Value = cell_result
End Sub
Sub Ratio_12(x)
'工位周转率
y = 182
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 107
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
If mole_result = "没有数据" Or deno_result = "没有数据" Then
cell_result = "缺失数据无法得出结果"
Else
mole_result = mole_result / 30
cell_result = mole_result / deno_result
End If
ThisWorkbook.Sheets(3).Range("N43").Value = cell_result
End Sub
Sub Ratio_13(x)
' 新保战败率
y = 101
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
mole_result = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
mole_result = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 160
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_1 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_1 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
y = 164
Select Case True
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value) / 2
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y + 2).Value
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = wb_data.Sheets(1).Cells(x, y + 1).Value
Case wb_data.Sheets(1).Cells(x, y).Value = "" And wb_data.Sheets(1).Cells(x, y + 1).Value = "" And wb_data.Sheets(1).Cells(x, y + 2).Value = ""
deno_result_2 = "没有数据"
Case wb_data.Sheets(1).Cells(x, y).Value <> "" And wb_data.Sheets(1).Cells(x, y + 1).Value <> "" And wb_data.Sheets(1).Cells(x, y + 2).Value <> ""
deno_result_2 = (wb_data.Sheets(1).Cells(x, y).Value + wb_data.Sheets(1).Cells(x, y + 1).Value + wb_data.Sheets(1).Cells(x, y + 2).Value) / 3
End Select
If mole_result = "没有数据" Or deno_result_1 = "没有数据" Or deno_result_2 = "没有数据" Then
cell_result = "缺失数据无法得出结果"
Else
cell_result = 1 - mole_result / (deno_result_1 + deno_result_2)
End If
ThisWorkbook.Sheets(3).Range("N74").Value = cell_result
End Sub
VBA-- 从汇总表中求指标值_并赋值给每张报表(1)
最新推荐文章于 2023-03-16 10:55:15 发布