公式中的這些特殊數字，你都了解嗎？

在使用函數公式過程中，有一些經常用到的有些特殊含義的數字，這些數字你知道幾個呢？

9E 307是科學計數法表示的一個數字，就簡單理解成是Excel支持的一個很大的數字就可以了。

用法示例：

=LOOKUP(9E 307,A:A)

根據LOOKUP函數的性質，提取A列最後一個數字。

24^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。

這兩個數字用法實在是太多了。

比如：0可以在判斷的時候當FALSE用，可以用某些文本數字 0變成數值，用-(0&mid函數提取出的空)可以把空值轉化成0避免出現錯誤值……

1可以在判斷的時候當TRUE，1可以當做1天24小時來計算時間，1可以是比0大的數字被用在Lookup(1,0/條件判斷,數據)這樣的組合里……還是碰到相關函數公式再單獨研究其用法吧。

關於0的使用，建議到Excelhome函數版去查找一個《「0」活多變的公式與格式》的帖子。

這幾個數字有一個特點，就是運算返回的值裡面包括0~9所有的10個數字。

比如：1/17=0.0588235294117647，5^19=19073486328125，5/19=0.263157894736842。

這個一般用於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))

599

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

這些數字一般是用來將某些數值擴大成原來的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)生成：{830001;770002;920003}後面四位代表行號信息。

然後用LARGE從小到大輸入，再將輸出的值求對10^4的餘數就得到了行號信息，然後用INDEX從原數據區域提取出來。

例如最大的是LARGR({830001;770002;920003},row(A1))

返回920003，920003再取10^4的餘數就是3，也就是最大值92對應的行號信息，然後用INDEX提取出來即可。

Excel里，時間和日期都是數字，可以顯示成不同的樣式。

1代表1天，代表24個小時，代表86400秒等等。

用法示例：

A2是上班時間，B2是下班時間，C2輸入B2-A2得到的0.38代表0.38天。

如果C2設置成時間格式，可以返回實際時常是9個小時。

如果要顯示小時數，而不是以9:00這種格式顯示，可以再乘以24：

81900/4/3或類似1900年開頭的日期數據

這種返回值一般是兩個日期減差或者一個不大的數字顯示成了日期格式。

用法示例：

兩個日期相減期望得到相隔的天數，但是由於C2單元格也是日期格式，所以就顯示成了1900/4/3這種，其實結果是94天。

Excel從1900年1月1日起算1,1900年1月2日算2，依次類推。因為單元格返回數值是94，顯示成日期格式就成了1900年4月3日。

更改C2單元格數據格式，顯示成數值即可：

942,771或者其他4萬多、3萬多的數字

這個和上面的類似，一般是某個日期顯示成了數值格式。

比如：

Excel中類似的常見數字和用法還有不少，實際遇到的時候多思考，試著把數字變成其他值，觀察返回結果的差異，慢慢就會了解其作用了。

作者：ExcelHome版主 shaowu459

推薦閱讀：