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

°Ï°ì¤ºÅã¥Ü¿é¤J­È¶ñº¡ÃC¦â¤§°ÝÃD

°Ï°ì¤ºÅã¥Ü¿é¤J­È¶ñº¡ÃC¦â¤§°ÝÃD

°ÝÃD1¡G¥þ³¡(¦C2-¦C18)¡A·í¿é¤J¶¶§Ç1-¶¶§Ç6(¤£­«½Æ)«á¡A¤W­z°Ï°ì¬Û¦P¤§­È¥HÀx¦s®æ¶ñº¡¶À¦âÅã¥Ü¡A¨Ã¦b­Ó¼Æ²Î­pÄæ¡A¦b¦U¶¶§Ç¤U¤è²Î­p¥X­Ó¼Æ¡C

°ÝÃD2¡G­­¨î°Ï¶¡(¨Ò¦p¡G¦C10-¦C18)¡A·í¿é¤J¶¶§Ç1-¶¶§Ç6(¤£­«½Æ)«á¡A¤W­z°Ï°ì¬Û¦P¤§­È¥HÀx¦s®æ¶ñº¡¶À¦âÅã¥Ü¡A¨Ã¦b­Ó¼Æ²Î­pÄæ¡A¦b¦U¶¶§Ç¤U¤è²Î­p¥X­Ó¼Æ¡C

°ÝÃD40-¼Ð¥Ü.rar (9.09 KB)

§Æ±æ¤ä«ù!

http://blog.xuite.net/hcm19522/twblog/356570552

TOP

ªþÀɦ³­×¥¿¡A§Úªº°ÝÃDÁöµM¦³¤G¡A¦ý¿é¤J¸ê®Æ¥u¦³¤@¦C
¡A·í¿é¤J¦C½d³ò¤£¦P®É¡A¤~·|§ó°Ê¡A¨Ã«ö¤£¦PÃC¦âÅã¥Ü¡C

°ÝÃD1¡G¥þ³¡(¦C2-¦C18)¡A·í¿é¤J·j´M½d³ò(2-18)¡Aªí¥Ü·j´Mb2¦Üx18¡A¨Ã¶ñ¤J¶¶§Ç1-¶¶§Ç6(¤£­«½Æ)«á¡A¤W­z°Ï°ì¬Û¦P¤§­È¥HÀx¦s®æ¶ñº¡¦U¶¶§Ç¤§ÃC¦âÅã¥Ü¡A¨Ã¦b­Ó¼Æ²Î­pÄæ¡A¦b¦U¶¶§Ç¤U¤è²Î­p¥X­Ó¼Æ¡C

°ÝÃD2¡G¥þ³¡(¦C10-¦C18)¡A·í¿é¤J·j´M½d³ò(10-18)¡Aªí¥Ü·j´Mb10¦Üx18¡A¨Ã¶ñ¤J¶¶§Ç1-¶¶§Ç6(¤£­«½Æ)«á¡A¤W­z°Ï°ì¬Û¦P¤§­È¥HÀx¦s®æ¶ñº¡¦U¶¶§Ç¤§ÃC¦âÅã¥Ü¡A¨Ã¦b­Ó¼Æ²Î­pÄæ¡A¦b¦U¶¶§Ç¤U¤è²Î­p¥X­Ó¼Æ¡C

°ÝÃD40-¼Ð¥Ü.rar (9.47 KB)

§Æ±æ¤ä«ù!

TOP

¦^´_ 1# s7659109


    ³o¼Ë¦³²Å¦X§Aªº»Ý¨D¶Ü¡H
  1. Sub check_it()
  2. Sheets("¤u§@ªí1").Range("B2:x18").Interior.ColorIndex = 0
  3. Sheets("¤u§@ªí1").Range("g21:l21").Clear
  4. For i = 7 To 12
  5.     For Each mrng In Sheets("¤u§@ªí1").Range("B2:x18")
  6.      If mrng.Value = Cells(20, i) Then
  7.       mrng.Interior.ColorIndex = 6
  8.       Cells(20, i).Offset(1, 0) = Cells(20, i).Offset(1, 0).Value + 1
  9.      End If
  10.     Next
  11. Next
  12. End Sub

  13. Sub check_it2()
  14. Sheets("¤u§@ªí1").Range("B2:x18").Interior.ColorIndex = 0
  15. Sheets("¤u§@ªí1").Range("G23:L23").Clear
  16. RngA = Left([c22], Application.Find("-", [c22]) - 1)
  17. RngB = Right([c22], Len([c22]) - (Application.Find("-", [c22])))
  18. For x = 7 To 12
  19.     For Each mrng In Sheets("¤u§@ªí1").Range("B" & RngA & ":x" & RngB)
  20.      If mrng.Value = Cells(22, x) Then
  21.       mrng.Interior.ColorIndex = 6
  22.       Cells(22, x).Offset(1, 0) = Cells(22, x).Offset(1, 0).Value + 1
  23.      End If
  24.     Next
  25. Next
  26. End Sub
½Æ»s¥N½X

TOP

