[第82期]数字的个位数在一行内出现频率最高的数字的总和（已总结评分）

wcymiss

题目在未置顶之前请勿解答，附件所述内容将有所变动,2011-9-13。
开贴，可以解答了，2011-9-14。

请注意：A1:I9为0-999间的任意整数。公式要具有通用性。

wajour

1. =SUMPRODUCT((MOD(A1:I9,10)=SUMPRODUCT((FREQUENCY(MOD(A1:I9,10),ROW(A1:A9)-1)=MAX(FREQUENCY(MOD(A1:I9,10),ROW(A1:A9)-1)))*(ROW(A1:A10)-1)))*(A1:I9))

复制代码

弄了一个sumproduct 函数
contif第一个参数不能是只能是单元个区域，不能是数组
所以用频数函数代替


{:soso_e135:}悲崔我看成是整个区域个位出现次数最高的数和了

wangg913

好久没占个高楼层了。
新瓶装旧酒：{:soso_e116:}
=SUM(A1:I9*ISNUMBER(MATCH(RIGHT(A1:I9)+ROW(1:9)/1%,MMULT(TRUNC(SMALL(FREQUENCY(ROW(1:9)*10+RIGHT(A1:I9),ROW(10:98))*10+TEXT(ROW(10:99),"0!00"),ROUNDUP(ROW(1:90),-{0,1,0})),-{0,1,2}),{1;-1;1}),)))

竟然只减去一个字：{:soso_e127:}
=SUM(A1:I9*ISNUMBER(MATCH(RIGHT(A1:I9)+ROW(1:9)/1%,MMULT(-MID(SMALL(FREQUENCY(ROW(1:9)*10+RIGHT(A1:I9),ROW(10:98))*10+TEXT(ROW(10:99),"0!00"),ROUNDUP(ROW(1:90),-{0,1})),{1,2},{3,1}),{-1;10}),)))

老办法：{:soso_e113:}
=SUM(A1:I9*(LEN(MMULT(10^MID(MMULT(10^-RIGHT(A1:I9),ROW(1:9)^0)%,RIGHT(A1:I9)+4,1),1^ROW(1:9)))-MID(MMULT(10^-RIGHT(A1:I9),1^ROW(1:9))%,RIGHT(A1:I9)+4,1)=1))

想减一个字也难：{:soso_e127:}
=SUM(A1:I9*(LEN(MMULT(1-ISERR(FIND(COLUMN(A:I),MMULT(10^RIGHT(A1:I9),ROW(1:9)^0))),9^ROW(1:9)))-MID(MMULT(10^-RIGHT(A1:I9),1^ROW(1:9))%,RIGHT(A1:I9)+4,1)=0))

有空再想想。

chenhh803

