No se detenga con simples controles de validación. En este artículo, aprenderá a utilizar fórmulas personalizadas para limitar la entrada de datos.
La validación de datos es una de las herramientas más poderosas de Excel, que le permite establecer límites para la entrada de datos. Por ejemplo, puede limitar las fechas a un período específico o números enteros a un límite superior e inferior utilizando la configuración incorporada. Lo que tal vez no se dé cuenta es que puede introducir fórmulas personalizadas cuando la configuración incorporada no es la adecuada. En este artículo, le mostraré cómo utilizar esta función para acomodar un límite de presupuesto.
Más información sobre Office
Estoy usando Excel 2019 (escritorio) en un sistema Windows 10 de 64 bits, pero esta función está disponible en versiones anteriores. La edición del navegador de 365 admite la validación de datos en uso, pero no se puede agregar o manipular la validación de datos. Puede trabajar con sus propios datos o descargar los archivos de demostración.
Los datos de ejemplo
Para ilustrar la combinación de fórmulas y validación de datos, usaremos el presupuesto simple que se muestra en la . (El conjunto de datos es un objeto Tabla, pero no es necesario usar una Tabla para que esta técnica funcione.)
Añadiremos la validación a este simple presupuesto.
Inicialmente, el presupuesto utiliza un formato condicional para alertarle cuando los gastos exceden el presupuesto. Este es un uso común del formato condicional. La aplicación de este formato condicional es simple:
- Seleccione D3:D7.
- Haga clic en la ficha Inicio y seleccione Nueva regla en el menú desplegable Formato condicional del grupo Estilos.
- En el panel superior, seleccione la opción Usar una fórmula para determinar qué celdas formatear.
- En el panel inferior, introduzca la fórmula =$D3>$C3.
- Haga clic en Formato, haga clic en la ficha Rellenar, seleccione un color y haga clic en Aceptar dos veces.
No se pueden limitar los gastos después del hecho, por lo que el formato condicional simple de la columna Gastos puede ser adecuado. Pero supongamos que desea limitar los valores del presupuesto. Específicamente, supongamos que desea rechazar una partida presupuestaria si empuja al total (C7) a exceder un límite específico.
Añadir validación de datos
En este punto, podría considerar un formato más condicional, pero esa ruta permite a los usuarios introducir valores que tal vez no desee. En su lugar, utilicemos un control de validación que limita las entradas comparando los resultados de la aceptación de ese valor con un límite.
El presupuesto total es el resultado de una función SUM(); no es un valor fijo, así que no queremos cambiarlo. En su lugar, añadiremos un valor de entrada () que podrá modificar a medida que cambie su presupuesto.
Añada un valor de entrada para su presupuesto total.
A continuación, utilizaremos una regla personalizada que toma la siguiente forma:
SUM(partidas_de_presupuesto)<=límite_de_presupuesto
Tenga en cuenta que la validación en la columna C y el formato condicional en la columna D no están conectados. Ahora, vamos a crear un control de validación de datos que limite los valores del presupuesto de la siguiente manera:
- Seleccione C4:C7.
- Haga clic en la ficha Datos y seleccione Validación de datos en el menú desplegable Validación de datos del grupo Herramientas de datos.
- En el menú desplegable Permitir, seleccione Personalizar.
- En el control Fórmula, introduzca =SUMA($C$4:$C$7)<=$C$1 .
- Haga clic en la ficha Alerta de error e introduzca Error de artículo como título y el simple mensaje de error Este artículo excede el límite de su presupuesto .
- Haga clic en Aceptar.
Esta fórmula rechazará cualquier partida presupuestaria que le haga exceder su límite.
Introduzca un simple mensaje de error.
El control está listo para la prueba ahora. Seleccione cualquier partida presupuestaria en la columna C y auméntela un poco. Como puede ver en la , el control rechaza el valor más alto y comparte un poco de información sobre el porqué. Haga clic en Cancelar. El presupuesto ya está en su límite. Excel rechazará cualquier intento de aumentar el valor de cualquier partida presupuestaria. La única manera de aumentar un presupuesto es reducir una de las otras partidas presupuestarias.
O bien, puede aumentar el límite de presupuesto global en C1. Por supuesto, puede gastar lo que quiera y el formato condicional en la columna D continuará alertándole cuando los costos excedan el presupuesto.
El control rechaza una posición.
Esta potente característica es fácil de implementar y versátil. Lea los siguientes artículos para obtener más información sobre la función de validación de datos de Excel:
Cuatro maneras de especificar fechas utilizando la validación de datos de Excel
Cómo crear dos listas dinámicas avanzadas en Excel
Boletín semanal de Microsoft
Conviértase en un experto en Microsoft de su empresa con la ayuda de estos tutoriales de Windows y Office y de los análisis de nuestros expertos sobre los productos empresariales de Microsoft. Entregado Lunes y Miércoles
Envíeme su pregunta sobre Office
Respondo a las preguntas de los lectores cuando puedo, pero no hay garantía. No envíe archivos a menos que se le solicite; las solicitudes iniciales de ayuda que lleguen con archivos adjuntos se eliminarán sin leer. Puede enviar capturas de pantalla de sus datos para ayudar a aclarar su pregunta. Cuando se ponga en contacto conmigo, sea lo más específico posible. Por ejemplo, «Please troubleshoot my workbook and fix what’s wrong» probablemente no obtendrá respuesta, pero «Can you tell me why this formula is’ t returning the expected results? Por favor, menciona la aplicación y la versión que estás usando. ConsejoTecnologico.com no me reembolsa por mi tiempo o experiencia en ayudar a los lectores, ni tampoco pido una cuota a los lectores a los que ayudo. Puede ponerse en contacto conmigo en susansalesharkins@gmail.com.
Véase también….