Excel 2007綜合班I

16106班課時(shí)小結(jié)

小結(jié)信息速覽

● 辛勤園丁

講師：函數(shù)菜鳥

助教：李興德、200240

● 培訓(xùn)時(shí)間

2012-07-04（周三)20:00—2012-07-10（周二）21:00

● 主要內(nèi)容

一、利用獲取外部數(shù)據(jù)實(shí)現(xiàn)數(shù)據(jù)透視表的動(dòng)態(tài)鏈接

二、利用多重合并計(jì)算數(shù)據(jù)區(qū)域來(lái)創(chuàng)建數(shù)據(jù)透視表

三、通過(guò)導(dǎo)入外部數(shù)據(jù)“編輯OLE DB查詢”創(chuàng)建數(shù)據(jù)透視表

四、數(shù)據(jù)透視表SQL語(yǔ)句實(shí)例

分頁(yè)符

分頁(yè)符

膩如玉指涂朱粉 光似金刀剪紫霞

——Excel 2007綜合班I課時(shí)小結(jié)之五

終于堅(jiān)持到第五課時(shí)了，前面的內(nèi)容還好，但這節(jié)課受到的打擊很大，第一遍幾乎聽(tīng)不懂，加上天氣極熱，有點(diǎn)急躁，只好靜下心來(lái)，慢慢聽(tīng)，看了一些SQL的參考資料，總算回過(guò)神來(lái)，就像當(dāng)初學(xué)習(xí)VBA一樣，見(jiàn)識(shí)到了另一片天空。

一、利用獲取外部數(shù)據(jù)實(shí)現(xiàn)數(shù)據(jù)透視表的動(dòng)態(tài)鏈接

在前面的課程中，數(shù)據(jù)透視表的操作都是基于同一個(gè)工作簿，數(shù)據(jù)源都是在同一張工作簿的連續(xù)區(qū)域；但是如果對(duì)不同工作表或不同工作簿中的多個(gè)數(shù)據(jù)源進(jìn)行合并匯總或創(chuàng)建數(shù)據(jù)透視表，Excel也同樣提供了解決方案。首先是針對(duì)要變化的外部數(shù)據(jù)，要實(shí)現(xiàn)數(shù)據(jù)透視表的動(dòng)態(tài)鏈接，主要有三種方法，其中通過(guò)OFFSET函數(shù)定義名稱和創(chuàng)建表來(lái)實(shí)現(xiàn)的兩種方法，在第三課時(shí)已經(jīng)學(xué)習(xí)過(guò)，簡(jiǎn)單的復(fù)習(xí)一下，第三種方法是利用獲取外部數(shù)據(jù)實(shí)現(xiàn)動(dòng)態(tài)鏈接。

1、通過(guò)函數(shù)公式定義名稱實(shí)現(xiàn)引用動(dòng)態(tài)數(shù)據(jù)源

將可以實(shí)現(xiàn)動(dòng)態(tài)引用數(shù)據(jù)源的函數(shù)公式定義為名稱，在創(chuàng)建透視表時(shí)數(shù)據(jù)源區(qū)域直接輸入該名稱創(chuàng)建，首先定義名稱data，在引用位置輸入公式：=OFFSET(數(shù)據(jù)源!$A$1,,,COUNTA(數(shù)據(jù)源!$A:$A),COUNTA(數(shù)據(jù)源!$1:$1))，在創(chuàng)建數(shù)據(jù)透視表時(shí)，在表/區(qū)域中直接輸入data即可。如圖

當(dāng)數(shù)據(jù)源增加行/列或是刪除行/列時(shí)，透視表刷新后，自動(dòng)隨之改變數(shù)據(jù)源區(qū)域發(fā)生變化，透視表永遠(yuǎn)跟隨發(fā)生了變化后的數(shù)據(jù)源創(chuàng)建。

此方法要求數(shù)據(jù)源中的首列和首行不包含空單元格，否則將無(wú)法用定義名稱取得正確的數(shù)據(jù)區(qū)域。

2、創(chuàng)建表方式

將數(shù)據(jù)源通過(guò)插入表的方式創(chuàng)建為表后，在創(chuàng)建數(shù)據(jù)透視表，07中表支持行列內(nèi)容的擴(kuò)展－可實(shí)現(xiàn)動(dòng)態(tài)數(shù)據(jù)透視表。如圖

