贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

有網友想要在 Excel 中,判斷一個數字是位那個數字區間,例如在下圖中,0.32 是位於31%~40%這個級別中,所以使用了以下的公式:(假設資料位於儲存格A2)

=IF(A2<=10%,"10%以下",IF(AND(A2>=11%,A2<=20%),"11%~20%",
IF(AND(A2>=21%,A2<=30%),"21%~30%",IF(AND(A2>=31%,A2<=40%),
"31%~40%",IF(AND(A2>=41%,A2<=50%),"41%~50%",IF(AND(A2>=51%,
A2<=60%),"51%~60%",IF(AND(A2>=61%,A2<=70%),"61%~70%")))))))

以上的公式受限於 IF 函數的七層限制,所以只能判斷至 61%~70%。而且如果公式的級別要再擴充,則更是不可能。

改良的方法,建議使用 VLOOKUP 函數。參考下圖,要根據A欄的數值判定其級別。對照表位於D欄和E欄。

儲存格B2:=VLOOKUP(A2,$D$2:$E$11,2,TRUE)

複製儲存格B2,往下各列貼上。

如果日後要擴充更多的級別,公式也是通用的。

創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

vincent 發表在 痞客邦 留言(2) 人氣()


留言列表 (2)

發表留言
  • 春秋大飯店
  • IF 函數的七層限制,是在 Excel 2003 以前的版本。
    現在早已擴充到 64 層了。
  • 謝謝您。
    因為問這個問題的讀者使用的版本有7層的限制,再者,這個問題的重點並不是在於幾層的限制,而是他可以使用其他結構較簡單的公式寫法,以利日後的維護與更新。就像本題的數字區間若要區隔為100個呢?是否回到原始問題(超過64)。
    如果你有程式設計的基礎,可能可以理解本例就像使用巢狀結構
    IF XXX Then
    IF XXX Then
    IF XXX Then
    ...
    End IF
    End IF
    End IF

    Select Case XXX
    Case X:
    Case X:
    Case X:
    ...
    End Select
    的差異。
    使用VLOOKUP函數也不是唯一解,還有其他解法,大家再自行思考。
    再次感謝你提供其他網友不同的想法。

    vincent 於 2015/09/01 22:08 回覆

  • 春秋大飯店
  • 我知道,這個例子,可以用很多比 IF 更方便的函數。
    但真的,有些例子,就只有用 IF 函數,才寫得出來。
    例如,想要由下往上找資料,就只能用 IF。 (其他的函數,都只會由上往下找)

    所以,我要表達的是:
    只為了少數幾個,還在用快淘汰版本的人,
    就讓大部份的讀者,誤以為 IF 最多只能用七層。

    這很明顯是錯誤的教學。
  • 謝謝您的指正,我個人是才疏學淺,無法滿足各樣的人。本文的內容是讀者問我的原文,再疊加我的回覆,我的用意是在幫他解決問題,並非用此文來引導或校正任何人。
    由於我發現您多次會誤會我寫作上的用意,而我個人不想花時間解釋太多,因為我只是把我知道的寫出來,不是要做完美的詮釋,也無法叫他人都改用新的版本。
    就好像我的學生中有許多孩子,是經濟弱勢家庭,能有Excel 2003 版可用,對他們而言已經是一種幸福,能用很差的電腦或軟體,能解決他們的問題,或是讓他們的學習能成長,是我們當老師的重大課題。
    基於上述,向您說聲抱歉,此後,您的回應,我將不再回覆,敬請見諒。
    本人寫作部落格文章,原意單純,只是貢獻所學,也當為學生補充教材,雖不完美,但盡力而為,因為我不是要寫一本聖經。而如果因為和網友互動回應而衍生其他不要的問題,實在不是我的原意。如果您真的一直認為我的文章有缺陷,建議您轉往其他部落格,不要再此浪費時間。本篇文章造成您的困擾,在此表達最深的歉意!

    vincent 於 2015/09/02 22:12 回覆

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