EXCEL2010課件(特別基礎)

EXCEL2010實用教程2013年11月課程背景當你面對每個月、甚至每一天都要去做同一件數(shù)據(jù)處理工作時；當面對龐大的數(shù)據(jù)處理量，哪怕你三頭六臂、身懷絕技依然要做個三天三夜時，你是否感到厭煩和無奈？有沒有更輕松、更快速的方法來完成這些工作？Excel是一個優(yōu)秀的電子表格軟件，備受廣闊辦公人員的青睞。是一種功能強大的電子表格軟件，具有對數(shù)據(jù)進行系統(tǒng)處理、分析、統(tǒng)計等功能，而且具有豐富的函數(shù)，在數(shù)學計算中發(fā)揮著不容無視的作用。Excel值得學習的原因普及性標準性兼容性目錄根底篇中級篇認識EXCEL工作界面操作EXCEL工作表實用小技巧匯萃公式與函數(shù)認識EXCEL工作界面，了解術語工作表標簽單元格區(qū)域編輯欄與EXCEL合作多年，它們的名字叫得上來嗎？目錄根底篇中級篇認識EXCEL工作界面操作EXCEL工作表實用小技巧匯萃公式與函數(shù)1.2操作Excel工作表工作簿與工作表1.工作簿

工作簿〔Book〕是Excel中存儲電子表格的一種根本文件，其后綴名為.xls，一個工作簿可以看成是由假設干個二維表格疊合而成的一組三維表格。

一個工作簿由多張工作表〔Sheet〕組成。每個工作簿最多可以包含255張工作表。2.工作表 工作表就類似帳簿中的帳頁。工作表是Excel的根本工作單位，位于工作簿的中央?yún)^(qū)域，包含按行和列排列的1048576行*6384列個單元格，是工作簿的一局部，也稱為電子表格，它是Excel用來存儲和處理數(shù)據(jù)的最主要的文檔，可以顯示和分析數(shù)據(jù)，能容納的數(shù)據(jù)有字符、數(shù)字、公式、圖表等?？梢酝瑫r在多張工作表上輸入并編輯數(shù)據(jù)，并且可以對不同工作表的數(shù)據(jù)進行匯總計算。3.單元格與單元格地址 工作表區(qū)中每一個行、列交叉處所形成的矩形區(qū)域稱為單元格。單元格是Excel處理表格數(shù)據(jù)時的最小操作單位。在單元格內可以存放文字、數(shù)字或公式，每個單元格中最多可存放32000個字符信息。為了數(shù)據(jù)處理方便起見，單元格應該使用一個唯一的名稱來識別，這個名稱稱為單元地址，單元格的地址由列號和行號組成。1.2.2輸入工作表數(shù)據(jù)1、工作表的建立 任何一個二維數(shù)據(jù)表都可建成一個工作表。在新建的工作簿中，選取一個空白工作表，是創(chuàng)立工作表的第一步。逐一向表中輸入文字和數(shù)據(jù)，就有了一張工作表的結構。2、數(shù)據(jù)輸入(1)數(shù)值輸入數(shù)值輸入默認右對齊。正數(shù)輸入時可省略“+”；負數(shù)輸入時，或者加負號，或者將數(shù)值加上圓括號，例如，-6.09與〔6.09〕同義。(2)字符輸入字符是指字母、漢字以及非計算性的數(shù)字等，默認情況下輸入的文本在單元格中以左對齊形式顯示。如輸入學號“2001528121”等數(shù)字型信息時，必須在第一個數(shù)字前先輸入一個‘〔單引號〕。例如，’2001528121。

身份證號碼的錄入問題：有時我們在向單元格中輸入保單號或比較長的數(shù)字時，單元格有時會自動的把數(shù)字按科學計數(shù)法表示，如1.23457E+15。解決方法主要是將數(shù)字轉為文本解決方法一：在輸入數(shù)字時在數(shù)字前加一個單引號，如：’，Excel會把前面加單引號的數(shù)字串按文本方式來處理。解決方法二：先將單元格設為文本格式，然后再輸入數(shù)字。

