Excelで個々のセルを要約する方法。 Excel。条件付き書式の条件を満たすセルをカウントして合計する

スプレッドシートの2番目、3番目、4番目などのセルごとに合計する必要がある場合がよくあります。今、次のトリックのおかげで、それを行うことができます。

Excelは、それぞれを合計できる標準関数を提供していません n番目のセルまたは文字列。ただし、このタスクはいくつかの方法で実行できます違う方法..。これらのアプローチはすべて、ROW関数とMOD関数に基づいています。

ROW関数 指定されたセル参照の行番号を返します：ROW（参照）、ロシア語版のExcel ROW（参照）。
OSTAT関数（MOD） 数値を除数で除算した余りを返します：MOD（数値;除数）、ロシア版のExcel OSTAT（数値;除数）。

ROW関数をMOD関数に入れ（数値引数を渡すため）、2で除算し（1つおきのセルを合計するため）、結果がゼロでないかどうかを確認します。その場合、セルは合計されます。これらの関数はさまざまな方法で使用できます。一部の関数は他の関数よりも優れた結果を提供します。たとえば、$ A $ 1：$ A $ 100の範囲の1つおきのセルを合計する配列数式は次のようになります。\u003d SUM（IF（MOD（ROW（$ A $ 1：$ A $ 500）; 2） \u003d 0; $ A $ 1：$ A $ 500; 0））、Excelのロシア語バージョン\u003d SUM（IF（OSTAT（LINE（$ A $ 1：$ A $ 500）; 2）\u003d 0; $ A $ 1：$ A $ 500; 0））。

これは配列数式であるため、Ctrl + Shift + Enterを押して入力する必要があります。Excelでは中括弧が追加され、次のようになります。（\u003d SUM（IF（MOD（ROW（$ A $ 1：$ A $ 500）、2）\u003d 0; $ A $ 1：$ A $ 500; 0）））、Excelのロシア語バージョン：（\u003d SUM（IF（Remaining（LINE（$ A $ 1：$ A $ 500） ; 2）\u003d 0; $ A $ 1：$ A $ 500; 0）））中括弧を単独で追加するにはExcelが必要です。自分で追加すると、数式は機能しません。

目標は達成されますが、この方法は設計に悪影響を及ぼします。スプレッドシート..。これは、配列数式の不要なアプリケーションです。さらに悪いことに、この長い数式には、大きな数式を再計算可能な関数に変換する再計算可能なROW関数が含まれています。これは、ワークブックで何をしても、常に再計算されることを意味します。これは非常に悪い方法です！

これは少しある別の式です最善の選択：\u003d SUMPRODUCT（（MOD（ROW（$ A $ 1：$ A $ 500）; 2）\u003d 0）*（$ A $ 1：$ A $ 500））、ロシア語版のExcel \u003d SUMPRODUCT（（REST （ROW（$ A $ 1：$ A $ 500）; 2）\u003d 0）*（$ A $ 1：$ A $ 500））。

ただし、この数式は#VALUEを返すことに注意してください。（#VALUE！）範囲内のセルに数値ではなくテキストが含まれている場合。この数式は、実際には配列数式ではありませんが、速度も低下します。 excelの仕事何度も使用したり、毎回広い範囲を参照したりする場合。

幸いなことにあります最良の方法、これはより効率的であるだけでなく、はるかに柔軟です。 DSUM関数を使用する必要があります。この例では、n番目ごとのセルを追加する必要がある範囲として範囲A1：A500を使用しました。

セルE1にCriteriaという単語を入力します。セルE2に次の数式を入力します。\u003d MOD（ROW（A2）-$ C $ 2-1; $ C $ 2）\u003d 0、ロシア語版のExcel \u003d OSTAT（ROW（A2）-$ C $ 2- 1; $ C $ 2）\u003d 0。セルC2を選択し、[データ]→[検証]コマンドを選択します。

[許可]フィールドで[リスト]を選択し、[ソース]フィールドに1、2、3、4、5、6、7、8、9、10と入力します。チェックボックスがオンになっていることを確認します。（セル内）、および[OK]ボタンをクリックします。セルC1に、テキストSUMevery…を入力します。行1以外のセルに、次の数式を入力します。\u003d DSUM（$ A：$ A; 1; $ E $ 1：$ E $ 2）、ロシア語版のExcel \u003d BDSUMM（$ A：$ A; 1; $ E $ 1：$ E $ 2）。

