軟體:EXCEL
版本:2007
大家好，今天要來和大家交流一個模式的計算，
這個模式是我在製作臉書餐城遊戲的會計檔案時，一時興起想到的東西。
(詳情可至臉書板搜尋"餐城"和"會計"兩個字)
這次我希望得到的東西是，一個元素(食材)在不同類別中(不同食譜的種類)，
分別出現幾次(該類食譜用到指定食材的次數)。
假設我現在的資料是這樣...
[食譜]
A B C D E
類型 食材 食材 食材 食材
飲料 Cherry Raspberry Sugar Orange
點心 Chocolate Mint Gold dust
前菜 Salmon Crab Milk Cream
主菜 Lobster Lobster Egg Salt
在資料中，每個食譜(就是每一列)都有一個類型，而食譜裡面有1~4個不等的食材
我現在要計算的就是在該分類下，某食材總共有幾個(例：EGG在主菜中出現幾次)
而在旁邊我也準備了食材的資料和統計的表格，如下：
[統計表]
G H I J K L
食材名 前菜 主菜 點心 飲料 總計
Sugar
Flour
Chocolate
Cream
Beef
在這個表格中，我必須把H~K的公式都寫出來，而L可以簡單的用SUM處理。
公式的寫法有兩種
一種是多重篩選加總，一種是先合併內容再計算個數
兩種在下面都會提到。
1.多重篩選加總：
此方法是參考精華區的多重條件寫法，利用SUMPRODUCT配合"--"的數值化，
找出同時符合條件的項目，然後在計算總和，其公式如下：
=SUM(SUMPRODUCT(--($B$2:$B$363=$G2),--($A$2:$A$363=H$1)),
SUMPRODUCT(--($C$2:$C$363=$G2),--($A$2:$A$363=H$1)),
SUMPRODUCT(--($D$2:$D$363=$G2),--($A$2:$A$363=H$1)),
SUMPRODUCT(--($E$2:$E$363=$G2),--($A$2:$A$363=H$1)))
可以在公式中看到，我分成四次判定在加總，其原因在於我找不到可以一次
檢驗四個格位的方法，所以就分開算了...。
說明 => $B$2:$B$363=$G2 判定資料庫中是否符合統計表格的資料，
如果是則為TRUE，如果否則為FALSE，
(例：在SUGAR和點心的欄位中，如果資料庫)
(內的SUGAR正好是在點心的食譜裏面，則會)
(為TRUE，但如是在飲料的SUGAR，則會是FALSE)
-- 把所有的陣列數值化，TRUE轉成1，把FALSE轉成0
經過轉換後，會得到{0,1,0,1,0,1,1,0}像這樣的陣列
sumprodct 將兩個陣列個別相乘後加總，也就是把每一個陣列
的第一個數字相乘、第二個數字相乘…(依此類推)
得到所有的數字後，再全部總和起來。
(到這個部分就把一個欄位的食材全部篩選加總起來了)
sum 加總，把四個欄位所得到的數字全部加起來，就能
得到我們要的答案。
(ps 這邊的解釋可能有些不專業，還請見諒。)
2.先合併內容再計算個數
這個方法是我一開始想到的正解，因為其他方法嘗試不出我要的東西，所以最後用
這個解決。
在寫這個公式前，我們需要新增四個欄位，如下：
[合併資料]
N O P Q
合併1 合併2 合併3 合併4
在這個地方我會把前面的類型和食譜內容合併起來，
例如 "點心"、"SUGAR"就會變成"點心SUGAR"
這個公式是: =$A2&B2 說明=> & 可以把兩邊的資料合併、靠再一起
在這個例子中，就是把"類型"、"食材"
合併成"類型食材"這樣
在合併後就會變成這樣
[合併表格(合併後)]
N O P Q
合併1 合併2 合併3 合併4
飲料Cherry 飲料Raspberry 飲料Sugar 飲料Orange
點心Chocolate 點心Mint 點心Gold dust 點心
前菜Salmon 前菜Crab 前菜Milk 前菜Cream
主菜Lobster 主菜Lobster 主菜Egg 主菜Salt
然後我們在用簡單的COUNTIF來搜尋符合條件的格子就可以了，
所以H~K的公式就是： =COUNTIF($N:$Q,H$1&$G2)
從裡面到外面的說明 => & 一樣是把兩個資料併在一起，在這邊要注意順序
必須和上面合併的"類型食材"是一樣的順序。
COUNTIF 計算範圍內符合條件的儲存格個數。
這樣一來，我們的答案也同樣的就算出來了。
後續想要交流的地方
在想出這兩個方法後，實在內心有所不甘，前面的方法太過冗長，如果有很多個欄位，
那在編輯公式的時候就會很麻煩；而後面的方法則是必須多出另一套欄位，很佔空間，
對於完美主義者來說，不太能接受用兩個公式間接求解(XD)。
其實我一開始有想過很多種方法，其中有一個概念或許可行：
把某個食材在一個食譜中出現的個數算出來，然後在判定前面的類型是否符合
(這個部分可以用相乘來解決，[個數]*[0或1的判定值])然後再用SUM把所有陣列
的數字加總起來，但是這樣的公式一直無法想出來。
不知道板上的高手們，在這樣的情況下，有沒有更簡潔有力的公式寫法呢？
另外這種東西是不是能用樞紐分析來寫？我嘗試了很久，但是得不到我要的結果，
如果網友有對樞紐研究，也希望一同分享。
最後希望大家能多多給予本人指教，謝謝。(XD)
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 114.39.174.249