请教excel函数表达式

显示全部楼层 · 2011-9-26 04:45:48

excel函数表达式A1B1和取个位，A2B2的和的个位，A3B3的和的个位,....，A10B10的和的个位把，这上面10个数字按照从小到大，重复的数字只显示一次，显示在C10框里，请问C10的表达式应该怎么写?

千问 · 2011-9-26 04:45:48

用文本函数中的：多字串组合函数，右取字串函数，组合使用。
=CONCATENATE(RIGHT(A1B1,1),RIGHT(A2B2,1),RIGHT(A3B3,1),........,RIGHTA10B10,1))

千问 · 2011-9-26 04:45:48

c10里面要一下子显示10个数？
公式有点长

=MOD(A1B1,10)defds123IF(MOD(A2B2,10)=MOD(A1B1,10),\"\",MOD(A2B2,10))defds123IF(OR(MOD(A3B3,10)=MOD(A1B1,10),MOD(A3B3,10)=MOD(A2B2,10)),\"\",MOD(A3B3,10))defds123IF(OR(MOD(A4B4,10)=MOD(A1B1,10),MOD(A4B4,10)=MOD(A2B2,10),MOD(A4B4,10)=MOD(A3B3,10)),\"\",MOD(A4B4,10))defds123IF(OR(MOD(A5B5,10)=MOD(A1B1,10),MOD(A5B5,10)=MOD(A2B2,10),MOD(A5B5,10)=MOD(A3B3,10),MOD(A5B5,10)=MOD(A4B4,10)),\"\",MOD(A5B5,10))defds123IF(OR(MOD(A6B6,10)=MOD(A1B1,10),MOD(A6B6,10)=MOD(A2B2,10),MOD(A6B6,10)=MOD(A3B3,10),MOD(A6B6,10)=MOD(A4B4,10),MOD(A6B6,10)=MOD(A5B5,10)),\"\",MOD(A6B6,10))defds123IF(OR(MOD(A7B7,10)=MOD(A1B1,10),MOD(A7B7,10)=MOD(A2B2,10),MOD(A7B7,10)=MOD(A3B3,10),MOD(A7B7,10)=MOD(A4B4,10),MOD(A7B7,10)=MOD(A5B5,10),MOD(A7B7,10)=MOD(A6B6,10)),\"\",MOD(A7B7,10))defds123IF(OR(MOD(A8B8,10)=MOD(A1B1,10),MOD(A8B8,10)=MOD(A2B2,10),MOD(A8B8,10)=MOD(A3B3,10),MOD(A8B8,10)=MOD(A4B4,10),MOD(A8B8,10)=MOD(A5B5,10),MOD(A8B8,10)=MOD(A6B6,10),MOD(A8B8,10)=MOD(A7B7,10)),\"\",MOD(A8B8,10))defds123IF(OR(MOD(A9B9,10)=MOD(A1B1,10),MOD(A9B9,10)=MOD(A2B2,10),MOD(A9B9,10)=MOD(A3B3,10),MOD(A9B9,10)=MOD(A4B4,10),MOD(A9B9,10)=MOD(A5B5,10),MOD(A9B9,10)=MOD(A6B6,10),MOD(A9B9,10)=MOD(A7B7,10),MOD(A9B9,10)=MOD(A8B8,10)),\"\",MOD(A9B9,10))defds123IF(OR(MOD(A1B1,10)0=MOD(A1B1,10),MOD(A1B1,10)0=MOD(A2B2,10),MOD(A1B1,10)0=MOD(A3B3,10),MOD(A1B1,10)0=MOD(A4B4,10),MOD(A1B1,10)0=MOD(A5B5,10),MOD(A1B1,10)0=MOD(A6B6,10),MOD(A1B1,10)0=MOD(A7B7,10),MOD(A1B1,10)0=MOD(A8B8,10),MOD(A1B1,10)0=MOD(A9B9,10)),\"\",MOD(A1B1,10)0)

应该可以满足你的需求你直接把公式复制粘贴进C10试试

如果公式太长，可以这样操作
1.在C1输入=MOD(A1B1,10)
C2输入=MOD(A2B2,10)

C10输入=MOD(A10B10,10)

依次输到C10(可以输完第一个，然后拖动右下角，直接复制)

2.在C11输入：
=D1defds123IF(D2=D1,\"\",D2)defds123IF(OR(D3=D1,D3=D2),\"\",D3)defds123IF(OR(D4=D1,D4=D2,D4=D3),\"\",D4)defds123IF(OR(D5=D1,D5=D2,D5=D3,D5=D4),\"\",D5)defds123IF(OR(D6=D1,D6=D2,D6=D3,D6=D4,D6=D5),\"\",D6)defds123IF(OR(D7=D1,D7=D2,D7=D3,D7=D4,D7=D5,D7=D6),\"\",D7)defds123IF(OR(D8=D1,D8=D2,D8=D3,D8=D4,D8=D5,D8=D6,D8=D7),\"\",D8)defds123IF(OR(D9=D1,D9=D2,D9=D3,D9=D4,D9=D5,D9=D6,D9=D7,D9=D8),\"\",D9)defds123IF(OR(D10=D1,D10=D2,D10=D3,D10=D4,D10=D5,D10=D6,D10=D7,D10=D8,D10=D9),\"\",D10)

C11就是你要的结果

千问 · 2011-9-26 04:45:48

在C1输入公式：=RIGHT(A1B1,1)下拉到C9，在C10输入公式：=RIGHT(A10B10,1)defds123C1defds123C2defds123C3defds123C4defds123C5defds123C6defds123C7defds123C8defds123C9