Excel技巧_IT電腦知識

1、ITIT電腦知識電腦知識 - Excel高階篇 -特別感謝特別感謝湯思量主任湯思量主任友情贊助友情贊助Page 3ExcelExcel基礎知識基礎知識1ExcelExcel格式修改細節(jié)格式修改細節(jié)2ExcelExcel打印得心應手打印得心應手3ExcelExcel常用函數(shù)常用函數(shù)4目目 錄錄特別補充特別補充- -日期計算日期計算5Page 4思考：思考：我們?yōu)槭裁匆獙W習該項技能？我們?yōu)槭裁匆獙W習該項技能？Page 5ExcelExcel基礎知識基礎知識1Page 6一、打開和關閉一、打開和關閉 打開打開ExcelExcel方式：方式： 右鍵右鍵 - - 新建新建- microsoft exce

2、l- microsoft excel工作表工作表 開始開始 - - 所有程序所有程序 - Microsoft Office - Microsoft Office Excel Excel 開始開始 - - 運行運行 - - 輸入輸入excelexcel 雙擊桌面快捷方式雙擊桌面快捷方式 直接在程序安裝位置直接在程序安裝位置C C盤找到程序文件打開盤找到程序文件打開 關閉關閉ExcelExcel方式：方式： 直接點右上角的關閉按鈕直接點右上角的關閉按鈕 點點“文件文件”菜單中的菜單中的“退出退出”按鈕按鈕 在任務欄上點文件鼠標右鍵，選擇在任務欄上點文件鼠標右鍵，選擇“關閉關閉” 雙擊工作簿左上角圖

3、標雙擊工作簿左上角圖標 Alt+F4Alt+F4或或ctrl+F4ctrl+F4 Ctrl+WCtrl+W 直接切斷計算機電源關閉直接切斷計算機電源關閉還有更絕的嗎？還有更絕的嗎？_ Page 7二、工作簿和工作表二、工作簿和工作表工作簿：工作簿：【BookBook】 一個一個ExcelExcel文件就是一個工作簿，可由一個文件就是一個工作簿，可由一個或多個工作表組成，默認名字為或多個工作表組成，默認名字為“book1”book1”。工作表：工作表：【SheetSheet】 用于存儲和管理數(shù)據(jù)的主要文檔，又稱電子用于存儲和管理數(shù)據(jù)的主要文檔，又稱電子表格，默認名字為表格，默認名字為“sheet

4、1”,sheet1”,用列號（用列號（A A、B B、C)C)和行號（和行號（1 1、2 2、3)3)命名。命名。單元格單元格 是是ExcelExcel的基本元素，是整體操作的最小單的基本元素，是整體操作的最小單位，可以存放文字、數(shù)字和公式標記：用行標和位，可以存放文字、數(shù)字和公式標記：用行標和列標一起，如列標一起，如B5B5，連續(xù)的單元格區(qū)域用冒號隔開，連續(xù)的單元格區(qū)域用冒號隔開，如如A3:F8A3:F8。Page 8三、行和列三、行和列 【列列】“豎豎”者為列，者為列，excelexcel中用大寫英文表示中用大寫英文表示ExcelExcel中最大的列數(shù)為：中最大的列數(shù)為：256256列列

5、【行行】“橫橫”者為行，者為行，excelexcel中用阿拉伯數(shù)字表示中用阿拉伯數(shù)字表示ExcelExcel中最大的行數(shù)為：中最大的行數(shù)為：6553665536行行Page 9ExcelExcel格式修改細格式修改細節(jié)節(jié)2Page 10一、單元格的選定一、單元格的選定選定單獨的單元格選定單獨的單元格選定整行或整列選定整行或整列選定連續(xù)的多單元格：選定連續(xù)的多單元格：選定不連續(xù)的多單元格：選定不連續(xù)的多單元格：選定只有內容的單元格：選定只有內容的單元格：選定所有的單元格選定所有的單元格鼠標拖曳鼠標拖曳ctrlctrlshift+ctrl+shift+ctrl+箭頭箭頭Page 11二、提高輸入效

6、率二、提高輸入效率“記憶式記憶式”鍵入鍵入 單擊單擊“工具工具”菜單中的菜單中的“選項選項”命令，在命令，在“編輯編輯”選項卡中選中選項卡中選中“記憶式鍵入記憶式鍵入”快速輸入當天日期：快速輸入當天日期：快速輸入當前時間：快速輸入當前時間：當輸入數(shù)字太多時，以科學記數(shù)或以當輸入數(shù)字太多時，以科學記數(shù)或以“# #”表示，表明該列沒有足夠的寬度，需調整。表示，表明該列沒有足夠的寬度，需調整。ctrl+ctrl+；Ctrl+Shift+Ctrl+Shift+；Page 12三、合適列寬、行高三、合適列寬、行高點擊工作表左上角的“全選”按鈕，選中全部單元格然后把鼠標移到任何兩列的交界線處，待鼠標指針變

