[µo°Ý] ¨Ì·Ó Date Ä檺¬Û¦P¤é´Á³]©w®æ¦¡¤Æ

maiko

¦p¦ó¨Ì·Ó Date Ä檺¬Û¦P¤é´Á³]©w®æ¦¡¤Æ¡A§â¥¦©³¦â¶ñ¤WÃC¦â¡H¦A­n·s¼W¸ê®Æªº®É­Ô·|¦Û°Ê°Ï¤À¬Û¦P¤é´Áªº´N¦Û°Ê¶ñ¤WÃC¦â¡AÁÂÁ¡I

¤U¸ü (13.52 KB)

2012-12-20 14:25

Book1.rar (4.36 KB)

¤U¸ü¦¸¼Æ: 2

2012-12-20 14:25

maiko

¦pªG¤é´Á¬O¡G
2010-10-01
2010-10-02
2011-11-01
2011-11-15
2011-11-19
2011-11-21
2012-03-14
2012-04-15
2012-05-06
2013-01-23
2013-07-01
2013-12-20

¦p¦ó«ö¦~¥÷§â¥¦®æ¦¡¤Æ¡H

ÁÂÁ¡I

Hsieh

¦^´_ 2# maiko


   ¤é´ÁÄ檽±µ¥Î¤é´Áªº®æ¦¡¤Æ®æ¦¡
²Ä¤@²Õ¦~¥÷
=MOD(SUMPRODUCT((FREQUENCY(YEAR($A$2:$A2),YEAR($A$2:$A2))>0)*1),2)
²Ä¤G²Õ¦~¥÷
=MOD(SUMPRODUCT((FREQUENCY(YEAR($A$2:$A2),YEAR($A$2:$A2))>0)*1),2)=0

¤U¸ü (24.62 KB)

2012-12-20 16:29

maiko

¦^´_  maiko
GBKEE µoªí©ó 2012-12-20 16:43

   
¦pªG¤é´Á¬O¡G
2010-10-01
2010-10-02
2011-11-01
2011-11-15
2011-11-19
2011-11-21
2012-03-14
2012-04-15
2012-05-06
2013-01-23
2013-07-01
2013-12-20

¦p¦ó«ö¦~¥÷§â¥¦®æ¦¡¤Æ¡H

ÁÂÁ¡I

GBKEE

¦^´_ 4# maiko

1. Option Explicit
   
2. Sub Ex1()  '1# ªºµ{¦¡½X
   
3.     Dim xi As Integer
   
4.     With Sheet1
   
5.         .Range("A:A").AdvancedFilter xlFilterCopy, , .Cells(1, .Columns.Count), True
   
6.         '¶i¶¥¿z¿ï: ¤£­«½Æªº¸ê®Æ¨ì³Ì«á¤@Äæ
   
7.         For xi = 2 To .Cells(.Rows.Count, .Columns.Count).End(xlUp).Row
   
8.             '³Ì«á¤@Äæ: 2¦C->³Ì«á¦³¸ê®Æªº¦C,
   
9.             'ª«¥ó¦CªºÁ`¼Æ: Rows.Count,
   
10.             'ª«¥óÄ檺Á`¼Æ:.Columns.Count
    
11.             .Range("A1").AutoFilter 1, .Cells(xi, .Columns.Count)
    
12.             '¦Û°Ê¿z¿ï: ·Ç«h¬O->³Ì«á¤@Ä檺¨C¤@¦C 2010,2011.....
    
13.             .Range("A2", .Range("a2").End(xlDown)).SpecialCells(xlCellTypeConstants).Resize(, 3).Interior.ColorIndex = IIf(xi Mod 2 > 0, 36, 34)
    
14.             '³]©w©³¦â: ¿z¿ï¨ìªº¸ê®Æ
    
15.         Next
    
16.         .Cells(1, .Columns.Count).EntireColumn = ""
    
17.         '²M°£³Ì«á¤@Ä檺¸ê®Æ
    
18.         .AutoFilterMode = False
    
19.         '¤u§@ªí: ¨ú®ø¦Û°Ê¿z¿ï
    
20.     End With
    
21. End Sub
    
22. Sub Ex4() '4# ªºµ{¦¡½X
    
23.     Dim E As Range
    
24.     With Sheet1
    
25.         For Each E In Range("A2", .Range("A2").End(xlDown))
    
26.             E.Resize(, 3).Interior.ColorIndex = IIf(Year(E) Mod 2 > 0, 36, 34)
    
27.         Next
    
28.         
    
29.     End With
    
30.    
    
31. End Sub

½Æ»s¥N½X

maiko

·PÁÂHsieh¡BGBKEE¨â¦ì¤j¤jªº¼ö¤ßÀ°§U¡C