EXCEL函數(shù)學(xué)習(xí)

1、（一）函數(shù)學(xué)習(xí)：1 去除空格TRIM(text)text表示要被操作的對(duì)象。SUBSTITUTE(text,old_text,new_text,instance_num)text表示要被操作的對(duì)象。old_text整個(gè)文本里需要替換的部分。new_text被替換進(jìn)去的部分。 instance_num15266371582152*371582instance_num不填的情況,兩個(gè)6均被替換。15266371582152*6371582instance_num=1,第一個(gè)6被替換。152663715821526*371582instance_num=2,第一個(gè)6被替換。REPLACE(old_t

2、ext,start_num,num_chars,new_text)old_text表示要被操作的對(duì)象。start_num從第幾個(gè)字符開始數(shù)起num_chars往后數(shù)幾個(gè)new_text替換成的內(nèi)容序號(hào)品番納期指示數(shù)量月份1 7075890E61002017/6/51262 7075865A31002017/6/214657075865A2000 BB 2017/4/2224467075870A90002017/5/313652 取字符=LEFT/RIGHT(text,num_chars)text表示要被操作的對(duì)象。num_chars要截取的字符的個(gè)數(shù)。品番left()right()結(jié)合使用88

3、*16228888883 邏輯=IF(logical_test,value_if_true,value_if_false)logical_test條件判斷value_if_true條件成立對(duì)應(yīng)值value_if_false 條件不成立對(duì)應(yīng)值如果張三出生在9月之前，就可以獲得某某獎(jiǎng)勵(lì)，否則就不可以。張三生日1990/12/1212 不可以不可以4 年月日函數(shù)=MONTH(serial_number)=YEAR(serial_number)=DAY(serial_number)2017/5/1420175145 鏈接字符串=CONCATENATE(text1,text2,text3,)納期月份日顯

4、示納期在上旬還是下旬2017/5/14514 5月上6 計(jì)算符合多條件的數(shù)據(jù)和=SUMPRODUCT(array1,array2,array3, )品目納期數(shù)量月份7075885430002017-06-052467075885430002017-04-064847075885430002017-05-154857075885430002017-07-141277075885430002017-04-104847075885430002017-04-134847075885430002017-05-064857075885430002017-05-094857075810720002017-0

5、6-052467075810720002017-04-064847075810720002017-05-154857075810720002017-07-141277075810720002017-04-104847075810720002017-04-134847075810720002017-05-064857075810720002017-05-094857 豎直查找=vlookup(lookup_value,table_array,col_index_num,range_lookup)lookup_value查找值table_array查找范圍col_index_num查找列數(shù)rang

6、e_lookup精確匹配或者近似匹配應(yīng)用：比如想比較南通桿頭各個(gè)品目的漲價(jià)后的百分比。而現(xiàn)行價(jià)格表中的品目順序和報(bào)價(jià)單中的品目順序不同。漲價(jià)后vlookup()KOWA系統(tǒng)品目南通報(bào)價(jià)品目稅含現(xiàn)行價(jià)格NR7-63-90590NR76390590306.4281.929%721-63-55410721-63-5541072.2367.427%707-63-10711NC 707-63-10711-NC221.13201.4710%707-63-85890NC 707-63-85890-NC135.62124.319%721-63-55391721-63-55391-NO74.3869.577%7

7、21-63-55360721-63-55360-NO65.5617%707-63-50631707-63-50631-NO48.2945.56%NR1-63-90110NR1-63-90110-CH153.01139.999%721-63-65020721-63-65020-CH97.8289.499%707-63-50371707-63-50371-NO69.564.628%707-63-10970-CH707-63-10970-NB196.02179.879%707-63-10920-CH707-63-10920-CH211.12194.069%707-63-11810-CH707-63-

8、11810-CH256.91235.239%707-63-10930-CH707-63-10930-CH203.18188.138%NC7-63-75870NC7-63-75870-0096.5888.0710%NC7-63-60630NC7-63-60630-0081.5575.528%NC7-63-70721NC7-63-70721-0092.0384.968%NC7-63-65820NC7-63-65820-0081.1775.158%NC7-63-95671NC7-63-95671-00161.67147.89%707-63-80780-CH707-63-80780-CH154.831

