在图纸上的单元格背景中运行公式

Question

我正在尝试制作一张表格，在其中我可以在没有冒号或小数的单元格中键入时间(持续时间(，它显示在带有冒号和小数的同一个单元格中(例如：输入"10342"，单元格读取"1:03.42"(。我在另一个单元格中使用的公式是：

=ARRAYFORMULA(TEXT(AVERAGE(VALUE(IF(D3<>"", TEXT(
IF(IFERROR(      LEFT(D3, LEN(D3)-6))="",      0,         LEFT(D3, LEN(D3)-6))&":"&
IF(IFERROR(RIGHT(LEFT(D3, LEN(D3)-4), 2))="", "00", RIGHT(LEFT(D3, LEN(D3)-4), 2))&":"& 
IF(IFERROR(RIGHT(LEFT(D3, LEN(D3)-2), 2))="", "00", RIGHT(LEFT(D3, LEN(D3)-2), 2))&"."& 
IF(LEN(D3)>1, RIGHT(D3, 2), "0"&D3), "[h]:mm:ss.00"), ))), "[h]:mm:ss.00"))

我尝试过条件格式，但我不是最擅长宏的。这有可能吗？

Answer 1

您显然需要一个onEdit函数。您必须打开电子表格文件中的脚本编辑器，然后复制粘贴我在此处提供的代码片段。使用以下代码，每次编辑"的列a中的单元格时；Sheet1"；您可以将所需的输入格式返回到完全相同的单元格中。如果您输入的值是：10342，则它将变为：1:03.42。如果您输入值是130342，则返回13:03.42。如果您只想编辑第1行之后的单元格(如果您有标题(，则可以在If条件语句中添加condition:row>1.

function onEdit(e) {

var row = e.range.getRow();
var col = e.range.getColumn();

if (col === 1 && e.source.getActiveSheet().getName() === "Sheet1"){

var valu = e.source.getActiveSheet().getRange(row,1).getValue().toString()

if (valu.length == 5){
var result =  valu.slice(0,1)+":"+valu.slice(1,3)+"."+valu.slice(-2);
} 
else if ( valu.length==6) {
var result = valu.slice(0,2)+":"+valu.slice(2,4)+"."+valu.slice(-2);
}
e.source.getActiveSheet().getRange(row,1).setValue(result); 
}

}

不要忘记修改工作表的名称(在我的例子中是"Sheet1"(以及您想要使用的列。如您所见，我的解决方案使用col===1、e.source.getActiveSheet().getRange(row,1).setValue(result)和var valu = e.source.getActiveSheet().getRange(row,1).getValue().toString()。数字1对应于A列。例如，如果要使用D列，则需要将所有这些公式的1替换为4。如果你有任何问题，请在评论中告诉我。