Taller 12: Formato Condicional

Formato Condicional

Excel nos ofrece la posibilidad de cambiar el color, estilo, subrayado y tamaño de fuente, así como los bordes de una celda cuando el valor que aparece cumple unas determinadas condiciones. Para ello debemos:

1º Seleccionar la celda o celdas donde queremos que apa el formato condicional.

2º. Ir al menú Formato, opción Formato condicional y aparecerá esta pantalla:

3º Elegimos si el formato se lo queremos dar al valor de una celda o a una fórmula.

4º Elegimos la condición que queremos que cumpla. En la imagen está seleccionado entre, solo tenemos que poner en las casillas en blanco de la derecha los valores que queramos. Si presionamos sobre la lista despegable tendremos las siguientes opciones:

5º. Pinchamos sobre el botón Formato y nos sale una ventana con tres pestañas, en ellas podemos cambiar el formato a la fuente, el color de la misma y el diseño de los bordes de la celda.

6º Se pueden añadir más formatos condicionales a las celdas pulsando sobre el botón añadir. 

Ejercicio 1

Crea una hoja de cálculo donde aparezca el número de ejemplares vendidos de los diferentes periódicos por trimestre, calcula el total por anual.

A la celda que contenga el total anual se le tiene que dar el siguiente formato condicional:

·         Si es mayor de 2.500.000, formato cursiva color rojo, fondo de celda azul celeste

·         Si está entre 2.000.000 y 2.500.000, negrita azul oscuro, fondo lavanda.

·         Si es inferior a 2.000.000, formato negrita ciruela

Guarda el ejercicio como ejercicio1.

Ejercicio 2

Crea una hoja de cálculo para obtener la nota media de los alumnos en la primera evaluación. Debes dar el siguiente formato condicional al rango (D4:D13):

·         Si tiene una nota igual o superior a un 7 azul oscuro  negrita, borde color rojo oscuro, color fondo amarillo

·         Si tiene una nota comprendida entre 5 y 7 verde negrita

·         Si tiene menor que 5 color verde negrita, borde discontinuo (----), color fondo lavanda

Ordena los datos por orden alfabético y guarda la hoja con el título ejercicio2.

Taller 11: Validación de datos

Validación de datos Excel

Hola a todos, hoy os explicare como podemos validar datos que introduzcamos en una hoja de excel, vale tanto para excel 2007 como para excel 2010.

Validar datos en excel es muy útil  ya que nos permite que el usuario o nosotros mismos, nos aseguremos de que los datos se introducen son correctos.

Por ejemplo, para una factura en cantidad de un producto, solo podamos meter datos numéricos positivos y no una cadena de texto o algún numero negativo.

Vamos a ver un ejemplo practico. En la siguiente imagen mostramos una pequeña factura de un negocio de reparaciones (algunos campos están completados a partir de formulas).

Nuestro objetivo sera validar  los datos que se encuentran en la columna “unidades” para que al realizar el calculo en la columna importe, no nos de ningún error. En este caso vamos a indicar que el numero de unidades deba ser un entero positivo, siendo el mínimo 1.

Para validar los datos, primero seleccionaremos el rango de celdas que queremos validar, pinchamos en la pestaña Datos -> validación de datos (en el apartado de herramientas de datos).

Nos aparecerá esta ventana.

En la pestaña Configuración, indicaremos que tipo de datos (entero, decimal, fecha, etc) y lo que debe cumplir para que se acepte en la hoja excel (mayor que, menor que, entre, igual a, etc), si no se cumple, excel nos mostrara una ventana de error diciendo que ese dato introducido no es valido.

Para nuestro caso seleccionaremos en Permitir, numero entero (si se introduce algo que no sea un entero, saltara el error), en Datos, mayor o igual que (esto hará que solo aceptara el valor que sea mayor o igual al introducido) y en Mínimo, escribimos un 1 (en caso de que sea menor a este numero nos saltara el error).

También podemos seleccionar la opción “omitir blancos”, si la seleccionamos, en caso de dejar una celda del rango vació, no nos saltara el error, pero si la deshabilitamos y dejamos una celda vacía si nos saltara el error.

NOTA: si en lugar de un 1 hubiéramos puesto un 0, esta opción no tiene utilidad ya que se supone que cuando una celda es nula es como si tuviera un 0.

Vemos que si introducimos un valor erróneo, nos aparecerá esta ventana. Veremos un poco mas adelante como personalizar esta ventana.

En la pestaña Mensaje de entrada de la ventana que nos aparece al pinchar en la validación de datos, nos permite indicar con un simple texto que es lo que debe introducirse en la celda, esto es muy útil, en caso de terceros ya que ellos no saben en principio, las restricciones impuestas.

Simplemente ponemos un titulo y el mensaje con la condicion que debe cumplir el valor de la celda.

