函數(shù)基礎(chǔ)-基礎(chǔ)概念篇

掌握核心概念，突破菜鳥瓶頸！Excel函數(shù)基礎(chǔ)了解函數(shù)原理，掌握學(xué)習(xí)方法！提升辦公效率，體會學(xué)習(xí)樂趣！引言這不是一個介紹excel基礎(chǔ)知識的資料，它僅僅為一些想學(xué)習(xí)excel函數(shù)應(yīng)用卻又難以入門的朋友提供一些幫助；我們假定你已經(jīng)有了一些excel的使用經(jīng)驗，例如新建一個表格并能夠簡單的排版，同時你也見過一些公式以及函數(shù)，盡管你可能不明白它們的作用和含義；你的工作需要經(jīng)常使用表格人并且會遇到數(shù)據(jù)統(tǒng)計之類的任務(wù)，或者見過別人使用公式和函數(shù)自己很有興趣想去了解；那么你可以每天抽出一點時間去了解基礎(chǔ)知識，剛開始的節(jié)奏也許會比較慢，正如蓋一座大廈，花在打地基的時間會比較多一樣，只有基礎(chǔ)牢固了，以后才能有更多的進步；每一頁內(nèi)容希望可以做到逐字琢磨，并加以理解和實踐，看完每一章內(nèi)容也許只需要花費一兩個小時，但是掌握每一章內(nèi)容可能需要一到兩天甚至更久，第一次不必理解所有的文字，掌握每篇重點即可，所謂循序漸進；請關(guān)注每章的重點并保證有充足的練習(xí)時間；當(dāng)你讀完全部內(nèi)容后可以重新再看一次，相信會有新的收獲，所謂溫故知新！每頁最后會有一句話總結(jié)重點內(nèi)容，頁面下方有一個Excel技巧供學(xué)習(xí)積累；部分頁面的內(nèi)容對于功力尚淺的朋友來說可以看起來會有不適感，常見癥狀為眩暈、焦慮，如遇此類內(nèi)容立即跳過進入下一頁，并不會對你的學(xué)習(xí)有任何不良后果，可待日后功力提升再回頭重新修煉，所謂循序漸進，切勿急于求成！第一篇、學(xué)習(xí)基礎(chǔ)從概念開始本篇主要介紹一些公式中可能涉及到的基本概念；每頁說明一個概念，每頁下方或許會有一句話的重點提示，這是務(wù)必要搞清楚并且能夠理解的要點；個別頁面有些提示語，都是一些容易忽視的地方，需要謹記；還有些“成功秘技”之類的語句，僅僅是老鳥個人經(jīng)驗，并非重點；個別概念需要動手實踐，一定不要偷懶，只有動手了、明白了，才是學(xué)會了！不要去背概念，這是老鳥深惡痛絕的，概念背的滾瓜爛熟也未必能懂得其中的要領(lǐng)，一定要在用的過程中去理解；本篇結(jié)束時會有小結(jié)部分總結(jié)重點內(nèi)容；提供部分測試問題的答案；還有在學(xué)習(xí)中可能會遇到的問題解答；閑話打住，下面開始了解第一個概念：什么是公式？公式公式是Excel中以等號開頭的可以得到一個結(jié)果的等式公式以等號(=)開頭，公式中可以包括函數(shù)、運算符、引用和常量。例如：=5+2*3、=Sheet1!$Q$26、=A1>0、=sum(A:A)，等等都是公式。在Excel的公式中乘號（×）用星號（*）代替，除號（÷）用斜杠（/）代替；乘方運算用符號^，（這個符號使用shift和6鍵即可輸入），例如，10的5次方，數(shù)學(xué)式為：105，Excel中寫作：=10^5。小測試：試將以下算式改寫為Excel的公式：算式1：3+(5+20)÷4算式2：10×[(5+7)÷6]算式3：23×（4-2）提示：在Excel公式中，數(shù)學(xué)算式里的中括號和大括號一律用小括號代替，中括號和大括號在Excel公式中有其他的含義，后文會有詳細介紹；公式中的所有符號請在英文輸入模式下完成。技巧分享：Ctrl+;（按著CTRL鍵按一下分號鍵）可以快速輸入當(dāng)天日期本頁重點：公式必須以(=)等號開頭，公式中不一定非得有函數(shù)存在。函數(shù)Excel中存在大量預(yù)設(shè)的函數(shù)，每個函數(shù)在使用不同的參數(shù)后都可以得到各種結(jié)果，例如：=SUM(A:A)，這個公式用到了SUM函數(shù)，可以實現(xiàn)求和功能。技巧分享：在單元格內(nèi)按住Ctrl和shift鍵不松手，再按一下冒號鍵即可輸入當(dāng)前時間?？梢栽贓xcel中點擊fx這個按鈕（左圖上方箭頭所指位置）或者按住shift鍵和f3功能鍵，調(diào)出插入函數(shù)對話框，選擇全部函數(shù)（左圖中間箭頭所指位置），查看Excel的全部內(nèi)置函數(shù)，選擇具體函數(shù)后，查看簡短說明或者點擊函數(shù)幫助（左圖下方箭頭所指位置）了解該函數(shù)的詳細說明。也可以點擊公式選項卡，在函數(shù)庫中選擇函數(shù)，如上圖。函數(shù)的結(jié)構(gòu)：函數(shù)名，一對括號，參數(shù)，逗號；每個函數(shù)的參數(shù)數(shù)量不盡相同，但是兩個參數(shù)之間需要用逗號分開，如果僅有一個參數(shù)就不需要逗號了，也有的函數(shù)是不需要參數(shù)的。例如：=NOW()，這個函數(shù)前面加等號，可以直接構(gòu)成公式，得到當(dāng)前日期和時間，是個不需要參數(shù)的函數(shù)。=SUM(A:A,5)，這個函數(shù)有兩個參數(shù)，A:A和5，兩個參數(shù)之間用逗號分開，公式得到A列所有數(shù)字與5之和。本頁重點：了解如何插入函數(shù)，了解函數(shù)的結(jié)構(gòu)。編輯欄當(dāng)有公式或者自定義格式時，編輯欄看到的和單元格看到的內(nèi)容往往不同，例如上圖中，編輯欄看到的是=NOW()，NOW是一個不需要參數(shù)的函數(shù)，可以得到系統(tǒng)日期和時間；而在單元格看到的是這個公式的結(jié)果，也就是說，編輯欄里看到的是真實的內(nèi)容，單元格看到的是最終顯示的內(nèi)容。技巧分享：Ctrl+9（按著CTRL鍵按一下數(shù)字9）可以快速隱藏選定的行。菜鳥成功秘技：在你輸入某個函數(shù)的過程中，不妨將鼠標停留在提示的其他函數(shù)上，大致了解一下對應(yīng)函數(shù)的功能，不管是不是用的到，多認識幾個函數(shù)，對今后的好處都是不可估量的除了上頁所說的，用插入函數(shù)的方法來寫公式，還可以直接在單元格輸入公式內(nèi)容，如果你記得函數(shù)名，那怕只記得函數(shù)名的開頭幾個字母也行，在單元格內(nèi)輸入=和函數(shù)名的開頭字母，Excel會出現(xiàn)以你輸入字母開頭的所有函數(shù)，當(dāng)我們選擇其中某一個函數(shù)時，會有一個簡短的函數(shù)說明，如左圖。在編輯欄輸入公式也有同樣的效果，當(dāng)函數(shù)名輸入完成后，輸入(，這個括號代表函數(shù)名完成輸入，Excel會接著提示這個函數(shù)需要提供哪些參數(shù)。左圖中紅色框內(nèi)白色的區(qū)域就是編輯欄，通常我們習(xí)慣雙擊單元格修改里面的內(nèi)容或公式，其實也可以在編輯欄里進行修改。算術(shù)運算符算術(shù)運算符包括：加（+）、減（-）、乘（*）、除（/）、乘方（^），百分比（%）。關(guān)于百分號的一個用法：打開一個空表，A1單元格輸入一個數(shù)字，例如150，B1輸入公式：=A1/100，回車后B1顯示結(jié)果為1.5，這個公式中用到了單元格引用（A1）和運算符除號（/），意思是對A1單元格的數(shù)據(jù)除以100，在C1單元格輸入=A1%，回車后你會發(fā)現(xiàn)結(jié)果和B1的公式結(jié)果一樣，這里就是利用了百分號（%）直接參與運算了。提示：除100（/100）可以用%代替，公式結(jié)果一樣，但是公式減少了三個字符。那么除10000（/10000）如果用%的話改怎么寫呢？不妨自己動手試試自己的想法是否正確。運算符分為四種不同類型：算術(shù)、比較、文本連接和引用。技巧分享：Ctrl+shift+9（按著CTRL鍵和shift鍵，再按一下數(shù)字9）可以快速顯示隱藏的行菜鳥成功秘技：在學(xué)習(xí)Excel的過程中，尤其是公式函數(shù)的學(xué)習(xí)過程中，能夠舉一反三、觸類旁通，大膽假設(shè)后動手去驗證結(jié)果是一個非常好的習(xí)慣！本頁重點：了解算術(shù)運算符有哪些，練習(xí)一下%的用法。其他運算符比較運算符：包括=(等號)、>（大于號）、<（小于號）、>=（大于等于號）、<=（小于等于號）、<>（不等號）。提示：Excel中大于等于是>=，不是≥，小于等于是<=，不是≤，不等于是<>，不是≠。例如：=(A1<=B1)，=(A1>B1)*10，=A1=“優(yōu)秀”，這三個公式里用到了比較運算符，比較運算的結(jié)果為邏輯值，關(guān)于邏輯值第二章會詳細介紹，這里了解即可。文本運算符：&，這個符號使用shift和7鍵即可輸入。打開一個空表，A1輸入：學(xué)習(xí)，B1輸入：公式函數(shù)，C1輸入：=A1&B1，回車后可以看到，這個公式會把兩個單元格的內(nèi)容合起來。提示：使用&得到的值是文本類型，假如A1內(nèi)容為3，B1內(nèi)容為5，=A1&B1可以得到35，但是這個35是文本類型的數(shù)字。關(guān)于數(shù)字類型第二章會詳細介紹，這里了解即可。引用運算符：“:”（冒號）區(qū)域運算符、“,”（逗號）聯(lián)合運算符、“”（空格）交集運算符。這三個運算符的含義下頁介紹。技巧分享：在單元格內(nèi)按住alt鍵回車，即可在同一單元格內(nèi)換行輸入。本頁重點：了解其他的運算符有哪些，能夠掌握&的用法。名稱框技巧分享：選擇多個單元格，輸入內(nèi)容后按住Ctrl鍵回車，可以快速批量輸入相同內(nèi)容。左圖中紅色箭頭所指的白色區(qū)域就是名稱框，通常顯示當(dāng)前選定的單元格位置，左圖中選定的C4單元格。當(dāng)表格數(shù)據(jù)較大時，如果要選擇后面的單元格，可以直接在名稱框輸入，比如，要選擇Z100單元格，可以直接在名稱框輸入Z100，回車，而不用去拉滾動條來找單元格了。當(dāng)選擇一個區(qū)域的時候，同時觀察名稱框，會顯示選擇范圍的行數(shù)和列數(shù)，字母R代表行，字母C代表列，左圖中選定的范圍就是7行3列的一個區(qū)域。掌握這一點很重要，當(dāng)你后面學(xué)習(xí)了函數(shù)的時候，經(jīng)常要知道行列等信息，就可以通過名稱框來知道這些。當(dāng)你需要選擇較多的單元格時，例如要選擇A列的1000個單元格，用鼠標拉選會很費力，這時候可以在名稱框里輸入A1：A1000，回車即可完成，可謂是準確快捷。當(dāng)選擇一行或者一列的時候，名稱框顯示選擇范圍內(nèi)第一個單元格，這個可以自己試試。上頁介紹了三個引用運算符：“:”（冒號）、“,”（逗號）、“”（空格）。剛才的例子里已經(jīng)用了冒號，下面再通過幾個例子理解一下引用的含義。名稱框里輸入E5：F6回車、3:5回車、E4,F7,G1:G8回車、5:7C:G回車（7后面有個空格哦），你還可以輸入更多的引用方式，相信可以理解冒號、逗號和空格在引用中的作用了，這里需要提示一下，理解作用即可，不必糾結(jié)具體定義和為什么會這樣。本頁重點：會看名稱框里的信息，會在名稱框輸入引用地址。A1引用樣式技巧分享：按住shift鍵拖動行、列或者單元格，可以移動選擇的數(shù)據(jù)。默認情況下，Excel使用A1引用樣式，字母和數(shù)字被稱為行號和列標，例如，B2引用列B和行2交叉處的單元格。A1，E7，AB3，F(xiàn)22……等等這些都是如此，在2007以上的版本中，列號從A到XFD，共16,384列，行號從1到1048576，共1048576行，最后一列最后一行可以表示為：XFD1048576。在A2單元格輸入：=A1，這就是一個單元格引用的公式，這時候，不管你在A1單元格里輸入什么內(nèi)容，A2單元格都會隨著改變現(xiàn)實結(jié)果。=Sheet1!A1，這個公式也是一個單元格的引用，不過帶了工作表（也就是sheet）名，公式會隨著sheet1這個表的A1單元格內(nèi)容發(fā)生變化。需要說明的是：在公式里，sheet名后面要加個感嘆號。如果sheet名是自己修改的，那么引用其中的單元格后，sheet名還會有一對單引號，例如：=‘1’!A1，=‘1月’!$A$1，如果修改的sheet名只有字母，則不需要單引號，例如=Sheet!$A$1，=a!$A$1。以上的引用都發(fā)生在同一個文件（工作?。├铮瑢τ诳绻ぷ鞅〉囊?，還需要帶有文件名，文件名在一對中括號里，例如：=[1.xls]Sheet1!$C$5，=[舒爾特訓(xùn)練法.xlsx]Sheet1!$F$1。對于單元格引用，可以輸入等號，然后點擊需要引用的單元格，或者復(fù)制要引用的單元格，然后在目的單元格粘貼鏈接即可。本頁重點：掌握引用單元格內(nèi)容的方法，了解中括號和感嘆號在引用中的作用。相對引用技巧分享：在excel中F4鍵可以重復(fù)上一步的操作，例如你插入一列，按F4可以重復(fù)插入操作。絕對引用、相對引用和混合引用可以說是三個概念，也可以說是一個概念，就是“$”這個符號的用法。而關(guān)于這個問題，理解比概念本身更重要。按照以下步驟實際操作一下有助于你輕松掌握本頁內(nèi)容。在表格里輸入：=a1（如果你是在a1單元格輸入的話，可能會提示循環(huán)引用，不用管提示，直接確定），下拉幾個單元格（不用管公式得到什么結(jié)果），顯示公式：按公式選項卡的顯示公式按鈕或者按住Ctrl按一下~鍵（在數(shù)字1的左邊），看到的結(jié)果應(yīng)該是右圖所示。發(fā)現(xiàn)規(guī)律了嗎？字母（列號）不變，數(shù)字（行號）發(fā)生了變化。將A1公式右拉，結(jié)果如下圖所示，字母（列號）變，數(shù)字（行號）不變。復(fù)制A1單元格，粘貼到其他任意單元格，你會發(fā)現(xiàn)：公式所在單元格的位置改變，引用也隨之改變。這種引用就是相對引用，可以自己做下圖的例子，金額一列的公式就是相對引用的最常見應(yīng)用。絕對引用技巧分享：按住Ctrl鍵，按方向下（↓）或者右（→)鍵，可以快速到達最后一行或者最后一列數(shù)據(jù)。上一頁里我們知道了相對引用，但是很多時候我們需要引用不要隨位置發(fā)生變化，例如下面的例子：在單元格輸入=A1,不要回車，按一下F4功能鍵，會發(fā)現(xiàn)變成了=$A$1，自動多了兩個$，回車后將公式右拉，然后再下拉，結(jié)果如下圖所示，字母（列號）不變，數(shù)字（行號）也不變。當(dāng)然，你也可以直接輸入=$A$1，這樣就不用按F4了。F2的公式可以寫作：=B2*C2，這沒有問題，但是公式向右拉就出錯了，G2的公式變成了：=C2*D2，在這個例子里，我們希望公式里的B2不要隨著公式的位置發(fā)生變化，為此就需要用到絕對引用，還是按前頁的方法來了解這個問題。相信現(xiàn)在你應(yīng)該可以解決本頁開始的問題了，輸入=，點一下B2單元格，按一次F4功能鍵，繼續(xù)輸入*，再點一下C2單元格，回車右拉完成。如果對于這個問題你已經(jīng)沒有任何問題了，那么可以繼續(xù)下一頁的學(xué)習(xí)，否則一定要反復(fù)測試到成功為止，因為這個技能太重要了！本頁重點：掌握F4功能鍵的用法，初步理解公式中“$”的作用?；旌弦眠@是上一頁的問題解決方法，對于商品2，你當(dāng)然可以重復(fù)商品一的做法，F(xiàn)3單元格里寫：=$B$3*C3，然后右拉完成，但是如果有一百個甚至一千個商品呢？需要一個一個修改嗎？有沒有一種更靈活的引用方式來解決呢？答案是肯定的，這就是混合引用。繼續(xù)用我們自學(xué)的方法：就是動手實驗法來尋找答案。在單元格輸入=A1,不要回車，按一下F4功能鍵，會發(fā)現(xiàn)變成了=$A$1，不要回車，繼續(xù)按F4，變成=A$1，還是不回車按F4，又變成=$A1，再繼續(xù)按F4，變成=A1……你可以一直不停的按F4，最終發(fā)現(xiàn)，只有四種變化，區(qū)別就是在不同的位置出現(xiàn)$，對于沒有$的，術(shù)語叫做相對引用，有兩個$的，術(shù)語叫做絕對引用，前面用了兩頁來說明，只有一個$的，叫做混合引用，而混合引用有兩種情況，分別是列號前面有$和行號前面有$，希望能夠自己用前面兩頁的方法，通過下拉和右拉來領(lǐng)略兩種混合引用的作用，并且能夠用自己的理解總結(jié)出一句話，這是你完成蛻變的第一個門檻！老鳥總結(jié)如下：行號前有$，下拉不變；列號前有$，橫拉不變！相信你的總結(jié)一定比老鳥更精辟吧，吼吼~~~本頁問題的答案，F(xiàn)2寫公式：=$B2*C2，右拉下拉完成，你是不是做出來了呢？閱讀本頁內(nèi)容時，可能略有眩暈感，但是請你務(wù)必堅持下去，這是提升功力必不可少的一個技能！本頁重點：能夠熟練運用F4功能鍵，反復(fù)練習(xí)理解“$”。技巧分享：按住Ctrl鍵，按方向上（↑）或者左（←)鍵，可以快速到達第一行或者第一列數(shù)據(jù)。R1C1引用樣式R1C1引用樣式中：R代表行(row)，C代表列(column)，也就是使用“R”加行數(shù)字和“C”加列數(shù)字來指示單元格的位置，在A1方式下，列在前行在后，在RC模式下，行在前列在后。舉例對比：如何切換兩種引用方式：“Excel選項”，“公式”分類中的“使用公式”部分下，“R1C1引用樣式”前面打鉤即可進入RC模式。使用RC模式后，表格里的列號會以數(shù)字方式顯示。RC模式有什么用：在錄制宏的過程中，Excel將使用R1C1引用樣式錄制一些命令。在某些函數(shù)中，例如：indirect，支持RC模式的引用方式，對于一些特殊的引用問題非常有用。閱讀本頁內(nèi)容時，如有不適感，可隨時進入下一頁，待功力較強時再回來溫習(xí)。如果你剛才在選項里勾選了“R1C1引用樣式”。那么提醒你趕快取消這個勾。技巧分享：雙擊單元格的邊緣（鼠標指針出現(xiàn)黑色十字箭頭時）也有Ctrl鍵+按方向鍵的效果。常量老鳥閑話：不知不覺已經(jīng)介紹了好多概念，也許你覺得學(xué)概念好無趣，老鳥也覺得記那些很累，其實根本不用記的，知道是怎么回事足夠了，學(xué)excel不是為了應(yīng)付考試（筆試和機試），如果學(xué)excel是為了解決工作中的問題，那么你可以繼續(xù)堅持，但是會覺得累；如果你覺得excel挺有意思，挺神的，好玩，那么保持你的這種心態(tài)，excel不會讓你失望，你只會發(fā)現(xiàn)更多好玩的東西，終有一天不知不覺你也成為了高手之一！常量是不用計算的值。如果在公式中使用常量而不是對單元格的引用（例如，=30+70+110），則只有在自己更改公式時其結(jié)果才會更改。漢字、字母、數(shù)字、符號、日期、時間這些都是常量。在A1單元格輸入一個1到12之間的數(shù)字，B1單元格輸入一個1到20之間的數(shù)字，C1單元格輸入公式：=“今天是”&A1&“月”&B1&“日”，可以根據(jù)A1和B1的內(nèi)容得到一句話，今天是某月某日。這個公式里用了三個常量（都是漢字的）和兩個變量，還用了文本連接符&。需要強調(diào)的是，在公式里（包括函數(shù)里）引用常量時，只要不是數(shù)字，常量一律要加雙引號（是英文狀態(tài)的）。技巧分享：按住Ctrl、shift和方向鍵，可以快速選擇連續(xù)的非空區(qū)域。認識更多的公式=5+2*3將5加到2與3的乘積中。=A1+A2+A3將單元格A1、A2和A3中的值相加。=SQRT(A1)使用SQRT函數(shù)返回A1中值的平方根。=TODAY()返回當(dāng)前日期。=UPPER("hello")使用UPPER工作表函數(shù)將文本“hello”轉(zhuǎn)換為“HELLO”。=IF(A1>0,"包含","不包含")測試單元格A1，確定它是否包含大于0值。小測試：以上公式中分別包含了函數(shù)、運算符、引用和常量的一種或幾種？=D5*60/$D$13*60%這個公式是什么意思？本篇小結(jié)公式必須以(=)等號開頭。函數(shù)的結(jié)構(gòu)：函數(shù)名，一對括號，參數(shù)，逗號。編輯欄里看到的是真實的內(nèi)容，單元格看到的是最終顯示的內(nèi)容。在公式里（包括函數(shù)里）引用常量時，只要不是數(shù)字，常量一律要加雙引號（是英文狀態(tài)的）。本篇見到了這些符號：加（+）、減（-）、乘（*）、除（/）、乘方（^）、百分比（%）、冒號（:）、逗號（,）、連字符（&）、還有最最重要的一個符號（$），成對出現(xiàn)的符號有：小括號（）、中括號[]、單引號‘‘、雙引號“”。公式中所有符號都是在英文狀態(tài)下輸入。小測試答案小測試：試將以下算式改寫為Excel的公式：算式1：3+(5+20)÷4=3+(5+20)/4算式2：10×[(5+7)÷6]=10*((5+7)/6)算式3：23×（4-2）=23*（4-2）小測試：以下公式中分別包含了函數(shù)、運算符、引用和常量的一種或幾種？=5+2*3常量、運算符=A1+A2+A3引用、運算符=SQRT(A1)函數(shù)、引用=TODAY()函數(shù)=UPPER(“hello“)函數(shù)、常量=IF(A1>0,“包含”,“不包含“)函數(shù)、引用、運算符、常量=D5*60/$D$13*60%這個公式是什么意思？D5單元格的數(shù)字乘以60，再除以D13單元格的數(shù)字，結(jié)果乘以百分之六十（等同于乘以0.6），其中D5單元格是相對引用，D13單元格是絕對引用。你可能遇到的問題單元格輸入了1+1，沒有得到2，如右圖。錯誤原因：公式少了等號，正確寫法，=1+1單元格輸入了=1+1，還是沒有得到2，如右圖。錯誤原因：單元格為文本格式，檢查一下單元格格式是否為文本，如果是文本格式的話，改為常規(guī)格式，按一下F2鍵再回車。如果還是不行，則可能表格為顯示公式狀態(tài)，按住Ctrl鍵，按一下~（這個鍵在ESC鍵的下面，或者點擊公式選項卡的顯示公式按鈕，如右圖位置。在用NOW函數(shù)的時候發(fā)生了錯誤，如右圖。錯誤原因：函數(shù)名寫在括號里面了，正確寫法：=NOW()輸入公式=A1時報錯，如右圖。錯誤原因：列號顯示為數(shù)字，說明表格處于R1C1引用樣式，刪除錯誤公式后在選項里去掉“R1C1引用樣式”前面的勾，重新輸入公式即可。輸入公式=32+[5*(6+8)]時報錯，如右圖。錯誤原因：括號使用錯誤，正確寫法：=32+(5*(6+8))切換絕對引用時，按F4鍵沒變化。問題原因：需要雙擊單元格在編輯狀態(tài)按F4，或者在鼠標點在編輯欄里按F4；如果是筆記本的話，使用功能鍵可能需要