Excel數(shù)據(jù)分析教程 上機題及課后習題答案 第1-8章

Excel數(shù)據(jù)分析教程上機題及課后習題

上機題1

1、在Excel條件格式中運用公式進行篩選

公式是使用Excel處理和分析數(shù)據(jù)的重要工具，在Excel的條件格式中使

用公式能夠速準確地篩選到滿足條件的數(shù)據(jù)，從而更好地發(fā)揮Excel的條件格

式功能。在Excel的條件格式中使用公式的具體操作步驟如下。

①打開素材，選擇F2:F14單元格區(qū)域，單擊“開始”選項卡下“樣式”

選項組中的“條件格式”按鈕，在彈出的下拉菜單中選擇“新建規(guī)則”選項，

如圖IB-la所示：

文件開始插入頁面布局公式數(shù)據(jù)審閱視

等線▼11▼唱條件格式▼

1

粘1貼—日ERU-AA

突出顯不單

今▼A▼燮▼

剪貝顓后字體氐

最前/最后先

ABCDF

性別

部門數(shù)據(jù)條(D)

職工姓名職務(wù)工齡

陳

亦民

部門經(jīng)理10釜

源

楊

三

高級職員6500

衛(wèi)

劉

中12

色階⑸

正高級職員4300

任

義8

遠

羅

民高級職員94300

普通職員23600

部門經(jīng)理33600

張愛國音通職員64300圖標集(1)

但明玉普通職員65000

馬耀華高級職員75200□新建規(guī)則⑻)..

李光東普通職員85000

雷民部門經(jīng)理96500國清除規(guī)則。

孔婉晴普通職員12800

馬羽音通職員107000

Ira^arrcD+ni

IB-la“條件格式”的“新建規(guī)則”選項

②彈出“新建格式規(guī)則”對話框，在“選擇規(guī)則類型”列表框中選擇最后

一項“使用公式確定要設(shè)置格式的單元格”選項，在“編輯規(guī)則說明“選項區(qū)域

“為符合此公式的值設(shè)置格式”文本框中輸入公式

“=F2>LARGE($F$2:$F$14,4)”°輸入完成后單擊“格式”按鈕來設(shè)置相應(yīng)的格

式，如圖IB-lb所示：

新建格式規(guī)則？X

則類型⑤：

?基于各自值設(shè)置所有單元格的格式

?只為包含以下內(nèi)容的單元格設(shè)置格式

?僅神E名靠前或靠后的數(shù)值設(shè)置格式

?僅對高于或彳氐于平均值的數(shù)值設(shè)置格式

?僅對唯一值或重復(fù)值設(shè)置格式

A使用公式確定要設(shè)置格式的單元格

編輯規(guī)則說明(E)：

確定取消

圖IB-lb在“新建規(guī)則”中輸入公式

③單擊“確定”按鈕即可將設(shè)置好的格式應(yīng)用到銷售額排名前三的數(shù)據(jù)所

在的單元格中，效果如圖1B-1C所示。

ABCDEF

1職工姓名性別部門職務(wù)工齡工資

開發(fā)部

陳亦

男

民3

r部門經(jīng)理107800I

男

楊源

部

S財務(wù)3

,高級職員126400

3女

劉衛(wèi)

部

測試

中3

4,高級職員84300

任正

部

開發(fā)

義

男3

,高級職員94300

羅遠

5浩

部

測試

男S

,2

利嬲普通職員

6思3600

當

財務(wù)

女3

7,部門經(jīng)理33600

部

開發(fā)

男

張

國

愛3

,普通職員64300

8部

測試

女

信

明

玉3

,昔通職員65000

卻

開發(fā)

9男

馬

窿

華5

,高級職員7

部5200

開發(fā)

男

李

10光

東5

,普通職員

釬

春85000

酢

測試

11W男3

,9|

部部門經(jīng)理6500

開發(fā)

12男

,3

部

財務(wù)

孔婉晴女普通職員12800

13,

14馬羽，言通職員107000|

圖IB-lc使用了公式的條件格式效果

提示：公式中的“LARGE($F$2:$F$14,4)”表示在F2:F14單元格區(qū)域中的

數(shù)值按從大到小的順序排列，排位第4的值。很顯然，這實際上是在進行類似

于“數(shù)字篩選”的篩選操作，對篩選出的結(jié)果應(yīng)用指定格式。所以條件格式本質(zhì)

上仍然是篩選。

同樣的，如果要選擇最小的三位，可改成“二F2VsMALL($F$2:$F$14,4)”。

另外，如果排名第三位的值有兩個，則總共只有兩個單元格應(yīng)用了指定格式,

而不是我們通常以為的會有四個單元格(即存在并列第三)應(yīng)用指定格式。

