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

§ä¥X«ü©w¤H¼Æ

§ä¥X«ü©w¤H¼Æ

§Ú·Q§ä¥X«ü©w¾¯Å, ¦ý­×Ū¹L¤@ºØ©Î¥t¤@ºØ½Òµ{ªº¤H¦³¦h¤Ö¤H¼Æ ? À³¦p¦ó©O ?thx !!!!
ªþ¤Wªþ¥ó !!!
excel_§ä¥X¤H¼Æ.rar (4.03 KB)

¥»©«³Ì«á¥Ñ trainee ©ó 2011-1-29 11:15 ½s¿è

¸Õ吓±NÊ\哋¥[®I¡I

SUMPRODUCT((A2:A52="¤@¯Å¤å­û")*(C2:C52="¤¤¤å½Òµ{")) + SUMPRODUCT((A2:A52="¤@¯Å¤å­û")*(C2:C52="­^¤å½Òµ{"))

TOP

¦ý«Y¥[®Iªº¸Ü¥u¯à­p¥X­×Ū¸Ó¨â­Ó½Òµ{ªºÁ`¼Æ, ¤£¯à­p¥X¤H¼Æ ( ¦]¬°¦³¤@¨Ç¤H¬O¦P®ÉŪ¤F¤¤¤Î­^¤å½Òµ{), ¦p¦ó¯à­p¥X¤H¼Æ©O ?

TOP

¦^´_ 3# yan870510


    ¤]´N¬O»¡"¦W¦r¤£¯à­«½Æ"¡A¨º»ò¥Î¿z¿ï©Î¶i¶¥¿z¿ï§¹«á¡A2007¦³¤@­Ó"²¾°£­«½Æ"--²¾°£­«½Æ¦W¦r«á¡A¦Aºâ­Ó¼Æ¡A³o¼ËÀ³¸Ó¤]¦æ±o³q§a¡C

TOP

¦^´_ 3# yan870510

¶i¶¥¿z¿ï¥X¥þ³¡¦W³æ
¥Î°}¦C¤½¸Õºâ¥X¤£­«½Æ¼Æ¶q
   
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

·|§_¦Ò¼{§ïÅܽs¿è¼Ò¦¡¡A­Y¤£¿¡³Â·Ð¡A½Ð°Ñ¾\ªþ¹Ï¡I


TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-5-12 14:20 ½s¿è

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æ«e:


°õ¦æµ²ªG:



Option Explicit
Sub TEST()
Dim Brr, Crr, Y, N&, i&, j&
'¡ô«Å§iÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO ¦r¨å
Brr = Range([C1], Cells(Rows.Count, 1).End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HA~CÄæÀx¦s®æ¸ê®Æ±a¤J°}¦C¸Ì
ReDim Crr(1 To UBound(Brr), 1 To 3)
'¡ô«Å§iCrr¬O¤GºûªÅ°}¦C,Áa¦V½d³ò¦PBrr°}¦C,¾î¦V±q1~3
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é
   If i = 1 Then N = 1: For j = 1 To 3: Crr(N, j) = Brr(i, j): Next
   '¡ô³B²z¼ÐÃD¦C
   If Brr(i, 1) = "¤@¯Å¤å­û" And InStr("/¤¤¤å½Òµ{/­^¤å½Òµ{/", "/" & Brr(i, 3) & "/") Then
   '¡ô¦pªG²Å¦X±ø¥ó
      If Y(Brr(i, 2) & "") = "" Then
      '¡ô¦pªG©m¦W¤£­«½Æ
         N = N + 1
         For j = 1 To 3: Crr(N, j) = Brr(i, j): Next
         '¡ô²Å¦X±ø¥ó¤S¤£­«½Æªº¸ê®Æ¼g¤JCrr°}¦C¸Ì
         Y(Brr(i, 2) & "") = 1
         '¡ô¥O¦¹¦r¨å¸Ì¦¹keyªºitem­È¬O 1(Åý«á°j°éÂo­«½Æ)
      End If
   End If
Next
[E12].Resize(N, 3) = Crr
'¡ô¥OCrr°}¦C­È±q[E12]¼g¤JÀx¦s®æ¸Ì
Set Y = Nothing: Erase Brr, Crr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

=SUM(COUNTIFS(A:A,"¤@¯Å¤å­û",C:C,{"­^¤å½Òµ{","¤¤¤å½Òµ{"}))
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

        ÀR«ä¦Û¦b : ­n§åµû§O¤H®É¡A¥ý·Q·Q¦Û¤v¬O§_§¹¬üµL¯Ê¡C
ªð¦^¦Cªí ¤W¤@¥DÃD