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

1、Excel函数在粮食库存检查数据统计中的应用摘要：Excel是Office办公自动化组件之一，有着强大的数据分析和数据处理能力。本文应用Excel的函数功能对粮食库存检查数据统计提出了自动填充方案，列出了具体的操作步骤。通过Excel函数在粮食库存检查数据统计中的应用，提高了工作效率，达到了事半功倍的效果。关键词：Excel;粮食;数据;统计1 研究背景在历年的粮食库存检查工作中，检验完成后，数据统计一般是通过Excel的筛选功能加手工计算的方式来完成的，但常常因为一个数据的变化，需要重复进行统计计算，费工费时1-2。对此，本文介绍一种利用Excel函数的解决方案。2 操作步骤第一步：按照一定

2、的格式填充输入基础数据。第二步：按照要求对数据进行排序。第三步：在统计表中输入函数计算公式，自动生成数据。第四步：把统计表里的生成的结果复制粘贴为数值格式。2.1 第一步在此环节要先在另外的EXCEL表中按照格式进行调整，调整好后按照标准格式一次性复制到Sheet 1（见图1）中。注意标题行在第1行，数据从第2行第1列开始。注意各列数据的规范填写，比如品种只能填写“小麦”“玉米”“稻谷”“大豆”，储粮性质只能填写“中央储备粮”“最低收购价粮”“国家临时存储粮”“地方储备粮”等，达标与否只能填写“达标”“不达标”，宜存与否只能填写“宜存”“轻度不宜存”“重度不宜存”。地市的名称也要与统计表中的地

3、市名称完全一致。填写的时候注意不带引号和空格，为避免填写不规范，可以进行筛选确认。没有数据的行不要填写内容。2.2 第二步选中整个Sheet 1工作表，按照“地市”“检查库点”“扦样仓号”的顺序进行排序。见图2。2.3 第三步这是最重要的一步，也是本文重点探讨的对象。下面将重点加以阐述。以“常规质量分性质分品种统计表”为例进行说明，表格格式见图33。在图3的表格对应位置输入下列函数公式即可4-5。D9=SUM（E9：Q9），在D9单元格里输入引号内内容，“=SUM（E9：Q9）”，下同。D10=SUM（E10：Q10）D11=SUM（E11：Q11）D12=SUMIF（Sheet1！M：M，达

4、标，Sheet1！F：F）*100/D11D13=COUNTIF（Sheet1！$L：$L，B13）D14=SUMIF（Sheet1！$L：$L，B13，Sheet1！F：F）D15=IF（D14=0，/，SUMIFS（Sheet1！F：F，Sheet1！$L：$L，B13，Sheet1！M：M，达标）*100/D14）D25=COUNTIF（Sheet1！$I：$I，$A25）D26=SUMIF（Sheet1！$I：$I，$A25，Sheet1！$F：$F）D27=IF（D26=0，/，SUMIFS（Sheet1！$F：$F，Sheet1！$I：$I，$A25，Sheet1！$M：$M，达标

5、）*100/D26）D28=COUNTIFS（Sheet1！$I：$I，$A25，Sheet1！$L：$L，$B28）D29=SUMIFS（Sheet1！$F：$F，Sheet1！$I：$I，$A25，Sheet1！$L：$L，$B28）D30=IF（D29=0，/，SUMIFS（Sheet1！$F：$F，Sheet1！$I：$I，$A25，Sheet1！$L：$L，$B28，Sheet1！$M：$M，達标）*100/D29）E9=SUM（N（MATCH（IF（Sheet1！$O$2：$O$9999=E$8，Sheet1！$C$2：$C$9999），IF（Sheet1！$O$2：$O$9999

6、=E$8，Sheet1！$C$2：$C$9999），）=ROW（$1：$9998）-1 此为数组公式E10=COUNTIF（Sheet1！$O：$O，E$8）E11=SUMIF（Sheet1！$O：$O，E8，Sheet1！$F：$F）E12=SUMIFS（Sheet1！$F：$F，Sheet1！$O：$O，E$8，Sheet1！$M：$M，达标）*100/E11E13=COUNTIFS（Sheet1！$O：$O，E$8，Sheet1！$L：$L，$B13）E14=SUMIFS（Sheet1！$F：$F，Sheet1！$L：$L，$B12，Sheet1！$O：$O，E$8）E15=IF（E14