Al tener seleccionada una celda del rango con esa restricción nos mostrara el mensaje que hemos escrito.

En la pestaña Mensaje de error, nos permite personalizar el mensaje que aparece cuando insertamos un dato que no es correcto. Lo único que debemos hacer es insertarle un titulo, un mensaje y seleccionar el tipo de error.

Los tipos de errores pueden modificar el comportamiento a la hora de introducir los datos:

• Detener: si se inserta un dato no valido, nos obliga a que insertemos un dato valido para la celda.

• Advertencia: si se inserta un dato no valido, nos informa de que no es valido, pero no nos obliga a que insertemos un dato valido.

• Información: si se inserta un dato no valido, nos  informa del dato no es valido, es como el anterior pero algo mas simple.
  

Taller 10: Macros 3

 Macros 3

Abra un nuevo archivo de Excel. Primero debemos activar la Ficha: Desarrollador (Programador) si no está activa.

De clic en el botón Archivo, luego en Opciones, luego en Personalizar cinta de opciones, y en la ventana que sale, en el lado derecho active la opción Desarrollador (Programador) y Aceptar.  

De clic en la Ficha Desarrollador (Programador) y luego de clic en el comando: Seguridad de macros. Busque al lado izquierdo: Configuración de macros  y a la derecha active el comando: Habilitar todas las macros(…) dando clic sobre este. También de clic sobre el comando: Confiar en el acceso al modelo de objetos de proyectos de VBA. De clic en Aceptar.

Una Macro es una serie de pasos que se almacenan y se pueden activar con la tecla de control y una letra. Por ejemplo: si vamos a dar formato a diferentes celdas, como: Fuente (Tipo de letra), tamaño de Fuente, Negrita, y Color de Fuente, para no estar repitiendo estos pasos se pueden almacenar en una macro y cuando ejecute la macro los pasos antes mencionados se realizan las veces que se desee. A continuación veamos cómo grabar y cómo ejecutar una macro.

1.      Vaya a la celda A1 escriba su Nombre y presione Enter.

2.      Regrese a la celda A1 y ajuste el ancho de la columna.

3.      En la ficha Desarrollador (Programador) de clic en el comando: Grabar macro y podrá observar esta figura:

4.      Windows activa el cuadro de dialogo Grabar Macro, que permitirá darle el nombre a la macro y que será el método abreviado para ejecutarla. El método Abreviado se refiere a la letra con la que se va activar la macro, utilizando la tecla Control. Esta letra debe ser en minúscula, porque si activa las mayúsculas la macro se ejecutará presionando la tecla Shift + Control + la letra que usted indicó.

5.      Donde dice Nombre de la macro ya aparece el nombre que llevará la macro en este caso Macro1.  Si desea cambiarlo escriba uno nuevo, pero yo le recomiendo que lo deje así.

6.      En la opción Método Abreviado dice que se activa con la tecla Control (CTRL) + la letra que usted indique, de clic en el cuadrito y ponga la letra a (en minúsculas).

7.      En la opción Descripción, escriba brévemente para qué sirve la macro que va a hacer: Esta macro cambia el formato a una celda.

8.      De clic en el Botón Aceptar. Excel empezará a grabar en la Macro1 todos los pasos que hagamos ya sea con el ratón o con el teclado.

9.      Cambie el Tipo de Letra en el Botón Fuente de la Ficha Inicio por: Calibri.

10.    Cambie el tamaño de la letra en el Botón Tamaño de Fuente y déjelo en 14.

11.    De clic en el Botón Negrita y en el botón de Subrayado.

12.    Cambie el color de la letra dando clic en el botón Color de Fuente y seleccione color rojo y en Color de Relleno seleccione amarillo. Recuerde que todos estos pasos están siendo almacenados en la macro que estamos grabando y también recuerde que estos pasos se están efectuando en la celda A1.

13.     Regrese a la Ficha Desarrollador (Programador)  y presione el botón Detener Grabación. Excel guardó los pasos en la Macro1 la cual se activará presionado las teclas Control + a.

14.     Escriba otro nombre en la celda C1 y presione Enter, después regrese a la celda C1. Luego presione las teclas Control + a. Excel efectuará todos los pasos grabados sobre la celda C1, esto quiere decir que el nombre que está en C1 tendrá las características del que está en A1. Tipo de letra, tamaño, negrita y el color que se indicó al grabar la macro.

Nota: Cada vez que usted presione Control + a,  Excel ejecutará la macro y efectuará los pasos en la celda activa. Puede grabar todas las macros que usted desee.

PRACTICA

Genere las siguientes Macros:

1.   Grabe una Macro que se active con Control + f y que permita dar fondo verde claro a la celda con borde azul oscuro, y alineación centrar.

