求和单元格，如果它们不是粗体 [英] Sum cells if they are not bold
问题描述
我对我的Google Apps脚本感到困惑，该脚本旨在仅在这些单元格加粗时计算单元格的总和。
$ b $ pre $函数SumIfNotBold（range，startcol，startrow）{
//从int转换为ALPHANUMERIC
// - 感谢Daniel在http://stackoverflow.com/a/3145054/2828136
var start_col_id = String.fromCharCode（64 + startcol）;
var end_col_id = String.fromCharCode（64 + startcol + range [0] .length -1）;
var endrow = startrow + range.length - 1
//建立范围字符串，然后获得字体权重
var range_string = start_col_id + startrow +：+ end_col_id + endrow
var ss = SpreadsheetApp.getActiveSpreadsheet（）;
var getWeights = ss.getRange（range_string）.getFontWeights（）;
var x = 0;
var value;对于（var j = 0; j
） （getWeights [i] [j] .toString（）！=bold）{
value = range [i] [j];
if（！isNaN（value））{
x + = value;
}
}
}
}
return x;
以下是公式：
=（SumIfNotBold（K2：K100，COLUMN（K2），ROW（K2）））* 1
我有三个主要问题：
- 当我设置触发器在任何编辑时启动此脚本时，我意外收到来自Google Apps的电子邮件称
TypeError：无法从undefined读取属性length。 （第7行，文件
SumIfNotBold）
因此，我该如何解决它？有没有办法忽略这些自动发送的通知？
-
公式不会计算细胞，如果他们在另一个列表上。例如，如果我将公式放在B列表中，但单元格位于列表中，则此脚本在导出错误计算方面无法正常工作。
- <当单元格值更新时，公式推导不是。在这种情况下，我正在刷新公式本身（即，将K2：K50更改为K3：K50，并且一次返回）以获得更新的派生。 >
请帮我解决这个脚本的问题。或者，如果使用新的计算非粗体单元格的总和会更好，那么我会很乐意接受您的新解决方案。 方案
这个脚本的一个版本解决了你提出的一些问题。它被简单调用为 = sumifnotbold（A3：C8）或 = sumifnotbold（Sheet2！A3：C8）另一张纸。
与任何自定义函数一样，如果编辑引用的范围内的条目，它会自动重新计算。
如果您将字体从粗体更改为正常或后退，则 不会自动重新计算。在这种情况下，您可以通过delete-undo来快速刷新该函数。 （即删除某个数字，然后撤消删除操作。）
大多数函数通过解析活动单元格中的公式来获取对传入范围的引用。 警告：这是基于假设该函数是独立使用的， = sumifnotbold（B2：C4）。它不会在 = max（A1，sumifnotbold（B2：C4）等另一个函数内工作。
function sumifnotbold（reference）{
var ss = SpreadsheetApp.getActiveSpreadsheet（）;
var sheet = SpreadsheetApp.getActiveSheet（）;
var formula = SpreadsheetApp.getActiveRange（ ）.getFormula（）;
var args = formula.match（/ = \ w + \（（。*）\）/ i）[1] .split（'！'）;
尝试{
if（args.length == 1）{
var range = sheet.getRange（args [0]）;
}
else {
sheet = ss.getSheetByName（args [0] .replace（/'/ g，''））;
range = sheet.getRange（args [1]）;
}
}
catch（e）{
throw new Error（args.join（'！'）+'不是有效范围'）;
}
//上面的所有内容都是从公式中提取范围
//实际计算现在开始
var weights = range.getFontWeights（）;
var numbers = range.getValues（）;
var x = 0;
为（var i = 0; i< numbers.length; （var j = 0; j< numbers [0] .length; j ++）{
if（weights [i] [j]！=bold&& typeof数字[i] [j] =='数字'）{
x + =数字[i] [j];
}
}
}
return x;
}
I'm confused with my Google Apps script which is purposed to calculate the sum of the cells only if these cells are bold.
Here is the source:
function SumIfNotBold(range, startcol, startrow){
// convert from int to ALPHANUMERIC
// - thanks to Daniel at http://stackoverflow.com/a/3145054/2828136
var start_col_id = String.fromCharCode(64 + startcol);
var end_col_id = String.fromCharCode(64 + startcol + range[0].length -1);
var endrow = startrow + range.length - 1
// build the range string, then get the font weights
var range_string = start_col_id + startrow + ":" + end_col_id + endrow
var ss = SpreadsheetApp.getActiveSpreadsheet();
var getWeights = ss.getRange(range_string).getFontWeights();
var x = 0;
var value;
for(var i = 0; i < range.length; i++) {
for(var j = 0; j < range[0].length; j++) {
if(getWeights[i][j].toString() != "bold") {
value = range[i][j];
if (!isNaN(value)){
x += value;
}
}
}
}
return x;
Here is the formula:
=(SumIfNotBold(K2:K100,COLUMN(K2), ROW(K2)))*1
I have three major concerns:
- When I set up a trigger to launch this script on any edits I accidentally receive an email from Google Apps stating that
TypeError: Cannot read property "length" from undefined. (line 7, file "SumIfNotBold")
Thus, how can I fix it? Are there any ways to ignore these automatically delivered notifications?
The formula doesn't calculate the sum of cells if they are on the other list. For example, if I put the formula on B list but the cells are located on A list then this script doesn't work properly in terms of deriving wrong calculations.
When the cell values are updated the formula derivation is not. In this case I'm refreshing the formula itself (i.e., changing "K2:K50" to "K3:K50" and once back) to get an updated derivation.
Please, help me with fixing the issues with this script. Or, if it would be better to use a new one to calculate the sum in non-bold cells then I'll be happy to accept your new solution.
Here is a version of this script that addresses some of the issues you raised. It is invoked simply as =sumifnotbold(A3:C8) or =sumifnotbold(Sheet2!A3:C8) if using another sheet.
As any custom function, it is automatically recalculated if an entry in the range to which it refers is edited. It is not automatically recalculated if you change the font from bold to normal or back. In this case you can quickly refresh the function by delete-undo on any nonempty cell in the range which it sums. (That is, delete some number, and then undo the deletion.)
Most of the function gets a reference to the passed range by parsing the formula in the active cell. Caveat: this is based on the assumption that the function is used on its own, =sumifnotbold(B2:C4). It will not work within another function like =max(A1, sumifnotbold(B2:C4).
function sumifnotbold(reference) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSheet();
var formula = SpreadsheetApp.getActiveRange().getFormula();
var args = formula.match(/=\w+\((.*)\)/i)[1].split('!');
try {
if (args.length == 1) {
var range = sheet.getRange(args[0]);
}
else {
sheet = ss.getSheetByName(args[0].replace(/'/g, ''));
range = sheet.getRange(args[1]);
}
}
catch(e) {
throw new Error(args.join('!') + ' is not a valid range');
}
// everything above is range extraction from the formula
// actual computation begins now
var weights = range.getFontWeights();
var numbers = range.getValues();
var x = 0;
for (var i = 0; i < numbers.length; i++) {
for (var j = 0; j < numbers[0].length; j++) {
if (weights[i][j] != "bold" && typeof numbers[i][j] == 'number') {
x += numbers[i][j];
}
}
}
return x;
}
这篇关于求和单元格，如果它们不是粗体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
