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

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

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

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

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

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

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

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

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

在忽略不可見(jiàn)單元格進(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…中有其他的分類(lèi)匯總（嵌套分類(lèi)匯總），將忽略這些嵌套分類(lèi)匯總，以避免重復(fù)計(jì)算。即ref1、ref2…參數(shù)引用的單元格是含有SUBTOTAL函數(shù)的公式計(jì)算結(jié)果，則該單元格的值將被忽略。

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

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