9、44.417%707-63-10731707-63-10731244.74224.39%707-63-70590707-63-7059095.9388.98%707-63-75A20707-63-75A20112.68104.228%707-63-75741707-63-75741107.1998.219%NR7-63-65721NR7-63-65721105.5697.368%NC7-63-11790NC7-63-11790307.63280.9410%721-63-65600721-63-6560089.54#N/A#N/A707-63-11870-NC707-63-11870-NC330

10、.23323.932%707-63-11221-NC707-63-11221-NC344.43337.122%707-63-85950707-63-85950157.88154.812%NC7-63-70630NC7-63-70630106.299.177%721-63-10111-CH721-63-10111253.03248.582%707-63-90900-NO707-63-90900-NO182.01167.958%8 四舍五入=ROUND(number,num_digits)number表示要被操作的對(duì)象。num_digits保留位數(shù)round()13 0.3333333330.33

11、67 0.8571428570.86num_digits=負(fù)數(shù)時(shí)，可以對(duì)其他位上進(jìn)行取舍。36834368009 特定位四舍五入=fixed(number,decimals,no_commas)number表示要被操作的對(duì)象。decimals表示對(duì)那個(gè)數(shù)位進(jìn)行取舍。設(shè)定成負(fù)值可以對(duì)小數(shù)點(diǎn)前面的數(shù)位進(jìn)行取舍。-1表示各位no_commas是否阻止千分符fasle 不阻止；true 阻止。7884 7,90010 文本轉(zhuǎn)換=text(value,format_text)format_text格式要求#0.00,$0.00#0.00$0.001234.567 1234.57$1234.57一套格式轉(zhuǎn)

12、換=now()2017/4/25 2017/04/252017/05/E2017/3/182017/3/12 2017/03/122017/04/E2017/3/1811 計(jì)數(shù)=countif(range,criteria)1false12 match該函數(shù)一般不單獨(dú)使用，經(jīng)常和index（）一起使用。=match(lookup_value,lookup_array,match_type)lookup_value查找值lookup_array查找范圍match_type匹配類型 0為精確匹配KOWA系統(tǒng)品目NR7-63-905904721-63-554104707-63-10711NC4707

13、-63-85890NC4721-63-553914721-63-55360413 index=index(array,row_num,column_num)array最終得到的值（該值一定在這個(gè)數(shù)組中）row_num最終得到的值在第一個(gè)參數(shù)數(shù)組中的位置，比如第幾行或第幾列。這時(shí)就要用到Match函數(shù)，因?yàn)镸atch函數(shù)返回的就是行或者列的位置發(fā)發(fā)貨貨平平臺(tái)臺(tái)運(yùn)運(yùn)輸輸方方式式城城市市費(fèi)費(fèi)用用北京公路專線深圳55上海公路專線廣州32深圳鐵路運(yùn)輸哈爾濱20北京航空運(yùn)輸廣東25深圳公路運(yùn)輸昆明66深圳航空運(yùn)輸哈爾濱90發(fā)發(fā)貨貨平平臺(tái)臺(tái)運(yùn)運(yùn)輸輸方方式式城城市市費(fèi)費(fèi)用用北京航空運(yùn)輸深圳#N/A上海公路專線

14、廣州32深圳鐵路運(yùn)輸哈爾濱20北京公路專線深圳55深圳航空運(yùn)輸昆明#N/A深圳航空運(yùn)輸哈爾濱9014單條件求和=SUMIF(range,criteria,sum_range)range條件區(qū)域criteria求和條件sum_range實(shí)際求和區(qū)域品目納期數(shù)量7075885430002017-06-05247075885430002017-04-06487075885430002017-05-15487075885430002017-07-14127075885430002017-04-10487075885430002017-04-13487075885430002017-05-0648707

15、5885430002017-05-09487075810720002017-06-05247075810720002017-04-06487075810720002017-05-15127075810720002017-07-14127075810720002017-04-10487075810720002017-04-13487075810720002017-05-06127075810720002017-05-094815 多條件求和=sumifs(sum_range,criteria_range1,criteria1,riteria_range2,criteria2.)sum_range

16、是要求和的范圍criteria_range1是條件的范圍criteria1是條件品目納期數(shù)量月份7075885430002017-06-052467075885430002017-04-064847075885430002017-05-154857075885430002017-07-141277075885430002017-04-104847075885430002017-04-134847075885430002017-05-064857075885430002017-05-094857075810720002017-06-052467075810720002017-04-0648470

