Capacitación Excel 2003

Ejercicios Actualizados para Excel 2007 y Excel 2010

2012-07-11T16:38:00.000-04:00

Ejercicios funciones varias1
Ejercicio1 funciones consultav para Excel 2010 o buscarv para Excel 2007
Ejercicio2 funciones consultav para Excel 2010 o buscarv para Excel 2007
Ejercicio esquemas y subtotales1
Ejercicio esquemas y subtotales2
Ejercicio esquemas y subtotales3
Ejercicios funciones varias2
Evaluación 1
Evaluación 2
Buscar Objetivo1
Buscar Objetivo2
Buscar Objetivo3
Problema Buscar Objetivo
Archivo Excel Buscar Objetivo
Ejercicio Resuelto Buscar Objetivo
Ejercicio Funciones Estadística
Ejercicio Funciones Base Datos1
Ejercicio Funciones Base Datos2
Ejercicio Practico Funciones Estadísticas (Enunciado)
Ejercicio Practico Funciones Estadísticas (Excel)
Ejercicio Practico Funciones Estadística Resuelto
Ejercicios Funciones Financieras
Ejercicios Funciones Financieras Resuelto
Ejercicio Tablas Dinámicas
Ejercicio T. Dinámica 1
Ejercicio T. Dinámica 2
Ejercicio T. Dinámica 3
Ejercicio T. Dinámica 4
Ejercicio T. Dinámica 5
Ejercicio T. Dinámica 6
Ejercicio T. Dinámica 7
Ejercicio T. Dinámica 8
Ejercicio T. Dinámica 9

Ejercicio 1 Macros
Ejercicio 2 Macros

Ejercicios Tablas Dinámica, subtotales y esquemas

2010-12-27T12:09:00.011-03:00

Ejercicio Nº1 Descargar

Ejercicio Nº2 Descargar

Ejercicio Nº3 Descargar

Ejercicio Nº4 Descargar

Ejercicio Nº5 Descargar

Ejercicio Nº6 Descargar

Ejercicio Nº7 Descargar

Prueba 2-A Descargar

Prueba 2-B Descargar

Prueba 2-C Descargar

Prueba 2-D Descargar

Prueba 2-E Descargar

Prueba 2-F Descargar

Tablas Dinámicas en Excel Capítulo 6

2010-12-20T12:24:00.002-03:00

Son tablas interactivas que permiten resumir y analizar los datos procedentes de listas y tablas existentes.

Para crear una tabla dinámica lo primero es seleccionar completamente la base de datos a consultar

A continuación ir a Datos/informe de Tablas y gráficos dinámicos.

En esta ventana deberán estar seleccionadas las opciones Lista o base de datos de Microsoft Office Excel y la opción Tabla dinámica, una vez que nos aseguramos de esta selección hacemos clic en Siguiente

A continuación aparecerá una pequeña ventana preguntándonos el rango de los datos que queremos en la tabal dinámica pero como ya los hemos seleccionado, el rango ya aparecerá en el recuadro por lo que sólo haremos clic en el botón Siguiente.

Si no seleccionamos la tabla de datos al principio este es le momento para determinar el rango, haciendo clic en el icono Rango y seleccionando los datos, incluyendo los títulos..

A continuación nos preguntará donde queremos situar nuestra tabla dinámica, seleccionamos Hoja de cálculo nueva y hacemos clic en el botón Finalizar

Con esto nuestra pantalla deberá quedar como sigue:

Ahora lo que tenemos que hacer es arrastrar los campos de acuerdo a como queramos consolidar nuestra información. Para este ejemplo queremos saber cuanto se vendió de cada artículo por región por lo que de la Lista de campos de tabla dinámica arrastramos el campo Región a donde dice Coloque los campos de columna aquí.

Posteriormente arrastramos el campo Articulo a donde dice coloque campos de fila aquí y por último lo hacemos también con el campo Total arrastrándolo a donde dice Coloque datos aquí

Con esto, se nos presentará un resumen de ventas por región y por artículo.

Descargar Ejercicio Propuesto 1

Descargar Ejercicio Propuesto 2

Esquemas en Excel Capítulo 5

2010-12-20T12:10:00.000-03:00

Un esquema podríamos definirlo como un resumen preciso que refleja los conceptos más importantes o de mayor trascendencia del documento esquematizado.

