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