7、成帶兩個方向箭頭的豎線時，雙擊鼠標，就可以得到合適的列寬了(正好使單元格的內容得到完全顯示) 同樣，我們將鼠標移到行與行的交界線處雙擊，也可以得到合適的行高。 Page 13四、四、“有效性有效性”輸入輸入目的：目的： 限定輸入數(shù)據(jù)的有效范圍，如指定為特定數(shù)限定輸入數(shù)據(jù)的有效范圍，如指定為特定數(shù)據(jù)類型、取值范圍、限定輸入的字符數(shù)選定據(jù)類型、取值范圍、限定輸入的字符數(shù)選定需要設置有效性的單元格區(qū)域需要設置有效性的單元格區(qū)域操作方法：操作方法： 數(shù)據(jù)數(shù)據(jù) 有效性有效性 設置設置 允許允許 序列序列 在在“來源來源”處填寫選擇內容處填寫選擇內容 注意兩個選項之間必須用注意兩個選項之間必須用 英文格式

8、下的英文格式下的逗號逗號隔開隔開Page 14五、移動單元格五、移動單元格直接拖動直接拖動 移動移動CtrlCtrl拖動拖動 復制復制ShiftShift拖動拖動 插入插入Page 15六、設置條件格式六、設置條件格式用途：設定單元格在符合某條件時的顯示用途：設定單元格在符合某條件時的顯示方法：方法：Page 16七、設置單元格保護七、設置單元格保護用途：設定保護可防止對單元格進行移動、用途：設定保護可防止對單元格進行移動、修改、刪除及隱藏，還可隱藏公式。修改、刪除及隱藏，還可隱藏公式。方法：只有工作表被保護，鎖定單元格或方法：只有工作表被保護，鎖定單元格或隱藏公式才有效隱藏公式才有效Page

9、 17八、更改表格線顏色八、更改表格線顏色方法一：“工具選項”，打開“選項”對話框，點擊“視圖”選項卡 在 “網(wǎng)格線顏色”下拉列表中，可以為單元格邊框線重新指定一種顏色 Page 18方法二：選定單元格區(qū)域后，右鍵選擇“設置單元格格式”，打開“單元格格式”對話框 點擊“邊框”選項卡，然后指定邊框線的顏色、線型，為單元格的四個邊框分別指定邊框線 八、更改表格線顏色八、更改表格線顏色Page 19“工具選項”在打開的“選項”對話框中點擊“視圖”選項卡 去掉“窗口選項”中的“零值”復選項的選擇 九、零值不顯示九、零值不顯示Page 20讓每一頁都顯示標題行單擊表格標題行下邊那行最前端的單元格 “窗口

10、凍結窗口”十、凍結窗口十、凍結窗口Page 21十一、表頭斜線十一、表頭斜線在A1單元格中，輸入“項目名稱”，然后按兩次Alt+回車鍵，再輸入“日期”“格式單元格”，打開“單元格格式”對話框點擊“邊框”選項卡單擊左斜線按鈕點擊“對齊”選項卡，將文本的水平和垂直對齊方式都設置為“兩端對齊” “確定”雙擊A1單元格，將插入點定位到“項目名稱”左邊，并用空格鍵調整“項目名稱”的位置至合適為止Page 22十二、加密十二、加密“文件另存為”在彈出的“另存為”對話框中，點擊“工具”欄下的“常規(guī)選項”按鈕在彈出的“保存選項”對話框中輸入密碼，確定Page 23十三、行列轉換粘貼十三、行列轉換粘貼復制行或列

11、內容右鍵-“選擇性粘貼”選擇“數(shù)值”和“轉制”Page 24ExcelExcel打印得心應手打印得心應手3Page 25一、固定標題打印一、固定標題打印讓每一頁打印輸出后都有標題行“文件頁面設置” “工作表”選項卡 點擊“頂端標題行”框后面的紅色箭頭按鈕打開“頁面設置-頂端標題行”對話框在工作表中選擇好需要在每一頁都打印輸出的標題行退出“頁面設置-頂端標題行”對話框后，確定 Page 26二、選擇希望打印的區(qū)域二、選擇希望打印的區(qū)域先在工作表中選擇要打印的區(qū)域 “文件打印” 在打開的“打印內容”對話框中，選中“選定區(qū)域”單選項 Page 27三、打印整頁顯示三、打印整頁顯示打印預覽打印預覽選擇

