一种使用统计学函数的Excel数据统计方法与流程

本发明涉及一种Excel数据统计方法，特别涉及一种使用统计学函数的Excel数据统计方法。

背景技术：

目前，Excel本身的计算能力不足以用于统计学的教学，学习以及科研，就算加上Excel之类的通用包也是不够的。通过调研，发现了Excel中使用MicrosoftData分析工具包的几种统计算法会产生错误的结果，例如使用Excel执行涉及相关独立变量的回归分析的问题。

目前，有相当多的第三方Excel外接程序是为了解决Excel不足以满足需求或者是使用Excel的MicrosoftData分析工具包有错误而编写的。例如，Analyse-it ，Data Analysis Plus，Fast Statistics，Lumenauto，NSEA，PHStat，PopTools，SigmaXL，statistiXL，StatTools，UNISTAT和XLSTAT。除了PopTools，它是专门为分析生态模型而编写的，这些加载项大都是商业产品，不具有开源的源码；这对于想要使用这些插件的用户，不是十分友好。

R是用于统计分析、绘图的语言和操作环境；R是属于GNU系统的一个自由、免费、源代码开放的软件，它是一个用于统计计算和统计制图的优秀工具。

技术实现要素：

本发明的目的是提供一种使用统计学函数的Excel数据统计方法，通过利用R的计算能力，解决使用Excel及其内部统计功能和程序数值不准确的问题。

本发明的目的是这样实现的：一种使用统计学函数的Excel数据统计方法，其特征在于，包括以下步骤：

1）通过R的编译器RGui加载一个R的Web服务器接口Rook；

2）在R里面搭建起Rook服务器；

3）在R里面编写统计函数；

4）统一R以及Excel编译环境VBA里的URL以及端口；

5）通过Rook服务器建立Excel与R的连接；

6）利用设计好的R内的统计函数作为统计工具，将Excel传过来的数据加以计算，并且将结果返回Excel。

作为本发明的进一步限定，步骤2）中Rook服务器的搭建方法具体为：

通过RGui加载Rook包，library(Rook)，并且建立一个类，在这个类里面建立一个call方法，方法设置只能传一个参数或者一个对象，设置这个方法的返回值，返回值为一个列表，列表中包括三个元素：'status'，'headers'和 'body'；

status：这是HTTP状态值，必须大于或等于100；

headers：这是一个命名列表，其中包含仅与有效HTTP标头对应的字符串值；

body：这是一个字符或原始矢量，如果字符向量以值'file'命名，则向量的值将被解释为文件的位置。

作为本发明的进一步限定，步骤5）中Rook服务器与Excel、R的连接采用HTTP协议，将Rook服务器设置好后，在Rook服务器中，设置捕获Rook服务器上传来的命令以及数据的方法，并将该方法得到的结果传给body，在R中建立一个HTTP连接对象，在该对象上绑定地址以及端口，与VBA统一，再在该对象上调用getRefClass方法，得到R处理好的结果，最后将结果add在该对象上；在VBA中建立一个HTTP连接对象，在该对象上绑定地址以及端口，与R统一，传递方式为POST，该对象调用send方法，将VBA得到的数据，以及命令发送到Rook服务器，最后等待并得到Rook服务器的反馈。

与现有技术相比，本发明的有益效果在于，将Excel的交互模式与R的统计计算功能结合起来，我们通过对R的函数进行设计，即重新编写统计学函数以及对R里面没有的函数扩充，通过利用R的计算能力，解决使用Excel及其内部统计功能和程序数值不准确的问题。

附图说明

图1为本发明中R与Excel通过Rook的交互方式。

图2为本发明中需要开发的函数。

图3为本发明中Excel中使用单个函数的界面。

图4为本发明中所有函数集成界面。

具体实施方式

下面结合具体实施例对本发明做进一步说明。

如图1所示一种使用统计学函数的Excel数据统计方法，包括以下步骤：

1）通过R的编译器RGui加载一个R的Web服务器接口Rook；

2）在R里面搭建起Rook服务器；

3）在R里面编写函数；

4）统一R以及Excel编译环境VBA里的URL以及端口；

5）通过Rook服务器采用HTTP协议建立Excel与R的连接；

6）利用设计好的R内的函数作为统计工具，将Excel传过来的数据加以计算，并且将结果返回Excel。

下面举例说明本发明技术方案，本发明需要实现的函数，如图2所示。

一、Rook服务器搭建

Rook服务器的搭建是一个R引用类对象的应用程序，它实现一个'call'方法或一个R闭包，它只接受一个参数，一个环境，并返回一个包含三个命名元素的列表：'status'，'headers'和 'body'；

程序 1：Rook服务器搭建示例

library(Rook)

setRefClass(

'RNSE',

methods = list(

call = function(env){

body = result

list(

status = 200L,

headers = list(

'Content-Type' = 'text/html'

),

body = body

)

}

)

)

library( )：加载Rook服务器包；

The SetRefClass：定义一个名字为RNSE的类；

The Methods：定义一个方法列表；

The Status：这是HTTP状态值，必须大于或等于100；

HTTP标头对应的字符串值；

The Body：这是一个字符或原始矢量，如果字符向量以值'file'命名，则向量的值将被解释为文件的位置。

