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

[µo°Ý] ¨â­Ó¤u§@ªí¦X¨ÖÄæ¦ì¤ñ¹ï¼Ð¥ÜÃC¦â

[µo°Ý] ¨â­Ó¤u§@ªí¦X¨ÖÄæ¦ì¤ñ¹ï¼Ð¥ÜÃC¦â

¥»©«³Ì«á¥Ñ jackie-ap ©ó 2020-4-2 20:36 ½s¿è

¦U¦ì«e½ú¤j¤j

¦³sheet1,sheet2¨â­Ó¤u§@ªí, ¦U¦³¤W¸Uµ§¸ê®Æ, ¤º®e·|­«½Æ, µ§¼Æ¸ê®Æ¤]·|¼W´î, [»¡©ú¦p¤U]: B,CÄæ¬O¼Æ¦rÄæ, ¼Æ¦r¤j¤p­È·|­«½Æ(¦psheet1ªíB2~B4,B30~B33, sheet2ªíB2~B4,B33~B36), ±Æ¦C¤è¦¡¬O¨S¦³©T©w, ¦³¨Ç·|±Æ¦b«á­±[¦p¦psheet1ªíBÄæ12¬õ¦â¼Æ¦r¦bB2~B4,B30~B33(¦ì¸m¦bÀɮ׫᭱), sheet2ªíBÄæ12¼Æ¦r¦bB2~B4,B33~B36(¦ì¸m¦bÀɮ׫᭱)]; DÄæ¬O¼Æ¦rÄæ, ©Î¬OªÅ¥Õ®æ(¦psheet1ªíD4,D19, sheet2ªíD4,D18Àx¦s®æ¤º®e¬OªÅ¥Õ®æµL¸ê®Æ), , ±Æ¦C¤è¦¡¤]¨S¦³©T©w ¦ý¼Æ¦r¤£·|­«½Æ; F,LÄæ¬O»Ý­n¤À¶}°µ¤ñ¹ï, Àx¦s®æ¸ê®Æ¤º®e¦³¤j/¤p¼g,¦³¨Ç¬O¤å¦r, ¦³¨Ç¬O¯S®í¦r¦p ¡§:¡¨, ¡§-¡¨, ¡§_¡¨, ¡§(¡¨ ©Î ")"¡K¡K, ¦r¼Æ¤]¤£·|§¹¥þ¬Û¦P©MÀx¦s®æ«e/¤¤/«á¦ì¸m,¥i¯à¦³¤@­Ó©Î¦h­Ó¡§ ¡¨ (ªÅ¥Õ¦r).
[§Æ±æµ²ªG]:
1.¦P®É¹ïsheet1,sheet2ªºB,C,DÄæ¶i¦æ¦X¨Ö¤ñ¹ï, ­YB,C,DÄæ¸ê®Æ§¹¥þ¬Û¦P´N¹ïF,LÄæ°µ¦X¨Ö¤ñ¹ï, °²¦psheet1,sheet2ªíB,C,D,F,LÄæ¸ê®Æ¤º®e§¹¥þ¬Û¦P, ©³¦â¤£°µÃC¦â§ïÅÜ.
2.°²¦psheet1,sheet2ªíB,C,D,Äæ¸ê®Æ¤º®e¬Û¦P, ¦ýF,LÄ椤¸ê®Æ¤º®e¦³¤£¦Pªº¸Ü,±Nsheet1,sheet2ªíF,LÄæÀx¦s®æÃC¦â¬°¶À©³¦â(¦psheet1¤u§@ªíL3 (Dog:cat LLL)Àx¦s®æ¸ê®Æ¤º®e, »Psheet2¤u§@ªíL3 (Dog:cat MMM) Àx¦s®æ¬O¤£¦P, ÃC¦â´N¼Ð¥Ü¶À©³¦â), ­YF,LÄæÀx¦s®æ¸ê®Æ¤º®e¦P®É¤£¦P®É´N¤À§O¼Ð¥ÜÃC¦â¬°¶À©³¦â(¦psheet1, sheet2¤u§@ªí¤¤²Ä4¦C F4©ML4 ¸ê®Æ¬O¤£¦Pªº).
3.°²¦psheet1,sheet2ªíB,C,D,Äæ¦X¨Ö¤ñ¹ï¸ê®Æ¤º®e¬O¤£¦P(¦psheet1¤u§@ªíB,C,D,Äæ¦X¨Ö¤ñ¹ïÀx¦s®æ¸ê®Æ¤º®e¤À§O¬° ¡§13,5,5" ¦ýsheet2¤u§@ªíB,C,D,ÄæÀx¦s®æ¨S¦³¡§13,5,x(µL)"¸ ªí¥Ü¨â­Ó¤u§@ªí¨S¦³²Å¦X±ø¥ó, ´N±NF,LÄæÀx¦s®æ¤À§O¼Ð¥ÜÃC¦â¬°ºñ©³¦â.
4.µ²ªGÅã¥Ü¦psheet1a,sheet2aªí, ¦p¶À©³¦â©Mºñ©³¦â.

¥H¤W Àµ½Ð«e½ú¤j¤j¨ó§U, ÁÂÁÂ!

color.rar (12.51 KB)

F/LÄæ,
1) ²Å¸¹¤ÎªÅ®æ­n¥h°£¦A¤ñ¹ï¶Ü?  
2) ­^¤å¤j¤p¼gµø¬°¬Û¦P¶Ü?

TOP

¥ý°µ­Ó~~
  1. Sub ¤ñ¹ï()
  2. Dim Arr, xD, Ra As Range, Rb As Range, j%, R&, T$, SS, C%
  3. Set xD = CreateObject("Scripting.Dictionary")
  4. Arr = Sheets("Sheet1").UsedRange: Set Ra = [Sheet1!A1]
  5. For R = 2 To UBound(Arr)
  6.     T = Arr(R, 2) & "|" & Arr(R, 3) & "|" & Arr(R, 4)
  7.     xD(T) = R & "|" & Arr(R, 6) & "|" & Arr(R, 12)
  8. Next
  9. '------------------------------
  10. Arr = Sheets("Sheet2").UsedRange: Set Rb = [Sheet2!A1]
  11. For R = 2 To UBound(Arr)
  12.     T = Arr(R, 2) & "|" & Arr(R, 3) & "|" & Arr(R, 4)
  13.     If T = "||" Then GoTo 102
  14.     SS = Split(xD(T) & "||", "|")
  15.     If Val(SS(0)) = 0 Then Union(Rb(R, 6), Rb(R, 12)).Interior.ColorIndex = 43: GoTo 102
  16.     For j = 1 To 2
  17.         C = Array(0, 6, 12)(j)
  18.         If SS(j) <> Arr(R, C) Then
  19.            Ra(SS(0), C).Interior.ColorIndex = 6
  20.            Rb(R, C).Interior.ColorIndex = 6
  21.         End If
  22.     Next j
  23.     xD.Remove T
  24. 102: Next
  25. '------------------------------
  26. For Each SS In xD.items
  27.    R = Val(Split(SS & "||", "|")(0))
  28.    If R > 0 Then Union(Ra(R, 6), Ra(R, 12)).Interior.ColorIndex = 43
  29. Next
  30. End Sub
½Æ»s¥N½X
Xl0000066.rar (14.62 KB)


=============================

TOP

¦^´_ 3# ­ã´£³¡ªL
ª©¥D «D±`ÁÂÁÂ

TOP

        ÀR«ä¦Û¦b : §Ñ¥\¤£§Ñ¹L¡A§Ñ«è¤£§Ñ®¦¡C
ªð¦^¦Cªí ¤W¤@¥DÃD