1.基礎(chǔ)知識2.函數(shù)3.數(shù)據(jù)有效性4.條件格式5.圖表目

錄mulù)第2頁，共50頁?！?/p>

工

作(gōngzuò)簿

與

工

作(gōngzuò)表工作簿是Excel使用的文件架構(gòu)，我們可以將它想象成是一個工作夾，在這個工作夾里面有許多工作紙，這些工作紙就是工作表。ABC1ABC

2

3

A12452345ABC12345工作表第4頁，共50頁?！肮ぷ鲓A”20工作表標簽匯總未下單匯總Sheet1

布無庫存皮無庫存Sheet2℃第5頁，共50頁。列

號當前單元格行號名字框A1BExcel工作(gōngzuò)界面條件格式數(shù)字

樣式

單元格常規(guī)困

合并后居中對齊方式開始

頁面布局

公式品剪切宋體105號未下單匯總[只讀[兼容模式Microsoft

excel加載項公式欄工作簿工作表排序和查找和

篩選▼選擇編輯字體選項…數(shù)據(jù)

審閱26常

用(chángyòng)快

捷

鍵※

(

1)復制、粘貼、剪切選中區(qū)域復制選定區(qū)域

CTRL+C粘貼選定區(qū)域

CTRL+V剪切選定區(qū)域

CTRL+X撤消最后一次操作

CTRL+Z復制上行單元格

CTRL+D復制左側(cè)單元格

CTRL+R重復上一步操作

CTRL+Y(2)打印顯示“打印”對話框

CTRL+P顯示“打印”預覽

CTRL+F2第6頁，共50頁。序號公式說明1=15*3+20*2包含常量運算的公式2=A1*3+A2*2包含單元格引用的公式3=單價*數(shù)量包含名稱的公式4=SUM(A1*3,A2*2)包含函數(shù)的公式公式是以“=”為引導，通過運算符按照一定的順序組合進行數(shù)據(jù)運算處理的等式。函數(shù)是按照特定的算法執(zhí)行計算的產(chǎn)生一個或一組結(jié)果的預定義的特殊公式。什

么(shén

me)是

函

數(shù)

?

什

么(shén

me)是

公

式

?第8頁，共50頁。Excel幫助Excel幫助和使用方法窗體和控件激活

Excel自定義函數(shù)參考設(shè)置條件格式窗體和控件■

導入和導出數(shù)據(jù)■

安全和隱私拼寫和語法2、EXCEL

公式分類fx邏輯

文本

日期和時間查找與引用

數(shù)學和其他函數(shù)三角函數(shù)函數(shù)庫2、用好EXCEI自帶的幫助，活用函數(shù)的關(guān)鍵是要具備清晰的程序邏輯思維。輔助功能獲取幫助文件轉(zhuǎn)換和兼容性工作表協(xié)同處理工作表數(shù)據(jù)

篩選序公式分析數(shù)據(jù)打印驗證數(shù)據(jù)使用另一種語音表格插入函數(shù)

自動求和最近使用的

財務

函數(shù)1

、函

數(shù)(hánshu)分

類函數(shù)參考導入和導出數(shù)據(jù)安全和隱私文件管理圖表瀏覽

Excel幫助第9頁，共50頁。使用圖形·3、EXCEL

公式(gōngshì)表

達

式公式特定引導符號區(qū)域范圍引用

單元格地址(B4+25)/SUM(D5:F5)數(shù)值型常量

Excel函數(shù)運算操作符號第10頁，共50頁。符號類型符號舉例算術(shù)運算+;-

;*;/;=6^2=3+2*5比較運算>;<;=;<>;>=;<=;=5<2=false文本運算&="天龍"&"八部"區(qū)域運算:=Sum(A1:A3)交叉運算

(

空格)=sum(A1:B5

A4:D9)=sum(A4:B5)聯(lián)合運算,

(

逗

號

)=rank(A1,(A1:A10,C1:C10))4、EXCEL

公式(gōngshì)運算符號第11頁，共50頁?！?/p>

5、EXCEL公式(gōngshì)的相對引用、絕對引用和混合引用1、相對引用：復制公式時地址跟著發(fā)生變化，如C1

單元格有公式：=A1+B1當將公式復制到C2單元格時變?yōu)椋?A2+B2當將公式復制到D1單元格時變?yōu)椋?B1+C12、絕對引用：復制公式時地址不會跟著發(fā)生變化，如C1單元格有公式：=$A$1+$B$1當將公式復制到C2單元格時仍為：=$A$1+$B$1當將公式復制到D1

