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列按照该顺序排序
英文版
凑个热闹