Material de Estudio Excel para Expertos.pdf

conocimiento para crecer

grupo

Programa Excel para Expertos

MS Excel 2010 Página: 1

Cuando filtramos una tabla, Microsoft Excel oculta de la vista todos aquellos registros

(filas) de la lista que no cumplen con la condición establecida. De esta forma, sólo

quedan visibles aquellos registros que hayamos elegido.

Contamos con la siguiente lista (Libros de Computación e Informática) y deseamos

filtrar la lista de manera tal que sólo muestre aquellos datos del campo Tipo de Salida:

Realizar lo siguiente:

• Haga clic en una celda de la lista.

• En la ficha Datos, seleccione el botón Filtro. Observe que al lado derecho de los

nombres de cada campo aparece un botón de lista desplegable llamado Flecha

Autofiltro:

• Haga clic sobre el botón de flecha del campo requerido (para nuestro ejemplo,

seleccione Tipo de Salida):

TEMATEMATEMATEMA No. No. No. No. 1111 –––– MS EXCEL MS EXCEL MS EXCEL MS EXCEL 2010201020102010

– Listas y Filtros I –

1


grupo



• Si escogemos la opción Venta el resultado será:

Nota: Para quitar el filtro de la columna Tipo de Salida haga clic en la flecha de filtro y

elija “Seleccionar todo”.

Los filtros se pueden combinar, por ejemplo si queremos mostrar las ventas realizadas

el día 5 de Julio, entonces:

• Seleccione el campo Tipo de Salida y elija Venta, luego seleccione en el campo

Fecha el día 5 de Julio:

Filtros Personalizados:

Por ejemplo, deseamos conocer y mostrar los precios de libros mayores a 50, para ello

procedemos de la siguiente forma:

2


grupo



• Haga clic en una celda de la lista.

• En la ficha Datos, seleccione el botón Filtro. Observe que al lado derecho de los

nombres de cada campo aparece un botón de lista desplegable llamado Flecha

Autofiltro.

• Haga clic en el botón de flecha del campo Precio y seleccione Filtro de número y

luego “Filtro Personalizado“:

• El resultado es el siguiente:

Nota: Para salir del modo Filtrar, seleccione de Datos la opción Borrar: Filtros Múltiples:

Existen tres formas básicas en las que podemos realizar Filtros Múltiples:

1º. Condición Y: Por ejemplo, deseamos conocer las salidas realizadas entre los días

03 y 05 de Julio:

3


grupo



El resultado será:

2º. Condición O: Por ejemplo, deseamos conocer los libros que se han dado como

Muestra o Donación:

El resultado será:

3º. Caracteres Comodín (*) – (?): Por ejemplo, deseamos filtrar la lista de modo

que sólo muestre los datos de los Clientes cuyo nombre empiece con la letra “R”.

4


grupo



El resultado será:

Filtros Avanzados: A diferencia del comando Autofiltro, el comando Filtro Avanzado requiere que los

criterios de filtrado sean especificados dentro de un rango separado de la lista. Un rango

de criterios debe constar al menos de dos filas. En la primera fila se introduce uno o más

encabezamientos de columnas y en las filas restantes los criterios de filtrado.

Por ejemplo, deseamos conocer todos los libros que han sido dados como Donación, para

ello:

• Copie los encabezados en la celda A24.

• Coloque en la celda F25 Tipo de Salida “Donación”.

• Copie los encabezados en la celda A28.

Hasta este momento la hoja debe verse así:

• Seleccione cualquier celda de la tabla.

5


grupo



• En la ficha Datos seleccione el botón Avanzadas, en el siguiente cuadro de dialogo

realice lo siguiente:

o Seleccione Copiar a otro lugar.

o Rango de la lista: $A$1:$H$21

o Rango de criterios: $A$24:$H$25

o Copiar a: $A$28:$H$28


Condiciones O: Se coloca el nuevo criterio debajo del anterior, por ejemplo:

6


grupo




Condiciones Y: Se debe agregar columnas. Por ejemplo al filtro anterior le agregaremos

la condición que sea en una fecha determinada, en este caso el 3 de Julio:


7


grupo



1. Ingrese los datos como se muestra en la figura siguiente:

�� Importante: Respete la posición de las celdas, observe y cree la línea de división

(Ficha Inicio/Fuente/Borde inferior).

2. Seleccione las columnas B y C y ocúltelas. El resultado debe ser el siguiente:

3. Realice la siguiente ordenación teniendo como criterio varios campos, para ello

utilice de la ficha Datos la opción Ordenar:


– Listas y Filtros II –

8


grupo



4. Muestre las columnas B y C que anteriormente fueron ocultadas, para ello

seleccione una columna en cada lado de las columnas ocultas (seleccione A y D).

5. Observe los resultados del ordenamiento.

�� Importante: Cuando ordene, siempre el primer criterio será el que Excel elegirá primero,

después, el segundo criterio y, por último, el tercero. Los criterios pueden establecerse

eligiendo cualquiera de los campos de la lista, y el orden puede ser ascendente o

descendente.

6. Agregue ahora 3 registros al final de la Lista:

APELLIDO

PATERNO

APELLIDO

MATERNO

NOMBRES DIRECCION DNI PROVINCIA

Cárdenas Arce Gustavo Alex P. Viejo 312 Dpto. 2 29857413 Arequipa

Gómez Sandoval Ana Karina Manuel Ballón 205 28546089 Lima

Llerena Castro Amancia Villa América Dpto. 8 29638112 Cajamarca

7. Ahora vamos a crear Autofiltros, para ello seleccione la ficha Datos/Filtro, luego

elija de la columna PROVINCIA la opción Arequipa y observe los resultados:

8. Para crear Filtros Avanzados siga estos pasos:

• Copie el rango de cabecera o título por debajo de la propia lista o base.

• En las celdas de la siguiente fila a la copiada de títulos se le irán indicando las

condiciones de búsqueda o criterios.

9


grupo



• Se copiará de nuevo el rango de cabecera o títulos por debajo de la anterior

copiada y será bajo esta cabecera donde aparecerán los datos filtrados.

9. Seleccione de la ficha Datos la opción Avanzadas y seleccione los rangos que

aparecen en la imagen:

10. Observe en el resultado que aparecen todos los registros cuya PROVINCIA sea

diferente a Lima.

11. Seleccione la celda F24 y ordene la columna en forma Ascendente utilizando el

botón de la ficha Datos.

10


grupo



12. Seleccione de la ficha Datos la opción Subtotal y realice lo siguiente:

13. El resultado será el siguiente:

14. Seleccione el rango E4:E16 (DNI), vaya a la ficha Datos y elija la opción

Validación de datos.

� En la pestaña Configuración coloque lo siguiente:

11


grupo



� En la pestaña Mensaje de entrada coloque lo siguiente:

� En la pestaña Mensaje de error coloque lo siguiente:

15. Observe los resultados de sus validaciones equivocándose a propósito:

16. Guarde su hoja de cálculo dentro de su carpeta de trabajo.

17. En el mismo Libro de trabajo, en la Hoja2 ingrese los datos como en la figura:

12


grupo



�� Nota: Para la columna de Apellidos y Nombres haga lo siguiente:

- En la Hoja1 en la celda H4 coloque la siguiente función y luego arrastre el

resultado hasta la celda H16: =CONCATENAR(A4," ",B4,","," ",C4)

- Copie luego el rango H4:H16

- Vaya a la Hoja2 y pegue el rango en la celda B4.

18. Luego calcule el Promedio con 2 decimales y la Nota Final redondeada a 0

decimales.

19. A partir de las columnas APELLIDOS Y NOMBRES y N.FINAL realice el siguiente

grafico en una hoja nueva, póngale de nombre NOTAS_FINALES:

13


grupo



20. Se va a mostrar en una nueva tabla, la información correspondiente a alumnos

Repitentes, sin distinción de Sexo ni de Distrito ni de otra clase. Para especificar

este criterio de filtrado realice lo siguiente:

� Ubique el cursor en la celda J4 y escriba en ella SITUACION (rótulo de la

columna que contiene los datos de alumnos nuevos y Repitentes).

� En la celda J5 escriba Repitente (situación que interesa filtrar).

21. Haga clic en cualquier celda no vacía de la columna SITUACION. Vaya a la ficha

Datos y seleccione la opción Avanzadas...y seleccione los rangos como se

muestra en la figura:

� Haga clic en Aceptar y observe el resultado: se muestran solamente los datos de

los alumnos Repitentes, en una nueva tabla con su gráfico.

� Copie esta nueva tabla en la Hoja3 y su gráfico en la Hoja4 del Libro de Excel

abierto y ponga a esta hoja el nombre REPITENTES. Regresar a la Hoja2.

14


grupo



� Para volver a la visualización completa de los datos originales seleccione la

opción Borrar de la ficha Datos.

22. Para concluir realice los siguientes ejercicios:

�� Vuelva a la lista completa original. Aplique un filtro avanzado que permita

mostrar únicamente los datos de Alumnas, sin mover la lista a otro lugar. No

olvide especificar el criterio del filtro en una parte despejada de la hoja de datos.

�� Vuelva a la lista completa original. Aplique un filtro avanzado que muestre la

información de todos los alumnos(as) que viven en el Distrito de J.L.B.y R., sin

mover la lista a otro lugar.

�� Vuelva a la lista completa original y aplique 2 criterios de filtro simultáneamente:

Repitentes del Distrito de Cerro Colorado.

Importante: Hacer que las especificaciones de estos 2 criterios de filtro queden

en columnas adyacentes de una parte despejada de la tabla. Al indicar el Rango

de criterios anotar las referencias absolutas a las celdas del vértice izquierdo

superior y derecho inferior de dicho rango.

�� Vuelva a la lista completa original. Filtrar la información de los alumnos(as) que

vivan en el Distrito del Cercado o Cayma.

Importante: Para ello se debe especificar el criterio anotando Cercado y

Cayma en celdas contiguas de la columna DISTRITO (una bajo la otra), para

que el filtro opere con la conectiva O.

�� Vuelva a la lista completa original. Filtre la información de Promedios entre 10

y 13.

Importante: Para eso es necesario especificar el criterio poniendo los datos en

dos columnas contiguas, usando los símbolos > (mayor que) y < (menor que):

PROM. PROM.

>10 <13

15


grupo



Ejercicio 1: Universidad

1. Empezar un nuevo libro de trabajo.

La Universidad necesita saber los gastos cuatrimestrales previstos para cada

proyecto de los distintos departamentos y dirigidos por los directores de cada

departamento.

2. Crear el modelo de datos que vemos en la imagen (los datos en negrita son

fórmulas).

3. Realizar un Esquema Automático. Para ello:

a) Estar situado en la hoja donde tenemos los datos.

b) Ir a la pestaña Datos.

c) Elegir la opción Agrupar.

d) Seleccionar la opción Autoesquema.

4. Guardar el libro en su carpeta de trabajo del disco duro, con el nombre de

Universidad.

5. Cerrar el libro.


– Esquemas y Vistas –

16


grupo



Ejercicio 2: Coches

1. Empezar un nuevo libro de trabajo.

Un concesionario quiere estudiar las ventas realizadas durante el año y las clasifica

por la categoría del vehículo y por ventas trimestrales

2. Crear el modelo de datos que vemos en la imagen (los datos en negrita son

fórmulas).

3. Realiza un Esquema Manual. Para ello:

•••• Estar situado en la hoja donde tenemos los datos.

•••• Seleccionar las filas 5-7 y presionar Alt+Shift+Flecha derecha.



•••• Seleccionar las columnas B-D y presionar Alt+Shift+Flecha derecha.

•••• Seleccionar las columnas F-H y presionar Alt+Shift+Flecha derecha.

6. Guardar el libro en su carpeta de trabajo del disco duro, con el nombre de Coches.

4. Cerrar el libro.

5. Al concluir la práctica avise a su instructor y luego salga del Windows realizando los

pasos recomendados. Finalmente apague su equipo (CPU y monitor).

17


grupo



Con la función Consolidar de la ficha Datos puedes:

• Reunir información de hasta 255 hojas de cálculo.

• Vincular los datos consolidados a los datos fuente, de tal forma que una vez

modificados estos últimos, se alteren los resultados de la consolidación.

• Consolidar por posición o por categoría.

• Consolidar hojas de cálculo utilizando funciones como Promedio, Máx, Mín,

Producto, Cuenta, Desvest, etc.

• Consolidar hojas de cálculo en libros abiertos o almacenados en disco.

Los datos pueden consolidarse de varias maneras:

Por posición: recopila información de las celdas de la misma posición situadas en las

hojas que quieres consolidar.

Por categorías: resume un conjunto de hojas de cálculo que tienen los mismos rótulos

pero organiza los datos de forma diferente.

Por creación de una tabla dinámica: es similar al método de consolidación por

categorías, pero ofrece una mayor flexibilidad para reorganizar dichas categorías.

Consolidar datos por posición:

1. Selecciona el área destino.

2. En la ficha Datos, clic en Consolidar.

.

3. En el cuadro Función, clic en la función de resumen que deseas que utilice

Microsoft Excel para consolidar los datos

4. En el cuadro Referencia, introduce el área origen que deseas consolidar.

5. Clic en Agregar.

6. Repite los pasos 4 y 5 en cada área de origen que desees consolidar.


– Consolidar Datos –

18


grupo



7. Para actualizar automáticamente la tabla de consolidación cuando cambien los

datos de origen, selecciona el cuadro de verificación Crear vínculos con los

datos de origen.

Para crear vínculos, el área de origen y el área de destino deben estar en diferentes

hojas de cálculo. Una vez creados los vínculos, no pueden agregarse nuevas áreas de

origen ni cambiarse las que se han incluido en la consolidación.

Nota Si haces la consolidación por posición, Microsoft Excel no copiará los rótulos de

categoría de las áreas de origen en el área de destino. Si deseas rótulos en la hoja de

cálculo de destino, cópialos y pégalos.

Consolidar datos por categoría:

1. Selecciona el área destino.

2. En la ficha Datos, clic en la opción Consolidar.

3. En el cuadro Función, clic en la función de resumen que deseas que utilice

Microsoft Excel para consolidar los datos.

4. En el cuadro Referencia, introduce el área de origen que deseas consolidar.

Asegúrate de incluir en la selección los rótulos de datos.

5. Clic en Agregar.

6. Repite los pasos 4 y 5 en cada área de origen que desees consolidar.

7. En Usar rótulos en selecciona las casillas de verificación que indican dónde están

localizados los rótulos en el área de origen: indistintamente, la fila superior, la

columna izquierda o ambas.

8. Para actualizar automáticamente la tabla de consolidación cuando cambian los

datos de origen, selecciona el cuadro de verificación Crear vínculos con los

datos de origen.

Nota Cuando consolides datos, los rótulos de un área de origen que no coincidan con

ningún rótulo en otra área de origen estarán en filas o en columnas independientes.

19


grupo



Supongamos que tenemos en una hoja los datos de ventas por productos, zonas y mes

del año.

La opción Subtotales nos permite agrupar los totales, por ejemplo, por producto. Para

poder utilizar esta herramienta, debemos ordenar los datos del campo correspondiente

en orden ascendente o descendente.

Una vez hecho esto, abrimos el diálogo de Subtotales:

Elegimos "Producto", "Suma" y "Ventas" en las opciones correspondientes y pulsamos

"Aceptar" Los botones nos permiten presentar el informe en distintos grados de

resumen. Si pulsamos el botón 2 veremos solamente las líneas de totales por producto.


– Consolidar datos con Subtotales –

20


grupo



En este ejemplo vemos que tenemos 3 grados de resumen. Qué pasa si queremos

agregar un cuarto grado, por ejemplo, subtotales por área.

Para hacer esto tenemos que asegurarnos que todos los campos relevantes estén

ordenados en forma ascendente o descendente.

Para reordenar los campos primero debemos cancelar los subtotales. Esto se hace en el

mismo diálogo

Luego abrimos el menú de Ordenar:

y pulsamos "aceptar". Ahora producimos los subtotales para productos, como en el

primer paso, y luego volvemos a seleccionar la opción para producir subtotales por área.

21


grupo



Pero esta vez quitamos la marca de la opción "reemplazar subtotales actuales"

Ahora veremos 4 niveles de resumen

22


grupo



La Auditoria en Excel no tiene nada que ver con el concepto económico de auditoría,

aunque, remotamente, su origen pueda ser similar. Partimos de una hoja de cálculo ya

creada y en la que existen multitud de celdas que realizan operaciones matemáticas

entre sí, se relacionan, se vinculan, etc. llega un momento en que la telaraña puede

tener un tamaño considerable, hasta el extremo de no saber de que datos procede el

resultado de una determinada fórmula.

Mediante estas auditorías la hoja de cálculo nos mostrará, de una forma sencilla y muy

gráfica, como se relacionan las distintas celdas entre sí, como son recalculadas y como

afecta la modificación de un dato en otras celdas. También podremos en algunos casos

rastrear el origen de un error.

Veamos un ejemplo práctico. Partimos de esta hoja de cálculo:

En ella tenemos una columna de cifras (de la C4 a la C8) que son sumadas en la celda C

10. El resultado de esta suma es dividido en la celda E10 por cinco. En la hoja de cálculo

no veremos, como en nuestro ejemplo, la fórmula en las casillas, sino sólo el resultado

de la operación.

Gracias a la auditoría podremos ver de forma gráfica de donde son obtenidos los

resultados, o como afecta un determinado dato en la hoja de cálculo. Vayamos por

partes.

En la ficha Fórmulas, en el grupo Auditoría de fórmulas, se observa cada una de las

diferentes opciones:


– Auditoría de Formulas –

23


grupo



Estudiemos ahora cada una de ellas:

• Rastrear precedentes: Situado el cursor sobre una celda que contenga una

fórmula (en nuestro ejemplo, C10, que contiene la suma de la columna que tiene

sobre ella), si seleccionamos esta opción de menú en pantalla nos aparecerá estos

extraños dibujos:

Surge '"de la nada" una flecha que apunta hacia la celda donde nos hemos situado. En

este ejemplo es bastante evidente de donde proceden los datos de la fórmula, pero en

otros casos la situación puede ser mucho más compleja, y esta operación, de gran

utilidad.

Si hacemos lo propio en la celda E10 comprobaremos que ese resultado se deriva, a su

vez, de la celda C10. Cada vez que pulsemos el menú la auditoria se retrocederá un paso

en las operaciones previas.

Como podemos ver, rastrear precedentes muestra de donde proceden los datos que

operan en la fórmula seleccionada. Esta opción de menú, por tanto, sólo será operativa

en celdas que contengan fórmulas, o como mínimo, referencias a otras celdas.

• Rastrear dependientes: La segunda opción de auditoria es exactamente lo

contrario: situados sobre una celda que contenga un valor numérico el ordenador

nos muestra a qué otras celdas afecta su existencia.

En nuestro ejemplo, si nos situamos sobre la celda C4, que contiene sola y

exclusivamente un número, y seleccionamos esta opción de menú, nuestra hoja de

cálculo pasará a tener este aspecto:

24


grupo



Sin mover el cursor de la celda C4, si volvemos a efectuar la operación de rastrear

dependientes, aparecerá otra flecha más, que ahora apuntará a la celda E10.

Esto es por que ese dato, además de ser sumado en la celda C10 afecta en cierto modo a

la E10, donde se divide el resultado de la suma por 5, de tal manera que si variásemos el

contenido de C4 variarían C10 y E10.

• Rastrear errores: La tercera opción del submenú Auditoría es esta. Nos permite, en

determinadas ocasiones verificar de que celda proviene el error que impide el cálculo

adecuado de las diferentes celdas. En este caso la procedencia del error quedará

señalada por una flecha de color rojo.

Vamos a realizar unas pequeñas modificaciones en nuestro ejemplo: en la celda E10

sustituiremos la división que había antes esta:

=C10/0

Un número dividido por cero es igual a infinito, lo cual para Excel es incalculable. En la

celda E12 escribiremos

=E10+2

En este caso los mensajes de error provocados son muy evidentes, pero en otras

ocasiones con las que nos enfrentaremos cuando trabajemos con Excel no lo serán tanto.

Situados sobre la celda E12 seleccionamos la opción Rastrear Error dentro de la

Auditoría. La apariencia de la pantalla será esta:

25


grupo



• Quitar flechas. A medida que ejecutamos las diferentes opciones de la auditoría la

pantalla se va cubriendo de flechas. Aquí se nos ofrece la posibilidad de limpiarla

para poder seguir trabajando con comodidad.

26


grupo



Base de datos

Una base de datos es una herramienta que permite almacenar, organizar y manejar un

grupo de datos. Microsoft Excel puede utilizarse fácilmente como una base de datos,

permitiendo realizar tareas como búsquedas, clasificaciones o cálculos parciales.

En Microsoft Excel, una base de datos es una lista de datos relacionados en la que las

filas de información son registros y las columnas de datos son campos. La primera fila de

la lista contiene los rótulos de cada columna. La referencia a la base de datos se puede

introducir como un rango de celdas o como un nombre que represente el rango que

contiene la lista.

Para crear una base de datos en Excel hay que realizar tres pasos:

• Introducir los nombres de los campos en la primera fila de la lista (texto o una

formula que produzca texto)

• Introducir los registros en las líneas situadas debajo de los nombre de los campos

• Asignar un nombre al rango (Opcional)

Ordenar una base de datos

Excel permite la ordenación de un rango de celdas por orden alfabético, numérico o

temporal, de una forma rápida y sencilla, haciendo uso de la opción DATOS ORDENAR. La

ordenación consiste en cambiar los contenidos de unas celdas por el de otras, de forma

que aparezca en las distintas columnas (o filas) ordenados. Para ello, primer lugar, habrá

que seleccionar el rango a ordenar. Este rango puede incluir también otras celdas en la

misma fila (o columna) que las que se vayan a ordenar, conservándose después de la

ordenación la misma posición respecto a las celdas clave de la ordenación. Al pulsar

sobre la opción indicada aparece el cuadro Ordenar.

TEMATEMATEMATEMA No. No. No. No. 7777 –––– MS MS MS MS EXCEL EXCEL EXCEL EXCEL 2010201020102010

– Trabajo con Bases de Datos –

27


grupo



En este cuadro se puede indicar por qué columna (o fila) se quiere realizar la ordenación

en primer lugar (Ordenar por). En caso de que haya varias celdas con el mismo

contenido, es posible ordenar las filas (o columnas) en función de los datos de una

segunda o tercera columna (Luego por). Además, es posible indicar también si la

ordenación será Ascendente (por orden alfabético, de menor a mayor, o de anterior a

más reciente, según la ordenación sea alfabética, numérica o cronológica) o bien

Descendente. En el caso de que el rango tenga una columna (o fila) de títulos, que se

deba ordenar, deberá activarse la opción en Mis datos tienen encabezado. Si la

ordenación no es en sentido vertical, sino horizontal, deberá señalarse desde el cuadro

que aparece al pulsar sobre el botón Opciones, en el campo Orientación.

En este cuadro puede indicarse, también, si la ordenación tendrá en cuenta si las letras

son mayúsculas o no. Si se tiene en cuenta (marcando sobre la casilla Distinguir

mayúsculas y minúsculas), en caso de dos textos iguales, aparecerá primero el que

tenga las letras minúsculas. El campo Criterio de ordenación se utiliza cuando los datos

no obedecen a un orden normal (números, fechas o textos), sino que son textos que

representan los días de la semana o los meses del año.

Funciones de bases de datos Cuando sea necesario analizar si los valores de una base de datos cumplen una condición

determinada, o criterio, pueden utilizarse las funciones de base de datos de Excel. Por

ejemplo, en una base de datos que contenga información acerca de ventas, pueden

contarse todas las filas o registros en las que el importe de las ventas sea mayor que 100

pero menor que 2.500.

Algunas funciones de base de datos y de gestión de listas de la hoja de cálculo tienen

nombres que comienzan por la letra “BD”. Estas funciones, conocidas también como

funcionesBD, tienen tres argumentos (base de datos, campo, criterios). Estos

28


grupo



argumentos se refieren a los rangos de la hoja de cálculo empleados en la función para

base de datos.

Sintaxis:

Bdfunción(base_de_datos;nombre_de_campo;criterios)

• Base_de_datos es el rango de celdas que compone la base de datos. Debe incluirse

la fila que contenga los rótulos de columna en el rango.

• Nombre_de_campo indica el campo que se utiliza en la función.

Nombre_de_campo puede ser texto con el rótulo encerrado entre dobles comillas,

como por ejemplo “Edad” o “Campo”, o un número que represente la posición de la

columna en la lista: 1 para la primera columna, 2 para la segunda y así

sucesivamente.

• Criterios es el rango de celdas que contiene los criterios de la base de datos. Se

puede utilizar cualquier rango en el argumento Criterios mientras éste incluya por lo

menos un rótulo de columna y por lo menos una celda debajo del rótulo de columna

que especifique una condición de columna.

Sugerencias:

• Cualquier rango se puede usar como argumento criterios, siempre que incluya por lo

menos un nombre de campo y por lo menos una celda debajo del nombre de campo

para especificar un valor de comparación de criterios. Por ejemplo, si el rango G1:G2

contiene el encabezado de campo Ingresos en la celda G1 y la cantidad 10.000 en la

celda G2, el rango podría definirse como CoincidirIngresos y ese nombre podría

usarse como argumento criterios en las funciones para bases de datos.

• Aunque el rango de criterios puede ubicarse en cualquier parte de la hoja de cálculo,

no coloques el rango de criterios debajo de la lista. Si agregas más información a la

lista utilizando el comando Formulario en el menú Datos, la nueva información se

agrega a la primera fila debajo de la lista. Si la fila de debajo no está vacía, Microsoft

Excel no podrá agregar la nueva información.

• Asegúrate de que el rango de criterios no se superpone sobre la lista.

• Para realizar una operación en toda una columna de la base de datos, introduce una

línea en blanco debajo de los nombres de campo en el rango de criterios.

Lista de Funciones:

29


grupo



Ejercicio: (Bdsuma, Bdpromedio, Bdproducto, Bdmin, Bdmax)

30


grupo



Las funciones que vamos a ver trabajan de una forma muy parecida a otra función que

quizás ya conozcas: sumar.si. La diferencia es que no sólo podemos hacer sumas, sino

multiplicar, calcular promedios y el número más grande o más pequeño, según

utilicemos una función u otra.

Tienes que tener claro que todas las funciones que vamos a ver se definen exactamente

igual, si aprendes a manejar una de ellas, ya las has aprendido todas. Vamos a verlo en

el siguiente ejemplo:

En esta tabla están representados los tiempos (en minutos, para no complicarlo mucho)

que tardan dos corredores de maratón en completar un recorrido. El más rápido,

evidentemente, será el que tarde menos minutos.

Para calcular las estadísticas de la parte inferior de la hoja (suma, media, mejor tiempo y

peor tiempo) vamos a utilizar las funciones BD:

1. Sitúa el cursor en la celda B10, que es donde se va a calcular la media de tiempo de

Fast Slim.

2. Pulsa el botón del asistente para funciones (fx) y selecciona la función Bdpromedio.

La función Bdpromedio calcula la media de una serie de números.

3. Verás que aparece una nueva ventana con tres casillas:

• En la primera casilla 'Base_de_datos' tenemos que indicar donde está nuestra

tabla. La tabla comienza en la celda A1 y termina en la celda B7 (la tabla donde

31


grupo



están los tiempos de los corredores, que son los que vamos a promediar, por eso

no se indica hasta la fila 13). Así que tienes que escribir A1:B7

• En la segunda casilla 'Nombre_de_campo' tenemos que indicar qué columna se

va a promediar. Nosotros podemos verlo claro, entre otras cosas, porque sólo hay

una columna que se puede promediar (la del tiempo), pero Excel no lo ve tan

fácil, así que escribe en esta casilla B1. ¿Por qué B1? porque es la primera celda

de la columna que queremos calcular).

• Ya le hemos dicho a Excel que queremos calcular la media de la columna B, pero

si no le indicamos nada, calculará la media de toda la columna. Como nosotros

estamos intentando calcular la media del corredor Fast Slim, tenemos que

indicarlo de alguna forma. Aquí es donde entra en juego la tercera casilla. ¿Cómo

le podemos indicar a Excel que queremos calcular sólo la media de Fast Slim?.

Fíjate que en las celdas B8 y B9 aparecen las palabras 'corredor' y 'Fast Slim'.

Esto será suficiente para que Excel comprenda qué es lo que queremos así que

escribe en la tercera casilla B8:B9.

4- Pulsa el botón Aceptar y, si has realizado bien los pasos, debería aparecer la media

de tiempos de Fast Slim.

5- Si es la primera vez que utilizas la función Bdpromedio tienes aproximadamente un

40% de posibilidades de que te haya salido bien el resultado. Si te ha salido mal o te

ha salido un error mira a continuación algunos de los errores más frecuentes que se

cometen:

�� El error más común es el de no escribir los valores correctamente dentro de la

hoja. Por ejemplo: Para Excel no es lo mismo Fast Slim que FastSlim o Fast Slim

(con dos espacios en medio en lugar de uno). De igual forma, las cabeceras de

las columnas deben estar escritas igual en todos sitios, por ejemplo: si en la celda

A1 has escrito la palabra corredor y en la B8 corredores o al contrario.

�� Otro error muy común es el de dar espacios en blanco detrás de una palabra o

frase por ejemplo: Podemos escribir en una celda Aitor Tuga con un espacio en

blanco entre ambas palabras, esto es correcto. Pero no podemos dar un espacio

en blanco al final, es decir, detrás de Tuga, ya que Excel lo tiene en cuenta y

nosotros no vamos a verlo.

�� También puedes obtener errores si no has escrito la tabla que está al principio de

esta hoja exactamente igual en Excel. Ten en cuenta que los valores para las

fórmulas que se han definido aquí están expresados según la tabla de arriba. Si

32


grupo



en lugar de empezar a escribir en la celda A1, lo has hecho en la C4, no

coincidirán las filas ni las columnas.

6. Otros errores pueden ocurrir al teclear los valores en la ventana de la función. Por

ejemplo, si tienes que escribir B8:B9 y, por error, tecleas B8:B8, por ejemplo.

Así que ya sabes, si no te ha salido a la primera, comprueba los cuatro puntos anteriores

y vuelve a intentarlo. La forma de calcular el resto de las funciones es prácticamente

igual. Para calcular la suma de tiempo de Fast Slim:

• Sitúa el cursor en la celda B13, que es donde debe aparecer el resultado.

• Pulsa el botón del asistente para funciones (fx) y busca Bdsuma.

• Verás que aparecen las mismas tres casillas que cuando utilizaste Bdpromedio.

• Rellénalas de la siguiente forma:

Casilla 1: 'Base_de_datos'. A1:B7 (donde está la tabla con los datos)

Casilla 2: 'Nombre_de_campo'. B1 (esto indica a Excel que lo que se van a sumar

son los números de abajo)

Casilla 3: 'Criterios'. B8:B9 (esto indica a Excel que sólo queremos sumar los

tiempos de Fast Slim)

Pulsa el botón Aceptar. Si no obtienes el resultado correcto comprueba la lista de

posibles errores.

Para calcular el mejor tiempo, vamos a utilizar la función Bdmax. Esta función nos

devuelve el número más grande de la lista. Sitúa el cursor en la celda B11 y busca en el

asistente de funciones Bdmax. Sigue los mismos pasos que realizaste para las funciones

Bdsuma y Bdpromedio.

Para calcular el peor tiempo, utiliza la función Bdmin de la misma forma que utilizaste

Bdmax o cualquiera de las otras.

7. 7- Calcula los resultados para Aitor Tuga

33


grupo



Puede utilizar Microsoft Query para recuperar datos de orígenes externos. Si utiliza

Microsoft Query para recuperar datos de las bases de datos y de los archivos corporativos,

no es necesario que vuelva a escribir los datos que desea analizar en Excel. También

puede actualizar los informes y resúmenes de Excel automáticamente de la base de datos

de origen inicial siempre que la base de datos se actualice con información nueva.

Con Microsoft Query, puede conectar con orígenes de datos externos, seleccionar datos de

esos orígenes externos, importar datos a la hoja de cálculo y actualizar los datos según

sea necesario para mantener los datos de la hoja de cálculo sincronizados con los datos de

los orígenes externos.

Tipos de bases de datos a los que se puede obtener acceso.

Es posible recuperar datos de varios tipos de bases de datos, incluidos Microsoft Office

Access, Microsoft SQL Server y los servicios OLAP de Microsoft SQL Server. También puede

recuperar datos de libros de Excel y de archivos de texto.

Microsoft Office facilita controladores que pueden utilizarse para recuperar datos de los

siguientes orígenes de datos.

Seleccionar datos de una base de datos.

Puede recuperar datos de una base de datos creando una consulta, que es una pregunta

que se hace acerca de los datos almacenados en una base de datos externa. Por ejemplo,

si los datos están almacenados en una base de datos de Access, puede que desee conocer

las cifras de ventas de un producto determinado por regiones. Puede recuperar parte de

los datos seleccionando sólo los datos del producto y la región que desee analizar.

Actualizar la hoja de cálculo en una operación.

Cuando disponga de datos externos en un libro de Excel, siempre que cambie la base de

datos, puede actualizar los datos y, a su vez, el análisis, sin tener que volver a crear los

informes y los gráficos de resumen. Por ejemplo, puede crear un resumen de ventas

mensual y actualizarlo cada mes cuando disponga de nuevas cifras de ventas.


– Obtener Datos Externos (Query Analyzer) –

34


grupo



Forma en la que Microsoft Query utiliza los orígenes de datos.

Una vez establecido un origen de datos para una base de datos determinada, lo podrá

utilizar siempre que desee crear una consulta para seleccionar y recuperar los datos de

esa base de datos, sin tener que volver a escribir toda la información de conexión.

Microsoft Query utiliza el origen de datos para conectarse con la base de datos externa y

mostrar los datos que están disponibles. Después de crear la consulta y devolver los datos

a Excel, Microsoft Query proporciona al libro de Excel la información de la consulta y del

origen de datos de modo que pueda volverse a conectar con la base de datos cuando

desee actualizar los datos.

Conectar con un origen de datos

¿Qué es un origen de datos?

Un origen de datos es un conjunto de información que permite que Excel y Microsoft Query

se conecten con una base de datos externa. Cuando utilice Microsoft Query para

establecer un origen de datos, asigne un nombre al origen de datos y, a continuación,

proporcione el nombre y la ubicación de la base de datos, el tipo de base de datos y la

información de inicio de sesión y la contraseña. En esta información también se incluye el

nombre de un controlador OBDC o un controlador del origen de datos, que es un programa

que realiza conexiones con un tipo de base de datos determinado.

Para definir un origen de datos mediante Microsoft Query:

1. En la ficha Datos, en el grupo Obtener datos externos, haga clic en De otras

fuentes y luego en Desde Microsoft Query.

2. Siga uno de los procedimientos siguientes:

� Para especificar un origen de datos para una base de datos, un archivo de texto o

un libro de Excel, haga clic en la ficha Bases de datos.

35


grupo



3. Haga doble clic en <Nuevo origen de datos>.

O bien, haga clic en <Nuevo origen de datos> y luego en Aceptar.

Aparecerá el cuadro de diálogo Crear nuevo origen de datos.

4. En el paso 1, escriba un nombre para identificar el origen de datos.

5. En el paso 2, haga clic en un controlador para el tipo de base de datos que se va a

utilizar como origen.

Si la base de datos externa a la que desea obtener acceso no es compatible con los

controladores ODBC instalados con Microsoft Query, deberá conseguir e instalar un

controlador ODBC que sea compatible con Microsoft Office de otro proveedor, como

el fabricante de la base de datos. Póngase en contacto con el proveedor de la base

de datos para obtener instrucciones sobre la instalación.

6. Haga clic en Conectar y, a continuación, proporcione la información necesaria para

conectarse al origen de datos. Para las bases de datos, los libros de Excel y los

archivos de texto, la información proporcionada dependerá del tipo de origen de

datos que haya seleccionado. Es posible que se pida un nombre de conexión, una

contraseña, la versión de la base de datos que utiliza, la ubicación de la base de

datos e información adicional específica para el tipo de base de datos.

.7. Tras especificar la información necesaria, haga clic en Aceptar o Finalizar para

volver al cuadro de diálogo Crear nuevo origen de datos.

36


grupo



Combinar Correspondencia entre Excel y Word:

Quizás te estés preguntando "¿qué es eso de combinar correspondencia?". Pues la verdad

es que combinar correspondencia, es lo que hacemos cuando tenemos los datos de

nuestros contactos en un fichero, y una carta modelo en otro. En ese caso, lo que haremos

será combinar la carta modelo, con todos los contactos que tenemos en el otro fichero.

Veámoslo de una forma más simplificada, y con un ejemplo. Supongamos que tenemos en

un documento, una lista con los nombres, direcciones, y un montón de información más

relativos a nuestros clientes. Ahora supongamos que queremos mandarles a todos ellos

una oferta, por la cual si compran durante este mes, les haremos un descuento que será

distinto dependiendo de quién sea el cliente. Para mandar esas cartas a nuestros clientes

con el mínimo esfuerzo y máximas garantías, lo ideal es preparar una carta modelo, y

seguir los pasos que se van a explicar.

Para nuestro ejemplo, usaremos Excel como "base de datos", es decir, como fuente u

origen donde almacenaremos los datos de nuestros clientes, aunque podríamos usar

Word, de Access, etc., pero claro, como este curso versa sobre Excel, es lógico que los

datos los tengamos en esta aplicación.

Imagine que tenemos una tabla como esta, donde tenemos los datos correspondientes a 8

clientes, aunque podríamos haber puesto tantos como tengamos:

Ahora imaginemos que queremos mandarles esta carta a todos ellos, y que redactaremos

en Word (si tenemos nuestra carta con un membrete creado con el propio Word, será

mucho mejor, pues nos ahorraremos el papel pre impreso que encargamos a la imprenta)


– Conexión con otros Programas –

37


grupo



Cómo ves, he habilitado una zona en la parte superior del escrito (XXX-YYY-ZZZ), que

corresponde al nombre, domicilio, y demás datos que queramos incluir, relativos a nuestro

cliente. En el texto, tenemos también un dato que es el porcentaje de descuento que

aplicaremos a ese cliente, y que a efectos ilustrativos, para que sea más visible, figura en

XXXX%.

Ahora nos queda el trabajo de combinar la correspondencia, es decir, de aplicar ese escrito

a los 8 clientes que tenemos en nuestra tabla de Excel. Este libro de Excel que tendremos

grabado en nuestro disco duro, lo llamaremos clientes, y la hoja del libro donde

tendremos los datos, la llamaremos Datos de clientes. Para combinar la

correspondencia, abriremos Word, y redactaremos nuestra carta, dejando vacíos los

espacios que yo he puesto en formato particular, es decir, omitiremos el nombre del

cliente, su dirección, y el porcentaje de descuento.

Una vez tengamos redactado nuestro escrito, desde el propio Word, en la ficha

Correspondencia, seleccionaremos Barras de herramientas, y a continuación Iniciar

combinación de correspondencia. Una vez hecho eso, utilice el Asistente para la

combinación de correspondencia. En el paso 3 de 6 seleccione Utilizar una lista

existente.

Aparecerá una ventana, para localizar el fichero de origen, es decir, el fichero donde

tenemos los datos de los clientes, y que se llama Clientes.xls. Tan solo deberemos

localizarlo, y haciendo clic sobre él. Seguidamente nos aparecerá una imagen como esta:

38


grupo



Seleccionaremos la hoja donde tenemos los datos de los clientes (recuerde que antes les

dije que precisamente la hoja del libro se llamaba así Datos de clientes), y pulsaremos el

botón aceptar, fijándonos que esté marcada con una muesca la opción que aparece al pie

de esa ventana y donde pone La primera fila de datos contiene encabezados de columna,

pues precisamente la primera fila de la hoja de Excel, es el nombre de cada uno de los

campos: cliente, dirección, población, etc.

Ahora ya estaremos en disposición de empezar a combinar los datos de nuestros clientes,

con ese escrito. Para ello, nos situaremos en el encabezado del escrito, es decir, en la zona

habilitada para poner el nombre de nuestro cliente, y pulsaremos en Insertar campos

combinados. Aparecerá una pantalla como esta, donde nos aparecerán los campos

correspondientes a la tabla de Excel, donde teníamos los datos de los clientes:

39


grupo



Como ya estamos situados en el encabezado del texto, pulsaremos en esa ventana, sobre

el Nombre, luego sobre Apellidos, y así hasta el final, aunque no incluiremos el País, por

ser todos nuestros clientes de España, y el servicio de correos ya sabe que cuando

hablamos de Madrid o de Barcelona, si no decimos lo contrario, nos estamos refiriendo a

ciudades españolas. Una vez insertados los campos de combinación, veremos que en

pantalla nos aparece esto:

«Nombre»«Apellidos»«Dirección»«Población»«Código_postal»«Provincia»

Ahora solo tendremos que incluir un espacio entre el nombre y los apellidos, entre la

población y el código postal (para que no se junten los datos), y unos saltos de línea,

pulsando intros entre los campos, de tal forma que nos quede así:

«Nombre» «Apellidos»

«Dirección»

«Población» «Código_postal»

«Provincia»

Ahora solo nos quedará situar el cursor en la zona de texto donde informamos del

porcentaje de descuento correspondiente a ese cliente, y haremos lo mismo, insertando el

campo combinado correspondiente al descuento, de tal forma que el texto nos quedará

así:

Ahora ya tenemos la carta modelo (el documento maestro), donde combinaremos los

datos de todos nuestros clientes. Para proceder al último paso, tan solo nos quedará

40


grupo



pulsar en el cuarto icono de la barra de combinación de correspondencia, comenzando a

contar desde la derecha (el tercer icono habilitado), y nos aparecerá esta ventanita final:

Automáticamente se generarán tantos documentos como registros tengamos en nuestra

“base de datos” de Excel.

41


grupo



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. Además, podemos modificar su aspecto y mover

campos de lugar.

Para crear tablas dinámicas hemos de tener previamente una tabla de datos preparada y

posteriormente acceder a Insertar - Tabla Dinámica y Gráfico dinámico.

1. Crea la siguiente tabla de datos:

2. En Microsoft Office Excel 2010, el comando Informe de tablas y gráficos

dinámicos se ha separado en los dos siguientes comandos:

• El comando Tabla dinámica, que muestra el cuadro de diálogo Crear tabla

dinámica.

• El comando Gráfico dinámico, que muestra el cuadro de diálogo Crear tabla

dinámica con el gráfico dinámico.

Nota: Estos dos comandos están disponibles en el comando agrupado Tabla

dinámica, en el grupo Tablas de la ficha Insertar.

3. Selecciona toda la tabla y accede a Tabla Dinámica.

4. 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 (opción elegida por

defecto).

5. Acepta el rango pulsando en Aceptar.

6. Se crea una hoja nueva con la estructura de lo que será la tabla dinámica. Lo que

hay que hacer es "arrastrar" los campos desde la barra que aparece en la parte

inferior, hacia la posición deseada en el interior de la tabla.


– Tablas Dinámicas (Informes) –

42


grupo



7. Arrastra los campos Producto y Mes a la posición que se muestra en la figura:

8. Arrastra ahora el campo Precio en el interior (ventana grande). Automáticamente

aparecerá el resultado:

Nota: Este resultado también lo hubiésemos conseguido arrastrando Mes a Rótulo de

Columna, Producto a Rótulo de Fila y Precio a Valores (Suma de Precio).

Hemos diseñado la estructura para que nos muestre los productos en su parte izquierda,

los meses en columnas, y además, el precio de cada producto en la intersección de la

columna. Observa también que se han calculado los totales por productos y por meses.

Si modificamos algún dato de la tabla original, podemos actualizar la tabla dinámica

desde la opción Datos - Actualizar datos siempre que el cursor esté en el interior de la

tabla dinámica.

Al actualizar una tabla, Excel compara los datos originales. Pero si se han añadido nuevas

filas, tendremos que indicar el nuevo rango accediendo nuevamente a Insertar - Tablas

y gráficos dinámicos.

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

43


grupo



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. Por ejemplo, puede ser que queramos ver la tabla con la disposición

de los campos al revés, es decir, los productos en columnas y los meses en filas.

Desde la barra de modificación de la tabla, podemos realizar operaciones de

actualización, selección de campos, ocultar, resumir, agrupar, etc.

44


grupo



Ejercicio Práctico:

1. Crea la siguiente tabla de datos:

2. Seleccione el rango A1:G15.

3. Haz clic en el botón Tabla dinámica de la ficha Insertar.

4. La opción Seleccione una tabla o rango debería estar marcada y con el rango

A1:G15 en la caja de texto.

5. Seleccione Nueva hoja de cálculo y pulse en Aceptar.

6. Se abrirá el panel lateral de tablas dinámicas.

7. Arrastre el campo SEMANA a Rótulos de columna.

8. Arrastre el campo CLIENTE a Rótulos de fila.

9. Arrastre el campo TOTAL PAGO a ∑ Valores.

10. Arrastre el campo ARTÍCULO a ∑ Valores.

Nota: El campo ARTÍCULO se añadirá a los rótulos de columna, vamos a cambiar

esto porque lo queremos en los rótulos de fila.

11. En el área de Rótulos de columna despliegue el campo Valores y seleccione la

opción Mover a rótulos de fila (Menú contextual).

12. Haga clic en el botón Encabezados de campo de la ficha Opciones para quietar

los encabezados de la tabla dinámica.

TEMATEMATEMATEMA No. No. No. No. 11111111 –––– MS EXMS EXMS EXMS EXCEL CEL CEL CEL 2010201020102010

– Tablas Dinámicas (Configuración de campo de valor) –

45


grupo



13. El resultado es el siguiente:

.

En estos momentos tenemos una tabla en la que se nos muestra por cada

semana qué ha comprado cada cliente.

La fila Suma de TOTAL PAGO nos da lo que ha gastado cada cliente en cada

semana.

Sin embargo la fila Suma de ARTÍCULO nos muestra la suma del número de

artículo para cada semana, esto no es lo que queremos. Nuestro objetivo es que

esta última fila muestre el número de artículos que se han comprado, así

podríamos saber para cada semana cuánto se ha gastado cada cliente y cuántos

artículos ha comprado.

14. Haga clic derecho sobre cualquier celda de la fila Suma de ARTÍCULO y en el

menú emergente seleccione Configuración de campo de valor.

15. En el cuadro de diálogo seleccione la función Cuenta y pulse Aceptar.

46


grupo



Ya tenemos lo que queríamos. Ahora desde la ficha Diseño modifique el aspecto

de la tabla, deberá quedarte algo como esto:

Nota: Active la opción Filas con bandas – Estilo de tabla dinámica claro 20

16. Filtremos ahora los resultados para ver solamente los datos del cliente Renato:

�� Haga clic sobre una celda de la tabla para que aparezca el panel lateral.

�� Haga clic sobre la flecha a la derecha del campo CLIENTE en la lista de

campos.

47


grupo



�� Deseleccione todos los clientes menos al cliente Renato.

�� Pulse en Aceptar.

�� Cierre el libro de trabajo guardando los cambios realizados.

48


grupo



Las tablas dinámicas son un tipo de objeto que "reside" en la hoja de cálculos pero el

rango ocupado por la tabla tiene un comportamiento distinto a los rangos normales de

Excel. El rango ocupado por la tabla dinámica no puede ser modificado directamente en la

hoja. Por ejemplo, no podemos agregar líneas o columnas, o fórmulas en las distintas

celdas.

Cuando queremos hacer este tipo de modificaciones en una tabla dinámica tenemos dos

posibilidades:

1 - Seleccionar la tabla y copiarla a otra ubicación (ya sea en la misma hoja, o en otra)

usando Edición – Pegado Especial – Valores. Este método tiene la ventaja de dar mucha

flexibilidad en el manejo de los datos (formatos, fórmulas, etc.); pero tiene la gran

desventaja de romper el vínculo dinámico entre la tabla y los datos originales.

2 – Crear campos y/o elementos calculados. De esto trata justamente este tema.

Basándose en el archivo entregado por el instructor (hoja Ventas) genere la siguiente

Tabla Dinámica:

En el área de Página hemos puesto los meses, de manera que podemos ver los datos de

cada mes con un clic.

Ahora digamos que queremos calcular el precio promedio de las unidades vendidas

(ventas / unidades = precio promedio) y también agrupar los meses por bimestres

(enero + febrero = bimestre 1).

El precio promedio consiste en crear un nuevo campo, dividiendo los elementos de un

campo por los de otro. Esto es un campo calculado. Los pasos a dar son los siguientes:


– Tablas Dinámicas (Campos y Elementos calculados) –

49


grupo



1 – Hacemos clic en algún lugar de la tabla y en el asistente de Tablas Dinámicas

activamos Fórmulas – Campos Calculados…

2 - En la caja "nombre" anotamos "Precio Promedio" y en la caja "Fórmula"

=Ventas/Unidades (con la caja activada, hacemos doble clic a Ventas, luego anotamos el

símbolo "/" y luego doble clic a Unidades)

3 - Después de hacer clic en "Aceptar" y realizar algunos ajustes al formato, obtenemos

esta tabla dinámica:

Como se observa, hemos agregado un nuevo campo a la tabla: "Precio Promedio".

Para demostrar el uso de elementos calculados, reorganizamos nuestra tabla dinámica

poniendo los meses como campos de fila y los departamentos en el área de Página, de

esta manera:

50


grupo



Para crear el elemento calculado "Bimestre 1" procedemos de la siguiente manera:

1 - Seleccionamos la celda A4 (donde aparece "Meses", el nombre del campo con cuyos

elementos crearemos un elemento calculado), en el asistente de Tablas Dinámicas

activamos la opción Fórmulas – Elementos Calculados.

2 - En el diálogo que se abre seleccionamos "Meses" en la lista "Campos"; en la lista

"elementos" vemos los meses (los elementos del campo). En la caja nombre escribimos

Bimestre 1; luego seleccionamos la caja Fórmula y anotamos =enero+febrero (lo que se

hace con un doble clic sobre el nombre del elemento). Luego clic en Enter y volvemos a

seleccionar el campo Meses.

3 - Repetimos la operación para el Bimestre 2 (marzo+abril) y Bimestre 3

(mayo+junio), luego pulsamos "Aceptar".

Excel tiene dos problemas relacionados con los elementos calculados:

51


grupo



1 - Son agregados automáticamente al final de la lista de elementos del campo.

2 – El total general incluye los elementos calculados, por lo tanto da como resultado el

doble de lo que debería ser.

El primer problema lo resolvemos moviendo los elementos a la posición deseada. Una

de las formas de hacer esto es copiar Bimestre 1 y pegarlo inmediatamente debajo de

"febrero" (o escribir manualmente Bimestre 1). Excel reorganiza la tabla

automáticamente:

El problema del total general lo podemos tratar de dos maneras. Una es eliminar el total

general para las columnas en Opciones de Tablas (opción Mostrar totales generales

en columnas). La otra es crear un nuevo elemento, Total, que sume Bimestre 1,

Bimestre 2 y Bimestre 3:

52


grupo



Obteniendo al final lo siguiente:

En resumen:

Un campo calculado es un nuevo campo creado realizando operaciones con campos

existentes.

Un elemento calculado es un nuevo elemento en un campo, creado con los elementos

existentes del campo.

Para eliminar los campos o elementos calculados tenemos que usar el asistente de Tablas

Dinámicas. Por ejemplo, para eliminar el campo Precio Promedio abrimos el diálogo de

Fórmulas en Campos Calculados. En la caja Nombre elegimos el campo que queremos

eliminar y luego pulsamos el botón Eliminar.

53


grupo



Informes de gráfico dinámico

Un informe de gráfico dinámico representa gráficamente los datos de un informe de

tabla dinámica. Puede modificar el diseño y los datos que se muestran en un informe

de gráfico dinámico tal y como se hace con un informe de tabla dinámica. Un informe de

gráfico dinámico siempre tiene un informe de tabla dinámica asociado que utiliza el

diseño correspondiente. Ambos informes tienen campos que se corresponden. Cuando

modifica la posición de un campo en uno de los informes, también se modifica el campo

correspondiente del otro informe.

Además de las series, categorías, marcadores de datos de los gráficos estándar, los

informes de gráficos dinámicos tienen algunos elementos especializados que se

corresponden con los informes de tablas dinámicas, como se muestra en la siguiente

ilustración.

Campo de filtro del informe: Campo que se utiliza para filtrar datos por elementos

específicos. En el ejemplo, el campo de página Región muestra los datos de todas las

regiones. Para mostrar los datos de una sola región, puede hacer clic en la flecha de lista

desplegable junto a (Todas) y seleccionar la región.

Utilizar campos de filtro de informe es una forma cómoda de resumir y centrarse

rápidamente en un subconjunto de datos sin modificar la información de la serie y la

categoría. Por ejemplo, si está realizando una presentación, puede hacer clic en (Todos)


– Creación de Informes de Gráficos Dinámicos –

54


grupo



en un campo de filtro Año para mostrar las ventas de todos los años y luego centrarse en

años concretos haciendo clic en un año cada vez. Cada página del filtro del informe del

gráfico tiene el mismo diseño de categorías y series para distintos años, de modo que

resulta fácil comparar los datos de cada año. Además, al permitir recuperar sólo una

página del filtro a la vez de un conjunto grande de datos, los campos de página del filtro

conservan la memoria cuando el gráfico utiliza datos de origen externo.

Campo de valores: Campo del origen de datos subyacente que proporciona valores

para comparar o medir. En el ejemplo, Suma de ventas es un valor de datos que resume

las ventas trimestrales por región para cada deporte. El marcador de datos de primera

categoría (Trim1) llega casi a 250 en el eje de valores (y). Esta cantidad es la suma de

las ventas correspondientes a Tenis, Safari y Golf durante el primer trimestre. Según el

origen de datos utilizado para el informe, puede cambiar la función de resumen a

Promedio, Contar, Producto u otro cálculo.

Campo de serie: Campo que se asigna a una orientación de serie en un informe de

gráfico dinámico. Los elementos del campo proporcionan las series de datos individuales.

En el ejemplo, Deporte es un campo de serie con tres elementos: Tenis, Safari y Golf. En

el gráfico, las series se representan en la leyenda.

Elemento: Los elementos representan entradas únicas en un campo de columna o de

fila, y aparecen en las listas desplegables de los campos de filtro del informe, en los

campos de categoría y en los campos de serie. En el ejemplo, Trim1, Trim2, Trim3 y

Trim4 son elementos del campo de categoría Trimestre, mientras que Tenis, Safari y Golf

son elementos del campo de serie Deporte. Los elementos de un campo de categoría

aparecen como rótulos en el eje de categorías del gráfico. Los elementos de un campo de

serie aparecen en la leyenda y proporcionan los nombres de las serie de datos

individuales.

Campo de categoría: Campo de los datos de origen que se asigna a una orientación de

categoría en un informe de gráfico dinámico. Un campo de categoría proporciona las

categorías individuales cuyos puntos de datos se representan gráficamente. En el

ejemplo, Trimestre es un campo de categoría. En un gráfico, las categorías suelen

aparecer en el eje x, o eje horizontal, del gráfico.

Personalizar el informe: Puede cambiar el tipo de gráfico y otras opciones, como los

títulos, la ubicación de las leyendas, los rótulos de datos, la ubicación del gráfico, etc.

55


grupo



Puede crear un informe de gráfico dinámico cuando primero crea un informe de tabla

dinámica, o bien puede crear un informe de gráfico dinámico desde un informe de tabla

dinámica existente.

Comparar un informe de tabla dinámica y un informe de gráfico dinámico

Al crear un informe de gráfico dinámico a partir de un informe de tabla dinámica, el

diseño del informe de gráfico dinámico, es decir, la posición de sus campos, está

determinado inicialmente por el diseño del informe de tabla dinámica. Si crea primero el

informe de gráfico dinámico, determina el diseño del gráfico arrastrando campos de la

Lista de campos de tabla dinámica hasta las áreas de la hoja de grafico.

Los siguientes informes de ventas de tabla dinámica y gráfico dinámico muestran la

relación entre los dos.

56


grupo



Diferencias entre gráficos e informes de gráficos dinámicos

Si ya conoce los gráficos normales, verá que la mayoría de operaciones de los informes

de gráficos dinámicos son las mismas. Sin embargo, hay algunas diferencias:

Interacción: Con los gráficos normales, se crea un gráfico para cada vista de los datos

que se desea mostrar, pero no son interactivos. Con los informes de gráficos dinámicos

puede crear un gráfico y ver los datos interactivamente modificando el diseño del informe

o los detalles que se muestran.

Tipos de gráficos: El tipo de gráfico predeterminado para un gráfico normal es un

gráfico de columnas agrupadas, que compara los valores de las categorías. El tipo de

gráfico predeterminado para un informe de gráfico dinámico es un gráfico de columnas

apiladas, que compara la contribución de cada valor a un total en todas las categorías.

Puede cambiar el informe de gráfico dinámico por cualquier tipo de gráfico, menos xy

(dispersión), de cotizaciones y de burbuja.

Ubicación de los gráficos: Los gráficos normales están incrustados de forma

predeterminada en hojas de cálculo. Los informes de gráfico dinámico se crean en hojas

de gráficos de forma predeterminada. Una vez creado, el informe de gráfico dinámico se

puede cambiar a otra ubicación en una hoja de cálculo.

Datos de origen: Los gráficos normales están vinculados directamente a las celdas de la

hoja de cálculo. Los informes de gráfico dinámico se pueden basar en varios tipos de

datos del informe de tabla dinámica asociado.

Elementos de gráficos: Los informes de gráfico dinámico contienen los mismos

elementos que los gráficos normales, pero además contienen campos y elementos que

pueden agregarse, girarse o quitarse para mostrar vistas distintas de los datos. Las

categorías, series y datos de los gráficos normales son campos de categoría y campos de

serie, y en los informes de gráfico dinámico, también campos de valor. Los informes de

gráfico dinámico también pueden contener campos de filtro del informe. Cada uno de

estos campos contiene elementos, que en los gráficos normales se muestran como

rótulos de categorías o nombres de serie en las leyendas.

Formato: La mayoría del formato, incluidos los elementos, el diseño y el estilo, se

guarda cuando se actualiza un informe de gráfico dinámico. Sin embargo, no se guardan

57


grupo



las líneas de tendencia, los rótulos de de datos, las barras de error u otros cambios

realizados en las series de datos. Los gráficos normales no pierden estos cambios de

formato cuando se les aplican.

Mover o cambiar el tamaño de los elementos: En un informe de gráfico dinámico, no

es posible mover ni cambiar el tamaño del área de trazado, la leyenda, los títulos de

gráficos ni los títulos de ejes, aunque se puede seleccionar una de las distintas posiciones

preestablecidas para la leyenda y modificar el tamaño de fuente de los títulos. En un

gráfico normal, se pueden mover todos estos elementos y cambiar su tamaño.

58


grupo



Es una de las opciones de la ficha Datos – Análisis Y si que nos permite guardar con un

nombre los cambios realizados en un conjunto de valores de la hoja para observar cómo

éstos afectan al resto de datos.

Vamos a partir de la siguiente hoja de cálculo:

Se ha calculado el valor actual neto (VAN) y la tasa interna de rendimiento (TIR)

para cuatro proyectos de inversión diferentes, de los que conocemos la inversión inicial y

los rendimientos a obtener en los próximos 5 años. La tasa con la que estamos

resolviendo el ejercicio es el 5%.

Vamos a utilizar el Administrador de escenarios para proponer -por ejemplo- 3 tipos

de entornos:

• CRITICO (tasa del 8%)

• NORMAL (tasa del 5,1%)

• EXTRAORDINARIO (tasa del 3,5%)

En primer lugar, como es la tasa la que va a condicionar el que un entorno sea de un tipo

o de otro, debemos situarnos en dicha celda: [C4]. Posteriormente, vamos a la opción

"Administrador de Escenarios..." de la opción Análisis Y si de la ficha Datos,

apareciendo el siguiente cuadro de diálogo:


– Administrador de Escenarios –

59


grupo



A continuación debemos seleccionar el botón , para ir añadiendo cada uno de

los distintos escenarios que vamos a definir; cada vez que lo hagamos obtendremos el

siguiente cuadro:

En dicho cuadro vamos a incorporar el nombre que pretendemos asignar al peor de los

escenarios (entorno CRITICO), la celda [C4] sale por defecto porque la habíamos

seleccionado previamente y, en el recuadro de observaciones, indica quien es la persona

que ha realizado las últimas modificaciones en los escenarios, etc..

60


grupo



Una vez que estamos de acuerdo con el primer escenario incorporado, aceptamos y

obtenemos.

Por defecto, se visualiza el contenido de la celda [C4], pero ya mencionábamos que para

el escenario CRITICO, el tipo sería del 8%, entonces debemos teclearlo:

Procedemos del mismo modo para cada uno de los restantes...obteniendo al final:

61


grupo



Una vez creados los escenarios, debemos aprender a mostrarlos y editarlos.

Para mostrarlos, una vez que activábamos la opción "Administrador de Escenarios" de

la ficha Datos, debemos seleccionar el botón una vez que hayamos elegido

uno de los tres escenarios, -en esta ocasión tomamos el CRÍTICO- QUEDÁNDONOS la

Hoja de cálculo con este aspecto:

De igual forma, procederíamos con el NORMAL y el EXTRAORDINARIO:

También podríamos combinar escenarios desde otras hojas; esta vez deberíamos pulsar

el botón

62


grupo



Por otra parte, podemos generar informes resumen de los distintos escenarios,

creándose una lista con las variables y celdas resultantes. Al seleccionar la opción

, obtenemos el cuadro:

Automáticamente, se crea una hoja como la que vemos a continuación, en donde el

informe queda esquematizado y se asigna directamente un formato para una mayor

facilidad de utilización del usuario:

63


grupo



... Si hubiéramos seleccionado el otro tipo de informe...

... hubiéramos obtenido

64


grupo



La utilización de software computacional para resolver problemas de programación lineal

es actualmente una fortaleza tecnológica que facilita la elaboración de estudios de

factibilidad. Específicamente la opción de Solver de Excel constituye una adecuada

herramienta en este sentido, de relativamente fácil programación inicial y posterior

versatilidad para aplicar a diferentes problemas.

Para conocer la conveniencia de la aplicación SOLVER de EXCEL Microsoft®, utilizaremos

un ejemplo práctico:

Max Z = 10 X1 + 8 X2 [Ecuación 1]

Sujeto a:

30X1 + 20X2 ≤ 120 [Ecuación 2]

2X1 + 2X2 ≤ 9 [Ecuación 3]

4X1 + 6X2 ≤ 24 [Ecuación 4]

y X1, X2 ≥ 0 [Ecuación 5]

La única dificultad que tenemos es el de modelar el programa dentro del Excel, y eso, es

muy fácil. Por supuesto, aunque existe una infinidad de maneras de hacerlo, aquí

propongo una.

Procedimiento:

�� Se abre Excel 2010.

�� En una hoja, se ubican las celdas que se corresponderán con el valor de las variables

de decisión; en éste caso, las celdas B6 y C6, se les da un formato para diferenciarlas

de las demás, aquí azul oscuro (ver captura abajo). Se ubican también, las celdas que

contendrán los coeficientes de las variables de decisión, B4 y C4, y se llenan con sus

respectivos valores, 10 y 8. Este último paso se podría omitir y dejar los coeficientes

definidos en la celda de la función objetivo, lo cual es mejor para los análisis de

sensibilidad y para que la hoja quede utilizable para otro programa.

�� Se ubica la celda B3 que corresponderá a la función objetivo (celda objetivo). En ella

se escribe la función correspondiente, en éste caso la Ecuación 1: el coeficiente de X1

(en B4) por el valor actual de X1 (en B6) mas el coeficiente de X2 (en C4) por el valor

actual de X2 (en C6). Es decir, =$B$4*$B$6+$C$6*$C$4


– Solver –

65


grupo



�� Coeficientes para la primera restricción los podemos escribir en la misma columna de

las variables de decisión; en las celdas B7 y C7, con los valores 30 y 20, seguido del

sentido de la desigualdad (<=) y de su correspondiente RHS: 120.

�� A la derecha ubicaremos el valor actual de consumo de la restricción que se escribirá

en función de las variables de decisión y de los coeficientes de la restricción. Esta

celda, la utilizará Solver como la real restricción, cuando le digamos que el valor de

ésta celda no pueda sobrepasar la de su correspondiente RHS (en este caso, 120). De

nuevo será el valor del coeficiente por el de la variable: =B7*$B$6+C7*$C$6. Nótese

que ahora B7 y C7 no tienen el signo $. Esto nos permitirá que luego que se haya

escrito esta celda, se podrá arrastrar hacia abajo para que Excel escriba la fórmula por

nosotros (¡la comodidad de Excel!), pero tomando los valores relativos a los

coeficientes que corresponda a los mismos valores de las variables de decisión.

�� Se repite los pasos anteriores para las otras restricciones, pero ahora la fórmula será:

=B8*$B$6+C8*$C$6 y =B9*$B$6+C9*$C$6.

66


grupo



El resto del formato es para darle una presentación más bonita a la hoja. ¡Ahora a

resolverlo!

Resolución:

�� Hacer clic en la ficha Datos > Solver.

�� Lo primero que hay que hacer es especificar la celda objetivo y el propósito:

Maximizar. Se escribe B3 (o $B3 ó B$3 ó $B$3 como sea, da igual), en el recuadro

"Cambiando las celdas", se hace un clic en la flechita roja, para poder barrer las

celdas B6 y C6 es exactamente lo mismo si se escriben directamente los nombres.

Hasta aquí se tendrá una pantalla como la siguiente:

�� Y ahora para las restricciones se hace clic en agregar. En F7 está la primera

restricción, como se puede ver en la captura. Se especifica el sentido de la restricción

<=, >= ó =. Aquí también se puede especificar el tipo de variable, por defecto es

continua, pero se puede escoger "Int" para entera o "Bin" para binaria. En el recuadro

67


grupo



de la derecha establecemos la cota. Aquí podemos escribir 120 pero mejor escribimos

$E$7 para que quede direccionado a la celda que contiene el 120, y después lo

podríamos cambiar y volver a encontrar la respuesta a manera de análisis de

sensibilidad. Se presiona Aceptar.

Repita el paso anterior para las otras restricciones. Se obtiene el siguiente resultado:

�� La condición de no negatividad hay que incluirla manualmente:

Finalmente, el cuadro de dialogo debe verse así:

68


grupo



�� Hacer clic en Resolver:

69


grupo




2. Primer Ejercicio: Los datos anteriores representan las notas obtenidas por un

grupo de 20 estudiantes.

3. Ahora debemos representar los datos por medio de una grafica circular y grafica de

barras. Para ello cree un gráfico con las siguientes características:

• Rango de celdas a graficar: A1:B6

• Tipo de Gráfico: Circular.

• Series en: Columnas.

• Título del gráfico: NOTAS DE LOS ESTUDIANTES.

• Leyenda: Abajo.

• Rótulos de Datos: Mostrar porcentaje.

• Ubicación del gráfico: En una hoja nueva (nombre: Notas de los Estudiantes).

4. De igual forma realice el grafico de Columnas así:

• Rango de celdas a graficar: A1:B6

• Tipo de Gráfico: Columna agrupada.

• Series en: Columnas.


– Excel Estadístico I (Anexo) –

NOTAS DE LOS ESTUDIANTES

25%

15%

40%

5%

15%

A B C D F

70


grupo



• Título del gráfico: NOTAS DE LOS ESTUDIANTES.

• En eje de categorías X: Notas.

• En eje de Valores Y: Frecuencias.

• Leyenda: Abajo.

• Rótulos de Datos: Mostrar valor.

• Ubicación del gráfico: En una hoja nueva (nombre: Notas de los Estudiantes_2).

5. Segundo Ejercicio: Ingrese los datos como aparecen ahora:

6. Los datos anteriores representan el Peso y Estatura de un grupo de 15 estudiantes.

7. Debemos ahora determinar la Media Aritmética, Moda, Mediana y Desviación

Estándar de la variable PESO.

• Para calcular la Media Aritmética: =PROMEDIO (B2:B16)

• Para calcular la Moda: =MODA(B2:B16)

• Para calcular la Mediana: =MEDIANA(B2:B16)

• Para calcular la Desviación Estándar: =DESVEST(B2:B16)

8. Ahora calcule lo mismo para la variable FRECUENCIA. Al final quedará así:

NOTAS DE LOS ESTUDIANTES

5

3

8

1

3

0

1

2

3

4

5

6

7

8

9

A B C D F

Notas

Fre

cuen

cias

FRECUENCIA (Nº Estudiantes)

71


grupo



9. Es el momento de hacer el Histograma de la variable PESO. Para ello debemos

hacer la tabla de frecuencia por clases. Estas clases se establecen tomando en

cuenta los datos. Se debe calcular que más o menos queden 6 ó 7 clases. Ingrese

las columnas E, F y G como en la figura:

�� Importante: Una vez calculada la Frecuencia, seleccione el rango H3:H4 (para el

primer caso) comenzando por la celda de la fórmula. Presione F2 y, a continuación,

CTRL+MAYÚS+ENTRAR. Si la fórmula no se introduce como fórmula matricial, el resultado

único es 1.

Función: =SUMA(H13:H14)-I11-I9-I7-I5-I3

Función: =FRECUENCIA(B2:B16,G3:G4)

72


grupo



10. Realice un Histograma con la siguiente descripción:

• Rango de celdas a graficar: F3:F14, I3:I14.

• Tipo de Gráfico: Columnas / Columna agrupada.

• Series en: Filas. (Quite las Series en blanco)

• Título del gráfico: ESTUDIO SOBRE PESO DE ESTUDIANTES.

• En eje de categorías X: Peso.

• En eje de Valores Y: Frecuencias.

• Leyenda: Abajo.

• Rótulos de Datos: Mostrar valor.

• Ubicación del gráfico: En una hoja nueva (nombre: Peso de Estudiantes).

11. En este ejercicio se puede asumir que dependiendo de la estatura (X), se puede

determinar un peso (Y) aproximado. Para corroborar si esa relación se comporta de

una manera de ecuación lineal (Y = mX + b) Para calcular el Coeficiente de

Regresión debemos determinar los valores de m (pendiente) y b (intercepto).

• Determinar el Coeficiente de Correlación Lineal:

=COEF.DE.CORREL(B2:B16,C2:C16)

Como el coeficiente de correlación es 0.78 esto quiere decir que hay

correlación, y que ésta es positiva, por lo cual si la estatura aumenta

entonces el peso aumenta.

• Determinar el valor de los Coeficientes de la Ecuación de Regresión:

=PENDIENTE(B2:B16,C2:C16)

Los rangos en este caso indican el alcance de la variable dependiente Y

(peso) y el alcance de la variable independiente X (estatura)

• Determinar la Intercepción de los Ejes:

ESTUDIO SOBRE PESO DE ESTUDIANTES

2

4 4

3

1 1

0

0.5

1

1.5

2

2.5

3

3.5

4

4.5

1

Peso

Fre

cuen

cias

90-109 110-129 130-149 150-169 170-189 190-209

73


grupo



=INTERSECCION.EJE(B2:B16,C2:C16)

Los rangos en este caso indican el alcance de la variable dependiente Y

(peso) y el alcance de la variable independiente X (estatura).

12. El resultado debe ser el siguiente:

13. Por tanto la ecuación de regresión es Y = 2,858332127 X + -48,26769944

De esa ecuación podemos predecir el Peso (Y) si damos la Estatura (X):

• Si alguien mide más de 40 pulgadas debe pesar 66,06558564 libras, si

alguien mide 70 pulgadas debe pesar 151,81555246 libras, etc.

La Fórmula para esta prueba es:

14. Guarde su Libro de trabajo con el nombre Excel_Estadistico1.

74


grupo




2. Primer Ejercicio: Los datos anteriores representan las notas obtenidas por un

grupo de 10 estudiantes.

� Aplique Formato Condicional para que las notas desaprobatorias aparezcan en

rojo y las aprobatorias en azul.

� En la columna F la nota final será la media de las 3 Unidades redondeada.

� En la columna G aparecerá la situación del alumno de acuerdo a:

- Si la nota media esta entre 0 y 10 aparecerá Insuficiente.

- Si la nota media esta entre 11 y 14 aparecerá Aprobado.

- Si la nota media esta entre 15 y 17 aparecerá Notable.

- Si la nota media esta entre 18 y 20 aparecerá Excelente.

� Calcule la Mediana, Notas máxima y mínima, y Moda.

� Calcule el número de alumnos que hay.

� Calcule el número de alumnos insuficientes, aprobados, notables y excelentes y el

porcentaje que representa cada uno de los anteriores.

� Cree un Histograma que muestre el desempeño académico de todos los

estudiantes (En una hoja nueva con el nombre: Notas de los Estudiantes).


– Excel Estadístico II (Anexo) –

75


grupo



� Cree una grafica Circular que muestre el desempeño académico de todos los

estudiantes (En una hoja nueva con el nombre: Notas de los Estudiantes_2).

Solucionario:

• Fórmula para la celda F5:

• Fórmula para la celda G5:

• Fórmula para la celda C16:




• Fórmula para la celda G16:





• Fórmula para la celda D22:

3. Segundo Ejercicio: Hotel Playa Caliente

Los gastos del primer trimestre del Hotel Playa Caliente han sido:

� Las nóminas del personal de los meses de Enero y Febrero son $ 25,000 y de

Marzo $ 30,000.

� En alimentación $ 10,000 cada mes.

� En electricidad por los tres meses ha pagado $ 3,000.

� Los gastos de teléfono de las oficinas son: el primer mes $ 330., el segundo $

345, y el tercero $ 385.

� La póliza del seguro del hotel es de $ 110,000. anuales, pagadera

trimestralmente.

� El hotel tiene contratado un servicio de mantenimiento y paga una factura

mensual de $ 290. También tiene contratado un servicio de jardinería por $ 170

al mes

� En Marzo el hotel renovó el 40% de su lencería con un coste de $ 850.

� El grupo musical Hermanos Acuña anima el hotel cada noche cobrando por

actuación $ 80. En carnaval se organizó una fiesta con un coste de $ 165.

� Para la publicidad el hotel tiene contratado un servicio a una Empresa de

Marketing por $ 120 al mes. En Marzo, debido al comienzo de la temporada alta

invierten $ 250 más en publicidad.

76


grupo



� Para la prevención de riesgos tienen un contrato con una mutual por $ 45,000 al

año. Por falta de liquidez acuerdan pagarla mensualmente.

Los ingresos han sido los siguientes:

� Como resultado de la actividad hotelera en Enero, Febrero y Marzo, $ 225,000, $

198,700 y $ 218,500 respectivamente.

� Por inversiones financieras ha obtenido un resultado de $ 244,000, $ 216,000 y

$ 97,000 en los meses de Enero, Febrero y Marzo respectivamente.

Ejercicio:

� Ingrese los datos como se muestran en la figura.

� Halle el total de los gastos e ingresos en cada mes.

� Halle el resultado de cada mes. (Ingresos - Gastos).

� Halle el promedio de cada partida de ingreso y gasto durante los tres meses.

� Halle el porcentaje de cada partida de ingreso y gasto en relación con el total.

� Haga una fórmula en la que dependiendo de si se obtiene beneficios o no, se

aplique el 35% del impuesto de sociedades.

� Si hay beneficios y se aplica el impuesto de sociedades ¿cuál es el resultado

mensual? ¿Y el trimestral?

� Cree un grafico de tipo Línea que muestre los Ingresos y Gastos en el primer

trimestre (En una hoja nueva con el nombre: HPC-Ingresos-Gastos).

4. Guarde su Libro de trabajo con el nombre Excel_Estadistico2.

77


grupo



Google presenta la alternativa de poder trabajar y compartir Documentos, Hojas de

Cálculo y Presentaciones en Línea. Entre sus principales ventajas están:

�� Comparte y colabora en tiempo real

Elige quién puede acceder a tus documentos

Basta con que escribas la dirección de correo electrónico de los usuarios con quienes

quieras compartir un documento determinado y les envíes una invitación.

Comparte inmediatamente

Cualquier usuario al que hayas invitado a editar o a ver tu documento, hoja de cálculo o

presentación podrá acceder al mismo tan pronto como inicien sesión.

Edita y presenta con otros usuarios en tiempo real

Varios usuarios pueden ver los documentos y hacer cambios al mismo tiempo. Se incluye

una ventana de chat en pantalla para las hojas de cálculo y, con las revisiones de los

documentos, puedes saber exactamente quién ha cambiado qué y cuándo. Ver una

