今天給大家聊一個數(shù)字:9E307,完整的表述是9E+307。在Excel里它屬于科學(xué)計數(shù)法,表示9*10^307,是一個非常接近Excel能夠容納的最大數(shù)值的數(shù)值。
那這東西有啥用呢?
1、忽略錯誤值求和
如下圖所示,B2:B9區(qū)域內(nèi)存在錯誤值,現(xiàn)在需要在B10單元格求和。
有的朋友會直接使用SUM函數(shù):=SUM(B2:B9)
結(jié)果會返回一個錯誤值,這是由于SUM函數(shù)不會忽視錯誤值,一旦求和范圍內(nèi)存在,就會返回錯誤值自身——傳說中的一見楊過誤終身。
有朋友說,這事簡單啊,可以把SUM函數(shù)改成下面這樣:=SUM(B2,B4:B6,B8:B9)
正確的解答公式是使用SUMIF函數(shù):=SUMIF(B2:B9,”<9E307″)
SUMIF省略了第3參數(shù)求和區(qū)域,也就默認(rèn)使用第1參數(shù)的條件區(qū)域作為求和區(qū)域,求和的條件是小于數(shù)值9E307,也就是對所有的數(shù)值進(jìn)行求和。
2、最后的查詢
如下圖所示,如果需要查詢A列最后出現(xiàn)的數(shù)值。
這個問題最佳公式是使用LOOKUP函數(shù)。 A列最后出現(xiàn)的數(shù)值 ▼=LOOKUP(9E+307,A:A)
這是LOOKUP一個固定的套路,當(dāng)查找值大于查找范圍內(nèi)所有的同類型值時,固定返回最后的同類值。
9E307是一個極大的數(shù)值,所以上述公式會固定返回A列最后出現(xiàn)的數(shù)值。
把這個套路延伸一下,可以解決兩個常見的問題。
如下圖所示,A1:M8是數(shù)據(jù)明細(xì),需要在N列查詢每個人最后考評的月份。
所謂最后考評的月份,也就是最后出現(xiàn)數(shù)值的月份。
比如,A2單元格的看見星光,最后考評的月份是3月(3月就辭職去看奧運(yùn)了),A4單元格的肥書記,最后考評月份是12月(8月就把12月的考評得分做好了)
N2單元格輸入以下公式向下復(fù)制填充即可:
=LOOKUP(9E+307,B2:M2,B$1:M$1)
B2:M2是單行查詢范圍,B$1:M$1是對應(yīng)的單行結(jié)果范圍,9E307比查詢范圍內(nèi)所有的同類數(shù)據(jù)都大,因此返回最后出現(xiàn)的數(shù)值對應(yīng)的月份。
再如下圖所示,A列是數(shù)據(jù)源,需要查詢前面出現(xiàn)的連續(xù)數(shù)值
B列參考公式如下:
=LOOKUP(9E+307,–LEFT(A2,ROW($1:$15)))
LEFT(A2,ROW($1:$15))部分,從A2單元格的左邊,依次提取1、2、3、4……直至15位的數(shù)據(jù),返回結(jié)果:2,20,204,204看,204看見……
再通過減負(fù)運(yùn)算(–),將LEFT函數(shù)的計算結(jié)果轉(zhuǎn)換為數(shù)值。此時純文本無法進(jìn)行數(shù)學(xué)運(yùn)算,例如–204看,它將返回錯誤值#VALUE!。
這個部分計算結(jié)果是一個內(nèi)存數(shù)組▼
{2;20;204;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
LOOKUP天生忽略錯誤值,再用一個比查詢范圍所有數(shù)值都大的9E307進(jìn)行查詢,也就返回最后出現(xiàn)的數(shù)值204。
就這么回事。
承擔(dān)因您的行為而導(dǎo)致的法律責(zé)任,
本站有權(quán)保留或刪除有爭議評論。
參與本評論即表明您已經(jīng)閱讀并接受
上述條款。