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

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

中圖分類號：TP311.52

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

1 研究背景

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

2 操作步驟

第一步：按照一定的格式填充輸入基礎(chǔ)數(shù)據(jù)。第二步：按照要求對數(shù)據(jù)進(jìn)行排序。第三步：在統(tǒng)計(jì)表中輸入函數(shù)計(jì)算公式，自動生成數(shù)據(jù)。第四步：把統(tǒng)計(jì)表里的生成的結(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ì)只能填寫“中央儲備糧”“最低收購價(jià)糧”“國家臨時(shí)存儲糧”“地方儲備糧”等，達(dá)標(biāo)與否只能填寫“達(dá)標(biāo)”“不達(dá)標(biāo)”，宜存與否只能填寫“宜存”“輕度不宜存”“重度不宜存”。地市的名稱也要與統(tǒng)計(jì)表中的地市名稱完全一致。填寫的時(shí)候注意不帶引號和空格，為避免填寫不規(guī)范，可以進(jìn)行篩選確認(rèn)。沒有數(shù)據(jù)的行不要填寫內(nèi)容。

2.2 第二步

選中整個(gè)Sheet 1工作表，按照“地市”“檢查庫點(diǎn)”“扦樣倉號”的順序進(jìn)行排序。見圖2。

2.3 第三步

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

在圖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á)標(biāo)"，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，"達(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)"）*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=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/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，"達(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/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，"達(dá)標(biāo)"，Sheet1！$L：$L，$B28）*100/E29）

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

SUM（E9：Q9）代表的意義是把E9、F9、G9、一直到Q9單元格的數(shù)字相加，就是把各地市的樣品個(gè)數(shù)相加產(chǎn)生全省的樣品總個(gè)數(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計(jì)算達(dá)標(biāo)數(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（稻谷）這兩個(gè)條件的對應(yīng)的F列（糧食數(shù)量）進(jìn)行加和，對應(yīng)產(chǎn)生中央儲備糧稻谷的總糧食數(shù)量。由此可見，SUMIFS函數(shù)適用于符合多個(gè)條件的對應(yīng)某列的加和。

COUNTIF（Sheet1！$L：$L，B13）代表的意義是在Sheet 1表格中L列（品種）為B13（稻谷）的樣品數(shù)量。由此可見，COUNTIF函數(shù)適用于符合單一條件的計(jì)數(shù)。

COUNTIFS（Sheet1！$I：$I，$A25，Sheet1！$L：$L，$B28）代表的意義是sheet 1表格中I列（儲存性質(zhì)）為A25（中央儲備糧），sheet1表格中L列（品種）為B28（稻谷）的數(shù)量。由此可見，COUNTIFS函數(shù)適用于符合多個(gè)條件的計(jì)數(shù)。

IF（D14=0，"/"，“F”）代表的意義是當(dāng)D14單元格等于0時(shí)，在該公式所在的單元格里填寫/，不為0時(shí)填寫F。F可以用其它的公式代替，這主要是為了當(dāng)D14（代表數(shù)量）為0時(shí)不能計(jì)算達(dá)標(biāo)率（0不能作除數(shù)）。

另外，在公式中經(jīng)?？梢钥吹健?”這個(gè)符號，例如$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列（檢查庫點(diǎn)）篩選出的檢查庫點(diǎn)的數(shù)量（庫點(diǎn)名稱相同的按照一個(gè)來計(jì)算）。需要注意的是這是一個(gè)數(shù)組公式，輸入完公式后，按Ctrl+Shift+Enter組合鍵鎖定數(shù)組公式，Excel將在公式兩邊自動加上花括號“{ }”。在兩端鍵入花括號是不正確的。在本示例里是按照Sheet 1最大9999行數(shù)據(jù)設(shè)定的。設(shè)定的數(shù)據(jù)行數(shù)越大公式的運(yùn)算時(shí)間就會越長。

函數(shù)公式輸入完成后，對于數(shù)據(jù)顯示不正常的進(jìn)行檢查，對公式進(jìn)行調(diào)整。

2.4 第四步

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

3 結(jié)論

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

參考文獻(xiàn)：

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

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

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

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

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

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

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