☰

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等于0或1

​拉姆达公式（第 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是该对中的第一个或第二个成员

排rROW(r)如果第一个成员位于奇数行，则添加行号
rowOffsetROW()+rowOffset
甚至r如果的行 +rowOffset为偶数，则返回 TRUEISEVEN(ROW()+rowOffset)
如果's value if TRUE= 第 1 位成员，& value if false= 第 2 位IF(ISEVEN(ROW()+rowOffset),
#6 第一个成员将值与第二个成员进行比较
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 第二个成员将值与第一个成员进行比较
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列 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))

相关内容