Excel

案例01

第1部分Excel基礎(chǔ)概念

L名稱

名稱可以代表一個(gè)單元格或者一個(gè)單元格區(qū)域，或者是常量，公式。

名稱的定義：選中須要命名的單元格或區(qū)域，在界面左上角名稱框中輸入名稱后回車;

名稱的刪除：插入一名稱一定義，選中須要?jiǎng)h除的名稱點(diǎn)擊刪除按鈕，點(diǎn)確定。

名稱的引用：須要引用某單元格時(shí)輸入該單元格的名稱：=名稱

第1步：選中須要命名的某個(gè)單元格或單元格區(qū)域。

第2步：在左上角名稱框輸入命名后回車。

standard)▼儀工與

AB

1工資系數(shù)金額

2G1-11000

3G1-22000

4G1-33000

5G2-14000

6G2-25000

7G2-36000

8

刪除已有命名：選擇“插入”一“名稱”一“定義”；選中須要?jiǎng)h除的名稱，點(diǎn)擊“刪除”

按鈕。

插入復(fù)）I格式（Q）工具（1）數(shù)據(jù)Q）窗口也）

工作表但）7工

A函數(shù)但）.一

區(qū)用％，工+

名稱⑥一

定義如…

圖片9粘貼，...

嚏超鏈接9…指定（￡）...

應(yīng)用（A）...

標(biāo)簽?…

名稱命名的優(yōu)點(diǎn)：

1）避開確定引用的錯(cuò)誤

2）對(duì)公式進(jìn)行文字化表述，讓公式更加簡(jiǎn)潔理解

3）可以在整個(gè)工作簿中通用，引用便利

2,常用引用函數(shù)

row:返回指定單元格的行號(hào)

column:返回指定單元格的列標(biāo)

match:返回查找值在查找范圍中的序號(hào)

=match（查找值，查找范圍，0）

其第三個(gè)參數(shù)為0,表示查找精確值

address:返回單元格名稱，其參數(shù)為行號(hào)列標(biāo)或計(jì)算行號(hào)列標(biāo)的表達(dá)式

=address（行號(hào)，列標(biāo)）

=address（l,l）此公式返回Al

indirect:返回單元格的值.其參數(shù)為單元格名稱

=indirect（“A1"）,假設(shè)Al=10,則indirect返回10

index:在某區(qū)域內(nèi)查找某個(gè)位置的值

=index（查找區(qū)域，查找值所在的行號(hào)，查找值所在的列號(hào)）

offset：指定基點(diǎn)，指定位移量，得到單元格引用

單個(gè)單元格引用：offset（基點(diǎn)單元格，向下移動(dòng)的行，向右移動(dòng)的列）

區(qū)域的引用：=offset（基點(diǎn)單元格，向下移動(dòng)的行，向右移動(dòng)的列，區(qū)域

包括的行數(shù)，區(qū)域包括的列數(shù)）

向上和向左移動(dòng)時(shí)，位移量為負(fù)值。

=offset（Al,l,l：l將得到B2單元格的值

3.動(dòng)態(tài)引用

動(dòng)態(tài)引用是通過引用函數(shù)實(shí)現(xiàn)對(duì)于單元格或區(qū)域的相對(duì)引用。它和相對(duì)引用的效果很

相像，但比簡(jiǎn)潔的相對(duì)引用用途廣泛。

常用的動(dòng)態(tài)引用的實(shí)現(xiàn)方法有：

引用函數(shù)嵌套。比如offset和row,column嵌套;index和match,indirect,address

嵌套等；

引用函數(shù)與控件結(jié)合運(yùn)用，此類控件包括組合框，滾動(dòng)條。

動(dòng)態(tài)引用的作用：實(shí)現(xiàn)對(duì)于單元格的動(dòng)態(tài)引用；進(jìn)行動(dòng)態(tài)分析；制作動(dòng)態(tài)報(bào)表。

4.運(yùn)算類型

數(shù)值運(yùn)算：1+1=2

邏輯運(yùn)算：1>O=TRUE

在邏輯運(yùn)算中，TRUE=1,FALSE=O

我們利用邏輯運(yùn)算進(jìn)行條件推斷

在Excel中常用的一些邏輯函數(shù)：if,and,or,not,iseiror等

and:只有當(dāng)全部條件全部滿意，才會(huì)返回true的邏輯值

語法：and（條件1,條件2,…）

or:滿意其中任何一個(gè)條件，都會(huì)返回true的邏輯值

語法：or（條件1,條件2,條件3,…）

5.函數(shù)調(diào)用的語法

=函數(shù)名稱（參數(shù)1,參數(shù)2,…）

6.確定引用和相對(duì)引用：

確定引用：所引用的單元格不隨著公式的復(fù)制而移動(dòng)的引用方式。

相對(duì)引用：所引用的單元格隨著公式的復(fù)制而移動(dòng)的引用方式。

變更引用方式的方法：

1）在公式欄中選中須要變更引用方式的單元格，按F4鍵

2）給須要引用的單元格定義名稱，然后在公式中引用該名稱

7.數(shù)組公式

對(duì)單元格區(qū)域進(jìn)行多重計(jì)算的計(jì)算方式。與一般計(jì)算公式的區(qū)分是錄入公式結(jié)束后,

