立即上手-雲端應用

• 預設 開啟Excel就直接開啟「空白活頁簿」：檔案\選項\一般\[ ]這個應用程式啟動時顯示開始畫面(取消勾選)。

• 新增 空白活頁簿︰Ctrl+N

• 由 範本 新增新活頁簿：檔案\新增→關鍵字 搜尋\建立。

• Office網站 範本 [Link]

• [儲存檔案 Ctrl+S]與[另存新檔 F12]的差別。

• [雲端硬碟OneDrive]儲存空間，須先申請[Link]。若只使用免費版本，可有5GB的空間。若想要更大的空間可付費租用。

  • 若欲使用OneDrive須先登入微軟MicroSoft帳號。

• 預設 檔案儲存位置︰檔案\選項→儲存\預設本機檔案位置。

• 開啟/關閉 BackStage︰檔案\選項→儲存\[ ]開啟或儲存檔案時不顯示Backstage

• 關閉檔案：Ctrl+W 或 Alt+F4 或 檔案\關閉

• 開啟舊檔：Ctrl+O、檔案\開啟舊檔。

  • 指定開啟舊檔方式：

    • 開啟：一般常用模式。

    • 開啟為唯讀檔案：只能檢視，若要儲存變更後的檔案，須另存新檔。

    • 開啟複本：複製原檔案，並以「複本(編號)原檔名」為檔名開啟。

    • 以瀏覽器開啟：若開啟的檔案類型是瀏覽器支援的檔案，則可利用此功能將檔案於瀏覽器開啟。

    • 以受保護的檢視開啟：以唯獨模式開啟檔案，若要編輯檔案，可解除鎖定保護狀態。

    • 開啟並修復：對檔案進行偵測並修復錯誤。

• 設定最近使用活頁簿的歷史記錄數目：檔案\進階\顯示\顯示最近使用的活頁簿的數目

Excel基礎

• 活頁簿(試算表、檔案) > 工作表 > 列、欄 > 儲存格

• 設定新活頁簿的原始字型：檔案\選項→一般\建立新的活頁簿時:以此作為預設字型

• 、檢視模式、工作表份數：檔案\選項\一般\建立新活頁簿時

• SUBTOTAL(參數,範圍)函數：自動排除運算隱藏或被篩選的數值 [Link], [Link2]

• 儲存格位址：

  • 可使用「名稱方塊」輸入位址測試。

  • 單儲存格(A1)。

  • 連續儲存格(A1:D5)：請給區域範圍的對角線儲存格位址，中間以「：」號隔開。

  • 不連續儲存格(A1, D5)：將不連續儲存格位址，中間以「,」號隔開。

  • 參照儲存格位址：相對儲存格位址、絕對儲存格位址(以$符號綁定位址)。參照位址發生於設定「公式」編輯於「公式填滿、複製、移動」時，會產生不同的影響。

  • 參照儲存格位址可利用「F4」，快速切換該參照位址的相對/絕對儲存格格式樣式。

• 公式

  • 公式編輯一律須以「=」號開始。

  • 公式中所使用的函數，其括號必定「成對」出現。

  • 公式可「巢狀」使用。

• 數值資料格式。

• 資料錯誤訊息：

  • ######：儲存格寬度不夠資料顯示。

  • #VALUE：錯誤的資料格式，公式無法計算

  • #DIV/0!：除數=0

  • #NAME!：公式名稱中有拼字錯誤 

  • #N/A：公式找不到要求尋找的項目 

  • #REF!：公式參照不是有效的儲存格

  • #NUM!：公式或函數中有無效的數值 

• 函數的特色

  • 函數的括號一定「成對」，且一定以「洋蔥式」成對出現，一層包含著一層。

  • 多重式函數一定以「洋蔥式」方式編寫。

• 小數點取捨函數

  • ROUNDUP(數值,小數點位數)︰小數位數 無條件進入法

  • ROUNDDOWN(數值,小數點位數)︰小數位數 無條件捨去法

  • ROUND(數值,小數點位數)︰小數位數 四捨五入法

• IF函數

  • IF(邏輯條件,條件成立回傳值,條件不成立回傳值)

  • 邏輯條件的結果只有兩種(成立 或 不成立)

  • 邏輯︰大於(>)、小於(<)、大於等於(>=)、小於等於(<=)、不等於(<>)

  • 多邏輯條件：

    • AND(條件1,條件2,...,條件n)函數︰同時成立可使用。

    • OR(條件1,條件2,...,條件n)函數︰至少有一個成立即可。

