将来自多个单元格的数据合并为一个 [英] Combining Data From Many Cells into One
问题描述
我在Google电子表格中整理了一个很长的电子表格.该电子表格包含有关产品的信息,例如名称,品牌,零件号等.我之前曾获得过帮助,并给出了一些不错的解决方案,但我仍然遇到限制.
Im putting together a pretty lengthy spreadsheet in google spreadsheets. This spreadsheet contains information about products ie name, brand, part number, ect... I was helped previously and given some nice solutions but I am still running into limitations.
我想做的是根据其他单元格中的信息自动生成一个描述字段.
What I am trying to do is generate, automatically, a description field based on information in other cells.
我在说明"列中使用的公式是="Brand Name"&" "&A3&" "&B3&" "&(joinVals(E3:G3," x "))&" "&K3
The formula I am using in the descriptions column is ="Brand Name"&" "&A3&" "&B3&" "&(joinVals(E3:G3," x "))&" "&K3
joinVals(E3:G3," x ")
将包含Length(E)Width(F)Height(G)的单独列连接在一起,并在两个值之间添加"x".结果是E x F x G
joinVals(E3:G3," x ")
is joining together separate columns containing Length(E) Width(F) Height(G) and adding 'x' between the values. That results in E x F x G
此脚本适用于以上公式
function joinVals( rangeValues, separator ) {
function notBlank(element) {return element != '';}
return rangeValues[0].filter(notBlank).join(separator);
}
但是我仍然收到此错误
此Google用户帐户每秒调用脚本的次数过多.
Script invoked too many times per second for this Google user account.
我想知道是否可以将其作为数组来避免错误,因为此文档包含1000多个ROWS.
I am wondering If I can do this as an array to avoid the error as this doc contains 1000+ ROWS.
说完所有事情,我想实现的目标应该是这样的 品牌"零件编号(A)产品名称(B)尺寸(E xF x G)尺寸(K)
When all is said and done my result I would like to achieve should be something like this "Brand Name" Part Number(A) Product Name(B) Dimensions(E x F x G) Size(K)
我应该运行ARRAY脚本吗?
Should I be running an ARRAY Script?
非常感谢,这个论坛对您有帮助!
Thank you all so much, this forum has been such a help!
推荐答案
可以使用数组解决方案,但是您必须对电子表格中使用公式的方式进行很多更改.最简单的解决方案是直接使用内置电子表格公式.无需为此自定义Apps脚本公式.
It is possible to use an Array solution, but you'd have to change a lot how you use formulas in your spreadsheet. The easiest solution is to use built-in spreadsheet formulas directly. There's no need for a custom Apps Script formula for this.
="Brand Name"&" "&A3&" "&B3&" "&(join(" x ";filter(E3:G3;E3:G3<>"")))&" "&K3
如注释中的 AdamL 所示,这是一个仅使用内置公式的ArrayFormula
解决方案./p>
As shown by AdamL in the comments, here's an ArrayFormula
solution that uses only built-in formulas.
=ArrayFormula(IF(LEN(A3:A),REGEXREPLACE("Brand Name "&A3:A&" "&B3:B&" "&REPT(E3:E&" x ",E3:E<>"")&REPT(F3:F&" x ",F3:F<>"")&REPT(G3:G&" x ",G3:G<>"")&CHAR(9)&" "&K3:K,"( x \t)|\t",""),))
正如我所说,这种ArrayFormula
样式的解决方案也可以在编写自定义Apps脚本时使用.当有(可以说)更简单的内置解决方案(但肯定更快且配额更大)时,我只是不认为这是值得的.
As I said, such ArrayFormula
style of solution can be used when writing custom Apps Script as well. I just don't think that's worth it when there's (arguably) simpler built-in solutions (but surely faster and way larger quotas).
这篇关于将来自多个单元格的数据合并为一个的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!