°Ï°ì¤ºÅã¥Ü¿é¤Jȶñº¡ÃC¦â¤§°ÝÃD
- ©«¤l
- 248
- ¥DÃD
- 76
- ºëµØ
- 0
- ¿n¤À
- 331
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- XP
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- kaohsiung
- µù¥U®É¶¡
- 2013-6-10
- ³Ì«áµn¿ý
- 2020-7-3
|
°Ï°ì¤ºÅã¥Ü¿é¤Jȶñº¡ÃC¦â¤§°ÝÃD
°ÝÃD1¡G¥þ³¡(¦C2-¦C18)¡A·í¿é¤J¶¶§Ç1-¶¶§Ç6(¤£«½Æ)«á¡A¤Wz°Ï°ì¬Û¦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¤Wz°Ï°ì¬Û¦P¤§È¥HÀx¦s®æ¶ñº¡¶À¦âÅã¥Ü¡A¨Ã¦bӼƲÎpÄæ¡A¦b¦U¶¶§Ç¤U¤è²Îp¥XӼơC |
|
§Æ±æ¤ä«ù!
|
|
|
|
|
- ©«¤l
- 1392
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 1402
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN
- ³nÅ骩¥»
- 2007
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2015-9-11
- ³Ì«áµn¿ý
- 2024-12-25
|
http://blog.xuite.net/hcm19522/twblog/356570552 |
|
|
|
|
|
|
- ©«¤l
- 248
- ¥DÃD
- 76
- ºëµØ
- 0
- ¿n¤À
- 331
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- XP
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- kaohsiung
- µù¥U®É¶¡
- 2013-6-10
- ³Ì«áµn¿ý
- 2020-7-3
|
ªþÀɦ³×¥¿¡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¤Wz°Ï°ì¬Û¦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¤Wz°Ï°ì¬Û¦P¤§È¥HÀx¦s®æ¶ñº¡¦U¶¶§Ç¤§ÃC¦âÅã¥Ü¡A¨Ã¦bӼƲÎpÄæ¡A¦b¦U¶¶§Ç¤U¤è²Îp¥XӼơC |
|
§Æ±æ¤ä«ù!
|
|
|
|
|
- ©«¤l
- 396
- ¥DÃD
- 12
- ºëµØ
- 0
- ¿n¤À
- 425
- ÂI¦W
- 1
- §@·~¨t²Î
- Win10
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¤¤
- µù¥U®É¶¡
- 2013-7-2
- ³Ì«áµn¿ý
- 2024-8-23
|
¦^´_ 1# s7659109
³o¼Ë¦³²Å¦X§Aªº»Ý¨D¶Ü¡H- Sub check_it()
- Sheets("¤u§@ªí1").Range("B2:x18").Interior.ColorIndex = 0
- Sheets("¤u§@ªí1").Range("g21:l21").Clear
- For i = 7 To 12
- For Each mrng In Sheets("¤u§@ªí1").Range("B2:x18")
- If mrng.Value = Cells(20, i) Then
- mrng.Interior.ColorIndex = 6
- Cells(20, i).Offset(1, 0) = Cells(20, i).Offset(1, 0).Value + 1
- End If
- Next
- Next
- End Sub
- Sub check_it2()
- Sheets("¤u§@ªí1").Range("B2:x18").Interior.ColorIndex = 0
- Sheets("¤u§@ªí1").Range("G23:L23").Clear
- RngA = Left([c22], Application.Find("-", [c22]) - 1)
- RngB = Right([c22], Len([c22]) - (Application.Find("-", [c22])))
- For x = 7 To 12
- For Each mrng In Sheets("¤u§@ªí1").Range("B" & RngA & ":x" & RngB)
- If mrng.Value = Cells(22, x) Then
- mrng.Interior.ColorIndex = 6
- Cells(22, x).Offset(1, 0) = Cells(22, x).Offset(1, 0).Value + 1
- End If
- Next
- Next
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤l
- 248
- ¥DÃD
- 76
- ºëµØ
- 0
- ¿n¤À
- 331
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- XP
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- kaohsiung
- µù¥U®É¶¡
- 2013-6-10
- ³Ì«áµn¿ý
- 2020-7-3
|
®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¤Wz°Ï°ì¬Û¦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¤Wz°Ï°ì¬Û¦P¤§È¥HÀx¦s®æ¶ñº¡¦U¶¶§Ç¤§ÃC¦âÅã¥Ü¡A¨Ã¦bӼƲÎpÄæ¡A¦b¦U¶¶§Ç¤U¤è²Îp¥XӼơC |
|
§Æ±æ¤ä«ù!
|
|
|
|
|
- ©«¤l
- 396
- ¥DÃD
- 12
- ºëµØ
- 0
- ¿n¤À
- 425
- ÂI¦W
- 1
- §@·~¨t²Î
- Win10
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¤¤
- µù¥U®É¶¡
- 2013-7-2
- ³Ì«áµn¿ý
- 2024-8-23
|
¦^´_ 6# s7659109
³o¼Ë¤l©O¡H- Sub check_it()
- Range("B2:x18").Interior.ColorIndex = 0
- Range("G21:L21").Clear
- RngA = Left([c20], Application.Find("-", [c20]) - 1)
- RngB = Right([c20], Len([c20]) - (Application.Find("-", [c20])))
- For x = 7 To 12
- For Each mrng In Sheets("¤u§@ªí1").Range("B" & RngA & ":x" & RngB)
- If mrng.Value = Cells(20, x) Then
- mrng.Interior.ColorIndex = 6
- Cells(20, x).Offset(1, 0) = Cells(20, x).Offset(1, 0).Value + 1
- End If
- Next
- If Cells(21, x) = "" Then Cells(21, x).Value = 0
- Next
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤l
- 2839
- ¥DÃD
- 10
- ºëµØ
- 0
- ¿n¤À
- 2895
- ÂI¦W
- 0
- §@·~¨t²Î
- ¡e²¤¡f
- ³nÅ骩¥»
- ¡e²¤¡f
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¡e²¤¡f
- µù¥U®É¶¡
- 2013-5-13
- ³Ì«áµn¿ý
- 2024-12-25
|
¢Õ¢±¢°¡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¯à³]¢²Ó¡^ |
|
|
|
|
|
|
- ©«¤l
- 248
- ¥DÃD
- 76
- ºëµØ
- 0
- ¿n¤À
- 331
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- XP
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- kaohsiung
- µù¥U®É¶¡
- 2013-6-10
- ³Ì«áµn¿ý
- 2020-7-3
|
µ{¦¡½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) |
|
§Æ±æ¤ä«ù!
|
|
|
|
|
- ©«¤l
- 396
- ¥DÃD
- 12
- ºëµØ
- 0
- ¿n¤À
- 425
- ÂI¦W
- 1
- §@·~¨t²Î
- Win10
- ³nÅ骩¥»
- Office 2016
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¤¤
- µù¥U®É¶¡
- 2013-7-2
- ³Ì«áµn¿ý
- 2024-8-23
|
¥»©«³Ì«á¥Ñ 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±¼¤~·|¥¿±`- Private Sub Worksheet_Change(ByVal Target As Range)
- With Target
- If .Row = 20 And .Column >= 7 And .Column <= 12 Then
- RngA = Left([C20], Application.Find("-", [C20]) - 1)
- RngB = Right([C20], Len([C20]) - (Application.Find("-", [C20])))
- Ans = .Value
- On Error GoTo 99
- If Application.CountA([G20:L20]) = 0 Then GoTo 99
- If .Value = "" Then .Offset(1, 0) = ""
-
- For Each Mrng In Range("B" & RngA & ":x" & RngB)
- If Mrng.Value = Ans Then
- Mrng.Interior.ColorIndex = .Offset(-1, 0).Interior.ColorIndex
- .Offset(1, 0) = .Offset(1, 0).Value + 1
- End If
- Next
- End If
- End With
- Exit Sub
- 99: Range("b2:x18").Interior.ColorIndex = 0: Range("G21:L21") = ""
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤l
- 1392
- ¥DÃD
- 4
- ºëµØ
- 0
- ¿n¤À
- 1402
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN
- ³nÅ骩¥»
- 2007
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2015-9-11
- ³Ì«áµn¿ý
- 2024-12-25
|
«ôŪ "ã¤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"),) |
|
|
|
|
|
|