Excel基礎培訓課件

Excel基礎培訓Excel基礎培訓培訓內(nèi)容本專題旨在讓同事們掌握EXCEL在日常工作中的經(jīng)常用到的一些功能及技巧，提高Excel實際操作能力的同時提高工作效率。數(shù)據(jù)的輸入1數(shù)據(jù)的常用操作233435掌握并熟練運用Excel常用技巧常用函數(shù)

單元格的格式

6工作表常用操作培訓內(nèi)容本專題旨在讓同事們掌握EXCEL在日常工作中的經(jīng)常用3培訓內(nèi)容79數(shù)據(jù)透視表3培訓內(nèi)容79數(shù)據(jù)透視表一、數(shù)據(jù)的輸入縱向填充上一單元格內(nèi)容在不連續(xù)的單元格中填充相同內(nèi)容單元格內(nèi)的分行填充序列設置數(shù)據(jù)輸入時的格式，如小數(shù)點開頭為0的數(shù)據(jù)的輸入日期的輸入一、數(shù)據(jù)的輸入縱向填充上一單元格內(nèi)容數(shù)據(jù)的輸入1、縱向填充上一單元格內(nèi)容按鍵盤上的”Ctrl+d”即可。

“Ctrl+R”是向右填充2、在不連續(xù)的單元格中填充相同內(nèi)容先按住Ctrl鍵選中所需要填充的單元格，輸入內(nèi)容后，按Ctrl+回車鍵即可。3、單元格內(nèi)的分行在輸入時或雙擊單元格后在要分行的地方按Alt+回車鍵即可。數(shù)據(jù)的輸入1、縱向填充上一單元格內(nèi)容數(shù)據(jù)的輸入4、填充序列在編輯菜單—填充—序列中選擇要插入的序列。5、設置數(shù)據(jù)輸入時的格式，如小數(shù)點小數(shù)點格式：在工具—選項—編輯中，自動設置小數(shù)點，這樣在輸入數(shù)據(jù)時就不用每次輸入小數(shù)點，如1.22只須輸入122即可，1.2則輸入120。6、開頭為0的數(shù)據(jù)的輸入在單元格格式—數(shù)字中選擇文本，即可輸入開頭為0的數(shù)據(jù)。7、日期的輸入以-或/號將月與日分開即可數(shù)據(jù)的輸入4、填充序列二、數(shù)據(jù)的常用操作插入行或列選擇性粘貼，如公式、數(shù)值、運算排序自動篩選二、數(shù)據(jù)的常用操作插入行或列數(shù)據(jù)的常用操作1、插入行或列在需要插入行或列的地方右擊單擊插入，選擇行或列。2、選擇性粘貼，如公式、數(shù)值、運算選擇性粘貼公式、數(shù)值：首先復制要粘貼的內(nèi)容，定位到要粘貼的位置，右擊選擇選擇性粘貼，選擇公式或者數(shù)值即可。運算：首先復制一個數(shù)值，然后選擇要運算的所有單元格，右擊選擇選擇性粘貼，選擇運算符確定即可。數(shù)據(jù)的常用操作1、插入行或列數(shù)據(jù)的常用操作3、排序選中所有需要排序的內(nèi)容，選擇數(shù)據(jù)—排序，選擇好主要關鍵字的升序或降序和次要關鍵字的升序和降序，以及有無標題行，單擊確定即可。注意：當選中的標題行中有合并的單元格的時候是不能排序的；如果數(shù)據(jù)有多列，則要選中所有列的數(shù)據(jù)，否則只排序選中列的數(shù)據(jù)。4、自動篩選選中所有需要篩選的內(nèi)容，選擇數(shù)據(jù)—篩選—自動篩選。注：數(shù)據(jù)篩選中的自定義還可以篩選出介于兩個數(shù)之間的所有數(shù)據(jù)。數(shù)據(jù)的常用操作3、排序三、單元格的格式插入批注設置單元格格式，如對齊、填充顏色、邊框等三、單元格的格式單元格的格式1、插入批注選擇要插入批注的單元格，右擊選擇插入批注即可輸入批注。2、設置單元格格式，如對齊、填充顏色、邊框等對齊：主要是水平居中對齊和垂直水平對齊，其中水平居中對齊可以在工具欄上直接設置。填充顏色：可以在工具欄上直接設置，也可在單元格格式中的圖案上設置。邊框：可以在工具欄上直接設置也可在單元格格式中的邊框上設置。注：除可設置單個單元格格式外，還可以批量設置單元格格式，也就是先選中要設置的所有單元格后，再右擊—設置單元格格式。單元格的格式1、插入批注四.EXCEL日常操作中的常用技巧

