EXCEL与质量报表及品质七大工具的结合运用1

1、办公软件常用技巧第一节第一节一一第二节第二节二二1、数据透视表在报表中的运用2、常用函数1、EXCEL与品质7大工具的结合运用办公软件实用技巧讲解1.1、数据透视表在报表中的运用（1 1）、）、“数据透视表数据透视表”的作用：的作用：您有超出您处理能力的数据吗？您想一眼就看出趋势和模式吗您有超出您处理能力的数据吗？您想一眼就看出趋势和模式吗？假设我们公司一年的质量报表全部采用Excel表，其中有几百、几千行数据，其中包含产品名称、操作日期、批次、合格数、料废数、工废数及各种缺陷数等等庞大信息。每个人想从繁杂的质量报表中得到不同信息，该如何操作？每个人想从繁杂的质量报表中得到不同信息，该如何操作

2、？“数据透视表”适合您的需要，它通过不同视角显示数据来进行比较、揭示和分析，从而将数据转化成有意义的信息，揭示数据的内涵。“数据透视表”功能能够将筛选、排序和分类汇总等操作依次完成，并生成汇总表格，是Excel强大数据处理能力的具体体现。1.1、数据透视表在报表中的运用（2 2）、数据清单的建立（基础表）：）、数据清单的建立（基础表）：在运用数据透视表之前，首先要建立在运用数据透视表之前，首先要建立可用的数据清单可用的数据清单。清单要求如下：清单要求如下：A、数据由若干列组成，每一列有一个列标题，相当于数据库的字段名，如“产品名称”、“交验数”“工废率”等等，每一列的数据属性相同，每一行构成一

3、条记录，每一条记录存放一组相关数据。B、数据列表应避免空白行和空白列，单元格不要以空格开头。如下：1.1、数据透视表在报表中的运用（3 3）、建立数据透视表的步骤：）、建立数据透视表的步骤： 确定要放置透视表的位置，本文以Excel中sheet1作为数据列表（基础表），将sheet2作为透视表存放位置为例进行讲解。 鼠标点击在sheet2的A1处（根据需要也可以是别的空白单元格），在菜单栏上选择“插入”，选择弹出的“数据透视表”，数据透视表存放位置为sheet2的A1处，如下。透视表放置位置121.1、数据透视表在报表中的运用（3 3）、建立数据透视表的步骤：）、建立数据透视表的步骤： 选择要

4、分析的数据。单击“表/区域”的折叠按钮，在之前创建好的“基础表”中选择需要分析的数据列。如图选择AM列，选择好后收起折叠窗口，点击“确定”，数据选择就完成了。接下来我们进行数据布局。单击折叠按钮，选择要分析分数据3选择需要进行分析的列，如图A-M列。1.1、数据透视表在报表中的运用（3 3）、建立数据透视表的步骤：）、建立数据透视表的步骤： 数据布局。步骤3点击确认后会回到SHEET2中，表格右边出现如下图1所示的提示。根据需要将“数据透视表字段”中的字段用鼠标拖到下面四个表框中的相应位置（筛选器，行、列、值），如图2.图1图24拖动1.1、数据透视表在报表中的运用（3 3）、建立数据透视表的

5、步骤：）、建立数据透视表的步骤： 调整表格。完成图2的拖动后， Excel表上生成如下图所示透视表。注意，此时并没有完成，还需要根据实际情况作调整。值字段的计算我们常用“求和、计数、平均值”，可根据情况调整。双击单元格“B5”弹出“值字段设置”窗口，在 “计算类型”中将默认的“计数”改成“求和”，在“自定义名称”处根据需要定义名称51.1、数据透视表在报表中的运用（3 3）、建立数据透视表的步骤：）、建立数据透视表的步骤： 调整表格。除了上面的情况需要调整外，对于需要通过公式求百分比的“值字段”也要编辑。单击现有透视表中任一单元格，在菜单栏上选择“分析”中的“字段、项目和集”，在弹出的对话框中

