MS - Excel

UES. FMO

Objetivos Familiarizarse con el entorno Excel. Conocer los elementos y conceptos

asociados con las planillas electrónicas.

Desplazarse adecuadamente por las hojas de calculo

Escribir y editar textos Crear, agregar y editar fórmulas

sencillas Guardar hojas y libros Excel.

UES. FMO

La Hoja De Calculo De ExcelUna hoja de cálculo es un programa que permite realizar cálculos aritméticos, estadísticos, financieros, etc., con una serie de datos previamente introducidos. La pantalla principal de trabajo de Excel es muy similar a la de cualquier otra aplicación Windows.

UES. FMO

Los Menús De Excel Los menús de Excel

 Los menús de Excel contienen acciones muy comunes en la mayoría de aplicaciones Windows. Las acciones más importantes a grandes rasgos son las siguientes:

a) Menú Archivo: podemos abrir, guardar, imprimir, etc. Excel incorpora una nueva característica en este menú que es la posibilidad de guardar el archivo en formato HTML

b) Menú Edición: permite la mayoría de acciones conocidas como copiar, pegar, buscar, reemplazar, etc.

c) Menú Ver: sirve para cambiar el aspecto de la ventana. Una característica especial de este menú es la posibilidad de ver los saltos de página previos a una impresión.

UES. FMO

Los Menús De Excel

d) Menú Insertar: permite insertar celdas, hojas, saltos de página, gráficos, funciones, etc.

e) Menú Formato: para cambiar el aspecto de las celdas, tipos de letra, etc.

f) Menú Herramientas: revisión ortográfica, auditoría, o realizar macros.

g) Menú Datos: útil para gestionar bases de datos, listas, o esquemas resumidos.

h) Menú Ventana: permite organizar las ventanas abiertas en cascada, mosaico, etc.

i) Menú Ayuda: información sobre aprendizaje y uso de Excel.

UES. FMO

Conceptos Básicos Es importante definir algunos conceptos

fundamentales de Excel Hoja. Se denomina así a la zona donde estamos

trabajando. Cada hoja tiene un nombre que la identifica que podemos cambiar. Los nombres de las hojas se pueden observar en la zona inferior de la pantalla.

Celda. Cuadro individual que forma parte de la hoja. En las celdas introduciremos los datos.

Columna. Se nombran de la A a la Z y están dispuestas en vertical. Después de la columna Z, nos encontramos con la columna AA,AB,AC... y así hasta la AZ. Seguidamente, comenzaría la BA, BB.. y así hasta la última columna que es la IV

UES. FMO

Conceptos BásicosFila. Dispuestas en horizontal, se numeran desde la 1 hasta la 16.384 que es la última.Libro de trabajo. Conjunto de hojas. Un libro puede tener varias hojas. Al grabarlo, se crea un fichero con la extensión XLS con todas las hojas que tuviese el libro.Rango. Grupo de celdas adyacentes, es decir, que se tocan. Un rango de celdas por ejemplo que va desde la A1 hasta la A5 se reflejaría con el siguiente nombre: A1:A5 El nombre de un rango siempre hará referencia a la primera y a la última celda seleccionadas.

UES. FMO

La Hoja de ExcelDesplazamiento por la hojaPara desplazarte a través de las celdas de Excel puedes utilizar alguno de estos métodos: Con las teclas de movimiento de cursor del teclado, con un clic en una celda específica, con la función Ir a.... del menú Edición (o bien la tecla F5)

Selección de celdasPara seleccionar celdas simplemente debemos situar el cursor en medio de una celda, pulsar el botón izquierdo del ratón y, sin soltarlo, “arrastrar” hacia alguna dirección.

UES. FMO

Escribir En Excel

La escritura del texto en Excel es sumamente sencilla. Tan sólo hemos de situarnos en la celda requerida y escribir directamente el texto. Una vez escrito, podemos aceptarlo pulsando la tecla Intro o bien en la barra de fórmulas pulsar el botón Introducir

UES. FMO

Modificar Y Borrar Texto

Para modificar el texto de una celda podemos

Situar el cursor en la celda y escribir directamente el nuevo texto.

Situar el cursor en la celda y pulsar clic en la barra de fórmulas.

Pulsar doble clic en la celda del texto Situar el cursor en la celda y pulsar la tecla F2 Para borrar el texto de una celda podemos: Situar el cursor en la celda y pulsar la tecla Supr. Ir a Edición – Borrar Pulsar el botón derecho y escoger la opción Eliminar. De

todas formas, siempre es recomendable optar por la solución más rápida (en este caso, la tecla Supr.).

MS - Excel

UES.FMO

Objetivos

Asignar, cambiar y borrar nombre a las hojas de calculo de un libro.

Desarrollar las distintas opciones de operaciones con las hojas de calculo (mover, borrar, desplazarse, etc.)

Crear, agregar y editar fórmulas sencillas

Guardar hojas y libros Excel.

Operación con Hojas de Excel

Un libro de trabajo consta de varias hojas. Inicialmente, trabajamos con tres hojas cuyas etiquetas podemos observar en la parte inferior de la hoja. No obstante, podemos insertar hojas, copiarlas, moverlas, borrarlas, seleccionarlas.Cambiar el nombre a una hojaPulsa doble clic sobre la etiqueta de la Hoja1 y cuando esté seleccionada, escribe directamente el nombre que deseas darle.Otro sistema para cambiar el nombre será desde Formato – Hoja – Cambiar nombre, o bien desde el menú contextual (botón derecho del ratón).

Operación con Hojas de ExcelCopiar una hoja: Manteniendo la tecla de CONTROL pulsada, arrastra la pestaña de la hoja Listado una posición hacia su derecha.. Otro sistema sería desde Edición – Mover o copiar hoja. Mover una hoja: Arrastra directamente (sin mantener la tecla de CONTROL pulsada), la hoja Listado hacia otra posición.Insertar una hoja: Selecciona con un click la hoja Listado. Abre el menú Insertar y escoge la opción Hoja. También podríamos insertarla con el botón derecho del mouse.Eliminar una hoja: Selecciona cualquier hoja y pulsa el botón derecho del ratón. Escoge la opción Eliminar. Acepta el mensaje de Excel.

UES.FMO

Ingreso de Datos en la Hoja de Calculo

Tipos de datosValores constantes. Todo tipo de datos que escribamos directamente ya sea texto o números. Este valor no cambia a no ser que lo modifiquemos o borremos.

Fórmulas. Un valor especial que hace referencia a celdas, nombres, datos, etc, y que producen un resultado. Las fórmulas comienzan siempre con el signo de igual (=). Si modificamos el valor de una celda a la que la fórmula esté haciendo referencia, el resultado de la fórmula varía automáticamente .

Introducción de Valores Numéricos

Excel posee para los valores numéricos el formato general, es decir, que podemos escribir un número como 200000 sin separadores de miles (el cero) y Excel lo dejará tal y como lo hemos escrito. También podemos utilizar signos como:

El punto (.) para separar los millares La coma (,) para expresar decimales El signo menos (-) para indicar cantidades

negativas. Éstas se pueden indicar también entre paréntesis.

El signo del porcentaje (%) Las fracciones debemos introducirlas de forma

especial, ya que por ejemplo 4/3 Excel lo tomará como una fecha y colocará en la celda el cuatro de marzo (4-mar). En su lugar introduciremos 1 1/3

UES.FMO

Introducción de Valores Numéricos

Si el valor no cabe en la celda, se visualizarán los signos ######. Debemos cambiar el ancho de la columna (como veremos más adelante) para poder visualizar todas las cifras.

Si deseamos introducir un número y que Excel lo tome como un texto, debemos anteponer al número el signo del apóstrofe (‘). Introducción de valores de texto.

Un texto es cualquier conjunto de caracteres que Excel no considera como un número (cadena). Podemos introducir directamente los caracteres en la celda. Un texto puede invadir la celda y celdas de su derecha, y

éste se visualizará por completo siempre que las celdas estén vacías. Si no lo están, el texto será recortado en la celda.

Los textos pueden ajustarse (centrados, alineados, etc.)

UES.FMO

Fecha y Hora

Introducción de fechas y horas

Las fechas se almacenan internamente como números de serie que Excel cuenta desde el día 1 de Enero de 1990 y que transforma en caracteres legibles en pantalla. El usuario puede introducir las fechas de varias formas: 23/07/03, 23-Julio-03, 23-Julio-2003, etc.

Las horas pueden representarse en formatos de 12 o 24 horas.

UES.FMO

Más Opciones en Excel

Copiar y mover celdas Para copiar o mover celdas

podemos recurrir a las conocidas opciones de Copiar, Cortar y Pegar o bien utilizar el sistema de arrastrado.

Dar nombres a las celdas Es posible asignar un nombre a

una celda o a un rango. De esta forma podremos:

a) Desplazarnos a esa celda o rango más fácilmente

b) Utilizar los nombres en una fórmula

c) Identificar mejor los rangos

UES.FMO

Guardar el Archivo

Para guardar las hojas de cálculo el método utilizado es igual que en cualquier programa de Windows (Archivo – Guardar). Cuando guardamos un libro, se están guardando todas las hojas con las que estemos trabajando en aquel momento. Excel guardar sus archivos en formato XLS aunque podemos guardarlo en otros formatos de hojas de cálculo.

Seleccionar Archivo – Guardar o bien pulsa el botón Guardar

Aparecerá la típica pantalla desde donde podemos guardar el archivo.

Las aplicaciones y programas de Office permiten también colocar una contraseña en nuestros archivos por seguridad.

MS - Excel

UES.FMO

Objetivos

Copiar, pegar, insertar, mover celdas, rangos de celdas, filas y columnas.

Crear series numéricas. Buscar y reemplazar datos en una hoja de calculo. Conocer y aplicar las potencialidades de la opción de

edición “pegado especial”. Inmovilizar paneles Crear y administrar mas de una ventanas en un mismo

libro.

UES.FMO

Copiar datosCuando se sitúa el puntero del mouse en la esquina inferior derecha de la celda o de la selección (sobre un punto negro) el puntero del mouse adquiere una forma de cruz negra. Esta forma indica que estamos en modo copiar o modo llenado. Si arrastramos la celda pulsando el botón izquierdo, realizaremos un llenado de celdas. Si arrastramos hacia abajo o hacia un lado, el contenido de las celdas se copiaráSi lo que deseas es seleccionar un rango de celdas que no estén juntas, deberás hacerlo pulsando al mismo tiempo que seleccionas, la tecla de Control del teclado.

