EXCEL在人力資源管理中的應(yīng)用(案例與技巧)-HR貓貓

Excel在管理中的應(yīng)用案例01第1局部Excel基礎(chǔ)觀點(diǎn)名稱名稱可以代表一個(gè)單元格也許一個(gè)單元格地域，也許是常量，公式。名稱的定義：選中需要命名的單元格或地域，在界面左上角名稱框中輸入名稱后回車；名稱的刪除：插入－名稱－定義，選中需要?jiǎng)h除的名稱點(diǎn)擊刪除按鈕，點(diǎn)確定。名稱的引用：需要引用某單元格時(shí)輸入該單元格的名稱：＝名稱第1步：選中需要命名的某個(gè)單元格或單元格地域。第2步：在左上角名稱框輸入命名后回車。刪除已有命名：選擇“插入〞－“名稱〞－“定義〞；選中需要?jiǎng)h除的名稱，點(diǎn)擊“刪除〞按鈕。名稱命名的優(yōu)點(diǎn)：防備絕對(duì)引用的錯(cuò)誤對(duì)公式進(jìn)行文字化表述，讓公式更加容易理解可以在整個(gè)工作簿中通用，引用方便精選常用引用函數(shù)row:返回指定單元格的行號(hào)column:返回指定單元格的列標(biāo)match:返回查找值在查找范圍中的序號(hào)=match(查找值，查找范圍，0)其第三個(gè)參數(shù)為0，表示查找精確值address:返回單元格名稱，其參數(shù)為行號(hào)列標(biāo)或計(jì)算行號(hào)列標(biāo)的表達(dá)式=address(行號(hào)，列標(biāo))=address(1,1)此公式返回A1indirect:返回單元格的值.其參數(shù)為單元格名稱=indirect(“A1〞),假設(shè)A1=10,那么indirect返回10index:在某地域內(nèi)查找某個(gè)地址的值=index(查找地域，查找值所在的行號(hào)，查找值所在的列號(hào))offset：指定基點(diǎn)，指定位移量，獲得單元格引用單個(gè)單元格引用：=offset(基點(diǎn)單元格，向下移動(dòng)的行，向右移動(dòng)的列)地域的引用：=offset(基點(diǎn)單元格，向下移動(dòng)的行，向右移動(dòng)的列，地域包括的行數(shù)，地域包括的列數(shù))向上和向左移動(dòng)時(shí)，位移量為負(fù)值。=offset(A1,1,1)將獲得B2單元格的值動(dòng)向引用動(dòng)向引用是經(jīng)過引用函數(shù)實(shí)現(xiàn)對(duì)于單元格或地域的相對(duì)引用。它和相對(duì)引用的效果很相似，但比簡單的相對(duì)引用用途廣泛。常用的動(dòng)向引用的實(shí)現(xiàn)方法有：引用函數(shù)嵌套。比方offset和row,column嵌套；index和match，indirect,address嵌套等；引用函數(shù)與控件結(jié)合使用，此類控件包括組合框，轉(zhuǎn)動(dòng)條。動(dòng)向引用的作用：實(shí)現(xiàn)對(duì)于單元格的動(dòng)向引用；進(jìn)行動(dòng)向解析；制作動(dòng)向報(bào)表。運(yùn)算種類數(shù)值運(yùn)算：1+1=2邏輯運(yùn)算：1>0=TRUE在邏輯運(yùn)算中，TRUE=1，F(xiàn)ALSE=0我們利用邏輯運(yùn)算進(jìn)行條件判斷在Excel中常用的一些邏輯函數(shù)：if,and,or,not,iserror等and:只有當(dāng)所有條件全部知足，才會(huì)返回true的邏輯值語法：and(條件1，條件2，)or:知足其中任何一個(gè)條件，都會(huì)返回true的邏輯值語法：or(條件1，條件2，條件3,)函數(shù)調(diào)用的語法＝函數(shù)名稱〔參數(shù)1，參數(shù)2，〕精選絕對(duì)引用和相對(duì)引用：絕對(duì)引用：所引用的單元格不隨著公式的復(fù)制而移動(dòng)的引用方式。相對(duì)引用：所引用的單元格隨著公式的復(fù)制而移動(dòng)的引用方式。改變引用方式的方法：1)在公式欄中選中需要改變引用方式的單元格，按F4鍵給需要引用的單元格定義名稱，然后在公式中引用該名稱數(shù)組公式對(duì)單元格地域進(jìn)行多重計(jì)算的計(jì)算方式。與普通計(jì)算公式的區(qū)別是錄入公式結(jié)束后，需要同時(shí)按下ctrl+shift+enter，其特點(diǎn)是在公式兩端會(huì)出現(xiàn)一對(duì)大括號(hào)。如上圖例，使用一個(gè)公式計(jì)算出所有產(chǎn)品的金額合計(jì)，引用的是所有的單價(jià)和所有的數(shù)量，執(zhí)行的是多重計(jì)算。錯(cuò)誤提示Excel中存在錯(cuò)誤的種類，比方1/0=#DIV/0!,todas()=#NAMES!iserror是一個(gè)邏輯函數(shù)，用以判斷某個(gè)單元格內(nèi)的值是否是一個(gè)錯(cuò)誤，是錯(cuò)誤那么返回TRUE,不是錯(cuò)誤那么返回FALSE.iserror有時(shí)可以和if函數(shù)嵌套進(jìn)行一些較為復(fù)雜的判斷。有取值區(qū)間的隨機(jī)數(shù)=最小值＋〔最大值－最小值〕*rand()循環(huán)引用是單元格引用其自己的引用方式?？梢栽O(shè)置Excel允許進(jìn)行循環(huán)引用：工具-選項(xiàng)-重新計(jì)算：將迭代計(jì)算選項(xiàng)打鉤選中。行列互換〔函數(shù)方式〕：利用transpose函數(shù)+數(shù)組公式實(shí)現(xiàn)。首先選中行列數(shù)和原地域相反的一個(gè)地域；然后輸入transpose函數(shù)最后按下組合鍵ctrl+shift+enter第2局部Excel設(shè)置精選顯示當(dāng)前文件的完整路徑菜單地域－右鍵菜單－Web顯示菜單項(xiàng)全部菜單視圖－工具欄－自定義－選項(xiàng)－始終顯示整個(gè)菜單鼠標(biāo)移動(dòng)方向工具－選項(xiàng)－編寫－按Enter鍵后移動(dòng)方向隱藏界面要素工具－選項(xiàng)－視圖包括：網(wǎng)格線，轉(zhuǎn)動(dòng)條，工作表標(biāo)簽，行號(hào)列標(biāo)等。以顯示值為準(zhǔn)工具－選項(xiàng)－重新計(jì)算－以顯示精度為準(zhǔn)行的合計(jì)與列的合計(jì)有時(shí)出現(xiàn)不相等的情況?？梢圆捎靡韵路椒ń鉀Q。選擇“工具〞－“選項(xiàng)〞－“重新計(jì)算〞－“以顯示精度為準(zhǔn)〞，選中該選項(xiàng)。自定義序列工具－選項(xiàng)－自定義序列改變文件保存地址工具－選項(xiàng)－老例－默認(rèn)文件地址改變文件用戶名工具－選項(xiàng)－用戶名單元格自動(dòng)換行格式－單元格－對(duì)齊－自動(dòng)換行第3局部Excel基礎(chǔ)操作快速選中數(shù)據(jù)表的整行也許整列ctrl+shift+下箭頭/右箭頭快速選中地域精選ctrl+shift+8行列互換復(fù)制需要進(jìn)行行列互換的地域后，將光標(biāo)放置在數(shù)據(jù)表外面地址，選擇性粘貼－選中“轉(zhuǎn)置〞選項(xiàng)一個(gè)單元格內(nèi)容輸入為多行alt+回車鍵顯示公式ctrl+~凍結(jié)窗口選擇需要進(jìn)行凍結(jié)的單元格地址，選擇窗口－凍結(jié)窗格自定義格式語法：[大于條件值]格式；[小于條件值]格式；[等于條件值]格式；文本格式縮放數(shù)值0.00，，按百萬縮放“.〞0，按萬縮放0.00,按千縮放不復(fù)制隱藏的行或列a首先選中需要復(fù)制的被隱藏了一些行或列的表格地域；b然后點(diǎn)擊“編寫〞-“定位〞“定位條件〞，在其中選擇“可見單元格〞；c復(fù)制表格地域，粘貼即可。案例02一文本的辦理等長文本的切割從字符串的左邊取字符：=Left(字符串，文本長度)從字符串的右邊取字符：=Right(字符串，文本長度)從字符串的中間取字符：=Mid(字符串，文本初步地址，文本長度)不等長文本的切割第1步：選中要進(jìn)行切割的字符串地域。精選第2步：點(diǎn)擊“數(shù)據(jù)〞－“排列〞，在文本排列向?qū)е羞x擇適合的分開符。第3步：設(shè)置需要導(dǎo)入的列以及放置該列的地址。文本的合并=Concatenate(文本1，文本2，)使用連接符&：動(dòng)向表頭：="ABC公司"&YEAR(NOW())&"年"&MONTH(NOW())&"月報(bào)表"有效性選中需要設(shè)置有效性的地域，點(diǎn)擊“數(shù)據(jù)〞－“有效性〞，在有效性條件中選擇“序列〞，在本源中錄入或采用列表。精選輸入信息：事前提醒。出錯(cuò)警告：事后提示。如果待選列表不在當(dāng)前工作表中，需要首先為該列表定義名稱，然后在有效性本源框中輸入：“=該列表的名稱〞有效性的其他用法不允許錄入重復(fù)數(shù)據(jù)的有效性設(shè)定：countif(e:e,e12)=1輸入的內(nèi)容中必須包括某字符：=not(iserror(find("中國",g30)))二級(jí)選項(xiàng)：首先將一級(jí)選項(xiàng)的每個(gè)工程定義一個(gè)名稱，該名稱內(nèi)容包括相應(yīng)的二級(jí)工程；制作一級(jí)工程的有效性；制作二級(jí)工程的有效性：內(nèi)容為：=INDIRECT(g6)，其中g(shù)6為設(shè)定了有效性的一級(jí)選項(xiàng)所在的單元格。圈示無效數(shù)據(jù)。選擇“工具〞－“公式審核〞－“顯示公式審核工具欄〞，點(diǎn)擊“圈示無效數(shù)據(jù)〞按鈕。該工具可以將所有不吻合有效性設(shè)定的內(nèi)容圈示出來。二日期的辦理1.Datedif函數(shù)：用于計(jì)算兩個(gè)日期之間的年數(shù)，月數(shù)，天數(shù)。精選=Datedif(開始日期，結(jié)束日期，“y〞)第三個(gè)參數(shù)：“y〞：表示年數(shù)“m〞：表示月數(shù)“d〞：表示天數(shù)生日提醒=DATEDIF(TODAY(),DATE(YEAR(TODAY()),MONTH(G2),DAY(G2)),"d")某日期的星期=weekday(A2,2)返回的值是3，那么表示該日期是星期三。兩個(gè)日期間的工作日天數(shù)=networkdays(開始日期，結(jié)束日期，節(jié)假日列表)需要首先加載“解析工具庫〞才能使用此工具案例03數(shù)據(jù)盤問VLOOKUP〔〕VLOOKUP在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。VLOOKUP中的V代表垂直。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value為需要在數(shù)組第一列中查找的數(shù)值。Lookup_value可以為數(shù)值、引用或文本字符串。也可以理解為：兩表共有的索引字段。Table_array為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表?？梢允褂脤?duì)地域或地域名稱的引用，比方數(shù)據(jù)庫或列表。必須使得共有字段位于該范圍的第一列。col_index_num為需要調(diào)轉(zhuǎn)的數(shù)據(jù)位于第二個(gè)參數(shù)中定義的范圍的第幾列。必須為單純數(shù)值。range_lookup定義大體匹配或精確匹配。False或0:精確匹配；true或忽略或1：如果無法找到精確匹配的值，那么就查找并匹配比查找值小的最近似的值。2.HLOOKUP()Hlookup中的H是水平的意思，在橫向排列的表格中盤問數(shù)據(jù)用Hlookup，垂直排列的表格盤問用Vlookup函數(shù)。其參數(shù)含義與VLOOKUP近似。案例04盤問表信息盤問表利用有效性和VLOOKUP函數(shù)實(shí)現(xiàn)。利用有效性實(shí)現(xiàn)索引字段的切換。利用VLOOKUP函數(shù)盤問與索引字段相關(guān)的信息。精選案例05數(shù)據(jù)篩選自動(dòng)篩選〔略〕高級(jí)篩選由于自動(dòng)篩選只能篩選出簡單條件的數(shù)據(jù)，因此如要在復(fù)雜條件下進(jìn)行篩選就需要采用高級(jí)篩選的方式。在需要進(jìn)行篩選的數(shù)據(jù)表外設(shè)定篩選條件。在對(duì)話框中設(shè)定：列表地域?yàn)閿?shù)據(jù)表地域；條件地域?yàn)榉讲旁跀?shù)據(jù)表外部設(shè)定的條件地域，要包括字段名稱和條件所在的單元格地域。條件格式條件格式可以使得吻合特定條件的記錄按照某種設(shè)定的格式顯示。選中要進(jìn)行條件格式設(shè)定的單元格范圍執(zhí)行【格式】【條件格式】精選在條件格式設(shè)置界面進(jìn)行條件的設(shè)定：統(tǒng)計(jì)Frequency(需要進(jìn)行統(tǒng)計(jì)的數(shù)據(jù)地域，分段點(diǎn))對(duì)數(shù)據(jù)進(jìn)行區(qū)間統(tǒng)計(jì)。此函數(shù)需要和數(shù)組公式一起使用。此函數(shù)是根據(jù)數(shù)值的大小劃分區(qū)間進(jìn)行統(tǒng)計(jì)的。列出需要解析的薪水區(qū)間，選中需要計(jì)算統(tǒng)計(jì)結(jié)果的單元格地域，輸入函數(shù)：按下組合鍵：ctrl+shift+enter排名次=rank(需排名數(shù)值，排名數(shù)據(jù)范圍，排序方式)精選排序方式：0或忽略降序，否那么為升序。案例06窗體控件的使用下拉菜單的制作。選擇“視圖〞－“工具欄〞－“窗體〞，出現(xiàn)“窗體〞工具條，在工具條上選擇“組合框〞。按下鼠標(biāo)左鍵，拖放出適宜的大小后釋放鼠標(biāo)，出現(xiàn)下拉菜單形狀。選中該下拉菜單，右鍵選擇“設(shè)置控件格式〞在“控制〞菜單上，去選擇相應(yīng)的數(shù)據(jù)源?！叭S陰影〞選項(xiàng)是下拉菜單顯示效果設(shè)定。精選單元格鏈接：該處指定的單元格中將存儲(chǔ)在控件中被選中的工程在數(shù)據(jù)源中的序列號(hào)。我們經(jīng)常利用該序列號(hào)引用控件選中的值，還可以利用該值制作動(dòng)向圖表。根據(jù)報(bào)銷單上填報(bào)的日期自動(dòng)生成該報(bào)銷單的起止日期。使用最大值函數(shù)MAX和最小值函數(shù)MIN。制作單項(xiàng)選擇按鈕。使用窗體工具條上的“選項(xiàng)按鈕〞界面定制行號(hào)列標(biāo)，轉(zhuǎn)動(dòng)條，工作表標(biāo)簽的隱藏。選擇“工具〞－“選項(xiàng)〞－“視圖〞，將窗口選項(xiàng)中的行號(hào)列標(biāo)，水平轉(zhuǎn)動(dòng)條，垂直轉(zhuǎn)動(dòng)條，工作表標(biāo)簽選項(xiàng)去掉。精選案例07匯總報(bào)表1.對(duì)于散布在同一文件中的表格匯總：首先創(chuàng)立格式完全一致的匯總表的表結(jié)構(gòu)在匯總表需要匯總的工程上輸入：=sum()將光標(biāo)放入括號(hào)，用鼠標(biāo)選擇第一張工作表，按下shift鍵，再采用最后一張工作表，然后選擇需要匯總的單元格，回車。將該公式復(fù)制到其他需要匯總的單元格即可實(shí)現(xiàn)整張表的匯總。對(duì)于散布在不同文件中的表格匯總：首先創(chuàng)立格式完全一致的匯總表的表結(jié)構(gòu)將光標(biāo)置于初步報(bào)表工程上選擇【數(shù)據(jù)】－【合并計(jì)算】“引用地址〞選擇各個(gè)需要被合并的報(bào)表，點(diǎn)擊“增添〞，直至所有需要被合并的報(bào)表增添完成。精選需要?jiǎng)h除一張表格時(shí)，只需要在合并計(jì)算界面上選中該表格，點(diǎn)擊刪除按鈕。當(dāng)子表格數(shù)據(jù)發(fā)生變化需要更新匯總表數(shù)據(jù)時(shí)，只需執(zhí)行：數(shù)據(jù)-合并計(jì)算，進(jìn)入合并計(jì)算界面后直接點(diǎn)擊確定退出該界面即可完成刷新數(shù)據(jù)的操作。案例08報(bào)表保護(hù)單元格地域的保護(hù)。首先取消不需要保護(hù)的單元格地域的鎖定狀態(tài)。右鍵“設(shè)置單元格格式〞，在“保護(hù)〞標(biāo)簽上將鎖定選項(xiàng)取消。如果需要隱藏單元格公式內(nèi)容，可以將隱藏選項(xiàng)選中。選擇“工具〞－“保護(hù)〞－“保護(hù)工作表〞，輸入保護(hù)密碼。隱藏公式右鍵菜單－設(shè)置單元格格式－保護(hù)－選中隱藏選項(xiàng)，然后執(zhí)行對(duì)工作表的保護(hù)精選隱藏工作表：隱藏工作表后需要保護(hù)工作簿。案例09按鈕的制作選擇“視圖〞－“工具欄〞－“窗體〞，調(diào)出窗體工具條。選擇工具條上的命令按鈕。繪制出按鈕的根本形狀后，自動(dòng)跳出“指定宏〞對(duì)話框，點(diǎn)擊“新建〞按鈕，出現(xiàn)VBA編程界面。在Sub和EndSub之間輸入：sheets(“要跳轉(zhuǎn)的工作表名稱〞).select，關(guān)閉該窗口。選中該按鈕，右鍵菜單“編寫文字〞，可以更正按鈕名稱。案例10財(cái)務(wù)報(bào)表利用excel創(chuàng)立財(cái)務(wù)報(bào)表，要注意報(bào)表之間的勾稽關(guān)系。一方面是出于高效率管理報(bào)表的考慮，另一方面，可以幫助我們解析報(bào)表中的數(shù)據(jù)，并快速檢查出報(bào)表中的可能錯(cuò)誤。為了可以自動(dòng)維護(hù)報(bào)表間的數(shù)據(jù)關(guān)系，我們經(jīng)常需要使用單元格鏈接，將所有的表鏈接起來。注意鏈接方式請(qǐng)使用并聯(lián)而非串通。串通致使鏈接關(guān)系更加復(fù)雜且難以維護(hù)，而并聯(lián)的方式將只有一個(gè)統(tǒng)一的數(shù)據(jù)源，鏈接關(guān)系簡單不容易出錯(cuò)，而且易于管理。比方損益表，財(cái)產(chǎn)負(fù)債表，現(xiàn)金流量表，需要使用公式和鏈接使得這些表鏈接起來，該鏈接精選關(guān)系可以自動(dòng)維護(hù)和更新多表之間的勾稽關(guān)系，時(shí)刻可以保證報(bào)表數(shù)據(jù)之間的勾稽的正確性。〔詳盡公式參見電子文檔〕。案例11表格的幾各種類數(shù)據(jù)表可以分為數(shù)據(jù)列表，二維表，多維表和帶有格式的報(bào)表等幾各種類。是否與數(shù)是否需要數(shù)據(jù)表種類操作目標(biāo)匯總方法據(jù)源有鏈重復(fù)制作接關(guān)系多字段數(shù)據(jù)記錄疊加并導(dǎo)入外部數(shù)據(jù)+SQL語句不需要有列表按表名劃分二維表多個(gè)二維表透視表多維數(shù)據(jù)地域合并不需要有生成透視表同一文件內(nèi)數(shù)據(jù)匯總=sum(begin:end!B2)不需要有的報(bào)表不同文件中數(shù)據(jù)匯總合并計(jì)算不需要有的報(bào)表建議更正原表的結(jié)構(gòu)也許僅非標(biāo)準(zhǔn)數(shù)據(jù)匯總作為終端報(bào)表，上層再成立需要無表一個(gè)數(shù)據(jù)源表作為收集數(shù)據(jù)使用案例12數(shù)據(jù)透視表應(yīng)用數(shù)據(jù)透視表進(jìn)行各類數(shù)據(jù)解析。選擇“數(shù)據(jù)〞－“數(shù)據(jù)透視表和數(shù)據(jù)透視圖〞，進(jìn)入透視表設(shè)置向?qū)?。精選點(diǎn)擊“下一步〞，選擇正確的數(shù)據(jù)范圍。點(diǎn)擊“下一步〞，選擇“布局〞按鈕。在如下列圖界面上，將右側(cè)的字段拖入左側(cè)相應(yīng)地域內(nèi)。形成如下列圖的布局，點(diǎn)擊確定按鈕。生成如下的透視表后，將鼠標(biāo)懸停在想要移動(dòng)的字段上，鼠標(biāo)左鍵按下，將字段拖放到其他地域，生成自己需要的數(shù)據(jù)顯示。精選數(shù)據(jù)百分比顯示：右鍵菜單－字段設(shè)置－選項(xiàng)，將數(shù)據(jù)顯示方式改為“占同列數(shù)據(jù)總和的百分比〞同時(shí)顯示數(shù)據(jù)與百分比：在【布局】里將銷售收入兩次拖入數(shù)據(jù)地域?qū)⑵渲幸粋€(gè)銷售收入改為百分比顯示在報(bào)表工程上輸入新的名稱可以更正工程名稱將報(bào)表工程拖拽到列標(biāo)題地址，可以將數(shù)值和百分比改為按列排列篩選數(shù)據(jù)：點(diǎn)擊字段名稱后邊的下拉菜單，可以對(duì)字段內(nèi)容進(jìn)行篩選。顯示明細(xì)數(shù)據(jù)：雙擊需要查察明細(xì)的數(shù)據(jù)，在新的工作表上將顯示組成此匯總數(shù)據(jù)的所有明細(xì)數(shù)據(jù)。對(duì)數(shù)據(jù)進(jìn)行排序：把光標(biāo)放在行地址的報(bào)表工程上，【右鍵菜單】【字段設(shè)置】，【高級(jí)】按鈕，左側(cè)可以進(jìn)行排序設(shè)置。按照日期分組：將日期放入行地域，【右鍵菜單】【組及顯示明細(xì)數(shù)據(jù)】【組合】在【步長】中選擇需要的分組標(biāo)準(zhǔn)按照數(shù)值分組：將銷售收入放入行地域，【右鍵菜單】【組及顯示明細(xì)數(shù)據(jù)】【組合】在【步長】中選擇需要的分組標(biāo)準(zhǔn)插入計(jì)算字段：精選在【名稱】后給計(jì)算字段定義名稱在【公式】后輸入計(jì)算字段的計(jì)算公式，可以引用下面的字段自動(dòng)更新：【右鍵菜單】【表格選項(xiàng)】【翻開時(shí)刷新】可以讓報(bào)表始終有更新后的數(shù)據(jù)顯示。透視圖可以單獨(dú)生成數(shù)據(jù)透視圖，也可以基于現(xiàn)有的透視表生成透視圖。如果已經(jīng)有現(xiàn)成的透視表，經(jīng)過點(diǎn)擊數(shù)據(jù)透視表工具條上的圖表按鈕可以生成一個(gè)透視圖。精選經(jīng)過拖動(dòng)透視圖上的各個(gè)字段到右側(cè)也許底部的地址，可以方便地改變圖表組織數(shù)據(jù)的方式。隱藏透視圖字段按鈕：把光標(biāo)懸停在任意字段上，右鍵菜單，選擇【隱藏?cái)?shù)據(jù)透視圖字段按鈕】，即可以將字段按鈕隱藏；再次顯示字段按鈕：點(diǎn)擊透視圖工具條上第一項(xiàng)，在出現(xiàn)的下拉菜單中選擇【隱藏?cái)?shù)據(jù)透視圖字段按鈕】如果有些數(shù)據(jù)無法在數(shù)據(jù)透視表內(nèi)局部析，可以引用透視表的數(shù)據(jù)作為解析的基礎(chǔ)。制作試算平衡表精選點(diǎn)擊“數(shù)據(jù)〞并拖拽到“匯總〞隱藏?cái)?shù)據(jù)透視表中的匯總字段或精選單元格格式自定義功能格式設(shè)定為：[紅色]"借貸不平衡";[紅色]"金額借貸不平衡"；[藍(lán)色]〞借貸平衡〞Ctrl+拖拽標(biāo)簽來復(fù)制工作表編制損益表創(chuàng)立損益表格式精選創(chuàng)立IS嵌套函數(shù)IS類函數(shù)，可以查驗(yàn)數(shù)值的種類并根據(jù)參數(shù)取值返回TRUE或FALSE。函數(shù)如果為下面的內(nèi)容，那么返回TRUEISBLANK值為空白單元格。ISERR值為任意錯(cuò)誤值〔除去#N/A〕。ISERROR值為任意錯(cuò)誤值〔#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!〕。ISLOGICAL值為邏輯值。ISNA值為錯(cuò)誤值#N/A〔值不存在〕。ISNONTEXT值為不是文本的任意項(xiàng)〔注意此函數(shù)在值為空白單元格時(shí)返回TRUE〕。ISNUMBER值為數(shù)字。ISREF值為引用。ISTEXT值為文本。創(chuàng)立財(cái)務(wù)比率銷售毛利率=銷售毛利/銷售凈額——毛利率大，表示經(jīng)營能力強(qiáng)。銷售本錢率=銷售本錢/銷售凈額——本錢率低，表示經(jīng)營能力好。凈利率率=凈利潤/銷售凈額——凈利潤率越高，表示經(jīng)營能力強(qiáng)。營業(yè)比率=〔銷售本錢+營業(yè)費(fèi)用〕/銷售凈額，營業(yè)比率越低，表示經(jīng)營能力越強(qiáng)營業(yè)費(fèi)用率=銷售費(fèi)用/銷售凈額——營業(yè)費(fèi)用越低，表示經(jīng)營績效越好。精選用MAX、MIN函數(shù)顯示制表日期MAX，返回一組值中的最大值MIN，返回一組值中的最小值案例13多重合并計(jì)算數(shù)據(jù)如下列圖格式的即為二維表：現(xiàn)在我們需要將數(shù)個(gè)格式相同的二維表匯總為一張表格，且可以劃分不同表格屬性進(jìn)行分析，比方三張表分別為北京，上海，深圳分公司的表格。我們使用的方法是利用透視表多重合并計(jì)算數(shù)據(jù)地域的功能。選擇數(shù)據(jù)-數(shù)據(jù)透視表和數(shù)據(jù)透視圖精選選擇第3個(gè)數(shù)據(jù)源種類：多重合并計(jì)算數(shù)據(jù)地域，點(diǎn)擊下一步按鈕：在出現(xiàn)的界面上選擇：自定義字段選擇需要被合并的表格地域，點(diǎn)擊增添將其增添到所有地域；將頁字段數(shù)據(jù)改為1，在項(xiàng)標(biāo)志處輸入該表格的標(biāo)志，使用相同的方法將其他需要合并的表格全部增添。精選透視表生成后如下列圖所示，已經(jīng)將多個(gè)表格合并成一個(gè)：雙擊字段名，可以對(duì)字段名進(jìn)行更正：案例14回歸與預(yù)測回歸解析說明事物之間相互影響的關(guān)系，主要用于解析單個(gè)因變量怎樣受一個(gè)或多個(gè)自變量影響的。比方某地空調(diào)的銷量與氣溫的變化之間的關(guān)系?？梢杂媒y(tǒng)計(jì)獲得的歷史數(shù)據(jù)，對(duì)未來的數(shù)據(jù)進(jìn)行預(yù)測?？梢岳盟鼛椭?jīng)營，財(cái)務(wù)，銷售，營銷等方面的決策。趨勢線預(yù)測根據(jù)歷史數(shù)據(jù)制作出折線圖選中圖表中的折線，右鍵菜單－增添趨勢線選擇預(yù)測種類〔可以先選擇默認(rèn)的線性〕切換到選項(xiàng)標(biāo)簽，根據(jù)需要預(yù)測的期間數(shù)，在“前推〞處輸入預(yù)測期間個(gè)數(shù)，并選中“顯示公式〞和“顯示R平方值〞選項(xiàng)5)根據(jù)出現(xiàn)的回歸系數(shù)〔R平方〕判斷預(yù)測種類是否適合，R平方越湊近1越好〔多項(xiàng)式除外，需要考慮業(yè)務(wù)數(shù)據(jù)是否存在較大的波動(dòng)，如果沒有那么不適用多項(xiàng)式解析種類〕根據(jù)回歸方程求預(yù)測值移動(dòng)平均沒有R平方，不適用此解析方法。精選Correl函數(shù)判斷相關(guān)性。相關(guān)系數(shù)在0.8以上為強(qiáng)相關(guān)，0.6以上為弱相關(guān)。案例15-16數(shù)據(jù)解析與圖表解析平時(shí)工作中主要用到的數(shù)據(jù)解析有：組成：局部與整體比率關(guān)系。序列：在一個(gè)時(shí)間序列內(nèi)進(jìn)行的比較與解析。差別：估算與實(shí)際數(shù)的版本差別等。增長：增長率與增速，進(jìn)行趨勢預(yù)測。勾稽：數(shù)據(jù)之間固有的相互聯(lián)系的屬性，利用此屬性進(jìn)行數(shù)據(jù)合理性的判斷。圖表用于形象地展示數(shù)據(jù)。利用圖表可以更好地對(duì)業(yè)務(wù)數(shù)據(jù)進(jìn)行解析。差別解析：利用上面的表格制作面積圖，數(shù)據(jù)地域選擇C25:D31精選選中B公司數(shù)據(jù)系列，右鍵菜單“數(shù)據(jù)系列格式〞將其內(nèi)部〔填充色〕和邊框色都改為“無〞精選圖表上將只顯示差別局部，如下列圖：雙坐標(biāo)圖表：利用下面的數(shù)據(jù)制作雙坐標(biāo)圖表：選擇“插入〞-“圖表〞，進(jìn)入圖表向?qū)?，選擇柱形圖，第一種子圖表種類簇狀柱形圖。精選選擇“視圖〞-“工具欄〞-“圖表〞，調(diào)出圖表工具條：點(diǎn)開圖表工具條上下拉菜單，選擇系列“完成率〞點(diǎn)擊圖表工具條上“數(shù)據(jù)系列格式〞按鈕：在數(shù)據(jù)系列格式界面上，切換到坐標(biāo)軸標(biāo)簽，將系列繪制在由“主坐標(biāo)軸〞改為“次坐標(biāo)軸〞精選再次選中系列“完成率〞，右鍵菜單項(xiàng)選擇擇“圖表種類〞選擇折線圖，點(diǎn)確定：精選雙坐標(biāo)圖表制作完成。氣泡圖：利用下表制作氣泡圖：在圖表向?qū)е羞x擇“氣泡圖〞因?yàn)闅馀輬D沒有分類軸〔其橫坐標(biāo)為序列號(hào)〕，我們需要增添數(shù)據(jù)表記，點(diǎn)擊下一步，切換到數(shù)據(jù)標(biāo)志標(biāo)簽，將系列名稱打勾：精選縱坐標(biāo)表示%Growth,氣泡大小表示Cost:單元格嵌入式圖表：利用下表制作如圖中所示的嵌入式圖表精選在單元格中敲入如下公式：=REPT("|",B2/400)其中，除數(shù)的值由源數(shù)據(jù)的大小決定，源數(shù)據(jù)大，除數(shù)應(yīng)相應(yīng)變大，其意義在于減少豎線的數(shù)量，使得單元格可以容納由一系列豎線組成的圖表。復(fù)合餅圖：當(dāng)數(shù)據(jù)系列內(nèi)值的大小差別較大時(shí)，可以制作復(fù)合餅圖以便數(shù)據(jù)顯示更加清楚，如下列圖所示：選擇餅圖中的復(fù)合餅圖子圖表種類：點(diǎn)擊下一步，切換到圖例標(biāo)簽，將顯示圖例的勾去掉：切換到數(shù)據(jù)標(biāo)志標(biāo)簽，把類又名稱和百分比打勾：精選選中數(shù)據(jù)系列，右鍵菜單，選擇數(shù)據(jù)系列格式：切換到選項(xiàng)標(biāo)簽，將第二繪圖區(qū)包含最后的值改為：4精選復(fù)合餅圖制作完成。圖表組合：一個(gè)圖表中的不同數(shù)據(jù)系列可以采用不同的圖表種類顯示，如下列圖：首先制作一個(gè)柱形圖：在圖表工具條上選擇數(shù)據(jù)系列Compensation:精選在圖表工具條上選擇數(shù)據(jù)系列格式按鈕：在數(shù)據(jù)系列格式界面上，切換到坐標(biāo)軸標(biāo)簽，將主坐標(biāo)軸改為次坐標(biāo)軸：右鍵點(diǎn)擊Compensation,選擇圖表種類：將其圖表種類改為折線圖；用同樣的方式操作Production系列，將其圖表種類改為面積圖；完成圖表組合的制作。精選下拉菜單式圖表：寫入如下的公式：index函數(shù)查找出的值由A9單元格來指定。制作三維餅圖，其數(shù)據(jù)地域?yàn)锳3:I3,A9:I9,需要手工采用。點(diǎn)擊“視圖〞-“工具欄〞-“窗體〞，調(diào)出窗體工具條：在窗體工具條上選擇組合框，在餅圖上拖放出一個(gè)組合框：精選右鍵選中組合框，在菜單中選擇“設(shè)置控件格式〞：在控制標(biāo)簽中做如下設(shè)置：其中單元格鏈接為存儲(chǔ)控件選中工程序號(hào)的單元格。完成下拉菜單式圖表的制作：精選帶有轉(zhuǎn)動(dòng)條的圖表：該圖表中可以隨著點(diǎn)擊轉(zhuǎn)動(dòng)條的動(dòng)作而動(dòng)向翻看源數(shù)據(jù)中的大量數(shù)據(jù)。定義兩個(gè)動(dòng)向引用的名稱：Period:=offset('7.動(dòng)向圖表'!$A$1,'7.動(dòng)向圖表'!$D$1,0,10,1)Data:=offset('7.動(dòng)向圖表'!$B$1,'7.動(dòng)向圖表'!$D$1,0,10,1)制作折線圖，選擇系列標(biāo)簽，刪除period系列：將“值〞和“分類〔X〕軸標(biāo)志〞設(shè)置為如下列圖內(nèi)容：精選調(diào)出窗體工具條，在圖表外繪制轉(zhuǎn)動(dòng)條，右鍵選中轉(zhuǎn)動(dòng)條，選擇設(shè)置控件格式：在單元格鏈接中設(shè)置為D1:精選甘特圖如果想制作如下列圖的甘特圖，需要以下4列數(shù)據(jù)：首先制作聚積條形圖：點(diǎn)擊下一步，切換到系列標(biāo)簽，刪除結(jié)束日期系列：精選生成的條形圖如下列圖所示：下面變換縱坐標(biāo)的工程排列次序：雙擊縱坐標(biāo)，切換到刻度，將分類次序反轉(zhuǎn)和數(shù)據(jù)軸交錯(cuò)于最大分類選項(xiàng)打勾。精選此時(shí)縱坐標(biāo)次序已經(jīng)反轉(zhuǎn)雙擊開始日期數(shù)據(jù)系列，在數(shù)據(jù)系列格式對(duì)話框中將邊框和內(nèi)部都改選為無，此時(shí)開始日期數(shù)據(jù)系列隱藏。此時(shí)需要將橫坐標(biāo)開始日期改為真切的工程開始日期：在excel中，每一個(gè)日期都對(duì)應(yīng)一個(gè)數(shù)值，選中B2,查察2021-7-1對(duì)應(yīng)數(shù)值為39630精選雙擊橫坐標(biāo)，切換到刻度，將最小值改為39630，同樣將橫坐標(biāo)最大值改為真切的工程結(jié)束日期。甘特圖制作完成。案例17動(dòng)向圖表選中北京，深圳，上海以下的地域，分別定義北京，深圳，上海的名稱選擇視圖-工具欄-窗體，選擇選項(xiàng)按鈕，畫出如下三個(gè)選項(xiàng)按鈕：精選右鍵選中選項(xiàng)按鈕，在右鍵菜單中選擇設(shè)置控件格式，在跳出的對(duì)話框中選擇控制標(biāo)簽，在單元格鏈接中選擇A16：在A17單元格中輸入公式：=choose(a16,〞北京〞,〞上海〞,〞深圳〞)，為A17定義名稱：choose在A2,A3,A4單元格中輸入以下內(nèi)容：在B16單元中寫入以下公式，并復(fù)制到該行1-12月的單元格中：=VLOOKUP(A3,INDIRECT(choose),COLUMN()-1,0)依據(jù)此數(shù)據(jù)表制作雙曲線圖：精選案例18比率解析利用透視表靈活的特性，將兩個(gè)結(jié)構(gòu)完全相同的透視表相除，即獲得可以進(jìn)行任意兩個(gè)因素間的比率解析圖表。案例19邏輯判斷1.IF(條件判斷，如果條件知足那么返回的結(jié)果，如果條件不知足那么返回的結(jié)果)根據(jù)工齡計(jì)算員工年假。年假規(guī)那么：規(guī)定：公司工齡小于1年的，享受10天年假；大于1年小于10年的，工齡每增加一年，年假增加1天；增長到20天不再增加。使用IF函數(shù)嵌套實(shí)現(xiàn)。單元格F3為工齡。=IF(F3<1,10,IF(F3<10,9+F3,20))IF和OR，AND嵌套使用用于執(zhí)行更加復(fù)雜的判斷if(or(條件1，條件2，)，假設(shè)條件知足那么返回的結(jié)果，假設(shè)條件不知足那么返回的結(jié)果)if(and(條件1，條件2，)，假設(shè)條件知足那么返回的結(jié)果，假設(shè)條件不知足那么返回的結(jié)果)計(jì)算薪水及所得稅=-IF((K3-1600)>100000,(K3-1600)*0.45-15375,IF((K3-1600)>80000,(K3-1600)*0.4-10375,IF((K3-1600)>60000,(K3-1600)*0.35-6375,IF((K3-1600)>40000,(K3-1600)*0.3-3375,IF((K3-1600)>20000,(K3-1600)*0.25-1375,IF((K3-1600)>5000,(K3-1600)*0.2-375,IF((K3-1600)>2000,(K3-1600)*0.15-125,IF((K3-1600)>500,(K3-1600)*0.1-25,(K3-1600)*0.05))))))))自動(dòng)計(jì)算加班費(fèi)=I