Excel2010高級使用技巧

會計學1Excel2010高級使用技巧Contents一、數(shù)據(jù)錄入和格式化工作表二、公式與函數(shù)的應用及案例三、圖表及打印輸出及其它設置四、數(shù)據(jù)分析及管理第1頁/共42頁1、Excel2010的數(shù)據(jù)錄入技巧；2、自定義數(shù)字格式；3、Excel2010條件格式設置；4、數(shù)據(jù)有效性；5、EXCEL多工作表鏈接、凍結與并排；6、EXCEL工作表的數(shù)據(jù)保護功能；一、數(shù)據(jù)錄入和格式化工作表第2頁/共42頁1.1、數(shù)據(jù)錄入的相關技巧說明性，解釋性的數(shù)據(jù)描述，非數(shù)值類型的。如，員工編號、姓名、出生年月、手機號碼常常用于各種數(shù)學計算的數(shù)據(jù)。如：工資、學生成績、銷售額等是以“=”號開頭，它可以是簡單的數(shù)學式，也可以是包含各種Excel函數(shù)的式子數(shù)值文本公式第3頁/共42頁數(shù)據(jù)錄入技巧靈活使用TAB、ALT、ENTER鍵錄入分數(shù)：零+空格+分數(shù)錄入文本格式數(shù)字（郵編、電話等）：單引號+錄入內容輸入當前日期、時間：Ctrl+;和Ctrl+Shift+;錄入特殊字符（對號、平方）;不連續(xù)區(qū)域的填充：Ctrl+Enter填充柄錄入（序列填充）插入批注實例01數(shù)據(jù)錄入第4頁/共42頁案例分析（1）在數(shù)字后面自動添加單位（如：元、千克等）將文本字符括在雙引號("")內或在單個字符前面添加一個反斜杠(\)（2）輸入身份證的前幾位"51102519"@（3）輸入條形碼前的某些重復數(shù)字“999999999”@（4）定義小數(shù)點對齊、分數(shù)對齊???.???#???/???（5）自定義各種特殊日期格式mmmmd,yyyy(dddd)December30,2008(Tuesday)yyyy“年”m“月”d“日”aaaa2008年12月30日星期四1.2、自動限定數(shù)據(jù)格式實例02自定義數(shù)字格式第5頁/共42頁1、EXCEL表格的排版技巧；2、EXCEL條件格式1.3、Excel2010條件格式設置；第6頁/共42頁1.3.1、單元格格式設置文字的對齊與旋轉合并單元格使用工具欄上的快捷按鈕格式—單元格—對齊—合并單元格自動換行格式—單元格—對齊—自動換行強制換行ALT+ENTER文字旋轉格式—單元格—對齊—文字方向設定邊框和底紋使用工具欄上的“邊框”“底紋”快捷按鈕格式—單元格—邊框—圖案實例03對齊技巧與邊框底紋第7頁/共42頁1.3.2、條件格式管理器設置什么是條件格式當單元格的值滿足預先設定的條件時，自動將單元格設置成預先設定的格式多條件格式的規(guī)則按從上到下的順序判斷條件并執(zhí)行最多指定三個條件根據(jù)公式自動設置格式在條件格式中使用公式和函數(shù)實現(xiàn)隔行變色等的特殊格式掌握ROW和MOD函數(shù)的用法==mod(row(),2)實例04條件格式第8頁/共42頁提高數(shù)據(jù)錄入準確性有效性檢查。什么是數(shù)據(jù)的有效性？數(shù)據(jù)有效性是一個包含幫助你在工作表中輸入資料提示信息的工具.它有如下功能：(1)給用戶提供一個選擇列表(2)限定輸入內容的類型或大小(3)自定義設置數(shù)據(jù)有效性的設置提供下拉式菜單的輸入設置輸入提示設置出錯警告自定義有效性條件1.4、數(shù)據(jù)的有效性實例05數(shù)據(jù)有效性第9頁/共42頁1.5、如何處理大型工作表在Excel多個工作表間快速切換技巧一：用快捷鍵快速導航工作表Ctrl+PageUp 移到上一個工作表Ctrl+PageDown移到下一個工作表技巧二：右鍵菜單準確定位工作表右擊導航狀態(tài)按鈕選擇所需工作表標簽名凍結工作表窗口大型工作表數(shù)據(jù)的分組、分級顯示命名單元格或單元格區(qū)域，以便能夠快速返回到這些工作表區(qū)域組合工作表，以便同時在多張工作表中輸入共用數(shù)據(jù)實例06凍結窗格第10頁/共42頁并排比較數(shù)據(jù)兩個工作簿將在同一個窗口中打開。當一個工作簿滾動時，Excel將使第二個工作簿自動滾動相應數(shù)量的單元格同時比較兩個工作簿可能很困難實例07-1并排比較數(shù)據(jù)1實例07-2并排比較數(shù)據(jù)2第11頁/共42頁1.6、數(shù)據(jù)保護功能保護工作表、工作簿（1）為工作簿加密碼

