Excel制表方法

1、返回絕對值的公式為=abs()做管理費(fèi)時(shí)，從序時(shí)賬篩選出所有管理費(fèi)用，單獨(dú)開一個(gè)工作表標(biāo)簽，貼進(jìn)去，然后有必要的話把這個(gè)標(biāo)簽移動(dòng)或復(fù)制到“管理費(fèi)用通用表格”中去。退人員的費(fèi)用計(jì)入職工福利費(fèi)，通過應(yīng)付職工薪酬過賬，離退休人員的費(fèi)用直接計(jì)入福利費(fèi)下的離退休人員統(tǒng)籌外費(fèi)用，不通過應(yīng)付職工薪酬過賬，Xlw不保存數(shù)據(jù)，只是一個(gè)快捷方式按住shift拖拽某列可以與某列替換ctrl加冒號鍵，會(huì)輸入當(dāng)天的日期實(shí)心黑加號拖拽，按住ctrl鍵拖拽使拖拽出來的容發(fā)生變化：本來拖拽出來的是一樣的容（即復(fù)制了第一個(gè)單元格），按住ctrl鍵會(huì)拖拽出序列來；本來拖拽出來的是一個(gè)序列，按住ctrl鍵會(huì)變成復(fù)制第一個(gè)單元格。實(shí)

2、心黑加號拖拽，不是按住左鍵而是按住右鍵拖拽，會(huì)有很多種填充方法供你選擇在“文件”“選項(xiàng)”“高級”下頭兒“編輯自定義列表”中，可以自定義經(jīng)常拖拽的容，比如三四王五六。同樣的位置下面還有l(wèi)otus123兼容性，點(diǎn)選“轉(zhuǎn)換lotus123”后，可不輸入等號的情況下直接輸入公式得到公式的結(jié)果雙擊“格式刷”，可以按照選中的格式刷無數(shù)次“設(shè)置單元格格式”“數(shù)字""分類""自定義":y表示顯示年，yyyy表示年份用四位數(shù)表示；m表示顯示月，mm表示用兩位數(shù)顯示月，mmm表示用英文簡寫顯示月，mmmm表示用英文全稱顯示月；d表示顯示日，dd表示用兩位數(shù)顯示日，

3、ddd表示用英文簡寫顯示日，dddd表示用英文全稱顯示日。數(shù)字格式分四種情況：正值；負(fù)值；零；文本，四種情況用分號“；”分開。日期的本質(zhì)也是數(shù)字，是從1900年1月1日開始計(jì)算的，比如3就表示1900年1月3日數(shù)字格式自定義為aaa，是用一、二、三、四、五、六、日來顯示，定義為aaaa是用星期一、星期二、星期三、星期四、星期五、星期六、星期天來顯示。如果想讓他顯示周幾，輸入周”aaa”即可。Excel只能保證15位的精確度，如果超過15位，比如說18位，超過的部分會(huì)忽略為0。左上角有綠箭頭的數(shù)字為文本格式，不可進(jìn)行運(yùn)算，而且通過修改單元格格式也不能把它轉(zhuǎn)換為數(shù)字。應(yīng)當(dāng)選中要修改的文本格式數(shù)字，

4、左上角會(huì)出現(xiàn)一個(gè)黃色的感嘆號，點(diǎn)擊之，可以將其改成數(shù)字格或者在旁邊隨便什么單元格里打上一個(gè)數(shù)字1，然后復(fù)制這個(gè)單元格，在要粘貼到選中的文本格式數(shù)字時(shí)，選擇“選擇性粘貼”，會(huì)打開一個(gè)對話框，選擇“乘”，Excel就會(huì)強(qiáng)制把不能運(yùn)算的文本格式數(shù)字轉(zhuǎn)為數(shù)字格式的數(shù)字。從文檔中復(fù)制過來的數(shù)據(jù)，會(huì)自動(dòng)分行，但是不會(huì)自動(dòng)分列，即都擠到第一列里去了?？蛇x中復(fù)制過來的數(shù)據(jù)，點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡，找到工具“分列”，可以按照固定的分隔符號（比如逗號或者分號）或者固定的寬度進(jìn)行分列，方便數(shù)據(jù)處理。但是Excel里默認(rèn)的標(biāo)點(diǎn)符號都是英文模式下的標(biāo)點(diǎn)符號，所以如果想以中文的逗號為分隔符，就用“其他”，可自定義分隔符。而且

5、在點(diǎn)擊“完成”前，如果點(diǎn)擊“下一步”，會(huì)有機(jī)會(huì)對分隔后的兩列單元格的格式進(jìn)行修改?！胺至小惫ぞ呤且粋€(gè)很強(qiáng)的工具。正常情況下，文本格式是不能轉(zhuǎn)換成其他格式的，就算把單元格格式改了，里面的值也該是文本形態(tài)，不方便分析的；但是“分列”這個(gè)工具卻可以，因?yàn)槿绻选跋乱徊健秉c(diǎn)到最后，會(huì)發(fā)現(xiàn)它可以改變分出來的（或者根本沒分，還是保持在一列里）單元格格式，連同里面值的格式，從而把文本形態(tài)的值變成數(shù)據(jù)格式的值。當(dāng)然也可以不點(diǎn)“下一步”，直接點(diǎn)“完成”，得到的就是默認(rèn)格式，還是可以把文本形態(tài)的值變成數(shù)據(jù)形態(tài)。Ctrl+f是查找，其中也可以替換。但是如果遇到這種情況：有一些單元格寫的是管理費(fèi)，另一些寫的是管理費(fèi)用

