Excelで選択されたセルを細くする。エクセル。条件付きフォーマットの基準を満たすセルのカウントと合計

スプレッドシート内のセルを各秒、3番目、4番目など）を要約する必要があることがよくあります。今、次のトリックのおかげで、それはできます。

Excelはそれぞれを要約することができる標準機能を提供しませんセルごとにまたは文字列。ただし、いくつかのタスクを実行できます違う方法。これらすべてのアプローチは文字列関数（行）に基づいており、（mod）のままです。

行関数（行） ロシア語バージョンのExcel row（Link）で、指定されたリンクの行番号をセル：行（参照）に返します。
関数の左（mod） ロシアのバージョンのExcel氏（Number; Divider）のDivider：MOD（番号除数）への番号のバランスを返します。

回線関数（行）を関数（mod）（数値引数を転送するため）に配置し、2でvigame（各セルセルを要約するため）で、結果がゼロかどうかを確認します。もしそうであれば、セルは合計される。これらの機能はさまざまな方法で使用できます - 一部は他の人よりも最良の結果を保証します。たとえば、$ 1 1：$と100ドルの範囲の各2番目のセルの合計の配列式は、次のようになります。\u003d sum（mod（mod（row（$ 1：$ 500）; 2）\u003d 0; $ A $ 1：$ A $ 500; 0））、Excel \u003d金額（残り（列（$ A $ 1：$ 500）; 2）\u003d 0; $ a $ 1：$と500ドル; 0））。