（2）為工作表加密碼

（3）設置不同區(qū)域不同密碼

（4）設置只有部分單元格可編輯第12頁/共42頁二、公式與函數(shù)的應用及案例公式的基本用法2.1函數(shù)分類與常用函數(shù)應用舉例2.2第13頁/共42頁雙擊填充柄復制公式區(qū)別公式中各種單元格引用方式絕對引用、相對引用、混合引用、三維引用跨工作表或工作簿的數(shù)據(jù)計算不同工作表間數(shù)據(jù)的計算、不同工作簿間數(shù)據(jù)的計算自動求和與自動計算功能的使用2.1公式的基本用法實例08使用相對和絕對引用計算產(chǎn)品銷售額與利潤實例9-1跨工作表數(shù)據(jù)計算實例9-2跨工作簿數(shù)據(jù)計算第14頁/共42頁函數(shù)：是預定義的公式，通過使用一些稱為參數(shù)的特定值來安特定的順序和結構執(zhí)行計算函數(shù)的使用方式=函數(shù)名稱（參數(shù)1，參數(shù)2，參數(shù)3……)函數(shù)的嵌套特性一個函數(shù)可以引用其他函數(shù)的結果作為參數(shù)Excel最多支持七層嵌套函數(shù)邏輯文本類函數(shù)應用日期時間函數(shù)應用查詢引用類函數(shù)應用統(tǒng)計計數(shù)類函數(shù)應用財務函數(shù)應用2.2函數(shù)應用與常用函數(shù)應用舉例實例10函數(shù)綜合實例第15頁/共42頁IFAND\ORLENMID\LEFT\RIGHT2.2.1邏輯文本類函數(shù)第16頁/共42頁判斷一個條件是否滿足，如果滿足返回一個值，如果不滿足則返回另一個值IF函數(shù)語法：

IF(logical_test,value_if_true,value_if_false)條件為真時條件為假時第17頁/共42頁AND\OR函數(shù)AND函數(shù)的作用：是對多個邏輯值進行交集計算，返回邏輯值

語法：AND(logical1,logical2…)OR函數(shù)的作用：是對多個邏輯值進行并集計算，返回邏輯值

語法：OR(logical1,logical2,…)第18頁/共42頁MIDB函數(shù)作用自文字指定位置開始向右提取指定長度的字符串，操作對象均為文本，MID以字符為單位，MIDB以字節(jié)為單位截取

語法：MID(text,start_num,num_bytes)LEFT返回文本字符串中的第一個或前幾個字符，RIGHT返回文本字符串中的最后一個或后幾年字符

語法：LEFT(text,num_chars)

語法：RIGHT(text,num_chars)MID(MIDB)

\

LEFT

\

RIGHT函數(shù)第19頁/共42頁DATE：返回代表特定日期的序列號語法：DATE(year,month,day)NOW：返回計算機內部時鐘的當前日期和時間語法：NOW()TODAY：返回當前日期的序列編號語法：TODAY()注意：序列號是MicrosoftExcel日期和時間計算使用的日期-時間代碼。MicrosoftExcel可將日期存儲為可用于計算的序列號。默認情況下，1900年1月1日的序列號是1，而2008年1月1日的序列號是39448，這是因為它距1900年1月1日有39448天。2.2.2日期與時間函數(shù)：第20頁/共42頁LOOKUPHLOOKUPVLOOKUP2.2.3查詢和引用類函數(shù)第21頁/共42頁LOOKUP使用LOOKUP函數(shù)是在單行區(qū)域或單列區(qū)域中查找數(shù)值，然后返回第二個單行區(qū)域或單列區(qū)域中相同位置的數(shù)值

LOOKUP(lookup_value,lookup_vector,result_vector)

說明：lookup_vector為第一個包含一行或一列的區(qū)域；result_vector為第二個包含一行或一列的區(qū)域，其大小必須與參數(shù)lookup_vector相同第22頁/共42頁HLOOKUP實現(xiàn)水平查找使用HLOOKUP函數(shù)可以在表格的首行查找指定的數(shù)值,并由此返回指定行處與該數(shù)值同一列的數(shù)值，當比較值位于數(shù)據(jù)表首行時，可使用此函數(shù)語法：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)第23頁/共42頁VLOOKUP_實現(xiàn)豎直查找在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行中指定列處的數(shù)值。當比較值位于數(shù)據(jù)表首列時,可以使用此函數(shù)語法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)第24頁/共42頁COUNTCOUNTIFSUMIFINTMOD2.2.4統(tǒng)計計數(shù)類函數(shù)第25頁/共42頁COUNT

