Excel函數公式使用心得

06-03

excel表格的基本操作　　在論壇學習已有時日，常見新手求助後興奮地拿著答案回往了，可是題目解決了，卻由於不能明白公式的含義，碰到類蘇光目自己還難以舉一反三應用甚至連一點小改動都需要再次求助；對函數公式略知一二者因不明公式含義不易拓展思路……等等現象，雖大多數都能在原帖得到熱心版主、壇友的解答，屢見妙答，但沒見到的人又重新發帖問及類蘇光目，不利於各種題目的綜合匯總，遂發此帖作為公式解釋使用！　　一、怎樣學習函數公式　　這是很多新手最想知道的事，函數那麼多，要從哪兒學起呢。我個人談點小體會：　　1、「學以致用」，用才是目的——就是你要和將要用到的東西先學。比如你根本用不上財務、工程函數，沒必要一下子就往看那些專業性很強的東西（嘿嘿，那些我基本不會），這樣就輕易進門了。基本上函數用得最多的邏輯判定和查找和引用這2類函數了。先不要急於學會「數組」，自己常用函數的普通用法有個大致的用法了解之後再往看它的數組用法。　　2、善於搜索，搜一下，能找到更多的解答；善於求助發帖求助要描述清楚附上必要的圖文並茂的附件，輕易得到解答，而且鍛煉了自己的表述能力。　　3、除了「求助」式學習，還要「助人」式的學習，相信這一點是眾多論壇高手們都經歷過的。只要有時間，少看一會兒電視少聊一會兒QQ少跟同事吹一會兒牛，到論壇上看看有沒有別人不懂而你懂的，別怕出糗，是驢是馬牽出來遛遛，相信你熱心幫人不會被嘲笑的，況竊冬拋磚引玉，說不定你拋的對別人甚至對高手來說也是塊寶玉呢。而，助人助己，有了越來越多的「求助」者給你免費提供了練習的機會，練得多了再綜合各種思路的比較，自己就有了一些想法，你的水平肯定與日俱增。　　4、一口氣吃不成胖子，多記一些學習的體會，日積月累，你就是高手了。　　二、如何解讀公式我也談點小體會吧：　　1、多看函數幫助。各個函數幫助裡面有函數的基本用法和一些「要點」，以及對數據排序、引用類型等等的要求。當然，函數幫助並不囊括所有函數的細微之處，不然，也就不會有那麼多求「解釋」的帖了。　　2、庖丁解牛——函數的參數之間用逗號隔開。（別笑話，這是最最基本的基本功，單個函數沒啥，組合多個函數的公式就是靠它了），這些逗號就是「牛」的關節，先把長公式大卸八塊之後逐個看明白了再拼湊起來讀就輕易多了。　　3、獨孤九劍——開個玩笑啦，這裡是取諧音「F9鍵」。F9鍵用來「抹黑」公式對解讀尤其是數組公式有非常強的作用，不過假如公式所含數據區域太大（比如上百行）你可以改變一下區域。具體方法比如下面這個簡單數組公式　　=sum(if(A1:A3>0,B1:B3))，用滑鼠在編輯欄（或F2）把把A1:A3>0部分「抹黑」，按下F9鍵，就看到{True;True;False}（假設A3不滿足），表示if的條件是這麼3行1列的邏輯值數組。——別忘了，看完之後按ESC取消哦，否則公式就變了。　　4、公式審核——（選擇公式單元格-〉菜單欄-〉[工具]-〉[公式審核]-〉[公式求值] 　　然後按照導向提示，一步步地觀察公式求值的過程）就是工具〉公式審核〉公式求值那個有fx的放大鏡，與F9功能基本相同，能一步步看公式運行的結果（但兩者效果均有一定限制，具體情況尚未明了，fx有時會造成Excel的重啟）。配合著用吧。　　5、留意定義名稱：很多人都回用定義名稱來使公式簡潔、直觀，更輕易讓「庖丁」來解，按下Ctrl+F3可以查看定義名稱（或者菜單〉插進〉名稱〉定義），假如名稱是對單元格區域的引用，這當你點擊名稱框下方的「引用位置」時，會在相應區域出現虛線選擇框。還可以選個空缺的地方，按下F3鍵，選擇「粘貼列表」把定義的名稱複製到空缺區域。　　6、關於函數的一些深層次的東西，　　公式解讀之基礎知識篇：　　1熟悉運算符　　*、+號在數組運算中，我們通常理解為「且」、「或」，但是他們與and、or函數是有著本質的區別的。 2、熟悉row()
ow(1:1)
ows($1:1)及column()函數。　　常見用數組公式取得多條記錄的開頭都是=if(row(1:1)>XXX,""……表示當公式下拉複製超過一定行數（滿足條件記錄的個數）時，顯示為空。書寫方式有row()-n、row(1:1)、rows($1:1)，前2個得到的是一個{}1行1列的數組，後一個得到的是一個單值，此外，假如未知數據行數要形成「多單元格數組公式」，則必須使用row()-n的形式；假如已確定要得到記錄的行數，則也可以用row(1:X)實現。比如：=IF(ROW(1:6)>COUNTIF(A1:A6,">0"),"",SMALL(IF(A1:A6>0, A1:A6),ROW(1:6)))——謝謝gvntw版主補充。　　備註：n是公式所在第一行的上一行的行號。比如在A7輸進第一個公式，則n=6。　　由於column函數與row函數很相似，就不在此贅述了。　　關於3種形式的討論請參考：　　3、熟悉「值」類型和數字格式：　　數值、文本以及邏輯值、錯誤值。這裡就講講數字吧。　　常犯的錯誤：見到「數字」就以為是「數值」了，實在數字分「數值型數字」和「文本型數字」。（註：日期是數值的一種特殊形式。）。竊冬由於數字格式不一致，輕易導致公式錯誤如查找不到或不能求和等運算。　　文本型數字轉換為數值型數字的方法：Value()函數轉換，*1、/1、+0、-0、--（兩個減號）轉換，這幾種轉換是在函數公式里的方法。　　基礎操縱法：a、複製一個空缺單元格，選擇需要轉換區域，選擇性粘貼為「加」；b、選擇1列數據區域，菜單〉數據〉分列〉完成（前一步可以選擇為日期、文本）；c、利用工具〉選項〉「錯誤檢查」選項，選擇需要轉換的區域，點擊頭一個單元格左上角出現的感嘆號〉「轉換為數字」。　　函數公式得到結果為文本的情況：使用文本函數比如Text、Char、CONCATENATE、Fixed、Left、Right、Mid、Substitute等函數以及文本合併符&得到的均為文本型。　　數字格式多種多樣：設置單元格格式〉數字選項卡下面除了「常規」，點擊其他任何選項以及右邊相應格式，然後再點擊常規最下方的「自定義」就可以看到剛才所選格式的表達方式了，這些方式都可以在自定義格式和Text函數第2參數中得到應用。具體可參考論壇中關於自定義格式的帖子。　　4、熟悉引用：　　（1）引用樣式：　　A1——用列標字母與行標數字表示，A1表示第1行第1列：　　R1C1——用R與行標數字、C與列標數字表示，R1C3表示第1行第3列，就是C1單元格。　　（2）盡對引用與相對引用：　　A1樣式：A1——相對引用，橫豎拉動公式都會變；A$1——列相對行盡對引用，橫拉列標變而豎拉行標不會變　　$A1——列盡對行相對引用，橫拉列標不會變而豎拉行標會變。$A$1——橫豎拉都不變。　　R1C1引用樣式——R[-1]C[3]——當前單元格的往上1行往右3列的位置，比如當前單元格是B2，則R[-1]C[3]表示E1單元格。　　關於數組公式進門到深進理解，　　數組公式解讀之基礎知識篇：　　1、概念：數組、多重計算、數組公式　　數組：就是具有一定行列尺寸的單元格元素或數值、文本、邏輯判定等組成的單、多元素的東西，比如：　　單元格A1在數組公式中也可以算1行1列的數組，A1:A2是一個2行1列的單元格數組，A1:B10是一個10行2列的單元格數組，{1,2;3,4}是一個2行（用分號隔開）2列（用逗號隔開）的常量數組，{True,False}是一個1行2列的邏輯值常量數組等等。　　多重計算：對一組或多組值執行多重計算。　　數組公式：Excel自帶幫助文件「數組公式對一組或多組值執行多重計算，並返回一個或多個結果。數組公式括於大括弧 ({ }) 中。按 Ctrl+Shift+Enter 可以輸進數組公式。」——記住按三鍵。　　在這裡：數組公式僅僅是一個「稱呼」，用以區別「普通公式」——不按三鍵。　　經過大多數人討論，儘管意見各異，為了同一定義方便理解，我們稱「只有按Ctrl+shift+enter結束的公式才是數組公式」。按這3個鍵的的作用在於通知Excel：「嘿！我是數組哦，要對我執行的是多重計算，別搞錯了哦」！　　比如=sumproduct(條件1*條件2*……*統計區域)這麼一個常用的多條件求和公式，只要它不用按三鍵，我們就稱為「普通公式」　　計算單個結果的數組公式：用數組公式執行多個計算而天生單個結果。——在單個單元格輸進公式並按三鍵形成的數組公式。　　計算多個結果的數組公式：使數組公式能計算出多個結果，必須將數組輸進到與數組參數具有相同列數和行數的單元格區域中。——在多個單元格區域輸進公式並按三鍵組成的一個整體的數組公式，我們稱之為「多單元格數組公式」。　　多條件篩選單列不重複值（解釋見附件）題目「公式裡面怎麼還能有空格？」　　一個挺有意思的取行列交叉值的方法，固然以前見Gvntw版主寫過取行列交叉值，不過用的人還是少。　　空格法：舉個簡單的例子：=sum(1:3 C:E)——這麼一個公式實質是對C1:E3求和，即對空格前與空格後的兩個區域取交叉部分。不過，這個題目不是這樣的，而是由於字型檔或者某種誤操縱產生的錯誤顯示，假如沒有繼續改動該名稱的話，它只是顯示成這個怪樣，而實質還是原來沒有錯的公式。但假如你按下Ctrl+F3在定義名稱框點擊該名稱什麼也不改按「添加」則會出錯！！！這個也是：題目2、莫名其妙之處，名稱後面「?」——我看到的是一個韓文？F3粘貼名稱後變為「?」，乖乖。呵呵。還沒弄明白怎麼出來的。是不是由於Excel版本差異的題目，5樓看到的不是韓文。　　至於求多條件不重複值，既然定義名稱，我還是習慣於「列表」功能定義的，（原因：1、動態引用可隨數據輸進而增加引用範圍；2、方便快速定義（插進〉名稱〉指定〉首行），3、可以用於Indirect函數的再次引用，參考：列表動態引用、關於Indirect對定義名稱再引用），個人習慣不同，呵呵，給個參考：為不佔空間，具體的公式解釋都在附件里了　　

　　多條件單列求不重複值.rar___.rar 　　取得工作表名稱　　這是個宏表函數取得工作表名稱的「定義名稱」　　方法：插進〉名稱〉定義shtname，引用位置輸進公式〉確定　　在某單元格輸進=shtname，則返回該工作表名稱。　　1、首先，get.document(1)是宏表4.0函數（可以搜論壇下載一個幫助說明）: 　　假如工作簿中不只一張表，用文字形式以「［book1］sheet1」的格式返回工作表的文件名。否則，只返回工作簿的文件名。工作簿文件名不包括驅動器，目錄或窗口編號。通常最好使用 GET. DOCUMENT(76) 　　和 GET. DOCUMENT(88) 來返回活動工作表和活動工作簿的文件名。　　get.document(88)以「book1」的形式返回活動工作簿的文件名。　　2、=SUBSTITUTE(GET.DOCUMENT(1),"["&GET.DOCUMENT(88)&" ]",)——就是將［book1.xls］sheet1中的[]號及book1替換為空　　得到sheet1 　　3、now()是一個易失性函數，隨著Excel的一些動作比如編輯單元格等變化，產生當前時間（數值的一種），T()函數對文本返迴文本自身，對數值返回空。所以&T(now())相當於&""，只不過這個""會隨時變化。　　這樣連起來就使得我們用shtname得到的工作表名稱是一個可以隨著Excel動作（如改變工作表名也是動作）而「實時」變化的工作表名　　常用的幾個取當前工作表名的公式：（基本都是用宏表函數，套上文本處理）　　=SUBSTITUTE(GET.DOCUMENT(76),"["&GET.DOCUMENT(88)& "]",)&T(NOW()) 　　=REPLACE(GET.DOCUMENT(76),1,FIND("]",GET.DOCUMENT( 76)),)&T(NOW()) 　　=MID(GET.DOCUMENT(76),FIND("]",GET.DOCUMENT(76))+1 ,255)&T(NOW()) 　　不用宏表函數取得工作表名稱　　在單元格中輸進：　　=MID(CELL("filename",A1),FIND("]",CELL("filename", A1))+1,255)——可得到工作表名，其原理同上，主要是利用CELL函數用"filename"作第1參數取得帶路徑的工作表名。　　在定義名稱中使用（強烈推薦）：　　shtname=MID(CELL("filename",!A1),FIND("]",CELL("fi lename",!A1))+1,255)——這是個工作簿級的定義名稱，可以得到工作簿中公式所在工作表的名稱。　　留意：CELL函數要求Excel文件已保存。　　再來一個公式解釋　　=INDEX(A:A,SMALL(IF(A$1:A$19<>"",ROW($1 　　

　　19),),ROW()))&"" 　　實在這個公式出往和&""兩個部分的話大家並不陌生，是依次列出滿足某條件的記錄的數組公式。　　解釋：　　1、關於if部分：假如A1:A19不為空，則返回A1:A19的行號（條件），否則返回（留意Excel2003及之前版本的最大行數是，Excel2007將採用行，具體見有關先容）；　　if外部套用small()+row()進行排序，將if得到的滿足條件的行號和不滿足條件的進行排序　　2、關於&""：首先說一個大家很熟悉的事情，那就是引用空單元格的情況，比如=A1、=index(1:1,,1)、=index(A:A,1)、=offset(B1,,-1)、=indirect("A1")等等各種各樣的方法引用A1單元格，當A1為空的時候，這些引用都返回0，假如這個0時我們所不想看到的話，經常會採用工具〉視圖〉零值勾選掉的方法。但比如我們想當A1=0的時候，引用A1就顯示0，當A1為空時，引用A1顯示空，那麼我們就可以在以上引用公式的尾巴加上&""——將A1單元格的內容與""空合併成文本，則此時返回的將不是0而是空了。留意，返回的是「文本」，假如A1是日期，則返回的是日期序列號數字的「文本」，等等（可以再套用Text(引用,"yyyy-m-d")等來顯示日期）。假如A1是數值型數字，返回的則是文本型數字，切記！　　3、基於以上兩點熟悉，我們不難理解，該公式的含義就是：　　對A列取行列交叉值（index），其行號是small函數排好序（按記錄先後順序）的行號和組成的。而由於一般情況下，Excel最底下那一行是沒有內容的（空），所以這個公式能達到=if(row(1:1)>滿足條件記錄個數,"",index(……))一樣的顯示效果。值得留意的是，除了這個公式縮短了長度和計算滿足條件記錄個數步驟帶來的好處以外，要切記返回的是文本哦！　　這個公式的用法還不少，喜歡的朋友們可以自己多試試。　　

詳情查看：excel表格的基本操作http://hi.baidu.com/xueexcel

2011職稱計算機考試Excel中快捷鍵和功能鍵

如何給excel的單元格添加斜線並在對角線兩邊添加內容

課題3:Excel(電子表格)基本操作

Office2007之Word表格及Excel中斜線表頭的繪製方法

輕鬆玩轉Excel(4)——單元格操作

[轉載]Excel表格基本操作

Excel中將平均值與標準差合併於一個單元格內的操作技巧

DxDbGrid與DbGridEh表格使用及導出Excel

Excel合併單元格技巧知多少(轉)