Así pues, un esquema puede ser perfectamente un índice de un libro, donde vemos todos los puntos tratados en el libro, también podemos ver como ejemplo de esquema el índice de este curso, el cual contiene los puntos más importantes que se tratan en él y además está estructurado por niveles de profundización sobre un tema en concreto, vamos desplegando el esquema de los puntos contenidos en el tema.

Antes de ponernos a crear un esquema debemos tener en cuenta algunos aspectos.

Debemos asegurarnos de que los datos sean apropiados para crear un esquema. Los datos apropiados para crear un esquema deben tener una jerarquía o disponer de una estructura por niveles, por ejemplo si tenemos datos sobre las precipitaciones ocurridas a lo largo del año en toda España con las precipitaciones mensuales de todas las provincias, estos datos son buenos candidatos a formar un esquema

En una hoja solo podemos incluir un esquema, para tener más de un esquema sobre los mismos datos, debemos copiar los datos a otra hoja.

Para crear esquemas automáticamente debemos preparar lo hoja con un formato adecuado como veremos más adelante.

Existen dos formas de crear un esquema en Excel2003: Manual y Automática.

La mejor opción para crear esquemas es que lo haga Excel automáticamente, puesto que tarda mucho menos tiempo que haciéndolo manualmente.

Existen unos requisitos previos para que Excel2003 pueda crear automáticamente el esquema:

-Las filas sumario deben estar por encima o por debajo de los datos, nunca entremezclados.

-Las columnas sumario deben estar a la derecha o a la izquierda de los datos, nunca entremezclados.

-Si la disposición de los datos no se corresponde con estas características nos veremos obligados a definir el esquema manualmente.

En la imagen podemos ver el ejemplo de datos bien estructurados:

En la imagen anterior podemos ver que ahora aparecen unas líneas en la zona izquierda de las celdas y otra línea sobre las columnas de la tabla.

Además, vemos en la esquina superior izquierda unos números que nos indican cuántos niveles tiene el esquema.

Por columnas podemos ver que existen dos niveles:

- La tabla desplegada por completo

- y la tabla con los datos trimestrales.

Por filas tenemos tres niveles:

- La tabla desplegada completamente

- Por autonomías

- y solo por España.

Para comprimir y expandir el esquema sólo tenemos que hacer clic en los símbolos - y + de cada nivel.

Por ejemplo, en la tabla del ejemplo, si hacemos clic sobre el encima del primer trimestre, comprimiremos ese trimestre, si hacemos lo mismo sobre los niveles de cada autonomía, el esquema se nos quedaría como podemos ver en la imagen.

Descargar Problemas Propuesto 1

Subtotales en Excel Capítulo 4

2010-12-20T11:22:00.007-03:00

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

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

Si los datos no están organizados en una lista, o si sólo necesita un total, puede utilizar Autosuma en lugar de subtotales automáticos.

Cómo se calculan los subtotales

Excel calcula los subtotales con una función de resumen, como Suma o Promedio. Puede mostrar subtotales en una lista con más de un tipo de cálculo a la vez.

Totales generales

Los valores del total general se obtienen a partir de los datos de detalle y no de los valores de las filas de subtotales. Por ejemplo, si se utiliza la función de resumen Promedio, la fila Total general mostrará el promedio de todas las filas de detalle de la lista y no el promedio de los valores de las filas de subtotales.

Actualización automática: Excel actualizará automáticamente los subtotales y el total general cuando se modifiquen los datos de detalle.

Subtotales anidados

Puede insertar subtotales de grupos más pequeños en los grupos de subtotales existentes. En el ejemplo a continuación, los subtotales de cada deporte están en una lista que ya tiene subtotales para cada región.

Subtotales exteriores

Antes de insertar los subtotales anidados, compruebe que ha ordenado la lista por todas las columnas para las que desea los valores subtotales de manera que las filas cuyos subtotales desea estén agrupadas.

Insertar subtotales individuales

Subtotales

Asegúrese de que los datos cuyos subtotales desea calcular están en el siguiente formato: cada columna tiene un rótulo en la primera fila, contiene hechos similares y no hay filas ni columnas en blanco en el rango.lHaga clic en una celda de la columna cuyos subtotales se desea calcular.

En el ejemplo anterior, haría clic en una celda de la columna Deporte, columna B.

Haga clic en Orden ascendente o en Orden descendente.

