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

[µo°Ý] ¦p¦ó·j´M²Å¦X¦r¦ê , ¨Ã±N¸Ó¦æ¦C¸¹¤Î¸ê®Æ¦CÅã¥Ü¥X¨Ó

[µo°Ý] ¦p¦ó·j´M²Å¦X¦r¦ê , ¨Ã±N¸Ó¦æ¦C¸¹¤Î¸ê®Æ¦CÅã¥Ü¥X¨Ó

¥»©«³Ì«á¥Ñ marklos ©ó 2017-3-24 10:16 ½s¿è

Q1 ½Ð°Ý­n±N¤u§@ªí¤º¦³§R°£½uªº¼Æ¾Ú , ¦Û°Ê¾ã¦C§R°£ , ¸Ó¦p¦ó°µ?


Q2 ¨Ì¾ÚA1Àx¦s®æªº¦r¦ê , ·j´M"Data"¤u§@­¶¤º "D"Äæ¦ì¬Û²Åªº¦r¦ê , ±N¦C¸¹Åã¥Ü¦bAÄæ¦ì¨Ã±N¾ã¦C¸ê®ÆÅã¥Ü¦b¨ä«á


·PÁÂ~~

Question8.zip (15.26 KB)

A3:A6{=IFERROR(SMALL(IF(Data!D$2:D$99=A$1,ROW(D$2:D$99)),ROW(A1)),"")
B3:M6=IF(A3="","",INDEX(Data!A:A,Result!$A3))
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

[attachimg]26883¦^´_ 2# hcm19522

½Ð°Ý¤j¤j
    Q1 ¦Û°Ê§R°£"¦³§R°£½u"ªº¾ã¦C¼Æ¾Ú ¥i¦æ¶Ü?

Q2  ±a¤J¨ç¼Æ«á , ¦C¸¹Åã¥Ü³£¬°0 ?? ³£¬°0ªº±¡§Î¤U , «á­±Äæ¦ìªº¼Æ¾Ú , À³¸Ó³£¬O¬Û¦P¤~¬O , ¬°¦ó·|¦³¤£¦Pªº¸ê®Æ³Q¦^¶Ç??

TOP

¦^´_ 3# marklos


    ½Æ»s¤½¦¡ ¤£§t "=" ,¶K¤W«á¥[ "="   ;³Ì«e "{" ¥Nªí shift+ctrl+enter ¤TÁä¤@°_«ö©Ò²£¥Í ,«Dµ{¦¡¸Ì­±
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

[attach]26884¦^´_ 4# hcm19522
½ÐÀ°¦£¬Ý¬Ý~­þ¸Ì¦³°ÝÃD
·PÁÂ
Question8-1.zip (15.91 KB)

TOP

¦^´_ 5# marklos

   ½Æ»s«á¥[ "="   ; shift+ctrl+enter ¤TÁä¤@°_«ö
2003ª©
IF(ISERR(SMALL(IF(Data!D$2:D$99=A$1,ROW(D$2:D$99)),ROW(A1))),"",SMALL(IF(Data!D$2:D$99=A$1,ROW(D$2:D$99)),ROW(A1)))
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 6# hcm19522

·PÁÂ~¥i¥H¥¿±`¨Ï¥Î¤F~
²Ä¤@­Ó°ÝÃD ¸ê®Æ¤u§@ªí¤¤ , ¬O§_¦³¨ç¼Æ©Î¬O¥¨¶°¥i¥H¦Û°Ê §R°£¦³§R°£½uªº¸ê®Æ¦C©O??

TOP

¦^´_ 6# hcm19522

¦A½Ð±Ð¤@­Ó°ÝÃD
¦pªG·Q­n·j´M©Ò¦³EB¶}ÀYªº¦r¦ê , ¸Ó«ç»ò°µ©O?

TOP

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

°õ¦æ«e:


°õ¦æµ²ªG:



Option Explicit
Sub TEST()
Dim Brr, Y, R&, i&, j%, V$
'¡ô«Å§iÅܼÆ
Brr = Range([Data!M1], [Data!A65536].End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HDataªíA~MÄæÀx¦s®æ­È±a¤J°}¦C¤¤
V = [Result!A1]
'¡ô¥OVÅܼƬO Resultªí[A1]Àx¦s®æ¦r¦ê
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é
   If Brr(i, 4) = V Then
   '¡ô¦pªG²Ä4Äæ­È¬O ¦PVÅܼÆ
      R = R + 1: Brr(R, 1) = i
      '¡ô¥ORÅܼƲ֥[1,¥OBrr°}¦C²Ä1Äæ½Æ¼g¤JiÅܼƭÈ
      For j = 1 To 12: Brr(R, j + 1) = Brr(i, j): Next
      '¡ô¥O¨ä¥L¸ê®Æ½Æ¼g¤JBrr°}¦C2~13Äæ
   End If
Next
With Sheets("Result")
'¡ô¥H¤U¬OÃö©óResultªíªºµ{§Ç
   .UsedRange.Offset(2, 0).ClearContents
   '¡ô¥O¼ÐÃD¦C¯d¤U,¨ä¾lÀx¦s®æ²M°£¤º®e
   .[A3].Resize(R, 13) = Brr
   '¡ô¥OBrr°}¦C­È¼g¤JÀx¦s®æ¸Ì,¶W¹L½d³òªº°}¦C­È©¿²¤
End With
Set Y = Nothing: Erase Brr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤â¤ß¦V¤U¬O§U¤H¡A¤â¤ß¦V¤W¬O¨D¤H¡F§U¤H§Ö¼Ö¡A¨D¤Hµh­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD