将包含文本值列表的单元格拆分为其他单元格，具体取决于列表中的值 [英] Splitting a cell containing a list of text values into other cells, depending on the values in the list

问题描述

我想知道是否可以根据可编辑的参考表，使用仅 Google表格公式，将包含逗号分隔列表的单元格拆分为多个列，而不使用Apps脚本.

我创建了一个简化版本，以更好地解释我要实现的目标.

我看到了这样的一张桌子:

在 A2 中有一个颜色名称列表，我希望将它们分类到新的，较小的列表中的正确列中.理想的解决方案如下所示:

理想情况下，我将使用存在于 B2 ， C2 和 D2 中的公式来执行此操作.哪个单元格中的内容必须是动态的，以便在出现新选项时将其分类到正确的单元格中.

我想在保持动态状态的同时，最好的方法是使公式与位于主"表中的参考表进行比较.文档.例如:

B2 中的公式将引用表列 A ， C2 将引用表列 B ，依此类推.

通过这种方式，无需编辑公式-我想避免这种情况，因为该系统将在许多不同的Google表格文档中使用.

尽管使用Apps Script肯定可以实现这一点，但我宁愿使用公式来完成繁重的工作，因此，如果有人继承了我的角色，那么他们就不需要了解Apps Script/JS等来维护和/或修改系统.

我可以想象该过程涉及到 SPLIT 和 JOIN ，其中可能包含"，""，还有可能是 LOOKUP (或 VLOOKUP )，但是我无法弄清楚它是如何实现的(甚至是可能的).

任何输入将不胜感激！

谢谢

解决方案

粘贴到REDS下，然后拖动到右侧:

  = INDEX(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(IF(REGEXMATCH($ A11:$ A，(?i)"&TRANSPOSE(FILTER(master！A2:A8，master！A2:A8)")))，TRANSPOSE(FILTER(master！A2:A8，master！A2:A8"))&，"，)),, 9 ^ 9)))，，$"，)) 

---

更新:

  = ARRAYFORMULA(IFNA(VLOOKUP(''"& ROW(A11:A)，SUBSTITUTE(REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(QUERY(SPLIT(FLATTEN(ROW(A11:A)&``♠♣'')& IFERROR(TRIM(SPLIT(LOWER($ A11:$ A)，，"))&♦"))，♣")，"选择max(Col2)其中Col2匹配'^'& TEXTJOIN("$ | ^"，1，IF(B2:B8 ='',, LOWER(B2:B8)&"♦))&" $'按Col2枢纽分组Col1),, 9 ^ 9))，"♠))，"♦$，)，"♦，"，)，2，0))) 

I'm wondering whether it's possible to split a cell that contains a comma-separated list into various columns, based on a reference table that is editable, using only Google Sheets formulae and not resorting to Apps Script.

I have created a simplified version to better explain what I'm trying to achieve.

I am presented with a table like this:

In A2 there is a list of colour names, and I would like them to be sorted into the correct columns in new, smaller lists. The ideal solution would look like this:

Ideally I would do this with formulae that live in B2, C2, and D2. What goes in which cell though has to be dynamic, so that if new options came about they could be sorted into the correct cells.

I imagine the best way to achieve this whilst keeping what goes in which column dynamic would be to have the formulae compare against a a reference table living in a "master" document. For example:

The formula in B2 would reference table column A, C2 would reference table column B, etc.

This way the formulae would not require editing - which I want to avoid as this system would be used across many different Google Sheets documents.

Whilst this is certainly achievable using Apps Script, I would rather the formulae do the heavy lifting so that if someone inherits my role further down the line they aren't required to understand Apps Script/JS etc. to maintain and/or modify the system.

I would imagine that the process involves SPLIT and JOIN by ", ", and possibly a LOOKUP (or VLOOKUP), but I can't work out how (or even if) it's possible.

Any input would be greatly appreciated!

Thanks

解决方案

paste under REDS and drag to the right:

=INDEX(REGEXREPLACE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(REGEXMATCH($A11:$A, "(?i)"&
 TRANSPOSE(FILTER(master!A2:A8, master!A2:A8<>""))), 
 TRANSPOSE(FILTER(master!A2:A8, master!A2:A8<>""))&",", )),,9^9))), ",$", ))

---

update:

=ARRAYFORMULA(IFNA(VLOOKUP(""&ROW(A11:A), SUBSTITUTE(REGEXREPLACE(TRIM(
 SPLIT(FLATTEN(QUERY(QUERY(SPLIT(FLATTEN(ROW(A11:A)&"♠♣"&IFERROR(TRIM(
 SPLIT(LOWER($A11:$A), ","))&"♦")), "♣"), 
 "select max(Col2) 
  where Col2 matches '^"&TEXTJOIN("$|^", 1, IF(B2:B8="",,LOWER(B2:B8)&"♦"))&"$' 
  group by Col2 pivot Col1"),,9^9)), "♠")), "♦$", ), "♦", ","), 2, 0)))

这篇关于将包含文本值列表的单元格拆分为其他单元格，具体取决于列表中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！