Excel製表技巧（50）公式及函數C

2.1.2.9INDEX函數

INDEX函數的功能是返回表格或區域中的數值或對數值的引用。INDEX函數有以下兩種形式：

1．返回數組中指定單元格或單元格數組的數值。公式為

=INDEX(array,row_num,column_num)

式中array—單元格區域或數組常數；

row_num—數組中某行的行序號，函數從該行返回數值。如果省略row_num，則必須有column_num；

column_num—數組中某列的列序號，函數從該列返回數值。如果省略column_num，則必須有row_num。

需要注意的是：如果同時使用row_num和column_num，函數INDEX返回row_num和column_num交叉處的單元格的數值。如果數組只包含一行或一列，則相對應的參數row_num或column_num為可選。如果數組有多行和多列，但只使用row_num或column_num，函數INDEX返回數組中的整行或整列，且返回值也為數組。如果將row_num或column_num設置為0，函數INDEX則分別返回整個列或行的數組數值。如果需要使用以數組形式返回的數值時，請在一個水平單元格區域中將函數INDEX作為數組公式輸入。此外，row_num和column_num必須指向array中的某一單元格，否則，函數INDEX返回錯誤值#REF!。

例如：de<INDEX({1,2;3,4},2,2)de<=4。如果作為數組公式輸入，則：de<INDEX({1,2;3,4},0,2)de<={2;4}

2．返回引用中指定單元格。公式為

INDEX(reference,row_num,column_num,area_num)

式中reference—對一個或多個單元格區域的引用；

如果為引用輸入一個不連續的選定區域，必須用括弧括起來。如果引用中的每個區域只包含一行或一列，則相應的參數row_num或column_num分別為可選項。例如，對於單行的引用，可以使用函數INDEX(reference,column_num)。

row_num—引用中某行的行序號，函數從該行返回一個引用；

column_num—引用中某列的列序號，函數從該列返回一個引用；

area_num—選擇引用中的一個區域，並返回該區域中row_num和column_num的交叉區域。選中或輸入的第一個區域序號為1，第二個為2，以此類推。如果省略area_num，函數INDEX使用區域1。

說明：row_num、column_num和area_num必須指向reference中的單元格,否則，函數INDEX返回錯誤值#REF!。如果省略row_num和column_num，函數INDEX返回由area_num所指定的區域。

函數INDEX的結果為一個引用，且在其他公式中也被解釋為引用。根據公式的需要，函數INDEX的返回值可以作為引用或是數值。例如，公式CELL("width",INDEX(A1:B2,1,2))等價於公式CELL("width",B1)。CELL函數將函數INDEX的返回值作為單元格引用。而在另一方面，公式2*INDEX(A1:B2,1,2)將函數INDEX的返回值解釋為B1單元格中的數字。

2.1.2.10ADDRESS函數

ADDRESS函數的功能是按照給定的行號和列標，建立文本類型的單元格地址。公式為

=ADDRESS(row_num,column_num,abs_num,a1,sheet_text)

式中row_num—在單元格引用中使用的行號；

column_num—在單元格引用中使用的列標；

abs_num—指明返回的引用類型，其中：當為1或省略時為絕對引用，當為2時為絕對行號，相對列標，當為3時為相對行號，絕對列標，當為4時為相對引用；

a1—用以指明A1或R1C1引用樣式的邏輯值。如果A1為TRUE或省略，函數ADDRESS返回A1樣式的引用，如果A1為FALSE，函數ADDRESS返回R1C1樣式的引用；

sheet_text—一文本，指明作為外部引用的工作表的名稱，如果省略sheet_text，則不使用任何工作表名。

de<例如，ADDRESS(2,3)de<等於「$C$2」；de<ADDRESS(2,3,2)de<等於「C$2」。

2.1.2.11INDIRECT函數

INDIRECT函數的功能是返回由文字串指定的引用。此函數立即對引用進行計算，並顯示其內容。當需要更改公式中單元格的引用，而不更改公式本身時，可使用此函數。公式為

=INDIRECT(ref_text,a1)

式中ref_text—對單元格的引用，此單元格可以包含A1樣式的引用、R1C1樣式的引用、定義為引用名稱或對文字串單元格的引用，如果ref_text不是合法的單元格的引用，函數INDIRECT返回錯誤值#REF!；

a1—一邏輯值，指明包含在單元格ref_text中的引用的類型，如果a1為TRUE或省略，ref_text被解釋為A1樣式的引用，如果a1為FALSE，ref_text被解釋為R1C1樣式的引用。

需要注意的是，如果ref_text是對另一個工作簿的引用（外部引用），則那個工作簿必須被打開。如果源工作簿沒有打開，函數INDIRECT返回錯誤值#REF!。

例如：如果單元格A1包含文本"B2"，且單元格B2包含數值1.333，則：de<INDIRECT($A$1)=de<1.333。

上述介紹的幾個查找函數LOOKUP、VLOOKUP、HLOOKUP、MATCH、INDEX、ADDRESS、INDIRECT等在財務分析與決策、預測及建立動態圖表等中是非常有用的。

