2016年8月13日 星期六

試算表按姓名/國別/分類/群組/... 等等標籤來分項/分組加總

你有一個試算表, 其中有一個數字欄位 (例如金額/人口/面積), 還有一個文字欄位 (例如商品名稱/團隊名稱/姓名), 現在想要根據這個文字欄位來對數字欄位分項/分組加總, 把相同商品名稱 (或相同團隊名稱、 或相同姓名) 的所有列的數字欄位全部累加起來, 該怎麼做? 在 LibreOffice/OpenOffice/google sheet/excel 等等各家試算表軟體裡面, 都有 (1) subtotal (2) pivot table (3) sumif 三種方式都可以達到相同的效果。 本文採用 LibreOffice Calc, 分別以這三種方法來處理這兩個範例:

  1. 處理 blockbuster.csv 「有史以來票房前五十名的電影及導演列表」 (從 1 2 整理出來的) 從而製作出 「導演-導過最賣座幾部片票房總額」 的對照表 ( 成果檔)。
  2. 處理 countries.csv 「全球各國人口及面積列表」, (從 這裡 整理出來的) 從而製作出 「六大洲人口密度列表」 ( 成果檔)。

這三個方法也可以拿來應用於統計各國奧運得獎數、 朋友旅遊結束後拆帳、 公司部門之間比較績效等等場合。

方法一: subtotal

用 subtotal 分類加總

  1. 框選原始資料, 含第一列表頭 (欄位名稱)。
  2. Data => Subtotals
  3. "group by" 選 「分類用的文字欄位」 (導演姓名或洲別); "calculate subtotals for" 勾選 「要加總的數字欄位」 (票房/人口/面積)

這個方法步驟最少、 也有很方便的各種函數可選, 不限加總 (可求各組最大最小等等)。 唯一的缺點是它把結果跟原始資料混在一起, 看起來很雜亂, 也不方便後續處理。 以上參考 OO.o 官網 subtotal 文件

方法二: pivot table 樞紐表

各家試算表軟體裡面都有一個 pivot table 樞紐表 的功能 (在舊版的 OO.o 當中叫做 data pilots) 可以處理我們的問題。

  1. 用 pivot table 樞紐表根據某文字欄位(姓名/類別/群組)對某數字欄位加總 在原始表格 (包含最上面「各欄位名稱」那一列表頭, 重要!) 的上下左右四週各留至少一列/一欄空白。
  2. 把遊標放在原始表格的左上角即可 (第一欄的欄位名稱那一格), 不必選取整個表格。
  3. 從選單的 「Insert」 叫出 「Pivot Table」。
  4. 選預設的 「current selection」 即可。
  5. 從右側 「availabel fields」 把 「導演」 (想要拿來分類的文字欄位名稱) 拉到左側的 「row fields」; 把 「票房」 (想要加總的數字欄位名稱) 拉到中間的 「data fields」。
  6. 按 「OK」, 就會在一個新的分頁產生分類加總表格。

pivot table 產生之後, 就不太方便隨意編輯。 我喜歡複製, 然後用 edit => paste special 把它的文字數字內容貼到原始表格旁邊,再做後續處理。

如果原始表格內有好幾個數字欄位, 還可以把它們全都拉到 data fields, 這樣就可以對各個數字欄位分別分類加總。 例如計算六大洲人口密度時, 就可以把 Population 跟 Area 拉到 data fields 各自按照洲別加總。 (至於 GDP、 壽命、 出生率、 死亡率等等數字就免了 -- 加總沒什麼意義。) 注意: 這種情況下, 請不要去動那個原本位於 「column fields」 的 「data」。 我原先把它拉到右側 「availabel fields」 (丟掉), 結果出來的加總變成每分類底下有好幾列數字垂直疊在一起, 醜醜的又不便後續處理。

如果把兩個文字欄位拉到 row fields, 就會產生 「主分類-子分類」 的效果。

以上參考這篇: Data Pilots (Pivot Tables) in OpenOffice Calc

方法三: unique filter 去除重複列, 再 sumif 條件加總

用 unique filter 建立分類/分項名稱清單 喜歡鍵盤文字勝過滑鼠選單的讀者可能會偏好這個方法。

  1. 把分類/分項那一欄 (例如導演姓名或六大洲別) 的所有內容 (不包含標頭欄位名稱) 剪貼到一個新分頁。
  2. 在新分頁上對複製過來的新資料排序: Data => Sort Ascending
  3. Data => More Filters => Standard Filters 叫出 filter 對話框。
  4. 不要有任何過濾條件 (第一列的 field name 改成 "-none-"), 要勾選 "no duplications", 按 OK 產生分類/分項名稱清單。 (所有導演姓名, 或六大洲名稱, 各只出現一次。)
  5. 把結果複製貼上回原始資料分頁的旁邊。
  6. 用 sumif 分類加總 在導演姓名或六大洲旁邊加上 sumif 運算式。 如圖, 原始資料的導演姓名在 F 欄, 要加總的是 D 欄的票房, 去除重複的導演姓名清單在 K 欄, 所以 L7 的公式長得像這樣: sumif(F$7:F$57, K7, D$7:D$56) 。 「拿 K7 的姓名去 F 欄查, 相同的話就把 D 欄的數字納入總和。」 然後就可以用 L7 的公式填滿 L 欄。

如果你習慣用命令列, 可以這樣取代 1 到 4 步: 先將分類欄的內容剪貼到 names.txt 文字檔, 再下這個指令: sort names.txt | uniq -c 。 Gnumeric 試算表不支援 pivot table 跟 subtotal/group-by, 就必須採用這種方式。

採用這個方法, 除了可以用 sumif 分項/分類/分組求和, 也可以用 countif 數每項/每類/每組含有幾列, 進而再從這兩組數字求出各項/各類/各組的平均值、 標準差等等統計數據; 但無法求各項/各類/各組的最大最小值。

沒有留言:

張貼留言