第8章函數(shù)公式綜合應(yīng)用

3）公式解析

兩個(gè)公式結(jié)構(gòu)相似，都是一個(gè)OFFSET函數(shù)，其

中第2參數(shù)“COUNTA（$B:$B）-T'定位到各列最

后一個(gè)非空單元格，第4參數(shù)使用-5表示向上引用5個(gè)

單元格。

完成定義名稱(chēng)之后又回歸到圖表問(wèn)題，進(jìn)入“編輯

數(shù)據(jù)系列”對(duì)話框，在“系列值”框中使用已定義的名

稱(chēng)DATA,如圖8.39所示。

在“系列值”框中使用名稱(chēng)時(shí)，前面要加上引用的

工作表名，Excel會(huì)自動(dòng)轉(zhuǎn)化成工作簿名，完成之后在

“水平（分類(lèi)）軸標(biāo)簽”框下單擊“編輯”命令，在彈

出的“軸標(biāo)簽”對(duì)話框中設(shè)置使用名稱(chēng)“日期”，如圖

8.40所示。

圖8.39在“編輯數(shù)據(jù)系列”對(duì)話框中使用名稱(chēng)

?I不/區(qū)吸

用適應(yīng)艇崛!日期■.20造X的12…

［雇：［:KW］

第8章函數(shù)公式綜合應(yīng)用

圖8.40在“軸標(biāo)簽”對(duì)話框中使用名稱(chēng)

只需要改動(dòng)這兩處，就能將一個(gè)普通的圖表變成可

自動(dòng)生成近5天營(yíng)業(yè)額曲線的動(dòng)態(tài)圖表。這種動(dòng)態(tài)圖表

不需要使用任何控件，這里稱(chēng)之為“自適應(yīng)動(dòng)態(tài)圖表”。

2.交互式動(dòng)態(tài)圖表

相對(duì)于“自適應(yīng)動(dòng)態(tài)圖表”，更多動(dòng)態(tài)圖表是通過(guò)

控件來(lái)控制定義名稱(chēng)中的引用范圍，進(jìn)而改變圖表顯示

內(nèi)容，這種動(dòng)態(tài)圖表稱(chēng)之為“交互式動(dòng)態(tài)圖表”。

仍使用上例中的數(shù)據(jù)源，共有9天的數(shù)據(jù)，要求在

一個(gè)圖表中動(dòng)態(tài)顯示3?9天的營(yíng)業(yè)額曲線圖，如圖

8.41所示。

圖8.41動(dòng)態(tài)顯示3?9天營(yíng)業(yè)額曲線

問(wèn)題分析：這是一個(gè)典型的交互式動(dòng)態(tài)圖表，使用

了“滾動(dòng)條”控件來(lái)改變圖表數(shù)據(jù)系列中定義名稱(chēng)的引

用范圍，進(jìn)而使圖表動(dòng)起來(lái)。

解決方案：對(duì)于動(dòng)態(tài)圖表，除了像上例中先繪制普

通圖表，然后修改數(shù)據(jù)源的取值之外，還可以直接以定

義名稱(chēng)作為數(shù)據(jù)源來(lái)繪制。繪制動(dòng)態(tài)圖表常規(guī)的步驟是

先設(shè)置控件，然后定義名稱(chēng)作為圖表的數(shù)據(jù)源（也可以

使用輔助列代替定義名稱(chēng)），最后以已定義的名稱(chēng)為數(shù)

第8章函數(shù)公式綜合應(yīng)用

據(jù)源繪制圖表。

1）設(shè)置“滾動(dòng)條”控件

單擊“開(kāi)發(fā)工具”|“控件”|“插入”命令，在彈

出的下拉選項(xiàng)中選取“表單控件”中的“滾動(dòng)條”控

件，如圖8.42所示。如用戶的Excel中沒(méi)有“開(kāi)發(fā)工具”

選項(xiàng)卡，可以單擊“文件”|“選項(xiàng)”命令，在彈出的

“Excel選項(xiàng)”對(duì)話框中點(diǎn)選“自定義功能區(qū)”選項(xiàng)，在

右側(cè)的“主選項(xiàng)框”中勾選“開(kāi)發(fā)工具”選項(xiàng)卡。

在鼠標(biāo)變成十字箭頭形狀之后，按住左鍵拖動(dòng)，放

開(kāi)左鍵后即完成“滾動(dòng)條”控件的生成。選中生成的

“滾動(dòng)條”控件，在右鍵菜單中選擇“設(shè)置控件格式”

命令，在彈出的“設(shè)置控件格式”對(duì)話框中設(shè)置如圖

8.43所示。

圖8.42插入“滾動(dòng)條”控件

第8章函數(shù)公式綜合應(yīng)用

左小命毗Iaig/蝴

?彳二金用勒0)

圖8.43設(shè)置“滾動(dòng)條”控件

設(shè)置的最大值、最小值和需要?jiǎng)討B(tài)顯示的曲線天數(shù)

有關(guān)，步長(zhǎng)1代表單擊滾動(dòng)條數(shù)據(jù)變動(dòng)為1,單元格鏈

接D1是輔助單元格。

2)定義名稱(chēng)

需要定義兩個(gè)名稱(chēng)，分別作為數(shù)據(jù)源和坐標(biāo)軸標(biāo)簽

的取值，名稱(chēng)分別為“數(shù)據(jù)源”和“日期坐標(biāo)軸”，公

式如下：

數(shù)據(jù)源：

=0FFSET($B$2,?$D$1)