6、编辑需要用公式计算的项目（此项目基础表中可有可无）。671、“名称”：自定义。2、“公式”：在“字段”中双击选择。其他符号（=、/、*）键盘输入。1.1、数据透视表在报表中的运用（3 3）、建立数据透视表的步骤：）、建立数据透视表的步骤： 调整表格。根据以上步骤得出的字段会自动添加到“数据透视表字段”中，用鼠标选中拖至“值”一栏中，得到如下图所示透视表。值字段名称根据需要自行变更（双击字段名称可编辑）。这样一个透视表就完成了。8鼠标下托鼠标下托91.1、数据透视表在报表中的运用（3 3）、建立数据透视表的步骤：）、建立数据透视表的步骤： 功能使用技巧。1、单击筛选项下拉按钮（如图红色框内），可

7、进行不同条件下数据筛选、汇总。（可选择不同日期、批量/新品等状态下产品的废损汇总情况）2、“行标签”折叠按钮打开可进行产品选择。3、双击“数据区域”中任意单元格，可生成与此数据所在行相关联的基础表信息。（如点击交验数520，则另外生成一个Sheet,里面包含基础表中EC5进气罩盖汇总前的所有信息。）数据数据区域区域筛选项筛选项第一节第一节一一第二节第二节二二1、数据透视表在报表中的运用2、常用函数1、EXCEL与品质7大工具的结合运用办公软件实用技巧讲解1.2、常用函数所涉及的Excel函数:1.逻辑运算函数逻辑运算函数:= = + - / *2. 数学和三角函数数学和三角函数:SUM： 返回

