如何编写公式提取表格中指定30分钟增量与日期对应的值

Excel 解决方案 #

方法1：INDEX+MATCH组合公式 #

假设表格结构如下：

• X轴日期在第一行（如A1:Z1），格式为标准日期（例如2024/05/20）
• Y轴30分钟时间增量在第一列（如A2:A49，覆盖一天48个时段），格式为时间（例如00:30、01:00）
• 数据区域为B2:Z49

要匹配指定日期（如单元格F1）和指定时间（如单元格F2）的数值，公式如下：

=INDEX(B2:Z49, MATCH(F2, A2:A49, 0), MATCH(F1, B1:Z1, 0))

• MATCH(F2, A2:A49, 0)：精准定位指定时间在Y轴的行号
• MATCH(F1, B1:Z1, 0)：精准定位指定日期在X轴的列号
• INDEX根据行/列坐标返回对应单元格数值

方法2：XLOOKUP（Excel 365及以上版本） #

若使用新版Excel，可使用更简洁的嵌套XLOOKUP：

=XLOOKUP(F2, A2:A49, XLOOKUP(F1, B1:Z1, B2:Z49))

内层XLOOKUP先匹配日期对应的整列数据，外层再匹配时间对应的行数值。

Google Sheets 解决方案 #

方法1：INDEX+MATCH组合 #

语法与Excel完全一致，直接套用：

=INDEX(B2:Z49, MATCH(F2, A2:A49, 0), MATCH(F1, B1:Z1, 0))

方法2：XLOOKUP或QUERY #

• 嵌套XLOOKUP写法：

=XLOOKUP(F2, A2:A49, XLOOKUP(F1, B1:Z1, B2:Z49))

• 复杂场景可选QUERY函数：

=QUERY(B2:Z49, "select Col"&MATCH(F1, B1:Z1, 0)&" where A='"&TEXT(F2, "HH:mm")&"'", 0)

关键注意事项 #

• 确保查询用的日期、时间格式，与表格中对应列的格式完全一致（例如表格时间为00:30，不要用12:30 AM作为查询值）
• 若日期/时间为文本格式，需将查询值统一转为相同格式的文本，或全部转为日期/时间格式
• 存在重复日期/时间时，MATCH会返回第一个匹配项的位置

内容的提问来源于stack exchange，提问作者stang-Spitfire1975