如圖1所示,
N函數的基本功能是將日期轉換為序列值,
將邏輯值轉換為 1和 0,
將文本資料轉換為 0,
而數值資料依舊返回數值本身。
其中將邏輯值轉換為 0 和 1 的特性在計數統計的陣列公式中經常被使用。
此外,
N 函數處理一個區域時實際只將區域的左上角儲存格作為參數進行處理,
並且在處理一組只包含一個儲存格的區域時能生成記憶體陣列。
下面分別加以介紹。
圖1 N函數的基本功能
1 將邏輯值轉化為數值如圖2所示, A2:A9儲存格區域是商品清單, D列需要統計同行C列儲存格對應商品在商品列表中出現的次數。
圖2 N函數在計數統計中的應用
定義名稱“商品列表”引用A2:A9儲存格區域, “商品”指代同行C列儲存格, 在D2儲存格輸入陣列公式“=SUM(商品列表=商品)”, 結果返回 0。 這是因為“商品列表=商品”返回{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}, 陣列元素是邏輯值, SUM函數在求和時忽略邏輯值, 所以返回0。
D3儲存格公式“=SUM(N(商品列表=商品))”,
邏輯值轉換為數位0和1的關鍵是讓邏輯值參與運算, 常用的方式就是套一個N函數或者在邏輯值前使用雙重求負“--”運算, 如D4儲存格公式“=SUM(--(商品列表=商品))”也能正確返回商品的出現次數。
2 生成記憶體陣列使用OFFSET函數、INDIRECT函數能夠生成一組引用, 但一般函數在單儲存格陣列公式條件下只能對第一個引用進行處理。 如果對這組引用外套一個 N 函數, 就能得到由這組引用左上角儲存格的值構成的記憶體陣列, 進而可以在儲存格陣列公式中返回預期的結果。
如圖3所示, A2:A11儲存格區域依次輸入了數位1~10, 目前希望對這組資料進行“隔行求和”,
圖3 使用N函數將一組引用轉換為記憶體陣列
使用公式
=OFFSET(A1,{1;3;5;7;9},0)
或
=INDIRECT("A"&{2;4;6;8;10})
能夠依次引用A2、A4……A10, 但直接在外層套一個SUM函數則只能對第一個引用A2儲存格進行求和, 如C3儲存格和C7儲存格公式所示。 在OFFSET函數和INDIRECT函數外套一個N函數即可改變這種情況, 如以下C2儲存格和C6儲存格公式所示。
=SUM(N(OFFSET(A1,{1;3;5;7;9},0)))
=SUM(N(INDIRECT("A"&{2;4;6;8;10})))
文本值經N函數處理後返回0, 並不能保留文本值本身。 此時, 可以使用T函數替換N函數實現生成記憶體陣列的功能, 如圖56-4所示。 在未經T函數處理時, C3儲存格和C7儲存格返回 1, 即只處理第一個引用, 使用 T函數生成記憶體陣列後返回 5, C2儲存格和 C6儲存格公式如下所示。
圖4 T函數生成記憶體陣列
=COUNTA(T(OFFSET(A1,{1;3;5;7;9},0)))
=COUNTA(T(INDIRECT("A"&{2;4;6;8;10})))
本例中COUNTA函數用來統計陣列參數中資料的個數。