UES.FMO

Crear seriesExcel permite crear series de datos a partir del valor inicial de la primera celda o celdas. Simplemente tenemos que utilizar el cuadro de llenado y Excel creará una serie automática.Otra forma de crear series es arrastrar el cuadro de llenado pero con el botón derecho del ratón. Al soltar el botón, Excel mostrará un menú con varias opciones.Si escogemos la opción Series... nos aparecerá un menú donde podemos crear este tipo de series.

UES.FMO

Copiar y Mover celdas

Otra forma de copiar o mover celdas sería situando el puntero del ratón en el mismo borde de la selección.

Arrastrando de esta forma la selección, moveremos las celdas a otra ubicación. Si lo arrastramos manteniendo pulsada la tecla de Control, lo que haremos será copiar las celdas.

UES.FMO

Pegado Especial Pegado especial Esta orden se encuentra

ubicada en el menú Edición y nos permite realizar pegados más específicos que con la orden Pegar habitual.. Si realizamos una acción de Copiar y Pegar normal y corriente, para una serie de celdas con fórmulas, lo que se pegarán serán las fórmulas con el resultado. En cambio, con la orden Pegado especial podemos hacer que sólo se peguen los valores de los resultados, pero no las fórmulas.

UES.FMO

Pegado Especial Las opciones de este menú son:

a) Todo: pega todos los atributos del portapapeles. Es como el pegado normal.

b) Formulas: pega sólo las fórmulas de la celda origen.

c) Valores: pega sólo los valores de la celda origen.

d) Formatos: no se pegarán números ni fórmulas. Sólo el formato (negrita, cursiva, etc.) de las celdas originales.

e) Comentarios: sólo se copian los comentarios de las celdas

f) Validación: se pegan las reglas de validación de entrada de datos.

UES.FMO

Pegado Especial

g) Todo excepto bordes: pega valores, formatos y fórmulas pero no bordes.

h) En la sección de Operación se muestran varias operaciones que pueden realizarse en el área de pegado.

i) Saltar blancos. Si está activada, la información que se pega no se pegará en las celdas en blanco.

j) Transponer. Para transponer una selección de celdas. Esta opción cambia la posición de las filas por columnas.

k) Pegar vínculos. Establece un vínculo con la fuente de datos. Si los datos originales cambian, también cambiarán los datos pegados.

UES.FMO

Insertar y Eliminar Filas y Columnas

Pulsa un clic a la izquierda de la fila, en el número de fila.

Accede a Insertar – Filas o bien pulsa el botón derecho del mouse sobre el número de fila y escoge Insertar. Se habrá insertado una nueva fila.

De la misma forma podríamos borrar una fila completa. (Seleccionándola y accediendo a Edición – Eliminar).

Al igual que las filas, también podemos insertar y eliminar columnas de la misma forma.

UES.FMO

Buscar y reemplazar datos

Al igual que otros programas de Windows, con Excel es posible buscar algún dato en el libro de trabajo desde Edición – Buscar. Aparecerá la típica pantalla desde donde podemos escribir alguna palabra que Excel se encargará de buscar, o si se prefiere, podemos indicarle que reemplace un dato por otro en todo el libro. Esto último podemos hacerlo desde el mismo cuadro de diálogo de Buscar o bien desde la opción Edición – Reemplazar.

UES.FMO

Borrar datos Una opción rápida para borrar todos los

datos de una hoja sería cerrando el libro sin grabarlo y creando uno nuevo, pero es posible que tengamos datos en otras hojas del libro que no queramos desperdiciar. También podríamos eliminar la hoja, pero un buen sistema sería el siguiente:

Pulsa en el cuadro de la esquina superior derecha (encima de los rótulos de las filas y a la izquierda de los rótulos de las columnas). Verás que toda la hoja queda seleccionada.

Pulsa la tecla Supr del teclado. Pulsa un clic en cualquier parte de la hoja

para quitar la selección.

UES.FMO

Inmovilizar PanelesEn hojas muy extensas puede ocurrir que tengamos una o varias filas o columnas con Títulos de nombres y que al desplazar la hoja y debido a su longitud, perdamos de vista esos Títulos que nos pueden servir como referenciaPara Inmovilizar Paneles, accede a Ventana – Inmovilizar panelesVeremos unas líneas negras que significan la división que hemos hecho. Lo que haya por encima y a la izquierda de esas líneas será lo que quede inmovilizado.Para anular la inmovilización de los paneles, deberíamos acceder a Ventana – Movilizar paneles y las líneas de inmovilización desaparecerían, quedando la hoja como estaba antes.

UES.FMO

División en ventanasEn libros extensos es posible crear una o varias ventanas del mismo libro y trabajar en una u otra con hojas distintas. Para ello debemos acceder a Ventana – Nueva ventanaCon esta orden se habrá creado una nueva ventana del mismo libro. Es importante observar el nombre de la ventana en la barra azul del título; aparece junto a dos puntos y el número de ventana.

Si escribimos algo en una de las dos ventanas, veremos cómo la otra se va escribiendo exactamente lo mismo. Para pasar de una ventana a otra pulsaremos un click, y para cerrar una de las dos ventanas, simplemente pulsaremos el botón de cerrar.

MS - Excel

UES.FMO

Objetivos Conocer y aplicar el concepto de formulas en una

celda. Reconocer los elementos de sintaxis en las formulas

de Excel. Reconocer los distintos operadores para la

construcción de formulas. Conocer las jerarquía asociada a los operadores. Identificar los distintos mensajes asociados a error en

las formulas. Diferencias entre formulas relativas y absolutas.

UES.FMO

La sintaxis de una fórmulaUna fórmula es una expresión que introducimos en una celda y que relaciona valores y fórmulas de otras celdas para producir un resultado. Una fórmula comienza siempre con el signo igual (=) y puede contener textos, números, referencias de celdas, etc.

En la celda que contiene una fórmula se visualiza siempre el resultado de la misma y la fórmula en sí se visualiza en la barra de fórmulas.

UES.FMO

Operadores La fórmula combina diferentes operadores

para realizar los cálculos. Estos operadores son:

Artiméticos:

Suma + Resta - Multiplicación * División / Porcentaje % Exponente ^

UES.FMO

Operadores

De comparación

Igual = Distinto <> Mayor > Menor < Mayor o igual >= Menor o igual <=

De texto Concatenación &

UES.FMO

Prioridad en las formulas

Es muy importante señalar que en una fórmula, la introducción de algunos de los diferentes operadores tiene jerarquía (prioridad) sobre otros.

Porcentaje Exponente Multiplicación y división Suma y resta Unión de texto Comparación

UES.FMO

Mensajes de error En algún momento puede producirse el

hecho de que nos equivoquemos en la realización de una fórmula y que ésta intente realizar cálculos con datos erróneos.

#¡DIV/0! Se está intentando dividir un número entre 0

#N/A Valor no disponible #¿NOMBRE? Se ha utilizado un nombre que Excel

no reconoce #¿NULO! Intersección no válida de dos áreas #¡NUM! Número utilizado de forma

incorrecta #¡REF! Referencia no válida a una celda #¡VALOR! Operando o argumento erróneo ####### Columna demasiado estrecha

para ver los datos

UES.FMO

Referencias

Cuando copiamos fórmulas, el contenido de ésta se actualiza a medida que copiamos en horizontal o en vertical. La referencia indica la posición de la celda contenida en la fórmula.

Celdas relativas: indican la posición de la celda como desplazamiento a partir de la cual se está introduciendo la fórmula. Si las celdas referenciadas cambian de ubicación, Excel ajusta las referencias para adaptarlas a la nueva posición.

UES.FMO

Referencias Celdas absolutas: indican posiciones que no

cambian. Una celda se convierte en absoluta añadiendo antes y después de la letra de la columna el signo dólar ($). Por ejemplo: $B$6

Celdas mixtas: combina los dos tipos de referencia anteriores. Por ejemplo: $B6, A$7...

Podemos convertir una celda en absoluta posicionando el cursor al lado del nombre de la columna y pulsando la tecla F4. Esto añade automáticamente el signo dólar a la columna y la convierte en absoluta.

MS - Excel

UES.FMO

Objetivos

Conocer el concepto de función Excel. Reconocer los elementos de la sintaxis de funciones. Conocer y usar las funciones básicas de Autosuma() y

Promedio(). Conocer y usar el asistente de funciones. Construir formulas usando funciones. Desarrollar formulas que incluyan funciones anidadas.

UES.FMO

Funciones en Excel

Una función es una fórmula ya escrita y preparada para realizar cálculos y simplificar el uso de fórmulas extensas. Las funciones tienen un nombre propio y existen multitud de funciones.

Existen funciones que realizan complejos cálculos financieros, estadísticos, matemáticos, etc, y que permiten ahorrar trabajo y tiempo en la escritura de una fórmula.

UES.FMO

Sintaxis de una función

Las funciones deben mantener unas reglas de sintaxis tal y como se indica en el siguiente formato :

=nombre función(argumento1 ;argumento2 ;argumento3...)

UES.FMO

Función AutosumaEs quizá la función más utilizada en una hoja de cálculo. Por ello, Excel proporciona un botón exclusivo para la función Autosuma en la barra de herramientas.Para utilizar una función, podemos escribirla manualmente =SUMA(), usar el botón o bien utilizar el Asistente para funciones.

La función tiene entre paréntesis la celda inicial del rango a sumar y la celda final separadas por dos puntos. Desde aquí podemos modificar manualmente el rango.

UES.FMO

