Análisis de datos de Excel: validación de datos

La validación de datos es una herramienta muy útil y fácil de usar en Excel con la que puede establecer validaciones de datos sobre los datos que se ingresan que se ingresan en su hoja de trabajo.

Para cualquier celda de la hoja de trabajo, puede

  • Muestre un mensaje de entrada sobre lo que debe introducirse en él.
  • Restrinja los valores que se ingresan.
  • Proporcione una lista de valores para elegir.
  • Muestra un mensaje de error y rechaza una entrada de datos no válida.

Considere el siguiente Rastreador de riesgos que puede usarse para ingresar y rastrear la información de Riesgos identificados.

En este rastreador, los datos que se ingresan en las siguientes columnas se validan con restricciones de datos preestablecidas y los datos ingresados ​​se aceptan solo cuando cumplen con los criterios de validación. De lo contrario, recibirá un mensaje de error.

  • Probability
  • Impact
  • Categoría de riesgo
  • Fuente de riesgo
  • Status

La columna Exposición al riesgo tendrá valores calculados y no puede ingresar ningún dato. Incluso la columnaS. No. está configurado para tener valores calculados que se ajustan incluso si elimina una fila.

Ahora, aprenderá a configurar dicha hoja de trabajo.

Prepare la estructura para la hoja de trabajo

Para preparar la estructura de la hoja de trabajo:

  • Comience con una hoja de trabajo en blanco.
  • Coloque el encabezado en la Fila 2.
  • Coloque los encabezados de las columnas en la Fila 3.
  • Para los encabezados de columna Probabilidad, impacto y exposición al riesgo:
    • Haga clic derecho en la celda.
    • Haga clic en Formato de celdas en el menú desplegable.
    • En el cuadro de diálogo Formato de celdas, haga clic en la pestaña Alineación.
    • Escriba 90 en Orientación.
  • Combine y centre las celdas en las filas 3, 4 y 5 para cada uno de los encabezados de columna.
  • Aplicar formato a los bordes de las celdas de las filas 2 a 5.
  • Ajuste los anchos de filas y columnas.

Su hoja de trabajo se verá de la siguiente manera:

Establecer valores válidos para la categoría de riesgo

En las celdas M5 - M13 ingrese los siguientes valores (M5 es rumbo y M6 - M13 son los valores)

Valores de categoría
Los usuarios finales
Cliente
administración
Calendario
Calendario
Ambiente
Producto
Proyecto
  • Haga clic en la primera celda debajo de la columna Categoría de riesgo (H6).
  • Haga clic en la pestaña DATOS en la cinta.
  • Haga clic en Validación de datos en el grupo Herramientas de datos.
  • Seleccione Validación de datos ... en la lista desplegable.

Aparece el cuadro de diálogo Validación de datos.

  • Haga clic en la pestaña Configuración.
  • En Criterios de validación, en el Allow: lista desplegable, seleccione la opción List.
  • Seleccione el rango M6: M13 en el cuadro Fuente: que aparece.
  • Marque las casillas Ignorar el espacio en blanco y el menú desplegable en la celda que aparecen.

Establecer mensaje de entrada para la categoría de riesgo

  • Haga clic en la pestaña Mensaje de entrada en el cuadro de diálogo Validación de datos.
  • Revisa la caja Show input message cuando se selecciona la celda.
  • En el cuadro debajo de Título :, escriba Categoría de riesgo:
  • En el cuadro debajo de Mensaje de entrada: elija la categoría del riesgo de la lista.

Establecer alerta de error para la categoría de riesgo

Para configurar una alerta de error:

  • Haga clic en la pestaña Alerta de error en el cuadro de diálogo Validación de datos.
  • Marque la casilla Mostrar alerta de error después de ingresar datos no válidos.
  • Seleccione Detener en Estilo: menú desplegable
  • En el cuadro debajo de Título:, escriba Entrada no válida:
  • En el cuadro debajo de Mensaje de error: escriba Elija un valor de la lista desplegable.
  • Haga clic en Aceptar.

Verificar la validación de datos para la categoría de riesgo

Para la primera celda seleccionada en Categoría de riesgo,

  • Se establecen los criterios de validación de datos
  • El mensaje de entrada está configurado
  • La alerta de error está configurada

Ahora, puede verificar su configuración.

Haga clic en la celda para la que ha establecido los criterios de validación de datos. Aparece el mensaje de entrada. El botón desplegable aparece en el lado derecho de la celda.

El mensaje de entrada se muestra correctamente.

  • Haga clic en el botón desplegable en el lado derecho de la celda. Aparece la lista desplegable con los valores que se pueden seleccionar.

  • Verifique los valores de la lista desplegable con los que se utilizan para crear la lista desplegable.

Ambos conjuntos de valores coinciden. Tenga en cuenta que si el número de valores es mayor, obtendrá una barra de desplazamiento hacia abajo en el lado derecho de la lista desplegable.

Seleccione un valor de la lista desplegable. Aparece en la celda.

Puede ver que la selección de valores válidos está funcionando bien.

Finalmente, intente ingresar una entrada no válida y verifique la alerta de error.

Escriba Personas en la celda y presione Entrar. Se mostrará el mensaje de error que ha configurado para la celda.

  • Verifique el mensaje de error.
  • Tiene la opción de Reintentar o Cancelar. Verifique ambas opciones.

Ha configurado correctamente la Validación de datos para la celda.

Note - Es muy importante comprobar la ortografía y gramática de sus mensajes.

Establecer criterios válidos para la columna de categoría de riesgo

Ahora, está listo para aplicar los criterios de Validación de datos a todas las celdas de la columna Categoría de riesgo.

En este punto, debe recordar dos cosas:

  • Debe establecer los criterios para el número máximo de celdas que se pueden utilizar. En nuestro ejemplo, puede variar de 10 a 100 según el lugar donde se utilizará la hoja de trabajo.

  • No debe establecer los criterios para un rango de celdas no deseado o para toda la columna. Esto aumentará innecesariamente el tamaño del archivo. Se llama formato en exceso. Si obtiene una hoja de trabajo de una fuente externa, debe eliminar el formato sobrante, lo cual aprenderá en el capítulo de Consulta de este tutorial.

Siga los pasos que se indican a continuación:

  • Establezca los criterios de validación para 10 celdas en Categoría de riesgo.
  • Puede hacer esto fácilmente haciendo clic en la esquina inferior derecha de la primera celda.
  • Mantenga presionado el símbolo + que aparece y tire hacia abajo.

La validación de datos se establece para todas las celdas seleccionadas.

Haga clic en la última columna seleccionada y verifique.

Se completó la validación de datos para la columna Categoría de riesgo.

Establecer valores de validación para la fuente de riesgo

En este caso, solo tenemos dos valores: interno y externo.

  • Haga clic en la primera celda debajo de la columna Fuente de riesgo (I6)
  • Haga clic en la pestaña DATOS en la cinta
  • Haga clic en Validación de datos en el grupo Herramientas de datos.
  • Seleccione Validación de datos ... en la lista desplegable.

Aparece el cuadro de diálogo Validación de datos.

  • Haga clic en la pestaña Configuración.
  • En Criterios de validación, en la lista desplegable Permitir:, seleccione la opción Lista.
  • Escriba Interno, Externo en el cuadro Fuente: que aparece.
  • Marque las casillas Ignorar el espacio en blanco y el menú desplegable en la celda que aparecen.

Establecer mensaje de entrada para fuente de riesgo.

Establecer alerta de error para fuente de riesgo.

Para la primera celda seleccionada en Fuente de riesgo:

  • Se establecen los criterios de validación de datos
  • El mensaje de entrada está configurado
  • La alerta de error está configurada

Ahora, puede verificar su configuración.

Haga clic en la celda para la que ha establecido los criterios de validación de datos. Aparece el mensaje de entrada. El botón desplegable aparece en el lado derecho de la celda.

El mensaje de entrada se muestra correctamente.

  • Haga clic en el botón de flecha desplegable en el lado derecho de la celda. Aparece una lista desplegable con los valores que se pueden seleccionar.

  • Compruebe si los valores son los mismos que escribió: interno y externo.

Ambos conjuntos de valores coinciden. Seleccione un valor de la lista desplegable. Aparece en la celda.

Puede ver que la selección de valores válidos está funcionando bien. Finalmente, intente ingresar una entrada no válida y verifique la alerta de error.

Escriba Financial en la celda y presione Entrar. Se mostrará el mensaje de error que ha configurado para la celda.

  • Verifique el mensaje de error. Ha configurado correctamente la Validación de datos para la celda.

  • Establecer criterios válidos para la columna Fuente de riesgo

  • Aplique los criterios de Validación de datos a las celdas I6 - I15 en la columna Fuente de riesgo (es decir, el mismo rango que el de la columna Categoría de riesgo).

La validación de datos se establece para todas las celdas seleccionadas. Se completó la validación de datos para la columna Fuente de riesgo.

Establecer valores de validación para el estado

  • Repita los mismos pasos que utilizó para configurar los valores de Validación para la Fuente de riesgo.

  • Establezca los valores de Lista como Abierto, Cerrado.

  • Aplique los criterios de Validación de datos a las celdas K6 - K15 en la columna Estado (es decir, el mismo rango que el de la columna Categoría de riesgo).

La validación de datos se establece para todas las celdas seleccionadas. Se completó la validación de datos para el estado de la columna.

Establecer valores de validación para la probabilidad

Los valores del puntaje de probabilidad de riesgo están en el rango de 1 a 5, siendo 1 bajo y 5 alto. El valor puede ser cualquier número entero entre 1 y 5, ambos inclusive.

  • Haga clic en la primera celda debajo de la columna Fuente de riesgo (I6).
  • Haga clic en la pestaña DATOS en la cinta.
  • Haga clic en Validación de datos en el grupo Herramientas de datos.
  • Seleccione Validación de datos ... en la lista desplegable.

Aparece el cuadro de diálogo Validación de datos.

  • Haga clic en la pestaña Configuración.
  • En Criterios de validación, en la lista desplegable Permitir:, seleccione Número entero.
  • Seleccione entre en Datos:
  • Escriba 1 en el cuadro debajo de Mínimo:
  • Escriba 5 en el cuadro debajo de Máximo:

Establecer mensaje de entrada para probabilidad

Configure Alerta de error para probabilidad y haga clic en Aceptar.

Para la primera celda seleccionada en Probabilidad,

  • Se establecen los criterios de validación de datos.
  • El mensaje de entrada está configurado.
  • Se establece una alerta de error.

Ahora, puede verificar su configuración.

Haga clic en la celda para la que ha establecido los criterios de validación de datos. Aparece el mensaje de entrada. En este caso, no habrá un botón desplegable porque los valores de entrada están configurados para estar en un rango y no en la lista.

El mensaje de entrada se muestra correctamente.

Ingrese un número entero entre 1 y 5 en la celda. Aparece en la celda.

La selección de valores válidos está funcionando bien. Finalmente, intente ingresar una entrada no válida y verifique la alerta de error.

Escriba 6 en la celda y presione Entrar. Aparecerá el mensaje de error que ha configurado para la celda.

Ha configurado correctamente la Validación de datos para la celda.

  • Establezca criterios válidos para la columna de probabilidad.

  • Aplique los criterios de Validación de datos a las celdas E6 - E15 en la columna Probabilidad (es decir, el mismo rango que el de la columna Categoría de riesgo).

La validación de datos se establece para todas las celdas seleccionadas. Se completó la validación de datos para la columna Probabilidad.

Establecer valores de validación para el impacto

Para configurar los valores de validación para Impact, repita los mismos pasos que utilizó para configurar los valores de validación para la probabilidad.

Aplique los criterios de Validación de datos a las celdas F6 - F15 en la columna Impacto (es decir, el mismo rango que el de la columna Categoría de riesgo).

La validación de datos se establece para todas las celdas seleccionadas. Se completó la validación de datos para la columna Impacto.

Establecer la exposición al riesgo de la columna con valores calculados

La exposición al riesgo se calcula como producto de la probabilidad de riesgo y el impacto del riesgo.

Exposición al riesgo = probabilidad * impacto

Escriba = E6 * F6 en la celda G6 y presione Entrar.

Se mostrará 0 en la celda G6 ya que E6 y F6 están vacíos.

Copie la fórmula en las celdas G6 - G15. Se mostrará 0 en las celdas G6 - G15.

Como la columna Exposición al riesgo está pensada para valores calculados, no debe permitir la entrada de datos en esa columna.

  • Seleccione las celdas G6-G15

  • Haga clic con el botón derecho y en la lista desplegable que aparece, seleccione Formato de celdas. Aparece el cuadro de diálogo Formato de celdas.

  • Haga clic en la pestaña Protección.

  • Marque la opción Locked.

Esto es para garantizar que no se permita la entrada de datos en esas celdas. Sin embargo, esto entrará en vigor solo cuando la hoja de trabajo esté protegida, lo que hará como último paso después de que la hoja de trabajo esté lista.

  • Haga clic en Aceptar.
  • Sombree las celdas G6-G15 para indicar que son valores calculados.

Dar formato a los valores del número de serie

Puede dejar que el usuario complete la S. No. Columna. Sin embargo, si formatea los valores de S. No., la hoja de trabajo se verá más presentable. Además, muestra cuántas filas tiene formateada la hoja de trabajo.

Escriba = fila () - 5 en la celda B6 y presione Entrar.

1 aparecerá en la celda B6. Copie la fórmula en las celdas B6-B15. Aparecen los valores del 1 al 10.

Sombrea las celdas B6-B15.

Envolver

Casi ha terminado con su proyecto.

  • Ocultar la columna M que contiene valores de categoría de datos.
  • Dar formato a Bordes para las celdas B6-K16.
  • Haga clic derecho en la pestaña de la hoja de trabajo.
  • Seleccione Proteger hoja en el menú.

Aparece el cuadro de diálogo Proteger hoja.

  • Marque la opción Proteger hoja de trabajo y contenido de celdas bloqueadas.
  • Escriba una contraseña en Contraseña para desproteger la hoja -
    • La contraseña es sensible a mayúsculas
    • La hoja protegida no se puede recuperar si se olvida la contraseña
    • Es una buena práctica mantener una lista de nombres de hojas de trabajo y contraseñas en algún lugar
  • En Permitir a todos los usuarios de esta hoja de trabajo: marque la casilla Seleccionar celdas desbloqueadas.

Ha protegido las celdas bloqueadas en la columna Exposición al riesgo de la entrada de datos y ha mantenido editables el resto de las celdas desbloqueadas. Haga clic en Aceptar.

los Confirm Password aparece el cuadro de diálogo.

  • Vuelva a ingresar la contraseña.
  • Haga clic en Aceptar.

Su hoja de trabajo con Validación de datos establecida para las celdas seleccionadas está lista para usar.