6、，現(xiàn)在要統(tǒng)一成管理費(fèi)用，怎么辦？這時(shí)可以點(diǎn)開選項(xiàng)，勾選“單元格匹配”，在將所有的管理費(fèi)替換成管理費(fèi)用就可以了。因?yàn)閷懼芾碣M(fèi)用的單元格是不符合搜索條件的。在“查找和替換”單元格中，可以查找一定格式的單元格來替換成其他格式或容的單元格，“格式”按鈕后面有個(gè)下拉箭頭，拉出來的第二個(gè)就是從現(xiàn)有的表格里提取格式。在“替換”的時(shí)候，輸入星號*用來表示通配符，比如替換“管理費(fèi)用*”為“我了個(gè)擦”，那所以前面帶“管理費(fèi)用”的，不管“管理費(fèi)用”后面還跟了什么字，統(tǒng)統(tǒng)都會(huì)變成“我了個(gè)擦”。如果說要把“管理費(fèi)用”后面只跟了三個(gè)字的找出來，比如管理費(fèi)用辦公費(fèi)，管理費(fèi)用差旅費(fèi)，把這樣的都找出來，應(yīng)該怎么辦呢？替換“管

7、理費(fèi)用?”+勾選單元格匹配，替換為“我了個(gè)擦”，那么所有管理費(fèi)用后面跟三個(gè)字的就都替換為“我了個(gè)擦”了。也就是說*是表示一切東西的通配符，而?則是表示一個(gè)字的通配符。其中?一定要是英文格式下的?如果有需要查找所有帶*的單元格怎么辦？需要在*前面加，否則Excel會(huì)默認(rèn)*為通配符，找到些亂七八糟的東西，而加上后，*就失去了通配符的功能，?也是同理。選中某個(gè)區(qū)域的單元格，然后在名稱框里輸入一個(gè)隨便起的名字，就可以為這個(gè)區(qū)域的單元格命名，下次再在名稱框里面輸入名字，就可以直接跳到這個(gè)區(qū)域。添加備注時(shí)，office2010是可以改變備注框的形狀的，但是要自己先設(shè)置一下，第一次設(shè)置好了，后面就不用管了。

8、先隨便插入一個(gè)“形狀”，點(diǎn)擊這個(gè)圖形，會(huì)發(fā)現(xiàn)多了“繪圖工具”這個(gè)選項(xiàng)卡，在這個(gè)選項(xiàng)卡中找到“編輯形狀”按鈕，點(diǎn)擊下拉箭頭，看到第一個(gè)選項(xiàng)就是“更改形狀”，在“更改形狀”這個(gè)選項(xiàng)上點(diǎn)擊右鍵，再點(diǎn)擊“添加到快捷訪問工具欄”，會(huì)發(fā)現(xiàn)Excel窗口的最上頭多了一個(gè)“更改形狀”按鈕。這時(shí)候再點(diǎn)擊備注的框框就可以通過上面的“更改形狀”來選擇想要的批注框的形狀了。如何在批注中插入圖片呢？選中要插圖片的批注，出現(xiàn)十字箭頭的時(shí)候點(diǎn)擊右鍵，選擇“設(shè)置批注格式”，點(diǎn)選其中的顏色與線條，可以看到可以修改填充色，點(diǎn)擊“顏色”下拉箭頭，選中“填充效果”，就可以看到有“漸變”“紋理”“圖案”“圖片”幾個(gè)選項(xiàng)卡了，選擇“圖片

9、”，就可以插入圖片了。如果想將所有的批注同時(shí)顯示出來，可以點(diǎn)擊“審閱”選項(xiàng)卡，里面有“顯示所有批注”這個(gè)按鈕，按一下就可以了。“定位”的意思就是選中符號條件的單元格。按Ctrl+G，選擇“定位條件”，點(diǎn)選“批注”，就可以同時(shí)選擇所有含有批注的單元格，然后就任人宰割咯。Ctrl+Z可以撤銷剛剛的操作。選中一個(gè)區(qū)域多個(gè)單元格，輸入1，然后按Ctrl+回車，就可以在所有選中的單元格中輸入1.工作中獲得的表格往往有合并的單元格，Excel只會(huì)認(rèn)為第一個(gè)單元格里有東西，其他都是空的，所以不好編輯。這時(shí)應(yīng)當(dāng)將所有合并的單元格拆開，然后Ctrl+G,點(diǎn)擊"定位條件"選中“空值”，這時(shí)就選

10、中了所有空著的空格，再輸入=，再按鍵盤上的方向鍵“上”，再Ctrl+回車，可以看到所有單元格都被填充了，而且是它上面那個(gè)格子里的值?！岸ㄎ弧睘椤皩ο蟆钡脑?，會(huì)選中所有的圖片。如果有一成績表，有數(shù)學(xué)、語文、英語三門的成績，其中數(shù)學(xué)是最重要的，語文次之，英語再次，按照這個(gè)原則給一個(gè)班的同學(xué)排名，也就是說先看數(shù)學(xué)成績，高的排前面，數(shù)學(xué)成績一樣的話再看語文成績，語文成績再一樣再看英語成績，應(yīng)該怎么辦呢？首先選中要排序的數(shù)據(jù)所在的區(qū)域，點(diǎn)擊“開始”選項(xiàng)卡中的“排序和篩選“，點(diǎn)擊”自定義排序“，出現(xiàn)一個(gè)對話框，里面有”主要關(guān)鍵字“下拉箭頭，選擇”數(shù)學(xué)“，按照“數(shù)值”排序，次序?yàn)椤敖敌颉保冱c(diǎn)擊“添加條件”

