Escribe lo que quieres encontrar y haz clic en el botón BUSCAR

31 de agosto de 2014

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.

18 de agosto de 2014

Reporte Comparativo en Excel 2010 Comparativo Mes Anterior

Reporte comparativo en Excel, comparativo mes anterior


Este artículo se puede considerar como continuación de la serie de artículos de "Como hacer un comparativo en Excel 2010".
En esta ocasión se modifica el archivo incluyendo el comparativo del mes anterior, incluso Enero 2014 se compara con Diciembre 2013 actualizado.


Columna Mes anterior (MesAnt) VENTAS

  • Incluimos una columna (ver columna D) y le ponemos el Título que mejor nos parezca para el mes anterior.
  • En la celda D7 escribimos la siguiente fórmula =SI($C$2-1=0,BUSCARH(12,'2013'!$B$12:$N$17,$A7,FALSO),BUSCARH($C$2-1,'2014'!$B$3:$N$8,$A7,FALSO))
  • Explicación de la Fórmula anterior: Si la celda C2 menos 1 es igual a 0, Buscamos el valor 12 (Mes de diciembre) en la hoja 2013 en el rango de datos actualizados que en esta caso es $B$12:$N$17, de no ser igual a 0 entonces buscamos el resultado de C2-1 (que en este caso es el mes anterior) en la hoja 2014 en el rango de datos $B$3:$N$8.
Columna Mes anterior (MesAnt) CLIENTES
  • En la celda =SI($C$2-1=0,BUSCARH(12,'2013'!$B$32:$N$36,$A14,FALSO),BUSCARH($C$2-1,'2014'!$B$23:$N$27,$A14,FALSO))
  • La fórmula arriba funciona de la misma manera, si el resultado de C2-1=0 entonces se busca el mes 12 en 2013, sino se busca el resultado de C2-1 en 2014.





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.

13 de agosto de 2014

Como enumerar datos en Excel 2010


Como enumerar datos en Excel 2010

En este artículo veremos como enumerar una lista de datos en Excel 2010 utilizando la opción de Series y utilizando el seleccionar y arrastrar.

Enumerando utilizando la opción series.
En nuestro caso tenemos una lista de 19 datos que tienen el título en la primera fila y necesitamos enumerar desde el número 1 todos los datos, para ello hacemos lo siguiente.



  • Nos posicionamos en la primera celda donde va iniciar la enumeración, en nuestro caso es la celda A2 y vamos a enumerar desde el número 1 y de ahí de uno en uno hasta el número 19.
  • Después de seleccionar la celda A2 hacemos clic en la pestaña de Inicio después en el botón de Series del grupo Modificar (son los botones a la derecha) ver imagen.
Series en Excel

  • Al hacer clic en Series se despliega el cuadro de Series (ver imagen) 

Series con Excel
  • Ahora en el cuadro de "Series en" seleccionamos Columnas en el cuadro Tipo seleccionamos Lineal en el cuadro de Texto Incremento escribimos 1 pues es la cantidad que deseamos que incremente cada número siguiente. En Límite escribimos el número de la última fila de nuestros datos, en nuestro caso es 19 (aunque estamos en la fila 20 los datos a enumerar son 19 pues no cuenta el título)
  • Hacemos clic en el botón de Aceptar  y ya tenemos numerada nuestra lista de datos (ver imagen)
Enumerando utilizando seleccionar y arrastrar.


Otra opción que tenemos para llenar una fila de datos es haciendo lo siguiente:

  • Escribimos en la primera celda en número con el que inicia y nuestra lista y seguido abajo escribimos el número que le sigue, en nuestro caso inicia con 1 y le sigue el 2.
  • Ahora seleccionamos las dos celdas que contienen los números y sin pulsar algún botón del ratón seleccionamos con el puntero la esquina inferior derecha del rango seleccionado.



  • Ahora sin dejar de seleccionar hacemos clic al botón izquierdo del ratón y arrastramos la esquina hasta la fila donde debemos de tener el último número.
  • Ahora ya tenemos nuestros datos enumerados.

Cualquier duda puedes enviarla por este medio. Saludos.


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.

9 de agosto de 2014

Como hacer un Reporte Comparativo en Excel 2010 - Parte 1

Reporte Comparativo en Excel - Parte 1 de 4


En esta serie de artículo mostraré como crear un reporte comparativo en Excel 2010 utilizando algunas funciones y herramientas de datos. El modelo en cuestión es muy sencillo de elaborar, no se requiere crear macros ni conexión a enormes base de datos.





Descargar el Archivo en Excel Aquí


Curso de Excel Avanzado, informes aquí.....

