微軟Excel 2010中文官方培訓(xùn)課件-了解 Excel 2010：創(chuàng)建公式-2

有關(guān)單元格引用和公式的更多信息指向視頻可以顯示視頻控件。沿進(jìn)度欄拖動(dòng)或單擊進(jìn)度欄可以向前或向后移動(dòng)。我在單元格B7中創(chuàng)立了一個(gè)公式，以獲得B列中所有數(shù)字的總和。我還想獲得C列中數(shù)字的總和。為此，我可以創(chuàng)立另一個(gè)公式，也可以將現(xiàn)有公式復(fù)制到C列。在單元格B7中單擊，移動(dòng)光標(biāo)直至其變?yōu)橐粋€(gè)黑色加號(hào)，按下鼠標(biāo)左鍵并拖動(dòng)到單元格C7，釋放鼠標(biāo)按鈕，此時(shí)C列中將顯示總和。看一下這兩個(gè)公式，以確定復(fù)制公式時(shí)發(fā)生的事情。在B7中單擊并查看編輯欄，您可以看到單元格引用為“B2:B6〞，它表示包括B列中的所有數(shù)字。在單元格C7中單擊并查看編輯欄，您可以看到B2變成了C2，而B(niǎo)6變成了C6。現(xiàn)在，公式包含C列中的所有數(shù)字。在將公式從該單元格復(fù)制到該單元格時(shí)，單元格引用會(huì)自動(dòng)發(fā)生更改以反映公式的新位置。復(fù)制公式時(shí)，如果單元格引用發(fā)生更改，那么這種單元格引用稱為相對(duì)單元格引用。您不必了解該術(shù)語(yǔ)，這只是它的名稱。在繼續(xù)下一局部之前，我還想獲得每名銷售人員在第一周和第二周的總銷售額。在D2中單擊，上移到“編輯〞區(qū)域，并單擊“自動(dòng)求和〞按鈕，然后按Enter，此時(shí)將顯示總和，移動(dòng)光標(biāo)直至其變?yōu)楹谏犹?hào)并向下拖動(dòng)，現(xiàn)在即會(huì)得到每個(gè)人在這兩周的總工作業(yè)績(jī)。順便說(shuō)一下，拖放公式非常有用，但您應(yīng)該知道，還可以將公式從一個(gè)位置復(fù)制并粘貼到其他位置。如果要將公式從一個(gè)單元格復(fù)制到并非緊鄰的單元格，那么這樣做會(huì)很方便。我將舉例說(shuō)明。刪除此公式，在此處單擊，單擊右鍵，并單擊“復(fù)制〞。然后，在希望應(yīng)用公式的位置單擊，并單擊右鍵，將顯示一些粘貼選項(xiàng)，這是我需要的“公式〞選項(xiàng)，因?yàn)槲乙迟N公式。單擊此選項(xiàng)，將再次顯示C列的總和，要消除單元格B7四周的點(diǎn)線框，可以按Esc。接下來(lái)，您將看到一個(gè)公式，在將該公式從一個(gè)單元格復(fù)制到其他單元格時(shí)，公式中的單元格引用不發(fā)生更改。每名銷售人員每?jī)芍芫涂梢詮钠鋬芍艿墓ぷ鳂I(yè)績(jī)中獲得3%的傭金。我將創(chuàng)立一個(gè)公式，該公式可用于計(jì)算每個(gè)人的傭金。在單元格F2中鍵入比率3%，它可以位于任何單元格中，這只是我隨便選擇的單元格，在此處鍵入公式，“=〞，我希望雅玲的總和顯示在公式中，并且我希望總和乘以傭金比率，這是傭金比率，所以單擊此處，然后按Enter，傭金額為“￥43.45〞?，F(xiàn)在，我要復(fù)制該公式，得到的結(jié)果可能會(huì)讓您大吃一驚。向下復(fù)制，您會(huì)看到其他每個(gè)人的傭金額為零。這顯然不正確。他們應(yīng)該得到傭金。我們看一下公式，找出發(fā)生這種情況的原因。如果在表示雅玲的傭金的E2中單擊，我們會(huì)看到“D2*F2〞，這非常正確，這是D2，這是乘數(shù)F2。但是，如果我單擊此處并查看編輯欄，可以看到它顯示“D3*F3〞。單元格F3中沒(méi)有任何內(nèi)容，這就是我們得到零結(jié)果的原因。當(dāng)然，因?yàn)閺?fù)制時(shí)單元格引用會(huì)更改，所以向下復(fù)制時(shí)此處的F2會(huì)變?yōu)镕3，接著再往下看，會(huì)發(fā)現(xiàn)它已變?yōu)镕4。接下來(lái)是一個(gè)例如，它與單元格引用可以自動(dòng)更改同樣重要，在該例如中我們不希望單元格引用發(fā)生更改。我將演示如何實(shí)現(xiàn)這種情況。刪除這些內(nèi)容。實(shí)際上是刪除整個(gè)公式，并重新開(kāi)始。鍵入等號(hào)，單擊此處以再次引用D2，并鍵入乘號(hào)。單擊此處，引用傭金比率。但是，這次我要將單元格引用從復(fù)制時(shí)會(huì)發(fā)生更改的相對(duì)單元格引用更改為始終指向單元格F2的絕對(duì)單元格引用。絕對(duì)單元格引用看起來(lái)不同于相對(duì)單元格引用，可以按F4鍵進(jìn)行這種更改。現(xiàn)在，可能看起來(lái)有點(diǎn)奇怪，至少對(duì)我來(lái)說(shuō)確實(shí)如此。將顯示“$F$2〞。這意味著該公式將始終指向單元格F2。順便說(shuō)一下，如果忘記了鍵盤(pán)快捷方式F4，那么始終可以自己鍵入美元符號(hào)。美元符號(hào)始終位于列前面，即“$F〞；并且始終位于行前面，即“$2〞。按Enter，顯示與先前相同的傭金額，但這次當(dāng)我向下復(fù)制公式時(shí)，每個(gè)人都會(huì)擁有自己的銷售傭金。如果我在此單元格中單擊并查看，我們可以看到“D3〞，還有絕對(duì)引用，在這里它指向單元格“F2〞。單擊此處，同樣，相對(duì)引用從“D3〞更改為“D4〞，但絕對(duì)引用仍指向單元格“F2〞。接下來(lái)，我要介紹另一公式例如，在該例如中，單元格引用是相對(duì)引用和絕對(duì)引用的組合。我希望生成一張表，該表將顯示以銷售人員的銷售額為根底的一些內(nèi)容，并顯示這些人員的銷售傭金，所得結(jié)果取決于傭金比率。為此，我需要?jiǎng)?chuàng)立一個(gè)公式，該公式包含稱為混合引用的單元格引用，即局部單元格引用為絕對(duì)引用，局部引用為相對(duì)引用。理解這一點(diǎn)對(duì)我來(lái)說(shuō)有點(diǎn)困難，但我最終弄明白了，我希望通過(guò)我的講解您可以更好地理解它。在類似于傭金比率表或稅率表之類的表中，通常使用混合單元格引用。單擊此處，我要在該位置生成第一個(gè)公式，您要考慮的問(wèn)題是您是否要生成這樣一張表，您是否希望公式始終指向的內(nèi)容是絕對(duì)引用？我始終希望指向B列，這一列是銷售額，我始終希望公式指向第2行，這一行是傭金比率。繼續(xù)操作，鍵入公式，等號(hào)，單擊此處以在公式中引用￥100,000?，F(xiàn)在，我希望使B列成為絕對(duì)引用，因此按F4鍵，這會(huì)使B3成為絕對(duì)引用，這并不是我要的結(jié)果，我只希望列成為絕對(duì)引用。再次按F4鍵，這次變?yōu)椤?3〞，這意味著行將成為絕對(duì)引用，這也不是我要的結(jié)果，因此我最后一次按F4，我獲得了指向B列的絕對(duì)引用和指向第3行的相對(duì)引用，這正是我要的結(jié)果。公式始終指向B列，但是當(dāng)我向下復(fù)制時(shí)相對(duì)局部3將更改為顯示第4行和第5行，依此類推?，F(xiàn)在，我將通過(guò)乘法運(yùn)算獲得銷售傭金，我不能立即看到銷售傭金，它尚未清晰地顯示出來(lái)，繼續(xù)操作，鍵入“C2〞。因?yàn)槲蚁Ｍ冀K引用第2行，所以按F4鍵，所有內(nèi)容都成為絕對(duì)引用，這不是我要的結(jié)果，再次按F4，獲得的正是我要的結(jié)果，即指向C列的相對(duì)引用，復(fù)制到D列和E列，依此類推，但絕對(duì)引用始終指向第2行。按Enter。如果銷售額為￥100,000，傭金比率為1%，那么獲得的結(jié)果為￥1,000。將此公式復(fù)制到鄰近的單元格，將獲得正確的結(jié)果。如果傭金比率為5%，那么獲得的結(jié)果為￥5,000。選擇這些單元格，因?yàn)槲乙诒碇袑⒐綇念^至尾向下復(fù)制，釋放鼠標(biāo)按鈕，現(xiàn)在，我完成了這張表。如果在任一單元格中單擊，比方在F10中。如果我銷售了￥800,000并且傭金比率為4%，那么傭金額為￥32,000，如果查看編輯欄，我們可以看到，絕對(duì)引用指向B列，并且我們位于第10行中，乘以，我們正好位于F列中，而我們?nèi)允构街赶虻?行。單擊此處，這里的銷售額為￥400,000，傭金比率為1%，傭金額為￥4,000。查看編輯欄，我們可以看到，我們指向B列，正好位于第6行中，乘以，又正好位于C列中，并且絕對(duì)引用指向第2行。接下來(lái)，您將了解其他一些函數(shù)。有關(guān)函數(shù)的更多信息指向視頻可以顯示視頻控件。沿進(jìn)度欄拖動(dòng)或單擊進(jìn)度欄可以向前或向后移動(dòng)。Excel具有很多函數(shù)或預(yù)定義公式，您可以使用它們執(zhí)行各種計(jì)算。在本課程前面的內(nèi)容中，我演示了如何使用“開(kāi)始〞選項(xiàng)卡上的“編輯〞組中的“求和〞函數(shù)。這是“自動(dòng)求和〞按鈕，如果單擊該按鈕，您可以看到“求和〞和“平均值〞函數(shù)。此外，還有用于計(jì)數(shù)以及定義最大值或最小值的函數(shù)，您還可以使用其他很多函數(shù)。我準(zhǔn)備單擊“公式〞選項(xiàng)卡，這是“函數(shù)庫(kù)〞，請(qǐng)注意，此處也提供“自動(dòng)求和〞按鈕，還包含“財(cái)務(wù)〞、“邏輯〞、“文本〞、“日期和時(shí)間〞等函數(shù)。這些種類繁多的函數(shù)使您能夠了解可以使用函數(shù)執(zhí)行的一些操作。如果您知道函數(shù)的名稱，那么可以在庫(kù)中查找該函數(shù)，但更重要的是，如果您不知道函數(shù)的名稱，那么可以單擊這個(gè)“插入函數(shù)〞按鈕。我要查找這樣的函數(shù)：我想計(jì)算購(gòu)置的復(fù)印機(jī)的還款額。將在此處翻開(kāi)“插入函數(shù)〞框，我在其中鍵入“付款〞，然后單擊“轉(zhuǎn)到〞。此處列表中的第一個(gè)函數(shù)是“PMT〞，其描述顯示在函數(shù)列表的下方：計(jì)算在固定利率下，貸款的等額分期歸還額。這看起來(lái)就是我需要的函數(shù)，因此我單擊“確定〞。將翻開(kāi)“函數(shù)參數(shù)〞對(duì)話框，需要在此對(duì)話框中輸入公式的“參數(shù)〞。對(duì)于“PMT〞函數(shù)，有三個(gè)必需的參數(shù)，分別為“Rate〞、“Nper〞和“Pv〞?？赡芎茈y理解這些參數(shù)到底表示什么，但很快您就會(huì)明白它們的含義。這兩個(gè)參數(shù)“Fv〞和“Type〞，它們不是必需的。在“Rate〞框中單擊，下面顯示：“Rate各期利率。例如，當(dāng)利率為6%時(shí)，使用6%/4計(jì)算一個(gè)季度的還款額〞。購(gòu)置復(fù)印機(jī)的利率為“3.5%/〞，因?yàn)槲颐吭逻€款一次，所以鍵入“12〞。然后，將插入點(diǎn)移到“Nper〞框中?！癗per〞的描述為“總投資期或貸款期，即該項(xiàng)投資或貸款的付款期總數(shù)〞。我要在3年內(nèi)還清，因此這段時(shí)間內(nèi)的付款期為“36〞。然后，將插入點(diǎn)置于“Pv〞框中?！癙v〞是“從該項(xiàng)投資(或貸款)開(kāi)始計(jì)算時(shí)已經(jīng)入賬的款項(xiàng)，或一系列未來(lái)付款當(dāng)前值的累積和〞。因此貸款總額為“3500〞元，鍵入該值，然后單擊“確定〞。結(jié)果為“(￥-102.56)〞，即每月還款額。還款額的格式設(shè)置為紅色并且括在圓括號(hào)內(nèi)，因?yàn)檫@是設(shè)置財(cái)務(wù)函數(shù)格式的方式。接下來(lái)，我將演示如何使用另一種方式輸入同一函數(shù)。如果您知道函數(shù)的名稱，那么無(wú)需使用“插入函數(shù)〞按鈕。我將再次使用“PMT〞函數(shù)，但這次我要計(jì)算住房抵押的每月還款額。在空白單元格中單擊，鍵入等號(hào)，然后鍵入“P〞表示還款，您將看到公式記憶式鍵入。此處顯示一個(gè)以“P〞開(kāi)頭的函數(shù)列表，這是“PMT〞，如果不確定它是否是所要查找的函數(shù)，只需查看此處的描述計(jì)算在固定利率下，貸款的等額分期歸還額。這正是我要查找的函數(shù)?？梢岳^續(xù)鍵入該函數(shù)，但也可以僅僅雙擊它就自動(dòng)鍵入該函數(shù)和起始圓括號(hào)，參數(shù)在下面列出?，F(xiàn)在，如果我不確定，實(shí)在想不起來(lái)有哪些參數(shù)，我可以單擊此處的“PMT〞，這樣會(huì)獲得一個(gè)描述參數(shù)的幫助主題?，F(xiàn)在，第一個(gè)參數(shù)是“rate〞，即利率，它為“5%〞，我用該值除以“12〞，因?yàn)槲覍⒎?2次還款，每月還款一次。自己鍵入此公式時(shí)，您需要自己分隔參數(shù)，因此，我鍵入逗號(hào)進(jìn)行分隔?！癗per〞是下一個(gè)參數(shù)〔它是30年內(nèi)的付款期數(shù)〕。鍵入“30〞表示30年，乘以“12〞，因?yàn)槲覍⒎?2次還款，每月還款一次。鍵入逗號(hào)以分隔“Nper〞和下一個(gè)參數(shù)“Pv〞，即當(dāng)前值。它是貸款額的當(dāng)前值，即“180000〞。您會(huì)發(fā)現(xiàn)后兩個(gè)參數(shù)“[fv]〞和“[type]〞括在方括號(hào)內(nèi)，這意味著它們不是公式必需的。輸入結(jié)束圓括號(hào)并按Enter，獲得的結(jié)果是每月抵押還款額，即“(￥966.28)〞。接下來(lái)，我將演示一個(gè)可用于處理文本的函數(shù)。您不僅能夠使用函數(shù)處理數(shù)字，還可以使用函數(shù)處理文本。例如，這是一張員工姓名列表，它們的格式非?；靵y；大寫(xiě)字母普及各個(gè)位置，我希望只有每個(gè)姓名的第一個(gè)字母大寫(xiě)，而其余字母均為小寫(xiě)。將插入點(diǎn)置于單元格B2中，我希望公式位于Nancy的姓名旁邊。然后，單擊“插入函數(shù)〞來(lái)搜索函數(shù)。在“搜索函數(shù)〞列表中，我鍵入“propernames〞，然后單擊“轉(zhuǎn)到〞，返回這樣的內(nèi)容。列表中的第一個(gè)函數(shù)稱為“PROPER〞，我看了看它的描述：“將一個(gè)文本字符串中各英文單詞的第一個(gè)字母轉(zhuǎn)換成大寫(xiě)，將其他字符轉(zhuǎn)換成小寫(xiě)〞。這正是我需要的函數(shù)，因此我單擊“確定〞。這樣會(huì)在電子表格中的此位置輸入“PROPER〞，“函數(shù)參數(shù)〞對(duì)話框僅包含一個(gè)參數(shù)“Text〞。我要在公式中使用Nancy的姓名，所以我在單元格A2中單擊，框中將顯示A2，然后單擊“確定〞。這是Nancy的姓名，它已經(jīng)從所有字母都大寫(xiě)更改為正確的姓名大小寫(xiě)。我要復(fù)制公式以便重復(fù)應(yīng)用它。只需選擇公式，按住鼠標(biāo)左鍵并向下拖動(dòng)，然后釋放鼠標(biāo)按鈕，即會(huì)得到大小寫(xiě)正確的姓名列表?，F(xiàn)在，有兩列，而我只需要一列。您會(huì)注意到，如果在Nancy的姓名中單擊，然后查看編輯欄，可以看到姓名后面是公式，我實(shí)際上不需要公式。就像此處一樣，我希望單擊時(shí)在編輯欄中看到的是Andrew的姓名，而不是公式。為此，可選擇列表，單擊右鍵并單擊“復(fù)制〞，以便復(fù)制格式正確的姓名列表。然后，選擇格式混亂的姓名列表，并單擊右鍵，在“粘貼選項(xiàng)〞框中，此處有一個(gè)用于僅粘貼值的選項(xiàng)，這正是我需要的選項(xiàng)，因?yàn)槲也幌Ｍ迟N公式。單擊此選項(xiàng)，并按Esc以消除B列中所有這些姓名周圍的點(diǎn)線框。單擊此處并查看編輯欄，我們可以看到Nancy的姓名，而不是公式。在這里，您仍然可以看到公式。只需刪除此列，因?yàn)槲也辉傩枰恕，F(xiàn)在只剩下一列。本課程到此結(jié)束。有關(guān)練習(xí)的建議鍵入一些簡(jiǎn)單公式進(jìn)行加、減、乘、除運(yùn)算。在同一公式中使用多個(gè)數(shù)學(xué)運(yùn)算符。使用sum函數(shù)將數(shù)字相加。使用sum函數(shù)將列中的兩個(gè)數(shù)字相加。使用相對(duì)單元格引用將公式從一個(gè)單元格復(fù)制到另一個(gè)單元格。有關(guān)練習(xí)的建議，續(xù)使用絕對(duì)單元格引用創(chuàng)立公式。使用混合單元格引用創(chuàng)立公式。使用PMT函數(shù)創(chuàng)立公式。使用PROPER函數(shù)創(chuàng)立公式。聯(lián)機(jī)練習(xí)〔需要Excel2021〕測(cè)驗(yàn)問(wèn)題1Excel中的每個(gè)公式都以等號(hào)開(kāi)頭?！策x擇一個(gè)答案?！痴_。錯(cuò)誤。測(cè)驗(yàn)問(wèn)題1無(wú)論是簡(jiǎn)單還是復(fù)雜的公式，都以等號(hào)開(kāi)頭。Excel中的每個(gè)公式都以等號(hào)開(kāi)頭。答案：正確。測(cè)驗(yàn)問(wèn)題2數(shù)學(xué)運(yùn)算符優(yōu)先級(jí)的第一個(gè)規(guī)那么是什么？〔選擇一個(gè)答案。〕先計(jì)算指數(shù)〔根和冪〕。先計(jì)算除法，再計(jì)算加法。先計(jì)算括號(hào)中的內(nèi)容。測(cè)驗(yàn)問(wèn)題2首先計(jì)算括號(hào)中的內(nèi)容。然后依次計(jì)算指數(shù)、乘除〔從左到右〕，最后是計(jì)算加減〔從左到右〕。數(shù)學(xué)運(yùn)算符優(yōu)先級(jí)的第一個(gè)規(guī)那么是什么？答案：先計(jì)算括號(hào)中的內(nèi)容。測(cè)驗(yàn)問(wèn)題3Excel將首先計(jì)算此算術(shù)問(wèn)題的哪一局部：=30/5*3？〔選擇一個(gè)答案?！诚扔?jì)算30/5。先計(jì)算乘法5*3。測(cè)驗(yàn)問(wèn)題3先計(jì)算30/5，將得到6*3，最終值為18。Excel將首先計(jì)算此算術(shù)問(wèn)題的哪一局部：=30/5*3？答案：先計(jì)算30/5。測(cè)驗(yàn)問(wèn)題4如果您要將位于單元格C1和C2中的值〔93和14〕相加，為何在公式中使用單元格引用(=C1+C2)而不是直接將公式寫(xiě)為：=93+14？〔選擇一個(gè)答案。〕公式看起來(lái)更為專業(yè)。公式更加準(zhǔn)確。公式結(jié)果將在單元格值更改時(shí)自動(dòng)更新。測(cè)驗(yàn)問(wèn)題4您可更新單元格值而無(wú)需更改公式。當(dāng)每個(gè)單元格值發(fā)生更改時(shí)，公式結(jié)果將自動(dòng)更新。如果您在公式中鍵入值自身，那么必須在每次更改值時(shí)修改公式。如果您要將位于單元格C1和C2中的值〔93和14