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

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