Función PromedioOtra interesante función es la llamada =PROMEDIO(). Funciona exactamente igual que la suma, pero no existe ningún botón, por lo que debemos introducirla manualmente. Cuando introducimos una función mediante el teclado, podemos escribirla por completo o escribir =PROMEDIO( luego marcar el rango y cerrar el paréntesis.

UES.FMO

El Asistente Para Funciones

Existen funciones que realizan complejos cálculos y que tan sólo nos piden unos datos específicos. Si no recordamos la sintaxis de una función, podemos hacerlo con el Asistente de funciones el cual, nos guiará paso a paso hasta obtener el resultado buscado.

Pulsa el botón Pegar función de la barra de herramientas estándar

Busque la función y haga un clic sobre ella. Observa la línea de estado de la ventana; nos explica para qué sirve esa función. Complete los cuadros de texto solicitados.

UES.FMO

Funciones Más Usadas

Algunas de las funciones estadísticas que debemos conocer son:

=SUMA() =PROMEDIO() =MAX() =MIN() =CONTAR()

UES.FMO

Sugerir Funciones

En ocasiones, podemos conocer el cálculo que queremos realizar, pero no si existe alguna función que Excel nos pueda aportar para obtener el resultado.

En este caso, podemos hacer que sea el propio Excel el que nos sugiera una función a utilizar. Cuando ocurra esto, podemos pulsar el botón Pegar función y seguidamente pulsar el botón de ayuda que aparece en la parte inferior del cuadro de diálogo. Aparecerá el asistente de Excel

Pulsando el botón Ayuda con esta característica podemos escribir una descripción de lo que queremos hacer y posiblemente Excel nos ayude.

UES.FMO

Funciones Anidadas Se llaman así aquellas funciones que actúan como

argumento de otra función, es decir, que se encuentran dentro de otra función. En el proceso de cálculo, Excel realiza primero el cálculo de la función interior y después, el resultado de la función exterior teniendo ya en cuenta el resultado que se ha obtenido con la función interior.

La función =SI() Una de las funciones más potentes que se utilizan en

Excel es la función =SI(). Esta función tiene el siguiente formato:

=SI(condición;verdadero;falso) Donde condición es una condición que se tiene que

cumplir. Si ésta se cumple, se ejecutará verdadero, o en caso contrario, se ejecutará falso.

Computación I

UES.FMO

Objetivos

Modificar formato de presentación de las celdas

Crear listas de datos y filtros.

Dar formato a las planillas de calculo.

UES.FMO

Formato de celdas Existen varios botones en la barra

de herramientas que permiten cambiar los formatos de letras, colores, fondos, etc.

No obstante, existe un menú bastante completo desde donde podemos escoge o hasta modificar alguna característica del formato de las celdas.

Para colocar un formato a un grupo de celdas, éstas deberían estar seleccionadas previamente. Cuando colocamos un formato cualquiera, al salir del cuadro de diálogo podemos escribir y los datos en las celdas seleccionadas, aparecerán ya con el formato escogido.

UES.FMO

Formato de celdas Autoformato

Otra forma de establecer un formato para las celdas de una hoja que ya contenga datos, es con la opción llamada Autoformato. Esta opción nos lleva a un menú desde donde podemos elegir entre varios modelos preestablecidos.

Alineación de los datos

Para alinear los datos de una celda tenemos los botones de la barra de herramientas:

Desde el cuadro que hemos visto anteriormente (Formato – Celdas) podemos también utilizar un tipo de alineación más específica.

UES.FMO

Fuentes y Bordes Podemos establecer bordes

para las celdas o rangos. Es importante no confundir los bordes desde las opciones que vamos a ver a los bordes de referencia que vemos normalmente en Excel.

Éstos últimos son por defecto de color gris y nos sirven para tener la referencia de las celdas. En cambio, los bordes añadidos son por defecto negros y forman parte de los datos de la hoja a la hora de visualizarlos o imprimirlos.

UES.FMO

Formato de númerosCuando introducimos números en una hoja de cálculo, el formato de las celdas es el General, es decir, números sin ceros separadores de miles, ni formato monetario, y alineados a la derecha. Para ello, tenemos una pequeña barra de herramientas con los formatos más utilizados, y también el cuadro de diálogo Formato –Celdas.

Desde el menú de Formato – Celdas también podemos cambiar el formato de los números accediendo a un completo menú con numerosos formatos preestablecidos.

UES.FMO

Listas de datos Las listas es una de las opciones que más se utiliza en Excel. Permiten almacenar datos en forma de columnas a modo de base de datos para posteriormente realizar cálculos, consultar datos, realizar sub-totales, etc.

Normalmente, una lista contiene las cabeceras de los datos en la primera fila. Estas cabeceras son los títulos de los campos. Un campo es un dato individual con un nombre propio.

De ser posible, dejar una única lista en la hoja.

UES.FMO

Lista de datos

Las operaciones más comunes con listas son:

Utilizarla para imprimir listados. Ordenarla por un campo en concreto. Crear una ficha llamada “formulario” para

trabajar con la lista. Filtrar datos, es decir, obtener datos de la

lista según unas condiciones específicas. Crear informes de resumen de sub-totales

de datos.

UES.FMO

Ordenar una lista de datos

En ciertos momentos nos interesa que una misma lista sea ordenada por diferentes campos. Para ordenar una lista, Excel dispone de dos opciones:

1. Ordenación rápida: Excel ordena rápidamente a través de un campo mediante los botones de Orden ascendente o descendente de la barra de herramientas.

2. Ordenación por Opción Ordenar del Menú:

Seleccione Datos – Ordenar y aparecerá el cuadro de diálogo: Desde aquí podemos establecer hasta tres criterios

de ordenación. En la imagen se aprecia la prioridad por la queremos la ordenación.

UES.FMO

Validación de los datos

Para validar datos, acceda a Validación de Datos. Aparecerá el siguiente cuadro:

En la lista desplegable configuración podemos elegir qué valores serán válidos para las celdas activas.

UES.FMO

Funciones de búsqueda BUSCARV: compara el valor de la búsqueda con

la primera columna de la lista y nos devuelve un valor asociado en la misma fila.

BUSVARH: compara el valor de la búsqueda con la primera fila de la lista y nos devuelve un valor asociado en la misma columna.

COINCIDIR: compara el valor de búsqueda con el contenido de cierta columna que se le pasa como parámetro y devuelve el índice del registro de la lista.

INDICE: a partir del índice de la lista, nos proporciona el dato del campo o columna que se le pasa como parámetro.

UES.FMO

Funciones de búsqueda

UES.FMO

Filtro automático de Datos

Otra posibilidad para trabajar con listas son los llamados Filtros. Estos actúan en forma de lista desplegable y nos permite filtrar o elegir datos según una o más condiciones específicas.Para filtros AutomáticosSelecciona el rango de la lista

Accede a Datos – Filtro – Autofiltro y pulsa un click en cualquier parte de la lista para quitar la selección.

Computación I

UES.FMO

Objetivos

Conocer los diferentes tipos de gráficos que puede generar usando Excel.

Crear gráficos usando asistente. Modificar gráficos. Personalizar un gráfico. Imprimir gráfico

UES.FMO

Gráficos

Un gráfico en Excel es la representación gráfica de un conjunto de datos de una hoja de cálculo. Podemos crear diferentes tipos de gráficos (barras, columnas, líneas, etc) dependiendo de la información visual que queramos conseguir. Los datos utilizados en su creación, pueden variar y el gráfico se actualizará automáticamente.

UES.FMO

Tipos de gráficosGráfico de áreas:Representan la evolución de las series a lo largo del tiempo. Muestran el volumen de cada serie y el total acumulado de las mismas.

Gráfico de barras: Comparan las series. El eje X se representa verticalmente y el eje Y horizontalmente.

Gráfico de columnas: Representa las series en barras verticales y permite compararlas y analizar las diferencias de valores entre los puntos a través del tiempo.

Gráfico de líneas: Representa las tendencias de los valores a lo largo de un período de tiempo, resaltando la velocidad del cambio.

0

20

40

60

80

100

120

enero febrero marzo abril mayo junio

Serie1

0

20

40

60

80

100

120

1

Meses

Mile

s d

e p

eso

s

enero

febrero

marzo

abril

mayo

junio

0 20 40 60 80 100 120

enero

febrero

marzo

abril

mayo

junio

Serie1

0

20

40

60

80

100

120

enero febrero marzo abril mayo junio

Serie1

UES.FMO

Tipos de gráficosGráfico bursátil: Adecuado para representar datos bursátiles. Es un gráfico ideal para estudiar las fluctuaciones.

Gráfico circular o de sectores: Representa una sola serie de datos que son analizados y cuyo valor se expresa en porcentaje. Se utilizan también para resaltar algún valor concreto.

Gráfico de anillos: Similar al de sectores, no se limita a una sola serie sino que puede representar tantas como deseemos. Las series son los anillos y los colores representan cada categoría.

Gráfico de radar: Cada categoría forma un eje y cada eje sale del punto central, Si existen varias series, todos sus puntos se unirán con una línea. No se podrán intercambiar los valores del orden una vez creado.

0

20

40

60

80

100

120

enero febrero marzo abril mayo junio

Min.

Max.

Prom.

Min.

enero

febrero

marzo

abril

mayo

junio

enero

febrero

marzo

abril

mayo

junio

0

50

100

150enero

febrero

marzo

abril

mayo

junio

Min.

Max.

Prom.

UES.FMO

Tipos de gráficosGráfico XY (de dispersión)

Trabajan con dos ejes de valores. Se selecciona la primera columna del rango para los valores de eje X y la segunda para los del eje Y. Se usan para analizar tendencias de los valores a través del tiempo, y sus posibles relaciones entre series.

Gráfico de superficie (3-D)

Sirve para trabajar con grandes cantidades de datos y su combinación.

0

20

40

60

80

100

120

0 1 2 3 4 5 6 7

Min.

Max.

Prom.

enero

marzo

mayo

Min. Max. Prom.

0

20

40

60

80

100

120

UES.FMO

Crear y Modificar gráficos

Crea una hoja con los datos. Selecciona el rango a graficar Seleccionar Insertar – Gráfico: Aparece un asistente

para la creación del gráfico. En este primer paso podemos elegir el tipo de gráfico que queremos.

Pulsa el botón Siguiente Se mostrará el rango de datos que hemos

seleccionado previamente. Podemos cambiarlo o dejar el que ya está seleccionado.

La pestaña superior Serie nos muestra las series que están seleccionadas en este momento. Las series nos muestran los colores correspondientes a cada una.

La representación de las series se llama Leyenda.

UES.FMO

Crear y Modificar gráficos

UES.FMO

Crear y Modificar Gráficos Pulsa el botón

Siguiente. Aquí podemos

modificar varias opciones como títulos, leyenda, etc.

Escribe en la casilla Título del gráfico.

Escribe como título del eje de las X .

Escribe como título del eje de las Y

UES.FMO

Crear Y Modificar Gráficos: Opciones

La pestaña superior Ejes nos permite activar y desactivar la visualización de los ejes.

La pestaña Líneas de división permite activar o desactivar las líneas horizontales o verticales de división.

La pestaña Leyenda permite activar, desactivar y modificar la posición de la leyenda.

La pestaña Rótulos de datos permite varios modelos de visualización de los títulos de datos.

La pestaña Tabla de datos si está activada, nos muestra en miniatura la tabla origen de los datos del gráfico.

Pulsa el botón Siguiente.

Finalizar

UES.FMO

GráficoFinalmente podemos optar por crear el gráfico en la misma hoja, como un objeto o bien crear el gráfico en una hoja completamente nueva.

Una vez que aparece el grafico, podemos estirar su tamaño desde uno de los nodos de control, moverlo arrastrando desde el interior del gráfico, etc.

UES.FMO

GráficoPara modificar cualquier parte del gráfico podemos pulsar doble clic. Se puede también pulsar un clic sobre alguno de los tres títulos que hemos colocado (título principal, eje X y eje Y) y modificar dicho título.Si el gráfico está seleccionado se puede abrir algún menú y observar que algunas opciones han cambiado. Estas afectan al gráfico.Si se desea borrar un gráfico, sólo hay que tenerlo seleccionado y pulsar la tecla Supr del teclado.

MS - Excel

UES.FMO

Tablas Dinámicas Una tabla dinámica nos

permite modificar el aspecto de una lista de elementos de una forma más fácil, cómoda y resumida. Para crear tablas dinámicas hemos de tener previamente una tabla de datos preparada y posteriormente acceder a Datos – Informe de Tablas y Gráficos Dinámicos.

Aparecerá un asistente, en el cual se nos consulta que es lo que deseamos hacer.

UES.FMO

Tablas Dinámicas

Luego se debe ingresar la referencia de la tabla de datos.Como último paso, Excel nos propone crear la tabla en la misma hoja de trabajo a partir de una celda determinada, o bien en una hoja completamente nueva.Pulsa Finalizar.

UES.FMO

Tablas Dinámicas Es posible que al terminar de diseñar la tabla

dinámica nos interese ocultar algún subtotal calculado. Si es así, debemos pulsar doble click en el campo gris que representa el nombre de algún campo, y en el cuadro de diálogo que aparece, elegir la opción Ninguno. Desde este mismo cuadro podemos también cambiar el tipo de cálculo.

Es posible también mover los campos de sitio simplemente arrastrando su botón gris hacia otra posición.

Podemos activar la visualización de la barra de herramientas para tablas dinámicas (Ver- Barras de herramientas – Tablas dinámicas).

UES.FMO

Búsqueda De ObjetivosEn algunos casos conocemos el resultado que se desea

obtener, pero no las variables que necesita la fórmula para alcanzar dicho resultado.

Para ello, tenemos la opción Buscar objetivos, a través de la cual Excel nos proporcionará el resultado buscado.

Accede a Herramientas – Buscar Objetivos. Rellena las casillas de la ventana y acepta. Excel avisa que ha hallado una solución al problema.

UES.FMO

Búsqueda De Objetivos

Acepta este último cuadro de diálogo. Para trabajar con la opción de Buscar objetivos,

hay que tener presente lo siguiente: Una celda cambiante (variable) debe tener un

valor del que dependa la fórmula para la que se desea encontrar una solución específica.

Una celda cambiante no puede contener una fórmula.

Si el resultado esperado no es el deseado, debemos deshacer la acción.

UES.FMO

Tablas De Datos De Una Y Dos Variables

Existe otro método para buscar valores deseados llamado tablas de variables.

Existen dos tipos de tablas: Tabla de una variable: utilizada cuando se quiere

comprobar cómo afecta un valor determinado a una o varias fórmulas.

Tabla de dos variables: para comprobar cómo afectan dos valores a una fórmula.

Para usar tablas de datos de una o dos variables sigue el procedimiento:

1. Prepare la tabla2. Selecciona el rango acceda a Datos – Tabla3. Rellena las casillas y acepta.

UES.FMO

Escenarios

Un Escenario es un conjunto de proyecciones que se guardan en una sola hoja de calculo. Los escenarios se guardan con un nombre y que tiene por objetivo mostrar como cambian las celdas para un mismo modelo de hoja y una misma formula.

Para crear escenarios sigua el siguiente procedimiento: Acceda a Herramientas – Escenarios y pulsa en Agregar. Rellene las casillas Acepte el cuadro de diálogo. Vuelva a aceptar el siguiente cuadro de diálogo. Vuelva a pulsar en Agregar. Escriba el nombre del escenario Acepte y modifique el siguiente cuadro Acepte y agregue otros escenarios.

UES.FMO

Escenarios Vuelva a escribir otro nombre para cada nuevo escenario: Acepte y modifique las líneas que correspondan Acepte. Se han creado los escenarios con distintas celdas

cambiantes para un mismo modelo de hoja y una misma fórmula.

Seleccione el primer escenario de la lista y pulse en Mostrar. Observe el resultado en la hoja de cálculo.

Haga lo mismo para los otros escenarios. Muéstrelos y observe el resultado. Podemos también crear un resumen de todos los escenarios

existentes en una hoja para observar y comparar los resultados.

Pulsa en Resumen y acepta el cuadro que aparece. Excel creara una nueva hoja en formato de sub-totales (o

en formato tabla dinámica si se hubiera elegido la otra opción). Esta hoja puede ser tratada como una hoja de sub-totales expandiendo y encogiendo niveles.

UES.FMO

Escenarios

Acepte y modifique las líneas que correspondanAcepteSe han creado los escenarios con distintas celdas cambiantes para un mismo modelo de hoja y una misma fórmula.Seleccione el primer escenario de la lista y pulse en Mostrar. Observe el resultado en la hoja de cálculo.Haga lo mismo para los otros escenarios. Muéstrelos y observe el resultado.Podemos también crear un resumen de todos los escenarios existentes en una hoja para observar y comparar los resultados.Pulsa en Resumen y acepta el cuadro que aparece.Excel creara una nueva hoja en formato de sub-totales (o en formato tabla dinámica si se hubiera elegido la otra opción). Esta hoja puede ser tratada como una hoja de sub-totales expandiendo y encogiendo niveles.

MS - Excel

UES.FMO

Objetivos

Conocer y comprender el concepto de macro.

Crear macros para automatizar procesos básicos.

Revisar el código Visual Basic de una macro.

UES.FMO

Introducción a las macros Las macros son funciones que ejecutan

instrucciones automáticamente y que nos permiten ahorrar tiempo y trabajo.

Los pasos para crear una macro son: Acceder a Herramientas – Macro – Grabar

macro Pulsar las teclas o tareas una tras otra

teniendo cuidado en no equivocarnos. Detener la grabación de la macro. Depurar posibles errores o modificar la

macro.

Las macros también pueden ejecutarse pulsando una combinación de teclas específica, por lo que ni siquiera debemos acceder a un menú para invocar a la macro, o bien asignársela a un botón.

UES.FMO

Creación De Una Macro1. Herramientas – Macro – Grabar nueva macro.

2. Asigne nombre a la macro y aceptar el cuadro de diálogo.

A continuación aparecerá un pequeño botón desde el que podrás detener la grabación de la macro.

A partir de estos momentos, todo lo que hagas (escribir, borrar, cambiar algo...) se irá grabando.

La creación de macros no se limita a pequeñas operaciones rutinarias, también podemos crear macros más complejas que resuelvan situaciones complicadas de formateo y cálculo de celdas que nos ahorrarán mucho trabajo.

Excel crea sus macros utilizando el lenguaje común de programación de los componente de Office: el Visual Basic, por lo que si tenemos conocimientos de programación podremos crear macros y modificarlas usando el código del lenguaje

UES.FMO

Código De La Macro Excel trabaja sus macros básicamente en el lenguaje

común Visual Basic.

Para revisar el código Visual Basic de una macro, seleccione Herramientas - Macros - Editor de Visual Basic.

Ventana de proyecto: es donde se almacenan los nombres de las hojas y las macros que hay creadas.

Ventana de módulos: un módulo es una rutina escrita en Visual Basic que se almacena en forma de archivo y que puede ser utilizada en cualquier programa.

Ventana de código: aquí es donde podemos escribir y modificar el código de la macro actual.

Excel Avanzado

UES.FMO

OBJETIVOS

Conocer y comprender el concepto de Función.

Conocer la estructura de una función y diferenciar cada una de sus partes.

Escribir funciones usando las potencialidades de Excel. Construir funciones anidadas.

Usar adecuadamente las funciones =SI() y =Buscarv().

Copiar formulas que contengan funciones usando los conceptos de referencia relativa y absoluta.

UES.FMO

FUNCIONES

Las funciones son fórmulas predefinidas que ejecutan cálculos utilizando valores específicos, denominados argumentos, en un orden determinado o estructura.

Las funciones pueden utilizarse para ejecutar

operaciones simples o complejas.

Ejemplo:=Suma(A1:A8)

UES.FMO

ESTRUCTURA DE UNA FUNCIÓN

La estructura de una función comienza por el signo igual (=), seguido por el nombre de la función, un paréntesis de apertura, los argumentos de la función separados por comas y un paréntesis de cierre.

Ejemplo

=Redondear(A4;3)

UES.FMO

NOMBRE DE LA FUNCIÓN

Para obtener una lista de funciones disponibles, haga clic en una celda y presione MAYÚSC+F3.

Existen muchos tipos de funciones; matemáticas, estadísticas, de fecha, científicas, etc., alguna de las cuales contiene una sintaxis bastante difícil

UES.FMO

EL ASISTENTE DE FUNCIONES

Si no recordamos la sintaxis de una función, podemos hacerlo con el Asistente de funciones el cual, nos guiará paso a paso hasta obtener el resultado buscado.

¿Cómo hacerlo?: Borra el contenido de la última fórmula y sitúa el cursor

en ella. Pulsa el botón Pegar función de la barra de

herramientas estándar

UES.FMO

FUNCIONES ANIDADAS

Se llaman así aquellas funciones que actúan como argumento de otra función, es decir, que se encuentran dentro de otra función.

En el proceso de cálculo, Excel realiza primero el cálculo de la función interior y después, el resultado de la función exterior teniendo ya en cuenta el resultado que se ha obtenido con la función interior.

UES.FMO

FUNCIONES ANIDADAS

Por ejemplo, la función:

=RAIZ(POTENCIA(2;6))

Primero calculará el resultado de la función interior, o sea, de la potencia, cuyo resultado es 64 y luego se calculará el resultado de la exterior, teniendo en cuenta ya este resultado.

UES.FMO

LA FUNCIÓN =SI()

Una de las funciones más potentes que se utilizan en Excel es la función =SI(). Esta función tiene la siguiente estructura:

=SI(condición;verdadero;falso)

Donde condición es una condición que se tiene que cumplir. Si ésta se cumple, se ejecutará verdadero, o en caso contrario, se ejecutará falso. Por ejemplo:

=SI(A3>B12;”Correcto”;”Incorrecto”)

Si la celda A3 es mayor que la celda B12, aparecerá la palabra Correcto, en caso contrario, aparecerá la palabra Incorrecto.

UES.FMO

LA FUNCIÓN =BUSCARV()

Busca un valor específico en la columna más a izquierda de una matriz y devuelve el valor en la misma fila de una columna especificada en la tabla.

Utilice BUSCARV cuando los valores de comparación se encuentren en una columna situada a la izquierda de los datos que desea encontrar o viceversa.

UES.FMO

LA FUNCIÓN =BUSCARV()

También existe la función BUSCARH, que a diferencia de BUSCARV, realiza búsquedas en forma horizontal.

Entonces tenemos: La V de BUSCARV significa "Vertical". La H de BUSCARH significa “Horizontal”.

Sintaxis

=BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado)