日期坐標(biāo)軸:

第8章函數(shù)公式綜合應(yīng)用

=OFFSET($A$2,?$D$1)

這兩個(gè)名稱(chēng)中都使用了控件中的輔助單元格D1,

實(shí)現(xiàn)了以控件來(lái)改變定義名稱(chēng)中的引用范圍。

3）繪制圖表

（1）插入一個(gè)空?qǐng)D表：不選擇數(shù)據(jù)源（選中某個(gè)空單元格）,

單擊“插入”|“圖位表”|“折線圖”命令，在彈出的下拉選項(xiàng)

中選擇"二維折線圖”中的“帶數(shù)據(jù)標(biāo)記的折線圖”，在表格中

插入了一個(gè)空白圖表。

（2）修改數(shù)據(jù)源：選中空白圖表，單擊“圖表工具”|“設(shè)

計(jì)”|“數(shù)據(jù)”|“選擇數(shù)據(jù)”命令，彈出“選擇數(shù)據(jù)源”對(duì)話

框，單擊“添加”按鈕，在彈出的“編輯數(shù)據(jù)系列”對(duì)話框中設(shè)

置如圖8.44所示。

單擊“確定”按鈕后回到“選擇數(shù)據(jù)源”對(duì)話框,

單擊“水平（分類(lèi)軸）標(biāo)簽”下的“編輯”按鈕，在彈

出的“軸標(biāo)簽”對(duì)話框中設(shè)置如圖8.45所示。

揄煙敷加系列7宏’

系列名稱(chēng)垣）:

1

取消

圖8.44添加“營(yíng)業(yè)額”系列

第8章函數(shù)公式綜合應(yīng)用

油保塞區(qū)域1）：

交互式切乏圄酒!日期上飛陽(yáng)圃選擇區(qū)域

睚一瑜

圖8.45設(shè)置“軸標(biāo)簽”

單擊“確定”按鈕，返回“選擇數(shù)據(jù)源”對(duì)話框,

單擊“確定”按鈕退出，完成交互式動(dòng)態(tài)圖表的初步設(shè)

置，如圖8.46所示。

營(yíng)業(yè)額

圖8.46未做修飾的交互式動(dòng)態(tài)圖表

4）美化圖表

（1）刪除圖例：因只有一個(gè)數(shù)據(jù)系列，圖例屬于

多余。鼠標(biāo)單擊選中圖例，按Delete鍵刪除。

（2）設(shè)置坐標(biāo)軸在刻度線上：對(duì)于折線圖，可以

將水平坐標(biāo)軸刻度設(shè)置成和數(shù)值軸相交（柱形圖則無(wú)需

改變），雙擊“水平（類(lèi)別）軸”，彈出“設(shè)置坐標(biāo)軸

第8章函數(shù)公式綜合應(yīng)用

格式”對(duì)話框，改變“位置坐標(biāo)軸”下的單選為“在刻

度線上”，如圖8.47所示。

(3)組合圖表和控件：先選中“滾動(dòng)條”控件,

在右鍵菜單中選取“疊放次序”|“置于頂層”命令,

如圖8.48所示。

*菁-t!W

IW0M坐標(biāo)軸選項(xiàng)

砂字

g兄41X5?*<G>

wa?fcjivp

VSMeftt。u、<>

as

fft

n祀源m3

力元［Mt知電”

。iantu?ain^aci>

=S?X文*￥*5,i:

熊芥方式臼>1，，“rr□

l?namaSlQ)T*tf

I.

4h?tk?<l>s*\

隊(duì)二驚”7.R：

?日tUMlr)

*BM<t)：:、；??

圖8.47設(shè)置坐標(biāo)軸在刻度線上

第8章函數(shù)公式綜合應(yīng)用

會(huì)放次序［R1

電胃T底層的

4上移層(D

ATW層(B)

圖8.48設(shè)置“滾動(dòng)條”控件的“疊放次序”

然后將“滾動(dòng)條”控件拖動(dòng)到圖表中的指定位置,

如圖表的右上角。

最后按住CM鍵，選中圖表和“滾動(dòng)條”控件，在

右鍵菜單中選取“組合”|“組合”命令，完成圖表和

“滾動(dòng)條”控件的組合。

備注:組合之后直接拖動(dòng)圖表時(shí)控件會(huì)一起移動(dòng),

但如單擊圖表后再單擊拖動(dòng)圖表,圖表即和控件分離。

這屬于2010版中的Bug,使用2003版沒(méi)有這個(gè)問(wèn)題。

(4)編輯圖表標(biāo)題：先設(shè)置輔助單元格的自定義

格式，選中D1單元格，按CM+1組合鍵進(jìn)入“設(shè)置單

元格格式”對(duì)話框，在“分類(lèi)”框中選擇“自定義”，

設(shè)置自定義代碼為“最近0天營(yíng)業(yè)額”。

選中圖表中的“圖表標(biāo)題"在編輯欄中錄入

“二"然后引用D1單元格，此時(shí)圖表標(biāo)題為“最近7天

營(yíng)業(yè)額”。

(5)移動(dòng)圖表位置：移動(dòng)圖表位置遮蓋住D1單元

格，完成全部交互式圖表的設(shè)置，效果如圖8.41所

第8章函數(shù)公式綜合應(yīng)用

ZFo

3.控件相互交互的動(dòng)態(tài)圖表

再舉一個(gè)復(fù)雜些的案例，圖8.49是一個(gè)二維表

格，橫向是各個(gè)商場(chǎng)，縱向是統(tǒng)計(jì)的月份，每個(gè)商場(chǎng)都

有3列數(shù)據(jù)，分別為“銷(xiāo)售額”、“銷(xiāo)售目標(biāo)”和“完成

率”。需要在一張圖表中動(dòng)態(tài)反映分商場(chǎng)各月份的銷(xiāo)售

完成情況以及分月份各個(gè)商場(chǎng)的指標(biāo)。

/ABCDEFoORs

10場(chǎng)自件

2月份依”霜納=/U—偏士

3mc<Ki1Ggg260*000ioea2.061.若，工T：”O(jiān)CO:二f

4二月fti.T24200.COO31工xaitsLSW.g03

6三月,，?MM1.3M.OOO

6HL200^20Q.CW一%?<MOSLCOO.OOOC2%

IM.729280.000snJS-720180.OX201rci.ioiu1W.OOO€1%

穴日123xa13Q.000sea6S2*431S€0,000

Ml-L

9切IL組goop然CTO.OGO仆

10181X000m亮5<2S.SS9000

11九同SOOL000g)60.00017M1.n.161.1UH000gz