2、在篩選結(jié)果中重新排序

我們都曾遇到過這種情況：數(shù)據(jù)在篩選前序號(如排名)是連續(xù)的，但篩選

后就不連續(xù)了，如圖1B-2所示：

二

BJD，--

A二-

城

1年hM

20191I2019年云貴川三省城市GDP排名前十

IH市<

省

2城

排名區(qū)數(shù)市區(qū)GDP總量2

都

四

成都

川

成

33四*JI

云12

南121310213102

明3

宜賓

四

1昆

46*JI

陽1610.76

275068.9綿陽3

四

貴州

5貴7/1k

賓

四

瀘州

四

川

3宜63241.9831592.42

68*JI

四

431610.76自貢41150.02

陽

川編

7四?1U

531592.4212880.06

瀘

州

四

8川

631150.02

道

貴

9州

看

這

里

曲

7云31122.99

w南

云

玉

n8南21105.2

92974.9

1210四川4880.06

圖1B-2篩選結(jié)果會打亂原表序號

如果想要在篩選后結(jié)果依然保持順序，應(yīng)按如下方式操作:

首先，將原表的排名清除，然后選擇“插入I表格I表格”將原來的數(shù)據(jù)清

單轉(zhuǎn)化為Excel表，如圖IB-3a所示：

ABCDF

1排名■省□城市■區(qū)數(shù)□市區(qū)GDP總量口

2四川成都1213102

3云南昆明75068.9

4貴州貴陽63241.98

5四川宜其創(chuàng)建表X

6四川綿隹

7四川瀘升據(jù)的來源(W)：

貴州遵義

8=$A$1:$E$11±

9云南曲扉

10云南玉浮叼表包含標會(M)

11四川自貢

12取消

13

圖IB-3a將原表數(shù)據(jù)區(qū)域轉(zhuǎn)化為Excel表

接著在A2單元格輸入公式二511811仃人口103Q$2:82),該公式使用了匯總函

數(shù)SUBTOTAL,其第一個參數(shù)表示counta統(tǒng)計，即統(tǒng)計第二個參數(shù)所表示的區(qū)

域中非空單元格個數(shù)，此時SUBTOTAL就在這些個數(shù)中自動給出排名，如圖1B-

3b所示:

▼

SUMxyA=SUBTOTAL(103/B$2:B6)

A1CDEF

1排名「省□r城市口區(qū)數(shù)。市區(qū)GDP總量□

21四川成都1213102

32云南昆明75068.9

43貴州貴陽6324198

54四川宜賓31610.76

哂

綿

川

61陽3

■

5|一■1592.42

瀘

州

6川

7四3

道

義

州1150.02

87貴3

1122.99

98云南曲靖21105.2

109云南玉溪2974.9

1110四川自貢4880.00

圖lB-3b使用公式排名

此時進行篩選，結(jié)果就會自動排名了，如圖IB-3c所示:

A|BCDE

排名EI省S3城市口區(qū)數(shù)。市區(qū)GDP總量口|

1四川成都1213102

2匹川宜賓31610.76

63四川綿陽31592.42

1—、匹川瀘州31150.02

11郁川自貢4880.0包

12'看這里

圖IB-3c篩選后保持排名

3、讓表中序號不參與排序

在對數(shù)據(jù)進行排序的過程中，在某些情況下并不需要對序號進行排序，例如

已有按“語文”進行了排序的清單如圖IB-4a所示：

ABCDE

1學生成績表

2序號姓名語文數(shù)學英語

31柳建927486

42劉衛(wèi)中908988

53張愛國887267

64雷民819272

75楊源喜779890

86侗明玉747273

97馬耀華677094

108任正義656579

119羅遠浩6268」5

圖lB-4a已按“語文”進行了排序

現(xiàn)在要改成按“數(shù)學”排序，如果選中整個表，勢必會使“序號”發(fā)生改變，

因此只需選中B2:E11單元格區(qū)域，將“主要關(guān)鍵字”設(shè)置為“數(shù)學”后進行降

序排列即可，如圖1B?4b所示：

ABCDE

1學生成績表

2序號姓名語文數(shù)學英語

31楊源喜779890

42雷民819272

53劉衛(wèi)中908988

64柳愿927486

75張愛國887267

86侗明玉747273

97馬耀華677094

108羅遠浩626875

119任正義656579

圖IB-4b不變更序號重新進行了排序

更有甚者，如果連字段也沒有，例如下圖IB-4c所示表格:

ABC

1語文成績表

21楊源喜77

32雷民81

43劉衛(wèi)中90

54柳愿92

65張愛國88

76侗明玉74

87馬耀華67

98羅遠浩62