• 202-快樂小學學生名冊

  • ROUNDUP()與IF()的使用

  • 相對位址與絕對位址

  • 自訂公式

  • AVERAGE()函數使用

  • 字型設定

  • 範圍名稱設定

  • 設定列印範圍、列印標題

格式設定與版面配置

• 字型：字型、大小、粗體、底線、填滿色彩

• 填滿色彩：右鍵\儲存格格式→填滿\背景色彩+圖樣色彩+圖樣樣式。

• 日期格式：「2019/3/28」或「2019-3-28」

• 儲存格格式：Ctrl+1

• 自訂數值格式：

  • 格式碼：

    • 「正數;複數;零;文字」

    • 「[]」顏色

    • 「"文字"」

    • 「0」一定顯示位數

    • 「#」不一定顯示位數，若0時不顯示

    • 「_」保留字元寬度，保留 控制字元「_」後的字元的空白寬度

    • 「@」原輸入儲存格的文字資料

    • 日期格式代碼 [Link]

    • 其他格式碼定義 [Link]

  • 例：正數時顯示藍色字「成長1245.56」，負數顯示紅色「衰退1245.56」。儲存格式碼為「[藍色]"成長"0.00;[紅色]"衰退"0.00」。

• 框線：線條樣式、線條位置、線條顏色、線條粗細

  • 手繪框線：常用\字型\框線\繪製框線(外框線) 或 繪製框線格線(外框線+格線)→取消 繪製 ESC

  • 手繪 清除框線：常用\字型\框線\清除框線→取消 清除 ESC

  • 儲存格格式\外框；常用\字型\框線\其他框線。

• 複製與貼上

  • 貼上格式︰常用\剪貼簿\貼上\其他貼上選項\格式設定 (貼上後，除欄寬外其他格式均已複製)。

  • 複製貼上欄寬：常用\剪貼簿\貼上\選擇性貼上→欄寬度。

  • 連續複製格式：(開啟) 雙擊 複製格式→(取消)ESC

• 自動填滿選項\僅以格式填滿

• 複製格式：常用\剪貼簿\複製格式

  • [複製格式] 左鍵1下：複製格式使用1次。

  • [複製格式] 左鍵2下：複製格式連續使用。若要停止複製格式，可按「ESC」。

• 套用 儲存格樣式：常用\樣式\儲存格樣式。

• 工作版面與列印設定

  • 紙張方向、縮放比率、起始頁碼、預覽列印

  • 版面配置\邊界設定

  • 版面配置\版面設定 群組對話方塊→邊界\邊界設定(上、下、左、右、頁首、頁尾) 和 置中方式(水平置中 垂直置中)

• 頁首與頁尾資訊

  • 頁首 與 頁尾

    • 整頁模式→滑鼠 單點 頁首方塊→頁首及頁尾工具\頁首及頁尾項目\檔案名稱

  • 自訂頁首與頁尾資訊

    • 版面配置\版面設定 全功能視窗→頁首與頁尾

  • 每頁重覆 標題

    • 版面配置\版面設定\列印標題

• 數值類別

  • 貨幣：千分位符號、小數點2位、貨幣符號(緊鄰)、負數表示

  • 會計：千分位符號、小數點2位、貨幣符號、負數表示；小數點與貨幣符號會自動對齊

  • 百分比

  • 科學記號

  • 時間

  • 文字

  • 自訂數值格式：[Link]

    • m放在ss前面，或放在h(或hh)後面，會當成「分鐘」，否則會當成「月份」。

• 清除框線

  • 選取儲存格→常用\字型\無框線

  • 常用\字型\繪製框線格線+線條樣式\無框線

  • 常用\字型\清除框線

• 儲存格樣式

  • 先直接將 儲存格格式(數值、對齊方式、字型、框線、圖樣、保護) 設定好→常用\樣式\儲存格格式\新增儲存格格式

• • 合併匯算

數據整理自動化

• 文字檔資料匯入：資料\取得外部資料\從文字檔→分隔符號+我的資料有標題→內容：每隔數分鐘更新一次

• 新增連線並格式化為[表格]：點選空白資料儲存格→資料\取得外部資料\現有連線→瀏覽更多...\選取檔案→表格+新工作表

• 移除重復資料：資料\資料工具\移除重複

• 資料剖析(可將單欄資料分割成多個欄的資料)：

• 移除資料連線：資料\連線\連線→選取資料表→移除

• 資料排序：資料\排序與篩選→排序\從最小到最大排序