11、，出現(xiàn)一行“次要關(guān)鍵字”，選擇“語文”，按照“數(shù)值”排序，次序?yàn)椤敖敌颉?，然后再點(diǎn)“添加條件”，又出現(xiàn)一行“次要關(guān)鍵字”，選擇“英語”然后就可以了。還有一種操作也可以達(dá)到一樣的效果：先從最次要的一列開始，點(diǎn)選英語那一列的任意單元格，點(diǎn)擊“排序和篩選”，點(diǎn)擊“降序”，然后點(diǎn)選語文那一列的任意單元格，點(diǎn)擊“排序和篩選”，點(diǎn)擊“降序”，然后點(diǎn)選數(shù)學(xué)那一列的任意單元格，點(diǎn)擊“排序和篩選”，點(diǎn)擊“降序”，發(fā)現(xiàn)這樣做的結(jié)果和自定義是一樣的，因?yàn)樽钚碌呐判驎?huì)刷新掉原來的排序，但是如果最新的排序和原來的排序不沖突，原來的排序就得以保留下來，比如三個(gè)人數(shù)學(xué)都是90分，那么點(diǎn)選數(shù)學(xué)那一列的排序并不會(huì)對他們?nèi)齻€(gè)本來

12、的排序做改變，所以他們實(shí)際上還是按照語文的分值進(jìn)行排序的。如果想按照“紅”“黃”“綠”的顏色對單元格進(jìn)行排序，可以用“自定義排序”，很簡單?，F(xiàn)在人力資源部要給大家發(fā)工資啦，大家的和所在部門都在表格中，在“部門”那一列輸入為“審計(jì)一部”“審計(jì)二部”“審計(jì)七部”，現(xiàn)在要按照1234567的順序把人員排列起來，怎么辦？先選中要排序的大區(qū)域，點(diǎn)擊“排序和篩選”“自定義排序”選擇主要關(guān)鍵字“部門”“排序依據(jù)”還是選擇“數(shù)值”“次序”選擇“自定義序列”，然后依次輸入“審計(jì)一部”回車“審計(jì)二部”回車“審計(jì)七部”，點(diǎn)擊“添加”再點(diǎn)“確定”確定，就完成任務(wù)了。制作工資條：表頭有“”“部門”“學(xué)歷”“出勤天數(shù)”“

13、加班天數(shù)”等等等等，下面是一個(gè)個(gè)人以與他們的工資，工資單就是每個(gè)人的數(shù)據(jù)上面一行都有表頭，到時(shí)候打印出來，然后把它撕開，然后他們過來簽名后領(lǐng)走。怎么做到每一行信息上面都有表頭呢？首先復(fù)制表頭，看還缺多少表頭，就在數(shù)據(jù)區(qū)域的下面復(fù)制幾行，然后在數(shù)據(jù)最后一列后面的那一列輸入編號，表頭后面輸入0，第一個(gè)人后面輸入1，依次類推，然后把復(fù)制出來的表頭后面輸入1.5、2.5、3.5，然后點(diǎn)選這一列的任意一個(gè)單元格，按升序排列就可以了。比如說有一個(gè)非常多行的表格要打印，打印出來好幾頁，但是只有第一頁有表頭，其他的都沒有，很不方便，怎么辦？點(diǎn)擊頁面設(shè)置選項(xiàng)卡，里面有個(gè)“打印標(biāo)題”選項(xiàng)，點(diǎn)擊之，可以看到“打印標(biāo)

14、題”“頂端標(biāo)題行”，就可以設(shè)置每一頁都打印的表頭了?！昂Y選”是Excel中bug最多的工具了。比如我要把篩選出來的數(shù)據(jù)貼到另外一表里，結(jié)果復(fù)制粘貼之后，發(fā)現(xiàn)沒有被篩選出來的數(shù)據(jù)也被貼過來了，怎么辦？方法是：首先還是選中要復(fù)制的區(qū)域，然后點(diǎn)Ctrl+G"定位條件"“可見單元格”，復(fù)制、粘貼，就可以了通過表格里的下拉箭頭里的“數(shù)字篩選”，可以篩出>某值<某值的數(shù)來。果這一列全是文字，還可以進(jìn)行“文本篩選”，可選擇“開頭是”或者“結(jié)尾是”?？旖萱I：先點(diǎn)選某個(gè)數(shù)據(jù)區(qū)域的某個(gè)單元格，按住Ctrl鍵，再按“左”，就會(huì)自動(dòng)選中該單元格那一行的該數(shù)據(jù)區(qū)域最左邊的單元格。如果按住

15、Shift鍵，再按“左”，就會(huì)選中該單元格以與它左邊的單元格。如果選中某數(shù)據(jù)區(qū)域最左上角的單元格，同時(shí)按住Ctrl和Shift，再按“右”再按“下”，即可選中整個(gè)數(shù)據(jù)區(qū)域。除了“篩選”外，還有“高級篩選”，在“數(shù)據(jù)”選項(xiàng)卡里，“篩選”的旁邊，有個(gè)“高級”。例如，某單位銷售產(chǎn)品，有個(gè)銷售記錄表，其中客戶那一列里有客戶A、客戶B、客戶C、等等，但是這一列是按照時(shí)間順序排列的，客戶A、B、C等都出現(xiàn)了多次，現(xiàn)在要獲取客戶的列表（不能重復(fù)），怎么辦？打開“高級篩選”，會(huì)看到有“在原有區(qū)域顯示篩選結(jié)果”和“將篩選結(jié)果復(fù)制到其他位置”，前一個(gè)和篩選一樣，是把不被篩選的單元格隱藏，而后一個(gè)是把篩選的結(jié)果另外

16、貼到別的地方去?，F(xiàn)在我們選擇“將篩選結(jié)果復(fù)制到其他位置”?！傲斜韰^(qū)域”就選擇客戶那一列，“條件區(qū)域”空著，“復(fù)制到”隨便選擇一個(gè)單元格，最重要的是在“選擇不重復(fù)的記錄”前面打勾。點(diǎn)確定就好了。“條件區(qū)域”的應(yīng)用可以非常炫目。比如“部門”“工資”這兩列，要求從這兩列中挑選審計(jì)六部工資在8000塊錢以上的人，那么在空白處中輸入部門工資審計(jì)六部>8000“列表區(qū)域”選擇整個(gè)數(shù)據(jù)區(qū)域，“條件區(qū)域”選擇剛剛填的四個(gè)格子，點(diǎn)擊確定，可以得到結(jié)果?！皸l件區(qū)域”中在一行里表示“并且”，而不在一行里表示“或者”。例如，要挑選審計(jì)六部或者工資8000塊錢以上的人，怎么辦？在空白處輸入部門工資審計(jì)六部>

