2012年1月2日 星期一

Excel-計算奇偶數列(欄)儲存格的值

最近又有人問到要計算奇數或偶數列(欄)儲存格的值等問題,只要要計算的儲存格有規律,就可以使用一個公式來完成。你可以分別以陣列公式或 SUMPRODUCT 函數來完成。

參考下圖的數值,試著找出第1列奇數欄的和、第1列偶數欄的和、第1欄奇數列的和、第2欄偶數列的和、所有列奇數欄的和、所有欄偶數列的和、偶數列和偶數欄交會的和等的值。

【運算公式】

以下分別以兩種方式來運算,如果為陣列公式,則輸入完成要按 Ctrl+Shift+Enter 鍵。以下均以 MOD 函數來求某一欄(列)除以 2 的運算結果,來判斷是偶數或奇數。

(1) 計算第1列奇數欄的和

儲存格E12:=SUMPRODUCT((MOD(COLUMN(A1:J1),2)=1)*A1:J1)

儲存格E12:{=SUM(IF(MOD(COLUMN(A1:J1),2)=1,A1:J1))}  (陣列公式)

試比較每一種運算的兩個運算式子,其相似度很高。

(2) 計算第1列偶數欄的和

儲存格E13:=SUMPRODUCT((MOD(COLUMN(A1:J1),2)=0)*A1:J1)

儲存格E13:{=SUM(IF(MOD(COLUMN(A1:J1),2)=0,A1:J1))} (陣列公式)

(3) 計算第1欄奇數列的和

儲存格E14:=SUMPRODUCT((MOD(ROW(A1:A10),2)=1)*A1:A10)

儲存格E14:{=SUM(IF(MOD(ROW(A1:A10),2)=1,A1:A10))} (陣列公式)

(4) 計算第2欄偶數列的和

儲存格E15:=SUMPRODUCT((MOD(ROW(A1:A10),2)=0)*A1:A10)

儲存格E15:{=SUM(IF(MOD(ROW(A1:A10),2)=0,A1:A10))} (陣列公式)

(5) 計算所有列奇數欄的和

儲存格E16:=SUMPRODUCT((MOD(COLUMN(A1:J10),2)=1)*A1:J10)

儲存格E16:{=SUM(IF(MOD(COLUMN(A1:J10),2)=1,A1:J10))} (陣列公式)

(6) 計算所有欄偶數列的和

儲存格E17:=SUMPRODUCT((MOD(ROW(A1:J10),2)=0)*A1:J10)

儲存格E17:{=SUM(IF(MOD(ROW(A1:J10),2)=0,A1:J10))} (陣列公式)

(7) 計算偶數列和偶數欄交會的和等的值

儲存格E18:=SUMPRODUCT((MOD(COLUMN(A1:J10),2)=0)*(MOD(ROW(A1:J10),2)=0)*A1:J10)

儲存格E18:{=SUM(IF(MOD(COLUMN(A1:J10),2)=0,IF(MOD(ROW(A1:J10),2)=0,A1:J10,0),0))} (陣列公式)

3 則留言:

  1. 請問一下 要怎麼把資料連續加總還要相除
    sun(A1:A2)/2
    sun(A1:A3)/3
    sun(A1:A4)/4
    sun(A1:A5)/5
    依此類推
    但是因為有三千多筆資料
    不知道該如何一次設定完成
    --------------------------
    先把A1加到A10 再/10
    一直到A1加到A3000 再/3000

    在一列中要怎麼把資料加完再除
    列中的每一格都要這樣
    有比較快的做法嗎?!
    還是只能一個一個改?

    回覆刪除
  2. 這是我的信箱
    能否請您為我解答這問題?!
    謝謝^^
    f19920429@hotmail.com

    回覆刪除
  3. 您好
    您的問題應該是要求平均嗎?
    可運用公式 AVERAGE(A1:A??)
    公式可在第一個儲存格輸入AVERAGE($A$1:A1)
    儲存格A1以絶對參照表示
    複製後往下各列貼上即可。

    回覆刪除

好康東東