單元格時仍為：=$A$1+$B$13、混合引用：復制公式時地址的部分內(nèi)容跟著發(fā)生變化，如C1單元格有公式：=$A1+B$1當將公式復制到C2單元格時變?yōu)椋?$A2+B$1當將公式復制到D1單元格時變?yōu)椋?$A1+C$1=SUM(C5:C5)=SUM($C$5:C5)=SUM($C$5)數(shù)據(jù)列相對引用混合引用絕對引用111223336不同的引用在拖動公式時具有不同的結(jié)果。引用的轉(zhuǎn)換快捷鍵：

F4第12頁，共50頁。例

：

=[Book2.xls]sheet1!$A$13、引用未打開的工作簿的數(shù)據(jù)=‘文件路徑[工作簿名稱]工作表名稱′

!目標單元格例：=

D:\Excel

案例[Book2.xls]sheet1′!$A$1※

注：將最好先打開工作簿，再編寫跨表引用公式2、引用已打開的工作簿的數(shù)據(jù)=[工作簿名稱]工作表名稱!

目標單元格1、引用其他工作表的數(shù)據(jù)例：=sheet2!A1EXCEL

公式中的跨工作(gōngzuò)表引用=工作表名稱!目標單元格第13頁，共50頁。類型函數(shù)用途類型函數(shù)用途數(shù)值Sum求和函數(shù)文本len計算文本長度Average平均值函數(shù)left文本截取(左)Max/Min最大/最小函數(shù)right文本截取(右)sumproduct數(shù)組求和函數(shù)mid文本截取(指定)SUMIF條件求和CONCATENATE文本合并Sumifs多條件求和text文本化統(tǒng)計CountCountAcountblank單元格統(tǒng)計函數(shù)value數(shù)值化時間DAY日期查找與引用COLUMN列號比較RANK排名ROW行號LARGE第K大的值LOKKUP查找SMALL第K小的值VLOKKUP列查找邏輯IF判斷函數(shù)HLOOKUP行查找AND且函數(shù)MATCH匹配OR或函數(shù)INDEX引用IFERROR錯誤判斷offset

偏移以上函數(shù)(hánshù)基本能滿足一般

工

作

需

求Q?共50頁.7、EXCEL

常用函數(shù)員工代碼社保號姓名=left(a1,10)=mid(a1,11,3)0002337084張三0002337084張三0000656246李世民0000656246李世民身份證號地區(qū)出

生日

期性別=left(a1,6)=mid(a1,7,6)=right(a1,1)3201027810014923201027810012注

意

：MID函

數(shù)(hánshù)

有3個參數(shù)，而LEFT、RIGHT

只有2個參數(shù)1----文本處理函數(shù)(提取字符Left

、Right

、Mid)為什么是3而不是2?為什么不用Right函

數(shù)

?第15頁，共50頁。日

期年月日日

期=Year(a1)=Month(a1)=Day(a1)=DATE(A1,B1,C1)2009-6-1620096162009-6-16※(2)取得日期/時間的部分字段值(年份、月份、

日數(shù)或小時),無參數(shù)。YEAR()

、MONTH()

、DAY()

、HOUR()2----日期與時間函數(shù)1※(1)取出當前系統(tǒng)時間/日期信息，無參數(shù)(cānshù)。NOW()、TODAY()。第16頁，共50頁。unit代碼函數(shù)返回值開始日期結(jié)束日期公式顯示結(jié)果m時間段中間段中的忽略日期的

數(shù)20009-B-16te

dif(A1,B1,"y")dif(A2,B2,"r2008-3-17366"md"中的

年

月

十

天

數(shù)

差2009-6-16=datedi(A4,B4,"md")30"ym"忽略日期中的年和日，計算月數(shù)差2008-6-172009-6-16=datedif(A5,B5,"ym")11"yd"忽略日期中的年，計算天數(shù)差2008-6-172009-6-16=datedif(A6,B6,"yd")3647.2-----日期與時間函數(shù)2(3)計算兩個liǎnggè)日期之間的天數(shù)、月數(shù)或年數(shù)之差。

Dated(

start_date,end_date,unit)當單位代碼為"YM"

