Excel函數(shù)在糧食庫存檢查數(shù)據(jù)統(tǒng)計中的應(yīng)用

1、Excel函數(shù)在糧食庫存檢查數(shù)據(jù)統(tǒng)計中的應(yīng)用摘要：Excel是Office辦公自動化組件之一，有著強大的數(shù)據(jù)分析和數(shù)據(jù)處理能力。本文應(yīng)用Excel的函數(shù)功能對糧食庫存檢查數(shù)據(jù)統(tǒng)計提出了自動填充方案，列出了具體的操作步驟。通過Excel函數(shù)在糧食庫存檢查數(shù)據(jù)統(tǒng)計中的應(yīng)用，提高了工作效率，達(dá)到了事半功倍的效果。關(guān)鍵詞：Excel;糧食;數(shù)據(jù);統(tǒng)計1 研究背景在歷年的糧食庫存檢查工作中，檢驗完成后，數(shù)據(jù)統(tǒng)計一般是通過Excel的篩選功能加手工計算的方式來完成的，但常常因為一個數(shù)據(jù)的變化，需要重復(fù)進(jìn)行統(tǒng)計計算，費工費時1-2。對此，本文介紹一種利用Excel函數(shù)的解決方案。2 操作步驟第一步：按照一定

2、的格式填充輸入基礎(chǔ)數(shù)據(jù)。第二步：按照要求對數(shù)據(jù)進(jìn)行排序。第三步：在統(tǒng)計表中輸入函數(shù)計算公式，自動生成數(shù)據(jù)。第四步：把統(tǒng)計表里的生成的結(jié)果復(fù)制粘貼為數(shù)值格式。2.1 第一步在此環(huán)節(jié)要先在另外的EXCEL表中按照格式進(jìn)行調(diào)整，調(diào)整好后按照標(biāo)準(zhǔn)格式一次性復(fù)制到Sheet 1（見圖1）中。注意標(biāo)題行在第1行，數(shù)據(jù)從第2行第1列開始。注意各列數(shù)據(jù)的規(guī)范填寫，比如品種只能填寫“小麥”“玉米”“稻谷”“大豆”，儲糧性質(zhì)只能填寫“中央儲備糧”“最低收購價糧”“國家臨時存儲糧”“地方儲備糧”等，達(dá)標(biāo)與否只能填寫“達(dá)標(biāo)”“不達(dá)標(biāo)”，宜存與否只能填寫“宜存”“輕度不宜存”“重度不宜存”。地市的名稱也要與統(tǒng)計表中的地

3、市名稱完全一致。填寫的時候注意不帶引號和空格，為避免填寫不規(guī)范，可以進(jìn)行篩選確認(rèn)。沒有數(shù)據(jù)的行不要填寫內(nèi)容。2.2 第二步選中整個Sheet 1工作表，按照“地市”“檢查庫點”“扦樣倉號”的順序進(jìn)行排序。見圖2。2.3 第三步這是最重要的一步，也是本文重點探討的對象。下面將重點加以闡述。以“常規(guī)質(zhì)量分性質(zhì)分品種統(tǒng)計表”為例進(jìn)行說明，表格格式見圖33。在圖3的表格對應(yīng)位置輸入下列函數(shù)公式即可4-5。D9=SUM（E9：Q9），在D9單元格里輸入引號內(nèi)內(nèi)容，“=SUM（E9：Q9）”，下同。D10=SUM（E10：Q10）D11=SUM（E11：Q11）D12=SUMIF（Sheet1！M：M，達(dá)

4、標(biāo)，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，達(dá)標(biāo)）*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，達(dá)標(biāo)

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，達(dá)標(biāo)）*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 此為數(shù)組公式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，達(dá)標(biāo)）*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，達(dá)標(biāo)）*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，達(dá)標(biāo)）*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，達(dá)標(biāo)，Sheet1！$L：$L，$B28）*100/E29）下面對上面用到的函數(shù)公式進(jìn)行分類解釋6-7：SUM（E9：Q9）代表的意義是把E9、F9、G9、一直到Q9單元格的數(shù)字相加，就是把各地市的

9、樣品個數(shù)相加產(chǎn)生全省的樣品總個數(shù)，E9就是第E列，第9行對應(yīng)的數(shù)據(jù)。SUMIF（Sheet1！M：M，達(dá)標(biāo)，Sheet1！F：F）代表的意義是把Sheet 1表中M列為達(dá)標(biāo)的挑選出來把對應(yīng)的F列進(jìn)行加和，就是把達(dá)標(biāo)的樣品對應(yīng)的代表數(shù)量進(jìn)行加和，產(chǎn)生達(dá)標(biāo)糧食的總數(shù)量，*100/D11代表的是達(dá)標(biāo)的糧食數(shù)量除以總數(shù)量乘以100計算達(dá)標(biāo)數(shù)量百分率。由此可見，SUMIF函數(shù)適用于符合單一條件的加和。D29=SUMIFS（Sheet1！$F：$F，Sheet1！$I：$I，$A25，Sheet1！$L：$L，$B28）代表的意義是把符合sheet 1表格中I列（儲存性質(zhì)）為A25（中央儲備糧）和Shee

10、t 1表格中L列（品種）為B28（稻谷）這兩個條件的對應(yīng)的F列（糧食數(shù)量）進(jìn)行加和，對應(yīng)產(chǎn)生中央儲備糧稻谷的總糧食數(shù)量。由此可見，SUMIFS函數(shù)適用于符合多個條件的對應(yīng)某列的加和。COUNTIF（Sheet1！$L：$L，B13）代表的意義是在Sheet 1表格中L列（品種）為B13（稻谷）的樣品數(shù)量。由此可見，COUNTIF函數(shù)適用于符合單一條件的計數(shù)。COUNTIFS（Sheet1！$I：$I，$A25，Sheet1！$L：$L，$B28）代表的意義是sheet 1表格中I列（儲存性質(zhì)）為A25（中央儲備糧），sheet1表格中L列（品種）為B28（稻谷）的數(shù)量。由此可見，COUNTIF

11、S函數(shù)適用于符合多個條件的計數(shù)。IF（D14=0，/，“F”）代表的意義是當(dāng)D14單元格等于0時，在該公式所在的單元格里填寫/，不為0時填寫F。F可以用其它的公式代替，這主要是為了當(dāng)D14（代表數(shù)量）為0時不能計算達(dá)標(biāo)率（0不能作除數(shù)）。另外，在公式中經(jīng)?？梢钥吹健?”這個符號，例如$A$1、$A1、A$1，加“$”符號的原因是為了公式輸入的簡便，同類公式可以在單元格內(nèi)拖動，$A$1在拖動過程中列號和行號均不發(fā)生變化，$A1在拖動過程中列號不發(fā)生變化，A$1在拖動過程中行號不發(fā)生變化。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列（檢查庫點）篩選出的檢查庫點的數(shù)量（庫點名稱相同的按照一個來計算）。需要注意的是這是一個數(shù)組公式，輸入完公式后，按Ctrl+Shift+Enter組合鍵鎖定數(shù)組公式，Excel將在公式兩邊自動加上花括號“ ”。在兩端鍵入花括號是不正確的。在本示例里是按照Sheet 1最大9999行數(shù)據(jù)設(shè)定的。設(shè)定的數(shù)據(jù)行數(shù)越大公式的運算時間就會越長。函數(shù)公式輸入完成后，對于數(shù)據(jù)顯示不正常的進(jìn)行檢查，對公式進(jìn)行調(diào)整。2.4 第四步數(shù)據(jù)雖然生成了