基于兩列值的區分-Google電子表格

我有以下挑戰。

你能做的最好的事情就是看照片，這樣你就可以更好地理解它。我想在 Google 電子表格中動態顯示預訂約會之間的區別。

存在以下任命： = 新銷售和贏回。

1. New Sales = 是新的約會，如果之前沒有，所以沒有“lead_id”，或者如果已經有“lead_id”，則約會時間必須超過過去30天。
2. Winback = 如果在 30 天內再次預訂相同的“lead_id”。

所以我想要如果“lead_id”存在，那么“lead_id”的最后一個日期->“oppt_booked_on”減去當前日期“oppt_booked_on”。

我嘗試了很多，vlookp，匹配函式等。但我就是無法撰寫動態公式。始終使用最后一次預訂也很重要，如示例（照片）所示。我希望有一個人可以幫助我。非常感謝。

uj5u.com熱心網友回復：

不確定這是否完全是您要找的，但是以下公式：

=datedif(index(sort(filter(F:F,A:A=A2)),match(F2,sort(filter(F:F,A:A=A2)),0)-1),F2,"d")

在“Days between_booked on”列（您的 J 列）中，將回傳該行的日期與同一 Lead_id 的前一個實體的日期（A 列）之間的天數。

此后，以下公式：

=if(and(countif(A:A,A2)>1,isbetween(J2,1,30)),"Winback","New Sales")

如果當前行的 Lead_id 實體超過 1 個，并且當前行與同一 Lead_id 的前一個實體之間的持續時間小于或等于，則在“結果”（您的第一列）中將回傳“Winback”到 30 天。

uj5u.com熱心網友回復：

不確定它是否有意義，但這是腳本：

function main() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getActiveSheet();
  const range = sh.getDataRange();
  const [header, ...rows] = range.getValues();

  // get indexes of the columns
  const col = {};
  col.id      = header.indexOf('lead_id')
  col.outcome = header.indexOf('Outcome');
  col.date    = header.indexOf('oppt_booked_on');
  col.days    = header.indexOf('Days Between booked_on');

  
  // create the object 'data' from the rows
    
  const data = {};
  for (let row in rows) {
    let id = rows[row][col.id];
  
    try {
      data[id].dates.push(rows[row][col.date]);
    }
    catch(e) {
      data[id] = {
        outcome: 'New Sales',
        days: '',
        dates: [rows[row][col.date]],
      }
    }
  }
  
  // change the rows
  
  for (let row in rows) {
    let id = rows[row][col.id];
    let obj = data[id];

    rows[row][col.outcome] = obj.outcome;
    rows[row][col.days] = obj.days;

    if (obj.dates.length > 1) {
      obj.days = days_between(obj.dates.shift(), obj.dates[0]);
      obj.outcome = (obj.days < 30) ? 'Winback' : 'New Sales';
    }
  }

  // set the columns 'Outcome' and 'Days...' on the sheet

  const outcome = rows.map(x => [x[col.outcome]]);
  sh.getRange(2, col.outcome 1, outcome.length, 1).setValues(outcome);

  const days = rows.map(x => [x[col.days]]);
  sh.getRange(2, col.days 1, days.length, 1).setValues(days);

}


// function returns number of days between two dates

function days_between(date1, date2) {
  let msec = Math.abs(date1.getTime() - date2.getTime());
  let days = Math.floor(msec/(1000*60*60*24));
  return days;
}

隨意為您的電子表格修改它。

uj5u.com熱心網友回復：

線索分析

我想擁有可以復制和粘貼的資料來測驗此代碼：

