Excel関数の学習は10対10のSUBTOTAL関数です！

初心者がよく口にするのは、「Excel は関数が多すぎて覚えられない。たくさんの関数の働きをまとめた関数はないの？」ということです。正直に言うと、本当にあるんです！今回説明する機能の最大の特徴は、11個の機能を置き換えることができるということです。さらに、さまざまなフィルタリング結果に基づいて最終的な計算結果を変更することもできます。どうでしょうか？すごいと思いませんか？それでは早速、エディターと一緒に見てみましょう。

フィルターされた行の合計を無視します

「ミャオ先生、問題が発生しました。」シャオバイは出勤するとすぐに私のところに来ました。 、彼女は言いました：「合計の表があります。印刷するときに特定の人の内容を印刷したくないので、フィルターで非表示にしています。ただし、合計するたびに合計領域を変更する必要があり、常に変更されます。」

私:「それは簡単です。合計関数を変更するだけです。SUM を使用せず、SUBTOTAL を試してください。」

Xiaobai:「これは何の関数ですか?私」これまで使ったことはありません。」

私:「この関数は SUM 関数よりもはるかに強力です。いくつかの合計シナリオを処理できます!」

Xiaobai:「とても強力ですね。

「じゃあ、詳しく教えてあげるね～まずはフォームの問題を解いてみましょう。」 そう言って彼女のフォームを開いたところ、写真のようになりました。

「テーブルでは合計に SUM 関数が使用されています。それを SUBTOTAL 関数に置き換えましょう。もう一度見てみましょう。」その後、セルA7に数式を入力しました。

=SUBTOTAL(9,A2:A6)

「本当に変わりました！」その後、Xiaobai は他のフィールドでさらにフィルタリングしました。 、彼女は望んでいた結果が得られることがわかり、とても幸せでした。しかしその後、彼女は新しい世界を発見しました。「では、この 9 はどういう意味ですか?」

私: 「この 9 は、フィルタリングされていないデータを無視し、フィルタリングされた結果のみを合計することを意味します。」

シャオバイ:「あなたの言ったことを聞いてください、他の意味を表す他の数字はありますか?」

私:「もちろん、それでは他の数字の意味についてお話します。 ''

非表示の行の合計を無視する

この状況に遭遇することがあります。数字の列があり、いくつかの不明な行を非表示にする必要があります。 rows. 操作を実行するデータ。 SUM を直接使用すると、図に示すように正しい結果が得られません。

先ほど学習したSUBTOTAL関数のパラメータ「9」を使っても、図のように実現できません。

現時点では、パラメータの変更を検討する必要があります。

次のパラメータは「109」です、さあ!

式: =SUBTOTAL(109,A1:A5)

次の図に示すように、次の最初のパラメータを変更します。 SUBTOTAL関数を使うと「109」になったら、隠れた行を無視して簡単に集計結果を得ることができます！写真が示すように。

パラメータ「109」の機能は、表示されている値を合計することであり、非表示のデータまたはフィルタリングされたデータを合計することができます。パラメータ「9」はフィルタリングされた行でのみ使用でき、非表示の行には影響しません。

SUBTOTAL の他のパラメータの適用

SUBTOTAL は、合計、平均、最大、標準偏差、分散のフィールドに限定されません。 all これは見つかります。最初のパラメータを変更するだけです。たとえば、図 6 に示すように、無視された非表示行の最大値をカウントしたいとします。

式: =SUBTOTAL(104,A1:A5)

## (非表示にする前)

後最大値「8」を非表示にすると、現在表示されている最大値「7」がセル A6 で直接取得されます。

では、なぜ 104 なのでしょうか?実はSUBTOTAL関数には数値表現のルールがあり、今回はその他のパラメータ、平均値、最大値、最小値、標準偏差、分散など11個の関数についてお話します。一般的に使用されるものと、一般的に使用されないものがありますので、ニーズに応じて選択できます。以下は 11 個のパラメータの比較表です。

##SUM10#110計算樣本變異數VAR11111

計算時忽略被篩選值	#計算時忽略隱藏行和被篩選值	#作用	對應函數
#1	101	平均值	AVERAGE
##2	102	計算包含數字的單元格數	COUNT
3	103	#計算非空白單元格數	COUNTA
4	#104	最大值	MAX
#5	105	最小值 #	MIN
6	#106	乘法	PRODUCT
#7	##107	計算樣本標準差	STDEV
8	108	計算總體標準差	STDEVP
9	109	#求和
	#	計算總體變異數	VARP

拓展部分1：只統計分類匯總

我們在製表的時候，經常會碰到這樣一種匯總情況，在同表內進行分項匯總，如圖所示。

如果使用SUM進行匯總，則會統計出所有的數據，如圖所示。

可是我們只想合計各個小計的內容呀！別慌，只要把SUM換成SUBTOTAL就可以得到我們想要的答案。如圖所示。

這是為什麼？其實SUBTOTAL除了能忽略掉被隱藏、篩選的行外，還會忽略掉包含SUBTOTAL，以及AGGREGATE函數的儲存格。單元格B3、B6、B10都是用SUBTOTAL函數計算的小計，自然在最後用SUBTOTAL函數求和時，會被忽略掉。

拓展部分2：不間斷序號

「我們了解了SUBTOTAL函數的特性之後，就可以用它來做一些什麼，例如給列表編號。”

“什麼，列表編號不是用滑鼠拉一下就好了嗎？”

“不一樣~我的編號，可是自動的哦！無論是刪除行還是隱藏行，編號都能自動重新排列!”

“這麼神奇，那我可要好好學學。”

其實它非常簡單，假設我有一張列表，目前序號列是空的，如圖所示。

在A2儲存格輸入公式：=SUBTOTAL(103,B$2:B2)，然後下拉填充，就能得到我們想要的序號。如圖所示。

我們試著來隱藏一行，就會發現，序號仍然是按照順序排列的，並沒有中斷，如圖所示。

現在我們來逐步解釋公式=SUBTOTAL(103,B$2:B2)

• # 103：查看上述參數對照表可以得知，103的作用是忽略隱藏行和被篩選值，統計非空白單元格數。

• B$2:B2：A2儲存格內的區域是B$2:B2，目的是，統計出B2:B2區域中非空白儲存格數，結果為1。在公式下拉後，A3單元格內的區域變成了B$2:B3，那麼統計的非空單元格數就變成了兩個，得到的結果為2。如圖所示。

以此類推，隨著公式的下拉，我們就可以得到一組連續的序號。再結合SUBTOTAL函數第一參數只計算可見數值的特性，就可以得到一組不間斷的序號！

你還知道哪些關於SUBTOTAL函數的妙用呢？歡迎留言分享給我們哦~喜歡文章的小夥伴不妨點下“在看”，支持我們哦！

相關學習推薦：excel教學

以上がExcel関数の学習は10対10のSUBTOTAL関数です！の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。