17、75810720002017-05-151257075810720002017-07-141277075810720002017-04-104847075810720002017-04-134847075810720002017-05-061257075810720002017-05-0948516 其他簡單常用函數(shù)=max()=min()=AVERAGE()=sum()=and(mod(row(),2)=0)利用這個(gè)函數(shù)可以設(shè)置單元格格式。（二）圖表制作A帕累托圖制作Pareto理論依據(jù)： 80%的問題是由20%的原因引起； 80%的索賠發(fā)生在20%的生產(chǎn)線上； 80%的銷售額由20%的產(chǎn)品

18、帶來； 80%的品質(zhì)成本由20%的品質(zhì)問題造成； 20%的人員掌握著社會(huì)80%的財(cái)富；帕累托圖在精益六西格瑪、質(zhì)量管理、工業(yè)工程、流程改善過程中應(yīng)用非常普遍。有兩個(gè)縱坐標(biāo)，一個(gè)橫坐標(biāo)，幾個(gè)長方形，一條曲線，左邊縱坐標(biāo)表示頻數(shù)，右邊縱坐標(biāo)表示頻率，以百分?jǐn)?shù)表示。橫坐標(biāo)表示影響質(zhì)量的各項(xiàng)因素，按影響大小從左向右排列，曲線表示各種影響因素大小的累計(jì)百分?jǐn)?shù)。故障名稱次數(shù)累計(jì)比率顯示屏無顯示217527.81%顯卡插槽松動(dòng)142546.02%電源線接觸不良123661.83%按鈕損壞96874.20%風(fēng)扇異響87685.40%系統(tǒng)藍(lán)屏67894.07%其他故障464100.00%通過設(shè)置網(wǎng)格線格式，可以

19、去除網(wǎng)格線。右擊坐標(biāo)軸，通過添加主要網(wǎng)格線可以進(jìn)行添加。B圖表下方顯示數(shù)據(jù)通過設(shè)置模擬運(yùn)算表讓表格下方顯示圖表的數(shù)據(jù)。月份檢查數(shù)不良數(shù) 1月2,214-2月2,34113月3,060-4月2,939-5月2,315121751425123696887667846427.81%46.02%61.83%74.20%85.40%94.07%100.00%顯示屏無顯示顯卡插槽松動(dòng)電源線接觸不良按鈕損壞風(fēng)扇異響系統(tǒng)藍(lán)屏其他故障050010001500200025006月1,692-7月1,70718月2,31929月2,905310月4,193211月4,118-12月4,8854C坐標(biāo)軸位置不在邊緣日

20、期氣溫3月1日-73月2日-83月3日-13月4日-33月5日-103月6日-23月7日53月8日83月9日-13月10日-63月11日-93月12日-13 - 2 4 6 8 10 0 1,000 2,000 3,000 4,000 5,000 6,000 1月2月3月4月5月6月7月8月9月10月11月12月不良數(shù) - 1 - - 1 - 1 2 3 2 - 4 檢查數(shù) 2,214 2,341 3,060 2,939 2,315 1,692 1,707 2,319 2,905 4,193 4,118 4,885 不良數(shù) 檢查數(shù)-7-8-1-3-10-258-1-6-9-13氣氣溫溫3月1日

21、3月2日3月3日3月4日3月5日3月6日3月7日3月8日3月9日3月10日3月11日3月12日氣溫右鍵“設(shè)置坐標(biāo)軸格式”，“坐標(biāo)軸交叉”進(jìn)行設(shè)置坐標(biāo)軸的位置。-7-8-1-3-10-258-1-6-9-13氣氣溫溫3月1日3月2日3月3日3月4日3月5日3月6日3月7日3月8日3月9日3月10日3月11日3月12日氣溫trim()substitute()replace()7075890E61007075890E6100A 7075890E61007075865A31007075865A31007075865A2000 BB 7075865A2000BB7075870A90007075870A9

22、000instance_num不填的情況,兩個(gè)6均被替換。如果張三出生在9月之前，就可以獲得某某獎(jiǎng)勵(lì)，否則就不可以。對(duì)85430 交貨期為4月份的數(shù)量求和。sumproduct()707588543000144現(xiàn)行價(jià)格稅含707-63-10711NC201.47707-63-10920-CH194.06707-63-10930-CH188.13707-63-10970-CH179.87707-63-11810-CH235.23707-63-5037164.62707-63-5063145.5707-63-85890NC124.31721-63-5536061721-63-5539169.577

