Excel：公式中的这些特殊数字 _公式

1 9E+307
9E+307是科学计数法表示的一个数字，就简单理解成是Excel支持的一个很大的数字就可以了。
用法示例：
=(9E+307,A:A)
根据函数的性质，提取A列最后一个数字。

文章插图
2 4^8或65536或100
4^8=4的8次方=65536，也是一个比较大的数字。
因为2003版最大支持65536行，所以以前会经常看到这个数字。一般用途是排除错误值，当公式下拉超过实际返回数据行数后显示空值。
用法示例：
=INDEX(A:A,SMALL(IF(A$2:A$5>80,ROW(A$2:A$5),4^8),ROW(A1)))&""
公式意思是将A列大于80的数值顺序输出来。
因为原始数据一共有2个大于80，当公式下拉到第3个单元格时，因为已经没有要输出的数据了，所以就引用了A列的第4^8（65536）个单元格（一个用不到的空单元格），这样就返回了空值。

文章插图
如果原始数据一共就80多行，那么第100行就是用不到的空单元格，公式中的4^8也可以替换成100 。
3 1和0
这两个数字用法实在是太多了。
比如：0可以在判断的时候当FALSE用，可以用某些文本数字+0变成数值，用-(0&mid函数提取出的空)可以把空值转化成0避免出现错误值……
1可以在判断的时候当TRUE ， 1可以当做1天24小时来计算时间，1可以是比0大的数字被用在(1,0/条件判断,数据)这样的组合里……还是碰到相关函数公式再单独研究其用法吧。
关于0的使用，建议到函数版去查找一个《“0”活多变的公式与格式》的帖子。
4 1/17或5^19或5/19等等
这几个数字有一个特点，就是运算返回的值里面包括0~9所有的10个数字。
比如：1/17=0.17647 ， 5^19=125，5/19=0.2632 。
这个一般用于FIND函数在单元格中查找数字时避免出现错误值。
用法示例：
=MIN(FIND(ROW(1:10)-1,A2&1/17))

文章插图
这个公式用于查找A2单元格第一个数字出现的位置。
ROW(1:10)-1返回{0;1;2;3;4;5;6;7;8;9}，然后FIND({0;1;2;3;4;5;6;7;8;9},A2&1/17)查找0~9分别在A2&1/17中出现的位置，避免了查找某个A2中本来没有的数字出错。
比如A2单元格中不包含1，那么FIND查找1时会出错。
如果A2包含负数，可以用下面的公式来完成。
用A2&-1/17
=MIN(FIND(LEFT(ROW($1:$11)-2),A2&-1/17))

文章插图
5 99
99是一般用在文本函数中的，也充当一个大数字的角色。
用法示例1：
=MID(A2,3,99)

文章插图
这个MID函数返回A2单元格中第3个字符后的所有字符。因为不确定A2单元格字符一共多少个，所以就用99来代替了。
也就是只要第3个字符后面的字符不超过99个，就都能正确提取出来。
用法示例2：
=COUNT(--MID(A2,ROW($1:99),1))
这里的--MID()也可以直接用-MID()，前者返回的是正数，后者返回的是负数。

文章插图
这个公式返回A2单元格中数字的个数（不考虑更复杂情况），和上面一样，不确定A2有多少个字符，因此从A2的第1-99个字符起分别取一个字符，将A2单元格文本每个字符拆开，MID函数部分返回：
={"超";"人";"4";"5";"9";……;""}
这里的99是否可以替换成其他数字呢？
比如说100,200？当然可以，只要超过A2单元格字符长度即可。
为啥弄个99呢，因为99是最大的两位数，应该是想当初“丧心病狂”的既缩短公式长度又保证不出错的用法：）由于99前面没有加$，如果下拉会变成ROW($1:100)，ROW($1:101)等。
6 1%%，1%，10^4，10^6或10000等
这些数字一般是用来将某些数值扩大成原来的1万倍，100倍，或者变成原来值的万分之一，百分之一等。
用法示例：
=INDEX(A$2:A$4,MOD(LARGE(B$2:B$4/1%%+ROW($1:$3),ROW(A1)),10^4))

文章插图
这个公式根据B列成绩从小到大输入A列的姓名。B$2:B$4/1%%+ROW($1:$3)，其中B$2:B$4/1%%将分数变成原来的1万倍，再加上对应的【Excel工作表行号-1】仍然不改变B列数据本来的排序。
但是这样就将分数和行号信息连接在了一起。
B$2:B$4/1%%+ROW($1:$3)生成：{;;}后面四位代表行号信息。
然后用LARGE从小到大输入，再将输出的值求对10^4的余数就得到了行号信息，然后用INDEX从原数据区域提取出来。
例如最大的是LARGR({;;},row(A1))
返回，再取10^4的余数就是3，也就是最大值92对应的行号信息，然后用INDEX提取出来即可。
7 1和24
Excel里，时间和日期都是数字，可以显示成不同的样式。
1代表1天，代表24个小时，代表86400秒等等。
用法示例：
A2是上班时间，B2是下班时间，C2输入B2-A2得到的0.38代表0.38天。