En el menú Datos, haga clic en Subtotales.

En el cuadro Para cada cambio en, haga clic en la columna cuyos subtotales desee calcular. En el ejemplo anterior, haría clic en la columna Deporte.

En el cuadro Usar función, haga clic en la función de resumen (función de resumen: tipo de cálculo que combina datos de origen en un informe de tabla dinámica o una tabla de consolidación, o cuando se insertan subtotales automáticos en una lista o base de datos. Algunos ejemplos de funciones de resumen son: Sumar, Contar y Promedio.) que desee utilizar para calcular los subtotales.

En el cuadro Agregar subtotal a, active la casilla de verificación de cada columna que contenga valores cuyos subtotales desee calcular. En el ejemplo anterior, seleccionaría la columna Ventas.

Si desea un salto de página automático después de cada subtotal, active la casilla de verificación Salto de página entre grupos.

Si desea que los subtotales aparezcan encima de las filas cuyos subtotales se han calculado en lugar de que aparezcan debajo, desactive la casilla de verificación Resumen debajo de los datos.

Haga clic en Aceptar.

Descargar Problema Propuesto 1

Descargar Problema Propuesto 2

Ejercicios Funciones

2010-12-10T13:39:00.003-03:00

Ejercicio Nº1 Función SI() Descargar

Ejercicio Nº2 Función Buscarv Descargar

Ejercicio Nº3 Función Buscarv Descargar

Prueba Nº1-A Descargar

Prueba Nº1-B Descargar

Prueba Nº1-C Descargar

Prueba Nº1-D Descargar

Funciones en Excel Capítulo 3

2010-12-01T14:58:00.003-03:00

Funciones de búsqueda y referencia.

1.-Función BUSCARV

La función BUSCARV o Buscar verticalmente se utiliza cuando los datos se muestran en columnas. Esta función busca un valor en la columna situada más a la izquierda y lo hace coincidir con datos de una columna especificada en la misma fila. Puede utilizar BUSCARV para buscar datos en una tabla, tanto si está ordenada como si no. En el ejemplo siguiente se utiliza una tabla con datos no ordenados.

BUSCARV(Valor que se desea buscar en la matriz; Matriz de datos donde buscar datos; Columna que se desea obtener dato; Ordenado)
Excel busca en la primera columna de la matriz, definida en el segundo argumento, de forma vertical el valor que ponemos en el primer argumento.

Normalmente esta búsqueda Excel la hace pensando que esta primera columna está ordenada. Si los valores no lo estuvieran tenemos que indicárselo para que pueda encontrar el dato. Si la tabla no está ordenada deberemos escribir Falso en el argumento que hemos llamado Ordenado.

Ejemplo:

Se desea buscar el nombre de un producto en una lista de productos ingresando el código del producto.

=BUSCARV(C1;B7:C18;2)

2.-Función BUSCARH

Busca un valor en la fila superior de una tabla o una matriz de valores y, a continuación, devuelve un valor en la misma columna de una fila especificada en la tabla o matriz. Use BUSCARH cuando los valores de comparación se encuentren en una fila en la parte superior de una tabla de datos y desee encontrar información que se encuentre dentro de un número especificado de filas. Use BUSCARV cuando los valores de comparación se encuentren en una columna a la izquierda o de los datos que desee encontrar.

La H de BUSCARH significa "Horizontal".

Sintaxis

BUSCARH(valor_buscado;matriz_buscar_en;indicador_filas; ordenado)

Valor_buscado es el valor que se busca en la primera fila de la tabla. Valor_buscado puede ser un valor, una referencia o una cadena de texto.

Matriz_buscar_en es una tabla de información en la que se buscan los datos. Utilice una referencia a un rango o el nombre de un rango.

Ejemplo:

Se desea buscar el nombre de un producto en una lista de productos ingresando el código del producto.

=BUSCARH(B5;A2:L3;2)

Ejercicios.

Descargar enunciados de ejercicios

Descargar Excel para resolver ejercicios Nº1

Descargar Excel para resolver ejercicios Nº2

Funciones en Excel Capítulo 2

2010-11-30T10:40:00.001-03:00

Funciones Lógicas.

1.-Función SI()

Es una de las formulas mas potentes de Excel donde nos devuelve un verdadero o falso según corresponda.

Ejemplos:

1)Calcular las comisiones de ventas según las siguientes condiciones:

