Excel函数在粮食库存检查数据统计中的应用

Excel函数在粮食库存检查数据统计中的应用

摘要：Excel是Office办公自动化组件之一，有着强大的数据分析和数据处理能力，本

文应用Excel的函数功能对粮食库存检查数据统计提出/自动填充方案，列出J'具体的操作

步骤。通过Excel函数在粮食库存检查数据统计中的应用，提高了工作效率，达到了小半功

倍的效果。

关键词：Excel；粮食;数据;统计

1研究背景

在历年的粮食库存检查工作中，检验完成后，数据统计一般是通过Excel的筛选功能加

手工计算的方式来完成的，但常常因为一个数据的变化，需要重复进行统计计算，费工费时

口-2］。对此，本文介绍一种利用Excel函数的解决方案。

2操作步骤

第一步：按照一定的格式填充输入基础数据。第二步：按照要求对数据进行排序。第三

步：在统计表中输入函数计算公式，自动生成数据。第四步：把统计表里的生成的结果复制

粘贴为数值格式。

2.1第一步

在此环节要先在另外的EXCEL表中按照格式进行调整，调整好后按照标准格式一次性复

制到Sheet1（见图1）中。注意标题行在第1行，数据从第2行第1列开始。注意各列数

据的规范填写，比如品种只能填写“小麦”“玉米”“稻谷”“大豆”，储粮性质只能填写“中

央储备粮”“最低收购价粮”“国家临时存储粮”“地方储备粮”等，达标与否只能填写“运

标”“不达标”，宜存与否只能填写“宜存”“轻度不宜存”“重度不宜存”。地市的名称也要

与统计表中的地市名称完全一致。填写的时候注意不带引号和空格，为避免填写不规范，可

以进行筛选确认。没有数据的行不要填写内容。

2.2第二步

选中整个Sheet1工作表，按照“地市”“检查库点”“打样仓号”的顺序进行排序。见

图2。

2.3第三步

这是最重要的一步，也是本文重点探讨的对象。下面将重点加以阐述。以''常规质量分

性质分品种统计表”为例进行说明，表格格式见图3［3］。

在图3的表格对应位置输入下列函数公式即可［4-5］,

D9=SUM（E9：Q9）,在D9单元格里输入引号内内容，"=SUM（E9：Q9）"，下同。

D10=SUM（E10：Q10）

D11=SUM(Ell：Qll)

D12=SUMIF(Sheetl!M：M,"达标",Sheetl!F：F)*1OO/D11

D13=C0UNTIF(Sheetl!$L：$L,B13)

D14=SUMIF(Sheetl!$L：$L,B13,Sheetl!F：F)

D15=IF(D14=0,SUMIFS(Sheetl!F：F,Sheetl!$L：$L,B13,Sheetl!M：M,

"达标")*100/D14)

D25=COUNTIF(Sheetl!$1：$1,$A25)

D26=SUMIF(Sheetl!$1：$1,$A25,Sheetl!$F：$F)

