等級:初學(xué)者
-
積分:6
-
財富值:49.27
-
身份:普通用戶
以Sheet2工作表第一組（第2、第3、第4行）數(shù)據(jù)為例，邏輯步驟如下：
說明：Sheet1工作表A列數(shù)據(jù)為不重復(fù)的編號數(shù)據(jù)。
1、根據(jù)Sheet2工作表A列A2單元格里的條件，篩選出Sheet1工作表B列符合條件的數(shù)據(jù)；（B列數(shù)據(jù)總共有7類："BJCK"、“GZCK”、“HKCK”、“NJCK”、“SHCK”、“SZCK”、“WHCK”）；
2、根據(jù)Sheet2工作表B列B2單元格里的條件，篩選出Sheet1工作表C列符合條件的數(shù)據(jù)；（C列數(shù)據(jù)總共有7類："A3"、“B2、“D1”、“S1”、“V1”、“Y1”、“Y2”）；
3、根據(jù)Sheet2工作表C列C2單元格里的條件，篩選出Sheet1工作表D列符合條件（此處舉例“自營電商”）的數(shù)據(jù)，接著繼續(xù)篩選出Sheet1工作表E列“已出庫”的數(shù)據(jù)；（D列數(shù)據(jù)總共有3類："自營電商"、“合作代理商”、“線下旗艦店”）；
4、第2行數(shù)據(jù)：根據(jù)Sheet2工作表D1單元格里的條件，篩選出Sheet1工作表F列符合條件的數(shù)據(jù)，再以Sheet1工作表上述篩選的A列數(shù)據(jù)進行計數(shù)，將計數(shù)數(shù)量寫入Sheet2工作表D1到O1日期下方對應(yīng)的第2行單元格。
5、第3行數(shù)據(jù)：取消Sheet1工作表E列的篩選，以Sheet1工作表上述篩選的A列數(shù)據(jù)進行計數(shù)，將計數(shù)數(shù)量寫入Sheet2工作表D1到O1日期下方對應(yīng)的第3行單元格。
6、第4行數(shù)據(jù)：使用（總數(shù)-出庫數(shù)）/總數(shù)，得出出庫率，將出庫率寫入Sheet2工作表D1到O1日期下方對應(yīng)的第4行單元格。
等級:傳說級人物
等級:學(xué)有小成-
積分:23
-
財富值:2
-
身份:普通用戶
```vba
Sub FilterAndCountData()
Dim ws1 As Worksheet ' Sheet1
Dim ws2 As Worksheet ' Sheet2
Dim rng1 As Range ' Sheet1數(shù)據(jù)范圍
Dim rng2 As Range ' Sheet2數(shù)據(jù)范圍
Dim filterValue1 As String ' Sheet2 A列條件值
Dim filterValue2 As String ' Sheet2 B列條件值
Dim filterValue3 As String ' Sheet2 C列條件值
Dim filterValue4 As String ' Sheet2 D1單元格條件值
Dim countValue As Long ' 計數(shù)值
Dim totalCount As Long ' 總數(shù)
Dim outCount As Long ' 出庫數(shù)
Dim outRate As Double ' 出庫率
Dim i As Long ' 循環(huán)變量
' 設(shè)置工作表對象
Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")
' 獲取Sheet1數(shù)據(jù)范圍
Set rng1 = ws1.Range("A1").CurrentRegion
' 獲取Sheet2數(shù)據(jù)范圍
Set rng2 = ws2.Range("A2:C4")
' 遍歷Sheet2第一組數(shù)據(jù)
For i = 2 To 4
' 獲取條件值
filterValue1 = rng2.Cells(i, 1).Value
filterValue2 = rng2.Cells(i, 2).Value
filterValue3 = rng2.Cells(i, 3).Value
filterValue4 = rng2.Cells(i, 4).Value
' 篩選Sheet1數(shù)據(jù)
rng1.AutoFilter Field:=2, Criteria1:=filterValue1
rng1.AutoFilter Field:=3, Criteria1:=filterValue2
rng1.AutoFilter Field:=4, Criteria1:=filterValue3
rng1.AutoFilter Field:=5, Criteria1:=filterValue4 & "*"
' 計算總數(shù)
totalCount = rng1.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
' 計算出庫數(shù)
outCount = rng1.Columns(5).SpecialCells(xlCellTypeVisible).Count - 1
' 計算出庫率
If totalCount > 0 Then
outRate = (totalCount - outCount) / totalCount
Else
outRate = 0
End If
' 將計數(shù)值和出庫率寫入Sheet2
ws2.Cells(2, i + 3).Value = totalCount
ws2.Cells(3, i + 3).Value = outCount
ws2.Cells(4, i + 3).Value = outRate
Next i
' 清除篩選
rng1.AutoFilter
' 清除對象引用
Set rng1 = Nothing
Set rng2 = Nothing
Set ws1 = Nothing
Set ws2 = Nothing
End Sub
```
請注意，上述代碼僅供參考，你可能需要根據(jù)實際情況進行調(diào)整和補充。在運行代碼之前，請確保工作簿中存在名為"Sheet1"和"Sheet2"的工作表，并按照你的描述在Sheet2的A2:C4范圍內(nèi)填入相應(yīng)的條件值。