☰

Excel — агрегация одинаковых пар идентификаторов с вычитанием значений

tag-icon • tag-icon microsoft-excel
Excel — агрегация одинаковых пар идентификаторов с вычитанием значений

Источник данных :введите описание изображения здесь

Результат, который я ищу:введите описание изображения здесь

Мне нужна формула, которая сделает это для тысячи случаев (всегда парами, это потоковые данные). Наименьшее значение должно быть вычтено из большего. Строка с большим значением должна быть той, которая останется.

решение1

Поскольку наборы данных сгруппированы по строкам в пары, это используется для идентификации и сравнения значений. Формула проверяет, находится ли первая строка данных на нечетной или четной строке. Двигаясь вперед, это используется для идентификации всех пар.

odd; even, odd; even, odd; even,и т. д.
или
even; odd, even; odd, even; odd,и т. д.

Формула

  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 столбца 1 dataдиапазонаINDEX(data,1,1)
РЯДполучает номер строки индексированной ячейкиROW(INDEX(data,1,1))
ДАЖЕвозвращает ИСТИНА, если номер строки четный ISEVEN(ROW(INDEX(data,1,1))
и, наконец,ЕСЛИчетное, то 0, или 1 для нечетногоIF(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четная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),
"Значение, если истинно" — это вычесть из rзначения ниже r-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),
"Значение, если истина" - это вычесть из rзначения выше e r-OFFSET(r,-1,0)
"Значение, если ложь" - это0
#8 Построить массив newDataиз столбца ID 1, столбца ID2 иnewValues

ИНДЕКСполучает первый столбец ID1 из dataдиапазонаINDEX(data,,1)
ИНДЕКС также получает второй столбец ID2 из dataдиапазонаINDEX(data,,2)
HSTACKобъединяет индексированные столбцы newValuesв массив из 3 столбцовnewData
HSTACK(INDEX(data,,1), INDEX(data,,2), newValues)
#9 ФИЛЬТРмассив newData, где значения 3-го столбца > 0
FILTER(newData, INDEX(newData,,3)>0))

Связанный контент