Excel高效商務(wù)運(yùn)用技巧

Excel高效商務(wù)運(yùn)用技巧

JimmyHou課程大綱ClicktoaddTitle1基礎(chǔ)知識(shí)1ClicktoaddTitle2公式及函數(shù)2ClicktoaddTitle2數(shù)據(jù)分析3EXCEL用戶水平的5個(gè)層次專(zhuān)家高級(jí)用戶中級(jí)用戶初級(jí)用戶新手Excel技能+專(zhuān)業(yè)知識(shí)+行業(yè)經(jīng)驗(yàn)數(shù)組+VBA數(shù)據(jù)透視表+常用公式基本掌握常用功能剛開(kāi)始接觸2010基本界面基礎(chǔ)知識(shí)工作窗口的視圖控制知識(shí)點(diǎn)練習(xí)要求工作簿的多窗口顯示增加一個(gè)窗口，對(duì)兩個(gè)窗口進(jìn)行平鋪、水平、垂直、層疊排放并排比較比較兩張不同的工作表，并使他們同步滾動(dòng)拆分窗口固定顯示表頭標(biāo)題行或標(biāo)題列凍結(jié)窗口固定顯示前兩列自定義功能區(qū)在功能區(qū)中添加照相機(jī)按鈕基礎(chǔ)知識(shí)單元格和區(qū)域知識(shí)點(diǎn)練習(xí)要求多表區(qū)域的選擇在sheet1/sheet2/sheet3的A1:D1分別輸入姓名、性別、年齡、籍貫，并將文字標(biāo)為藍(lán)色，背景選擇淡黃色。特殊區(qū)域的選擇找出sheet4中的空值，并將空值隱藏。將非空的數(shù)據(jù)復(fù)制到sheet5中，并使之連續(xù)。選擇任意區(qū)域，為它命名?；A(chǔ)知識(shí)輸入和編輯數(shù)據(jù)知識(shí)點(diǎn)練習(xí)要求強(qiáng)行換行（Alt+Enter）將B10單元格強(qiáng)行換行自動(dòng)填充以步長(zhǎng)為2，自動(dòng)填充編號(hào)設(shè)置自定義序列將表中儲(chǔ)蓄所添加到自定義

序列中去，便于日后工作選擇性粘貼對(duì)行列進(jìn)行轉(zhuǎn)置基礎(chǔ)知識(shí)數(shù)據(jù)處理與保護(hù)知識(shí)點(diǎn)練習(xí)要求查找與替換將D列的藍(lán)色字體替換為黑色查找功能CTRL+F保護(hù)單元格對(duì)SHEET數(shù)據(jù)進(jìn)行保護(hù)設(shè)文件設(shè)置打開(kāi)密碼基礎(chǔ)知識(shí)數(shù)據(jù)有效性知識(shí)點(diǎn)練習(xí)要求限制輸入內(nèi)容日期限制為2010年的某一天，時(shí)間點(diǎn)限制為8：00-17：00，客戶數(shù)量限制為大于0的整數(shù)制作下拉菜單網(wǎng)點(diǎn)處制作下拉菜單基礎(chǔ)知識(shí)條件格式知識(shí)點(diǎn)練習(xí)要求數(shù)據(jù)條在總分處設(shè)置數(shù)據(jù)條色階排名處按照百分比設(shè)置漸變色階圖標(biāo)對(duì)負(fù)債、資產(chǎn)、表外設(shè)置紅黃綠燈圖標(biāo)。對(duì)名次變化設(shè)置上下箭頭圖標(biāo)。使用公式及函數(shù)函數(shù)基礎(chǔ)使用公式及函數(shù)認(rèn)識(shí)公式公式中的運(yùn)算符辨別&:/,的區(qū)別使用單元格名稱(chēng)（對(duì)I33進(jìn)行定義）單元格引用相對(duì)引用、絕對(duì)引用公式中的運(yùn)算符:(冒號(hào))區(qū)域運(yùn)算符，對(duì)兩個(gè)引用之間，包括兩個(gè)引用在內(nèi)的所有單元格進(jìn)行引用B5:B15，(逗號(hào))聯(lián)合操作符將多個(gè)引用合并為一個(gè)引用SUM(B5:B15,D5:D15)&

將兩個(gè)文本值連接起來(lái)產(chǎn)生一個(gè)連續(xù)的文本值公式