大量的數(shù)據(jù)輸入是一件比較繁瑣的工作，除了提高輸入的速度外，還可以使用Excel提供的幾種用于輔助輸入的方法：1、不連續(xù)單元格填充同一數(shù)據(jù)：選中一個單元格，按住Ctrl鍵，用鼠標單擊其他單元格，就將這些單元格全部都選中了。2、快速輸入的技巧：輸入范圍的選定，Ctrl+單元格；如果需要輸入的文本和上一個單元格的文本相同，直接按下“Ctrl＋D（或R）”鍵就可以完成輸入，其中“Ctrl+D”是向下填充，“Ctrl+R”是向右填充。如果多個單元格需要輸入同樣的文本，我們可以在按住Ctrl鍵的同時，用鼠標點擊需要輸入同樣文本的所有單元格，然后輸入該文本，再按下“Ctrl+Enter”鍵即可，右鍵下拉列表選項快速錄入。四.EXCEL日常操作中的常用技巧大量的數(shù)據(jù)輸入是EXCEL日常操作中的常用技巧1、單元格內(nèi)容的合并：方法一：連字符“&”合并，方法二：利用CONCATENATE函數(shù)。=CONCATENATE(B2,C2)2、單元格內(nèi)容的分列：方法一：工具菜單（數(shù)據(jù)-分列…），方法二：LEFT、RIGHT函數(shù)。例如aaa-111B1=left（A1,3）C1=RIGHT(A1,3)3、格式：自定義格式只改變數(shù)據(jù)的顯示外觀，并不改變數(shù)據(jù)的值，也就是說不影響數(shù)據(jù)的計算。靈活運用好自定義格式功能，將會給實際工作帶來很大的方便。EXCEL日常操作中的常用技巧1、單元格內(nèi)容的合并：方法一：EXCEL日常操作中的常用技巧4、單元格錯誤信息函數(shù)處理：錯誤信息注釋#DIV/0在公式中出現(xiàn)了除以0的錯誤。

#N/A在公式中引用的一些數(shù)據(jù)不可用。

#NAME?公式中的文字，系統(tǒng)不能識別。

#NULL!指定的相交并沒有發(fā)生。

#NUM!參數(shù)輸入值不正確。

#REF!單元格引用無效。

#VALUE!提供的參數(shù)沒有使用系統(tǒng)期望的數(shù)值類型。EXCEL日常操作中的常用技巧4、單元格錯誤信息函數(shù)處理：錯EXCEL日常操作中的常用技巧5、數(shù)字、文本的快速轉換：數(shù)字轉變?yōu)槲谋靖袷剑ㄈ缟矸葑C號）數(shù)字前加“’”，文本格式轉變?yōu)閿?shù)字格式：（1、選擇性粘貼，2、函數(shù)：(TEXT)）。6、建立分類下拉列表：數(shù)據(jù)有效性7、“m2”、“m3”、“°C”快速輸入：ALT+178、179、186。8、禁止復制隱藏行或列中的數(shù)據(jù)：選中要復制的區(qū)域，單擊“編輯→定位”命令，在打開的對話框中點擊“定位條件”按鈕，選中“可見單元格”選項，然后復制粘貼。9、統(tǒng)計不重復數(shù)據(jù)個數(shù)：{=SUM(1/COUNTIF(A1:A12,A1:A12))}然后按下ctrl+shift+enter鍵可在選中單元格里看到統(tǒng)計值EXCEL日常操作中的常用技巧5、數(shù)字、文本的快速轉換：數(shù)字EXCEL日常操作中的常用技巧10、保證數(shù)據(jù)的唯一性：選定目標單元格區(qū)域（這里假設為A1:A10），依次單擊“數(shù)據(jù)→有效性”，打開“數(shù)據(jù)有效性”對話框。在“設置”選項卡中單擊“允許”下拉列表，選擇“自定義”。然后，在“公式”中輸入“=countif($A$1:$A$10,$A1)=1”（不包括引號）。接著，單擊切換到“出錯警告”選項卡，在“樣式”中選擇“停止”，然后分別在“標題”和“錯誤信息”中輸入錯誤提示標題和信息。設置完畢后單擊“確定”退出。此時，我們再在目標單元格錄入數(shù)據(jù)時，Excel就會自動對數(shù)據(jù)的唯一性進行校驗。當出現(xiàn)重復數(shù)據(jù)時，Excel中會出現(xiàn)前面設置的錯誤提示信息。EXCEL日常操作中的常用技巧10、保證數(shù)據(jù)的唯一性：選定目五、函數(shù)Excel公式函數(shù)的介紹自動求和平均數(shù)最大數(shù)最小數(shù)排序計數(shù)條件計數(shù)日期五、函數(shù)18函數(shù)判斷函數(shù)if條件求和sumif查找函數(shù)vlookup18函數(shù)判斷函數(shù)ifExcel公式函數(shù)的介紹