109任正義65

圖|B-4c沒有字段的數(shù)據(jù)清單

原來的數(shù)據(jù)是隨機的，現(xiàn)在需要按姓名發(fā)音排序，只需選擇B2:C10單元格

區(qū)域，在“排序”對話框中去掉“數(shù)據(jù)包含標題”前的對勾，然后將“主要關(guān)鍵

字”設(shè)置為“列B”后升序排序即可。

提示：在排序之前選中數(shù)據(jù)區(qū)域則只對數(shù)據(jù)區(qū)域內(nèi)的數(shù)據(jù)進行排序。

實際上，讓序號不參與排序，使用的原理就是前面講到的，只選擇部分字段

排序，排序結(jié)果只影響部分字段。本例中就是除了序號以外的“部分字段”參與

了排序，所以結(jié)果只影響了除了序號以外的其他字段。

課后習題1

1、在自定義篩選時，如果要篩選“川”開頭的字符串，可使用的方法有哪

些？如果要篩選包含“川”的字符串，又該怎么做？

2、操作：使用篩選匯總分析員工工資

本例將統(tǒng)計分析員工的工資，涉及以下內(nèi)容：

①按照“工資”降序排列

②按照“部門”升序排列，按照“工資”降序排列

③篩選出“高級職員”的相應(yīng)數(shù)據(jù)

④篩選出“部門”為“開發(fā)部”、工資低于5000的數(shù)據(jù)

⑤以“性別”為分類依據(jù)，統(tǒng)計男女的人數(shù)

⑥以“部門”為分類依據(jù)，統(tǒng)計各部門的工資總和

原始數(shù)據(jù)如圖所示：

ABCDEF

職工姓名性別部職務(wù)工齡工資

陳

民

亦

2部門經(jīng)理107800

楊

源1

3高級職員126500

衛(wèi)

劉

中高級職員84300

4義

正

任高級職員94300

5浩

遠

羅

6京普通職員23600

颼

莎

7國部門經(jīng)理33600

愛

張

玉普通職員64300

8明

侗

華普通職員65000

9耀

馬

東高級職員75200

10光

李

11一普通職員85000

.雷民

12部門經(jīng)理96500

胺

褫

普通職員12800

13n

14J普通職員107000

請將每個操作步驟的結(jié)果截圖保存。

操作如下：

①按列簡單排序：直接點擊“工資”列中任意一個單元格后，在“數(shù)據(jù)|

排序和篩選”組中單擊“降序”按鈕幺即可。

②多關(guān)鍵字排序：在“數(shù)據(jù)I排序和篩選”組中單擊“排序”按鈕靠，在

打開的“排序”對話框中設(shè)置主要關(guān)鍵字和次要關(guān)鍵字即可.

③自動篩選：在“數(shù)據(jù)排序和篩選”組中單擊“篩選”按鈕T,打開“職

務(wù)”標題的下拉箭頭后選擇“高級職員”即可。

④高級篩選:先退出自動篩選，然后數(shù)據(jù)表中建立如下圖所示的條件區(qū)域：

117,部門?T資?

I18I開發(fā)部I<=500。！

再在“數(shù)據(jù)|排序和篩選”組中單擊“高級篩選”按鈕&，在彈出的“高

級篩選”對話框中選擇篩選的數(shù)據(jù)區(qū)域和條件區(qū)域，完成高級篩選。

最后，再在“數(shù)據(jù)排序和篩選”組中單擊“清除”按鈕入，清除當前的

篩選。

⑤分類匯總：首先在“數(shù)據(jù)|排序和篩選”組中單擊“排序”按鈕0,在

打開的“排序”對話框中設(shè)置主要關(guān)鍵字為“性別”、次要關(guān)鍵字“部門”后進

行排序，本次分類匯總只需用到“性別”這個分類依據(jù)，“部門”這個評估依據(jù)

是為下面的操作預(yù)告準備的；_

排序完成后，再單擊“數(shù)據(jù)|分級顯示”組中的“分類匯總”按鈕昌國，在

彈出的“分類匯總”對話框中以“性別”為分類依據(jù)、以“計數(shù)”為匯總方式,

以“職工姓名”為匯總項進行匯總。

?嵌套分類匯總：用同樣方式再次進入“分類匯總”而話框，以“部門”

為分類依據(jù)、以“求和”為匯總方式，以“工資”為匯總項進行匯總，此時注意

要清除“替換當前分類匯總”復(fù)選框。

3、操作：利用模糊篩選從給定范圍中篩選出適當?shù)臄?shù)據(jù)

模糊篩選通常也可稱為通配符篩選，模糊篩選常用的數(shù)值類型有數(shù)值型、日