引用概念①相對(duì)引用公式中的相對(duì)單元格引用（例如A1）是基于包含公式和單元格引用的單元格的相對(duì)位置。如果公式所在單元格的位置改變，引用也隨之改變。如果多行或多列地復(fù)制公式，引用會(huì)自動(dòng)調(diào)整。默認(rèn)情況下，新公式使用相對(duì)引用。例如，如果將單元格B2中的相對(duì)引用復(fù)制到單元格B3，將自動(dòng)從=A1調(diào)整到=A2。②絕對(duì)引用單元格中的絕對(duì)單元格引用（例如＄A＄1）總是在指定位置引用單元格。如果公式所在單元格的位置改變，絕對(duì)引用保持不變。如果多行或多列地復(fù)制公式，絕對(duì)引用將不作調(diào)整。默認(rèn)情況下，新公式使用相對(duì)引用，需要將它們轉(zhuǎn)換為絕對(duì)引用。例如，如果將單元格B2中的絕對(duì)引用復(fù)制到單元格B3，則在兩個(gè)單元格中一樣，都是＄A＄1。③混合引用混合引用具有絕對(duì)列和相對(duì)行，或是絕對(duì)行和相對(duì)列。絕對(duì)引用列采用＄A1、＄B1等形式。絕對(duì)引用行采用A＄1、B＄1等形式。如果公式所在單元格的位置改變，則相對(duì)引用改變，而絕對(duì)引用不變。如果多行或多列地復(fù)制公式，相對(duì)引用自動(dòng)調(diào)整，而絕對(duì)引用不作調(diào)整。例如，如果將一個(gè)混合引用從A2復(fù)制到B3，它將從=A＄1調(diào)整到=B＄1。相對(duì)、絕對(duì)引用實(shí)例如C1單元格有公式=A1+$B$1

然后你把光標(biāo)指向C1單元格的右下角，當(dāng)光標(biāo)變成一個(gè)黑實(shí)小十字時(shí)，按左鍵向下拖動(dòng)幾行，如拖到C10，放開(kāi)。

從C2開(kāi)始公式已經(jīng)變化了變成了這樣：

=A2+$B$1

=A3+$B$1

=A4+$B$1

=A5+$B$1

=A6+$B$1

=A7+$B$1

=A8+$B$1

…………

看明白了嗎？不變的是絕對(duì)引用，變化的是相對(duì)引用。相對(duì)、絕對(duì)引用實(shí)例使用管公式宣及函鐘數(shù)函數(shù)分類(lèi)數(shù)學(xué)與三角加減乘除、乘方、四舍五入等統(tǒng)計(jì)方差、標(biāo)準(zhǔn)差、平均數(shù)、中值、眾數(shù)等。日期與時(shí)間年月日的顯示、日期序列值的相互轉(zhuǎn)化、當(dāng)前日期時(shí)間邏輯條件判斷查找與引用從表格和數(shù)組中提取指定行或列中的數(shù)值文本大小寫(xiě)、全角半角轉(zhuǎn)換、指定位置提取某些字符等財(cái)務(wù)計(jì)算貸款支付額、存款到期額、利率、余額遞減折舊費(fèi)信息檢測(cè)單元格內(nèi)包含的數(shù)據(jù)類(lèi)型、求錯(cuò)誤值種類(lèi)的類(lèi)型數(shù)據(jù)庫(kù)從函數(shù)清單或數(shù)據(jù)庫(kù)中提取符合給定條件數(shù)據(jù)的函數(shù)工程工程系函數(shù)、貝塞爾函數(shù)、復(fù)數(shù)計(jì)算等。使用趙公式腐及函攔數(shù)搜索稻函數(shù)使用祥公式賺及函民數(shù)公式旗審核使用論公式搏及函煙數(shù)文本函數(shù)LEFT從文本符字符串的第一個(gè)字符開(kāi)始返回指定個(gè)數(shù)的字符。MID從文本符字符串制定的起始位置開(kāi)始返回指定個(gè)數(shù)的字符。RIGHT從文本符字符串的最后一個(gè)字符開(kāi)始返回指定個(gè)數(shù)的字符。RMB四舍五入數(shù)值并轉(zhuǎn)換為帶有￥符號(hào)的文本DOLLAR四舍五入數(shù)值并轉(zhuǎn)換為帶有$符號(hào)的文本NUMBERSTRING將數(shù)值替換為大寫(xiě)漢字VALUE將文本轉(zhuǎn)換為數(shù)值REPT按照指定的次數(shù)重復(fù)顯示文本使用父公式鄉(xiāng)豐及函嶄數(shù)AB1數(shù)據(jù)

2SalePrice

3瑞典

公式說(shuō)明（結(jié)果）=LEFT(A2,4)第一個(gè)字符串中的前四個(gè)字符(Sale)LEFT(text,num_chars)根據(jù)所指定的字符數(shù)，LEFT返回文本字符串中第一個(gè)字符或前幾個(gè)字符。Text

是包含要提取的字符的文本字符串。Num_chars

指定要由LEFT提取的字符的數(shù)量。使用扇公式寧及函沈數(shù)AB1數(shù)據(jù)

2FluidFlow

公式說(shuō)明（結(jié)果）=MID(A2,1,5)上面字符串中的5個(gè)字符，從第一個(gè)字符開(kāi)始(Fluid)=MID(A2,7,20)上面字符串中的20個(gè)字符，從第七個(gè)字符開(kāi)始(Flow)MID(text,start_num,num_chars)MID返回文本字符串中從指定位置開(kāi)始的特定數(shù)目的字符，該數(shù)目由用戶指定。Text

是包含要提取字符的文本字符串。Start_num

