提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

在Excel中輸入或更改日期時如何自動排序日期?

在Excel中, 分類 函數可以幫助您根據需要按升序或降序排序日期。 但它不是動態的,如果你已經對日期進行排序然後添加新的日期,那麼你需要重新排序。 當您每次在工作表中輸入新日期時,是否有自動排序日期的好方法?

輸入日期或使用公式更改日期時自動排​​序日期

使用VBA代碼輸入或更改日期時自動排​​序日期


箭頭藍色右泡 輸入日期或使用公式更改日期時自動排​​序日期


例如,列A中的原始日期,以下公式可幫助您根據要分類的列自動對新幫助程序列中的日期或任何其他文本字符串進行排序,請按如下操作:

1。 輸入這個公式:

=INDEX($A$2:$A$15,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$15,"<="&$A$2:$A$15),0)) 到日期列旁邊的空白單元格中, C2,例如,然後按 Ctrl + Shift + Enter 鍵,然後你將得到一個數字序列,然後把填充手柄拖到你想要使用的單元格中,看截圖:

備註:在上面的公式中: A2:A15 是您想要自動排序的原始日期範圍。

doc按日期1自動排序

2. 然後通過點擊將數字格式化為日期格式 短日期 來自 一般 下拉列表下 主頁 標籤,看截圖:

doc按日期2自動排序

3。 然後序號被轉換成日期格式,並且原始日期也被排序了,見截圖:

doc按日期3自動排序

4。 從現在開始,當你在列A中輸入新的日期或更改日期時,C列中的日期將自動按升序排列,請參見截圖:

doc按日期4自動排序


箭頭藍色右泡 使用VBA代碼輸入或更改日期時自動排​​序日期

當您輸入新日期或根據需要更改日期時,以下VBA代碼可以幫助您在原始列中自動排序日期。

1。 在您輸入或更改日期時,轉到要自動排序日期的工作表。

2。 右鍵單擊工作表選項卡,然後選擇 查看代碼 從上下文菜單中,彈出 Microsoft Visual Basic for Applications 窗口,請將以下代碼複製並粘貼到空白處 模塊 窗口,看截圖:

VBA代碼:輸入日期或更改日期時自動排​​序:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160606
    On Error Resume Next
    If Application.Intersect(Target, Application.Columns(1)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Range("A1").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
                                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

doc按日期6自動排序

備註:在上面的代碼中,輸入的日期將在列A中自動排序,您可以更改 A1 - A2 到你自己的細胞,只要你需要。

3。 從現在起,當您在A欄中輸入日期時,日期將自動升序排列。


Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
kte tab 201905

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Ross · 8 months ago
    With the VBA code, I have copy and pasted the above but wish for the dates in column F to be the values by which the data is sorted. I've changed the range values to F2 and F3500 (the size of the spreadsheet where row 1 is titles), but it still sorts by the dates in column A. Can somebody help me please?
    • To post as a guest, your comment is unpublished.
      skyyang · 7 months ago
      Hello, Ross,
      When applying the code to column F, you should change some references to your need as below code:
      Private Sub Worksheet_Change(ByVal Target As Range)
      'Updateby Extendoffice 20160606
      On Error Resume Next
      If Application.Intersect(Target, Application.Columns(6)) Is Nothing Then Exit Sub
      If Target.Count > 1 Then Exit Sub
      Range("F1").Sort Key1:=Range("F2"), Order1:=xlAscending, Header:=xlYes, _
      OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
      End Sub

      Please try, hope it can help you!
  • To post as a guest, your comment is unpublished.
    Hector · 8 months ago
    Is there a particular formula to keep the cells following the sorted date? It would be nice to organize by date but keep the entire row of information. Any help would be much appreciated.
  • To post as a guest, your comment is unpublished.
    Robert Smith · 1 years ago
    I mad a checkbook register and it works but I want to figure out how to make my entry’s to go into date order. Any help would be appreciated. I’m still learning excel.
  • To post as a guest, your comment is unpublished.
    KC · 1 years ago
    In addition to the duplicate dates, is there also a way to include multiple columns of data when it sorts? I need it to include multiple columns and sort them all together with the expiration dates.
  • To post as a guest, your comment is unpublished.
    Bo · 1 years ago
    how can I do this same sorting calculation but from newest date to oldest? Currently it is Oldest to Newest. Flipping the < sign isn't enough and beyond that I don't have a strong enough understanding of what it is doing. Also I think what may be happening is excel automatically works top to bottom causing difficulties.
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Bo,

      To auto sort the date from newest to oldest, you just need to change the <= to >= in the above formula as follows:
      =INDEX($A$2:$A$15,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$15,">="&$A$2:$A$15),0))
      After inserting this formula, please remember to press Ctrl + Shift + Enter keys together to get the correct result.
      Please try it.
  • To post as a guest, your comment is unpublished.
    Ryan · 2 years ago
    What if there is a duplicate date in the list? And I want both numbers to show up.
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, Ryan,

      To sort the date with duplicate ones, you should apply the following formula:

      =IFERROR(INDEX($A$2:$A$11,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$11,"<="&$A$2:$A$11),0)),IF(ROWS($A$2:A2)<ROWS($A$2:$A$11),B3,""))

      Please remember to press Shift + Ctrl + Enter keys together.

      Hope it can help you, thank you!
  • To post as a guest, your comment is unpublished.
    UrLoser · 2 years ago
    U forgot to mention the formula is array and you need to ctrl+Shift+ enter. Luckily you had a screenshot or your page would be a waste of cyberspace