問題描述
如何使用度量單位對類似項目進行分組和添加 (How to group and add similar items with a unit of measurement)
我有以下電子表格。我需要壓縮這個電子表格的內容。我想知道是否可以將類似的項目（如 1952）分組並在下一列中添加數字並輸出 5m / 2w 或類似的東西？1951 年將被壓縮到 9w。這些數據不斷變化，並且經常添加新的參考編號。
參考解法
方法 1:
Sheets doesn't do well adding numbers when they're right next to letters, so you'll need to split those cells into a number column and letter column using left() and right(). Put the number from left() inside the value() function as well so Sheets knows it's a number.
Once you have the helper columns made, you can use query() to consolidate and sum up your values. Query language can get tricky so I recommend the reference page. Once you have the sums and letters spit out, you can concatenate them in another column (J for me).
方法 2:
=ARRAYFORMULA(SUBSTITUTE(TRIM(SPLIT(TRANSPOSE(QUERY(TRANSPOSE({
SORT(UNIQUE(INDIRECT("B2:B"&COUNTA(B2:B)+1)&"♦")),IF(ISNUMBER(
QUERY(QUERY(TO_TEXT(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(IFERROR({
B2:B&"♦", REGEXEXTRACT(C2:C, "\d+")*1, REGEXEXTRACT(C2:C, "\d+(.+)")}),
"select Col1,sum(Col2),Col3
where Col3 is not null
group by Col1,Col3
label sum(Col2)''", 0)),,999^99)), "♦")),
"select count(Col1)
group by Col1
pivot Col2", 0), "offset 1", 0)), SUBSTITUTE(
QUERY(QUERY(TO_TEXT(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(IFERROR({
B2:B&"♦", REGEXEXTRACT(C2:C, "\d+")*1, REGEXEXTRACT(C2:C, "\d+(.+)")}),
"select Col1,sum(Col2),Col3
where Col3 is not null
group by Col1,Col3
label sum(Col2)''", 0)),,999^99)), "♦")),
"select count(Col1)
group by Col1
pivot Col2", 0), "limit 0", 1), " ", ), )}),,999^99)), "♦")), " ", " / "))
spreadsheet demo
(by AHCB、Luke Allpress、player0)