您正在尝试计算列表中提供给每个人的现金折扣总额。您具有从POS系统导出的数据,并已在其中添加了公式以逐行计算折扣的金额。您已推测是否可以使用SUMIFS
公式计算折扣总额。
在我看来,尽管Google表格具有功能,但电子表格的布局和POS报告的格式并不能隔离离散的数据元素(尽管,毫无疑问,比我更熟练的人会反驳这一理论)。包含名称的A列还包括子分组(及其小计)以及交易日期。有83位独特的人员和超过31,900条交易线。
此答案是基于脚本的解决方案,它使用折扣总计的名称和值来更新工作表。执行时间为@ 11秒。
function so5882893202() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
// get the Discounts sheet
var discsheetname = "Discounts";
var disc = ss.getSheetByName(discsheetname);
//get the Discounts data
var discStartrow = 3;
var discLR = disc.getLastRow();
var discRange = disc.getRange(discStartrow,1,discLR-discStartrow+1,9);
var discValues = discRange.getValues();
// isolate Column A
var discnameCol = discValues.map(function(e){return e[0];});//[[e],[e],[e]]=>[e,e,e]
//Logger.log(discnameCol); // DEBUG
// isolate Column I
var discDiscounts = discValues.map(function(e){return e[8];});//[[e],e]
//Logger.log(discDiscounts); // DEBUG
// create an array to build a names list
var names =[]
// get the number of rows on the Discounts sheet
var discNumrows = discLR-discStartrow+1;
// Logger.log("DEBUG: number of rows = "+discNumrows);
// identify search terms
var searchPercent = "%";
var searchTotal = "Total";
// loop through Column A
for (var i=0; i<discNumrows; i++){
//Logger.log("DEBUG: i="+i+",content = "+discnameCol[i]);
// test if value is a date
if (Object.prototype.toString.call(discnameCol[i]) != "[object Date]") {
//Logger.log("it isn't a date")
// test whether the value contains a % sign
if ( discnameCol[i].indexOf(searchPercent) === -1){
//Logger.log("it doesn't have a % character in the content");
// test whether the value contains the word Total
if ( discnameCol[i].indexOf(searchTotal) === -1){
//Logger.log("it doesn't have the word total in the content");
// test whether the value is a blank
if (discnameCol[i] != ""){
//Logger.log("it isn't empty");
// this is a name; add it to the list
names.push(discnameCol[i])
}// end test for empty
}// end test for Total
} // end for percentage
} // end test for date
}// end for
//Logger.log(names);
// get the number of names
var numnames = names.length;
//Logger.log("DEBUG: number of names = "+numnames)
// create an array for the discount details
var discounts=[];
// loop through the names
for (var i=0;i<numnames;i++){
// Logger.log("DEBUG: name = "+names[i]);
// get the first row and last rows for this name
var startrow = discnameCol.indexOf(names[i]);
var endrow = discnameCol.lastIndexOf(names[i]+" Total:");
var x = 0;
var value = 0;
// Logger.log("name = "+names[i]+",start row ="+ startrow+",end row = "+endrow);
// loop through the Cash Discounts Column (Column I) for this name
// from the start row to the end row
for (var r = startrow; r<endrow;r++){
// get the vaue of the cell
value = discDiscounts[r];
// test that it is a value
if (!isNaN(value)){
// increment x by the value
x = +x+value;
// Logger.log("DEBUG: r = "+r+",value = "+value+",x = "+x);
}
}
// push the name and the total discount onto the array
discounts.push([names[i],x]);
}
//Logger.log(discounts)
// get the reporting sheet
var reportsheet = "Sheet10";
var report = ss.getSheetByName(reportsheet);
// define the range (allow row 1 for headers)
var reportRange = report.getRange(2,numnames,2);
// clear any existing content
reportRange.clearContent();
//update the values
reportRange.setValues(discounts);
}
报告表-提取
,
并不是每个人都希望脚本解决他们的问题。该答案旨在提供使用常见的花园品种公式/函数的可重复解决方案。
如其他地方所述,电子表格的布局并不适合快速/简单的解决方案,但是可以分解数据以编译非脚本答案。尽管以下公式似乎比“简单”似乎“看起来”要少,但一次使用它们是合乎逻辑的,非常容易创建,并且非常容易验证成功的结果。
注意:首先要知道数据的第一行=第3行,数据的最后一行=第31916行。
步骤#1-从ColumnA中获取Text
值
- 在单元格J3中输入此公式,然后复制到第31916行
-
=if(isdate(A3),"",A3)
:
- 计算A列,如果内容是日期,则返回空白,否则返回上下文
- 以客户“ AJ”为例,此时的内容包括:
- AJ
- 10%的建筑折扣
- 建筑物总折扣的10%:
- 西北10%
- 西北10%总计:
- AJ总计:
第2步-忽略包含“ 10%”的值(这会删除标题和小计
- 在单元格K3中输入此公式,然后复制到31916行
-
=iferror(if(search("10%",J3)>0,J3),J3)
:在J列中搜索“ 10%”。返回除包含“ 10%”的那些值以外的所有值。
- 以客户“ AJ”为例,此时的内容包括:
**步骤3-忽略包含单词“总计”的值
- 在单元格L3中输入此公式,然后复制到第31916行。
-
=iferror(if(search("total",K3)>0,K3),K3)
- 以客户“ AJ”为例,此时的内容包括:
步骤3之后的结果
您可能会想,“ 不能在单个公式中完成吗?”和/或“ 数组公式会更有效”。这两种想法都是正确的,但是我们正在寻找简单而容易的方法,而一个公式并不简单(如下所示);鉴于此,除非/直到专家可以对数据挥动魔杖,否则数组公式就毫无疑问。
FWIW-合并步骤#1、2和3
- 步骤#1,步骤2和步骤3相互依赖。因此,可以 创建一个将这些步骤组合在一起的公式。
- 在单元格J3中输入此公式,并将dow复制到#31916行。
-
=iferror(if(search("total",iferror(if(search("10%",if(isdate(A3),A3))>0,A3)),A3)))>0,A3))),A3)))
如图所示,第3步以L列中的空白单元格结束;唯一填充的单元格是客户在交易开始时的第一个实例名称,例如本例中的“ Alec”。但是,(@Rubén的道具)可以填充L列中的空白交易单元。Webapps上的An arrayformula to find the previous non-empty cell in another column解释了如何。
步骤4 -为每个交易行创建一个客户名称。
- 在单元格M3中输入此公式,它将自动将单元格填充到行#31916
-
=ArrayFormula(vlookup(ROW(3:31916),{IF(LEN(L3:L31916)>0,ROW(3:31916),""),L3:L31916},2))
步骤5 -获取每笔交易金额的折扣金额
- 折扣值已经显示在列I中。它们已散布在文本值中,因此,通过测试列D中的值,可以确定公式是否为总行。仅当存在淡谷(产品项)时,才进行公式计算,然后测试第一列中是否存在值。
- 在单元格N3中输入此公式,它将自动将单元格填充到行#31916
-
=ArrayFormula(if(len(D3:D31914)>0,if(ISNUMBER(I3:I31916),I3:I31916,0),""))
第5步之后的屏幕截图
按查询报告
报告通过查询完成。这些可以放到任何地方,但将其放在单独的纸上可能更方便。
步骤#6.1 -查询结果以创建显示所有客户总计的报告
-
=query(Discounts_analysis!$M$2:$N$31916,"select M,sum(N) where N is not null group by M label M 'Customer',sum(N) 'Total Discount' ",1)
步骤#6.2 -查询结果以创建显示客户总额的报告客户获得折扣的地方
-
=query(Discounts_analysis!$M$2:$N$31916,sum(N) where N >0 group by M label M 'Customer',1)
步骤#6.3 -查询结果以创建显示没有折扣的客户的报告
- `=query(query(Discounts_analysis!$M$2:$N$31916,1),"select Col1 where Col2=0")`
查询屏幕截图
本文链接:https://www.f2er.com/3113382.html