17、8000然后設(shè)置為“條件區(qū)域”就可以了。上述的單元格里還可以使用*和?作為通配符。再上一個(gè)例子：如果要找到所有審計(jì)五部的+審計(jì)六部工資8000塊以上+不管什么部門工資在10000以上的，怎么辦？“條件區(qū)域”設(shè)置為部門工資審計(jì)五部審計(jì)六部>8000>10000就可以達(dá)到目的。所以可以看出一行就是一個(gè)選擇的條件。上一個(gè)更難得例子：現(xiàn)有一個(gè)表格列出了本期所有銷售的記錄，其中有一列是“成本”，有一列是“售價(jià)”?，F(xiàn)在要求把所有成本>售價(jià)的銷售記錄找出來，怎么辦？可以通過在“條件區(qū)域”的單元格里編輯公式來完成。假定“成本”是在G列，“售價(jià)”是在H列，第一行數(shù)據(jù)在“3”列?！皸l件區(qū)域”設(shè)置

18、為=G3>H3在“條件區(qū)域”中如果用公式進(jìn)行篩選，第一個(gè)單元格可以是空的，也可以是錯(cuò)的，但就不能是對的，也不能不選上，否則一條記錄都篩不出來。另，=G3>H3這個(gè)公式寫在Excel的表格里，如果G3確實(shí)大于H3，會(huì)顯現(xiàn)TURE，否則顯示FALSE。分類匯總功能：在“數(shù)據(jù)”選項(xiàng)卡中有“分類匯總”的功能，但是要應(yīng)用分類匯總功能，必須先排序，在所要分類的列里按照升序或者降序排列都可以（不論是文字還是數(shù)字），要的并不是排序的結(jié)果，而是使同樣的文字匯集到一起。排序完了點(diǎn)選所屬數(shù)據(jù)區(qū)域的任意單元格，按“分類匯總”鍵，可以根據(jù)“所屬地區(qū)”對“金額”進(jìn)行分類匯總。很簡單。如果不想要了，還是點(diǎn)選所屬

19、數(shù)據(jù)區(qū)域的任意單元格“分類匯總”“全部刪除”。如果要求在分地區(qū)分類匯總的基礎(chǔ)上再按產(chǎn)品分類統(tǒng)計(jì)數(shù)量、金額、成本，怎么辦？其實(shí)沒有區(qū)別，首先是要排序，兩列同時(shí)排序可以用“數(shù)據(jù)”選項(xiàng)卡中的“排序”鍵進(jìn)行，只要有個(gè)順序就可以了，沒有其他要求。然后，再按“分類匯總”將“替換當(dāng)前分類匯總”前面的勾取消掉就可以了定位到可見單元格的快捷鍵是Alt+；有個(gè)“分類匯總”的變態(tài)的操作：要把同一列中填有同樣容（所屬地區(qū)）的單元格合并：首先對所屬地區(qū)排序?qū)Α八鶎賲^(qū)域”進(jìn)行分類匯總會(huì)在左邊出現(xiàn)一列，選中這一列（除了第一個(gè)單元格）定位到空值合并單元格取消分類匯總把合并過的那一列的格式貼到所屬區(qū)域那一列。第五講：數(shù)據(jù)有效性

20、1、 對單元格設(shè)置規(guī)則，不符合條件的數(shù)據(jù)不允許輸入，例如只能在A列輸入5001000的整數(shù)：選中A列，點(diǎn)開“數(shù)據(jù)”選項(xiàng)卡選中“數(shù)據(jù)有效性”“數(shù)據(jù)有效性”“設(shè)置”“整數(shù)”最大值1000最小值500確定；2、 設(shè)置B列僅能輸入字符長度為8位的產(chǎn)品編碼：選中B列，“數(shù)據(jù)有效性“設(shè)置”允許“文本長度”數(shù)據(jù)“等于”長度“8”；3、 設(shè)置C列付款方式中僅能輸入現(xiàn)金、轉(zhuǎn)賬、支票：選中C列，“數(shù)據(jù)有效性”允許“序列”來源“現(xiàn)金,轉(zhuǎn)賬,支票”，其中的逗號必須是英文的。第六講：數(shù)據(jù)透視表應(yīng)用1、 一列是一個(gè)字段，一行是一個(gè)完整的記錄2、 選中數(shù)據(jù)區(qū)域中的某一個(gè)單元格，點(diǎn)擊“插入”選項(xiàng)卡，點(diǎn)擊數(shù)據(jù)透視，就會(huì)自動(dòng)選中

21、整個(gè)數(shù)據(jù)區(qū)域，點(diǎn)擊“確定”，插入了新的數(shù)據(jù)透視表。在數(shù)據(jù)透視表上點(diǎn)擊右鍵，“數(shù)據(jù)透視表選項(xiàng)”“顯示”選項(xiàng)卡勾選“經(jīng)典數(shù)據(jù)透視表布局”確定。想把什么往哪里放，拖過去就是了。3、 雙擊表格左上角的“計(jì)數(shù)項(xiàng)：發(fā)生額”，可以更改計(jì)數(shù)的方法，比如從發(fā)生額變?yōu)橛?jì)數(shù)，就可以知道每個(gè)月發(fā)生了多少筆交易。還可以在“分類匯總”下面點(diǎn)選“無”，就可以不對本列進(jìn)行匯總。如果說對某月某部門的數(shù)據(jù)有疑問，可以雙擊那個(gè)單元格，就會(huì)出現(xiàn)一新的表格，顯示被雙擊單元格的數(shù)據(jù)來源。4、 如果要按照地區(qū)分大類，季度分小類，統(tǒng)計(jì)數(shù)據(jù)，應(yīng)該怎么辦？在月份那一列按右鍵點(diǎn)擊“創(chuàng)建組”在“步長”里選中“季度”就可以了。5、 比如現(xiàn)在想分析一下

