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：#.0000eg：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.002. 如果設定兩個區段的代碼，則第一組會套用到正值和負值，第二組會套用到零值 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 （精確到秒）

F9手動更新 =now() 的時間※ 日期單元格更改為常規後，日期跳轉為數字，表示自1900/1/1以來的第幾天1900/1/1為1，1900/1/2為2 以此類推
※ 時間單元格更改為常規後，時間跳轉為數字，以午夜00:00為0，24:00為1，具體時間以0-1之間的小數表示 eg：

※ 故，上下班時間可以相減得到時間差，但會忽略日期差得到錯誤的結果調整後：① =D3-D2   ② [h]:mm   可得到包含日期差的時間差調整時間差為分鐘：[m]入職至今多少天：年資（日）① =TODAY()-H3  ② 格式調整為常規   （H3：入職日）Datedif：DATEDIF(開始日期,結束日期,計算單位)eg：年資（年） =DATEDIF(H3,TODAY(),"y")年資（月） =DATEDIF(H3,TODAY(),"m")忽略日、年 =DATEDIF(H3,TODAY(),"ym")

Networkdays：NETWORKDAYS(開始日期,結束日期,假日)可以自動排除週末和指定假日=NETWORKDAYS(M3,N3,Q3:Q4)M3：工作起始日，N3：工作結束日，Q3:Q4：公司休假
NETWORKDAYS.INTL：非週末休假NETWORKDAYS.INTL(開始日期,結束日期,自訂週末,假日)=NETWORKDAYS.INTL(M4,N4,2,Q3:Q4)

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為公司年會活動抽獎、班級分組？來試試看Excel的隨機函數吧

P29 養成不拖延的好習慣、從做一個美美的進度追蹤表開始吧！

P30 如何製作一個讓老闆眼睛為之一亮的甘特圖呢？

P31 這個函數的功能強到像瑞士軍刀？來自Excel老司機們的真心推薦

P32 從此讓寫公式變得超級簡單

P33 來自2/8法則的神奇圖表，讓自己+公司的績效事半功倍！

P34 VLOOKUP功成身退！全新函數XLOOKUP霸氣取而代之！

P35 Excel怪盜現身！這些網頁資料我全部都要了

P36 Excel問卷也要玩一條龍服務？不要等待英雄，因為你就是那條龍

P37 這是什麼巫術？我弄半天的表格，結果旁邊同事彈指一揮間就全部做好了！