• 自訂排序：資料\排序與篩選→新增層級

• 資料篩選：資料\篩選→▼勾選欲保留的選項

• 文字篩選：資料\篩選→▼文字篩選\包含...

• 數字篩選：資料\篩選→▼數字篩選\等於 不等於 大於...

• 資料格式化條件：

  • 儲存格-醒目提示：常用\樣式\設定格式化條件\醒目提示儲存格規則\...

統計圖表設計

• 插入\走勢圖\折線圖→資料範圍(資料必須是連續)+走勢圖位置

• 編輯具空白資料的走勢圖：走勢圖工具\走勢圖\編輯資料\[V]以線段連接資料點

• 走勢圖工具\設計\顯示：高點、低點、負點 、負點、最後點、標記

• 清除走勢圖：走勢圖工具\設計\清除\清除選取的走勢圖 或 清除選取的走勢圖群組

• 走勢圖類型：

  • 折線圖：以折線表示數據的高低變化

  • 直條圖：以直條表示數據的高低變化

  • 輸贏圖：以直條向上與向下，表示數值的正與負

• 統計圖表：

  • 建立：選取資料範圍→插入\圖表

  • 編輯資料來源：圖表工具\設計\資料\選取資料

  • 美化版面：

    • 快速配置：圖表工具\圖表版面配置\快速配置

    • 圖表樣式：圖表工具\設計\圖表樣式

  • 標籤：圖表工具\圖表版面配置\新增圖表項目\資料標籤

  • 細調項目格式：選取項目→圖表工具\格式\目前的選取範圍\格式化選取範圍

  • 水平軸交叉於：設定水平軸與垂直軸交叉處

  • 變更圖表類型：圖表工具\設計\類型\變更圖表類型

  • 切換欄與列：圖表工具\設計\資料\切換列/欄

  • 移動圖表：圖表工具\設計\移動圖表

  • 瀑布圖表：

    • 瀑布圖：可 觀察/顯示 數值正負變化對總數值的影響

    • 漏斗圖：可 觀察/顯示 逐漸減少數值的變化

• 快速分析：

  • 設定格式：儲存格格式化條件

  • 圖表：建立圖表

  • 總計：欄或列統計分析

  • 表格：格式化為表格

  • 走勢圖：建立走勢圖

資料驗證與輔助

• 一般準則驗證資料：資料\資料工具\資料驗證\資料驗證

  • 資料驗證準則：整數、實數

  • 提示訊息：V當儲存格被選取時，顯示提示訊息。

  • 錯誤提醒：當輸入的數值，不符合驗證準則，顯示的訊息。

  • 輸入法模式：設定輸入該儲存格時，使用的輸入法模式(中文、英文)

• 圈選「不符合」準則的儲存格：資料\資料工具\資料驗證\圈選錯誤資料。會依「前面設定的準則」，將「不符合」的儲存格圈選出來。

• 「清除錯誤圈選」的儲存格：資料\資料工具\資料驗證\清除錯誤圈選。

• 「自訂公式」驗證儲存格：資料\資料工具\資料驗證\資料驗證→資料驗證準則\自訂。公式前一定要打「=」。

• 「清單」驗證儲存格：資料\資料工具\資料驗證\資料驗證→資料驗證準則\清單。

  • 清單的來源：可直接選取工作表的儲存格範圍，例如：「=$A$1:$A$3」。

  • 清單的來源：可直接輸入選擇項目，例如：「項目1,項目2,項目3」。前面不需加「=」，項目間以「,」隔開。

• 建立同步資料：複製→貼上\選擇性貼上\貼上連結

• 建立超連結：選取「儲存格、文字、圖片、物件」→插入\連結\超連結→設定：顯示文字+輸入儲存格位址+選取文件中的一個位置(工作表)

• 建立「電子郵件」連結：選取目標→插入\連結\超連結\電子郵件位址

• 建立「網頁 或 檔案」連結：選取目標→插入\連結\超連結\現存的檔案或網頁

• 移除超連結：選取目標→右鍵\快顯選單

• 尋找「資料驗證」儲存格：常用\編輯\尋找與選取\特殊目標→資料驗證

-------------------------------------------------------------------------------------------------------------------

[問題] 資料驗證的預設「輸入法」設定。

• 系統輸入法設定

  • 欲使用預設輸入法設定，須先將Windows系統設定的語系須有 中文[CH]和 英文[EN]兩種以上。

  • 且 中文[CH]語系的預設輸入法，不可為「中文-美式鍵盤」。

  • 若於Excel開啟的狀態下，新增輸入法語系，須要重新啟動Excel才可以作資料驗證的輸入法設定。

