フォーミュラの例を備えたExcel亜一度関数

チュートリアルでは、Excelの小計関数の特異性について説明し、目に見える細胞のデータを要約するために亜2フォーミュラを使用する方法を示しています。

前の記事では、小計機能を使用して、SubtotalsをExcelに挿入する自動方法について説明しました。今日、あなたはあなた自身で亜最高式を書く方法と、これがあなたに与える利点を学びます。

• Excel -Syntaxおよび使用の小計関数
• Excelで小計機能を使用する上位3つの理由
• 小計の式の例
• Excel subtotalが機能しません

Excel亜テタル関数 - 構文と使用

Microsoftは、Excel Subtotalを、リストまたはデータベースのサブテルを返す関数として定義しています。この文脈では、「亜極性」は、定義されたセルの範囲の合計数字だけではありません。 1つの特定のことのみを実行するように設計された他のExcel関数とは異なり、Subtotalは驚くほど用途が広く - セルのカウント、平均の計算、最​​小値または最大値の検索など、異なる算術および論理操作を実行できます。

小計関数は、Excel 2016、Excel 2013、Excel 2010、Excel 2007、およびLowerのすべてのバージョンで利用できます。

Excel小計関数の構文は次のとおりです。

subtotal（function_num、ref1、[ref2]、…）

どこ：

• function_num-サブテルに使用する関数を指定する数字。
• ref1、ref2、… - 1つ以上のセルまたは亜極性の範囲。最初のREF引数が必要であり、その他（最大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が利用可能な機能番号のリストを表示します。

たとえば、これは、細胞C2からC8の値を要約して、以下9式を作成する方法です。

式に関数番号を追加するには、ダブルクリックして、コンマを入力し、範囲を指定し、閉じた括弧を入力し、Enterを押します。完成したフォーミュラは次のようになります：

=SUBTOTAL(9,C2:C8)

同様に、亜テタル1式を記述して、数字を持つセルをカウントするために平均的な亜テタル2を、非黒色をカウントするために亜テル3をカウントすることができます。次のスクリーンショットには、他のいくつかの式が動作しています。

注記。合計や平均などの概要関数を持つ亜集合式を使用すると、空白と非数値を含むセルを無視して数字を持つセルのみを計算します。

Excelで亜最高式を作成する方法がわかったので、主な問題は、なぜそれを学ぶのに苦労したいのでしょうか？ Sum、Count、Maxなどのような通常の関数を単に使用してみませんか？あなたはすぐに答えを見つけます。

Excelでsubtotalを使用する上位3つの理由

従来のExcel関数と比較して、Subtotalは次の重要な利点を提供します。

1.フィルターされた行で値を計算します

Excelサブトータル関数はフィルタリングされた行の値を無視するため、それを使用して、フィルターに従って亜テタル値が自動的に再計算される動的なデータ概要を作成できます。

たとえば、東部地域のみの販売を示すためにテーブルをフィルタリングすると、他のすべての領域が合計から削除されるように、亜潜在式が自動的に調整されます。

注記。両方の関数番号セット（1-11および101-111）はろ過されたセルを無視するため、この場合はEther subtotal 9または亜テタル109式を使用できます。

2。可視セルのみを計算します

ご覚えているように、function_num 101〜111を備えた小計式は、すべての隠れたセルを無視します - 手動で除外され、隠されています。したがって、Excel's Hide機能を使用して無関係なデータを表示している場合は、機能番号101-111を使用して、非表示の行の値を除外します。

次の例では、それがどのように機能するかをより多くの理解を得るのに役立ちます：Subtotal 9 vs. subtotal 109。

3.ネストされた小計式の値を無視します

Excelサブテルフォーミュラに供給された範囲に他の亜潜水式が含まれている場合、それらのネストされた小計は無視されるため、同じ数値は2回計算されません。すごいよね？

以下のスクリーンショットでは、2つの個別の範囲AVERAGE(C2:C5, C7:C9)を持つ平均式を使用したかのように、グランドアベレージフォーミュラSUBTOTAL(1, C2:C10)は、セルC3およびC10の亜集合式の結果を無視します。

Excelでsubtotalを使用 - 式の例

初めてsubtotalに遭遇したとき、それは複雑で、トリッキーで、さらに無意味に見えるかもしれません。しかし、ブラスタックに着くと、マスターするのはそれほど難しくないことに気付くでしょう。次の例では、いくつかの役立つヒントと心に強く訴えるアイデアを示します。

例1。サブテル9対サブトータル109

すでにご存知のように、Excel Subtotalは2セットの関数番号を受け入れます：1-11および101-111。どちらのセットもろ過された行を無視しますが、数字1〜11には手動で隠された行が含まれますが、101-111はそれらを除外します。違いをよりよく理解するには、次の例を考えてみましょう。

合計フィルタリングされた行には、以下のスクリーンショットに示すように、以下のような小計9または小計109式のいずれかを使用できます。

ただし、Home The The Home Tab> Cells Group> Format > Hide＆Unideを使用して、Hide行コマンドを使用して無関係なアイテムを手動で非表示にしている場合、または右クリックして、 Hideをクリックすると、目に見える列のみの合計値が唯一のオプションです。

他の関数番号も同じように機能します。たとえば、非ブランクろ過されたセルをカウントするには、亜塩分3または亜潜熱103式のいずれかが行います。ただし、範囲に隠された行がある場合、目に見える非黒色を適切にカウントできます。

注記。 function_num 101-111を備えたExcelサブトータル関数は、非表示の列では表示されませんが、非表示の行の値を無視します。たとえば、 SUBTOTAL(109, A1:E1)などの式を使用して水平範囲で数値を合計する場合、列を隠すことはサブテルに影響しません。

例2。データを動的に要約するためにサブテルの場合

概要レポートまたはさまざまなデータの概要を表示する必要があるが、すべてのスペースがないダッシュボードを作成している場合、次のアプローチが解決策になる場合があります。

• 1つのセルで、Total、Max、Minなどの関数名を含むドロップダウンリストを作成します。
• ドロップダウンの隣のセルに、ドロップダウンリストの関数名に対応する埋め込みサブトータル関数を備えたネストされたif式を入力します。

たとえば、サブテルへの値がセルC2：C16にあると仮定すると、A17のドロップダウンリストには合計、平均、最大、およびMINアイテムが含まれています。

=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 subtotalが機能しない - 一般的なエラー

あなたの亜一度のフォーミュラがエラーを返している場合、それは次の理由のいずれかが原因である可能性があります。

#VALUE! -function_num引数は、1〜11または101-111の整数以外です。または、REF引数のいずれかには3D参照が含まれています。

#DIV/0! - 指定された概要関数がゼロで分割を実行する必要がある場合に発生します（たとえば、単一の数値を含まないセルの範囲の平均または標準偏差の計算）。

#NAME? - 小計関数の名前は間違っています - 修正する簡単なエラー:)

ヒント。まだサブテル機能に慣れていない場合は、組み込みのサブトータル機能を使用して、式を自動的に挿入できます。

これが、Excelで亜極式を使用して、可視セルのデータを計算する方法です。例を容易にするには、以下にサンプルワークブックをダウンロードできます。読んでくれてありがとう！

練習ワークブック

excel subtotalフォーミュラの例（.xlsxファイル）

以上がフォーミュラの例を備えたExcel亜一度関数の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。