方法一:
=SUM(ISNUMBER(MATCH(MOD(A1:I9,10)+ROW(1:9)*10,LARGE((FREQUENCY(MOD(A1:I9,10)+ROW(1:9)*10,ROW(10:99))+INT((ROW(1:91)-1)/10)*10=LARGE(FREQUENCY(MOD(A1:I9,10)+ROW(1:9)*10,ROW(10:99))+INT((ROW(1:91)-1)/10)*10,(COLUMN(A:I)-1)*10+2))*ROW(10:100),COLUMN(A:CZ)),))*A1:I9)
终于可以拿一分了:
=SUM(ISNUMBER(MATCH(RIGHT(A1:I9)+ROW(1:9)*10,LARGE((FREQUENCY(RIGHT(A1:I9)+ROW(1:9)*10,ROW(10:99))+INT(ROW(10:100)/10)*10=SMALL(FREQUENCY(RIGHT(A1:I9)+ROW(1:9)*10,ROW(10:99))+INT(ROW(10:100)/10)*10,COLUMN(A:I)*10))*ROW(10:100),ROW(1:81)),))*A1:I9)
方法二:195字符
=SUM((9-LEN(SUBSTITUTE(TEXT(MMULT(MOD(A1:I9,10),10^(ROW(1:9)-1)),REPT(0,9)),RIGHT(A1:I9),))=MOD(SMALL(FREQUENCY(RIGHT(A1:I9)+ROW(1:9)*10,ROW(10:99))+INT(ROW(10:100)/10)*10,ROW(1:9)*10),10))*A1:I9)
再简(这条也有问题):
=SUM((9-LEN(SUBSTITUTE(RIGHT(0&MMULT(MOD(A1:I9,10),10^(ROW(1:9)-1)),9),RIGHT(A1:I9),))=MOD(SMALL(FREQUENCY(RIGHT(A1:I9)+ROW(1:9)*10,ROW(10:98))+INT(ROW(10:99)/10)*10,ROW(1:9)*10),10))*A1:I9)
再简(这条有问题看样子降不到181):
=SUM((9-LEN(SUBSTITUTE(LEFT(MMULT(MOD(A1:I9,10),10^ROW(1:9)),9),RIGHT(A1:I9),))=MOD(SMALL(FREQUENCY(RIGHT(A1:I9)+ROW(1:9)*10,ROW(10:98))+INT(ROW(10:99)/10)*10,ROW(1:9)*10),10))*A1:I9)
这样
=SUM((9-LEN(SUBSTITUTE(LEFT(MMULT(MOD(A1:I9,10),10^ROW(1:9))&"0000",9),RIGHT(A1:I9),))=MOD(SMALL(FREQUENCY(RIGHT(A1:I9)+ROW(1:9)*10,ROW(10:98))+LEFT(ROW(10:99))*10,ROW(1:9)*10),10))*A1:I9)
方法三:
=SUM((RIGHT((INT((MMULT(10^RIGHT(A1:I9),ROW(1:9)^0)/10^RIGHT(A1:I9)))))=RIGHT(SMALL(FREQUENCY(RIGHT(A1:I9)+ROW(1:9)*10,ROW(10:98))+LEFT(ROW(10:99))*10,ROW(1:9)*10)))*A1:I9)
再简:
=SUM((LEFT(RIGHT(MMULT(10^RIGHT(A1:I9),ROW(1:9)^0),RIGHT(A1:I9)+1))=RIGHT(SMALL((0&MID(MMULT(10^RIGHT(A1:I9),ROW(1:9)^0),COLUMN(A:J),1))+ROW(1:9)*10,ROW(1:9)*10)))*A1:I9)
再简(终于到168了):
=SUM((LEFT(RIGHT(MMULT(10^RIGHT(A1:I9),ROW(1:9)^0),RIGHT(A1:I9)+1))=RIGHT(SMALL(FREQUENCY(RIGHT(A1:I9)+ROW(1:9)*10,ROW(10:98))+LEFT(ROW(10:99))*10,ROW(1:9)*10)))*A1:I9)
再简一个167
=SUM((LEFT(RIGHT(MMULT(10^RIGHT(A1:I9),ROW(1:9)^0),RIGHT(A1:I9)+1))=RIGHT(SMALL(FREQUENCY(RIGHT(A1:I9)+ROW(1:9)*10,ROW(10:98))+LEFT(ROW(10:99))*10,ROW(1:9)&0)))*A1:I9)
再简:
=SUM((MID(MMULT(0.1^RIGHT(A1:I9),ROW(1:9)^0)%,RIGHT(A1:I9)+4,1)=RIGHT(SMALL(FREQUENCY(RIGHT(A1:I9)+ROW(1:9)*10,ROW(10:98))+LEFT(ROW(10:99))*10,ROW(1:9)&0)))*A1:I9)
再简,158
=SUM((-MID(MMULT(10^-RIGHT(A1:I9),ROW(1:9)^0)%,RIGHT(A1:I9)+4,1)=1-LEN(MMULT(10^(0&MID(MMULT(10^RIGHT(A1:I9),ROW(1:9)^0),COLUMN(A:J),1)),ROW(1:10)^0)))*A1:I9)
再简一个:157
=SUM((MID(MMULT(10^-RIGHT(A1:I9),ROW(1:9)^0)%,RIGHT(A1:I9)+4,1)+1=LEN(MMULT(10^(0&MID(MMULT(10^RIGHT(A1:I9),ROW(1:9)^0),COLUMN(A:J),1)),ROW(1:10)^0)))*A1:I9)
呵呵,不行的没删,留做记念

hopeson2010

先占位，有时间再考虑
=SUM((LEN(SUBSTITUTE(TEXT(MMULT(MOD(A1:I9,10),10^(9-ROW(1:9))),REPT(0,9)),RIGHT(A1:I9),))=MOD(SMALL(ROW(1:9)*10+LEN(SUBSTITUTE(TEXT(MMULT(MOD(A1:I9,10),10^(9-ROW(1:9))),REPT(0,9)),RIGHT(A1:I9),)),ROW(1:9)*9-8),10))*A1:I9)

简化了一下：207个字符
=SUM(A1:I9*(MOD(SMALL(LEN(SUBSTITUTE(LEFT(MMULT(MOD(A1:I9,10),10^ROW(1:9))*10^9,9),RIGHT(A1:I9),))+ROW(1:9)*10,ROW(1:9)*9-8),10)=LEN(SUBSTITUTE(LEFT(MMULT(MOD(A1:I9,10),10^ROW(1:9))*10^9,9),RIGHT(A1:I9),))))

wkbu