22、本年不同金額的訂單的分布情況，比如1萬以下的有多少單？金額總共是多少？1萬到兩萬的單有多？總共金額有多少？怎么辦？把“金額”拖到行標(biāo)簽去，在把“金額”拖到數(shù)值里去，在右鍵點(diǎn)擊“行標(biāo)簽”任意單元格點(diǎn)擊“創(chuàng)建組”在“起始于”填0在“終止于”填320000（因?yàn)榻痤~的最大值是30萬多一點(diǎn)，但是如果填31萬的話，步長不好填，只能填一萬，填其他的不容易除盡,而填32萬就好很多）“步長”可填40000.6、 對同一數(shù)據(jù)進(jìn)行不同分析，只要將所要分析的數(shù)據(jù)往數(shù)據(jù)欄里多拖拽幾次就可以實(shí)現(xiàn)了。7、 在數(shù)據(jù)透視表里插入公式：點(diǎn)擊數(shù)據(jù)透視表區(qū)域里的任何一個(gè)單元格，會(huì)出現(xiàn)一個(gè)“數(shù)據(jù)透視表工具”選項(xiàng)卡點(diǎn)擊里面的“選項(xiàng)”選

23、項(xiàng)卡點(diǎn)擊里面的“域、項(xiàng)目和集”選點(diǎn)擊“計(jì)算字段”在名稱里寫“利潤率”在“公式”里把0刪掉，想輸入那一列的數(shù)據(jù)從下面“字段”里選“=（金額-利潤）/金額”點(diǎn)擊確定，就完成了。如果想把一些公式計(jì)算錯(cuò)誤的格子美觀掉，右鍵點(diǎn)擊數(shù)據(jù)透視表中任意單元格，選中“數(shù)據(jù)透視表選項(xiàng)”，點(diǎn)擊“布局和格式”，勾選“對于錯(cuò)誤值，顯示”，就完成了。8、 將不同的項(xiàng)目在不同的工作表里顯示：把要分不同工作表顯示的項(xiàng)目拖到“報(bào)表篩選”里面去把需要顯示的東西拖進(jìn)數(shù)據(jù)透視表里去選中數(shù)據(jù)透視表里的任意單元格，點(diǎn)擊“選項(xiàng)”選項(xiàng)卡在最左側(cè)找到“選項(xiàng)”下拉箭頭點(diǎn)擊“顯示報(bào)表篩選頁”選定要顯示的報(bào)表篩選頁字段確定，完成發(fā)現(xiàn)自動(dòng)建了非常多的工

24、作表，而且都是按你選的那一列里的文字命名的。第七講：函數(shù)1、&是連字符，在數(shù)字7上面。比如單元格A1寫的是“”，單元格B1寫的是“三”，在單元格C1里寫“=A1&B1”，則C1單元格會(huì)顯示為“三”。2、在中文模式下，Shift+6表示省略號，在英文模式下Shift+6表示，是乘方。3、當(dāng)鼠標(biāo)移動(dòng)到右下角變成黑實(shí)心十字時(shí)，雙擊，就起到了拖拽的作用，下面的單元格自動(dòng)填充了。4、絕對引用：當(dāng)公式中引用某個(gè)單元格比如A2單元格后，按F4，就會(huì)顯示為$A$2，表示絕對引用，即無論公式怎么拖拽，始終引用A2這一個(gè)單元格。其中$表示鎖定，如果按兩次F4會(huì)變成A$2，按三次會(huì)變成$A2，按四次

25、就沒有了。$A$2表示永遠(yuǎn)引用這一個(gè)單元格，A$2表示上下拖動(dòng)（沿1、2、3、4方向）不會(huì)改變引用的單元格，但左右拖動(dòng)會(huì)改變；$A2表示左右拖動(dòng)（沿A、B、C、D、E方向）不會(huì)改變引用的單元格，但上下拖拽會(huì)改變?；旌弦美壕啪懦朔ū恚ㄖ挥?jì)算得數(shù)）123456789112243369448121655101520256612182430367714212835424988162432404856649918273645546372815、 求排名的函數(shù)rank：這個(gè)函數(shù)的完整形式=rank（需要被排名的數(shù)字，在哪些數(shù)字中進(jìn)行排名，0或者1）其中0表示降序排名，即最大的數(shù)是第一名，比較常用；1表

26、示升序排名，即最小的數(shù)是第一名。另注意，因?yàn)樾枰献?，而所選“在哪些數(shù)字中進(jìn)行排名”往往不需要變，所以在選中“在哪些數(shù)字中進(jìn)行排名”后按一下F4.6、 在“開始”選項(xiàng)卡左邊有個(gè)自動(dòng)“自動(dòng)求和”按鈕，下拉箭頭中還有其他函數(shù)。這個(gè)按鈕的最大特點(diǎn)是可以自動(dòng)選擇數(shù)據(jù)區(qū)域，因此當(dāng)不方便選擇數(shù)據(jù)區(qū)域時(shí)好用。例如有一列數(shù)據(jù)，中間有隔兩個(gè)數(shù)需要求一次和的，有隔三個(gè)數(shù)求一次和的，有隔五個(gè)數(shù)求一次和的，各不一樣，不方便拖拽。這時(shí)只要定位到空值，然后使用“自動(dòng)求和”按鈕就可以自動(dòng)選中要求和的數(shù)據(jù)，非常方便。自動(dòng)求和的快捷鍵為Alt+=.第八講：IF函數(shù)邏輯判斷1、 =if（條件，如果符合條件將顯示的值，如果不符合條