®M¥Îµ{¦¡½X¡A¤´¦³»~¡A°ÝÃD¤w­×¥¿¦p¤U
°ÝÃD1¡G¥þ³¡(¦C2-¦C18)¡A·í¿é¤J·j´M½d³ò(2-18)¡Aªí¥Ü·j´Mb2¦Üx18¡A¨Ã¶ñ¤J¶¶§Ç1-¶¶§Ç6(¤£­«½Æ)«á¡A¤W­z°Ï°ì¬Û¦P¤§­È¥HÀx¦s®æ¶ñº¡¦U¶¶§Ç¤§ÃC¦âÅã¥Ü¡A¨Ã¦b­Ó¼Æ²Î­pÄæ¡A¦b¦U¶¶§Ç¤U¤è²Î­p¥X­Ó¼Æ¡C

°ÝÃD2¡G¥þ³¡(¦C10-¦C18)¡A·í¿é¤J·j´M½d³ò(10-18)¡Aªí¥Ü·j´Mb10¦Üx18¡A¨Ã¶ñ¤J¶¶§Ç1-¶¶§Ç6(¤£­«½Æ)«á¡A¤W­z°Ï°ì¬Û¦P¤§­È¥HÀx¦s®æ¶ñº¡¦U¶¶§Ç¤§ÃC¦âÅã¥Ü¡A¨Ã¦b­Ó¼Æ²Î­pÄæ¡A¦b¦U¶¶§Ç¤U¤è²Î­p¥X­Ó¼Æ¡C

°ÝÃD40-1-¼Ð¥Ü.rar (10.05 KB)

§Æ±æ¤ä«ù!

TOP

¦^´_ 6# s7659109


    ³o¼Ë¤l©O¡H
  1. Sub check_it()
  2. Range("B2:x18").Interior.ColorIndex = 0
  3. Range("G21:L21").Clear
  4. RngA = Left([c20], Application.Find("-", [c20]) - 1)
  5. RngB = Right([c20], Len([c20]) - (Application.Find("-", [c20])))
  6. For x = 7 To 12
  7.     For Each mrng In Sheets("¤u§@ªí1").Range("B" & RngA & ":x" & RngB)
  8.      If mrng.Value = Cells(20, x) Then
  9.       mrng.Interior.ColorIndex = 6
  10.       Cells(20, x).Offset(1, 0) = Cells(20, x).Offset(1, 0).Value + 1
  11.      End If
  12.     Next
  13.   If Cells(21, x) = "" Then Cells(21, x).Value = 0
  14. Next
  15. End Sub
½Æ»s¥N½X

TOP

¢Õ¢±¢°¡G
=COUNTIF(INDIRECT("B"&SUBSTITUTE($C$20,"-",":X")),G20)

¢Ð¢±¡D®æ¦¡¤Æ±ø¥ó¡G
±ø¥ó¢°¡G=(B2=$G$20)*MODE(ROW(),ROW(INDIRECT(SUBSTITUTE($C$20,"-",":"))))¡@¶À©³¦â
±ø¥ó¢±¡ã¢µ¡A¦Û¦æ¥h³]¡ã¡ã
¡]§Úªºª©¥»¥u¯à³]¢²­Ó¡^

TOP

µ{¦¡½X³¡¤À¡A¬O¥i¥H¡A¦ý¥uÅã¥Ü¶À¦â¡A¥i§_«ö¶¶§Ç1~6¤À¦¨¤£¦PÃC¦âÅã¥Ü¡A¥t¥~¨C­×§ï1¦¸
³£­n¨ìµ{¦¡½X¤¤¡A­«·s°õ¦æ1¦¸¡A¤~¯à§ó·s¡A¬O§_ÁÙ¦³¨ä¥L¤è¦¡¡A§ó§ï®É¡A´N¥iª½±µ§ó°Ê¡C
(Àx¦s¬°.xlsm)
§Æ±æ¤ä«ù!

TOP

¥»©«³Ì«á¥Ñ owen06 ©ó 2015-11-13 17:23 ½s¿è

¦^´_ 9# s7659109

¹³³o¼Ë¡H
¥t¥~§Aªºb2:x18¦ü¥G¦]¬°Àx¦s®æ¦³³]©w®æ¦¡¤Æ±ø¥óªºÃö«Y¡A©Ò¥H¦³¨Ç¸ê®Æ¶]¥X¨ÓªºÃC¦â·|¿ù¶Ã¡A§â®æ¦¡¤Æ±ø¥ó²M±¼¤~·|¥¿±`
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. With Target
  3.    If .Row = 20 And .Column >= 7 And .Column <= 12 Then
  4.     RngA = Left([C20], Application.Find("-", [C20]) - 1)
  5.     RngB = Right([C20], Len([C20]) - (Application.Find("-", [C20])))
  6.     Ans = .Value
  7. On Error GoTo 99
  8.    If Application.CountA([G20:L20]) = 0 Then GoTo 99
  9.    If .Value = "" Then .Offset(1, 0) = ""
  10.    
  11.      For Each Mrng In Range("B" & RngA & ":x" & RngB)
  12.        If Mrng.Value = Ans Then
  13.         Mrng.Interior.ColorIndex = .Offset(-1, 0).Interior.ColorIndex
  14.         .Offset(1, 0) = .Offset(1, 0).Value + 1
  15.        End If
  16.      Next
  17.    End If
  18. End With
  19. Exit Sub

  20. 99: Range("b2:x18").Interior.ColorIndex = 0: Range("G21:L21") = ""
  21. End Sub
½Æ»s¥N½X

TOP

«ôŪ "­ã¤j" ,G20:L20¤£­«½Æ¥B¨¾¤î¾ã¦C¬Û¦P
G20:L20{=INDIRECT(TEXT(RIGHT(MIN(IF(COUNTIF($F20:F20,INDIRECT("B"&SUBSTITUTE($C$20,"-",":X")))=0, COLUMN(INDIRECT("B"&SUBSTITUTE($C$20,"-",":X")))+ROW(INDIRECT("B"&SUBSTITUTE($C$20,"-",":X")))*100)),4),"!R0C00"),)

TOP

        ÀR«ä¦Û¦b : «H¤ß¡B¼Ý¤O¡B«i®ð¤TªÌ¨ã³Æ¡A«h¤Ñ¤U¨S¦³°µ¤£¦¨ªº¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD