9E307在Excel中還能這么用？（excel 9e307）

9E307在Excel中還能這么用？（excel 9e307）

手機(jī)如何做表格：點(diǎn)擊查看

今天給大家聊一個(gè)數(shù)字：9E307，完整的表述是9E+307。在Excel里它屬于科學(xué)計(jì)數(shù)法，表示9*10^307，是一個(gè)非常接近Excel能夠容納的最大數(shù)值的數(shù)值。

那這東西有啥用呢？

打個(gè)響指，我舉幾個(gè)小例子。

如下圖所示，B2:B9區(qū)域內(nèi)存在錯(cuò)誤值，現(xiàn)在需要在B10單元格求和。

有的朋友會(huì)直接使用SUM函數(shù)：

=SUM(B2:B9)

結(jié)果會(huì)返回一個(gè)錯(cuò)誤值，這是由于SUM函數(shù)不會(huì)忽視錯(cuò)誤值，一旦求和范圍內(nèi)存在，就會(huì)返回錯(cuò)誤值自身——傳說中的一見楊過誤終身。

有朋友說，這事簡單啊，可以把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)行求和。

如下圖所示，如果需要查詢A列最后出現(xiàn)的數(shù)值。

這個(gè)問題最佳公式是使用LOOKUP函數(shù)。

A列最后出現(xiàn)的數(shù)值

=LOOKUP(9E+307,A:A)

這是LOOKUP一個(gè)固定的套路，當(dāng)查找值大于查找范圍內(nèi)所有的同類型值時(shí)，固定返回最后的同類值。

9E307是一個(gè)極大的數(shù)值，所以上述公式會(huì)固定返回A列最后出現(xiàn)的數(shù)值。

把這個(gè)套路延伸一下，可以解決兩個(gè)常見的問題。

如下圖所示，A1:M8是數(shù)據(jù)明細(xì)，需要在N列查詢每個(gè)人最后考評(píng)的月份。

所謂最后考評(píng)的月份，也就是最后出現(xiàn)數(shù)值的月份。

比如，A2單元格的看見星光，最后考評(píng)的月份是3月（3月就辭職去看奧運(yùn)了），A4單元格的肥書記，最后考評(píng)月份是12月（8月就把12月的考評(píng)得分做好了）

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ù)的計(jì)算結(jié)果轉(zhuǎn)換為數(shù)值。此時(shí)純文本無法進(jìn)行數(shù)學(xué)運(yùn)算，例如–204看，它將返回錯(cuò)誤值#VALUE!。

這個(gè)部分計(jì)算結(jié)果是一個(gè)內(nèi)存數(shù)組

{2;20;204;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

LOOKUP天生忽略錯(cuò)誤值，再用一個(gè)比查詢范圍所有數(shù)值都大的9E307進(jìn)行查詢，也就返回最后出現(xiàn)的數(shù)值204。

就這么回事。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。