Macros y formulas en excel, Ejercicios de Informática General

¡Descarga Macros y formulas en excel y más Ejercicios en PDF de Informática General solo en Docsity!

Excel Avanzado

Centro de Cómputo e Informática

Ing. Lenin Huayta Flores

2.1.1 Esquemas Automáticos

1. Creamos la tabla siguiente:
2. Seleccione todo el rango de la tabla, incluyendo las columnas y filas de totales.
3. Vaya a la pestaña DATOS , ubique el icono Agrupar , de un clic en la flecha hacia abajo y seleccione Autoesquema.

Nota: Vera que Excel selecciona automáticamente todas las filas seleccionadas y aplica la agrupación correspondiente.

2.1.2 Esquemas manuales

1. Realice la siguiente tabla en Excel:
2. Seleccione un rango de filas como por ejemplo Melgar - Puno.
3. Vaya a la pestaña de DATOS , y busque el icono Agrupar. De un clic en Filas y el botón Aceptar.

Nota: Agrupar manualmente significa que usted deberá seleccionar que filas son las que desea esquematizar. Sin embargo si desea agrupar mediante un dato, estas filas deben estar contiguas; si acaso tiene filas dispersadas por toda la tabla, es recomendable hacer una Ordenación primero por la columna que vaya a agrupar para poder realizar el esquema.

Centro de Cómputo e Informática

Ing. Lenin Huayta Flores

2. Casi todas las funciones se construyen así: CONTAR(B2:B11)
3. Realice las funciones indicadas siguiendo el mismo ejemplo.

Algunas funciones estadísticas

1. =CONTAR(Rango de casillas). Cuenta la cantidad de casillas numéricas que hay en el rango.
2. =CONTARA(Rango de casillas). Cuenta la cantidad de casillas alfanuméricas del rango indicado.
3. =MODA(Rango de casillas). Devuelve el valor más común del rango.
4. =PROMEDIO(Rango de casillas). Devuelve la media de los valores del rango de casillas indicado.

Algunas Funciones de bases de datos.

1. =BDCONTAR(Rango o nombre de la tabla;Número de columna;Criterio). Cuenta el número de casillas de la columna Número de columna de tabla que cumplen la condición de criterio. Rango o Nombre de la tabla= Rango de casillas o nombre de la tabla. Número columna= Columna donde se encuentran los datos sobre los que se realiza la operación. Criterio= Rango donde se encuentra el criterio,(previamente indicado en forma de tabla).
2. =BDSUMAR(Rango o nombre de tabla;Número de columna;Criterio). Suma las casillas numéricas de la columna Número de Columna de tabla, que cumplan la condición del criterio.

Centro de Cómputo e Informática

Ing. Lenin Huayta Flores

3. =BDPROMEDIO(Rango o nombre de tabla;Número Columna;Criterio). Calcula la media de los valores de la columna Número columna de tabla, que cumplan el criterio.
4. =BDMAX(Rango o nombre de tabla;Número Columna;Criterio). Devuelve el valor máximo de la columna Número columna indicada de tabla, que cumpla la condición del criterio.
5. =BDMIN(Rango o nombre de tabla;Número Columna;Criterio). Devuelve el valor mínimo de la columna Número columna de tabla, que cumpla la condición del criterio. Criterio , son siempre como mínimo dos casillas, una que es el nombre de la columna y otra que es la condición que se ha de cumplir. Número Columna es la posición que ocupa una determinada columna dentro de la tabla. Por ejemplo en ejercicio que se verá posteriormente, Curso ocupa la columna cuatro.

2.4.1 Buscar

1. Realice la siguiente tabla y cambie el nombre la de hoja a Stock :
2. A continuación inserte una nueva hoja y realice la siguiente tabla:

Centro de Cómputo e Informática

Ing. Lenin Huayta Flores

2.4.2 BuscarV Aprenderá a usar las funciones de búsqueda con que cuenta Excel.

1. Realice la siguiente tabla y renombre la hoja de Excel como Libros :
2. En una segunda hoja de Excel, realice la siguiente tabla:

Centro de Cómputo e Informática

Ing. Lenin Huayta Flores

3. Lo que vamos a buscar es el precio de los libros que aparecen en esta segunda hoja, por lo que la formula deberá ir en la celda PRECIO de cada fila.
4. La función a usar es BUSCARV (en inglés VLOOKUP), la formula queda así. , donde A2, es el valor buscado – en este caso, la Clave del libro-, Libros!$A$2:$D$11 , es el rango de toda la tabla que está en la Libros, que se renombro como Libros ; y 4, es la columna – Precio - que tiene el valor que quiero que me regrese.
5. Lo mismo tenemos que hacer con el resto de la tabla.

2.5 Escenarios Aprenderá a crear escenarios para responder la pregunta ¿Qué pasa Si? Los escenarios muestran diferentes situaciones sobre una misma base de información.

