Excel技巧-IT電腦知識分析課件

1、IT電腦知識 - Excel高階篇 -第1頁，共57頁。特別感謝湯思量主任友情贊助第2頁，共57頁。Excel基礎知識1Excel格式修改細節(jié)2Excel打印得心應手3Excel常用函數(shù)4目 錄特別補充-日期計算5第3頁，共57頁。思考：我們?yōu)槭裁匆獙W習該項技能？第4頁，共57頁。Excel基礎知識1第5頁，共57頁。一、打開和關閉 打開Excel方式：右鍵 - 新建- microsoft excel工作表開始 - 所有程序 - Microsoft Office Excel開始 - 運行 - 輸入excel雙擊桌面快捷方式直接在程序安裝位置C盤找到程序文件打開 關閉Excel方式：直接點右上角

2、的關閉按鈕 點“文件”菜單中的“退出”按鈕 在任務欄上點文件鼠標右鍵，選擇“關閉”雙擊工作簿左上角圖標Alt+F4或ctrl+F4Ctrl+W直接切斷計算機電源關閉還有更絕的嗎？_ 第6頁，共57頁。二、工作簿和工作表工作簿：【Book】 一個Excel文件就是一個工作簿，可由一個或多個工作表組成，默認名字為“book1”。工作表：【Sheet】 用于存儲和管理數(shù)據(jù)的主要文檔，又稱電子表格，默認名字為“sheet1”,用列號（A、B、C)和行號（1、2、3)命名。單元格 是Excel的基本元素，是整體操作的最小單位，可以存放文字、數(shù)字和公式標記：用行標和列標一起，如B5，連續(xù)的單元格區(qū)域用冒號

3、隔開，如A3:F8。第7頁，共57頁。三、行和列 【列】“豎”者為列，excel中用大寫英文表示Excel中最大的列數(shù)為：256列 【行】“橫”者為行，excel中用阿拉伯數(shù)字表示Excel中最大的行數(shù)為：65536行第8頁，共57頁。Excel格式修改細節(jié)2第9頁，共57頁。一、單元格的選定選定單獨的單元格選定整行或整列選定連續(xù)的多單元格：選定不連續(xù)的多單元格：選定只有內(nèi)容的單元格：選定所有的單元格鼠標拖曳ctrlshift+ctrl+箭頭第10頁，共57頁。二、提高輸入效率“記憶式”鍵入 單擊“工具”菜單中的“選項”命令，在“編輯”選項卡中選中“記憶式鍵入”快速輸入當天日期：快速輸入當前時

4、間：當輸入數(shù)字太多時，以科學記數(shù)或以“#”表示，表明該列沒有足夠的寬度，需調(diào)整。ctrl+；Ctrl+Shift+；第11頁，共57頁。三、合適列寬、行高點擊工作表左上角的“全選”按鈕，選中全部單元格然后把鼠標移到任何兩列的交界線處，待鼠標指針變成帶兩個方向箭頭的豎線時，雙擊鼠標，就可以得到合適的列寬了(正好使單元格的內(nèi)容得到完全顯示) 同樣，我們將鼠標移到行與行的交界線處雙擊，也可以得到合適的行高。 第12頁，共57頁。四、“有效性”輸入目的：限定輸入數(shù)據(jù)的有效范圍，如指定為特定數(shù)據(jù)類型、取值范圍、限定輸入的字符數(shù)選定需要設置有效性的單元格區(qū)域操作方法：數(shù)據(jù) 有效性設置 允許 序列在“來源”

5、處填寫選擇內(nèi)容注意兩個選項之間必須用 英文格式下的逗號隔開第13頁，共57頁。五、移動單元格直接拖動 移動Ctrl拖動 復制Shift拖動 插入第14頁，共57頁。六、設置條件格式用途：設定單元格在符合某條件時的顯示方法：第15頁，共57頁。七、設置單元格保護用途：設定保護可防止對單元格進行移動、修改、刪除及隱藏，還可隱藏公式。方法：只有工作表被保護，鎖定單元格或隱藏公式才有效第16頁，共57頁。八、更改表格線顏色方法一：“工具選項”，打開“選項”對話框，點擊“視圖”選項卡 在 “網(wǎng)格線顏色”下拉列表中，可以為單元格邊框線重新指定一種顏色 第17頁，共57頁。方法二：選定單元格區(qū)域后，右鍵選擇