須要同時(shí)按下Ctrl+shift+enter,其特征是在公式兩端會(huì)出現(xiàn)一對(duì)大括號(hào)。

D11A{=SUM(B4:B10*C4:C10)}

ABCD

數(shù)量金額

1600020

800090

1200050

500100

100200

器

顯

5示

300090

顯

器

7示

500010

求合計(jì)

如上圖例，運(yùn)用一個(gè)公式計(jì)算出全部產(chǎn)品的金額合計(jì)，引用的是全部的單價(jià)和全部的

數(shù)量，執(zhí)行的是多重計(jì)算。

&錯(cuò)誤提示

Excel中存在錯(cuò)誤的類型，比如1/O=#DIV/O1,todas()=#NAMES!

iserror是一個(gè)邏輯函數(shù)，用以推斷某個(gè)單元格內(nèi)的值是否是一個(gè)錯(cuò)誤，是錯(cuò)誤則返回

TRUE,不是錯(cuò)誤則返回FALSE.

iserror有時(shí)可以和if函數(shù)嵌套進(jìn)行一些較為困難的推斷。

9.有取值區(qū)間的隨機(jī)數(shù)

=最小值+(最大值一最小值)*rand()

10.循環(huán)引用

是單元格引用其自身的引用方式。可以設(shè)置Excel允許進(jìn)行循環(huán)引用：

工具-選項(xiàng)-重新計(jì)算：將迭代計(jì)算選項(xiàng)打鉤選中。

行列互換(函數(shù)方式)：利用transpose函數(shù)十?dāng)?shù)組公式實(shí)現(xiàn)。

首先選中行列數(shù)和原區(qū)域相反的一個(gè)區(qū)域；

然后輸入transpose函數(shù)

最終按下組合鍵ctrl+shift+enter

第2部分Excel設(shè)置

1顯示當(dāng)前文件的完整路徑

菜單區(qū)域-右鍵菜單一Web

2顯示菜單項(xiàng)全部菜單

視圖一工具欄一自定義-選項(xiàng)一始終顯示整個(gè)菜單

3鼠標(biāo)移動(dòng)方向

工具一選項(xiàng)一編輯一按Enter鍵后移動(dòng)方向

4隱藏界面要素

工具一選項(xiàng)一視圖

包括：網(wǎng)格線，滾動(dòng)條，工作表標(biāo)簽，行號(hào)列標(biāo)等。

5以顯示值為準(zhǔn)

工具一選項(xiàng)一重新計(jì)算-以顯示精度為準(zhǔn)

行的合計(jì)與列的合計(jì)有時(shí)出現(xiàn)不相等的狀況。

93,409.74566,433.18

81,745.78535,989.59

28,441.93485,422.01

￥1,006,054.41￥10,913,030.98

￥10,913,030.96

可以采納以下方法解決。

選擇“工具”-“選項(xiàng)”-“重新計(jì)算”-“以顯示精度為準(zhǔn)。選中該選項(xiàng),

6自定義序列

工具一選項(xiàng)一自定義序列

7變更文件保存位置

工具一選項(xiàng)一常規(guī)一默認(rèn)文件位置

8變更文件用戶名

工具一選項(xiàng)一用戶名

9單元格自動(dòng)換行

格式一單元格一對(duì)齊一自動(dòng)換行

第3部分Excel基礎(chǔ)操作

1.快速選中數(shù)據(jù)表的整行或者整列

Ctrl+shift+下箭頭/右箭頭

2.快速選中區(qū)域

Ctrl+shift+8

3.行列互換

復(fù)制須要進(jìn)行行列互換的區(qū)域后，將光標(biāo)放置在數(shù)據(jù)表外面位置，選擇性粘貼一選中

“轉(zhuǎn)置”選項(xiàng)

4.一個(gè)單元格內(nèi)容輸入為多行

alt+回車鍵

5.顯示公式

Ctrl+~

6.凍結(jié)窗口

選擇須要進(jìn)行凍結(jié)的單元格位置，選擇窗口一凍結(jié)窗格

7.自定義格式語法：

［大于條件值］格式；I小于條件值］格式；［等于條件值］格式；文本格式

8.縮放數(shù)值

0.00,,按百萬縮放

00,按萬縮放

0.00,按千縮放

9.不復(fù)制隱藏的行或列

a首先選中須要復(fù)制的被隱藏了一些行或列的表格區(qū)域；b然后點(diǎn)擊“編輯”-“定位”

-“定位條件”，在其中選擇“可見單元格”；c復(fù)制表格區(qū)域，粘貼即可。

案例02

一文本的處理

1.等長(zhǎng)文本的分割

從字符串的左邊取字符：=Left（字符串，文本長(zhǎng)度）

從字符串的右邊取字符：=Right（字符串，文本長(zhǎng)度）

從字符串的中間取字符：=Mid（字符串，文本起始位置，文本長(zhǎng)度）

2.不等長(zhǎng)文本的分割

第1步：選中要進(jìn)行分割的字符串區(qū)域。

英文姓名英文昵稱，

steven.li

John,li

aeorae.liu

第2步：點(diǎn)擊“數(shù)據(jù)”-“分列”，在文本分列向?qū)е羞x擇適合的分隔符。

文本分列向?qū)?3步郭之2

請(qǐng)?jiān)O(shè)置分列數(shù)據(jù)所包含的分隔符號(hào)。在預(yù)覽窗口內(nèi)可看到分列的效果.

分隔符號(hào)

□連茂分隔符號(hào)視為單個(gè)處理6)

回Tab鍵(X)口分號(hào)皿)口逗號(hào)?

□空格6)□其他(Q)：文本識(shí)別符號(hào)Q):

數(shù)據(jù)預(yù)覽化)

steven.li

john.li

george.liu

?ric.zhou

I取消]k上一步8)]「三歪:誨二wi[完成?]

第3步：設(shè)置須要導(dǎo)入的列以與放置該列的位置。

3.文本的合并

=Concatenate（文本1,文本2,…）

運(yùn)用連接符&：

動(dòng)態(tài)表頭：="ABC公司"&YEAR(NOW())&”年"&MONTH(NOW())&”月報(bào)表”

4.有效性

選中須要設(shè)置有效性的區(qū)域，點(diǎn)擊“數(shù)據(jù)”一“有效性”，在有效性條件中選擇“序列”,

在來源中錄入或選取列表。

輸入信息：事前提示。

出錯(cuò)警告：事后提示。

假如待選列表不在當(dāng)前工作表中，須要首先為該列表定義名稱，然后在有效性來源框中輸

入：

”-該列表的名稱“

5.有效性的其他用法

不允許錄入重復(fù)數(shù)據(jù)的有效性設(shè)定：countif(e:e,el2)=l

輸入的內(nèi)容中必需包括某字符：=not(iserror(find("41,g30)))

二級(jí)選項(xiàng)：

首先將一級(jí)選項(xiàng)的每個(gè)項(xiàng)目定義一個(gè)名稱，該名稱內(nèi)容包括相應(yīng)的二級(jí)項(xiàng)目;

制作一級(jí)項(xiàng)目的有效性；

制作二級(jí)項(xiàng)目的有效性：內(nèi)容為：=lNDlRECT(g6),其中g(shù)6為設(shè)定「有效性的一級(jí)選項(xiàng)

所在的單元格。

6.圈示無效數(shù)據(jù)。

選擇“工具”一“公式審核”一“顯示公式審核工具欄”，點(diǎn)擊“圈示無效數(shù)據(jù)”按鈕。

公式審核▼x

6回回L后豉

該工具可以將全部不符合有效性設(shè)定的內(nèi)容圈示出來。

二日期的處理

:.Datedif函數(shù)：用于計(jì)算兩個(gè)日期之間的年數(shù)，月數(shù)，天數(shù)。

=Datedif(起先日期，結(jié)束日期，“y”)

第三個(gè)參數(shù)：

“y”：表示年數(shù)

“m”：表示月數(shù)

“d”：表示天數(shù)

2.生日提示

=DATEDIF(TODAY(),DATE(YEAR(TODAY()),MONTH(G2),DAY(G2)),"d")

3.某日期的星期

=weekday(A2,2)返回的值是3,則表示該日期是星期三。

4.兩個(gè)日期間的工作日天數(shù)

=networkdays(起先日期，結(jié)束日期，節(jié)假日列表)

須要首先加載“分析工具庫”才能運(yùn)用此工具

案例03數(shù)據(jù)查詢

1.VLOOKUP()

VLOOKUP在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前

行中指定列處的數(shù)值。VLOOKUP中的V代表垂直。

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value為須要在數(shù)組第一列中查找的數(shù)值。Lookup_value可以為數(shù)值、

引用或文本字符串。也可以理解為：兩表共有的索引字段。

Table.array為須要在其中查找數(shù)據(jù)的數(shù)據(jù)表?？梢赃\(yùn)用對(duì)區(qū)域或區(qū)域名稱的引用,

例如數(shù)據(jù)庫或列表。必需使得共有字段位于該范圍的第一列。

col_index_num為須要調(diào)轉(zhuǎn)的數(shù)據(jù)位于其次個(gè)參數(shù)中定義的范圍的第幾列。必需為

單純數(shù)值。

rangejookup定義大致匹配或精確匹配。False或0：精確匹配；true或忽視或1:

假如無法找到精確匹配的值，那么就查找并匹配比查找值小的最近似的值。

2.HLOOKUP0

Hlookup中的H是水平的意思，在橫向排列的表格中查詢數(shù)據(jù)用Hlookup,垂直排

列的表格查詢用Vlookup函數(shù)。其參數(shù)含義與VLOOKUP近似。

案例04查詢表

信息查詢表

利用有效性和VLOOKUP函數(shù)實(shí)現(xiàn)。

利用有效性實(shí)現(xiàn)索引字段的切換。

利用VLOOKUP函數(shù)查詢與索引字段相關(guān)的信息。

案例05數(shù)據(jù)篩選

1.自動(dòng)篩選（略）

2.高級(jí)篩選

由于自動(dòng)篩選只能篩選出簡(jiǎn)潔條件的數(shù)據(jù)，因此如要在困難條件下進(jìn)行篩選就須要采納高

級(jí)篩選的方式。

在須要進(jìn)行篩選的數(shù)據(jù)表外設(shè)定篩選條件。

地區(qū)銷售收入

北京>30000

濟(jì)南>30000

數(shù)據(jù)也）|窗口電）幫助國(guó)）AdobePDF

H排序@…

篩選任）?自動(dòng)篩選更）

有效性&）...全部顯示6）

模擬運(yùn)篁表1）...高級(jí)篩選&）...

在對(duì)話框中設(shè)定：列表區(qū)域?yàn)閿?shù)據(jù)表區(qū)域；條件區(qū)域?yàn)閯偛旁跀?shù)據(jù)表外部設(shè)定的條件區(qū)域,

要包括字段名稱和條件所在的單元格區(qū)域。

高級(jí)篇選

方式

。，在原有區(qū)域顯示篩選結(jié)果國(guó)）

O將篩選結(jié)果復(fù)制到其他位置（Q）

列表區(qū)域1）：0

條件區(qū)域C）：$H$24:$工$26S

口選擇不重復(fù)的記錄?）

I確定]I取消I

3.條件格式

條件格式可以使得符合特定條件的記錄依據(jù)某種設(shè)定的格式顯示。

1）選中要進(jìn)行條件格式設(shè)定的單元格范圍

2）執(zhí)行【格式】【條件格式】

格式@）|工具（I）數(shù)據(jù)①）

廳單元格更）...Ctrl+1

行??

列??

工作表也）?

自動(dòng)套用格式

I~條件格式①）

3）在條件格式設(shè)置界面進(jìn)行條件的設(shè)定:

4.統(tǒng)計(jì)

Frequency（須要進(jìn)行統(tǒng)計(jì)的數(shù)據(jù)區(qū)域，分段點(diǎn)）

對(duì)數(shù)據(jù)進(jìn)行區(qū)間統(tǒng)計(jì)。此函數(shù)須要和數(shù)組公式一起運(yùn)用。此函數(shù)是依據(jù)數(shù)值的大小劃

分區(qū)間進(jìn)行統(tǒng)計(jì)的。

列出須要分析的工資區(qū)間，選中須要計(jì)算統(tǒng)計(jì)結(jié)果的單元格區(qū)域，輸入函數(shù):

二frequency(P2:P22,R2:R7)

|FREQUENCY(dataarray,binsarrayj|QPORS

婚姻性別血型年齡公司工齡工資系數(shù)工資金頡Ifreauencv

女

未攵B

R6G1-11000「1508空：P22,R2:R7

攵

男

未B

RG2-360002500；

攵

女

皆

己

G1-220003500：

女

男

己

皆□

G2-140004500；

攵

未

B女

RG1-330005500；

攵

未

男

皆

G1-22000^6500

攵

未

女

皆q

己

攵G2-14000

6男

RG1-33000

按下組合鍵：Ctrl+shift+enter

5.排名次

=rank（需排名數(shù)值，排名數(shù)據(jù)范圍，排序方式）

排序方式：0或忽視降序，否則為升序。

案例06窗體控件的運(yùn)用

-下拉菜單的制作。

選擇“視圖”一“工具欄”一“窗體”，出現(xiàn)“窗體”工具條，在工具條上選擇“組合框”。

Web

保護(hù)

邊框

窗體

公式審核

按下鼠標(biāo)左鍵，拖放出合適的大小后釋放鼠標(biāo)，出現(xiàn)下拉菜單形態(tài)。選中該下拉菜單，右

鍵選擇“設(shè)置控件格式”

剪切①

復(fù)制?

粘貼9

組合⑥

存放次序(R)

指定宏國(guó)).?.

%設(shè)置控件格式［).?.

在“限制”菜單上，去選擇相應(yīng)的數(shù)據(jù)源?！叭S陰影”選項(xiàng)是下拉菜單顯示效果設(shè)定。

單元格鏈接：該處指定的單元格中將存儲(chǔ)在控件中被選中的項(xiàng)目在數(shù)據(jù)源中的序列號(hào)。我

們常常利用該序列號(hào)引用控件選中的值，還可以利用該值制作動(dòng)態(tài)圖表。

依據(jù)報(bào)銷單上填報(bào)的日期自動(dòng)生成該報(bào)銷單的起止H期o

Peiiod:

From:l2006^3

運(yùn)用最人值函數(shù)MAX和最小值函數(shù)MINo

To:2006?9/16

制作單選按鈕。

Paymentparty:

◎COMPANYPAY

運(yùn)用窗體工具條上的“選項(xiàng)按鈕”

OCLIENTPAY

2.界面定制

行號(hào)列標(biāo)，滾動(dòng)條，工作表標(biāo)簽的隱藏。選擇“工具”-“選項(xiàng)”-“視圖”，將窗口選

項(xiàng)中的行號(hào)列標(biāo)，水平滾動(dòng)條，垂直滾動(dòng)條，工作表標(biāo)簽選項(xiàng)去掉。

工具復(fù))數(shù)據(jù)Q)窗匚選項(xiàng)|T|x

保護(hù)9?圖表碩色I(xiàn)flMF|詒蜃檢查耕與檢查安全性

視圖……篁新計(jì)算神常換1-2-3的普助自定義序列

方案量)...顯示01*|剪切①

回啟動(dòng)任務(wù)窗柘小)回喇欄9回伏花巖(S)

公式審核@)r>4

?JM?X員?利

。無Of)◎只顯示標(biāo)識(shí)苻IX)OJitilW

規(guī)劃求解案例07匯總

q祜貼位)

。全部顯示Q)O顯示占住將?c全3啊一—

選項(xiàng)Q)…編輯文字9

C日雙8M得G)□行€到標(biāo)電,□水平海t

向?qū)б舱?/p>

J?口原?團(tuán)分級(jí)顯示符號(hào)⑥□垂直褥t組合⑥

叵阿格就⑥回零值②□工作表標(biāo)

數(shù)據(jù)分析CD)...

網(wǎng)格蹣成色①)自動(dòng)V登放次序⑻

指定宏?…L對(duì)于分布

口3設(shè)置控件格式也)

在同一文

件中的表格匯總:

首先創(chuàng)建格式完全一樣的匯總表的表結(jié)構(gòu)

在匯總表須要匯總的項(xiàng)目上輸入：=sum()

將光標(biāo)放入括號(hào)，用鼠標(biāo)選擇第一張工作表，按下shift鍵，再選取最終一張工作表，然

后選擇須要匯總的單元格，回車。

將該公式復(fù)制到其他須要匯總的單元格即可實(shí)現(xiàn)整張表的匯總。

2.對(duì)于分布在不同文件中的表格匯總：

首先創(chuàng)建格式完全一樣的匯總表的表結(jié)構(gòu)

將光標(biāo)置于起始報(bào)表項(xiàng)目上

選擇【數(shù)據(jù)】一【合并計(jì)算】

數(shù)據(jù)也）|窗口/幫助國(guó)）AdomPD

21排序⑤）...~I

篩選9?

有效性|

模擬運(yùn)算表（!）...

分列⑥…|

|合并計(jì)算??...~|

“引用位置”選擇各個(gè)須要被合并的報(bào)表，點(diǎn)擊“添加”，直至全部須要被合并的報(bào)表添

加完成。

須要?jiǎng)h除一張表格時(shí)，只須要在合并計(jì)算界面上選中該表格，點(diǎn)擊刪除按鈕。

當(dāng)子表格數(shù)據(jù)發(fā)生變更須要更新匯總表數(shù)據(jù)時(shí)，只需執(zhí)行：數(shù)據(jù)-合并計(jì)算，進(jìn)入合并計(jì)

算界面后干脆點(diǎn)擊確定退出該界面即可完成刷新數(shù)據(jù)的操作。

案例08報(bào)表愛護(hù)

單元格區(qū)域的愛護(hù)。

首先取消不須要愛護(hù)的單元格區(qū)域的鎖定狀態(tài)。右鍵“設(shè)置單元格格式”，在“愛護(hù)”標(biāo)

簽上將鎖定選項(xiàng)取消。假如須要隱藏單元格公式內(nèi)容，可以將隱藏選項(xiàng)選中。

單元格格式口區(qū)

數(shù)手一II對(duì)齊II字體II邊根IIsag1匚保塞］

E鎖定《)

口隱藏9

只有在工作表被保護(hù)時(shí)，鎖定單元格或隱藏公式才有效。如要保護(hù)工

作表，請(qǐng)選擇“工具”菜單中的“保護(hù)”命令，然后選擇“保護(hù)工作

表”?可以選擇是否加密碼保護(hù).

選擇“工具”-“愛護(hù)”一“愛護(hù)工作表”，輸入愛護(hù)密碼。

工具復(fù))L數(shù)據(jù)①)窗曰?)幫助QpAdobePDF

:呆護(hù)2)匕)國(guó)保護(hù)工作表也)...=

方案量)...我允許用戶編輯區(qū)域3)...

公式審核@)?盟保護(hù)工作簿電).

規(guī)劃求解(V)..二保護(hù)并共享工作簿6)一.

隱藏公式

右鍵菜單一設(shè)置單元格格式一愛護(hù)一選中隱藏選項(xiàng)，然后執(zhí)行對(duì)工作表的愛護(hù)

隱藏工作表:

隱藏工作表后須要愛護(hù)工作簿。

案例09按鈕的制作；案例lOTonaLisa-excel.xls-模塊3(代雞

|M)V

"S曲技的9_單擊0

選擇“視圖”—“工具欄”—“窗體”,EndSub調(diào)出窗體工具條。選擇

工具條上的吩咐按鈕。繪制出按鈕的基本形態(tài)后，自動(dòng)跳出

“指定宏”對(duì)話框，點(diǎn)擊“新建”按鈕，出現(xiàn)VBA編程界面。

在Sub和EndSub之間輸入：sheets（“要跳轉(zhuǎn)的工作表名稱"）.select,關(guān)閉該窗口。

選中該按鈕，右鍵菜單“編輯文字”，可以修改按鈕名稱。

案例10財(cái)務(wù)報(bào)表

利用excel創(chuàng)建財(cái)務(wù)報(bào)表，要留意報(bào)表之間的勾稽關(guān)系。一方面是出于高效率管理報(bào)表的

考慮，另一方面，可以幫助我們分析報(bào)表中的數(shù)據(jù)，并快速檢查出報(bào)表中的可能錯(cuò)誤。

為了能夠自動(dòng)維護(hù)報(bào)表間的數(shù)據(jù)關(guān)系，我們常常須要運(yùn)用單元格鏈接，將全部的表鏈接起

來。留意鏈接方式請(qǐng)運(yùn)用并聯(lián)而非串聯(lián)。串聯(lián)導(dǎo)致鏈接關(guān)系更加困難且難以維護(hù)，而并聯(lián)

的方式將只有一個(gè)統(tǒng)一的數(shù)據(jù)源，鏈接關(guān)系簡(jiǎn)潔不簡(jiǎn)潔出錯(cuò)，而且易于管理。

比如損益表，資產(chǎn)負(fù)債表，現(xiàn)金流量表，須要運(yùn)用公式和鏈接使得這些表鏈接起來，該鏈

接關(guān)系能夠自動(dòng)維護(hù)和更新多表之間的勾稽關(guān)系，時(shí)刻能夠保證報(bào)表數(shù)據(jù)之間的勾稽的正

確性。（具體公式參見電子文檔）。

案例11表格的幾種類型

數(shù)據(jù)表可以分為數(shù)據(jù)列表，二維表，多維表和帶有格式的報(bào)表等兒種類型。

是否與

是否須

數(shù)據(jù)表類數(shù)據(jù)源

操作目標(biāo)匯總方法要重復(fù)

型有鏈接

制作

關(guān)系

記錄疊加

多字段數(shù)導(dǎo)入外部數(shù)據(jù)+

并按表名不須要有

據(jù)列表SQL語句

區(qū)分

多個(gè)二維

透視表多維數(shù)據(jù)區(qū)

二維表表生成透不須要有

域合并

視表

同一文件=sum(begin:end!

數(shù)據(jù)匯總不須要有

內(nèi)的報(bào)表B2)

不同文件

數(shù)據(jù)匯總合并計(jì)算不須要有

中的報(bào)表

建議修改原表的結(jié)

構(gòu)或者僅作為終端

非標(biāo)準(zhǔn)數(shù)

匯總報(bào)表，上層再建立須要無

據(jù)表

一個(gè)數(shù)據(jù)源表作為

收集數(shù)據(jù)運(yùn)用

案例12數(shù)據(jù)透視表

1.應(yīng)用數(shù)據(jù)透視表進(jìn)行各類數(shù)據(jù)分析。

選擇“數(shù)據(jù)”-“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”，進(jìn)入透視表設(shè)置向?qū)А?/p>

數(shù)據(jù)也）|窗口世）幫助量）AdobePD

21排序⑹…

篩選9數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)б?步驟之133

有效性&）...話指定得分析糠的數(shù)據(jù)酸型：

0ffl:crosoft6麗ceExcel額據(jù)更俵或數(shù)據(jù)庫應(yīng)j

模擬運(yùn)算表（：）.?.0蔚凝蕾談…

分列⑥…。多重合并計(jì)算數(shù)珞區(qū)域。

合并計(jì)算國(guó)）...

j湛T數(shù)據(jù)透視表和數(shù)據(jù)透視圖它1二

所需創(chuàng)建的報(bào)祺數(shù)

導(dǎo)入外部數(shù)據(jù)?）?蹦透源Q）

列表9。數(shù)據(jù)透罐國(guó)緘透瞧）@）

XMLQC）

d刷新數(shù)據(jù)國(guó)）

［取消］耳方i：r［下一步?〉?［疑電）?

點(diǎn)擊“下一步”，選擇正確的數(shù)據(jù)范圍。

點(diǎn)擊“下一步”，選擇“布局”按鈕。

在如下圖界面上，將右側(cè)的字段拖入左側(cè)相應(yīng)區(qū)域內(nèi)。

形成如下圖的布局，點(diǎn)擊確定按鈕。

生成如下的透視表后，將鼠標(biāo)懸停在想要移動(dòng)的字段上，鼠標(biāo)左鍵按下，將字段拖放到其

他區(qū)域，生成自己須要的數(shù)據(jù)顯示。

2電:戶名稱（全部）O

3期間（全部）日

4鈞隹員（全部）

b（主部）F1

6

7式和項(xiàng):銷售收入

坤區(qū)G15顯示器17顯六器

101076188728

10_10592691351

112406914172

123683178595

1372651118809

14147605116495

數(shù)據(jù)百分比顯示：右鍵菜單一字段設(shè)置一選項(xiàng),效摳送議笈字段x將數(shù)據(jù)顯示方

潮序注銷皆收入

名稱色）求和項(xiàng)銷西收入

敦?fù)?jù)透視表字段

式改為“占同列數(shù)據(jù)總和的百分

源字段：銷售收入

名稱題-TTMTTF?

比”

數(shù)據(jù)顯示方式達(dá)）

選定⑤?普通

.-一

組及顯示明細(xì)數(shù)據(jù)⑥?最大

值

最小2.同時(shí)顯示

值

乘積

眼序也）?

計(jì)

數(shù)

值

數(shù)