12220LCO0Til14t.l€02&^C：O5MLg.906LS50.000

13R什U033,S9)1.730,0860?%06^323IL6M.000

圖8.49以二維數(shù)據(jù)源制作圖表

1）問(wèn)題分析

一張圖表中不可能容納太多的信息，但可以通過(guò)控

件來(lái)控制圖表中顯示的信息，是一個(gè)使用控件的交互式

動(dòng)態(tài)圖表。

有通過(guò)商場(chǎng)和月份分析數(shù)據(jù)的要求，可以用一個(gè)控

件實(shí)現(xiàn)在“商場(chǎng)”和“月份”之間的切換；使用第二個(gè)

控件來(lái)動(dòng)態(tài)顯示“商場(chǎng)”和“月份”的明細(xì)，思路類(lèi)似

于之前介紹的“二級(jí)下拉”列表，稱(chēng)之為“控件交互”

的動(dòng)態(tài)圖表。

這里每個(gè)商場(chǎng)有3列數(shù)據(jù)，相當(dāng)于圖表有3個(gè)系

列，“完成率”系列的數(shù)據(jù)等量級(jí)和“銷(xiāo)售額”及“銷(xiāo)

售目標(biāo)”系列不一樣，可以將“完成率”系列設(shè)置在次

坐標(biāo)軸。

第8章函數(shù)公式綜合應(yīng)用

總結(jié)一下，需要繪制的交互式圖表有2個(gè)控件3個(gè)

數(shù)據(jù)系列，并使用主次坐標(biāo)軸。

2)解決方案

不再重復(fù)完整的交互式動(dòng)態(tài)圖表操作步驟，這里需

要注意以下3點(diǎn)：

(1)設(shè)置“商場(chǎng)”或“月份”明細(xì)數(shù)據(jù)控件：這

個(gè)控件下拉顯示的內(nèi)容需要根據(jù)前一個(gè)控件的變化而變

化，屬于“交互式控件”。先定義名稱(chēng)“二級(jí)下拉”，

公式如下：

=CHOOSE($C$15,$D$16:$D$21,$A$3:$A

$13)

這里C15是第一個(gè)控件使用的輔助單元格，D16：

D21區(qū)域是輔助單元格，存放商場(chǎng)名稱(chēng)，A3：A13區(qū)

域是月份信息。使用CHOOSE函數(shù)實(shí)現(xiàn)第一控件取值

變化時(shí)第二控件引用單元格區(qū)域的變化。

第二控件設(shè)置如圖8.50所示，其中數(shù)據(jù)源區(qū)域?yàn)?/p>

定義的名稱(chēng)“二級(jí)下拉”，D15單元格為輔助單元格。

第8章函數(shù)公式綜合應(yīng)用

?!--

二！iTli

隼可。**咕r1^11?

TRfi".ffW'ii"

■JC-4>

圖8.50第二控件(交互式控件)設(shè)置

(2)定義名稱(chēng)：一共需要定義4個(gè)名稱(chēng)，分別為3

個(gè)數(shù)據(jù)系列及坐標(biāo)軸，相關(guān)公式如下。

銷(xiāo)售額:

二CHOOSE（控件交互!$C$15,OFFSET（控件交

互！$A$3,,控件交互！$D

$15*3-2,10,）,N（INDIRECT（"控件交互!R&2+控件

交互!$D$15&C&R0W（控件交互!$1:$5）*3-1,）））

銷(xiāo)售目標(biāo)：

二CHOOSE（控件交互!$C$15,OFFSET（控件交

互！$A$3?控件交互！$D

$15*3-1,10,）,N（INDIRECT,控件交互!R&2+控件

交互!$D$交&C&ROW（控件交互!$1:$5）*3,）））

第8章函數(shù)公式綜合應(yīng)用

完成率：

二CHOOSE(控件交互!$C$15QFFSET(控件交

互!$A$3”控件交互!$D$15*3,10,),N(INDIRECT("控

件交互!R"&2+控件交互!$D$15&"C”&R0W(控件交

互!$1:$5)*3+1,)))

坐標(biāo)軸：

二CHOOSE(控件交互!$C$15,控件交互!$A$3:

$A$12,控件交互!$D$16:$D$20)

公式解析：前3個(gè)公式結(jié)構(gòu)相似，外層都是

CHOOSE函數(shù)，根據(jù)CHOOSE函數(shù)第1參數(shù)，即第一

