多個(gè)Excel表的合并計(jì)算優(yōu)質(zhì)資料

多個(gè)Excel表的合并計(jì)算優(yōu)質(zhì)資料(可以直接使用，可編輯優(yōu)質(zhì)資料，歡迎下載）

多個(gè)Excel表的合并計(jì)算多個(gè)Excel表的合并計(jì)算優(yōu)質(zhì)資料(可以直接使用，可編輯優(yōu)質(zhì)資料，歡迎下載）一、Excel

建立合并計(jì)算一個(gè)公司內(nèi)可能有很多的銷售地區(qū)或者分公司，各個(gè)分公司具有各自的銷售報(bào)表和會(huì)計(jì)報(bào)表，為了對整個(gè)公司的所有情況進(jìn)行全面的了解，就要將這些分散的數(shù)據(jù)進(jìn)行合并，從而得到一份完整的銷售統(tǒng)計(jì)報(bào)表或者會(huì)計(jì)報(bào)表。在Excel中系統(tǒng)提供了合并計(jì)算的功能，來完成這些匯總工作。

所謂合并計(jì)算是指，可以通過合并計(jì)算的方法來匯總一個(gè)或多個(gè)源區(qū)中的數(shù)據(jù)。MicrosoftExcel提供了三種合并計(jì)算數(shù)據(jù)的方法。一是通過公式，對于所有類型或排列的數(shù)據(jù)，推薦使用公式中的三維引用。二是通過位置，如果要合并幾個(gè)區(qū)域中相同單元格里的數(shù)據(jù)，可以根據(jù)位置進(jìn)行合并。三是通過分類，如果包含幾個(gè)具有不同布局的區(qū)域，并且計(jì)劃合并來自包含匹配標(biāo)志的行或列中的數(shù)據(jù)，可以根據(jù)分類進(jìn)行合并。

要想合并計(jì)算數(shù)據(jù)，首先必須為匯總信息定義一個(gè)目的區(qū)，用來顯示摘錄的信息。此目標(biāo)區(qū)域可位于與源數(shù)據(jù)相同的工作表上，或在另一個(gè)工作表上或工作簿內(nèi)。其次，需要選擇要合并計(jì)算的數(shù)據(jù)源。此數(shù)據(jù)源可以來自單個(gè)工作表、多個(gè)工作表或多重工作簿中。

在Excel2000中，可以最多指定255個(gè)源區(qū)域來進(jìn)行合并計(jì)算。在合并計(jì)算時(shí)，不需要打開包含源區(qū)域的工作簿。1、通過公式來合并計(jì)算數(shù)據(jù)1）在合并計(jì)算工作表上，復(fù)制或輸入待合并計(jì)算數(shù)據(jù)的標(biāo)志。2）單擊用來存放合并計(jì)算數(shù)據(jù)的單元格。3）鍵入合并計(jì)算公式，公式中的引用應(yīng)指向每張工作表中包含待合并數(shù)據(jù)的源單元格。例如，若要合并工作表2到工作表7的單元格B3中的數(shù)據(jù)，請鍵入“=SUM(Sheet2:Sheet7!B3)”。如果要合并的數(shù)據(jù)在不同的工作表的不同單元格中，請輸入公式“=SUM(Sheet3!B4,Sheet4!A7,Sheet5!C5)”。如果不想使用鍵入的方式在公式中輸入引用如Sheet3!B4，請?jiān)谛枰褂靡锰庢I入公式，單擊工作表選項(xiàng)卡，再單擊單元格。2、通過位置來合并計(jì)算數(shù)據(jù)

通過位置來合并計(jì)算數(shù)據(jù)是指：在所有源區(qū)域中的數(shù)據(jù)被相同地排列，也就是說想從每一個(gè)源區(qū)域中合并計(jì)算的數(shù)值必須在被選定源區(qū)域的相同的相對位置上。這種方式非常適用于我們處理日常相同表格的合并工作，例如，總公司將各分公司的合并形成一個(gè)整個(gè)公司的報(bào)表。再如，稅務(wù)部門可以將不同地區(qū)的稅務(wù)報(bào)表合并形成一個(gè)市的總稅務(wù)報(bào)表等等。下面我們以一個(gè)實(shí)例來說明這一操作過程，建立如圖11-1所示的工作簿文件。在本例中我們將對工作表濟(jì)南、天津、青島各區(qū)進(jìn)行合并計(jì)算形成一個(gè)整個(gè)公司的報(bào)表。執(zhí)行步驟如下：

（1）為合并計(jì)算的數(shù)據(jù)選定目的區(qū)，如圖11-2所示。

（2）執(zhí)行“數(shù)據(jù)”菜單中的“合并計(jì)算”命令，出現(xiàn)一個(gè)如圖11-3的對話框。

（3）在“函數(shù)”框中，選定我們希望MicrosoftExcel用來合并計(jì)算數(shù)據(jù)的匯總函數(shù)，求和（SUM）函數(shù)是默認(rèn)的函數(shù)。

（4）在“引用位置”框中，輸入希望進(jìn)行合并計(jì)算的源區(qū)的定義。如果想不擊鍵就輸入一個(gè)源區(qū)域定義，先選定“引用位置”框，然后在工作表選項(xiàng)卡上單擊“濟(jì)南”，在工作表中選定源區(qū)域。該區(qū)域的單元格引用將出現(xiàn)在“引用位置”框中，如圖11-4所示。

（5）按下“添加”按鈕。對要進(jìn)行合并計(jì)算的所有源區(qū)域重復(fù)上述步驟?？梢钥吹胶喜⒂?jì)算對話框如圖11-5所示。

