将来自多个单元格的数据合二为一 [英] Combining Data From Many Cells into One

问题描述

我在谷歌电子表格中整理了一个相当长的电子表格.该电子表格包含有关产品的信息，即名称、品牌、零件号等...我之前得到了帮助并提供了一些不错的解决方案，但我仍然遇到了限制.

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);
}

但是我一直收到这个错误

However I keep getting this error

每秒为此 Google 用户帐户调用脚本的次数过多.

Script invoked too many times per second for this Google user account.

我想知道是否可以将其作为一个数组来避免错误，因为该文档包含 1000 多个行.

I am wondering If I can do this as an array to avoid the error as this doc contains 1000+ ROWS.

当一切都说完后，我想要达到的结果应该是这样的"品牌名称" 零件编号(A) 产品名称(B) 尺寸(E x F 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 解决方案，它仅使用内置-在公式中.

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",""),))

正如我所说，在编写自定义 Apps 脚本时也可以使用这种 ArrayFormula 风格的解决方案.当有(可以说)更简单的内置解决方案(但肯定更快，更大的配额)时，我认为这不值得.

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屋！