PDF de programación - Excel avanzado

Excel Avanzado







UNIVERSIDAD NACIONAL DEL ALTIPLANO



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.



2





UNIVERSIDAD NACIONAL DEL ALTIPLANO



Centro de Cómputo e Informática

Ing. Lenin Huayta Flores



2.2

Subtotales

1. Realice la siguiente tabla en Excel:


2. A continuación, seleccione todo el rango de la tabla



3. Ordenar de A a Z
4. Vaya a la pestaña DATOS, ubique el icono de SUBTOTAL. Haga clic ahí.
5. Aparecerá una caja de dialogo que le pregunta por algunas opciones:

, se realiza un ordenado ascendente mediante la columna Curso

a. PARA CADA CAMBIO EN. Esto le indica la agrupación según el cambio de dato en una fila. Si

seleccionamos Curso, agrupara por planteles.

b. USAR FUNCION. Aquí le indicamos a Excel que tipo de subtotal queremos: CUENTA, SUMA,

PROMEDIO, MAXIMO, MINIMO, PRODUCTO, seleccionamos Cuenta

c. AGREGAR SUBTOTAL A. Aquí indicamos en que columna queremos que aparezca el subtotal.

Marque Alumno.
d. Clic en ACEPTAR.

6. Ahora Excel aplicará los cambios y mostrará la tabla con subtotales insertando filas según los cursos, y

agrupándolos como esquema.


2.3

Funciones

1. Realice la siguiente tabla:



3

UNIVERSIDAD NACIONAL DEL ALTIPLANO



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.



4





UNIVERSIDAD NACIONAL DEL ALTIPLANO



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:



5

UNIVERSIDAD NACIONAL DEL ALTIPLANO



Centro de Cómputo e Informática

Ing. Lenin Huayta Flores



3. Ubíquese en la posición B5, y vaya a la Barra de fórmulas e ingrese:



para la Unidad de Medida, Ingrese en C5:

haga lo mismo para el Precio Unitario en E5:

4. Realice la multiplicación condicional en Total S/. posición F5:



5. Para el Descuento en F13 realice:
6. Realice las demás operaciones de tal manera que el resultado me muestre de esta forma:



,

,



6





UNIVERSIDAD NACIONAL DEL ALTIPLANO



Centro de Cómputo e Informática

Ing. Lenin Huayta Flores



BuscarV

2.4.2
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:



7





UNIVERSIDAD NACIONAL DEL ALTIPLANO



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í.

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.

, donde A2, es el valor buscado –en este caso, la Clave del libro-,

5. Lo mismo tenemos que hacer con el resto de la tabla.

Escenarios


2.5
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 DESTINO1.
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:C7

7. Y damos ACEPTAR. Nuestro primer escenario ha sido creado.
8. Ahora modificaremos la columna C, cambiando los valores:

9.
10.

Una vez terminado, repetimos los pasos 3 al 7. Poniendo como nombre de escenario DESTINO2.
Por último, volvamos a cambiar los valores de la columna C, con esta información:



8





UNIVERSIDAD NACIONAL DEL ALTIPLANO



Centro de Cómputo e Informática

Ing. Lenin Huayta Flores



Y volvemos a repetir los pasos del 3 al 7 poniendo como nombre de escenario DESTINO3.
Ahora, en nuestra caja de dialogo de ADMINISTRACION DE ESCENARIO, tenemos 3 destinos. En la parte
inferior de esta ventana, tenemos un botón que dice MOSTRAR. Si seleccionamos DESTINO1 y luego
pulsamos MOSTRAR, veremos los valores que introducimos al inicio.
De esta manera, podemos interpretar la pregunta: ¿Qué pasa si voy de vacaciones al DESTINO1? ¿Qué
pasa si voy de vacaciones al DESTINO2?

11.
12.

13.



3.1

Calculando el IGV

1. Sea la tabla siguiente:



2. El problema es que sabemos que el valor Total de la mercancía es de 118 nuevos soles CON IGV INCLUIDO.

Dicho de otra manera, sabemos que el total es de 118 y que el IGV es el 18%. Nos hace saber cuál es el Precio
de la mercancía.

3. Y que sucede si tengo solamente el Precio de la mercancía, como hago para sacar el Total.

Tabla de Datos

1. Elaboramos una tabla y utilizando la función Pago() en Cuota Mensual B5, capturamos la siguiente

información:



2. Establecer las filas y columnas de nuestra tabla.



9



3.2







UNIVERSIDAD NACIONAL DEL ALTIPLANO



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 B2
Introduce la celda de entrada para la columna. Que es la tasa de interés anual está en B1.



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?



10





UNIVERSIDAD NACIONAL DEL ALTIPLANO



Centro de Cómputo e Informática

Ing. Lenin Huayta Flores



3.4.1 Funci