最后按下“確定”按鈕。我們就可以看到合并計(jì)算的結(jié)果，如圖11-6所示。

（6）對于合并計(jì)算，我們還可以將保存在不同工作簿中的工作表進(jìn)行合并操作，其操作步驟是在我們執(zhí)行上述步驟2時(shí)，如果需要的工作表沒有被打開，選擇“瀏覽”按鈕，之后出現(xiàn)一個(gè)如圖11-7的瀏覽對話框，我們可以從中選定包含源區(qū)域的工作簿。

當(dāng)我們選擇了“確定”按鈕后，MicrosoftExcel將在（引用位置）框中插入文件名和一個(gè)感嘆號(hào)(!)。下一步我們可以鍵入單元格引用或源區(qū)域的名字，也可以為源區(qū)域鍵入全路徑，工作簿名字和工作表名字。然后在工作表名字后面鍵入一個(gè)感嘆號(hào)(!)，并鍵入單元格引用或源區(qū)域的名字。

如果不能確定單元格引用區(qū)域，也可以先將該工作簿文件打開，然后利用“窗口”菜單，選擇該工作簿文件，按上例中的步驟3完成選定工作。

重復(fù)上例中的步驟2到5，最后按下“確定”按鈕完成合并計(jì)算的工作。圖11-8顯示了一個(gè)引用不同工作簿的實(shí)例。

3、通過分類來合并計(jì)算數(shù)據(jù)

通過分類來合并計(jì)算數(shù)據(jù)是指：當(dāng)多重來源區(qū)域包含相似的數(shù)據(jù)卻以不同方式排列時(shí)，此命令可使用標(biāo)記，依不同分類進(jìn)行數(shù)據(jù)的合并計(jì)算，也就是說，當(dāng)選定的格式的表格具有不同的內(nèi)容時(shí)，我們可以根據(jù)這些表格的分類來分別進(jìn)行合并工作。舉例來說，假設(shè)某公司共有兩個(gè)分公司，它們分別銷售不同的產(chǎn)品，如圖11-9的顯示，總公司要得到完整的銷售報(bào)表時(shí)，就必須使用“分類”來合并計(jì)算數(shù)據(jù)。

下面我們就以匯總這兩個(gè)分公司的銷售報(bào)表事例來說明這一操作過程。在本例中我們將對工作簿XS1、XS2進(jìn)行合并操作，其結(jié)果保存在工作簿XS3中，執(zhí)行步驟如下：

