基于多条件填充Excel表格：PERSP_CODE筛选及时间格式问题求助

第一步：统一时间格式（核心前提） #

时间格式混乱是函数匹配失败的根源，先把所有时间转成Excel可识别的统一格式：

• 用TEXT函数标准化文本格式
  在原数据旁插入辅助列，输入公式（假设原时间在A2单元格）：

  =TEXT(A2,"yyyy-mm-dd hh:mm:ss")
  

  下拉填充后，所有时间会变成统一的文本格式，后续匹配直接用这个辅助列作为时间条件。

• 用DATEVALUE+TIMEVALUE转成数值格式
  如果原时间是文本型（比如"2024/5/20 14:30"或"2024-05-20下午2:30"这类混合格式），用公式拆分日期和时间并转为数值：

  =DATEVALUE(LEFT(A2,FIND(" ",A2)-1))+TIMEVALUE(RIGHT(A2,LEN(A2)-FIND(" ",A2)))
  

  转换后得到的是Excel内部的日期时间序列号，函数能准确识别匹配。

• 分列功能批量转换
  选中时间列，点击「数据」→「分列」，选择「分隔符号」，下一步勾选「空格」作为分隔符，把日期和时间分成两列；之后分别对日期列用DATEVALUE、时间列用TIMEVALUE转成数值，最后用=日期列单元格+时间列单元格合并成统一的时间数值列。

第二步：多条件筛选特定PERSP_CODE数据 #

时间格式统一后，用以下方法实现筛选：

• FILTER函数（适合Excel 365/2021及以上）
  假设原数据在Sheet1，A列是PERSP_CODE，B列是统一后的时间，C:Z是其他数据；在新表格的起始单元格输入：

  =FILTER(Sheet1!A:Z, (Sheet1!A:A="你的目标PERSP_CODE")*(Sheet1!B:B>=DATE(2024,5,1))*(Sheet1!B:B<=DATE(2024,5,31)+TIME(23,59,59)))
  

  可根据需求调整时间条件，比如只匹配特定日期：TEXT(Sheet1!B:B,"yyyy-mm-dd")="2024-05-20"。

• INDEX+SMALL+IF数组公式（兼容旧版Excel）
  在新表格第一行输入公式，按Ctrl+Shift+Enter（数组公式确认键），下拉填充直到出现#NUM!：

  =INDEX(Sheet1!A:A, SMALL(IF((Sheet1!A:A="你的目标PERSP_CODE")*(Sheet1!B:B>=DATE(2024,5,1))*(Sheet1!B:B<=DATE(2024,5,31)+TIME(23,59,59)), ROW(Sheet1!A:A)), ROW(A1)))
  

  其他列只需把公式里的Sheet1!A:A换成对应列即可（比如Sheet1!C:C提取第三列数据）。

• Power Query批量处理（大数据更高效）

  1. 选中原数据区域，点击「数据」→「从表格/区域」，导入Power Query编辑器；
  2. 处理时间列：右键时间列→「更改类型」→「日期/时间」，如果自动识别失败，添加自定义列：

     =DateTime.FromText([时间列名], [Format="yyyy-mm-dd hh:mm:ss"])
     

     （根据实际格式调整Format参数，比如"yyyy/mm/dd hh:mm"）；
  3. 添加筛选：点击PERSP_CODE列的筛选按钮，选择目标代码；再对时间列设置范围筛选；
  4. 点击「关闭并上载」，选择加载到新工作表，即可得到筛选后的表格。

内容的提问来源于stack exchange，提问作者YousefMenesy