是文本中要提取的第一個(gè)字符的位置。文本中第一個(gè)字符的start_num為1，以此類(lèi)推。Num_chars

指定希望MID從文本中返回字符的個(gè)數(shù)。使用盯公式敢及函堡數(shù)AB1數(shù)據(jù)

21234.567

公式說(shuō)明（結(jié)果）=DOLLAR(A2,2)或RMB(A2,2)以貨幣格式及小數(shù)點(diǎn)右邊2位數(shù)字的形式顯示第一個(gè)數(shù)（￥1,234.57）=DOLLAR(A2,-2)或RMB(A2,-2)以貨幣格式及小數(shù)點(diǎn)左邊2位數(shù)字的形式顯示第一個(gè)數(shù)(￥1,200)DOLLAR或RMB(number,decimals)依照貨幣格式將小數(shù)四舍五入到指定的位數(shù)并轉(zhuǎn)換成文本。Number

為數(shù)字、包含數(shù)字的單元格引用，或是計(jì)算結(jié)果為數(shù)字的公式。Decimals

為十進(jìn)制數(shù)的小數(shù)位數(shù)。如果decimals為負(fù)數(shù)，則number在小數(shù)點(diǎn)左側(cè)進(jìn)行舍入。如果省略decimals，則假設(shè)其值為2。使用葵公式宜及函體數(shù)AB1公式說(shuō)明（結(jié)果）2=REPT("*-",3)字符串顯示3次(*-*-*-)3=REPT("-",10)短劃線顯示10次(----------)REPT(text,number_times)按照給定的次數(shù)重復(fù)顯示文本?？梢酝ㄟ^(guò)函數(shù)REPT來(lái)不斷地重復(fù)顯示某一文本字符串，對(duì)單元格進(jìn)行填充。Text

需要重復(fù)顯示的文本。Number_times

是指定文本重復(fù)次數(shù)的正數(shù)。使用理公式幻玉及函有數(shù)時(shí)間與日期函數(shù)WEEKDAY返回某日期為星期幾（星期幾）WEEKNUM返回日期序列號(hào)代表一年中的第幾周DATE以年月日表示日期的序列號(hào)WORKDAY求指定工作日后的日期序列號(hào)（完成日期）NETWORKDAYS計(jì)算起始日和結(jié)束日期的天數(shù)（除去節(jié)假日）DATEDIF用指定單位計(jì)算兩個(gè)日期之間的天數(shù)使用遼公式爆及函百數(shù)AB1日期說(shuō)明22008/10/1起始日期3151完成所需天數(shù)42008/11/26假日52008/12/4假日62009/1/21假日公式說(shuō)明（結(jié)果）=WORKDAY(A2,A3,A4:A6)從起始日期開(kāi)始151個(gè)工作日的日期，除去假日(2009-5-5)WORKDAY(start_date,days,holidays）返回某日期（起始日期）之前或之后相隔指定工作日的某一日期的日期值。工作日不包括周末和專(zhuān)門(mén)指定的假日。Start_date

為一個(gè)代表開(kāi)始日期的日期。Days

為Start_date不含周末及節(jié)假日的天數(shù)。Holidays

需要從工作日歷中排除的一個(gè)或多個(gè)日期。使用哈公式垂及函身數(shù)DATEDIF(start_date,days,unit)UNIT“Y”整年“M”整月“D”整日“YM”不足一年的月“YD”不足一年的日“MD”不足一月的日使用貨公式榨及函未數(shù)概念使用弓函數(shù)WE欺EK淺NU湯M可以繡返回清一個(gè)隔數(shù)字炭，該械數(shù)字盲代表瓜該日垂期在獸一年狂中的嬌第幾常周。2語(yǔ)法WE巷EK示NU膚M（se為ri晨al給_n切um，re比tu墊rn吹_t筍yp組e）3參數(shù)參數(shù)Se豪ri解a_掛nu迎m代表蠻要確銅定它夏位于艷一年析中的受幾周孝的特婚定日樂(lè)期。泰參數(shù)Re掏tu扮rn孟_t眠yp竊e為一詢(xún)數(shù)字脂，它憤確定苦星期彩計(jì)算砍從哪羞一天喬開(kāi)始家，其醫(yī)默認(rèn)屑值為1，參毯數(shù)具所體含茄義如尼表所界示。Re危tu防rn悶_t好yp待e星期脾開(kāi)始面于1星期牙從星晶期日描開(kāi)始哪。星遼期內(nèi)俱的天現(xiàn)數(shù)從1到7記數(shù)較。2星期般從星銹期一脅開(kāi)始謠。星郵期內(nèi)興的天蓮數(shù)從1到7記數(shù)少。數(shù)據(jù)伴分析數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視表改變數(shù)據(jù)透視表的布局在數(shù)據(jù)透視表中排序在數(shù)據(jù)透視表中執(zhí)行計(jì)算：字段計(jì)算、項(xiàng)計(jì)算、自定義計(jì)算更新數(shù)據(jù)數(shù)據(jù)透視