在Excel選項(xiàng)中的“公式”中“在公式中使用表名”勾選可以去掉在選擇數(shù)據(jù)時(shí)出現(xiàn)的“表”。

3、利用獲取外部數(shù)據(jù)實(shí)現(xiàn)數(shù)據(jù)透視表的動(dòng)態(tài)鏈接

在任一張空白工作表中，點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡的“獲取外部數(shù)據(jù)”組中的 “現(xiàn)有連接”命令，如圖

在“現(xiàn)有連接”對(duì)話框中選擇“瀏覽更多”選擇數(shù)據(jù)源所在的工作表，如圖

在“選擇表格”對(duì)話框中選擇數(shù)據(jù)源所在的工作表，一般需要比較規(guī)范的數(shù)據(jù)源，首行都包含了列標(biāo)題，此時(shí)應(yīng)勾選“數(shù)據(jù)首行包含列標(biāo)題”，如圖

點(diǎn)擊“確定”后，在“導(dǎo)入數(shù)據(jù)”對(duì)華軍中，選擇創(chuàng)建數(shù)據(jù)的顯示類型，其中“表”只進(jìn)行簡(jiǎn)單的排序和篩選，后兩種方式可創(chuàng)建數(shù)據(jù)透視表或數(shù)據(jù)透視圖，同時(shí)可以選擇數(shù)據(jù)的放置位置，與前面學(xué)習(xí)的類似，如圖

這樣，可以對(duì)數(shù)據(jù)源進(jìn)行更改，只需刷新數(shù)據(jù)透視表即可實(shí)現(xiàn)動(dòng)態(tài)鏈接。需要注意的是，若數(shù)據(jù)源和創(chuàng)建的數(shù)據(jù)表同時(shí)打開(kāi)時(shí)，如果只對(duì)數(shù)據(jù)源增加行，只需再創(chuàng)建的表中直接右鍵刷新，即可動(dòng)態(tài)更新；但若在數(shù)據(jù)源中增加列，則需要點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡下“連接”組中的“連接”命令，在“工作簿連接”對(duì)話框中選擇數(shù)據(jù)源所在工作簿，點(diǎn)擊“屬性”，在“連接屬性”對(duì)話框中點(diǎn)擊“確定”，即可實(shí)現(xiàn)創(chuàng)建的數(shù)據(jù)表對(duì)增加列的更新，如圖

二、利用多重合并計(jì)算數(shù)據(jù)區(qū)域來(lái)創(chuàng)建數(shù)據(jù)透視表

若數(shù)據(jù)源在兩張或多張共組表中，或在同一張工作表中的不同區(qū)域，若需要對(duì)數(shù)據(jù)合并計(jì)算，可以手工將數(shù)據(jù)放在一起進(jìn)行計(jì)算。但若是數(shù)據(jù)源非常多，顯然非常麻煩，此時(shí)可通過(guò)多重合并計(jì)算數(shù)據(jù)區(qū)域來(lái)創(chuàng)建數(shù)據(jù)透視表。在03版中已經(jīng)介紹過(guò)，但沒(méi)有具體使用，07版中創(chuàng)建數(shù)據(jù)透視表各數(shù)據(jù)透視圖向?qū)е苯芋w現(xiàn)在功能區(qū)，需要重新調(diào)出來(lái)。

1、調(diào)出“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А钡膬煞N方法

（1）通過(guò)自定義快速訪問(wèn)工具欄，在“Excel選項(xiàng)”對(duì)話框的“自定義功能區(qū)”選項(xiàng)卡中，選擇“所有命令”找到“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?，添加到“自定義快速訪問(wèn)工具欄”，可直接激活。

（2）通過(guò)快捷鍵：Alt+D+P，按住Alt鍵，按D鍵松開(kāi)后再按P鍵。

兩種方法調(diào)出“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А保蛇x擇“多重合并計(jì)算數(shù)據(jù)圖區(qū)域”，如圖

2、多重合并計(jì)算數(shù)據(jù)區(qū)域的兩個(gè)具體應(yīng)用

（1）用多重合并計(jì)算數(shù)據(jù)區(qū)域創(chuàng)建數(shù)據(jù)透視表并比較差額

如圖，對(duì)獨(dú)立區(qū)域的數(shù)據(jù)源，要合并且比較差異

以前的方法是將兩個(gè)數(shù)據(jù)區(qū)域合并在一起，同時(shí)添加輔助列以區(qū)分是哪個(gè)區(qū)域的數(shù)據(jù)（表1或表2），利用新表創(chuàng)建數(shù)據(jù)透視表并進(jìn)行數(shù)據(jù)分析。

