Ming Chang's Blog

大三統計套裝軟體（Excel）上課操作記錄 - Ming Chang

大三統計套裝軟體（Excel）上課操作記錄

記錄一下老師上課到底教了啥操作……快速鍵前面放Windows後面放Mac的

快速導引

內文

2021/09/25

2021/10/02

  • 自訂排序 Screen Shot 2021-10-02 at 1 43 05 PM

  • 排序順序自訂清單 Screen Shot 2021-10-02 at 1 46 53 PM

  • 多重小計：先做一次「資料」-「小計」，之後再做一次小計時取消選擇「取代目前小計」即可 Screen Shot 2021-10-02 at 2 20 05 PM

  • 表格

    1. 「插入」-「表格」-Excel會自動帶入範圍，按下確定（有標題的表格如果不選，Excel會自己加標題）。點完之後，標題會出現下拉式選單可以做排序跟調整，也會出現「表格工具」功能。
      Screen Shot 2021-10-02 at 2 30 17 PM

    2. 點出「合計列」會在最後一列加入最後一行的加總，可以用下拉式選單調整內容
      Screen Shot 2021-10-02 at 2 38 14 PM

    3. 合計格子右下角黑點滑鼠移過去變黑色加號後按住拉動可以增加其他合計
      Screen Shot 2021-10-02 at 2 43 11 PM

    4. 在最後一列的儲存格按Tab可以新增新列

    5. 在最後一欄的儲存格拉儲存格右下角的黑點可以新增新欄

    6. 在表格中的欄位使用函數，會自動套用到整行 Screen Shot 2021-10-02 at 2 56 25 PM

    7. 「常用」-「設定格式化」功能可以在選取的表格中新增不同的效果，並可透過「醒目提示儲存格規則」和「頂端/底端項目規則」進行調整
      1. 資料橫條（進度條） Screen Shot 2021-10-02 at 3 18 50 PM 2. 色階 Screen Shot 2021-10-02 at 3 19 07 PM 3. 圖示集 Screen Shot 2021-10-02 at 3 20 38 PM

2021/10/09

  • 篩選
    Screen Shot 2021-10-09 at 1 17 00 PM

    1. 數字篩選
    Screen Shot 2021-10-09 at 1 18 34 PM Screen Shot 2021-10-09 at 1 18 20 PM 2. 依色彩篩選 Screen Shot 2021-10-09 at 1 19 54 PM 3. 前幾位篩選 Screen Shot 2021-10-09 at 1 22 25 PM Screen Shot 2021-10-09 at 1 22 32 PM 4. 介於篩選 Screen Shot 2021-10-09 at 1 23 10 PM Screen Shot 2021-10-09 at 1 23 14 PM

    Q: 金額小於600大於10000要怎麼按?
    A: Screen Shot 2021-10-09 at 1 33 46 PM

    SELECT * FROM table WHERE 金額 > 10000 AND 金額 < 600; # 且
SELECT * FROM table WHERE 金額 > 10000 OR 金額 < 600; # 或
    1. 日期篩選
    Screen Shot 2021-10-09 at 1 34 29 PM 6. 局部篩選（後1/3）：先選要篩的那排，點那一列的資料，再按篩選 Screen Shot 2021-10-09 at 1 41 31 PM 7. 進階篩選：先做好一般篩選，按進階 Screen Shot 2021-10-09 at 1 48 28 PM 8. 交叉篩選：插入->表格->交叉分析篩選器（Excel 2010有Bug用不了） Screen Shot 2021-10-09 at 2 12 00 PM Screen Shot 2021-10-09 at 2 12 08 PM

  • 資料驗證

    1. 下拉式選單：拉要做下拉選單的格子->資料->資料驗證
    Screen Shot 2021-10-09 at 2 18 19 PM Screen Shot 2021-10-09 at 2 18 58 PM 2. 輸入提示與錯誤警告 Screen Shot 2021-10-09 at 2 26 26 PM Screen Shot 2021-10-09 at 2 26 32 PM Screen Shot 2021-10-09 at 2 26 36 PM Screen Shot 2021-10-09 at 2 26 43 PM Screen Shot 2021-10-09 at 2 26 50 PM 3. 日期（操作跟上面一樣，但條件不同） Screen Shot 2021-10-09 at 2 30 32 PM

  • 資料剖析：先插入幾個欄位才不會被蓋掉->資料->資料剖析，寬度不固定的時候，可以先用尋找取代的功能插入符號，再用分隔符號的方式操作

    1. 固定寬度
    Screen Shot 2021-10-09 at 2 41 59 PM Screen Shot 2021-10-09 at 2 42 05 PM Screen Shot 2021-10-09 at 2 45 51 PM Screen Shot 2021-10-09 at 2 46 56 PM 2. 分隔符號（如果自訂符號，記得先把符號複製起來再操作） Screen Shot 2021-10-09 at 2 49 29 PM Screen Shot 2021-10-09 at 2 49 57 PM Screen Shot 2021-10-09 at 2 50 08 PM Screen Shot 2021-10-09 at 2 50 14 PM （如果想轉成直的，可以做完之後複製再轉置貼上） 3. 連續分隔符號視為單一處理 Screen Shot 2021-10-09 at 2 54 13 PM Screen Shot 2021-10-09 at 2 57 24 PM 4. 擠在同一個儲存格的多行資料 換行符號：Ctrl + J或者用CHAR(10)做替代

    =SUBSTITUTE(目標格, CHAR(10))

    Screen Shot 2021-10-09 at 3 14 32 PM Screen Shot 2021-10-09 at 3 14 45 PM