2.   Grabe una Macro que se active con Control + h y que permita cambiar la fuente por Tahoma tamaño 16, estilo negrita, cursiva, subrayada y centrada con alto de fila en 20.

3.   Grabe una Macro que se active con Control + i y que permita dar formato moneda con símbolo pesos, sin decimales, alineación derecha, tamaño de letra en 9.

4.   Guarde el archivo con su nombre en la carpeta Mis Documentos, con la opción: Libro de Excel habilitado para macros.

EJERCICIO

Descargar el archivo y seguir las instrucciones:

https://drive.google.com/file/d/0B3tPe6hjiPzkaVVuaU1vd2VwWlk/view?usp=sharing

Taller 9: Filtros

FILTROS

Los filtros sirven para obtener una lista mas simple a partir de otra mas compleja, esto se hace eliminando filas que cumplen con determinadas condiciones o criterios, por ejemplo si tenemos la siguiente tabla

podría interesarnos saber los alumnos que tuvieron un porcentaje de asistencia del 70 % por lo tanto eliminaremos todas las filas de alumnos que no cumplan con ese criterio.

Para nuestro objetivo, introducimos la tabla en una Hoja de Excel 2007, seleccionamos cualquier celda de lista y vamos a la pestaña "Datos" en el panel  "Ordenar y filtrar" donde pulsamos en el ícono señalado por la flecha (que se pone de color anaranjado)

automáticamente aparecen dos controles en  las etiquetas como se muestra

si oprimimos en control que muestra la flecha, aparece un menú desplegable que nos permite establecer los criterios que nos interesan

para nuestro objetivo nos interesa la sección recuadrada en rojo, que tiene los porcentajes de asistencia con una casilla de verificación a la izquierda, para nuestro propósito, deberemos deseleccionar todos los porcentajes distintos al 70%

después de aceptar obtenemos la tabla que nos interesa

Este es un ejemplo muy simple que nos sirve para introducir el tema, solo agregaremos que hay dos tipos de filtros:

• Autofiltros 
  Son del tipo que acabamos de describir,  como se vio, los criterios de filtrado se pueden seleccionar de un menú desplegable..
• Filtros avanzados
  En estos filtros, los criterios de filtrado pueden ser mas complejos. y tambien funcionan en forma diferente

Una de las diferencias entre ambos tipos de filtros se puede apreciar con la simple inspección de la siguiente imagen, que es la tabla final vista en una Hoja de Excel

se puede ver que las filas no están en forma correlativa, con lo que se infiere que las supuestas filas filtradas en realidad están ocultas y si, por ejemplo, quisiéramos contarlas con la función CONTAR el resultado daría la totalidad de las filas pues se contarían las ocultas también, en algunos problemas puede ser un inconveniente que se supera con los Filtros Avanzados ya que con estos se filtran realmente (y no se ocultan) las filas que cumplen con lo criterios.

Vamos a explicar el funcionamiento de los Filtros avanzados utilizando la misma tabla que se empleo para AUTOFILTROS.

 

En los filtros avanzados se utilizan criterios lógicos para filtrar las filas, en este caso, se debe especificar el rango de celdas donde se ubican los mismos, veamos como se procede.

En la cinta de opciones debemos ir a la pestaña "Datos" y luego al panel "Ordenar y filtrar" donde oprimimos el botón "Avanzadas" luego aparece el panel "Filtro avanzado"

Veamos que significan cada uno de las acciones que se pueden tomar:

• Filtrar la lista sin moverla a otro lugar: se filtran los datos en el mismo lugar donde se encuentra la tabla.
• Copiar a otro lugar: la tabla filtrada puede aparecer en un lugar especificado de la misma Hoja o en otra Hoja de cálculo.
• Rango de la lista : automáticamente Excel coloca el rango done esta la lista
• Rango de criterios: es el rango elegido por el usuario para ubicar los criterios de filtrado.
• Copiar a: esta opción queda habilitada cuando se marca la casilla del punto 2, en cuyo caso deberemos especificar el lugar sonde queremos que aparezca la tabla filtrada, para esto solo es necesario especificar donde estarán los rótulos.
• Sólo registros únicos: en el caso de haber registros duplicados, mostrar solo uno de ellos.

Para dar un ejemplo simple filtraremos las mismas filas que en la introducción de AUTOFILTROS  sin moverla a otro rango (recordemos que en este caso se filtraban todas las filas que no tuvieran un porcentaje  del 70%), para hacer esto marcamos en la casilla de verificación de Filtrar la lista sin moverla a otro lugar y  luego elegimos 2 celdas, una para el rótulo y otra para el criterio a cumplir, como se muestra en el recuadro rojo(%D%1:%D%2)

el panel queda como se muestra

al aceptar nos queda la tabla filtrada

                            

            

