ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

§Q¥Î¤é´Á¨Ó¥[Á`·í¤éª÷ÃB¥X¿ù

§Q¥Î¤é´Á¨Ó¥[Á`·í¤éª÷ÃB¥X¿ù

½Ð°Ý¦U¦ì¤j¤j
§Ú­n­pºâªºÄæ¦ì¬OBGÄæ¦ì
§PÂ_¬OAOÄæ¦ì
§Úªº­pºâ¦¡¦p¤U¡G
=SUMPRODUCT((AO3:AO39=TODAY())*BG46:BG82)
¦ý¬O§ÚAOÄæ¦ì¬O¥Î­pºâ¦¡Åã¥Ü¤é´Á
=IF(AS3="","",IF(AO3="",TEXT(TODAY(),"YYYY/mm/dd"),AO3))
¾É­PSUMPRODUCTµLªk§PÂ_¤é´Á
½Ð°Ý¤j¤j¦³¤èªk¶Ü
ÁÂÁÂ

=SUMPRODUCT((AO3:AO39-TODAY()=0)*BG46:BG82)
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 2# hcm19522

³ø§i¤j¤j
¤£¦æ
µ²ªG¬O0
¦]¬°§Úªº¤é´Á¤£¬O¤â°Ê¿é¤Jªº
¬O¤½¦¡
¾É­PSUMPRODUCTµLªk§PÂ_AOÄæ¦ì¤é´Á(¦]¬°¬O¤½¦¡)
½Ð°Ý¤j¤j¦³¸Ñ¤è¶Ü¡H
ÁÂÁ¤j¤j

TOP

¦^´_ 3# coafort
ÁÂÁ«e½úµoªí¦¹¥DÃD
½Ð«e½ú¸Õ¸Õ¬Ý
1.AO3:AO39®æ¦¡³]¬°yyyy/m/d;@
2.AO3³]¤½¦¡ =IF(AS3="","",TODAY())
3.¤U¨ê¨ìAO39
TEST_20220923-1.zip (25.09 KB)


¤µ¤é²ß±o=SUMPRODUCT()
  1. Option Explicit
  2. Sub TEST_1()
  3. '[AS3:AS39]Åܧó IJµo
  4. 'Åý[AO3:AO39]¤µ¤Ñ¤é´ÁªºAO©³¦â¤£¦P,¹ïÀ³[BG46:BG82]©³¦â¤£¦P¤]¤£¦P
  5. '¥uÁ`­p[AO3:AO39]¬O¤µ¤éªº,¹ïÀ³[BG46:BG82]¼Æ­È¦bBG2
  6. Application.ScreenUpdating = False
  7. Dim i
  8. [BG2] = ""
  9. For i = 3 To 39
  10.    If Cells(i, "AO") = Date Then
  11.       Cells(i, "AO").Interior.ColorIndex = 17
  12.       Cells(i, "BG").Item(44, 1).Interior.ColorIndex = 17
  13.       [BG2] = [BG2] + Cells(i, "BG").Item(44, 1)
  14.       Else
  15.          Cells(i, "AO").Interior.ColorIndex = xlNone
  16.          Cells(i, "BG").Item(44, 1).Interior.ColorIndex = xlNone
  17.    End If
  18. Next
  19. End Sub
  20. Sub TEST_2()
  21. '[AS3:AS39]Åܧó IJµo
  22. 'Åý[AO3:AO39]¤µ¤Ñ¤é´ÁªºAO©³¦â¤£¦P,¹ïÀ³[BG46:BG82]©³¦â¤£¦P¤]¤£¦P
  23. '¥uÁ`­p[AO3:AO39]¬O¤µ¤éªº,¹ïÀ³[BG46:BG82]¼Æ­È¦bBG2
  24. Application.ScreenUpdating = False
  25. Dim i As Range, Y
  26. Set Y = CreateObject("Scripting.Dictionary")
  27. [BG2] = ""
  28. For Each i In Range([AO3], [AO82])
  29.    Y.Add i, i.Item(44, 19)
  30.    If i = Date Then
  31.       i.Interior.ColorIndex = 35
  32.       i.Item(44, 19).Interior.ColorIndex = 35
  33.       [BG2] = [BG2] + Y(i)
  34.       Else
  35.          i.Interior.ColorIndex = xlNone
  36.          i.Item(44, 19).Interior.ColorIndex = xlNone
  37.    End If
  38. Next
  39. End Sub
  40. Sub TEST_3()
  41. '[AS3:AS39]Åܧó IJµo
  42. 'Åý[AO3:AO39]¤µ¤Ñ¤é´ÁªºAO©³¦â¤£¦P,¹ïÀ³[BG46:BG82]©³¦â¤£¦P¤]¤£¦P
  43. '¥uÁ`­p[AO3:AO39]¬O¤µ¤éªº,¹ïÀ³[BG46:BG82]¼Æ­È¦bBG2
  44. Application.ScreenUpdating = False
  45. Dim R&, Y, Z As Range, xR As Range
  46. Set Y = CreateObject("Scripting.Dictionary")
  47. [BG2] = ""
  48. Set Z = Range([AO3], [BG82])
  49. Z.Interior.ColorIndex = xlNone
  50. For Each xR In Z
  51.    Y(xR.Value) = Y(xR.Value) + xR.Item(44, 19).Value
  52.    If xR = Date Then
  53.       xR.Interior.ColorIndex = 38
  54.       xR.Item(44, 19).Interior.ColorIndex = 38
  55.    End If
  56. Next
  57. [BG2] = Y(Date)
  58. End Sub
½Æ»s¥N½X

TOP

¦^´_ 4# Andy2483


ÁÂÁ¤j¤j
¤ñ¸û»Ý­n¥i¥H¤£¥Î«ö¿z¿ï
ª½±µ¦Û°Ê§PÂ_ªº

TOP

¦^´_ 3# coafort


    =SUMPRODUCT(IFERROR(AO3:AO39-TODAY()=0,)*BG46:BG82)
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 6# hcm19522

³ø§i¤j¤j
¥X²{#name?¿ù»~
ÁÂÁ¤j¤j

TOP

=SUMPRODUCT(Isnumber(0/(AO3:AO39-TODAY()=0))*BG46:BG82)

TOP

¥»©«³Ì«á¥Ñ coafort ©ó 2022-9-24 17:51 ½s¿è

¦^´_ 8# ­ã´£³¡ªL


ÁÂÁ¤j¤j
¥i¥H¥Î
¦ý¬O
¦pªGÄæ¦ì¬OªÅ¥Õ
·|¥X²{#VALUE¡I
¦]¬°§ÚBG46:BG82¬O¥Î=IF(AS41="","",...............
¦pªG§ï¬°=IF(AS41="",0,...............
¬O¥i¥H¸Ñ
¨º¦pªG­n«O¯d¥Î=IF(AS41="","",...............
¸Ó«ç»ò­×§ï©O¡H
·P®¦¤j¤j

TOP

¦^´_ 9# coafort


=SUMPRODUCT(--Isnumber(0/(AO3:AO39-TODAY()=0)),BG46:BG82)

TOP

        ÀR«ä¦Û¦b : §Ú­Ì­n°µ¦nªÀ·|ªºÀô«O¡A¤]­n°µ¦n¤º¤ßªºÀô«O¡C
ªð¦^¦Cªí ¤W¤@¥DÃD