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

¦p¦ó¦b²Ä¤@¦¸¤ñ¹ï§ä¤£¨ì®É¦Û°Ê¦A¤ñ¹ï²Ä¤G±ø¥ó®M¨ú²Å¦Xªº­È??

¦p¦ó¦b²Ä¤@¦¸¤ñ¹ï§ä¤£¨ì®É¦Û°Ê¦A¤ñ¹ï²Ä¤G±ø¥ó®M¨ú²Å¦Xªº­È??

¦pªþ¥ó¦³©ú²Ó¤Î²Î­p¤G³øªí¡A²Î­p³øªí¦C¥X®M¨ú©ú²Ó¤¤ªºÁ`¼Æ¶q¡A¦ý¦p¦ó¦b¤ñ¹ï¤£¨ì®Æ¸¹A®É¥i¦A¦Û°Ê¤ñ¹ï®ÆB¡A¨Ã±N¦P«È¤á¤Î®Æ¸¹ªº¼Æ¶q¥[Á`???

¦p¦b©ú²Ó¤¤¦³²Î­p¤u§@ªí¤W¨S¦³ªº«È¤á¤Î®Æ¸¹¸ê®Æ¡A¬O§_¤S¥i³]©wÅý¨ä¼W¥[¦b²Î­p³øªí³Ì«á¤@¦C¡]©Î¦¹¥u¯à¨Ï¥Î¥¨¶°¤~¯à¹F¦¨???¡^

Book2.rar (1.89 KB)

=IF(SUMPRODUCT((©ú²Ó!$A$2:$A$23=A2)*(©ú²Ó!$B$2:$B$23=B2)*(©ú²Ó!$D$2:$D$23)),SUMPRODUCT((©ú²Ó!$A$2:$A$23=A2)*(©ú²Ó!$B$2:$B$23=B2)*(©ú²Ó!$D$2:$D$23)),SUMPRODUCT((©ú²Ó!$A$2:$A$23=A2)*(©ú²Ó!$C$2:$C$23=B2)*(©ú²Ó!$D$2:$D$23)))

TOP

·PÁÂANGELAª©¥D¡A¸Ñ¨M¤F¤p§Ìªº¤j°ÝÃD¡A¦ý½Ð°Ý¤p§Ìªº²Ä¤G­Ó°ÝÃD¡A¦pªG­n±N¨S¦³ªº¸ê®Æ¥[¦b²Î­p©ú²Ó¤¤¡A¬O§_¥u¯à¨Ï¥Î¥¨¶°¹F¦¨???

TOP

A2=INDEX(©ú²Ó!$A$1:$A$1000,SMALL(IF(©ú²Ó!$A$2:$A$100<>"",ROW($A$2:$A$100),999),ROW(A1)))&""  °}¦C¤½¦¡
®Æ¸¹¬O­na®Æ¸¹ÁÙ¬Ob®Æ¸¹?¥i¥ÎVLOOKUP§ì¨ú

TOP

¦^´_ 3# p6703
¦pªG¦³¤F°t¹ï²M³æ
C2=SUMPRODUCT((IF(COUNTIF(©ú²Ó!$B$2:$B$23,²Î­p!$B2),©ú²Ó!$B$2:$B$23,©ú²Ó!$C$2:$C$23)=$B2)*(©ú²Ó!$A$2:$A$23=²Î­p!$A2)*(©ú²Ó!$D$2:$D$23))

¦ý¬O¤½¦¡¨ú±o©Ò¦³°t¹ï²M³æ·|«Ü³Â·Ð¡A¥B·í¼Æ¶q¦h®É¥i¯à·|«ÜºC
ª½±µ¨Ï¥ÎVBA°õ¦æ·|¤ñ¸û«ê·í
  1. Sub ex()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. With Sheets("©ú²Ó")
  4.   For Each a In .Range(.[A2], .[A2].End(xlDown))
  5.      For Each b In a.Offset(, 1).Resize(, 2)
  6.        If IsEmpty(d(a & b)) Then d(a & b) = Array(a, b, a.Offset(, 3))
  7.      Next
  8.   Next
  9. End With
  10. With Sheets("²Î­p")
  11. .UsedRange.Offset(1) = ""
  12. .[A2].Resize(d.Count, 3) = Application.Transpose(Application.Transpose(d.items))
  13. End With
  14. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

·PÁÂANGELA¤ÎHsiehª©¥Dªº¤£§[±Ð¾É¡A¸Ñ¨M¤F¤p§Ìªº¤j°ÝÃD¡A¥ý¦Û¦æºN¯Á¬Ý¬Ý¡A¦p¦³°ÝÃD¦A¦æ½Ð±Ð^^

TOP

        ÀR«ä¦Û¦b : ¤£­n¤p¬Ý¦Û¤v¡A¦]¬°¤H¦³µL­­ªº¥i¯à¡C
ªð¦^¦Cªí ¤W¤@¥DÃD