→ JieJuen:本文檔案中做了點實驗，把bbs畫面轉到excel中~蠻有趣的 XD 11/21 05:30
※ 編輯: JieJuen 來自: 218.164.49.72 (05/11 19:10)
本文說明"條件加總"，尤其當條件很多時該如何處理。
(只是很多個條件，但準則並不複雜)
問題整理如下：
有7個人員各屬於三個"課"
一課 大黑 小吳
二課 高 陳 鄭
三課 阿草 阿點
有數據資料庫如下
人名 a項 b項
大黑 5 5
小吳 0 2
高 5 13
陳 5 2
鄭 3 1
阿草 5 4
阿點 0 0
目標：求得各課之加總
結果：
a項 b項
一課 5 7
二課 13 16
三課 5 4
回應原文：
所以舉例中，一課的23應該是5+0=5對吧
23可能是原檔案中還有加其他人?
(但全部人總合剛好23 另一欄也是總合27，太容易誤會)
有您寫的公式後可知，的確是
DSUM SUMIF SUMPRODUCT SUM這幾個函數(選一個)可完成的事
因為各課人員似乎寫明列表比較好
條件比較多 所以適合DSUM 或SUM配合其他函數的陣列公式
http://2y.drivehq.com/p/Dsum.xls
檔案中有十個方法(9個工作表)
我最喜歡的是
DsumOffset (4)
SumMatch (7)
這兩種方法
從Dsum (1)最基本款的公式開始漸漸演變，
至DsumOffset (4)時應該比較好了解~
Sumif (5)
Sum (6) ←SUM 與 SUMPRODUCT
這是三個十分常用的條件加總，
一般情況很好用，但在此人數(條件)多時太麻煩
SumMatch (7)
是稍複雜的陣列公式，但功能性最強
SumMatch (8)
完全精簡寫法，
只是表達若不要額外的列表，可以這樣寫，
(DSUM"必須"有額外列表，這是一般嫌它麻煩的地方)
但更新不易，在此不建議
SumMatch (9)
(7)中無查詢欄位名稱功能(因此例中不需要 此例只需依序求解)
若需要則用此式
===============================================
太多公式在BBS上不易觀看，以下主要是備份用，部分修飾稍微易讀
大部分只寫"一課" 之 a項 計算結果(5)
***
共同參照部分
$A$1:$C$8 或 $A$1:$C$7(沒有第一列標題時)
人名 a項 b項
大黑 5 5
小吳 0 2
高 5 13
陳 5 2
鄭 3 1
阿草 5 4
阿點 0 0
$E$6:$E$8 或 $E$6:$E$9 (工作表1~6使用)
(E欄就對了)
↓
E F G
5 一課 二課 三課
6 人名 人名 人名
7 大黑 高 阿草
8 小吳 陳 阿點
9 / 鄭 / (空格輸入一個不在"人名"之中的字 如 / )
F$1
a項
***
Dsum (1)
=DSUM($A$1:$C$8,2,$E$4:$E$6) DSUM的第二參數field可寫上要傳回第幾欄(2 3)
Dsum (2)
=DSUM($A$1:$C$8,F$1,$E$6:$E$8) 增加項目名(a項 b項)確保資料正確
Dsum (3)
=DSUM($A$1:$C$8,F$1,$E$6:$E$9) 使用Offset的前置作業(條件列表空格加字 / )
DsumOffset (4)
=DSUM($A$1:$C$8,F$1,OFFSET($E$6:$E$9,,ROW(1:1)-1)) 本式可用拖曳(複製)完成
Sumif (5)
=SUMIF($A$1:$A$7,$E$7,B1:B7)+SUMIF($A$1:$A$7,$E$8,B1:B7)
Sum (6)
{=SUM((($A$1:$A$7=$E$7)+($A$1:$A$7=$E$8))*B$1:B$7)}
=SUMPRODUCT(($A$1:$A$7=$F$7)+($A$1:$A$7=$F$8)+($A$1:$A$7=$F$9),B$1:B$7)
第二式是"二課"
***
以下陣列公式
SumMatch (7)
=SUM(IF(ISNUMBER(MATCH($A$2:$A$8,$F5:$H5,)),B$2:B$8))
SumMatch (8)
=SUM(IF(ISNUMBER(MATCH($A$1:$A$7,{"大黑","小吳"},)),B$1:B$7))
SumMatch (9)
=SUM(IF(ISNUMBER(MATCH($A$2:$A$8,$F5:$H5,)),
INDEX($B$2:$C$8,,MATCH(F$1,$B$1:$C$1,))))
SumMatch (7)(9)的條件列表與1~6工作表不同(為使查詢條件列表較方便)
$F5:$H5
E F G H
一課 大黑 小吳
二課 高 陳 鄭
三課 阿草 阿點
SumMatch (8)不需條件列表
===============================================
備份完畢。。。
※ 引述《ichol (深色憂鬱的心)》之銘言：
: ※ 引述《ichol (深色憂鬱的心)》之銘言：
: : 軟體:
: : EXCEL
: : 版本:2000
: : 現在有一個表格
: : 假設
: : 一課 23 27
: : 二課 17 8
: : 三課 15 6
: : 大黑 5 5
: : 小吳 0 2
: : 高 5 13
: : 陳 5 2
: : 鄭 3 1
: : 阿草 5 4
: : 阿點 0 0
: : 一課有 大黑 小吳
: : 二課有 高 陳 鄭
: : 三課 有阿草 阿點
: : 想做一課人員的加總 且!當下列大黑 小吳 高 陳 數據作排序的時候
: : 各課的加總不會因為下面排序而改變
: : 主要是主管又說 如果他想下面由高到低排序 上面的又會變動到了
: : 本來我自己是看一課有誰 就用sum 可是主管這麼說就不可以用了=_=...
: : 可以幫幫忙一下嗎@@
: 因為1課是大黑 小吳
: 就把B1格 設定成=B4+B5
: 其他類推
: 結果下面大黑 小吳部分 主管說他可能會排序 會造成B4 B5不是原來的數字
: 想說有沒有可以跟著變的方法
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 218.164.51.13
※ 編輯: JieJuen 來自: 218.164.51.13 (11/21 02:06)