Excel 筆記
P1 Excel入門
P2 表格設計 & 自動加總函數
P3 凍結表格欄的應用 & 分割視窗
P4 資料排序
P5 資料篩選
P6 格式化為表格 & 交叉分析篩選表
P7 設定格式化表格的條件
P8 工作表設定 & 多個工作表合併計算
P9 圖表製作
P10 圖表製作
P11 數據透視錶應用
P12 打印分頁設定
P13 頁首、頁尾設計 & 表格加水印
P14 函數簡介
求和 =sum(C3:C6)
平均 =avg(C3:C6)
最大 =max(C3:C6)
最小 =min(C3:C6)
第二大 =large(C3:C6,2)
第二小 =small(C3:C6,2)
資料範圍array：C3:C6
順位k：2
P15 邏輯函數 IF
條件（成立，不成立）
if函數：
=IF(C7>=60,"及格","不及格")
條件格式 → 突出顯示單元格規則 → 等於（手動輸入條件內容）
ifs函數：2016以上版本
=IFS(C7>=90,"A",C7>=80,"B",C7<80,"C")
巢狀 if 技巧：
=IF(C7>=90,"A",IF(C7>=80,"B",IF(C7<80,"C")))
或 =IF(C7>=90,"A",IF(C7>=80,"B","C"))
=if(條件,結果A,if(條件,結果B,結果C)) 最多可64層嵌套
P16 VLOOKUP函數 & 絕對參照設定
=VLOOKUP(C3,E2:J12,2,FALSE)
=VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup)
=VLOOKUP(關鍵字,資料範圍,欄數,查詢模式False精準對比)
※ 模糊查詢TRUE關鍵字應為遞增序列
※ 關鍵字應為資料範圍的第一列
※ 成績表和成績等第表可更改內容，但不能移動位置，可選擇隱藏內容
Excel默認相對參照
選中資料範圍，F4 → 鎖定參照為絕對參照
=VLOOKUP(J3,$M$2:$N$7,2,TRUE)
=VLOOKUP($C$3,$E$2:$J$12,2,FALSE)
P17 IFERROR函數 & 資料驗證
=IFERROR(VLOOKUP($C$3,$E$2:$K$12,2,FALSE),"查無此人")
=IFERROR(VLOOKUP($C$3,$E$2:$K$12,6,FALSE),"")
學號欄是否為空白{ 是 → 姓名欄保持空白@ @，否 → 執行學號查詢 }
=IF(C3="","",IFERROR(VLOOKUP($C$3,$E$2:$K$12,2,FALSE),"查無此人"))
如果C3欄位為空白，該欄位跟著保持空白，若為否，則執行後邊的查詢公式
驗證：
數據 → 數據有效性 → 設置（設置輸入數據範圍：允許整數，介於101-110）
輸入信息：（輸入信息：請輸入學生學號）
出錯警告（錯誤信息：不要來亂好嗎？）
P18 COUNTIFS & SUMIFS函數應用
計數：
=COUNT(D2:D14)
※ count() 函數會直接忽略中文、英文的儲存格，改用COUNTA函數
=COUNTIF(C2:C14,"手機")
=COUNTIF(資料範圍,條件)
=COUNTIF(C2:C14,H9)
※ 文字類型資料用雙引號括起，以防出錯（條件可放在儲存格中）
=COUNTIFS(B2:B14,F2,C2:C14,G2)
=COUNTIFS(範圍1,條件1,範圍2,條件2)
=COUNTIFS(C2:C14,G2,D2:D14,">1000") 數據大小判斷
=COUNTIFS(C2:C14,G2,D2:D14,">"&H2)
=COUNTIFS(C2:C14,G2,A2:A14,">=2018/6/1",A2:A14,"<=2018/6/30")
=COUNTIFS(C2:C14,H2,D2:D14,H3) 某月份的刷卡次數
=SUMIFS(E2:E14,C2:C14,H2,D2:D14,H3) 某月消費金額加總
=SUMIFS(加總範圍,範圍1,條件1,範圍2,條件2)
COUNT() 計數
COUNTA() 計數‘非空白儲存格’ & 中英文字符
COUNTIF() 計算‘符合條件’的儲存格數目
COUNTIFS() 多個條件
=MONTH() 截取日期月份
P19 定義名稱 & INDIRECT函數 & 下拉選單設定
儲存格的定義名稱：
選中數據 → 公式 → 定義名稱
選中數據 → 左上角欄位輸入想要定義的名字
=SUM(D3:D7)
=SUM(金額)
=COUNTIF(金額,">3000")
=COUNTIF(資料範圍,判斷條件)
公式 → 名稱管理器 對名稱進行處理
INFIRECT函數：間接
=INDIRECT(D3)
=INDIRECT(D7&D8)
下拉選單：
數據 → 數據有效性 → 設置（允許：序列/清單，來源：=$F$3:$F$4）
選中表格 → 公式 → 根據所選內容創建 → 勾選最左欄 → 確定
選中餐點名稱 → 數據 → 數據有效性 → 設置
（允許：序列，來源：=indirect($B$3)）
P20 讓你事半功倍的12個小技巧
1、數據剖析
選中 → 數據 → 分列 → 選中分隔符號 → 分隔分號：空格 → 完成
分離姓名：選中數據 → 數據 → 分列 → 固定寬度 → 拖動分割線到姓名之間
2、快速選取資料
Ctrl + ↑→↓← 跳轉至表格角落
Ctrl + Shift + ↑→↓← 選取範圍
3、插入多個空白列
右鍵 → 插入 → F4（重複上一功能）
選取多行 → 右鍵 → 插入（選中幾行則插入幾行）
4、儲存格選取框
選中 → 拖移邊框 → 實現剪切粘貼功能
選中 → 按住Ctrl鍵 → 拖移邊框 → 實現複製粘貼功能
選中 → 按住Shift鍵 → 拖移邊框 → 實現剪切插入功能，不會發生覆蓋情況
5、移除重複資料
數據 → 刪除重複項 → 取消全選 → 勾選姓名
6、表格轉置
選中複製 → 右鍵 → 選擇性粘貼 → 勾選轉置
7、貼上運算值
儲存格輸入5 → 複製儲存格 → 選中英語成績 → 選擇性粘貼 → 勾選加
8、顯示公式
選中總平均 → 公式 → 顯示公式
9、目標搜尋
空白儲存格 → 數據 → 模擬分析 → 單變量求解 →
目標單元格：總平均，目標值：60，可變單元格：空白格
10、表格對角線
①邊框 → 繪圖邊框
②右鍵 → 設置單元格格式 → 邊框 → 對角線類型
11、儲存格內換行
Alt+Enter
12、插入圖片註解
文字註解：右鍵 → 插入批註
圖片註解：右鍵 → 插入批註 → 刪除框內文本 → 選中批註框 → 右鍵 →
設置批註格式 → 色彩和線條標籤 → 顏色下拉 → 填充效果 →
圖片 → 選擇圖片 → 調整圖片大小
P21 表格數值格式
輸入分數：鍵入0 1/2（0+空格+分數）
零值開頭：'0026 或 '1/2（單引號+內容）
預先套用文本格式（開始 → 數字）
自訂數值格式："$"#,##0_);[紅色]("$"#,##0)
選中 → 開始 → 數字（Ctrl+1）→ 自定義 → 類型 → #.#
# 井字號：代表一個位數的預留位置 eg：#.# （儲存格不會顯示多餘的零）
? 問號：無意義的零以空格顯示，小數點將會對齊 eg：#.?
0 零：會強制顯示每一個指定的位數 eg：#.0000
eg：0000-000000，電弧號碼顯示為：0941-618745
五位數的員工ID：選中 → 00000 或 "T"00000 或 00000"公分"
@ at符號：代表文字預留位置：選中等第框 → @等
* 星號：目錄內容填充空白：選中 → @*.
薪資欄：選中 → $0 （顯示為$33000）
選中 → $* 0 （顯示為$ 33000,*和0之間有空格）
, 千分位：0, =千 0,, =百萬 eg：$* 0,"K"
復合結構：0.00;(0.00);0.00;@ （正值;負值;零值;文字）
_ 底線：利用其接續符號的寬度來增加留白
0.00_);(0.00);0.00_);@：代表在正值和零值的右側增加一個右括號的寬度
使小數點對齊
[蓝色]0.00_);[红色](0.00);[黄色]0.00_);@ （正值藍色，負值紅色，零值黃色）
總共八種顏色：[紅色][黑色][黃色][綠色][白色][藍色][青色][洋紅]
Excel自帶調色盤56種顏色 eg：[颜色43]0.00_);[颜色5](0.00);[颜色26]0.00_);@
※ 代碼要寫全
1. 如果只填寫一個區段的代碼，則這個區段的代碼會套用到正值以外的區段 eg：[蓝色]0.00
2. 如果設定兩個區段的代碼，則第一組會套用到正值和負值，第二組會套用到零值 eg：[蓝色]0.00_);[红色](0.00)
3. 如果一個區段內沒有輸入 任何內容的話，則該區段的數值就會被隱藏 eg：[蓝色]0.00_);[红色](0.00);;@
eg：;;; （隱藏儲存格內的全部內容）
4. 可以另外設定條件式的語法
eg：[>90][绿色]0.00;[>80][蓝色](0.00);0.00
大於90綠色，89-90藍色，其餘為預設的黑色
5. 選中電話號碼 → 設置單元格格式 → 特殊 → 區域設置：中文(台灣) → 一般電話號碼(8位數) → 自定義 → [<=99999999]####-####;(0#) ####-####
如果：電話號碼≤99999999，系統判斷不包含區碼，套用####-####格式
反之，套用(0#) ####-####，包含區碼的格式
6. 系統設定格式，最多可設定兩個格式，如果要套用三個以上條件，或是要符合條件時，儲存格填充或添加外框，使用設定格式化的條件（條件格式）更為理想）
Excel調色盤
P22 時間格式 & 工齡與工時計算
二十四時製轉十二時製：框選時間 → 自定義單元格格式 →
hh:mm AM/PM 或 上午/下午 hh:mm（h代表小時，m代表分鐘）
年份日期：yyyy/mm/dd（四位數的年，兩位數的月和日）
yyyy"年"mm"月"dd"日"
ge"年"mm"月"dd"日"（台灣版本顯示為民國X年X月X日）
[DBNUM1]m"月"d"日"（中文日期：七月二日）
[DBNUM1]m"月"d"日"aaa（七月二日六）
[DBNUM1]m"月"d"日"(aaaa)（七月二日星期六）
yyyy/m/d h:mm:ss （精確到秒）
| 組合 | 功能 | 代码 |
|---|---|---|
| Ctrl+； | 鍵入今天日期 | =TODAY() |
| Ctrl+Shift+； | 鍵入目前時間 | |
| Ctrl+；空格 再按Ctrl+Shift+ | 鍵入今天日期和當前時間 | =NOW() |
F9手動更新 =now() 的時間
※ 日期單元格更改為常規後，日期跳轉為數字，表示自1900/1/1以來的第幾天
1900/1/1為1，1900/1/2為2 以此類推
※ 時間單元格更改為常規後，時間跳轉為數字，以午夜00:00為0，24:00為1，
具體時間以0-1之間的小數表示 eg：
| 00:00 | 06:00 | 12:00 | 18:00 | 24:00 |
|---|---|---|---|---|
| 1 | 0.25 | 0.5 | 0.75 | 1 |
※ 故，上下班時間可以相減得到時間差，但會忽略日期差得到錯誤的結果
調整後：① =D3-D2 ② [h]:mm 可得到包含日期差的時間差
調整時間差為分鐘：[m]
入職至今多少天：年資（日）① =TODAY()-H3 ② 格式調整為常規 （H3：入職日）
Datedif：DATEDIF(開始日期,結束日期,計算單位)
eg：年資（年） =DATEDIF(H3,TODAY(),"y")
年資（月） =DATEDIF(H3,TODAY(),"m")
忽略日、年 =DATEDIF(H3,TODAY(),"ym")
| DATEDIF單位類型 | 說明 |
|---|---|
| Y | 計算兩日之間的年數 |
| M | 計算兩日之間的月數 |
| D | 計算兩日之間的天數 |
| MD | 計算兩日之間的天數，忽略月、年 |
| YM | 計算兩日之間的月數，忽略日、年 |
| YD | 計算兩日之間的天數，忽略年數 |
Networkdays：NETWORKDAYS(開始日期,結束日期,假日)
可以自動排除週末和指定假日
=NETWORKDAYS(M3,N3,Q3:Q4)
M3：工作起始日，N3：工作結束日，Q3:Q4：公司休假
NETWORKDAYS.INTL：非週末休假
NETWORKDAYS.INTL(開始日期,結束日期,自訂週末,假日)
=NETWORKDAYS.INTL(M4,N4,2,Q3:Q4)
| 自定週末代碼 | 週末天數 |
|---|---|
| 1或省略 | 星期六、星期日 |
| 2 | 星期日、星期一 |
| 3 | 星期一、星期二 |
| 4 | 星期二、星期三 |
| 5 | 星期三、星期四 |
| 6 | 星期四、星期五 |
| 7 | 星期五、星期六 |
| 11 | 僅星期日 |
| 12 | 僅星期一 |
| 13 | 僅星期二 |
| 14 | 僅星期三 |
| 15 | 僅星期四 |
| 16 | 僅星期五 |
| 17 | 僅星期六 |
P23 如何計算成績排名
RANK.EQ：不打亂學號，按原有學生信息在新列添加排名
RANK.EQ(主體,比較範圍)
=RANK.EQ(D3,$D$3:$D$10)
針對重複排名：RANK.AVG
=RANK.AVG(D3,$D$3:$D$10)
排序方式：RANK.EQ(主體,比較範圍,排序方式)
默認為0：遞減排序，數字越大，排名越靠前
P24 如何用LEFT函數提取表格中的文字資料
P25 最強函數搭檔 INDEX & MATCH-VLOOKUP
P26 保護表格必學四招 讓陌生人無法更改你的重要文件
P27 重複內容
P28 用Excel為公司年會活動抽獎、班級分配
P29 養成不拖延的好習慣、從做一個美美的進度
P30 如何製作一個優秀的甘特圖
P31 这个函数的功能抢到像万能瑞士军刀
P32 从此让写公式变得超级简单
P33 来自2/8法则的神奇图表，让自己+公司的
P34 VLOOKUP功成身退，全新函数XLOOKUP
P35 Excel怪盗现身，这些网页资料我全部都要了
P36 Excel问卷也要玩一条龙服务？不要等待英雄，
P37 这是什么巫术？我弄半天表格，结果旁边的同事