2021/10/16

  • 常用函數

    1. 資料來源基礎概念：假設要複製A1到C3
    • 相對位置：A1 -> C3
    • 絕對位置：$A$1 -> $A$1
    • 混合位置：A$1 -> C$1（相對欄絕對列）$A1 -> $A3（絕對欄相對列）
    1. 函數可以用儲存格右下角的點點拖曳或雙擊，直接寫整排同樣的
    Screen Shot 2021-10-16 at 1 33 00 PM
    1. 如果函數中沒有使用絕對位置，在拖拽的時候會參照錯誤，這時可以改用絕對位置
    Screen Shot 2021-10-16 at 1 34 43 PM Screen Shot 2021-10-16 at 1 36 32 PM
    1. 一些必知的函數
    2. SUM（加總函數）：可以直接用自動加總功能，也可以多選範圍做不連續的加總
    Screen Shot 2021-10-16 at 1 44 47 PM Screen Shot 2021-10-16 at 1 46 04 PM Screen Shot 2021-10-16 at 1 47 03 PM
    1. SUMIF（有條件的加總）：資料範圍之外，還需要多填選擇條件
    Screen Shot 2021-10-16 at 2 01 32 PM Screen Shot 2021-10-16 at 2 01 38 PM
    1. COUNT（計數）：反正就是SQL的COUNT()，算個數
    Screen Shot 2021-10-16 at 2 15 21 PM Screen Shot 2021-10-16 at 2 15 47 PM Screen Shot 2021-10-16 at 2 15 47 PM
    1. COUNTIF（有條件的計數）：加入條件
    Screen Shot 2021-10-16 at 2 17 24 PM Screen Shot 2021-10-16 at 2 17 52 PM Screen Shot 2021-10-16 at 2 17 57 PM
    1. COUNTA（文字個數計數）：上面COUNT只能計算數字，這個可以跟文字混
    Screen Shot 2021-10-16 at 2 21 29 PM Screen Shot 2021-10-16 at 2 21 31 PM

    1. AVERAGE（平均值）、MAX（最大值）、MIX（最小值）

    2. ROUND（四捨五入到指定位置）：也可以用「常用」裡面的位數增加減少來做

    Screen Shot 2021-10-16 at 2 28 17 PM Screen Shot 2021-10-16 at 2 28 22 PM

    1. ROUNDUP（無條件進位）：跟上面操作一樣

    2. IF：讓符合條件的計算產生A結果，否則產生B結果

    Screen Shot 2021-10-16 at 2 33 07 PM Screen Shot 2021-10-16 at 2 34 01 PM Screen Shot 2021-10-16 at 2 34 18 PM

    當然也可以在這裡做計算 Screen Shot 2021-10-16 at 2 37 04 PM Screen Shot 2021-10-16 at 2 37 14 PM

    文字轉數字 Screen Shot 2021-10-16 at 2 43 55 PM Screen Shot 2021-10-16 at 2 44 01 PM

    1. EDATE（以月計算日期）：如果秀出來是亂碼，記得把格子格式改成日期
    Screen Shot 2021-10-16 at 2 47 09 PM Screen Shot 2021-10-16 at 2 47 13 PM
    1. DAYS（計算多少天）：2013之後才有
    Screen Shot 2021-10-16 at 2 52 53 PM
    1. LOOKUP（查找）：如果要下拉，要用絕對位置去填lookup vector跟result vector（可以用F4去做不用自己加$），還有lookup vector要填區間最小值
    Screen Shot 2021-10-16 at 3 32 54 PM Screen Shot 2021-10-16 at 3 33 00 PM Screen Shot 2021-10-16 at 3 33 30 PM Screen Shot 2021-10-16 at 3 33 35 PM Screen Shot 2021-10-16 at 3 37 01 PM
    1. VLOOKUP（自動查找）：查閱值必須對應到查詢範圍最左邊的第一個欄位，然後第四個引數的值只接受0跟1（是否要完全符合）
    Screen Shot 2021-10-16 at 3 46 01 PM Screen Shot 2021-10-16 at 3 48 02 PM Screen Shot 2021-10-16 at 3 48 06 PM
    1. 在函數中使用巢狀函數
    Screen Shot 2021-10-16 at 3 57 37 PM

2021/10/23

  • 樞紐分析表

    1. 建立樞紐分析表
    Screen Shot 2021-10-23 at 1 45 17 PM Screen Shot 2021-10-23 at 1 45 28 PM Screen Shot 2021-10-23 at 1 51 21 PM
    1. 樞紐分析表選項
    Screen Shot 2021-10-23 at 2 40 50 PM

    剩下都在介紹樞紐分析表的功能，我就不收錄了