presentación con otros usuarios es muy sencillo, ya que cualquier usuario que se haya

unido a la presentación puede automáticamente seguir al presentador.

�� Almacena y organiza tu trabajo de forma segura


– Google Applications Spreadsheets (Hojas de Cálculo en Línea) –

78


grupo



Edita y accede desde cualquier parte

No tienes que descargar nada. Puedes acceder a tus documentos, hojas de cálculo y

presentaciones desde cualquier equipo que tenga una conexión a Internet y un navegador

estándar. Y es gratis.

Almacena tu trabajo de forma segura

Con el almacenamiento online y la función de guardado automático, ya no tendrás que

temer que falle el disco duro local o que haya un apagón.

Guarda y exporta copias fácilmente

Puedes guardar los documentos y hojas de cálculo en tu propio equipo en formato DOC,

XLS, CSV, ODS, ODT, PDF, RTF o HTML.

Organiza tus documentos

Encuentra tus documentos con facilidad organizándolos en carpetas. Arrastra y suelta los

documentos en tantas carpetas como quieras.

�� Controla quién puede ver tus documentos

Publica tu trabajo en forma de página web

Puedes publicar tus documentos online con un solo clic, como páginas web de aspecto

normal, sin tener que aprender nada nuevo.

Controla quién puede ver tus páginas

Puedes publicar tu trabajo para que esté a disposición de todo el mundo, de sólo algunas

personas o de nadie en absoluto... Depende de ti. También puedes anular la publicación

en cualquier momento.

Publica tus documentos en tu blog

Cuando hayas creado un documento, podrás publicarlo en tu blog.

Publica en tu empresa o grupo

Con Google Apps, es incluso más sencillo compartir documentos, hojas de cálculo y

presentaciones importantes en tu empresa o grupo.

79


grupo



¿Listo para crear tu primer documento de Google?

Ingrese a la siguiente dirección: http://spreadsheets.google.com

Aparecerá la siguiente página de inicio:

Sólo tienes que acceder a tu cuenta de Google. Ingrese allí su Correo Electrónico (no

necesariamente en Gmail) y su Contraseña.

Por Ejemplo, yo tengo el siguiente archivo guardado en mi cuenta de Google

Spreadsheets:

80


grupo



Al abrirlo se observa así:

Nota: Observe que está activada la Barra de Formulas.

Es posible Insertar Formulas que naturalmente están agrupadas según funciones:

81


grupo



También es posible Crear Gráficos:

Lógicamente el potencial de Google Spreadsheets esta en Compartir Documentos en

Línea, esto lo veremos con un buen ejemplo en clase.

82


grupo



Los controles son dispositivos de entrada de datos que pueden aparecer en una hoja de

cálculo o en un cuadro de diálogo.

Los controles usados en una hoja de cálculo son vinculados hacia una celda de esta.

Cuando Ud. Ingrese datos desde un control o cree una selección desde este, el resultado

de la selección aparece en la celda de la hoja de cálculo vinculada. El resultado en esta

celda entonces puede ser usado en cálculos de una hoja de cálculo estándar, tal como si

el usuario hubiese digitado el valor en las celdas.

Usando la barra de herramientas de Formularios

Ud. Puede dibujar controles en una hoja de cálculo haciendo clic en un botón en la barra

de herramientas Formulario y entonces arrastre en la hoja de cálculo indicando el

tamaño y localización para el control.

¿Cómo mostrar la barra de herramientas de Formulario...?

1. Seleccione del Menú Ver, la opción de Barra de herramientas.

2. Haga clic en Formularios.

Botón Nombre Descripción

Etiqueta Texto agregado a una hoja de cálculo o formulario para proporcionar

información acerca de un control, de una hoja de cálculo o de un formulario.

Cuadro de edición Caja de entrada de datos para texto, números, fechas o referencias de celdas.

No esta disponible en una hoja de cálculo.

Cuadro de grupo Un borde y un rótulo que agrupa los controles relacionados, como los botones

de opción o las casillas de verificación.

Botón Crear un botón para ejecutar una macro.

Casilla de verificación Un botón que activa o desactiva una opción. Puede seleccionarse más de una

casilla de verificación a la vez en una hoja o en un grupo.

Botón de opción Un botón que selecciona un grupo de opciones contenido en un cuadro de

grupo. Solamente puede seleccionarse un botón de opción en un grupo. Los botones de opción se utilizan cuando se permite una de varias posibilidades.

Cuadro de lista Una lista de texto. Retorna el número del item seleccionado.

Cuadro combinado Un cuadro con una lista desplegable. El elemento que se seleccione en el

cuadro de lista aparecerá en el cuadro de texto.

Cuadro combinado de lista

Una lista de texto con una caja de entrada de datos. No esta disponible para una hoja de cálculo.

Cuadro combinado desplegable

Un cuadro de lista con una caja de entrada de datos. No esta disponible para una hoja de cálculo.


– Formularios –

83


grupo



Botón Nombre Descripción

Barra de desplazamiento Un control que sirve para desplazarse a través de un rango de valores cuando

se hace clic en las flechas de desplazamiento o cuando se arrastra el cuadro de desplazamiento. Puede desplazarse por una página de valores haciendo clic entre el cuadro de desplazamiento y una flecha de desplazamiento.

Control de número Un botón que tiene una flecha arriba y otra abajo que puede adjuntarse a una

celda. Para aumentar un valor, haga clic en la flecha arriba; para disminuir un valor, haga clic en la flecha abajo.

Propiedades de control Muestra un cuadro de diálogo para el control seleccionado. Use el cuadro de

diálogo para configurar el control.

Modificar código Úselo para editar el código asignado al control seleccionado. Se usa tanto

para la hoja de cálculo como para el cuadro de diálogo.

Alternar cuadrícula Activa o desactiva la rejilla de alineamiento tanto en un cuadro de diálogo

como en una hoja de cálculo.

Ejecutar cuadro de diálogo

Muestra el cuadro de diálogo en la hoja de cálculo activa. Se usa para probar un cuadro de diálogo que Ud. haya dibujado. No esta disponible para la hoja de cálculo.

Creando hojas de cálculo que se asemejen a un formulario

Con un pequeño formateo, Ud. puede crear una hoja de cálculo bastante semejante a un

papel de formulario. Ud. probablemente quiera iniciar teniendo un formulario que no

contenga los elementos comunes en un libro de trabajo o en una hoja de cálculo.

Ud. puede crear una hoja de cálculo parecida a un cuadro de diálogo o formulario

separado (ver figura de arriba), pero que este incluido dentro del libro de trabajo

¿Cómo cambiar la apariencia de una hoja de cálculo...?

1. Seleccione del Menú Ventana, la opción de Nueva Ventana.

84


grupo



2. En esta nueva ventana seleccione la hoja de cálculo en la que piensa diseñar el

formulario.

3. Presione la combinación de teclas [ALT] [-] (menos) y seleccione el comando

Restaurar.

4. Ahora que el formulario esta en una hoja separada Ud. necesita crear la apariencia

con la cual desea que aparezca el formulario.

Para cambiar la apariencia de la hoja de manera similar a un papel formulario.

1. Seleccione del menú Herramientas el comando Opciones, para mostrar el cuadro

de diálogo de Opciones.

2. Seleccione la ficha Ver.

3. Seleccione desde las siguientes cajas de verificación en la ventana de opciones el

grupo que afectan la apariencia de sólo la ventana activa.

Caja de verificación Efecto Salto de páginas Al desactivarlo no muestra los saltos de página automático.

Fórmulas Desactivarlo hace que sólo se vean los resultados y las formulas no.

Líneas de división Con esta opción desactivada se dejará de visualizar las líneas de división.

Encabezados de Fila y Columna

Desactivando esta opción se dejarán de visualizar los encabezados de las filas y las columnas.

Símbolos del Esquema Al desactivar esta opción se dejará de visualizar los símbolos que se muestran al trabajar con esquemas.

Valores Cero Es opcional, esto desactiva la visualización de los valores cero.

Barra de desplazamiento Horizontal

Desactive esta para ocultar la barra de desplazamiento horizontal.

Barra de Desplazamiento Vertical

Desactive esta para ocultar la barra de desplazamiento vertical.

Etiquetas de Hojas Desactive esta opción para ocultar las etiquetas con los nombres de las hojas de cálculo.

Añadiendo controles a la hoja de cálculo

Cuando se crea un formulario personalizado mediante una hoja de cálculo o una hoja de

gráfico, pueden agregarse controles que permiten al usuario interactuar con el

formulario. Los controles son objetos gráficos que se insertan en un formulario para

mostrar o introducir datos, llevar a cabo una acción o facilitar la lectura del formulario.

Estos objetos incluyen cuadros de texto, cuadros de lista, botones de opción, botones de

comando, etc. Los controles contienen opciones entre las que puede elegir el usuario o

botones en los que el usuario puede hacer clic para ejecutar macros o secuencias de

comandos Web que automatizan las tareas.

85


grupo



¿Cómo dibujar un control...?

1. Abra la hoja de cálculo a la que desee agregar controles.

2. Asegúrese de que aparece la barra de herramientas Formularios.

3. En la barra de herramientas Formularios, haga clic en el botón del control que desee

agregar.

4. En la hoja de cálculo, arrastre el control hasta que tenga el tamaño que desee.

5. Si agrega un botón, seleccione la macro que desee ejecutar cuando se haga clic en el

botón del cuadro Nombres de macro.

Construyendo Formularios:

Cambiando el formato de los controles

Puede establecer una serie de cambios como el tamaño, la posición, protección y el

control de los diferentes controles que puede agregar en una hoja de cálculo. Para

modificar estas opciones puede cambiar uno o mas de estas propiedades según como se

describe a continuación:

�� Tamaño

Controla el tamaño en escala o con medidas exactas del control seleccionado, también

puede activar la opción de controlar aspecto que hace que los cambios del control se

hagan de manera proporcional.

�� Proteger

Al seleccionar un objeto este puede ser desplazado, redimensionado o cambiado, pero al

activar la protección de la hoja en la que dibujo los controles estos no podrán se

modificados, ya que la opción bloqueado en la ficha proteger esta activa por defecto.

86


grupo



�� Propiedades

Restringe como un control se moverá o redimensionara al cambiar el tamaño de la celda

en la cual esta ubicada. Además puede especificar si desea que el control se imprima o

no.

�� Control

Esta configuración determina el valor por defecto para un control, los limites de datos del

control y cuando el dato será pasado. Las opciones disponibles dependen del control

seleccionado.

Añadiendo una casilla de verificación

Una caja de verificación permite al usuario sólo dos opciones, VERDADERO o FALSO. La

caja de verificación está vinculada a la celda en la que aparecerá el resultado, en esta

aparecerá o verdadero o falso. La caja de verificación cuando está deseleccionada

devuelve el valor FALSO. Ud. puede usar la función SI para producir dos resultados

dependiendo del resultado que arroje el control.

¿Cómo añadir controles de casilla de verificación...?

1. Seleccione la herramienta de Casilla de verificación en la barra de herramientas de

Formulario y dibuje el control sobre la hoja de cálculo.

2. Con el control seleccionado ingrese el título del control.

87


grupo



Luego pulse el botón derecho del mouse sobre el control y seleccione Formato de

control.

3. Seleccione el valor por defecto para el control. Sin activar devuelve como resultado

FALSO, Activado devuelve VERDADERO y mixto el resultado de #NA. Opcionalmente

puede activar Sombreado 3D para añadir un efecto diferente al control.

4. Seleccione la casilla Vincular con la celda e indique haciendo un clic sobre la hoja, la

posición en la que se mostrará el valor resultante.

5. Haga clic en Aceptar.

Cuando Ud. necesite que el usuario necesite que escoja entre dos valores, use el control

de casilla de verificación combinado con la función SI. Use la función SI para convertir el

resultado VERDADERO/FALSO de la celda vinculada en uno de dos resultados. El

resultado devuelto por la función SI puede ser un texto, fecha, fórmula o un número. La

sintaxis de la función SI es como se muestra a continuación:

=SI(Celda_Vinculada;Resultado_Verdadero;Resultado_Falso)

Si la celda vinculada fuera B5, por ejemplo, la siguiente fórmula devuelve no Exonerado

si la casilla esta desactivada y Exonerado cuando esta es activada. Cree su fórmula en

una celda diferente a B5.

=SI(B5;”No Exonerado”;”Exonerado”)

Añadiendo botones de opción para múltiples opciones

Los botones de opción son usados más frecuentemente para seleccionar uno y solamente

uno opción de un grupo de opciones. Los botones de opción son redondos que

usualmente están agrupados. Estos botones son excluyentes, eso quiere decir que al

88


grupo



seleccionar uno de ellos otro se deseleccionará, lo cual indica que sólo puede seleccionar

un solo botone del grupo a la vez.

¿Cómo añadir controles para múltiples opciones...?

1. Dibuje un Cuadro de grupo. Mientras la caja este seleccionada Ingrese el Título para

reemplazar el título por defecto.

2. Seleccione la herramienta de Botón de opción y dibujar este dentro del cuadro de

grupo. Ingrese el título de este mientras esta seleccionado.

3. Pulse el botón derecho sobre el botón de opción y seleccione la opción Formato de

Control y seleccione la ficha Control. Luego seleccione el valor inicial para el botón

de opción: Sin Activar, Activado o Mixto. Recordar que sólo un botón de opción puede

ser seleccionado en un grupo.

4. en la casilla Vincular con la celda, indicar en que celda de la hoja de cálculo se

colocará el resultado arrojado por el grupo de botones de opción.

5. Clic en Aceptar.

6. Para crear otro botón de opción repita esta secuencia a partir del paso número 2. Si

Ud. no necesita crear otro botón de opción haga clic en una celda fuera del grupo.

Los siguientes botones de opción que Ud. cree no necesitan que se les indiquen la celda

de referencia en la casilla Vincular con la celda.

Un grupo de botones de opción usualmente son usados para forzar al usuario a

seleccionar una opción de muchas. Ud. puede usar la función ELEGIR para devolver un

resultado de diferentes resultados. La sintaxis para usar la función ELEGIR se muestra en

la siguiente Línea.

=ELEGIR(Celda_Vinculada;Respuesta1;Respuesta2;Respuesta3;...)

Añadir Listas o listas desplegables para opciones de texto limitadas.

Una lista o lista desplegable restringe al usuario a sólo seleccionar desde una lista de

items. La lista puede ser nombres de productos, Ciudades o lo que Ud. guste.

Restringiendo la selección del usuario, los previene de digitar con un error, ingresando

información no existente o usando datos pasados.

89


grupo



Una lista y una lista desplegable producen el mismo resultado, pero la apariencia de

estas listas es diferente. Una caja de lista muestra múltiples items en la lista. Una lista

desplegable muestra un item a la vez y si desea ver todos los elementos de la lista

(dependiendo del tamaño de la caja) haga clic en la flecha ubicada a la derecha de esta.

¿Cómo añadir una lista control...?

1. En una hoja de cálculo ingrese una lista vertical de items que Ud. quiera que

aparezcan en la lista. Ingrese un item por celda tal como se muestran en el rango

H5:H17 en la figura 9.5.

2. Clic en la herramienta Cuadro de lista o Cuadro combinado y dibujar una lista en la

hoja de cálculo. Si no puede dibujar una lista muy amplia como para mostrar todo el

contenido de un item, cree una lista que muestre por lo menos un gran porcentaje de

cada item. Cree una lista lo suficientemente alta como para ver varios items a la vez.