個(gè)控件的輔助單元格數(shù)值來(lái)決定返回CHOOSE函數(shù)的

第2參數(shù)或者第3參數(shù)。這里以第一個(gè)“銷(xiāo)售額”公式

為例講解。

在C15單元格數(shù)值為1時(shí)，返回CHOOSE函數(shù)第2

參數(shù)即OFFSET函數(shù)引用的結(jié)果；為2時(shí)返回

CHOOSE函數(shù)第3參數(shù)N(INDIRECT)函數(shù)引用的結(jié)

果。

以兩個(gè)控件的輔助單元格C15和D15數(shù)值都為1時(shí)

來(lái)分析，CHOOSE函數(shù)第2參數(shù)OFFSET函數(shù)相當(dāng)于

“OFFSET(A3,0,1*3-2,10,)w,即以A3為起點(diǎn),

向右偏移1列，取到“武廣”商場(chǎng)1?10月份的銷(xiāo)售

額。輔助單元格D15取值的變化控制了OFFSET函數(shù)

向右偏移的列。

如C15單元格數(shù)值為2,D15單元格數(shù)值仍為1,

第8章函數(shù)公式綜合應(yīng)用

CHOOSE函數(shù)第3參數(shù)中INDIRECT函數(shù)相當(dāng)于

KINDIRECT("R"&2+1&"C"&ROW($1:$5)*3-

1,y,即分別引用第3行的2、5、8、11、14列數(shù)

據(jù)。輔助單元格D15取值的變化控制了INDIRECT函數(shù)

引用的行，外套N函數(shù)起到了降維的作用。

(3)設(shè)置“完成率”系列在次坐標(biāo)軸：在繪制圖

表時(shí)可先插入一個(gè)空白的柱形圖，然后添加3個(gè)數(shù)據(jù)系

歹必因“完成率”系列的數(shù)據(jù)等量級(jí)和“銷(xiāo)售額”及

“銷(xiāo)售目標(biāo)”系列不一樣，可以將“完成率”系列設(shè)置

在次坐標(biāo)軸，并更改圖表類(lèi)型為折線圖。

操作時(shí)先從“圖表工具”|“布局”|“當(dāng)前所選內(nèi)

容”選項(xiàng)中的下拉列表中選取“完成率”系列，如圖

8.51所示。

Fta詠?zhàn)?m.M

可七可均日嗣㈤川—.

BK彩狀文g■蕓*■發(fā)行”flM9去幢MPHMKX?

圖8.51選取“完成率”系列

然后單擊“設(shè)置所選內(nèi)容格式”命令，在彈出的

“設(shè)置數(shù)據(jù)系列格式”對(duì)話框中選擇“系列選項(xiàng)”，系

列繪制在“次坐標(biāo)軸”。

再次選中“完成率”系列，單擊“圖表工具”|

“設(shè)計(jì)，，|“類(lèi)型”|“更改圖表類(lèi)型，，命令，在彈出的

“更改圖表類(lèi)型”對(duì)話框中選擇“折線圖——帶數(shù)據(jù)標(biāo)

記的折線圖”。

之后是一些圖表美化及控件組合操作，完成效果如

圖8.52所示。

第8章函數(shù)公式綜合應(yīng)用

圖8.52控件交互的動(dòng)態(tài)圖表

從上述案例可以發(fā)現(xiàn)動(dòng)態(tài)圖表其實(shí)就是在玩函數(shù),

有能力把定義名稱(chēng)的公式寫(xiě)出來(lái)，就能讓圖表動(dòng)起來(lái)。

職場(chǎng)點(diǎn)睛:繪制動(dòng)態(tài)圖表能實(shí)現(xiàn)在一張圖表中動(dòng)態(tài)

顯示大量的數(shù)據(jù),在會(huì)議演示時(shí)較有用,建議用戶嘗試。

本節(jié)示例文件：《824擴(kuò)展圖表功能.xlsx》。

8.3小結(jié)

本章對(duì)于一些常用的函數(shù)公式組合套路做了總結(jié)，

講解了最常用的條件計(jì)算、條件查找和條件篩選公式，

對(duì)于可能遇到的條件加深了理解。限于篇幅所限，舉例

僅圍繞查找引用和計(jì)算類(lèi)問(wèn)題展開(kāi)，對(duì)于其他精彩的函

數(shù)組合只能割爰。

使用函數(shù)擴(kuò)展Excel內(nèi)置功能的例子還有很多，比

如函數(shù)公式在排序、篩選、數(shù)據(jù)透視表中都有精彩應(yīng)用，

相關(guān)內(nèi)容將在后續(xù)章節(jié)中繼續(xù)講解。

絕了！Excel可以這樣用——數(shù)據(jù)處理、計(jì)算…

第3篇分析數(shù)據(jù)

第9章數(shù)據(jù)分析的常規(guī)武器

在本書(shū)之前的5個(gè)章節(jié)中介紹了使用函數(shù)處理數(shù)

據(jù)，執(zhí)行查找、計(jì)算等。函數(shù)公式很精彩，但需要經(jīng)過(guò)

一些系統(tǒng)的學(xué)習(xí)才能融會(huì)貫通運(yùn)用自如。從本章開(kāi)始將

介紹一些使用鼠標(biāo)操作就能完成的功能，比如常規(guī)的數(shù)

據(jù)分析武器。

9.1這樣的排序你用過(guò)嗎

說(shuō)起Excel中的排序功能，似乎人人都會(huì)。應(yīng)該說(shuō)