二、R语言函数设计和连接设置

1. R语言函数设计，以unstack函数为例，函数用法my.unstack(InputDF)：

程序 2：my.unstack

my.unstack <- function(InputDF){

idx <- 1

browser()

group.flag <- InputDF[1,idx]

idx <- idx + 1

nvar <- InputDF[1,idx]

idx <- idx + 1

xs <- InputDF[,idx:(idx+nvar-1),drop=FALSE]

idx <- idx + nvar

xg <- InputDF[,idx,drop=FALSE]

g.name <- colnames(InputDF)[idx]

if(!group.flag){

xs <- cbind(as.data.frame(xs),as.data.frame(xg))

}

split(xs, xg) -> split.o

nlevel = length(split.o)

split.names <- names(split.o)

split.oo <- NULL

level.count <- vector("numeric",nlevel)

for(i in 1:nlevel){

level.count[i]<- nrow(split.o[[i]])

split.oo <- rbind(split.oo,as.data.frame(split.o[[i]]))

# 将不同level的dataframe合并

}

list(unstack=split.oo,nlevel=nlevel,

level.name=split.names,level.count = level.count)

}

函数的作用是根据分组因子xg将堆叠数据拆分为xs

InputDF

传递组件的dataframe；

group.flag

InputDF [1,1]; 一个逻辑标志，表明是否取消Grouping变量是要取消堆叠的变量之一；

nvar

InputDF [1,1]，要取消堆叠的变量数；

xs

InputDF[,2:(1+nvar)]，堆叠数据，可以是多列

xg

InputDF[,2+nvar]，分组变量，单列

usntack

未堆叠的数据，它本身就是每个未堆叠的列表

level

一个dataframe

nlevel

分组变量xg中的level

velit.name

分组变量级别的名称。

2. 建立和Excel的连接：

程序 3：建立与Excel的HTTP连接

Con<- Rhttpd$new()

Con$start(listen="127.0.0.1", port="8080")

Con$add(name="response",app=getRefClass('RNSE')$new())

实例化一个HTTP连接对象；

启动并监听URL及其端口；

通过getRefClass调用setRefClass定义类RNSE并返回生成器对象。

三、Excel的界面设计和连接设置

1. Excel方面需要完成的工作是，能够选择Sheet表中的数据，通过Excel自带的编程环境VBA里面的窗体应用，为每一种函数设计一个操作界面，如图3所示；最后将所有函数界面集成，设计整个XLR界面，如图4所示；

程序 4：选择并得到Excel表里面的数据

Private Sub RefEdit1_BeforeDragOver(Cancel As Boolean, ByVal Data As MSForms.DataObject, ByVal x As stdole.OLE_XPOS_CONTAINER, ByVal y As stdole.OLE_YPOS_CONTAINER, ByVal DragState As MSForms.fmDragState, Effect As MSForms.fmDropEffect, ByVal Shift As Integer)

End Sub

Set GVALUE=range(RefEdit1.Value)

使用一个RefEdit控件，并且定义其方法，即可选择数据；

将选中的数据对象赋给一个变量；

2. 函数界面集成，是通过使用Ribbon自制选项卡，具体方式是，将在VBA编辑的WorkSpace保存为xlam或者xlsm文件，可以在Excel里面或者外部程序为该文件添加XML文件；

程序 5：Ribbon XML设计示例

<ribbon>//使用ribbon标签

<tabs>

<tab id="TestTab" label="XLR">//使用tab标签

<group id="g1" label="TestGroup1">//定义组

<button id="BinomialDistribution" label="BinomialDistribution" onAction="AA" imageMso="Piggy"/>//定义按钮，调用方法，设计图标

<button id="ChiSquarTestGoodness" label="ChiSquarTestGoodness" onAction="AA" imageMso="Piggy"/>

<separator id="S1" />

<button id="FriedmanRankSumTest" label="FriedmanRankSumTest" onAction="AA" imageMso="Piggy"/>

<button id="KruskalWallis" label="KruskalWallis" onAction="AA" imageMso="Piggy"/>

<button id="WilcoxonRankTest" label="WilcoxonRankTest" onAction="AA" imageMso="Piggy"/>

</group>

</tab>

</tabs>

</ribbon>

Excel方面需要完成的工作是，能够选择Sheet表中的数据，通过Excel自带的编程环境VBA里面的窗体应用，为每一种函数设计一个操作界面，如图3所示；最后将所有函数界面集成，设计整个XLR界面；

3. 建立和R的连接：

程序 6：建立与R的HTTP连接

Set REC = CreateObject("WinHttp.WinHttpRequest")

REC.Open "POST", "http://127.0.0.1:8080/custom/response/", True

REC.Send GVALUE

REC.WaitForResponse

MsgBox REC.ResponseText

实例化一个WinHttp连接对象；

打开URL及其端口，连接方式为POST；

传送经过处理过的Excel的数据；

等待响应；

响应完成，输出返回的结果。

本发明并不局限于上述实施例，在本发明公开的技术方案的基础上，本领域的技术人员根据所公开的技术内容，不需要创造性的劳动就可以对其中的一些技术特征作出一些替换和变形，这些替换和变形均在本发明的保护范围内。