Cree una lista desplegable lo suficientemente grande como para ver un item.

3. Presione el botón derecho del mouse sobre la lista y seleccione el comando Formato

de control, para mostrar el cuadro de diálogo Formato de Control. Luego seleccione

la ficha Control.

Si Ud. esta trabajando con una lista desplegable, la ficha Control será como el gráfico

que esta a continuación. La ficha control del Cuadro de lista es similar a esta con la única

diferencia que este no trae la opción Líneas de unión verticales.

90


grupo



4. En rango de entrada, seleccione el rango de celdas que contiene los nombres de los

items a mostrar. El rango seleccionado aparecerá en la lista o la lista desplegable.

5. En Vincular con la celda, seleccione la celda que recibirá el resultado de lo

seleccionado en la lista o lista desplegable.

6. Si esta formateando una lista desplegable indique en la casilla Líneas de unión

verticales el número de items que se mostrarán a la vez en la lista cuando esta sea

abierta.

7. Clic en Aceptar.

El resultado de la selección que haga en una lista o lista desplegable es un número que

indica la posición en la lista del item seleccionado. Si Ud. selecciona por ejemplo el item

número tres en la lista o lista desplegable, la celda vinculada contendrá el número 3. En

muchos casos Ud. querrá convertir este número en el item actual en la lista. Para hacer

esto, use la función INDICE. La sintaxis para la función INDICE se muestra a

continuación:

=INDICE(Lista_Items;Celda:_Vinculada;1)

Como ejemplo, la lista de departamentos en el rango de H5:H17 es usado como el rango

de entrada en la casilla de Rango de entrada. La celda vinculada para esta lista es la F4.

Esta celda muestra un valor numérico que corresponde al item seleccionado, en otra

celda Ud. puede mostrar la ciudad seleccionada con la fórmula:

=INDICE(H5:H17;F4;1)

91


grupo



Esta función hace una búsqueda en el rango H5:H17, según la fila especificada en la

celda F4. El item ubicado en esta fila es retornado en la celda en la que se escribió la

fórmula.

Puede usar otra técnica bastante útil, Ud. puede escoger un valor en la lista pero use el

valor correspondiente en la lista para buscar en otra lista, de tal forma que pueda buscar

otros valores correspondientes a la opción seleccionada en la lista, como por ejemplo,

números, precio, población o cualquier otro valor incluido en la lista de búsqueda. Para

hacer esto Ud. necesita una tabla que este compuesta de 2 o más campos: El primer

campo H5:H17 será usado como rango de entrada para el cuadro de lista o lista

desplegable, las siguientes lista se usarán para buscar los valores que desee usar en

determinado momento. En la figura la fórmula retorna la cantidad de habitantes de la

ciudad seleccionada. La fórmula ingresada en la celda F11 se muestra a continuación:

=INDICE(I5:I17;F4;1)

Añadiendo un Control de número para cambiar números rápidamente.

Los controles de número son controles que muestran dos cabezas de flechas. Cada clic

en una cabeza de flecha aumenta o disminuye el monto en la celda vinculada para el

control de número. Manteniendo presionado el botón del mouse sobre un botón del

Control de número produce un cambio continuo del número.

92


grupo



¿Cómo añadir un control de número...?

1. Seleccione la herramienta de Control de número en la barra de herramientas

Formulario.

2. Dibuje el control sobre la hoja de cálculo en la que quiera añadir dicho control,

dibújelo los suficientemente grande como para que se pueda trabajar con el control

sin problemas.

3. Pulse el botón derecho del mouse sobre el control y seleccione el comando Formato

de control.

4. En la casilla de Valor Actual ingrese el valor que quiere muestre la celda vinculada

cuando se abra la hoja de cálculo.

5. En la casilla valor mínimo, ingrese el valor mínimo a obtener. En la casilla valor

máximo ingresar el valor máximo a considerar. Configure la cantidad que cambiará

el control en la casilla Incremento. La casilla cambio de página no es usado para el

control de número.

6. Seleccione la casilla Vincular con la celda y haga clic en la celda en la que desea que

se muestre el resultado arrojado por control de número.


Añadiendo barras de desplazamiento para ingresar números

La barra de desplazamiento permite al usuario escoger un valor dentro de un amplio

rango mientras obtiene una impresión visual de en donde esta ubicado este dentro del

rango. Al igual que otros controles, la salida de la barra de desplazamiento es vinculada

hacia una celda de la hoja de cálculo. El modo de uso de este control es similar al de

Control de número.

¿Cómo añadir un control de número...?

93


grupo



1. Seleccione la herramienta de Barra de desplazamiento en la barra de herramientas

Formulario.

Dibuje el control sobre la hoja de cálculo en la que quiera añadir dicho control, dibújelo

los suficientemente grande como para que se pueda trabajar con el control sin

problemas. Luego pulse el botón derecho del mouse sobre el control y seleccione el

comando Formato de control.

2. En la casilla de Valor Actual ingrese el valor que quiere muestre la celda vinculada

cuando se abra la hoja de cálculo.

3. En la casilla valor mínimo, ingrese el valor mínimo a obtener. En la casilla valor

máximo ingresar el valor máximo a considerar. Configure la cantidad que cambiará

el control en la casilla Incremento. La casilla cambio de página ingrese el monto a

cambiar cuando se haga clic en cualquier parte gris de la barra de desplazamiento.

4. Seleccione la casilla Vincular con la celda y haga clic en la celda en la que desea que

se muestre el resultado arrojado por control de número.


94


grupo



¿Que es una macro?

Una macro son un conjunto de instrucciones que sirven para automatizar procesos.

Refiriéndonos a Excel, supongamos que realizamos frecuentemente la acción de

seleccionar un rango para aplicarle negrita, cambio de fuente y centrado. En lugar de

hacer estas acciones manualmente, se puede elaborar una macro e invocarla para que

ejecute los tres procesos automáticamente.

Objetos, propiedades y métodos.

A la hora de trabajar con macros en Excel, deben tenerse claros ciertos conceptos de lo

que se llama programación orientada a objetos (OOP). No nos extendamos demasiado

sobre la OOP, pero si definamos a continuación los conceptos de Objeto, Propiedades y

Métodos.

�� Objeto.

Cuando en el mundo real nos referimos a objeto significa que hablamos de algo más o

menos abstracto que puede ser cualquier cosa. Si decidimos concretar un poco más

podemos referirnos a objetos coche, objetos silla, objetos casa, etc. En OOP, la

generalización (o definición) de un objeto se llama Clase, así la clase coche seria como la

representante de todos los coches del mundo, mientras que un objeto coche seria un

coche en concreto. De momento, no definiremos ni estudiaremos las clases sino que nos

concentraremos en los objetos, tenga en cuenta pero que cualquier objeto está

definido por una clase.

Cuando decimos que la clase coche representa a todos los coches del mundo significa que

define como es un coche, cualquier coche. Dicho de otra forma y para aproximarnos a la

definición informática, la clase coche define algo que tiene cuatro ruedas, un motor, un

chasis,... entonces, cualquier objeto real de cuatro ruedas, un motor, un chasis,... es un

objeto de la clase coche.

�� Propiedades.

Cualquier objeto tiene características o propiedades como por ejemplo el color, la forma,

peso, medidas, etc. Estas propiedades se definen en la clase y luego se particularizan en

cada objeto. Así, en la clase coche se podrían definir las propiedades Color, Ancho y Largo


– Macros I (Módulos y Procedimientos) –

95


grupo



, luego al definir un objeto concreto como coche ya se particularizarían estas propiedades

a, por ejemplo, Color = Rojo, Ancho = 2 metros y Largo = 3,5 metros.

�� Métodos.

La mayoría de objetos tienen comportamientos o realizan acciones, por ejemplo, una

acción evidente de un objeto coche es el de moverse o lo que es lo mismo, trasladarse de

un punto inicial a un punto final.

Cualquier proceso que implica una acción o pauta de comportamiento por parte de un

objeto se define en su clase para que luego pueda manifestarse en cualquiera de sus

objetos. Así, en la clase coche se definirían en el método mover todos los procesos

necesarios para llevarlo a cabo (los procesos para desplazar de un punto inicial a un punto

final), luego cada objeto de la clase coche simplemente tendría que invocar este método

para trasladarse de un punto inicial a un punto final, cualesquiera que fueran esos puntos.

Repasemos a continuación todos estos conceptos pero ahora desde el punto de vista de

algunos de los objetos que nos encontraremos en Excel como WorkSheet (Objeto hoja

de cálculo) o Range (Objeto casilla o rango de casillas).

Un objeto Range está definido por una clase donde se definen sus propiedades,

recordemos que una propiedad es una característica, modificable o no, de un objeto. Entre

las propiedades de un objeto Range están Value, que contiene el valor de la casilla,

Column y Row que contienen respectivamente la fila y la columna de la casilla, Font que

contiene la fuente de los caracteres que muestra la casilla, etc.

Editor de Visual Basic

El editor de Visual Basic es la aplicación que utilizaremos para construir las macros que

interactuaran junto con los libros de trabajo. A continuación prepararemos un archivo en

el que escribiremos las primeras instrucciones en Visual Basic.

Preparar un archivo nuevo.

Para entrar en el editor de Visual Basic, ejecute los pasos siguientes.

a) Seleccione de la ficha Programador la opción Visual Basic. Se abrirá la ventana

siguiente.

96


grupo



Insertar un nuevo módulo.

Un módulo sirve para agrupar procedimientos y funciones. El procedimiento y la función

son entidades de programación que sirven para agrupar instrucciones de código que

realizan una acción concreta.

Para insertar un módulo active opción del menú Insertar/ Módulo. Se activará una

nueva ventana, si aparece demasiado pequeña, maximícela.

Insertar un procedimiento.

Ya hemos dicho que un procedimiento es un bloque de instrucciones de código que sirven

para llevar a cabo alguna tarea específica. Un procedimiento empieza siempre con la

instrucción Sub Nombre_Procedimiento

Y termina con la instrucción End Sub.

A continuación crearemos un procedimiento para poner el texto "Bienvenido a BS Grupo"

en la casilla A1.

Ejemplo 1:

Sub Primero

Range("A1").Value = "Bienvenido a BS Grupo"

End Sub

Observe el código.

Range("A1").Value=" Bienvenido a BS Grupo "

97


grupo



En esta línea estamos indicando que trabajamos con un objeto Range. Para indicarle que

nos referimos a la casilla A1, encerramos entre paréntesis esta referencia. De este objeto,

indicamos que queremos establecer un nuevo valor para la propiedad Value, observe que

para separar el objeto de su propiedad utilizamos la notación punto.

Recuerde que el conjunto Range es un objeto que pende del objeto WorkSheets, así por

ejemplo el siguiente código haría lo mismo que el anterior:

WorkSheets(1).Range("A1").Value = "Bienvenido a BS Grupo"

Bueno, de hecho no hace lo mismo, en la primera opción, el texto "Bienvenido a BS

Grupo" se pone dentro de la casilla A1 de la hoja activa, mientras que en el segundo es en

la casilla A1 de primera hoja (del conjunto de hojas).

La segunda notación es más larga, pero también más recomendable ya que se especifican

todos los objetos. En muchas ocasiones se pueden omitir algunos objetos precedentes, no

le aconsejamos hacerlo, sus programas perderán claridad y concisión.

Si desea hacer referencia a la hoja activa puede utilizar ActiveSheet, así, el primer

ejemplo lo dejaremos de la manera siguiente:

Sub Primero

ActiveSheet.Range("A1").Value = "Bienvenido a BS Grupo"

End Sub

Si desea poner "Bienvenido a BS Grupo" (o cualquier valor) en la casilla activa, puede

utilizar la propiedad (objeto) Activecell de WorkSheets. Así para poner "Hola" en la

casilla activa de la hoja activa seria:

Sub Primero

ActiveSheet.ActiveCell.Value = "Hola"

End Sub

Para terminar con este primer ejemplo. WorkSheets están dentro del Objeto WorkBooks

(libros de trabajo) y WorkBooks están dentro de Application. Application es el objeto

superior, es el que representa la aplicación Excel. Así, el primer ejemplo, siguiendo toda la

jerarquía de objetos quedaría de la forma siguiente:

Sub Primero

Application.WorkBooks(1).WorkSheets(1).Range("A1").Value = "Hola"

End Sub

98


grupo



Insistiendo con la nomenclatura, Application casi nunca es necesario especificarlo, piense

que todos los objetos penden de este, WorkBooks será necesario implementarlo si en las

macros se trabaja con diferentes libros de trabajo (diferentes archivos), a partir de

WorkSheets, es aconsejable incluirlo en el código, sobre todo si se quiere trabajar con

diferentes hojas, verá, sin embargo, que en muchas ocasiones no se aplica.

Ejecutar un procedimiento o función.

Pruebe ejecutar el primer procedimiento de ejemplo.

1. Sitúe el cursor dentro del procedimiento.

2. Active opción de la barra de menús Ejecutar/ Ejecutar Sub Userform. También

puede hacer clic sobre el botón o pulsar la tecla F5.

Para ejecutar el procedimiento desde la hoja de cálculo.

Debe estar en una hoja, no en el editor de Visual Basic

1. Active opción de la barra de menús Herramientas/ Macro/ Macros. Se despliega una

ventana que muestra una lista donde estás todas las macros incluidas en el libro de

trabajo.

2. Seleccione la macro de la lista y pulse sobre el botón Ejecutar.

Ejemplo:

Desarrollar un programa en Diagrama de flujo y Excel utilizando macros que pida 5

números por teclado que están en las celdas y despliegue la suma total en otra celda y

graficar.

99


grupo



Sub MacroSuma() Dim n1, n2, n3, n4, n5, suma As Integer n1 = Range("B5").Value n2 = Range("B6").Value n3 = Range("B7").Value n4 = Range("B8").Value n5 = Range("B9").Value suma = n1 + n2 + n3 + n3 + n5 Range("B10").Value = suma End Sub

100


grupo



La forma de crear un formulario y luego programarlo es como sigue:

1. Presione las teclas Alt + F11, para entrar al editor de Visual Basic.

2. Activa las siguientes opciones:

• De clic en el Menú Ver y elija la opción Explorador de Proyectos

• De clic en el Menú ver y elija la opción Ventana Propiedades

3. Del Menú Insertar seleccione la opción UserForm. Esto inserta el Formulario que

programaremos con controles. En el Explorador de Proyecto se observara que se

inserto el UserForm.

Nota: También cuando de clic en el Formulario UserForm1 se debe activar el Cuadro de

Herramientas, si no se activa de clic en el Menú Ver y elija la opción Cuadro de

Herramientas.

4. Elija del Cuadro de Herramientas el Control Etiqueta y arrastre dibujando en el

Formulario UserForm1 la etiqueta. Quedara el nombre Label1, después de un clic en

la etiqueta dibujada y podrá modificar el nombre de adentro y pondremos ahí

Nombre. Si por error da doble clic en la etiqueta y lo manda a la pantalla de

programación de la etiqueta, solo de doble clic en UserForm1 que se encuentra en el

Explorador de Proyecto.

5. Elija del Cuadro de Herramientas el control Cuadro de Texto y arrastre dibujando

en el formulario UserForm1 el cuadro de texto a un lado de la etiqueta que dice

Nombre. El cuadro de texto debe de estar vacío y su nombre será Textbox1, el

nombre solo aparecerá en el control.


– Macros UserForms I –

101


grupo



6. Haga los dos pasos anteriores igualmente poniendo Dirección en el Label2 y

Teléfono en el Label3 y también dibújeles su Textbox. Esto quedara así después de

