curso de excel avanzado sesión 1

8/16/2019 Curso de Excel Avanzado Sesión 1

1/24

CURSO DE EXCEL AVANZADO

Ing. Laura Garzón Santodomingo

[email protected]

Junio de 2.010


2/24

Primera sesión

Técnicas avanzadas de Excel

Duración: 3 horas


3/24

Primera sesión – Técnicas avanzadas de Excel

AGENDANombres de celdas y rangos.Validación de datos en celdas.

Esquemas.

Subtotales.Modos de ingresar información a Excel.

Filtros.

Buscar objetivo.

Ing. Laura Garzón Santodomingo - Junio de 2.010


4/24

Nombres de celdas y rangos

Asignar nombres a celdas o rangos:

Método 1: Seleccione celda o rango a nombrar y escriba el nombre enel Cuadro de nombres. Presione Enter.

Método 2: Seleccione celda o rango a nombrar y vaya al menú Insertar-Nombre-Definir…

Método 3: Seleccione celda o rango a nombrar y vaya al menú Insertar-Nombre-Crear…

Aplicación principal:Formular con nombres: a través de la opción “Pegar nombres…”




5/24

Nombres de celdas y rangos – Ejercicio 1

En la hoja Nombres, sección Ejercicio 1 asigne los nombresrespectivos a cada columna:

- Código (método 1)

- Nombre_producto (método 2)

- Precio_unitario (método 3)- Cantidad (método 3)

TODOS: Calcular el valor de la columna Precio_total (sombreado

en gris) formulando a través de nombres.




6/24

Nombres de celdas y rangos – Ejercicio 2

En la hoja Nombres, sección Ejercicio 2, asigne los nombres respectivosa las celdas y rangos:

• Tasa Representativa del Mercado TRM

• Precio acción día anterior

• Precio acción hoy

Calcule el valor de las columnas formulando a través de nombres:

• Precio acción día anterior (pesos colombianos)

• Precio acción hoy (pesos colombianos)

• Crecimiento precio acción (ver fórmula)




7/24

Validación de datos en celdas

La validación de datos permite definir el tipo de datos que se desea introducir enuna celda. De igual forma permite interacción con el usuario, al informarle el tipode información que se permite ingresar y emitir mensajes de alerta cuando elusuario ingresa datos no válidos.

Cómo asignar validaciones: Ver ejemplo

- Menú Datos, opción Validación...- Se despliega la ventana de Validación de datos, con tres pestañas:

Configuración: selecciono el criterio de validación.

Mensaje de entrada: al seleccionar la celda.

Mensaje de error: al ingresar datos no válidos.




8/24

Esquemas

Los esquemas permiten expandir o contraer la apariencia de una hoja decálculo, de forma que la información se pueda ver con más o menosdetalle. Permiten ver o imprimir sólo los resultados principales de unatabla obviando los pormenores de los datos.

Los símbolos necesarios para contraer o expandir los niveles de un

esquema son el más (+) y el menos (-).

Dos formas para crear esquemas: se pueden crear esquemas de formaautomática o manual.

Ver ejemplo: colegios de Bogotá. Resumir datos por localidad.




9/24

Esquemas

Esquema de forma automática:

• Seleccionar las celdas a agrupar, ir al menú Datos, Agrupar y Esquema yactivar la opción Autoesquema.

• A la izquierda y en la parte superior de la hoja de cálculo aparecen unosbotones que permiten gestionar los diferentes niveles del esquema.

(+) y (-).

Esquema de forma manual:

• Seleccionar las filas o columnas contiguas que deberán formar parte delmismo nivel de esquema (no incluir en la selección la fila de resumenasociada a la selección).

• Ir al menú Datos, Agrupar y Esquema y activar la opción Agrupar.




10/24

Esquemas - Ejercicio


1. Genere un esquema de forma automática para agrupar los datos de la tablamostrada (Autoesquema).

2. Elimine el esquema diseñado a través de la opción “Borrar esquema”.

3. Genere un esquema de forma manual para agrupar los datos de la siguienteforma:

– Agrupar únicamente los datos de venta de las oficinas de las regionales Valle yCosta.

– Agrupar únicamente los datos de venta del segundo trimestre.

Recuerde: no seleccione la fila de resumen para generar el esquema de forma manual.



11/24

Subtotales

1. Tener los datos organizados por columnas.2. Dependiendo del criterio escogido para el subtotal, organizar el

listado en orden ascendente o descendente.

3. Seleccionar todos los datos e ir al menú Datos – Subtotales...

4. En la opción “Para cada cambio en” seleccione el criterio por el

cual desea calcular el subtotal.5. En la opción Función seleccione la función que desea calcular

(suma, cuenta, promedio, etc.)

6. En la opción Agregar subtotales a active la casilla de verificaciónde las columnas que tengan datos para calcular los subtotales.

7. Si desea que los subtotales salgan encima de las filas agrupadas yno debajo, desactive la casilla de verificación Resumen debajo delos datos.

8. Clic en Aceptar.




12/24

Subtotales - Ejercicios

1. Calcular subtotales de ventas por localidad.

2. Calcular subtotales de ventas por vendedor.

3. Quitar subtotales: a través de la opción “Quitar todos”.

4. TODOS: Calcular subtotales de ventas y cantidad de artículos vendidos,

por localidad y por vendedor.

Subtotales anidados:

• Comenzar de afuera hacia adentro.

• Organizar el listado según la anidación que se desea.

• Deshabilitar la opción “Reemplazar subtotales actuales”




13/24

Modos de ingresar información a Excel


INFORMACIÓN

POR EL USUARIO

IMPORTAR DATOS DEARCHIVOS EXTERNOS



14/24

Modos de ingresar información a Excel

Archivos planos: en diferentes formatos. Texto, separados por comas,delimitados por tabulaciones, entre otros. Ver ejemplo.

Cómo importar datos de fuentes externas?

•

Menú Datos – opción Obtener datos externos, Importar datos.• Se abre un cuadro de diálogo, donde se busca el archivo que contiene los

datos a importar (Ejemplo_importar.txt)

• Siga las instrucciones del asistente para importar datos: – Tipo de los datos originales: si están delimitados o son de ancho fijo.

–

Formato de columnas: fecha, texto general, etc. – Definir dónde se van a situar los datos.




15/24

Modos de ingresar información a Excel - Ejercicio

Importe los datos contenidos en el archivo de texto llamado NOMINA,utilizando el asistente.

Opciones:

• Delimitado. Separador: tabulación, Punto y coma.

• Formato de columnas: para las que contienen fechas, el formato es: FECHA(DMA). Para el resto de columnas el formato es GENERAL.

• No importar las columnas “segundo apellido” y “segundo nombre”.

• Situar los datos en la hoja Importar_datos, celda B20 (sombreada en verde).




16/24

Filtros

Los filtros facilitan la búsqueda de un subconjunto de datos dentro deun rango. Permite que se muestren únicamente las filas que cumplenel criterio que se especifique para una columna.

Dos modalidades: Autofiltro y Filtro avanzado.

Activación de autofiltro

1. Seleccionar el listado que se desea filtrar (todas las celdas, o sólolos rótulos de columna).

2. Ir al menú Datos – Filtro – opción Autofiltro.

Ejemplo: Ver hoja Autofiltro




17/24

Autofiltro - Ejercicio

Filtre y determine cuántos registros cumplen con lossiguientes criterios:

1. Clientes de ambos sexos con edades comprendidas entre los 19 y los36 años, que vivan en el barrio Cedritos (Cedritos I, Cedritos II, oCedritos Reservado) o Las Margaritas.

2. Clientes de sexo femenino, tarjetahabientes, cuyo consumopromedio mensual sea superior a $300.000 e inferior a $1.500.000.

3. Clientes de sexo masculino, que no sean de estrato 3, cuya marca devehículo sea Renault (cualquier referencia).




18/24

Filtros avanzados

Activación de filtro avanzado

• Deje 4 filas vacías al inicio de la hoja, para establecer el rango de criterios.

• Copie los rótulos de las columnas de la lista por los cuales va a filtrar y péguelos en la