Explicación del Modelo
El modelo consiste en un cuadro dinámico donde se compara el mes actual contra el mismo mes del año anterior y contra el importe del presupuesto, de igual manera se comparan los datos acumulados al mes actual contra presupuesto y año anterior.
El modelo contiene las ventas y número de clientes de 4 sucursales.
Para ver un mes en solo debemos de cambiar el número de mes mediante un botón desplegable colocado en la celda C2 de la hoja reporte y en automático los datos cambian.

Estructura del Modelo



REPORTE es la hoja principal o pantalla del reporte (ver imagen de abajo)
2013. Contiene los datos históricos y actualizado de ejercicio anterior los cuales deben ser capturados a valor histórico y en la misma hoja ser actualizados.
2014. Contiene los datos del ejercicio Actual.
PTO Contiene los datos del presupuesto autorizado.
Títulos. Esta hoja contiene los datos para que al cambiar el número del mes en la celda C2 automáticamente se actualice el nombre del mes de la celda D3

Imagen del modelo o Reporte

Prácticamente de esta hoja solo debemos de cambiar el mes en la celda C2


Creando el modelo

En la hoja Reporte creamos las columnas de los años y de las variaciones, de preferencia sin espacios entre años.
Como pueden ver dejé una fila de espacio entre las ventas, los clientes y los ingresos por clientes (ver filas 12 y 19. Agregué los nombres de las sucursales y  totales.
Por ahora no tiene fórmulas y funciones, en el siguiente artículo veremos como preparar las demás hojas y finalizaremos poniendo fórmulas y funciones.

La hoja sin fórmulas y funciones queda de la siguiente manera, ver imagen



Seleccionar el mes
En la hoja REPORTE seleccionamos la celda C2 y hacemos clic en la pestaña de Datos / Validación de Datos / Listas
En el cuadro Permitir de la pestaña Configuración seleccionamos Lista, en el cuadro Origen escribimos los números del 1 al 12 (separados por comas) que corresponde a los meses del año, luego hacemos clic en Aceptar.
Cuando seleccionemos la celda C2 se activa una la lista desplegable de los meses (ver imagen).


Ver las 4 partes del Artículo
Como hacer un Reporte Comparativo en Excel 2010 -  Parte 1
Como hacer un Reporte Comparativo en Excel 2010 -  Parte 2
Como hacer un Reporte Comparativo en Excel 2010 -  Parte 3
Como hacer un Reporte Comparativo en Excel 2010 -  Parte 4

Como hacer un Reporte Comparativo en Excel 2010 - Parte 2

Como hacer un reporte en Excel 2010, Parte 2

Preparando las hojas 2013

En este artículo veremos como preparar la hoja 2013 la cual contiene los datos del ejercicio pasado.

Para ello necesitamos tener los datos de la siguiente manera, ver imagen.


Descargar el Archivo en Excel Aquí

VENTAS: Histórico y Actualizado
  1. Escribimos el nombre de cada sucursal y sus valores, como título de cada mes ponemos el número del mes, adicional ponemos el total para ver que hemos capturado bien los datos.
  2. Si vamos a actualizar los datos históricos escribimos en la fila 10 los factores de actualización. Este dato lo estaremos capturando cada mes.
  3. Ahora requerimos actualizar los datos por lo que en la celda C13 ponemos la siguiente fórmula =C4*C$10 fijando la fila 10 pues necesitamos que la fila de actualización no se mueva.
  4. Ahora copiamos la fórmula de la celda C13 y la pegamos en las demás celdas de la sección ACTUALIZADO.

Acumulado
  1. Para crear el cuadro de Acumulado escribimos en la celda C22 la siguiente fórmula =C13 con ello creamos un vínculo con la celda de enero pues prácticamente enero es lo mismo en lo acumulado. 
  2. Copiamos esta fórmula a las celdas C23, C24 y C25.
  3. En la celda D22 escribimos la siguiente fórmula =C22+D13 con lo que al mes de Febrero sumamos lo que teníamos acumulado a Enero más lo que hay en Febrero (ver imagen derecha). Los datos mensuales los tomamos actualizados.
  4.  Ahora solo copiamos y pegamos esta fórmula a las demás celdas con excepción de Enero que tienen una fórmula distinta.

CLIENTES
Ahora necesitamos preparar los datos de la sección de clientes del ejercicio 2013 (ver imagen)



Mes
En la sección mensual solo capturamos el número de clientes de cada mes y de nuevo ponemos como título el número de mes, tanto en el mensual como en el acumulado.

Acumulado
  1. Hacemos lo mismo que en el acumulado de las ventas, en la celda C42 escribimos la siguiente fórmula =C33 y la copiamos y pegamos para las demás sucursales en el mes de Enero.
  2. En la celda D42 escribimos la fórmula =C42+D33 y la copiamos y pegamos a las demás celdas con excepción de Enero.
Ahora ya tenemos lista nuestra hoja del ejercicio anterior 2013, seguimos preparando las demás hojas.


Como hacer un Reporte Comparativo en Excel 2010 - Parte 3

Como hacer un Reporte Comparativo en Excel 2010 -  Parte 3

Construyendo la hoja 2014 y Presupuesto (PTO)

Ahora vamos a prepara la hoja del ejercicio 2014, para ello construimos la siguiente hoja.


La hoja del 2014 es muy parecida a la hoja de 2013 con la diferencia que no es necesario la sección de actualización.

Descargar el Archivo en Excel Aquí

VENTAS 
Acumulado
  1. Para crear el cuadro de Acumulado escribimos en la celda C13 la siguiente fórmula =C4 con ello creamos un vínculo con la celda de enero pues prácticamente Enero es lo mismo en lo acumulado. 
  2. Copiamos esta fórmula a las celdas C14, C15 y C16.
  3. En la celda D13 escribimos la siguiente fórmula =C13+D4 con lo que al mes de Febrero sumamos lo que teníamos acumulado a Enero más lo que hay Febrero.
  4.  Ahora solo copiamos y pegamos esta fórmula a las demás celdas con excepción de Enero que tienen una fórmula distinta.
     


CLIENTES
Acumulado

  1. Para construir el Acumulado de clientes 2014 escribimos en la celda C33 la siguiente fórmula =C24 el cual corresponde al mes de Enero. 
  2. Copiamos esta fórmula a las celdas C34, C55 y C36.
  3. En la celda D33 escribimos la siguiente fórmula =C33+D24 sumando con esto lo acumulado al mes anterior más lo del mes actual.
  4.  Ahora solo copiamos y pegamos esta fórmula a las demás celdas con excepción de Enero que tienen una fórmula distinta.





La hoja de Presupuesto 2014 (PTO)

La hoja de Presupuesto es idéntica a la hoja 2014 en estructura por lo que las instrucciones para crear el acumulado de Ventas y de Clientes es igual, con el cuidado de utilizar los rangos correctos.



Ver las 4 partes del Artículo
Como hacer un Reporte Comparativo en Excel 2010 -  Parte 1
Como hacer un Reporte Comparativo en Excel 2010 -  Parte 2
Como hacer un Reporte Comparativo en Excel 2010 -  Parte 3
Como hacer un Reporte Comparativo en Excel 2010 -  Parte 4

Como hacer un Reporte Comparativo en Excel 2010 - Parte 4

Como hacer un Reporte Comparativo en Excel 2010 -  Parte 4

Ahora que ya tenemos todas nuestras hojas completa solo necesitamos agregar las fórmulas y funciones necesarias a la hoja REPORTE para construir nuestro modelo.

Descargar el Archivo en Excel Aquí

Empezamos agregando en la columna los número del 2 al 5 los cuales son utilizados en la celda C7 que veremos y explicaré a continuación.

Estos números solo se agregan en la hoja Reporte, más adelante se seleccionan y se le da el color blanco para que no se vean.












- VENTAS MENSUALES 2013
Para obtener lo datos del 2013 escribimos la siguiente función en la celda C7 de la hoja REPORTE  =BUSCARH($C$2,'2013'!$B$12:$N$17,$A7,FALSO) Esta es la función buscar donde $C$2 es el mes a buscar, '2013'!$B$12:$N$17 es el rango en la hoja 2013 donde va a buscar el mes y $A7 es la fila que va a devolver. 

- VENTAS ACUMULADAS 2013
Para obtener lo datos acumulados del 2013 escribimos la siguiente función en la celda I7 de la hoja REPORTE  =BUSCARH($C$2,'2013'!$B$21:$N$25,$A7,FALSO) Esta es la función buscar donde $C$2 es el mes a buscar, '2013'!$B$21:$N$25 es el rango en la hoja 2013 donde va a buscar el mes y $A7 es la fila que va a devolver. 
Hoja 2013

La función empieza a buscar en en rango $B$12:$N$17 (ver cuadro rojo) y cuando busca el mes en nuestro caso el mes 2 (ver columna D) devuelve la fila 2 que es a lo que se refiere el $A7 de la función y corresponde a Mérida de los datos "Actualizados" no históricos. Ahora solo resta copiar y pegar la función a las demás sucursales de las ventas 2013.

Recuerda que la función está en la hoja REPORTE.

- VENTAS MENSUALES 2014
Para obtener lo datos del 2014 escribimos la siguiente función en la celda D7 de la hoja REPORTE  =BUSCARH($C$2,'2014'!$B$3:$N$8,$A7,FALSO) Esta es la función buscar donde $C$2 es el mes a buscar, '2014'!$B$3:$N$8 es el rango en la hoja 2014 donde va a buscar el mes y $A7 es la fila que va a devolver. 

- VENTAS ACUMULADAS 2014
Para obtener lo datos acumulados del 2014 escribimos la siguiente función en la celda J7 de la hoja REPORTE  =BUSCARH($C$2,'2014'!$B$12:$N$16,$A7,FALSO) Esta es la función buscar donde $C$2 es el mes a buscar, '2014'!$B$12:$N$16 es el rango en la hoja 2014 donde va a buscar el mes y $A7 es la fila que va a devolver. 

Ahora solo resta copiar y pegar la función a las demás sucursales de las ventas 2014.

Hoja 2014

- VENTAS MENSUALES PRESUPUESTO (PTO)
Para obtener lo datos del PTO escribimos la siguiente función en la celda E7 de la hoja REPORTE  =BUSCARH($C$2,PTO!$B$3:$N$8,$A7,FALSO) Esta es la función buscar donde $C$2 es el mes a buscar, 'PTO'!$B$3:$N$8 es el rango en la hoja PTO donde va a buscar el mes y $A7 es la fila que va a devolver. 
Ahora solo resta copiar y pegar la función a las demás sucursales de las ventas PTO.

- VENTAS ACUMULADAS PRESUPUESTO (PTO)
Para obtener lo datos acumulados del PTO escribimos la siguiente función en la celda E7 de la hoja REPORTE =BUSCARH($C$2,PTO!$B$12:$N$16,$A7,FALSO) Esta es la función buscar donde $C$2 es el mes a buscar, 'PTO'!$B$12:$N$16 es el rango en la hoja PTO donde va a buscar el mes y $A7 es la fila que va a devolver. 
Ahora solo resta copiar y pegar la función a las demás sucursales de las ventas PTO.

Hoja PTO
CLIENTES MENSUALES 2013
Escribir en la celda C14 de la hoja REPORTE  la siguiente función 
=BUSCARH($C$2,'2013'!$B$32:$N$36,$A14,FALSO)
De igual manera que en las ventas ahora es en la sección de Clientes también tiene que copiar la función a las demás sucursales, ver imagen

CLIENTES ACUMULADO 2013
Escribir en la celda I14 de la hoja REPORTE  la siguiente función 
=BUSCARH($C$2,'2013'!$B$32:$N$36,$A14,FALSO)
De igual manera que en las ventas ahora es en la sección de Clientes también tiene que copiar la función a las demás sucursales, ver imagen
Hoja 2013
CLIENTES MENSUALES 2014
Escribir en la celda D14 de la hoja REPORTE  la siguiente función 
=BUSCARH($C$2,'2014'!$B$23:$N$27,$A14,FALSO)
De igual manera que en las ventas ahora es en la sección de Clientes también tiene que copiar la función a las demás sucursales, ver imagen.

CLIENTES ACUMULADO 2014
Escribir en la celda J14 de la hoja REPORTE  la siguiente función 
=BUSCARH($C$2,'2014'!$B$32:$N$36,$A14,FALSO)
De igual manera que en las ventas ahora es en la sección de Clientes también tiene que copiar la función a las demás sucursales, ver imagen.
Hoja 2014
CLIENTES MENSUALES PRESUPUESTO (PTO)
Escribir en la celda D14 de la hoja REPORTE  la siguiente función 
=BUSCARH($C$2,PTO!$B$23:$N$27,$A14,FALSO)
De igual manera que en las ventas ahora es en la sección de Clientes también tiene que copiar la función a las demás sucursales, ver imagen.

CLIENTES ACUMULADO PRESUPUESTO (PTO)
Escribir en la celda K14 de la hoja REPORTE  la siguiente función 
=BUSCARH($C$2,PTO!$B$32:$N$36,$A14,FALSO)
De igual manera que en las ventas ahora es en la sección de Clientes también tiene que copiar la función a las demás sucursales, ver imagen.
Hoja PTO

Ventas x Clientes

Ahora, en la hoja REPORTE dividimos el importe de las ventas entre el número de clientes, tanto en 2013, 2014, PTO mensualmente y acumulado.















Variaciones vs 2013 y vs PTO

Ahora necesitamos obtener las variaciones mensuales y acumuladas de Ventas y Clientes por lo que con lo que solo necesitamos restar el las Ventas 2014 menos Ventas PTO y también Ventas 2014 menos ventas 2013. Esto se hace también por los clientes.


Para terminar cambiamos el color de los números de la columna A y creamos la hoja Títulos esta hoja contiene el título del mes y se usa de la siguiente manera (ver imagen)


Hoja Títulos

.