2013年10月22日 星期二

Excel-計算儲存格裡最後一個數字之個數(RIGHT,VALUE,SUMPRODUCT)

有人問到如下圖中的代碼欄位,其中的每個代碼都有一個檢查碼(最後一個數字),想要來找出最後一碼是 0 ~ 1 的個數和分別為多少?

【準備工作】

選取A欄中有代碼的儲存格,按一下 Ctrl+Shift+F3 ,勾選「頂端列」,定義名稱:代碼。

【輸入公式】

儲存格D2:=SUMPRODUCT(--(VALUE(RIGHT(代碼,1))=ROW(1:1)-1))

RIGHT(代碼,1):透過 RIGHT 函數取出所有代碼中最右(後)一碼成為一個陣列,目前這個陣列都是數字字串("0”,"1”,…)。

VALUE(RIGHT(代碼,1)):透過 VALUE 函數將上述的數字字串陣列("0”,"1”,…)轉換為數字的陣列(1,2,…)。

ROW(1:1)-1:ROW(1:1)=1,當往下複製時可以產生 ROW(2:2)=2、ROW(3:3)=3、…,再減一可得 0 ~ 9。

最後透過 SUMPRODUCT 函數計算總和,其中「--」運算,乃將公式 VALUE(RIGHT(代碼,1))=ROW(1:1)-1) 所得的 True/False 陣列轉換為 1/0 陣列。

複製儲存格D2,貼至儲存格D2:D11。

沒有留言:

張貼留言

好康東東