haberlo hecho:

Nota: Si tiene algún problema al dibujar las etiquetas o los cuadros de texto, solo cámbiele el

nombre a la etiqueta o el cuadro de texto en la Ventana Propiedades la opción se llama

(Name). El Error que marque puede ser Nombre Ambiguo, pero si le cambias el Nombre al

control se quitara el error. Puedes ponerle cualquier nombre en lugar de Label1.

Los controles como las Etiquetas y Cuadros de Textos pueden modificárseles algunas

opciones en la Ventana Propiedades. No altere las propiedades si no las conoce.

7. Elija del Cuadro de Herramientas el control Botón de Comando y arrastre

dibujando en el Formulario UserForm1 el Botón, después de un clic en el nombre del

Botón dibujado y podrá modificar el nombre y pondremos ahí Insertar. Si por error

da doble clic en el Botón y lo manda a la pantalla de programación de la etiqueta, solo

de doble clic en UserForm1 que se encuentra en el Explorador de Proyecto.

102


grupo



8. Ahora de doble clic sobre el control Textbox1 para programarlo y después inserte el

siguiente código:

Private Sub TextBox1_Change() Range("A9").Select ActiveCell.FormulaR1C1 = TextBox1 End Sub

Esto indica que vaya a la celda A9 y escriba lo que hay en el Textbox1.

Para volver al Formulario y programar el siguiente Textbox de doble clic en

UserForm1 que se encuentra en el Explorador de Proyecto, o simplemente de clic

en Ver Objeto en el mismo Explorador de Proyecto.


siguiente código:

Private Sub TextBox2_Change() Range("B9").Select ActiveCell.FormulaR1C1 = TextBox2

End Sub

Esto indica que vaya a la celda B9 y escriba lo que hay en el Textbox2.

Para volver al Formulario y programar el siguiente Textbox de doble clic en

UserForm1 que se encuentra en el Explorador de Proyecto, o simplemente de clic

en Ver Objeto en el mismo Explorador de Proyecto.


siguiente código:

Private Sub TextBox3_Change() Range("C9").Select ActiveCell.FormulaR1C1 = TextBox3

End Sub

Esto indica que vaya a C9 y escriba lo que hay en el Textbox3

Para volver al Formulario y programar el Botón de Comando Insertar de doble

clic en UserForm1 que se encuentra en el Explorador de Proyecto, o simplemente

de clic en Ver Objeto en el mismo Explorador de Proyecto.

11. Ahora de doble clic sobre el control Botón de Comando para programarlo y después

inserte el siguiente código:

Private Sub CommandButton1_Click() Rem inserta un renglón Selection.EntireRow.Insert Rem Empty Limpia Los Textbox TextBox1 = Empty TextBox2 = Empty TextBox3 = Empty Rem Textbox1.SetFocus Envía el cursor al Textbox1 TextBox1.SetFocus

End Sub

103


grupo



Nota: El comando Rem es empleado para poner comentarios dentro de la programación, el

comando Empty es empleado para vaciar los Textbox.

12. Ahora presione el botón Ejecutar UserForm que se encuentra en la barra de

herramientas o simplemente la tecla de función F5.

Se activara el Userform1 y todo lo que escriba en los Textbox se escribirá en Excel y

cuando presione el botón Insertar, se insertara un renglón y se vaciaran los Textbox y

después se mostrara el cursor en el Textbox1.

104


grupo



Es de suma importancia saber aplicar Formulas en Macros de Excel, ya que la mayoría

de las hojas de cálculos las involucran, por ejemplo los Inventarios, las Nominas o

cualquier otro tipo de hoja las llevan, es por eso que en la siguiente Fase se muestra

como manejar Formulas en Macros de Excel:

1. Presione las teclas Alt + F11, para entrar al editor de Visual Basic.

2. Activa las siguientes opciones:

• De clic en el Menú Ver y elija la opción Explorador de Proyectos

• De clic en el Menú ver y elija la opción Ventana Propiedades

3. Del Menú Insertar seleccione la opción UserForm. Esto inserta el Formulario que

programaremos con controles. En el Explorador de Proyecto se observara que se

inserto el UserForm.

Ahora crearas un formulario con el siguiente aspecto:



Private Sub CommandButton1_Click() Selection.EntireRow.Insert TextBox1 = Empty TextBox2 = Empty TextBox3 = Empty TextBox1.SetFocus

End Sub


siguiente código:


– Macros UserForms II –

105


grupo



Private Sub TextBox1_Change() Range("A9").Select ActiveCell.FormulaR1C1 = TextBox1

End Sub 6. Ahora de doble clic sobre el control Textbox2 para programarlo y después inserte el

siguiente código:

Private Sub TextBox2_Change() Range("B9").Select ActiveCell.FormulaR1C1 = TextBox2 Rem aquí se crea la Formula TextBox3 = Val(TextBox2) * 365 Rem El Textbox3 guardara el total de la multiplicación del Textbox2 por 365 Rem El Comando Val permite convertir un valor de Texto a un Valor Numérico Rem Esto se debe a que los Textbox no son Numéricos y debemos de Convertirlos

End Sub


siguiente código:


End Sub

Nota: El comando Val es un comando de Visual Basic que te permite convertir un valor de texto

a un valor numérico.

Ahora crearas un siguiente formulario con el siguiente aspecto:



106


grupo




End Sub


siguiente código:


End Sub


siguiente código:


End Sub


siguiente código:


End Sub


siguiente código:

Private Sub TextBox4_Change() Range("D9").Select ActiveCell.FormulaR1C1 = TextBox4 Rem aquí se crea la formula TextBox5 = Val(TextBox2) * Val(TextBox3) + Val(TextBox4) Rem El TextBox5 guardara el total

End Sub


siguiente código:

Private Sub TextBox5_Change() Range("E9").Select ActiveCell.FormulaR1C1 = TextBox5

End Sub

14. Cuando se introduzca el Bonos automáticamente se generara el Sueldo Neto.

107


grupo



i. Diseñe el siguiente formulario:

ii. Ahora de doble clic sobre el control Botón de Comando para programarlo y después



End Sub

iii. Ahora de doble clic sobre el control TextBox1 para programarlo y después inserte el

siguiente código:


End Sub

iv. Ahora de doble clic sobre el control TextBox2 para programarlo y después inserte el

siguiente código:


End Sub

v. Ahora de doble clic sobre el control TextBox3 para programarlo y después inserte el

siguiente código:


– Macros UserForms III –

108


grupo




End Sub

vi. Ahora de doble clic sobre el control TextBox4 para programarlo y después inserte el

siguiente código:

Private Sub TextBox4_Change() Range("D9").Select ActiveCell.FormulaR1C1 = TextBox4 Rem aquí se crea la formula TextBox5 = Val(TextBox2) * Val(TextBox3) + Val(TextBox4) Rem El TextBox5 guardara el total

End Sub

vii. Ahora de doble clic sobre el control TextBox5 para programarlo y después inserte el

siguiente código:

Private Sub TextBox5_Change() Range("E9").Select ActiveCell.FormulaR1C1 = TextBox5

End Sub

viii. Cuando se introduzca el Bonos automáticamente se generara el Sueldo Neto.

109


grupo



1. Cree la siguiente Hoja de calculo llamada Consulta:

2. Cree la siguiente Hoja de calculo llamada Datos:


– Base de Datos con Imágenes ActiveX –

Insertar una imagen (control ActiveX)

Combinar el rango de celdas D6:D11

110


grupo



Nota: Las imágenes serán entregadas por el instructor. No olvide colocar como Propiedad

PictureSizeMode: 1-fmPictureSizeModeStretch.

3. Los pasos a seguir para este ejercicio son:

Hoja Datos:

• Asignar un nombre de rango a la columna A de la hoja Datos. Para ello:

Seleccionar el rango A5:A9, luego seleccionar de la ficha Formulas la opción

Asignar nombre a un rango y colocar como nombre al rango: Apellidos

• En la columna E de la hoja Datos se ingresan valores correlativos: 1, 2, 3, etc.

• Seleccionar la primer celda de la columna E (con valor 1) y asígnele como nombre:

foto1 - Aceptar

• Repetir el paso anterior para todas las celdas de la columna E, siguiendo con foto2,

foto3, etc.

Hoja Consulta:

• Seleccionar la celda D4, ir a la ficha Datos, seleccionar Validación de datos, Lista

y en el campo Origen escribir: = Apellidos

111


grupo



• Se visualizará una lista así:

• Dar el tamaño adecuado a 1 celda para darle el tamaño de la imagen.

• En esa celda (en el ejemplo: D6:D11 vinculada) ingresar esta fórmula:

="foto" & BUSCARV(D4,Datos!$A$5:$E$9,5,FALSO)

• Seleccionar celda combinada, ir a la ficha Formulas y elija Asignar nombre a un

rango, e ingresar como nombre: miFoto

• Seleccionando el control ActiveX de la hoja Consulta, escribir en la barra de

fórmula:

=Fotos

• Con la imagen seleccionada, ir a la ficha Formulas y elija Administrador de

nombres, e ingresar como nombre: Fotos y en el campo 'se refiere a' ingresar:

=INDIRECTO(miFoto) – Aceptar

• Complete Ud. las formulas para las celdas C7,C8,C9 y C10, al final quedará:

112


grupo



113


grupo



Aprenderemos a realizar consultas de selección mediante orígenes ODBC, es decir,

mediante motores de distintos programas de gestión de base de datos.

Como primer ejemplo nos conectaremos con la Base de Datos Universidad (Microsoft

Access) realizada en prácticas anteriores.

1. Crear una conexión ODC, para ello:

• Seleccione del Panel de Control la opción Herramientas Administrativas.

• Seleccione Orígenes de Datos/ODBC:

• Seleccione la pestaña DSN de Sistema, y allí Agregar:

• Aparecerá el cuadro de dialogo Crear nuevo origen de datos, allí seleccione

Driver do Microsoft Access (*.mdb) y luego Finalizar.


– Consultas SQL con ODBC –

114


grupo



• En el cuadro de dialogo Configuración de ODBC Microsoft Access coloque

como nombre del origen de datos: MiOrigen

• Seleccione la Base de Datos Universidad y luego Aceptar:

• En el Administrador de Orígenes de datos se habrá agregado nuestra conexión

MiOrigen:

• Ingrese el nombre de MiOrigen en el archivo creado junto al instructor (Hoja

Inicio):

115


grupo



• Haga clic en el botón Ejecutar Macro:

• Ingrese la siguiente Sentencia SQL como prueba y haga clic en Ejecutar:

• Observe la Hoja Resultados como contiene los registros que cumplen con la

condición ingresada como sentencia query.

2. Como segundo ejemplo nos conectaremos con la Base de Datos PracASP-NET

(Microsoft SQL Server 2005).

• Seleccione la pestaña DSN de Sistema, y allí Agregar.

• Aparecerá el cuadro de dialogo Crear nuevo origen de datos, seleccione SQL

Server:

116


grupo



• Ingrese como Nombre: MiOrigen1 y como Servidor: <El nombre del servidor de

SQL Server> (en mi caso, AMSAVS1\SQLEXPRESS) en el cuadro de dialogo:

• Haga clic en el botón Siguiente:

• Haga clic en el botón Siguiente y complete lo siguiente:

117


grupo



• Haga clic en el botón Siguiente:

• Haga clic en el botón Finalizar:

• Haga clic en el botón Probar Origen de datos… y luego Aceptar

118


grupo



• En el Administrador de Orígenes de datos se habrá agregado nuestra conexión

MiOrigen1:

• Ingrese el nombre de MiOrigen1 en el archivo creado junto al instructor (Hoja

Inicio):

• Pruebe Ejecutar la macro como en el ejercicio 1.

3. Como ultimo ejemplo lo haremos filtrando datos de una Base de Datos creada en una

Hoja específica de Microsoft Excel.

• Seleccione del Panel de Control la opción Herramientas Administrativas.

• Seleccione Orígenes de Datos/ODBC.

• Seleccione la pestaña DSN de Usuario (Excel Files), y allí Agregar:

119


grupo



• Haga clic en el botón Agregar y en el cuadro de dialogo Crear nuevo origen de

datos seleccione Microsoft Excel Driver (*.xls):

• Clic en Finalizar y aparece el cuadro Configuración de ODBC Microsoft Excel,

allí coloque como nombre: MiOrigen2 y seleccione el Libro de Excel

(Agentes.xls):

• Finalmente Aceptar y luego verifique que se ha creado el origen de datos

MiOrigen2.

120


grupo



• Ingrese el nombre de MiOrigen2 en el archivo creado junto al instructor (Hoja

Inicio):

• Ingrese la siguiente Sentencia SQL como prueba y haga clic en Ejecutar:

• Observe la Hoja Resultados como contiene los registros que cumplen con la

condición ingresada como sentencia query.

121


grupo



1. Ingrese al Enterprise Manager y en la Base de Datos BDClub cree la tabla Socios

de acuerdo a la siguiente estructura:

2. Seguidamente ingrese los siguientes registros:

3. Ingrese ahora a la macro SQL-ODBC (Excel) y realice las siguientes consultas:

• Mostrar los Socios cuyos nombres empiecen con la letra [M].

• Mostrar los Socios cuyos nombres terminen con la palabra [Carlos].

• Mostrar los Socios en cuyos nombres se encuentre la secuencia [IA].

• Mostrar los Socios cuyo primer apellido empiece con [Morales] y el segundo

apellido termine con [O]. Renombre esta consulta como [APELLIDO COMPLETO].

(Utilice la clausula AS).


– Query Analyzer mediante SQL Server –

122


grupo



• Mostrar todos los nombres y DNI de los Socios cuyos DNI sea mayor que 26

millones y menor a 28 millones. (Utilice el operador de comparación BETWEEN).

• Mostrar los Socios cuyo nombre comience con la letra [S] o el nombre tenga

exactamente 11 caracteres.

4. Cree una nueva tabla llamada Jugadores de acuerdo a la siguiente estructura:

5. Seguidamente ingrese los siguientes registros:

6. Ingrese ahora a la macro SQL-ODBC (Excel) y realice las siguientes consultas:

• Mostrar todos los apellidos no repetidos de la entidad Socios. (Utilice la palabra

clave DISTINCT).

• Mostrar todos los puestos no repetidos de la entidad Jugadores.

• Obtener todos los nombres de la entidad Socios cuyos apellidos comiencen con

[Morales] o [Rojas]. (Utilice la operación de conjunto UNION).

• Obtener todos los DNI de los que juegan al futbol y, además, están en la lista de

la entidad Socios. (Utilice la operación de conjunto INTERSECT).

• Encontrar los nombres de los Socios que juegan al futbol. (Utilice el conector IN).

123


grupo



1. Crearemos una Macro en Excel que permita enviar datos a una hoja específica de

Excel o a la tabla de una Base de Datos de Access.

2. Cree la Base de Datos Exporta en Microsoft Access.

3. Cree la tabla Datos de acuerdo a la siguiente estructura:

Considere lo siguiente:

• Nombre tamaño del campo: 50

• Sexo tamaño del campo:10

• Dirección tamaño del campo:20

• Edad tamaño del campo: entero largo

4. Diseñe la siguiente interface:

Nota: Las imágenes serán entregadas por el instructor.

La grafica indica claramente el objetivo de este ejercicio, es por ello que esta macro

será construida en aula junto al instructor.


– Exportar datos a Excel y Access –

124

Material de Estudio Excel para Expertos.pdf

Documents

Transcript of Material de Estudio Excel para Expertos.pdf