Friday, September 30, 2005

Incredible solution!

一次关于EXCEL的使用求助,记在这里,作者的答复实在令人叹服。

问题:求一串数字随机相加,所有产生的可能的和。例如给出1,2,3 三个数,显示:1+0,2+0,3+0,1+2,1+3,2+3,1+2+3的结果。

Solution by Harlan Grove

I've always disliked parsing approaches that use dummy characters as
placeholders. I also dislike the B1&A1&B1 because it involves an
unnecessary concatenation.

If this formula would be used a lot, then there's cause to use a
defined name like seq referring to =ROW(INDIRECT("1:1024")). Then you
could use the array formula

=SUM(IF(MID(B1&A1,seq,1)=B1,--MID(A1,FIND(B1,B1&A1,seq),
FIND(B1,A1&B1,seq+1)-FIND(B1,B1&A1,seq))))

to sum the numbers in the string. Without the defined name, it becomes

=SUM(IF(MID(","&A1,ROW(INDIRECT("1:1024")),1)=",",--MID(A1,
FIND(",",","&A1,ROW(INDIRECT("1:1024"))),
FIND(",",A1&",",ROW(INDIRECT("2:1025")))
-FIND(",",","&A1,ROW(INDIRECT("1:1024"))))))

As for the OP's follow-up asking for a way to calculate the sums of all
subsequences of the sequence in A1, it can be done with formulas, but
it's MESSY & INEFFICIENT. But when have I ever let that stop me?

Step 1: parse the sequence in A1 into single numbers in separate cells
using Data > Text to Columns, and name the resulting range nums. For
the OP's example, this would be 6 numbers.

Step 2 Select a 63 (2^6-1) row by 1 column range and enter the array
formula

=MMULT(MOD(INT(ROW(INDIRECT("1:63"))/2^{5,4,3,2,1,0}),2),
TRANSPOSE(nums))

点击这里看求助过程。

No comments: