En este video, veremos cómo resaltar las búsquedas de coincidencias aproximadas con formato condicional.
Aquí tenemos una tabla de búsqueda simple que muestra los costos de material para varias alturas y anchos. La fórmula en K8 usa las funciones INDICE y MATCH para recuperar el costo correcto basado en los valores de ancho y alto ingresados en K6 y K7.
Tenga en cuenta que la búsqueda se basa en una coincidencia aproximada. Dado que los valores están en orden ascendente, COINCIDIR comprueba los valores hasta que se alcanza un valor mayor y luego retrocede y devuelve la posición anterior.
Construyamos una regla de formato condicional para resaltar la fila y la columna coincidentes.
Como siempre con el formato condicional más complicado, le recomiendo que primero trabaje con fórmulas ficticias y luego transfiera una fórmula de trabajo directamente a la regla de formato condicional. De esta manera, puede usar todas las herramientas de Excel cuando esté depurando la fórmula, lo que le ahorrará mucho tiempo.
Primero configuraré la fórmula para el ancho. Necesitamos devolver VERDADERO para cada celda en la fila 7, donde el ancho coincidente es 200.
Esto significa que comenzamos nuestra fórmula con $ B5 =, y necesitamos bloquear la columna.
= $ B5 =
Ahora, no podemos buscar 275 en la columna de anchos, porque no está allí. En cambio, necesitamos una coincidencia aproximada que encuentre 200, al igual que nuestra fórmula de búsqueda.
La forma más sencilla es utilizar la función BUSCAR. BUSCAR automáticamente hace una coincidencia aproximada y, en lugar de devolver una posición como PARTIDA, BUSCAR devuelve el valor de coincidencia real. Entonces, podemos escribir:
$ B5 = BUSCAR ($ K $ 6, $ B $ 6: $ B $ 12)
Con nuestro ancho de entrada para el valor de búsqueda y todos los anchos en la tabla para el vector de resultado.
Si utilizo F9, puede ver el valor que devuelve BUSCAR.
Ahora, cuando ingreso la fórmula en la tabla, obtenemos VERDADERO para cada celda en la fila de 200 de ancho.
Ahora necesitamos extender la fórmula para que coincida con la columna de altura. Para hacer esto, agregaré la función OR y luego una segunda fórmula para igualar la altura.
Comenzaremos la fórmula de la misma manera, pero esta vez necesitamos bloquear la fila:
= B $ 5
Luego usamos la función BUSCAR nuevamente con la altura para el valor de búsqueda y todas las alturas en la tabla como vector de resultado.
= O ($ B5 = BUSCAR ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = BUSCAR ($ K $ 7, $ C $ 5: $ H $ 5))
Cuando copio la fórmula en la tabla, obtenemos VERDADERO para cada celda de la columna coincidente y cada celda de la fila coincidente, justo lo que necesitamos para el formato condicional.
Puedo simplemente copiar la fórmula en la celda superior izquierda exactamente y crear una nueva regla.
Ahora, si cambio el ancho o el alto, el resaltado funciona como se esperaba.
Finalmente, si solo desea resaltar el valor de búsqueda en sí, es un cambio simple. Simplemente edite la fórmula y reemplace la función OR con la función AND.
= Y ($ B5 = BUSCAR ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = BUSCAR ($ K $ 7, $ C $ 5: $ H $ 5))
Curso
Formato condicionalAtajos relacionados
Ingrese los mismos datos en varias celdas Ctrl + Enter ⌃ + Return Mostrar el cuadro de diálogo Pegado especial Ctrl + Alt + V ⌘ + ⌃ + V Alternar referencias absolutas y relativas F4 ⌘ + T
