Tengo un conjunto de números separados por comas. Cada conjunto representa una serie de números únicos que se relacionan con un proyecto. ¿Cómo puedo escribir una fórmula para buscar en una hoja de referencia cualquier número de elementos de la lista y devolver un valor codificado?
Incluiré ejemplos (anónimos) a continuación para cada hoja (tablas 1-5) y el resultado previsto/esperado. Me imagino que tiene algo que ver con SUMA, FRECUENCIA, ÍNDICE o alguna otra fórmula matricial, pero no puedo entenderlo. puedes ver mi preguntaaquítambién, pero tiene prácticamente la misma información.
He usado "PAGculo" "Ftodo" y "METROissing" pero todo funciona.
Lógica:
La fórmula debe realizar los siguientes pasos.
- Extraiga la lista de números de cada celda en la columna C detabla 4.
- BuscarTabla 3(columna D) para ejemplos de los números de la lista.
2b. Si aparece algún número en la lista, verifique si la auditoría ha fallado (indicado por el texto "fallido" en la columna B deTabla 3).
2c. Si es así, imprimaF.
2do. Si no es así, imprimaPAG. - Si no aparece ninguno de los números de la lista, imprimaMETRO.
Mesas:
tabla 1
Esta tabla es la hoja de referencia principal. Enumera todos los UID con el nombre, el servidor y el proyecto.
Unique ID Name Server Address Project
10000 Company 1 Server 1 Project 1
10001 Company 2 Server 2 Project 2
10002 Company 3 Server 3 Project 3
10003 Company 3 Server 3 Project 4
10004 Company 3 Server 3 Project 5
10005 Company 3 Server 3 Project 6
10006 Company 4 Server 4 Project 7
10007 Company 4 Server 4 Project 7
10008 Company 4 Server 4 Project 7
10009 Company 5 Server 5 Project 8
10010 Company 6 Server 6 Project 9
10011 Company 7 Server 7 Project 10
10012 Company 8 Server 8 Project 11
10013 Company 8 Server 8 Project 11
10014 Company 8 Server 8 Project 11
10015 Company 8 Server 8 Project 11
10016 Company 8 Server 8 Project 11
10017 Company 8 Server 8 Project 11
10018 Company 8 Server 8 Project 11
10019 Company 8 Server 8 Project 11
10020 Company 8 Server 8 Project 11
10021 Company 9 Server 9 Project 12
10022 Company 10 Server 10 Project 13
10023 Company 11 Server 10 Project 14
10024 Company 12 Server 10 Project 15
10025 Company 13 Server 10 Project 16
10026 Company 14 Server 10 Project 17
10027 Company 15 Server 10 Project 18
10028 Company 16 Server 10 Project 19
10029 Company 17 Server 10 Project 20
10030 Company 18 Server 10 Project 21
10032 Company 19 Server 10 Project 23
10033 Company 19 Server 10 Project 24
Tabla 2
esta tabla tiene la misma información quetabla 1pero agrupa cada UID en su proyecto respectivo. Actualmente no lo uso, pero ¿podría ser útil? Lo eliminaré si no.
Company Trading Name Project UID UID UID UID
Company 1 Project 1 10000
Company 2 Project 2 10001
-----------------------------------------------------
Company 4 Project 7 10006 10007 10008
Tabla 3
esta tabla es donde se pegan los resultados de la auditoría. Siguen este diseño exactamente, con la columna de notas con la palabra fallido si la auditoría no ha pasado (el formato condicional resalta esto actualmente), y el UID se extrae mediante una simple función IZQUIERDA. Las columnas Nombre y empresa luego utilizan una BUSCARV contra el UID entabla 1para tomar el nombre/servidor.
Notes Time Rec. UID Name Server
Audit for company passed:10001 auto 10001 Company 2 Server 2
Audit for company passed:10006 auto 10006 Company 4 Server 4
Audit for company failed:10007 auto 10007 Company 4 Server 4
tabla 4
Esta es la hoja de auditoría. La fórmula iría a la columna D y el resultado se copiaría/pegaría como valores cada día. He completado algunos ejemplos aquí de cómo debería verse después.
Server Name Project (UID) Today's Date
Server 1 Company 1 Project 1 (10000) P
Server 2 Company 2 Project 2 (10001) N
Server 3 Company 3 Project 3 (10002,10003,10004,10005) F
Server 4 Company 4 Project 4 (10006,10007,10008) P
-----------------------------------------------------
Server 10 Company 10 Project 13 (10022) P
Server 10 Company 11 Project 14 (10023) M
Server 10 Company 12 Project 15 (10024) P
Server 10 Company 13 Project 16 (10025) P
Server 10 Company 15 Project 18 (10027) F
Server 10 Company 16 Project 19 (10028) P
Server 10 Company 17 Project 20 (10029) M
Server 10 Company 18 Project 21 (10030) P
Server 10 Company 19 Project 22 (100310) P
Server 10 Company 19 Project 23 (10032) P
Server 10 Company 19 Project 24 (10033) M
Alternativamente tengo una quinta mesa,tabla 5, que busca automáticamente cualquier identificación faltante deTabla 3usando la siguiente fórmula:
=INDEX(Reference!$A$2:$A$160, SMALL(IF(ISERROR(MATCH(Reference!$A$2:$A$160, Check!$D$2:$D$350, 0)), (ROW(Reference!$A$2:$A$160)-MIN(ROW(Reference!$A$2:$A$160))+1), ""), ROWS($A$1:A1)))
CuandoTabla 3está vacío (se borra cada día), esto simplemente enumera todos los proyectos. Cuando se pega cualquier número de auditorías enTabla 3sin embargo, la lista entabla 5Se actualizará para mostrar cuáles faltan. Quizás haya una manera de incorporar esto en una fórmula final.
Tengo una versión no identificada por si alguien quiere trabajar con ella.
Gracias.
edición1: Alguien solicitó más detalles del sistema. Esto es en un entorno Windows 10 usando Excel 365.
Respuesta1
Intenté elaborar un enfoque que admito que no es muy escalable y utiliza algunas columnas auxiliares. No estoy muy seguro de si existe una forma directa de buscar valores separados por comas de una sola vez. Podría ser posible, pero no soy tan experto en Excel.
Además, creo que la versión de escritorio de Office 365 admite macros de VBA. Si tiene una versión de escritorio, puede optar por una solución macro VBA que puede ser más sencilla. Sin embargo, creo que las macros no son compatibles con la versión en línea.
Vea la siguiente instantánea.
Hay 5 columnas auxiliares. H,M,N,O,P. H puede ser opcional, pero prefiero usar una secuencia numérica simple ya que es posible que los nombres de sus servidores no tengan el último carácter como Números como Servidor 1, Servidor 2 en ese orden.
En M4 coloque la siguiente fórmula y arrástrela hacia abajo.
=SUBSTITUTE(RIGHT(K4,LEN(K4)-FIND("(",K4)),")","")
Esto crea una lista adecuada de UID separados por comas.
En N4 coloque la siguiente fórmula y arrástrela hacia abajo hasta el número provisional de celdas. Cuando no haya nada que mostrar, el contenido de la celda estará en blanco, pero extiéndalo para un aprovisionamiento posterior, ya que el recuento de UID puede variar.
=VALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",TRUE,$M$4:$M$22),",",REPT(" ",99)),(ROW(1:1)-1)*99+1,99)))
En O4 coloca la siguiente fórmula y arrástrala hacia abajo.
=IF(ISNUMBER(VALUE(N4)),MATCH("*"&N4&"*",$M$4:$M$22,0),"")
Esto enumera las posiciones de los UID que se utilizarán más adelante.
En P4 coloque la siguiente fórmula y arrástrela hacia abajo.
=IF(ISNUMBER(VALUE(N4)),IF(ISNUMBER(FIND("passed",INDEX($B$4:$B$22,MATCH(VALUE(N4),$D$4:$D$22,0)))),"P",IF(ISNUMBER(FIND("failed",INDEX($B$4:$B$22,MATCH(VALUE(N4),$D$4:$D$22,0)))),"F","M")),"")
Esto decide el estado de cada UID de la Tabla 4.
Ahora la columna de salida L en mi ejemplo.
En L4 coloque la siguiente fórmula y arrástrela hacia abajo.
=IF(COUNTIFS($O$4:$O$22,H4,$P$4:$P$22,"M")=COUNTIF($O$4:$O$22,H4),"M",IF(COUNTIFS($O$4:$O$22,H4,$P$4:$P$22,"P")+COUNTIFS($O$4:$O$22,H4,$P$4:$P$22,"M")=COUNTIF($O$4:$O$22,H4),"P","F"))
Esto aplica la lógica final para decidir el estado de la auditoría Pasa, Falla o Falta según la lista transpuesta en las columnas auxiliares.
Puede convertir estos conjuntos de datos a tablas de Excel y utilizar referencias de Tabla[Encabezado] para hacerlo más dinámico.
Pruébelo y decida si podría resultarle útil. Acepto que no es tan bueno ya que utiliza varias columnas auxiliares. La idea es transponer una lista separada por comas a celdas contiguas y luego aplicar la búsqueda.