【造價(jià)必學(xué)】EXCEL技巧寶典

【造價(jià)必學(xué)】EXCEL技巧寶典第1節(jié)：EXCEL冷門快捷鍵一、Ctrl+I（設(shè)置傾斜格式）二、Ctrl+U（設(shè)置下劃線格式）三、Ctrl+；（設(shè)置應(yīng)用系統(tǒng)日期）四、Ctrl+Shift+；（設(shè)置應(yīng)用系統(tǒng)時(shí)間）五、Ctrl+Shift+1（設(shè)置數(shù)值格式）六、Ctrl+Shift+4（設(shè)置貨幣格式）設(shè)置格式快捷鍵全列表第2節(jié)：EXCEL格式冷知識(shí)1、套用表格格式后怎么取消?在制作表格時(shí)，很多小伙伴們會(huì)使用套用表格樣式，這樣就可以輕而易舉的制作漂亮的表格，但有時(shí)需要取消套用表格樣式，該如何操作呢？操作步驟：選中區(qū)域內(nèi)的任意單元格，之后再點(diǎn)擊【設(shè)計(jì)】——【工具】——【轉(zhuǎn)為普通區(qū)域】。具體操作如下：2、如何快速插入空行？這里幫主介紹一個(gè)比較簡(jiǎn)單的方法，就是選中行區(qū)域，之后按Shift鍵不放，使光標(biāo)變成雙向箭頭時(shí)，向下拉動(dòng)鼠標(biāo)即可。具體操作如下：3、排序之后如何恢復(fù)原來的排序？在Excel中，已經(jīng)排序好的數(shù)據(jù)被保存，是不能恢復(fù)原來的排序，但在排序之前你做了這些事情，是可以恢復(fù)原來的排序的。首先，在排序之前，添加輔助列，并輸入從小到大的自然數(shù)，之后按要求進(jìn)行排序，后面若想恢復(fù)的原來順序，可直接將輔助列按升序排序。具體操作如下：4、如何批量創(chuàng)建工作表？操作步驟：選中區(qū)域，點(diǎn)擊【插入】——【數(shù)據(jù)透視表】，將相應(yīng)的內(nèi)容字段拖到【篩選】字段，然后點(diǎn)擊【分析】——【數(shù)據(jù)透視表】——【選項(xiàng)】——【顯示報(bào)表篩選頁(yè)】即可。具體操作如下：第3節(jié)：EXCEL數(shù)據(jù)處理當(dāng)表格中有大量數(shù)據(jù)時(shí)，把數(shù)據(jù)折疊起來，需要看的時(shí)候再點(diǎn)擊瀏覽，就不會(huì)看的眼花繚亂。步驟：1、選中需折疊的數(shù)據(jù)，點(diǎn)擊【數(shù)據(jù)】-【分級(jí)顯示】-【創(chuàng)建組】，根據(jù)實(shí)際情況選擇【行】或【列】；2、點(diǎn)擊【-】可折疊數(shù)據(jù)，點(diǎn)擊【+】可打開數(shù)據(jù)。繪制與網(wǎng)格線重合的文本框直接在EXCEL中繪制文本框，需要經(jīng)過調(diào)整，文本框線才會(huì)與網(wǎng)格線重合。而在繪制文本框的同時(shí)按【Alt】鍵就可以繪制出與網(wǎng)格線重合的文本框。改變批注形狀插入批注大家應(yīng)該都會(huì)了，改變批注形狀大家知道怎么操作嗎？其實(shí)也是很簡(jiǎn)單的！步驟：1、點(diǎn)擊【文件】-【選項(xiàng)】-【快速訪問工具欄】，從下拉位置選擇【所有命令】-【更改形狀】，點(diǎn)擊【添加】。2、插入批注，點(diǎn)擊批注邊框，然后在左上角的快速訪問工具欄中，點(diǎn)擊【更改形狀】，就可以任意選擇批注的形狀了。有時(shí)我們需要給輸入的數(shù)值加上單位（如"立方米"等），少量的我們可以直接輸入，而大量的如果一個(gè)一個(gè)地輸入就顯得太慢了?？梢韵葘?shù)值輸入相應(yīng)的單元格中（注意:僅限于數(shù)值），然后在按住Ctrl鍵的同時(shí)，選取需要加同一單位的單元格，右鍵打開"單元格格式"對(duì)話框，在"數(shù)字"標(biāo)簽中，選中"分類"下面的"自定義"選項(xiàng)，再在"類型"下面的方框中輸入"#""立""方""米"，按下確定鍵后，單位（立方米）即一次性加到相應(yīng)數(shù)值的后面。二、巧妙輸入位數(shù)較多的數(shù)字如果向Excel中輸入位數(shù)比較多的數(shù)值（如身份證號(hào)碼），則系統(tǒng)會(huì)將其轉(zhuǎn)為科學(xué)計(jì)數(shù)的格式，除了可以將該單元格中的數(shù)值設(shè)置成"文本"格式。只要在數(shù)值的前面加上一個(gè)小單引號(hào)也可以（注意:'單引號(hào)必須是在英文狀態(tài)下輸入）。三、取消單元格鏈接將鏈接單元格格式更改為數(shù)值格式就可以取消單元格鏈接選擇包含鏈接的單元格，然后單擊"復(fù)制"按鈕，保持該格處于選中狀態(tài)，單擊鼠標(biāo)右鍵選擇"選擇性粘貼"命令選擇"數(shù)值"后單擊"確定"，單元格內(nèi)容就被單元格實(shí)際數(shù)值替代，與其它源文件的鏈接就刪除了。四、快速輸入拼音選中已輸入漢字的單元格，然后單擊"格式---拼音信息----顯示或隱藏"命令，選中的單元格會(huì)自動(dòng)變高，再單擊"格式----拼音信息----編輯"命令，即可在漢字上方輸入拼音。單擊"格式----拼音信息----設(shè)置"命令，可以修改漢字與拼音的對(duì)齊關(guān)系。五、按小數(shù)點(diǎn)對(duì)齊選中位數(shù)少的單元格，根據(jù)需要單擊格式工具欄上的"增加小數(shù)位數(shù)"按鈕多次，將不足位數(shù)補(bǔ)以0。選中位數(shù)少的單元格，右鍵單擊選擇"設(shè)置單元格格式"命令，在彈出的窗口中單擊"數(shù)字"標(biāo)簽，選中"數(shù)值"，在右面的"小數(shù)位數(shù)"中輸入需要的，程序就會(huì)自動(dòng)以0補(bǔ)足位數(shù)。對(duì)于位數(shù)多的單元格，如果設(shè)置了較少的小數(shù)位數(shù)，程序會(huì)自動(dòng)去掉后面的數(shù)字。六、對(duì)不同類型的單元格定義不同的輸入法在一個(gè)工作表中，通常既有數(shù)字，又有字母和漢字。于是，我們?cè)诰庉嫴煌愋偷膯卧駮r(shí)，需要不斷地切換中英文輸入法，這不但降低了編輯效率，而且讓人覺得麻煩。選擇需要輸入漢字的單元格區(qū)域，單擊"數(shù)據(jù)----有效性"命令，在"數(shù)據(jù)有效性"對(duì)話框中選擇"輸入法模式"選項(xiàng)卡，在"模式"下拉列表中選擇"打開"，單擊"確定"按鈕即可。選擇需要輸入字母或數(shù)字的單元格區(qū)域，單擊"數(shù)據(jù)→有效性"命令，選擇"輸入法模式"選項(xiàng)卡，在"模式"下拉列表中選擇"關(guān)閉(英文模式)"，單擊"確定"按鈕。1、批量錄入相同內(nèi)容選中要輸入內(nèi)容的單元格，輸入要添加的信息，按Ctrl+Enter即可把選中的單元格中全部填充好已輸入的內(nèi)容哦、批量打開工作簿有多個(gè)工作簿需要同時(shí)打開，你可千萬(wàn)別傻傻的一個(gè)個(gè)打開。同時(shí)選中要打開的工作簿，然后敲一下Enter鍵就全部打開了。3、批量關(guān)閉工作簿同樣也別傻傻的一個(gè)個(gè)點(diǎn)關(guān)閉，按住Shift鍵，點(diǎn)一下關(guān)閉就可以。4、批量填充空值選中單元格，按F5（或者Ctrl+G），打開定位，點(diǎn)擊定位條件，選擇空值，然后設(shè)定單元格中想添加的內(nèi)容，然后按Ctrl+Enter即可快速調(diào)整行高/列寬打開需要修改的excel表格，全選列內(nèi)容，光標(biāo)放在任意兩列之間，直接拖動(dòng)鼠標(biāo)到所需要的寬度即可，調(diào)整行高時(shí)則選中表格后，光標(biāo)放在任意兩行之間，拖動(dòng)鼠標(biāo)到所需要的行高。也可全選內(nèi)容，執(zhí)行【格式】--【行高/列寬】方法一：方法二：2、快速插入空行首先選中一列數(shù)據(jù)，光標(biāo)移動(dòng)到左下角，當(dāng)變?yōu)椤笆睍r(shí)，按【shift】+鼠標(biāo)左鍵，向下拖拽即可5、快速選中空單元格按【ctrl】+【G】進(jìn)行定位，選擇空格6、分散單元格快速填充【ctrl】+【G】快速選中空格后，輸入內(nèi)容后，按【ctrl】+【enter】即可7、行列快速轉(zhuǎn)換8、并排查看多個(gè)窗口視圖-并排查看 快速填充公式的三種方式2、最快求和3、設(shè)置列寬的3種方法5、以cm為單位設(shè)置行高跟列寬7、快速輸入當(dāng)天日期與時(shí)間8、如何輸入分?jǐn)?shù)？11、隱藏沒有數(shù)據(jù)區(qū)域12、填充合并單元格內(nèi)容13、保留整數(shù)跟保留小數(shù)14、定義名稱的3種方法15、管理名稱一、數(shù)字處理

