Excel共享 | 軟件資訊

分享Excel的高級應(yīng)用，數(shù)據(jù)處理和數(shù)據(jù)分析的方法介紹，配合詳細的案例分析講解（三）

SEP技術(shù)支持 | 2020/5/23 0:00:00

推薦使用：Excel數(shù)據(jù)采集軟件(免費下載，像Excel一樣極速上手，可靈活自定義的企業(yè)管理軟件)

Match格式

Match ( x， r，f )

其中x是要查找的數(shù)值，r可以是一個數(shù)組常量，或某列（或行）連續(xù)的單元格區(qū)域，其中可能包含有要查找的x。f用于指定match的查找方式，它可以是-1，0或1。

功能

Match(x，r，f)表示的意思是：在數(shù)組或連續(xù)的單元格區(qū)域r中查找x，并返回x在r中的位置編號。當f為0是，match進行精確查找，當f為1（或-1）時，match進行模糊查找。

說明

f=-1時，r必須按降序排列，查找大于或等于 x的最小數(shù)值

f=0時，r 不必排序，查找等于x的第一個數(shù)值

f=1時，r必須按升序排列，查找小于或等于x的最大數(shù)值

Index函數(shù)使用講解

格式

Index(Area，r，c，n)

其中，Area是1個或多個單元格區(qū)域；r是某行的行序號，c是某列的列序號，該函數(shù)返回指定的行與列交叉處的單元格引用。如果r等于0，則返回整行單元格引用，如果c等于0，則返回整列單元格引用。

當Area包括多個單元格區(qū)域時，n=1就表示結(jié)果來自于Area中的第1個區(qū)域，n=2表示結(jié)果來源于第2個單元格區(qū)域……。如果省略n表示結(jié)果來源于第1個單元格區(qū)域。

功能

Index（Area，r，c，n）的功能是返回Area中第n個單元格區(qū)域中的r行，c列交叉處的單元格引用。

案例講解

前面提到的案例2.1，利用Index和Match函數(shù)結(jié)合起來可以很快速的進行解決，如下圖2.2。

圖2.2 蔬菜單價查詢

三

D函數(shù)查詢數(shù)據(jù)的方法 ? ? ? ? ? ? ? ??

如果能把Excel里面某個區(qū)域里面的數(shù)據(jù)看成是數(shù)據(jù)庫中一張表，在Excel里面對數(shù)據(jù)進行數(shù)據(jù)庫里面SQL一樣的操作該多好。

在Excel中，數(shù)據(jù)庫是指每列數(shù)據(jù)都有標題的數(shù)據(jù)表。Excel提供大約12個專用數(shù)據(jù)庫函數(shù)來簡化這種數(shù)據(jù)表的數(shù)據(jù)統(tǒng)計和數(shù)據(jù)查找工作，這些函數(shù)都以D開頭，所以也稱為D函數(shù)。

D函數(shù)有相同的調(diào)用形式，相同參數(shù)表，格式如下：

Dname(database,field,criteria)

其中的Dname是函數(shù)名，它可以是Dsum、Daverage、Dget、Dcount、Dcounta、Dmax、Dmin等。各函數(shù)的功能如其名字所示，Dsum求總和，Daverage求平均數(shù)，Dget查找數(shù)據(jù)，Dcount統(tǒng)計數(shù)字個數(shù)，Dcounta統(tǒng)計文本和數(shù)據(jù)的個數(shù)，Dmax求最大數(shù)，Dmin求最小數(shù)。

database是一個單元格區(qū)域，要求該區(qū)域中的每列數(shù)據(jù)都必須有標題；field是database區(qū)域中某列數(shù)據(jù)的列標題（稱為字段，出現(xiàn)在字符串中）；criteria稱為條件區(qū)域，它與高級篩選條件區(qū)域的含義和構(gòu)造方法完全相同。

【案例3.1】某校某專業(yè)共有224名學(xué)生，某次期末考試的“數(shù)據(jù)庫系統(tǒng)應(yīng)用”課程的成績表如圖所示?，F(xiàn)在要查找每位學(xué)生的成績，希望輸入學(xué)號后，就能夠得到該生的各種詳細數(shù)據(jù)，如圖3.1的J1:M8區(qū)域所示。此外，還希望對各班的考試情況進行簡單的統(tǒng)計分析，能夠隨時查看各班的考試人數(shù)，最高成績，高低成績，及缺考人數(shù)等，如圖3.1的J10:N17區(qū)域所示。

圖3.1 班級成績分析

對于對各班的考試情況進行簡單的統(tǒng)計分析，如果是在數(shù)據(jù)庫里面，就是一段簡單的SQL代碼，如統(tǒng)計上機平均成績：

select ?avg(上機成績) ? ? ? ?

from database ? ? ? ? ? ? ? ??

where 班級=’0320302’； ??

在Excel里面，這個就可以借助D函數(shù)實現(xiàn)異曲同工之效。

案例3.1解決方案如下：

（1）在K13中輸入計算上機平均成績的公式：

=DAVERAGE(A4:H227,"上機成績",J12:J13)

（2）在M13輸入計算綜合平均成績的公式：

=DAVERAGE(A4:H227,"綜合成績",J12:J13)

（3）在K15輸入計算缺考人數(shù)的公式：

=DCOUNTA(A4:H227,"期末考試成績",J12:J13)-DCOUNT(A4:H227,"期末考試成績",J12:J13)

（4）在M15輸入計算最高成績的公式：

=DMAX(A4:H227,"期末考試成績",J12:J13)

（5）在K17輸入計算最低成績的公式：

=DMIN(A4:H227,"期末考試成績",J12:J13)

（6）在M17輸入計算考試人數(shù)的公式：