多重合并數(shù)據(jù)區(qū)域創(chuàng)建的步驟為：首先激活“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А保跀?shù)據(jù)源類型中選擇“多重合并計(jì)算數(shù)據(jù)區(qū)域”，單擊“下一步”，會(huì)出現(xiàn)“頁(yè)字段”的選擇項(xiàng)，這與普通創(chuàng)建方式不同，分為“創(chuàng)建單頁(yè)字段”和“自定義頁(yè)字段”兩種，如圖

創(chuàng)建單頁(yè)字段，選擇數(shù)據(jù)源所在的區(qū)域，可多次選擇數(shù)據(jù)源并添加進(jìn)去，如圖

所有數(shù)據(jù)源添加完成后，下一步選擇數(shù)據(jù)透視表的顯示位置，根據(jù)需要選擇后單擊“完成”，如圖

創(chuàng)建的數(shù)據(jù)透視表，字段會(huì)自動(dòng)合并，生成字段為一個(gè)行、列、值、頁(yè)1的數(shù)據(jù)透視表，如圖

新生成的字段中，行字段是將所選數(shù)據(jù)區(qū)域的首列作為行標(biāo)簽，除首列外，列字段的數(shù)據(jù)僅充當(dāng)一個(gè)列標(biāo)識(shí)，修改成為行標(biāo)簽后就會(huì)失去原有功能而僅作為值進(jìn)行計(jì)算，即除了首列，其它列均為數(shù)據(jù)值，頁(yè)字段中的“頁(yè)1”作為引用所在數(shù)據(jù)區(qū)域的一個(gè)區(qū)分，如圖，“項(xiàng)1”表示第一個(gè)選中的數(shù)據(jù)區(qū)域，如果先選表2區(qū)域，在表示表2區(qū)域。如圖

也可以將頁(yè)1添加到列標(biāo)簽查看或計(jì)算。

自定義頁(yè)字段同樣是添加不同區(qū)域，不過(guò)可指定頁(yè)字段數(shù)目以及為不同區(qū)域選擇一個(gè)字段名，如圖

同時(shí)，創(chuàng)建的數(shù)據(jù)透視表中，頁(yè)字段顯示的就是所指定的字段名，如圖

（2）在多行多列中取不重復(fù)值

多重合并計(jì)算數(shù)據(jù)區(qū)域也有其局限性，即將第一列數(shù)據(jù)作為合并基準(zhǔn)，即使需要合并的數(shù)據(jù)列有多個(gè)，也只會(huì)第一列作為行字段，其它列作為列字段。但也可以通過(guò)這個(gè)特點(diǎn)在多行多列中取不重復(fù)值。如圖

要將上表中的不重復(fù)值取出來(lái)，有很多種方法，如利用函數(shù)等，但列用多重合并計(jì)算數(shù)據(jù)區(qū)域卻非常方便。與以前的不同，多重合并計(jì)算數(shù)據(jù)區(qū)域允許首行首列區(qū)域空白，將值字段放到行標(biāo)簽實(shí)現(xiàn)多行多列取不重復(fù)值。具體操作方法非常簡(jiǎn)單，只要選擇數(shù)據(jù)區(qū)域時(shí)多選擇前面一空白列和上面一空白行即可。

三、通過(guò)導(dǎo)入外部數(shù)據(jù)“編輯OLE DB查詢”創(chuàng)建數(shù)據(jù)透視表

如果說(shuō)前面的這些內(nèi)容還能夠理解的話，后面的內(nèi)容基本上就是天書了，不過(guò)還好功夫不負(fù)有心人，多看幾遍視頻，到論壇中學(xué)習(xí)一下SQL語(yǔ)句的基本知識(shí)，發(fā)現(xiàn)也沒(méi)有那么不能理解嘛。

這是在論壇上和百度上抓的OLE DB的一些簡(jiǎn)單知識(shí)，放在這里自己以后不用再去找了。

OLE DB是Object Linking and EmbeddingDatabase的縮寫，中文為對(duì)象鏈接嵌入數(shù)據(jù)庫(kù)，是微軟為以通過(guò)以統(tǒng)一方式訪問(wèn)不同類型的數(shù)據(jù)存儲(chǔ)設(shè)計(jì)的一種應(yīng)用程序接口，被設(shè)計(jì)為ODBC的一種高級(jí)替代者和繼承者，把它的功能擴(kuò)展到支持到更多種類型的非關(guān)系型數(shù)據(jù)庫(kù)，例如可能不支持SQL的對(duì)象數(shù)據(jù)庫(kù)和電子表格（如Excel）。——總算和Excel有點(diǎn)聯(lián)系了^_^