1. Estamos planeando nuestras vacaciones y tenemos un presupuesto de 9500 soles. Vamos a seleccionar 3 distintos destinos para saber cual nos conviene mejor.
2. Comenzamos con el primer destino. Realice la siguiente tabla:
3. Ahora, nos vamos a la pestaña DATOS , y ubicamos el icono ANALISIS DE HIPOTESIS
4. Seleccionamos la opción ADMINISTRACION DE ESCENARIOS y aparecerá una caja de dialogo con un cuadro de lista que contendrá los distintos escenarios. (Al principio aparecerá vacía). Damos un clic en AGREGAR
5. Aparece otro cuadro de dialogo AGREGAR ESCENARIO , escribimos el nombre de escenario como DESTINO.
6. Después, aparece una caja indicando las celdas cambiantes, seleccionaremos únicamente las celdas de la columna C, incluyendo su título (para identificarlo). Ejemplo: C1:C
7. Y damos ACEPTAR. Nuestro primer escenario ha sido creado.
8. Ahora modificaremos la columna C, cambiando los valores:
9. Una vez terminado, repetimos los pasos 3 al 7. Poniendo como nombre de escenario DESTINO.
10. Por último, volvamos a cambiar los valores de la columna C, con esta información:

Centro de Cómputo e Informática

Ing. Lenin Huayta Flores

La idea es que la fórmula que estamos usando, tome diversos valores y no del resultado. ¿Cuánto debemos pagar modificando la tasa de interés y la cantidad de meses a pagar, por ejemplo a 6, 12 y 24 meses?

3. Seleccionar el rango de la tabla e ir a la pestaña DATOS. Seleccionar la flechita del icono ANALISIS DE HIPOTESOS , para desplegar el menú y seleccionar TABLA DE DATOS. Introduce la celda de entrada para la fila. En este caso es el número de meses para pagar y está en B Introduce la celda de entrada para la columna. Que es la tasa de interés anual está en B.
4. Aplica y ve los resultados.

3.3 Función Si Tenemos la siguiente relación de alumnos con su respectiva calificación final. La condición es: Si la calificación es mayor a 10.5 debe decir APROBADO , si no, debe decir DESAPROBADO.

La fórmula es:

¿Cómo modificas para que el primer alumno no salga desaprobado?

Centro de Cómputo e Informática

Ing. Lenin Huayta Flores

3.4.1 Función Si anidada

Tenemos un listado de alumnos y sus calificaciones finales, las condiciones para obtener una beca son:

1. Si la calificación final está entre 19.5 y 10, obtiene un 80% de beca
2. Si la calificación final está entre 18.5 y 19.4, obtiene un 40% de beca
3. Si la calificación final está entre 18.0 y 18.4, obtiene un 25% de beca
4. Si la calificación final es menor a 17.9, no se otorga beca.

La sintaxis es la siguiente:

3.4.1 Sumar Si Otra función de condición es la función SUMAR.SI() que permite hacer una suma condicional.

La función es: = SUMAR.SI( rango, criterio, rango_suma) Ejemplo:

3.5 Tablas Dinámicas Los informes de tabla dinámica permiten presentar desde distintos puntos de vista una misma información, usando para ello funciones como la suma o el promedio.

Tenemos la siguiente tabla de datos:

Centro de Cómputo e Informática

Ing. Lenin Huayta Flores

4. Vamos a Campos de tabla dinámica , Fecha pedido / Filtros de fecha / Este año

3.6 Gráficos Dinámicos

Es la misma idea de las tablas dinámicas pero con un gráfico. Se recomienda que para que los gráficos sean fáciles de entender, la tabla dinámica no debe tener más de dos campos de filas y dos de columnas. Realiza una gráfica dinámica del listado de alumnos de la práctica anterior. Debe quedar así:

Centro de Cómputo e Informática

Ing. Lenin Huayta Flores

1. Ilumina el rango de datos
2. Ve a la pestaña INSERTAR, ubica el icono de TABLA DINAMICA, aplicas las opciones según la ventana de dialogo Campos de Gráfico Dinámico.
3. Das un clic en INSERTAR / GRAFICO DINAMICO.
4. Cambie seleccionando el botón Lugar de Nacimiento.

3.7 Añadir Imágenes a una Gráfica Usaremos la opción efectos de Relleno dentro de las gráficas. El procedimiento es el mismo de crear una gráfica, la diferencia se encuentra en la pestaña TRAMAS en el botón efectos de relleno.

Insertamos un gráfico estadístico y vayamos a Formato de serie de datos / Opciones de Serie / Relleno / Relleno de trama y seleccionamos una trama.

4.1 Tablas Dinámicas 2 Personal de la Empresa Realizamos la siguiente tabla

Centro de Cómputo e Informática

Ing. Lenin Huayta Flores

Realice una consulta para cada columna.

4.3 Filtros Avanzados Abrir el Archivo Excel Avanzado 3 Hoja 4.

Y Realice Filtros para:

1. Realice un filtro avanzado para mostrar los pacientes del SIS que sean del C.S. Vallecito.
2. Liste los pacientes de sexo Femenino del C.S. Laraqueri mayor igual a 18 años de edad.
3. Obtenga los pacientes del C.S. Chejoña, con fecha de afiliación del año 2013.
4. Liste los pacientes cuyo número de identificación N° AFILIACV sea mayor o igual a 76484044 y cuyo nombre empiece con L o M.
5. Liste los pacientes que no tienen fecha de baja.

Centro de Cómputo e Informática

Ing. Lenin Huayta Flores

6. Liste los pacientes que sean mayores a 90 años de edad y sean del C.S. Simón Bolívar.
7. Liste los pacientes que no tengan registrados su DNI y sean del C.S. Puno y sean mayores de 65 años de edad ordenados por Apellidos.

4.4 Tablas Dinámicas 4 Cursos CCI Sea la tabla:

Utilizando una tabla dinámica, mostrar la cantidad de cursos que ha realizado cada alumno en los diferentes períodos.

5.1 Macros: Formato de Fuente Nos ubicamos en la primera celda de la hoja 5.1 y vamos a Vista / MaĐros / Graďar MaĐro… , Dar el Nombre a la macro como FormatoFuente , crear un atajo de teclas C. Y hacemos clic en Aceptar.

Centro de Cómputo e Informática

Ing. Lenin Huayta Flores

Y Aceptar.

Ejemplo: 20 → Veinte Nos ubicamos en la Hoja 5.2 y vamos a Microsoft Visual Basic para Aplicaciones pulsando la combinación de teclas ALT + F11. Luego creamos un Módulo, haciendo clic derecho en VBAProject ;ExĐel Avanzado 4 …Ϳ y vamos a Insertar / Módulo, tal como se muestra en la figura siguiente:

En Módulo ingrese el código siguiente:

Function NumLetras(tyCantidad As Currency) As String Dim lyCantidad As Currency, lyCentavos As Currency, lnDigito As Byte, lnPrimerDigito As Byte, lnSegundoDigito As Byte, lnTercerDigito As Byte, lcBloque As String, lnNumeroBloques As Byte, lnBloqueCero Dim laUnidades As Variant, laDecenas As Variant, laCentenas As Variant, I As Variant 'Si esta como Option Explicit tyCantidad = Round(tyCantidad, 2) lyCantidad = Int(tyCantidad) lyCentavos = (tyCantidad - lyCantidad) * 100 laUnidades = Array("UNO", "DOS", "TRES", "CUATRO", "CINCO", "SEIS", "SIETE", "OCHO", "NUEVE", "DIEZ", "ONCE", "DOCE", "TRECE", "CATORCE", "QUINCE", "DIECISEIS", "DIECISIETE", "DIECIOCHO", "DIECINUEVE", "VEINTE", "VEINTIUNO", "VEINTIDOS", "VEINTITRES", "VEINTICUATRO", "VEINTICINCO", "VEINTISEIS", "VEINTISIETE", "VEINTIOCHO", "VEINTINUEVE")

Centro de Cómputo e Informática

Ing. Lenin Huayta Flores

laDecenas = Array("DIEZ", "VEINTE", "TREINTA", "CUARENTA", "CINCUENTA", "SESENTA", "SETENTA", "OCHENTA", "NOVENTA") laCentenas = Array("CIENTO", "DOSCIENTOS", "TRESCIENTOS", "CUATROCIENTOS", "QUINIENTOS", "SEISCIENTOS", "SETECIENTOS", "OCHOCIENTOS", "NOVECIENTOS") lnNumeroBloques = 1 Do lnPrimerDigito = 0 lnSegundoDigito = 0 lnTercerDigito = 0 lcBloque = "" lnBloqueCero = 0 For I = 1 To 3 lnDigito = lyCantidad Mod 10 If lnDigito <> 0 Then Select Case I Case 1 lcBloque = " " & laUnidades(lnDigito - 1) lnPrimerDigito = lnDigito Case 2 If lnDigito <= 2 Then lcBloque = " " & laUnidades((lnDigito * 10) + lnPrimerDigito - 1) Else lcBloque = " " & laDecenas(lnDigito - 1) & IIf(lnPrimerDigito <> 0, " Y", Null) & lcBloque End If lnSegundoDigito = lnDigito Case 3 lcBloque = " " & IIf(lnDigito = 1 And lnPrimerDigito = 0 And lnSegundoDigito = 0, "CIEN", laCentenas(lnDigito - 1)) & lcBloque lnTercerDigito = lnDigito End Select Else lnBloqueCero = lnBloqueCero + 1 End If lyCantidad = Int(lyCantidad / 10) If lyCantidad = 0 Then Exit For End If Next I Select Case lnNumeroBloques Case 1 NumLetras = lcBloque Case 2 NumLetras = lcBloque & IIf(lnBloqueCero = 3, Null, " MIL" & NumLetras) Case 3 NumLetras = lcBloque & IIf(lnPrimerDigito = 1 And lnSegundoDigito = 0 And lnTercerDigito = 0, " MILLON", " MILLONES" & NumLetras) End Select