EXCEL及質量報表及品質七大工具的結合運用1

1、辦公軟件常用技巧第一節(jié)第一節(jié)一一第二節(jié)第二節(jié)二二1、數(shù)據(jù)透視表在報表中的運用2、常用函數(shù)1、EXCEL與品質7大工具的結合運用辦公軟件實用技巧講解1.1、數(shù)據(jù)透視表在報表中的運用（1 1）、）、“數(shù)據(jù)透視表數(shù)據(jù)透視表”的作用：的作用：您有超出您處理能力的數(shù)據(jù)嗎？您想一眼就看出趨勢和模式嗎您有超出您處理能力的數(shù)據(jù)嗎？您想一眼就看出趨勢和模式嗎？假設我們公司一年的質量報表全部采用Excel表，其中有幾百、幾千行數(shù)據(jù)，其中包含產(chǎn)品名稱、操作日期、批次、合格數(shù)、料廢數(shù)、工廢數(shù)及各種缺陷數(shù)等等龐大信息。每個人想從繁雜的質量報表中得到不同信息，該如何操作？每個人想從繁雜的質量報表中得到不同信息，該如何操作

2、？“數(shù)據(jù)透視表”適合您的需要，它通過不同視角顯示數(shù)據(jù)來進行比較、揭示和分析，從而將數(shù)據(jù)轉化成有意義的信息，揭示數(shù)據(jù)的內(nèi)涵。“數(shù)據(jù)透視表”功能能夠將篩選、排序和分類匯總等操作依次完成，并生成匯總表格，是Excel強大數(shù)據(jù)處理能力的具體體現(xiàn)。1.1、數(shù)據(jù)透視表在報表中的運用（2 2）、數(shù)據(jù)清單的建立（基礎表）：）、數(shù)據(jù)清單的建立（基礎表）：在運用數(shù)據(jù)透視表之前，首先要建立在運用數(shù)據(jù)透視表之前，首先要建立可用的數(shù)據(jù)清單可用的數(shù)據(jù)清單。清單要求如下：清單要求如下：A、數(shù)據(jù)由若干列組成，每一列有一個列標題，相當于數(shù)據(jù)庫的字段名，如“產(chǎn)品名稱”、“交驗數(shù)”“工廢率”等等，每一列的數(shù)據(jù)屬性相同，每一行構成一

3、條記錄，每一條記錄存放一組相關數(shù)據(jù)。B、數(shù)據(jù)列表應避免空白行和空白列，單元格不要以空格開頭。如下：1.1、數(shù)據(jù)透視表在報表中的運用（3 3）、建立數(shù)據(jù)透視表的步驟：）、建立數(shù)據(jù)透視表的步驟： 確定要放置透視表的位置，本文以Excel中sheet1作為數(shù)據(jù)列表（基礎表），將sheet2作為透視表存放位置為例進行講解。 鼠標點擊在sheet2的A1處（根據(jù)需要也可以是別的空白單元格），在菜單欄上選擇“插入”，選擇彈出的“數(shù)據(jù)透視表”，數(shù)據(jù)透視表存放位置為sheet2的A1處，如下。透視表放置位置121.1、數(shù)據(jù)透視表在報表中的運用（3 3）、建立數(shù)據(jù)透視表的步驟：）、建立數(shù)據(jù)透視表的步驟： 選擇要

4、分析的數(shù)據(jù)。單擊“表/區(qū)域”的折疊按鈕，在之前創(chuàng)建好的“基礎表”中選擇需要分析的數(shù)據(jù)列。如圖選擇AM列，選擇好后收起折疊窗口，點擊“確定”，數(shù)據(jù)選擇就完成了。接下來我們進行數(shù)據(jù)布局。單擊折疊按鈕，選擇要分析分數(shù)據(jù)3選擇需要進行分析的列，如圖A-M列。1.1、數(shù)據(jù)透視表在報表中的運用（3 3）、建立數(shù)據(jù)透視表的步驟：）、建立數(shù)據(jù)透視表的步驟： 數(shù)據(jù)布局。步驟3點擊確認后會回到SHEET2中，表格右邊出現(xiàn)如下圖1所示的提示。根據(jù)需要將“數(shù)據(jù)透視表字段”中的字段用鼠標拖到下面四個表框中的相應位置（篩選器，行、列、值），如圖2.圖1圖24拖動1.1、數(shù)據(jù)透視表在報表中的運用（3 3）、建立數(shù)據(jù)透視表的

