excel表格的基本操作 在論壇學(xué)習(xí)已有時(shí)日，常見(jiàn)新手求助后興奮地拿著答案回往了，可是題目解決了，卻由于不能明白公式的含義，碰到類蘇光目自己還難以舉一反三應(yīng)用甚至連一點(diǎn)小改動(dòng)都需要再次求助；對(duì)函數(shù)公式略知一二者因不明公式含義不易拓展思路……等等現(xiàn)象，雖大多數(shù)都能在原帖得到熱心版主、壇友的解答，屢見(jiàn)妙答，但沒(méi)見(jiàn)到的人又重新發(fā)帖問(wèn)及類蘇光目，不利于各種題目的綜合匯總，遂發(fā)此帖作為公式解釋使用！ 一、怎樣學(xué)習(xí)函數(shù)公式 這是很多新手最想知道的事，函數(shù)那么多，要從哪兒學(xué)起呢。我個(gè)人談點(diǎn)小體會(huì)： 1、“學(xué)以致用”，用才是目的——就是你要和將要用到的東西先學(xué)。比如你根本用不上財(cái)務(wù)、工程函數(shù)，沒(méi)必要一下子就往看那些專業(yè)性很強(qiáng)的東西（嘿嘿，那些我基本不會(huì)），這樣就輕易進(jìn)門(mén)了?；旧虾瘮?shù)用得最多的邏輯判定和查找和引用這2類函數(shù)了。先不要急于學(xué)會(huì)“數(shù)組”，自己常用函數(shù)的普通用法有個(gè)大致的用法了解之后再往看它的數(shù)組用法。 2、善于搜索，搜一下，能找到更多的解答；善于求助發(fā)帖求助要描述清楚附上必要的圖文并茂的附件，輕易得到解答，而且鍛煉了自己的表述能力。 3、除了“求助”式學(xué)習(xí)，還要“助人”式的學(xué)習(xí)，相信這一點(diǎn)是眾多論壇高手們都經(jīng)歷過(guò)的。只要有時(shí)間，少看一會(huì)兒電視少聊一會(huì)兒QQ少跟同事吹一會(huì)兒牛，到論壇上看看有沒(méi)有別人不懂而你懂的，別怕出糗，是驢是馬牽出來(lái)遛遛，相信你熱心幫人不會(huì)被嘲笑的，況竊冬拋磚引玉，說(shuō)不定你拋的對(duì)別人甚至對(duì)高手來(lái)說(shuō)也是塊寶玉呢。而，助人助己，有了越來(lái)越多的“求助”者給你免費(fèi)提供了練習(xí)的機(jī)會(huì)，練得多了再綜合各種思路的比較，自己就有了一些想法，你的水平肯定與日俱增。 4、一口氣吃不成胖子，多記一些學(xué)習(xí)的體會(huì)，日積月累，你就是高手了。 二、如何解讀公式 我也談點(diǎn)小體會(huì)吧： 1、多看函數(shù)幫助。各個(gè)函數(shù)幫助里面有函數(shù)的基本用法和一些“要點(diǎn)”，以及對(duì)數(shù)據(jù)排序、引用類型等等的要求。當(dāng)然，函數(shù)幫助并不囊括所有函數(shù)的細(xì)微之處，不然，也就不會(huì)有那么多求“解釋”的帖了。 2、庖丁解?！瘮?shù)的參數(shù)之間用逗號(hào)隔開(kāi)。（別笑話，這是最最基本的基本功，單個(gè)函數(shù)沒(méi)啥，組合多個(gè)函數(shù)的公式就是靠它了），這些逗號(hào)就是“牛”的關(guān)節(jié)，先把長(zhǎng)公式大卸八塊之后逐個(gè)看明白了再拼湊起來(lái)讀就輕易多了。 3、獨(dú)孤九劍——開(kāi)個(gè)玩笑啦，這里是取諧音“F9鍵”。F9鍵用來(lái)“抹黑”公式對(duì)解讀尤其是數(shù)組公式有非常強(qiáng)的作用，不過(guò)假如公式所含數(shù)據(jù)區(qū)域太大（比如上百行）你可以改變一下區(qū)域。具體方法比如下面這個(gè)簡(jiǎn)單數(shù)組公式 =sum(if(A1:A3>0,B1:B3))，用鼠標(biāo)在編輯欄（或F2）把把A1:A3>0部分“抹黑”，按下F9鍵，就看到{True;True;False}（假設(shè)A3不滿足），表示if的條件是這么3行1列的邏輯值數(shù)組?！?jiǎng)e忘了，看完之后按ESC取消哦，否則公式就變了。 4、公式審核——（選擇公式單元格-〉菜單欄-〉[工具]-〉[公式審核]-〉[公式求值] 然后按照導(dǎo)向提示，一步步地觀察公式求值的過(guò)程）就是工具〉公式審核〉公式求值那個(gè)有fx的放大鏡，與F9功能基本相同，能一步步看公式運(yùn)行的結(jié)果（但兩者效果均有一定限制，具體情況尚未明了，fx有時(shí)會(huì)造成Excel的重啟）。配合著用吧。 5、留意定義名稱：很多人都回用定義名稱來(lái)使公式簡(jiǎn)潔、直觀，更輕易讓“庖丁”來(lái)解，按下Ctrl+F3可以查看定義名稱（或者菜單〉插進(jìn)〉名稱〉定義），假如名稱是對(duì)單元格區(qū)域的引用，這當(dāng)你點(diǎn)擊名稱框下方的“引用位置”時(shí)，會(huì)在相應(yīng)區(qū)域出現(xiàn)虛線選擇框。還可以選個(gè)空缺的地方，按下F3鍵，選擇“粘貼列表”把定義的名稱復(fù)制到空缺區(qū)域。 6、關(guān)于函數(shù)的一些深層次的東西， 公式解讀之基礎(chǔ)知識(shí)篇： 1熟悉運(yùn)算符 *、+號(hào)在數(shù)組運(yùn)算中，我們通常理解為“且”、“或”，但是他們與and、or函數(shù)是有著本質(zhì)的區(qū)別的。 2、熟悉row()\row(1:1)\rows($1:1)及column()函數(shù)。 常見(jiàn)用數(shù)組公式取得多條記錄的開(kāi)頭都是=if(row(1:1)>XXX,""……表示當(dāng)公式下拉復(fù)制超過(guò)一定行數(shù)（滿足條件記錄的個(gè)數(shù)）時(shí)，顯示為空。書(shū)寫(xiě)方式有row()-n、row(1:1)、rows($1:1)，前2個(gè)得到的是一個(gè){}1行1列的數(shù)組，后一個(gè)得到的是一個(gè)單值，此外，假如未知數(shù)據(jù)行數(shù)要形成“多單元格數(shù)組公式”，則必須使用row()-n的形式；假如已確定要得到記錄的行數(shù)，則也可以用row(1:X)實(shí)現(xiàn)。比如：=IF(ROW(1:6)>COUNTIF(A1:A6,">0"),"",SMALL(IF(A1:A6>0, A1:A6),ROW(1:6)))——謝謝gvntw版主補(bǔ)充。 備注：n是公式所在第一行的上一行的行號(hào)。比如在A7輸進(jìn)第一個(gè)公式，則n=6。 由于column函數(shù)與row函數(shù)很相似，就不在此贅述了。 關(guān)于3種形式的討論請(qǐng)參考： 3、熟悉“值”類型和數(shù)字格式： 數(shù)值、文本以及邏輯值、錯(cuò)誤值。這里就講講數(shù)字吧。 常犯的錯(cuò)誤：見(jiàn)到“數(shù)字”就以為是“數(shù)值”了，實(shí)在數(shù)字分“數(shù)值型數(shù)字”和“文本型數(shù)字”。（注：日期是數(shù)值的一種特殊形式。）。竊冬由于數(shù)字格式不一致，輕易導(dǎo)致公式錯(cuò)誤如查找不到或不能求和等運(yùn)算。 文本型數(shù)字轉(zhuǎn)換為數(shù)值型數(shù)字的方法：Value()函數(shù)轉(zhuǎn)換，*1、/1、+0、-0、--（兩個(gè)減號(hào)）轉(zhuǎn)換，這幾種轉(zhuǎn)換是在函數(shù)公式里的方法。 基礎(chǔ)操縱法：a、復(fù)制一個(gè)空缺單元格，選擇需要轉(zhuǎn)換區(qū)域，選擇性粘貼為“加”；b、選擇1列數(shù)據(jù)區(qū)域，菜單〉數(shù)據(jù)〉分列〉完成（前一步可以選擇為日期、文本）；c、利用工具〉選項(xiàng)〉“錯(cuò)誤檢查”選項(xiàng)，選擇需要轉(zhuǎn)換的區(qū)域，點(diǎn)擊頭一個(gè)單元格左上角出現(xiàn)的感嘆號(hào)〉“轉(zhuǎn)換為數(shù)字”。 函數(shù)公式得到結(jié)果為文本的情況：使用文本函數(shù)比如Text、Char、CONCATENATE、Fixed、Left、Right、Mid、Substitute等函數(shù)以及文本合并符&得到的均為文本型。 數(shù)字格式多種多樣：設(shè)置單元格格式〉數(shù)字選項(xiàng)卡下面除了“常規(guī)”，點(diǎn)擊其他任何選項(xiàng)以及右邊相應(yīng)格式，然后再點(diǎn)擊常規(guī)最下方的“自定義”就可以看到剛才所選格式的表達(dá)方式了，這些方式都可以在自定義格式和Text函數(shù)第2參數(shù)中得到應(yīng)用。具體可參考論壇中關(guān)于自定義格式的帖子。 4、熟悉引用： ?。?）引用樣式： A1——用列標(biāo)字母與行標(biāo)數(shù)字表示，A1表示第1行第1列： R1C1——用R與行標(biāo)數(shù)字、C與列標(biāo)數(shù)字表示，R1C3表示第1行第3列，就是C1單元格。 ?。?）盡對(duì)引用與相對(duì)引用： A1樣式：A1——相對(duì)引用，橫豎拉動(dòng)公式都會(huì)變；A$1——列相對(duì)行盡對(duì)引用，橫拉列標(biāo)變而豎拉行標(biāo)不會(huì)變 $A1——列盡對(duì)行相對(duì)引用，橫拉列標(biāo)不會(huì)變而豎拉行標(biāo)會(huì)變。$A$1——橫豎拉都不變。 R1C1引用樣式——R[-1]C[3]——當(dāng)前單元格的往上1行往右3列的位置，比如當(dāng)前單元格是B2，則R[-1]C[3]表示E1單元格。 關(guān)于數(shù)組公式進(jìn)門(mén)到深進(jìn)理解， 數(shù)組公式解讀之基礎(chǔ)知識(shí)篇： 1、概念：數(shù)組、多重計(jì)算、數(shù)組公式 數(shù)組：就是具有一定行列尺寸的單元格元素或數(shù)值、文本、邏輯判定等組成的單、多元素的東西，比如： 單元格A1在數(shù)組公式中也可以算1行1列的數(shù)組，A1:A2是一個(gè)2行1列的單元格數(shù)組，A1:B10是一個(gè)10行2列的單元格數(shù)組，{1,2;3,4}是一個(gè)2行（用分號(hào)隔開(kāi)）2列（用逗號(hào)隔開(kāi)）的常量數(shù)組，{True,False}是一個(gè)1行2列的邏輯值常量數(shù)組等等。 多重計(jì)算：對(duì)一組或多組值執(zhí)行多重計(jì)算。 數(shù)組公式：Excel自帶幫助文件“數(shù)組公式對(duì)一組或多組值執(zhí)行多重計(jì)算，并返回一個(gè)或多個(gè)結(jié)果。數(shù)組公式括于大括號(hào) ({ }) 中。按 Ctrl+Shift+Enter 可以輸進(jìn)數(shù)組公式?！薄涀“慈I。 在這里：數(shù)組公式僅僅是一個(gè)“稱呼”，用以區(qū)別“普通公式”——不按三鍵。 經(jīng)過(guò)大多數(shù)人討論，盡管意見(jiàn)各異，為了同一定義方便理解，我們稱“只有按Ctrl+shift+enter結(jié)束的公式才是數(shù)組公式”。按這3個(gè)鍵的的作用在于通知Excel：“嘿！我是數(shù)組哦，要對(duì)我執(zhí)行的是多重計(jì)算，別搞錯(cuò)了哦”！ 比如=sumproduct(條件1*條件2*……*統(tǒng)計(jì)區(qū)域)這么一個(gè)常用的多條件求和公式，只要它不用按三鍵，我們就稱為“普通公式” 計(jì)算單個(gè)結(jié)果的數(shù)組公式：用數(shù)組公式執(zhí)行多個(gè)計(jì)算而天生單個(gè)結(jié)果?！趩蝹€(gè)單元格輸進(jìn)公式并按三鍵形成的數(shù)組公式。 計(jì)算多個(gè)結(jié)果的數(shù)組公式：使數(shù)組公式能計(jì)算出多個(gè)結(jié)果，必須將數(shù)組輸進(jìn)到與數(shù)組參數(shù)具有相同列數(shù)和行數(shù)的單元格區(qū)域中。——在多個(gè)單元格區(qū)域輸進(jìn)公式并按三鍵組成的一個(gè)整體的數(shù)組公式，我們稱之為“多單元格數(shù)組公式”。 多條件篩選單列不重復(fù)值（解釋見(jiàn)附件） 題目“公式里面怎么還能有空格？” 一個(gè)挺有意思的取行列交叉值的方法，固然以前見(jiàn)Gvntw版主寫(xiě)過(guò)取行列交叉值，不過(guò)用的人還是少。 空格法：舉個(gè)簡(jiǎn)單的例子：=sum(1:3 C:E)——這么一個(gè)公式實(shí)質(zhì)是對(duì)C1:E3求和，即對(duì)空格前與空格后的兩個(gè)區(qū)域取交叉部分。 不過(guò)，這個(gè)題目不是這樣的，而是由于字庫(kù)或者某種誤操縱產(chǎn)生的錯(cuò)誤顯示，假如沒(méi)有繼續(xù)改動(dòng)該名稱的話，它只是顯示成這個(gè)怪樣，而實(shí)質(zhì)還是原來(lái)沒(méi)有錯(cuò)的公式。但假如你按下Ctrl+F3在定義名稱框點(diǎn)擊該名稱什么也不改按“添加”則會(huì)出錯(cuò)！?。∵@個(gè)也是：題目2、莫名其妙之處，名稱后面“?”——我看到的是一個(gè)韓文？F3粘貼名稱后變?yōu)椤?”，乖乖。呵呵。還沒(méi)弄明白怎么出來(lái)的。是不是由于Excel版本差異的題目，5樓看到的不是韓文。 至于求多條件不重復(fù)值，既然定義名稱，我還是習(xí)慣于“列表”功能定義的，（原因：1、動(dòng)態(tài)引用可隨數(shù)據(jù)輸進(jìn)而增加引用范圍；2、方便快速定義（插進(jìn)〉名稱〉指定〉首行），3、可以用于Indirect函數(shù)的再次引用，參考：列表動(dòng)態(tài)引用、關(guān)于Indirect對(duì)定義名稱再引用），個(gè)人習(xí)慣不同，呵呵，給個(gè)參考：為不占空間，具體的公式解釋都在附件里了  多條件單列求不重復(fù)值.rar___.rar 取得工作表名稱 這是個(gè)宏表函數(shù)取得工作表名稱的“定義名稱” 方法：插進(jìn)〉名稱〉定義shtname，引用位置輸進(jìn)公式〉確定 在某單元格輸進(jìn)=shtname，則返回該工作表名稱。 1、首先，get.document(1)是宏表4.0函數(shù)（可以搜論壇下載一個(gè)幫助說(shuō)明）: 假如工作簿中不只一張表，用文字形式以“［book1］sheet1”的格式返回工作表的文件名。否則，只返回工作簿的文件名。工作簿文件名不包括驅(qū)動(dòng)器，目錄或窗口編號(hào)。通常最好使用 GET. DOCUMENT(76) 和 GET. DOCUMENT(88) 來(lái)返回活動(dòng)工作表和活動(dòng)工作簿的文件名。 get.document(88)以“book1”的形式返回活動(dòng)工作簿的文件名。 2、=SUBSTITUTE(GET.DOCUMENT(1),"["&GET.DOCUMENT(88)&" ]",)——就是將［book1.xls］sheet1中的[]號(hào)及book1替換為空 得到sheet1 3、now()是一個(gè)易失性函數(shù)，隨著Excel的一些動(dòng)作比如編輯單元格等變化，產(chǎn)生當(dāng)前時(shí)間（數(shù)值的一種），T()函數(shù)對(duì)文本返回文本自身，對(duì)數(shù)值返回空。所以&T(now())相當(dāng)于&""，只不過(guò)這個(gè)""會(huì)隨時(shí)變化。 這樣連起來(lái)就使得我們用shtname得到的工作表名稱是一個(gè)可以隨著Excel動(dòng)作（如改變工作表名也是動(dòng)作）而“實(shí)時(shí)”變化的工作表名 常用的幾個(gè)取當(dāng)前工作表名的公式：（基本都是用宏表函數(shù)，套上文本處理） =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()) 不用宏表函數(shù)取得工作表名稱 在單元格中輸進(jìn)： =MID(CELL("filename",A1),FIND("]",CELL("filename", A1))+1,255)——可得到工作表名，其原理同上，主要是利用CELL函數(shù)用"filename"作第1參數(shù)取得帶路徑的工作表名。 在定義名稱中使用（強(qiáng)烈推薦）： shtname=MID(CELL("filename",!A1),FIND("]",CELL("fi lename",!A1))+1,255)——這是個(gè)工作簿級(jí)的定義名稱，可以得到工作簿中公式所在工作表的名稱。 留意：CELL函數(shù)要求Excel文件已保存。 再來(lái)一個(gè)公式解釋 =INDEX(A:A,SMALL(IF(A$1:A$19<>"",ROW($1  19),),ROW()))&"" 實(shí)在這個(gè)公式出往和&""兩個(gè)部分的話大家并不陌生，是依次列出滿足某條件的記錄的數(shù)組公式。 解釋： 1、關(guān)于if部分：假如A1:A19不為空，則返回A1:A19的行號(hào)（條件），否則返回（留意Excel2003及之前版本的最大行數(shù)是，Excel2007將采用行，具體見(jiàn)有關(guān)先容）； if外部套用small()+row()進(jìn)行排序，將if得到的滿足條件的行號(hào)和不滿足條件的進(jìn)行排序 2、關(guān)于&""：首先說(shuō)一個(gè)大家很熟悉的事情，那就是引用空單元格的情況，比如=A1、=index(1:1,,1)、=index(A:A,1)、=offset(B1,,-1)、=indirect("A1")等等各種各樣的方法引用A1單元格，當(dāng)A1為空的時(shí)候，這些引用都返回0，假如這個(gè)0時(shí)我們所不想看到的話，經(jīng)常會(huì)采用工具〉視圖〉零值勾選掉的方法。但比如我們想當(dāng)A1=0的時(shí)候，引用A1就顯示0，當(dāng)A1為空時(shí)，引用A1顯示空，那么我們就可以在以上引用公式的尾巴加上&""——將A1單元格的內(nèi)容與""空合并成文本，則此時(shí)返回的將不是0而是空了。留意，返回的是“文本”，假如A1是日期，則返回的是日期序列號(hào)數(shù)字的“文本”，等等（可以再套用Text(引用,"yyyy-m-d")等來(lái)顯示日期）。假如A1是數(shù)值型數(shù)字，返回的則是文本型數(shù)字，切記！ 3、基于以上兩點(diǎn)熟悉，我們不難理解，該公式的含義就是： 對(duì)A列取行列交叉值（index），其行號(hào)是small函數(shù)排好序（按記錄先后順序）的行號(hào)和組成的。而由于一般情況下，Excel最底下那一行是沒(méi)有內(nèi)容的（空），所以這個(gè)公式能達(dá)到=if(row(1:1)>滿足條件記錄個(gè)數(shù),"",index(……))一樣的顯示效果。值得留意的是，除了這個(gè)公式縮短了長(zhǎng)度和計(jì)算滿足條件記錄個(gè)數(shù)步驟帶來(lái)的好處以外，要切記返回的是文本哦！ 這個(gè)公式的用法還不少，喜歡的朋友們可以自己多試試。  詳情查看：excel表格的基本操作 2011職稱計(jì)算機(jī)考試Excel中快捷鍵和功能鍵 如何給excel的單元格添加斜線并在對(duì)角線兩邊添加內(nèi)容 Office2007之Word表格及Excel中斜線表頭的繪制方法 Excel中將平均值與標(biāo)準(zhǔn)差合并于一個(gè)單元格內(nèi)的操作技巧 |
聯(lián)系客服
