提示:其它语言是由 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。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
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 · 5 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 · 4 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 · 6 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 · 10 months 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 · 1 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 · 1 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