6、“設置單元格格式”，打開“單元格格式”對話框 點擊“邊框”選項卡，然后指定邊框線的顏色、線型，為單元格的四個邊框分別指定邊框線 八、更改表格線顏色兩種有何區(qū)別？第18頁，共57頁?！肮ぞ哌x項”在打開的“選項”對話框中點擊“視圖”選項卡 去掉“窗口選項”中的“零值”復選項的選擇 九、零值不顯示第19頁，共57頁。讓每一頁都顯示標題行單擊表格標題行下邊那行最前端的單元格 “窗口凍結窗口”十、凍結窗口第20頁，共57頁。十一、表頭斜線在A1單元格中，輸入“項目名稱”，然后按兩次Alt+回車鍵，再輸入“日期”“格式單元格”，打開“單元格格式”對話框點擊“邊框”選項卡單擊左斜線按鈕點擊“對齊”選項卡，將

7、文本的水平和垂直對齊方式都設置為“兩端對齊” “確定”雙擊A1單元格，將插入點定位到“項目名稱”左邊，并用空格鍵調(diào)整“項目名稱”的位置至合適為止第21頁，共57頁。十二、加密“文件另存為”在彈出的“另存為”對話框中，點擊“工具”欄下的“常規(guī)選項”按鈕在彈出的“保存選項”對話框中輸入密碼，確定第22頁，共57頁。十三、行列轉換粘貼復制行或列內(nèi)容右鍵-“選擇性粘貼”選擇“數(shù)值”和“轉制”第23頁，共57頁。Excel打印得心應手3第24頁，共57頁。一、固定標題打印讓每一頁打印輸出后都有標題行“文件頁面設置” “工作表”選項卡 點擊“頂端標題行”框后面的紅色箭頭按鈕打開“頁面設置-頂端標題行”對話

8、框在工作表中選擇好需要在每一頁都打印輸出的標題行退出“頁面設置-頂端標題行”對話框后，確定 第25頁，共57頁。二、選擇希望打印的區(qū)域先在工作表中選擇要打印的區(qū)域 “文件打印” 在打開的“打印內(nèi)容”對話框中，選中“選定區(qū)域”單選項 第26頁，共57頁。三、打印整頁顯示打印預覽選擇“設置”頁面“縱向”或“橫向”調(diào)整“縮放比例”第27頁，共57頁。四、同時對多個工作表設置打印要求 一個工作簿中往往有多個工作表，而在打印時往往這些工作表的設置又基本相同。如果逐份工作表設置，那就會很麻煩，怎么辦？方法一：先單擊第一張工作表的標簽，然后按住“Ctrl”鍵再單擊其它工作表的標簽，就可以將這些工作表同時選中

9、。然后再對它們進行相應的頁面設置。方法二：如果這些工作表是相鄰的，那么，我們可以單擊第一份工作表標簽后，再按住“Shift”鍵，單擊最后一份工作表標簽，將它們之間的工作表同時選中。然后再對它們進行相應的頁面設置。 第28頁，共57頁。五、同時打印多個工作簿“文件 打開” 在“打開”對話框中，按住“Ctrl”鍵，逐一選中要打印的工作簿然后點擊對話框中“工具”菜單中的“打印”命令即可第29頁，共57頁。六、信函套打準備工作：一個word文檔：續(xù)期繳費通知書模版一個excel文檔：客戶信息（對應通知書上的缺失信息）例：續(xù)期繳費通知書批量打印第30頁，共57頁。六、信函套打建立word文檔與excel

10、文檔的聯(lián)系在word文檔中，進行“視圖工具欄郵件合并”操作點擊“郵件合并工具欄”中的“打開數(shù)據(jù)源”按鈕，在彈出的“選擇數(shù)據(jù)源”對話框中打開客戶信息的excel文檔，并在接下來的“選擇表格”對話框中選擇對應的sheet調(diào)用數(shù)據(jù)將鼠標點在“投保人姓名”前面的橫線上點擊“合并郵件工具欄”中的“插入域”按鈕，在彈出的“插入合并域”對話框中選擇“投保人姓名”，插入用同樣的方法，插入其他的信息，如地址、郵編、應繳日期等查看數(shù)據(jù)點擊“合并郵件工具欄”中的“查看合并數(shù)據(jù)”按鈕，即可查看數(shù)據(jù)打印“合并郵件工具欄”中的“合并到打印機”按鈕，即可打印第31頁，共57頁。Excel常用函數(shù)4第32頁，共57頁。一、求

