網友想要在 Excel 中的一個資料清單(下圖左)，轉換為矩陣計算小計(下圖右)，該如何處理？如果資料範圍會增加時，公式如何設計？
(1) 資料範圍固定
若是資料範圍固定，若要定義名稱，選取儲存格A1:B22，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：部門主管、考績。其定義的內容如下：
部門主管：=工作表1!$A$2:$A$21
考績：=工作表1!$B$2:$B$21
儲存格E2：=SUMPRODUCT((部門主管=$D2)*(考績=E$1))
其中「*」運算相當於使用 AND 邏輯運算。
複製儲存格E2，貼至儲存格E2:H4。
(2) 資料範圍不固定
若是資料範圍不固定，儲存格內容會不斷的增加，則必須設定為動態範圍。藉助 OFFSET 函數來定義名稱，其內容如下：
部門主管：=OFFSET(工作表1!$A$2,,,COUNTA(工作表1!$A:$A),)
考績：=OFFSET(工作表1!$B$2,,,COUNTA(工作表1!$B:$B),)
COUNTA(工作表1!$A:$A)：找出A欄中有文字的內容有幾個，也就是A欄內容增加時，函數結果也會再加 1。
再藉由 OFFSET 函數定義出含有資料的儲存格範圍。
儲存格E2：=SUMPRODUCT((部門主管=$D2)*(考績=E$1)) <==公式內容不變
當A欄和B欄增加儲存格內容時，不需更改公式。
文章標籤
全站熱搜
vincent您好： 我終於找到解決的辦法，在此與您分享。 您常會在教學裡說到「按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱...」 但，這是很不實用的方式。 因為資料經常會新增，但新增的內容卻不會在「定義範圍」裡，於是答案就會出錯... 而改用選取 A:A 或 2:3 的方式，雖然比較實用，但在用到「陣列公式」時，卻會把整列 (欄) 一百多萬筆的資料全部運算一遍，造成 Excel 癱瘓... 最近發現，改用「表格」來「定義名稱、範圍」，就可以同時避免這兩個問題。 希望vincent您接下來，也能改教學這種方式。
春秋大飯店您好，感謝你提供寶貴意見，對我和讀者都是受用無窮。 在函數中使用定義名稱，在教學和實務上有二個基本功能： 一是容易識別儲存格範圍及資料定義(就好像Doname比IP容易記憶、理解) 二是公式修改容易(使用名稱時只要在名稱中修改公式，所有關聯到名稱的公式都可以一併被修改) 。 其次是大部分的函數應用如果在不想寫程式的狀況下，應該只是適用在資料量不大的情形下。如果資料量很大，建議還是以VBA等方式來處理。 再次感謝您！
抱歉剛才忘了登入，我是之前常留言的「春秋大飯店」員工。
vincent 您好： 關於您的解釋，我並不認為如此。 （不是找喳。因為您介紹的方式，我都大量運用過，真的是不夠理想） １資料定義是為了容易識別： 我相信，我所介紹的「表格」，就更容易識別。 例如我最近在用的表格「外幣」公式：=美金[淨值]*美金[@金額]，看起來也很容易識別（因為比一般定義多了個表格名稱，更簡潔易懂）。 重要的是，這些的名稱與範圍，都會自動出現與調整（不像一般的定義，想改名稱或範圍，都得自己手動去改） 再加上，上面的＠符號，代表只對單個儲存格做計算（也是自動出現），而這就不是一般的定義能做到的。 所以，一般的範圍定義，我只會用在：不會增減資料、也不需用到陣列公式的地方。 ２公式修改容易： 以我的操作，想要修改所有關聯到名稱的公式，直接用「全部取代」，絕對比去修改定義還容易許多。 特別是，當同一個公式，有用在多個工作表時。若用定義裡的公式，它會只認唯一的工作表，套用在別的工作表時，就經常會出錯。 還有，製作一個工作表，一定會經常修修改改、增刪或移動儲存格。這時，公式也通常會自動的修改參照位置。 但在定義裡的公式，卻「不會自動修改」，需要去手動修改。 這樣不但麻煩（有在定義裡修改過長公式的，就知道有多麻煩了），還容易出錯。 所以，一般的公式定義，我只會用在： １要讓同事看得懂：例如 =售價 ２超長又不需修改的公式。 ３ VBA的運用，對我而言，和資料量的大小無關。 兩者的功能不太一樣，看哪個方便，就用哪個。 所以我會在大資料用函數，也會在小資料時，用VBA，做一些函數做不到的事。
我想你誤會我的意思了，我沒有否定你的意思，也沒有說我的做法適用於全部，你的方法的確是好方法。我只是提供一種做法給學生，不會要求學生只能用某種方法，而且還鼓勵學生開創各種方法。部落格文章中也不可能把所有方法都介紹，造成誤解，深感抱歉!再次感謝你無私的提供不同解法给廣大讀者。感恩!