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

[µo°Ý] ¦³ >1²Õ¥H¤Wªº²Õ¦X®É¡A«h¼Ð¥Ü©³¦âªº»yªk¡C

[µo°Ý] ¦³ >1²Õ¥H¤Wªº²Õ¦X®É¡A«h¼Ð¥Ü©³¦âªº»yªk¡C

¥»©«³Ì«á¥Ñ papaya ©ó 2017-11-8 22:47 ½s¿è

12¥Í¨v-VBA.png
2017-11-8 22:44


PS¡J
µ{¦¡½X¼g¦bSheet1§Y¥i¡FSheet2©MSheet3¥u§@»²§U»¡©ú¥Î~§¹¦¨«á§Y²¾°£¡C
B2ªº½s¸¹©MC2ªº«ü©w¥Í¨v¥H¤H¤u¶ñ¤J¡C

¥HDÄæ=B2½s¸¹¦P¦C(E¡JH)=C2ªº¥Í¨v©MJ¡JM¹ïÀ³°Ï¬qªº¦P¦C¶¡¹j5Ä椧¥Í¨v(¥H¤U²ºÙ¬°¹ïÀ³¥Í¥Í¨v)¬°«ü©w²Õ¦X~
EX¡JB2=10¡FC2=ªê¡A§Y¥HG2ªºªê©ML2ªºÀs¬°«ü©w²Õ¦X¡C
EX¡JB2=14¡FC2=¤û¡A§Y¥HE6ªº¤û©MJ6ªº¦Ï¬°«ü©w²Õ¦X¡C
¨ä¾l.......Ãþ±À

»Ý¨D¡J
·íE¡JH°Ï¬q¦³Åã¥Ü=C2ªº«ü©w¥Í¨v©MJ¡JM¹ïÀ³°Ï¬qªº¦P¦C¤]¦³Åã¥Ü¹ïÀ³¥Í¨v(¤£­­¶¡¹j5Äæ)¥B¬°>1²Õ(§t«ü©w²Õ¦X)®É¡A
«hC2¼Ð¥Ü¶À©³¦â¡F¦p¤W­zªº >1²Õªº²Õ¦X¡A¦U¼Ð¥Ü¶À©³¦â(E¡JH)©M²L¯»ÂÅ©³¦â(J¡JM)¡C
EX¡JSheet1=>·íB2=10¡FC2=ªê®É¡F§Y¬Oªê(G2)©M¹ïÀ³°Ï¬qªºÀs(L2)¬°«ü©w²Õ¦X¡F
«h¦³¡J
G2=ªê*L2=Às¡FF5=ªê*M5=Às¡FE7=ªê*K7=Àsµ¥¦@­p3²Õ¡C
©Ò¥H±NC2,G2, F5,E7¦U¼Ð¥Ü¶À©³¦â¡F¥t±NL2, M5, K7¦U¼Ð¥Ü²L¯»ÂÅ©³¦â¡C

EX¡JSheet2=>·íB2=14¡FC2=¤û®É¡F§Y¬O¤û(E6)©M¹ïÀ³°Ï¬qªº¦Ï(J6)¬°«ü©w²Õ¦X¡F
«h¦³¡J
E6=¤û*J6=¦Ï¡FF8=¤û*M8=¦Ïµ¥¦@­p2²Õ¡C
©Ò¥H±NC2,E6, F8¦U¼Ð¥Ü¶À©³¦â¡F¥t±NJ6, M8¦U¼Ð¥Ü²L¯»ÂÅ©³¦â¡C

EX¡JSheet3=>·íB2=12¡FC2=µU®É¡F§Y¬OµU(E4)©M¹ïÀ³°Ï¬qªºµU(J4)¬°«ü©w²Õ¦X¡F
«h¥u¦³¡J
E4=µU*J4=µUµ¥¦@­p1²Õ¡C
©Ò¥H³£¤£¼Ð¥Ü¥ô¦ó©³¦â¡C


½Ð°Ý¡J¦p¤W­z»Ý¨Dªºµ{¦¡»yªk?
ÁÂÁÂ!


12¥Í¨v-VBA.rar (10.92 KB)

¦^´_ 1# papaya

¡u®æ¦¡¤Æ±ø¥ó¡v¸Ñªk
®æ¦¡.gif
2017-11-9 10:51


¬¡­¶Ã¯­pºâ¡A¿ï¡u¦Û°Ê¡v¡]­ìÀɬ°¤â°Ê¡^
2017-11-09_101643.png
2017-11-9 10:52