電字段設(shè)置?數(shù)據(jù)與百

表格選項(xiàng)Q）…

顯示數(shù)據(jù)透視表工具欄①分比:

3顯示字段列表?

1）在【布局】里將銷售收入兩次拖入數(shù)據(jù)區(qū)域

2）將其中一個(gè)銷售收入改為百分比顯示

3）在報(bào)表項(xiàng)目上輸入新的名稱可以修改項(xiàng)目名稱

4）將報(bào)表項(xiàng)目拖拽到列標(biāo)題位置，可以將數(shù)值和百分比改為按列排列

3.篩選數(shù)據(jù)：

點(diǎn)擊字段名稱后面的下拉菜單，可以對(duì)字段內(nèi)容進(jìn)行篩選。

4.顯示明細(xì)數(shù)據(jù)：

雙擊須要查看明細(xì)的數(shù)據(jù)，在新的工作表上將顯示構(gòu)成此匯總數(shù)據(jù)的全部明細(xì)數(shù)據(jù)。

5.對(duì)數(shù)據(jù)進(jìn)行排序：

把光標(biāo)放在行位置的報(bào)表項(xiàng)目上，【右鍵菜單】【字段設(shè)置L【高級(jí)】按鈕，左側(cè)可以進(jìn)行

排序設(shè)置。

6.依據(jù)日期分組：

1）將口期放入行區(qū)域，【右健菜單】【組與顯示明細(xì)數(shù)據(jù)】【組合】

2）在【步長(zhǎng)】中選擇須要的分組標(biāo)準(zhǔn)

7.依據(jù)數(shù)值分組：

1）將銷售收入放入行區(qū)域，【右鍵菜單】【組與顯示明細(xì)數(shù)據(jù)】【組合】

2）在【步長(zhǎng)】中選擇須要的分組標(biāo)準(zhǔn)

&插入計(jì)算字段:

數(shù)據(jù)透視表

日設(shè)置報(bào)告格式①）...

別數(shù)據(jù)透視圖C）

回?cái)?shù)據(jù)透視表向?qū)溃?/p>

/刷新數(shù)據(jù)⑥

脫機(jī)03?...

隱藏也）

選定⑤）?

組及顯示明細(xì)數(shù)據(jù)?

L公式?計(jì)算字段電）...

順序d）計(jì)篁項(xiàng)復(fù)）…

字段設(shè)置⑨…求解次序9...

分類匯總@）列出公式1）

插入計(jì)算字段區(qū)|

名稱國(guó)）:

公式也）：

字段但）:

地

區(qū)

產(chǎn)

品

銷

售

員

銷

售

途

徑

銷

皆

收

入

［插入字段量）］

［確定］［關(guān)閉］

在【名稱】后給計(jì)算字段定義名稱

在【公式】后輸入計(jì)算字段的計(jì)算公式，可以引用下面的字段

9.自動(dòng)更新：

【右鍵菜單】【表格選項(xiàng)】【打開時(shí)刷新】

可以讓報(bào)表始終有更新后的數(shù)據(jù)顯示。

10.透視圖

可以單獨(dú)生成數(shù)據(jù)透視圖，也可以基于現(xiàn)有的透視表生成透視圖。假如已經(jīng)有現(xiàn)成的透視

表，通過點(diǎn)擊數(shù)據(jù)透視表工具條上的圖表按鈕可以生成一個(gè)透視圖。

數(shù)據(jù)透視表▼X

數(shù)據(jù)透視表（￡）▼右回L空氣U,?:國(guó):電百

卜“10小?[▼]MCOum[pfQ4E▲Efinx[“paE[

(ACdUCt[▼]

通過拖動(dòng)透視圖上的各個(gè)字段到右側(cè)或者底部的位置，可以便利地變更圖表組織數(shù)據(jù)的方

式。

隱藏透視圖字段按鈕：

把光標(biāo)懸停在隨意字段上，右鍵菜單，選擇【隱藏?cái)?shù)據(jù)透視圖字段按鈕L即可以將字段

按鈕隱藏；

再次顯示字段按鈕：

點(diǎn)擊透視圖工具條上第一項(xiàng)，在出現(xiàn)的下拉菜單中選擇【隱藏?cái)?shù)據(jù)透視圖字段按鈕】

II隱藏?cái)?shù)據(jù)透視圖字段按鈕圖）

公式（M）

J冊(cè)?除字段現(xiàn)）

假如有些數(shù)據(jù)無法在數(shù)據(jù)透視表內(nèi)部分析，可以引用透視表的數(shù)據(jù)作為分析的基礎(chǔ)。

H.制作試算平衡表

月?（全部）"I

科目代碼1“會(huì)計(jì)科目！▼數(shù)據(jù)、匯算

1101現(xiàn)金計(jì)數(shù)項(xiàng):借方金額7

計(jì)數(shù)項(xiàng):貸方全頷R

1101計(jì)數(shù)項(xiàng):借方金額7

1101計(jì)數(shù)項(xiàng):貸方金額8

1102銀行存款-中行（乙存）計(jì)數(shù)項(xiàng):借方金額1

計(jì)數(shù)項(xiàng):貸方金額4

A1B______________CD

1月（全部）

2

