淺議Excel中數(shù)組公式的應(yīng)用

唐松

摘 要 公式和函數(shù)是Excel的重要組成部分，有非常強(qiáng)大的計(jì)算能力，為用戶分析和處理工作表中數(shù)據(jù)提供了方便。數(shù)組公式是指在數(shù)組的一項(xiàng)或多項(xiàng)上執(zhí)行計(jì)算的公式，它具有靈活、快捷、安全的優(yōu)點(diǎn)，執(zhí)行更復(fù)雜的計(jì)算。

關(guān)鍵詞 處理數(shù)據(jù) 數(shù)組公式 復(fù)雜計(jì)算

1數(shù)組數(shù)據(jù)基礎(chǔ)

數(shù)組是按行、列排列的一組數(shù)據(jù)元素，在Excel中位于一行、一列中的數(shù)組成為一維數(shù)組，多行多列的稱為二維數(shù)組。

在Excel中數(shù)組常量是指包含在單元格中的數(shù)值數(shù)組。我們在Excel中錄入數(shù)組數(shù)據(jù)，應(yīng)選中數(shù)組所在的區(qū)域，以逗號(hào)來分隔各列數(shù)據(jù)，用分號(hào)來分隔各行數(shù)據(jù)，輸入完成后用組合鍵來確認(rèn)數(shù)組數(shù)據(jù)，系統(tǒng)自動(dòng)用“{}”將數(shù)組數(shù)據(jù)括起來。

數(shù)組數(shù)據(jù)將作為一個(gè)整體被存儲(chǔ)和引用，如圖1所示。

不可直接修改數(shù)組常量中某一個(gè)單元格的值，系統(tǒng)會(huì)報(bào)錯(cuò)?？蛇x中單元格，在編輯欄中找到需修改的數(shù)組中數(shù)據(jù)的位置，修改它的值，再用組合鍵來確認(rèn)，可更改數(shù)組中某個(gè)元素的值。

數(shù)組常量可以包含數(shù)字、文本、邏輯值和錯(cuò)誤值（如#N/A），可用于Excel提供的所有內(nèi)置函數(shù)，但是數(shù)組常量不能包含其它數(shù)組、公式或函數(shù)。只能包含以逗號(hào)或分號(hào)分隔的文本或數(shù)字。

2數(shù)組公式

數(shù)組公式與標(biāo)準(zhǔn)公式語法相同，以等號(hào)開頭，使用大部分內(nèi)置函數(shù)和運(yùn)算符進(jìn)行計(jì)算。區(qū)別在于確認(rèn)公式時(shí)，數(shù)組公式需要用組合鍵輸入，系統(tǒng)將用“{}”將數(shù)組公式括起來。用戶不能手動(dòng)輸入括號(hào)，否則系統(tǒng)會(huì)認(rèn)為是字符串，不能識(shí)別為公式，將不能返回計(jì)算結(jié)果。

2.1多單元格數(shù)組公式

如果函數(shù)或公式返回多個(gè)結(jié)果值并需要保存在不同的單元格中，可以利用多單元格數(shù)組公式。如圖2，計(jì)算每種產(chǎn)品的銷售額，可以在第一個(gè)單元格輸入公式“=B2*C2”，然后用公式填充來完成計(jì)算。

也可以用數(shù)組公式來計(jì)算。選中結(jié)果區(qū)域D2：D6，輸入公式“=B2：B6*C2：C6”，按下組合鍵以數(shù)組形式輸入公式，也可以得到正確結(jié)果。

2.2單個(gè)單元格數(shù)組公式

輸入公式或函數(shù)對多個(gè)單元格數(shù)據(jù)分別進(jìn)行計(jì)算機(jī)，計(jì)算結(jié)果保存在一個(gè)單元格中，可利用單個(gè)單元格數(shù)組公式計(jì)算機(jī)完成。

如圖2，對每種產(chǎn)品計(jì)算銷售額然后求和，在總計(jì)單元格D8中輸入公式“=SUM（C2：C6*D2：D6）”， 按下組合鍵輸入數(shù)組公式，即得到正確結(jié)果。若沒有按下組合鍵，則以普通公式計(jì)算，則系統(tǒng)報(bào)錯(cuò)，如圖2單元格D7。兩種不同的輸入公式方式，系統(tǒng)處理結(jié)果截然不同。

利用此方法可以省去很多中間公式和計(jì)算，極大地簡化計(jì)算，體現(xiàn)了數(shù)組公式的靈活性。

2.3數(shù)組公式的編輯

與數(shù)組常量類似，對數(shù)組公式的編輯都需要按下組合鍵，以確認(rèn)公式為數(shù)組公式，此外還應(yīng)該遵守相關(guān)的處理規(guī)則。

（1）對多單元格數(shù)組公式，在輸入公式之前應(yīng)選擇用于保存結(jié)果的單元格區(qū)域；

（2）不能更改數(shù)組公式中單個(gè)單元格的內(nèi)容，需要選中其中一個(gè)單元格，在編輯欄修改后按下組合輸入公式；

（3）不能向多單元格數(shù)組公式中插入空白單元格或者刪除單元格，也無法移動(dòng)部分內(nèi)容，但可以整體移動(dòng)或刪除數(shù)組公式；

3數(shù)組公式的應(yīng)用

如圖3，根據(jù)學(xué)生成績和分?jǐn)?shù)分布區(qū)間，確定成績評級(jí)。

MATCH函數(shù)的作用是根據(jù)成績在分?jǐn)?shù)段中去查詢，找到成績所在分?jǐn)?shù)段中匹配的位置，如果能找到相應(yīng)的值返回所在行，如果不能找到，返回區(qū)間中比分?jǐn)?shù)大的最小值。CHOOSE函數(shù)的作用是利用得到的分?jǐn)?shù)在分布區(qū)間中的位置，取所對應(yīng)的“A，B，C，D”的等級(jí)。在結(jié)果區(qū)域中利用數(shù)組公式批量處理得到結(jié)果。

4結(jié)語

數(shù)組公式的應(yīng)用非常方便，計(jì)算靈活、高效，在實(shí)際案例中應(yīng)多思考計(jì)算原理和方法，嘗試不同的計(jì)算處理方法，掌握數(shù)組公式的使用，為復(fù)雜數(shù)據(jù)處理提供便利。

參考文獻(xiàn)

[1] 傅靖，李東.Excel2007公式、函數(shù)與圖表寶典[M].電子工業(yè)出版社，2007.

[2] 鄧多輝. Excel2013使用詳解[M].人民郵電出版社，2014.