[µo°Ý] ¨ÌÄæ¦ì±ø¥ó¦Û°ÊÅܦ⠻P Àx¦s®æ¤ºªº¤½¦¡¦V¤U¶ñº¡

»k¥ú°j¯T

¥H¤U¦³¦h­Ó±ø¥ó·Q¦P®É¹F¦¨¡A½Ð°Ý«e½úµ{¦¡¸Ó¦p¦ó¼g?

A7Äæ¦ì¤½¦¡¡G

1. =IF(C7="","",LOOKUP(C7,{0,701,1301,1501,1701},{"B","A","S","S+","SS"}))

½Æ»s¥N½X

B7Äæ¦ì¤½¦¡¡G

1. =IF(C7="","",RANK(C7,C:C ))

½Æ»s¥N½X

¢°. ·Q¨Ì¡iC¦C¡j(ª½¦æ)¡@¥h°µ¡i§PÂ_³Ì«á¤@µ§¸ê®Æ¦ì¸m¡j±N¡iA7Äæ¦ì¡j»P¡iB7Äæ¦ì¡j¥H¤UªºÄæ¦ì¦Û°Ê¶ñº¡¡C
¢±. ¨Ì¡iA¦C¡j(ª½¦æ)¥h°µ§PÂ_¡A±N¡iA7Äæ¦ì¡j»P¡iG7Äæ¦ì¡j§ï¦¨¦P¼ËªºÄæ¦ì©³¦â¡A¦V¤UÄæ¦ì¦P¼Ë±ø¥ó¡C

¡° ¦]¦³¦h­ÓÃC¦â±ø¥ó¡A¬GµLªk¥Î¡i³]©w®æ¦¡¤Æ±ø¥ó¡j


¦³°µ¤@­Ó«ü¥O«ö¶s¡Aµ{¦¡½X¦p¤U¡G(§Æ±æ¥i¥H§â¥H¤W±ø¥ó¥þ¥[¶i¨Ó)

1. Private Sub CommandButton1_Click()
   
2. 
   
3.     Rows("6:6").AutoFilter   '¦Û°Ê¿z¿ï(­n¿z¿ï¦Cªº¦ì¸m)
   
4.     Range("A7:M200").Sort Key1:=Range("C7"), Order1:=xlDescending, Header:=xlGuess   '¦Û°Ê±Æ¦C¤j¤p(»¼´î±Æ§Ç)
   
5.     ActiveSheet.AutoFilterMode = False   'Ãö³¬¦Û°Ê¿z¿ï
   
6. 
   
7. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 3# »k¥ú°j¯T

1. For Each E In Rng(1)
   
2.                         'AÄ椽¦¡
   
3.         E.Cells(1, -1) = "=IF(RC[2]="""","""",LOOKUP(RC[2],{0,701,1301,1501,1701},{""B"",""A"",""S"",""S+"",""SS""}))"
   
4.         E.Cells(1, -1) = E.Cells(1, -1).Value
   
5.                         'BÄ椽¦¡
   
6.         E.Cells(1, 0) = "=IF(RC[1]="""","""",RANK(RC[1],C[1]))"
   
7.         E.Cells(1, 0) = E.Cells(1, 0).Value
   
8.         '******** AÄ椽¦¡ªº­È ¦³¦b"±¡³ø°Ï" ¶Ü? ************************
   
9.         '¤£¦b·|¦³¡i°õ¦æ¶¥¿ù»~'91'¡j ªº¿ù»~¬O¡i¨S¦³³]©wª«¥óÅÜÅÜ©ÎWith °Ï¶ôÅܼơj
   
10.       '********************************************************
    
11.         Set Rng(2) = Range("H:H").Find(E.Cells(1, -1), LOOKAT:=xlWhole)
    
12.         If Not Rng(2) Is Nothing Then  '±ø¥ó¦¡§PÂ_: AÄ椽¦¡ªº­È ¦b"±¡³ø°Ï" ¦³§ä¨ì.±Æ°£AÄ椽¦¡ªº­È ¤£¦b"±¡³ø°Ï"ªº¿ù»~
    
13.             E.Cells(1, -1).Interior.ColorIndex = Rng(2).Interior.ColorIndex            
    
14.             E.Cells(1, 5).Interior.ColorIndex = Rng(2).Interior.ColorIndex
    
15.         End If
    
16.     Next

½Æ»s¥N½X

»k¥ú°j¯T

¦^´_ 2# GBKEE


Dear  GBKEE ¡G¡@·PÁ±zªºÀ°§U¡A¦ý°õ¦æ®É¥X²{¡i°õ¦æ¶¥¿ù»~'91'¡j»P¡i¨S¦³³]©wª«¥óÅÜÅÜ©ÎWith °Ï¶ôÅܼơj

¡@¥i§_¦A¦¸À°¦£¡AÁÂÁ±z¡ã

GBKEE

¦^´_ 1# »k¥ú°j¯T
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Private Sub CommandButton1_Click()
   
3.     Dim Rng(1 To 2) As Range
   
4.     Set Rng(1) = Range("c7", Range("c" & Rows.Count).End(xlUp))
   
5.     If Rng(1).Cells(1) = Range("c6") Then MsgBox "¤À¼ÆÄæ ¨S¦³¸ê®Æ": Exit Sub
   
6.    
   
7.     With Range("A6").Resize(Rng(1).Rows.Count + 1, 7)
   
8.         .Interior.ColorIndex = xlNone
   
9.         .Sort Key1:=Range("C6"), Order1:=xlDescending, Header:=xlYes  '¦Û°Ê±Æ¦C¤j¤p(»¼´î±Æ§Ç)
   
10.     End With
    
11.     For Each E In Rng(1)
    
12.                         'AÄ椽¦¡
    
13.         E.Cells(1, -1) = "=IF(RC[2]="""","""",LOOKUP(RC[2],{0,701,1301,1501,1701},{""B"",""A"",""S"",""S+"",""SS""}))"
    
14.         E.Cells(1, -1) = E.Cells(1, -1).Value
    
15.                         'BÄ椽¦¡
    
16.         E.Cells(1, 0) = "=IF(RC[1]="""","""",RANK(RC[1],C[1]))"
    
17.         E.Cells(1, 0) = E.Cells(1, 0).Value
    
18.         Set Rng(2) = Range("H:H").Find(E.Cells(1, -1), LOOKAT:=xlWhole)
    
19.         E.Cells(1, -1).Interior.ColorIndex = Rng(2).Interior.ColorIndex
    
20.         E.Cells(1, 5).Interior.ColorIndex = Rng(2).Interior.ColorIndex
    
21.     Next
    
22. End Sub

½Æ»s¥N½X