時，計算結(jié)果是兩個日期間隔的月份數(shù)，不計相差年數(shù)。

如忽略年份后，開始日期必須大于結(jié)束日期，否則公式計算錯誤。第17頁，共50頁。數(shù)值Round--四舍五入Roundup--向上取整Rounddown--向下取整公式結(jié)果公式結(jié)果公式

結(jié)果123.456=round(A2,0)123=roundup(A3,0)124=rounddown(A3,0)123123.456=round(A3,2)123.46=roundup(A3,2)123.46=rounddown(A3,2)123.451234.56=round(A4,-2)1200=roundup(A3,-2)1300=rounddown(A3,-2)1200單元格格式數(shù)字對齊分類C):常規(guī)數(shù)值小數(shù)位數(shù)①):區(qū)別qubié)負數(shù)N):(1234)(1234)1234-1234數(shù)值格式用于一般數(shù)字的表示。貨幣和會計格式則提供貨幣值計算的

專用格式。確定

取消3----數(shù)字計算函數(shù)(取整函數(shù)Round、Roundup、Rounddown)字體

邊

框示例123貨市合適專用

時間百分比科學記數(shù)自定義圖案

保護第18頁，共50頁。備注=ROUNDDOWN(

a1,0

)7.----數(shù)字shuz)計尊函數(shù)(取整函數(shù)

Int、Trunc)公式=Int(alI

nt(al上結(jié)果

說明-3.2=Trunc(數(shù)值3.2第19頁，共50頁。Iicrosoft

Excel-Book2回圖文件F)背景著色工具)編輯(E)視圖V)插入I)格式@)

工具(T)

數(shù)據(jù)(①)窗口W)易用寶幫助CH)∑