先发一个“最多数唯一”时的和值，占个位。
=SUM(IF(--RIGHT(A1:I9,1)-MODE(--RIGHT(A1:I9,1))=0,A1:I9,0))  数组，又看一遍题，发现自己对题意的理解错了，过几天再说吧。

fugb-2010

L2=SUM((LEN(MMULT(10^LEFT(RIGHT(MMULT(10^RIGHT(A1:I9,1),ROW(1:9)^0),RIGHT(A1:I9,1)+1),1),ROW(1:9)^0))-1=--LEFT(RIGHT(MMULT(10^RIGHT(A1:I9,1),ROW(1:9)^0),RIGHT(A1:I9,1)+1),1))*A1:I9)
数组公式180字元
简化174字元
L2=SUM((LEN(MMULT(10^MID(MMULT(10^(-1-RIGHT(A1:I9,1)),ROW(1:9)^0),RIGHT(A1:I9,1)+3,1),ROW(1:9)^0))-1=--MID(MMULT(10^(-1-RIGHT(A1:I9,1)),ROW(1:9)^0),RIGHT(A1:I9,1)+3,1))*A1:I9)
解题思路
A = "数据源"单元分别提取最后1位
        公式  =RIGHT(A1:I9,1)
B = 以10为底的"A数据"次方,并每行分别累加,以"A数据"为位置及个数形式显示
        公式 =MMULT(10^"A数据",ROW(1:9)^0)
C = 以"A数据"为位置对"B数据"截取1位数字,得出各个单元格所在行重复个数
        公式 =--LEFT(RIGHT("B数据","A数据"+1),1)
D = 以10为底的"C数据"次方,并每行分别累加,以"C数据"为位置及个数形式显示
        公式 =MMULT(10^"C数据",ROW(1:9)^0)
E = "D数据"长度减1为所在行最大重复个数
        公式 =LEN("D数据")-1
F = "E数据"与"C数据"相等时为所需数据
        公式 = "E数据"="C数据"

liuguansky

1. =SUM(A1:I9*(MOD(SMALL(LEN(SUBSTITUTE(MMULT(MOD(A1:I9,10),10^ROW(2:10))%,RIGHT(A1:I9),))+ROW(1:9)*10,ROW(1:9)*9-8),10)=LEN(SUBSTITUTE(MMULT(MOD(A1:I9,10),10^ROW(2:10))%,RIGHT(A1:I9),))))

复制代码

chunlin1wang

离目标太远了点，字符342,

1. 
   
2. =SUM((MMULT(1*(RIGHT(SMALL(RIGHT(A1:I9)+ROW(1:9)*1010+COLUMN(A:I)*100,ROW(1:81)),2)=RIGHT(SMALL(RIGHT(A1:I9)+ROW(1:9)*1010+COLUMN(A:I)*100,COLUMN(A:CC)),2)),ROW(1:81)^0)+INT(ROW(1:81)/9.1)*10=SMALL(FREQUENCY(RIGHT(A1:I9)+ROW(1:9)*10,ROW(11:100)-1)+INT(ROW(1:91)/10.1)*10,COLUMN(A:I)*10))*N(OFFSET(A1,INT((ROW(1:81)-1)/9),MOD(ROW(1:81)-1,9))))

复制代码

还是老思路减到300字符（好累） 过几天再想想

1. =SUM((MMULT(1*(RIGHT(SMALL(A1:I9+ROW(1:9)/1%%,ROW(1:81)))+INT(ROW(10:90)/9.1)*10=SMALL(RIGHT(A1:I9)+ROW(1:9)*10,COLUMN(A:CC))),ROW(1:81)^0)+INT(ROW(1:81)/9.1)*10=SMALL(FREQUENCY(RIGHT(A1:I9)+ROW(1:9)*10,ROW(10:99))+INT(ROW(1:91)/10.1)*10,COLUMN(A:I)*10))*RIGHT(SMALL(A1:I9+ROW(1:9)/1%%,ROW(1:81)),3))

复制代码

fangjianp

1. 
   
2. =SUM(TEXT(MMULT(TRUNC(SMALL((ROW(1:9)&MID(MMULT(0.1^RIGHT(A1:I9),ROW(1:9)^0)%,RIGHT(A1:I9)+4,1))/1%%+A1:I9,CEILING(ROW(1:81),{1,9})),{0,-4}),{1;-1}),"0;\0")^1)
   
3. 
   
4. 
   
5. =SUM((LEN(MMULT(10^(0&MID(MMULT(10^RIGHT(A1:I9),ROW(1:9)^0),COLUMN(A:J),1)),ROW(1:10)^0))-MID(MMULT(10^-RIGHT(A1:I9),1^ROW(1:9))%,RIGHT(A1:I9)+4,1)=1)*A1:I9)

复制代码