1、取絕對(duì)值=ABS(數(shù)字)2、取整=INT(數(shù)字)3、四舍五入=ROUND(數(shù)字,小數(shù)位數(shù))二、判斷公式1、把公式產(chǎn)生的錯(cuò)誤值顯示為空公式：C2=IFERROR(A2/B2,"")說明：如果是錯(cuò)誤值則顯示為空，否則正常顯示。2、IF多條件判斷返回值公式：C2=IF(AND(A2<500,B2="未到期"),"補(bǔ)款","")說明：兩個(gè)條件同時(shí)成立用AND,任一個(gè)成立用OR函數(shù)。三、統(tǒng)計(jì)公式1、統(tǒng)計(jì)兩個(gè)表格重復(fù)的內(nèi)容公式:B2=COUNTIF(Sheet15!A:A,A2)說明：如果返回值大于0說明在另一個(gè)表中存在，0則不存在。2、統(tǒng)計(jì)不重復(fù)的總?cè)藬?shù)公式：C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))說明:用COUNTIF統(tǒng)計(jì)出每人的出現(xiàn)次數(shù)，用1除的方式把出現(xiàn)次數(shù)變成分母，然后相加。四、求和公式1、隔列求和公式：H3=SUMIF($A$2:$G$2,H$2,A3:G3)或=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)說明：如果標(biāo)題行沒有規(guī)則用第2個(gè)公式2、單條件求和公式：F2=SUMIF(A:A,E2,C:C)說明：SUMIF函數(shù)的基本用法3、單條件模糊求和公式：詳見下圖說明：如果需要進(jìn)行模糊求和，就需要掌握通配符的使用，其中星號(hào)是表示任意多個(gè)字符，如"*A*"就表示a前和后有任意多個(gè)字符，即包含A。4、多條件模糊求和公式：C11=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)說明：在sumifs中可以使用通配符*5、多表相同位置求和公式：b2=SUM(Sheet1:Sheet19!B2)說明：在表中間刪除或添加表后，公式結(jié)果會(huì)自動(dòng)更新。6、按日期和產(chǎn)品求和公式：F2=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)說明：SUMPRODUCT可以完成多條件求和五、查找與引用公式1、單條件查找公式公式1：C11=VLOOKUP(B11,B3:F7,4,FALSE)說明：查找是VLOOKUP最擅長(zhǎng)的，基本用法2、雙向查找公式公式：=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))說明：利用MATCH函數(shù)查找位置，用INDEX函數(shù)取值3、查找最后一條符合條件的記錄。公式：詳見下圖說明：0/(條件)可以把不符合條件的變成錯(cuò)誤值，而lookup可以忽略錯(cuò)誤值4、多條件查找公式:詳見下圖說明:公式原理同上一個(gè)公式5、指定區(qū)域最后一個(gè)非空值查找公式;詳見下圖說明：略6、按數(shù)字區(qū)域間取對(duì)應(yīng)的值公式：詳見下圖公式說明：VLOOKUP和LOOKUP函數(shù)都可以按區(qū)間取值，一定要注意，銷售量列的數(shù)字一定要升序排列。六、字符串處理公式1、多單元格字符串合并公式：c2=PHONETIC(A2:A7)說明：Phonetic函數(shù)只能對(duì)字符型內(nèi)容合并，數(shù)字不可以。2、截取除后3位之外的部分公式：=LEFT(D1,LEN(D1)-3)說明：LEN計(jì)算出總長(zhǎng)度,LEFT從左邊截總長(zhǎng)度-3個(gè)3、截取-前的部分公式:B2=Left(A1,FIND("-",A1)-1)說明：用FIND函數(shù)查找位置，用LEFT截取。4、截取字符串中任一段的公式公式:B1=TRIM(MID(SUBSTITUTE($A1,"",REPT("",20)),20,20))說明:公式是利用強(qiáng)插N個(gè)空字符的方式進(jìn)行截取5、字符串查找公式：B2=IF(COUNT(FIND("河南",A2))=0,"否","是")說明:FIND查找成功，返回字符的位置，否則返回錯(cuò)誤值，而COUNT可以統(tǒng)計(jì)出數(shù)字的個(gè)數(shù)，這里可以用來判斷查找是否成功。6、字符串查找一對(duì)多公式：B2=IF(COUNT(FIND({"遼寧","黑龍江","吉林"},A2))=0,"其他","東北")說明：設(shè)置FIND第一個(gè)參數(shù)為常量數(shù)組，用COUNT函數(shù)統(tǒng)計(jì)FIND查找結(jié)果七、日期計(jì)算公式1、兩日期相隔的年、月、天數(shù)計(jì)算A1是開始日期（2011-12-1），B1是結(jié)束日期(2013-6-10)。計(jì)算：相隔多少天？=datedif(A1,B1,"d")結(jié)果：557相隔多少月?=datedif(A1,B1,"m")結(jié)果：18相隔多少年?=datedif(A1,B1,"Y")結(jié)果：1不考慮年相隔多少月？=datedif(A1,B1,"Ym")結(jié)果：6不考慮年相隔多少天？=datedif(A1,B1,"YD")