100%C12fABC16=sum('*3!A1)=SUM(Sheet1:Sheet3!A1)23=SUM(Sheet1:Sheet2!A1)3456?川Sheet1/Sheet2/Sheet3Sheet4/就緒數(shù)字7.4----數(shù)字計算函數(shù)----sum語法：SUM(number1,number2,...)。參數(shù)：Number1,number2,

…為1到30(07以上版本254)個需要求和的數(shù)值、區(qū)域或引用(yǐnyong)。巧用：格式相同的多個工作表的同位置單元格求和。若Sheet1!A1=1、Sheet1!A1=2、Sheet1!A1=3第20頁，共50頁。A列B列C列D列=If(C1>=22,"全勤",C1)序號姓名出勤備注李口3王五214賈六22全勤7

.

6

邏

輯(luó

jio函

數(shù)IFF

用途：按指定的條件計算滿足條件選項第23頁，共50頁。第1個IF的條件參數(shù)當條件為真時返回該值=IF(MOD(ROW(),3)=1源數(shù)據(jù)!A$1,IF

(一MOD(ROW(),3)=2,INDEX

(源數(shù)據(jù)!$A$1:$G$349,(ROW()+4)/3,COLUMN()),當條件為真時進入Index函數(shù)當條件為假時返回空值7.6邏輯函數(shù)IF1、IF

函數(shù)的嵌套：

IF(A1>1,SUM(B1:G1),"")在這個公式(gōngshi)中

，sum

函數(shù)是2級函數(shù)，它是if函

數(shù)

的

參

數(shù)

，(Excel2007

允許64層的嵌套，2003只能7層)=IF(MOD(ROW(),3)=1,源數(shù)據(jù)!A$1,IF(MOD(ROW(),3)=2,INDEX(源數(shù)據(jù)!$A$1:$G$349,(ROW()+4)/3,COLUMN(),""))當條件為假時進入下一個IF第2個IF的條

件

參

數(shù)3、解讀如下：2、復雜案例第24頁，共50頁。ABCDEFG1部門成本中心工號姓名性別工作地點到職日期2人事總務部培訓科000010趙一男廣州3963834部門成本中心工號姓名性別工作地點到職日期5人事總務部培訓科000011錢二男廣州2008/7/1067部門成本中心工號姓名性別工作地點到職日期8人事總務部培訓科000012孫三男廣州2008/7/1910部門成本中心工號姓名性別工作地點到職日期11人事總務部培訓科000013王四男廣州2008/7/127.6函數(shù)編寫邏輯思路例：工資打印切割公式=IF(MOD(ROW(),3)=1,

源數(shù)據(jù)!A$1,IF(MOD(ROW(),3)=2,INDEX(源數(shù)據(jù)!$A$1:$G$349,(ROW()+4)/3,COLUMN()),""))結(jié)果的第一行：永遠是原數(shù)據(jù)中的第一行。結(jié)果的第二行：結(jié)果第2行對應原數(shù)據(jù)第2行，結(jié)果第5行對應原數(shù)據(jù)第3行，第8對應第4,第11對應第5。結(jié)果的第三行：永遠為空。1、

目

的(mudi):

按規(guī)律切割工資條部

門

成本中心

工

號

姓

名

性

別這里面第二個最難算，找找看它們之間有什么規(guī)律。2、

分析數(shù)據(jù)規(guī)律，確定公式組合人事總務部

人事總務部

人事總務部

人事總務部

人事總務部

人事總務部000010000011000012000013000014000015培訓科

培訓科

培訓科

培訓科

培訓科

培訓科趙錢孫王周吳一

二三

四

五

六男

男男

男男

男1234567錯誤信息原因#####!列寬不夠顯示，或使用了負的日期，和時間。#DIV/0!被零除錯誤，將數(shù)字除以零(0)或除以不含數(shù)值的單元格#N/A在函數(shù)或公式中沒有可用數(shù)值時#NAME?在公式中出現(xiàn)excel不能識別的文本時#NULL!計算為空值時，無法得出計算結(jié)果#NUM!當函數(shù)或公式中某個數(shù)字有問題時#REF!當單元格引用無效時，如被刪除、超出單元格位置#VALUE!參數(shù)或數(shù)值類型錯誤公式查錯時的要訣：

公式查錯工具：1、讀懂公式1、鍵盤上的F92

、看看公式在拖動過程中的引用方式是否正確

2、公式選項卡中的3、

當嵌套太多太復雜時，把公式分層粘到單元格中顯示公式錯誤檢查

公式求值公式審核

Excel

公

式(gōngshi)的常見錯誤信息第26頁，共50頁。統(tǒng)計函數(shù)AverageMaxMtinRank提升(tshēng)工作效率熟練掌握靈活運用數(shù)學函數(shù)SUMROUNDROUNDUPROUNDDOWN查詢函數(shù)COUNTIFSUMIF邏輯函數(shù)第27頁，共50頁?！?/p>

對有同樣設(shè)置的所有其他單元格應用這些更改

□

對有同樣設(shè)置的所有其他單元格應用這些更改P)

□

對有同樣設(shè)置的所有其他單元格應用這些更改(P)全部清除C)確定

全部清除C)確定全部清除C)確定取消第30頁，共50頁。數(shù)據(jù)有效性設(shè)置

輸入信息

出錯警告有效性條件允許(A):整數(shù)數(shù)據(jù)①):介于最小值(皿)數(shù)據(jù)有效性設(shè)置

輸入信息出錯警告有效性條件允許(A):時間數(shù)據(jù)D):介于開始時間(S)6:00:00結(jié)束時間N)8:30:00|數(shù)據(jù)有效性設(shè)置

輸入信息有效性條件允

許(A):序列數(shù)據(jù)①):介于來

源(S):數(shù)據(jù)有效性設(shè)置

輸入信息有效性條件允

許(A):文本長度數(shù)據(jù)(①):等于長

度L)10|輸入法模式?忽略空值⑤)?提供下拉箭頭(T)輸入法模式忽略空值B)數(shù)據(jù)有效性設(shè)置

輸入信息有效性條件允許(A):日期數(shù)據(jù)(①):介于開始日期(⑤):數(shù)據(jù)有效性?忽略空值B“公司“設(shè)置

輸入信息出錯警告

輸入法模式有效性條件允許(A):出錯警告

輸入法模式忽略空值B)輸入法模式輸入法模式出錯警告出錯警告自定義數(shù)據(jù)(①):等于公式(F)=Right(a1,2)=數(shù)據(jù)(shuju)有

效

性

2數(shù)椐(shuju)有

效

性

3licrosoftExcel輸入值非法。其他用戶已經(jīng)限定了可以輸入該單元格的數(shù)值。

重試(R)

取消Iicrosoft

Excel輸入值非法。其他用戶已經(jīng)限定了可以輸入該單元格的數(shù)值。是否繼續(xù)?是

Y)

否

)