期型和文本型，通配符“？”和“*”只能配合“文本型”數(shù)據(jù)使用，如果數(shù)據(jù)

是日期型利數(shù)值型，則需要通過設(shè)置限定范圍（如大于、小于、等于等）來實現(xiàn)。

例如，篩選出姓“馬”且名字只有一個字的人名的項：

ABCD

啕

施請

職工號姓名性另

公室

辦

1女U

zgOOl楊源直

公室

2辦

里

雷民

zg002事部

人

3男

羅遠浩

zg003售部

俏

4女

馬羽

zg004售部

俏

5女

侗明玉

場部

zg005市

6男

期底息

場部

市

7zg006男

zg007孔婉晴

場部

市

8女

陳亦民

zg008事部

人

9女

李光東

務(wù)部

zg009財

10女

馬耀華

務(wù)部

財

11zgOlO男

任正義

售部

消

12zgOl1男

劉衛(wèi)中

售部

徜

zg012里

13張愛國

徜

售部

zg013男

14何洪濟

辦

公室

女

15zg014秦桂榮

開

發(fā)部

男

16zg015岳云山

開

發(fā)部

女

17zg016周紅兵

開

發(fā)部

18zg017女

開

發(fā)部

19zg018馬苗男

20zg019王利宏

具體操作步驟如下：

①選擇列表任意一個單元格，單擊“數(shù)據(jù)I排序和篩選I篩選”按鈕，在

標題行每列的右側(cè)出現(xiàn)一個下拉按鈕，然后單擊“姓名”列右側(cè)的下拉按鈕，在

彈出的下拉列表中選擇“文本篩選I等于"（或“文本篩選自定義篩選”）選

項，如圖所示：

ABCDEFG

1職工號

2zgOOl

-

3_zg002里辦公室

妻

zg003

4升序兇

5zg004N

6zg005

7zg006降序(Q)

孑

8zg007a

9zg008N按顏色排序①

r

10zg009E

-

11zgOlOf工作表視圖M

12zgOl1

文

13zg012K從“姓名”中清除篩選?

zg013n

14n

15zg014

者按顏色篩選(1)

16zg0155

17zg0166

18zg017文本篩選(E)等于㈤…

19zg018

20zg019媵不等于⑻)…

21

22陽全選)開頭是(!)...

x

②都將彈出“自定義自動篩選方式”對話框，在“等于”后面的文本框中

輸入“馬？”注意此處的問號是英文狀態(tài)下輸入的，單擊“確定”按鈕，即可篩

選出姓“劉”且名字只有一個字的人名，如圖所示：

CD

性別▼部門名稱，

19zg018

22自定義自動篩選方式

23

24顯示行:

25

26

27

28等于7

29

30

31

32

33

34

35

36可用？

37

用*代表任意多個字符

提示：通配符中“？”代表單個字符，可代表多個字符。如輸入“馬？”

表示姓馬，且名字只有一個字，輸入“馬”則表示姓劉，且名字至少是一個字。

此例中，如果只使用快捷文本篩選項“包含”“不包含”“開頭是”“結(jié)尾是”等

幾個選項是達不到目的的，必須使用自定義選項，以公式來篩選。

上機題2

1、應(yīng)用數(shù)據(jù)透視表進行多地區(qū)經(jīng)濟指標統(tǒng)計分析

以某市下轄各縣市區(qū)2018?2020年的各項經(jīng)濟指標為依據(jù)，創(chuàng)建數(shù)據(jù)透視表

并進行相應(yīng)的分析。原始數(shù)據(jù)如圖2B-1所示：

AB1CI?DI_..E...IF

1年代縣/rH/固定資產(chǎn)投奧國民生產(chǎn)總值國民生產(chǎn)總值指數(shù)人均GDP

2J2018江城區(qū)2.743880.53108.B67813.07

32019江城區(qū)7.644212.82109.227434.00

42020江城區(qū)8.234757.45109.719010.70

52018璇神區(qū)0.503831.90109.D36054.00

62019落神區(qū)0.294151.54108.996734.00

72020游神區(qū)1.284659.99109.607554.00

2018州區(qū)23.832120.35109.307640.01

8」Lj

92019州區(qū)19.672348.54109.508714.00

102020州區(qū)24.132662.08110.209338.00

112018河油市3.479456.84110.1512922.00

122019河油市4.4110606.85111.6614396.00

13|2020河油市6.3812442.87113.6216809.00

142018四臺縣0.132175.68108.785221.00

152019四臺縣3.082450.48110.545829.00

162020四臺縣2.652807.41112.966678.00

172018茶縣29.885033.08108.9812040.86

182019茶李縣35.775458.22110.2513000.00

2020茶亍縣37.926002.54