Ventas mayores a 50.000 se paga el 10% de comisión.
Ventas de 50000 o menores se paga el 2% de comisión.

2)Calcular el 15% de descuento si el cliente paga al contado.

2.-Función SI() anidada.

Excel tiene la posibilidad de anidar las funciones SI(), es decir que en caso de ser necesario, podemos volver a plantear nuevamente un Si(), en caso de que se cumpla o no la condición planteada para el primero y así sucesivamente.
Es posible anidar hasta siete funciones SI(), todo depende de la cantidad posible de salidas a contemplar. Dicho de otra forma un SI() permite obtener dos salidas posibles, una para el caso verdadero y otra para el caso falso; pero si tuviéramos que contemplar tres posibles salidas o resultados, no nos alcanzaría con un SI() y deberíamos utilizar otro en la misma fórmula.

Se tiene la siguiente tabla del valor de las acciones y se desea

tomar la decisión de comprar acciones según la siguiente condición:

Acción cuyo valor es mayor a 5000 precio alto.
Acción cuyo valor es 5000 o menor precio aceptable.
Acción cuyo valor es menor a 2000 precio bajo.

Solución:

Ejercicios Propuestos:

Descargar enunciados de ejercicios

Descargar Excel para resolver ejercicios

Funciones en Excel Capítulo 1

2010-11-29T15:05:00.000-03:00

Para insertar una función debemos pinchar el siguiente botón de la barra de formulas.

Luego buscar la forma que nos acomode para obtener el resultado esperado.

Funciones Matemáticas y Trigonométricas.

1.-Función Entero

Muestra la parte entera de un número decimal. Ejemplo:

entero(8,9) devuelve 8

2.-Función Redondeo

Redondea un número, al número de decimales especificado.Ejemplos:

redondear(1,57;1) Resultado 1,6

redondear(1,57;0) Resultado 2

redondear(1,45;0) Resultado 1

3.-Función Producto

Multiplica los números que recibe como argumentos

producto(número1; número2; ...)

Funciones de fecha.

1.-Función AHORA()

Entrega en una celda la fecha y hora que posee el computador cuando se abre la planilla.

2.-Función HOY()

Entrega en una celda la fecha y hora que posee el computador cuando se

abre la planilla.

Funciones de uso de texto.

1.-Función MAYUSC(texto).

Convierte el texto a mayúsculas.

2.-Función MINUSC(texto).

Convierte el texto a minúsculas.

3.-Función NOMPROPIO(texto).

Convierte el texto a minúsculas.

Convierte a mayúsculas la primera letra de cada palabra de un texto. Ejemplo: nompropio(“este es un TÍTULO”)

Funciones Estadísticas.

1.-Función CONTAR(rango)

Cuenta cuántos datos tipo números hay en un rango de datos.

2.-Función CONTARA(rango)

Cuenta cuántos valores no vacíos hay en un rango de datos.

Funciones Financieras.

1.-Función tasa de interés TASA(nper; pago; va; vf; tipo; estimar)

Devuelve la tasa de interés por período de una anualidad.

La sintaxis de la función TASA tiene los siguientes argumentos:

Núm_per Obligatorio. El número total de períodos de pago en una anualidad.
Pago Obligatorio. El pago efectuado en cada período, que no puede variar durante la vida de la anualidad. Generalmente el argumento pago incluye el capital y el interés, pero no incluye ningún otro arancel o impuesto. Si se omite el argumento pago, deberá incluirse el argumento vf.
Va Obligatorio. El valor actual, es decir, el valor total que tiene actualmente una serie de pagos futuros.
Vf Obligatorio. El valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si el argumento vf se omite, se supone que el valor es 0 (por ejemplo, el valor futuro de un préstamo es 0).
Tipo Opcional. El número 0 ó 1 e indica cuándo vencen los pagos.

Ejemplo:

Calcular la tasa mensual de un préstamo de 800.000 mil pesos a

cuatro años con pagos mensuales de 20.000 mil pesos:

2.-Función valor futuro vf(tasa; nper; pago; va; tipo)

Devuelve el valor futuro de una inversión basándose en pagos periódicos constantes y en una tasa de interés constante.

La sintaxis de la función VF tiene los siguientes argumentos:

