【造價必學】EXCEL技巧寶典

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

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

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