27、件將顯示的值）2、 例如現(xiàn)在有一個(gè)學(xué)校的學(xué)生的分?jǐn)?shù)表，其中600以上（含）的是第一批，大于等于500小于600的是第二批，大于等于400小于500的是第三批，小于400的是落榜。公式應(yīng)該怎樣寫呢？注意，Excel是不認(rèn)識(shí)如“400<=H4<500”這個(gè)公式的，它會(huì)先運(yùn)算前面的“<=400”得到“TURE”或者“FALSE”,然后把“TURE”或者“FALSE”跟500進(jìn)行比較。上例的公式可以寫為：=if（H4>=600,”第一批”，if（H4>=500,”第二批”,if(H4>=400,”第三批”,”落榜”)）3、 if函數(shù)和iserror函數(shù)相連，比如某列

28、用公式計(jì)算出來的值沒有意義（比如說因?yàn)槌艘粋€(gè)0），導(dǎo)致表不好看，或者后面的數(shù)據(jù)引用這個(gè)值的時(shí)候沒有意義，這時(shí)就可以利用if函數(shù)和iserror函數(shù)結(jié)合使用：=if（iserror（D3/F3）,0,D3/F3）本公式的意思是如果D3/F3是錯(cuò)誤的話，那就顯示為0，否則就顯示其運(yùn)算結(jié)果。4、 if函數(shù)和and函數(shù)、or函數(shù)相連，比如年齡60歲以上的男性員工給予1000元獎(jiǎng)勵(lì)：=if(and(A3=”男”,B3>=60),1000,0)比如給所有女性和大于60歲的男性1000元獎(jiǎng)金：=if（or（A3=”女”,and(A3=”男”,B3>=60)）,1000,0）在and()和or(

29、)中可以有多個(gè)條件，三個(gè)、四個(gè)都搞得定。一定要記住：if函數(shù)嵌套幾層取決于結(jié)果有幾種，而有幾種可能性只影響and或者or函數(shù)怎么搭配。第九講：COUNTIF函數(shù)1、 count函數(shù)只會(huì)數(shù)數(shù)字，而不會(huì)把文字包含在。2、 =countif(需要計(jì)數(shù)的區(qū)域，條件)3、 例如要看看一個(gè)班里數(shù)學(xué)考與格的有幾個(gè)人：=countif(B2:G2,”>=60”)，完成。因?yàn)?gt;=60不算是數(shù)字也不算是完整的公式，所以只能認(rèn)為他是一個(gè)字符串，所以要用”把它括起來。4、 使用countif函數(shù)數(shù)超過15位的數(shù)字的時(shí)候要注意，因?yàn)镋xcel會(huì)自動(dòng)忽略15位以后的不同，所以后面要加點(diǎn)東西以保證數(shù)出來的數(shù)正確

30、：=countif(A2:A200,A2&”*”)5、 Countif函數(shù)的應(yīng)用1：例如有一個(gè)班的人需要體檢，其中昨天已經(jīng)體檢了10個(gè)了，而且有，現(xiàn)在要把沒有體檢的人找出來（全班人員在A列，已體檢的人員在H列）。=if（countif(H:H,A2)=1,”已體檢”,”未體檢”）6、 Countif和條件格式搭配:接上例，如果要把沒有體檢過的人都標(biāo)記為紅色，豈不是更加好找？選中全班同學(xué)的名字“開始”選項(xiàng)卡“條件格式”“新建規(guī)則”“使用公式確定要設(shè)置格式的單元格”在“為符合此公式的值設(shè)置格式”下面寫公式：=countif(H:H,A2)=0“格式”背景顏色改為紅色，完成7、 Counti

31、f和數(shù)據(jù)有效性搭配：例如，要把C列的數(shù)據(jù)有效性設(shè)置為不允許輸入重復(fù)的值。選中C列“數(shù)據(jù)”選項(xiàng)卡“數(shù)據(jù)有效性”“設(shè)置”選項(xiàng)卡允許“自定義”“公式”：=countif(C:C,C1)<=1確定，完成8、 注意，在跟條件格式、數(shù)據(jù)有效性搭配的時(shí)候，countif函數(shù)中的第二項(xiàng)數(shù)據(jù)，一般都選擇選中區(qū)域中發(fā)白的那個(gè)單元格，且不固定（沒有$符號），其他單元格就自動(dòng)填充起來了。9、 2010和2007版還有個(gè)加強(qiáng)的功能：countifs，可以數(shù)出符合多個(gè)條件的數(shù)據(jù)的個(gè)數(shù)。=countifs（區(qū)域，某值，區(qū)域，某值）其中區(qū)域和某值必須成對出現(xiàn)。第十講：SUMIF函數(shù)1、 =sumif(查找區(qū)域，查找條件

32、，加總區(qū)域)，如果加總區(qū)域和查找區(qū)域是一個(gè)區(qū)域,那加總區(qū)域可以不寫。2、 如果有往一些銀行卡存錢的記錄，其中有一卡存多次的情況，現(xiàn)在已經(jīng)有了一個(gè)不重復(fù)的銀行卡號的列，要求把每卡一共存了多少錢統(tǒng)計(jì)出來：=sumif(A:A,F2&”*”,B:B)，因?yàn)镋xcel是會(huì)自動(dòng)忽略15位以后的數(shù)字的，因此F2后面必須要跟&”*”才能正確統(tǒng)計(jì)。拖拽，完成。HI部門發(fā)生額總計(jì)一車間空格3、 要把所有一車間的發(fā)生額統(tǒng)計(jì)加總，但是我很淘氣，我把數(shù)據(jù)表格里的一車間后面加了一些奇怪的字符（比如lalala,daren），然后在空格里寫下了公式：=sumif（D2：D9，H2&”*”,F2：F