• Excel-資料驗證之輸入法設定

  • 選取儲存格→資料\資料工具\資料驗證\資料驗證→輸入法模式

    • 開啟(中文模式)

    • 關閉(英文模式)

• 參考資料：[Link1], [Link2]

TQC-408-監視器報價管理系統

• 資料驗證-下拉式清單：[Link1], [Link2]

• 範圍名稱使用：F3

• 函數使用：INDIRECT(), IFERROR(), VLOOKUP(), SUMPRODUCT()

• 儲存格格式

  • 自訂儲存格格式：

    • 分為4個區段，區段以「;」區分：正;負;零;文字

    • 格式符號：

      • _：保留下一個字元的空白寬度

      • *：重複下一個字元，直到填滿儲存格

      • #：代表一個數字的位置，但如果是「0」，則不顯示

      • ,：數值的千分位符號

      • 0：代表一個數字的位置，但如果是「0」，則顯示「0」

      • @：代表儲存格輸入的文字

• 工作表標籤顏色

• 工作表群組化工作

• 欄寬設定

[問題]

使用vlookup(,,True)的情況：欲使用「True」，預設被查詢表的第一欄是「遞增排序」。而查詢結果會將，比查詢值「小」的「最大值」項，當成查詢結果。

進階資料檢視

• 新增註解、編輯註解、修改註解字型、修改註解方塊的大小和位置

• 刪除註解：校閱\註解\刪除

• 顯示/隱藏註解：校閱\註解\顯示/隱藏註解

• 顯示或隱藏 所有註解：校閱\註解\顯示所有註解

• 縮小字型以符合欄寬：儲存格格式對話視窗→對齊方式\[V]縮小字型以符合欄寬

• 顯示公式：公式\公式稽核\顯示公式

• 追蹤前導參照：顯示「目前」儲存格中公式，顯示「來源」數據。每按「一次」就可以往前追蹤一層。

• 追蹤從屬參照：顯示「目前」儲存格中公式，顯示「衍生」數據。

• 移除箭頭：移除「追蹤前導參照」與「追蹤從屬參照」的箭頭。

  • 移除「前導參照箭頭」：公式\移除箭頭\移除前導參照箭頭。每執行一次，移除一層。

• 公式錯誤檢查

  • 公式\公式稽核\錯誤檢查

  • 錯誤檢查規則 設定：檔案\選項\公式\錯誤檢查規則

  • 追蹤公式的錯誤：公式\公式稽核\錯誤檢查\追蹤錯誤

  • 評估值公式：可以檢視「公式」計算過程中的參數值，觀看「有底線」的部分，按下「評估值」可顯示計算後的值。

• 監看視窗：

  • 若要同時監看不同「工作表」，或不同的「活頁簿」的「儲存格」，可使用監看視窗。

  • 公式\監看視窗

• 尋找與取代

  • 尋找：常用\尋找與取代\尋找。或Ctrl+F

  • 取代：常用\尋找與取代\取代。或Ctrl+H

• 列印「註解」：版面配置\版面設定\版面設定全視窗→工作表\列印\註解：和工作表上顯示狀態相同

• 並排顯示：檢視\並排顯示。可在同一個螢幕上，顯示不同的活頁簿檔案。

• 分割視窗：檢視\分割視窗。將同一個活頁簿檔案，分割不同視窗觀察檔案中的不同部分。

• 凍結窗格：檢視\凍結窗格。

公式與函數應用

•  公式第一個字元「=」。

• 運算子分類有：算數運算子、比較運算子、文字關聯運算子、參照運算子

• 算數運算子：+(加)、-(減)、*(乘)、/(除)、%(百分比)、^(冪次方)

• 比較運算子：=(等於)、>(大於)、<(小於)、>=(大於等於)、<=(小於等於)、<>(不等於)

• 文字關聯運算子：&(串接文字)

• 參照運算子：

  • 冒號(:)：連續儲存格範圍，例：「A1:D10」

  • 鬥號(,)：不連續儲存格範圍，例：「A1,D10」

  • 空格( )：儲存格範圍「

  • 」，例：「A1:D10 B2:E15」

• 函數

  • TODAY()：自動取得今天日期

  • COUNTBLANK()：計算範圍中「空白」的數量

  • COUNTIF()：計算符合條件的儲存格個數

  • IF()：邏輯判斷

  • 巢式多條件IF()函數

