Uso de la Función Indice

Veremos como utilizar la funcíón INDICE, COINCIDIR y el Formato Condicional de Fórmula

El usuario quiere que al seleccionar un Estado y un mes de Enero a Junio en la celda F2 se visualice el importe correspondiente al Estado y mes en cuestión.

Para ello utilicé la funcion INDICE la cual me devuelve el valor de una referencia de fila y columna en una matriz dada.

 Descargar el archivo en Excel desde este enlace

Solución (ver celda F2)
Sintaxis: INDICE(matriz; núm_fila; [núm_columna]) En forma de Matriz

=INDICE(C6:H37,COINCIDIR(C2,B6:B37,0),COINCIDIR(C3,C5:H5,0))

Matriz (obligatorio): En este caso es el rango C6:H37 el cual corresponde a todos los valores de las sucursales de los meses de enero a junio.

núm_fila (obligatorio) : Número de fila en la matriz (no la hoja) del valor que queremos.
COINCIDIR(C2,B6:B37,0) La función COINCIDIR devuelve la posición relativa de un valor buscado en una matriz, en este caso se desea saber que posición tiene el Estado (C2) en la lista de Estados (B6:B37), el cero quiere decir encontrar el primer valor que es exactamente igual que el valor_buscado.

[núm_columna] (opcional): Número de columna en la matriz (no la hoja) del valor que queremos.
COINCIDIR(C3,C5:H5,0) Lo mismo que en núm_fila pero esta vez se necesita la posición relativa del mes en la lista de meses C5:H5, el cero se utiliza para encontrar el primer valor que es exactamente igual que el valor_buscado.

Formato condicional
Para que el número correspondiente en la matriz se vea de un color de manera automática se utilizó el formato condicional de fórmula.

1. Selecciona el rango de celdas C6:H37
2. Clic en Inicio > Formato condicional y en Editar una descripción de regla poner la siguiente fórmula =Y(C$5=$C$3,$B6=$C$2)
3. Clic en el botón "Formato" y selecciona el formato que deseas.
4. Clic en aceptar en las ventanas que aparecen (deben ser 3) y listo.

 Descargar el archivo en Excel desde este enlace

El archivo contiene una segunda opción en la cual se utiliza el formato condicional. Ver imagen

Ver calendario de cursos de Excel en Mérida Yucatán

Solo 3 decimales en Excel - Validación de datos

Validación de datos para restringir a 3 decimales

Durante el curso de Excel Avanzado viendo el módulo de "Validación de datos" una alumna me hizo la siguiente pregunta.

¿Como puedo validar una celda de tal manera que cuando haya decimales solo puedan capturar hasta 3 decimales? El número puedo ser sin decimales o con 3 decimales.

Descargar archivo en Excel Aquí...

Respuesta

Para poder llegar a una respuesta hice el siguiente cuadro.

Captura.- Es el dato a capturar y a validar

Entero.- A la celda captura se le aplicó la función =ENTERO( B2) para obtener solo la parte entera de la fracción

Largo Captura.-  Para obtener el largo del valor capturado se aplicó la función =LARGO(B2)

Largo Entero.-  Para obtener el largo del entero se aplicó la función =LARGO(C2)

Diferencia.- Se obtiene la diferencia entre LARGO CAPTURA y LARGO ENTERO con el objeto de obtener solo la fracción.

Podemos ver que solo aquellos valores que tiene una diferencia menor o igual a 4 cumplen con el requisito por lo que con esto ya tenemos nuestra prueba lógica para hacer la validación.

Creando la validación de datos

• Seleccionamos el rango B2:B8 y hacemos clic en la pestaña Datos - Validación de datos
• En el cuadro de Permitir en la pestaña configuración seleccionamos Personalizada
• En el cuadro Fórmula escribimos la siguiente función 

=O((LARGO(B5)-LARGO(ENTERO(C5)))=0,(LARGO(B5)-LARGO(ENTERO(C5)))=4)

• Para poner un mensaje de entrada ir a la pestaña "Mensaje de entrada", para configurar el mensaje de error ir a la Pestaña "Mensaje de error" por ahora lo dejamos en "Detener"
• Hacemos clic en el botón de Aceptar y listo.

Tal vez te interese ver más artículos sobre "Validación de datos" Aquí...

Explicación de la fórmula

Hacemos la afirmación que el número de caracteres del dato capturado menos el largo de la parte entera del dato capturado es igual a "0" o a "4" con lo que solo permitirá los valores que cumplan con estos criterios.

