具有公式示例的Excel小計函數

該教程解釋了Excel中的小計函數的特異性，並顯示瞭如何使用小計公式來匯總可見細胞中的數據。

在上一篇文章中，我們討論了一種使用小計特徵在Excel中插入微分的自動方法。今天，您將學習如何獨自編寫小計公式以及這給您帶來的優勢。

• excel -excel -contotal函數 - 語法和使用
• 在Excel中使用小計函數的三大理由
• 小計公式示例
• excel小節不起作用

Excel小計函數 - 語法和使用

Microsoft將Excel Subtotal定義為返回列表或數據庫中的小計的函數。在這種情況下，“小計”不僅是在定義的單元格範圍內的總數。與其他設計僅執行一件特定事物的Excel函數不同，小計具有驚人的用途 - 它可以執行不同的算術和邏輯操作，例如計數單元，計算平均值，找到最小值或最大值等。

次序功能可在Excel 2016，Excel 2013，Excel 2010，Excel 2007和Lives的所有版本中獲得。

Excel小計函數的語法如下：

小計（function_num，ref1，[ref2]，…）

在哪裡：

• function_num-一個指定用於小計的函數的數字。
• ref1，ref2，… - 一個或多個單元格或多個範圍。需要第一個參數，其他參數（最多254）是可選的。

function_num參數可以屬於以下集合之一：

• 1-11忽略過濾的單元格，但包括手動隱藏的行。
• 101-111忽略所有隱藏的單元 - 被過濾並手動隱藏。

function_num		功能	描述
1	101	平均的	返回平均數字。
2	102	數數	計算包含數字值的單元格。
3	103	伯爵	計算非空細胞。
4	104	最大限度	返回最大的價值。
5	105	最小	返回最小的值。
6	106	產品	計算細胞的乘積。
7	107	Stdev	根據數字樣本返回人群的標準偏差。
8	108	stdevp	根據整個數字人群返回標準偏差。
9	109	和	加起來數字。
10	110	var	根據數字樣本估算人群的差異。
11	111	varp	根據整個數量人群估算人口的差異。

實際上，無需記住所有功能號碼。一旦您開始在單元格或公式欄中鍵入小計公式後，Microsoft Excel將為您顯示可用功能號碼的列表。

例如，這就是您可以製作一個小計9公式來總結單元格至C8中的值：

要在公式中添加功能號，請雙擊它，然後鍵入逗號，指定範圍，鍵入閉合括號，然後按Enter。完整的公式看起來像這樣：

=SUBTOTAL(9,C2:C8)

以類似的方式，您可以編寫一個小計1公式，以獲取平均值，小計2以計數數字，小計3以計數非空白等等。以下屏幕截圖顯示了其他一些行動中的公式：

筆記。當您使用具有摘要函數（如SUM或平均值）的小計公式時，它僅計算出具有忽略空白和包含非數字值的單元格的單元格。

現在，您知道如何在Excel中創建一個小計公式，主要的問題是 - 為什麼要遇到學習的麻煩？為什麼不簡單地使用常規函數，例如總和，計數，最大等？您會在下面找到答案。

在Excel中使用小計的三個理由

與傳統的Excel功能相比，小計為您帶來以下重要優勢。

1。計算過濾行中的值

由於Excel小計函數忽略了過濾的行中的值，因此您可以使用它來創建動態數據摘要，其中根據過濾器自動重新計算小計值。

例如，如果我們過濾表僅顯示東部地區的銷售，則小計公式將自動調整，以便將所有其他區域從總數中刪除：

筆記。由於兩個函數編號集（1-11和101-111）忽略了過濾的單元格，因此在這種情況下，您可以使用Ether subtotal 9或小計109公式。

2。僅計算可見細胞

如您所記得的那樣，具有函數_num 101至111的小計公式忽略了所有隱藏的單元格 - 被過濾並手動隱藏。因此，當您使用Excel的Hide功能從視圖中刪除無關緊要的數據時，使用功能編號101-111將隱藏行中的值排除在小計中。

以下示例將幫助您對其工作方式有更多了解：小計9與小計109。

