如何在Google Sheets中按条件自动提取跨表数据并完成收银员汇总？

问题 #

主工作表Sheet1每日录入数据，包含三列：DATE（存在合并行，合并组内所有行共享首行日期）、CASHIER NAME、CASHIER SHORT AMOUNT，同日期内收银员姓名无固定排序。Sheet2用于汇总数据，每行对应唯一日期，各唯一收银员姓名作为列，需自动将Sheet1中对应日期+收银员的短款金额匹配填入对应单元格。

示例Sheet1 #

示例Sheet2 #

解决方案 #

步骤1：预处理Sheet1的合并日期（可选） #

若不想修改Sheet1原始数据可跳过此步；若要让DATE列每行都显示对应日期，在Sheet1空白列（比如D2）输入公式后下拉填充：

=IF(A2<>"",A2,D1)

此公式会自动继承上方最近的非空日期，替代合并单元格的空值。

步骤2：Sheet2生成动态收银员表头 #

在Sheet2的DATE列右侧第一个表头单元格（比如B1）输入数组公式，自动提取Sheet1中所有唯一收银员姓名：

=UNIQUE(Sheet1!C2:C17)

注：若Sheet1数据范围会扩展，可改用Sheet1!C:C，但限定具体范围能提升公式运行效率。

步骤3：填充短款金额的核心公式 #

在Sheet2的B2单元格（对应Jan1的ALICE）输入公式，向右、向下填充至所有单元格：

=XLOOKUP(1,(Sheet1!$A:$A=Sheet2!$A2)*(Sheet1!$C:$C=Sheet2!B$1),Sheet1!$D:$D,"")

如果已完成步骤1的日期预处理，公式可改为：

=XLOOKUP(1,(Sheet1!$D:$D=Sheet2!$A2)*(Sheet1!$C:$C=Sheet2!B$1),Sheet1!$E:$E,"")

说明：XLOOKUP通过「日期匹配」+「收银员姓名匹配」的双重条件，精准返回对应短款金额，无匹配时返回空值。

高效一键填充的数组公式 #

若不想手动下拉填充，在Sheet2的B2单元格输入以下数组公式（新版Google Sheets直接回车，旧版按Ctrl+Shift+Enter），公式会自动覆盖整个数据区域：

=ARRAYFORMULA(IFERROR(VLOOKUP($A2:$A&"|"&B$1:G$1,{Sheet1!$D:$D&"|"&Sheet1!$C:$C,Sheet1!$E:$E},2,FALSE),""))

注：需根据Sheet2的实际日期范围（如$A2:$A5）和表头范围（如B$1:G$1）调整公式中的区域参数。

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