Espero sus comentarios al respecto, saludos.

Listas Condicionadas

Validación de datos con Listas Condicionadas
En otros artículos he hablado de como crear listas desplegables con validación de datos en Microsoft Excel 2010. Y en esta ocasión voy a retomar el tema con la peculiaridad de utilizar listas que tengan como condición una lista anterior, pero para explicarme mejor voy a decirles cual es el punto a solucionar

Descargar el archivo en Excel  haciendo clic en este link

Antecedentes
Una empresa tiene sucursales en los siguientes en los Estados de Mérida y Campeche. El Estado de Mérida tiene las siguientes sucursales: Mérida_1, Mérida_2, Mérida_3, Mérida_4. El Estado de Campeche tiene las siguientes sucursales: Campeche_1, Campeche_2, Campeche_3, Campeche_4.

La estructura es la siguiente (ver imagen) y cada sucursal tiene los empleado que se pueden ver en la imagen.

Requerimiento

La empresa necesita tener una hoja de cálculo con la opción de Seleccionar un Estado y luego poder seleccionar la sucursal de este Estado, a su vez los empleados de dicha sucursal, todo esto de manera dinámica.

Solución
Para ello hacemos lo siguiente:

1.-Agregamos nombre a los rangos de los Estados, a las Sucursales y a los empleados haciendo lo siguiente: Seleccionamos las celdas a nombrar y hacemos clic en pestaña Fórmulas > Asignar nombre  y con ello vemos el cuadro para asignar nombre (ver imagen).

2.- Ahora vamos a crear la primea lista, seleccionamos la celda C2 y hacemos clic en la pestaña Datos y luego en el ícono de Validación de datos con lo que vemos el cuadro de Validación de datos. En Permitir seleccionamos Lista y en Origen escribimos =Estado que es el nombre del rango E3:E4.

Celda con Validación de datos Lista

3.- Ahora aplicamos la validación de Lista a la celda de Sucursal seleccionando la celda C3 y hacemos clic en el botón de Validación de datos con lo que se activa el cuadro de Validación de datos. En Origen escribimos la fórmula =INDIRECTO($C$2)

4.- Vamos a crear la lista de Vendedor en función a la sucursal, para ello seguimos los mismos pasos del punto 3 pero ahora en Origen escribimos la fórmula =INDIRECTO($C$3)

Listo ya tienes tu modelo con listas desplegables y condicionadas en Excel

Ver más temas de Validación de datos haciendo clic en el siguiente enlace:
http://www.capacitateexcel.com/search/label/Validaci%C3%B3n

Si te ha gustado el artículo compártelo con los demás, haciendo clic en las herramientas de redes sociales del blog.

Auditar los datos capturados con Validación de Datos en Excel

Como validar los datos de una hoja de cálculo utilizando la herramienta "Validación de datos"

En el artículo anterior vimos Como validar el ingreso de datos en una hoja de Excel. En esta ocasión veremos como verificar que los datos ya capturados son los correctos cuando previamente NO se utilizó la validación de datos.

Para ello solo necesitamos hacer lo siguiente:

• Seleccionar los datos a validar y aplicar validación correspondiente.
• Luego hacer clic en Validación de Datos > Rodear con un círculo datos no validos.
• Al hacer esto las celdas que no cumplen con el criterio de validación se ven rodeados de un círculo rojo.

Validación para la fila 8: Agua, Luz y Teléfono no pueden ser cada una mas de 5000 pesos mensuales

Clic sobre la imagen para apreciarla mejor

Validación fila 12: El costo de un evento no puede ser más de 7000 pesos.

Clic sobre la imagen para apreciarla mejor

Nota: los círculos rojos desaparecen unos minutos después, pero si deseas que desaparezcan solo tienes que pulsar en botón de Borrar círculos de validación.

Ejercicio
Aplica la auditoria de la plantilla PRODUCTOS, el proceso es el mismo, solo que en esta ocasión la validación es la longitud del texto.

Descarga aquí el archivo para practicar

Si te gustó este artículo y te pareció de utilidad, te invitamos a suscribirte sin costo para que recibas los siguientes directamente en tu cuenta de correo. También te invito a dar clic en Me Gusta de Facebook y +1 de Google.

Como validar el ingreso de datos en una hoja de Excel

Validación de datos - Caso práctico

Que es la validación de datos
La validación de datos es una herramienta de Excel que nos permite aplicar restricciones o criterios a los datos a capturar en una celda.