12、選擇“設置設置”頁面頁面“縱向縱向”或或“橫向橫向”調整調整“縮放比例縮放比例”Page 28四、同時對多個工作表設置打印要求四、同時對多個工作表設置打印要求 一個工作簿中往往有多個工作表，而在打印時往往這些工作表的設置又基本相同。如果逐份工作表設置，那就會很麻煩，怎么辦？方法一：先單擊第一張工作表的標簽，然后按住“Ctrl”鍵再單擊其它工作表的標簽，就可以將這些工作表同時選中。然后再對它們進行相應的頁面設置。方法二：如果這些工作表是相鄰的，那么，我們可以單擊第一份工作表標簽后，再按住“Shift”鍵，單擊最后一份工作表標簽，將它們之間的工作表同時選中。然后再對它們進行相應的頁面設置。 Pag

13、e 29五、同時打印多個工作簿五、同時打印多個工作簿“文件 打開” 在“打開”對話框中，按住“Ctrl”鍵，逐一選中要打印的工作簿然后點擊對話框中“工具”菜單中的“打印”命令即可Page 30六、信函套打六、信函套打準備工作：一個一個wordword文檔：文檔：續(xù)期繳費通知書模版續(xù)期繳費通知書模版一個一個excelexcel文檔：文檔：客戶信息（對應通知書客戶信息（對應通知書上的缺失信息）上的缺失信息）Page 31六、信函套打六、信函套打建立word文檔與excel文檔的聯(lián)系在在wordword文檔中，進行文檔中，進行“視圖視圖工具欄工具欄郵件合并郵件合并”操作操作點擊點擊“郵件合并工具欄郵

14、件合并工具欄”中的中的“打開數(shù)據(jù)源打開數(shù)據(jù)源”按鈕，在彈出的按鈕，在彈出的“選選擇數(shù)據(jù)源擇數(shù)據(jù)源”對話框中打開客戶信息的對話框中打開客戶信息的excelexcel文檔，并在接下來的文檔，并在接下來的“選擇表格選擇表格”對話框中選擇對應的對話框中選擇對應的sheetsheet調用數(shù)據(jù)將鼠標點在將鼠標點在“投保人姓名投保人姓名”前面的橫線上前面的橫線上點擊點擊“合并郵件工具欄合并郵件工具欄”中的中的“插入域插入域”按鈕，在彈出的按鈕，在彈出的“插入合插入合并域并域”對話框中選擇對話框中選擇“投保人姓名投保人姓名”，插入，插入用同樣的方法，插入其他的信息，如地址、郵編、應繳日期等用同樣的方法，插入其

15、他的信息，如地址、郵編、應繳日期等查看數(shù)據(jù)點擊點擊“合并郵件工具欄合并郵件工具欄”中的中的“查看合并數(shù)據(jù)查看合并數(shù)據(jù)”按鈕，即可查看數(shù)按鈕，即可查看數(shù)據(jù)據(jù)打印“合并郵件工具欄合并郵件工具欄”中的中的“合并到打印機合并到打印機”按鈕，即可打印按鈕，即可打印Page 32ExcelExcel常用函數(shù)常用函數(shù)4Page 33一、求和函數(shù)一、求和函數(shù)【SUMSUM】行或列求和 H3單元格 =SUM(B3:G3)B11單元格 =SUM(B3:B10)區(qū)域求和 H11單元格 =SUM(B3:G10)Page 34鼠標拖拉法選中存放求和結果的單元格，點擊工具欄中的“”（自動求和）按鈕用鼠標拖過需要求和的所有

16、單元格，然后回車即可注：如果需要求和的單元格是不連續(xù)的，則可按住Ctrl鍵分別拖過它們。一、求和函數(shù)一、求和函數(shù)【SUMSUM】Page 352 2、平均數(shù)函數(shù)、平均數(shù)函數(shù)【AVERAGEAVERAGE】AVERAGE函數(shù)用途：求算數(shù)平均數(shù)請注意下面兩個函數(shù)的書寫=AVERAGE(A14:A19)=AVERAGEA(A14:A19)分數(shù)平均分15065.6=AVERAGE(A14:A19)（328/5=65.6）無效64平均分28554.7=AVERAGEA(A14:A19)（328/6=54.7）3495Page 36三、計數(shù)函數(shù)三、計數(shù)函數(shù)【COUNTCOUNT】用途：計數(shù)COUNT統(tǒng)計所