7、=0，/，SUMIFS（Sheet1！$F：$F，Sheet1！$L：$L，$B13，Sheet1！$O：$O，E$8，Sheet1！$M：$M，达标）*100/E14）E25=COUNTIFS（Sheet1！$I：$I，$A25，Sheet1！$O：$O，E$8）E26=SUMIFS（Sheet1！$F：$F，Sheet1！$I：$I，$A25，Sheet1！$O：$O，E$8）E27=SUMIFS（Sheet1！$F：$F，Sheet1！$I：$I，$A25，Sheet1！$O：$O，E$8，Sheet1！$M：$M，达标）*100/E26E28=COUNTIFS（Sheet1！$I：$I

8、，$A25，Sheet1！$O：$O，E$8，Sheet1！$L：$L，$B28）E29=SUMIFS（Sheet1！$F：$F，Sheet1！$I：$I，$A25，Sheet1！$O：$O，E$8，Sheet1！$L：$L，$B28）E30=IF（E29=0，/，SUMIFS（Sheet1！$F：$F，Sheet1！$I：$I，$A25，Sheet1！$O：$O，E$8，Sheet1！$M：$M，达标，Sheet1！$L：$L，$B28）*100/E29）下面对上面用到的函数公式进行分类解释6-7：SUM（E9：Q9）代表的意义是把E9、F9、G9、一直到Q9单元格的数字相加，就是把各地市的

9、样品个数相加产生全省的样品总个数，E9就是第E列，第9行对应的数据。SUMIF（Sheet1！M：M，达标，Sheet1！F：F）代表的意义是把Sheet 1表中M列为达标的挑选出来把对应的F列进行加和，就是把达标的样品对应的代表数量进行加和，产生达标粮食的总数量，*100/D11代表的是达标的粮食数量除以总数量乘以100计算达标数量百分率。由此可见，SUMIF函数适用于符合单一条件的加和。D29=SUMIFS（Sheet1！$F：$F，Sheet1！$I：$I，$A25，Sheet1！$L：$L，$B28）代表的意义是把符合sheet 1表格中I列（储存性质）为A25（中央储备粮）和Shee

10、t 1表格中L列（品种）为B28（稻谷）这两个条件的对应的F列（粮食数量）进行加和，对应产生中央储备粮稻谷的总粮食数量。由此可见，SUMIFS函数适用于符合多个条件的对应某列的加和。COUNTIF（Sheet1！$L：$L，B13）代表的意义是在Sheet 1表格中L列（品种）为B13（稻谷）的样品数量。由此可见，COUNTIF函数适用于符合单一条件的计数。COUNTIFS（Sheet1！$I：$I，$A25，Sheet1！$L：$L，$B28）代表的意义是sheet 1表格中I列（储存性质）为A25（中央储备粮），sheet1表格中L列（品种）为B28（稻谷）的数量。由此可见，COUNTIF

11、S函数适用于符合多个条件的计数。IF（D14=0，/，“F”）代表的意义是当D14单元格等于0时，在该公式所在的单元格里填写/，不为0时填写F。F可以用其它的公式代替，这主要是为了当D14（代表数量）为0时不能计算达标率（0不能作除数）。另外，在公式中经常可以看到“$”这个符号，例如$A$1、$A1、A$1，加“$”符号的原因是为了公式输入的简便，同类公式可以在单元格内拖动，$A$1在拖动过程中列号和行号均不发生变化，$A1在拖动过程中列号不发生变化，A$1在拖动过程中行号不发生变化。SUM（N（MATCH（IF（Sheet1！$O$2：$O$9999=E$8，Sheet1！$C$2：$C$9

12、999），IF（Sheet1！$O$2：$O$9999=E$8，Sheet1！$C$2：$C$9999），）=ROW（$1：$9998）-1，代表的意义是在Sheet 1表格中O列（地市）为E8（A市）的C列（检查库点）筛选出的检查库点的数量（库点名称相同的按照一个来计算）。需要注意的是这是一个数组公式，输入完公式后，按Ctrl+Shift+Enter组合键锁定数组公式，Excel将在公式两边自动加上花括号“ ”。在两端键入花括号是不正确的。在本示例里是按照Sheet 1最大9999行数据设定的。设定的数据行数越大公式的运算时间就会越长。函数公式输入完成后，对于数据显示不正常的进行检查，对公式进行调整。2.4 第四步数据虽然生成了