Excel 的練習及應用                                          2023/09/26 (c)謝碧景編製更新

課程單元   請點選 1.Excel操作環境簡介 2.編輯Excel工作表及存檔 3.功能及函數的應用 4.文字檔與Excel檔的轉換 5.樞紐分析表及圖報表 6.超連結 的應用 7.統計圖表的建立 8.行政進階研習

Excel操作環境簡介

編輯Excel工作表及存檔

功能及函數的應用 練習範例

輸入公式【=】：四則運算+-*/、複製公式、儲存格格式化、填數列資料 -->ex1.xls

數學函數：sum加總函數、ROUND四捨五入 -->ex1.xls、MOD求餘數、INT取整數 -->ex2.xls

SUM(範圍)：求範圍內資料的總和，例 =SUM(B1:B10)

ROUND(數值,小數位數)：將數值四捨五入到指定的小數位數，例 =ROUND(123.567,1)  【結果123.6】小數點下一位

INT(數值)：取一數字或數值運算結果的整數部分，其小數部份無條件捨去，例 =INT(8/3)【結果2】 =INT(7.5)【結果7】

MOD(被除數,除數)：求兩數相除後之餘數，且兩數均為實數，例 =MOD(17,3)【結果2】

＊填數列資料：編輯→填滿→數列…

＊設定格式化條件：格式→設定格式化條件…

統計函數：AVERAGE平均、MAX、MIN、FREQUENCY次數分配、RANK、COUNT、COUNTIF -->ex2.xls

AVERAGE((範圍)：求範圍內資料的平均值，例 =AVERAGE(B1:B10)

MAX(範圍)：求範圍內數值資料最大值，例 =MAX(B1:B10)

MIN(範圍)：求範圍內數值資料最小值，例 =MIN(B1:B10)

RANK(數值,範圍,順位方式)：決定數值在範圍內的順位。順位方式：0為由大至小(可省略)，1為由小至大，例 =RANK(B4,B$4:B$14)

COUNT(範圍)：為一個計數函數，求範圍中含“數值”資料的儲存格個數，例 =COUNT(C4:C13)

COUNTIF(範圍,條件)：計算範圍內符合某條件的儲存格個數，例 =COUNTIF(C4:G4,"<60")

FREQUENCY(範圍,基準)：計算範圍內的值依據某基準出現的次數，並傳回一個垂直數值陣列，
   【Ctrl+Shift+Enter】合按會產生陣列{} ，例 {=FREQUENCY(D$4:D$14,M$3:M$10)} 

＊資料排序：資料→排序→是否有標題、設定主要鍵、次要件…

＊新增工作表：插入→工作表

邏輯、日期、文字函數：IF、AND、OR、MONTH、LEFT、REPT -->資料轉置ex3.xls

IF(條件,成立,不成立)：根據條件之真假，傳回不同的結果，例 =IF(B2>=80,"甲","乙")

AND(條件1,條件2,...)：所有條件都是TRUE，則傳回TRUE，例 =AND(A5="F",B5>=80)

OR(條件1,條件2,...)：有任何一個條件是TRUE，則傳回TRUE，例 =OR(A5<0,A5>100)

MONTH(時間數值)：傳回時間數值之月份(1~12)，例 =MONTH(2003/02/28)【結果2】

LEFT(字串,n)：由字串左邊開始取n個字元或字元組，例 =IF(LEFT(B4,1)="陳","yes","no")

REPT(字串,n)：重複字串n次，例 =REPT("*",10)

DATEDIF(開始日期,結束日期,單位) -->求年資.xls

＊資料的篩選：自動篩選、進階篩選 -->ex3.xls

＊資料轉置：複製→選擇性貼上…

檢視與參照函數：HLOOKUP水平查詢、VLOOKUP垂直查詢 -->ex4.xls

VLOOKUP(查詢值,查詢範圍,欄位置)：由查詢範圍最左側的欄位中尋找出與查詢值相同者，則取出相同資料所在列中「欄位置」指定之儲存格資料。查詢範圍最左側視為第1欄，例 =VLOOKUP(C3,$H$3:$I$17,2)

HLOOKUP(查詢值,查詢範圍,列位置)：由查詢範圍最上面一列開始尋找出與查詢值相同者，則取出相同資料所在欄中「列位置」指定之儲存格資料。查詢範圍最上方一列視為第1列，例 =HLOOKUP(C3,$A$10:$HI$15,2)

INDEX(資料庫,列位置,欄位置)：由資料庫中，輸出列及欄位置交會之儲存格資料，例 =INDEX(C3:M13,8,3)

資料庫函數(資料庫 表格,欄名或第幾欄,準則範圍)：DSUM計算符合條件之總和、DCOUNT計算符合條件之個數

DSUM(資料庫,欄位置,條件)：求資料庫指定範圍內符合某條件之資料的總和，例 =DSUM($A$2:$H$9,8,M1:M2)

DCOUNT(資料庫,欄位置,條件)：求資料庫指定範圍內符合某條件之資料的儲存格個數，例 =DCOUNT(A2:F12,1,A16:F18)

DAVERAGE(資料庫,欄位置,條件)：求資料庫指定範圍內符合某條件之資料的平均值，例 =DAVERAGE(A2:E20,5,G1:G2)

DMAX(資料庫,欄位置,條件)：求資料庫指定範圍內符合某條件之資料的最大值，例 =DMAX(A2:E20,"業績",G1:G2)

DMIN(資料庫,欄位置,條件)：求資料庫指定範圍內符合某條件之資料的最小值，例 =DMIN(A2:E20,"業績",G1:G2)

DSTDEV(資料庫,欄位置,條件)：求資料庫指定範圍內符合某條件之資料的標準差，例 =DSTDEV(A2:E20,"業績",G1:G2)

不同檔案資料的連結 -->ex5.xls

樞紐分析表 -->ex6.xls

插入→樞紐分析表→列、欄

＊比較：資料的篩選、SUBTOTAL函數、FREQUENCY函數

文字檔與Excel檔的轉換 -->ex5-5.xls

txt 轉 xls：檔案→開啟舊檔(.txt文字檔)→匯入字串精靈(進行資料剖析)…

xls 轉 txt：檔案→另存新檔→檔案類型：文字檔(Tab字元分隔)(*.txt)…

超連結 的應用：可連結至檔案、Web網址或電子郵件位址 -->ex7.xls

轉存網頁：檔案→另存成Web畫面→檔案類型：Web畫面(*.htm;*.html】…

超連結名稱：定義名稱：插入→名稱→定義。連結：插入→超連結→這份文件中位置→已定義之名稱

超連結URL：插入→超連結→現存的檔案或Web網頁

統計圖表的建立-->ex-gr.xls

資料 ／運算列表：資料→運算列表→欄、列變數儲存格的應用(進階練習：相同公式的再應用)

PMT(利率,期數,貸款額)：計算貸款每期所要攤還的金額，例 =PMT(A4/12,B3*12,-C1)

行政進階研習 下載練習範例