DSUM関数を入力したセルのすぐ上のセルに、テキスト\u003d "Summing Every"＆$С$ 2＆CHOOSE（$ C $ 2; "st"; "nd"; "rd"; "th" ; "th"; "th"; "th"; "th"; "th"; "th"）＆ "セル"。残っているのはセルC2で目的の番号を選択することだけで、残りはDSUM関数が実行します。

関数BDSUM（DSUM）を使用すると、指定した間隔でセルを合計できます。 DSUM関数は、配列数式やSUMPRODUCT関数よりもはるかに効率的です。セットアップには少し時間がかかりますが、これは習得が難しく、戦いやすい場合です。

先ほど、を見つける方法を説明しました。残念ながら、セルがで着色されている場合、この機能は機能しません条件付き書式..。機能を「洗練」することを約束しました。しかし、そのメモが発行されてから2年が経ちましたが、私は自分で、またはインターネットからの情報を使用して、消化可能なコードを書くことができませんでした...（ 2017年3月29日現在の更新 さらに5年後、私はまだコードを書くことができました。メモの最後の部分を参照してください）。そしてつい最近、D。Hawley、R。Hawleyの本「Excel2007。Tricks」に含まれているアイデアに出くわしました。これにより、コードなしで実行できます。

A1：A100の範囲にある1から100までの番号のリストがあるとします（図1。Excelファイルの「SUMIF」シートも参照してください）。範囲は、10より大きく20以下の数値を含むセルをマークするように条件付きでフォーマットされています。

図： 1.数値の範囲; 10から20までの値を含む強調表示されたセルの条件付き書式

フォーマットのメモ、フォーマットの例をダウンロード

次に、設定した基準を満たすセルに値を追加する必要があります。これらのセルにどのようなフォーマットを適用するかは問題ではありませんが、セルを強調表示する基準を知っておく必要があります。

一致するセルの範囲を合計するには 1 基準として、SUMIF関数を使用できます（図2）。

図： 2.1つの条件を満たすセルの合計

あなたが持っている場合 いくつか 条件があれば、SUMIFS機能を使用できます（図3）。

図： 3.いくつかの条件を満たすセルを合計する

COUNTIF関数を使用して、1つの基準を満たすセルの数を数えることができます。

COUNTIF関数を使用して、複数の条件を満たすセルの数を数えることができます。

Excelには、複数の条件を指定できる別の関数が用意されています。この関数は一連の関数に含まれています excelデータ BDSUMMと呼ばれます。これを確認するには、A2：A100の範囲の同じ番号のセットを使用します（図4。Excelファイルの「BDSUMM」シートも参照してください）。

図： 4.データベース機能の使用

セルC1：D2を選択し、数式バーの左側にある名前ボックスに入力して、この範囲にCriterionという名前を付けます。次に、セルC1を選択し、\u003d $ A $ 1と入力します。これは、データベースの名前を含むシートの最初のセルへの参照です。セルD1に\u003d $ A $ 1と入力すると、列見出しAのコピーが2つ取得されます。これらのコピーは、Criterionという名前の条件BDSUMM（C1：D2）のヘッダーとして使用されます。セルC2に、\u003e 10と入力します。セルD2に次のように入力します<=20. В ячейке, где должен быть результат, введите следующую формулу:

BDSUMM（$ A $ 1：$ A $ 101.1、基準）

COUNT関数を使用して、複数の条件を満たすセルの数をカウントできます。

John Walkenbachの本を読んで、Excel 2010以降、VBAに新しいDisplayFormatプロパティがあることを学びました（たとえば、Range.DisplayFormatプロパティを参照）。つまり、VBAは画面に表示されている形式を読み取ることができます。直接のユーザー設定や条件付き書式の使用によってどのように受信されたかは関係ありません。残念ながら、MSは、DisplayFormatプロパティがVBAから呼び出されたプロシージャでのみ機能するように設定しており、このプロパティに基づくUDFは#VALUEをスローします。ただし、プロシージャ（マクロですが関数ではありません）を使用して、特定の色のセルごとに範囲内の値の合計を取得できます。開く（VBAコードを含む）。メニューに目を通す見る -> マクロ -> マクロ; ウィンドウ内 大きい、行を強調表示します SumColorUsl、を押します 実行する..。マクロを実行し、合計範囲と基準を選択します。答えがウィンドウに表示されます。