D27=IF(D26=0,SUMIFS(Sheetl!$F：$F,Sheetl!$1：$1,$A25,Sheetl!$M：

$M,"达标”)*100/D26)

D28=COUNTIFS(Sheetl!$1：$1,$A25,Sheetl!$L：$L,$B28)

D29=SUMIFS(Sheetl!$F：$F,Sheetl!$1：$1,$A25,Sheetl!$L：$L,$B28)

D30=IF(D29=0,"/"»SUMIFS(Sheetl!$F：$F,Sheetl!$1：$1,$A25,Sheetl!$L：

$L,$B28,Sheetl!$M：$M,“if标”)*100/D29)

E9=SUM(N(MATCH(IF(Sheetl!$0$2：$O$9999=E$8,Sheetl!$C$2：$C$9999),

IF(Sheetl!$0$2：$O$9999=E$8,Sheetl!$C$2：$C$9999),)=ROW($1：$9998)))-1"

此为数组公式

E10=COUNTIF(Sheetl!$0：$0,E$8)

E11=SUMIF(Sheetl!$0：$0,E8,Sheetl!$F：$F)

E12=SUMIFS(Sheetl!$F：$F,Sheetl!$0：$0,E$8,Sheetl!$M：$M,"达标”)*100/Ell

E13=C0UNTIFS(Sheetl!$0：$0,E$8,Sheetl!$L：$L,$B13)

E14=SUMIFS(Sheetl!$F：$F,Sheetl!$L：$L,$B12,Sheetl!$0：$0,E$8)

E15=IF(E14=0,"/"»SUMIFS(Sheetl!$F：$F,Sheetl!$L：$L,$B13,Sheetl!$0：

$0,E$8,Sheetl!$M：•'达标")*100/E14)

E25=COUNTIFS(Sheetl!$1：$1,$A25,Sheetl!$0：$0,E$8)

E26=SUMIFS(Sheetl!$F：$F,Sheetl!$1：$1,$A25,Sheetl!$0：$0,E$8)

E27=SUMIFS(Sheetl：$F：$F,Sheetl!$1：$1,$A25,Sheetl!$0：$0,E$8,Sheetl!

$M：$M,”达标")*100/E26

E28=COUNTIFS(Sheetl!$1：$1,$A25,Sheetl!$0：$0,E$8,Sheetl!$L：$L,$B28)

E29=SUMIFS(Sheetl!$F:$F,Sheetl!$1:$1,$A25,Sheetl!$0:$0,E$8,

Sheetl!$L:$L,$B28)

E30=IF(E29=0fSUMIFS(Sheetl!$F:$F,Sheetl!$I:$I,$A25,Sheetl!

$0:$0,E$8,Sheetl!$M:$M,"达标"，Sheetl!$L:$L,$B28）*100/E29）

下面对上面用到的函数公式进行分类解释[6-7]:

SUM（E9:Q9）代表的意义是把E9、F9、G9、一直到Q9单元格的数字相加，就是

把各地市的样品个数相加产生全省的样品总个数，E9就是第E歹I」，第9行对应的数据。

SUMIF（Sheetl!M:M「达标"，Sheetl!F:F）代表的意义是把Sheet1表中M

列为达标的挑选出来把对应的F列进行加和，就是把达标的样品对应的代表数量进行加和，

产生达标粮食的总数量,*100/Dll代表的是达标的粮食数量除以总数量乘以100计算达

标数量百分率。由此可见，SUMIF函数适用于符合单一条件的加和。

D29=SUMIFS（Sheetl!$F:$F,Sheetl!$1:$1,$A25,Sheetl!$L:$L,$B28）

代表的意义是把符合sheet1表格中I列（储存性质）为A25（中央储备粮）和Sheet1

表格中L列（品种）为B28（稻谷）这两个条件的对应的F列（粮食数量）进行加和，对

应产生中央储备粮稻谷的总粮食数量。由此可见，SUMIFS函数适用于符合多个条件的对应

某列的加和。

COUNTIF（Sheetl!$L:$L,B13）代表的意义是在Sheet1表格中L列（品种）为

B13（稻谷）的样品数量。由此可见,COUNTIF函数适用于符合单一条件的计数。

COUNTIFS（Sheetl!$1:$1,$A25,Sheetl!$L:$L,$B28）代表的意义是sheet

1表格中I列（储存性质）为A25（中央储备粮），sheetl表格中L列（品种）为B28（稻

谷）的数量。由此可见，COUNTIFS函数适用于符合多个条件的计数。

IF（D14=0,7","F"）代表的意义是当D14单元格等于0时，在该公式所在的单元

格里填写/,不为0时填写F。F可以用其它的公式代替，这主要是为了当D14（代表数量）

为0时不能计算达标率（0不能作除数\

另外,在公式中经常可以看到夕’这个符号,例妇$A$1、$ALA$1,加第”符号

的原因是为了公式输入的笥便,同类公式可以在单元格内才总动，$A$1在拖动过程中列号和

行号均不发生变化，$A1在拖动过程中列号不发生变化，A$1在拖动过程中行号不发生变

化。

SUM(N(MATCH(IF(Sheetl!$0$2:$O$9999=E$8,Sheetl!$C$2:$C$9999),

IF(Sheetl!$0$2:$O$9999=E$8,Sheetl!$C$2:$C$9999),)=ROW($1:$9998)))

-1，代表的意义是在Sheet1表