2.1.2.12矩陣函數——TRANSPOSE函數、MINVERSE函數和MMULT函數

1．TRANSPOSE函數

TRANSPOSE函數的功能是求矩陣的轉置矩陣。公式為

=TRANSPOSE(array)

式中，Array—需要進行轉置的數組或工作表中的單元格區域。

函數TRANSPOSE必須在某個區域中以數組公式的形式輸入，該區域的行數和列數分別與array的列數和行數相同。

【例2-7】假設矩陣A中的值如圖2-18中單元格區域A2:C5，求其轉置矩陣的步驟如下：

圖2-18求轉置矩陣

（1）選取存放轉置矩陣結果的單元格區域，如E2:H4。

（2）單擊工具欄上的【粘貼函數】按鈕，在【粘貼函數】對話框中選取函數TRANSPOSE，在該函數對話框中輸入（可用滑鼠拾取）單元格A2:C5，按「Crtl+Shift+Enter」組合鍵，即得轉置矩陣如圖2-18所示。

利用TRANSPOSE函數可以把工作表中的某些行（或列）排列的數據轉換成列（或行）排列的數據。例如，由於工作需要，要把工作表中的某些行數據改為列數據，若一個一個地改動數據，將是很麻煩也很費時的，而利用TRANSPOSE函數則可以很輕鬆地進行這項工作。但需要注意的是，利用TRANSPOSE函數對行（列）數據進行轉換，則無法單獨修改其中轉換單元格區域中的某單元格的數據。

2．MINVERSE函數

MINVERSE函數的功能是返回矩陣的逆矩陣。公式為

=MINVERSE(array)

式中，array—具有相等行列數的數值數組或單元格區域。

MINVERSE函數的使用方法與TRANSPOSE函數是一樣的。在求解線性方程組時，常常用到MINVERSE函數。

3．MMULT函數

MMULT函數的功能是返回兩數組的矩陣乘積。結果矩陣的行數與array1的行數相同，列數與array2的列數相同。公式為

=MMULT(array1,array2)

式中array1,array2—要進行矩陣乘法運算的兩個數組。

array1的列數必須與array2的行數相同，而且兩個數組中都只能包含數值。array1和array2可以是單元格區域、數組常數或引用。如果單元格是空白單元格或含有文字串，或是array1的行數與array2的列數不相等時，則函數MMULT返回錯誤值#VALUE!。

同樣地，由於返回值為數組公式，故必須以數組公式的形式輸入。

以例2-7的原矩陣和其轉置矩陣為例，它們的乘積矩陣求解方法如下：

（1）選取存放乘積矩陣結果的單元格區域，如J2:L5。

（2）單擊工具欄上的【粘貼函數】按鈕，在【粘貼函數】對話框中選取函數MMULT，在該函數對話框中的array1欄中輸入（可用滑鼠拾取）單元格區域A2:C5，在array2欄中輸入單元格區域E2:H4，然後按「Crtl+Shift+Enter」組合鍵，即得矩陣的乘積如圖2-18所示。

2.1.2.13ROUND函數

ROUND函數的功能是返回某個數字按指定位數舍入後的數字。公式為

=ROUND(number,num_digits)

式中number—需要進行舍入的數字；

num_digits—指定的位數，按此位數進行舍入。

如果num_digits大於0，則舍入到指定的小數位；如果num_digits等於0，則舍入到最接近的整數；如果num_digits小於0，則在小數點左側進行舍入。

利用ROUND函數可以防止利用格式工具欄上的【增加小數位數】或【減少小數位數】所帶來的看起來「假數據」問題的出現，使得工作表上顯示的數據真實可靠。實際上，如果需要調整數據的小數位數，最好使用ROUND函數，而不要使用格式工具欄上的【增加小數位數】或【減少小數位數】按鈕。

例如，若單元格A1中的數據為14.3772，若使用格式工具欄上的【減少小數位數】按鈕將小數位數設為兩位，則單元格A1中的數據顯示為14.38，看起來似乎單元格A1的數據為14.38，但實際上仍為14.3772。若在單元格B1中輸入公式「=3*A1」，則單元格B1中的數據顯示為43.13，也許「不明真相」的人認為單元格B1的數據算錯了（14.38乘以3應該等於43.14），但實際上單元格的數據為43.1316，這種看起來的「假」數據可能會對實際工作帶來不便。因此，正確的方法應是：單元格B1中應輸入公式「=ROUND(3*ROUND(A1,2),2)」，結果為43.14，即先將單元格A1的數據用函數ROUND四捨五入，然後再對計算後的數據四捨五入。

邏輯運算符

1.AND　　用途：所有參數的邏輯值為真時返回TRUE(真);只要有一個參數的邏輯值為假，則返回FALSE(假)。

　　語法：AND(logical1，logical2，…)。

　參數：Logical1，logical2，…為待檢驗的1～30個邏輯表達式，它們的結論或為TRUE(真)或為FALSE(假)。參數必須是邏輯值或者包含邏輯值的數組或引用，如果數組或引用內含有文字或空白單元格，則忽略它的值。如果指定的單元格區域內包括非邏輯值，AND將返回錯誤值#value!。