1.excel公式函數(shù)的構成

=（A2+67)/SUM(B2:F2）等號，操作符，運算符構成2.公式函數(shù)的輸入直接在編輯欄輸入，直接在單元格輸入，手工輸入，公式向導輸入3.引用引用分相對引用，絕對引用，混合應用，引用單元格，引用連續(xù)區(qū)域。Excel公式函數(shù)的介紹

1.excel公式函數(shù)的構成函數(shù)數(shù)據(jù)的引用工作簿內(nèi)的數(shù)據(jù)引用：在需要引用數(shù)據(jù)的單元格中輸入=，然后找到要引用的數(shù)據(jù)所在的單元格單擊，這時在需要引用數(shù)據(jù)的單元格中出現(xiàn)類似=I87的內(nèi)容，單擊任意單元格后即可出現(xiàn)所引用的數(shù)據(jù)。不同工作簿內(nèi)的數(shù)據(jù)引用：打開引用數(shù)據(jù)所在的excel文件后，在需要引用數(shù)據(jù)的單元格中輸入=，然后找到要引用的數(shù)據(jù)所在的單元格單擊，這時在需要引用數(shù)據(jù)的單元格中出現(xiàn)類似=[book2.xls]Sheet1!$I$85（其中book2.xls為excel文件名，Sheet1為工作表名，$I$85為單元格名）的內(nèi)容，單擊任意單元格后即可出現(xiàn)所引用的數(shù)據(jù)。函數(shù)數(shù)據(jù)的引用函數(shù)1、自動求和利用狀態(tài)欄查看求和結果：如果狀態(tài)欄上不是求和，則可以右擊更改為求和。利用自動求和工具：選擇好要求和的區(qū)域，單擊工具欄上的自動求和工具即可。利用函數(shù)SUM：利用公式=SUM（I83:I87），括號內(nèi)為所要求和的區(qū)域。選擇好插入公式的單元格后單擊工具欄上的按鈕，再彈出的窗口中選擇SUM確定，單擊選擇求和區(qū)域后確定即可。函數(shù)1、自動求和函數(shù)2.最大數(shù)選擇好插入公式的單元格后單擊工具欄上的按鈕，再彈出的窗口中選擇max確定，單擊選擇求最大數(shù)區(qū)域后確定即可。公式為：=max(I83:I88)3.最小數(shù)選擇好插入公式的單元格后單擊工具欄上的按鈕，再彈出的窗口中選擇min確定，單擊選擇求最小數(shù)區(qū)域后確定即可。公式為：=min(I83:I88)函數(shù)23函數(shù)4.排序選擇好插入公式的單元格后單擊工具欄上的按鈕，再彈出的窗口中選擇rank確定，輸入公式即可。公式為：=RANK(A2,$A2:$A50,1)5、平均數(shù)選擇好插入公式的單元格后單擊工具欄上的按鈕，再彈出的窗口中選擇AVERAGE確定，單擊選擇求平均數(shù)區(qū)域后確定即可。公式為：=AVERAGE(I83:I88)23函數(shù)4.排序24動手做做航空路店員工考試的統(tǒng)計表要求：求每個人的總分，平均分，單項的最高分，最低分，以及排名24動手做做航空路店員工考試的統(tǒng)計表25函數(shù)6、計數(shù)選擇好插入公式的單元格后單擊工具欄上的按鈕，再彈出的窗口中選擇COUNT確定，單擊選擇求計數(shù)區(qū)域和條件后確定即可。公式為：=COUNT(I83:I88)7、條件計數(shù)選擇好插入公式的單元格后單擊工具欄上的按鈕，再彈出的窗口中選擇COUNTIF確定，單擊選擇求計數(shù)區(qū)域后確定即可。公式為：=COUNTIF(I83:I88,">30")25函數(shù)函數(shù)5.日期選擇好插入公式的單元格后單擊工具欄上的按鈕，再彈出的窗口中選擇DATE確定，\

DATE返回代表特定日期的序列號，語法（year，month，day).1900日期系統(tǒng)，1904日期系統(tǒng)=DATE（）-today（）&“天”函數(shù)5.日期動手做設置航空路店開業(yè)倒計時提示牌開業(yè)時間2012.06.11動手做設置航空路店開業(yè)倒計時提示牌286.判斷函數(shù)if

