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))

点击这里看求助过程。

Thursday, September 29, 2005

昨夜

昨天晚上的月亮好大啊,我跟你坐在我家的房顶上,很凉快惬意。

听说晚上可以看到水星,就是那个同样蓝色的星球。肉眼很难看到吧,我想。秋风习习的吹。
天边渺渺的飘来一轮"月亮",我兴奋的指给你看,你惊讶的望着两个月亮,难道这就是水星?造物主真的神奇的不可思议。
那"月亮"竟越飘越近,好像能被我双手捉住!
该不会……?不可能的,水星几十亿年前就只绕着太阳旋转,不可能偏离到地球的轨道上来!
拉着你的手,奔向地心bus-近几年刚开发出的一种列车,可以直达地心掩体。
那车一直开阿开阿,那条路是近90度上坡的,路边看到很多废墟,据说多年以前,北京的故宫被恐怖份子炸了之后,政府就在旁边修了这条通天道,警示世人。
这简直就是一条通天路,我都不敢多看窗外。
终于到了路的尽头,竟然又是一部电梯!!――以前跟你说过的,电梯在我的梦里一直扮演反面角色。这部电梯绝对不会令导演失望。
不错,这确实是一部变态的电梯。我们进去以后,先是做自由落体运动,然后做加速上升运动,不过我对这种场景有点习惯了,看到对我起不到恐吓作用,电梯干脆失望的卡住了。
你毫无畏惧,因为我没有。
我跟你掀开顶部,爬了出去。
夜空依旧美丽。
水星擦着地球过去了。有一些猪啊,牛啊被飓风旋到水星上去了,孤独的吼叫着,随着那星球月行越远。

Saturday, September 10, 2005

SUN FLOWER

I took this sun flower on the way to Grape home.
Beautiful! isn't it? Posted by Picasa