　　實例：如果A1=2、A=6，那麼公式「=AND(A1A2)」返回FALSE。如果B4=104，那麼公式「=IF(AND(1

　　2.FALSE

　　用途：返回邏輯值FALSE。

　　語法：FALSE()

　　參數：該函數不需要參數

　　實例：如果在A1單元格內輸入公式「=FALSE()」，回車後即可返回FALSE。若在單元格或公式中輸入文字FALSE，Excel會自動將它解釋成邏輯值FALSE。

　　3.IF

　　用途：執行邏輯判斷，它可以根據邏輯表達式的真假，返回不同的結果，從而執行數值或公式的條件檢測任務。

　　語法：IF(logical_test，value_if_true，value_if_false)。

　　參數：Logical_test計算結果為TRUE或FALSE的任何數值或表達式;value_if_true是Logical_test為TRUE時函數的返回值，如果logical_test為TRUE並且省略了value_if_true，則返回TRUE。而且value_if_true可以是一個表達式;value_if_false是Logical_test為FALSE時函數的返回值。如果logical_test為FALSE並且省略value_if_false，則返回FALSE。value_if_false也可以是一個表達式。

　　實例：公式「=IF(C2>=85，"A"，IF(C2>=70，"B"，IF(C2>=60，"C"，IF(C2<60，"D"))))」，其中第二個IF語句同時也是第一個IF語句的參數。同樣，第三個IF語句是第二個IF語句的參數，以此類推。例如，若第一個邏輯判斷表達式C2>=85成立，則D2單元格被賦值「A」;如果第一個邏輯判斷表達式C2>=85不成立，則計算第二個IF語句「IF(C2>=70」;以此類推直至計算結束，該函數廣泛用於需要進行邏輯判斷的場合。

　　4.NOT

　　用途：求出一個邏輯值或邏輯表達式的相反值。如果您要確保一個邏輯值等於其相反值，就應該使用NOT函數。

　　語法：NOT(logical)

　　參數：Logical是一個可以得出TRUE或FALSE結論的邏輯值或邏輯表達式。如果邏輯值或表達式的結果為FALSE，則NOT函數返回TRUE;如果邏輯值或表達式的結果為TRUE，那麼NOT函數返回的結果為FALSE。

　　實例：如果A1=6、A2=8，那麼公式「=NOT(A1

　　5.OR

　　用途：所有參數中的任意一個邏輯值為真時即返回TRUE(真)。

　　語法：OR(logical1，logical2，...)

　　參數：Logical1，logical2，...是需要進行檢驗的1至30個邏輯表達式，其結論分別為TRUE或FALSE。如果數組或引用的參數包含文本、數字或空白單元格，它們將被忽略。如果指定的區域中不包含邏輯值，OR函數將返回錯誤#value!。

　　實例：如果A1=6、A2=8，則公式「=OR(A1+A2>A2，A1=A2)」返回TRUE;而公式「=OR(A1>A2，A1=A2)」返回FALSE。

　　6.TRUE

　　用途：返回邏輯值TRUE。

　　語法：TRUE()

　　參數：該函數不需要參數

　　實例：如果在A1單元格內輸入公式「=TRUE()」，回車後即可返回TRUE。若在單元格或公式中輸入文字TRUE，Excel會自動將它解釋成邏輯值TRUE。函數TRUE主要用於與其它電子表格程序兼容。

Excel公式處理文本有妙招

　　一、判斷單元格數據類型是否為文本　　有時，我們需要判斷單元格中是否包含文本，這時可以藉助ISTEXT函數（具體函數功能以及用法請參閱《Excel常用函數及實例剖析》），如圖1所示，在B2單元格中輸入公式「=ISTEXT(A2)」。如果單元格中包含文本，則返回值為TRUE，反之，返回值為FALSE。

　　二、確定文本字元串的長度　　當需要確定文本字元串的長度時，利用LEN函數可以很容易得出答案（具體函數功能以及用法請參閱《Excel常用函數及實例剖析》）。如圖2所示，在B2單元格中輸入公式「=LEN(A2)」，複製到恰當位置後，即可得出A列單元格中相應字元串的長度。

　　三、從文本字元串中提取字元　　有時我們可能需要從文本字元串中提取字元，比如從姓名字元串中提取出姓，從包含國家和城市的字元串中提取出城市名等等。在這種情況下，可以供我們使用的常用函數有三個：LEFT、RIGHT和MID。　　下面我們用三個實例進一步的體會它們的使用方法：

　　1.使用LEFT函數提取姓名字元串中的姓字元　　2.使用RIGHT函數提取城市名稱　　3.使用MID函數提取區域字元　　四、將數值轉換為文本並以指定格式顯示　　在某些任務中，我們需要將數值轉換為文本，並以指定的格式顯示，比如在將金額小寫轉換為大寫格式的過程中，就有這種需求。這時在公式中利用TEXT函數可以很好地解決問題。

　　五、在文本中進行替換　　某些情況下，我們需要將文本字元串中的一部分替換為其他文本，可以在公式中使用這兩個函數：SUBSTITUTE和REPLACE。
推薦閱讀：