（1）為合并計(jì)算的數(shù)據(jù)選定目的區(qū)。執(zhí)行“數(shù)據(jù)”菜單中的“合并計(jì)算”命令，出現(xiàn)一個(gè)合并計(jì)算對話框。在“函數(shù)”框中，選定用來合并計(jì)算數(shù)據(jù)的匯總函數(shù)。求和（SUM）函數(shù)是默認(rèn)的函數(shù)。

（2）在“引用位置”框中，輸入希望進(jìn)行合并計(jì)算的源區(qū)的定義。如果想不擊鍵就輸入一個(gè)源區(qū)域定義，先選定“引用位置”框，然后在“窗口”菜單下，選擇該工作簿文件，在工作表中選定源區(qū)域。該區(qū)域的單元格引用將出現(xiàn)在“引用位置”框中。

對要進(jìn)行合并計(jì)算的所有源區(qū)域重復(fù)上述步驟。

如果源區(qū)域頂行有分類標(biāo)記，則選定在“標(biāo)題位置”下的“首行”復(fù)選框。如果源區(qū)域左列有分類標(biāo)記，則選定“標(biāo)題位置”下的“最左列”復(fù)選框。在一次合并計(jì)算中，可以選定兩個(gè)選擇框。在本例中我們選擇“最左列”選項(xiàng)，如圖11-10所示。

按下“確定”按鈕。我們就可以看到合并計(jì)算的結(jié)果，如圖11-11所示。

4、合并計(jì)算的自動(dòng)更新

