如何在Google Sheets中制作组合X轴标签图表并优化数据录入？

问题背景 #

坚持每日晨起称重2年，在Google Sheets中用矩阵式表格（日期行+月份列）记录体重数据，格式如下：

原有矩阵式记录表格 #

为了制作X轴显示「1月1日、1月2日」这类组合标签的图表，将数据转换为两列格式：

转换后的两列表格（用于图表） #

但因数据量达2年，每次录入新数据需滚动到表格底部，操作繁琐，以下是几种解决办法：

解决方案 #

1. 保留矩阵式录入，用公式自动生成图表数据源 #

不用手动维护两列表格，在新工作表中用公式将矩阵数据自动转换为连续的日期-体重对，录入仅需在原矩阵表格操作，图表数据源自动更新。

假设原矩阵表格名为记录，A列是日期（1-31），B列起为各月份（B=1月、C=2月…），在新工作表的A1单元格输入公式（根据实际年份调整）：

=QUERY(FLATTEN(ARRAYFORMULA(IF(记录!B2:Z32<>"", 记录!A2:A31&"月"&REGEXEXTRACT(记录!B1:Z1,"\d+")&"日"&"2024", "")&"|"&记录!B2:Z32)), "SELECT SPLIT(Col1, '|') WHERE Col1 <> '' LABEL SPLIT(Col1, '|') '日期', '体重'")

公式逻辑：

• FLATTEN将矩阵数据转为一维数组
• ARRAYFORMULA批量生成「X月X日YYYY」格式的日期字符串，与体重用|拼接
• QUERY过滤空值并拆分字符串为两列，直接作为图表数据源

2. 用表单录入自动同步数据 #

创建Google表单，提交后数据自动追加到表格底部，无需手动滚动：

• 打开体重表格，点击菜单栏「工具」→「创建表单」
• 添加「日期」字段（设为日期类型，默认可选当前日期）、「体重」字段（设为数字类型）
• 保存表单，后续直接通过表单链接录入数据，数据自动同步到表格，图表引用整个数据范围（如A:B）即可自动包含新数据

3. 顶部录入框+脚本自动追加 #

在表格顶部设置临时录入区域，用脚本一键将数据追加到底部：

• 在表格顶部插入两行，A1填「今日日期」、B1填「今日体重」，A2、B2作为录入框
• 点击菜单栏「扩展程序」→「Apps脚本」，粘贴以下代码：

function addWeightData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("数据"); // 替换为你的数据工作表名
  const inputDate = sheet.getRange("A2").getValue();
  const inputWeight = sheet.getRange("B2").getValue();
  
  if (inputDate && inputWeight) {
    sheet.appendRow([inputDate, inputWeight]);
    sheet.getRange("A2:B2").clearContent(); // 清空录入框
  }
}

• 保存脚本，回到表格插入绘图按钮（插入→绘图），将按钮关联到addWeightData函数
• 录入数据后点击按钮，即可自动追加到底部并清空录入框

4. 直接用矩阵数据生成目标图表 #

无需转换数据格式，调整图表设置即可实现X轴标签效果：

• 选中矩阵数据区域插入折线图/散点图
• 在图表编辑器中，将「日期」列设为维度，各月份列设为指标
• 切换到「自定义」标签，在「X轴」设置中选择自定义标签，用公式（如=A2&"月"&B1&"日"）生成组合标签

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