(2016-02-09 21:40:43)
轉載
▼
標籤： 分類：
如何看懂一個Excel公式？
公式返回了錯誤值，或者公式返回的值不正確，如何查找出錯的原因？
下面介紹分析公式的方法。
一、分析公式的要點
一般地，數值既是公式處理的對象，也是公式中每個計算步驟返回的結果，同時又是公式最終返回的結果。分析公式要注意以下兩點：
1、公式中各部分的計算次序；
2、公式中每個計算步驟返回的值及其數值類型。
公式中的每個計算步驟，都是一個表達式，表達式不包括公式開頭的等號。例如，對於下面的公式：
=B1*IF(A1>5,10%,20%)
其中的“A1>5”“IF(A1>5,10%,20%)”“B1*IF(A1>5,10%,20%)”都是公式中的表達式。
能夠弄清楚公式中每個計算步驟返回值的數值類型，才算是真正理解公式。儘管很多時候Excel可以自動把數值轉換成適合的類型來計算，但有時候，不同的數值類型可能產生不同的計算結果。
例如，如果單元格A1是數字值1，那麼表達式A1=1返回TRUE；如果A1是文本型數字1，那麼表達式A1=1返回FALSE。
二、分析公式的方法
Excel提供了錯誤檢查、追蹤引用單元格和從屬單元格、公式求值、監視窗口等多種公式審核功能。本文介紹使用F9鍵和拆解公式兩種方法來分析公式。
（一）使用F9鍵
在編輯欄，選擇公式中的部分內容，然後按F9鍵，則該部分變成其計算結果。
選擇的內容必須是完整的表達式，選擇的函數必須包括完整的函數名其及左右圓括號。
分析公式局部計算結果，需要注意公式中的計算次序。例如公式=2+3*2，如果選擇其中的“2+3”然後按F9鍵，則變成=5*2，但這樣的分析結果是錯誤的。
如果不想修改公式，退出編輯公式時按Esc鍵，或單擊編輯欄左側的“取消”按鈕；如果想以計算結果取代選擇的部分，按Enter，或單擊編輯欄左側的“輸入”按鈕，如果是數組公式則按Ctrl+Shift+Enter。
（二）拆解公式
對於一個複雜的公式，把公式的計算過程拆解成多個公式，將更加易於理解和分析。
如圖，假設在單元格G1輸入下面的公式：
=INDEX(B2:E5,MATCH("B",A2:A5,),MATCH("G",B1:E1,))
公式返回A列為“B”，第1行為“G”相交的單元格D3的值10。
以下按照公式的計算次序，對該公式進行拆解分析。
第一步，選定單元格G1，在編輯欄中把公式中的“MATCH("B",A2:A5,)”拉黑、複製，然後按Esc鍵退出編輯狀態。
選定單元格G2，輸入等號並粘貼上述表達式。也就是在單元格G2輸入以下公式：
=MATCH("B",A2:A5,)
該公式返回2，就是字母“B”位於數組A2:A5中第2行的位置。（注意不是A列的第2行！）
第二步，用同樣的方法，在單元格G3輸入以下公式：
=MATCH("G",B1:E1,)
該公式返回3，就是字母“G”位於數組B1:E1中第3列的位置。
第三步，在單元格G4輸入以下公式：
=INDEX(B2:E5,G2,G3)
好了，現在已經把一個較長的、包含函數嵌套的公式，拆解成三個較短的、只包含一個函數的公式。
分析這三個公式，是不是容易理解得多了！
三、合併公式
反過來，輸入一個較為複雜的公式，往往不是一氣呵成的。還是用上面的例子來解說。
上面的拆解公式，對於包含函數嵌套的公式，按照計算次序，使用由內而外的順序進行拆解；如果要輸入一個包含函數嵌套的公式，可以使用由外而內的順序分別輸入，最後進行合併。
第一步，首先在單元格H2輸入以下公式：
=INDEX(B2:E5,2,3)
公式中直接指定要返回第幾行第幾列的數值。驗證一下計算結果是不是自己想要的。
很好，結果正確。你可以休息一下，理清一下自己的思路：如何繼續求公式中的2和3呢？
第二步，在單元格H3輸入以下公式，求得2：
=MATCH("B",A2:A5,)
第三步，同樣地，在單元格H4輸入以下公式，求得3：
=MATCH("G",B1:E1,)
第四步，最後要把三個公式合併成一個了。
選定單元格H3，把等號後面的表達式“MATCH("B",A2:A5,)”拉黑、複製，按Esc鍵退出編輯；然後選定單元格H2，選擇公式中的2然後粘貼，即把2替換成該表達式。
同樣地，把單元格H4中的表達式“MATCH("G",B1:E1,)”替換掉單元格H2公式中的3。
公式合併完畢！