5、步驟：）、建立數(shù)據(jù)透視表的步驟： 調(diào)整表格。完成圖2的拖動后， Excel表上生成如下圖所示透視表。注意，此時并沒有完成，還需要根據(jù)實際情況作調(diào)整。值字段的計算我們常用“求和、計數(shù)、平均值”，可根據(jù)情況調(diào)整。雙擊單元格“B5”彈出“值字段設置”窗口，在 “計算類型”中將默認的“計數(shù)”改成“求和”，在“自定義名稱”處根據(jù)需要定義名稱51.1、數(shù)據(jù)透視表在報表中的運用（3 3）、建立數(shù)據(jù)透視表的步驟：）、建立數(shù)據(jù)透視表的步驟： 調(diào)整表格。除了上面的情況需要調(diào)整外，對于需要通過公式求百分比的“值字段”也要編輯。單擊現(xiàn)有透視表中任一單元格，在菜單欄上選擇“分析”中的“字段、項目和集”，在彈出的對話框中

6、編輯需要用公式計算的項目（此項目基礎表中可有可無）。671、“名稱”：自定義。2、“公式”：在“字段”中雙擊選擇。其他符號（=、/、*）鍵盤輸入。1.1、數(shù)據(jù)透視表在報表中的運用（3 3）、建立數(shù)據(jù)透視表的步驟：）、建立數(shù)據(jù)透視表的步驟： 調(diào)整表格。根據(jù)以上步驟得出的字段會自動添加到“數(shù)據(jù)透視表字段”中，用鼠標選中拖至“值”一欄中，得到如下圖所示透視表。值字段名稱根據(jù)需要自行變更（雙擊字段名稱可編輯）。這樣一個透視表就完成了。8鼠標下托鼠標下托91.1、數(shù)據(jù)透視表在報表中的運用（3 3）、建立數(shù)據(jù)透視表的步驟：）、建立數(shù)據(jù)透視表的步驟： 功能使用技巧。1、單擊篩選項下拉按鈕（如圖紅色框內(nèi)），可

7、進行不同條件下數(shù)據(jù)篩選、匯總。（可選擇不同日期、批量/新品等狀態(tài)下產(chǎn)品的廢損匯總情況）2、“行標簽”折疊按鈕打開可進行產(chǎn)品選擇。3、雙擊“數(shù)據(jù)區(qū)域”中任意單元格，可生成與此數(shù)據(jù)所在行相關聯(lián)的基礎表信息。（如點擊交驗數(shù)520，則另外生成一個Sheet,里面包含基礎表中EC5進氣罩蓋匯總前的所有信息。）數(shù)據(jù)數(shù)據(jù)區(qū)域區(qū)域篩選項篩選項第一節(jié)第一節(jié)一一第二節(jié)第二節(jié)二二1、數(shù)據(jù)透視表在報表中的運用2、常用函數(shù)1、EXCEL與品質7大工具的結合運用辦公軟件實用技巧講解1.2、常用函數(shù)所涉及的Excel函數(shù):1.邏輯運算函數(shù)邏輯運算函數(shù):= = + - / *2. 數(shù)學和三角函數(shù)數(shù)學和三角函數(shù):SUM： 返回