文章插图
如果C2设置成时间格式，可以返回实际时常是9个小时。

文章插图
如果要显示小时数，而不是以9:00这种格式显示，可以再乘以24：

文章插图
8 1900/4/3或类似1900年开头的日期数据
这种返回值一般是两个日期减差或者一个不大的数字显示成了日期格式。
用法示例：

文章插图
两个日期相减期望得到相隔的天数，但是由于C2单元格也是日期格式，所以就显示成了1900/4/3这种，其实结果是94天。
Excel从1900年1月1日起算1,1900年1月2日算2，依次类推。因为单元格返回数值是94，显示成日期格式就成了1900年4月3日。
更改C2单元格数据格式，显示成数值即可：

文章插图
9 42,771或者其他4万多、3万多的数字
这个和上面的类似，一般是某个日期显示成了数值格式。
比如：

文章插图
1 9E+307
9E+307是科学计数法表示的一个数字，就简单理解成是Excel支持的一个很大的数字就可以了。
用法示例：
=(9E+307,A:A)
根据函数的性质，提取A列最后一个数字。

文章插图
2 4^8或65536或100
4^8=4的8次方=65536 ，也是一个比较大的数字。
因为2003版最大支持65536行，所以以前会经常看到这个数字。一般用途是排除错误值，当公式下拉超过实际返回数据行数后显示空值。
用法示例：
=INDEX(A:A,SMALL(IF(A$2:A$5>80,ROW(A$2:A$5),4^8),ROW(A1)))&""
公式意思是将A列大于80的数值顺序输出来。
因为原始数据一共有2个大于80，当公式下拉到第3个单元格时，因为已经没有要输出的数据了，所以就引用了A列的第4^8（65536）个单元格（一个用不到的空单元格），这样就返回了空值。

文章插图
如果原始数据一共就80多行，那么第100行就是用不到的空单元格，公式中的4^8也可以替换成100 。
3 1和0
这两个数字用法实在是太多了。
比如：0可以在判断的时候当FALSE用，可以用某些文本数字+0变成数值，用-(0&mid函数提取出的空)可以把空值转化成0避免出现错误值……
1可以在判断的时候当TRUE，1可以当做1天24小时来计算时间，1可以是比0大的数字被用在(1,0/条件判断,数据)这样的组合里……还是碰到相关函数公式再单独研究其用法吧。
关于0的使用，建议到函数版去查找一个《“0”活多变的公式与格式》的帖子。
4 1/17或5^19或5/19等等
这几个数字有一个特点，就是运算返回的值里面包括0~9所有的10个数字。
比如：1/17=0.17647，5^19=125，5/19=0.2632 。
这个一般用于FIND函数在单元格中查找数字时避免出现错误值。
用法示例：
=MIN(FIND(ROW(1:10)-1,A2&1/17))

文章插图

文章插图
这个公式用于查找A2单元格第一个数字出现的位置。
ROW(1:10)-1返回{0;1;2;3;4;5;6;7;8;9} ，然后FIND({0;1;2;3;4;5;6;7;8;9},A2&1/17)查找0~9分别在A2&1/17中出现的位置，避免了查找某个A2中本来没有的数字出错。
比如A2单元格中不包含1 ，那么FIND查找1时会出错。
如果A2包含负数，可以用下面的公式来完成。
用A2&-1/17
=MIN(FIND(LEFT(ROW($1:$11)-2),A2&-1/17))

文章插图
5 99
99是一般用在文本函数中的，也充当一个大数字的角色。
用法示例1：
=MID(A2,3,99)

文章插图
这个公式返回A2单元格中数字的个数（不考虑更复杂情况），和上面一样，不确定A2有多少个字符，因此从A2的第1-99个字符起分别取一个字符，将A2单元格文本每个字符拆开，MID函数部分返回：
={"超";"人";"4";"5";"9";……;""}
这里的99是否可以替换成其他数字呢？
比如说100,200？当然可以，只要超过A2单元格字符长度即可。
为啥弄个99呢，因为99是最大的两位数，应该是想当初“丧心病狂”的既缩短公式长度又保证不出错的用法：）由于99前面没有加$，如果下拉会变成ROW($1:100)，ROW($1:101)等。
6 1%%，1% ， 10^4，10^6或10000等
这些数字一般是用来将某些数值扩大成原来的1万倍， 100倍，或者变成原来值的万分之一，百分之一等。
用法示例：
=INDEX(A$2:A$4,MOD(LARGE(B$2:B$4/1%%+ROW($1:$3),ROW(A1)),10^4))

文章插图
如果C2设置成时间格式，可以返回实际时常是9个小时。

文章插图
如果要显示小时数，而不是以9:00这种格式显示，可以再乘以24：

文章插图
8 1900/4/3或类似1900年开头的日期数据
这种返回值一般是两个日期减差或者一个不大的数字显示成了日期格式。
用法示例：

文章插图
9 42,771或者其他4万多、3万多的数字
这个和上面的类似，一般是某个日期显示成了数值格式。
比如：
【Excel：公式中的这些特殊数字】

文章插图