UES.FMO

ACERCA DE LAS REFERENCIAS

Cuando copiamos fórmulas de la forma que acabamos de ver, el contenido de la fórmula se actualiza a medida que copiamos en horizontal o en vertical.

La referencia indica la posición de la celda contenida en la fórmula.

UES.FMO

CELDAS RELATIVAS

Indican la posición de la celda como desplazamiento a partir de la cual se está introduciendo la fórmula.

Si las celdas referenciadas cambian de ubicación, Excel ajusta las referencias para adaptarlas a la nueva posición.

UES.FMO

CELDAS ABSOLUTAS

Indican posiciones que no cambian.

Una celda se convierte en absoluta añadiendo antes y después de la letra de la columna el signo pesos($).

Por ejemplo: $B$6

Excel Avanzado

UES.FMO

OBJETIVOS

Conocer y utilizar funciones de Base de Datos.

Crear funciones de Base de Datos con más de un criterio para determinar la función de calculo.

UES.FMO

FUNCIONES BASE DE DATOS

Microsoft Excel incluye funciones que analizan los datos almacenados en listas o bases de datos.

Cada una de estas funciones, denominadas colectivamente funciones BD, usa tres argumentos

función(base_de_datos;nombre_de_campo;criterios)

Todas las funciones BD utilizan el mismo conjunto de argumentos.

