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

[µo°Ý] Ãö©óSUMPRODUCT¨ç¼Æ...

[µo°Ý] Ãö©óSUMPRODUCT¨ç¼Æ...

¦U¦ì«e½ú...
§Ú¦³¥÷¸ê®Æ»Ý­n°µ±ø¥ó§PÂ_,sheet4¤º¨C¦¸ªº¸ê®Æ³£¬O¸Uµ§°_¸õ

¦ý²{¦b¹J¨ìªº°ÝÃD,SUMPRODUCTªº¤½¦¡®M¥Î¦b±Nªñ4000­ÓÀx¦s®æ,
©Ò¥H¨C·í¸ê®Æ¦³©ÒÅÜ°Ê,´N·|­«·sŪ¨ú¤@¦¸®É¶¡«D±`ªº¤[...

¬O§_¦³¤èªk¯à¨Ï¥H¤Wªº§PÂ_¥u°µ¤@¦¸Åª¨ú??

©Î¬O±N =SUMPRODUCT((Sheet4!$B:$B=$C$2)*(Sheet4!$C:$C=F2))ÂàÅܬ°VBA??

ªþ¤WÀɮ׽ШD¦U¦ì«e½ú«üÂI. (ÀÉ®×¥u¦³³¡¤À®æ¦¡)

test1.zip (11.46 KB)

³¡¤À®æ¦¡

¦^´_ 1# andy8426

¤½¦¡¡÷­«ºâ¿ï¶µ¡÷¤â°Ê¡A¥i¥Hµ¥¸ê®Æ¿é¤J§¹¦¨«á¦A¦æ­pºâ

¦Ü©ó¤½¦¡®Ä²v¤è­±¡Asumproduct¨ç¼ÆÀ³¸Ó¤w¸gº¡¦³®Ä²vªº
¥i¯à­n±q§A¸ê®Æªº§e²{¤è¦¡¤U¤â¡A±q§AªºÀɮפ¤¥i¥H¬Ý¥X¤@¨Ç³W«ß
¦ý¬O¦]¬°¤£§¹¾ã¡A©Ò¥H¤]µLªkÀ°¦£

©Î³\§A¥i¥H»¡©ú¤@¤U§A¾ã­Ó¸ê®ÆªºÅÞ¿è¡A¥H¤Î·Q­n¹F¨ì¤°»ò¼Ëªº®ÄªG (¦p¡G¬O§_¤@©w­n®M¦b4000­ÓÀx¦s®æ¡H³o4000­ÓÀx¦s®æ¬O¦p¦ó¦w±Æ¡H¦³¦ó³W«ß¡H)
³o¼Ë¤~¤ñ¸û¦³¿ìªkµ¹§A·N¨£

TOP

¦^´_ 2# Bodhidharma

·PÁ´£¿ô~

¥Ø«e¤½¦¡½Õ¦¨¤â°Ê,¾Þ§@¤W¤è«K³\¦h.

¸ê®Æ¬O¹Bºâ¦bGÄ椤16­ÓÀx¦s®æ~
³W«ß¥Ñ¤W¦Ó¤U  ³£¬O¬Û¦PªºÀx¦s®æ~
©Ò¥H¤½¦¡·|®M¥Î¦b3808­ÓÀx¦s®æ(238­Ó¶µ¥Ø*©T©wªº16­Ó±ø¥ó)

SUMPRODUCT((Sheet4!$B:$B=$C$2)*(Sheet4!$C:$C=F2))

Sheet4¤º¬O­n²Î­pªº¸ê®Æ  
C2  »P F2  ¬O ±ø¥ó  

¦³¸ÕµÛ¿ý¨îVBA»P°Ñ¦Ò¥t½g¡u¥ÎVBA¨Ó°õ¦æSUMPRODUCT¦h±ø¥ó²Î­p¡v
¦ý¦]¬°Åv­­¤£¨¬Ãö¨tµLªk¨ú±oÀɮװѦÒ.

¥Ø«e°£¤F¥ý±N¤½¦¡½Õ¦¨¤â°Ê¥~,¤]¥¿¦b·Q¦p¦óÀu¤Æ¦¨VBA
¸Ñ¨M§¹¹Bºâªº°ÝÃD¤~¦³¿ìªk±N¨ä¾l«e¸m°Ê§@¾ã¦X¦b¸Ì­±....
ÁٽЫe½ú«ü±Ð.

TOP

¦^´_ 3# andy8426

¦pªG¤@©w­n±N¸ê®Æµ²ªG¹Bºâ¨ì¨º238*16­Ócell¤¤¡A¨º¤j·§¯uªº­n¥ÎVBA¤~·|¤ñ¸û¦³®Ä²v¤F
²¦³ºª½±µ¥Îsumproductªº¸Ü¡A¨C­ÓÀx¦s®æ³£­nºâ¤@¦¸¡A©Ò¦³¸ê®Æ³£±oºâ­Ó3808¦¸
¦ý¬O¥ÎVBAªº¸Ü¡A¥i¥Hª½±µ±N©Ò¦³ªº¸ê®Æ±½¹L¤@¹M¡AµM«á¥á¨ì[238][16]ªº°}¦C¤§¤¤§Y¥i
§A¥i¥H§â§A¼gªºµ{¦¡PO¦bµ{¦¡°Ï¡A¦A½Ð¤j®aµ¹·N¨£

(¸Ü»¡§ÚÁÙ¨S¦³ªÅ¾ÇVBA¡A¥H¤W¯Âºé´Nµ{¦¡³]­pªº·§©Àµo¨¥)

TOP

¦^´_ 3# andy8426
  1. Sub ex()
  2. Dim a As Range, s&
  3. Set dic = CreateObject("Scripting.Dictionary")
  4. With Sheets("Sheet4")
  5.   For Each a In .Range(.[B1], .[B1].End(xlDown))
  6.      dic(a & a.Offset(, 1)) = dic(a & a.Offset(, 1)) + 1
  7.   Next
  8. End With
  9. With Sheets("Sheet3")
  10.   For Each a In .Range(.[F2], .[F2].End(xlDown))
  11.   s = dic(a.Offset(, -3).MergeArea(1) & a)
  12.      a.Offset(, 1) = s
  13.   Next
  14. End With
  15. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 5# Hsieh

«D±`·PÁª©¥D«ü¾É~   

¥t¥~·Q°Ý¤@¤U...
Dim a As Range, s&
¨º­Ós&¥Nªí¬O¬Æ»ò?

a.Offset(, -3).MergeArea(1)
³o¬q¬Ý¤£À´...

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-5-9 18:48 ½s¿è

¦^´_ 6# andy8426

> ¨º­Ós&¥Nªí¬O¬Æ»ò?
¬Ûµ¥©ó dim s as Long
>a.Offset(, -3).MergeArea(1)

§ä¥X a(FÄæ) ®æ¤l«e3Äæ (§YCÄæ)¤U¦X¨Ö®æ¤lªº¸ê®Æ
¨Ò¦p¦b®æ¤lF3®É , ´N·|§ä¥X C2 ªº¸ê®Æ (B1-1)
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

¦^´_ 7# kimbal


  ·PÁª©¥D»¡©ú~~
¸g¹L³o¤@½Ò¾Ç¨ì«Ü¦h.

TOP

¦^´_ 5# Hsieh


    ¥i¥H³B²z¦X¨ÖÀx¦s®æªº²Î­p
   ¾Ç²ß¤F
   ¹Á¸Õ¸ÑŪ¤£¹LÁÙ¤£ª¾¹D²Î­pªº¤è¦¡
   ¬Ý¨Ó§Ú¹ï©ó¦r¨åÁٻݭn¦h§V¤O
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 1# andy8426

4000¦hµ§¨Ï¥Î¤½¦¡À³¸ÓÁÙ¤£·|¤ÓºC

«Øij1¡A¤£­n¥ÎÂX¤j½d³ò
=SUMPRODUCT((Sheet4!$B:$B=$C$2)*(Sheet4!$C:$C=F2))
B:B C:C ³oºØÂX¤j½d³ò¦b2007ª©¥H¤W¬°1048576¦C¡A¼W¥[¤Ó¦hµL®Ä®æªº­pºâ«Ü®ö¶O®É¶¡
§Aªº¥Ü½d¨Ò¤~72µ§¬d¸ß¸ê®Æ¡A­pºâ¤½¦¡¤~65µ§¡A¥u­n«öF9´N·Pı¥L¶]±o®ð©I©I
±N¤½¦¡§ï¬°
=SUMPRODUCT((Sheet4!$B1:$B72=$C$2)*(Sheet4!$C1:$C72=F2))
°¨¤W´N§ïµ½«Ü¦h

«Øij2¡A¸ê®Æ®w§ó¤j®É¡A­n±Æ§Ç¾ã²zÅý¬d¸ß¥[³t
¦pSheet4 BÄæÀ³¸Ó±Æ§Ç¡A¤~¯à©w¦ì¥X¬d¸ß¤p½d³ò(°ÊºA¬d¸ß½d³ò)¡AÅý­pºâ¤ñ¹ï¤u§@¶qÁY¤p
³o³¡¤À¤ñ¸û½ÆÂø¥ý´£¥Ü¤@¤U¡Aµ¥±ß¤W¦^¨Ó¦³ªÅ¦A»¡
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¨S¦³©Ò¦³Åv¡A¥u¦³¥Í©Rªº¨Ï¥ÎÅv¡C
ªð¦^¦Cªí ¤W¤@¥DÃD