贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

在 Excel 中有一組資料表(如下圖左),如果想要根據號碼,以特定分組人數來分組,進而求得各組小計(如下圖右),該如何處理?

為了說明方便,先執行名稱的定義:

選取儲存格A1:B22,按一下 Ctrl+Shift+F3 鍵,定義名稱:號碼、數值。再選取儲存格D1:D2,按一下Ctrl+Shift+F3 鍵,定義名稱:分組人數。

【公式】

儲存格F2:=SUMPRODUCT((號碼>(ROW(1:1)-1)*分組人數)*(號碼<=ROW(1:1)*分組人數)*數值)

號碼>(ROW(1:1)-1)*分組人數:找出號碼大於第 N-1 組的陣列(True/False),其中以ROW(1:1)來表示第N組。

號碼<=ROW(1:1)*分組人數:找出號碼小於或等於第 N 組的陣列(True/False)。

透過 SUPRODUCT 函數,將符合上述二個條者(二者皆為 True),再乘以「數值」陣列,即為所求。

複製儲存格F2,往下各列貼上。

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

這次來練習,在 Excel 中輸入一個數(例如45789),如何取出其中的每一位數(4,5,7,8,9)?為了說明方便,假設輸入的數字最多有5位數。

儲存格B2:=MID(RIGHT("00000"&$A2,5),COLUMN(A:A),1)

RIGHT("00000"&$A2,5):將 "00000" 串接原來的數,再透過 RIGHT 函數取出由右算起的5 個字。將不滿 5 位數者,在其左側以 0 填補。

MID(RIGHT("00000"&$A2,5),COLUMN(A:A),1):透過 COLUMN 函數指定第幾個數,其中 COLUMN(A:A)=1、COLUMN(B:B)=2、…。透過 MID 函數逐一取出各個位數。

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在 Excel 的資料表中,可能一個數列中包含了沒有規則排序的一些正數和一些負數(參考下圖),例如數列中有收入和支出的資料等。如果想要以此計算正數和、負數和該如何輸入公式。

選取儲存格B1:B21,按一下 Ctrl+Shift+Enter 鍵,定義名稱:數值。

儲存格F2:=SUMPRODUCT(--(數值>=0),數值)

儲存格F3:=SUMPRODUCT(--(數值<0),數值)

上式中的「--」,可以將關係運算(<0)所得的 True/Fasle 陣列,轉換為 1/0 陣列。

如果你想以陣列公式來練習,公式改為:

儲存格F2:{=SUM(IF(數值>=0,數值))}

儲存格F3:{=SUM(IF(數值<0,數值))}

以上為陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