Tasa Obligatorio. La tasa de interés por período.
Núm_per Obligatorio. El número total de períodos de pago en una anualidad.
Pago Obligatorio. El pago que se efectúa cada período y que no puede cambiar durante la vigencia de la anualidad. Generalmente, el argumento pago incluye el capital y el interés pero ningún otro arancel o impuesto. Si se omite el argumento pago, se deberá incluir el argumento va.
Va Opcional. El valor actual o el importe total de una serie de pagos futuros. Si se omite el argumento va, se considerará 0 (cero) y se deberá incluir el argumento pago.
Tipo Opcional. El número 0 ó 1. Indica cuándo vencen los pagos. Si se omite el tipo, se considera que es 0.

Ejemplo:

Si ahorramos $350 mensuales durante 3 años en un banco que paga el 18% nominal Anual y deseamos saber cuánto dinero tendremos ahorrado al final de los 3 años.

3.-Función Pago PAGO(tasa;nper;va;vf;tipo)

Calcula el pago de un préstamo basándose en pagos constantes y en una tasa de interés constante.

La sintaxis de la función PAGO tiene los siguientes argumentos:

Tasa Obligatorio. Es el tipo de interés del préstamo.
Nper Obligatorio. Es el número total de pagos del préstamo.
Va Obligatorio. Es el valor actual, o la cantidad total de una serie de futuros pagos. También se conoce como valor bursátil.
Vf Opcional. Es el valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si el argumento vf se omite, se supone que el valor es 0 (es decir, el valor futuro de un préstamo es 0).
Tipo Opcional. Es el número 0 (cero) o 1 e indica cuándo vencen los pagos.

Ejemplo:

Supongamos las siguientes condiciones del préstamo:

Capital: 120.000 Tipo: 4% anual Período: 15 años

Periodicidad: mensual

Problemas Propuestos

Hoja para responder problemas propuestos.

Pruebas de Diagnostico

2010-11-28T17:51:00.000-03:00

Prueba Nº1

Las instrucciones para desarrollar la prueba Nº1 es la siguiente:(Cree una carpeta nueva en el escritorio con su nombre y guarde el archivo)

Calcula los datos que faltan en la hoja siguiendo las siguientes instrucciones: (Utilice celda absoluta o relativas donde sea necesario)

*Unidades vendidas = suma de los tres modelos.

*Ingreso por ventas = Modelo1 * PrecioModelo1 + ...

*Coste de las ventas = Modelo1 * CostoModelo1 + ....

*Margen bruto = Ingreso por ventas – Costo de las ventas.

*Comisión venta = Ingreso por ventas * Comisión Ventas.

*Costos fijos = Ingreso por ventas * Porcentaje Costos fijos.

*Costo total = suma de sus cuatro celdas superiores.

*Beneficio = Margen bruto – Costo total.

*Margen beneficio = Beneficio / Ingreso por ventas.

*Crea 2 gráfico mostrando las ventas trimestrales(tubos, torta)

*Crea 2 gráfico mostrando las ventas por modelo(barras y líneas)

*Crea 1 gráfico mostrando las ventas por modelo(columnas)

*Crea 1 gráfico mostrando los beneficios trimestrales(Conos)

Descargar prueba de diagnostico Nº1

Prueba Nº2

Las instrucciones para desarrollar la prueba Nº2 es la siguiente:(Guarde el archivo en su carpeta)

Genere un estilo con los siguientes datos:

Número categoría número con un decimal.
Alineación del texto horizontal derecho sangría.
Fuente Verdana Nº12 con negrita.

Colocar borde al titulo con línea doble.(Fondo plomo, letras amarillas)
Colocar borde a los datos con línea simple.
Cambie el nombre de la Hoja1 a Promedio Finales.
Calcule el promedio de cada alumno utilizando la formula promedio.
Utilizando formato condicional indicar con rojo las notas menores a 4,0 y con azul las notas iguales o mayores a 4,0.
Colocar clave de lectura y escritura al archivo. La clave es notas2010.
Muestre el promedio más alto y el más bajo utilizando funciones.
En todas las hojas configure el área de impresión para que todo salga en una hoja.
Configurar las hojas del libro tipo horizontal.
Personalizar el encabezado de pagina con el nombre del colegio.(Colegio Particular “Los 33”)
Personalizar el pie de página a la derecha colocando la fecha, hora y nº de página.
Genere un gráfico de cualquier tipo donde muestre los tres mejores promedio.

Descargar prueba de diagnostico Nº2