EXCEL2007舉例

動(dòng)態(tài)合并幾張工作表到一張合并表，合并表數(shù)據(jù)隨明細(xì)表變動(dòng)而變動(dòng)。

舉例數(shù)據(jù)如下圖，3張表明細(xì)表，分別是“一”“二”“三”，將3張表動(dòng)態(tài)合并到最后一張“合并”表中。

●●●

表“二”、表“三”和表“一”格式類似。

1、公式菜單 → 名稱管理器

●●●

2、新建 → 在“名稱”右邊的輸入框內(nèi)輸入：yi → 在“引用位置”右邊的輸入框內(nèi)輸入：

=OFFSET(一!$A$1,ROW()-1,COLUMN()-1,,)

→ 點(diǎn)擊：確定。如下圖。

●●●

公式解析：

OFFSET 是定位引用函數(shù)

語(yǔ)法結(jié)構(gòu)為：

OFFSET(定位起點(diǎn)，上下偏移行數(shù)，左右偏移列數(shù)，返回引用的行數(shù)，返回引用的列數(shù))

ROW 函數(shù)返回行號(hào)，省略參數(shù)則返回公式所在單元格行號(hào)；

CLOUMN 函數(shù)返回列號(hào)，省略參數(shù)則返回公式所在單元格列號(hào)。

本例中的OFFSET

■ OFFSET定位起點(diǎn)是：一!$A$1，即表“一”的單元格A1

■ 向下偏移ROW()-1 返回的行數(shù)

■ 向右偏移COLUMN()-1 返回的列數(shù)

■ OFFSET省略最后2個(gè)參數(shù)，則返回引用的行數(shù)和列數(shù)默認(rèn)和第1參數(shù)一樣，即1行和1列。

■ 把這個(gè)公式復(fù)制粘貼到其他表格單元格A1，作用相當(dāng)于拷貝表“一”的數(shù)據(jù)到其他表格，從表“一”的A1開(kāi)始拷貝，公式粘貼到哪個(gè)單元格，就拷貝出表“一”對(duì)應(yīng)單元格的內(nèi)容。

3、新建 → 在“名稱”右邊的輸入框內(nèi)輸入：yia → 在“引用位置”右邊的輸入框內(nèi)輸入：

=OFFSET(一!$A$1,ROW()-1,,,)

→ 點(diǎn)擊：確定。如下圖。

●●●

這個(gè)OFFSET公式與上面第2步相比，不光省略后面2個(gè)參數(shù)，還省略了第3參數(shù)，即左右不偏移，只返回表“一”A列的值。

4、新建 → 在“名稱”右邊的輸入框內(nèi)輸入：er → 在“引用位置”右邊的輸入框內(nèi)輸入：

=OFFSET(二!$A$1,ROW()-1-COUNTA(一!$A:$A),COLUMN()-1,,)

→ 點(diǎn)擊：確定。如下圖。

●●●

這個(gè)OFFSET公式和第2步名稱：yi 定義的公式類似，不同之處是第2參數(shù)：

ROW()-1-COUNTA(一!$A:$A)

■ COUNTA是計(jì)數(shù)函數(shù)

■ COUNTA(一!$A:$A)公式返回的值是表“一”A列有數(shù)據(jù)的行數(shù)

為什么用ROW()返回的行號(hào)減去表“一”A列有數(shù)據(jù)的行數(shù)
■ 因?yàn)椤昂喜ⅰ北戆聪缺怼耙弧?、然后表“二”、最后表“三”的順序合并，我們把表“二”?shù)據(jù)排在表“一”的后面，這時(shí)的ROW() = 表“一”的有數(shù)據(jù)的行數(shù) + 表“二”的行號(hào)

■ 用OFFSET公式引用表“二”數(shù)據(jù)到合并表時(shí)，OFFSET的第2參數(shù)要用ROW()減去表“一”的有數(shù)據(jù)的行數(shù)

■ 以此類推，后面表“三”要用ROW()減去表“一”和表“二”的行數(shù)

5、新建 → 在“名稱”右邊的輸入框內(nèi)輸入：era → 在“引用位置”右邊的輸入框內(nèi)輸入：

=OFFSET(二!$A$1,ROW()-1-COUNTA(一!$A:$A),,,)

→ 點(diǎn)擊：確定。如下圖。

●●●

這個(gè)OFFSET公式與上面第4步公式相比，省略了第3參數(shù)，即左右不偏移，只返回表“二”A列的值。

6、新建 → 在“名稱”右邊的輸入框內(nèi)輸入：san → 在“引用位置”右邊的輸入框內(nèi)輸入：

=OFFSET(三!$A$1,ROW()-1-COUNTA(一!$A:$A)-COUNTA(二!$A:$A),COLUMN()-1,,)

→ 點(diǎn)擊：確定。如下圖。

●●●

在第4步已經(jīng)詳細(xì)解釋，這里不贅述。

7、新建 → 在“名稱”右邊的輸入框內(nèi)輸入：sana → 在“引用位置”右邊的輸入框內(nèi)輸入：

=OFFSET(三!$A$1,ROW()-1-COUNTA(一!$A:$A)-COUNTA(二!$A:$A),,,)

→ 點(diǎn)擊：確定。如下圖。

●●●

這個(gè)OFFSET公式，省略了第3參數(shù)，即左右不偏移，只返回表“三”A列的值。

8、'合并'表單元格A1輸入如下公式，按回車并向下向右復(fù)制完成（向右復(fù)制到E列）。

=IF(yia<>'',yi,IF(era<>'',er,IF(sana<>'',san,'')))

如下圖。

●●●

這是一個(gè)IF嵌套公式，判斷表“一”A列是否有數(shù)據(jù)，如有數(shù)據(jù)則返回表“一”的數(shù)據(jù)，否則返回另一個(gè)IF公式：

IF(era<>'',er,IF(sana<>'',san,''))

判斷表“二”A列是否有數(shù)據(jù)，如有數(shù)據(jù)則返回表“二”的數(shù)據(jù)，否則返回另一個(gè)IF公式：

IF(sana<>'',san,'')

判斷表“三”A列是否有數(shù)據(jù)，如有數(shù)據(jù)則返回表“三”的數(shù)據(jù)，否則返回空單元格“”

9、'合并'表單元格F2輸入如下公式，按回車并向下復(fù)制完成。

=IF(yia<>'','表一',IF(era<>'','表二',IF(sana<>'','表三','')))

如下圖。

●●●

這是一個(gè)IF嵌套公式，判斷表“一”A列是否有數(shù)據(jù)，如有數(shù)據(jù)則返回“表一”，否則返回另一個(gè)IF公式：

IF(era<>'','表二',IF(sana<>'','表三',''))

判斷表“二”A列是否有數(shù)據(jù)，如有數(shù)據(jù)則返回“表二”，否則返回另一個(gè)IF公式：

IF(sana<>'','表三','')

判斷表“三”A列是否有數(shù)據(jù)，如有數(shù)據(jù)則返回“表三”的數(shù)據(jù)，否則返回空單元格“”

10、Merry Christmas!

PS：您也可以留言給我，我會(huì)及時(shí)給您答復(fù)！