CURSO EXCEL AVANZADO 2013

UNIVERSIDAD NACIONAL DE INGENIERIA

DIRECCION DE POSGRADO

CURSO EXCEL AVANZADO 2013

PARTE 1

Ing. Reynaldo Valencia

1. SELECCIONAR LA TABLA VENTAS RAPIDAMENTE

Nos ubicamos en la hoja Ventas, para seleccionar la tabla hacemos clic en cualquier celda de la

tabla y presionamos la combinación de teclas CTRL + SHIFT + BARRA ESPACIADORA, o también

CTRL + * (asterisco) de esta manera podemos seleccionar toda la tabla.

2. ACTIVAR EL FILTRO

Para activar el filtro de manera rápida seleccionar cualquier celda de la tabla y presionamos la

combinación de teclas CTRL + SHIFT + L.

3. FILTRAR LA TABLA POR PRODUCTO

Pasa utilizar el filtro y filtrar por la columna producto, seleccionamos la lista desplegable de la

columna Producto y seleccionamos Camisas.

Se mostrara un embudo en el icono de la lista desplegable de la columna que estamos filtrando y

los números correspondientes a las filas visibles se mostraran en color azul.

Para volver a mostrar todos los registros hacemos clic en el icono del embudo luego

hacemos clic en la casilla de verificación que dice (Seleccionar todo). Se mostraran todos

los registros.

Para desactivar el filtro, hacemos clic sobre cualquier celda de la tabla y presionamos la

combinación de teclas CTRL + SHIFT + L

4. CONVERTIR TABLA NORMAL A TABLA DE EXCEL

Para convertir la tabla normal a tabla de Excel hacemos clic en cualquier celda dentro de la tabla y

presionamos la combinación de teclas CTRL + T. Excel nos mostrara un menú contextual que nos

preguntara donde están los datos de la tabla y que si la tabla contiene encabezados. Solamente

presionaremos el botón Aceptar para continuar.

Excel nos mostrara la tabla con el formato de bandas alternantes celestes con blanco y con el filtro

activado de manera predeterminada.

Si quisiéramos agregar una columna adicional a la tabla, esto se puede hacer de manera muy fácil.

Por ejemplo, escribimos en la fila 1 “Pronóstico” como título del encabezado, Excel detecta

automáticamente que estamos agregando otra columna y adiciona la columna a la tabla, esto lo

podemos ver porque Excel le aplica el formato de bandas a la nueva columna de manera

automática.

Lo mismo sucede si nosotros queremos agregar un nuevo registro al final de la tabla, después de

presionamos ENTER en la fila inmediatamente después de la tabla Excel copia el formato en

bandas a cada nuevo registro que nosotros vayamos agregando de forma automática.

5. CAMBIAR SELECCIÓN DEL RANGO DE REGISTROS INCLUIDOS EN LA TABLA DE EXCEL

Para incluir o excluir filas o columnas del rango seleccionado por la tabla de Excel, solo

arrastramos el icono verde de la esquina inferior derecha de la tabla hacia arriba, abajo, izquierda

o derecha para agregar o eliminar registros de la tabla de Excel.

6. AGREGAR FILA DE TOTALES

Para agregar la fila de totales, hacemos clic en cualquier parte de la tabla, y luego vamos al menú

contextual de HERRAMIENTAS TABLAS – DISEÑO. Hacemos clic en DISEÑO, en el grupo “Opciones

de estilo de tabla” hacemos clic en la casilla de verificación que dice “Fila de totales”.

Una vez que hagamos esto se mostrara automáticamente en la parte inferior de la tabla el total de

la columna más a la izquierda de la tabla, en este caso en la última fila de la columna CMV, y el

título “Total” en la última fila en la primera columna, en nuestro caso la columna “Ciudad”

Si hacemos clic en la celda donde se muestra el total se mostrara una lista desplegable donde

podremos seleccionar todas las funciones disponibles con los cálculos que podemos hacer sobre la

celda donde estamos ubicados. Entre las funciones disponibles están: Promedio, Cuenta, Contar

números, Max, Min, Suma, Desvest, Var. Ojo: dependiendo del tipo de datos que tenga en la

columna en que se ubique, solo podrá utilizar ciertas funciones. Por ejemplo, si la columna a la

que le quiere aplicar los totales es no numérica, por ejemplo la columna Cliente, solo podrá aplicar

la función cuenta, al querer aplicar las otras funciones, se mostrara el valor cero “0” o un mensaje

de error: #DIV/0!.

La lista desplegable con todas las funciones de suma está disponible para cada una de las

columnas de la tabla.

Para desactivar la fila de totales, solo vamos a la pestaña DISEÑO del menú contextual de la tabla,

y deseleccionamos la casilla de verificación que dice “Fila de totales”.

7. USAR REFERENCIAS ESTRUCTURADAS DENTRO DE LA TABLA DE EXCEL