此外，我們還可以利用鏈接功能來實(shí)現(xiàn)表格的自動(dòng)更新。也就是說，如果我們希望當(dāng)源數(shù)據(jù)改變時(shí)，MicrosoftExcel會(huì)自動(dòng)更新合并計(jì)算表。要實(shí)現(xiàn)該功能的操作是，在“合并計(jì)算”對話框中選定“鏈接到源”復(fù)選框，選定后在其前面的方框中會(huì)出現(xiàn)一個(gè)“√”符號(hào)。這樣，當(dāng)每次更新源數(shù)據(jù)時(shí)，我們就不必都要再執(zhí)行一次“合并計(jì)算”命令。還應(yīng)注意的是：當(dāng)源和目標(biāo)區(qū)域在同一張工作表時(shí)，是不能夠建立鏈接的。二、更改多個(gè)Excel文檔數(shù)據(jù)合并計(jì)算對來自多個(gè)Excel工作表中的數(shù)據(jù)進(jìn)行合并計(jì)算后，可能需要更改對數(shù)據(jù)進(jìn)行合并計(jì)算的方式。例如，可能要添加新的地區(qū)辦事處的工作表，或刪除不再存在的部門的工作表，或更改帶有三維引用(三維引用：對跨越工作簿中兩個(gè)或多個(gè)工作表的區(qū)域的引用。)的公式。1、更改按位置或分類進(jìn)行的合并計(jì)算注釋只有當(dāng)您以前未在“合并計(jì)算”對話框內(nèi)選中“創(chuàng)建連至源數(shù)據(jù)的鏈接”復(fù)選框的情況下，才能更改合并計(jì)算。如果選中該復(fù)選框，請單擊“關(guān)閉”，然后重新創(chuàng)建合并計(jì)算。1）單擊合并計(jì)算數(shù)據(jù)的左上角單元格。2）在“數(shù)據(jù)”選項(xiàng)卡的“數(shù)據(jù)工具”組中，單擊“合并計(jì)算”。3）請執(zhí)行下列一項(xiàng)或多項(xiàng)操作：·為合并計(jì)算添加源區(qū)域新的源區(qū)域必須在相同位置中有數(shù)據(jù)(如果以前按位置進(jìn)行合并計(jì)算)，或者有與合并計(jì)算中其他區(qū)域內(nèi)的那些列標(biāo)簽匹配的列標(biāo)簽(如果以前按分類進(jìn)行合并計(jì)算)。A如果工作表在另一個(gè)工作簿中，請單擊“瀏覽”找到文件，然后單擊“確定”以關(guān)閉“瀏覽”對話框。在“引用”框中輸入后跟感嘆號(hào)的文件路徑。B鍵入為區(qū)域指定的名稱，然后單擊“添加”。·調(diào)整源區(qū)域的大小或形狀A(yù)在“所有引用位置”下，單擊要更改的源區(qū)域。B在“引用”框中，編輯所選引用。C單擊“添加”。·從合并計(jì)算中刪除源區(qū)域A在“所有引用位置”中，單擊要?jiǎng)h除的源區(qū)域。B單擊“刪除”。·自動(dòng)更新合并計(jì)算要點(diǎn)：只有當(dāng)該工作表位于其他工作簿中時(shí)，才能選中此復(fù)選框。一旦選中此復(fù)選框，則不能對在合并計(jì)算中包括哪些單元格和區(qū)域進(jìn)行更改。4）若要更新合并計(jì)算，請單擊“確定”。2、更改按公式進(jìn)行的合并計(jì)算通過編輯公式(例如，更改函數(shù)或表達(dá)式)，可以更改按公式的合并計(jì)算。對于單元格引用，可以執(zhí)行下列操作：添加、更改或刪除對其他工作表的單元格引用。例如，若要添加對在營銷部工作表后面插入的供應(yīng)部工作表中的單元格G3的引用，需要按以下示例編輯公式。之前：之后：（二）用EXCEL的統(tǒng)計(jì)函數(shù)進(jìn)行統(tǒng)計(jì)卡方檢驗(yàn)（χ2）卡方（χ2）常用以檢驗(yàn)兩個(gè)或兩個(gè)以上樣本率或構(gòu)成比之間差別的顯著性分析，用以說明兩類屬性現(xiàn)象之間是否存在一定的關(guān)系?？ǚ綑z驗(yàn)常采用四格表，如圖5-4-18所示，比較的A、B兩組數(shù)據(jù)分別用a、b、c、d表示，a為A組的陽性例數(shù)，b為A組的陰性例數(shù)，c為B組的陽性例數(shù)，d為B組的陰性例數(shù)。用EXCEL進(jìn)行卡方檢驗(yàn)時(shí)，數(shù)據(jù)的輸入方式按實(shí)際值和理論值分別輸入四個(gè)單元格，如圖5-4-18所示。（1）比較的A、B兩組數(shù)據(jù)分別用a、b、c、d表示。a=52，為A組的陽性例數(shù)；b=19，為A組的陰性例數(shù)；c=39，為B組的陽性例數(shù)；d=3，為B組的陰性例數(shù)。根據(jù)公式計(jì)算理論值T11、T12、、T21和T22。將實(shí)際值和理論值分別輸入如圖所示的四個(gè)單元格（圖5-4-19）。選擇表的一空白單元格，存放概率p值的計(jì)算結(jié)果，將鼠標(biāo)器移至工具欄的“fx”處，鼠標(biāo)器左鍵點(diǎn)擊工具欄的“fx”快捷鍵，打開函數(shù)選擇框。（2）在函數(shù)選擇框的“函數(shù)分類”欄選擇“統(tǒng)計(jì)”項(xiàng)，然后在“函數(shù)名”欄內(nèi)選擇“CHITEST”函數(shù)，用鼠標(biāo)器點(diǎn)擊“確定”按鈕，打開數(shù)據(jù)輸入框（圖5-4-20）。（3）在“Actual_range”項(xiàng)的輸入框內(nèi)輸入實(shí)際值（a、b、c、d）的起始單元格和結(jié)束單元格的行列號(hào)，在“Expected_range”項(xiàng)的輸入框內(nèi)輸入理論值（T11、T12、T21、T22）的起始單元格和結(jié)束單元格的行列號(hào)，起始單元格和結(jié)束單元格的行列號(hào)之間用“:”分隔（圖5-4-20）。在數(shù)據(jù)輸入完畢后，p值的計(jì)算結(jié)果立即顯示。用鼠標(biāo)器點(diǎn)擊“確定”按鈕，觀察計(jì)算結(jié)果。