(3)日期和時間輸入Excel將日期和時間視為數(shù)字處理，默認情況下也以右對齊方式顯示。輸入日期時，可用“/”或“—”〔減號〕分隔年、月、日局部。例如，2002/02/12。輸入時間時，可用“：”〔冒號〕分隔時、分、秒局部。例如，10：30：47。 雙擊單元格，系統(tǒng)轉入編輯狀態(tài)，可作修改操作。3、單元格與數(shù)據(jù)區(qū)的選取單元格的選取連續(xù)單元格的選取不全連續(xù)單元格的選定整行〔或整列〕的選取多行〔或多列〕的選取工作表所有單元格的選取Ctrl鍵妙用：1、快速定位：Ctrl+→(上下左右鍵〕2、選擇不連續(xù)的區(qū)域3、選擇不連續(xù)的工作表Shift鍵妙用：1、選擇指定連續(xù)的區(qū)域shift+鼠標左鍵，2、快速選擇連續(xù)區(qū)域：Ctrl+Shift→(上下左右鍵〕3、選擇不連續(xù)的工作表

巧用Ctrl、Shift鍵，省時省力在Windows操作系統(tǒng)中，按住Shift鍵可以選擇多個彼此相鄰的選項，按住Ctrl鍵可以選擇彼此不相鄰的選項。常識4、數(shù)據(jù)的復制和移動單元格或數(shù)據(jù)區(qū)的信息移動和復制。以插入方式移動或復制。5、數(shù)據(jù)填充對重復或有規(guī)律變化數(shù)據(jù)的輸入，可用數(shù)據(jù)的填充來實現(xiàn)。填充相同數(shù)據(jù)輸入序列數(shù)據(jù)6、行、列及單元格的插入 修改工作表數(shù)據(jù)時，可在表中添加一個空行、一個空列或是假設干個單元格，而表格中已有的數(shù)據(jù)會按照指定的方式遷移，自動完成表格空間的調整。7、數(shù)據(jù)區(qū)或單元格的刪除 刪除操作有兩種形式：一是只刪除選擇區(qū)中的數(shù)據(jù)內容，而保存數(shù)據(jù)區(qū)所占有的位置；二是數(shù)據(jù)和位置區(qū)域一起被刪除。1.2.3編輯工作表1、工作表的添加、刪除與重命名2、工作表的移動和復制在一個工作簿內移動或復制工作表在工作簿之間移動或復制工作表3、工作表窗口的拆分和凍結拆分：可同時查看分隔較遠的工作表局部凍結：保持工作表的某一局部在其他局部滾動時可見1.2.4格式化工作表1、數(shù)字格式的設置使用“開始”工具欄設置數(shù)字格式(1)”貨幣樣式”按鈕給數(shù)字添加貨幣符號，并且增加兩位小數(shù)。(2)”百分比樣式“按鈕將原數(shù)字乘以100后，再在數(shù)字后加上百分號。(3)”千位分隔樣式”按鈕在數(shù)字中參加千位分隔符。(4)”增加小數(shù)位數(shù)”按鈕使數(shù)字的小數(shù)位數(shù)增加一位。(5)”減少小數(shù)位數(shù)”按鈕使數(shù)字的小數(shù)位數(shù)減少一位。使用菜單命令設置數(shù)字格式2、字體、對齊方式、邊框底紋的設置 對表格的數(shù)據(jù)顯示及表格邊框的格式可進行修飾和調整。方法是：先選定數(shù)據(jù)區(qū)域，再選擇“格式|單元格”命令，在翻開的對話框中選擇不同的選項卡來實現(xiàn)。

3、行高和列寬的調整行高和列寬有精確和粗略兩種調整方式。(1)精確調整選擇“格式|行〔列〕|行寬〔列寬〕”，翻開對話框，輸入適當?shù)闹怠?2)粗略調整將鼠標指針移向需調整的格線上，使鼠標指針變?yōu)橐粋€帶有左右箭頭的黑色十字，按住鼠標左鍵并拖動即可。4、自動套用格式 Excel內置了一些表格修飾方案，對表格的組成局部定義了一些特定的格式。套用這些格式，既可美化工作表，也可省去設置和操作過程。目錄根底篇中級篇認識EXCEL工作界面操作EXCEL工作表實用小技巧匯萃公式與函數(shù)多條件排序也不是很難2010篩選功能更強大連不同顏色都能篩選出來2010的粘貼更靈活數(shù)據(jù)的篩除更加強大輕松去除重復數(shù)據(jù)2010的條件格式更加智能為了對數(shù)據(jù)進行自動分類，便于快速發(fā)現(xiàn)問題，只要加上條件格式，就輕松多了局部鎖定工作表，讓局部重要信息無法修改12象系統(tǒng)一樣，做個下拉框也就這么簡單只要你喜歡，可以選右邊任一一種方法做下拉框，第一種方法，需要手工輸入，第二方法可以利用現(xiàn)成的數(shù)據(jù)，但下拉框的內容也隨數(shù)據(jù)的改變而改變自定義序列讓你感覺很個性〔1/2〕自定義序列讓你感覺很個性〔2/2〕1、輸入其中一個序列，再下拉單元格，序列會自動填充2、還可以按自定義序列排序目錄根底篇中級篇認識EXCEL工作界面操作EXCEL工作表實用小技巧匯萃公式與函數(shù)公式Excel表格中不僅可以輸入數(shù)據(jù)，也可以使用公式對工作表中的數(shù)據(jù)進行各種數(shù)值計算和統(tǒng)計分析等Excel的公式是由運算符號，和參與計算的運算數(shù)組成的表達式公式中的運算符算術運算符：+-*/^%字符運算符：&連接符比較運算符：=<><=>=<>邏輯運算符：andornot以函數(shù)形式出現(xiàn)優(yōu)先級順序：算術運算符→字符運算符→比較運算符→邏輯函數(shù)符(使用括號可確定運算順序)公式中的數(shù)據(jù)類型輸入公式要注意公式中的運算對象可以包括：數(shù)值和字符、單元格地址、區(qū)域、函數(shù)等,如：=A2+B2+C2不要隨意包含空格公式中的字符要用半角引號括起來公式中運算符兩邊的數(shù)據(jù)類型要相同如：="ab"+25出錯#VALUE公式的輸入公式的根本特性：公式的輸入是以“=”開始公式計算結果顯示在單元格中公式本身顯示在編輯欄中公式例如=1+2+6〔數(shù)值計算〕=A1+B2〔引用單元格地址〕=SUM(A1:C4)〔公式計算〕=IF(and(A1>A2,B1>B2),100*A1/A2,100*B1/B2)〔函數(shù)計算〕="ABC"&"XYZ"(字符計算，結果:ABCXYZ)