是幾乎人人都在用，但是不是人人都會(huì)那可就要打個(gè)問(wèn)

號(hào)了。

9.1.1Excel中的兩種排序方法

在Excel中有兩種排序方法，但絕大多數(shù)用戶使用

的是第一種。

1.常規(guī)的排序操作

如圖9.1所示的數(shù)據(jù)源，單擊“開(kāi)始”|“編輯”|

“排序和篩選”命令，在彈出的下拉菜單中選擇“升序”

或者僻序”。

9.1這樣的排序你用過(guò)嗎

因J??J二曲?:9.1MK，OWHEM?<<@□

__16*EBA興nVMSUBKtMQnA以

■Ao.11????，總喳?4X?

々■中■/?木W(wǎng)孽3田?U.、?rVMS-

U￡wtuane

/?

?J2?%▲?Y?

F13■A\U?序9：

1/A

BCDEF3maAJMmo

1月份

*WkD

5422月WEST1?197.30

5431月WEST11473.40

M2HWEST其色*界452880

5451月WEST里夫E.匕Z懂和Rf149760

5462月WEST*發(fā)仄列W.60

5472aWEST分校系列84330

548IXWEST720.10

5491月WEST窘就仄乃711.10

550WESTit文市男士系列10960

圖9.1常規(guī)的排序操作

這時(shí)候Excel會(huì)自動(dòng)對(duì)活動(dòng)單元格所在的列執(zhí)行

“升序”或者“降序”操作。在數(shù)據(jù)源規(guī)范的情況下不

需要先選擇排序的區(qū)域，Excel會(huì)自動(dòng)作出判斷。

這種方法操作很簡(jiǎn)單，但一次只能對(duì)一列（一個(gè)關(guān)

鍵字）排序。

2.使用“排序”對(duì)話框

如果需要對(duì)數(shù)據(jù)源按多個(gè)關(guān)鍵字排序，上述方法不

僅是效率低，而且僅限于常規(guī)的“升序”和“降序”。

自Excel2007版起升級(jí)了“排序”對(duì)話框，可以對(duì)數(shù)

據(jù)源按多個(gè)關(guān)鍵字進(jìn)行排序。

有兩種方法打開(kāi)“排序”對(duì)話框，分別如下，彈出

的“排序”對(duì)話框如圖9.2所示。

口單擊“開(kāi)始”|“編輯”|“排序和篩選”命令，在彈出的

下拉菜單中選擇“自定義排序”。

口單擊“數(shù)據(jù)”|“排序和篩選”|“排序”命令。

9.1這樣的排序你用過(guò)嗎

所mran

，注酒條件卜x*際條并◎。復(fù)制條件?I■：▼|/靜揖包含標(biāo)non

［列切序體丹-

王耿"字'-iH1.開(kāi)中-

__、f1

取肩

圖9.2“排序”對(duì)話框

在“排序”對(duì)話框中可以添加或者刪除條件，單擊

“選項(xiàng)”按鈕，還可以執(zhí)行一些特殊的排序操作。

9.1.2按多個(gè)關(guān)鍵字排序

在Excel2003及以前版本中，排序的關(guān)鍵字最多

不能超過(guò)3個(gè)。如操作3個(gè)以上關(guān)鍵字，需要分批設(shè)

置。而在Excel2007及以后版本中，可以對(duì)數(shù)據(jù)源按

最多64個(gè)關(guān)鍵字進(jìn)行排序。

1,按4個(gè)關(guān)鍵字排序

沿用上例中的數(shù)據(jù)源，需要按照“金額”、“區(qū)

域”、“省份”、“月份”等4個(gè)關(guān)鍵字依次排序，其中

“金額”為主要關(guān)鍵字，降序排列，其他關(guān)鍵字按升序

排列。

解決方案：選取需要排序的數(shù)據(jù)源區(qū)域（在數(shù)據(jù)規(guī)

范時(shí)鼠標(biāo)選中數(shù)據(jù)源中某個(gè)單元格即可），按之前講述

的兩種方法之一調(diào)出“排序”對(duì)話框，設(shè)置排序條件如

圖9.3所示。

單擊“確定”按鈕完成，效果如圖9.4所示。

2.字符排序的規(guī)律

9.1這樣的排序你用過(guò)嗎

相對(duì)于數(shù)字排序，字符排序的規(guī)律比較難理解。對(duì)

于圖9.4中的前4條記錄，在主要關(guān)鍵字“金額”相等的

情況下，區(qū)域“EAST”排在“SH”前面，這個(gè)和英

文字母的順序有關(guān)；區(qū)域相同時(shí)省份“江蘇省”排在

“浙江省”前面，這就涉及到中文字符排序的規(guī)則了。

圖9.3按多個(gè)關(guān)鍵字排序

在Excel中對(duì)于中文字符默認(rèn)按照第一個(gè)字符的拼

音首字母在26個(gè)英文字母中的順序進(jìn)行排列。如首字

母相同則比較后續(xù)字母的順序；如第一個(gè)字符相同則比

較后續(xù)字符。

這里“江蘇省”第一個(gè)字符的第一個(gè)字母是J,“浙

江省”第一個(gè)字符的第一個(gè)字母是乙已經(jīng)有了先后之

分。

3.按筆劃排序

對(duì)于中文字符，除了常規(guī)的按拼音順序排序外，還

可以按照字符的筆劃順序排列。

在完成排序關(guān)鍵字的設(shè)置后，在“排序”對(duì)話框中

