manual excel avanzado 2007

Manual de Excel Avanzado.

Tecnológico NacionalManuel Olivares Rodríguez

2014

INTAE-MOR

|Departamento de Informática

2 Manual de Excel Avanzado:

TABLA DE CONTENIDO

Funciones de base de datos....................................................................................................4Asistente de Base de datos......................................................................................................6Función Buscar.............................................................................................................................7Función BuscarV..........................................................................................................................9Buscar H:......................................................................................................................................11Listas Desplegables..................................................................................................................12

Listas desplegables dependientes...................................................................................13Cómo actualizar una lista desplegable en Excel........................................................15Crear fórmula para detectar rango.................................................................................15Crear la lista desplegable en Excel.................................................................................16

Informe de tablas y graficos dinamicos.............................................................................19Funcionamiento de las tablas dinámicas......................................................................19Cómo crear una tabla dinámica.......................................................................................22Crear una tabla dinámica en Excel 2010......................................................................22Partes de una tabla dinámica............................................................................................26Dar formato a una tabla dinámica...................................................................................27Formato de valores en una tabla dinámica..................................................................28Filtrar una tabla dinámica...................................................................................................30Segmentación de datos en tablas dinámicas..............................................................31Modificar campos de una tabla dinámica.....................................................................33Quitar un campo de una tabla dinámica.......................................................................33Mover un campo de una tabla dinámica.......................................................................33Nuevo campo en una tabla dinámica.............................................................................34Consolidar datos en Excel con tablas dinámicas........................................................34Datos a consolidar en Excel...............................................................................................34Campos calculados...............................................................................................................37Crear un gráfico dinámico..................................................................................................39Mover un gráfico dinámico.................................................................................................41Mover un gráfico dinámico a otra hoja..........................................................................41Modificar el estilo de un gráfico dinámico....................................................................42Cambiar origen de datos de una tabla dinámica.......................................................42

Subtotales....................................................................................................................................43Subtotales anidados.............................................................................................................45

Consolidar Hojas de Trabajo..................................................................................................47

.



Funciones de base de datosLas funciones de base de datos en Excel facilitan nuestro trabajo con información que está organizada como una bases de datos, es decir, que se encuentra en un rango de celdas donde la primera fila tiene los títulos de cada columna. Estas fuciones nos permiten contar, sumar, multiplicar los valores de una columna que cumplen con los criterios especificados e inclusive podremos extraer un registro que cumpla con ciertas condiciones.

FUNCIÓN DESCRIPCIÓN BDCONTAR Cuenta las celdas que contienen números en el campo

(columna) de registros de la base de datos que cumplen las condiciones especificadas.

BDCONTARA Cuenta el número de celdas que no están en blanco en el campo (columna) de los registros de la base de datos que cumplen las condiciones especificadas.

BDMAX Devuelve el número máximo en el campo (columna) de registros de la base de datos que coinciden con las condiciones especificadas.

BDMIN Devuelve el número menor del campo (columna) de registros de la base de datos que coincide con las condiciones especificadas.

BDPRODUCTO

Multiplica los valores del campo (columna) de registros en la base de datos que coinciden con las condiciones especificadas.

BDPROMEDIO Obtiene el promedio de los valores de una columna, lista o base de datos que cumplen las condiciones especificadas.

BDSUMA Suma los números en el campo (columna) de los registros que coinciden con las condiciones especificadas.

Nota: Consulta la sintaxis de cada función haciendo clic sobre su nombre.