3。忽略嵌套小計公式中的值

如果向您的Excel小計公式提供的範圍包含任何其他小計公式，則將忽略那些嵌套的小計公式，因此不會兩次計算相同的數字。太好了，不是嗎？

在下面的屏幕截圖中，平均平均公式SUBTOTAL(1, C2:C10)忽略了細胞C3和C10中的小計公式的結果，就好像您使用了平均公式，平均公式為2個單獨的AVERAGE(C2:C5, C7:C9) 。

在Excel -Formula示例中使用小計

當您第一次遇到小計時時，它看起來似乎很複雜，棘手，甚至毫無意義。但是，一旦您到達黃銅釘，您就會意識到掌握並不難。以下示例將向您展示一些有用的技巧和鼓舞人心的想法。

示例1。小計9 vs.小計109

如您所知，Excel小計接受2組功能編號：1-11和101-111。兩組都忽略了過濾的行，但是數字1-11包括手動隱藏行，而101-111則將其排除在外。為了更好地理解差異，讓我們考慮以下示例。

對於總過濾行，您可以使用小計9或小計109公式，如下面的屏幕截圖所示：

但是，如果使用“ home”選項卡> “單元格組”> “格式” > “ hide＆undide”或右鍵單擊行，然後單擊“ hide”的“ hish clove”命令在“ hiber”選項卡>單元格組上手動無關項目，然後您只想在可見行中進行總值，subtotal 109是唯一的選擇：唯一的選擇：唯一的選擇：

其他功能數字以相同的方式工作。例如，要計算非濾波的細胞，小計3或小計103公式將可以做到。但是，只有小計103才能正確計算可見的非空白，如果該範圍內有任何隱藏行：

筆記。具有函數_num 101-11的Excel小計函數忽略了隱藏行中的值，但在隱藏的列中不忽略值。例如，如果您使用諸如SUBTOTAL(109, A1:E1)之類的公式來在水平範圍內總和數字，則隱藏列將不會影響小計。

示例2。如果要動態總結數據

如果您要創建摘要報告或儀表板，其中您必須顯示各種數據摘要，但沒有所有空間，則以下方法可能是一種解決方案：

• 在一個單元格中，製作一個下拉列表，其中包含函數名稱，例如Total，Max，Min等。
• 在下拉列表旁邊的單元格中，輸入嵌套的嵌套公式，該公式帶有與下拉列表中功能名稱相對應的嵌入式小計函數。

例如，假設小計的值在單元格中C2：C16中，並且A17中的下拉列表包含總，平均，最大和最小項目，則“動態”小計公式如下：

=IF(A17="total", SUBTOTAL(9,C2:C16), IF(A17="average", SUBTOTAL(1,C2:C16), IF(A17="min", SUBTOTAL(5,C2:C16), IF(A17="max", SUBTOTAL(4,C2:C16),""))))

現在，根據您的用戶從下拉列表中選擇的功能，相應的小計函數將計算過濾波行中的值：

提示。如果突然間，下拉列表和配方單元從工作表中消失，請確保在過濾器列表中選擇它們。

excel小節不起作用 - 常見錯誤

如果您的小計公式返回錯誤，則可能是由於以下原因之一：

#VALUE! - 函數_num參數不僅僅是1-11或101-111之間的整數；或任何參數中的任何一個包含3-D參考。

#DIV/0! - 如果指定的摘要函數必須按零執行劃分（例如計算不包含單個數字值的單元格的平均值或標準偏差）。

#NAME? - 小計函數的名稱拼寫錯誤 - 修復更容易的錯誤:)

提示。如果您對小計函數不滿意，則可以使用內置的小計功能，並自動插入公式。

這就是如何使用Excel中的小計公式來計算可見細胞中的數據。為了使示例更容易遵循，歡迎您下面下面下載我們的樣本工作簿。謝謝您的閱讀！

練習工作簿

Excel小計公式示例（.xlsx文件）

以上是具有公式示例的Excel小計函數的詳細內容。更多資訊請關注PHP中文網其他相關文章！