單擊“選項(xiàng)”按鈕，在彈出的“排序選項(xiàng)”對(duì)話框中單

選“筆劃排序”方法，如圖9.5所示。

9.1這樣的排序你用過(guò)嗎

■,

ABCDE

1月份區(qū)域省份產(chǎn)品分美「金眼

22月EAST江蘇省NB>1基8奇衿技系列1.000,00000

3EAST浙江省N&1JB因高卜技系列1.000.000.00

4SH上海市N51%快將七特系列1.000.000.00

52月SH上龍巾NB*1基代島牛技系列1.000,000.00

61月EAST江蘇省生化￥4技系列813.742.10

72月EAST江方省生化蒼小技系九785.30320

圖9.4多關(guān)鍵字排序結(jié)果

tw的首盤(pán)

?I一I一I

□區(qū)把M'g

櫥用岸QJ

自注

。宇日井*電

，祥佛南呼

QjjfexJdIMI

圖9.5“排序選項(xiàng)”對(duì)話框

對(duì)本例中的省份分別采用兩種排序方法，顯示結(jié)果

如圖9.6所示。

9.1這樣的排序你用過(guò)嗎

ABC

1常嫌排序技筆劃排序

2山東省

3北京市山西省

4福建省上海市

5甘肅省廣東省

6廣東省廣東省L州

圖9.6兩種排序方法比較

按筆劃排序?qū)儆谥袊?guó)人的習(xí)慣，首先比較第一個(gè)字

符筆劃的數(shù)量，相同按照內(nèi)碼順序進(jìn)行排列，第一個(gè)字

符完全相同再比較第二個(gè)字符。

9.1.3按特殊的規(guī)則排序

常規(guī)的排序是按列、按數(shù)值的升序或者降序排列。

有常規(guī)自然就有非常規(guī)，這些都隱藏在“選項(xiàng)”按鈕及

“排序依據(jù)”和“次序”下拉列表中。

1,按行排序

圖9.5的“排序選項(xiàng)”對(duì)話框中還有其他選項(xiàng)，如

“區(qū)分大小寫(xiě)”、“按行排序”方向等。這些選項(xiàng)有一個(gè)

共同的特點(diǎn)，其作用于排序框中的所有排序關(guān)鍵字，并

不能僅針對(duì)于某個(gè)排序關(guān)鍵字。

比較特別的是“按行排序”，顛覆了絕大多數(shù)用戶

對(duì)于Excel僅能按列進(jìn)行排序的認(rèn)知。

“按行排序”的操作方法和之前介紹的“按列排

序”類(lèi)似，例如需對(duì)圖97中的數(shù)據(jù)源按照第1行“省

份”排序。

ABCDG

例廣東W女?dāng)?shù)W廣東*廣州甘肅發(fā)北京市福建傕

219,29。56,6/468題769,914J3,92380707

9.1這樣的排序你用過(guò)嗎

圖9.7需按行排序的數(shù)據(jù)源

操作步驟如下：

(1)選取B1：G2單元格區(qū)域，調(diào)出“排序”對(duì)話框。

(2)單擊“選項(xiàng)"按鈕，在"排序選項(xiàng)”對(duì)話框中選擇“按

行排序”，單擊“確定”按鈕回到"排序”對(duì)話框。

(3)在"主要關(guān)鍵字”框中選擇“行1”，注意比較圖9.8中

的變化。

X?柒律9，7黃II擊仔(0］一?Ar

［布11'-I的值團(tuán)n

SX.]94

圖9.8按行排序

因“數(shù)據(jù)包含標(biāo)題”復(fù)選框?yàn)榛疑?，?duì)于按行排序

是沒(méi)有表頭的，因此在操作中不能選中需要排序內(nèi)容表

頭所在的列。對(duì)應(yīng)的關(guān)鍵字也僅能選擇“行N”之類(lèi)的。

2.按顏色排序

在Excel2003版及其之前版本中，對(duì)于顏色的操

作需要通過(guò)宏表函數(shù)結(jié)合輔助列來(lái)實(shí)現(xiàn)。自2007版開(kāi)

始，新增了按顏色排序和篩選功能。

圖9.9是一個(gè)按照單元格背景色及銷(xiāo)售額大小排序

的例子。

9.1這樣的排序你用過(guò)嗎

ABCjF-GHii'J|K

1**GH播序規(guī)削：

21、五NN色星元電!FBI、a；r??(2Jonft.

3NURIH3,663,88012.ITH,氈及里博售M。、府序邦州?

444”7|

4SH

5SOUTH

6tASI/加乙362]

71

8WEST26744C2|

9COUNItR

10odi、

11TRAINING

12SPA

圖9.9按單元格背景色排序

操作步驟如下：

復(fù)制數(shù)據(jù)到D：E歹IJ,選取需要排序的D2：E12單

元格區(qū)域，調(diào)出“排序”對(duì)話框，設(shè)置如圖9.10所示。

圖9.10“排序”對(duì)話框中設(shè)置按顏色排序

單擊“排序依據(jù)”框的下拉列表，除了“數(shù)值”、

“單元格顏色”之外還有“字體顏色”和“單元格圖

標(biāo)”，操作類(lèi)似，不再舉例。

職場(chǎng)點(diǎn)睛:對(duì)于工作中喜歡設(shè)置單元格顏色的用戶,

2007版中新增加的按顏色排序和篩選功能非常實(shí)用,

其他用戶也可以考慮改變一下操作習(xí)慣。

