iT邦幫忙

0

excel依日期30天內檢查重覆值

https://ithelp.ithome.com.tw/upload/images/20200926/201092319PEHQg8vl6.jpg

說明:

  1. A:D欄均為人工每日KEY入資料
  2. E欄查核條件如下:
    條件1:如A欄日期為電腦時間往前推30天內(含30天),D欄店家名稱首次出現為空白,但出現第二次則顯示"重覆1",出現第三次則顯示"重覆2",以此類推...
    條件2:計算D欄重覆次數僅計算電腦時間往前推30天內(含30天),故以當日為準如A欄日期超過31天,則該列D欄店家不列入計算,
       例如D欄店家『首幅』共出現4次,但今日為9/26,往前推30天內為8/28~9/26,故8/27第一次出現則不列入計算。
       『首幅』第一次為9/10、第二次為9/11、第三次為9/17,故E欄查核結果依序為【空白】、【重覆1】、【重覆2】

提醒:
E欄查核結果會依照A欄日期是否為當天往前推30天內(含30天),判斷是否列入計算。
如上例店家『首幅』,如之後未再出現,則等到10/10當天,往前推30天內為9/11~10/10,故9/10第一次出現將不列入計算
,而9/11第一次出現則改顯示為空白、9/17第二次則改顯示為重覆1,以此類推...

E欄查核公式是我目前想到破頭的可能方向,但是錯誤的喔!!!因為驗證結果失敗
所以可能要再請大師們幫幫忙了~~~

測試檔
https://drive.google.com/file/d/1M2hYtaanFctihTRAyV3XuIBrBgR1poLU/view?usp=sharing

wrxue iT邦新手 2 級 ‧ 2020-09-26 15:44:52 檢舉
不考慮用vba嗎
slime iT邦大師 1 級 ‧ 2020-09-26 16:15:02 檢舉
如果只要抓到重複次數, 可以改這樣:
=SUMPRODUCT((A:A>NOW()-30)*(D:D=D2)*1)

但是建議改用 VBA 或 Access 等方式, 因為每個比對都是該欄全部掃描一次, 再把兩個乘起來....
w大~ 因為公司檔案限制,故無法使用巨集檔耶,只能依靠公式了~~~~
s大~因為需求有點複雜,所以~~~~不只是要統計重覆次數喔!!!

2 個回答

2
海綿寶寶
iT邦大神 1 級 ‧ 2020-09-26 18:04:48
最佳解答

這種每天執行結果會不一樣的題目
我只會用 VBA 做

Sub Macro1()
    Dim arrName() As Variant        '記錄廠商名稱
    Dim arrCount() As Variant       '記錄廠商出現次數
    arrCnt = 2                      '陣列長度
    ReDim arrName(1 To arrCnt)
    ReDim arrCount(1 To arrCnt)
    dStart = DateAdd("D", -30, Now) '開始時間
    dEnd = Now                      '結束時間
    inRange = False                 '是否在範圍中
    Debug.Print dStart, dEnd
    
    Range("A1").Select
    Do While True
        dDate = ActiveCell.Value
        sName = ActiveCell.Offset(0, 3).Value
        '判斷空白, 結束執行
        If dDate = "" Then
            Exit Do
        End If
        '判斷目前是否在範圍中
        If (dDate > dStart) And (dDate < dEnd) Then
            inRange = True
        Else
            inRange = False
        End If
        '若在範圍中則進行重覆判斷
        If (inRange) Then
            Pos = IsInArray(sName, arrName)
            If Pos > 0 Then
                If arrCount(Pos) > 0 Then
                    ActiveCell.Offset(0, 4).Value = "重覆" + Trim(Str(arrCount(Pos)))
                Else
                    ActiveCell.Offset(0, 4).Value = "重覆"
                End If
                arrCount(Pos) = arrCount(Pos) + 1
            Else
                arrCnt = arrCnt + 1
                ReDim Preserve arrName(1 To arrCnt)
                ReDim Preserve arrCount(1 To arrCnt)
                arrName(UBound(arrName)) = sName
                arrCount(UBound(arrCount)) = arrCount(UBound(arrCount)) + 1
                ActiveCell.Offset(0, 4) = ""
            End If
        Else
            ActiveCell.Offset(0, 4) = ""
        End If
        ActiveCell.Offset(1, 0).Select
    Loop
    For i = 1 To UBound(arrName)
        Debug.Print i, arrName(i), arrCount(i)
    Next i
End Sub
Public Function IsInArray(ByVal stringToBeFound As String, ByVal arr As Variant) As Integer
    Dim i
    For i = LBound(arr) To UBound(arr)
        If arr(i) = stringToBeFound Then
            IsInArray = i
            Exit Function
        End If
    Next i
    IsInArray = -1
End Function

另外,點這裡是我這次鐵人賽唯一的一篇文章,喜歡的話左上角點 Like

什麼!!!不能用巨集
https://ithelp.ithome.com.tw/upload/images/20200926/200017872JEIbSqfqD.jpg

下次請先寫在問題第一列
/images/emoticon/emoticon41.gif

海大~感謝幫忙喔,問題已解決,再次感謝~~~

2
ccenjor
iT邦新手 3 級 ‧ 2020-09-26 18:57:16

E1儲存格:
=IF(COUNTIFS($A$2:A2,">="&TODAY()-29,$D$2:D2,D2)>=2,"重覆"&COUNTIFS($A$2:A2,">="&TODAY()-29,$D$2:D2,D2)-1,"")
也可以寫成
=IF(SUMPRODUCT(($A$2:A2>=(TODAY()-29))X($D$2:D2=D2)X1)<2,"","重覆"&SUMPRODUCT(($A$2:A2>=(TODAY()-29))X($D$2:D2=D2)X1)-1)
https://ithelp.ithome.com.tw/upload/images/20200926/20109881VJQwjtseFK.png
https://ithelp.ithome.com.tw/upload/images/20200926/20109881ck3tMcFSb4.png

c大~感謝幫忙喔,問題已解決,再次感謝~~~

我要發表回答

立即登入回答