23、21-63-5541067.42721-63-6502089.49NC7-63-6063075.52NC7-63-6582075.15NC7-63-7072184.96NC7-63-7587088.07NC7-63-95671147.8NR1-63-90110139.99NR7-63-6572197.36NR7-63-90590281.92707-63-80780-CH144.41應(yīng)用：比如想比較南通桿頭各個(gè)品目的漲價(jià)后的百分比。而現(xiàn)行價(jià)格表中的品目順序和報(bào)價(jià)單中的品目順序不同。707-63-10731224.3707-63-7059088.9707-63-75A20104.22707-63-

24、7574198.21NC7-63-11790280.94707-63-6582071.02707-63-11870-NC323.93707-63-11221-NC337.12707-63-85950154.81NC7-63-7063099.17721-63-10111-CH248.58707-63-90900-NO167.95roundup()rounddown()0.340.330.860.85=text()=IF(TEXT(NOW(),YYYY/MM/DD)C146,uncomplete,complete)2017/03/18uncomplete2017/03/18complete表示對(duì)那

25、個(gè)數(shù)位進(jìn)行取舍。設(shè)定成負(fù)值可以對(duì)小數(shù)點(diǎn)前面的數(shù)位進(jìn)行取舍。-1表示各位ctrl+shift+entectrl+shift+enter rsumif()707588543000324707581072000252最終得到的值在第一個(gè)參數(shù)數(shù)組中的位置，比如第幾行或第幾列。這時(shí)就要用到Match函數(shù)，因?yàn)镸atch函數(shù)返回的就是行或者列的位置4707588543000144 對(duì)品目為85430并且4月份的內(nèi)示求和。=sumifs(sum_range,criteria_range1,criteria1,riteria_range2,criteria2.)Pareto理論依據(jù)： 80%的問題是由20%的

26、原因引起； 80%的索賠發(fā)生在20%的生產(chǎn)線上；帕累托圖在精益六西格瑪、質(zhì)量管理、工業(yè)工程、流程改善過程中應(yīng)用非常普遍。 - 2 4 6 8 10 0 1,000 2,000 3,000 4,000 5,000 6,000 1月2月3月4月5月6月7月8月9月10月11月12月不良數(shù) - 1 - - 1 - 1 2 3 2 - 4 檢查數(shù) 2,214 2,341 3,060 2,939 2,315 1,692 1,707 2,319 2,905 4,193 4,118 4,885 不良數(shù) 檢查數(shù)地區(qū)營業(yè)額小計(jì)（萬元）所屬部門營業(yè)額（萬元）天津1499 河?xùn)|323山東715 河西555上海109

27、0 南開621江蘇886 濟(jì)南289青島426靜安512黃浦244徐匯211盧灣123南京295無錫277蘇州314步驟1 繪制內(nèi)層餅圖。選擇A2:B5,繪制普通餅圖2 增加外層餅圖系列。選擇圖表，單擊右鍵，執(zhí)行【選擇數(shù)據(jù)】命令，打開【選擇數(shù)據(jù)】對(duì)話框，在“系列”選項(xiàng)卡中添加“系列2”，其值為D2:D13。單擊【確定】按鈕，關(guān)閉【選擇數(shù)據(jù)】對(duì)話框。此時(shí)可見圖表似乎沒有任何變化。 3 選擇當(dāng)前圖表中可見的系列1，打開【設(shè)置數(shù)據(jù)系列格式】。在【設(shè)置數(shù)據(jù)系列格式】對(duì)話框中，選擇“次坐標(biāo)軸”單選按鈕。單擊【確定】按鈕，關(guān)閉【設(shè)置數(shù)據(jù)系列格式】對(duì)話框。 4 選擇圖表，單擊右鍵，執(zhí)行【選擇數(shù)據(jù)】命令，打開【選擇數(shù)據(jù)】對(duì)話框，選擇“系列2”將軸標(biāo)簽值設(shè)定為C2:C13。單擊【確定】按鈕，關(guān)閉【源數(shù)據(jù)】對(duì)話框。5 選擇系列1，即目前可見層，然后單擊其中某一塊餅，比如“天津”，按住左鍵不放，向外拖動(dòng)該餅，將整個(gè)系列一起往外拖，使整個(gè)系列的扇區(qū)形狀一起縮小到合適的