Intenté usar varias combinaciones de funciones, pero parece que no hay ninguna función específica que pueda devolver valores no buscados/coincidentes. Hacerlo manualmente lleva días debido a la gran cantidad de datos que necesito clasificar.
Quiero que MS Excel 2003 extraiga el resto de la Lista A según la Lista B.
*La Lista A es de 2000 artículos, la Lista B es solo de 10 a 30 como máximo
Lista A
No.1----1 2 3 4 5 6 (cada dígito se coloca en 1 celda, siempre 6 dígitos)
No.2----1 1 2 3 4 5 (cada dígito se coloca en 1 celda, siempre 6 dígitos)
No.3----1 3 4 5 6 7 (cada dígito se coloca en 1 celda, siempre 6 dígitos)
Lista B
No.1----1 2 3 (cada dígito se coloca en 1 celda, siempre 3 dígitos)
No.2----1 1 4 (cada dígito se coloca en 1 celda, siempre 3 dígitos)
No. 3----2 3 5 (cada dígito se coloca en 1 celda, siempre 3 dígitos)
Por ejemplo:
En la Lista A, busque coincidencias (si las hay) según la entrada de la Lista B y devuelva el resto como salida. Si no se encuentra ninguna coincidencia, no se requiere ninguna salida.
Lista A
No.1----1 2 3 4 5 6 (cada dígito se coloca en 1 celda, siempre 6 dígitos)
Basado en la Lista B
No.1----1 2 3 (coincidencia encontrada (1&2&3 está presente), luego selecciono el resto manualmente # # # 4 5 6 o = 456)
No.2----1 1 4 ( no se encontró ninguna coincidencia (1&1&4 no está presente), no hay salida)
No.3----2 3 5 (coincidencia encontrada (2&3&5 está presente), luego selecciono 1 # # 4 # 6 o salida = 146)
Pensé que uso la función CONTAR para contar la frecuencia de cada dígito del 0 al 9 en cada elemento de la Lista A y luego uso la función SI y Y (especificando cuáles y cuántos dígitos se requieren para calificar como una coincidencia) para decirme cuál Los elementos de la Lista B coinciden con la Lista A.
Entonces, para cada elemento de la Lista A, necesito que Excel ejecute toda la Lista B y el resultado podría variar desde ningún resultado hasta un máximo de 3 resultados.
También quiero poder cambiar los valores en la Lista B sin alterar la fórmula utilizada para buscar para poder usar la misma hoja de trabajo repetidamente con facilidad.
Hasta ahora, todos mis otros intentos de utilizar otras funciones no han logrado extraer el resto de la manera que quiero. Si tienes alguna sugerencia, por favor enséñame.
Respuesta1
Bien, aquí tienes una solución que funciona, pero puede provocar daño cerebral al configurarla. Lo construí paso a paso, calculando un conjunto de cosas que luego se utilizaron en los siguientes cálculos. Una vez que tuve un modelo funcional, trabajé al revés, sustituyendo las fórmulas reales por las referencias de celda para que todas las fórmulas se refirieran solo a sus listas reales y no a cálculos intermedios. Las fórmulas se multiplicaron. De hecho, el primer intento produjo fórmulas que excedían la capacidad de la celda. Lo dividí en dos mesas, la primera alimentando a la segunda. Las tablas son muy grandes y te habrías vuelto completamente loco tratando de hacer que todas las referencias de celdas apunten a los lugares correctos para completar las fórmulas en dos direcciones a lo largo de las tablas. Así que agregué algunas referencias indirectas para que las fórmulas puedan simplemente copiarse y pegarse y funcionen sin limpieza manual. Desafortunadamente, eso produjo algunas fórmulas bastante importantes.
Explicaré esto como un ejemplo ubicado en lugares específicos de una hoja de cálculo. Si necesita ubicar las piezas en otro lugar, edite todas las referencias de filas y columnas en la primera celda y luego copie y pegue para completar las tablas. Para su propia cordura, configure algunos ejemplos conocidos para que pueda verificar que las primeras filas y columnas de cada tabla funcionan antes de completar todo. Toma un par de aspirinas profilácticas y comenzamos.
Esto se basa en su Lista A en las columnas A a F con datos que comienzan en la fila 1 (2000 filas). La lista B está en las columnas H a J con datos que comienzan en la fila 1 (30 filas).
La primera mesa comienza en L1. Esta tabla crea una lista de las posiciones de las entradas de la Lista B en los registros de la Lista A. Por ejemplo:
Position: 1 2 3 4 5 6
So if a List A record contains: 1 3 3 5 7 9
and a List B record contains: 1 3 7
the entry in this table will be: 1 2 5 (stored as a single number: 125)
Si el registro de la Lista B no coincide con el registro de la Lista A, habrá un #N/A en la celda. El diseño de esta tabla es el siguiente:
[L] [M] [N] [O]
[1] <=======List B Row========>
[2] List A Row 1 2 3 ...
[3] 1
[4] 2
[5] 3
...
En realidad, debe colocar los números de fila como encabezados de columna en la fila 2 de las columnas M a AP y como etiquetas de fila en la columna L. Esto es lo que las fórmulas usan como punteros. Hay 30 columnas de datos, una para cada fila de entradas de la Lista B, y tendrá 2000 filas, que representan las entradas de la Lista A, comenzando en la fila 3. Cada celda de la tabla refleja una entrada de la Lista B frente a una entrada de la Lista A. . Esta es la fórmula para M3:
=MATCH(INDIRECT("H"&M$2),$A1:$F1,0)&MATCH(INDIRECT("H"&M$2),$A1:$F1,0)
+MATCH(INDIRECT("I"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)+1, , )&":$F"&$L3),0)&MATCH(INDIRECT("H"&M$2),$A1:$F1,0)
+MATCH(INDIRECT("I"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)+1, , )&":$F"&$L3),0)
+MATCH(INDIRECT("J"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)
+MATCH(INDIRECT("I"&M$2),INDIRECT(ADDRESS($L3,MATCH(INDIRECT("H"&M$2),$A1:$F1,0)+1, , )&":$F"&$L3),0)+1, , )&":$F"&$L3),0)
He dividido la fórmula aquí para que sea más legible, pero es toda una fórmula. Verifique que lo tenga funcionando desde M3 hasta N4 con algunos datos de muestra y luego copie y pegue para completar la tabla.
La segunda tabla comienza en AR1. Esta tabla está estructurada de la misma manera:
[AR] [AS] [AT] [AU]
[1] <=======List B Row========>
[2] List A Row 1 2 3 ...
[3] 1
[4] 2
[5] 3
...
Esta tabla funciona de manera similar a la primera: cada celda representa los resultados de un registro de la Lista B frente a un registro de la Lista A. Esta tabla contiene el resto. Entonces, en el ejemplo que di para la primera tabla, el resto sería 359:
So if a List A record contains: 1 3 3 5 7 9
and a List B record contains: 1 3 7
the remainder is: 3 5 9
La fórmula que va en la celda AS3 es:
=IF(ISNA(M3),"",IF(ISERROR(FIND(COLUMN(INDIRECT("a"&$AR3)),M3)),INDIRECT("a"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("b"&$AR3)),M3)),INDIRECT("b"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("c"&$AR3)),M3)),INDIRECT("c"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("d"&$AR3)),M3)),INDIRECT("d"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("e"&$AR3)),M3)),INDIRECT("e"&$AR3),"")&
IF(ISERROR(FIND(COLUMN(INDIRECT("f"&$AR3)),M3)),INDIRECT("f"&$AR3),""))
Cada celda de esta tabla contendrá el resto o un carácter nulo si no hubo coincidencia.
Deseaba obtener un resumen de los resultados de cada registro de la Lista A. Dado que cada fila de la tabla representa un registro de la Lista A, el resumen puede ir al final de cada fila de la tabla. Las 30 columnas de la tabla terminan en la columna BV, por lo que los resultados están en la columna BW. La fórmula para BW3 será:
=AS3&IF(ISBLANK(AS3),""," ")&AT3&IF(ISBLANK(AT3),""," ")& ... &BV3&IF(ISBLANK(BV3),""," ")
En lugar de mostrar los 30 términos aquí, se muestran solo los dos primeros y el último. Sigue el mismo patrón para agregar el resto. Construye la cadena de resultados concatenando los resultados de cada coincidencia. Si hay un valor en una celda, agrega un espacio antes del siguiente valor. Si desea un delimitador diferente, cambie el espacio por otro, como un espacio de coma. Copie esta fórmula en la columna BW para todas las filas.
Probablemente este no sea el lugar más útil para obtener los resultados. Una vez que tengas todo funcionando, puedes mover las cosas. En realidad, si mueve algo, es posible que tenga una limpieza masiva de referencias de celda. Tendría más sentido simplemente crear la salida que desea en otra ubicación y usar referencias de celda para hacer referencia a lo que ya está configurado.