标签:Excel公式,数组公式
好久没有碰复杂的公式了,这不,手又有点痒痒了。这里的示例来自excelunplugged.com,权当练练手,活动活动头脑。
如下图1所示,将列A中句子的最后一个单词提取到列B中。(这里的最后一个单词用数字代替)
图1
解决的公式貌似复杂,实则不难。
在单元格B1中输入数组公式:
=MID(A1,MAX((--(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)=" "))*ROW(INDIRECT("1:" & LEN(A1))))+1,50)
然后向下拖拉复制到数据末尾即可,如下图2所示。
图2
注意,输入完后按Ctrl+Shift+Enter组合键,Excel会自动添加两侧的花括号。
公式中:
MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)
会得到由组成句子的单个字母组成的数组:
{"P";"o";"o";"l";" ";"P";"l";"a";"y";"i";"n";"g";" ";"2";"6"}
这样,
MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)=" "
即:
{"P";"o";"o";"l";" ";"P";"l";"a";"y";"i";"n";"g";" ";"2";"6"}=" "
得到:
{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}
其中,TRUE值就是分隔单词的空格。
前面的双减号,将TRUE/FALSE值构成的数组转换为1/0值构成的数组:
{0;0;0;0;1;0;0;0;0;0;0;0;1;0;0}
公式中:
ROW(INDIRECT("1:" & LEN(A1)))
生成由连续数字构成的数组,最大数值为单元格中字符串长度:
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}
这样,
{0;0;0;0;1;0;0;0;0;0;0;0;1;0;0}*{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}
得到:
{0;0;0;0;5;0;0;0;0;0;0;0;13;0;0}
传递给MAX函数获得数组的最大值为:
13
即为最后一个分隔单词的空格在句子中的位置。
这个值加上1就是最后一个单词的起始位置。这样,公式变为: MID(A1,13+1,50)
结果就是最后一个单词。
这里的50是任意给出的一个认为最后一个单词的最长长度,你可以修改,只要不小于最后一个单词的长度即可。
这不是最简单的方法。
下面的公式不是数组公式,仍然能够得到想要的结果:
=MID(A1,SEARCH("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,50)
下面的公式更简洁:
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",20)),20))
有兴趣的朋友可以自己解析上述两个公式,锻炼自己的公式思维。
本文配套示例工作簿已放置在知识星球App完美Excel社群中,有兴趣的朋友可以前往下载。
欢迎在下面留言,完善本文内容,让更多的人学到更完美的知识。