☰

Excel - 透過值減法聚合相同的 ID 對

tag-icon • tag-icon microsoft-excel
Excel - 透過值減法聚合相同的 ID 對

來源資料：在此輸入影像描述

我正在尋找的結果：在此輸入影像描述

我想要一個公式來對數千次出現執行此操作（始終成對，它是流資料）。應從較大值中減去最小值。具有較大值的線應該是剩下的線。

答案1

由於資料集按行成對分組，因此可用於識別和比較值。這個公式檢查第一行資料是在奇數行還是偶數行。繼續前進，用於識別所有對。

odd; even, odd; even, odd; even,ETC。
或者
even; odd, even; odd, even; odd,ETC。

公式

  1. 此公式使用命名範圍data使公式更易於遵循和維護$A2:$C1000
  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等於或01

蘭姆達公式（第 4 行）假設第一對成員位於偶數行。
rowOffset根據需要偏移行號
指數用於標識範圍的第 1 行第 1data列INDEX(data,1,1)
排取得索引單元格的行號ROW(INDEX(data,1,1))
甚至如果行號為偶數ISEVEN(ROW(INDEX(data,1,1))
，則傳回 TRUE，最後如果偶數則為 0，奇數為 1IF(ISEVEN(ROW(INDEX(data,1,1))),0,1)
#3 聲明newValues儲存結果蘭姆達功能
#4 蘭姆達函數建立值列0或一對之間的差異。

指數 用於從data第 3 列INDEX(data,,3)
索引列中取得舊值地圖進入蘭姆達MAP(INDEX(data,,3),函數
蘭姆達定義r保存映射的值MAP(INDEX(data,,3), LAMBDA(r,
如果語句跳過其餘部分蘭姆達如果映射的值為空IF(r<>"",
#5 檢查是否r是該對中的第一個或第二個成員

排給出我們r的行號ROW(r)
rowOffset，以防第一個成員位於奇數行ROW()+rowOffset
甚至r如果的行 +rowOffset為偶數，則傳回 TRUEISEVEN(ROW()+rowOffset)
如果's value if TRUE= 第一個成員，& value if false= 第二個成員IF(ISEVEN(ROW()+rowOffset),
#6 第一個成員將數值與第二個成員進行比較
IF(r>OFFSET(r,1,0),r-OFFSET(r,1,0),0)

如果測試是否r大於該值抵銷下面 1 行和 0 列「Value if true」是從IF(r>OFFSET(r,1,0),「Value if false」下面的值
中減去rr-OFFSET(r,1,0)
0
#7 第二個成員將數值與第一個成員進行比較
IF(r>OFFSET(r,-1,0),r-OFFSET(r,-1,0),0)

如果測試是否r大於該值抵銷上面 1 行和 0 列「Value if true」是從IF(r>OFFSET(r,-1,0),上面的值
中減去e rr-OFFSET(r,-1,0)
“Value if false”是0
#8 newData從列 ID 1、列 ID2 和列建置數組newValues

指數data從範圍中取得第一列 ID1INDEX(data,,1)
指數data也從範圍 中取得第二列 ID2INDEX(data,,2)
堆疊將索引列組合newValues成一個 3 列數組newData
HSTACK(INDEX(data,,1), INDEX(data,,2), newValues)
#9 篩選數組newData，其中第三列值 > 0
FILTER(newData, INDEX(newData,,3)>0))

相關內容