UES.FMO

TIPOS DE FUNCIONES BD Dentro de la gama de funciones BD que contempla

MS-Excel, podemos mencionar algunas de mayor importancia a saber:

BDPROMEDIO. Devuelve el promedio de los valores de una columna de

una lista o base de datos que coinciden con las condiciones especificadas.

SintaxisBDPROMEDIO(base_de_datos;nombre_de_campo;criterios)

BDSUMA. Suma los números de una columna de una lista o base de datos

que concuerden con las condiciones especificadas.Sintaxis

BDSUMA(base_de_datos;nombre_de_campo;criterios)

UES.FMO

Funciones de Base de Datos BDCONTAR.

Cuenta las celdas que contienen números en una columna de una lista o base de datos y que concuerdan con las condiciones especificadas.

El argumento nombre_de_campo es opcional. Si se pasa por alto, BDCONTAR cuenta todos los registros de la base de datos que coinciden con los criterios.

SintaxisBDCONTAR(base_de_datos;nombre_de_campo;criterios)

BDEXTRAER. Extrae un único valor de una columna de una lista o

base de datos que coincida con las condiciones especificadas.

SintaxisBDEXTRAER(base_de_datos;nombre_de_campo;criterios)

UES.FMO

Funciones de Base de Datos

BDMAX / BDMIN. Devuelve el valor máximo de una columna de una lista o base

de datos que coincida con las condiciones especificadas.Sintaxis

BDMAX(base_de_datos;nombre_de_campo;criterios)

Devuelve el valor mínimo de una columna de una lista o base de datos que coincida con las condiciones especificadas.

SintaxisBDMIN(base_de_datos;nombre_de_campo;criterios)

UES.FMO

UTILIZAR FUNCIONES BD La importancia de las funciones BD radica en que me

permiten realizar ciertas operaciones aritméticas y estadísticas estableciendo criterios.

Para ello debo contar con una tabla de datos con varios campos, cada uno de ellos debidamente rotulados. Es decir debe existir un nombre (o encabezado) para cada columna de la tabla (o base) de datos.

UES.FMO

CREAR FUNCIONES BD

Establezco los criterios a consultar en celdas en blanco fuera de la tabla de datos. En estas celdas debo escribir el nombre del campo y los criterios a consultar. Una vez establecidos los criterios formulo mi función.

Según la sintaxis descrita anteriormente la función debe contener

Base de Datos. Hace referencia a las celdas que contienen la tabla de datos. Debe incluir los encabezados

Nombre del Campo. Es el nombre de la columna de la tabla de datos sobre la cual aplico la función. Debe estar entre comillas doble.

Criterios. Es el rango de celdas que contiene las condiciones especificadas.

UES.FMO

CRITERIOS MULTIPLES

En las funciones BD se puede escribir más de un criterio, para determinar el cálculo de la función.

Si deseo establecer criterios que se cumplan a la vez debo escribirlos en celdas pareadas en forma horizontal.

Si deseo establecer criterios donde se cumplan tanto el uno como el otro debo escribirlos en celdas pareadas pero esta vez en forma vertical.

Considere que debe respetarse para cada criterio el nombre del encabezado de la columna respectiva.

Excel Avanzado

UES.FMO

OBJETIVOS

Filtrar tablas de Base de Datos usando filtros automáticos.

Crear y aplicar filtros avanzados a una tabla de Base de Datos, como alternativa a los filtros automáticos.

Construir y aplicar subtotales a tablas de Base de Datos, usando distintos criterios de agrupación y estadígrafos.

UES.FMO

FILTROS

Aplicar filtros es una forma rápida y fácil de buscar un subconjunto de datos de un rango. Un rango filtrado muestra sólo las filas que cumplen el criterio que se especifique para una columna. Existen dos tipos de filtro

Autofiltro. Filtro avanzado.

El filtrado no reorganiza los rangos. El filtrado oculta temporalmente las filas que no se desea mostrar.

UES.FMO

AUTOFILTRO

Para aplicar Autofiltro debo posicionarme en cualquier lugar de la tabla de datos, dirigirme al menú Datos y de la opción Filtrar hacer un clic sobre Autofiltro.

Aparecerán unos botones en los encabezados de cada columna que me permitirán elegir los criterios para realizar el filtro.

UES.FMO

AUTOFILTRO

Para aplicar el filtro debo pinchar el botón del encabezado y luego elegir una opción.

Puedo también personalizar el filtro estableciendo ciertos criterios. Para ello debo escoger la opción Personalizar del menú que se despliega al hacer clic en el botón del encabezado

UES.FMO

FILTRO AVANZADO

El comando Filtro avanzado permite filtrar un rango en contexto, como el comando Autofiltro, pero no muestra listas desplegables para las columnas. En lugar de ello, tiene que escribir los criterios según los cuales desea filtrar los datos en un rango de criterios independiente situado sobre el rango. Un rango de criterios permite filtrar criterios más complejos.

UES.FMO

SUBTOTALES

Microsoft Excel puede calcular automáticamente valores de subtotales y de totales generales en una lista. Cuando se insertan subtotales automáticos, Excel esquematiza la lista para que se puedan mostrar y ocultar las filas de detalle de cada subtotal.

Para insertar subtotales, primero se ordena la lista para agrupar las filas cuyos subtotales se desea calcular. Después pueden calcularse los subtotales de cualquier columna que contenga números.

UES.FMO

SUBTOTALES

Ejemplo. Aplique subtotales a la columna de Precio Unitario para calcular el total de precios de productos distribuidos por Zona. Para cada cambio en zona debo agregar un subtotal en la columna Precio Unitario.

Primero debo ordenar la tabla, por Zonas para que estas me queden agrupadas. Una vez ordenada la tabla voy al menú datos y hago clic sobre Subtotales.

En la ventana que aparece debo considerar para este ejemplo.

Para cada cambio en: Zona.(ya explicado) Aplicar función: Suma. (estoy sumando los

precios unitarios de productos)

UES.FMO

SUBTOTALES Agregar subtotal a:

Aquí debe preguntar ¿donde agregaré mi subtotal?, a pesar que puede ser bajo cualquier columna lo más lógico es que lo agregue a la columna sobre la cual estoy realizando el cálculo. En este caso bajo la de Precio Unitario.

Las otras opciones Reemplazar subtotales.

En caso de que ya existan subtotales calculados, si esta marcada esta casilla de verificación, reemplaza los datos anteriormente calculados.

Resumen bajo los datos. Siempre debe estar marcada, pues el resumen

de datos va siempre abajo. luego en Aceptar para ver el resultado.

UES.FMO

RESULTADO EJEMPLO

Excel Avanzado

UES.FMO

OBJETIVOS

Conocer los controles asociados a la construcción de formularios.

Diseñar y crear formularios de visualización de datos.

UES.FMO

FORMULARIOS

Otra forma de mostrar datos y realizar cálculos en MS – Excel es diseñando y creando formularios en una hoja de cálculo.

Los formularios en pantalla pueden incluir controles, como botones de opción y listas desplegables.

Puede proteger un formulario en pantalla de modo que sólo estén disponibles ciertas celdas para la entrada de datos, y también puede validar los datos para asegurarse de que los usuarios sólo escriben los tipos de datos que requiere el formulario.

UES.FMO

ELEMENTOS DE UN FORMULARIO

Los formularios no sólo están formados por controles, sino que también por funciones que pueden estar asociadas a los controles.

Para poder acceder a los controles debemos activar la barra de herramientas de formulario.

Para ello vamos al menú Ver – Barra de Herramientas y elegimos la opción Formulario

