フィルターされた列での基準ベースのカウント（Microsoft Excel）

マーティは、会社のすべての従業員（過去と現在）をリストした大きなワークシートを持っています。ワークシートは、名前、住所、部門、性別、ステータスなど、各従業員に関するさまざまな情報を追跡します。

マーティは、自分のニーズを満たすためにデータをフィルタリングすることがよくあります。彼は、フィルタリング後に表示される行に基づいて、男性の従業員の割合と女性の割合を決定する方法を望んでいます。 SUBTOTAL関数は、フィルター処理された列で機能してさまざまなカウントを提供できますが、フィルター処理された列の内容（ “M”または “F”）に基づいてカウントを決定することはできません。

1つのアプローチは、ピボットテーブルを使用してパーセンテージを決定することです。

ピボットテーブルは、特にこのような1つの質問に答える場合に、比較的使いやすいです。ただし、従業員の詳細情報を表示できるという点ではそれほど優れていません。男性/女性の質問に対する総合的な回答は表示できますが、同時にそれらの従業員の詳細を表示することはできません。ですから、マーティの問題に対応するために、まっすぐな数式を使用することに焦点を当てたいと思います。

目的のパーセンテージを取得するための数式を作成することは、最初に表示されるよりも困難です。たとえば、すべての従業員レコードが表示されているときにカウントを決定するのは簡単です。たとえば、性別が列Cにあると仮定して、次のようなものを使用して、レコードの何パーセントが男性従業員のものであるかを判断できます。

問題は、列Cとは異なる列でレコードをフィルタリングする場合です。たとえば、ステータス列にあるものでレコードをフィルタリングする場合があります。上記の数式は、フィルタリングのために現在表示されているレコードだけでなく、性別列のすべてのレコードに基づいたパーセンテージを提供します。

この時点で、SUBTOTAL関数が機能する可能性があると思うかもしれませんが、Martyはそれが機能しないことを発見しました。繰り返しますが、性別が列Cにある場合は、列Cの下部に次のように入力できます。

ただし、これは目的のニーズには機能しません。主な問題は、SUBTOTAL関数が表示されているレコードのカウントを提供することですが、これらのレコードの列Cに「M」と「F」のどちらが含まれているかは区別されません。2番目の問題は、COUNTAがすべてのレコードをカウントすることです。表示されたものだけです。したがって、この式では、「M」または「F」を含む表示されたレコードのパーセンテージは示されません

ただし、代わりに、レコードの総数のうち表示されるレコード数のパーセンテージ。

必要に応じて、ヘルパー列を使用してみてください。各従業員の「M」または「F」ステータスを1または0として表す列を追加するだけです。

これは、次のような簡単な式で実行できます。

この式も、性別列がCであると想定しています。この式が列X（ヘルパー列）に配置されているとすると、次のように2つのSUBTOTAL関数を使用できます。

SUBTOTALを最初に使用すると、「M」を含む行数がわかります

SUBTOTALを2回使用すると、表示されている行の総数がわかります。式の結果は、記録が表示されている男性労働者の割合です。 1から男性の結果を引くことにより、女性労働者の割合を決定できます。

ヘルパー列を使用できない場合（またはヘルパー列を使用したくない場合）、前述のアプローチの欠点を回避するのは少し難しいです。もう少し複雑な式が必要です。以下は機能する数式ですが、説明を少し簡単にするために、1つの数式を4行に分割しました。

この式がどのように機能するかについて詳しく説明しますので、少しお待ちください。これにはしばらく時間がかかります。最初に、最初の行の終わりと2番目の行の全体にまたがる数式のこの部分を見てみましょう。

ここでのOFFSET関数の使用法は、3つのパラメーターに依存しています。 1つ目は、返される参照を計算するための開始点に対応します（この場合、開始点はC2：C9999です）。 2番目のパラメーターは、最初のパラメーターで定義された範囲の先頭からオフセットされる行数です。この場合、オフセットされる行は、分析されている実際の行から範囲内の最小の行番号（常に値2を返す）を引くことによって定義されます。したがって、たとえば、分析対象の行が行10の場合、そこから2（開始行）を引くと、最初のパラメーターで指定された範囲の先頭から8行のオフセットが得られます。

3番目のパラメーターはブランクであるため、デフォルトは0です。これは、最初のパラメーターで指定された範囲内の最初の列からオフセットされる列の数です。最後に、4番目のパラメーターは数値1です。これは、OFFSETが1セルの高さの範囲のみを返すようにすることを示します。

要するに、数式のこの部分全体が含まれているため、分析対象の列の単一のセルへの参照が返されます。この式で説明するために、返されるものを「SingleCell」と呼びましょう。これを元の数式に接続すると、次のようになります。

次に、最初のSUBTOTAL関数は、単一セルのCOUNTA結果（最初のパラメーターに使用されている値3で示されます）を返します。その結果、SUBTOTALは、セルが表示されているかどうかに応じて、0または1を返します。 （セルが結果から除外された場合は0が返されます。除外されていない場合（表示されている場合）は1が返されます。）

式の次の部分は、ISNUMBER関数とSEARCH関数に依存しています。コードのこの部分は、セルに文字「M」が含まれているかどうかに応じて、0または1を返します。最終的には、次のようになります。

この単一の行の場合、SUMPRODUCTは0を返します。これは、行が全体のカウントで「カウントされない」ことを意味します。 SUMPRODUCTは配列ベースの関数であるため、元の範囲のすべての行の乗算に基づいて個別に積を計算しています。したがって、2つの条件を満たすすべての行の数を決定します。行が表示され、行に文字「M」が含まれます。

これは最終的に、最後のSUBTOTAL関数の結果で除算されます。これは、表示されている行数のCOUNTA結果です。最終結果は、列Cに「M」文字が表示されている表示行の数のパーセンテージです。これはマーティが望んでいた正確な結果です。

表示されている行の女性の割合を取得するには、検索対象を変更するだけです。数式の「M」を「F」に変更すれば、問題はありません。

ただし、言及すべき注意点が1つあります。 SEARCH関数は、大文字と小文字を区別しません。したがって、性別列（列C）で「M」の代わりに「男性」を使用し、「F」の代わりに「女性」を使用する場合、式で「男性」を検索すると、「 「女性」には「男性」の文字が含まれます。したがって、「M」と「F」を使用するのが最善です。「男性」と「女性」を使用する必要がある場合は、数式で「女性」を使用して、男性レコードのパーセンテージを1マイナスとして計算します。女性の割合。

注：

このページ（または_WordTips_サイトの他のページ）で説明されているマクロの使用方法を知りたい場合は、役立つ情報を含む特別なページを用意しました。

linkここをクリックして、新しいブラウザタブでその特別なページを開きます。

_ExcelTips_は、費用効果の高いMicrosoftExcelトレーニングのソースです。

このヒント（13550）は、Microsoft Excel 2007、2010、2013、2016、2019、およびOffice365のExcelに適用されます。