Google Sheets进阶：如何基于姓名匹配终点计算空驶里程

现有基础实现 #

你当前已完成两点间驾驶里程的计算，具体实现如下：

• 单元格公式：

  =IF(M20 = "", , VALUE(GOOGLEMAPS_DISTANCE(M20, N20, "driving")))
  

• 自定义Apps Script脚本：

  const GOOGLEMAPS_DISTANCE = (origin, destination, mode) => {
    const { routes: [data] = [] } = Maps.newDirectionFinder()
      .setOrigin(origin)
      .setDestination(destination)
      .setMode(mode)
      .getDirections();
  
    if (!data) {
      throw new Error('No route found!');
    }
  
    const { legs: [{ distance: { text: distance } } = {}] = [] } = data;
    return distance.slice(0,-3);
  };
  

空驶里程实现方案 #

要根据A列姓名匹配上一次的终点并计算空驶里程，提供两种可行方案：

方案1：纯公式实现 #

假设数据从第2行开始，A列为姓名，M列为当前起点，N列为当前终点，在目标单元格（如O列）输入以下数组公式：

=IF(M2="","",IF(COUNTIF(A$2:A2,A2)=1,"",VALUE(GOOGLEMAPS_DISTANCE(M2,INDEX(N$1:N1,MATCH(MAX(IF(A$1:A1=A2,ROW(A$1:A1))),ROW(A$1:A1),0)),"driving"))))

输入完成后按 Ctrl+Shift+Enter 确认（新版Google Sheets可直接按回车）。

• 逻辑说明：通过MAX(IF(...))找到当前姓名在之前行的最后一条记录行号，再用INDEX取出对应终点，最后传入里程函数计算。

方案2：优化自定义函数 #

修改脚本，让函数自动完成姓名匹配和历史终点查找，简化单元格公式：

const GOOGLEMAPS_EMPTY_RUN = (name, currentOrigin, sheetName = "Sheet1") => {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const activeRow = sheet.getActiveCell().getRow();
  const data = sheet.getRange(1, 1, activeRow - 1, 14).getValues(); // 假设终点在N列（第14列），按需调整列数
  let lastDestination = null;

  // 遍历当前行之前的记录，抓取同姓名的最后一个终点
  for (const row of data) {
    if (row[0] === name) { // A列是姓名，索引为0，按需调整
      lastDestination = row[13]; // N列索引为13，按需调整
    }
  }

  if (!lastDestination) return ""; // 第一条记录无历史终点，返回空

  // 复用原有里程计算逻辑
  const { routes: [routeData] = [] } = Maps.newDirectionFinder()
    .setOrigin(currentOrigin)
    .setDestination(lastDestination)
    .setMode("driving")
    .getDirections();

  if (!routeData) throw new Error('No route found!');
  
  const { legs: [{ distance: { text: distance } }] } = routeData;
  return distance.slice(0, -3);
};

之后在单元格中调用：

=IF(M20="","",VALUE(GOOGLEMAPS_EMPTY_RUN(A20, M20)))

• 注意：根据你的实际列位置调整脚本中的列索引（姓名列、终点列）和工作表名称。

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