11、和函數(shù)【SUM】行或列求和 H3單元格 =SUM(B3:G3)B11單元格 =SUM(B3:B10)區(qū)域求和 H11單元格 =SUM(B3:G10)第33頁，共57頁。鼠標拖拉法選中存放求和結果的單元格，點擊工具欄中的“”（自動求和）按鈕用鼠標拖過需要求和的所有單元格，然后回車即可注：如果需要求和的單元格是不連續(xù)的，則可按住Ctrl鍵分別拖過它們。一、求和函數(shù)【SUM】第34頁，共57頁。2、平均數(shù)函數(shù)【AVERAGE】AVERAGE函數(shù)用途：求算數(shù)平均數(shù)請注意下面兩個函數(shù)的書寫=AVERAGE(A14:A19)=AVERAGEA(A14:A19)第35頁，共57頁。三、計數(shù)函數(shù)【COUNT】

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

13、時，返回的值第38頁，共57頁。COUNTIF函數(shù)功能：統(tǒng)計符合條件要求的單元格的數(shù)量例，求出在B2到B4這三個單元格中大于20的數(shù)字有幾個五、IF函數(shù)【COUNTIF/SUMIF】第39頁，共57頁。SUMIF函數(shù)功能：統(tǒng)計符合條件要求的單元格中數(shù)字之和例，可以求出在B2到B4這三個單元格中大于20的數(shù)字之和五、IF函數(shù)【COUNTIF/SUMIF】第40頁，共57頁。六、索引函數(shù)【Vlookup/Hlookup】Vlookup用于在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當前列中指定行處的數(shù)值Hlookup用于在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行

14、中指定列處的數(shù)值當比較值位于數(shù)據(jù)表的首行，并且要查找下面給定行中的數(shù)據(jù)時，請使用Hlookup當比較值位于數(shù)據(jù)表的最左列，并且要查找右面給定列中的數(shù)據(jù)時，請使用Vlookup第41頁，共57頁。七、美化表格的函數(shù)【MOD】功能：設置間隔式底紋例，如何實現(xiàn)？第42頁，共57頁。選中需要設置間隔式底紋的區(qū)域“格式條件格式”，彈出的“條件格式”對話框（如圖）在左側的下拉菜單中選擇“公式”，并在右側區(qū)域輸入=MOD(ROW()，2),然后點擊“格式”按鈕七、美化表格的函數(shù)【MOD】第43頁，共57頁。八、字段取數(shù)函數(shù)【mid】從左邊取 =left(A6,3）從右邊取 =right(A6,3)從中間取

15、=mid(A6,4,3) 從第四個字段開始共取3個數(shù)字第44頁，共57頁。九、文本函數(shù)【text】例如保全批量上載模版需要的是文本格式，如何將數(shù)字轉換為文本格式？ =TEXT(A2,0)注意左上角部分的綠色標志保單總數(shù)為15位，前面不足位數(shù)需補零，如何操作？ =TEXT(A2,000000000000000)第45頁，共57頁。特別補充-日期計算5第46頁，共57頁。一、顯示當前日期顯示當天日期（即能夠自動更新） 函數(shù)TODAY顯示工作時的日期Ctrl + ;顯示當前日期第47頁，共57頁。二、快速輸入日期先在工作表的A2單元格中輸入日期“2008-4-1”在末尾右格處往下拖曳右尾格點下拉選項

16、可進行多種方式的填充輸入第48頁，共57頁。三、計算對應的星期數(shù)先在日期一列輸入日期，如“2007-4-21”,并將格式設置為日期格式；在B2單元格中輸入公式： =CHOOSE(WEEKDAY(A4,2),星期一,星期二,星期三,星期四,星期五,星期六,星期日)第49頁，共57頁。四、計算年數(shù)在B5單元格中輸入公式： =YEAR(B3)-YEAR(B2)第50頁，共57頁。五、計算天數(shù)第51頁，共57頁。六、計算工作日安裝分析工具庫選擇菜單“工具加載宏”命令，打開“加載宏”對話框，勾選“可用加載宏”下方列表框內(nèi)的“分析工具庫”復選框，單擊“確定”按鈕完成安裝。NETWORKDAYS (start_date,end_date)將單元格格式設置為“常規(guī)”即可注意：僅排除雙周日第52頁，共57頁。六、計算工作日將期間的非周末的節(jié)假日排除NETWORKDAYS (start_date,end_date, holidays)第53頁，共57頁。七、計算若干年、月、日后的日期DATE函數(shù)例，計劃用1年2個月零15天來完成一本圖書的編寫工作，開工日期為2004年7月28日，那么什么時候能完成圖書的編寫呢？ 在B8單元格中輸入公式： =DATE(YEAR(B2)+B4,MONTH(B2)+B5,DAY(B2)+B6)第54