3.按自定義序列排序

9.1這樣的排序你用過(guò)嗎

對(duì)于中文字符，Excel中默認(rèn)的是按照拼音的字母

順序排序，也可以選擇按照筆劃排序，但這些選項(xiàng)并不

能完全滿足用戶特定的需求。

如圖9.11的例子，按職務(wù)高低排序，相同職務(wù)則按

入職時(shí)間先后。常規(guī)方法無(wú)法解決，需要使用“自定義

序列”排序。

*AJB1)tFG

1*00

—1AWTVL.

3KeltSU24箱6口拄上TOOOBIOTUR

4虧小用TOOWilOfluR■計(jì)，：以

5馮2舄圾外L抑事?印口MWIi700943月？Sfl

6Q?廣2009iM0月24口助201042電口

7十員020”用10用2H王營(yíng)20091312$曰

8艘科2010年2“3y■節(jié)牛首20gBJO」24H

90付JT婚百楞201X3月1雨2012?7市B

，而

10用儻粒月？SRwgp”1*1月IBB

HZ2OO?S3M2SB1千通中Eizigi/n

12

13捕用恭4X，

14持廠界R｝假播序，楣E3F曼傳■人中酊依￡后達(dá)早、

圖9.11按“自定義序列”排序

操作步驟如下：

(1)設(shè)置"自定義序列”，單擊“文件’|“選項(xiàng)”命令，在

“Excel選項(xiàng)”對(duì)話框中單擊"高級(jí)”選項(xiàng)，單擊“編輯自定義列

表”按鈕，在彈出的"自定義序列”對(duì)話框中新增職務(wù)名稱(chēng)序列,

具體操作參照本書(shū)2.2節(jié)中的講解。

(2)選取需要排序的E2：G11單元格區(qū)域，調(diào)出“排序”

對(duì)話框，設(shè)置如圖9.12所示。其中對(duì)于主要關(guān)鍵字“職務(wù)”，在

“次序”下拉列表中選取“自定義序列”，在彈出的“自定義序列”

對(duì)話框中選擇已設(shè)置的序列。

9.1這樣的排序你用過(guò)嗎

圖9.12設(shè)置"自定義序列”排序

職場(chǎng)點(diǎn)睛：“自定義序歹非常有用,學(xué)會(huì)這一

招,可以拖動(dòng)復(fù)制生成特定的序列及按特定的順序排序,

會(huì)讓Exce操作更加智能。

9.1.4排序時(shí)處理非規(guī)范數(shù)據(jù)區(qū)域

之前介紹的例子都是很規(guī)范的數(shù)據(jù)源，如數(shù)據(jù)源的

表頭就在第一行，此時(shí)只需要將鼠標(biāo)定位在數(shù)據(jù)源中，

Excel會(huì)自動(dòng)判斷需要排序的數(shù)據(jù)區(qū)域，無(wú)須先選定；

如表頭不在第一行中，也只需要全選需要排序的數(shù)據(jù)區(qū)

域即可。實(shí)際工作中的數(shù)據(jù)源及排序要求會(huì)更復(fù)雜。

1,排序時(shí)處理合并單元格

如圖9.13是一個(gè)在各個(gè)“品類(lèi)”中按“數(shù)量”降序

排列的例子，并非對(duì)于整個(gè)C列排序。

9.6小結(jié)

A8CFG

1推字后

2

3山依840山東840

4平果日奉476關(guān)印777

5■國(guó)777H本476

6本絕533日本870

日本870本地533

7蟲(chóng)子架子

8L西435廣西435

9二專(zhuān)176/一節(jié)176

廣西488廣488

10ft<725

11264云?264

13II序艘則，

14在百個(gè)“居英”中及“也重"誨序抖列.

圖9.13排序時(shí)處理合并單元格

因“品類(lèi)”下有合并單元格，如全選區(qū)域或者選擇

了有合并單元格E列的區(qū)域排序，Exc目會(huì)報(bào)錯(cuò)，如圖

9.14所示。

解決方法為不選取有合并單元格的列，因是分“品

類(lèi)”排序，分別選取各個(gè)“品類(lèi)”的后兩列數(shù)據(jù)，如選

取F3：G5單元格區(qū)域，調(diào)出“排序”對(duì)話框，設(shè)置如

圖9.15所示。

U&0g十

就將作事才言并單元整排應(yīng)由鈿畫(huà)大小&

「賈詞陽(yáng)后pF

圖9.14排序時(shí)有合并單元格會(huì)報(bào)錯(cuò)

10.2數(shù)據(jù)透視表之結(jié)構(gòu)變身

圖9.15取消“數(shù)據(jù)包含標(biāo)題”的勾選

注意需要取消“數(shù)據(jù)包含標(biāo)題”的勾選，此時(shí)關(guān)鍵

字顯示為列標(biāo)。依次完成其他“品類(lèi)”中的排序即可。

2.對(duì)部分區(qū)域排序

有時(shí)候僅需要對(duì)數(shù)據(jù)源中某個(gè)區(qū)域排序（如僅對(duì)其

中某一列排序等），此時(shí)操作方法為先選定需要排序的

數(shù)據(jù)區(qū)域，然后再做排序設(shè)置。

沿用上例中的數(shù)據(jù)源，如僅選中K3：K5單元格區(qū)

域，單擊“開(kāi)始”|"編輯”|"排序和篩選"命令，在

下拉菜單中選擇“自定義排序”，會(huì)彈出“排序提醒”

對(duì)話框，如圖916所示。