function leadAnalysis() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const sr = 2;//data start row
  const vs = sh.getRange(sr, 1, sh.getLastRow() - sr   1, 6).getValues();
  let lo = { lA: [] };
  vs.forEach((r, i) => {
    if (!lo.hasOwnProperty(r[0])) {
      lo[r[0]] = [];
      lo[r[0]].push({ lead_id: r[0], lead_name: r[1], booked_by: r[2], cars_on_search: r[3], sales_segment: r[4], oppt_booked_on: r[5], digital_consulant: r[6], 'Outcome': 'New Sales', days_between_booked_on: 0 });
      sh.getRange(i   sr, 9,1,2).setValues([[lo[r[0]][lo[r[0]].length-1].Outcome,lo[r[0]][lo[r[0]].length-1].days_between_booked_on]]);
      lo.lA.push(r[0]);
    } else {
      let d = DiffInDays1(new Date(lo[r[0]][lo[r[0]].length - 1].oppt_booked_on), new Date(r[5]))
      lo[r[0]].push({ lead_id: r[0], lead_name: r[1], booked_by: r[2], cars_on_search: r[3], sales_segment: r[4], oppt_booked_on: r[5], digital_consulant: r[6], 'Outcome': (d > 30) ? 'New Sales' : 'WinBack', days_between_booked_on: d });
      sh.getRange(i   sr, 9,1,2).setValues([[lo[r[0]][lo[r[0]].length-1].Outcome,lo[r[0]][lo[r[0]].length-1].days_between_booked_on]]);
    }
  })
}
function DiffInDays1(Day1, Day2) {
  if (Day1 && Day2 && (Object.prototype.toString.call(Day1) === '[object Date]') && (Object.prototype.toString.call(Day2) === '[object Date]')) {
    var day = 86400000;
    var t1 = new Date(Day1).valueOf();
    var t2 = new Date(Day2).valueOf();
    var d = Math.abs(t2 - t1);
    var days = Math.floor(d / day);
    //Logger.log(days);
    return days;
  } else {
    throw 'Invalid Inputs';
  }
}

從我自己的輸入中獲得如下資料：

此版本將運行得更快：

function leadAnalysis() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const sr = 2;//data start row
  const vs = sh.getRange(sr, 1, sh.getLastRow() - sr   1, 6).getValues();
  let lo = { lA: [] };
  let oA = [];
  vs.forEach((r, i) => {
    if (!lo.hasOwnProperty(r[0])) {
      lo[r[0]] = [];
      lo[r[0]].push({ lead_id: r[0], lead_name: r[1], booked_by: r[2], cars_on_search: r[3], sales_segment: r[4], oppt_booked_on: r[5], digital_consulant: r[6], 'Outcome': 'New Sales', days_between_booked_on: 0 });
      //sh.getRange(i   sr, 9,1,2).setValues([[lo[r[0]][lo[r[0]].length-1].Outcome,lo[r[0]][lo[r[0]].length-1].days_between_booked_on]]);
      oA.push([lo[r[0]][lo[r[0]].length-1].Outcome,lo[r[0]][lo[r[0]].length-1].days_between_booked_on])
      lo.lA.push(r[0]);
    } else {
      let d = DiffInDays1(new Date(lo[r[0]][lo[r[0]].length - 1].oppt_booked_on), new Date(r[5]))
      lo[r[0]].push({ lead_id: r[0], lead_name: r[1], booked_by: r[2], cars_on_search: r[3], sales_segment: r[4], oppt_booked_on: r[5], digital_consulant: r[6], 'Outcome': (d > 30) ? 'New Sales' : 'WinBack', days_between_booked_on: d });
      //sh.getRange(i   sr, 9,1,2).setValues([[lo[r[0]][lo[r[0]].length-1].Outcome,lo[r[0]][lo[r[0]].length-1].days_between_booked_on]]);
      oA.push([lo[r[0]][lo[r[0]].length-1].Outcome,lo[r[0]][lo[r[0]].length-1].days_between_booked_on]);
    }
  })
  sh.getRange(sr,9,oA.length,oA[0].length).setValues(oA)
}

標籤：谷歌应用程序脚本 谷歌表格 电子表格

上一篇：如何根據單元格的值在單元格下方添加行

下一篇：如何使用DEV鏈接將JSON發布到GoogleApps腳本