運(yùn)用OLE DB查詢技術(shù)講不通工作表，甚至多個(gè)工作簿的不同數(shù)據(jù)區(qū)域進(jìn)行合并匯總，以生成動(dòng)態(tài)的數(shù)據(jù)透視表，可避免多重合并數(shù)據(jù)區(qū)域創(chuàng)建的數(shù)據(jù)透視表只能選擇第一列作為行字段的限制。——終于知道為什么要學(xué)這玩意了。

通過(guò)導(dǎo)入外部數(shù)據(jù)時(shí)，編輯OLE DB的查詢方法，借助OLE DB技術(shù)，對(duì)數(shù)據(jù)列表進(jìn)行連接及存儲(chǔ)，以形成新的數(shù)據(jù)源來(lái)創(chuàng)建數(shù)據(jù)透視表。——好像有點(diǎn)懂了！

應(yīng)用導(dǎo)入外部數(shù)據(jù)，結(jié)合OLE DB查詢中使用SQL語(yǔ)句，可以對(duì)不同工作表或不同工作簿中結(jié)構(gòu)相同的數(shù)據(jù)表進(jìn)行匯總，創(chuàng)建動(dòng)態(tài)數(shù)據(jù)透視表，并且可以不受多重合并計(jì)算區(qū)域數(shù)據(jù)透視表只能選擇第一列作為行字段的限制?！呀?jīng)有些迫不及待想看看咋弄的嘞！

1、通過(guò)編輯OLE DB查詢匯總同一工作薄中所有數(shù)據(jù)

對(duì)同一工作簿中的多張結(jié)構(gòu)相同的工作表中的數(shù)據(jù)，若只想對(duì)部分?jǐn)?shù)據(jù)進(jìn)行匯總，同時(shí)要?jiǎng)?chuàng)建分科目、分部門的動(dòng)態(tài)費(fèi)用分析數(shù)據(jù)透視表。當(dāng)然可以將所有的數(shù)據(jù)源合并在一張共組表中，并通過(guò)添加輔助列來(lái)標(biāo)識(shí)數(shù)據(jù)原來(lái)的所在區(qū)域，創(chuàng)建數(shù)據(jù)透視表。還是繁瑣的問(wèn)題，就不多說(shuō)了。

利用SQL語(yǔ)句的方法看似很難，但照葫蘆畫瓢的方法多學(xué)幾次，也很好理解。操作步驟如下：

首先新建一張空白工作表以創(chuàng)建數(shù)據(jù)透視表，單擊“數(shù)據(jù)”選項(xiàng)卡下“獲取外部數(shù)據(jù)”組中的“現(xiàn)有連接”命令，選擇“瀏覽更多”選擇數(shù)據(jù)源所在的工作簿，如圖

單擊“確定”后選擇創(chuàng)建數(shù)據(jù)透視表，同時(shí)單擊“數(shù)據(jù)”選項(xiàng)卡下“連接”組中的“屬性”命令，在“連接屬性”對(duì)話框中的“使用狀況”選項(xiàng)卡下勾選“打開(kāi)文件時(shí)刷新數(shù)據(jù)”或“刷新頻率”并選擇間隔時(shí)間，以實(shí)現(xiàn)數(shù)據(jù)透視表的動(dòng)態(tài)更新。如圖

單擊“定義”選項(xiàng)卡，在“命令文本”中清空原有內(nèi)容，輸入SQL連接語(yǔ)句，如圖

發(fā)現(xiàn)寫SQL語(yǔ)句時(shí)最好還是在文本文檔中編寫好再?gòu)?fù)制進(jìn)去比較好，本例中的SQL語(yǔ)句為：

創(chuàng)建好的數(shù)據(jù)透視表字段列表中含有部門、科目名稱和實(shí)際發(fā)生額三個(gè)字段，其中“部門”是數(shù)據(jù)源中沒(méi)有的，作為每個(gè)數(shù)據(jù)源所在工作表的區(qū)別，通過(guò)將字段拖入相應(yīng)的標(biāo)簽，即可創(chuàng)建數(shù)據(jù)透視表。如圖