公式中常用單元格的引用來代替單元格的具體數(shù)據(jù)，當公式中被引用單元格的數(shù)據(jù)變化時，公式的計算結果會隨之變化引用運算符引用運算符意義例子冒號(:)區(qū)域運算符，表示對兩個引用之間，包括兩個引用在內的單元格區(qū)域進行引用A1:B2表示A1、A2、B1和B2共4個單元格逗號(,)聯(lián)合運算符，表示將多個引用合并為一個引用A1:B2,D2:E3表示A1、A2、B1、B2、D2、D3、E2、E3共8個單元格空格()交叉運算符，表示產生同時屬于兩個引用的單元格區(qū)域G1:I3H2:J3表示單元格區(qū)域H2:I3，即H2、H3、I2、I3共4個單元格相對引用、絕對引用、混合引用引用：在單元格中輸入的不是數(shù)據(jù)，而是該數(shù)據(jù)所在的單元格地址或范圍相對引用：引用時，在單元格地址的列編號和行編號前不加任何符號絕對引用：引用時，在單元格地址的列編號和行編號前各加一個“$”符號混合引用：引用時，只在單元格地址的列編號或行編號前加一個“$”符號相對引用E5絕對引用$E$5混合引用$E2、A$8三者區(qū)別相對引用：當把公式從某一個單元格復制到另一個單元格時，公式中的被引用單元格地址隨公式的被復制而發(fā)生變化，但是目標單元格與被引用單元格保持相對位移關系絕對引用：當把公式從某一個單元格復制到另一個單元格時，被復制公式中的單元格地址不隨公式的被復制而改變混合引用：當把公式從某一個單元格復制到另一個單元格時，混合引用的單元格地址只會局部發(fā)生變化，并且發(fā)生變化的局部與原引用單元格保持相對位移關系總結：凡加上“$”符號的行或列在復制時不發(fā)生變化，否那么都會發(fā)生變化公式的調整相對地址引用:在公式復制時將自動調整調整原那么是：新行地址=原來行地址+行偏移量新列地址=原來列地址+列偏移量例如：將D2單元中的公式=A2+B2+C2復制到D4后，D4中的公式=A4+B4+C4公式的調整絕對地址引用在公式復制時不變。例如：C3單元的公式=$B$2+$B$3+$B$4復制到D5中，D5單元的公式=$B$2+$B$3+$B$4混合地址引用在公式復制時絕對地址不變，相對地址按規(guī)那么調整。有錢能使鬼推磨，給“$”的就不動了當把一個含有單元格地址的公式拷貝到一個新的位置，公式中的單元格地址會隨著改變。如讓你的EXCEL中單元格地址按你的需要改變或不改變？你可以在列號或行號前添加符號“$”來凍結單元格地址，使之在拷貝時保持固定不變。491.函數(shù)語法：由函數(shù)名+括號+參數(shù)組成例：求和函數(shù)：SUM〔A1,B2,…)。參數(shù)與參數(shù)之間用逗號“,”隔開函數(shù)常用函數(shù)〔1/2〕Vlookup：不會vlookup,不要說自己精通excel進行兩個數(shù)的數(shù)據(jù)匹配，將一個表的信息對應到另一個表中Match:無論你在哪，我都能找到你的位置查找某個數(shù)據(jù)在區(qū)域中的相對位置If:讓你更加有邏輯IF(C2>100%，“達成”，“未達成”〕，如果單元格C2大于100%，那么顯示“達成”

rank:電腦排名，錯不了計算一個數(shù)值在一組數(shù)值中的排名，可以正著排，也可以反著排常用函數(shù)〔2/2〕Left\right\mid：好象也不復雜，字符串隨你取left取左邊的幾位，right取右邊的幾位，mid取中間的幾位Sum\sumif\sumifs：變著把戲求和sumif單條件求和：sumifs象英文里面的復數(shù)，那就是多條件求和count\countif\countifs：sum的兄弟函數(shù)，變著把戲計數(shù)

value\text:單元格格式隨你設 例：A1=2.3%，那么公式=TEXT(A1，“00.00%”)返回02.30%Row\column\round：留給大家自己去核實吧能看懂公式的幫助，就又是一種境界了〔1/2〕提示區(qū)單擊其實看幫助沒有我們想象的那么難，還有實例呢能看懂公式的幫助，就又是一種境界了〔2/2〕54常用函數(shù)具體使用〔一〕數(shù)學函數(shù)〔二〕邏輯函數(shù)〔三〕文本函數(shù)〔四〕查找與引用函數(shù)〔五〕其他函數(shù)55〔一〕：數(shù)學函數(shù)求和：SUM()條件求和：SUMIF()求個數(shù)：COUNT()條件求個數(shù)：COUNTIF()求算術平均數(shù)：AVERAGE()四舍五入函數(shù)：ROUND()排位函數(shù)：RANK()乘積函數(shù)：PRODUCT()56〔一〕：數(shù)學函數(shù)1.求和：SUM()■SUM(數(shù)值1,數(shù)值2,…)

A1=6A2=7A3=8=SUM(6,8)=6+8=14=SUM(A1,A3)=6+8=14=SUM(A1:A3)=6+7+8=2157〔一〕：數(shù)學函數(shù)2.條件求和：SUMIF()■SUMIF(查找的范圍，條件〔即對象〕，要求和的范圍)例

ABC1100201000220040150033006020004400802500=SUMIF(A1:A4,”>=200”,B1:B4)=40+60+80=180=SUMIF(A1:A4,”<300”,C1:C4)=1500+1000=2500

58〔一〕：數(shù)學函數(shù)3.求個數(shù)：COUNT()

■COUNT(數(shù)值1,數(shù)值2,…)例

ABC1100201000220040150033006020004400802500=COUNT(A1:A4)=4=COUNT(A1:C4)=12

59〔一〕：數(shù)學函數(shù)4.條件求個數(shù)：COUNTIF()

■COUNTIF(范圍,條件)例

ABC1100201000220040150033006020004400802500=COUNTIF(A1:A4，”<>200”)=3=COUNTIF(A1:C4,”>=1000”)=4=COUNTIF(A1:C4,”>=100”)-COUNTIF(A1:C4,”>2000”)=7

60〔一〕：數(shù)學函數(shù)5.求算術平均數(shù)：AVERAGE()

■AVERAGE(數(shù)值1,數(shù)值2,…)例

ABC1100201000220040150033006020004400802500=AVERAGE(A1,B2)=60=AVERAGE(A1:A4)=25061〔一〕：數(shù)學函數(shù)6.四舍五入函數(shù)：ROUND()■ROUND(數(shù)值,保存的小數(shù)位數(shù))例

ABC1100201000220040150033006020004400802500=ROUND(A1/A3,2)=0.3362〔一〕：數(shù)學函數(shù)7.排位函數(shù)：RANK()

■RANK(數(shù)值,范圍,序別)1-升序0-降序例

ABC1100201000220040150033006020004400802500=RANK(A1,A1:A4,1)=1=RANK(A1,A1:A4,0)=4=RANK(A1,A1:C4,1)=563〔一〕：數(shù)學函數(shù)8.乘積函數(shù)：PRODUCT()

■PRODUCT(數(shù)值1,數(shù)值2,…)例

ABC1100201000220040150033006020004400802500=PRODUCT(A1,A2)=20000=PRODUCT(A1,A2,A3)=6000000=PRODUCT(A1,B2,C3)=80000001.將條件按且方式結合函數(shù)ANDAND(條件)括號中條件值均為TRUE，那么為TRUE；如果任一個為FALSE，那么為FALSE

=AND〔5>3,2>1〕=TRUE=AND〔1>3,2>1〕=FALSE〔二〕：邏輯函數(shù)2.將條件按或方式結合函數(shù)OROR(條件)括號中條件值均為FALSE，那么為FALSE；如果任一個為TRUE，那么為TRUE

=OR〔5>3,2>1〕=TRUE=OR〔1>3,2>1〕=TRUE=OR〔1>3,2>5〕=FALSE〔二〕：邏輯函數(shù)3.將條件值反過來函數(shù)NOTNOT(條件)括號中條件值為FALSE，那么為TRUE；如果為TRUE，那么為FALSE

=NOT〔5>3〕=FALSE=NOT〔1>3〕=TRUE〔二〕：邏輯函數(shù)4.執(zhí)行條件函數(shù)IFIF(條件,執(zhí)行條件真,執(zhí)行條件假)可以執(zhí)行七層嵌套.

=IF〔5>3,2,3〕=2=IF〔5<3,2,3〕=3=IF(5>3,IF(1>2,6,7),0)=7〔二〕：邏輯函數(shù)5.決斷錯誤函數(shù)IFERRORIFERROR(value,value_if_error)是用來處理可能存在錯誤的函數(shù)value可能存在錯誤的錯value_if_error如果出現(xiàn)錯誤那么出現(xiàn)的值=IFERROR(5/0,”被零除有錯誤”)〔二〕：邏輯函數(shù)69〔三〕：文本函數(shù)截取函數(shù)：LEFT()、RIGHT()、MID()計算字符長度：LEN()合并字符函數(shù)：CONCATENATE()在字符串中查找特定字符：FIND()比較兩個字符是否完全相符：EXACT()將數(shù)值轉化為文本：TEXT()將數(shù)值型字符轉換為數(shù)值：VALUE()70〔三〕：文本函數(shù)1.截取函數(shù)：LEFT()、RIGHT()、MID()

■LEFT(文本,截取長度)--從左邊截取

例=LEFT(“abcdefg”,3)=abc■RIGHT(文本,截取長度)--從右邊截取

例=RIGHT(“abcdefg”,3)=efg■MID(文本,開始位,截取長度)--從中間截取

例=MID(“abcdefg”,2,3)=bcd

71〔三〕：文本函數(shù)2.計算字符長度：LEN()

■LEFT(文本)

例=LEN(“abcdefg”)=7

3.合并字符函數(shù)：CONCATENATE()

■CONCATENATE(文本1,文本2,…)

例=CONCATENATE(“ab”,“cd”,“ef”)=abcdef

72〔三〕：文本函數(shù)4.在字符串中查找特定字符：FIND()■FIND(文本,范圍,數(shù)值)--數(shù)值表示查找第幾個

例

=FIND(“a”,“abcadeafga”,2)=4=FIND(“a”,“abcadeafga”,3)=7=FIND(“a”,“abcadeafga”,4)=10

73〔三〕：文本函數(shù)5.比較兩個字符是否完全相符：EXACT()■EXACT(文本1,文本2)

例=EXACT(“a”,“a”)=TRUE=EXACT(“a”,“ab”)=FALSE=EXACT(“a”,“A”)=FALSE

74〔三〕：文本函數(shù)6.將數(shù)值轉化為文本：TEXT()

■TEXT(數(shù)值,參數(shù))--參數(shù)一般為0

例=TEXT(“1234”,0)=1234

7.將數(shù)值型字符轉化為數(shù)值：VALUE()

■VALUE(數(shù)值文本)

例=VALUE(“1234”)=1234

75〔四〕：查找函數(shù)1.以列的方式查找表格中的值：VLOOKUP()

■VLOOKUP(對象,范圍,數(shù)值,FALSE)

對象----需要查找的文本范圍----至少包含對象及需要返回的值所在的列的連續(xù)范圍數(shù)值----需要返回的值所在列的列數(shù)

FALSE----精確查找

例

=VLOOKUP(A1,A1:D10,3,FALSE)

MATCH查找索引位置MATCH(lookup_value,lookup_array,match_type)lookup_value需要查找的數(shù)值