3數(shù)據(jù)M

4科目代碼會(huì)計(jì)科目F計(jì)數(shù)頃:借方金薇計(jì)效頊:貸方金額

51101現(xiàn)金18

61101:匚總78

711021配行存款-中行（乙套）14

81102匯總14

911411應(yīng)收票據(jù)85

1C1141匯總86

點(diǎn)擊“數(shù)據(jù)”并拖拽到“匯總”；；卜44匯*"應(yīng)收賬歙

數(shù)據(jù)透視表字段

源字段：借方金額

名稱⑥）：|惜方金額

匯總方式⑤）：

均

值

VT平

大

量

值

小

最

值

積

乘

叱

管

數(shù)

數(shù)

＞隱藏?cái)?shù)據(jù)透視表中的匯總字段

AB

月(全部)卜

科目代碼付］會(huì)計(jì)科目G

11011現(xiàn)金

,■c?：L乂|

11設(shè)置單元格格式d)...

1102匯總數(shù)據(jù)透視困&)

11

窈據(jù)透視表向?qū)L)

1141匯總數(shù)據(jù)透視表字段

11刷新數(shù)據(jù)?

1144匯忠

隋藏回

12酸［確定

1281匯總選定⑹飛

分類匯總

求

組及顯示明細(xì)數(shù)據(jù)電)和

1523匯總

計(jì)

O自動(dòng)9數(shù)

1E順方但)

值

平

均

1531匯總O目定義也)

值

最

-字段設(shè)置國(guó)).(5)無如大

值

最

小

乘

1541匯總表格選項(xiàng)(Q)…積

18

匿藏?cái)?shù)據(jù)透視表工具欄(1)

1821匯總

21B隋藏字段列表

4^□顯示空數(shù)據(jù)項(xiàng)G)

2141匯總或

＞單元格格式自定義功能

儀=SUM(C:C)-SUM(D:D)

A|BCD

40

5月1侄部)卜

6

1數(shù)據(jù)2

■和目代碼卜|會(huì)計(jì)科目?求和項(xiàng):借萬余頷求和項(xiàng):貸方余額

9iioii現(xiàn)金69609.0450

格式設(shè)定為:

［紅色「借貸不平衡”;［紅色「金額借貸不平衡”；［藍(lán)色「借貸平衡”

單元格格式

數(shù)字I對(duì)齊1字體邊電1曝俁護(hù)

分類&):示例

—金額惜貸平衡

類我

專

用1)：

i［藍(lán)色］［＞=0「金額借貨平衡"；［紅色］'

比$#,牡。_);［紅色］($*,**))A

5#,府00_)：($#,#SO03)

記

f數(shù)$#,00k［紅色)($#.##3.00)

陶羚月

yyjcr匯d

yyyy一年?、月"d"日;_~

［崎回］

以現(xiàn)有格式為基礎(chǔ)，生成自定義的數(shù)字格式。

［確定］［取消］

Ctrl+拖拽標(biāo)簽來復(fù)制工作爰年度試篝平衡表腳

編制損益表

＞創(chuàng)建損益表格式

插入U(xiǎn)格式Q）工具

行?

列?

二作衰也）

9特殊符號(hào)四）.?.

分頁符⑹

名稱?

批注如

＞創(chuàng)建IS嵌套函數(shù)

is類函數(shù)，可以檢驗(yàn)數(shù)值的類型并依據(jù)參數(shù)取值返回TRUE或FALSEo

函數(shù)假如為下面的內(nèi)容，則返回TRUE

ISBLA

值為空白單元格。

NK

ISER

值為隨意錯(cuò)誤值（除去#N/A）。

R

ISER值為隨意錯(cuò)誤值（#N/A、#VALUE!＞#REF!、

ROR#DIV/O!>#NUM!>#NAME?或#NULL!）。

ISLO

GICA值為邏輯值。

L

ISNA值為錯(cuò)誤值#N/A（值不存在）。

ISNO

值為不是文本的隨意項(xiàng)（留意此函數(shù)在值為空白單元

NTEX

格時(shí)返回TRUE）o

T

ISNU

值為數(shù)字。

MBER

ISREF值為引用。

ISTEX

值為文本。

T

5類別及科目名稱本期金額比率%

7【業(yè)務(wù)收入】

8銷售收入4=IF（ISERROR|（VLOOKUP（A8,年度試算平衡表,2.

9業(yè)務(wù)收入合計(jì):0））,0,IF（VLOOKUP（A8,年度試算平衡表，2,0）

10>0,-VLOOKUP（A8,年度試算平衡表，2,0）,

LL【業(yè)務(wù)成本】/OOKUP（A8,年度試算平衡表，3,0）））

12進(jìn)貨_|IFQLogical_test,[valuei￡true],[value

13業(yè)務(wù)成本合計(jì):￥75%

＞創(chuàng)建財(cái)務(wù)比率

銷售毛利率=銷售毛利/銷售凈額一一毛利率大，表示經(jīng)營(yíng)實(shí)力強(qiáng)。

銷售成本率=銷售成本/銷售凈額——成本率低，表示經(jīng)營(yíng)實(shí)力好。

凈利率率=凈利潤(rùn)/銷售凈額——