UES.FMO

TIPOS DE CONTROLES

Casilla de verificación. Opción que puede activarse o desactivarse. Puede tener activada más de una casilla de verificación a la vez en una hoja.

Botón de comando. Botón que inicia una acción cuando se hace clic en él.

Cuadro de grupo. Agrupa los controles relacionados, como los botones de opción o las casillas de verificación.

UES.FMO

Controles

Botón de opción. Selecciona una de las opciones de un grupo contenido en un cuadro de grupo. Utilice los botones de opción para permitir solamente una de varias posibilidades.

Cuadro de lista. Cuadro que contiene una lista de elementos.

Cuadro combinado. Cuadro de texto que contiene un cuadro de lista desplegable. Puede seleccionar una opción de la lista o escribir una entrada propia en el cuadro.

Etiqueta. Texto que proporciona información sobre un control, de la hoja de cálculo o de un formulario.

UES.FMO

EJEMPLO DE FORMULARIO

UES.FMO

CREAR UN FORMULARIO

Para crear un formulario se debe considerar lo siguiente: Tener una tabla de datos de donde extraer los

datos que mostraré en el formulario. Activar la barra de herramientas formulario

para poder insertar los controles. Para insertar un botón debo marcarlo en la barra de herramientas y “dibujarlo” sobre la hoja de cálculo.

Asociar o vincular a cada control una celda. Esta será la referencia para poder insertar en el formulario una función asociada al control.

Excel Avanzado

UES.FMO

OBJETIVOS

Conocer los elementos de configuración de los controles asociados al diseño y construcción de formularios.

Configurar los controles de un formulario de datos.

UES.FMO

BARRA DE HERRAMIENTAS FORMULARIO

Cada control que figura en la barra de herramientas de formulario, tiene propiedades.

Cada propiedad es importante de revisar, por que de ello depende la configuración de los botones y por ende, el buen funcionamiento del formulario.

Para acceder a las propiedades de cada control se debe hacer un clic con el botón secundario del mouse sobre el control y elegir del menú emergente la opción formato de control.

UES.FMO

CASILLA DE VERIFICACIÓN Valor. Determina el estado de la

casilla de verificación; es decir, si está activada (Activado), desactivada (Sin activar) o ninguna de las dos (Mixto).

Vincular con la celda. Una celda que devuelve el estado de la casilla de verificación. Si la casilla de verificación está activada, la celda contiene VERDADERO. Si la casilla de verificación está desactivada, la celda contiene FALSO. Si la casilla de verificación es mixta, la celda contiene #N/A. Si la celda está vacía, Excel interpreta que el estado de la casilla de verificación es FALSO.

Sombreado 3D. Muestra la casilla de verificación con un efecto de sombreado tridimensional.

UES.FMO

BOTON DE OPCIÓN Valor. Determina el estado

inicial del botón de opción; es decir, si está activado (Activado) o desactivado (Sin activar).

Vincular con la celda. Devuelve el número del botón de opción seleccionado en el grupo de opciones (el primer botón de opción es el número 1). Utilice la misma celda Vincular con la celda para todas las opciones de un grupo. Después podrá utilizar el número devuelto en una fórmula o macro para responder a la opción seleccionada.

UES.FMO

CUADRO DE LISTA Rango de entrada. Hace referencia

al rango que contiene los valores que se mostrarán en el cuadro de lista.

Vincular con la celda. Devuelve el número del elemento seleccionado en el cuadro de lista (el primer elemento de la lista es 1). Este número puede utilizarse en una fórmula o macro para que devuelva el elemento real del rango de entrada.

Tipo de selección. Especifica el modo en que pueden seleccionarse los elementos de la lista. Si se define el tipo de selección como Múltiple o Extendida, la celda especificada en el cuadro Vincular con la celda no se tendrá en cuenta.

Sombreado 3D. Muestra el cuadro de lista con un efecto de sombreado tridimensional.

UES.FMO

CUADRO COMBINADO

Rango de entrada. Hace referencia al rango que contiene los valores que se mostrarán en la lista desplegable.

Vincular con la celda. Devuelve el número del elemento seleccionado en el cuadro combinado (el primer elemento de la lista es 1). Este número puede utilizarse en una fórmula o macro para que devuelva el elemento real del rango de entrada.

Líneas de unión verticales      Especifica el número de líneas que aparecerá en la lista desplegable.

Sombreado 3D      Muestra el cuadro combinado con un efecto de sombreado tridimensional.

UES.FMO

BOTON DE NÚMERO

Valor actual. La posición relativa del cuadro de desplazamiento dentro de la barra de desplazamiento.

Valor mínimo. La posición del cuadro de desplazamiento más próxima a la parte superior de una barra de desplazamiento vertical o al extremo izquierdo de una barra de desplazamiento horizontal.

Valor máximo. La posición del cuadro de desplazamiento más lejana a la parte superior de una barra de desplazamiento vertical o al extremo derecho de una barra de desplazamiento horizontal.

Incremento. El tamaño del espacio que se desplazará el cuadro de desplazamiento cuando se haga clic en la flecha situada en cualquier extremo de la barra de desplazamiento.

Cambio de página. El tamaño del espacio que se desplazará el cuadro de desplazamiento cuando se haga clic entre el cuadro de desplazamiento y una de las flechas de desplazamiento.

UES.FMO

BARRA DE DESPLAZAMIENTO Valor actual. La posición relativa

del cuadro de desplazamiento dentro de la barra de desplazamiento.

Valor mínimo. La posición del cuadro de desplazamiento más próxima a la parte superior de una barra de desplazamiento vertical o al extremo izquierdo de una barra de desplazamiento horizontal.

Valor máximo. La posición del cuadro de desplazamiento más lejana a la parte superior de una barra de desplazamiento vertical o al extremo derecho de una barra de desplazamiento horizontal.

Incremento. El tamaño del espacio que se desplazará el cuadro de desplazamiento cuando se haga clic en la flecha situada en cualquier extremo de la barra de desplazamiento.

Cambio de página. El tamaño del espacio que se desplazará el cuadro de desplazamiento cuando se haga clic entre el cuadro de desplazamiento y una de las flechas de desplazamiento.

Introducción a las Tablas y Gráficos Dinámicos

Tablas dinámicas y gráficos dinámicos

Las tablas dinámicas (TD) resumen grandes volúmenes de datos para obtener información resumida utilizando herramientas de totales, subtotales, filtros de datos; entre otros, pudiendo visualizar todo o parte de los datos de la tabla origen.

Una tabla dinámica consiste en el resumen de un conjunto de datos, atendiendo a varios criterios de agrupación, representado como una tabla de doble entrada que nos facilita la interpretación de dichos datos. Es dinámica porque nos permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentación de los datos, visualizando o no los datos origen, etc...

Crear un informe de tabla dinámica

Se creara una tabla dinámica a partir de unos datos que ya se tienen

Para crear una tabla dinámica, Excel nos proporciona la opción Insertar > tabla dinámica

Suponer que se tiene una colección de datos de los artículos de un almacén con el número de referencia y el mes de compra, además se sabe la cantidad comprada y el importe del mismo

Como se ve a continuación

En este ejemplo se indicara que se seleccionaran los datos de un rango de celdas y que quiere ubicar la tabla dinámica en una hoja de cálculo nueva.

Se podría crear una conexión con otra aplicación para obtener los datos desde otra fuente diferente a Excel.

En el caso de seleccionar la opción Selecciona una tabla o rango se deben seleccionar todas las celdas que vayan a participar, incluyendo las cabeceras.

Se procede siguiendo los siguientes pasos:

1. El primer paso es verificar que todas las columnas del rango de celdas a utilizar en la tabla dinamica tengan encabezados de columna, en caso contrario Excel mostrara una ventana de error advirtiendo que no se esta cumpliendo esta regla.

2. Se va a crear una tabla dinámica a partir de estos datos para poder examinar mejor las ventas de cada artículo en cada mes. Para ello se va a la pestaña Insertar y hace clic en Tabla dinámica.

3. Aparece el cuadro de diálogo de creación de tablas dinámicas. Desde aquí se puede indicar el lugar donde se encuentran los datos que se quieren analizar y el lugar donde se quiere ubicar la tabla dinámica.

Este es el informe de

Tablas Dinámicas

Este es el área de

trabajo para formar las

Tablas Dinámicas

Y al hacer clic en aceptar se genera otra hoja con la información y el formato que se ve a continuación

Lista de Campos

Área de rótulos de columna

Área donde se ubican los

valores numéricos

Área de filtro de datos

Área de rótulos de

fila

Examinando con mas detenimiento el recuadro de la derecha, se puede identificar las siguientes aéreas:

Precisamente se observa en este panel en la derecha de la pantalla.

Desde este panel se puede personalizar la forma en que van a verse los datos en la tabla dinámica.

Con esta herramienta se podría construir una tabla dinámica con la siguiente estructura:- Una fila para cada una de las Referencias de la tabla.- Una columna para cada uno de los Meses de la tabla. - En el resto de la tabla se incluirá el total del Importe para cada Referencia en cada Mes.

Para ello simplemente se tendrán que arrastrar los elementos que se ven listados a su lugar correspondiente al pie del panel.

En este ejemplo se debería arrastrar el campo REF a , el campo MES a y finalmente el campo IMPORTE a la sección

Tras realizar lo anterior, se obtendría la siguiente tabla

• Se puede ver que la estructura es la que hemos definido anteriormente, en el campo fila tenemos las referencias, en el campo columnas tenemos los meses y en el centro de la tabla las sumas de los importes.

Con esta estructura es mucho más fácil analizar los resultados.

Una vez creada la tabla dinámica nos aparecen dos pestañas: Opciones y Diseño

• El panel lateral seguirá pudiéndose utilizar, así que en cualquier momento podremos quitar un campo de un zona arrastrándolo fuera.

Con esto vemos que en un segundo podemos variar la estructura de la tabla y obtener otros resultados sin casi esfuerzos.• Si arrastrásemos a la zona de datos los campos cantidad y

total, obtendríamos la siguiente tabla, más compleja pero con más información:

• Puede que no se visualice la tabla de la misma forma, al añadir varios campos en la sección Valores el rótulo aparecerá en una las secciones de rótulos, si aparece en Rótulos de columna desplegar la lista asociada a él y seleccionar la opción Mover a rótulos de fila.

