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

¦p¦ó§PÂ_¬°¦P¤@²Õ(¶i¶¥°ÝÃD¡^

¦p¦ó§PÂ_¬°¦P¤@²Õ(¶i¶¥°ÝÃD¡^

±ø¥ó:·íaÄæbÄæ¤@­P¡A¦ýdÄæ¥X²{¦r¦ê¤£¤@­P®É,¼Ð¥Üx¡A¸Ó¦C¼Ð¥Ü¤Ï¶À

0801.zip (16.7 KB)

§Æ±æ¤ä«ù!

¦^´_ 1# s7659109

§Ú¥ý´y­z¤@¤U§Aªº°ÝÃD¡A
§A¬Ý¤@¤U¬O¤£¬O³o¼Ë¡H

¥ý¥HAÄæ»PBÄ檺¸ê°T°µ¤À²Õ¡A
­Y²Õ¤ºDÄæ¥X²{¨âºØ¥H¤Wªº¸ê°T´NHighlight¡H

TOP

¬O¡Aa.bÄ欰¤@²Õ¡]¬Û¦P¦r¦ê¡^¡A¦ýdÄ檺¦r¦ê¤]­n¦C¤J¦Ò¼{¡A­n¤@­P¡]¨Ò¡Grows(2:12)³£¬Oa ¥HªÅ¥Õªí¥Ü¡^,¦ý­Y¦³¤£¦P¡A¥X²{¤Ï¶À»Px´£¥Ü¡A¦prows(13:18)¦³¤£¦PªÌ
§Æ±æ¤ä«ù!

TOP

¦^´_ 3# s7659109

¼Ð¥Üx¡G
G2¶ñ¤J¡A¨ä¾l¤U©Ô
  1. =IF(COUNTIFS(A:A,A2,B:B,B2)=COUNTIFS(A:A,A2,B:B,B2,D:D,D2),"","x")
½Æ»s¥N½X
¼Ð¥Ü¤Ï¶À¡G
®æ¦¡¤Æ±ø¥ó
¨Ï¥Î¤½¦¡¨M©w®æ¦¡¤Æ
  1. =COUNTIFS($A:$A,$A2,$B:$B,$B2)<>COUNTIFS($A:$A,$A2,$B:$B,$B2,$D:$D,$D2)
½Æ»s¥N½X
½d³ò¡GA2:G27

TOP

¦^´_ 4# naruto018


    ³Ì«á­±¨M©w®æ¦¡¤Æ¥i¥Hª½±µ§PÂ_GÄæ¬O§_¬°"x"

TOP

¦p¦ó§ï¬°vba¤è¦¡¡H
§Æ±æ¤ä«ù!

TOP

¦^´_ 6# s7659109

§ÚVBA¬O©ÓÄò¤§«eªºCode¡A
½Ð°Ñ¦Ò
0801.zip (18.47 KB)
  1. Sub NumberCode()
  2.     Dim i%, Str$, ArrStr$
  3.     i = 2
  4.     ArrStr = "" '²MªÅ¤w°O¿ý¨ìªº±ø¥ó
  5.     Do Until Range("C" & i) = ""
  6.         '±NAÄæ¡BBÄæ¡BDÄæ¦X¨Ö¬°¤@­Ó¦r¦ê§@¬°±ø¥ó
  7.         Str = Range("A" & i) & Range("B" & i)
  8.         '­Y¸Ó±ø¥ó¥¼°O¿ý¹L¡A«h°O¿ý¤§¡C
  9.         If InStr("," & ArrStr & ",", "," & Str & ",") = 0 Then ArrStr = ArrStr & "," & Str
  10.         '¼´¥XStr¦b°}¦CArrStr¤¤ªº¯Á¤Þ­È
  11.         Range("F" & i) = "No. " & UBound(Split(Split(ArrStr, Str)(0), ","))
  12.         i = i + 1
  13.     Loop
  14.     ConsistentJudgment ArrStr, UBound(Split(ArrStr, ","))
  15. End Sub

  16. Sub ConsistentJudgment(ArrStr, R%)
  17.     Dim i%, Str$, N%, StrMeno$
  18.     ReDim ArrMemo(R) As String
  19.     '¬ö¿ýMemo¸ê®ÆºØÃþ
  20.     i = 2
  21.     Do Until Range("C" & i) = ""
  22.         Str = Range("A" & i) & Range("B" & i)
  23.         StrMeno = Range("D" & i)
  24.         N = UBound(Split(Split(ArrStr, Str)(0), ","))
  25.         If InStr("," & ArrMemo(N) & ",", "," & StrMeno$ & ",") = 0 Then ArrMemo(N) = ArrMemo(N) & "," & StrMeno
  26.         i = i + 1
  27.     Loop
  28.     '¦^õX§P©wµ²ªG
  29.     i = 2
  30.     Do Until Range("C" & i) = ""
  31.         Str = Range("A" & i) & Range("B" & i)
  32.         StrMeno = Range("D" & i)
  33.         N = UBound(Split(Split(ArrStr, Str)(0), ","))
  34.         If UBound(Split(ArrMemo(N), ",")) > 1 Then Range("G" & i) = "x"
  35.         i = i + 1
  36.     Loop
  37. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2018-8-13 17:33 ½s¿è

Sub NumberCode()
Dim Arr, i&, xD, T$, TC$
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([D2], [A65536].End(3))
For i = 1 To UBound(Arr)
¡@¡@T = Arr(i, 1) & Arr(i, 2):  TC = xD(T)
¡@¡@If TC = "" Then xD(T) = "|" & Arr(i, 4): GoTo 101
¡@¡@If TC <> "|" & Arr(i, 4) Then xD(T) = "²§±`"
101: Next i
For i = 1 To UBound(Arr)
¡@¡@T = Arr(i, 1) & Arr(i, 2): Arr(i, 1) = ""
¡@¡@If xD(T) = "²§±`" Then Arr(i, 1) = "x"
Next i
[G2].Resize(UBound(Arr)) = Arr
End Sub
¡@
¡@

TOP

ÁÂÁ¤G¦ì¤j¤jªº¦^µª,«e­±ªº°ÝÃDok,¦ýÁÙ¦³¥t¤@°ÝÃD:dÄæ,¥Hvlookup¨ú±o¸ê®Æ(¥u·|¤½¦¡,¤w³]¤F); eÄæ0²MªÅ

0814.zip (20.65 KB)

§Æ±æ¤ä«ù!

TOP

¦^´_ 9# s7659109


With Range("D2:D" & [A65536].End(3).Row)
¡@¡@.Formula = "=IF(ISNA(VLOOKUP(C2,accnum,2,)),"""",VLOOKUP(C2,accnum,2,))"
¡@¡@.Value = .Value
End With
Range("E:E").Replace 0, "", Lookat:=xlWhole

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦pÆp¥Û¡j®É¶¡¹ï¤@­Ó¦³´¼¼zªº¤H¦Ó¨¥¡A´N¦pÆp¥Û¯ë¬Ã¶Q¡F¦ý¹ï·M¤H¨Ó»¡¡A«o¹³¬O¤@§âªd¤g¡A¤@ÂI»ù­È¤]¨S¦³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD