为什么我的遗漏公式会是错误的

上天恩宠的东

遗漏.zip (68.12 KB, 下载次数: 18)

2014-8-14 17:30 上传

点击文件名下载附件

sw=andy

下班，留个脚印！！！

毕释理

按照你的公式，若找不到K1，结果就是上一个单元格加1，就是6啊，没错额。。。

wangjguo44

因为你的F1:O1的0~9是数值型，改成文本型（前面加个单引号）就不会错了（以你举的K9为例，K1若是数值型，K8=5，K9=N(K8)+1=5+1=6，而若K1是文本5，K8也是文本5，则K9=N(K8)+1=0+1=1）
附件把条件格式也简化了一下。

上天恩宠的东

wangjguo44 发表于 2014-8-14 18:52
因为你的F1:O1的0~9是数值型，改成文本型（前面加个单引号）就不会错了（以你举的K9为例，K1若是数值型，K8 ...

谢谢老师，我的公式全部都是错误的，原来文本和数值没有搞清楚，还是要再学习。谢谢老师。

上天恩宠的东

wangjguo44 发表于 2014-8-14 18:52
因为你的F1:O1的0~9是数值型，改成文本型（前面加个单引号）就不会错了（以你举的K9为例，K1若是数值型，K8 ...

遗漏--1.zip (61.66 KB, 下载次数: 4)

2014-8-14 22:12 上传

点击文件名下载附件

老师，如果提取遗漏值最大的数相对应的数据又如何应用公式（只提取前面3位对应的数）。

wangjguo44

上天恩宠的东 发表于 2014-8-14 22:13
老师，如果提取遗漏值最大的数相对应的数据又如何应用公式（只提取前面3位对应的数）。

请验证
EH(WJG)c.rar (471.5 KB, 下载次数: 16)

2014-8-15 09:34 上传

点击文件名下载附件

上天恩宠的东

wangjguo44 发表于 2014-8-15 09:34
请验证

老师我没有看明白，您看我说的对不对“000”，{1;2;3}是指我要求的数是3个.{100;10;1}也是指我要求的数是3个，SUM是和数，其他的我不懂。谢谢老师来指点。

wangjguo44

上天恩宠的东 发表于 2014-8-15 10:43
老师我没有看明白，您看我说的对不对“000”，{1;2;3}是指我要求的数是3个.{100;10;1}也是指我要求的数是 ...

以P8为例：

1. =TEXT(SUM((9-RIGHT(LARGE(IF(ISNUMBER(F8:O8),F8:O8,)*10+9-$F$1:$O$1,{1;2;3})))*{100;10;1}),"000")

复制代码

1、IF(ISNUMBER(F8:O8),F8:O8,)——因为F8:O8中有文本型（数字），也有数值型数字：{1,"1",5,2,1,"5",4,"7",5,1}，首先把文本数字排除掉（置零），只考虑数值型：{1,0,5,2,1,0,4,0,5,1}；

2、IF(ISNUMBER(F8:O8),F8:O8,)*10+$F$1:$O$1——把这些数字扩大10倍加上第一行的数字（虽是文本型数字，但一归入加减乘除运算，就如同数值型），这样一来，就把第一行的数字与本行数值大小结合起来，（本行大的依然是大的），由于你的模拟结果，本行相同大小的，列标序号小的要排在前面，所以做了变通，不是直接加上第一行的数字，而是用9去减掉第一行数字，即IF(ISNUMBER(F8:O8),F8:O8,)*10+9-$F$1:$O$1，得到{19,8,57,26,15,4,43,2,51,10}，这样保证列表号小的数值更大；


3、LARGE(IF(ISNUMBER(F8:O8),F8:O8,)*10+9-$F$1:$O$1,{1;2;3})——取出前三个大数，这很容易理解：{57;51;43}；


4、RIGHT(LARGE(IF(ISNUMBER(F8:O8),F8:O8,)*10+9-$F$1:$O$1,{1;2;3}))——从这三个数取得个位数的值：{"7";"1";"3"}，因为前面用9去减，现在再把这个9减回来：9-RIGHT(LARGE(IF(ISNUMBER(F8:O8),F8:O8,)*10+9-$F$1:$O$1,{1;2;3}))，得到第一行的数：{2;8;6}；

5、SUM((9-RIGHT(LARGE(IF(ISNUMBER(F8:O8),F8:O8,)*10+9-$F$1:$O$1,{1;2;3})))*{100;10;1})——把这三个数分别乘以{100;10;1})得到{200;80;6}，再求和，得到286，即是所要求的结果；

6、TEXT(SUM((9-RIGHT(LARGE(IF(ISNUMBER(F8:O8),F8:O8,)*10+9-$F$1:$O$1,{1;2;3})))*{100;10;1}),"000")——由于有可能某一行的F列数字最大，（如34行的第31期，F34=5，系一行中最大，对应的F1是0，其次是M34、H34对应的M1、H1的7和2），第四步得到的是{0;7;2}，倘仅用SUM求值，0乘100仍然0，结果是两位数72，用TEXT(……,"000")就可以显示为以0开头的三位数“072”了！

上天恩宠的东

wangjguo44 发表于 2014-8-15 12:23
以P8为例：

1、IF(ISNUMBER(F8:O8),F8:O8,)——因为F8:O8中有文本型（数字），也有数值型数字：{1,"1" ...

谢谢老师的详细解答，我先慢慢理解，衷心感谢老师。