Excel 根据包含的关键词将指定列按关键词指定顺序排列
例题描述和简单分析
有 Excel 文件，数据如下所示：
| A | B | |
| 1 | Parent Column | Modifier (Column) |
| 2 | Jack lives in the village | Rose |
| 3 | As mentioned by jack | Village |
| 4 | Rose already spoke to jack about last night | Jack |
| 5 | Rose left the village | |
| 6 | rose was their yesterday | |
A列（Parent Column）需要根据包含的关键词按照关键单词列表 (Modifier (Column)) 的顺序排序，结果如下：
| A | B | |
| 1 | Parent Column | Modifier (Column) |
| 2 | Rose already spoke to jack about last night | Rose |
| 3 | Rose left the village | Village |
| 4 | rose was their yesterday | Jack |
| 5 | Jack lives in the village | |
| 6 | As mentioned by jack | |
解法及简要说明
使用插件 SPL XLL
在空白格填写代码：
=spl("=(?1.conj().sort((X=lower(~).words(),b=?2.conj().(lower(~)),X.min(b.pselect(~:X.~))))).concat@n()",A2:A6,B2:B4)
如图：
返回结果：
简要说明：
A列每个成员（句子）转为小写后，拆成单词序列，在单词序列中找出每个单词在B列中对应的位置序号，并取每个句子的最小值（优先级最高的单词），A列按照该顺序排序
英文版
凑个热闹