我想在Google Sheet
中得到逗号分隔变量(已发布项)的值的总和。请见下表;
+-----------------------+-----------+
| Issued Items | SUM |
+-----------------------+-----------+
| A-22, A-22, B-11 | 120 |
+-----------------------+-----------+
| C-33, 11, 22-X | 160 |
+-----------------------+-----------+
| 22-X, D-54, 22 | 110 |
+-----------------------+-----------+
已编辑:每个Item
的Values
将存储在另一个工作表中。我怎样才能得到Issued items
的数量呢?
请注意,Items
可能在单个单元中重复,也可能具有需要作为个体计数的前缀和后缀。
+-----------------------+-------+
| Items | Values | QTY |
+-----------------------+-------+
| A-22 | 50 | 2 |
+-----------------------+-------+
| B-11 | 20 | 1 |
+-----------------------+-------+
| C-33 | 70 | 1 |
+-----------------------+-------+
| D-54 | 40 | 1 |
+-----------------------+-------+
| 11 | 30 | 1 |
+-----------------------+-------+
| 22 | 10 | 1 |
+-----------------------+-------+
| 22-X | 60 | 2 |
+-----------------------+-------+
这将为我节省大量的时间和精力。请提前给予帮助和感谢。
发布于 2019-03-20 19:50:53
=SUM(ARRAYFORMULA(IFERROR(VLOOKUP(TRANSPOSE(SPLIT(D2, ", ")), A:B, 2, 0))))
发布于 2019-03-21 03:58:26
={"QTY"; ARRAYFORMULA(IFERROR(VLOOKUP(TO_TEXT(A2:A),
QUERY(TRANSPOSE(SPLIT(TEXTJOIN(", ", 1, D2:D), ", ")),
"select Col1,count(Col1) group by Col1 label count(Col1)''", 0), 2, 0)))}
https://stackoverflow.com/questions/55258347
复制相似问题