8、某一單元格區(qū)域中所有數(shù)字之和數(shù)字之和。如SUM(A1:F1):求A1到F1區(qū)域單元格數(shù)字之和。(要注意SUM和COUNT的區(qū)別）Vlookup：Excel中的一個縱向查找函數(shù),是按列列查找，最終返回該列所需查詢列序所對應的值。此功能在報表建立中多處用到。如輸入產(chǎn)品名稱，產(chǎn)品單重/材料牌號等自動生成。COUNT：計算某個區(qū)域中包含數(shù)字數(shù)字單元格的個數(shù)單元格的個數(shù)。COUNTA：計算某個區(qū)域中非空非空單元格的個數(shù)單元格的個數(shù)。COUNTIF：計算某個區(qū)域中滿足給定條件滿足給定條件的的單元格數(shù)目單元格數(shù)目。SUMIF：對滿足條件滿足條件的單元格求和求和。 3. 其他其他:$ 鎖定目標選項. 如:SU

9、M($C$8:H8):在單元格C8到H8的所有數(shù)字，C8項是不隨鼠標拉動或粘貼而變更的,H8項則會變化.1.2、常用函數(shù)4. 其他常用功能：其他常用功能：u 單元格單元格分行分行：如果你是想在EXCEL單元格中分行，則必須在單元格中輸入硬回車，即按住Alt鍵的同時按回車鍵。u EXCEL通過數(shù)據(jù)有效性序列設置下拉列表的通過數(shù)據(jù)有效性序列設置下拉列表的技巧：技巧：選擇列A，點擊菜單欄中“數(shù)據(jù)”，選擇“數(shù)據(jù)驗證”（老版本的Office是“數(shù)據(jù)有效”），將“設置”中的額“允許”選為“序列”，“來源”：點擊折疊按鈕選擇列F，然后“確定”就完成了。1.2、常用函數(shù)u 輸入無序數(shù)據(jù)輸入無序數(shù)據(jù)在Excel

10、數(shù)據(jù)表中，我們經(jīng)常要輸入大批量的數(shù)據(jù)，如學生的學籍號、身份證號等。這些數(shù)值一般都無規(guī)則，不能用“填充序列”的方法來完成。通過觀察后我們發(fā)現(xiàn)，這些數(shù)據(jù)至少前幾位是相同的，只有后面的幾位數(shù)值不同。通過下面的設置，我們只要輸入后面幾位不同的數(shù)據(jù)，前面相同的部分由系統(tǒng)自動添加，這樣就大大減少了輸入量。例如以壓鑄批次號為例，假設由7位數(shù)值組成，每月前4位相同（年月），如4月份，均為1404XXX，后3位為不規(guī)則數(shù)字（日期、班組），如批次號為1404121、1404252等。操作步驟如下：選中學籍號字段所在的列，鼠標右擊，選中“設置單元格格式”，單擊“數(shù)字”“分類”中的“自定義”，單擊“格式”菜單中的“單

11、元格”命令，在“分類”中選擇“自定義”，在“類型”文本框中輸入“1404000”。不同的3位數(shù)字全部用“0”來表示，有幾位不同就加入幾個“0”，確定退出后，輸入“121”按回車鍵，便得到了“1404121”，輸入“252”按回車便得到了“1404252”。身份證號的輸入與此類似。選擇“自定義”輸入需要的數(shù)值1.2、常用函數(shù)u 如如何何在已有的單元格中批量加入一段固定字符在已有的單元格中批量加入一段固定字符？例如：在單位的人事資料，在excel中輸入后，由于上級要求在原來的職稱證書的號碼全部再加兩位，即要在每個人的證書號碼前再添上兩位數(shù)13，如果一個一個改的話實在太麻煩了，那么我們可以用下面的辦

12、法，省時又省力： 1）假設證書號在A列，在A列后點擊鼠標右鍵，插入一列，為B列 ； 2）在B2單元格寫入： =“13” & A2 后回車； 3）看到結果為 13xxxxxxxxxxxxx 了嗎？鼠標放到B2位置，單元格的下方不是有一個小方點嗎，按著鼠標左鍵往下拖動直到結束。當你放開鼠標左鍵時就全部都改好了。 若是在原證書號后面加13 則在B2單元格中寫入：=A2 & “13” 后回車。 此方法也可將兩列中對應單元格字符合并，如=A2&B2后回車，在用鼠標下托公式，那么A、B兩列內(nèi)容就合并了。CTRL+CTRL+第一節(jié)第一節(jié)一一第二節(jié)第二節(jié)二二1、數(shù)據(jù)透視表在報表中的運用

13、2、常用函數(shù)1、EXCEL與品質7大工具的結合運用辦公軟件實用技巧講解2.1、EXCEL與品質7大工具的結合運用278182125482927201338.50%63.71%81.02%87.67%91.69%95.43%98.20%100.00%0.00%20.00%40.00%60.00%80.00%100.00%120.00%050100150200250300缺肉/多肉試漏冷隔氣孔夾皮變形裂紋氣泡2月月T53離殼壓鑄料廢統(tǒng)計離殼壓鑄料廢統(tǒng)計T53離合器殼體累積比例柏拉圖柏拉圖與與EXCEL的結合運用的結合運用首先，我們來看一個關于T53離殼2月質量分析：從柏拉圖中可看成出影響最主要因素

14、：缺肉/多肉、試漏、冷隔（80%原則）2.1、EXCEL與品質7大工具的結合運用柏拉圖柏拉圖與與EXCEL的結合運用的結合運用怎樣制作完成怎樣制作完成呢？呢？所涉及的Excel函數(shù):1.邏輯運算函數(shù)邏輯運算函數(shù):= = + - / *2. 數(shù)學和三角函數(shù)數(shù)學和三角函數(shù): SUM：返回某一單元格區(qū)域中所有數(shù)字之和數(shù)字之和。3. 其他其他:$ 鎖定目標選項. 如:SUM($C$8:H8):在單元格C8到H8的所有數(shù)字，C8項是不隨鼠標拉動或粘貼而變更的,H8項則會變化.2.1、EXCEL與品質7大工具的結合運用柏拉圖柏拉圖與與EXCEL的結合運用的結合運用用以上提及的函數(shù)在Excel表格里輸入,如

15、下:B10=B9/SUM($B$9:$I$9): 單元格單元格B9除除于于單元格單元格B7至至I9的的數(shù)據(jù)之數(shù)據(jù)之和。將鼠標放置和。將鼠標放置B10右下角拖動十子光標，完成右下角拖動十子光標，完成“比例比例”這一行所有計算。（或者輸入這一行所有計算。（或者輸入=B9/278，拖動公式完成計算）拖動公式完成計算） Ctrl+在工作sheet里面進行顯示結果和運算式之間的切換.切換后結果如右:2.1、EXCEL與品質7大工具的結合運用柏拉圖柏拉圖與與EXCEL的結合運用的結合運用制作表格:點擊或: 插入圖表。選擇制圖區(qū)域,如下陰影區(qū)選擇如圖所示圖表類型。裝飾圖表（編輯標題、增加數(shù)據(jù)標簽、顏色填充等

16、）278182125482927201338.50%63.71%81.02%87.67%91.69%95.43%98.20%100.00%0.00%20.00%40.00%60.00%80.00%100.00%120.00%050100150200250300缺肉/多肉試漏冷隔氣孔夾皮變形裂紋氣泡2月月T53離殼壓鑄料廢統(tǒng)計離殼壓鑄料廢統(tǒng)計T53離合器殼體累積比例完成2.1、EXCEL與品質7大工具的結合運用直方直方圖與圖與EXCEL的結合運用的結合運用某客戶的電阻值要求為50020歐最近發(fā)現(xiàn)客戶的產(chǎn)品波動比較大，所以歸納了近段時間的連續(xù)七批IQC來料測量數(shù)值，數(shù)值如下：如何對該些數(shù)據(jù)進行處理

17、呢？我們就可以運用直方圖就行分析如何對該些數(shù)據(jù)進行處理呢？我們就可以運用直方圖就行分析!41216171074024681012141618個數(shù)個數(shù)區(qū)間值區(qū)間值電阻電阻500+/-20直方圖分析直方圖分析469.3478.6478.6487.9487.9497.2497.2506.5506.5515.8515.8525.1525.1534.42.1、EXCEL與品質7大工具的結合運用直方直方圖與圖與EXCEL的結合運用的結合運用從下圖我們可以輕松的分析出數(shù)據(jù)的分布情況。怎樣制作出得體美觀怎樣制作出得體美觀的直方圖呢？的直方圖呢？2.1、EXCEL與品質7大工具的結合運用直方直方圖與圖與EXCE

18、L的結合運用的結合運用所涉及的函數(shù)所涉及的函數(shù):COUNTIF：計算區(qū)域中滿足給定條件的單元格的個數(shù)。 MAX：返回一組值中的最大值。 MIN：返回一組值中的最小值。 CONCATENATE：將幾個文本字符串合并為一個文本字符串。 2. 數(shù)學與三角函數(shù)數(shù)學與三角函數(shù):LOG：按所指定的底數(shù)，返回一個數(shù)的對數(shù)。 3.文本函數(shù)文本函數(shù):FIXED：將數(shù)字按指定的小數(shù)位數(shù)進行取整，利用句號和逗號，以小數(shù)格式對該數(shù)進行格式設置，并以文本形式返回結果。2.1、EXCEL與品質7大工具的結合運用直方直方圖與圖與EXCEL的結合運用的結合運用步驟1：計算數(shù)據(jù)中的最大最小值步驟2：計算全距（最大值-最小值）步

19、驟3：計算組數(shù)K步驟4：計算組距（全距/組數(shù)K ）2.1、EXCEL與品質7大工具的結合運用直方直方圖與圖與EXCEL的結合運用的結合運用步驟5：計算上下組列用用CONCATENATE函數(shù)合并上下界函數(shù)合并上下界數(shù)值文本，計算數(shù)值文本，計算如下：（或者用如下：（或者用&符符號連接）號連接）2.1、EXCEL與品質7大工具的結合運用直方直方圖與圖與EXCEL的結合運用的結合運用步驟6：計算組列間的個數(shù)鍵入鍵入COUNTIF和和SUM函數(shù)函數(shù)運運算滿足條件算滿足條件的個數(shù)的個數(shù)步驟7：制作圖表把下面的單元格選取并點擊直方圖把下面的單元格選取并點擊直方圖即可制作開始直方圖圖表即可制作開始直方

20、圖圖表運算結果如下運算結果如下2.1、EXCEL與品質7大工具的結合運用直方直方圖與圖與EXCEL的結合運用的結合運用需要留意的是：4121617107405101520469.34 478.64 487.94 497.25 506.55 515.85 525.15個數(shù)區(qū)間值電阻500+/-20直方圖分析4121617107405101520個數(shù)區(qū)間值電阻500+/-20直方圖分析469.3478.6478.6487.9487.9497.2497.2506.5506.5515.8確定后，就完成直觀美麗的直方圖了！確定后，就完成直觀美麗的直方圖了！然后根據(jù)圖表找出產(chǎn)品存在的差異。然后根據(jù)圖表找出

21、產(chǎn)品存在的差異。2.1、EXCEL與品質7大工具的結合運用散點散點圖與圖與EXCEL的結合運用的結合運用某嵌件長度尺寸,由于公司和供應商之間一直在數(shù)據(jù)上面存在差異,選取30個樣板,分別注上編號測得數(shù)據(jù)如右側:所涉及的函數(shù)所涉及的函數(shù):返回單元格區(qū)域 array1 和 array2 之間的相關系數(shù)。使用相關系數(shù)可以確定兩種屬性之間的關系。例如，可以檢測某地的平均溫度和空調(diào)使用情況之間的關系。語法語法CORREL(array1,array2)Array1 第一組數(shù)值單元格區(qū)域。Array2 第二組數(shù)值單元格區(qū)域。1. 統(tǒng)計函數(shù)統(tǒng)計函數(shù): CORREL 表示多少次方; 比如:56就是說5的6次方2.數(shù)學運算符數(shù)學運算符: 2.1、EXCEL與品質7大工具的結合運用散點散點圖與圖與EXCEL的結合運用的結合運用=FIXED(CORREL(C7:C36,D7:D36),2)在單元格C37、C38分別鍵入:=FIXED(C372,2)可容易得到相關系數(shù)的可容易得到相關系數(shù)的結果結果然后彈出右圖對話框:插入圖表：2.1、EXCEL與品質7大工具的結合運用散點散點圖與圖與EXCEL的結合運用的結合運用完成上面操作后完成上面操作后,一個漂亮的散布圖就這樣誕生了一個漂亮的散布圖就這樣誕生了y = 0.6684x + 5.0277R2 = 0.6