en este caso las filas se han ocultado como en el caso de autofiltros, para solucionar el problema debemos copiar la tabla filtrada a otro lugar, por ejemplo al rango $E$1:$F$1, quedando el panel emergente como se ve

y la tabla en su nuevo lugar se ve como en la figura

aquí se puede ver que las filas están en forma correlativa y por lo tanto se pueden aplicar funciones, por ejemplo si quisiéremos contar el número de alumnos con un porcentaje de asistencia del 70%, utilizaríamos la función CONTAR y como se puede ver se obtiene el resultado correcto que es 8

Vamos a dar otro ejemplo con dos condiciones. Supongamos que queremos saber cuantos alumnos tuvieron menos de 70% y  mas de 85% de asistencias, en este caso debemos poner 2 condiciones, por lo que necesitaremos una celda mas en el rango de criterios

 

y el panel  Filtro avanzado se configura como sigue

al aceptar obtenemos la tabla

donde al aplicar la función CONTAR vemos que los alumnos que cumplen los criterios son 6.

Taller 8: Macros 2

ELABORACIÓN DE UN FLUJO DE CAJA

En el siguiente taller realizaremos un listado de flujo de caja al cual se le creara un filtro avanzado en una hoja aparte el cual activaremos con una macro automática.

Entre a Excel y comience creando los siguientes campos en la hoja tal como se ve en el gráfico: Flujo de Caja, Fecha, Concepto, Ingreso, Egreso y Saldo Actual.

Ahora presionando la tecla Ctrl y sin soltarla de clic sobre las celdas que se encuentra debajo del título Saldo y la celda E8. De clic en la herramienta bordes selecciona todos los bordes, ver dibujo.

1)

2)

Ahora selecciona los otros títulos como se ve a continuación

A continuación se van a alinear los títulos en cada una de las celdas para ello con las celdas aun seleccionadas y si perdió la selección vuelva a seleccionarlas. De clic en las herramientas de alineación vertical y horizontal respectivamente como se ve a continuación.

Ahora los títulos se verán de la siguiente forma; ajusta el tamaño de las columnas para una mejor distribución de los títulos en cada una de las celdas.

Con los títulos aun seleccionados asigne color de fondo a las celdas de la siguiente forma:

Ahora selecciona todos los títulos y continúa con la selección hasta la fila 30. Y das clic en la herramienta bordes selecciona todos los bordes, ver dibujo:

Como puedes ver, las líneas de división aún se ven ahora, por tal razón vamos a quitarlas para dar un aspecto más profesional a nuestro trabajo, de esta forma solo se verán los bordes del cuadro. Para ello haga lo siguiente.

En la ficha vistas en la sección mostrar de clic en la opción líneas de cuadricula de tal forma que quede desmarcada, ver imagen:

En este momento ya tenemos el esqueleto de nuestra plantilla desarrollada; Comencemos agregando siguientes datos a la lista.

A continuación ubíquese en la celda D1 y escriba =B8 y presione Enter, ubíquese nuevamente en la celda D1 y asigne color blanco el texto esto ocultara el dato que aparecerá allí, ver Dibujo

Ubíquese en la celda E8 y escriba la siguiente formula; ella le permitirá sumar todo lo que hay en la columna D y a ella restarle la suma de los que hay en la columna E a partir de la celda E11.

La formula se veré como en la siguiente imagen:

Ya con el esqueleto de nuestro flujo de caja comience a agregar los siguientes conceptos que aparecen  a continuación. Ver gráfico:

Observe como el saldo actual varía de acuerdo al concepto y el valor ingresado en la columna ingreso y la columna egreso. Termine la lista agregando conceptos entre pago, cobro, compra.

Ahora vamos a cambiar el nombre de la hoja1 para ello damos doble clic sobre el nombre de la hoja y vamos a escribir Flujo de caja; lo mismo haremos con la hoja2 le pondremos el nombre consulta.

Damos clic en la hoja Flujo de caja y seleccionamos los títulos y presionamos las teclas Ctrl + C, ver imagen:

Damos clic en la hoja de Consulta nos ubicamos en la celda B4 y presionamos Ctrl+V y luego nos ubicamos en la celda B8 y presionamos Ctrl+V; ajuste el ancho de la columna mas o menos como se ve en la siguiente imagen.

Ahora seleccione las celdas que se encuentran debajo de los primeros títulos como se ve en la siguiente imagen:

Asigne borde a las celdas seleccionadas y oculte las líneas de cuadricula como lo hizo en la hoja anterior. Ahora su trabajo se debe ver como la imagen que se encuentra a continuación.

De clic en la hoja Flujo de Caja, seleccione el rango de la lista donde registraremos los movimientos de caja, luego de clic en el cuadro de nombre y escriba Regcaja y presione Enter; esto le dará un nombre al rango de lista seleccionado, que utilizaremos más tarde. Ver imagen: