在Excel中,，如何忽略隱藏的行匯總數(shù)據(jù)

在使用Excel處理數(shù)據(jù)時(shí)，有時(shí)候需要把數(shù)據(jù)表中的若干行隱藏,，然后對(duì)可見部分的數(shù)據(jù)進(jìn)行求和或統(tǒng)計(jì)分析,，如果使用SUM或COUNT等函數(shù)計(jì)算，則會(huì)將隱藏行中的數(shù)據(jù)也計(jì)算在內(nèi),，無(wú)法滿足用戶的需求,。如圖 85?1所示為一份銷售記錄表，C3:C16銷售額之和為5579,，現(xiàn)將其中的二月份銷售數(shù)據(jù)隱藏,，如何才能快速求出可見部分的銷售額之和3358？

圖85?1忽略隱藏行求和

→ 解決方案：

使用SUBTOTAL函數(shù)忽略隱藏行對(duì)數(shù)據(jù)求和,。

→ 操作方法

在E2單元格輸入如下公式,，按Enter鍵結(jié)束。

=SUBTOTAL(109,C3:C16)

→ 原理分析

本例使用109作為SUBTOTAL函數(shù)的function_num參數(shù),，相當(dāng)于SUM函數(shù)功能,，并且具有忽略隱藏行進(jìn)行求和的特性，匯總C3:C16單元格區(qū)域可見單元格的銷售額數(shù)據(jù)。

SUBTOTAL函數(shù)語(yǔ)法如下：

SUBTOTAL(function_num,ref1,[ref2],...])

其中,，function_num參數(shù)為必需參數(shù),，用數(shù)字1 到 11（包含隱藏值）或 101 到 111（忽略隱藏值）表示，用于指定使用何種函數(shù)在列表中進(jìn)行分類匯總計(jì)算,。ref1……ref n參數(shù)為要對(duì)其進(jìn)行分類匯總計(jì)算的第1至n個(gè)命名區(qū)域或引用,。這些參數(shù)必須是對(duì)單元格區(qū)域的引用。

→ 知識(shí)擴(kuò)展

SUBTOTAL函數(shù)對(duì)應(yīng)功能及忽略不可見單元格特性

SUBTOTAL函數(shù)第1參數(shù)的數(shù)字與對(duì)應(yīng)功能函數(shù)關(guān)系如表格 85?1 所示,。

表格 85?1 SUBTOTAL第1參數(shù)代碼對(duì)應(yīng)功能表

在忽略不可見單元格進(jìn)行計(jì)算時(shí),，SUBTOTAL函數(shù)具有以下特性：

1．當(dāng) function_num 為從 1 到 11 的常數(shù)時(shí)，SUBTOTAL函數(shù)將計(jì)算包括通過(guò)“隱藏行”命令所隱藏的行中的值,，其功能與AVERAGE,、COUNT等11個(gè)函數(shù)接近。

2．當(dāng) function_num 為從 101 到 111 的常數(shù)時(shí),，SUBTOTAL 函數(shù)將忽略計(jì)算通過(guò)“隱藏行”命令所隱藏的行中的值,。

3．當(dāng)使用“篩選”命令后，SUBTOTAL 函數(shù)忽略計(jì)算任何不包括在篩選結(jié)果中的行,，不論使用什么 function_num 值,。即SUBTOTAL 函數(shù)不計(jì)算因篩選而隱藏的數(shù)據(jù)。

4．如果在 ref1,、ref2…中有其他的分類匯總（嵌套分類匯總）,，將忽略這些嵌套分類匯總，以避免重復(fù)計(jì)算,。即ref1,、ref2…參數(shù)引用的單元格是含有SUBTOTAL函數(shù)的公式計(jì)算結(jié)果，則該單元格的值將被忽略,。

5．SUBTOTAL 函數(shù)適用于數(shù)據(jù)列或垂直區(qū)域,。不適用于數(shù)據(jù)行或水平區(qū)域。例如,，當(dāng) function_num 大于或等于 101 時(shí)需要分類匯總某個(gè)水平區(qū)域時(shí),，例如 SUBTOTAL(109,B2:G2)，則隱藏某一列不影響分類匯總,。但是隱藏分類匯總的垂直區(qū)域中的某一行就會(huì)對(duì)其產(chǎn)生影響,。

版權(quán)所有 轉(zhuǎn)載須經(jīng)Excel技巧網(wǎng)許可