17、選中的單元格區(qū)域中數(shù)字的個數(shù)COUNTA統(tǒng)計所選中的單元格區(qū)域中非空單元格的個數(shù)（包括數(shù)字、文字等）例，Page 37四、常用函數(shù)快捷方式四、常用函數(shù)快捷方式在excel表格的右下角有一個常用函數(shù)快捷方式的區(qū)域（如同所示），可以右鍵點擊該區(qū)域，使用六種常用函數(shù)這六種常用函數(shù)，分別是：平均值（AVERAGE）計數(shù)（COUNTA）計數(shù)值（COUNT）最大值（MAX）最小值（MIN）求和（SUM）Page 38五、五、IFIF函數(shù)函數(shù)【COUNTIF/SUMIFCOUNTIF/SUMIF】 IF函數(shù)函數(shù)在B12單元格中輸入： =IF(B11=60,”合格”,”不合格”)邏輯判斷式邏輯判斷式當邏輯判斷

18、式為真當邏輯判斷式為真時，返回的值時，返回的值當邏輯判斷式為當邏輯判斷式為假時，返回的值假時，返回的值Page 39COUNTIFCOUNTIF函數(shù)函數(shù)功能：統(tǒng)計符合條件要求的單元格的數(shù)量例，求出在B2到B4這三個單元格中大于20的數(shù)字有幾個五、五、IFIF函數(shù)函數(shù)【COUNTIF/SUMIFCOUNTIF/SUMIF】Page 40SUMIFSUMIF函數(shù)函數(shù)功能：統(tǒng)計符合條件要求的單元格中數(shù)字之和例，可以求出在B2到B4這三個單元格中大于20的數(shù)字之和五、五、IFIF函數(shù)函數(shù)【COUNTIF/SUMIFCOUNTIF/SUMIF】Page 41六、索引函數(shù)六、索引函數(shù)【Vlookup/Hl

19、ookupVlookup/Hlookup】Vlookup用于在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當前列中指定行處的數(shù)值Hlookup用于在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行中指定列處的數(shù)值當比較值位于數(shù)據(jù)表的首行，并且要查找下面給定行中的數(shù)據(jù)時，請使用Hlookup當比較值位于數(shù)據(jù)表的最左列，并且要查找右面給定列中的數(shù)據(jù)時，請使用VlookupPage 42七、美化表格的函數(shù)七、美化表格的函數(shù)【MODMOD】功能：設置間隔式底紋例，Page 43選中需要設置間隔式底紋的區(qū)域“格式條件格式”，彈出的“條件格式”對話框（如圖）在左側的下拉菜單中選擇

20、“公式”，并在右側區(qū)域輸入=MOD(ROW()，2),然后點擊“格式”按鈕七、美化表格的函數(shù)七、美化表格的函數(shù)【MODMOD】Page 44八、字段取數(shù)函數(shù)八、字段取數(shù)函數(shù)【midmid】從左邊取從左邊取 =left(A6,3=left(A6,3）從右邊取從右邊取 =right(A6,3)=right(A6,3)從中間取從中間取 =mid(A6,4,3)=mid(A6,4,3) 從第四個字段開始共取3個數(shù)字Page 45九、文本函數(shù)九、文本函數(shù)【texttext】例如保全批量上載模版需要的是文本格式，例如保全批量上載模版需要的是文本格式，如何將數(shù)字轉換為文本格式？如何將數(shù)字轉換為文本格式？ =

21、TEXT(A2,0) =TEXT(A2,0)注意左上角部分的綠色標志注意左上角部分的綠色標志保單總數(shù)為保單總數(shù)為1515位，前面不足位數(shù)需補零，位，前面不足位數(shù)需補零，如何操作？如何操作？ =TEXT(A2,000000000000000) =TEXT(A2,000000000000000)Page 46特別補充特別補充- -日期計日期計算算5Page 47一、顯示當前日期一、顯示當前日期顯示當天日期顯示當天日期（即能夠自動（即能夠自動更新）更新） 函數(shù)函數(shù)TODAY顯示工作時的顯示工作時的日期日期Ctrl + ;顯示當前日期顯示當前日期Page 48二、快速輸入日期二、快速輸入日期先在工作表的A2單元格中輸入日期“2008-4-1”在末尾右格處往下拖曳右尾格點下拉選項可進行多種方式的填充輸入Page 49三、計算對應的星期數(shù)三、計算對應的星期數(shù)先在日期一列輸入日期，如“2007-4-21”,并將格式設置為日期格式；在B2單元格中輸入公式： =CHOOSE(WEEKDAY(A4,2),星期一,星期二,星期三,星期四,星期五,星期六,星期日)Page 50四、計算年數(shù)四、計算年數(shù)在B5單元格中輸入公式： =YEAR(B3)-YEAR(B2)Page 51五、計算天數(shù)五、計算天數(shù)Page 52六、計算工作日六