Luego en la Pestaña Diseño > Diseño > Diseño de Informe se pueden apreciar diferentes opciones para mejorar la apariencia de la Tabla. Por ejemplo:

Aplicar filtros a tabla dinámica Al observar el formato que toma la tabla dinámica se puede

percibir que de forma automática quedan activados algunos filtros que permiten visualizar solo algunos datos.

Obtener promedios en una Tabla dinámica Por defecto, al crear una tabla dinámica, Excel genera

unos totales con sumatorio, puede interesar modificar esas fórmulas por otras como para ser sacar el máximo o el mínimo, el promedio, etc.

Para hacer esto hay que situarse en cualquier celda de la zona que se quiere rectificar y hacer clic con el botón derecho del ratón, aparecerá un menú emergente con diferentes opciones, se debe escoger la opción Configuración de campo de valor... Y aparecera un cuadro de diálogo como este:

Herramientas Avanzadas de Excel

Herramientas avanzadas:

Se plantearan tres de las herramientas de mayor utilidad dentro de Excel:

1. El uso de formulas con operadores lógicos

2. Los análisis de escenarios ¿Qué sucede si?

3. Formatos condicionales

Uso de formulas con operadores lógicos

Los operadores lógicos de Excel son de mucha utilidad cuando se desea crear formulas que tomen decisiones, dependiendo si cumple o no algún criterio en particular. Se estudiaran las siguientes funciones:

FunciónYOSi

Función “Y” La función “Y” evalúa “n” criterios, si

todos son verdaderos, da como resultado una cadena de texto con la palabra “Verdadero”. Devuelve “Falso” si uno o mas criterios no se cumplen.

La sintaxis de esta función en Excel es: Y(valor_logico1, [valor_logico2], …) Valor_logico”n”: son las n condiciones

a evaluar

Para hacer uso de esta función (al igual que todas las funciones en Excel) se debe hacer clic en el botón insertar función que se encuentra en la barra de formula y enseguida aparece la ventana de esta opción.

Ejemplo de aplicación 1:

Se tienen las notas de 4 personas y se desean evaluar 2 criterios simultáneamente:

1. Que la nota 2 sea mayor que la nota 1

2. Que la nota 2 sea mayor que 5

Ejemplo de aplicación 2:

Juan Pérez, supervisor de ventas de la compañía ACME, esta en una reunión con sus jefes quienes le han solicitado que presente el monto diario en dólares, de todas las ventas de la tercera semana de enero del 2008.

SoluciónJuan tiene un cuadro donde se muestran las fechas y las ventas realizadas diariamente en todo el mes de enero

Juan se recordó de la utilidad de la función “Y” de Excel, y siguió los siguientes pasos para realizar la tarea que le han encomendado.

1. Digito la fecha de inicio y fecha final de la semana de interés. Como se muestra

2. Selecciono el rango de datos de las ventas y digito la formula =Y(A2>=$F$4;A2<=$F$5)

3. Luego pego la formula en las celdas seleccionadas

4. Finalmente utilizo un autofiltro para visualizar aquellas filas donde la función “Y” devuelve el valor de “Verdadero”, obteniendo el resultado que se muestra a continuación.

La función “O”

La función “O” evalúa “n” criterios de los cuales si uno o mas resultan ser ciertos da como resultado una cadena de texto con la palabra “Verdadero”. Devuelve “Falso”, si ninguno de los “n” criterios se cumple.

La sintaxis de la función en Excel es:O(valor_logico1, [valor_logico2], …)

Donde:Valor_logico”n”: son las n condiciones a evaluar

Ejemplo de aplicación 3: A los jefes de Juan Pérez le han informado que la

primera semana de Febrero, los productos menos vendidos de su compañía han sido los “B y D”, pero también sospechan que existen otros productos que no se han vendido como se esperaba. Le han pedido a Juan Pérez que les muestre el monto diario de ventas en dólares de los productos B y D de la primera semana de Febrero del 2008. Adicionalmente le solicitaron mostrar todos aquellos productos con ventas menores a $500.oo

SoluciónJuan tiene un cuadro donde se muestran las fechas, el producto y las ventas realizadas diariamente en la primera semana del mes de Febrero, como se ve en la figura

• Juan utilizo la función “O” de Excel, y siguió los siguientes pasos para realizar la tarea que le habían encomendado

1. Digito los parámetros B, D y $500, en el rango mostrado

2. Selecciono el rango D2:E22 y digito la siguiente formula: =O(B2=$F$2;B2=$F$3;C2<$G$2)

3. Presiono la combinación de teclas <Ctrl+Enter> para pegar la formula en todas las otras celdas.

4. Finalmente utilizo un autofiltro para visualizar solo las filas donde “O” devuelve verdadero

La Función “SI” La función “SI” devuelve un valor si

la condición especificada es VERDADERA y otro valor si dicho argumento es FALSO.

La sintaxis de la función “SI” de Excel es:SI(prueba_logica;[ valor_si_verdadero];[ valor_si_falso])

Donde:

Prueba_logica

• Es cualquier valor o expresión que pueda evaluarse como VERDADERO O FALSO

• Por ejemplo A10=100 es una expresión lógica; si el valor de la celda A10 es igual a 100 la expresión da como resultado VERDADERO. De lo contrario se evaluara como FALSO. Este argumento puede utilizar cualquier formula o valor numérico

Valor_si_verdadero

• Es el valor que devuelve si la prueba lógica resulta VERDADERO

• Por ejemplo si este argumento es la cadena de texto “Dentro de Presupuesto” y el argumento prueba lógica se evalúa como VERDADERO, la función “SI” muestra el texto “Dentro de Presupuesto” y si este esta en blanco devuelve 0 (cero). Este argumento también puede ser una formula o un valor numérico

Valor_si_falso

• Es el valor que devuelve si la prueba lógica resulta FALSO

• Por ejemplo si este argumento es la cadena de texto “Presupuesto excedido” y el argumento prueba lógica se evalúa como FALSO, la función “SI” muestra el texto “Presupuesto EXCEDIDO” y si este esta en blanco devuelve 0 (cero). Este argumento también puede ser una formula o un valor numérico

De acuerdo a lo anterior, dentro de esta función lógica pueden combinarse múltiples formulas y funciones con las cuales cuenta adicionalmente Excel.

De nuevo para utilizar hay dos caminos, o recurrir al botón fx de la barra de formulas o digitarla directamente en la celda.

En todo caso ya sea de una o de la otra forma hay que estar atentos a los argumentos que de acuerdo a la condición a evaluar, ejecutara la función “SI”.

Ejemplo de aplicación 4:

Se tienen las calificaciones de 4 personas y se desea catalogarlas como “Aprobado” en caso que el promedio de las 2 notas sea mayor a 6, caso contrario se catalogara como “Reprobado”

Solución:1. Se selecciona el rango C2:C5 y se digita la

siguiente formula: =SI(PROMEDIO(A2:B2)>6; “Aprobado”;”Reprobado”)

2. Presionar la combinación de teclas<ctrl+Enter>

3. Nótese que el resultado de la formula es “Aprobado” en todas aquellas notas cuyo promedio es superior a 6. Caso contrario el resultado es “Reprobado”. También puede observarse que en el argumento Prueba_logica se ha utilizado la formula llamada “PRIMEDIO” con la que cuenta Excel, y que las cadenas de texto siempre se encierran entre comillas.

Funciones derivadas de la función “SI”

A continuación se estudiaran brevemente 3 funciones derivadas de la función “SI”. Estas funciones son de utilidad cuando seleccionando un rango se quiere realizar una operación (suma, multiplicación, conteo, promedio, etc.) solo con aquellas celdas del rango que cumplen un criterio en particular.

FunciónSUMAR.SI

CONTAR.SI

PROMEDIO.SI

Función “SUMAR.SI” Suma los valores de las celdas de un rango que coinciden en

un único criterio. La sintaxis de esta función es:

SUMAR.SI(rango;criterio;[rango_suma]) Donde:

rango   es el rango de celdas que se desea evaluar según los criterios especificados. Las celdas de cada rango deben ser números, o bien nombres, matrices o referencias que contengan números. Los valores en blanco y los de texto no se tienen en cuenta.criterio   es el criterio en forma de número, expresión o texto, que determina las celdas que se van a sumar. Por ejemplo, los criterios pueden expresarse como 32, "32" ">32" o "manzanas".rango_suma   son las celdas reales que se deben sumar si las correspondientes celdas incluidas en el rango cumplen los criterios. Si se omite el argumento rango_suma, las celdas del rango se evaluarán según los criterios y se sumarán si cumplen los criterios.

Ejemplo de aplicación 5: Se tienen las propinas de un día de

trabajo obtenidas por los meseros de un mini restaurante. Se desea sumar todas aquellas propinas de los meseros que pertenezcan al equipo “A”.

Solución:En una celda determinada se digita la formula: =SUMAR.SI(B2:B8;”A”;C2:C8) , lo cual se puede observar en la siguiente hoja.

Recordar que el criterio se introduce entre comillas, es decir como cadena de texto. La función “PROMEDIO.SI” es similar a la función “SUMAR.SI”, en lugar de sumar las celdas que cumplen el criterio, realiza el promedio de los valores de dichas celdas.

La función “CONTAR.SI” Cuenta el numero de celdas en un rango que

coinciden en un único criterio La sintaxis de esta función es:

CONTAR.SI(rango;criterio) Donde:

Rango   es la celda o las celdas que se van a contar; deben contener números, o nombres, matrices o referencias que contengan números. Los valores en blanco y los de texto no se tienen en cuenta.Criterio   es el criterio en forma de número, expresión, referencia a celda o texto, que determina las celdas que se van a contar. Por ejemplo, los criterios pueden expresarse como 32, "32", ">32", "manzanas" o B4.

Ejemplo de aplicación 6: Con la misma información del

ejemplo anterior se desea contar los miembros del equipo “A”

Solución:En la celda B10 se digita la formula =CONTAR.SI(B2:B8;”A”)

El resultado se puede ver en la siguiente figura

Formato Condicional En muchas ocasiones se presentara la situación de

tener muchísimos datos en una hoja de calculo y el análisis de esta hoja por si misma no será prioridad.

Mas bien importara responder preguntas simples como ¿Cuáles son los 10 valores mayores?, ¿Qué valores son menores que cero? O ¿Cuáles valores están por encima del promedio y cuales por debajo de este?