• 評估值公式：逐步察看函數計算的結果值。公式\公式稽核\評估值公式

跨工作表公式與名稱應用

• 函數：

  • COUNTA():計算非空白的儲存格數量

  • COUNTBLANK():計算空白儲存格數量

  • SUM():計算數值的總和

  • 跨工作表儲存格位置：工作表名稱!儲存格位置

  • 串接文字：

    • CONCAT()：串接文字，適用Excel 2016。舊版Excel請使用CONCATENATE()。

    • CONCATENATE()：串接文字

  • VLOOKUP(搜尋值, 資料表, 回傳欄位鎖引, 0/1)

  • COLUMN()：回傳儲存格欄位值

  • ISERROR(數值)：當數值產生「#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME!, #NULL」錯誤，回傳「TRUE」。

• 清單輸入：資料\資料工具\設定\清單\=位址

• 定義「名稱」

• 使用「名稱」建立「公式」

• 呼叫「名稱」：F3

• 「名稱」使用是「絕對位址」，因此無法使用拖曳填滿公式。但可利用「名稱管理員」修改為非絕對位址，即可拖曳填滿公式。

• 從選取範圍建立公式

資料分組與篩選

• 小計

  • 將欲「分組」的項目進行「排序」，再進行小計

  • 資料\大綱\小計

    • 分組小計欄位

    • 使用函數：加總、項目個數、平均值、最大數、最小數、乘積

    • 新增小計位置：下方、上方 

  • 檢視大綱

  • 清除大綱：資料\大綱\取消群組\清除大綱

  • 移除「小計」：資料\大綱\小計→全部移除

• 組成群組/取消群組：[Link1], [Link2]

  • 組成群組[欄]:彙總欄預設位於「右方」

  • 組成群組[列]:彙總列預設位於「下方」

  • 若要修改 「彙總欄」和「彙總列」的預設位置，可於「資料\大綱\全功能視窗」中設定

  • 自動建立大綱：資料\大綱\組成群組\自動建立大綱。使用自動建立大綱前，需先利用公式建立「彙總欄」或「彙總列」，與其他「欄」或「列」的關係

• 進階篩選資料

  • 要素：(1)資料來源範圍，(2)篩選準則範圍，(3)篩選結果

  • 篩選來源「資料標題」需和篩選準則「資料標題」，需要「一致」

  • 準則

    • 不同「列」的準則，條件間為「或」

    • 相同「列」的準則，條件間為「且」

  • 資料\排序與篩選\進階...

• 合併彙算

  • 資料\資料工具\合併彙算

• 目標搜尋：就跟單變數函數求解相同。

  • 功能：於「目標儲存格」，給定「目標值」修訂「變數儲存格」的「值」，使目標儲存格的值符合目標值。

  • 資料\資料工具\模擬分析\目標搜尋

• 分析藍本：[Link1], [Link2]；分析不同變數值(可以多個)，對目標儲存格(結果)的影響。

  • 資料\資料工具\模擬分析\分析藍本

    • 步驟一：先將「變數」與「目標儲存格」間的「公式」，編輯完成

    • 步驟二：圈選「變數」

    • 步驟三：資料\資料工具\模擬分析\分析藍本

    • 步驟四：設定「分析藍本名稱」

    • 步驟五：輸入每一個變數儲存格的數值(Excel不會自動擷取數值，要手動輸入)

    • 步驟六：「新增」，重複「步驟四」～「步驟五」。全部輸入完後，再至步驟七。

    • 步驟七：可點選「顯示」，察看不同藍本的結果。

    • 步驟八：點選「摘要」→「目標儲存格 (結果)」，可產生結果摘要報告表。

• 運算列表

  • 單變數資料表

    • 編輯「欄標頭」的公式

    • 圈選「運算列表」的目標儲存格

    • 資料\資料工具\模擬分析\運算列表

    • 設定「欄變數」

  • 雙變數資料表

    • 編輯「目標 儲存格」的公式

    • 圈選「運算列表」的目標儲存格

    • 設定「欄變數」、「列變數」

• 規劃求解

  • 開啟增益集：檔案\選項\增益集\規劃求解增益集\執行→[V]規劃求解增益集→確定

  • 功能：針對「目標儲存格(公式)」，附加「多個條件」，求出符合條件的「變數」值

  • 資料\分析\規劃求解

  • 「設定目標式」：設定欲求解的公式→「藉由變更變數儲存格」：設定可變更的變數儲存格→「設定限制式」