用途：執(zhí)行邏輯判斷，它可以根據(jù)邏輯表達式的真假，返回不同的結果，從而執(zhí)行數(shù)值或公式條件的檢測任務。簡單的邏輯判斷，表達式=IF（A2>0,”未完成任務“，”完成任務“）

286.判斷函數(shù)if

用途：29函數(shù)編碼商品名稱效期

100001353°紅花郎十五年41197=IF(TODAY()-C4>0,"未過期","過期了")100000753°青花郎二十年41198=IF(TODAY()-C5>0,"未過期","過期了")100001553°老郎酒195641199=IF(TODAY()-C6>0,"未過期","過期了")100000852°豐谷特曲(新包裝)41200=IF(TODAY()-C7>0,"未過期","過期了")100000948°豐谷酒王（新）40942=IF(TODAY()-C8>0,"未過期","過期了")100000552°國窖157340943=IF(TODAY()-C9>0,"未過期","過期了")29函數(shù)編碼商品名稱效期100001353°紅花郎十五年430按住CTRL+~則變成如下的結果編碼商品名稱效期

100001353°紅花郎十五年2012-10-15過期了100000753°青花郎二十年2012-10-16過期了100001553°老郎酒19562012-10-17過期了100000852°豐谷特曲(新包裝)2012-10-18過期了100000948°豐谷酒王（新）2012-2-3未過期100000552°國窖15732012-2-4未過期30按住CTRL+~則變成如下的結果編碼商品名稱效期10031函數(shù)復雜的邏輯判斷實例：

=IF(C2>=85,”A”,IF(C2>=70,”B”,IF(C2>=60,”C”,IF(C2<60,”D”))))

意思，如果第一個邏輯判斷表達式“C2>=85”，則單元格被賦值“A”，如第一個邏輯判斷式不成立，則計算第二個if語句，依次類推，直到計算結束。31函數(shù)復雜的邏輯判斷32動手做判斷各個門店應收賬款是否超期已經(jīng)知道業(yè)務發(fā)生日期和賬期，判斷是否超期，函數(shù)同判斷是否已經(jīng)到效期交易時間流水碼商品編碼商品名稱金額倍數(shù)賬期天數(shù)2011-12-23111223604010022100000152°五糧液1701010302012-3-71201076040100201000315勃艮柔赤霞珠干紅葡萄酒40012302012-1-12120112604010013100001252°瀘州老窖特曲1760103032動手做判斷各個門店應收賬款是否超期交易時間流水碼商品編碼33動手做根據(jù)新的資金利息收取辦法，幫小徐做個表格，格式如評分函數(shù)33動手做根據(jù)新的資金利息收取辦法，幫小徐做個表347.條件求和函數(shù)sumifSUMIF(range,criteria,sum_range)