A veces es muy difícil con la sola observación de los datos numéricos responder estas preguntas

Excel mediante la opción formato condicional permite de forma visual responder a dichas preguntas

Es un formato especial que se aplica solo a aquellas celdas que cumplen una condición.

Los tópicos que se verán son:

FORMATO CONDICIONAL

RESALTAR REGLAS DE CELDAS

CREACION DE REGLAS SUPERIORES E INFERIORES

AÑADIENDO BARRAS DE DATOS

ESCALAS DE COLOR

CONJUNTO DE ICONOS

Resaltar reglas de celda significa aplicar un formato a la celda que reúne criterios específicos.

Se ejecuta inicio>Formato condicional>Resaltar reglas de celda y se visualiza lo siguiente:

Las 7 opciones que se visualizan son:

RESALTAR REGLAS DE CADENA

DESCRIPCION

Mayor que Aplica formato a las celdas con mayor valor que el especificado. Ej.Aplicar formato color azul a un rango

de celdas >10

Menor que Aplica formato a las celdas con menor valor que el especificado. Ej.Aplicar formato color rojo a un rango

de celdas <6

Entre Aplicar formato a celdas cuyos valores están entre dos valores especificados. Ej. Aplicar formato color naranja a las

celdas de un rango que sean mayores a 100 y menores a 200.

Igual a Aplica formato a celdas cuyos valores son iguales al valor especificados. Ej. Aplicar formato color azul a las celdas de un

rango que sean iguales a 3º.

Texto que contiene

Aplica formato a celdas con valor de texto que contiene el texto que se especifica. Ej. Aplicar formato color rojo a las

celdas de un rango que tenga la palabra “Reprobado”

Una fecha Aplica formato a celdas cuyos valores de fechas satisfagan la condición seleccionada. Ayer, hoy, mañana, en los últimos

días, semana siguiente, etc.

Valores duplicados

Para aplicar formato a celdas con valores que aparecen mas de una vez en el rango. O caso contrario aplicar formato a

celdas con valores únicos.

Creación de Reglas superiores e inferiores

Estas reglas aplican formato a las celdas que se ubican en los valores superiores o inferiores en un rango.

Para crear este tipo de regla superior o inferior se ejecuta:Inicio>Formato condicional>Reglas superiores e inferiores

Se visualiza el siguiente menú

Las opciones de las reglas superior e inferior son:

REGLAS SUPERIOR E INFERIOR

DESCRIPCION

10 Mejores Aplica formato a aquellas celdas con valores que se ubican en los x mejores datos en el rango en donde x es el numero de

datos que se quieren visualizar

10% Mejores Aplica formato a aquellas celdas con valores que se ubican en los x mejores porcentajes de datos en el rango, en donde x

es el porcentaje que se quiere visualizar

10 Inferiores Aplica formato a aquellas celdas con valores que se ubican en los x peores datos en el rango en donde x es el numero de

datos que se quieren visualizar

10% peores Aplica formato a aquellas celdas que se ubican en los inferiores x datos en el rango, en donde x es el numero de

datos que se quiere visualizar.

Por encima del promedio

Para aplicar formato a aquellas celdas con valores que se encuentran por encima del promedio de todos los valores en

un rango.

Por debajo del promedio

Para aplicar formato a aquellas celdas con valores que se encuentran por debajo del promedio de todos los valores de

un rango

Valores duplicados

Para aplicar formato a celdas con valores que aparecen mas de una vez en el rango. O caso contrario aplicar formato a

celdas con valores únicos.

Ejemplo de aplicación 7: La empresa “Música es Arte” se dedica al

comercio de instrumentos musicales. A continuación se presenta una tabla resumen con las ventas realizadas en el 2007 y 2008. Así también las variaciones porcentuales de ventas del año 2008 respecto al año 2007.

Se desea visualizar aquellos productos que presentan disminuciones de ventas con relación a las ventas del 2007

• Se ve en la tabla la variación de ventas en la columna D

• Por lo que se selecciona el rango D2:D14 para aplicar sobre el las diferentes opciones de formato.

• De la siguiente manera:

En el menú correspondiente: Inicio>formato condicional>resaltar reglas de celdas>es menor que

Es menor que: en el caso del ejemplo menor que 0

Si por algún motivo, de presentación, de estética o de lo que sea, se desea cambiar el formato de la regla, se debe desplegar la lista de opciones de formato, y elegir “formato personalizado…” como se ve en la figura

De la misma tabla se desean visualizar los 5 productos que presentan los mayores ingresos en el año 2008, que se hace?

Añadiendo Barras de Datos Muchas veces el interés ante una situación radica en

visualizar la relación entre valore similares en una hoja de calculo.

En estos casos, esta visualización es brindada por las barras de datos que posee Excel como un formato condicional especial.

Este formato añade barras horizontales que aparecen detrás de los valores en el rango seleccionado.

La característica principal de este tipo de formato es que la longitud de la barra de datos que aparece en cada celda depende del valor de esa celda, cuanto mas grande es el valor, mas grande es la barra de datos.

Solo hay que ejecutar: Inicio > Formato Condicional > Barras de Datos y luego seleccionar el color deseado.

Ejemplo de aplicación 8: Se tienen las notas finales de los alumnos de

Manejo las cuales van de 0 a 10. Están en una hoja de calculo de Excel. ¿Cómo podría comparar cada nota en relación a la de todos los alumnos?

SoluciónSe podría crear una nueva columna que calcula el porcentaje de las notas por cada alumno relativo a la calificación mas alta. Esto podría funcionar, pero lo que se estaría haciendo es añadir mas números a la hoja de calculo, lo cual no hace las cosas mas claras.Una forma optima de visualizar los valores relativos consiste en utilizar el formato condicional “barras de datos”.Se puede ver en la siguiente imagen:

Visualmente se percibe que alumnos tienen mejores calificaciones.

Se pueden visualizar mas reglas en cuanto a esta forma de barras de datos

Escala de Color Este tipo de formato condicional permite visualizar la

distribución total de los valores en una tabla u hoja de calculo.

Una escala de color es similar a una barra de datos en la comparación que hace de los valores relativos de las celdas en un rango.

En lugar de barras cada celda se vera sombreada, en donde el color de fondo es un reflejo del valor de la celda.

La distribución de colores en el rango da una inmediata visualización de la distribución de los valores de las celdas.

Para aplicar este formato: Inicio > Formato condicional > Escala de color y luego seleccionar el color

Conjunto de Iconos Este tipo de formato condicional puede utilizarse al igual que las barras de datos y las escalas de colores para visualizar valores relativos en un rango.

En este caso Excel añade un icono particular a cada celda en el rango, y este representara algo acerca del valor de la celda respecto al resto del rango.

Hay que ejecutar:Inicio > Formato condicional > Conjunto de iconos

Luego seleccionar el grupo de iconos que mas guste

Aplicando este formato a los datos de la empresa de música:

Editando reglas de formato condicional Cuando se aplican dos o mas reglas de formato condicional a un rango de celdas, estas reglas se evalúan por orden de prioridad en función de este cuadro de dialogo:

Pasos para editar la regla de formato condicional

1. Seleccionar una regla. Solo se puede seleccionar una regla a la vez.

2. Para subir la regla seleccionada en prioridad, hacer clic en Subir. Para bajar la regla seleccionada en prioridad, hacer clic en Bajar

3. Opcionalmente, para detener la evaluación de la regla en una regla especifica, se debe activar la casilla de verificación Detener si es verdad . Adicionalmente esta opción se debe activar para que no exista problemas de compatibilidad con versiones anteriores a Excel 2007 las cuales no admiten varias reglas de formato condicionales par un rango de celdas.

Utilizar “Análisis Y Si” Este método es el mas básico para analizar los

posibles resultados de un modelo a partir de la creación de diversos escenarios en la hoja de calculo.

Se puede calcular una formula basada en entradas de variables y luego evaluar el nuevo resultado como consecuencia de los cambios en las variables de la formula.

A continuación se estudiara 1 de los 2 métodos de Excel para análisis Que sucede si : Buscar objetivo y creación de escenarios

Análisis Y SiBuscar Objetivo

Administrador de escenarios

Buscar Objetivo Es una herramienta de Excel muy útil cuando se

sabe de antemano el resultado deseado de una formula y se sabe también que para esto se debe o desea cambiar solo una de sus variables.

De esta forma Buscar Objetivo solicita el resultado deseado y la variable que se desea cambiar, finalmente este encuentra la solución, si existe. Esto se logra mediante un método iterativo.

Antes de ejecutar Buscar Objetivo, se deben identificar en la hoja algunos siguientes parámetros que se detallaran en el siguiente ejemplo.

Ejemplo de aplicación 9:

Se esta evaluando de una forma muy sencilla la capacidad necesaria que hoy en día se requiere en un centro telefónico de cobranza de créditos a personas. La capacidad instalada actualmente es de 3 gestores, quienes atienden vía telefónica 90 clientes al día para cobrar su crédito.

Debido al crecimiento en el numero de clientes que hoy en día se tienen, se vuelve necesario que cada gestor cubra un listado de 150 clientes al día. Tanto el tiempo de llamada, la intensidad como las horas laborales se desean mantener fijos. Lo mas factible es cambiar el numero de gestores de cobro.

Solución:La interrogante seria:¿Cuántos gestores mas se deben contratar para cobrar a 150 clientes de forma diaria?Los parámetros para Buscar Objetivo serían:

Celda de cambio: es la celda B8, puesto que el numero de gestores es la variable que se desea cambiar.

La celda de formula: es B9, puesto que el objetivo es que el numero de clientes al día sea 150. Esta celda esta relacionada con las celdas que contienen los parámetros de tiempo, intensidad, horas laborales y la variable a cambiar: Gestores, tal como se ve en la barra de formulas.

Entonces ubicados en B9 seleccionar:Datos > Análisis Y si > Buscar Objetivo

Se vera el cuadro de dialogo Buscar Objetivo, el cual tiene 3 argumentos como se muestra.Después se debe hacer clic en Aceptar. Excel empezara la búsqueda y

mostrara el cuadro de dialogo Estado de buscar objetivo. Cuando ha terminado, el cuadro de dialogo informa si Buscar Objetivo ha

encontrado una solución, en este caso 5 gestores como se puede ver.