學不完．教不停．用不盡

在 Excel 的工作表中有一個數值清單（參考下圖），如何分別計算 1, 2, 3 位數的個數和計算 1, 2, 3 位數的總和？

【公式設計與解析】

選取儲存格A1:A26，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：數值。

1. 計算 1, 2, 3 位數的個數

儲存格D2：=SUMPRODUCT((LEN(數值)=1)*1)

利用 LEN 函數計算數值的字數（=位數），若等於「1」表示為1位數。

公式「*1」的作用為讓「LEN(數值)=1」所傳回的邏輯判斷結果 TRUE／FALSE 陣列，經由運算轉換為 1／0 陣列。

2 位數和 3 位數的處理原則相同，公式如下：

儲存格D3：=SUMPRODUCT((LEN(數值)=2)*1)

儲存格D4：=SUMPRODUCT((LEN(數值)=3)*1)

 

2. 計算 1, 2, 3 位數的總和

儲存格D5：=SUMPRODUCT((LEN(數值)=1)*數值)

利用 LEN 函數計算數值的字數（=位數），若等於「1」表示為1位數。

公式「*數值」的作用為讓「LEN(數值)=1」所傳回的邏輯判斷結果 TRUE／FALSE 陣列，只會留下「1」對應的數值，再由 SUMPRODUCT 函數予以加總，即為所求。

儲存格D6：=SUMPRODUCT((LEN(數值)=2)*數值)

儲存格D7：=SUMPRODUCT((LEN(數值)=3)*數值)