COUNT函數(shù)的作用：返回包含數(shù)字以及包含參數(shù)列表中的數(shù)字的單元格的個數(shù)。利用函數(shù)COUNT可以計算單元格區(qū)域或數(shù)字數(shù)組中數(shù)字字段的輸入項個數(shù)。語法：COUNT(value1,value2,...)參數(shù)：Value1,value2,...為包含或引用各種類型數(shù)據(jù)的參數(shù)（1到30個），但只有數(shù)字類型的數(shù)據(jù)才被計算。說明函數(shù)COUNT在計數(shù)時，將把數(shù)字、日期、或以文本代表的數(shù)字計算在內；但是錯誤值或其他無法轉換成數(shù)字的文字將被忽略。如果參數(shù)是一個數(shù)組或引用，那么只統(tǒng)計數(shù)組或引用中的數(shù)字；數(shù)組或引用中的空白單元格、邏輯值、文字或錯誤值都將被忽略。第26頁/共42頁COUNTIF函數(shù)可累計滿足某一條件的對象在單元格區(qū)域中出現(xiàn)的次數(shù)，該條件可以是數(shù)值、文本或表達式，在人事、工資和成績統(tǒng)計中有廣泛用途。

COUNTIF函數(shù)語法：

COUNTIF（range,criteria)第27頁/共42頁SUMIF函數(shù)可對滿足某一條件的單元格區(qū)域求和，該條件可以是數(shù)值、文本或表達式。SUMIF函數(shù)語法：

SUMIF（range,criteria,sum_range)第28頁/共42頁INT

INT函數(shù)的作用：將數(shù)字向下舍入到最接近的整數(shù)。語法：INT(number)參數(shù)：number是需要進行向下舍入取整的實數(shù)。MOD

返回兩數(shù)相除的余數(shù)。結果的正負號與除數(shù)相同。語法MOD(number,divisor)；Number

為被除數(shù)；Divisor

為除數(shù)。注解如果divisor為零，函數(shù)MOD返回錯誤值#DIV/0!。函數(shù)MOD可以借用函數(shù)INT來表示:MOD(n,d)=n-d*INT(n/d)第29頁/共42頁PMT（）貸款函數(shù)；基于固定利率及等額分期付款方式，返回貸款的每期付款額。語法PMT(rate,nper,pv,fv,type)Rate

貸款利率。Nper

該項貸款的付款總數(shù)。Pv

現(xiàn)值，或一系列未來付款的當前值的累積和，也稱為本金。Fv

為未來值，或在最后一次付款后希望得到的現(xiàn)金余額，如果省略fv，則假設其值為零，也就是一筆貸款的未來值為零。Type

數(shù)字0或1，用以指定各期的付款時間是在期初還是期末。注解

PMT返回的支付款項包括本金和利息，但不包括稅款、保留支付或某些與貸款有關的費用。應確認所指定的rate和nper單位的一致性。例如，同樣是四年期年利率為12%的貸款，如果按月支付，rate應為12%/12，nper應為4*12；如果按年支付，rate應為12%，nper為4。2.2.6財務函數(shù)及案例應用第30頁/共42頁三、圖表及打印輸出及其它設置創(chuàng)建Excel圖表3.1編輯Excel圖表3.2打印輸出及其它設置3.3第31頁/共42頁柱形圖：用來顯示一段時間內數(shù)據(jù)的變化或者描述各個項目之間數(shù)據(jù)比較圖表線性圖：主要用于以等時間間隔顯示數(shù)據(jù)的變化趨勢，強調的是時間性和變動率，而不是變動量餅形圖：顯示數(shù)據(jù)系列中的項目和該項目數(shù)值總和的比例關系。它只能顯示一個系列的數(shù)據(jù)比例關系圖表在數(shù)據(jù)分析中的作用第32頁/共42頁確定分析目標明確表格數(shù)據(jù)的取值范圍圖表向導使用一鍵成圖創(chuàng)建圖表3.1創(chuàng)建分析圖表實例11圖表類型匯總第33頁/共42頁常規(guī)編輯技巧改變圖表大小移動圖表位置圖表內其他對象編輯圖表的特殊編輯向圖表添加數(shù)據(jù)刪除圖表內數(shù)據(jù)3.2圖表的編輯、美化技巧文字數(shù)據(jù)修飾線和陰影修飾背景修飾設置圖表的序列格式綜合案例分析第34頁/共42頁頁面設置工作表的頁眉、頁腳分頁符和打印選項工作表參數(shù)設置設置打印區(qū)域自動打印統(tǒng)一表頭打印指定頁面打印連續(xù)頁碼一次打印多個工作表使某個工作表不能被打印3.3打印輸出及其它設置第35頁