☰

Excel で各行の数値の合計が 0 より大きい場合にのみ、配列内の行数をカウントするにはどうすればよいですか?

tag-icon • tag-icon microsoft-excel worksheet-function
Excel で各行の数値の合計が 0 より大きい場合にのみ、配列内の行数をカウントするにはどうすればよいですか?

Excel に、ランダムな数字 (たとえば A2 から ET361) を含む 150 列 x 360 行の配列があります。

各列 (セル B1 から ET1 まで) について、その前の列の行数がゼロより大きいかどうかを計算するにはどうすればよいですか?

基準：

B1 は、0 より大きいセルの数 (A2 から A361) を計算する必要があります。C1
は、各行の合計が 0 より大きい行の数 (A2:B2、A3:B3、...、A361:B361) を計算する必要があります。D1
は、各行の合計が 0 より大きい行の数 (A2:C3、...、A361:C361) を計算する必要があります。

COUNTIF 数式を使用してみましたが、行数ではなくセル数のみが返されます。
ネストされた ROWS() および IF() 数式が必要だと思いますか? また、Excel ファイルのスペースを節約したいので、この問題に対処するために別の 150 x 360 マトリックスを作成したくありません。

また、マクロや VBA はスプレッドシートを複雑にするので、使いたくありません。

方程式全体に複雑さが 1 つ加わり、小計機能が動作しなくなりました。

マトリックス内の各セルについて、各行の列の合計が 0 より大きい行の数を計算する必要があります。Barry のソリューションは、この例では機能しません (テスト済み)。これは、「小計」数式が「小計」数式を持つセルでは機能しないためです。

他に選択肢はありますか?

答え1

私は数式による解決法を 1 つも思いつくことができませんでしたが (おそらく他の誰かが思いつくでしょう)、別の 150 x 360 マトリックスよりもスプレッドシートの占有領域がはるかに少ないものを思いつきました。

基本的な考え方は、データの 1 つの列の各行の累積合計を計算し、それをデータ テーブル (「what-if 分析」) で使用して、すべての列のカウントを生成することです。

開始点は、単一のデータ列内の行の計算の列です。

下のスクリーンショットに示すように、10 列のデータを含むワークシートを設定しました。

ヘルパー列

データの右側にヘルパー列 L を設定しました。

セル L1 にCOUNTIFは、その列内で合計がゼロより大きい行が含まれます。

行の合計については、各行の列の単純な合計（ここでも列Aのみ）ではなく、関数によって返される範囲の合計を使用しますOFFSET。この関数の形式は次のとおりです。

OFFSET(reference cell, number of rows to offset, number of columns to offset, 
       height of range to return, width of range to return)

セル L3 には最初の式がありますSUM(OFFSET(...))。セル A2 から 0 行下、0 列右、高さ 1 行、幅がセル L2 の値と同じ範囲の行の合計を計算します。この場合、L2 の値は 1 です。

この数式は 360 行にわたってコピーされ、各行で高さ 1 行、幅はセル L2 の値によって決まる範囲の合計が計算されます。

たとえば、L2 の値が 2 に変更された場合、列の数式は 360 行それぞれについて列 A と列 B の値の行ごとの合計を計算します。また、セル L1 には、範囲 A2:B361 内で合計が 0 より大きい行の数が表示されます。

単一データ列の行数を計算する

データ表

Excel のデータ テーブル機能を使用すると、計算への入力の 1 つ (または 2 つ) の値を変更した場合の計算への影響をすばやく判断できます。この機能は、リボンのタブのセクションWhat-If Analysisにあるボタンを使用して設定します。Data ToolsData

添付の画像はデータ テーブルの設定を示しています。

データ テーブルは、範囲 R1:S10 に作成されます。テーブルの上部にあるセル S1 は、入力が変化する結果セルです。この場合、結果セルには数式が保持されますが、これはヘルパー列 L の上部にある数式=L1への参照にすぎません。COUNTIF

セル R2:R10 に「what-if」値を事前に入力しました。表示されている値 (1、2、...、9) は、OFFSET が返す範囲の幅を表します。また、「列入力セル」はセル でL1、ヘルパー列で合計される行の幅を決定するセルです。

簡単に言うと、幅 1 ～ 9 (列「A」、「A:B」、「A:C」などに相当) を入力すると、データ テーブルは、それらの列の範囲ごとに合計が 0 より大きい行の数を計算します。

データテーブルの設定

最後の図は最終結果を示しています。データ テーブルでは、入力データの各列の行数、つまり 0 より大きい行単位の合計 (前の列の合計) の数を計算しています。これらの数は、データ テーブルのセル S2:S10 に返されました。関数を使用して、元のデータの最初の行に数を転送しましたTRANSPOSE。

最終結果

すべての計算を含むサンプルワークシートは入手可能ですここ。

答え2

あなたが尋ねていることを正しく理解しているなら、一番上の行に各列の合計数を表示したいということですね。個々の細胞その前のすべての列の値が 0 より大きい。正しいですか?

もしそうなら、これは非常に簡単でCountIf、$符号を使用して参照をロックします。

セル B1 に と入力します=CountIf($A2:A361,">0")。クリックして右にドラッグします。$記号は をロックしA、常に列 A と現在の列の間にあるすべてのものをカウントします。ドラッグすると、数式は次のようになります。

  • C1=Countif($A2:B361,">0")
  • D1:=Countif($A2:C361,">0")
  • E1:=Countif($A2:D361,">0")
  • 等...

CountIf範囲全体をカウントできるので、一度に 1 つのセルまたは数式を選択する必要はありません。この方法を使用すると、現在の列の左側にあるすべてのセルを簡単にカウントできます。

答え3

OFFSET関数を使用すると、範囲内の個々の行を分離できます...そして、各行を合計しSUBTOTAL、0より大きい行を数えることがSUMPRODUCTできます。したがって、B1にコピーされたこの数式は、ヘルパーセルなしで機能します。

=SUMPRODUCT((SUBTOTAL(9,OFFSET($A2:A2,ROW(A2:A361)-ROW(A2),0))>0)+0)

それは、説明したのと同様の技術を使用するここ[ここではフィルタリングはありませんが、OFFSET によって生成された各範囲を合計するには SUBTOTAL を使用する必要があります]

それはchuffの解決策と同じ結果をもたらすだろう

関連情報