33、9）,還是如愿得到了正確的結(jié)果4、 Sumif函數(shù)就有極強(qiáng)的糾錯(cuò)功能。由于查找區(qū)域和加總區(qū)域默認(rèn)為相等大小的區(qū)域，所以當(dāng)查找區(qū)域已經(jīng)選好時(shí)，加總區(qū)域只要選中應(yīng)當(dāng)選中區(qū)域的左上角第一個(gè)單元格就可以了。5、 當(dāng)有多條件時(shí)，使用sumif應(yīng)該怎樣做呢？部門科目劃分發(fā)生額一車間郵寄費(fèi)空格二車間獨(dú)子費(fèi)二車間過橋過路費(fèi)二車間手機(jī)費(fèi)可以通過做輔助列來搞定。插入一列比如A列，使之=E2&F2,而數(shù)據(jù)在G列，在空格里寫=sumif（A:A,J5&K5,G:G）,就搞定了。6、 還有升級版的=sumifs（加總區(qū)域，查找區(qū)域1，查找條件1，查找區(qū)域2，查找條件2）7、 當(dāng)返回值為數(shù)字時(shí)，sumif

34、可以在一定程度上替代vlookup.比如我們手里有無數(shù)個(gè)產(chǎn)品的單價(jià)，但是某分銷商只銷售其中幾種產(chǎn)品，現(xiàn)在要把這幾種產(chǎn)品的單價(jià)找出來，就可以用sumif，當(dāng)然也可以用vlookup8、 現(xiàn)在要做一個(gè)出庫單的表格，后期要在這表格里輸入出庫記錄，要求制作數(shù)據(jù)有效性，使得出庫單中每種產(chǎn)品的總數(shù)不超過庫存表中該產(chǎn)品的數(shù)量。過程用文字很難描述，見下圖 首先做一個(gè)下拉箭頭，使產(chǎn)品這一列只能填庫存表中存在的產(chǎn)品。 設(shè)置數(shù)量這一列的數(shù)據(jù)有效性，公式為=sumif（F:F,F3，G:G）<=sumif(A:A,F3,B:B)第十一講：VLOOKUP函數(shù)1、 =vlookup(查找條件，查找與所要返回的數(shù)據(jù)所

35、在的區(qū)域，要返回的數(shù)據(jù)在第幾列，模糊匹配<TRUE，1>或者精確匹配<FALSE，0>)2、 選擇查找與需返回?cái)?shù)據(jù)所在區(qū)域時(shí)要注意，所查找的數(shù)據(jù)必須在該區(qū)域的最左側(cè)。3、 Vlookup只能在不重復(fù)的情況下進(jìn)行查找，如果有重復(fù)，他只會(huì)返回第一個(gè)值，后面他就不找了。4、 有些時(shí)候要查找某些公司的某些信息，雖然我們有數(shù)據(jù)源，但是別人給我們的表里的公司名稱往往是簡稱，而數(shù)據(jù)源里的公司名稱是全稱這時(shí)可以使用通配符*：=vlookup(A2&”*”,B:E,4,0)，就完成了。5、 模糊匹配什么時(shí)候可以用呢？只有在數(shù)據(jù)源里沒有這一項(xiàng)的時(shí)候才會(huì)用到模糊匹配。模糊匹配會(huì)在數(shù)據(jù)

36、源里找小于等于被查找數(shù)據(jù)的數(shù)據(jù)，并在這些數(shù)據(jù)中挑個(gè)最大的返回。利用這一點(diǎn)，可以用來計(jì)算工資：將提成比例劃分等級的下限寫出來，可以方便地使用模糊匹配找到銷售所處的提成等級。注意數(shù)據(jù)源里的被查找的那列（C列）是要按從小到大排序的。6、 由于文本格式的數(shù)字和數(shù)據(jù)格式的數(shù)字是不相等的，在工作中會(huì)造成一定的困難。例如而且由于是公共數(shù)據(jù)，不允許更改A列的格式，怎么辦？Excel認(rèn)為文本是不能加減乘除的，所以當(dāng)對文本格式的數(shù)字進(jìn)行加減乘除時(shí)，文本格式的數(shù)字會(huì)被強(qiáng)制變成數(shù)據(jù)格式；同理，數(shù)據(jù)格式的數(shù)字是不能連接其他字符的，如果連接了其他東西，就會(huì)被Excel當(dāng)成文本格式。所以我們可以再G4輸入公式：=VLOOK

