Excel - Aggregieren derselben ID-Paare mit Wertsubtraktion

Question

Quelldaten :

Ergebnis, nach dem ich suche:

Ich hätte gern eine Formel, die das für Tausende von Vorkommen ausführt (immer paarweise, es sind Flussdaten). Der kleinste Wert sollte vom größeren abgezogen werden. Die Zeile mit dem größeren Wert sollte übrig bleiben.

Answer 1

Da die Datensätze paarweise zeilenweise gruppiert sind, wird dies genutzt, um die Werte zu identifizieren und zu vergleichen. Die Formel prüft, ob die erste Datenzeile eine gerade oder ungerade Zeile ist. In Zukunft wird dies verwendet, um alle Paare zu identifizieren.

odd; even, odd; even, odd; even,usw.
oder
even; odd, even; odd, even; odd,usw.

Formel

1. Die Formel verwendet einen benannten Bereich, dataum die Nachverfolgung und Wartung der Formel zu vereinfachen$A2:$C1000
2. Aus der Frage ergibt sich die Grundannahme, dass zwei Mitglieder eines Paares niemals gleiche Werte haben werden oder dass sie, falls doch, nicht von Interesse sind. Falls doch, werden sie beide aus den Ergebnissen herausgefiltert, da 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))

Anmerkungen

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))

Linie	Anmerkungen
#1	Verwenden Sie dieLASSENFunktion zum Speichern von Zwischenberechnungen und Definieren von Namen in der Formel.
#2	rowOffsetgleich 0oder 1 DasLAMBDADie Formel (Zeile 4) geht davon aus, dass die Mitglieder des ersten Paars in geraden Zeilen stehen. rowOffsetVerschiebt die Zeilennummer nach Bedarf INDEXdient zur Identifizierung von Zeile 1 Spalte 1 des dataBereichsINDEX(data,1,1) REIHEerhält die Zeilennummer der indizierten ZelleROW(INDEX(data,1,1)) ISTSIEBENgibt TRUE zurück, wenn die Zeilennummer gerade ist ISEVEN(ROW(INDEX(data,1,1)) und schließlichWENNes ist gerade dann 0, oder 1 für ungeradeIF(ISEVEN(ROW(INDEX(data,1,1))),0,1)
#3	Declare newValueszum Speichern des Ergebnisses derLAMBDAFunktion
#4	LAMBDAFunktion erstellt Wertespalte0oder der Unterschied zwischen einem Paar. INDEX wird verwendet, um die alten Werte aus dataSpalte 3 zu erhalten. INDEX(data,,3) Die indizierten SpaltenKARTEin dieLAMBDAFunktion MAP(INDEX(data,,3), DieLAMBDAdefiniert rto hält Werte abgebildet inMAP(INDEX(data,,3), LAMBDA(r, WENNAnweisung überspringt den Rest derLAMBDAwenn der zugeordnete Wert leer istIF(r<>"",
#5	Überprüfen Sie, ob res sich um das erste oder zweite Mitglied des Paares handelt REIHEgibt uns rdie Zeilennummer ROW(r) rowOffsetan, falls die ersten Mitglieder in ungeraden Zeilen stehenROW()+rowOffset ISTSIEBENgibt TRUE zurück, wenn rdie Zeile + rowOffsetgerade istISEVEN(ROW()+rowOffset) WENN's value if TRUE= 1. Mitglied, & value if false= 2.IF(ISEVEN(ROW()+rowOffset),
#6	1. Mitglied vergleicht Wert mit 2. Mitglied IF(r>OFFSET(r,1,0),r-OFFSET(r,1,0),0) WENNtestet, ob rgrößer als der Wert istVERSCHIEBUNGdurch 1 Zeile darunter und 0 Spalten "Wert wenn wahr" ist von IF(r>OFFSET(r,1,0),dem Wert darunter abzuziehen rr-OFFSET(r,1,0) "Wert wenn falsch" ist0
#7	2. Mitglied vergleicht Wert mit 1. Mitglied IF(r>OFFSET(r,-1,0),r-OFFSET(r,-1,0),0) WENNtestet, ob rgrößer als der Wert istVERSCHIEBUNGdurch 1 Zeile darüber und 0 Spalten "Wert wenn wahr" ist vom IF(r>OFFSET(r,-1,0),Wert darüber abzuziehen e rr-OFFSET(r,-1,0) "Wert wenn falsch" ist0
#8	Erstellen Sie ein Array newDataaus Spalte ID 1, Spalte ID2 undnewValues INDEXruft die erste Spalte ID1 aus datadem Bereich abINDEX(data,,1) INDEX erhält auch die zweite Spalte ID2 aus datadem BereichINDEX(data,,2) HSTACKkombiniert indizierte Spalten und newValuesin ein 3-Spalten-ArraynewData HSTACK(INDEX(data,,1), INDEX(data,,2), newValues)
#9	FILTERArray newData, bei dem die Werte der 3. Spalte > 0 sind FILTER(newData, INDEX(newData,,3)>0))