圖5-4-18四格表圖5-4-19四格表數(shù)據(jù)輸入

圖5-4-20計(jì)算選擇框

圖5-4-21

p值計(jì)算結(jié)果4．在表存放概率p值的空白單元格處顯示p值的計(jì)算結(jié)果。在“編輯”欄處顯示χ2檢驗(yàn)的函數(shù)“CHITEST”及兩組比較數(shù)據(jù)的起始與結(jié)束單元格的行列號(hào)（圖5-4-21）。

在日常工作中，大家有時(shí)會(huì)遇到過這樣的情況：使用Excel編制的報(bào)表、表格、程序等，在單元格中設(shè)置了公式、函數(shù)等，為了防止其他人修改您的設(shè)置或者防止您自己無意中修改，您可能會(huì)使用Excel的工作表保護(hù)功能，但時(shí)間久了保護(hù)密碼容易忘記，這該怎么辦？有時(shí)您從網(wǎng)上下載的Excel格式的小程序，您想修改，但是作者加了工作表保護(hù)密碼，怎么辦？您只要按照以下步驟操作，Excel工作表保護(hù)密碼瞬間(稍微有點(diǎn)夸張)即破！工具/原料

待破解的Excel工作表方法/步驟1打開您需要破解的Excel文件，如圖所示：2依次點(diǎn)擊菜單欄上的工具---宏----錄制新宏；3輸入宏名字（PS：打擊積極性?。。┤?易爾拓，然后停止錄制(這樣得到一個(gè)空宏，為后面添加vb代碼做準(zhǔn)備)；4依次點(diǎn)擊菜單欄上的工具---宏----宏,選易爾拓,點(diǎn)編輯按鈕；

刪除窗口中的所有字符,替換為下面的內(nèi)容；OptionExplicitPublicSubAllInternalPasswords()

'Breaksworksheetandworkbookstructurepasswords.BobMcCormick

'probablyoriginatorofbasecodealgorithmmodifiedforcoverage

'ofworkbookstructure/windowspasswordsandformultiplepasswords

'

'NormanHarkerandJEMcGimpsey27-Dec-2002(Version1.1)

'Modified2003-Apr-04byJEM:Allmsgstoconstants,and

'eliminateoneExitSub(Version1.1.1)

'RevealshashedpasswordsNOToriginalpasswords

ConstDBLSPACEAsString=vbNewLine&vbNewLine

ConstAUTHORSAsString=DBLSPACE&vbNewLine&_

"AdaptedfromBobMcCormickbasecodeby"&_

"NormanHarkerandJEMcGimpsey"

ConstHEADERAsString="AllInternalPasswordsUserMessage"

ConstVERSIONAsString=DBLSPACE&"Version1.1.12003-Apr-04"

ConstREPBACKAsString=DBLSPACE&"Pleasereportfailure"&_

"tothegrammingnewsgroup."

ConstALLCLEARAsString=DBLSPACE&"Theworkbookshould"&_

"nowbefreeofallpasswordprotection,somakesureyou:"&_

DBLSPACE&"SAVEITNOW!"&DBLSPACE&"andalso"&_

DBLSPACE&"BACKUP!,BACKUP!!,BACKUP!!!"&_

DBLSPACE&"Also,rememberthatthepasswordwas"&_

"putthereforareason.Don'tstuffupcrucialformulas"&_

"ordata."&DBLSPACE&"Accessanduseofsomedata"&_

"maybeanoffense.Ifindoubt,don't."

ConstMSGNOPWORDS1AsString="Therewerenopasswordson"&_

"sheets,orworkbookstructureorwindows."&AUTHORS&VERSION

ConstMSGNOPWORDS2AsString="Therewasnoprotectionto"&_

"workbookstructureorwindows."&DBLSPACE&_

"Proceedingtounprotectsheets."&AUTHORS&VERSION

ConstMSGTAKETIMEAsString="AfterpressingOKbuttonthis"&_

"willtakesometime."&DBLSPACE&"Amountoftime"&_

"dependsonhowmanydifferentpasswords,the"&_

