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

[µo°Ý] ¦p¦ó·j´M¯S©w¤é´Á

[µo°Ý] ¦p¦ó·j´M¯S©w¤é´Á

A1~A20¬Ò¬°¤é´Á®æ¦¡(¨Ò¦p2015/4/15),¦p¦ó·j´M¯S©w¤ë¥÷?
¨Ò¦p2015¦~6¤ë

¦^´_ 1# acdx


°ÝÃD´y­z¤Ó²²¤¡A»¡¤£©w¦^ÂЪ̥´ªº¦r³£¤ñ±z¦h¡I^^
´£¥X¦h¤@¨Ç¤£¦P¹ê¨Ò¨Ã¼ÒÀÀ¥X©Ò­nªºµ²ªG¬Ý¬Ý¡A
³Ì¦n¤WªþÀɧa¡I

TOP

¼Ó¥DªºExcelª©¥»¬O2010,¥Î´¶³qªº¿z¿ï§Y¥i

TOP

¦^´_ 1# acdx

§A¥i¥H°µ»²§UÄæ
¨Ò¦p
A1¬O¤é´Á
B2¤½¦¡ =MONTH(A1)
¤½¦¡¤U¨ê

¥ÎBÄæ´N¯à¿z¿ï¤ë¥÷
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ acdx ©ó 2015-9-10 14:56 ½s¿è

data base.zip (142.07 KB)
§Ú¼g¤F¤U¦Cµ{¦¡¨Ó·j´M¯S©w¤é´Áªº¸ê®Æ
  1. Sub ¿z¿ï()
  2. Dim St, Col, Ed As Integer

  3. Set DateRange = Worksheets(1).Range("A1").CurrentRegion.Columns(1)
  4. St = Range("A2").Row
  5. Col = DateRange.End(xlToRight).Column
  6. Ed = DateRange.End(xlDown).Row
  7. DateRange.EntireRow.Hidden = False

  8. For i = St To Ed
  9.     If Year(Cells(i, 1)) <> 2009 Then
  10.         Rows(i).EntireRow.Hidden = True
  11.     End If
  12. Next i
  13. End Sub
½Æ»s¥N½X
¦ý¬O³oµ{¦¡°õ¦æªº³t«×»·§C©ó¨Ï¥ÎExcel¤º«Øªº¿z¿ï¥\¯à¡A¸Ó¦p¦ó¼g¤~¯à¹F¨ì¤º«Ø¿z¿ï¥\¯àªº³t«×?

TOP

¦^´_ 5# acdx


¢°¡D³v¦C³B²z¡]ÁôÂáD§R°£...µ¥°Ê§@¡^¡A³t«×¥²©wºC¡A
¡@¡@­Y¹J¤£±o¤£³v¦C°õ¦æ°Ê§@¡A³q±`­n¥[ Application.ScreenUpdating = False¡AÃö³¬¿Ã¹õ§ó·s
¢±¡D¤º«Ø¥\¯à¥²µM¦³¨ä§óÀu¤Æªº³B²z¤èªk¡A©Ò¥H¡A¾¨¶q§Q¥Î¤º«Ø¥\¯à¨Ó³B²z¸ê®Æ
¢²¡D¿z¿ï¦³®É¨S¦³³Ì²«Kªº¤èªk¡A¨Ò¦p¥»ÃD¢ÏÄ欰¡e¤é´Á¡f®æ¦¡¡A­Y·Q¥u°w¹ï¡e¦~¡f©Î¡e¤ë¡f¿z¿ï¡A
¡@¡@±o¨Ï¥Î¡e»²§UÄ椽¦¡¡f¸û¦³®Ä¯q¡C
¡@
¥H¤U¬°¨Ï¥Î¡eÀx¦s®æÁp¶°¡f¤èªk¡A¤@¦¸©ÊÁôÂæC¡AUnion »P«ö Ctrl ¸õ¿ïÀx¦s®æ¤èªk¬Û¦P¡A
¦ý³Ì¦h¥iÁp¶°¦h¤Ö­ÓÀx¦s®æ¡A©|¥¼´ú¸Õ¨ä·¥­­¡A°Ñ¦Ò§Y¥i¡G
  1. Sub ¿z¿ï()
  2. Dim xR As Range, xU As Range
  3. Cells.EntireRow.Hidden = False
  4. For Each xR In Range([A2], Cells(Rows.Count, 1).End(3))
  5. ¡@¡@If Year(xR) <> 2009 Then If xU Is Nothing Then Set xU = xR Else Set xU = Union(xU, xR)
  6. Next
  7. If Not xU Is Nothing Then xU.EntireRow.Hidden = True
  8. End Sub
½Æ»s¥N½X
¡@

TOP

¦^´_ 5# acdx


¢µ¼Óµ{¦¡¹J¤j¸ê®Æ¡A³t«×ÁÙ¬O¤ÓºC¡A¥H¤U¹B¥Î¡e°}¦C+»²§UÄæ¡f¡A³t«×À³¥i§Ö¨Ç¡G
¢°¡D¥ý¥H°}¦C¦s©ñÀË´úµ²ªG
¢±¡D±NÀË´úµ²ªG©ñ¦b¢ÔÄæ¡]©Î¶¢¸m¥ô¤@Äæ¤]¥i¡^¡A
¡@¡@§Q¥Î¡e¨ì¡D¯S®í¡D±`¼Æ¡D¼Æ¦r¡f¡A¿ï¨ú¦³®Ä¼Æ¦rÀx¦s®æ¡A¦A¤©¥HÁôÂÃ
¡@
  1. Sub ¿z¿ï2()
  2. Dim Arr, i&
  3. Cells.EntireRow.Hidden = False
  4. Arr = Range([A2], Cells(Rows.Count, 1).End(3))
  5. For i = 1 To UBound(Arr)
  6. ¡@¡@If Year(Arr(i, 1)) <> 2009 Then Arr(i, 1) = 1 Else Arr(i, 1) = ""
  7. Next i
  8. With [F2].Resize(UBound(Arr))
  9. ¡@¡@.Value = Arr
  10. ¡@¡@On Error Resume Next
  11. ¡@¡@.SpecialCells(xlCellTypeConstants, 1).EntireRow.Hidden = True
  12. ¡@¡@.Clear
  13. End With
  14. End Sub
½Æ»s¥N½X
¡@

TOP

¦^´_ 7# ­ã´£³¡ªL
½Ð°ÝCells(Rows.Count, 1).End(3)--->¥Nªíªº¬O?¬°¦ó¬OEnd(3)?

TOP

¦^´_ 8# acdx


End(3) §Y¬O End(xlUp)
¦³®É¡e±`¼Æ¡f¥i¨Ï¥Î¡e¥N½X¡f¡A¶i VBE »¡©úÀɬݬݡA¨ä¹ê³o»¡©úÀɫܦn¥Î¡A¦³«Ü¦h½d¨Ò¡A¬O¾Ç²ßªº¦n©Ò¦b¡I

TOP

        ÀR«ä¦Û¦b : ¤Ó¶§¥ú¤j¡B¤÷¥À®¦¤j¡B§g¤l¶q¤j¡A¤p¤H®ð¤j¡C
ªð¦^¦Cªí ¤W¤@¥DÃD