Excel基礎(chǔ)教程(函數(shù)應(yīng)用)

PAGE第5頁目錄TOC\o"1-3"\h\z目的 2報(bào)表兩大主要元素（文本、數(shù)值）解說 2文本（字符串） 2數(shù)值 3常用功能 3『轉(zhuǎn)置粘貼』 3『數(shù)據(jù)透視』 5『定位填充』 9常用公式函數(shù) 14& 14len 14Trim 14left 14right 15mid 15value 15min 15max 16sum 16average 16vlookup 16功能擴(kuò)展 22『與Access的報(bào)表銜接』 22『宏編譯』 22一些使用方法與技巧 23將數(shù)值金額轉(zhuǎn)換為大寫金額 23后語 23目的在Excel報(bào)表大量存在的情況下，通過培訓(xùn)，使員工更加了解Excel表格的使用，同時(shí)了解Excel的某些特殊擴(kuò)展功能，使得員工能發(fā)現(xiàn)自身操作過程中的“可優(yōu)化環(huán)節(jié)”，為優(yōu)化提出良好建議，提高工作效率。并為發(fā)現(xiàn)工作流中的真正瓶頸打下基礎(chǔ)。信不信由你，在工作中，能靈活使用以下函數(shù)，你就會(huì)被認(rèn)為是Excel高手。報(bào)表兩大主要元素（文本、數(shù)值）解說在日常報(bào)表操作中，通常涉及到對以下兩種元素的操作：文本（字符串）如表格抬頭、產(chǎn)品型號(hào)、備注等其它一些內(nèi)容，主要是一些中英文字符。對文本的操作主要是“標(biāo)準(zhǔn)化”，即通過各種手段，如連接（加長）、截?cái)啵ㄌ崛〔糠肿址┑?，使得文本以統(tǒng)一的格式、特征存在于表格當(dāng)中，以便于對報(bào)表中相關(guān)的數(shù)據(jù)進(jìn)行其它的查找、透視、匯總等操作。（這段話寫得有點(diǎn)牽強(qiáng)，寫完后我都看不大懂了。在此可先不管它，在以后的操作當(dāng)中會(huì)很有體會(huì)得。呵呵）文本通常是不能做加減等運(yùn)算的。特例一：有些看上去像數(shù)值的也可能是文本。當(dāng)使用求和【sum】等一些公式對某部分?jǐn)?shù)值進(jìn)行加減，得到錯(cuò)誤的結(jié)果時(shí)，那些“數(shù)值”很可能就是文本。這時(shí)候，為了要對“數(shù)值”進(jìn)行計(jì)算，就需要用函數(shù)將文本轉(zhuǎn)為數(shù)值。轉(zhuǎn)換函數(shù)為【value】，具體使用方法見《常用函數(shù)》部分。特例二：有很多看上去一樣的文本實(shí)際上是不一樣的。例如在型號(hào)中經(jīng)常出現(xiàn)的括號(hào)和橫桿，在中文輸入法（全角）的情況下輸入的括號(hào)和在英文輸入法（半角）的情況下輸入的括號(hào)是不一樣的，但通常很難辨別。這時(shí)候，在要對型號(hào)或其它一些文本進(jìn)行統(tǒng)一的時(shí)候，通常可以使用『查找替換』的功能，為了滿足分析的要求，一般建議將全角（中文輸入法）的字符替換為半角（英文輸入法）的字符。有部分文本因?yàn)閮啥税崭穸鴮?dǎo)致不一致。如“BCD－209S/E”和“BCD－209S/E”在Excel表格中看上去完全一樣，但實(shí)際上是不一樣的。這種情況下，在要對文本進(jìn)行統(tǒng)一的時(shí)候，通?？梢允褂谩総rim】函數(shù)，將兩端帶有空格的文本中的空格給去除，具體使用方法見《常用函數(shù)》部分。數(shù)值如表格中的收發(fā)存等數(shù)據(jù)，主要有“0”～“9”十個(gè)數(shù)字組成，另外，金額實(shí)際也是一種數(shù)值，只是其表示方式（保留兩位小數(shù)）和一般數(shù)值不大一致而已。數(shù)值通常要用來做加減等運(yùn)算。特例一：有些看上去像文本的也可能是數(shù)值。主要出現(xiàn)在一些通過公式將數(shù)值轉(zhuǎn)換為大寫金額的情況下，發(fā)生得不多，而且在單純使用Excel時(shí)不會(huì)造成什么錯(cuò)誤。在這里只提一下，在『與Access銜接』部分再做介紹。特例二：想輸入文本的時(shí)候卻是在輸入數(shù)值。目前見得最多的情況是在錄入郵政編碼或者電話號(hào)碼的時(shí)候，有些地區(qū)的郵政編碼第一個(gè)數(shù)字為“0”，這時(shí)候，如果直接在表格中輸入時(shí)，“0”值將不能顯示出來，因?yàn)镋xcel里會(huì)將數(shù)值前面的零值去掉。這樣，郵政編碼將被表示為一個(gè)5位的數(shù)值。解決辦法有兩種：一是在輸入之前將單元格的屬性（單元格格式）設(shè)置為文本格式，然后再輸入；二是在數(shù)值前面加上“’”一起輸入，如要輸入“013023”，則在單元格中輸入“’013023”，此時(shí)Excel便會(huì)自動(dòng)將該單元格設(shè)置成文本格式，并且不顯示常用功能『顯示/隱藏零值』功能：將表格中的所有零值一次性顯示或隱藏。主要用途：方便閱讀與打印。2、點(diǎn)擊“選項(xiàng)”2、點(diǎn)擊“選項(xiàng)”1、點(diǎn)擊菜單“工具”4、選擇是否需要顯示零值，前面打勾表示顯示，點(diǎn)擊去掉即不顯示。確定后，效果如下圖所示4、選擇是否需要顯示零值，前面打勾表示顯示，點(diǎn)擊去掉即不顯示。確定后，效果如下圖所示3、選擇“視圖”選項(xiàng)卡效果：所有零值已被隱藏效果：所有零值已被隱藏『轉(zhuǎn)置粘貼』功能：復(fù)制區(qū)域的頂行數(shù)據(jù)將顯示于粘貼區(qū)域的最左列，而復(fù)制區(qū)域的最左列將顯示于粘貼區(qū)域的頂行。即行變列，列變行。主要用途：調(diào)整格式與方便其它功能的使用。使用方法：2、右鍵單擊，復(fù)制數(shù)據(jù)1、選定要轉(zhuǎn)置的區(qū)域4、點(diǎn)擊“選擇性粘貼”2、右鍵單擊，復(fù)制數(shù)據(jù)1、選定要轉(zhuǎn)置的區(qū)域4、點(diǎn)擊“選擇性粘貼”3、右鍵單擊要粘貼數(shù)據(jù)的位置5、點(diǎn)擊選定“轉(zhuǎn)置5、點(diǎn)擊選定“轉(zhuǎn)置”復(fù)選框，然后點(diǎn)擊確定即得結(jié)果，如下圖所示?！簲?shù)據(jù)透視』功能：按一定格式將數(shù)據(jù)進(jìn)行加總。主要用途：對數(shù)據(jù)進(jìn)行匯總統(tǒng)計(jì)分析。使用方法：1、打開要進(jìn)行匯總分析的表格，選擇“數(shù)據(jù)透視表和圖表報(bào)告”1、打開要進(jìn)行匯總分析的表格，選擇“數(shù)據(jù)透視表和圖表報(bào)告”2、點(diǎn)擊下一步（在此僅介紹最常用的一種操作，做個(gè)引子，其它深入的操作讀者按需要自己摸索）2、點(diǎn)擊下一步（在此僅介紹最常用的一種操作，做個(gè)引子，其它深入的操作讀者按需要自己摸索）4、下一步，繼續(xù)其它內(nèi)容選擇3、選擇涉及分析的數(shù)據(jù)范圍4、下一步，繼續(xù)其它內(nèi)容選擇3、選擇涉及分析的數(shù)據(jù)范圍5、點(diǎn)擊完成，進(jìn)入后面的操作4、為了避免分析數(shù)據(jù)和目前表格數(shù)據(jù)混淆，可選“新建工作表5、點(diǎn)擊完成，進(jìn)入后面的操作4、為了避免分析數(shù)據(jù)和目前表格數(shù)據(jù)混淆，可選“新建工作表”，在數(shù)據(jù)量較少的情況下，也可以選擇“現(xiàn)有工作表”，使產(chǎn)生結(jié)果在當(dāng)前的工作表中顯示出來。6、字段列表。里面包含了前面所選擇的數(shù)據(jù)所涉及到的所有列的內(nèi)容。通過將相關(guān)的字段拖動(dòng)到【數(shù)據(jù)透視結(jié)果表】中，即可產(chǎn)生相應(yīng)的結(jié)果。如圖將三個(gè)字段分別拖放至相應(yīng)位置，即可得到下圖中的結(jié)果7、在此，這部分內(nèi)容簡稱為【數(shù)據(jù)透視結(jié)果表】6、字段列表。里面包含了前面所選擇的數(shù)據(jù)所涉及到的所有列的內(nèi)容。通過將相關(guān)的字段拖動(dòng)到【數(shù)據(jù)透視結(jié)果表】中，即可產(chǎn)生相應(yīng)的結(jié)果。如圖將三個(gè)字段分別拖放至相應(yīng)位置，即可得到下圖中的結(jié)果7、在此，這部分內(nèi)容簡稱為【數(shù)據(jù)透視結(jié)果表】8、明顯，【結(jié)果表】中的數(shù)據(jù)表示某時(shí)某庫實(shí)際發(fā)貨的匯總數(shù)。此時(shí)，還可以調(diào)整字段的相對位置或者通過拖放加入其它字段，來實(shí)現(xiàn)具有不同含義的結(jié)果。如將“計(jì)劃提貨時(shí)間8、明顯，【結(jié)果表】中的數(shù)據(jù)表示某時(shí)某庫實(shí)際發(fā)貨的匯總數(shù)。此時(shí)，還可以調(diào)整字段的相對位置或者通過拖放加入其它字段，來實(shí)現(xiàn)具有不同含義的結(jié)果。如將“計(jì)劃提貨時(shí)間”和計(jì)劃發(fā)貨倉庫調(diào)換，得到下表結(jié)果。9、這時(shí)，表格中的數(shù)據(jù)的含義為：某庫某時(shí)實(shí)際發(fā)貨數(shù)的匯總數(shù)。9、這時(shí)，表格中的數(shù)據(jù)的含義為：某庫某時(shí)實(shí)際發(fā)貨數(shù)的匯總數(shù)。『定位填充』功能：批量選定有相同特征的單元格，并按要求對這些單元格填充相應(yīng)的內(nèi)容。主要用途：通常用于對表格中存在的大量空格進(jìn)行填充。使用方法：1、對空格填“0”1、選定待填充區(qū)域1、選定待填充區(qū)域3、點(diǎn)擊“定位條件”3、點(diǎn)擊“定位條件”按鈕彈出下面對話框2、按“Ctrl”＋“g”健，彈出“定位”窗口5、點(diǎn)擊確定，即選定所有內(nèi)容為空的單元格4、選擇“空值5、點(diǎn)擊確定，即選定所有內(nèi)容為空的單元格4、選擇“空值”選項(xiàng)6、輸入填充內(nèi)容，這里是將空值填上“06、輸入填充內(nèi)容，這里是將空值填上“0”，所以輸入公式“＝0”，然后“Ctrl”—＋“Enter2、將某單元格的內(nèi)容填充到緊接其下的空格當(dāng)中去，如下面這種情況：為了分析方便，經(jīng)常要將“廣州為了分析方便，經(jīng)常要將“廣州”下面的空白單元格全部填上“廣州”，“深圳”下面的單元格填上“深圳”等等其前5個(gè)步驟和填充“0”6、輸入“＝6、輸入“＝”＋“上箭頭”，然后“Ctrl”＋“Enter”，即得結(jié)果。如下圖所示。常用公式函數(shù)&這是一個(gè)連接符，相當(dāng)于加號(hào)一樣，不過其所加的不是數(shù)值，而是字符串。即將該符號(hào)兩端的字符串給連接起來。使用方法：＝”tring1”&“string2”結(jié)果為：string1string2len該函數(shù)用于提取文本的長度。使用方法：＝len（“學(xué)好Excel”）結(jié)果：7Trim除了單詞之間的單個(gè)空格外，清除文本中所有的空格。主要用于清除文本兩端的空格。使用方法：＝trim（“hello”）結(jié)果：helloleft基于所指定的字符數(shù)截取文本字符串中的第一個(gè)或前幾個(gè)字符，即從文本的左端截取一定數(shù)量的字符。使用方法：1、截取字符串左端第一個(gè)字符＝left（“BCD－209S/E”）結(jié)果：B2、截取字符串左端3個(gè)字符＝left（“BCD－209S/E”，3）結(jié)果：BCD3、結(jié)合使用【&】的一個(gè)例子說明：由于科龍產(chǎn)品中出現(xiàn)同型號(hào)不同品牌的情況，這樣，在查詢（尤其是使用vlookup函數(shù)進(jìn)行垂直查找）的時(shí)候就會(huì)出現(xiàn)錯(cuò)誤的結(jié)果。比如康拜恩209S/E和容聲209S/E等。=left(“康拜恩”)&“209S/E”結(jié)果：康209S/E=left(“容聲”)&“209S/E”結(jié)果：容209S/E具體例子參考《Excel表格應(yīng)用技能培訓(xùn)實(shí)例匯總》right與【left】函數(shù)相對，該函數(shù)基于所指定的字符數(shù)返回文本字符串中最后一個(gè)或多個(gè)字符，即從文本的右端截取一定數(shù)量的字符。使用方法：截取字符串右端第一個(gè)字符＝right（“BCD－209S/E”）結(jié)果：E截取字符串右端3個(gè)字符＝right（“BCD－209S/E”，3）結(jié)果：S/Emid返回文本字符串中從指定位置開始的特定數(shù)目的字符。即從文本的中間截取一定數(shù)量的字符。使用方法：截取字符串中從第3個(gè)字符開始的1個(gè)字符＝mid（“BCD－209S/E”，3，1）結(jié)果：D截取字符串中從第5個(gè)字符開始的6個(gè)字符＝mid（“BCD－209S/E”，5，6）結(jié)果：209S/Evalue將代表數(shù)字的文本字符串轉(zhuǎn)換成數(shù)字。使用方法：將文本轉(zhuǎn)換為數(shù)值：=value(“09”結(jié)果：9min返回選定區(qū)域中所有數(shù)值中的最小值。這個(gè)函數(shù)很容易使用，但有個(gè)限制，即當(dāng)數(shù)據(jù)或者數(shù)據(jù)區(qū)域超過29個(gè)的時(shí)候，該函數(shù)將不能直接得出結(jié)果。如在連續(xù)日報(bào)中，通常包含31天的數(shù)據(jù)，當(dāng)要求31天的結(jié)存數(shù)的最小值時(shí)，就不能一次性的使用公式得到。這種情況的解決辦法其實(shí)也很簡單，“一次不行就用兩次甚至三次”，即先用【min】函數(shù)對一部分?jǐn)?shù)值求最小，然后再用【min】函數(shù)對剩余部分求最小，最后用【min】函數(shù)求兩個(gè)最小中的最小。采用這種分而治之的辦法，可以在不改變報(bào)表格式的情況下使問題得到解決。對下面介紹的【max】函數(shù)也有這種情況。max與【min】函數(shù)相對，返回選定區(qū)域中所有數(shù)值中的最大值。sum計(jì)算選定單元格區(qū)域中所有數(shù)字之和。average計(jì)算選定單元格區(qū)域中的所有數(shù)字的平均值（算術(shù)平均值）。vlookup在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。（這個(gè)定義太難看懂了，但我也不知道該怎么說。還是通過例子來看吧。）在日常使用中，也有人把它叫做匹配函數(shù)，但實(shí)際上，這不是真正意義上的匹配函數(shù)，而僅是一個(gè)垂直查找函數(shù)。如果大家對匹配函數(shù)有興趣，可以使用Excel的幫助，進(jìn)行了解，這里不做介紹。這個(gè)函數(shù)比較難熟練掌握，但這個(gè)函數(shù)在報(bào)表制作當(dāng)中經(jīng)常用到。所以，大家在學(xué)習(xí)使用的過程中，一定要多嘗試使用，堅(jiān)定信心把它練熟?？梢哉f，學(xué)習(xí)好這個(gè)函數(shù)，對Excel表格的應(yīng)用是一個(gè)質(zhì)的飛躍。另外，此培訓(xùn)手冊中的其它一些文本函數(shù)如【&】、【trim】、【left】、【right】、【mid】等，主要就是為了配合這個(gè)函數(shù)使用的。使用方法：＝vlookup（查找標(biāo)識(shí)，要查找內(nèi)容所在的數(shù)據(jù)區(qū)域，內(nèi)容所在第幾列，0）其中每一項(xiàng)的定義很難用一兩句話通俗的表達(dá)出來。通過具體例子可以得到更好的理解。固定標(biāo)識(shí)查找從查找標(biāo)識(shí)所在列起算，結(jié)存數(shù)在第5列。查找標(biāo)識(shí)所在列公式：=VLOOKUP("XQB58-528VID",A1:E217,5,0)從查找標(biāo)識(shí)所在列起算，結(jié)存數(shù)在第5列。查找標(biāo)識(shí)所在列公式：=VLOOKUP("XQB58-528VID",A1:E217,5,0)其中"XQB58-528VID"即為要查找的標(biāo)識(shí)；A1:E217為要查找的內(nèi)容所在的數(shù)據(jù)區(qū)域，注意查找標(biāo)識(shí)必須在數(shù)據(jù)區(qū)域的第一列；5表示結(jié)存數(shù)在選定數(shù)據(jù)區(qū)域的第5列；0表示非排序查找，一般都用0即可。從左邊的表格中查找型號(hào)為“XQB58-528VID”的結(jié)存數(shù)，填入單元格G4中存在如圖表格，在此簡稱“數(shù)據(jù)源存在如圖表格，在此簡稱“數(shù)據(jù)源”表現(xiàn)在要將上面數(shù)據(jù)源表中的“收入現(xiàn)在要將上面數(shù)據(jù)源表中的“收入”、“發(fā)出”和“結(jié)存”填到現(xiàn)在這個(gè)表格當(dāng)中，在此簡稱為“填充表”4、由于列表中沒有“vlookup”函數(shù)，所以點(diǎn)擊“4、由于列表中沒有“vlookup”函數(shù)，所以點(diǎn)擊“其它函數(shù)”。（當(dāng)使用過該函數(shù)時(shí)，vlookup函數(shù)會(huì)被記錄在清單當(dāng)中的，用起來就方便一些）3、點(diǎn)擊下三角按鈕以選擇函數(shù)1、點(diǎn)擊C2格2、點(diǎn)擊“＝”，開始編輯公式6、雙擊vlookup函數(shù)5、單擊選中“查找與引用6、雙擊vlookup函數(shù)5、單擊選中“查找與引用”類函數(shù)7、點(diǎn)擊以選定查找標(biāo)識(shí)7、點(diǎn)擊以選定查找標(biāo)識(shí)9、點(diǎn)擊以進(jìn)入下一步選擇內(nèi)容8、點(diǎn)擊要查找的標(biāo)識(shí)所在單元格A29、點(diǎn)擊以進(jìn)入下一步選擇內(nèi)容8、點(diǎn)擊要查找的標(biāo)識(shí)所在單元格A211、點(diǎn)擊以切換到要查找內(nèi)容（收入數(shù)）所在的表格10、點(diǎn)擊以選定要查找內(nèi)容（收入數(shù)）所在的數(shù)據(jù)區(qū)域11、點(diǎn)擊以切換到要查找內(nèi)容（收入數(shù)）所在的表格10、點(diǎn)擊以選定要查找內(nèi)容（收入數(shù)）所在的數(shù)據(jù)區(qū)域13、點(diǎn)擊以進(jìn)入下一步的輸入12、選定數(shù)據(jù)所在的區(qū)域，這里選擇全部，即從單元格A2到E21713、點(diǎn)擊以進(jìn)入下一步的輸入12、選定數(shù)據(jù)所在的區(qū)域，這里選擇全部，即從單元格A2到E21716、點(diǎn)擊確定，完成公式設(shè)置15、填014、填入3，因?yàn)樵跀?shù)據(jù)源表選定的內(nèi)容（A2：E217）中，從型號(hào)（查找標(biāo)識(shí)所在列）開始數(shù)，發(fā)出數(shù)在第3列。14、將A2：E217設(shè)成絕對引用，即在A,2,E,217前分別加上$符號(hào)16、點(diǎn)擊確定，完成公式設(shè)置15、填014、填入3，因?yàn)樵跀?shù)據(jù)源表選定的內(nèi)容（A2：E217）中，從型號(hào)（查找標(biāo)識(shí)所在列）開始數(shù)，發(fā)出數(shù)在第3列。14、將A2：E217設(shè)成絕對引用，即在A,2,E,217前分別加上$符號(hào)18、同樣操作以填充發(fā)出數(shù)據(jù)和結(jié)存數(shù)據(jù)，亦可將C2中的公式向右拖動(dòng)擴(kuò)展填充，但要注意以下兩個(gè)動(dòng)作：擴(kuò)展公式前，修改C2格中的公式，即在A2前加上“18、同樣操作以填充發(fā)出數(shù)據(jù)和結(jié)存數(shù)據(jù)，亦可將C2中的公式向右拖動(dòng)擴(kuò)展填充，但要注意以下兩個(gè)動(dòng)作：擴(kuò)展公式前，修改C2格中的公式，即在A2前加上“$”符號(hào)，表示絕對引用A列擴(kuò)展公式后，修改D2格中的公式，即將公式中的第三個(gè)參數(shù)（表示第幾列的）3改成4，因?yàn)榘l(fā)出數(shù)在選定的數(shù)據(jù)區(qū)域中，從型號(hào)開始數(shù)，處于第4列。同樣，當(dāng)該公式擴(kuò)展到結(jié)存數(shù)時(shí)，這個(gè)參數(shù)要改為5。17、拖動(dòng)以擴(kuò)展填充公式顯然，使用這個(gè)函數(shù)有個(gè)前提條件，即涉及到的填充表和數(shù)據(jù)源表中的型號(hào)必須是一致的才能實(shí)現(xiàn)查找，否則，結(jié)果會(huì)顯示為“＃N/A”。所以，在使用函數(shù)前先要對一些表示不一致的型號(hào)進(jìn)行統(tǒng)一，通常就要應(yīng)用到以上的幾個(gè)函數(shù)【&】、【left】、【mid】和【right】等。功能擴(kuò)展『與Access的報(bào)表銜接』由于Excel表格的易用性，所以對于數(shù)據(jù)的處理通常都通過Excel表格來處理，但在很多情況下，要根據(jù)Excel表格中記錄的數(shù)據(jù)填寫單據(jù)作為其它的用途。填寫費(fèi)用申領(lǐng)單就是一個(gè)很好的例子。由于費(fèi)用的筆數(shù)很多，通常要填寫大量的單據(jù)，這樣，會(huì)很浪費(fèi)人力物力。在這種情況下，如果將Excel與Access中的報(bào)表連接起來，通過報(bào)表的自由設(shè)計(jì)和打印功能直接批量的打印出來，則能省下大量的時(shí)間。這里只對此做簡單介紹，有興趣的朋友可以去詳細(xì)了解一下?！汉昃幾g』Excel中提供了大量的函數(shù)，使得在利用Excel做數(shù)據(jù)分析時(shí)能夠很方便的達(dá)到目的，但當(dāng)要對數(shù)據(jù)進(jìn)行比較復(fù)雜的分析時(shí)，通常就涉及到很多很多個(gè)步驟，即使能很熟練使用各種功能和函數(shù)，也需要花上大量的時(shí)間，才能完成數(shù)據(jù)的分析。由其當(dāng)這樣的復(fù)雜分析每月或者每周甚至每天都要進(jìn)行時(shí)，花費(fèi)這些時(shí)間就很不值得。為此，Excel中提供了宏匯編的功能，即通過編譯一段程序，有序地組織數(shù)據(jù)分析時(shí)使用的各種功能和函數(shù)，使得分析中所涉及到的各個(gè)步驟自動(dòng)執(zhí)行，一步到位地生成分析結(jié)