Para realizar cálculos con una tabla de Excel, usaremos una sintaxis que se llama REFERENCIAS

ESTRUCTURADAS.

Si nos colocamos en la celda H2, presionamos el signo = para entrar en modo de edición de

formula y luego nos movemos hacia la izquierda hasta llegar a la columna “Ventas” observamos

que Excel muestra una referencia diferente a la sintaxis normal de Columna y Fila (Ejemplo E2 para

referirnos a la celda en la columna E y la fila 2). Lo que se mostrara en la celda será =[@Ventas]

con lo que Excel ahora hará referencia al valor que se encuentra en la fila adyacente

correspondiente a la columna Ventas.

Supondremos que para el próximo año incrementaremos las ventas en 25% y queremos hacer una

columna de pronóstico para este cálculo Ingresaríamos la formula =[@Ventas]*1.25. Una vez

hecho esto Excel copia la fórmula de manera automática a las otras celdas de la columna.

Existen también identificadores especiales para hacer referencia a determinadas áreas de la tabla:

#Todo = Hace referencia a toda la tabla

#Encabezados = Hace referencia a la fila de encabezados

#Datos = Se refiere a los datos en la tabla sin incluir filas de encabezados y Totales.

#Totales = hace referencia a la fila de totales (si esta estuviera activada)

@ = Junto con el nombre de la columna hacen referencia a la celda adyacente correspondiente a

la columna especificada.

Para utilizar estos identificadores debemos empezar la sintaxis con el nombre de la tabla. Si es la

primera tabla que crea en el documento, por defecto el nombre será tabla1.

Nota: el nombre de la tabla lo puede ver en la pestaña Diseño

A continuación detallamos algunos ejemplos donde podemos usar referencias estructuradas.

a.

b.

c.

d.

=[@Ventas]/[@Cantidad] para calcular las ventas promedio en la tabla ventas

=Tabla1[Ventas]*1.05 o =[Ventas1]*1.05 para incrementar en 5% las ventas.

=Tabla1[[#Encabezados],[Cliente]] muestra el encabezado de la Columna Cliente.

=Tabla1[[#Totales],[CMV]] muestra el total de la columna CMV, si estuviera activado, sino

muestra error.

e. =Tabla1[@Cliente] muestra el cliente que se encuentra en la columna adyacente.

8. OTRAS MANERAS DE CREAR TABLAS DE EXCEL

Ademas de la combinación de teclas CTRL + T, también podemos crear una tabla de Excel de las

siguientes formas:

i.

Desde el menú INICIO, en el grupo Estilos, hacemos clic en el botón “Dar formato

como tabla” y hacemos clic en cualquiera de los estilos.

ii.

Seleccionamos toda la tabla, después de seleccionada en la parte inferior derecha

hacemos en el icono de Analisis Rapido, hacemos clic en el menú tabla, y luego

hacemos clic en el botón Tabla.

iii.

En el menu INSERTAR, hacemos clic en el boton “Tabla”

9. COMO CAMBIAR EL NOMBRE A LA TABLA DE EXCEL

Para cambiar el nombre de una tabla de Excel, hacemos clic sobre cualquier celda de la tabla,

luego hacemos clic en el menu contextual DISEÑO de la barra de herramientas, y en la parte

derecha, en el grupo propiedades cambiamos el nombre en el cuadro de texto que dice Tabla1,

escribimos “Ventas” y presionamos ENTER.

Ahora para escribir cualquier formula estructurada debemos hacer referencia a la tabla Ventas en

vez de a Tabla1. Ejemplo: =Ventas[@Cantidad] para hacer referencia a la celda adyacente en la

columna Cantidad.

10. COMO CONVERTIR DE NUEVO A RANGO UNA TABLA DE EXCEL

Para convertir una tabla de excel de nuevo a rango, hacemos clic en la barra de herramientas

contextual DISEÑO, luego en el grupo “Herramientas” hacemos clic en el botón “Convertir en

rango”. Entonces excel nos pregunta que si deseamos convertir a la tabla en un rango normal?.

Presionas Si. La tabla se convierte en una tabla o rango normal.

11. FILTRAR POR COLOR

Si tenemos una tabla donde tenemos celdas marcadas en la misma columna con colores de fondo

diferentes para diferenciar montos, o valores diferentes, lo primero que tenemos que hacer, si

tenemos una tabla o rango normal, es aplicar el filtro a la tabla seleccionada con la combinacion

de teclas CTRL + SHIFT + L. Luego hacemos clic en la lista desplegable en el encabezado de la

columna donde queremos filtrar la informacion. Hacemos clic en el menu que dice “Filtrar por

color” y seleccionamos el color de fondo por el cual queremos filtrar la tabla

12. OCULTAR LOS ENCABEZADOS TABLA EXCEL

Para ocultar los encabezados

...