これは配列式ですので、Ctrl + Shift +キーの組み合わせを押すと、Excelがこのように見えるブレースを追加します。（\u003d sum（mod（mod（row（$ 1：$ 500）） 2）\u003d 0; $ A $ 1：$ a $ 500; 0））、Excelのロシア版:( \u003d額（\u003d残量（列（$ 1：$ 500）; 2）\u003d 0; $ 1：$ A $ 500; 0））））Excel自身がフィギュアブラケットを追加する必要があります。あなたが自分でそれらを追加するならば、式はうまくいきません。

目標は達成されますが、この方法は設計に悪影響を及ぼす。スプレッドシート。これはアレイ式の不要な使用です。ケースを劣化させるためには、文字列（行）の再計算関数がこの長い式に投資され、これは大きな式を再計算されたものに変換する。これは、それがあなたがワークブックで何をしても、それが常に翻訳されることを意味します。これは非常に悪い方法です！

これがもう一つの式です最善の選択：\u003d Sumproduct（（mod（$ a $ 1：$ a $ 500）; 2）\u003d 0）*（$ A $ 1：$ A $ 500））、ロシア版のExcel \u003d Summacy（（（（文字列（$ A $ 1：$ A $ 500）; 2）\u003d 0）*（$ A $ 1：$ 500））。

ただし、この式はエラーを返すことを忘れないでください。（#value！）範囲の範囲に数字が含まれていない場合はテキストが含まれていない場合。この式は実際には配列式ではありませんが、遅くなります。エクセルワークあなたがそれを何度も使うならば、またはそれが大きな範囲を指すたびに。

幸いなことに、そこにいる一番いい方法これは、より効率的ではなく、もっと柔軟な決定だけでもあります。 BDSMM機能（DSUM）の使用が必要です。この例では、各N - Mをセルに要約する必要がある範囲として、A1：A500の範囲を使用した。

E1セルでは、単語基準を入力してください。 E2セルでは、次の式を入力します。\u003d mod（行（a2） - $ c $ 2-1; $ c $ 2）\u003d 0、ロシア語のExcel \u003d残骸（String（A2） - $ C $ 2-1; $С$ 2）\u003d 0。 C2セルを選択して[データ]→[チェック（データ→検証）]を選択します。

[データ型（許可）]フィールド、[リスト]（リスト）、および[ソース]フィールド（ソース）で、1,2,3,4,5,6,7,8,9,10を入力します。リストチェックボックスを確認してください。チェックされます。許容値（セル内）、そしてOKボタンをクリックしてください。 C1セルで、すべてのSUMのテキストを入力します。行1を除く任意のセルで、次の式を入力します。\u003d DSUM（$ A：$ A; 1; $ E $ 1：$ E $ 2）、Excel \u003d BDSMMのロシア語\u003d BDSMM（$ A：$ A; 1; $ E $ 1：$ E $ 2）。

BDSMM関数（DSUM）を入力したときに直接セルで、2ドルで「every」＆$を「毎回合計」と入力して、（$ C $ 2; "ST"; "rd"; 「TH」;「TH」;「TH」;「TH」;「TH」;「セル」。これでC2セル内の希望の数を選択するだけで、残りはBDSMM機能（DSUM）を作成します。

BDSMM機能（DSUM）を使用して、指定した間隔でセルを要約することができます。 BDSMM（DSUM）関数は、配列式またはSumproduct関数よりもはるかに効率的です。設定はもう少し時間がかかりますが、これは学ぶのが難しい場合は、戦いが簡単です。

以前は、ユーザー関数を見つける方法を説明しました。残念ながら、セルが使用して描画されている場合、この機能は機能しません。条件付き書式。私は機能を「ファイナイズ」することを約束しました。しかし、メモの出版から渡された2年で、私は回答者のコードを書くためにインターネットからの情報の助けを借りてもまたは 2017年3月29日に更新されます 5年後、私はまだコードを書くことができました。ノートの最終リストを参照してください。そして最近私はD. Choli、R. Holi "Excel 2007. Tricks"の本に含まれているアイデアに出会いました。

A1：A100の範囲に配置された1から100までの数字のリストがあるとします（図1;シート「SMERE」Excelファイルも参照）。適用された条件付きフォーマットの範囲、10を超える数および20以上の数を含むマーキングセル。

図。数の範囲条件付きフォーマット10から20の値を含む強調表示されたセル

フォーマットでメモをダウンロードしてください

これで、インストールされている基準を満たすセル内の値を折りたたむ必要があります。これらのセルにはどのようなフォーマットが適用されているかは関係ありませんが、セルが割り当てられているかに応じて基準を知る必要があります。

回答するセルの範囲を折りたたみます 1 基準では、サイレントの機能を使用できます（図2）。

図。 1つの状態を満たすセルの合計

あなたが持っている場合 いくつか 条件、Summelimn関数を使用できます（図3）。

図。いくつかの条件を満たす細胞の合計

1つの基準を満たすセルの数を数えるには、メーター機能を使用できます。

いくつかの基準を満たすセルの数を数えるには、MeckeStimn機能を使用できます。

Excelは、いくつかの条件を指定できるようにする別の機能を提供します。この機能はデータベース機能セットに含まれています。 excelデータそしてBDSUMMと呼ばれます。確認するには、A2範囲で同じ数字のセットを使用します.A100（図4; BDSMMリストExcelファイルも参照）。

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

C1：D2セルを選択し、式の文字列の左名フィールドに入力して、この範囲に基準の名前を割り当てます。これで、C1セルを選択し、$ A $ 1、つまりデータベースの名前を含むシートの最初のセルへの参照を選択します。 Enter \u003d $ A $ 1をD1セルに$ 1に入れると、列ヘッダーの2コピーが表示されます。これらのコピーは、基準と呼ばれるBDSMM条件（C1：D2）の見出しとして使用されます。 C2セルでは、\u003e 10を入力してください。 D2セルに入る<=20. В ячейке, где должен быть результат, введите следующую формулу:

BDSMM（$ A $ 1：$と$ 101,1、基準）

いくつかの基準を満たすセルの数を計算するには、BSC関数を使用できます。

本を読むJohn Waolenbach私は、Excel 2010バージョンから始めて、新しいDisplayFormatプロパティがVBAに表示されたことを学びました（たとえば、range.displayformatプロパティを参照）。これらの。また、VBAは画面に表示されている形式を読むことができます。それがどのようにして得られたか、直接ユーザー設定、または条件付き書式によっては関係ありません。残念ながら、MS開発者は、DisplayFormatプロパティがVBAから呼び出された手順でのみ機能するようにしています。このプロパティに基づくユーザー関数はエラー＃意味を与えます！ただし、手順（マクロ、機能しない）を使用して、特定の色のセルの範囲内の値の量を取得することができます。 Open（VBAコードを含みます）。メニューを通過します見る -> マクロ -> マクロ; 窓の中に 大きい、行を強調表示します スコーズベトスそして、を押す 演奏する。マクロを実行し、合計範囲と基準を選択します。答えがウィンドウに表示されます。

手順コード

sub sumyzvetusl（）application.volatile true dim sumcolor and Request Sumcolor \u003d 0 "Request Set UserRange \u003d Application.InputBox（_プロンプト：\u003d"合計範囲の選択 "、_ title：\u003d"範囲選択 "、_デフォルト：\u003d ActiveCell.Address、_ Type：\u003d 8） "Criterine Request Set CriterionRange \u003d Application.InputBox（_プロンプト：\u003d" 基準合計"、_タイトル：\u003d" Critter Selection "、_ Default：\u003d ActiveCell.Address、_ Type：\u003d 8）" i.color \u003d _ criterionRange.Color \u003d _ CriterionRange.DisplayFormat。interial.colorその後sumcolor \u003d sumcolor + I End次のmsgbox sumcolorエンドサブ

サブSumyzvetus（）

応用。揮発性の真

Dim Sumcolor Double

範囲として薄暗い

範囲としてDim UserRange

範囲としてのDIM CRITERIONRANGE

sumcolor \u003d 0。

"リクエスト範囲

set userRange \u003d Application.InputBox（_

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

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

デフォルト：\u003d activeCell.address、_

タイプ：\u003d 8）

"リクエスト

CriterionRange \u003dアプリケーションを設定します。入力ボックス（_

プロンプト：\u003d。「合計基準を選択」, _

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

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

営業担当者の販売に関するこの報告があるとします。

それからあなたはいくらを見つける必要があります鉛筆販売代理人と言った イワノビ に 1月.

問題：複数の基準に関するデータを要約する方法？

決定方法1：

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

英語版では：

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

使い方：

私たちが指定したデータベースから A1：G16。 関数 BDSUMM. 列データを削除して合計します数（引数 " フィールド" = F1セルで指定されたセルに従って I1：K2。 (売り手\u003d Ivanov.; 製品\u003d鉛筆; 月\u003d 1月）基準

短所：基準のリストはシート上にあるべきです。

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

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

方法2：

サミツィス（（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）

使い方：

Sump関数は、Excelのメモリで、選択された基準に従って、真実と嘘の値から配列を生成します。

計算がシートセル内で実行された場合（明確にするために、私は計算のすべての作業を説明します。

たとえば、 D2 \u003d鉛筆それから値は真実と等しくなり、なら D3 \u003dフォルダそして、FALSE（私たちの例の商品の選択基準としての価値が価値があります鉛筆).

真実の価値が常に1に等しく、常に0に等しい。数字0と1と同じように配列で動作し続けます。
一貫して自分の間のアレイの結果の値を交互に交互にすると、1つの非OLSとユニットの配列が取得されます。 3つの選択基準すべてが実行された場所（ イヴァノフ、鉛筆、1月すなわちすべての条件は、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でまとめることは乗算しませんでした。