取消IicrosoftExcel輸入值非法。其他用戶已經(jīng)限定了可以輸入該單元格的數(shù)值。

確定取消不能輸入不符合條件的數(shù)據(jù)，點擊

“重試”可重新輸入，點擊“取消

”則取消輸入。可以選擇是否繼續(xù)輸入數(shù)據(jù)，點

擊“是”可以強行輸入不符合條件數(shù)據(jù)，點擊“否”可以重新輸

入數(shù)據(jù)，點擊“取消”則取消輸入

?？蛇x擇是否輸入數(shù)據(jù)，點擊“確定”可以直接輸入數(shù)據(jù)，點擊“取消”可以取消輸入。告

信息停止第31頁，共50頁。-常用(changyong圖表WebSiteVisitors30,000T25,00020.00015,00010,0005.000JanFebMar

Apr

May

Jun

Jul

Aug

Sep

OctNovDec表現(xiàn)數(shù)據(jù)間的比例關(guān)系比較數(shù)間之間的多少反映數(shù)據(jù)變化的趨勢WebSiteVisitors20,00015,00010,000Jul

Aug

Sep

Oct

NovWebSiteVisitors餅

圖第38頁，共50頁。折線圖表操作實例1按下柱形圖按鈕第五章示文件

開始插入頁面布局公式

數(shù)據(jù)

審閱視圖選

擇(xuǎnzé)三維柱形圖下的三

維簇狀柱形圖隨即在工作表中建立好圖表物件數(shù)據(jù)

表格

透視表表格A32

牌3

福

特4

寶

士5

裕隆6

三菱

791011121314151617181920柱形圖折線圖

餅圖

條形圖面積圖散點圖其他圖表二維柱形圖I圖片

剪貼畫

形狀

SmartArt

屏幕截圖插圖f

福

特C第

一

季高級房車銷售量一

月12159831536756二月985745962569三月75312501123628所有圖表類型(A)

…

.第39頁，共50頁。圓柱圖棱錐圖折線圖

柱形圖盈迷你圖三維柱形圖ABCDEFGHIJKL第一季高級房車銷售量2廠牌

一

月

二

月

三月

總計40003500300025002000150010005003福特121598575329534寶士983745125029785裕隆1536962112336216三菱756569628195378若是修改了圖表的來源資料，圖表會自動重新修正內(nèi)容，不需重新繪制圖表91011121314151617注：也可以在選取源數(shù)據(jù)后，直接按下ALT

十F1鍵，快速在工作表中建立圖表，不過(buguò)所建立的圖表類型則是預設(shè)的柱形圖，如果你有自行修改過默認的圖表類型，那么將以你設(shè)定的為主。圖表操作實例2■

福特■

三菱第40頁，共50頁。3繪圖區(qū)格線■一

月■二

月三月圖例圖表標題第一季高級房車銷售量福特

寶士

裕隆

三菱X軸16001400120010008006004002000圖表操作(cāozuò)實

例

3圖表區(qū)Y軸第41頁，共50頁。16001400120010008006004002000第一季高級房車銷售量福特

寶士裕隆

三菱圖表操作實例4圖表區(qū)：指整個圖表及所涵蓋的所有項目(xiàngmù)。繪圖區(qū)：指圖表顯示的區(qū)域，包含圖形本身、類別名稱、坐標軸等區(qū)域?！?/p>

一

月■二

月三

月繪圖區(qū)第42頁，共50頁。第一季高級房車銷售量16001400120010008006004002000福特

寶士

裕隆三菱圖表操作實例5圖例：辨識圖表中各組數(shù)據(jù)系列(xiliè)的說明。圖例內(nèi)還包括圖例項標示、圖

例

項

目

。圖例項目是指與圖例符號對應的資

料數(shù)列名稱圖例項標示代表數(shù)據(jù)系列的圖樣第43頁，共50頁。格線第一季高級房車銷售量1600140012001000■二月三月4002000福特寶士

裕隆

三菱圖表操作實例6座標軸：平面圖表通常有兩個(liǎng

gè)坐標軸：X

軸和Y軸；立體圖表上則有2個坐標軸：X-Y

和Z軸。網(wǎng)格線：由坐標軸的刻度記號向上或向右延伸到整個繪圖區(qū)的直線。顯示網(wǎng)格線比較容易察看圖表上數(shù)據(jù)點的實際數(shù)值。Y軸通常是垂直

軸，包