手順コード

Sub SumColorConv（）Application.Volatile True Dim SumColor As Double Dim i As Range Dim UserRange As Range Dim CriterionRange As Range SumColor \u003d 0 "Range query Set UserRange \u003d Application.InputBox（_ Prompt：\u003d" Select summation range "、_ Title： \u003d "範囲の選択"、_デフォルト：\u003d ActiveCell.Address、_タイプ：\u003d 8） "基準リクエストセットCriterionRange \u003d Application.InputBox（_プロンプト：\u003d"選択合計基準"、_ Title：\u003d" Criterion selection "、_ Default：\u003d ActiveCell.Address、_ Type：\u003d 8）" UserRange If i.DisplayFormat.Interior.Color \u003d _CriterionRange.DisplayFormatの各iの「正しい」セルの合計.Interior.Color Then SumColor \u003d SumColor + i End If Next MsgBox SumColor End Sub

サブSumColorUl（）

応用。揮発性True

SumColorを2倍に薄暗く

Dim i As Range

範囲としての薄暗いUserRange

範囲としての薄暗いCriterionRange

SumColor \u003d 0

"範囲クエリ

UserRange \u003d Application.InputBox（_

プロンプト：\u003d "合計範囲を選択"、_

タイトル：\u003d "範囲の選択"、_

デフォルト：\u003d ActiveCell.Address、_

タイプ：\u003d 8）

「Proscriterion

CriterionRange \u003d Applicationを設定します。 InputBox（_

プロンプト：\u003d 「合計基準を選択してください」, _

タイトル：\u003d "基準の選択"、_

デフォルト：\u003d ActiveCell。住所、 _

次のような販売レポートがあるとします。

それからあなたはどれくらいを知る必要があります鉛筆営業担当者が販売 イワノフ に 1月.

問題：いくつかの基準でデータを要約する方法??

決定：方法1：方法1：

BDSUMM（A1：G16; F1; I1：K2）

英語版：

DSUM（A1：G16、F1、I1：K2）

使い方：

指定したデータベースから A1：G16 関数 BDSUMM 列データを取得して要約します数（引数 " フィールド" = F1）セルで与えられたに従って I1：K2 (売り手\u003dイワノフ; 製品\u003d鉛筆; 月\u003d 1月）基準。

短所：基準のリストはシートに記載されている必要があります。

ノート：合計基準の数はRAMによって制限されます。

アプリケーションエリア：任意のバージョンのExcel

方法2：

SUMPRODUCT（（B2：B16 \u003d I2）*（D2：D16 \u003d J2）*（A2：A16 \u003d K2）* F2：F16）

英語版：

SUMPRODUCT（（B2：B16 \u003d I2）*（D2：D16 \u003d J2）*（A2：A16 \u003d K2）* F2：F16）

使い方：

SUMPRODUCT関数は、選択した基準に従って、ExcelメモリにTRUE値とFALSE値の配列を形成します。

計算がシートのセルで実行された場合（わかりやすくするために、計算がメモリではなくシートで行われているように数式の全体の作業を示します）、配列は次のようになります。

たとえば、次の場合は明らかです。 D2 \u003d鉛筆の場合、値はTRUEになり、 D3 \u003dフォルダ、次にFALSE（この例で製品を選択するための基準は値であるため）鉛筆).

TRUEは常に1に等しく、FALSEは常に0に等しいことを知っているので、数値0および1と同様に配列を引き続き処理します。
得られた配列の値を順番に乗算すると、0と1の配列が1つ得られます。 3つの選択基準すべてが満たされた場合、（ IVANOV、鉛筆、1月）つまりすべての条件がTRUEの値を取り、1（1 * 1 * 1 \u003d 1）を取得しますが、少なくとも1つの条件が満たされない場合、0（1 * 1 * 0 \u003d 0; 1 * 0 * 1 \u003d 0; 0 * 1 * 1 \u003d 0）。

これで、結果の配列に、結果として合計する必要のあるデータを含む配列を乗算するだけで済みます（範囲 F2：F16）そして、実際には、0を掛けていないものを合計します。