8、某一单元格区域中所有数字之和数字之和。如SUM(A1:F1):求A1到F1区域单元格数字之和。(要注意SUM和COUNT的区别）Vlookup：Excel中的一个纵向查找函数,是按列列查找，最终返回该列所需查询列序所对应的值。此功能在报表建立中多处用到。如输入产品名称，产品单重/材料牌号等自动生成。COUNT：计算某个区域中包含数字数字单元格的个数单元格的个数。COUNTA：计算某个区域中非空非空单元格的个数单元格的个数。COUNTIF：计算某个区域中满足给定条件满足给定条件的的单元格数目单元格数目。SUMIF：对满足条件满足条件的单元格求和求和。 3. 其他其他:$ 锁定目标选项. 如:SU

9、M($C$8:H8):在单元格C8到H8的所有数字，C8项是不随鼠标拉动或粘贴而变更的,H8项则会变化.1.2、常用函数4. 其他常用功能：其他常用功能：u 单元格单元格分行分行：如果你是想在EXCEL单元格中分行，则必须在单元格中输入硬回车，即按住Alt键的同时按回车键。u EXCEL通过数据有效性序列设置下拉列表的通过数据有效性序列设置下拉列表的技巧：技巧：选择列A，点击菜单栏中“数据”，选择“数据验证”（老版本的Office是“数据有效”），将“设置”中的额“允许”选为“序列”，“来源”：点击折叠按钮选择列F，然后“确定”就完成了。1.2、常用函数u 输入无序数据输入无序数据在Excel

10、数据表中，我们经常要输入大批量的数据，如学生的学籍号、身份证号等。这些数值一般都无规则，不能用“填充序列”的方法来完成。通过观察后我们发现，这些数据至少前几位是相同的，只有后面的几位数值不同。通过下面的设置，我们只要输入后面几位不同的数据，前面相同的部分由系统自动添加，这样就大大减少了输入量。例如以压铸批次号为例，假设由7位数值组成，每月前4位相同（年月），如4月份，均为1404XXX，后3位为不规则数字（日期、班组），如批次号为1404121、1404252等。操作步骤如下：选中学籍号字段所在的列，鼠标右击，选中“设置单元格格式”，单击“数字”“分类”中的“自定义”，单击“格式”菜单中的“单

11、元格”命令，在“分类”中选择“自定义”，在“类型”文本框中输入“1404000”。不同的3位数字全部用“0”来表示，有几位不同就加入几个“0”，确定退出后，输入“121”按回车键，便得到了“1404121”，输入“252”按回车便得到了“1404252”。身份证号的输入与此类似。选择“自定义”输入需要的数值1.2、常用函数u 如如何何在已有的单元格中批量加入一段固定字符在已有的单元格中批量加入一段固定字符？例如：在单位的人事资料，在excel中输入后，由于上级要求在原来的职称证书的号码全部再加两位，即要在每个人的证书号码前再添上两位数13，如果一个一个改的话实在太麻烦了，那么我们可以用下面的办

12、法，省时又省力： 1）假设证书号在A列，在A列后点击鼠标右键，插入一列，为B列 ； 2）在B2单元格写入： =“13” & A2 后回车； 3）看到结果为 13xxxxxxxxxxxxx 了吗？鼠标放到B2位置，单元格的下方不是有一个小方点吗，按着鼠标左键往下拖动直到结束。当你放开鼠标左键时就全部都改好了。 若是在原证书号后面加13 则在B2单元格中写入：=A2 & “13” 后回车。 此方法也可将两列中对应单元格字符合并，如=A2&B2后回车，在用鼠标下托公式，那么A、B两列内容就合并了。CTRL+CTRL+第一节第一节一一第二节第二节二二1、数据透视表在报表中的运用2、常用函数1、EXCE

13、L与品质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离壳压铸料废统计离壳压铸料废统计T53离合器壳体累积比例柏拉图柏拉图与与EXCEL的结合运用的结合运用首先，我们来看一个关于T53离壳2月质量分析：从柏拉图中可看成出影响最主要因素：缺肉/多肉、试漏、冷隔

14、（80%原则）2.1、EXCEL与品质7大工具的结合运用柏拉图柏拉图与与EXCEL的结合运用的结合运用怎样制作完成怎样制作完成呢？呢？所涉及的Excel函数:1.逻辑运算函数逻辑运算函数:= = + - / *2. 数学和三角函数数学和三角函数: SUM：返回某一单元格区域中所有数字之和数字之和。3. 其他其他:$ 锁定目标选项. 如:SUM($C$8:H8):在单元格C8到H8的所有数字，C8项是不随鼠标拉动或粘贴而变更的,H8项则会变化.2.1、EXCEL与品质7大工具的结合运用柏拉图柏拉图与与EXCEL的结合运用的结合运用用以上提及的函数在Excel表格里输入,如下:B10=B9/SUM

15、($B$9:$I$9): 单元格单元格B9除除于于单元格单元格B7至至I9的的数据之数据之和。将鼠标放置和。将鼠标放置B10右下角拖动十子光标，完成右下角拖动十子光标，完成“比例比例”这一行所有计算。（或者输入这一行所有计算。（或者输入=B9/278，拖动公式完成计算）拖动公式完成计算） Ctrl+在工作sheet里面进行显示结果和运算式之间的切换.切换后结果如右:2.1、EXCEL与品质7大工具的结合运用柏拉图柏拉图与与EXCEL的结合运用的结合运用制作表格:点击或: 插入图表。选择制图区域,如下阴影区选择如图所示图表类型。装饰图表（编辑标题、增加数据标签、颜色填充等）27818212548

16、2927201338.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离壳压铸料废统计离壳压铸料废统计T53离合器壳体累积比例完成2.1、EXCEL与品质7大工具的结合运用直方直方图与图与EXCEL的结合运用的结合运用某客户的电阻值要求为50020欧最近发现客户的产品波动比较大，所以归纳了近段时间的连续七批IQC来料测量数值，数值如下：如何对该些数据进行处理呢？我们就可以运用直方图

17、就行分析如何对该些数据进行处理呢？我们就可以运用直方图就行分析!41216171074024681012141618个数个数区间值区间值电阻电阻500+/-20直方图分析直方图分析469.3478.6478.6487.9487.9497.2497.2506.5506.5515.8515.8525.1525.1534.42.1、EXCEL与品质7大工具的结合运用直方直方图与图与EXCEL的结合运用的结合运用从下图我们可以轻松的分析出数据的分布情况。怎样制作出得体美观怎样制作出得体美观的直方图呢？的直方图呢？2.1、EXCEL与品质7大工具的结合运用直方直方图与图与EXCEL的结合运用的结合运用所

18、涉及的函数所涉及的函数:COUNTIF：计算区域中满足给定条件的单元格的个数。 MAX：返回一组值中的最大值。 MIN：返回一组值中的最小值。 CONCATENATE：将几个文本字符串合并为一个文本字符串。 2. 数学与三角函数数学与三角函数:LOG：按所指定的底数，返回一个数的对数。 3.文本函数文本函数:FIXED：将数字按指定的小数位数进行取整，利用句号和逗号，以小数格式对该数进行格式设置，并以文本形式返回结果。2.1、EXCEL与品质7大工具的结合运用直方直方图与图与EXCEL的结合运用的结合运用步骤1：计算数据中的最大最小值步骤2：计算全距（最大值-最小值）步骤3：计算组数K步骤4：

19、计算组距（全距/组数K ）2.1、EXCEL与品质7大工具的结合运用直方直方图与图与EXCEL的结合运用的结合运用步骤5：计算上下组列用用CONCATENATE函数合并上下界函数合并上下界数值文本，计算数值文本，计算如下：（或者用如下：（或者用&符符号连接）号连接）2.1、EXCEL与品质7大工具的结合运用直方直方图与图与EXCEL的结合运用的结合运用步骤6：计算组列间的个数键入键入COUNTIF和和SUM函数函数运运算满足条件算满足条件的个数的个数步骤7：制作图表把下面的单元格选取并点击直方图把下面的单元格选取并点击直方图即可制作开始直方图图表即可制作开始直方图图表运算结果如下运算结果如下2

20、.1、EXCEL与品质7大工具的结合运用直方直方图与图与EXCEL的结合运用的结合运用需要留意的是：4121617107405101520469.34 478.64 487.94 497.25 506.55 515.85 525.15个数区间值电阻500+/-20直方图分析4121617107405101520个数区间值电阻500+/-20直方图分析469.3478.6478.6487.9487.9497.2497.2506.5506.5515.8确定后，就完成直观美丽的直方图了！确定后，就完成直观美丽的直方图了！然后根据图表找出产品存在的差异。然后根据图表找出产品存在的差异。2.1、EXCE

21、L与品质7大工具的结合运用散点散点图与图与EXCEL的结合运用的结合运用某嵌件长度尺寸,由于公司和供应商之间一直在数据上面存在差异,选取30个样板,分别注上编号测得数据如右侧:所涉及的函数所涉及的函数:返回单元格区域 array1 和 array2 之间的相关系数。使用相关系数可以确定两种属性之间的关系。例如，可以检测某地的平均温度和空调使用情况之间的关系。语法语法CORREL(array1,array2)Array1 第一组数值单元格区域。Array2 第二组数值单元格区域。1. 统计函数统计函数: CORREL 表示多少次方; 比如:56就是说5的6次方2.数学运算符数学运算符: 2.1、EXCEL与品质7大工具的结合运用散点散点图与图与EXCEL的结合运用的结合运用=FIXED(CORREL(C7:C36,D7:D36),2)在单元格C37、C38分别键入:=FIXED(C372,2)可容易得到相关系数的可容易得到相关系数的结果结果然后弹出右图对话框:插入图表：2.1、EXCEL与品质7大工具的结合运用散点散点图与图与EXCEL的结合运用的结合运用完成上面操作后完成上面操作后,一个漂亮的散布图就这样诞生了一个漂亮的散布图就这样诞生了y = 0.6684x + 5.0277R2 = 0.627