《公式與函數(shù)》PPT課件.ppt

3 公式與函數(shù),在單元格建立公式 相對(duì)參照位址與絕對(duì)參照位址 函數(shù)的使用方法 插入函數(shù)的綜合練習(xí)-獎(jiǎng)金發(fā)放作業(yè) 不需要公式也能得知計(jì)算結(jié)果,3-1 在單元格建立公式,當(dāng)我們需要將工作表中的數(shù)字?jǐn)?shù)據(jù)做加、減、乘、除等運(yùn)算時(shí)，可以把計(jì)算的動(dòng)作交給Excel的公式去做，省去自行運(yùn)算的工夫，而且當(dāng)數(shù)據(jù)有變動(dòng)時(shí)，公式計(jì)算的結(jié)果還會(huì)立即更新。,3-1-1 公式的表示法,Excel的公式和一般數(shù)學(xué)公式差不多，數(shù)學(xué)公式的表示法為：,若將這個(gè)公式改用Excel表示，則變成要在A3單元格中輸入：,意思是Excel會(huì)將Al單元格的值十A2單元格的值，然后把結(jié)果顯示在A3單元格中。,3-1-2 輸入公式,輸入公式必須以等號(hào)“=”起首，例如= Al+A2，這樣Excel才知道我們輸入的是公式，而不是一般的文字?jǐn)?shù)據(jù)?，F(xiàn)在我們就來練習(xí)建立公式，請(qǐng)開殷范例文件Ch03-01，我們已在其中輸入了兩個(gè)學(xué)生的成績：,我們打算在E2單元格存放“王書桓的各科總分”，也就是要將“王書桓”的英文、生物、 理化分?jǐn)?shù)加總起來，放到E2單元格中，因此將E2單元格的公式設(shè)計(jì)為“=B2十C2+D2”。,請(qǐng)選定要輸入公式的E2單元格，并將指針移到數(shù)據(jù)編輯列中輸入等號(hào)”=”：,函數(shù)方塊,在此輸入“”,接著輸入“=”之后的公式，請(qǐng)?jiān)趩卧馚2上單擊，Excel便會(huì)將B2輸入到數(shù)據(jù)編輯列中：,此時(shí)B2被虛線框包圍住,B2自動(dòng)輸入到公式中,再輸入，“+“，然后選取C2單元格，繼續(xù)輸入，“+“，選取D2單元格，如此公式的內(nèi)容便輸入完成了：,最后按下數(shù)據(jù)編輯列上的翰入鈕 或按下 鍵，公式計(jì)算的結(jié)果馬上顯示在E2單元格中：,數(shù)據(jù)編輯列會(huì)顯示公式,單元格顯示公式計(jì)算的結(jié)果,Tip:若想直接在單元格中查看公式，可按下 + 鍵 鍵在 鍵的上方，在公式和計(jì)算結(jié)果間做切換。,你也可以直接在E2儲(chǔ)存格中，以鍵盤直接輸入“=82十C2十D2”、再按下Enter鍵來輸入公式,3-1-3 自動(dòng)更新結(jié)果,公式的計(jì)算結(jié)果會(huì)隨著單元格內(nèi)容的變動(dòng)而自動(dòng)更新。以上例來說，假設(shè)當(dāng)公式建好以后，才發(fā)現(xiàn)”王書桓”的英文成績打錯(cuò)了，應(yīng)該是”90”分才對(duì)，當(dāng)我們將單元格B2的值改成”90”，E2單元格中的計(jì)算結(jié)果立即從220更新為225：,自動(dòng)更新計(jì)算結(jié)果了,3-2 相對(duì)參照位址與絕對(duì)參照位址,3-2-1 相對(duì)與絕對(duì)參照的差異,公式中會(huì)運(yùn)用到的地址有相對(duì)引用地址與絕對(duì)參照地址2種類型。相對(duì)引用地址的表示法例如：Bl、 C4；而絕對(duì)參照地址的表示法，則須在單元格地址前面加上”$”符號(hào)，例如：$B$l、$C$4。,假設(shè)你要前往某地，但不知道該怎么走，于是就向路人打聽。結(jié)果得知你現(xiàn)在的位置往前走，碰到第一個(gè)紅綠燈后右轉(zhuǎn)，再直走約100公尺就到了，這就是相對(duì)引用地址的概念。 另外有人干脆將實(shí)際地址告訴你，假設(shè)為”中正路二段60號(hào)”，這就是絕對(duì)參照地址的概念，由于地址具有唯一性，所以不論你在什么地方，根據(jù)這個(gè)絕對(duì)參照地址，所找到的永遠(yuǎn)是同一個(gè)地點(diǎn)。 將這兩者的特性套用在公式上，代表相對(duì)引用地址會(huì)隨著公式的位置而改變，而絕對(duì)參照位址則不管公式在什么地方，它永遠(yuǎn)指向同一個(gè)單元格。,3-2-2 實(shí)例說明,底下我們以實(shí)例為你說明相對(duì)引用地址與絕對(duì)參照地址的使用方式。先選取D2單元格，在其中輸入公式”=B2十C2”并計(jì)算出結(jié)果，根據(jù)前面的說明，這是相對(duì)引用地址。以下我們要在D3單元格輸入絕對(duì)參照地址的公式=$B$3+$C$2。,選取D3單元格，然后在數(shù)據(jù)編輯列中輸入”=B3”。,按下 鍵，B3會(huì)切換成$B$3的絕對(duì)參照地址：,你也可以直接在資料編輯列中輸入“=$B$3“。,切換相對(duì)引用與絕對(duì)參照地址的快速鍵,鍵可循序切換單元格地址的參照類型，每 按一次 鍵，參照地址的類型就會(huì)改變，其 切換結(jié)果如右：,接著輸入“+ C3”，再按下F4鍵將C3變成$C$3，最后按下 鍵，公式就建立完成了：,D3的公式內(nèi)容,D2及D3的公式分別是由相對(duì)地址與絕對(duì)地址組成，但兩者的計(jì)算結(jié)果卻一樣。到底它們差別在哪里呢？選定D2：D3單元格，拉曳填滿控點(diǎn)到下一欄，將公式復(fù)制到E2：E3儲(chǔ)存格：,計(jì)算結(jié)果不同了,相對(duì)地址公式,D2的公式=B2+C2，使用了相對(duì) 位址，表示要計(jì)算D2往左找兩個(gè)儲(chǔ)存 格(B2、C2)的總和，因此當(dāng)公式復(fù)制 到E2單元格后，便改成從E2往左找兩 個(gè)儲(chǔ)存格相加，結(jié)果就變成C2和D2相 加的結(jié)果：,絕對(duì)地址公式,D3的公式=$B$3十$C$3，使用了絕 對(duì)位址，因此不管公式復(fù)制到哪里，Excel 都是找出B3和C3的值來相加，所以 D3和E3的結(jié)果都是一樣的：,3-2-3 混合參照,如果在公式中同時(shí)使用相對(duì)引用與絕對(duì)參照，這種情形稱為混合參照，例如：,這種公式在復(fù)制后，絕對(duì)參照的部份(如$Bl的$B)不會(huì)變動(dòng)，而相對(duì)引用的部份則會(huì)隨情況做調(diào)整。,絕對(duì)參照,相對(duì)參照,絕對(duì)參照,相對(duì)參照,我們繼續(xù)沿用范例文件Ch03-02做練習(xí)，請(qǐng)依照下列步驟將E3單元格中的公式改成混合參照公式= $B3+C3：,雙按E3單元格，將插入點(diǎn)移至”=”之后，接著按兩次F4鍵，讓$B$3變成$B3。,將插入點(diǎn)移至“+”之后，按3次F4鍵將$C$3變成C3，最后按下enter鍵，公式便輸入完成。,接著選定F3，分別拉曳填滿控點(diǎn)至F3及E4：,E4單元格,F3單元格,B4單元格為0，C4單元格也為0,B3維持1215，D3為2180,3-3 函數(shù)的使用方法,3-3-1 函數(shù)的格式,函數(shù)是Excel根據(jù)各種需要，預(yù)先設(shè)計(jì)好的運(yùn)算公式，可讓你節(jié)省自行設(shè)計(jì)公式的時(shí)間，底下我們就來看看如何運(yùn)用Excel的函數(shù)。,每個(gè)函數(shù)都包含三個(gè)部份：函數(shù)名稱、自變量和小括號(hào)。我們以加總函數(shù)SUM來說明： SUM即是函數(shù)名稱，從函數(shù)名稱可大略得知函數(shù)的功能、用途。 小括號(hào)用來括住自變量，有些函數(shù)雖沒有自變量，但小括號(hào)還是不可以省略。 引敷是函數(shù)計(jì)算時(shí)所必須使用的數(shù)據(jù)，例如SUM(1，3，5)即表示要計(jì)算1-3-5三個(gè)數(shù)字的總 和，其中的1.3.5就是自變量。,自變量的數(shù)據(jù)類型,函數(shù)的自變量不僅只有數(shù)字類型而已，也可以是文字或以下3項(xiàng)類別： 位址：如SUM (B1，C3)即是要計(jì)算B1單元格的值十C3單元格的值。 范圍：如SUM (A1：A4)即是要加總Al：A4范圍的值。 函數(shù)：如SQRT (SUM(B1：B4即是先求出B1：B4的總和后，再開平方根的結(jié)果。,3-3-2 使用函數(shù)方塊輸入函數(shù) 函數(shù)也是公式的一種，所以輸入函數(shù)時(shí)，也必須以等號(hào)“=”起首，假設(shè)我們要在B8單元格運(yùn)用SUM函數(shù)來計(jì)算班費(fèi)的總支出。,首先選取存放計(jì)算結(jié)果的 B8 儲(chǔ)存 格，并在數(shù)據(jù)編輯列中數(shù)入等號(hào)”=”,接著按下函數(shù)方塊右側(cè)的下拉鈕，在列 示窗中選取SUM，此時(shí)會(huì)開啟函數(shù)引 數(shù)交談窗來協(xié)助我們輸入函數(shù)：,選取SUM函數(shù),若按下此處，可取得函數(shù)的進(jìn)階說明,TIP：函數(shù)方塊列示窗只會(huì)顯示最近用過的10個(gè)函數(shù)，若在函數(shù)方塊列示窗中找不到想要的函數(shù)，可選取其他函數(shù)項(xiàng)目開啟插入函數(shù)交談窗來尋找欲使用的函數(shù)（稍后說明）。,這里會(huì)描述此函數(shù)的功能,再來就是要設(shè)定函數(shù)的自變量。先按下第一個(gè)自變量欄Number 1右側(cè)的折迭鈕 ，將函數(shù)自變量 交談窗收起來，再從工作表中選取B4：B6當(dāng)作自變量：,按下自變量欄右側(cè)的展開鈕 ，再度將函數(shù)自變量交談窗展開：,選取B4：B6當(dāng)作自變量,TIP：除了從工作表中選取單元格來設(shè)定自變量，你也可以直接在自變量欄中輸入自變量，省下折迭、展開函數(shù)引，數(shù)交談窗的痲煩。,選取的范圍會(huì) 被虛線框圍住,函數(shù)自變量交談窗 目前被折迭起來,這里會(huì)顯示計(jì) 算的結(jié)果,按下確定鈕，函數(shù)的計(jì)算結(jié)果就會(huì)顯示在B8單元格內(nèi)：,剛才輸入的 函數(shù)及公式,計(jì)算結(jié)果,3-3-3 用自動(dòng)顯示的函數(shù)列表輸入函數(shù) 若已經(jīng)知道要使用哪一個(gè)函數(shù)，或是函數(shù)的名稱很長，我們還有更方便的輸入方法。請(qǐng)直接在單元格內(nèi)輸入”=”再輸入函數(shù)的第1個(gè)字母，例如”S”，單元格下方就會(huì)列出S開頭的函數(shù)，如果還沒出現(xiàn)要用的函數(shù)，再繼續(xù)輸入第2個(gè)字母，例如”U”，出現(xiàn)要用的函數(shù)后，用鼠標(biāo)雙按函數(shù)就會(huì)自動(dòng)輸入單元格了：,移動(dòng)箭頭鍵到函數(shù)上，會(huì)顯示函數(shù)的說明,雙按函數(shù)可自動(dòng)輸入單元格,TIP:若一直沒有出現(xiàn)函數(shù)列表，請(qǐng)切換到文件頁次再按下選項(xiàng)鈕，然后切換到公式頁次，確認(rèn)已勾選公式自動(dòng)完成選項(xiàng)。,3-3-3 利用“自動(dòng)求和”鈕快速輸入函數(shù),在開始頁次編輯區(qū)有一個(gè)自動(dòng)加按鈕 ，可讓我們快速輸入函數(shù)。例如當(dāng)我們選取B8單元格，并按下 鈕時(shí)，便會(huì)自動(dòng)插入SUM函數(shù)，且連自變量都自動(dòng)幫我們設(shè)定好了：,只要按下enter鍵，就可 以算出班費(fèi)的總支出了,會(huì)自勤選取好函數(shù)引 數(shù)，你也可以自行重新 選取其他范圍,這里會(huì)有函數(shù)的輸入格式提示,除了加總功能之外，還提供數(shù)種常用的函數(shù)供我們選擇使用，只要按下 鈕旁邊的下拉鈕 ，即可選擇要進(jìn)行的計(jì)算：,可用來做這些 運(yùn)算,若選此項(xiàng)，會(huì) 開啟插入函敷 交談窗,3-3-4 開啟“插入函數(shù)”交談窗輸入需要的函數(shù),插入函數(shù)交談窗是Excel函數(shù)的大本營，當(dāng)你在函數(shù)方塊列示窗中找不到需要的函數(shù)時(shí)，就可從這里來輸入函數(shù)?，F(xiàn)在我們要練習(xí)透過插入函數(shù)交談窗來輸入函數(shù)，列出面包店各家門市的營業(yè)額排名。,請(qǐng)選取C4單元格，然后按下數(shù)據(jù)編輯列上的插入函數(shù)鈕，你會(huì)發(fā)現(xiàn)數(shù)據(jù)編輯列自動(dòng)輸入等號(hào)”=”，并且開殷插入函數(shù)交談窗：,可從這里選擇函數(shù)的類別，如財(cái) 務(wù)、統(tǒng)計(jì)、文字、日期及時(shí)間等,列出Excel所提供的函數(shù),按下此處可顯示目前所選取函數(shù)的使用說明,函數(shù)的功能敘述,接著從插入函數(shù)交談窗中選取RANK.EQ函數(shù)，進(jìn)行門市營業(yè)額的排名：,1、選擇統(tǒng)計(jì)類別,若不知道Excel是否提供你所要的函數(shù)，也可在此欄 輸入中、英文關(guān)鍵詞，再按下右側(cè)的開始鈕進(jìn)行搜尋,2、選取此類別下的 RANK.EO函數(shù),3按下確定鈕，開啟 函數(shù)引數(shù)交談窗,開啟函數(shù)自變量交談窗后，如下圖輸入各自變量的內(nèi)容：,在此輸入B4,2輸入要進(jìn)行排名的單元格范圍（在此我們要排名的范圍 是固定在B4：813之間，所以使用絕對(duì)參照地址）,按下確定鈕即可得到計(jì)算 結(jié)果。,計(jì)算出，惠城精城門市的營業(yè)額是所有門市的第3名,請(qǐng)選取C4單元格，并拉 曳其填滿控點(diǎn)到C13儲(chǔ) 存格。,計(jì)算出所有門市 的營業(yè)額排名,若想變更自變量設(shè)定，請(qǐng)選取函數(shù)所在的單元格，然后按下插入函數(shù)鈕 展開函數(shù)自變量交談窗來重新設(shè)定,RANK.EQ與RANK.AVG函數(shù)的差異,用來算排名的函數(shù)有RANK.EO和RANK.AVG兩個(gè)，而在Excel 2007（或之前）版本則只有RANK函數(shù)。,這3個(gè)函數(shù)都可用來排序，RANK.AVG和RANK.EQ的差異是在遇到相同數(shù)值時(shí)的處理方法不同，RANK.AVG會(huì)傳回等級(jí)的平均值，RANKEQ則會(huì)傳回最高等級(jí)；RANK則是Excel2007之前版本的函數(shù)，在Excel2010仍可使用，其作用與RANK.EQ相同。,傳回3、4的 平均等級(jí)3.5,傳回最高等級(jí)，所以 會(huì)有2個(gè)3，沒有4,結(jié)果與RANK. EQ相同,3-4 函數(shù)綜合練習(xí)-獎(jiǎng)金發(fā)放作業(yè),學(xué)會(huì)插入函數(shù)之后，我們來做個(gè)綜合練習(xí)，讓你對(duì)函數(shù)有更進(jìn)一步的了解。假設(shè)我們要依年資來計(jì)算中秋節(jié)發(fā)放的獎(jiǎng)金，年資未滿1年的員工沒有獎(jiǎng)金，滿1年以上未滿3年則發(fā)放5，000元獎(jiǎng)金，滿3年以上則發(fā)給8，000元獎(jiǎng)金。 如下圖所示：,首先要進(jìn)行年資的計(jì)算，請(qǐng)先選取D2儲(chǔ)存格，在此要使用DATEDIF這倜函數(shù)來計(jì)算兩個(gè)日期之間的年數(shù)、月數(shù)或天數(shù)，其格式如下： DATEIF(開始日期，結(jié)束日期，差距參數(shù)單位）,到職日,獎(jiǎng)金基數(shù)基準(zhǔn)日（我們以這個(gè)日期為基準(zhǔn)，所以使用絕對(duì)參照位址）,求算兩日期差距的整年數(shù)要使用“Y”參數(shù),在D2單元格中輸入“=DATEDIF(B2,$C$2,”Y“)”， 然后按Enter,DATEIF的差距單位參數(shù),計(jì)算出”蔣文文”的年資后，請(qǐng)選取D2儲(chǔ)存格，然后拉曳填滿控黠至Dll儲(chǔ)存格，即可算出所有員工的年資。,年資為0表示未滿一年,計(jì)算出”蔣文文”的年資后，請(qǐng)選取D2儲(chǔ)存格，然后拉曳填滿控黠至Dll儲(chǔ)存格，即可算出所有員工的年資。計(jì)算出年資後，就可以依年資來計(jì)算獎(jiǎng)金，我們要用IF函數(shù)來判斷應(yīng)發(fā)放的獎(jiǎng)金。IF函數(shù)可判斷條件是否成立，如果所傳回的值為TRUE時(shí)，就執(zhí)行條件成立時(shí)的作業(yè)，反之則執(zhí)行條件不成立時(shí)的作業(yè)。,IF(判讀式，條件成立時(shí)的作業(yè)，條件不成立時(shí)的作業(yè)）,當(dāng)年資小于1年的條件成立，沒有獎(jiǎng)金,當(dāng)年資小于3年的條件成立，獎(jiǎng)金為5000；若條件不成立，就是滿3年以上，獎(jiǎng)金為8000,在E2單元格中輸入” =IF(D21,0,IF(D23,5000,8000)”,就是好蔣文文的獎(jiǎng)金后，請(qǐng)將E2單元格的填滿控點(diǎn)拉曳到E11單元格，即可算出所有人的獎(jiǎng)金。,3-5 不需輸入公式也能得知計(jì)算結(jié)果,這一節(jié)要介紹一個(gè)超實(shí)用的自動(dòng)計(jì)算功能，