Soy un contador público certificado con mucha experiencia en Excel, pero parece que no puedo encontrar una respuesta a esta pregunta (por mi cuenta). ¿Hay alguna manera de aplicar formato condicional para el siguiente ejemplo?
EJEMPLO: Tengo una fórmula que es =sum(B4,C28,A32,B40)---> ¿Hay alguna manera de aplicar una regla de formato condicional en las celdas B4, C28, A32 y B40 que resalte automáticamente las celdas que se utilizan en ¿la formula?
^ esto sería de gran ayuda con una conciliación bancaria que estoy realizando. En lugar de resaltar manualmente. Hay muchas celdas en las que tendría que hacer esto manualmente. Creo firmemente en estar lo más automatizado posible para las conciliaciones (de hecho, la mayor parte de mi reconocimiento es simplemente una plantilla de arrastrar y soltar que hice y que identifica la mayoría de los elementos de reconocimiento automáticamente).
NOTA: Actualmente lo que estoy haciendo es usar =isformula(xxxxx) en las celdas que tengo que emparejar manualmente. Luego resalte para asegurarse de que todo esté capturado y no se pierda nada.
Respuesta1
Lo que usted describe suena a auditoría de hojas de cálculo. Excel tiene algunas herramientas integradas para ayudar con eso.
Puede utilizar "Seguir precedentes" en la cinta Fórmulas para dibujar líneas en las celdas de las que depende la celda actual. Utilice "Eliminar flechas" para eliminar las flechas.
O puede usar la vista Fórmula haciendo clic en "Mostrar fórmulas" en la cinta Fórmulas (o use Ctrl+`, que es la marca posterior a la izquierda del número 1 en un teclado estadounidense). Eso mostrará las fórmulas en las celdas en lugar de los resultados y se resaltará cualquier celda que se utilice en la fórmula.
El formato condicional no es un buen enfoque porque tendría que aplicar los formatos condicionales a todas las celdas de la hoja de cálculo. Eso haría que las cosas se aceleraran de manera bastante drástica.
Respuesta2
Sí, puedes utilizar FORMULATEXT()como base para esto:
=IFERROR(FIND(SUBSTITUTE(CELL("ADDRESS",INDIRECT(ADDRESS(ROW(),COLUMN()))),"$",""),FORMULATEXT($C$1)),0)<>0
Lo anterior supone que conoces la celda en la que reside la fórmula que estás examinando. Utiliza $C$1 pero, por supuesto, puedes cambiarlo para que sea cualquier celda que desees.
Puede crear la regla de formato condicional ("CF") para cualquier celdaPERO hazlo mientras ESTÁS EN esa celda: mientras esa celda está seleccionada, luego copie la celda y pegue los formatos en cualquier otra celda que desee incluir en el CF.
Me imagino que tienes la celda con la SUM()fórmula y estás completando las celdas a las que hace referencia directamente. Si tiene una lista que está escribiendo en algún lugar, puede consultarla directamente al crear su fórmula CF.
La fórmula se utiliza ADDRESS()para crear una referencia de celda textual para cualquier celda seleccionada y, por lo tanto, para cualquier celda sobre la que finalmente se coloque el CF. Luego se utiliza INDIRECT()para convertirla en una referencia de celda "real". Finalmente, se utiliza SUBSTITUTE()para eliminar los $ (le da "A1", no $A$1").
Ese se convierte en el material a FIND()buscar en la SUM()función a la que le estás agregando celdas. FORMULATEXT()se utiliza para obtener el texto de esa fórmula y dado que "SUM(" y ")" no son parte de ninguna referencia de celda aceptable, no es necesario hacer nada al respecto y FIND()solo buscará entre las celdas a las que se hace referencia entre paréntesis. IFERRORcomprueba si falló, dando 0 como resultado si es así porque FIND()no puede producir un resultado de 0. Eso significa que obtienes un número entero positivo o 0 en este paso.
Por último, el CF verifica que sea 0 y, si NO es 0, resalta la celda como desee.
Esto es parte de una capacidad más amplia que nadie parece estar explorando. Puedo crear rangos con nombre a los que puedo agregar ()e ingresar lo que quiera dentro de los paréntesis, al igual que los argumentos en una función "real". Un par de rangos con nombre que admiten luego me dan un rango con nombre que se ve y actúa como una función programada en Excel, tal como SUM()está, con cualquier argumento que elija. Los favoritos aquí para los compañeros de trabajo incluyen ourLOOKUP()(nombre de la empresa, no "nuestro") tomar argumentos INDEX/MATCH y ponerlos en una función creada como VLOOKUP().
SO, esencialmente, UDF, sin conocer mucho VBA o encontrarse con lugares donde no se permite el uso de VBA.
Un poco b%st%rds para hacerlo la primera vez, y delicado, pero agradable.
Pero lo anterior se encargará de resaltar si completa el ()en su SUM()como supongo. No extiende la respuesta para abordar su tarea más amplia, solo lo resaltado sobre lo que pregunta.