2、SQL語(yǔ)句的基本知識(shí)

（1）SQL語(yǔ)句的基本格式

SELECT ? FROM [ $] UNION ALL SELECT ? FROM [$]

SELECT語(yǔ)句用于從數(shù)據(jù)庫(kù)表中獲取部分?jǐn)?shù)據(jù)并用UNION ALL連接在一起，其中英文字母不區(qū)分大小寫，但要在半角狀態(tài)下輸入；“?”表示金提取某幾列數(shù)據(jù)，列字段用半角逗號(hào)隔開(kāi)；除已用半角逗號(hào)隔開(kāi)的外，每個(gè)字符后均有空格；[ ]內(nèi)輸入的是工作表名稱，名稱后需添加“$”以區(qū)別是是絕對(duì)引用還是相對(duì)引用；SELECT后的數(shù)據(jù)須一一對(duì)應(yīng)，若沒(méi)有，可用NULL代替。

（2）操作方法

單擊“數(shù)據(jù)”選項(xiàng)卡下“連接”組中的“屬性”命令，在“連接屬性”對(duì)話框中的“定義”選項(xiàng)卡下的“命令文本”中輸入SQL語(yǔ)句。

單一表數(shù)據(jù)：

多個(gè)表數(shù)據(jù)：

添加字段項(xiàng)：

此時(shí)“表名1”、“表名2”所屬的字段名自動(dòng)生成為Expr1000。

添加字段項(xiàng)并修改字段名稱：

全部選取所有的字段信息：

3、利用數(shù)據(jù)透視表對(duì)列數(shù)不等的數(shù)據(jù)源表進(jìn)行多表合并

對(duì)列數(shù)不等的兩張表進(jìn)行合并，如圖

以上兩表分別為8列和6列數(shù)據(jù)，若要將兩表數(shù)據(jù)合并，需在“命令文本”中輸入以下SQL語(yǔ)句：

這段語(yǔ)句是分別從“采購(gòu)”、“付款”兩張數(shù)據(jù)表中獲取“金額”、“付款金額”、“日期”、“材料名稱”、“供應(yīng)商”五個(gè)字段，把“采購(gòu)”表中的字段名“金額”和“付款”表中的字段名“付款金額”用AS分別自定義為新字段名“應(yīng)付金額”和“已付金額”，并把“采購(gòu)”表中所缺的“已付金額”和“付款”表中所缺的“應(yīng)付金額”通過(guò)AS自定義添加，字段賦值為“NULL”。

之后可創(chuàng)建空白的數(shù)據(jù)透視表，通過(guò)相應(yīng)的設(shè)置及添加計(jì)算字段，可以得到不等列數(shù)據(jù)源創(chuàng)建的“應(yīng)付賬款分析”的數(shù)據(jù)透視表。

四、數(shù)據(jù)透視表SQL語(yǔ)句實(shí)例

1、通過(guò)編輯OLE DB查詢實(shí)現(xiàn)員工分類統(tǒng)計(jì)

三種方法：一是通過(guò)多次組合后連接在一起，二是添加輔助列，增加“部門”、“學(xué)歷”、“年齡區(qū)間”，再添加一列“次分類”加入對(duì)應(yīng)的“部門”、“年齡分段”、“學(xué)歷層次”。

第三種方法是SQL語(yǔ)句

2、利用編輯OLE DB查詢創(chuàng)建動(dòng)態(tài)原材料收發(fā)存匯總表

將數(shù)據(jù)源規(guī)范后，使用SQL編寫：

添加計(jì)算項(xiàng)：結(jié)存=期初+入庫(kù)-出庫(kù)

若出現(xiàn)數(shù)據(jù)位數(shù)有效性的問(wèn)題，應(yīng)事先修改數(shù)據(jù)透視表中數(shù)據(jù)的單元格格式。

3、查找復(fù)雜數(shù)據(jù)表之間的差異

利用SQL語(yǔ)句自動(dòng)生成字段以比較采購(gòu)與付款清單。

總算堅(jiān)持寫完了最后一課的小結(jié)，對(duì)SQL的SELECT語(yǔ)句的基本格式有了基本的了解。在工作中確實(shí)有很多需要用到的地方，近半年來(lái)通過(guò)不斷的學(xué)習(xí)，極大提高了自己的工作效率。

感謝木蘭老師，感謝兩位班主任！

愛(ài)華網(wǎng)本文地址 » http://www.klfzs.com/a/25101016/307202.html