☰

根据特定值将单元格数据列表放入单个单元格中

tag-icon • tag-icon microsoft-excel worksheet-function cell-format
根据特定值将单元格数据列表放入单个单元格中

我知道问题标题不太好，我想不出一个好的措辞方式来表达这个问题，这让我很难在网上搜索找到答案。我没有太多的 Excel 经验，但我通常能够通过几次快速的谷歌搜索来弄清楚如何做某事。

到目前为止，我已经尝试了多种功能组合TEXTJOIN(), FILTER(), SEARCH() and FIND()。

我正在尝试根据空闲时间制定时间表。

我使用谷歌表单来调查人们什么时候有空（表 1）。

Sheet 1 (Data)                         
    A      B         C
1  NAME   MON       TUES      
2  Dan    1, 2, 5   1, 4
3  Robert 12, 3     12, 3
4  Gregg  1, 2      4,
5  Joe    2, 3      3,

我希望能够看到空闲时间的重叠，这样我就可以根据两个人同时有空的时间制定时间表。

Sheet 2 (Results)
     A        B                  C   
1   TIME     MON                TUES
2    12      Robert             Robert 
3    1       Dan, Gregg         Dan
4    2       Dan, Gregg, Joe    
5    3       Robert, Joe        Robert, Joe 
6    4                          Dan, Gregg
7    5       Dan

答案1

[编辑 ]添加了屏幕截图、替代描绘校正和额外警告。

这有效。

=TEXTJOIN(", ", TRUE, IF(IFERROR(FIND(","&TRIM($A2)&",",","&SUBSTITUTE(Sheet1!B:B," ","")&","),),TRIM(Sheet1!$A:$A),""))

B2在单元格中输入Ctrl- Shift- Enter(CSE) 使其成为数组公式。复制并向下拖动。

日间细胞空闲时间，必须像样本数据一样以逗号分隔。

  • 数字之间的任意位置都可以：12, 1 ,2。

匹配字符串中分隔值的快捷方式
在 find 函数使用工作表-2，小时单元，和Sheet-1，小时单元格，此公式会删除空格，并在这些单元格的输入的左右两侧添加逗号。然后find在没有空格的字符串中查找用逗号括起来的数字；只有数字和逗号，两个参数的两端都有逗号。

  • Find( ",2," , ",12,1,2," )。
    请注意寻找价值和按价值搜索。

[编辑] 注意：此公式不匹配天Mon | Tue | Wed列。它使用相对位置。如果数据和结果之间的序列顺序Mon | Wed不同，则需要编辑每个结果列公式以Sheet1!DayColumn正确对齐引用（或进一步自定义公式以自动按标题匹配列）。

轮廓校正

当数字以空格分隔且它们之间没有逗号时，上述公式会连接数字1 2→ 12。

Gregg 在星期二被添加到，因为和12之间的空格被公式删除了12（Gregg 未添加到1也没有2）14.如果这是 24 点钟，那么 Joe 将被安排在星期二（Joe 未添加到1也未4）。

替代描绘校正。1 2用逗号→ 替换空格1,2。

=TEXTJOIN(", ", TRUE, IF(IFERROR(FIND(","&TRIM($A2)&",",","&SUBSTITUTE(Sheet1!B:B," ",",")&","),),TRIM(Sheet1!$A:$A),""))

Gregg 被添加到1and on Tues，因为 the和 the2之间的空格被替换为逗号12（Gregg 未添加到12）。Joe 被添加到1和4。

如果两种描绘校正均不可接受，则创建数据完整性公式并：

  • 将完整性公式添加到结果公式中
  • 将诚信公式放入通知栏
  • 使用完整性公式有条件地格式化（突出显示）数据和/或结果

相关内容