結(jié)果：192不考慮年月相隔多少天？=datedif(A1,B1,"MD")結(jié)果：9datedif函數(shù)第3個(gè)參數(shù)說明："Y"時(shí)間段中的整年數(shù)。"M"時(shí)間段中的整月數(shù)。"D"時(shí)間段中的天數(shù)。"MD"天數(shù)的差。忽略日期中的月和年。"YM"月數(shù)的差。忽略日期中的日和年。"YD"天數(shù)的差。忽略日期中的年。2、扣除周末天數(shù)的工作日天數(shù)公式：C2=NETWORKDAYS.INTL(IF(B2<date(2015,1,1),date(2015,1,1),b2),date(2015,1,31),11)<span="">說明：返回兩個(gè)日期之間的所有工作日數(shù)，使用參數(shù)指示哪些天是周末，以及有多少天是周末。周末和任何指定為假期的日期不被視為工作日。1.工程量計(jì)算公式快捷地得出計(jì)算結(jié)果在很多情況下，造價(jià)人員在計(jì)算工程量時(shí)，需要列出及保留工程量的計(jì)算公式和計(jì)算備注，以方便后期的對(duì)賬。如何在輸入計(jì)算式和計(jì)算備注后，就能很方便地得出工程量計(jì)算結(jié)果呢？實(shí)用案例列述如下：首先，需選中顯示計(jì)算結(jié)果的單元格E2(可以理解為定位作用)，然后再按以下動(dòng)態(tài)圖演示：在此，解釋一下這個(gè)公式“=EVALUATE(SUBSTITUTE(SUBSTITUTE(Sheet3!D2,"[","*ISTEXT(""["),"]","]"")"))”的注意點(diǎn)：除公式中的黃色標(biāo)識(shí)“Sheet3!D2”的參數(shù)需要自行修改外，其他復(fù)制即可。其中Sheet3是標(biāo)簽名，D2是計(jì)算書所在單元格位置。切記：上述計(jì)算書備注須用英文格式中括號(hào)"[""]"。使用此函數(shù)的Excel的文件需以（*.XLSM）格式進(jìn)行保存，否則下次重新打開表格，該函數(shù)無法實(shí)現(xiàn)（及需重新定義該函數(shù)）。2.Datedif函數(shù)計(jì)算工期及租賃天數(shù)在大家在計(jì)算工程工期或周轉(zhuǎn)性材料租賃期時(shí)，是否為每個(gè)月的31日或30日所困擾？可能會(huì)用扳手指或翻日歷的笨方法計(jì)算兩個(gè)日期的相隔天數(shù)？其實(shí)Datedif函數(shù)就可以幫你很輕松解決。實(shí)用案例如下：筆者先簡(jiǎn)單解釋下以下表格中的單元格D2輸入的公式“=DATEDIF（B2，C2，"d"）“前兩個(gè)參數(shù)分別是開始日期和結(jié)束日期，第三個(gè)參數(shù)"d"是計(jì)算天數(shù)的參數(shù)，也可以改成"m"(計(jì)算月份)和"y"(計(jì)算年份)。3.Exact函數(shù)在快速查找修改前后表格的內(nèi)容差異在平時(shí)的造價(jià)工作中，大家一定會(huì)碰到這樣一個(gè)問題，在您已經(jīng)編制了工程量清單的格式（包括清單描述、單位等），需要其他算量同事填寫工程量。但他們完成填寫工程量的表格是否會(huì)調(diào)整或不小心修改過原表格內(nèi)容（如清單描述或單位等），你要是以一一核對(duì)或復(fù)核一下，那簡(jiǎn)直太費(fèi)時(shí)間了。其實(shí)Exact函數(shù)火眼金睛，很快識(shí)別出其中的差異。比如下表中的第2、3行的B、C列的內(nèi)容已有差異了，請(qǐng)看動(dòng)態(tài)演示：4.Vlookup函數(shù)實(shí)現(xiàn)清單套價(jià)一步到位Vlookup函數(shù)是一個(gè)功能十分強(qiáng)大的函數(shù)，他能幫助大家從一大堆錯(cuò)綜復(fù)雜的數(shù)據(jù)中查詢并提取你所需要的數(shù)據(jù)。下面舉一個(gè)例子，方便大家認(rèn)識(shí)并了解他。例如：施工單位在進(jìn)行投標(biāo)報(bào)價(jià)時(shí)，有很多單體工程分不同的清單表格進(jìn)行報(bào)價(jià)，而其實(shí)大部分的清單項(xiàng)目是相同的，在完成第一個(gè)單體工程的投標(biāo)報(bào)價(jià)后，再運(yùn)用此函數(shù)在其他單體清單中，可達(dá)到了事半功倍。又如某個(gè)變更簽證要參照工程合同（工程量清單為計(jì)價(jià)合同）的相應(yīng)清單項(xiàng)目進(jìn)行套價(jià)：在單元格E15中輸入公式“=VLOOKUP(B15,$B$3:$F$11,4,FALSE)”這個(gè)公式有幾個(gè)要點(diǎn)需要跟大家解釋一下，是下面動(dòng)態(tài)演示不能完全反映的，也是幫助大家理解這個(gè)函數(shù)，所以請(qǐng)大家務(wù)必仔細(xì)閱讀。VLOOKUP函數(shù)有四個(gè)參數(shù)：第1個(gè)參數(shù)的解釋是所需查詢的項(xiàng)目名稱。第2個(gè)參數(shù)的解釋是查詢的范圍，需要注意的是選取范圍的首列必須為第一個(gè)參數(shù)所對(duì)應(yīng)的項(xiàng)目名稱，還有在選取范圍的時(shí)候需要用到“$”符號(hào)，該符號(hào)在Excel中是鎖定單元格行或列的功能。這么做是為了方便后期批量復(fù)制單元格的公式。第3個(gè)參數(shù)的解釋是查詢數(shù)據(jù)在選取范圍的第幾列。第4個(gè)參數(shù)的解釋是選擇模糊查找或精確查找。請(qǐng)看動(dòng)態(tài)演示：5.Sumif函數(shù)簡(jiǎn)單乖巧完成工程量指標(biāo)匯總大家在統(tǒng)計(jì)工程量的各項(xiàng)技術(shù)指標(biāo)時(shí)，往往先需要匯總各項(xiàng)工程量（如混凝土的總和），該項(xiàng)工作特別麻煩，還容易出錯(cuò)，有什么方法可以一步到位呢？常用函數(shù)Sumif可以幫大家解決這個(gè)問題。案例如下：請(qǐng)注意，需要將各項(xiàng)工程量進(jìn)行分類(如11、12……）。筆者再解釋下單元格D14的公式“=SUMIF($C$2:$C$12,C14,$D$2:$D$12)”：第一個(gè)參數(shù)“$C$2:$C$12”和第三個(gè)參數(shù)”$D$2:$D$12“必須保持單元格行數(shù)對(duì)應(yīng)。比如本案例中"分類"列的起始行數(shù)為2，"工程量"列的起始行數(shù)也必須為2。同樣，"分類"列的結(jié)束行數(shù)為12，"工程量"列的結(jié)束行數(shù)也必須為12。另外，這兩個(gè)參數(shù)必須用”$”鎖定單元格，這么做是為了方便后期批量復(fù)制單元格的時(shí)候分類匯總數(shù)據(jù)不會(huì)隨粘貼單元格位置的不同而變化。下面請(qǐng)看動(dòng)態(tài)演示：6.Sumproduct函數(shù)一鍵匯總多戶型工程量大家在匯總多戶型工程量的時(shí)候是否還在用公式“=A戶型單戶工程量*A戶型戶數(shù)+B戶型單戶工程量*B戶型戶數(shù)+C戶型單戶工程量*C戶型戶數(shù)……”，一系列的操作下來估計(jì)會(huì)使大家眼花繚亂了，其實(shí)有一個(gè)捷徑，用Sumproduct函數(shù)就可以簡(jiǎn)單、高效地實(shí)現(xiàn)這一功能了。實(shí)用案例的動(dòng)態(tài)演示：解釋一下上面的公式“=SUMPRODUCT(C3:E3,F3:H3)”，大家可以理解為兩個(gè)數(shù)組(戶型組和每戶工程量組)的乘積求和公式。7.條件格式快速標(biāo)注各項(xiàng)報(bào)價(jià)最高(低)價(jià)對(duì)于業(yè)主或造價(jià)咨詢?nèi)藛T在對(duì)投標(biāo)文件進(jìn)行回標(biāo)分析的時(shí)候一定是時(shí)間緊、任務(wù)重，如何才能快速地將各家單位投標(biāo)單價(jià)中的最高價(jià)和最低價(jià)以不同顏色突出顯示，以方便下一步去判斷各投標(biāo)單位的投標(biāo)報(bào)價(jià)高低。條件格式就可以幫我們這個(gè)忙。下面請(qǐng)看案例：第1步，需要注意