Sintaxis: BDFunción (base_de_datos; nombre_de_campo; criterio

.

http://exceltotal.com/funciones/base-de-datos/funcion-bdsuma/

http://exceltotal.com/funciones/base-de-datos/funcion-bdpromedio/

http://exceltotal.com/funciones/base-de-datos/funcion-bdproducto/

http://exceltotal.com/funciones/base-de-datos/funcion-bdproducto/

http://exceltotal.com/funciones/base-de-datos/funcion-bdmin/

http://exceltotal.com/funciones/base-de-datos/funcion-bdmax/

http://exceltotal.com/funciones/base-de-datos/funcion-bdcontara/

http://exceltotal.com/funciones/base-de-datos/funcion-bdcontar/


4 Manual de Excel Avanzado: Funciones de base de datos

Procedimiento

1. Escribir el criterio en cualquier lugar de la hoja2. Insertar función3. En categoría, seleccionar Base de datos4. Seleccionar bdcontar, bdpromedio,bdmax, bdmin o bdsuma5. Seleccionar el rango de inicio y fin de la base de datos en Base de

datos6. Seleccionar el nombre del campo sobre el cual se efectuaran los

cálculos Nombre_del_campo7. Seleccionar el rango donde escribió el Criterio

.

A B C D E F



Asistente de Base de datos

Ejercicios1: Base de datos

1. Contar las propiedades cuyo valor sea mayor de 50000

2. Mostrar el promedio del valor de las propiedades de la ciudad de Managua

3. Mostrar el valor de la propiedad de Masaya con el precio más bajo

4. Visualizar el valor promedio de las propiedades de León y Masaya

5. Totalizar el valor de las propiedades que tipo sean casa y estén ubicadas en el Dpto. de León

.


6 Manual de Excel Avanzado: Función Buscar

Función BuscarDevuelve un valor procedente de un rango de una fila o columna o de una matriz. La función BUSCAR tiene dos formas de sintaxis: vectorial y matricial. La forma vectorial de BUSCAR busca en un rango de una fila o de una columna un valor (vector) y devuelve un valor desde la misma posición en un segundo rango de una fila o de una columna.

La forma matricial de BUSCAR (la que estudiaremos), busca el valor especificado en la primera fila o en la primera columna de la matriz y devuelve un valor desde la misma posición en la última fila o columna de la matriz.Sintaxis:

Buscar (Valor_búscado; Matríz)Ejemplo: Una empresa dedicada la distribución y venta de bebidas refrescantes, ha decidido (como método de promoción y vía de investigación de mercado) premiar a aquellos consumidores que poseen etiquetas de los refrescos de dos litros.Al cabo de un mes de elabora la lista de los primeros ganadores, incluyendo los puntos obtenidos por cada uno. En la 3ra. Columna deberá aparecer el premio que corresponda a cada ganador:

Se trata de confeccionar dicha lista de modo que el premio conseguido por cada ganador aparezca automáticamente en la 3ra columna solo con introducir el número de puntos obtenidos.

.



Transcriba los datos de la tabla.

Pasos para aplicar la función Buscar.1. Seleccione insertar función.2. En categorías busque funciones de

búsqueda y referencias.3. Selecciones la función buscar.4. Se abre un cuadro de dialogo en donde debe

seleccionar la opción valor_buscado, matríz.

5. Seleccionemos cada uno de los rangos y presionemos F4 para fijar la función.

6. Haga clic en aceptar.

También puede hacerlo desde la celda escribiendo la función: Aplicar la función buscar en la columna C, en la celda 10,

.



seleccionemos el valor buscado que es el rango de celdas B10:B15, luego seleccionemos la matriz que en este caso es el rango de celdas A2:B6.

Función BuscarVEsta función se utiliza cuando son más de dos columnas. Use BUSCARV cuando los valores de comparación se encuentren en una columna a la izquierda o de los datos que desee encontrar.

Sintaxis: Buscar V (Valor_buscado; matriz_buscar_en; Indicador; ordenado)

Dónde:Valor buscado: es lo el rango que deseamos encontrar en base a la tabla matriz.Matríz_buscar_en: Es el rango de celdas que contiene los datos. Puede usar una referencia a un rango (por ejemplo, A2:D8) o un nombre de rango.

Los valores de la primera columna de matriz_buscar_en son los valores que busca valor_buscado.

Indicador: Es un número de columna del argumento matriz _ buscar en desde la cual debe devolverse el valor coincidente. Si el argumento indicador_columnas es igual a 1, la función devuelve el valor de la primera columna del argumento matriz_buscar en.

Ordenado: Es un valor lógico que especifica si BUSCARV va a buscar una coincidencia exacta o aproximada:

1. Si omite ordenado o es VERDADERO, devolverá una coincidencia exacta o aproximada. Si no encuentra ninguna coincidencia exacta, devolverá el siguiente valor más alto inferior a valor_buscado.

2. Si el argumento ordenado es FALSO, BUSCARV solo buscará una coincidencia exacta. Si hay dos o más valores en la primera columna de matriz_buscar_en que coinciden con el

.


9 Manual de Excel Avanzado: Función BuscarV

argumento valor_buscado, se usará el primer valor encontrado. Si no se encuentra una coincidencia exacta, se devolverá el valor de error #N/A.

Ejemplo.1. Transcríbalos siguientes datos.

2. Seleccionamos el rango B10_B15, luego la matriz que es el rango de celdas A2:D6, y luego seleccionamos el indicador que en este caso es 3, por que deseamos ingresar los premios 2 y como estos son la columna 3 entonces nuestro indicador es 3 y por ultimo ingresamos ordenado Falso.

Nota: Tambien puede utilizar el asisente de funciones en categorías, búsqueda y referenciar, función buscar V y seguir las

.


10 Manual de Excel Avanzado: Función BuscarV

instrucciones del asistente.

.


11 Manual de Excel Avanzado: Buscar H:

Buscar H: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. SintaxisBUSCARH (valor_buscado; matriz_buscar_en; indicador_filas; ordenado)Dónde:

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.

Indicador_filas es el número de fila en matriz_buscar_en desde el cual debe devolverse el valor coincidente. Si indicador_filas es 1, devuelve el valor de la primera fila en matriz_buscar_en;

Indicador_filas es el número de fila en matriz_buscar_en desde el cual debe devolverse el valor coincidente. Si indicador_filas es 1, devuelve el valor de la primera fila en matriz_buscar_en; Ejemplo:

1. Transcriba los siguientes datos.

2. Seleccionemos toda la tabla y copies dicha tabla.3. Abrimos una nueva hoja de cálculo y en grupo

portapapeles busquemos las opciones del comando pegar selecciones la opción transponer.

4. Una vez copiado los datos observe el resultado.

.


12 Manual de Excel Avanzado: Buscar H:

5. Nos ubicamos en la hoja donde tenemos los resultados de ganadores seleccionemos la celda C3 para crear la función.

a. Dónde: Valor buscado es la celda B2. Matriz es toda la tabla de pegado especial. Indicador es el número de celda de la fila que deseo

encontrar es este caso es 3. Ordenado es verdadero porque es un valor aproximado.

Listas Desplegables.Existen dos formas de realizar esto. Una es haciendo referencia a través de Rangos de celdas o realizarlo utilizando un Nombre de Rango que haga referencia a un rango de celdas.

1. Cree la lista datos o valores que desea desplegar. Esta puede ser del tipo de dato que usted desee en el orden que desee.

La lista puede estar en la misma hoja o en otra.

2. Luego de tener preparada la lista, seleccione la celda en la cual quiere que se despliegue la lista.

3. Ahora, vaya Datos,haga click en Validación de Datos4. En la lista de permitir seleccione Lista

5. En el Origen, seleccione los datos que quiere que contenga la lista, (ver imagen)

.


13 Manual de Excel Avanzado: Listas Desplegables.

6. Aceptar.

Si desea crear una lista desplegable con nombres de rangos, cree previamente el nombre de rango y luego haga referencia en el origen de la ventana validación de datos.

Listas desplegables dependientes En muchas ocasiones podemos necesitar generar listas desplegables, por medio de la herramienta de validación de datos, dependientes entre si, es decir, que en función de lo que seleccione en la primera lista se me habiliten unas u otras opciones en la segunda lista. Como siempre la solución es bastante sencilla. Supongamos que tenemos tres productos, a saber, Coca-Cola, Fanta y Powerade y que cada uno de ellos tiene las opciones que se muestran en la tabla:

Necesitamos que cuando seleccionemos el producto deseado en la celda C3 en C6 aparezcan los subproductos correspondientes al producto seleccionado. Para ello hacemos lo siguiente:1. Seleccionamos el rango B12:B15 y vamos al menú Insertar/Nombre/Crear. Por defecto nos pregunta si los nombres están en la fila superior de la selección y, como así es, aceptamos.2. Seleccionamos el rango B17:D21 y vamos al menú Insertar/Nombre/Crear. Por defecto nos pregunta si los nombres están en la fila superior y en la columna izquierda, ya que en esta ocasión se encuentra con texto en ambas zonas. Dejamos marcada sólo la opción de Fila Superior, que es donde se encuentran los nombres que utilizaremos.3. Nos situamos en la celda C3 y vamos al menú Datos/Validación de datos. Le indicamos permitir Lista y en Origen escribimos =Producto (que es el nombre del rango que tiene los productos que compondrán nuestra lista). Aceptamos.

.



4. Nos situamos en la celda C6 y vamos al menú Datos/Validación de datos. Le indicamos permitir Lista y en Origen escribimos =INDIRECTO(C3) y aceptamos.

De esta manera convertimos el nombre del producto seleccionado en C3 en el rango que debe mostrar en C6, ya que los nombres de los rangos de los subproductos coinciden con los nombres de los productos. INDIRECTO convierte esta relación y nos muestra la lista correcta en C6.

.



Cómo actualizar una lista desplegable en ExcelPara actualizar una lista desplegable en Excel podemos utilizar fórmulas que nos ayudarán a detectar cualquier cambio en el rango sobre el cual se basa nuestra lista desplegable y por lo tanto se actualizarán automáticamente sus elementos.Supongamos la siguiente lista de productos que será incluida dentro de un control de lista desplegable:

Crear fórmula para detectar rangoEl primer paso es crear una fórmula que identificará el rango donde se encuentran las celdas que contienen los elementos de nuestra lista. Para ello utilizaré la función DESREF la cual devuelve como resultado una referencia a un rango de celdas en base a ciertos parámetros de configuración. Comenzaré por escribir la fórmula de la siguiente manera:

.



DESREF(A2,El primer argumento de la función DESREF indica la celda donde iniciará el rango de los elementos de la lista. Los siguientes dos argumentos de la función DESREF permiten especificar el número de celdas que deseamos movernos a partir de la celda inicial. Ya que no deseo tener un “desfasamiento” de la celda A2 escribimos la fórmula de la siguiente manera:

DESREF(A2, 0, 0,El cuarto argumento de la función DESREF nos permite indicar el alto del rango y por lo tanto es importante utilizar una función que nos indique el número adecuado de elementos de la columna A. Para esto utilizaré la función CONTARA que cuenta el número de celdas no vacías dentro de un rango. Observa con detenimiento la manera en que utilizo la función CONTARA como el cuarto argumento de la función DESREF:

DESREF(A2, 0, 0, CONTARA(A:A) - 1)La función CONTARA tiene el argumento A:A que indica que debe tomar en cuenta toda la columna A. De acuerdo a nuestro ejemplo, la columna A tiene 4 celdas no vacías que incluyen el título, por esta razón debo restar uno al resultado de la función CONTARA para que me devuelva el número real de elementos en la columna.

De esta manera la función DESREF nos ayuda a obtener el rango de celdas que contienen los elementos que formarán parte de nuestra lista desplegable en Excel. Este rango es totalmente dinámico ya que si agregamos un nuevo elemento o eliminamos alguno otro se devolverá el rango de elementos adecuado.

Crear la lista desplegable en Excel

Ahora que ya tenemos la fórmula que nos devolverá el rango de celdas adecuado podemos iniciar con la creación de la lista desplegable. Selecciona el comando Datos > Herramientas de datos > Validación de datos. Se mostrará el cuadro de diálogo Validación de datos y en la pestaña Configuración selecciona la opción

.



Lista y en el cuadro de texto origen pega la fórmula que acabamos de construir en los pasos anteriores:

Al pulsar el botón Aceptar se habrá creado una nueva lista desplegable en Excel cuyos elementos son los enlistados en la columna A:

Actualizar la lista desplegable en ExcelPara actualizar los valores de la lista desplegable será suficiente con agregar un nuevo elemento en la columna A para que sea incluido automáticamente dentro de la lista desplegable.

.



Lo mismo sucederá si eliminamos algún elemento de la columna A ya que será excluido de la lista desplegable. La única condición es que todos los valores de la columna A sean contiguos, es decir, que no existan celdas en blanco entre ellos.

.



Informe de tablas y graficos dinamicosUna tabla dinámica es una de las herramientas más poderosas de Excel, pero también es una de las características que más usuarios de Excel se sienten intimidados a utilizar. Si eres uno de ellos te estás perdiendo de utilizar una gran herramienta de Excel.

Las tablas dinámicas te permiten resumir y analizar fácilmente grandes cantidades de información con tan sólo arrastrar y soltar las diferentes columnas que formarán el reporte.

Funcionamiento de las tablas dinámicasLas tablas dinámicas en Excel permiten agrupar datos en una gran cantidad de maneras diferentes para poder obtener la información que necesitamos. En esta ocasión explicaré el funcionamiento básico de una tabla dinámica.

Supongamos que tienes una tabla de datos que contiene mucha información sobre las ventas de la compañía entre las cuales se encuentra una columna con los productos de la empresa, también la ciudad donde se vende y las ventas correspondientes para cada ciudad.

Me han solicitado un reporte con el total de ventas por ciudad y el total de ventas por producto. Así que lo que necesitas hacer es sumar las ventas para cada ciudad y sumar las ventas de cada producto para obtener el reporte. En lugar de utilizar fórmulas podemos utilizar una tabla dinámica para obtener el resultado deseado. Una tabla dinámica nos permite hacer una comparación entre diferentes columnas de una tabla. Puedes imaginarte una tabla dinámica de la siguiente manera:

.


20 Manual de Excel Avanzado: Informe de tablas y graficos dinamicos

Lo primero que debemos hacer es especificar los campos de nuestra tabla de datos que vamos a comparar. Elegimos las ciudades como las filas de nuestra tabla dinámica:

Excel tomará todos los valores de ciudades de nuestra tabla de datos y los agrupará en la tabla dinámica, es decir, colocará los valores únicos de la columna de datos eliminando las repeticiones. Ahora hacemos lo mismo para especificar las columnas de la tabla dinámica.

.



Finalmente elegimos una columna de valores numéricos que serán calculados y resumidos en la tabla dinámica:

Así tendrás un reporte que compara las ciudades y los productos y para cada combinación obtendrás el total de ventas. Lo más importante que quiero transmitir con este artículo es que las tablas dinámicas te permiten elegir entre todas las columnas de una tabla de datos y hacer comparaciones entre ellas para poder realizar un buen análisis de la información.

.



Cómo crear una tabla dinámicaLas tablas dinámicas en Excel reciben su nombre por su capacidad de cambiar dinámicamente la información agrupada con tan solo rotar las columnas o filas de la tabla. En esta ocasión veremos un ejemplo claro de cómo crearlas.

Crear una tabla dinámica en Excel 2010Haz clic sobre cualquier celda de la tabla de datos que se desea considerar en la nueva tabla dinámica.

.



Ahora selecciona el comando Tabla dinámica que se encuentra dentro del grupo Tablas de la ficha Insertar.

Se mostrará el cuadro de diálogo Crear tabla dinámica. Si es necesario podrás ajustar el rango de datos que se considerará en la tabla dinámica.

.



En este mismo cuadro de diálogo se puede elegir si se desea colocar la tabla dinámica en una nueva hoja de Excel o en una ya existente. Haz clic en el botón Aceptar y se creará la nueva tabla dinámica.

.



Excel agregará en la parte izquierda del libro la tabla dinámica y en la parte derecha la lista de campos. Esta lista de campos está dividida en dos secciones, primero la lista de todos los campos de los cuales podremos elegir y por debajo una zona a donde arrastraremos los campos que darán forma al reporte ya sea como columna, fila, valor o como un filtro.

Para completar la tabla dinámica debemos arrastrar los campos al área correspondiente. Siguiendo el ejemplo propuesto del artículo anterior, colocaré como columna el campo Producto y como fila al campo Ciudad. Finalmente como valores colocaré el campo Ventas.

.



De manera predeterminada Excel aplica la funcón SUMA a los valores y la tabla dinámica que resulta después de hacer esta configuración es la

siguiente:

Utilizando una tabla dinámica fue posible crear un reporte de una manera fácil y sin la necesidad de utilizar fórmulas. Pronto veremos cómo se pueden elaborar tablas dinámicas más complejas que permitirán realizar un análisis profundo de la información.

Partes de una tabla dinámicaUna vez que has aprendido cómo crear una tabla dinámica en Excel es conveniente detenerse un momento para conocer las partes que la componen y comprender el funcionamiento de cada una de ellas.

Partes de una tabla dinámica en Excel

Justo cuando se ha creado una tabla dinámica se muestra en la parte derecha de la hoja la lista de campos disponibles y por debajo las áreas donde podemos arrastrar dichos campos. Estas áreas denotan cada una de las partes de una tabla dinámica.

.



Filtro de informe. Los campos que coloques en esta área crearán filtros para la tabla dinámica a través de los cuales podrás restringir la información que ves en pantalla. Estos filtros son adicionales a los que se pueden hacer entre las columnas y filas especificadas.

Etiquetas de columna. Esta área contiene los campos que se mostrarán como columnas de la tabla dinámica.

Etiquetas de fila. Contiene los campos que determinan las filas de la tabla dinámica.

Valores. Son los campos que se colocarán como las “celdas” de la tabla dinámica y que serán totalizados para cada columna y fila.

Una vez especificados los campos para cada una de las áreas, la tabla dinámica cobra vida. Puedes tener una tabla dinámica funcional con tan solo especificar las columnas, filas y valores. Los filtros son solamente

una herramienta para mejorar el análisis sobre los datos de la tabla dinámica.

.



Dar formato a una tabla dinámicaUna vez que has creado una tabla dinámica, Excel permite aplicarle formato fácilmente como si fuera una tabla de datos. La ficha Diseño incluye comandos especiales para aplicar formato a una tabla dinámica.

La ficha Diseño es una ficha contextual, por lo que deberás seleccionar la tabla dinámica para que se muestre.

Formato de valores en una tabla dinámicaEn esta ocasión te mostraré cómo dar formato rápidamente a los valores agrupados de una tabla dinámica de manera de puedan tener el formato de número adecuado. Solamente sigue los siguientes pasos:

Supongamos la siguiente tabla dinámica:

Para dar formato a los valores numéricos debes hacer clic sobre el campo correspondiente dentro del área Valores.

.



Del menú mostrado debes seleccionar la opción Configuración de campo de valor.

Se mostrará el cuadro de diálogo. Configuración de campo de valor.

Debes hacer clic en el botón Formato de número y se mostrará el cuadro de diálogo Formato de celdas donde podrás seleccionar el formato

deseado:

.



Después de hacer la selección adecuada acepta los cambios y de inmediato se aplicará el nuevo formato a todos los valores de la tabla dinámica:

Filtrar una tabla dinámicaPuedes filtrar y ordenar la información que se encuentra dentro de una tabla dinámica utilizando los filtros que Excel coloca de manera predeterminada en el reporte como Etiquetas de columna y Etiquetas de fila.

Al seleccionar cualquier de las opciones del filtro, la información será resumida y solamente mostrará un subconjunto de los datos de la tabla dinámica.Filtrar una tabla dinámica

Puedes filtrar y ordenar la información que se encuentra dentro de una tabla dinámica utilizando los filtros que Excel coloca de manera predeterminada en el reporte como Etiquetas de columna y Etiquetas de fila.

.



Al seleccionar cualquier de las opciones del filtro, la información será resumida y solamente mostrará un subconjunto de los datos de la tabla dinámica.

Una vez que se ha aplicado un filtro, Excel reemplaza el icono predeterminado para indicar que ese campo está siendo actualmente filtrado

Para mostrar de nuevo todos los valores de los campos filtrados debes hacer clic en el botón de filtrado y seleccionar la opción Borrar filtro de.

De igual manera puedes ordenar instantáneamente los valores de la tabla dinámica. Solamente haz clic sobre el botón de Etiquetas de fila o Etiquetas de columna y elige la opción Ordenar de A a Z o la opción Ordenar de Z a A.

.



Segmentación de datos en tablas dinámicasLa segmentación de datos en tablas dinámicas es una nueva característica de Excel 2010 que permite hacer un filtrado de los datos dentro de una tabla dinámica. De esta manera puedes filtrar fácilmente la información por más de una columna.

En primer lugar debes hacer clic sobre cualquier celda de la tabla dinámica y posteriormente en la ficha Opciones y dentro del grupo Ordenar y filtrar deberás hacer clic sobre el comando Insertar Segmentación de datos.

Excel mostrará el cuadro de diálogo Insertar segmentación de datos.

En este cuadro deberás seleccionar los campos que deseas utilizar como filtros en la tabla dinámica y Excel colocará un filtro para cada campo seleccionado:

Para filtrar la información de la tabla dinámica es suficiente con hacer clic

sobre cualquiera de las opciones del filtro.

.



Excel ajustará la información de la tabla dinámica de acuerdo a las opciones seleccionadas. Para mostrar de nuevo toda la información puedes hacer clic en el botón Borrar filtro que se encuentra en la esquina superior derecha de cada panel.

.



Podrás agregar tantos filtros como campos disponibles tengas en la tabla dinámica, lo cual te permitirá hacer un buen análisis de la

información.

Modificar campos de una tabla dinámicaLas tablas dinámicas son muy fáciles de manipular y de modificar. Excel permite cambiar los campos de la tabla de una manera muy sencilla y reestructurar la información con tan solo arrastrar los nuevos campos.

.



Quitar un campo de una tabla dinámicaPara remover un campo arrástralo fuera del área donde se encuentra y cuando el puntero del ratón se convierta en una “X” suelta el botón del ratón para eliminar el campo. Otra manera de eliminar el campo es haciendo clic sobre él y seleccionar la opción Quitar campo

Mover un campo de una tabla dinámicaPara mover un campo ya existente hacia una nueva ubicación solamente arrástralo de un área a otra o de igual manera selecciona cualquiera de las opciones de menú que permiten mover el campo a cualquiera de las áreas disponibles: Mover al filtro de informe, Mover a rótulos de fila, Mover a rótulos de columna o Mover a valores.

Con este método puedes cambiar fácilmente las columnas por filas y viceversa.

Nuevo campo en una tabla dinámicaFinalmente para agregar un nuevo campo puedes arrastrarlo desde la lista de campos hacia el área deseada. También puedes marcar la caja de selección del campo lo cual hará que sea agregado a alguna de las áreas predeterminadas.

Las tablas dinámicas son un elemento de análisis de información de Excel muy poderoso y esa fortaleza proviene

.



de la facilidad que provee para manipular la información de acuerdo a tus necesidades.

Consolidar datos en Excel con tablas dinámicasFrecuentemente necesitamos consolidar datos en Excel pero la técnica que utilicemos depende mucho del tipo de consolidación a realizar así como la manera en como se encuentre las fuentes de datos.En esta ocasión mostraré la técnica de consolidación de datos en Excel con tablas dinámicas. Este método no solamente consolidará los datos sino que tendrás un reporte flexible al mismo tiempo sin mayor esfuerzo.Datos a consolidar en ExcelPara este ejemplo, tengo tres hojas de Excel. Cada una de las hojas tiene las ventas de los tres vendedores en la empresa en los primeros 2 meses del año.

.



El reporte que necesito realizar es sobre las ventas del mes de enero y febrero. Para iniciar debo abrir el Asistente para tablas y gráficos dinámicos con el atajo de teclado ALT+T+B, o bien puedes agregar el botón a la barra de acceso rápido, el botón se llama Asistente para tablas dinámicas.

Seleccionamos la opción Rangos de consolidación múltiples y también la opción Tabla dinámica. Al pulsar el botón siguiente deberé seleccionar la opción Crear un solo campo de página.

En el siguiente paso deberás agregar los rangos de cada una de las tablas en las diferentes hojas de Excel:

Finalmente hago clic en Siguiente y en el último paso selecciono la opción Hoja de cálculo nueva que significa que Excel creará una nueva hoja en mi libro donde pondrá el resultado. A continuación puedes ver los datos consolidados:

.



Puedes dar el detalle final a esta tabla dinámica aplicando un formato de número a las celdas.

Campos calculados¿Calcular una comisión para cada vendedor, directamente en las tablas dinámicas?

Sí, se puede.

De hecho ese es el ejemplo que voy a usar para mostrarte cómo se puede agregar una columna adicional en la tabla dinámica, sin tener que modificar tu base de datos original.

Imagina que tienes una base de datos en la que tengas estas dos columnas. Te interesa hacer un reporte por vendedor con su respectivo monto de ventas.

Si sigues los pasos para crear una tabla dinámica tendrás como resultado algo parecido a esto:

.

http://raymundoycaza.com/tablas-dinamicas/

http://raymundoycaza.com/static/tablas-dinamicas-000318.png%00%E5%A1%B9%EF%92%81%E1%B4%BB%E4%A1%BF%E2%B2%AF%E5%B6%82%E8%97%84%E6%8C%A7%00%00%EA%AE%A5



Pero, necesitas asignar una comisión del 10% a aquellos que lograron un total de ventas mayor a $3,000¿Cómo calculamos eso en las tablas dinámicas?

1. Pincha en cualquier celda dentro de tu tabla dinámica.

2. Pincha en la pestaña ‘Opciones’.3. Ahora pincha en el botón ‘Campos, elementos y conjuntos’. Luego

selecciona la opción ‘Campo calculado…’ como te muestro en la imagen:

4. Aparecerá un cuadro de diálogo como el de la siguiente imagen, en el que debes hacer lo siguiente:

Escribe el nombre de la nueva columna ‘Comisión’- Escribe la fórmula que se usará para calcular la nueva columna = SI(Ventas > 3000, Ventas * 0.1, 0)

5- Pincha sobre el botón ‘Sumar’ (3 Verás que la nueva columna se agregó a la lista de campos disponibles:

.






Verás que la nueva columna se agregó a la lista de campos disponibles:Solo debes pinchar sobre el botón ‘Aceptar y verás una nueva columna en tu tabla dinámica.

El resultado será éste:

Crear un gráfico dinámicoUn gráfico dinámico es un gráfico que está basado en los datos de una tabla dinámica y que se ajusta automáticamente al aplicar cualquier filtro sobre la información. Con este tipo de gráfico podrás ayudar en la compresión de los datos de una tabla dinámica.

Cómo crear un gráfico dinámico en Excel

Para crear un gráfico dinámico debes hacer clic sobre cualquier celda de la tabla dinámica que servirá como base del gráfico y posteriormente hacer clic sobre el comando Gráfico dinámico que se encuentra dentro del grupo Herramientas de la ficha Opciones.

.



Se mostrará el cuadro de diálogo Insertar gráfico de donde podrás seleccionar el tipo de gráfico que deseas utilizar.

Una vez que has seleccionado el gráfico adecuado Excel lo insertará en la hoja de trabajo. Observa cómo el gráfico se modifica al momento de aplicar algún filtro sobre la tabla dinámica:

De igual manera puedes filtrar la información utilizando los botones de filtrado que aparecen dentro del gráfico dinámico:

.



Para eliminar un gráfico dinámico solamente debes seleccionarlo y pulsar la tecla Suprimir.

Mover un gráfico dinámicoEs probable que encuentres útil el mover un gráfico dinámico a su propia hoja de Excel para poder trabajar con él de manera independiente e inclusive aplicar algún estilo y formato personalizado.

Mover un gráfico dinámico a otra hoja

Para mover un gráfico dinámico debes seleccionarlo y posteriormente seleccionar el comando Mover gráfico que se encuentre en el grupo Ubicación dentro de la ficha Diseño.

.



Se mostrará el cuadro de diálogo Mover gráfico y podrás seleccionar una nueva hoja donde será colocado.

Modificar el estilo de un gráfico dinámico

Ya sea que muevas el gráfico a una nueva hoja de Excel o lo dejes en su ubicación original puedes modificar su estilo con los comandos de la ficha Diseño, Presentación y Formato:

Dentro de la ficha Diseño encontrarás los comandos necesarios para seleccionar un nuevo estilo del gráfico dinámico. Con los comandos de la ficha Presentación podrás personalizar aún más el gráfico dinámico agregando títulos, rótulos de eje y leyendas. Finalmente la ficha Formato permitirá refinar los estilos de forma del gráfico creado.

Cambiar origen de datos de una tabla dinámica

Es probable que el origen de datos de una tabla dinámica haya cambiado de ubicación y ahora tengas que considerar este cambio en tu tabla dinámica. A continuación unos pasos muy sencillos para modificar el origen de los datos.

.



Haz clic sobre la tabla dinámica y selecciona el comando Cambiar origen de datos que se encuentra en la ficha Opciones dentro del grupo Datos.

Se mostrará el cuadro de diálogo Cambiar origen de datos de tabla dinámica el cual te permitirá ampliar (o reducir) o cambiar el rango de los datos de la tabla dinámica.

Acepta los cambios y la tabla dinámica reflejará el nuevo rango de datos que has especificado.

Subtotales

Cuando hablamos de crear un resumen de los datos de una lista, nos referimos a crear subtotales agrupando los registros por alguno de los campos de la lista.

Un ejemplo sería una tabla de vehículos clasificados por marca y modelo; y queremos averiguar el costo total de cada marca.

.


45 Manual de Excel Avanzado: Subtotales

Copie la siguiente tabla y realizaremos un subtotal por marca

Pasos para crear subtotal:

1-

1- Ordene la tabla por la columna Marca mantenga seleccionada la tabla.

2- Haga clic en la pestaña 3- Datos en el grupo Esquema

selecciones el comando Subtotal.

4- Se abre un cuadro de dialogo, seleccione en para cada cambio Marca.

5- En usar Función: elije la opción

.

VEHICULOSMARCA MODELO AÑOS COSTO

Ford Fiesta 5 $ 16,000.00

Peugeot 205 2 $ 16,000.00

Ford Fussion 3 $ 17,500.00

Opel Zafirra 4 $ 18,500.00

Opel Vectra 6 $ 17,600.00

Seat Córdoba 8 $ 17,250.00

Peugeot 306 5 $ 16,300.00

Seat León 3 $ 15,800.00



suma, en agregar subtotal seleccione la opción Costo.6- Aceptar

Este es el resultado:

Subtotales anidadosEn este tema se muestra cómo utilizar los subtotales anidados, es decir, insertar subtotales de grupos más pequeños, donde, ya existen otros subtotales, puede utilizar distintas funciones en una misma hoja y con los mismos datos.

En otras palabras es una función que contiene otra función por lo que se les denomina anidados. En el ejemplo a continuación, los subtotales de cada reporte están en una lista que ya tiene subtotales para cada región.

Insertar subtotales anidadosAsegú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.

Ordene el rango por varias columnas, ordenando primero por la columna de subtotales exteriores, después por la siguiente columna interior de los subtotales anidados

Transcriba la siguiente tabla.

VENDEDORES

Vendedor Año Producto Region VentasCarlos 2006 Carnes Norte 40000Luis 2006 Lácteos Norte 25000Roberto 2007 Bebidas Centro 60000Carlos 2007 Carnes Centro 35000Luis 2006 Lácteos Norte 20000Luis 2007 Bebidas Centro 30000Roberto 2006 Carnes Centro 60000Carlos 2007 Lácteos Norte 30000Carlos 2006 Bebidas Centro 60000Roberto 2007 Carnes Norte 30000Roberto 2006 Lácteos Centro 25000Luis 2007 Bebidas Norte 37000Carlos 2006 Carnes Centro 90000Roberto 2006 Lácteos Norte 71000Roberto 2007 Bebidas Centro 56000Luis 2007 Carnes Norte 70000

Crear un subtotal que muestre el resumen de los datos para cada cambio en vendedor, la función que utilizara será máximo y el campo sobre el que se realizara el subtotal será ventas.Agregar un subtotal anidado que muestre otro resumen de datos por año, la función a utilizar será suma y el campo sobre el que se calculara será ventas.

.



Procedimiento

1- Ordene la tabla por vendedor y mantenga seleccionado los datos.

2- Ir a la pestaña Datos en el grupo esquema seleccione en comando Subtotales

3- En la lista para cada cambio seleccione Vendedor4- En función seleccione Max5- En agregar subtotal seleccione Ventas6- Haga clic en el botón Aceptar.7- Para realizar el subtotal anidado haga clic en la pestaña Datos,

grupo Esquema seleccione el comando Subtotal.8- En la lista para cada cambio en, seleccione Año9- En función seleccione suma.10- En agregar subtotal seleccione Ventas11- Desactivar opción reemplazar subtotales actuales si ésta se

encuentra activa12- Haga clic en el botón Aceptar.13- Observe el resultado.

.



Consolidar Hojas de TrabajoEsta herramienta sirve para obtener datos de dos o más libros y obtener un resultado, a partir de estos, en un libro nuevo; por ejemplo puede tener los volúmenes de ventas de varios almacenes por mes en libros distintos y con esta herramienta puede crear uno nuevo con la suma anual de cada almacén.Un ejemplo donde se puede utilizar la consolidación de hojas de trabajo puede ser una compañía que dispone de varios departamentos, cada uno de ellos con un presupuesto, para conocer el presupuesto total de la empresa, crearíamos una hoja dependiente y los libros fuentes serían los libros de los departamentos con sus prepuestos. Nota: el consolidado se puede hacer en una nueva hoja o en la hoja actual.

Pasos para consolidar datos:

1. Ir a la pestaña Datos, ubicarse en el grupo herramientas de datos y seleccionar el comando consolidar.

2. Nos aparece un cuadro de dialogo en como se observa en la imagen.

3. En Función: seleccione la función suma.

4. En Referencia: vamos seleccionando las celdas donde están los datos que deseas consolidar.

5. Haga clic en Agregar para añadir las celdas seleccionadas a la lista de todas las referencias,

6. Haz clic nuevamente en Agregar, para seleccionar otro rango.

.

Enero 2014Ingreso 500, 000Gasto 300, 000Beneficio 200, 000


50 Manual de Excel Avanzado: Consolidar Hojas de Trabajo

7. Activa la casilla columna izquierda Fila superior y columna izquierda

8. Aceptar.Este será el resultado de la suma de ingreso, gasto y beneficio de Diciembre 2008 a Febrero 2009.

.

Ingreso 130, 000

Gastos 720, 000

Beneficios 630, 000

manual excel avanzado 2007

Documents