Ok, tengo un problema de fórmula. Necesito enumerar todas las líneas de orden de compra de un determinado comprador que tienen un resto mayor que cero.
Necesito completar el panel del comprador con datos del panel del comprador. Nota: Cada orden de compra puede tener una o más líneas que deben enumerarse en orden y ambas hojas están en un libro de trabajo.
Panel del comprador:
Datos del panel del comprador:
Estos son los enfoques que he probado:
=IFERROR(INDEX(Download!$B$2:$B$50000,MATCH(TRUE,$B$3,Download!$C$2:$C$50000>0,0)),"")
=IFERROR(INDEX(Download!$B$2:$B$50000,MATCH($B$3&>0,Download!C2:C50000&DownloadG2:G50000,0)),"")
=IF(ISERROR(SMALL(IF(IF(ISERROR(SEARCH($B$3,$C$2:$C$50000)),FALSE,TRUE),ROW($C$2:$C$50000)),ROW($B$2:$B$50000))),"",INDEX($A$2:$K$50000,SMALL(IF(IF(ISERROR(SEARCH($B$3,$C$2:$C$50000)),FALSE,TRUE),ROW($$C$2:$C$50000)),ROW($B$2:B50000)),3))
=IF(ISERROR(INDEX(Download!A2:K50000,SMALL(IF(Download!C2:C50000=$B$3,ROW(Download!B2:B50000)),ROW(1:1))-1,3)),"",INDEX(Download!A2:K50000,SMALL(IF(Download!C2:C50000=$B$3,ROW(Download!B2:B50000)),ROW(1:1))-1,3))
¿¿¿¿Algunas ideas????
Respuesta1
Este es un enfoque para hacer listas dinámicas de cosas en Excel.
Supongamos que tiene una matriz de números de fila donde están sus criterios TRUE(es decir, Código de comprador = JDH y Restante > 0). Luego, podría alimentar esa matriz a una SMALL()función que enumeraría los números de fila a medida que se completaban. Y finalmente, podría usar SMALL()como row_numsen una INDEX()función, y eso enumeraría los valores apropiados de cualquier columna que especifique con el column_numparámetro.
Entonces, comencemos obteniendo una matriz de números de fila. Para simplificar, asumiré que su tabla de datos tiene solo 25 filas y que el Panel está en la misma hoja que los datos. Además, estoy usando el Código de Comprador ELN, que está en la celda B30.
Primero calcularemos una matriz de TRUE/FALSEvalores correspondientes a sus criterios:
($C$1:$C$25=$B$30)*($G$1:$G$25>0)
La primera expresión proporciona una matriz de TRUE/FALSEvalores donde Código de comprador (columna C) = ELN, y la segunda proporciona una matriz de TRUE/FALSEvalores donde Restante (columna G) es > 0. En operaciones aritméticas que involucran valores lógicos, TRUEse FALSEconvierten a 1 y 0 , por lo que esta multiplicación da una matriz de 1 y 0 con 1 en las posiciones donde sus criterios son ambos TRUE.
Ahora necesitamos convertir esta matriz en una lista de números de fila donde están sus criterios TRUE. Si dividimos una matriz de números secuenciales por esta matriz de 1 y 0, obtendremos una matriz de números de fila dondequiera que haya un 1, intercalados con #DIV/0!errores dondequiera que haya un 0.
ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0))
A continuación, en lugar de SMALL(), usaremos AGGREGATE(), que hace lo mismo que SMALL()haría (función=15), excepto que tiene una opción para ignorar errores (opción=6). También AGGREGATE()puede manejar matrices sin tener que ingresarlas con CTRLShiftEnter.
Entonces, en la siguiente fórmula, AGGREGATE()se ignoran los #DIV/0!errores y se proporciona una matriz que contiene solo los números de fila que coinciden con sus criterios:
AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33)
Tengo esta fórmula en la fila 34, por lo que "-33" hace que la función comience en 1 y enumere los números de fila a medida que se completan. Cambie este parámetro para adaptarlo a su situación.
Ahora podemos usar esto en un INDEX() y envolver todo en un IFERROR()para insertar espacios en blanco cuando INDEX()se agote row_numsmientras se completa. Esta fórmula, completada desde B34:
=IFERROR(INDEX($A$1:G$25,AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33),MATCH(B$32,$A$1:$G$1,0)),"")
da los resultados que se muestran a continuación.
Para el ELN del comprador, solo las filas 5 y 11 tienen 0 restante, y la fórmula enumera todas las filas excepto esas.
Tenga en cuenta que me referí a los encabezados del panel en el MATCH()que se proporciona column_num'susando B$32como lookup_value. Cuando se completa la fórmula, esto funciona para el número de orden de compra y la descripción, pero los otros encabezados en el Panel no son los mismos que los encabezados en su tabla de datos.
Entonces, para las otras columnas, reemplacé la referencia de la celda B$32con texto entre comillas: "LINE_NBR" para la columna Línea de orden de compra, "CANTIDAD" para la columna Cantidad pedida y "RESTANTE" para la columna Cantidad recibida. Esta última columna implica un cálculo, por lo que la fórmula en realidad es:
=E34-IFERROR(INDEX($A$1:G$25,AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33),MATCH("REMAINING",$A$1:$G$1,0)),"")
La última columna, "Necesaria", se calcula como:
=E34-F34, y rellenó.
Espero que esto ayude y buena suerte.