Google Sheets中英日期时间转换及透视表查找公式问题求助

错误原因分析 #

1. FILTER公式错误：No matches are found 本质是匹配值类型不统一（比如F5是文本日期，透视表表头是日期值），或者你固定了透视表的行范围（$C$3:$H$3），但目标日期对应的是透视表的其他行，导致找不到匹配项。
2. GETPIVOTDATA公式错误：你嵌套的FILTER返回了多维度范围，不符合GETPIVOTDATA的参数要求，且参数逻辑混乱（"Date"和"Start Time"的搭配不符合透视表字段结构）。

方案一：跳过透视表，直接在原工作表生成目标格式（更高效） #

假设原表结构：

• F列：原始英国日期（dd/mm/yyyy，确保是日期值，不是文本）
• E列：原始24小时制开始时间

1. 提取唯一日期并转美制格式 #

在新单元格（比如G1）输入公式，生成唯一的美制日期：

=TEXT(UNIQUE(F:F),"mm/dd/yyyy")

公式说明：UNIQUE提取不重复日期，TEXT将日期格式转为mm/dd/yyyy的文本格式（也可直接设置单元格格式为mm/dd/yyyy，用=UNIQUE(F:F)即可）。

2. 提取对应日期的1-4个时间并转12小时制 #

在G2单元格（对应第一个日期的第一个时间）输入公式，按Ctrl+Shift+Enter（旧版Excel）或直接回车（Excel 365/2021），然后向右拖动到J列（最多4个时间），向下拖动到所有日期行：

=TEXT(INDEX($E:$E,SMALL(IF($F:$F=$G$1,ROW($F:$F)),COLUMN(A:A))),"h:mm AM/PM")

公式说明：

• IF($F:$F=$G$1,ROW($F:$F))：筛选出当前日期对应的所有行号
• SMALL(...,COLUMN(A:A))：按顺序取第1、2、3、4个行号（向右拖动时COLUMN(A:A)自动变为COLUMN(B:B)等）
• INDEX：根据行号提取对应时间
• TEXT：将24小时制时间转为h:mm AM/PM格式

Excel 365/2021简化版

用动态数组公式直接横向排列所有对应时间：

=TOROW(TEXT(FILTER($E:$E,$F:$F=F5),"h:mm AM/PM"),,TRUE)

公式说明：FILTER筛选对应日期的时间，TEXT转格式，TOROW将纵向结果转为横向，自动填充右侧单元格。

方案二：修复透视表取数公式 #

假设透视表结构：

• 行字段：Date（英国日期，日期值）
• 列字段：时间序号（比如Time Index，1-4，用于区分同一日期的多个时间）
• 值字段：Start Time（已设置为h:mm AM/PM格式）

1. 修复FILTER公式 #

先确保F5的日期和透视表的行日期是同一类型（都是日期值），然后修改公式为：

=FILTER('Pivot Table 1'!$C3:$H3, 'Pivot Table 1'!$B$3:$B=F5)

公式说明：用$C3:$H3（相对行）匹配透视表中对应F5日期的那一行数据，$B$3:$B是透视表的日期行范围。

2. 正确使用GETPIVOTDATA #

用GETPIVOTDATA精准提取对应日期的第n个时间：

=GETPIVOTDATA("Start Time", 'Pivot Table 1'!$A$1, "Date", F5, "Time Index", COLUMN(A:A))

公式说明：

• "Start Time"：透视表的值字段名称
• 'Pivot Table 1'!$A$1：透视表的左上角单元格
• "Date", F5：匹配的行字段和值
• "Time Index", COLUMN(A:A)：匹配的列字段和值（向右拖动时自动取1、2、3、4）

关键注意事项 #

• 统一日期类型：如果原始日期是文本格式（比如"01/05/2024"），先用DATE(RIGHT(F5,4),MID(F5,4,2),LEFT(F5,2))转换为日期值，再进行匹配。
• 透视表字段一致性：确保透视表的行/列字段名称和公式中引用的完全一致（包括大小写、空格），否则GETPIVOTDATA会失效。

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