©w¸q¦WºÙ¡A²¤Æ¤½¦¡¡F³]©w®æ¦¡¤Æ±ø¥ó
2017-11-09_102958.png
2017-11-9 10:55

TOP

(»²§U)C3=IF(SUM(MMULT((INDEX(J$2:M$9,MATCH(B2,D:D,)-1,MATCH(C2,OFFSET(E$1:H$1,MATCH(B2,D:D,)-1,),))=J$2:M$8)*1,{1;1;1;1})*MMULT((E$2:H$8=C2)*1,{1;1;1;1}))>1,INDEX(J$2:M$9,MATCH(B2,D:D,)-1,MATCH(C2,OFFSET(E$1:H$1,MATCH(B2,D:D,)-1,),)),"X")

C2®æ¦¡¤Æ=C3<>"X"

E2:H8®æ¦¡¤Æ=(E2=$C$2)*OR($J2:$M2=$C$3)

J2:M8®æ¦¡¤Æ=(J2=$C$3)*OR($E2:$H2=$C$2)
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 2# joblyc017
J¤j:
·PÁ±z¦A¦¸«ü¾É^^
¤£¦n·N«ä¡A¦pªG¬O¥H®æ¦¡¤Æ±ø¥ó¨Ó¼Ð¥Ü©³¦â¡A¤p§Ì¤w¸g·|¤F¡C
¥»ÃD¥Hµ{¦¡»yªk§@»Ý¨D¡A¬O¤p§Ì·Q¬ã¨s»yªkªº½s¼g¡C

¦A¦¸ÁÂÁ±z¶O¯«ªº¼ö¤ß«ü¾É~·P®¦^^

TOP

¦^´_ 3# hcm19522
h¤j:
·PÁ±z¦A¦¸«ü¾É^^
¤£¦n·N«ä¡A¥»ÃD¥Hµ{¦¡»yªk§@»Ý¨D¡A¬O¤p§Ì·Q¬ã¨s»yªkªº½s¼g¡C

¦A¦¸ÁÂÁ±z¯Ó¯«ªº¼ö¤ß«ü¾É~·P®¦^^

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2017-11-10 12:14 ½s¿è

¦^´_ 1# papaya
  1. Private Sub CommandButton1_Click()
  2. Dim MyRng As Range, MyRng1 As Range
  3. k = [B2]
  4. a = [C2]
  5. Set Rng = Columns("D").Find(k, lookat:=xlWhole)
  6. mystr = Join(Application.Transpose(Application.Transpose(Rng.Offset(, 1).Resize(, 4))), "")
  7. s = InStr(mystr, a)
  8. If s = 0 Then MsgBox "¦¹¦CµL¦¹¥Í¨v": End
  9. t = Mid(Join(Application.Transpose(Application.Transpose(Rng.Offset(, 6).Resize(, 4))), ""), s, 1)
  10. For Each c In Range([D2], [D2].End(xlDown))
  11.    Set n = c.Offset(, 1).Resize(, 4).Find(a, lookat:=xlWhole)
  12.    Set m = c.Offset(, 6).Resize(, 4).Find(t, lookat:=xlWhole)
  13.    If Not n Is Nothing And Not m Is Nothing Then
  14.       cnt = cnt + 1
  15.       If MyRng Is Nothing Then
  16.       Set MyRng = n
  17.       Set MyRng1 = m
  18.       Else
  19.       Set MyRng = Union(MyRng, n)
  20.       Set MyRng1 = Union(MyRng1, m)
  21.       End If
  22.    End If
  23. Next
  24. MsgBox cnt & "¦¸"
  25. If cnt > 1 Then
  26.   MyRng.Interior.ColorIndex = 6
  27.   MyRng1.Interior.ColorIndex = 8
  28. End If
  29. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 6# Hsieh
H¶W¯Åª©¥D:
·PÁ±zªº¦A¦¸«ü¾É^^

¶Q¸Ñµª¤ñ¤p§Ìªº»Ý¨D³]·QÁÙ©P¥þ¡C
¤p§Ì·|¥J²Ó¬ã²ßªº~·P®¦^^

TOP

        ÀR«ä¦Û¦b : ¤Ó¶§¥ú¤j¡B¤÷¥À®¦¤j¡B§g¤l¶q¤j¡A¤p¤H®ð¤j¡C
ªð¦^¦Cªí ¤W¤@¥DÃD