分類彙整:EXCEL

【EXCEL】PivotTable 樞紐分析表

説明

將清單形式的資料整理成表格,
可以自訂縱軸與橫軸的欄位。

範例:研討會人數統計

原始資料名單

場次 負責業務 參加者姓名 更新日期 性別 素食 / 葷食
台北 A組 Mary 9月23日 素食
台中 B組 Moko 9月23日 素食
高雄 B組 Cherry 9月23日 素食
台北 A組 Apple 9月24日 素食
台北 A組 Banana 9月25日 素食
台中 B組 Grape 9月25日 素食
高雄 C組 Vegetable 9月25日 葷食
高雄 A組 Blue 9月25日 葷食
台中 A組 Sky 9月26日 葷食
台中 C組 Ocean 9月26日 葷食
台北 C組 Autumn 9月26日 葷食

建立樞紐分析表步驟:

    1. 選取資料範圍
    2. 標籤塊【插入】→樞紐分析表→確定
    3. 本次分析:場次與負責業務的人數相對關係
    4. 【場次】拉向列方格
    5. 【欄位】拉向欄方格
    6. 【參加者姓名】拉向値方格

※名單更新→同步更新樞紐分析表的資料

EXCEL的樞紐分析表無法即時同步更新(Google Sheet的就可以)
但是,名單更新後,可以手動點選以下路徑,更新樞紐分析表資料

樞紐分析表工具→標籤塊【分析】→重新整理

範例:業績報告(使用加總功能)

原始資料名單

日期 訂單號 客戶 産品 數量 金額
9月23日 A0001 Bank Banana 1  $        1,000
9月23日 A0002 Bank Apple 10  $        5,000
9月23日 A0003 Bank Banana 3  $        3,000
9月24日 A0004 Bank Banana 5  $        5,000
9月24日 A0005 Bakery Apple 3  $        1,500
9月26日 A0006 Bakery Banana 1  $        1,000
9月26日 A0007 Bakery Apple 1  $            500
9月26日 A0008 School Apple 1  $            500
9月26日 A0009 School Apple 2  $        1,000
9月26日 A0010 School Orange 1  $        3,000
9月26日 A0011 School Orange 3  $        9,000
9月29日 A0012 Council Orange 5  $      15,000
9月29日 A0013 Council Mango 1  $      10,000
9月29日 A0014 Council Mango 5  $      50,000

建立樞紐分析表:分析客戶、産品的銷售金額關係。

  • 【産品】拉向列方格
  • 【客戶】拉向欄方格
  • 【金額】拉向値方格
  • ※點選樞紐分析表儲存格→右鍵→摘要値方式→加總

 

EXCEL 比對多元資料方法

原理

excel中的vlookup函數只能用找單一資料,也就是當手上有一筆清單及資料庫如下

資料庫

中文 日文
紅色 レッド
藍色 ブルー
綠色 グリーン
黃色 イエロー
黑色 ブラック
白色 ホワイト
棕色 ブラウン
粉紅色 ピンク

資料清單

紅色
藍色
棕色

經過vlookup比對後可以得出

比對後的資料清單

紅色 レッド
藍色 ブルー
棕色 ブラウン

但當資料庫非一對一情況下,vlookup只能隨機找到一個對應資料,無法全部顯示

資料庫:單一資料擁有多個對應資料

中文 日文
紅色 レッド
紅色 ボルドー
紅色 ダークレッド
藍色 ブルー
藍色 ネイビー
綠色 グリーン
黃色 イエロー
黑色 ブラック
白色 ホワイト
棕色 ブラウン
棕色 ショコラ
棕色 モカ
粉紅色 ピンク

解決方法

  1. 下載充滿函數的excel檔案
  2. 將資料庫部分貼在B跟C欄
  3. 清單貼在E欄

可以找出所有相關資料

紅色 レッド ボルドー ダークレッド
藍色 ブルー ネイビー
棕色 ブラウン ショコラ モカ

EXCEL轉檔CSV方法,解決日文亂碼問題

csv,是一種逗點分隔形式的文字檔。比方説在excel中一個表格長這樣。

這是 一個 表格

這一段資訊轉成csv後就會變成:

"這是","一個","表格"

轉成csv後,可以大幅輕量化,所以很適合資訊量很多的文件傳輸。
而excel支援内建csv存檔格式,所以要存csv也很方便(存檔時選擇存成csv格式)。
BUT!!
因為excel不支援utf-8格式。
文件裡面有日文,轉檔出來的csv會變成亂碼。

一開始是安裝OpenOffice轉檔解決問題,但公司的acer win10毎次用OpenOffice開.xlsx都會出現重複字問題。
比方説某一格是

コード

,用OpenOffice開啓後會變成

コードコードコードコードコードコードコード。

毎次開檔案還要一格一格檢査實在很麻煩(眼睛都要花了…)
所以我後來研究出用access轉檔的方法!既可以解決重複字問題也可以快速轉檔。

條件限制

  1. 需安裝access
  2. 原始文件儲存格内不能有換行資訊,像是這樣
    這是

    換行部分

    一個 表格

    最左邊第一格,有換行資訊。
    如果取消換行也沒差的話可以用先用excel取代功能,將換行資訊(輸入Ctrl+J)取代為無。

excel轉檔csv步驟

  1. 準備好excel文件,確認儲存格内沒有換行資訊。
  2. Ctrl+A全選,格式改為「文字」,存檔,關閉excel。
    excel-to-csv1-min
  3. 找到剛剛存好的xlsx檔案,按右鍵「開啓檔案」,「選擇其他應用程式」,「Access」
  4. 出現這個視窗。甚麼都不要設定一直按「下一步」,「完成」
    excel-to-csv2-min
  5. 點選標籤「外部資料」,「匯出-文字檔」 【快速鍵:Alt + X + T】excel-to-csv3-min
  6. 接著出現匯出視窗,指定好儲存位置後按確定(中間的指定匯出選項不用勾)
  7. 匯出格式-分欄字元,按右下角的進階【快速鍵:V】
  8. 語言-全部,字碼頁-日文(Shift-JIS)
    確認最上面的欄位分隔符號是「,」,文字辨識符號是「”」excel-to-csv4-min
  9. 然後一直按下一步按到完成,結束,關掉Access,接著用excel確認看看剛剛做好的檔案

excel開啓csv步驟

  1. 開一個新的excel
  2. 標籤「資料」,取得外部資料「從文字檔」
  3. 原始資料類型「分隔符號」,檔案原始格式「日文(Shift-JIS)」,下一步
  4. 分隔符號「逗點」打勾,下一步,完成
  5. 打開後檢査完畢,不要存檔直接關掉(如果在這裡存檔的話會變亂碼喔~)