37、UP(F4&"",$A$2:$C$6,3,0)，就可以了，雙引號中可以加*也可以不加，效果是一樣的。同樣的，如果數(shù)據(jù)源里是數(shù)字格式，而查找表里是文本格式，公式就變?yōu)?VLOOKUP(F4*1,$A$2:$C$6,3,0)，完成任務(wù)。如果想耍一下帥，也可以在F4前面加兩個(gè)負(fù)號。還有種情況，就是數(shù)據(jù)源里面的數(shù)字什么格式都有，擦找表里面也是什么格式都有，公式可以這么寫：=if(isna(vlookup(F4&"",$A$2:$C$6,3,0),vlookup(-F4,$A$2:$C$6,3,0),vlookup(F4&"&qu

38、ot;,$A$2:$C$6,3,0)，其中isna是指N/A錯(cuò)誤，當(dāng)然也可以用iserror代替。7、 Hlookup和vlookup用法一樣第十二講：Match與Index函數(shù)1、 當(dāng)使用右側(cè)的數(shù)據(jù)來查找左側(cè)的數(shù)據(jù)時(shí)，vlookup就傻眼了，例如用公司名稱來查找客戶ID這時(shí)候，就要其他兩個(gè)函數(shù)來幫忙了MATCH和INDEX.=MATCH(查找條件，查找區(qū)域，精準(zhǔn)匹配或者大于或者小于)，返回的值是一個(gè)數(shù)字，為在查找區(qū)域這一列里要查找的值是第幾個(gè)。=INDEX（查找區(qū)域，在該列里的第N個(gè)單元格），返回值是在查找區(qū)域里第N個(gè)單元格里的值。這里的區(qū)域都是某列。2、 當(dāng)需要返回多列結(jié)果的時(shí)候，看未來要

39、用到的數(shù)據(jù)是在某一列還是在某一行來判讀混合引用到底要鎖列還是鎖行。3、 認(rèn)識(shí)column函數(shù)。這個(gè)函數(shù)就很簡單，就是顯示某單元格的列號的。=column(某單元格)，返回值就是這個(gè)單元格所在列的列號，用數(shù)字表示。如果括號里什么都不寫，返回值為函數(shù)所在列的列號。第十四講：日期函數(shù)1、 日期的本質(zhì)是數(shù)字，例如數(shù)字1表示1900年1月1日。而每增加1表示增加一天。2、 要求B5單元格里的日期之后4個(gè)月的日期，即直接在月份上加4，需要先把日期拆開成年月日，然后在月份上加4，就得到=DATE(YEAR(B5),MONTH(B5)+C5,DAY(B5)3、 如果要求B13日期所在月份的最后一天的日期，怎么

40、辦？其實(shí)就是下個(gè)月第一天的前一天：=DATE(YEAR(B13),MONTH(B13)+1,1)-1，或者說是下個(gè)月的0號：=DATE(YEAR(B13),MONTH(B13)+1,0)4、 要求B21日期所在月份有多少天，怎么辦？延續(xù)上例中的公式，本月最后一天所顯示的日子就是本月的天數(shù)。=DAY(DATE(YEAR(B21),MONTH(B21)+1,0)5、 計(jì)算入職時(shí)間（B13）和離職時(shí)間（C13）之間的時(shí)間間隔，要用到datedif函數(shù)。=DATEDIF(B13,C13,"y")&"年"&DATEDIF(B13,C13,"

41、;ym")&"月"&DATEDIF(B13,C13,"md")&"日"，其中，“y”表示總共多少年，”ym”表示去除整年還有多少個(gè)月，”md”表示去除整月還有多少天。Datedif函數(shù)第一參數(shù)是較早時(shí)間，第二參數(shù)是較晚時(shí)間。6、 要求B13日期是那一年的第幾周第幾天。比如2012/3/10是2012年的第幾周第幾天？="第"&WEEKNUM(B13,2)&"周第"&WEEKDAY(B13,2)&"天"7、 TE

42、XT函數(shù)是一個(gè)可以將數(shù)字真正轉(zhuǎn)換為某種格式的文本的函數(shù)。一般情況下，通過修改單元格格式可以將數(shù)字轉(zhuǎn)化為日期格式，使他看起來像日期，但實(shí)質(zhì)上還是數(shù)字，如果進(jìn)行選擇性粘貼為值，就會(huì)露餡。但是TEXT是在實(shí)質(zhì)上將數(shù)字轉(zhuǎn)化。如果在C4單元格公式后面*1的話，文本的日期就直接轉(zhuǎn)化為數(shù)字41441。8、 EDATE() 計(jì)算出所指定月數(shù)之前或之后的日期EDATE(起始日期,月數(shù)) 月數(shù)為正,表示未來日期,月數(shù)為負(fù)表示過去日期.第十五講：條件格式與公式1、 在條件格式中使用公式時(shí)，選中要改變格式的一系列單元格，這時(shí)只有一個(gè)單元格是白色的，其他都是藍(lán)色。我們在編輯公式時(shí)，僅考慮條件是否能夠使白色單元格的格式改

43、變而進(jìn)行編輯，其他單元格在白色單元格編輯成功之后，格式會(huì)隨著改變。例如要將數(shù)量大于100的日期的單元格填充為紅色，那么公式為=D2>100，相當(dāng)于將本公式輸入向下拖拽。2、 如果是要將數(shù)量大于100的那一列都變成紅色，則不僅要考慮向下拖拽，還要考慮向右拖拽。否則會(huì)出現(xiàn)如下錯(cuò)誤：這是因?yàn)锽列和C列的紅色是根據(jù)E列和F列來判斷的，那么只需要在公式中將D列鎖定就可以了：3、 感覺在條件格式中只要公式的結(jié)果是true,格式就會(huì)變，結(jié)果是false,格式不會(huì)變。同樣true可以用1來代替，false可以用0來代替。第十六講：簡單文本函數(shù)1、 文本函數(shù)=left(某單元格，數(shù)字n)，表示取某單元格從

44、左邊開始的n個(gè)字符。2、 文本函數(shù)=right(某單元格，數(shù)字n)，表示取某單元格從右邊開始的n個(gè)字符。3、 文本函數(shù)=mid(某單元格，m，n)，表示從某單元格中字符串的第m個(gè)開始取n個(gè)字符。4、 要把下面的“江湖人稱”里的名字提取出來，怎么辦？在C3單元格里寫公式=mid(A3,4,3)，表示從“奔雷手文泰來”中的第四個(gè)字開始取，取三個(gè)，就可以得到“文泰來”這三個(gè)字。盡管“鴛鴦刀駱冰”他的名字只有兩個(gè)字，同樣可以用這種方法取到。5、 在很久之前，只有15位，最后一個(gè)數(shù)字表示性別，單數(shù)是男，雙數(shù)是女?，F(xiàn)在有18位，倒數(shù)第二個(gè)數(shù)表示性別?，F(xiàn)在要求把表示性別的數(shù)字取出來：輸入函數(shù)=RIGHT(LEFT(B13,17),1)就可以了，表示從左邊開始取17位數(shù)，這時(shí)老的數(shù)字都被取了，但是新的最后一位被篩掉了，這時(shí)從右邊開始取1位數(shù)就可以了。6、 函數(shù)=find(“某字符”，某單元格)，表示在某單元格里找某字符，返回?cái)?shù)值為這個(gè)字符為第幾個(gè)。7、 要從一些中提取用戶名可寫公式=left(F2,find(“”,