Excel函数：使用定界符将字符串拆分为水平数组 [英] Excel function: Split string into horizontal Array by delimiter

问题描述

这一次，我确实已经在这里找到了解决我的问题的解决方案，但是我不知道如何使用它。

所以基本上我想转换类似

aa＃b＃ccc＃1＃2

转换为

  {aa.b.ccc 1.2} 
  

在以后与Sumproduct一起使用。因此，这篇文章似乎可以完成工作：

如您所见，我们将垂直数组传递给了匹配：

这会导致数组正确TRUE / FALSE的值传递给SUMPRODUCT：

this time I did find a solution for my problem in here already but I could not figure out how to use it.

So basically I want to convert strings like

"aa#b#ccc#1#2"

into a horizontal array like

{aa.b.ccc.1.2} 

to use them later on with Sumproduct. So this article seems to do the job:

Split a string (cell) in Excel without VBA (e.g. for array formula)

In there I found the formula:

TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1)))=1),99))

However this will return an vertical array like:

{aa;b;ccc;1;2}

When I combine this with Sumproduct like

Sumproduct((a5:a10)*(b5:b10=ABOVE FORMULA))

I only get the sum of "aa" but not the rest. I tried transpose(ABOVE FORMULA) but it did not do the job.

Can you help me out?

Many greetings, Peter

PS: another problem is that my numbers become strings but this is something I can handle

PPS: {aa.b.ccc.1.2} This type of array is what I see when I press F9 on the formula "{=b1:f1"}

解决方案

Use ISNUMBER(MATCH()) instead:

Sumproduct((a5:a10)*(ISNUMBER(MATCH(b5:b10&"",ABOVE FORMULA,0))))

Even with SUMPRODUCT, you need to use Ctrl-Shift-enter instead of Enter when exiting edit mode.

Edit:

A slightly shorter and less volatile version of your formula:

=TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",99)),(ROW(INDEX(AAA:AAA,1):INDEX(AAA:AAA,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1))-1)*99+1,99))

so in total:

=SUMPRODUCT((A5:A10)*(ISNUMBER(MATCH(B5:B10&"",TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",99)),(ROW(INDEX(AAA:AAA,1):INDEX(AAA:AAA,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1))-1)*99+1,99)),0))))

And use Ctrl-Shift-enter instead of Enter when exiting edit mode.

As you can see, we are passing the vertical array to the MATCH:

Which results in the proper array of TRUE/FALSE to pass to SUMPRODUCT:

这篇关于Excel函数：使用定界符将字符串拆分为水平数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！