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

摘要：Excel是Office辦公自動化組件之一，有著強大的數(shù)據(jù)分析和數(shù)據(jù)處理能力。本文應用Excel的函數(shù)功能對糧食庫存檢查數(shù)據(jù)統(tǒng)計提出了自動填充方案，列出了具體的操作步驟。通過Excel函數(shù)在糧食庫存檢查數(shù)據(jù)統(tǒng)計中的應用，提高了工作效率，達到了事半功倍的效果。

關(guān)鍵詞：Excel;糧食;數(shù)據(jù);統(tǒng)計

中圖分類號：TP311.52

作者簡介：檀軍鋒，男，本科，高級工程師，研究方向為糧油質(zhì)量檢測和質(zhì)量管理。

1 研究背景

在歷年的糧食庫存檢查工作中，檢驗完成后，數(shù)據(jù)統(tǒng)計一般是通過Excel的篩選功能加手工計算的方式來完成的，但常常因為一個數(shù)據(jù)的變化，需要重復進行統(tǒng)計計算，費工費時[1-2]。對此，本文介紹一種利用Excel函數(shù)的解決方案。

2 操作步驟

第一步：按照一定的格式填充輸入基礎(chǔ)數(shù)據(jù)。第二步：按照要求對數(shù)據(jù)進行排序。第三步：在統(tǒng)計表中輸入函數(shù)計算公式，自動生成數(shù)據(jù)。第四步：把統(tǒng)計表里的生成的結(jié)果復制粘貼為數(shù)值格式。

2.1 第一步

在此環(huán)節(jié)要先在另外的EXCEL表中按照格式進行調(diào)整，調(diào)整好后按照標準格式一次性復制到Sheet 1（見圖1）中。注意標題行在第1行，數(shù)據(jù)從第2行第1列開始。注意各列數(shù)據(jù)的規(guī)范填寫，比如品種只能填寫“小麥”“玉米”“稻谷”“大豆”，儲糧性質(zhì)只能填寫“中央儲備糧”“最低收購價糧”“國家臨時存儲糧”“地方儲備糧”等，達標與否只能填寫“達標”“不達標”，宜存與否只能填寫“宜存”“輕度不宜存”“重度不宜存”。地市的名稱也要與統(tǒng)計表中的地市名稱完全一致。填寫的時候注意不帶引號和空格，為避免填寫不規(guī)范，可以進行篩選確認。沒有數(shù)據(jù)的行不要填寫內(nèi)容。

2.2 第二步

選中整個Sheet 1工作表，按照“地市”“檢查庫點”“扦樣倉號”的順序進行排序。見圖2。

2.3 第三步

這是最重要的一步，也是本文重點探討的對象。下面將重點加以闡述。以“常規(guī)質(zhì)量分性質(zhì)分品種統(tǒng)計表”為例進行說明，表格格式見圖3[3]。

在圖3的表格對應位置輸入下列函數(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，"達標"，Sheet1！F：F）*100/D11

D13=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，"達標"）*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=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，"達標"）*100/E11

E13=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=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/E26

E28=COUNTIFS（Sheet1！$I：$I，$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）

下面對上面用到的函數(shù)公式進行分類解釋[6-7]：

SUM（E9：Q9）代表的意義是把E9、F9、G9、一直到Q9單元格的數(shù)字相加，就是把各地市的樣品個數(shù)相加產(chǎn)生全省的樣品總個數(shù)，E9就是第E列，第9行對應的數(shù)據(jù)。

SUMIF（Sheet1！M：M，"達標"，Sheet1！F：F）代表的意義是把Sheet 1表中M列為達標的挑選出來把對應的F列進行加和，就是把達標的樣品對應的代表數(shù)量進行加和，產(chǎn)生達標糧食的總數(shù)量，*100/D11代表的是達標的糧食數(shù)量除以總數(shù)量乘以100計算達標數(shù)量百分率。由此可見，SUMIF函數(shù)適用于符合單一條件的加和。

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

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ù)量。由此可見，COUNTIFS函數(shù)適用于符合多個條件的計數(shù)。

IF（D14=0，"/"，“F”）代表的意義是當D14單元格等于0時，在該公式所在的單元格里填寫/，不為0時填寫F。F可以用其它的公式代替，這主要是為了當D14（代表數(shù)量）為0時不能計算達標率（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$9999），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ù)顯示不正常的進行檢查，對公式進行調(diào)整。

2.4 第四步

數(shù)據(jù)雖然生成了，但是數(shù)據(jù)仍然是公式的格式，接下來需要把Excel表格另存為一個EXCEL表格復制，然后對統(tǒng)計表進行復制再粘貼為數(shù)值格式，對于小數(shù)點位數(shù)不正確的進行設(shè)置，這樣數(shù)據(jù)統(tǒng)計填充工作就完成了。

3 結(jié)論

總之，Excel函數(shù)擁有極其強大的功能，在數(shù)據(jù)處理方面發(fā)揮著極其重要的作用，在糧食庫存檢查數(shù)據(jù)統(tǒng)計等實驗室質(zhì)量管理工作中應用Excel函數(shù)，能夠有效提高工作和學習效率，達到事半功倍的效果。

參考文獻：

[1]黃少玲.探析Excel函數(shù)在數(shù)據(jù)處理中的應用[J].電腦編程技巧與維護，2017（3）.58-60

[2]李如平，朱煉.EXCEL函數(shù)在數(shù)據(jù)處理與分析中的應用[J].電腦知識與技術(shù)，2015（5）.199-201.

[3]全國政策性糧食庫存數(shù)量和質(zhì)量大清查部際協(xié)調(diào)機制辦公室.2019年全國政策性糧食庫存大清查培訓教材[M].北京：中國海關(guān)出版社，2019

[4]Excel Home.別怕，Excel函數(shù)其實很簡單[M].北京：人民郵電出版社，2016.

[5]Excel精英部落.Excel函數(shù)與公式速查寶典[M].北京：中國水利水電出版社，2019.

[6]賽貝爾資訊.高效隨身查：Excel函數(shù)與公式應用技巧[M].北京：清華大學出版社，2016.

[7]《電腦愛好者》編輯部.做表不求人 Excel函數(shù)進階大全[M].北京：北京《電腦愛好者》雜志社，2018.