"passwords,andyourcomputer'sspecification."&DBLSPACE&_

"Justbepatient!Makemeacoffee!"&AUTHORS&VERSION

ConstMSGPWORDFOUND1AsString="YouhadaWorksheet"&_

"StructureorWindowsPasswordset."&DBLSPACE&_

"Thepasswordfoundwas:"&DBLSPACE&"$$"&DBLSPACE&_

"Noteitdownforpotentialfutureuseinotherworkbooksby"&_

"thesamepersonwhosetthispassword."&DBLSPACE&_

"Nowtocheckandclearotherpasswords."&AUTHORS&VERSION

ConstMSGPWORDFOUND2AsString="YouhadaWorksheet"&_

"passwordset."&DBLSPACE&"Thepasswordfoundwas:"&_

DBLSPACE&"$$"&DBLSPACE&"Noteitdownforpotential"&_

"futureuseinotherworkbooksbysamepersonwho"&_

"setthispassword."&DBLSPACE&"Nowtocheckandclear"&_

"otherpasswords."&AUTHORS&VERSION

ConstMSGONLYONEAsString="Onlystructure/windows"&_

"protectedwiththepasswordthatwasjustfound."&_

ALLCLEAR&AUTHORS&VERSION&REPBACK

Dimw1AsWorksheet,w2AsWorksheet

DimiAsInteger,jAsInteger,kAsInteger,lAsInteger

DimmAsInteger,nAsInteger,i1AsInteger,i2AsInteger

Dimi3AsInteger,i4AsInteger,i5AsInteger,i6AsInteger

DimPWord1AsString

DimShTagAsBoolean,WinTagAsBooleanApplication.ScreenUpdating=False

WithActiveWorkbook

WinTag=.ProtectStructureOr.ProtectWindows

EndWith

ShTag=False

ForEachw1InWorksheets

ShTag=ShTagOrw1.ProtectContents

Nextw1

IfNotShTagAndNotWinTagThen

MsgBoxMSGNOPWORDS1,vbInformation,HEADER

ExitSub

EndIf

MsgBoxMSGTAKETIME,vbInformation,HEADER

IfNotWinTagThen

MsgBoxMSGNOPWORDS2,vbInformation,HEADER

Else

OnErrorResumeNext

Do'dummydoloop

Fori=65To66:Forj=65To66:Fork=65To66

Forl=65To66:Form=65To66:Fori1=65To66

Fori2=65To66:Fori3=65To66:Fori4=65To66

Fori5=65To66:Fori6=65To66:Forn=32To126

WithActiveWorkbook

.UnprotectChr(i)&Chr(j)&Chr(k)&_

Chr(l)&Chr(m)&Chr(i1)&Chr(i2)&_

Chr(i3)&Chr(i4)&Chr(i5)&Chr(i6)&Chr(n)

If.ProtectStructure=FalseAnd_

.ProtectWindows=FalseThen

PWord1=Chr(i)&Chr(j)&Chr(k)&Chr(l)&_

Chr(m)&Chr(i1)&Chr(i2)&Chr(i3)&_

Chr(i4)&Chr(i5)&Chr(i6)&Chr(n)

MsgBoxApplication.Substitute(MSGPWORDFOUND1,_

"$$",PWord1),vbInformation,HEADER

ExitDo'Bypassallfor...nexts

EndIf

EndWith

Next:Next:Next:Next:Next:Next

Next:Next:Next:Next:Next:Next

LoopUntilTrue

OnErrorGoTo0

EndIf

IfWinTagAndNotShTagThen

MsgBoxMSGONLYONE,vbInformation,HEADER

ExitSub

EndIf

OnErrorResumeNext

ForEachw1InWorksheets

'AttemptclearancewithPWord1

w1.UnprotectPWord1

Nextw1

OnErrorGoTo0

ShTag=False

ForEachw1InWorksheets

'ChecksforallclearShTagtriggeredto1ifnot.

ShTag=ShTagOrw1.ProtectContents

Nextw1

IfShTagThen

ForEachw1InWorksheets

Withw1

If.ProtectContentsTh