小窍门:点击图片或把手机横放能放大图片! 方法:OFFSET+COUNTA 详解 EXCEL2007举例 动态合并几张工作表到一张合并表,合并表数据随明细表变动而变动。 举例数据如下图,3张表明细表,分别是“一”“二”“三”,将3张表动态合并到最后一张“合并”表中。 ●●● 表“二”、表“三”和表“一”格式类似。 1、公式菜单 → 名称管理器 ●●● 2、新建 → 在“名称”右边的输入框内输入:yi → 在“引用位置”右边的输入框内输入: =OFFSET(一!$A$1,ROW()-1,COLUMN()-1,,) → 点击:确定。如下图。 ●●● 公式解析: OFFSET 是定位引用函数 语法结构为: OFFSET(定位起点,上下偏移行数,左右偏移列数,返回引用的行数,返回引用的列数) ROW 函数返回行号,省略参数则返回公式所在单元格行号; CLOUMN 函数返回列号,省略参数则返回公式所在单元格列号。 本例中的OFFSET ■ OFFSET定位起点是:一!$A$1,即表“一”的单元格A1 ■ 向下偏移ROW()-1 返回的行数 ■ 向右偏移COLUMN()-1 返回的列数 ■ OFFSET省略最后2个参数,则返回引用的行数和列数默认和第1参数一样,即1行和1列。 ■ 把这个公式复制粘贴到其他表格单元格A1,作用相当于拷贝表“一”的数据到其他表格,从表“一”的A1开始拷贝,公式粘贴到哪个单元格,就拷贝出表“一”对应单元格的内容。 3、新建 → 在“名称”右边的输入框内输入:yia → 在“引用位置”右边的输入框内输入: =OFFSET(一!$A$1,ROW()-1,,,) → 点击:确定。如下图。 ●●● 这个OFFSET公式与上面第2步相比,不光省略后面2个参数,还省略了第3参数,即左右不偏移,只返回表“一”A列的值。 4、新建 → 在“名称”右边的输入框内输入:er → 在“引用位置”右边的输入框内输入: =OFFSET(二!$A$1,ROW()-1-COUNTA(一!$A:$A),COLUMN()-1,,) → 点击:确定。如下图。 ●●● 这个OFFSET公式和第2步名称:yi 定义的公式类似,不同之处是第2参数: ROW()-1-COUNTA(一!$A:$A) ■ COUNTA是计数函数 ■ COUNTA(一!$A:$A)公式返回的值是表“一”A列有数据的行数 为什么用ROW()返回的行号减去表“一”A列有数据的行数 ■ 用OFFSET公式引用表“二”数据到合并表时,OFFSET的第2参数要用ROW()减去表“一”的有数据的行数 ■ 以此类推,后面表“三”要用ROW()减去表“一”和表“二”的行数 5、新建 → 在“名称”右边的输入框内输入:era → 在“引用位置”右边的输入框内输入: =OFFSET(二!$A$1,ROW()-1-COUNTA(一!$A:$A),,,) → 点击:确定。如下图。 ●●● 这个OFFSET公式与上面第4步公式相比,省略了第3参数,即左右不偏移,只返回表“二”A列的值。 6、新建 → 在“名称”右边的输入框内输入:san → 在“引用位置”右边的输入框内输入: =OFFSET(三!$A$1,ROW()-1-COUNTA(一!$A:$A)-COUNTA(二!$A:$A),COLUMN()-1,,) → 点击:确定。如下图。 ●●● 在第4步已经详细解释,这里不赘述。 7、新建 → 在“名称”右边的输入框内输入:sana → 在“引用位置”右边的输入框内输入: =OFFSET(三!$A$1,ROW()-1-COUNTA(一!$A:$A)-COUNTA(二!$A:$A),,,) → 点击:确定。如下图。 ●●● 这个OFFSET公式,省略了第3参数,即左右不偏移,只返回表“三”A列的值。 8、'合并'表单元格A1输入如下公式,按回车并向下向右复制完成(向右复制到E列)。 =IF(yia<>'',yi,IF(era<>'',er,IF(sana<>'',san,''))) 如下图。 ●●● 这是一个IF嵌套公式,判断表“一”A列是否有数据,如有数据则返回表“一”的数据,否则返回另一个IF公式: IF(era<>'',er,IF(sana<>'',san,'')) 判断表“二”A列是否有数据,如有数据则返回表“二”的数据,否则返回另一个IF公式: IF(sana<>'',san,'') 判断表“三”A列是否有数据,如有数据则返回表“三”的数据,否则返回空单元格“” 9、'合并'表单元格F2输入如下公式,按回车并向下复制完成。 =IF(yia<>'','表一',IF(era<>'','表二',IF(sana<>'','表三',''))) 如下图。 ●●● 这是一个IF嵌套公式,判断表“一”A列是否有数据,如有数据则返回“表一”,否则返回另一个IF公式: IF(era<>'','表二',IF(sana<>'','表三','')) 判断表“二”A列是否有数据,如有数据则返回“表二”,否则返回另一个IF公式: IF(sana<>'','表三','') 判断表“三”A列是否有数据,如有数据则返回“表三”的数据,否则返回空单元格“” 10、Merry Christmas! PS:您也可以留言给我,我会及时给您答复! |
|