☰

列Aの特定の値を持つ前の行を検索します

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
列Aの特定の値を持つ前の行を検索します

数式付きと数式なしのサンプルシート

セマンティックな「ヘッダー」、「サブヘッダー」、および「小計」行がある上記のサンプル シートを前提として、現在のセルを基準として前のサブヘッダー行を見つける数式を決定しようとしています。たとえば、数式を に入力するとF5行 が見つかり2、 に入力するとF17行 が見つかります13。

H行は、値、SまたはT列の存在によって、ヘッダー、サブヘッダー、または小計として条件付きで書式設定されます。つまり、$A:$Aサブヘッダー行nは の行です$An = "S"。ここで、この概念を数式に拡張したいと思います。

ヘッダー行の後には必ずサブヘッダーが続きます (そのため、ヘッダーとサブヘッダーの順序が間違っていることを心配する必要はありません)。

私は次のことを試しました:

  1. =MATCH("S", OFFSET($A5, 0, 0, -ROW($A5), 1), 0)

    これは常に行を返します2。MATCH初めセット内で一致し、OFFSET高さを制限することはできません (つまり、前のサブヘッダーの位置が不明であるため、再帰的に)。

  2. {=LARGE(MATCH("S", OFFSET($A5, 0, 0, -ROW($A5), 1), 0), 1)}

    これも を返します2。配列コンテキスト (つまり、Ctrl+ Alt+を使用Enter) でも、MATCH最初の結果のみを返すためです。

  3. =LARGE(IF(OFFSET($A5, 0, 0, -ROW($A5), 1)="S", ROW(OFFSET($A5, 0, 0, -ROW($A5), 1))), 1)

    これは を返します。ここでは配列を期待していない0ため、 は単一の値 に展開されますが、これは と一致しないため、数値として扱われます。IFOFFSET($A5, 0, 0, -ROW($A5), 1)0"S"LARGEFALSE

  4. {=LARGE(IF(OFFSET($A5, 0, 0, -ROW($A5), 1)="S", ROW(OFFSET($A5, 0, 0, -ROW($A5), 1))), 1)}

    これは を返します#VALUE。配列の拡張が早すぎるため、-ROW($A5)配列が残りますが、これは の有効な数値パラメータでは-{5}ありません(ビットを ではなく配列にしたかったのですが、Excel では区別できません)。heightOFFSETIF(OFFSET(...)="S",...)-ROW($A5)

現在、Excel 2010 をターゲットにしています。以前のバージョンは適用できません (ただし、上位互換性は利点です)。*.xlsx ファイルを配布するよりも *.xlsm ファイルを配布する方が困難なため、VBA は避けるようにしています (また、VBA で配布する方法はすでにわかっています)。

他に試せることはありますか?

答え1

これを行う簡単な方法は、絶対/相対の混合数式をチートして使用することです。これは、セルに入力された配列数式 (CTRL+SHIFT+ENTER で入力) ですB4が、行 4 のどこにでも配置できます。マークされた行の行番号を返しますS。

=MAX(IF($A$1:A4="S",ROW($A$1:A4)))

コピーすると、参照の2番目の部分B4 and A4が増加します。これにより、最大の一致を持つ行が確実に取得されます。その上現在の行。F4関連する範囲を入力/選択した後、 を使用してこれらの数式をより速く入力できます。 これにより、すべての選択肢でドル記号が循環します。

範囲の写真

データと結果の画像

数式を置き換えるために使用

質問を少し読んだ後（そして @SteveTaylor による編集に基づいて）、これを使用する目的は数式を更新することであるようです。上記から返された行を使用して、INDEX合計するデータの範囲を取得できます。置き換えることができる 2 つの数式があります。

  • ラベル付けされたデータ行ごとに合計を計算します。この場合、上の小計行を動的に参照できます。
  • 小計行の合計計算。この場合、上から合計する値は動的に参照できます。

単一行データの場合は、配列数式として開始する数式を使用できますF3。 を使用するように切り替えたSUMPRODUCTため、2 列を超えるデータへの移動がはるかに簡単になったことに注意してください。

=C3*SUMPRODUCT(INDEX(D:E,MAX(IF($A$1:A3="S",ROW($A$1:A3))),),D3:E3)

合計行の数式には、 から始めてF11、再び配列数式を使用できます。

=SUM(F10:INDEX($F$1:F10, 1+MAX(IF($A$1:A11="S",ROW($A$1:A11)))))

IF1 つの数式ですべてを制御したいという場合は、列の値に基づいてこれらをネストして結合できますA。以下は、 から始まる配列数式で、F2下にコピーできます。

=IF(
  A2="S", 
  SUM(D2:E2), 
    IF(A2="T", 
      SUM(F1:INDEX($F$1:F1, 1+MAX(IF($A$1:A2="S",ROW($A$1:A2))))), 
      C2*SUMPRODUCT(INDEX(D:E,MAX(IF($A$1:A2="S",ROW($A$1:A2))),),D2:E2)))

この数式は、空白行と「データ」行を区別しません。現在、スペーサー行には 0 が返されますが、これは問題ありません。

および式の結果の画像データの 2 つのブロックに対して。

ここに画像の説明を入力してください

ここに画像の説明を入力してください

関連情報