VBA在统计分析中的应用.doc

1 质量统计分析系统该质量分析系统就是把下表(总统计)的数据按不同的要求进行统计，比如按日期、产品、操作者或按日期和产品以及按日期和姓名等要求进行对产品不合格因素的各项进行统计分析，即使是使用Excel里自带的函数功能，面对这样的要求也是爱莫能助了。不过还好，Excel里的VBA可以让我们实现，而且速度快，计算结果准确。2.1 基本数据表既然是统计，那么无论如何就得有一个带有很多数据的表，也就是统计系统要求根据不同日期、产品、和操作者及产品不合格项目对表格数据的录入和编辑，对数据的录入也就是把收集来数据输入到Excel的工作表中。当然，面大量的数据，数据表的样式如何设计？才方便数据的输入，同时也要方便后序工作的统计要求；首先，从数据的来源来看，我们收集来的数据是零乱的，也就是不同日期会有不同的产品，也会有不同的操作者等等，如果哪怕是按其中一种要求来输入数据，那么在输入数据时都会很艰难，不仅慢，而且也容易出错。对于一个系统而言，如何使录入数据时的方便就是一个主要的问题；这里把录入数据和存储数据的在这个表格叫基本数据表，它主要是负责具体单位的数据的输入和存储，无论如何进行单位项目数据的录入和编辑操作都要使其不会影响系统运行的界面，也就是在进行统计分析时是不会改变此工作表的数据的，这样的话统计结果就需要以其它方式表现出来，比如在新的表格中或图表中得到统计结果。所以，基本数据表的数据输入就按所收集来的数据顺序进行一一输入，这样可以提高输入速度，而且也不容易出错。具体表格样式还可以根据使用者的具体要求进行设计。2.1.1 数据表的优化根据要求，我们在输入数据时，会遇到相同对象的重复输入；为了减少数据的输入次数，这里力争做到数据输入的一次性，即相同的数据只输入一次，如操作者送检的不合格数与检验员检的不合格数是一样的，而操作者与检验员是一对多的关系，从上表F16(27：操作者的编号)与F17(49：检验员的编号)和F18(50：检验员的编号)（关于编号问题下一步说明），对于H16的数据和H17与H18的和是相同的，这里就没有必要在对H16进行输入了，只要一输入H17和H18的数据，那么H16的数据就会自动生成了，同理，对于H列的数据和(I列:X列)的数据也是一样的，而（I列:X列）的数据是必须一一对应的，也是必须输入的，所以这里要求只要输入（I列:X列）的数据，那么H列的数据也要自动生成。关于操作者使用的是编号，主要是作为一个数据的输入载体，如果不断的在汉字和数字之间相互输入，那么汉字的输入必然会成为该操作的瓶颈，所以把姓名和操作者编号统一联系起来，只要在F列输入编号，那么E列就自动生成与之对应的姓名了，同时为了区分检验员和操作者，把140号定为操作者的号，而4160定为检验员的号。既然上面已经提到操作者与检验员的一对多的关系，那么到底是一对几呢？也就是什么时候才是操作者与相应检验员的数据的输入结束呢？从编程的概念来将，如何控制这个起点和结束点呢？当然，一个操作者就必然是该组数据的起点，而下一个操作者（可以和上一操作者相同）的上一个数据就成为结束点了，也就是数据表会成在这样的关系： 操作者检验员1检验员2检验员X 操作者检验员X也就是只要一遇到操作者，那么操作者与相应检验员的关系就结束了。为了配合姓名与编号的一一对应关系，这里需要新建了一个表(操作人员)：这个表主要是为了能在总统计工作表中输入操作者编号时能自动从这个表中返回操作者的姓名。到此，该系统的基本数据表的基本框架已差不多完成，即表格的表现形式、要求都已经明确。现在的关键就是如何应用VBA编程来实现该表格在输入时的自动计算了。2.1.2 基本数据表的VBA编程 首先，为了能自动运行，那么就必须有什么对象被触发而引起某一事件的产生了，在Excel的VBA里有当活动单元格被改变时就会触发的事件：Private Sub Worksheet_SelectionChange(ByVal Target As Range)Call Auto 调用自动运算过程End Sub这个事件，只要活动单元格一改变，就可以让Excel运行其中的Auto过程，Sub Auto()为了使运算速度快，不至于把所有的数据都重复计算，那么就只需要计算刚输入的数据就可以了，那么如何去得到这个刚输入的数据呢？由于输入数据后单元格及有内容了，就不是空的了，么就让VBA去找到这个空格，这样只要一找到空格，那它的上一个数据不就是刚输入的数据了吗？i = HKSDo While Cells(i, CZZ) i = i + 1Loop找到了刚输入的数据，接下来就可以一步一步实现数据的自动计算了，首先，把输入操作者的编号转变成操作者的姓名Call SRName(i) 调用SRName过程，该过程就要根据操作者的编号在操作人员表里找到与编号相对应的姓名； Sub SRName(X)N = X - 1i = 2 Do While (Worksheets(2).Cells(i, 2) Cells(N, CZZ) 找到要找的编号数据，如果找不到，就继续找， If Worksheets(2).Cells(i, 2) Then 又当找到空格时就可以不用再找了 i = i + 1 Else Exit Sub End IfLoopCells(N, XM) = Worksheets(2).Cells(i, 3) 把找到的名字给输入到与之对应的单元格End Sub 其次就是计算检验退修的数据了，之前说过检验退修的数据是不用手动输入的，而是根据各个缺陷数累加而得的，If i = 2 ThenElseCall TX(i)End IfSub TX(X) 自动算出检验退修总数i = QXKS1 JS = QXJS1M = 0N = 0Do While i = JSM = M + Cells(X - 1, i)i = i + 1LoopCells(X - 1, JYFX) = MEnd Sub再次就是对表的进一步优化了，首先就是在操作人的缺陷里不用输入数据，否则给予提示，If i = 2 ThenElseCall KZSR(i - 1) 控制不准在操作人的缺陷里输入数据只要是操作者的数据，就要判断在缺陷项目里有没有输入数据，若有就会给予提示，同时还要把输入的数据给清除掉，否则就继续往下运行。Sub KZSR(i)FK = Int(Worksheets(2).TextBox1)i1 = QXKS1If Cells(i, CZZ) = FK Then Do While i1 = 0 Then MsgBox (此处不需要输入数据！) Cells(i, i1) = Exit Do Else i1 = i1 + 1 End If LoopElseEnd IfEnd SubCall CZETX(i) 调用操作者退修过程该过程就是根据检验员退修的数自动计算出操作者的不合格数。全面已经提到，一个操作者有n个检验给其检查，所以首先就要算出有几个检验员给该操作者检产品，这里是这样进行的，只要刚输入的数据是检验的，那么这个数据到上一个操作者之间的数据都是检验员的数据，然后把这些检验员退回的不合格数一起加就得到该操作者的不合格数了。Sub CZETX (i) FK = Int(Worksheets(2).TextBox1)ks = i - 1JS = i - 1Do While Cells(ks, CZZ) FK ks = ks - 1LoopCall TX1(ks, JS)End SubSub TX1(ks, JS) 计算n个检验员退修总数M = 0i = ks + 1Do While i FK Then Exit SubElse If ks FK ks = ks - 1 If ks 3 Then Exit Do Else End If Loop End IfEnd IfM = 0i = ksDo While i JSM = M + Cells(i, SJ)i = i + 1LoopIf Cells(ks - 1, SJ) = M ThenElse MsgBox (你输入的数据有误！请核对后重新输入！) i = 1 Do While i = CZZ Cells(JS + 1, i) = i = i + 1 Loop Cells(JS, CZZ) = Cells(ks - 1, CZZ).SelectEnd IfEnd SubEnd IfEnd Sub 经过上面的分析和程序设计，基本数据表及相关的要求通过VBA程序都得到了解决，这样就可以更快，更准确在输入数据了。2.2 统计分析 要分析，就必须对数据进行统计，本系统要求得到如下几种统计结果： a) 按操作者统计其一次交检合格率；b) 按检验员统计其退修率；c) 按产品统计其合格率；d) 合格率最底的产品还要将其各种缺陷类型及缺陷率统计出来，同时还要统计出与这个产品相关操作者的返修率情况；现在根据上面的要求来设计VBA函数了，由于函数代码较多，就不逐一分析了，这里只针对一种要求进行分析，就从a)要求来说，从统计表可以看出，统计的数据就是操作者送检的产品总数和不合格的总数，同时计算出不合格率。而这些数据的来源就是基本数据表里的数据了，面对成千上万条的数据，为了让VBA更好的计算，就要有一个暂时装数据的载体，这里可以使用数组，而且根据表的格式，此数组还是二维的；然后把符合条件的数据装到此数组里，即要统计的是哪个小组在什么时间段生产产品的情况。具体分析如下段程序：Private Sub CommandButton3_Click() XZ1 = XZ(Cells(1, 13) 确定对哪个组进行统计，i = TJI() 算出定义的数组要有多大，ReDim TJ(1 To 2) 定义数组来装总数和总的不合格数，ReDim M(1 To i - 1) 定义数组来单独装操作人的姓名ReDim Data(1 To i - 1, 1 To 3) 明确要装3列的数据，即姓名、送检数和退修数Mt = 0 用来计算到底有多少条符合要求的数据If OptionButton1.Value = True Then 窗体3.Show KSRQ = Int(窗体3.TextBox1) JSRQ = Int(窗体3.TextBox2) Call PDRQ(KSRQ, JSRQ) Call TYTJCZZ1(XZ1, TJ, Data, M, Mt, KSRQ, JSRQ) 调用函数把XZ1小组在KSRQ日期到JSRQ 日期时间段里符合要求的数据装入到Data里，同时返回TJ和M及Mt；Else Call TYTJCZZ(XZ1, TJ, Data, M, Mt) 调用函数把XZ1小组的全部数据进行统计End IfCall SCTJ(TJ) 调用函数把总数和总不合格数输出给统计表ReDim N(1 To Mt) 用来装实际的人数的姓名，因为在基本数据表里的操作者或检验员都有重复的数据，统计就是把这些重复的数据都累计得到一个总的数据，那么就要在这些重复的姓名里统计出没有重复的姓名！i = 1Do While i = 20 Call zhaoc(M, i, N) 从M里统计出没有重复的姓名装在N里。 i = i + 1LoopNt = TJSZ(N, Mt) 计算 N里到底有多少人。到这里已经得到了在要求的条件下都是那些人员参与了生产，然后按每一个人逐一计算出他送检的总数和不合格数，最后输出到统计表里。Call TJSC(Data, N, Mt, Nt)调用函数，根据N里的操作人的姓名在Data进行统计，函数如下：Function TJSC(Data, N, Mt, Nt) ReDim MN(1 To Nt, 1 To 5) 定义一个数组来装统计的结果，即可以装操作者姓名、产品总数、不合格总数、不合格率和合格率， i = 1 j = 1Do While i = Nt Do While j = Mt If Data(j, 1) = N(i) Then当在Data里找到和N(i)相同的姓名时，就把姓名赋给MN(i, 1)， MN(i, 1) = N(i)然后累加产品总数和不合格数， MN(i, 2) = MN(i, 2) + Data(j, 2) MN(i, 3) = MN(i, 3) + Data(j, 3)最后计算出不合格率和合格率 MN(i, 4) = MN(i, 3) / MN(i, 2) MN(i, 5) = 1 - MN(i, 4) Else End If j = j + 1 Loop j = 1 i = i + 1Loop当运行到这里，得到的MN就是统计的结果了，而这个结果只是在临时的载体里，要表现出来，还要将其输出到统计表中，即：Call SC(MN, Nt) 调用函数把MN输出到统计表中End FunctionEnd Sub经过上面的VBA程序就实现了对基本数据表的统计，而其他几张统计表的统计过程是差不多的，只要把函数或过程里的参数改一下就可以实现了。2.3 对统计结果进行排序处理统计表里最先输出的结果是按操作者的先后顺序进行排列的，为了更为直观的反映出统计的情况和分析问题，最好是对其再进行排序处理，这里主要是分析操作者送检的不合格率，所以就要按不合格率进行排序。排序，可以按升序或降序进行。根据表格格式，为了方便操作，这里并没有采用Excel自带的排序功能进行，同样是采用VBA编程实现，这样使得操作起来方便、快捷。排序就要让与之相关联的数据也要一起改变，所以也要把统计表里的数据随按要求排序的数据一起进行处理了，下面是一个升序过程，降序时只要把作“大于”相比换成“小于”就可以了。Sub DataUp()M = TJN(ks1)N = 5 X = 4 ReDim Data(1 To M, 1 To N)Call SRSZ(Data, M, ks1) 把统计数据放在Data数组里Call Up(Data, M, N, X) 调用升序函数，按X列进行排序这里首先找到第一大的数据，然后找第二大的数据，依次类推找到第M大的数据，就可以实现排序的功能了。Function Up(Data, M, N, X) i = 1Do While i = M j = i + 1L1: Do While j Data(j, X) Then 把“”改成“”就是降序了 Call GLSJ(Data, i, j, N) 让与之关联的数据也跟着一起重新排序 Function GLSJ(Data, i, j, N) i1 = 1 Do While i1 = N 把与之关联的数据也一改变顺序 z = Data(i, i1) Data(i, i1) = Data(j, i1) Data(j, i1) = z i1 = i1 + 1 LoopEnd Functionj = j + 1 GoTo L1 E