選擇“以當(dāng)前選定區(qū)域排序”選項(xiàng)，單擊“排序”

按鈕回到“排序”對(duì)話框，取消“數(shù)據(jù)包含標(biāo)題”選項(xiàng),

主要關(guān)鍵字選擇“列K”，單擊“確定”按鈕完成排序

操作。

依次完成其他“品類(lèi)”的K列排序，兩種不同區(qū)域

的排序結(jié)果比較如圖9.17所示。

10.2數(shù)據(jù)透視表之結(jié)構(gòu)變身

太愿在正士號(hào)蟲(chóng)亙有效次?詁RIB*軟時(shí)4，比

不孝口用a-

靖二器堤京器

.廠呼培工憶向

◎世4前骨區(qū)武邦中

1劇加1L

圖9.16“排序提醒”對(duì)話框

圖9.17兩種不同區(qū)域的排序結(jié)果比較

9.1.5排序應(yīng)用舉例

排序?qū)儆诤芑A(chǔ)的操作，實(shí)際工作中靈活運(yùn)用排序

有時(shí)會(huì)起到意想不到的效果，下面看兩個(gè)例子。

1.恢復(fù)排序前的順序

排序的特點(diǎn)是會(huì)直接改變數(shù)據(jù)源，有時(shí)候需要恢復(fù)

到排序之前的順序。Excel中可以撤銷(xiāo)之前的操作，但

撤銷(xiāo)的步數(shù)是有限的。如果保存了工作簿，之前的操作

都不能再恢復(fù)。

如圖9.18是一個(gè)對(duì)營(yíng)業(yè)額排序的例子，在排序之

前設(shè)置了輔助列，排序之后如需要恢復(fù)，只需對(duì)輔助列

按照升序排序即可。

10.2數(shù)據(jù)透視表之結(jié)構(gòu)變身

1

A|CDEFGH

IP

1---T一

2隅助列r!語(yǔ)明列營(yíng)業(yè)軟輔助列,營(yíng)業(yè)輯)

3169757541697

4257787262577

S32471697互247

6445025774450

7575465605754

8656044506560

9723432473234

10872672348726

圖9.18使用輔助列恢復(fù)到排序前狀態(tài)

職場(chǎng)點(diǎn)睛：對(duì)于重要的數(shù)據(jù)源,在操作之前建議先

備份,如在會(huì)議演示中使用排序,事先使用輔助列也是

一種選擇,可以快速恢復(fù)到排序前的狀態(tài)。

2.實(shí)現(xiàn)隔行插入

對(duì)于指定數(shù)據(jù)源實(shí)現(xiàn)隔行插入也是一個(gè)很經(jīng)典的問(wèn)

題，解決方法有很多，其中使用輔助列結(jié)合排序的方法

最簡(jiǎn)單。如圖9.19是一個(gè)工資表的例子，需要做成工資

條的形式，即隔行插入第1行中的表頭。

ABCD~E~FGH1

1I工號(hào)姓名基本工資13阿比1日匕也加LJ［充匕逞M電氏於1

2A001越梭1,0006.0004001,332二十151805,91620

3A002古亭1,0006,0001,5001,5306.970242006,72800

4A003于成中1,0005,000400115269805.17820

5A0O4次影汛1,0006,0001.5001.5306.970242.006,728.00

6A0O5騏結(jié)1,0004.000200012605740119005.62100

7A006林影伊1.0005,0001,5001,3506,150160005.990.00

8A007襲位棟1,0005,0004001,152524869805.17820

9A006部日華1.0004,000200012605740119005,62100

10A009物I拎1,0006.0008001.4046.396184.606,211.40

圖9.19隔行插入完成工資條

操作步驟如下：

(1)以J列為輔助列，在J2：J10單元格中錄入序列1?9。

(2)在J11單元格中錄入1.5,J12單元格中錄入2.5,序列

填充到8.5。

10.2數(shù)據(jù)透視表之結(jié)構(gòu)變身

(3)對(duì)數(shù)據(jù)區(qū)域按J列升序排序。

(4)復(fù)制表頭A1：11單元格區(qū)域。

(5)選取八1：A18單元格區(qū)域，按F5鍵定位到"空值"按

Ctrl+V快捷鍵粘貼完成。

如果需要隔行插入2行，可以在J列繼續(xù)添加輔助

單元格參與排序，注意輔助單元格的取值大小。

職場(chǎng)點(diǎn)睛:使用排序的方法生成工資條是最簡(jiǎn)便的

方法,無(wú)需學(xué)習(xí)函數(shù)公式和VBA代碼,只要鼠標(biāo)動(dòng)幾

下就能解決,建議用戶多嘗試。

本節(jié)示例文件：《91這樣的排序你用過(guò)

嗎.xlsx》。

絕了！Excel可以這樣用——數(shù)據(jù)處理、計(jì)算…

9.2避開(kāi)篩選中的陷阱

篩選也是很常用的數(shù)據(jù)分析工具，因其操作并不會(huì)

改變數(shù)據(jù)源，會(huì)比排序操作安全很多。但也僅是相對(duì),

如果數(shù)據(jù)源不規(guī)范或者操作不當(dāng)，還是很容易出錯(cuò)。

9.2.1篩選的建立與取消

自Excel2007版開(kāi)始，篩選功能得到了極大的增

強(qiáng)，表現(xiàn)在篩選選項(xiàng)增加了很多，而且有一定的智能。

這里先舉例對(duì)于篩選功能做一些介紹。

1.建立