☰

Excel: agregar los mismos pares de ID con resta de valores

tag-icon • tag-icon microsoft-excel
Excel: agregar los mismos pares de ID con resta de valores

Datos fuente :ingrese la descripción de la imagen aquí

Resultado que estoy buscando:ingrese la descripción de la imagen aquí

Me gustaría una fórmula para hacer esto para miles de ocurrencias (siempre por par, son datos de flujo). El valor más pequeño debe restarse del mayor. La línea con el valor mayor debe ser la que quede.

Respuesta1

Debido a que los conjuntos de datos están agrupados por filas en pares, esto se aprovecha para identificar y comparar los valores. La fórmula verifica si la primera fila de datos está en una fila par o impar. Avanzando, se utiliza para identificar todos los pares.

odd; even, odd; even, odd; even,etc.
o
even; odd, even; odd, even; odd,etc.

Fórmula

  1. La fórmula utiliza un rango con nombre datapara que sea más fácil de seguir y mantener.$A2:$C1000
  2. De la pregunta se desprende una suposición básica de que dos miembros de un par nunca tendrán valores iguales o que, si los tienen, no son de interés. Si lo hacen, ambos serán excluidos de los resultados desde 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))

Notas

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))
Línea Notas
#1 Utilizar elDEJARfunción para permitir almacenar cálculos intermedios y definir nombres en la fórmula.
#2 rowOffsetigual 0o 1

elLAMBDALa fórmula (línea 4) supone que los miembros del primer par están en filas pares.
rowOffsetcompensa el número de fila según sea necesario
ÍNDICEse utiliza para identificar la fila 1, columna 1 del datarangoINDEX(data,1,1)
FILAobtiene el número de fila de la celda indexadaROW(INDEX(data,1,1))
INCLUSOdevuelve VERDADERO si el número de fila es par ISEVEN(ROW(INDEX(data,1,1))
y finalmenteSIes par entonces 0, o 1 para imparIF(ISEVEN(ROW(INDEX(data,1,1))),0,1)
#3 Declarar newValuesalmacenar el resultado de laLAMBDAfunción
#4 LAMBDAla función crea una columna de valores0o la diferencia entre un par.

ÍNDICE se utiliza para obtener los valores antiguos de la datacolumna 3 INDEX(data,,3)
Las columnas indexadasMAPAen elLAMBDAfunción MAP(INDEX(data,,3),
laLAMBDAdefine rpara mantener los valores asignados enMAP(INDEX(data,,3), LAMBDA(r,
SIdeclaración se salta el resto de laLAMBDAsi el valor asignado está en blancoIF(r<>"",
#5 Comprueba si res el primer o segundo miembro de la pareja.

FILAnos da rel número de fila ROW(r)
rowOffsetque se agrega en caso de que los primeros miembros estén en filas imparesROW()+rowOffset
INCLUSOdevuelve VERDADERO si rla fila + rowOffsetes parISEVEN(ROW()+rowOffset)
SI's value if TRUE= 1er miembro, & value if false= 2doIF(ISEVEN(ROW()+rowOffset),
#6 El primer miembro compara el valor con el segundo miembro
IF(r>OFFSET(r,1,0),r-OFFSET(r,1,0),0)

SIprueba si res mayor que el valorCOMPENSARpor 1 fila debajo y 0 columnas IF(r>OFFSET(r,1,0),
"Valor si es verdadero" es para restar del rvalor debajo r-OFFSET(r,1,0)
"Valor si es falso" es0
#7 El segundo miembro compara el valor con el primer miembro.
IF(r>OFFSET(r,-1,0),r-OFFSET(r,-1,0),0)

SIprueba si res mayor que el valorCOMPENSARpor 1 fila arriba y 0 columnas IF(r>OFFSET(r,-1,0),
"Valor si es verdadero" es restar del rvalor arriba e r-OFFSET(r,-1,0)
"Valor si es falso" es0
#8 Cree una matriz newDataa partir de la columna ID 1, la columna ID2 ynewValues

ÍNDICEobtiene la primera columna ID1 del datarangoINDEX(data,,1)
ÍNDICE también obtiene la segunda columna ID2 del datarangoINDEX(data,,2)
HSTACKcombina columnas indexadas y newValuesen una matriz de 3 columnasnewData
HSTACK(INDEX(data,,1), INDEX(data,,2), newValues)
#9 FILTRARmatriz newDatadonde los valores de la tercera columna> 0
FILTER(newData, INDEX(newData,,3)>0))

información relacionada