primera fila vacía del rango de criterios.

• En las filas situadas bajo los rótulos de criterios, introduzca las condiciones que desea

buscar. Debe existir, al menos, una fila vacía entre los valores de los criterios y la lista.

• En el menú Datos, selecciona Filtro y luego Filtro avanzado.

• Para filtrar la lista ocultando las filas que no cumplen los criterios, seleccione Filtrar la

lista, sin moverla.

• Para filtrarla, copiando las filas que cumplen los criterios a otra área de la hoja de

cálculo, active Copiar a otro lugar; después, en la casilla Copiar a, seleccione la celda

donde quiere obtener la información extraída de la lista.• En la casilla Rango de criterios, introduzca su referencia, incluidos los rótulos de

criterios.

– Ver ejemplo: hoja Filtro Avanzado.




19/24

Filtro avanzado - Ejercicios

Filtre y determine cuántos registros cumplen con lossiguientes criterios:

1. Clientes cuyo nombre sea Juan, Victoria o Martha, que no tengan vehículo pero quetengan tarjeta de crédito.

2. Clientes cuya edad esté entre algunos de estos rangos: 25 a 30 años, 40 a 45 años, y50 a 55 años; y que adicionalmente tengan vehículo de marcas Renault o Chevrolet.




20/24

Buscar objetivo

Esta función sirve para calcular automáticamente el valor de una celda,para que el resultado final de ciertos cálculos sea un valor deseado.

Ejemplo: Un estudiante ha obtenido las siguientes notas para los dosprimeros tercios en Cálculo I:

Qué nota debe obtener en el tercio 3 para pasar la materia con una notade 2.96?


NOTA PESO

Tercio 1 3,5 30%

Tercio 2 1,6 30%

Tercio 3 40%



21/24

Buscar objetivoPasos:

• En la celda D11 de la hoja Buscar objetivo, ingresar la fórmula que se requiere paracalcular la nota del semestre. No importa que la celda de la nota del tercio 3 estévacía, debe incluirse en la formulación.

(Nota Tercio 1 * Peso) + (Nota Tercio 2 * Peso) + (Nota tercio 3 * Peso)

• Ir al menú Herramientas – opción Buscar objetivo.

• Se despliega un cuadro de diálogo donde se debe incluir la siguiente información:

– Definir la celda: D11 (es la celda que contiene la fórmula de cálculo de notas, yla que se desea ajustar a un valor específico con el que se pasa la materia, esdecir, 3.0)

– Con el valor: 2.96 (es el valor objetivo, el que se está buscando alcanzar).

– Para cambiar la celda: C9 (es la celda que cambia para poder llegar al valordeseado).

Al presionar ENTER, Excel arroja una solución al problema planteado. La nota que se

requiere en el tercio 3 para pasar la materia con 2.96 es 3.6.




22/24

Buscar objetivo – Ejercicio 1

Tres trabajadores en una compañía no están satisfechos con los aumentosporcentuales de salario que han decretado los directivos, por lo cual deciden querenunciarán si sus salarios no llegan a los siguientes valores:

En qué porcentaje debe aumentarse el salario actual de cada uno de lostrabajadores, para llegar al salario deseado?

Recuerde que: Salario actual * (1 + Incremento) = Salario deseado.


Salario deseado

Trabajador 1 1.748.000$





23/24

Buscar objetivo – Ejercicio 2

La empresa FCD Ltda. Tiene dentro de su portafolio de productos ciertareferencia, cuya estructura de costos es la siguiente:

Usted es contratado para que defina la cantidad de unidades que debevender como mínimo para no tener pérdidas (punto de equilibrio).

• Encuentre el punto de equilibrio utilizando la herramienta “Buscarobjetivo”.

• Encuentre la cantidad que debe venderse para alcanzar una utilidad de$1.000.000


Precio de venta por unidad 20.000$

Costo variable por unidad 10.000$

Costo fijo por mes 10.000.000$

EGRESOS

INGRESOS


ó é d d l


24/24

GRACIAS POR SU ATENCIÓN



curso de excel avanzado sesión 1

Documents