Validación es parte del grupo "Herramienta de datos" que se encuentra en la pestaña DATOS.

Es útil para evitar que los usuarios capturen datos incorrectos mediante el bloqueo de la celda a dichos datos o mediante anuncios.

La Herramienta, "Validación de Datos"
La herramienta contiene tres pestañas, Configuración, Mensaje de entrada y la pestaña de Mensaje de error.

Pestaña Configuración

Aquí seleccionamos el tipo de validación mediante el botón desplegable de Permitir.
Los tipos de validación son: Entero, Decimal, Lista, Fecha, Hora, Longitud de Texto, Personalizada.

Aquí Puedes ver una explicación más amplia de como aplicar Validación de datos Personalizada

Pestaña: Mensaje de entrada

Al habilitar esta opción se despliega un mensaje al seleccionar la celda con la validación. La pestaña contiene los cuadros de texto para el título y cuerpo del mensaje..

Pestaña: Mensaje de error

En esta pestaña configuramos el mensaje de error que se despliega cuando el usuario intenta ingresar el dato y éste es incorrecto. Con la opción de Estilo podemos establecer el nivel de permiso habiendo 3 tipos de Estilos:
Detener: No permite ingresar el dato
Advertencia: Excel advierte al usuario que la información es incorrecta y le da la opción de continuar o cancelar el ingreso del dato.
Información: Excel despliega el mensaje de error y da la opción de aceptar o cancelar el ingreso.

Prácticamente, la única opción que bloquea el ingreso de los datos es Detener 

Ejemplos
Aquí te presento algunos ejemplo de cómo utilizar la validación de datos. Podrás consultar como se resuelve descargando el archivo en Excel

Clic para Descargar Archivo

Producto A: Son refacciones y no pueden ser más de 25
Producto B: Es cera de abeja por lo que pueden ser decimales
Producto C: De este producto siempre se mantiene un stock en entre 100 y 500 kilos

Caso 1: El horario permitido es de 7:00 a 14:00 horas
Caso 2: La salida del personal es de 8:00pm en adelante
Caso 3: El RFC de los clientes. Persona física es de 13 dígitos y Persona Moral es 12 dígitos


Si te gustó este artículo y te pareció de utilidad, te invitamos a suscribirte sin costo para que recibas los siguientes directamente en tu cuenta de correo. También te invito a dar clic en Me Gusta de Facebook y +1 de Google.

Validación Personalizada en Excel

La validación de datos

En Excel es posible establecer restricciones sobre los datos que puede aceptar una celda. Esto quiere decir, que si queremos que una celda solo acepte números y además que se encuentren entre un rango de 100 a 500, podemos hacerlo mediante la opción de Validación.

Ver artículo de validación de datos

Caso práctico

En nuestro libro de Excel tenemos dos hojas, una llamada Total la cual suma los importes de una hoja de captura llamada ENE (depende del mes).

Puedes descargar el libro en el link al final de este artículo.

Se suman los datos, solo si corresponden a su departamento, ver fórmula de la imagen.

Hoja TOTAL

Explicación de la fórmula =SUMAR.SI del ejercicio.

 =SUMAR.SI(ENE!$B$2:$I$2,A3,ENE!$B$3:$I$3)

De la hoja ENE sumar los datos del rango B3:I3 de la hoja ENE solo si el rango B2:I2 (siempre de la hoja ENE) tiene el mismo dato que la celda A3 en la hoja Total.

Hoja ENE

Que vamos a validar.

Ningún mes puede tener un total mayor a 100 mil (ver celda B15 hoja TOTAL). Cuando introducimos datos en las celdas validadas, (ver hoja ENE) automáticamente Excel arroja un aviso de que el total del Mes en la hoja Total se ha excedido el máximo establecido.

Como hacemos la validación, solución.

• De la hoja Total seleccionamos la celda B11 y en el cuadro de nombres escribimos TotalPtoEne ver imagen.

• De la hoja Total seleccionamos la celda B15 que contiene el importe máximo permitido. Escribimos en el cuadro de nombre la palabra TotalPto ver imagen.

• Ahora de la hoja ENE seleccionamos el rango de celdas B3:I3 y aplicamos la validación.

Hacemos clic en Datos > Validación de datos para activar el cuadro de validación.

Establecemos un mensaje de Error.

Finalmente hacemos clic en el botón de Aceptar y ya tenemos lista la validación de datos.

Descargar archivo en Excel