Controlar valores fuera de rango en Excel

Ya hemos escrito en alguna nota sobre ciertos problemas que pueden surgir al usar validación de datos para controlar los valores que son introducidos en una celda.
Supongamos esta lista de números en el rango A1:A10, para el cual hemos definido como valores permitidos sólo números enteros en 50 y 100



Si intentamos poner, por ejemplo, 48 Excel genera un mensaje de error



¿Cómo es entonces que en la lista aparecen valores "ilegales"? Esto se debe a que Excel controla la validez de los datos en el momento de ser introducidos manualmente. Si los datos existen en la hoja antes de haber definido la regla de validación de datos o si copiamos los datos de otra fuente y los pegamos en la hoja, Validación de datos no funciona.

Una forma rápida de controlar la validez de valores en situaciones como las descritas en el párrafo anterior es usar la barra de Auditoría de fórmulas



Esta barra tiene un botón para rodear con círculos valores no válidos



En nuestro caso, al apretar el botón vemos este resultado



Al reemplazar el valor inválido por uno permitido, el círculo desaparece. Esto hace que este método sea muy cómodo.

Por ejemplo, si tenemos que ubicar un valor determinado en una tabla existente, podemos definir una regla para validación de datos que excluya este valor y luego usar auditoría de fórmulas para ubicarlo.

Si queremos ubicar el valor 561 en esta tabla,



definimos validación de datos con la fórmula =A1<>561 (asegurándonos que la elda activa sea A1 y que usamos referencias relativas)



Luego usamos auditoría de fórmulas para ubicar la celda que contiene el valor



Para ubicar duplicados podemos usar esta fórmula en validación de datos

=CONTAR.SI($A$1:$F$14,A1)=1



Al aplicar auditoría de fórmulas vemos que el valor 360 se repite

Technorati Tags: MS Excel