特定の値を含む行数をExcelでカウントする
特定の値を持つセルの数をExcelワークシートでカウントするのは簡単かもしれません。しかし、特定の値を含む行数を取得するのはかなり複雑になることがあります。このような場合、SUM、MMULT、TRANSPOSE、COLUMN関数に基づいたより複雑な数式が役立つかもしれません。このチュートリアルでは、Excelでこの作業を行うためにこの数式を作成する方法について説明します。
特定の値を含む行数をカウントする
例えば、ワークシートに値の範囲があり、以下のように指定された値「300」を持つ行数をカウントする必要があるとします:
特定の値を含む行数を取得するための一般的な構文は次のとおりです:
{=SUM(–(MMULT(–(data=X),TRANSPOSE(COLUMN(data)))>0))}
配列数式であり、Ctrl + Shift + Enterキーを同時に押す必要があります。
配列数式であり、Ctrl + Shift + Enterキーを同時に押す必要があります。
- data: 特定の値が含まれているかどうかを確認するセルの範囲;
- X: 行をカウントするために使用する特定の値。
1. 結果を表示したい空白のセルに以下の数式を入力またはコピーしてください:
=SUM(--(MMULT(--($A$2:$C$12=300),TRANSPOSE(COLUMN($A$2:$C$12)))>0))
2. 次に、正しい結果を得るために Ctrl + Shift + Enter キーを同時に押してください。スクリーンショットをご覧ください:
数式の説明:
=SUM(--(MMULT(--($A$2:$C$12=300),TRANSPOSE(COLUMN($A$2:$C$12)))>0))
- --$A$2:$C$12=300: この式は、「300」という値が範囲 A2:C12 に存在するかどうかをチェックし、TRUE および FALSE の配列結果を生成します。二重の負符号は、TRUE を 1 に、FALSE を 0 に変換するために使用されます。したがって、次のような結果が得られます:{0,0,0;1,0,0;0,0,0;0,1,1;0,0,0;0,1,0;0,0,0;1,0,0;0,0,1;0,0,0;1,1,1}。この配列は、11行3列で構成され、MMULT 関数の array1 として機能します。
- TRANSPOSE(COLUMN($A$2:$C$12)): ここでの COLUMN 関数は、範囲 A2:C12 の列番号を取得するために使用され、次のような3列の配列を返します:{1,2,3}。そして TRANSPOSE 関数は、この配列を3行の配列 {1;2;3} に交換し、MMULT 関数内の array2 として機能します。
- MMULT(--($A$2:$C$12="Joanna"),TRANSPOSE(COLUMN($A$2:$C$12))): この MMULT 関数は、上記の2つの配列の行列積を返します。結果として次のようなものが得られます:{0;1;0;5;0;2;0;1;3;0;6}。
- SUM(--(MMULT(--($A$2:$C$12="Joanna"),TRANSPOSE(COLUMN($A$2:$C$12)))>0))= SUM(--{0;1;0;5;0;2;0;1;3;0;6}>0): 最初に、配列内の0より大きい値をチェックします:値が0より大きい場合は TRUE が表示され、0より小さい場合は FALSE が表示されます。そして、二重の負符号により、TRUE と FALSE がそれぞれ 1 と 0 に強制変換されるため、次のような結果が得られます:SUM({0;1;0;1;0;1;0;1;1;0;1})。最後に、SUM 関数は配列内の値を合計して結果を返します:6。
ヒント:
特定のテキストを含む行数をワークシートでカウントする必要がある場合は、以下の数式を適用してください。また、総数を取得するために Ctrl + Shift + Enter キーを一緒に押すことを忘れないでください:
=SUM(--(MMULT(--(ISNUMBER(SEARCH("Joanna",A2:C12))),TRANSPOSE(COLUMN($A$2:$C$12)))>0))
関連する関数:
- SUM:
- Excel の SUM 関数は、提供された値の合計を返します。
- MMULT:
- Excel の MMULT 関数は、2つの配列の行列積を返します。
- TRANSPOSE:
- TRANSPOSE 関数は、特定のセル範囲に基づいて新しい方向に配列を返します。
- COLUMN:
- COLUMN 関数は、数式が現れる列番号、または指定された参照の列番号を返します。
その他の記事:
- 内部基準を満たす行をカウントする
- 今年と昨年の製品販売レポートを持っていると仮定し、今年の売上が昨年より多い製品や、今年の売上が昨年より少ない製品の数をカウントする必要があるとします。通常、ヘルパー列を追加して2年間の売上差を計算し、COUNTIFを使用して結果を得ることができます。しかし、この記事では、ヘルパー列なしで直接結果を得るためのSUMPRODUCT関数を紹介します。
- 複数の基準を満たす行をカウントする
- Excelでは、行レベルで動作する論理テストに基づく複数の基準に基づいて範囲内の行数をカウントするには、SUMPRODUCT関数が役立ちます。
- 多くの値のいずれかに等しいセルの数をカウントする
- 列Aに製品リストがあり、列Aから特定の製品であるApple、Grape、Lemon(範囲C4:C6にリストされている)の総数を取得したいとします。通常、Excelでは単純なCOUNTIFやCOUNTIFS関数はこのシナリオでは機能しません。この記事では、SUMPRODUCTとCOUNTIF関数の組み合わせを使用してこの問題を迅速かつ簡単に解決する方法について説明します。
最高のオフィス生産性ツール
Kutools for Excel - あなたを群衆から際立たせるツール
🤖 | Kutools AI Aide: 次の要素に基づいてデータ分析を革新:インテリジェント実行|コード生成 |カスタム数式の作成|データの分析とチャートの生成 |Kutools機能の呼び出し… |
人気の機能:重複を見つける、ハイライトする、または特定する | 空白行を削除 | データを失うことなく列やセルを結合 | 数式を使わずに丸める... | |
スーパー VLookup:複数条件 | 複数値 | 複数シート間 | ファジーマッチ... | |
高度なドロップダウンリスト:簡単なドロップダウンリスト | 従属ドロップダウンリスト | 複数選択可能なドロップダウンリスト... | |
列マネージャー:指定した数の列を追加 | 列を移動 | 非表示の列の可視化ステータスを切り替え | 列を比較して同じ&異なるセルを選択... | |
特徴的な機能:グリッドフォーカス | デザインビュー | 強化された数式バー | ワークブックとシートマネージャー | リソースライブラリ(オートテキスト) | 日付ピッカー | ワークシートの統合 | セルの暗号化/復号化 | リストによるメール送信 | スーパーフィルター | 特殊フィルタ(太字/斜体/取り消し線のフィルタリング...)... | |
上位 15 のツールセット: 12 個のテキストツール (テキストの追加, 特定の文字を削除...) | 50以上 のチャート タイプ (ガントチャート...) | 40以上の実用的な 数式 (誕生日に基づいて年齢を計算...) | 19 個の挿入ツール (QRコードの挿入, パスからの画像挿入...) | 12 個の変換ツール (単語に変換, 通貨変換...) | 7 個のマージ&分割ツール (高度な行のマージ, Excelセルの分割...) | ... さらに多くのツール |
お好みの言語で Kutools を使用可能 - 英語、スペイン語、ドイツ語、フランス語、中国語など、40以上の言語に対応!
Kutools for Excel は300以上の機能を誇り、必要なものがワンクリックで手に入ります...
Office Tab - Microsoft Office(Excel含む)でタブ形式の閲覧と編集を有効にする
- 数十の開いている文書を1秒で切り替える!
- 毎日数百回のマウスクリックを減らし、マウス手から解放されます。
- 複数の文書を閲覧・編集する際、生産性が50%向上します。
- Chrome、Edge、Firefoxのように、タブ機能をOffice(Excel含む)にもたらします。