☰

Excel - 値の減算による同じ ID ペアの集計

tag-icon • tag-icon microsoft-excel
Excel - 値の減算による同じ ID ペアの集計

ソースデータ:ここに画像の説明を入力してください

私が探している結果:ここに画像の説明を入力してください

これを何千回もの発生に対して実行する数式が必要です (常にペアで、フロー データです)。最小の値は、大きい値から減算する必要があります。大きい値を持つ行が残る必要があります。

答え1

データ セットは行ごとにペアでグループ化されているため、これを利用して値を識別して比較します。数式は、データの最初の行が奇数行か偶数行かをチェックします。これを使用して、すべてのペアを識別します。

odd; even, odd; even, odd; even,等
または
even; odd, even; odd, even; odd,等

式

  1. 数式では、data数式を簡単に理解し、維持できるように、名前付き範囲を使用します。$A2:$C1000
  2. 質問には、ペアの 2 つのメンバーが等しい値を持つことは決してない、または等しい値を持つとしてもそれは重要ではないという基本的な前提があります。等しい値を持つ場合は、 のため、両方とも結果から除外されますx-x=0。
=LET(
   rowOffset, IF(ISEVEN(ROW(INDEX(data,1,1))),0,1),
   newValues,
      MAP(INDEX(data,,3), LAMBDA(r,IF(r<>"",
         IF(ISEVEN(ROW(r)+rowOffset),
             IF(r>OFFSET(r,1,0),r-OFFSET(r,1,0),0),
                 IF(r>OFFSET(r,-1,0),r-OFFSET(r,-1,0),0)),0))),
   newData, HSTACK(INDEX(data,,1), INDEX(data,,2), newValues),
   FILTER(newData, INDEX(newData,,3)>0))

ノート

Line |  Code
-----+------------------------------------------------
  1  |  =LET(
  2  |   rowOffset, IF(ISEVEN(ROW(INDEX(data,1,1))),0,1),
  3  |   newValues,
  4  |      MAP(INDEX(data,,3), LAMBDA(r,IF(r<>"",
  5  |         IF(ISEVEN(ROW(r)+rowOffset),
  6  |             IF(r>OFFSET(r,1,0),r-OFFSET(r,1,0),0),
  7  |                 IF(r>OFFSET(r,-1,0),r-OFFSET(r,-1,0),0)),0))),
  8  |   newData, HSTACK(INDEX(data,,1), INDEX(data,,2), newValues),
  9  |   FILTER(newData, INDEX(newData,,3)>0))
ライン ノート
＃1 使用させて中間計算を保存し、数式内の名前を定義できるようにする機能。
＃2 rowOffset等しい0か1

​ラムダ式（4行​​目）は、最初のペアのメンバーが偶数行にあると想定しています。
rowOffset必要に応じて行番号をオフセットします。
索引data範囲の行1列1を識別するために使用されますINDEX(data,1,1)
行インデックスされたセルの行番号を取得しますROW(INDEX(data,1,1))
アイセブン行番号が偶数の場合はTRUEを返しISEVEN(ROW(INDEX(data,1,1))
、最後にもし偶数の場合は0、奇数の場合は1IF(ISEVEN(ROW(INDEX(data,1,1))),0,1)
＃3 newValues結果を格納するために宣言するラムダ関数
＃4 ラムダ関数は値の列を作成します0またはペア間の違い。

索引data3列目 の古い値を取得するために使用されます。INDEX(data,,3)
インデックスされた列地図にラムダ機能MAP(INDEX(data,,3),
​ラムダ定義してr、マップされた値を保持しますMAP(INDEX(data,,3), LAMBDA(r,
もしステートメントは残りの部分をスキップしますラムダマッピングされた値が空白の場合IF(r<>"",
＃5 rペアの最初か2番目のメンバーかを確認します

行rROW(r)最初のメンバーが奇数行にある場合、行番号が追加されます。
rowOffsetROW()+rowOffset
アイセブンrの行 +rowOffsetが偶数の場合にTRUEを返しますISEVEN(ROW()+rowOffset)
もし's value if TRUE= 1番目のメンバー、& value if false= 2番目IF(ISEVEN(ROW()+rowOffset),
＃6 1番目のメンバーは2番目のメンバーと値を比較します
IF(r>OFFSET(r,1,0),r-OFFSET(r,1,0),0)

もしr値より大きいかどうかをテストしますオフセット1行下、0列「真の場合の値」はIF(r>OFFSET(r,1,0),下の値
から減算し、rr-OFFSET(r,1,0)
「偽の場合の値」は0
＃7 2番目のメンバーは1番目のメンバーと値を比較します
IF(r>OFFSET(r,-1,0),r-OFFSET(r,-1,0),0)

もしr値より大きいかどうかをテストしますオフセット1行上に0列「真の値」はIF(r>OFFSET(r,-1,0),上記の値
から減算され、rr-OFFSET(r,-1,0)
「偽の値」は0
＃8 newData列ID1、列ID2、およびnewValues

索引data範囲から最初の列ID1を取得しますINDEX(data,,1)
索引data範囲 から2番目の列ID2も取得しますINDEX(data,,2)
Hスタックインデックス付き列をnewValues3列の配列に結合しますnewData
HSTACK(INDEX(data,,1), INDEX(data,,2), newValues)
＃9 フィルターnewData3列目の値が0より大きい配列
FILTER(newData, INDEX(newData,,3)>0))

関連情報