时间戳

首页 > 解决方案 > 谷歌表格中的销售计划仪表板

google-sheets - 谷歌表格中的销售计划仪表板

问题描述

测试表：

https://docs.google.com/spreadsheets/d/1t_hjD6Yx7iv_a9tcgETf82cZbl2cdV8vd0PuTQXZP6Y/edit?usp=sharing

客观的：

我正在尝试为我的一位客户创建一个自动计划表。工作表的想法是：

当前图纸设置：

单元格B1是工作表中唯一的手动输入。对于 之间的表A4:G10，以绿色突出显示的列是固定值。成本列B4:B10和销售额列G4:G10是计算字段。目标是计算潜在客户列的值，以使单元格中的销售额列的总和G10等于单元格中的销售目标B1。

计算每个渠道的潜在客户的约束：

每个广告平台都有它可以产生的最大潜在客户数量，如列中所示E4:E10。因此，在此测试表中，Facebook 可以生成的最大潜在客户数为 500，必应为 1000，依此类推。

广告平台按列中显示的每条线索成本递增的顺序列出C4:C10。所以上面的渠道是最便宜的，而下面的渠道是最贵的。目标是首先从最便宜的渠道最大化潜在客户，然后转移到下一个渠道，直到实现销售目标。在测试表中，我们需要先从 Facebook 获得 500 个潜在客户，然后从 Google Ads 获得 500 个潜在客户，然后从 Bing 获得 1000 个潜在客户，以此类推，直到 cell 中的总销售额G10等于 cell 中的销售目标B1。

我对此有点迷茫，如果您能与我分享任何帮助，我将不胜感激！

标签： google-sheetsgoogle-sheets-formulagoogle-sheets-api

解决方案

我想出了如何做我需要的事情。我添加了一个按钮来触发脚本函数calculate()进行计算。它是一个非常基本的脚本，我将发布另一个问题来确定它是否可以优化 - 我的实际工作表具有动态数量的广告平台，我仍然需要脚本来适应它。

脚本：

function calculate() {

  // Get the planning sheet  
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = spreadsheet.getSheetByName('Planner')

  // Get contract target as the input
  var target = sheet.getRange('B1').getValue()

  // Clear the pin cards list
  sheet.getRange('D11:D15').clearContent()

  // Set the value of the first channel
  firstChannelValue(sheet, target)

  // Set the value of the second channel
  secondChannelValue(sheet, target)

  // Set the value of the third channel
  thirdChannelValue(sheet, target)

  // Set the value of the fourth channel
  fourthChannelValue(sheet, target)

  // Set the value of the fifth channel
  fifthChannelValue(sheet, target)

}

function firstChannelValue(sheet, target) {

  // Max number of pin cards
  var pcLimit = sheet.getRange('E11').getValue()

  // Conversion rate
  var convRate = sheet.getRange('F11').getValue()

  // Cell that needs to be filled
  var pinCard = sheet.getRange('D11')

  // Max possible value
  var maxValue = pcLimit*convRate

  // Set value of cell  
  if (maxValue <= target) {  
    pinCard.setValue(pcLimit)    
  }
  else {
    pinCard.setValue(target/convRate)
  }  
}


function secondChannelValue(sheet, target) {

  // Max number of pin cards
  var pcLimit = sheet.getRange('E12').getValue()

  // Conversion rate
  var convRate = sheet.getRange('F12').getValue()

  // Cell that needs to be filled
  var pinCard = sheet.getRange('D12')

  // Max possible value
  var maxValue = (pcLimit*convRate)

  // Value of first channel contracts
  var firstChannel = sheet.getRange('G11').getValue()

  // Revised target
  var revisedTarget = target - firstChannel

  // Set value of cell  
  if (maxValue <= revisedTarget) {  
    pinCard.setValue(pcLimit)    
  }
  else {
    pinCard.setValue(revisedTarget/convRate)
  }    
}


function thirdChannelValue(sheet, target) {

  // Max number of pin cards
  var pcLimit = sheet.getRange('E13').getValue()

  // Conversion rate
  var convRate = sheet.getRange('F13').getValue()

  // Cell that needs to be filled
  var pinCard = sheet.getRange('D13')

  // Max possible value
  var maxValue = (pcLimit*convRate)

  // Value of previous channel contracts
  var firstChannel = sheet.getRange('G11').getValue()
  var secondChannel = sheet.getRange('G12').getValue()

  // Revised target
  var revisedTarget = target - firstChannel - secondChannel

  // Set value of cell  
  if (maxValue <= revisedTarget) {  
    pinCard.setValue(pcLimit)    
  }
  else {
    pinCard.setValue(revisedTarget/convRate)
  }    
}


function fourthChannelValue(sheet, target) {

  // Max number of pin cards
  var pcLimit = sheet.getRange('E14').getValue()

  // Conversion rate
  var convRate = sheet.getRange('F14').getValue()

  // Cell that needs to be filled
  var pinCard = sheet.getRange('D14')

  // Max possible value
  var maxValue = (pcLimit*convRate)

  // Value of previous channel contracts
  var firstChannel = sheet.getRange('G11').getValue()
  var secondChannel = sheet.getRange('G12').getValue()
  var thirdChannel = sheet.getRange('G13').getValue()

  // Revised target
  var revisedTarget = target - firstChannel - secondChannel - thirdChannel

  // Set value of cell  
  if (maxValue <= revisedTarget) {  
    pinCard.setValue(pcLimit)    
  }
  else {
    pinCard.setValue(revisedTarget/convRate)
  }    
}


function fifthChannelValue(sheet, target) {

  // Max number of pin cards
  var pcLimit = sheet.getRange('E15').getValue()

  // Conversion rate
  var convRate = sheet.getRange('F15').getValue()

  // Cell that needs to be filled
  var pinCard = sheet.getRange('D15')

  // Max possible value
  var maxValue = (pcLimit*convRate)

  // Value of previous channel contracts
  var firstChannel = sheet.getRange('G11').getValue()
  var secondChannel = sheet.getRange('G12').getValue()
  var thirdChannel = sheet.getRange('G13').getValue()
  var fourthChannel = sheet.getRange('G14').getValue()

  // Revised target
  var revisedTarget = target - firstChannel - secondChannel - thirdChannel - fourthChannel

  // Set value of cell  
  if (maxValue <= revisedTarget) {  
    pinCard.setValue(pcLimit)    
  }
  else {
    pinCard.setValue(revisedTarget/convRate)
  }    
}

推荐阅读