1）range為用于條件判斷的單元格區(qū)域。

2）criteria為確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達式或文本。例如，條件可以表示為32、"32"、">32"或"apples"。條件還可以使用通配符：問號(?)和星號(*)，如需要求和的條件為第二個數(shù)字為2的，可表示為"?2*",從而簡化公式設置。問號匹配任意單個字符；星號匹配任意一串字符。如果要查找實際的問號或星號，請在該字符前鍵入波形符(~)

3）sum_range是需要求和的實際單元格。347.條件求和函數(shù)sumifSUMIF(range,cri35函數(shù)0100000152°五糧液500ml180100001252°瀘州老窖特曲500ml40100000152°五糧液500ml1801000001=SUMIF(B2:B4,B5,E2:E4)

01000012=SUMIF(B3:B4,B6,E3:E4)

35函數(shù)0100000152°五糧液500ml180100036上個表CTRL+~后則得出下面的結果0100000152°五糧液500ml18.000100001252°瀘州老窖特曲500ml4.000100000152°五糧液500ml18.000100000136

010000124

36上個表CTRL+~后則得出下面的結果0100000152378.查找函數(shù)VLOOKUP在表格或數(shù)組的首列查找制定的數(shù)值，并由此返回表格或數(shù)組當前行中制定列處的數(shù)值格式：VLOOKUP(查詢值,被查詢表格的坐標,返回值在表格中的列號,查詢類型)378.查找函數(shù)VLOOKUP在表格或數(shù)組的首列查找制定的數(shù)38函數(shù)查找類型為邏輯值，匹配方式可以是精確匹配或指近似匹配。TRUE,1或者不輸代表近似匹配；FALSE或0代表精確匹配。如果選擇近似匹配，列表的第一列必須從小到大排序，并返回小于或等于查找值的最大值。比如在1,2,3中找2，返回2；在1,3中找2，返回1。可以看見，如果列表中每一行都是獨立的，一般用近似匹配都不合適。只有當數(shù)據(jù)是小于等于一定值都有同樣結果時，近似匹配才會適用。38函數(shù)查找類型為邏輯值，匹配方式可以是精確匹配或指近39函數(shù)實例0100000152°五糧液500ml180100001252°瀘州老窖特曲500ml40100000152°五糧液500ml1801000001=VLOOKUP($B5,$B$2:$E$4,2,0)=VLOOKUP($B5,$B$2:$E$4,4,0)

01000012=VLOOKUP($B6,$B$2:$E$4,2,0)=VLOOKUP($B6,$B$2:$E$4,4,0)

39函數(shù)實例0100000152°五糧液500ml1801040上個表CTRL+~后則得出下面的結果0100000152°五糧液500ml18.000100001252°瀘州老窖特曲500ml4.000100000152°五糧液500ml18.000100000152°五糧液18

0100001252°瀘州老窖特曲4

40上個表CTRL+~后則得出下面的結果010000015241函數(shù)Vlookup查找函數(shù)的靈活運用，判斷兩個表格中是否有不同項。0100000152°五糧液0100000852°豐谷特曲(新包裝)01000315勃艮柔赤霞珠干紅葡萄酒01000684*勃根金拉菲干紅葡萄酒2005（皮盒）0100001252°瀘州老窖特曲01000615繡女人0100078914°會稽山1743八年陳花雕0100008953°紅花郎十年0100000852°豐谷特曲(新包裝)0100001252°瀘州老窖特曲0100000152°五糧液41函數(shù)Vlookup查找函數(shù)的靈活運用，判斷兩個表格中是否42函數(shù)找出上圖中的相同項和不同項0100000152°五糧液=VLOOKUP(B7,$E$7:$F$10,2,0)0100000852°豐谷特曲(新包裝)=VLOOKUP(E7,$B$7:$C$13,2,0)01000315勃艮柔赤霞珠干紅葡萄酒=VLOOKUP(B8,$E$7:$F$10,2,0)01000684*勃根金拉菲干紅葡萄酒2005（皮盒）=VLOOKUP(E8,$B$7:$C$13,2,0)0100001252°瀘州老窖特曲=VLOOKUP(B9,$E$7:$F$10,2,0)01000615繡女人=VLOOKUP(E9,$B$7:$C$13,2,0)0100078914°會稽山1743八年陳花雕=VLOOKUP(B10,$E$7:$F$10,2,0)0100008953°紅花郎十年=VLOOKUP(E10,$B$7:$C$13,2,0)0100000852°豐谷特曲(新包裝)=VLOOKUP(B11,$E$7:$F$10,2,0)

0100001252°瀘州老窖特曲=VLOOKUP(B12,$E$7:$F$10,2,0)

0100000152°五糧液=VLOOKUP(B13,$E$7:$F$10,2,0)

42函數(shù)找出上圖中的相同項和不同項0100000152°五糧43上個表CTRL+~后則得出下面的結果0100000152°五糧液#N/A0100000852°豐谷特曲(新包裝)52°豐谷特曲(新包裝)01000315勃艮柔赤霞珠干紅葡萄酒#N/A01000684*勃根金拉菲干紅葡萄酒2005（皮盒）#N/A0100001252°瀘州老窖特曲#N/A01000615繡女人#N/A0100078914°會稽山1743八年陳花雕#N/A0100008953°紅花郎十年#N/A0100000852°豐谷特曲(新包裝)52°豐谷特曲(新包裝)

0100001252°瀘州老窖特曲#N/A

0100000152°五糧液#N/A

43上個表CTRL+~后則得出下面的結果010000015244綜合實例進銷存電子表格（講解表格）東勝店進銷存表格44綜合實例進銷存電子表格（講解表格）45動手練（函數(shù)的綜合運用）應付總表（準備表格，指導學員做表格）3月應付總表核對表45動手練（函數(shù)的綜合運用）應付總表（準備表格，指導學員做表六、工作表常用操作工作表的改名工作表的添加、刪除工作表的移動、復制超級鏈接單元格區(qū)域窗口凍結六、工作表常用操作工作表的改名工作表常用操作1、工作表的改名雙擊工作表默認名稱Sheet1，在Sheet1選中后輸入新名稱即可。2、工作表的添加、刪除添加：選擇插入——工作表即可刪除：右擊工作表名—刪除即可3、工作表的移動、復制在同一個工作簿移動：鼠標左鍵按住工作表名拖動即可在同一個工作簿中移動工作表；在不同工作簿移動：右擊工作表名—移動或復制工作表，在彈出的窗口中選擇好工作簿確定即可移動工作表。工作表常用操作1、工作表的改名工作表常用操作在同一個工作簿復制：按住Ctrl的同時，鼠標左鍵按住工作表名拖動即可在同一個工作簿中復制工作表；在不同工作簿復制：右擊工作表名—移動或復制工作表，在彈出的窗口中選擇好工作簿，并且勾上建立副本，單擊確定即可復制工作表。4、超級鏈接在單元格上右擊，選擇超級鏈接，再彈出的對話框中選擇本文檔中的位置，單擊確定即可。5、單元格區(qū)域選中要命名的單元格區(qū)域，單擊名稱框并輸入名稱，按回車鍵確認。工作表常用操作在同一個工作簿復制：按住工作表常用操作6、窗口凍結在輸入行列數(shù)特別多的表格中，凍結窗格

（窗格：文檔窗口的一部分，以垂直或水平條為界限并由此與其他部分分隔開。）使您得以選擇滾動工作表時始終保持可見的數(shù)據(jù)。在滾動時保持行和列標志可見。頂部水平窗格：選擇待拆分處的下一行，然后在“窗口”菜單上，單擊“凍結窗格”。左側垂直窗格：選擇待拆分處的右邊一列，然后在“窗口”菜單上，單擊“凍結窗格”。同時生成頂部和左側窗格：單擊待拆分處右下方的單元格，然后在“窗口”菜單上，單擊“凍結窗格”。工作表常用操作6、窗口凍結50七、數(shù)據(jù)透視表什么是數(shù)據(jù)透視表可以把Excel